スピルの結果をPower Queryで使う
2025年9月追記：これ、知らないうちに仕様が変わったようです。せっかく書いたので、この内容は残しておきますが、名前定義をしなくてもPower Queryに読み込めるようになったみたいです。詳しくは文末に追記します。
セミナーで「え？それ、できますよ」とお話したら、みなさん「知らなかったわ～」と喜んでくれたので、ご紹介します。本当は、別のコンテンツにも書いてあるんですけどね…。
発端は、セミナー中の雑談で、受講者さんからの「テーブル内でスピルは使えないんですよね～」みたいな話でした。それは仕様です。テーブル内ではスピれません。
まぁ、しかたないですね～的な流れから「スピった結果をPower Queryで使いたいんですよね～」みたいに言われたので「え？それ、できますよ」と。まず前提として、テーブル内ではスピれません。でも、やりたいことって「テーブル内でスピらせる」ことではなく、スピルの結果を「Power Queryで使う」ことですよね。だったら、何も"テーブル内"に、こだわる必要はありません。Power Queryに読み込めるのって、テーブルだけじゃないです。セル範囲に名前を定義しておけば読み込めるんです。超簡略化したケースで、実際にやってみましょう。
セルF2にFILTER関数を入れました。A列からC列の元データは、テーブルではなく普通のワークシートです。ここに深い意味はありません。元データはテーブルでもいいです。普通のワークシートなのでトリム参照を使っています。ここも、たいした意味はありませんので流してください。要するに、何らかの"スピル系関数"を使ったとします。ちなみに、このFILTER関数は、セルE1に入力した記号で絞り込んでいます。この、FILTER関数の結果を、テーブルにすることはできません。なので、FILTER関数の結果に、名前を定義します。
ここで注意していただきたいのは、セル範囲F1:G1のタイトルは名前定義に含めません。結論から言いますが、値(ここでは"名前"と"数値")を直接入力したセルを含めると、このあとでFILTER関数の結果が増減したときに、名前の定義範囲が連動しません。名前を定義するのは、あくまでFILTER関数の結果です、したがって「F2#」です。
[参照範囲]ボックスで指定したアドレスの最後に、スピル範囲演算子(#)がついていることに留意してください。ここでは「Data」という名前にしました。これで準備完了です。FILTER関数の結果内にアクティブセルを置いて、[データ]タブ[データの取得と変換]グループ[テーブルまたは範囲から]ボタンをクリックします。
ちなみに、この機能は「テーブルから」ではなく「テーブルまたは範囲から」です。テーブル限定などとは言っていません。なぜテーブルだとPower Queryに読み込めるのかというと、テーブルにはもれなく"名前"が定義されるからです。ブック内で同じ名称のテーブルは作れませんよね。それは「テーブルの名前」が"名前定義"としても設定されるためです。要は、名前が定義されていれば、Power Queryに読み込めるということです。[テーブルまたは範囲から]ボタンをクリックすると、Power Queryエディタが起動して、名前範囲「Data」が読み込まれます。
ここで、一手間必要です。名前「Data」を定義したのは「F2#」です。タイトル(ヘッダ)は含まれていません。なのでPower Queryは、便宜的な「Columns1」「Columns2」というヘッダをつけました。これは手で直してください。最初に1回やるだけなのですから、それほど手間でもないです[*1(下記参照)]。
一般的には、ここでPower Queryを使って何らかの処理をするのでしょう。今回は、このまま読み込みます。[閉じて読み込む]-[閉じて次に読み込む]で、お好きなところに読み込んでください。
正しく読み込まれています。さて、絞り込みを変えてみましょう。セルE1を"B"にしてみます。
まず、FILTER関数が反映されます。Power Queryは、[更新]を実行しないと更新されません。[データ]タブの[すべて更新]をクリックするか、Ctrl+Alt+[F5]キーを押します。
Power Queryの結果も更新されます。スピった結果をPower Queryで利用したい、ってのは実務でよくあります。「テーブル内ではスピれない」ということで諦めている方が多いようですけど、この方法で可能です。ぜひ、ご活用ください
補足
(*1)今回は、簡単なケースで解説しました。名前を定義した範囲に「ヘッダを含めなかった」のは、手でやっても、たいした作業量ではないからです。もし「ヘッダも含めてPower Queryに読み込みたい」のでしたら、ちょっと難しくなりますけど方法はあります。ヘッダも含めてスピらせればいいんです。たとえば、次のように。
VSTACK関数は、複数の表やリストを縦に結合する関数です。上図では、セル範囲B1:C1と、FILTER関数の結果を縦に結合しています。これなら、ヘッダも含めた結果全体が、ひとつの数式でスピっているので、「F1#」に名前を定義できます。確認しましたが、ちゃんとヘッダもPower Queryで読み込まれました。上図は、ヘッダがシート上の連続したセル範囲(ここではB1:C1)に入力されているケースです。もし下図のように、シート上には存在しない"任意の"文字をヘッダとして表示したいのでしたら、もう少し難しくなります。
ヘッダが、シート上の連続したセル範囲に入力されているのでしたら、そのアドレスを指定するだけです。しかし、たとえば"担当"と"売上"のように、セルには入力されていない文字をヘッダとして表示したいのなら、まず「{"ヘッダ","売上"}」という配列を作らなければなりません。このように、複数の値を横方向に結合して、その結果を返してくれるのがHSTACK関数です。
このHSTACK関数で作った配列と、FILTER関数の結果を、VSTACK関数で縦に結合します。
上記で何度も「連続したセル範囲に入力」されていれば、アドレスを指定するだけと書きましたが、もし非連続のセルに入力されている場合は、さらに難易度があがり、CHOOSECOLS関数やINDEX関数との併用になります。さらに、非連続のセル範囲に対してFILTER関数で絞り込むのでしたら、そちらも難しくなります。それらはもう、補足の範疇を超えますので、リクエストがあったら別コンテンツで解説しましょう。実務は複雑です。がんばってくださいね。
追記(2025年9月)
この件、セミナーで実際にデモしようとしたら驚きました。名前定義しなくてもPower Queryに読み込めるようになってましたΣ( ºωº )
上図のリストは手入力しました。それぞれのセルには値が入力されています。このリストに対して[テーブルまたは範囲から]を実行してみます。
すると、いつものように「テーブルにしますか？」と促すダイアログボックスが表示されます。では、別のセルで、このリストを参照してみます。
名前定義などは、何もしていません。この参照しているセル範囲E1:G7内にアクティブセルを置いて、[テーブルまたは範囲から]を実行すると、
なんと、Power Queryエディタに読み込まれます。驚きです。クエリの名前は「FromArray_1」となっています。"配列から"というクエリ名から、何となく予想はつきますが、では次のような数式で参照してみましょう。
それぞれのセルで、1つのセルを参照しています。つまり、セル範囲E1:G7は配列になっていません。この状態だと
読み込んでくれません。配列じゃなきゃダメなんですね。スピルという仕組みを使った数式を「動的配列数式」と呼びます。つまり、スピルの結果は"配列"です。そして、ワークシート上に存在する配列なら、Power Queryに読み込まれるということです。一応、次のようなケースでも試してみます。
いけますね。もちろん、スピル系の関数結果でも名前定義なしで読み込めます。
上図のFILTER関数はデータのみです。ヘッダを含みません。だからPower Queryには"ヘッダなし"として読み込まれ、便宜的な"Column1"などが付けられています。
いやぁ～さすがMicrosoftです。便利な仕組みを追加してくれました。本来、名前定義という機能は、セルに分かりやすい名前をつけて、数式の可読性を高めましょう、という仕組みです。ですが、定義した名前の範囲って、データの増減に合わせて変化します。その特性を利用して、けっこう昔から便利なテクニックとして応用してきました。UNIQUE関数の結果を入力規則のリストに表示するときも、一度名前定義しておけば可能、みたいな。でも、正直言って毎回名前定義するのって面倒くさいですし、ケースによっては範囲が自動的に変化しない場合もあります。今回の機能拡張で、スピッた結果をPower Queryで利用しやすくなりましたね。喜ばしい限りです。