Бөлімшелер көрсеткіштерінің мониторингін Excel-де қалай құруға болады

Мекемеде немесе басқармада бірнеше бөлім мен ондаған көрсеткіш болғанда, сандар әртүрлі файлдарда жатады, ал басшы кеңесте жалпы көріністі көрмейді. Бір бөлімнің есебі бір форматта, екіншісінікі басқа форматта келеді. Нәтижесінде кім жоспарды орындап жатыр, кімнің жағдайы нашарлап барады деген сұраққа жауап беру үшін бірнеше сағат кетеді.

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

Алдымен көрсеткіштерді анықтаңыз

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

• өлшем бірлігі (дана, ₸, %, күн);
• бағыты: «көп — жақсы» немесе «аз — жақсы»;
• жинақтау тәсілі: сома (дана, ₸ үшін) немесе орташа (пайыз, мерзім үшін);
• салмағы (1-ден 5-ке дейін): маңыздылық.

Бағыт өте маңызды: шағымдар саны жоспардан асса, бұл жақсы емес. Жиынтық кестеде бұл екі түрдің орындалуы әртүрлі есептелуі керек.

Орындалу пайызын есептеу

«Көп — жақсы» көрсеткіштер үшін:

=факт/жоспар

«Аз — жақсы» көрсеткіштер үшін (шағымдар, кешігулер, өңдеу мерзімі):

=жоспар/факт

Бір формулаға біріктіру үшін бағытты жеке бағанға жазыңыз (мысалы, «+» немесе «−»):

=IF(E2="+";C2/B2;B2/C2)

Мұндағы B2 — жоспар, C2 — нақты, E2 — бағыт. Нөлге бөлуден сақтану үшін IFERROR қолданыңыз. Нәтижені шектеңіз: бір көрсеткіш 400%-ға жетіп, орташаны бұрмаламауы үшін, ең үлкен мәнді (мысалы, 150%) параметрге шығарыңыз:

=MIN(IFERROR(IF(E2="+";C2/B2;B2/C2);0);$K$1)

Мысал:

Шағым саны жоспардан аз болғандықтан орындалу 100%-дан жоғары, ал мерзім ұзарғандықтан 100%-дан төмен.

Жинақталған нәтиже және бағдаршам

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

• сома үшін: =SUM(C2:C6) (қаңтардан есепті айға дейін);
• орташа үшін: =AVERAGE(C2:C6).

Есепті айды жеке ұяшықта таңдасаңыз, SUM аймағы үшін OFFSET немесе INDEX қолдануға болады: =SUM(C2:INDEX(C2:N2;$K$2)), мұндағы K2 — ай нөмірі. Бұл Excel-дің барлық нұсқасында істейді.

Бағдаршам үшін екі шекті параметр ұяшығына шығарыңыз. Мысалы, «жоспарға сай» — 95% және одан жоғары, «тәуекел» — 80%-дан төмен:

=IF(F2>=$K$3;"Жасыл";IF(F2>=$K$4;"Сары";"Қызыл"))

Шектер сіздің ішкі бағдарыңыз екенін есте ұстаңыз: оларды ұйымыңыздың қолданыстағы жоспарларымен және бұйрықтарымен салыстырыңыз. Содан кейін Conditional Formatting арқылы жасыл, сары және қызыл түсті қойыңыз.

Бөлімшелер рейтингі

Бөлімшенің жалпы орындалуы — оның көрсеткіштерінің салмақпен өлшенген орташасы:

=SUMPRODUCT(орындалу_аймағы;салмақ_аймағы)/SUM(салмақ_аймағы)

Мысал: екі көрсеткіш, салмақтары 5 және 2, орындалуы 90% және 120%. Орташа = (0,9×5 + 1,2×2)/7 = 6,9/7 = 98,6%. Бөлімшелерді реттеу үшін RANK: =RANK(G2;$G$2:$G$13).

Қосымша пайдалы көрсеткіш — динамика: өткен айға қарағанда орындалу өсті ме, түсті ме. =F2-G2 айырмасы кімнің жағдайы жақсарып, кімдікі нашарлап жатқанын көрсетеді.

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

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

Дайын үлгі: бөлімшелер көрсеткіштерінің мониторингі

Формулаларды өзіңіз құрғыңыз келмесе, дайын кестені алуға болады. Үлгі ақылы және төрт парақтан тұрады.

«Анықтамалықтар» парағында жыл, есепті ай, бағдаршам шектері, бөлімшелер (12-ге дейін) және өлшем бірлігі, бағыты, жинақтауы мен салмағы бар көрсеткіштер (20-ға дейін) беріледі. «Жоспар» және «Нақты» парақтарында қаңтардан желтоқсанға дейінгі айлары бар «бөлімше — көрсеткіш» 200 жолға дейін тұрады; «Нақтыда» атаулар өздігінен қойылады. «Есептеу» парағында жинақталған орындалу, бағдаршам, өткен айға қатысты динамика және әр ай бойынша орындалу есептеледі. «Жиынтық» парағында бөлімшелер рейтингі, жасыл, сары және қызыл аймақтағы көрсеткіштер саны, айлар бойынша динамика кестесі және екі диаграмма бар.

«Есепке алу максимумы» әдепкі бойынша 150% болады. Файл қазақ тілінде, Excel 2007 және одан жаңа нұсқаларда және LibreOffice-те макростарсыз жұмыс істейді. Шектер мен салмақтарды өзіңіз баптайсыз: бұл ішкі бағдарлар, оларды қолданыстағы жоспарлармен салыстырыңыз. Есептеу болжамды.

Басқармалардың, мекемелердің және бөлімдердің басшыларына, мемлекеттік органдар мен бюджеттік ұйымдардағы бақылау және есептілік мамандарына, сондай-ақ күрделі жүйесіз көрсеткіштердің түсінікті жиынтығы қажет бизнестегі бөлімше бастықтарына қолайлы.

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

Қанша көрсеткіш жеткілікті?

Бөлімшеге 5–10 негізгі көрсеткіш жеткілікті. Көрсеткіш көп болса, нақты маңыздысы жоғалады. Дайын үлгіде 20 көрсеткішке дейін орын бар.

Жоспар жыл ішінде өзгерсе не істеу керек?

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

Бір көрсеткіш жеке адамдарға да қатысты бола ма?

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

Мониторингті қаншалықты жиі жаңарту керек?

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