Przejdź do głównej zawartości

Sumuj, jeśli komórki zawierają określony tekst w innej kolumnie

Ten samouczek pokaże Ci, jak sumować wartości, jeśli komórki zawierają określony lub częściowy tekst w innej kolumnie. Jako przykład weź poniższy zakres danych, aby uzyskać łączne ilości produktów zawierających tekst „T-Shirt”, zarówno funkcja SUMA.JEŻELI, jak i funkcja SUMA.PRODUKT mogą poradzić sobie z tym zadaniem w programie Excel.


Sumuj wartości, jeśli komórka zawiera określony lub częściowy tekst z funkcją SUMA.JEŻELI

Aby zsumować wartości, jeśli komórka zawiera określony tekst w innej kolumnie, możesz użyć funkcji SUMA.JEŻELI z symbolem wieloznacznym (*). Ogólne składnie to:

Formuła ogólna z tekstem zakodowanym na stałe:

=SUMIF(range,"*text*",sum_range)
  • range: zakres danych, który chcesz ocenić za pomocą kryteriów;
  • *text*: Kryteria, na podstawie których chcesz zsumować wartości. W tym przypadku symbol wieloznaczny * służy do znajdowania dowolnej liczby znaków, do dopasowania wszystkich elementów zawierających określony tekst, umieszczania tekstu między dwoma znakami *. (Pamiętaj, że tekst i symbol wieloznaczny należy umieścić w podwójnych cudzysłowach.)
  • sum_range: zakres komórek z dopasowanymi wartościami liczbowymi, które chcesz zsumować.

Formuła ogólna z odwołaniem do komórki:

=SUMIF(range,"*"&cell&"*",sum_range)
  • range: zakres danych, który chcesz ocenić za pomocą kryteriów;
  • "*"&cell&"*": Kryteria, na podstawie których chcesz zsumować wartości;
    • *: Symbol wieloznaczny, który znajduje dowolną liczbę znaków.
    • komórka: Komórka zawiera określony tekst do wyszukania.
    • &: Ten operator konkatenacji (&) służy do łączenia odwołania do komórki z gwiazdkami.
  • sum_range: zakres komórek z dopasowanymi wartościami liczbowymi, które chcesz zsumować.

Po zapoznaniu się z podstawowym działaniem funkcji, użyj dowolnej z poniższych formuł, której potrzebujesz, a następnie naciśnij Wchodzę klucz do uzyskania wyniku:

=SUMIF($A$2:$A$12,"*T-shirt*",$B$2:$B$12)                     (Type the criteria manually)
=SUMIF($A$2:$A$12,"*"&D2&"*",$B$2:$B$12)                 
 (Use a cell reference)

Note: W tej funkcji SUMA.JEŻELI nie jest rozróżniana wielkość liter.


Sumuj wartości, jeśli komórka zawiera określony lub częściowy tekst z funkcją SUMPRODUCT

Funkcja SUMPRODUCT może również pomóc w dokończeniu tego zadania bez użycia symboli wieloznacznych. Tutaj powinieneś umieścić funkcje ISNUMBER i SEARCH wewnątrz funkcji SUMPRODUCT, ogólna składnia to:

=SUMPRODUCT(sum_range *(ISNUMBER(SEARCH(criteria,range))))
  • sum_range: zakres komórek z dopasowanymi wartościami liczbowymi, które chcesz zsumować;
  • criteria: Kryteria, na podstawie których chcesz zsumować wartości. Może to być odwołanie do komórki lub określony tekst, który zdefiniowałeś;
  • range: zakres danych, który chcesz ocenić za pomocą kryteriów;

Użyj dowolnej z poniższych formuł w pustej komórce i naciśnij Wchodzę klucz do zwrócenia wyniku:

=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12))))          (Type the criteria manually)
=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH(D2,$A$2:$A$12))))                   
(Use a cell reference)


Wyjaśnienie tego wzoru:

=PROD.SUMA($B$2:$B$12*(CZY.LICZBA(SZUKAJ("Koszulka";$A$2:$A$12))))

  • SZUKAJ("Koszulka", $A$2:$A$12): Funkcja SZUKAJ zwraca lokalizację określonego tekstu „T-Shirt” z zakresu danych A2:A12, więc otrzymasz tablicę taką jak ta: {5;#WARTOŚĆ!;#WARTOŚĆ!;7;#WARTOŚĆ! ;7;#WARTOŚĆ!;#WARTOŚĆ!;#WARTOŚĆ!;#WARTOŚĆ!;7}.
  • ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12))= ISNUMBER({5;#VALUE!;#VALUE!;7;#VALUE!;7;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7}): Ta funkcja CZY.LICZBA służy do testowania wartości liczbowych i zwraca nową tablicę: {PRAWDA;FAŁSZ;FAŁSZ;PRAWDA;FAŁSZ;PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA}.
  • $B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12)))= {347;428;398;430;228;379;412;461;316;420;449}*{TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}: Aby pomnożyć te dwie tablice, operacja matematyczna wymusza wartości TRUE i FALSE na 1 i 0. Zatem mnożenie dwóch tablic będzie wyglądało tak: {347;428;398;430;228;379;412;461;316;420;449}*{1;0;0;1;0;1; 0;0;0;0;1}={347;0;0;430;0;379;0;0;0;0;449}.
  • SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12)))) =SUMPRODUCT({347;0;0;430;0;379;0;0;0;0;449}):Na koniec funkcja SUMPRODUCT sumuje wszystkie wartości w tablicy, aby uzyskać wynik:1605.

Zastosowana funkcja względna:

  • SUMA:
  • Funkcja SUMA.JEŻELI może pomóc w sumowaniu komórek na podstawie jednego kryterium.
  • SUMPRODUCT:
  • Funkcja SUMPRODUCT może służyć do mnożenia dwóch lub więcej kolumn lub tablic, a następnie do uzyskania sumy iloczynów.
  • CZY.NUMER:
  • Funkcja Excel CZY.LICZBA zwraca PRAWDA, jeśli komórka zawiera liczbę, a FAŁSZ, jeśli nie.
  • SZUKAJ:
  • Funkcja SEARCH może pomóc w znalezieniu pozycji określonego znaku lub podciągu z podanego ciągu tekstowego

Więcej artykułów:

  • Suma najmniejszych lub najniższych wartości N
  • W programie Excel łatwo jest zsumować zakres komórek za pomocą funkcji SUMA. Czasami może być konieczne zsumowanie najmniejszych lub najniższych liczb 3, 5 lub n w zakresie danych, jak pokazano na poniższym zrzucie ekranu. W takim przypadku SUMPRODUCT wraz z funkcją MAŁY może pomóc rozwiązać ten problem w programie Excel.

Najlepsze narzędzia biurowe

Kutools dla programu Excel - pomaga wyróżnić się z tłumu

🤖 Pomocnik AI Kutools: Zrewolucjonizuj analizę danych w oparciu o: Inteligentne wykonanie   |  Wygeneruj kod  |  Twórz niestandardowe formuły  |  Analizuj dane i generuj wykresy  |  Wywołaj funkcje Kutools...
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 VLookup: Wiele kryteriów  |  Wiele wartości  |  W wielu arkuszach  |  Wyszukiwanie rozmyte...
Adw. Lista rozwijana: Łatwa lista rozwijana  |  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 kolumny z Wybierz Te same i różne komórki ...
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, Podziel komórki Excela ...)  |  ... i więcej

Kutools dla programu Excel oferuje ponad 300 funkcji, Pewność, że to, czego potrzebujesz, jest w zasięgu jednego kliknięcia...

Opis


Karta Office - Włącz czytanie i edycję na kartach w Microsoft Office (w tym Excel)

  • Jedna sekunda, aby przełączać się między dziesiątkami otwartych dokumentów!
  • Zmniejsz liczbę kliknięć myszą każdego dnia, pożegnaj się z dłonią myszy.
  • Zwiększa produktywność o 50% podczas przeglądania i edytowania wielu dokumentów.
  • Wprowadza wydajne karty do pakietu Office (w tym programu Excel), podobnie jak przeglądarki Chrome, Edge i Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations