以上以下の時のIFS関数の書き方

Posted on

ExcelでIFS関数を使ってみたけれどうまく動かないとのヘルプ。

// IFS関数  
= IFS ( 条件1, 値1, 条件2, 値2, 条件3, 値3, ........ )

IFS関数はこんな感じ。
これまでだったらIFの入れ子にしていかなきゃいけなかったとものをすっきりさせることができる関数。

やりたかったこと

  • 人数によって料金が変動する表がある　ー表A
  • 日別で人数を入力していくリストがある　ー表B

仮に表A、Bは下記の通りとする。

表A

人数 料金
1〜4 1,000
5〜10 2,000
10〜 3,000

表B

日付 名前 人数 料金
5/1 aaa 5 2,000
5/2 bbb 7 2,000
5/3 ccc 15 3,000
5/4 ddd 4 1,000

表Bに行を追加するたびに、人数に応じて料金を自動で入力されるようにしたい。(表B-料金列)
そこで表B-料金列にExcelのIFS関数を利用して、条件に応じて料金が自動で表示されるようにしたかったらしい。

動かなかった関数

= IFS ( 1<=A3<5, 1000, 5<=A3<10, 2000, A3>10, 3000 )

結論から言うと条件の指定の仕方が違う。
「1以上5人未満」という条件は1<=A3<5という書き方はできない。

IFS関数の条件をAND関数に変えてみた

A3を標的とした「1以上5人未満」という条件には、

  • A3が1以上である…1<=A3
  • A3が5未満である…A3<5

という2つの条件が入っており、それぞれを独立した条件として書き、それをANDな条件(かつ)とする必要がある。

AND( 条件1, 条件2 )

だから上の式を「日本語の通り」にExcel上で動くようにするには、

= IFS ( AND(1<=A3, A3<5), 1000, AND(5<=A3, A3<10), 2000, A3>10, 3000 )

のような感じになる。
※この式の場合A3に0以下が入力されることを考えていない。実際0以下が入力されると#N/Aが返るはず。

けど、これに関してはこんな面倒な式にしなくても大丈夫。

もっと簡単に書けたIFS関数

先ほどの式をJavascriptで似たようなことを再現しようとするとこんな感じか。

var count;
var money = '0';

function hoge(count) {
    // 条件1
    if (count>= 1 || count < 5) {
        money = 1,000;
    }
    // 条件2
    else if (count>= 5 || count < 10){
        money = 2,000;
    }
    // 条件3
    else {　
        money = 3,000;
    }
    console.log(money );
}

hoge(4); //1,000が出力される

プログラムをかじったことのある人ならわかると思うのだけれど、よくあるif文で、これがどういう順番で動作していくかというと、

  1. 条件1に合致した場合、moneyには1,000が代入され、条件2以降は実行されない
  2. 条件2に合致した場合、moneyには1,000が代入され、条件3以降は実行されない
  3. 条件3に合致した場合、moneyには1,000が代入される
    4.どの条件にも合致しない場合はmoneyには0のまま

さて、ここでIFS関数の説明をもう一度見直す。

IFS 関数は、1 つ以上の条件が満たされているかどうかをチェックして、最初の TRUE 条件に対応する値を返します。IFS は、複数のネストした IF ステートメントに置き換えることができるため、複数の条件を読み込むのがより簡単です。
「IFS 関数 – Office サポート」より引用

冒頭の「IFS 関数は、1 つ以上の条件が満たされているかどうかをチェックして、最初の TRUE 条件に対応する値を返します。」がここで大事なところ。

つまり先ほどのJavascriptと同じで、初めに条件が合致した場合に処理が行われ、それ以降は処理されない。
だから、先ほどのJavascriptをちょっと直してあげると、

var count;
var money = '0';

function hoge(count) {
    // 条件1
    if (count < 5) {
        money = 1,000;
    }
    // 条件2
    else if (count < 10){
        money = 2,000;
    }
    // 条件3
    else if (count >= 10) {　
        money = 3,000;
    }
    console.log(money );
}

hoge(4); //1,000が出力される

こんなイメージか。条件の部分(ifとかelse if)が短くなったのがわかる。
※条件3の形が変わったのはelseのままだと、countが0以下が入った場合にも3,000が代入されてしまうから。

このスクリプトと同じような考え方でいいので、ExcelのIFSの部分は次のように書くことができる。

= IFS ( A3<5, 1000, A3<10, 2000, A3>10, 3000 )

だいぶスッキリしたのではないだろうか。
ただしこれだとA3に0以下の数字が入っても1,000を返してしまうので、ちゃんとするならば、条件1だけAND関数でAND(A3>0,A3<5)みたいにするか、条件1の前に条件0を追加して、IFS( A3<1, 0, A3<5, 1000,………)のようにしてあげたらいい。

この考え方のポイントになるのは、条件は満たすのが難しい方から書いていく、ということ。例えば先ほどのIFSを逆に書いていって、

= IFS ( A3>10, 3000, A3<10, 2000, A3<5, 1000 )

としてしまうと、A3(人数)が1～9の時に2番目のA3<10に合致してしまうのですべて2,000が返ってしまう。
なので、IFS関数を使うときは各順番を一度整理してから設定するのが大事です。

参考サイト