「Power Query × SQL Server」で実現！効率的なデータ基盤の構築法

ビッグデータや機械学習などのキーワードが有名になり、データの利用価値 の高さが広く知られるようになってきました。会社組織などの事務業務においても データの活用ニーズ は高まる一方です。

一方で大量のデータをSUM関数などの 従来のExcel関数 を駆使して処理することは 既に限界 に来ています。大量データを扱うには 強力なデータ基盤が必要 です。データ基盤を構築できれば、データ処理の効率を飛躍的に向上させることができます。

しかし、データ基盤の構築作業の負担が大き過ぎては本末転倒になってしまいます。また、そのデータ基盤の構築に高度なITの知識が必須であるとすると、その実現自体が困難なものになってしまいます。

そこで、この記事では Power Query と SQL Server を組み合わせて 効率的にデータ基盤を構築する方法 をご紹介します。

データ基盤が構築できたら今まで何時間もかけて手作業で処理していたものが一瞬で終わらせてしまうようなことも可能となります。

皆さんも是非、強力なデータ基盤を構築して膨大な事務業務からの解放を目指してみて下さい。

データ基盤とは

まずデータ基盤について解説します。データ基盤を簡単に表現すると以下のようになります。

必要なデータを集めて保管できる
必要なデータを取り出せる
多くの人がデータにアクセスできる

このデータ基盤の機能について、従来のExcel関数を駆使した仕事の仕方ではどうやっていたでしょうか？

従来の方法：必要なデータを集める

様々なシステムからダウンロードしたCSVファイルなどを Excelファイルにコピー＆ペーストして、SUM関数などで集計するというような操作が必要です。データの量が大きくなると手間が増大するのと同時に作業ミスのリスクが増えていきます。

更に処理すべきデータが複数あったり、毎日データを更新しなければならない場合を想像してみて下さい。データを集める作業だけで軽く1時間程度かかってしまうことも想定されます。

従来の方法：データを取り出す

コピー＆ペーストしたデータに対してSUMIF関数などを使って様々な条件の数値集計を行います。しかし、一つの関数だけでは実現できない場合は VLOOKUP関数や COUNTIF関数など、様々な Excel関数を駆使してデータの処理が必要になります。

しかも Excel関数は一つずつ使い方を身に付けて正しく使う必要があって、分厚い解説本や Web記事の検索が都度必要になります。

従来の方法：多くの人がデータにアクセス

組織などで仕事をしているなら共有フォルダやクラウドサーバーで Excelファイルを共有することができます。

しかし、ここに問題があるのです。ファイルは簡単にコピーができてしまうので、誰かがコピーを作成して別の作業を始めたとします。すると、どちらのファイルが最新なのか、たちまち分からなくなってしまいます。

コピー禁止とした場合でも、誰かがファイルを開いているとファイルを上書き保存できず、せっかく作業した内容を保存できないか、別名でコピーを保存せざるを得なくなってしまいます。

これは実は非常に深刻な問題で、この一つの問題によって大勢での共同作業が成立しにくくなってしまいます。

データ基盤の必要性

以上のようにデータ基盤の一つ一つの機能は単純のようで非常に重要であることが分かります。データ基盤があれば上記のように日々多くの人が悩まされている課題を解決することが可能なのです。

また、有難いことにこれらを解決する手段が一般ユーザの手の届くところに提供されているのです。それが、Power Query と SQL Server です。

次の章で Power Query と SQL Server について紹介します。

Power Query と SQL Server について

Power Query：新たな Excel のデータ処理機能

Power Query は従来の Excel関数とは全く異なる新しいデータ処理の仕組みです。実は Excel2016 から標準搭載となった機能ですが、未だに活用が普及しているとは言い難い状況です。

Excel の登場が 1980年代ということからすると、Power Query は比較的新しい機能ではありますが、使っていない人が多くて大変勿体無いと筆者は考えています。

Power Query の特徴

Power Query の特徴は以下のようになっています。

マウス操作だけでデータ取り込み 可能
複数のファイルを一気に取込み が可能
CSV, テキスト, PDF, Excel, データベースなど 様々な形式のデータを取込み 可能
マウス操作だけでデータの加工 が可能

これを見て勘の良い方はお気付きかもしれませんが、データ基盤に必要な機能 がかなり揃っていることが分かります。

しかし、１つだけ弱点 があるとすると、Power Query は Excel の機能なのでやはり Excel ファイルとして保管 されるので、大勢の人と共有する際に前述のような問題が発生してしまいます。

Power Query を習得する

Power Query は大変強力な機能で、現代においては必修科目的なものになっています。興味があって勉強してみたいと思った方は以下の記事がお役に立つかもしれませんので、良かったら覗いてみて下さい。

SQL Server：無償版もある本格データベース

SQL Server はITのプロも活用する Microsoft社製の本格データベースです。しかも、有難いことに SQL Server Express という 無償版 も公開されており、誰でも無償で利用できるのです。

無償版だからと言ってあなどってはいけません。有償版の機能のほとんどが使えますし、制限がかかっているデータの最大容量についても、素人が使い切ろうとしても並大抵では使い切ることができないほどのデータ量設定になっています。

つまり、本当のITのプロフェッショナルだけが有償版が必要で、広く一般的な人にとっては無償版で十分なのです。

しかも、万が一データの最大容量に達してしまった場合も、有償版にスムーズに移行できる仕組みがあるので心配は無用です。その頃にはそのデータベースはお金を払ってでも利用価値があるシステムに成長していることでしょう。

また、データベースはコンピュータが発明された当初から考案されて利用されてきた利用価値の非常に高いアプリケーションです。使わない手は無いと思います。

SQL Serverの特徴

SQL Server の特徴は以下のようになります。

データを 大量に保管 可能
大量のデータの 高速処理 が可能
多数のデータアクセス に対応可能
取り込めるデータ形式は限定的

SQL Server もデータ基盤に必要な機能がほとんど揃っていることが分かります。

しかし、SQL Server も万能では無く、4つ目の特徴である 取り込めるデータ形式は限定的 という難点があります。

SQL Server では以下のような要件を満たしたデータで無ければ大量に取り込むことは難しいのです。更にITの専門用語を知らないとコマンドを使いこなすこともできません。

SQL Server が取り込めるデータ形式

テキストファイルである
１行目が列名、２列目以降がデータ
データはタブやカンマなどで区切られている
例外的なデータ形式の行があるとエラーになる

SQL Server の導入

SQL Server は本格データベースですが、実は導入するのは簡単で、多くのソフトウェアと同じように簡単にインストールして数件の設定をするだけでもう使えるようになってしまいます。

SQL Server を導入してみたいと思った方には以下の記事がお役に立つかもしれません。良かったら一読してみて下さい。

Power Query と SQL Server の長所と短所

ここまで解説した Power Query と SQL Server の特徴をシンプルにまとめると図のようになります。

図を見るとお分かりのように、Power Query と SQL Server はそれぞれが短所とするところを補い合う関係にあるのです。

ですから、Power Query と SQL Server を上手く組み合わせることができれば強力な仕組みを構築できるのです。

データ基盤を構築

ではいよいよデータ基盤を構築していきます。とは言っても筆者のこれまでの多くの経験を踏まえて極力シンプルな方法になっているので、是非参考にして頂きたいと思います。

仕組みの全体像

次の項からデータ基盤の構築の手順を Step by Step で解説しますが、最初に仕組みの全体像を解説します。

この仕組みは図で表すと下の図のようになります。

手順1：Power Query でデータを取得する

Power Query は解説でも述べたように、様々なデータ形式、複数ファイルに対応しており、仕事で必要とされるデータのほとんどに対応しています。

そこで、まず Power Query で自動的にデータを取得できるよう、システムからダウンロードするデータや、大勢の人が入力する Excelファイルなど、取込みが必要なデータの保管場所を決めます。

決められたフォルダにファイルが保管されたら Power Query を使って一気にデータを読み込みます。

ここで後でデータを利用することを考慮して Power Query でデータを整えておきます。これは例えば次のような処理になります。

データを整える

不要なデータはフィルタリング機能で除外
数値列に紛れ込んだ文字列などエラーの原因の除去
縦軸に項目、横軸に日付など、縦横に広がったデータはピボット解除をして縦一列のデータに変換

Power Query の操作方法は 別記事 で勉強してみて下さい。

手順2：クエリに名前をつけて Excelシートに出力

Power Query でデータの取り込み、加工ができたらクエリに名前をつけて Excel シートにテーブルとして出力します。

Power Query の操作は下の図のようになります。

このクエリの名前を SQL Server のテーブルの名前として後で利用しますので、分かり易い名前にしておくと良いです。

ここで クエリの名前と SQL Server のテーブルの名前を一致させる ことで、VBAのプログラムをシンプルにすることができるのです。

重要：バックグラウンド更新の設定を外す

このファイルを閉じる前に一つだけ設定が必要です。Power Query のバックグラウンド更新の設定を外す必要があります。

これは以下の手順で行います。

メニューから「データ」＞「クエリと接続」をクリック
ウィンドウ右に表示されたサブウィンドウにある今回作成したクエリを右クリック
サブメニューから「プロパティ」をクリック
出てきたウィンドウの「バックグラウンドで更新する(G)」のチェックボックスを外す

これは後で設定するVBAマクロが正しく動作するために必要で、この設定をしないとデータの更新をしないまま SQL Server にデータを登録してしまうことになりますので、注意して下さい。

手順3： VBAマクロを追加

Power Query を設定した Excel ファイルに以下の VBAマクロのコードを追加します。

このVBAマクロは Excel のテーブル形式で出力された Power Query のデータを SQL Server にアップロードするプログラムです。

これにより、簡単に Power Query と SQL Server を連携させて利用することが可能になります。

操作手順は以下の通りです。

メニューから「開発」＞「Visual Basic」をクリックして Visual Basic の編集ウィンドウを開く
プロジェクトエクスプローラーの当該ファイルのアイコンを右クリック
サブメニューから「挿入」＞「標準モジュール」をクリックして標準モジュールを追加（図参照）
開いたコードウィンドウにしたのVBAプログラムをペースト（右上のコピーアイコンをクリックするとコード全体をコピーできます）
プログラム中のコメントの「データベースの設定」と「Excelの設定」の部分をコメントを参考に編集
最後に当該のExcelファイルを名前をつけて「Excel マクロ有効ブック(*.xlsm)」として保存

Excelテーブル SQL Server アップロード　VBAマクロ　プログラムコード

Sub InsertExcelTableToSqlServer(shtName As String, tableName As String)
    Dim conn As Object
    Dim rs As Object
    Dim sql As String
    Dim excelTable As ListObject
    Dim rowCounter As Long
    Dim colCounter As Long
    Dim maxBatchSize As Long
    Dim batchSize As Long
    Dim ServerName As String
    Dim DatabaseName As String

    ' ************** データベースの設定 *******************

    ServerName = "SERVERNAME\SQLEXPRESS"
    DatabaseName = "DMS"

    ' *****************************************************

    ' バッチサイズの設定
    maxBatchSize = 500

    ' Excelテーブルの取得

    Set excelTable = ThisWorkbook.Sheets(shtName).ListObjects(tableName)

    ' SQL Serverへの接続
    Set conn = CreateObject("ADODB.Connection")
    conn.ConnectionString = "Provider=SQLOLEDB;Data Source=" & ServerName & ";Initial Catalog=" & DatabaseName & ";Integrated Security=SSPI;"
    conn.Open

    ' DELETE文の実行
    conn.execute "delete from " & excelTable 

    ' バッチごとにINSERT文を生成して実行
    rowCounter = 1
    While rowCounter <= excelTable.ListRows.Count
        sql = "INSERT INTO " & tableName & " ("
        For colCounter = 1 To excelTable.ListColumns.Count
            sql = sql & "[" & excelTable.HeaderRowRange.Cells(1, colCounter).Value & "], "
        Next colCounter
        sql = Left(sql, Len(sql) - 2) & ") VALUES "

        batchSize = 0
        While rowCounter <= excelTable.ListRows.Count And batchSize < maxBatchSize
            sql = sql & "("
            For colCounter = 1 To excelTable.ListColumns.Count
                sql = sql & "'" & excelTable.DataBodyRange.Cells(rowCounter, colCounter).Value & "', "
            Next colCounter
            sql = Left(sql, Len(sql) - 2) & "), "
            batchSize = batchSize + 1
            rowCounter = rowCounter + 1
        Wend

        ' 最後の","を削除してSQL文を完成させる
        sql = Left(sql, Len(sql) - 2)

        ' INSERT文の実行
        conn.execute sql
    Wend

    ' 接続を閉じる
    conn.Close

 End Sub

Sub execute()

    '  ************** Excel の設定 *********************************
    '
    ' １つ目の引数： Power Query のテーブル出力のあるシート名
    ' ２つ目の引数： Power Query名（データベースのテーブル名と同じ）
    '
    ' **************************************************************

    Call InsertExcelTableToSqlServer("テーブル1", "売上データ")

End Sub

Power Query のファイルはデータの種類ごとに必要なだけ作成

以上で Power Query でのデータ取込みの部分の作成は完了です。

複数のデータに対応するために、この方法で Power Query 付きの Excel ファイルをデータの種類ごとに作成します。

ここはファイルをコピーして Power Query の部分だけを削除して追加するというやり方で楽に増やしていきましょう。

Power Query はマウス操作だけで簡単にデータを取り込むことができるので、苦労無く沢山のデータに対応した Power Query 付きの Excel ファイルを作成することができると思います。

おそらくこれまで Excel ファイルにコピー＆ペーストしていた全てのデータを Power Query で取り込むことができると思いますので、全部作りきってしまうと良いと思います。

ここで重要なのはデータ取込みの仕組みは一度作成してしまえば取込み作業が自動化され、今後は自分で操作することがほとんど無くなるということです。

これでこれまでにデータ取り込みにかけていた時間はほぼゼロにすることができるのです。

手順4： SQL Server 上にデータベースとテーブルを作成

Power Query で取得したデータは先ほどのVBAマクロを使って SQL Server に保管します。そうすることで Power Query が苦手としていた多くの人とのデータ共有が実現できるのです。

このために、SQL Server 上にデータベースとテーブルを作成します。

細かい操作方法は、以下の記事の「サーバーPCに SQL Server Express版をインストール&設定」の章で説明していますのでご参照下さい。

データベース作成

SQL Server はデータベースソフトなので、同じ名前で混乱を招いてしまいそうですが、SQL Server ではテーブルを作成する前に、一連のテーブルなどを保管するデータベースというフォルダのようなものを作成する必要があります。

SQL Server では複数のデータベースを分けて管理することで、全く違う用途のデータベースも適切に管理ができるようになっているのです。

データベースの名称は何でも良いので分かり易い名前をつけておけば良いでしょう。また、特別な設定も不要です。

テーブル作成

更にデータ保管用のテーブルを下記のルールで作成して下さい。このルールを守ることで VBAマクロのプログラムを編集することなく使うことができるのです。厳密なルールを設定することでプログラム編集の負担を無くす工夫をしています。

テーブル作成のルール

列の名称： Power Query の列の名称と同じ
列のデータ型：　Power Query の列のデータ型と同じ
テーブルの名称：　Power Query のクエリの名称と同じ

以上で SQL Server の設定は完了です。一度理解してしまえばあっけない程に簡単に思えると思います。

手順5：Excel ファイルの VBA マクロを自動実行するスクリプトを作成

続いて、作成した Excel ファイルの VBA マクロを自動実行するスクリプトを作成します。

以下のプログラムコードをメモ帳などで開いて、データ取込み.vbs などと名前をつけて VBScript のファイルとして保管します。

なお、プログラム中のコメントの部分を編集して、作成した Excel ファイルのパスに書き換えておいて下さい。

Excelファイルが複数ある場合は下のコードの例のようにカンマ区切りで並べて入力します。

このスクリプトのファイルをダブルクリックすると、次々と Excel ファイルを開いて SQL Server にデータを登録してくれます。

これで今まで手作業でやっていたデータの取り込み作業はワンクリックになってしまいました。

' データ取込み.vbs

Dim excelFiles, objExcel, objWorkbook

' ********** Power Query 付き Excel ファイルのパスを設定 **********

excelFiles = Array("C:\売上データ取込.xlsm", "C:\受注データ取込.xlsm", "C:\コストデータ取込.xlsm")

' *****************************************************************

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
For Each excelFilePath In excelFiles
    Set objWorkbook = objExcel.Workbooks.Open(excelFilePath)
    objExcel.Run "execute"
    objWorkbook.Close False
    Set objWorkbook = Nothing
Next
objExcel.Quit
Set objExcel = Nothing

手順6：　更に自動化を進めて自動実行にする

作成した VBScript は自分で実行しなくても自動で実行させることができます。これには Windows のタスクスケジューラを使います。

タスクスケジューラの設定

Windowsキー + R を押し、taskschd.msc と入力してタスクスケジューラを開く。
右側の基本タスクの作成をクリック。
任意の名前（例: SQL_Server_Data_Upload など）を入力して次へ。
トリガー（実行タイミング）を選択（例: 毎日やログオン時など。）
操作でプログラムの開始を選択。
プログラム/スクリプトに cscript を入力
引数の追加にスクリプトのパス（例：C:\データ取込み.vbs など）を入力

これでデータを取り込んでSQL Serverにデータをアップロードするところまで完全に全自動になってしまいました。

筆者はお昼休みに自分のPCでこのタスクを走らせることで毎日データを SQL Server に集めることができています。

お昼休みの後に BI 画面などを表示してチェックすれば最新のデータを見ることができるというワケです。

まとめ

以上が Power Query と SQL Server を活用して効率的にデータ基盤を構築する方法です。

普段から使っている Excel と無償で利用できる SQL Server だけで強力なデータ基盤を構築できるのですから、使わない手は無いと思います。

昔ならこのようなデータ基盤の構築はプロに頼まないと実現できなかったと思われますが、便利なツールの組み合わせでこれだけのことができてしまうのです。

SQL Server に蓄えられたデータは Power Query, Power Pivot, 更には Excel関数も組み合わせても色々と利用ができますので、このデータ基盤を様々な場面で活用してみて下さい。

データ活用については関連する記事も多く書いていますので、これらも参考にしてみて下さい。