учет показаний счетчиков в excel
VBA Excel. Учет расхода воды и других ресурсов
Учет расхода холодной воды и других коммунальных ресурсов по показаниям счетчиков с помощью VBA Excel. Расчет и хранение данных в электронном виде.
Учет расхода воды
Учет расхода коммунальных ресурсов по показаниям счетчиков рассмотрим на примере холодной воды, так как это самый распространенный случай, когда может понадобиться расчет и хранение данных в рабочей книге Excel.
В июне 2020 года у нас появилась возможность оплачивать воду через Сбербанк-онлайн, поэтому отпала необходимость заполнять и печатать квитанции, о которых я писал раньше. Но возникло желание автоматизировать расчет суммы к оплате и сохранять данные по месяцам в электронном виде с помощью VBA.
Для записи показаний счетчика холодной воды, расхода ресурса и суммы к оплате с помощью VBA Excel я использую следующую таблицу:
Таблица учета холодной воды
Справа от таблицы учета холодной воды расположены командные кнопки и таблица тарифов:
Командные кнопки и тарифы
Верхняя строка рабочего листа закреплена (Вид –> Закрепить области –> Закрепить верхнюю строку). Кнопки «Добавить» и «Печатная форма» добавлены из коллекции элементов управления ActiveX:
Добавление элементов управления ActiveX
Таблица с показаниями счетчика, расходом воды и суммой к оплате преобразована в умную таблицу, у которой снят фильтр, отображавшийся в строке заголовков.
Кнопка «Добавить» рабочего листа открывает следующую пользовательскую форму:
Форма для добавления текущих показаний счетчика
Код кнопки «Добавить» на рабочем листе (расположен в модуле листа с таблицей):
При загрузке формы выполняется следующая процедура (расположена в модуле формы):
Нам остается только ввести текущие показания счетчика и нажать кнопку «Добавить» на пользовательской форме. Поля «Год» и «Месяц» добавлены на всякий случай, если понадобится их скорректировать.
Код кнопки «Добавить» пользовательской формы, который записывает новую* строку в таблицу (расположен в модуле формы):
* Первая строка таблицы с данными должна быть записана вручную, так как предыдущие показания счетчика копируются из строки выше. Если вы делаете расчет не для себя, добавьте в код обработчик ошибок.
Печать квитанций
Квитанция может понадобиться, если контролеры ресурсоснабжающей организации попросят подтверждение оплаты. Заполненная кодом VBA Excel и распечатанная квитанция с приложенным платежным чеком будет нагляднее, чем один чек Сбербанка или другой финансовой организации, платежной системы.
Я добавил реквизиты ресурсоснабжающей организации к первоначальной квитанции, которые на изображении и в приложенном файле изменены:
Квитанция на оплату холодной воды
Чтобы распечатать квитанцию на оплату холодной воды или другого ресурса, необходимо в таблице с показаниями счетчика выбрать любую ячейку в строке с нужным периодом и нажать кнопку «Печатная форма». Квитанция будет заполнена данными за выбранный период и затем лист, на котором она расположена, будет активирован.
Код кнопки «Печатная форма» (расположен в модуле листа с таблицей):
Как начать учитывать коммунальные услуги с комфортом
Я никогда не считал себя дисциплинированным человеком в вопросах, связанных с учетом коммунальных услуг: показания счетчиков снимал нерегулярно, оплату по квитанциям каждый раз откладывал на потом, которое наступало месяца через 3-4. Из-за этого приходилось переплачивать, т.к. начислялись различные пенни, да и сложно было перепроверить управляющую компанию, а правильно ли они начисляют. Периодические попытки начать учитывать показания счетчиков и платежей по ним подручными средствами ни к чему не приводили: листки с записями показаний терялись, excel-файл забывал заполнять. А тут еще в ноябре прошлого года наша УК учудила и прислала мне и другим жильцам нашего дома квитанции с непонятно большими суммами на оплату по отоплению, просто перестав учитывать показания счетчиков тепла, объясняя это большими теплопотерями дома и совершенно не прозрачными формулами расчета, по которым даже у них самих «дебет с кредитом не сходился».
Жильцы нашего дома, в том числе и я, сильно возмутились такому хамскому отношению и, в страстном порыве восстановить справедливость, начали объединяться в ТСЖ, я же задумался об удобном способе учета показаний счетчиков со всевозможными анализами данных, отчетами, напоминалками, а также обмене полезной информацией со своими соседями.
Сразу скажу, что сервис, пусть и в минимальном функционале, уже доступен любому по адресу: dom24x7.ru
Для учета своих личных финансов я часто пользовался различными специализированными программами и сервисами, которых в интернете хоть пред пруди, на любой вкус и цвет, поэтому моей первой мыслю было найти подходящий сервис для учета коммуналки. Как же я был удивлен, когда не нашел такого сервиса (не исключаю возможности, что просто плохо искал), а различные поделки для мобильного устройства были примитивны и не удовлетворяли моим требованиям.
Раз такого сервиса я не нашел, то нужно его сделать, подумал я и, засучив рукава, приступил к разработке.
На носу были новогодние каникулы и можно было ни на что не отвлекаясь полностью погрузиться в разработку.
Набросав небольшой план по хотелкам и функционалу, приступил к разработке архитектуры сервиса и структуры БД, параллельно заказал дизайн сайта. Немного поразмышляв, решил для разработки сервиса использовать язык java и фреймворк vaadin. Почему именно его? Я его знаю достаточно хорошо и мне не хотелось городить огород с использованием разных технологий и языков, а по максимуму писать именно на java. К сожалению, у такого решения есть и свои минусы, основной из которых, по моему мнению, это плохое взаимодействие с поисковыми сервисами. Но, в основном, практически весь функционал будет доступен только в закрытой зоне только после авторизации, куда поисковикам и так доступа нет, а также, как у Яндекса, так и у Гугла есть механизм индексации сайтов, которые полностью сделаны на технологии ajax, и vaadin этот механизм полностью поддерживает (ссылки на описание механизма можно посмотреть в конце статьи).
С выбором хостинга оказалось чуть посложнее, т.к. мало кто предлагает хостинг под java. Тут либо арендовать виртуальный сервер, либо смотреть в сторону облаков. Я выбрал второй путь и решил присмотреться к активно развивающемуся в последнее время облачному хостингу jelastic. Разобраться с ним удалось буквально слету (спасибо ребятам, разработавшим столь удобный продукт) и окружение было создано и настроено, так же никаких проблем не вызвало настроить домен dom24x7.ru для работы с облачным хостингом.
На текущий момент удалось разработать маленькую толику запланированного функционала:
сервис позволяет после регистрации создать сколько угодно адресов и прицепить к каждому из них неограниченное количество счетчиков различных типов (вода, электроэнергия, отопление и газ). Можно создать несколько счетчиков одного типа (например, для учета горячего и холодного водоснабжения). По каждому счетчику можно сохранять показания, и просматривать различные отчеты и графики.
В планах внедрить социальную составляющую, которая позволит жителям одного дома обмениваться сообщениями, учитывать платежи по присылаемым платежным поручениям, а в перспективе платить непосредственно через сервис, добавлять различные напоминания и многое другое.
P.S.: Это моя первая статья из многих, в которых я постараюсь полностью осветить жизнь и развитие сервиса dom24x7.ru, а также тех технологий, которые используются в его создании.
Обновление от 29.03.2014: Пока разбираюсь с не найденными адресами, да и на будущее, разрешил редактировать адрес непосредственно
Расчет коммуналки в Excel
Спасибо тебе добрый человек!!
У меня есть бот для телеграмма если кому надо в поиске: pikabu_hot
он туда всю ленту транслирует
правда он последние два как-то странно работает..
Интересует как можно использовать меньше 100 кВт*ч электричества?
И куда, если у тебя отопление отдельной графой, можно сжечь 50 кубов газа?
блин здорово а для многотарифных счетчиков нету?
у нас 3 рубля за электричество. странно это)))
А у нас электричество считается по трем тарифам (до 150 кВт, от 150 до 450, от 450 и выше. Если не ошибаюсь, так).
Хорошая табличка, но только от квитанции будет отличаться. У нас например добавлены какие-то обще домовые коэффициенты, которые невозможно предугадать((((
У меня проблема возникла. Копирую область, добавляю текущие показатели, все считается правильно, график ссылается на сводную таблицу предыдущего месяца, не понимаю, почему(
большое спасибо за проделанную работу!
можно будет допилить для себя отталкиваясь от наработки
Кстати, я под себя переделал, тут правда не доработка. Вопрос остаётся открытым, как писать в exsel за электроэнергия одн? Тоже не маловажный момент.
Отопление как считать? Выложите формулу с отоплением.
Я давно подобный файлик налабал с дебитом/кредитом для снимающих квартиру. Вбил показания счетчиков и все само расчитывается.
мне не надо, но возьму ибо халява
Нифига себе, я уже забыл, когда трехзначные суммы платил за квартиру =/
тариф на газ смени, уже по 3,60
Охуенчик, схоронил! Вроде все просто, но самому пилить это в Excel в лом
Погодь, а перерасход не макрос создает? А если перерасход воды, например? Это самому надо строку добавлять?
ахуенчик, пойду жену обучу.
Спасибо.
лабораторная по информатике первого курса института 10-летней давности
Что это значит? Дорого или дешево? И зачем это нужно? Может здесь юмор какой зарыт.
Школьникам то тоже объясняйте.
Эстетика старины
Расчет коммунальных услуг в Microsoft Excel.
Практическая работа направлена на изучение возможностей программы Microsoft Excel. Дети учатся вводить исходные данные, производить расчеты, используя автозаполнение, копирование, стандартные математические формулы, функцию автосуммирования, строить диаграммы, проводить анализ полученных данных.
Содержимое разработки
«Расчет платежей за коммунальные услуги в Microsoft Excel».
Научиться вводить исходные данные, используя возможности автозаполнения, автомусуммирования, копирования.
Научиться производить расчеты, используя формулы, стандартные математические и логические функции.
Научиться строить диаграммы.
Задание. Расчет платежей за коммунальные услуги
Заполнить исходные данные таблицы за сентябрь, октябрь и ноябрь.
— ежемесячной стоимости потребления коммунальных услуг;
-сумму оплаты за каждый месяц.
Построить диаграмму стоимости потребления услуг по месяцам.
Для этого надо выделить диапазон ячеек А2:А10, зажать клавишу Ctrl и не отпуская, выделить диапазон F2:F10 (одновременно должны быть выделены два диапозона). Далее перейти на вкладку Вставка (в горизонтальном меню сверху), выбрать тип диаграммы Гистограмма и нажать на любой вариант из предложенных гистограмм (см. рисунок)
Щелкните указателем мышки по словам К уплате (над диаграммой), удалите этот текст, наберите Сентябрь, а справа слова К уплате удалите.
Как организовать учет показаний счетчиков на компьютере
Независимый учет показаний счетчиков позволит найти ошибку в квитанции за услуги ЖКХ, понять в какой месяц вы потратили больше, в какой меньше. Если у вас есть компьютер, то организовать такой учет просто. Два бесплатных способа хранения и анализа счетчиков — в нашей статье.
С использованием Google Docs
Самый простой способ учесть показания счетчиков на компьютере — создать таблицу в Google Docs. Преимущество такого способа в том, что заносить показания счетчиков можно будет как с компьютера, так и с телефона.
Зайдите по этой ссылке на свое Google Drive и создайте кнопкой + новую таблицу. У меня на создание такой таблички ушло 30 секунд.
Чтобы не искать каждый раз ее в списке документов, выделите адрес вкладки, нажмите левую клавишу мыши и вытяните ссылку на Рабочий стол компьютера.
У вас на Рабочем столе будет создан ярлычок для быстрого доступа к файлу.
Для работы со смартфона нужно установить бесплатное приложение Google Таблицы по этой ссылке. Так вы сможете пополнять таблицу со счетчиками прямо с телефона.
Установить приложение Коммуналка для Windows 10
Второй вариант удобного учета показаний счетчиков на компьютере — установить программу Коммуналка из стандартного магазина приложений Microsoft Store по этой ссылке.
Начать работу в ней просто. Нужно добавить счетчик, указать действующий тариф.
Можно установить напоминание о необходимости внести новые показания, например, 21 числа.
Ведется статистика показаний, подсчитываются суммы квитанций, чтобы проконтролировать управляющие компании.
Есть режим работы с несколькими квартирами. В настройках нужно поставить галочку напротив позиции Открывать список квартир.
Приложение бесплатно. Может работать и на мобильном устройстве под управлением Windows Mobile. Если они вообще у кого-нибудь остались.