Топ-10 приемов в Excel от финансистов | Бизнес-школа Laba (Лаба)
Журнал

Поиск

Топ-10 приемов в Excel от финансистов

Больше скорости, меньше работы руками.

cover-finexcel-61c1f55eed310098923179.jpg

Excel постоянно повышает свою функциональность. За 35 лет в нем набралось около 500 различных формул. Неудивительно, что до сих пор Excel — один из любимых инструментов финансистов. А с макросами VBA, настройками Power Query, Power Pivot и Power BI — еще и «бесконечно продвинутый».

Рассмотрим 10 несложных приемов Excel, которые ускорят и упростят работу финансистам и другим специалистам. Эти функции помогут: 

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

#1. Взяться за #ДЕЛ/0!, или Функция перехвата ошибок 

Со школы все помнят правило: «на ноль делить нельзя». Excel тоже так считает, но на практике не редкость, когда приходится это делать. Например, поступление не запланировано, а по факту оно есть — и нужно определить процент выполнения плана. Тогда во всех таких строках при делении появляется #ДЕЛ/0! (см. рис. 1). 

Из-за этого может не считаться итоговая строка. Бывает, что #ДЕЛ/0! идет дальше и «ломает» по цепочке все расчеты. И постоянно приходится очищать отчет вручную. Для таких случаев в Excel есть функция ЕСЛИОШИБКА

Синтаксис формулы:

=ЕСЛИОШИБКА(проверяемое_значение ; значение_если_ошибка), где

проверяемое значение — выражение, в котором надо перехватить ошибки

значение если ошибка — значение, формула или текст, которую вернет функция, если найдет ошибку

В отличие от своих предшественников (ЕОШ, ЕНД), эта функция короткая, перехватывает и заменяет все некрасивые коды: #Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ?, #ПУСТО!. В нашем примере — замена на «0».

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

Важно. Не используйте функцию ЕСЛИОШИБКА везде «на всякий случай». Например, при расчете прибыли: мы ничего не делим и вероятности возникновения такой ошибки нет. Безопаснее применять ее после появления кодов, анализа причин и только точечно. Недаром ее называют функцией сокрытия ошибок. Так с ЕСЛИОШИБКА вы можете не заметить, что случайно удалили строку, которая была прописана в сложной формуле (раньше бы высветилось #ССЫЛКА!).  

#2. Начать все с нуля, или Как удалить ноль из поля зрения 

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

Алгоритм действий:

  • выделить область (используйте прием с Shift)
  • пройти по маршруту: формат ячеек / число / все форматы
  • ввести маску 0;-0;;@ в поле Тип

Важно. Будьте аккуратны с другим приемом — «Найти и заменить». Он может казаться быстрее и проще (пара кликов мышки), но с этой задачей корректно не справится. Он уберет все нули, в том числе и внутри чисел, — и данные серьезно исказятся. 

Рекомендуем почитать:

img-excel8-609be79973497647992745.jpg

8 приемов для ускоренной работы в Excel

Читать

#3. Придумать свои правила и поставить условия

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

Алгоритм действий простой:

  • выделить область форматирования (без шапки таблицы и обычно без итоговой строки)
  • далее переходите на Условное форматирование (вкладка «Главная») и выбираете подходящий вариант

Можно воспользоваться готовым вариантом, а можно создать свое правило. Важно соблюдать чувство меры и не превращать таблицу в раскраску. Эффект будет обратным. Одного-двух запросов достаточно. Не забывайте в формуле правил корректно зафиксировать знаком $ строку или столбец (чаще столбец).

На рис. 3 показаны три варианта визуального выделения отклонений от плана. 

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

#4. Включить динамическую подсветку строки на листе

Почти все таблицы финансистов не помещаются на экране. Держать нужную строку постоянно в поле зрения — сложно. Выбирать ее фильтрами или выделять каждую цветом — неудобно.

Есть один продвинутый прием — подсветка строки. Она перемещается вместе с курсором, и информацию удобно анализировать (на рис. 4 — зеленого цвета). Создать ее можно с помощью одной формулы в Условном форматировании и очень простого макроса VBA по образцу.

Алгоритм действий:

  • выделить всю область таблицы, кроме шапки
  • через Условное форматирование создать правило с формулой по образцу:

    =СТРОКА(A6)=ЯЧЕЙКА("строка")

    где A6 — это начало первой строки (см. рис. 4).
  • выбрать цвет заливки

Рекомендуем почитать:

img-605c536954022948839417.pl

Как строить отчет о прибылях и убытках в Excel

Читать

Далее надо открыть редактор макросов (лист / просмотреть код) и вставить макрос по образцу:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

ActiveCell.Calculate

End Sub

Теперь цветная подсветка будет перемещаться по строкам.

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

#5. Найти случайные формулы и константы в таблице или формуляре

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

Excel это тоже может быстро проверить. Рассмотрим две ситуации:

Ситуация 1. Вместо формулы стоит значение

  • выделить область проверки (используйте прием с Shift)
  • пройти по маршруту: найти и выделить / выделить группу ячеек / константы

    (альтернативный вариант: F5 (или Ctrl+G) / кнопка Выделить)
  • выделить найденные значения цветом

Ситуация 2. Вместо значения числа стоит формула

  • выделить область проверки (используйте прием с Shift)
  • пройти по маршруту: найти и выделить / выделить группу ячеек / формулы

    (альтернативный вариант: F5 (или Ctrl+G) / кнопка Выделить)
  • выделить найденные значения цветом

Основное преимущество — очень быстро и наглядно (см. рис. 5). Дальше надо провести анализ ячеек с несоответствием (или разобраться с логикой нового отчета).

Важно. Этот прием не позволяет выделить отдельно формулы с ручным исправлением для корректировки значения. Например, к финальной формуле добавили число или сложили в ячейке два числа, набранные вручную. Excel также будет считать это формулой и не выделит отдельно. А для финансиста такое бывает важно. Решением может быть защита ячеек от изменений с помощью пароля (п. 10 этой статьи).

#6. Написать формулу полезного текста

Excel, конечно, изначально создан для расчетов, а не для написания текстов. Но все же несколько полезных предложений он может создать. Для этого есть функция СЦЕП.

Синтаксис формулы:

=СЦЕП(текст1;текст2;... текст n),

где текст 1, текст 2… текст n — это слова и фразы, числа, ссылки на ячейки с необходимой информацией.

Рекомендуем почитать:

img.excellist-5f884d801f05f611204898.jpg

Как строить выпадающие списки в Excel

Читать

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

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

Здесь есть особенность. Когда дата документа в отдельной ячейке, то функцию СЦЕП надо сделать двойной, встроив в нее функцию ТЕКСТ. Иначе дата перенесется только в числовом формате.

Формула для назначения платежа по компании Альфа (см. рис. 6) может иметь такой вид:

=СЦЕП(D6;" по договору ";E6;" от ";ТЕКСТ(F6;"ДД.ММ.ГГ");" (";G6; ")").

 

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

#7.  Выделить из текста главное

Бывает обратная ситуация. Из текста надо выделить (извлечь) информацию по правилу. Делать это вручную неудобно. Для таких целей есть функция ПСТР.

Синтаксис функции:

=ПСТР(текст; начальная позиция; количество знаков), где

текст — ссылка на ячейку с нужной информацией

начальная позиция — порядковый номер символа, с которого начнется извлечение

количество знаков — длина (число символов)

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

Функцию ПСТР рекомендуют сразу объединять с функцией ПОИСК. Так эффективнее. Для определения даты оплаты по компании Дрим (см. рис. 7) формула будет иметь вид:

=ПСТР(G6;ПОИСК("??.??.????";G6);10)+10 — оплата счета в течение 10 дней.

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

#8.  Заполнить все мгновенно по образцу

Excel (начиная с версии 2013) способен считывать логику ваших действий (записей по образцу), а затем легко и быстро все повторить. С функцией «Мгновенное заполнение» можно без написания сложных формул извлечь из текста символы, даты, числа и слова (возможно — с перестановкой), склеить текст из разных ячеек, разделить его по регистрам. Например, привести к одному виду номера мобильных телефонов, отображение ФИО и т. д.

Алгоритм действий:

  • добавить еще один столбец рядом с исходной информацией (вводить пример важно строго рядом — в следующем столбце справа от данных)
  • вручную ввести 1–3 разных примера (важно — именно разных)
  • выделить область заполнения информацией
  • пройти по маршруту: Данные / Мгновенное заполнение — или воспользоваться сочетанием клавиш Ctrl+E

Как и функции ПСТР, ПОИСК и т. п., «Мгновенное заполнение» не работает при опечатках и ошибках. Нужно определенное единообразие в заполнении данных. Поэтому безопаснее применять функцию в своих таблицах, а не в чужих.

Финансист может воспользоваться этим инструментом для быстрого извлечения даты, формирования назначения платежа. Посмотрим на примерах (рис. 8 и 9).

Если при расчетах формулами можно все действия прописать сразу, то с функцией «Мгновенное заполнение» важна этапность. Сначала выделим дату в столбце H, затем посчитаем по формуле в столбец L (см. рис. 8).

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

Сочетание Ctrl+E хорошо справилось и с назначением платежа. Однозначно быстрее, чем через функцию СЦЕП (со всеми пробелами). Важно также выбирать 2–3 разных варианта формулировки. Последовательность их неважна. В примере есть оплата со счетом и без счета (см. рис. 9). 

Если у вас есть разные варианты состава формулировок, то можно создать шаблоны для обучения Excel. Вставлять их в каждый новый реестр, запускать Ctrl+E и удалять сразу после заполнения всего документа (шаблон — это целая строка реестра, а не только отдельно назначение платежа).

Важно. Формула работает всегда и реагирует на изменение, а «Мгновенное заполнение» — нет. Потребуется повторить алгоритм действий при вводе новой информации.  

Хотите получать дайджест статей?

Одно письмо с лучшими материалами за неделю. Подписывайтесь, чтобы ничего не упустить.
 
Спасибо за вашу подписку!

#9. Не сверять даты с календарем

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

В Excel есть функция НОМНЕДЕЛИ.ISO(Дата). Она присваивает каждому дню свой номер недели с начала года (от 1 до 52). Также можно в формуляре дополнительно проверять, что для даты оплаты выбраны только рабочие дни. Здесь поможет функция ДЕНЬНЕД(дата; 2). То есть все это можно настроить автоматически.

Можно воспользоваться условным форматированием: создать правило через формулу ДЕНЬНЕД(K6; 2)>5 и выбрать яркую заливку ячейки. Теперь, когда расчетная дата оплаты попадает на выходной день, ячейка будет менять цвет и привлекать внимание (см. рис. 10). Это очень удобно.

Важно. В начале года нужно проверить корректность определения первой недели. Платеж без планируемой даты оплаты будет автоматически отнесен к неделе №52 (может не попасть в платежный календарь).

#10. Запретить, но защитить

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

Документ можно зашифровать, пройдя по маршруту: файл / сведения / зашифровать с использованием пароля.

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

Весь бизнес-контент в удобном формате. Интервью, кейсы, лайфхаки корп. мира — в нашем телеграм-канале. Присоединяйтесь!

Прежде чем защитить файл от нежелательных изменений, надо его подготовить.

1. Выделить области, в которых можно вносить данные, далее:

формат ячеек / защита / снять галочку с «защита ячеек»

2. Выделить области, в которых надо скрыть формулы, далее:

формат ячеек / защита / поставить галочку в «скрыть формулы»

После этого: рецензирование / защитить лист (или защитить книгу) / пароль. Если у вас есть запрет на изменение данных после конкретной даты, то безопаснее воспользоваться этим вариантом. 

Важно. Возможности восстановить пароль, если забыли, — нет. Лучше придумать свой алгоритм его создания.

Хотите получать дайджест статей?

Одно письмо с лучшими материалами за неделю. Подписывайтесь, чтобы ничего не упустить.
Спасибо за подписку!
Курс по теме:
"Excel для бизнеса"
Программы
Ведет Алексей Вощак
17 марта 14 апреля
Алексей Вощак