V tomto článku sa naučíme nájsť počet pomocou viacerých kritérií s logikou ISNA v programe Excel.
Scenár:
Jednoducho povedané, pri práci s dlhými nespravovanými údajmi. Pomocou vzorca v programe Excel musíme extrahovať počet s viacerými kritériami bez logických alebo vyslovených hodnôt, ktoré sa majú vylúčiť.
Ako problém vyriešiť?
V tomto článku budeme potrebovať funkciu MATCH a funkciu SUMPRODUCT. Teraz zo spomínanej funkcie vytvoríme vzorec. Tu uvádzame zoznam zmiešaných hodnôt. Musíme nájsť počet hodnôt, ktoré spĺňajú podmienky, a pomocou funkcie ISNA hodnoty vylúčiť.
Použite vzorec:
| = SUMPRODUCT ((rozsah = kritériá) * (ISNA (MATCH (rng, cri_rng, 0))) |
rozsah: prvý rozsah, ktorý zodpovedá prvým kritériám
kritériá: kritériá, ktoré sa majú zhodovať s prvým rozsahom
rng: druhý rozsah, kde sa majú vylúčiť kritériá
cri_rng: hodnoty, ktoré sa majú vylúčiť z druhého rozsahu
Príklad:
Toto všetko môže byť mätúce na pochopenie. Otestujme teda tento vzorec spustením na príklade uvedenom nižšie.
Tu máme tabuľku a musíme spočítať hodnoty zodpovedajúce určitým nie logickým podmienkam. Podmienky sú nasledujúce.
- Musí to byť zamestnankyňa, takže rodové pole sa zhoduje s F.
- Nesmie zahŕňať zamestnancov zo skupín A a B.
Teraz použijeme nižšie uvedený vzorec na získanie počtu hodnôt v tabuľke, ktoré spĺňajú uvedené kritériá.
Vzorec:
| = SUMPRODUCT ((Pohlavie = H5) * (ISNA (MATCH (Skupiny, I5: I6, 0))) |
Vysvetlenie:
- MATCH (Skupiny, I5: I6,0) porovná všetky skupinové hodnoty s danými hodnotami kritérií a vráti pole čísel a hodnôt #NA.
- Funkcia MATCH vracia index pre priradené hodnoty a #NA! Chyba pre bezkonkurenčné hodnoty.
- ISNA vráti PRAVDU hodnotu pre zodpovedajúcu zhodnú #NA! Hodnoty a FALSE pre vrátené hodnoty indexu.
- Pohlavie = H5, toto vráti všetky zodpovedajúce hodnoty ako PRAVDA a bezkonkurenčné ako NEPRAVDU.
- Funkcia SUMPRODUCT vracia počet zodpovedajúcich hodnôt TRUE zodpovedajúcich obidvom poliam.
Tu je pole funkcie uvedené ako pomenované rozsahy, ako je uvedené v žlto sfarbených bunkách.
Pomenované rozsahy použité vo vzorci:
Pohlavie (E4: E17)
ID (D4: D17)
Skupiny (F4: F17)
Hodnota jedného argumentu je poskytovaná ako odkaz na bunku. Výsledok získate stlačením klávesu Enter.
Ako vidíte na vyššie uvedenom obrázku, počet je 3. Existujú 3 zamestnanci, ktorí sú ženy a nepatria ani do skupiny A, ani do skupiny B.
Teraz môžeme výsledok skontrolovať pomocou filtrovania obsahu, pozri nižšie uvedený gif. Stačí filtrovať pole tabuľky a spočítať počet zhodných hodnôt alebo na získanie počtu použiť vyššie uvedený vzorec.
Tu sú všetky pozorovacie poznámky týkajúce sa použitia vzorca.
Poznámky:
- Vzorec funguje iba s číslami a textovými hodnotami.
- Pole, ktoré je pohlavia, a skupiny musí mať rovnakú dĺžku, inak funkcia SUMPRODUCT vráti chybu.
- Operátori sa radi rovnajú ( = ), menej ako sa rovná ( <= ), väčší než ( > ) alebo sa nerovná () je možné vykonať v rámci funkcie aplikovanej iba s číslami.
Dúfam, že tento článok o tom, ako nájsť počet pomocou viacerých kritérií pomocou logiky ISNA v programe Excel, je vysvetľujúci. Tu nájdete ďalšie články o funkciách COUNTIF. Ak sa vám páčili naše blogy, zdieľajte ich so svojimi prvými na Facebooku. A tiež nás môžete sledovať na Twitteri a Facebooku. Radi by sme vás počuli, dajte nám vedieť, ako môžeme zlepšiť, doplniť alebo inovovať našu prácu a zlepšiť ju pre vás. Napíšte nám na emailovú stránku
Nájdite posledný riadok údajov s číslami v programe Excel : V rade textových hodnôt nájdite posledné údaje v programe Excel.
Ako používať funkciu SUMPRODUCT v programe Excel: Vráti SUMU po vynásobení hodnôt vo viacerých poliach v programe Excel.
COUNTIFS s rozsahom dynamických kritérií : Počet buniek, ktoré sa odlišujú od ostatných hodnôt buniek v programe Excel.
COUNTIFS zápas dvoch kritérií : Počet buniek zodpovedajúcich dvom rôznym kritériám v zozname v programe Excel.
COUNTIFS s ALEBO pre viac kritérií : Počet buniek s viacerými kritériami sa zhoduje pomocou funkcie OR.
Funkcia COUNTIFS v programe Excel : Počet buniek závislých od iných hodnôt buniek.
Ako používať Countif vo VBA v programe Microsoft Excel : Počítanie buniek pomocou kódu Visual Basic for Applications.
Ako používať zástupné znaky v programe Excel : Spočítajte bunky zodpovedajúce frázam pomocou zástupných znakov v programe Excel
Populárne články
50 Skratka pre Excel, ktorá zvýši vašu produktivitu : Zrýchlite sa vo svojej úlohe. Týchto 50 skratiek vám umožní pracovať ešte rýchlejšie v Exceli.
Ako používať tFunkcia VLOOKUP v programe Excel : Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
Ako používať funkciu COUNTIF v programe Excel : Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.
Ako používať funkciu SUMIF v programe Excel : Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.