クロス集計クエリ

こんにちは。伊川(@naonaoke)です。

集計は、単純集計と、クロス集計があります。

クロス集計はデータ分析の基本になります。

ACCESSはこのクロス集計クエリをウィザードで作成できるようになっています。

問題提起

(株)IKAWAの阿久 世子男(アク セスオ)は、上司から、Q_売上速報について、クロス集計を作成後、売上について報告書を提出するように言われました。集計の内容は、日別、社員別で売上を計算し、1枚で報告書をまとめるように言われました。ACCESSでそんなことができるのでしょうか?

 

解決策

回答
sesuo2.jpg
・SQL文で縦算を実行
・NZ関数を使う
・CLng関数を使う
・クエリの書式を変更する
スポンサーリンク

クロス集計クエリをウィザードで作成する


では、契約日時、社員氏名、代金のクロス集計を、ACCESSに搭載されているウィザードで作成してみます。

作成手順 その1


「作成」⇒「クエリウィザード」を選択


「クロス集計ウィザード」⇒「OK」を選択

作成手順 その2


「クエリ」⇒「クエリ: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”);


ここがポイント
Function8.png
これで、縦列の合計を計算できました。
ACCESSは、Excelと違って、結構シビアなのです。
よく、「演算子がありません」というようなエラーが表示されますが、
半角が、全角になっている等、イジーミスが多いです。
根気よくエラーを修正してください。

コードの解説



※ 同じデータで、社員氏名を ‘合計’ に変更して UNION ALL したのが縦の合計用

ユニオンクエリの応用です。

空白を、0(ゼロ)にする


上の図の赤枠ですが、ACCESSは、Excelのように合計が0(ゼロ)のときは空白を返します。

会議資料等で使う場合は、空白ではなく、0(ゼロにしなければなりません)

空白を0(ゼロ)にするためには、NZ関数を使う


NZ関数は、空白を0(ゼロ)に変換する関数です。


あら? 数式が崩れました。

NZ関数を使うと、数字が左によってしまうのです・

CLng関数で、再度、クロス集計クエリの形を整えます。



今度は、数字は右に寄りましたが、円マーク(¥)がありません。

円マーク(¥)で書式を変更する


「デザインビュー」を選択します。


「プロパティ」を選択します。


「プロパティシート」⇒「書式」⇒「通貨」


完成しました。

かなり、面倒な作業が続きました。

こんなに面倒なら・・・

じつは、クエリで縦算は、かなり面倒なのですが、レポート機能使うとカンタンに作成できます。

レポートの紹介は、またの機会にします。

まとめ

今回は、クロス集計クエリの、縦算も含めた紹介になりました。

こんな面倒はしたくないと思わないでください。

SQL文に慣れるには、これが一番良い方法だと思います。

Googleで、「クロス集計クエリ、縦算」などで検索すると、様々は方法が紹介されています。

しかし、ぜひ、このSQL文を使いこなせるようになりましょう。

サンプルファイルはNo27です

 

スポンサーリンク

Twitterでフォローしよう

おすすめの記事