Automatyczne numerowanie wierszy w Excelu

Usuwasz dwa produkty z listy i w kolumnie Lp zostaje 1, 2, 5, 6. Dopisujesz pozycję na dole i trzeba numerować od nowa. Po przefiltrowaniu numery skaczą, bo pamiętają miejsca, których na ekranie już nie ma.

W tym poradniku przechodzimy automatyczne numerowanie w Excelu po kolei: od numerów wpisanych ręcznie, przez formułę z funkcją JEŻELI (IF), numerowanie odporne na sortowanie, numerowanie w tabeli Excela, aż po takie, które po filtrowaniu zaczyna od 1 i idzie bez dziur. Przy każdym sposobie wady i zalety, na tej samej liście produktów.

Powód tych kłopotów jest zawsze ten sam: wpisany numer jest wartością, a nie wynikiem. Nie wie nic o wierszu, w którym stoi, ani o tym, czy obok coś w ogóle jest. Dopóki numer nie stanie się formułą, żadne przeciąganie go nie naprawi.

W dalszej części poradnika:

1. Jak ponumerować wiersze w Excelu i dlaczego numery się rozjeżdżają
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. Numeracja bez dziur po przefiltrowaniu tabeli - SUMY.CZĘŚCIOWE
6. Podsumowanie - który sposób wybrać

Jak ponumerować wiersze w Excelu i dlaczego numery się rozjeżdżają

Kolumna z numerami to w arkuszach zwykle Lp., czyli liczba porządkowa. Pokażę to na liście produktów, którą będę numerował właśnie w takiej kolumnie.

Najprościej wpisać 1 w komórce B3 i przeciągnąć ją w dół za uchwyt w prawym dolnym rogu, trzymając Ctrl. Excel zamiast powielać jedynkę, doda do niej kolejne numery.

Do tego samego prowadzą jeszcze dwie drogi. Możesz wpisać 1 i 2 w dwóch komórkach, zaznaczyć obie i przeciągnąć uchwyt w dół, a Excel sam rozpozna krok. Albo wpisać 1 i wybrać Narzędzia główne > Wypełnienie > Seria danych, gdzie krok i wartość końcową podajesz z klawiatury. Efekt jest w każdym przypadku ten sam.

To jednak nie jest jeszcze autonumeracja. Usuń kilka produktów, a numery zostaną na swoich miejscach, bo nie mają z tymi produktami nic wspólnego. Numeracja powinna iść za danymi: jest produkt, jest numer; nie ma produktu, nie ma numeru.

W kolejnym dziale pierwszy sposób, który to naprawia.

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="" - warunek: czy komórka z produktem jest pusta
• "" - co wstawić, gdy produktu nie ma: nic
• 1 - co wstawić, gdy produkt jest: numer 1

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="" - ten sam warunek, wiersz niżej
• "" - pusto, gdy produktu nie ma
• B3+1 - numer z komórki wyżej powiększony o 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.

🚀 Następny poziom

JEŻELI z jednym warunkiem to początek. Dalej zaczyna się liczenie na warunkach

✓SUMA.JEŻELI i SUMA.WARUNKÓW, czyli sumowanie tylko tego, co pasuje

✓LICZ.WARUNKI i ŚREDNIA.WARUNKÓW na tych samych danych

✓Funkcja WARUNKI zamiast zagnieżdżonych JEŻELI

✓40 lekcji i 17 zadań z rozwiązaniami · gwarancja zwrotu 30 dni

Zobacz kurs

🛡 Gwarancja zwrotu 30 dni · ⚡ Dostęp natychmiastowy

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

Żeby numeracja przetrwała sortowanie i filtrowanie, numer nie może brać się z komórki wyżej, tylko z samego wiersza. Robi to funkcja WIERSZ, która zwraca numer wiersza wskazanej komórki: =WIERSZ(A4) zwróci 4.

Bierzemy formułę z poprzedniego działu i zamieniamy jej trzeci argument na WIERSZ:

=JEŻELI(C3="";"";WIERSZ(C3)-2)

Tę samą formułę kopiujesz w dół na wszystkie pozycje.

WIERSZ odwołuje się tu do komórki C3, więc zwraca 3. Odejmujemy 2, czyli tyle wierszy, ile stoi nad pierwszą pozycją, i pierwszy produkt dostaje numer 1.

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 wprowadzamy numerację pozycji.

W pierwszej pozycji wpisujemy podobną formułę. Tym razem od numeru wiersza z produktem odejmujemy numer wiersza nagłówka tabeli, więc pierwszy produkt daje 3 - 2 = 1:

=JEŻELI(C3="";"";WIERSZ([@Produkt])-WIERSZ(Tabela3[[#Nagłówki];[Produkt]]))

Nazwa Tabela3 to nazwa Twojej tabeli; podstaw własną, jeśli nazywa się inaczej.

W tabeli nie odwołujesz się do komórek, tylko do całych kolumn, a znacznik @ oznacza „ten wiersz”. Dlatego ta formuła nie rozjedzie się po dopisaniu wierszy ani po przesunięciu tabeli w inne miejsce arkusza.

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: po przefiltrowaniu tabeli numery zostają takie, jakie były. Na zrzucie niżej widać 1, 5, 7, 8, bo to numery wierszy, które filtr akurat przepuścił. Jeśli chcesz, żeby widoczne pozycje numerowały się od 1 do 4, potrzebujesz funkcji SUMY.CZĘŚCIOWE z kolejnego działu.

Numeracja bez dziur po przefiltrowaniu tabeli - SUMY.CZĘŚCIOWE

Żeby po filtrowaniu numery szły po kolei, zastępujemy WIERSZ funkcją SUMY.CZĘŚCIOWE. Ona jako jedyna widzi tylko te wiersze, które filtr zostawił na ekranie, i ma co najmniej dwa argumenty.

Pierwszy argument to numer działania, które ma wykonać. Nas interesuje liczba niepustych komórek, czyli pozycja 3 - ILE.NIEPUSTYCH.

Drugi argument to zakres, który rośnie razem z kopiowaniem formuły w dół: zaczyna się w nagłówku i kończy w bieżącym wierszu. Dlatego adres początku trzeba zablokować znakiem dolara, a od wyniku odjąć 1, czyli sam nagłówek.

=JEŻELI(C3="";"";SUMY.CZĘŚCIOWE(3;$C$2:C3)-1)

Bez dolarów w $C$2 zakres przesunąłby się przy kopiowaniu i każdy wiersz pokazywałby 1.

Teraz po przefiltrowaniu tabeli numeracja widocznych pozycji zaczyna się od 1 i idzie bez przerw.

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

Jedno zastrzeżenie: numery przy ukrytych wierszach znikają z widoku, ale wracają po zdjęciu filtra. To nie jest trwała zmiana danych, tylko przeliczenie.

Podsumowanie - który sposób wybrać

Wybór sprowadza się do jednego pytania: co będzie się działo z tą listą.

• Lista, która się nie zmienia - wpisz 1 i przeciągnij z Ctrl. Numery są wartościami, więc nic ich nie przeliczy, i o to tu chodzi.
• Lista, z której dopisujesz i usuwasz pozycje - formuła z JEŻELI. Numer pojawia się razem z produktem i znika razem z nim.
• Lista, którą sortujesz - JEŻELI z WIERSZ. Numer bierze się z położenia wiersza, więc sortowanie go nie rozsypie. Cena: nie przeniesiesz takiego zakresu w inne miejsce arkusza bez poprawienia odejmowanej liczby.
• Tabela Excela - ta sama formuła, ale z odwołaniami do kolumn. Tabela wchłania nowe wiersze i sama kopiuje w nie formułę, więc to domyślny wybór przy zestawieniu, które rośnie.
• Tabela, którą filtrujesz i drukujesz - SUMY.CZĘŚCIOWE. Jako jedyna numeruje to, co widać, a nie to, co jest w pliku.

Jedna pułapka wspólna dla wszystkich formuł z tego poradnika: wynikiem jest tekst, nie liczba, bo dla pustego wiersza wstawiamy "". Jeśli ktoś potem spróbuje te numery zsumować albo posortować rosnąco, dostanie wynik, którego się nie spodziewa.

Te formuły składają się z trzech rzeczy. Wszystkie trzy są w kursie

✓Funkcja analizy warunkowej - JEŻELI i WYBIERZ

✓Tabele Excela: tworzenie, sortowanie, filtrowanie, obliczenia w tabeli

✓Odwołania względne i bezwzględne, czyli po co te znaki dolara

✓40 lekcji wideo · ćwiczeń z rozwiązaniami: 52 · gwarancja zwrotu 30 dni

Zobacz kurs