Энергоресурстарды тұтыну мен лимиттерді Excel-де есепке алу

Есептегіштердің көрсеткіштері бір қалтада, шоттар екіншісінде, лимит туралы құжат үшіншісінде жатады. Нәтижесінде мекеме жылдың аяғында ғана «лимиттен асып кеттік» деп біледі, ал ол кезде әрекет жасауға кеш болады. Бірнеше ғимараттың электрі, суы, жылуы және газы болса, шатасу одан да көп.

Excel-де бұл жүйені жарты күнде құруға болады. Негізгі идея: көрсеткішті айына бір рет бір жолға жазасыз, ал сома, лимит, артық шығыс пен өткен жылмен салыстыруды формула есептейді. Төменде қадамдарын көрсетеміз.

Қандай деректер керек

Үш анықтамалық және бір журнал жеткілікті:

1. Ресурстар мен тарифтер: электр энергиясы (кВт·сағ), су (м³), жылу (Гкал), газ (м³).
2. Ғимараттар тізімі.
3. Жылдық лимиттер — әр ғимарат пен ресурс бойынша.
4. Тұтыну журналы: жыл, ай, ғимарат, ресурс, тұтыну.

Тарифтер мен лимиттерді ресурстармен қамтамасыз ету шарттарынан, шоттардан және мекемеңіздің лимиттер бойынша құжаттарынан алыңыз. Мақаладағы сандар — шартты.

Айлық лимит: бөлу үлестері

Жылдық лимитті 12-ге бөлу әрқашан дұрыс емес. Жылу мен газ қыста көп жұмсалады, ал электр жыл бойы біршама тең. Сондықтан әр ай үшін үлес (%) береді, қосындысы 100% болуы керек. Мысал үшін электрге үлес айына 8,33% (бірдей), жылуға қаңтар — 18%, шілде — 0%.

Айлық лимит: =Жылдық_лимит*VLOOKUP(ай;Үлестер;2;0)

Сома, артық шығыс, ақшалай әсер

Сома = тұтыну × тариф:

=C2*VLOOKUP(B2;Тарифтер;2;0)

Артық шығыс = тұтыну − айлық лимит, егер оң болса:

=MAX(0;C2-D2)

Артық шығыстың ақшалай құны = артық шығыс × тариф.

Мысал. Бір ғимарат үшін электрдің жылдық лимиті 120 000 кВт·сағ, наурызға 10% бөлінген, яғни айлық лимит 12 000 кВт·сағ. Нақты тұтыну 13 500 кВт·сағ, тариф 30 ₸/кВт·сағ. Сома: 13 500 × 30 = 405 000 ₸. Артық шығыс: 1 500 кВт·сағ, ақшамен 1 500 × 30 = 45 000 ₸.

Бағдаршам: лимитке жақындағанда ескерту

Лимит асқанда емес, асуға жақындағанда білу пайдалы:

=IF(C2>D2;"Асып кетті";IF(C2>=D2*0,9;"Лимитке жақын";"Қалыпты"))

Шекті 90% деп қойдық, оны өз қажетіңізге қарай өзгертіңіз. Түстерді «Шартты пішімдеу» береді: қызыл, сары, жасыл.

Өткен жылмен салыстыру

Өткен жылдың деректерін сол журналға енгізсеңіз, салыстыру оңай. Ағымдағы жылдың тұтынуын өткен жылдың сол айымен, сол ғимаратпен және ресурспен салыстыру:

=C2/SUMIFS($C:$C;$A:$A;A2-1;$B:$B;B2;$E:$E;E2;$F:$F;F2)-1

Нәтиже пайыздық пішімде өзгерісті көрсетеді. +12% — тұтыну өткен жылмен салыстырғанда 12% артқан.

Жиынтық есеп

Ресурстар мен ғимараттар бойынша қорытындыны SUMIFS жинайды, ал айлар бойынша кесте мен диаграмма тенденцияны көрсетеді. Басшыға ең пайдалысы: қай ғимарат пен қай ресурс лимитті асырып жатыр және ақшамен қанша.

Жиі кездесетін қателер

• Көрсеткішті емес, тұтынуды жазбау. Есептегіш көрсеткіші мен айлық тұтыну екі басқа нәрсе: тұтыну — екі айдың көрсеткіш айырмасы.
• Үлестердің қосындысы 100% емес. Лимит жылдық санмен сәйкес келмейді.
• Тарифті жаңартпау. Тариф өзгергенде анықтамалықты түзетіңіз, әйтпесе сома ескі бағамен есептеледі.
• Ғимараттардың атын әртүрлі жазу. Ашылмалы тізім қолданыңыз.

Дайын Excel үлгісі

Өзіңіз құрғыңыз келмесе, «Мекеменің энергоресурстарын тұтыну мен лимиттерін есепке алу» үлгісі бар. Үлгі ақылы. Онда:

• «Анықтамалықтар» парағында ресурстар мен тарифтер, 8 ғимаратқа дейін, әр ғимарат пен ресурс бойынша жылдық лимиттер, лимиттің айлар бойынша үлестері және есепті жыл беріледі.
• «Тұтыну» парағы 1000 жазбаға дейін қабылдайды: жыл, ай, ғимарат, ресурс, тұтыну; тариф, сома, айға лимит, артық шығыс, бағдаршам және өткен жылға қатысты өзгеріс өздігінен есептеледі.
• «Жиынтық» парағында ресурстар мен ғимараттар бойынша қорытындылар, ақшалай артық шығыс, айлар бойынша кесте және екі диаграмма бар.

Лимитке жақындау шегі әдепкі бойынша 90% және оны «Анықтамалықтар» парағында өзгертуге болады. Жинақта бос кесте және ойдан шығарылған деректері бар толтырылған үлгі бар, сондықтан формулалар қалай жұмыс істейтінін көресіз. Файл Excel 2007 және одан жаңа нұсқаларында, LibreOffice-те жұмыс істейді, макростарсыз, қазақ тілінде. Тарифтер мен лимиттерді қолданыстағы ережелермен салыстырыңыз; есептеу болжамды. Үлгі бас инженерлерге, шаруашылық жөніндегі орынбасарларға, бюджеттік мекемелердің, мектептердің, ауруханалардың, басқарушы компаниялардың басшылары мен бухгалтерлеріне жарайды.

Жиі қойылатын сұрақтар

Көрсеткіштерді қаншалықты жиі енгізу керек?

Айына бір рет, айдың соңында, шоттармен бір мезгілде. Сонда айлық лимитпен салыстыру мағыналы болады.

Бір ғимаратта бірнеше есептегіш болса ше?

Тұтынуды ғимарат бойынша қосып, бір жолға жазыңыз. Қосымша детализация қажет болса, ғимараттарды бөлек жолдармен көрсетуге болады.

Лимит жоқ болса, кесте керек пе?

Иә: тұтынуды өткен жылмен салыстыру өзі-ақ үнемдеуді көрсетеді. Лимит ретінде өткен жылдың сандарын қоюға болады.

Жылыту маусымын қалай ескереміз?

Жылу мен газға айлар бойынша үлестерді өзгеше қойыңыз: қыс айларына көбірек, жазға аз немесе нөл.