Ich habe einen Datensatz aus einer Webdatenbank heruntergeladen, der eine variable Formatierung hat. Das Ziel besteht darin, alle Daten in Zeilen zu bringen, damit sie sortiert und möglicherweise in eine CSV-KMZ(KML)-Datei umgewandelt werden können. Nach dem Aufheben der Zellenzusammenführung werden die Daten wie folgt formatiertDas.
Die Brute-Force-Lösung, die ich gefunden habe, besteht darin,='cell number'in den Zellen rechts (So was.) und löschen Sie dann alle leeren Zellen, um alle Daten in derselben Zeile zu konsolidieren (Beispiel hier). Von hier aus kann ich einfach auswählen und nach unten ziehen, um das Zellenarray in die unteren Zellen zu kopieren. Das Problem entsteht, wenn ich auf Responsible Party-Einträge mit mehreren Sites stoße, wie im ersten Bild, da diese die Abstände durcheinanderbringen, was viel Kopieren und Einfügen erfordert, um diese zu umgehen.
Da dieser Datensatz bis zur Zeile 10.000+ reicht und es wahrscheinlich noch mehr solcher Datensätze geben wird, hoffe ich, dass einige der großen Talente hier eine andere Lösung finden. Vielen Dank an alle, die sich Gedanken zu diesem Problem machen!
Antwort1
Das abgebildete Datenlayout-Beispiel ist der Schlüssel zu Ihrem Erfolg. Es istgut charakterisiertDaten. Das bedeutet, dass sie einem festgelegten Muster folgen und nicht überall verstreut sind. Und das bedeutet, dass Sie einfache, unkomplizierte Formeln schreiben können, um jedes Stück davon zu erfassen.
Als ich die Einleitung vor dem Klicken las, dachte ich, ich würde überall Daten sehen. Beispielsweise könnte {City} in Zelle D3, E3 oder F3 stehen und andere ähnlich überall, oder vielleicht kombiniert mit {State}, und jeder Datensatz ist anders: in einigen nicht kombiniert, sechs in einer Reihe mit {City} in E3, dann ein paar in F3, so etwas in der Art.
Sie leiden unter KEINEM dieser Art von Fehler!
Die einzige Schwierigkeit besteht darin, dass die Site-Informationen möglicherweise aus mehreren Sites bestehen und nicht nur aus einer pro Datensatz. Aber das ist eine kleine Komplikation, die mit zwei Dingen gelöst werden kann:
1) Eine Einschätzung, wie viele Sites pro Datensatz möglich sind. Sie denken bestimmt schon darüber nach, da Sie die Ergebnisse in einer einzigen Zeile pro RP darstellen möchten. Verwenden Sie also diese Funktion. 2) Verwenden Sie die Funktion IF(), um ein bestimmtes Datenelement zu testen. Dadurch erfahren Sie, ob Sie Site-Informationen extrahieren oder einen neuen Datensatz beginnen sollen.
Der zweite Punkt erscheint etwas seltsam, da Sie die Entscheidung sozusagen an zwei Stellen treffen und nicht nur an einer.
Nach der Entscheidung können die Zellen in der Ausgabezeile einfachere Formeln enthalten, die den Inhalt der Entscheidungszelle testen und sich dann durch die Zeile ziehen.
Was tun Sie? Zunächst gehe ich davon aus, dass die fettgedruckten Elemente der Übersichtlichkeit halber hinzugefügt wurden und nicht extrahiert werden müssen. (Wenn doch, machen Sie es genauso.) Ich plane auch eine einfache Ausgabe, die eine Datenzeile, dann eine Reihe „leerer“ Zeilen und dann eine weitere Datenzeile usw. enthält. Die Idee am Ende ist, Kopieren und Einfügen|Spezial|Werte, dann den riesigen Stapel „leerer“ Zeilen am Ende zu sortieren und zu löschen. Es KANN noch ausgefallener werden, aber das ist um 23:30 Uhr nicht drin …
Um die Hauptinformationen, die „RP“-Daten, zu extrahieren (ich gehe davon aus, dass sie in T2 beginnen, und gehe davon aus, dass die „Party-ID“ die „RP#“ ist), benötigen Sie einige Daten, auf die Sie sich konzentrieren können, und auf die Sie dann die für die anderen Teile betrachteten Positionen beziehen können. Die „RP#“ scheint dafür ideal zu sein. Zelle T2 hätte also:
= C1
Verwenden Sie nun OFFSET(), um alle restlichen RP-Daten zu finden. Denken Sie jedoch daran, dass jede Zeile in der Ausgabe prüfen muss, ob sie überhaupt Daten sammeln soll. Sie müssen dies also in ein IF() einschließen, um zu sehen, ob die Zeile Daten erhält oder nicht:
= IF( C1 = "", "", C1)
Dadurch wird T2 mit einer RP# gefüllt. In T3 bis T7 wird "" angezeigt. Gehen Sie weiter zu U2. Geben Sie die folgende Formel ein:
= IF( C1="", "", OFFSET( C1, 0, 2 ) )
Wenn C1 eine RP# hatte, dann finden Sie den Wert der Zelle 0 Zeilen weiter unten und 2 Spalten weiter rechts. Wenn nicht, erhalten Sie "" in U2 (und Sie werden dies weiterhin für alle Spalten erhalten, die nach den Nicht-Site-Daten suchen).
Weiter geht‘s. Variieren Sie einfach die beiden Werte (Zeilen ist der erste, Spalten ist der zweite) nach Bedarf, um jedes Stück relativ zu C1 zu lokalisieren. Thant verarbeitet alle gesuchten Nicht-Site-Daten. (Interessant zu merken und als Nächstes zu verwenden ist, dass Ihre Offsets negative Werte sein können, sodass Sie OFFSET() verwenden können, um nach links und oben sowie nach rechts und unten zu schauen.)
Geben Sie für Zelle AB2 einen Offset von +4 Zeilen ein (alsorunter) und -1 Spalten (alsolinks). Also der einfache IF()-Test, dann der Offset. Angesichts der Daten scheint es unmöglich, dass es nicht mindestens eine Site für jede RP gibt, aber wenn es keine gibt, fügen Sie dem IF()-Test Folgendes hinzu:
= IF( OR( C1="", OFFSET( C1, 4, -1 ) = "" ), "", OFFSET( C1, 4, -1 ) )
Gehen Sie nun zum Sammeln der Site-Daten zurück und kehren Sie zum einfachen IF()-Test zurück, verwenden Sie jedoch AB2 statt C1. (Wenn C1 keine RP-Nummer war, haben Sie ein „leeres“ AB2, sodass ein „leeres“ AB2 bedeutet, dass C1 auch „leer“ war, sodass Sie nicht jedes Mal einen Test durchführen müssen.) Sammeln Sie alle Daten dieser Site, genau wie Sie es mit den RP-Daten getan haben.
Nun zum Kern des Problems: Gibt es eine zweite Site oder den Anfang eines neuen Datensatzes? Die Zelle, dieselbe Spalte, aber 7 Zeilen unterhalb der RP# dieses Datensatzes, ist entweder eine neue RP# oder leer. Auf „Leer“ kann wie zuvor getestet werden. Nehmen wir an, die Daten der zweiten Site sollten bei AK2 beginnen. Testen Sie diese Zelle einfach auf leer oder nicht leer. Wenn sie leer ist, dann gibt es eine zweite Site und Sie finden deren Daten auf die gleiche Weise wie oben. Verwenden Sie:
= IF( OR( C1="", OFFSET( C1, 7, 0 ) = "" ), "", OFFSET( C1, 7, -1 ) )
Dadurch wird die Site-Nummer abgerufen, falls eine solche vorhanden ist, oder ein "". Dieselbe Art von Formeln wie für die erste Site, nur dass der Zeilenoffset geändert wird (die Spaltenoffsets bleiben gleich). Wenn überhaupt keine Site-Informationen vorhanden sein könnten, ändern Sie dies, um auch AB2 auf "" zu testen, sodass, wenn es "leer" ist, die ""-Ergebnisse von hier aus nach rechts wandern.
Tun Sie dies für so viele Site-Datensätze, wie Sie für möglich halten. Und vielleicht noch ein oder zwei mehr, oder?
Nun zur „anderen Hälfte“ der Existenz von Site-Daten: Wir gehen zu Zeile 3 und Zelle T3. Kopieren Sie alle Zellen von Zeile 2 bis beispielsweise Zeile 15. Es wird eine Reihe leer aussehender Zeilen geben, wobei C2, C3 usw. keine RP#s haben, bis C8 erreicht ist. Dann werden wieder Daten angezeigt.
Und da T8 nicht "" sein wird, wird die Zeile rechts davon mit Daten gefüllt. Juhu!
Sie denken vielleicht, dass Site-Zellen rechts von echten Site-Sammlungen bizarre oder verwirrende Einträge erhalten könnten, weil sie ihre Daten in den nächsten Datensatz, oder zwei oder vier, lesen. Aber die ERSTE Prüfung, um zu sehen, ob es überhaupt einen Grund dafür gibt, führt zu "" für die (befürchtet, dass es sich um eine falsche Site-Nummer handelt) und dann breitet sich das nach rechts aus, was zu ""-Einträgen führt, anstatt die Daten eines anderen Datensatzes zu lesen. Keine Sorge.
Kopieren Sie entweder alle vorhandenen Datenzeilen oder so viele, wie Sie verarbeiten können, und fügen Sie sie ein (denken Sie daran, dass Ihr Computer dadurch nicht nur alle Formeln in den 10.000 Zeilen im Auge behält, sondern gleichzeitig auch deren Werte kopiert und einfügt). Nehmen wir an, es gibt hier keine Bedenken, aber wenn doch, müssen Sie dies in Gruppen von 1.000 Zeilen oder einer anderen Methode, die für Sie am besten geeignet ist, verwalten.
Wenn alle Berechnungen abgeschlossen sind, kopieren Sie die Ausgabezellen und fügen Sie sie über „Einfügen|Spezial|Werte“ an einer anderen Stelle ein, z. B. in ein zweites Blatt. (Wenn Sie an dieser Stelle Bedenken hinsichtlich der Maschinenleistung haben, löschen Sie alle Formelzeilen außer den ersten paar.)
Da es sich auf einem ganz anderen Blatt befindet, können Sie mit der Ausgabe alles Mögliche tun, ohne die Formelextraktionszellen oder die Quelldaten zu beeinträchtigen.
Sobald Sie sich auf dem zweiten Blatt befinden, müssen Sie sich nur noch um eine Sache kümmern: Als Nächstes müssen Sie die Ausgabe sortieren. Manchmal muss die ursprüngliche Datenreihenfolge beibehalten werden, und das ist NICHT die Sortierreihenfolge, die Excel erstellt. Wenn das der Fall ist, fügen Sie links eine Spalte ein und füllen Sie sie nach Belieben mit einer fortlaufenden Nummernliste. Es müssen nur „Konstanten“ und keine Formeln sein, damit sich nichts ändert, weil Formeln sich nach dem Sortieren neu berechnen …
OK, es ist Zeit, all diese „leeren“ Zeilen loszuwerden und nur noch einen kleinen Satz Datenzeilen zu haben. Sortieren Sie die Daten (UND auch die Nummerierungsspalte, falls Sie eine brauchen oder wollen). Gehen Sie wie üblich von unten nach oben vor, damit die „leeren“ Zeilen unten erscheinen. Suchen Sie die erste dieser „leeren“ Zeilen. Es gibt viele Möglichkeiten, dies zu tun ... Scrollen zum Beispiel ... oder vielleicht gehen Sie zuerst zu JEDER leeren Zeile und geben „zzzzzzzzzzzz“ in die erste (ganz linke) Zelle ein, damit sie bis zum Ende der Daten sortiert wird, was sie zur ersten Zeile vor allen „leeren“ Zeilen macht. Wenn Sie zur ersten Zelle der ersten Zeile mit „leeren“ Zeilen gelangen, drücken Sie Strg-Umschalt-Ende, um den GESAMTEN Müll hervorzuheben, den Sie entfernen müssen. Löschen Sie den Inhalt mit der Entf-Taste.
Jetzt sind alle Ihre Datenzeilen zusammen, es gibt keine falschen, nicht wirklich leeren Zeilen, die Ihre Verwendung der Daten beeinträchtigen könnten, und Sie können loslegen. Arbeiten Sie daran, oder kopieren Sie es und fügen Sie es an einem endgültigen Ruheplatz ein, vielleicht in einer anderen Tabelle, und legen Sie los!
Übrigens ist die Erstellung sehr einfach und überhaupt nicht zeitaufwändig. Wenn sie einmal erledigt ist, ist sie für immer erledigt, solange die Daten nicht verschoben werden. Das wird passieren, Websites ändern sich, Software wird aktualisiert und ganz neue Spalten verschieben Dinge, aber es gibt viele einfache Möglichkeiten, diese Probleme zu umgehen, wenn Sie Ihre anfängliche Arbeit langsam aktualisieren.
Sobald die Daten erstellt sind, werden sie gelesen, sobald sie geladen sind. Dann müssen Sie nur noch kopieren und einfügen, sortieren und löschen (alles nur Tastendrücke, buchstäblich eine Minute, nicht Stunden) und fertig. Ich erwähne dies, weil ich an Ihrer Stelle war und Dinge mit roher Anstrengung gelöst habe, genau wie Sie es in der Frage ansprechen. Ich glaube, Sie werden es kaum glauben, aber STUNDEN des hässlichen, brutalen Durcharbeitens mit Fehlern, die tonnenweise rückgängig gemacht werden müssen, Mausausrutschern usw. dauern jetzt buchstäblich eine Minute. Genießen Sie das Leben, das Sie nicht mehr bestiehlt!