Excelの基本機能にPowerQueryという機能があります
この機能は外部データ（内部も可能）をテーブルとして取り込む機能です
この機能では取り込みを行う際のフィルターや並べ替えなどを含め記録することができます
それを記録しておくことで、定期的に外部データを取り込む際に効率化を図ることができます
多くの場合の取り込みデータはcsvか別ブックのデータになると思います
この機能の制約としてはテーブルとして出力されるため、取り込み先も表形式のデータになっていることが重要になります
ある程度は操作によって調整することが可能ではありますが、同じルールで作成が続けられることが前提となります
この機能を有効的に利用するなら、取り込み元もきれいに整えておくことをおすすめします
ただしcsvであれば、データの形式はきれいにそろっているのでそこはあまり気にしなくても大丈夫です
別ブックのデータを取得する際に特に重要です
まずPowerQueryを利用するにはデータの取り込み作業が必要となります
その取り込みの際に利用するのが、PowerQueryです
上記の画像の手順ではcsvファイルを取り込む際の流れになっています
- リボンメニューから「データ」タブを選択
- 「データの取得」を選択
- 「ファイルから」を選択
- 「テキストまたはCSVから」を選択
⇒取り込みたいcsvファイルを選択してください
今回の記事では郵便番号一覧のCSVデータを利用しています
日本郵便株式会社 – 郵便番号データダウンロード
ファイルを選択すると、そのデータから自動的にデータが表形式に作成されてプレビュー表示されます
この画面の右下にある「読み込み」ボタンをクリックすると、新しいシート・テーブルが作成されてそこにデータが読み込まれます
約12万5千レコードが読み込まれた状態になっていると思います
このようにcsvファイルの取り込みは格段に簡単になっています
さらにcsvファイルでよくあるのが文字コードの不一致による文字化けです
これに関しては、このプレビュー画面の左上にある「元のファイル」という選択ボックスから文字コードを指定することで簡単に変更が可能です
プレビュー表示もされているので、正しい文字コードも選んでいけば分かります
ただここまでではPowerQueryの活用ではありません
この取り込んだデータの加工を、取り込む時点で加工するのがPowerQueryの機能となります
郵便番号一覧のデータでは郵便番号の列は全て数値になっていることが分かると思いますが、これは数字では無く決まった文字数のコードなので、どちらかというと文字列で取得をする方がデータに即しています
そこでPowerQueryを利用して郵便番号列は文字列に型を変更することが可能です
- テーブル範囲にアクティブセルを置いた状態で表示される「クエリ」タブを選択
- 「編集」を選択 - PowerQuery画面が表示される
- 変換したい列の見出しの左側にある個所をクリックすると型変換が行える
- 「テキスト」を選択
- 「現在のものを置換」を選択
上記の手順を行うと、こちらの画像の様にデータが文字列型に変換されます
数値となり除外されていた頭の0が付与されていることも確認できます
単純にExcelでcsvファイルを開いたときは、この0が無くなるのがよくある注意点でしたがPowerQueryで取り込みを行えば次回以降は意識する必要が無くなります
変換作業が完了したら、PowerQueryの画面の左上の「閉じて読み込む」の画像の箇所をクリックするとその設定で再度csvファイルのデータを読み込みます
この操作は記録されているので、次回以降はPowerQueryの画面を開く必要は無くデータの更新を行うだけでこの作業が実行されます
PowerQueryの注意点はファイルへのリンクは絶対パスを利用している点です
フォルダ構成やファイル名などが変更になった場合はリンク切れとなり、データの更新が出来なくなります
VBAのような相対パスの概念は無いので注意してください
ここは環境によっては少し不便かもしれませんが、パスの修正自体はそれほど難しくは無いので割り切っておいて大丈夫です
普通に数式で外部参照している場合も同様ですし、それらより圧倒的にメンテナンスしやすいです
型変換に関しては、他にもいろいろな型があるのでリストを確認しておいてください
それと同じくらい強力なのがフィルタ処理です
取り込んだ後に必要なデータにフィルタ機能を利用してフィルタをかける作業がある場合は、そのフィルタ条件も取り込みの時点で指定することが可能です
1列など、切り替えが多い場合は逆に必要ないかもしれませんが
複数列などで条件立てをする場合は強力です
フィルタの手順については、通常のフィルタ機能と大きな違いはありません
各列の見出しにある▼ボタンをクリックすることで、条件の設定が出来ます
特殊条件として便利なのが、この「空の削除」です
これを指定した列にある空白のレコードは除外されます
例えばコードが無いデータは不要なので、その列でこの設定をすることで不要なデータの取り込みを行わないようにできます
注意点としては、このPowerQueryの画面では全てのデータが読み込まれていないので
フィルタの選択項目がデータの全てになっていない可能性がある点です
また取り込み元のデータのリストも、この手順作成時点と同じであるかどうかも分からないのでなるべく条件設定は抽象的な方がメンテナンスは減らせます
上記の画像例では、市町村以下の列で「ｵｵｻｶｼ」が含まれたものを指定しています
こうすることでデータの取り込み量を減らすことが出来るのでファイルサイズを無駄に大きくせずに済みます
これはこの後に行う、関数や作業の際にファイルの動作に影響を与える部分なので
可能な限りこのPowerQueryでデータを減らすことをお勧めします
また、Microsoftの推奨としてPowerQueryのステップもレコードを減らすものをなるべく上位にすることがあります
ステップの順番は、作業を行った順番に登録されていきます
例えば、今回の記事解説順で行くと以下の順番になっています
- データの取り込み
- 郵便番号列の型をテキスト型に変更
- 市町村列で「ｵｵｻｶｼ」を含むものにフィルタ設定
この手順で言うと、レコードの数を減らすのは3の手順となります
このフィルタによって1,200レコードほどになりますので100分の1ほどになります
これを2の手順にすることで、型変換の処理を行うレコード数を減らすことで読み込み速度の向上が見込めます
ステップの順番はドラッグするだけで簡単に行えます
またステップの文字にマウスを移動させると、左側に×ボタンが表示されます
これをクリックすることでステップの削除も行えます
ステップの移動については、移動先との矛盾に注意が必要です
例えば今回の記事で行くと、上記の手順2で文字列に変更しています
その変更を前提として、郵便番号列で「011」で始まるものなどでフィルタ設定を行った場合にフィルタを移動させるとエラーとなります
なぜなら、型変換前は数値型のため「011」という文字列でフィルタ設定を行うことが出来ないためです
この場合にはエラーがちゃんと表示されるので、その場合は諦めるかステップを修正します
修正する場合は、ステップの文字列をwクリックすれば設定画面が表示されます
最後にPowerQueryとは直接の関係はありませんが、この機能を最大限活用するためにテーブル機能も活用します
と、いうのもテーブル機能というのは自動的に行が拡張されていきますが、その際に数式の入力された列の場合はその数式も自動的に拡張された行に反映されることを活用できます
実はPowerQueryで自動作成されたテーブルの最左列に追加を行っても読み込みに影響を与えません
これを利用します
このcsvファイルのデータ列数は15列となります
その左に住所の文字列を結合したセルを作成してみます
画像の様に単純に行の各列を&で結合しただけの関数です
テーブル機能のおかげでデータの取り込み数によって自動的に増減するので、関数の行数を調整する必要もありません
またデータの取り込みを完了した時点で関数の結果も計算されています
もし、これまでcsvファイルをテキストコピーして貼り付け-データの分割でデータを作成-フィルタ機能で条件抽出-関数を作成して行数分オートフィルする
というようなよくある手順を踏んでいた場合は、データの更新を行うだけ（ワンクリック）でこの作業が完了してしまいます
この様にPowerQueryとテーブル機能と関数の組み合わせで、データの取り込みから加工までを設定で行えるためVBAをこれのために組んでいるなら早々にこちらへの切り替えをおすすめします
VBAのメンテナンスより遥かに簡単ですから