Ich habe eine große Tabelle mit täglichen Eingaben in ein System, die die folgenden Spalten enthält:
Tag Artikelname1 Artikelmenge1 Artikelname2 Artikelmenge2 .....
Die Artikelnamen und Mengen sind voneinander unabhängig. An einem Tag erhält man möglicherweise 10 Artikel A, an einem anderen Tag 5 Artikel D, 6 Artikel B und 234 Artikel C. Im ersten Fall enthalten nur 2 Spalten Daten, im zweiten Fall 6 Spalten. Alle anderen Spalten sind leer. Ein Beispiel:
Day N1 Q1 N2 Q2 N3 Q3
1 A 10
2 D 5 B 6 C 234
Wie könnte eine Pivot-Tabelle beispielsweise verwendet werden, um die täglichen Mengen von A über ein Jahr oder die Anzahl der von Null verschiedenen Werte von A in einem Jahr anzuzeigen? Beachten Sie, dass A in mehreren Spalten erscheinen kann und es Hunderte von möglichen Elementen gibt. Daher ist es nicht möglich, einfach eine Tabelle mit jeder Variable in einer Spalte zu erstellen.
Ich habe versucht, alle Spalten auf mehrere Arten hinzuzufügen, aber letztendlich werden bei gegebenen Werten von B und gegebenen Werten von C die Werte von A zurückgegeben. Das ist nützlich, wenn die Werte abhängig sind, führt aber zu einer Überfrachtung der Seite, da sie nicht korreliert sind.
Wenn dies nicht möglich ist, was wäre eine gute Methode, um die erforderlichen Informationen aus der Tabelle zu erhalten?
Antwort1
Sie müssen Ihre Daten dringend neu organisieren. Aus den Gründen, die Sie jetzt kennen, ist es wirklich keine gute Idee, eine unbestimmte Anzahl von Spalten zu haben. Mein Rat ist, wie andere bereits vorgeschlagen haben, Ihre Daten in eine einfache dreispaltige Tabelle mit einer Zeile für jeden Tageseintrag umzuwandeln.
Day Item Quant
1 A 10
2 D 5
2 B 6
2 C 234
Anschließend können Sie eine Pivot-Tabelle verwenden, um die Daten nach Wunsch zusammenzufassen und detaillierter zu untersuchen.
Die Frage ist nun, wie man diese Daten in einer unbestimmten Anzahl von Spalten neu organisieren kann. Glücklicherweise lässt sich das mit einem ziemlich einfachen VBA-Verfahren erledigen. Fügen Sie den folgenden Code in ein neues VBA-Modul ein (drücken Sie Alt+ F11und gehen Sie dann zu Insert> Module).
Sub SalvageMyTable()
Dim sOrig As Worksheet, sOut As Worksheet
Dim arrIn() As Variant
Dim rOut As Range, arrOut() As Variant
Dim i As Long, j As Long, k As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set sOrig = ActiveSheet
'Store original data in an array for faster processing
arrIn = sOrig.UsedRange.Value
'Create maximal output array. This can be resized before outputting the data.
ReDim arrOut(0 To 2, 0 To UBound(arrIn, 1) * (UBound(arrIn, 2) - 1) / 2) As Variant
'Loop through original table and copy values to output array.
For i = LBound(arrIn, 1) + 1 To UBound(arrIn, 1)
For j = LBound(arrIn, 2) + 1 To UBound(arrIn, 2) Step 2
If arrIn(i, j) <> "" Then
arrOut(0, k) = arrIn(i, LBound(arrIn, 2))
arrOut(1, k) = arrIn(i, j)
arrOut(2, k) = arrIn(i, j + 1)
k = k + 1
Else
Exit For
End If
Next j
Next i
'Resize output array
ReDim Preserve arrOut(0 To 2, 0 To k) As Variant
'Create output sheet and print output there.
Set sOut = Worksheets.Add
sOut.Name = "Reorganized Data"
sOut.Range("A1").Value = "Day"
sOut.Range("B1").Value = "Item"
sOut.Range("C1").Value = "Quantity"
sOut.Range("A2").Resize(k, 3).Value = Application.WorksheetFunction.Transpose(arrOut)
'Reset Excel settings
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Stellen Sie sicher, dass das Arbeitsblatt mit Ihren Originaldaten geöffnet ist. Wechseln Sie dann zum VBA-Editor und führen Sie diesen Code aus (durch Drücken von F5). Dieser Code gibt die transformierten Daten in drei Spalten auf einem neuen Arbeitsblatt mit dem Namen „Reorganisierte Daten“ aus.
Antwort2
Um die Datenanalyse für diesen Datensatz jemals zuverlässig abschließen zu können, müssen Sie die Einrichtung/Organisation/Eingabe der Daten ändern.
Dies lässt sich in Excel erledigen, aber auch die Einrichtung einer Datenbank wäre recht einfach.
Tabelle für Artikel (Artikelnummer, Name, Beschreibung)
Tabelle für Fahrzeuge (Fahrzeugnummer, Name, Beschreibung, Typ, Marke, Modell)
Tabelle für Verwendung (Artikelnummer, Fahrzeugnummer, Datum, verwendete Nummer)
Sie können dies auch in Excel mit verschiedenen Tabellenblättern tun und mithilfe der Datenüberprüfung eine Dropdown-Liste mit den Artikeln und Fahrzeugen im Nutzungsblatt erstellen. Wenn Sie keine Programmiererfahrung haben, ist dies möglicherweise die beste Option.
Es tut mir leid, dass ich Ihr Problem nicht beantworten kann, aber das eigentliche Problem ist die Organisation Ihrer Daten.
Antwort3
Sie konvertieren zunächst die Tabelle in das unten stehende Format „Tag NQ“ mit Beispieldaten als
1 A 10
2 D 6
2 B 6
2 C 234
Wenn Sie dann diese Tabelle pivotieren, sollten Sie das Ergebnis erhalten. Um es in die obige Tabelle zu konvertieren, erstellen Sie ein neues Blatt. Angenommen, Sie haben 10 Zeilen und 3 Name-Mengen-Paare im Hauptblatt (unter der Annahme, dass Blatt1 der Name des Blatts ist und nicht die Kopfzeile/-spalte), dann können Sie die folgende Formel im neuen Blatt von Zeile 1 bis 30 verwenden (10*3)
1 =Sheet1!A1 =Sheet1!B1 =Sheet1!C1
...
10 =Sheet1!A10 =Sheet1!B10 =Sheet1!C10
11 =Sheet1!A1 =Sheet1!D1 =Sheet1!E1
...
20 =Sheet1!A10 =Sheet1!D10 =Sheet1!E10
21 =Sheet1!A1 =Sheet1!F1 =Sheet1!G1
...
30 =Sheet1!A10 =Sheet1!F10 =Sheet1!G10
Antwort4
Nachfolgend finden Sie das folgende Bild. Die Einsen stehen für den Teil, der gewartet wurde, und die Nullen stehen für den Teil, der „nicht ausgetauscht“ wurde. Wenn Ihre Daten so dargestellt werden, können Sie Ihr Problem mithilfe einer einfachen Pivot-Tabelle lösen.
