LoginSignup
6
11

More than 1 year has passed since last update.

@tak_ham

Power Query(パワークエリ) の基本的な使い方と活用方法

Last updated at Posted at 2021-06-07

使い方

  1. エクセルのテーブルや CSV を用意する。
    1. テーブルの作りかた
      1. 表のセルを選択し、「挿入」→「テーブル」を選択する。
        image.png
      2. データ範囲を指定する。きれいなテーブルなら特にいじらないでも問題なく範囲が指定されている。必要に応じて見出しのチェックを入れる。
        image.png
      3. テーブルに変換される。
        image.png
      4. テーブルのセルを選択し、「デザイン」→「テーブル名」で適切なテーブル名を設定する。
        image.png
      5. エクセルファイルを保存する。
  2. 新規エクセルを開き、「データ」→「データの取得」→「ファイルから」→「ブックから」(ほか適切なもの)を選択し、テーブルのあるエクセルファイルをインポートする。
    image.png
    image.png
  3. ナビゲーターが表示されるので、対象のテーブルを選択し、「データの変換」を選択する。
    image.png
  4. Power Query エディターが表示されるので、データをコネコネ(※ここが一番大事)する。
    image.png
  5. 「閉じて読み込む」を選択し、エクセルデータにする。
  6. 任意で自動更新の設定を行う。この設定を行わない場合、元データの更新時には、手動でクエリの更新をしないとデータが反映されない。
    1. 「クエリ」→「プロパティ」を選択する。
      image.png
    2. 「定期的に更新する」「ファイルを開くときにデータを更新する」にチェックを入れて、OK。
      image.png

コネコネ用例 (随時追加していく)

集計

以下のような伝票テーブルを、顧客ごとに売り上げの集計や件数の集計を行う。
SUMIF, COUNTIF を使ってもできる。

image.png

  1. データの取得で上記エクセルファイルを選択、テーブルを選択し、データの変換で Power Query エディターを開く。

  2. 集計のキーとなる列(今回だと顧客番号)を選択し、「グループ化」を選択する。

    image.png

  3. 顧客ごとの件数の集計の場合

    1. 「行数のカウント」を選択し、OK。
      image.png
    2. 以下のような件数データに変換される。列名は適切に変更する。
      image.png

  4. 顧客ごとの売り上げの集計の場合

    1. 「合計」を選択し、集計したい列名(今回だと価格)を選択し、OK。
      image.png
    2. 以下のような売り上げデータに変換される。列名は適切に変更する。
      image.png

  5. 顧客・月毎の売り上げの集計の場合

    1. 月でグループ化できるよう、グループ化の前に月の列を作成する。 売上日を選択し、「列の追加」→「日付」→「月」→「月の開始日」を選択し、「月の開始日」列を追加する。
      image.png
      image.png
    2. 「詳細設定」を選択すると、グループ化のキーを複数選べる。グループ化で「顧客番号」「月の開始日」を選択、「合計」を選択し、集計したい列名(今回だと価格)を選択し、OK。
      image.png
    3. 以下のような売り上げデータに変換される。列名は適切に変更する。
      image.png

連結

以下のように支店毎にファイルが分かれてフォルダに置かれている売り上げデータを一つのデータにまとめる。

image.png

image.png

  1. データの取得で「フォルダーから」を選択する。
    image.png
  2. 支店ごとのファイルが入っているフォルダーを選択する。
    image.png
  3. 「結合および編集」を選択する。
    image.png
  4. テーブルを選択し、OK。
    image.png
  5. 連結されたデータが作成される
    image.png

  6. 任意でデータの値を修正する。

    1. ファイル名の列を選択し、「変換」→「値の置換」を選択する。
      image.png
    2. 検索する値を ".xlsx"、置換後の値を空にし、OK。
      image.png
    3. 文字列から .xlsx が削除され、支店名になる。
      image.png
    4. 列名は適切に変更する。

  7. フォルダに新しい支店のデータを追加する。

    image.png

  8. 自動で新支店のデータが取り込まれる。

追加前
image.png

追加後
image.png

結合

以下のような伝票テーブルに、顧客テーブルから顧客名を結合する。
VLOOKUP を使ってもできる。

image.png
image.png

  1. データの取得で伝票テーブルのエクセルファイルを選択、テーブルを選択し、データの変換で Power Query エディターを開く。
  2. 新しいソースから、顧客テーブルのエクセルファイルを選択、テーブルを選択する。
    image.png
  3. ベースにしたいテーブル(今回だと伝票)を選択し、「クエリのマージ」を選択する。
    image.png
  4. マージのポップアップがでてくるので、もう一つのテーブルを選択する。
    image.png
  5. 結合したい列をそれぞれ選択し、適切な結合の種類を選択し、OK。
    image.png
  6. 列が一行増えるので、右上のボタンを選択する。
    image.png
  7. 追加で表示したい列名を選択し、OK。
    image.png
  8. 伝票テーブルに会社名がマージされた。
    image.png

正規化

以下のような売り上げデータを[顧客番号、月、売り上げ]の形に変換する。

image.png

  1. データの取得で上記エクセルファイルを選択、テーブルを選択し、データの変換で Power Query エディターを開く。
  2. 基準となる列(今回だと顧客番号)を選択し、「変換」→「列のピボット解除」の横の▼ → 「その他の列のピボット解除」を選択する。
    image.png
  3. 以下のような正規データに変換される。列名は適切に変更する。
    image.png

EoL確認

メーカのサイトにある EoL のテーブルを取得し、EoL の日付が近いものを確認する。
(プロキシ超えて取得する方法は確認中。。)

  1. データの取得で「Webから」を選択する。
    image.png
  2. EoL テーブルが記載されている URL を入力し、OK。
    今回だと JUNOS のページ https://support.juniper.net/support/eol/software/junos/ image.png
  3. 対象のテーブルを選択し、「データの変換」を選択する。
    image.png
  4. 必要に応じて不要な行を削除したり、加工したりして、「閉じて読み込む」でエクセルデータにする。
    image.png
  5. 以下のようなエクセルデータになる。
    image.png
  6. 例えば以下のような条件付き書式で一年以内に EoL を迎えるものを色付けし、気付けるようにする。
    image.png
    image.png
  7. 多分 PAD 使えば、その情報を Teams に投稿することもできるじゃないか、と妄想。(未確認)
6
11
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
6
11