Lecture note 6th

「データ科学と社会I」　第6回講義ノート

Excelの基本操作 2

1. オートフィル

Excelの最も有用な機能の一つに、一定の規則で自動的に値や関数をセルに入力できる「オートフィル」がある。

1.1. オートフィルの使い方

(1) アクティブセルの右下にマウスカーソルをもってくると、マウスカーソルか以下のように黒十字に変わる。

　 ==>　

(2) この状態で下へドラッグ（右ボタンを押したままマウスを下へ移動させる）すると、それらのセルへ「オートフィル」が実行され、下のような入力が自動で行われる。

【注意】
・オートフィルで入力できるのは、行または列方向の一方だけである。
・複数のアクティブセルからオートフィルを行うことができる。

【演習問題1.1】 次の [ ] 内のアドレスのセルに「」内の値を入力し、( ) 内のセルにオートフィルを行え。

(1) [A1], 「1」 --> (A2:A5)

(2) [B1], 「1」, [B2], [2] --> (B3:A10)

(3) [C1], 「月」 --> (C2:C8)

(4) [D1], [May] --> (D2:D13)

(5) [E1], [子] --> (E2:E13)

(6) [F1], [皐月] --> (F2:F13)

(7) [G1], 「1」, [G2], 「3」 --> (G3:G10)

(8) [H1], 「100」, [H2], 「97」 --> (H3:H10)

(9) [I1], 「1」, [I2], 「-1」 --> (I3:I10)

(10) [J1], 「1」, [J2], 「-1」 --> (J3:J10)

1.2. オートフィルの応用問題

【演習問題1.2】 オートフィルを利用して次の表を作れ。ただし１行目に１～１５までの数を入力し、一般項を作る際にそれを参照してよい。

(1) 数列の一般項

1行：　「0から15までの整数の列」（以下１行を参照せよ）

2行：　「1以上の奇数列」

3行：　「初項1、公比2の等比数列」

4行：　「1、－１の繰り返し」

5行：　「奇数列は0、偶数列が1、－１を交互に変化する」

※B2:P5セルは、A2:A5セルからオートフィルで入力される。

(2) 複数のセルの参照

2. 論理式（AND, OR, NOT）

2.1. 論理式・真偽

「セルA1の値と10に等しい」 という命題は、Excelでは 「=(A1=10)」 と表す。

「セルA1の値は10より小さい」 という命題は、Excelでは 「=(A1<10)」 と表す。

「セルA1の値は10より小さいか等しい」 という命題は、Excelでは 「=(A1<=10)」 と表す。

これらの式が入力されたセルは、命題が真の場合には "TRUE"、偽の場合には "FALSE" を返す。

【例題2.1】 [A1]に「2」を入力し、[B1]に「A1が3より小さい」という命題を記述せよ。

【演習問題2.1】 Excelで次の命題を記述せよ。

(1) [A1] に「2」を入力して [C1] に「A1が1より大きい」

(2) [A2 ]に「○」を入力して [B2] に「A2が"●"に等しい」

(3) [A3] に「0.5」を入力して [B3] に「A3が0以上」

2.2. AND

2つ以上の命題： 「命題１」, …, 「命題ｎ」が全て成り立つ という命題は次のように表す。

「 =AND(命題１, …, 命題n) 」: （結果は "TRUE" か "FALSE" で返される。）

【演習問題2.2】 Excelで次の命題を記述せよ。

(1) [A4] に「2」、[B4] に「3」を入力して [C4] に「A4が2かつB4が3である」

(2) [D4 ]に「A4が2以上B4が5未満である」

(3) [A5 ]に「大」、[B5]に「中」、[C5]に「小」を入力して [D5] に「A5が"大"かつB5が"中"かつC5が"少"である」

2.3. OR

2つ以上の命題： 「命題１」, …, 「命題ｎ」のどれかが成り立つ という命題は次のように表す。

「 =OR(命題１, …, 命題n) 」: （結果は "TRUE" か "FALSE" で返される。）

【演習問題2.3】 Excelで次の命題を記述せよ。

(7) [E4] に「A4が2またはB4が4である」

(8) [E5 ]に「A5が"大"またはB5が"中"またはC5が"少"である」

2.4. NOT

命題を否定する命題は次のように表す。

「=NOT(命題)」

【演習問題2.4】 Excelで次の命題を記述せよ。

(9) [F4] にE4の否定

(10) [F5] にE5の否定

3. 条件式

3.1. IF

「命題が真の場合は1, 偽の場合は2である」は、次のIF関数で与えられる。

「=IF(命題, 1, 2)」

【演習問題3.1】

(1) [A1] に「0.5」を入力し、[C1] にA1の値が0未満ならば"負", 0以上ならば"非負"を表示させよ。

(2) [D1 ]にA1の値が0未満ならば"負", 0ならば"零"、0より大きければ"正"を表示させよ。

(3) [A7] に「-1」、「0」、「1」などを入力し、[C7] と [D7] の変化を確かめよ。

3.2. COUNTIF

COUNTIF は以下の記述により、範囲内のセル中で条件を満たすものの個数 を返す。

「=COUNTIF(範囲, 条件)」

【例題3.2.1】 [A8:J8] に「赤」、「青」、「黄」のどれかを入力し、[K8] に青の個数を表示させよ。

解） =COUNTIF(A8:J8,"青")

【例題3.2.2】 [A9:J9]にRAND関数を用いて0以上1未満の乱数を表示させ、[K9] にそれらの中で「0.2以上」のものの個数を表示させよ。

解） =COUNTIF(A9:J9,">="&0.2)

【演習問題3】 Excel を用いて次の問いに答えよ。

(1) [A2:J2] に「赤」、「青」、「黄」のどれかを入力し、[L2]に黄の個数を表示させよ。

(2) [L3] に青または黄色の個数を表示させよ。

(3) [A4:J4] にRAND関数を用いて0以上1未満の乱数を表示させ、[L4] にそれらの中で「0.2以上、0.6以下」のものの個数を表示させよ。

4. Excel の関数と応用

【問題4】 次の200人の成績データ（100点満点）を分析する。

94	51	66	74	85	36	72	73	64	80	50	68	88	47	72	86	88	90	58	58
94	69	79	62	84	29	65	74	59	92	68	72	93	51	64	77	78	93	54	68
62	77	93	59	72	34	69	52	58	87	65	56	85	58	73	77	96	79	66	68
67	75	84	69	61	70	65	92	69	64	96	65	91	55	62	73	87	87	71	66
63	59	84	51	56	70	79	94	71	62	58	36	84	52	66	82	50	73	77	85
98	62	72	78	80	66	74	81	61	53	18	70	78	32	78	100	46	82	84	87
96	58	74	78	81	90	81	87	51	57	62	47	80	43	74	90	56	74	72	84
92	52	42	70	80	90	54	74	92	82	91	38	71	83	82	90	50	86	82	89
95	65	66	66	75	22	83	50	75	81	59	70	87	59	73	98	48	77	77	84
88	57	71	74	90	46	80	88	76	81	74	48	91	40	67	94	47	79	82	67

上の表の得点は次のルールで並んでいる。

・得点表の得点には、18-2201-001 から 18-2201-200 までの学籍番号が対応している。

・学籍番号の最後の3ケタの数字が001～200まで変化する。

・得点表の点数と学籍番号の対応は、左上から下に向かって、学籍番号の下3ケタの数が小さい順である。

・成績は得点から次のように評価される：

秀	優	良	可	不可
90点以上	80点以上90点未満	70点以上80点未満	60点以上70点未満	60点未満

4.1. シートの準備

・Excel のブックを開き上の表の数値を全て選んで「Sheet2」に張り付けるコピーする。

・「Sheet1」の名前を「成績表」に変更し、[A2:A201] に1から200までの通し番号をつける。

・[B2:B201] に学籍番号を入力する。

・[C2:C201] に「Sheet2」のデータを順番にコピーする。

4.2. 統計処理の実践

【演習問題4】 次の図にならって表を完成させよ。

【ヒント】 x を得点、E を平均、σ を標準偏差したとき偏差値 T は次式で与えられる：

T = 50 + 10(x-E)/σ

>5. 今回の課題

【演習問題4】を完成せよ。

「2018_05_23」という名前のブックを添付ファイルとして提出せよ。

提出期限： 5月28日（月）２４時まで

課題の解答例

Back

94	51	66	74	85	36	72	73	64	80	50	68	88	47	72	86	88	90	58	58
94	69	79	62	84	29	65	74	59	92	68	72	93	51	64	77	78	93	54	68
62	77	93	59	72	34	69	52	58	87	65	56	85	58	73	77	96	79	66	68
67	75	84	69	61	70	65	92	69	64	96	65	91	55	62	73	87	87	71	66
63	59	84	51	56	70	79	94	71	62	58	36	84	52	66	82	50	73	77	85
98	62	72	78	80	66	74	81	61	53	18	70	78	32	78	100	46	82	84	87
96	58	74	78	81	90	81	87	51	57	62	47	80	43	74	90	56	74	72	84
92	52	42	70	80	90	54	74	92	82	91	38	71	83	82	90	50	86	82	89
95	65	66	66	75	22	83	50	75	81	59	70	87	59	73	98	48	77	77	84
88	57	71	74	90	46	80	88	76	81	74	48	91	40	67	94	47	79	82	67

94	51	66	74	85	36	72	73	64	80	50	68	88	47	72	86	88	90	58	58
94	69	79	62	84	29	65	74	59	92	68	72	93	51	64	77	78	93	54	68
62	77	93	59	72	34	69	52	58	87	65	56	85	58	73	77	96	79	66	68
67	75	84	69	61	70	65	92	69	64	96	65	91	55	62	73	87	87	71	66
63	59	84	51	56	70	79	94	71	62	58	36	84	52	66	82	50	73	77	85
98	62	72	78	80	66	74	81	61	53	18	70	78	32	78	100	46	82	84	87
96	58	74	78	81	90	81	87	51	57	62	47	80	43	74	90	56	74	72	84
92	52	42	70	80	90	54	74	92	82	91	38	71	83	82	90	50	86	82	89
95	65	66	66	75	22	83	50	75	81	59	70	87	59	73	98	48	77	77	84
88	57	71	74	90	46	80	88	76	81	74	48	91	40	67	94	47	79	82	67

94	51	66	74	85	36	72	73	64	80	50	68	88	47	72	86	88	90	58	58
94	69	79	62	84	29	65	74	59	92	68	72	93	51	64	77	78	93	54	68
62	77	93	59	72	34	69	52	58	87	65	56	85	58	73	77	96	79	66	68
67	75	84	69	61	70	65	92	69	64	96	65	91	55	62	73	87	87	71	66
63	59	84	51	56	70	79	94	71	62	58	36	84	52	66	82	50	73	77	85
98	62	72	78	80	66	74	81	61	53	18	70	78	32	78	100	46	82	84	87
96	58	74	78	81	90	81	87	51	57	62	47	80	43	74	90	56	74	72	84
92	52	42	70	80	90	54	74	92	82	91	38	71	83	82	90	50	86	82	89
95	65	66	66	75	22	83	50	75	81	59	70	87	59	73	98	48	77	77	84
88	57	71	74	90	46	80	88	76	81	74	48	91	40	67	94	47	79	82	67