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

Обновлено: 21.11.2024

Формулы — это уравнения, которые выполняют вычисления со значениями на вашем листе. Все формулы начинаются со знака равенства (=). Вы можете создать простую формулу, используя константу и оператор вычисления. Например, формула =5+2*3 умножает два числа, а затем добавляет число к результату.

Если вы хотите ссылаться на переменные вместо констант, вы можете использовать значения ячеек, например, =A1+A2. Если вы работаете с длинными столбцами данных или данными, расположенными в разных частях листа или на другом листе, вы можете использовать диапазон, например =СУММ(A1:A100)/СУММ(B1:B100), который представляет собой деление суммы первых сотен чисел в столбце A на сумму этих чисел в столбце B. Когда ваша формула ссылается на другие ячейки, каждый раз, когда вы изменяете данные в любой из ячеек, Excel автоматически пересчитывает результаты. .

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

Все формулы начинаются со знаков равенства.

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

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

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

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

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

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

Введите знак равенства =

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

Введите оператор. Оператор — это тип вычисления, который выполняет формула. Например, оператор * (звездочка) умножает числа. В этом примере используйте оператор / (косая черта) для деления. На этом этапе ваша формула должна выглядеть так:

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

Результат вычисления появится в ячейке.

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

Введите формулу, содержащую функцию

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

Введите знак равенства и функцию, например =MIN. МИН находит наименьшее число в диапазоне ячеек.

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

В нашем примере функция MIN возвращает 11, наименьшее число в ячейках с A1 по C4.

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

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

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

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

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

При выборе функции открывается построитель формул с дополнительной информацией о функции.

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

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

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

Узнайте, как вводить простую формулу

Формулы – это уравнения, которые выполняют вычисления со значениями на листе. Формула начинается со знака равенства (=). Например, следующая формула добавляет 3 к 1.

Формула также может содержать некоторые или все из следующих элементов: функции, ссылки, операторы и константы.

Части формулы

Функции: функции, включенные в Excel, представляют собой разработанные формулы, которые выполняют определенные вычисления. Например, функция PI() возвращает значение числа пи: 3,142.

Ссылки: ссылки на отдельные ячейки или диапазоны ячеек. A2 возвращает значение в ячейке A2.

Константы: числа или текстовые значения, введенные непосредственно в формулу, например 2.

Операторы. Оператор ^ (вставка) возводит число в степень, а оператор * (звездочка) умножает. Используйте + и – для сложения и вычитания значений и / для деления.

Примечание. Для некоторых функций требуются так называемые аргументы. Аргументы — это значения, которые определенные функции используют для выполнения своих вычислений. При необходимости аргументы помещаются между скобками функции (). Функция PI не требует никаких аргументов, поэтому она пуста. Некоторые функции требуют одного или нескольких аргументов и могут оставлять место для дополнительных аргументов. Вам нужно использовать запятую для разделения аргументов или точку с запятой (;) в зависимости от настроек вашего местоположения.

Например, для функции SUM требуется только один аргумент, но общее количество аргументов может достигать 255.

=SUM(A1:A10) – это пример одного аргумента.

=СУММ(A1:A10, C1:C10) — это пример нескольких аргументов.

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

Убедитесь, что вы

Начинайте каждую функцию со знака равенства (=)

Если вы опустите знак равенства, введенное вами может отображаться как текст или как дата. Например, если ввести СУММ(A1:A10), Excel отобразит текстовую строку СУММ(A1:A10) и не выполнит расчет. Если вы введете 11/2, Excel отобразит дату со 2 по ноябрь (при условии, что формат ячейки Общий) вместо деления 11 на 2.

Соответствует всем открывающим и закрывающим скобкам

Убедитесь, что все круглые скобки являются частью совпадающей пары (открывающей и закрывающей). Когда вы используете функцию в формуле, важно, чтобы каждая скобка находилась в правильном положении, чтобы функция работала правильно. Например, формула =ЕСЛИ(B5

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

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

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

Для Excel в Windows нажмите "Файл" > "Параметры" > "Формулы" или
для Excel на Mac выберите меню Excel > "Настройки" > "Проверка ошибок".

В Excel 2007 нажмите кнопку Microsoft Office > Параметры Excel > Формулы.

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

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

В разделе "Правила проверки Excel" установите или снимите флажки любого из следующих правил:

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

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

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

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

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

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

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

Ячейки, содержащие годы, представленные в виде двух цифр. Ячейка содержит текстовую дату, которая может быть неправильно истолкована как неверное столетие при использовании в формулах. Например, дата в формуле =ГОД("1/1/31") может быть 1931 или 2031. Используйте это правило, чтобы проверить наличие неоднозначных текстовых дат.

Числа в текстовом формате или с предшествующим апострофом: ячейка содержит числа, сохраненные в виде текста. Обычно это происходит, когда данные импортируются из других источников. Числа, хранящиеся в виде текста, могут привести к неожиданным результатам сортировки, поэтому лучше преобразовать их в числа. ‘=SUM(A1:A10) рассматривается как текст.

Формулы несовместимы с другими формулами в регионе. Формула не соответствует шаблону других формул рядом с ней. Во многих случаях формулы, расположенные рядом с другими формулами, отличаются только используемыми ссылками. В следующем примере с четырьмя соседними формулами Excel отображает ошибку рядом с формулой =СУММ(A10:C10) в ячейке D4, потому что соседние формулы увеличиваются на одну строку, а эта увеличивается на 8 строк — Excel ожидает формулу =СУММ (А4:С4).

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

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

Например, Excel вставляет ошибку рядом с формулой =СУММ(D2:D4), когда применяется это правило, поскольку ячейки D5, D6 и D7 находятся рядом с ячейками, на которые ссылается формула, и ячейкой, на которую ссылается формула. содержит формулу (D8), а эти ячейки содержат данные, на которые должна была быть ссылка в формуле.

Разблокированные ячейки, содержащие формулы: Формула не заблокирована для защиты. По умолчанию все ячейки на рабочем листе заблокированы, поэтому их нельзя изменить, когда рабочий лист защищен. Это может помочь избежать непреднамеренных ошибок, таких как случайное удаление или изменение формул. Эта ошибка указывает на то, что ячейка была разблокирована, но лист не защищен. Убедитесь, что ячейка не заблокирована или нет.

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

Предположим, вы хотите вычислить среднее значение чисел в следующем столбце ячеек. Если третья ячейка пуста, она не включается в расчет и результат равен 22,75. Если третья ячейка содержит 0, результат равен 18,2.

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

Выберите лист, который вы хотите проверить на наличие ошибок.

Если рабочий лист рассчитан вручную, нажмите F9, чтобы пересчитать.

Если диалоговое окно "Проверка ошибок" не отображается, нажмите вкладку "Формулы" > "Проверка формул" > кнопка "Проверка ошибок".

Если вы ранее игнорировали какие-либо ошибки, вы можете снова проверить их, выполнив следующие действия: нажмите "Файл" > "Параметры" > "Формулы". Для Excel на Mac выберите меню Excel > «Настройки» > «Проверка ошибок».

В разделе "Проверка ошибок" нажмите "Сбросить пропущенные ошибки" > "ОК".

Примечание. При сбросе пропущенных ошибок сбрасываются все ошибки на всех листах активной книги.

Совет. Переместите диалоговое окно "Проверка ошибок" чуть ниже строки формул.

Нажмите одну из кнопок действий в правой части диалогового окна. Доступные действия различаются для каждого типа ошибки.

Нажмите "Далее".

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

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

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

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

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

Excel отображает эту ошибку, когда число делится либо на ноль (0), либо на ячейку, которая не содержит значения.

Совет. Добавьте обработчик ошибок, как в следующем примере: =IF(C2,B2/C2,0)

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

Если вы используете функцию ВПР, имеет ли то, что вы пытаетесь найти, совпадение в диапазоне поиска? Чаще всего это не так.

=ЕСЛИОШИБКА(ВПР(D2,$D$6:$E$8,2,ИСТИНА),0)

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

Примечание. Если вы используете функцию, убедитесь, что имя функции написано правильно. В этом случае СУММА написана неправильно. Удалите букву «е», и Excel исправит ее.

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

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

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

Вы случайно удалили строку или столбец? Мы удалили столбец B в этой формуле =СУММ(A2,B2,C2) и посмотрите, что получилось.

Либо используйте команду "Отменить" (Ctrl+Z), чтобы отменить удаление, либо перестройте формулу, либо используйте ссылку на непрерывный диапазон, например: =СУММ(A2:C2), которая автоматически обновлялась бы при удалении столбца B.

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

Используете ли вы математические операторы (+, -, *, /, ^) с разными типами данных? Если это так, попробуйте вместо этого использовать функцию. В этом случае =СУММ(F2:F5) устранит проблему.

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

Эту панель инструментов можно перемещать или закреплять, как и любую другую панель инструментов. Например, вы можете закрепить его в нижней части окна. Панель инструментов отслеживает следующие свойства ячейки: 1) Рабочая книга, 2) Лист, 3) Имя (если ячейка имеет соответствующий именованный диапазон), 4) Адрес ячейки, 5) Значение и 6) Формула.

Примечание. В каждой ячейке может быть только одно наблюдение.

Добавить ячейки в окно просмотра

Выберите ячейки, которые вы хотите просмотреть.

Чтобы выделить все ячейки на листе с формулами, на вкладке «Главная» в группе «Редактирование» нажмите «Найти и выделить» (или можно использовать Ctrl+G или Control+G на Mac) > «Перейти к специальным» > «Формулы». .

На вкладке "Формулы" в группе "Аудит формул" нажмите "Окно наблюдения".

Нажмите "Добавить наблюдение".

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

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

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

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

Удалить ячейки из окна просмотра

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

Выберите ячейки, которые хотите удалить.

Чтобы выбрать несколько ячеек, нажмите клавишу CTRL, а затем щелкните ячейки.

Нажмите «Удалить отслеживание».

Иногда сложно понять, как вложенная формула вычисляет окончательный результат, потому что есть несколько промежуточных вычислений и логических тестов. Однако с помощью диалогового окна «Вычислить формулу» можно увидеть, как различные части вложенной формулы оцениваются в том порядке, в котором вычисляется формула. Например, формулу =ЕСЛИ(СРЗНАЧ(D2:D5)>50;СУММ(E2:E5),0) будет легче понять, если вы увидите следующие промежуточные результаты:

В диалоговом окне "Вычислить формулу"

Описание

Сначала отображается вложенная формула. Функция СРЗНАЧ и функция СУММ вложены в функцию ЕСЛИ.

Диапазон ячеек D2:D5 содержит значения 55, 35, 45 и 25, поэтому результат функции СРЗНАЧ(D2:D5) равен 40.

Поскольку 40 не больше 50, выражение в первом аргументе функции ЕСЛИ (аргумент логическая_проверка) равно False.

Функция ЕСЛИ возвращает значение третьего аргумента (аргумент значение_если_ложь). Функция СУММ не вычисляется, поскольку она является вторым аргументом функции ЕСЛИ (аргумент значение_если_истина) и возвращается только в том случае, если выражение истинно.

Выберите ячейку, которую вы хотите оценить. Одновременно можно оценивать только одну ячейку.

Выберите вкладку "Формулы" > "Аудит формул" > "Оценить формулу".

Нажмите "Оценить", чтобы проверить значение подчеркнутой ссылки. Результат оценки выделен курсивом.

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

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

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

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

Чтобы завершить оценку, нажмите "Закрыть".

Если ссылка пуста, в поле "Оценка" отображается нулевое значение (0).

Следующие функции пересчитываются каждый раз при изменении рабочего листа и могут привести к тому, что в диалоговом окне "Вычислить формулу" будут отображаться результаты, отличные от показанных в ячейке: СЛУЧАЙ, ОБЛАСТИ, ИНДЕКС, СМЕЩ, ЯЧЕЙКА, ДВССЫЛ, СТРОКИ, СТОЛБЦЫ, СЕЙЧАС , СЕГОДНЯ, СЛУЧАЙНО МЕЖДУ.

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

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

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

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

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

В следующей формуле используется функция ПЛТ для расчета платежа по ипотеке (1073,64 долл. США), основанного на процентной ставке 5 % (5 %, разделенные на 12 месяцев, равняется месячной процентной ставке) за 30-летний период (360 месяцев). ) для кредита в размере 200 000 долларов США:

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

=A1+A2+A3 Складывает значения в ячейках A1, A2 и A3.

=SQRT(A1) Использует функцию SQRT для возврата квадратного корня из значения в A1.

=TODAY() Возвращает текущую дату.

=ПРОПИСН("привет") Преобразует текст "привет" в "ПРИВЕТ" с помощью функции листа ПРОПИСН.

=IF(A1>0) Проверяет ячейку A1, чтобы определить, содержит ли она значение больше 0.

Части формулы

<р>1. Функции: функция PI() возвращает значение числа пи: 3,142.

<р>2. Ссылки: A2 возвращает значение в ячейке A2.

<р>3.Константы: числа или текстовые значения, введенные непосредственно в формулу, например 2.

<р>4. Операторы: оператор ^ (вставка) возводит число в степень, а оператор * (звездочка) умножает числа.

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

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

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

Типы операторов

Существует четыре различных типа операторов вычисления: арифметические операции, сравнение, конкатенация текста и ссылка.

Арифметические операторы

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

Арифметический оператор

Операторы сравнения

Вы можете сравнить два значения с помощью следующих операторов. Когда два значения сравниваются с помощью этих операторов, результатом является логическое значение — либо ИСТИНА, либо ЛОЖЬ.

Оператор сравнения

> (знак больше)

= (знак больше или равно)

Больше или равно

(не равно знаку)

Оператор объединения текста

Используйте амперсанд (&), чтобы соединить (объединить) одну или несколько текстовых строк, чтобы получить единый фрагмент текста.

Текстовый оператор

Соединяет или объединяет два значения для создания одного непрерывного текстового значения

"Север"&"ветер" приводит к "Борей"

Ссылочные операторы

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

Оператор ссылки

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

Оператор объединения, который объединяет несколько ссылок в одну ссылку

Оператор пересечения, создающий одну ссылку на ячейки, общие для двух ссылок

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

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

Порядок расчета

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

Приоритет оператора

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

Описание

Отрицание (как в –1)

Умножение и деление

Сложение и вычитание

Соединяет две строки текста (объединение)

Использование скобок

Чтобы изменить порядок вычисления, заключите в круглые скобки ту часть формулы, которая будет вычисляться первой. Например, следующая формула дает 11, так как Excel в Интернете выполняет умножение перед сложением. Формула умножает 2 на 3, а затем добавляет к результату 5.

Наоборот, если вы используете круглые скобки для изменения синтаксиса, Excel для Интернета суммирует 5 и 2, а затем умножает результат на 3, чтобы получить 21.

В следующем примере круглые скобки, заключающие первую часть формулы, заставляют Excel для Интернета сначала вычислить B4+25, а затем разделить результат на сумму значений в ячейках D5, E5 и F5.< /p>

Использование функций и вложенных функций в формулах

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

Синтаксис функций

Следующий пример функции ОКРУГЛ, округляющей число в ячейке A10, иллюстрирует синтаксис функции.

<р>1. Структура. Структура функции начинается со знака равенства (=), за которым следует имя функции, открывающая скобка, аргументы функции, разделенные запятыми, и закрывающая скобка.

<р>2. Имя функции. Чтобы просмотреть список доступных функций, щелкните ячейку и нажмите SHIFT+F3.

<р>4. Подсказка аргумента. При вводе функции появляется всплывающая подсказка с синтаксисом и аргументами.Например, введите =ROUND( и появится всплывающая подсказка. Подсказки появляются только для встроенных функций.

Ввод функций

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

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

Вложенные функции

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

<р>1. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.

Ограничения уровня вложенности Формула может содержать до семи уровней вложенности функций. Когда одна функция (назовем ее Функцией Б) используется в качестве аргумента в другой функции (назовем ее Функцией А), Функция Б действует как функция второго уровня. Например, функция СРЗНАЧ и функция СУММ являются функциями второго уровня, если они используются в качестве аргументов функции ЕСЛИ. Функция, вложенная во вложенную функцию СРЗНАЧ, становится функцией третьего уровня и т. д.

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

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

Справочный стиль A1

Стили ссылок по умолчанию По умолчанию Excel в Интернете использует стиль ссылок A1, который ссылается на столбцы с буквами (от A до XFD, всего 16 384 столбца) и ссылается на строки с номерами (от 1 до 1 048 576). Эти буквы и цифры называются заголовками строк и столбцов. Чтобы сослаться на ячейку, введите букву столбца, а затем номер строки. Например, B2 относится к ячейке на пересечении столбца B и строки 2.

Для ссылки

Ячейка в столбце A и строке 10

Диапазон ячеек в столбце А и строках с 10 по 20

Диапазон ячеек в строке 15 и столбцах с B по E

Все ячейки в строке 5

Все ячейки в строках с 5 по 10

Все ячейки в столбце H

Все ячейки в столбцах с H по J

Диапазон ячеек в столбцах от A до E и строках с 10 по 20

Создание ссылки на другой лист В следующем примере функция листа AVERAGE вычисляет среднее значение для диапазона B1:B10 на листе Marketing в той же книге.

<р>1. Относится к рабочему листу под названием "Маркетинг"

<р>2. Относится к диапазону ячеек от B1 до B10 включительно

<р>3. Отделяет ссылку на рабочий лист от ссылки на диапазон ячеек

Разница между абсолютными, относительными и смешанными ссылками

Относительные ссылки Относительная ссылка на ячейку в формуле, например A1, основана на относительном положении ячейки, содержащей формулу, и ячейки, на которую ссылается ссылка. Если положение ячейки, содержащей формулу, изменяется, ссылка изменяется. Если вы скопируете или заполните формулу между строками или столбцами, ссылка будет автоматически скорректирована. По умолчанию в новых формулах используются относительные ссылки. Например, если вы скопируете или заполните относительную ссылку из ячейки B2 в ячейку B3, она автоматически изменится с =A1 на =A2.

Абсолютные ссылки Абсолютная ссылка на ячейку в формуле, например $A$1, всегда указывает на ячейку в определенном месте. Если положение ячейки, содержащей формулу, изменяется, абсолютная ссылка остается прежней. Если вы скопируете или заполните формулу между строками или столбцами, абсолютная ссылка не изменится. По умолчанию в новых формулах используются относительные ссылки, поэтому вам может потребоваться переключить их на абсолютные ссылки. Например, если вы скопируете или заполните абсолютную ссылку из ячейки B2 в ячейку B3, она останется одинаковой в обеих ячейках: =$A$1.

Смешанные ссылки Смешанная ссылка имеет либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка на столбец имеет вид $A1, $B1 и т. д. Абсолютная ссылка на строку принимает форму A$1, B$1 и т. д.Если положение ячейки, содержащей формулу, изменяется, относительная ссылка изменяется, а абсолютная ссылка не изменяется. Если вы копируете или заполняете формулу между строками или столбцами, относительная ссылка корректируется автоматически, а абсолютная ссылка не корректируется. Например, если вы скопируете или заполните смешанную ссылку из ячейки A2 в ячейку B3, она изменится с =A$1 на =B$1.

Трехмерный эталонный стиль

Удобные ссылки на несколько листов Если вы хотите проанализировать данные в одной и той же ячейке или диапазоне ячеек на нескольких листах в книге, используйте трехмерную ссылку. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, которому предшествует диапазон имен рабочих листов. Excel в Интернете использует все листы, хранящиеся между начальным и конечным именами ссылки. Например, =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5, на всех листах между листами 2 и 13 включительно.

Трехмерные ссылки можно использовать для ссылки на ячейки на других листах, для определения имен и создания формул с помощью следующих функций: СУММ, СРЗНАЧ, СРЗНАЧ, СЧЕТ, СЧЕТ, МАКС, МАКС, МИН, МИН, PRODUCT, STDEV.P, STDEV.S, STDEVA, STDEVPA, VAR.P, VAR.S, VARA и VARPA.

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

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

Что происходит при перемещении, копировании, вставке или удалении листов В следующих примерах показано, что происходит при перемещении, копировании, вставке или удалении листов, включенных в трехмерную ссылку. В примерах используется формула =СУММ(Лист2:Лист6!A2:A5) для добавления ячеек с A2 по A5 на листах со 2 по 6.

Вставка или копирование Если вы вставляете или копируете листы между Листами2 и Лист6 (конечными точками в этом примере), Excel в Интернете включает в расчеты все значения в ячейках с A2 по A5 из добавленных листов.

Удалить. Если вы удалите листы между Листами2 и Лист6, Excel в Интернете удалит их значения из расчета.

Переместить. Если вы перемещаете листы между Листами2 и Лист6 в место за пределами указанного диапазона листов, Excel в Интернете удаляет их значения из расчета.

Перемещение конечной точки. Если вы перемещаете Лист2 или Лист6 в другое место в той же книге, Excel в Интернете корректирует расчет, чтобы учесть новый диапазон листов между ними.

Удалить конечную точку. Если вы удаляете Лист2 или Лист6, Excel в Интернете корректирует расчет, чтобы учесть диапазон листов между ними.

Стиль ссылок R1C1

Вы также можете использовать справочный стиль, в котором и строки, и столбцы на листе пронумерованы. Справочный стиль R1C1 полезен для вычисления позиций строк и столбцов в макросах. В стиле R1C1 Excel для Интернета указывает расположение ячейки буквой "R", за которой следует номер строки, и буквой "C", за которой следует номер столбца.

Относительная ссылка на ячейку двумя строками выше и в том же столбце

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

Абсолютная ссылка на ячейку во второй строке и во втором столбце

Относительная ссылка на всю строку над активной ячейкой

Абсолютная ссылка на текущую строку

При записи макроса Excel в Интернете записывает некоторые команды, используя стиль ссылок R1C1. Например, если вы записываете команду, например нажатие кнопки "Автосумма", чтобы вставить формулу, которая добавляет диапазон ячеек, Excel в Интернете записывает формулу, используя стиль R1C1, а не стиль A1, ссылки.

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

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

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

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

Excel автоматически загрузит мастер.

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

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

Решение. Определите имя в диспетчере имен, а затем добавьте его в формулу. Для этого выполните следующие действия:

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

На вкладке "Формулы" в группе "Определенные имена" нажмите "Определить имя", а затем нажмите "Определить имя".

Введите уникальное имя.

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

Введите необязательный комментарий.

Следующая часть — добавить имя в формулу.

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

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

Дополнительную информацию об использовании определенных имен см. в разделе Определение и использование имен в формулах.

Решение. Исправьте опечатку в синтаксисе и повторите ввод формулы.

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

В синтаксисе в этом примере отсутствуют двойные кавычки для has; вот почему ошибка.

Решение. Вернитесь к своему синтаксису и проверьте вручную, чтобы убедиться, что все текстовые значения заключены в кавычки.

Решение. Проверьте синтаксис и убедитесь, что все ссылки на диапазоны включают двоеточие.

Чтобы включить надстройки в Excel:

Нажмите "Файл" > "Параметры".

Нажмите "Надстройки".

В списке "Управление" выберите "Надстройки Excel" и нажмите "Перейти".

Установите соответствующий флажок и нажмите OK.

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

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