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

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

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

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

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

文字コードの例 (Shift JIS 1 バイト 目 ) 半角 1 文字を 1 バイト (=8bit) のコードで表す 全角 1 文字は 2 バイト ( 例 : １ =824F h, あ =82A0 h ) 制御文字全角文字 1 バイト目用 (60 通り ) 資料 進数 ABCDEF 10 進数 NULSOHSTXEXTEOTENQACKBELBSHTLFHMCLCRSOSI 1016 DLEDC1DC2DC3DC4NAKSYNETBCANEMSUBESC→←↑↓ 2032 SPC!"#$%&'()*+,-./ :;<=>? 5080 PQRSTUVWXYZ[\]^_ 6096 `abcdefghijklmno pqrstuvwxyz{|}~DEL A0160 ｡｢｣､･ｦｧｨｩｪｫｬｭｮｯ B0176 ｰｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿ C0192 ﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏ D0208 ﾐﾑﾒﾓﾔﾕﾖﾗﾘﾙﾚﾛﾜﾝﾞﾟ E0224 F0240

コンピュータの中での文字 コンピュータの中 – 文字も数値 ( 文字コード ) として記録されてい る – 文字コードに対応したフォントを表示してい る This is a text file. これはテキストファイルです。 : This is a text f : 696c 652e 0d0a 82b1 82ea 82cd c ile... これはテキ : b 82c5 82b7 ストファイルです : d0a 。.. 文字として見えているが本当は文字コードの羅列

文字列の比較 先頭から 1 文字づつ比較 – 文字単位の大小関係が決着した所で比較終了 – 比較する文字がない場合、長いほうが大 a = a b = b c = c d > a = a b b a = a b b d > abc = abc a = a b = b c = c abcd > abc abc < acb abcd < acb 同一 右が大 左が大 右が大

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

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

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

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

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

論理演算関数 AND, OR, NOT 関数 ANDORNOT – 条件を論理演算する場合に使う AND( 条件式 1, 条件式 2, …) AND OR( 条件式 1, 条件式 2, …) OR NOT( 条件式 ) NOT 条件 1 条件 2 ANDOR FALSE TRUEFALSETRUE FALSE TRUE 条件 NOT FALSETRUE FALSE 資料 001資料 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) 資料 001資料 001: うるう年の判別 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資料 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) 内側から順に判別している 資料 001資料 001: うるう年の判別 X Y Z

うるう年の判別 ( 式の解説 3/3) =IF(OR(AND(X, NOT(Y)), Z), 1, 0) =IF(, 1, 0) OR( ) AND( ), Z X, NOT(Y) 外側の青領域 内側の青領域 青領域全体 論理演算により うるう年とそれ以外に分離 資料 001資料 001: うるう年の判別 X Y Z

うるう年の判別結果 ここには名前の定義で ワークシート内のみ有効な 西暦, X, Y, Z という名前を付けています MOD 関数MOD 関数は 剰余を求める関数。 剰余が 0 なら 割り切れるということ。 資料 001資料 001: うるう年の判別

COUNT 系関数 (1/2) セルの個数を数える – COUNT( 値 1, 値 2, …) COUNT 数値セルや数値の個数 – COUNTA ( 値 1, 値 2, …) COUNTA 空白でないセルや空白でない値の個数 – COUNTBLANK ( 値 1, 値 2, …) COUNTBLANK 空白セルや空白の値の個数 – COUNTIF ( 範囲, 条件 ) COUNTIF 条件に合致するセルの個数 – COUNTIFS ( 範囲 1, 条件 1, 範囲 2, 条件 2, …) COUNTIFS 複数の条件に合致するセルの個数 (Excel2007 以降 )

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

SUM 系関数 (1/2) 値を合計する – SUM( 数値 1, 数値 2, …) SUM セル範囲に含まれる数値をすべて合計 – SUMIF( 範囲, 検索条件, 合計範囲 ) SUMIF 指定された検索条件に一致するセルの値を合計 – SUMIFS( 合計対象範囲, 条件範囲 1, 条件 1, 条件 範囲 2, 条件 2,...) SUMIFS セル範囲内で、複数の検索条件を満たすセルの値 を合計 (Excel2007 以降 )

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

成績表の例 ( 集計 ) (1/3) 中間・期末テストの成績 ( 資料 101) 資料 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) 資料 102 オートフィル

成績表の例 ( 桁数調整 ) (1/2) 調整したい部分選択「ホーム」 → 「桁数調 整」 – または、セルの書式設定ダイアログから設定

成績表の例 ( 桁数調整 ) (2/2) 小数点以下 1 桁で揃えた ( 資料 103) 資料 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) 資料 104 オートフィルで

成績表の例 ( 合否判定 ) (5/5) 合否判定結果

成績表の例 ( 得点分布 ) (1/4) 「得点分布」ワークシート ( 資料 105) 資料 点区切りで人数集計 (○ 点以上、 ○+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) 資料 106

成績表の例 ( ヒストグラム ) (1/9) 中間 (D1:D11), 「グラフの作成」, 「集合縦 棒」

成績表の例 ( ヒストグラム ) (2/9) タイトルをクリックして編集 ここは得点の範囲にしたい

成績表の例 ( ヒストグラム ) (3/9) 「グラフツール」, 「デザイン」, 「データ の選択」 – 横軸ラベル「編集」で「得点分 布 !$A$2:$A$11 」に

成績表の例 ( ヒストグラム ) (4/9) 軸の調整

成績表の例 ( ヒストグラム ) (5/9) 凡例の表示

成績表の例 ( ヒストグラム ) (6/9) 凡例の調整 背景の塗りつぶしと 枠線の描画も設定

成績表の例 ( ヒストグラム ) (7/9) 同様にして、期末、評点も作成 ( 資料 107) 資料 107 比較対象となるグラフは 軸のスケールを統一する

成績表の例 ( ヒストグラム ) (8/9) 同様に中間～評点 (D1:F11) を選択しグラフ 化 – 「集合縦棒」、「 3-D 縦棒」それぞれ作ってみ る

成績表の例 ( ヒストグラム ) (9/9) あとは見易く、分かり易く調整を – タイトル、軸ラベル、 3-D の表示角、等々 – サンプル ( 資料 108) 資料 108 この手のグラフは分けた方が見易いかも？

成績表の例 ( 評価 ) (1/5) 秀優良可不可の 5 段階評定を行う – それぞれ 90,80,70,60,0 点以上 昇順でソートされた 基準得点と評価の対応表を用意する 検索値 点数に対応した評価を探してきて 表示したい

成績表の例 ( 評価 ) (2/5) VLOOKUP 関数を使う ( 詳細はヘルプ参照 ) VLOOKUP – 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) 資料 109

成績表の例 ( オートフィルター ) オートフィルター – ソート ( 並べ替え ) やフィルタリング ( データ抽 出 ) を簡単に実行出来る – 見出しを選択して「データ」 → 「フィル ター」

並べ替えの際の注意 並べ替えると元の並び順が失われる !!! 元に戻すには – 「元に戻す」を使う – あらかじめ元の並び順の情報を入れておく 出席番号等

ウインドウ枠の固定 見出し行をスクロールさせないようにで きる – 大きな表、表示する際に便利 – 見出し行に合わせ「表示」, 「ウインドウ枠の 固定」 スクロールしても 見出し行が残る

成績表の例 ( 最終結果 ) 資料 110 資料 110

得点分布の集計 ( 別の方法 ) FREQUENCY 関数を使う方法 FREQUENCY – 配列数式を使うので若干難解？ 配列数式 分析ツールのヒストグラム機能を使う方 法 分析ツール – 集計結果を生成する – 元の表を変えたら要作り直し Google 等で検索してみましょう – 「 Excel FREQUENCY 関数」 Excel FREQUENCY 関数 – 「 Excel 分析ツール ヒストグラム」 Excel 分析ツール ヒストグラム

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