Symbole wieloznaczne w kryteriach

Do tej pory kryteria wymagały dokładnej zgodności wartości. Jednak rzeczywiste dane bywają nieuporządkowane. Czasem potrzebne jest znalezienie każdego kodu produktu, który zaczyna się od AB, albo każdego stanowiska, które zawiera słowo Manager.

Symbole wieloznaczne pozwalają dopasowywać fragmenty tekstu w funkcjach SUMIF, COUNTIF i AVERAGEIF. Całą pracę wykonują dwa znaki specjalne: gwiazdka i znak zapytania.

Są dokładnie dwa:

• * gwiazdka dopasowuje dowolną liczbę znaków, także zero
• ? znak zapytania dopasowuje dokładnie jeden znak

Umieść je wewnątrz tekstu kryterium ujętego w cudzysłowy. Wszystkie informacje o cudzysłowach i ampersandzie nadal mają zastosowanie.

Umieść gwiazdkę na końcu, aby oznaczyć dowolne zakończenie. Aby zliczyć każdy kod w A2:A10 zaczynający się od AB, umieść gwiazdkę po AB.

Dopasowanie obejmie AB, AB1, ABXYZ i wszystko, co zaczyna się od tych dwóch liter. Gwiazdka zastępuje dowolny tekst znajdujący się dalej.

Umieść gwiazdkę na początku, aby dopasować dowolny początek. Aby zsumować kwoty w B2:B10, gdy etykieta w kolumnie A kończy się słowem North, poprzedź to słowo gwiazdką.

W ten sposób zostaną znalezione Far North, Up North i zwykłe North. Gwiazdka zastępuje wszystko, co znajduje się przed tym słowem.

Ujmij tekst w gwiazdki po obu stronach, aby dopasować go w dowolnym miejscu komórki. Aby zliczyć tytuły w A2:A10 zawierające Manager:

Ta reguła znajdzie zarówno Sales Manager, Manager of IT, jak i Assistant Manager. Dwie gwiazdki pozwalają na wystąpienie dowolnego tekstu przed słowem i po nim.

Znak zapytania dopasowuje dokładnie jeden znak — ani więcej, ani mniej. Jeśli kody zawsze składają się z litery, po której następują dwie cyfry, aby zliczyć te zaczynające się od A, użyj po jednym znaku zapytania dla każdego nieznanego znaku.

\"A??\" dopasowuje A12 i A99, ale nie A1 ani A123, ponieważ długość musi wynosić dokładnie trzy znaki.

Aby zbudować warunek dopasowania początku tekstu na podstawie komórki, połącz komórkę z gwiazdką za pomocą ampersandu. Jeśli D1 zawiera AB, dopasuj wszystko, co zaczyna się od tego tekstu.

Operator & dołącza gwiazdkę do wartości w D1, tworząc \"AB*\". Teraz dopasowanie częściowe jest sterowane przez komórkę, a nie przez tekst wpisany na stałe.

Ta sama sztuczka tworzy wyszukiwanie tekstu zawierającego szukany fragment. Otocz komórkę gwiazdkami po obu stronach, używając dwóch ampersandów.

Wartość w D1 zostaje tu ujęta jako \"*value*\", więc zmiana D1 natychmiast zmienia tekst wyszukiwany przez formułę. To podstawa prostego podsumowania z wyszukiwaniem podczas pisania.

Co zrobić, jeśli dane rzeczywiście zawierają znak * lub ? i ma on zostać dopasowany dosłownie? Umieść przed nim tyldę ~.

Zatem \"~?\" wyszukuje dosłowny znak zapytania, a \"~*\" wyszukuje dosłowną gwiazdkę. Tylda informuje arkusz, aby potraktował następny znak jako zwykły tekst.

Jedno ważne ograniczenie: symbole wieloznaczne działają na tekście, a nie na liczbach. Nie można użyć *, aby dopasować fragment liczby, na przykład ceny.

Jeśli kody są zapisane jako rzeczywiste liczby, symbole wieloznaczne ich nie rozpoznają. Najpierw należy przekonwertować je na tekst albo użyć operatorów porównania dla zakresów liczbowych. Symbole wieloznaczne i operatory liczbowe służą do rozwiązywania różnych problemów.

Wyobraźmy sobie kody takie jak SALES-01, SALES-02 i HR-01 w kolumnie A oraz budżety w kolumnie B. Aby zsumować cały dział Sales, dopasuj każdy kod zaczynający się od SALES, po którym może występować dowolny tekst.

Jedna funkcja SUMIF z użyciem \"SALES*\" podsumuje wszystkie podkody naraz, bez potrzeby wymieniania ich pojedynczo. Zmień prefiks, aby podsumować inny dział.

Proszę sprawdzić swoją znajomość dopasowywania za pomocą symboli wieloznacznych.

Można już dopasowywać częściowy tekst w funkcjach matematycznych z rodziny IF. Należy pamiętać:

• * dopasowuje dowolną liczbę znaków, a ? dopasowuje dokładnie jeden znak.
• Warto użyć "AB*", aby sprawdzić początek tekstu, "*North", aby sprawdzić jego koniec, oraz "*Manager*", aby sprawdzić, czy zawiera dany fragment.
• Komórkę należy połączyć za pomocą ampersandu, jak w przykładzie D1&"*".
• Dosłowny znak * lub ? należy poprzedzić tyldą. Należy też pamiętać, że symbole wieloznaczne działają tylko w przypadku tekstu.

To kończy temat warunkowych obliczeń za pomocą SUMIF, COUNTIF i AVERAGEIF.