みなさん、こんにちは！
エラーは嫌いですが直ると人一倍スッキリしますタカハシ(@ntakahashi0505)です。
こちらの記事で
掛け算したものを全て足し合わせる機能を持つエクセル関数、SUMPRODUCT関数について紹介しました。
この関数ですが、条件に合ったデータの計算するという発展をさせることができます。
例えば、サイトのアクセス解析でいうと、特定のカテゴリを条件とした平均ページ滞在時間や直帰率を算出するときがあります。
この場合はいわゆる一般的な平均である算術平均ではなくて加重平均を使う必要があるのですが
特定のカテゴリのデータのみを計算対象とするときに、SUMPRODUCT関数を活用することができます。
今回はその準備として、SUMPRODUCT関数を使って条件付きの計算する方法、またSUMPRODUCT関数を使う際に出くわすトラップがありますのでその回避方法について、お伝えしていきたいと思います。
条件付きの合計を求める
例として前回も使ったフルーツの合計金額を求める場合を考えます。
SUMPRODUCT関数を使うことで、それぞれのフルーツの単価×個数の総和を
=SUMPRODUCT(B:B,C:C)
たったこれだけの数式で算出することができます。
では、ここに条件を加えまして、「商品名に”ゴ”が含まれているフルーツだけの合計金額を求める」としたいと思います。
どのようにすることができますでしょうか？
SUMIF関数を使う方法と、SUMPRODUCT関数を使う方法の２つの方法について解説をしていきます。
SUMIF関数で算出する方法
一つの算出方法としてはSUMIF関数を使うという方法があります。記述の方法は
=SUMIF(範囲,検索条件,合計範囲)
範囲の中から検索条件に見合ったデータのみ合計範囲を合計します。
この例では
とすることで算出が可能です。
条件の箇所は
“*”&F2&”*”
としています。
「*」はワイルドカードと言いまして任意の文字列を表現しています。
「&」は文字列の連結をする演算子ですから、条件式としては「F2セルの値の前後に任意の文字列が配置されている文字列」つまり「F2セルの値が含まれている文字列」と同義です。
ただしこの方法ですとD列の金額欄が必要になります。SUMPRODUCT関数を使うとこの金額欄を設けずに同様の計算が可能になります。
SUMPRODUCT関数を使う方法
おさらいですがSUMPRODUCT関数は
=SUMPRODUCT(配列1,配列2,…)
配列1,配列2,…の対応する要素の積の総和返します。
と記述します。
今回の例では、B列とC列とA列の条件式を使って
=SUMPRODUCT(A:A=”*”&F2&”*”,B:B,C:C)
としたくなりますが、この結果は0になってしまいます。
いくつか理由があるのですが、一つ一つ解決をしていきましょう。
SUMPRODUCT関数はワイルドカードが使えない
そうなんです。SUMPRODUCT関数ではワイルドカード「*」を使うことができませんので
A:A=”*”&F2&”*”
この表現について別の方法を考える必要があります。
いくつか方法はあるのですが、今回はFIND関数とISNUMBER関数を使います。
=FIND(検索文字列,対象)
対象に検索文字列が含まれていればその先頭からの文字数を返します。検索文字列がない場合はエラーを返します。
=ISNUMBER(テストの対象)
テストの対象が数値であるときにTRUEを返します。そうでない場合はFALSEを返します。
商品名に対象とする文字列が含まれている場合はその先頭からの文字数が返されます。そのISNUMBERをとるとTRUEが返るということになります。
これをSUMPRODUCT関数に応用した場合を図に表すとこのようになります。
まずD列はFIND関数でA列のセルに対して”ゴ”が含まれているかを検査します。
該当の文字列が含まれていればその先頭からの文字数を返し、そうでなかえればエラーを返します。
次にE列ではISNUMBER関数でD列を数値かどうか検査をします。
D列の値が数値であればTRUE、数値でなければFALSEを返します。エラーの場合もFALSEが返ります。
F列では単価であるB列、個数であるC列、E列を掛け算しています。
エクセルの四則演算においてTUREは1、FALSEは0と同様に扱われますので、E列がTRUEであればそのフルーツの合計金額、E列がFALSEであれば0が返ります。
このTRUEとFALSEを掛け算することで、条件付きの計算を実現しているのです。
上記をSUMPRODUCT関数で表すと
=SUMPRODUCT(ISNUMBER(FIND(F2,A:A)),B:B,C:C)
となります。
が、これの結果はなぜか0になってしまいます。
実はSUMPRODUCT関数にはワイルドカード以外の罠があるので、それを解決しなければいけません。
純粋に数値以外は0の扱い
SUMPRODUCT関数では純粋な数値以外は全て0と同様に扱われてしまいます。
ですから上記の例では、E列ですね。TRUEかFALSEの値が存在しうるのですが、これらは数値ではありませんので、FALSEはもちろん0、TRUEでも0の扱いになってしまいます。
これを解決する方法ですが、単純です。
=SUMPRODUCT((ISNUMBER(FIND(F2,A:A))*B:B),C:C)
とします。わかりますか？？
ISNUMBER(FIND(F2,A:A))
の配列はTRUEかFALSEかですので、カンマつなぎでSUMPRODUCT関数に計算をさせると全て0の扱いになってしまいます。
一方で先ほどお伝えした通り、TRUEやFALSEはエクセルでの四則演算上では1もしくは0の扱いになりますので
ISNUMBER(FIND(F2,A:A))*B:B
と別の配列との四則演算にすることで、本来の目的を達成することができるわけです。
これで計算できるだろう…などと思っていると、ですがそうはいかずに今度はエラーになってしまいます。
実はもう一つだけ乗り越えないといけない課題があるのです。
見出しがエラーになる
TRUE,FALSEが0として扱われないように
ISNUMBER(FIND(F2,A:A))*B:B
と四則演算を入れたことで、別のエラーが発生してしまったのです。
注目すべきは見出しの行です。
まずA1セルは「商品名」ですから
=ISNUMBER(FIND(F2,A1))
の結果はFALSEになります。
次にB列との掛け算をしています。
=ISNUMBER(FIND(F2,A1))*B1
B1セルは「単価」という文字列ですから、FALSE×文字列という計算になり、その結果はエラーになってしまいます。
エクセルではTRUEやFALSEは1または0として四則演算ができますが、文字列は四則演算はできないのです。
従ってSUMPRODUCTで四則演算を組み合わせて使う場合は、見出し行のない範囲を使う必要があります。
=SUMPRODUCT((ISNUMBER(FIND(F2,A2:A5))*B2:B5),C2:C5)
これでOKです。
もしくは見出し自体をカットして純粋にデータだけに対してならば
=SUMPRODUCT((ISNUMBER(FIND(F1,A:A))*B:B),C:C)
で計算が可能です。
まとめ
条件付きSUMPRODUCT関数の使い方と、様々に出くわすトラップおよびその回避方法についてお伝えしてきました。
ちょっと面倒と思われるかも知れませんが、いくつかのルールさえ覚えてしまえばその発展で相当複雑な条件付きの計算も可能になっていきます。
また別の利点としては、データカラムを無駄に増やさなくて良いという点もあります。
今回のフルーツのように小さいデータ群であればたいした問題はありませんが、何万行もあるようなデータ群に対して計算をする場合では、1つカラムがないだけでもかなりのデータ量の節約にもなりますし、データをスッキリさせることができます。
時と場合に応じてSUMIF（やその発展系のSUMIFS）とうまく使い分けて頂ければと思います。
さて、次回はだいぶ寄り道をしてしまいましたが、Googleアナリティクスのデータからカテゴリ別の平均ページ滞在時間と直帰率をパッと求める方法についてお伝えします。
どうぞお楽しみに！
コメント
順を追った解り易い説明で大変勉強になりました。
さて、そもそもこのページに辿り着いた問題に是非解答頂きたいのですが。。。
何卒宜しくお願い致します。
上記の商品表を他表で引用するために、下記条件にあった項目の行番号が欲しいのです。
「商品名に”ゴ”が含まれている商品名のうち、２個目の商品名が入力されている行数を求める」です。
当方で試してみた式が以下ですが、うまく動かず悩んでいます。
セルE2：=SUMPRODUCT(SMALL((ISNUMBER(FIND(“ゴ”,$A$2:$A$5)))*(ROW($A$2:$A$5)),SUMPRODUCT(NOT(ISNUMBER(FIND(“ゴ”,$A$2:$A$5)))*1)+2))
「計算式の過程を表示 数式の検証」で確認すると、
ISNUMBER(FIND(“ゴ”,$A$2:$A$5))の$A$2:$A$5の部分が配列で処理できておらず、
ISNUMBER(FIND(“ゴ”,”バナナ”))となっていました。
E3に同式を入れた場合にはISNUMBER(FIND(“ゴ”,”リンゴ”))、
E4に同式を入れた場合にはISNUMBER(FIND(“ゴ”,”みかん”))、
と計算式が入っている行の情報しか参照できていないように見えます。
解決方法はあるでしょうか。