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.
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.
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.
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.
Ü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.
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.
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.
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
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
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”]