Типы функций в excel: Функции Excel (по категориям)

Содержание

Функции Excel (по категориям) — Служба поддержки Office

БЕССЕЛЬ.I	Возвращает модифицированную функцию Бесселя In(x).
БЕССЕЛЬ.J	Возвращает функцию Бесселя Jn(x).
БЕССЕЛЬ.K	Возвращает модифицированную функцию Бесселя Kn(x).
БЕССЕЛЬ.Y	Возвращает функцию Бесселя Yn(x).
ДВ.В.ДЕС	Преобразует двоичное число в десятичное.
ДВ.В.ШЕСТН	Преобразует двоичное число в шестнадцатеричное.
ДВ.В.ВОСЬМ	Преобразует двоичное число в восьмеричное.
БИТ.И	Возвращает результат операции поразрядного И для двух чисел.
БИТ.СДВИГЛ	Возвращает число со сдвигом влево на указанное число бит.
БИТ.ИЛИ	Возвращает результат операции поразрядного ИЛИ для двух чисел.
БИТ.СДВИГП	Возвращает число со сдвигом вправо на указанное число бит.
БИТ.ИСКЛИЛИ	Возвращает побитовое «исключающее или» двух чисел.
КОМПЛЕКСН	Преобразует коэффициенты при вещественной и мнимой частях комплексного числа в комплексное число.
ПРЕОБР	Преобразует число из одной системы счисления в другую.
ДЕС.В.ДВ	Преобразует десятичное число в двоичное.
ДЕС.В.ШЕСТН	Преобразует десятичное число в шестнадцатеричное.
ДЕС.В.ВОСЬМ	Преобразует десятичное число в восьмеричное.
ДЕЛЬТА	Проверяет равенство двух значений.
ФОШ	Возвращает функцию ошибки.
ФОШ.ТОЧН	Возвращает функцию ошибки.
ДФОШ	Возвращает дополнительную функцию ошибки.
ДФОШ.ТОЧН	Возвращает дополнительную функцию ФОШ, проинтегрированную в пределах от x до бесконечности.
ПОРОГ	Проверяет, не превышает ли данное число пороговое значение.
ШЕСТН.В.ДВ	Преобразует шестнадцатеричное число в двоичное.
ШЕСТН.В.ДЕС	Преобразует шестнадцатеричное число в десятичное.
ШЕСТН.В.ВОСЬМ	Преобразует шестнадцатеричное число в восьмеричное.
МНИМ.ABS	Возвращает абсолютную величину (модуль) комплексного числа.
МНИМ.ЧАСТЬ	Возвращает коэффициент при мнимой части комплексного числа.
МНИМ.АРГУМЕНТ	Возвращает значение аргумента комплексного числа (тета) — угол, выраженный в радианах.
МНИМ.СОПРЯЖ	Возвращает комплексно-сопряженное комплексного числа.
МНИМ.COS	Возвращает косинус комплексного числа.
МНИМ.COSH	Возвращает гиперболический косинус комплексного числа.
МНИМ.COT	Возвращает котангенс комплексного числа.
МНИМ.CSC	Возвращает косеканс комплексного числа.
МНИМ.CSCH	Возвращает гиперболический косеканс комплексного числа.
МНИМ.ДЕЛ	Возвращает частное от деления двух комплексных чисел.
МНИМ.EXP	Возвращает экспоненту комплексного числа.
МНИМ.LN	Возвращает натуральный логарифм комплексного числа.
МНИМ.LOG10	Возвращает десятичный логарифм комплексного числа.
МНИМ.LOG2	Возвращает двоичный логарифм комплексного числа.
МНИМ.СТЕПЕНЬ	Возвращает комплексное число, возведенное в степень с целочисленным показателем.
МНИМ.ПРОИЗВЕД	Возвращает произведение от 2 до 255 комплексных чисел.
МНИМ.ВЕЩ	Возвращает коэффициент при вещественной (действительной) части комплексного числа.
МНИМ.SEC	Возвращает секанс комплексного числа.
МНИМ.SECH	Возвращает гиперболический секанс комплексного числа.
МНИМ.SIN	Возвращает синус комплексного числа.
МНИМ.SINH	Возвращает гиперболический синус комплексного числа.
МНИМ.КОРЕНЬ	Возвращает значение квадратного корня из комплексного числа.
МНИМ.РАЗН	Возвращает разность двух комплексных чисел.
МНИМ.СУММ	Возвращает сумму комплексных чисел.
МНИМ.TAN	Возвращает тангенс комплексного числа.
ВОСЬМ.В.ДВ	Преобразует восьмеричное число в двоичное.
ВОСЬМ.В.ДЕС	Преобразует восьмеричное число в десятичное.
ВОСЬМ.В.ШЕСТН	Преобразует восьмеричное число в шестнадцатеричное.

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

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

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

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

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

3. аргументы. Аргументы могут быть числами, текстом, логическими значениями, такими как Истина или ложь, массивами, значениями ошибок, например #N/a или ссылками на ячейки. Используемый аргумент должен возвращать значение, допустимое для данного аргумента. В качестве аргументов также используются константы, формулы и другие функции.

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

Примечание: Вам не нужно вводить функции во все прописные буквы, например = «ОКРУГЛИТЬ», так как Excel автоматически заполнит ввод имени функции после нажатия кнопки «Добавить». Если вы неправильно наводите имя функции, например = СУМА (a1: A10), а не = сумм (a1: A10), Excel вернет #NAME? Если позиция, которую вы указали, находится перед первым или после последнего элемента в поле, формула возвращает ошибку #ССЫЛКА!.

Ввод функций Excel

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

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

Вложение функций Excel

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

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

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

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

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

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

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

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

=2*3+5

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

=ПЛТ(0,05/12;360;200000)

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

=A1+A2+A3 Вычисляет сумму значений в ячейках A1, A2 и A3.
=КОРЕНЬ(A1) Использует функцию КОРЕНЬ для возврата значения квадратного корня числа в ячейке A1.
=СЕГОДНЯ() Возвращает текущую дату.
=ПРОПИСН(«привет») Преобразует текст «привет» в «ПРИВЕТ» с помощью функции ПРОПИСН.
=ЕСЛИ(A1>0) Анализирует ячейку A1 и проверяет, превышает ли значение в ней нуль.

Элементы формулы

Формула также может содержать один или несколько из таких элементов: функции, ссылки, операторы и константы. («крышка») применяется для возведения числа в степень, а оператор * («звездочка») — для умножения.

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

Константа представляет собой готовое (не вычисляемое) значение, которое всегда остается неизменным. Например, дата 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

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

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

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

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

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

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

Оператор	Описание
: (двоеточие) (один пробел) , (запятая)	Операторы ссылок
–	Знак «минус»
%	Процент
^	Возведение в степень
* и /	Умножение и деление
+ и —	Сложение и вычитание
&	Объединение двух текстовых строк в одну
= < > <= >= <>	Сравнение

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

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

=5+2*3

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

=(5+2)*3

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

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

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

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

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

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

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

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

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

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

Ввод функций

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

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

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

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

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

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

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

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

Ссылка определяет ячейку или диапазон ячеек на листе, а также указывает 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

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

1. Ссылка на лист «Маркетинг».

2. Ссылка на диапазон ячеек с B1 по B10 включительно.

3. Ссылка на лист, отделенная от ссылки на диапазон значений.

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

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

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

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

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

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

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

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

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

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

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

Ссылка	Значение
R[-2]C	относительная ссылка на ячейку, расположенную на две строки выше в том же столбце
R[2]C[2]	Относительная ссылка на ячейку, расположенную на две строки ниже и на два столбца правее
R2C2	Абсолютная ссылка на ячейку, расположенную во второй строке второго столбца
R[-1]	Относительная ссылка на строку, расположенную выше текущей ячейки
R	Абсолютная ссылка на текущую строку

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

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

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

Тип примера	Пример использования диапазонов вместо имен	Пример с использованием имен
Ссылка	=СУММ(A16:A20)	=СУММ(Продажи)
Константа	=ПРОИЗВЕД(A12,9.5%)	=ПРОИЗВЕД(Цена,НСП)
Формула	=ТЕКСТ(ВПР(MAX(A16,A20),A16:B20,2,FALSE),»дд.мм.гггг»)	=ТЕКСТ(ВПР(МАКС(Продажи),ИнформацияОПродажах,2,ЛОЖЬ),»дд.мм.гггг»)
Таблица	A22:B25	=ПРОИЗВЕД(Price,Table1[@Tax Rate])

Типы имен

Существует несколько типов имен, которые можно создавать и использовать.

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

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

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

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

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

Имя можно ввести указанными ниже способами.

Клавиш Введите имя, например, в качестве аргумента формулы.
<c0>Автозавершение формул</c0>. Используйте раскрывающийся список автозавершения формул, в котором автоматически выводятся допустимые имена.

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

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

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

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

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

Например, по заданному ряду из трех значений продаж (в столбце 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: подробная инструкция

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

Работа Мастера функций

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

Переход в Мастер функций

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

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

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

Можно также кликнуть на любую другую кнопку блока инструментов «Библиотека функций». При этом, в выпадающем меню появится список, в самом низу которого есть пункт «Вставить функцию…». Вот по нему и нужно кликнуть. Но, данный способ является ещё запутаннее предыдущего.

Очень простым способом перехода в режим Мастера является нажатие комбинации горячих клавиш Shift+F3. Этот вариант предусматривает быстрый переход без дополнительных «телодвижений». Главный недостаток его состоит в том, что далеко не каждый пользователь способен удержать у себя в голове все комбинации горячих клавиш. Так что для новичков в освоении Excel этот вариант не подходит.

Категории элементов в Мастере

Какой бы способ активации из вышеуказанных вы не выбрали, в любом случае, после данных действий запускается окно Мастера. В верхней части окна расположено поле поиска. Сюда можно ввести наименование функции и нажать кнопку «Найти», чтобы быстрее отыскать нужный элемент и получить доступ к нему.

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

Все функции разделены на следующие 12 категорий:

Текстовые;
Финансовые;
Дата и время;
Ссылки и массивы;
Статистические;
Аналитические;
Работа с базой данных;
Проверка свойств и значений;
Логические;
Инженерные;
Математические;
Определенные пользователем;
Совместимость.

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

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

Выбор функции

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

Аргументы функции

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

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

После того, как все нужные данные введены, жмем на кнопку «OK», тем самым запуская процесс выполнения задачи.

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

После того, как вы нажали на кнопку «OK» Мастер закрывается и происходит выполнение самой функции. Результат выполнения может быть самый разнообразный. Он зависит от тех задач, которые ставятся перед формулой. Например, функция СУММ, которая была выбрана в качестве примера, производит суммирование всех введенных аргументов и показывает результат в отдельной ячейке. Для других вариантов из списка Мастера результат будет абсолютно иным.

Урок: Полезные функции Excel

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

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

Помогла ли вам эта статья?

ДА НЕТ

Функция в функции в Excel

Пример 1: Два условия

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

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

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

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

Пример базируется на корневой функции «ЕСЛИ», поэтому в окне «Вставка функции» выберите именно ее.

В качестве логического выражения записывайте функцию, считающую среднее значение и определяющую, больше ли оно указанного числа. Тогда строка обретет вид СРЗНАЧ(A1:A6)>300, где A1 и A6 — диапазон захватываемых ячеек, > — знак «больше», а 300 — целевое среднее значение, являющееся истинным.

Другая функция, используемая внутри корневой, называется «СУММ» — она и будет выводить сумму чисел при истинном значении, поэтому записывается во втором поле в представлении СУММ(C1:D1:E1:F1).

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

Сейчас функция ЕСЛИ находится в состоянии «истина», а значит, в заданной клетке отобразится сумма указанного диапазона.

Если же условия ложные, всплывет указанное сообщение или 0.

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

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

Подробнее: Мастер функций в программе Microsoft Excel

Пример 2: Три и более условий

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

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

Поставьте знак ; и сразу же впишите новую функцию ЕСЛИ без знака =, где внутри содержится второе условие.

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

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

Для лучшего понимания оставим полную строку формулы с функцией в функции в правильном ее виде =ЕСЛИ(B2=C2;»Не изменилась»;ЕСЛИ(B2C2;»Уменьшилась»))). Если требуется, скопируйте ее и используйте в своих целях, изменив условия и значения.

Помогла ли вам эта статья?

ДА НЕТ

Функции и ошибки в MS Excel — урок. Информатика, 9 класс.

Функция Excel — это заранее определённая формула, которая работает с одним или несколькими значениями и возвращает результат.

Фунции бывают:

Функции баз данных (Database)
Функции даты и времени (Date & Time)
Инженерные функции (Engineering)
Финансовые функции (Financial)
Проверка свойств и значений и Информационные функции (Information)
Логические функции (Logical)
Ссылки и массивы (References and arrays)
Математические и тригонометрические функции (Math & Trig)
Статистические функции (Statistical)
Текстовые функции (Text)

Приведём примеры часто используемых функций:

Функция	Описание
	Выдает значение ИСТИНА, если все аргументы имеют значение ИСТИНА.
НЕ() – NOT()	Меняет на противоположное логическое значение своего аргумента.
ИЛИ() – OR()	Выдаёт значение ИСТИНА, если хотя бы один аргумент имеет значение ИСТИНА.
ИСТИНА() – TRUE()	Вставляет логическое значение ИСТИНА.
ABS() – ABS()	Находит модуль (абсолютную величину) числа.
ACOS() – ACOS()	Вычисляет арккосинус числа.
ACOSH() – ACOSH()	Вычисляет гиперболический арккосинус числа.
ASIN() – ASIN()	Вычисляет арксинус числа.
COS() – COS()	Вычисляет косинус числа.
COSH() – COSH()	Вычисляет гиперболический косинус числа.
ЧЁТН() – EVEN()	Округляет число до ближайшего чётного целого.
EXP() – EXP()	Вычисляет число $e$, возведённое в указанную степень.
ФАКТР() – FACT()	Вычисляет факториал числа.
СРЗНАЧ() – AVERAGE()	Вычисляет среднее арифметическое аргументов.
СЧЁТ() – COUNT()	Подсчитывает количество чисел в списке аргументов.
МАКС() – MAX()	Определяет максимальное значение из списка аргументов.
МИН() – MIN()	Определяет минимальное значение из списка аргументов.
СУММ() – SUM()	Суммирует аргументы.

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

Обрати внимание!

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

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

Ошибка $####$ появляется, когда вводимое число не умещается в ячейке. В этом случае следует увеличить ширину столбца.

Ошибка $#ДЕЛ/0!$ появляется, когда в формуле делается попытка деления на ноль. Чаще всего это случается, когда в качестве делителя используется ссылка на ячейку, содержащую нулевое или пустое значение.

Ошибка $#Н/Д!$ является сокращением термина «неопределённые данные». Эта ошибка указывает на использование в формуле ссылки на пустую ячейку.

Ошибка $#ИМЯ?$ появляется, когда имя, используемое в формуле, было удалено или не было ранее определено. Для исправления определите или исправьте имя области данных, имя функции и др.

Ошибка $#ПУСТО!$ появляется, когда задано пересечение двух областей, которые в действительности не имеют общих ячеек. Чаще всего ошибка указывает, что допущена ошибка при вводе ссылок на диапазоны ячеек.

Ошибка $#ЧИСЛО!$ появляется, когда в функции с числовым аргументом используется неверный формат или значение аргумента.

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

Ошибка $#ЗНАЧ!$ появляется, когда в формуле используется недопустимый тип аргумента или операнда. Например, вместо числового или логического значения для оператора или функции введён текст.

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

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

Список функций Excel

COUNT	Возвращает количество числовых значений в предоставленном наборе ячеек или значений
COUNTA	Возвращает количество непустых пробелов в предоставленном наборе ячеек или значений
COUNTBLANK	Возвращает количество пустых ячеек в заданном диапазоне
COUNTIF	Возвращает количество ячеек (из предоставленного диапазона), которые удовлетворяют заданному критерию
COUNTIFS	Возвращает число ячеек (из предоставленного диапазона), которые удовлетворяют набору заданных критериев (Новое в Excel 2007)
ЧАСТОТА	Возвращает массив, показывающий количество значений из предоставленного массива, которые попадают в указанные диапазоны

PERMUT	Возвращает количество перестановок для заданного количества объектов
PERMUTATIONA 9000 6	Возвращает количество перестановок для заданного числа объектов (с повторениями), которые могут быть выбраны из общего количества объектов (Новое в Excel 2013)

КОНФИДЕНЦИЯ	Возвращает доверительный интервал для среднего значения генеральной совокупности , используя нормальное распределение (заменено на Confidence.Функция Norm в Excel 2010)
CONFIDENCE.NORM	Возвращает доверительный интервал для среднего значения генеральной совокупности с использованием нормального распределения (Новое в Excel 2010 — заменяет функцию достоверности)
CONFIDENCE.T	Возвращает доверительный интервал для среднего значения генеральной совокупности с использованием t-распределения Стьюдента (новое в Excel 2010)

ПРОЦЕНТИЛЬ	Возвращает K-й процентиль значений в заданном диапазоне, где K находится в диапазоне 0 — 1 (включительно) (Заменено процентилем.Inc в Excel 2010)
PERCENTILE.INC	Возвращает K-й процентиль значений в указанном диапазоне, где K находится в диапазоне от 0 до 1 (включительно) (Новое в Excel 2010 — заменяет функцию Percentile )
PERCENTILE.EXC	Возвращает K-й процентиль значений в указанном диапазоне, где K находится в диапазоне 0 — 1 (исключая) (Новое в Excel 2010)
QUARTILE	Возвращает указанный квартиль набора предоставленных чисел на основе значения процентиля 0–1 (включительно) (заменено квартилем.Функция Inc в Excel 2010)
QUARTILE.INC	Возвращает указанный квартиль из набора предоставленных чисел на основе значения процентиля 0–1 (включительно) (Новое в Excel 2010 — заменяет функцию Quartile)
QUARTILE.EXC	Возвращает указанный квартиль набора предоставленных чисел на основе значения процентиля 0 — 1 (исключая) (Новое в Excel 2010)
RANK	Возвращает статистический ранг заданного значения в пределах предоставленный массив значений (заменен на Rank.Функция Eq в Excel 2010)
RANK.EQ	Возвращает режим (наиболее часто встречающееся значение) списка предоставленных чисел (если более одного значения имеют одинаковый ранг, возвращается верхний ранг этого набора) (Новое в Excel 2010 — заменяет функцию Rank)
RANK.AVG	Возвращает статистический ранг заданного значения в предоставленном массиве значений (если более одного значения имеют одинаковый ранг, возвращается средний ранг ) (Новое в Excel 2010)
ПРОЦЕНТРАНГ	Возвращает ранг значения в наборе данных в процентах (от 0 до 1 включительно) (Заменено на Percentrank.Функция Inc в Excel 2010)
PERCENTRANK.INC	Возвращает ранг значения в наборе данных в процентах (от 0 до 1 включительно) (Новое в Excel 2010 — заменяет функцию Percentrank)
PERCENTRANK.EXC	Возвращает ранг значения в наборе данных в процентах (0–1 без учета) (Новое в Excel 2010)

AVEDEV	Возвращает среднее абсолютных отклонений точек данных от их среднего значения
DEVSQ	Возвращает сумму квадратов отклонений набора точек данных от их среднего значения выборки
STDEV	Возвращает стандартное отклонение предоставленного набора значений (которое представляют собой выборку населения) (заменено на Stdev.S-функция в Excel 2010)
СТАНДОТКЛОН.S	Возвращает стандартное отклонение предоставленного набора значений (которые представляют выборку генеральной совокупности) (Новое в Excel 2010 — заменяет функцию Stdev)
СТАНДОТКЛОН	Возвращает стандартное отклонение предоставленного набора значений (которые представляют собой выборку генеральной совокупности), считая текст и логическое значение FALSE как значение 0 и считая логическое значение TRUE как значение 1
STDEVP	Возвращает стандартное отклонение предоставленного набора значений (которые представляют всю генеральную совокупность) (заменено на Stdev.Функция P в Excel 2010)
STDEV.P	Возвращает стандартное отклонение предоставленного набора значений (которые представляют всю генеральную совокупность) (Новое в Excel 2010 — заменяет функцию Stdevp)
STDEVPA	Возвращает стандартное отклонение предоставленного набора значений (которые представляют всю генеральную совокупность), считая текст и логическое значение FALSE как значение 0 и считая логическое значение TRUE как значение 1
VAR	Возвращает дисперсию предоставленный набор значений (которые представляют собой выборку совокупности) (заменено на Var.S-функция в Excel 2010)
VAR.S	Возвращает дисперсию предоставленного набора значений (которые представляют собой выборку генеральной совокупности) (Новое в Excel 2010 — заменяет функцию Var)
VARA	Возвращает дисперсию предоставленного набора значений (которые представляют собой выборку совокупности), считая текст и логическое значение FALSE как значение 0 и считая логическое значение TRUE как значение 1
VARP	Возвращает дисперсия предоставленного набора значений (которые представляют всю генеральную совокупность) (заменено на Var.P-функция в Excel 2010)
VAR.P	Возвращает дисперсию предоставленного набора значений (которые представляют всю совокупность) (Новое в Excel 2010 — заменяет функцию Varp)
VARPA	Возвращает дисперсия предоставленного набора значений (которые представляют всю генеральную совокупность), подсчет текста и логического значения ЛОЖЬ как значение 0 и подсчет логического значения ИСТИНА как значение 1
COVAR	Возвращает ковариацию генеральной совокупности (т.е.е. среднее значение произведений отклонений для каждой пары в двух предоставленных наборах данных) (Заменено функцией Covariance.P в Excel 2010)
COVARIANCE.P	Возвращает ковариацию генеральной совокупности (т. е. среднее значение произведений отклонений для каждого пара в двух предоставленных наборах данных) (Новое в Excel 2010 — заменяет функцию Ковара)
COVARIANCE.S	Возвращает выборочную ковариацию (т. е. среднее значение произведений отклонений для каждой пары в двух предоставленных наборах данных) (Новое в Excel 2010)

ПРОГНОЗ	Предсказывает будущую точку на линейной линии тренда, соответствующей заданному набору значений x и y (заменено на Forecast.Линейная функция в Excel 2016)
FORECAST.ETS	Использует алгоритм экспоненциального сглаживания для прогнозирования будущего значения на временной шкале на основе ряда существующих значений (новое в Excel 2016 — недоступно в Excel 2016 для Mac)
FORECAST.ETS.CONFINT	Возвращает доверительный интервал для значения прогноза на указанную контрольную дату (Новое в Excel 2016 — недоступно в Excel 2016 для Mac)
FORECAST.ETS.SEASONALITY	Возвращает длина повторяющегося шаблона, который Excel обнаруживает для указанного временного ряда (Новое в Excel 2016 — недоступно в Excel 2016 для Mac)
ПРОГНОЗ.ETS.STAT	Возвращает статистическое значение, относящееся к прогнозированию временных рядов (Новое в Excel 2016 — недоступно в Excel 2016 для Mac)
FORECAST.LINEAR	Предсказывает будущую точку на линейной линии тренда, подогнанной к предоставленный набор значений x и y (новый в Excel 2016 (не Excel 2016 для Mac) — заменяет функцию прогноза)
INTERCEPT	Вычисляет наиболее подходящую линию регрессии через предоставленные серии x и y — значения и возвращает значение, при котором эта линия пересекает ось Y
ЛИНЕЙН	Возвращает статистическую информацию, описывающую тенденцию линии наилучшего соответствия, через предоставленную серию значений x и y
SLOPE	Возвращает наклон линии линейной регрессии через предоставленный ряд значений x и y
TREND	Вычисляет линию тренда по заданному набору значений y и возвращает Дополнительные значения y для предоставленного набора новых значений x
GROWTH	Возвращает числа в тенденции экспоненциального роста на основе набора предоставленных значений x и y
LOGEST	Возвращает параметры экспоненциального тренда для предоставленного набора значений x и y
STEYX	Возвращает стандартную ошибку предсказанного значения y для каждого x в линии регрессии для набора предоставленных значений x и y

Функции Excel (по алфавиту) — служба поддержки Office

Функция ABS	Математика и тригонометрия: Возвращает абсолютное значение числа
Функция ACCRINT	Financial: Возвращает накопленные проценты по ценной бумаге, по которой выплачиваются периодические проценты
Функция ACCRINTM	Финансовый: Возвращает накопленные проценты по ценной бумаге, по которой проценты выплачиваются при наступлении срока погашения
Функция ACOS	Математика и тригонометрия: Возвращает арккосинус числа .
Функция ACOSH	Математика и тригонометрия: Возвращает обратный гиперболический косинус числа .
Функция ACOT	Математика и тригонометрия: Возвращает арккотангенс числа .
Функция ACOTH	Математика и тригонометрия: Возвращает гиперболический арккотангенс числа .
АГРЕГАТ функция	Математика и тригонометрия: Возвращает агрегат в списке или базе данных
АДРЕС функция	Поиск и ссылка: Возвращает ссылку в виде текста в одну ячейку на листе
Функция AMORDEGRC	Финансы: Возвращает амортизацию для каждого отчетного периода с использованием коэффициента амортизации
Функция AMORLINC	Финансы: Возвращает амортизацию за каждый отчетный период
И функция	Логический: Возвращает ИСТИНА, если все его аргументы ИСТИНА
АРАБСКИЙ (функция)	Математика и тригонометрия: Преобразует римское число в арабское как число
ОБЛАСТИ (функция ОБЛАСТИ)	Поиск и ссылка: Возвращает количество областей в ссылке
Функция ARRAYTOTEXT	Текст: Возвращает массив текстовых значений из любого указанного диапазона
Функция ASC	Текст: Изменяет полноширинные (двухбайтовые) английские буквы или катакану в строке символов на символы половинной ширины (однобайтовые)
Функция ASIN	Математика и тригонометрия: Возвращает арксинус числа .
Функция ASINH	Математика и тригонометрия: Возвращает обратный гиперболический синус числа .
Функция ATAN	Математические функции и тригонометрия: Возвращает арктангенс числа .
Функция ATAN2	Математические и тригонометрические функции: Возвращает арктангенс от координат x и y
Функция ATANH	Математика и тригонометрия: Возвращает обратный гиперболический тангенс числа .
Функция АВЕДЕВ	Статистический: Возвращает среднее абсолютных отклонений точек данных от их среднего значения
СРЕДНИЙ функция	Статистический: Возвращает среднее значение аргументов
Функция СРЗНАЧ	Статистический: Возвращает среднее значение аргументов, включая числа, текст и логические значения
Функция СРЗНАЧЕСЛИ	Статистический: Возвращает среднее (среднее арифметическое) всех ячеек в диапазоне, которые соответствуют заданному критерию
Функция СРЗНАЧЕСЛИМН	Статистический: Возвращает среднее (среднее арифметическое) всех ячеек, соответствующих нескольким критериям.
БАТТЕКСТ функция	Текст: Преобразует число в текст с использованием денежного формата ß (бат)
ОСНОВНАЯ функция	Математика и тригонометрия: Преобразует число в текстовое представление с заданной системой счисления (основанием)
Функция БЕССЕЛИ	Инжиниринг: Возвращает модифицированную функцию Бесселя In (x)
Функция БЕССЕЛЬ	Инжиниринг: Возвращает функцию Бесселя Jn (x)
БЕССЕЛК функция	Инжиниринг: Возвращает модифицированную функцию Бесселя Kn (x)
БЕССИЛЬНО функция	Инжиниринг: Возвращает функцию Бесселя Yn (x)
БЕТАРАСП функция	Совместимость: Возвращает кумулятивную функцию распределения бета В Excel 2007 это статистическая функция .
БЕТАРАСП (функция БЕТАРАСП)	Статистический: Возвращает кумулятивную функцию распределения бета
БЕТОБР (функция БЕТОБР)	Совместимость: Возвращает обратную кумулятивную функцию распределения для указанного бета-распределения В Excel 2007 это статистическая функция .
БЕТА.ОБР	Статистический: Возвращает обратную кумулятивную функцию распределения для указанного бета-распределения
Функция ДВИГАТЕЛЬ	Инженерное дело: Преобразует двоичное число в десятичное
Функция ДВИР.В.В.	Инженерное дело: Преобразует двоичное число в шестнадцатеричное
Функция БИН2ОКТ	Инженерное дело: Преобразует двоичное число в восьмеричное
Функция БИНОМРАСП	Совместимость: Возвращает индивидуальную вероятность биномиального распределения В Excel 2007 это статистическая функция .
Функция БИНОМ.РАСП	Статистический: Возвращает вероятность биномиального распределения отдельного члена
БИНОМ.РАСП.ДИАПАЗОН функция	Статистический: Возвращает вероятность результата испытания с использованием биномиального распределения
БИНОМ.Функция INV	Статистический: Возвращает наименьшее значение, для которого кумулятивное биномиальное распределение меньше или равно значению критерия
БИТАН (функция БИТАН)	Инженерное дело: Возвращает побитовое И из двух чисел
Функция БИТЛСДВИГ	Инженерное дело: Возвращает число значения, сдвинутое влево на бит shift_amount
Функция БИТР	Инженерное дело: Возвращает поразрядное ИЛИ 2 чисел
Функция БИТРСДВИГ	Инженерное дело: Возвращает число значения, сдвинутое вправо на бит shift_amount
Функция BITXOR	Инженерное дело: Возвращает поразрядное исключающее ИЛИ двух чисел
ВЫЗОВ функция	Надстройка и автоматизация: Вызывает процедуру в библиотеке динамической компоновки или ресурсе кода
ПОТОЛОК (функция ПОТОЛОК)	Математика и тригонометрия: Округляет число до ближайшего целого или до ближайшего кратного значимости
ПОТОЛОК.МАТЕМАТИЧЕСКАЯ функция	Математика и тригонометрия: Округляет число в большую сторону до ближайшего целого или до ближайшего кратного значимости
ПОТОЛОК.ТОЧНАЯ функция	Математика и тригонометрия: Округляет число до ближайшего целого или до ближайшего кратного значимости.Независимо от знака числа, число округляется в большую сторону.
ЯЧЕЙКА функция	Информация: Возвращает информацию о форматировании, расположении или содержимом ячейки Эта функция недоступна в Excel в Интернете.
Функция СИМВОЛ	Текст: Возвращает символ, указанный в кодовом номере
Функция ХИДИСП	Совместимость: Возвращает одностороннюю вероятность распределения хи-квадрат Примечание. В Excel 2007 это статистическая функция .
КИСЛОБР функция	Совместимость: Возвращает значение, обратное односторонней вероятности распределения хи-квадрат Примечание. В Excel 2007 это статистическая функция .
ЧИТАСТ функция	Совместимость: Возвращает тест на независимость Примечание. В Excel 2007 это статистическая функция .
CHISQ.Функция РАСП	Статистический: Возвращает кумулятивную функцию плотности бета-вероятности
ХИСК.РАСП.РТ функция	Статистический: Возвращает одностороннюю вероятность распределения хи-квадрат
CHISQ.Функция INV	Статистический: Возвращает кумулятивную функцию плотности бета-вероятности
ХИСК.ОБР.РТ функция	Статистические функции: Возвращает значение, обратное односторонней вероятности распределения хи-квадрат .
CHISQ.Функция ТЕСТ	Статистический: Возвращает тест на независимость
ВЫБРАТЬ функцию	Поиск и ссылка: Выбирает значение из списка значений
ЧИСТЫЙ функция	Текст: Удаляет все непечатаемые символы из текста
КОД (функция КОД)	Текст: Возвращает числовой код для первого символа в текстовой строке
КОЛОНКА функция	Поиск и ссылка: Возвращает номер столбца ссылки
COLUMNS функция	Поиск и ссылка: Возвращает количество столбцов в ссылке
Функция КОМБИНАР	Математика и тригонометрия: Возвращает количество комбинаций для заданного количества объектов
Функция КОМБИНА	Математика и тригонометрия: Возвращает количество комбинаций с повторениями для заданного количества элементов
КОМПЛЕКСНАЯ функция	Инженерное дело: Преобразует действительные и мнимые коэффициенты в комплексное число
СЦЕПИТЬ функция	Текст: Объединяет текст из нескольких диапазонов и / или строк, но не предоставляет аргументы разделителя или IgnoreEmpty.
СЦЕПИТЬ функция	Текст: Объединяет несколько текстовых элементов в один текст
УВЕРЕННОСТЬ	Совместимость: Возвращает доверительный интервал для среднего значения для генеральной совокупности В Excel 2007 это статистическая функция .
КОНФИДЕНЦИЯ.НОРМ, функция	Статистический: Возвращает доверительный интервал для среднего генерального значения
КОНФИДЕНЦИЯ.Т функция	Статистический: Возвращает доверительный интервал для среднего значения генеральной совокупности с использованием t-распределения Стьюдента
ПРЕОБРАЗОВАТЬ функцию	Инженерное дело: Преобразует число из одной системы измерения в другую
КОРРЕЛЬ функция	Статистический: Возвращает коэффициент корреляции между двумя наборами данных
Функция COS	Математика и тригонометрия: Возвращает косинус числа .
Функция COSH	Математика и тригонометрия: Возвращает гиперболический косинус числа .
Функция СОТ	Математика и тригонометрия: Возвращает гиперболический косинус числа .
COTH функция	Математика и тригонометрия: Возвращает котангенс угла .
Функция СЧЁТ	Статистический: Подсчитывает количество чисел в списке аргументов
Функция СЧЁТ	Статистический: Подсчитывает, сколько значений находится в списке аргументов
СЧИТАТЬПУСТОТЫ функция	Статистический: Подсчитывает количество пустых ячеек в диапазоне
СЧЁТЕСЛИ функция	Статистический: Подсчитывает количество ячеек в диапазоне, удовлетворяющем заданным критериям
СЧЁТЕСЛИМН	Статистический: Подсчитывает количество ячеек в диапазоне, которые соответствуют нескольким критериям
ДНЕЙ КУПЕ	Financial: Возвращает количество дней от начала купонного периода до даты расчетов
ДНЕЙ КУПЕ	Financial: Возвращает количество дней в купонном периоде, который содержит дату расчета.
ДНЕЙКУПОНЧСС	Financial: Возвращает количество дней от даты расчета до даты следующего купона
Функция COUPNCD	Financial: Возвращает дату следующего купона после даты расчета
КОПЧИСЛО (функция СЧЕТЧИК)	Financial: Возвращает количество купонов, подлежащих выплате между датой расчета и датой погашения
Функция COUPPCD	Финансовый: Возвращает дату предыдущего купона до даты расчета
КОВАР функция	Совместимость: Возвращает ковариацию, среднее значение произведений парных отклонений В Excel 2007 это статистическая функция .
COVARIANCE.P функция	Статистический: Возвращает ковариацию, среднее значение произведений парных отклонений
COVARIANCE.S функция	Статистический: Возвращает выборочную ковариацию, среднее значение отклонений произведений для каждой пары точек данных в двух наборах данных
КРИТБИНОМ функция	Совместимость: Возвращает наименьшее значение, для которого кумулятивное биномиальное распределение меньше или равно значению критерия В Excel 2007 это статистическая функция .
Функция CSC

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

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

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

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

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

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

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

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

Примечание: Вам не нужно вводить функции заглавными буквами, например = ОКРУГЛ, поскольку Excel автоматически будет использовать заглавные буквы в имени функции, как только вы нажмете клавишу ВВОД. Если вы неправильно написали имя функции, например = SUME (A1: A10) вместо = SUM (A1: A10), тогда Excel вернет #NAME? ошибка.

Ввод функций Excel

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

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

Вложение функций Excel

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

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

Действительный возврат Когда вложенная функция используется в качестве аргумента, вложенная функция должна возвращать тот же тип значения, что и аргумент. Например, если аргумент возвращает значение TRUE или FALSE, вложенная функция должна возвращать значение TRUE или FALSE. Если функция не работает, Excel отображает #VALUE! значение ошибки.

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

Формулы и функции в Excel

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

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

Например, ячейка A3 ниже содержит функцию СУММ, которая вычисляет сумму диапазона A1: A2.

Введите формулу

Чтобы ввести формулу, выполните следующие действия.

1. Выберите ячейку.

2. Чтобы сообщить Excel, что вы хотите ввести формулу, введите знак равенства (=).

3. Например, введите формулу A1 + A2.

Совет: вместо ввода A1 и A2 просто выберите ячейки A1 и A2.

4. Измените значение ячейки A1 на 3.

Excel автоматически пересчитывает значение ячейки A3.Это одна из самых мощных функций Excel!

Редактировать формулу

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

1. Чтобы изменить формулу, щелкните строку формул и измените формулу.

2. Нажмите Enter.

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

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

Сначала Excel выполняет умножение (A1 * A2). Затем Excel добавляет к этому результату значение ячейки A3.

Другой пример,

Сначала Excel вычисляет часть в скобках (A2 + A3).Затем он умножает этот результат на значение ячейки A1.

Копировать / вставить формулу

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

1. Введите формулу, показанную ниже, в ячейку A4.

2а. Выделите ячейку A4, щелкните правой кнопкой мыши и затем нажмите Копировать (или нажмите CTRL + c)…

… затем выберите ячейку B4, щелкните правой кнопкой мыши и затем щелкните Вставить в разделе «Параметры вставки:» (или нажмите CTRL + v).

2б. Вы также можете перетащить формулу в ячейку B4. Выберите ячейку A4, щелкните в правом нижнем углу ячейки A4 и перетащите его в ячейку B4. Это намного проще и дает точно такой же результат!

Результат. Формула в ячейке B4 ссылается на значения в столбце B.

Вставить функцию

Все функции имеют одинаковую структуру. Например, СУММ (A1: A4). Имя этой функции — СУММ. Часть в скобках (аргументы) означает, что мы передаем Excel диапазон A1: A4 в качестве входных данных. Эта функция складывает значения в ячейках A1, A2, A3 и A4. Непросто запомнить, какую функцию и какие аргументы использовать для каждой задачи. К счастью, в этом вам поможет функция «Вставить» в Excel.

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

1. Выберите ячейку.

2. Нажмите кнопку «Вставить функцию».

Откроется диалоговое окно «Вставить функцию».

3. Найдите функцию или выберите функцию из категории. Например, выберите СЧЁТЕСЛИ в категории «Статистика».

4. Щелкните OK.

Откроется диалоговое окно «Аргументы функции».

5.Щелкните поле Диапазон и выберите диапазон A1: C2.

6. Щелкните поле Criteria и введите> 5.

7. Щелкните OK.

Результат. Функция СЧЁТЕСЛИ подсчитывает количество ячеек, превышающих 5.

Примечание: вместо использования функции «Вставить функцию» просто введите = СЧЁТЕСЛИ (A1: C2, «> 5»). Когда вы придете к: = СЧЁТЕСЛИ (вместо ввода A1: C2, просто выберите диапазон A1: C2.

10 основных функций

Ниже вы можете найти обзор 10 наиболее часто используемых функций Excel (+ дополнительные приемы при создании формул в Excel). Загрузите файл Excel и попробуйте вставить эти функции.

1. СЧЕТ

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

Примечание: используйте COUNTBLANK для подсчета пустых ячеек и COUNTA для подсчета всех непустых ячеек.

2. СУММА

Чтобы суммировать диапазон ячеек, используйте функцию СУММ (см. Второе изображение на этой странице). Вы также можете использовать функцию СУММ в Excel для суммирования всего столбца.

Примечание. Вы также можете использовать функцию СУММ в Excel для суммирования всей строки. Например, = СУММ (5: 5) суммирует все значения в 5-й строке.

3. IF

Функция ЕСЛИ проверяет, выполняется ли условие, и возвращает одно значение, если оно истинно, и другое значение, если ложно.

Объяснение: если оценка больше или равна 60, функция ЕСЛИ возвращает Pass, иначе она возвращает Fail. Помните, чтобы быстро скопировать эту формулу в другие ячейки, щелкните в правом нижнем углу ячейки C2 и перетащите его в ячейку C6.

4. СРЕДНЕЕ

Чтобы вычислить среднее значение группы чисел, используйте функцию СРЕДНЕЕ (здесь нет ракетостроения). Приведенная ниже формула вычисляет среднее значение трех первых чисел в диапазоне A1: A6.

Объяснение: функция НАИБОЛЬШИЙ возвращает константу массива {20,15,10}. Эта константа массива используется в качестве аргумента для функции СРЗНАЧ, давая результат 15.

5. СЧЕТЕСЛИ

Функция СЧЁТЕСЛИ ниже подсчитывает количество ячеек, которые содержат ровно звездочку + последовательность из нуля или более символов.

Объяснение: звездочка (*) соответствует серии из нуля или более символов.Посетите нашу страницу о функции СЧЁТЕСЛИ для получения дополнительной информации и примеров.

6. СУММЕСЛИ

Функция СУММЕСЛИ ниже суммирует значения в диапазоне B1: B5, если соответствующие ячейки в диапазоне A1: A5 содержат ровно круг + 1 символ.

Объяснение: вопросительный знак (?) Соответствует ровно одному символу. Посетите нашу страницу о функции СУММЕСЛИ для получения дополнительной информации и примеров.

7. ВПР

Функция ВПР ниже ищет значение 53 (первый аргумент) в крайнем левом столбце красной таблицы (второй аргумент). Значение 4 (третий аргумент) указывает функции VLOOKUP вернуть значение в той же строке из четвертого столбца красной таблицы.

Примечание. Посетите нашу страницу о функции ВПР, чтобы узнать больше об этой мощной функции Excel.

8. МИН.

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

9. МАКС

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

Примечание. Посетите нашу главу о статистических функциях, чтобы узнать больше о Excel и статистике.

10.SUMPRODUCT

Чтобы вычислить сумму произведений соответствующих чисел в одном или нескольких диапазонах, используйте мощную функцию СУММПРОИЗВ в Excel.

Объяснение: функция СУММПРОИЗВ выполняет следующие вычисления: (2 * 1000) + (4 * 250) + (4 * 100) + (2 * 50) = 3500. Загрузите файл Excel и попробуйте вставить эти функции.

Ссылки на ячейки

Ссылки на ячейки в Excel очень важны.По умолчанию Excel использует относительные ссылки. При копировании формулы относительная ссылка изменяется.

1. См. Формулу в ячейке D2 ниже. Ячейка D2 ссылается на ячейки B2 и C2 (указывает на них). Обе ссылки относительны.

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

Чек:

Заключение: ячейка D3 ссылается на ячейки B3 и C3.Ячейка D4 ссылается на ячейки B4 и C4. Ячейка D5 ссылается на ячейки B5 и C5.

Абсолютная ссылка в Excel относится к фиксированному месту на листе. Когда вы копируете формулу, абсолютная ссылка никогда не изменяется.

3. Ячейка C2 ниже относится к ячейкам B2 и E2. Обе ссылки относительны.

4. Вот что происходит, когда мы копируем формулу из ячейки C2 в ячейку C3.

5. Исправьте ссылку на ячейку E2, поместив символ $ перед буквой столбца и номером строки.

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

Чек:

Вывод: абсолютная ссылка ($ E $ 2) остается неизменной, а относительная ссылка (B2) изменяется на B3, B4, B5, B6 и B7.Посетите нашу страницу об абсолютной ссылке, чтобы узнать больше об этом типе ссылки.

Как использовать функцию ЕСЛИ в Excel

Функция ЕСЛИ используется для выполнения логической проверки и реагирует по-разному в зависимости от того, является ли результат ИСТИННЫМ или ЛОЖНЫМ. Первый аргумент, logic_test , представляет собой выражение, которое возвращает ИСТИНА или ЛОЖЬ. И value_if_true , и value_if_false являются необязательными, но должен быть указан хотя бы один из них.Результатом IF может быть значение, ссылка на ячейку или даже другая формула.

В примере, показанном выше, мы хотим назначить либо «Пройден», либо «Не сдан» на основе результатов теста. Проходной балл 70 или выше. Скопированная формула в D6:

  = ЕСЛИ (C6> = 70, «прошел», «не прошел»)

Перевод: Если значение в C6 больше или равно 70, вернуть «Пройдено». В противном случае верните «Fail».

Логическая последовательность этой формулы может быть обратной.Приведенная ниже формула возвращает тот же результат:

Перевод: Если значение в C6 меньше 70, вернуть «Fail». В противном случае верните «Pass».

Обе приведенные выше формулы при копировании вернут правильные результаты.

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

Другая формула

В результате функция ЕСЛИ может вернуть другую формулу. Например, приведенная ниже формула вернет A1 * 5%, если A1 меньше 100, и A1 * 7%, если A1 больше или равно 100:

Вложенные операторы IF

Функция ЕСЛИ может быть «вложенной».«Вложенное ЕСЛИ» относится к формуле, в которой по крайней мере одна функция ЕСЛИ вложена в другую, чтобы проверить наличие большего количества условий и вернуть больше возможных результатов. Каждый оператор IF должен быть аккуратно «вложен» в другой, чтобы логика была правильной.

Например, следующая формула может использоваться для присвоения оценки, а не результата «прошел / не прошел»:

  = ЕСЛИ (C6 <70, «F», IF (C6 <75, «D», IF (C6 <85, «C», IF (C6 <95, «B», «A»))))

Можно вложить до 64 функций ЕСЛИ.Однако в целом вам следует рассмотреть другие функции, такие как VLOOKUP или HLOOKUP, для более сложных сценариев, поскольку они могут обрабатывать большее количество условий гораздо более оптимизированным образом.

Логические операторы

При построении теста с IF вы можете использовать любой из следующих логических операторов:

Оператор сравнения	Значение	Пример
=	равно	A1 = D1
>	больше	A1> D1
> =	больше или равно	A1> = D1
	менее	A1
	меньше или равно	A1
	не равно	A1D1

Функция ЕСЛИ не поддерживает подстановочные знаки, но вы можете комбинировать ЕСЛИ с СЧЁТЕСЛИ, чтобы получить базовую функциональность подстановочных знаков.

ЕСЛИ с И, ИЛИ

Функцию ЕСЛИ можно комбинировать с функцией И и функцией ИЛИ. Например, чтобы вернуть «ОК», когда A1 находится между 7 и 10, вы можете использовать следующую формулу:

  = ЕСЛИ (И (A1> 7, A1 <10), «ОК», «»)

Перевод: если A1 больше 7 и меньше 10, вернуть «ОК». В противном случае ничего не вернуть ("").

Чтобы вернуть B1 + 10, когда A1 "красный" или "синий", вы можете использовать функцию ИЛИ следующим образом:

  = ЕСЛИ (ИЛИ (A1 = «красный», A1 = «синий»), B1 + 10, B1)

Перевод: если A1 красный или синий, вернуть B1 + 10, иначе вернуть B1.

Дополнительная информация

Дополнительные примеры функций ЕСЛИ см. Ниже.

Банкноты

Для условного подсчета используйте функции СЧЁТЕСЛИ или СЧЁТЕСЛИ.
Для условного суммирования используйте функции СУММЕСЛИ или СУММЕСЛИМН.
Если какой-либо из аргументов IF предоставляется в виде массивов, функция IF оценивает каждый элемент массива. y = EXP (x * y)
e = EXP (1) = = 2.718282…
Комбинаторные функции
Рисунок 2 - Таблица комбинаторных функций
Функции округления
Рисунок 3 - Таблица функций округления
Целочисленные функции
Рисунок 4 - Таблица целочисленных функций
Обратите внимание, что INT ( x ) = TRUNC ( x , 0), когда x ≥ 0 и INT ( x ) = TRUNC ( x , 0) - 1, когда x <0.
Функции случайных чисел
Рисунок 5 - Таблица функций случайных чисел
Сгенерированные случайные числа изменяются каждый раз при регенерации ячейки, содержащей функцию. Чтобы сгенерировать значение случайного числа, которое не изменяется, введите RAND () или выражение, содержащее RAND (), а затем нажмите функциональную клавишу F9, а затем Enter.
Сумма и родственные функции
Для следующих примеров пусть R1 = {4, 6, 7, -3}, R2 = {6, 8, -5, 7}, R3 = {5, «», «A», 6, -1}
Рисунок 6 - Таблица суммирования и связанных функций
Вы также можете подсчитать количество уникальных элементов в диапазоне данных в Excel.Следующая формула массива подсчитывает количество уникальных значений в диапазоне R1 (информацию о COUNTIF см. Ниже).
= СУММ (1 / СЧЁТЕСЛИ (R1; R1))
Единственная проблема заключается в том, что ошибка возникает, если R1 содержит пустую ячейку. Чтобы исправить это, вы можете использовать следующую формулу массива:
= СУММ ((R1 <> ””) / СЧЁТЕСЛИ (R1, R1 & ””))
Если вы замените СУММ на СУММПРОИЗВ, результатом будет обычная формула, а не формула массива, поэтому вам нужно только нажать Enter, а не Ctrl-Shft-Enter.
Чтобы подсчитать количество уникальных числовых ячеек в диапазоне R1, вы можете использовать следующую формулу массива:
= СУММ (ЕСЛИ (ЧАСТОТА (R1; R1)> 0,1))
Еще раз, если вы замените СУММ на СУММПРОИЗВ, результатом будет обычная формула. Фактически, когда SUMPRODUCT имеет только один аргумент, он эквивалентен SUM даже при использовании в формулах массива. Причины, по которым эти формулы дают желаемые результаты, немного сложны и зависят от того, как Excel обрабатывает формулы массива, поэтому для наших целей нет необходимости вдаваться в подробности.
Пакет ресурсов Real Statistics предоставляет дополнительную функцию COUNTU (R1), которая подсчитывает количество уникальных числовых ячеек в диапазоне R1 и COUNTAU (R1), которая подсчитывает количество уникальных ячеек в диапазоне R1 (см. Дополнительные функции Excel) .
Рисунок 7 содержит несколько примеров использования этих формул.
Рисунок 7. Подсчет количества уникальных элементов в диапазоне
Условные функции
IF : самая важная условная функция - IF, которая принимает форму
.
ЕСЛИ (логический-тест; значение-если-истина, значение-если-ложь)
Таким образом, если логический тест дает значение ИСТИНА, тогда значение ячейки, содержащей функцию ЕСЛИ, является значением аргумента 2 ^nd, тогда как если логический тест оценивается как ЛОЖЬ, тогда значение ячейки, содержащей функцию ЕСЛИ - значение аргумента 3 ^-го.
Здесь логический тест может быть построен из логических операторов и операторов сравнения. Например
= ЕСЛИ (A5> G6,…)
= ЕСЛИ (НЕ (A7 = B8 + 7) И (СУММ (E2: E7)> = 0,…)
Кроме того, доступны некоторые специальные логические функции, такие как ISERROR , ISBLANK , ISODD , ISEVEN и ISNUMBER . Например. IF (ISEVEN (A5),…) возвращает значение TRUE, если ячейка A5 содержит четное число, и возвращает значение FALSE, если ячейка A5 не четное.
Инструкции
IF также могут быть вложенными. Например. формула
ЕСЛИ (A3> 8, «ХОРОШО», ЕСЛИ (A3 <0, «ОТРИЦАТЕЛЬНЫЙ», «ПЛОХОЙ»))
возвращает ХОРОШО, если значение в ячейке A3 больше 8, возвращает NEG, если это значение отрицательно, и возвращает ПЛОХО, если A3 имеет значение от 0 до 8 включительно.
IFS : Как описано выше, можно вкладывать операторы IF. Проблема в том, что вложение (и особенно размещение скобок) может усложняться. Excel 2016 представляет новую функцию IFS , которая упрощает такие формулы и принимает форму
IFS ( логический-тест1, значение-если-истина1, логический-тест2, значение-если-истина2,… )
Таким образом, IF (A3> 8, «GOOD», IF (A3 <0, «NEG», «BAD»)) может быть выражено как
= IFS (A3> 8, «ХОРОШО», A3 <0, «ОТРИЦАТЕЛЬНЫЙ», «ИСТИНА», «ПЛОХО»)
Обратите внимание, что эта формула IFS содержит три логических теста, третье из которых - ИСТИНА.
SWITCH : Excel 2016 также предоставляет новую условную функцию, аналогичную IFS, которая принимает одну из следующих форм:
SWITCH ( выражение, значение1, результат1, значение2, результат2,… )
SWITCH ( выражение, значение1, результат1, значение2, результат2,…, результат по умолчанию )
Это эквивалент
IFS ( выражение = значение1, результат1, выражение = значение2, результат2,…, ИСТИНА, # Н / Д!)
IFS ( выражение = значение1, результат1, выражение = значение2, результат2,…, ИСТИНА , результат по умолчанию )
СУММЕСЛИ и СЧЁТЕСЛИ функции: первая из этих функций принимает форму СУММЕСЛИ (R1, критерии , R2), где R2 - это массив потенциальных значений, которые необходимо суммировать, а R1 - массив той же формы и размера. содержащие значения, которые необходимо сопоставить с критериями .Для каждого значения в R1, которое соответствует критерию , соответствующее значение в R2 используется в сумме. Если значения в R1 являются числовыми, критерий может принимать форму константы, такой как 34 или B5, или логического выражения (в кавычках) формы «> 34», «<> 34», «<= 34». или «>» & B5.
Обратите внимание, что вам нужно использовать амперсанд (т. Е. Оператор конкатенации) при создании логических выражений, которые ссылаются на значение ячейки. Таким образом, критерий «<=» & B5 удовлетворяется для всех ячеек в R1, которые меньше или равны значению в ячейке B5.Критерий B5 эквивалентен «=» и B5.
На рисунке 8 представлены некоторые примеры использования функции СУММЕСЛИ. Например. если значение в ячейке A9 изменится на «Разведено», тогда значение в ячейке G7 автоматически изменится на 46.
Рисунок 8 - Примеры функций СЧЁТЕСЛИ и СУММЕСЛИ
СЧЁТЕСЛИ принимает синтаксис СЧЁТЕСЛИ (R1, критерий ). Значение этой функции - это количество элементов в массиве R1, которое удовлетворяет критерию .В то время как для СУММЕСЛИ R1 и R2 обычно являются одномерными массивами (то есть векторами строк или столбцов), для СЧЁТЕСЛИ R1 может быть любым массивом m × n .
Если значения в массиве R1 являются буквенно-цифровыми, критерии принимают такие формы, как «Мужской» или B5. Также можно использовать подстановочные знаки; например «* Ite» выбирает все значения в R1, которые заканчиваются буквами «ite». Выбор осуществляется без учета регистра, т.е. «Мужской» эквивалентен «МУЖЧИНА» или «МАЛЕНЬКОЕ». Вы также можете использовать такие критерии, как «> =» и «M», чтобы выбрать все значения в R1, которые начинаются с буквы M или выше.
Еще несколько примеров:
Рисунок 9 - Дополнительные примеры COUNTIF
СУММЕСЛИМН и СЧЕТЕСЛИ : Эти функции аналогичны приведенным выше функциям. Разница в том, что можно применять несколько критериев. Синтаксис этих функций следующий, где все массивы имеют одинаковую форму и размер:
СУММЕСЛИМН (R, R1, критериев1 , R2, критериев2 ,…)
СУММЕСЛИМН (R1, критериев1 , R2, критериев2 ,…)
Выше указаны два критерия, но можно использовать и другие.Значение СУММЕСЛИМН - это сумма всех элементов в массиве R, которые соответствуют всем критериям. Элемент в R соответствует всем критериям при условии, что соответствующий элемент в R1 соответствует критериям1 , а соответствующий элемент в R2 соответствует критериям2 и т. Д.
Значение СЧЁТЕСЛИМН - это количество элементов, соответствующих всем критериям.
Обратите внимание, что СУММЕСЛИ (R1, критерий , R2) эквивалентен СУММЕСЛИМН (R2, R1, критерий ).
Мы также можем определить СРЕДНЕЛИ и СРЕДНЕМН , используя тот же синтаксис, что и для СУММЕСЛИ и СУММЕСЛИМН.Эти функции вычисляют средние значения элементов, соответствующих критериям.
Excel 2016 также предоставляет MINIFS и MAXIFS с использованием того же синтаксиса, что и для SUMIFS. Эти функции вычисляют минимальные и максимальные значения элементов, соответствующих перечисленным критериям.
Функции поиска в таблице
Чтобы проиллюстрировать функции поиска в таблице, мы используем следующие данные в примерах.
Рисунок 10 - Диапазон данных для примеров
Рисунок 11 - Таблица функций поиска
Если R - вектор-столбец (т.е.е. массив r × 1), тогда ИНДЕКС (R, r ) можно использовать вместо ИНДЕКС (R, r , 1).
Обратите внимание, что первый аргумент функции СМЕЩЕНИЕ должен быть ссылкой на ячейку; это не может быть константой. Таким образом, = OFFSET (4,2,3) приведет к ошибке.
Для наших целей мы используем только ПОИСКПОЗ, где второй параметр - это вектор строки или столбца. Мы не рассматриваем R в такой форме, как G4: J7, только в таких формах, как G4: J4 (вектор-строка) или G4: G8 (вектор-столбец).
Если R - вектор-строка, то ПОИСКПОЗ ( x , R, 0) возвращает номер столбца первого случая x в R.Если x встречается несколько раз, возвращается номер столбца первого совпадения. Если совпадений нет, возвращается # N / A. Вы можете проверить наличие такой ошибки с помощью ISERROR (MATCH ( x , R, 0)). Точно так же, если R - вектор-столбец, то ПОИСКПОЗ ( x , R, 0) возвращает номер строки первого случая x в R.
Обратите внимание, что когда MATCH ищет совпадение, форматы также совпадают. Таким образом, MATCH (6, A1: A5,0) = 4 для приведенного выше примера данных, а MATCH («6», A1: A5,0) = # N / A, что указывает на то, что совпадение не найдено.В тексте регистр не учитывается. Таким образом совпадают «Z» и «z», а также «cat» и «CaT». Таким образом, MATCH (A4: E4, ”z”, 0) = 2, а не 5.
Подстановочные знаки также используются с текстом:? обозначает любой одиночный символ, а * обозначает любую последовательность символов. Таким образом, MATCH («?», C1: C4,0) = 1, а не 3, поскольку «?» распознается как подстановочный знак, а не как знак вопроса.
Наконец, во всех приведенных выше примерах мы устанавливаем третий аргумент MATCH равным нулю. Фактически этот параметр может быть установлен на -1, 0 или 1.Если опущено, значение по умолчанию - 1.
Рисунок 12 - Третий аргумент функции ПОИСКПОЗ
Матричные функции
Excel поддерживает следующие матричные функции. Эти функции более подробно описаны в разделе «Матрицы и итерационные процедуры».
Рисунок 13 - Таблица матричных функций
Excel 2013 представила функцию массива MUNIT ( n ), которая возвращает единичную матрицу n × n .Пользователь предыдущих версий Excel может вместо этого использовать функцию массива реальной статистики IDENTITY ( n ). Пакет ресурсов Real Statistic Resource Pack также предоставляет функцию массива MPROD , где MPROD (R1, R2, R3) = MMULT (R1, MMULT (R2, R3)) и MPROD (R1, R2, R3, R4) = MMULT (MMULT ( R1, R2), MMULT (R3, R4)).

Функции Excel (по категориям) — Служба поддержки Office

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

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

Элементы формулы

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

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

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

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

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

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

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

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

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

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

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

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

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

Ввод функций

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

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

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

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

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

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

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

Типы имен

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

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

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

Мастер функций в Excel: подробная инструкция

Работа Мастера функций

Переход в Мастер функций

Категории элементов в Мастере

Выбор функции

Аргументы функции

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

Помогла ли вам эта статья?

Функция в функции в Excel

Пример 1: Два условия

Пример 2: Три и более условий

Помогла ли вам эта статья?

Функции и ошибки в MS Excel — урок. Информатика, 9 класс.

Список функций Excel

Функции Excel (по алфавиту) — служба поддержки Office

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

Формулы и функции в Excel

Введите формулу

Редактировать формулу

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

Копировать / вставить формулу

Вставить функцию

10 основных функций

1. СЧЕТ

2. СУММА

3. IF

4. СРЕДНЕЕ

5. СЧЕТЕСЛИ

6. СУММЕСЛИ

7. ВПР

8. МИН.

9. МАКС

10.SUMPRODUCT

Ссылки на ячейки

Как использовать функцию ЕСЛИ в Excel

Другая формула

Вложенные операторы IF

Логические операторы

ЕСЛИ с И, ИЛИ

Дополнительная информация

Банкноты

Комбинаторные функции

Функции округления

Целочисленные функции

Функции случайных чисел

Сумма и родственные функции

Условные функции

Функции поиска в таблице

Матричные функции

Добавить комментарий Отменить ответ