情報リテラシー演習

情報リテラシー演習
第5週
Excelの使い方2
Excelの使い方
• 一応シラバスでは
• 先週
–
–
–
–
–
セル入力の基本操作
数式の記述法
よく利用される関数
範囲指定とグラフの描画
表とグラフの作成演習
• 今週
– IF関数を用いた判定と表
示
– その他の関数
– 判定表の作成演習
– ヒストグラムの作成演習
• 来週
–
–
–
–
並び替えとオートフィルタ
グラフ表示の編集
クロス集計
上記機能の演習
相対参照と絶対参照(1/4)
• 相対参照をオートフィルすると
– 行列ともに参照位置が自動的にずれる
相対参照と絶対参照(2/4)
• 「$」を付けると絶対参照
– 行のみに付けると、行が固定される
• 行は絶対参照、列は相対参照
相対参照と絶対参照(3/4)
• 列のみに付けると、列が固定される
– 行は相対参照、列は絶対参照
相対参照と絶対参照(4/4)
• 行列ともに付けると、完全に固定される
– 絶対参照
IF文
• IF文
– プログラムで条件分岐に使われる
– Excelでは関数扱い(IF関数)
論理式
真
• 数式の一部として用いる
• 文字列与えるときは「"」(ダブルクォーテーション)で囲む
• 書式(詳細は「IF文のヘルプ」参照)
– IF(論理式, 真の場合, 偽の場合)
• 使用例
– =IF(A1>0, "正の数", "負の数またはゼロ")
– =IF(A1>0, "正の数", IF(A1<0, "負の数", "ゼロ"))
偽
IF関数の例
• 正負の判定の例
• 数値と文字列の比較には注意
「’」(シングルクォーテーション)で始めたので
文字列扱いになる
論理演算関数
• AND, OR, NOT 関数
– 条件を論理演算する場合に使う
• AND(条件式1, 条件式2, …)
• OR(条件式1, 条件式2, …)
• NOT(条件式)
• 使用例
– =IF(AND(-10<=A1,A1<=10),"-10～10","～-10 or 10～")
– =IF(OR(A1<-10,10<A1),"～-10 or 10～","-10～10")
– =IF(NOT(A1<=0),"正の数", "負の数またはゼロ")
COUNT系関数(1/2)
• セルの個数を数える
– COUNT(値1, 値2, …)
• 数値セルや数値の個数
– COUNTA (値1, 値2, …)
• 空白でないセルや空白でない値の個数
– COUNTBLANK (値1, 値2, …)
• 空白セルや空白の値の個数
– COUNTIF (範囲, 条件)
• 条件に合致するセルの個数
– COUNTIFS (範囲1, 条件1, 範囲2, 条件2, …)
• 複数の条件に合致するセルの個数
COUNT系関数(2/2)
• COUNT系関数の使用例
SUM系関数(1/2)
• 値を合計する
– SUM(数値1, 数値2, …)
• セル範囲に含まれる数値をすべて合計
– SUMIF(範囲, 検索条件, 合計範囲)
• 指定された検索条件に一致するセルの値を合計
– SUMIFS(合計対象範囲, 条件範囲1, 条件1, 条件
範囲2, 条件2, ...)
• セル範囲内で、複数の検索条件を満たすセルの値を
合計
SUM系関数(2/2)
• SUM系関数の使用例
成績表の例(集計) (1/3)
• 中間・期末テストの成績(資料001)
– 評点(中間期末の合計点を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)
• とりあえず模範解答の一例(資料002)
オートフィルで
成績表の例(桁数調整) (1/2)
• 調整したい部分選択「ホーム」→「桁数調整」
– 右クリックして書式設定しても良いが
成績表の例(桁数調整) (2/2)
• 小数点以下1桁で揃えた(資料003)
成績表の例(合否判定) (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)
• 模範解答の一例(資料004)
オートフィルで
成績表の例(合否判定) (5/5)
• 合否判定結果
成績表の例(得点分布) (1/4)
• 「得点分布」ワークシート(資料005)
– 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)
• とりあえず、模範解答の一例(資料006)
成績表の例(ヒストグラム) (1/6)
• 中間(D1:D11),「グラフの作成」,「集合縦棒」
成績表の例(ヒストグラム) (2/6)
• タイトルダブルクリックして編集
得点範囲がおかしい
成績表の例(ヒストグラム) (3/6)
• 「グラフツール」,「デザイン」,「データの選択」
– 「横軸ラベル編集」で「得点分布!$A$1:$A$11」に
成績表の例(ヒストグラム) (4/6)
• 同様にして、期末、評点も作成(資料007)
成績表の例(ヒストグラム) (5/6)
• 同様に中間～評点(D1:F11)を選択しグラフ化
– 「集合縦棒」、「3-D縦棒」それぞれ作ってみる
成績表の例(ヒストグラム) (6/6)
• あとは見易く、分かり易く調整を
– タイトル、軸ラベル、3-Dの表示角、等々
– サンプル(資料008)
• この手のグラフは分けた方が見易いかも？
成績表の例(評価) (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)
• ここまでの結果(資料009)
成績表の例(オートフィルタ)
• オートフィルタ
– 並べ替えやデータの抽出が簡単にできる
– 見出しを選択して
– 「データ」→「フィルタ」
並べ替えの際の注意
• 並べ替えるともとの並び順に戻せない！！！
• 元に戻すには
– 「元に戻す」を使う
– あらかじめ元の並び順の情報を入れておく
• 出席番号等
ウインドウ枠の固定
• 見出し行をスクロールさせないようにできる
– 大きな表、表示する際に便利
– 見出し行に合わせ「表示」,「ウインドウ枠の固定」
見出し行が
スクロールせずに残る
成績表の例(最終結果)
• 資料010

Download Report