Accessでクロス集計する方法をお探しですね。
広告
Accessで縦横のマトリクス表を作ろう！クロス集計クエリの使い方
Accessで売上データや件数を集計していると、「日付を縦に並べて、顧客名や商品の種類を横に並べて見比べたいな」と思うことがありますよね。
普通の選択クエリでは一覧表は作れるんですが、縦横のマトリクス表にして比較するには、ちょっとした工夫が必要です。
そんなときに便利なのが「クロス集計クエリ」という機能なんです。
この記事では、Accessのクロス集計クエリの基本から作り方、実際に使うときに気をつけたいポイントまで、わかりやすく解説していきます。
クロス集計クエリって何？縦横の表でデータを整理する基本
Accessのクロス集計クエリは、テーブルやクエリに保存されている細かいデータを、縦の見出し・横の見出し・集計した数値の3つの要素で整理して、マトリクス表として見せてくれる機能です。
たとえば、売上明細のテーブルに「売上日」「顧客名」「商品分類」「金額」が入っているとしましょう。
このとき、売上日を縦方向に、顧客名を横方向に並べて、それぞれの交わるところに金額の合計を表示できるんです。
Excelのピボットテーブルに似ていると思うかもしれませんが、Accessで作ったクエリは、フォームやレポート、別のクエリの元データとして何度も使い回しやすいのが特徴です。
普通の集計クエリだと、「顧客別の売上合計」や「月別の件数」のように、結果が縦一列に並びます。
でもクロス集計クエリなら、横方向にも項目を広げられるので、数字を比較しやすくなるんです。
たとえば「月別×担当者別の受注件数」「店舗別×商品カテゴリ別の売上」「部署別×ステータス別の問い合わせ件数」なんかを、パッと一目で確認できます。
数字の増え方や偏りを見つけたいときは、縦に長い一覧よりも、マトリクス形式のほうが断然わかりやすいんですよね。
ただし、Excelのピボットテーブルみたいに、画面上で自由に項目をドラッグして入れ替える…といった使い方には向いていません。
集計の切り口を変えたいときは、クエリのデザインビューやSQLビューで設定を変えてから、もう一度実行する必要があります。
なので、毎回違う角度で試行錯誤しながら分析したいときはExcelのほうが便利かもしれません。
でも、決まった形式の集計表を定期的に作る業務なら、Accessのほうが安定して使えます。
特に、入力データがAccessに溜まっていて、毎月同じ形式のレポートを作るようなケースでは、クロス集計クエリを覚えておくと本当に役立ちますよ。
クロス集計クエリの作り方！ウィザードで簡単作成
Accessでクロス集計クエリを作る方法はいくつかありますが、初めての人におすすめなのは「クロス集計クエリウィザード」を使う方法です。
ウィザードを使えば、縦に並べたい項目、横に並べたい項目、集計したい数値を順番に選んでいくだけで、基本的なクロス集計表ができあがります。
ここでは例として、売上テーブルに「売上日」「顧客名」「商品名」「金額」が入っていると仮定して、「売上日ごと・顧客名ごとの売上金額合計」を表示する流れで説明しますね。
作る前に確認しておきたいのが、元データが明細形式になっているかどうかです。
クロス集計クエリは、すでに横に広がっている表をさらに集計するよりも、「1行＝1取引」「1行＝1明細」みたいな縦持ちデータを集計するのに向いています。
たとえば、1件の売上につき売上日、顧客名、金額が1行に入っている形なら扱いやすいです。
逆に、1月、2月、3月…のように月が列として分かれている表だと、Accessで集計する前にテーブルの設計を見直したほうがいい場合もあります。
基本的な操作手順はこんな感じです。
– Accessの「作成」タブから「クエリ ウィザード」を開いて、「クロス集計クエリ ウィザード」を選びます
– 集計の元にするテーブルまたはクエリを選んで、縦に並べたい項目（行見出し）に「売上日」などを指定します
– 横に展開したい項目（列見出し）に「顧客名」や「商品分類」などを選びます
– 集計する値に「金額」を指定して、集計方法として「合計」「件数」「平均」など、目的に合った関数を選びます
– 必要に応じて行ごとの合計を表示するかどうか決めて、クエリに名前を付けて完了です
完成したクエリを実行すると、左側に売上日がズラッと並んで、列方向に顧客名が広がって、それぞれの交わるところに売上金額の合計が表示されます。
横の見出しは元データにある値から自動的に作られるので、顧客名が増えたら列も増えることになります。
この動きは便利なんですが、レポートで列の幅や順番を固定したい場合は注意が必要です。
まずはウィザードで作ってみて全体像を確認して、そのあとデザインビューで細かく調整していく流れが、実際の仕事では使いやすいですよ。
デザインビューで理解する！縦・横・集計値の設定
クロス集計クエリを安定して使えるようになるには、ウィザードで作ったあとにデザインビューを開いて、どの項目がどんな役割を持っているのか理解することが大事です。
クロス集計クエリのデザインビューでは、普通のクエリと同じように項目を並べるんですが、下のほうに「集計」と「クロス集計」っていう行が表示されます。
ここで、縦に並べたい項目には「行見出し」、横に展開したい項目には「列見出し」、数値を集計する項目には「値」を指定するんです。
集計の行には、グループ化や合計、件数、平均といった集計方法を設定します。
たとえば「売上日」を行見出し、「顧客名」を列見出し、「金額」を値にする場合、売上日はグループ化、顧客名もグループ化、金額は合計にします。
こうすると、同じ売上日と顧客名の組み合わせが何行もあっても、金額がまとめて合計されるんです。
もし「金額の合計」じゃなくて「受注件数」を見たいときは、値にする項目を伝票番号なんかにして、集計方法を「カウント」にする方法があります。
何を分析したいのか—金額なのか、件数なのか、平均単価なのか—によって設定を変えることが大切です。
あと、行見出しは1つだけじゃなくて、複数指定することもできます。
たとえば「年月」と「商品分類」を行見出しにして、「担当者」を列見出しにして「金額」を合計すると、年月別・商品分類別に担当者ごとの売上を比較できるようになります。
ただし、行見出しを増やしすぎると表が細かくなりすぎて、全体像が見えにくくなることもあります。
クロス集計表は、見たい切り口を絞るほど読みやすくなるので、最初に「縦に何を並べるか」「横に何を比較するか」「マスに何の数値を入れるか」をはっきりさせてから作ると失敗しにくいです。
抽出条件を組み合わせると、もっと実務向きの集計表になります。
たとえば売上日を今年度のデータだけに絞ったり、特定の商品カテゴリだけを対象にしたりできます。
日付を月単位で集計したいときは、Format関数で「yyyy/mm」みたいな年月を作ったり、DateSerial関数なんかで月初日を作ってグループ化する方法があります。
日付をそのまま行見出しにすると日別の細かい表になっちゃいますが、年月に変換すれば月別の推移として見やすくなります。
分析の目的に合わせて、元の項目をそのまま使うのか、計算で新しい項目を作るのかを選ぶのがポイントですね。
実務で使うときの注意点と見やすい表にするコツ
Accessのクロス集計クエリでよくあるつまずきの1つが、データが存在しない組み合わせのマスが空白になってしまうことです。
たとえば、ある顧客に売上がなかった日とか、ある商品カテゴリが販売されなかった月は、そのマスに値が表示されません。
分析するうえでは空白でも問題ない場合もあるんですが、レポートやExcelに出力するときに「0」と表示したいなら、Nz関数を使ってNullを0に置き換える方法があります。
Nullっていうのは「値が存在しない」状態を表すもので、0とは意味が違うんです。
合計や計算に使う場合は、空白のままでいいのか、0として扱うのかを事前に決めておく必要があります。
もう1つ重要なのが、横の見出しの並び順や列の固定です。
クロス集計クエリでは、横の見出しがデータの値から自動的に作られるので、月や区分の列が思った通りの順番で表示されないことがあります。
たとえば「1月、10月、11月、2月」みたいに文字列順で並んじゃうと、時系列の分析では見づらくなっちゃいますよね。
こんなときは、横見出しの値を「01月」「02月」のように整えたり、クエリのプロパティで列見出しを固定する方法を検討します。
フォームやレポートの元データとして使う場合は、列が増えたり減ったりするとレイアウトが崩れる可能性があるので、あらかじめ表示したい列を固定しておくと運用が安定しますよ。
クロス集計クエリは便利なんですが、万能ではありません。
複数の値を同時に横方向へ集計したい場合、たとえば「売上金額」と「件数」を同じクロス集計表に並べたいときは、クエリを分けて作ったり、別の集計クエリと組み合わせたりする設計が必要になることもあります。
あと、元データの件数が多い場合や、横見出しの種類が多すぎる場合は、表示に時間がかかったり、横にすごく長い表になったりします。
クロス集計表は「比較するための表」なので、列数が多くなりすぎると、かえって分析しにくくなる点には気をつけましょう。
実務で使いやすいクロス集計クエリにするには、最初から最終的にどう使うかをイメージしておくことが大切です。
画面で確認するだけなのか、レポートとして印刷するのか、Excelに出力して追加で分析するのかによって、適した設計は変わってきます。
毎月同じ形式で売上を確認するならAccess内でクエリとレポートを整備しておくと効率的です。
逆に、集計の切り口を頻繁に変えて試行錯誤したいなら、Accessから明細データをExcelに出力して、ピボットテーブルやPower Queryで分析する方法も選択肢になります。
Accessのクロス集計クエリの強みは、決まった条件のマトリクス表を何度でも同じように作れることにあります。
目的に合わせて使い分けることで、縦横のマトリクス表を単なる一覧じゃなくて、仕事の判断に役立つ分析資料として活用できるようになりますよ。
広告
