Jak „cofnąć przestawienie” lub „odwrócić pivot” w programie Excel?

Mam dane, które wyglądają tak:

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

I chcę przekonwertować to na to:

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

Jak najłatwiej to zrobić w programie Excel 2007?

Możesz to zrobić za pomocą tabeli przestawnej.

1. Utwórz „tabelę przestawną z wieloma zakresami konsolidacji”. (Tylko w Kreatorze tabeli przestawnej. Wywołanie z ALT+ D, Pw programie Excel 2007)
2. Wybierz „Stworzę własne pola strony”.
3. Wybierz swoje dane.
4. Dwukrotnie kliknij wielką wartość całkowitą - tę na przecięciu Row Grand i Column Grand , aż do prawego dolnego rogu tabeli przestawnej.

Powinieneś zobaczyć nowy arkusz zawierający wszystkie dane w tabeli przestawnej, transponowane tak, jak szukasz.

Technologie Datapig zapewniają instrukcje krok po kroku, które są w rzeczywistości bardziej skomplikowane niż potrzebujesz - jego przykład transponuje tylko część zbioru danych i wykorzystuje technikę przestawną w połączeniu z TextToColumns . Ale ma wiele zdjęć.

Pamiętaj, że tabela przestawna grupuje dane. Jeśli chcesz go rozgrupować, jedynym sposobem na to jest skopiowanie tabeli przestawnej i „wklejenie specjalne” jako wartości. Następnie możesz wypełnić puste pola za pomocą następującej techniki: http://www.contextures.com/xlDataEntry02.html

Jeśli twoje dane nie są tabelą przestawną programu Excel, ale tylko danymi, możesz chcieć ją „cofnąć” za pomocą prostego kodu VBA. Kod zależy od dwóch nazwanych zakresów, źródłowego i docelowego. Źródło to dane, które chcesz cofnąć przestawienie (z wyjątkiem nagłówków kolumn / wierszy, np. NY-RI w próbce), a Obiekt docelowy to pierwsza komórka, w której chcesz umieścić wynik.

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

Zbudowałem dodatek, który pozwoli ci to zrobić, a to ułatwi dostosowanie się do różnych sytuacji. Sprawdź to tutaj: http://tduhameau.wordpress.com/2012/09/24/the-unpivot-add-in/

Do tej pory najlepiej wymyśliłem:

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)

To działa, ale muszę wygenerować identyfikator i LocNum ręcznie. Jeśli istnieje bardziej zautomatyzowane rozwiązanie (oprócz pisania makra), daj mi znać w osobnej odpowiedzi.

W programie Excel 2010 jest całkiem fajne rozwiązanie, wystarczy trochę zagrać przy stole przestawnym.

Utwórz tabelę przestawną na podstawie danych przy użyciu następujących ustawień:

• bez sum częściowych, bez sum całkowitych
• układ raportu: formularz tabelaryczny, powtórz wszystkie etykiety pozycji
• dodaj wszystkie potrzebne kolumny, zachowaj kolumny, które chcesz przekształcić, po prawej stronie
• dla każdej kolumny, którą chcesz przekształcić: otwórz ustawienia pola - na karcie układu i wydruku: wybierz „Pokaż etykiety elementów w formie konspektu” i zaznacz oba pola wyboru poniżej
• skopiuj tabelę do osobnej lokalizacji (tylko wartości)
• jeśli masz puste komórki w oryginalnych danych, odfiltruj puste wartości w prawej kolumnie i usuń te wiersze (nie filtruj w tabeli przestawnej, ponieważ nie będzie działać tak, jak potrzebujesz!)

Jeśli wymiary twoich danych są takie, jak w przykładzie podanym w pytaniu, to następujący zestaw formuł używających OFFSET powinien dać ci wymagany wynik:

Zarozumiały

1 | NY | CA | TX | IL

2 | WA | LUB | NH | RI

są w zakresie A2: E3, a następnie wprowadź

= PRZESUNIĘCIE (2 $ A $, PODŁOGA ((RZĘD (A2) -RROW (2 $ A $)) / 4,1), 0)

w F2, powiedzmy, i

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

w G2, powiedzmy, i

= PRZESUNIĘCIE ($ B $ 2, PODŁOGA ((WIERSZ (B2) -ROW ($ B $ 2)) / 4,1), MOD (WIERSZ (A2) -ROW ($ A $ 2), 4))

powiedzmy w H2.

Następnie skopiuj te formuły w miarę potrzeb.

Jest to najłatwiejsze, czyste, wbudowane rozwiązanie do formuł, jakie mogę wymyślić.

Wygląda na to, że masz kolumnę „loc” (o czym świadczy Twoja pierwsza odpowiedź), a teraz potrzebujesz pomocy w uzyskaniu pozostałych dwóch kolumn.

Pierwszą opcją jest po prostu wpisanie pierwszych kilku (powiedzmy 12) wierszy w tych kolumnach i przeciągnięcie w dół - myślę, że Excel robi w tym przypadku właściwą rzecz (nie mam programu Excel na tym komputerze, aby to sprawdzić na pewno).

Jeśli to nie zadziała, lub jeśli chcesz czegoś bardziej programistycznego, spróbuj użyć funkcji row (). Coś w rodzaju „= Floor (row () / 4)” dla kolumny ID i „= mod (row (), 4) +1” dla kolumny LocNum.

Istnieje parametryczne narzędzie do konwersji VBA do odwracania lub odwracania przestawnych danych z powrotem do tabeli bazy danych, zobacz

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

Oto fajne narzędzie do rozpinania, normalizowania tabeli przestawnej.

Normalisieren von Pivot Tabellen

Mam nadzieję, że to pomoże.

Odpowiedź @DaveParillo jest najlepszą odpowiedzią na tabelę z pojedynczą kartą. Chciałem tutaj dodać dodatki.

W przypadku wielu kolumn poprzedź kolumny przestawne

Ta metoda nie działa.

W YouTube można przestawić proste dane, takie jak pytanie

To jest film na YouTube pokazujący, jak to zrobić w prosty sposób. Pominąłem część dotyczącą dodawania skrótu i ​​użyłem skrótu @devuxer, który jest w odpowiedzi DaveParillo.

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

To jest o wiele prostsze niż to. Wystarczy kliknąć opcję „Transponuj” w „Wklej specjalnie ...”, aby uzyskać żądaną transpozycję.