PR

脱Excel DX by Excel のススメ【実践編】

2023.07.22

「脱Excel DX by Excel のススメ【コンセプト編】」では Excel を使いつつ、「データ駆動型」の仕組みを構築する考え方をご紹介しました。この記事では具体的にどのようにして「データ駆動型」の仕組みを構築するのか、実際に使われている工夫のアイディアも含めてご説明します。

仕組みの流れは次のようになります。

  1. Excelの入力フォームから Power Queryを使うなどして「データ」を抽出
  2. 「データ」をVBAマクロを使ってデータベースに登録
  3. 集めたデータを Power Query、Power Pivot を使って様々な形で集計

これらの全てのステップが Excel の自動化の機能を活用しているため、どれだけデータの量が多くなっても「残業してデータ処理する」なんてことは不要で、クリックして自動処理が終わるのを待つだけでOKという仕組みになります。

従来であれば、「データを集める」、「データを加工する」、「データを集計する」という一連の作業が、データ量に比例して増大していたはずです。これが最新のExcelの機能をフル活用することで言葉通り瞬時に終わらせることができるようになります。

では、早速その仕組み作りのノウハウを見ていきましょう。

旧Excel入力フォームの手直し

旧Excel入力フォームの問題点は人間には読み易いものの、コンピュータによる自動処理に向いていないことです。

今回は入力された情報を「データ形式」にすることで一括処理が出来るよう工夫します。これにより集めてきたExcel入力フォームのファイルを人の手をかけずに自動処理することができるようになります。

このことはとても重要で、たとえばファイルか2,3個の場合なら手作業でもできますが、これが10個を超えてきたらもう面倒になるのは想像がつくと思います。ちなみに自動処理ならファイルが100個あっても全く問題になりません。

自動化の真価はこのスケーラビリティです。一度仕組みを作ればばどれだけ量が増えても問題なく処理できるのが素晴らしい点です。

さで、ではこの自動処理に持ち込むにはどうしたら良いのでしょうか？

Excel入力フォームのパターンによって対応方法も変わりますので、パターン毎に対応のアイデアを紹介します。

申込み用紙形式のケース

このケースでは記入項目が全て異なり、一通り記入すると一件のデータになります。

しかし、記入データはあちこちのセルに点在してそのままでは自動処理には向きません。

そこで別シートにExcelのテーブルを作成して、図のようにフォームの値をリンクさせてデータをテーブルに落とし込みます。

このテーブルを Power Query や VBA で読み取るのです。

こうすることで記入フォームのファイルが幾つあっても同じフォルダに保管しておけば VBA で一括して読み込むことができます。

　縦横に展開された表のケース

良くあるのが横に時間軸を展開した表形式の記入フォームです。

データベースで扱うデータは縦に綺麗に並んだデータのため、このままではデータベースに落とし込むことはできません。

Power Queryにはこの縦横変換をするピボット解除という機能があります。これを活用します。

Power Queryの処理を加えることでデータベースで扱うデータの形式にすることができます。

　「データ形式」にした後は・・・

ここまでで、旧Excelフォームに記入されたデータをちょっとした工夫や、Power Queryの処理により自動処理に有利な形式の「データ」に変換することができました。

この「データ」を 誰もが・最新の・正式な データとしてアクセスできるようデータベースで管理することにします。

次の章で、本格データベースである SQL Server のインストールからデータベースの作成までを解説します。

SQL Server データベースの構築

　SQL Serverのインストール

“SQL Server Express” のキーワードで検索するとMicrosoftのダウンロードサイトが1番に出てくると思いますので、そこでインストーラをダウンロードします。

インストーラを実行して特に特別な設定をせずにインストールします。悩むところは無いと思います。

SQL Server Management Studioのインストール

SQL Serverをインストールしただけではデータベースの作成や設定などの管理が出来ませんので、管理ツールである SQL Server Management Studio をインストールします。

“SQL Server Management Studio” のキーワードで検索して、Microsoftのサイトに行きます。ページの最初に出てくるダウンロードリンクをクリックしてしまうと、英語版がダウンロードされてしまいますので、図のように日本語版のリンクが出てくるまでスクロールしてクリックしてダウンロードします。

あとはそのままインストーラを実行してインストールすれば完了です。

データベースの作成

いよいよデータベースの構築作業に入ります。SQL Server Management Studioを起動します。

起動すると図のようにSQL Serverとの接続設定のウィンドウが出ると思います。このウィンドウが出てない場合は、メニューから ファイル＞接続 でウィンドウを出します。

接続先の設定は SQL Server をインストールしたコンピュータの名前の後ろに “\SQLEXPRESS” をつけて下さい。無事接続できると一番上にサーバ名が書かれたデータベースのアイコンが表示されます。

続いて、図のようにデータベースのフォルダアイコンを右クリックして、出たメニューから「新しいデータベース」を選択します。

データベース名を “TEST” など適当な名前をつけて「OK」ボタンを押してデータベースを作成します。

データベースを作成したら、いよいよデータを格納するテーブルを作成します。テーブルは入力フォームに合わせて必要な項目（列、フィールド）を全て作成します。

基本的に入力フォームの項目の順番に合わせてそのまま同じようにテーブルを作成すると分かりやすいです。

【重要！】「データの住所」を設定する

ここまででユーザーが入力したデータを自動処理で扱いやすい「データ形式」に変換し、この「データ」を格納するデータベースを作成してきました。

しかし、ここで一工夫が必要です。データはやみくもにデータベースに登録してしまうと、同じデータが幾つも登録されてしまいます。ですから、登録するデータには「データの住所」を与えてそこには必ず１組のデータだけを登録するようにしなくてはなりません。

ではどのように「データの住所」を割り振れば良いでしょうか？それは集めてくるデータの内容から判断できます。

例えば社員それぞれが何かの申請フォームを提出してくるのであれば、「社員番号」が良いと思われます。部署毎に何かの数値報告をするのであれば、「部署名」が良いことが分かります。

これらに「日付」も加えておくとより管理し易いものになります。例えば「社員番号YYYYMMDD」のようにアンダースコア「」で繋いだものを「データの住所」とするのです。

設定した「データの住所」をここでは「Data_ID」として説明していきます。

VBAでファイルのデータをデータベースに登録する

VBAによるプログラミングはハードルが比較的高く、なるべくなら避けたいところですがデータベースにデータを登録する機能は Excel関数 や Power Query には無く、VBAが唯一の選択肢となるため、やむを得ずこれを選択することにします。

しかし、労力を極力省くと共により多くの方にプログラムの内容をご理解頂けるように、Chat GPTにVBAのコードを書かせてみました。まず、Chat GPTに出した指示を以下に紹介します。

以下の仕様のExcel VBAのコードを書いて。

概要：ファイル選択ダイアログでユーザが選択したファイルの「テーブル1」の内容をSQL Serverの [tableA] にINSERTするプログラム
仕様：

  1. SQL Server のインスタンス名は \ComputerA\SQLEXPRESS
  2. SQL Server には Windows認証で接続
  3. 「テーブル1」 の各フィールドに加えて 「Data_ID」 のフィールドを加えたものが [tableA] のフィールドとして設定済み
  4. このVBAマクロが登録されているファイルの Sheet1 Range(“A1”) セルに 「Data_ID」の値が記入されている
  5. SQL Server にデータを INSERT する前に [tableA] から同じ 「Data_ID」 を持つデータを削除する
  6. SQLのINSERT文の生成の際に、列数や列名は 「テーブル1」を読み取り、「テーブル1」や[tableA] がどのような内容となっていても動的に対応できるようにする

この指示でChat GPTが作成したコードが以下です。実際にこれでうまく動作しました。たった11行の指示で209行のプログラムをバグ無しで作成してくれるところがさすがChat GPTですね。

Option Explicit

Sub InsertDataToSqlServer()
    On Error GoTo ErrorHandler

    ' SQL Serverの接続情報
    Dim connectionString As String
    Dim serverName As String
    Dim databaseName As String
    serverName = "\\ComputerA\SQLEXPRESS"
    databaseName = "YourDatabaseName" ' データベース名を設定してください
    connectionString = "Provider=SQLOLEDB;Data Source=" & serverName & ";Initial Catalog=" & databaseName & ";Integrated Security=SSPI;"

    ' ファイル選択ダイアログの表示
    Dim fileDialog As FileDialog
    Dim selectedFilePath As String
    Set fileDialog = Application.FileDialog(msoFileDialogFilePicker)
    With fileDialog
        .Title = "テーブル1を含むファイルを選択してください"
        .AllowMultiSelect = False
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xls; *.xlsx; *.xlsm", 1
        If .Show = -1 Then
            selectedFilePath = .SelectedItems(1)
        Else
            Exit Sub ' ファイルが選択されなかった場合、処理を中止
        End If
    End With

    ' Excelデータの取得
    Dim excelApp As Object
    Dim excelWorkbook As Object
    Dim excelSheet As Object
    Set excelApp = CreateObject("Excel.Application")
    Set excelWorkbook = excelApp.Workbooks.Open(selectedFilePath)
    Set excelSheet = excelWorkbook.Worksheets("テーブル1")

    ' データの挿入用SQL文の生成
    Dim insertSQL As String
    Dim columnNames As String
    Dim columnValues As String
    Dim lastColumn As Long
    Dim i As Long
    lastColumn = excelSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    columnNames = "Data_ID, "
    columnValues = "'" & excelSheet.Range("A1").Value & "', "
    For i = 2 To lastColumn
        columnNames = columnNames & excelSheet.Cells(1, i).Value & ", "
        columnValues = columnValues & "'" & excelSheet.Cells(2, i).Value & "', "
    Next i
    columnNames = Left(columnNames, Len(columnNames) - 2) ' 末尾のカンマを除去
    columnValues = Left(columnValues, Len(columnValues) - 2) ' 末尾のカンマを除去
    insertSQL = "INSERT INTO [tableA] (" & columnNames & ") VALUES (" & columnValues & ");"

    ' 既存のデータ削除用SQL文の生成
    Dim deleteSQL As String
    deleteSQL = "DELETE FROM [tableA] WHERE Data_ID='" & excelSheet.Range("A1").Value & "';"

    ' SQL Serverへの接続とデータの挿入・削除の実行
    Dim connection As Object
    Set connection = CreateObject("ADODB.Connection")
    connection.ConnectionString = connectionString
    connection.Open
    connection.Execute deleteSQL ' 既存のデータ削除
    connection.Execute insertSQL ' データの挿入
    connection.Close

    ' オブジェクトの解放とExcel終了
    Set connection = Nothing
    Set excelSheet = Nothing
    excelWorkbook.Close SaveChanges:=False
    excelApp.Quit
    Set excelApp = Nothing

    MsgBox "データの挿入が完了しました。", vbInformation

    Exit Sub

ErrorHandler:
    MsgBox "エラーが発生しました。" & vbCrLf & Err.Description, vbExclamation
End Sub

このVBAマクロを実行する前に Sheet1 の A1 のセルに先に決めた 「Data_ID」 の値を記入します。前の説明の通り、例えば「営業部_20230401」のような文字列を入力しておきましょう。

続いてVBAマクロを実行すると、ファイル選択ダイアログが開きますので、Excel入力フォームのファイルを選択して「開く」ボタンを押します。

するとVBAマクロが SQL Server にデータを登録します。この際、既に 「Data_ID」 として「営業部_20230401」 のデータがある場合は一旦削除してから登録します。こうすることでデータが重複して登録されることを防ぎます。

Excel Power Query、Power Pivot によるデータ集計

データベースにデータが登録されたら後は Excel の自動処理により自在に集計が可能です。具体的には次のようにして処理します。

  1. Power Query でデータベースに接続してテーブルデータを取得
  2. Power Query の接続を「データモデル」にして Power Pivot の処理へ繋ぐ
  3. Power Pivot で各種集計処理を行う
  4. その他の活用として、Power Query の出力テーブルを SUMIFS 関数などで集計する（定型の集計に便利）

このようにしておくと、各ユーザーがデータを更新して登録した場合でも、このファイルを開いてメニューからデータ＞すべて更新 とするだけで全自動で集計値が更新されます。

まとめ

この記事ではユーザーが入力する Excel入力フォームを工夫して整形された「データ」を含むようにすることでデータベースへの登録を容易にし、VBAの簡単なプログラムでデータベースにデータを登録、最後に Excel の新しい機能である Power Query、Power Pivot による自動集計までを行いました。

全てのプロセスが自動化されているため、相当な業務効率が図れます。これにより、従来はデータ集計までで終わっていた業務を更に進めることができ、より深いことまでできるようになると思います。

是非、これを参考にそれぞれの業務の「DX改革」に取り組んでみて下さい。

タイトルとURLをコピーしました