W tym poradniku przedstawię najlepsze metody wykonania autonumeracji, podam wady i zalety każdej z metod. Całość podzielimy na autonumerację zakresów i autonumerację tabel.

1. Czym jest automatyczne numerowanie w Excelu?
2. Automatyczne numerowanie w Excelu z zastosowaniem funkcji Jeżeli
3. Automatyczne numerowanie w Excelu z możliwością filtrowania i sortowania danych
4. Excel – automatyczne numerowanie danych w tabeli
5. Zachowanie kolejności numeracji przy filtrowaniu danych w tabeli w Excelu – SUMY.CZĘŚCIOWE()

Czym jest automatyczne numerowanie w Excelu?

Najlepiej wytłumaczę to na przykładzie z listą produktów, którą będę chciał ponumerować w kolumnie Lp.

Aby ponumerować te produkty, zastosujemy zwykłe wprowadzenie wartości 1 w komórce B3, a następnie przeciągając tę komórkę w dół z wciśniętym klawiszem CTRL otrzymamy statyczną listę z numeracją wierszy.

Natomiast nie jest to autonumeracja, ponieważ po usunięciu kilku produktów numery dla produktów nadal występują. A powinny podążać za produktami i się nie pojawiać.

W kolejnym dziale pokażę pierwszy sposób wprowadzenia automatycznej numeracji pozycji w zakresach danych

Automatyczne numerowanie w Excelu z zastosowaniem funkcji Jeżeli

Pierwszym najprostszym sposobem wykonania automatycznego numerowania jest zastosowanie funkcji JEŻELI(), która umożliwia logiczne porównanie wartości z oczekiwanym wynikiem przez sprawdzenie, czy warunek jest spełniony, czy nie.

Jeśli chciałbyś dowiedzieć się więcej o działaniu funkcji JEŻELI zobacz artykuł Funkcja JEŻELI Excel – JEŻELI z funkcjami ORAZ i LUB

W pierwszej komórce wpisz funkcję:

JEŻELI(C3=””;””;1)

• C3=”” – w tym argumencie sprawdzamy, czy komórka (niebieska) jest pusta
• ”” – wartość jeśli prawda
• 1 – wartość jeśli fałsz, czyli gdy komórka jest zapisana

Ta funkcja zwróci nam dla pierwszej pozycji wartość 1.

Teraz przejdźmy do komórki poniżej, w której wprowadzimy:

JEŻELI(C4=””;””;B3+1)

• C4=”” – w tym argumencie sprawdzamy, czy komórka (niebieska) jest pusta
• ”” – wartość jeśli prawda
• B3+1 – wartość jeśli fałsz (czyli gdy komórka niebieska jest zapisana) to dodajemy do czerwonej komórki wartość 1

Tym samym wynikiem tej funkcji jest wartość 2. Przekopiowując tę funkcję dla pozostałych pozycji, otrzymamy automatyczne numerowanie wszystkich pozycji.

Teraz po usunięciu kilku pozycji wartości nie będą się pojawiały.

ZALETY:

• Zastosowanie prostej intuicyjnej funkcji
• Możliwość przenoszenia zakresu danych w inne miejsce

WADY:

• Możliwość pojawienia się błędów, ponieważ w pierwszej i drugiej komórce jest inna formuła.
• Nie działa filtrowanie i sortowanie (o tym w kolejnym dziale)
• Numerowanie będzie obowiązywać tylko w zakresie komórek, w których jest wprowadzona funkcja.

Automatyczne numerowanie w Excelu z możliwością filtrowania i sortowania danych

Aby po zastosowaniu automatycznego numerowania w Excelu możliwe było sortowanie oraz filtrowanie danych, należy zastosować funkcję WIERSZ(), która zwraca numer komórki argumentu funkcji. Przykładowo funkcja =Wiersz(A4) zwróci nam wartość 4.

Modyfikujemy funkcję z poprzedniego działu i zastępujemy 3 argument funkcji JEŻELI() funkcją WIERSZ()

Z racji tego, że funkcja WIERSZ() odwołuje się do niebieskiej komórki C3 ta funkcja zwróci nam wartość 3 dlatego musimy odjąć od niej 2 aby uzyskać wynik równy 1.

Teraz wystarczy przekopiować tą funkcję dla pozostałych pozycji

ZALETY:

• Działa filtrowanie i sortowanie danych

WADY:

• Zakresu danych nie możemy przenosić w inne miejsce (automatyczna numeracja będzie wskazywała nieprawidłowe wyniki)
• Numerowanie będzie obowiązywać tylko w zakresie komórek, w których jest wprowadzona funkcja (zakres powiększa się gdy zastosujemy tabele – o tym w dalszej części artykułu).

Excel – automatyczne numerowanie danych w tabeli

Aby wprowadzić automatyczne numerowanie danych w tabeli, musimy najpierw zamienić zakres danych w tabelę.

W tym celu zaznacz zakres danych, a następnie naciśnij Ctrl+T. W pojawiającym się oknie Utwórz tabelę, wybierz Moja tabela ma nagłówki i kliknij OK.

Na tak utworzonej tabeli danych wprowadzimy automatyczną numerację pozycji

W pierwszej pozycji wprowadzimy podobną funkcję co poprzednio. Tym razem będziemy odejmować od numeru komórki niebieskiej „Kawa” (3) wiersz dla komórki czerwonej znajdującej się powyżej (2). Dzięki czemu wynik funkcji będzie równy 3 – 2 = 1

Pracując na tabelach nie odnosimy się do komórek, ale do całych kolumn, a znacznik @ wskazuje ten wiersz. Więcej o pracy na tabelach dowiesz się z artykułu Jak Zrobić Tabelę w Excelu – Tworzenie i Edycja Tabel

ZALETY:

• Możliwość przenoszenia w inne miejsce
• Możliwość dopisywania danych poza zakresem tabeli (tabela wchłania dane)
• Działa filtrowanie i sortowanie danych

WADY:

• Brak

Uwaga ! W przypadku filtrowania danych numeracja nie ulega zmianie. Jeśli chciałbyś, aby po przefiltrowaniu danych numeracja dostosowała się do ilości produktów, (dla poniższego przykładu od 1 do 4) to powinieneś zastosować funkcję SUMY.CZĘŚCIOWE()

Zachowanie kolejności numeracji przy filtrowaniu danych w tabeli w Excelu – SUMY.CZĘŚCIOWE()

Aby numeracja zachowywała swoją kolejność, nawet po wyfiltrowaniu danych w tabeli należy zmodyfikować formułę, zastępując w niej funkcję WIERSZ, funkcją SUMY.CZĘŚĆIOWE(), która składa się z co najmniej dwóch argumentów.

Pierwszy argument mówi nam co chcemy zliczyć. W naszym przypadku będzie to ilość niepustych komórek – 3.

W drugim argumencie wskazujemy zakres danych poniżej zaznaczony na czerwono. Pamiętaj o zablokowaniu komórki nagłówka, aby zablokować go podczas kopiowania dla pozostałych pozycji. Na koniec od uzyskanej wartości odejmujemy 1 (odjęcie wiersza nagłówka)

Teraz o wykonaniu filtrowania, automatyczne numerowanie nadal będzie wskazywać poprawne pozycje.

ZALETY:

• Możliwość przenoszenia w inne miejsce
• Możliwość dopisywania danych poza zakresem tabeli (tabela wchłania dane)
• Działa filtrowanie i sortowanie danych

WADY:

• Brak

Uwaga! W przypadku filtrowania danych pozycje są nadal automatycznie numerowane.

Tutaj pobierz plik z tego nagrania:

Kurs Excel

Chcesz dobrze nauczyć się Excela ? Nie trać więcej czasu na rzeczy które możesz wykonać jednym kliknięciem. Prawie 10 godzin praktycznej wiedzy, w której przedstawiam najprzydatniejsze możliwości Excela. Wszystkie 72 lekcje wideo oparte są na przykładach podsumowanych w ebooku. Dodatkowo 62 zadania do samodzielnego wykonania.

Zobacz mój kurs Excel