Excel-12: 絶対参照の応用〜三次元グラフ

Excel-11: 絶対参照とは何か〜グラフの例をもとにで絶対参照について学んだ。

もう一つ絶対参照が必要になる例として、三次元グラフの作成法を学ぼう。
三次元のグラフとは下図のように、x, y の値を元に z の値を計算してプロットするようなグラフである。

二次元グラフと同様にまずデータの表を作成することから始めるのであるが、
そこで絶対参照の知識が必要になる。





三次元グラフを描く〜準備

描くグラフは、以下の式で表されるものである。



三次元グラフを作成する際、入力 (x,y) を 出力 z に結びつける対応表が必要になるが、
それをするための準備を行う。

その前に、どのような表を作成すれば良いかを解説する。

まず、これまで学んで来たような y=f(x) のグラフであれば、下図のような表を作成すれば良いのだった。



一方、今回のように z = f(x, y) の三次元グラフの場合、下図のように 1 行目を x 軸、A 列目を y 軸と見立て、
残りの領域に下図のように x, y に対応する f(x, y) を記した表を作成する。
この図を念頭に以下の作業を進めて行こう。



まず、キーボードで Ctrl キーを押しながら a キーを押し (Ctrl-a)、セル全体を選択しよう
そして、列名を表すアルファベット (ここでは「B」とした) の上でマウスを右クリックして「列の幅」を選択しよう。



すると、以下のようにセルの幅を指定できるウィンドウが現れるので、「5」を入力して OK しよう。



すると、以下のように列の幅がやや狭くなる。
以上の操作は必須ではないが、セルの広い範囲を見ることができるので以下の作業がわかりやすくなる。



まず、入力 y の値を作成しよう。-2 < y < 2 の範囲で 0.2 刻みのデータを作成しよう。
そのために、セル A2、A3 に -2、-1.8 と記述し、下図のようにこの2つのセルを選択する。
セル A1 は空欄としておくこと!!

そして、右下のフィルハンドルを下方向に移動しよう。



すると、オートフィルの働きにより 0.2 間隔のデータが作成される。 2 までの表を作成する。
これが y 軸となるのだった。



次にx 軸の値を作成しよう。やはりこちらも -2 < x < 2 の範囲で 0.2 刻みのデータを作成しよう。
セル B1、C1 に -2、-1.8 と記述し、セルB1とC1をマウスで選択する。
こちらでも、セル A1 は空欄としておくこと!!

そして、こちらでも右下のフィルハンドルをつかみ、マウスで右方向に移動する。



オートフィルで 0.2 ごとのデータが作成される。2 までの表を作成しよう。
これが x 軸となるのだった。 なお、横方向のオートフィルはここで初めて使用した。



これで x 軸と y 軸の準備はできた。先へ進もう。



三次元グラフを描く〜悪い例

まず、絶対参照が必要であることを実感するため、間違った方法で三次元グラフを描いてみよう。

まず、セル B2 に式を記述しよう。ここで記述する式は



であったから、以下の図のように記述すれば良い。ここで、「x^y」は「xy」 (x の y 乗) に相当する。
自分で記述するときは、かっこ、すなわち「(」と「)」の個数に注意を払って記述しよう。
多くの書き間違いは、「(」と「)」のかっこの個数が合わないことに起因する。



Enter で確定すると、値が 3E-4 と表示される。(これは 3×10-4、すなわち 0.0003 のことを表す。)
ここに3E-4 (0.0003)に近い数値が現れれば書いた式が正しかった可能性がある。



どうやっても正しく式を記入できない学生は、こちらで用意した式をコピーして貼り付けることを検討しても良いだろう。

=EXP(-(B1^2+A2^2))

上の式をコピー (Ctrl-c) し、下図のようにセル B2 をダブルクリックしてマウスカーソルが現れた状態で貼り付け (Ctrl-v) するのだった。



さて、セル B2 に式が書けたら、下図のようにセル B2 を選択し直し、下方向にオートフィルしよう。



以下のように、セルB22 までオートフィルする。



そして、セルの選択を解除せず、そのまま再びフィルハンドルを掴んで右方向にオートフィルする。



下図のようにセルV22までオートフィルすれば、表の完成である。



そこで、表全体を選択しなおす。
そうしないと、グラフの軸の数値がおかしなものになってしまう。これは課題において減点対象となる。




そして、グラフを作成する。 リボンの「挿入」タブから「3-D 等高線」をみつけて選択しよう。



Excel のバージョンによってはグラフのアイコンは以下のようになっていることがある。「3-D 等高線」をよく探そう。



現れたグラフは上図のように滅茶苦茶なものである。何か表作成の過程で問題があったと考えられる。
何が問題だったのだろうか?

それは、やはり相対参照の意味を丁寧に考えると明らかになる。

セル B2 の値は、セル B1 とセル A2 を参照することで計算されているのであった。
相対参照の意味より、この式をセル D5 にコピーすると、セル D4 と C5 を参照するようになってしまう。

図からわかるように、この参照は誤りである。



正しくは、下図のように、セル D5 はセル D1 と セル A5 を参照すべきである。
つまり、行方向は行 1 を、列方向は列 A を常に参照すべきだということである。(図中緑の四角に注意)




三次元グラフを描く〜正しい例



さて、式を正しく記述して正しい三次元グラフを作成しよう。
上の議論から、行方向は行 1 を、列方向は列 A を常に参照すべきだということまで理解してもらえたと思う。

このように、常に参照して欲しい行や列に「$」をつけると絶対参照になる。
具体的には、まずセル B2 をダブルクリックして式を編集できる状態にする。
そして、下図のように式の中の「1」を「$1」に、「A」を「$A」にすれば良い。



Enter キーを押して式を確定させたら、セルB2 を選択し直し、下図のようにまず下方向のオートフィルを行う。



先程と同様セル B22 まで行う。



そして、セルの選択を解除せず、そのまま右方向にもオートフィルするのだった。







このように、三次元グラフでは絶対参照の知識が必須である。



←Excel-11: 絶対参照とは何か〜グラフの例をもとにExcel-13: 種々の統計量と相関係数→

Microsoft Office の基礎に戻る