はじめに
はじめまして。プロダクト開発統括部 第一グループの平田です。
突然ですが、皆さんは業務の中で手元のExcelファイルに別のところからデータを持ってきて、集計作業などをする機会はありますか。その作業、もしかしたらPower Queryを利用すると少なくともデータの取得、整形は一発でできるようになるかもしれません。
今回はExcelに備わっている機能の一つであるPower Queryの使用例を紹介したいと思います。
Power Queryとは
簡単に説明すると外部からデータを取得し必要な形に整形することが可能な、Excelに備わっているツールの一つです。
このツールの利点はデータの取得から整形までの操作を一括で実行することが可能なところにあります。この機能により、データの準備の部分の作業を大幅に効率化することができます。
Power Queryを使おうと思ったきっかけ
業務上でSharePoint上のリストのデータを利用する機会があり、手順の一部を自動化したいなと思ったのがきっかけです。
プロダクト開発では備品の情報をSharePoint上のリストに登録して管理しています。登録する備品の情報としては、例えばPCだったらOSやメモリサイズ、メーカー名などです。そしてどの備品にも一意の番号が割り当てられています。新規で備品を追加する場合はこの番号も考えなければなりません。
備品の購入が発生してリストに新規で情報登録する時この番号を割り当てるのが地味に手間でした。番号には特定の桁に分類ごとの連番が含まれており、 分類の桁を決定するのはそれほど手間ではありません。しかし、そのあとの連番の桁を決めるのにリストのフィルター機能などを操作して同じ分類の組み合わせで最大の連番が割り当てられている物品を探す必要がありました。
サイト上のリストのフィルターを操作しながら目的の値を探すのはあまりにも面倒なので、リストのエクスポート機能を利用してExcelファイルとして取り出し、そこで検索したりもしてみました。それでも、リストごとにいちいちExcelファイルをダウンロードするのは面倒だと感じていました。
そこでSharePoint上のリストを簡単にExcelに取り込める方法はないかと調べたところPower Queryを見つけました。
今回紹介する使用例
SharePoint上にあるリストのデータを取得し、文字列形式で表された数字の列から特定の桁を取り出し、数値型に変換する整形する例を紹介します。
リストデータの取得
まずExcelのデータタブから「データの取得」の詳細を開きます。選択肢の「オンラインサービスから(E)」の中で「SharePoint OnlineListから(L)」を選びます。
以下の様なウィンドウが開くので「サイトURL」の項目に取得するリストが存在しているSharePointのURLを指定し、OKボタンを押します。
※ここで認証情報が求められる場合はSharePointにアクセス可能な認証情報を入力してください。
指定した「サイトURL」の下に存在するリストの一覧が表示されるため、取得したリストを選び「読み込み」のボタンを押します。
新しくシートが作られ、指定したリストからデータを取り込んだテーブルが作成されます。
取り込んだデータの整形
複数の列を取り込んだ状態から必要な列だけを取り出し、データを整形する例です。一つだけ列を残し、その列にある文字列で表された数字から特定の桁を抜き出し、数値型に変換する例を紹介します。
1.取得対象となる列の絞り込み
取り込んだデータのテーブルの任意のセルを選択するとExcelのタブに「クエリ」が表示されるので、選択して「編集」の項目を押します。
Power Queryエディター画面が開きます。
クエリエディター上で残したい列の列名を選択し「他の列の削除」を押すと一発で選択した列だけ残ります。
2.列データの加工
文字列形式の数字の列から任意の位置で任意の桁を取り出すための操作例を紹介します。 今回は以下のようなデータから数値を取り出す前提で説明していきます。
Power Queryエディターの「変換」タブ→「抽出」→「範囲」を選択します。
「テキスト範囲を抽出する」というウィンドウが表示されるので抽出の開始位置と抽出する文字数を入力します。
左から六文字以降にある四文字が抜き出され、列の内容は以下のようになります。
列名の横にある「ABC」を押し整数を選択すると・・・
列の内容が数字に変換されます。
ここまで操作したらPower Queryエディターを「ホーム」タブ→「閉じて読みこむ」を選択し閉じます。
Excelの方のテーブルへも内容が反映されます。
データの更新方法
データを更新したいテーブルのどこかを選択した状態で「クエリ」タブの「更新」を押すとテーブルの情報が更新されます。更新の際はPower Queryエディターでの操作が適用されるため最初から整形されたデータのテーブル内容になります。
また、以下の設定をすることでエクセルのファイルを開いたときにテーブルの内容を自動的に更新することもできます。
まとめ
いかがだったでしょうか。今回はExcelに備わっているツールの一つであるPower Queryを使用してSharePoint上にあるリストのデータを取得し、整形する処理の一例を紹介しました。うまく利用できれば、Excelの資料作成のために必要なデータを持ってくる作業を更新ボタン一つで容易にすることも可能だと思います。
今回紹介した以外にも、データを取り込む先であれば他のExcelやSharePoint以外のWEB上のデータ、PDFファイルなどからでも取得可能です。
取得可能なデータソースに関して詳しく知りたい方は「Excel バージョンの Power Query データ ソース」をご確認ください。
また、データの整形機能についてもまだまだ紹介していない機能がたくさんありますので実際にいろいろと試してみるのもおすすめです。
ここまで読んでいただきありがとうございました！
お知らせ
ecbeingでは新進気鋭なエンジニアを募集しております！ careers.ecbeing.tech
