В управлінні запасами Excelє незамінним інструментом. Розглянемо приклад.

Завдання закупника полягає в тому, щоб постійно контролювати, які товари потрібно замовити у постачальника, а яких товарів навпаки забагато і їх потрібно якнайшвидше продати (щоб пізніше не довелося знищувати через закінчення термінів придатності). На цьому прикладі я покажу, як легко можна отримати цінну інформацію з великого масиву даних та на її основі прийняти грамотні раціональні рішення, які принесуть велику користь (тобто багато грошей). Вихідна інформація – це, звичайно, залишки товару на складі. Проте темпи продажу в різних товарів різні, тому дані тільки про кількість товару на складі не мають достатньої інформативності. Один товар продається за 10 шт. на місяць, інший – по 100 шт. в місяць. Очевидно, що однакова кількість товару на складі означатиме різний період продажу, на який вистачить залишків. Тому для аналізу необхідно залучити дані про темпи продажу. Зазвичай у цій ролі виступають середні продажі за минулий період. Їх і візьмемо.

Вихідна таблиця має вигляд.

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

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

З такої таблиці можна робити деякі висновки. Припустимо, час, який потрібно на доставку товару на склад – 1 місяць. Тепер дивимося на останній стовпець. Запас за деякими товарами становить менше 1 місяця, це означає, що у разі розміщення замовлення прямо зараз дефіциту вже не уникнути, оскільки товар продадуть раніше, ніж встигнуть привезти нову партію. Потрібно терміново робити нове замовлення, а також не допускати такої ситуації надалі, тому що дефіцит товару – це недоотриманий прибуток та душевний розлад клієнтів.

Також видно, що запас за деякими товарами суттєво більше одного місяця. Це може означати необґрунтовано заморожені гроші. Заморожені кошти - це теж зло (потрібно брати кредити та сплачувати відсотки).

Таким чином, у таблиці можна виявити товари, які слід швидше продати, та товари, які потрібно терміново замовити у постачальника. Цей приклад спрощений, насправді обсяг даних і кількість умов та обмежень у десятки та сотні разів більше. Асортимент може складатися із тисяч позицій. Тому навіть якщо ми розрахуємо останній стовпець, який показує час, на які вистачить запасу, то в нас може піти тиждень на вивчення стану складу. А поки мине тиждень, ситуація зміниться і потрібно буде знову оновлювати дані. Загалом, ця таблиця хоч і відображає корисну інформаціюале непридатна для оперативного використання. Для більш точної, наочної та швидкої роботидо таблиці необхідно внести доповнення. Одна з моїх улюблених функцій Excel- умовне форматування. Її суть полягає в тому, що при настанні деякої умови осередок змінює свій формат (шрифт, колір та ін.). Ця функціяв автоматичному режиміозначає критично важливі значення, тобто товари, з якими потрібно терміново щось зробити. Зрозуміло, що всі налаштування та розрахунки залежать від конкретного завдання. Нас, припустимо, в першу чергу дуже цікавить недопущення дефіциту, тобто максимально можлива наявність всього асортименту на складі. Для цього потрібно уважно контролювати запаси, тобто не допускати, щоб їхній рівень зменшився нижче, ніж 1 місяць продажу (час на поповнення).

Використовуючи функцію умовного форматування, наша табличка може набути такого вигляду.

Я нічого не розфарбовував вручну, слово честі. Тільки один раз поставив умову та зразок для розфарбовування, а Excel сам все розфарбував. Відмінно видно, що три товари настав критичний запас і дефіцит неминучий. Інформація з такої таблиці сприймається моментально, ймовірність чогось не помітити практично відсутня. Однак той факт, що товару менше, ніж на 1 місяць продажів, констатує наявність проблеми, що вже є. Проблем же потрібно намагатися уникати, тобто замовляти товар слід тоді, коли його запас становить трохи більше ніж 1 місяць продажу. У цьому випадку на момент приходу наступної партії на складі ще залишатиметься запас із попередньої партії. Дефіциту вдасться уникнути. Табличка заграє новими фарбами.

Видно, терміново потрібно розміщувати замовлення на 6 позицій. Причому 3 з них вже потрапляють у дефіцит, замовлення розміщене надто пізно. Але краще, пізно ніж ніколи. Яскравість фарб відповідає рівню небезпеки.

Уявімо тепер, що товар порається від різних постачальників і замовлення потрібно планувати заздалегідь, скажімо, за 2 тижні до відправки заявки. Тому коли запас товару менший, ніж 2 місяці продажу, але ще не досяг 1,5 місяця, нам потрібно про це знати. Легко!

Зелененькі осередки – це і є товари, які слід замовити найближчим часом.

Отже, нескладні маніпуляції над вихідною купою сірих однорідних даних дозволяють отримати важливу інформаціюпро те, що потрібно терміново замовити 6 позицій та ще 6 позицій планувати до замовлення найближчим часом.

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

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

Нині кількість різних звітів, які готуються всіма підрозділами організацій, невпинно зростає. Найчастіше на підприємствах здійснюється автоматизація звітності на основі різних програмних продуктів (SAP, 1С, Інталев та інше). Але навіть у тих організаціях, де система "автоматизована", не обходяться без використання Excel. Оскільки в програмних продуктах, які використовуються для автоматизації, закладені загальні принципипобудови звітів, а кожне підприємство – унікальне, складно уніфікувати всю звітність у спеціалізованих програмних продуктах, а інколи – і неможливо це зробити. І в цьому випадку на допомогу приходять електронні таблиці Excel. Вони дозволяють доопрацьовувати звіти, представляти інформацію, отриману при вивантаженні з тієї ж 1С, у необхідному вигляді, а іноді й взагалі є основною програмою ведення управлінської та іншої звітності.

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

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

Так чи інакше, бухгалтерія в Excel стане набагато простіше, якщо освоїти кілька корисних прийомів, які зможуть прискорити роботу з даними. Дізнайтеся, чим може бути корисним Excel для бухгалтера в прикладах, які ми підготували. Наступні вправи в Ексель для роботи бухгалтерів виявляться не просто корисними, а необхідними.

Фундаментальний інструмент Excel

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

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

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

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

Можливість простягати формули – одна з базових функцій програми. Вона автоматизує процес підрахунку даних у таблиці, без багаторазового прописування однієї й тієї формули. Виконувати протягування формул можна в такий спосіб.

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

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

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

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

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

Щоб виконати розрахунки, необхідно виділити три вільні осередки в таблиці. В одну потрібно записати початкову дату, в другу кінцеву, а третю залишити порожньою для отримання результатів.

Далі вибираємо третю комірку і тиснемо "Вставити функцію", ви можете знайти її за значком ¶x. Після натискання з'явиться вікно “Майстер функцій”. Зі списку "Категорія" вибираємо "Дата і час", а зі списку "Функція"- "ДНІВ360" і натискаємо кнопку Ок. У вікні потрібно вставити значення початкової та кінцевої дати. Для цього потрібно просто клацнути по осередках таблиці з цими датами, а в рядку "Метод" поставити одиницю та натиснути Ок. Якщо підсумкове значення відображено не у числовому форматі, потрібно перевірити формат комірки: клацнути правою кнопкою миші та вибрати з меню Формат комірки, встановити Числовий формат і натиснути Ок.

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

Також до дати можна додавати та забирати будь-яку кількість днів. Щоб це виконати, потрібно в одному осередку написати дату, в другому розмістити знак рівності, потім клацнути по осередку з датою і набрати "+" або "-" та потрібну кількість днів.

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

Дуже зручна функція, яка дозволяє розмістити дані щодо зростання/зменшення. Також сортувати дані можна й упорядкування записів за датою.

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

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

Якщо дані потрібно сортувати за середнім стовпцем, можна використовувати меню “Дані” - пункт “Сортування” - “Сортування діапазону”. У розділі “Сортувати за” необхідно вибрати стовпець та тип сортування.

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

Таблиці Excel для бухгалтера - багатофункціональний робочий інструмент, який містить велику кількість інформації для ведення звітності та виконання поточних розрахунків. Під час друку таблиці, яка не вміщується на один аркуш, можна розмістити її “шапку” на кожній окремій сторінці, що полегшить пошук необхідних даних. Для цього потрібно вибрати в меню "Файл" - "Параметри сторінки" та закладку "Лист". Розміщуємо курсор на "Наскрізні рядки" або "Наскрізні стовпці" і в таблиці клацаємо на рядки, які потрібно розмістити на кожному аркуші.

Також для роботи з такими документами можна використовувати колонтитули. Вони відзначають необхідні дані, такі як дата, номери листів, ім'я укладача та інше. Налаштування колонтитулів доступне в “Параметрах сторінки” – “Колонтитули”. Там є готові варіанти розмітки або можливість додавання власного.

Крім корисних прийомів роботи в Ексель, бухгалтеру необхідно освоїти його гарячі клавіші.

Набір гарячих клавіш 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 спростять та прискорять вашу роботу лише за умови того, що ви вже на достатньому рівні володієте цією програмою. Якщо ви хочете підвищити свій рівень та ефективніше використовувати електронні таблиці вести бухгалтерський облік в Екселі, то можете безкоштовно скачати книгу