パワー クエリ。 Power Queryで複数のExcelブックの共通しない名称のシートを読み込む

ExcelでPowerQueryを使ってデータ収集分析

パワー クエリ

はじめに Excelにデータを集めようとするとき、数式とVBAを駆使して行う方法が一般的です。 キーを使って複数のデータを結合するときには、Accessなども用いられます。 ですが、データが増えてくると下記のような課題が発生してきます。 収集に必要な手順が増え、数式やVBAが複雑化する• データ収集に長い時間がかかる• サイズが巨大化して格納しきれなくなる これらの課題を解消するツールとして PowerQuery を紹介します。 PowerQueryとは 2016年に公開されたMicrosoft製のデータ分析用Excelアドインであり、Excel2010以降に対応しています。 ファイル・DB・Webサービス等からデータを読み込み、変換・加工してExcelのシートに出力できます。 VBAのマクロの記録と同じように、画面上で操作するとクエリ(読み込み手順の定義)が生成されます。 複雑な加工をしない限りプログラミングは必要ありません。 Excel内に全ての情報を格納するため、一つのファイルで完結し他のツールは必要ありません。 PowerQueryの入っていない端末では普通のExcelファイルとして閲覧することができます。 中間処理は全てメモリ上で行われ、処理が非常に高速であることが特徴です。 ファイルに出力されるのは最終の出力結果のみであるため、ファイルサイズも少なく済みます。 PowerQueryのインストール Excel2010,Excel2013であれば、下記からダウンロードしてインストールします。 Excel2016からは標準機能となっているのでインストールの必要はありません。 PowerQueryではなく 取得と変換 と名付けられていますが、同じ機能です。 PowerQueryの起動 Excel2010, Excel2013 PowerQueryタブをクリックします。 Excel2016 取得と変換から新しいクエリをクリックします。 Office365 を利用している場合、最新版ではされています。 PowerQueryを使ってみる PowerQueryを使ってデータを読み込むサンプルです。 単純な読み込み処理で加工・変換は行っていませんが、その分手順の少なさがわかります。 フォルダ内のファイルを一覧にする 複数のフォルダに分散したファイルを一覧にしていきます。 手順 フォルダーから をクリックします。 取得するフォルダーパスを指定します。 読み込み をクリックします。 ファイルの一覧が読み込まれます。 読み込んだデータを更新する 読み込んだデータを最新化するには、 すべて更新 をクリックします。 新しく追加されたファイルが読み込まれました。 データの更新はPowerQueryが入ったExcelであれば、どの端末であっても実施できます。 複数のExcelファイルを結合する 複数のファイルを結合して読み込みます。 手順 フォルダーから をクリックします。 取得するフォルダーパスを指定します。 結合および読み込み をクリックします。 読み込む対象ファイルの例が表示されるので、OKをクリックします。 Excelに結合したデータの一覧が読み込まれます。 Webサービスから読み込む Webサービスから読み込みます。 サンプルに用いているのはです。 手順 Webから をクリックします。 APIのURLを指定します。 テーブルへの変換 をクリックします。 JSONはPowerQuery内でリストとして取り込まれ、テーブルに変換することで加工可能になります。 必要に応じて区切り記号など選択しOKをクリックします。 テーブル形式に変換されるので、列タイトルにある展開ボタンをクリックします。 テーブルが展開されたら、閉じて読み込むをクリックします。 ExcelにAPIから取得したデータが読み込まれます。 その他の機能について PowerQueryは読み込んだデータを変換・加工することで真価を発揮します。 Excelの数式で実現できることは一通りPowerQueryでも実装されており、 クエリーエディタ上で手順をプレビューしながら変換・加工していくことができます。 PowerQueryでできることの例 下記は一例ですが、画面操作だけで他にも様々な処理を定義することができます。 必要な行・列のみを取得する• 値を加工して出力する(LEFT,MID,RIGHT関数に相当• 特定の条件に該当する行にマークをつける IF関数に相当)• 複数の行をグループ化して集計する• 複数のクエリを参照して新しいクエリを作成する• IDを使って複数のクエリを結合する(VLOOKUP関数に相当)• 内部結合、外部結合などSQLで実現できる一通りの結合処理が行えます• に詳しい手順を記載しています。 特定の加工をまとめて実施するカスタム関数を定義する• ファイルパスなどの変化する値をパラメータ化する• データソースにセキュリティをかけ、アクセス制限を行う また、PowerQueryで作成したクエリはM言語 PowerQueryFormulaLanguage のプログラムとして出力されます。 GUI上にない処理であっても、M言語を駆使すると自由にクエリを組み立てられるようになります。 クエリを編集するには 詳細エディター を開いてください。 下記は Webサービスから読み込む で出力されたプログラムです。 役に立つリンク 基本的な情報 Microsoftの公式サイトです。 日本語訳がわかりにくいこともありますが、情報が豊富に載っています。 PowerQueryに関する情報が日本語で書かれています。 解説もアニメーションGIFを交えて丁寧に行われていてわかりやすいです。 M言語の情報 日本語で書かれたM言語の関数およびデータ型のリファレンスです。 必要な関数を検索するときに使っています。 日本語訳はさんが実施されています。 Microsoftの公式関数リファレンスです。 日本語訳がなくExampleが少ないのが難点ですが、全ての関数を網羅しています。 M言語のサンプルが豊富に載っています。 GUIだけでは実現できない高度なデータ収集手順を作成するのに役に立ちます。 M言語のカスタム関数集です。 標準の関数で実現できない処理があればここを探してみています。

次の

Power Queryで複数のExcelブックの共通しない名称のシートを読み込む

パワー クエリ

最近、世界一簡単シリーズをよく書いていますが、今回は、これからのExcelの基本機能になるかもしれないPower Queryってどんなものかを解説したいと思います。 今までのExcelのデータは、シートに入力されていました。 しかし、イマドキのExcelデータは元データがExcelシートに存在しなくていいのです。 先日、CSVデータの取り込みについての記事を書きましたが、それと同じ操作をしてデータを取り込んでみましょう。 こちらの記事から Power Queryって 取り込むと、画面の右にウィンドウが出てきます。 〇行読み込まれましたとか書いてありますね。 これをダブルクリックしてください。 すると、こんなExcelみたいな画面が出てきます。 これがPower Queryです。 これって何をするものかというと、Excelに取り込むデータを加工して、Excelにデータを受け渡すものです。 うーん、実際に操作してみた方がわかるので、やってみましょう。 Power Queryの操作 今、日付とその内容の2項目があるのですが、例えば曜日分析する時って、Excelで処理する時ってWEEKDAY関数とかTEXT関数で曜日特有の値を取り出すような面倒な作業の必要があるじゃないですか。 でも、元データが取り込まれた時点で曜日に分かれるなにかが付いているといいと思いませんか。 曜日データを追加してみます。 列の追加をクリックします。 日付の項目を選択して、例から列をクリックします。 右の方に列が出てくるので、この右に現れた列をダブルクリックします。 ダブルクリックをしても何も出ない時は1文字入力してからその文字を消します。 この中から曜日のデータをセットします。 OKボタンをクリックすると曜日の列が追加されるので、そのまま閉じます。 変更は保持します。 するとExcel画面にも曜日の列が追加されています。 同じように年、月、日などを入れることができます。 Power Queryのメリット でも正直、従来の関数を使ってデータを細分化してもいいじゃないですか。 って考えるとPower Queryの意味、ないですよね。 じゃあ、Power Queryってなんのためにあるのでしょう。 一つは、関数の知識がなくても元データを加工してしまって、Excelに集計だけを任せられる、つまりピボットテーブルさえあれば関数の知識はいらなくなることです。 もう一つはもっと大きい理由で、Excelって100万件ちょっと以上のデータって扱えないのです。 でも全国規模のネットショップの売上データや、Twitterのつぶやきデータなんて1日で100万件なんていとも簡単に突破されちゃうんです。 Excelで処理するデータって、大元のデータはとんでもなく大きくても、その一部しか使わないのです。 だから、大きなデータの中からExcelの集計に使うデータだけをPower Queryで取り出してからExcelで集計すればいいのです。 Power Query自体は無制限に(コンピュータシステムが許す限り)大きなデータを扱えます。

次の

パワークエリで2行データを1行データに変換する方法|とある会計士のひとりごと。

パワー クエリ

スポンサーリンク Power Query パワークエリ を利用したい状況 ・勤怠管理システムを7月より新しくした。 ・4月~3月の勤怠実績で勤怠管理をする必要があるため、旧システムの4月~6月勤怠データを新システムへ登録する必要がある。 ・旧システムから吐き出せる日時勤怠データはPDFのみで、それをExcelにするしかない。 取り込みたいExcelブックを選択します。 接続されていますのメッセージが表示されます。 取り込みたいシートを選択 ナビゲーターウィンドウが開くので、「複数のアイテムを選択」にチェックを入れる。 シート全部にチェックを入れます。 「データの変換」をクリック。 「データを読み込んでいます」とメッセージが表示されます。 シート数によっては読み込みに時間を要します。 読み込みが完了しました。 「追加」のウィンドウが表示されます。 「3つ以上のテーブル」にチェックを入れます。 「利用可能なテーブル」にシートが表示されますので、すべてのシートを「追加するテーブル」に追加します。 シートが取り込まれました。 4つのシートが縦に並んだ状態で取り込まれています。 ここから「100181」のみを取り出したい。 区切り文字による列の分割ウィンドウが開きますので、区切り文字選択のコンボボックスから「スペース」を選択します。 「区切り文字の出現ごと」を選択し、「OK」をクリックします。 1列目が分割され、2行目に社員番号のみ抽出されたました。 右の適用したステップに「区切り文字による列の分割」が追加されました。 列が削除され、適用したステップに「削除された列」が追加されました。 他にも不要な列を削除していきましょう。 列の並べ替える 列を見やすくするために列を移動しましょう。 列を選択した状態で(複数の列をまとめて移動可能) ドラック&ドロップで移動します。 適用したステップに「並べ替えられた列」が追加されました。 列にある不要なデータを削除する 今回は時間の前にある「当」という文字が不要です。 値の置換ウィンドウが表示されます。 「検索する値」に『当』 「置換後」は空(何も入力しない) で「OK」をクリックします。 『当』が消えました。 勤務区分を設定する 勤務区分は5種類「01出勤」「02有給休暇」「03午前半休」「04午後半休」「05休日出勤」があります。 旧データの複数の列データの条件により設定します。 条件列の追加ウィンドウが開きます。 条件列はその名の通り、列データの値によって、新しく作成する列にどのような値になるか設定できます。 列名 演算子 値 出力 column17 指定の値に等しい 有AM 02午前半休 列名の値【column】に対して、【指定した値【有AM】と等しい】場合、新しい列に【02午前半休】と入力するという式になります。 他の条件式も追加していきます。 列名 演算子 値 出力 条件1 column17 指定の値に等しい 有AM 03午前半休 条件2 column18 指定の値に等しい 有PM 04午後半休 条件3 column19 指定の値に等しい 年休 02有給休暇 条件4 column8 指定の値を含む : 01出勤 条件5 column5 指定の値に等しい 休日 05休日出勤 どれにも当てはまらない null 出勤がある場合(打刻時間が入っている場合)を第1条件にしようと思いましたが、「03午前半休」「04午後半休」の場合も打刻時間があるので、条件4にしました。 新しい列ができ、適用したステップに「追加された条件列」が表示されました。 勤務設定を設定する 勤務設定は2種類「01日勤」「02A勤務」 前で設定した勤務区分に比べると一つの列データのみで完結するので楽です。 column7 指定の値に等しい 日勤 01出勤 column7 指定の値に等しい A出勤 02A出勤 新しい列ができ、適用したステップに「追加された条件列」が表示されました。 カレンダーを設定する カレンダーは「01通常」「02振出」「03振休」の3種類。 いくつかの列データの値により設定します。 列名 演算子 値 出力 条件1 column5 指定の値に等しい 振出 02振出 条件2 column19 指定の値に等しい 振休 03振休 どれにも当てはまらない 01通常 新しい列ができ、適用したステップに「追加された条件列」が表示されました。 しかし、きちんと設定されているかを確認したところ、「振休」である場合に「03振休」としたいのになっていないケースがありました。 原因は条件の値である「振休」の後ろにスペースが入って「振休 」となっていたのが原因であるようでした。 半角スペースを削除する 値の置換メニューを使って半角スペースを削除します。 条件式を設定したよりも前に設定するため、適用したステップに並んでいる式のうち、カレンダー設定の条件式よりも前に追加しましょう。 ステップの挿入のメッセージウィンドウが開きました。 ステップを挿入すると後のステップに影響を与える場合がありますが、とりあえず気にせず「挿入」をクリックします。 「検索する値」に『 (半角スペース)』 「置換後」は空(何も入力しない) で「OK」をクリックします。 「振休」の後ろにあった半角スペースが消えたため、条件式に一致したため、「03振休」になりました。 不要な列を削除 条件列の追加で利用した情報が入っていた列ですが、お役目を終えたので削除します。 ヘッダー名を変更する データを取り込む際に新システムの仕様に合うようにヘッダー名を変更しました。 ヘッダー名を変更するには、ヘッダーの行をダブルクリックすると編集状態になるので変更が可能になります。 1つ目は「開始」で時間が入ってない空欄と元データのタイトル名であった「出勤」のチェックを外しました。 消えました。 2つ目に年月日の列のフィルター機能で、null データが入っていないの意味)のチェックを外しました。 消えました。 これで一通りのデータ生成処理の登録が完了しました。 データが完成しました! まとめ ExcelのPower Query パワークエリ で複数シートを1つのシートにまとめて新しいデータを作成する方法について紹介しました。 データの構造さえ理解できれば、操作方法自体は大変ではありません。 ぜひ試してみてください! こちらの書籍でPower Queryについて丁寧に紹介されており、勉強させていただきました。 おすすめExcel本(知り合いの羽毛田さんが書いた本です).

次の