☰

Sie müssen fehlende Iterations-ID-Nummern zu einer Reihe in Text oder Excel hinzufügen. Wie geht das ohne Codierung?

tag-icon • tag-icon microsoft-excel regex
Sie müssen fehlende Iterations-ID-Nummern zu einer Reihe in Text oder Excel hinzufügen. Wie geht das ohne Codierung?

Ich habe eine Text-, CSV- oder Excel-Datei, die aussieht wie

||--ID-----||--Name--||--Date of birth--||
    1            Jo          1/1/11
    32           Mo          2/2/12
    3382         Ro          3/3/10
    21,252       Do          4/4/09

Der tatsächliche Satz enthält 280.000 davon von insgesamt 1.000.000 Zeilen. Ich muss irgendwie alle fehlenden ID-Nummern (die alle sequenziell +1 Iteration sind) und nur leere Felder für Name und Geburtsdatum hinzufügen. Damit ich etwas bekomme, das so aussieht:

||--ID-----||--Name--||--Date of birth--||
    1,           "Jo",     "1/1/11"
    2,           "",         ""
    3,           "",         ""
    4,           "",         ""

bis 32 und dann noch einmal, bis alle Integer da sind. Gibt es eine einfache Möglichkeit, dies zu tun? Ohne einen Codeblock in einer Schleife zu schreiben? Alles aus Excel, einer App oder einem beliebigen Windows-Texteditor-Trick wäre willkommen.

Edit: Kommata, Anführungszeichen etc. bitte ignorieren. Einzig die fehlenden ID Nummern sind kritisch.

Antwort1

Ein Ansatz wäre:

  1. Importieren Sie Ihre CSV als Blatt 1 in Excel.
  2. Erstellen Sie auf Blatt 2 Ihre vollständige Liste der ID-Nummern mit einer Formel wie a2: =a1+1 und kopieren Sie diese dann nach unten.
  3. Verwenden Sie in den anderen beiden Spalten Vlookup-Formeln, die auf Ihre Tabelle auf Blatt 1 verweisen, z. B. b2: =VLOOKUP(A2,Sheet1!A2:C13,2,false)und c2:, =VLOOKUP(A2,Sheet1!A2:C13,3,false)oder um Ihrer Anforderung genau zu entsprechen, schließen wir das in eine IFNA-Anweisung ein, um "" zurückzugeben, wenn kein Wert vorhanden ist. b2: =IFNA(VLOOKUP(A2,Sheet1!A2:C13,2,FALSE),"")und c2: =IFNA(VLOOKUP(A2,Sheet1!A2:C13,3,FALSE),"")(diese Formeln können jetzt in die Spalte nach unten kopiert werden.)
  4. Nach dem Ausfüllen speichern Sie Blatt 2 als CSV.

Hinweis: Damit dies richtig funktioniert, muss die Tabelle auf Blatt 1 nach Spalte 1 sortiert sein. Den gegebenen Informationen zufolge scheint dies der Fall zu sein. Ist dies jedoch nicht der Fall, sortieren Sie die Informationen nach der ersten Spalte.

Bei einer Million Zeilen wird dies auf vielen Computern tatsächlich extrem langsam, das habe ich vor dem Schreiben der Formeln übersehen. Ich habe einmal etwas Ähnliches mit einem großen Datensatz gemacht, indem ich die automatische Neuberechnung deaktiviert habe, um alle Formeln an die richtige Stelle zu bringen, und dann eine manuelle Neuberechnung durchgeführt habe, die Stunden gedauert hat, aber korrekt abgeschlossen wurde.

Antwort2

Ich würde dies mit dem Power Query-Add-In lösen.

Ich habe einen Prototyp erstellt, den Sie ansehen oder herunterladen können – seine „Power Query-Demo – Fehlende ID-Nummern zu einer Reihe hinzufügen.xlsx“ in meinem One Drive:

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398

Um dorthin zu gelangen, sind einige Schritte und ein wenig Code in der Power Query-Sprache (M) erforderlich, um die Funktion List.Numbers aufzurufen (sie wird in der Power Query-Benutzeroberfläche nicht angezeigt). Trotzdem ist das nur eine einfache Codezeile – der Rest kann durch Klicken in Power Query erstellt werden.

Meine Technik bestand im Wesentlichen darin, mithilfe von List.Numbers eine Tabelle mit ID-Nummern zu generieren und dann einen Merge hinzuzufügen, um die Spalten aus den Eingabedaten abzurufen (in denen die ID-Nummer vorhanden ist).

Die Dokumentation für List.Numbers finden Sie hier:

http://office.microsoft.com/en-au/excel-help/list-numbers-HA104111648.aspx?CTT=5&origin=HA104122363

Großes „Hut ab“ an Matt Masson für die „Settings“-Technik.

http://www.mattmasson.com/2014/04/defining-configurable-settings-for-your-queries/

Beachten Sie, dass Power Query direkt aus einer CSV-Datei lesen kann, die ich als Quelle für die Abfrage „Eingabedaten“ verwenden würde. Am einfachsten ist es wahrscheinlich, diese Abfrage zu löschen, dann eine neue aus der CSV-Datei zu erstellen und sie „Eingabedaten“ zu nennen. Deaktivieren Sie die Option „In Arbeitsblatt laden“, um Ressourcen zu sparen.

verwandte Informationen