Нестандартные решения в Excel для повседневных задач финансиста

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

Наглядный отчет о движении денежных средств

Хорошей иллюстрацией к отчету о движении денежных средств может стать каскадная диаграмма отклонений (см. рис. 1). Она характеризует динамику чистого денежного потока. Входящий остаток на январь у компании был в размере 100 млн рублей. За месяц чистый денежный поток оказался отрицательным – 10 млн рублей, а в феврале – еще –30 млн рублей.

Рисунок 1. Построение каскадной диаграммы отклонений

Разобрались? Такие диаграммы очень удобны, но не входят в число стандартных. Вот как можно восполнить этот пробел:

• сначала готовим исходные данные – дополнительную таблицу, содержащую четыре столбца (см. рис. 1): дата (месяц), положительный и отрицательный чистые денежные потоки, а также вспомогательный столбец – остаток денежных средств на конец месяца (первое значение в нем – входящий остаток);
• выделяем таблицу (диапазон A1:D13) и строим на ее основе гистограмму с накоплением (на вкладке «Вставка» выбрать пункт «Гистограмма», далее – «Гистограмма с накоплением»);
• кликаем правой клавишей мышки по нижнему ряду данных диаграммы и делаем его невидимым. Для этого нужно выбрать в контекстном меню команду «Формат ряда данных» (на рис. 2). В открывшемся окне в категории «Заливка» указать «Нет заливки», в категории «Цвет линий» – «Нет линий». Остается нажать «ОК» – диаграмма готова.

Рисунок 2. Выбор невидимой границы и прозрачной заливки

Оформление отчетов в Excel: диаграммы и графики

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

Скачать подборку диаграмм и графиков

Диаграмма Ганта в Excel

Самый популярный инструмент планирования и контроля сроков работ по тем или иным проектам – диаграмма Ганта. Но использовать специализированные решения, например MS Project, не всегда удобно. Лучше построить диаграмму Ганта в Excel. Самый простой способ – рисовать ее с помощью условного форматирования.

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

Рисунок 3. Диаграмма Ганта

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

• даты по горизонтали (на рис. 3 от ячейки G1 вправо по строке) зависят от сроков самого проекта. Для краткосрочных проектов, например, это один-два дня, для долгосрочных – неделя, месяц или более. Для универсальности можно указать шаг временной шкалы в отдельной ячейке, а потом ссылаться на нее при разметке дат;
• начало первого этапа (на рис. 3 ячейка B2) задается вручную;
• значение задержки по этапу определяет, через какое время пос­ле завершения предыдущего этапа начинается следующий. Она может быть как положительной (пауза между этапами), так и отрицательной (следующий этап выполняется одновременно с предыдущим). Технически это реализовано с помощью таких формул, как указано на рисунке 4.

Рисунок 4. Исходная таблица для диаграммы Ганта

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

Первое, что нужно сделать, – выделить диапазон таблицы, непосредственно предназначенный для отображения диаграммы Ганта (в примере его верхний левый угол – ячейка G2).

Затем выбрать на вкладке «Главная» команду «Условное форматирование», кликнуть по пункту «Создать правило», затем «Использовать формулу для определения форматируемых ячеек». Понадобится сформулировать всего два правила – заливать, например, синим цветом интервал, где работы уже завершены, голубым – еще выполняются. Эти условия задаются с помощью формул типа:

И(G$1>=$B2;G$1<=($B2+$C2*$F2-1)),

И(G$1>=$B2;G$1<=$E2),

где И – это логическая функция, проверяющая все заданные в аргументах (перечисленных в скобках через точку с запятой) критерии и требующая обязательного выполнения каждого из них. Например, условие G$1>=$B2 – не что иное, как проверка того, позже ли дата текущей ячейки (G1), чем дата начала работ (B2), а G$1

Стоит обратить внимание на последовательность ввода условий, поскольку Excel проверяет их и применяет выбранное форматирование именно в том порядке, в котором они внесены.

Автоматически форматируемые таблицы и отчеты

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

Предположим, что есть план продаж – таблица в Excel. Он используется как источник данных для других бюджетов (см. рис. 5). Нужна динамическая ссылка на его содержимое, не зависящая от размера плана продаж. Действуем так.

Выделяем то количество строк и столбцов, которое есть в настоящий момент, затем на вкладке «Главная» нажимаем кнопку «Форматировать как таблицу» и выбираем любой подходящий вариант форматирования (см. рис. 5). Далее на вкладке «Конструктор» задаем ей имя (без пробелов), например, «План продаж». Оно и станет ссылкой на план продаж, а также может использоваться как аргумент для различных функций (например, ВПР), как указание источника данных для сводных таблиц. Сколько бы вы ни добавляли в таблицу строк или столбцов в дальнейшем, формулы исправлять не придется – все будет работать корректно.

Рисунок 5. Динамический диапазон

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

• красивые стили дизайна;
• автоматическое включение для них автофильтра и строки итогов;
• автоматическое копирование формул на весь столбец;
• закрепленная шапка таблицы (остается на месте при прокрутке).

Способы навести порядок в Excel-отчетах

Скачайте инструкции, которые помогут объединить несколько отчетов на одном листе, свести в короткий отчет информацию из таблицы в тысячу строк, сцепить данные и разместить в одной ячейке отчетной таблицы. Здесь же – рекомендации о том, как добавить данные в выпадающий список Excel, найти повторы в списке контрагентов, уменьшить размер отчетов и ускорить их работу. Активируйте доступ к Системе Финансовый директор-у вас будет 3 дня на ознакомление с материалом.

Скачать инструкции и примеры

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

Выгрузка данных из учетной системы

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

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

• выделяем столбец с незаполненными ячейками (диапазон A1:A12);
• оставляем в выделении только ячейки без значений. Для этого нужно нажать клавишу F5, в открывшемся диалоговом окне нажать кнопку «Выделить», далее выбрать условие «Пустые ячейки» и нажать «ОК»;
• в первую ячейку из этого диапазона (А2) вводим знак «равно» и нажимаем на клавиатуре кнопку «Вверх». В итоге получится формула, ссылающаяся на предыдущую ячейку. Но после ее появления надо нажать не Enter, а сочетание клавиш CTRL + Enter. Так формула скопируется во все выделенные ячейки;
• избавляемся от формул и оставляем только значения аналитики в столбце. Снова выделяем диапазон (A1:A12), копируем его и вставляем на то же самое место, но уже одни значения (щелкнуть правой кнопкой мыши по выделению – «Специальная вставка» – выбрать опцию «Вставить значения»).

Управленческая отчетность 2.0: шаблоны в Excel и инструкции по применению

Чтобы вы сэкономили время, редакция эксперты Актион Финансы сделали подборку готовых шаблонов отчетов и финансовых моделей в Excel, которыми поделились ваши коллеги. Активируйте доступ к журналу "Финансовый директор" и скачайте готовые формы.

Управленческая отчетность 2.0

Защита от ошибок ручного ввода

Те финансовые директора, кому постоянно приходится готовить планы или отчеты в разрезе контрагентов, не понаслышке знают, сколько хлопот доставляет отсутствие единообразия данных, введенных в разных таблицах. Например, бывает так. В ре­естре покупателей компания отражена как ООО «Ромашка», в отчете по дебиторам – «Ромашка», в платежном календаре – Romashka Ltd. Казалось бы, мелочь, и так понятно, о какой организации идет речь, но при попытке сделать сортировку по компаниям, построить сводную таблицу ничего хорошего не получится. У Excel на этот случай есть простой и удобный инструмент – проверка вводимых данных с раскрывающимся списком.

Итак, предположим, что нам крайне важно проверять правильность ввода на­именований товаров в отчете о продажах.

Начинаем с создания на любом листе списка правильных наименований товаров – образца, который Excel будет использовать для проверки вводимых данных. Дальше созданному диапазону надо дать имя для использования в дальнейшем в качестве источника данных для выпадающего списка. Для этого выделяем наш список (все ячейки, содержащие корректные наименования товаров), нажимаем сочетание клавиш CTRL + F3 и кнопку «Создать» и вводим имя диапазона без пробелов, к примеру, «Товары».

Теперь возвращаемся в отчет о продажах и выделяем ту область ячеек, куда будут вноситься названия товаров. Открываем вкладку «Данные» – «Проверка данных». В появившемся окне «Проверка вводимых значений» на вкладке «Параметры» задаем тип данных. В нашем случае – «Список» .

Пос­ле этого в появившейся строке «Источник» достаточно ввести ссылку на образцовый список наименований – «=Товары». После этого останется нажать кнопку «ОК».

Перейдя на одну из ячеек в отчете, куда будут заноситься наименования товаров, вы увидите, что рядом с ней появится стрелка. Теперь можно вводить номенклатуру вручную (Excel выдаст предупреждение, если была допущена ошибка) или выбирать из раскрывающегося списка.

Кстати, при помощи «Проверки» можно ограничивать размеры вводимых чисел («Целое» и «Действительное») и длину строки («Длина текста»), устанавливать диапазоны дат («Дата») или создать свое собственное ограничение, введя необходимую формулу («Другой»).

Группировка данных в Excel

Предположим, у вас на руках сводная ведомость заказов, полученных от покупателей за предыдущий год. В таблице – более 5,5 тысячи записей (строк), основные колонки: «Номер заказа», «Дата», «Наименование товара», «Артикул», «Количество», «Сумма заказа в рублях без учета НДС». Задача – срочно сгруппировать все заказы по месяцам и кварталам, подсчитать по ним промежуточные итоги.

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

На выполнение всех этих действий уйдет уйма времени. А можно справиться за две минуты, если воспользоваться «Сводными таблицами».

Рисунок 6. Группировка данных по месяцам и кварталам

Порядок действий будет таким:

• выделяем все ячейки исходной таблицы. После этого на вкладке «Вставка» нажимаем кнопку «Сводная таблица». В появившемся диалоговом окне нажимаем кнопку «ОК». Сразу же на новом листе появится макет сводной таблицы, а также список ее полей (колонок ведомости заказов в нашем случае);
• мышкой перетаскиваем поле «Дата» из списка полей в область «Названия строк», поле «Сумма заказа в рублях без учета НДС» – в область «Значения»;
• задаем «правильные» имена двум колонкам сводной таблицы – «Дата» и «Сумма заказа в рублях без учета НДС» соответственно. Макет уже приобрел вид нормальной, привычной таблицы, осталось немного;
• ставим курсор на первое значение колонки «Дата», переходим на вкладку меню «Данные» и кликаем кнопку «Группировать». В появившемся диалоговом окне выбираем условие группировки «Месяцы» и «Кварталы», нажимаем кнопку «ОК». Программа автоматически создаст структуру данных в разрезе кварталов и месяцев, а также посчитает итоги (см. рис. 6).

Экспорт отчетов из «1С» в Excel

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

Подборка отчетов из «1С»

Актуальный курс валют в Excel

Очень часто в расчетах, выполненных с помощью Excel, нужно использовать актуальные курсы различных валют.

Приведем пример из практики. Компания специализируется на торговле импортными товарами. Прайс-лист включает цену в долларах и рублях. Отпускная цена в рублях рассчитывается в Excel ежедневно по курсу Центробанка, увеличенному на 2 процента.

Расчет не представляет труда при условии, что специалист ежедневно вносит в таблицу актуальный курс доллара. Увы, но в компании, о которой идет речь, однажды курс не был изменен на актуальный вовремя. В результате клиенту выставили счет с неправильной ценой и этот счет был оплачен. Компания потеряла деньги. Этого не произошло бы, если бы обновление курса доверили Excel. Сделать это просто:

• нажимаем на вкладке «Данные» в группе «Получить внешние данные» кнопку «Из Веба»;
• в появившемся окне запроса (оно напоминает обычный интернет-браузер) вводим адрес сайта, с которого планируем брать информацию (например, yandex.ru или любой другой портал, публикующий актуальную информацию о курсе валют) и нажимаем «Пуск». Обратите внимание, что в Excel и на сайте должны быть одинаковыми знаки-разделители целой и дробной части числа (точка или запятая);
• как только страница загрузится, на ней появятся черно-желтые стрелки. Ими помечены области данных, пригодные для импорта в Excel (см. рис. 7). Остается кликнуть по стрелке рядом с интересующими данными и нажать кнопку «Импорт» в правом нижнем углу окна. Программа запросит, куда поместить новые данные, и загрузит их в нужные ячейки через несколько секунд;
• для настройки частоты обновления валютного курса нужно кликнуть правой кнопкой мыши по любой ячейке из вставленного диапазона. Далее в контекстном меню выбрать команду «Свойства диапазона данных» и указать наиболее приемлемый вариант – обновлять каждый раз при открытии файла или с периодичностью в несколько минут (количество минут указывается).

Рисунок 7. Настройка автоматического обновления курса валют

Проверка заявки на платеж на соответствие бюджету

Стандартная функция «СУММЕСЛИ» может творить чудеса. К примеру, с ее помощью, прежде чем согласовать очередную заявку на платеж, можно легко и быстро проверить, приведет ли это к перерасходу по той или иной статье бюджета движения денежных средств. 

Предположим, что есть бюджет движения денежных средств, составленный на месяц. В нем отдельным блоком выделены статьи, платежи по которым осуществляются на основе поступающих заявок от подразделений (оплата товаров поставщикам, аренда, транспортные расходы). А также предусмотрены графы, названия которых говорят сами за себя: «План на месяц», «Факт на текущую дату», «Сумма заявленных, но не исполненных на текущую дату платежей», «Ожидаемые отклонения от бюджета при выполнении всех заявок».

Кроме того, есть реестр платежей, формируемый из заявок на оплату, поступающих от руководителей функциональных подразделений. У ре­естра аналитика такая: дата подачи заявки, реквизиты счета, подразделение, инициировавшее платеж, а также сумма. Самое главное – для каждой заявки на платеж указывается бюджетная статья, в рамках которой будет осуществлена выплата и статус заявки – «Оплачено» или нет (в последнем случае графа не заполняется).

А теперь о том, как организовать автоматическую проверку платежных заявок на базе этих таблиц:

• добавляем в реестр платежей еще одну колонку. Она предназначена исключительно для служебных целей – для решения технических задач. В ее ячейки вводим следующую формулу:
  
  =F14&" "&H14,
  
  где F14 – ссылка на ячейку, в которой указывается, по какой статье бюджета запланирована выплата, Н14 – ссылка на ячейку со статусом заявки (оплачено или нет), а знак & – функция, объединяющая текстовые значения. Пробел в кавычках (" ") введен в формулу для того, чтобы текст не слипался. Если в ячейке F14 указано «Оплата товаров», Н14 – «Оплачено», то выше описанная формула выдаст вот такой результат: «Оплата товаров оплачено»;

• проверяем, совпадают ли наименования бюджетных статей в реестре заявок и в бюджете движения денежных средств. Они должны быть полностью идентичны;
• в бюджете денежных средств по каждой статье расходов рассчитываем фактический расход денежных средств на текущую дату. Например, чтобы определить, сколько средств потратила компания на оплату товаров, в ячейку на пересечении строки «Оплата товаров» и графы «Факт на текущую дату» вводится формула:
  
  =СУММЕСЛИ('Реестр заявок на платеж'!I14:I57;"Оплата товаров оплачено";'Реестр заявок на платеж'!G14:G39),
  
  где 'Реестр заявок на платеж'!I14:I57 – ссылка на все ячейки технического столбца в реестре заявок. Перебирая данные из этого столбца, функция будет либо складывать суммы заявок, либо игнорировать их. «Оплата товаров оплачено» – это и есть критерий, которым будет руководствоваться программа, решая, учитывать тот или иной платеж или нет. Совпадает значение в ячейках технического столбца с этой фразой – складывать, нет – пропускать. 'Реестр заявок на платеж'!G14:G39 – ссылка на все ячейки, содержащие суммы по заявкам на платеж. Аналогично вводятся формулы для всех статей, оплата по которым осуществляется на основании заявок от руководителей подразделений (см. рис. 7);
• добавляем в бюджет движения денежных средств данные о тех заявках, которые еще не были оплачены. Иначе говоря, нужно заполнить графу «Сумма заявленных, но не исполненных на текущую дату платежей». Логика будет той же, что и в предыдущем случае. Разница в том, что вместо оплаченных заявок надо будет заставить Excel отобрать те, по которым деньги еще не ушли. Формула выглядит так:
  
  =СУММЕСЛИ('Реестр заявок на платеж'!I14:I57;"Оплата товаров";'Реестр заявок на платеж'!G14:G39).

Обратите внимание, что в критерии отбора заявок («Оплата товаров») после названия статьи бюджета должен стоять пробел. Помните, создавая технический столбец в реестре заявок, мы добавили знак пробела? Его и надо учесть. Иначе ничего работать не будет.

Теперь все готово. В бюджете есть данные о запланированных выплатах на месяц, фактически осуществленных (оплаченные заявки) и предстоящих (заявленные, но не исполненные платежи). Этих сведений более чем достаточно, чтобы заранее предвидеть перерасход бюджета. Кстати, по мере добавления заявок в реестр (или изменения их статуса), благодаря функции «СУММЕСЛИ» данные будут мгновенно меняться.

Умный платежный календарь

Активируйте гостевой доступ к журналу "Финансовый директор" и скачайте готовый «Умный платежный календарь», в который уже встроены инструменты управления деньгами: автоматизированная заявка на оплату, автопроверка заявки на соответствие бюджету, автозаполнение повторяющихся платежей и другие.

Скачать платежный календарь

Таблица. Горячие клавиши, незаменимые при работе в Excel