法政大学市ヶ谷基礎科目

情報処理演習II

担当　重定　如彦

2005年10月5日

 

第21回　表計算ソフト（その３）

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

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

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

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

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

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

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

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

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

2.      データの表示形式

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

通貨

￥1000、＄2000

日付

2002/9/23、平成14年9月23日

時刻

14:00、14時00分

百分率

100%

分数

12 1/4、34 3/8

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

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

練習問題その１：以下の表を入力し、Ｂ１、Ｂ２、Ｂ３にそれぞれ左のセルの値に２０を足す式を入力せよ（Ｂ１にはＡ１のセルの２０日後の日付が表示される）。次にＡ３のセルの表示形式を数値に変更し、小数点以下３桁まで表示するようにせよ。注：オートフィル機能を使ってＢ１の内容をＢ２、Ｂ３にコピーすると表示形式までコピーされるので、その場合はＢ２、Ｂ３のセルの表示形式をメニューで適切なものに変更すること。

 

Ａ

Ｂ

Ｃ

１

2004/6/28

 

 

２

\1,000

 

 

３

12 1/3

 

 

3.      関数

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

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

関数名（引数１、引数２．．．）

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

　　　　ＳＵＭ（引数１、引数２、引数３…..）

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

＝ＳＵＭ（Ａ１，　Ｂ２）

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

　　　　＝ＳＵＭ（Ａ１：Ｊ１）

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

 

Ａ

Ｂ

Ｃ

Ｄ

Ｅ

Ｆ

Ｇ

Ｈ

Ｉ

Ｊ

Ｋ

１

1

2

3

4

5

6

7

8

9

10

 

２

 

 

 

 

 

 

 

 

 

 

 

　このとき、「Ａ１：Ｊ１」というセルの範囲を表す部分を、マウスでその範囲を選択操作することで入力することができます。まず、　＝ＳＵＭ（　　の部分までキーボードで入力し、マウスでＡ１：Ｊ１の範囲を選択してみて下さい。（ の後にＡ１：Ｊ１という文字が入力されるはずです。その後に　）を記述し、エンターキーを押せば式の完成です。

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

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

 

Ａ

Ｂ

Ｃ

Ｄ

Ｅ

Ｆ

１

1

2

3

4

5

 

２

2

3

4

5

6

 

３

3

4

5

6

7

 

４

 

 

 

 

 

 

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

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

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

関数名

分類

意味

SUM

数学／三角

引数の合計を計算する

AVERAGE

統計

引数の平均を計算する

COUNT

統計

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

MAX

統計

引数の最大値を計算する

MIN

統計

引数の最小値を計算する

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

 

 

Ａ

Ｂ

Ｃ

Ｄ

Ｅ

Ｆ

１

名前

出席点

レポート

試験

合計

 

２

法政　太郎

80

30

50

 

 

３

市ヶ谷　花子

70

50

70

 

 

４

多摩　次郎

20

10

 

 

 

５

小金井　三郎

90

80

90

 

 

６

国際　学

60

60

100

 

 

７

平均点

 

 

 

 

 

８

最高点

 

 

 

 

 

９

最低点

 

 

 

 

 

１０

人数

 

 

 

 

 

１１

 

 

 

 

 

 

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

次は表計算の実用的な例として、成績表を作ってみましょう。先ほどの例題で、すでに５人の生徒の点数について、平均点などのデータを計算しましたが、ここでは生徒の点数データから、自動的に成績評価（Ａ，Ｂ，Ｃ，Ｄ）を計算する表を作成することにします。

まず、わかりやすいように合計点の成績を１００点満点になおしてみましょう。上記の例では、出席点、レポート点数、試験の点数はそれぞれ１００点満点なので、合計は３００点満点になります。そこで、これを３で割って１００点満点に直します。Ｆ１のセルに「合計２」、Ｆ２のセルに　＝Ｅ２／３　と記述し、Ｆ２の内容をＦ３：Ｆ６までコピーして下さい。また、この時小数点第一位まで表示するように表示形式を変更して下さい。

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

Ａ：　８０点以上

Ｂ：　７０点以上８０点未満

Ｃ：　５０点以上７０点未満

Ｄ：　５０点未満

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

　　　ＩＦ（第一引数、　第二引数、　第三引数）

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

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

　　＝ＩＦ（Ａ１１＞＝５０，“○”，“×”）

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

演算子

意味

＞

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

＞＝

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

＜

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

＜＝

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

＝

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

＜＞

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

　それではこれを利用して点数によってＡ，Ｂ，Ｃ，Ｄを計算して表示する式を記述してみましょう。結果はＧの列に書くことにするので、Ｇ１に「成績」と記述して下さい。最初に８０点以上だったら「Ａ」、そうでなければ「Ｂ」と表示する式を作ってみましょう。これは先ほどの「○」と「×」の式とほとんど同じで、Ｇ２に

　　＝ＩＦ（Ｆ２＞＝８０，“Ａ”，“Ｂ”）

　と書けばＯＫです。次は、これに式を書き足して、７０点以上で８０点未満であれば「Ｂ」、７０点未満であれば「Ｃ」と表示するように変更してみましょう。７０点以上であれば「Ｂ」、そうでなければ「Ｃ」と表示する式を書くには先ほどの式と同様に

　　＝ＩＦ（Ｆ２＞＝７０，“Ｂ”，“Ｃ”）

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

　　＝ＩＦ（Ｆ２＞＝８０，“Ａ”，ＩＦ（Ｆ２＞＝７０，“Ｂ”，“Ｃ”））

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

·           Ｆ２の値が８０以上であるかどうかをチェックし、真であれば“Ａ”と表示する。

·           Ｆ２が８０以上でなければ、　ＩＦ（Ｆ２＞＝７０，“Ｂ”，“Ｃ”）　が実行される。この部分では、Ｆ２が７０以上であるかどうかをチェックし、真であれば“Ｂ”、偽であれば“Ｃ”を表示するので、これで３つの成績を評価することができる。

上記の式で、点数によってＡ，Ｂ，Ｃを表示する式が完成しました。最後は５０点未満の場合「Ｄ」と表示するようにすれば成績を判定するための式は完成です。これは先ほどと全く同じように、“Ｃ”の部分を　ＩＦ（Ｆ２＞＝５０，“Ｃ”，“Ｄ”）　に置き換えればＯＫです。従って最終的な式は次のようになります。

　＝ＩＦ（Ｆ２＞＝８０，“Ａ”, ＩＦ（Ｆ２＞＝７０，“Ｂ”，ＩＦ（Ｆ２＞＝５０，“Ｃ”，“Ｄ”）））

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

 

Ａ

Ｂ

Ｃ

Ｄ

Ｅ

Ｆ

Ｇ

Ｈ

１

名前

出席点

レポート

試験

合計

合計２

成績

 

２

法政　太郎

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

 

 

１０

人数

5

5

4

 

 

 

 

１１

 

 

 

 

 

 

 

 

完成した表

5.      ＡＮＤとＯＲ

ＡＮＤとＯＲという関数を使って複数の論理式の判定を行うことができます。

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

＝ＡＮＤ（１０＞５，２０＞１０，３０＞２０）

関数ＡＮＤの中の３つの論理式はすべて真なので、答えは真（ＴＲＵＥ）となります。

＝ＡＮＤ（１０＞５，２０＞１０，１０＞２０）

一方上の式は３つ目の論理式が偽なので、答えは偽（ＦＡＬＳＥ）となります。

＝ＯＲ（１０＞５，２０＞１０，１０＞２０）

また、ＯＲの場合は、一つでも正しい論理式があれば答えは真となるので、上記の式の答えは真（ＴＲＵＥ）となります。ＡＮＤを関数ＩＦの中で使用する例を挙げます。

＝ＩＦ（ＡＮＤ（Ａ１＞＝１０，Ａ１＜２０），“○”，“×”）
この式はＡ１の内容が１０以上２０未満であった場合に○そうでなければ×を表示します。

6.      課題

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

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

Ø         Ｂ２：Ｂ１１に打率を計算する式を入力して打率を計算し、小数点以下第３桁まで表示すること。なお、打率は　　安打／打数　　で計算できます。

Ø         関数を使ってＣ１２：Ｆ１３に最大値、最小値を計算する。

Ø         関数ＩＦとＯＲを使ってＧ２：Ｇ１１に本塁打数が１５以上または、打点が４０以上の場合に　○　を、そうでなければ　×　を表示する式を入力する。

Ø         関数ＩＦとＡＮＤを使ってＨ２：Ｈ１１に安打数が６９以上、本塁打数が１０以上、打点が３０以上の場合に　○　を、そうでなければ　×　を表示する式を入力する。

 

Ａ

Ｂ

Ｃ

Ｄ

Ｅ

Ｆ

Ｇ

Ｈ

１

名前

打率

打数

安打

本塁打

打点

評価１

評価２

２

多村

 

245

74

21

53

 

 

３

前田

 

273

83

15

41

 

 

４

金城

 

289

87

3

44

 

 

５

金本

 

311

86

17

60

 

 

６

阿部

 

229

65

14

38

 

 

７

青木

 

285

83

1

10

 

 

８

赤星

 

323

90

1

15

 

 

９

種田

 

279

75

5

25

 

 

１０

新井

 

245

69

16

38

 

 

１１

シーツ

 

310

87

7

39

 

 

１２

最大値

 

 

 

 

 

 

 

１３

最小値

 

 

 

 

 

 

 

 

 

 

出席、課題のメールは　ta050029@edu.i.hosei.ac.jp　までお願いします。

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

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