VBA100本ノック 97本目を動画で解説しています。Excelの神髄さんの模範解答はコチラ
問題
#VBA100本ノック 97本目
DB1.accdbから取引先&商品で集計出力する。
■出力項目
取引先CD,取引先名,商品CD,商品名,数量合計,金額合計,平均単価,標準単価,最低単価
平均単価は金額/数量(整数に丸め)
最低単価は全取引先での商品の最低単価
■抽出条件
平均単価 > 標準単価
※シートは任意
解答
Sub ノック97本目()
Dim adoCN As New ADODB.Connection '接続本体
Dim strDb As String
strDb = ThisWorkbook.Path & "\DB1.accdb" '絶対パスで指定
With adoCN
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDb 'Access
.Open
End With
Dim ws As Worksheet: Set ws = Sheets("出力"): ws.Cells.Clear
Dim adoRS As ADODB.Recordset: Dim strSQL As String
strSQL = _
"SELECT T1.取引先CD,M1.取引先名,T1.商品CD,M2.商品名," & _
" SUM(T1.数量) AS 数量合計," & _
" SUM(T1.数量*T1.単価) AS 金額合計," & _
" ROUND(SUM(T1.数量*T1.単価)/SUM(T1.数量),0) AS 平均単価," & _
" M2.標準単価," & _
" S1.最低単価" & _
" FROM (((T売上 AS T1" & _
" LEFT JOIN M取引先 AS M1 ON T1.取引先CD=M1.取引先CD)" & _
" LEFT JOIN M商品 AS M2 ON T1.商品CD=M2.商品CD)" & _
" LEFT JOIN (SELECT 商品CD,MIN(単価) AS 最低単価" & _
" FROM T売上" & _
" GROUP BY 商品CD)" & _
" AS S1 ON T1.商品CD=S1.商品CD)" & _
" GROUP BY T1.取引先CD,M1.取引先名,T1.商品CD,M2.商品名,M2.標準単価,S1.最低単価" & _
" HAVING ROUND(SUM(T1.数量*T1.単価)/SUM(T1.数量),0)>M2.標準単価"
Set adoRS = adoCN.Execute(strSQL)
Dim i As Long
For i = 0 To adoRS.Fields.Count - 1
Cells(1, i + 1) = adoRS.Fields(i).Name
Next i
ws.Range("A2").CopyFromRecordset adoRS
End Sub■考え方・流れ
0:00 冒頭・問題確認
1:52 テーブルに名前を付けて扱う(AS句)
5:38 テーブルを集計する(GROUP BY)
8:14 テーブルを集計する(SUM, ROUND)
10:08 標準単価をM2から拾う
12:08 最低単価のサブクエリを作る
16:07 グループ化したデータを絞り込む(HAVING)
今回はSQLのところ以外は一切触りません！
その他コードの詳細を見たい方は96本目を参考にしてね！
SQLも実務で使う人は
多そうですもんね！
俺はSLをよく使ってたぞ！
電車かな…？
テーブルに名前を付けて扱う(AS句)
まずどういうことかって言うと…
今はテーブル名、列名をそのまんま使ってるよな！
FROM T売上← とか SELECT T売上←みたいな部分だ！
ここに、以後扱いやすいような短い名前(T1とかM1とか)を付けていくって話だ！！
名前を付けたい時は、FROMのところを変更するところから！
「テーブル名 AS 付けたい名前」もしくは「テーブル名 付けたい名前」だ！
変更後がこちら↓FROMが変更できたらお次はSELECTやLEFT JOINのところを変更しよう！
コードを実行すると…
一応補足↓
T1,T2みたいなのはよく見るぞ！
てかこれ以外は見たことねえ！
テーブルを集計する(GROUP BY)
続いては！！データを”集計”していくぞ！！
抜き出すだけじゃなく！！合計したり…まとめたり！！
まずは合計する列を追加だ！！
まずはSELECT のところに「T1.数量」を追加して、
数量の列を追加しよう！
これで抽出した結果↓
今から、A～D列が同じグループを合計していってみるぞ！！
そのためには、GROUP BYというものを一番下に入れてあげる！！
一番下だぞ！！一番下！！
LEFT JOINの下だ！
そこに、SELECTしたデータの中から、グループ化したいものを
書いてあげる！！
今回はA～D列の項目でグループ化したいから…↓以下イメージ！
実行結果↓
A～D列で同じグループの合計が出せてるね！
テーブルを集計する(SUM, ROUND)
お次は！！SQL内でSUMやROUNDを使ってみるぞ！！
ココは正直…感覚でいけると思う！！
“_”を使って改行だけしてるからね！
数量と単価を掛け算すりゃええから…
SUM(T1.数量*T1.単価) と書けるんスわ！
お次は平均単価！！
平均単価は、金額合計から、数量を割ってあげて、それを丸めてあげればいいから…
ROUND(SUM(T1.数量*T1.単価)/SUM(T1.数量)) でOKOK
結果はこの通り！
標準単価をM2から拾う
お次！！これは簡単！！
標準単価を拾うだけ！！！
標準単価はM2に入ってるからね！
ただし！！！今回はSELECTするだけじゃなくて、
GROUP BYのところにも入れてあげる必要があるぞ！！！
そこだけが注意だ！
実行結果↓
ぎゃんぎゃんいこう！と言いたいところなんだけど…
これは、既にテーブルにデータが存在してたから簡単だったんだよね！！
お次の章からは、テーブルに存在しないデータを作っていくぞ！！！
イメージとしてはこんな感じ！↓
SQLの中で、商品CDと最低単価の2列のテーブルを作る感じね！！www
頑張ろう！！
最低単価のサブクエリを作る
上で説明した通り、SQLの中でテーブルを作ることを「サブクエリ」を作るというぞ！！
どこに書いていくかというと…FROMの中だ！
見にくいので改行↓
LEFT JOINの行をコピペして、M商品になっているところ！！！
ここに一つ新たにSQLを記述していくぞ！！！
商品CDと最低単価の2列のテーブルを作りたいから、まずはこんな感じ↓
ほんで最低単価を出すには…
T1の単価列のミニマムを出せばいいから…
FROMはもちろんT売上から。
SELECTのところに
MIN(単価) AS 最低単価 を追加！
GROUP BY を作って
商品CD でグループ化すればOK！
後はこのサブクエリに名前を付けて…
ココさえできたらあとは簡単…
一番上のSELECTのところに、S1.最低単価と、
GROUP BYのところに、S1.最低単価を付け加えてあげりゃOK！
ムズすぎるんやがな…
ここに新たにテーブルを作ってるイメージが見えればOK！！
実行結果でごわす↓
グループ化したデータを絞り込む(HAVING)
最後だ…これで最後だ…
グループ化したデータを絞り込んで終わり！！
前回使ったWHEREとほぼほぼ一緒なんだ！
だけど…ほんのちょっとだけ違うから気を付けてくれ！
実は…SQLが処理される順番ってモノがあってですね…www
一度GROUP BYした後に、絞り込みたい場合はHAVINGというものを使うんスわ…www
むずいわーーー！！
だけど…書き方は簡単！！！WHEREと全くおんなじだからね！！
なんだけど…けど…けど…
SELECTのところの名前で指定することはできないんスわ…
処理される順番がこんなだから…
つまり…
だからまぁ…、めんどいんだけど、
HAVING ROUND(SUM(T1.数量*T1.単価)/SUM(T1.数量),0)>M2.標準単価
って感じで、平均単価を求める式をここでももう一回書かないといけないんスわ！！
実行結果↓
お疲れ…さまでした…！
お疲れさマントヒヒ！
お疲れさまでした！
あとがたり
おはこんばんちは。uぷ主です。
97本目！！SQLの問題でした！
あれ？VBA100本ノックでは…？？
VBAはいずこ？？
と思いながらなんとか完成。
覚えりゃそこまで難しくはない気がしてるSQLなんですけど…、
教えるとなるとまぁ大変でしたわwwwwwww
疲れた～～～～！！！
マジで一通り解説した気がするぞwww
頑張ったからポケモンしよ。
コメント