зміст
При згадці баз даних (БД) в першу чергу спадають на думку, звичайно ж, всілякі модні слова на кшталт SQL, Oracle, 1C або хоча б Access. Звичайно, це дуже потужні (і дорогі здебільшого) програми, які можуть автоматизувати роботу великої та складної компанії з великою кількістю даних. Біда в тому, що іноді така потужність просто не потрібна. Можливо, ваш бізнес невеликий і має відносно прості бізнес-процеси, але ви також хочете його автоматизувати. І саме для невеликих компаній це часто питання виживання.
Для початку сформулюємо ТЗ. У більшості випадків база даних для обліку, наприклад, класичних продажів повинна вміти:
- тримати у таблицях інформацію про товари (ціни), здійснені операції та клієнтів і зв’язати ці таблиці між собою
- мати зручні форми введення дані (з розкривними списками тощо)
- автоматично заповнити деякі дані друковані форми (платежі, рахунки тощо)
- видати необхідне звіти контролювати весь бізнес-процес з точки зору менеджера
Microsoft Excel може впоратися з усім цим, доклавши трохи зусиль. Давайте спробуємо це реалізувати.
Крок 1. Вихідні дані у вигляді таблиць
Ми будемо зберігати інформацію про товари, продажі та клієнтів у трьох таблицях (на одному аркуші чи на різних – не має значення). Принципово важливо перетворити їх на «розумні таблиці» з авторозміром, щоб не думати про це в майбутньому. Це робиться за допомогою команди Відформатувати як таблицю таб Головна (Домашня сторінка — форматувати як таблицю). На вкладці, яка потім з’явиться Конструктор (Дизайн) дайте таблицям описові назви в полі назва таблиці для подальшого використання:
Всього у нас має вийти три «розумні таблиці»:
Зверніть увагу, що в таблицях можуть бути додаткові уточнюючі дані. Так, наприклад, наш цінамістить додаткову інформацію про категорію (товарну групу, упаковку, вагу тощо) кожного товару та табл Клієнт — місто та регіон (адреса, ІПН, банківські реквізити тощо) кожного з них.
таблиця Sales буде використовуватися нами пізніше для введення в нього завершених транзакцій.
Крок 2. Створення форми введення даних
Звичайно, ви можете вводити дані про продажі безпосередньо в зелену таблицю Sales, але це не завжди зручно і тягне за собою появу помилок і опечаток через «людського фактору». Тому краще було б зробити спеціальну форму для внесення даних на окремому аркуші приблизно такого вигляду:
У клітинці B3, щоб отримати оновлені поточні дату й час, скористайтеся функцією TDATA (ЗАРАЗ). Якщо час не потрібен, то замість нього TDATA можна застосувати функцію СЬОГОДНІ (СЬОГОДНІ).
У клітинці B11 знайдіть ціну вибраного продукту в третьому стовпці розумної таблиці ціна за допомогою функції VPR (ВПР). Якщо ви раніше з цим не стикалися, то спочатку прочитайте і подивіться відео тут.
У клітинці B7 нам потрібен випадаючий список з товарами з прайс-листа. Для цього можна скористатися командою Дані – Перевірка даних (Дані — Перевірка), вкажіть як обмеження список (Список) а потім введіть у поле Source (Джерело) посилання на колонку ІМ'Я з нашого розумного столу ціна:
Аналогічно створюється випадаючий список з клієнтами, але джерело буде вужчим:
=INDIRECT(“Клієнти[Клієнт]”)
функція НЕПРАВИЛЬНО (НЕПРЯМИЙ) у цьому випадку потрібен, оскільки Excel, на жаль, не розуміє прямих посилань на розумні таблиці в полі Джерело. Але те саме посилання «загорнуте» у функцію НЕПРАВИЛЬНО при цьому працює на ура (докладніше про це було в статті про створення випадаючих списків з вмістом).
Крок 3. Додавання макросу запису продажу
Після заповнення форми в кінець таблиці потрібно додати введені в неї дані Sales. Використовуючи прості посилання, ми сформуємо рядок, який буде додано прямо під формою:
Ті. клітинка A20 матиме посилання на =B3, клітинка B20 матиме посилання на =B7 і так далі.
Тепер давайте додамо 2-рядковий елементарний макрос, який копіює згенерований рядок і додає його до таблиці Sales. Для цього натисніть комбінацію Alt + F11 або кнопку Візуальний Бейсік таб розробник (розробник). Якщо ця вкладка не відображається, то спочатку увімкніть її в налаштуваннях Файл – Параметри – Налаштування стрічки (Файл — Параметри — Налаштувати стрічку). У вікні редактора Visual Basic, що відкриється, вставте новий порожній модуль через меню Вставка – модуль і введіть туди наш код макросу:
Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Copy 'Копіювати рядок даних із форми n = Worksheets("Sales").Range("A100000").End(xlUp) . Рядок 'визначте номер останнього рядка в таблиці. Sales Worksheets("Sales").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues 'вставте в наступний порожній рядок Worksheets("Input Form").Range("B5,B7,B9"). ClearContents 'очистити кінцеву підформу Тепер ми можемо додати кнопку до нашої форми для запуску створеного макросу за допомогою спадного списку Insert таб розробник (Розробник — Вставити — Кнопка):
Після того, як ви намалюєте його, утримуючи ліву кнопку миші, Excel запитає вас, який макрос йому потрібно призначити – виберіть наш макрос Додати_Продати. Ви можете змінити текст кнопки, клацнувши її правою кнопкою миші та вибравши команду Змінити текст.
Тепер після заповнення форми ви можете просто натиснути на нашу кнопку, і введені дані будуть автоматично додані в таблицю Sales, а потім форму очищають, щоб ввести нову угоду.
Крок 4 Зв'язування таблиць
Перш ніж створювати звіт, давайте зв’яжемо наші таблиці, щоб пізніше ми могли швидко розрахувати продажі за регіоном, клієнтом або категорією. У старих версіях Excel для цього потрібно було б використовувати кілька функцій. VPR (ВПР) для підстановки в таблицю цін, категорій, клієнтів, міст тощо Sales. Це вимагає від нас часу та зусиль, а також «з’їдає» багато ресурсів Excel. Починаючи з Excel 2013, все можна реалізувати набагато простіше, встановивши зв’язки між таблицями.
Для цього на табл дані (Дата) клацання відносини (Стосунки). У вікні, що з’явилося, натисніть кнопку Створювати (новий) і виберіть зі спадних списків таблиці та назви стовпців, за якими вони мають бути пов’язані:
Важливий момент: таблиці повинні бути вказані в такому порядку, тобто зв'язана таблиця (ціна) не має містити в ключовому стовпці (ІМ'Я) повторювані продукти, як це відбувається в таблиці Sales. Іншими словами, пов’язана таблиця має бути тією, у якій ви шукатимете дані VPRякби його використовували.
Зрозуміло, стіл з'єднується схожим чином Sales зі столом Клієнт загальним стовпцем Клієнт:
Після налаштування посилань вікно керування посиланнями можна закрити; вам не потрібно повторювати цю процедуру.
Крок 5. Будуємо звіти за допомогою зведення
Тепер для аналізу продажів і відстеження динаміки процесу створимо, наприклад, якийсь звіт за допомогою зведеної таблиці. Установити активну комірку в таблицю Sales і виберіть вкладку на стрічці Вставка – зведена таблиця (Вставити — зведена таблиця). У вікні, що відкриється, Excel запитає нас про джерело даних (тобто таблицю Sales) і місце для завантаження звіту (бажано на новому аркуші):
Важливо, що необхідно включити прапорець Додайте ці дані до моделі даних (Додати дані до моделі даних) у нижній частині вікна, щоб Excel зрозумів, що ми хочемо побудувати звіт не лише на поточній таблиці, але й використовувати всі зв’язки.
Натиснувши на OK у правій половині вікна з’явиться панель Поля зведеної таблиціде натиснути посилання ВСІщоб побачити не лише поточну, а одразу всі «розумні таблиці», які є в книзі. А потім, як і в класичній зведеній таблиці, ви можете просто перетягнути потрібні поля з будь-якої пов’язаної таблиці в область фільтр, ряди, Столбцова or Цінності – і Excel миттєво створить на аркуші будь-який потрібний звіт:
Не забувайте, що зведену таблицю потрібно періодично оновлювати (при зміні вихідних даних), клацаючи по ній правою кнопкою миші та вибираючи команду Update & Save (Оновити), тому що він не може зробити це автоматично.
Також, вибравши будь-яку комірку у зведенні та натиснувши кнопку Зведена діаграма (Зведена діаграма) таб аналіз (Аналіз) or параметри (Параметри) ви можете швидко візуалізувати обчислені в ньому результати.
Крок 6. Заповніть роздруківки
Ще одним типовим завданням будь-якої бази даних є автоматичне заповнення різноманітних друкованих форм і бланків (рахунків-фактур, накладних, актів тощо). Про один із способів це я вже писав. Тут ми реалізуємо, наприклад, заповнення форми за номером рахунку:
Передбачається, що в клітинку C2 користувач введе число (номер рядка в таблиці Sales, власне), а потім потрібні нам дані підтягуються за допомогою вже знайомої функції VPR (ВПР) І особливості ІНДЕКС (ІНДЕКС).
- Як використовувати функцію VLOOKUP для пошуку та пошуку значень
- Як замінити VLOOKUP функціями INDEX і MATCH
- Автоматичне заповнення бланків і форм даними з таблиці
- Створення звітів за допомогою зведених таблиць