Как посмотреть этапы вычисления в excel

Просмотр в EXCEL промежуточного результата вычисления формулы (клавиша F9)

Рассмотрим формулу =(A1+B1)*A2/B2 . Пусть нам потребовалось быстро узнать результат вычисления A2/B2 . Чтобы не терять зря времени на создание новых формул в соседних ячейках, делаем следующее:

• выделяем ячейку с формулой;
• в Строке формул мышкой выделяем A2/B2 (тем самым входим в Режим Правки ячейки);
• нажимаем клавишу F9 ;
• EXCEL заменяет A2/B2 на результат.

Если после отображения результата Вы нажмете клавишу ECS , то EXCEL вернет формулу обратно, если ENTER , то заменит выделенную часть формулы результатом. Особенно начинаешь ценить этот прием при работе с формулами массива .

Рассмотрим другой пример с формулой массива =СУММ((Продажи>=6)*(Продажи<=10)*(Продажи)) Пусть формула введена в ячейку B2 , а диапазону A2:A13 , содержащему некие суммы Продаж, присвоено имя Продажи .В формуле вместо ссылки на обычный диапазон использована ссылка на Именованный диапазон Продажи . Как видно из формулы, она складывает только те значения из исходного списка значений, которые больше или равны шести и меньше или равны 10 .

Проверим, какие значения из исходного диапазона были выбраны для суммирования.

• Выделим ячейку с формулой ( B2 );
• Выделим в Строке формул выражение (Продажи>=6)*(Продажи <=10)*(Продажи) ;
• Нажмем F9 ;
• Получим массив

Действительно, все ненулевые значения лежат в интервале [6;10]

Другим вариантом является использование инструмента Вычислить формулу ( Формулы/ Зависимости формул/ Вычислить формулу ).

Примечание : Нажатие клавиши F9 имеет разный эффект при различных режимах выделения ячейки. Если ячейка просто выделена (в левом нижнем углу окна EXCEL выведена надпись Готово),

то нажатие клавиши F9 приведет к пересчету формул на листе ( Формулы/ Вычисления/ Пересчет ).

Если ячейка находится в Ре жиме Правка (в левом нижнем углу окна EXCEL выведена надпись Правка),

то нажатие клавиши F9 приведет к замене выделенного фрагмента формулы на ее значение. В Режим Правка можно войти дважды кликнув на значение в ячейке или поставив курсор в Строку формул или нажав клавишу F2 .

Как посмотреть этапы вычисления в excel

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

Как просмотреть этапы вычисления формул

Часто ли Вам приходилось разбирать чужой файл с непонятными на первый взгляд формулами? Вроде считают, но как? Вроде и разобраться хочется как работает какая-нибудь мега-формула — но как это сделать? Я хочу рассказать о паре простых шагов, которые необходимо сделать, чтобы разобраться в работе любой формулы. Давайте попробуем разобраться на примере формулы из моей статьи: Как получить список уникальных(не повторяющихся) значений?:
=ИНДЕКС($A$2:$A$51;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0;СТРОКА($A$1:$A$50));1))
Что нам понадобится для начала:

1. Знать что такое формула
2. Знать что такое формула массива
3. Не лениться заглядывать в справку по неизвестной функции. Как это сделать: ставим курсор мыши на интересующую формулу и жмем F1(в Excel 2003 и более ранних версиях только так можно). Начиная с Excel 2007 можно еще и иначе: ставим курсор внутрь функции — появится подсказка по функции. После чего нажимаем на имя функции из подсказки:

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

4. Не обязательно, но желательно скачать файл, приложенный к статье Как получить список уникальных(не повторяющихся) значений?, чтобы наглядно пройти все шаги, описанные ниже
   Скачать пример: Tips_All_ExtractUnique.xls (108,0 KiB, 18 853 скачиваний)

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

Вот теперь можно начать потрошить формулу. В принципе, самый сложный этап уже пройден. Теперь остается только воспользоваться встроенным средством 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 и структура их вычисления

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

Анализ формул в Excel

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

В ячейку B5 введите формулу, которая просто суммирует значения нескольких ячеек (без использования функции СУММ).

Теперь проследим все этапы вычисления и содержимое суммирующей формулы:

1. Перейдите в ячейку B5, в которой содержится формула.
2. Выберите инструмент: «Формулы»-«Зависимости формул»-«Вычислить формулу». Появиться диалоговое окно «Вычисление».
3. В данном окне периодически нажимайте на кнопку «Вычислить», наблюдая за течением расчета в области окна «Вычисление:»

Для анализа следующего инструмента воспользуемся простейшим кредитным калькулятором Excel в качестве примера:

Чтобы узнать, как мы получили результат вычисления ежемесячного платежа, перейдите на ячейку B4 и выберите инструмент: «Формулы»-«Зависимости формул»-«Влияющие ячейки».

Теперь мы наглядно видим, с какими значениями работает формула ежемесячного платежа. Дальше нам нужно узнать, как сформировались эти значения. Для этого перейдите сначала на ячейку с первым значением – С2 и снова выберите инструмент «Влияющие ячейки».

В результате у нас сформировалась графическая схема цепочки вычисления ежемесячного платежа формулами Excel.

Примечание. Чтобы очистить схему нужно выбрать инструмент «Убрать стрелки».

Полезный совет. Если нажать комбинацию клавиш CTRL+«`» (апостроф над клавишей Tab) или выберите инструмент : «Показать формулы». Тогда мы увидим, что для вычисления ежемесячного платежа мы используем 3 формулы в данном калькуляторе. Они находиться в ячейках: B4, C2, D2.

Такой подход тоже существенно помогает проследить цепочку вычислений. Снова перейдите в обычный режим работы, повторно нажав CTRL+«`».

Как убрать формулы в Excel

Теперь рассмотрим, как убрать формулы в Excel, но сохранить значение. Передавая финансовые отчеты фирмы третьим лицам, не всегда хочется показывать способ вычисления результатов. Самым простым решением в данной ситуации – это передача листа, в котором нет формул, а только значения их вычислений.

На листе в ячейках B5 и C2:C5 записанные формулы. Заменим их итоговыми значениями результатов вычислений.

Нажмите комбинацию клавиш CTRL+A (или щелкните в левом верхнем углу на пересечении номеров строк и заголовков столбцов листа), чтобы выделить все содержимое.

Теперь щелкните по выделенному и выберите опцию из контекстного меню «Специальная вставка» (или нажмите комбинацию горячих клавиш CTRL+ALT+V).

В появившемся диалоговом окне выберите опцию «значения» и нажмите кнопку ОК.

В результате лист не содержит только значения данных.