Работа с формулами Excel: основные функции

Рассмотрим функции, необходимые для построения финансовой модели организации в Excel, начиная с самых простых. Откройте файл (Приложение 2) листе «Основные функции» видим обычную план-фактную таблицу помесячного отчёта по выручке.

Функция СУММ суммирует заданные аргументы. Аргументами могут быть числа, отдельные ячейки, диапазоны ячеек, значения вложенных функций. Все аргументы должны быть разделены точкой с запятой. Рассмотрим простой случай, суммирование ячеек в диапазоне. Введите в ячейке В14 функцию «=СУММ(В2:В13)», получится итоговое значение плановой выручки. Протяните эту ячейку на две ячейки вправо, чтобы посчитать все итоговые значения.

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

Вариант с двумя аргументами: введите в ячейку А17 функцию «=СУММЕСЛИ(D2:D13;"<0")». Программа посчитает сумму отрицательных значений. Здесь проверялись значения самих аргументов.

Вариант с тремя аргументами: введите в ячейку А18 выражение «=СУММЕСЛИ(B2:B13;">1000000";D2:D13)». Программа посчитает сумму отклонений, проверяя столбец А, но суммируя соответствующие (в тех же строчках) данные столбца С.

Есть более сложный вариант функции СУММЕСЛИ, проверяющий несколько условий и суммирующий значения при выполнении каждого критерия: СУММЕСЛИМН. Введите в ячейку А21 выражение «=СУММЕСЛИМН(C2:C13;B2:B13;">1000000";D2:D13;"<0")». Программа суммирует значения столбца С, проверяя по условиям столбцы В и D. Обратите внимание, что синтаксис существенно изменился.

Функция СЧЁТ действует аналогично функции СУММ, только не суммирует аргументы, а просто считает их количество. Такая функция используется достаточно редко, более применима условная функция СЧЁТЕСЛИ. Введите в ячейку А19 выражение «=СЧЁТЕСЛИ(D2:D13;"<0")», программа посчитает число месяцев, в которых не выполнен план. Доступна также функция СЧЁТЕСЛИМН.

Функция СРЗНАЧ вычисляет среднее значение заданного набора аргументов. Введите в ячейку А20 выражение «=СРЗНАЧ(C2:C13)» для получения усреднённой фактической выручки за месяц. По сути, функция аналогична комбинации функций СУММ и СЧЁТ с одинаковыми аргументами. Доступна также условная функция СРЗНАЧЕСЛИ с синтаксисом, аналогичным СУММЕСЛИ и СЧЁТЕСЛИ. Доступны функции СРЗНАЧЕСЛИМН.

Полезны также функции МИН и МАКС, определяющие минимальные и максимальные значения соответственно. Введите формулы для расчёта минимальной и максимальной выручки в ячейки А22 и А23 самостоятельно. Рассчитать отклонение от плана в следующих двух строках будет сложнее. Воспользуемся промежуточным столбцом «Абсолютное отклонение»: в ячейку E2 введите функцию «=ABS(D2)», теперь можно посчитать известным способом значения для ячеек А24 и А25.

Функции ОКРУГЛ, предназначены для округления численных значений с указанной точностью: первый аргумент – округляемое значение, второе – необходимое число. Функции ОКРВВЕРХ, ОКРВНИЗ, кроме того, округляют до ближайшего большего или меньшего числа соответственно. Кроме того, есть удобная функция ОКРУГЛТ – округление до числа, кратного указанному. Попробуйте поработать с этими функциями в ячейке А37, округляя значение средней выручки до 0,01; 0,1; 10; 1000.

Логические функции

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

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

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