Excelやスプレッドシートで業務管理はどこまで頑張れるのか？②

この記事は今何かと話題沸騰中の ChatGPT-4 と会話形式で「Excelやスプレッドシートで業務管理はどこまで頑張れるのか？」について、業務システム開発実績200件以上という弊社代表の島田がこれから数回に分けてお話をしたいと思います。

前回のコラムのおさらい

＼前のコラムはこちらです／

Excelやスプレッドシートで業務管理はどこまで頑張れるのか？①

Excelやスプレッドシートを使った業務管理の可能性と限界について探求します。管理業務の範囲、Excelと本格的システムの適用範囲

前回のコラムでは、一般的な企業における業務管理の範囲と、その業務管理が、Excelやスプレッドシートでどこまで効果的に管理できるかについてお話しました。

「 VBAを駆使しても、データを絞り込んで一気に取得するというようなことが出来ず、Do Loopを駆使しまくって巨大なすり鉢状プログラムになった記憶があります。

ちょっとマニアックな話になりますが、そのあたり聞いてみますか。本当はSQLが使えたのかも知れません。」

今回のコラムはこの続きから始めたいと思います。

ExcelではVBAを使えばSQL文が発行できるのか

島田

ところで、ExcelではVBAを使えばSQL文が発行でき、その結果を配列等に得られますか？

GPT-4

はい、ExcelでVBA（Visual Basic for Applications）を使用することで、SQL文を発行し、その結果を配列やセル範囲に格納することが可能です。これは主にデータベースに対してクエリを実行し、結果をExcel内で利用するために使われます。

VBAでSQL文を実行する基本的な手順
1.データベース接続の設定：
ADO（ActiveX Data Objects）やDAO（Data Access Objects）などのライブラリを使用してデータベースに接続します。

2.SQL文の作成：
必要なSQL文（SELECT, UPDATE, INSERT, DELETE など）をVBA内で記述します。

3.SQL文の実行：
接続を介してSQL文をデータベースに対して実行します。

4.結果の取得：
SELECT文の場合、結果セットをRecordsetオブジェクトに格納し、そのデータをExcelのセルや配列に転送します。

注意点
データベースの種類：使用するデータベース（例：SQL Server, Oracle, Access など）によって、接続文字列や方法が異なることがあります。

セキュリティとエラー処理：データベース操作にはセキュリティ上のリスクが伴うため、適切なエラー処理とセキュリティ対策が必要です。

パフォーマンスの考慮：大量のデータを扱う場合、パフォーマンスの影響を考慮する必要があります。

VBAを使用したデータベースの操作は、基本的なデータ取得や簡単なデータベースの更新には有効ですが、より複雑なデータベース操作や大規模なデータの扱いには適していないこともあります。そのため、用途に応じて適切なツールや方法を選択することが重要です。

あー、これは何か勘違いしてますね。今聞いてるのはExcelで完結したシステムを作ろうとしてますので、外部のデータベースに接続したらダメです。

島田

あー、1のようにDBに接続するのではなくて、データソースがExcelのシートで、それに対してSQLを使って絞り込めますか？という意味です。

GPT-4

Excelのシート内のデータに対して直接SQLを使用してデータを絞り込むことは、Excelの標準機能としては直接サポートされていません。ただし、いくつかの方法で類似の機能を実現することは可能です。

1.Power Queryの使用：
ExcelのPower Query（または「データの取得と変換」機能）を使用することで、SQLのようなクエリ言語を用いてデータを絞り込むことができます。Power Queryでは、Excelシートのデータをソースとして読み込み、フィルタリングや並べ替え、集計などの操作を行うことが可能です。

2.マクロ（VBA）を使用した方法：
VBAを使用して、Excelシートのデータを配列に読み込み、配列操作やループ処理によりSQLのようなデータ処理を模倣することは可能です。これはプログラミングが必要になりますが、より複雑なデータ操作を行うことができます。

3.外部データベースに一時的にデータを転送：
Excelシートのデータを一時的に外部データベース（例えばAccessやSQL Server）にインポートし、そこでSQLクエリを実行して結果をExcelに戻す方法もあります。この方法は少し複雑ですが、SQLを完全に利用することができます。

これらの方法の選択は、扱うデータの量、必要な操作の複雑さ、および使用可能なツールや技術に依存します。もっとも簡単で直接的な方法はPower Queryを使用することですが、複雑なデータ操作が必要な場合はVBAの使用や外部データベースへの一時的なデータ転送を検討する価値があります。

島田

わかりました。

Power Query？全然分かってないですが、まぁ面倒くさそうですね。

自分はこの「2.マクロ（VBA）を使用した方法：」で必死に頑張ってましたが、これはデータ件数が万単位になると遅すぎますし、マッチしたデータが多いとメモリがパンクしますし、そうならないために結果を一時的に保存する中間テーブル（シート）などが必要になり、システムは肥大化して「これは何か間違っているのでは・・・！？」と思った次第です。

GPT-4

ご理解いただきありがとうございます。ExcelやVBA、Power Queryに関するさらに詳細な質問があれば、お気軽にお尋ねください。お手伝いできることがあれば喜んで対応いたします。

まだ何にも解決してないのですが、まぁ気持ちよさそうにしてるのでそのままにして、先の質問に進みましょう。

昨今では、Excelか本格的な業務システムか？の選択ではなく、その中間に「ノーコード開発」という選択肢がありますね。

ノーコード開発の代表格は「Kintone」ですので、それを例にして聞いてみますか。