Excel ソフト ( 関数 )
Excel の関数 EXCEL の機能のなかで重要なものに関数がある 数学 統計 データベース 文字列等に分類される関数がある すべてを取り扱うことはできないが 代表的なものについて紹介したい ただ眺めるだけでは理解も浅くなる そこで演習問題を中心に使い方を見ていきたい 活用方法 グラフ化 統計分析などの応用的な計算 データベース等 ワードの代わりに報告書の作成や領収書の作成 2
EXCEL 関数 まず メニューの数式のところを見たいただきたいいくつかの関数が分類されて存在する 3
EXCEL 関数 例えば数学 / 三角を選ぶと関数の一覧が出てくる 4
EXCEL 関数 下記サイトをクリックしてみてください http://www.excel-list.com/math_and_trigonometry.html 一覧が出てきます 5
count 数値データのセルを数えるカウント = COUNT( 範囲 ) 文字や数値 式などのデータが入力されているセルを数える未入力セルは数えません カウントエー = COUNTA( 範囲 ) 空白のセルを数えるカウントブランク = COUNTBLANK( 範囲 ) 6
count 未入力セル と "" ( 長さ 0 の文字列 ) ではカウントのされ方が違います 未入力セルと "" ( 長さ 0 の文字列 ) は COUNTBLANK 関数ではカウントされます "" ( 長さ 0 の文字列 ) は COUNTA 関数ではカウントされます 下図では B2:B5 セルには A2:A5 で説明しているデータが入力されています 7
count 練習データを入力 (Data_meibo を読み込んで ) し 平均点を求めなさい ただし欠席者はカウントしません ヒント : sun と count を組み合合わせる 8
最大値 最小値 範囲内の最大値を求めます マックス = MAX( 範囲 ) 範囲内の最小値を求めます ミン = MIN( 範囲 ) 9
最大値 最小値 10
最大値 最小値 11
日付 日付や時刻を表示するトゥデイ =TODAY() : 今日の日付を表示する ショートカットキーでは [Ctrl]+[;] ( セミコロン ) ナウ = NOW() : 今日の日付と時刻を表示する ショートカットキーでは [Ctrl]+ : ( コロン ) 問題演習 1. 今日の日付を B2 セルに 今の時刻を C2 セルに表示しなさい 2.B3 セルに明日の日付を表示しなさい 3.C3 セルに 1 時間後の時刻を表示しなさい 12
日付 13
日付 年 月 日 の数値を日付データ( シリアル値 ) に変えるデイト = DATE( 年, 月, 日 ) : シリアル値を返します 問題 1 C3:E4 セルにそれぞれ年月日の数値が入力されています これらの数値を使って F3:F4 セルに日付を表示しなさい 14
数学 数値の絶対値を返します アブソルート = ABS( 数値 ) 問題 値 A と値 B の差を求めなさい データは入力してください 15
数学 円周率 π を返すパイ = PI() 円周率 3.14159265358979 を返します この数値の精度は 15 桁です 角度 ( 度数 ) をラジアンに変換するラジアン = RADIANS( 角度 ) ラジアンを度数に変換するディグリーズ = DEGREES( ラジアン ) 角度の sin を返すサイン = SIN( ラジアン ) 角度の cos を返すコサイン = COS( ラジアン ) 角度の tan を返すタンジェント = TAN( ラジアン ) 16
数学 度数とラジアン 演習 : 角度が 30 度の場合の cos,sin,tan を求めてみましょう 17
数学グラフを書こう sin,cos カーブを書いてみます A2 セルに 0 と入力します A2 を選択した状態で 編集 フィル 連続データ を選択します 範囲 : 列 にチェックを付けます 増分値 : 1 停止値 700 と入力します OK ボタンをクリックすると A2:A702 に 0~700 の数値が入力できます 18
数学グラフを書こう 元データをシートで計算して表示します B2 に =SIN(RADIANS(A2)) C2 に =COS(RADIANS(A2)) と入力します B2:C2 を選択した状態で フィルハンドルをダブルクリックすると B2:C702 まで数式が入力され sin(0)~sin(700) と cos(0)~cos(700) の値が求められます ダブルクリック 19
数学グラフを書こう グラフウィザードで散布図を選択して描くと下図のようになります 20
数学 平方根を返すスクエア = SQRT( 数値 ) 数値に負の数を指定すると エラー値 #NUM! が返されます ルート ( ) の計算に使います =SQRT(4)=2 となります 数値が負の数になるとエラーになるので 絶対値を返すABS 関数と組み合わせて =SQRT(ABS( 数値 )) のようにすることもあります 他の計算式 算術演算子 (^) を使った例 :=4^(1/2) =4^0.5 POWER 関数を使った例 :=POWER(4,1/2) =POWER(4,0.5) べき乗を返すパワー = POWER( 数値, 指数 ) 数値の指数乗を返します 2 の 2 乗なら =POWER(2,2)=4 となります 21
数学乱数 乱数を返すランド ( ランダム ) = RAND() 0 以上で 1 より小さい乱数を発生させます ワークシートが再計算されるたびに 新しい乱数が返されます RAND 関数で乱数を発生させた後 ワークシートが再計算されるたびに乱数の値が変更されます 数値に置き換えるには 数式バーに =RAND() と入力して [F9] キーを押します a と b の範囲で乱数を発生させるには =RAND()*(b-a)+a とします セル B1 に =RAND()*(10-1)+1 と入力し B10 までフィルハンドルをドラッグして数式をコピーします セル C1 に =ROUND(B1,0) と入力し C10 までフィルハンドルをドラッグして数式をコピーします =ROUND(RAND()*(10-1)+1,0) と一つの数式にできますが 説明のために 2 列に分けています 22
数学乱数 23
数学乱数 演習 : 以下の手順に沿って 1~10 の整数値を A1:A10 にランダムに表示せよ 1. 作業列 (C 列 ) を使います セル C1 に =RAND() と入力し C10 までフィルハンドルをドラッグして数式をコピーします 2. セル A1 に =RANK(C1,$C$1:$C$10) と入力し A10 までフィルハンドルをドラッグして数式をコピーします 24
数学乱数 3. 再計算または F9 キーを押すごとに値が変化しますので 値を固定したい時は A1:A10 を選択して コピー ボタンをクリック 編集 形式を選択して貼り付け で 値 にチェックを入れて OK ボタンをクリックします A1:A10 が数値になります C 列は不要ですので削除します 25
その他の関数情報 下記サイト等を参照ください 本日の関数を含め 関数の使い方が公開されています http://kokodane.com/kansuwaza.htm http://www.eurus.dti.ne.jp/yoneyama/excel/kansu/itiran.html 26
演習 1 1. 下図のような請求書を作成したい 本日の日付を知って翌月 20 日の日付を求める ( セル A7 に入力すべき ) 関数を作成し 提出ファイルへコピーペーストし 紙に印刷し提出してください メールで 提出ファイルを送っていただいてもいいです 必ず 学籍番号 名前 月曜 3 限か 4 限の別は明記ください 27
演習 1 ヒント : 日付を求める関数 : =DATE( 年, 月, 日 ) を入力 請求日などを指定するために 基準となるのは 今日の日付です 翌月の日付 を求めるには YEAR 関数 MONTH 関数 DATE 関数の 3 つの関数を使います 次図にあるように D3 に =TODAY() と入力し 今日の日付を出します ついで A7 に今日の日付を出したい場合 =DATE(YEAR(D2), MONTH(D2), 20) とします 2013/6/20 と表示されますがこれを 2013/7/20 に変えて欲しいという問題です DATE(YEAR(D2), 7, 20) とするのは不可とします 28
演習 1 A7 に =DATE(YEAR(D2), MONTH(D2), 20) と入力した場合 29
演習 2 1. 学生番号末尾が 1,5,9 の人はデータ 1 を 2,6,0 の人はデータ 2 を 3.7 の人はデータ 3 を 4,8 の人はデータ 4 を 読み込んで 出席の場合 1 を入力し 欠席の場合空欄とせよ 2. 次いで 出席日数をカウントせよ 受験科目 出席日数 関数をコピーペースト ( 入力でもよい ) して張り付けよ 30
移動平均 移動平均とは? 時系列データを観察する時 データの変化が激しく 基本的な変化の傾向がつかみにくいことがあります これを解決する一つの手段として移動平均という方法があります この移動平均とは ある個数分のデータの平均値を連続的に求め そのデータ全体の変化の傾向を解析するものです 株価を分析する時などでよく使われています まず Data_idou を読み込んで 下記表を作成してみましょう 31
移動平均 月次データをベースに 3 か月移動平均を用いる場合 1 当該月の前後 1 か月のデータを平均する方法 ( 例えば 5 月の平均値は 4 月 5 月 6 月の値を平均 ) 2 当該月以前の 3 か月のデータを平均する方法 ( 例えば 5 月の平均値は 3 月 4 月 5 月の値を平均 ) 3 当該月以後の 3 か月のデータを平均する方法 ( 例えば 5 月の平均値は 5 月 6 月 7 月の値を平均 ) の 3 通りのものがあります このように加工したデータを用いることにより 不規則変動も ある程度ならされ 時系列データの基本的な傾向が読み取り易くなります 32
移動平均 操作法 1.SUM 関数を使う 2 当該月以前の 3 か月のデータを平均する方法で表を作成してみます 使うのは SUM 関数だけです 2 月 3 月 4 月の合計を 3 で割ります =SUM(B2:D2)/3 あとは 式をコピーします 33
移動平均 34
演習 3 3. データ _idou には月毎のデータが読み込まれている データ ( データ _idou ) を読み込んで 移動平均 およびグラフを作成してみよ 表のデータを解答にコピーペーストしてください 35
移動平均ウイザードによる Excel 2003 の移動平均分析ツールを使う 36
移動平均ウイザードによる 37
移動平均ウイザードによる (3)[ 移動平均 ] ダイアログボックスが表示されます 上図 [ 入力範囲 ] ボックスで 1 行または 1 列のデータを入力します 下図 38
39