Note: The other languages of the website are Google-translated. Back to English

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))

Uwagi: 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:

Wskazówki: 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 Zakres pole tekstowe, a następnie zaznacz komórkę tekstową, na podstawie której chcesz zsumować wartości w pliku Tekst pole tekstowe.
Wskazówki: 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

Kutools dla programu Excel rozwiązuje większość problemów i zwiększa produktywność o 80%

  • Ponowne użycie: Szybko włóż złożone wzory, wykresy i wszystko, czego używałeś wcześniej; Szyfruj komórki z hasłem; Utwórz listę mailingową i wysyłaj e-maile ...
  • Pasek Super Formula (łatwo edytować wiele wierszy tekstu i formuły); Układ do czytania (łatwe odczytywanie i edytowanie dużej liczby komórek); Wklej do filtrowanego zakresu...
  • Scal komórki / wiersze / kolumny bez utraty danych; Podziel zawartość komórek; Połącz zduplikowane wiersze / kolumny... Zapobiegaj zduplikowanym komórkom; Porównaj zakresy...
  • Wybierz Duplikat lub Unikalny Wydziwianie; Wybierz puste wiersze (wszystkie komórki są puste); Super Find i Fuzzy Find w wielu zeszytach ćwiczeń; Losowy wybór ...
  • Dokładna kopia Wiele komórek bez zmiany odwołania do formuły; Automatyczne tworzenie odniesień do wielu arkuszy; Wstaw punktory, Pola wyboru i nie tylko ...
  • Wyodrębnij tekst, Dodaj tekst, Usuń według pozycji, Usuń przestrzeń; Tworzenie i drukowanie podsumowań stronicowania; Konwertuj zawartość komórek i komentarze...
  • Super filtr (zapisz i zastosuj schematy filtrów do innych arkuszy); Zaawansowane sortowanie według miesiąca / tygodnia / dnia, częstotliwości i innych; Specjalny filtr pogrubieniem, kursywą ...
  • Połącz skoroszyty i arkusze robocze; Scal tabele na podstawie kluczowych kolumn; Podziel dane na wiele arkuszy; Konwersja wsadowa xls, xlsx i PDF...
  • Ponad 300 zaawansowanych funkcji. Obsługuje Office / Excel 2007-2021 i 365. Obsługuje wszystkie języki. Łatwe wdrażanie w przedsiębiorstwie lub organizacji. Pełne funkcje 30-dniowa bezpłatna wersja próbna. 60-dniowa gwarancja zwrotu pieniędzy.
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!
officetab dół
Sortuj komentarze według
Komentarze (47)
Brak ocen. Oceń jako pierwszy!
Ten komentarz został zminimalizowany przez moderatora na stronie
myślę, że jesteś prawdziwym nauczycielem i dobrym człowiekiem. powodzenia.
Ten komentarz został zminimalizowany przez moderatora na stronie
W komórce A1 programu Excel wprowadzam dane takie jak 5+6+3+1-2, a komórka A2 programu Excel daje wynik 13, jak to możliwe w programie Excel.
Ten komentarz został zminimalizowany przez moderatora na stronie
ok, zrozumiałem to. powodzenia.
Ten komentarz został zminimalizowany przez moderatora na stronie
Makro SumNumbers zaoszczędziło mi wielu godzin ręcznej pracy. Dziękuję Ci!
Ten komentarz został zminimalizowany przez moderatora na stronie
Czy można zmienić formułę SumNumbers, aby dodać wszystkie liczby w kolumnie mieszanych komórek? Na przykład każda kolumna ma 1A, 2B, 3C i chciałbym, aby formuła na dole kolumny mówiła mi 6.
Ten komentarz został zminimalizowany przez moderatora na stronie
czy można przepisać kod vba, aby zsumować wiele komórek zamiast jednego? Mam około 8 kolumn danych i chcę sumę na końcu.
Ten komentarz został zminimalizowany przez moderatora na stronie
To bardzo pomocne! Próbuję zmusić ARRAY do pracy z kolumną komórek, z których każda zawiera liczbę dziesiętną i tekst (np. .2 A; .5 C; .1 X itd.) i nadal otrzymuję wynik 0. Jeśli ja zmienić liczby na liczby całkowite, wynik jest poprawny. Pomoc?
Ten komentarz został zminimalizowany przez moderatora na stronie
GENUIS.... Bardzo dziękuję
Ten komentarz został zminimalizowany przez moderatora na stronie
Jak konwertować między tekstem a liczbą za pomocą KUTOOls?
Ten komentarz został zminimalizowany przez moderatora na stronie
Kutools
Treść
Liczba na słowo
Ten komentarz został zminimalizowany przez moderatora na stronie
Udało mi się doskonale wykorzystać tę formułę, ale chciałbym również dodać warunek, aby w formule SUMA zawierał tylko określone liczby, jeśli wartość w innej kolumnie = „wady”. Moja oryginalna formuła (przy użyciu Twojego oryginalnego posta) to: =SUM(IF(ISNUMBER(FIND("hrs",E17:E38))),VALUE(LEFT(E17:E38,FIND("hrs",E17:E38)-1 ))),0)) Gdzie E17:E38 zawiera tekst i liczby, które chcę zsumować A B17:B38 zawiera wartości „cechy” i „wady” wartość w kolumnie B = "wady"? Z góry dziękuję! Kimberly,
Ten komentarz został zminimalizowany przez moderatora na stronie
Oto prosty skrypt, który akceptuje jednowymiarowy i dwuwymiarowy zakres zaznaczenia komórek z każdą komórką zawierającą ciąg lub liczby, ale z każdą komórką zredukowaną do pojedynczych liczb i zwraca sumę wszystkich komórek. Opcja Funkcja jawna SUMCELLS(InputRange As Range) As Double Dim Arr() As Variant Arr = InputRange Dim R As Long Dim C As Long SUMCELLS = 2 For R = 0 To UBound(Arr, 1) ' Pierwszy wymiar tablicy to wiersze. For C = 1 To UBound(Arr, 1) ' Drugi wymiar tablicy to kolumny. Debug.Print Arr(R, ​​C) Dim NumericValue As Double Dim StringValue As String StringValue = Trim(CStr(Arr(R, ​​C))) If (Not (StringValue = "")) Then NumericValue = ONLYDIGITS(StringValue) SUMCELLS = SUMCELLS + NumericValue End If Next C Next R End Function Function ONLYDIGITS(s As String) As String Dim retval As String Dim i As Integer Dim period As Integer period = 2 retval = "" ' For i = 0 To Len(s) Dim char As String char = Mid(s, i, 1) If (char >= "1" And char
Ten komentarz został zminimalizowany przez moderatora na stronie
Jak uwzględnić ułamki dziesiętne w formule „=SumNumbers(A1)”? Dziękuję Ci!
Ten komentarz został zminimalizowany przez moderatora na stronie
Czy ktoś może mi pomóc z formułą sumy? Próbuję zsumować kolumnę z komórkami zawierającymi zarówno liczby, jak i tekst. Chcę zsumować liczby po lewej stronie litery tekstu. próbka: 3P30 2P30 2P30 2P30 1P20 3P30 1P20 3P50 3P30 3P60
Ten komentarz został zminimalizowany przez moderatora na stronie
Witaj, =SUM(IF(ISNUMBER(FIND(D1,$B$2:$B$8)),VALUE(LEFT($B$2:$B$8,FIND(D1,$B$2:$B$8))-1 )),0)) - zadziałało idealnie, poza tym, że mój tekst jest po prawej stronie. Próbowałem zmienić formułę, ale to nie działa... po prostu ciągle otrzymuję #ARG! Czy ktoś może pomóc?
Ten komentarz został zminimalizowany przez moderatora na stronie
Pamiętaj, aby po zmianie formuły nacisnąć Ctrl + Shift + Enter, a nie tylko Enter.
Ten komentarz został zminimalizowany przez moderatora na stronie
Jak zsumować wybrane słowo w komórce w Excelu Egzamin 1100/656480 1100 to kwota, a 656480 to recpt. nie. ale chcę w sumie tylko 1100
Ten komentarz został zminimalizowany przez moderatora na stronie
kwiecień maj czerwiec lipiec Razem 1100/65658 1200/65657 1500/65656 2100/65650 ? chcę sumować tylko 1100+1200+1500+2100 Inne nie tylko wartość Nr paragonu. proszę o pomoc
Ten komentarz został zminimalizowany przez moderatora na stronie
Siemanko! Mam dwa rodzaje liczb w jednej kolumnie, jeden to liczby numeryczne, drugi to znak, po którym następuje liczba (np. n20, n30 itp.). Chciałbym dodać te liczby przez „n”. Zastosowałem twoje wskazówki dotyczące „Podsumuj komórki zawierające tekst i liczby na podstawie określonego tekstu ze wzorem”, ale to nie zadziałało. Czy jest jakaś inna metoda? Z góry dziękuję!
Ten komentarz został zminimalizowany przez moderatora na stronie
cześć czy ktoś może mi pomóc jak dodać poniższe cyfry z literami? próbowałem to zrobić, ale to działa Myślę, że brakuje mi czegoś w formule = SUM (IF (ISNUMBER (FIND (D1, $ B $ 2: $ B $ 8)), VALUE (LEFT ($ B $ 2: $ B $ 8, ZNAJDŹ(D1,$B$2:$B$8)-1)),0)). proszę o pomoc. Dzięki.

2L
5D
10S
3L
11D
16S
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć Anno,
Najpierw należy wyodrębnić litery do innej kolumny pomocniczej, a następnie zastosować powyższą formułę, po wprowadzeniu tej formuły pamiętaj, aby nacisnąć jednocześnie klawisze Shift + Ctrl + Enter. zobacz następujący zrzut ekranu:
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć,

Dziękuję za ten post! Chętnie korzystam z kodu VBA i zastanawiałem się, czy byłoby możliwe posiadanie funkcji wielu zmiennych, aby można było sumować liczby z różnych komórek kolumn i na przykład napisać coś takiego jak SumNumbers (A1: C1).


Wielkie dzięki


John
Ten komentarz został zminimalizowany przez moderatora na stronie
Witaj, Giovanni,
Czy możesz podać przykład swojego problemu?
Tutaj możesz dołączyć zrzut ekranu.
Dziękuję Ci!
Ten komentarz został zminimalizowany przez moderatora na stronie
Chcę również sprawdzić, czy ta funkcja może sumować wiele komórek.
Ten komentarz został zminimalizowany przez moderatora na stronie
=SumaLiczb(D8:AH8)
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć,
Jak pomnożyć 10 sztuk * 200,

nie 10*200
Nie ma tu jeszcze żadnych komentarzy
Pokaż więcej

Bądż na bieżąco

Prawa autorskie © 2009 - www.extendoffice.com. | Wszelkie prawa zastrzeżone. Zasilany przez ExtendOffice, | Mapa strony
Microsoft i logo Office są znakami towarowymi lub zastrzeżonymi znakami towarowymi Microsoft Corporation w Stanach Zjednoczonych i / lub innych krajach.
Chronione przez Sectigo SSL