Автоматизация системы учета и проверки коммунальных платежей в MS Excel

Аннотация. В данной работе описывается разработка, функции и использование программы для учета и проверки коммунальных платежей. Программа была создана в среде MS Excel.

Ключевые слова: MS Excel, арифметические операторы, логические операторы, информационная система, условное форматирование, автоматизированная система

Во многих видах профессиональной деятельности, в том числе деятельности экономики предприятий и организаций, MS Excel используется в качестве электронной таблицы для автоматизации расчетов различной направленности. Использование таблиц MS Excel удобно тем, что он предоставляет возможность быстрого вычисления расчетов и выполнения как сложных, так и простых математических операций. MS Excel полезен так же тем, что при копировании формул автоматически производятся расчеты по ним, и выдается точный наглядный результат.

Как правило, в формулах для обозначения таких действий как сложение и вычитание применяются операторы. Их можно поделить на несколько групп.

Арифметические операторы:

1. «+» - сложение. Пример: =А2+В4;
2. «-» - вычитание. Пример: =А2-В4;
3. «*» - умножение. Пример: =А2*В4;
4. «/» - деление. Пример: =А2/В4;
5. «%» - знак процента. Пример: =25%;
6. «^» - знак степени. Пример: =3^2 (3 во 2-й степени).

Операторы сравнения:

1. «Равно». Пример: =ЕСЛИ(A2=B4;« Да»; «Нет»);
2. «Больше». Пример: =ЕСЛИ(А2>B4;A2;B4);
3. «Меньше». Пример: =ЕСЛИ(А2<В4;В4;А2);
4. «Не равно». Пример: =ЕСЛИ(А2<>В4;"Не равны");
5. «Больше или равно/Меньше или равно». Пример: =ЕСЛИ(А2>=В4;А2;В4) =ЕСЛИ(А2<=В4;В4;А2).

В результате выполнения арифметической операции мы всегда получаем число. Арифметические операторы применяются для вычисления базовых математических операций.

Операторы сравнения применяются для сравнения двух чисел. В результате мы получаем логическое значение ЛОЖЬ/ИСТИНА.

В частности, функция ЕСЛИОШИБКА используется для обработки ошибок в формуле. Она возвращает значение, которое мы указываем, если формула оценивается как ошибка. В противном случае возвращается результат формулы.

На практике использование этих операторов можно рассмотреть в автоматизированной системе учета и проверки коммунальных платежей.

Процесс расчета коммунальных платежей, требующий множества действий, можно представить в виде простой таблицы в MS Excel, которая будет подсчитывать нужные значения автоматически. Удобство системы заключается в следующем:

1. Самостоятельный расчет суммы к оплате по счетчикам, возможность хранения и проверки счетов – в электронном виде;
2. Автоматическая проверка ошибок;
3. Компактный формат таблицы: одна строка на месяц, всего 12 строк за расчеты на год;
4. Возможность ввода и расчета значений в соответствии с должными тарифами, а также легкость в смене тарифов;
5. Комфорт и отсутствие усилий при использовании.

При подсчете итоговых значений в таблице используется функция СУММ, которая суммирует все значения в указанном диапазоне ячеек.

Для расчетов, требующих учета тарифных планов, а также для проверки значений, встроены операторы сравнения и функции ЕСЛИ и ЕСЛИОШИБКА.

«Функцию ЕСЛИОШИБКА можно использовать для треппинга и обработки ошибок в формуле. ЕСЛИОШИБКА возвращает значение, которое вы указываете, если формула оценивается как ошибка. в противном случае возвращается результат формулы.» [1]

Обозначить ошибки в таблице позволяет условное форматирование, с помощью которого можно легко выделять необходимые ячейки или диапазоны, подчеркивать необычные значения и визуализировать данные с помощью гистограмм, цветовых шкал и наборов значков. При его использовании на основе указанных условий изменяется внешний вид ячеек; если же условия выполнены, то форматирование применяется к диапазону ячеек. «Существует множество встроенных условий, а также вы можете создавать собственные условия (в том числе и с помощью формулы, результатом которой становится значение "Истина" или "Ложь")» [2]

Набор этих простых функций помогает выполнить действия, которые без использования автоматизированной системы могут вызывать некоторые трудности.

Использование таблицы основано на изменении исходных данных: чисел, тарифов, цен. Например, при оформлении нового месяца требуется только скопировать последнюю строку, вставить ее следующей и затем ввести показания счетчиков. Система выдаст значения на основе новых данных.

Также наша система рассчитывает показания счетчиков на электричество по трем показателям: T1, T2, T3. Такой расчет называется дифференцированным по трем зонам суток.

Рисунок 1. Дифференцированный расчет электричества по трем зонам Т1, Т2, Т3

• Т1 – пиковая зона (07:00 – 10:00 и 17:00 - 21:00) 
• Т2 – ночная зона (23:00 – 7:00)
• Т3 – полупиковая зона (10:00-17:00 и 21:00-23:00)

Ниже представлена автоматизированная система учета и проверки коммунальных платежей на целый календарный год.

Рисунок 2. Автоматизированная система учета и проверки коммунальных платежей