ExcelでSQLを使う-011: SQL 算術演算子と算術関数 SUM, AVG,MIN,MAX
動物のお医者さん 第3章 |
バイトに研修に実習、4年生になったハムテルたちは、獣医師への道まっしぐらーとそう順調にはいかないようで、元競走馬の世話やら牛の出産立ち合いやら動物園での飼育係体験やらで、もう大変。チョビの”あ・そ・ぼ”とミケの蹴りに力づけられつつ(?)、着実に経験を積んでいきます。異常な動物好きの二階堂の弟妹たち、菱沼に絶対服従の犬・源三や見合い相手まで登場して、獣医学部の日々は過ぎていく。試験シーズンはもう目前ー!(同書の帯より)
明後日には、出産師が我が家のラブラドール・レトリバーを迎えにきます。犬の出産は、とてつもない一大イベントです。複数のベテラン出産師が昼夜交替で仔犬を取り上げます。そして、出産二日目にはママ犬が仔犬を連れて帰ってきます。そうなれば、24時間体制での仔犬の世話に突入。本ブログが一応の完結を迎えるかどうかは、微妙なところとなった。
◇◇◇◇◇
算術演算子
SELECT 節に列記した数値型の列に対しては、次の算術演算子を使うことができます。
---------------------------
記号 機能
---------------------------
+ 加算
- 減算
* 乗算
/ 除算
% 剰余
---------------------------
算術演算子を使うことにより、表<商品台帳明細>の基準販売単価を5%値上げした値を検索することができます。算術演算子を利用すると、検索結果の型が通貨型ではなくなります。そのために DSelect関数は、通貨型のフォーマットを適用しません。通貨型にフォーマットして表示したいのであれば、FormatCurrency関数を使って整形する必要があります。
? DSELECT("SELECT 販売単価 * 1.05 FROM [商品台帳明細$A1:Z10]",,Chr(13))1606.51764
1764
1764
1932
? DSELECT("SELECT FormatCurrency(基準販売単価 * 1.05,2) FROM [商品台帳明細$A1:Z10]",,Chr(13))
¥1,764.00
¥1,764.00
¥1,932.00
単なる小数点を含む桁揃えをしたいのであれば、FormatNumber関数を使います。
? DSELECT("SELECT FormatNumber(基準販売単価 * 1.05,2) FROM [商品台帳明細$A1:Z10]",,Chr(13))
1,764.00
1,764.00
1,932.00
※FormatDateTime関数
? DLookup("SELECT 日付 FROM [Sheet2$A1:H100] WHERE FormatDateTime(日付)=#2019/02/11#")
2019/02/11
? DLookup("SELECT 時刻 FROM [Sheet2$A1:H100] WHERE FormatDateTime(時刻)=#22:30:00#")
22:30:00
算術関数
SELECT 節に列記した数値型の列に対しては、次の関数を使って計算することができます。
COUNT(*) |
WHERE節の条件を満たす行の総数を算出します。 |
SUM(<列名>) |
指定した列の値について合計を算出します。 |
AVG(<列名>) |
指定した列の値について平均値を算出します。 |
MAX(<列名>) |
指定した列の値の最大値を求めます。 |
MIN(<列名>) |
指定した列の値の最小値を求めます。 |
集計関数に列を指定する時は、列の名前をカッコで囲みます。一つの SELECT節に複数の関数を記述する時には、関数をカンマで区切ります。
FROM 商品台帳明細
SELECT AVG(販売単価), MIN(販売単価), MAX(販売単価)
FROM 商品台帳明細
? DSelect("SELECT FormatCurrency(AVG(販売単価), 2) AS avg_price
FROM [商品台帳明細$A1:G100]")
¥1,682.00
? DSelect("SELECT AVG(販売単価), MIN(販売単価), MAX(販売単価)
FROM [商品台帳明細$A1:G100]")
1682;1530;1840
集計関数とGROUP BY 節
<商品台帳明細> |
集計関数を GROUP BY 節とともにSELECT文と組み合わせて使うと、強力な処理を行うことができます。次の例では、区分ごとの平均販売単価を求めています。
? DSelect("SELECT 区分, AVG(販売単価) FROM [商品台帳明細$A1:H100] GROUP BY 区分",,Chr(13))
0;1530
1;1680
2;1840
? DBSelect("SELECT 区分, AVG(販売単価) FROM 商品台帳明細 GROUP BY 区分","D:\DB1.mdb",,Chr(13))
0;1530
1;1680
2;1840
不具合発生メモ!
先頭行にnull行が発生した。原因は目に見えない空行の存在だ。Access相手のDBSelect関数では、あり得ないこと。実に、エクセル固有の現象だ。どうやら、《目に見えない空行が存在します!》という警告を出す必要がありそうだ。