Как просмотреть шаги расчета в Excel

Обновлено: 20.11.2024

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

В целом в Excel используется двухэтапный процесс расчета:

Шаг 1. Создайте деревья зависимостей и пометьте ячейки как невычисленные.
Шаг 2. Определите последовательность вычислений дерева зависимостей, рассчитайте невычисленные формулы в соответствии с последовательностью вычислений и запомните последовательность вычислений для следующего пересчета.

Первый шаг выполняется при каждом вводе или изменении ячейки даже в ручном режиме расчета. Обычно это выполняется так быстро, что вы не замечаете, что это происходит, но если есть заметная задержка в ручном режиме, то в Excel 2007 установка для Workbook.ForceFullCalculation значения true устранит задержку за счет того, что каждое вычисление станет полным.

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

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

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

Зависимости

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

Зависимости в функциях, на которые нет ссылок, могут дать вам неожиданные (неправильные) ответы.

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

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

Понимание этого важно для пользовательских функций, поскольку необходимо убедиться, что все ячейки, используемые функцией, упоминаются в аргументах функции. В противном случае Excel не сможет правильно определить, когда функция должна быть рассчитана и каковы ее зависимости, и вы можете получить неожиданный ответ. Указание Application.Volatile или использование Ctrl/Alt/F9 часто позволяет Excel обойти эту проблему, но вам все равно нужно написать свою функцию для обработки нескольких выполнений за цикл вычислений и невычисленных данных. Более подробное объяснение см. на отдельной странице, посвященной функциям, определяемым пользователем.

Пересылать ссылки

Прямая ссылка относится к ячейке, которая еще не была рассчитана.

Зависимостью может быть либо обратная зависимость/обратная ссылка (относится к ячейке, которая уже окончательно вычислена), либо прямая зависимость/прямая ссылка (относится к ячейка, которая еще не была окончательно вычислена)

Эти зависимости называются:

В зависимостях/ссылках листа, когда ячейка ссылается на ячейку на том же рабочем листе.
Зависимости/ссылки между листами, когда ячейка ссылается на ячейку на другом листе в той же книге.
Зависимости/ссылки между книгами, когда ячейка ссылается на ячейку в другой книге.

Например:
A1=A2+5
A2=A3+1
A3=2

Эти ячейки должны быть окончательно рассчитаны в порядке A3 A2 A1
Ячейка A1 Прямые ссылки ячейка A2 и ячейка A2 Прямые ссылки ячейка A3 .

Последовательность расчета ячеек.

Последовательность расчетов в Excel является динамической и может меняться от расчета к расчету.

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

Некоторые действия, такие как «Расчет диапазона», могут сбросить внутреннюю последовательность вычислений:
Выбор одного прямоугольного диапазона и выполнение вычисления диапазона приведет к сбросу последовательности вычислений всех формул в прямоугольном диапазоне слева направо, сверху вниз в Excel 97 и 2000.Если между ячейками в пределах диапазона есть зависимости, то Excel2002 и 2003 сбросят последовательность вычислений всех формул в прямоугольном диапазоне к последовательности вычисления зависимостей для диапазона.
Если выбрано более одной области, каждая область вычисляется слева направо и сверху вниз в той последовательности, в которой области были выбраны.

Время, необходимое Excel для обработки последовательности вычислений, может быть увеличено за счет интенсивного использования таких функций, как ДВССЫЛ и СМЕЩ, которые увеличивают сложность и изменчивость дерева зависимостей. Сравнение времени, затрачиваемого на расчет диапазона для используемого диапазона на листе, со временем, затраченным на расчет листа сразу после расчета диапазона, даст вам измерение времени, необходимого для определения оптимальной последовательности вычислений для рабочего листа. Если вы выполните вычисление диапазона, за которым следуют два вычисления листа, сравнение второго вычисления листа с первым даст вам измерение изменчивости формул и дерева зависимостей.

Метод вычисления по умолчанию для Excel заключается в вычислении только тех ячеек, которые помечены как невычисленные или зависящие от невычисленной ячейки.
Excel по-прежнему будет использовать свою внутреннюю динамическую последовательность вычислений, но будет вычислять только отмеченные ячейки в последовательности.

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

Второй расчет начинается с использования самой последней последовательности вычислений.

Первоначальная последовательность вычислений по умолчанию для Excel заключается в оценке ячеек в обратной последовательности, в которой формулы были введены/изменены на листе (сначала введены самые последние, или LIFO), а затем изменена эта последовательность в соответствии с требованиями деревьев зависимостей. . Окончательная последовательность вычислений определяется таким образом, чтобы все ячейки в цепочках зависимостей вычислялись в правильном порядке. Для большинства формул и функций Excel этот процесс сортировки ячеек в последовательности цепочек зависимостей выполняется относительно быстро, а последовательность цепочек зависимостей сохраняется после каждого вычисления, поэтому последующие пересчеты выполняются еще быстрее.

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

Для определяемых пользователем функций Excel должен выполнить функцию, чтобы определить, содержит ли она скрытые зависимости в ссылках на ячейки, которых нет в списке аргументов. Обратите внимание, что если вы поместите ложную зависимость в список аргументов (ссылка, которая на самом деле не используется внутри функции), Excel выполнит функцию, но не обязательно в той последовательности, которую вы ожидаете. Кроме того, Excel не распознает зависимость, которая игнорируется оператором IF. Эти факторы могут вызвать проблемы в вашей пользовательской функции, если вы не примете соответствующие меры предосторожности.

Последовательность расчета круговой ссылки

Если ваша книга содержит циклические ссылки и включена итерация (Application.Iteration имеет значение True), Excel должен использовать немного другой алгоритм расчета:

Первый шаг аналогичен перерасчету (или полному расчету, в зависимости от того, как был запущен расчет), который не включает циклические ссылки. Это позволяет правильно вычислить все ячейки, предшествующие циклической ссылке, и идентифицировать список ячеек в циклической ссылке вместе с их зависимостями. На этом первом шаге Application.Iteration имеет значение False.

Второй шаг выполняется один раз на каждой итерации:

Список ячеек в циклической ссылке рассчитывается лист за листом в алфавитной последовательности имен листов (даже в Excel 2002 и 2003). На каждом листе список ячеек и формул массива в циклической ссылке, а также все ячейки, которые от них зависят, вычисляются слева направо и сверху вниз. (все столбцы в первой строке слева направо, затем в следующей строке. ). Каждая формула массива в списке вычисляется как единый блок ячеек слева направо и сверху вниз, поэтому, если вы хотите использовать расчет по столбцам, а не по строкам, вам нужно использовать формулу массива столбцов с несколькими ячейками.
Этот расчет игнорирует дерево зависимостей и использует итеративный процесс расчета для разрешения зависимостей.
Изменчивые ячейки, которые не зависят от ячеек в круговом списке, НЕ пересчитываются при каждой итерации.

Этот шаг повторяется до тех пор, пока не будут достигнуты пределы итерации (максимальное количество итераций и максимальное изменение ячейки за итерацию). Во время этого второго шага Application.Iteration имеет значение True.

Проверка данных и автоматический расчет

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

Эксперименты показывают, что эта последовательность зависит от того, как вы выходите из ячейки после изменения данных.
Нажатие другой ячейки приводит к выполнению проверки данных перед пересчетом.
Использование Tab, клавиш со стрелками или Enter для выхода из ячейки приводит к тому, что проверка данных выполняется после вычисления.

Загрузите книгу, демонстрирующую этот эффект (с обходом проблемы), здесь.
Спасибо Джерри Лэтэму за то, что он сообщил мне об этом странном эффекте и предложил обходной путь.

Документация Microsoft по расчетам

Компания Microsoft недавно опубликовала некоторые дополнительные сведения об алгоритме вычислений Excel и изменениях, внесенных в Excel 2002.

Последовательность расчета рабочей книги и рабочего листа.

Excel 97 и 2000 вычисляют рабочие листы в алфавитной последовательности имен. Для каждого листа ведется отдельный список последовательности вычислений.

Excel97 и Excel2000 вычисляют рабочие книги и рабочие листы в каждой рабочей книге в алфавитной последовательности имен:

«Book1» вычисляется перед «Book2».
В рабочей книге «Балансовый отчет» рассчитывается перед «Денежным потоком», который рассчитывается перед «Убытком от прибыли», независимо от видимого порядка вкладок рабочего листа в рабочей книге.

Для каждого листа разрабатывается отдельная последовательность расчета цепочки зависимостей вместе с флагом расчета для каждого листа и каждой книги. Значительного увеличения скорости вычислений часто можно добиться, определив оптимальную последовательность именования рабочих листов. См. команду FastExcel V4 CrossRefs.

Excel2002/Excel2003 также вычисляет книги и листы в алфавитном порядке, но создает единую глобальную последовательность цепочки зависимостей для всех открытых книг. Для сложных книг, содержащих много прямых ссылок на рабочие листы и/или циклических ссылок на рабочие листы, использование Excel 2002 может значительно ускорить время пересчета.

Excel95 и Excel5 рассчитывают рабочие листы в той последовательности, в которой они определены.
(Спасибо Робу Бови за этот совет).

Многопоточный расчет

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

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

Тестирование ряда рабочих книг с формулами от 840 000 до 23 000 с использованием Excel 2007 на двухъядерной системе показало, что коэффициенты улучшения при использовании MTC варьируются от 1,9 до нулевого улучшения, причем более крупные книги, как правило, демонстрируют наибольшее улучшение.< /p>

В сотрудничестве с корпорацией Intel Microsoft провела тестирование набора электронных таблиц, созданных пользователями. Сравнение проводилось между Excel 2007 и Excel 2003. (Использовалась предварительная версия Excel 2007, но в окончательной версии практически не ожидается никакой разницы.)

Результаты показали, что время расчета варьируется от нулевого улучшения до более чем теоретического (2x/4x) улучшения как для двухъядерных, так и для четырехпроцессорных систем. Типичное (медианное) улучшение для системы с двухъядерным процессором Intel Pentium 4 с тактовой частотой 3,0 ГГц и 1 ГБ ОЗУ по сравнению с тем же файлом, вычисляемым в Excel 2003, составило 48 процентов, или ускорение в 1,92 раза. Типичное (медианное) ускорение для системы с четырехъядерным процессором Intel Xeon с тактовой частотой 3,0 ГГц и 4 ГБ ОЗУ составило 76 процентов, или ускорение в 4,17 раза. Аналогичные улучшения скорости наблюдались и на других процессорах и платформах. Улучшения помимо теоретического ускорения (из-за многопоточности) связаны с другими улучшениями производительности в Excel 2007, такими как повышение скорости выполнения функций.

Некоторые функции Excel не используют многопоточные вычисления, например:

Вычисление таблицы данных (но структурированные ссылки на таблицы используют MTC).
Определяемые пользователем функции (но функции XLL могут поддерживать многопоточность).
Функции XLM.
ДВССЫЛ, ЯЧЕЙКА, которые используют параметры формата2 или адреса.
GETPIVOTDATA и другие функции, относящиеся к сводным таблицам или кубам.
Range.Calculate и Range.CalculateRowMajorOrder.
Ячейки в циклических ссылках.

Когда Excel впервые вычисляет книгу на компьютере с несколькими процессорами, у вас возникают дополнительные затраты, пока Excel проверяет зависимости. Таким образом, вы можете увидеть максимальное увеличение производительности при втором и последующих вычислениях (хотя обычно все же есть улучшение при первом вычислении по сравнению с выполнением той же задачи на той же скорости компьютера с одним процессором).

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

Управление количеством потоков вычислений

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

Чтобы управлять параметрами MTC, нажмите кнопку Microsoft Office, выберите «Параметры Excel», выберите «Дополнительно», а затем выберите «Формулы».

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

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

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

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

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

1. Показать параметр «Формулы» на ленте Excel

В листе Excel перейдите на вкладку Формулы > группу Аудит формул и нажмите кнопку Показать формулы.

Microsoft Excel сразу отображает формулы в ячейках вместо их результатов. Чтобы вернуть рассчитанные значения, снова нажмите кнопку Показать формулы, чтобы отключить ее.

2. Опция "Показывать формулы в ячейках вместо их результатов"

В Excel 2010, Excel 2013 и Excel 2016 выберите Файл > Параметры. В Excel 2007 нажмите Кнопка Office > Параметры Excel.

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

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

3. Ярлык Excel для отображения формул

Самый быстрый способ просмотреть каждую формулу в электронной таблице Excel — нажать сочетание клавиш Ctrl + `

Клавиша серьезного ударения (`) — это крайняя слева клавиша в ряду с цифровыми клавишами (рядом с клавишей с цифрой 1).

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

Примечание. Какой бы из вышеперечисленных методов вы ни использовали, Microsoft Excel покажет все формулы текущего рабочего листа. Чтобы отобразить формулы на других листах и книгах, вам нужно будет повторить процесс для каждого листа отдельно.

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

Совет. Если вы щелкнете ячейку с формулой, но формула не появится в строке формул, скорее всего, эта формула скрыта, а рабочий лист защищен. Вот шаги, чтобы отобразить формулы и снять защиту листа.

Как печатать формулы в Excel

Если вы хотите напечатать формулы в электронной таблице Excel вместо того, чтобы печатать результаты вычислений этих формул, просто используйте любой из 3 методов, чтобы отобразить формулы в ячейках, а затем распечатайте рабочий лист, как вы обычно печатаете файлы Excel (< em>Файл > Печать). Вот и все!

Почему Excel показывает формулу, а не результат?

Случалось ли с вами когда-нибудь, что вы вводите формулу в ячейку, нажимаете клавишу Enter... и Excel по-прежнему показывает формулу вместо результата? Не беспокойтесь, с вашим Excel все в порядке, и мы скоро исправим эту ошибку.

Как правило, Microsoft Excel может отображать формулы вместо вычисляемых значений по следующим причинам:

Возможно, вы случайно активировали режим «Показать формулы», нажав соответствующую кнопку на ленте или нажав сочетание клавиш CTRL+`. Чтобы вернуть результаты вычислений, просто выключите кнопку Показать формулы или снова нажмите CTRL+`.
Возможно, вы случайно ввели пробел или одинарную кавычку (') перед знаком равенства в формуле:

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

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

Вас также может заинтересовать

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

Используется лист Excel 1 с расчетом базовой формулы с каждой ячейкой + и - используется почти весь лист, при этом согласовывается формула листа. но не смог идентифицировать ячейки ошибок формулы.

Пожалуйста, сообщите, как проверить формулу для каждой ячейки, и дисперсия должна выделяться

Привет,
Если в ячейках A1=1, A2=13, то формула в ячейке A3=A1+A2, поэтому результат =1+13=14. Это нормально.
Скажем, я хочу, чтобы ячейка A4 отображала формулу (не ответ) в ячейке A3, возможно ли это?
(это означает, что в ячейке A4 будет отображаться =A1+A2, что на самом деле является формулой в ячейке A3)
И если я изменю формулу в ячейке A3, тогда A4 покажет мне формулу (или текст), которую я использую теперь и на A3.

Здравствуйте!
Я рекомендую использовать функцию FORMULATEXT. Он вытягивает формулу из нужной ячейки и записывает ее в виде текста.
Например,

Мне нужен метод, которым мы выводим формулы с результатами в excel.

если какая-либо возможность поможет мне.

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

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

Проблема с файлом Excel. У одного из моих конечных пользователей есть странный пользователь с его файлом Excel. в своем файле он использует формулу и другие вещи для работы с числами, а в ячейке он заметил, что когда он нажимает f2, чтобы увидеть формулу, она есть, но как только он нажимает ESC, ячейка пуста, и правильный результат не отображается но когда он выделяет ту же ячейку желтым или другим цветом, он может видеть правильное значение. Я подтвердил, что в ячейке нет скрытого цвета. Мой конечный пользователь использовал ввод для перехода в другую ячейку, и это отлично работает. Мне нужно знать проблему, когда мой конечный пользователь нажимает клавишу esc, чтобы увидеть правильное значение в ячейке, использующей формулу.

Как только вы введете формулу в Excel и нажмете Enter, она вернет вычисленный результат, а формула исчезнет.

Вот как это должно работать.

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

В этом руководстве по Excel я рассмотрю следующие темы:

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

Показывать формулы в Excel вместо значений

Вот шаги, чтобы показать формулы в Excel вместо значения:

Как только вы нажмете «Показать формулы», формулы на листе станут видимыми. Это кнопка-переключатель, поэтому вы можете щелкнуть ее еще раз, чтобы формулы были заменены ее расчетным результатом.

Это кнопка-переключатель, поэтому вы можете щелкнуть ее еще раз, чтобы формулы были заменены ее расчетным результатом.

Как показано ниже, столбец I содержит формулы. Как только нажата кнопка «Показать формулы», в ячейках вместо значения отображаются формулы.

Вы также можете использовать сочетание клавиш Excel – Control + ` (эта клавиша находится в верхней левой части клавиатуры под клавишей Escape).

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

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

Вот шаги, которые отобразят формулы на всех листах в Excel:

Как уже упоминалось, хотя может показаться, что здесь требуется больше шагов по сравнению с ярлыком или кнопкой «Показать формулы» на ленте, это полезно, когда у вас есть несколько листов и вы хотите отобразить формулы на всех этих листах.< /p>

Как печатать формулы в Excel

Вот шаги для печати формул в Excel:

Перейдите на вкладку "Формула".
Нажмите кнопку "Показать формулу".
Выберите «Файл» –> «Печать».

Вышеуказанные шаги гарантируют, что будут напечатаны формулы, а не значения.

Показывать формулы в Excel вместо значения только в выбранных ячейках

Вышеупомянутые методы, рассмотренные до сих пор, будут отображать все формулы на листе.

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

Например, как инструктор по Excel, я часто создаю шаблоны, в которых я показываю формулу в одной ячейке, а ее результат — в другой (как показано ниже).

Вот шаги, чтобы показать формулы в Excel только в выбранных ячейках:

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

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

Как работать с Excel, показывающим формулы вместо вычисляемых значений

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

Есть несколько причин, по которым это может произойти:

Включен режим «Показать формулы», или вы случайно нажали сочетание клавиш Control + `. Чтобы отключить его, просто снова используйте ярлык или нажмите «Показать формулу» на вкладке «Формулы».
Это может быть связано с наличием пробела или апострофа перед знаком равенства в формуле. Наличие их перед знаком равенства делает формат ячейки текстовым, и формула отображается вместо значения. Чтобы справиться с этим, просто удалите их. Для этого можно использовать поиск и замену.
Если к ячейке применено форматирование «Текст», а вы введете формулу и нажмете клавишу ввода, в ней по-прежнему будет отображаться формула вместо вычисленного значения. Чтобы устранить эту проблему, перейдите на вкладку "Главная" и в группе "Число" измените форматирование на "Общее".

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

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

Это тот же рабочий лист, который мы рассматривали в предыдущем видео, когда говорили об отладке формул с помощью клавиши F9.Столбец «Возраст» содержит формулу, вычисляющую возраст по дате рождения, а столбец «Статус» использует функцию ЕСЛИ для обозначения каждого человека в списке как взрослого или несовершеннолетнего.

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

Оценить формулу можно найти на вкладке "Формулы" ленты в группе "Аудит формул".

Чтобы использовать формулу оценки, выберите формулу и нажмите кнопку на ленте.

Когда откроется окно, вы увидите формулу, отображаемую в текстовом поле, с кнопкой "Оценить" под ней.

Одна часть формулы будет подчеркнута — это часть, которая в настоящее время находится в стадии оценки. В этом случае решение функции СЕГОДНЯ является первым шагом в решении этой формулы.

Когда мы нажимаем «Оценить», функция СЕГОДНЯ оценивается и возвращает дату в формате серийного номера Excel, а функция ДОЛЯ ГОДА подчеркивается в качестве следующего шага в процессе оценки.

При следующем щелчке вычисляется функция ДОЛЯ ГОДА, а функция ЦЕЛОЕ подчёркивается.

Последний щелчок, и формула будет решена с результатом 12.

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

Теперь давайте оценим формулу статуса.

В этом случае сначала решается E5, чтобы получить возраст, который используется в логическом тесте в операторе IF. 12 меньше 20, поэтому FALSE является результатом логического теста. «Незначительный» — это окончательный результат, поскольку «Незначительный» задается в качестве значения, которое следует использовать, если оно ложно.

Давайте перезапустим оценку и посмотрим на функции "Вход" и "Выход".

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

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

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

Итак, вот оно. Evaluate Formula — удобное дополнение к использованию клавиши F9. И это отличный способ понять, как Excel решает формулы.

Читайте также: