Формула на excel: Обзор формул — Служба поддержки Майкрософт

Экспорт отчетов > Электронные таблицы > Excel значение

При экспорте отчета в электронные таблицы, каждое значение данных будет располагаться в определенной ячейке Excel таблицы. Например, если в построенном отчета четыре колонки данных и десять строк, то при экспорте в Excel таблицу будет занято 40 ячеек.

 

Однако, иногда возникает необходимость применить формулы Excel к этим ячейкам. Это можно выполнить в готовом документе, открыв его на редактирование или определить формулу в свойстве Excel значение (Excel value) для текстового компонента в дизайнере отчетов при разработке отчета.

 

Для того чтобы задать текстовому компоненту формулу Excel следует в дизайнере отчетов:
Выделить этот текстовый компонент в шаблоне отчета;
На панели свойств, нажать кнопку Обзор (Browse) у свойства Excel значение;
В открывшимся редакторе задать формулу Excel.

 

Рассмотрим несколько примеров применения Excel формул в шаблоне отчета. Допустим, есть отчет с перечнем продуктов, их ценами, количеством ордеров и количеством продуктов на складе.

 

 

Пример 1

Кроме данных, в этом отчете отсутствуют другие компоненты. Все четыре колонки по 77 значений в каждой. Это значит, что при экспорте в Excel будут заняты ячейки:
Имена продуктов будут располагаться в ячейках с A1 по А77;
Цены продуктов будут располагаться в ячейках с B1 по B77;

Количество ордеров продуктов будут располагаться в ячейках с C1 по C77;

Количество на складе продуктов будут располагаться в ячейках с D1 по D77.

 

Допустим, необходимо чтобы в столбце D выводилось не количество продуктов на складе, а объем продаж продуктов (цена умноженная на количество ордеров). В этом случае, следует:

 

Шаг 1: Выделить текстовый компонент, в котором необходимо вывести результат расчета Excel формулы;

 

Шаг 2: На панели свойств, нажать кнопку Обзор у свойства Excel значение;

 

Шаг 3: В открывшимся редакторе, ввести Excel формулу. В данном случае, формулу =B{Line}*C{Line};

 

Шаг 4: Нажать кнопку Ок в редакторе;

 

Шаг 5: Перейти на вкладку предварительного просмотра или открыть во вьювере этот отчет;

 

Шаг 6: Нажать кнопку Сохранить (Save) и в выпадающем меню выбрать команду Microsoft Excel File;

 

Шаг 7: Определить настройки экспорта, нажать кнопку Ок;

 

Шаг 8: Выбрать место сохранения Excel документа, изменить имя и нажать кнопку Сохранить (Save).

 

После этого откройте этот Excel документ, в колонке D для каждой ячейки будет рассчитана формула, в результате чего, в ячейке будет отображаться значение. В данном случае, для каждого продукта будет рассчитан объем продаж.

 

 

Пример 2

Кроме данных, в отчете присутствуют другие компоненты, или скажем, если бэнд Данные расположен на другом компоненте. В этом случае, спрогнозировать ячейки, в которые будут вставлены данные не предоставляется возможным. Поэтому, для того чтобы в Excel формуле корректно задать диапазон ячеек, следует:

 

Шаг 1: Создать финальную версию шаблона отчета, со всеми компонентами и их правильным местоположением. В этом случае, добавим заголовок отчета и заголовок данных, а также список данных разместим на другом компоненте — панели.

Шаг 2: Перейти на вкладку предварительного просмотра или открыть этот отчет во вьювере;

 

Шаг 3: Нажать кнопку Сохранить и в выпадающем меню выбрать команду Microsoft Excel File;

 

Шаг 4: Определить настройки экспорта, нажать кнопку Ок;

 

Шаг 5: Выбрать место сохранения Excel документа, изменить имя и нажать кнопку Сохранить;

 

Шаг 6: Открыть этот сохраненный документ и запомнить диапазон ячеек с данными;

 

Шаг 7: Вернуться в дизайнер отчетов с этим шаблоном;

 

Шаг 8: Выделить текстовый компонент, в котором необходимо вывести результат расчет Excel формулы;

 

Шаг 9: На панели свойств, нажать кнопку Обзор у свойства Excel значение;

 

Шаг 10: В открывшимся редакторе ввести Excel формулу с необходимым диапазоном ячеек. В данном случае, формулу =C{Line+3}*D{Line+3}. Число 3 в этом случае, это количество строк в Excel файле, которое необходимо пропустить.

 

Шаг 11: Нажать кнопку Ок в редакторе;

 

Шаг 12: Перейти на вкладку предварительного просмотра или открыть этот отчет во вьювере;

 

Шаг 13: Нажать кнопку Сохранить и в выпадающем меню выбрать команду Microsoft Excel File;

 

Шаг 14: Определить настройки экспорта, нажать кнопку Ок;

 

Шаг 15: Выбрать место сохранения Excel документа, изменить имя и нажать кнопку Сохранить.

 

После этого откройте этот Excel документ, в колонке E для каждой ячейки будет рассчитана формула, в результате чего, в ячейке будет отображаться значение. В данном случае, для каждого продукта будет рассчитан объем продаж.

 

Почему Эксель не считает формулу: 5 решений проблемы

Содержание

• Устранение проблем с вычислением
  • Способ 1: изменение формата ячеек
  • Способ 2: отключение режима «Показать формулы»
  • Способ 3: исправление ошибки в синтаксисе
  • Способ 4: включение пересчета формулы
  • Способ 5: ошибка в формуле
• Вопросы и ответы

Одной из наиболее востребованных возможностей Excel является работа с формулами. Благодаря данной функции программа самостоятельно производит различного рода расчеты в таблицах. Но иногда случается так, что пользователь вписывает формулу в ячейку, но она не выполняет своего прямого назначения – вычисления результата. Давайте разберемся, с чем это может быть связано, и как решить данную проблему.

Причины проблем с вычислением формул в Экселе могут быть совершенно разными. Они могут быть обусловлены, как настройками конкретной книги или даже отдельного диапазона ячеек, так и различными ошибками в синтаксисе.

Способ 1: изменение формата ячеек

Одной из самых распространенных причин, по которой Эксель вообще не считает или не правильно считает формулы, является неверно выставленный формат ячеек. Если диапазон имеет текстовый формат, то расчет выражений в нем вообще не производится, то есть, они отображаются как обычный текст. В других случаях, если формат не соответствует сути рассчитываемых данных, результат, выводимый в ячейку, может отображаться некорректно. Давайте выясним, как решить эту проблему.

1. Для того, чтобы посмотреть, какой формат имеет конкретная ячейка или диапазон, переходим во вкладку «Главная». На ленте в блоке инструментов «Число» имеется поле отображения текущего формата. Если там указано значение «Текстовый», то формула точно вычисляться не будет.
2. Для того, чтобы произвести смену формата достаточно кликнуть по данному полю. Откроется список выбора форматирования, где можно выбрать значение, соответствующее сути формулы.
3. Но выбор типов формата через ленту не такой обширный, как через специализированное окно. Поэтому лучше применить второй вариант форматирования. Выделяем целевой диапазон. Кликаем по нему правой кнопкой мыши. В контекстном меню выбираем пункт «Формат ячеек». Можно также после выделения диапазона нажать комбинацию клавиш Ctrl+1.
4. Открывается окно форматирования. Переходим во вкладку «Число». В блоке «Числовые форматы» выбираем тот формат, который нам нужен. Кроме того, в правой части окна имеется возможность выбрать тип представления конкретного формата. После того, как выбор произведен, кликаем по кнопке «OK», размещенной внизу.
5. Выделите поочередно ячейки, в которых функция не считалась, и для пересчета нажмите функциональную клавишу F2.

Теперь будет производиться расчет формулы в стандартном порядке с выводом результата в указанную ячейку.

Способ 2: отключение режима «Показать формулы»

Но возможно причина того, что вместо результатов расчета у вас отображаются выражения, состоит в том, что в программе включен режим «Показать формулы».

1. Чтобы включить отображение итогов, переходим во вкладку «Формулы». На ленте в блоке инструментов «Зависимости формул», если кнопка «Показать формулы» активна, то кликаем по ней.
2. После этих действий в ячейках снова вместо синтаксиса функций начнет отображаться результат.

Способ 3: исправление ошибки в синтаксисе

Формула также может отображаться как текст, если в её синтаксисе были допущены ошибки, например, пропущена или изменена буква. Если вы вводили её вручную, а не через Мастер функций, то такое вполне вероятно. Очень распространенной ошибкой, связанной с отображением выражения, как текста, является наличие пробела перед знаком «=».

В таких случаях нужно внимательно пересмотреть синтаксис тех формул, которые неправильно отображаются, и внести в них соответствующие коррективы.

Способ 4: включение пересчета формулы

Бывает и такая ситуация, что формула вроде и отображает значение, но при изменении связанных с ней ячеек сама не меняется, то есть, результат не пересчитывается. Это означает, что у вас неправильно настроены параметры вычислений в данной книге.

1. Перейдите во вкладку «Файл». Находясь в ней, следует кликнуть по пункту «Параметры».
2. Откроется окно параметров. Нужно перейти в раздел «Формулы». В блоке настроек «Параметры вычислений», который расположен в самом верху окна, если в параметре «Вычисления в книге», переключатель не установлен в позицию «Автоматически», то это и есть причина того, что результат вычислений неактуальный. Переставляем переключатель в нужную позицию. После выполнения вышеуказанных настроек для их сохранения в нижней части окна жмем на кнопку «OK».

Теперь все выражения в данной книге будут автоматически пересчитываться при изменении любого связанного значения.

Способ 5: ошибка в формуле

Если же программа все-таки производит расчет, но в результате показывает ошибку, то тут вероятна ситуация, что пользователь просто ошибся при вводе выражения. Ошибочными формулами считаются те, при расчете которых в ячейке появляются следующие значения:

• #ЧИСЛО!;
• #ЗНАЧ!;
• #ПУСТО!;
• #ДЕЛ/0!;
• #Н/Д.

В этом случае нужно проверить, правильно ли записаны данные в ячейках, на которые ссылается выражение, нет ли в них ошибок в синтаксисе или не заложено ли в самой формуле какое-либо некорректное действие (например, деление на 0).

Если функция сложная, с большим количеством связанных ячеек, то легче проследить вычисления с помощью специального инструмента.

1. Выделяем ячейку с ошибкой. Переходим во вкладку «Формулы». На ленте в блоке инструментов «Зависимости формул» кликаем по кнопке «Вычислить формулу».
2. Открывается окно, в котором представляется полный расчет. Нажимаем на кнопку «Вычислить» и просматриваем вычисление пошагово. Ищем ошибку и устраняем её.

Как видим, причины того, что Эксель не считает или не правильно считает формулы, могут быть совершенно различными. Если вместо расчета у пользователя отображается сама функция, тот в этом случае, скорее всего, либо ячейка отформатирована под текст, либо включен режим просмотра выражений. Также, возможна ошибка в синтаксисе (например, наличие пробела перед знаком «=»). В случае если после изменения данных в связанных ячейках результат не обновляется, то тут нужно посмотреть, как настроено автообновление в параметрах книги. Также, нередко вместо корректного результата в ячейке отображается ошибка. Тут нужно просмотреть все значения, на которые ссылается функция. В случае обнаружения ошибки следует устранить её.

Как избавиться от формул в Excel | Малый бизнес

Автор: Стивен Мелендес Обновлено 8 августа 2018 г. их вычисленные значения. Вы можете заменить часть или всю формулу постоянным значением, когда захотите. Вы также можете настроить формулы так, чтобы они не выполняли автоматический пересчет при изменении входных значений, пока вы не сообщите об этом Excel.

Удалить формулу в Excel

В некоторых случаях вы можете использовать формулу Excel для выполнения многоэтапных вычислений, но вы предпочтете просто заменить результаты фактическим числом, а не формулой. Это может быть полезно, если вы хотите реорганизовать электронную таблицу для целей презентации или просто не хотите беспокоиться о том, что произойдет, если вы измените определенные входные ячейки.

Например, если вы используете функцию формулы ВПР, которая извлекает данные из других частей электронной таблицы, вы можете очистить этот раздел электронной таблицы, не беспокоясь о проблемах ВПР в других местах ваших формул.

Какой бы ни была причина, вы можете удалить формулу в Excel и заменить ее ее значением так же просто, как скопировать и вставить. Просто используйте мышь, чтобы выбрать область электронной таблицы, содержащую рассматриваемые формулы, и нажмите кнопку «Копировать». Затем, когда эта область электронной таблицы все еще выделена, нажмите кнопку «Вставить». После этого щелкните стрелку рядом с кнопкой «Параметры вставки» и выберите «Только значения». Если вы предпочитаете, вы можете вручную ввести число или другой результат формулы в ячейки по отдельности, хотя это обычно более утомительно и подвержено человеческим ошибкам.

В качестве альтернативы, если вы редактируете ячейку, вы можете использовать функцию преобразования формулы Excel в значение, нажав клавишу F9 на клавиатуре, чтобы навсегда заменить формулу ее значением.

Заменить часть формулы

Если вы хотите заменить только часть формулы ее значением, вы можете отредактировать код формулы, дважды щелкнув ячейку.

Затем выберите часть формулы, которую вы хотите заменить, и нажмите клавишу F9, чтобы вычислить ее подзначение. Убедитесь, что вы включили целый раздел формулы, например подраздел в круглых скобках, включая открывающую и закрывающую круглую скобку, или ссылку на другую ячейку целиком. Нажмите Enter, чтобы заменить эту часть формулы ее значением.

Отключение автоматического вычисления значений формул

Если в электронной таблице много формул, может потребоваться много времени для повторного вычисления формул при изменении входных данных. Если вы не хотите, чтобы это происходило автоматически, вы можете перенастроить этот параметр.

Перейдите на вкладку «Файл», затем выберите «Параметры» и «Формулы». В разделе «Параметры расчета» измените «Расчет рабочей книги» на «Вручную». Затем формулы будут автоматически пересчитываться только тогда, когда вы сохраните электронную таблицу или сообщите об этом Excel. Если вы также хотите предотвратить автоматический пересчет при сохранении файла, снимите флажок «Пересчитывать книгу перед сохранением».

Если вы хотите пересчитать формулы, нажмите «Рассчитать сейчас» на вкладке «Формулы», чтобы пересчитать все открытые листы, или нажмите «Рассчитать лист», чтобы пересчитать только текущий лист. Сбросьте настройку «Расчет рабочей книги» на «Автоматически», если вы хотите вернуться к автоматическому пересчету. Помните, что если вы не используете автоматический расчет, ваши сбросы формул могут не синхронизироваться с вашими входными ячейками.

Ссылки

• Microsoft: заменить формулу ее результатом
• Microsoft: переосмысление изменения формулы, итерация, или точность
• Microsoft: Vlookup Function

Ресурсы

• Microsoft Office: New Forcement Excel 2013