Финансовые расчеты в Excel

Значительную часть времени многих экономистов или финансистов занимает работа на компьютере с офисным приложением MS Excel. В этой программе есть немалое количество функций, предназначенных для создания отчетов, анализа данных, сведения планов, проведения математических расчетов и много другого. Знание важнейших формул Excel и сочетания различных функций значительно облегчает решение практических задач и сокращает затрачиваемые на это время и усилия.
Функции ИНДЕКС и ПОИСКПОЗ
Функция ЕСЛИ в сочетании с функцией И
Сочетание функций СУММ и СМЕЩ
Функции СУММЕСЛИ и СЧЁТЕСЛИ
Функция МОДА.ОДН

Функции ИНДЕКС и ПОИСКПОЗ

В финансовых расчетах нередко используется сочетание функций ИНДЕКС и ПОИСКПОЗ. Их совместное действие аналогично работе функции ВПР (VLOOKUP), но имеет множество преимуществ по сравнению с ней. Сначала рассмотрим эти функции по отдельности.

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

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

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

  • массив – это блок ячеек, где будет производиться поиск.
  • номер_строки – это порядковый номер строки, в которой находится искомое значение. Является обязательным параметром, если не указан номер столбца.
  • номер_столбца – это порядковый номер столбца, в котором находится нужное значение. Если в формуле не указан номер строки, этот параметр является обязательным.

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

Рассмотрим пример. Имеется таблица с перечнем номеров изделий и их габаритами. Для того, чтобы найти длину изделия №2, нужно написать формулу вида =ИНДЕКС(B2:D6;3;2)
Функция ИНДЕКС
В этом примере результатом работы функции будет являться содержимое ячейки на пересечении третьей строки и второго столбца данного массива.
К сожалению, чаще всего в расчетах номер строки или столбца неизвестен. Тогда на помощь приходит функция ПОИСКПОЗ.

Функция ПОИСКПОЗ

Действие функции ПОИСКПОЗ похоже на действие функции INDEX, но ПОИСКПОЗ возвращает не значение ячейки, а позицию ячейки в указанном диапазоне. В общем случае формула будет иметь вид :

ПОИСКПОЗ (искомое_значение;массив ;[вид_сопоставления]), где

  • искомое_значение – это то, что нужно найти. Здесь может быть не только текстовое или числовое значение, но и логическое, а также ссылка на ячейку.
  • массив – это просматриваемый диапазон ячеек.
  • вид_сопоставления – это необязательный параметр, который может иметь значение «1», «0»или «-1». Он указывает функции, какое значение нужно найти: точное или приблизительное. Если данные в массиве указы по возрастанию, параметр «1» укажет, что надо выбрать максимальное значение, меньшее или равное искомому. Параметр «-1» указывается для убывающего массива. В этом случае функция выберет минимальное значение, большее или равное искомому. Параметр «0» находит первое значение, равное искомому. Именно это значение типа сопоставления используется в сочетании функций ИНДЕКС и ПОИСКПОЗ.

Для иллюстрации работы этой функции рассмотрим пример. В вышеуказанной таблице найдем в столбце «№ изделия», каким по счету будет изделие номер 2. Для этого пишем формулу: =ПОИСКПОЗ (2;B2:B6;0)
Функция ПОИСКПОЗ
Зачем нужно знать позицию элемента в таблице? Оказывается, очень удобно использовать это значение в качестве аргумента функции INDEX.

Сочетание функций ИНДЕКС и ПОИСКПОЗ

Если проанализировать обе функции, становится понятным, что функция INDEX ищет значения ячеек по номеру строки и номеру столбца. В то же время, функция ПОИСКПОЗ находит номера строк и номера столбцов. Таким образом, если использовать эти две функции в одной формуле, ПОИСКПОЗ будет находить относительную позицию искомого значения, а функция INDEX будет использовать эти значения и возвращать содержимое вычисленных ячеек.

Рассмотрим пример. В вышеуказанной таблице найдем ширину изделия под номером 3. Для этого используем такую формулу: =ИНДЕКС($D$2:$D$6;ПОИСКПОЗ(3;$B$2:$B$6;0))
Функции ИНДЕКС и ПОИСКПОЗ
Рекомендуется использовать абсолютные ссылки для этих формул, чтобы не сбивались диапазоны поиска при копировании формул.

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

Во-вторых, при использовании функции ВПР нельзя удалять или добавлять столбцы в таблицу. В противном случае результат работы формулы будет некорректным. Это происходит потому, что синтаксис этой функции предполагает указание всего диапазона и конкретного номера столбца, из которого будут браться данные. При использовании функций ИНДЕКС и ПОИСКПОЗ можно удалять или добавлять сколько угодно столбцов.

В-третьих, при использовании сочетания функций ИНДЕКС и ПОИСКПОЗ не существует ограничения на размер искомого значения, в то время как функция ВПР ограничивает количество символов искомого значения до 255 знаков.

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

Функция ЕСЛИ в сочетании с функцией И

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

=ЕСЛИ (логическое_выражение;значение_если_истина;значение_если_ложь), где

  • логическое_выражение – это данные, которые нужно проверить и условия проверки. Например, А2>10.
  • значение_если_истина – это запись, которая появится, если значение ячейки удовлетворяет заданному условию.
  • значение_если_ложь – это запись, которая появится, если значение ячейки не удовлетворяют заданному условию.

Многие пользователи, которые выполняли сложные финансовые расчеты, знают, как трудно разобраться в формулах, использующих вложенные циклы с использованием оператора ЕСЛИ. Оказывается, эти формулы можно упростить, если использовать функцию ЕСЛИ в сочетании с функциями И / ИЛИ. Сочетание функций И и ЕСЛИ работает следующим образом. ЕСЛИ А=1 И А=2, формула возвращает значение В, в противном случае – значение С. Для функции ИЛИ формула работает немного не так. ЕСЛИ А=1 ИЛИ А=2, то формула возвращает значение В, иначе – значение С.

Рассмотрим пример. Создадим формулу, которая проверяет содержимое ячейки С2, равное 110. Если число находится в диапазоне от 90 до 300, результат будет равен 1, в противном случае 0. Формула будет иметь следующий вид: =ЕСЛИ(И(C2>=C4;C2<=C5);C7;C8)
Функция ЕСЛИ
Как видно из рисунка, в ячейку с результатом работы функции будет помещено значение, равное 1. Число 110 действительно находится в интервале от 90 до 300.
Итоговое значение может быть не только числом, но и текстом, например словами «ДА» или «Нет» или любой другой фразой.

Сочетание функций СУММ и СМЕЩ

Функция СМЕЩ сама по себе используется редко, но сочетание ее с другими функциями может приносить весьма неплохие результаты. Например, совместное применение функций СУММ и СМЕЩ позволяет создавать довольно сложные формулы при создании динамической функции, которая суммирует переменное количество ячеек. Для решения этой задачи используется функция СУММ, а вместо конечной ячейки указывается функция СМЕЩ, то есть формула становится динамической.

Полученная формула будет иметь такой вид:

= СУММ (начало_диапазона: СМЕЩ (ссылка, количество_строк, количество_столбцов)), где

  • начало_диапазона – это начальная точка диапазона ячеек, используемая функцией СУММ.
  • ссылка – это ссылка на ячейку, которая используется для вычисления конечной точки диапазона.
  • количество_строк – это количество строк, используемое при расчете смещения ячеек. Это значение может быть положительным, отрицательным и равным нулю.
  • количество_столбцов – это количество столбцов справа или слева заданной ссылки на ячейку. Используется при расчете смещения. При смещении налево это значение отрицательно. При смещении направо значение положительно. Если вычисляемые данные находятся в том же столбце, то этот параметр равен нулю.

Рассмотрим пример. Имеется таблица с порядковыми номерами дней месяца и доходами, полученными в каждый из дней. Ежедневно информация в таблице обновляется путем добавления строки с доходом, полученным за день. Составим формулу в итоговой ячейке после четвертого дня продаж: =СУММ(B2:СМЕЩ(B6;-1;0))
Функция СУММ
Для того, чтобы добавить информацию о пятом дне продаж, нужно добавить пустую строку после четвертого дня и внести в нее нужную информацию. При этом формула примет вид: =СУММ(B2:СМЕЩ(B7;-1;0)), а суммарный доход увеличится на размер дохода, полученного в пятый день.

Функции СУММЕСЛИ и СЧЁТЕСЛИ

Эти две функции очень часто используются в финансовых расчетах. СУММЕСЛИ находит сумму в заданном диапазоне ячеек по определенному условию. СЧЁТЕСЛИ подсчитывает все ячейки, соответствующие заданному условию.

Функция СУММЕСЛИ имеет такую структуру:

    • СУММЕСЛИ (диапазон; критерий; диапазон_суммирования), где
  • диапазон – это массив ячеек, в котором будет производиться проверка соответствия заданным критериям.
  • критерий – это условие для отбора ячеек. Это может быть число, текст, выражение или ссылка на ячейку.
  • диапазон_суммирования – это необязательный параметр. Если его не указывать, суммирование будет производиться с учетом аргумента «Диапазон».

Действие этой функции легко понять на примере. Имеется таблица со списком товаров и их количеством. Нужно найти количество «Товара 1». Формула для подсчета будет иметь следующий вид: =СУММЕСЛИ(B2:B7;”Товар 1″;C2:C7)
Функция СУММЕСЛИ
В этом же примере можно подсчитать количество товаров без учета «Товара 1» по такой формуле: =СУММЕСЛИ(B2:B7;”<>Товар 1″;C2:C7)

Функция СЧЁТЕСЛИ имеет следующую структуру:

СЧЁТЕСЛИ(диапазон; критерий), где диапазон и критерий аналогичны функции СУММЕСЛИ.

В том же примере подсчитаем количество строк «Товар 1». Формула для подсчета будет иметь вид: =СЧЁТЕСЛИ(B2:B7;”Товар 1″).
Функция СЧЁТЕСЛИ
С помощью этой формулы можно подсчитать количество строк, удовлетворяющих некоторым условиям. Например, чтобы в данной таблице подсчитать число строк с количеством товаров больше 10, нужно создать такую формулу: =СЧЁТЕСЛИ(C2:C7;”>10″).

Функция МОДА.ОДН

Статистическая функция МОДА. ОДН и ее устаревший вариант МОДА находит максимально часто встречающееся значение в диапазоне (массиве) данных и возвращает это значение.

Синтаксис функции: =МОДА((число1;[число2];…), где

  • число 1 – это обязательный аргумент, представляющий собой число , ссылку на числовую ячейку, массив или диапазон ячеек.
  • число 2 – необязательный аргумент. Таких аргументов может быть от 1 до 255.

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

Для чего можно использовать эту функцию в финансовых расчетах? Например, для того, чтобы на основании сводных данных выяснить, какие товары покупаются чаще всего. Критерием отбора может быть цена изделия, размер, объем, габариты и так далее. В качестве иллюстрации рассмотрим таблицу, в которой отражены продажи товаров с указанием даты продажи, размера изделия и его цены. Для того, чтобы выяснить, каких размеров изделия продаются чаще всего, используем формулу: =МОДА(C2:C6)
Функция МОДА
По рисунку видно, что изделия с размером 36 пользуются наибольшим спросом. Таким образом, функция МОДА определяет наиболее часто случающееся событие в диапазоне событий.

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