Обзор формул

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

=5+2*3

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

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

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

1. Функции. Функция ПИ() возвращает значение числа Пи: 3,142...

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

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

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

В этой статье

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

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

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

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

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

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

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

Константа представляет собой готовое (не вычисляемое) значение. Например, дата 09.10.2008, число 210 и текст "Прибыль за квартал" являются константами. Выражение и его значение константами не являются. Если в формуле в ячейке не содержится ссылок на другие ячейки (например, формула имеет вид =30+70+110), значение в такой ячейке изменяется только после изменения формулы вручную.

К началу страницы

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

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

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

В Microsoft Excel включено четыре вида операторов: арифметические, текстовые, операторы сравнения и операторы ссылок.

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

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

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

Значение

Пример

+ (знак "плюс")

Сложение

3+3

– (знак минус)

Вычитание
Отрицание

3–1
–1

* (звездочка)

Умножение

3*3

/ (косая черта)

Деление

3/3

% (знак процента)

Процент

20%

^ (крышка)

Возведение в степень

3^2

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

Используются для сравнения двух значений. Результатом сравнения является

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

Значение

Пример

= (знак равенства)

Равно

A1=B1

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

Больше

A1>B1

< (знак меньше)

Меньше

A1<B1

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

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

A1>=B1

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

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

A1<=B1

<> (знак не равно)

Не равно

A1<>B1

логическое значение: либо ИСТИНА, либо ЛОЖЬ.

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

Амперсанд (&) используется для объединения (конкатенации) текстовых строк в одну строку.

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

Значение

Пример

& (амперсанд)

Объединение двух последовательностей знаков в одну

"Северный"&"ветер"

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

Для описания ссылок на диапазоны ячеек используются следующие операторы.

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

Значение

Пример

: (двоеточие)

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

B5:B15

, (запятая)

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

СУММ(B5:B15,D5:D15)

(пробел)

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

B7:D7 C6:C8

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

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

Порядок вычислений

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

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

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

Оператор

Описание

: (двоеточие)

(один пробел)

, (запятая)

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

Знак "минус"

%

Процент

^

Возведение в степень

* и /

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

+ и –

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

&

Объединение двух текстовых строк в одну

=
< >
<=
>=
<>

Сравнение

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

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

=5+2*3

Если же с помощью скобок изменить синтаксис, Microsoft Excel сложит 5 и 2, а затем умножит результат на 3; результатом этих действий будет число 21.

=(5+2)*3

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

=(B4+25)/СУММ(D5:F5)

К началу страницы

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

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

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

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

Структура функции

Структура функции

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

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

3. Аргументы. Существуют различные типы аргументов: числа, текст, логические значения (ИСТИНА и ЛОЖЬ), массивы, значения ошибок (например, #Н/Д) и ссылки на ячейки. В качестве аргументов можно использовать константы, формулы или функции. В каждом конкретном случае необходимо использовать аргументы подходящего типа.

4. Всплывающая подсказка аргумента. Всплывающая подсказка с синтаксисом и аргументами появляется после ввода функции. Например, всплывающая подсказка появится после ввода "=ОКРУГЛ(". Всплывающие подсказки отображаются только для встроенных функций.

Ввод функций

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

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

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

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

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

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

Допустимые типы вычисляемых значений    Вложенная функция, используемая в качестве аргумента, должна вычислять соответствующий этому аргументу тип данных. Например, если аргумент должен быть логическим, то есть иметь значение либо ИСТИНА, либо ЛОЖЬ, то вложенная функция в результате вычислений тоже должна давать логическое значение ИСТИНА или ЛОЖЬ. Иначе появится сообщение об ошибке "#ЗНАЧ!".

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

К началу страницы

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

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

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

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

Ячейка или диапазон

Ссылка

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

A10

Диапазон ячеек: столбец А, строки 10-20.

A10:A20

Диапазон ячеек: строка 15, столбцы B-E

B15:E15

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

5:5

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

5:10

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

H:H

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

H:J

Диапазон ячеек: столбцы А-E, строки 10-20

A10:E20

Ссылка на другой лист    В приведенном ниже примере функция СРЗНАЧ используется для расчета среднего значения диапазона B1:B10 на листе "Маркетинг" той же книги.

Пример ссылки на лист

Ссылка на диапазон ячеек в другом листе той же книги

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.

Скопированная формула со смешанной ссылкой

Скопированная формула со смешанной ссылкой

Стиль трехмерных ссылок

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

  • Трехмерные ссылки могут быть использованы для создания ссылок на ячейки на других листах, определения имен и создания формул с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН, СТАНДОТКЛОНА, СТАНДОТКЛОНП, СТАНДОТКЛОНПА, ДИСП, ДИСПА, ДИСПР и ДИСПРА.

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

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

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

  • Вставка или копирование    Если между листом 2 и листом 6 вставить новые листы, Microsoft Excel добавит в сумму содержимое ячеек с A2 по A5 на новых листах.

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

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

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

  • Удаление конечного листа    Если удалить лист 2 или лист 6, Excel скорректирует сумму с учетом изменения диапазона листов.

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

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

Ссылка

Значение

R[-2]C

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

R[2]C[2]

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

R2C2

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

R[-1]

Относительная ссылка на строку, расположенную выше текущей ячейки

R

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

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

Чтобы включить или выключить стиль ссылок R1C1, нажмите кнопку Microsoft Office Изображение кнопки Office , откройте диалоговое окно Параметры Excel, в категории Формулы в разделе Работа с формулами установите или снимите флажок Стиль ссылок R1C1.

К началу страницы

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

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

Тип примера

Пример без имени

Пример с именем

Ссылка

=СУММ(C20:C30)

=СУММ(ПродажиЗаПервыйКвартал)

Константа

=ПРОИЗВЕД(A5;8,3)

=ПРОИЗВЕД(Цена;НСП)

Формула

=СУММ(ВПР(A1;B1:F20;5;ЛОЖЬ); —G5)

=СУММ(объем запасов;—сумма_заказа)

Таблица

C4:G36

=МаксПродажи06

Типы имен

Можно создавать и использовать несколько типов имен.

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

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

Создание и ввод имен

Создание имени, используя:

  • Поле имени в строке формул    Это поле лучше всего использовать для создания имени на уровне книги для выделенного диапазона.

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

  • Диалоговое окно "Создание имени"     Это лучший вариант, когда нужна большая гибкость при создании имен, например, при задании локальной области на уровне листа или создании примечания к имени.

Примечание : По умолчанию имена являются абсолютными ссылками.

Можно ввести имя таким образом.

  • С помощью ввода    Введите имя (например, в качестве аргумента в формуле).

  • С помощью автозавершения формулы    Используйте раскрывающийся список автозавершения формулы, в котором перечислены корректные имена.

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

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

К началу страницы

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

Формула массива может выполнить несколько вычислений, а затем вернуть одно значение или группу значений. Формула массива обрабатывает несколько наборов значений, называемых аргументами массива. Каждый аргумент массива должен включать одинаковое число строк и столбцов. Формула массива создается так же, как и другие формулы, с той разницей, что для ввода такой формулы используются клавиши CTRL+SHIFT+ВВОД. Некоторые встроенные функции являются формулами массива, и для получения правильных результатов их следует вводить как массивы.

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

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

При вводе формулы массива Microsoft Excel автоматически помещает ее в фигурные скобки ({}).

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

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

Формула массива, вычисляющая одно значение

Формула массива, вычисляющая одно значение

При вводе формулы ={СУММ(B2:D2*B3:D3)} в качестве формулы массива, она умножает "Акции" и "Цена" для каждой биржи, после чего складывает результаты этих вычислений друг с другом.

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

Например, по заданному ряду из трех значений продаж (в столбце B) и ряду из трех месяцев (в столбце A) функция ТЕНДЕНЦИЯ определяет продолжение линейного ряда объемов продаж. Для отображения всех вычисляемых значений формула введена в три ячейки столбца C (C1:C3).

Формула массива, вычисляющая несколько значений

Формула массива, вычисляющая несколько значений

Формула "=ТЕНДЕНЦИЯ(B1:B3;A1:A3)", введенная как формула массива, возвращает три значения (22 196, 17 079 и 11 962), вычисленные по трем объемам продаж за три месяца.

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

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

Константы массива могут содержать числа, текст, логические значения, например ИСТИНА или ЛОЖЬ, или значения ошибки, такие как #Н/Д. Различные типы значений могут быть в одной константе массива, например {1;3;4:ИСТИНА;ЛОЖЬ;ИСТИНА}. Числа в массиве могут быть целыми, с десятичной точкой или в экспоненциальном формате. Текст должен быть взят в двойные кавычки, например "Вторник".

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

При форматировании констант массива убедитесь, что:

  • Константы заключены в фигурные скобки ({ }).

  • Столбцы разделяются точкой с запятой (;). Например, чтобы представить значения 10, 20, 30 и 40, введите {10;20;30;40}. Такой массив констант является матрицей размерности 1 на 4 и соответствует ссылке на 1 строку и 4 столбца.

  • Строки разделяются двоеточиями (:). Например, чтобы представить значения 10, 20, 30, 40 и 50, 60, 70, 80, находящиеся в расположенных друг под другом ячейках, можно создать массив констант размерностью 2 на 4, причем строки будут отделены друг от друга двоеточиями, а значения в столбцах — точкой с запятой: {10;20;30;40:50;60;70;80}.

К началу страницы

Были ли сведения полезными?

Отлично! Что-то еще?

Что мы могли бы улучшить?

Спасибо за ваш отзыв!

×