第 5 回 表計算

本日の内容


5-1. 前回の復習とまとめ

ファイル名、ファイルのダウンロード、インストール

外国で開発されたソフトウェアの中には、ディレクトリ名やファイル名などに 日本語が入るとトラブルを生じるものがあります。 必要な時以外はなるべく日本語を使わないようにするとトラブルが発生しにく いです。

今後、授業でファイルを作ることが多くなりますが、ファイルを作る時は必ず ファイル名を指定しなければなりません。必要な時以外はこちらでは特にファ イル名を指定しませんので、適宜英字や数字の組合せでファイル名を作って下 さい。 ファイル名に演習番号や日付を入れるのも一つのアイディアです。

また、ファイルのダウンロードなどで指定するディレクトリを常に c:\work にしておくと、トラブルを避けるだけでなく、ファイルの整理もつきやすいで す。

Meadow のファイルの作り方

Meadow は一般的な Windows のソフトウェアと違い、目標のファイルを作成す る時、まず、ファイル名を指定します。 「Files→Open File...」を選び、ファイル名を入力して下さい。 一番下の画面に「Find file:~/」という表示がでます。この状態でカレントディ レクトリは c:\work になってます。 この画面では Windows のパスの区切り文字はスラッシュ「/」が使えます。 また、円記号「¥」も入力できますが、画面内ではバックスラッシュ「\」が 表示されます。

検索、置換

検索や置換を行うとき、現在のカーソルの位置が重要になります。 通常、検索や置換は現在のカーソルより後ろに対して作用します。 ファイルの全体を対象にしたい場合は、カーソルをファイルの先頭に持っていく 必要があります。 Meadow では Alt+Shift+, (Alt+<) を打つとファイルの先頭にカーソルが 動きます。また、 Alt+Shift+. (Alt+>) を打つとファイルの最後にカーソ ルが移動します。

GIMP のファイルの作り方

GIMP でファイルを作る時、画像上で右クリックし、「ファイル→別名で保存」 を選びます。 そして、「選択」という欄で、「c:\work\ファイル名.png」などとファイル名 を指定します。最後が png だと、 PNG 形式になります。 最後が jpg だと JPEG 形式になります。

カット、コピー、ペースト

さまざまなソフトウェアでカット、コピー、ペーストの機能は実現されていま すが、それぞれ微妙に呼び方が異なることがあります。 以下はそれぞれのソフトウェアでの画面写真です。

メモ帳
Netscape Communicator
Almail
コマンドプロンプト
Meadow
GIMP
OpenOffice の表計算

5-2. OpenOffice の設定

OpenOffice は Microsoft Office に似せて作られたフリーソフトウェアです。 まだ不十分なところもいくつか残っているため、次のような設定をする必要が あります。

初回一回のみの設定

  1. 「Tools→Options」
  2. 「言語設定→言語」で、「日本語体裁の支援」を「アクティブにする」を 選び、次の図のように設定する。
    言語設定→言語の設定内容

毎回設定が必要

  1. 「セルスタイル」ウィンドウの中の「標準」を右クリックし、「変更」を 左クリックする。
  2. 「フォント」を選び、「英数字用フォント」の「フォントの種類」と「日 本語用フォント」の「フォントの種類」の両方とも「MS P ゴシック」に変更 する。
  3. 「OK」を左クリック。

なお、「文書ドキュメント」を作成する場合の「標準」の設定は次の通りであ る。

5-3. 表計算

コンピュータのプログラムは、入力、処理、出力の部分に分けることができます。 表計算ソフトウェアでは、(1)入力、出力のプログラミングの手間を省き、(2) 処理に必要な情報を視覚化し、(3)二次元までのデータの処理に特化すること により、基本的な情報処理を簡単な操作で可能にします。 但し、複雑な情報処理はしないように設計されてますので、 (1)1000 件を越えるような情報の操作や、(2)状態を遷移しながら動作するよ うなプログラム、(3)入力や出力のデザインを変えるようなことはしない方が 良いです(できないわけではありません)。

画面はセルと言われる長方形の記憶領域が表の形に集まっていて、 ここにデータや式を入れて、計算するようになっています。

カーソルはセルを囲むような太線の長方形です。 目的のセルを左クリックするとそこにカーソルが移動します。 また、矢印キーでも移動できます。

カーソルにより選択されているセルの内容は数式入力ボックス(Input Line)に 表示されます。

入力

セルには、数字、文字列、日付、式などを入れることができます。 入力の際、矢印キーは修正には使えないことに注意して下さい。矢印キーを押 すと入力が確定し、矢印の方向へカーソルが移動します。 セルの内容の修正には「F2」キーを押すか、数式入力ボックスをクリックして 下さい。この場合、カーソルキーが使用できます。修正が終ったら Enter を 押して下さい。 セルの内容の削除は Delete キーを使います。 シート全体の消去は「Edit→Delete Sheet」とします。

数字の入力
数字をそのまま入力します。
文字列の入力
シングルクォーテーションマーク「'」を入力してから文字列を入力しま す。シングルクォーテーションマークを入力しなくても大丈夫な場合もありま す。
日付
「月/日/年」の組合せで入力します。
イコール「=」を入力してから数式を入力します。足し算は +、引き算は -、かけ算は *、割算は / で表します。関数を使う時は丸かっこ「()」を使い ます。

演習5-1

次のようにセルに値を入れなさい。

ABCDE
1123あいうえお5/20/2002「1+1の結果」=SIN(PI()/2)

セルの参照(1)

式の中にセルの名前を入れることができます。例えば、上の演習問題の「123」 の入っているセルの名前は A1 と呼ぶことができます。 式の中で、セルに入っている内容をセルの名前で参照できます。 なお、セルの名前の入力は、そのまま英字と数字の組合せでも入力できますが、 マウスのクリックやカーソルの移動+Enter でも入力できます。

演習5-2

一次方程式の解は次のようにすると求まります。

ax+b=0 (a≠0)
   x=-b/a

これを計算させましょう。

ABC
1'a'b'ax+b=0 の解
21020=-B2/A2

このように表を作ると解が求まります。 10 や 20 という数字を他の値に変えてみましょう。 数字を変更した瞬間に再計算が行われ、瞬時に解が求まります。

演習5-3

二元一次連立方程式を解くシートを作りなさい。

ax+by=e (ad-bc≠0)
cx+dy=f
ヒント

この式を x=○、y=○という形にし、それぞれの計算 式をセルに入れなさい。

領域

指定

セル上で次のことをすると領域を反転(指定)することができます。

  1. ポインターをドラッグする。
  2. Shift を押しながらカーソルキーを動かす。

領域の選択はいろいろな用途に使えます。

  1. Cut, Copy, Paste
  2. 領域のふちをドラッグ & ドロップすると、領域の移動ができる。
  3. 領域のふちを Ctrl キーを押したままドラッグ & ドロップすると、 領域のコピーができる。
  4. 領域を指定してから入力を行うと、 Enter キーを押すたびにカーソルが 領域内に収まるように動く。

関数

領域を引数とする関数があります。 領域を関数の引数として指定する場合、長方形の領域のうち「左上のセル名: 右下のセル名」という形で指定します。 もちろん、上記のようにマウスのドラッグや、カーソルキーの移動でも指定で きます。

以下は領域を引数とする関数の例です。

SUM(X)
領域 X 内の数を合計を求める。
AVERAGE(X)
領域 X 内の数の平均を求める。
STDEV(X)
領域 X 内の数の標準偏差を求める。
COUNT(X)
領域 X 内でデータの入っているセルの数を求める。
MAX(X)
領域 X 内の数の最大値を求める。
MIN(X)
領域 X 内の数の最小値を求める。
RANK(X;Y;Z)
領域 Y の中で X の順位を返す。Z=0 なら大きい順、Z=1 なら小さい順。
LARGE(X;Y)
領域 X 内の数で Y 番目に大きい値を求める。

演習5-4

次の表を作り、データの合計を求めなさい。

ABCDEF
113825=SUM(A1:E1)

演習5-5

上記の演習の表に対して、平均値を求めなさい。

ハンドルと参照

参照のコピー

次の表を考えます。

ABCD
11020=A1
23040 ?

ここで、 C1 のセルを D2 にコピーすると D2 にはどんな内容が入るのでしょ うか? 実は、この時、D2 には「=A1」は入らず、「=B2」が入ります。 右に一つ、下に一つ移動すると、A が B にずれ、 1 が 2 にずれます。コピー のこのような性質を使うと、表を作る時便利です。

さらに、ハンドルを使用するとコピーが簡単に行えます。 コピー元にカーソルを合わせ、ハンドルをドラッグするとドラッグした範囲に コピーが行われます。 また、横に複数の範囲を選択してからハンドルを縦にドラッグすると、複数の 範囲をそのまま縦方向にコピーできます。

演習5-6

ハンドルを使って次の表を完成させなさい。

ABCDE
1'X'Y'X^2'X*Y
23040=B2*B2=B2*C2
35060=B3*B3=B3*C3
47080=B4*B4=B4*C4
590100=B5*B5=B5*C5
6'合計=SUM(B2:B5)=SUM(C2:C5)=SUM(D2:D5)=SUM(E2:E5)

ヒント

  1. 「X」,「Y」,「X^2」,「X*Y」,「合計」などはそのまま入力する。各 X, Y の値も入力する。
  2. D2, E2 は入力する。そして、 D2, E2 を選択し、ハンドルを 5 行目まで 引っ張る
  3. B6 を入力したあと、 B6 を選択し、ハンドルを E 列目まで引っ張る

演習5-7

上の表や、関数 COUNT を使って、この与えられたデータに対して最小二乗法 により、近似関数を求めなさい。

値のコピー

値(数、日付)の入っているセルのハンドルを引っ張ると、その値を 1 ずつ増 やした値が埋められます。

二つのセルを選択した後、選択した方向にハンドルを引っ張ると、等差数列を 作ります。

ABCDE
133.13.23.33.4
25/1/20026/1/20027/1/20028/1/20029/1/2002

このような表を作るには、 (1) 3 と 3.1 を入力し、選択してハンドルを引っ張り、 (2) 同様に、 5/1/2002 と 6/1/2002 を選択してハンドルを引っ張る。

演習5-8

sin の関数表を作りなさい(次の演習で使用するので消さないこと)。

AB
10=sin(A1)
20.1=sin(A2)
30.2=sin(A3)
:::
:::
646.3=sin(A64)

ヒント: (1) 0 と 0.1 を入力し、選択して、ハンドルを引っ張り、 6.3 までの等差数列を作り、(2) =sin(A1) を入力し、ハンドルを引っ張り 6.3 ま での表を作る。

グラフ

データの入っている領域を選択して、「Insert→Chart」とするとグラフが描 けます(「Insert→Chart」をしてから領域を選ぶこともできます)。

演習5-9

上の演習で作成した sin の関数表を元に、 sin 関数のグラフを描きなさい。

ヒント
  1. 領域指定。
  2. Insert→Chart。
  3. 「最初の行/列を項目名に引用」のチェックを両方とも外し、「次へ」。
  4. 「散布図」を選び、「次へ」。
  5. 「データポイントなし平滑線つなぎ」を選び、「次へ」。
  6. 適宜タイトルなどの指定を確認し、「完了」を選ぶ。
  7. 作成したグラフ中の「メインタイトル」や「凡例」のフォントの指定がお かしいので、それぞれ「右クリック、オブジェクトの属性、文字」を選択し、 フォントを「MS P ゴシック」に変更する。

他のアプリケーションとのやりとり

表計算ソフトウェアやデータベースなどでもっとも基本的なデータ形式は、 CSV(Comma Separated Value)と呼ばれる、カンマ「,」でデータを区 切った形式です。 この形式でやりとりする限りは、ほぼ間違いなくデータ交換がうまくいきます。

一方、電子メールや Web など通常のテキストファイルの形式でデータをやり とりするにはどうすれば良いでしょうか? 作成した表を電子メールなどで送るには、OpenOffice の表の部分を選択して、 Copy & Paste で電子メールソフトにデータを貼り付けると、各々のデー タが Tab コードで区切られた形式で貼りつけられます。

一方、電子メールや Web 上のデータに関しては、データ同士が Tab で区切ら れていない限り、 Copy & Paste ではうまく行きません。 空白などで区切られていたらどのようにすれば良いでしょうか? この場合、次のような手順で読み込みます。

  1. テキストエディタで一旦テキストファイルを作成し、ディスクに保存します。
  2. OpenOffice 側で「File→Open」でファイル名を指定し、「ファイルの種 類」から、「テキスト CSV」を選びます。
  3. 区切りのオプションとして、「タブ」「スペース」「フィールド区切りの 結合」を選択します。
  4. 「セルスタイル」の「標準」を右クリックして変更を選び、「フォント」のフォ ントの種類を両方とも「MS P ゴシック」に変更します。

演習5-10

次の表を作り、小計を計算しなさい。


割引率  30
商品名  単価 数量 小計 値引き 販売価格
りんご   120   5 
デコポン 300   3
バナナ    60  10
キウイ   100   7
いちご   400   5
合計  

(この表は演習5-12で使います)

演習5-11

  1. 前回作成した testdata を「テキスト CSV」で読み込む。
  2. データを選択し、「Insert→Chart...」でグラフを作成する。
  3. 散布図の「データポイントのみ」を選ぶ。
  4. 「完了」を押してグラフを表示させる。
  5. プロットされているデータの点を右クリックし、「オブジェクトの属性」 を左クリックする。
  6. 「統計」を選び、「回帰曲線」の「線形回帰」を選ぶ。
  7. 適宜白抜きの四角が表示されているところのフォントを「MS P ゴシック」 に変更する。

絶対参照

セルの参照の入ったセルをコピーすると、移動先に応じて参照先が変化します。 しかし、これでは都合の悪い場合があります。 例えば、演習5-10の表において、値引き率を変えて、各々の販売価格の 変化を見たいとします。この場合、各項目が同じ値引き率の値を参照する必要が あります。 しかし、そのためには、この表をハンドルやコピーを使用して作成するとき、 コピーしても参照先が変わらないような仕方が必要になります。

コピーをしても参照先が変わらないような参照の仕方を絶対参照と いい、セル名の行や列の名前の前に「$」記号を入れて表します。 演習5-10の表の場合、次のようになります。

ABCDEF
1割引率30
2商品名単価数量小計値引き販売価格
3りんご1205=B3*C3=D3*$B$1/100
4デコポン3003=B4*C4
5バナナ6010=B5*C5
6キウイ1007=B6*C6
7いちご4005=B7*C7
8合計 =sum(D3:D7)

「$B$1」という参照はどこにコピーしても「$B$1」となり、B1 のセルの内容 を示します。 E3 のように式を「=D3*$B$1/100」と入力したとき、これを E4 にコピーする と、$ のついてない D3 への参照だけ変わり、「=D4*$B$1/100」となります。

また、横にコピーした時は変化させたくなく、縦に変化させたい時もあると思 います。この場合は、変化させたくない方だけ $ を付ければ可能です。「$A1」を縦方向にコピーすると $A2、$A3 と行が変 化しますが、横方向にコピーするときは常に $A1 のままです。

絶対参照は参照を直接キーボードで入力することもできますが、式の入力時に、 カーソルやポインタで指定してから「Shift+F4」を押すことでも指定できます (マイクロソフトエクセルやロータス 1-2-3 では F4 のみ)。

演習5-12

演習5-10の表を完成させなさい。割引率 10%, 20%, 30% でのそれぞ れの販売価格を求めなさい。

IF と論理関数

表計算の関数には IF という関数があります。

AB
11=IF(A1=3;5;10)

このような場合、B1 の値は A1 の値が 3 の時だけ 5 に、それ以外の時は 10 になります(この場合 10)。 IF を使うと一つの式で表せなかったような計算をすることができるようにな ります。

演習5-13

方程式 ax+b=0 の解は aの値により、次のようになります。

1. a≠0 のとき
   x=-b/a。
2. a=0 のとき
2-1. b≠0 のとき
     解なし。
2-2. b=0 のとき
     任意の x は式を満たす。

これを計算させましょう。

ABC
1'a'b'ax+b=0 の解
21020=if(A2=0;if(B2=0;"任意の値";"解なし");-B2/A2)

このように表を作ると、aが 0 の時でも解が求まります。 実際に a の値に 0 を入れて確かめましょう。

注: このように式に文字列を入れる時は、ダブルクォーテーションマークで 「"文字列"」というように括ります。

演習5-14

次の方程式の組みを満たす x, y の組みを求めるよう なシートを作りなさい。 但し、 ad-bc=0 の時は、解 が無限に存在したり、解がなかったりするので、注意すること。

ax+by=e
cx+dy=f
ヒント

二元一次方程式は二つの直線の共通部分と考えることができます。平面上の二 つの直線の関係は次のうちのどれかになります。

つまり、与えられた二つの式が平行かどうかを調べてから、さらに平行なら一致す るかどうかを調べると、この場合分けができます。 但し、abcdの組が 0 になると直線を表さなくなりますので、話は複雑になります。 まず、abのどちらかは 0 でなく、かつ、 cdのどちらかが 0 でない場合を考えましょう。

なお、「解は 5x+ 3y = 1 を 満たす全ての(x, y)」と文字列に実際の 値を入れて表示するには次のようにします。

="文字列1"&A1&"文字列2"&...

演習5-15

次の表を作り、完成させなさい。

平均点 ? 標準偏差 ?
合格人数 1 合格最低点 ?    重み付き
         国語 数学 英語    国語 数学 英語 合計 偏差値 順位 合否
重み      0.5  1.0  0.9

02kc201   50   30   40
02kc202   80   60   70
02kc203   70   40   40
02kc204   60   60   60 
02kc205   20   80   40
02kc206   40   80   60
02kc207   70   70   70
02kc208   90   60   60
02kc209   70   50   60
02kc210   30   70   50
  1. 国語、数学、英語の点数にそれぞれの重みをかけたものを計算しなさい。
  2. 重みをかけた値の合計値を求めなさい。
  3. 合計値から標準偏差を求めなさい。
  4. 偏差値は次の式から求めなさい。
             (素点-平均点)
    偏差値 = ------------- × 10 + 50
               標準偏差
    
  5. RANK 関数を使用して、合計点から順位を求めなさい。
  6. LARGE 関数を使用して合格最低点を求め、 IF 文を使用して「合格」「不 合格」をそれぞれ表示させなさい。

坂本直志 <sakamoto@c.dendai.ac.jp>
東京電機大学工学部情報通信工学科