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;;@ в поле Тип
Важно. Будьте аккуратны с другим приемом — «Найти и заменить». Он может казаться быстрее и проще (пара кликов мышки), но с этой задачей корректно не справится. Он уберет все нули, в том числе и внутри чисел, — и данные серьезно исказятся.
#3. Придумать свои правила и поставить условия
В огромной таблице часто взгляду не за что зацепиться. Условное форматирование в Excel сейчас может быстро визуализировать данные. Вариантов много: интуитивная заливка цветом, гистограммы, значки.
Алгоритм действий простой:
- выделить область форматирования (без шапки таблицы и обычно без итоговой строки)
- далее переходите на Условное форматирование (вкладка «Главная») и выбираете подходящий вариант
Можно воспользоваться готовым вариантом, а можно создать свое правило. Важно соблюдать чувство меры и не превращать таблицу в раскраску. Эффект будет обратным. Одного-двух запросов достаточно. Не забывайте в формуле правил корректно зафиксировать знаком $ строку или столбец (чаще столбец).
На рис. 3 показаны три варианта визуального выделения отклонений от плана.
Важно. Соблюдайте последовательность при нескольких условиях (правилах) форматирования: сразу проверяйте, чтобы верхнее (первое) правило не перекрывало другие.
#4. Включить динамическую подсветку строки на листе
Почти все таблицы финансистов не помещаются на экране. Держать нужную строку постоянно в поле зрения — сложно. Выбирать ее фильтрами или выделять каждую цветом — неудобно.
Есть один продвинутый прием — подсветка строки. Она перемещается вместе с курсором, и информацию удобно анализировать (на рис. 4 — зеленого цвета). Создать ее можно с помощью одной формулы в Условном форматировании и очень простого макроса VBA по образцу.
Алгоритм действий:
- выделить всю область таблицы, кроме шапки
- через Условное форматирование создать правило с формулой по образцу:
=СТРОКА(A6)=ЯЧЕЙКА("строка")
где A6 — это начало первой строки (см. рис. 4). - выбрать цвет заливки
Далее надо открыть редактор макросов (лист / просмотреть код) и вставить макрос по образцу:
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 — это слова и фразы, числа, ссылки на ячейки с необходимой информацией.
Вы сами задаете последовательность текста. Сцепить можно больше 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. Выделить области, в которых надо скрыть формулы, далее:
формат ячеек / защита / поставить галочку в «скрыть формулы»
После этого: рецензирование / защитить лист (или защитить книгу) / пароль. Если у вас есть запрет на изменение данных после конкретной даты, то безопаснее воспользоваться этим вариантом.
Важно. Возможности восстановить пароль, если забыли, — нет. Лучше придумать свой алгоритм его создания.
Хотите получать дайджест статей?
