- Як створити реляційну базу даних в Excel
- Створення основної і додаткової таблиць
- Визначення відносин між таблицями
Час від часу у всіх нас виникає необхідність створити невелику базу даних зі зручною і зрозумілою логікою і інтерфейсом, але при цьому бажання возитися з Access або іншими подібними програмами абсолютно відсутня. Більш того, додатках баз даних мають багато в чому обмежений арсенал інструментів аналізу, розрахунків і візуалізації. Ось тут в голову нам приходить ідея створити базу даних в Excel (тим більше, багато користувачів використовують її в основному як місце зберігання даних).
На жаль (а може і на щастя), Excel в першу чергу є електронною таблицею, тому щоб створити базу даних в цій програмі, потрібно вибудовувати зв'язку за допомогою формул підстановки , Створювати інтерфейс на VBA і додавати дашборда . Все це було до недавнього часу. З приходом Excel 2013, програма обзавелася новими інструментами роботи з таблицями, що дозволяють пов'язувати діаграмами та комірками, виконувати пошук і створювати динамічно оновлювані звіти. Все вірно!!! Як в реляційних базах даних. Excel може щодня обробляти великі кількість даних. Яким чином? Читайте далі.
Як створити реляційну базу даних в Excel
Реляційна база даних - це така база, в якій відносини між інформацією в таблицях чітко визначені. Це важливо для роботи з великою кількістю бізнес інформації. Такий підхід дозволяє швидко знаходити і отримувати інформацію, відображати одну і ту ж інформацію під різним ракурсом і уникати помилок і дублювання. Спробуємо зробити щось подібне за допомогою Excel.
Щоб полегшити завдання, розберемо приклад з двома таблицями: основної та додаткової. Основна таблиця найчастіше містить унікальні записи (такі як ім'я, адреса, місто, область і т.д.). Вона рідко редагується, за виключення, якщо, наприклад, вам потрібно додати або видалити запис.
Однією записи основної таблиці може відповідати кілька записів з додатковою (або дочірньої) таблиці. Цей зв'язок називається один-ко-многим. Інформація в дочірній таблиці - така як, щоденні продажі, ціна на продукт, кількість - зазвичай періодично змінюється.
Щоб уникнути повторення всієї інформації з основної таблиці в додатковій таблиці, необхідно створити відносини, використовуючи унікальне поле, таке як ID Продаж, і дозволити Excel зробити все інше. Наприклад, у вас є 10 продавців зі своєю унікальною інформацією (основна таблиця). Кожен продавець має 200 продуктів, які він продає (додаткова таблиця). В кінці року вам необхідно створити звіт, який відображає результати продажів кожного співробітника. Плюс до цього, вам необхідно створити звіт, який відображає результати продажів по містах.
У цьому прикладі ми створимо основну таблицю з інформацією про продавців і додаткову таблицю, яка відображає інформацію про продажі. ID Продаж - це поле, яке буде з'єднувати таблиці. В кінцевому підсумку ми створимо звіт (або зведену таблицю ), Яка покаже інформацію з максимальними продажами.
Створення основної і додаткової таблиць
Наша основна таблиця буде містити 4 поля: ID Продаж, Ім'я продавця, Адреса і Місто. Створіть таблицю аналогічно зображенню на малюнку. Дані для таблиці можна взяти з файлу прикріпленому в кінці статті.
Моя таблиця має двадцять продавців з 3-х різних міст, за якими в подальшому ми будемо робити звіт.
Тепер необхідно перетворити дані в справжню таблицю Excel. Для цього виділяємо весь діапазон разом з заголовками. Переходимо у вкладку Головна в групу Стилі. Натискаємо кнопку Форматувати як таблицю, з випадаючого меню вибираємо стиль, який ми хочемо надати таблиці.
Після клацання по стилю з'явиться діалогове вікно Форматування таблиці, в якому ви побачите поле з адресою виділеного діапазону. Переконайтеся, що стоїть галочка Таблиця із заголовками і натисніть кнопку ОК. Після цього діапазон даних придбає обраний формат і перетвориться в таблицю Excel.
Залишилося дати ім'я нашої таблиці. Виберіть будь-яку клітинку в таблиці, перейдіть по вкладці Робота з таблицями -> Конструктор в групу Властивості. В поле Ім'я таблиці поміняйте назву таблиці на Основний.
Аналогічним способом створюємо додаткову таблицю з інформацією про суму продажів по кварталах для кожного співробітника. Зовнішній вигляд оформлення таблиці ви бачите на зображенні нижче.
Також таблиці необхідно дати більш осмислену назву, наприклад, Продажі.
Визначення відносин між таблицями
Перше правило зведених таблиць: відносини між таблицями визначаються в системі звітів зведеної таблиці, з використанням інструменту Відносини. Не намагайтеся з самого початку визначити зв'язку між таблицями - інструмент формування звітів зведених таблиць все одно їх не сприйме.
Виділяємо таблицю з продажами, у вкладці Вставка переходимо до групи Таблиці, клацаємо Зведена таблиця.
У діалоговому вікні Створення зведеної таблиці в поле Таблиці або діапазон переконуємося, що вказана таблиця Продажі. Також ставимо галку навпроти поля Додати ці дані в модель даних і клацаємо кнопку ОК.
Excel створить новий лист з порожньою зведеною таблицею. У лівій частині екрана з'явиться панель Поля зведеної таблиці. Щоб звести дані обох таблиць, в панелі Поля зведеної таблиці вкладки Активна проставте галочки навпроти пунктів Квартал 1, Квартал 2, Квартал 3 і Квартал 4. Excel побудує зведену таблицю з даними по кварталах, поки не звертайте на неї увагу. Далі в цій же панелі переходимо на вкладку Все, де ви побачите обидві наші таблиці. Ставимо галочку навпроти поля Місто, таблиці Основний. З'явиться жовте поле з повідомленням Можуть знадобитися зв'язку між таблицями, клацаємо кнопку Створити.
У діалоговому вікні Створення зв'язку необхідно визначити відносини між таблицями. Виберіть зі списку Таблиця пункт Продажі, а зі списку Стовпець (Чужий) пункт ID Продаж. Пам'ятайте, що ID Продаж єдине поле, яке знаходиться в обох таблицях. Зі списку Пов'язана таблиця вибираємо Основний, зі списку Зв'язаний стовпець (первинний ключ) - пункт ID Продаж. Тиснемо ОК.
Excel створить зв'язку і відобразить результуючий звіт на екрані. Дайте ім'я вашого звіту, і він буде готовий.
З новими можливостями створення реляційних баз даних і таблиць, цей процес значно спрощується. І ви можете будувати необхідні звіти і отримувати специфічні дані за лічені хвилини.
