教えて!HELPDESK      Excel     Word      Outlook      Power Point      Windows

 

 

Excel2010-2016:SUMPRODUCT関数でOR条件を使う

   

 

 

 

 

 


 

 

 

 

 


 

 

 

SUMPRODUCT関数でOR条件を使う方法をご紹介します。

AND条件 … SUMPRODUCT((配列○の条件)*(配列○の条件))

OR条件 … SUMPRODUCT((配列1の条件)+(配列1の条件)★条件が同一フィールド

OR条件 … SUMPRODUCT((配列1の条件)+(配列2の条件))-SUMPRODUCT((配列1の条件)*(配列2の条件)) ★条件が異なるフィールド


 例:3歳以下または60歳以上は何人?区内または4歳以下は何人?(OR条件)

 

★ 条件が同じ配列(フィールド)なら足せばカウントできます。

◆ 3歳以下または60歳以上

SUMPRODUCT((C2:C7<=3)+(C2:C7>=60))

解説:配列の各要素の結果がTrue(1)False(0)で返され、それを配列数式で足したものを合計する → 結果3人

年齢 3以下   60以上   結果
3

60

35

4

21

82

1

0

0

0

0

0

0

1

0

0

0

1

= 1

1

0

0

0

1

 

★ 条件が異なる配列(フィールド)では重複することがあるので、足した後に重複分を引く必要があります。

◆ 区内または4歳以下

SUMPRODUCT((C2:C7<=4)+(D2:D7="区内"))-SUMPRODUCT((C2:C7<=4)*(D2:D7="区内"))

 

足すだけでは「区内」かつ「4歳」の人が2回カウントされてしまう(区内で1、4歳以下で1)ので【区内の4歳】分を引きます。

解説:配列の各要素の結果がTrue(1)False(0)で返されたものを足し、重複分を引いて合計する → 結果5人

区内   年齢 4以

     

  結果

内内

0

1

0

1

1

1

3

60

35

4

21

82

1

0

0

1

0

0

= 1

1

0

2

1

1

- 0

0

0

1

0

0

= 1

1

0

1

1

1

 

 仕組み

SUMPRODUCT関数は各配列の条件が合致するとTrue(1)、合致しないならFalse(0)で返します。それを掛けると1×1の場合のみ1が返るので(0×1も0×0も0)AND条件で計算できます。OR条件では同じフィールドの場合は足す(1+0、0+0)と1が返る場合に合計することでカウントできます。異なるフィールドのOR条件は0+1または1+0であればOKですが、どちらのフィールドにも合致すると1+1となってしまうので、その分引く必要があります。

@ AND条件 … 配列1の結果と、配列2の結果を掛ける→どちらにも合致した場合のみ1×1=1が返るのでこれを配列計算できます。

A OR条件 … 配列1の結果と、配列2の結果を足す → どちらかに合致した場合に1以上が返る(0+1か1+1)のでこれを配列計算できます。但しどちらにも合致した分が重複してしまうので、これを引く必要があります。(注:引くのは異なるフィールド(配列)の複数条件の場合。同じフィールド(配列)のOR条件では引く必要はありません)

 

 

教えて!HELPDESK      Excel     Word      Outlook      Power Point      Windows