Всем привет! 👋
Сегодня, на примере создания калькуляции стоимости продукции, мы научим:
- выполнять расчеты без единой формулы на листе;
- выполнять поиск значения и создавать выпадающий список средствами Visual Basic Application (VBA);
- сохранять данные в формате PDF.
Подготовь чашечку чая (кофе) ☕️, будет долго, но интересно! 🤩
А в благодарность автору не забудь оставить комментарий и поставить лайк 😊.
📢 Файл с примером размещен в конце статьи 🔽
▶️ Исходные данные
Пусть на отдельном листе имеется прайс определенной продукции:
Для удобства дальнейшего использования зададим именованные диапазоны:
- Продукт:
- Прайс:
▶️ Цели и задачи
Для заполнения калькуляции стоимости продукции на отдельном листе подготовим следующий шаблон:
С учетом выбранного наименования в результате мы должны получить заполненную таблицу с расчетом полной стоимости заказа:
Чтобы вам было проще адаптировать данное решение под свои нужды рассмотрим его пошагово.
▶️ Создаем выпадающий список
Выпадающий список позволяет выбирать одно из значений из диапазона, облегчая и ускоряя ввод данных и предотвращая ошибки.
Для целей данного примера первым шагом является создание выпадающего списка продукции в столбце А.
И здесь есть два решения:
🔘 Создание выпадающего списка средствами Excel:
- выберите ячейку (например А4);
- укажите для нее Тип данных - Список;
- задайте нужный источник данных:
В дальнейшем вы можете протягивать ячейку на требуемый диапазон.
🔘 Выпадающий список средствами VBA:
Данный макрос значительно улучшает работу с данными в Excel, обеспечивая точность и удобство при выборе продуктов из списка.
Когда пользователь выбирает ячейку в столбце А, макрос автоматически создает выпадающий список на основе данных из именованного диапазона "Продукт" (Лист2).
📝 Примечание автора: На наш взгляд данное решение является более изящным, обеспечивая точность и удобство при выборе продуктов из списка, поэтому рекомендуем использовать его.
Для достижения той же цели, вы можете использовать вместо Worksheet_SelectionChange событие Worksheet_BeforeRightClick:
В этом случае выпадающий список будет появлялся только при клике правой кнопкой мыши, а не при изменении выделения.
Обращаем внимание! Макрос размещается в модуле листа.
▶️ Выполняем поиск стоимости товара. Функция ВПР на VBA
О том, как работает функция ВПР мы ни раз обсуждали в наших обзорах:
Но задача данного примера заключается в том, чтобы исключить наличие каких бы то ни было формул на листе.
А потому, для поиска соответствующего значения (как альтернативу функции ВПР) применим следующий макрос:
Благодаря процедуре Worksheet_Change макрос будет выполняться при изменении данных в столбце A.
- Если значение в столбце A не пустое, происходит поиск соответствующего значения в диапазоне (resultRange) с помощью функции VLookup. Затем найденное значение записывается в столбец C для той же строки:
- Если значение в столбце A пустое, то происходит очистка содержимого в диапазоне от столбца B до D для соответствующей строки:
📝 Примечание автора: В целях обработки ошибки при использовании функции VLookup использован блок On Error Resume Next. Это позволяет игнорировать ошибку, которая может возникать при поиске значения в списке, а в дальнейшем позволит выполнить пересчет общей стоимости без удаления строки итогов.
▶️ Определяем стоимость выбранного товара
Так как по условию задачи требуется не только выполнить поиск соответствующего значения, но и рассчитать стоимость позиции товара создадим модуль и добавим следующий макрос:
- Чтобы расчет происходил автоматически при изменении на листе добавим данный макрос к ранее созданному событию Worksheet_Change:
- Теперь когда в столбце B (диапазон строк с B4 до последней заполненной строки) происходят изменения, макрос автоматически умножает соответствующее значение в столбце B на значение в столбце C и записывает результат в столбец D:
- Если значение в столбце B становится пустым, соответствующая ячейка в столбце D очищается, что позволяет автоматически обновлять данные в соответствии с изменениями в столбцах B и C:
▶️ Подводим итоги
Заключительным этапом данной задачи является подведение итогов (общей стоимости калькуляции). Для этого добавим на лист:
- элемент управления кнопку:
- или любой рисунок:
На который зададим следующий макрос:
➡️ Макрос выполняет следующие действия:
- находит последнюю заполненную ячейку в столбце "C" и вставляет в следующую за ней ячейку запись "ВСЕГО";
- объединяет ячейки "B" и "C" с записью "ВСЕГО" в одну ячейку;
- выравнивает содержимое "ВСЕГО" по правому краю;
- находит последнюю заполненную ячейку в столбце "D";
- определяет диапазон для суммирования (от D4 до последней заполненной ячейки плюс одну новую ячейку);
- считает сумму и записывает результат в последнюю заполненную ячейку столбца "D".
- добавляет обводку для ячеек от А4 до последней заполненной ячейки, чтобы подчеркнуть диапазон данных.
При внесении изменений следует повторно нажать кнопку "ВСЕГО":
▶️ Дополнения
Чтобы сделать форму еще более универсальной добавим следующие дополнения:
🔘 корректировка макроса для выпадающего списка
Как указывалось ранее код обработчика события Worksheet_SelectionChange формирует выпадающий список на листе в столбце А.
Но после подсчета общей суммы вы могли наблюдать следующее:
И на наш взгляд это безусловно может путать и даже раздражать, а потому внесем небольшую корректировку:
Теперь, если ячейка содержит "ВСЕГО", выпадающий список для неё будет снят:
Иначе, выпадающий список будет установлен, как и ранее:
🔘 опция начать новый расчет
Данный макрос позволит очистить область расчетов
В результате (начиная с последней заполненной строки) в столбце A будут удалены строки до строки 4 включительно:
🔔 Важно! удаление строк необратимо.
🔘 опция убрать итоги (будет полезна если потребуется внести изменения)
Чтобы удалить последнюю строку содержащую итоги можно использовать следующий код:
Благодаря данному дополнению можно внести дополнительные позиции в уже с формированный расчет:
🔘 опция сохранить pdf
Для создания макроса, который запросит у пользователя выбрать диапазон и затем сохранит его в формате PDF, можем использовать следующий код:
Данный макрос запросит пользователя выбрать диапазон:
и место для сохранения PDF файла:
После этого выбранный диапазон будет сохранен в формате PDF:
В заключение...
Как вы могли заметить на листе нет ни одной формулы, а следовательно не нужно переживать, что пользователь испортит расчет.
Да, данный пример является частным решением, и возможно довольно прост 😊. Но полагаем, что на его основе вы сможете сделать программу для расчета отвечающую вашим потребностям и задачам.
📍 Рекомендуемые статьи 🔽
