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

Формулы представляют собой выражения, по которым выполняются вычисления на листе. Формула начинается со знака равенства (=). Ниже приведен пример формулы, умножающей 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 выполняются в порядке, показанном в следующей таблице. Если формула содержит операторы с одинаковым приоритетом, например, операторы деления и умножения, они выполняются слева направо.

Оператор

Описание

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

(одиночный пробел)

; (точка с запятой)

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

Знак минус (например, –1)

%

Процент

^

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

* и /

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

+ и –

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

&

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

=
< >
<=
>=
<>

Сравнение

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

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

=5+2*3

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

=(5+2)*3

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

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

В начало страницы

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

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

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

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

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

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

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

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

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

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

Ввод функций

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

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

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

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

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

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

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

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

В начало страницы

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

Ссылка указывает на ячейку или диапазон ячеек листа и передает в 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 используют все листы, помещенные между начальным и конечным именами, указанными в ссылке. Например, формула «=СУММ(Лист:Лист13!B5)» суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от Лист до Лист13 включительно.

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

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

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

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

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

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

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

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

  • Удаление конечного листа     Если удалить лист 2 или 6, Microsoft 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 и соответствует ссылке на одну строку и четыре столбца.

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

В начало страницы

Применяется к: Excel 2007



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

Да Нет

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

255 осталось символов

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

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

Ресурсы поддержки

Изменить язык