スピルの結果を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関数で絞り込むのでしたら、そちらも難しくなります。それらはもう、補足の範疇を超えますので、リクエストがあったら別コンテンツで解説しましょう。実務は複雑です。がんばってくださいね。