Создание базы данных в Excel
При упоминании баз данных (БД) первым делом, конечно, в голову приходят всякие умные слова типа 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).
Ссылки по теме
- Как использовать функцию ВПР (VLOOKUP) для поиска и подстановки значений
- Как заменить ВПР функциями ИНДЕКС и ПОИСКПОЗ
- Автоматическое заполнение форм и бланков данными из таблицы
- Создание отчетов с помощью сводных таблиц
Отличная статья! Первым, что пришло в голову: это доработать "Счёт" чтобы там изначально выходил номер последней продажи для печати. Реализовал). Всё работает. Ещё раз, спасибо!
обнаружил, что не могу добавить вычисляемые поля в сводную таблицу, при создании которой установлена галка"Добавить эти данные в модель данных", пункт "Вычисляемое поле" в конструкторе сводной просто недоступно.
либо я криво делаю что-нибудь, либо это ограничение, заложенное программистами
жаль, так как функционал сводных таблиц, дающий возможность вычислений много дает...подскажите плиз, действительно невозможно вычислять в сводной таблице при таком способе создания?
спасибо
PS: У себя в работе пользуюсь похожей схемой с использованием POWER PIVOT
Подскажите, пожалуйста, столкнулась с проблемой на шаге 3: при переносе данных из стоки в таблицу, макрос копирует строку, но она не становится частью "умной таблицы", а встает при копировании под нее. Можно ли встраивать копируемую строку сразу в таблицу?
Вопрос следующий, а если клиент берет несколько товаров? Как будет выглядеть счет и с/ф?
Подскажите, если у меня в листе "Продажи" внесено 50 товаров это 50 строк, в следующий раз внесено 5 товаров, потом 100 товаров, мне все время придется менять копируемый диапазон в макросе? можно ли сделать так что бы вносились только указанное количество товаров, как будет выглядеть макрос?
Вопрос немного не по профилю, но все же: а можно ли форму сделать как отдельный объект? Чтобы она была похожа на форму в Аксессе?
P.s.: Огромнейшее спасибо вам за ваш сайт ! Мне, человеку с филологическим образованием (очччень далекому от программирования, баз данных и прочего), ваш сайт помог создать почти автоматизированную базу данных в Excel!
Выполнил все согласно указаниям в уроке. Запускаю макрос (нажав на кнопку), на что получаю ошибку: (Run-time error "9": Subscript out of range)
В макросе указывает на 2 строку с диапазоном ячеек для копирования.
Быть подумал может, что то конкретно с данной строкой - заменил строку, результат тот же самый.
Прошу помочь советом.
!. Вопрос снят. Проблема в простой опечатке )
Николай, подскажите, пожалуйста, как связать созданные таблицы на шаге 4 в Microsoft Excel 2007?
Разве данный макрос "работает" с умными таблицами, а не с листами книги? Worksheets("Продажи") это обращение к листу, а не к умной таблице. Макрос работает корректно только если умная таблица "Продажи" начинается в ячейке А1 листа "Продажи" и при отключенной в ней строке итогов. Если умная таблица начинается с ячейки А1, но в ней включена строка итогов, то данные с листа "Форма ввода" записываются ниже ее. Если умная таблица начинается не в первом столбце, то данные с листа "Форма ввода" копируются "мимо" таблицы начиная с первого столбца листа "Продажи". Если таблица начинается не с первой строки, то данные копируются во вторую строку листа "Продажи". Умной таблицы "Продажи" и вовсе может не быть, достаточно чтоб был лист с таким названием.
Подскажите, пожалуйста, как заставить макрос отключать/включать итоги и работать именно с умной таблицей, а не с листом книги.
Спасибо.
Если таблицы начинаются не с А1 - ничего страшного, а вот строка итогов, конечно же, помешает - ее надо выключать.
Чтобы макрос работал с умными таблицами, нужно использовать совершенно другой подход (коллекцию ListObjects из VBA).
Спасибо за ваши статьи, очень полезные.
Подскажите п-та или поделитесь ссылкой для новичка, где посмотреть (коллекцию ListObjects из VBA) чтобы настроить через умные таблицы.
Если ставить цифру 2, то теперь данные заносятся как надо, но постоянно в одну и ту же строчку. Водишь данные, и они постоянно сохраняются в одну и ту же строчку, стирая предыдущие символы
Worksheets("ИП") . Cells(n + 1, 2).PasteSpecial Paste:=xlPasteValues
Надеюсь я изьясняюсь понятно =)
Есть база данных , в которую ежедневно вносятся данные по приходу и расходу (т.е. складской учет) Не ас в программе, но хочется больше автоматизировать этот файл. Есть и возможность выводить какой то отчет, где будет видно остатки на начало и конец дня в разрезе каждой номенклатуры (их всего до 10 позиций) Пробовала в сводные таблицы собирать, но они больше суммируют показатели, а вот чтобы в них формулы задавать остаток на н.дня+приход и расход=остаток на к.дня не вижу этой картинки, как это возможно
Помогите, пожалуйста - умные головушки!
Скажите, пожалуйста, можно ли с помощью описанного Вами способа построить базу данных договоров компании с автоматическим присвоением номера (кода) договора при появлении: 1. Нового контрагента, 2. Нового договора и/или Дополнительного соглашения к договору. Структура номера (кода) договора: Номер контрагента - разделитель - порядковый номер договора с данным контрагентом в текущем году - разделитель - год (например, 1-2-2018)? У меня возникла проблема с автоматической подстановкой порядкового номера договора с данным контрагентом в текущем году. Заранее благодарен за помощь.
Завел умную таблицу для внесения новых данных на отдельном листе и записал макрос как указано в статье.
Но выдает ошибку "метод pastespecial из класса range завершен неверно" и подчеркивает соответствующую строчку. Как быть?
спасибо!
Присоединяюсь ко всем благодарностям в Ваш адрес за эту статью и прошу помочь в решении вот какого вопроса
Я использовал Ваш макрос в своей отчетности, но хочу немного изменить. макрос вставляет в следующую незаполненную строку данные из формы: допустим строка 5 ячейки A5; B5; C5; D5; E5; F5, а вот данные из ячейки Н5 я бы хотел чтобы переносились в определенную ячейку формы в моем случае в ячейку С2. постараюсь пояснить: форма это у меня наряд, заполняется наряд, сводятся данные в таблицу и номер наряда (по порядку, который у меня в таблице в столбце Н5 уже забит заранее) вставлялся в форму, распечатав форму имелось бы уже нумерация. естественно операцию очистка формы я исключил, вставил ее в кнопку распечатать, Сам как и большинство здесь я новичок, но благодаря Вашему сайту и Вашим статьям думаю могу претендовать на звание продвинутого чайника.
Спасибо большое. На основе вашей БД сделал себе, но заполняет сразу две таблицы в разных листах. В одной общее, во второй - только те значения, которые нужны для данной таблицы.
Но после нажатия кнопки, переклчаеся на последнюю страницу куда были скопированы значения.
Как сделать так, чтобы оставаться на исходной странице с формой?
Sub РудСклад() Worksheets("Форма").Range("C21:H21").Copy '???????? ??????? ? ??????? ?? ?????0 n = Worksheets("РудСкл").Range("A100000").End(xlUp).Row '?????????? ????? ????????? ?????? ? ????. ??????? Worksheets("РудСкл").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues '????????? ? ????????? ?????? ?????? Worksheets("Форма").Range("C21:H21").Copy n = Worksheets("Реестр").Range("A100000").End(xlUp).Row '?????????? ????? ????????? ?????? ? ????. ??????? Worksheets("Реестр").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues '????????? ? ????????? ?????? ?????? Worksheets("Форма").Range("D3,D4,D5,D6,D7,D8").ClearContents '??????? ????? End SubПодскажите, пожалуйста, что надо добавить к Макросу, чтобы он не переносил данные в конец таблицы, если среди переносимых значений присутствует слово "Нет", т.е. чтобы он эти значения пропускал, а переносил следующие. Не знаю, понятно ли изъясняюсь. Могу вложить пример если понадобиться.
Можно ли с помощью выпадающих списков скопировать из справочника строку из двух-трех значений (два-три столбца) в другую таблицу ? Для одного столбца все работает, а вот из двух столбцов в ячейку копируется только одно значение. Все примеры в книгах по копированию одного столбца, а у меня связанные данные, например марка бензина и его цена. Или тут без макроса никак ?
Подскажите плс я вот ставил ваш макрос, но у меня почему то Excel теперь закрывается. Через F8 я понял что это происходит на 3 шаге, не могу понять почему. Да кстати, при попытке сохранить, файл также закрывается. Я создал тему на форуме, но ответа пока что не получил
Буду очень признателен за вашу помощь
Заранее благодарю!
ub WWW() Worksheets("Формат_ввода").Range("A24:j24").Copy 'копируем строчку с данными n = Worksheets("Операции").Range("A100000").End(xlUp).Row 'определяем номер последней строки в табл. Продажи Worksheets("Операции").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues 'вставляем в следующую пустую строку Worksheets("Формат_ввода").Range("B5,B7,B9,B11,B13,B15,B17").ClearContents 'очищаем форму End Subпоэтому застряла на четвертом шаге. у меня Exel 2010. что не на так не пойму.
А как реализовать ввод нескольких товаров под один заказ?
Пример:
Сотрудник сделал работу "А" для заказчика "Х" в день "Д" и использовал материал:
1. ........
2. ........
и так далее.
В один день сотрудник может сделать несколько заданий для разных заказчиков.
На ум приходит добавить еще одну таблицу "материал" и связать их по "заданию"
Как на меня удобней было бы на странице ввода добавлять целый материал и потом внести в базу.
Спасибо.
А как быть если необходима база данных которая бы выводила все данные по объекту?
Например технические характеристики машины.
Допустим, в ячейку вносишь Марку машины, а из базы подтягивается все информация по ней.
Спасибо.
Спасибо огромное за статью!
по вашему примеру создал БД для регистрации выдачи оборудования сотрудникам. моя база получилась несколько сложнее, чем тут описана - в разы больше макросов, сами макросы намного сложнее, с условиями и переменными.., посложнее отчет...и многое другое, тем не менее, все это было вполне работоспособно!)
НО! для тех, кто так же, как и я, вдохновился данной статьей и собрался сделать что-то подобное для своих нужд, стоит сказать, что Exel очень чувствителен к кривым рукам пользователей (а их в моей базе предполагалось аж 8 человек, не все одновременно, конечно, но все же..), так что сбитые формулы со всеми вытекающими - обычное дело), даже защита листа не помогала))
В итоге пришлось делать аналог в ACCESS, но полученный опыт при написании базы данных в exel воистину бесценен!))
p/s/ Закрепление только шапки недостаточно для создания нормальной формы с динамичными таблицами со своими шапками и таблицами.. - в одной форме.
Очень благодарен за ответ.
Sub Add_Sell()
n = Worksheets("Продажи").Range("A100000").End(xlUp).Row 'определяем номер последней строки в табл. Продажи
If n < 10 Then ' формируем номер чека вида «ч001»
a = "ч00 " & n
ElseIf n >= 10 And n < 100 Then
a = "ч0" & n
Else
a = "ч" & n
End If
Worksheets("Форма ввода").Range("A20").Value = a ' записываем в А20 номер чека ч001
Worksheets("Форма ввода").Range("A20:E20").Copy 'копируем строчку с данными из формы
Worksheets("Продажи").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues 'вставляем в следующую пустую строку
Worksheets("Форма ввода").Range("B5,B7,B9").ClearContents 'очищаем форму
End Sub
как-то так
И привет вам из солнечного Баку!