Содержание:
При упоминании баз данных (БД) первое, что приходит на ум, конечно же, всякие модные словечки типа SQL, Oracle, 1С или хотя бы Access. Конечно, это очень мощные (и по большей части дорогие) программы, способные автоматизировать работу большой и сложной компании с большим количеством данных. Беда в том, что иногда такая мощность просто не нужна. Ваш бизнес может быть небольшим и с относительно простыми бизнес-процессами, но вы также хотите его автоматизировать. И именно для небольших компаний это зачастую вопрос выживания.
Для начала сформулируем ТЗ. В большинстве случаев база данных для учета, например классических продаж, должна уметь:
- держать в таблицах информацию о товарах (ценах), совершенных сделках и покупателях и связать эти таблицы между собой
- иметь удобный формы ввода данные (с раскрывающимися списками и т. д.)
- автоматически заполнить некоторые данные печатные формы (платежи, счета и т. д.)
- выдать необходимые отчеты контролировать весь бизнес-процесс с точки зрения менеджера
Microsoft Excel может справиться со всем этим, приложив небольшие усилия. Давайте попробуем это реализовать.
Шаг 1. Исходные данные в виде таблиц.
Информацию о товарах, продажах и клиентах мы будем хранить в трёх таблицах (на одном листе или на разных – не важно). Принципиально важно превратить их в «умные таблицы» с авторазмером, чтобы не думать об этом в будущем. Это делается с помощью команды Форматировать как таблицу таб Главная (Главная страница — Форматировать как таблицу). На появившейся вкладке Конструктор (Дизайн) давать таблицам описательные имена в поле Название таблицы для дальнейшего использования:
Итого у нас должно получиться три «умные таблицы»:
Обратите внимание, что таблицы могут содержать дополнительные уточняющие данные. Так, например, наш Ценасодержит дополнительную информацию о категории (группе товаров, упаковке, весе и т.п.) каждого товара, а также таблицу "Клиент" — город и регион (адрес, ИНН, банковские реквизиты и т.д.) каждого из них.
Настольные Продажа будет использоваться нами позже для ввода в него завершенных транзакций.
Шаг 2. Создайте форму ввода данных
Конечно, вы можете ввести данные о продажах прямо в зеленую таблицу. Продажа, но это не всегда удобно и влечет за собой появление ошибок и опечаток из-за «человеческого фактора». Поэтому лучше было бы сделать специальную форму для внесения данных на отдельном листе примерно такого вида:
В ячейке B3, чтобы получить обновленную текущую дату и время, используйте функцию ТДАТА (ТЕПЕРЬ). Если время не нужно, то вместо этого ТДАТА функция может быть применена СЕГОДНЯ (CЕГОДНЯ).
В ячейке B11 найдите цену выбранного товара в третьем столбце смарт-таблицы. Цена используя функцию ВПР (ВПР). Если вы еще не сталкивались с этим, то сначала прочитайте и посмотрите видео здесь.
В ячейке B7 нам нужен выпадающий список с товарами из прайс-листа. Для этого вы можете использовать команду Данные – Проверка данных (Валидация данных), укажите в качестве ограничения Список (Список) а затем введите в поле Источник (Источник) ссылка на столбец Фамилия с нашего умного стола Цена:
Аналогично создается выпадающий список с клиентами, но источник будет уже:
=ДВССЫЛ («Клиенты[Клиент]»)
Функция КОСВЕННЫЕ (КОСВЕННО) в данном случае необходим, поскольку Excel, к сожалению, не понимает прямых ссылок на смарт-таблицы в поле «Источник». Но та же ссылка, «завернутая» в функцию КОСВЕННЫЕ при этом работает на ура (подробнее об этом было в статье про создание выпадающих списков с содержимым).
Шаг 3. Добавление макроса записи продаж
После заполнения формы необходимо добавить введенные в нее данные в конец таблицы. Продажа. С помощью простых ссылок сформируем строку для добавления прямо под формой:
Те. ячейка A20 будет иметь ссылку на =B3, ячейка B20 будет иметь ссылку на =B7 и так далее.
Теперь давайте добавим двухстрочный элементарный макрос, который копирует сгенерированную строку и добавляет ее в таблицу «Продажи». Для этого нажмите комбинацию Alt + F11 или кнопка Визуальный Бейсик таб разработчик (Разработчик). Если этой вкладки не видно, то сначала включите ее в настройках Файл – Параметры – Настройка ленты. (Файл — Параметры — Настроить ленту). В открывшемся окне редактора Visual Basic вставьте новый пустой модуль через меню Вставка – Модуль и вводим туда наш макрокод:
Sub Add_Sell() Worksheets("Входная форма").Range("A20:E20").Copy 'Скопируйте строку данных из формы n = Worksheets("Sales").Range("A100000").End(xlUp) . Row 'определяем номер последней строки в таблице. Sales Worksheets("Продажи").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues 'вставляет в следующую пустую строку Worksheets("Входная форма").Range("B5,B7,B9"). ClearContents 'очистить завершающую подформу Теперь мы можем добавить в нашу форму кнопку для запуска созданного макроса, используя выпадающий список. Вставить таб разработчик (Разработчик — Вставка — Кнопка):
После того, как вы его нарисуете, зажав левую кнопку мыши, Excel спросит, какой макрос ему нужно назначить – выбираем наш макрос Добавить_Продать. Вы можете изменить текст на кнопке, щелкнув ее правой кнопкой мыши и выбрав команду Изменить текст.
Теперь после заполнения формы вы можете просто нажать на нашу кнопку, и введенные данные автоматически добавятся в таблицу. Продажа, а затем форма очищается для заключения новой сделки.
Шаг 4. Связывание таблиц
Прежде чем создавать отчет, давайте свяжем наши таблицы вместе, чтобы позже можно было быстро рассчитать продажи по региону, клиенту или категории. В старых версиях Excel для этого потребовалось бы использование нескольких функций. ВПР (ВПР) для подстановки в таблицу цен, категорий, клиентов, городов и т.д. Продажа. Это требует от нас времени и усилий, а также «съедает» много ресурсов Excel. Начиная с Excel 2013 все можно реализовать гораздо проще, настроив связи между таблицами.
Для этого на вкладке Данные (Дата) нажмите на Отношения (Связи). В появившемся окне нажмите кнопку Создавай (Новый) и выберите из раскрывающихся списков имена таблиц и столбцов, которыми они должны быть связаны:
Важный момент: таблицы должны быть указаны именно в таком порядке, т.е. связанная таблица (Цена) не должно содержаться в ключевом столбце (Фамилия) дублирующиеся товары, как это происходит в таблице Продажа. Другими словами, связанная таблица должна быть такой, в которой вы будете искать данные, используя ВПРесли бы он использовался.
Разумеется, таблица подключается аналогичным образом. Продажа со столом "Клиент" по общему столбцу Клиент:
После настройки ссылок окно управления ссылками можно закрыть; вам не нужно повторять эту процедуру.
Шаг 5. Строим отчеты по сводке
Теперь, чтобы анализировать продажи и отслеживать динамику процесса, создадим, например, какой-нибудь отчет с использованием сводной таблицы. Установить активную ячейку в таблицу Продажа и выберите вкладку на ленте Вставка — сводная таблица (Вставка — сводная таблица). В открывшемся окне Excel спросит нас об источнике данных (т.е. таблице Продажа) и место для загрузки отчета (желательно на новом листе):
Важным моментом является то, что необходимо включить флажок Добавьте эти данные в модель данных (Добавить данные в модель данных) внизу окна, чтобы Excel понял, что мы хотим построить отчет не только по текущей таблице, но и использовать все связи.
После нажатия на OK в правой половине окна появится панель Поля сводной таблицыгде нажать на ссылку Всечтобы увидеть не только текущую, но и все «умные таблицы», которые есть в книге сразу. А дальше, как и в классической сводной таблице, можно просто перетащить в область нужные нам поля из любых связанных таблиц. ФИЛЬТР, Ряды, Столбцов or Наши ценности — и Excel моментально построит на листе любой нужный нам отчет:
Не забывайте, что сводную таблицу необходимо периодически обновлять (при изменении исходных данных), щелкнув по ней правой кнопкой мыши и выбрав команду Обновить и сохранить (Обновить), потому что он не может сделать это автоматически.
Также, выделив любую ячейку в сводке и нажав кнопку Сводная диаграмма (Сводная диаграмма) таб Анализ (Анализ) or параметры (Параметры) вы можете быстро визуализировать рассчитанные в нем результаты.
Шаг 6. Заполните распечатки
Еще одна типичная задача любой базы данных – автоматическое заполнение различных печатных форм и бланков (счетов-фактур, счетов-фактур, актов и т.п.). Об одном из способов это сделать я уже писал. Здесь реализуем, например, заполнение формы по номеру счета:
Предполагается, что в ячейку С2 пользователь введет число (номер строки в таблице Продажа, собственно), а затем с помощью уже знакомой функции подтягиваются нужные нам данные ВПР (ВПР) и функции ИНДЕКС (ПОКАЗАТЕЛЬ).
- Как использовать функцию ВПР для поиска значений
- Как заменить ВПР функциями ИНДЕКС и ПОИСКПОЗ
- Автоматическое заполнение бланков и форм данными из таблицы
- Создание отчетов с помощью сводных таблиц