Excel プログラム開発の練習マニュアルー1
( 関数の学習 )
作成
2015.01.31
修正
2015.02.04
本マニュアルでは、Excel のプログラム開発を行なうに当
たって、まずは、
Excel の関数に関する学習
について記述する。
Ⅰ.Excel の関数に関する学習
1.初めに
Excel は単なる表計算のソフトと思っている方も多いと思います。
しかし、Excel には、一般的に使用する Excel 関数の他に、Excel Basic と 呼ばれる VBA( Visual Basic for Application ) を組み込む事も出来る。
Excel で VBA を使って開発を行うと、極めて強力な業務用ソフトを開発 する事が出来て、業務効率化に大変有用である。
Excel Basic を使った開発の練習を行う前に、まず、本マニュアルで、Excel で利用出来る関数の使い方の練習を行う。
なお、Excel では非常に沢山の関数が使えるので、本マニュアルで説明を していない関数については、Excel のヘルプを参照して勉強して下さい。 Excel Basic を使った開発の練習については、「
Excel のマクロに関す
る学習 」
を参照して下さい。 2.事前準備 ①.MS Office の準備 通常は、パソコンに MS Office が予めインストールされているので、そ の中の Excel を使用します。 インストールされていない場合は、MS Office の Standard 版を購入し ます。 ②.Excel 関数の学習 以下の「3.Excel の関数に関する学習
」を参考にして、Excel のヘ ルプで十分にスキルを付ける事が出来ます。 但し、ヘルプで勉強する事が苦手だという人は、解説本を購入して勉強 して下さい。3.
Excel の関数に関する学習
①.添付の PDF ファイル「Excel の関数に関する学習(資料)
」の解説 a.「アンケート調査票」の作成 「Excel の関数に関する学習(資料)
」の 1 頁に在る様なアンケー ト調査票を Excel で作成します。 アンケートの回答は、後で分析し易い様に、また、答える人がある程 度答え易い様に、5 段階位の回答がお勧めです。 2 段階だと、後で行う解析が荒くなり過ぎて、大雑把な分析結果しか得 られないし、10段階では答える人が大変です。 b.「アンケート調査票」の集計と分析 「Excel の関数に関する学習(資料)
」の2頁に在る様なアンケー ト調査票を Excel で作成します。 2頁の例題では、30件のアンケート調査票の結果について記述して あります。 ・平均値について前頁の表の35行目の平均値は、Excel の関数を使って算出して います。 平均値を出す関数は、「Average 関数」を使用しています。 ( AVERAGE(R[-30]C:R[-1]C)) : 下記の表のカラム参照 ) R[-30]C とは、30行上で同じ列のカラムの事を示します。 R[-1]C とは、1行上で同じ列のカラムの事を示します。 R[-30]C:R[-1]C とは、30行上の同じ列のカラムから1行上 の同じ列のカラム迄の範囲のカラム(データ)の事を示します。 すなわち、AVERAGE(R[-30]C:R[-1]C) とは、30行上の同じ 列から1行上の同じ列迄の値の平均値を表します。 平均値の表示が「3.1666667」と、小数点以下7桁なのは、タ ブの「書式」のセルで、セルの書式を「小数点以下7桁」に設 定している為です。(次頁も参照)
・中央値について 平均値と中央値は紛らわしくて、よく間違える人がいます。 平均値とは、上記の表で言えば、30個の値の合計値を個数(3 0)で割った値です。 中央値とは、最大値と最小値の真ん中の値(最大値+最小値)/ 2 の事です。 下記の表に在る様に、「MEDIAN」という関数を使用します。 ・分散について 分散とは、データのバラツキの大きさの事です。
分散が大きければ、アンケートに答えた人の「好み」とか「評価 とか、が一定していない事になります。 つまり、「答えがばらばら」だという事です。 分散の計算には、上記の表に在る様に、「VAR」という関数を使 用します。 ・MAXとMINについて 最大値と最小値の事です。 MAXは「VAR」という関数を使用し、MINは「VAR」という 関数を使用します。(下記の表と次頁の表を参照)
・SUM(合計値)について SUMとは、複数のデータの合計値の事です。 合計値の計算には、下記の表に在る様に、「SUM」という関数を 使用します。 ・合計値のSQRT(ルート)について SQRTとは、ルート(√)の事です。 下記の表に在る様に、「SQRT」という関数を使用します。 本マニュアルでは、説明しませんが、統計解析では非常に重要 な値である標準偏差を算出する時にも使用します。 (次頁の表を参照)
・平均値のROUNDについて ROUNDとは、四捨五入の事であり、四捨五入の計算には、 下記の表にある様に「ROUND」という関数を使用します。 この表では、値を小数点以下 3 桁に四捨五入していますが、表 示(セルの書式)は小数点以下7桁なので、この様な表示になっ ています。 ・平均値のROUNDUPとROUNDDOWNについて ROUNDUPとは切り上げの事で、ROUNDDOWNとは 切り下げの事です。
・平均値のMIDについて
MIDとは、文字列の加工をする関数で、下記の表の例
では、分散値(0.9712644)を文字列として扱い、その文
字列の 3 桁目から5文字をとりだしています。
( MID(R[-13],3,5) )
・分散値のLENについて
LENとは文字列の長さを返す関数で、下記の表の例で
は、分散値は、Excel の内部計算では17桁の精度で計算
している事が分かります。
・実数の解析
質問2の列のデータは異常値を除かずにデータを解析
しています。(下記の表を参)
したがって、正しい分析結果とは言い難いものです。
・異常値の除去
通常は、データ解析を行う前に、質問2改の例(下記の
表を参)の様に異常値を除去して解析を行います。
前頁の表の計算結果を見れば分かる様に、異常値を除か
ない場合と除いた場合では、分析結果がこの様に異なるの
です。
もちろん、異常値を除いた計算結果が正しい値である事
は明白です。
c.「アンケート調査票」のグラフ化 設問4と設問5のデータの関係を理解し易くする為に、「Excel の
関数に関する学習(資料)
」の3頁に在る様な Excel のグラフ(散布 図)を作成します。 以下、Excel で散布図を作成する手順を記述します。 c-1.Excel の Tab の「挿入」をピックし、「グラフ」を選択します。c-2.グラフウィザード(グラフの種類)の画面が出るので、グラフ の種類として散布図を選び、「次へ」のボタンを押します。
c-3.グラフウィザード(データ範囲)の画面が出るので、グラフ表 示をする範囲を選び、「X」を押します。
c-4.グラフウィザード(グラフの元データ)の画面になるので、「次へ」 のボタンを押します。 c-5.グラフウィザード(グラフ グラフオプション)の画面になるの で、「グラフタイトル」に「設問4と設問5の相関性」、「X 数値軸」 に「設問4のアンケート結果」、「Y 数値軸」に「設問5のアンケー ト結果」を入力します。
c-6.グラフウィザード(グラフ グラフオプション)の画面で、「メモリ 線」の Tab を選択し、その画面で、「Y/数値軸」の「メモリ線」のチ ェック(レ)を外すと下記の画面になります。 c-7.グラフウィザード(グラフ グラフオプション)の画面で、「凡例」 の Tab を選択し、その画面で、「凡例を表示する」のチェック(レ) を外すと下記の画面になります。 なお、グラフに複数の組のデータを表示する場合は、「凡例を表示 する」のチェック(レ)を外さないでおきます。
c-7.前頁の画面で、「次へ」の Tab を選択すると、グラフウィザード(グ ラフ グラフの作成場所)の画面(下記の画面)になるので、そのま まに(「オブジェクト」にチェック、「相関性表示:ワークシートの 名前」)します c-8.上記 c-7 の画面で、「完了」のボタンを押すと下記の画面になりま す。 上記 c-7 の画面では、データがプロットされている様子が非常に 見難いので、修正します。
c-9.前記 c-8 の「設問4と設問5の相関性」画面で、X 軸と Y 軸に囲ま れた部分(プロットエリア)をダブルピックすると、下記の画面が 出ます。そこで、「輪郭」を「なし」に指定し、領域の色を「白(右 下)」に指定し、「OK」を押すと c-10 の画面になります。 c-10.しかし、この画面では、データのプロットが見にくいので、更に修 正します。
c-11.表のプロットをダブルピックすると下記の画面(データ系列の書 式設定画面)が出るので、「マーカー」の「前景」と「背景」の色を 黒に指定する。
「OK」ボタンを押すと c-12.の画面になる。