Power Query - テーブル化せずにシートからデータを読み込む方法

# 概要
ワークシートのセル範囲をクエリ(PowerQuery)にてデータを取得する場合、取得するセル範囲を選択した状態で、データメニューから「データの取得と変換 - テーブルまたは範囲から」を実行すると、セル範囲をテーブル化してからクエリに取得されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8721/3af88d08-0b81-f4bf-05e8-eee6cbe3fde2.png)

この記事では、元のセル範囲をテーブル化せずにデータ取得する方法を考えます。
手順としては、セル範囲に対して名前を定義してから、その名前を使ってクエリにてデータを取得させる方法となります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8721/beeb0020-46e9-c2f6-6407-ac486f5aba33.png)

# 名前の管理
セル範囲を参照する名前を作成するため、数式メニューから「名前の管理」を開きます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8721/bf92464d-6156-eec9-f547-47f4756a0224.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8721/0aa2a03b-280c-5a94-22ce-cb05f7751095.png)

「新規作成」ボタンを押してクエリでインポートしたい範囲を選択して名前を作成します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8721/8d6e806e-905f-5bf4-30c7-6c9c60e9c770.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8721/91691861-c96c-204b-971e-5249cf664ee8.png)

# データの取得
ワークシート左上の「名前ボックス」に、上記で作成した名前「Sheet1_A_C」を入力してEnterキーを押すと、参照範囲が選択された状態になります。
この状態で、データメニューから「データの取得と変換 - テーブルまたは範囲から」を実行します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8721/ec6374ba-ba62-1163-02ed-c1b77bbe91e9.png)

Power Query エディタが立ち上がってクエリが自動作成されますが、選択範囲に名前がついている場合は、元のセル範囲はテーブル化せずに読み込むことができます。
```PowerQuery:Sheet1_A_C
let
    ソース = Excel.CurrentWorkbook(){[Name="Sheet1_A_C"]}[Content],
    昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"a", Int64.Type}, {"b", Int64.Type}, {"c", Int64.Type}})
in
    変更された型
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8721/e0aa151f-561e-6b5b-b9ab-1a1e2ab72512.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8721/9499ab7c-13f3-194a-bec7-89d077ded0d7.png)

# データ範囲を抽出するクエリ
名前の参照範囲を列全体に設定したので、データ入力の無い行が大量に読み込まれてしまいます。
そこでデータが入力された最後の行位置を求めて、その行までを保持するクエリを作成します。
```PowerQuery:Sheet1_Trim
let
    ソース = Sheet1_A_C,
    EmptyRowCheck = Table.AddColumn(ソース, "EmptyRow", each List.NonNullCount(Record.FieldValues(_)) = 0),
    追加されたインデックス = Table.AddIndexColumn(EmptyRowCheck, "インデックス", 1, 1, Int64.Type),
    NonEmptyRows = Table.SelectRows(追加されたインデックス, each [EmptyRow] = false),
    MaxNonEmptyIndex = List.Max(NonEmptyRows[インデックス]),
    保存された先頭行 = Table.FirstN(ソース, MaxNonEmptyIndex)
in
    保存された先頭行
```

EmptyRow 列を追加して、同じ行の他の列の値がすべて null かそうでないかを演算します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8721/f8d948cf-7c3c-bd83-153f-e6b85fc61948.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8721/bf280095-31aa-b69e-c63f-f169d8ea6512.png)

インデックス列を追加したあと、EmptyRow でフィルターして、インデックスの最大値として `MaxNonEmptyIndex` を求めます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8721/419c2632-6d80-e27b-c7e1-f0805e04c9ca.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8721/bcc44c8d-522a-dcfa-f6ed-bea07ed5a6c6.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8721/17e163b0-be4c-fd7f-af49-4905e9e27c4a.png)

テーブルの先頭から `MaxNonEmptyIndex` までの行を保持します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8721/fe81b86f-b4d6-c34d-3f1c-f23d366e795e.png)

# データ範囲を抽出する数式
名前付き範囲の数式にて、セル範囲を求める事もできます。
`TAKE`関数の第一引数には入力する列範囲、第二引数には最大で読み込む行数を処理負荷や入力データ数と相談して決定します。
```Excel:Sheet2_A_C
=LET(A, TAKE(Sheet2!$A:$C, 65535), height, AGGREGATE(14,6,ROW(A)/(A<>""),1), OFFSET(A,,, height))
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8721/c9302165-e0ea-7e18-842f-7d48bb9fcf98.png)

https://support.microsoft.com/ja-jp/office/aggregate-%E9%96%A2%E6%95%B0-43b9278e-6aa7-4f17-92b6-e19993fa26df

この方法で作成した数式の場合「名前ボックス」に名前を入力してもセル範囲を取得できないので、最初からPower Queryエディタを使って直接クエリを指定する必要があります。
とりあえず今回はSheet1_A_Cクエリを複製して、下記の通り作成しました。
```PowerQuery:Sheet2_A_C
let
    ソース = Excel.CurrentWorkbook(){[Name="Sheet2_A_C"]}[Content],
    昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"a", Int64.Type}, {"b", Int64.Type}, {"c", Int64.Type}})
in
    変更された型
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8721/c306d441-878c-050e-2289-098a1adc203e.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8721/c5226ff4-1d88-4e0b-442d-36d7001ac08e.png)

## MAX関数を使った方法
`AGGREGATE`関数の代わりに`MAX`関数使って書くと下記の数式で抽出することもできます。
```
=LET(A, TAKE(Sheet2!$A:$C, 65535), height, MAX(ROW(A)*(A<>""),1), OFFSET(A,,, height))
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8721/c36f0ae3-f510-be6e-fcd2-c88bab483c72.png)

ただし`MAX`関数を使った数式だと、入力範囲にエラー値が紛れ込むと数式全体がエラーとなってしまいます。
一方`AGGREGATE`関数を使った数式では、一箇所でも有効な値が入力されていればエラーも含めて抽出することができます。ただし末尾の行にエラーしか存在しない場合は除外されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8721/1abb9ec4-7d1d-45e0-2d36-643958112b16.png)

# スピル範囲からデータの取得
スピルを使ってデータが入力されてる場合は、その範囲を選択状態にした状態で「データの取得と変換 - テーブルまたは範囲から」を実行することでクエリを作成することができます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8721/1ca79a62-6c2f-8133-6596-2c2ce84ef5b1.png)
```PowerQuery:FormArray_1
let
    ソース = Excel.CurrentWorkbook(){[Name="FromArray_1"]}[Content],
    昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"a", Int64.Type}, {"b", Int64.Type}, {"c", Int64.Type}})
in
    変更された型
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8721/749dff75-bbd1-8041-6e6c-1cf68cd14dd3.png)

スピル範囲を選択した場合は、セル範囲をテーブル化せずに、名前「FormArray_1」が自動で登録されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8721/eb51426f-b1a1-6767-c828-c2ea46f88e69.png)