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

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

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


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

###四則演算等
####・特定の列に一定の値を四則演算する
対象とする列を選んだ状態で、変換タブの「標準」メニューを使います。
※「指数」メニュー、「三角関数」メニューも同様。
![四則演算.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/c7129219-9e40-ed30-3523-d8ca425606dd.png)

####・特定の列に別の列の値を四則演算する
列の追加タブ―「カスタム列」メニューから、計算式を入れます。「+-*/」の各記号はワークシート同様に使えます。
![カスタム列追加メニュー.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/80a1b16b-f79a-05a4-07ce-ebbabbdb8859.jpeg)
※上記の手順で、計算結果の列が追加されます。計算対象列が不要であれば、手動で削除します。

####・四捨五入をしたい


###SUM/AVERAGE/MAX/MIN（列・行）
####・列（縦方向）の合計値を得たい
　対象となる1列を選択した状態で、変換タブの「統計」―「合計」をクリック。

####・列（縦方向）の合計値を入れた列を追加したい
　1)数式バー左の「fx」マークをクリック
　2）自動で入るコードを、下記のように編集して、enter。
![合計列いれ.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/3bf14b69-be42-2cfc-1294-48fcd34f14a3.jpeg)


　完成図
![列合計完成図.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/b7766450-f831-dc26-8cc4-dd2c6934b9b1.png)

####・行の合計（合計列の追加）
対象となる複数列を選択した状態で、列の追加タブの「標準」―「加算」をクリック。
![行の合計.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/4adb6f52-a8db-8a0f-7d66-fb07412f1b85.png)

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

[クエリをマージする (Power Query)｜Microsoft](https://support.office.com/ja-jp/article/%E3%82%AF%E3%82%A8%E3%83%AA%E3%82%92%E3%83%9E%E3%83%BC%E3%82%B8%E3%81%99%E3%82%8B-power-query-fd157620-5470-4c0f-b132-7ca2616d17f9)

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

変換前の状態
![変換前.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/4197237b-090d-099c-aed8-82c591821466.png)

１）ホームタブ「1行目をヘッダーとして使用」―「ヘッダーを1行目として使用」
![ヘッダ操作.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/48cdea41-c2be-e11c-9b23-35f98e9921fd.png)

２）変換タブ―「入れ替え」
![入れ替えメニュー.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/0893324f-f443-165f-0c7c-8ba1d8a82378.png)

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

変換後
![変換後.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/b7c9835e-0179-1888-bc62-7dda2ae0942b.png)

###日付・時刻操作
####・任意の日付・時刻を入力したい
2019/1/1

```
#date(2019,1,1)
```
2019/1/1 13:00

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


####・曜日の列を追加したい
対象となる列を選んだ状態で、
列の追加タブ「日付」メニュー「日」―「曜日名」
![曜日いれ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/fe8a2128-b54d-3fdd-a047-f9b83dfa75ea.png)

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

![eomonth代わり.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/ce74f4a8-3b70-05ef-d7c5-fd27e3e8ba39.png)


####・EDATE関数をやりたい
こちらは手間がかかります。列の追加タブ―「カスタム列」メニューをクリック後、下記のような具合でコードを入れます。下記の例では、2ヵ月後を求めています。
![edateのコード.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/79a1ef5a-102d-0a6e-4963-32a0a634ccf1.jpeg)

###文字列の処理
####・先頭（最後）から指定数だけ文字を取りたい
変換タブ―「抽出」―「最後の文字」をクリック。
※列の追加でも同様の操作ができます。
![最後の文字抽出.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/8abfe6e9-3d66-dbba-4e22-6adeef9b7882.png)


####・文字の置換をしたい
変換タブ―「値の置換」―「値の置換」をクリック。下記は改行を入れた様子。
![改行入れ操作.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/52ed173a-2b33-d24e-afa3-689b2584236a.png)

####・数値を指定の桁数に揃えた文字列にしたい
列の追加タブ―「カスタム列」をクリックし、下記のようなコードを入力します。
下記は「Column1」という列を4桁表示にするコードです。

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


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

全体の評点平均を上回る件数を求めた様子①
![元テーブルから平均いれコード.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/5aa0745c-8541-5345-6859-57d5e45b30f8.png)

全体の評点平均を上回る件数を求めた様子②
![カウント集計の様子.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/fba1557b-c4a1-a761-3978-d9c6ce9758e4.png)

###一つ上の行の値を使った処理をしたい
　できなくはないけど、向いてはいません。なるべく、そのような処理にならないようにデータを用意するのがよいです。
下記は、上の行の値との差額を求めた例です。
![上の行の値をとる.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/2427e71f-3401-b6f1-68d0-349db0bccfd7.png)


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


###テストした環境
Excel（Office365：バージョン1904 32ビット）
Power Queryバージョン：2.68.5432.241 32 ビット