Spočítajte bunky, ktoré obsahujú buď x alebo y

Na počítanie buniek, ktoré obsahujú buď x alebo y, môžete použiť vzorec založený na Funkcia SUMPRODUCT . V uvedenom príklade vzorec v bunke F5 je:

= SUMPRODUCT (--(( ISNUMBER ( FIND ('abc',rng)) +  ISNUMBER ( FIND ('def',rng)))>0))

Toto je roztok vzorca pre jednu bunku, vysvetlený nižšie. Je tiež možné použiť jednoduchší vzorec založený na a pomocný stĺp , tiež vysvetlené nižšie.

Keď počítate bunky s „ALEBO logikou“, musíte dávať pozor, aby ste nezapočítali dvakrát. Ak napríklad počítate bunky, ktoré obsahujú „abc“ alebo „def“, nemôžete jednoducho sčítať dve funkcie COUNTIF, pretože bunky, ktoré obsahujú „abc“ aj „def“, môžete zdvojnásobiť.

Jednobunkový roztok

Pre jeden vzorec môžete použiť SUMPRODUCT s ISNUMBER + NÁJSŤ . Vzorec v F5 je:

 
= SUMPRODUCT (--(( ISNUMBER ( FIND ('abc',B5:B11))+ ISNUMBER ( FIND ('def',B5:B11)))>0))

Tento vzorec je založený na vzorci vysvetlené tu ktorý lokalizuje text vo vnútri bunky:

 
= SUMPRODUCT (--(( ISNUMBER ( FIND ('abc',B5:B11)) +  ISNUMBER ( FIND ('def',B5:B11)))>0))

Keď je daný rozsah buniek, tento útržok vráti príponu pole hodnôt TRUE/FALSE, jedna hodnota pre každú bunku v rozsahu. Pretože to používame dvakrát (raz pre „abc“ a raz pre „def“), dostaneme dva polia.

Ďalej tieto polia sčítame (s +), čím vytvoríme nové jediné pole čísel. Každé číslo v tomto poli je výsledkom súčtu hodnôt TRUE a FALSE v pôvodných dvoch poliach dohromady. V uvedenom príklade výsledné pole vyzerá takto:

 
 ISNUMBER ( FIND ('abc',B5:B11)

Tieto čísla musíme sčítať, ale nechceme ich zdvojnásobovať. Musíme sa teda uistiť, že každá hodnota väčšia ako nula sa započítava iba raz. Aby sme to urobili, vynútime všetky hodnoty na TRUE alebo FALSE s '> 0', potom silou na 1/0 pomocou dvojito negatívny (-).

Nakoniec SUMPRODUCT vráti súčet všetkých hodnôt v poli.

Pomocný kolónový roztok

S pomocným stĺpcom na kontrolu každej bunky jednotlivo je problém menej zložitý. Môžeme použiť COUNTIF s dvoma hodnotami (poskytuje sa ako „konštanta poľa“). Vzorec v C5 je:

 
{2020102}

tabuľková funkcia na výpočet čistej súčasnej hodnoty je

COUNTIF vráti pole, ktoré obsahuje dve položky: počet pre 'abc' a počet pre 'def'. Aby sme zabránili dvojitému počítaniu, položky sčítame a potom vynútime výsledok na hodnotu TRUE/FALSE pomocou '> 0'. Nakoniec prevedieme hodnoty TRUE/FALSE na 1 a 0 dvojitým záporným znamienkom (-).

Konečný výsledok je buď 1 alebo 0 pre každú bunku. Ak chcete získať súčet za všetky bunky v rozsahu, jednoducho sčítajte pomocný stĺpec.