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

Excelの使い方 一応シラバスでは 先週 今週 来週 並び替えとオートフィルタ グラフ表示の編集 セル入力の基本操作 クロス集計 数式の記述法 よく利用される関数 範囲指定とグラフの描画 表とグラフの作成演習 今週 IF関数を用いた判定と表示 その他の関数 判定表の作成演習 ヒストグラムの作成演習 来週 並び替えとオートフィルタ グラフ表示の編集 クロス集計 上記機能の演習

比較演算子 比較結果として真偽値を得る 真なら TRUE 偽なら FALSE 資料001: 比較演算子 数学 Excel ＝ = ≠ <> ＜ < ≦ <= ＞ > ≧ >= 比較結果として真偽値を得る 真なら TRUE 偽なら FALSE

文字列の結合 数式中の文字列 文字列の結合演算子「&」 「"」(ダブルクォーテーション)で囲む 演算子前後の値を繋げた文字列を得る 資料001: 文字列の演算 文字列の結合 " 2 ふ ↑Shift + 数式中の文字列 「"」(ダブルクォーテーション)で囲む 文字列の結合演算子「&」 演算子前後の値を繋げた文字列を得る

文字列の比較 数値との比較 文字列同士の比較 「文字列>数値」が成立 先頭から文字単位で文字コードを比較 資料001: 文字列の比較 文字列の比較 数値との比較 「文字列>数値」が成立 文字列同士の比較 先頭から文字単位で文字コードを比較 より大きな文字コードでより長い文字列が大 常に文字列>数値 より大きな文字コードが大 より長い文字列が大 文字列としては “1234” より “234” の方が大きい

文字コードの例(Shift JIS 1バイト目) 資料002 文字コードの例(Shift JIS 1バイト目) 半角1文字を1バイト(=8bit)のコードで表す 全角1文字は2バイト(例: １=824Fh,あ=82A0h) 16進数 1 2 3 4 5 6 7 8 9 A B C D E F 10進数 10 11 12 13 14 15 NUL SOH STX EXT EOT ENQ ACK BEL BS HT LF HM CL CR SO SI 16 DLE DC1 DC2 DC3 DC4 NAK SYN ETB CAN EM SUB ESC → ← ↑ ↓ 20 32 SPC ! " # $ % & ' ( ) * + , - . / 30 48 : ; < = > ? 40 64 @ 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 128 90 144 A0 160 ｡ ｢ ｣ ､ ･ ｦ ｧ ｨ ｩ ｪ ｫ ｬ ｭ ｮ ｯ B0 176 ｰ ｱ ｲ ｳ ｴ ｵ ｶ ｷ ｸ ｹ ｺ ｻ ｼ ｽ ｾ ｿ C0 192 ﾀ ﾁ ﾂ ﾃ ﾄ ﾅ ﾆ ﾇ ﾈ ﾉ ﾊ ﾋ ﾌ ﾍ ﾎ ﾏ D0 208 ﾐ ﾑ ﾒ ﾓ ﾔ ﾕ ﾖ ﾗ ﾘ ﾙ ﾚ ﾛ ﾜ ﾝ ﾞ ﾟ E0 224 F0 240 １=0x824F=33359, あ=0x82A0=33440 制御文字 全角文字1バイト目用(60通り)

コンピュータの中での文字 コンピュータの中 文字も数値(文字コード)として記録されている 文字コードに対応したフォントを表示している 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文字づつ比較 文字単位の大小関係が決着した所で比較終了 比較する文字がない場合、長いほうが大 abc = abc abc < acb abcd > abc abcd < acb a = a b = b c = c a = a b < c c > b a = a b = b c = c d > a = a b < c c > b d > 右が大 右が大 同一 左が大

名前の定義 セルに名前を付けて参照し易く出来る 選択セルを「数式」→「名前の定義」で命名 資料001: 名前の定義 名前の有効範囲は ブックまたはワークシート内を 選択可能

定義した名前を利用した参照 定義した名前を用いて数式を記述できる セル名で参照するより意味が分かり易くなる 資料001: 名前の定義 名前が定義されている場合 アクティブセル名にも表示される

資料001: 名前の定義 名前の管理 定義した名前の一覧・修正・削除 定義した名前を数式へ入力する際は 「数式で使用」ボタンから選択可能

IF関数 IF文 書式(詳細は「IF関数」参照) 使用例 プログラムで条件分岐に使われる Excelでは関数扱い(IF関数) 論理式 真 偽 処理 IF文 プログラムで条件分岐に使われる Excelでは関数扱い(IF関数) 数式の一部として用いる 書式(詳細は「IF関数」参照) IF(論理式, 真の場合, 偽の場合) 使用例 数値を返す例 文字列を返す例

資料001: 成年・未成年の判別 IF関数の使用例 成年・未成年の判別 年齢が20歳以上かで判別してみる

論理演算関数 AND, OR, NOT 関数 条件を論理演算する場合に使う AND(条件式1, 条件式2, …) 資料001: 論理演算関数 論理演算関数 AND, OR, NOT 関数 条件を論理演算する場合に使う AND(条件式1, 条件式2, …) OR(条件式1, 条件式2, …) NOT(条件式) 条件1 条件2 AND OR FALSE TRUE 条件 NOT FALSE TRUE

うるう年の判別 うるう年の定義 うるう年を1,通常年を0として 4で割り切れるもの(X)はうるう年 資料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

うるう年の判別(式の解説1/3) =IF(X, IF(Y, IF(Z, 1, 0), 1), 0) 資料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

うるう年の判別(式の解説2/3) =IF(Z, 1, IF(Y, 0, IF(X, 1, 0))) 資料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

うるう年の判別(式の解説3/3) =IF(OR(AND(X, NOT(Y)), Z), 1, 0) 資料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: うるう年の判別 ここには名前の定義で MOD関数は ワークシート内のみ有効な 剰余を求める関数。 西暦, X, Y, Z という名前を付けています MOD関数は 剰余を求める関数。 剰余が0なら 割り切れるということ。

COUNT系関数(1/2) セルの個数を数える COUNT(値1, 値2, …) COUNTA (値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(範囲, 検索条件, 合計範囲) セル範囲に含まれる数値をすべて合計 SUMIF(範囲, 検索条件, 合計範囲) 指定された検索条件に一致するセルの値を合計 SUMIFS(合計対象範囲, 条件範囲1, 条件1, 条件範囲2, 条件2, ...) セル範囲内で、複数の検索条件を満たすセルの値を合計(Excel2007以降)

資料001: SUM系関数の例 SUM系関数(2/2) SUM系関数の使用例

成績表の例(集計) (1/3) 中間・期末テストの成績(資料101) 評点(中間期末の合計点を100点換算) 平均(AVERAGE関数), 標準偏差(STDEV関数)

成績表の例(集計) (2/3) やり方はいくつもある 評点(E2セルの場合) 平均(C13セルの場合) なるべく関数で一発計算できるように =(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, "合") 人数を数えるか、合否の合計を取るか =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) 絶対参照を使う 文字列連結演算子「&」を使う あとはオートフィルで 人数合計はSUM関数 =COUNTIFS(成績!C2:C11, ">=0", 成績!C2:C11, "<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等で検索してみましょう 配列数式を使うので若干難解？ 分析ツールのヒストグラム機能を使う方法 集計結果を生成する 元の表を変えたら要作り直し Google等で検索してみましょう 「Excel FREQUENCY関数」 「Excel 分析ツール ヒストグラム」

どうやって探すのか？ 以下の状況でどうやって見つける？ 検索キーワードを工夫する FREQUENCY関数なんて知らない 分析ツールなんて知らない 検索キーワードを工夫する 「Excel 成績表 人数分布」 ↑検索キーワードをスペースで並べると AND (=すべてのキーワードを含む)検索になる