Nur bestimmte Zeilen 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. Werfen Sie einen Blick in die Inhalte folgender Kurse:

Excel Kompakt: Der Intensivkurs – Werden Sie vom Einsteiger zum Fortgeschrittenen
Excel Grundlagen: Excel – Das vielseitige Tabellenkalkulationsprogramm
Excel Aufbau: Mit diesem Seminar werden Sie zum Ex(cel)perten!
Excel Datenanalysen: Datenauswertungen mit Aussagekraft
Excel PowerPivot: PowerPivot – Der Turbo für Ihre Auswertungen
Excel für Datenanalyse und Controlling: Werden Sie zum professionellen Zahlen-Jongleur!

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

Comments are closed.

Kategorien

Archiv