シリーズでお送りしている”一昔前エクセルシリーズ”でございます。
今回からは「Office365を使っている場合のエクセル2016」、または
「パッケージ版であればエクセル2019」から追加された主な関数を取り上げていきます。
まず始めに強化された条件分岐として使い勝手のいい「SWITCH」関数からいってみましょう。
SWITCH関数を使えば余計な引数がいらなくなる？！
エクセルで条件分岐のできる関数と言えばもちろん最初に
「IF関数」
が思い浮かぶわけですが、「IF関数」の最大の弱点は、3つ以上の条件がある場合に「IF関数」をネストしていかなければならず関数が乱雑になってしまう点です。
例えば、「Aという条件」と「A以外の条件」の2つを処理するのであれば
「IF関数」の「真の場合(=Aという条件)」と「偽の場合(=A以外の条件)」の2つを指定すれば成立します。
上の「IF関数」は「卵が10個ある場合」と「そうでない場合」の2つです。
しかし、「Aという条件」と「Bという条件」、さらに「この2つ以外の条件」という3つ以上を処理しなければならなくなった途端に「IF関数」をいくつも指定する必要が出てきます。
「卵が10個」、「卵が0個」、「それ以外」の3つの場合になると、引数にもう一つ「IF関数」が入り見た目からして複雑な印象を与えてしまいます。
これが、「卵が10個」、「卵が0個」、「卵が100個」、「それ以外」のように4つの条件分岐が必要になると、さらに引数に指定する「IF関数」の数が増えてしまいます。
「条件A」なら「○」、「条件B」なら「?」、「条件C」なら「△」のように、条件に対する答えをそのまま引数に指定できると便利なのに・・・と生まれた関数が「SWITCH」関数となります。
「SWITCH関数」の最初の引数である「式」には、評価をしたいセル番地や数値などを入力します。
上の例で言えば、「セルB1」に入力されている文字を評価したいので、最初の引数には「B1」が入力されています。
後は、「値1」「結果1」、「値2」「結果2」・・・・・「値X」「結果X」のように、「値」の場合の「結果」を必要な数だけ指定していきます。
「セルB1(式)」が「10(値1)」であれば「卵の数は合っています(結果1)」だし、
「セルB1(式)」が「0(値2)」であれば「卵がありません(結果2)」になります。
最後の引数「値3」に、そのまま「買い足しましょう」と指定しています。
「セルB1」の値が「10」でも「0」でもない場合は、この「買い足しましょう」が結果として返されます。
これが「既定値」という意味になります。
このように、「SWITCH関数」を使った条件分岐は、引数に新たに関数を指定しなくていいためパッと見直した時にわかりやすくなっているのです。
SWITCH関数の使い所
さて、「SWITCH関数」を使う場面は「値1」や「値2」などの各「値」の引数に数値を指定する場合に有用になります。
そこで思い出してほしいのが、このシリーズでもお送りしてきた「AGGREGATE関数」の時にその前身となる「SUBTOTAL関数」を紹介した時のお話になります。
「SUBTOTAL関数」は、引数で「集計方法」を指定すると、同じく引数で指定されたリストの集計範囲から、指定した集計方法で計算をする関数でした。
「SUBTOTAL関数」の「集計方法」に指定する数値は
「平均値を求めるAverage」である「1」から
「標本分散を求めるVARP」である「11」までを指定します。
念のために各数値がどういう集計になるのか、数値と関数名を以下に書いておきます。
- 1—AVERAGE
- 2—COUNT
- 3—COUNTA
- 4—MAX
- 5—MIN
- 6—PRODUCT
- 7—STDEV
- 8—STDEVP
- 9—SUM
- 10—VAR
- 11—VARP
上の11個の引数は、「非表示の行を含めずに集計する場合」でした。
そして、「集計に非表示の行も含める場合」は、
上の引数はそれぞれ「101～111」で指定するようになるのです。
以前に書いた「SUBTOTAL」関数の記事では、集計したい数値を入力するとそれに応じて集計方法が切り替わるので、現在はどの集計方法なのかを分かりやすくするために、以下のような関数を設定して集計方法を文字で表示していました。
簡単に説明すると、「平均」、「個数」、「最大」、「最小」、「加算」を集計方法として選択された時だけは、該当のセルにそれらの集計方法を表示する、という関数を上のように作ったのです。
そして、この時に使った関数は「CHOOSE関数」なのですが、この関数を使用すると上の画像のようにおびただしい数の「,(カンマ)」が入力されてしまいます。
それはなぜかと言うと、「1の時は～」、「2の時は～」・・・・・「256の時は～」と言うように、
「CHOOSE関数」は、最初の引数に指定された数値に基づいた値を順番に指定する関数だからです。
例えば、上のように「セルB3」を最初の引数に指定します。
この数値は「4」なので、「CHOOSE関数」の「値4」の引数に入力された値を結果として返します。
その際の、関数式を見てみると、「値1」から「値3」までの引数は省略されていますが、それらは空白として「,(カンマ)」で区切られてしまうので、このような形になるのです。
もし「セルB3」に「100」という数値が入る可能性があって、何らかの値を結果として返したいのであれば、「値100」を指定しておく必要があり、当然その前までの引数を省略しているので、空白としての引数が「,(カンマ)」で区切られていきます。
「SUBTOTAL関数」の記事の時は、前述の通り「101から111」までの数値が指定される可能性があったため、「CHOOSE関数」の「値101」から「値111」までに値を設定する必要がありました。
101に至るまでの引数が例え省略されていてもすべて「,(カンマ)」で区切られてしまうので、あのようなおびただしい数の「,(カンマ)」が出来上がった、というわけです。
その記事の中でも、
「エクセル2016から追加された関数ではこうはならないので、しばらくお待ち下さい」
と予告しておきましたが、あれから2ヶ月半も経ってしまいました。
大変お待たせをいたしました。
それでは、今回の「SWITCH関数」を使って、あの時の「SUBTOTAL関数」の集計方法を表示した関数を書き換えてみたいと思います。
どうでしょうか？
「SWITCH関数」は指定したい値だけを引数に設定できるので、上のように引数の間に「,(カンマ)」が入らずに「CHOOSE関数」と比べてもすっきりと書けるようになります。
同じ条件分岐の関数であっても、
「IF関数」のように関数をネストさせる必要があったり、
「CHOOSE関数」のように、101番目の引数を指定したい場合に例えそれ以前の1から100までを省略したとしても空白として引数を順番に指定しなければならない不便さだったり、
というものが「SWITCH関数」にはありません。
データベースソフトの「ACCESS」には、昔から「SWITCH関数」が用意されていたのですが、エクセルに搭載されるまでこれだけの時間がかかったのが何とも不思議ですね。
つまり、「SWITCH関数」の使い所というのは、これまで見てきたように
処理したい数値だけを引数に指定できる、となりそれが強みでもあるわけです。
SWITCH関数の不便な点は・・
関数式から無駄がなくなって進化した条件分岐となる姿を見せてくれた「SWITCH関数」ですが、不便な点ももちろんあります。
それは、引数に数値の範囲を指定できない点です。
先程見た卵の数による結果の返し方に何か違和感を覚えませんでしたか？
「卵が10個の時は～」、「卵が0個の時は～」、「それ以外は～」という指定でしたが、
「それ以外」の指定が”買い足しましょう”であれば、”卵が20でも100でも買い足すのかい！”と突っ込みたくなる方もいたのではないでしょうか・・。
そう、これが「SWITCH関数」の不便な点なのです。
つまり、「0から9」だったら「買い足しましょう」
「10から19」だったら「ちょうどいい」
「20以上」だったら「十分にあります」
のような数値の範囲指定ができないのです。
これを解消するには、やはり進化した条件分岐の関数に登場していただきますがそれはまた次回、となります。。。
総括
さて、「SUBTOTAL関数」で含みのある宣伝文句から2ヶ月半が経ってからようやく解決となる「SWITCH関数」のお話までたどり着きました。
ものすごくシンプルに使える関数でありながら、”引数に数値の範囲指定ができない”、というちょっと残念な関数でもあります。
それでも、「CHOOSE関数」よりは式が見やすいのは一目瞭然ですね。
「SUBTOTAL関数」のように数値によって集計方法が変わるような場合に、その数値に合わせた処理を行える「SWITCH関数」は実際のところ重宝するかもしれません。
特定の数値で処理を分岐させる場合には「SWITCH関数」とぜひぜひ覚えておいてください。
さて、次回のお話・・・の前に
今回の「SWITCH関数」に繋げるために、あえて「SUBTOTAL関数」の「CHOOSE関数」であのおびただしい「,(カンマ)」を表示しましたが・・・。
実はあの場面、プログラマブルな書き方で関数式を作成すると、
「見た目も超！スッキリ」
とした関数式に変更できるのです。
というわけで、またまた予告となってしまいますが、
この”一昔前エクセルシリーズ”の番外編としてシリーズの一番最後で以下をお送りする予定です。
題して「再検証！あのCHOOSE関数の引数は本当に必要だったのか！」
実はあんなアホみたいな引数の指定をしなくても、同様の結果を得られるように関数式を作成できるのです。
もちろん、関数はそのまま「CHOOSE関数」を使いますよ。
とまぁ、最終回の前にまだもう少し続くこのシリーズ。
次回は、今回補えなかった
「引数に数値の範囲を指定」しながら
シンプルに関数式を作成できる
「IFS関数」の登場となります。
どうぞお楽しみに！
