コンピュータリテラシ 第 6 回表計算 2 このスライド 例題 http://cobayasi.com/jm/6th/6th.pdf /reidai6.xlsx /reidai6a.xlsx 課題 12 /reidai6b.xlsx /table12_13.xlsx
今日の学習要点 ( テキスト P152-167) IF 関数の使い方 IF 関数による条件判定 複合条件による判定 順位付け (RANK.EQ) 検索関数 (VLOOKUP) 条件に一致したセルの計算 (COUNTIF,SUMIF) 2
IF 関数の使い方 ( テキスト P153) 論理式の結果が 真 ( 条件を満足した ) の場合と偽 ( 条件を満足しない ) の場合で 異なった処理をする 書式 = IF ( 論理式, 真の場合, 偽の場合 ) 論理式真偽を判断する条件式 ( 比較演算子や論理演算子を使った式 ) 例 : = IF ( F4 >= C4, O, X ) F4 の値が C4 の値と同じまたは大きいときには O( オー ) を それ以外の場合は X( エックス ) を表示する O 真 A1 偽 X
論理式に使う比較演算子と比較対象項目 ( テキスト P153) 比較演算子 記号条件意味 A = B 一致 A と B は等しい A <> B 不一致 A と B は等しくない A > B 超過 A は B より大きい A < B 未満 A は B より小さい A >= B 以上 A は B 以上 A <= B 以下 A は B 以下
比較対象項目 = IF ( 論理式, 真の場合, 偽の場合 ) セルと数値または計算式 C3 > 85 : C3 の値が 85 より大きい C3 < > 100 : C3 の値が 100 でない セルと文字 C3 = 合計 : C3 の文字列が 合計 である C3 > B : C3 の文字列が B よりも後の文字である セルとセル C3 = G3 : C3 の文字列が G3 の文字列と同じである C3 > = G3 : C3 の内容 ( 値, 文字列 ) が G3 の内容以上である
真の場合 と 偽の場合 に書けるもの = IF ( 論理式, 真の場合, 偽の場合 ) 文字, 数値, 記号文字 : 品川 A 数値 : 1 記号 :? 計算式セル番号 A1 の内容に数値 1 を加える :A1+1 関数セル番号 A1~A3 の内容を加算する :SUM( A1:A3 ) 無表示 ( 表示しない )
条件判定 IF 関数による条件判定 ( テキスト P152) 例題 1( 例題 40) 合計が目標数以上だったら O( オー ) を 未満だったら X( エックス ) を表示する 安藤奈津ドリンク販売実績商品数 5 ドリンク名 昨年 目標数 1 日目 2 日目 合計 前年比 割合 判定 ミルクティー 482 500 250 300 550 114.1% 33.2% O ココア 253 300 110 98 208 82.2% 12.6% X コーヒー 292 321 100 150 250 85.6% 15.1% X チョコレート 181 200 87 110 197 108.8% 11.9% X ストロベリー 321 350 200 250 450 140.2% 27.2% O 合計 1,529 1,671 747 908 1,655 108.2% 100.0% 平均 306 334 149 182 331 108.2% 最大 482 500 250 300 550 114.1% 最小 181 200 87 98 197 108.8%
IF 関数による条件判定 ( テキスト P152) 1 reidai6.xlsx を Excel2016 で開く 2 セル :I3 を選択し 判定 を入力する 3 セル :I4 を選択する 4 [ ホーム ] タグ [ 編集 ] グループ /[ オート SUM] 印 [ その他の関数 ] [ 関数の分類 ] 印 [ 論理 ] [ 関数名 ]IF を選択 5 [ 論理式 ] に F4>=C4,[ 真の場合 ] に O( オー ),[ 偽の場合 ] に X( エックス ) を入力 6 [OK] ボタンを押す 7 セル :I4 の式をコピーして セル :I5~I8 に張り付ける
複合条件による判定 ( テキスト P154) 例題 2( 例題 41) 合計が目標数以上だったら O( オー ) を 目標数より少ないが昨年以上なら ( サンカク ) を どちらにもあてはまらないなら X( エックス ) を 表示する ドリンク名昨年目標数 1 日目 2 日目合計前年比割合判定 ミルクティー 482 500 250 300 550 114.1% 33.2% ココア 253 300 110 98 208 82.2% 12.6% X コーヒー 292 321 100 150 250 85.6% 15.1% X チョコレート 181 200 87 110 197 108.8% 11.9% ストロベリー 321 350 200 250 450 140.2% 27.2% 合計 1,529 1,671 747 908 1,655 108.2% 100.0% 平均 306 334 149 182 331 108.2% 最大 482 500 250 300 550 114.1% 最小 181 200 87 98 197 108.8%
複合条件による判定 ( テキスト P154-155) 1 編集中の reidai6.xlsx のセル :I4 を選択する 2 数式バーの [ 関数の挿入 fx] ボタンを押す 3 [ 偽の場合 ] の X を削除する 4 数式バーの左横にある [ 名前ボックス ](IF と表示されているボックス ) をクリックする 5 新しく開いた [ 関数の引数 ] ダイアログボックスの [ 論理式 ] に F4>=B4 を入力する 6 [ 真の場合 ] に を [ 偽の場合 ] に X を入力し [OK] ボタンを押す 7 セル :I5~I8 の計算式を削除し セル :I4 の計算式をセル :I5~I8 にコピー & 貼り付ける
順位付け RANK.EQ 関数 ( テキスト P156) 例題 3( 例題 42) 合計が多い順に 順位を表示する 昨年 目標数 1 日目 2 日目 合計 前年比 割合 判定 順位 482 500 250 300 550 114.1% 33.2% 1 253 300 110 98 208 82.2% 12.6% X 4 292 321 100 150 250 85.6% 15.1% X 3 181 200 87 110 197 108.8% 11.9% 5 321 350 200 250 450 140.2% 27.2% 2 1,529 1,671 747 908 1,655 108.2% 100.0% 306 334 149 182 331 108.2% 482 500 250 300 550 114.1% 181 200 87 98 197 108.8%
RANK.EQ 関数の書式 ( テキスト P157) セル範囲内で 指定した値が何番目かを求める =RANK.EQ( 数値, 参照, 順位 ) 対象の値 順位の対象範囲 0: 降順 ( 大きい方から ) 1: 昇順 ( 小さい方から ) 例 : =RANK.EQ( F4, $F$4:$F$8, 0 ) F4 の値が F4~F8 の範囲の何番目か ( 降順 ) を求める
RANK.EQ 関数 ( テキスト P156) 1 編集中の reidai6.xlsx のセル :J3 を選択し 順位 を入力する 2 セル :J4 を選択する 3 数式バーの [ 関数の挿入 fx] ボタンを押す 4 [ 関数の分類 ] 印 [ 統計 ] [ 関数名 ]RANK.EQ を選択して [OK] ボタンを押す 5 [ 数値 ] に F4 を [ 参照 ] に $F$4:$F$8( 絶対参照 ) を [ 順序 ] に 0 ( ゼロ ) を入力して [OK] ボタンを押す 6 セル :J4 の計算式を セル :J5~J8 にコピー & 貼り付ける
保存 編集した reidai6.xlsx を ファイル名 : reidai6_3.xlsx で Z ドライブに保存する
検索関数 VLOOKUP 関数 ( テキスト P161) 例題 4( 例題 43) ドリンクの単価を 商品コードを元にして商品一覧表から検索して 入力する コード ドリンク名 昨年 目標数 1 日目 2 日目 合計 前年比 割合 判定 順位 単価 101 ミルクティー 482 500 250 300 550 114.1% 33.2% O 1 110 102 ココア 253 300 110 98 208 82.2% 12.6% X 4 100 103 コーヒー 292 321 100 150 250 85.6% 15.1% X 3 100 104 チョコレート 181 200 87 110 197 108.8% 11.9% 5 120 105 ストロベリー 321 350 200 250 450 140.2% 27.2% O 2 120 合計 1,529 1,671 747 908 1,655 108.2% 100.0% 平均 306 334 149 182 331 108.2% 最大 482 500 250 300 550 114.1% 最小 181 200 87 98 197 108.8% 商品一覧表 コード ドリンク名 単価 101 ミルクティー 110 102 ココア 100 103 コーヒー 100 104 チョコレート 120 105 ストロベリー 120
VLOOKUP 関数の書式 ( テキスト P162) 指定セル範囲の左端の値を検索して 対応する値を求める =VLOOKUP( 検索値, 範囲, 列番号 ) 検索対象値検索範囲表示列番号 例 : =VLOOKUP( A4, $A$16:$C$20, 3 ) A4 の値を A16~C20 の範囲の左端の値を上から検索して 3 列目の値を求める
VLOOKUP 関数 ( テキスト P161) 1 reidai6a.xlsx のセル :L3 を選択し 単価 を入力する 2 セル :L4 を選択する 3 数式バーの [ 関数の挿入 fx] ボタンを押す 4 [ 関数の分類 ] 印 [ 検索 / 行列 ] [ 関数名 ]VLOOK UP を選択して [OK] ボタンを押す 5 [ 検索値 ] に A4 を [ 範囲 ] に $A$16:$C$20( 絶対参照 ) を [ 列番号 ] に 3 を入力して [OK] ボタンを押す 6 セル :L4 の計算式を セル :L5~L8 にコピー & 貼り付ける
保存 編集した reidai6a.xlsx を ファイル名 : reidai6_4.xlsx で Z ドライブに保存する
条件に一致したセルの計算 COUNTIF 関数 ( テキストP163) 例題 5( 例題 44) [ 動向表 ] 組数を [ 入力表 ] 備考欄の内容と一致した数から求める 入力表 商品一覧表 伝票番号 コード ドリンク名 単価 個数 金額 備考 コード ドリンク名 単価 1 103 コーヒー 100 2 200 テイクアウト 101 ミルクティー 110 105 ストロベリー 120 1 120 102 ココア 100 101 ミルクティー 110 3 330 103 コーヒー 100 2 101 ミルクティー 110 5 550 テイクアウト 104 チョコレート 120 103 コーヒー 100 1 100 105 ストロベリー 120 3 102 ココア 100 2 200 テーブル 101 ミルクティー 110 1 110 4 105 ストロベリー 120 5 600 テーブル 5 104 チョコレート 120 3 360 テイクアウト 動向表 売上集計表 種別 組数 コード ドリンク名 売上個数売上金額 テイクアウト 3 101 ミルクティー テーブル 2 102 ココア 合計 5 103コーヒー 104 チョコレート 105 ストロベリー 合計 0 0
COUNTIF 関数の書式 ( テキスト P166) 指定したセル範囲から条件に一致したセルの数を求める =COUNTIF( 範囲, 検索条件 ) 検索範囲検索する値 ( 内容 ) 例 : =COUNTIF( $G$5:$G$13, A17 ) G5~G13 の範囲を検索して A17 の値 ( 内容 ) と一致したセル数を求める
COUNTIF 関数 ( テキスト P163-165) 1 reidai6b.xlsx のセル :B17 を選択する 2 数式バーの [ 関数の挿入 fx] ボタンを押す 3 [ 関数の分類 ] 印 [ 統計 ] [ 関数名 ]COUNTIF を選択して [OK] ボタンを押す 4 [ 範囲 ] に $G$5:$G$13( 絶対参照 ) を [ 検索条件 ] に A17 を入力し [OK] ボタンを押す 5 セル :A17 に入力した計算式を セル :B18 にコピー & 貼り付ける
SUMIF 関数 ( テキストP163) 例題 6( 例題 44) [ 売上集計表 ] 売上個数を [ 入力表 ] 商品コードと一致した売上個数の合計から求める 入力表 商品一覧表 伝票番号 コード ドリンク名 単価 個数 金額 備考 コード ドリンク名 単価 1 103 コーヒー 100 2 200 テイクアウト 101 ミルクティー 110 105 ストロベリー 120 1 120 102 ココア 100 101 ミルクティー 110 3 330 103 コーヒー 100 2 101 ミルクティー 110 5 550 テイクアウト 104 チョコレート 120 103 コーヒー 100 1 100 105 ストロベリー 120 3 102 ココア 100 2 200 テーブル 101 ミルクティー 110 1 110 4 105 ストロベリー 120 5 600 テーブル 5 104 チョコレート 120 3 360 テイクアウト 動向表 売上集計表 種別 組数 コード ドリンク名 売上個数 売上金額 テイクアウト 3 101 ミルクティー 9 テーブル 2 102 ココア 2 合計 5 103コーヒー 3 104 チョコレート 3 105 ストロベリー 6 合計 23 0
SUMIF 関数の書式 ( テキスト P166) 指定したセル範囲を検索して 条件に一致したセルの合計値を求める =SUMIF( 範囲, 検索条件, 合計範囲 ) 検索範囲検索する値 ( 内容 ) 合計する値の範囲 例 : =SUMIF( $B$5:$B$13, D17, $E$5:$E$13 ) B5~B13 の範囲を検索して D17 の値 ( 内容 ) と一致した E5~E13 の範囲の合計値を求める
SUMIF 関数 ( テキスト P165) 1 編集中の reidai6b.xlsx のセル :F17 を選択する 2 数式バーの [ 関数の挿入 fx] ボタンを押す 3 [ 関数の分類 ] 印 [ 数学 / 三角 ] [ 関数名 ]SUMIF を選択して [OK] ボタンを押す 4 [ 範囲 ] に B5:B13 を入力して 確定する前に Function キー [F4] を押し $B$5:$B$13( 絶対参照 ) にする 5 [ 検索条件 ] に D17 を入力する 6 [ 合計範囲 ] に E5:E13 を入力して 確定する前に Function キー [F4] を押し $E$5:$E$13 ( 絶対参照 ) にする 最後に [OK] ボタンを押す 7 セル :F17 に入力した計算式を セル :F18~F21 にコピー & 貼り付ける
保存 編集した reidai6b.xlsx を ファイル名 : reidai6_6.xlsx で Z ドライブに保存する
課題 11 例題 6( テキスト P163 例題 44) で作成した ドリンク販売一覧 (Z ドライブに保存したファイル名 :reidai6_6.xlsx) を Excel2016 で開き 売上集計表 の売上金額を SUMIF 関数を使って求め セル :G17-G21 を埋めなさい テキスト P165 を参照 また 出来上がった ドリンク販売一覧 をファイル名 : kadai11.xlsx で Z ドライブに保存しなさい 印刷範囲を設定し 印刷して提出しなさい 空きスペースに 学生証番号と氏名を入力することを忘れずに
課題 12 テキスト P159 の [ 実習 26] を実習しなさい 英語成績表 は ダウンロードした table12_13.xlsx(table12 のワークシート ) を使いなさい また 完成した表は ファイル名 :kadai12_13.xlsx で Z ドライブに保存しなさい 印刷範囲を設定し 印刷して提出しなさい 空きスペースに 学生証番号と氏名を入力することを忘れずに
課題 13 テキスト P167 の [ 実習 31],[ 実習 32] を実習しなさい 営業担当商品割り当て表 は ダウンロードした table12_13.xlsx(table13 のワークシート ) を使いなさい また 完成した表は ファイル名 :kadai12_13.xlsx で Z ドライブに保存しなさい 印刷範囲を設定し 印刷して提出しなさい 空きスペースに 学生証番号と氏名を入力することを忘れずに