Как вычислить функцию в excel с шагом
Обновлено: 04.07.2024
Часто ли Вам приходилось разбирать чужой файл с непонятными на первый взгляд формулами? Вроде считают, но как? Вроде и разобраться хочется как работает какая-нибудь мега-формула - но как это сделать? Я хочу рассказать о паре простых шагов, которые необходимо сделать, чтобы разобраться в работе любой формулы. Давайте попробуем разобраться на примере формулы из моей статьи: Как получить список уникальных(не повторяющихся) значений?:
=ИНДЕКС($A$2:$A$51;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0;СТРОКА($A$1:$A$50));1))
Что нам понадобится для начала:
Tips_All_ExtractUnique.xls (108,0 KiB, 17 244 скачиваний)
Если Вы не знакомы с функциями, используемыми в приведенной выше формуле и хотите разобраться - необходимо просмотреть справку по ним, иначе работу формулы не поймете даже с пояснениями
Вот теперь можно начать потрошить формулу. В принципе, самый сложный этап уже пройден. Теперь остается только воспользоваться встроенным средством Excel - окно просмотра этапов вычислений формулы. Выделяем ячейку с нужной формулой и:
для пользователей Excel 2007 и более поздних версий:
вкладка Формулы-группа кнопок Зависимости формул-Вычислить формулу (Formulas-Formula Auditing-Evaluate Formula)
для пользователей Excel 2003:
Сервис-Зависимости формул-Вычислить формулу
Появится форма
После каждого нажатия на кнопку Вычислить (Evaluate) будет произведен очередной этап вычислений формулы и в окне формы будет отображен этот этап. Вычисляемая в текущий момент часть формулы(этап) подчеркивается одинарной линией.
Что следует знать: сначала вычисляется самая глубоко вложенная функция, а уже потом самая первая. Самая первая и основная функция у нас будет ИНДЕКС , а самая глубоко вложенная - СЧЁТЕСЛИ . Поэтому на примере нашей формулы следующим этапом будет вычисление функции СЧЁТЕСЛИ и в скобках будет показан результат для этой функции: . Т.е. для каждого значения диапазона $A$2:$A$51 будет выведено количество - сколько раз это значение встречается в диапазоне $C$1:C1 . Т.к. это первая строка формулы - то будут все нули:
Далее будет произведено вычисление логического выражения =0 : сравнение результата функции СЧЁТЕСЛИ с нулем. Результатом будет ИСТИНА или ЛОЖЬ.
Этот результат(ИСТИНА, ЛОЖЬ) обрабатывается далее функцией ЕСЛИ . А в ЕСЛИ у нас условие: если СЧЁТЕСЛИ равно нулю (т.е. если результат ИСТИНА), то в ЕСЛИ возвращаем номер строки( СТРОКА($A$1:$A$50) ), если нет - то вернет ЛОЖЬ.
Т.к. функция НАИМЕНЬШИЙ работает только с числами, игнорируя любые другие значения, то она не будет учитывать ЛОЖЬ(т.к. это логическое значение, а не число), а будет отбирать только числа - что и ложится в основу формулы.
Чтобы в этом примере было более просто разобраться(насколько это возможно), коротко расскажу о принципе работы этой формулы: если значение из диапазона $A$2:$A$51 встречается в диапазоне вывода формулы(на строку выше) $C$1:C1 , то СЧЁТЕСЛИ вернет не нулевое значение и получится ЛОЖЬ. Если такого значения ещё нет - будет нуль и в НАИМЕНЬШИЙ будет передан номер строки. А уже номер строки передается в ИНДЕКС , которая возвращает непосредственно значение по номеру строки. Чтобы более точно понять подобные формулы надо рассмотреть не только формулу из первой ячейки, но и пару следующих.
Помимо кнопки Вычислить в этом окне есть и другие: Шаг с заходом (Step In) и Шаг с выходом (Step Out) . Делают они почти тоже самое, но доступны не для всех видов формул, а лишь для тех, в которых участвуют ссылки на ячейки с другими функциями. Если вычисляемая в настоящий момент функция содержит внутри ссылку на ячейку, в которой записана другая функция или формула - то Шаг с заходом (Step In) выводит в окно вычисления эту функцию(формулу) и активирует ячейку с этой формулой. При этом доступна эта кнопка становится лишь тогда, когда при вычислении основной формулы шаг вычисления доходит до этой самой ссылки на вложенную формулу. Шаг с выходом (Step Out) при этом возвращает к вычислению предыдущей формулы.
Небольшой практический совет: если используете инструмент Вычислить формулу для поиска ошибки в своей формуле для поиска ошибки и в формуле используются слишком большие диапазоны, то просматривать по шагам такую формулу неудобно. Чтобы было проще - можно уменьшить диапазоны ячеек до 10, выделить ячейку с ошибочным результатом и посмотреть этап вычисления - все участвующие ячейки будут на виду и проще будет понять где ошибка.
Конечно, если формулу создал кто-то другой такой подход не всегда справедлив для сложных формул, т.к. изменение диапазонов без понимания для чего они может привести к нерабочей формуле и в этом случае смотреть этапы вычисления бесполезно.
Есть еще одна возможность анализировать этапы вычислений. Необходимо выделить ячейку с нужной формулой, перейти в строку формул и там выделить фрагмент формулы, результат вычисления которого требуется получить:
после чего, не снимая выделения нажимаем клавишу F9. Выделенный блок формулы будет вычислен и результат будет помещен на место выделенного блока формулы:
Мне этот метод нравится меньше, т.к. он не показывает именно шаги вычисления, а вычисляет разом выделенный блок. Поэтому его можно применять в случаях, когда порядок вычисления известен и надо лишь убедиться, что интересующий блок формулы работает правильно.
Табулирование функции представляет собой вычисление значения функции для каждого соответствующего аргумента, заданного с определенным шагом, в четко установленных границах. Эта процедура является инструментом для решения целого ряда задач. С её помощью можно локализовать корни уравнения, найти максимумы и минимумы, решать другие задачи. С помощью программы Excel выполнять табулирование намного проще, чем используя бумагу, ручку и калькулятор. Давайте выясним, как это делается в данном приложении.
Использование табулирования
Создание таблицы
Создаем шапку таблицы с колонками x, в которой будет указано значение аргумента, и f(x), где отобразится соответствующее значение функции. Для примера возьмем функцию f(x)=x^2+2x, хотя для процедуры табулирования может использоваться функция любого вида. Устанавливаем шаг (h) в размере 2. Граница от -10 до 10. Теперь нам нужно заполнить столбец аргументов, придерживаясь шага 2 в заданных границах.
- В первую ячейку столбца «x» вписываем значение «-10». Сразу после этого жмем на кнопку Enter. Это очень важно, так как если вы попытаетесь произвести манипуляцию мышкой, то значение в ячейке превратится в формулу, а в данном случае это не нужно.
Таким образом, табуляция функции была проведена. На её основе мы можем выяснить, например, что минимум функции (0) достигается при значениях аргумента -2 и 0. Максимум функции в границах вариации аргумента от -10 до 10 достигается в точке, соответствующей аргументу 10, и составляет 120.
Построение графика
На основе произведенной табуляции в таблице можно построить график функции.
-
Выделяем все значения в таблице курсором с зажатой левой кнопкой мыши. Перейдем во вкладку «Вставка», в блоке инструментов «Диаграммы» на ленте жмем на кнопку «Графики». Открывается список доступных вариантов оформления графика. Выбираем тот вид, который считаем наиболее подходящим. В нашем случае отлично подойдет, например, простой график.
Далее по желанию пользователь может отредактировать график так, как считает нужным, используя для этих целей инструменты Excel. Можно добавить названия осей координат и графика в целом, убрать или переименовать легенду, удалить линию аргументов, и т.д.
Как видим, табулирование функции, в общем, процесс несложный. Правда, вычисления могут занять довольно большое время. Особенно, если границы аргументов очень широкие, а шаг маленький. Значительно сэкономить время помогут инструменты автозаполнения Excel. Кроме того, в этой же программе на основе полученного результата можно построить график для наглядного представления.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Иногда трудно понять, как вложенная формула вычисляет конечный результат, поскольку в ней выполняется несколько промежуточных вычислений и логических проверок. Но с помощью диалогового окна Вычисление формулы вы можете увидеть, как разные части вложенной формулы вычисляются в заданном порядке. Например, формулу =ЕСЛИ(СПБ(F2:F5)>50;СУММ(G2:G5);0) проще понять, если вы увидите промежуточные результаты:
Шаги, показанные в диалоговом окне
Сначала выводится вложенная формула. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.
=ЕСЛИ(40>50;СУММ(G2:G5);0)
Диапазон ячеек F2:F5 содержит значения 55, 35, 45 и 25, поэтому функция СРЗНАЧ(F2:F5) возвращает результат 40.
=ЕСЛИ(Ложь;СУММ(G2:G5);0)
40 не больше 50, поэтому выражение в первом аргументе функции ЕСЛИ (аргумент logical_test) ложно.
Функция ЕСЛИ возвращает значение третьего аргумента (аргумент значение_если_ложь). Функция СУММ не вычисляется, так как она является вторым аргументом функции ЕСЛИ (value_if_true) и возвращается только в том случае, если выражение истинно.
Выделите ячейку, которую нужно вычислить. За один раз можно вычислить только одну ячейку.
На вкладке Формулы в группе Зависимости формул нажмите кнопку Вычислить формулу.
Примечание: Кнопка Шаг с заходом недоступна для ссылки, если ссылка используется в формуле во второй раз или если формула ссылается на ячейку в отдельной книге.
Продолжайте этот процесс, пока не будут вычислены все части формулы.
Чтобы посмотреть вычисление еще раз, нажмите кнопку Начать сначала.
Чтобы закончить вычисление, нажмите кнопку Закрыть.
Если ссылка пуста, в поле Вычисление отображается нулевое значение (0).
Формулы с циклыми ссылками могут не оцениваться, как ожидалось. При желании можно включить итеративные вычисления.
Следующие функции пересчитываются каждый раз при внесении изменений в ячейку и могут привести к тому, что инструмент "Вычислите формулу" дает результаты, отличаные от результатов в ячейке: СЛЧИС, СМЕДЕН, ЯЧЕЙКА, ДВЕ, СЕГОДНЯ, СЛЧИСЛО, МНИМ.СТ.ЕСЛИ (в некоторых случаях).
Читайте также: