- Accessクエリを扱う時によく使われる関数の使い方が分かる
- Access関数の特徴とExcel関数との違いが分かる
今回からは関数の使い方についてシリーズで4回にわたって投稿していきます。
1回目の今回は、Access初心者の方が身に付けておきたい関数の基礎についてです。
Excelと同じように、Accessにもたくさんの関数がありますが、全てを知っておく必要はありません。
いくつかの関数を組み合わせるだけで、Accessのデータを思い通りに加工することができます。
本記事では、よく使うAccess関数の中でも特におさえておきたい関数を5種類に絞って紹介します。
他にも文字列や日付を操作する関数などもありますが、まずは本記事で紹介する関数をしっかり身に付けましょう。
また、AccessとExcelの関数の違いについても本記事の終盤で触れますが、Access関数を使うための本質がきっと理解して頂けるでしょう。
その1-四則演算の使い方
まずは、全ての関数の基礎となる『四則演算』の使えるようになりましょう。
実際にテーブル「発注伝票明細」にある2つのフィールド(数量と単価)を掛けて金額を計算してみます。
数式を使用する前準備として、デザインビューで新しいクエリを作っておきましょう。
クエリを作ったら、新しいフィールドに『金額:[数量]*[単価]』と入力します。
書き方のルールは「フィールド名:数式」ですが、フィールド名を書き忘れがちになるので注意しておきたいところです。
データシートビューで確認すると『金額』フィールドが作成されています。
計算結果も間違いないようです。
Accessの場合、フィールド単位で数式を設定するようになっています。
一方、Excelはセル単位で数式を設定します。
Excel関数になじんでいる方にとって、Accessの数式は使い勝手が悪いものに感じてしまうでしょう。
Accessの数式に慣れるためにも、まずは手をつけやすいします四則演算でいろいろ試してみることをまずはオススメします。
その2-集計関数
集計関数は集計クエリを作るときに使われる関数です。
Accessデータベースの運用を進めると、当然入力されたデータが溜められます。
溜まったデータをいろいろな角度から集計して分析をすすめると、新しい方向性が見えてきます。
データを有効活用するためにも、Accessの集計関数は強力な武器になりえます。
詳しい使い方は集計クエリについて記事を書いていますので、気になる方はそちらをごらんください。
>>【Accessクエリ入門】4回目-集計クエリを作る方法
集計クエリで選べる集計方法と関数名をかんたんに下にまとめておきます。
| 集計方法 | 関数名 |
|---|---|
| 合計 | Sum |
| 平均 | Average |
| 最小 | Min |
| 最大 | Max |
| カウント | Count |
| 標準偏差 | StDev |
| 分散 | Var |
| 先頭 | First |
| 最後 | Last |
全部で9種類の集計方法を選べますが、一番お世話になるのは「合計」「カウント」の２種類でしょうか。
「平均」もありますが、Nullも一つのデータとしてカウントされます。
Nullを排除したければ、「合計」と「カウント」の組み合わせをオススメします。
「最小」と「最大」もたまに使う事のある便利な関数です。
「標準偏差」や「分散」を使いこなすには、統計についてある程度の知識があった方がいいでしょうね。
当ブログでもExcelの統計関数についての記事を書いていますので、気になる方はそちらをごらんください。
>>エクセルの統計関数を使って問題を解決する
「先頭」と「最後」については、集計する前に「データの並び替え」という前準備が必要なようです。
つまり、クエリを重ねるか、サブクエリを使うか、どちらかを選ばないといけないということですね。
もし並び替えをしていなければ、適当にデータが選ばれるようです。
詳しい内容については、Microsoftの公式ページで「First関数およびLast関数」が解説されているようです。
>>First 関数および Last 関数
その3-条件分岐(IIf関数)
四則演算に次に習得しておきたいのは、『条件分岐関数』です。
Accessでクエリを作るときに、いろんな場面で使われる関数の代表格と言ってもよいでしょう。
ExcelではIF関数が条件分岐でよく使われていますが、Accessでは「IIf関数」という名前になっています。
IIf関数の使い方はExcelのIF関数と基本的に同じです。
- IIf(条件,Yesの時の処理,Noの時の処理)
ここでは金額が10000円以上を「高額」、9999円以下を「低額」に仕分けてみます。
新しいフィールドに『判定:IIf([金額]>9999,"高額","低額")』と入力します。
データシートビューで確認すると、金額によって高額と低額に仕分けることができました。
その4-Format関数
Format関数は指定した表示形式に変換する関数で、日付型を「○年」や「〇年〇月」などに変えたい時によく使われます。
- Format(数式,"表示形式")
例えば、日付型を使って集計関数を作った場合は、グループ化さるのは日付単位のみです。
年や月単位での集計したい人にとっては、期待していた集計ができません。
Format関数で集計したい単位の文字列型フィールドを作ってあげることで、年や月単位の集計が可能になるのです。
では、日付単位で入力されているフィールド「発注日」を「発注年月」に変換してみましょう。
新しいフィールドに『発注年月:Format([発注日],"yyyy年mm月")』と入力します。
デザインビューの場合、入力後は表示形式が自動で変わりますが何も問題ありません。
データシートを確認すると、年月単位で表示されたフィールド「発注年月」に変わりました。
Accessの表示形式は基本Excelと同じです。
Excelの表示形式をある程度覚えている方はそのまま使いまわせます。
Excelの表示形式を覚えていない方は「Excelのマクロ記録」を活用してもよいでしょう。
その5-Nullやエラーを処理する関数
Accessのクエリを作っていると、「#エラー」をいう表示がときどき現れます。
そのようなエラーを回避するためにも、知っておきたい関数がいくつかあります。
必要に応じてIIf関数と組み合わせましょう。
Nz関数
Nz関数は、指定したフィールドがNullの場合に返す値(戻り値)を指定します。
- Nz(フィールド名,戻り値)
よほどの例外がなければ「0(ゼロ)」を第二引数に指定しておけば問題ないでしょう。
シンプルで可読性が高いので、まずはNz関数で問題が解決できないか試してみましょう。
IsNull関数
指定したフィールドや式がエラーになるかどうかを判定し、TrueかFalseを出力します。
- IsNull(フィールド名)
下のようにIIf関数と組み合わせるのが良く使われるパターンです。
- IIf(IsNull(フィールド名),エラー回避の数式,数式)
Nz関数で問題が解決できない場合に使ってみましょう。
IsError関数
指定したフィールドや式がエラーになるかどうかを判定し、TrueかFalseを出力します。
- IsError(数式)
IsError関数もIIf関数と同じような使い方です。
- IIf(IsError(数式),エラー回避の数式,数式)
引数に数式を設定するので、IIf関数と併せて使うとどうしても長くなってしまいます。
IsError関数を使う必要がある場合は、入力支援機能の「式ビルダ」や「ズーム」を使って式を書いていきましょう。
AccessとExcelの関数で違うところ
Access関数の特徴は「フィールド毎に式を設定する」ということでしたよね？
「一つの数式で列(フィールド)の数式を完結できる」というメリットは、「セル毎の微調整ができない」というデメリットだとも言い替えることができます。
一方、Excelの場合はセル毎に数式が設定されるので、微調整が簡単です。
Excelに慣れていると、Accessでの式の使い方は使いづらく感じるでしょう。
Accessでは一つの数式で答えを完結させるためにも、IIf関数をはじめとした条件分岐関数を操るスキルが重要になってきます。
まとめ
今回は、Accessでよく使う5種類の関数を紹介しました。
本記事で紹介した5種類の関数を繰り返し使っていくことで、Access関数のクセが分かるようになります。
Excelに慣れてしまっている方は、クセの強いAccess関数に戸惑ってしまうかもしれません。
しかし、Accessでの関数の使い方をExcelのテーブル機能にそのまま流用できます。
Excelの場合でも、データ量が大きくなれば列単位で式を完結させた方が、作業効率は高くなるのでぜひマスターしておきたいところです。
次回はAccessの文字列操作関数について解説していきますが、関数の基本的な考え方がしっかり理解できていれば、スムーズに学習は進むでしょう。
本日もありがとうございました。