text
Читать свежий номер бесплатно >>
Финансовый директор

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

  • 9 июня 2018
  • 7997
руководитель отдела финансового контроля ООО «Аплеона ХСГ»
8 функций Ехсеl, которые помогут сократить работу вдвое

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

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

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

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

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

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

Синтаксис

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

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

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

Скачайте дополнительный материал к статье:

Иконка PDFExcel поможет превратить бесчеловечные отчеты в удобные сводные таблицы

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

ВПР (англ. VLOOKUP)

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

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

Синтаксис

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:

Иконка галкаВидео-тренинг по Excel для финансового директора

Чем поможет: В программе видео-тренинга собраны 7 полезных приемов работы с Excel для финансового директора.

Иконка галкаКак с помощью Excel организовать совместную работу с отчетами в локальной сети компании

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

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

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

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

Синтаксис

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

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

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

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

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

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

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

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

Синтаксис

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Еще по теме:

Иконка галкаКак сформировать управленческие отчеты на основе бухгалтерских данных в Excel

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

Иконка галкаКак построить интерактивный график для анализа затрат в Excel

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

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

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

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

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

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

Синтаксис

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

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

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

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

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

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

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

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

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

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

{=СУММ((С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.

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

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

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

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

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

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

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

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

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

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

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

Синтаксис

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Синтаксис

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

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

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

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

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

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

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

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

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

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

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

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

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

Готово!

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

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

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

Методические рекомендации по управлению финансами компании

Подписка на статьи

Чтобы не пропустить ни одной важной или интересной статьи, подпишитесь на рассылку. Это бесплатно.

Рекомендации по теме

Школа

Школа

Проверь свои знания и приобрети новые

Записаться

Самое выгодное предложение

Самое выгодное предложение

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

Мы в соцсетях
А еще:
Чтобы скачать документ, зарегистрируйтесь на сайте!

Это бесплатно и займет меньше минуты!

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Я тут впервые
И получить доступ на сайт Займет минуту!
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль

Внимание!
Вы читаете профессиональную статью для финансиста.
Зарегистрируйтесь на сайте и продолжите чтение!

Это бесплатно, и замёт менее 1 минуты!

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Я тут впервые
Присоединяйтесь к выбору Ваших коллег.
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Сайт использует файлы cookie. Они позволяют узнавать вас и получать информацию о вашем пользовательском опыте. Это нужно, чтобы улучшать сайт. Посещая страницы сайта и предоставляя свои данные, вы позволяете нам предоставлять их сторонним партнерам. Если согласны, продолжайте пользоваться сайтом. Если нет – установите специальные настройки в браузере или обратитесь в техподдержку.