Index ausfüllen

Index-Spalte einfügen und ausfüllen

Die Aufgabe: In eine bestehende Liste, eine (normale) Tabelle soll eine Index-Spalte eingefügt werden. Beginnend in A2 mit der Index-Nummer 1001 soll die Folgezeile den um 1 erhöhten Wert enthalten, jeweils bis hinunter zur letzten Zeile der Daten. Hier gibt es reichlich Wege, von denen ich einen recht suboptimalen und verschiedene gute vorstelle.Grundsätzlich gilt für alle Vorgehensweisen: Entweder eine Leerspalte vor den bestehenden Daten einfügen (die neue Spalte A) oder rechts der bestehenden Daten eine Indexspalte erstellen und diese nach dem Erstellen verschieben. Und es gilt auch, dass in der ersten Zeile der entsprechenden Spalte der Name Index (oder was auch immer) steht. Und noch ein Hinweis: Im Beschreibungstext werde ich immer die Spalte A als Adresse verwenden, auch wenn es vor dem Verschieben eine andere Spalte sein könnte.

▲ nach oben …

Addition

Schreiben Sie in A2 den Start-Index, also 1001. In A3 kommt nun diese Formel: =A2+1. Diese Formel kopieren Sie nun per Doppelklick auf das Ausfüllkästchen nach unten. Alternativ ziehen Sie die Formel bis zur letzten Zeile nach unten. Anschließend noch die Formeln durch Kopieren und Als Wert einfügen gegen Veränderungen schützen.

▲ nach oben …

Addition ++

(Ergänzung vom 18.07.2016)
Prinzipiell beginnt das Ganze wie im Beispiel hierüber. Aber wenn Sie ausschließlich einen Index generieren wollen und in der direkten Spalte daneben stehen keine Daten, dann funktioniert das mit dem Doppelklick nicht.

Angenommen, Sie wollen den Index 1001 bis 1100 erstellen, also 100 Zeilen füllen. Dann geht das natürlich mit dem herunterziehen (noch) ganz gut; Sie sehen ja jeweils den aktuellen Endwert in einem kleinem Fenster neben dem Mauszeiger.  Bei 1000 Zeilen wird das dann schon eine richtige Arbeit und wenn 48752 der Startwert ist und Sie wollen 863 Werte erzeugen, dann wird manch ein User zu Recht die Stirn runzeln.

OK, um es besser und übersichtlicher zu demonstrieren, belassen Sie es beim Startwert von 48752, es sollen aber nur insgesamt 14 Index-Werte erzeugt werden. Der letzte Wert soll also 48765 sein. Und die letzte Zeile ist naturgemäß Zeile 14, wenn der erste Wert in A1 steht. Step by step hier die Vorgehensweise:

  • Schreiben Sie in A1 den Startwert
  • In A2 kommt die Formel =A1+1
  • Kopieren Sie A2 in die Zwischenablage
  • Gehen Sie in das Adress-Feld und ergänzen Sie dort den bereits dort stehenden Wert oder überschreiben sie ihn ,damit dort anschließend A2:A14 steht.
  • Drücken Sie die Eingabetaste (Return).

Das war’s auch schon. Die Reihe ist bis zum letzten Wert in Einerschritten korrekt ausgefüllt. Und das geht natürlich mit jeder beliebigen Schrittweite.

Nutzen der ZEILE() – Funktion

Schreiben Sie in A2 die Formel =ZEILE()+999. Zur Verdeutlichung: Die Zeile hat die Nummer 2, durch die Addition kommen Sie auf automatisch auf 1001. Füllen Sie diese Formel bis zum Ende der Datenliste aus. Auch hier gilt, dass die Formeln gegen Werte ausgetauscht werden müssen.

▲ nach oben …

ZEILE() die Zweite

Machen Sie aus den bisherigen Daten eine Intelligente Tabelle, beispielsweise über die Menüleiste oder per StrgT. Fügen Sie entweder eine neue Spalte ein (beispielsweise an der Position der Spalte C) oder ergänzen Sie durch Hinzufügen der Überschrift direkt nach der letzten Spalte die Tabelle. Schreiben Sie in die Zeile 2 dieser Spalte die Formel =ZEILE()+999 und der Rest wird automatisch nach unten ausgefüllt. Jetzt muss diese Spalte nur noch nach ganz links verschoben werden. Die Umwandlung in Werte versteht sich von alleine. Und neue Zeilen müssen auf jeden Fall von Hand ausgefüllt werden. – Anschließend eventuell daran denken, die Formeln in Werte umzuwandeln.

▲ nach oben …

Über Power Query

Falls Sie mindestens Excel 2010 verwenden, können Sie Power Query als Add-In einsetzen. Nach der Auswahl Power Query wählen Sie in der Gruppe Excel-Daten den einzigen Punkt Von Tabelle. Im Abfrage-Editor-Fenster Spalte1 markieren, Menüpunkt Spalte hinzufügen | Indexspalte hinzufügen | Benutzerdefiniert. Als Startindex verwenden Sie natürlich 1001. Die Schrittweite ist vereinbarungsgemäß 1. Jetzt nur noch die Spalte Index an die erste Stelle verschieben und über Start | Schließen und laden den Editor beenden. – Überraschung, hier sind es schon Werte und keine Formeln.

▲ nach oben …

1+1=2  🙂 

Die oft vergessene Methode: Schreiben Sie in A2 die Zahl 1001. In A3 nun die 1002. Markieren Sie diese beiden Zellen und ziehen Sie mittels des Ausfüllkästchens den Bereich nach unten. Und auch hier sind es schon Werte, die nicht angepasst werden müssen.

▲ nach oben …

VBA – Schleife (1)

Das ist die oben schon „angedrohte“ Ausnahme. Schleifen sind für solche Aufgaben in den meisten Fällen nicht wirklich optimal, weil es schnellere Möglichkeiten gibt. Ich muss mich schon fast anstrengen, um so etwas zu fabrizieren 😎 . Aber der Code tut seinen Dienst:

Sub IndexNeu_1()
   Dim Ze As Long, x As Long, lRow As Long
   
   x = 999
   lRow = Cells(Rows.Count, 2).End(xlUp).Row
   For Ze = 2 To lRow
      Cells(Ze, 1) = Ze + x
   Next Ze
   MsgBox "Fertig!"
End Sub

Etwas schneller (oder auch einiges mehr) geht es, wenn die Bildschirmaktualisierung ausgeschaltet wird. Und auf jeden Fall stimmt das Ergebnis, und es sind auch alles Werte.

▲ nach oben …

VBA – Schleife (2)

Erheblich schneller ist solch eine Schleife, wenn sich alles nur im Arbeitsspeicher abspielt. Fast unschlagbar schnell dürfte dieser Code sein. Hier wird nur in einem Array hoch gezählt. Und um nicht allzu viele “Klimmzüge” machen zu müssen, ist im Kopf des Moduls direkt nach der Anweisung Option Explicit ein Option Base 1 eingefügt. Damit ist gewährleistet, dass das Array nicht null- sondern eins-basiert ist. Der erste Array-Eintrag hat also den Array-Index 1:

Option Explicit
Option Base 1

Sub IndexNeu_2()
   Dim Idx As Long, aIdx, lRow As Long, x As Long
   
   x = 1000 'Weil Zähl-Index 1 ist
   lRow = Cells(Rows.Count, 2).End(xlUp).Row - 1
   Range("A2:A" & lRow + 1).ClearContents
   ReDim aIdx(lRow)
   
   For Idx = 1 To lRow
      aIdx(Idx) = Idx + x
   Next Idx
   
   Range("A2:A" & lRow + 1) = WorksheetFunction.Transpose(aIdx)
   MsgBox "Fertig!"
End Sub

▲ nach oben …

VBA mit ZEILE()-Funktion

Erstaunlich lange dauert hier die Verwendung der ZEILE()-Funktion in Verbindung mit dem ausschalten der Bildschirmaktualisierung, obwohl im folgenden Code sehr vieles für die Beschleunigung getan wurde. Und natürlich wird jeder berechnete Wert per VBA auch in einen festen Wert umgewandelt:

Sub IndexNeu_3()
   Dim x As Integer, Ze As Long, lRow As Long
   Dim CalcStatus As Long

   On Error GoTo ErrorHandler
   With Application
      .ScreenUpdating = False
      CalcStatus = .Calculation
      .Calculation = xlCalculationManual
   End With
   x = 999
   lRow = Cells(Rows.Count, 2).End(xlUp).Row
   With Range("A2:A" & lRow)
      .ClearContents
      .Formula = "=Row()+" & x
      .Value = .Value
   End With

ErrorHandler:
   If Err.Number <> 0 Then MsgBox "Fehler Nr.: " & Err.Number _
      & vbCrLf & Err.Description
   With Application
      .Calculation = CalcStatus
      .Calculate
      .ScreenUpdating = True
   End With
   MsgBox "Fertig!"
End Sub

▲ nach oben …

Auffüllen lassen

Als letzten Vorschlag empfehle ich Ihnen wiederum eine Plain Excel-Methode: Ausfüllen. In A2 kommt (natürlich) die 1001. Der einfachste Weg wäre hier nun folgender: In B1 klicken, dann Strg. Damit sind Sie in der letzten Datenzeile. Ein Mal nach links in die gleiche Zeile, Spalte A. Jetzt StrgShift, um den kompletten auszufüllenden Bereich zu markieren. Menü Start, Gruppe Bearbeiten, dort beim Ausfüllen-Symbol den Punkt Reihe auswählen. Inkrement ist mit 1 vorbelegt und dabei kann es auch bleiben. OK und automatisch sind alle Zellen mit den Werten ausgefüllt.

Und hier genau diesen Vorgang als VBA-Code, meiner Meinung nach super schnell:

Sub IndexNeu_4()
   Dim lRow As Long
   Dim rngData As Range
 
   lRow = Cells(Rows.Count, 2).End(xlUp).Row
   Set rngData = Range("A2:A" & lRow)
   Range("A2") = 1001
   Range("A2").AutoFill Destination:=rngData, Type:=xlFillSeries
End Sub
[NachObenLetzte Verweis=”Wege nach Rom: Index”]
Dieser Beitrag wurde unter Mit VBA/Makro, Musterlösungen, Ohne Makro/VBA, Power Query, Tabelle und Zelle, Tipps und Tricks, Wege nach Rom abgelegt und mit , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.