空白データ（Null値）があっても合計値を求めたい時はNz関数

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

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

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

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

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

テーブルの確認

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

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

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

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

テーブル［T_商品テスト］を基にクエリを作成します。

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

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

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

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

参考クエリのデザインビューで演算フィールドなどを追加する場合は、ズームウィンドウを利用すると入力しやすくなります。

［ズーム］ウィンドウを表示して入力

Nz関数を使って集計

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

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

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

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

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

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

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

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

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

Microsoftの解説ページをリンクしておきます。

Access の Null 値と、長さ 0 の文字列の違いについて（Microsoft）

［値要求］プロパティ

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

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

［既定値」プロパティ

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

ここでは［既定値］に［0］を設定します。

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

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

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

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

［空文字列の許可］プロパティ

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

［OK］ボタンをクリックして閉じます。

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

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

以下のようになります。

デザインビューで確認

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

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

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

フィールドプロパティで［結果の型］なども変更できます。

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

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

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

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

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

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

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

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

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

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

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

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

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