表計算の世界で、同じ形式の表をシート単位で分けてしまうのは悪手以外の何ものでもない。ところが、Excelに不慣れな人は必ずといっていいほどこれをやってしまう。
例えば、A列に「名前」、B列に「値」が入力されている表が「Sheet1」から「Sheet10」まで並んでいる。しかも名前が同じ順に並んでいないから３－Ｄ参照が使えない。さあどうしよう。
Microsoft365なら VSTACK関数でデータを縦結合できるので、効率を無視すれば 10シートくらいなら何とかなる。繋いでさえしまえばピボットテーブルで集計できるし。
ただせっかく Excelには Power Queryという強力なツールがあるのだから、これを使わない手はないだろう。
Power Queryを使うことに思い至ったところまではいいのだが、ここで選択をミスすると後の運用に支障が出るので注意が必要である。よもや「複数アイテムの選択」でシートごとにクエリを作成しようとしていないだろうか。
これは止めたほうがいい。チェックを入れる作業自体がわずらわしい上に、エディタ画面が取っ散らかってしまう。何よりシートが後で増えた時に、どこまで追加していたかを把握できなくなる可能性が高い。
ここは面倒でもちゃんと手順を踏んで進めたほうがいいだろう。今回はカスタム関数を作る方法をお勧めしたい。お勧めする理由は、１シートを読み込むクエリを少しいじるだけでできるからだ。
データ］→［データの取得］→［ファイルから］→［Excel ブックから］
開いた画面では、ひとまず１シートのみを選択する。
ファイルを選択して［開く］ 「Sheet1」のみを選択して［データの変換］をクリック
ここからはエディタ画面上の操作になる。
といっても、この時点で「先頭行をヘッダーにして列にデータ型を指定する」ステップが自動的に入るので、既に形としては整っていると思われる。必要ならここに加工を加えて、まずは「１シートを読み込むクエリ」を完成させよう。
「１シートを読み込むクエリ」が完成したら、これをカスタム関数に変換する。
いや、簡単なので心配しなくて大丈夫。
クエリ（画像では「Sheet1」）を選択 ［ホーム］→［詳細エディター］をクリック
今回の場合なら、参照しているシート名の文字列を変数に変えることと、それを先頭行で宣言することの２つだけ。
【1.シート名を変数に変更する】 「Sheet1_Sheet1 = ソース{Item="Sheet1" Kind="Sheet"]}[Data]」を 「Sheet1_Sheet1 = ソース{Item=シート名 Kind="Sheet"]}[Data]」に 書き換える（変数なので「""」で囲まないこと） 【2.変数の宣言をする】 先頭行に 「(シート名 as text)=>」を追加して［OK］
すると、クエリが関数に変換される。因みにカスタム関数は、先頭行の「変数の宣言」を削除してやれば、元のクエリに戻せるので再編集も簡単にできる。
さすがに関数名が「Sheet1」は不格好なので、この時点で適当な名前に変更してしまおう。
左ペインの「クエリ」から作成したカスタム関数を選択 右クリック［名前の変更］で「シート取込」に名称を変更
次のステップで「読み込むシート名の一覧」が必要になるので、今回は直接入力でシート名テーブルを作る。
［ホーム］→［データの入力］でテーブルの作成画面を開く
この画面では直接入力してもいいし、既に作った改行入りのテキストデータを「貼り付け」してもいい。
読み込みたいシート名を直接入力か「貼り付け」で入力 →［OK］
これでシート名のテーブルが出来上がった。
今回はなるべく M言語を記述しないことにこだわったのでこんなやり方をしているが、わざわざ手作りしなくても、予め作っておいたテーブルを読み込んでも何ら問題ない。
最悪手作りするにしても、下記のようにして作ったほうが更新時の手間が少ない。
= Table.FromColumns(
{{"Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8", "Sheet9", "Sheet10"}}, type table [列1=text]
)今回の場合なら下記でも
= #table(
type table [列1=text], List.Transform({1..10}, each {Number.ToText(_, "Sheet0")})
)さて、ここからは作っておいたカスタム関数の出番である。
［列の追加］→［カスタム関数の呼び出し］→［OK］
これで、それぞれのシートごとのテーブルを読み込んだ「シート取込」列が追加される。
あとはこのテーブルを展開すればいい。
「シート取込」列の右上のアイコンをクリック 「元の列名をプレフィックスとして使用します」のチェックを外す →［OK］
これでシートごとにデータが展開される。
ここまでくれば、このまま接続のみで読み込んでピボットテーブルの元データにしてもいいんだけど、一応グループ化までやっておく。
「名前」列を右クリック「グループ化」
新しい列名「値」・操作「合計」・列「値」を指定して［OK］
これで完成。
あとはシートに読み込むだけ。
今回は手作りしているけど、シート名のテーブルは「Excel.Workbook」で表示されるテーブルから、条件を指定して抽出する形でも簡単に作れる。
let
ファイルパス = "C:\...\...\sumple.xlsx",
ソース = Excel.Workbook(File.Contents(ファイルパス), null, true),
行の抽出 = Table.SelectRows(ソース, each
not List.Contains({"対象外1", "対象外2"}, [Name])
and [Kind]="Sheet"
),
列の選択 = Table.SelectColumns(行の抽出, {"Name"})
in
列の選択これで対象外の「対象外1」「対象外2」シートを除外したシート名テーブルが作成できる。
