Курьерлерге төлемді Excel-де есептеу: мөлшерлеме, шақырым, айыппұл, бонус

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

Шешімі қарапайым: әр жеткізуді бір жолға жазып, төлемді формуламен есептеу керек. Сонда кез келген соманың қайдан шыққанын жолдан көресіз. Бұл мақалада төлем схемасын қалай құруды, қандай формулалар қажет екенін және қандай қателерден сақ болу керегін көрсетеміз. Соңында — дайын үлгі туралы.

Төлем схемасын алдын ала келісіп алыңыз

Кесте жасамас бұрын ережені қағазға түсіріңіз. Әдетте мынадай бөліктер болады:

• аймақ мөлшерлемесі — бір тапсырыс үшін тұрақты сома (қала орталығы, шет аудан, іргелес елді мекен);
• шақырымға үстеме — әр шақырым үшін қосымша ақы;
• кешігу айыппұлы — уәде етілген уақыттан артық кешіккені үшін ұсталым;
• жол берілетін уақыт — мысалы, 10 минут: одан кіші кешігу айыппұлға кірмейді;
• жоспар бонусы — айда белгіленген тапсырыс санын орындағанға қосымша сома.

Осының бәрін жеке «Анықтамалық» парағына шығарыңыз. Мөлшерлеме өзгергенде формулаларды емес, тек бір ұяшықты түзетесіз.

Жеткізулер журналы

Бір жол — бір жеткізу. Қажетті бағандар:

Аймақ бағанына ашылмалы тізім жасаңыз («Деректер → Деректерді тексеру → Тізім»). Сонда «Орталық» пен «орталық » деген екі түрлі жазылу пайда болмайды және VLOOKUP қате бермейді.

Бір жеткізу үшін төлемді есептеу

Кешігу минуттарын мына формуламен алуға болады (E — уәде етілген уақыт, F — нақты уақыт, жол берілетін уақыт — $K$1 ұяшығында):

=MAX(0;ROUND((F2-E2)*1440;0)-$K$1)

Төлем: аймақ мөлшерлемесі плюс шақырым × үстеме, минус айыппұл. Төлем нөлден төмен болмауы үшін MAX қолданыңыз:

=MAX(0;VLOOKUP(B2;Аймақтар;2;0)+D2*VLOOKUP(B2;Аймақтар;3;0)-IF(G2>0;$K$2;0))

Мысал. Орталық аймақ: тапсырыс үшін 700 ₸, әр шақырымға 50 ₸. Жеткізу 6 км, кешігу 18 минут, жол берілетін уақыт 10 минут, айыппұл 200 ₸. Төлем = 700 + 6 × 50 − 200 = 800 ₸. Дәл уақытында жеткізілсе, 1 000 ₸ болар еді.

Кезең бойынша жиынтық пен бонус

Курьер бойынша көрсеткіштерді SUMIFS және COUNTIFS функциялары жинайды:

• тапсырыс саны: =COUNTIFS(Журнал!$C:$C;A2;Журнал!$A:$A;">="&$B$1;Журнал!$A:$A;"<="&$B$2);
• төленетін сома: =SUMIFS(Журнал!$J:$J;Журнал!$C:$C;A2;Журнал!$A:$A;">="&$B$1;Журнал!$A:$A;"<="&$B$2);
• мерзімінде жеткізу үлесі: кешікпегендер санын жалпы санға бөлесіз.

Бонус шарты екі бөліктен тұрады: тапсырыс саны жоспардан кем емес және мерзімінде жеткізу үлесі белгіленген шектен төмен емес. Мысалы, жоспар 150 тапсырыс, ең аз үлес 80%:

=IF(AND(B2>=C2;D2>=0,8);$K$3;0)

Мұнда B2 — орындалған тапсырыс, C2 — жоспар, D2 — мерзімінде жеткізу үлесі, $K$3 — бонус сомасы.

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

• Уақытты мәтін ретінде енгізу. «14:30» уақыт пішімінде болмаса, айырма есептелмейді. Ұяшық пішімін тексеріңіз.
• Түнгі ауысымда түннен өту. Тапсырыс 23:50-де уәде етіліп, 00:10-да жеткізілсе, айырма теріс шығады. Мұндай жағдайда күнін де жазыңыз.
• Мөлшерлемені формуланың ішіне жазып қою. Ертең мөлшерлеме өзгерсе, жүздеген ұяшықты қолмен түзетуге тура келеді.
• Айыппұлды төлемнен асырып ұстау. MAX(0;…) қоспасаңыз, кейбір жолда теріс сома шығады.
• Кезең күндерін қолмен ауыстырмау. Есептің басы мен соңы бір жерде тұрса, басқа айды қарау оңай.

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

Мұны өзіңіз құрғыңыз келмесе, «Жеткізулерді есепке алу және курьерлерге төлем» үлгісін қолдануға болады. Үлгі ақылы. Онда жоғарыдағы әдіс дайын күйінде жасалған:

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

Жинақта бос кесте және ойдан шығарылған деректері бар толтырылған үлгі бар, сондықтан формулалар қалай жұмыс істейтіні бірден көрінеді. Бұл қарапайым формулалардағы Excel кестесі, бухгалтерлік бағдарлама емес. Файл Excel 2007 және одан жаңа нұсқаларында, сондай-ақ LibreOffice-те жұмыс істейді, макростарсыз, қазақ тілінде. Мөлшерлемелер мен шектер параметрлерде өзгереді: жұмыс алдында оларды өз келісімдеріңізбен және жергілікті талаптармен салыстырыңыз. Үлгі шағын жеткізу қызметтеріне, интернет-дүкендерге, жеткізуі бар мейрамханалар мен кафелерге, гүл және азық-түлік дүкендеріне, дәріханаларға арналған.

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

Курьерге төлемнің қандай жүйесі ең әділ?

Бұл бизнеске байланысты. Тұрақты мөлшерлеме мен шақырымға үстеме алыс тапсырыстарды ескереді, ал жоспар бонусы мен кешігу айыппұлы жылдамдық пен сапаға ынталандырады. Негізгі талап — ереже жазбаша болып, курьерге алдын ала таныс болуы.

Кешігуді қалай дұрыс өлшеуге болады?

Уәде етілген уақытты тапсырыс қабылданған кезде жазыңыз, нақты уақытты — клиент тапсырысты алған кезде. Жол берілетін уақытты (мысалы, 10 минут) бөлек параметр етіп қойсаңыз, дау азаяды.

Басқа айды қалай есептеймін?

Журналды тазалаудың қажеті жоқ. Есептегі кезеңнің басталу және аяқталу күндерін ауыстырсаңыз, қорытындылар жаңа кезеңге қайта есептеледі.

Төлемнің нөлден төмен болуы мүмкін бе?

Біздің формулада жоқ: MAX(0;…) қолданылады. Айыппұл мөлшерлеме мен үстемеден көп болса да, бір жеткізу үшін төлем нөлден төмен түспейді.