スピルで単価と数量のデータからSUMIF関数を使う

スピルは便利なのですが、イマイチうまく集計できないときがあります。

下のような表で、G2からG6に売上金額を求める場合です。せっかくスピルを使うのですから計算列を追加したくありません。このまま個数と単価を掛け合わせるような列を作らずに、セルG2にSUMIF関数で求めたいものです。

そうなると、以下のような計算式になります。

=SUMIF(A2:A16,F2:F6,B2:B16*C2:C16)

第二引数はスピルではない場合、1セルの指定なのですが、B2からB6というセル範囲を入れているので商品A1からA5までの売上金額を求める、また第三引数でもスピルであれば、範囲×範囲が可能になるはずなので、この計算式と考えました。

ここで確定するのにEnterを押すのですが、確定になりません。まるでExcelがこの計算式を確定することを拒否しているようです。

では、このような場合、どのように攻略していけばいいのでしょうか。

実験１・SUMIF関数の第二引数をスピルではなくしてみる。

この場合、このおかしな動作の原因として考えられるのは、スピルに関わることだと考えました。そこで、まずは第二引数のスピルをやめ、1セルだけの指定にしました。

=SUMIF(A2:A16,F2,B2:B16*C2:C16)

それでもやはり結果は同じ、何回Enterキーを押しても確定になりません。

実験２・SUMIF関数の第三引数をスピルではなくしてみる。

今度は、第二引数はスピルのまま、第三引数のスピルをやめ、個数だけで集計してみます。

=SUMIF(A2:A16,F2:F6,B2:B16)

今度は問題なく集計できました。

このことから、原因はどうあれ、第三引数で範囲×範囲の掛け算をしてはいけないということがわかりました。

では、個数×単価の計算列をD列に追加すれば計算できそうですが、それではスピルのいいところが消されてしまう気がします。

SUM関数とスピル

話は変わりますが、SUM関数とスピルは全く相性がよくありません。例えば個数と単価の数値を行ごとの足した値をD列に求めたいとします。行ごとなのでセルD2は11、セルD3は22という結果になると思います。

行ごと合計をスピルで一気に求めるために、セルD2に以下の計算式を入れます。

=SUM(B2:C16)

するとよく考えると、この計算式は、B2からC16の範囲の合計というスピルでもなんでもない、ただの合計を求めるSUM関数なので、1つのセルにB2からC16までの合計が求まってしまいます。

当たり前の話ではあるのですが、でも行ごとの合計もせっかくのスピルなのでできてほしいと思ってしまうのです。

BYROW関数

スピルが登場したときに、これができないというのは、実務で使う上でとても痛いなぁと思ったのです。しかしその後、スピルするときにBYROW関数という行ごとに集計ができる関数が登場しました。

BYROW関数は記事執筆をしている2022年11月時点では、Microsoft365かWeb版のExcelでしかできません。まだ動作しないExcelも存在しますので、もしBYROW関数が動かないExcelをお持ちの方でBYROW関数を試したい方は、Web版Excelで試してみてもよいでしょう。

BYROW関数の使い方は次の通りです。

=BYROW(スピル範囲,LAMBDA(変数名,計算式))

なにやら中にLAMBDAだとか、変数だとか、計算式だとか、何を設定したらいいのかわからないものが入っていますが、書式はこの通りです。実はLAMBDAというのはLAMBDA関数という新しい関数で、この使い方については後日、詳しい記事を書きますが、今は、書式はこの通りで覚えてください。

変数名は「a」でも「範囲」でもかまいません。ダブルクォーテーションなしで、何かの文字列を指定します。

計算式は、どんな計算をするかなのですが、変数名の正体として、スピル範囲の1行が入るようになっているので、その変数名を使った計算式を書きます。上記の例では変数名を「a」とした場合はSUM(a)となります。

上記の例での計算式は次の通りになります。

=BYROW(B2:C16,LAMBDA(a,SUM(a)))

これで、行ごとの合計が求まります。

心情的には、こんなに面倒なの？と思いますが、一番楽な方法がこのような方法になります。

BYROW関数とSUMIF関数

では、同じように、SUMIF関数とBYROW関数の組み合わせも考えていきましょう。

その場合、スピル範囲はどこを指定するか、ということになりますが、スピルでなければSUMIF関数の第二関数が1セルであり、スピルになればそこが範囲になるので、SUMIF関数の第二引数を指定する、F2からF6となります。それで考えた式が次の通りです。

=BYROW(F2:F6,LAMBDA(a,SUMIF(A2:A16,a,B2:B16*C2:C16))))

しかし、これでもEnterキーで確定できませんでした。

これはもう、BYROW関数を使おうが、SUMIF関数の第三引数に範囲×範囲を入れている時点ではどうにもならないということと判断しました。

スピルとIF関数

そこで、もっと単純に、SUMIF関数ではなく、SUM関数とIF関数に分けて考えてみようと思いました。

合計と同じように、D列にA列の販売商品がF列の商品と同じかどうかをIF関数で求めるスピル計算式が作れるか試してみます。

セルD2に次の計算式を入れました。

=IF(A2:A16=F2:F6,TRUE)

結果、A列=F列の同じ行が一致していたらTRUEになり、F列が存在していない行はN/Aのエラーとなりました。これでは想定している動作とは違います。

これはよく考えてみると、A列とF列のどちらのスピルをさせた結果がほしいのかわからないということに気づきました。この場合、比較するF列は1セルしか反応できないと思いました。

そこで、F列をセルF2だけ指定してみました。

=IF(A2:A16=F2,TRUE)

これならばきちんと求まります。

そこからF列をスピルさせることができるのか、イマイチ不安でしたが、これで1ステップ先に進みます。今度は、A列がセルF2の値と同じ行は、個数×単価ができるか試してみます。スピルなので個数と単価は範囲で指定します。

=IF(A2:A16=F2,B2:B16*C2:C16)

きちんと範囲×範囲ができています。

これで望みが繋がったと思いました。というのも、もしA列とF列が違ったら0にすればいいし、その計算式の合計が、商品A1に対する合計だということになるからです。

では、それを実証してみましょう。

IF関数の第三引数のそうではなかったらどう言う値にするかを0にし、IF関数全体をSUM関数でくくってみます。

=SUM(IF(A2:A16=F2,B2:B16*C2:C16,0))

きちんとA列が商品A1の行の個数×単価の合計が求まりました。

あとは、この計算式で指定しているF2をスピルにすればいいのです。

=SUM(IF(A2:A16=F2:F6,B2:B16*C2:C16,0))

そう甘くはなく、N/Aエラーになりました。先ほど、A2:A16=F2:F6というようにIF関数の中で範囲=範囲をしたときに、7行目以降がN/Aエラーになりました。SUMで範囲の合計をしていますが、関数で指定した範囲の中にエラーがあるとそのエラーに引っ張られてしまうので、それが原因だと思いました。

また、この時点で気づいたのですが、このままスピルにした場合、SUM関数では行ごとの指定ができずに全部の合計をしてしまうかもしれないと思いました。

BYROW関数とSUM関数とIF関数

では、行ごとの指定をすればいいのでBYROW関数の出番かと思いました。しかし、行ごとに分けたいのはA列からC列。BYROW関数で指定できるのはスピルの範囲なので、F列です。1行ごとの仕分けする範囲をBYROWの範囲として指定すべきなので、A列からC列を指定しなくていいのか、その違いが不安だったのですが、とにかくトライしてみることにしました。

組み合わせると、次の計算式になります。

=BYROW(F2:F6,LAMBDA(a,SUM(IF(A2:A16=a,B2:B16*C2:C16,0))))

この計算式をセルG2に入力してみました。

すると、うまく求まっているように見えました。

検算するため、スピルはしないで、セルD2にB2×C2の計算式を設定し下にコピー、さらに、H列にA列がF2に一致していたらD列の合計を求めるSUMIF関数を入力し、下にコピーしました。

その結果、先ほどの計算式の結果と完全一致。この計算式でよかったようです。

しかし、解せないのが、BYROW関数のスピル範囲の指定です。

この時点で言えることは、BYROW関数の第1引数は、スピル範囲はスピル範囲で指定していいのだけど、1行ごとに分けて集計するという範囲ではないということなのかなと思いました。

おそらく理由があると思いますが、そこの理解には至っていません。もしかしたら、現状の動作がおかしくてBYROW関数の動作が修正になる可能性もありますが、今のところ動作しているのでこの方法がベストなのかもしれません。

本件は進展がありしたい、追記していきたいと思います。

まとめ

スピルはまだ理解が進んでいるわけではなく、やっとスピルだけではできない計算をフォローするための関数が揃ったところです。

今回は、単価と個数のデータがあって、スピルなのだから、1つのセルの計算式だけでなんとかできるだろう、と思ってやってみたのでが、うまくいかなかったので、どういう手順で考えたかというのをせっかくなので記事としてまとめました。

Excelの機能や関数の使い方は覚えられても、なかなか発想の方法がわからないので教えてほしいと言われます。しかし発想の方法は目の前に課題があって、その課題を解決していくプロセスを私が解説しながら操作して、初めてお伝えできるものです。この記事が少しでもみなさんの発想力のお手伝いになれば幸いです。

この記事のポイントとしては、スピルは普通ではできないことがあるということです。

今回のように範囲×範囲がSUMIF関数に入ったとたんに使えなくなる、SUM関数とスピルの相性が悪いということを説明しました。SUM関数のほかのAVERAGE関数のような基本集計関数も一度に範囲を計算するので相性は悪いです。

そのために用意されたのがBYROW関数で、行ごとの計算をしてくれる関数が新しい関数として登場しました。また、同様に列ごとを計算するのがBYCOL関数です。

まだまだ、これからスピルでできないことに気づいていくでしょう。しかし、どうしてもできない場合は、また新しい関数が登場すると思いますし、それが揃えば、1シート内の計算式も1セルに1つの計算式を入力すればいいだけ、という時代も来るかもしれません。

その1つの計算式は難しくても1回設定すれば何度でも使いまわせるわけですから、どんどん便利になっていくでしょう。