8 функций Ехсеl, которые помогут сократить работу вдвое

ОКРУГЛ (англ. ROUND)

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

Дело в том, что Excel, в котором большинство менеджеров создает сметы и другие расчеты по договорам, считает до пятнадцати знаков после запятой, даже если в формате ячейки пользователь видит всего два знака. Бухгалтерские же программы, например, 1С, по умолчанию считают все числа с двумя знаками после запятой. Вот и «едут» копейки в расчетах, порождая дополнительные сложности в работе менеджеров и бухгалтеров.

Но устранить проблему и снизить количество бесполезной работы сотрудников довольно просто. Разработанная таблица в Excel формы смет или приложений к договорам, использовав в расчетах функцию ОКРУГЛ. Или же обучить менеджеров ее использованию.

Функция ОКРУГЛ позволяет округлять расчеты в ячейках Ecxel до нужного количества знаков. В нашем случае, до двух.

Синтаксис

ОКРУГЛ (число; число_разрядов)

Число – значение, которое нужно округлить,

Число разрядов – количество знаков после запятой.

ВПР (англ. VLOOKUP)

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

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

Синтаксис

ВПР (искомое_значение; диапазон_просмотра; номер_столбца; интервальный_просмотр)

Искомое значение – параметр, по которому нужно произвести поиск,

Диапазон просмотра – два или более столбца с данными.

Нужно помнить, что:

1. Искомое значение должно находиться в крайнем левом столбце диапазона.
2. При протягивании функции ВПР диапазон будет смещаться, поэтому надо закрепить его абсолютными ячейками.

Номер столбца – количественный номер столбца в диапазоне относительно крайнего левого столбца.

Интервальный просмотр – определяет, нужно ли искать точное совпадение по параметру, или приблизительное совпадение.

Пример с функцией ВПР

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

Вы сформировали итоговую таблицу, в которой перечислены все номенклатурные позиции, и теперь вам нужно собрать все данные воедино (см. также Power Pivot: как научиться создавать наглядные аналитические отчеты в Excel).

Перенесем данные по  остаткам на складе Точки 1.

Для этого зададим в ячейке B3 функцию ВПР.

ВПР(A3;'точка 1'!$A:$B;2;ЛОЖЬ)

И протянем ее до конца столбца. Остатки по точке 1 перенесены.

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

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

ПОИСКПОЗ (англ. MATCH) и 4. ИНДЕКС (англ. INDEX)

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

Функция ПОИСКПОЗ призвана искать указанное значение в диапазоне и отображать относительный номер строки, в которой находится искомое значение

Синтаксис

ПОИСКПОЗ (искомое_значение; просматриваемый_массив; тип_сопоставления)

Искомое значение – данные, которые нужно найти в массиве,

Массив – область анализируемых данных, таблица.

Тип сопоставления:

0 – поиск первого значения, точно совпадающего с указанным;

1 – поиск наибольшего значения, не превышающего указанное значение;

2 – поиск наименьшего значения, превышающего или равного указанному.

Назначение функции ИНДЕКС направлено на отображение данных в ячейке, которая была найдена по заданным параметрам адреса.

Синтаксис

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

Массив – область анализируемых данных, таблица,

Номер строки – порядковый номер искомой строки,

Номер столбца – порядковый номер искомого столбца (опционально).

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

Пример с функциями ПОИСКПОЗ и ИНДЕКС

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

Перед вами стоит задача быстро найти необходимые цифры продаж по указанной розничной точке и указанной номенклатуре. Можно, конечно искать с помощью фильтров и внимательного просмотра, но если выборка больше чем из ста строк, это будет долго и утомительно.

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

Обратите внимание на строку формулы.

ИНДЕКС (А2:F7 – определили массив, в котором искать; координаты столбца заданы функцией ПОИСКПОЗ ($B$10 – искомые данные; A2:F2 – просмотр по столбцам; 0 –точное совпадение); координаты строки заданы функцией ПОИСКПОЗ ($B$11 – искомые данные; A2:A7 – просмотр по строкам; 0 –точное совпадение).

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

Формулы массива (англ. array formulas)

Формула массива – это ускоритель работы финансиста в разы, потому что она одна изящно заменяет несколько формул, помогая попутно решать задачи, неподвластные общеизвестным функциям Excel (см. также, как построить Диаграмму Ганта в Excel).

Как понятно из названия, формулы массива работают с массивами данных, которые бывают:

1. Одномерными (строка, столбец).
2. Двумерными (таблица).

Формулы массива в свою очередь тоже делятся на те, которые показывают результат в одной ячейке, и те, что выводят результат в виде массива данных.

Синтаксис

{=СУММ(А2:А15*В2:В15)}

{}  – фигурные скобки вызываются только комбинацией клавиш Ctrl+Shift+Enter вместо обычного Enter при вводе формулы;

СУММ – функция суммирования приведена для примера, можно применять любую функцию;

А2:А15 – через двоеточие задается массив данных.

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

Рассмотрим другой, более интересный пример применения формулы массива. Допустим, у вас есть сводный отчет по экономике проектов за период  и вам нужно собрать из него данные по общей EBITDA региона Москва по девелоперу IKEA.

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

Разберем подробнее аргументы формулы.

{=СУММ((С3:С13=С17) – функция просматривает массив в столбце С и выбирает значения равные заданному в ячейке С17. Таким значениям она присваивает «1», остальным «0»;

*(D3:D13=C16) – аналогично функция просматривает массив в столбце D и сравнивает его со значением в ячейке C16. На выходе «1» и «0»;

*L3:L13)} – все значения умножаются на соответствующие значения в столбце L и суммируются. Нетрудно догадаться, что суммироваться будут только те строки, в которых получены две «1», остальные будут умножаться на «0».

Вот такое торжество логики.

Подбор параметра (англ. goal seek)

Эта функция (или, скорее, встроенный прием Excel) позволяет ответить на вопрос: «Какие должны быть исходные данные, чтобы получить желаемый результат?».

Например, вам нужно определить объем начальных инвестиций в стартап, для того, чтобы к n-му периоду объем свободных денежных средств компании был равен, допустим, 1 млн. руб. (предполагается закупка оборудования).

В вашем распоряжении подготовленный Cash flow.

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

Работает это так:

1. Открываем «Данные» – «Работа с данными» – «Анализ «что если» – «Подбор параметра».
2. В область «Установить в ячейке» вставляем ссылку на ячейку с нужной формулой.
3. В области «Значение» записываем искомый результат.
4. В области «Изменяя значение ячейки» вставляем ссылку на ячейку с исходными данными.

Готово! Чтобы получить к концу 12 месяца 1 000 000 на счету, нужно вложить 5,714 млн. рублей на старте.

ПРЕДСКАЗ (англ. FORECAST ) и ТЕНДЕНЦИЯ (англ. TREND)

Следующая группа функций призвана повысить эффективность работы финансиста при формировании прогнозов.

Каким образом вы рассчитываете прогнозные значения? Многие ставят их «на глаз», продвинутые пользователи Excel умеют строить линии тренда и из диаграммы берут итоговое уравнение для расчета.

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

Функция ПРЕДСКАЗ рассчитывает будущие значения параметра y в зависимости от заданного параметра x с использованием линейной регрессии.

Синтаксис

ПРЕДСКАЗ (x; известные_значения_y; известные_значения_x)

X – значение переменной x, для которой нужно найти y

Известные значения y – массив наблюдений по параметру y. Как и всегда в статистике, чем больше массив, тем точнее прогноз.

Известные значения x – массив наблюдений по параметру x. Это обычно год, месяц, номер заказа и прочее.

Пример использования функции ПРЕДСКАЗ

Разберем пример применения функции ПРЕДСКАЗ. У вас есть отчет по выручке в разрезе регионов за 9 лет. Вам нужно составить прогноз продаж по каждому региону на следующие три года.

Напишем функцию ПРЕДСКАЗ для Воронежа

= ПРЕДСКАЗ (А11; В2:В10; А2:А10)

И получим искомый результат: 161,86

Закрепив абсолютные столбцы и строки $A11, B$2:B$10; $A$2:$A$10, протянем формулу на 2019, 2020 годы и на все регионы.

Результат получен за одну минуту.

Функция ТЕНДЕНЦИЯ во многом напоминает ПРЕДСКАЗ, но отличается от него использованием линейного тренда (вида y=ax+b ) для формирования прогноза.

Синтаксис

ТЕНДЕНЦИЯ(известные_значения_y; известные_значения_x; новые_значения_x; константа)

Известные значения y – массив наблюдений по параметру y,

Известные значения x – массив наблюдений по параметру x,

Новые значения x – значения, для которых нужно найти y,

Константа – константа (b) в линейном уравнении, то число, которое является решением уравнения при x = 0.

Пример использования функции ТЕНДЕНЦИЯ

Для таблицы с выручкой в разрезе регионов применим функцию ТЕНДЕНЦИЯ, закрепим абсолютные строки и столбцы, протянем формулу на все периоды и регионы.

Как мы видим, результат прогнозирования с использованием функций ПРЕДСКАЗ и ТЕНДЕНЦИЯ одинаков, значит, выручка по регионам в данном случае подчиняется линейному тренду.

Бонус-трюк с перемещением данных

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

Алгоритм быстрого перемещения данных:

1. Выделите целевой массив.
2. Зайдите в директорию «Найти и выделить» – «Заменить».
3. Замените знак «=» на любой другой редко используемый знак, превратив тем самым формулы в текст.
4. Перенесите массив.
5. Выполните обратную замену.

Готово!

Как защитить данные в Excel

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

Excel – это как раз тот случай, когда десять минут осмысленной работы заменяют час «ручного» труда. Поэтому вложения в изучение Excel для финансиста трудно переоценить. Хорошим вариантом будет прохождение сотрудниками обучающих курсов у специалистов или организация внутрикорпоративного обучения. Поверьте, это в результате сэкономит вам не только массу времени, но и ощутимую долю бюджета.

Больше полезной информации по теме Бюджетирование, читайте по ссылке>>>