V tem članku se bomo naučili šteti več obsegov z enim merilom v programu Microsoft Excel.
Scenarij:
Preprosto povedano, pri delu s podatkovnimi tabelami moramo včasih prešteti celice, kjer več kot dva obsega izpolnjujeta merila. V Excelu lahko izvajate naloge, kot so operacije v več obsegih, s spodnjo formulo. Merila lahko uporabite za besedilo, številko ali katero koli delno ujemanje v Excelu. Merila v formuli, izvedena z uporabo operatorjev. Operaterji so enaki ( = ), manj kot enako ( <= ), večji kot ( > ) ali ni enako ().
Kako rešiti težavo?
Za to težavo bomo morali uporabiti funkcijo SUMPRODUCT. Zdaj bomo iz funkcije naredili formulo. Tu smo dobili dva obsega podatkov in prešteti moramo vrstice, ki izpolnjujejo 3 merila. Funkcija SUMPRODUCT vrne SUM ustreznih vrednosti TRUE (kot 1) in prezre vrednosti, ki ustrezajo vrednosti FALSE (kot 0) v vrnjenem nizu
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 samo enkrat obiskal Indijo ameriški predsednik Barack Obama, kar se je zgodilo leta 2015. To kaže, da formula izvleče število ujemanja v ustreznem nizu. Ker je tudi en in isti čas, 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.
Upam, da je ta članek o štetju več obsegov z enim merilom v microsoft excelu razložljiv. Več člankov o izračunu vrednosti in sorodnih formulah Excel najdete tukaj. Če so vam bili naši blogi všeč, jih delite s prijatelji 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 spletnem mestu e -pošte.
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 SUMPRODUCT v Excelu: Vrne SUM po množenju vrednosti v več matrikah v Excelu.
COUNTIFS z dinamičnim obsegom meril : Preštejte celice, ki izberejo merila s seznama možnosti v celici meril v Excelu z orodjem za preverjanje podatkov.
COUNTIFS Ujemanje dveh meril : več meril se ujema na različnih seznamih v tabeli s funkcijo COUNTIFS v Excelu
RAČUNI Z OR Za več meril : se ujemata z dvema ali več imeni na istem seznamu z uporabo meril ALI, ki se uporabljajo na seznamu v Excelu.
Kako uporabljati Countif v VBA v programu Microsoft Excel : Štetje celic s kriteriji z uporabo kode Visual Basic za aplikacije v makrih Excel.
Kako uporabljati nadomestne znake v Excelu : Preštejte celice, ki se ujemajo z besednimi zvezami na seznamih besedila z uporabo nadomestnih znakov ( * , ? , ~ ) v Excelu
Priljubljeni članki:
Kako uporabljati funkcijo IF v Excelu : Stavek IF v Excelu preveri pogoj in vrne določeno vrednost, če je pogoj TRUE, ali vrne drugo posebno vrednost, če je FALSE.
Kako uporabljati funkcijo 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 SUMIF v Excelu : To je še ena bistvena funkcija nadzorne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.
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.