BlogNur bestimmte Zeilen in Excel nummerieren

Nur bestimmte Zeilen in Excel nummerieren

In einem meiner Excel-Seminare stellte eine Teilnehmerin
eine interessante Frage. Sie erzeugt eine Aufstellung in Excel und möchte, dass nur die Materialien eine fortlaufende Nummerierung erhalten. Normalerweise können Sie einfach die ersten beiden Werte (1, 2) erfassen und die Zahlenreihe durch Herunterziehen ausfüllen. Das ist in diesem Beispiel nicht so einfach, da Gruppenüberschriften und Leerzeilen die fortlaufende Nummerierung erschweren. Die folgende Abbildung zeigt den Nummerierungswunsch in der ersten Spalte:

Das Ziel: Die Nummerierung soll zwei Zellen überspringen

Im Bonusteil erfahren Sie, wie die Formel zur automatischen
Nummerierung auch funktioniert, wenn Zeilen mitten in der Liste gelöscht werden.

Die Schritt-für-Schritt-Anleitung

In diesem Beispiel sind die ersten beiden Werte 1 und 2 in den Zellen A4 und A5 bereits erfasst.

  • Klicken Sie in die Zelle A6 und erfassen Sie die folgende Formel:

=WENN(B6=““;““;WENN(B5=““;““;WENN(A5<>““;A5+1;A3+1)))

Die Formel ermittelt den nächsten Wert
  • Kopieren Sie die Formel nach unten.
Die Formel berücksichtigt die leeren Zellen

Der erste Teil der Formel WENN(B6=““;““; prüft, ob die Zelle rechts
daneben leer ist. Wenn die Prüfung positiv verläuft, die Nachbarzelle also leer ist, dann bleibt die aktuelle Zelle leer. Wenn die Prüfung negativ verläuft, wird der nächste Formelteil ausgeführt.

Der zweite Teil der Formel WENN(B5=““;““; prüft, ob die Zelle rechts
daneben und einen darüber leer ist. Wenn die Prüfung positiv verläuft, dann bleibt die aktuelle Zelle leer. Wenn die Prüfung negativ verläuft, wird der nächste Formelteil ausgeführt.

Der dritte Teil der Formel WENN(A5<>““; A5+1;
prüft, ob die Zelle darüber nicht leer ist. Wenn die Prüfung positiv verläuft, dann wird der Wert der Zelle darüber um 1 erhöht.

Wenn keine der Prüfungen positiv verläuft, dann sind die
zwei Zellen über der aktuellen Zeile leer. Dann soll der Wert der dritten Zelle darüber um 1 erhöht werden.

Bonus: Auch nach dem Löschen einer Zeile eine korrekte Nummerierung

Das oben vorgestellte Beispiel rechnet auch richtig, wenn Zeilen eingefügt werden. Sollte allerdings eine Zeile gelöscht werden, erscheint die Meldung: #BEZUG!.

Hier wurde die Zeile 7 gelöscht

Sie könnten jetzt die Formel in A6 erneut nach unten kopieren und hätten sofort wieder ein korrektes Ergebnis. Sie könnten auch Ihre Formel erweitern und sich somit einen Arbeitsschritt sparen.

  • Erweitern Sie die Formel in A6, drücken Sie ENTER und kopieren Sie sie wieder nach unten.

=WENN(B6=““;““;WENN(INDIREKT(„B“&ZEILE()-1)=““;““;WENN(INDIREKT(„A“&ZEILE()-1) >““;INDIREKT(„A“&ZEILE()-1)+1;INDIREKT(„A“&ZEILE()-3)+1)))

Die Formel wirkt nur etwas umfangreicher

Jedes Mal, wenn eine Zelle über der aktiven Zelle benannt
wird, greifen jetzt die Funktionen INDIREKT() und ZEILE().

ZEILE() gibt immer die Nummer der aktuellen Zeile aus. Probieren Sie es doch einfach einmal aus. Geben Sie in eine Zelle folgende kleine Formel ein und drücken Sie ENTER.

=ZEILE()

Als Ergebnis wird die Nummer der aktuellen Zeile angezeigt.

Mit INDIREKT() setzen Sie eine Zelladresse zusammen. Im
zweiten Teil der Formel prüfen Sie, ob die Zelle darüber leer ist INDIREKT(„A“&ZEILE()-1). Für die Formel in A6 ist das die Zelle A5. Mit INDIREKT() geben Sie zuerst den Spaltennamen in Anführungszeichen „A ein. Die Zeilennummer wird mit einem &-Zeichen angefügt. Jetzt berechnen Sie mit ZEILE()-1 die Nummer der Zeile über der aktiven Zelle, die 5. So ermittelt INDIREKT() als Ergebnis A5.

  • Testen Sie Ihre Formel, in dem Sie eine Zeile
    löschen.
Auch nach dem Löschen einer Zeile arbeitet die Nummerierung korrekt

Dank der Kombination der Funktionen INDIREKT() und ZEILE() wird die Nummerierung immer richtig berechnet.

Excel Schulungen bei INCAS Training

Ob Kompaktkurs, Grundlagen- oder Aufbauseminar, wir bieten Ihnen professionelle Excel-Kurse an. Wir sind Microsoft Partner und arbeiten ausschließlich mit qualifizierten Office Trainern zusammen. Die Spitzenbewertungen unserer Trainer und des Trainingsumfeldes sprechen für sich: Kurse bei INCAS Training sind der beste Weg, um sich professionell und mit Spaß einem Programm und seinen neuen Funktionen zu nähern.

Saskia Gießen ist ‎IT Dozentin im Bereich ‎Internet und Informationstechnologie aus Köln und für INCAS Training als Autorin und Excel-Spezialistin im Einsatz. Sie verfasst regelmäßig Artikel für Sie, in denen Sie Best Practices rund um das Thema Excel verrät.

Wir stehen Ihnen auch jederzeit telefonisch zur Verfügung: Sie erreichen uns unter 02151 366250

Schulungen die dich interessieren könnten

Bewertungen

Kundenstimme
Martin S.
Bundeseisenbahnvermögen
star-participantstar-participantstar-participantstar-participantstar-participant
Das Training zeichnet sich durch einen sehr hohen Praxisbezug und Raum für individuelle Hilfe persönlicher Problemstellungen sowie durch einen engagierten und hoch kompetenten Trainer aus.
Kundenstimme
Mausolf B.
Struers GmbH
star-participantstar-participantstar-participantstar-participantstar-participant
Tolle Schulung - kompetenter Trainer, der geduldig auf alle Fragen einging, diese beantworten konnte und darüber hinaus viele neue Anregungen mit auf den Weg gab. Die Schulung hat Spaß gemacht.
Kundenstimme
Michael W.
Ernst & Young Retail Services GmbH
star-participantstar-participantstar-participantstar-participantstar-participant
Ich fühlte mich in diesem Seminar hervorragend betreut. Es war sehr praxisorientiert und anschaulich.
Kundenstimme
Wolfgang N.
ThyssenKrupp Nirosta
star-participantstar-participantstar-participantstar-participantstar-participant
Eine gute Adresse für das Erlernen scheinbar schwieriger und trockener Themen, die hier gut aufbereitet werden.