Работа с электронными таблицами. Расчет квартплаты.
Задание:
1. Создать таблицу в папке с вашей фамилией на Google Диске.
2. Выполнить расчет квартплаты по трем различным вариантам исходных данных.
3. Оформить квитанции для предоставления их жильцам дома.
Ход выполнения работы:
1. Для того, чтобы создать таблицу воспользуемся приобретенными ранее знаниями и навыками. Зайдя на ваш Google Диск, нажмите на кнопку "Создать" и выберите пункт "Google Таблицы".
Перед вами откроется новая вкладка со следующим содержанием:
Переименуем таблицу в "Расчет квартплаты. Фамилия Имя"
1. Начнем создавать документ для расчета квартплаты.
1) Зададим возможность выбирать нужный месяц и год из выпадающего списка.
Кликнем на нужную ячейку.
Нажмите Данные - Проверка данных.
Выберите вариант в пункте "Правила" Значение из списка. Введите пункты списка через запятую, не разделяя их пробелами.
Нажмите на кнопку "Сохранить".
В ячейках появится значок - треугольник. При нажатии на который будет появляться выпадающий список, из которого будем выбирать нужные данные.
2) Создадим раздел с основными исходными данными.
И таблицу для расчета квартплаты.
А также форму для введения данных показаний по индивидуальным приборам учета.
2. Расчет квартплаты.
Начнем заполнять таблицу, ориентируясь на задание.
Номер квартиры для общего расчета смотрим в задании в таблице "Варианты заданий".
Фамилию И.О. берем из базы данных паспортного стола. Выберем совершеннолетнего собственника, на которого будут приходить счета на оплату.
Общую площадь квартиры берем в задании. Количество прописанных (зарегистрированных) также берем из задания. Информацию о том, есть ли льготы, найти можно там же.
Переходим к расчету квартплаты, с учетом того, какие приборы учета у вас установлены. Информацию о наличии приборов учета и их показаний также берем в задании.
Заполняем таблицу "Показания индивидуальных приборов учета". Если какого-либо счетчика по заданию нет, то поле оставляем свободным.
Далее переходим к вводу данных о тарифах.
Сбор, вывоз ТБО - 1,57 руб/м2
Содержание придомовой территории - 7,08% руб/м2 от размера платы за пользование жилым помещением http://arhcity.ru/?page=310/2 (принимаем капитальный 2 этажный дом с износом до 30% в округе Варавино-Фактория) вычислим тариф самостоятельно.
Техническое обслуживание - 7,95% руб/м2 от размера платы за пользование жилым помещением http://arhcity.ru/?page=310/2 (принимаем капитальный 2 этажный дом с износом до 30% в округе Варавино-Фактория) вычислим тариф самостоятельно.
Уборка лестничных клеток - 16,31% руб/м2 от размера платы за пользование жилым помещением http://arhcity.ru/?page=310/2 (принимаем капитальный 2 этажный дом с износом до 30% в округе Варавино-Фактория) вычислим тариф самостоятельно.
Услуги по управлению - 11,50% руб/м2 от размера платы за пользование жилым помещением http://arhcity.ru/?page=310/2 (принимаем капитальный 2 этажный дом с износом до 30% в округе Варавино-Фактория) вычислим тариф самостоятельно.
Текущий ремонт - 31,84% руб/м2 от размера платы за пользование жилым помещением http://arhcity.ru/?page=310/2 (принимаем капитальный 2 этажный дом с износом до 30% в округе Варавино-Фактория) вычислим тариф самостоятельно.
Горячая вода - тариф на горячую воду состоит из суммы двух компонентов: 1) тариф на тепловую энергию (руб/1 гкл), умноженный на количество гкл, необходимого для подогрева 1 м3 холодной воды, и 2) тариф на ХВС (руб/1м3). Стоимости этих компонентов найдем самостоятельно. Пример формулы =ОКРУГЛ(47,25+2349,21*0,0426;2)
Холодная вода - найдем тариф самостоятельно на сайте https://gkh.dvinaland.ru/
Водоотведение - найдем тариф самостоятельно на сайте https://gkh.dvinaland.ru/
Электроснабжение день - тариф зависит от того, установлены ли счетчики, а также какой именно счетчик установлен однотарифный или двухтарифный. Формула будет выглядеть следующим образом =ЕСЛИ(N15="";3,58;9,54) Значения тарифов в формуле не равны значениям настоящим!!! N15 - это ячейка в таблице "Показания индивидуальных приборов учета", соответствующая начальным показаниям электричество (ночь). Формула расшифровывается следующим образом: если ячейка с начальными показаниями ночного электросчетчика пустая " " (т.е. счетчика нет), то тариф будет равен стоимости 1 кВт электроэнергии с однотарифным счетчиком, если нет, то стоимости 1кВт электроэнергии с двухтарифным счетчиком. Данные о тарифе также берем на сайте https://gkh.dvinaland.ru/.
Электроснабжение ночь - подобным образом рассчитывается и этот тариф. Этот показатель будет считаться, если установлен двухтарифный счетчик на электричество. Формула будет выглядеть следующим образом =ЕСЛИ(N15="";0;2,74) Значения тарифов в формуле не равны значениям настоящим!!! N15 - это ячейка в таблице "Показания индивидуальных приборов учета", соответствующая начальным показаниям электричество (ночь). Формула расшифровывается следующим образом: если ячейка с начальными показаниями ночного электросчетчика пустая " " (т.е. счетчика нет), то тариф будет равен 0, если нет, то стоимости 1кВт электроэнергии со счетчиком. Данные о тарифе также берем на сайте https://gkh.dvinaland.ru/.
Газоснабжение - данные о тарифе берем на сайте https://gkh.dvinaland.ru/ Сжиженный газ реализуемый из групповых резервуарных установок, руб/м3
Отопление - тариф на отопление рассчитаем чуть позже, он будет зависеть от показаний общедомового прибора учета в зависимости от месяца.
Капитальный ремонт - 7,91 руб/м2
Для того, чтобы заполнить ячейку тарифа для отопления необходимо создать еще один лист. Переименовать его в "Расчет тарифа на отопление". И на этом листе создать 2 таблицы.
Далее введем следующие показания общедомового прибора учета:
Рассчитаем расход для каждого месяца. Конечные показания минус начальные.
Теперь введем значение тарифа на энергию, руб/гкл, найдем его самостоятельно. И найдем жилую площадь дома также самостоятельно.
Для того, чтобы рассчитать тариф на каждый месяц, нам нужна формула вида
=ОКРУГЛ(E4*$D$19/$D$20;2)
Расшифруем ее: тариф на отопление для каждого месяца будет равен произведению расхода на тариф, деленное на жилую площадь дома. Округлим это значение до 2 знаков после запятой.
Возвращаемся в лист Расчет квартплаты. Кликаем по ячейке тарифа для отопления. Формула будет иметь следующий сложный вид
=СУММЕСЛИ('Расчет тарифа на отопление'!A4:A15;D1;'Расчет тарифа на отопление'!B4:B15)
И расшифровывается она следующим образом: если при выборе месяца вверху листа Расчет квартплаты значение совпадаем со значением в таблице на листе Расчет тарифа на отопление, то выбрать соответствующее месяцу значение тарифа.
Переходим к заполнению столбца Расходы.
Здесь зависимость простая - если единицы измерения м2 - то расход равен общей площади квартиры. Формула будет следующая =$C$4. Таким образом, написав эту формулу в первой строке, мы сможем растянуть ее до параметра Горячая вода. Этот же показатель будет и в строках Отопление и Капитальный ремонт.
Следующим пунктом заполним значения расхода в параметре электроснабжение. Расход дневного и ночного показателей будет равен разнице конечных и начальных показаний приборов учета. Формула будет выглядеть так: =O14-N14 для дневного счетчика. Для ночного аналогично, только адреса ячеек изменятся на соответствующие.
Перейдем к расходу газоснабжения. Он зависит от того, установлены ли счетчики на газ. Если счетчиков нет, то зависимость будет от числа зарегистрированных в квартире граждан. Начнем разбираться подробнее.
Формула будет выглядеть следующим образом:
=ЕСЛИ(N16="";ЕСЛИ(C5<5;10*C5;ЕСЛИ(C5>=5;11,5*C5));O16-N16)
(Нормативы на газ в формуле не соответствуют реальным! Данные о нормативе берем самостоятельно на сайте https://gkh.dvinaland.ru/)
Расшифруем ее: если счетчик на газ не установлен, т.е. ячейка с начальными показаниями газового счетчика пустая, то тариф на газ будет зависеть от числа зарегистрированных граждан. А именно, если количество зарегистрированных граждан меньше 5, то тариф будет равен произведению норматива на количество прописанных, если больше 5, то также будет равен произведению нужного норматива на количество прописанных. Если же счетчики есть, то расход будет считаться, как конечные показатели минус начальные.
У нас в этом столбике осталось посчитать расход горячей и холодной воды. Он зависит опять же от того, установлены ли счетчики на воду. Если счетчиков нет, то расход будет зависеть от количества зарегистрированных граждан. Обо всем по порядку.
Если счетчик не установлен, то расход будет считаться как произведение количества прописанных на норматив, если же установлен, то как разница конечного и начального показаний.
Нам понадобится еще одна таблица.
В графу норматив мы вставим значения норматива на воду, взятого на сайте. Значения норматива на скриншоте не соответствуют реальным!!!
Обобщим эту информацию в формулу.
=ЕСЛИ(N13="";N23*$C$5;O13-N13)
Формулы на холодную и горячую воду примерно одинаковые, разница только в ячейках показаний и нормативе.
Расход водоотведения равен сумме расхода холодной и горячей воды
Рассчитаем сумму.
Для того, чтобы найти искомую сумму по каждой услуге необходимо умножить Тариф на Расход.
Формула будет выглядеть следующим образом:
=ОКРУГЛ(F10*G10;2)
Расшифруем ее: умножим тариф на расход и округлим значение до 2 знаков после запятой.
Исключение составит только расчет суммы для горячей и холодной воды, так как здесь есть зависимость от того, установлены ли счетчики в квартире. Если счетчиков нет, то будет учитываться повышающий коэффициент.
В соответствующей ячейке повышающего коэффициента введем формулу со следующей расшифровкой: если счетчика нет, т.е. ячейка с начальными данными пустая, то повышающий коэффициент будет равен 1,5, иначе ячейка останется пустой.
=ЕСЛИ(N12="";1,5;"")
Таким образом сумма для холодной и горячей воды будет рассчитываться по формуле:
=ОКРУГЛ(ЕСЛИ(H17="";F17*G17;F17*G17*H17);2)
Если ячейка с повышающим коэффициентом пустая, то сумма будет равна произведению тарифа на расход, иначе произведению тарифа на расход и на повышающий коэффициент. Значение округляется до 2 знаков после запятой.
После того, как сумма по всем услугам будет посчитана, найдем общую сумму оплаты коммунальных услуг "Итого":
=ОКРУГЛ(СУММ(I10:I23);2)
Расчет льготы.
Расчет льгот будем вести для всех услуг кроме электро- и газоснабжения.
Нам понадобится новая таблица. Расчет площади для льгот.
Рассчитаем социальные нормы площади. СНОПф (фактические) используются всегда, если льготы рассчитываем для инвалида, и равны:
=ОКРУГЛ(C4/C5;2)
Разнице общей площади квартиры и количества зарегистрированных. Причем, значение округлим до 2 знаков после запятой.
СНОПз (законные) используются для расчета ветеранских льгот в том случае, если они не превышают СНОПф. Зависят от количества проживающих в квартире людей.
=ЕСЛИ(C5=1;33;ЕСЛИ(C5=2;21;ЕСЛИ(C5>=3;18)))
СНОПитог зависит от того, какие именно льготы мы считаем.
=ЕСЛИ(C6="Инвалид";G6;ЕСЛИ(C6="Ветеран";МИН(G5;G6);ЕСЛИ(C6="Без льгот";"-")))
Расшифруем: если считаем льготы для инвалида, то выбираем СНОПф. Если считаем льготу для ветерана, то выберем минимальное значение из СНОПф и СНОПз. Если льгот нет, то поставим "-" прочерк.
Итак, мы рассматриваем три варианта расчета льгот: льгота для инвалида, льгота для ветерана и отсутствие всяческих льгот.
Максимальное количество льгот имеет ветеран. Ветеранам положены льготы за
сбор и вывоз ТБО, содержание придомовой территории, техническое обслуживание, уборка лестничных клеток, услуги по управлению и за текущий ремонт. Формула в каждом из этих пунктов будет следующая:
=ОКРУГЛ(ЕСЛИ($C$6="Ветеран";$G$4*F10*50%;0);2)
Если рассчитываем льготу для ветерана, то льгота будет равна произведению СНОПитога и тарифа, умноженного на 50%. Во всех остальных случаях льгота равна 0. Округлим это значение до 2 знаков после запятой.
Льгота на отопление и капитальный ремонт есть и у ветеранов и у инвалидов. Поэтому:
=ОКРУГЛ(ЕСЛИ($C$6="Без льгот";0;$G$4*F22*50%);2)
Если льгот нет, то значение равно 0, во всех остальных случаях льгота рассчитывается как произведение СНОПитога и тарифа, умноженного на 50%. Значение также округлим до 2 знаков после запятой.
Льгота на холодную и горячую воду, а также водоотведение положена также и ветеранам и инвалидам. Но для её расчета нам нужно дозаполнить следующую таблицу.
Рассчитаем реальный расход по каждому пункту.
=ОКРУГЛ(G16/C5;2)
Нам необходимо разделить расход на количество зарегистрированных в квартире человек и округлить это значение до 2 знаков после запятой.
Теперь перейдем к столбцу расход итоговый. Итоговый расход равен минимальному значению: или это будет норматив, или реальный расход.
=МИН(N22;O22)
После того, как таблица заполнена до конца, рассчитаем искомые льготы на воду.
=ОКРУГЛ(ЕСЛИ($C$6="Без льгот";0;P22*F16*50%);2)
Если льгот нет, то значение равно 0, во всех остальных случаях льгота рассчитывается как произведение итогового расхода и тарифа, умноженного на 50%. Значение также округлим до 2 знаков после запятой.
Осталось только подсчитать итоговую сумму всех льгот:
=СУММ(K10:K23)
Все таблицы рассчитаны.
3. Оформим наши расчеты в квитанцию.
Создадим новый лист и назовем его "Квитанция"
Сделаем следующую форму:
Ваша задача заключается в том, чтобы создать точно такую же форму, а также сделать так, чтобы в зависимости от того, как меняются данные в листе расчета квартплаты, данные в квитанции автоматически менялись.
Готовую квитанцию необходимо сохранить следующим образом: нажмем на иконку "Печать", или Файл - Печать.
Откроется такое окно:
Настроим его так, как показано на скриншоте и нажмем Далее.
Наша квитанция автоматически сохранится в виде .pdf документа и будет готова для печати.
Согласно заданию выполним все расчеты по вашему варианту. В итоге у вас должно быть 3 квитанции с необходимыми расчетами.
.pdf файлы с квитанциями загрузить в папку с Вашей фамилией на гугл диске.
Comments