Учет расхода холодной воды и других коммунальных ресурсов по показаниям счетчиков с помощью VBA Excel. Расчет и хранение данных в электронном виде.
Учет расхода воды
Учет расхода коммунальных ресурсов по показаниям счетчиков рассмотрим на примере холодной воды, так как это самый распространенный случай, когда может понадобиться расчет и хранение данных в рабочей книге Excel.
В июне 2020 года у нас появилась возможность оплачивать воду через Сбербанк-онлайн, поэтому отпала необходимость заполнять и печатать квитанции, о которых я писал раньше. Но возникло желание автоматизировать расчет суммы к оплате и сохранять данные по месяцам в электронном виде с помощью VBA.
Для записи показаний счетчика холодной воды, расхода ресурса и суммы к оплате с помощью VBA Excel я использую следующую таблицу:
Справа от таблицы учета холодной воды расположены командные кнопки и таблица тарифов:
Верхняя строка рабочего листа закреплена (Вид –> Закрепить области –> Закрепить верхнюю строку). Кнопки «Добавить» и «Печатная форма» добавлены из коллекции элементов управления ActiveX:
Таблица с показаниями счетчика, расходом воды и суммой к оплате преобразована в умную таблицу, у которой снят фильтр, отображавшийся в строке заголовков.
Кнопка «Добавить» рабочего листа открывает следующую пользовательскую форму:
Код кнопки «Добавить» на рабочем листе (расположен в модуле листа с таблицей):
|
1 2 3 |
Private Sub CommandButton1_Click() UserForm1.Show End Sub |
При загрузке формы выполняется следующая процедура (расположена в модуле формы):
|
1 2 3 4 5 |
Private Sub UserForm_Activate() TextBox1.Text = Format(Now, "MMMM") TextBox2.Text = Format(Now, "YYYY") TextBox3.SetFocus End Sub |
Текстовые поля:
- TextBox1 – поле для корректировки месяца;
- TextBox2 – поле для корректировки года;
- TextBox3 – поле для ввода текущего показания счетчика.
Нам остается только ввести текущие показания счетчика и нажать кнопку «Добавить» на пользовательской форме. Поля «Год» и «Месяц» добавлены на всякий случай, если понадобится их скорректировать.
Код кнопки «Добавить» пользовательской формы, который записывает новую* строку в таблицу (расположен в модуле формы):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Private Sub CommandButton1_Click() Dim pps As Long, tarif As String pps = Range("A1").End(xlDown).Row + 1 tarif = Range("K1").End(xlDown).Address Cells(pps, 1) = TextBox1.Text Cells(pps, 2) = TextBox2.Text Cells(pps, 3) = Cells(pps - 1, 4) Cells(pps, 4) = Val(TextBox3.Text) Cells(pps, 5) = Cells(pps, 4) - Cells(pps, 3) Cells(pps, 6) = Range(tarif) Cells(pps, 7) = Cells(pps, 5) * Cells(pps, 6) Unload Me ThisWorkbook.Save End Sub |
Переменные:
- pps – первая пустая строка в таблице с показаниями счетчика;
- tarif – адрес последней ячейки в колонке с тарифами.
* Первая строка таблицы с данными должна быть записана вручную, так как предыдущие показания счетчика копируются из строки выше. Если вы делаете расчет не для себя, добавьте в код обработчик ошибок.
Печать квитанций
Квитанция может понадобиться, если контролеры ресурсоснабжающей организации попросят подтверждение оплаты. Заполненная кодом VBA Excel и распечатанная квитанция с приложенным платежным чеком будет нагляднее, чем один чек Сбербанка или другой финансовой организации, платежной системы.
Я добавил реквизиты ресурсоснабжающей организации к первоначальной квитанции, которые на изображении и в приложенном файле изменены:
Чтобы распечатать квитанцию на оплату холодной воды или другого ресурса, необходимо в таблице с показаниями счетчика выбрать любую ячейку в строке с нужным периодом и нажать кнопку «Печатная форма». Квитанция будет заполнена данными за выбранный период и затем лист, на котором она расположена, будет активирован.
Код кнопки «Печатная форма» (расположен в модуле листа с таблицей):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
Private Sub CommandButton2_Click() Dim a() As Variant, i As Byte ReDim a(1 To 7) For i = 1 To 7 a(i) = Cells(ActiveCell.Row, i) Next With Sheets("Квитанция") For i = 1 To 7 .Range("Cell" & i) = a(i) Next .Select End With ' .Cells(1, 3) => Cell1 ' .Cells(1, 5) => Cell2 ' .Cells(9, 2) => Cell3 ' .Cells(9, 3) => Cell4 ' .Cells(9, 4) => Cell5 ' .Cells(9, 5) => Cell6 ' .Cells(9, 6) => Cell7 End Sub |
Информация из выбранной строки записывается в массив a(). Бланк документа расположен на листе «Квитанция». Чтобы заполнить бланк данными из массива с помощью цикла, заполняемым ячейкам присвоены имена (Формулы –> Присвоить имя). Ячейки, которым присвоены имена, перечислены внутри процедуры в комментариях.
В ячейке с итоговой суммой к оплате стоит формула =F9+C10.
Скачать пример
Вы можете скачать архив с моим файлом «Учет расхода воды», чтобы проверить его работоспособность. А также использовать его в качестве примера при создания своего файла Excel для регистрации показаний счетчика и расхода какого-либо коммунального ресурса с помощью VBA.
Чтобы хранить чеки Сбербанка, других финансовых организаций и платежных систем в электронном виде на своем компьютере, выведите их на печать, выберите принтер «Печать в PDF» и сохраните в выбранное место.
Спасибо!
Обсуждение закрыто.