SUMMESLE funktsiyasi va SUMMESLESni ikkita mezon bo'yicha belgilaydi

1. Teglar bo'yicha qidirish

Bir jadvalni tasavvur qiling, unda bo'limlar (yoki hisoblar, yoki boshqa biror narsa) satrlar qatorida berilgan.

Hujayralarni kriteriya bo'yicha sarflang
Har bir bo'lim uchun umumiy miqdorni hisoblash kerak. Ko'pchilik buni filtr bilan va hujayralardagi qalamlar bilan yozadi.
SUMMESLI - faqat bitta funktsiyasi bilan osongina va sodda tarzda bajarilishi mumkin.
SUMMESLES (SUMIF) - ma'lum bir shartni qondiradigan hujayralarni sarflaydi (faqat bitta shart belgilanishi mumkin). Jadvallar davrlarga (oylik, har oyda, uchta ustun - Daromadlar uchun ketadigan xarajatlar) va agar barcha davrlar uchun faqatgina daromad, xarajat va farq bo'yicha jami miqdorni hisoblash kerak bo'lsa, bu funksiyadan foydalanish mumkin.

SUMMESLI: KOffice , Criterion , Range_Summing uchun jami uchta argument bor.
= SUMMESLE (A1: A20000; A1; B1: B20000)
= SUMIF (A1: A20000, A1, B1: B20000)

• Range (A1: A20000) - bu mezonlarga mos keladigan masofani bildiradi. Ya'ni Criterion argumenti bilan belgilangan qiymatni qidirish uchun ustun.
• Me'yor (A1) - oralig'ida bo'lishi kerak bo'lgan qiymat (matn yoki raqam, shuningdek sana). "*" Va "?" Joker belgilarni o'z ichiga olishi mumkin. Ya'ni «massa» so'zi paydo bo'lgan qiymatlarni umumlashtirish uchun «* mass *» mezonini belgilash. Shu bilan birga, "ommaviy" so'zi matnning har qanday joyida paydo bo'lishi mumkin, yoki bu so'z bitta hujayradagi bo'lishi mumkin. Va "ommaviy *" ni belgilab, "massa" dan boshlangan barcha qiymatlar to'planadi. "?" - faqat bitta belgi o'rnini egallaydi, ya'ni. "mas? a" ni belgilab, "massa" va "niqob" qiymatini belgilashingiz mumkin.
  Agar mezon hujayradan yozilgan bo'lsa va siz hali ham joker belgilarni ishlatishingiz kerak bo'lsa, unda siz zarur bo'lgan qo'shimchani qo'shib bu xujayra bilan bog'lanishingiz mumkin. Siz "jami" so'zini o'z ichiga olgan qiymatlarni jamlashingiz kerakligini taxmin qilaylik. «Umumiy» so'zi A1 hujayralarida yozilsa, «A» ustunida «jami» so'zi «jami» so'zi, «iyun oyi yakunlari», «mart oyi» so'zlari mavjud bo'lgan turli xil yozma qadriyatlar bo'lishi mumkin. Formuladan keyin shunday bo'lishi kerak:
  = SUMMERS (A1: A20000; "*" & A1 & "*"; B1: B20000)
  "*" & A1 & "*" - belgisi (ampersand) bir nechta qiymatlarni birlashtiradi. Ya'ni natija "* natija *" bo'ladi.
  Formulalar qanday ishlashini bilish uchun, Formula'ni hisoblash vositasini ishlatish yaxshiroq: Formulalarni hisoblash uchun qanday qadamlarni ko'rish kerak
  Mantiqiy va matematik belgilar bilan matnning barcha mezonlari va mezonlari ikki martali tirnoqlarga (= SUMMESLI (A1: A20000; "total"; B1: B20000) qo'shilsin. Agar mezon kritik bo'lsa, takliflar talab qilinmaydi. Agar siz to'g'ridan-to'g'ri savol belgisi yoki yulduzcha topmoqchi bo'lsangiz, oldida bir tilde (~) qo'yish kerak.
  Tilde va uning xususiyatlari haqida ushbu maqolada keltirilgan: Asteriskni qanday almashtirish / olib tashlash / topish mumkin?
• Sum_Range (B1: B20000) (ixtiyoriy argument) - jamlanadigan summalar oralig'ini yoki son qiymatlarini belgilaydi.

Qanday ishlaydi: funktsiya Kriteriya mustaqil o'zgaruvchisi tomonidan belgilangan qiymat uchun oralig'ini izlaydi va agar o'yin topilsa, Range_Amount argumenti bilan ko'rsatilgan ma'lumotlarni jamlaydi. Ya'ni agar bizda A ustunidagi bo'lim nomi va B ustunidagi miqdor mavjud bo'lsa, demak, Rivojlanish boshqarmasi mezon sifatida belgilanadi, B ustunining barcha qiymatlari yig'indisi, natijada Rivojlanish boshqarmasi a ustidagi A. Aslida, SumArrangement Range argumenti bilan bir xil o'lchamli bo'lishi mumkin emas va bu funksiyaning o'zi xatoga yo'l qo'ymaydi. Biroq, jamlash uchun hujayralarni belgilaganda, Range_Amount argumentining yuqori chap xujayrasi summa uchun boshlang'ich hujayra sifatida ishlatiladi va keyinchalik o'lcham va shaklga mos keladigan hujayralar oralig'i argumentiga yig'iladi.

Ba'zi xususiyatlar
Funktsiyaning oxirgi argumenti (Sum_And_Band: B1: B20000) ixtiyoriy. Bu shuni bildiradiki, uni aniqlab bo'lmaydi. Agar uni belgilamasangiz, funktsiya intervalli argument bilan belgilangan qiymatlarni qo'shib qo'yadi. Bu nima uchun. Misol uchun, faqat noldan yuqori bo'lgan raqamlarni yig'ishingiz kerak. Miqdori A ustunida. Keyin funksiya shunday bo'ladi:
= SUMMERS (A1: A20000; "> 0")

Qanday hisobga olinishi kerak: range_summing va diapazoni satrlar soniga teng bo'lishi kerak. Aks holda, noto'g'ri natijani olishingiz mumkin. Optimallashtiradigan bo'lsak, men beradigan formulalarda shunday ko'rinadi: to'plamlar oralig'i va oralig'i bir qatordan boshlanadi va bir xil sonli qatorga ega: A1: A20000; B1: B20000

Ikki yoki undan ortiq mezon bo'yicha jamlangan
Ammo 2 va undan ortiq summalarni aniqlashda nima qilish kerak? Bir bo'limga tegishli bo'lgan va ma'lum bir sana uchun faqatgina summani jamlashingiz kerak deb taxmin qiling. 2007 va keyingi versiyalarida baxtli egalari SUMMESLIMN funktsiyasidan foydalanishlari mumkin:
= SUMMESLIMN ($ C $ 2: $ C $ 50; $ A $ 2: $ A $ 50; $ I $ 3; $ B $ 2: $ B $ 50; $ H8)
$ C $ 2: $ C $ 50 - range_summing. Birinchi dalil yig'ilgan miqdorlarni o'z ichiga olgan hujayra oralig'ini bildiradi.
$ A $ 2: $ A $ 50, $ B $ 2: $ B $ 50 - Range_Criteria. Mezonga ko'ra o'yinni qidirmoqchi bo'lgan hujayra doirasini bildiradi.
$ I $ 3, $ H8 - mezon. Bu erda, SUMMESLI da bo'lgani kabi, joker belgilar * va ? Ruxsat beriladi . va ular xuddi shu tarzda ishlaydi.

Dalillarni belgilashning o'ziga xos jihatlari: birinchi navbatda, mezon mezonlari ko'rsatilgan (ular raqamlangan), keyin qiymat (mezon) to'g'ridan-to'g'ri nuqta-vergul bilan belgilanadi - bu $ 2 $ A $ 50 $ $ I $ 3. Va boshqa hech narsa. Siz avval barcha intervallarni belgilashga urinmasligingiz kerak, va keyin ular uchun mezonlar - funktsiya xato yoki xato keltirishi mumkin yoki zaruriy ma'lumotlarni jamlamaydi.

Barcha shartlar I tamoyiliga ko'ra taqqoslanadi. Bu shuni anglatadiki, agar barcha shartlar bajarilgan bo'lsa. Agar kamida bitta shart bajarilmasa, funktsiya chiziqni o'chiradi va hech narsa qo'shmaydi.
SUMMERSga kelsak, summa va mezon ko'rsatkichlari qatorlar qatorida teng bo'lishi kerak.

Chunki SUMMESLIMN faqat 2007 yilda Excel versiyalarida paydo bo'ldi, keyinchalik bunday hollarda oldingi versiyalarning baxtsiz foydalanuvchilari qanday bo'lishi mumkin? Juda oddiy: boshqa funktsiyadan foydalaning - SUMPRODUCT. Men argumentlarni bo'yalmayman, chunki Ulardan ko'pchiligi bor va ular hammasi qadriyatlar majmuasi. Ushbu funktsiya argumentlar bilan ko'rsatilgan dizilrlarni ko'paytiradi. Ushbu funksiyadan foydalanishning umumiy printsipini bir nechta sharoitlarda ma'lumotlarni umumlashtirish uchun tasvirlashga harakat qilaman.
Summa masalasini bir necha mezonlar bo'yicha hal qilish uchun, funktsiya quyidagi tarzda ko'rinadi:
SUMPRODUCT ($ A $ 2: $ A $ 50 = $ I $ 3) * ($ B $ 2: $ B $ 50 = H5); $ C $ 2: $ C $ 50)
$ A $ 2: $ A $ 50 - sana oralig'i. $ I $ 3 - ma'lumotlarni yig'ish kerak bo'lgan kriteriya sanasi.
$ B $ 2: $ B $ 50 - bo'limlar nomlari. H5 - bo'limning nomi, undagi ma'lumotlarni yig'ish kerak.
$ C $ 2: $ C $ 50 - miqdori bilan oralig'i.

Biz mantiqni tahlil qilamiz, chunki ko'pchilikka bu funktsiyani ko'rib chiqish orqali to'liq noma'lum bo'ladi. Agar faqatgina ushbu ilovada tavsif etilmasa. Keyinchalik katta o'qilishi uchun intervallarni o'lchamini kamaytiring:
= SUMPRODUCT ($ A $ 2: $ A $ 5 = $ I $ 3) * ($ B $ 2: $ B $ 5 = H5); $ C $ 2: $ C $ 5)
Masalan, ($ A $ 2: $ A $ 5 = $ I $ 3) va ($ B $ 2: $ B $ 5 = H5) ifodalari mantiqiy va mantiqiy FALSE va TRUE arraysini qaytaradi. $ A $ 2 oralig'idagi hujayra $ A $ 5 hujayrasi $ I $ 3 va $ B $ 2 oralig'idagi hujayra qiymati bilan teng bo'lsa, $ B $ 5 hujayra H5 uyasi qiymatiga teng. Ya'ni bizda quyidagilar mavjud:
= SUMPRODUCT ({FALSE; TRUE; TRUE; FALSE} * {FALSE; FALSE; TRUE; FALSE; $ C $ 2: $ C $ 50)
Ko'rib turganingizdek, birinchi qatorda vaziyat uchun ikkita o'yin bor, ikkinchisi esa. Bundan tashqari, bu ikkita massiv ko'paytiriladi (buning uchun ko'paytirish belgisi (*) mas'ul). Ko'paytirish amalga oshirilganda, ketma-ketliklarni FALSE va ROST qiymatlari o'z navbatida 0 va 1 (0); Ma'lumki, nolga ko'paytirilganda nolga erishamiz. Natijada bitta qator:
= SUMPRODUCT ({0; 0; 1; 0}; $ C $ 2: $ C $ 50)
Keyin {0; 0; 1; 0} qatori $ C $ 2 oralig'idagi sonlar qatoriga ko'paytiriladi: $ C $ 50:
= SUMPRODUCT ({0; 0; 1; 0}; {10; 20; 30; 40})
Natijada, biz 30 yoshda bo'lamiz. Bizga kerak bo'lgan narsa - faqat mezonga javob beruvchi miqdorni olamiz. Agar mezonni qondiradigan bir nechta sum bo'lsa, unda ular umumlashtiriladi.

SUMMYROIZVning afzalligi
Argumentlar ko'paytirish belgisi o'rniga ortiqcha belgisi bo'lsa:
($ A $ 2: $ A $ 5 = $ I $ 3) + ($ B $ 2: $ B $ 5 = H5)
shartlar OR printsipi bo'yicha taqqoslanadi: ya'ni, $ A $ 2: $ A $ 5 hujayra qiymati $ I $ 3 yoki $ B $ 2 oralig'idagi hujayra qiymatiga teng bo'lsa: $ B $ 5 hujayra qiymati H5 ga teng bo'lsa, jami mablag'lar summa hisoblanadi.
Bu SUMMESLIMN orqali SUMMPRODUCTning afzalligi. SUMMESLIMN qiymatlari OR tamoyiliga ko'ra faqatgina AND printsipiga (barcha shartlar bajarilishi kerak) muvofiq summalarni keltira olmaydi.

Kamchiliklari
SUMPRODUCT joker belgilarni * va. To'g'ri ishlatish mumkin, lekin ular maxsus belgilar sifatida emas, balki yulduzcha va savol belgisi sifatida qabul qilinadi. O'ylaymanki, bu katta salbiy holat. Va buning oldini olish mumkin bo'lsa-da, men SUMPRODUCT ichidagi boshqa funktsiyalarni ishlataman - bu funksiya joker belgilarni ishlatishi mumkin.

Misol tariqasida yuqorida yozilgan narsalarni yaxshiroq tushunish uchun bir necha misollarni topasiz.

Misolni yuklab oling

Bir necha mezon bo'yicha miqdori (41.5 Kb, 10.477 Yuklashlar)

Shuningdek qarang:
To'ldirish rangidagi hujayralarni jamlash
Hujayralarning shrift rangi bo'yicha jamlanganligi
Hujayra formati bo'yicha hujayralarni jamlash
To'ldirish rangidagi hujayralar miqdorini hisoblang
Shrift rangi bo'yicha hujayralar miqdorini hisoblang
Vaziyatga qarab, bir nechta varaqlardan ma'lumotlarni jamlash

: "Textpositionmarginstatic": 0, "textpositionminginstatic": "bottomleft", "textpositionmarginleft": 24, "matnli pozitsiyani": "statik", "textpositionstatic": " "textpositionmarginright": 24, "textpositionmarginbottom": 24, "texteffect": "slide", "texteffecteasing": "easeOutCubic", "texteffectduration": 600, "texteffectslidedirection": "chap", "texteffectslidedistance" : 30, "texteffectdelay": 500, "texteffectseparate": noto'g'ri, "texteffect1": "slide", "texteffectslidedirectction1": "o'ng", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 "texteffectdelay1": 1000, "texteffect2": "slide", "texteffectslidedirectction2": "o'ng", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500, height = "100"; "textcss": "displey: blok; padding: 12px; text-align: left;", "textbgcss": "displey: blok; joylashuv: mutlaq; tepada: 0px; chap: 0px; kenglik: 100% fon rang: # 333333; shaffoflik: 0,6; filtri: a lpha (opacity = 60); "," titlecss ":" displey: blok; Lavozim: nisbiy; shrift: bold 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; rang: #fff; "," descriptioncss ":" displey: blok; Lavozim: nisbiy; shrift: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; rang: #fff; margin-top: 8px; "," buttoncss ":" displey: blok; Lavozim: nisbiy; "shablonlar hajmi: 12px;", "descriptioncssresponsive": "displey: none: important;", "buttoncssresponsive": 640, "ttteffectresponsive"; "", "addgooglefonts": noto'g'ri, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Teglar bo'yicha qidirish