エクセルで「ピボット解除」や「逆ピボット」をするには？

こんな感じのデータがあります

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

そして、これに変換したい

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

エクセル2007で一番簡単な方法は？

　 69　 devuxer　 2009-12-03

ベストアンサー

ピボットテーブルを使って、こんなことができます

“複数の連結範囲のピボットテーブル “を作成します。(ピボットテーブルウィザードのみ。 Excel 2007ではALT+D, Pで呼び出し)
自分でページフィールドを作成します」を選択
データを選択してください
グランド合計値をダブルクリックして、ピボットテーブルの右下隅にある「行グランド」と「列グランド」の交点をダブルクリックします

ピボットテーブルのすべてのデータを含む新しいシートが表示され、探している方法で転置されているはずです

Datapig technologies は、ステップバイステップの説明を提供していますが、実際には必要以上に複雑です – 彼の例では、データセットの一部だけを転置しています & TextToColumns と組み合わせたピボット技術を使用しています。しかし、それはたくさんの写真を持っています

ピボットテーブルはデータをグループ化するので注意してください。グループ化されていない状態にしたい場合は、ピボットテーブルをコピーして、値として「特殊なものを貼り付ける」しか方法はありません。あとは、このようなテクニックで空白を埋めることができます。http://www.contextures.com/xlDataEntry02.html

70　 DaveParillo　 2009-12-03

データがExcelのピボット・テーブルではなく、ただのデータであれば、簡単なVBAコードを使ってピボットを「解除」したいかもしれません。コードは、ソースとターゲットという2つの名前の範囲に依存します。ソースはピボットを解除したいデータ（サンプルのNY-RIなどの列/行ヘッダを除く）で、ターゲットは結果を配置したい最初のセルです

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

7　 TJMelrose　 2013-04-15

それをさせてくれるアドインを作ったので、いろいろな状況にも対応しやすくなりました。ここでチェックアウトしてみてください。http://tduhameau.wordpress.com/2012/09/24/the-unpivot-add-in/

5　 nutsch　 2013-09-21

excel 2010で非常に良いソリューションがありますが、ちょうどピボットテーブルとビットを再生する必要があります

これらの設定でデータからピボットテーブルを作成します

小計も大計もない
レポートのレイアウト：表形式、すべての項目のラベルを繰り返します
必要なカラムをすべて追加し、変換したいカラムは右に置いておきます
変換したい各列のために：フィールド設定を開きます – レイアウトと印刷タブで：「アウトラインフォームにアイテムラベルを表示する」を選択し、その下にある両方のチェックボックスにチェックを入れます
テーブルを別の場所にコピーします(値だけ)
元のデータに空のセルがある場合は、右端の列の空の値をフィルタリングして、それらの行を削除します（ピボットテーブルでフィルタリングしてはいけません

4　 Máté Juhász　 2015-03-12

今までに思いついた最高のものはこれだ

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)

これは動作しますが、私は手動でIdとLocNumを生成しなければなりません。もっと自動化された解決策があれば（マクロを書く以外に）、別の回答で教えてください

3　 devuxer　 2009-12-03

データの寸法がご質問のサンプルの通りであれば、OFFSETを使用した以下の式のセットで必要な結果が得られるはずです

Assuming