Функции математические эксель: Математические функции Excel

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

Пример

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

4. Функция СУММЕСЛИ

Функция СУММЕСЛИ возвращает сумму чисел, которые удовлетворяют заданному вами условию . Проще говоря, он рассматривает и вычисляет только сумму значений, удовлетворяющих условию.

СУММЕСЛИ(диапазон, критерии, [сумма_диапазон])

• диапазон:  Диапазон ячеек, из которых вы хотите проверить критерии.
• критерии:  Критерий, который может быть числом, текстом, выражением, ссылкой на ячейку или функцией.
• [sum_range]:  Диапазон ячеек со значениями, которые вы хотите суммировать.

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

Пример

В приведенном ниже примере мы указали A1:A9 в качестве диапазона критериев и B1:B9 в качестве диапазона суммы, после чего мы указали критерии в A12, которые имеют значение C.

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

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

5. Функция ИНДЕКС

Функция ИНДЕКС возвращает значение из списка значений на основе его порядкового номера. Простыми словами, ИНДЕКС возвращает значение из списка значений, и вам нужно указать позицию этого значения.

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

ИНДЕКС(массив, номер_строки, [номер_столбца]) 

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

ИНДЕКС(ссылка, номер_строки, [номер_столбца], [номер_области])

• массив:  диапазон ячеек или константа массива.
• ссылка:  Диапазон ячеек или несколько диапазонов.
• row_number:  Номер строки, из которой вы хотите получить значение.
• [col_number]:  Номер столбца, из которого вы хотите получить значение.
• [номер_области]:  Если вы ссылаетесь на несколько диапазонов ячеек (используя ссылочный синтаксис), укажите число, чтобы ссылаться на диапазон из всех этих.

• Если указаны аргументы row_num и column_num, будет возвращено значение в ячейке на пересечении обоих.
• Если указать row_num или column_num как 0 (ноль), будет возвращен массив значений для всего столбца или строки соответственно.
• Когда номер_строки и номер_столбца выходят за пределы допустимого диапазона, возвращается ошибка #ССЫЛКА!.
• Если area_number превышает указанные вами диапазоны чисел, возвращается #REF!.

Пример 1. Использование МАССИВА для получения значения из списка

В приведенном ниже примере мы использовали функцию ИНДЕКС для получения количества за июнь месяц. В списке Jun находится на 6-й позиции (6-я строка), поэтому я указал 6 в row_number. INDEX вернул в результате значение 1904.

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

Пример 2. Использование REFERENCE для получения значения из нескольких списков

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

Теперь во втором диапазоне мы имеем в виду 5-ю строку и 1-й столбец. ИНДЕКС вернул значение 172, которое находится в 5-й строке во 2-м диапазоне.

6. Функция ВПР

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

ВПР(искомое_значение,табличный_массив,кол_номер_индекса,диапазон_просмотра)

90 041 искомое_значение:  Значение, которое вы хотите найти в столбце. Вы можете сослаться на ячейку, которая имеет значение поиска, или вы можете напрямую ввести это значение в функцию.
• table_array:  Диапазон ячеек, именованный диапазон, в котором вы хотите найти значение.
• col_index_num:  Число представляет номер столбца, из которого вы хотите получить значение.
• range_lookup:  Используйте false или 0 для точного совпадения и true или 1 для соответствующего совпадения. Значение по умолчанию — Истина.

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

Пример

1. Использование функции ВПР для категорий

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

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

Здесь у нас есть два варианта использования.

ПЕРВЫЙ — создать формулу вложения с ЕСЛИ, что занимает немного времени, а ВТОРОЙ вариант — создать формулу с ВПР с подходящим совпадением. И формула будет следующей:

=ВПР(B2,$E$2:$G$5,3,ИСТИНА)

Как это работает

Я получаю значение в ответ из столбца «Примечания».

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

Вот почему мы получили «Хорошо» в комментариях.

2. Обработка ошибок в функции ВПР

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

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

Но я хочу, чтобы вместо ошибки отображалось осмысленное сообщение. Формула будет следующей: =IFNA(VLOOKUP(D6,Sheet3!$A$1:$B$14,2,0)»Not Found»)

Как это работает : IFNA может проверить значение для #N/A и если есть ошибка, вы можете указать значение вместо ошибки.

7. Функция IFNA

Функция IFNA возвращает определенное значение, если возникает ошибка #Н/Д. В отличие от ЕСЛИОШИБКА, он оценивает только ошибку #Н/Д и возвращает указанное вами значение.

IFNA(value, value_if_na)

• value: Значение, которое вы хотите проверить на #N/ Ошибка.
• value_if_na: Значение, которое вы хотите вернуть в случае возникновения ошибки.

• Если вы пропустите указание какого-либо аргумента, IFNA будет рассматривать его как пустую строку («»).
• Если значение представляет собой массив, то он вернет результат в виде массива.
• Все остальные ошибки #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME? и #NULL! будут игнорироваться.

Пример

В функции ВПР #N/A возникает, когда искомое значение не находится в диапазоне поиска, и для этого мы указали осмысленное сообщение, используя IFNA.

Примечание. IFNA введен в Excel 2013, поэтому он недоступен в предыдущих версиях.

8. Функция RAND

Функция RAND возвращает случайное число в диапазоне от 0 до 1. Проще говоря, вы можете сгенерировать случайное число в диапазоне от 0 до 1 (оно обновляет свое значение каждый раз, когда вы вносите изменения в рабочий лист).

RAND()

• В функциях RAND нет аргументов для указания

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

Пример

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

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

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

Связанный: Как быстро генерировать случайные буквы в Excel

9. Функция СУММ

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

• число1 : число, диапазон ячеек, содержащих числа, или отдельная ячейка, содержащая число.
• [число2] : число, диапазон ячеек, содержащих числа, или отдельная ячейка, содержащая число.

• Текстовые значения игнорируются.

Пример

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

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

Если в ячейке, на которую вы ссылаетесь, есть значение ошибки, в результате будет возвращен #N/A.

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

10. Функция ИЛИ

Функция ИЛИ возвращает логическое значение (ИСТИНА или ЛОЖЬ) после проверки заданных вами условий. Проще говоря, вы можете проверить несколько условий с помощью функции И, и она возвращает ИСТИНА, если какое-либо из этих (или всех) условий ИСТИНА, и возвращает ЛОЖЬ, только если все эти условия ложны.

ИЛИ(логическое1, [логическое2], …)

• логическое1:  Условие, которое вы хотите проверить.
• [логический2]:  Дополнительные условия, которые вы хотите проверить.

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

Пример

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

В приведенном ниже примере мы использовали число для получения логических значений в формуле. Вы также можете выполнить вышеуказанное условие в обратном порядке. Вы можете использовать TRUE и FALSE вместо чисел. Функция ИЛИ обрабатывает эти логические значения как числа.

Математические функции Microsoft Excel

В приложении 8 вы найдете описание всех 50  функций Excel в категории «Математика» . Ниже приведен список из 9 самых полезных из них.

Советы
Прочтите другие общие советы по формулам во Введении к этому разделу на

Когда вы указываете в формате ячейки, что вы хотите только 2 десятичных знака, Excel показывает только 2 десятичных знака (округление), НО он по-прежнему использует все десятичные знаки. Например, если в ячейке A1 вы введете 2,1456 и отформатируете ее так, чтобы отображались только 2 десятичных знака, вы увидите 2,15. Теперь, если в ячейке B1 вы напишете формулу = A1 и сделаете формат «Общий», вы увидите, что Excel использует все 4 десятичных знака (2,1456). Вот почему вам нужно будет использовать такие функции, как INT, TRUNC, ROUND, ROUNDUP и ROUNDDOWN, если вы хотите использовать определенное количество десятичных знаков в своих вычислениях.

СУММА

=СУММ(A1,B6,G6) или =СУММ(A1+B6+G6)

В ячейке B2 сводки за год вы хотите просуммировать значения в ячейках B2 каждого из ежемесячных листов. Вы назвали свои листы «Январь», «Февраль». .. и использовали: =Январь!B2+Февраль!B2+Март!B2…+Декабрь!B2
Вы также можете написать это: =СУММ(Январь:Декабрь!B2)

ТРАНК

Я не использую функции INT или ROUNDDOWN, потому что TRUNC делает то же самое и даже больше. Функция TRUNC удаляет десятичные дроби без округления. Если у вас есть 2,2 или 2,7 в ячейке A1, =TRUNC(A1,0) вернет 2. Достаточно интересно, если у вас есть 12 345 в ячейке B1, используя знак минус во втором аргументе TRUNC

КРУГЛЫЙ

Эта функция удаляет десятичные дроби, округляя последнее десятичное число в большую сторону, если следующее десятичное число равно 5 или больше. Таким образом, если у вас есть 4,126 в ячейке A1 и вы используете формулу = ОКРУГЛ (A1,2) , результат будет 4,13, если значение в ячейке A1 равно 4,123, результат будет 4,12.

ОБЗОР

Эта функция делает то же самое, что и функция ОКРУГЛ, но всегда округляет в большую сторону. Таким образом, если у вас есть 4,126 в ячейке A1 и вы используете формулу

АБС

=ABS(A1) вернет 5, если в ячейке A1 у вас есть -5 или 5. Эта функция удаляет знак.

МОД

Модуль — это то, что осталось после деления. =MOD(20,6) равно 2, потому что 3 умножить на 6 в 20, а остальное равно 2. Обратите внимание на использование запятой для разделения аргументов. См. приложение ниже в определении возраста человека.

СУММЕСЛИФ

См. урок 15 – Функция СУММЕСЛИ в Excel

СУММПРОИЗВ
Самый сокровенный секрет в Microsoft Excel

Вот что Excel говорит, что вы можете сделать с СУММПРОИЗВ:

Допустим, у вас есть ряд количеств в ячейках с A1 по A5 и ряд цен за единицу в ячейках с B1 по B5. С помощью СУММПРОИЗВ вы можете рассчитать общий объем продаж по следующей формуле: =СУММПРОИЗВ(A1:A5,B1:B5)

В основном СУММПРОИЗВ суммирует A1, умноженное на B1, плюс A2, умноженное на B2………

За последние 20 лет я использовал СУММПРОИЗВ для целей, представленных в Excel, один или два раза. Но я ежедневно использую СУММПРОДУКТ для решения всевозможных других проблем с бизнес-данными. Это самая мощная и полезная функция в Excel. Прочтите главу 13, полностью посвященную СУММПРОДУКТ

ИТОГО

Один из гигантских шагов (№ 2), который делают пользователи, — это когда они узнают о функциях базы данных в Excel. Когда вы знаете, как фильтровать данные, ПРОМЕЖУТОЧНЫЕ ИТОГО становятся очень интересной функцией.

Функция ПРОМЕЖУТОЧНЫЙ ИТОГ позволяет (среди другие операции) для подсчета, суммирования или рассчитать среднее значение отфильтрованных элементов базы данных. Для функции требуется два аргументы, второй — это диапазон, охватываемый функция и первое число между «1» и «11», которые определяют операцию для быть выполнено (например, «1» для среднего, «2» для подсчета и «9» для сумма).
=ПРОМЕЖУТОЧНЫЙ ИТОГ(9,B2:B45)

SQRT

Извлечение квадратного корня — это нахождение числа, умножение которого само на себя даст число, которое вы проверяете. Извлечение кубического корня – это нахождение числа, которое умножается на себя два раза и дает число, которое вы проверяете. Извлечение четвертого корня — это нахождение числа, умножение которого само на себя 3 раза даст число, которое вы тестируете.

Чтобы извлечь квадратный корень из числа, вы будете использовать следующую формулу:
=КОРЕНЬ(16) , что даст 4, потому что 4, умноженное на 4, равно 16, или
=КОРЕНЬ(A1) , что также даст результат в 4, если значение в ячейке A1 равно 16.

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

ПИТАНИЕ

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

Вы можете заставить функцию СТЕПЕНЬ извлекать квадратный корень, кубический корень и любой другой корень, отправив дробь в качестве второго аргумента. Например, вы можете извлечь квадратный корень из 16 по формуле 9.0587 =СТЕПЕНЬ(16,1/2) , кубический корень с =СТЕПЕНЬ(16,1/3) и так далее.

ОКРУГЛ, СУММА
= ОКРУГЛ(СУММ(A1:A5),2) вернет сумму от A1 до A5, округленную до 2 знаков после запятой.

IF, MOD, TRUNC и &
Сколько десятков в 106 единицах?
С количеством единиц в ячейке A1 формулы в B1:
=TRUNC(A1/12,0) вернут количество полных десятков
эта формула в C1:

Если вы хотите представить результат как «8 десятков и 10 единиц» в одной ячейке, вы будете использовать следующую формулу, сочетающую математические функции и функции Trig и знак амперсанда (&):
=TRUNC(A1/12) & «десятки и» & MOD(A1,12) & «единицы»
Но что, если имеется 96 единиц и вы не хотите, чтобы результат отображался как «8 десятки и 0 единиц», но как «8 десятков». Затем вы будете использовать эту формулу:
=ЕСЛИ(MOD(A1,12)=0,TRUNC(A1/12) & «десятки» ,TRUNC(A1/12) & «дюжины и» & MOD(A1 12) & «единицы»)

INT, TRUNC, MOD  и &
Вы хотите определить возраст человек.