V tomto článku sa dozvieme, ako počítať bunky, ktoré sa mnohým veciam v programe Excel nepočítajú.
Jednoducho povedané, zvážte scenár, v ktorom musíme počítať údaje na základe konkrétnych kritérií v poli. počítanie buniek, ktoré neobsahujú hodnoty, na zachytenie hodnoty bunky pomocou funkcie programu Excel, ktorá je tu vysvetlená na príklade. Na chytanie reťazcov používajte znaky zástupných znakov a vykonávajte na nich funkcie.
Na tento účel použijeme nasledujúce funkcie.
- Funkcia SUMPRODUCT
- Funkcia MATCH
- Funkcia ISNA
- Funkcia COUNTASEO
- Funkcia COUNTIFS
Teraz sa o nich v skratke zoznámime. Máme rôzne metódy, ako to urobiť. Uvidíme to teda jeden po druhom.
Prvá metóda
Počítajte bunky, ktoré neobsahujú hodnoty v rozsahu. Tu máme vzorec, ako to urobiť. Na nájdenie hodnôt, ktoré je potrebné spočítať, použijeme funkciu MATCH a funkcia ISNA zachytí chybu #NA. Potom SUMPRODUCT vráti súčet hodnôt.
- Funkcia SUMPRODUCT
- Funkcia MATCH
- Funkcia ISNA
Všeobecný vzorec:
| = SUMPRODUCT ( - (ISNA (MATCH (rozsah, hodnoty, 0))) |
Premenné:
rozsah: pole bunky, ktorá sa má počítať
hodnoty: pole hodnôt, ktoré sa majú v rozsahu ignorovať.
Príklad
Poďme túto funkciu pochopiť na príklade.
Tu máme ovocie v jednom stĺpci a zeleninu v inom. Prvá žiadostivosť má v zozname nejakú zeleninu a musíme počítať iba ovocie, ktoré neuvažuje o zelenine.
Použite vzorec:
| = SUMPRODUCT ( -- (ISNA (ZÁPAS (ovocie, zelenina, 0)))) |
ovocie: pomenovaný rozsah pre pole B2: B10
Zelenina: pomenovaný rozsah použitý pre pole D3: D5
Vysvetlenie:
- Funkcia MATCH vracia pozíciu každého indexu, kde sa hodnoty zhodujú v rozsahu, alebo vracia chybu #NA, ak sa nenašla.
- Funkcia ISNA zachytí chybu #NA a vráti hodnotu TRUE pre chybu #NA a FALSE inde.
- -- operátor prevedie všetky hodnoty TRUE na 1 s a FALSE na 0 s.
- Funkcia SUMPRODUCT vracia súčet všetkých čísel v poli, čo je skutočne počet buniek, v ktorých sa hodnoty neberú do úvahy.
Ako vidíte, vzorec vracia počet ovocia bez ohľadu na hodnotu zo zeleniny.
Druhá metóda:
Budeme zvažovať iné metódy, pretože rôzne typy problémov majú rôzne riešenia. Použitie funkcie COUNTIFS v programe Excel.
Všeobecný vzorec:
| = COUNTIFS (rozsah, "hodnota1", rozsah, "hodnota2", …) |
Premenné:
rozsah: pole bunky, ktorá sa má počítať
hodnota1: hodnota, ktorá sa má v rozsahu ignorovať
hodnota2: hodnota, ktorá sa má v rozsahu ignorovať
Príklad
Poďme túto funkciu pochopiť na príklade.
Tu máme ovocie v jednom stĺpci a zeleninu v inom. V prvom stĺpci je v zozname zelenina a musíme počítať iba ovocie, ktoré neuvažuje o zelenine.
Použite vzorec:
| = COUNTIFS (ovocie, "" & D3, ovocie, "" & D4, ovocie, "" & D5) |
ovocie: pomenovaný rozsah pre pole B2: B10
D3, D4 a D5 sú odkazy na bunky použité vo vzorci
Vysvetlenie:
- Tu sa rozsah kontroluje s jednotlivými hodnotami a vráti počet buniek.
- operátor je nerovná sa
- & operátor, zreťazte hodnoty s referenčnou hodnotou bunky.
Použite vzorec:
| = COUNTIFS (ovocie, "" & D3, ovocie, "" & D4, ovocie, "" & D5) |
ovocie: pomenovaný rozsah pre pole B2: B10
D3, D4 a D5 sú odkazy na bunky použité vo vzorci
Vysvetlenie:
- Tu sa rozsah kontroluje s jednotlivými hodnotami a vráti počet buniek.
- operátor je nerovná sa
- & spojte operátor s hodnotou používanou odkazom na bunku.
Ako vidíte, vzorec vracia počet ovocia, pričom sa neberie do úvahy žiadna hodnota zeleniny.
Tretia metóda
Budeme zvažovať iné metódy, pretože rôzne typy problémov majú rôzne riešenia. Použitie funkcie COUNTA v programe Excel. Tu logika vypočíta počet buniek s hodnotami a potom ich odčíta od počtu všetkých hodnôt v rozsahu.
Všeobecný vzorec:
| = COUNTA (rozsah) - SUMPRODUCT (COUNTIF (rozsah, hodnoty) |
Premenné:
rozsah: pole bunky, ktorá sa má počítať
hodnoty: pole hodnôt, ktoré sa majú v rozsahu ignorovať.
Príklad
Poďme túto funkciu pochopiť na príklade.
Tu máme ovocie v jednom stĺpci a zeleninu v inom. Prvá žiadostivosť má v zozname nejakú zeleninu a musíme počítať iba ovocie, ktoré neuvažuje o zelenine.
Použite vzorec:
| = COUNTA (ovocie) - SUMPRODUCT (COUNTIF (ovocie, zelenina)) |
ovocie: pomenovaný rozsah pre pole B2: B10
Zelenina: pomenovaný rozsah použitý pre pole D3: D5
Vysvetlenie:
- Funkcia COUNTIF počíta bunky v ovocí so zeleninou.
- SUMPRODUCT získa súčet s prihliadnutím na všetky hodnoty.
- Funkcia COUNTA vypočítava počet prázdnych buniek v rozsahu.
- Rozdiel spôsobí, že počet buniek (ovocie) nemá hodnoty (zelenina).
Ako vidíte, vzorec vracia počet ovocia bez ohľadu na hodnotu zo zeleniny.
Dúfam, že ste pochopili, ako počítať bunky, ktoré sa mnohým veciam v programe Excel nerovná. Tu nájdete ďalšie články o funkciách programu Excel Count. Neváhajte uviesť svoj dotaz alebo spätnú väzbu k vyššie uvedenému článku.
COUNTIFS s rozsahom dynamických kritérií: Počet buniek s dynamickým rozsahom kritérií pomocou funkcie COUNTIFS.
COUNTIFS zápas dvoch kritérií: Počet buniek s viacerými rozsahmi kritérií pomocou funkcie COUNTIFS.
COUNTIFS s ALEBO pre viac kritérií: Počet buniek používajúcich logické funkcie s funkciou COUNTIFS.
Funkcia COUNTIFS v programe Excel: použitie funkcie COUNTIFS na počítanie buniek s viacerými kritériami
Ako používať Countif vo VBA v programe Microsoft Excel: Na počítanie buniek použite funkciu COUNTIF vo VBA.
Ako používať zástupné znaky v programe Excel: Chytajte frázy a kľúčové slová pomocou vysvetlených zástupných znakov v programe Excel
Populárne články
50 Skratka pre Excel, ktorá zvýši vašu produktivitu
Upravte rozbaľovací zoznam
Ak s podmieneným formátovaním
Ak so zástupnými znakmi
Vlookup podľa dátumu