Wie kann man in Excel das Pivot "aufheben" oder "umkehren"?

106389
devuxer

Ich habe Daten, die so aussehen:

Id | Loc1 | Loc2 | Loc3 | Loc4 ---+------+------+------+----- 1 | NY | CA | TX | IL 2 | WA | OR | NH | RI 

Und ich möchte es umwandeln:

Id | LocNum | Loc ---+--------+---- 1 | 1 | NY 1 | 2 | CA 1 | 3 | TX 1 | 4 | IL 2 | 1 | WA 2 | 2 | OR 2 | 3 | NH 2 | 4 | RI 

Was ist der einfachste Weg, dies in Excel 2007 zu tun?

63
Die Antwort gefunden: http://stackoverflow.com/questions/32115219/unpivot-an-excel-matrix-pivot-table/32116657#32116657 Quandary vor 9 Jahren 0

11 Antworten auf die Frage

66
DaveParillo

Sie können dies mit einer Pivot-Tabelle tun.

  1. Erstellen Sie eine "PivotTable für mehrere Konsolidierungsbereiche". (Nur auf Pivot - Tabellen - Assistenten. Callup mit ALT+ D, Pauf Excel 2007)
  2. Wählen Sie "Ich werde meine eigenen Seitenfelder erstellen".
  3. Wählen Sie Ihre Daten aus.
  4. Doppelklicken Sie auf den Gesamtwert - den Wert an der Kreuzung von Row Grand und Column Grand - ganz unten rechts in Ihrer Pivot-Tabelle.

Sie sollten ein neues Blatt mit allen Daten in Ihrer Pivot-Tabelle sehen, die in der von Ihnen gewünschten Art und Weise transponiert sind.

Datapig-Technologien bieten Schritt-für-Schritt-Anweisungen, die tatsächlich komplizierter sind als Sie benötigen. In seinem Beispiel wird nur ein Teil des Datensatzes umgesetzt und die Pivot-Technik in Kombination mit TextToColumns verwendet . Aber es gibt viele Bilder.

Beachten Sie, dass eine Pivot-Tabelle die Daten gruppiert. Wenn Sie die Gruppierung aufheben möchten, können Sie dies nur tun, indem Sie die Pivot-Tabelle kopieren und "special" als Werte einfügen. Sie können dann die Lücken mit einer Technik wie folgt ausfüllen: http://www.contextures.com/xlDataEntry02.html

Danke für Ihre Antwort, aber ich bin nicht ganz sicher, was Sie hier sagen. Was würde ich für das Beispiel in meiner Frage für Zeilenbeschriftungen, Spaltenbeschriftungen, Werte und Berichtsfilter verwenden? devuxer vor 14 Jahren 0
Wenn ich Id in Row Labels und Loc1 bis Loc4 in Column Labels eingebe, dann auf die untere rechte Zelle (den Schnittpunkt der Gesamtsummen) klicke, wird zwar ein neues Blatt mit einer Tabelle erstellt, aber es ist nicht das, was ich will. Es ist im Grunde nur eine Kopie der normalen Pivot-Tabelle. devuxer vor 14 Jahren 0
Es tut mir leid - ich habe eine wichtige Datenrolle ausgelassen. Die Pivot-Tabelle ** muss ** eine Pivot-Tabelle vom Typ 'Multiple Consolidation Range' sein, auch wenn Sie in diesem Fall nichts zu konsolidieren haben. DaveParillo vor 14 Jahren 0
Dave, vielen Dank, dass Sie Ihre Antwort bearbeitet haben, aber sind Sie sicher, dass diese Anweisungen für Excel 2007 gelten? Ich sehe nichts über "mehrere Konsolidierungsbereiche". devuxer vor 14 Jahren 0
Ah okay, ich habe in der Hilfe nachgesehen und eine Hintertür gefunden, um den alten Pivot-Tabellen-Assistenten in Excel 2007 zu erhalten (Sie müssen ALT + D + P drücken). Jetzt kann ich deinen Schritten folgen und es scheint zu funktionieren. Danke für Ihre Hilfe! devuxer vor 14 Jahren 4
Definitely works, thanks! I had some trouble following DaveParillo's condensed instructions, and I found that I needed to follow DataPig's step-by-step instructions to get everything to work. Pictures! Kent Hu vor 11 Jahren 0
Genau wie folgt: http://stackoverflow.com/questions/32115219/unpivot-an-excel-matrix-pivot-table/32116657#32116657 Quandary vor 9 Jahren 0
Kann dies funktionieren, wenn * zwei * Ebenen für Spaltenüberschriften vorhanden sind? Zum Beispiel hat dies bereits Loc1, Loc2, Loc3, Loc4 - aber was ist, wenn eine Zeile darüber. Loc1 und Loc2 befinden sich in LocGroup1 und Loc3 und Loc4 in LocGroup2? The Red Pea vor 8 Jahren 0
Ooh, ich kann * 2 * Levels machen, aber es ist eine neue Gruppe von Schritten (insbesondere das Hinzufügen von zwei Bereichen, eines für LocGroup1 und eines für LocGroup2), und * Manuelles * Benennen jedes dieser Bereiche für den entsprechenden Grouper. Hat noch jemand diese Erfahrung? The Red Pea vor 8 Jahren 0
6
TJMelrose

If your data isn't an Excel Pivot Table but just data, you might want to "un-pivot" it with some simple VBA code. The code depends on two named ranges, Source and Target. Source is the data you want to un-pivot (exclusive of the column/row headers, e.g. NY-RI in the sample) and Target is the first cell where you want to place your result.

Sub unPivot() Dim oTarget As Range Dim oSource As Range Dim oCell As Range Set oSource = Names("Source").RefersToRange Set oTarget = Names("Target").RefersToRange For Each oCell In oSource If oCell.Value <> "" Then oTarget.Activate ' get the column header oTarget.Value = oCell.Offset(-(oCell.Row - oSource.Row + 1), 0).Text ' get the row header oTarget.Offset(0, 1).Value = oCell.Offset(0, _ -(oCell.Column - oSource.Column + 1)).Text ' get the value oTarget.Offset(0, 2).Value = oCell.Text ' move the target pointer to the next row Set oTarget = oTarget.Offset(1, 0) End If Next Beep End Sub 
Danke dafür. Funktioniert wie ausnahmslos und ich kann viel Zeit sparen. tigrou vor 11 Jahren 1
Vielen Dank!! das ist einfach magisch! viel besser als jigiry-pokery mit unpivotisierenden Berichten, die für mich nie funktionierten trailmax vor 10 Jahren 0
Das sieht gut aus, aber so gut ich das beurteilen kann, funktioniert es nur für eine Tabelle, die nur einen Zeilenheader hat. Wenn die Tabelle im Beispiel mehrere Zeilenüberschriften hätte, sagen wir, sie hätte zusätzlich zum Feld Id eine "SubId", würde dies nicht funktionieren. Gibt es eine einfache Möglichkeit, es so zu ändern, dass es in dieser Situation funktionieren würde? Chris Stocking vor 9 Jahren 0
5
nutsch

Ich habe ein Add-In entwickelt, mit dem Sie dies tun können. Dadurch können Sie sich problemlos an verschiedene Situationen anpassen. Schaut es euch hier an: http://tduhameau.wordpress.com/2012/09/24/the-unpivot-add-in/

3
devuxer

Am besten habe ich mir bisher ausgedacht:

Id LocNum Loc --------------------------------- 1 1 =INDEX(Data,A6,B6) 1 2 =INDEX(Data,A7,B7) 1 3 =INDEX(Data,A8,B8) 1 4 =INDEX(Data,A9,B9) 2 1 =INDEX(Data,A10,B10) 2 2 =INDEX(Data,A11,B11) 2 3 =INDEX(Data,A12,B12) 2 4 =INDEX(Data,A13,B13) 

Das funktioniert, aber ich muss die Ids und LocNums manuell generieren. Wenn es eine automatisiertere Lösung gibt (abgesehen vom Schreiben eines Makros), teilen Sie mir dies bitte in einer separaten Antwort mit.

3
Máté Juhász

Es gibt eine ziemlich gute Lösung in Excel 2010, man muss nur ein bisschen mit Pivot Table spielen.

Erstellen Sie eine Pivot-Tabelle aus Ihren Daten mit diesen Einstellungen:

  • Keine Zwischensummen, keine Gesamtsummen
  • Berichtslayout: tabellarisch, alle Artikelbeschriftungen wiederholen
  • Fügen Sie alle Spalten hinzu, die Sie benötigen, und halten Sie die Spalten, die Sie umwandeln möchten, rechts
  • Für jede Spalte, die Sie transformieren möchten: Feldeinstellungen öffnen - Auf der Registerkarte Layout und Druck: Wählen Sie "Elementbeschriftungen in Umrissform anzeigen" und aktivieren Sie die beiden Kontrollkästchen darunter
  • Kopieren Sie Ihre Tabelle an einen anderen Ort (nur Werte)
  • Wenn Ihre ursprünglichen Daten leere Zellen enthalten, filtern Sie in der rechten Spalte nach leeren Werten und löschen Sie diese Zeilen (nicht in der Pivot-Tabelle filtern, da sie nicht wie gewünscht funktioniert!)
Dies ist sicherlich die beste Antwort, wenn Sie 2010 oder älter sind und in der Benutzeroberfläche überhaupt nicht offensichtlich sind. Toller Fund - leider habe ich nur eine positive Bewertung! jkf vor 6 Jahren 1
2
Aaa

Wenn die Dimensionen Ihrer Daten den Angaben in Ihrer Frage entsprechen, sollten Sie mit dem folgenden Satz von Formeln, die OFFSET verwenden, Ihr gewünschtes Ergebnis erhalten:

Angenommen,

1 | NY | CA | TX | IL

2 | WA | ODER | NH | RI

im Bereich A2: E3 liegen, dann eingeben

= OFFSET ($ A $ 2, FLOOR ((ROW (A2) -ROW ($ A $ 2)) / 4,1), 0)

in F2 sagen wir und

= MOD (ROW (A2) -ROW ($ A $ 2), 4) + 1

in G2 sagen wir und

= OFFSET ($ B $ 2, FLOOR ((ROW (B2) -ROW ($ B $ 2)) / 4,1), MOD (ROW (A2) -ROW ($ A $ 2), 4))

in H2 sagen wir.

Dann kopieren Sie diese Formeln nach Bedarf.

Dies ist die einfachste, reine, integrierte Formellösung, die ich mir vorstellen kann.

1

Sie scheinen die "loc" -Spalte erhalten zu haben (belegt durch Ihre erste Antwort), und jetzt brauchen Sie Hilfe, um die anderen beiden Spalten zu erhalten.

Ihre erste Option ist, einfach die ersten (z. B. 12) Zeilen in diese Spalten einzugeben und nach unten zu ziehen. Ich denke, dass Excel in diesem Fall das Richtige tut (ich habe kein Excel auf diesem Computer, um es sicher zu testen).

Wenn dies nicht funktioniert oder Sie mehr Programmierer-y wünschen, versuchen Sie es mit der Funktion row (). So etwas wie "= Floor (row () / 4)" für die ID-Spalte und "= mod (row (), 4) +1" für die LocNum-Spalte.

1
Petros

Es gibt ein Hilfsprogramm für die parametrische VBA-Konvertierung, um die geschwenkten Daten in eine Datenbanktabelle rückgängig zu machen oder umzukehren

http://www.spreadsheet1.com/unpivot-data.html

Willkommen bei Super User! Während dies die Frage theoretisch beantworten kann, ist es [bevorzugt] (http://meta.stackexchange.com/q/8259), die wesentlichen Teile der Antwort hier aufzunehmen und den Link als Referenz bereitzustellen. Peachy vor 12 Jahren 1
1
L4a-Thompson

Hier ist ein schönes Werkzeug, um eine Pivot-Tabelle zu entfernen.

Normalisieren von Pivot Tabellen

Ich hoffe, es hilft.

Willkommen bei SuperUser! Während das von Ihnen verlinkte Tool eine Lösung bietet, ist dieser Beitrag nur nützlich, solange der Link aktiv bleibt. Um sicherzustellen, dass Ihr Beitrag auch in Zukunft nützlich sein wird, [edit] (http://superuser.com/posts/745524/edit), geben Sie bitte Ihre Antwort an, um weitere Informationen zum Produkt hinzuzufügen, einschließlich der Verwendung des Postens zur Lösung des Problems Problem in der Frage beschrieben. Vielen Dank! Excellll vor 10 Jahren 1
1
BrinkDaDrink

Die Antwort von @DaveParillo ist die beste Antwort für eine Tabulatortabelle. Ich wollte hier ein paar Extras hinzufügen.

Bei mehreren Spalten werden die Unpivot-Spalten vorangestellt

Diese Methode funktioniert nicht.

Youtube löst keine einfachen Daten wie die Frage auf

Dies ist ein Youtube-Video, das auf einfache Weise zeigt, wie es geht. Ich habe den Teil über das Hinzufügen der Verknüpfung übersprungen und in der DaveParillo-Antwort eine @devuxer-Verknüpfung verwendet.

https://www.youtube.com/watch?v=pUXJLzqlEPk

Der Inhalt Ihrer Antwort ist ein Link zu einem Video und der Inhalt wird nicht beschrieben. Wenn der Link abbricht oder nicht verfügbar ist, hätte Ihre Antwort keinen Wert. Bitte geben Sie die wesentlichen Informationen in Ihre Antwort ein und verwenden Sie den Link nur für die Zuordnung und weitere Erkundung. fixer1234 vor 9 Jahren 3