ピボットテーブルの元データが既に集計された表のとき

質問

上手く質問の内容が伝えられるか心配ですが。

ピボットテーブルは通常、元データの表は最上部にフィールド名があり、横軸にはフィールド名がないリスト化されたものを使用しますよね。

今回の質問は既に縦軸横軸にフィールド名がある、集計されている表をピボットテーブルの機能を使って色々な角度から集計したいとき、そのデータを整形する、またはそのままでもピボットに利用できる方法はないものかと考えています。

たとえば、横軸のフィールド名には「営業エリア」「支店名」と2段の項目があり、それぞれの支店の舌に、業績として「予算」「実績」「前年」と言うフィールド名が設定され、縦軸には「上半期、下半期」「月」と2段になったフィールド名があり、中にそれぞれの業績が表になっていると言う感じです。

なぜ、このようなことをしたいのかというと、私の会社で出力されるデータが全て集計表となっていて、自分のやりたい分析をピボットテーブルを使ってやるには適さないフォーマットでしかダウンロードできないからなんです。

縦軸、横軸にフィールド名がある集計された表を、ピボットでうまく使える方法や、エクセルのマクロなどどなたかご存じないでしょうか？

よろしくお願いします。

回答No.3

end-u · ベストアンサー

行と列のフィールド数が１つずつなら
『40-2 複数のワークシート範囲からのピボットテーブルを普通のピボットテーブルに変える』
http://www11.plala.or.jp/koma_Excel/contents6/mame6040/mame604002.html
これが使えたのでしょうけど、複数のフィールド数だとちょっとやっかいですね。

例えば、結合セルを解除して空白セルを直ぐ上（もしくは左）の値で補完できれば後はなんとかなるなら

Sub 結合解除() '結合解除し空白を埋めたい範囲を指定して実行
　　Dim frmL As String

　　If TypeName(Selection) <> "Range" Then MsgBox "cell select": Exit Sub
　　If MsgBox("結合セルの方向は縦？", vbYesNo) = vbYes Then
　　　　frmL = "=R[-1]C"
　　Else
　　　　frmL = "=RC[-1]"
　　End If
　　With Selection
　　　　.UnMerge
　　　　On Error Resume Next
　　　　.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = frmL
　　　　On Error GoTo 0
　　　　.Value = .Value
　　End With
End Sub

こんな感じのマクロで取り敢えず整形してみるというのも一つの手です。

ついでにちょっと考えてみましたが、ピボットテーブルと同じように
[行フィールド][列フィールド][データフィールド]と区分して考えれば、割と簡単そうです。

Sub Macro()
　　Dim rowF　 As Range '行フィールド
　　Dim colF　 As Range '列フィールド
　　Dim datF　 As Range 'データフィールド
　　Dim Target As Range 'コピー先
　　Dim x　　　As Long　'データフィールド列数
　　Dim y　　　As Long　'データフィールド行数
　　Dim z　　　As Long
　　Dim i　　　As Long

　　On Error Resume Next
　　'行、列、データフィールド選択する。行の項目名は含まず選択。
　　With Application
　　　　Set rowF = .InputBox("行フィールドを選択。", Type:=8)
　　　　If rowF Is Nothing Then GoTo extLine
　　　　Set colF = .InputBox("列フィールドを選択。", Type:=8)
　　　　If colF Is Nothing Then GoTo extLine
　　　　Set datF = .InputBox("データフィールドを選択。", Type:=8)
　　　　If datF Is Nothing Then GoTo extLine
　　End With
　　With datF
　　　　x = .Columns.Count
　　　　y = .Rows.Count
　　End With
　　'行列数で選択ミスチェック。その後結合解除し値補完。
　　If colF.Columns.Count <> x Then MsgBox "列数相違": GoTo extLine
　　With rowF
　　　　If .Rows.Count <> y Then MsgBox "行数相違":　GoTo extLine
　　　　.UnMerge
　　　　.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
　　　　.Value = .Value
　　　　z = .Columns.Count
　　End With
　　With colF
　　　　.UnMerge
　　　　.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC[-1]"
　　　　.Value = .Value
　　　　z = z + .Rows.Count
　　End With
　　On Error GoTo 0

　　'データ変換して新規Bookに展開。
　　With Sheets.Add
　　　　'項目名セット。実際には後で手入力修正。
　　　　With .Range("A1")
　　　　　　.Value = "field1"
　　　　　　.AutoFill .Resize(, z)
　　　　　　.Offset(, z).Value = "data"
　　　　End With
　　　　'行フィールドをコピー。（データフィールドの行数×列数）行必要。
　　　　rowF.Copy .Range("A2").Resize(x * y)
　　　　Set Target = .Range("A2").Offset(, rowF.Columns.Count)
　　End With

　　'列フィールドとデータフィールドを各列ごとに繰り返しコピー。
　　z = colF.Rows.Count
　　For i = 1 To x
　　　　colF.Columns(i).Copy
　　　　Target.Resize(y, z).PasteSpecial Paste:=xlPasteValues, _
　　　　　　　　　　　　　　　　　　　　 Transpose:=True
　　　　datF.Columns(i).Copy Target.Offset(, z)
　　　　'次コピー位置セット。
　　　　Set Target = Target.Offset(y)
　　Next

extLine:
　　Set Target = Nothing
　　Set colF = Nothing
　　Set rowF = Nothing
　　Set datF = Nothing
End Sub

行フィールド選択では画像の赤のエリア、A3:B9
列フィールド選択では画像の緑のエリア、C1:F2
データフィールド選択では画像の青のエリア、C3:F9
...をそれぞれ選択します。ある程度は汎用的に対応できるかと。

お礼

マクロのコードまでつけていただいて、ありがとうございます。
私の理解できない書式もあるので、これからゆっくり理解を指定校と思いますが、実際に動かしてみて思ったとおりの動作が確認でき驚いています。
私もちょっとずつマクロを組み始めたのですが、はじめの結合セルを解除するところまでは、組み立てとして考えていたのですが、結合セルの個数を数えて、ＤＯ　ＬＯＯＰなどをつかってやってみようと思ってました。またまだ到底組みきれていませんが。

早速使いたいと思います。コードも勉強してみます。ありがとうございました。

回答No.2

MackyNo1

>今回張った右側の表は今年に値が入っていれば必ず前年には数値が入っていない状態になってしまっています。しかし私が例としてあげたかったのは必ずしもそうではありません。全てに数字が入っています。

実際のリストを例示されるなど、正確な情報を提示されないと的確な回答は得られないと思います（特にサンプルコードを提示してもらうときは重要）。

＞また、上の行も項目が1段にしかなっていませんが、左の軸と同じように、結合セルが含まれる多層になった表になります。この（結合セルで表される）階層が、2段だったり、3段だったり、その時のダウンロードしたいデータの種類によって毎回変わってしまいます。

この部分も、リストの形式が同じレイアウトなら数式で対応できる可能性がありますが、レイアウトが種々変わるリストからのデータ取得をVBAで実行するには、大変なプログラミングが必要になります。

実戦的には、元データを吐き出す部分のプログラムを変更したほうが圧倒的に簡単だと思いますので、そちらをチャレンジされたほうがよいのではないでしょうか。

補足

大変申し訳ありません。ご助言ありがとうございます。

>実戦的には、元データを吐き出す部分のプログラムを変更したほうが圧倒的に簡単だと思いますので、そちらをチャレンジされたほうがよいのではないでしょうか。

なにせ私はサラリーマンで、既存のシステムを私の一言でプログラムを変えてくれるほど、優しい会社ではないのです。コストもかかりますし。

データをクロス集計するのにピボットテーブルの機能を知らない人がいるように、クロス集計の表を戻す便利な機能や方法、クロス集計された表でもピボットテーブルが使える機能がエクセルに備わっていないのかどうか。または、そういった便利なマクロが流通していないかどうかと思ったものですから。

文章で表現するのがどうも下手なので、大変申し訳ありませんでした。
出来るかどうかわかりませんが、自分でマクロを組む方向で考えて見ます。そこでまた行き詰ったら改めて質問させていただきます。

ありがとうございました。

回答No.1

MackyNo1

右側の表のデータの項目名がA1セルから入力されているなら、以下のような処理で左側のテーブルに処理できます。

G1セルから右に場所、氏名、・・・と項目名を入力しておきます。

A2セルから下方向に結合セルを含むデータ範囲を選択して、右クリック「コピー」、G2セルを選択して右クリック「形式を選択して貼り付け」で「数式」にします。
H2セルには「=IF(C2>0,"今年",IF(D2>0,"前年"))」I2セルには「=B2&""」J2セルには「=E2」と入力し、最大データ数まで大きめに下方向にオートフィルコピーしておきます。

A2セルから下方向に結合セルを含むデータ範囲を大きめに選択して、右クリック「コピー」、G2セルを選択して右クリック「形式を選択して貼り付け」で「数式」にします。
そのまま（G列が選択された状態で）、Ctrl+Gで「セル選択」「空白セル」とし、数式バーに「=G2」と入力し、Ctrl+Enterで確定します。

上記の操作で、データの変換が完了しますが、これを自動的に行うなら、後半の数式貼り付けの部分をマクロの記録に登録しておけば、簡単に同じ操作を実行することができます（このテンプレートシートのA1セルに元データを貼り付ける操作をマクロで記録すればデータの取り込みから自動化できます）。

最終的には、このデータ範囲を「形式を選択して貼り付け」で「値」貼り付けでピボットテーブル用の元データに追加します（この部分も少し工夫すればマクロの記録だけで対応することができます）。

補足

早速、回答頂きありがとうございます。

ただ、私の質問の仕方や例に貼ったサンプルもいけなかったと思います。今回張った右側の表は今年に値が入っていれば必ず前年には数値が入っていない状態になってしまっています。しかし私が例としてあげたかったのは必ずしもそうではありません。全てに数字が入っています。また、上の行も項目が1段にしかなっていませんが、左の軸と同じように、結合セルが含まれる多層になった表になります。

この（結合セルで表される）階層が、2段だったり、3段だったり、その時のダウンロードしたいデータの種類によって毎回変わってしまいます。

うまく説明できなくて大変申し訳ありません。