Ich habe einen Datensatz, der derzeit 39.000 Zeilen und 27 Spalten umfasst. Die erste Spalte ist die ID-Nummer. Die nachfolgenden Spalten entsprechen den Wahlen, wobei das Wahldatum in Zeile 1 (Zellen B1- AA1) steht. Die restlichen Zellen sind mit einem Buchstaben gefüllt, der der Wahlmethode entspricht (oder null, wenn nicht gewählt wurde). Ich muss diese Tabelle neu anordnen, sodass insgesamt drei Spalten vorhanden sind: ID, Datum und Wahlmethode. Beispiel:
Aktuelle Tabelle:
ID 05/2005 11/2005 03/2006 (etc., for 27 total columns)
2345 P V
3789 A V
4321 V A V
7890 I
Und es muss so aussehen:
ID Date Voting Method
2345 05/2005 P
2345 11/2005 V
3789 11/2005 A
3789 03/2006 V
4321 05/2005 V
4321 11/2005 A
4321 03/2006 V
7890 11/2005 I
Ich glaube, dafür ist ein VBA-Skript erforderlich, und ich habe versucht, Teile des Skripts, die ich online gefunden habe, zusammenzusetzen (weil ich VBA nie gelernt habe), aber ich kriege es einfach nicht richtig hin. Vielleicht gibt es diese Funktion bereits in Excel?
Hier ist das Skript, mit dem ich bisher arbeite:
Sub NewLayout()
For i = 2 To Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
For j = 0 To 26
If Cells(i, 3 + j) <> vbNullString Then
intCount = intCount + 1
Cells(i, 1).Copy Destination:=Cells(intCount, 10)
Cells(i, 2).Copy Destination:=Cells(intCount, 11)
[I think this one is wrong. It needs to copy the column name,
not the cell value, if there is a cell value.]
Cells(i, 3 + j).Copy Destination:=Cells(intCount, 12)
End If
Next j
Next i
End Sub
Wenn jemand Vorschläge hat, wäre ich dankbar!
Antwort1
Mit VBA und unter der Annahme, dass sich Daten in Sheet1 ab A1 befinden und dass Sheet2 vorhanden ist:
Sub normalize()
Dim wks1 As Worksheet, wks2 As Worksheet
Dim iColCount As Integer, iRowCount As Integer
Dim i As Integer, j As Integer, k As Integer
Set wks1 = ActiveWorkbook.Sheets("Sheet1")
Set wks2 = ActiveWorkbook.Sheets("Sheet2")
iColCount = Application.WorksheetFunction.CountA(wks1.Range("1:1"))
iRowCount = Application.WorksheetFunction.CountA(wks1.Range("A:A"))
k = 1
For i = 2 To iRowCount
For j = 2 To iColCount
If wks1.Cells(i, j) <> vbNullString Then
wks1.Cells(i, 1).Copy Destination:=wks2.Cells(k, 1)
wks1.Cells(1, j).Copy Destination:=wks2.Cells(k, 2)
wks1.Cells(i, j).Copy Destination:=wks2.Cells(k, 3)
k = k + 1
End If
Next j
Next i
End Sub
Ergebnisse in Sheet2:
2345 5/2005 P
2345 11/2005 V
3789 11/2005 A
3789 3/2006 V
4321 5/2005 V
4321 11/2005 A
4321 3/2006 V
7890 11/2005 I
Antwort2
Der Vollständigkeit halber zeige ich nun, wie ich das macheohneauf VBA zurückgreifen. Ich muss warnen, dass der folgende Code kompliziert ist und sich nur schwer effektiv skalieren lässt.
Nehmen wir folgende Anfangsbedingung an:
Blatt1
| A | B | C | D | …
---+----+---------+---------+---------+---
1 | ID | 05/2005 | 11/2005 | 03/2006 |
2 |2345| P | V | |
3 |3789| | A | V | …
4 |4321| V | A | V |
5 |7890| | I | |
… | …
Tabelle 2
| A | B | C |
---+----+------+---------------+
1 | ID | Date | Voting method |
2 | #1 | #2 | #3 |
Die Zelle mit dem Namen#1hat die folgende Formel:
=INDIRECT(ADDRESS(FLOOR((ROW(A2)-2)/27 + 2,0),1,,,"Sheet1"))
Diese Formel ordnet die aktuelle Zelle den entsprechenden Zellen in Blatt1 zu. Dies geschieht mithilfe der FLOORFunktion. Die Funktion wird immer dann um 1 erhöht, wenn 27 Zeilen übergeben wurden, sodass die Zeilen in Blatt2 korrekt dem Inhalt in Blatt1 zugeordnet werden.
Die ADDRESSFunktion erstellt aus numerischen Eingaben und Blattnamen eine verwendbare Referenz auf eine Zelle, während die INDIRECTFunktion den Inhalt abruft, auf den die Referenz zeigt.
Für die übrigen Funktionen gilt das gleiche Prinzip: Sie verwenden eine Hilfsfunktion, um die Koordinaten der aktuellen Zelle der richtigen Zelle in Blatt1 zuzuordnen.
Für die Zelle mit dem Namen#2:
=INDIRECT(ADDRESS(1,MOD(ROW(A2)-2,27)+2,,,"Sheet1"))
In diesem Fall MODwechselt die Funktion sequenziell zwischen 0 und 26, was dann in eine Sequenz zwischen 2 und 28 (also dort, wo die Zellen mit den Daten sind) umgewandelt wird.
Schließlich für die Zelle mit dem Namen#3:
=INDIRECT(ADDRESS(FLOOR((ROW(A2)-2)/27 + 2,0),MOD(ROW(A2)-2,27)+2,,,"Sheet1"))
Es handelt sich um eine Mischung der beiden zuvor verwendeten Sequenzen. Der Grund dafür ist, dass die Inhalte in Bezug auf die ID variieren (so ist der Teil aus der Zelle#1heißt) und Datum (wo der Teil aus der Zelle#2eintritt).
Nachdem Sie diese Funktionen in die richtigen Zellen eingegeben haben, ziehen Sie einfach nach unten und die Ergebnisse sollten sichtbar sein, mit einem kleinen Problem: Die Nullstimmen werden ebenfalls angezeigt.
Sie können diese Ergebnisse jedoch filtern. Wählen Sie die Kopfzeile (in diesem Fall die AZeile in Blatt 2) und gehen Sie zu Daten > Filter > Automatischer Filter (oder das Äquivalent in der von Ihnen verwendeten Excel-Version). Klicken Sie auf das Dropdown-Menü in der Spalte „Abstimmungsmethode“ und personalisieren Sie die Sortierung, um Ergebnisse auszuschließen, die aus Nullen bestehen.
Antwort3
Ich würde hierfür das Power Query-Add-In verwenden. Es erfordert weder Code noch komplexe Funktionen. Wenn Sie von Grund auf neu beginnen, dauert es wahrscheinlich weniger als 5 Minuten, diese Aufgabe abzuschließen.
Sie können eine Abfrage aus einer vorhandenen Excel-Tabelle starten. Ich würde dann den Befehl Unpivot verwenden, um die Daten nach Bedarf zu transformieren.
http://office.microsoft.com/en-au/excel-help/unpivot-columns-HA104053356.aspx
Das Schöne an ihrer Implementierung von Unpivot ist, dass sie alle weiteren hinzugefügten Spalten (neue Daten) akzeptiert und verarbeitet, ohne dass Ihre Abfragedefinition geändert wird.
Ich würde die Spalten nach Bedarf umbenennen und das Ergebnis in eine Excel-Tabelle liefern.