Accessでクロス集計表を作成（ウィザードとデザインビューで解説）

Accessでも、Excelのピボットテーブルのようなクロス集計表を作成できます。

クロス集計クエリを使えば、行見出しと列見出しを使った見やすい集計表が作れます。

クロス集計クエリの作成は、ウィザードを使って作成する方法と、デザインビューで手動で作る2つの方法があります。

行見出しと列見出しは、作成後に簡単に入れ替えることができます。

クロス集計クエリは、印刷やExcelへエクスポートして見やすい表が欲しいというときにも有効です。

参考Access2010までは、ピボットテーブル機能が標準で用意されていました。しかし、Access2013以降では廃止されて、現在は［クロス集計クエリ］が最も近い代替手段になります。

テーブルの確認

3つのテーブルがあり、リレーションシップの設定をしています。

ここでは、この3つのテーブルを基にクロス集計クエリを作成します。

参考リレーションシップについては、以下の記事で解説しています。

クロス集計クエリウィザードを使って作成

クロス集計ウィザードは、1つのテーブルまたはクエリしか元データにできません。

複数のテーブルのフィールドを使うクロス集計クエリを作成する場合は、まず必要なフィールドをすべて含むクエリを作成します。

クロス集計ウィザードでは「表示項目に名前（製品名など）」を使いたい場合、事前にクエリで結合しておく必要があります。

クエリの作成

ここでは、行見出しに［製品コード］で列見出しには［販売店種別］を表示したいと思いますので、以下のようなクエリを作成します。

テーブル［T_製品コード］から［製品コード］、テーブル［T_販売店種別］から［販売店種別］、テーブル［T_売上］から［売上額］をデザイングリッドに挿入しています。

クエリを実行すると、以下のようになります。

クエリは、［Q_売上_製品コードと販売店種別］という名前で保存しています。

参考既存のクエリで必要なフィールドが含まれているものがあれば、それを使用してかまいません。

クエリウィザード（クロス集計クエリ）の作成

［作成］タブの［クエリ］グループにある［クエリウィザード］をクリックします。

［新しいクエリ］ダイアログボックスから［クロス集計クエリウィザード］を選択して［OK］ボタンをクリックします。

クロス集計クエリウィザードが起動します。

クロス集計を行うフィールドがあるテーブルまたはクエリを選択します。

ここでは、［クエリ］を選択して、作成した［Q_売上_製品コードと販売店種別］を選択して、［次へ］をクリックします。

行見出しとして使用するフィールドを選択して、中央にある［＞］をクリックします。

以下のように［選択したフィールド］に［製品コード］が表示されているのを確認して、［次へ］をクリックします。

列見出しとして使用するフィールドを選択します。ここでは、［販売店種別］を選択して、［次へ］をクリックします。

集計する値があるフィールドと、集計方法を選択します。

ここでは、［売上額］と［合計］を選択します。

［行ごとに集計地を表示しますか？］の［集計値を表示する］のチェックボックスはオフにして、［次へ］をクリックします。

行ごとの集計値が必要であれば、オンにしてください。

クエリ名を付けます。

既定では、クエリ名やテーブル名の後ろに［のクロス集計］と付けられます。

ここでは、［Q_製品コードと販売店種別のクロス集計］として［完了］をクリックします。

以下のようなクロス集計クエリが完成します。

参考［行ごとに集計地を表示しますか？］の［集計値を表示する］のチェックボックスをオンにした場合の結果は、以下を参照してください。

クロス集計クエリに合計列を追加する

クエリウィザードには、クロス集計クエリウィザードの他、選択クエリウィザード、重複クエリウィザード、不一致クエリウィザードがあります。以下の記事で解説しています。

通貨型の表示にするには

数値の表示形式を通貨型に変更するには、デザインビューで［売上額の合計: 売上額］内にカーソルを置いてプロパティを表示します。

キーボードから［F4］キーを押すと、プロパティを表示できます。

［標準］タブの［書式］で［通貨］を選択します。

結果、以下のようなクロス集計クエリになります。

行見出しと列見出しの入れ替え

作成したクエリ［Q_売上_製品コードと販売店種別のクロス集計］をデザインビューで開くと、以下のようになっています。

デザイングリッドの［行列の入れ替え］で列見出しと行見出しを入れ替えることができます。

結果、以下のように行列が入れ替わったクロス集計クエリなります。

デザインビューからクロス集計クエリを作成

クロス集計クエリウィザードを使用しなくても、クエリ作成のデザインビューから作成することもできます。

ここでは、行見出しに［月］、列見出しに［製品コード］のクロス集計クエリを作成します。

クエリのデザイングリッドに必要なフィールドを追加します。

テーブル［T_売上］から［売上額］、テーブル［T_製品コード］から［製品コード］を挿入します。

売上年月日は、月ごとにまとめたいので、フィールドにはFormat関数を使って［yyyy/mm］の形式に入力します。

参考Format関数については、Microsoftの記事を参照してください。

Format 関数 - Microsoft サポート（Microsoft）

集計クエリの作成

［クエリデザイン］タブの［表示/非表示］グループにある［集計］をクリックします。

デザイングリッドに［集計］行が追加されます。

［売上額］で［合計］を選択し、［並べ替え］は［月］の［昇順］にします。

クエリを実行して、確認します。

クエリは、［Q_売上_月と製品コード］という名前にして保存します。

参考［集計］行で［合計］を指定するのを忘れないようにしてください。データを更新しても変化がない場合は、ここで［最小］や［最大］などを選択している可能性があります。

クロス集計表に変換

［集計クエリ］を［クロス集計クエリ］に変換します。

［クエリデザイン］タブの［クエリの種類］グループから［クロス集計］をクリックします。

デザイングリッドに［行列の入れ替え］行が追加されます。以下のように設定します。

• ［月］を［行見出し］
• ［製品コード］を［列見出し］
• ［売上の合計］を［値］

クエリを実行すると、以下のようになります。

通貨表示にするには、デザインビューで［売上額の合計］内にカーソルを置いて、プロパティを表示します。

［標準］タブの［書式］で［通貨］を選択します。

結果、以下のようなクロス集計クエリなります。

列見出しの項目の順番を変更するには

列見出しの項目の順番は変更することができます。

デザインビューでクエリプロパティを表示します。

キーボードから［F4］キーを押してプロパティを表示し、フィールドリストを表示している領域の何もない箇所でクリックすると［クエリプロパティ］が表示されます。

［クエリ列見出し］に表示したい順番にカンマ（,）で区切って入力します。テキストは、ダブルクォーテーションで囲みます。

結果、以下のように並びが変わります。

クロス集計クエリに合計列を追加する

ウィザードでクロス集計クエリを作成する場合、集計方法を選択する画面で［集計値を表示する］のチェックボックスをオンにして作成すると合計列が追加されます。

既定では、オンになっています。

以下のようなクロス集計表が完成しますが、［合計 売上額］列が［製品コード］の右に表示されます。

デザインビューで確認すると、右端の位置になっているのですが、クエリをデータシートビューで表示すると位置が異なります。

データシートビューのまま、［合計 売上額］の列を選択してフィールド名を右端へドラッグします。

デザインビューで［売上額の合計: 売上額］と［合計 売上額: 売上］を通貨表示にすると、以下のようになります。

手動クロス集計クエリで合計列を追加する

手動でクロス集計クエリを作成した場合でも、行ごとの合計値を表示させることができます。

［行列の入れ替え］で［値］を設定したフィールドと同じものを追加し、［集計］で［合計］、［行列の入れ替え］で［行見出し］を選択します。

フィールド名は同じにならないように気をつけてください。ここでは、［合計: 売上額］としています。

プロパティで書式を通貨表示にして、クエリを実行すると以下のようになります。

この場合も［合計］フィールドの位置が行見出しの右になりますので、必要であればドラッグで調整してください。

以下のようになります。

列見出しに［<>］と表示される場合の対処

クロス集計クエリの列見出しに［<>］が表示されることがあります。

これは、列見出しとして指定したフィールドに未入力のレコードがある場合に現れます。

上記クロス集計クエリは、列見出しに［製品コード］を指定していますが、クエリ［Q_売上_製品コードと販売店種別］でNullになっているレコードがあるためです。

こういう状態のまま、クロス集計クエリを作成すると、列見出しに［<>］が表示されます。

クロス集計クエリでこの［<>］列を非表示にするには、デザインビューで［製品コード］フィールドの［抽出条件］に［Is Not Null］を入力して条件を指定します。

以下のように［<>］と表示されていた列は非表示になります。

クロス集計表をExcelへエクスポートする

クロス集計クエリを使って作成したクロス集計表をExcelへエクスポートすることができます。

出力したいクエリで右クリックして、ショートカットメニューから［エクスポート］の［Excel］をクリックします。

［外部データ］タブの［エクスポート］グループにある［Excel］をクリックしても同じです。

［エクスポート-Excelスプレッドシート］ダイアログボックスが表示されます。

［ファイル名］を確認し、必要であれば修正します。保存先は、［参照］ボタンをクリックして変更できます。

エクスポートのオプションで、［書式設定とレイアウトを保存したままデータをエクスポートする］をオンにするか、オフにするかを決めます。

ここでは、オンにして、［エクスポートの完了後にエクスポート先のファイルを開く］もオンにします。

［OK］ボタンをクリックします。

以下のように出力されます。

参考［書式設定とレイアウトを保存したままデータをエクスポートする］をオフにして出力した場合は、以下のようになります。

オフにすると、［エクスポートの完了後にエクスポート先のファイルを開く］はオンにできません。

エクスポート操作を保存

エクスポートすると、出力と同時に［エクスポート操作の保存］のウィンドウが表示されます。

［エクスポート操作の保存］のチェックボックスをオンにすると、以下のように展開されて名前を付けて保存できます。

［外部データ］の［エクスポート］グループにある［保存済みのエクスポート操作］をクリックすると、

［データタスクの管理］ダイアログボックスの［保存済みのエクスポート操作］タブが表示され、選択していつでも実行できるようになります。

参考エクスポート操作を保存しておくと、マクロにすることもできます。

Excelへエクスポートするコマンドボタンを作成する方法は、以下の記事で解説しています。