Transcript 情報リテラシー演習 第5週 Excelの使い方2 Excelの使い方 一応

情報リテラシー演習
第5週
Excelの使い方2
Excelの使い方
• 一応シラバスでは
• 先週
–
–
–
–
–
セル入力の基本操作
数式の記述法
よく利用される関数
範囲指定とグラフの描画
表とグラフの作成演習
• 今週
– IF関数を用いた判定と表
示
– その他の関数
– 判定表の作成演習
– ヒストグラムの作成演習
• 来週
–
–
–
–
並び替えとオートフィルタ
グラフ表示の編集
クロス集計
上記機能の演習
資料001: 比較演算子
比較演算子
• 比較結果として真偽値を得る
– 真なら TRUE
– 偽なら FALSE
数学
Excel
＝
=
≠
<>
＜
<
≦
<=
＞
>
≧
>=
資料001: 文字列の演算
文字列の結合
• 数式中の文字列
↑Shift
– 「"」(ダブルクォーテーション)で囲む
• 文字列の結合演算子「&」
– 演算子前後の値を繋げた文字列を得る
+
"
2 ふ
資料001: 文字列の比較
文字列の比較
• 数値との比較
– 「文字列>数値」が成立
• 文字列同士の比較
– 先頭から文字単位で文字コードを比較
– より大きな文字コードでより長い文字列が大
常に文字列>数値
より大きな文字コードが大
より長い文字列が大
文字列としては “1234” より
“234” の方が大きい
資料002
文字コードの例(Shift JIS 1バイト目)
• 半角1文字を1バイト(=8bit)のコードで表す
• 全角1文字は2バイト(例: １=824Fh,あ=82A0h)
0
1
2
3
4
5
6
7
8
10進数
0
1
2
3
4
5
6
7
8
0
0 NUL SOH STX EXT EOT ENQ ACK BEL BS HT
10
16 DLE DC1 DC2 DC3 DC4 NAK SYN ETB CAN EM
"
#
$
%
&
'
(
)
20
32 SPC !
1
2
3
4
5
6
7
8
9
30
48 0
B
C
D
E
F
G
H
I
40
64 @ A
P
Q
R
S
T
U
V
W
X
Y
50
80
a
b
c
d
e
f
g
h
i
60
96 `
q
r
s
t
u
v
w
x
y
70
112 p
80
128
90
144
｡
｢
｣
､
･
ｦ
ｧ
ｨ
ｩ
A0
160
ｱ
ｲ
ｳ
ｴ
ｵ
ｶ
ｷ
ｸ
ｹ
B0
176 ｰ
ﾀ
ﾁ
ﾂ
ﾃ
ﾄ
ﾅ
ﾆ
ﾇ
ﾈ
ﾉ
C0
192
ﾑ
ﾒ
ﾓ
ﾔ
ﾕ
ﾖ
ﾗ
ﾘ
ﾙ
D0
208 ﾐ
E0
224
F0
240
16進数
制御文字
9
9
A
10
LF
B
11
HM
C
12
CL
D
13
E
14
F
15
CR
SO
SI
SUB ESC →
←
↑
↓
*
+
,
-
.
/
:
;
<
=
>
?
J
K
L
M
N
O
Z
[
\
]
^
_
j
k
l
m
n
o
z
{
|
}
~
DEL
ｪ
ｫ
ｬ
ｭ
ｮ
ｯ
ｺ
ｻ
ｼ
ｽ
ｾ
ｿ
ﾊ
ﾋ
ﾌ
ﾍ
ﾎ
ﾏ
ﾚ
ﾛ
ﾜ
ﾝ
ﾞ
ﾟ
全角文字1バイト目用(60通り)
コンピュータの中での文字
• コンピュータの中
– 文字も数値(文字コード)として記録されている
– 文字コードに対応したフォントを表示している
This is a text file.
これはテキストファイルです。
文字として見えているが本当は文字コードの羅列
0000000:
0000010:
0000020:
0000030:
5468
696c
8358
8142
6973 2069 7320 6120 7465 7874 2066
652e 0d0a 82b1 82ea 82cd 8365 834c
8367 8374 8340 8343 838b 82c5 82b7
0d0a
This is a text f
ile...これはテキ
ストファイルです
。..
文字列の比較
• 先頭から1文字づつ比較
– 文字単位の大小関係が決着した所で比較終了
– 比較する文字がない場合、長いほうが大
abc = abc
a = a
b = b
c = c
abc < acb
a = a
b < c
c > b 右が大
同一
abcd < acb
abcd > abc
a
b
c
d
= a
= b
= c
>
a
b
c
d
左が大
= a
< c
> b 右が大
>
資料001: 名前の定義
名前の定義
• セルに名前を付けて参照し易く出来る
– 選択セルを「数式」→「名前の定義」で命名
名前の有効範囲は
ブックまたはワークシート内を
選択可能
資料001: 名前の定義
定義した名前を利用した参照
• 定義した名前を用いて数式を記述できる
– セル名で参照するより意味が分かり易くなる
名前が定義されている場合
アクティブセル名にも表示される
資料001: 名前の定義
名前の管理
• 定義した名前の一覧・修正・削除
定義した名前を数式へ入力する際は
「数式で使用」ボタンから選択可能
資料001: IF関数
IF関数
論理式
• IF文
– プログラムで条件分岐に使われる
– Excelでは関数扱い(IF関数)
• 数式の一部として用いる
• 書式(詳細は「IF関数」参照)
– IF(論理式, 真の場合, 偽の場合)
• 使用例
数値を返す例
文字列を返す例
偽
真
処理
処理
資料001: 成年・未成年の判別
IF関数の使用例
• 成年・未成年の判別
– 年齢が20歳以上かで判別してみる
資料001: 論理演算関数
論理演算関数
• AND, OR, NOT 関数
– 条件を論理演算する場合に使う
• AND(条件式1, 条件式2, …)
• OR(条件式1, 条件式2, …)
• NOT(条件式)
条件1
条件2
AND
OR
条件
NOT
FALSE
FALSE
FALSE
FALSE
FALSE
TRUE
FALSE
TRUE
FALSE
TRUE
TRUE
FALSE
TRUE
FALSE
FALSE
TRUE
TRUE
TRUE
TRUE
TRUE
資料001: うるう年の判別
うるう年の判別
• うるう年の定義
• 4で割り切れるもの(X)はうるう年
• そのうち100で割り切れるもの(Y)は除外
• そのうち400で割り切れるもの(Z)は含む
• うるう年を1,通常年を0として
•
•
•
•
例えば次のように書ける
=IF(X, IF(Y, IF(Z, 1, 0), 1), 0)
=IF(Z, 1, IF(Y, 0, IF(X, 1, 0)))
=IF(OR(AND(X, NOT(Y)), Z), 1, 0)
X
Y
Z
資料001: うるう年の判別
うるう年の判別(式の解説1/3)
• =IF(X, IF(Y, IF(Z, 1, 0), 1), 0)
• =IF(X,
, 0)
IF(Y,
, 1)
IF(Z, 1, 0)
外側から順に判別している
X
Y
Z
資料001: うるう年の判別
うるう年の判別(式の解説2/3)
• =IF(Z, 1, IF(Y, 0, IF(X, 1, 0)))
• =IF(Z, 1,
)
IF(Y, 0,
)
IF(X, 1, 0)
内側から順に判別している
X
Y
Z
資料001: うるう年の判別
うるう年の判別(式の解説3/3)
• =IF(OR(AND(X, NOT(Y)), Z), 1, 0)
論理演算により
• =IF(
, 1, 0) うるう年とそれ以外に分離
青領域全体
OR(
)
AND(
), Z
X, NOT(Y) 内側の青領域
外側の青領域
X
Y
Z
資料001: うるう年の判別
うるう年の判別結果
ここには名前の定義で
ワークシート内のみ有効な
西暦, X, Y, Z という名前を付けています
MOD関数は
剰余を求める関数。
剰余が0なら
割り切れるということ。
COUNT系関数(1/2)
• セルの個数を数える
– COUNT(値1, 値2, …)
• 数値セルや数値の個数
– COUNTA (値1, 値2, …)
• 空白でないセルや空白でない値の個数
– COUNTBLANK (値1, 値2, …)
• 空白セルや空白の値の個数
– COUNTIF (範囲, 条件)
• 条件に合致するセルの個数
– COUNTIFS (範囲1, 条件1, 範囲2, 条件2, …)
• 複数の条件に合致するセルの個数(Excel2007以降)
資料001: COUNT系関数の例
COUNT系関数(2/2)
• COUNT系関数の使用例
SUM系関数(1/2)
• 値を合計する
– SUM(数値1, 数値2, …)
• セル範囲に含まれる数値をすべて合計
– SUMIF(範囲, 検索条件, 合計範囲)
• 指定された検索条件に一致するセルの値を合計
– SUMIFS(合計対象範囲, 条件範囲1, 条件1, 条件
範囲2, 条件2, ...)
• セル範囲内で、複数の検索条件を満たすセルの値を
合計(Excel2007以降)
資料001: SUM系関数の例
SUM系関数(2/2)
• SUM系関数の使用例
成績表の例(集計) (1/3)
• 中間・期末テストの成績(資料101)
– 評点(中間期末の合計点を100点換算)
– 平均(AVERAGE関数), 標準偏差(STDEV関数)
成績表の例(集計) (2/3)
• やり方はいくつもある
• 評点(E2セルの場合)
–
–
–
–
–
–
=(C2+D2)/2
=SUM(C2:D2)/2
=SUM(C2:D2)/COUNT(C2:D2)
=AVERAGE(C2:D2)
=C2*50/100+D2*50/100
中間、期末で点数配分が異なる場合もある
• 平均(C13セルの場合)
–
–
–
–
=(C2+C3+C4+C5+C6+C7+C8+C9+C10+C11)/10
=SUM(C2:C11)/10
=SUM(C2:C11)/COUNT(C2:C11)
=AVERAGE(C2:C11)
• なるべく関数で一発計算できるように
• 残りはオートフィルで
成績表の例(集計) (3/3)
• 集計方法の一例(資料102)
オートフィル
成績表の例(桁数調整) (1/2)
• 調整したい部分選択「ホーム」→「桁数調整」
– または、セルの書式設定ダイアログから設定
成績表の例(桁数調整) (2/2)
• 小数点以下1桁で揃えた(資料103)
成績表の例(合否判定) (1/5)
• 評点60点以上合格、それ未満不合格
– IF関数も利用して合否の表示
– COUNTIF関数で合否人数の集計も
成績表の例(合否判定) (2/5)
• やはりやり方はいくつもある
• 合否判定(F2セルの場合)
•
•
•
•
•
=IF(AVERAGE(C2:D2)>=60, "合", "否")
=IF(E2>=60, "合", "否")
=IF(60<=E2, "合", "否")
=IF(E2<60, "否", "合")
境界値には注意(未満、以下、以上、超)
• なるべく計算済みの結果を利用すると楽
成績表の例(合否判定) (3/5)
• 合否人数の集計
• =COUNTIF(F2:F11, "合")
• =COUNTIF(F2:F11, "否")
• 合計人数は迷うところ
• 人数を数えるか、合否の合計を取るか
• =COUNTA(F2:F11)
• =SUM(F15:F16)
• 何のための集計項目か？
• 入力した数式に間違いがないか確認の意味？
• であれば合否の合計の方がよい？
• 人数合計、合否人数合計の2項目あった方がベターかも？
成績表の例(合否判定) (4/5)
• 判定方法の一例(資料104)
オートフィルで
成績表の例(合否判定) (5/5)
• 合否判定結果
成績表の例(得点分布) (1/4)
• 「得点分布」ワークシート(資料105)
• 10点区切りで人数集計(○点以上、○+10点未満)
• 90～100点の区間だけは90点以上100点以下
• 少し工夫が必要(上限条件の列設ける)
成績表の例(得点分布) (2/4)
• やはりやり方はいろいろだが
• D2セル(中間の0～10点)の場合
• =COUNTIF(成績!C2:C11, ">=0")-COUNTIF(成績!C2:C11, ">=10")
• =COUNTIFS(成績!C2:C11, ">=0", 成績!C2:C11, "<10")
• オートフィルしたいのだが
• 「成績!C2:C11」の行範囲が自動的に変わる
• $をつけて行のみ絶対参照にして固定
• 列は中間、期末、評点で自動で変わってほしい
• 文字列(得点範囲)が自動で変わらない
成績表の例(得点分布) (3/4)
• 絶対参照を使う
• =COUNTIFS(成績!C2:C11, ">=0", 成績!C2:C11, "<10")
• ↓
• =COUNTIFS(成績!C$2:C$11, ">=0", 成績!C$2:C$11, "<10")
• 文字列連結演算子「&」を使う
• =COUNTIFS(成績!C$2:C$11, ">=0", 成績!C$2:C$11, "<10")
• ↓
• =COUNTIFS(成績!C$2:C$11, ">="&$A2, 成績!C$2:C$11, $B2&$C2)
• あとはオートフィルで
• 人数合計はSUM関数
成績表の例(得点分布) (4/4)
• 集計方法の一例(資料106)
成績表の例(ヒストグラム) (1/9)
• 中間(D1:D11),「グラフの作成」,「集合縦棒」
成績表の例(ヒストグラム) (2/9)
• タイトルをクリックして編集
ここは得点の範囲にしたい
成績表の例(ヒストグラム) (3/9)
• 「グラフツール」,「デザイン」,「データの選択」
– 横軸ラベル「編集」で「得点分布!$A$2:$A$11」に
成績表の例(ヒストグラム) (4/9)
• 軸の調整
成績表の例(ヒストグラム) (5/9)
• 凡例の表示
成績表の例(ヒストグラム) (6/9)
• 凡例の調整
背景の塗りつぶしと
枠線の描画も設定
成績表の例(ヒストグラム) (7/9)
• 同様にして、期末、評点も作成(資料107)
比較対象となるグラフは
軸のスケールを統一する
成績表の例(ヒストグラム) (8/9)
• 同様に中間～評点(D1:F11)を選択しグラフ化
– 「集合縦棒」、「3-D縦棒」それぞれ作ってみる
成績表の例(ヒストグラム) (9/9)
• あとは見易く、分かり易く調整を
– タイトル、軸ラベル、3-Dの表示角、等々
– サンプル(資料108)
• この手のグラフは分けた方が見易いかも？
成績表の例(評価) (1/5)
• 秀優良可不可の5段階評定を行う
– それぞれ90,80,70,60,0点以上
検索値
昇順でソートされた
基準得点と評価の対応表を用意する
点数に対応した評価を探してきて
表示したい
成績表の例(評価) (2/5)
• VLOOKUP関数を使う(詳細はヘルプ参照)
– VLOOKUP(検索値,範囲,列番号,検索の型)
– 範囲の左端の列から検索値を探す
– 見つかった行の列番号で指定されたデータ返す
戻す値
検索値と
– 検索の型
• TRUEにすると
– 検索値未満の最大値を見つける
• FALSEにすると
– 完全に一致するものを見つける
– みつからなければ #N/A エラー値を返す
比較する値
列番号2
成績表の例(評価) (3/5)
• 「成績」ワークシートG2セル
– =VLOOKUP(E2,評価基準!A$2:B$6,2,TRUE)
– 範囲の行は絶対参照で固定する
– あとはオートフィルして完成
成績表の例(評価) (4/5)
• 評価基準に人数分布を作成
– C2セル
• =COUNTIF(成績!G$2:G$11,B2)
• オートフィルするので範囲の行を絶対参照で固定
成績表の例(評価) (5/5)
• ここまでの結果(資料109)
成績表の例(オートフィルター)
• オートフィルター
– ソート(並べ替え)やフィルタリング(データ抽出)を
簡単に実行出来る
– 見出しを選択して「データ」→「フィルター」
並べ替えの際の注意
• 並べ替えると元の並び順が失われる!!!
• 元に戻すには
– 「元に戻す」を使う
– あらかじめ元の並び順の情報を入れておく
• 出席番号等
ウインドウ枠の固定
• 見出し行をスクロールさせないようにできる
– 大きな表、表示する際に便利
– 見出し行に合わせ「表示」,「ウインドウ枠の固定」
スクロールしても
見出し行が残る
成績表の例(最終結果)
• 資料110
得点分布の集計(別の方法)
• FREQUENCY関数を使う方法
– 配列数式を使うので若干難解？
• 分析ツールのヒストグラム機能を使う方法
– 集計結果を生成する
– 元の表を変えたら要作り直し
• Google等で検索してみましょう
– 「Excel FREQUENCY関数」
– 「Excel 分析ツール ヒストグラム」
どうやって探すのか？
• 以下の状況でどうやって見つける？
– FREQUENCY関数なんて知らない
– 分析ツールなんて知らない
• 検索キーワードを工夫する
– 「Excel 成績表 人数分布」
– ↑検索キーワードをスペースで並べると
– AND (=すべてのキーワードを含む)検索になる