Нөхцөлт тооллого (Томьёоны шийдэл)

Бороотой өдрийн мээнд. Оройтсон бороо ч уйдатал орж байна даа. Зуны гурван сар Excel-ээсээ холдсон чинь толгой тархи ажиллахгүй, гар хуруу эвлэхгүй нэг л эвгүй байх чинь ээ. Өнгөрсөн долоо хоногт нэгэн уншигч маань нөхцөлт тооллого хэрхэн хийх тухай асууж байсан юм. Тухайн үед COUNTIF функцээр шийдчих гээд өөртөө итгэлтэй шууд хариулаад байсан чинь би өөрөө тэр функцээр үүссэн асуудлаа шийдэж чаддагүй нэлээд юм боллоо. Анх сонсоход амархан юм шиг санагдаад байсан чинь яг тулаад томьёолох гэтэл тийм ч амархан шийдэгдэх асуудал биш болж хувирсан. Гэхдээ ямар ч байсан асуудлыг томьёолж шийдэж чадсан. Зиак ингээд энэхүү асуудлыг хэрхэн шийдсэн талаараа бичие.

Үүссэн асуудал:

Надад дараах зурагт үзүүлсэн мэдээлэл өгөгдсөн байна.

Энэхүү мэдээллээс сонгогдсон ажилтан тухайн нэг өдөр хэдэн харилцагчтай харьцсан тоог олох шаардлага гарсан байна. Жишээлбэл Түшиг гэдэг ажилтан 1-ний өдөр 4 харилцагчтай уулзсан байна. Гэхдээ 4 харилцагчийн 2 нь нэг харилцагч байгааг та бүхэн анзаараарай. Энэ талаар дараах зурагт үзүүлэв.

Тодруулбал Анар гэдэг харилцагчтай 2 удаа уулзсан байна. Тэгэхээр Түшиг гэдэг ажилтан бодитоор 3 харилцагч буюу Анар, Жаргалтан х/д2, Тамир гэсэн 3 харилцагчтай уулзсан гэсэн үг юм. Тэгэхээр бидний гол зорилго бол тухайн өдөр болон ажилтын мэдээллийг оруулахад автоматаар тухайн ажилтан тухайн өдөр хэдэн харилцагчтай уулзсаныг тодорхойлох явдал юм. Энэ талаар дараах зурагт үзүүлэв.

Зиак үүссэн асуудал тодорхой болсон байх. Одоо тэгвэл асуудлыг хэрхэн шийдвэрлэх талаар бичие.

Асуудал шийдвэрлэсэн нь:

Нэн тэргүүнд бид өгөгдсөн нөхцлийг (Өдөр, ажилтан) хангасан харилцагчийн нэрнүүд өгөгдсөн мэдээллийн хэд дэх мөрнүүдэд байгаа эсэхийг тодорхойлно. Өөрөөр хэлбэл нөхцөл хангасан харилцагч нарын байршлыг тодорхойлно гэсэн үг юм. Үүнийг бид дараах томьёог F5 нүдэд бичих замаар олно.

=IF(A2:A29=F2,IF(B2:B29=F3,MATCH(C2:C29,C2:C29,0)))
(Ctrl+Shift+Enter)

Дээрх томьёоны үр дүн F5 нүдэнд 1 гэж гарах боловч тухайн нүдэнд өгөгдсөн нөхцлийг хангасан харилцагч нарын байршил буюу мөрийн дугаар тодорхойлогдосон байгаа болно. Үүнийг задалж H2:H29 мужид жишээ болгон дараах зурагт харуулая.

Одоо FREQUENCY функцийн тусламжтайгаар харилцагч нарын мөрийн дугаар өгөгдсөн интервалд хэдэн удаа давтагдаж байгааг тодорхойлно. FREQUENCY Функцийн Data Bin буюу давтамж олох интервалыг өгөгдсөн мэдээллийн мөрийн тоогоор үүсгэнэ.

За одоо дээрх томьёоллыг FREQUENCY Функцээр өргөтгөе.

=FREQUENCY(IF(A2:A29=F2,IF(B2:B29=F3,MATCH(C2:C29,C2:C29,0))),ROW(C2:C29)-ROW(C2)+1)
(Ctrl+Shift+Enter)

FREQUENCY функцийн Bins_array аргументыг =ROW(C2:C29)-ROW(C2)+1 томьёогоор үүсгэсэн. Энэхүү томьёон нь өгөгдсөн мэдээллийн нийт мөрийн тоог үүсгэх зориулалттай томьёолол юм. Тодруулбал өгөгдсөн мэдээлэл маань нийт 29 мөртэй тул энэхүү томьёо нь 1-ээс 29 хүртэл дэс дараалсан тоог үүсгэнэ. Энэ томьёооны үр дүнг H2:H29 мужид дараах зурагт үзүүлэв.

FREQUENCY функцээр өргөтгөсөн томьёоллын үр дүн F5 нүдэнд 2 гэж гарсан байна. Энэ нь өгөгдсөн нөхцлийг хангасан эхний мэдээлэл нь =ROW(C2:C29)-ROW(C2)+1 томьёогоор тодорхойлогдсон bin_array-д 2 удаа давтагдсан байна гэдгийг илэрхийлж байна. Жишээ болгож бусад үр дүнг H2;H29 мужид үзүүлэв.

Нэгэнт бид FREQUENCY Функцийн тусламжтайгаар өгөгдсөн нөхцөл хангасан мэдээлэл хэдэн удаа давтагдаж байгааг тодорхойлсон. Одоо тэгвэл 0-ээс их утагтай давтамжийн тоог олчихвол өгөгдсөн нөхцөлд тохирох харицлагчийн тоог давхардаагүй утгаар нь олох боломжтой болно. Иймээс бид:

=FREQUENCY(IF(A2:A29=F2,IF(B2:B29=F3,MATCH(C2:C29,
C2:C29,0))),ROW(C2:C29)-ROW(C2)+1)

Томьёоноос 0-ээс их утгуудыг олно. Үүний тулд дээрх томьёог дараах байдлаар өргөтгөнө.

=–(FREQUENCY(IF(A2:A29=F2,IF(B2:B29=F3,MATCH(C2:C29,
C2:C29,0))),ROW(C2:C29)-ROW(C2)+1)>0)
(Ctrl+Shift+Enter)

Энэхүү томьёоны өргөтгөлийг хийснээр нөхцөл хангасан мэдээллийг 1, нөхцөл хангаагүй мэдээллийг 0 гэж тэмдэглэнэ. Үр дүнг дараах зурагт үзүүлэв. H2:H29 муж.
Шаардлагтай үр дүнг 1, 0 утгуудаар тэмдэглэж чадсан учраас SUM Функцийн тусламжтайгаар бид анхны хүссэн үр дүнгээ авч чадна. Эцсийн томьёо дараах байдлаар бичигдэнэ.

=SUM(–(FREQUENCY(IF(A2:A29=F2,IF(B2:B29=
F3,MATCH(C2:C29,C2:C29,0))),ROW(C2:C29)-ROW(C2)+1)>0))
(Ctrl+Shift+Enter)

Ингээд л бидний асуудал шийдэгдлээ дээ. Одоо шалгаж үзье. 1-ны өдрийн Батболд.Ж нэртэй борлуулагчийн хэдэн харилцагчтай харилцсаныг олоё. Тэгэхээр бид F2 нүдэнд 1 гэсэн утга, F3 нүдэнд Батболд.Ж гэсэн утгыг тус тус оруулна. Үр дүнг сонирхоё.

Үр дүн зөв гарч байна. Асуудал шийдэгдсэн. АМЖИЛТ ХҮСЬЕ та бүхэнд.