Excel для бухгалтера: 12 корисних інструментів, прийомів та функцій

Потрібно швидко відсортувати та зіставити дані з різних таблиць? Знайти помилку в заповнених комірках? Сформувати зведену таблицю із підсумковими показниками? Ми зібрали корисні інструменти, прийоми та функції з Excel, з якими можна виконати всі ці дії за пару кліків. Бухгалтерія в Excel стає набагато простішою, якщо використовувати кілька корисних прийомів, які можуть прискорити роботу з даними. Дізнайтесь, чим корисний Excel для бухгалтера із прикладів, які ми підготували.

1. Автозаповнення формул у таблиці Excel 

Окрім безпосередніх обов'язків, бухгалтеру можуть доручити функції з підготовки комерційних пропозицій, розрахунку договірних цін та інше. Для виконання розрахунків необхідно застосовувати різні коефіцієнти та поправки, а також конвертувати ціни. І головне, виконувати всі дії швидко та без шкоди головним обов'язкам. 

Ексель для бухгалтера допоможе у підготовці основного документа, який можна виконувати на робочому аркуші, а додаткові розрахунки на окремих. Так, перераховуючи курс, в одній комірці можна вказати ціну, в другій — курс валюти, а в третій задати формулу перерахунку (= перша комірка * другу комірку), далі натиснути Enter і отримати ціну. У першому аркуші в потрібній комірці можна поставити «=», перейти на другий аркуш і вказати третю комірку з результатом. Знову натиснути Enter та отримати результат. 

Якщо необхідно провести такі розрахунки за великою кількістю пунктів, де змінюється лише ціна, можна скористатись фундаментальним прийомом Excel — автозаповнення формул, або протягування. Можливість протягувати формули — одна з базових функцій програми. Вона автоматизує процес підрахунку даних у таблиці, без багаторазового прописування однієї й тієї ж формули. Виконувати протягування формул можна в такий спосіб. У рядку формул ставимо дорівнює і посилання на комірку з таблиці із вихідними даними (=А3). Після цього отримаємо просто дублювання значення таблиці. При протягуванні цієї комірки вийде копія таблиці з даними, які змінюватимуться відповідно до зміни інформації у вихідній таблиці. Це приклад протягування комірок без фіксування діапазонів.

Можна закріпити посилання, щоб залишити його незмінним при протягуванні повністю, за рядком або стовпцем. Фіксування виконується у рядку формул за допомогою символу $. Цей знак ставлять перед тією частиною координат у посиланні, які необхідно зафіксувати: $ перед літерою — фіксування по стовпцю — $С1; $ перед цифрою — фіксування по рядку — С$1; $ перед літерою та цифрою — повне фіксування комірки - $С$1.

2. Підрахунок календарних днів Excel

Excel може стати незамінним помічником навіть у таких простих діях, як підрахунок календарних днів. Бухгалтеру необхідно точно знати, скільки днів було в тому чи іншому періоді, щоб розрахувати відсотки, розмір пені, штрафу, кредиту тощо. Якщо це невеликий відрізок часу, його просто порахувати, скориставшись календарем, але для виконання постійних розрахунків такий формат досить незручний. У таких ситуаціях на допомогу бухгалтеру приходить Excel.

Щоб виконати розрахунки, необхідно виділити три вільні комірки в таблиці. В одну потрібно записати початкову дату, в другу — кінцеву, а третю залишити порожньою для отримання результатів. Рекомендація: набирайте дату на цифровій частині клавіатури так: 12/10/2016. Програма сама перетворить введені дані у формат дати та вийде 12.10.2016. Далі обираємо третю комірку і тиснемо «Вставити функцію», ви можете знайти її за значком ¶x. Після натискання з'явиться вікно «Майстер функцій». Зі списку «Категорія» обираємо «Дата і час», а зі списку «Функція» — «ДНІВ360» і натискаємо кнопку Ок.

У вікні потрібно вставити значення початкової та кінцевої дати. Для цього потрібно просто натиснути на клітинки таблиці з цими датами, а в рядку «Метод» поставити одиницю і натиснути Ок. Якщо підсумкове значення відображено не в числовому форматі, потрібно перевірити формат комірки: клацнути правою кнопкою миші та вибрати з меню Формат комірки, встановити Числовий формат і натиснути Ок. 

Ще можна виконати підрахунок днів у такий спосіб: у третій комірці набрати = ДНІВ 360 (В1; В2; 1). У дужках необхідно вказати координати двох перших комірок з датами, а для методу поставити значення одиниці. При розрахунку відсотків за тижні треба отриману кількість днів розділити на 7. Також до дати можна додавати чи віднімати будь-яку кількість днів. Щоб це зробити, потрібно в одній комірці написати дату, в другій розмістити знак рівності, потім клацнути по комірці з датою і набрати «+» або «-» та потрібну кількість днів.

3. Сортування даних 

Дуже зручна функція, яка дозволяє розмістити дані за зростанням/зменшенням. А також сортувати дані для впорядкування записів за датою. Для виконання цієї дії необхідно обрати область, яка потребує сортування. Потім можна натиснути кнопку «Сортування за зростанням» у верхньому ряду меню «Дані», її ви знайдете за знаком «АЯ». Ваші дані розмістяться від меншого до більшого за першим виділеним стовпцем.

Таблиці Ексель для бухгалтера дозволяють сортувати дані, починаючи з першого виділеного стовпця. Якщо ви виділили комірки зліва направо, то послідовність буде виконана в крайньому лівому стовпчику. Якщо справа наліво, то в правому. Якщо дані потрібно сортувати за середнім стовпцем, можна використовувати меню «Дані» – пункт «Сортування» – «Сортування діапазону». У розділі «Сортувати за» необхідно обрати стовпчик та тип сортування.

4. Робота з довгими таблицями 

Таблиці Excel для бухгалтера — багатофункціональний робочий інструмент, який містить безліч інформації для ведення звітності та виконання поточних розрахунків. Під час друку таблиці, яка не вміщується на один аркуш, можна розмістити її «шапку» на кожній окремій сторінці, що полегшить пошук необхідних даних. 

Для цього потрібно вибрати в меню «Файл» – «Параметри сторінки» та закладку «Аркуш». Розміщуємо курсор на «Наскрізні рядки» або «Наскрізні стовпці» і в таблиці клацаємо на рядки, які потрібно розмістити на кожному аркуші. Також для роботи з такими документами можна використовувати колонтитули. Вони позначають необхідні дані, такі як дата, номери аркушів, ім'я укладача та інше. Налаштування колонтитулів доступне в «Параметрах сторінки» – «Колонтитули». Там є готові варіанти розмітки або можливість додавання власного. Крім корисних прийомів роботи в Ексель, бухгалтеру необхідно освоїти його гарячі клавіші.

5. Зіставлення показників за різні періоди

Часто дані за різні роки, квартали, місяці доводиться порівнювати. Щоб їх порівняти, потрібно вибрати критерій — унікальний показник для кожної одиниці обліку, наприклад артикул. Для цього використовуємо функцію ВПР (VLOOKUP), яка до значень за заданим критерієм з першої таблиці підбере відповідні з другої таблиці.

Щоб порівняти, наприклад, ціни на товари, дотримуйтесь алгоритму: на панелі інструментів заходимо у функції (fx) і обираємо ВПР. 

У вікні вказуємо для неї аргументи: 

• Шукане значення — критерій; 

• Таблиця — діапазон даних із другої таблиці, серед яких потрібно знайти відповідні;

• Номер стовпця — діапазон даних, до яких підбиратимуться відповідні; 

• Інтервальний перегляд — це ступінь відповідності, нам потрібна точна, тому обираємо 0. 

Копіюємо формулу до кінця стовпця. Повідомлення «#Н/Д» означає, що із зазначеного артикула у другій таблиці значень не виявлено.

6. Пошук показників, що відповідають умовам 

Можливості Excel полегшують пошук потрібних даних. Розберемо на прикладі пошуку заборгованості контрагента. Для цього: 

• Поруч з таблицею, в якій плануємо шукати, додаємо таблицю з умовами пошуку. Для цього копіюємо шапку й у відповідні стовпці вводимо критерії пошуку, використовуючи знаки порівняння: «>», «<», «=». 

• Заходимо на вкладку «Дані» та обираємо пункт «Фільтр — Додатково».

У вікні, що з’явилось, вказуємо: 

• Вихідний діапазон — повністю таблиця, в якій шукатимемо; 

• Діапазон умов — таблиця з критеріями пошуку. 

В результаті отримаємо значення, що відповідають заданим умовам.

7. Пошук помилок у таблицях 

Пошук помилок у таблицях — робота довга та нудна. Щоб прискорити процес, скористайтесь можливостями електронних таблиць Excel: 

• Заходимо на вкладку «Дані» та обираємо пункт «Перевірка даних». 

У вікні вказуємо: 

• Тип даних — обираємо «перелік»; 

• Джерело — виділяємо діапазон зі зразковим переліком.

У першому стовпці таблиці з'явиться кнопка «Перелік». Нею можна скористатись, щоб надалі вводити дані без помилок. Далі обираємо пункт «Перевірка даних — Обвести неправильні дані» і комірки, в яких є друкарські помилки, будуть виділені овалом.

8. Формування зведеної таблиці

Підбиття підсумків за період — це невід'ємна частина роботи бухгалтера. Формування зведеної таблиці з підсумковими показниками значно спростить справу. 

Для цього виконуйте інструкції: 

• Повністю виділяємо діапазон з таблицею, переходимо на вкладку «Вставка» та обираємо пункт «Зведена таблиця». 

• У вікні, що з’явилось, обираємо опцію «Помістити звіт на новий аркуш». 

• Заходимо на нову вкладку та обираємо поля для нової таблиці. 

• Для підбиття підсумків, обираємо діапазон даних за потрібний період, клацаємо правою кнопкою мишки та обираємо пункт «Групувати».

9. Вибір показників у межах ліміту 

Щоб відібрати витрати у межах встановленого ліміту, потрібно виконати в Excel наступні дії: 

• Заходимо на вкладку «Файл», обираємо пункт «Параметри Excel – Надбудови».

• У вікні ставимо галочку навпроти пункту «Пошук рішення» і клацаємо Ок (надбудова «Пошук рішення» з'явиться на вкладці «Дані»).

У вільній комірці поруч із таблицею вказуємо максимальну суму (S1), а нижче знаходимо S2.  Для цього використовуємо формулу: «=СУММПРОИЗВ(Діапазон1*Діапазон)», де: 

• Діапазон1 — стовпчик зі значеннями, серед яких ми підбираємо потрібні; 

• Діапазон2 — стовпчик, де буде вказано, які значення потрібно скласти. 

Нижче знаходимо різницю S1 та S2 за формулою «=ABS(S1-S2)». Переходимо на вкладку «Дані», обираємо пункт «Пошук рішення».

У вікні, що з’явилось, вказуємо: 

• Встановити цільову комірку: комірка з формулою «=ABS(S1-S2)»; 

• Рівний: мінімальному значенню; 

• Змінюючи комірки: Діапазон2 — стовпець, де буде вказано, які значення потрібно скласти; Обмеження: додаємо для Діапазон2 та вибираємо опцію «бін» («двійк»). 

Клацаємо «Знайти рішення» або «Виконати» та зберігаємо результат. Навпроти необхідних сум побачимо одиниці, а непотрібних — нулі. При додаванні відібраних програмою значень отримаємо число, наближене до максимального значення.

10. Визначення впливу зміни даних на кінцевий розрахунок 

А тепер навчимося відстежувати, як зміна даних відображається на кінцевому розрахунку на прикладі кошторису витрат: 

• Заходимо на вкладку «Формули» і обираємо пункт «Вікно контрольного значення». 

• У вікні обираємо «Додати контрольне значення» і вказуємо комірку з підсумковою сумою. Можна додати кілька контрольних показників. 

• Тепер при переході на інший аркуш контрольне вікно залишатиметься на екрані. При зміні даних на інших аркушах буде одразу видно, як це впливає на підсумкові показники.

11. Функція ЯКЩО і перелік, що випадає 

Одна з найбільш затребуваних функцій в Excel — функція ЯКЩО. Вона повертає результат (значення чи іншу формулу) залежно від умови. Функцію ЯКЩО створюють за таким синтаксисом: ЯКЩО (лог_вираз; значення_якщо_істина; [значення_якщо_неправдиво]) 

• Лог_вираз — це умова, яку потрібно перевірити.

• Значення_якщо_істина — це те, що має з'явитись в комірці, якщо текст або цифра відповідають зазначеним вимогам. 

• Значення_якщо_неправдиве — те, що з'явиться в комірці, коли буде визначено, що дані не відповідають заданим параметрам. 

Приклад: =ЯКЩО(В2> 200; «Ок»; «Видалити»)

Функція перевіряє комірку В2 і порівнює її з логічним виразом — «> 200». Якщо справжнє значення містить цифру більше 200, то в стовпці С з'явиться значення Ок, якщо менше — Видалити. 

Перелік, що випадає в Excel, можна створити за пару кліків. Для цього потрібно: 

1. Відкрити таблицю з даними, які необхідно додати до списку, що випадає. Для версії Excel 2003 і більш ранніх версій треба робити це через меню «Вставка — Ім'я — Присвоїти» (Insert - Name - Define).

2. Для версій Excel 2007 і більш ранніх потрібно клацнути на вкладку «Формули» —  «Диспетчер імен» та «Створити». Введіть назву позицій (наприклад Рахунки на оплату послуг), які бажаєте згрупувати, та підтвердіть її через «Ок». 

Важливо: ім'я має бути без прогалин і починатись з літери. 

3. Виділити комірки, які повинні стати списком, що випадає. Виберіть меню із вкладки «Дані» — «Перевірка» (Data - Validation). З запропонованого переліку оберіть «Тип даних», введіть рядок «Джерело»= назва діапазону Рахунку на оплату послуг. Готово!

12. Набір гарячих клавіш Excel, без яких не обійтись

Застосування цих клавіш в Excel прискорить роботу і допоможе у виконанні аналізу даних, побудові графіків та форматуванні таблиць.

• F4 — при введенні формули, регулює тип посилань (відносні, фіксовані). Можна використовувати для повторення останньої дії. 

• Shift+F2 — редагування приміток.

• Ctrl+; — введення поточної дати (для деяких комп'ютерів Ctrl+Shift+4) 

• Ctrl+' — копіювання значень комірки, що знаходиться над поточною (для деяких комп'ютерів працює комбінація Ctrl+Shift+2) 

• Alt+F8 — відкриття редактора макросів.

• Alt+= — підсумовування діапазону комірок, що знаходяться зверху або зліва від поточної комірки.

• Ctrl+Shift+4 — визначає грошовий формат комірки.

• Ctrl+Shift+7 — встановлення зовнішнього кордону виділеного діапазону комірок. Ctrl+Shift+0 — визначення загального формату комірки.

• Ctrl+Shift+F — комбінація відкриває діалог комірок.

• Ctrl+Shift+L — увімкнення/вимкнення фільтра.

• Ctrl+S — збереження файлу (зберігайте якомога частіше, щоб не втратити цінні дані).

Використання гарячих клавіш та корисних прийомів у Excel спростять та прискорять вашу роботу лише за умови, якщо ви вже на достатньому рівні володієте цією програмою. Якщо ви бажаєте підвищити свій рівень та ефективніше використовувати електронні таблиці, вести бухгалтерський облік в Ексель, то можете безкоштовно завантажити книгу Макроси в Excel для початківців — схеми створення та застосування, в якій приділено увагу питанням повсякденного використання Ексель у бухгалтерії. Книжка підійде як початківцям, так і досвідченим користувачам програми, які прагнуть підвищити свою кваліфікацію.