基本的な関数
- 本章で用いる実習用ファイル
四則演算だけで様々な計算をするには限界がある。複雑な計算を効率的に行うために関数が用意されている。
A | B | C | |
---|---|---|---|
1 | 点数 | ||
2 | 山岸 | 90 | |
3 | 小片 | 95 | |
4 | 新沼 | 78 | |
5 | 谷本 | 68 | |
6 | 岸本 | 97 | |
7 | 浅倉 | 0 | |
8 | 小野 | 83 | |
9 | 小野田 | 72 | |
10 | 秋山 | 79 | |
11 | 平均 | =(B2+B3+B4+B5+B6+B7+B8+B9+B10)/9 | |
12 |
9人のテスト結果から平均点を求めたいとする。四則演算で計算しようとすると9人の点数を合計して、9で除する。平均点は73.56である。
しかし浅倉はテストを欠席しており、当初「0」点と扱っていたが、平均点算出からは外すことになった。
A | B | C | |
---|---|---|---|
1 | 点数 | ||
2 | 山岸 | 90 | |
3 | 小片 | 95 | |
4 | 新沼 | 78 | |
5 | 谷本 | 68 | |
6 | 岸本 | 97 | |
7 | 浅倉 | ||
8 | 小野 | 83 | |
9 | 小野田 | 72 | |
10 | 秋山 | 79 | |
11 | 平均 | 73.5556 | |
12 |
セルB7の「0」を削除するだけだと、空白は「0」と同じ扱いをされるので、平均点は元のままである。
A | B | C | |
---|---|---|---|
1 | 点数 | ||
2 | 山岸 | 90 | |
3 | 小片 | 95 | |
4 | 新沼 | 78 | |
5 | 谷本 | 68 | |
6 | 岸本 | 97 | |
7 | 浅倉 | NA | |
8 | 小野 | 83 | |
9 | 小野田 | 72 | |
10 | 秋山 | 79 | |
11 | 平均 | #VALUE! | |
12 |
セルB7に欠損値「NA」(文字列「欠席」でも同じ)を入力すると、何やらエラーメッセージが表示される。
欲しい値は「8人分の点数の合計/8」(82.75)である。セルB7のデータが変更されるに応じて、正しい結果を得る、ということは四則演算ではできそうもない。ということで関数の出番である。
- 関数
-
得体の知れない複雑な処理を内部でこっそりやって、結果だけ教えてくれる宝箱
- 書式
-
=関数名(処理したい値1, 処理したい値2, …, 処理したい値n)
カンマ- 関数の()の中に入れる「処理したい値」を引数(ひきすう)argumentと呼ぶ。
引数に入るデータの種類・その順序は関数によって決まっている。 - 関数が諸々処理をして教えてくれる結果を戻り値return valueと呼ぶ。
- 関数の()の中に入れる「処理したい値」を引数(ひきすう)argumentと呼ぶ。
合計:SUM(値1・範囲1, 値2・範囲2, …)
SUM関数は合計を算出したい数値を引数に取る(順序は問わない)。
A | B | C | |
---|---|---|---|
1 | 点数 | ||
2 | 山岸 | 90 | |
3 | 小片 | 95 | |
4 | 新沼 | 78 | |
5 | 谷本 | 68 | |
6 | 岸本 | 97 | |
7 | 浅倉 | NA | |
8 | 小野 | 83 | |
9 | 小野田 | 72 | |
10 | 秋山 | 79 | |
11 | 合計 | =SUM(B2,B3,B4,B5,B6,B7,B8,B9,B10) | |
12 |
これで9人の合計点が計算できる。また浅倉の点数(B7)に「NA」といった文字列を入力しても、結果に影響を与えない。
ただいちいち9つのセルを指定するのは効率が悪い。そこで連続しているセルは[範囲]として、一つの引数にまとめることができる。
A | B | C | |
---|---|---|---|
1 | 点数 | ||
2 | 山岸 | 90 | |
3 | 小片 | 95 | |
4 | 新沼 | 78 | |
5 | 谷本 | 68 | |
6 | 岸本 | 97 | |
7 | 浅倉 | NA | |
8 | 小野 | 83 | |
9 | 小野田 | 72 | |
10 | 秋山 | 79 | |
11 | 合計 | =SUM(B2:B10) | |
12 |
- [範囲]
-
開始セル : 終了セル
コロン
平均:AVERAGE(値1・範囲1, 値2・範囲2, …)
AVERAGE関数は算術平均(相加平均arithmetic mean)を求めるための関数である。
A | B | C | |
---|---|---|---|
1 | 点数 | ||
2 | 山岸 | 90 | |
3 | 小片 | 95 | |
4 | 新沼 | 78 | |
5 | 谷本 | 68 | |
6 | 岸本 | 97 | |
7 | 浅倉 | NA | |
8 | 小野 | 83 | |
9 | 小野田 | 72 | |
10 | 秋山 | 79 | |
11 | 平均 | =AVERAGE(B2:B10) | |
12 |
浅倉(セルB7)の欄を空欄にしても、文字列(「NA」、「欠席」など)を入力しても、残りの8人の合計を8で除した結果を返す。もちろん後から浅倉の(追試験の)点数を入力したら、9人分の平均点を返す。
平均:AVERAGEA(値1・範囲1, 値2・範囲2, …)
AVERAGEA関数は算術平均(相加平均arithmetic mean)を求めるための関数である。引数中の文字列およびFALSEは0、TRUEは1が入力されているものと見なす。
A | B | C | |
---|---|---|---|
1 | 点数 | ||
2 | 山岸 | 90 | |
3 | 小片 | 95 | |
4 | 新沼 | 78 | |
5 | 谷本 | 68 | |
6 | 岸本 | 97 | |
7 | 浅倉 | NA | |
8 | 小野 | 83 | |
9 | 小野田 | 72 | |
10 | 秋山 | 79 | |
11 | 平均 | =AVERAGEA(B2:B10) | |
12 |
浅倉(セルB7)の欄を空欄にするとAVERAGE(B2:B10)と同じ結果を返すが、文字列(「NA」、「欠席」など)を入力すると浅倉分は0点と見なした平均点を返す。
- ---------A関数
-
関数の中に関数名の最後にAが付く関数がある。この関数はAが付かない関数とセットで存在し、引数中の
- 文字列およびFALSE→0
- TRUE→1
が入力されているものと見なして処理を行う。
最大値:MAX(値1・範囲1, 値2・範囲2, …), 最小値:MIN(値1・範囲1, 値2・範囲2, …)
MAX関数は指定数値・範囲の最大値を返す関数である。
MIN関数は指定数値・範囲の最小値を返す関数である。
A | B | C | |
---|---|---|---|
1 | 点数 | ||
2 | 山岸 | 90 | |
3 | 小片 | 95 | |
4 | 新沼 | 78 | |
5 | 谷本 | 68 | |
6 | 岸本 | 97 | |
7 | 浅倉 | NA | |
8 | 小野 | 83 | |
9 | 小野田 | 72 | |
10 | 秋山 | 79 | |
11 | 最大(MAX) | =MAX(B2:B10) | |
12 | 最小(MIN) | =MIN(B2:B10) | |
13 | 最大(MAXA) | ||
14 | 最小(MINA) | ||
15 |
※MAXA, MINAを用いた結果を求め、比較してみよう。
数値の個数:COUNT(値1・範囲1, 値2・範囲2, …), 入力済みセルの個数:COUNTA(値1・範囲1, 値2・範囲2, …), [空白]セルの個数:COUNTBLANK(値1・範囲1, 値2・範囲2, …)
COUNT関数は指定値・範囲の数値データの個数を返す関数である。
COUNTA関数は指定値・範囲の中の入力済みセルの個数を返す関数である。
COUNTBLANK関数は指定値・範囲の中の[空白]セル(未入力セルではない)の個数を返す関数である。
A | B | C | |
---|---|---|---|
1 | 点数 | ||
2 | 山岸 | 90 | |
3 | 小片 | 95 | |
4 | 新沼 | 78 | |
5 | 谷本 | 68 | |
6 | 岸本 | 97 | |
7 | 浅倉 | NA | |
8 | 小野 | 83 | |
9 | 小野田 | 72 | |
10 | 秋山 | 79 | |
11 | 受験者数 | =COUNT(B2:B10) | |
11 | データ数 | =COUNTA(B2:B10) | |
12 | 未入力数 | =COUNTBLANK(B2:B10) | |
13 |
※浅倉(セルB7)の欄を空欄にして、結果を比較してみよう。
- 未入力セルと空白セル
-
A B C 1 No データ 2 1 3 2 =IF(TRUE,"","") 4 3 FALSE 5 COUNT =COUNT(B2:B10) 6 COUNTA =COUNTA(B2:B10) 7 COUNTBLANK =COUNTBLANK(B2:B10) 8 セルB2は未入力セルであり、セルB3は常に[空白]となる。結果は
A B C 1 No データ 2 1 3 2 4 3 FALSE 5 COUNT 0 6 COUNTA 2 7 COUNTBLANK 2 8
中央値:MEDIAN(値1・範囲1, 値2・範囲2, …)
MEDIAN関数は中央値を求めるための関数である。
A | B | C | |
---|---|---|---|
1 | 点数 | ||
2 | 山岸 | 90 | |
3 | 小片 | 95 | |
4 | 新沼 | 78 | |
5 | 谷本 | 68 | |
6 | 岸本 | 97 | |
7 | 浅倉 | NA | |
8 | 小野 | 83 | |
9 | 小野田 | 72 | |
10 | 秋山 | 79 | |
11 | 中央値 | =MEDIAN(B2:B10) | |
12 |
中央値とは数値を大小順に並べたときに真ん中に来る値である(偶数個の時は中央の二つの算術平均)。
68 | 72 | 78 | 79 | 83 | 90 | 95 | 97 |
1 | 2 | 3 | 4 | 4 | 3 | 2 | 1 |
大きい方からn番目:LARGE(値・範囲, 順位n), 小さい方からn番目:SMALL(値・範囲, 順位n)
LARGE関数は指定数値・範囲のなかで大きい方からn番目(数値)の値を返す関数である。
SMALL関数は指定数値・範囲のなかで小さい方からn番目(数値)の値を返す関数である。
A | B | C | |
---|---|---|---|
1 | 点数 | ||
2 | 山岸 | 90 | |
3 | 小片 | 95 | |
4 | 新沼 | 78 | |
5 | 谷本 | 68 | |
6 | 岸本 | 97 | |
7 | 浅倉 | NA | |
8 | 小野 | 83 | |
9 | 小野田 | 72 | |
10 | 秋山 | 79 | |
11 | 第二位 | =LARGE(B2:B10, 2) | |
12 | ブービー | =SMALL(B2:B10, 2) | |
13 |
これまでの関数は引数の順序は不定だった(すべて結果を得たい値・範囲)。この関数は順位という要素が加わるため、引数の数と順序が厳密に定まる。
LARGE()関数・SMALL()関数、引数の数は2である(値・範囲, 順位n)。
複数の値・範囲を第一引数に指定したいときには(値1・範囲1, 値2・範囲2, …)のように()で一つの引数としてまとめる。
A | B | C | |
---|---|---|---|
1 | 点数 | ||
2 | 山岸 | 90 | |
3 | 小片 | 95 | |
4 | 新沼 | 78 | |
5 | 谷本 | 68 | |
6 | 岸本 | 97 | |
7 | 浅倉 | NA | |
8 | 小野 | 83 | |
9 | 小野田 | 72 | |
10 | 秋山 | 79 | |
11 | 第二位 | =LARGE((B2:B6, B8:B10), 2) | |
12 | ブービー | =SMALL((B2:B6, B8:B10), 2) | |
13 |
浅倉を除いた範囲で、LARGE()関数、SMALL()関数を用いる。
- 第一引数
- (B2:B6, B8:B10)
- 第二引数
- 「2」(順位)
課題
下表の
を全て埋めて表を完成させよ。ただしセルに数式を入力するのは
のセルのみで、残りのセルはオートフィルを用いて入力すること。
課題:店舗売上(形式)
A | B | C | D | E | F | |
---|---|---|---|---|---|---|
1 | 瀬田店 | 大宮店 | 深草店 | 各月合計 | ||
2 | 1月 | 12500 | 24000 | 18000 | ||
3 | 2月 | 15400 | 22000 | 21500 | 58900 | |
4 | 3月 | 25400 | 26000 | 16500 | 67900 | |
5 | 4月 | 13500 | 24000 | 17500 | 55000 | |
6 | 5月 | 14000 | 26500 | 18500 | 59000 | |
7 | 6月 | 17000 | 27000 | 17500 | 61500 | |
8 | 各店平均 | 24916.67 | 18250.00 | |||
9 | 各店最高売上 | 27000 | 21500 | |||
10 | 各店最低売上 | 220000 | 16500 | |||
11 | ||||||
12 | 合計 | 平均 | 最高 | 最低 | ||
13 | 全店全期間 | 356800 | 1982.22 | 27000 | 12500 | |
14 |
課題:テスト点数(形式)
A | B | C | D | E | F | G | H | I | |
---|---|---|---|---|---|---|---|---|---|
1 | 国語 | 英語 | 数学 | 社会 | 理科 | 平均 | 数学以外の 平均 | ||
2 | 中澤 | 66 | 34 | 54 | 57 | 87 | |||
3 | 石黒 | 64 | 56 | 34 | 87 | 55 | 59.20 | 65.50 | |
4 | 飯田 | 87 | 45 | 欠席 | 65 | 64 | 62.25 | 62.25 | |
5 | 安倍 | 98 | 67 | 64 | 90 | 65 | 76.80 | 80.00 | |
6 | 福田 | 欠席 | 56 | 36 | 75 | 72 | 67.67 | ||
7 | 受験者数 | 5 | 4 | 5 | 5 | ||||
8 | 平均 | 51.60 | 47.00 | 74.80 | 68.60 | ||||
9 | 平均 欠席は0点 | 51.60 | 37.60 | 74.80 | 68.60 | ||||
10 | 数学以外の 最大 | 98 | |||||||
11 | 数学以外の 最小 | 34 | |||||||
12 | 数学以外の 上位第二位 | 90 | |||||||
13 | 数学以外の 下位第二位 | 45 | |||||||
14 |
課題:回答件数(形式)
A | B | C | D | E | |
---|---|---|---|---|---|
1 | 回答番号 | 家族人数 | 所得(千円) | 年齢 | |
2 | 1 | 5 | 250 | 72 | |
3 | 2 | 4 | NA | NA | |
4 | 3 | 2 | 570 | 55 | |
5 | 4 | 3 | 450 | 34 | |
6 | 5 | 1 | NA | 44 | |
7 | 6 | 2 | 160 | 18 | |
8 | 7 | 4 | 480 | 29 | |
9 | 全件数 | 7 | 7 | ||
10 | 有効件数 | 5 | 6 | ||
11 | 平均値 | 382 | 42 | ||
12 |