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