Opis
Użyj tej funkcji, aby wyodrębnić wiersze z zakresu lub tablicy, które spełniają określone warunki.
Składnia
FILTR(tablica, zawiera, [jeśli_pusta])
Wejścia
Ta funkcja akceptuje następujący argument:
| Nazwa | Wymagane | Opis | Prawidłowe dane wejściowe |
|---|---|---|---|
szyk |
Tak | Zakres lub tablica do filtrowania. | Komórka, zakres komórek, tablica dynamiczna lub formuła, której wynikiem jest jeden z powyższych elementów. |
uwzględnij |
Tak | Tablica logiczna lub wyrażenie logiczne określające, które wiersze mają zostać zwrócone. | Wartość logiczna (PRAWDA lub FAŁSZ), liczba (0 to FAŁSZ, inne liczby to PRAWDA), odwołanie do komórki zawierającej jedną z tych wartości lub formuła, która zwraca jedną z tych wartości. |
jeśli_pusty |
Nie | Wartość zwracana, jeśli żaden wiersz nie spełnia warunków filtru. | Ciąg tekstowy, który zostanie wyświetlony, jeśli nie zostaną znalezione żadne pasujące wiersze. |
Przykłady
Dane próbki
| A | B | C | D | ||
|---|---|---|---|---|---|
| 1 | Nazwa | Wiek | Miejscowość | Wynagrodzenie | |
| 2 | Jan | 35 | Nowy Jork | 75000 | |
| 3 | Alicja | 28 | Chicago | 65000 | |
| 4 | Pion | 42 | Los Angeles | 85000 | |
| 5 | Jerzy | 22 | Denver | 55000 | |
| 6 | Helen | 30 | Scottsdale | 71000 |
Przykładowe formuły
Zakresy muszą się zgadzać! Jeśli używasz wielu zakresów z funkcją FILTER, wartości wierszy muszą być takie same dla wszystkich zakresów określonych jako część wyrażenia filtru.
| Przypadek użycia | Formuła | Wyjaśnienie i wynik | ||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Utwórz zbiór danych obejmujący wszystkich pracowników powyżej 30. roku życia. | =FILTRUJ(A1:D6, B1:B6>30) |
Ta formuła bada zbiór danych (A1:D6), a następnie wyodrębnia wiersze, w których Wiek jest większy niż 30.
|
||||||||||||||||||||||||||
| Utwórz zbiór danych obejmujący wszystkich pracowników powyżej 50 roku życia i przygotuj niestandardową wiadomość, jeśli nie ma żadnego pracownika. | =JEŻELIBŁĄD(FILTR(A2:D6, B2:B6>50), "Brak") |
Ta formuła używa FILTRA do sprawdzenia zestawu danych (A2:D6), a następnie wyodrębnia wiersze, w których Wiek jest większy niż 50. Ponieważ ich nie ma, generuje to błąd, który jest obsługiwany przez IFERROR w celu wyświetlenia ciągu „Brak”. Korzystając z powyższego zestawu danych, można uzyskać następujące wyniki:
|
||||||||||||||||||||||||||
| Utwórz zbiór danych obejmujący wszystkich pracowników powyżej 25 roku życia, posortowanych według wynagrodzenia w kolejności malejącej. | =SORTOWANIE(FILTR(A2:D6, B2:B6>25), 4, -1) |
Ta formuła używa FILTRA do sprawdzenia zestawu danych (A1:D6), a następnie wyodrębnia wiersze, w których Wiek jest większy niż 25. Korzystając z powyższego zestawu danych, można uzyskać następujące wyniki:
|
||||||||||||||||||||||||||
| Znajdź pracowników zarabiających więcej niż przeciętna pensja. | =FILTRUJ(A2:D6, D2:D6> ŚREDNIA(D2:D6)) |
Ta formuła określa ŚREDNIĄ wynagrodzeń (D2:D6), a następnie używa funkcji FILTR do analizy zbioru danych (A2:D6) i wyodrębnia wiersze, w których wynagrodzenie jest wyższe od średniej. Korzystając z powyższej tabeli danych, można uzyskać następujące wyniki:
|
||||||||||||||||||||||||||
| Zidentyfikuj pracowników, którzy mają mniej niż 30 lat i zarabiają ponad 60 000. | =FILTR(A1:D6,(B1:B6<30)*(D1:D6>60000)) |
Ta formuła określa ŚREDNIĄ wynagrodzeń (D2:D6), a następnie używa funkcji FILTR do analizy zbioru danych (A2:D6) i wyodrębnia wiersze, w których wynagrodzenie jest wyższe od średniej.
|
Uwagi
- Podobnie jak wszystkie funkcje tablicowe, funkcja FILTER dynamicznie wypełnia wyniki w sąsiadujących komórkach, zaczynając od komórki zawierającej funkcję FILTER.
- FILTR:
- Obsługuje złożone filtrowanie wielowarunkowe.
- Zwraca wiersze spełniające określone kryteria logiczne.
- Obsługuje wiele testów logicznych przy użyciu * („AND”) lub + („OR”).
Na przykład formuła filtrująca zakres w celu wyświetlenia rekordów, w których wartości w kolumnie B to „Czekolada” lub „Kawa”, wyglądałaby następująco:=FILTER(A2:C100, (B2:B100="Czekolada") + (B2:B100="Kawa")).
Porady
- Jeśli masz wiersz nagłówka i nie chcesz, aby nagłówki były uwzględniane, rozpocznij wartości tablicy o wiersz niżej (na przykład w powyższym zestawie danych zacznij od komórki A2, a nie od A1).
- FILTR można łączyć z innymi funkcjami, w tym:
- CHOOSEROWS: Dalsze udoskonalanie przefiltrowanych wyników.
- CHOOSECOLS: Wybierz konkretne kolumny z przefiltrowanych danych.
- SORT: Uporządkuj przefiltrowane wyniki.
Powiązane funkcje
Oto inne funkcje formuł tablicowych obsługiwane w arkuszach kalkulacyjnych Workiva.