エクセル上でデータベース(MySQL)へODBC接続し集計を行う方法

Excel/VBA

2021.05.07

日頃利用している業務システムのデータをエクセルで集計したいと言う要望は、どんなジャンルであれ未だに高い需要があると思います。( 情シス的にはセキュリティ面やサーバーへの負荷等のリスク的にシステムからCSV吐かせてそれで行って欲しい所ではありますけどもね…)本日はエクセル上で直接DBアクセスを行い、ワークシート関数で集計を作成するやり方をご紹介します。

ODBC接続-展開
D関数で集計を行う
Dの意志を継ぐ者たち

ODBC接続-展開

予めODBC設定が有る前提です。エクセル【データ】タブのその他のデータソースからデータ接続ウィザードを選択。

データソースの種類はそれぞれかと思いますが、今回はODBC DSNを選択。ちなみに利用しているドライバはMySQL ODBC 3.51 Driverです。

接続したいテーブルを設定します。

ここは何も指定せず完了でOK

どこのシートに展開するか聞かれるので任意の位置を選択して展開します。

単純なSELECT ALL(全レコード取得)で良ければ上記で引っ張ってきて集計作成でも大丈夫ですが、何十万・何百万レコードとか入っているテーブルをALLで持って来るのはとんでもない自殺行為です👼

そんな時は【テーブルデザイン】タブのプロパティを開いてWHERE句の指定を行ったり、より複雑なSELECTを行う事が可能です。

取ってくるものにもよると思いますが、大体日付で今月分みたいな感じで指定をする事が多いですね。

D関数で集計を行う

無事データを取ってこれたので、お次は実際に集計を行って見ます。データベース集計を行う時に良く使われる関数の一つでD関数(データベース関数)というものが有ります。

上記画像のDSUM関数はDSUM(データベース範囲,合算値,条件範囲 )の構文となっているので、商品名がけしごむの個数をSUM(合計)する関数です。SUMIFS的な使い方ですが、条件式を視認しやすく管理が非常に楽なのが特徴です。

例えば【日付が2019/5/1以降の場合】と言う条件を追加したい場合は上記の様に条件範囲の中に足してやるだけで出来ます。
色んな商品、色んな条件で集計シートを作成して行く時もコピペしやすいし、ミスも発見しやすい優秀な関数なのです。

Dの意志を継ぐ者たち

他にも色々なD関数が有りますが、特に良く用いられるDの者達がこちら↓

関数	構文	概要
DAVERAGE	DAVERAGE(データベース, 平均を取りたい値, 条件範囲)	指定した条件に一致した値の平均を取る。出番はあまり多くは無い。他にもDMAXやDMIN等がいる。
DCOUNT	DCOUNT(データベース, 数えたい値, 条件範囲)	単純に条件に一致するレコードを数える関数。ただレコードが空白の場合も数えてしまうので注意が必要。
DCOUNTA	DCOUNTA(データベース, 数えたい値, 条件範囲)	個人的に最も良く使うDの者。空白でない場合にCOUNTするのでDCOUNTと場面で使い分けよう。
DGET	DGET(データベース, 取得したい値, 条件範囲)	集計的にはあまり使わないけど便利、LOOKUP的関数。複数一致する場合は#NUM!が返るので注意
DPRODUCT	DPRODUCT (データベース, 積算したい値, 条件範囲)	SUMPRODUCTのDバージョン。