Excel とは エクセル (Excel) は,Microsoft 社が開発した表計算ソフトである. データを入力して様々な方法で集計させたり, 数式を入力して計算させたり, グラフを書いたりすることができる.VBA(Visual Basic for Application) というマクロ言語を搭載

Transcription

1 大分大学理工学部創生工学科福祉メカトロニクスコース情報処理概論 これからの講義の進め方 1)Excel とは 2)Excel の一般操作 : 入門編 3)Excel によるプログラミング : テキストに沿って説明と演習 : ここがメインテキスト : 加藤潔 :Excel 環境における Visual Basic プログラミング, 共立出版をかならず購入しておくこと. ( ア ) 構造化プログラミング ( イ ) VBA(Visual Basic for Application) による構造化プログラミング 1 Visual basic editor の起動と終了 2 プログラムの作成, 実行, 保存, 読み込み 3 マクロとは 4 オブジェクトとは ( ウ ) 変数と四則演算 ( エ ) データの型 : 整数型, 実数型, 文字列型, 論理型 ( オ ) 変数と宣言 ( カ ) 定数と宣言 ( キ ) 演算子 : 数値, 文字列 ( ク ) 四則演算 ( ケ ) 構造化プログラミング 1 条件判断 ( 分岐処理 ) 2 繰返し ( 反復処理 ) 3 プロシージャ, 関数 4 ワークシートの活用 ( コ ) 文字列処理 ( サ ) 配列 ( シ ) 応用プログラム 1 モンテカルロ法 2 2 二分法とニュートン法 3 差分法による運動方程式解法 1

2 Excel とは エクセル (Excel) は,Microsoft 社が開発した表計算ソフトである. データを入力して様々な方法で集計させたり, 数式を入力して計算させたり, グラフを書いたりすることができる.VBA(Visual Basic for Application) というマクロ言語を搭載しているので, 自動計算を可能にするプログラムを組むこともできる. 現在は Excel2013 になっているが, 基本的な使用法はあまり変わらないので, ここでは,Excel2007 をベースに説明する. なお, テキストは 2013 準拠で,2003,2007 の補足のカード説明が挿入されている. Excel の一般操作 1. 画面構成と操作方法 フォントボックス メニュータブ ツールバー 数式ボックス フォントサイズボックス 名前ボックス 列番号 行番号 セル I12 セル ワークシート シート見出し ステータスバー ワークシートは標準で 3 枚 Sheet1,Sheet1,Sheet3 ここをクリックすることで, めくることができる. 2

3 Microsoft Office ボタン 他 ファイルの 読み込み 書き出し 印刷 などのメニュー :Office2007 以降は, 左肩の (Microsoft Office ボタン ) の中に移動している. 編集 メニューがなくなったので コピー, 貼り付け などは右クリックする と表示されるメニューか, もしくは (Microsoft Office ボタンの下のあたりにあるア イコンで処理する. 元へもどす も変更になっている. セルへの書き込み 注目しているセルは黒い枠で囲まれていて, 矢印キーで移動することができる. キーボードから文字や数値などを打ち込むと注目しているセルに書き込まれる. セルに文字を書き込むには 赤い部分 ( 数式バー と呼ぶ) をマウスでクリックして書き込むこともできる ( 数式バー に書き込んだ文字はそのまま注目している セル に書き込まれる ) 3

4 セルの書式設定 上下方向揃え : 上揃え, 中央揃え. 下揃え 左右方向揃え : 左揃え, 中央揃え, 右揃え セルの結合 縦方向の幾つかのセルを一まとめにしたり, 横方向 の幾つかのセルを一まとめにする. ワークシート ワークシートの挿入削除 : ワークシート上を右クリ ックして, ショートカットメニューから指定する. CSV ファイル Comma Separated Value の略で, 各値をカンマで区切ったデータ形式のこと. EXCEL などの表計算ソフト, データベースソフトなどでは独自のデータ形式をもっており, それぞれのデータ間では互換性がないのが普通であるので, それらで相互にデータ読み込みをする際には,CSV 形式でつくったテキストファイルを利用する. ファイル名の拡張子は csv である. 4

5 csv ファイルを Excel で読むためには, エクスプローラからファイル名をダブルクリックするのが最も簡単. あるいは, (Microsoft Office ボタン ) の中の 開く を選んでから ファイルの種類 としてテキストファイル (*.prn,*.txt,*.csv) を選んでもよい. ソート ( 並べ替え ) 5

6 注意 1. ソートするには, 表の第一行目に 見出し をつけなければならない. 見出し によって 列 を区別するため, 見出し は互いに異なっていなければならない. 2. データベースの言葉では, 各行は レコード と呼ばれ, 列は フィールド と呼ばれる. 見出し は フィールド名 とも呼ばれる. フィルタ : データタブ 6

7 クリアをクリックすると, 元の 表に戻る. 7

8 コピー マウス右クリック Esc キー を押すことによっ て, 選択が解除される. 点線で囲まれた箇所が, 選択状態になる. コピー や 切り取り などは, 行単位あるいは列単位ですることができる. 例えば A 列を指定するには, A が書いてある部分をクリックする. フィルハンドルセルに 100 を書き込み, 一旦別のセルを選択し, もう一度書き込んだセルを選択すると, 選択範囲の右下に黒い四角形が表示される. このフィルハンドルにマウスカーソルを近づけると, マウスカーソルが次のように十字型に変化し, この状態で右クリックしてドラッグし, マウスから指を離すと, 連続コピーができる. 8

9 9

10 数式, オート SUM で 1 行,Ⅰ 列の和を求める. セルのコピーで, 和計算をコピーできる. SUM は和をとる関数のこと. セルの中身が =SUM(B2:C2) の場合, それを 1 つ下のセルにコピーすると, 行が相対的に1つプラスされ, =SUM(B3:C3),2つ下のセルにコピーすると, 行が相対的に2つプラスされ, =SUM(B4:C4) になる. 平均の計算 : オート SUM の平均メニューを選択 セルの中身が =AVERAGE(B2:B6) の場合, それを 1 つ右のセルにコピーすると, 列が相 対的に 1 つプラスされ, =AVERAGE(C2:C6),2 つ右のセルにコピーすると, 列が相対 的に 2 つプラスされ,=AVERAGE(D2:D6) になる. 10

11 関数とセル範囲で和を求める. 和を出力したいセルに,=( 半角 ) を入力する. 数式 タブの 数学 / 三 角 を選択する 表示される関数から SUM を選 ぶ 和を求める範囲を指定して,OK をクリックする. セルの範囲を表すのに, コロン : を用いる. 11

12 これを修正するために右の赤い 部分をクリックする. 平均を求める. 数式 タブの 数学 / 三角 を選択する 表示される関数から, AVERAGE を選ぶ. 12

13 計算結果の数値をコピーする方法 数式の挿入 を利用して計算した結果をコピーするときに困ることがある. 単に コピー, 貼り付け をすると数式がそのままコピーされ, 数値が失われてしまうことがある. このようなときは 貼り付けるときに, コピー領域を選択し, つぎに, 右クリックメニューから, コピー を選択する. さらに, コピーしたい領域を, 右クリックして表示されるメニューから 貼り付ける を選ばずに, 形式を選択して貼り付ける を選び, 次のようなメニューが登場するから 値 を選ぶ. グラフの表示横軸 : 学籍番号, 縦軸 : レポート, 期末試験で, 表示する例縦軸のデータ範囲を指定する. 挿入タブのグラフを選択する. グラフの種類選択 : ここでは, 折れ線を選択. 13

14 横軸を指定するために, グラフの設定を変更する. グラフ上で, 右クリックすると, 以下 のメニューが登場する. 横軸の指定をするために, 横軸 ラベルの編集ボタンを押す. 設定を変えたい軸やグラフ戦場で, 右 クリックすると, 編集メニューが表示 される. 14

15 グラフ表示その 2 15

16 2. 計算式の作り方 : Excel では, 表内の1つ1つの区画をセルとよび, 列方向をアルファベット, 行方向を数字で座標の形で指定する. たとえば,A5,B8など. 範囲を指定するときには,: を使う. たとえば,A2:A5の場合, セルA2,A3,A4,A5までを,A2:B5の場合, A2,A3,A4,A5,B2,B3,B4,B5を指定したことになる. 数式をセル内に入力した場合には, その数式を自動計算したものがセル内に表示される. この際の注意するのは, 以下の点である. (1) 数式の先頭に必ず=をつける. (2) 乗算の演算記号は ではなく * 記号を, 除算には ではなく / 記号を, べき乗には ^ 記号を使う. (3) 分数は表現できないので, たとえば, つぎのように入力する.1/2など. (4) 演算の順序は通常の計算と同様に, 乗算, 除算が加算, 減算より優先され,( ) の中から先に計算される. 例題 ) 預金残高計算入力したデータを用いて, 計算式の値を計算する方法です. 各自で, 以下を実行してみましょう. 1) まず, 以下の図の A セルの全行,B セルの全行を入力する. 2)C セル 2 行目,3 行目を入力する. 3)C セル 5 行目からを, 以下の説明にしたがって, 入力する. セル C4 には,=C3+C3*B3 により, 元金と利息の和を計算している. 16

17 セル C5 から C15 は, いちいち式を入力しなくても, セル C4 をコピーすれば, 自 動的に, 各セルはつぎのようになる. C5 は =C4+C4*B4,C6 は =C5+C5*B5,C7 は =C6+C6*B6... となる. 3. 関数の利用 複雑な計算が行えるように,Excel の内部にあらかじめ組み込まれている数式を関数という. 関数は, 計算に必要な数値や文字列を与えると, それらを使って自動的に計算してくれる. たとえば,SQRT(2) で2の平方根を計算してくれる. 計算に必要なデータを引数という. 引数には数値, 文字列, セルの参照, 数式, 関数などが書ける. 関数はセルの中に単独で書くことも, 数式の中にその一部として書くこともできる. 例 2) (1) 引数が数値の場合 : SUM(10,15,17) の計算 (2) 引数がセルの場合 : SUM(A1:A10) セル A1 から A10 までの合計を計算 (3) 引数が文字列の場合 : LEFT( ABCDEFG,4) ABCD という 4 文字を取り出す 17

18 例 3) 平方根の表作成 : 各自, 以下の説明にしたがって, 演習を行ってください. (1) セルA1にN, セルB1にSQRT(N) と入力する. (2) 1 列目のA2,A3, からA11までに,1から10までの値を入力する. (3) セルB2を選択し,=ボタンを押し, その左横のボタン を押し, その他の関数を選択すると, つぎのような画面になる. (4) 関数の分類で, 数学 / 三角を選択し, 関数名 SQRT を選び,OK ボタンを押すと, つぎのようになるので, 数値ボックスのなかに,A2 と直接入力するか, セル A2 をクリックし,OK ボタンを押す. 18

19 (5) セル B2 の式を B3 から B11 までコピーすると, つぎのようになる. 4. 連立方程式を解く つぎの連立方程式を逆行列と行列の掛算を用いて計算する. Ax = y x = A -1 y 例 4): 各自, 以下の説明にしたがって, 演習を行ってください. 19

20 (1) つぎのように,A と y の値をセルに入力する. 20

21 (2) 逆行列を求める. まず, 逆行列を求める関数は複数の値を返すので, 答えの入るセルとして, 何も入力していない場所で, 係数行列 A と同じおおきさのセル範囲を指定する. 次に,=ボタンを押し, その左横のボタン を押し, その他の関数を選択し, 関数の分類で, 数学 / 三角を選択し, 関数名 MINVERSE を選ぶと, つぎのようになる. (3) このとき,OK ボタンを押さずに,[Ctrl]+[Shift]+[Enter] を同時に押すと, 指定し たセルに逆行列が計算されたものが表示される. 21

22 (4) 計算した逆行列と定数行列の積を計算し, 方程式の解を求める. 行列の積を求めるには,MMULT 関数を用いる. 答えを入れるセル範囲を指定し,=ボタンを押した後, 関数 MMULT を選択し, 積をする2つの行列を指定する. 配列 1で最初の行列 A の値のセル範囲を囲むことにより指定し, 配列 2を入れるときには, マウスのポインタで配列 2のボックスをクリックした後で,yの値のセル範囲を指定する. (5) このとき,OK ボタンを押さずに,[Ctrl]+[Shift]+[Enter] を同時に押すと, 指定し たセルに逆行列が計算されたものが表示される. 22

23 5. ニュートン法による平方根の計算 (1) セル A2 に 0 を入れ, セル B2 に任意に選んで良い漸化式の初期値を適当に入れ る. また, 平方根を求めるべき数値を 2 とする. さらに, セル A3 に =A2+1 を 入れ, セル B3 に =0.5*(B2+2/B2) を入力する. あるいは,C2 セルに, 平方根を求めるべき数値を,2と入力し, これを絶対参照する方法を用いると, セル B3 に =0.5*(B2+$C$2/B2) を入力すればよい. この場合には,C2 セルの値を, 再度別の値に入力すると, 入力した値の平方根を求めることができる. 23

24 (2) セル範囲 A3 から B3 を選択し, コピーを選択する. さらに, コピー先のセルと して,A4 から B15 を指定し, 貼り付けを選択する. 24

25 (3) グラフを描く. 散布図を用いる. グラフを右クリック 25

26 26

27 6. 差分法による運動方程式 ( 微分方程式 ) の計算 運動方程式とは, 次式をいう. [ 質量 M] [ 加速度 a(t)]=[ 働く力 F(t)] Ma(t) = F(t) ここで,a(t) は時刻 t での加速度,F(t) は時刻 t での力を表す. また, 加速度は速度の微分, 速度は変位 ( 位置 ) の微分であるので, 次式のように表せる. [ 加速度 a(t)] = [ 速度の微分 v (t)], [ 速度 v(t)] = [ 変位の微分 x (t)] 運動方程式を速度を用いて表すと, 次式のようになる. Mv (t) = F(t) F(t) を与えて,v(t) を Excel で計算するには, つぎのようにする. (1) 時間 t を間隔 Δ できざんで, とびとびの値 ( 離散時間という ) にする. t = 0, Δ, 2Δ, 3Δ, 4Δ, 5Δ,, kδ, (k+1) Δ, (2) 速度 v(t) と力 F(t) を離散時間のデータに変換する. v[0] = v(0), v[1] = v(δ), v[2] = v(2δ),, v[k] = v(kδ), F[0] = F(0), F[1] = F(Δ), F[2] = F(2Δ),, F[k] = F(kΔ), (3) 速度の微分を離散データの差分に変換 ( 近似 ) する. v (kδ) =(v((k+1)δ)-v(kδ) Δ = (v[k+1]-v[k])/δ (4) 運動方程式を離散データの式 ( 差分方程式 ) で近似する. Mv (kδ) = F(kΔ) M(v[k+1]-v[k])/Δ = F[k] M(v[k-1]-v[k]) =ΔF[k] v[k+1] = v[k] +ΔF[k]/M (5) t=0 のときの速度 v(0)( 初期条件と言う ) を適宜与える. v(0) = v[0] = 0 (6) 働く力を差分データ (F[0],F[1],F[2], ) として与える. (7) k=1 として,v[1] を計算する. v[1] = v[0] +ΔF[0]/M (8) k=2 として,v[2] を計算する. v[2] = v[1] +ΔF[1]/M (9) v[3],v[4],v[5], を次々に求める. 27

28 この手順を Excel で計算し, グラフを書いてみよう. 簡単のため, つぎのようにおく. M = 1, v[0] = 0, F(t) = sin t, Δ = 0.1 (1) A2 に 0 を入力し,A3 に =A2+0.1 を入力する. さらに,A4 から A15 までに, セル A3 をコピーする. (2) B2 に速度の初期条件 0 を入力し,B3 に =B2+SIN(A3) を入力する. さらに,B4 か ら B15 までにセル B3 をコピーする. (3) グラフのツールボタンを押して, グラフウィザードを起動する. 28

29 (4) グラフウィザードの指示に従い, グラフを作成する. ただし, グラフの種類は散布 図を指定する. (5) タイトルとラベルを入れる 29

30 (6) グラフ表をさせると, つぎのようになる. (7) グラフを見て, 運動がどのようになっているか, 意味を考えること. ヒント : 一定 の力が働いている系は等加速度運動をする. 30

31 7. Excel マクロと Visual Basic for Application (VBA) の利用 さらに, 高度な処理を行うために,Microsoft Word, Excel, Access では,Visual Basic のコードを記述できる. これらは, まとめて VBA と呼ばれている. 次回より開始. 8. プログラミング言語で勉強しておくべきもの 必須 :Visual Basic および C 言語興味があればやっておいた方がよいもの :Java, Javascript, Perl 9. OS として知っておくべきもの 必須 :Windows, 推奨 :UNIX (Linux) 最初の方の一般的操作については, 以下のホームページを参考にした :