Jak ponumerować wiersze w Excelu?

MS Excel w porównaniu do Worda nie oferuje polecenia automatycznego numerowania list danych. Mimo tego wstawianie liczb porządkowych do arkusza z całą pewnością nie stanowi dla Ciebie większego problemu. Istnieją jednak triki i sztuczki, o których być może nie wiesz, pozwalające wykonać tę czynność dużo sprawniej i szybciej.

 

Spis treści:

• Jak wstawić liczby porządkowe w Excelu?
• Jak ponumerować długą tabelę w Excelu?
• Alternatywy dla ręcznego przeciągania
• Jak numerować wiersze za pomocą formuł Excela?
• Jakie zalety ma numeracja wierszy za pomocą formuły?
• Jak wstawić numerację wierszy odporną na filtrowanie?
• Jak oznaczyć literami wiersze tabeli Excela?
• Podsumowanie

 

Jak wstawić liczby porządkowe w Excelu?

 

Liczby porządkowe są zazwyczaj umieszczane w pierwszej kolumnie listy z danymi. Jeżeli nie zawiera ona zbyt wielu wierszy, to najszybszym sposobem wstawienia numeracji jest wpisanie w komórce liczby 1, a następnie przy wciśniętym klawiszu Ctrl przeciągnięcie uchwytu wypełniania w dół.

 

Rys. 1. Wstawienie liczb porządkowych przez przeciągnięcie uchwytu wypełniania przy wciśniętym klawiszu Ctrl

 

Jak ponumerować długą tabelę w Excelu?

Poprzedni sposób jest przydatny, ale tylko dla krótkich list. Aby ten sam efekt osiągnąć dla listy składającej się z kilku tysięcy wierszy, proponujemy Ci zastosować pewną sztuczkę. Jednakże jest jeden warunek jej użycia - w kolumnie obok numerowanej, powinny znajdować się dane.

 

Alternatywy dla ręcznego przeciagania

Aby szybko ponumerować długa listę:

1. Zaznacz pierwszą komórkę w kolumnie przeznaczonej na listę porządkową i wpisz 1. W komórce poniżej wpisz liczbę 2.

2. Następnie zaznacz obydwie komórki i dwukrotnie kliknij uchwyt wypełniania lewym przyciskiem myszy.

 

W efekcie pojawią się liczby porządkowe w obszarze od pierwszej zaznaczonej komórki do ostatniego wiersza z danymi. Jest to zdecydowanie najszybszy sposób wstawienia liczby porządkowej w przypadku bardzo długich list.

 

Co jednak zrobić w sytuacji, gdy dopiero przygotowujesz listę danych i chcesz rozpocząć jej budowę od wstawienia liczb porządkowych? Rozwiązaniem jest skorzystanie z polecenia Serie danych dostępnego na karcie Narzędzia główne, w grupie poleceń Edycja/Wypełnij. Ten sposób zakłada jednak, że użytkownik zna, choćby w przybliżeniu, ile wierszy będzie liczyć lista danych. Można oczywiście podać przybliżoną liczbę, a później dodać lub usunąć numerację.

 

W tym celu:

1. Wpisz w komórce początkowej liczbę 1.

2. Uruchom polecenie Serie danych.

3. W oknie dialogowym Serie zaznacz opcję Kolumny.

4. Wartość kroku: wynoszącą 1 pozostaw bez zmian, natomiast w polu Wartość końcowa: wpisz liczbę pozycji w Twoim zestawieniu.

5. Po kliknięciu przycisku OK komórki zostaną wypełnione kolejnymi liczbami z zakresu, od 1 do liczby wpisanej w polu Wartość końcowa.

 

Rys. 2. Wygląd okna dialogowego dla zestawienia liczącego 500 pozycji

 

Aby uzyskać lepszy efekt, możesz liczby porządkowe odpowiednio sformatować, aby były wyświetlane dodatkowo z kropką.

 

W tym celu:

1. Zaznacz obszar komórek, w których znajdują się liczby porządkowe.

2. Skorzystaj ze skrótu klawiaturowego Ctrl + 1.

3. Na karcie Liczby wybierz kategorię Niestandardowe.

4. W polu Typ wpisz: # ##0. i kliknij OK.

 

 

Jak numerować wiersze za pomocą formuł Excela?

Do numerowania pozycji na liście można również wykorzystać formuły, co daje wiele korzyści. Przyjmijmy, że chciałbyś wprowadzić numerację w zestawieniu z rysunku 3.

 

Rys. 3. Przykładowa tabela

 

Zauważ, że dane w liście nie są umieszczone w kolejnych wierszach, dlatego nie jest możliwe skorzystanie z wcześniej opisanych sposobów numeracji:

 

Aby sobie z tym poradzić:

1. Wprowadź do komórki B5 następującą formułę:

 

=JEŻELI(C5="";"";ILE.NIEPUSTYCH(C$5:C5))

 

Wyjaśnienie działania formuły:

Funkcja JEŻELI w Excelu wykorzystuje trzy argumenty. Jeśli pierwszy argument test_logiczny zwraca wartość PRAWDA, czyli komórka C5 jest pusta (C5=""), to w wyniku zwracana jest wartość podana jako drugi argument. W naszym przypadku komórka będzie wyglądała na pustą, co wyrażono za pomocą (""). Jeśli natomiast pierwszy argument test_logiczny zwróci wartość FAŁSZ, czyli zapis C5="" okaże się nieprawdziwy, wówczas zostanie zastosowana funkcja ILE.NIEPUSTYCH, zliczającą liczbę niepustych komórek w podanym zakresie.

 

W funkcji ILE.NIEPUSTYCH zakres komórek jest wyrażony za pomocą C$5:C5, co sprawia, że podczas kopiowania w dół, pierwszy adres zawsze będzie miał postać C$5, natomiast w drugim adresie numer wiersza będzie się zmieniał. Dzięki temu po skopiowaniu do komórki B6 formuła będzie miała postać:

 

=JEŻELI(C6="";"";ILE.NIEPUSTYCH(C$5:C6))

 

a więc funkcja ILE.NIEPUSTYCH będzie wyliczała liczbę niepustych komórek w obszarze

C$5:C6

 

Zaprezentowana formuła jest uniwersalna i jej stosowanie ma wiele zalet.

 

Jakie zalety ma numeracja wierszy za pomocą formuły?

- Można wcześniej wstawić formuły w kolumnie B, a liczby porządkowe zostaną wyświetlone dopiero po wpisaniu nazwiska w komórce obok. Dzieje się tak dzięki zastosowaniu funkcji JEŻELI.

- Formuła jest „odporna” na nieciągłe dane. Jeżeli chcesz wewnątrz listy z danymi pozostawić jakieś puste wiersze, to formuła pozostawi nienumerowane te wiesze, w których nie ma nazwisk i wznowi numerację w wierszach zawierających nazwiska.

- Numeracja zostanie automatycznie skorygowana w przypadku usunięcia wierszy. Nie ma potrzeby dokonywania żadnej zmiany w formule. Natomiast w przypadku wstawienia wiersza wewnątrz listy z danymi wystarczy jedynie skopiować i wkleić formułę w brakujące miejsce.

- Zwykle Excel obejmuje sortowaniem całą listę danych, a więc łącznie z kolumną zawierającą liczby porządkowe, chociaż sortowanie tej kolumny w większości przypadków nie jest pożądane. Zastosowanie powyższej formuły sprawia, że numeracja zostanie zachowana, niezależnie od sposobu sortowania.

 

Rys. 4. Formuły w kolumnie B wyświetlają poprawną numerację mimo braku danych w niektórych wierszach

 

Jak wstawić numerację wierszy odporną na filtrowanie?

Numeracja wierszy i filtrowanie to wydawać by sie mogło trudniejsza sprawa w Excelu. Z pomocą przyjdą Ci formuły. Wróćmy do naszego przykładu. Jeżeli jednak zamierzasz filtrować listę danych, to zauważysz, że numeracja w kolumnie B traci ciągłość. Dotychczasowa formuła nie dostosowuje numeracji tak, aby pokazywać poprawne liczby porządkowe tylko dla wierszy widocznych, tj. z pominięciem ukrytych.

 

Rozwiązaniem jest skorzystanie z formuły:

 

=JEŻELI(C5="";"";SUMY.CZĘŚCIOWE(3;C$5:C5))

 

która posiada zalety poprzedniej, ale dodatkowo jest również „odporna” na filtrowanie.

 

 

Wyjaśnienie działania formuły:

W miejscu funkcji ILE.NIEPUSTYCH została zastosowana SUMY.CZĘŚCIOWE, która 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.CZĘŚCIOWE, wbrew temu co sugeruje nazwa, może wyliczać nie tylko sumę, ale także iloczyn, średnią, maksimum, minimum, itd. dla zakresu komórek określonego w drugim argumencie.

 

Rys. 5. Tabela po zastosowaniu filtrowania względem oddziału

 

Warto wiedzieć!

Wiersze ukryte tylko na skutek filtrowania listy nie są brane pod uwagę przy wyświetlaniu liczby porządkowej w kolumnie B.

 

Jak oznaczyć literami wiersze tabeli Excela?

Numeracja literowa w Excelu jest jak najbardziej możliwa. Utworzoną wyżej formułę można dostosować tak, aby zamiast liczb numeracja wierszy była przeprowadzona za pomocą wielkich lub małych liter. W celu przekształcenia liczb na kolejne litery alfabetu, należy posłużyć się funkcją ZNAK, która daje w wyniku znak o podanym numerze kodu.

 

Wskazówka

Przykładowo, po wpisaniu do komórki formuły =ZNAK(65) otrzymasz dużą literę A, po wpisaniu =ZNAK(66) zostanie wyświetlona duża litera B, itd.

 

W tym celu:

1. Wpisz do komórki B5 następującą formułę:

 

=JEŻELI(C5="";"";ZNAK(
SUMY.CZĘŚCIOWE(3;C$5:C5)+64))

 

Podobnie będzie przebiegać przekształcanie liczb na małe litery, z tym, że w tym przypadku należy dodawać 96, ponieważ kod litery a wynosi 97. Formuła w komórce B5 wyświetlająca dla pierwszej pozycji tabeli literę a:

 

=JEŻELI(C5="";"";ZNAK(
SUMY.CZĘŚCIOWE(3;C$5:C5)+96))

 

Liczba pozycji numerowanych za pomocą liter nie powinna przekraczać 26 – tyle znaków liczy alfabet (z pominięciem znaków diakrytycznych).

 

Wskazówka

eżeli chcesz dodać dowolny znak po literze, wystarczy, że stworzysz odpowiedni format. Dla dodania po literze kropki wpisz @”.” a żeby uzyskać nawias @”)”

 

Rys. 6. Numeracja z wykorzystaniem wielkich liter

 

Podsumowanie

• Możesz znacznie przyśpieszyć wprowadzanie liczb porządkowych korzystając z uchwytu wypełniania lub polecenia Serie danych…
• Wykorzystanie funkcji SUMY.CZĘŚCIOWE do numeracji pozycji w tabeli, pozwala jedynie pomijać wiersze ukryte w wyniku filtrowania. Nie zapobiega jednak wstawianiu numeracji w wierszach ukrytych lub zgrupowanych.
• Tworząc odpowiedni format niestandardowy możesz po liczbach lub literach wstawić na stałe dowolny znak.