法政大学市ヶ谷基礎科目

情報処理演習II

担当 重定 如彦

2003108

 

3回 表計算ソフト(その3)

1.      セルの修飾と挿入、削除

ExcelではWordの表と同様の操作によって、セルの修飾、削除、挿入などを行えます。

Ø         セルの幅(高さ)の変更:一番上の(名前が書いてある)行又は一番左の列の中で枠の部分をドラッグするとセルの幅や高さを変更することができます。なお、Wordの表のように、枠の一部分だけをずらすことはできません。

Ø         セルの中の文字の修飾:「書式設定」のツールバーを使い、Wordと全く同じ方法で選択中のセルの中の文字を修飾することができます。

Ø         行(列、セル)の挿入:挿入したい行(列)のセルを一つ選択して、メニューの「挿入」→「行」(「列」)を選択すると新しい行(列)がその場所に挿入されます。メニューの「挿入」→「セル」を選択した場合、どのように挿入するかを選択するパネルが表示されるので、選択してOKボタンをクリックして下さい。例えば「右方向にシフト」を選択するとそのセルの右にあるセルの内容がすべて一つ右にずれます。

Ø         行(列、セル)の削除:セルを一つ選択し、メニューの「編集」→「削除」を実行するとどのように削除するかを選択するパネルが表示されます。行(列)全体を選択してOKボタンをクリックすると、選択されているセルの行(列)がすべて削除されます。「左方向にシフト」を選択すると、選択したセルの内容が削除され、その右にあるセルの内容が一つ左方向に詰めて埋められます(「上方向にシフト」も同様)。

Ø         セルの結合と解除:Wordの表と同様に複数のセルを結合して大きなセルを作ることができます。結合したいセルを全て選択し、ツールバーの「セルを結合して中央揃え」ボタンをクリックして下さい。結合するセルに複数のデータが入っていた場合は一番左上のセルだけが残ります。セルの結合を解除するには、結合されたセルをクリックしてもう一度「セルを結合して中央揃え」ボタンをクリックして下さい。

Ø         罫線の修飾:ツールバーの「罫線」→「罫線の作成」を選択すると罫線を修飾するためのツールバーが表示されます。Excelの罫線は初期状態(薄い灰色で表示)のままでは印刷した時に罫線は印刷されません。印刷時に罫線も印刷したい場合はこのツールバーを使って罫線を修飾して下さい。使い方はWordのものとほぼ同じですが、Wordの表のように罫線を新しく引いてセルを分割することはできません。

Ø         凝ったセルの修飾:セルを選択し、メニューの「書式」→「セル」を実行するとさらに凝った修飾を行う為のパネルが表示されます。例えばパネル上部の「配置」をクリックし、右に表示される「方向」の部分にある「文字列」をドラッグすることで文字の方向を斜めにしたりすることも可能です。また、通常はセルに表示しきれない文字を入力するとはみ出た部分は表示されませんが、「文字の制御」の部分で「折り返して全体を表示する」を選択すると表示しきれない部分を改行して表示するようになります。これ以外にも様々な修飾を行えるので興味がある方は試して下さい。

2.      データの表示形式

 前回の授業で、セルには数値を入力できると説明しましたが、Excelでは数値データを様々な形式で表示することができます。例えば、数値を入力する際に直接 ¥100 のように入力すれば、それをエクセルが通貨として¥記号をつけて表示してくれます。また、メニューの「書式」→「セル」を実行して表示されるパネルで「表示形式」をクリックし、「分類」の部分を選択することで、現在選択中のセルの表示形式を変更することができます。

通貨

1000、$2000

日付

2002/9/23、平成14923

時刻

14:001400

百分率

100%

分数

12 1/434 3/8

いくつかの表示形式は書式設定ツールバーを使って行うことができます(「通貨スタイル」、「パーセントスタイル」、「桁区切りスタイル」)。また、「小数点表示桁上げ」と「小数点表示桁下げ」によって数値を小数点以下何桁まで表示するかを設定することができます。

これらの表示スタイルでは、¥や%や月などの文字が数字についていますが、エクセルはこれらのデータを文字列ではなく数値として扱います。従って、足し算などの演算を行ったり、オートフィル機能を使うことも可能です。

練習問題その1:以下の表を入力し、B1、B2、B3にそれぞれ左のセルの値に20を足す式を入力せよ(B1にはA1のセルの20日後の日付が表示される)。次にA3のセルの表示形式を数値に変更し、小数点以下3桁まで表示するようにせよ。注:オートフィル機能を使ってB1の内容をB2、B3にコピーすると表示形式までコピーされるので、その場合はB2、B3のセルの表示形式をメニューで適切なものに変更すること。

 

2003/6/30

 

 

\1,000

 

 

12 1/3

 

 

3.      関数

これまでの授業では、式の中のセルや算術記号はすべて人間の手で入力していました。小さな表の場合、これでもかまわないのですが、大きな表になると単純な足し算でも式を書くのが大変な作業になってしまいます。例えば、100人分の点数の合計を計算するためには、式の中に100のセルと99の「+」記号を手で書く必要があります。

そこで、表計算ソフトでは隣り合ったセルの中の合計や平均などのよく使われる計算を簡単に行うため、「関数」と呼ばれるものを用意しています。関数は

関数名(引数1、引数2...)

 という形で記述します。関数に渡す数字やセルのことを引数と呼び、引数がいくつあるかはそれぞれの関数によって異なります。例えば、数字の合計を求める関数は

    SUM(引数1、引数2、引数3…..

 という形で記述します。例えばA1とB2のセルの内容の足し算を行うには、

=SUM(A1, B2)

と記述します。長方形の範囲のセルの中身の合計を計算する場合、この引数にセルの範囲を記述することで合計を計算することができます。例えば表に以下のようなデータを入力し、A1:J1の範囲のセルの合計を計算するには、K1のセルに

    =SUM(A1:J1)

 と記述することで計算できます。

 

1

2

3

4

5

6

7

8

9

10

 

 

 

 

 

 

 

 

 

 

 

 

 このとき、「A1:J1」というセルの範囲を表す部分を、マウスでその範囲を選択操作することで入力することができます。まず、 =SUM(  の部分までキーボードで入力し、マウスでA1:J1の範囲を選択してみて下さい。(の後にA1:J1という文字が入力されるはずです。その後に )を記述し、エンターキーを押せば式の完成です。

 又、ツールバーの「オートSUM」ボタンを使って関数を簡単に入力する方法があります。

·           合計を計算したい部分を選択状態にし、「オートSUM」ボタンをクリックする
選択した部分の右又は下のセルに自動的に選択した範囲の合計を計算するための式が自動的に記述されます。表に下記のデータを入力し、A1:E1の範囲を選択し、オートSUMボタンを押すと、その合計を計算する式がF1に記述されます。また、A1:E3の範囲を選択し、オートSUMボタンを押すと、A4:E4の範囲に縦方向の合計を計算する式がそれぞれのセルに記述されます。

 

1

2

3

4

5

 

2

3

4

5

6

 

3

4

5

6

7

 

 

 

 

 

 

 

·           何も記述されていない行で「オートSUM」ボタンをクリックする
そのセルに自動的に =SUM() という式が記述され、文字カーソルが()の中に入った状態になるので、マウスカーソルで合計を計算したい範囲を選択してエンターキーを押します。なお場合によっては()の中にあらかじめ近くのセルの範囲が自動的に記述される場合があります。例えば、上の表で、F2を選択してオートSUMボタンを押してください。()の中にはF2の左にあるA2:E2が自動的に記述されます。

合計以外の計算を行いたい場合は、「オートSUM」ボタンの中の▼ボタンをクリックします。合計、平均、最大値など、よく計算で使用される関数の項目がメニューで表示されるので、その中から選択するとその計算を行う為の関数がセルに記述されます。また、「その他の機能」をクリックすることでメニューに表示されない関数を選択するためのパネルが表示されます。「関数の分類」と書かれている部分を選択することで、指定した分類に属する関数の一覧が「関数名」の下に表示されるので、使いたい関数をその中から選んでクリックし、OKボタンを押せば、その関数がセルの中に記述されます。

 Excelは非常に多くの関数を用意しています。その中でよく使うものについて以下に紹介します。最低限下の表の中の関数に関しては名前とその意味を覚えて下さい。

関数名

分類

意味

SUM

数学/三角

引数の合計を計算する

AVERAGE

統計

引数の平均を計算する

COUNT

統計

引数に含まれるセルのデータの個数を計算する

MAX

統計

引数の最大値を計算する

MIN

統計

引数の最小値を計算する

練習問題その2:以下の表を入力し、関数を使って、各生徒の「出席点」、「レポート点」、「試験」、「合計点」のそれぞれについて、平均点、最高点、最小点、人数を計算しなさい。
 なお、多摩 次郎の試験のところが空欄になっていますが、これは彼が試験をサボったということを表しています。このようにセルに空欄があった場合、関数ではそのセルがなかったものとして計算を行います。従って、試験に関する平均点などの計算は5人の点数の平均ではなく、多摩 次郎を除いた4人の点数の平均が計算される点に注意して下さい。

 

 

名前

出席点

レポート

試験

合計

 

法政 太郎

80

30

50

 

 

市ヶ谷 花子

70

50

70

 

 

多摩 次郎

20

10

 

 

 

小金井 三郎

90

80

90

 

 

国際 学

60

60

100

 

 

平均点

 

 

 

 

 

最高点

 

 

 

 

 

最低点

 

 

 

 

 

10

人数

 

 

 

 

 

11

 

 

 

 

 

 

4.      条件分岐と応用例 〜成績表を作ってみる〜

次は表計算の実用的な例として、成績表を作ってみましょう。先ほどの例題で、すでに5人の生徒の点数について、平均点などのデータを計算しましたが、ここでは生徒の点数データから、自動的に成績評価(A,B,C,D)を計算する表を作成することにします。

まず、わかりやすいように合計点の成績を100点満点になおしてみましょう。上記の例では、出席点、レポート点数、試験の点数はそれぞれ100点満点なので、合計は300点満点になります。そこで、これを3で割って100点満点に直します。F1のセルに「合計2」、F2のセルに =E2/3 と記述し、F2の内容をF3:F6までコピーして下さい。また、この時小数点第一位まで表示するように表示形式を変更して下さい。

さて、成績評価ですが、以下のようにつけることにします。

A: 80点以上

B: 70点以上80点未満

C: 50点以上70点未満

D: 50点未満

このように条件によって計算結果を変える(これを条件分岐と呼びます)にはIFという関数を使います。関数IFは以下のように3つの引数を使って記述します。

   IF(第一引数、 第二引数、 第三引数)

関数IFは第一引数の式が正しいならば第二引数の値、間違っているならば第三引数の値の計算結果をセルに表示するという動作を行います。

例えばA11のセルが50以上なら「○」、50未満であれば「×」という文字を表示するには、A12のセルに次のように記述します。

  =IF(A11>=50,“○”,“×”)

○、×のように計算結果として文字列を表示したい場合は、文字列の前後を  (ダブルクオーテーション記号。シフトキーを押しながら2を押して入力)で囲む必要がある点に注意して下さい。第一引数記述する比較などの条件を判定するための式は論理式と呼ばれます。論理式はその式が正しいときに真(TRUE)という値を取り、間違っているときに偽(FALSE)という値をとります。論理式で使う比較を行う為の算術記号には以下のようなものがあります。

記号

意味

左の式が右の式より大きければ

>=

左の式が右の式以上であれば

左の式が右の式より小さければ

<=

左の式が右の式以下であれば

左の式が右の式と等しければ

<>

左の式が右の式と等しくなければ

 それではこれを利用して点数によってA,B,C,Dを計算して表示する式を記述してみましょう。結果はGの列に書くことにするので、G1に「成績」と記述して下さい。最初に80点以上だったら「A」、そうでなければ「B」と表示する式を作ってみましょう。これは先ほどの「○」と「×」の式とほとんど同じで、G2に

  =IF(F2>=80,“A”,“B”)

 と書けばOKです。次は、これに式を書き足して、70点以上で80点未満であれば「B」、70点未満であれば「C」と表示するように変更してみましょう。70点以上であれば「B」、そうでなければ「C」と表示する式を書くには先ほどの式と同様に

  =IF(F2>=70,“B”,“C”)

と書けばOKです。そこで、この式を最初の式の“B”の部分に以下のように網掛けの部分を埋め込んでやれば目的の式を作ることができます。

  =IF(F2>=80,“A”,IF(F2>=70,“B”,“C”)

式の見た目がずいぶんややこしくなりますが、落ち着いて順番に考えればこの式はそれほど複雑ではありません。この式は以下のような方法で計算が行われます。

·           F2の値が80以上であるかどうかをチェックし、真であれば“A”と表示する。

·           F2が80以上でなければ、 IF(F2>=70,“B”,“C”) が実行される。この部分では、F2が70以上であるかどうかをチェックし、真であれば“B”、偽であれば“C”を表示するので、これで3つの成績を評価することができる。

上記の式で、点数によってA,B,Cを表示する式が完成しました。最後は50点未満の場合「D」と表示するようにすれば成績を判定するための式は完成です。これは先ほどと全く同じように、“C”の部分を IF(F2>=50,“C”,“D”) に置き換えればOKです。従って最終的な式は次のようになります。

 =IF(F2>=80,“A”, IF(F2>=70,“B”,IF(F2>=50,“C”,“D”)))

エラーが出る人は、括弧の数に注意して下さい。これを、G3:G6にコピーすれば全員分の成績を計算する表が完成します。

 

名前

出席点

レポート

試験

合計

合計2

成績

 

法政 太郎

80

30

50

160

53.3

C

 

市ヶ谷 花子

70

50

70

190

63.3

C

 

多摩 次郎

20

10

 

30

10.0

D

 

小金井 三郎

90

80

90

260

86.7

A

 

国際 学

60

60

100

220

73.3

B

 

平均点

64

46

77.5

172

57.3

 

 

最高点

90

80

100

260

86.7

 

 

最低点

20

10

50

30

10.0

 

 

10

人数

5

5

4

 

 

 

 

11

 

 

 

 

 

 

 

 

完成した表

5.      ANDとOR

ANDとORという関数を使って複数の論理式の判定を行うことができます。

ANDは引数の論理式がすべて真の場合のみ真となる関数で、ORは引数の中に一つでも真となる論理式があれば真となる関数です。例えば次の式は

=AND(10>5,20>10,30>20)

関数ANDの中の3つの論理式はすべて真なので、答えは真(TRUE)となります。

=AND(10>5,20>10,10>20)

一方上の式は3つ目の論理式が偽なので、答えは偽(FALSE)となります。

=OR(10>5,20>10,10>20)

また、ORの場合は、一つでも正しい論理式があれば答えは真となるので、上記の式の答えは真(TRUE)となります。ANDを関数IFの中で使用する例を挙げます。

=IF(AND(A1>=10,A1<20),“○”,“×”)
この式はA1の内容が10以上20未満であった場合に○そうでなければ×を表示します。

6.      課題

以下の作業を行い、baseball.xlsという名前でGドライブに保存し課題のメールに添付ファイルとして送って下さい。締切りは来週の月曜日です。

Ø         下記の表の通りにデータを入力する。

Ø         B2:B11に打率を計算する式を入力して打率を計算し、小数点以下第3桁まで表示すること。なお、打率は  安打/打数  で計算できます。

Ø         関数を使ってC12:F14に合計、最大値、最小値を計算する。

Ø         関数IFとORを使ってG2:G11に本塁打数が15以上または、打点が35以上の場合に ○ を、そうでなければ × を表示する式を入力する。

Ø         関数IFとANDを使ってH2:H11に安打数が70以上、本塁打数が10以上、打点が30以上の場合に ○ を、そうでなければ × を表示する式を入力する。

 

名前

打率

打数

安打

本塁打

打点

評価1

評価2

小笠原

 

232

81

14

54

 

 

和田

 

192

66

13

28

 

 

カブレラ

 

197

67

23

55

 

 

坪井

 

207

69

4

21

 

 

城島

 

259

86

16

51

 

 

井口

 

245

79

12

39

 

 

松中

 

261

79

11

41

 

 

 

255

77

6

39

 

 

10

ブラウン

 

227

67

9

32

 

 

11

ショート

 

238

69

6

30

 

 

12

合計

 

 

 

 

 

 

 

13

最大値

 

 

 

 

 

 

 

14

最小値

 

 

 

 

 

 

 

 
出席のメールは takaaki_311@yahoo.co.jp までお願いします。

課題や質問のメールなどは、sigesada@edu.i.hosei.ac.jpまでお願いします。

授業の資料の最新版はhttp://www.edu.i.hosei.ac.jp/~sigesada/にあります。