Глава 6. Основные функции Excel

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

Функции вычисления в Excel: СУММ, СУММЕСЛ?, СЧЁТ, СРЗНАЧ, ОКРУГЛ

Рассмотрим функции, необходимые для построения финансовой модели организации в Excel, начиная с самых простых. Откройте файл uchebnik — funkcii. На листе «основные функции» видим обычную план-фактную таблицу помесячного отчёта по выручке.

Функция СУММ суммирует заданные аргументы. Аргументами могут быть числа, отдельные ячейки, диапазоны ячеек, значения вложенных функций. Все аргументы должны быть разделены точкой с запятой. Рассмотрим простой случай, суммирование ячеек в диапазоне. Введите в ячейке В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: ЕСЛ?, ?, ?Л?, НЕ

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

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

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

Практическая работа: логические функции

Прочитайте задание 1 вверху справа на листе «основные функции» файла Uchebnik-funkcii.xls. В нём отмечена одновременность условий, поэтому необходимо использовать функцию ? для проверки двух условий. ?скомая функция расчёта бонуса будет иметь вид: «=ЕСЛ?(?(C2>1000000;C2>=B2);C2*1%;0)». В этом примере функцией ? проверяются два условия для ячеек столбца С, если они оба истинны, тогда значение функции ? тоже истина и функция ЕСЛ? даст ненулевой результат.

Рассмотрим полезную для вёрстки таблиц бюджетов функцию ЕСЛ?ОШ?БКА. Бывает, что функция может при некоторых значениях дать некорректный результат, тогда в итоговой ячейке будет выдано сообщение об ошибке. Это может быть совсем не эстетично: например, ошибка деления на ноль в ещё незаполненной таблице.

Сохраните книгу Excel. Запишите в ячейку А26 формулу «=D14/B14», задайте процентный числовой формат. Пока таблица заполнена, результат есть. Теперь удалите значения в ячейках В2:С13. В ячейках А20 и А26 появились сообщения об ошибках, хотя в рабочей таблице ошибок нет, она ещё не заполнена. Дело в том, что сейчас все значения ячеек фактически заполнены нулями, и в двух формулах появилось недопустимое деление на ноль.

Отмените удаление значений (через меню или клавишами Ctrl+Z), либо переоткройте сохранённый результат. Теперь введите в ячейку А26 формулу «=ЕСЛ?ОШ?БКА(D14/B14;0)». Эта формула имеет два аргумента: первый – что отображать в «нормальной» ситуации, второй – при ошибке. Самостоятельно исправьте аналогичным образом формулу  в ячейке А20. Отметим, что данная функция появилась после Excel версии 2007. В более ранних версиях можно пользоваться аналогичными функциями ЕОШ, ЕНД, либо использовать ЕСЛ? в функциях, которые могут генерировать ошибку.

Функции поиска в Excel: ВПР, ГПР, СМЕЩ

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

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

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

Функция смещения СМЕЩ позволяет получать значения, отстоящие от начальной ячейки (первый аргумент) на заданное количество строк (второй аргумент) и столбцов (третий аргумент). Второй и третий аргумент могут быть целыми числами, в т.ч. отрицательными (если необходимо обратиться к ячейке левее и выше начальной).

При использовании этой функции зачастую возможен выход за рамки вычисляемых таблиц, поэтому её нужно использовать на пару с функцией ЕСЛ?ОШ?БКА.

Практическая работа: функции поиска и смещения

Обратите внимание на табличку «месяц – выполнение плана» внизу слева на листе «основные функции» файла uchebnik — funkcii. Смысл её в том, чтобы при выборе месяца программа показывала соответствующий показатель, это часто бывает удобно на сводных таблицах отчётности.

В ячейке А29 сделайте возможность выбора месяца с января по декабрь. Для этого выберите меню Данные – кнопка Проверка данных, выберите тип данныхСписок и задайте диапазон А2:А13 в строке ?сточник. Теперь значения в этой ячейке могут быть заданы только путём выбора из списка.

В ячейке В29 введите выражение для функции: «=ВПР(A29;A1:F14;6;ЛОЖЬ)».

Разберём подробно. Первый аргумент – что функция ВПР будет искать, в данном случае значение месяца. Поиск будет производиться в таблице A1:F14 (второй аргумент) в первом столбце. При нахождении нужного значения функция вернёт соответствующее значение из шестого столбца (третий аргумент). Четвёртым аргументом указано, что в данном случае интересует точное совпадение.

Для разбора второго примера использования функции ВПР прочитайте условие задачи 2 на этом же листе.

Постройте вспомогательную табличку в произвольном месте из двух столбцов. В первом столбце будут пороговые значения выполнения плана продаж (0%, 95%, 98%, 100%), во втором – соответствующая сумма бонуса. Отформатируйте эту таблицу по вкусу (включая числовой формат для процентных значений), выделите её и задайте имя диапазона (правая кнопка мыши – ?мя диапазона… — задайте произвольное имя, например Условия_бонуса). Теперь переходим к использованию функции ВПР. В ячейке Н2 введите функцию «=ВПР(F2;Условия_бонуса;2)» (используйте то имя диапазона, которое задали). Протяните эту формулу вниз до декабря.

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

Для исследования функции СМЕЩ откройте лист «Смещение». В нём в строчку записаны знакомые значения отгрузок  по месяцам. Теперь по условиям задачи необходимо вычислить значение поступлений от клиентов в зависимости от разных условий платежа: 1, 2 месяца отсрочки или оплата авансом (0 месяцев). ?спользуем функцию СМЕЩ.

Для начала, сделайте возможность выбора значения отсрочки. Встаньте на ячейку В9, меню Данные – кнопка Проверка данных, выберите тип данных – целое число, Значение – между, минимум -0, максимум – 2. Теперь для удобства задайте имя этой ячейке: правая кнопка – ?мя диапазона… — задайте, например, «Отсрочка». Поставьте пока значение 0 – отсрочки нет.

Перейдите к таблице поступлений. В ячейку В12 запишите формулу «=СМЕЩ(B7;0;-Отсрочка)» (обратите внимание, что значение ячейки берётся с минусом). Протяните формулу до декабря (не до итогового значения!). Пока формула работает нормально.

Поставьте значение отсрочки – 2 месяца. Теперь первое значение в таблице поступлений – ошибка (поскольку формула адресовала несуществующую ячейку), а в февраль попал заголовок предыдущей таблицы, итоговое значение не вычисляется. Добавьте функцию ЕСЛ?ОШ?БКА: «=ЕСЛ?ОШ?БКА(СМЕЩ(B7;0;-Отсрочка);0)». Теперь ошибка адресации отсечена, итог вычисляется, но появление текста в таблице портит общую картину. Доработаем функцию, умножив на единицу возвращаемое функцией СМЕЩ значение: «=ЕСЛ?ОШ?БКА(СМЕЩ(B7;0;-Отсрочка)*1;0)». Теперь при умножении текстового значения на единицу возникает ошибка вычисления (текст нельзя умножать на число), которая также отсекается функцией ЕСЛ?ОШ?БКА. Для численных значений программа выдаёт верный результат.

Банковские функции в Excel

Функция ПЛТ в Excel: расчёт кредитных платежей

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

Функция ПЛТ позволяет рассчитать полный аннуитетный платеж (платеж, включающий в себя выплату тела кредита и проценты по нему, размер которого не изменяется в течение всего периода). Первый аргумент функции – банковская ставка в процентах, второй – количество периодов (лет или месяцев), третий – сумма займа. Есть ещё два необязательных аргумента: четвёртый – сумма кредита после периода выплат (обычно считается, что кредит погашается полностью и эта сумма равна нулю), пятый – показатель условия, когда осуществляется очередной платеж – в начале или конце периода, это немного влияет на итоговую величину платежа.

Переключитесь на лист «кредит». Заполните начальные значения, например сумма кредита – 1 млн.руб., срок – 1,5 года, ставка – 24% годовых. Присвойте значениям удобные имена, например Сумма, Срок_кредита, Ставка_кредита. В ячейку В10 введите формулу ежемесячного платежа: «=-ПЛТ(Ставка_кредита/12;Срок_кредита*12;Сумма_кредита)». Обратите внимание, что платеж представляет собой отрицательный поток денег, поэтому Excel выдаёт отрицательное значение, которое мы для удобства отображения меняем в формуле на положительное. Обратите также внимание на корректировки периода: ставку нужно поделить на 12, а срок умножить, т.к. расчётный период должен быть везде одинаковый (месяц). Протяните формулу вниз на соответствующее число месяцев.

Кроме расчёта ежемесячного платежа, финансисту желательно знать сумму процентных платежей (эта информация нужна для точного расчёта налога на прибыль) и остаток долга (для составления прогноза баланса). Для этого используются функции ОСПЛТ и ПРПЛТ, которые для конкретного месяца показывают сумму погашения основного платежа и сумму уплаченных процентов соответственно. Состав аргументов тот же, что в функции ПЛТ, плюс порядковый номер месяца.

В ячейку С10 введите «=-ОСПЛТ(Ставка_кредита/12;A10;Срок_кредита*12;Сумма_кредита)», а в ячейку D10 – «=-ПРПЛТ(Ставка_кредита/12;A10;Срок_кредита*12;Сумма_кредита)». Здесь второй аргумент А10 – порядковый номер месяца. Протяните все формулы вниз, до 18 месяца.

В правом столбце рассчитаем остаток долга. В ячейку Е10 введите «=Сумма_кредита-C10», в ячейку Е11 «=E10-C11» и протяните ячейку Е11 до конца таблицы. В строчке, соответствующей 18 месяцу, должен появиться 0. Теперь можно посчитать итоговые значения аннуитетных платежей, платежей по основной сумме и процентных платежей.

Текстовые функции в Excel

В финансовом моделировании изредка применяются функции работы с текстом. Полезны будут следующие функции: оператор конкатенации & и функция ТЕКСТ.

Оператор конкатенации & объединяет строчки текста в теле формулы и текстовых значений в одну строку.

На листе «основные» файла Задание к разделу 2-1 (функции).xls в произвольной ячейке введите выражение «=»фактическая выручка за «&A2&» составляет «&C2&» рублей»». Программа подставит соответствующие значения из ячеек А2 и С2 и сформирует текстовую фразу. Обратите внимание на оператор конкатенации &, который используется между каждыми «кусочками» фразы, будь то текст или ссылка на ячейку.

Также в произвольном месте введите выражение «=»Среднемесячная выручка равна: «&A20». Результатом будет текст «Среднемесячная выручка равна: 1060833,33333333», так как в ячейке А20 именно такое значение. Чтобы привести фразу к читаемому виду, используем функцию ТЕКСТ. Эта функция имеет два аргумента: первый – ссылка на ячейку с числовым значением, второй – формат, к которому надо это значение преобразовать. Формат задаётся следующим образом:

  • # ###– числа с разделением разрядов;
  • # ###,## – числа с разделением разрядов и с двумя знаками после запятой;
  • #,#% — процентный формат с одним знаком после запятой и т.п.

?справьте выражение следующим образом: «=»Среднемесячная выручка равна: «&ТЕКСТ(A20;»### ###,##»)» и получите искомый результат.

При импорте данных из учётных систем может возникнуть потребность в чистке ненужных символов в текстовых значениях. Для удаления лишних пробелов используется функция СЖПРОБЕЛЫ, которая удаляет:  а) все пробелы перед первым символом текстовой строчки, б) все пробелы более одного между словами и числами, в) все пробелы после последнего символа.

Работа с датами в Excel

Excel хранит данные формата типа «дата» как численное значение дней, начиная с 1 января 1900 г., в этом несложно убедиться, набрав любую дату в формате например ДД/ММ/ГГ и поменяв затем формат ячейки с датой на любой числовой. ?з этого следует, что с датами можно производить обычные арифметические операции сложения и вычитания (чтобы узнать разницу между двумя датами, нужно просто вычесть раннюю дату из поздней).

Важные функции работы с датами:

Функция =СЕГОДНЯ() возвращает текущую дату (скобки в синтаксисе функции обязательны).

Функции ГОД, ДЕНЬ, МЕСЯЦ возвращает численное значение года, месяца и дня заданной аргументом даты соответственно.

Функция Ч?СТРАБДН? определяет количество рабочих дней между двумя датами: «=Ч?СТРАБДН?(начальная.дата;конечная.дата;[праздники])».

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

Аналогично функция РАБДЕНЬ возвращает порядковый номер даты, отстоящей от указанной даты (первый аргумент) на указанное количество дней (второй аргумент) с учётом праздников (необязательный третий аргумент).

Практическая работа: действия с датами

На листе «текст и даты» файла «uchebnik-funkcii.xls» введите свой день рождения в ячейку В7. Рассмотрите выражение в ячейке А8 (=»Сегодня мне «&(ГОД(СЕГОДНЯ())-ГОД(B7))&» лет, «&»…»&»месяцев и «&»…»&» дней «) и доработайте его, заменив многоточия на необходимые выражения. ?спользуйте функции МЕСЯЦ, ДЕНЬ и СЕГОДНЯ.

Рассмотрите второе задание. Введите в ячейку В14 выражение «=РАБДЕНЬ(СЕГОДНЯ();30)»

Предыдущая глава: Глава 5. Работа с большими таблицами. Протягивание. Относительные и абсолютные ссылки

Следующая глава: Глава 7. Сводные таблицы

Содержание учебника: Содержание

 

Leave a Reply

Ваш e-mail не будет опубликован.