Значительную часть времени многих экономистов или финансистов занимает работа на компьютере с офисным приложением 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 документам при помощи специальных программ.