Ms excel формулы: Обзор формул - Служба поддержки Майкрософт

Содержание

формулы, стандартные функции. Построение графиков.

Создание и удаление формулы

Скрыть все

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

С помощью констант (Константа. Постоянное (не вычисляемое) значение. Например, число 210 и текст «Квартальная премия» являются константами. Формула и результат вычисления формулы константами не являются.) и операторов (Оператор. Знак или символ, задающий тип вычисления в выражении. Существуют математические, логические операторы, операторы сравнения и ссылок.) вычисления можно создать простую формулу. Например, формула =5+2*3 позволяет умножить два числа, а затем прибавить число к результату. В Microsoft Office Excel используется обычный порядок математических операций.

В предыдущем примере операция умножения (2*3) выполняется в первую очередь, а затем к результату умножения прибавляется число 5.

Также формулу можно создать с помощью функции (Функция. Стандартная формула, которая возвращает результат выполнения определенных действий над значениями, выступающими в качестве аргументов. Функции позволяют упростить формулы в ячейках листа, особенно, если они длинные или сложные.). Например, в формулах =СУММ(A1:A2) и СУММ(A1,A2) для сложения значений в ячейках A1 и A2 используется функция СУММ.

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

Функции. Любая функция, например ПИ(), всегда начинается со знака равенства (=). В круглые скобки вводятся аргументы (Аргумент. Значения, используемые функцией для выполнения операций или вычислений. Тип аргумента, используемого функцией, зависит от конкретной функции.

(крышка) возводит число в степень, а оператор * (звездочка) перемножает числа

Для достижения желаемого результата вычисления можно использовать одну функцию, вложенные функции или массивы (Массив. Объект, используемый для получения нескольких значений в результате вычисления одной формулы или для работы с набором аргументов, расположенных в различных ячейках и сгруппированных по строкам или столбцам. Диапазон массива использует общую формулу; константа массива представляет собой группу констант, используемых в качестве аргументов.), которые позволяют получать один или несколько результатов. Если формула не нужна, ее можно удалить.

Создание простой формулы с константами и операторами

Щелкните ячейку, в которую требуется ввести формулу.
Введите = (знак равенства).

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

Введите константы и операторы, которые необходимо использовать для вычисления. 2
Возведение 5 в квадрат

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

Пример формулы	Описание
=A1+A2	Сложение значений в ячейках A1 и A2
=A1-A2	Вычитание значения в ячейке A2 из значения в ячейке A1
=A1/A2	Деление значения в ячейке A1 на значение в ячейке A2
=A1*A2	Умножение значения в ячейке A1 на значение в ячейке A2
=A1^A2	Возведение значения в ячейке A1 в степень, заданную значением в ячейке A2

Совет. Можно указать столько констант и операторов, сколько необходимо для достижения нужного результата вычисления.
Нажмите клавишу ВВОД.

Создание формулы со ссылками на ячейки и именами

Примеры формул, приведенные в конце этого раздела, содержат относительные ссылки (Относительная ссылка. Адрес ячейки в формуле, определяемый на основе расположения этой ячейки относительно ячейки, содержащей ссылку. При копировании ячейки относительная ссылка автоматически изменяется. Относительные ссылки задаются в форме A1.) на другие ячейки и их имена (Имя. Слово или строка знаков, представляющие ячейку, диапазон ячеек, формулу или константу. Понятные имена, такие как «Продукты», используют для ссылок на диапазоны, названия которых трудно запомнить, например, Продажи!C20:C30.). Ячейка, содержащая формулу, называется зависимой, если ее значение зависит от значений в других ячейках. Например, ячейка B2 является зависимой, если она содержит формулу =C2.

Щелкните ячейку, в которую требуется ввести формулу.
В строку формул (Строка формул. Панель в верхней части окна Microsoft Excel, которая используется для ввода или изменения значений или формул в ячейках или на диаграммах. На ней отображается константа или формула, содержащаяся в активной ячейке.) введите = (знак равенства).
Выполните одно из указанных ниже действий.
- Чтобы создать ссылку, выделите ячейку, диапазон ячеек, место в другом листе или место в другой книге. Этот способ называется псевдовыбор. Можно переместить границу выделения, перетащив границу ячейки, или перетащить угол границы, чтобы расширить выделение.

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

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

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

Для ввода ссылки на именованный диапазон, нажмите клавишу F3, выберите имя в поле Имя и нажмите кнопку ОК.

Пример формулы	Описание
=C2	Использует значение в ячейке C2
=Лист2!B2	Использует значение в ячейке B2 на Лист2
=Ответственность-Актив	Вычитает значение в ячейке «Ответственность» из значения в ячейке «Актив»

Как правильно вводить, редактировать и копировать формулы в таблицах Excel — Трюки и приемы в Microsoft Excel

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

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

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

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

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

Для определенности рассмотрим пример вычисления суммы первых 10 натуральных чисел двумя основными способами. Для этого на отдельном рабочем листе предварительно в ячейки А1:А10 с использованием второго способа автозаполнения следует ввести натуральные числа от 1 до 10. После этого необходимо выполнить следующую последовательность действий.

Способ задания формулы №1

Выделить вычислимую ячейку, в которую предполагается ввести некоторую формулу. В рассматриваемом примере пусть это будет ячейка А11.
Нажать кнопку Вставка функции, расположенную в строке ввода и редактирования формул, или выполнить операцию главного меню: Формулы → Вставить функцию, в результате чего будет открыто диалоговое окно мастера функций (рис. 1).
Выбрать необходимую функцию из предлагаемого списка и нажать кнопку ОК. Внешний вид окна мастера функций изменится (рис. 3). В отдельных нолях ввода появившегося окна следует выбрать ячейки, в которых содержатся данные, являющиеся аргументами выбранной функции.

Рис. 1. Задание формулы с помощью мастера функций (шаг 1 из 2)

При необходимости можно выполнить поиск нужной функции или выбрать категорию функций из вложенного списка. Применительно к рассматриваемому примеру требуемая функция присутствует в списке с именем Выберите функцию. Такой является функция с именем СУММ, которая стоит первой в списке. Дополнительно можно получить справку по выделенной функции, нажав на соответствующей ссылке в нижней части окна. В результате появляется дополнительное окно с информацией о данной функции с примерами правильной записи ее аргументов (рис. 2).

Рис. 2. Окно со справочной информацией по функции СУММ

Рис. 3. Задание аргументов формулы с помощью мастера функций (шаг 2 из 2)

Применительно к рассматриваемому примеру можно согласиться с диапазоном аргументов, предлагаемым программой MS Excel по умолчанию, поскольку аргументами функции суммирования СУММ является диапазон ячеек А1:А10. В этом же окне сразу отображается результат суммирования — число 55. Вводимая формула отображается также в строке ввода и редактирования формул. Для завершения ввода формулы следует нажать кнопку ОК. После ввода функции суммирования СУММ в вычислимую ячейку A11 рабочий лист будет иметь следующий вид (рис. 4, а).

Рис. 4. Результат ввода функции суммирования в ячейку A11 при отображении результата выполнения формулы (а) и собственно формулы (б)

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

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

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

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

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

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

Рис. 5. Задание аргументов формулы с помощью специального окна мастера функций (шаг 2 из 2)

Для задания отдельной ячейки в качестве аргумента функции достаточно просто ее выделить на рабочем листе и нажать клавишу Enter. Для задания диапазона ячеек необходимо выделить соответствующий диапазон с помощью мыши или клавиатуры и также нажать клавишу Enter. Если диапазон ячеек не является непрерывным, следует выделять отдельные его ячейки, удерживая нажатой клавишу Ctrl. Рамка выделяемого в качестве аргументов диапазона ячеек приобретет вид мерцающей пунктирной линии или «змейки» аналогично выполнению операций копирования и вырезания.

Второй способ задания формул в вычислимые ячейки основан на непосредственном вводе выражения, служащего для выполнения соответствующих расчетов. Для определенности рассмотрим в качестве примера ввод следующей функции: f(x) = 2х² - 3x + 10, которая используется для расчета значения функции одного из значений независимой переменной

х.

2-3*A1+10. Рабочий лист с вводом формулы данным способом будет иметь следующий вид (рис. 6).

Рис. 6. Задание формулы с помощью непосредственного ввода выражения в вычислимую ячейку

В данном случае все математические операции должны быть указаны явно с использованием принятых в программе MS Excel обозначений. В качестве аргумента функции записывается адрес соответствующей ячейки, в которую предполагается вводить те или иные данные. Применительно к рассматриваемому примеру это адрес ячейки А1. После окончания ввода выражения для формулы следует нажать клавишу Enter. В результате будет завершен ввод формулы, а в ячейке А2 при отсутствии ошибок будет указан результат выполнения введенной формулы — число 10. Этот результат получен в предположении, что в ячейке А1 содержится число 0, хотя это число не вводилось ранее. Если в эту ячейку ввести любое другое число, например, число 5, то после его ввода изменится и значение вычислимой ячейки А2, которое станет равно 45.

Отдельно следует остановиться на наиболее распространенных ошибках при вводе формул вторым способом. Первая группа ошибок связана с некорректной записью пользователем математических и логических операций. Чтобы убедиться в правильном вводе данных операций, можно воспользоваться справочной системой программы MS Excel. С этой целью ввести в поле поиска окна документации, вызванного с помощью клавиши F1, ключевое слово «операторы». В результате будет открыто отдельное окно справочной системы программы MS Excel, в котором следует выбрать информацию из группы «Типы операторов», нажав на ссылке с соответствующим именем (рис. 7).

Рис. 7. Окно со справочной информацией по типам операторов программы MS Excel

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

Наконец следует отметить на возможные ошибки, связанные с вводом адресов ячеек символами кириллицы. Хотя локализованная версия программы MS Excel допускает ввод имен встроенных функций символами кириллицы, при попытке ввода адресов ячеек символами кириллицы в вычислимой ячейке появится сообщение об ошибке: #ИМЯ?. В этом случае следует сменить язык ввода символов на английский, а при вводе адресов ячеек в формулу проверять корректность ввода с помощью цветового выделения. Отсутствие последнего при вводе формул свидетельствует об ошибочных действиях со стороны пользователя.

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

Копирование формул осуществляется аналогично копированию содержимого обычных ячеек с данными. Отличие заключается в изменении адресации ячеек. которые используются в качестве аргументов соответствующих функций. Речь идет о том, что в случае обычной адресации ячеек, которая называется относительном при копировании формул программа MS Excel автоматически изменяет адреса тех ячеек, которые используются в качестве аргументов и имеют относительную адресацию. Так, например, при копировании обычным способом содержимого ячейки A2 в ячейку В2 вместо ячейки А1 в качестве аргумента рассматриваемой функции будет подставлен адрес ячейки В1. Соответственно изменится и расчетное значение функции в ячейке В2 (рис. 8).

Рис. 8. Копирование формулы с относительной адресацией ячеек-аргументов

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

Для того чтобы при копировании функции вычислимой ячейки рассматриваемого примера всегда указывать в качестве ее аргумента ячейку А1, следует при первоначальном вводе этой функции или последующем ее редактировании записать соответствующее выражение функции в виде: =2*$A$1^2-3*$A$1+10. В этом случае при копировании содержимого вычислимой ячейки А2 в ячейку В2 адрес ячейки-аргумента в соответствующей формуле не изменится (рис. 9).

Рис. 9. Копирование формулы с абсолютной адресацией ячеек-аргументов

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

Как избежать неверных формул

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

К сожалению, это означает, что Excel не может понять, что вы пытаетесь сделать, поэтому вы можете просто начать заново.

Начните с выбора OK или нажмите ESC , чтобы закрыть сообщение об ошибке.

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

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

Примечание. Если вы используете Microsoft 365 для Интернета, вы можете не увидеть те же ошибки или решения могут не применяться.

Excel выдает различные ошибки фунта стерлингов (#), такие как #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? и #NULL!, чтобы указать что-то в вашей формуле не работает должным образом. Например, #ЗНАЧ! ошибка вызвана неправильным форматированием или неподдерживаемыми типами данных в аргументах. Или вы увидите #REF! ошибка, если формула ссылается на ячейки, которые были удалены или заменены другими данными. Рекомендации по устранению неполадок будут отличаться для каждой ошибки.

Примечание: #### не является ошибкой, связанной с формулой. Это просто означает, что ширина столбца недостаточно широка для отображения содержимого ячейки. Просто перетащите столбец, чтобы расширить его, или перейдите к Home > Format > AutoFit Column Width .

Обратитесь к любому из следующих разделов, соответствующих ошибке фунта, которую вы видите:

Исправьте #ЧИСЛО! ошибка
Исправьте #ЗНАЧ! ошибка
org/ListItem»>
Исправьте ошибку #Н/Д
Исправьте #DIV/0! ошибка
Исправьте #REF! ошибка
Исправить #ИМЯ? ошибка
Исправьте #NULL! ошибка

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

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

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

Важно: Если вы впервые работаете с неработающими ссылками в формулах, если вам нужно освежить в памяти информацию об устранении неработающих ссылок или если вы не знаете, следует ли обновлять ссылки, см. раздел Управление внешними ссылками. (ссылки) обновлены.

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

org/ListItem»>
Убедитесь, что Excel настроен на отображение формул в электронной таблице. Для этого выберите вкладку Формулы и в группе Аудит формул выберите Показать формулы .
Совет: Вы также можете использовать сочетание клавиш Ctrl + ` (клавиша над клавишей Tab). Когда вы это сделаете, ваши столбцы автоматически расширятся для отображения ваших формул, но не беспокойтесь, когда вы вернетесь к обычному виду, ваши столбцы изменятся в размере.
Если описанный выше шаг по-прежнему не решает проблему, возможно, ячейка отформатирована как текст. Вы можете щелкнуть правой кнопкой мыши ячейку и выбрать Формат ячеек > Общие (или Ctrl + 1 ), затем нажать F2 > Enter , чтобы изменить формат.
org/ListItem»>
Если у вас есть столбец с большим диапазоном ячеек, отформатированных как текст, вы можете выбрать диапазон, применить числовой формат по вашему выбору и перейти к шагу 9.0007 Данные > Текст в столбец > Готово . Это применит формат ко всем выбранным ячейкам.

Если формула не вычисляется, необходимо проверить, включен ли автоматический расчет в Excel. Формулы не будут вычисляться, если включен ручной расчет. Выполните следующие действия, чтобы проверить Автоматический расчет .

Выберите вкладку Файл , выберите Параметры , а затем выберите категорию Формулы .
Убедитесь, что в разделе Параметры расчета в разделе Расчет рабочей книги выбран параметр Автоматический .

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

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

Дополнительные сведения о циклических ссылках см. в разделе Удаление или разрешение циклических ссылок.

Если ваша запись не начинается со знака равенства, это не формула и не будет рассчитана — распространенная ошибка.

Когда вы вводите что-то вроде СУММ(A1:A10) , Excel отображает текстовую строку СУММ(A1:A10) вместо результата формулы. В качестве альтернативы, если вы введете 11/2 , Excel отобразит дату, например 2 ноября или 02.11.2009., вместо того, чтобы делить 11 на 2.

Чтобы избежать этих непредвиденных результатов, всегда начинайте функцию со знака равенства. Например, введите: = СУММ(A1:A10) и =11/2 .

При использовании функции в формуле каждой открывающей скобке нужна закрывающая скобка, чтобы функция работала правильно. Убедитесь, что все скобки являются частью совпадающей пары. Например, формула =ЕСЛИ(В5<0),"Недействительно",В5*1,05) не будет работать, потому что есть две закрывающие скобки, но только одна открывающая скобка. Правильная формула будет выглядеть так: =ЕСЛИ(B5<0,"Недействительно",B5*1,05) .

Функции Excel имеют аргументы — значения, которые необходимо указать для работы функции. Только несколько функций (например, PI или TODAY) не принимают аргументов. Проверьте синтаксис формулы, который появляется, когда вы начинаете вводить функцию, чтобы убедиться, что у функции есть необходимые аргументы.

Например, функция ПРОПИСНЫЕ принимает в качестве аргумента только одну строку текста или ссылку на ячейку: =ПРОПИСН(«привет») или =ПРОПИСН(C2)

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

Кроме того, некоторые функции, такие как SUM, требуют только числовых аргументов, в то время как другие функции, такие как REPLACE, требуют текстового значения по крайней мере для одного из своих аргументов. Если вы используете неправильный тип данных, функции могут возвращать неожиданные результаты или отображать ошибку #ЗНАЧ! ошибка.

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

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

Если вы используете форматированные числа в аргументах, вы получите неожиданные результаты вычислений, но вы также можете увидеть ошибку #ЧИСЛО! ошибка. Например, если ввести формулу =ABS(-2,134) , чтобы найти абсолютное значение -2134, Excel показывает #ЧИСЛО! ошибка, потому что функция ABS принимает только один аргумент и видит -2 и 134 как отдельные аргументы.

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

Ваша формула может не возвращать ожидаемые результаты, если тип данных ячейки нельзя использовать в вычислениях. Например, если вы введете простую формулу =2+3 в ячейку, отформатированную как текст, Excel не сможет вычислить введенные вами данные. Все, что вы увидите в ячейке, это =2+3 . Чтобы это исправить, измените тип данных ячейки с Text на General следующим образом:

Выберите ячейку.
Выберите Домашний и щелкните стрелку, чтобы развернуть группу Номер или Формат номера (или нажмите Ctrl + 1 ). Затем выберите General .
Нажмите F2 , чтобы перевести ячейку в режим редактирования, а затем нажмите . Введите , чтобы принять формулу.

Дата, которую вы вводите в ячейку с цифрой 9.0007 Тип данных Number может отображаться как числовое значение даты вместо даты. Чтобы число отображалось как дата, выберите формат Date в галерее Number Format .

Довольно часто используется x в качестве оператора умножения в формуле, но Excel может принимать только звездочку (*) для умножения. Если вы используете константы в своей формуле, Excel показывает сообщение об ошибке и может исправить формулу для вас, заменив x со звездочкой (*).

Однако, если вы используете ссылки на ячейки, Excel вернет #ИМЯ? ошибка.

Если вы создаете формулу, содержащую текст, заключите текст в кавычки.

Например, формула =»Сегодня » & ТЕКСТ(СЕГОДНЯ(),»дддд, мммм дд») объединяет текст «Сегодня» с результатами функций ТЕКСТ и СЕГОДНЯ и возвращает что-то вроде Сегодня понедельник, 30 мая .

В формуле «Сегодня» есть пробел перед завершающей кавычкой, чтобы обеспечить пробел между словами «Сегодня» и «Понедельник, 30 мая». Без кавычек вокруг текста в формуле может отображаться #ИМЯ? ошибка.

В формуле можно комбинировать (или вкладывать) до 64 уровней функций.

Например, формула =ЕСЛИ(КОРЕНЬ(ПИ())<2,"Меньше двух!","Больше двух!") имеет 3 уровня функций; функция PI вложена в функцию SQRT, которая, в свою очередь, вложена в функцию IF.

Если вы вводите ссылку на значения или ячейки на другом листе, а имя этого листа содержит небуквенный символ (например, пробел), заключите имя в одинарные кавычки (‘).

Например, чтобы получить значение из ячейки D3 на листе с названием «Квартальные данные» в книге, введите: = «Квартальные данные»! D3 . Без кавычек вокруг имени листа формула показывает #ИМЯ? ошибка.

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

При вводе ссылки на значения или ячейки в другой книге укажите имя книги, заключенное в квадратные скобки ([]), за которым следует имя рабочего листа, содержащего значения или ячейки.

Например, чтобы обратиться к ячейкам с A1 по A8 на листе Sales в рабочей книге Q2 Operations, открытой в Excel, введите: =[Q2 Operations.xlsx]Sales!A1:A8 . Без квадратных скобок формула показывает #ССЫЛКА! ошибка.

Если книга не открыта в Excel, введите полный путь к файлу.

Например, =ROWS(‘C:\My Documents\[Q2 Operations.xlsx]Sales’!A1:A8) .

Примечание. Если полный путь содержит пробелы, заключите путь в одинарные кавычки (в начале пути и после имени листа, перед восклицательным знаком).

Совет: Самый простой способ получить путь к другой книге — открыть другую книгу, затем в исходной книге ввести = и использовать Alt+Tab для перехода к другой книге. Выберите любую ячейку на нужном листе, а затем закройте исходную книгу. Ваша формула автоматически обновится, чтобы отобразить полный путь к файлу и имя листа вместе с необходимым синтаксисом. Вы даже можете скопировать и вставить путь и использовать там, где вам это нужно.

Деление ячейки на другую ячейку, которая имеет нулевое (0) значение или вообще не имеет значения, приводит к ошибке #DIV/0! ошибка.

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

=IF(B1,A1/B1,0)

Что говорит ЕСЛИ(B1 существует, затем разделите A1 на B1, иначе верните 0).

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

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

org/ListItem»>
Если формула ссылается на ячейки, которые были удалены или заменены другими данными, и если она возвращает #ССЫЛКА! ошибка, выберите ячейку с #ССЫЛКА! ошибка. В строке формул выберите #ССЫЛКА! и удалите его. Затем снова введите диапазон для формулы.
Если определенное имя отсутствует, а формула, которая ссылается на это имя, возвращает #ИМЯ? ошибки, определите новое имя, которое ссылается на нужный вам диапазон, или измените формулу, чтобы она ссылалась непосредственно на диапазон ячеек (например, A2:D8).
Если рабочий лист отсутствует, а формула, ссылающаяся на него, возвращает ошибку #ССЫЛКА! ошибка, исправить это, к сожалению, невозможно — удаленный рабочий лист невозможно восстановить.
Если рабочая книга отсутствует, формула, ссылающаяся на нее, остается неизменной, пока вы не обновите формулу.
Например, если ваша формула =[Book1.xlsx]Sheet1′!A1 и у вас больше нет Book1.xlsx, значения, указанные в этой книге, остаются доступными. Однако если вы отредактируете и сохраните формулу, которая ссылается на эту книгу, Excel отобразит диалоговое окно Update Values  и предложит ввести имя файла. Выберите Cancel , а затем убедитесь, что эти данные не потеряны, заменив формулы, которые ссылаются на отсутствующую книгу, результатами формулы.

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

Например, может потребоваться скопировать результирующее значение формулы в ячейку на другом листе. Или вы можете захотеть удалить значения, которые вы использовали в формуле, после того, как вы скопировали полученное значение в другую ячейку на листе. Оба этих действия приводят к тому, что в целевой ячейке появляется ошибка недопустимой ссылки на ячейку (#ССЫЛКА!), поскольку на ячейки, содержащие значения, которые вы использовали в формуле, больше нельзя ссылаться.

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

На листе выберите ячейки, содержащие результирующие значения формулы, которую вы хотите скопировать.
На вкладке Главная в группе Буфер обмена выберите Копировать .
Сочетание клавиш: нажмите CTRL+C.
Выберите верхнюю левую ячейку области вставки.
Совет: Чтобы переместить или скопировать выделенное на другой лист или книгу, выберите другую вкладку листа или переключитесь на другую книгу, а затем выберите верхнюю левую ячейку области вставки.
На вкладке Главная в группе Буфер обмена выберите Вставить , а затем выберите Вставить значения или нажмите Alt > E > S > V > введите для Windows или Option > Command > V > V > введите на Mac.

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

Выберите формулу, которую вы хотите вычислить.
Выберите Формулы > Вычислить формулу .
Выберите Оцените , чтобы проверить значение подчеркнутой ссылки. Результат оценки выделен курсивом.
Если подчеркнутая часть формулы является ссылкой на другую формулу, выберите Step In , чтобы показать другую формулу в поле Evaluation . Выберите Step Out , чтобы вернуться к предыдущей ячейке и формуле.
Кнопка Step In недоступна, когда ссылка появляется в формуле во второй раз или если формула ссылается на ячейку в другой книге.
Продолжайте, пока не будет оценена каждая часть формулы.
Инструмент «Оценить формулу» не обязательно скажет вам, почему ваша формула не работает, но может помочь указать, где именно. Это может быть очень удобным инструментом в больших формулах, где в противном случае может быть трудно найти проблему.
Примечания:
- Некоторые части функций ЕСЛИ и ВЫБОР не будут оцениваться, и в поле Оценка может появиться ошибка #N/A .
- org/ListItem»>
  Пустые ссылки отображаются как нулевые значения (0) в поле Оценка .
- Некоторые функции пересчитываются при каждом изменении листа. Эти функции, в том числе функции СЛЧИС, ПЛОЩАДИ, ИНДЕКС, СМЕЩ, ЯЧЕЙКА, ДВССЫЛ, СТРОКИ, КОЛОННЫ, СЕЙЧАС, СЕГОДНЯ и СЛУЧМЕЖДУ, могут привести к тому, что в диалоговом окне Оценить формулу будут отображаться результаты, которые отличаются от фактических результатов в ячейка на рабочем листе.

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

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

См. также

Обзор формул в Excel

Обнаружение ошибок в формулах

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Порядок, в котором Excel выполняет операции в формулах

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Дополнительно. .. Меньше

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

Порядок расчета
Формулы вычисляют значения в определенном порядке. Формула в Excel всегда начинается со знака равенства ( = ). Excel интерпретирует символы, следующие за знаком равенства, как формулу. После знака равенства следуют вычисляемые элементы (операнды), такие как константы или ссылки на ячейки. Они разделены операторами вычисления. Excel вычисляет формулу слева направо в соответствии с определенным порядком для каждого оператора в формуле.

Приоритет оператора в формулах Excel

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

Оператор	Описание
: (двоеточие) (один пробел) , (запятая)	Эталонные операторы
–	Отрицание (как в –1)
Возведение в степень
* и /	Умножение и деление
+ и –	Сложение и вычитание
и	Соединяет две строки текста (объединение)
= < > <= >= <>	Сравнение

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