V našom článku Počíta bunky, ktoré obsahujú konkrétny text, sme spočítali každú bunku, ktorá obsahuje konkrétny text. V tomto článku sa naučíme, ako počítať bunky, ktoré obsahujú jednu alebo druhú hodnotu. Inými slovami, počítanie s logikou ALEBO.
Môžete si myslieť, že môžete použiť funkciu COUNTIF dvakrát a potom ich sčítať. Ale to je nesprávny smer. Budete vedieť prečo.
Generický vzorec
= SUMPRODUCT (-((ISNUMBER (VYHĽADÁVANIE ("toto", rozsah)) + ISNUMBER (VYHĽADÁVANIE ("to", rozsah)))> 0))
“Toto”: je to prvý text, ktorý chcete počítať v rozsahu. Môže to byť ľubovoľný text.
"To": je to druhý text, ktorý chcete počítať v rozsahu. Môže to byť ľubovoľný text.
Rozsah: Toto je rozsah alebo pole obsahujúce text, v ktorom budete počítať pre svoje konkrétne texty.
Pozrime sa na príklad:
Príklad:
Tu mám pripomienky k spätnej väzbe v rozsahu A2 až A8. Chcem spočítať počet komentárov obsahujúcich „dobré“ alebo „pekné“ slová.
Ak chcete počítať bunky obsahujúce konkrétne texty „dobré“ alebo „pekné“, zadajte tento vzorec:
= SUMPRODUCT (-((ISNUMBER (VYHĽADÁVANIE ("dobré", A2: A8)) + ISNUMBER (VYHĽADÁVANIE ("pekné", A2: A8)))> 0))
Môžete vidieť, že existujú štyri komentáre, ktoré obsahujú buď „dobré“ alebo „pekné“.
Ako to funguje?
Začnime zvnútra.
VYHĽADÁVANIE ("dobré", A2: A8): táto časť vracia pole #HODNOTY chýb a čísel, ktoré predstavujú polohu nájdeného textu. {8; #HODNOTA!;#HODNOTA!;#HODNOTA!;#HODNOTA!; 30; 24}
ISNUMBER (SEARCH („dobrý“, A2: A8)): táto časť vzorca kontroluje každú hodnotu v poli návrat pomocou funkcie SEARCH, či je to číslo alebo nie, a vráti pole TRUE a FALSE. V tomto prípade vráti hodnotu {TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE}.
ISNUMBER (VYHĽADÁVANIE („pekné“, A2: A8)): táto časť vzorca robí to isté, ale tentoraz hľadá v bunke „pekné“ slovo a vráti pole PRAVDIVÉHO a NEPRAVDIVÉHO základu v bunke, ktoré obsahuje „pekné“ . ”
Ďalej sme tieto polia pridali pomocou znamienka +. Boolean hodnoty do 1 a 0 a sčíta ich. Interne to bude vyzerať takto: {1; 0; 0; 0; 0; 1; 1}+{0; 1; 0; 0; 0; 1; 0} = {1; 1; 0; 0; 0; 2; 1}.
Môžete vidieť, že komentár v A7 obsahuje texty „pekný“ aj „dobrý“. A tu sa to započítava dvakrát. Nechceme to Vykonáme teda ďalšie porovnanie na konverziu každého čísla na hodnotu True a False.
((ISNUMBER (VYHĽADÁVAŤ ("dobré", A2: A8)) + ISNUMBER (HLEDAŤ ("pekné", A2: A8)))>0): Táto časť vzorca konvertuje výsledné pole na hodnotu TRUE a FALSE. Ak je hodnota väčšia ako 0, je TRUE else FALSE. Tu to bude {TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE}.
Počet TRUE v poli je názov reťazca obsahujúceho „dobré“ alebo „pekné“ slová.
SUMPRODUKT (-((ISNUMBER (VYHĽADÁVANIE („dobré“, A2: A8)) + ISNUMBER (VYHĽADÁVANIE („pekné“, A2: A8)))> 0))
Ďalej použijeme - negatívne symboly na ich premenu na čísla. A nakoniec SUMPRODUCT sumarizuje pole na vrátenie počtu buniek obsahujúcich „dobré“ alebo „pekné“.
Môžete tiež použiť funkciu SUMA, ale potom budete musieť zadať tento vzorec ako vzorec poľa.
Prečo nevyužiť COUNTIFS?
Z dôvodu dvojnásobného počtu.
Ak bunka obsahuje obidva texty, bude započítaná dvakrát, čo v tomto prípade nie je správne.
Ale ak chcete, aby sa to stalo, použite tento vzorec,
= SUM (COUNTIFS (A2: A8, {"*nice*", "*good*"}))
V našom prípade vráti 5. Tu som to vysvetlil.
Rozlišovanie malých a veľkých písmen
Navrhované riešenie počíta daný text bez ohľadu na veľkosť písmen. Ak chcete počítať zhody s rozlišovaním malých a veľkých písmen, nahraďte funkciu SEARCH funkciou FIND.
= SUMPRODUCT (-((ISNUMBER (NÁJSŤ(„toto“, rozsah)) + ISNUMBER (NÁJSŤ(„to“, rozsah)))> 0))
TheNÁJSŤ funkcia rozlišuje veľké a malé písmená. Vráti pozíciu nájdeného textu.
Takže áno, chlapci, takto môžete spočítať počet buniek, ktoré obsahujú buď tento text alebo tamto. O tejto funkcii si môžete prečítať aj kliknutím na názvy funkcií vo vzorci. Zrozumiteľne som ich rozpracoval.
Ako skontrolovať, či bunka obsahuje konkrétny text v programe Excel
Ako skontrolovať zoznam textov v reťazci v programe Excel
Získajte v programe Excel zhodu dvoch kritérií COUNTIFS
Získajte COUNTIFS s ALEBO pre viac kritérií 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.