Excel для финансистов: статистические функции

12.04.201812.04.2018 by Ваш Казначей

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

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

[irp posts=”4043″ name=”Excel для финансистов: финансовые функции”] [irp posts=”3994″ name=”Excel для финансистов: полезные горячие клавиши”]

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

Сумма

СУММ (англ. SUM) – возвращает сумму соответствующих элементов: =СУММ (диапазон);
СУММПРОИЗВ (англ. SUMPRODUCT) – возвращает сумму произведений соответствующих элементов заданных массивов: =СУММПРОИЗВ(массив1;[массив2];[массив3];…);
СУММЕСЛИ (англ. SUMIF) – возвращает сумму ячеек, удовлетворяющих заданному условию: =СУММЕСЛИ(диапазон; условие; [диапазон_суммирования]);
СУММЕСЛИМН (англ. SUMIFS) – суммирует все аргументы, удовлетворяющие нескольким условиям: =СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …).

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

ПРОИЗВЕД (англ. PRODUCT) – возвращает произведение элементов: =ПРОИЗВЕД(число1;[число2];…);
СТЕПЕНЬ (англ. POWER) – возвращает результат возведения числа в степень: =СТЕПЕНЬ(число;степень);

! Для возведения числа в степень можно также воспользоваться знаком “^”: =число^(значение степени).

МУМНОЖ (англ. MMULT) – возвращает произведение матриц (матрицы хранятся в массивах). Результатом является массив с таким же числом строк, что и массив 1, и с таким же числом столбцов, что и массив 2: =МУМНОЖ(массив1; массив2).

! Чтобы умножить / разделить каждое число в массиве на выбранное число, нужно:

выделить ячейку со значением, на которое нужно умножить/поделить массив,
нажать «Копировать» (“Ctrl + C” / “Ctrl + Insert”),
выделить диапазон с числами, которые необходимо умножить/ разделить на скопированное число,
нажать «Вставить» (“Paste”) => «Специальная вставка» (“Paste special…”) => «Умножить» (“Multiply”) / «Разделить» (“Devide”) (также можно выбрать «Сложить» (“Add”) или «Вычесть» (“Subtract”).

Среднее

СРЗНАЧ (англ. AVERAGE) – для вычисления простого среднего арифметического значения: =СРЗНАЧ(число1;число2;…);
СРЗНАЧЕСЛИ (англ. AVERAGEIF) – возвращает среднее арифметическое всех ячеек в диапазоне, которые соответствуют данному условию: =СРЗНАЧЕСЛИ(диапазон; условие; диапазон_усреднения);

УРЕЗСРЕДНЕЕ (англ. TRIMMEAN) – возвращает среднее при необходимости отбросить аномальные значения/статистические выбросы: =УРЕЗСРЕДНЕЕ(массив;доля) , где массив – интервал усредняемых значений, доля – доля значений, исключаемых из вычислений;
СРГЕОМ (англ. GEOMEAN) – возвращает среднее геометрическое: =СРГЕОМ(число1;число2;…);
МЕДИАНА(англ. MEDIAN) – возвращает величину, находящуюся в середине ранжированного ряда: =МЕДИАНА(диапазон);

МОДА (англ. MODE) – возвращает наиболее часто встречающееся число в ряде чисел: =МОДА(число1;число2;…).

Разброс данных

ПОРОГ (англ. GESTEP) – возвращает значение 1, если число больше или равно пороговому значению; в противном случае возвращает 0 (например, для определения количества значений, превосходящих некий заданный порог): =ПОРОГ(число;[порог]);
СТАНДОТКЛОН (англ. STDEV) – рассчитывает стандартное отклонение по выборке: =СТАНДОТКЛОН(число1,[число2],…]);
ДИСП (англ. VAR) – оценивает дисперсию по выборке: =ДИСП(число1;число2;…);
МИН (англ. MIN) – возвращает минимальное значение из набора чисел: =МИН(число1;[число2];…);
МАКС (англ. MAX) – возвращает максимальное значение из набора чисел: =МАКС(число1;[число2];…);
НАИБОЛЬШИЙ (англ. LARGE) – возвращает k-ое наибольшее значение в множестве данных: =НАИБОЛЬШИЙ(массив;k);
НАИМЕНЬШИЙ (англ. SMALL) – возвращает k-ое наименьшее значение в множестве данных: =НАИМЕНЬШИЙ(массив;k).

Округление и модуль

ABS – возвращает модуль (абсолютную величину) числа: =ABS(число);
ОКРУГЛ (англ. ROUND) – возвращает число, округленное до указанного количества десятичных разрядов: =ОКРУГЛ(число;число_разрядов);
ОКРУГЛТ (англ. MROUND) – возвращает число, округленное с нужной точностью (например, =ОКРУГЛТ(10,5; 2) округляет число 10,5 до ближайшего большего числа, кратного 2): =ОКРУГЛТ(число;точность);
ОКРУГЛВВЕРХ (англ. ROUNDUP) – возвращает число, округленное до ближайшего большего по модулю значения: =ОКРУГЛВВЕРХ(число;число_разрядов);
ОКРУГЛВНИЗ (англ. ROUNDDOWN) – возвращает число, округленное до ближайшего меньшего по модулю значения: =ОКРУГЛВНИЗ(число;число_разрядов).

Зависимость, соответствие и поиск данных

ВПР (англ. VLOOKUP) – ищет значение в крайнем левом столбце таблицы и возвращает значение ячейки, находящейся в указанном столбце той же строки: =ВПР(искомое значение; диапазон с искомым значением; номер столбца в диапазоне с возвращаемым значением; при желании укажите ИСТИНА для поиска приблизительного или ЛОЖЬ для поиска точного совпадения);
ГПР (англ. HLOOKUP) – выполняет поиск в первой строке массива и возвращает значение указанной ячейки: =ГПР(искомое_значение;таблица;номер_строки;[интервальный_просмотр]);
ВЫБОР (англ. CHOOSE) – выбирает значение из списка по заданному номеру позиции (например, число 3 в качестве аргумента “номер_индекса” возвращает значение третьего аргумента списка): =ВЫБОР(номер_индекса;значение1;[значение2];…);
КОРРЕЛ (англ. CORREL) – возвращает коэффициент корреляции между двумя множествами данных: =КОРРЕЛ(массив1;массив2).