Numerowanie wierszy wstawione za pomocą autowypełniania nie spełnia swojej funkcji w przypadku, gdy dane są filtrowane. Ciągłość numeracji nie jest wówczas zachowywana. Zastosujmy zatem sprytną formułę, która będzie dostosowywać numerację do pogrupowanych wierszy.
Korzyść z zastosowania
formuły jest oczywista: nie musimy każdorazowo sprawdzać, ile pozycji zestawienia spełnia określone kryteria filtrowania, czyli zliczać przefiltrowanych wierszy. Przykładowe dane przedstawia rysunek 1.
Rys. 1. Przykładowe dane
W celu zastosowania takiej numeracji:
1. Zaznaczamy całą kolumnę A, poprzez kliknięcie jej nagłówek z oznaczeniem literowym i wciskamy kombinację klawiszy Ctrl + Shift + = (znak równości).
2. Nowej kolumnie nadajemy nazwę: Lp.
3. W komórce A2 wprowadzamy następującą formułę:
=JEŻELI(B2="";"";SUMY.POŚREDNIE(3;B$2:B2))
Uwaga
W nowszych wersjach Excela funkcję SUMY.POŚREDNIE należy zamienić na funkcję SUMY.CZĘŚCIOWE.
4. Skopiujmy ją poniżej na większą liczbę wierszy (co najmniej o jeden więcej) niż jest aktualnie wypełnionych w tabeli. Numeracja została nadana, jak to przedstawia rysunek 2.
Rys. 2. Formułę skopiujmy o jeden wiersz poniżej ostatniego wypełnionego
5. Uruchamiamy autofiltr i sprawdzamy, czy wszystko działa poprawnie.
Rys. 3. Numeracja została automatycznie dostosowana
Wyjaśnienie działania formuły:
Funkcja JEŻELI sprawdza, czy komórka po prawej stronie jest pusta. Jeśli tak, wynikiem działania funkcji jest także pusty ciąg znaków, a jeżeli nie, wykonywana jest operacja określona w drugim argumencie funkcji JEŻELI. Funkcja SUMY.POŚREDNIE używa dwóch argumentów. Pierwszy z nich to liczba z zakresu od 1 do 11 określająca funkcję, jaka zostanie użyta do obliczenia sum pośrednich (liczba 3 odpowiada
funkcji ILE.NIEPUSTYCH). Sama funkcja SUMY.POŚREDNIE, wbrew temu co sugeruje nazwa, może wyliczać nie tylko sumę, ale także iloczyn, średnią, maksimum, minimum, itd. W drugim argumencie wskazujemy zakres komórek. Dzięki zastosowaniu odpowiedniego adresowania będzie się rozszerzał w miarę kopiowania formuły w dół.