エクセルごはん初回（もしもSUMIF関数が使えなかったら）

はじめまして。
エクセルごはんです。
 
このnoteは、世間でブームを巻き起こした謎解き問題のように、子供から大人まで楽しめるExcelの問題を作りたい、そんな想いから始まりました。
易しい問題から難しい問題、はたまた奇問まで、多種多様なExcelの問題を通して、読者の皆様のExcel力をゴリゴリ鍛えていこうと思っています。
 
初回の今回は、「もしもSUMIF関数を使うことを禁じられていたなら」という問題を取り上げます。
 
まずは、次のExcel問題を考えてみてください。
 

セルB２に、果物。
セルB３からセルB９に、りんご、みかん、ぶどうが適当に配置されています。
セルC２に、個数。
セルC３からセルC９に、適当に配置された、りんご、みかん、ぶどうの個数が記されています。
 
セルE２に果物。
セルE３はリストです。ここでは、りんごが表示されていますが、りんご、みかん、ぶどうの３種類から選択できるように、データの入力規則から設定してください。データの入力規則は、データタブから、データの入力規則を選び、入力値の種類に、リストを選び、元の値に、
りんご,みかん,ぶどう
と入力することで設定できます。
 
そして、セルF３が、リストで選んだ果物の個数、りんごは何個か、みかんは何個か、ぶどうは何個かを求める箇所です。
 
セルF３が今回の問題の解答欄ということになります。
このF３にSUMIF関数を使わないで、選択されたリストの個数の合計を求めます。
 
どうですか？出来ましたか？
問題を今一度おさらいしておきましょう。
 

B列に、各果物の名前が入っています。
C列に、各果物の個数が入っています。
セルE３はリストです。りんご、みかん、ぶどうから選択します。
セルF３はリストで選択された果物の個数の合計を表示します。
りんごなら120個、みかんなら70個、ぶどうなら40個です。
 
どうでしょう？
SUMIF関数を使わないで、上手く合計が求まりましたでしょうか？
 
ここからは、解説に入ります。
まず、SUMIF関数を使った答えから解説していきます。
セルF3に、
=SUMIF($B$3:$B$9,$E$3,$C$3:$C$9)
と入力します。
SUMIF関数の範囲に、表の果物を選択し、検索条件にセルE３を選択し、合計範囲に表の個数を選択します。
これだけで、合計が求まります。
 
$記号は絶対参照のマークです。セルを固定しています。ここでは、セルを相対参照のように動かす必要がないので絶対参照にしています。絶対参照と相対参照の切り替えはF４キーを押すことで出来ます。
さて、では、この便利なSUMIF関数が使えない場合はどうするか？
答えは、配列数式を使います。
ここからは、配列数式を使った解答を解説していきます。
 
配列数式を使うには、数式を入力後、CtrlとShiftとEnterを押して確定する必要があります。
セルF３に、
=SUM(IF($B$3:$B$9=$E$3,$C$3:$C$9))
と入力してみてください。SUMIF関数を使った場合と同様の値が表示されたはずです。
 
どういう仕組みになっているのか詳しく見ていきましょう。
まず、
$B$3:$B$9=$E$3
という配列数式を考えてみましょう。
配列数式なので、数式を入力後にCtrlとShiftとEnterキーを押す必要がありますよ。
この配列数式が返す値が何になるか分かりますか？
セルE３のリストで選択された果物がりんごの場合を例に考えてみましょう。
適当なセルに、
$B$3:$B$9=$E$3
と入力して、CtrlとShiftとEnterキーを押すと、セルにTRUEと表示されるので、TRUEがこの配列数式が返す値だと思っていませんか？
実は、TRUE　FALSE　FALSE　TRUE　TRUE　FALSE　FALSE
が返ってくる値なのです。
配列数式という名の通り、返ってくる値が配列になっているわけです。
 
配列数式の返す値を調べるには、CONCAT関数を使って知ることができます。
適当なセルに、
=CONCAT($B$3:$B$9=$E$3)
と入力して、CtrlとShiftとEnterキーを押すと、
TRUEFALSEFALSETRUETRUEFALSEFALSE
と表示されたはずです。
 
区切りのない表示が見にくい場合は、TEXTJOIN関数を使うことができます。
適当なセルに、
=TEXTJOIN(",",TRUE,$B$3:$B$9=$E$3)
と入力して、CtrlとShiftとEnterキーを押すと、
TRUE,FALSE,FALSE,TRUE,TRUE,FALSE,FALSE
と表示されたはずです。
 
CONCAT関数とTEXTJOIN関数は、配列数式の中身がどうなっているかを確認するのに便利な関数ですので、覚えておいてください。
 
さて、では、
=IF($B$3:$B$9=$E$3,$C$3:$C$9)
という配列数式が返す値は何になるでしょうか？
セルE３のリストで選択された果物がりんごの場合を例に考えてみましょう。
適当なセルに、
=IF($B$3:$B$9=$E$3,$C$3:$C$9)
と入力して、CtrlとShiftとEnterキーを押すと、セルに30と表示されますが、この値が配列数式が返す値ではないですよね。
30　FALSE　FALSE　40　50　FALSE　FALSE
が返ってくる値になります。
配列数式という名の通り、返ってくる値が配列になっているわけです。
 
セルB３の値は、りんごなので、セルB３とセルE３の値が等しく、IF関数の条件式が真となり、セルC３の値、30を取得します。
セルB４の値は、みかんなので、セルB４とセルE３の値が等しくなく、FALSEが返ります。
セルB５の値は、ぶどうなので、セルB５とセルE３の値が等しくなく、FALSEが返ります。
セルB６の値は、りんごなので、セルB６とセルE３の値が等しく、IF関数の条件式が真となり、セルC６の値、40を取得します。
セルB７の値は、りんごなので、セルB７とセルE３の値が等しく、IF関数の条件式が真となり、セルC７の値、50を取得します。
セルB８の値は、みかんなので、セルB８とセルE３の値が等しくなく、FALSEが返ります。
セルB９の値は、ぶどうなので、セルB９とセルE３の値が等しくなく、FALSEが返ります。
 
このようにして得られた値を、あとはSUM関数に渡して合計してあげれば良いわけです。
=SUM(IF($B$3:$B$9=$E$3,$C$3:$C$9))
で、セルE３がりんごの場合、30+40+50で120を得ます。
 
セルE３がみかんの場合は、20+50で70を得ます。
セルE３がぶどうの場合は、10+30で40を得ます。
 
おつかれさまでした。
初回の第一回目は、配列数式を使った問題を取り上げましたが、今後も配列数式を使った問題を取り上げていきます。
配列数式なんて分からないという方も、何十回かの問題を通して、配列数式を十分に知って頂けたらと思います。
また、今回の問題が簡単だった方も、ちょっと骨のある配列数式の問題も考えていますので、ご安心ください。
次回の投稿でもExcelの問題を出題していきますので、楽しみにしていてください。
それでは。