パワー クエリ。 Excelでピボットテーブル使う人に全力でPowerQuery(パワークエリ)をお勧めしたい

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

パワー クエリ

1行目と2行目のテーブルに分割するために、 行数ごとのインデックスを付番すること。 1行目データテーブルと2行目データテーブルを結合(マージ)するための キー列を決めること。 1行目と2行目のデータに分割する データの取り込み まずはデータタブの「テーブルまたは範囲から」をクリックします。 そして取り込む範囲を指定し、OKを押し、 パワークエリの編集画面が開いて取込ができればOKです。 行数ごとのインデックス列の追加 次に、データごとに1行目には1を、2行目には2となる条件列を追加します。 列の追加タブの「例からの列」を選択します。 この機能では列のサンプルを入力することで必要な関数を作ってくれます。 例えば、1行目に1、2行目に2と入力してENTERキーを押すと、他の列も埋まります。 今回の例では1行目がnullってなるので、これは修正しないといけません。 ちなみに、表示タブの「数式バー」にチェックを入れると数式バーを表示できます。 そして数式バーの数式を、このように修正します。 前ステップ(変更された型)に対して、「行数」列を追加し、追加した列には、「列2」(伝票No)が空欄なら2を、それ以外は1をセットする。 (参考) Table. AddColumn:列の追加 if else 1 Then 2 else 3 : (1)の条件を満たすときは(2)を、満たさないときは(3)をセットする。 数式を修正すると、すべての行に行数インデックスを付番できました。 伝票Noをフィルで下方向にコピー ここでは、伝票Noを下方向にコピーし、すべてのデータに伝票Noが入るようにします。 列2を選択し(又は列2のどこかを選択)、変換タブのフィルボタンを押し、下方向を選択します。 下方向にフィルされ、空欄(null)がすべて埋まりました。 一見意味のなさそうなプロセスですが、この伝票Noが1行目テーブルと2行目テーブルを結合するキーとなるため、実は重要なプロセスなのです。 1行目テーブルと2行目テーブルの作成 ここまでできればいよいよテーブルを1行目テーブルと2行目テーブルに分けていきます。 手順は簡単です。 まず、このマル印 「>」をクリックし、クエリフィールドを開きます。 次に、テーブル2を右クリックし、「複製」を選択します。 ついでに名前も変えちゃいましょう。 同じように、右クリックで 「名前の変更」を選択して変えます。 次に、1行目テーブルを選択して行数列を1でフィルタします。 2行目テーブルも同じように、行数列を2でフィルタします。 これで、1行目テーブル、2行目テーブルが完成です。 分割したテーブルの結合 結合用クエリの作成 では、前段で作った1行目データと2行目データをくっつけていきましょう。 まずクエリフィールドで右クリックし、 「新しいクエリ」「結合」「新規としてクエリをマージ」と進んでいきます。 マージの画面が開くので、テーブルには 1行目・2行目を、照合列には 「列2」(伝票No)を選択します。 (マージ:「1行目」テーブルと「2行目」テーブルを、照合列「列2」をキーに結合する) これでOKすると・・・ 1行目テーブルに2行目テーブルがくっつきました。 結合データの整形 次に、くっつけてテーブルを展開・整形していきましょう。 まずは展開する項目をチェックします。 これでOKを押します。 このように2行目テーブルの項目が横に展開できました。 あとは、1行目をヘッダーに変換したり、 好きな並びに並び替えたりして整形し、読み込んだら完成です。 読み込むとこんな感じになりました。 ちなみに読込む際には、すべてのテーブルを「接続のみ」にし、データモデルに追加しておくことをお勧めします。 そうしておけばデータ容量節約しつつピボットテーブル等で分析できますので。 設定はExcelシートのクエリと接続フィールドからクエリを選択、右クリックして「読み込み先」を選択し、「接続の作成のみ」を選択して「このデータをデータモデルに追加する」にチェックを入れます。 そうすれば、シートにデータを貯めることなくピボットテーブル等で分析に使えます。 まとめ いかがだったでしょうか。 複数行データ(2行データ)を1行データのデータベース形式に変換する方法をまとめました。 パワークエリを使うと、今まで分析に適さなかったデータも分析可能なデータベースに変換することができるので、これからもいろいろな事例を使ってやっていこうと思います。 では! こちらもぽちっとお願いします。 データ整形にオススメ本はこちら!.

次の

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言語のカスタム関数集です。 標準の関数で実現できない処理があればここを探してみています。

次の

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

パワー クエリ

最近、世界一簡単シリーズをよく書いていますが、今回は、これからの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自体は無制限に(コンピュータシステムが許す限り)大きなデータを扱えます。

次の