Toshiyuki Ihara's Excel計算の技その２

Welcome to Toshiyuki Ihara's Excel計算の技その２

「y = A cos(Bx) * exp(-Cx) における極大、極小を与える(x,y)をxの小さい順に並べる」

イントロ（サンプル１の説明）	次に、「y = A cos(Bx) * exp(-Cx) における極大、極小を与える(x,y)をxの小さい順に並べる」という課題を取り上げます。この課題では、判別式を実現する「IF」関数と、探索を実現する「LOOKUP」関数を使います。この２つを使いこなせれば、極大や極小を探す・方程式を解く・曲線の交点を探す、いった問題を数値的に解けるようになるわけです。流れとしては１：まず、サンプル１（y = A cos(Bx) * exp(-Cx) をプロットしたもの）の完成品が手元にあるとする。２：サンプル１に対して、極大・極小を見つけるための判別式を書き、現われた順に番号を割り振ってゆく。３：割り振られた数字を探索して、数字順に極大と極小を並べるテーブルを作る。４：並べたテーブルをグラフにする。といった順で解説します。
	まず、「B列に並んだy=f(x)の値が極大かどうか」という判別式を作ります。 C8セルに下に書いた式を入力します。 =IF(AND(B8-B7>0,B9-B8<0),C7+1,C7 すると、図の赤字で囲んだようにC8セルに「0」と表示されます。「=IF(○,△,□)」という関数は、○という条件が成立するなら△を表示し、不成立なら□を表示する、というとても便利なものです。ここでは、y1,y2,y3と順に並んだ数値に対して「y1-y2>0かつy3-y2<0」ならそれは極大だ、という判別式を書いてあります。判別式が成立した場合だけ、一つ上のセルに＋１をして、不成立ならば上のセルをそのまま表示します。ついでにここで、A2からE2の範囲にあるパラメータの値を、図のように調節しておいてください。
	上で作った判別式をコピーして、C9からC207の範囲に貼り付けます。黒ポッチリをダブルクリックする技を使えば一瞬です。式の中身は赤字で示したようになっているはずです。 C16セルからC17セルで、数値が０から１に上がっているでしょうか？これは「B17のセルが極大になっている」ということを表します。このように、判別式をうまく作れば、順番に現われる極大に対して１、２、３･･･と番号を増やしてゆくことができるわけです。
	同様に極小を判別する下記の式を、D8セルに書きます。 =IF(AND(B8-B7<0,B9-B8>0),D7+1,D7) ここでは、y1,y2,y3と順に並んだ数値に対して「y1-y2<0かつy3-y2>0」ならそれは極小だ、という判別式です。
	D8セルをコピーし、D9からD207の範囲に貼り付けます。式の中身は赤字で示したようになっているはずです。
	C列の番号が切り替わる時、そこに極大があります。しかし、C列D列には同じ番号が並んでおり、これでは探索がしにくいです。これを解決するため、「切り替わった番号だけを抜き出して表示させる」という列をＥ列とＦ列に作ります。まず、E8セルに下記の条件式を書きます。 =IF(C7<>C8,C8,"") 「C列に並んだ番号のうち、ひとつ前と同じなら空欄にして、ひとつ前と違う番号ならその番号を表示しろ」という条件式です。「<>」は、「不等号≠」の意味です。
	E8セルをコピーし、E9からE207までの範囲に貼り付けます。 E17セルに「１」と表示されたでしょうか？
	同様に、極小を探すための番号が変わった時だけを抜き出す条件式を書きます。 F8列に条件式を書き、F9からF207の範囲に貼り付けて下さい。ここまでで、「極大と極小を探して、現われた順に番号を割り振ってゆく」という部分が完成しました。
	ここから、探索した極大と極小を番号順に並べるテーブルを作ります。まずは番号を並べるためにG2からG13の範囲に図のような式を入力します。
	H2セルに、下記の探索式を入力します。 =LOOKUP(G2,E$7:E$207,A$7:A$207) 「=LOOKUP(○,△,□)」の意味は、「△の範囲に○と一致するセルを見つけたら、その位置に相当する□の値を表示しろ」というものです。ここでは、「E列（番号が並んでいる列）の中からG2の値（ここでは「１」）を探して、それを見つけたら、その行にあるＡ列の値（ｘの値）を表示しろ」というような式になっています。「１」と表示されればＯＫです。これは、１番目の極大を与えるｘ軸の値が1.0（A17セル）である、ということを表しています。
	H2セルをコピーし、H3からH13の範囲に貼り付けます。２番目の極大はx=5.2、３番目の極大はx=9.4、・・・というように、極大を与えるｘの値が順に並んでいます。
	I2セルに下記の式を入力します。 =LOOKUP(G2,E$7:E$207,B$7:B$207) 0.902571、と表示されたでしょうか？これは、１番目の極大値そのものが0.902571という値であることを表します。
	I2セルをコピーし、I3からI13の範囲に貼り付けます。これで、N番目の極大値が並びました。
	同様に極小を与えるｘの値をJ列に表示させるため、図のような式をJ2からJ13の範囲に入力して下さい。とりあえずJ2セルに下の式を書いて、あとはコピー貼り付けで済ませましょう。 =LOOKUP(G2,F$7:F$207,A$7:A$207)
	同様に、極小値をK列に表示させるため、図のような式をK2からK13の範囲に入力して下さい。とりあえずK2セルに下の式を書いて、あとはコピー貼り付けで済ませましょう。 =LOOKUP(G2,F$7:F$207,B$7:B$207)
	これで、極大値と極小値を順番に並べたテーブルが出来上がりました。次に、H列をｘ軸、I列をy軸としてグラフを作ってみましょう。すると、図のように極大を表す（x,y）がプロットされるはずです。
	ちなみにここでは、図のような散布図の中にある「プロットのみ（線なし）」を使いました。
	同様に極小の方もプロットしてみると、図のようになります。 UP/DOWNを使って、パラメータを変化させると、それに連動して極大・極小のプロットも変化することを確かめて下さい。
	最後に、２つのグラフを重ねて表示する技を紹介します。ここでは、極小のプロットと極大のプロットを重ねて表示させるため、前者をコピーして後者に貼り付けます。まず、極小をプロットしたグラフの「白い部分（灰色の部分だとダメ）」で右クリックをします。すると、図のようなメニューが表示されるので、「コピー」をクリックして下さい。クリップボードにグラフがコピーされました。
	そして、極大をプロットしたグラフの「白い部分（灰色の部分だとダメ）」で右クリックをします。すると、図のようなメニューが表示されるので、今度は「貼り付け」をクリックして下さい。
	すると左図のように極小のプロットが極大のプロットに重ねて表示されます。ちなみに、極大極小のプロットと、y=f(x)を線のグラフを重ねて表示する時はちょっと面倒です。（曲線またはプロットの上で左クリックをして、「データ系列の書式設定」を選び、それぞれの線の書式を変える）だいぶ長くなってしまいましたが、この課題の説明はここまでにします。ここで紹介した技は、極大極小を探す以外にも色々な場面で活躍します。たとえば方程式の解を数値的に求めるには、条件式を「=IF(AND(B7<0,B8>0),C7+1,C7)」とするだけです。解が一つしかないならば、「=IF(AND(B7<0,B8>0),1,"")」とすれば、もっとシンプルな構造になり、列を節約することができます。

エクセル計算の技へ戻る

ホームへ

イントロ（サンプル１の説明）	次に、「y = A cos(Bx) * exp(-Cx) における極大、極小を与える(x,y)をxの小さい順に並べる」という課題を取り上げます。この課題では、判別式を実現する「IF」関数と、探索を実現する「LOOKUP」関数を使います。この２つを使いこなせれば、極大や極小を探す・方程式を解く・曲線の交点を探す、いった問題を数値的に解けるようになるわけです。流れとしては１：まず、サンプル１（y = A cos(Bx) * exp(-Cx) をプロットしたもの）の完成品が手元にあるとする。２：サンプル１に対して、極大・極小を見つけるための判別式を書き、現われた順に番号を割り振ってゆく。３：割り振られた数字を探索して、数字順に極大と極小を並べるテーブルを作る。４：並べたテーブルをグラフにする。といった順で解説します。
	まず、「B列に並んだy=f(x)の値が極大かどうか」という判別式を作ります。 C8セルに下に書いた式を入力します。 =IF(AND(B8-B7>0,B9-B8<0),C7+1,C7 すると、図の赤字で囲んだようにC8セルに「0」と表示されます。「=IF(○,△,□)」という関数は、○という条件が成立するなら△を表示し、不成立なら□を表示する、というとても便利なものです。ここでは、y1,y2,y3と順に並んだ数値に対して「y1-y2>0かつy3-y2<0」ならそれは極大だ、という判別式を書いてあります。判別式が成立した場合だけ、一つ上のセルに＋１をして、不成立ならば上のセルをそのまま表示します。ついでにここで、A2からE2の範囲にあるパラメータの値を、図のように調節しておいてください。
	上で作った判別式をコピーして、C9からC207の範囲に貼り付けます。黒ポッチリをダブルクリックする技を使えば一瞬です。式の中身は赤字で示したようになっているはずです。 C16セルからC17セルで、数値が０から１に上がっているでしょうか？これは「B17のセルが極大になっている」ということを表します。このように、判別式をうまく作れば、順番に現われる極大に対して１、２、３･･･と番号を増やしてゆくことができるわけです。
	同様に極小を判別する下記の式を、D8セルに書きます。 =IF(AND(B8-B7<0,B9-B8>0),D7+1,D7) ここでは、y1,y2,y3と順に並んだ数値に対して「y1-y2<0かつy3-y2>0」ならそれは極小だ、という判別式です。
	D8セルをコピーし、D9からD207の範囲に貼り付けます。式の中身は赤字で示したようになっているはずです。
	C列の番号が切り替わる時、そこに極大があります。しかし、C列D列には同じ番号が並んでおり、これでは探索がしにくいです。これを解決するため、「切り替わった番号だけを抜き出して表示させる」という列をＥ列とＦ列に作ります。まず、E8セルに下記の条件式を書きます。 =IF(C7<>C8,C8,"") 「C列に並んだ番号のうち、ひとつ前と同じなら空欄にして、ひとつ前と違う番号ならその番号を表示しろ」という条件式です。「<>」は、「不等号≠」の意味です。
	E8セルをコピーし、E9からE207までの範囲に貼り付けます。 E17セルに「１」と表示されたでしょうか？
	同様に、極小を探すための番号が変わった時だけを抜き出す条件式を書きます。 F8列に条件式を書き、F9からF207の範囲に貼り付けて下さい。ここまでで、「極大と極小を探して、現われた順に番号を割り振ってゆく」という部分が完成しました。
	ここから、探索した極大と極小を番号順に並べるテーブルを作ります。まずは番号を並べるためにG2からG13の範囲に図のような式を入力します。
	H2セルに、下記の探索式を入力します。 =LOOKUP(G2,E$7:E$207,A$7:A$207) 「=LOOKUP(○,△,□)」の意味は、「△の範囲に○と一致するセルを見つけたら、その位置に相当する□の値を表示しろ」というものです。ここでは、「E列（番号が並んでいる列）の中からG2の値（ここでは「１」）を探して、それを見つけたら、その行にあるＡ列の値（ｘの値）を表示しろ」というような式になっています。「１」と表示されればＯＫです。これは、１番目の極大を与えるｘ軸の値が1.0（A17セル）である、ということを表しています。
	H2セルをコピーし、H3からH13の範囲に貼り付けます。２番目の極大はx=5.2、３番目の極大はx=9.4、・・・というように、極大を与えるｘの値が順に並んでいます。
	I2セルに下記の式を入力します。 =LOOKUP(G2,E$7:E$207,B$7:B$207) 0.902571、と表示されたでしょうか？これは、１番目の極大値そのものが0.902571という値であることを表します。
	I2セルをコピーし、I3からI13の範囲に貼り付けます。これで、N番目の極大値が並びました。
	同様に極小を与えるｘの値をJ列に表示させるため、図のような式をJ2からJ13の範囲に入力して下さい。とりあえずJ2セルに下の式を書いて、あとはコピー貼り付けで済ませましょう。 =LOOKUP(G2,F$7:F$207,A$7:A$207)
	同様に、極小値をK列に表示させるため、図のような式をK2からK13の範囲に入力して下さい。とりあえずK2セルに下の式を書いて、あとはコピー貼り付けで済ませましょう。 =LOOKUP(G2,F$7:F$207,B$7:B$207)
	これで、極大値と極小値を順番に並べたテーブルが出来上がりました。次に、H列をｘ軸、I列をy軸としてグラフを作ってみましょう。すると、図のように極大を表す（x,y）がプロットされるはずです。
	ちなみにここでは、図のような散布図の中にある「プロットのみ（線なし）」を使いました。
	同様に極小の方もプロットしてみると、図のようになります。 UP/DOWNを使って、パラメータを変化させると、それに連動して極大・極小のプロットも変化することを確かめて下さい。
	最後に、２つのグラフを重ねて表示する技を紹介します。ここでは、極小のプロットと極大のプロットを重ねて表示させるため、前者をコピーして後者に貼り付けます。まず、極小をプロットしたグラフの「白い部分（灰色の部分だとダメ）」で右クリックをします。すると、図のようなメニューが表示されるので、「コピー」をクリックして下さい。クリップボードにグラフがコピーされました。
	そして、極大をプロットしたグラフの「白い部分（灰色の部分だとダメ）」で右クリックをします。すると、図のようなメニューが表示されるので、今度は「貼り付け」をクリックして下さい。
	すると左図のように極小のプロットが極大のプロットに重ねて表示されます。ちなみに、極大極小のプロットと、y=f(x)を線のグラフを重ねて表示する時はちょっと面倒です。（曲線またはプロットの上で左クリックをして、「データ系列の書式設定」を選び、それぞれの線の書式を変える）だいぶ長くなってしまいましたが、この課題の説明はここまでにします。ここで紹介した技は、極大極小を探す以外にも色々な場面で活躍します。たとえば方程式の解を数値的に求めるには、条件式を「=IF(AND(B7<0,B8>0),C7+1,C7)」とするだけです。解が一つしかないならば、「=IF(AND(B7<0,B8>0),1,"")」とすれば、もっとシンプルな構造になり、列を節約することができます。