Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
Help us understand the problem. What is going on with this article?

Excelワークシート操作に相当するPower Query操作のメモ

More than 1 year has passed since last update.

Excel関数に近い処理は、Power Queryではどれにあたるかな?という視点で、一度まとめてみました。
断りがない場合、Power Queryエディターで、テーブルでの話をしています。

※処理方法に抜け、漏れ等あると思います。ご意見、リクエスト等いただければ、なるべく対応します。

全般の注意事項

1.列の操作はやりやすく、行同士の操作はやりにくい。
 テーブル上のデータを、列名によって操作するのが基本のため、全く同じようにできるわけではないです。Accessと同様、別物と考えた方がよいです。
2.処理の順序で結果が変わる。
 例えば、ワークシート操作では、SUM関数をかけておけば、後から対象の値を編集しても、SUM関数の計算結果が変わりますが、Power Queryの場合は当初のSUM関数を掛けた時点の値で固定されます。
3.関数名を打つ場合は、ワークシート関数と異なり、大文字・小文字の区別が大事になる。
 Power BIのPower Queryエディタならインテリセンスが効きますが、Excelではまだ搭載されてません。
⇒ExcelもOffice365なら搭載されております。(Ver2.75.5649.201で確認済み)

四則演算等

・特定の列に一定の値を四則演算する

対象とする列を選んだ状態で、変換タブの「標準」メニューを使います。
※「指数」メニュー、「三角関数」メニューも同様。
四則演算.png

・特定の列に別の列の値を四則演算する

列の追加タブ―「カスタム列」メニューから、計算式を入れます。「+-*/」の各記号はワークシート同様に使えます。
カスタム列追加メニュー.JPG
※上記の手順で、計算結果の列が追加されます。計算対象列が不要であれば、手動で削除します。

・四捨五入をしたい

SUM/AVERAGE/MAX/MIN(列・行)

・列(縦方向)の合計値を得たい

 対象となる1列を選択した状態で、変換タブの「統計」―「合計」をクリック。

・列(縦方向)の合計値を入れた列を追加したい

 1)数式バー左の「fx」マークをクリック
 2)自動で入るコードを、下記のように編集して、enter。
合計列いれ.JPG

 完成図
列合計完成図.png

・行の合計(合計列の追加)

対象となる複数列を選択した状態で、列の追加タブの「標準」―「加算」をクリック。
行の合計.png

lookup系

・VLOOKUP関数みたいにしたい

クエリのマージで同様の効果が得られます。操作方法は下記のレファレンスを参照。
合成列などを作らなくても、複数条件一致で持ってこれるところは、VLOOKUPより便利です。
他方、読み取る列は列数では指定できず、列名で指定することになります。

クエリをマージする (Power Query)|Microsoft

・HLOOKUP関数みたいにしたい

直接解決する関数はないので、マスタ側(データを取りに行く方)のクエリをVLOOKUPのときと同様の形に変換しておいて、クエリのマージをすることになります。
マスタ側の変換手順は下記のとおり。

変換前の状態
変換前.png

1)ホームタブ「1行目をヘッダーとして使用」―「ヘッダーを1行目として使用」
ヘッダ操作.png

2)変換タブ―「入れ替え」
入れ替えメニュー.png

3)ホームタブ「1行目をヘッダーとして使用」―「1行目をヘッダーとして使用」
ちょうど、手順1と逆の操作になります。

変換後
変換後.png

日付・時刻操作

・任意の日付・時刻を入力したい

2019/1/1

#date(2019,1,1)

2019/1/1 13:00

#datetime(2019,1,1,13,0,0)

・曜日の列を追加したい

対象となる列を選んだ状態で、
列の追加タブ「日付」メニュー「日」―「曜日名」
曜日いれ.png

・EOMONTH関数みたいにしたい

対象となる列を選んだ状態で、
列の追加タブ―「日付」メニュー「月」―「月の最終日」
※変換タブにも同様のメニューがある。

eomonth代わり.png

・EDATE関数をやりたい

こちらは手間がかかります。列の追加タブ―「カスタム列」メニューをクリック後、下記のような具合でコードを入れます。下記の例では、2ヵ月後を求めています。
edateのコード.JPG

文字列の処理

・先頭(最後)から指定数だけ文字を取りたい

変換タブ―「抽出」―「最後の文字」をクリック。
※列の追加でも同様の操作ができます。
最後の文字抽出.png

・文字の置換をしたい

変換タブ―「値の置換」―「値の置換」をクリック。下記は改行を入れた様子。
改行入れ操作.png

・数値を指定の桁数に揃えた文字列にしたい

列の追加タブ―「カスタム列」をクリックし、下記のようなコードを入力します。
下記は「Column1」という列を4桁表示にするコードです。

Number.ToText([Column1],"0000")

SUMIF/SUMIFS関数など条件付集計をしたい

 特別の関数はありません。自分で好きにフィルタしてから、先の合計処理をすればよいだけです。MAX/MIN等も同様。

全体の評点平均を上回る件数を求めた様子①
元テーブルから平均いれコード.png

全体の評点平均を上回る件数を求めた様子②
カウント集計の様子.png

一つ上の行の値を使った処理をしたい

 できなくはないけど、向いてはいません。なるべく、そのような処理にならないようにデータを用意するのがよいです。
下記は、上の行の値との差額を求めた例です。
上の行の値をとる.png

※累積計算をしたいような場合は別の方法を取ったほうがよく、本格的にコードを書いたほうがいいと思います。累積計算については下記の @PowerBIxyz さんの記事が詳しいです。
Power Query の List.Generate 関数ってなんだよー - Qiita

テストした環境

Excel(Office365:バージョン1904 32ビット)
Power Queryバージョン:2.68.5432.241 32 ビット

tanuki_phoenix
Power Queryの活用を進めています。Accessも好きです。 よろしくお願いします。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away