[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『配列数式のIF関数』（所子＠１０１家）
お世話になります。さて、次のような表があったとします。
A B C
－－－－－－－－－－－－
1 | 10 1
2 | 1 20 2
3 | 30 3
4 | 1 40 4
5 | 50 5
6 | 1 60 6
7 | 70 7
8 | 1 80 8
9 | 90 9
このとき、次の(1)～(4)の4つのパターンの配列数式を表示させると(4)だけ期待通りになりません。
意図するところは、A列についての条件とB列についての条件の2つの条件を満たすC列の値の合計です。
A列の条件は "1" が入力されていること、B列の条件はある一定の値に満たない（または超えている）ことです。
(1)と(2)は「小なり」、(3)と(4)は「大なり」です。2つの条件のIF関数の入れ子の順序が (1)と(3)が同じ、(2)と(4)が同じです。
(1)
0 ← {=SUM(IF(IF($B$1:$B$9<$B1,$A$1:$A$9)=1,$C$1:$C$9))}
0 ← {=SUM(IF(IF($B$1:$B$9<$B2,$A$1:$A$9)=1,$C$1:$C$9))}
2 ← {=SUM(IF(IF($B$1:$B$9<$B3,$A$1:$A$9)=1,$C$1:$C$9))}
2 ← {=SUM(IF(IF($B$1:$B$9<$B4,$A$1:$A$9)=1,$C$1:$C$9))}
6 ← {=SUM(IF(IF($B$1:$B$9<$B5,$A$1:$A$9)=1,$C$1:$C$9))}
6 ← {=SUM(IF(IF($B$1:$B$9<$B6,$A$1:$A$9)=1,$C$1:$C$9))}
12 ← {=SUM(IF(IF($B$1:$B$9<$B7,$A$1:$A$9)=1,$C$1:$C$9))}
12 ← {=SUM(IF(IF($B$1:$B$9<$B8,$A$1:$A$9)=1,$C$1:$C$9))}
20 ← {=SUM(IF(IF($B$1:$B$9<$B9,$A$1:$A$9)=1,$C$1:$C$9))}
(2)
0 ← {=SUM(IF(IF($A$1:$A$9=1,$B$1:$B$9)<$B1,$C$1:$C$9))}
0 ← {=SUM(IF(IF($A$1:$A$9=1,$B$1:$B$9)<$B2,$C$1:$C$9))}
2 ← {=SUM(IF(IF($A$1:$A$9=1,$B$1:$B$9)<$B3,$C$1:$C$9))}
2 ← {=SUM(IF(IF($A$1:$A$9=1,$B$1:$B$9)<$B4,$C$1:$C$9))}
6 ← {=SUM(IF(IF($A$1:$A$9=1,$B$1:$B$9)<$B5,$C$1:$C$9))}
6 ← {=SUM(IF(IF($A$1:$A$9=1,$B$1:$B$9)<$B6,$C$1:$C$9))}
12 ← {=SUM(IF(IF($A$1:$A$9=1,$B$1:$B$9)<$B7,$C$1:$C$9))}
12 ← {=SUM(IF(IF($A$1:$A$9=1,$B$1:$B$9)<$B8,$C$1:$C$9))}
20 ← {=SUM(IF(IF($A$1:$A$9=1,$B$1:$B$9)<$B9,$C$1:$C$9))}
(3)
20 ← {=SUM(IF(IF($B$1:$B$9>$B1,$A$1:$A$9)=1,$C$1:$C$9))}
18 ← {=SUM(IF(IF($B$1:$B$9>$B2,$A$1:$A$9)=1,$C$1:$C$9))}
18 ← {=SUM(IF(IF($B$1:$B$9>$B3,$A$1:$A$9)=1,$C$1:$C$9))}
14 ← {=SUM(IF(IF($B$1:$B$9>$B4,$A$1:$A$9)=1,$C$1:$C$9))}
14 ← {=SUM(IF(IF($B$1:$B$9>$B5,$A$1:$A$9)=1,$C$1:$C$9))}
8 ← {=SUM(IF(IF($B$1:$B$9>$B6,$A$1:$A$9)=1,$C$1:$C$9))}
8 ← {=SUM(IF(IF($B$1:$B$9>$B7,$A$1:$A$9)=1,$C$1:$C$9))}
0 ← {=SUM(IF(IF($B$1:$B$9>$B8,$A$1:$A$9)=1,$C$1:$C$9))}
0 ← {=SUM(IF(IF($B$1:$B$9>$B9,$A$1:$A$9)=1,$C$1:$C$9))}
(4)
45 ← {=SUM(IF(IF($A$1:$A$9=1,$B$1:$B$9)>$B1,$C$1:$C$9))}
43 ← {=SUM(IF(IF($A$1:$A$9=1,$B$1:$B$9)>$B2,$C$1:$C$9))}
43 ← {=SUM(IF(IF($A$1:$A$9=1,$B$1:$B$9)>$B3,$C$1:$C$9))}
39 ← {=SUM(IF(IF($A$1:$A$9=1,$B$1:$B$9)>$B4,$C$1:$C$9))}
39 ← {=SUM(IF(IF($A$1:$A$9=1,$B$1:$B$9)>$B5,$C$1:$C$9))}
33 ← {=SUM(IF(IF($A$1:$A$9=1,$B$1:$B$9)>$B6,$C$1:$C$9))}
33 ← {=SUM(IF(IF($A$1:$A$9=1,$B$1:$B$9)>$B7,$C$1:$C$9))}
25 ← {=SUM(IF(IF($A$1:$A$9=1,$B$1:$B$9)>$B8,$C$1:$C$9))}
25 ← {=SUM(IF(IF($A$1:$A$9=1,$B$1:$B$9)>$B9,$C$1:$C$9))}
これはどのような動作になっているのでしょうか？
私の配列数式に対する理解が根本的に誤っているのかも知れません。
環境は Win98SE & Excel97 です。
宜しくお願い致します。
括弧内の条件を確認して下さい。下記のようになります。
(1)($B$1:$B$9<$B9,$A$1:$A$9)=1 は括弧内の条件が共にTRUEである条件になり
(2)($A$1:$A$9=1,$B$1:$B$9)<$B1 は括弧内の条件が共に$B1より小さい条件になります。
{=SUM(IF(($A$1:$A$9=1)*($B$1:$B$9<B1),$C$1:$C$9,0))} に相当します。
(3)($B$1:$B$9>$B5,$A$1:$A$9)=1 は括弧内の条件が共にTRUEである条件になり
{=SUM(IF(($A$1:$A$9=1)*($B$1:$B$9>B1),$C$1:$C$9,0))} に相当します。
(4)($A$1:$A$9=1,$B$1:$B$9)>$B1 はA列の値かB列の値が$B1より大きい条件ですから、A列の1は条件から除外されます。
従って、 {=SUM(IF(($A$1:$A$9=0)+($B$1:$B$9>B1),$C$1:$C$9,0))} で求めた結果と同様になります。
($B$1:$B$9<$B9,$A$1:$A$9)=1の 1 は TRUE で、0 は FALSE を意味します。
配列数式では AND は * で、OR は + で入力します。
（シニア）
質問者の所子です。お返事ありがとうございました。
配列数式で * と + で論理積・論理和が使えるのですね。とても勉強になりました。
ただ、表を下記のように改め、式の「=1」の部分を「="○"」に変えてみましたが、同様の結果となります。
「=1」は TRUE/FALSE を判断しているのではないように思いますが、いかがでしょうか？
よろしければ引き続きお願い致します。
A B C
－－－－－－－－－－－－
1 | 10 1
2 | ○ 20 2
3 | 30 3
4 | ○ 40 4
5 | 50 5
6 | ○ 60 6
7 | 70 7
8 | ○ 80 8
9 | 90 9
（所子＠１０１家）
再確認しました。ご指摘の通りです。
TRUE/FALSEが 1/0 で書換えられることを適用しましたが誤りでした。
問題は論理積の条件式の")"の位置の違いで(4)の条件式が満たされなかったのですね。
(1),(2)の式は{=SUM(IF(IF($B$1:$B$9<$B1,$A$1:$A$9=1),$C$1:$C$9))}
{=SUM(IF(($A$1:$A$9=1)*($B$1:$B$9<B1),$C$1:$C$9,0))} に相当します。
{=SUM(IF(IF($B$1:$B$9<$B1,$A$1:$A$9="○"),$C$1:$C$9))}
{=SUM(IF(($A$1:$A$9="○")*($B$1:$B$9<B1),$C$1:$C$9,0))} に相当します。
(3),(4)の式は{=SUM(IF(IF($B$1:$B$9>$B1,$A$1:$A$9=1),$C$1:$C$9))}
{=SUM(IF(($A$1:$A$9=1)*($B$1:$B$9>B1),$C$1:$C$9,0))} に相当します。
{=SUM(IF(IF($B$1:$B$9>$B1,$A$1:$A$9="○"),$C$1:$C$9))}
{=SUM(IF(($A$1:$A$9="○")*($B$1:$B$9>B1),$C$1:$C$9,0))} に相当します。
（シニア）
何度もありがとうございます。
")"の位置についてのご教授、おっしゃる通りでした。ありがとうございます。
ただ、(2)と(4)は同じ書式なのに、どうして(4)だけ結果が異なるのか、
それがわからなかったのですが・・・
例えば、比較する値が「>$B5」（つまり、「>50」）の場合
{=SUM(IF(IF($A$1:$A$9="○",$B$1:$B$9)>$B5,$C$1:$C$9))}
└──┬──┘ │ │
(Ａ) │ │
└───┬───┘ │
(Ｂ) │
└───┬──┘
(Ｃ)
(Ａ) { FALSE, TRUE, FALSE, TRUE, FALSE, TRUE, FALSE, TRUE, FALSE} を返す
(Ｂ) { FALSE, 20, FALSE, 40, FALSE, 60, FALSE, 80, FALSE} を返す
(Ｃ) { TRUE, FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE, TRUE } を返す
配列の 1,3,5,7,9番目の FALSE が TRUE に変わっているのです。
そこで、真偽値を数値と比較した場合、
(真偽値>数値) は必ず TRUE を返し、
(真偽値<数値) は必ず FALSE を返す、
ということがわかりました。
シニアさんの最初のご指摘は目の付けどころはよかったようです。
ただ問題の部分は 「=1」 ではなく 「>$B1」 の方だったようです。
いろいろ勉強になりました。
どうもありがとうございました。
（所子＠１０１家）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.