Počítanie počtu riadkov v programe Microsoft Excel. Počítanie počtu hodnôt v stĺpci vo funkcii Excel Excel počítanie počtu hodnôt

Počítanie počtu buniek v programe Excel môže byť potrebné v rôznych prípadoch. V tomto článku zvážime, ako počítať bloky s určitými hodnotami, prázdne a ak spadajú za daných podmienok. Využijeme na to funkcie: COUNT, COUNTIF, COUNTIFS, COUNTNUT.

naplnené

Najprv sa pozrime na najjednoduchší spôsob. Ak potrebujete spočítať počet vyplnených blokov v programe Excel, vyberte rozsah buniek v dokumente - existuje rôznymi spôsobmi, prečítajte si o nich kliknutím na odkaz - a v "Stavový riadok" venujte pozornosť položke "Množstvo". Bude tam napísaný počet neprázdnych blokov: berie sa do úvahy všetko s textom a číslami, ale prázdne nie.

Ak potrebujete spočítať bloky v tabuľke naplnenej určitými hodnotami a použiť toto číslo vo vzorcoch na výpočty, potom tadiaľto nevhodné, pretože údaje v tabuľke sa môžu pravidelne meniť. Preto prejdime k úvahám o funkciách.

Kde sa zadávajú čísla

Funkcia COUNT - počíta bloky vyplnené len číselnými hodnotami. Vyberte H1, vložte "=", napíšte funkciu "COUNT". Zadajte požadovaný rozsah (F1:G10) ako argument funkcie. Ak existuje viacero rozsahov, oddeľte ich znakom „;“ – (F1:G10;B3:C8) .

Celkovo je 20 blokov. Nebral sa do úvahy ten, v ktorom je napísaný text, ale rátali sa tie, ktoré sú vyplnené dátumom a časom.

S konkrétnym textom alebo významom

Existuje napríklad tabuľka, ktorá ukazuje, koľko kilogramov určitého produktu sa predalo za deň. Spočítajme si, koľko sa predalo tovaru s hmotnosťou nad 5 kilogramov. Aby ste to dosiahli, musíte spočítať, koľko blokov je v stĺpci Hmotnosť, kde je hodnota väčšia ako päť. Funkcia bude vyzerať takto: =COUNTIF(B2:B13; ">5"). Vypočíta počet blokov s obsahom väčším ako päť.

Aby sa funkcia rozšírila na ďalšie bloky, povedzme, zmenili sa podmienky, je potrebné zafixovať zvolený rozsah. Môžete to urobiť pomocou absolútnych odkazov v Exceli.

– počet buniek so zápornými hodnotami: =COUNTIF(B2:B13;"<0″) ;
- počet blokov, v ktorých je obsah väčší (menej) ako v A10 (napríklad): =COUNTIF(B2:B13;">"&A10);
– bunky s hodnotou väčšou ako 0: =COUNTIF(B2:B13; ">0");
– neprázdne bloky z vybraného rozsahu: =COUNTIF(B2:B13;"<>») .

Na výpočet buniek v Exceli, ktoré obsahujú text, môžete použiť aj funkciu COUNTIF. Spočítajme si napríklad, koľko ovocia je v tabuľke. Vyberme oblasť a špecifikujme „ovocie“ ako kritérium. Počítajú sa všetky bloky s daným slovom. Nemôžete písať text, ale jednoducho vybrať obdĺžnik, ktorý ho obsahuje, napríklad C2.

Pri vzorci COUNTIF nezáleží na veľkosti písmen, spočítajú sa bunky obsahujúce text „Ovocie“ a „ovocie“.

Môžete použiť aj ako kritérium Špeciálne symboly: "*" a "?" . Vzťahujú sa len na text.

Spočítajme, koľko produktov začína na písmeno A: „A*“. Ak zadáte „marhuľa *“, budú sa brať do úvahy všetky produkty, ktoré začínajú „marhuľou“: marhuľová šťava, marhuľový džem, marhuľový koláč.

Symbol "?" V slove môžete nahradiť akékoľvek písmeno. Zapísaním do kritérií "f?ukt" - budú zohľadnené slová ovocie, fuukt, fuukt.

Ak chcete spočítať slová v bunkách, ktoré pozostávajú z určitého počtu písmen, vložte otázniky do riadku. Pre počítanie produktov s 5 písmenami v názve nastavíme ako kritérium „?????“. .

Ak nastavíte hviezdičku ako kritérium, všetky bloky obsahujúce text sa budú počítať zo zvoleného rozsahu.

S viacerými kritériami

Funkcia COUNTIFS sa používa, keď potrebujete zadať niekoľko podmienok, maximálny počet v Exceli je 126. Ako argument: nastavte prvý rozsah hodnôt a špecifikujte podmienku cez ";" nastavte druhý rozsah a napíšte preň podmienku - =COUNTIFS(B2:B13;">5"C2:C13,"ovocie").

V prvom rozsahu sme uviedli, že hmotnosť bola viac ako päť kilogramov, v druhom sme zvolili, že ide o ovocie.

prázdne bloky

Spočítať v Exceli počet buniek, ktoré obsahujú textové alebo číselné hodnoty, nie je také ťažké. Použite na to špeciálne funkcie a nastavte podmienky. S ich pomocou môžete spočítať prázdne bloky aj tie, kde sú napísané určité slová alebo písmená.

Ohodnotiť článok:

(1 hodnotenie, priemer: 5,00 z 5)

Správca webu. Vysokoškolské vzdelanie v odbore "Ochrana informácií" Autor väčšiny článkov a hodín počítačovej gramotnosti

Veľmi často pri práci v Exceli musíte spočítať počet buniek na pracovnom hárku. Môžu to byť prázdne alebo vyplnené bunky obsahujúce iba číselné hodnoty a v niektorých prípadoch musí ich obsah spĺňať určité kritériá. V tomto návode sa podrobne pozrieme na dve hlavné funkcie Excelu na počítanie údajov − KONTROLA a COUNTIF, ako aj zoznámenie sa s menej populárnymi - ÚČTY, COUNTBLANK a COUNTIFS.

KONTROLA()

štatistická funkcia KONTROLA spočíta počet buniek v zozname argumentov, ktoré obsahujú iba číselné hodnoty. Napríklad na obrázku nižšie sme spočítali počet buniek v rozsahu, ktorý pozostáva výlučne z čísel:

V nasledujúcom príklade dve bunky rozsahu obsahujú text. Ako vidíte, funkcia KONTROLA ignoruje ich.

Zohľadňujú sa však bunky obsahujúce hodnoty dátumu a času:

Funkcia KONTROLA dokáže spočítať počet buniek v niekoľkých nesúvislých rozsahoch naraz:

Ak potrebujete spočítať počet neprázdnych buniek v rozsahu, môžete použiť štatistickú funkciu ÚČTY. Bunky obsahujúce text, číselné hodnoty, dátum, čas a boolovské hodnoty TRUE alebo FALSE sa považujú za neprázdne.

COUNTIF()

štatistická funkcia COUNTIF umožňuje spočítať bunky robotníka strana v Exceli použitím iný druh podmienky. Vzorec uvedený nižšie napríklad vráti počet buniek, ktoré obsahujú záporné hodnoty:

Nasledujúci vzorec vráti počet buniek, ktorých hodnota je väčšia ako obsah bunky A4.

COUNTIF umožňuje počítať bunky obsahujúce textové hodnoty. Napríklad nasledujúci vzorec vráti počet buniek obsahujúcich slovo „text“ a nerozlišuje veľké a malé písmená.

Podmienka booleovskej funkcie COUNTIF môže obsahovať zástupné znaky: * (hviezdička) a ? (otáznik). Hviezdička predstavuje ľubovoľný počet ľubovoľných znakov, zatiaľ čo otáznik predstavuje jeden ľubovoľný znak.

Funkcia COUNTIF umožňuje použiť párne vzorce ako podmienku. Ak chcete napríklad spočítať počet buniek s hodnotami vyššími ako je priemer, môžete použiť nasledujúci vzorec:

Ak vám jedna podmienka nestačí, vždy môžete použiť štatistickú funkciu COUNTIFS. Táto funkcia umožňuje počítať bunky v Exceli, ktoré spĺňajú dve alebo viac podmienok naraz.

Napríklad nasledujúci vzorec počíta bunky, ktorých hodnoty sú väčšie ako nula, ale menšie ako 50:

Funkcia COUNTIFS umožňuje počítať bunky pomocou podmienky A. Ak chcete vypočítať množstvo s podmienkou ALEBO, musíte použiť niekoľko funkcií COUNTIF. Napríklad nasledujúci vzorec počíta bunky, ktoré začínajú písmenom ALE alebo s listom Komu:

Funkcie Excelu na počítanie údajov sú veľmi užitočné a môžu sa hodiť takmer v každej situácii. Dúfam, že táto lekcia vám odhalila všetky tajomstvá funkcií. KONTROLA a COUNTIF, ako aj ich najbližší spolupracovníci - ÚČTY, COUNTBLANK a COUNTIFS. Vráťte sa k nám častejšie. Všetko najlepšie a úspech pri učení Excelu.

Povedzme, že chcete vedieť, koľko jedinečných hodnôt je v rozsahu, ktorý obsahuje duplicitné hodnoty. Napríklad, ak stĺpec obsahuje nasledovné:

Hodnoty 5, 6, 7 a 6 sa vydávajú ako tri jedinečné hodnoty: 5, 6 a 7.

Hodnoty sú "Kirill", "Sergey", "Sergey", "Sergey", výsledkom sú dve jedinečné hodnoty - "Kirill" a "Sergey".

Existuje niekoľko spôsobov, ako spočítať jedinečné hodnoty medzi duplikátmi.

Počítanie jedinečných hodnôt pomocou filtra

Pomocou dialógového okna Pokročilý filter Môžete extrahovať jedinečné hodnoty zo stĺpca údajov a vložiť ich na nové miesto. Potom pomocou funkcie MAKTY môžete spočítať počet prvkov v novom rozsahu.

Počítanie jedinečných hodnôt pomocou funkcií

Na splnenie tejto úlohy použite kombináciu funkcií Ak, sumy, Frekvencia, ZÁPAS a DLSTR .

Pomocou funkcie priraďte hodnotu 1 každej zo skutočných podmienok AK.

Pridajte súčet s funkciou sumy .

Spočítajte počet jedinečných hodnôt pomocou funkcie Frekvencia. Funkcia FREQUENCY nespracúva text a hodnoty null. Pri prvom výskyte konkrétnej hodnoty táto funkcia vráti číslo, ktoré sa rovná počtu výskytov tejto hodnoty. Pre každý výskyt rovnakej hodnoty, ktorá je po prvej, táto funkcia vráti nulu.

Vráti umiestnenie textovej hodnoty v rámci rozsahu pomocou funkcie ZÁPAS. Návratová hodnota sa potom použije ako argument funkcie FREQUENCY aby bolo možné vyhodnotiť zodpovedajúce textové hodnoty.

Hľadanie prázdnych buniek pomocou funkcie Len. Dĺžka prázdnych buniek je 0.

Poznámky:

Vzorce zobrazené v tomto príklade musia byť zadané ako vzorce poľa. Ak ste nainštalovali Aktuálna verzia Office 365, môžete jednoducho zadať vzorec do ľavej hornej bunky výstupného rozsahu a stlačiť kláves ENTER na potvrdenie použitia vzorca dynamického poľa. V opačnom prípade musí byť vzorec zadaný pomocou stará verzia výberom výstupného rozsahu, zadaním vzorca do ľavej hornej bunky rozsahu a stlačením klávesov CTRL+SHIFT+ENTER na potvrdenie. Excel automaticky vloží zložené zátvorky na začiatok a koniec vzorca. Ďalšie informácie Vzorce poľa nájdete v časti Používanie vzorcov poľa: Najlepšie postupy a príklady.

Ak chcete zobraziť podrobný výpočet funkcie, vyberte bunku so vzorcom a potom na kartu vzorce v skupine Závislosti vzorcov stlač tlačidlo Výpočet vzorca.

Popis funkcií

Funkcia Frekvencia Vypočíta frekvenciu výskytu hodnôt v rozsahu hodnôt a vráti vertikálne pole čísel. Použite napríklad funkciu Frekvencia spočítať počet výsledkov testov, ktoré spadajú do rozsahu skóre. Keďže táto funkcia vracia pole, musí byť zadané ako vzorec poľa.

Funkcia ZÁPAS vyhľadá konkrétny prvok v rozsahu buniek a vráti relatívnu pozíciu tohto prvku v rozsahu. Ak napríklad rozsah a1:A3 obsahuje hodnoty 5, 25 a 38, vzorec funkcie =Match(25;a1:A3;0) vráti číslo 2, pretože hodnota 25 je druhým prvkom rozsahu.

Funkcia DLSTR vráti počet znakov v textovom reťazci.

Funkcia SUM vypočíta súčet všetkých čísel zadaných ako argumenty. Každý argument môže byť rozsah, odkaz na bunku, pole, konštanta, vzorec alebo výsledok inej funkcie. Napríklad, funkcia súčtu (a1:A5) spočíta všetky čísla obsiahnuté v bunkách od a1 do a5.

Funkcia Ak vráti jednu hodnotu, ak je zadaná podmienka pravdivá, a inú hodnotu, ak je podmienka nepravdivá.

Ďalšie informácie

Vždy môžete položiť otázku komunite Excel Tech Community , požiadať o pomoc v komunite Odpovede a tiež navrhnúť Nová funkcia alebo zlepšenie na webovej stránke

Predpokladajme, že chcete určiť, koľkokrát sa konkrétny text alebo číslo vyskytuje v rozsahu buniek. Napríklad:

Ak má rozsah (napríklad a2:d20) číselné hodnoty 5, 6, 7 a 6, potom sa číslo 6 vyskytuje dvakrát.

Ak má stĺpec hodnoty "Baturin", "Belov", "Belov" a "Belov", potom "Belova" je spokojná s tromi hodnotami.

Spočítajte počet výskytov jednej hodnoty pomocou funkcie COUNTIF

Použite funkciu COUNTIF zistiť, koľkokrát sa určitá hodnota vyskytuje v rozsahu buniek.

Viac informácií nájdete v článku Funkcia COUNTIF.

Spočítajte výskyty na základe viacerých kritérií pomocou funkcie COUNTIFS

Funkcia COUNTIFS podobne ako funkcia COUNTIF s jednou dôležitou výnimkou: COUNTIFS vám umožňuje použiť kritériá na bunky vo viacerých rozsahoch a počíta počet zhôd pre každé kritérium. Pomocou funkcie COUNTIFS môžete použiť až 127 párov rozsahov a kritérií.

Syntax funkcie COUNTIFS je nasledovná:

COUNTIFS(rozsah_podmienky1; podmienka1; [rozsah_stavy2; podmienka2];…)

Pozrite si príklad nižšie.

Ďalšie informácie o používaní tejto funkcie na počítanie výskytov vo viacerých rozsahoch a s viacerými podmienkami nájdete v článku o funkcii COUNTIFS.

Počítajte výskyty na základe podmienok s funkciami COUNT a IF

Povedzme, že potrebujete určiť, koľko predajcov predalo určitú položku v určitom regióne, alebo chcete vedieť, koľko predajov spracoval s určitou hodnotou konkrétny predajca. Môžete použiť funkcie Ak a skontrolovať spolu. to znamená, že funkcia sa použije ako prvá Ak na testovanie podmienky a ak je výsledok funkcie pravdivý, funkcia sa použije na počítanie buniek skontrolovať .

Poznámky:

Ďalšie informácie o týchto funkciách nájdete v článkoch o funkcii COUNT a funkcii IF.

Spočítajte počet výskytov viacerých textových a číselných hodnôt pomocou funkcií SUM a IF

V nasledujúcich príkladoch funkcií AK a SUM sa používajú spolu. Funkcia AK najprv skontroluje hodnoty v určitých bunkách a potom, ak sa vráti TRUE, funkcia SUM pridá hodnoty, ktoré spĺňajú podmienku.

Poznámky: Vzorce zobrazené v tomto príklade musia byť zadané ako vzorce poľa.

Príklad 1

Vyššie uvedená funkcia znamená, že ak rozsah C2:C7 obsahuje hodnoty Šaškov a Tumanov, potom by funkcia SUM mala zobraziť súčet záznamov, v ktorých je podmienka splnená. Vzorec nájde v danom rozsahu tri záznamy pre "Dáma" a jeden pre "Hmly" a zobrazí 4 .

Príklad 2

Vyššie uvedená funkcia znamená, že ak bunka D2:D7 obsahuje hodnoty menšie ako 9 000 USD alebo väčšie ako 19 000 USD, funkcia SUM by mala zobraziť súčet všetkých záznamov, v ktorých je splnená podmienka. Vzorec nájde dva záznamy D3 a D5 s hodnotami menšími ako 9 000 USD a potom D4 a D6 s hodnotami väčšími ako 19 000 USD a zobrazí 4 .

Príklad 3

Vyššie uvedená funkcia hovorí, že D2:D7 obsahuje účty pre Baturin menej ako 9 000 USD a súčet by mal odrážať súčet záznamov, v ktorých je pozorovaný. Vzorec nájde bunku C6, ktorá zodpovedá podmienke a zobrazí 1 .

Spočítajte počet výskytov viacerých hodnôt pomocou kontingenčnej tabuľky

Pomocou kontingenčnej tabuľky môžete zobraziť súčty a spočítať počet opakovaní v kontingenčnej tabuľke. Kontingenčná tabuľka je interaktívny spôsob, ako rýchlo zhrnúť veľké množstvo údajov. Môžete ho použiť na rozbalenie a zbalenie prezentačných vrstiev, aby ste získali presné výsledky a hĺbkovú analýzu súhrnných údajov pre otázky, ktoré vás zaujímajú. Môžete tiež presunúť riadky do stĺpcov alebo stĺpce do riadkov ("pivotovať"), aby ste videli počet výskytov hodnoty v kontingenčnej tabuľke. Pozrime sa na príklad predajnej tabuľky, v ktorej môžete spočítať počet predajných hodnôt pre sekcie Golf a Tenis za konkrétne štvrťroky.

Ďalšie informácie

Vždy môžete položiť otázku komunite Excel Tech Community, požiadať o pomoc v komunite Odpovede alebo navrhnúť novú funkciu alebo vylepšenie na webovej lokalite Excel User Voice.

Poznámka: Táto stránka je preložená automaticky, takže môže obsahovať nepresnosti a gramatické chyby. Je pre nás dôležité, aby bol tento článok pre vás užitočný. Boli informácie užitočné? Pre pohodlie tiež

Ako počítať počet opakovaní

V tomto článku vám chcem povedať, ako môžete spočítať počet opakovaní hodnoty v tabuľke alebo bunke. Začnime po poriadku. Je tam tabuľka:

A musíte spočítať počet opakovaní každého mena:

Napodiv, ale urobiť to je veľmi jednoduché: Excel má funkciu - COUNTIF, s ktorým to všetko zvládnete za pár sekúnd. Ak sa počet opakovaní každého mena musí zobraziť v stĺpci B tabuľky a samotné názvy sa nachádzajú v stĺpci A:
=COUNTIF($A$2:$A$30, A2)
Rozsah($A$2:$A$30) - sú označené bunky rozsahu, v ktorých sú uložené hodnoty, ktorých počet sa musí vypočítať. Hlavná prednosť: Tento argument môže byť iba odkazom na bunku alebo rozsah buniek. Nie je dovolené zadať ľubovoľné pole hodnôt.
Kritérium(A2) - označuje odkaz na bunku alebo priamo hodnotu pre výpočet. Tie. môžete ho zadať aj takto: =COUNTIF($A$2:$A$30 ;"Apple") . Môžete použiť aj zástupné znaky: ? a *. Tie. zadaním „*banán*“ ako Kritéria môžete spočítať počet buniek, v ktorých sa slovo „banán“ vyskytuje (banán, banán, banány, banánová šťava, sto banánov, tri banány a orech atď.). A zadanie "banana*" - hodnoty začínajúce na "banana" (banány, banánová šťava, banánový háj atď.). "?" - nahrádza len jeden znak, t.j. zadaním "ban?n" môžete počítať riadky s hodnotou "banana" a s hodnotou "banon" atď. Ak zadáte ako kritérium =COUNTIF($A$2:$A$30;"*"), potom sa započítajú všetky textové hodnoty. Číselné hodnoty sa ignorujú. Tieto zástupné znaky (* a?) nemožno použiť na číselné hodnoty – iba na text. Tie. ak zadáte ako kritérium "12*", potom čísla 1234, 123, 120 atď. sa nebude počítať. Na počítanie číselných hodnôt by sa mali použiť porovnávacie operátory: =COUNTIF($A$2:$A$30 ;">12")

Ako vidno z druhého obrázku, mená sa tam neopakujú, hoci sú v tabuľke všetky pomiešané. Nebudem sa nad tým pozastavovať – už som sa tomu venoval v článku Ako získať zoznam jedinečných (neopakujúcich sa) hodnôt? a ak je to potrebné, môžete použiť ktorúkoľvek z metód opísaných v ňom.

Ale. Existujú prípady, keď sa zoznam vôbec nenachádza v tabuľke, ale v jednej bunke ($D$1):
Melón Kiwi Hruška Jablko Melón Hruška Hruška Vodný melón Jablko Banán Jablko Banán Jablko Jablko Melón Kivi Melón Banán Melón Vodný melón Kiwi Melón Jablko Melón Hruška Jablko Kiwi Vodný melón
COUNTIF tu určite nepomôže. Excel je však plný ďalších funkcií a všetko sa dá urobiť rovnako jednoducho:
=(DLSTR($D$1)-DLSTR(SUBSTITUTE($D$1 ; D3 ;"")))/DLSTR(D3)
DLSTR- spočíta počet znakov v zadanej bunke/riadku ($D$1 , D3)
NÁHRADŤ(text; starý_text; nový_text) - nahradí určený znak v zadanom texte ľubovoľným iným nastavená hodnota. Štandardne nahrádza všetky výskyty zadaného znaku. Na tom je založený algoritmus. Pomocou hodnoty Banana(D3) ako príkladu krok za krokom analýza vzorca:

• pomocou funkcie DLSTR dostaneme počet znakov v riadku so zdrojovým textom ($D$1) = (170-LEN (NÁHRAD ($D$1 ; D3 ; ""))) / DLSTR (D3) ;
• pomocou funkcie SUBSTITUTE nahradíme všetky hodnoty Banana(D3) v riadku so zdrojovým textom ($D$1) prázdnym a pomocou DLSTR dostaneme počet znakov v reťazci po tomto nahradení =(170-155) /DLSTR(D3);
• od celkového počtu znakov odpočítajte počet znakov v reťazci po nahradení a výsledok vydeľte počtom znakov v kritériách =(170-155)/5 .

Dostaneme číslo 3. Čo sme potrebovali.

("Spodná lišta":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"vľavo","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":150," textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100% ; farba pozadia:#333333; nepriehľadnosť:0,6; filter:a lpha(opacity=60);","titlecss":"display:blok; poloha:relatívna; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"zobraziť:blok; poloha:relatívna; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; farba:#fff; margin-top:8px;","buttoncss":"display:block; poloha:relatívna; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))