Štejte število celic, ki niso enake številnim vrednostim v Excelu
V Excelu lahko s funkcijo COUNTIF zlahka dobite število celic, ki niso enake določeni vrednosti, a ste kdaj poskusili prešteti celice, ki niso enake številnim vrednostim? Na primer, želim dobiti skupno število izdelkov v stolpcu A, vendar izključiti posebne postavke v C4: C6, kot je prikazano spodaj. V tem članku bom predstavil nekaj formul za reševanje tega dela v Excelu.
- Štejte število celic, ki niso enake številnim vrednostim s formulo1
- Štejte število celic, ki niso enake številnim vrednostim s formulo2
Štejte število celic, ki niso enake številnim vrednostim s formulo1
Če želite šteti celice, ki niso enake številnim določenim vrednostim, lahko uporabite kombinacijo funkcij MATCH, ISNA in SUMPRODUCT, splošna skladnja je:
= SUMPRODUCT ( -- ( ISNA ( MATCH ( range, exclude_values, 0)))
- range: Seznam celic, ki jih želite prešteti;
- exclude_values: Vrednosti, ki jih je treba prezreti s seznama obsegov.
1. Vnesite ali kopirajte spodnjo formulo v prazno celico, kjer želite izpisati rezultat:
=SUMPRODUCT(--(ISNA(MATCH(A2:A13,C2:C4,0))))
2. Nato pritisnite Vnesite tipko za pravilen rezultat, glejte posnetek zaslona:
Pojasnilo formule:
- UJEMA (A2: A13, C2: C4,0): Ta funkcija MATCH vrne položaj vsake ujemajoče se vrednosti kot številko in vrne #N/A vrednost napake, če je ni mogoče najti. To pomeni, da ko vrednosti v A2: A13 enake vrednostim v C2: C4, vrne številko, v nasprotnem primeru vrne napako #NA. Tukaj je rezultat: {1;#N/A;#N/A; 1; 3; 2;#N/A; #N/A; 3;#N/A; #N/A; #N/A;}.
- ISNA(MATCH(A2:A13,C2:C4,0))= ISNA({1;#N/A;#N/A;1;3;2;#N/A; #N/A;3;#N/A; #N/A; #N/A;}): Ta funkcija ISNA prisili vrednosti napake v TRUE, številke pa v FASLE, zato boste dobili to: {FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE} .
- -(ISNA (MATCH (A2: A13, C2: C4,0)))) =-({FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE}): Dvojni negativni operator pretvori vse TRUE v 1 in FALSE v 0 tako: SUMPRODUCT (-(ISNA (MATCH (A2: A13, C2: C4,0)))) = SUMPRODUCT ({0; 1; 1 ; 0; 0; 0; 1; 1; 0; 1; 1; 1;}).
- Nato se funkcija SUMPRODUCT uporabi za seštevanje števil v nizu in dobi rezultat: 7.
Štejte število celic, ki niso enake številnim vrednostim s formulo2
Če želite to delo rešiti v Excelu, lahko uporabite tudi kombinacijo funkcij COUNTA, SUMPRODUCT in COUNTIF, splošna sintaksa je:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude_values))
- range: Seznam celic, ki jih želite prešteti;
- exclude_values: Vrednosti, ki jih je treba prezreti s seznama obsegov.
1. Prosimo, vnesite ali kopirajte spodnjo formulo v prazno celico, da vnesete rezultat:
=COUNTA(A2:A13)-SUMPRODUCT(COUNTIF(A2:A13,C2:C4))
2. Nato pritisnite Vnesite ključ, da dobite rezultat na naslednji sliki zaslona:
Pojasnilo formule:
- COUNTA (A2: A13): Ta funkcija COUNTA vrne število vseh praznih celic v območju A2: A13. In to bo prineslo rezultat: 12.
- COUNTIF (A2: A13, C2: C4): Ta funkcija COUNTIF šteje celice v razponu A2: A13, ki so enake merilu v C2: C4, zato boste dobili ta rezultat: {2; 1; 2}, kar pomeni, da se Apple pojavi dvakrat, grozdje enkrat in limona dvakrat.
- SUMPRODUCT(COUNTIF(A2:A13,C2:C4))=SUMPRODUCT({2;1;2}): Ta funkcija SUMPRODUCT povzame vse elemente v matriki in vrne končni rezultat: 5.
- COUNTA(A2:A13)-SUMPRODUCT(COUNTIF(A2:A13,C2:C4))=12-5 : Rezultat (5) funkcije SUMPRODUCT se odšteje od števila vseh praznih celic (12), da dobite končni rezultat, ki ga potrebujete.
Opomba: Obe zgornji formuli ne razlikujeta velikih in malih črk.
Uporabljena relativna funkcija:
- MATCH:
- Funkcija MATCH išče določeno vrednost v obsegu celic in vrne relativni položaj te vrednosti.
- ISNA:
- Funkcija ISNA se uporablja za preverjanje, ali formula vrne napako #N/A.
- SUMPRODUCT:
- S funkcijo SUMPRODUCT lahko pomnožite dva ali več stolpcev ali nizov in nato dobite vsoto izdelkov.
- COUNTIF:
- Funkcija COUNTIF je statistična funkcija v Excelu, ki se uporablja za štetje števila celic, ki izpolnjujejo merilo.
- COUNTA:
- Funkcija COUNTA vrne število celic razen praznih celic.
Več člankov:
- Štetje celic je ena od mnogih vrednosti v Excelu
- 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.
- Štetje števila celic med dvema vrednostma / datumi v Excelu
- Ste že kdaj poskušali dobiti ali prešteti število celic med dvema danima številkama ali datumom v Excelu, kot je prikazano na spodnjem posnetku zaslona? Ta članek bo govoril o nekaj uporabnih formulah za spopadanje z njim.
- Štetje števila celic med dvema danima številkama v Excelu
- Štetje števila celic med dvema številkama je za nas običajna naloga v Excelu, vendar boste v določenih primerih morda želeli šteti celice ne med dvema danima številkama. Na primer, imam seznam izdelkov s prodajo od ponedeljka do nedelje, zdaj moram dobiti število celic, ki ne stoji med določenimi nizkimi in visokimi številkami, kot je prikazano na spodnjem posnetku zaslona. V tem članku bom predstavil nekaj formul za reševanje te naloge v Excelu.
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.
