Pobierz przykładowy skoroszyt
W tym samouczku zademonstrujemy, jak używać funkcji SUMIFS i znaków wieloznacznych do sumowania danych odpowiadających komórkom zawierającym określony tekst w programie Excel i Arkuszach Google.
Suma, jeśli tekst zawiera
Najpierw pokażemy, jak sumować dane związane z komórkami zawierającymi określony tekst za pomocą funkcji SUMIFS.
Funkcja SUMA.WARUNKI sumuje wiersze danych, które spełniają określone kryteria. Jego składnia to:
Ten przykład zsumuje wszystkie Wyniki z Nazwa stanu który zawiera „Dakota” przy użyciu funkcji SUMIFS i symbolu wieloznacznego *.
| 1 | =SUMA.WARUNKI(C3:C9;B3:B9;"*Dakota*") |
Znak * pozwala na zajęcie jego miejsca przez dowolną liczbę (w tym zero) innych znaków.
W tym przykładzie służy do znajdowania wszystkich komórek zawierających tekst „Dakota”. W wyszukiwaniu nie jest rozróżniana wielkość liter, więc „dakota” jest uważana za to samo co „Dakota” lub „DAKOTA”. Północna Dakota oraz Południowa Dakota zawierają „Dakota” i dlatego są uwzględnione w sumie.
Suma, jeśli tekst zaczyna się od
Znaku * można również użyć do wyszukiwania komórek rozpoczynających się określonym tekstem:
| 1 | =SUMA.WARUNKI(C3:C9;B3:B9;"Nowe*") |
Nowy Jork, New Jersey, oraz Nowy Meksyk zaczynają się od „Nowy” i dlatego są uwzględnione w sumie. Zauważ, że w przypadku wyszukiwanego terminu „Nowy *” komórka tekstowa musi zaczynać się od „Nowy”; samo zawieranie tych znaków nie wystarczy.
Suma, jeśli tekst kończy się na
Podobnie możemy zsumować wszystkie Wyniki dla Stany kończące się na „o” używając:
| 1 | =SUMA.WARUNKI(C3:C9;B3:B9;"*o") |
Nowy Meksyk oraz Ohio kończą się na „o” i dlatego są uwzględniane w sumie.
Używając ? Znak wieloznaczny
Ten ? znak może służyć do reprezentowania dowolnego pojedynczego znaku w ciągu tekstowym.
Ten przykład znajduje wszystkie Nazwy państwowe zaczynając od „Nowy”, po którym następuje dokładnie 7 znaków (ze spacjami).
| 1 | =SUMA.WARUNKI(C3:C9;B3:B9;"Nowe???????") |
New Jersey oraz Nowy Meksyk spełniają te kryteria, ale Nowy Jork nie, ponieważ po „Nowym” jest tylko 5 znaków w Nowy Jork.
Zauważ, że * i ? W razie potrzeby można łączyć symbole wieloznaczne, aby uzyskać bardzo szczegółowe polecenia wyszukiwania. W następnym przykładzie znajduje się Nazwy państwowe które zaczynają się od „N” i zawierają „o” przed ostatnim znakiem ciągu. To wyklucza Nowy Meksyk; zaczyna się od „N”, ale nie ma „o” przed ostatnim znakiem.
| 1 | =SUMA.WARUNKI(C3:C9;B3:B9;"Nie*nie?*") |
Funkcjonalność ta jest szczególnie przydatna podczas wyszukiwania kodów produktów, kodów pocztowych lub numerów seryjnych, gdzie pozycja każdego znaku ma określone znaczenie.
Używanie znaku ~ (tyldy)
Znak specjalny ~ (znany jako a tylda) pozwala nam traktować * lub ? znaki tak, jakby były prostymi wartościami tekstowymi i nie zachowują się jak symbole wieloznaczne.
W poniższym przykładzie musimy zsumować Poziom zapasów kiedy Nazwa produktu dokładnie pasuje do tekstu „Produkt?”:
| 1 | =SUMA.WARUNKI(C3:C8;B3:B8;"Iloczyn ~?") |
~ bezpośrednio przed * lub ? znak zamienia go z powrotem w wartość tekstową, więc wyszukiwane hasło „Produkt ~?” służy do znalezienia dokładnego dopasowania tekstu do „Produktu ?”.
Łączenie symboli wieloznacznych SUMIFS z odwołaniami do komórek
Zwykle nie jest dobrą praktyką zakodowanie wartości na stałe w formułach. Zamiast tego bardziej elastyczne jest używanie oddzielnych komórek do określania wartości naszych wyszukiwanych haseł.
Aby sprawdzić, czy komórki zawierają tekst przechowywany w komórce E3, możemy użyć funkcji SUMIFS z odwołaniem do komórki i * symbolami wieloznacznymi:
| 1 | =SUMA.WARUNKI(C3:C9;B3:B9;"*"&E3&"*") |
Zwróć uwagę, że tekst „Dakota” został zastąpiony odwołaniem do komórki &E3&, a znaki * zostały umieszczone w cudzysłowie („”).
Można również łączyć ze sobą wiele odwołań do komórek i znaków wieloznacznych. Znaleźć Nazwy państwowe które zaczynają się od tekstu w komórce E3 i zawierają tekst w komórce F3, po którym następuje co najmniej 1 dodatkowy znak, można użyć następującej formuły:
| 1 | =SUMA.WARUNKI(C3:C9;B3:B9;E3&"*"&F3&"?*") |
Blokowanie odwołań do komórek
Aby ułatwić czytanie naszych formuł, pokazaliśmy formuły bez zablokowanych odwołań do komórek:
| 1 | =SUMA.WARUNKI(C3:C9;B3:B9;"*"&E3&"*") |
Ale te formuły nie będą działać poprawnie po skopiowaniu i wklejeniu w innym miejscu pliku. Zamiast tego powinieneś używać zablokowanych odwołań do komórek w następujący sposób:
| 1 | =SUMATY($C$3:$C$9;$B$3:$B$9;"*"&E3&"*") |
Przeczytaj nasz artykuł na temat blokowania odwołań do komórek, aby dowiedzieć się więcej.
Sumuj, jeśli komórka zawiera określony tekst za pomocą symboli wieloznacznych w Arkuszach Google
Te formuły działają dokładnie tak samo w Arkuszach Google, jak w programie Excel.
