Accessで集計する方法をお探しですね。

広告

Accessを使っていると、テーブルに入力された大量のデータをどう集計して分析に役立てればいいのか、悩むことってありますよね。

Excelなら、SUM関数やピボットテーブルで簡単にできる作業も、Accessのクエリになると「グループ化」とか「演算フィールド」といった聞き慣れない言葉が出てきて、「うっ…」となってしまう方も多いんじゃないでしょうか。

でも安心してください。

Accessの集計機能は、最初はちょっとクセがあるものの、一度コツをつかんでしまえばとっても強力なんです。

数万件もあるようなデータでも、あっという間に「商品ごとの売上合計」や「月別の平均客単価」なんかを計算してくれます。

この記事では、Accessのクエリデザイン機能を使った基本的な合計・平均・カウントの出し方から、実務でよく困る「空白(Null)」データの扱い方、さらに別のテーブルから必要なデータをサッと取ってこれるDLookup関数の使い方まで、まとめて解説していきます。

これらのテクニックをマスターすれば、毎日のデータ集計作業がグッと楽になって、もっと高度なデータベース活用への道が開けますよ。

Accessクエリの集計機能で合計・平均・カウントを出す方法

Accessでデータを集計するとき、一番基本になるのがクエリデザイン画面の「集計」機能です。

SQLを知っている人なら「GROUP BY句」と言えばピンとくるかもしれませんが、Accessならマウス操作だけで簡単に設定できるのが嬉しいポイントです。

たとえば、「毎日の売上明細テーブルから、商品ごとの売上合計を知りたい」というケースを考えてみましょう。

このとき必要なのは、ただ足し算するだけじゃなくて、「商品IDでデータをグループ分け」してから、それぞれのグループで「金額を合計する」という二段階の処理なんです。

実際の操作は、こんな感じです。

まず、集計したいクエリをデザインビューで開いて、リボンの「クエリデザイン」タブにある「集計」ボタン(Σマーク)をクリックします。

すると、画面下のデザイングリッドに「集計」という行が新しく追加されます。

ここで大事なのが、「グループ化」と「集計方法」の使い分けです。

商品名や日付など、データを分類する基準になるフィールドには「グループ化」を選んで、金額や個数など計算したいフィールドには、プルダウンメニューから「合計」「平均」「カウント」などを選びます。

この設定をするだけで、Accessが自動的に同じ種類のデータをまとめて、指定した計算をやってくれるんです。

「カウント」を使うときは、空白(Null)を含めるかどうかで結果が変わることがあるので注意が必要ですが、基本的には「ID」などの主キーを対象にカウントすれば、正確な件数がわかります。

まずはこの「集計」ボタンを使った操作に慣れて、データをいろんな角度から要約する感覚をつかんでみてください。

演算フィールドで計算式を作るときの注意点とNull値対策

クエリには、テーブルにはない値をその場で計算して表示できる「演算フィールド」という便利な機能があります。

たとえば、テーブルに「単価」と「数量」というフィールドがあったら、これを掛け算して「小計」という新しい項目をクエリ上で作れるんです。

書き方はとってもシンプルで、クエリのフィールド欄に`小計: [単価] * [数量]`って入力するだけ。

コロン(:)の左側が表示されるフィールド名で、右側が計算式になります。

Excelの数式に似てますが、Accessではセル番地じゃなくてフィールド名(項目名)を使って計算するのがポイントです。

ただし、Accessで計算するときに必ずぶつかる壁が「Null(ヌル)」の問題なんです。

Nullっていうのは「数値の0」や「空白の文字列」とは違って、「値が何も入ってない状態」のこと。

Accessの計算ルールでは、「数値 + Null = Null」みたいに、計算式の中に一つでもNullが含まれてると、結果全体がNullになっちゃうんです。

これを知らずに集計すると、一部のデータが計算に入らなくて、合計が合わない!なんて大変なことになりかねません。

そこで登場するのが「Nz関数」です。

この関数は、フィールドの値がNullだったら、指定した代わりの値(普通は0)に置き換えてくれる、すごく便利な機能なんです。

さっきの例なら、`小計: Nz([単価], 0) * Nz([数量], 0)`って書けば、もし数量が未入力(Null)でも、それを0として扱って、計算結果をちゃんと0にしてくれます。

実際の業務データには入力漏れや不備がつきものなので、計算式を作るときはクセでNz関数を入れるようにすると、予想外のエラーや集計ミスを防げますよ。

別のテーブルから値を取ってくるDLookup関数の使い方

Accessでデータベースを作っていると、クエリのリレーションシップ機能だけじゃ対応しきれない、ちょっと複雑なデータ参照が必要になることがあります。

たとえば、集計クエリの中で「特定の条件に合う別のテーブルの値を一つだけ取ってきたい」とか、フォームの入力値に応じて参照先を変えたいとか。

こんなときに活躍するのが「定義域集計関数」の一つ、DLookup関数です。

この関数は、リレーションシップ(テーブル結合)を設定してなくても、指定したテーブルから条件に合うデータをピンポイントで取得できるので、VBAだけじゃなくクエリの演算フィールドでもよく使われます。

DLookup関数の基本的な書き方は、`DLookup(“取得したいフィールド名”, “テーブル名”, “抽出条件”)`という3つの部分でできています。

たとえば、商品マスタから「商品IDが1001」の「商品名」を取得したいなら、`DLookup(“商品名”, “T_商品マスタ”, “商品ID = 1001”)`って書きます。

ここで気をつけたいのは、引数全体をダブルクォーテーションで囲む必要があることと、抽出条件の中で文字列を扱うときはシングルクォーテーションで囲む必要があることです。

書き方がちょっと独特なので、最初は戸惑うかもしれませんが、「どのテーブルの、どの項目を、どんな条件で探すか」っていう構造を理解すれば、すごく自由にデータを取ってこれるようになります。

ただし、クエリの中でDLookup関数を使いすぎるのは要注意です。

DLookup関数は、レコード1件ごとにデータベースに問い合わせる処理をするので、数千件、数万件のレコードを処理するクエリで使うと、動作がめちゃくちゃ遅くなる原因になっちゃいます。

大量のデータを結合して表示したいときは、普通のクエリのテーブル結合(JOIN)を使って、DLookup関数は「特定の値を個別に参照したい場合」や「設定値テーブルから消費税率を取得する」みたいな、ピンポイントな用途に限定するのが、サクサク動かすコツです。

集計クエリを作るときによくあるエラーと対処法

集計クエリを作っていると、「次の一意のキーの一部として指定されていません」みたいな、なんだかよくわからないエラーメッセージに出会うことがよくあります。

これはAccess初心者にとって大きな壁なんですが、エラーの原因のほとんどは「グループ化」と「集計」の設定がちゃんと合ってないことなんです。

集計クエリ(Σボタンが押された状態)では、クエリに入れたすべてのフィールドについて、それが「グループ化の基準」なのか、「集計される値(合計やカウント)」なのか、それとも「抽出条件」なのかを、はっきり決めないといけません。

たとえば、あるフィールドで「合計」を出したいのに、集計行の設定がデフォルトの「グループ化」のままだと、Accessはその数値を「分類の基準」として扱おうとするので、思ったような集計結果になりません。

逆に、抽出条件としてだけ使いたいフィールド(たとえば、特定の日付以降のデータだけを集計したい場合)は、集計行で「Where条件」を選ぶ必要があります。

このとき、「表示」のチェックボックスを外す必要があるっていうのも、忘れちゃいけないポイントです。

Accessは集計の処理として、まずWhere条件でデータを絞り込んでから、グループ化と計算をする順番になってるんです。

あと、データ型の不一致もよくあるトラブルの元です。

特にDLookup関数や条件式を書くときに、数値型のフィールドに対して文字列として条件を指定しちゃったり(ダブルクォーテーションで囲んじゃう)、その逆をやったりすると、うまく動きません。

Accessではデータ型がきっちり区別されるので、テーブルデザインを確認して、数値なのかテキストなのかを常に意識することが大切です。

エラーが出たときは、いっぺんにすべてのフィールドを設定しようとせず、一つずつフィールドを追加しながら動作確認していくと、どこに問題があるのか見つけやすくなります。

焦らず構造を見直すことが、解決への一番の近道ですよ。

広告