Excelda SUMIF funktsiyasi va bir nechta shartlar bo'yicha yig'indi

Excel ajoyib funktsional dasturdir. Hatto o'rnatilgan funktsiyalar to'plami deyarli har qanday vazifani bajarish uchun etarli. Ko'pchilikka tanish bo'lgan standartlardan tashqari, kam odam eshitganlari ham bor. Ammo shu bilan birga, ular foydali bo'lishni to'xtatmaydi. Ular torroq mutaxassislikka ega va ularga har doim ham ehtiyoj sezilmaydi. Ammo agar siz ular haqida bilsangiz, u holda tanqidiy daqiqada ular juda foydali bo'lishi mumkin.

Bugun biz shunday funktsiyalardan biri haqida gaplashamiz - SUMMESLIMN.

Agar foydalanuvchi ma'lum mezonlarga e'tibor qaratgan holda bir nechta qiymatlarni jamlash vazifasiga duch kelsa, u holda funktsiyadan foydalanish kerak. SUMMESLIMN. Ushbu funktsiyadan foydalanadigan formula ushbu shartlarni argument sifatida qabul qiladi, so'ngra ularga mos keladigan qiymatlarni yig'adi va keyin topilgan qiymat u yozilgan katakka kiritiladi. 

SUMIFS funksiyasi Batafsil tavsif

Funktsiyani ko'rib chiqishdan oldin SUMMESLIMN, avval uning oddiyroq versiyasi nima ekanligini tushunishingiz kerak - SUMMESLI, chunki biz ko'rib chiqayotgan funksiya aynan unga asoslanadi. Har birimiz tez-tez ishlatiladigan ikkita funktsiyani allaqachon bilamiz - SUM (qiymatlar yig'indisini amalga oshiradi) va AGAR (qiymatni belgilangan shartga nisbatan sinab ko'radi).

Agar siz ularni birlashtirsangiz, siz boshqa funktsiyani olasiz - SUMMESLI, bu ma'lumotlarni foydalanuvchi tomonidan belgilangan mezonlarga muvofiq tekshiradi va faqat ushbu mezonlarga javob beradigan raqamlarni jamlaydi. Agar Excelning ingliz tilidagi versiyasi haqida gapiradigan bo'lsak, unda bu funktsiya SUMIF deb ataladi. Oddiy so'zlar bilan aytganda, -language nomi ingliz tilidagining to'g'ridan-to'g'ri tarjimasidir. Bu funksiyadan turli maqsadlarda foydalanish mumkin. Xususan, u muqobil sifatida ishlatilishi mumkin VPR, ya'ni yozing

Funktsiya o'rtasidagi asosiy farq SUMMESLIMN  odatiy funksiyadan SUMMESLI bir qancha mezonlardan foydalaniladi. Uning sintaksisi bir qarashda juda murakkab, ammo diqqat bilan o'rganib chiqqach, bu funktsiyaning mantig'i juda oddiy ekanligi ma'lum bo'ladi. Avval siz ma'lumotlar tekshiriladigan diapazonni tanlashingiz kerak, so'ngra tahlil o'tkaziladigan muvofiqlik shartlarini belgilashingiz kerak. Va bunday operatsiya juda ko'p shartlar uchun amalga oshirilishi mumkin.

Sintaksisning o'zi:

SUMIFS(jamlanma_diapazon, shart_diapazon1, shart1, [shart_diapazoni2, shart2], …)

Tegishli joylarda ma'lum bir holatga mos keladigan hujayralar qatorlarini qo'yish kerak. 

Keling, dalillarni batafsil ko'rib chiqaylik:

1. yig'indisi_oraliq. Ushbu argument, shuningdek, 1-shart va 1-shart oralig'i talab qilinadi. Bu jamlanishi kerak bo'lgan hujayralar to'plami.
2. Shart_diapazon1. Bu holat tekshiriladigan diapazondir. U keyingi argument - Shart 1 bilan bog'langan. Mezonga mos keladigan qiymatlarni yig'ish oldingi argumentda ko'rsatilgan katakchalar ichida amalga oshiriladi.
3. Vaziyat 1. Ushbu argument tekshirilishi kerak bo'lgan mezonlarni belgilaydi. Buni, masalan, quyidagi tarzda o'rnatish mumkin: "> 32".
4. 2-shart diapazoni, 2-shart... Bu erda quyidagi shartlar xuddi shu tarzda o'rnatiladi. Agar bir nechta shartlarni ko'rsatish kerak bo'lsa, 3-shart diapazoni va 3-shart argumentlari qo'shiladi. Sintaksis quyidagi argumentlar uchun bir xil.

Funktsiya 127 juft shart va diapazonni maksimal darajada qayta ishlash imkonini beradi. 

Siz uni bir vaqtning o'zida bir nechta sohalarda qo'llashingiz mumkin (biz faqat bir nechtasini beramiz, ro'yxat aslida uzoqroq):

1. Buxgalteriya hisobi. Masalan, funksiyadan foydalanish yaxshi SUMMESLIMN masalan, ma'lum miqdordan ko'proq sarflash uchun chorak bo'yicha yig'ma hisobotlarni yaratish. Yoki ma'lum bir narx kategoriyasidan bitta mahsulot bo'yicha hisobot yarating.
2. Sotishni boshqarish. Bu erda ham funktsiya juda foydali bo'lishi mumkin. Misol uchun, oldimizda faqat ma'lum bir vaqtda ma'lum bir mijozga sotilgan tovarlarning tannarxini jamlash vazifasi turibdi. Va bunday vaziyatda, funktsiya SUMMESLIMN juda foydali bo'lishi mumkin.
3. Ta'lim. Bugun biz ushbu sohadan ko'proq amaliy misollar keltiramiz. Xususan, siz undan talabalar baholarining xulosasini olish uchun foydalanishingiz mumkin. Siz bitta fan yoki alohida baholar uchun tanlashingiz mumkin. Biror kishi darhol baholashni tanlaydigan bir nechta mezonlarni belgilashi mumkin, bu juda qulay va ko'p vaqtni tejash imkonini beradi.

Ko'rib turganingizdek, ushbu funktsiya uchun ilovalar doirasi juda keng. Ammo bu uning yagona qadriyati emas. Keling, ushbu xususiyatning yana bir qancha afzalliklarini ko'rib chiqaylik:

1. Bir nechta mezonlarni belgilash qobiliyati. Nima uchun bu afzallik? Oddiy funktsiyadan foydalanishingiz mumkin SUMMESLI! Va barchasi qulay bo'lgani uchun. Mezonlarning har biri uchun alohida hisob-kitoblarni amalga oshirishning hojati yo'q. Barcha harakatlar oldindan, hatto undan oldin ham dasturlashtirilishi mumkin. ma'lumotlar jadvali qanday shakllantiriladi. Bu vaqtni tejash imkonini beradi.
2. Avtomatlashtirish. Zamonaviy davr - bu avtomatlashtirish davri. O'z ishini to'g'ri avtomatlashtirishni biladigan odamgina ko'p pul topishi mumkin. Shuning uchun Excel va funksiyani o'zlashtirish qobiliyati SUMMESLIMN ayniqsa, martaba qurmoqchi bo'lgan har qanday odam uchun juda muhimdir. Bitta funktsiyani bilish bir vaqtning o'zida bir nechta harakatlarni bajarishga imkon beradi. Va bu erda biz ushbu xususiyatning keyingi foydasiga o'tamiz.
3. Vaqtni tejash. Faqat bitta funktsiya bir vaqtning o'zida bir nechta vazifani bajarishi tufayli.
4. Oddiylik. Sintaksis o'zining kattaligi tufayli bir qarashda juda og'ir bo'lishiga qaramay, aslida bu funktsiyaning mantig'i juda oddiy. Birinchidan, ma'lumotlar diapazoni, so'ngra ma'lum bir shartga muvofiqligi tekshiriladigan qiymatlar oralig'i tanlanadi. Va, albatta, shartning o'zi ham ko'rsatilishi kerak. Va bir necha marta. Aslida, bu funktsiya faqat bitta mantiqiy konstruktsiyaga asoslangan bo'lib, uni hammaga ma'lum bo'lganidan ko'ra soddalashtiradi VPR u bir xil maqsadlarda ishlatilishi mumkinligiga qaramay, shuningdek, ko'proq mezonlarni hisobga olgan holda. 

SUMIFS funksiyasidan foydalanish xususiyatlari

Ushbu funktsiyadan foydalanishning bir nechta xususiyatlariga e'tibor berishingiz kerak. Avvalo, bu funktsiya matn satrlari yoki nullli diapazonlarni e'tiborsiz qoldiradi, chunki bu ma'lumotlar turlarini arifmetik naqshda qo'shib bo'lmaydi, faqat satrlar kabi birlashtiriladi. Bu funksiya buni amalga oshira olmaydi. Shuningdek, siz quyidagi shartlarga e'tibor berishingiz kerak:

1. Ushbu turdagi qiymatlardan ulardagi qiymatlarni qo'shish uchun hujayralarni tanlash shartlari sifatida foydalanishingiz mumkin: raqamli qiymatlar, mantiqiy ifodalar, hujayra havolalari va boshqalar. 
2. Agar matn, mantiqiy ifodalar yoki matematik belgilar tekshirilayotgan bo'lsa, bunday mezonlar qo'shtirnoq orqali ko'rsatiladi.
3. 255 belgidan ortiq atamalardan foydalanish mumkin emas.
4. Joker belgilar yordamida qiymatlarni tanlash uchun taxminiy mezonlardan foydalanish mumkin. Savol belgisi bitta belgini almashtirish uchun ishlatiladi va ko'paytirish belgisi (yulduzcha) bir nechta belgilarni almashtirish uchun kerak. 
5. Yig'ish oralig'ida joylashgan mantiqiy qiymatlar avtomatik ravishda ularning turiga ko'ra raqamli qiymatlarga aylantiriladi. Shunday qilib, "TRUE" qiymati bittaga, "FALSE" esa nolga aylanadi. 
6. Agar #QIYMAT! katakda xato paydo bo'lsa, bu holat va yig'ish diapazonidagi hujayralar soni boshqacha ekanligini anglatadi. Ushbu argumentlarning o'lchamlari bir xil ekanligiga ishonch hosil qilishingiz kerak. 

SUMIFS funksiyasidan foydalanishga misollar

vazifa SUMMESLIMN birinchi qarashda ko'rinadigan darajada murakkab emas, bu chiqadi. Ammo aniqroq bo'lishi uchun keling, funksiyadan qanday foydalanishingiz mumkinligi haqidagi amaliy misollarni ko'rib chiqaylik SUMMESLIMN. Bu mavzuni chuqur o'rganishni ancha osonlashtiradi.

Shart yig'indisi dinamik diapazoni

Shunday qilib, keling, birinchi misoldan boshlaylik. Aytaylik, bizda talabalarning ma'lum bir fan bo'yicha o'quv dasturini qanday bajarayotgani haqida ma'lumot mavjud bo'lgan jadval mavjud. Baholar to'plami mavjud, ishlash 10 balli tizimda baholanadi. Vazifa familiyasi A harfi bilan boshlangan va minimal balli 5 bo'lgan talabalarning imtihon bahosini topishdir.

Jadval shunday ko'rinadi.

Yuqorida tavsiflangan mezonlar asosida umumiy ballni hisoblashimiz uchun quyidagi formulani qo'llashimiz kerak.

Keling, dalillarni batafsilroq tasvirlab beraylik:

1. C3: C14 - bu bizning umumiy diapazonimiz. Bizning holatda, u shartlar oralig'iga to'g'ri keladi. Undan miqdorni hisoblash uchun ishlatiladigan ballar tanlanadi, lekin faqat bizning mezonimizga to'g'ri keladiganlar.
2. ">5" - bizning birinchi shartimiz.
3. B3: B14 - ikkinchi mezonga mos keladigan ikkinchi yig'indi diapazoni. Ko'ramizki, yig'ish diapazoni bilan tasodif yo'q. Bundan xulosaga kelamizki, yig’indi va shart diapazoni bir xil bo’lishi yoki bir xil bo’lmasligi mumkin. 
4. "A*" ikkinchi diapazon bo'lib, u faqat familiyasi A harfidan boshlanadigan talabalar uchun belgilarni tanlashni belgilaydi. Bizning holatlarimizda yulduzcha istalgan sonli belgilarni bildiradi. 

Hisob-kitoblardan so'ng biz quyidagi jadvalni olamiz.

Ko'rib turganingizdek, formula dinamik diapazonga asoslangan va foydalanuvchi tomonidan belgilangan shartlar asosida qiymatlarni jamladi.

Excelda shart bo'yicha tanlab yig'ish

Endi biz o'tgan chorakda qaysi mamlakatlarga qanday tovarlar jo'natilganligi haqida ma'lumot olmoqchimiz deylik. Shundan so'ng, iyul va avgust oylari uchun jo'natmalardan tushgan umumiy daromadni toping.

Jadvalning o'zi shunday ko'rinadi. 

Yakuniy natijani aniqlash uchun bizga bunday formula kerak.

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

Ushbu formula bo'yicha amalga oshirilgan hisob-kitoblar natijasida biz quyidagi natijaga erishamiz.

Diqqat! Biz faqat ikkita mezondan foydalangan bo'lsak ham, bu formula juda katta ko'rinadi. Agar ma'lumotlar diapazoni bir xil bo'lsa, unda quyida ko'rsatilganidek, formulaning uzunligini sezilarli darajada qisqartirishingiz mumkin.

SUMIFS bir nechta shartlar bo'yicha qiymatlarni yig'ish funktsiyasi

Endi tushuntirish uchun yana bir misol keltiramiz. Bunday holda, jadval avvalgi holatda bo'lgani kabi qoladi. 

Biz quyidagi formuladan foydalanamiz (lekin biz uni massiv formulasi sifatida yozamiz, ya'ni CTRL + SHIFT + ENTER tugmalar birikmasi orqali kiritamiz).

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

Funktsiyadan keyin SUMMESLIMN formulada ko'rsatilgan mezonlar (ya'ni Xitoy va Gruziya mamlakatlari) asosida qiymatlar massivini yig'adi, natijada olingan massiv odatiy funktsiya bilan yig'iladi. SUM, massiv formulasi sifatida yoziladi.

Agar shartlar bir nechta juftlik uchun massiv doimiysi sifatida berilgan bo'lsa, formula noto'g'ri natija beradi.

Keling, jami ko'rsatkichlarni o'z ichiga olgan jadvalni ko'rib chiqaylik.

Ko'rib turganingizdek, biz muvaffaqiyatga erishdik. Siz ham albatta muvaffaqiyatga erishasiz. Bu sohada katta muvaffaqiyat. Bu Excelni o'rganish yo'liga endigina qadam qo'ygan odam tushuna oladigan juda oddiy funksiya. Va biz allaqachon funktsiyani bilamiz SUMMESLIMN buxgalteriya hisobidan tortib, hatto ta'limgacha bo'lgan har qanday faoliyat sohasida samarali bo'lishga imkon beradi. Agar siz yuqorida tavsiflanmagan boshqa sohada martaba qurayotgan bo'lsangiz ham, bu xususiyat baribir pul ishlashingizga yordam beradi. Shuning uchun u qimmatlidir.

Eng muhimi, bu vaqtni tejash imkonini beradi, bu, afsuski, cheklangan manba. Ikki funktsiyani qo'llash uchun bir necha soniya bordek tuyuladi, lekin siz juda ko'p takroriy operatsiyalarni bajarishingiz kerak bo'lganda, bu soniyalar boshqa narsaga sarflanishi mumkin bo'lgan soatlarni qo'shadi. Shuning uchun biz ushbu xususiyatdan foydalanishni mashq qilishingizni tavsiya qilamiz. Bundan tashqari, bu juda oddiy.