シリーズでお送りしている”一昔前エクセルシリーズ”でございます。
とうとう、本当の最終回となりましたが、以前より告知しておりました
「番外編：再検証！あのCHOOSE関数の引数は本当に必要だったのか！」
をお送りいたします。
このページから見た方は、
”なんのこっちゃ”
となると思いますので、ちょっと長くなってしまいますが、以下の2つのページ達を順番にご覧いただくと内容がしっかりつかめるかもしれません。
「AGGREGATE」関数の動作には、エクセル2010以降が必要です。
「SWITCH」関数の動作には、エクセル2019以降(Office365はエクセル2016以降)が必要です。
簡単に要約すると、
「AGGREGATE」関数の説明内で使った「CHOOSE」関数が、引数の数が多すぎて「，(カンマ)」だらけになってしまったんですね。
そんな「CHOOSE」関数に代わる新しい関数として「SWITCH」関数をご紹介したわけですが・・・。
そもそも「CHOOSE」関数の引数指定に、なんか工夫があればあんな見づらい関数式にはならなかったんではないか、という検証をもう一回行おうとしているわけです。
結論から言えば、もちろんできます。
エクセルの使い手の方やプログラマーなどの技術者系の方が、万が一あの「CHOOSE」関数のページを見たら
”そんなことしなくても、こうしたらいいのに・・”
と言うであろうやり方があるのです。
さて、それでは早速すっきりした関数式にするための手順を見ていきましょう。
もう一度CHOOSE関数をおさらい
まずは、前回の「CHOOSE」関数でどういう指定をしたかをおさらいしておきます。
まず、北海道の各市町村の人口リスト(A列からD列)があって、その中から例えば最大の人口数や最小の人口数、市町村の個数などを「SUBTOTAL」関数を使って表示できるようにしています。
「セルF3」に求めた数値が表示されるように、上の画面のように関数を設定しています。
「セルF2」に、「1から11」もしくは「101から111」の数値を入力してもらい、入力された数値に応じてリストの計算を行います。
この入力する数値は、「SUBTOTAL」関数の引数であり、「1であれば平均」、「2であれば個数」のように各数値によって計算方法が決まっています。
【SUBTOTAL(計算方法の数値,集計対象)】となります。
ちなみに、前回の記事にも記載しましたが、計算方法の数値は以下のような対応となります。
改めて記載しておきます。
- 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」で指定するようになるのです。
「セルE3」には、集計方法の内
「平均」、「個数」、「最大」、「最小」、「合計」
の5つの場合だけ、「集計方法の名前」を表示するようにしていました。
例えば、上の画面を見て頂くとお分かりのように、
「セルF2」に「4」と入力されたら、それはリスト内から「最大値」を求めたい場合です。
でも、「4」だけでは分かりづらいから、
「セルE3」に集計方法である「最大」を表示するようにしているのです。
さぁ、それでは「セルF2」に入力された数値によって、「セルE3」に表示する集計方法をどのように表示するようにしたのか、設定した関数を見てみましょう。
「IF」関数は、「セルF2」に入力される数値が該当の数値かどうかを判断しています。
該当の数値であれば「CHOOSE」関数を実行するようにしています。
「CHOOSE」関数は、「値1」から「値254」までの引数にそれぞれ対応する文字や数値を指定します。
最初の引数の「インデックス」に入力された数値(X)に対応する「値X」を返す関数となるのです。
例えば、「インデックス」の引数に「2」と指定したら、「値2」の引数に指定した文字や数値を返すのです。
集計を司る「SUBTOTAL」関数の「集計に非表示の行も含める」場合の指定に「101～111」があるので、「セルF2」にこれらの数値が入力される可能性がありますよね。
そうすると、それぞれ「非表示の行を含めずに集計するかしないか」の違いはあれど、
「セルF2」に「1」と入力される場合も「セルE3」には「平均」と表示したいし、
同じく「セルF2」に「101」と入力された場合も「セルE3」には「平均」と表示したいのです。
そんな「セルE3」に設定した「CHOOSE」関数で「値101」に何らかの値を指定すると、それまでの「値1」から「値100」までは何も指定されていなくても、「空」ということで「，(カンマ)」が続いてしまうのです。
例えば、「CHOOSE」関数で「値1」と「値5」の引数に文字を指定し、間の「値2～値4」の引数には何も指定しない場合は、以下のようになります。
【CHOOSE(インデックス,1,,,,5)】
「値1」に1、「値5」に5を指定したとします。
その間の「値2～値4」は何も指定しないので「値5までは空っぽだよ」という意味で「,(カンマ)」が続きます。
もし、「値254」だけに引数を指定した場合は、それまでの253個は「空っぽ」の「,(カンマ)」が続きます。
あのおびただしいカンマから解放されるには
前置きが長くなりましたが、ここからあの「CHOOSE」関数を修正していきます。
まず、今回「CHOOSE」関数の修正を行うために目を付けた部分として以下が挙げられます。
「規則性のある数値2つに同じ文字が割り当たっている」
どういうことかというと、今回の場合、
「SUBTOTAL」関数の集計方法を指定する「最初の引数」に規則性があります。
「1」と「101」が「平均」
「2」と「102」が「個数」
「4」と「104」が「最大」
のように、ある数値とそれに100を足した数値に同じ文字が割り当たっているのです。
そして、「CHOOSE」関数では、それぞれの引数に文字を割り当てて判断しています。
それを踏まえた上で、先程見た「CHOOSE」関数をすっきりした形に修正します。
とりあえず、「IF」関数などは省いて、「CHOOSE」関数だけをクローズアップしています。
【CHOOSE(F2-100*(F2>=101),”平均”,”個数”,,”最大”,”最小”,,,,”加算”)】
どうでしょうか？
「CHOOSE」関数の「値X」の引数には、「値1」から「値9」までの設定しかしていません。
したがって、「値9」以降のおびただしいカンマがすっかり消えています。
では、「SUBTOTAL」関数の集計方法である「101」以降が指定された場合は、どうやって「CHOOSE」関数で「101」を判断しているのでしょうか。
それは、「CHOOSE」関数の最初の引数である「インデックス」に一手間加えて対処しています。
F2-100*(F2>=101)
最初は、「セルF2」に入力された数値をそのまま
「CHOOSE」関数の「インデックス引数」
として判断していましたね。
でも、よくよく考えてみると、「セルF2」に入力された数値が「1」でも「101」でも「平均」なのであれば、
どちらの数値も「CHOOSE」関数の「インデックス引数」では「1」として処理してあげればいいだけなのです。
それが、上の式になります。
数値の引き算や掛け算の中に「F2>=101」と計算式ではちょっと見慣れない不等号がありますね。
これは、「正(TRUE)」か「負(FALSE)」かの結果を数値として処理できます。
つまり、「セルF2が101以上」であれば「正」となり、エクセルの場合「1」を返します。
逆に、「セルF2が100以下」であれば「負」となり、「0」を返します。
簡単に言うとコンピュータは2進数の世界であり、「0」か「それ以外か」の連続処理で動いています。
プログラム言語によっては、「0」以外を「1」で扱ったり「-1」で扱ったりとありますが、エクセルでは0以外を1で扱っています。
さて、それでは「セルF2が1」の場合を式に当てはめてみます。
F2-100*(F2>=101)↓
1-100*0=1-0
つまり、答えは「1」ですよね。
それでは、「セルF2が101」の場合です。
F2-100*(F2>=101)↓
101-100*1=101-100
答えはやはり「1」になります。
そう、「セルF2」に「1」と入力されても、「101」と入力されても、
「CHOOSE」関数の「インデックス引数」に指定する段階でどちらも「1」として処理してあげれば、「値1引数」に「平均」とするだけで「値101引数」の設定はいらなくなる、というわけです。
同じように、「セルF2」に「2」と入力された場合や「102」と入力された場合など、色々試してみてください。
すべて「CHOOSE」関数の「インデックス引数」に指定した
「F2-100*(F2>=101)」
の式により「1」や「2」などの小さい方の数値だけを「インデックス引数」として処理できるはずです。
総括
さて、いかがでしたでしょうか。
「正」と「負」つまり「TRUE」と「FALSE」を計算式内で上手に使うと、今回のような計算ができる、というお話でした。
意味の分からない方は、数字のマジックのように見えるかもしれませんが、
それ程難しい話ではありません。
「条件が違う(0)」場合の計算と「条件があっている(1)」場合の両方を一つの計算式で実行できるようにしているだけですから。
ちなみに、以下のような判断をしたい場合、「CHOOSE」関数の「インデックス引数」にはどのような数式を設定したらいいと思いますか？
「セルF2」に入力された数値が
「1であればセルH2に東京」
「2であればセルH2に神奈川」
「3であればセルH2に埼玉」
「101であればセルH2に千葉」
千葉は「101」と飛んでいますね(笑)
でも、「CHOOSE」関数の「値101引数」に「千葉」と設定すると、それまでの引数におびただしい数の「，(カンマ)」が入ってしまいます。
はい、正解は↓です。
入力される数値が「101」であっても、「CHOOSE」関数の「インデックス引数」でそれを「4」になるように変換すればいいだけです。
「F2=101」なら「TRUE」つまり「1」となり、
「101-97*1=4」
で、「インデックス引数」を「4」と変換できます。
後は、「CHOOSE」関数の「値4」に「千葉」と設定しておけば、
「値101」に「千葉」と設定しなくても問題ないわけです。
この「正」、「負」を数式に含めて計算する方法は、結構応用が利きますので、
仕事でエクセルを使っている方は、色々と試してみてくださいね。
