Создание базы данных в 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).

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



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).
03.07.2018 13:48:12
Доброго времени суток.

Спасибо за ваши статьи, очень полезные.
Подскажите п-та или поделитесь ссылкой для новичка, где посмотреть (коллекцию ListObjects из VBA) чтобы настроить через умные таблицы.
05.07.2018 09:17:05
Проще всего зайти на msdn.microsoft.com и там поискать по ключевым словам в документации по VBA.
27.07.2021 16:25:36
Николай, а как же всё таки нужно изменить код, чтобы всё работало, если таблица начинается с А2?
Если ставить цифру 2, то теперь данные заносятся как надо, но постоянно в одну и ту же строчку. Водишь данные, и они постоянно сохраняются в одну и ту же строчку, стирая предыдущие символы
Worksheets("ИП") . Cells(n + 1, 2).PasteSpecial Paste:=xlPasteValues  
23.08.2017 05:45:22
Всем доброго дня. Прошу вас подсказать насчет формы для ввода данных. Бывают такие моменты, что одна и та же запись в базу данных может вводиться несколько раз (разумеется по ошибке). Возможно ли как то сразу показать пользователю, который вносит в форму данные, что такая запись уже существует и ее не нужно вносить в базу данных?
31.08.2017 11:54:17
Может использовать автоматическую подсветку повторяющихся значений цветом?
04.09.2018 15:57:57
Элементарным "Условным Форматированием" на повтор значений....
04.10.2017 23:04:40
Николай, ваш труд бесценен. Спасибо. Скажите, а как можно реализовать возможность корректировки записей в таблицах - справочниках, используя такую же простую форму ввода?
24.10.2017 08:08:08
Николай, потрясающая полезная информация на этом сайте. Спасибо, вам за ваш труд! Сделал эту базу данных и очень помогла мне в работе контроль и учет склада...только вот хочется, что бы этот файл как-то синхронизировался)простите за наглость) Дело в том, что этот файл просматривают 2 человека...И локальной сети между ними нет...только интернет. Можно это как нибудь решить? Или еще лучше перенести это в google таблицы?
04.09.2018 15:59:43
Выложите файл в облаке на почте.... и при одновременной работе в нем, все прекрасно меняется онлайн....
13.12.2017 10:17:11
Николай, здравствуйте! Вопрос такой. Поскольку материалы, которые добавляю с помощью ввода данных имеют составляющие компоненты, добавлен перечень всех составляющих, но возможны разные варианты по составляющим и часть из них остаются с нулевым количеством. Как сделать, чтобы они не добавлялись в таблицу?
28.12.2017 15:56:54
Добрый день, Николай! Воспользовалась вашей идеей для создания базы данных, заполняемую из формы. Возник вопрос: возможно ли используя две формы (по тому типу как вы показали в статье) заполнить таблицу по частям - первая форма (точно в соответствии с макросом) заполняет строки начиная со столбца А, а вторая - должна продолжить заполнение строки, начиная со столбца D? это возможно?.
Надеюсь я изьясняюсь понятно =)
14.02.2018 09:18:58
Добрый день! Очень нужна помощь ваша!
Есть база данных , в которую ежедневно вносятся данные по приходу и расходу (т.е. складской учет) Не ас в программе, но хочется больше автоматизировать этот файл. Есть и возможность выводить какой то отчет, где будет видно остатки на начало и конец дня в разрезе каждой номенклатуры (их всего до 10 позиций)   Пробовала в сводные таблицы собирать, но они больше суммируют показатели, а вот чтобы в них формулы задавать остаток на н.дня+приход и расход=остаток на к.дня  не вижу этой картинки, как это возможно
Помогите, пожалуйста - умные головушки!
06.07.2018 16:34:07
Добрый день, Николай!
Скажите, пожалуйста, можно ли с помощью описанного Вами способа построить базу данных договоров компании с автоматическим присвоением номера (кода) договора при появлении: 1. Нового контрагента, 2. Нового договора и/или Дополнительного соглашения к договору. Структура номера (кода) договора: Номер контрагента - разделитель - порядковый номер договора с данным контрагентом в текущем году - разделитель - год (например, 1-2-2018)? У меня возникла проблема с автоматической подстановкой порядкового номера договора с данным контрагентом в текущем году. Заранее благодарен за помощь.
05.08.2018 03:48:53
Николай, дай вам Бог здоровья! Вы делаете мир лучше! С теплым приветом из Баку
22.08.2018 15:07:25
Добрый день! Прошу помочь с макросом.
Завел умную таблицу для внесения новых данных на отдельном листе и записал макрос как указано в статье.
Но выдает ошибку "метод pastespecial из класса range завершен неверно" и подчеркивает соответствующую строчку. Как быть?
спасибо!
03.10.2018 14:50:21
Николай добрый день!
Присоединяюсь ко всем благодарностям в Ваш адрес за эту статью и прошу помочь в решении вот какого вопроса
Я использовал Ваш макрос в своей отчетности, но хочу немного изменить. макрос вставляет в следующую незаполненную строку данные из формы: допустим строка 5 ячейки A5; B5; C5; D5; E5; F5, а вот данные из ячейки Н5 я бы хотел чтобы переносились в определенную ячейку формы в моем случае в ячейку С2. постараюсь пояснить: форма это у меня наряд, заполняется наряд, сводятся данные в таблицу и номер наряда (по порядку, который у меня в таблице в столбце Н5 уже забит заранее) вставлялся в форму, распечатав форму имелось бы уже нумерация. естественно операцию очистка формы я исключил, вставил ее в кнопку распечатать, Сам как и большинство здесь я новичок, но благодаря Вашему сайту и Вашим статьям думаю могу претендовать на звание продвинутого чайника.  
02.11.2018 17:41:06
Просто хочу сказать Большое спасибо! Всегда обращаюсь за помощью к этому сайту и почти всегда нахожу ответы на свои вопросы. А еще и учусь таким крутым приемам благодаря вам!
19.11.2018 11:41:05
Добрый день!
Спасибо большое. На основе вашей БД сделал себе, но заполняет сразу две таблицы в разных листах. В одной общее, во второй - только те значения, которые нужны для данной таблицы.
Но после нажатия кнопки, переклчаеся на последнюю страницу куда были скопированы значения.
Как сделать так, чтобы оставаться на исходной странице с формой?

 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
17.12.2018 12:13:40
Всем доброго дня!
Подскажите, пожалуйста, что надо добавить к Макросу, чтобы он не переносил данные в конец таблицы, если среди переносимых значений присутствует слово "Нет", т.е. чтобы он эти значения пропускал, а переносил следующие. Не знаю, понятно ли изъясняюсь. Могу вложить пример если понадобиться.
18.02.2019 17:31:11
Добрый день !
Можно ли с помощью выпадающих списков скопировать из справочника строку из двух-трех значений (два-три столбца) в другую таблицу ?   Для одного столбца все работает, а вот из двух столбцов в ячейку копируется только одно значение.  Все примеры в книгах по копированию одного столбца, а у меня связанные данные, например марка бензина и его цена.      Или тут без макроса никак ?
16.04.2019 20:07:32
А в Excel 2007 не получится связать таблицы, как показано в примере, получится только в excel 2013 указанным способом?
А с помощью какой формулы реализовать, чтобы в ячейку C2 пользователь вводил число (номер строки в таблице Продажи, по сути)и потом уже к этому привязывались все остальные формулы? Спасибо.
04.10.2019 07:30:24
Николай, доброе утро! Благодарю Вас за ваш труд!!!
Подскажите плс я вот ставил ваш макрос, но у меня почему то 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 
Еще одним интересным фактом является то, что на разных компах он где то открывается, а где то нет. Я откл у себя антивирус и др проги, но это не помогло, может нужно настройки менять?
25.10.2019 12:00:22
День добрый! скачала пример и не могу найти кнопку на вкладке Данные - "Отношения" :?:
поэтому застряла на четвертом шаге. у меня Exel 2010. что не на так не пойму.
04.05.2020 22:20:34
Здравствуйте.
А как реализовать ввод нескольких товаров под один заказ?
Пример:
Сотрудник сделал работу "А" для заказчика "Х" в день "Д" и использовал материал:
1.    ........
2.    ........
и так далее.
В один день сотрудник может сделать несколько заданий для разных заказчиков.
На ум приходит добавить еще одну таблицу "материал" и связать их по "заданию"
Как на меня удобней было бы на странице ввода добавлять целый материал и потом внести в базу.
Спасибо.
24.05.2020 03:18:37
Добрый день.
А как быть если необходима база данных которая бы выводила все данные по объекту?
Например технические характеристики машины.
Допустим, в ячейку вносишь Марку машины, а из базы подтягивается все информация по ней.
Спасибо.
17.06.2020 16:34:31
ровно также, дочитайте статью до конца
Николай, добрый день!

Спасибо огромное за статью!
по вашему примеру создал БД для регистрации выдачи оборудования сотрудникам. моя база получилась несколько сложнее, чем тут описана - в разы больше макросов, сами макросы намного сложнее, с условиями и переменными.., посложнее отчет...и многое другое, тем не менее, все это было вполне работоспособно!)

НО! для тех, кто так же, как и я, вдохновился данной статьей и собрался сделать что-то подобное для своих нужд, стоит сказать, что Exel очень чувствителен к кривым рукам пользователей (а их в моей базе предполагалось аж 8 человек, не все одновременно, конечно, но все же..), так что сбитые формулы со всеми вытекающими - обычное дело), даже защита листа не помогала))

В итоге пришлось делать аналог в ACCESS, но полученный опыт при написании базы данных в exel воистину бесценен!))
31.07.2020 16:22:42
Access тоже непрост, большинство примеров для одно строчных и однозначных вариантов, но допустим строк минимум две и они динамичные.. 1. Дополните пример для двух строк, например, как в накладной или счете фактуре (из основной таблицы с выбором двух строк "Х" - или иначе)?.. и вопрос 2. Как Создать "Подвал" в документе печатной формы (или два подвала) (особенно под сводной таблицей - при расширении она затирает форму)?

p/s/ Закрепление только шапки недостаточно для создания нормальной формы с динамичными таблицами со своими шапками и таблицами.. - в одной форме.
15.08.2020 12:24:25
Добрый день!
Николай, присоединяюсь ко всем благодарностям в Ваш адрес! Т.к. я только начал осваивать Excel, для меня Ваш сайт просто находка!
У меня ситуация немного проще, но все равно что-то затрудняюсь...
Есть база товаров из 20-25 столбцов с характеристиками этих товаров (артикул, наименование, описание, код тнвэд, торговая марка, производитель и т.д.). Необходимо, чтоб по вводу артикула эта информация выгружалась на другой лист в этом файле в виде такой же таблицы. Только во 2-й таблице больше столбцов и искомые 20-25 нужно "раскидать" в определенной последовательности.
21.01.2021 14:54:11
Доброго дня! Отличный материал! Спасибо Есть вопрос: как на шаге номер три при добавлении формы продаж ввести номер чека по порядку - например ч001, ч002 и т.д. Т.Е смотрим последний номер и при вводе данных в таблицу добавляется следующий номер.
Очень благодарен за ответ.
Leo
27.04.2021 14:55:26
в переменной "n" мы получаем последнюю строку листа "Продажи".  Следовательно если номер чека по порядку у Вас хранится в столбце "А" и нумерация хранится со смещением на 1 от номера строки, например "ч001" в "А2", "ч002" в "А3", то нужно в ячейку "An+1" записать ("ч"+n). Для добавления нулей нужно сравнивать с 10, 100 и т.д.

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


как-то так
09.11.2021 12:34:37
Вы святой, Николай! Огромное вам спасибо за ваш бесценный труд!
И привет вам из солнечного Баку!
12.10.2022 15:04:20
Добрый день! Прекрасная статья, я пытаюсь перенести макрос на гугл таблицы, но встречает меня ошибка: "ReferenceError: Woeksheets is not defined". Подскажите, как мне ее решить? Сапсибо!
07.12.2022 14:52:03
Что-то этот пример какой-то неудачный у вас получился. Почему таблица "Прайс" содержит цену? Где таблица по приходу товара с датой поступления и ценой поставки? Таблица "Продажи" также должна иметь дату продажи, кол-во и цену продажи. Так же при заполнении таблицы "Продажи" стоит проверять имеется ли данный товар на приходе и в каком количестве. Ну и как итог вычислять нужно не только на какую сумму что продано, но и какая прибыль от проданного товара. В данной теме у вас раскрыта только третья часть базы данных.
Наверх