1 подписчик

Как пройти собеседование на должность финансового аналитика или менеджера/Формулы Excel

13 декабря 202213 дек 2022

279

5 мин

Оглавление

3 ключевых функции Excel:
1. Функция ВПР (VLOOKUP)
2. Функция СУММЕСЛИ(SUMIF)

Для тех, кто давно работает на финансовой должности не секрет, что необходимо уметь работать с инструментами для финансового моделирования и управления, в частности с Microsoft Excel.

Но в моей практике были случаи, когда опытный бухгалтер удивлялся абсолютно понятным и привычным для меня формулам в Excel:)

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

Постараюсь просто и доступно объяснить как они работают.

P.S. Если вы хотите зарабатывать от 60 000 рублей в месяц, то давайте разбираться вместе)

3 ключевых функции Excel:

1. Функция ВПР (VLOOKUP)

Разберем на реальном тестовом примере для собеседования по частям.

Условие задачи: "Определите цену на дату "23.12.2020"

Согласно условию, нам необходимо найти данные по конкретному показателю, если он индивидуален и не в массиве нет повторений , то нам подойдет формула ВПР.

Важный момент: если в массиве данных несколько одинаковых значений (несколько одинаковых дат, названий и так далее), то формула ВПР выдаст результат, который встретится в поиске впервые. Например, 3 из строк с одинаковой датой, Excel в формуле ВПР выведет 1 строку и только.

Когда запрос содержит несколько показателей, то стоит использовать формулы, которые мы разберем далее.

На языке Excel это функция описывается следующим образом: ВПР(искомое значение; таблица; номер столбца; интервальный просмотр)

Последовательность действий:

1) искомое значение; - то, что мы ищем в массиве данных- "23.12.2020".

=ВПР("23.12.2020"; - такой вид ввода данных подойдет если значение конкретно, например идентификационный номер

2) Далее нам необходимо определить поле таблица;. В формуле ВПР таблица; - те строки и столбцы где мы будем искать.

=ВПР("23.12.2020";B1:H36;

3) Далее необходимо задать номер столбца;

Важный момент! В формуле ВПР номер столбца; означает номер столбца в выделенной таблице, по которому необходимо провести поиск.

В нашему случае это столбец G "Цена".

Например, столбец G, по счету от начала будет 7 (от столба A до G), но в выделенной таблице (B1:H36) столбец G будет 6 по счету. Если не обратить на это внимание, то формула выдаст неверный результат, либо возникнет ошибка #Н/Д.

Таким образом, номер столбца; в формуле необходимо прописать так:

=ВПР("23.12.2020";B1:H36;6;

4) Последнее что нам нужно сделать это заполнить интервальный просмотр)

интервальный просмотр) отвечает за точность результата.

Возможны 2 варианта:

0 - (ЛОЖЬ, т.е. точное значение)
1 - (ИСТИНА, т.е. приблизительное значение)

Как правило, в финансовых расчетах важна точность, поэтому для получения верного результата пишем 0.

5) В конечном счете формула будет выглядеть следующим образом:

=ВПР("23.12.2020";B1:H36;6;0) и ответ будет равен: 56946

* Продвинутый способ =СУММЕСЛИ(D:D;"D3";H:H)

P.S. Когда я работала в аудите, мы с коллегами часто говорили влукапить, когда было необходимо применить эту формулу:)

2. Функция СУММЕСЛИ(SUMIF)

Разберем эту функцию на этом же примере.

Условие задачи: "Определите сумму выручки по Курс 6".

Если мы посмотрим на таблицу, то увидим в столбце D повторяющиеся значения Курс 6. Согласно условию, вне зависимость от других факторов (Дата, период, Бизнес юнит и пр.) нам необходимо вычислить общую сумму по Курсу 6.

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

На языке Excel это функция описывается следующим образом:

СУММЕСЛИ(диапазон; критерий; диапазон суммирования)

Последовательность действий:

1) диапазон; - это где находится то, что нужно найти - в нашем случае, столбец D (Курсы)

=СУММЕСЛИ(D1:D36; - данный ввод четко привязывает формулу к границам таблицы

2) Далее нам необходимо заполнить поле критерий; - то, что нужно найти

=СУММЕСЛИ(D1:D36;"Курс 6";

3) Далее необходимо определить диапазон суммирования) - то, что нужно сложить, чтобы получить ответ

=СУММЕСЛИ(D1:D36;"Курс 6";H1:H36)

В итоге, мы получим следующую формулу

=СУММЕСЛИ(D1:D36;"Курс 6";H1:H36) и ответ: 27943647.

* Продвинутый способ =СУММЕСЛИ(D:D;D3;H:H)

*Чтобы перепроверить себя: профильтруйте столбец D по полю Курс 6 и сложите результат по столбцу H.

3. Функция СУММЕСЛИМН(SUMIF)

Эта функция очень похожа на предыдущую, но она немного сложнее.

Разберем эту функцию на все том же примере.

Условие задачи: "Определите количество продаж по Курс 6 в декабре".

В этом задании задача усложняется, теперь необходимо помимо вычисления по Курсу 6 учесть параметр Период. То есть, нам необходимо просуммировать данные по количеству продаж (столбец F), а так же учесть Период (столбец С) и Курс (столбец D).

Так как у нас есть несколько критериев для суммирования нам необходимо применить формулу СУММЕСЛИМН.

На языке Excel это функция описывается следующим образом: СУММЕСЛИ(диапазон суммирования; диапазон критерия 1; критерий 1; диапазон критерия 2; критерий 2;…)

Последовательность действий:

1) Необходимо определить диапазон суммирования) - то, что нужно сложить, чтобы получить ответ

В нашем случае, это столбец F - количество продаж

=СУММЕСЛИ(F1:F36;

2) Так как у нас есть 2 условия нам необходимо их разбить на 2 критерия: