FILTRUJ (FILTER) w Excelu: wiele kryteriów, ORAZ i LUB

=FILTRUJ(A2:C7;B2:B7="North") (po angielsku FILTER) zwraca każdy wiersz A2:C7, w którym region w kolumnie B to North. Wpisujesz ją w jednej komórce, a pasujące wiersze rozlewają się do komórek poniżej i po prawej. Zmień region w kolumnie B na North albo jakieś North na South, a lista się zaktualizuje. Tabela pokazuje formułę po angielsku, =FILTER(A2:C7,B2:B7="North"), ale możesz w niej wpisywać formuły także po polsku, ze średnikami.

FormułyResetuj

E2fx

=FILTER(A2:C7,B2:B7="North")

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200		Finn	North	60
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.W polskim Excelu: =FILTRUJ(A2:C7;B2:B7="North")

Formułę zawiera tylko E2. Pozostałe wypełnione komórki w E:G to jej rozlany wynik: kliknij F3, a zobaczysz, że należy do formuły z E2. Jeśli w tym obszarze coś jest wpisane, FILTRUJ zamiast wierszy pokazuje #ROZLANIE! (po angielsku #SPILL!; tabele pokazują angielskie nazwy błędów; zobacz błąd #SPILL!).

Składnia funkcji FILTRUJ

=FILTER(array, include, [if_empty])

• array to to, co chcesz dostać: jedna kolumna, kilka kolumn albo cała tabela.
• include to warunek z jednym PRAWDA albo FAŁSZ na każdy wiersz array, na przykład B2:B7="North". Musi mieć dokładnie tyle wierszy co array. (Aby filtrować kolumny, podaj jedną wartość na kolumnę).
• if_empty to to, co pokazać, gdy żaden wiersz nie pasuje. Bez tego argumentu pusty wynik to błąd #OBL! (#CALC!).

FILTRUJ wymaga Excela 2021, Excela 2024 albo Microsoft 365. W Excelu 2019 i starszych pokazuje #NAZWA? (#NAME?), a filtrować można tam przyciskiem Filtruj na karcie Dane. Arkusze Google też mają FILTER, a tam każdy warunek można też podać jako osobny argument.

Porównania tekstu nie rozróżniają wielkości liter: B2:B7="north" pasuje do North. FILTRUJ zachowuje pierwotną kolejność wierszy; sortowanie wyniku to osobny krok, pokazany niżej.

Filtrowanie według wartości komórki

Wpisanie „North” na sztywno w formule oznacza edycję formuły za każdym razem. Wstaw wartość do komórki i porównuj z komórką. Wybierz inny region w F1, a wynik się dostosuje:

FormułyResetuj

E3fx

=FILTER(A2:C7,B2:B7=F1,"No sales")

	A	B	C	D	E	F	G
1	Name	Region	Sales		Region	North▾
2	Ann	North	120
3	Ben	South	80		Ann	North	120
4	Cara	North	200		Cara	North	200
5	Dan	East	150		Finn	North	60
6	Eve	South	95
7	Finn	North	60

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.W polskim Excelu: =FILTRUJ(A2:C7;B2:B7=F1;"No sales")

West nie ma wierszy, więc po jego wybraniu pojawia się tekst z if_empty, No sales.

Z liczbami jest tak samo. C2:C7>=F1 ze 100 w F1 zostawia każdy wiersz ze sprzedażą co najmniej 100, a C2:C7>F1 wymaga wartości ściśle większej.

FILTRUJ z wieloma kryteriami (ORAZ)

Aby zostawić wiersz tylko wtedy, gdy oba warunki są prawdziwe, pomnóż je. Ta formuła zwraca wiersze North ze sprzedażą powyżej 100:

FormułyResetuj

E2fx

=FILTER(A2:C7,(B2:B7="North")*(C2:C7>100))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.W polskim Excelu: =FILTRUJ(A2:C7;(B2:B7="North")*(C2:C7>100))

Ann (120) i Cara (200) przechodzą. Finn jest z North, ale jego 60 nie przekracza 100, więc odpada.

Dlaczego mnożenie: każdy warunek to kolumna wartości PRAWDA i FAŁSZ, a w arytmetyce PRAWDA liczy się jako 1, a FAŁSZ jako 0. Wiersz dostaje 1 tylko wtedy, gdy każdy czynnik to 1, więc * działa jak ORAZ. Każdy warunek potrzebuje własnych nawiasów, a łączyć możesz ich dowolnie wiele: (B2:B7="North")*(C2:C7>100)*(C2:C7<500).

Funkcja ORAZ() (AND) tu nie działa. ORAZ(B2:B7="North";C2:C7>100) sprowadza cały zakres do jednej wartości PRAWDA albo FAŁSZ zamiast jednej na wiersz, więc FILTRUJ dostaje zły kształt.

FILTRUJ z LUB

Dodaj warunki, aby zostawić wiersz, gdy co najmniej jeden z nich jest prawdziwy. Ta formuła zwraca wiersze North i East:

FormułyResetuj

E2fx

=FILTER(A2:C7,(B2:B7="North")+(B2:B7="East"))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200		Dan	East	150
5	Dan	East	150		Finn	North	60
6	Eve	South	95
7	Finn	North	60

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.W polskim Excelu: =FILTRUJ(A2:C7;(B2:B7="North")+(B2:B7="East"))

Wiersz, który spełnia oba warunki, daje w sumie 2, a FILTRUJ zostawia każdy wiersz, którego wynik nie jest 0, więc suma działa jak LUB. Oba sposoby można łączyć: ((B2:B7="North")+(B2:B7="East"))*(C2:C7>100) oznacza (North albo East) i powyżej 100. Tutaj zwraca to Ann, Carę i Dana.

FILTRUJ zwraca #OBL!, gdy nic nie pasuje

Gdy żaden wiersz nie przechodzi, FILTRUJ nie ma czego zwrócić. Bez trzeciego argumentu to błąd #OBL!; z nim dostajesz własny tekst:

FormułyResetuj

E2fx

=FILTER(A2:A7,B2:B7="West")

	A	B	C	D	E	F
1	Name	Region	Sales		No if_empty	With if_empty
2	Ann	North	120		#CALC!	No match
3	Ben	South	80
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

#CALC! Obliczenie nie ma wyniku, na przykład FILTRUJ, który nic nie znalazł.W polskim Excelu: =FILTRUJ(A2:A7;B2:B7="West")

E2 pokazuje #OBL!, a F2 No match. Zmień B3 z South na West, a obie formuły zwrócą Bena. Aby nie pokazywać niczego, użyj pustego tekstu: =FILTRUJ(A2:A7;B2:B7="West";"").

Ta tabela pokazuje też filtrowanie jednej kolumny: array to A2:A7, więc wracają tylko imiona. Aby dostać niektóre kolumny tabeli, a nie wszystkie, obejmij wynik funkcją WYBIERZ.KOLUMNY (CHOOSECOLS): =WYBIERZ.KOLUMNY(FILTRUJ(A2:C7;B2:B7="North");1;3) zwraca imiona i sprzedaż bez regionu. WYBIERZ.KOLUMNY wymaga Microsoft 365 albo Excela 2024.

Sortowanie wyniku FILTRUJ

FILTRUJ zwraca wiersze w kolejności, w jakiej występują w tabeli. Obejmij ją funkcją SORTUJ (SORT), aby uporządkować wynik: tutaj wiersze North posortowane według sprzedaży, od największej. 3 to kolumna wyniku, według której sortujesz, a -1 oznacza malejąco.

FormułyResetuj

E2fx

=SORT(FILTER(A2:C7,B2:B7="North"),3,-1)

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Cara	North	200
3	Ben	South	80		Ann	North	120
4	Cara	North	200		Finn	North	60
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.W polskim Excelu: =SORTUJ(FILTRUJ(A2:C7;B2:B7="North");3;-1)

Pierwsza jest Cara (200), potem Ann (120) i Finn (60). Aby zwrócić tylko najlepsze wiersze, obejmij to jeszcze funkcją WYCINEK (TAKE): =WYCINEK(SORTUJ(FILTRUJ(A2:C7;B2:B7="North");3;-1);2) zostawia pierwsze dwa (WYCINEK wymaga Microsoft 365 albo Excela 2024). Pozostałe opcje sortowania opisuje strona SORTUJ i SORTUJ.WEDŁUG.

FILTRUJ wiersze zawierające tekst

FILTRUJ nie ma symboli wieloznacznych, więc B2:B7="*th*" szuka dosłownego tekstu *th*. Aby zostawić wiersze, których imię zawiera dany tekst, sprawdź każdą komórkę funkcją SZUKAJ.TEKST (SEARCH), która zwraca pozycję, gdy tekst zostanie znaleziony, a błąd, gdy nie, i obejmij ją funkcją CZY.LICZBA (ISNUMBER):

FormułyResetuj

E2fx

=FILTER(A2:C7,ISNUMBER(SEARCH("an",A2:A7)))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Dan	East	150
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.W polskim Excelu: =FILTRUJ(A2:C7;CZY.LICZBA(SZUKAJ.TEKST("an";A2:A7)))

Zwraca to Ann i Dana: SZUKAJ.TEKST ignoruje wielkość liter, więc "an" pasuje też do An w Ann. Aby rozróżniać wielkość liter, użyj ZNAJDŹ zamiast SZUKAJ.TEKST.

Ćwiczenie: FILTRUJ z dwoma warunkami

FormułyResetuj

E2fx

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120
3	Ben	South	80
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

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

Twoja kolej: W E2 zwróć wiersze (wszystkie trzy kolumny) przedstawicieli z regionu South ze sprzedażą powyżej 85.

SprawdźPokaż odpowiedź

Ćwiczenie: FILTRUJ według komórki, z wartością zapasową

FormułyResetuj

F3fx

	A	B	C	D	E	F
1	Name	Region	Sales		Region	North
2	Ann	North	120
3	Ben	South	80		Names
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

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

Twoja kolej: W F3 wypisz imiona (tylko kolumna A) przedstawicieli z regionu wpisanego w F1. Jeśli takich nie ma, pokaż None.

SprawdźPokaż odpowiedź

Częste błędy z FILTRUJ

• Zakresy różnej wysokości. =FILTRUJ(A2:C7;B2:B6="North") sprawdza 5 wierszy dla tabeli z 6 wierszami, a Excel zwraca #ARG! (#VALUE!). include musi zaczynać się i kończyć w tych samych wierszach co array.
• Zera tam, gdzie źródło jest puste. FILTRUJ zwraca 0 dla pustej komórki w array. Przed filtrowaniem zamień puste komórki na pusty tekst: =FILTRUJ(JEŻELI(A2:C7="";"";A2:C7);B2:B7="North").
• Całe kolumny. =FILTRUJ(A:C;B:B="North") działa, ale jeśli sama formuła stoi w kolumnach od A do C, odwołuje się do siebie. Umieść wynik obok tabeli albo użyj stałego zakresu, na przykład A2:C1000.
• Cudzysłów wokół liczb. C2:C7>"100" porównuje liczby z tekstem i niczego nie zostawia. Pisz C2:C7>100.
• Oczekiwanie przycisku Filtruj. FILTRUJ kopiuje pasujące wiersze w nowe miejsce i nie rusza tabeli. Aby ukryć wiersze w samej tabeli, użyj Dane > Filtruj.