Liczba pozycji dostępnych do filtrowania jest ograniczona. Excel nie może filtrować kolumn, w których liczba elementów przekracza 999 (nie liczba wierszy).
Aby filtrować, gdy jest więcej niż 999 pozycji, użyj filtru zaawansowanego.
Aby stworzyć zaawansowany filtr, użyjemy funkcji „OFFSET” i „COUNTA” w programie Microsoft Excel.
LICZBA: Zwraca liczbę komórek zawierających wartości.
Składnia funkcji „ILE.LICZB”: =ILE.LICZB (wartość1, wartość2, wartość3… .)
Przykład: W zakresie A1:A5 komórki A2, A3 i A5 zawierają wartości, a komórki A1 i A4 są puste. Wybierz komórkę A6 i wpisz formułę-
=ILE.LICZB(A1:A5) funkcja zwróci 3
ZRÓWNOWAŻYĆ: Zwraca odwołanie do zakresu, który jest przesunięty o liczbę wierszy i kolumn od innego zakresu lub komórki.
Składnia funkcji PRZESUNIĘCIE: = PRZESUNIĘCIE (odniesienie, wiersze, kolumny, wysokość, szerokość)
Referencja:- To jest komórka lub zakres, od którego chcesz dokonać przesunięcia.
Wiersze i kolumny do przeniesienia: - Liczba rzędów, które chcesz przesunąć z punktu początkowego i oba mogą być dodatnie, ujemne lub zerowe.
Wysokość i szerokość: - Jest to rozmiar zakresu, który chcesz zwrócić. To pole jest opcjonalne.
Weźmy przykład, aby zrozumieć funkcję przesunięcia w programie Excel.
Mamy dane w zakresie A1:D10. Kolumna A zawiera kod produktu, kolumna B zawiera ilość, kolumna C zawiera koszt na produkt, a kolumna D zawiera koszt całkowity. Musimy zwrócić wartość komórki C5 w komórce E2.
Aby uzyskać pożądany efekt, musimy wykonać poniższe kroki.
- Wybierz komórkę E2 i napisz formułę.
- =PRZESUNIĘCIE(A1,4,2,1,1)i naciśnij Enter na klawiaturze.
- Funkcja zwróci wartość komórki C5.
W tym przykładzie musimy uzyskać wartość z komórki C5 do E2. Nasza komórka odniesienia jest pierwszą komórką w zakresie, który jest A1, a C5 to 4 rzędy poniżej i 2 kolumny na prawo od A1. Stąd formuła to =PRZESUNIĘCIE(A1,4,2,1,1) lub =PRZESUNIĘCIE(A1,4,2) (ponieważ 1,1 jest opcjonalne).
Teraz weźmy przykład, aby pobrać ostatnią wartość z listy dynamicznej.
Mamy wiele nazw krajów. Teraz, jeśli dodamy więcej krajów do tej listy, powinna ona być automatycznie dostępna na liście rozwijanej.
Aby przygotować filtr zaawansowany, wykonaj poniższe czynności:-
- Wybierz komórkę B2.
- Przejdź do zakładki Dane, wybierz Walidacja danych z grupy Narzędzia danych.
- Pojawi się okno dialogowe „Weryfikacja danych”. W zakładce "Ustawienia" wybierz "Niestandardowe" z rozwijanej listy Zezwalaj.
- Pole formuły zostanie aktywowane.
- Wpisz formułę w tym polu.
- =PRZESUNIĘCIE(A:A;1,0;ILE.LICZBA(A:A)-1,1).
- Kliknij OK.
- Na tym etapie ostatnia zaktualizowana komórka to A11.
- Aby sprawdzić, czy sprawdzanie poprawności danych działa poprawnie, dodaj nazwę miasta w komórce A12.
Gdy tylko dodasz wpis w A12, zostanie on dodany do listy rozwijanej.
W ten sposób możemy dodać więcej wpisów niż 999 pozycji w Microsoft Excel.