Přejít k hlavnímu obsahu

Spočítejte počet řádků, které obsahují konkrétní hodnoty v Excelu

Může být pro nás snadné spočítat počet buněk s konkrétní hodnotou v listu aplikace Excel. Získání počtu řádků, které obsahují konkrétní hodnoty, však může být poměrně komplikované. V takovém případě vám může pomoci komplexnější vzorec založený na funkcích SUM, MMULT, TRANSPOSE a COLUMN. Tento tutoriál bude hovořit o tom, jak vytvořit tento vzorec pro řešení této úlohy v aplikaci Excel.


Spočítat počet řádků, které obsahují konkrétní hodnoty

Například máte v listu řadu hodnot a nyní musíte spočítat počet řádků s danou hodnotou „300“, jak ukazuje níže uvedený snímek obrazovky:

Chcete -li získat počet řádků, které obsahují konkrétní hodnoty, obecná syntaxe je:

{=SUM(–(MMULT(–(data=X),TRANSPOSE(COLUMN(data)))>0))}
Array formula, should press Ctrl + Shift + Enter keys together.
  • data: Rozsah buněk ke kontrole, zda obsahují konkrétní hodnotu;
  • X: Specifická hodnota, kterou používáte k počítání řádků.

1. Zadejte nebo zkopírujte následující vzorec do prázdné buňky, kam chcete umístit výsledek:

=SUM(--(MMULT(--($A$2:$C$12=300),TRANSPOSE(COLUMN($A$2:$C$12)))>0))

2. Pak stiskněte tlačítko Ctrl + Shift + Enter společně získáte správný výsledek, viz screenshot:


Vysvětlení vzorce:

=SUM(--(MMULT(--($A$2:$C$12=300),TRANSPOSE(COLUMN($A$2:$C$12)))>0))

  • -$ A $ 2: $ C $ 12 = 300: Tento výraz zkontroluje, zda v rozsahu A300: C2 existuje hodnota „12“, a vygeneruje výsledek pole PRAVDA a NEPRAVDA. Dvojité záporné znaménko se používá k převodu hodnot TRUE na 1 s a FALSE na 0 s. Výsledek tedy získáte takto: {0,0,0; 1,0,0; 0,0,0; 0,1,1; 0,0,0; 0,1,0; 0,0,0 , 1,0,0; 0,0,1; 0,0,0; 1,1,1; 11}. Pole skládající se z 3 řádků a 1 sloupců bude fungovat jako poleXNUMX ve funkci MMULT.
  • TRANSPOZICE (SLOUPEK ($ A $ 2: $ C $ 12)): Funkce COLUMN zde slouží k získání čísla sloupce v rozsahu A2: C12, vrací pole se 3 sloupci takto: {1,2,3}. A pak funkce TRANSPOSE přepne toto pole na třířadé pole {3; 1; 2}, fungující jako pole3 ve funkci MMULT.
  • MMULT (-($ A $ 2: $ C $ 12 = "Joanna"), TRANSPOSE (SLOUPEK ($ A $ 2: $ C $ 12))): Tato funkce MMULT vrací maticový součin výše uvedených dvou polí, výsledek získáte takto: {0; 1; 0; 5; 0; 2; 0; 1; 3; 0; 6}.
  • SUM(--(MMULT(--($A$2:$C$12="Joanna"),TRANSPOSE(COLUMN($A$2:$C$12)))>0))= SUM(--{0;1;0;5;0;2;0;1;3;0;6}>0): Nejprve zkontrolujte hodnoty v poli větší než 0: Pokud je hodnota větší než 0, zobrazí se PRAVDA; pokud je menší než 0, zobrazí se FALSE. A pak dvojité záporné znaménko vynutí, aby PRAVDA a NEPRAVDA byly 1 s a 0 s, takže získáte toto: SUM ({0; 1; 0; 1; 0; 1; 0; 1; 1; 0; 1}). Nakonec funkce SUMA sečte hodnoty v poli a vrátí výsledek: 6.

Tip:

Pokud potřebujete spočítat počet řádků, které obsahují konkrétní text v listu, použijte níže uvedený vzorec a nezapomeňte stisknout Ctrl + Shift + Enter klíče dohromady, abyste získali celkový počet:

=SUM(--(MMULT(--(ISNUMBER(SEARCH("Joanna",A2:C12))),TRANSPOSE(COLUMN($A$2:$C$12)))>0))


Použitá relativní funkce:

  • SOUČET:
  • Funkce Excel SUMA vrací součet dodaných hodnot.
  • MMULT:
  • Funkce Excel MMULT vrací maticový součin dvou polí.
  • PŘEMÍSTIT:
  • Funkce TRANSPOSE vrátí pole v nové orientaci na základě určité oblasti buněk.
  • SLOUPEC:
  • Funkce COLUMN vrací číslo sloupce, ve kterém se vzorec zobrazuje, nebo vrací číslo sloupce daného odkazu.

Další články:

  • Počítejte řádky, pokud splňují interní kritéria
  • Předpokládejme, že máte zprávu o prodejích produktů za letošní a loňský rok a nyní možná budete muset počítat produkty, u nichž jsou tržby v tomto roce vyšší než v loňském roce nebo tržby v tomto roce jsou nižší než loni, jak je uvedeno níže zobrazený snímek obrazovky. Normálně můžete přidat pomocný sloupec pro výpočet rozdílu v prodeji mezi těmito dvěma roky a poté pomocí COUNTIF získat výsledek. Ale v tomto článku představím funkci SUMPRODUCT, abych získal výsledek přímo bez jakéhokoli pomocného sloupce.
  • Počítejte řádky, pokud splňují více kritérií
  • Počítejte počet řádků v rozsahu na základě více kritérií, z nichž některá závisí na logických testech, které fungují na úrovni řádků, funkce SUMPRODUCT v Excelu vám může udělat laskavost.
  • Spočítat počet buněk se rovná jedné z mnoha hodnot
  • Předpokládám, že mám seznam produktů ve sloupci A, nyní chci získat celkový počet konkrétních produktů Apple, Grape a Lemon, které jsou uvedeny v rozsahu C4: C6 ze sloupce A, jak je uvedeno níže. V aplikaci Excel v tomto scénáři obvykle nebudou fungovat jednoduché funkce COUNTIF a COUNTIFS. Tento článek budu mluvit o tom, jak rychle a snadno vyřešit tuto práci kombinací funkcí SUMPRODUCT a COUNTIF.

Nejlepší kancelářské nástroje produktivity

Kutools pro Excel - pomůže vám vyniknout před davem

Populární funkce: Najít, zvýraznit nebo identifikovat duplikáty  |  Odstranit prázdné řádky  |  Kombinujte sloupce nebo buňky bez ztráty dat  |  Kolo bez vzorce ...
Super VLookup: Více kritérií  |  Vícenásobná hodnota  |  Přes Multi-Sheets  |  Fuzzy vyhledávání...
Adv. Rozbalovací seznam: Snadno rozevírací seznam  |  Závislý rozbalovací seznam  |  Vícenásobný výběr rozevíracího seznamu...
Správce sloupců: Přidejte konkrétní počet sloupců  |  Přesunout sloupce  |  Přepnout stav viditelnosti skrytých sloupců  Porovnejte sloupce s Vyberte stejné a různé buňky ...
Doporučené funkce: Zaměření mřížky  |  Návrhové zobrazení  |  Velký Formula Bar  |  Správce sešitů a listů | Knihovna zdrojů (Automatický text)  |  Výběr data  |  Zkombinujte pracovní listy  |  Šifrovat/dešifrovat buňky  |  Odesílat e-maily podle seznamu  |  Super filtr  |  Speciální filtr (filtr tučné/kurzíva/přeškrtnuté...) ...
Top 15 sad nástrojů12 Text Tools (doplnit text, Odebrat znaky ...)  |  50+ Graf Typ nemovitosti (Ganttův diagram ...)  |  40+ Praktické Vzorce (Vypočítejte věk na základě narozenin ...)  |  19 Vložení Tools (Vložte QR kód, Vložit obrázek z cesty ...)  |  12 Konverze Tools (Čísla na slova, Přepočet měny ...)  |  7 Sloučit a rozdělit Tools (Pokročilé kombinování řádků, Rozdělit buňky Excelu ...)  |  ... a více

Kutools pro Excel se může pochlubit více než 300 funkcemi, Zajištění toho, že to, co potřebujete, je jen jedno kliknutí...


Záložka Office - Povolte čtení a úpravy na záložkách v Microsoft Office (včetně Excelu)

  • Jednu sekundu přepnete mezi desítkami otevřených dokumentů!
  • Snižte stovky kliknutí myší každý den, sbohem s myší rukou.
  • Zvyšuje vaši produktivitu o 50% při prohlížení a úpravách více dokumentů.
  • Přináší efektivní karty do Office (včetně Excelu), stejně jako Chrome, Edge a Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations