最下行に合計を表示する
まずは、こちらの画面をご覧ください。せーの、ドン！
そして～
からの～
みたいな。セルF2にはFILTER関数を入れています。データの件数によらず、表の一番下に「合計行」を表示したいんです。ああ、もちろんFILTER関数に、そんな仕組みはありません。GROUPBY関数じゃないんだから。この手の「表の一番下に合計行を表示したい」ってネタは、30年近く前のパソコン通信時代から鉄板でした。ただ、当時はFILTER関数なんて便利なものはなかったし、もちろんスピルもできません。あらかじめ、合計行のセルにSUM関数を仕込んでおくのですが、これがメッチャ難しい。現実的には不可能に近いです。それが、ちょっと工夫すれば可能になりました。便利な世の中になりましたね～長生きはするもんですなぁ。実際にどうやるかを、詳しく解説します。
複数の表を縦に並べる
今回は、2つの表を別々に作って縦に結合します。使うのはVSTACK関数です。STACKとは"積み重ねる"とか"並べる"という意味で、Vは"垂直"や"縦方向"などを表すVerticalの頭文字です。VSTACK関数は、複数の表やリストや配列を、縦方向に並べて一つの表やリストを作成する働きをします。簡単なケースで試してみましょう。
今回結合する表は2つです。
①はFILTER関数です。
さて、問題は②ですね。ここを、どう作るかがポイントです。
複数の値を横に並べる
今回は、この②を作るのに、2つの方法を思いつきました。まずはひとつめ。おそらく、この発想がセオリーでしょうね。まず、②部分ですが、ここを「3つの値が横方向に結合されたリスト(配列)」と考えます。表やリストを横方向に結合するにはHSTACK関数を使いますが、HSTACK関数に指定するものは一般的に「セル範囲」や「配列」です。
ですが、「もしかして、いけんじゃね？」と試してみたら、ただの文字列や数値も結合できました。
いやぁ、先入観にとらわれず、何でも試してみるものですね。つまり今回の②は、次のようにして作れます。
もちろん、右端の「300」は数値を直接指定するのではなく、FILTER関数の「数値」列をSUM関数で合計します。
今回のケースに限っていうなら、FILTER関数の結果で数値が入力されているのは"3列目"しかありませんので、実は「SUM(FILTER(Data,Data[名前]=E1))」でも計算できます。しかし、そんなことを言っているのではありません。そんな手抜きでは、さまざまなケースに応用できませんからね。ここは何とかして、FILTER関数の結果から「右端の1列」だけを特定しましょう。
特定の列を抽出する
VSTACK関数やFILTER関数など「スピルありき」で開発された関数を駆使するときは、この「特定の列を抽出する」処理が最も重要です。"最大の山場"であり"腕の見せどころ"でもあります。まず、特定の列を抽出するときによく使うパターンを、いくつかご紹介します。まずは、XLOOKUP関数です。
上図のように、タイトル(見出し)を探して列ごと抽出するときに便利です。あるいは、列を抽出するといえば、CHOOSECOLS関数ですね。
最後の「{1,3}」というのは、抽出したい列の位置を配列形式で指定しました。もちろん「CHOOSECOLS(Data,1,3)」でもOKです。詳しくは、下記のページをご覧ください。
CHOOSECOLS関数は、列を抽出する専門の関数ですので、次のように順序を入れ替えるのも簡単です。
さて、特定の列を抽出したいとき、忘れてならないのがTAKE関数とDROP関数です。両者を初めて触ったときは「う～ん、なんか微妙、CHOOSCOLS関数もあるし、あんまり使わないかなぁ～」なんて思いましたが、とんでもなかったです！心より謝罪をいたします。舐めてました。本当にごめんなさい。TAKE関数とDROP関数は、間違いなく鬼のように便利です。動作に関しては、下記のページをご覧ください。
今回のように「(左から数えて何列目か分からないけど)とにかく、右端の1列」を抽出するときは、TAKE関数で一発です。
ちなみに「左から2列目」を抜き出すようなとき、私はよくTAKE関数とDROP関数を組み合わせて使っています。
この結果だけを見て「はぁ？CHOOSECOLS(Data,2)と何が違うの？」って感じた方がいらしたら、その方はExcelやプログラミングに不向きですね。"何が違うの？"にお答えするなら、発想やプロセスが違うんです。実務の現場は千差万別です。答えが正しければ○になるような試験問題じゃありません。結果が正しいのなんて当たり前です。そんなことよりも、望む結果を得るために、いかに多くの"やり方"を知っているかが、実務では最も大切なんですよ。
さてさて、今回の目的である「FILTER関数の結果のうち右端の1列」は、次のようにTAKE関数で特定できます。
したがって、合計は次のように計算できます。
別の関数も使う
上図の配列を作るために、上記ではHSTACK関数を使いました。しかし、ほかにも方法があります。そちらもご紹介しましょう。本稿の冒頭で「FILTER関数には最下行に合計を表示する機能などない」という話をしたとき、このように書きました。
FILTER関数に、そんな仕組みはありません。GROUPBY関数じゃないんだから。
実はこれ、伏線です。そう、つまり、ということは「GROUPBY関数は最下行に合計を表示する仕組みがある」ということです。ちょっとやってみましょう。
ほらね、合計行が表示されたでしょ。でも、GROUPBY関数って、SUMIF関数的なことをする関数ですよね。"田中"だけとか"佐倉"だけの合計って出せるんですかと。はい、もちろん出せますよ。GROUPBY関数にはフィルタ機能がありますから。
いま欲しいのは、このGROUPBY関数の結果のうち「最も下の1行」だけです。こんなときにもTAKE関数が大活躍します。
したがって、次のような方法でも実現できます。
GROUPBY関数の詳しい解説は、下記のページをご覧ください。
以上です。どうです？難しいですか？難しいですよね。そう、関数は難しいんです。よく私は「VBAよりもワークシート関数の方が難しい」と言います。それを聞くと、多くのExcelユーザーが「まさか～VBAの方が難しいのではぁ～」って反応します。それって、1つのセルにVLOOKUP関数を1つだけ入れるようなケースで比較していませんか？違うんですよ。実務はそんなに甘くありません。本稿で解説してきたことって、これ立派なプログラミングです。関数を組み合わせて作る数式って、1行で書くプログラミングなんです。ただし、VBAのようなステートメントやプロパティやメソッドなんかありません。"関数だけ"を使って、すべての動作を1行で書かなくてはならないんです。だから、VBAより256倍(※1)難しいです。でも、だからこそ関数は楽しいです。ぜひみなさんも、関数の奥深さを堪能してくださいね。
(※1)Office TANAKA調べ