コンピュータリテラシ 第 6 回 表 計 算 2 http://cobayasi.com/jm/6th/6th.pdf /reidai6.xlsx /reidai6a.xlsx /reidai6b.xlsx /table12_13.xlsx 1
今 日 の 学 習 要 点 (テキストP152~167) IF 関 数 の 使 い 方 IF 関 数 による 条 件 判 定 複 合 条 件 による 判 定 順 位 付 け(RANK.EQ) 検 索 関 数 (VLOOKUP) 条 件 に 一 致 したセルの 計 算 (COUNTIF,SUMIF) 2
IF 関 数 の 使 い 方 (テキストP153) 論 理 式 の 結 果 が 真 ( 条 件 を 満 足 した)の 場 合 と 偽 ( 条 件 を 満 足 しない)の 場 合 で 異 なった 処 理 をする < 書 式 > = IF ( 論 理 式, 真 の 場 合, 偽 の 場 合 ) < 論 理 式 > 真 偽 を 判 断 する 条 件 式 ( 比 較 演 算 子 や 論 理 演 算 子 を 使 った 式 ) 例 : = IF ( 100 =< A1, O, X ) A1の 値 が100 以 上 のときにはO(オー)を それ 以 外 の 場 合 はX(エックス) を 表 示 する 真 偽 O A1 X
論 理 式 に 使 う 比 較 演 算 子 と 比 較 対 象 項 目 比 較 演 算 子 記 号 条 件 意 味 A = B 一 致 AとBは 等 しい A <> B 不 一 致 AとBは 等 しくない A > B 超 過 AはBより 大 きい A < B 未 満 AはBより 小 さい A >= B 以 上 AはB 以 上 A <= B 以 下 AはB 以 下 比 較 対 象 項 目 セルと 数 値 または 計 算 式 : C3 > 85 セルと 文 字 : C3 = 合 計 C3 = A C3 = a セルとセル : C3 = A1
真 の 場 合 と 偽 の 場 合 に 書 けるもの 文 字 や 数 値 記 号 品 川 A 1? 計 算 式 A1+1 関 数 SUM( A1:A3) 無 表 示 ( 表 示 しない)
条 件 判 定 IF 関 数 による 条 件 判 定 (テキストP152) 例 題 1 合 計 が 目 標 数 以 上 だったら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 関 数 による 条 件 判 定 (テキストP153) 1 reidai6.xlsxをexcel2013で 開 く 2 セル:I3を 選 択 し 判 定 を 入 力 する 3 セル:I4を 選 択 する 4 [ホーム]タグ [ 編 集 ]グループ [オートSUM] 印 [その 他 の 関 数 ] [ 関 数 の 分 類 ] 印 [ 論 理 ] [ 関 数 名 ]IFを 選 択 5 [ 論 理 式 ]にF4>=C4,[ 真 の 場 合 ]にO,[ 偽 の 場 合 ]にXを 入 力 6 [OK]ボタンを 押 す 7 セル:I4の 式 をコピーして セル:I5~I8に 張 り 付 ける
複 合 条 件 による 判 定 (テキストP154) 例 題 2 合 計 が 目 標 数 以 上 だったら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 合 計 が 多 い 順 に 順 位 を 表 示 する 昨 年 目 標 数 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( A1, $A$1:$A$10, 0 ) A1の 値 が A1~A10の 範 囲 の 値 の 何 番 目 か( 降 順 )を 求 める
RANK.EQ 関 数 (テキストP156) 1 編 集 中 のreidai6.xlsxのセル:J3を 選 択 し 順 位 を 入 力 する 2 セル:J4を 選 択 する 3 数 式 バーの[ 関 数 の 挿 入 fx]ボタンを 押 す 4 [ 関 数 の 分 類 ] 印 [ 統 計 ] して[OK]ボタンを 押 す [ 関 数 名 ]RANK.EQを 選 択 5 [ 数 値 ]にF4を [ 参 照 ]に$F$4:$F$8を [ 順 序 ]に0(ゼロ)を 入 力 して [OK]ボタンを 押 す 6 セル:J4の 計 算 式 を セル:J5~J8にコピー& 貼 り 付 ける
保 存 編 集 した reidai6.xlsx を ファイル 名 : reidai6_3.xlsxで Zドライブに 保 存 する
検 索 関 数 VLOOKUP 関 数 (テキストP161) 例 題 4 ドリンクの 単 価 を 商 品 コードを 元 にし て 商 品 一 覧 表 から 検 索 して 入 力 する コード ドリンク 名 昨 年 目 標 数 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( A1, B1:B10, 2 ) A1の 値 を B1~B10の 範 囲 の 左 端 の 値 を 上 から 検 索 して 2 列 目 の 値 を 求 める
VLOOKUP 関 数 (テキストP161) 1 reidai6a.xlsxのセル:l3を 選 択 し 単 価 を 入 力 する 2 セル:L4を 選 択 する 3 数 式 バーの[ 関 数 の 挿 入 fx]ボタンを 押 す 4 [ 関 数 の 分 類 ] 印 [ 検 索 / 行 列 ] を 選 択 して[OK]ボタンを 押 す [ 関 数 名 ]VLOOKUP 5 [ 検 索 値 ]にA4を [ 範 囲 ]にA16:C20を [ 列 番 号 ]に3を 入 力 して [OK]ボタンを 押 す 6 セル:L4の 計 算 式 を セル:L5~L8にコピー& 貼 り 付 ける
保 存 編 集 した reidai6a.xlsx を ファイル 名 : reidai6_4.xlsxで Zドライブに 保 存 する
条 件 に 一 致 したセルの 計 算 COUNTIF 関 数 (テキストP164) 例 題 5 [ 動 向 表 ] 組 数 を [ 入 力 表 ] 備 考 欄 の 内 容 と 一 致 し た 数 から 求 める 入 力 表 商 品 一 覧 表 伝 票 番 号 コード ドリンク 名 単 価 個 数 金 額 備 考 コード ドリンク 名 単 価 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( $A$1:$A$10, B1 ) A1~A10の 範 囲 を 検 索 して B1の 値 ( 内 容 )と 一 致 した セル 数 を 求 める
COUNTIF 関 数 (テキストP164) 1 reidai6b.xlsxのセル:b17を 選 択 する 2 数 式 バーの[ 関 数 の 挿 入 fx]ボタンを 押 す 3 [ 関 数 の 分 類 ] 印 [ 統 計 ] て[OK]ボタンを 押 す [ 関 数 名 ]COUNTIFを 選 択 し 4 [ 範 囲 ]に G5を 入 力 して 確 定 する 前 にFunctionキー[F4]を 押 し $G$5( 絶 対 参 照 )にする 続 いて[:](コロン)を 入 力 し G13を 入 力 して 確 定 する 前 にFunctionキー[F4]を 押 し $G$13( 絶 対 参 照 )にする 5 [ 検 索 条 件 ]にA17を 入 力 し [OK]ボタンを 押 す 6 セル:A17に 入 力 した 計 算 式 を セル:B18にコピー& 貼 り 付 ける
SUMIF 関 数 (テキストP165) 例 題 6 [ 売 上 集 計 表 ] 売 上 個 数 を [ 入 力 表 ] 商 品 コードと 一 致 した 売 上 個 数 の 合 計 から 求 める 入 力 表 商 品 一 覧 表 伝 票 番 号 コード ドリンク 名 単 価 個 数 金 額 備 考 コード ドリンク 名 単 価 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( $A$1:$A$10, B1, $C$1:$C$10 ) A1~A10の 範 囲 を 検 索 して B1の 値 ( 内 容 )と 一 致 した C1~C10の 範 囲 の 値 の 合 計 を 求 める
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ドライブに 保 存 する