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

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)

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

Chcesz szybko i perfekcyjnie zakończyć swoją codzienną pracę? Kutools dla programu Excel oferuje 300 zaawansowanych zaawansowanych funkcji (Łącz skoroszyty, sumuj według koloru, dziel zawartość komórek, konwertuj daty itd.) i oszczędzaj 80% czasu.

  • Zaprojektowany dla 1500 scenariuszy pracy, pomaga rozwiązać 80% problemów z Excelem.
  • Zmniejsz tysiące kliknięć klawiatury i myszy każdego dnia, odciąż zmęczone oczy i dłonie.
  • Zostań ekspertem Excela w 3 minuty. Nie musisz już pamiętać żadnych bolesnych formuł i kodów VBA.
  • 30-dniowy nieograniczony bezpłatny okres próbny. 60-dniowa gwarancja zwrotu pieniędzy. Bezpłatna aktualizacja i wsparcie przez 2 lata.
Wstążka programu Excel (z zainstalowanym Kutools dla programu Excel)

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 zakładki do pakietu Office (w tym Excel), podobnie jak Chrome, Firefox i nowy Internet Explorer.
Zrzut ekranu programu Excel (z zainstalowaną kartą Office)
Sortuj komentarze według
Komentarze (0)
Brak ocen. Oceń jako pierwszy!
Nie ma tu jeszcze żadnych komentarzy
Zostaw swój komentarz
Publikowanie jako gość
×
Oceń ten post:
0   Postacie
Sugerowane lokalizacje

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