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

Содержание

Почему в Excel вместо числа появляется формула

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

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

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

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

Чтобы узнать, какой формат имеет конкретная ячейка или диапазон, перейдите на вкладку «Главная». На ленте в панели инструментов Number есть поле для отображения текущего макета. Если указан «Текст», формула не будет рассчитана точно.

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

Но выбор типов форматов через ленту не такой широкий, как через специальное окно. Поэтому лучше использовать второй вариант форматирования. Выберите диапазон назначения. Щелкните по нему правой кнопкой мыши. В контекстном меню выберите пункт «Форматировать ячейки». Либо после выбора диапазона нажмите комбинацию клавиш Ctrl + 1.

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

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

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

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

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

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

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

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

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

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

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

Перейдите на вкладку «Файл». Пока вы в нем, вы должны нажать на запись «Параметры».

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

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

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

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

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

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

Выделите ячейку с ошибкой. Перейдите на вкладку «Формулы». На ленте в наборе инструментов «Зависимости формул» нажмите кнопку «Вычислить формулу».

Откроется окно, в котором представлен полный расчет. Нажимаем кнопку «Рассчитать» и шаг за шагом наблюдаем за расчетом. Ищем ошибку и исправляем.

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

Таблицы данных в Excel (в простых шагах)

Таблица данных с одной переменной | Таблица данных с двумя переменными

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

Предположим, вы владеете книжным магазином и храните 100 книг. Вы продаете определенный процент по самой высокой цене в 50 долларов и определенный процент по более низкой цене в 20 долларов. Если вы продаете 60% по самой высокой цене, ячейка D10 ниже рассчитывает общую прибыль 60 * 50 долларов + 40 * 20 долларов = 3800 долларов.

Таблица данных одной переменной

Чтобы создать таблицу данных одной переменной, выполните следующие шаги.

1. Выберите ячейку B12 и введите =D10 (см. ячейку общей прибыли).

2. Введите различные проценты в столбце A.

3. Выберите диапазон A12:B17.

Мы собираемся рассчитать общую прибыль, если вы продаете 60% по самой высокой цене, 70% по самой высокой цене и т. д. .

5. Щелкните Таблица данных.

6. Щелкните в поле «Ячейка ввода столбца» (проценты указаны в столбце) и выберите ячейку C4.

Мы выбираем ячейку C4, потому что проценты относятся к ячейке C4 (% продано по самой высокой цене). Вместе с формулой в ячейке B12 Excel теперь знает, что он должен заменить ячейку C4 на 60% для расчета общей прибыли, заменить ячейку C4 на 70% для расчета общей прибыли и т. д.

Примечание: это единица таблица переменных данных, поэтому мы оставляем ячейку ввода Row пустой.

7. Нажмите OK.

Результат.

Вывод: если вы продаете 60% по самой высокой цене, вы получаете общую прибыль в размере 3800 долларов США, если вы продаете 70% по самой высокой цене, вы получаете общую прибыль в размере 4100 долларов США и т. д.

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

Таблица данных с двумя переменными

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

1. Выберите ячейку A12 и введите =D10 (см. ячейку общей прибыли).

2. Введите различные единицы прибыли (самая высокая цена) в строке 12.

3. Введите различные проценты в столбце A.

4. Выберите диапазон A12:D17.

Мы собираемся рассчитать общую прибыль для различных комбинаций «прибыли за единицу (самая высокая цена)» и «% продаж по самой высокой цене».

5. На вкладке Данные в группе Прогноз нажмите Анализ «что, если».

6. Щелкните Таблица данных.

7. Щелкните в поле «Ячейка ввода строки» (единица прибыли указана в строке) и выберите ячейку D7.

8. Щелкните в поле «Ячейка ввода столбца» (проценты указаны в столбце) и выберите ячейку C4.

Мы выбираем ячейку D7, поскольку единичная прибыль относится к ячейке D7. Мы выбираем ячейку C4, потому что проценты относятся к ячейке C4. Вместе с формулой в ячейке A12 Excel теперь знает, что он должен заменить ячейку D7 на 50 долларов и ячейку C4 на 60% для расчета общей прибыли, заменить ячейку D7 на 50 долларов и ячейку C4 на 70% для расчета общей прибыли и т. д.

9. Нажмите OK.

Результат.

Вывод: если вы продаете 60% по самой высокой цене, при единичной прибыли 50 долларов, вы получаете общую прибыль 3800 долларов, если вы продаете 80% по самой высокой цене, при единичной прибыли 60 долларов, вы получаете общая прибыль $5200 и т. д.

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

Таблица Excel Формула промежуточного итога • Мой центр онлайн-обучения

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

Посмотреть видео

Загрузить рабочую тетрадь

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

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

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

Вы, наверное, уже встречали такую формулу промежуточного итога:

 = СУММ($E$3:E4)

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

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

Например, в приведенной ниже таблице содержится формула промежуточного итога в столбце C до того, как я добавлю новую строку в таблицу (столбец D показывает фактическую формулу в столбце C):

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

Не говоря уже о том, что промежуточный итог теперь возвращает значение, отформатированное как дата. Хм?

Вы можете увидеть это своими глазами на этом анимированном изображении:

Что еще странно, так это то, что если вы удалите строку 14, формула в строке 13 исправится сама!

Таблица Excel.

Вычисление общей формулы

К счастью, есть решение, включающее использование собственных структурированных ссылок таблицы Excel.

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

Моя таблица Excel Формула подсчета суммы выглядит следующим образом:

 =СУММ( ИНДЕКС([Значения],1)  :  [@Значения] )

Мы используем ИНДЕКС, чтобы вернуть первую ячейку в столбце значений, и просто используем структурированную ссылку на текущую строку, чтобы вернуть вторую ячейка в диапазоне, который мы хотим суммировать.

Примечание. [Значения] относятся к ячейкам B2:B13, а [@Значения] относятся к текущей строке столбца B. Узнайте больше о таблицах и структурированных ссылках здесь.

На английском языке формула выглядит следующим образом:

ИНДЕКС столбца Values и возвращает 1 ^st ячейка : Вернуть ссылку на ячейку в текущую строку столбца Values.