Створення бази даних в Excel
- Крок 1. Вихідні дані у вигляді таблиць
- Крок 2. Створюємо форму для введення даних
- Крок 3. Додаємо макрос введення продажів
- Крок 4. Зв'язуємо таблиці
- Крок 5. Будуємо звіти за допомогою зведеної
- Крок 6. Заповнюємо друковані форми
При згадці баз даних (БД) в першу чергу, звичайно, в голову приходять всякі розумні слова типу SQL, Oracle, 1С або хоча б Access. Безумовно, це дуже потужні (і недешеві в більшості своїй) програми, здатні автоматизувати роботу великої і складної компанії з купою даних. Біда в тому, що іноді така міць просто не потрібна. Ваш бізнес може бути невеликим і з відносно нескладними бізнес-процесами, але автоматизувати його теж хочеться. Причому саме для маленьких компаній це, найчастіше, питання виживання.
Для початку давайте сформулюємо ТЗ. У більшості випадків база даних для обліку, наприклад, класичних продажів повинна вміти:
- зберігати в таблицях інформацію стосовно товарів (прайс), зроблених операціях і клієнтам і пов'язувати ці таблиці між собою
- мати зручні форми введення даних (з випадають списками і т.п.)
- автоматично заповнювати цими даними якісь друковані бланки (платіжки, рахунки і т.д.)
- видавати необхідні вам звіти для контролю за все бізнес-процесу з точки зору керівника
З усім цим цілком може впоратися Microsoft Excel, якщо докласти трохи зусиль. Давайте спробуємо це реалізувати.
Крок 1. Вихідні дані у вигляді таблиць
Інформацію про товари, продажах і клієнтів будемо зберігати в трьох таблицях (на одному аркуші або на різних - все одно). Принципово важливо, перетворити їх в "Розумні таблиці" з автопідстроюванням розмірів , Щоб не думати про це в майбутньому. Це робиться за допомогою команди Форматувати як таблицю на вкладці Основне (Home - Format as Table). На що з'явилася потім вкладці Конструктор (Design) призначимо таблицями наочні імена в поле Ім'я таблиці для подальшого використання:
Разом у нас повинні вийти три "розумних таблиці":
Зверніть увагу, що таблиці можуть містити додаткові уточнюючі дані. Так, наприклад, наш Прайс містить додатково інформацію про категорії (товарної групи, упаковці, вагою і т.п.) кожного товару, а таблиця Клієнти - місто і регіон (адреса, ІПН, банківські реквізити тощо) кожного з них .
Таблиця Продажі буде використовуватися нами згодом для занесення в неї укладених угод.
Крок 2. Створюємо форму для введення даних
Само-собою, можна вводити дані про продажі безпосередньо в зелену таблицю Продажі, але це не завжди зручно і тягне за собою появу помилок і друкарських помилок через "людський фактор". Тому краще буде на окремому аркуші зробити спеціальну форму для введення даних приблизно такого вигляду:
В осередку B3 для отримання оновлюваної поточної дати-часу використовуємо функцію ТДАТУ (NOW). Якщо час не потрібно, то замість ТДАТУ можна застосувати функцію СЬОГОДНІ (TODAY).
В осередку B11 знайдемо ціну обраного товару в третьому стовпці розумної таблиці Прайс за допомогою функції ВПР (VLOOKUP). Якщо раніше з нею не стикалися, то спочатку почитайте і подивіться відео тут .
В осередку B7 нам потрібен список, що випадає з товарами з прайс-листа. Для цього можна використовувати команду Дані - Перевірка даних (Data - Validation), вказати в якості обмеження Список (List) і ввести потім в поле Джерело (Source) посилання на стовпець Найменування з нашої розумної таблиці Прайс:
Аналогічним чином створюється список, що випадає з клієнтами, але джерело буде вже:
= ДВССИЛ ( "Клієнти [Клієнт]")
Функція ДВССИЛ (INDIRECT) потрібна, в даному випадку, тому що Excel, на жаль, не розуміє прямих посилань на розумні таблиці в поле Джерело. Але та ж посилання "загорнута" в функцію ДВССИЛ працює при цьому "на ура" (докладніше про це було в статті про створення випадаючих списків з наповненням ).
Крок 3. Додаємо макрос введення продажів
Після заповнення форми потрібно введені в неї дані додати в кінець таблиці Продажі. Сформуємо за допомогою простих посилань рядок для додавання прямо під формою:
Тобто в осередку A20 буде посилання = B3, в осередку B20 посилання на = B7 і т.д.
Тепер додамо елементарний макрос в 2 рядки, який копіює створену рядок і додає її до таблиці Продажі. Для цього тиснемо поєднання Alt + F11 або кнопку Visual Basic на вкладці Розробник (Developer). Якщо цю вкладку не видно, то включіть її спочатку в налаштуваннях Файл - Параметри - Налаштування стрічки (File - Options - Customize Ribbon). У вікні редактора Visual Basic вставляємо новий порожній модуль через меню Insert - Module і вводимо туди код нашого макросу:
Sub Add_Sell () Worksheets ( "Форма введення"). Range ( "A20: E20"). Copy 'копіюємо рядок з даними з форми n = Worksheets ( "Продажі"). Range ( "A100000"). End (xlUp). Row 'визначаємо номер останнього рядка в табл. Продажі Worksheets ( "Продажі"). Cells (n + 1, 1) .PasteSpecial Paste: = xlPasteValues 'вставляємо в наступну порожню рядок Worksheets ( "Форма введення"). Range ( "B5, B7, B9"). ClearContents' очищаємо форму End Sub
Тепер можна додати до нашої формі кнопку для запуску створеного макросу, використовуючи список, що випадає Вставити на вкладці Розробник (Developer - Insert - Button):
Після того, як ви її намалюєте, утримуючи ліву кнопку миші, Excel сам запитає вас - який саме макрос потрібно на неї призначити - вибираємо наш макрос Add_Sell. Текст на кнопці можна поміняти, клацнувши по ній правою кнопкою миші і вибравши команду Змінити текст.
Тепер після заповнення форми можна просто тиснути на нашу кнопку, і введені дані будуть автоматично додаватися до таблиці Продажі, а потім форма очищається для введення нової угоди.
Крок 4. Зв'язуємо таблиці
Перед побудовою звіту зв'яжемо наші таблиці між собою, щоб потім можна було оперативно обчислювати продажу по регіонах, клієнтам або категоріям. У старих версіях Excel для цього треба було б використовувати кілька функцій ВПР (VLOOKUP) для підстановки цін, категорій, клієнтів, міст і т.д. в таблицю Продажі. Це вимагає часу і сил від нас, а також "їсть" чимало ресурсів Excel. Починаючи з Excel 2013 все можна реалізувати значно простіше, просто налаштувавши зв'язку між таблицями.
Для цього на вкладці Дані (Data) натисніть кнопку Відносини (Relations). У вікні натисніть кнопку Створити (New) і виберіть з випадаючих списків таблиці і назви стовпців, за якими вони повинні бути пов'язані:
Важливий момент: таблиці потрібно задавати саме в такому порядку, тобто пов'язана таблиця (Прайс) не повинна містити в ключовому стовпці (Найменування) повторюваних товарів, як це відбувається в таблиці Продажі. Іншими словами, пов'язана таблиця повинна бути тієї, в якій ви шукали б дані за допомогою ВВР, якби її використовували.
Само-собою, аналогічним чином зв'язуються і таблиця Продажі з таблицею Клієнти за загальним колонки Клієнт:
Після настройки зв'язків вікно управління зв'язками можна закрити, повторювати цю процедуру вже не доведеться.
Крок 5. Будуємо звіти за допомогою зведеної
Тепер для аналізу продажів і відстеження динаміки процесу, сформуємо для прикладу будь-якої звіт за допомогою зведеної таблиці . Встановіть активний осередок в таблицю Продажі і виберіть на стрічці вкладку Вставка - Зведена таблиця (Insert - Pivot Table). У вікні Excel запитає нас про джерело даних (тобто таблицю Продажі) і місце для вивантаження звіту (краще на новий лист):
Життєво важливий момент полягає в тому, що потрібно обов'язково включити прапорець Додати ці дані в модель даних (Add data to Data Model) в нижній частині вікна, щоб Excel зрозумів, що ми хочемо будувати звіт не тільки по поточній таблиці, але і задіяти всі зв'язки .
Після натискання на ОК в правій половині вікна з'явиться панель Поля зведеної таблиці, де потрібно клацнути по посиланню Все, щоб побачити не тільки поточну, а відразу все "розумні таблиці", які є в кніге.А потім можна, як і в класичній зведеної таблиці , просто перетягнути мишею потрібні нам поля з будь-яких пов'язаних таблиць в області Фільтра, Строк, Стовпців або Значний - і Excel моментально побудує будь-який потрібний нам звіт на аркуші:
Не забудьте, що зведену таблицю потрібно періодично (при зміні вихідних даних) оновлювати, клацнувши по ній правою кнопкою миші і вибравши команду Оновити (Refresh), тому що автоматично вона цього робити не вміє.
Також, виділивши будь-який осередок у зведеній і натиснувши кнопку Зведена діаграма (Pivot Chart) на вкладці Аналіз (Analysis) або Параметри (Options) можна швидко візуалізувати пораховані в ній результати.
Крок 6. Заповнюємо друковані форми
Ще однією типовою завданням будь-якої БД є автоматичне заповнення різних друкованих бланків та форм (накладні, рахунки, акти і т.п.). Про один із способів це зробити, я вже якось писав . Тут же реалізуємо, для прикладу, заповнення форми за номером рахунку:
Передбачається, що в осередок C2 користувач буде вводити число (номер рядка в таблиці Продажі, по суті), а потім потрібні нам дані підтягуються за допомогою вже знайомої функції ВПР (VLOOKUP) і функції ІНДЕКС (INDEX).