Symbole wieloznaczne w Excelu: *, ? i ~ w LICZ.JEŻELI

Excel ma trzy symbole wieloznaczne do kryteriów i wyszukiwań: * pasuje do dowolnej liczby znaków (także do żadnego), ? pasuje do dokładnie jednego znaku, a ~ zamienia następną * albo ? z powrotem w zwykły znak. =LICZ.JEŻELI(A2:A7;"*apple*") (po angielsku COUNTIF) liczy komórki, które gdziekolwiek zawierają apple. Tabela pokazuje formuły po angielsku, =COUNTIF($A$2:$A$7,C2), ale możesz w niej wpisywać formuły także po polsku, ze średnikami.

FormułyResetuj

D2fx

=COUNTIF($A$2:$A$7,C2)

	A	B	C	D
1	Product		Pattern	Count
2	Apple juice		*apple*	4
3	Green apple		apple*	2
4	Pineapple		*juice	2
5	Orange juice		?????	0
6	Pear		*e	4
7	Apples

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.W polskim Excelu: =LICZ.JEŻELI($A$2:$A$7;C2)

• *apple* zawiera apple: 4 dopasowania, bo liczy się też Pineapple.
• apple* zaczyna się od apple: tylko Apple juice i Apples. LICZ.JEŻELI ignoruje wielkość liter.
• *juice kończy się na juice.
• ????? ma dokładnie pięć znaków. Żaden z tych produktów nie ma pięciu znaków, więc 0; wpisz Peach w A6, a policzy 1.
• *e kończy się na e.

Wpisz własny wzorzec w kolumnie C, na przykład *an* albo P*, a liczba się zaktualizuje.

Dopasowanie częściowe w WYSZUKAJ.PIONOWO i X.WYSZUKAJ

WYSZUKAJ.PIONOWO (VLOOKUP) przyjmuje symbole wieloznaczne w trybie dopasowania dokładnego (FAŁSZ jako ostatni argument). Dołącz symbol do wartości w formule, żeby w D2 wpisywać tylko pierwsze litery:

FormułyResetuj

E2fx

=VLOOKUP(D2&"*",A2:B6,2,FALSE)

	A	B	C	D	E
1	Product	Price		Starts with	Price
2	Apple juice	3.5		Pin	4
3	Green apple	1.2			4
4	Pineapple	4
5	Orange juice	3.2
6	Pear	0.9

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.W polskim Excelu: =WYSZUKAJ.PIONOWO(D2&"*";A2:B6;2;FAŁSZ)

Obie formuły znajdują Pineapple. Zmień D2 na juice: wzorzec juice* z WYSZUKAJ.PIONOWO wymaga, żeby tekst zaczynał się od juice, i zwraca #N/D! (po angielsku #N/A; tabele pokazują angielskie nazwy błędów), a wzorzec *juice* z X.WYSZUKAJ znajduje pierwszy produkt, który go zawiera, Apple juice. Jak każde dopasowanie dokładne, wyszukiwanie z symbolem wieloznacznym zwraca pierwszy pasujący wiersz, więc wzorzec musi być wystarczająco konkretny.

X.WYSZUKAJ (XLOOKUP) traktuje * i ? jako symbole wieloznaczne tylko wtedy, gdy jej piąty argument, match_mode, wynosi 2. Bez niego szuka gwiazdki dosłownie. PODAJ.POZYCJĘ (MATCH) przyjmuje symbole wieloznaczne z typem dopasowania 0, a X.DOPASUJ (XMATCH) z match_mode 2, tak jak X.WYSZUKAJ. Pozostałe argumenty opisuje strona WYSZUKAJ.PIONOWO.

FormułyResetuj

D2fx

	A	B	C	D
1	Product			Phone products
2	Smartphone
3	Headphones
4	Phone stand
5	Laptop bag
6	Charger

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.

Twoja kolej: W D2 policz produkty, których nazwa gdziekolwiek zawiera phone.

SprawdźPokaż odpowiedź

Suma i średnia z symbolem wieloznacznym

Każda funkcja przyjmująca kryteria czyta je tak samo, więc te same wzorce działają w SUMA.JEŻELI (SUMIF), SUMA.WARUNKÓW, ŚREDNIA.JEŻELI, ŚREDNIA.WARUNKÓW, MAKS.WARUNKÓW i MIN.WARUNKÓW:

FormułyResetuj

E2fx

=SUMIF(A2:A7,D2,B2:B7)

	A	B	C	D	E
1	Region	Sales		Pattern	Total
2	North-East	120		North*	285
3	North-West	95		*West	155
4	South	80		?????	150
5	South-West	60
6	East	110
7	North	70

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.W polskim Excelu: =SUMA.JEŻELI(A2:A7;D2;B2:B7)

North* dodaje każdy region, który zaczyna się od North, także samo North, bo * pasuje też do niczego. ????? dodaje regiony mające dokładnie pięć znaków: South i North.

FormułyResetuj

E2fx

	A	B	C	D	E
1	Region	Sales			West total
2	North-East	120
3	North-West	95
4	South	80
5	South-West	60
6	East	110
7	North	70

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.

Twoja kolej: W E2 zsumuj sprzedaż wszystkich regionów, których nazwa kończy się na West.

SprawdźPokaż odpowiedź

Szukanie prawdziwej gwiazdki albo znaku zapytania z ~

Aby policzyć tekst, który zawiera prawdziwą * albo ?, postaw przed nią tyldę. Samą tyldę zapisuje się jako ~~.

FormułyResetuj

C2fx

=COUNTIF(A2:A5,"*~**")

	A	B	C
1	Note		Count
2	Rated 5*		1
3	Why?		1
4	Done		4
5	5 stars

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.W polskim Excelu: =LICZ.JEŻELI(A2:A5;"*~**")

C2 liczy komórki zawierające dosłowną gwiazdkę (tylko A2), C3 te ze znakiem zapytania. C4 pokazuje drugą stronę: samo "*" pasuje do każdego tekstu, więc liczy każdą komórkę tekstową, tutaj 4. Pomija liczby i puste komórki, dlatego LICZ.JEŻELI(zakres;"*") to typowy sposób liczenia komórek z tekstem.

Które funkcje przyjmują symbole wieloznaczne

SZUKAJ.TEKST (SEARCH) przyjmuje symbole wieloznaczne wewnątrz formuły: =SZUKAJ.TEKST("b?d";"a bad day") zwraca w Excelu 3 (szczegóły na stronie ZNAJDŹ i SZUKAJ.TEKST). W FILTRUJ użyj jako warunku CZY.LICZBA(SZUKAJ.TEKST(...)) zamiast wzorca.

Częsty błąd: symbol wieloznaczny po =

Operator = nigdy nie czyta symboli wieloznacznych: =A2="*apple*" pyta, czy A2 zawiera siedem znaków *apple*. W Excelu, gdy A2 zawiera Green apple:

=A2="*apple*"                    FALSE
=IF(A2="*apple*","yes","no")     no

W polskim Excelu druga formuła to =JEŻELI(A2="*apple*";"yes";"no") i też zwraca no. Zamiast tego umieść test w LICZ.JEŻELI, która dla pojedynczej komórki zwraca 1 albo 0, albo użyj SZUKAJ.TEKST:

FormułyResetuj

B2fx

=IF(COUNTIF(A2,"*apple*"),"contains apple","no")

	A	B	C
1	Product	COUNTIF test	SEARCH test
2	Green apple	contains apple	contains apple
3	Pear	no	no

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.W polskim Excelu: =JEŻELI(LICZ.JEŻELI(A2;"*apple*");"contains apple";"no")

JEŻELI (IF) traktuje 1 z LICZ.JEŻELI jako PRAWDA, a 0 jako FAŁSZ. Wersja z SZUKAJ.TEKST w ogóle nie potrzebuje symboli wieloznacznych, bo SZUKAJ.TEKST i tak szuka tekstu w dowolnym miejscu komórki.