リンクユニット

Google検索

Office 2019 Access 2019

空白データ(Null値)があっても合計値を求めたい

投稿日:2020年1月19日 更新日:

Accessのクエリで演算フィールドに式を入力しても値を求められないことがあります。

Excelなら空白セルがあっても計算してくれるので、「あれ?」と思ってしまいますよね。

Accessでは、未入力のデータを含んで計算すると、集計値は空白(Null値)になってしまいます。

空白のセルがあっても、Null値を0として集計するには[Nz関数]を使用します。

空白セルがあっても集計

参考ここでのウィンドウオプションの設定は、[ウィンドウを重ねて表示する]を選択しています。

[タブ付きドキュメント]と[ウィンドウを重ねて表示する]の違い

Access2007以降、オブジェクトを表示すると、タブ付きウィンドウで表示されるようになっています。従来からAccessを使用している方に ...

続きを見る

テーブルの確認

以下のようなテーブルを作成しています。

ただ、[商品C]の[デザイン]と[商品D]の[重量]のセルは空白になっています。

商品ごとの点数の合計を求めたいと思います。

テーブル-データシートビュー

クエリで演算フィールドを追加

テーブル[T_商品テスト]を元にクエリを作成します。

演算フィールドを追加します。

合計: [重量]+[強度]+[デザイン]

クエリのデザインビューに「演算フィールド」を追加

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

合計を求められていない空白のセルができてしまいます。これは、未入力の空白セルがあることが原因です。

クエリの実行

Accessでは、Null(ヌル)値を含んで計算すると、その計算結果はNull値になってしまいます。Nz関数を使用することで解決できます。

Nz関数を使って集計

こういう場合に便利なのがNz関数です。

データがNull値でない場合は、データをそのまま返して、Null値の場合はNullの代替値を返す関数です。

Nz([フィールド名],Nullの代替値)

クエリの演算フィールドを以下のように修正します。

合計: Nz([重量],0)+Nz([強度],0)+Nz([デザイン],0)

クエリの演算フィールドをNz関数に修正

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

クエリを実行

クロス集計などでは、Nz関数が重宝する場合があると思います。

空白のセルを作らないように設定するには

Null値のセルがあると、何かと面倒なこともありますので、テーブルのプロパティで空白を許可しない設定をすることもできます。

すでにデータベースを作成していて、テーブルのプロパティの変更は出来ない場合もあると思いますので、その場合は参考にしてください。

[値要求]プロパティ

テーブルをデザインビューで開いて、フィールドプロパティの[値要求]で[はい]を設定します。

これで必ず値を入力させることができます。既定は[いいえ]です。

テーブルの[値要求]プロパティ

[既定値」プロパティ

また、レコードを作成したときに最初から値が入力された状態にすることもできます。

ここでは[既定値]に[0]を設定します。

テーブルの[既定値]プロパティ

データシートビューに切り替えてみると、新規レコードに[0]が入力されているのを確認できます。

デザインビューで数値型のフィールド[重量]、[強度]、[デザイン]に既定[0]を設定しています。

ただ、[0]は表示したくない場合もあるかと思いますので、状況によって判断してください。

しっかりとしたデータベースを作成したいという場合は、できるだけNull値は避けるようにしたほうが良いと思います。

[既定値]に[0]を設定したデータシートビュー

[空文字列の許可]プロパティ

[テキスト型]には[空文字列の許可]というプロパティもあります。

長さ0の文字列を入力できるようにするかどうかの指定です。

[短いテキスト]の[空文字列の許可]プロパティ

参考Access2019の既定は、以下のように[値要求]は[いいえ]、[空文字列の許可]は[はい]になっています。

Access2019の[値要求]と[空文字列の許可]の既定

Accessのバージョンによって既定値は異なるかもしれませんので、気を付けてください。

Access2003の時は、[空文字列の許可]も[いいえ]が既定だったと思います。Access2007以降は、[空文字列の許可]は[はい]になっています。

値要求 空文字列の許可 動作
いいえ(既定) いいえ 空白はすべてNull値
いいえ(既定) はい(既定) 空白はNull値と空文字列
はい はい(既定) 空白はすべて空文字列
はい いいえ セルには必ず1文字以上の文字を入力しなければならない

以下の記事には、[値要求]と[空文字列の許可]の設定で躓いた事例を記しています。

追加クエリの作成と追加先テーブルの変更

1つのテーブルに別のテーブルのレコードを追加する方法です。 3個テーブルがあって、各テーブルのフィールド名は同じです。 ここでは例として、テ ...

続きを見る

フィルターの空白を含む複数選択で抽出数が異なる

Access2007からの新機能だと思うですが、テーブルやデータシートビューでフィルターが使用できます。 Excelのフィルターと同じように ...

続きを見る

データシートビューで集計列を追加

テーブルのデータシートビューのまま、フィールドを追加することもできます。

空白セル(Null値)がないテーブルでの設定です。

テーブルをデータシートビューで表示します。

[クリックして追加]と表示されたフィールドをクリックして、[集計フィールド]-[数値]を選択します。

データシートビューの[クリックして追加]

以下の[式ビルダー]ダイアログボックスが表示されます。

ここで、数式を入力します。

[式ビルダー]ダイアログボックス

以下のように数式を入力します。

[重量] + [強度] + [デザイン]

フィールド名は、[式カテゴリ]のフィールド名をダブルクリックすると入力できます。

演算子[+]は直接入力してもいいですし、[式の要素]の[演算子]をクリックしても入力することもできます。

[式の値]で[+]を選択してダブルクリックします。

[OK]ボタンをクリックして閉じます。

[式ビルダー]ダイアログボックスに数式を入力

以下のように合計値が表示されます。

フィールドが追加されたテーブル

フィールドの名前は、右クリックしてショートカットメニューの中の[フィールド名の変更]をクリックして、変更します。

ショートカットメニューの[フィールド名の変更]

以下のようになります。

フィールド名を[合計]に変更

デザインビューで確認

この作成したテーブルをデザインビューで確認します。

[合計]フィールドの[データ型]は[集計]になっていることが分かりますね。

フィールドプロパティの[式]には、[式ビルダー]で入力した数式が表示されています。

テーブルのデザインビュー

フィールドプロパティで[結果の型]なども変更することができます。

フィールドプロパティの変更

Access2010以降、データ型に[集計]が追加されました。以下の記事で解説しています。

データシートビューの[フィールドの追加]から設定しても、デザインビューで[データ型]から[集計]を選択しても同じです。

テーブルのデータ型に【集計】が追加された!集計フィールドが簡単作成

Access2010では、テーブルのデータ型に[集計]が追加され、テーブルに集計フィールドを作成できるようになりました。 注意拡張子が[.a ...

続きを見る

フィールドのデータを集計

また、データシートビューで[ホーム]タブの[集計]ボタンを使って、集計行を追加してフィールド(列)の集計を行うことができます。

列の値の合計や平均などの集計処理を自動的に表示できます。

テーブルをデータシートビューで表示して、[ホーム]タブの[レコード]グループにある[集計]ボタンをクリックします。

テーブルのレコードの下に[集計]行が追加されます。

集計をしたい列の集計セルをクリックして、メニューの中から目的の集計処理を選択します。

クエリやレポート、フォームにも[集計]ボタンはあります。

[ホーム]タブの[レコード]グループにある[集計]ボタン

集計の種類は以下のものがあります。主に数値型で使用しますが、テキスト型の場合は、[カウント]と[なし]になります。

  • 合計 フィールドの入力されているセルの値が合計されます。
  • 平均 フィールドの入力されているセルを対象に、平均値が表示されます。
  • カウント フィールドの値が入力されているセルの数がカウントされます。
  • 最大 フィールドの最大値が表示されます。
  • 最小 フィールドの最小値が表示されます。
  • 標準偏差 フィールドの値が入力されているセルを対象に、標準偏差が計算されます。
  • 分散 フィールドの値が入力されているセルを対象に、分散が計算されます。

この集計は、空白セル(Null値)があっても集計値が空白になることはありませんが、計算の対象にはなりません。

空白セルがある場合の集計結果

参考Accessでは、グループごとに集計することもできます。

クエリでデータをグループ化して集計

クエリのデザイングリッドに[集計]セルを表示すると、データをグループ化して、グループごとに集計することができます。 製品ごとに数量や売上の合 ...

続きを見る

Profile

執筆者/はま

おすすめ記事と広告

-Office 2019 Access 2019

Copyright © 2001-2020 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2020 AllRights Reserved Powered by AFFINGER4.