Excel-ийн SUMIF функц ба олон нөхцөлөөр нийлдэг

Excel бол гайхалтай ажиллагаатай програм юм. Бүр суурилагдсан функцийн багц нь бараг бүх ажлыг гүйцэтгэхэд хангалттай. Олон хүнд танил болсон стандартаас гадна цөөхөн хүн сонссон хүмүүс бас байдаг. Гэхдээ үүнтэй зэрэгцэн тэд ашиг тусаа өгөхөө больдоггүй. Тэд нарийхан мэргэшилтэй бөгөөд тэдний хэрэгцээ үргэлж байдаггүй. Гэхдээ хэрэв та тэдний талаар мэддэг бол эгзэгтэй үед тэд маш их хэрэгтэй байж болно.

Өнөөдөр бид ийм функцүүдийн нэгний талаар ярих болно - SUMMESLIMN.

Хэрэв хэрэглэгч тодорхой шалгуурт анхаарлаа төвлөрүүлж, хэд хэдэн утгыг нэгтгэх даалгавартай тулгарвал функцийг ашиглах шаардлагатай болно. SUMMESLIMN. Энэ функцийг ашигладаг томъёо нь эдгээр нөхцлийг аргумент болгон авч, дараа нь тэдгээрт тохирсон утгуудыг нэгтгэж, дараа нь олсон утгыг бичсэн нүдэнд оруулна. 

SUMIFS функцийн дэлгэрэнгүй тайлбар

Функцийг авч үзэхээсээ өмнө SUMMESLIMN, та эхлээд түүний энгийн хувилбар гэж юу болохыг ойлгох хэрэгтэй. СУММЕСЛИ, учир нь бидний авч үзэж буй функц үүн дээр суурилдаг. Бидний хүн нэг бүр ихэвчлэн ашиглагддаг хоёр функцийг аль хэдийн мэддэг болсон байх. SUM (утгын нийлбэрийг гүйцэтгэдэг) ба ХЭРЭВ (тодорхой нөхцөлийн эсрэг утгыг шалгадаг).

Хэрэв та тэдгээрийг нэгтгэвэл өөр функцийг авах болно - СУММЕСЛИ, энэ нь өгөгдлийг хэрэглэгчийн тодорхойлсон шалгуурын дагуу шалгаж, зөвхөн тэдгээр шалгуурт нийцсэн тоонуудыг нэгтгэдэг. Хэрэв бид Excel-ийн англи хувилбарын талаар ярих юм бол энэ функцийг SUMIF гэж нэрлэдэг. Энгийнээр хэлбэл, -language нэр нь англи хэл дээрх нэрний шууд орчуулга юм. Энэ функцийг янз бүрийн зорилгоор ашиглаж болно. Ялангуяа үүнийг өөр хувилбар болгон ашиглаж болно VPR, өөрөөр хэлбэл бичих

Функцийн гол ялгаа SUMMESLIMN  ердийн функцээс СУММЕСЛИ хэд хэдэн шалгуурыг ашигладаг. Түүний синтакс нь эхлээд харахад нэлээд төвөгтэй боловч сайтар судалж үзэхэд энэ функцийн логик нь маш энгийн болох нь харагдаж байна. Эхлээд та өгөгдлийг шалгах мужийг сонгох хэрэгтэй бөгөөд дараа нь дүн шинжилгээ хийх нөхцлийг бүрдүүлэх хэрэгтэй. Мөн ийм үйл ажиллагааг нэлээд олон тооны нөхцөлд хийж болно.

Синтакс нь өөрөө:

SUMIFS(нийлбэрийн_муж, нөхцөл_муж1, нөхцөл1, [нөхцөл_муж2, нөхцөл2], …)

Тохиромжтой газруудад тодорхой тохиолдолд тохирох эсийн массивыг байрлуулах шаардлагатай. 

Аргументуудыг илүү нарийвчлан авч үзье:

1. Нийлбэрийн_муж. Энэ аргумент, түүнчлэн 1 ба нөхцөл 1-ийн муж шаардлагатай. Энэ бол нэгтгэх шаардлагатай эсийн багц юм.
2. Нөхцөл_муж1. Энэ бол нөхцөлийг шалгах муж юм. Үүнийг дараагийн аргументтай хослуулсан - Нөхцөл1. Шалгуурт тохирсон утгуудын нийлбэрийг өмнөх аргументад заасан нүднүүдийн дотор гүйцэтгэнэ.
3. Нөхцөл 1. Энэ аргумент нь шалгах шалгуурыг тодорхойлдог. Үүнийг жишээлбэл, "> 32" гэсэн байдлаар тохируулж болно.
4. Нөхцөлийн хүрээ 2, Нөхцөл 2... Энд дараах нөхцлүүдийг ижил аргаар тохируулсан. Хэрэв хэд хэдэн нөхцөлийг зааж өгөх шаардлагатай бол 3-р нөхцөл ба 3-р нөхцөлийн аргументуудыг нэмнэ. Дараах аргументуудын синтакс ижил байна.

Энэ функц нь 127 хос нөхцөл, мужийг хамгийн их боловсруулах боломжийг олгодог. 

Та үүнийг нэг дор хэд хэдэн чиглэлээр ашиглаж болно (бид цөөн хэдэн зүйлийг өгөх болно, жагсаалт нь үнэндээ илүү урт байна):

1. Нягтлан бодох бүртгэл. Жишээлбэл, функцийг ашиглах нь сайн хэрэг SUMMESLIMN тухайлбал тодорхой хэмжээний зарцуулалтад улирал бүр хураангуй тайлан гаргах. Эсвэл тодорхой үнийн категориас нэг бүтээгдэхүүний тайланг үүсгэ.
2. Худалдааны менежмент. Энд бас функц нь маш хэрэгтэй байж болох юм. Жишээлбэл, бид зөвхөн тодорхой хэрэглэгчдэд тодорхой хугацаанд борлуулсан барааны өртгийг нэгтгэн дүгнэх даалгавартай тулгарч байна. Мөн ийм нөхцөл байдалд, функц SUMMESLIMN маш их тус болох болно.
3. Боловсрол. Бид өнөөдөр энэ талбараас илүү практик жишээг өгөх болно. Ялангуяа та үүнийг ашиглан оюутны дүнгийн хураангуйг авах боломжтой. Та нэг хичээлээр эсвэл ганцаарчилсан дүнгээр сонгож болно. Хүн үнэлгээг сонгох хэд хэдэн шалгуурыг нэн даруй тогтоох боломжтой бөгөөд энэ нь маш тохиромжтой бөгөөд маш их цаг хэмнэх болно.

Таны харж байгаагаар энэ функцийн хэрэглээний хүрээ маш өргөн байна. Гэхдээ энэ нь түүний цорын ганц гавьяа биш юм. Энэ функцэд байгаа хэд хэдэн давуу талыг харцгаая:

1. Олон шалгуур тавих чадвар. Энэ яагаад давуу тал вэ? Та ердийн функцийг ашиглаж болно СУММЕСЛИ! Мөн бүх зүйл тохиромжтой учраас. Шалгуур тус бүрийн хувьд тусдаа тооцоолол хийх шаардлагагүй. Бүх үйлдлийг урьдчилан, бүр өмнө нь програмчилж болно. өгөгдлийн хүснэгтийг хэрхэн бүрдүүлэх. Энэ бол маш сайн цаг хэмнэгч юм.
2. Автоматжуулалт. Орчин үеийн эрин үе бол автоматжуулалтын эрин үе юм. Ажлаа хэрхэн зөв автоматжуулахыг мэддэг хүн л ихийг олж чадна. Тийм ч учраас Excel болон функцийг эзэмших чадвартай SUMMESLIMN ялангуяа карьераа бүтээхийг хүсч буй аливаа хүнд маш чухал юм. Нэг функцийг мэдэх нь хэд хэдэн үйлдлийг нэгэн зэрэг гүйцэтгэх боломжийг олгодог. Энд бид энэ функцын дараагийн ашиг тус руу шилжиж байна.
3. Цаг хэмнэх. Зөвхөн нэг функц нь хэд хэдэн ажлыг нэгэн зэрэг гүйцэтгэдэгтэй холбоотой юм.
4. Энгийн байдал. Хэдийгээр том хэмжээтэй тул синтакс нь эхлээд харахад нэлээд хүнд байдаг ч үнэн хэрэгтээ энэ функцын логик нь маш энгийн юм. Эхлээд өгөгдлийн хүрээ сонгогдоод дараа нь тодорхой нөхцөлтэй нийцэж байгаа эсэхийг шалгана. Мэдээжийн хэрэг нөхцөлийг өөрөө зааж өгөх ёстой. Тэгээд хэд хэдэн удаа. Үнэн хэрэгтээ энэ функц нь зөвхөн нэг логик бүтэц дээр суурилдаг бөгөөд энэ нь үүнийг сайн мэддэгээс илүү хялбар болгодог VPR олон тооны шалгуурыг харгалзан ижил зорилгоор ашиглах боломжтой хэдий ч. 

SUMIFS функцийг ашиглах онцлог

Энэ функцийг ашиглахад анхаарах хэд хэдэн онцлог шинж чанарууд байдаг. Юуны өмнө, эдгээр өгөгдлийн төрлүүдийг арифметик загварт нэмж оруулах боломжгүй, зөвхөн мөр шиг холбодог тул энэ функц нь текстийн мөр эсвэл тэгтэй мужуудыг үл тоомсорлодог. Энэ функц үүнийг хийх боломжгүй. Мөн та дараахь нөхцөл байдалд анхаарлаа хандуулах хэрэгтэй.

1. Та эдгээр төрлийн утгыг нүднүүдийг сонгох нөхцөл болгон ашиглаж, тэдгээрт агуулагдсан утгуудыг нэмж болно: тоон утгууд, логик илэрхийлэл, нүдний лавлагаа гэх мэт. 
2. Хэрэв текст, логик илэрхийлэл эсвэл математикийн тэмдгийг шалгаж байгаа бол ийм шалгуурыг хашилтаар зааж өгнө.
3. 255 тэмдэгтээс урт нэр томъёог ашиглах боломжгүй.
4. Оролцогч тэмдэгтүүдийг ашиглан утгыг сонгохдоо ойролцоо шалгуурыг ашиглах боломжтой. Асуултын тэмдэг нь нэг тэмдэгтийг орлуулахад хэрэглэгддэг ба үржүүлэх тэмдэг (од) нь олон тэмдэгтийг орлуулахад шаардлагатай. 
5. Нийлбэрийн мужид байгаа логик утгууд нь төрлөөс нь хамааран автоматаар тоон утга руу хөрвүүлэгддэг. Тиймээс "ҮНЭН" утга нь нэг болж, "ХУДАЛ" нь тэг болж хувирна. 
6. Хэрэв #VALUE! Нүдэнд алдаа гарч ирвэл нөхцөл ба нийлбэрийн муж дахь нүдний тоо өөр байна гэсэн үг. Та эдгээр аргументуудын хэмжээ ижил байгаа эсэхийг шалгах хэрэгтэй. 

SUMIFS функцийг ашиглах жишээ

Чиг үүрэг SUMMESLIMN Энэ нь эхлээд харахад тийм ч төвөгтэй биш юм. Гэхдээ илүү тодорхой болгохын тулд функцийг хэрхэн ашиглах талаар практик жишээг харцгаая SUMMESLIMN. Энэ нь сэдвийг судлахад илүү хялбар болно.

Нөхцөл байдлын нийлбэрийн динамик муж

Тиймээс эхний жишээнээс эхэлье. Оюутнууд тодорхой хичээлийн хөтөлбөрийг хэрхэн даван туулж байгаа талаарх мэдээллийг агуулсан хүснэгттэй гэж үзье. Дүнгийн багц байдаг, гүйцэтгэлийг 10 онооны системээр үнэлдэг. Овог нь А үсгээр эхэлсэн, хамгийн бага оноо нь 5 байгаа оюутнуудын шалгалтын дүнг олох даалгавар юм.

Хүснэгт иймэрхүү харагдаж байна.

Дээр дурдсан шалгуурын дагуу нийт оноог тооцоолохын тулд бид дараах томъёог ашиглах шаардлагатай.

Аргументуудыг илүү дэлгэрэнгүй тайлбарлая:

1. C3:C14 бол бидний нийлбэр хүрээ юм. Манай тохиолдолд энэ нь нөхцөл байдлын мужтай давхцдаг. Үүнээс дүнг тооцоход ашигладаг оноог сонгох болно, гэхдээ зөвхөн бидний шалгуурт багтах оноо.
2. “>5” бол бидний эхний нөхцөл.
3. B3:B14 нь хоёр дахь шалгуурт нийцүүлэн боловсруулсан хоёр дахь нийлбэр муж юм. Нийлбэрийн хүрээтэй давхцах зүйл байхгүй гэдгийг бид харж байна. Эндээс бид нийлбэрийн хүрээ болон нөхцөлийн хүрээ ижил байж болно, үгүй ​​ч байж магадгүй гэж дүгнэж байна. 
4. “A*” нь хоёр дахь муж бөгөөд зөвхөн овог нь А үсгээр эхэлсэн оюутнуудад оноог сонгохыг зааж өгдөг. Манай тохиолдолд од гэдэг нь дурын тооны тэмдэгтийг хэлнэ. 

Тооцооллын дараа бид дараах хүснэгтийг авна.

Таны харж байгаагаар томъёо нь динамик муж болон хэрэглэгчийн тодорхойлсон нөхцөл дээр үндэслэн утгуудыг нэгтгэсэн болно.

Excel дээрх нөхцөлөөр сонгон нийлбэр

Одоо бид өнгөрсөн улиралд ямар улс руу ямар бараа ачуулсан талаар мэдээлэл авмаар байна гэж бодъё. Үүний дараа XNUMX, XNUMX-р сарын тээвэрлэлтийн нийт орлогыг ол.

Хүснэгт нь өөрөө иймэрхүү харагдаж байна. 

Эцсийн үр дүнг тодорхойлохын тулд бидэнд ийм томъёо хэрэгтэй.

=(СУММЕСЛИМН(D2:D14;A2:A14;»=июнь»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)+(СУММЕСЛИМН(D2:D14;A2:A14;»=август»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)))

Энэ томъёогоор хийсэн тооцооллын үр дүнд бид дараах үр дүнг авна.

Анхаар! Хэдийгээр бид хоёр шалгуурыг ашигласан ч энэ томьёо нэлээд том харагдаж байна. Хэрэв өгөгдлийн хүрээ ижил байвал доор үзүүлсэн шиг томъёоны уртыг мэдэгдэхүйц багасгаж болно.

SUMIFS нь олон нөхцөлийн утгыг нэгтгэх функцтэй

Одоо тайлбарлахын тулд өөр нэг жишээ хэлье. Энэ тохиолдолд хүснэгт нь өмнөх тохиолдолтой адил хэвээр байна. 

Бид дараах томъёог ашигладаг (гэхдээ бид үүнийг массивын томъёогоор бичдэг, өөрөөр хэлбэл CTRL + SHIFT + ENTER товчлуурын хослолоор оруулдаг).

=СУММ(СУММЕСЛИМН(D2:D14;B2:B14;»Товар_1″;C2:C14;{«Китай»;»Грузия»}))

Функцийн дараа SUMMESLIMN томъёонд заасан шалгуурын дагуу утгын массивыг нэгтгэх болно (өөрөөр хэлбэл Хятад, Гүрж улсууд), үүссэн массивыг ердийн функцээр нэгтгэнэ. SUM, массивын томъёогоор бичигдсэн байдаг.

Хэрэв нөхцөлүүдийг нэгээс олон хосын массивын тогтмол хэлбэрээр дамжуулсан бол томъёо нь буруу үр дүнг өгнө.

Одоо нийт дүнг агуулсан хүснэгтийг харцгаая.

Таны харж байгаагаар бид амжилтанд хүрсэн. Та ч бас амжилтанд хүрэх нь гарцаагүй. Энэ салбарт том амжилт. Энэ бол Excel сурах замд дөнгөж хөл тавьсан хүн ойлгох маш энгийн функц юм. Мөн бид аль хэдийн функц гэдгийг мэддэг SUMMESLIMN Нягтлан бодох бүртгэлээс эхлээд боловсрол хүртэл аль ч салбарт үр дүнтэй байх боломжийг танд олгоно. Хэрэв та дээр дурдаагүй өөр чиглэлээр карьераа эхлүүлж байсан ч энэ функц нь мөнгө олоход тань туслах болно. Тийм ч учраас тэр үнэ цэнэтэй юм.

Хамгийн чухал нь цаг хугацаа хэмнэх боломжийг олгодог бөгөөд энэ нь харамсалтай нь хязгаарлагдмал нөөц юм. Хоёр функцийг хэрэгжүүлэхэд хэдхэн секунд байгаа мэт санагдах боловч олон тооны давтагдах үйлдлүүдийг хийх шаардлагатай бол эдгээр секундууд нь өөр зүйлд зарцуулж болох цагийг нэмдэг. Тиймээс бид танд энэ функцийг ашиглах дасгал хийхийг зөвлөж байна. Үүнээс гадна, энэ нь гайхалтай энгийн юм.