Многие называют свои таблицы «базами данных», в крайнем случае «реестрами». Но если вы когда-либо пытались построить сводную по списку, где в файле «База_клиентов_финал_3_исправлено.xlsx» в одной ячейке записано Иванов И., а в другой Иванов Иван, или мучительно искали заказ, потому что фамилия клиента «уехала» на три столбца вправо - поздравляю, вы столкнулись с чем угодно, но не с базой данных.
Привет! На связи Ленивый аналитик, и сегодня поговорим про организацию хранения данных.
Когда мы слышим «база данных», то на ум приходят всякие SQL-серверы, ораклы, DWH и прочее, в крайнем случае Acсess.
А что если... замутить базу данных в Excel?
Да можно всё)
Поверьте, компаний, которые ведут свою аналитику исключительно в Excel, больше, чем вам кажется.
Зачем вообще вести БД в Excel?
1. Это дешево
Excel, как правило, уже есть на компьютере (даже не факт, что купленный, но я плохому не учу)))
2. Нулевой порог входа
Не нужно разворачивать сервер, учить SQL или нанимать архитектора БД, который стоит дорого. Откроется на любом компьютере с офисом.
3. Встроенная аналитика, с которой справится почти каждый
... кто хоть мало-мальски умеет в сводные таблицы и графики.
4. Идеальный прототип
Перед переходом на 1С, Битрикс24 или PostgreSQL можно обкатать логику в Excel. Ошибки архитектуры дешевле исправить на этапе таблицы, чем на этапе миграции.
5. Контроль и прозрачность
Вы видите каждую ячейку, понимаете, как считается итог, и можете в любой момент проверить, что строки не замножились, а форматы данных не съехали.
Ну и из моей практики пример, когда Excel использовался для ведения базы. В компании была официальная база данных, которая велась в SAP. Жутко неудобная и не гибкая. В ней не было половины необходимых полей, следовательно, организовать из нее какую-то отчетность было просто невозможно.
С чего начать построение базы данных в Excel?
В целом, тут принцип тот же, что и с построением базы данных во "взрослых" системах - начать нужно со структуры базы. Я не архитектор баз данных, поэтому просто поделюсь из своего опыта аналитика.
База данных состоит из таблиц. Таблицы делятся на 2 вида: таблицы фактов и таблицы измерений.
>> Таблица фактов - это фиксирование основных операций. Примеры таблицы фактов: данные по чекам, данные по приему/увольнению сотрудников, отработанные часы и т.д.
>> Таблица измерений (или справочники) - это описательные характеристики. Например, категория продукта, сведения о клиентах и т.д.
Примеры для тех, кто пока ничего не понял:
Табель учета отработанного времени - это таблица фактов, а список сотрудников со сведениями о них (ФИО, возраст, образование и т.д.) - это таблица измерений.
еще
Реестр счетов на оплату аренды - это таблица фактов, а список арендодателей, реестр договоров на аренду - это таблицы измерений.
Таким образом, для построения базы данных в Excel первым делом составляем список таблиц и разделяем их на таблицы фактов и таблицы измерений (справочники)
Как правило:
одно направление деятельности = одна таблица фактов
Но не всегда. По опыту, таблиц фактов всегда меньше, чем таблиц измерений.
Как правильно структурировать таблицы внутри базы данных Excel
Здесь мы, конечно, не будем усложнять себе жизнь и углубляться в архитектуру баз данных. Рассмотрим самую простую структуру базы данных, после которой вам уже будет понятно, куда двигаться.
Это пример базы данных типа "Звездочка".
В центре базы - таблица фактов "Реестр продаж", в которой содержится только ключевая информация о каждой транзакции.
Таблица фактов содержит 3 ключевых столбца: Дата, ID товара и ID клиента. И к каждому ключевому столбцу "цепляется" таблица измерений, каждая из которых содержит более детальную информацию.
Из этого простейшего, но наглядного примера базы данных можно вывести основные правила, без которых ваша база данных останется хаотичным набором таблиц.
5 золотых правил построения БД
- Одна сущность - одна таблица. Не пытайтесь в одну вкладку запихнуть клиентов, их заказы, товары и оплаты. Разделите: таблица «Клиенты», таблица «Заказы», таблица «Товары». Каждая отвечает за свой тип данных.
- Один столбец - один признак и тип данных. В каждом столбца должна быть одна единица информации. Например, в столбце «Телефон» должны быть только телефоны. Не пишите туда +7 (999) 123-45-67 (с 9 до 18). Контактное время вынесите в отдельный столбец. Лучше больше столбцов сделайте.
- Уникальный ID обязателен для таблиц-справочников. В таблицах-спавочниках не должно быть задвоений по ключевым полям. Например, ФИО сотрудников может повторяться - но если это не ключ (а это не должно быть ключом), то не страшно. Но вот табельный номер (ключ) повторяться не должен.
- Никаких объединённых ячеек и «дыр» внутри данных. Объединённые ячейки ломают сортировку, фильтрацию и формулы. Пустые строки/столбцы прерывают диапазон, который «видит» Excel. Данные должны идти сплошной плоской таблицей. Если какие-то ячейки действительно нужно оставить не заполненными - придумайте для них "заглушку" соответствующую смыслу данных в столбце.
- Связываем, а не дублируем. Не копируйте адрес клиента в каждую строчку заказа. Храните адрес в таблице «Клиенты», а в «Заказах» оставляйте только ID_клиента. Связь выстроите через сводную или Power Query.
Проблемы при построении БД в Excel, с которыми вы обязательно столкнетесь
И как их решить.
>> Лимит чуть более 1 млн. строк на одном листе и тормоза при больших объемах
Если ваша компания ведет активную деятельность (особенно это касается продаж, интернет-аналитики и т.п.), то ваша база очень быстро переполнится. Несмотря на лимит в 1 млн. строк, уже после 100 тыс. файл будет нещадно тормозить и долго открываться, поскольку его вес будет приличным.
Как решить:
Архивируйте данные за определенные отрезки времени (например, за год, но вообще вам нужно самим определить этот период, когда ваша база переполняется).
Используйте Power Query для подключения к архивам без копирования в рабочую книгу.
>> Совместная работа и риск ошибок ввода данных
Базу данных могут поддерживать несколько сотрудников. Это влечет за собой пару "неудобств":
> при входе одного из них файл Excel блокируется - остальные смогут открыть его только на чтение.
> Excel - это не полноценная база данных с четкой ролевой моделью, где сотрудник без нужных прав ничего не поломает. Здесь вероятность того, что новичок что-то порушит или просто некорректно внесет данные, высока.
Что делать:
Блокировку файла одним пользователем можно решить:
- функционалом совместного доступа (не всегда работает корректно, кстати)
- переносом базы в гугл или яндекс таблицы
- ведением таблиц базы в разных файлах "по ответственным" и последующим объединением их через Power Query и Power Pivot
Ну а чтобы вашу базу не поломали, можно настроить систему защиты и проверок.
Как настроить такую систему защиты и проверок для вашей базы данных в Excel (да и в принципе для любого реестра или таблицы) - поделилась в своих каналах в ТГ и MAX.
>> Нет настоящих связей между таблицами
Возможно, вы тот самый ценный сотрудник, который ориентируется, где лежит таблица с данными клиентов, и что именно этот справочник связан с таблицей фактов по полю ID клиента. Но просто по умолчанию эти таблицы между собой никак не "подружились"...
Решается это просто:
Таблицы можно "подружить" через модель данных в Power Query.
Заодно это решит проблему дубликатов в базе данных - ведь в Power Query допускается только связь "один-ко-многим", т.е. модель не допустит, чтобы в таблицах измерений были задвоения по ключевым полям.
Что в итоге - не передумали строить БД в Excel?
База данных в Excel - это не просто вести разные списки и реестры. Это про структуру, дисциплину, использование встроенных инструментов (таблицы, проверка данных, модель данных) и понимание, когда Excel уже пора масштабировать.
Если вы соблюдаете описанные в статье правила, ваша таблица превратится из «списка с данными» в рабочий инструмент, который экономит часы на отчётах и спасает от ручных ошибок.
P.S Завела канал в MAX Ленивый аналитик, там будут выходить не только анонсы статей, но и дополнительные материалы, все так же, как в канале в ТГ.