Preštejte manjkajoče vrednosti
Predpostavimo, da imate dva seznama, za preštevanje skupnega števila vrednosti na enem seznamu, ki ne obstajajo na drugem seznamu, lahko uporabite SUMPRODUCT formulo s pomočjo MATCH in ISNA funkcije ali COUNTIF Funkcija.
Preštejte manjkajoče vrednosti s SUMPRODUCT, MATCH in ISNA
Preštejte manjkajoče vrednosti s SUMPRODUCT in COUNTIF
Preštejte manjkajoče vrednosti s SUMPRODUCT, MATCH in ISNA
Za štetje skupno število vrednosti na seznamu B, ki manjkajo na seznamu A kot je prikazano zgoraj, lahko najprej uporabite funkcijo MATCH, da vrnete matriko relativnega položaja vrednosti s seznama B na seznamu A. Če vrednost ne obstaja na seznamu A, bo vrnjena napaka #N/A. Funkcija ISNA bo nato identificirala #N/A napake in SUMPRODUCT bo preštel skupno število napak.
Splošna skladnja
=SUMPRODUCT(--ISNA(MATCH(range_to_count,lookup_range,0)))
- obseg_za_štetje: Obseg, iz katerega se štejejo manjkajoče vrednosti. Tukaj se nanaša na seznam B.
- iskalni_obseg: Razpon za primerjavo z obseg_za_štetje. Tukaj se nanaša na seznam A.
- 0: O match_type 0 prisili MATCH, da izvede natančno ujemanje.
Za štetje skupno število vrednosti na seznamu B, ki manjkajo na seznamu A, kopirajte ali vnesite spodnjo formulo v celico H6 in pritisnite Vnesite da dobite rezultat:
=SUMPRODUCT(--ISNA(MACH(F6: F8,B6: B10,0)))
Pojasnilo formule
=SUMPRODUCT(--ISNA(MATCH(F6:F8,B6:B10,0)))
- UJEMANJE(F6:F8,B6:B10,0): O match_type 0 prisili funkcijo MATCH, da vrne številske vrednosti, ki označujejo relativne položaje vrednosti skozi celice F6 do F8 v območju B6: B10. Če vrednost ne obstaja na seznamu A, bo vrnjena napaka #N/A. Torej bodo rezultati v nizu, kot je ta: {2;3;#N/V}.
- ISNA(UJEMANJE(F6:F8,B6:B10,0)) = ISNA({2;3;#N/V}): ISNA deluje tako, da ugotovi, ali je vrednost napaka »#N/A« ali ne. Če je odgovor pritrdilen, bo funkcija vrnila TURE; Če ne, bo vrnil FALSE. Torej se bo formula ISNA vrnila {FALSE;FALSE;TRUE}.
- SUMPRODUCT(--ISNA(UJEMANJE(F6:F8,B6:B10,0))) = SUMPRODUCT(--{FALSE;FALSE;TRUE}): Dvojni negativni predznak pretvori TRUE v 1s in FALSE v 0s: {0; 1; 0}. Nato funkcija SUMPRODUCT vrne vsoto: 1.
Preštejte manjkajoče vrednosti s SUMPRODUCT in COUNTIF
Za štetje skupno število vrednosti na seznamu B, ki manjkajo na seznamu A, lahko uporabite tudi funkcijo COUNTIF, da ugotovite, ali vrednost obstaja na seznamu A ali ne s pogojem »=0«, saj bo 0 ustvaril, če vrednost manjka. SUMPRODUCT bo nato preštel skupno število manjkajočih vrednosti.
Splošna skladnja
=SUMPRODUCT(--(COUNTIF(lookup_range,range_to_count)=0))
- iskalni_obseg: Razpon za primerjavo z obseg_za_štetje. Tukaj se nanaša na seznam A.
- obseg_za_štetje: Obseg, iz katerega se štejejo manjkajoče vrednosti. Tukaj se nanaša na seznam B.
- 0: O match_type 0 prisili MATCH, da izvede natančno ujemanje.
Za štetje skupno število vrednosti na seznamu B, ki manjkajo na seznamu A, kopirajte ali vnesite spodnjo formulo v celico H6 in pritisnite Vnesite da dobite rezultat:
=SUMPRODUCT(--(COUNTIF(B6: B10,F6: F8)=0))
Pojasnilo formule
=SUMPRODUCT(--(COUNTIF(B6:B10,F6:F8)=0))
- COUNTIF(B6:B10;F6:F8): Funkcija COUNTIF prešteje pojavno število celic F6 do F8 v območju B6: B10. Rezultat bo v matriki, kot je ta: {1; 1; 0}.
- - (COUNTIF(B6:B10;F6:F8)= 0) = - ({1; 1; 0}= 0): Odrezek {1;1;0}=0 daje matriko TRUE in FALSE {FALSE;FALSE;TRUE}. Dvojni negativni predznak nato spremeni vrednosti TRUE v 1s in FALSE v 0s. Končni niz je tak: {0; 0; 1}.
- SUMPRODUCT(- (COUNTIF(B6:B10;F6:F8)= 0)) = SUMPRODUCT({0; 0; 1}): SUMPRODUCT nato vrne vsoto: 1.
Sorodne funkcije
V Excelu lahko funkcijo SUMPRODUCT pomnožite dva ali več stolpcev ali nizov in nato dobite vsoto izdelkov. Pravzaprav je SUMPRODUCT uporabna funkcija, ki lahko pomaga pri štetju ali seštevanju vrednosti celic z več merili, kot je funkcija COUNTIFS ali SUMIFS. Ta članek bo predstavil sintakso funkcije in nekaj primerov za to funkcijo SUMPRODUCT.
Funkcija Excel MATCH išče določeno vrednost v obsegu celic in vrne relativni položaj vrednosti.
Funkcija COUNTIF je statistična funkcija v Excelu, ki se uporablja za štetje števila celic, ki izpolnjujejo merilo. Podpira logične operatorje (<>, =, > in <) ter nadomestne znake (? in *) za delno ujemanje.
Sorodne formule
Obstajajo primeri, ko morate primerjati dva seznama, da preverite, ali vrednost seznama A obstaja na seznamu B v Excelu. Na primer, imate seznam izdelkov in želite preveriti, ali izdelki na vašem seznamu obstajajo na seznamu izdelkov, ki ga je zagotovil vaš dobavitelj. Za izpolnitev te naloge smo spodaj našteli tri načine, izberite tistega, ki vam je všeč.
Ta članek se bo osredotočil na Excelove formule za štetje celic, ki so popolnoma enake besedilnemu nizu, ki ste ga določili, ali delno enake danemu besedilnemu nizu, kot je prikazano na spodnjih zaslonih. Najprej bo razložil sintakso in argument formule ter zagotovil primere za lažje razumevanje.
Štejte število celic ne med dvema danima številkama
Š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.
