Przejdź do głównej zawartości

Jak podsumować komórki z tekstem i liczbami w programie Excel?

Na przykład mam listę wartości zawierającą ciągi liczbowe i tekstowe, teraz chcę zsumować tylko liczby oparte na tym samym tekście, spójrz na poniższy zrzut ekranu. Zwykle nie można bezpośrednio zsumować wartości z listy za pomocą ciągu tekstowego, tutaj przedstawię kilka formuł, które pozwolą sobie z tym zadaniem.


Sumuj komórki zawierające tekst i liczby na podstawie określonego tekstu ze wzorem

Możesz użyć formuły tablicowej, aby zsumować liczby na podstawie odpowiadającego im ciągu tekstowego w komórce, wykonaj następujące czynności:

1. Najpierw możesz zapisać ciągi tekstowe, które chcesz zsumować względne liczby w komórkach kolumn.

2. Następnie wpisz poniższą formułę w pustej komórce, w tym przykładzie wprowadzę formułę w komórce E2, a następnie naciśnij Ctrl + Shift + Enter klucze razem, zwróci całkowitą wartość z listy na podstawie określonego tekstu. Zobacz zrzut ekranu:

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

Note: W powyższym wzorze, D2 to kryterium, którego chcesz użyć, i B2: B9 wskazuje zakres komórek, który chcesz zsumować.

3. Następnie wybierz komórkę z formułą i przeciągnij uchwyt wypełniania w dół do komórek, do których chcesz zastosować tę formułę, aby uzyskać wynik:


Sumuj komórki zawierające tekst i liczby na podstawie określonego tekstu za pomocą przydatnej funkcji

Jeśli powyższy wzór jest dla Ciebie trudny do zrozumienia, Kutools dla programu Excel zapewnia przydatne narzędzie - Suma na podstawie tego samego tekstuDzięki tej funkcji możesz uzyskać całkowity wynik w komórce, który jest zmieszany z liczbami i tekstem bez pamiętania żadnych formuł.

Uwaga:Aby zastosować te Suma na podstawie tego samego tekstu funkcje, po pierwsze, należy pobrać plik Kutools dla programu Excel, a następnie szybko i łatwo zastosuj funkcje.

Po zainstalowaniu Kutools dla programu Excelwykonaj następujące czynności:

1. Kliknij, aby wybrać komórkę, w której chcesz wyprowadzić wynik, zobacz zrzut ekranu:

Tips: Aby uniknąć odwołań cyklicznych, nie wstawiaj tej formuły do ​​pierwszego wiersza arkusza.

2. A następnie kliknij Kutools > Pomocnik formuły > Pomocnik formułyzobacz zrzut ekranu:

3, w Pomocnik formuł w oknie dialogowym, wykonaj następujące operacje:

  • Wybierz Statystyczny z Typ formuły Lista rozwijana;
  • W Wybierz formułę kliknij, aby wybrać Suma na podstawie tego samego tekstu opcja;
  • Następnie w Wprowadzanie argumentów wybierz zakres komórek zawierających tekst i liczby, które chcesz zsumować w pliku łodzie pole tekstowe, a następnie zaznacz komórkę tekstową, na podstawie której chcesz zsumować wartości w pliku Tekst pole tekstowe.
Tips: W Tekst pole tekstowe, należy zmienić domyślne bezwzględne odwołanie do komórki na względne odwołanie do komórki, aby poprawnie przeciągnąć formułę.

4. A następnie kliknij Ok przycisk, pierwszy wynik zostanie obliczony, a następnie przeciągnij uchwyt wypełniania, aby wypełnić formułę do innych komórek, patrz zrzut ekranu:


Sumuj komórki zawierające tekst i liczby, ignorując tekst za pomocą funkcji zdefiniowanej przez użytkownika

Jeśli masz następujące dane, które są zmieszane z tekstem i liczbami, i chcesz po prostu wyodrębnić i dodać tylko liczby w każdej komórce, jak możesz szybko to zakończyć w programie Excel?

Aby poradzić sobie z tym problemem, możesz utworzyć plik Funkcja zdefiniowana przez użytkownika, zrób tak:

1. Przytrzymaj ALT + F11 klawisze, aby otworzyć Okno Microsoft Visual Basic for Applications.

2. Kliknij wstawka > Modułi wklej następujący kod w Okno modułu.

Kod VBA: sumuj tylko liczby w komórce zawierającej liczby i tekst

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. Następnie zapisz i zamknij ten kod, wróć do arkusza i wprowadź tę formułę = SumNumbers (A2) (A2 wskazuje komórkę, którą chcesz zsumować liczby), a następnie przeciągnij uchwyt wypełniania w dół do komórek, które chcesz wypełnić formułą, a tylko liczby w każdej komórce są sumowane, patrz zrzut ekranu:


Więcej artykułów względnych:

  • Połącz zduplikowane wiersze i zsumuj wartości w programie Excel
  • W programie Excel , możesz zawsze napotkać ten problem, gdy masz zakres danych, który zawiera zduplikowane wpisy, a teraz chcesz połączyć zduplikowane dane i zsumować odpowiednie wartości w innej kolumnie, jak pokazano na poniższych zrzutach ekranu. Jak możesz rozwiązać ten problem?
  • Pomnóż dwie kolumny, a następnie zsumuj w programie Excel
  • W Excelu większość z nas może cierpieć z powodu pomnożenia dwóch kolumn, a następnie zsumowania, oczywiście możemy pomnożyć każdy element, a następnie zsumować, ale będzie to kłopotliwe, jeśli trzeba będzie obliczyć setki lub tysiące wierszy. W Excelu istnieje potężna funkcja - SUMPRODUCT, dzięki której możemy szybko pomnożyć dwie kolumny, a następnie je zsumować. W tym artykule omówię, jak zastosować tę funkcję.
  • Zsumuj pierwsze 3 lub N wartości na podstawie kryteriów w programie Excel
  • Zwykle możemy zsumować n pierwszych wartości z zakresu komórek za pomocą prostej formuły, ale czy kiedykolwiek próbowałeś zsumować n pierwszych wartości na podstawie pewnych kryteriów. W tym artykule przedstawię kilka formuł do sumowania n najwyższych wartości z określonymi kryteriami w programie Excel.
  • Sumuj unikalne wartości na podstawie kryteriów w programie Excel
  • Na przykład mam zakres danych, który zawiera kolumny Nazwa i Kolejność, aby teraz zsumować tylko unikalne wartości w kolumnie Zamówienie na podstawie kolumny Nazwa, jak pokazano na poniższym zrzucie ekranu. Jak szybko i łatwo rozwiązać to zadanie w programie Excel?
  • Policz lub sumuj komórki na podstawie kolorów czcionek w programie Excel
  • Jak możesz uzyskać liczbę komórek lub zsumować wszystkie komórki, które zawierają określony kolor czcionki w programie Excel? Na przykład mam zakres danych w arkuszu, jak pokazano na poniższym zrzucie ekranu, a teraz chcę odpowiednio policzyć lub zsumować komórki, które mają czerwony, niebieski i czarny kolor czcionki. Zwykle nie ma bezpośredniego sposobu na poradzenie sobie z tym zadaniem, tutaj opowiem o kilku sztuczkach, aby rozwiązać tę pracę.

Najlepsze narzędzia biurowe

Popularne funkcje: Znajdź, wyróżnij lub zidentyfikuj duplikaty   |  Usuń puste wiersze   |  Łącz kolumny lub komórki bez utraty danych   |   Okrągły bez wzoru ...
Super wyszukiwanie: Wiele kryteriów VLookup    Wiele wartości VLookup  |   Przeglądanie pionowe na wielu arkuszach   |   Wyszukiwanie rozmyte ....
Zaawansowana lista rozwijana: Szybko twórz listę rozwijaną   |  Zależna lista rozwijana   |  Lista rozwijana wielokrotnego wyboru ....
Menedżer kolumn: Dodaj określoną liczbę kolumn  |  Przesuń kolumny  |  Przełącz stan widoczności ukrytych kolumn  |  Porównaj zakresy i kolumny ...
Polecane funkcje: Fokus siatki   |  Widok projektu   |   Duży pasek formuły    Menedżer skoroszytów i arkuszy   |  Biblioteka zasobów (Automatyczny tekst)   |  Selektor dat   |  Połącz arkusze   |  Szyfruj/odszyfruj komórki    Wysyłaj e-maile według listy   |  Super filtr   |   Specjalny filtr (filtruj pogrubienie/kursywa/przekreślenie...) ...
15 najlepszych zestawów narzędzi12 Tekst Tools (Dodaj tekst, Usuń znaki, ...)   |   50 + Wykres rodzaje (Wykres Gantta, ...)   |   40+ Praktyczne Wzory (Oblicz wiek na podstawie urodzin, ...)   |   19 Wprowadzenie Tools (Wstaw kod QR, Wstaw obraz ze ścieżki, ...)   |   12 Konwersja Tools (Liczby na słowa, Przeliczanie walut, ...)   |   7 Połącz i podziel Tools (Zaawansowane wiersze łączenia, Podział komórki, ...)   |   ... i więcej

Zwiększ swoje umiejętności Excela dzięki Kutools for Excel i doświadcz wydajności jak nigdy dotąd. Kutools dla programu Excel oferuje ponad 300 zaawansowanych funkcji zwiększających produktywność i oszczędzających czas.  Kliknij tutaj, aby uzyskać funkcję, której najbardziej potrzebujesz...

karta kte 201905


Karta Office wprowadza interfejs z zakładkami do pakietu Office i znacznie ułatwia pracę

  • Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
  • Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
  • Zwiększa produktywność o 50% i redukuje setki kliknięć myszką każdego dnia!
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