Excel講座

基本的な関数

本章で用いる実習用ファイル

excelBasicFunctions

四則演算だけで様々な計算をするには限界がある。複雑な計算を効率的に行うために関数が用意されている。

	A	B
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
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
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と呼ぶ。

合計:SUM(値1・範囲1, 値2・範囲2, …)

SUM関数は合計を算出したい数値を引数に取る(順序は問わない)。

	A	B
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
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
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
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
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
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
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
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
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
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
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
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
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
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