10 популярных базовых функций Excel для финансиста

Основные функции, используемые в Excel

Функция СУММ (SUM)

СУММ (Массив 1, Массив 2…..); SUM(Arr1, Arr2…..)

Показывает сумму всех аргументов внутри формулы.

Пример: СУММ(1;2;3)=6 или СУММ(А1;B1;C1), т.е. сумма значений в ячейках.

Функция ПРОИЗВЕД (PRODUCT)

ПРОИЗВЕД (Массив 1, Массив 2…..); PRODUCT(Arr1, Arr2…..)

Выполняет умножение аргументов.

Пример: ПРОИЗВЕД(1;2;3)=24 или ПРОИЗВЕД(А1;B1;C1), т.е. произведение значений в ячейках.

Функция ЕСЛИ (IF)

ЕСЛИ (Выражение 1; Результат ЕСЛИ Истина, Результат ЕСЛИ Ложь);IF(Expr1, ResultIFTrue, ResultIFFalse)/

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

Пример: А15=1. Тогда, =ЕСЛИ(А15=1;2;3)=2. Если поменять значение ячейки А15 на 2, тогда получим: =ЕСЛИ(А15=1;2;3)=3/

С помощью функции ЕСЛИ строят древо решения.

Формула для древа, будет следующая:

ЕСЛИ(А22=1; ЕСЛИ(А23<0;5;10); ЕСЛИ(А24<0;8;6)). ЕСЛИ А22=1, А23=-5, А24=6, то возвращается результат 5.

Функция СУММПРОИЗВ (SUMPRODUCT)

СУММПРОИЗВ(Массив 1; Массив 2;…..) SUMPRODUCT(Array1; Array2;…..)

Умножает соответствующие аргументы заданных массивов и возвращает сумму произведений.

Пример: найти сумму произведений

Находим произведения

ПРОИЗВ1 =1*2*3=6

ПРОИЗВ2 =4*5*6=120

ПРОИЗВ3 =7*8*9=504

Сумма произведений равна 6+120+504=630

Эти расчеты можно заменить функцией СУММПРОИЗВ.

= СУММПРОИЗВ(Массив 1; Массив 2; Массив 3)

Функция СРЗНАЧ (AVERAGE)

СРЗНАЧ(Массив1; Массив2;…..) AVERAGE(Array 1; Array 2;…..)

Рассчитывает среднее арифметическое всех аргументов.

Пример: СРЗНАЧ (1; 2; 3; 4; 5)=3

Функция МИН (MIN)

Функция МИН (Массив 1; Массив 2;…..) MIN(Array1; Array2;…..)

Возвращает минимальное значение массивов.

Пример: МИН(1; 2; 3; 4; 5)=1

Функция МАКС (MAX)

МАКС (Массив 1; Массив 2;…..) MAX(Array1; Array2;…..)

Обратная функции МИН. Возвращает максимальное значение массивов.

Пример: МАКС(1; 2; 3; 4; 5)=5

Функция НАИМЕНЬШИЙ (SMALL)

НАИМЕНЬШИЙ (Массив 1; Порядок k) SMALL(Array1, k-min)

Возвращает k наименьшее число после минимального. Если k=1, возвращаем минимальное число.

Пример: В ячейках А1;A5 находятся числа 1;2;6;5;10. Результат функции =НАИМЕНЬШИЙ (A1;A5) при разных k:

k=1; результат =1

k=2; результат=3

k=3; результат=2

Функция НАИБОЛЬШИЙ (LARGE)

НАИБОЛЬШИЙ (Массив 1; Порядок k) LARGE(Array1, k-min)

Возвращает k наименьшее число после максимального. Если k=1, возвращаем максимальное число.

Пример: В ячейках А1;A5 находятся числа 1;3;6;5;10. Результат функции = НАИБОЛЬШИЙ (A1;A5) при разных k:

k=1; результат =10

k=2; результат=6

k=3; результат=5

Функция ВПР(VLOOKUP)

ВПР(искомое значение; таблица; номер столбца; {0 (ЛОЖЬ, т.е. точное значение);1(ИСТИНА, т.е. приблизительное значение)}) VLOOKUP(lookup value, table, column number. {0;1})

Ищет значения в столбцах массива и выдает значение в найденной строке и указанном столбце.

Пример: Есть таблица находящаяся в ячейках А1;С4

Нужно найти (ищем в ячейку А6):

1. Возраст сотрудника Иванова (3 столбец)

2. ВУЗ сотрудника Петрова (2 столбец)

Составляем формулы:

1. ВПР(А6; А1:С4; 3;0) Формула ищет значение «Иванов» в первом столбце таблицы А1;С4 и возвращает значение в строке 3 столбца. Результат функции – 22

2. ВПР(А6; А1:С4; 2;0) Формула ищет значение «Петров» в первом столбце таблицы А1;С4 и возвращает значение в строке 2 столбца. Результат функции – ВШЭ

Функция ИНДЕКС(INDEX)

ИНДЕКС(Массив;Номерстроки;Номерстолбца); INDEX(table, row number, columnnumber)

Ищет значение пересечение на указанной строки и столбца массива.

Пример: Есть таблица находящаяся в ячейках А1;С4

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

«Петров» расположен на пересечении 3 строки и 1 столбца, соответственно, формула принимает вид: =ИНДЕКС(А1;С4;3;1).

Функция СУММЕСЛИ(SUMIF)

СУММЕСЛИ(диапазон для критерия; критерий; диапазон суммирования)SUMIF(criterionrange; criterion; sumrange)

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

Пример: в ячейках А1;C5

Найти:

1. Количество столовых приборов сделанных из серебра.

2. Количество приборов <=15.

Решение:

1. Выражение =СУММЕСЛИ(А1:C5;«Серебро»; В1:B5). Результат =40 (15+25).

2. =СУММЕСЛИ(В1:В5;« <=» & 15; В1:B5). Результат = 25(15+10).

Функция СУММЕСЛИМН(SUMIFS)

СУММЕСЛИ(диапазон суммирования; диапазон критерия 1; критерий 1; диапазон критерия 2; критерий 2;…) SUMIFS(criterion range; criterion; sumrange; criterion 1; criterion range 1; criterion 2; criterion range 2;)

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

Пример: в ячейках А1;C5 есть следующие данные

Найти:

1. Количество столовых приборов сделанных из серебра, единичное количество которых <=20.

Решение:

1. Выражение =СУММЕСЛИМН(В1:В5; С1:С5; «Серебро»; В1:B5;« <=» & 20). Результат = 15.

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

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

https://clck.ru/GtqAN