Štejte število vrstic, ki vsebujejo določene vrednosti v Excelu
Morda nam bo preprosto šteti število celic s posebno vrednostjo na Excelovem delovnem listu. Vendar pa je pridobivanje števila vrstic, ki vsebujejo določene vrednosti, lahko precej zapleteno. V tem primeru vam lahko pomaga bolj zapletena formula, ki temelji na funkcijah SUM, MMULT, TRANSPOSE in COLUMN. Ta vadnica bo govorila o tem, kako ustvariti to formulo za reševanje tega dela v Excelu.
Preštejte število vrstic, ki vsebujejo določene vrednosti
Na delovnem listu imate na primer obseg vrednosti, zdaj pa morate šteti število vrstic z dano vrednostjo »300«, kot je prikazano na spodnjem posnetku zaslona:
Za pridobitev števila vrstic, ki vsebujejo posebne vrednosti, je splošna skladnja:
Array formula, should press Ctrl + Shift + Enter keys together.
- data: Obseg celic za preverjanje, ali vsebujejo določeno vrednost;
- X: Posebna vrednost, ki jo uporabljate za štetje vrstic.
1. Prosimo, vnesite ali kopirajte spodnjo formulo v prazno celico, kamor želite dati rezultat:
2. Nato pritisnite Ctrl + Shift + Enter da dobite pravilen rezultat, glejte posnetek zaslona:
Pojasnilo formule:
=SUM(--(MMULT(--($A$2:$C$12=300),TRANSPOSE(COLUMN($A$2:$C$12)))>0))
- -$ A $ 2: $ C $ 12 = 300: Ta izraz preveri, ali v območju A300: C2 obstaja vrednost "12", in ustvari rezultat matrike TRUE in False. Dvojni negativni znak se uporablja za pretvorbo vrednosti TRUE v 1s in FALSE v 0s. Tako boste dobili rezultat tako: {0,0,0; 1,0,0; 0,0,0; 0,1,1; 0,0,0; 0,1,0; 0,0,0 , 1,0,0; 0,0,1; 0,0,0; 1,1,1; 11}. Niz, sestavljen iz 3 vrstic in 1 stolpcev, bo deloval kot matrikaXNUMX v funkciji MMULT.
- PREVOZ (STOLPEC ($ A $ 2: $ C $ 12)): Funkcija COLUMN tukaj se uporablja za pridobitev številke stolpca obsega A2: C12, vrne matriko s 3 stolpci, kot je ta: {1,2,3}. In potem funkcija TRANSPOSE zamenja to matriko v 3-vrstično matriko {1; 2; 3}, ki deluje kot matrika2 znotraj funkcije MMULT.
- MMULT (-($ A $ 2: $ C $ 12 = "Joanna"), PREVOZ (STOLPEC ($ A $ 2: $ C $ 12))): Ta funkcija MMULT vrne matrični produkt zgornjih dveh nizov, rezultat boste dobili tako: {0; 1; 0; 5; 0; 2; 0; 1; 3; 0; 6}.
- SUM(--(MMULT(--($A$2:$C$12="Joanna"),TRANSPOSE(COLUMN($A$2:$C$12)))>0))= SUM(--{0;1;0;5;0;2;0;1;3;0;6}>0): Najprej preverite, ali so vrednosti v matriki večje od 0: Če je vrednost večja od 0, se prikaže TRUE; če je manj kot 0, se prikaže FALSE. In potem dvojni negativni znak prisili TRUE in FALSE na 1s in 0s, tako da dobite to: SUM ({0; 1; 0; 1; 0; 1; 0; 1; 1; 0; 1}). Na koncu funkcija SUM sešteje vrednosti v matriki, da vrne rezultat: 6.
Nasvet:
Če morate na delovnem listu prešteti število vrstic, ki vsebujejo določeno besedilo, uporabite spodnjo formulo in ne pozabite pritisniti Ctrl + Shift + Enter skupaj, da dobite skupno število:
Uporabljena relativna funkcija:
- VSEM:
- Funkcija Excel SUM vrne vsoto predloženih vrednosti.
- MMULT:
- Excel MMULT funkcija vrne matrični produkt dveh nizov.
- PREVOZ:
- Funkcija TRANSPOSE bo vrnila matriko v novi usmeritvi, ki temelji na določenem obsegu celic.
- STOLPEC:
- Funkcija COLUMN vrne številko stolpca, v kateri se pojavi formula, ali vrne številko stolpca dane reference.
Več člankov:
- Preštejte vrstice, če izpolnjujejo notranja merila
- Recimo, da imate poročilo o prodaji izdelkov v tem in lanskem letu, zdaj pa boste morda morali prešteti izdelke, pri katerih je prodaja v tem letu večja kot lani, ali pa je prodaja v tem letu manjša kot lani, kot je prikazano spodaj prikazan posnetek zaslona. Običajno lahko dodate stolpec za pomoč za izračun razlike v prodaji med dvema letoma in nato uporabite COUNTIF, da dobite rezultat. Toda v tem članku bom predstavil funkcijo SUMPRODUCT, da dobite rezultat neposredno brez pomožnega stolpca.
- Štejte vrstice, če izpolnjujete več meril
- Preštejte število vrstic v obsegu na podlagi več meril, od katerih so nekatera odvisna od logičnih testov, ki delujejo na ravni vrstic, funkcija SUMPRODUCT v Excelu vam lahko naredi uslugo.
- Štetje celic je eno od mnogih vrednosti
- Recimo, da imam seznam izdelkov v stolpcu A, zdaj pa želim pridobiti skupno število posebnih izdelkov Apple, Grape in Lemon, ki so navedeni v območju C4: C6 iz stolpca A, kot je prikazano spodaj. Običajno v Excelu preproste funkcije COUNTIF in COUNTIFS v tem scenariju ne bodo delovale. V tem članku bom govoril o tem, kako hitro in enostavno rešiti to nalogo s kombinacijo funkcij SUMPRODUCT in COUNTIF.
Najboljša orodja za pisarniško produktivnost
Kutools za Excel - vam pomaga izstopati iz množice
Kutools za Excel se ponaša z več kot 300 funkcijami, Zagotavljanje, da je vse, kar potrebujete, le en klik stran ...
Kartica Office - omogočite branje in urejanje z zavihki v programu Microsoft Office (vključite Excel)
- Eno sekundo za preklop med desetinami odprtih dokumentov!
- Vsak dan zmanjšajte na stotine klikov z miško, poslovite se od roke miške.
- Poveča vašo produktivnost za 50% pri ogledu in urejanju več dokumentov.
- Prinaša učinkovite zavihke v Office (vključno z Excelom), tako kot Chrome, Edge in Firefox.
