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
- Sumuj wartości, jeśli komórka zawiera określony lub częściowy tekst z funkcją SUMPRODUCT
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:
- 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:
- 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,"*"&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:
- 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(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, jeśli zaczyna się lub kończy określonym tekstem lub znakami
- Aby zsumować wartości, jeśli odpowiednie komórki zaczynają się lub kończą określoną wartością, możesz zastosować funkcję SUMA.JEŻELI z symbolem wieloznacznym (*), aby ją wyciągnąć. W tym artykule szczegółowo przedstawimy, jak korzystać z formuły.
- 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.
- Suma najmniejszych lub najniższych wartości N w oparciu o kryteria
- W poprzednim samouczku omówiliśmy sposób sumowania najmniejszych wartości n w zakresie danych. W tym artykule wykonamy dalszą zaawansowaną operację – sumowanie najniższych wartości n na podstawie jednego lub więcej kryteriów w Excelu.
Najlepsze narzędzia biurowe
Kutools dla programu Excel - pomaga wyróżnić się z tłumu
Kutools dla programu Excel oferuje ponad 300 funkcji, Pewność, że to, czego potrzebujesz, jest w zasięgu jednego kliknięcia...
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.