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

前提・実現したいこと

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)を結合を行ったものの記入の不要なところについて空で入力されなかったり
外部結合の場合ですとレコードが重複されない為、ここからの加工方法に悩んでいます

行動規範の内容に同意します

回答3件

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

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

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

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

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

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

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

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

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

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

投稿2020/06/02 11:43

kai_keitai

総合スコア344

HajimeNaya

2020/06/02 14:49

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

行動規範の内容に同意します

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

データ
月内容　時間　連番
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

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

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

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

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

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

投稿2020/05/28 11:41

編集2020/05/28 12:01

hatena19

総合スコア33790

HajimeNaya

2020/06/02 14:46

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

行動規範の内容に同意します

ベストアンサー

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

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

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

投稿2020/05/28 09:41

sazi

総合スコア25206

HajimeNaya

2020/05/28 11:24

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

行動規範の内容に同意します

あなたの回答

tips

プレビュー

行動規範の内容に同意します

質問の解決につながる回答をしましょう。サンプルコードなど、より具体的な説明があると質問者の理解の助けになります。また、読む側のことを考えた、分かりやすい文章を心がけましょう。

15分調べてもわからないことは
teratailで質問しよう！

ただいまの回答率
85.46%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう！