法政大学国際文化学部

情報リテラシーI,II

担当 重定 如彦

2009 6 30

 

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

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

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

Ø  セルの中の文字の修飾:ホームタブの「フォント」グループのボタンを使い、Wordと全く同じ方法で選択中のセルの中の文字を修飾することができます。

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

Ø  行(列、セル)の削除:セルを一つ選択し、ホームタブの「セル」グループの「削除」→「シートの行(列)を削除」を実行するとその行(列)を削除します。「削除」→「セルの削除」を選択した場合、どのように削除するかを選択するパネルが表示されるので、選択してOKボタンをクリックして下さい。

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

Ø  折り返して全体を表示する:通常はセルに表示しきれない文字を入力するとはみ出た部分は表示されませんが、ホームタブの「配置」ループの「折り返して全体を表示する」ボタンをクリックすることで、表示しきれない部分を改行して表示することができます。また、もう一度このボタンをクリックすると元に戻ります。

Ø  罫線の修飾:ホームタブの「フォント」グループの「罫線の作成」ボタンをクリックすることで、マウスの形が鉛筆の形になり、罫線を描画するモードになります。この状態でマウスをドラッグすることで罫線を引くことができます。罫線の種類は「罫線の作成」の右の▼ボタンをクリックして表示されるメニューの「線の色」と「線のスタイル」で選択することができます。このモードを解除するにはもう一度「罫線の作成」ボタンをクリックして下さい。もう一つの方法として、罫線を修飾したい範囲を選択状態にしてから、「罫線の作成」の右の▼ボタンをクリックして表示されるメニューの上部を選択するという方法があります。例えば「外枠」を選ぶと選択されているセルの外枠の罫線が修飾されます。Excelの罫線は初期状態(薄い灰色で表示)のままでは印刷した時に罫線は印刷されません。印刷時に罫線も印刷したい場合はこのツールバーを使って罫線を修飾して下さい。

Ø  セルの塗りつぶし:セルを選択し、ホームタブの「フォント」グループの「塗りつぶしの色」の右の▼ボタンをクリックすることでセルの塗りつぶしの色を設定するメニューを開くことができます。

Ø  凝ったセルの修飾:セルを選択し、ホームタブの「フォント」グループの右下の小さな四角のボタンをクリックすることで、さらに凝った修飾を行う為のパネルが表示されます。例えばパネル上部の「配置」をクリックし、右に表示される「方向」の部分にある「文字列」をドラッグすることで文字の方向を斜めにしたりすることも可能です。これ以外にも様々な修飾を行えるので興味がある方は試して下さい。

2.      データの表示形式

前の授業で、セルには数値を入力できると説明しましたが、Excelでは数値データを様々な形式で表示することができます。例えば、数値を入力する際に直接 ¥100 のように入力すれば、それをエクセルが通貨として¥記号をつけて表示してくれます。また、ホームタブの「数値」グループのボタンによって、現在選択中のセルの表示形式を変更することができます。セルに何か数値を入力し、「数値」グループのボタンをそれぞれクリックしてどのように表示が変更されるか確かめてください。さらに細かい設定を行いたい場合は、「数値」グループの右下の四角いボタンをクリックして下さい。設定を行うためのパネルが表示されます。これらの表示スタイルでは、¥や%や月などの文字が数字にくっついて表示されますが、エクセルはこれらのデータを文字列ではなく数値として扱います。従って、足し算などの演算を行ったり、オートフィル機能を使うことも可能です。
練習問題その1:次の表を入力し、B1、B2、B3にそれぞれ左のセルの値に20を足す式を入力せよ(B1にはA1のセルの20日後の日付が表示される)。次にA3のセルの表示形式を数値に変更し、小数点以下3桁まで表示するようにせよ。注:オートフィル機能を使ってB1の内容をB2、B3にコピーすると表示形式までコピーされるので、その場合はB2、B3のセルの表示形式をメニューで適切なものに変更すること。

 

2004/6/28

 

 

\1,000

 

 

12 1/3

 

 

3.      関数

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

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

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

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

    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という文字が入力されるはずです。その後に )を記述し、エンターキーを押せば式の完成です。

 又、以下のようにホームタブの「編集」グループの「Σ」(合計)のボタンを使って関数を簡単に入力する方法があります。

 

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

 

1

2

3

4

5

 

2

3

4

5

6

 

3

4

5

6

7

 

 

 

 

 

 

 

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

合計以外の計算を行いたい場合は、「Σ」ボタンの右の▼ボタンをクリックします。合計、平均、最大値など、よく計算で使用される関数の項目がメニューで表示されるので、その中から選択するとその計算を行う為の関数がセルに記述されます。また、「その他の機能」をクリックすることでメニューに表示されない関数を選択するためのパネルが表示されます。「関数の分類」と書かれている部分を選択することで、指定した分類に属する関数の一覧が「関数名」の下に表示されるので、使いたい関数をその中から選んでクリックし、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にコピーすれば全員分の成績を計算する表が完成します。seiseki.xlsxという名前で保存しておいてください。

 

名前

出席点

レポート

試験

合計

合計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.xlsxという名前でGドライブに保存し課題のメールに添付ファイルとして送って下さい。締切りは来週の授業です。

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

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

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

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

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

 

名前

打率

打数

安打

本塁打

打点

評価1

評価2

坂本

 

250

86

9

29

 

 

和田

 

225

73

17

42

 

 

井端

 

241

78

2

24

 

 

内川

 

236

72

11

26

 

 

小笠原

 

234

71

15

50

 

 

宮本

 

218

66

3

25

 

 

ラミレス

 

255

76

10

46

 

 

藤井

 

205

61

7

23

 

 

10

ブランコ

 

244

71

19

50

 

 

11

ガイエル

 

183

53

11

43

 

 

12

最大値

 

 

 

 

 

 

 

13

最小値

 

 

 

 

 

 

 

 

課題のメールは masaki.yamashita.67@gs-art.hosei.ac.jp までお願いします。

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

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