Přejít k hlavnímu obsahu

Spočítat hodnoty oddělené čárkou v buňce

V tomto kurzu se naučíte, jak používat kombinaci funkcí LEN, TRIM a SUBSTITUTE k počítání hodnot oddělených čárkami v buňce v aplikaci Excel.


Jak počítat hodnoty oddělené čárkami v buňce v aplikaci Excel?

Jak je ukázáno na níže uvedeném snímku obrazovky, ve sloupci B je seznam řetězců oddělených čárkami, který chcete spočítat hodnoty oddělené čárkami v každé buňce. Chcete-li toho dosáhnout, postupujte následovně.

Obecný vzorec

=IF(ISBLANK(B3),"",LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3),",",""))+1)

Argumenty

A1: Představuje buňku obsahující řetězec oddělený čárkami, který spočítá hodnoty oddělené čárkami.

Jak používat tento vzorec?

1. Vyberte prázdnou buňku k odeslání výsledku. V tomto případě vyberu buňku D3.

2. Zadejte do ní následující vzorec a stiskněte vstoupit klíč. A potom přetáhněte jeho rukojeť výplně úplně dolů a použijte ji na další buňky.

=IF(ISBLANK(B3),"",LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3),",",""))+1)

Poznámka: V tomto vzorci je B3 buňka obsahující řetězec oddělený čárkami, uvnitř kterého budete počítat hodnoty oddělené čárkami. Můžete to změnit podle potřeby.

Jak tento vzorec funguje?

=IF(ISBLANK(B3),"",LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3),",",""))+1)

1. LEN(TRIM(B3)): Funkce TRIM odstraní všechny mezery pro extrahování z řetězce odděleného čárkami „AA, BB, CC, DD“ a mezi slovy ponechá pouze jednu mezeru. A pak funkce LEN vypočítá celkovou délku "AA, BB, CC, DD" a vrátí výsledek jako 14;

2. LEN(SUBSTITUTE(TRIM(B3),",",""))

  • SUBSTITUTE(TRIM(B3),",",""): Jak je uvedeno výše uvedené vysvětlení, funkce TRIM zde odstraní veškerý extrahovaný prostor z „AA, BB, CC, DD“ a vrátí výsledek jako „AA, BB, CC, DD“. A poté funkce SUBSTITUTE nahradí všechny čárky v „AA, BB, CC, DD“ ničím a získá výsledek „AA BB CC DD“;
  • LEN("AA BB CC DD"): Funkce LEN vrací počet znaků v textovém řetězci „AA BB CC DD“. Výsledek je 11.

3. 14 11 1-+: Zde použijte celkovou délku „AA, BB, CC, DD“ odečtením celkové délky „AA BB CC DD“, abyste získali celkový počet čárek v řetězci (14-11 = 3). Protože za poslední čárkou vždy bude slovo, musíte do čísla 1 přidat 3, abyste v řetězci získali celková slova oddělená čárkami. Zde je výsledek 4.

4. IF(ISBLANK(B3),"",14): Funkce If zde říká, že pokud je B3 prázdné, tak nevrací nic, jinak vrací číslo 14. Zde B3 obsahuje hodnoty, takže funkce IF vrací číslo 14.


Související funkce

Funkce Excel LEN
Funkce Excel LEN vrací počet znaků v textovém řetězci.

Funkce Excel SUBSTITUTE
Funkce Excel SUBSTITUTE nahradí text nebo znaky v textovém řetězci jiným textem nebo znaky.

Funkce Excel TRIM
Funkce Excel TRIM odstraní z textového řetězce všechny mezery navíc a mezi slovy zachová pouze jednotlivé mezery.


Související vzorce

Počítat výskyty konkrétních znaků v buňce aplikace Excel
V tomto kurzu se dozvíte, jak použít vzorce založené na funkcích LEN a SUSTITUTE k výpočtu počtu zobrazení konkrétního znaku v buňce aplikace Excel.

Počítat konkrétní znaky v rozsahu buněk
V tomto kurzu jsou uvedeny některé vzorce, které počítají, kolikrát se určitý znak objevil v rozsahu v aplikaci Excel.

Počítat konkrétní slova v buňce v aplikaci Excel
V tomto článku se chystáme představit vzorec, který spočítá, kolikrát se konkrétní slovo objeví v buňce v aplikaci Excel.

Počítat konkrétní slova v rozsahu v aplikaci Excel
Tento článek vysvětluje vzorec, který počítá, kolikrát se konkrétní slovo objevilo v rozsahu buněk v aplikaci Excel.

Spočítá počet znaků v buňce v aplikaci Excel
Celkový počet znaků zahrnuje nejen všechna písmena, ale také všechny mezery, interpunkční znaménka a symboly v buňce. Podle tohoto tutoriálu můžete snadno spočítat celkový počet znaků v buňce pomocí funkce LEN v aplikaci Excel.

Spočítat počet znaků v rozsahu v aplikaci Excel
Tento článek vysvětluje vzorec pro počítání celkových znaků, které se objevují v rozsahu buněk v aplikaci Excel.


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 (6)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Thanks for this formula. I had been trying a different formula: =LEN(A1)-LEN(SUBSTITUTE(A1),",",""))+1 but if the cell was blank it would have a 1 in it. This doesn't, so it works for counting up numbers. In my case, I'm trying to count the number of dates such as Oct. 6, 13, 20, 27. However, it's not working when it comes to multiplying the count by the cost of the lesson. For instance, if John had lessons on Oct. 6, 13, 20, 27, I have a column for dates of lessons, then a column for number of lessons, a column for the cost of each lesson (30 or 45), and a column to calculate monthly tuition due (number of lessons x cost). For the student with blank dates/no lessons, it gives an error value, and therefore affects my total tuition for the month. Please advise. Thanks!
This comment was minimized by the moderator on the site
Hi sir, formula is not working properly. I have numerical data in cells. It works correctly in cells where numbers consists upon two digits like 22,43,59 etc, but it fails when there comes data like 103,144 or 165,45 etc. Please help
This comment was minimized by the moderator on the site
Hi,
The formula works fine for me and do not reproduce the error you mentioned. May I ask what version of Excel you are using?
This comment was minimized by the moderator on the site
Office 2021
This comment was minimized by the moderator on the site
thanks! this worked very well, except for that it also returns "1" when the cell is blank. I'm trying to count the number of concatenated dates. There are some cells that don't have any dates, and those are coming back with the number '1'. However, the formula is still counting correctly the number of dates otherwise.
This comment was minimized by the moderator on the site
Hi Alicia Majcher,
Thank you for your feedback. If you want to return nothing when the reference cell is blank, apply the following formula.
=IF(ISBLANK(A30),"",LEN(TRIM(A30))-LEN(SUBSTITUTE(TRIM(A30),",",""))+1)
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations