Создание базы данных в Excel

103412 08.09.2016 Скачать пример

При упоминании баз данных (БД) первым делом, конечно, в голову приходят всякие умные слова типа 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).

Ссылки по теме



10.09.2016 07:47:43
Николай, в очередной раз спасибо вам за ваш сайт!:)
Отличная статья! Первым, что пришло в голову: это доработать "Счёт" чтобы там изначально выходил номер последней продажи для печати. Реализовал). Всё работает. Ещё раз, спасибо!
11.09.2016 13:04:33
Николай, спасибо за интересную статью.
обнаружил, что не могу добавить вычисляемые поля в сводную таблицу, при создании которой установлена галка"Добавить эти данные в модель данных", пункт "Вычисляемое поле" в конструкторе сводной просто недоступно.
либо я криво делаю что-нибудь, либо это ограничение, заложенное программистами
жаль, так как функционал сводных таблиц, дающий возможность вычислений много дает...подскажите плиз, действительно невозможно вычислять в сводной таблице при таком способе создания?
спасибо
12.09.2016 06:35:10
Николай, спасибо! А можно сделать отдельную статью или видео урок про функцию "отношения", которая здесь задействована? Со возможными сценариями использования?
12.09.2016 09:53:27
Николай, спасибо.

PS: У себя в работе пользуюсь похожей схемой с использованием POWER PIVOT
12.09.2016 15:07:53
Спасибо за полезную статью!
14.09.2016 08:01:57
Доброе утро! Николай, Вы повышаете наш уровень знаний, больше спасибо. Если база данных по размеру будет 184 КБ или больше? Можно использовать данную модель?
23.09.2016 12:23:43
Здравствуйте! Спасибо Вам большое за Ваш труд.
Подскажите, пожалуйста, столкнулась с проблемой на шаге 3: при переносе данных из стоки в таблицу, макрос копирует строку, но она не становится частью "умной таблицы", а встает при копировании под нее. Можно ли встраивать копируемую строку сразу в таблицу?
30.09.2016 10:23:19
Здравствуйте! Спасибо за статью.
Вопрос следующий, а если клиент берет несколько товаров? Как будет выглядеть счет и с/ф?
04.10.2016 11:18:43
Добрый день Николай!
Подскажите, если у меня в листе "Продажи" внесено 50 товаров это 50 строк, в следующий раз внесено 5 товаров, потом 100 товаров, мне все время придется менять копируемый диапазон в макросе? можно ли сделать так что бы вносились только указанное количество товаров, как будет выглядеть макрос?  
07.10.2016 08:49:22
Большое спасибо за статью! :) Пусть и не с продажами, а со списками спортсменов, но всё равно работает.
07.10.2016 14:47:10
Добрый день, Николай!
Вопрос немного не по профилю, но все же: а можно ли форму сделать как отдельный объект? Чтобы она была похожа на форму в Аксессе?
13.10.2016 13:10:22
Здравствуйте, Николай! Очень нужна ваша помощь! Есть сверхмалый бизнес, база данных в котором составлена в Excel. Сейчас работаю над автоматизацией введения и сортировки данных и возникла такая проблема: Есть клиенты, которые вносят оплату за услуги. Оплата фиксируется в таблицу с 3мя столбцами - "Дата взноса", "ФИО" и "Сумма взноса". Эта таблица заполняется вручную в хронологическом порядке. Необходимо копировать диапазон "Дата взноса-Сумма взноса" в другой диапазон по фамилии. Т.е. есть общая таблица взносов, в которой взносы хранятся в хаотичном порядке. Есть таблица, в которой хранятся взносы конкретного клиента. Необходимо настроить автоматическое копирование из общей таблицы в таблицу взносов конкретного клиента (чтобы просчитывалась не сумма взносов, а история). Буду безмерно благодарен за оказанную помощь!
P.s.: Огромнейшее спасибо вам за ваш сайт http://www.planetaexcel.ru/! Мне, человеку с филологическим образованием (очччень далекому от программирования, баз данных и прочего), ваш сайт помог создать почти автоматизированную базу данных в Excel!
04.12.2016 21:33:44
Доброго времени суток, Николай!

Выполнил все согласно указаниям в уроке. Запускаю макрос (нажав на кнопку), на что получаю ошибку: (Run-time error "9": Subscript out of range)
В макросе указывает на 2 строку с диапазоном ячеек для копирования.
Быть подумал может, что то конкретно с данной строкой - заменил строку, результат тот же самый.
Прошу помочь советом.

!. Вопрос снят. Проблема в простой опечатке )
15.02.2017 22:42:36
Здравствуйте!
Николай, подскажите, пожалуйста, как связать созданные таблицы на шаге 4 в Microsoft Excel 2007?
09.04.2017 17:54:37
Николай, здравствуйте.
Разве данный макрос "работает" с умными таблицами, а не с листами книги? Worksheets("Продажи") это обращение к листу, а не к умной таблице. Макрос работает корректно только если умная таблица "Продажи" начинается в ячейке А1 листа "Продажи" и при отключенной в ней строке итогов. Если умная таблица начинается с ячейки А1, но в ней включена строка итогов, то данные с листа "Форма ввода" записываются ниже ее. Если умная таблица начинается не в первом столбце, то данные с листа "Форма ввода" копируются "мимо" таблицы начиная с первого столбца листа "Продажи". Если таблица начинается не с первой строки, то данные копируются во вторую строку листа "Продажи". Умной таблицы "Продажи" и вовсе может не быть, достаточно чтоб был лист с таким названием.
Подскажите, пожалуйста, как заставить макрос отключать/включать итоги и работать именно с умной таблицей, а не с листом книги.
Спасибо.
31.08.2017 11:57:56
Виктор, макрос задуман и написан для работы с листами, да (я вроде нигде обратного и не утверждал).
Если таблицы начинаются не с А1 - ничего страшного, а вот строка итогов, конечно же, помешает - ее надо выключать.
Чтобы макрос работал с умными таблицами, нужно использовать совершенно другой подход (коллекцию ListObjects из VBA).
23.08.2017 05:45:22
Всем доброго дня. Прошу вас подсказать насчет формы для ввода данных. Бывают такие моменты, что одна и та же запись в базу данных может вводиться несколько раз (разумеется по ошибке). Возможно ли как то сразу показать пользователю, который вносит в форму данные, что такая запись уже существует и ее не нужно вносить в базу данных?
31.08.2017 11:54:17
Может использовать автоматическую подсветку повторяющихся значений цветом?
04.10.2017 23:04:40
Николай, ваш труд бесценен. Спасибо. Скажите, а как можно реализовать возможность корректировки записей в таблицах - справочниках, используя такую же простую форму ввода?