Excel формула на: Обзор формул

Замена результата формулы на значение в Excel — TutorExcel.Ru

Разберем как быстро сделать и получить результат формулы в виде значения (в любом: текстовом, числовом и прочих вариантах) в Excel.

Приветствую всех, уважаемые читатели блога TutorExcel.Ru!

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

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

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

Давайте перейдем к примеру.

Перед нами небольшая таблица и формула состоящая из нескольких подформул.

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

Разберем 2 основных инструмента позволяющих более детально разобраться в вычислении формулы.

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

Пошаговое вычисление формулы

В Excel есть стандартный инструмент пошагового вычисления формулы.

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

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

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

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

Поэтому перейдем к еще одному способу детализации формулы.

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

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

В этом случае нам сможет помочь другой прием.

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

Вот и все 🙂

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

Два важных момента.

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

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

Спасибо за внимание!
Если у вас есть вопросы и мысли по теме статьи — делитесь ими в комментариях.

Удачи вам и до скорых встреч на страницах блога TutorExcel.Ru!

Поделиться с друзьями:

Поиск по сайту:

Формулы

Хитрости работы с Excel – таблицы, функции, формулы, списки, рабочие книги, макросы

Ввод формул

Введенное в ячейку значение распознается как формула тогда, когда оно начинается со знака равенства «=», а также со знака плюс «+» или минус «—». Если в ячейку введена формула, то изображенное в ней значение будет представлять собой результат выполнения каких-либо действий над значениями других ячеек. Например, в ячейке с формулой может содержаться сумма чисел из других заранее заданных ячеек. При этом в формуле указываются не числа, содержащиеся в этих ячейках, а адреса ячеек.

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

Причем изменять в ней ничего не надо. Именно формулы являются одной из тех «изюминок», ради которых и стоит использовать Excel. В отличие от обычных таблиц, в электронных таблицах вычисляемые значения не требуется считать и заполнять вручную, они определяются (вычисляются) и выводятся в ячейках автоматически. Простейшими действиями в формулах являются арифметические действия над числами, содержащимися в ячейках: сложение + вычитание умножение деление / возведение в степень Для того, чтобы, например, в какой-либо ячейке было показано число, равное сумме числа 100, и чисел, записанных в ячейки А2, В2, С2 и F3, необходимо ввести в нее формулу: = 100+A2+B2 + C2+F3. Порядок выполнения арифметических действий в формуле соответствует принятым в математике правилам: сначала умножение и деление, затем сложение и вычитание.

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

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

— щелкнуть мышью по ячейке, адрес которой необходимо ввести;

— клавишами перемещения перевести табличный курсор (он становится пунктирным) на ячейку, адрес которой необходимо ввести. Знак равенства и знаки арифметических действий в этом случае по-прежнему необходимо набирать клавиатурой. То есть, например, для того, чтобы в ячейку ввести формулу =А2 + В2, следует набрать знак =, щелкнуть по ячейке А2, набрать знак +, щелкнуть по ячейке В2, а затем завершить ввод, например, нажав Enter.

Такой способ уменьшает вероятность ошибки в формуле и делает ее ввод в ячейку более удобным. Если выбран переключатель автоматически, то пересчет по любой формуле выполняется сразу же после изменения данных в одной из ячеек, адреса которых входят в формулу. При выборе переключателя вручную пересчет по формулам выполняется, только если нажать F9, Ctrl+= или кнопку Вычислить (F9) на вкладке Вычисления. В результате одного из этих действий пересчет будет выполнен в формулах, содержащихся во всех таблицах всех открытых в данный момент книг.

Для того, чтобы произвести пересчет только в текущей таблице (расположенной на текущем листе), следует нажать Shift+F9 или кнопку Пересчет листа на вкладке Вычисления. — необходимости ручного пересчета будет напоминать надпись Вычислить в строке состояния, которая появится после внесения первого изменения и исчезнет после пересчета. Если при выборе переключателя вручную включить переключатель пересчет перед сохранением, то перед сохранением до 5 Готово Вычислить документа автоматически будет выполняться пересчет формул всей книги. Замена формул на вычисленные значения Если, перейдя в режим редактирования формулы, нажать F9, то формула, содержащаяся в ячейке, будет заменена на значение, вычисленное по ней.

Например, если в ячейке записана формула =А2+В2, а в ячейках А2 и В2 содержатся числа 2, то после выполнения описанного действия вместо формулы =А2+В2 в данную ячейку будет записано простое числовое значение 4, которое уже не будет зависеть от содержимого ячеек А2 и В2. Для того, чтобы произвести такую же замену сразу в нескольких ячейках, необходимо выделить эти ячейки (п. 6.1), выполнить копирование в буфер обмена и выполнить команду Специальная вставка… (Правка). Затем в группе Вставить появившегося диалогового окна следует выбрать переключатель значение и нажать кнопку ОК.

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

Запустите Excel. На чистом листе в ячейку А1 введите слово Текст.

Убедитесь, что введенный текст выровнен по левой границе ячейки. В ячейку А2 введите Текст в ячейке А2 имеет большую длину. Убедитесь, что текст, введенный в ячейку А2, занял соседние ячейки строки 2.

Установите табличный курсор на ячейку В2 и введите в нее Перекрытие текста. Установите табличный курсор на ячейку А2. Убедитесь, что текст, введенный в ячейку А2, виден не полностью (он перекрыт значением ячейки В2), однако содержимое этой ячейки осталось прежним.

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

Перейдите на ячейку A3, наберите слово Ввод, нажмите Alt+Enter, наберите в три, еще раз нажмите Alt+Enter, наберите отроки и нажмите Enter. Убедитесь, что текст в ячейке A3 размещен в три строки.

Пример. Ввод числовых значений

Перейдите на чистый лист.

В ячейки А1 введите положительное число 261. Убедитесь, что введенное число выровнено по правой границе ячейки.

В ячейку А2 и В2 введите отрицательные числа -266 и -267, соответственно. Убедитесь, что в обеих этих ячейках записано отрицательное число.

В ячейку A3 введите дробное число 15/9. Установите табличный курсор на ячейку A3 и убедитесь, что реально в эту ячейку записано десятичное число, соответствующее введенному дробному числу.

Выполните команду Параметры… (Сервис). В появившемся диалоговом окне на вкладке Правка включите переключатель Фиксированный десятичный формат при вводе.

Затем в поле десятичных разрядов введите число 2 и нажмите кнопку ОК.

В ячейки строки 4 введите следующие значения: 89934, 1258, 211 и 56. Убедитесь, что в ячейках введены десятичные числа с двумя знаками после десятичного разделителя, а именно: 899.34, 12.58, 2.11 и 0.56. Вновь вызовите диалоговое окно Параметры и выключите режим фиксированного десятичного разделителя.

Установите табличный курсор на ячейку А5 и нажмите комбинацию Ctrl+;. Убедитесь, что в ячейку введена текущая дата. Установите табличный курсор на ячейку А6 и нажмите комбинацию Ctrl+Shift+;.

Убедитесь, что в ячейку введено текущее время. В ячейки В5 и В6 еще раз введите текущую дату и текущее время, соответственно, но используйте при этом обычный набор клавиатурой.

В ячейку А7 введите значение 0099, а в ячейку В7 — значение ‘0099 (с апострофом). Убедитесь, в ячейке А7 введено числовое значение 99, причем выровнено оно по правому краю ячейки, а в ячейке В7 — текстовое значение 0099 и выровнено оно по левой границе.

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

Пример. Ввод формул

Откройте документ Первая книга, перейдите на лист Лист1. Введите в ячейки А2, В2, С2 и ЕЗ числа 31, 246, 55 и 42, соответственно.

В ячейку А4 с использованием клавиатуры введите формулу =100+A2+В2+С2+ЕЗ. Запустите программу Калькулятор и убедитесь, что результат вычисления по срормуле верный.

В ячейку А5 введите ту же формулу, что и в ячейку А4, используя при этом следующий способ: введите знак равенства и первое слагаемое =100+, щелкните мышкой по ячейке А2, введите знак сложения +, щелкните по ячейке В2, введите знак сложения +, щелкните по ячейке С2, введите знак сложения +, щелкните по ячейке ЕЗ, нажмите Enter.

Измените значение в ячейке В2. Убедитесь, что значение в ячейках А4 и А5 автоматически пересчитались. Выполните команду Параметры…

(Сервис), в появившемся диалоговом окне на вкладке Вычисления выберите переключатель вручную и нажмите кнопку ОК. Измените значение в ячейке В2. Убедитесь, что значения в ячейках А4 и А5 не изменились, а в строке состояния появилась надпись Вычислить.  Нажмите клавишу F9. Убедитесь, что значение в ячейках А4 и А5 пересчитались. Верните режим автоматического пересчета формул.

Установите табличный курсор на ячейку А4, нажмите последовательно F2 (произойдет вход в режим редактирования ячейки), F9, а затем Enter. Убедитесь, что в ячейке А4 формула заменена на вычисленное по ней значение. Сохраните документ Первая книга.

Newer news items:

• 13/12/2010 17:47 — Excel формулы
• 13/12/2010 17:44 — Функции Excel
• 13/12/2010 16:45 — Расчеты в excel
• 08/07/2010 06:47 — Техника ввода данных Microsoft Excel
• 08/06/2010 06:06 — Работа с формулами в Excel

Older news items:

• 23/11/2007 14:24 — Использование констант и функций в именах
• 21/11/2007 05:56 — Расчет множественных результатов
• 19/11/2007 10:27 — Создание функций при помощи библиотеки
• 17/11/2007 15:12 — Циклические ссылки
• 16/11/2007 22:48 — Исправление ошибок в расчетах

Next page >>

Оператор неявного пересечения: @

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

Обновленный язык формул

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

Что такое неявное пересечение?

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

• Если значение представляет собой один элемент, вернуть элемент.

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

• Если значение является массивом, выберите левое верхнее значение.

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

Почему символ @?

Символ @ уже используется в ссылках на таблицы для обозначения неявного пересечения. Рассмотрим следующую формулу в таблице =[@Column1]. Здесь @ указывает, что формула должна использовать неявное пересечение для извлечения значения в той же строке из [Column1].

Можете ли вы удалить @?

Часто можно. Это зависит от того, что возвращает часть формулы справа от @: 

• Если он возвращает одно значение (наиболее распространенный случай), удаление @ ничего не изменит.

• Если он возвращает диапазон или массив, удаление @ приведет к тому, что он распространится на соседние ячейки.

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

Когда мы добавляем @ к старым формулам?

Вообще говоря, функции, которые возвращают диапазоны или массивы из нескольких ячеек, будут иметь префикс @, если они были созданы в более старой версии Excel. Важно отметить, что поведение вашей формулы не изменилось — теперь вы можете просто увидеть невидимое ранее неявное пересечение. Общие функции, которые могут возвращать диапазоны из нескольких ячеек, включают ИНДЕКС, СМЕЩЕНИЕ и пользовательские функции (UDF). Обычное исключение — если они заключены в функцию, которая принимает массив или диапазон (например, СУММ() или СРЗНАЧ()).

Дополнительные сведения см. в разделе Функции Excel, возвращающие диапазоны или массивы.

Примеры

Использование оператора @ в новых формулах

Если вы создаете или редактируете формулу в динамическом массиве Excel, содержащую оператор @, она может отображаться как _xlfn. SINGLE() в предварительно динамическом массиве Excel.

Это происходит, когда вы фиксируете смешанную формулу. Смешанная формула — это формула, основанная как на вычислении массива, так и на неявном пересечении, это не поддерживалось в Excel до динамического массива. Предварительно динамический массив поддерживал только формулы, которые делали i ) неявное пересечение или ii ) вычисление массива повсюду.

Когда динамический массив включен, Excel обнаруживает создание «смешанной формулы», он предлагает вариант формулы, который неявно пересекается повсюду. Например, если вы введете =A1:A10+@A1:A10, вы увидите следующий диалог:

Если вы решили отклонить формулу, предложенную в диалоговом окне, будет принята смешанная формула =A1:A10+@A1:A10. Если позже вы откроете эту формулу в предварительно динамическом массиве Excel, она будет отображаться как =A1:A10+_xlfn.SINGLE(A1:A10) с символами @ в смешанной формуле, отображаемыми как _xlfn. SINGLE(). Когда эта формула оценивается предварительным динамическим массивом Excel , она возвращает #NAME! значение ошибки.

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

См. также

ФИЛЬТР

Функция СЛУЧАЙ

ПОСЛЕДОВАТЕЛЬНОСТЬ

Функция СОРТИРОВКИ

СОРТИРОВАТЬ функцию

УНИКАЛЬНАЯ функция

#ПРОЛИВАТЬ! ошибки в экселе

Динамические массивы и поведение разлитого массива

Различные способы отображения формул в Excel

1. Информационный бюллетень
2. ДОПОЛНИТЕЛЬНЫЙ КРЕДИТ

Венди Титц, дипломированный бухгалтер, доктор философии; Дженнифер Кайнас, CPA, DBA; и Трейси Миллер-Ноблс, CPA

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

Отображение формул с помощью команды «Показать формулы»

Чтобы отобразить формулы в Excel, вы можете щелкнуть вкладку Формулы и затем нажать Показать формулы .

Это покажет все формулы на листе Excel.

Включение и выключение формул

Вы также можете использовать сочетание клавиш для включения и выключения формул. На клавиатуре, набрав комбинацию клавиш Ctrl+`, вы включите (или отобразите) формулы. (Обратите внимание, что ` в этом ярлыке — это знак серьезного ударения, обычно расположенный слева от клавиши 1 на клавиатуре.) Повторный ввод сочетания клавиш Ctrl+` отключит формулы.

Отображение формул с помощью функции FORMULATEXT

Иногда вам может понадобиться, чтобы формула отображалась в виде текстовой строки рядом с формулой. Это может быть особенно полезно, когда вы хотите увидеть и результат формулы, и формулу на одном листе, или при представлении формул учащимся. В этих случаях вы можете использовать функцию ФОРМУЛАТЕКСТ.

Функция FORMULATEXT возвращает формулу в виде текстовой строки, как показано на снимке экрана ниже. В этом примере, если вы хотите отобразить формулу ячейки D3 в ячейке F3, вы должны ввести следующую формулу: =ФОРМУЛАТЕКСТ(D3).

Есть несколько важных моментов, о которых следует помнить при использовании FORMULATEXT. Во-первых, обратите внимание, что вы можете использовать его для ссылки на ячейку на другом листе или в книге, но книга должна быть открыта. В противном случае Excel вернет значение ошибки #Н/Д.

Excel также вернет значение ошибки #Н/Д, если вы используете ФОРМУЛАТЕКСТ в ячейке, не содержащей формулы, или если формула не может быть отображена из-за защиты рабочего листа.

— Венди Титц, CPA, CGMA, Ph.D., 9 лет0008 — профессор бухгалтерского учета в Кентском государственном университете в Кенте.