EXCEL生産性向上委員会ちょっと便利テクニック21-2

12/23/2002 -
21-2.複数の条件を指定した各種計算を行う
・	21および21-1のページで、配列数式という機能がだいたいどのようなものか分かって（分かりかけて）来たという前提で、実例を紹介していこうと思います。
・	ここでは、右の表のようなデータをサンプルとして用います。
	この表から、色々な条件で件数や平均値・最大値を求めてみます。結果を表示させたい場所（例えばセルD13）に、以下の式を入力します。（配列数式の場合は入力する式を｛と｝で囲って記述していますが、｛と｝は実際には入力する必要は無く、式の入力の最後にShiftキーとCtrlキーの両方を「押しながら」Enterキーを押すということに注意してください）
		配列数式を使わない方法	配列数式を使う方法
(a)	東京都在住の人数	最初に、住所が東京都の人数の求め方です。この場合は、配列数式を使わないほうが簡単ですね。
		=COUNTIF(C2:C11,"東京都*")	{=SUM(IF(LEFT(C2:C11,3)="東京都",1,0))}
	東京都在住の男性の人数	次に、条件を１つ加えて、東京都の男性の人数を求めます。配列数式の方は、１つのＩＦ文で＊を使って論理積（AND）の結果を条件とするやり方と、ＩＦ文を２つネストするやり方があります。
(b1)		=SUMPRODUCT((LEFT(C2:C11,3)="東京都")(D2:D11="男")1)	{=SUM(IF((LEFT(C2:C11,3)="東京都")*(D2:D11="男"),1,0))}
(b2)			{=SUM(IF(LEFT(C2:C11,3)="東京都",IF(D2:D11="男",1,0),0))}
(c)	千葉県または神奈川県の在住人数	(a)の応用で、住所が千葉県あるいは神奈川県の人数の求め方です。論理和（OR）条件となるので、条件の結果を＋で足して判定しています。
		=SUMPRODUCT(((LEFT(C2:C11,3)="千葉県")+ (LEFT(C2:C11,4)="神奈川県"))*1)	{=SUM(IF((LEFT(C2:C11,3)="千葉県")+ (LEFT(C2:C11,4)="神奈川県"),1,0))}
(d)	30歳以上の人の身長の平均	今度は、数値の条件です。配列数式を使う場合は素直にAVERAGE関数に条件を指定すれば良いのですが、配列数式を使わない場合は30歳以上の人の合計を30歳以上の人の人数で割って平均を求めます。
		=SUMIF(E2:E11,">=30",F2:F11)/COUNTIF(E2:E11,">=30")	{=AVERAGE(IF(E2:E11>=30,F2:F11))}
	20歳台の人の身長の平均	(d)をちょっとアレンジして20歳台とした場合、ちょっと複雑になります。
(e1)		=(SUMIF(E2:E11,">=20",F2:F11)-SUMIF(E2:E11,">=30",F2:F11))/ (COUNTIF(E2:E11,">=20")-COUNTIF(E2:E11,">=30"))	{=AVERAGE(IF((E2:E11>=20)*(E2:E11<30),F2:F11))}
(e2)		=SUMPRODUCT((E2:E11>=20)(E2:E11<30)F2:F11)/ (COUNTIF(E2:E11,">=20")-COUNTIF(E2:E11,"<30"))	{=AVERAGE(IF(E2:E11>=20,IF(E2:E11<30,F2:F11)))}
(f)	20歳台の男性の身長の平均	ここから下の例は、配列数式を使わないと一発では求められないと思います。
		{=SUM(IF((D2:D11="男")(E2:E11>=20)(E2:E11<=29),F2:F11,0))/ SUM(IF((D2:D11="男")(E2:E11>=20)(E2:E11<=29),1,0))}
(g)	男性の体重の最高値	男ではない場合、 D2:D11="男"の部分は偽（False、つまり0）になり、男の場合は真（True、つまり１）になるので、それと体重を掛けた結果の中から最大値を求めます。
		{=MAX((D2:D11="男")*G2:G11)}
(h)	身長170cm未満の人の体重の最高値	数値の比較条件の場合は、このようになります。
		{=MAX((F2:F11<170)*G2:G11)}
(i)	女性の体重の最低値	最低値の場合、IFの条件部分が偽（False、つまり0）だとその0が最低になってしまうので、ここでは条件がFalseの場合は999として最低を求めています。
		{=MIN(IF(D2:D11="女",G2:G11,999))}

	他にも実例が思いついたら追加していきます。。。


	Web excel7.com