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 と記述し、下図のようにこの２つのセルを選択する。
セル 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)に近い数値が現れれば書いた式が正しかった可能性がある。
逆に、全く異なる値が現われた場合 (例えば 0.1 など) 、書いた数式が間違っているだろうから見直して修正する必要がある。
なお、この部分における数値の表記が模範例と異なってしまい (3E-04 とならず 0.0003 となる、など) 、それが気になるという場合、
本ページ末尾の「セル内の数値の表示について」が参考になるかもしれない。
どうやっても正しく式を記入できない学生は、こちらで用意した式をコピーして貼り付けることを検討しても良いだろう。
上の式をコピー (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 まで行う。
そして、セルの選択を解除せず、そのまま右方向にもオートフィルするのだった。
このように、三次元グラフでは絶対参照の知識が必須である。
なお、グラフを描く過程で、表全体を選択しなおさないと、下図のようにグラフの軸の目盛が
「1, 3, 5 …」や「系列1 …」などとなり正しくなくなる。正しい軸には、「-2 ～ 2」の間の数値のみが現れる。
これは課題では大幅な減点対象となるので注意すること。
なお、最後にこれまで通り、「グラフタイトル」、「軸ラベル」を追加すると、グラフ表示領域が狭くなった結果、
下記のようにグラフの色分けの分類数が減ってしまう。
そのような場合、下図のようにグラフの枠を掴んで大きくすると…、
下図のようにグラフ表示領域が大きくなり色分けの分類数が元に戻る。
さて、本ページでセルに数式を書いた場合、典型的には下図のように 3E-04 (0.0003) という数値が表示されるのだった。
しかし、そう表示されず、例えば「0.000335」などと表示されるケースもあるだろう。なぜそうなるのかを解説しておく。
いま、セルの表示形式が「標準」だとすると、
セル内の数値の表示は下図のように「セルとフォントの大きさの相対関係」によって変化する。
どのように表示されたとしても、正しい値 (この場合は 0.000335463) は内部的には保持されていることに注意しよう。
これが Excel の標準的な挙動であるので、早くこの挙動に慣れて欲しい。
なお、セルの表示形式が「標準」、とはセルに下図の設定がなされている状態のことをいう。
これが Excel のデフォルトの設定である。
なお、この部分で、セル内の数値の表示が期待したものと異なることから、
色々と試行錯誤した後に、下図のようにセルの表示形式を下図のように「数値」としている学生がいた。
セルの表示形式を「数値」とすると、小数点以下の桁数を固定することができる。
しかし、上図中にも記したように、セルの表示形式を「数値」とするのは会計処理や家計簿などの場合であり、
数学、物理、化学などで利用するのは注意が必要だと私は考えている。その理由を記しておこう。
下図は、セルの表示形式を「数値」、小数点以下の桁数を2 とし、
セルの列の幅を変化させたときの数値の表示の変化を記したものである。
この場合、見て分かる通り、Excel はセルの列の幅に関わらず、小数点以下の桁数 2 で数値を表記しようとする。
そのため、列の幅を 3 と小さくした場合、「0.00」と表示しきれず「##」と表示されている。
また、列の幅を 8 と大きくした場合であっても、本来「0.000335...」という数値であるにも関わらず、「0.00」と表示されてしまっている。
数学、物理、化学などでは、多くの場合これは望ましい挙動ではないだろう。
(とはいえ、正しい数値 (この場合は 0.000335463) は内部的には保持されているのではあるが)
ちなみに大学教員にとって、セルの表示形式を「数値」とするメリットがあるのは、テストのクラス平均を計算する場合だろうか。
普通に Excel でテストの平均値を計算すると、その表示は例えば「64.2857..」のように冗長なものになってしまう。
そこで、そのセルの表示形式を「数値」とし、小数点以下の桁数を 1 とすれば、表示は「64.3」と見やすくなる。
逆に、そのようなメリットがない場合はむやみにセルの表示形式を「数値」としない方が良いと思う。
なお、ここでは「フォントの種類」、「フォントサイズ」、「セルの表示形式」などの変更による数値の表示が変化することを見てきた。
他には、下図のように「ズームの変化」によっても数値の表示は変化する。これも覚えておくと良い。
さて、上では「フォントの種類」、「フォントサイズ」、「セルの表示形式」、「ズーム」などの変更によって
セル内の数字の見た目 (表示桁数) が変更されるされることを確認した。
これらを調整することで、セルの数字の表示桁数を講義ページの表示に合わせることは一般的には可能だろう。
しかし、講義中に、「どうやっても講義ページの表示と同じにならない」という学生がいた (常に小数点以下の数値が表示されない、など)。
どういう経緯でそうなったのかはわからないが、その場合、以下に記す「書式のクリア」が役に立つかもしれない。
まず、Excel のセルに、下図のように書式を滅茶苦茶に変更したセルがあるとする。
そのセルが選択された状態で、「ホーム」タブの「編集」→「クリア」→「書式のクリア」を選択する。
すると、書式を滅茶苦茶に変更したセルが一気にデフォルトの状態に戻る。
ただし、セルの幅ズームは変更されないので、手動で設定する必要がある。
もしかしたらこの書式のクリアが役に立つ場面があるかもしれない。
←Excel-11: 絶対参照とは何か～グラフの例をもとに／
Excel-13: 種々の統計量と相関係数→
Microsoft Office の基礎に戻る