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

64251
Павлов Николай
IT-тренер, консультант по продуктам Microsoft Office
Как построить диаграмму Ганта в Excel, автоматически проверять заявки на платеж, залатать дыры в таблицах, выгруженных из «1С»? Такие вопросы задали финансовые директора в ходе опроса, проведенного редакцией журнала. Некоторые из этих задач решаются с помощью встроенных инструментов программы, другие – за счет несложных комбинаций типовых функций.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Очень часто в расчетах, выполненных с помощью Excel, нужно использовать актуальные курсы различных валют. Приведем пример из практики. Компания специализируется на торговле импортными товарами. Прайс-лист включает цену в долларах и рублях. Отпускная цена в рублях рассчитывается в Excel ежедневно по курсу Центробанка, увеличенному на 2 процента. Сам расчет не представляет труда при условии, что в таблицу ежедневно специалистом «руками» вносится актуальный курс доллара. Увы, но в компании, о которой идет речь, однажды курс не был изменен на актуальный вовремя. В результате клиенту выставили счет с неправильной ценой и этот счет был оплачен. Компания потеряла деньги. Этого не произошло бы, если бы обновление курса доверили Excel.  Сделать это просто:

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

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

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

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

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

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

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

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

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

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

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

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

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

И(G$1>=$B2;G$1

И(G$1>=$B2;G$1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • добавляем в реестр платежей еще одну колонку. Она предназначена исключительно для служебных целей – для решения технических задач. В ее ячейки вводим следующую формулу:

    =F14&" "&H14,

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

    =СУММЕСЛИ('Реестр заявок на платеж'!I14:I57;"Оплата товаров оплачено";'Реестр заявок на платеж'!G14:G39),

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

    =СУММЕСЛИ('Реестр заявок на платеж'!I14:I57;"Оплата товаров";'Реестр заявок на платеж'!G14:G39).

 

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

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

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

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

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

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

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

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

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

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

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

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

Клавиша или комбинация клавиш
Действие
Аналогичная команда меню
Вычисления
SHIFT + F3
Вызвать мастер Функций
Формулы - Вставить функцию
F4
Задать тип сылки на ячейку при вводе формулы – относительная или абсолютная
CTRL + ~
Отображать в ячейках формулы или их результаты
Формулы – Показать формулы
ALT+ «Равно»
Ввести в ячейку функцию СУММ
Формулы – Автосумма
F9
Пересчитать формулы во всей книге
Формулы – Пересчет
SHIFT + F9
Пересчитать формулы на текущем листе
Формулы – Произвести вычисления
Редактирование
F2
Редактировать активную ячейку
SHIFT + F2
Редактировать примечание к ячейке
CTRL + Delete
Удалить текст от курсора до конца строки (при редактировании содержимого ячейки)
CTRL+ALT+V
Специальная вставка (после копирования)
CTRL + «Плюс»
Добавить выделенное количество строк или столбцов
Главная – Вставить – Строки (Столбцы)
CTRL + «Минус»
Удалить выделенные строки или столбцы
Главная – Удалить – Строки (Столбцы)
CTRL + D
Заполнить выделенный диапазон значениями или формулами из первой выделенной ячейки
Главная – Заполнить – Вниз
CTRL + H
Вызвать диалоговое окно «Найти и заменить»
Главная – Найти и выделить – Заменить
SHIFT + F11
Вставить пустой лист
Главная – Вставить – Вставить лист
ALT + SHIFT + «Стрелка вправо»
Сгруппировать выделенные строки (столбцы)
Данные – Группировать
ALT + SHIFT + «Стрелка влево»
Разгруппировать выделенные строки/столбцы
Данные – Разгруппировать
Ввод данных
CTRL + Enter
Ввод сразу во все выделенные ячейки
ALT + Enter
Перенос строки внутри ячейки (при вводе)
CTRL + SHIFT + «4»
Вставить текущую дату
ALT + «Стрелка вниз»
Открыть выпадающий список (в пустой ячейке под столбцом с данными)
Навигация и выделение
CTRL + Back space
Вернуться к активной ячейке (прокрутка)
CTRL + PgUp (PgDown)
Переключение между листами
CTRL+Tab
Переключение между открытыми книгами
Вид – Перейти в другое окно
CTRL + пробел
Выделить весь столбец
SHIFT + пробел
Выделить всю строку
CTRL + A
Выделить текущую область (до ближайших пустых строк и столбцов)
CTRL + End
Перейти к последней ячейке листа
CTRL + SHIFT + End
Выделить все до последней ячейки листа
CTRL + «Стрелка вправо / влево / вверх / вниз»
Перейти до конца или начала строки (столбца)
CTRL + SHIFT + «Стрелка вправо / влево или вверх /вниз»
Выделить до конца или начала строку или /столбец
Работа с файлами
CTRL + N
Создать новую пустую книгу
CTRL + S
Сохранить текущую книгу
CTRL + O
Открыть файл
Прочее
CTRL + L
Создание «умной» таблицы (с фильтрами, автоподстройкой размеров, дизайном, закреплением шапки и т. д.)
Данные – Форматировать как таблицу
CTRL + F2
Предварительный просмотр
Кнопка Офис (Файл) – Печать – Предварительный просмотр
F4
Повторить последнее действие (любое, если вообще возможно)
CTRL + K
Вставить гиперссылку
Вставка – Гиперссылка
CTRL + F3
Открыть диалоговое окно «Диспетчер имен»
Формулы – Диспетчер имен
ALT+F8
Открыть диалоговое окно «Макрос»
Разработчик – Макросы

 

Вложенные файлы

Доступно только подписчикам
  • Файлы Excel, иллюстрирующие описанные в статье приемы.rar


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

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

Школа

Школа

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

Записаться

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

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

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

Живое общение с редакцией

А еще...


Рассылка



© 2007–2016 ООО «Актион управление и финансы»

«Финансовый директор» — практический журнал по управлению финансами компании

Зарегистрировано Федеральной службой по надзору в сфере связи,
информационных технологий и массовых коммуникаций (Роскомнадзор)
Свидетельство о регистрации Эл №ФС77-43625 от 18.01.2011
Все права защищены. email: fd@fd.ru


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

Это бесплатно и займет всего 1 минуту.

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

Это бесплатно и займет всего 1 минут.

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