Excel постійно підвищує свою функціональність. За 35 років у ньому назбиралося близько 500 різних формул. Не дивно, що досі Excel — один із улюблених інструментів фінансистів. А з макросами VBA, налаштуваннями Power Query, Power Pivot і Power BI — ще й «нескінченно просунутий».
Розглянемо 10 нескладних прийомів Excel, які прискорять і спростять роботу фінансистам та іншим фахівцям. Ці функції допоможуть:
- виявити та перехопити потенційні помилки розрахунку
- надати величезній таблиці читабельний вигляд та вектор аналізу
- миттєво виділяти потрібну інформацію
- не дивитися на календар і не працювати у вихідні
- захистити дані від непотрібних запитань та неетичних дій
#1. Взятися за #DIV/0! або Функція перехоплення помилок
Зі школи всі пам'ятають правило: «на нуль ділити не можна». Excel теж так вважає, але на практиці не рідкість, коли це доводиться робити. Наприклад, надходження не заплановане, а фактично воно є і потрібно визначити відсоток виконання плану. Тоді у всіх таких рядках при розподілі з'являється #DIV/0!.
Через це може не рахуватися підсумковий рядок. Буває, що #DIV/0! йде далі і «ламає» по ланцюжку всі розрахунки. І завжди доводиться очищати звіт вручну. Для таких випадків у Excel є функція IFERROR.
Синтаксис формули:
=IFERROR (значення; значення_якщо_помилка), де
значення — вираз, в якому треба перевірити помилки
значення якщо помилка — це значення, формула або текст, який поверне функція, якщо знайде помилку
На відміну від своїх попередників (ISERR, ISNA), ця функція коротка, перехоплює та замінює всі некрасиві коди: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, або #NULL!. У нашому прикладі — заміна на «0».
Формули розрахунку, які можуть згенерувати код-помилку, треба ретельно перевіряти, перш ніж використовувати IFERROR. Особливо, коли формула складна. Якщо таблиця розрахункова, коди-помилки краще не замінювати текстовим значенням. Виглядає красиво, але може ускладнити подальший аналіз.
Важливо. Не використовуйте функцію IFERROR скрізь «про всяк випадок». Наприклад, при розрахунку прибутку: ми нічого не ділимо та ймовірності виникнення такої помилки немає. Безпечніше застосовувати її після появи кодів, аналізу причин і лише точково. Недарма її називають функцією приховування помилок. Так з IFERROR ви можете не помітити, що випадково видалили рядок, який був прописаний у складній формулі (раніше висвітилося б #REF!).
#2. Почати все з нуля, або Як видалити нуль з поля зору
Іноді велика кількість нульових значень у таблиці (а особливо ще й з нулями після коми) візуально заважає. Буває, що видалити такі рядки не можна. Наприклад, це фіксований список контрагентів чи бюджет. Але можна за кілька секунд зробити нулі невидимими і більше на них не відволікатися.
Алгоритм дій:
- виділити область (використовуйте прийом із Shift)
- пройти маршрутом: формат клітинок / число / всі формати
- ввести маску 0;-0;; @ у полі Тип
Важливо. Будьте обережні з іншим прийомом — «Знайти та замінити». Він може здаватися швидшим і простішим (кілька кліків мишки), але з цим завданням коректно не впорається. Він прибере всі нулі, в тому числі і всередині чисел, і дані будуть серйозно викривлені.
#3. Придумати свої правила та поставити умови
У величезній таблиці часто погляду нема за що зачепитися. Умовне форматування в Excel може швидко візуалізувати дані. Варіантів багато: інтуїтивна заливка кольором, гістограми, значки.
Алгоритм дій простий:
- виділити область форматування (без шапки таблиці та зазвичай без підсумкового рядка)
- далі переходите на Умовне форматування (вкладка «Головна») та вибираєте відповідний варіант
Можна скористатися готовим варіантом, а можна створити своє правило. Важливо дотримуватися почуття міри і не перетворювати таблицю на розмальовку. Ефект буде зворотним. Одного-двох запитів достатньо. Не забувайте у формулі правил коректно зафіксувати знаком $ рядок або стовпець (частіше стовпець).
Важливо. Дотримуйтесь послідовності за кількох умов (правил) форматування: відразу перевіряйте, щоб верхнє (перше) правило не перекривало інші.
#4. Увімкнути динамічну підсвітку рядка на аркуші
Майже всі таблиці фінансистів не поміщаються на екрані. Тримати потрібний рядок постійно в полі зору — складно. Вибирати його фільтрами чи виділяти кожен кольором — незручно.
Є один просунутий прийом — підсвітка рядка. Вона переміщується разом із курсором, й інформацію зручно аналізувати (на скріні — зеленого кольору). Створити її можна за допомогою однієї формули в Умовному форматуванні та дуже простого макросу VBA за зразком.
Алгоритм дій:
- виділити всю область таблиці, крім шапки
- через Умовне форматування створити правило із формулою за зразком:
=ROW(A6)=CELL("рядок")
де A6 — це початок першого рядка. - вибрати колір заливки
Далі треба відкрити редактор макросів (лист / переглянути код) і вставити макрос за зразком:
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) / кнопка Виділити)
- виділити знайдені значення кольором
Основна перевага — дуже швидко і наочно. Далі треба провести аналіз клітинок із невідповідністю (або розібратися з логікою нового звіту).
Важливо. Цей прийом не дозволяє окремо виділити формули з ручним виправленням для коригування значення. Наприклад, до фінальної формули додали число або склали в клітинці два числа, набрані вручну. Excel також вважатиме це формулою і не виділить окремо. А для фінансиста таке буває важливим. Рішенням може бути захист клітинок від змін паролем (п. 10 цієї статті).
#6. Написати формулу корисного тексту
Excel, звісно, в першу чергу створено для розрахунків, а не для написання текстів. Але все ж таки кілька корисних пропозицій він може створити. Для цього є функція CONCATENATE.
Синтаксис формули:
=CONCATENATE(текст1;текст2;... текст n), де
текст 1, текст 2 ... текст n — це слова та фрази, числа, посилання на клітинки з необхідною інформацією.
Ви самі задаєте послідовність тексту. Об’єднати можна понад 200 аргументів. Але важливо знати міру і не прагнути однією формулою прописати текст фінансового аналізу чи експертного висновку. Зручно так формувати в окремій клітинці умови для формул, щоб не обтяжувати їх написанням тексту всередині.
Фінансиста функція рятує, коли потрібно набирати текст призначення платежу, а їх багато. Зробити це можна автоматично за затвердженим реєстром — і потім просто скопіювати до платіжного доручення.
Тут є особливість. Коли дата документа в окремій клітинці, то функцію CONCATENATE треба зробити подвійною, вбудувавши у ній функцію TEXT. Інакше дата перенесеться лише у числовому форматі.
Формула для призначення платежу компанії Альфа може мати такий вигляд:
=CONCATENATE(D6;" за договором ";E6;" від ";TEXT(F6;"рік;місяць;день");" (";G6; ")").
Важливо. Текстові та символьні аргументи потрібно брати у лапки, числа та посилання на клітинку — ні. Коли ви прописуєте функцію не через Майстер формул, не забувайте розділяти аргументи крапкою з комою і стежити за пробілами.
#7. Вилучити з тексту головне
Буває зворотна ситуація. З тексту треба вилучити (витягти) інформацію за правилом. Робити це вручну незручно. Для цього є функція MID.
Синтаксис функції:
=MID(текст; початкова позиція; кількість знаків), де
текст — посилання на клітинку з потрібною інформацією
початкова позиція — порядковий номер символу, з якого розпочнеться вилучення
кількість знаків — довжина (число символів)
Фінансисту зручно використовувати цю функцію виділення дати документа. Наприклад, договори, рахунки тощо. А далі від дати будувати графік оплати або виявляти прострочену заборгованість.
Функцію MID рекомендують одночасно поєднувати з функцією SEARCH. Так ефективніше. Для визначення дати оплати щодо компанії Дрім формула матиме вигляд:
=MID(G6;SEARCH("??.??.????";G6);10)+10 — оплата рахунку протягом 10 днів.
Важливо. Якщо під час введення вихідної інформації у дат буде різний формат, то проста формула не спрацює. Визначте правила спочатку та прийдіть до однаковості. Особливо, коли дані вносять декілька співробітників.
#8. Заповнити все миттєво за зразком
Excel (починаючи з версії 2013) здатний зчитувати логіку ваших дій (записів за зразком), а потім легко і швидко все повторити. З функцією «Миттєве заповнення» можна без написання складних формул витягти з тексту символи, дати, числа і слова (можливо — з перестановкою), склеїти текст з різних клітинок, розділити його за регістрами. Наприклад, привести до одного вигляду номери мобільних телефонів, ПІБ тощо.
Алгоритм дій:
- додати ще один стовпець поряд з вихідною інформацією (вводити приклад важливо обов’язково поряд — у наступному стовпці праворуч від даних)
- вручну ввести 1–3 різних приклади (важливо саме різних)
- виділити область заповнення інформацією
- пройти маршрутом: Дані / Миттєве заповнення — або скористатися поєднанням клавіш Ctrl+E.
Як і функції MID, SEARCH тощо, «Миттєве заповнення» не працює при одруківках та помилках. Потрібна певна однаковість у заповненні даних. Тому безпечніше застосовувати функцію у своїх таблицях, а не в чужих.
Фінансист може скористатися цим інструментом для швидкого отримання дати, формування призначення платежу. Подивимося на прикладах.
Якщо під час розрахунків формулами можна всі дії прописати відразу, то з функцією «Миттєве заповнення» важлива етапність. Спочатку виділимо дату в стовпці H, потім порахуємо за формулою стовпець L.
Коли не впевнені, що заповнення ведеться за єдиними правилами та форматами, можна перевірити результат, отриманий миттєвим заповненням і формулою. Далі треба скоординувати роботу працівників.
Поєднання Ctrl+E добре впоралося з призначенням платежу. Однозначно швидше, ніж через функцію CONCATENATE (з усіма пробілами). Важливо також вибирати 2–3 різні варіанти формулювання. Послідовність їх не має значення. У прикладі є оплата з рахунком та без рахунку.
Якщо у вас є різні варіанти складу формулювань, можна створити шаблони для навчання Excel. Вставляти їх у кожен новий реєстр, запускати Ctrl+E і видаляти відразу після заповнення всього документа (шаблон — це цілий рядок реєстру, а не окремо призначення платежу).
Важливо. Формула працює завжди та реагує на зміну, а «Миттєве заповнення» — ні. Потрібно повторити алгоритм дій під час введення нової інформації.
#9. Не звіряти дати з календарем
Коли багато дат вводиться вручну, і якщо планова дата оплати визначається за формулою, то дуже легко вказати вихідний день. А потім загубити платіж. При розрахунку простроченої заборгованості також дуже важливо розрізняти календарні та робочі дні. Аналогічна ситуація з платіжним календарем на кілька тижнів. Щоразу звірятися з календарем — незручно та довго. 20 платежів вручну можливо рознести нескладно, а 200 — вже не дуже.
В Excel є функція WEEKNUM.ISO(Дата). Вона надає кожному дню свій номер тижня з початку року (від 1 до 52). Також можна у формулярі додатково перевіряти, що для дати оплати вибрано лише робочі дні. Тут допоможе функція WEEKDAY(дата; 2). Тобто все це можна налаштувати автоматично.
Можна скористатися умовним форматуванням: створити правило через формулу WEEKDAY(K6; 2)>5 і вибрати яскраву заливку клітинки. Тепер, коли розрахункова дата оплати потрапляє на вихідний день, клітинка змінюватиме колір та привертатиме увагу. Це дуже зручно.
Важливо. На початку року слід перевірити коректність визначення першого тижня. Платіж без запланованої дати оплати буде автоматично віднесений до тижня №52 (може не потрапити до платіжного календаря).
#10. Заборонити, але захистити
Файли, які містять конфіденційну інформацію, фінансисти, як і раніше, захищають паролем. Наприклад, з розрахунком премії, кадровими перестановками чи результатами внутрішнього асесменту. Крім того, в метушні можна випадково надіслати інформацію не тому адресату. Пароль — це впевненість у тому, що відомості не поширяться далі за файл.
Документ можна зашифрувати, пройшовши маршрутом: файл / відомості / зашифрувати з допомогою пароля.
У робочих файлах також часто є необхідність захистити аркуші від змін. Наприклад, коли консолідується бюджет, важливо, щоб у типовий формуляр ніхто випадково не додав рядка і не змінив формулу.
Перш ніж захистити файл від небажаних змін, його потрібно підготувати.
1. Виділити області, де можна вносити дані, далі:
формат клітинки / захист / зняти галочку із «захист клітинок»
2. Виділити області, у яких треба приховати формули, далі:
формат клітинки / захист / поставити галочку в «приховати формули»
Після цього: рецензування / захистити аркуш (або захистити книгу) / пароль. Якщо у вас є заборона зміни даних після конкретної дати, то безпечніше скористатися цим варіантом.
Важливо. Можливості відновити пароль, якщо забули, — немає. Краще вигадати свій алгоритм його створення.
Бажаєте отримувати дайджест статей?
