情報リテラシー演習

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

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

資料001: 比較演算子 比較演算子 • 比較結果として真偽値を得る • 真なら TRUE • 偽なら FALSE

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

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

資料002 文字コードの例(Shift JIS 1バイト目) • 半角1文字を1バイト(=8bit)のコードで表す • 全角1文字は2バイト(例: １=824Fh,あ=82A0h) 制御文字全角文字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文字づつ比較 • 文字単位の大小関係が決着した所で比較終了 • 比較する文字がない場合、長いほうが大 abcd < acb abcd > abc abc = abc abc < acb a = ab < cc > bd > a = ab = bc = cd > a = ab = bc = c a = ab < cc > 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(条件式)

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

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) • オートフィルするので範囲の行を絶対参照で固定

情報リテラシー演習

情報リテラシー演習

Presentation Transcript