EXCEL2013でSQL、EXCELシート同士をクエリする（第１回）

EXCEL2013での話


＜　お題　＞

口振の引落結果／コンビニ振替の支払結果／口振依頼データ作成／CVS振込票データ作成／全顧客の情報管理

これ等を全てをエクセルのブックだけで管理しているが、毎月2度発生する請求処理が大変。これは顧客一覧と未払者（銀行WebよりDLしたCSV）を突合し、未払額を上乗せした当月請求額を算出しなければならない。
処理としては該当する顧客を探し足し算で終わりなので、全てEXCELと手作業だけで行っている。
しかし・・、顧客は数千人、しかも毎月増え続けているので手作業での突合がとても大変。随時顧客のメンテナンスもある。よって最重要以外の処理を手順書を作ってパートさんに説明するが覚えさせるのに一苦労。そしてミス多発。




よくある話です。

私たちからすると、そんな情報や処理をエクセル＋手で管理しようとするから大変になる＝当たり前の結果、な訳でDBとC#があれば一瞬で解決でしょ！！




と考えるのが普通。
が、色々と制約があり社内の規定によりPCへのプログラムインストールは一切禁止。ACCESSさえ導入不可。
要するに何もインストールせずエクセルとSQL（VBA）のみを使い問題を一瞬で解決させる、そんな夢？のような話。

が、sqliteやMSのcompact DB（VSのサポートがVS2013以降なくなったが）ならインストール不要。
C#もXCOPY配置でOK？？必須.NETフレームワークはMS製なのでWindowsUpdateで取込まれるから問題なし。

結論から言えば、インストール不要DB＋インストール不要C# EXEを使えば問題なく対処できる可能性は高い。





しかし・・・そんな人気のない仕組みを使う＝不具合で嵌ると抜け出せなくなる可能性大。ただEXCEL（VBA＋ADO）も情報は非常に少ないが、どうせ嵌るならexcelの方が面白そうなので、excelで解決する手段を選択。その方がお客にも説明し易いからね。

という事で！エクセルだけを使い、他のDBなど一切使わず、エクセルシート同士をSQLでクエリし、業務を行う、ですね。




Googleで「EXCEL SQL 」とググると簡単に出来るらしい。
方法はエクセルの裏にあるVBAでADOを使うとエクセルのシートをテーブルとして扱え、すると普通にSQLが書けるので実行させ、結果をとあるシートへ貼りつけるだけ。なんだかとても簡単そう。

と思ってやってみたら、これがとんでもない！！！
joinキーがちょうどEXCELの数字制約に引っかかるケースだったため、他にもエクセルの制約に引っかかる箇所多発！最終形に落ち着くまでが非常に大変！！
結局調べ＋試行錯誤で丸々1週間。SQLServer使ってSQL＋C#を書けば一瞬で終わるのに、何故こんな苦労を・・・？。結果もうヘトヘト。

それとnet上に多々あるエクセル＋SQL解説はとても素晴らしいHPもあるが、どうも教科書的な解説で美し過ぎる！必要なのは裏のキタナイ部分であり、発生する問題解決事例なのだか、、それ等は皆無。従って残念ながらほぼ参考にならなかった。もっとも今回EXCEL＋VBA＋SQL（ADO）で苦労したが、C#＋DBを使えば一瞬で解決するので、やはり一般的でない事はするものではない、ようです。実はEXCEL VBA関数を応用しても出来ますが・・第3回に記す。





普段の開発？とは異なる点

・とにかくエクセルには数々の制約があり過ぎる
　内部で勝手に行われる数字の自動処理が非常にやっかい
・エクセルシートは自動処理され過ぎるため普通にjoinさせるだけのSQLでも苦労する
　ドライバからADOに渡される生データを見ることはできないので、ホボ勘で勝負するしかない
・ADOが古すぎる
　OSはWindows10だが20年前VB6時代に使っていたado、古過ぎ

・VBAは、、、久々のVB6＞VBAだが素晴らしい！！
　VB6がなければ現代のプログラマ人口は1/1000？
　感動的な適当さでプログラムが組め、物凄く素晴らしい事を再確認！！
　VB6はソフトウェア業界最大の発明では！？




前置？が長くなったので続きは次回。　具体的な問題点などUPします。




パート２はこちら　→　EXCEL2013でSQL、EXCELシート同士をクエリする（第２回）
パート３はこちら　→　EXCEL2013でSQL、EXCELシート同士をクエリする（最終回）








＝＝＝＝＝＝＝＝＝＝＝＝
　　VBAについて
＝＝＝＝＝＝＝＝＝＝＝＝

上記ではVBAを褒めましたが、それはVB6を褒めただけ。そのVBAはVB6のサブセットのようなモノ。VB6は20年前に流行った言語（VBAは未だ生き続けているが）。よって2015年現在VBAは学習教材としては 「　最悪！　で　極悪！　」

素人が遊びでやるならOK？ですが、VBAを使ってソフトの学習をしようなどと決して！考えない方が良いです。こんなに楽でいい加減に組んでも動く言語は他になく、もしこれで覚えてしまうと他の言語で超苦労、、というよりプロと言われる人々の中にも無知人間が多数存在し、その無知が作った穴だらけのシステムのせいで（特にSQL！）情報漏えいしたと騒がれている時代。楽なVBA組んで喜んでいては一生Web系アプリなど、危険過ぎ組めません。やるならC#のコンソールアプリかWindowsフォームアプリでも組んで学習しましょう。

また初心者がいきなりWeb系アプリを作ることもお勧めしません（スマホ用アプリは別）。無知なときに作ると脆弱性多々でサーバー乗っ取られるか情報漏えいして会社クビになりますよ！まず普通に言語使えるようになり、十二分にセキュリティーの勉強を行ってからWeb系アプリ、組みましょう。Web系アプリは最新技術満載でどんどん新しく進化しているので面白いです。JavaScriptなども古いですが面白く怖く、ですね。スマホアプリ開発も新時代に入ってきましたし、スマホアプリなら脆弱性などさほど気にせず組めるので良いかも？知れません。

でもそろそろ・・ロボットの時代？かも知れませんね。労働人口減少？怖いですね・・。
＝＝＝＝＝＝＝＝＝＝＝＝