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