目次
クロス集計クエリ
こんにちは。伊川(@naonaoke)です。
集計は、単純集計と、クロス集計があります。
クロス集計はデータ分析の基本になります。
ACCESSはこのクロス集計クエリをウィザードで作成できるようになっています。
問題提起
（株）IKAWAの阿久 世子男（アク セスオ）は、上司から、Q_売上速報について、クロス集計を作成後、売上について報告書を提出するように言われました。集計の内容は、日別、社員別で売上を計算し、1枚で報告書をまとめるように言われました。ACCESSでそんなことができるのでしょうか？
解決策
・SQL文で縦算を実行
・NZ関数を使う
・CLng関数を使う
・クエリの書式を変更する
クロス集計クエリをウィザードで作成する
では、契約日時、社員氏名、代金のクロス集計を、ACCESSに搭載されているウィザードで作成してみます。
作成手順 その1
「作成」⇒「クエリウィザード」を選択
「クロス集計ウィザード」⇒「OK」を選択
作成手順 その２
「クエリ」⇒「クエリ:Q_売上速報」⇒「次へ」を選択
※今回は、ベースとなるのが、クエリなので、クエリを選択していますが、テーブルからも作成できます。
作成手順 その3
「社員氏名」⇒「＞」⇒「次へ」を選択します。
作成手順 その4
「契約日時」⇒「次へ」
作成手順 その5
「日」⇒「次へ」を選択
作成手順 その6
「代金」⇒「合計」⇒「次へ」を選択
作成手順 その7
「※名前を決める：今回はそのままにします」⇒「完了」
完成しましたが、突っ込みどころ満載のクロス集計表です。
このような完成図を見ると、Excelのほうが便利と思う気持ちになり、Excelへ浮気をしてしまうのです。
完成からさらに補正
現時点では、横の合計しかしていません。
縦列の合計を表示するには、SQL文から補正します。
いろいろなやり方がありますが、私は、SQL文で補正します。
上の図のクエリのSQL文は書きの文章で構成されています。
TRANSFORM Sum(Q_売上速報.[代金]) AS 代金の合計
SELECT Q_売上速報.[社員氏名], Sum(Q_売上速報.[代金]) AS [合計 代金]
FROM Q_売上速報
GROUP BY Q_売上速報.[社員氏名]
PIVOT Format([契約日時],”Short Date”);
上のSQL文を変更します。
TRANSFORM Sum(Q_売上速報.代金) AS 代金の合計
SELECT Q_売上速報.社員氏名, Sum(Q_売上速報.代金) AS [合計 代金] FROM (
SELECT 0 AS T, Q_売上速報.社員氏名, [契約日時], [代金] FROM Q_売上速報
UNION ALL
SELECT 1, ‘合計’, [契約日時], 代金 FROM Q_売上速報) AS Q1
GROUP BY T,Q_売上速報.社員氏名
PIVOT Format([契約日時],”Short Date”);
これで、縦列の合計を計算できました。
ACCESSは、Excelと違って、結構シビアなのです。
よく、「演算子がありません」というようなエラーが表示されますが、
コードの解説
※ 同じデータで、社員氏名を ‘合計’ に変更して UNION ALL したのが縦の合計用
ユニオンクエリの応用です。
空白を、0（ゼロ）にする
上の図の赤枠ですが、ACCESSは、Excelのように合計が0（ゼロ）のときは空白を返します。
会議資料等で使う場合は、空白ではなく、0（ゼロにしなければなりません）
空白を０（ゼロ）にするためには、NZ関数を使う
NZ関数は、空白を０（ゼロ）に変換する関数です。
あら？ 数式が崩れました。
NZ関数を使うと、数字が左によってしまうのです・
CLng関数で、再度、クロス集計クエリの形を整えます。
今度は、数字は右に寄りましたが、円マーク（￥）がありません。
円マーク（￥）で書式を変更する
「デザインビュー」を選択します。
「プロパティ」を選択します。
「プロパティシート」⇒「書式」⇒「通貨」
完成しました。
かなり、面倒な作業が続きました。
こんなに面倒なら・・・
じつは、クエリで縦算は、かなり面倒なのですが、レポート機能使うとカンタンに作成できます。
レポートの紹介は、またの機会にします。
まとめ
今回は、クロス集計クエリの、縦算も含めた紹介になりました。
こんな面倒はしたくないと思わないでください。
SQL文に慣れるには、これが一番良い方法だと思います。
Googleで、「クロス集計クエリ、縦算」などで検索すると、様々は方法が紹介されています。
しかし、ぜひ、このSQL文を使いこなせるようになりましょう。
サンプルファイルはNo27です
