Асхана тапсырысы: AVERAGE, COUNTIF және сұрыптау қателері
Мектеп асханасында 30 оқушыға арналған алдын ала тапсырыс журналі Excel кестесінде жүргізіледі. Бағаналар: тегі, сыныбы, тапсырыс сомасы (теңге), төлем түрі. Наурыз мерекесінде 30 оқушының 7-і ауырып, тапсырыс бермеді, бірақ оператор олардың сомасын нөлге тең етіп толтырды. =AVERAGE(C2:C31) формуласы 393 теңге көрсетті, ал асхана меңгерушісі күткен орташа сома 512 теңге. «500 теңгеден жоғары тапсырыс берген оқушылар саны» үшін =COUNTIF(C2:C31;">500") формуласы 9 деп шықты, бірақ қолмен санағанда 11 болды, себебі екі оқушының сомасы дәл 500 теңге. Кестені сұрыптағаннан кейін тағы бір қате шықты: жеңілдік бағанасындағы =C2*0,1 формуласы барлық жолға дұрыс көшпей қалды, ал кейбір жолдарда нәтиже бос. Директор есепті ертеңгі жиынға дайындауды талап етті, ал меңгеруші қай көрсеткіштің дұрыс екенін білмей отыр. Кестеде қосымша беттер де бар: «Төлем түрі» және «Сынып бойынша қорытынды».
1. 393 пен 512 арасындағы айырмашылықтың себебін түсіндіріп, дұрыс формуланы жазыңыз.
Read the worked answer
Себеп: AVERAGE бос ұяшықтарды елемейді, бірақ нөлді нақты мән ретінде қосады. Оператор тапсырыс бермеген 7 оқушыға 0 жазған, сондықтан олар орташа мәннің бөлгішіне кіріп кетті.
Тексеру: 23 оқушының орташа сомасы 512 болса, жалпы сома 23 · 512 = 11 776 теңге. Оны 30-ға бөлсек: 11 776/30 ≈ 392,5 ≈ 393 - дәл формуланың нәтижесі. Яғни қате есептеуде емес, бөлгішті таңдауда.
Дұрыс нұсқалар:
- =AVERAGEIF(C2:C31;">0") → 512 (тек тапсырыс бергендер);
- =SUM(C2:C31)/COUNTIF(C2:C31;">0") → сол нәтиже;
- ең дұрысы - бос ұяшық қалдыру (0 емес), сонда AVERAGE өзі дұрыс есептейді.
Маңызды шешім: қай көрсеткіш керек екенін анықтау қажет. Асхана үшін «бір тапсырыстың орташа құны» = 512 теңге; «бір оқушыға шаққандағы орташа түсім» = 393 теңге. Екеуі де дұрыс, бірақ олар әртүрлі сұраққа жауап береді - есепте оның атауын нақты жазу керек.
2. COUNTIF нәтижесіндегі 9 бен 11-дің айырмашылығын түсіндіріп, дұрыс өрнекті жазыңыз.
Read the worked answer
Себеп: =COUNTIF(C2:C31;">500") өрнегіндегі ">" таңбасы қатаң үлкенді білдіреді, яғни 500-ге тең екі оқушы есепке кірмейді. Сұраныс «500 теңгеден жоғары» емес, «500 және одан жоғары» болғандықтан, 9 саны талапқа сай емес.
Дұрыс өрнек: =COUNTIF(C2:C31;">=500") → 11.
Негізгі ережелер:
- COUNTIF критерийі мәтін түрінде беріледі; оператор мен сан бір тырнақшаның ішінде тұрады: ">=500".
- Егер шек жеке ұяшықта болса (мысалы, E1 = 500), онда амал мен ұяшықты & арқылы біріктіру керек: =COUNTIF(C2:C31;">="&E1). Тек =COUNTIF(C2:C31;E1) деп жазса, ол «дәл 500-ге тең» дегенді білдіреді.
- Екі шартты санау үшін COUNTIFS қолданылады, мысалы сынып бойынша: =COUNTIFS(B2:B31;"11А";C2:C31;">=500").
Қорытынды: 9 - қатаң теңсіздіктің нәтижесі, 11 - талапқа сәйкес дұрыс жауап. Есепте формуланы да, оның мағынасын да түсіндіру керек.
3. Сұрыптаудан кейін пайда болған жеңілдік қатесінің себебін анықтап, оны болдырмаудың екі жолын ұсыныңыз.
Read the worked answer
Себеп: =C2*0,1 формуласы тек бірінші жолда жазылып, төменге толық көшірілмеген (немесе сұрыптау кезінде қосылған жолдар формуласыз қалған). Сұрыптау жолдардың ретін өзгертеді, бірақ формуласы жоқ ұяшықтарға формула қоспайды; сондықтан кейбір жолдарда нәтиже бос қалады. Сонымен қатар, егер жеңілдік мөлшерлемесі жеке ұяшықта тұрса, онда салыстырмалы сілтеме сұрыптаудан кейін басқа ұяшыққа «жылжып» кетуі мүмкін.
Тексеру тәсілі: D бағанының қосындысын қолмен есептелген мәнмен салыстыру; бос ұяшықтар санын =COUNTBLANK(D2:D31) арқылы тексеру.
Болдырмаудың екі жолы:
- Кестені «Table» форматына айналдыру (Ctrl+T): бағанға формула бір рет жазылады, ол барлық жолға автоматты түрде қолданылады, жаңа жол қосылса да көшіріледі.
- Мөлшерлемені бөлек ұяшыққа шығарып, абсолютті сілтеме қолдану: =C2*$F$1, содан кейін формуланы соңғы жолға дейін толтыру (Ctrl+D) және сұрыптау алдында барлық жолда формула бар екенін тексеру.
Қорытынды: деректерді сұрыптау алдында формулалардың толықтығын тексеру - есептің дұрыстығының міндетті шарты.