EXCEL集計レポート_グループ単位での行詰め方法

Question

前提・実現したいこと

AccessとExcelを使用してデータの集計を行っているのですが縦横変換の後、各種別ごとに行詰めを行って表示させたいです。
※月が切り替わった場合は切り替わり地点が最上位となる。
現実…の通り、外部結合を行ったはいいが月別毎に上に詰められず困っています。

データ
月 内容　 時間
1月　A 12:00
2月　A 13:00
2月　A 14:00
2月　B 12:00
2月　C 11:00
3月　A 14:00
3月　C 12:00

目標！
月|A | B | C |
１月|12:00 | | |
２月|13:00 | 12:00 | 11:00 |
２月|14:00 | | |
３月|14:00 | | 12:00 |

現実…
月　|A | B | C |
１月|12:00 | | |
２月|13:00 | | |
２月|14:00 | | |
２月| | 12:00 | |
２月| | | 11:00 |
３月|14:00 | | |
３月| | | 12:00 |

試したこと

Accessで月のマスタを作り内容(A,B,C)を結合を行ったものの記入の不要なところについて空で入力されなかったり
外部結合の場合ですとレコードが重複されない為、ここからの加工方法に悩んでいます

Answer 1

もし、Officeのバージョンが ProPlus であるのであれば、スピル関数のXLOOKUPで、実現が可能です。

イメージは、下図のようになります。

ACCESSからのデータをデータの取得で、ワークシート上にリンクさせます。
図の場合は、ACCESSのリンク範囲（テーブル）名を _Data1 としています。

F1の数式は、下のようにします。

=TRANSPOSE(SORT(UNIQUE(_Data1[内容])))

E2の数式は、下のようにします。

=SORT(UNIQUE(_Data1[年月]))

セル範囲 F2:N13 には、下のような数式を入力します。

=XLOOKUP($E2&F$1,_Data1[年月]&_Data1[内容],_Data1[時間],"")

この手は、昔からアルアルの要望で、通常は、複雑な数式や、マクロを駆使しないと実現できない事例でしたが、2019年末からMicrosoftから正式リリースされたスピル関数を用いることで、いとも簡単に出力することが可能です。
もし、バージョンが古いなら、残なら、複雑な数式やvbaを書くしか手段はありません。

Comments

[HajimeNaya]

スピル関数…凄いですね！初めて知りました。今回の件について皆様のおかげで解決しましたが次回の前にスピル関数について少し掘り下げてみてみます！ありがとうございます

Answer 2

とりあえず下記のようなグループ内連番が必要です。

データ
月 内容　 時間　 連番
1月　A 12:00 1
2月　A 13:00 1
2月　A 14:00 2
2月　B 12:00 1
2月　C 11:00 1
3月　A 14:00 1
3月　C 12:00 1

グループ内連番はクエリでDCount関数やサブクエリを使って計算する方法と、VBAでフィールドに出力する方法があります。下記でその両方を紹介しています。（特に下記で紹介している汎用関数で出力する方法がお勧めです。）

グループ毎連番を自動入力する関数 - hatena chips

汎用関数を使って連番を入力するコード例

Call SetSequenceNumber("連番", "T月クロス", "月,内容", "時間")

上記のようにグループ毎連番が取得出来たら、あとは、クロス集計クエリで簡単にご希望のレイアウトが実現できます。
下記のような設定になります。

テーブル名は「T月クロス」としてます。

TRANSFORM First(T月クロス.時間) AS 時間の先頭
SELECT T月クロス.月, T月クロス.連番
FROM T月クロス
GROUP BY T月クロス.月, T月クロス.連番
PIVOT T月クロス.内容;

Comments

[HajimeNaya]

返信が遅くなってすみません。Accessのグループ内連番について説明をみたのですがちょっと上手くいかなかなったのでExcelのCountifs関数で一度整形してからAccessでクロス集計を行ってみたところ上手くいけました！丁寧なご説明本当にありがとうございました

Answer 3

行を上に詰める為には、内容ごとに月の何番目になるか（連番）を求めてそれで結合する必要があります。

クロス集計であれば揃えてくれるのでその分は楽です。

連番については以下参考
クエリで連番、グループ連番をふる

Comments

[HajimeNaya]

グループとグループでの連番と紐付けを行っていけば良いということですね！
ぼやっと理解できましたので挑戦してみます。
クロス集計についてはピポットを試したのですが値が集計になってしまいわからずじまいでしたので紐付けの方でやってみますありがとうございます！