Excel Maniacs

循環参照入門 （5）循環参照で複数条件に合致するデータ数を求める

循環参照を極める

（5）循環参照で複数条件に合致するデータ数を求める 　 下準備： １．メニューを「ツール」「オプション」と辿り、ダイアログボックスをだす。「計算方法」タブを選択。 ２．「計算方法」タブを選択し「反復計算」にチェックをし、「最大反復回数」を１００にして「OK」をクリック。 ３．下図のようにB2に=IF(A1,A2+1,0)と入力する。 ４．チェックボックスをワークシートに貼り付けて「コントロールの書式設定」から「リンクするセル」をA1にする。

【´･ω･`】：CountIf関数では設定できる条件の数はひとつなので60点以上の者の数を求めることはできるが60点以上70点未満の者の数を求めることはできない。そこでよくある手は多少、トリッキーだがSumProduct関数を使う手がよく用いられる。 (●`ε´●):こんな感じだ。セルE2に

=SUMPRODUCT(((C2:C43)>=60)*((C2:C43)<70))

何て式を入力する。他には配列数式を使って

｛=SUM(((C2:C43)>=60)*((C2:C43)<70))｝

・・・とする。蛇足だが配列数式の｛｝は手入力しない。=SUM(((C2:C43)>=60)*((C2:C43)<70))　とセルに入力して確定時にShift＋Ctrl＋ENTERを同時に押下する。

【´･ω･`】：そして第三の手が循環参照による反復計算。SumProductや配列数式は質問掲示板でよく見かけるが循環参照はまだ珍しいと思う。下が循環参照による数式だ。

=IF(A1,IF(AND(INDIRECT("C" & $A$2)>=60,INDIRECT("C" & $A$2)<70),D2+1,D2),0)

【´･ω･`】：これでチェックボックス「実行」にチェックをいれれば複数条件に合致するデータ数が求められるはずだ。

サンプルファイルはこちら：Ref_CountIfsをダウンロード

（初出　2007/03/11）

循環参照入門 （４）循環参照でデータ数・最大値・最小値を求める

循環参照を極める

（3）循環参照でデータ数・最大値・最小値を求める 　 下準備： １．メニューを「ツール」「オプション」と辿り、ダイアログボックスをだす。「計算方法」タブを選択。 ２．「計算方法」タブを選択し「反復計算」にチェックをし、「最大反復回数」を１００にして「OK」をクリック。 ３．下図のようにB2に=IF(A1,A2+1,0)と入力する。 ４．チェックボックスをワークシートに貼り付けて「コントロールの書式設定」から「リンクするセル」をA1にする。

４．データ数をカウントするためにセルD2に=IF(A1,IF(INDIRECT("C" & A2+1)<>"",D2+1,D2),0)と入力する。 ５．最大値を求めるためにセルE2に=IF(A1,IF(INDIRECT("C"&D2+1)>E2,INDIRECT("C"&D2+1),E2),C2)と入力する。 ６．最小値を求めるためにセルF2に=IF(A1,IF(INDIRECT("C"&D2+1)<F2,INDIRECT("C"&D2+1),F2),C2)と入力する。

【´･ω･`】：これでチェックボックス「実行」にチェックをいれればデータ数・最大値・最小値が求められるはずだ。

サンプルファイルはこちら： Maxをダウンロード

（初出　2007/03/10）

循環参照入門 （3）１から１０までの整数の和を求める

循環参照を極める

（3）循環参照で１から１０までの整数の和を求める 　 下準備： １．メニューを「ツール」「オプション」と辿り、ダイアログボックスをだす。「計算方法」タブを選択。 ２．「計算方法」タブを選択し「反復計算」にチェックをし、「最大反復回数」を１０にして「OK」をクリック。 ３．下図のようにB2に=IF(A1,B2+1,0)と入力する。

４．下図のようにB3に=IF(A1,B3+B2,0)と入力する。

【´･ω･`】：これでA1に0以外の数を入力すると１から１０までの整数の和がB3に表示されるはずだ。

(●`ε´●)：0以外の数？なんでだ？ 【´･ω･`】：IF関数の第一引数はTRUEかFALSEの真偽値を判定する。実はFALSEは数字で言えば0と同じで、それ以外がTRUEと同じだから。 ★サンプルファイルはこちら (●`ε´●)：ふ～ん。 【´･ω･`】：それでは式の解説をしよう。まずはB2の式から。B2の式はA1が真ならB2+1を実行せよ。そうでなければ０と表示せよ、という意味だ。 (●`ε´●)：それはわかる。 【´･ω･`】：ここで大事なのはB2自身にB2+1と書き込まれている点だ。 (●`ε´●)：循環参照になっているというわけだな？ 【´･ω･`】：B2の初期値が０だとするとさっき「最大反復回数」を１０にしたから…。 (●`ε´●)：1回の反復でB2にプラス１されるわけだから、０に10回、１を足して１０になるというわけだ。 【´･ω･`】：そういうこと。それでは次にまずはB３の式=IF(A1,B3+B2,0)。下の表を見てほしい

B2の値 B3の値 ＝B3+B2 1回目 1 1 =0+1 2回目 2 3 =1+2 3回目 3 6 =3+3 4回目 4 10 =6+4 5回目 5 15 =10+5 6回目 6 21 =15+6 7回目 7 28 =21+7 8回目 8 36 =28+8 9回目 9 45 =36+9 10回目 10（終了値） 55（答え） =45+10

(●`ε´●)：なんだB2が変数i、B3が変数Ansだと思えば前回の表と同じじゃないか。 【´･ω･`】：そう。発想が同じだからね。以上で大事なところは終わり。あとは本質的なことではないけれどＡ１の値を直接書き換えるよりもチエックボックスを使った方がちょっと見栄えがいいかな。チェックを入れると計算が行われ、チェックをはずすとリセットされるよ。

（初出　2006/03/21）