V tem članku se bomo naučili, kako šteti vrstice, če to izpolnjuje več meril v Excelu.
Scenarij:
Preprosto povedano, pri delu s podatkovnimi tabelami moramo včasih prešteti celice, kjer več kot dva obsega izpolnjujeta merila. To je mogoče storiti s spodnjo formulo.
Kako rešiti težavo?
Za to težavo bomo morali uporabiti funkcijo SUMPRODUCT. Tukaj imamo dva obsega in potrebujemo število vrstic, ki izpolnjujejo 3 merila. Funkcija SUMPRODUCT vrne SUM ustreznih vrednosti TRUE (kot 1) in prezre vrednosti, ki ustrezajo vrednosti FALSE (kot 0) v vrne eno samo matriko, kjer so bili pogoji TRUE.
Splošna formula:
| = SUMPRODUCT ((rng_1 op_1 crit_1) + 0, (rng_2 op_2 crit_1) + 0, rng_2 op_2 krit_1) + 0) |
rng: obseg za iskanje
crit: merila za uporabo
op: operator meril, pogoj podan kot operator med obsegom in merili
+0: pretvori logične vrednosti v binarne (0 & 1).
Primer:
Vse to je lahko zmedeno za razumevanje. Torej, preizkusimo to formulo tako, da jo zaženemo na spodnjem primeru.
Tu moramo najti število vrstic, navedenih v obsegu, ki imajo 3 pogoje. Tu imamo seznam diplomatskih srečanj med Indijo in ZDA od leta 2014. Tabela prikazuje predsednika / premierja z oznako države in letom. Tabela je razdeljena tudi na dele, ki predstavljajo matično državo in seznam držav obiskov.
Spodaj navedeni pogoji:
Ameriški predsednik Barack Obama je obiskal Indijo in imel manj kot dve težavi.
Uporabite formulo:
| = SUMPRODUCT ((C4: C10 = "Barack Obama") + 0, (F4: F10 = "Indija") + 0, (G4: G10 <2) + 0)) |
C4: C10 = "Barack Obama": Predsednik se na seznamu obiskovalcev ujema z "Barack Obama".
F4: F10 = "Indija": država gostiteljica, ki ustreza "Indiji".
G4: G10 <2: izdaja manj kot dve.
+0: pretvori logične vrednosti v binarne (0 & 1).
Tukaj je obseg podan kot referenca celice. Pritisnite Enter, da dobite štetje.
Kot lahko vidite, je nekoč ameriški predsednik Barack Obama obiskal Indijo, kar se je zgodilo leta 2015. To kaže, da formula izvleče število ujemanja v ustreznem nizu. Kot je bilo enkrat, ko je ameriški predsednik "Barack Obama" obiskal Indijo, kjer so vprašanja enaka 1, kar je manj kot 2
Z enakimi merili:
Zgornji primer je bil enostaven. Da bo zanimivo, bomo po podatkih šteli, kolikokrat so ZDA gostile Indijo od leta 2014.
Spodaj navedeni pogoji:
Indija, ki gosti ZDA, ima težave enake 2.
Uporabite formulo:
| = SUMPRODUCT ((F4: F10 = "ZDA") + 0, (D4: D10 = "Indija") + 0, (G4: G10 = 2) + 0) |
F4: F10 = "ZDA": država gostiteljica, ki ustreza "ZDA".
D4: D10 = "Indija": država na obisku ustreza "Indiji".
G4: G10 = 2: izdaja je enaka dvema.
+0: pretvori logične vrednosti v binarne (0 & 1).
Tukaj je obseg podan kot referenca celice. Pritisnite Enter, da dobite štetje.
Kot lahko vidite, sta ZDA dvakrat gostila Indijo in izdala dva. To kaže, da formula izvleče število ujemanja v ustreznem nizu. Ker so ZDA gostile Indijo 5 -krat, vendar so bile težave bodisi 1 ali 3, tukaj pa moramo vprašanja uskladiti z 2.
Z več kot merili:
Da bi bilo zanimivo, bomo po podatkih šteli, kolikokrat je ameriški predsednik "Donald Trump" od leta 2014 gostil indijskega premierja.
Spodaj navedeni pogoji:
Ameriški predsednik Donald Trump je gostil Indijo, ki ima več kot 1 težave.
Uporabite formulo:
| = SUMPRODUCT ((C4: C10 = "Donald Trump") + 0, (F4: F10 = "Indija") + 0, (G4: G10> 1) + 0) |
F4: F10 = "ZDA": predsednik gostitelj se ujema z "Donald Trump".
D4: D10 = "Indija": država na obisku ustreza "Indiji".
G4: G10 = 2: izdaja je enaka dvema.
+0: pretvori logične vrednosti v binarne (0 & 1).
Tukaj je obseg podan kot referenca celice. Pritisnite Enter, da dobite štetje.
Kot lahko vidite, je nekoč ameriški predsednik "Donald Trump" gostil Indijo in izdal več kot dva. To kaže, da formula izvleče število ujemanja v ustreznem nizu. Kot sta dvakrat, ko je ameriški predsednik "Donald Trump" gostil Indijo, vendar so bila vprašanja 1 ali 3, tukaj pa moramo biti vprašanja večja od 1, kar je 3 laži v letu 2019.
Z vprašanji, ki niso obravnavana v merilih:
Za lažje in priročnejše razumevanje bomo po podatkih šteli, kolikokrat je ameriški predsednik od leta 2014 skupaj obiskal Indijo.
Spodaj navedeni pogoji:
Ameriški predsednik je od leta 2014 skupaj obiskal Indijo.
Uporabite formulo:
| = SUMPRODUCT ((F4: F10 = "Indija")+0, (D4: D10 = "ZDA")+0) |
F4: F10 = "ZDA": država gostiteljica, ki ustreza "ZDA".
D4: D10 = "Indija": država na obisku ustreza "Indiji".
G4: G10 = 2: izdaja je enaka dvema.
+0: pretvori logične vrednosti v binarne (0 & 1).
Tukaj je obseg podan kot referenca celice. Pritisnite Enter, da dobite štetje.
Kot lahko vidite, sta dvakrat, ko so ZDA obiskale Indijo, in vprašanj več kot dva. To kaže, da formula izvleče število ujemanja v ustreznem nizu. Tako je bilo nekoč, ko je ameriški predsednik "Barack Obama" leta 2015 obiskal Indijo, in nekoč, ko je leta 2020 Indijo obiskal ameriški predsednik "Donald Trump".
Kot merila lahko izvedete tudi obsege. Preštejte celice, pri katerih 2 obsega izpolnjujeta merila. Več o Countifu s SUMPRODUCT v Excelu tukaj.
Spodaj je nekaj opazovalnih opomb.
Opombe:
- Formula deluje samo s številkami.
- Polja v formuli morajo biti enaka po dolžini, saj formula vrne napako, kadar ni.
- Funkcija SUMPRODUCT šteje neštevilčne vrednosti kot 0.
- Funkcija SUMPRODUCT upošteva logično vrednost TRUE kot 1 in False kot 0.
- Niz argumentov mora biti enake velikosti, sicer funkcija vrne napako.
- Funkcija SUMPRODUCT vrne vsoto, potem ko vzamete posamezne izdelke v ustreznem nizu.
- Operaterji so enaki ( = ), manj kot enako ( <= ), večji kot ( > ) ali ni enako () je mogoče izvesti v uporabljeni formuli, samo s številkami.
Upam, da je ta članek o tem, kako šteti vrstice, ki izpolnjujejo več meril v Excelu, razložljiv. Več člankov o formulah štetja najdete tukaj. Če so vam bili naši blogi všeč, jih delite s svojimi začetniki na Facebooku. Prav tako nas lahko spremljate na Twitterju in Facebooku. Radi bi slišali od vas, nam sporočite, kako lahko izboljšamo, dopolnimo ali inoviramo svoje delo in ga izboljšamo. Pišite nam na spletni strani elektronske pošte
Poiščite zadnjo vrstico podatkov s številkami v Excelu : V nizu besedilnih vrednosti poiščite zadnje podatke v Excelu.
Kako uporabljati funkcijo SUMPRODUCT v Excelu: Vrne SUM po množenju vrednosti v več matrikah v Excelu.
COUNTIFS z dinamičnim obsegom meril : Preštejte celice na druge vrednosti celic v Excelu.
COUNTIFS Ujemanje dveh meril : Štetje celic, ki ustrezajo dvema različnima kriterijima na seznamu v Excelu.
RAČUNI Z OR Za več meril : Preštejte celice, ki se ujemajo z več merili, s funkcijo OR.
Funkcija COUNTIFS v Excelu : Šteje celice, odvisno od drugih vrednosti celic.
Kako uporabljati Countif v VBA v programu Microsoft Excel : Štetje celic s kodo Visual Basic za aplikacije.
Kako uporabljati nadomestne znake v Excelu : Preštejte celice, ki se ujemajo z frazami, z uporabo nadomestnih znakov v Excelu
Priljubljeni članki
50 Bližnjica v Excelu za večjo produktivnost : Hitreje opravite svojo nalogo. S temi 50 bližnjicami boste še hitreje delali v Excelu.
Kako uporabljati tFunkcija VLOOKUP v Excelu : To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
Kako uporabljati funkcijo COUNTIF v Excelu : Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.
Kako uporabljati funkcijo SUMIF v Excelu : To je še ena bistvena funkcija nadzorne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.