Accessで管理しているデータをExcelのピボットテーブルのように、行×列の2次元データとしてまとめて表示したい場合もあると思います。たとえば「月ごとの売上を商品別に一覧で見たい」といったケースです。
Accessでは、クロス集計クエリを活用することで実現できます。
この記事ではAccessでクロス集計クエリを作成する2種類の方法について、仕組みから実際の作成手順までを、順を追って解説していきます。
クロス集計クエリとは
クロス集計クエリとは、データを「行」「列」「値」で構成して集計表示するクエリです。
通常の「選択クエリ」による集計は“縦方向”に結果を出すのに対し、
クロス集計クエリでは“横方向”にも項目を展開して、見やすい一覧表を作ることができます。
たとえば、次のような「売上テーブル」があるとします。
これをクロス集計クエリで「月別 × 支店別」にまとめると、次のような表になります。
見た目がExcelのピボット表に似ており、「どの月にどの支店でどれくらい売れたか」がひと目で分かります。
クロス集計クエリの作成方法
クロス集計クエリには、次の2種類の作成方法があります。
①ウィザードで作る方法（初心者向け）
②デザインビューで作る方法（応用向け）
今回は、こちらのサンプルテーブルを題材に支店別の月別売上合計を求める集計を、それぞれの方法で詳しく解説していきます。
方法①：ウィザードで作成（初心者向け）
クロス集計クエリウィザードは、画面の指示に従い作業を進めることで作成することができる、対話式の便利な機能です。特に初心者におすすめです。
メニューから ［作成］→［クエリウィザード］→「クロス集計クエリ ウィザード」
メニューから「作成」→「クエリウィザード」から「クロス集計クエリ ウィザード」の順番に選択しています。
対象テーブル（クエリ）を選択する
クロス集計を行う対象のテーブル（またはクエリ）を選択する画面が表示されますので、テーブルを選択し、「次へ」をクリックします。今回はT_支店別売上管理テーブルを選択します。
行見出しにするフィールドを選択する
行見出し（縦軸）として使用するフィールドを選択します。
今回の例では 売上日フィールド を選択します。選択すると、画面下部のサンプル表示に縦軸として反映されますので、「次へ」をクリックします。
基本的に縦軸には、今後もデータが追加されるフィールドを指定します。支店は基本的に固定されていることに対して、売上日は今後も追加されていくため、行は売上日となります。
なお、行見出しは最大3つまで設定できます。今回の説明では1つだけですが、複数設定することで、より細かく複雑なクロス集計も行えるようになります。
ただし、行見出しが増えるほど処理が重くなる場合がありますので、用途に応じて設定数は調整するのがおすすめです。
列見出しにするフィールドを選択
列見出し（横軸）として使用するフィールドを選択します。
今回の例では支店フィールド を選択します。選択すると、画面下部のサンプル表示に横軸として反映されますので、「次へ」をクリックします。
集計する値のフィールドを選択
集計元の値となるフィールドと、集計方法を選択します。
今回のサンプルでは売上金額のみが残っていますので、集計する値はそのまま選択されています。
そして、集計方法は合計を選択します。
表示形式の調整
データシートビューに切り替えて結果を表示してみます。
今回は、月別・支店別の売上合計を確認したいのですが、縦軸は売上日で日付単位の集計となっています。これを月単位に変更します。
作成したクロス集計クエリをデザインビューで開きます。売上日付フィールドに対してMonth関数を用いて、日付から月部分のみ抜き出した売上月フィールドにします。
実行（結果確認）
再度データシートビュー切り替えてみます。これで希望どおりのクロス集計クエリが完成しました。
方法②：デザインビューで設定する（慣れている人向け）
メニューから ［作成］→［クエリデザイン］
メニューから「作成」→「クエリデザイン」で空のクエリデザインビューを開きます。
対象テーブル（クエリ）を追加後、集計対象フィールドを追加する
クロス集計を行う対象のテーブル（またはクエリ）をフィールドリストにドロップ＆ドラックで追加します。
その後、追加した集計対象のフィールドを、デザイングリッドに追加します。
クエリの種類を「クロス集計」に切り替える
メニュー「クエリデザイン」から、クエリの種類を「クロス集計」に切り替えます。
切り替えることにより、クエリグリットに「集計欄」と「行列の入れ替え欄」が追加されます。
行見出し・列見出し・値フィールドを指定
各欄を以下のとおり設定します。
- 行見出し：縦方向に並べたい項目（
売上月:Month([売上日])&"月"） - 列見出し：横方向に展開したい項目（
支店） - 値：集計したい数値（
売上金額）
実行（結果確認）
ウィザードと同じ結果で、クロス集計クエリを作成することができました。
慣れるまではウィザードを使いながら、最終的にはデザインビューで一から作成できるようになれば、色々応用が効くのでおすすめです。
応用テクニック
列と行を入れ替える
デザインビューで、クロス集計クエリの設定方法が分かっていれば、行と列を入れ替えることも簡単にできます。
このようにちょっとした変更で、行（支店）と列（売上月）に変更することができます。
クロス集計で空欄を「0」に置き換える方法
前項で作成したクロス集計クエリを見ると、値が存在しない部分は空欄として表示されます。
例として、12月の大阪支店や11月の名古屋支店が該当します。
本来であれば、空欄ではなく 「0」 を表示したいところですが、クロス集計クエリでは値が存在しないセルは Nullではなく「行自体が存在しない扱い」 となっています。
そのため、下図のように Nz関数で置き換えようとしても変換されません。
空欄を0に置き換えるには、クロス集計をそのまま編集するのではなく、2段階でクエリを作成する方法で可能となります。手順は次の通りです。
最初に作成したクロス集計クエリを元に、新しい選択クエリを作成します。
選択クエリのデザイングリッドに、クロス集計クエリのフィールドを追加し、Nz関数で囲みます。
選択クエリにフィールドを追加した時点で、空欄はNullと認識されますので、Nz関数により0への変換が行わます。
最終結果では、このように空欄だった部分が 「0」 として表示されます。
まとめ
クロス集計クエリは、データを俯瞰して分析することができる便利な機能です。一見むずかしそうに見えますが、手順さえ覚えればとても簡単です。
- 行見出し・列見出し・値の3つを意識する
- NULLや集計の設定に気をつける
- あまり複雑化させない
この3点を押さえれば、スムーズにクロス集計クエリを作成できるようになると思います。
・Accessで業務用ツールを作ってみたいけど、なかなか時間がとれない。
・先輩が作った既存のツールを改修したいけど難しい…。
そのような場合には、私がみなさまの代わってカタチにいたします。ぜひ作成サービス
をご利用ください。
