Přejít k hlavnímu obsahu

Jak sčítat buňky s textem a čísly v aplikaci Excel?

Například mám seznam hodnot obsahujících číselné a textové řetězce, nyní chci sčítat pouze čísla založená na stejném textu, podívejte se na následující snímek obrazovky. Za normálních okolností nemůžete hodnoty v seznamu sečíst přímo textovým řetězcem, zde vám představím několik vzorců pro řešení tohoto úkolu.


Součet buněk obsahujících text a čísla na základě určitého textu se vzorcem

Pomocí vzorce pole můžete sečíst čísla na základě jejich odpovídajícího textového řetězce v buňce, postupujte takto:

1. Nejprve si můžete napsat textové řetězce, které chcete sečíst relativní čísla v buňkách sloupce.

2. Pak zadejte níže vzorec do prázdné buňky, v tomto příkladu zadám vzorec do buňky E2 a poté stiskněte Ctrl + Shift + Enter kláves společně vrátí celkovou hodnotu ze seznamu na základě určitého textu. Viz snímek obrazovky:

=SUM(IF(ISNUMBER(FIND(D2,$B$2:$B$9)),VALUE(LEFT($B$2:$B$9,FIND(D2,$B$2:$B$9)-1)),0))

Poznámka: Ve výše uvedeném vzorci, D2 je kritérium, které chcete použít, a B2: B9 označuje rozsah buněk, který chcete sečíst.

3. Poté vyberte buňku vzorce a přetáhněte úchyt výplně dolů do buněk, na které chcete použít tento vzorec, abyste získali výsledek:


Sečtěte buňky obsahující text a čísla na základě určitého textu pomocí praktické funkce

Pokud je pro vás výše uvedený vzorec těžko pochopitelný, Kutools pro Excel poskytuje užitečný nástroj - Součet založený na stejném textu, s touto funkcí můžete získat celkový výsledek v buňce, která se mísí s čísly a textem, aniž byste si pamatovali jakékoli vzorce.

Poznámka:Chcete-li je použít Součet založený na stejném textu funkce, za prvé, měli byste si stáhnout Kutools pro Excela poté rychle a snadno aplikujte funkce.

Po instalaci Kutools pro Excel, postupujte následovně:

1. Klepnutím vyberte buňku, do které chcete výsledek odeslat, viz screenshot:

Tipy: Abyste zabránili cyklickým odkazům, nevkládejte tento vzorec do prvního řádku listu.

2. A pak klikněte na tlačítko Kutools > Pomocník vzorců > Pomocník vzorců, viz screenshot:

3. V Pomocník vzorců V dialogovém okně proveďte následující operace:

  • vybrat Statistický z Typ vzorce rozbalovací seznam;
  • v Vyberte vzorec v seznamu vyberte Součet založený na stejném textu volba;
  • Pak v Zadání argumentů V části vyberte rozsah buněk obsahujících text a čísla, která chcete sečíst v Rozsah textové pole a poté vyberte textovou buňku, na kterou chcete sečíst hodnoty na základě v Text Textové pole.
Tipy: V Text textové pole, měli byste změnit výchozí absolutní odkaz na buňku na relativní odkaz na buňku pro správné přetažení vzorce.

4. A pak klikněte Ok tlačítko, bude vypočítán první výsledek, pak přetáhněte úchyt pro vyplnění vzorce do dalších buněk, viz screenshot:


Součet buněk obsahujících text a čísla ignoruje text pomocí funkce definované uživatelem

Pokud máte následující data, která jsou smíchána s textem a čísly, a chcete pouze extrahovat a sečíst pouze čísla v každé buňce, jak to můžete rychle dokončit v aplikaci Excel ?.

K řešení tohoto problému můžete vytvořit a Uživatelem definovaná funkce, udělejte prosím toto:

1. Podržte ALT + F11 klávesy pro otevření Okno Microsoft Visual Basic pro aplikace.

2, klikněte Vložit > Modula vložte následující kód do Okno modulu.

Kód VBA: sečtěte pouze čísla v buňce obsahující čísla a text

Function SumNumbers(rngS As Range, Optional strDelim As String = " ") As Double
'Updateby Extendoffice
    Dim xNums As Variant, lngNum As Long
    xNums = Split(rngS, strDelim)
    For lngNum = LBound(xNums) To UBound(xNums) Step 1
        SumNumbers = SumNumbers + Val(xNums(lngNum))
    Next lngNum
End Function

3. Poté tento kód uložte a zavřete, vraťte se do listu a zadejte tento vzorec = SumNumbers (A2) (A2 označuje buňku, kterou chcete sečíst čísla), pak přetáhněte popisovač výplně dolů do buněk, které chcete vyplnit vzorec, a přidají se pouze čísla v každé buňce, viz screenshot:


Více relativních článků:

  • Zkombinujte duplicitní řádky a sečtěte hodnoty v aplikaci Excel
  • V aplikaci Excel , se s tímto problémem můžete vždy setkat, pokud máte rozsah dat, který obsahuje některé duplicitní položky, a nyní chcete kombinovat duplicitní data a sečíst odpovídající hodnoty v jiném sloupci, jak je znázorněno na následujících obrázcích obrazovky. Jak byste mohli vyřešit tento problém?
  • Vynásobte dva sloupce a poté součet v aplikaci Excel
  • V aplikaci Excel může většina z nás trpět vynásobením dvou sloupců a jejich následným sčítáním, samozřejmě můžeme jednotlivé položky vynásobit a poté je sečíst, ale to bude obtížné, pokud bude třeba počítat stovky nebo tisíce řádků. V aplikaci Excel existuje výkonná funkce - SUMPRODUCT, pomocí které můžeme rychle vynásobit dva sloupce a poté je sečíst. V tomto článku budu hovořit o tom, jak tuto funkci použít.
  • Součet nejlepších 3 nebo N hodnot na základě kritérií v aplikaci Excel
  • Normálně můžeme sčítat prvních n hodnot z řady buněk pomocí jednoduchého vzorce, ale zkusili jste někdy sčítat prvních n hodnot na základě některých kritérií. V tomto článku představím několik vzorců pro součet nejvyšších hodnot n se specifickými kritérii v aplikaci Excel.
  • Součet jedinečných hodnot na základě kritérií v aplikaci Excel
  • Například mám řadu dat, která obsahuje sloupce Název a Objednávka, nyní, abych shrnul pouze jedinečné hodnoty ve sloupci Objednávka na základě sloupce Název, jak ukazuje následující snímek obrazovky. Jak rychle a snadno vyřešit tento úkol v aplikaci Excel?
  • Počítání nebo součet buněk na základě barev písma v aplikaci Excel
  • Jak můžete získat počet buněk nebo sečíst všechny buňky, které obsahují určitou barvu písma v aplikaci Excel? Například mám v listu řadu dat, jak ukazuje následující snímek obrazovky, a teď chci příslušně spočítat nebo sečíst buňky, které mají červenou, modrou a černou barvu písma. Normálně neexistuje žádný přímý způsob, jak se s tímto úkolem vypořádat, zde budu hovořit o některých tricích pro řešení této úlohy.

Nejlepší nástroje pro produktivitu v kanceláři

🤖 Kutools AI asistent: Revoluční analýza dat založená na: Inteligentní provedení   |  Generovat kód  |  Vytvořte vlastní vzorce  |  Analyzujte data a generujte grafy  |  Vyvolejte funkce Kutools...
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 vyhledávání: Více kritérií VLookup    VLookup s více hodnotami  |   VLookup na více listech   |   Fuzzy vyhledávání ....
Pokročilý rozevírací seznam: Rychle vytvořte 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 rozsahy a sloupce ...
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, ...)   |   ... a více

Rozšiřte své dovednosti Excel pomocí Kutools pro Excel a zažijte efektivitu jako nikdy předtím. Kutools for Excel nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času.  Kliknutím sem získáte funkci, kterou nejvíce potřebujete...

Popis


Office Tab přináší do Office rozhraní s kartami a usnadňuje vám práci

  • Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
  • Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
  • Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!
Comments (50)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi,
I want to have a formula that count text. But what I want is that he count a criterium/ text like as 0,5 and not as 1. Like for example I have one column with T, TO and TM. T I want to count as 1 and TO/ TM as 0,50. I have tried several things, but can not solve it. Hopefully can someone help me out?
This comment was minimized by the moderator on the site
Hi, I am trying to check approximately 8 different types of leave. I am using conditional formatting to colour code letters and then calculate as numbers. It works well, I like it. I did not factor in a half day.

Can anyone tell me how to account for a half day, and still have Excel recognize the Alpha symbol.
This comment was minimized by the moderator on the site
Hi, how do I sum something like the following:
Column A
Row 1 2D
Row 2 2N
Row 3 1D+2N

I have no issues summing the Ds but when I try summing the Ns I would encounter a problem. The string 1D+2N is the culprit, so how do I go about this?
Any help is very much appreciated.

Thank you.
This comment was minimized by the moderator on the site
I must be doing something incorrect. When I try to use the SumNumbers() VB code nothing happens? excel does not recognize it as code? I am using a macro enabled document. Image attached.
https://ibb.co/6vRtD6N

[img]https://ibb.co/6vRtD6N[/img]
This comment was minimized by the moderator on the site
Hi, Michael,
I have tested the code based on the data you provied, it can work well.
This is User Defined Function, so, you needn't run the code, you just need to insert the formula SumNumbers(A1), and press Enter key to get the result. See screenshot:
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-sum-text-number.png

Please try, hope it can help you!
This comment was minimized by the moderator on the site
How to do this, but with decimal places? When using decimals, it returns 0...
This comment was minimized by the moderator on the site
HOW TO MULTIPLY COLUM 1 , 2 PCS , COLOUM 2 RATE 1 USD TOTAL AMOUNT ?
This comment was minimized by the moderator on the site
kindly reply to "I want to use this formula to sum only numbers in all columns like =SumNumbers(A1:G1) but it gives error VALUE. how it can be fixed?"
This comment was minimized by the moderator on the site
You need to use the VBA for that to work. the VBA code defines the SumNumbers as a function.
This comment was minimized by the moderator on the site
I have used VBA for that but it doesn't work. Can you plz take a look?
This comment was minimized by the moderator on the site
I have used VBA for that but it does not work. Can you take a look plz?
This comment was minimized by the moderator on the site
I want to use this formula to sum only numbers in all columns like =SumNumbers(A1:G1) but it gives error VALUE. how it can be fixed?
This comment was minimized by the moderator on the site
I am using this in excel 2016 but it doesn't seem to be working is there a work through? i keep getting a "
#name?" error
This comment was minimized by the moderator on the site
Hi,
I have a question. How to get the sum or the average of the column that has text inside that column. Please see attachment. Your answer would be greatly appreciated.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations