上手く質問の内容が伝えられるか心配ですが。
ピボットテーブルは通常、元データの表は最上部にフィールド名があり、横軸にはフィールド名がないリスト化されたものを使用しますよね。
今回の質問は既に縦軸横軸にフィールド名がある、集計されている表をピボットテーブルの機能を使って色々な角度から集計したいとき、そのデータを整形する、またはそのままでもピボットに利用できる方法はないものかと考えています。
たとえば、横軸のフィールド名には「営業エリア」「支店名」と2段の項目があり、それぞれの支店の舌に、業績として「予算」「実績」「前年」と言うフィールド名が設定され、縦軸には「上半期、下半期」「月」と2段になったフィールド名があり、中にそれぞれの業績が表になっていると言う感じです。
なぜ、このようなことをしたいのかというと、私の会社で出力されるデータが全て集計表となっていて、自分のやりたい分析をピボットテーブルを使ってやるには適さないフォーマットでしかダウンロードできないからなんです。
縦軸、横軸にフィールド名がある集計された表を、ピボットでうまく使える方法や、エクセルのマクロなどどなたかご存じないでしょうか?
よろしくお願いします。
No.3ベストアンサー
- 回答日時:
行と列のフィールド数が1つずつなら
『40-2 複数のワークシート範囲からのピボットテーブルを普通のピボットテーブルに変える』
http://www11.plala.or.jp/koma_Excel/contents6/ma …
これが使えたのでしょうけど、複数のフィールド数だとちょっとやっかいですね。
例えば、結合セルを解除して空白セルを直ぐ上(もしくは左)の値で補完できれば後はなんとかなるなら
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
...をそれぞれ選択します。ある程度は汎用的に対応できるかと。
マクロのコードまでつけていただいて、ありがとうございます。
私の理解できない書式もあるので、これからゆっくり理解を指定校と思いますが、実際に動かしてみて思ったとおりの動作が確認でき驚いています。
私もちょっとずつマクロを組み始めたのですが、はじめの結合セルを解除するところまでは、組み立てとして考えていたのですが、結合セルの個数を数えて、DO LOOPなどをつかってやってみようと思ってました。またまだ到底組みきれていませんが。
早速使いたいと思います。コードも勉強してみます。ありがとうございました。
No.2
- 回答日時:
>今回張った右側の表は今年に値が入っていれば必ず前年には数値が入っていない状態になってしまっています。
しかし私が例としてあげたかったのは必ずしもそうではありません。全てに数字が入っています。実際のリストを例示されるなど、正確な情報を提示されないと的確な回答は得られないと思います(特にサンプルコードを提示してもらうときは重要)。
>また、上の行も項目が1段にしかなっていませんが、左の軸と同じように、結合セルが含まれる多層になった表になります。この(結合セルで表される)階層が、2段だったり、3段だったり、その時のダウンロードしたいデータの種類によって毎回変わってしまいます。
この部分も、リストの形式が同じレイアウトなら数式で対応できる可能性がありますが、レイアウトが種々変わるリストからのデータ取得をVBAで実行するには、大変なプログラミングが必要になります。
実戦的には、元データを吐き出す部分のプログラムを変更したほうが圧倒的に簡単だと思いますので、そちらをチャレンジされたほうがよいのではないでしょうか。
この回答への補足
大変申し訳ありません。ご助言ありがとうございます。
>実戦的には、元データを吐き出す部分のプログラムを変更したほうが圧倒的に簡単だと思いますので、そちらをチャレンジされたほうがよいのではないでしょうか。
なにせ私はサラリーマンで、既存のシステムを私の一言でプログラムを変えてくれるほど、優しい会社ではないのです。コストもかかりますし。
データをクロス集計するのにピボットテーブルの機能を知らない人がいるように、クロス集計の表を戻す便利な機能や方法、クロス集計された表でもピボットテーブルが使える機能がエクセルに備わっていないのかどうか。または、そういった便利なマクロが流通していないかどうかと思ったものですから。
文章で表現するのがどうも下手なので、大変申し訳ありませんでした。
出来るかどうかわかりませんが、自分でマクロを組む方向で考えて見ます。そこでまた行き詰ったら改めて質問させていただきます。
ありがとうございました。
No.1
- 回答日時:
右側の表のデータの項目名が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段だったり、その時のダウンロードしたいデータの種類によって毎回変わってしまいます。
うまく説明できなくて大変申し訳ありません。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(Microsoft Office) ピボットテーブルへの集計フィールド挿入 1 2023/02/26 11:33
- Excel(エクセル) EXCELピボットテーブル関数について 2 2023/04/10 20:35
- Excel(エクセル) 【Excelの集計について質問です。】 7 2022/12/03 16:51
- その他(データベース) IT用語について質問です。 以前ITパスポートの試験を受けた際にデータベースが何の集まりかについての 2 2022/12/10 12:29
- Excel(エクセル) ピボットテーブル集計表について。ピボットテーブルで集計を行うとき、内訳集計ではなく、通常集計表にする 2 2023/08/10 07:16
- JavaScript javaScriptで画面に値を入れるには 1 2022/08/14 12:27
- Access(アクセス) お世話になっています いまクエリを作っています。。 デザインビューの画面の集計の欄で、「グループ化」 4 2022/09/17 17:03
- Excel(エクセル) ピボットテーブルについて 列フィールドに複数横並びに項目を表示したい場合、階層毎に表示されてしまう現 2 2022/06/28 00:27
- Access(アクセス) Accessのクエリの結果を、既存のエクセルに追加したい 2 2022/07/31 22:44
- その他(教育・科学・学問) 教員の業績のために学生を動員できる範囲 1 2023/06/06 16:57
関連するカテゴリからQ&Aを探す
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
差込印刷での全角表示について...
-
エクセルにおける「フィールド...
-
フィールドの更新がない
-
エクセルデータをワードで差し...
-
ACCESSのデータに自動で半角ス...
-
アクセスのクエリでパラメータ...
-
「Access2007」でレポートが作...
-
Null値を並べ替えで・・・
-
Accessのハイパーリンクをクリ...
-
英数字のチェックをしたい ACC...
-
Accessの日本語フィールド名
-
アクセスVBAでの添付ファイルの...
-
アクセスのレポートがうまく印...
-
CSVファイル読み込みでズレがお...
-
datetime型のUpdate文
-
奇数・偶数ページごとに差し込...
-
ACCESSで条件によってフォーム...
-
sqlserverにはグループ集計のfi...
-
Access2000のクエリでクラスご...
-
アクセス2007 複数フィールド...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
差込印刷での全角表示について...
-
エクセルにおける「フィールド...
-
エクセルデータをワードで差し...
-
クエリで割り算する方法を教え...
-
フィールドの更新がない
-
ACCESS 重複データを1...
-
ACCESSのデータに自動で半角ス...
-
Word差し込み印刷のハイフン(...
-
実行時エラー '3464': 抽出条件...
-
Accessのハイパーリンクをクリ...
-
ACCESSで条件によってフォーム...
-
クエリーで、全角混じりデータ...
-
Acsess アクセス のクエリで...
-
ACCESSでフィールド名の変更(...
-
Wordの差込印刷時間の表示について
-
ACCESSで複数の写真ファイルを...
-
「Access2007」でレポートが作...
-
sqlserverにはグループ集計のfi...
-
ワードファイルの文字数制限ロ...
-
アクセスでのテキストデータ取...
おすすめ情報