関数の構造

表 計 算 ソフトの 応 用 操 作 Microsoft Excel 2013 明 治 大 学 教 育 の 情 報 化 推 進 本 部 IZM20140417

はじめに Microsoft Office Excel 2013( 以 下 本 テキストでは Excel と 記 します)は スプレッドシートソフトの 代 表 的 なソフトです この 講 習 会 では 基 本 編 ですでに Excel の 基 本 的 な 動 作 を 習 得 していることを 前 提 に より 応 用 的 な 使 い 方 について 学 んでいきます 目 次 はじめに... 0 1 入 力 用 シートを 作 ろう... 2 1.1 配 布 資 料 の 確 認... 2 1.2 入 力 してみよう... 3 1.3 セルの 表 示 形 式... 5 1.4 入 力 規 則 の 設 定 (ドロップダウンリスト)... 7 1.5 VLOOKUP 関 数... 8 2 集 計 表 を 作 ろう... 12 2.1 SUMIF 関 数... 12 2.2 IF 関 数... 15 2.3 IF 関 数 (ネスト)... 17 2.4 SUMIFS 関 数... 19 2.5 複 合 参 照... 21 2.6 条 件 付 き 書 式... 23 3 グラフを 作 ろう... 26 3.1 積 み 上 げ 棒 グラフ... 26 3.2 レーダーチャート... 27 3.3 ドーナツグラフ... 28 4 印 刷 してみよう... 31 4.1 レイアウトの 設 定... 31 4.2 ヘッダー フッター... 34 4.3 印 刷 タイトルの 設 定... 35 4.4 印 刷... 36 5 付 録... 37 5.1 散 布 図... 37 5.2 シートの 保 護 非 表 示... 39 1

1 入 力 用 シートを 作 ろう 1.1 配 布 資 料 の 確 認 この 講 習 会 では 使 用 するデータをあらかじめ 用 意 しています 担 当 者 の 指 示 に 従 い 保 存 利 用 してく ださい それでは 配 布 されたファイル Excel 応 用 操 作 講 習 会 資 料.xlsx を 開 いてください このファイルは 表 データ 完 成 例 の 3 つのシートで 構 成 されています この 講 習 会 では データ シートにあるデータをもとに 表 シートの 商 品 管 理 表 や 店 舗 別 売 上 表 それらのグラフを 完 成 させていきます 2

1.2 入 力 してみよう では はじめに データ シートを 開 いてください ここには 4 つの 店 舗 で 販 売 している T シャツなどの 衣 料 品 の 商 品 コード 品 名 単 価 数 量 合 計 な どのデータが 入 力 されています このうち 左 側 の 売 上 データ(4 月 ) には 入 力 作 業 がしやすいように 設 定 がしてあります 試 しに 1 行 データが 抜 けているところがあるので そこに 次 のデータを 入 力 してみましょう Memo 4/4 生 田 店 1003 2 点 1 2 3 4 日 付 の 欄 に 4/4 と 入 力 2014/4/4 と 西 暦 付 きで 表 示 されます 店 舗 の 欄 は (ドロップダウンリスト)になっているので 生 田 店 を 選 びます 商 品 コード 欄 には 1003 と 入 力 右 に 商 品 名 と 単 価 が 自 動 的 に 表 示 されます 数 量 の 欄 に 2 と 入 力 合 計 金 額 が 自 動 的 に 計 算 されます 3

このように あらかじめシートに 設 定 をしておくことで 入 力 作 業 を 楽 に 進 めることができます ここからは 右 側 の 売 上 データ(5 月 ) に このような 便 利 な 設 定 を 加 えていきましょう 4

1.3 セルの 表 示 形 式 はじめに 日 付 のセルの 設 定 をします 売 上 データ(4 月 )の 日 付 のセルに 4/4 と 入 力 すると 2014/4/4 と 西 暦 年 付 きで 表 示 されまし た しかし 何 も 設 定 していない 売 上 データ(5 月 ) 表 に 5/1 と 打 ち 込 むと 右 のように 5 月 1 日 とだけ 表 示 されます 売 上 データ(5 月 )の 日 付 も 20xx/x/x と 表 示 されるように 表 示 形 式 を 変 えてみましょう セルの 表 示 形 式 は セルの 書 式 設 定 から 変 更 することができます 1 日 付 のセル( I3 ~ I100 )をすべて 選 択 し 右 クリック セルの 書 式 設 定 2 表 示 形 式 タブの 分 類 の 中 から 日 付 を 選 択 すると 右 にさまざまな 日 付 の 表 示 形 式 が 示 されます 3 ここでは 一 番 上 の 2012/3/14 を 選 択 し OK これで 日 付 が 20xx/x/x と 表 示 されるようになりました また 売 上 データ(4 月 )の 単 価 合 計 のように 金 額 を 表 示 するセルは 桁 区 切 り(カンマ) が あると 見 やすくなりますが これも セルの 表 示 形 式 で 設 定 できます 5

売 上 データ(5 月 )に 桁 区 切 りを 設 定 してみましょう ここでは M 列 と O 列 全 体 に 桁 区 切 りをつけます Ctrl キーで 2 つの 列 を 同 時 選 択 し 右 クリック セルの 書 式 設 定 を 選 び 分 類 数 値 の 中 から 桁 区 切 り(,)を 使 用 す る にチェックを 入 れて OK これで 数 値 に 桁 区 切 り(カンマ)が 入 るよう 設 定 できました ( 後 で 入 力 したときにわかります ) 桁 区 切 り は ホーム タブの % などの 隣 にある, (カンマ)ボタンでも 設 定 できます セルの 表 示 形 式 には 他 にもさまざまな 種 類 が 用 意 されており また ユーザー 定 義 を 使 えば 自 分 で 新 たな 表 示 方 法 を 決 めることもできます セルの 書 式 設 定 からは この 表 示 形 式 だけでなく セルのフォントや 配 置 色 や 罫 線 など 他 にもい ろいろな 設 定 を 変 更 することができます 使 っていくうちに 少 しずつ 覚 えていきましょう 6

1.4 入 力 規 則 の 設 定 (ドロップダウンリスト) 次 に 店 舗 のセルの 設 定 をします 売 上 データ(4 月 )では 店 舗 のセルでは 駿 河 台 店 和 泉 店 生 田 店 中 野 店 の 4 つの 店 舗 をドロップ ダウンリストで 選 択 できるようになっていました このような 入 力 規 則 を 設 定 すると 直 接 入 力 するより 作 業 を 楽 にできたり 入 力 ミスを 防 いだりすること ができます では 売 上 データ(5 月 )にもこのドロップダ ウンリストを 作 ってみましょう 1 店 舗 を 入 力 するセルすべて( J3 ~ J100 )を 選 択 し データ タブから データの 入 力 規 則 データの 入 力 規 則 をクリック 2 設 定 タブの 入 力 値 の 種 類 で リスト を 選 択 します 3 元 の 値 に リストの 選 択 肢 にしたい 項 目 を そ れぞれカンマ( 半 角 )で 区 切 って 入 力 します ここ では 駿 河 台 店, 生 田 店, 和 泉 店, 中 野 店 と 入 力 します もし 既 にシート 上 にこれらを 記 入 した 表 があれば そ の 範 囲 を 選 択 して 使 うこともできます 入 力 できたら OK 入 力 規 則 が 設 定 できました 入 力 する 時 は マウスで をクリックするか キーボードで Alt + キーを 押 すことで ドロッ プダウンリストを 出 すことができます 7

1.5 VLOOKUP 関 数 次 に 商 品 コードを 入 力 すると 自 動 的 に 商 品 名 や 単 価 が 表 示 されるようにしてみましょう 売 上 データ(4 月 )には 商 品 名 と 単 価 のセルに VLOOKUP という 関 数 が 入 っており となり の 表 シートにある 商 品 コード 表 を 参 照 しながら 商 品 コードにあったデータを 自 動 的 に 表 示 する ようになっています この VLOOKUP 関 数 は 簡 単 な 番 号 などを 打 ち 込 むだけで 別 の 場 所 ( 同 じシートあるいは 他 のシート) にある 対 応 表 にもとづいて 具 体 的 なデータを 参 照 してれくれるので このような 売 り 上 げの 集 計 などによく 使 われます では 5 月 の 表 に VLOOKUP 関 数 を 入 力 してみましょう VLOOKUP( 検 索 値, 範 囲, 列 番 号, 検 索 方 法 ) 1 まず 商 品 名 のセル L3 を 選 択 し 関 数 を 挿 入 する fx ボタンを 押 し VLOOKUP 関 数 を 検 索 選 択 し ます この 講 習 会 では 説 明 の 都 合 上 fx ボタン( 関 数 ウィザード)を 使 って 関 数 を 挿 入 していきます 慣 れている 方 は 直 接 キーボードで 関 数 を 入 力 しても 構 いません 2 検 索 値 範 囲 列 番 号 検 索 方 法 の 4 つの 引 数 をきかれます まず 検 索 値 には データの 手 掛 かりとなるコードが 入 力 されるセルを 選 択 します ここでは K3 セ 8

ルをクリックして 選 択 します 3 範 囲 には データの 対 応 関 係 が 書 いてある 表 全 体 ( 見 出 しは 含 まない)を 選 択 します ここでは とな りの 表 シートにある 商 品 コード 表 全 体 A3 ~ C7 を 選 択 してください 後 でコピーする 時 にずれないように 選 択 したら F4 キーを 押 して 絶 対 参 照 にしておきます 別 のシートにあるデータを 参 照 すると 上 の 表! のように シートの 名 前 +! が 範 囲 の 前 に 付 きます 4 列 番 号 には 表 の 中 の 左 から 何 列 目 のデータが 欲 しいかを 入 力 します ここでは 商 品 名 はコード 表 の 2 列 目 にあるので 2 と 入 力 します 5 最 後 の 検 索 方 法 には TRUE と FALSE の 2 種 類 があり 近 似 値 を 含 める 含 めないという 違 い があるのですが ここでは 含 めない FALSE と 入 力 してください 近 似 値 を 含 める TRUE にすると 例 えば 表 にない 1006 と 入 力 しても 最 も 近 い 値 である 1005 (= 革 ジャン) と 認 識 してしまいます 6 引 数 がすべて 入 力 できたら OK をクリック コードがまだ 入 力 されていないので #N/A というエラーが 出 ていますが そのままで 問 題 ありません こ のセルを 一 番 下 までコピーしてください 同 様 に 単 価 のセル( M3 セル)にも fx ボタンで VLOOKUP 関 数 を 入 力 してみましょう 9

欲 しいデータ( 単 価 )はコード 表 の 3 列 目 なので 列 番 号 は 3 にします あとは 先 程 と 同 じです OK で 関 数 を 入 れたら こちらも 下 までコピーします これで VLOOKUP 関 数 が 入 れられました 試 しに K3 セルに 1001 と 売 ってみましょう 右 の 商 品 名 の 欄 に T シャツ 単 価 の 欄 に 1,980 と 表 示 されれば 正 しく 関 数 が 入 力 されています 関 数 が 入 力 できたら 最 後 に 合 計 の 列 に 単 価 数 量 を 出 す 式 を 入 力 しておきましょう O3 セルに =M3*N3 と 入 力 し 下 までコピーします これで 入 力 用 シートの 設 定 がすべて 終 わりました それでは 試 しに 次 のサンプルデータを 入 力 してみましょう 商 品 名 や 単 価 合 計 額 が 正 しく 表 示 される でしょうか 10

Memo 5/1 生 田 店 1001 2 点 5/1 生 田 店 1003 2 点 5/1 和 泉 店 1002 1 点 5/2 駿 河 台 店 1001 3 点 5/2 駿 河 台 店 1004 1 点 以 上 入 力 作 業 に 役 に 立 つ 機 能 を 扱 いました このように シートにあらかじめ 関 数 や 規 則 などを 設 定 しておくことで データの 入 力 を 簡 単 にすること ができます ぜひ 活 用 できるようにしましょう 11

2 集 計 表 を 作 ろう さて ここからは 上 で 見 た 売 上 データをもとに 集 計 表 を 作 ります 表 シートにある 各 種 の 表 を 関 数 やその 他 の 機 能 を 使 って 完 成 させていきましょう 完 成 例 2.1 SUMIF 関 数 はじめに 商 品 管 理 表 を 作 成 します ここには 各 商 品 の 在 庫 数 (50 や 30)がすでに 入 力 さ れています その 右 に まず 商 品 ごとの 売 上 個 数 を 出 し さらにそこから 現 在 の 残 り 在 庫 数 を 求 めていきます まず 商 品 ごとの 売 上 個 数 を 求 めましょう 売 り 上 げた 個 数 は データ シートの 売 上 データ(4 月 ) の 数 量 を 見 ます 12

全 商 品 の 個 数 を 出 すならこれらを 単 純 に 合 計 (SUM 関 数 )すればよいのですが T シャツだけ スエ ットだけ など 一 定 の 条 件 のものだけを 合 計 したい 場 合 は SUMIF 関 数 を 使 います SUMIF( 範 囲, 検 索 条 件, 合 計 範 囲 ) 1 表 シートに 戻 ります はじめに T シャツの 売 上 個 数 から 出 していきましょう C11 セルを 選 択 し fx ボタンを 押 し SUMIF を 検 索 選 択 します 2 範 囲 検 索 条 件 合 計 範 囲 の 3 つの 引 数 をきかれます 範 囲 には 足 し 算 をする 範 囲 ではなく 条 件 を 探 す 範 囲 を 入 れます ここでは T シャツ という 条 件 を 探 す 範 囲 つまり 商 品 名 が 書 いてある データ シートの D3 ~ D100 セ ルをマウスで 選 択 します 後 でコピーする 時 にずれないよう F4 キーで 絶 対 参 照 にしておきましょう 検 索 条 件 は T シャツ です 直 接 T シャツ と 入 力 してもかまいませんが 表 の 見 出 しにすでに 商 品 名 が 書 いてあるので それを 使 いましょう A11 セルをクリックします 13

3 合 計 範 囲 には 合 計 したい 範 囲 を 選 択 します データ シートの 数 量 の 列 ( F3 ~ F100 セル)をマウスで 選 択 します ここもずれてはいけないので F4 キーで 絶 対 参 照 にします 4 OK をクリックすると T シャツのみの 合 計 が 出 ます SUMIF 関 数 がうまく 入 力 できました 関 数 が 入 力 できたら 下 までコピーします 商 品 ごとの 売 上 個 数 を 出 すことができました 後 は 右 の 残 り の 列 に 在 庫 - 売 上 個 数 を 計 算 式 を 入 れれば 残 り 在 庫 数 が 出 ます 14

2.2 IF 関 数 次 に 一 番 右 の 備 考 欄 で 残 り 在 庫 数 をたよりに 注 文 が 必 要 か 否 か を 自 動 的 に 表 示 させましょ う それには IF 関 数 を 使 います IF 関 数 は 指 定 された 条 件 をもとに その 条 件 に 合 っている 場 合 ( TRUE(= 真 ) といいます)と 合 っていない 場 合 ( FALSE(= 偽 ) といいます)の 2 通 りの 値 を 表 示 することができます IF( 論 理 式, 真 の 場 合, 偽 の 場 合 ) ここでは 残 り 在 庫 が 15 未 満 になったら 備 考 欄 に 注 文 と 表 示 させるようにしてみましょう 1 T シャツ の 備 考 ( E11 セル)を 選 択 し fx ボタンで 関 数 を 入 れます 2 IF を 検 索 選 択 します 3 論 理 式 真 の 場 合 偽 の 場 合 の 3 つの 引 数 をきいてきます 論 理 式 には 判 断 の 条 件 となる 式 を 入 れます この 場 合 残 りが 15 未 満 か どうかで 判 断 をするので D11<15 と 記 入 します セル 名 の 入 力 は マウスで 直 接 クリックしてもできます 4 真 の 場 合 (= 残 りが 15 未 満 の 場 合 )には 注 文 と 入 力 します 関 数 の 引 数 で 日 本 語 などの 文 字 列 を 使 う 場 合 は 必 ず で 囲 みますが このように fx ボタンで 関 数 を 入 れている 場 合 は 付 け 忘 れても Excel が 自 動 的 に 付 けてくれます 5 偽 の 場 合 (= 残 りが 15 以 上 の 場 合 )には 何 も 表 示 させたくないので (= 空 欄 )と 入 力 しま す Excel で 空 欄 を 表 示 させたいときは とだけ 入 力 します 15

6 引 数 が 入 力 できたら OK IF 関 数 が 挿 入 され T シャツの 備 考 欄 には 在 庫 が 15 を 下 回 っているので 注 文 と 表 示 されました す さらに セルを 下 までコピーします 残 りが 15 未 満 のところに 全 て 注 文 と 表 示 されていれば 成 功 で Excel での 比 較 演 算 子 ( 等 号 不 等 号 など)の 表 記 法 をまとめておきます 確 認 しておきましょう 比 較 演 算 子 内 容 例 = ( 等 号 ) 左 辺 と 右 辺 が 等 しい A1=B1 > (~より 大 きい) 左 辺 が 右 辺 よりも 大 きい A1>B1 < (~より 小 さい) 左 辺 が 右 辺 よりも 小 さい A1<B1 > = (~ 以 上 ) 左 辺 が 右 辺 以 上 である A1>=B1 < = (~ 以 下 ) 左 辺 が 右 辺 以 下 である A1<=B1 < > ( 不 等 号 ) 左 辺 と 右 辺 が 等 しくない A1<>B1 16

2.3 IF 関 数 (ネスト) 以 上 で 注 文 か ( 空 欄 ) の 2 通 りの 場 合 分 けができましたが 同 じ IF 関 数 を 使 って 今 度 は 3 通 り の 場 合 分 けをしてみましょう 更 に 細 かく 分 け 残 り 10 未 満 なら 注 文 10 以 上 ~15 未 満 であれば 準 備 15 以 上 は ( 空 欄 ) と 表 示 させます 1 E11 ~ E15 セルの 内 容 をいったん delete キーで 消 し 再 び E11 セルを 選 択 し fx ボタンで 関 数 を 入 れ 直 します 2 IF を 選 択 し OK 3 論 理 式 に まず 注 文 する 基 準 となる 残 りが 10 未 満 という 数 式 を 入 れます D11<10 と 記 入 4 真 の 場 合 ( 残 りが 10 未 満 の 場 合 )には 注 文 と 記 入 します 5 ここからがポイントです 偽 の 場 合 ( 残 りが 10 以 上 の 場 合 )には さらに 2 通 り つまり 15 未 満 ( 準 備 )かそれ 以 上 ( 空 欄 )かに 分 ける 必 要 があります つまり ここでもう 一 度 IF を 使 うのです ここに IF を 入 れるには fx ボタンは 使 えず 数 式 バーや fx ボタンの 一 番 左 側 にあるウィンドウの をクリックすると 最 近 使 った 関 数 が 表 示 されるので そこにある IF をクリックして 挿 入 します 6 すると 2 回 目 の IF の 入 力 画 面 が 始 まります あとは 残 りの 2 通 りの 場 合 分 けをします 論 理 式 には 残 りが 15 未 満 という 数 式 すなわち D11<15 と 入 力 します 17

7 8 真 の 場 合 ( 残 り 15 未 満 の 場 合 )には 準 備 と 入 力 します 偽 の 場 合 ( 残 り 15 以 上 の 場 合 )には ( 空 欄 )と 入 力 し OK 関 数 が 入 力 できたら そのセルを 下 までコピーします これで 残 り 10 未 満 であれば 注 文 15 未 満 であれば 準 備 15 以 上 であれば ( 空 欄 ) の 3 通 り に 場 合 分 けすることができました 以 上 のように IF 関 数 を 入 れ 子 状 に 使 うことによって 3 通 り 以 上 の 場 合 分 けも 可 能 になります この ような 関 数 の 入 れ 子 のことを ネスト といいます 18

2.4 SUMIFS 関 数 次 に 店 舗 別 売 上 表 を 入 力 していきましょう この 表 では 店 舗 商 品 ごとの 売 上 とその 合 計 をそれぞれ 求 めます 先 ほどの 商 品 管 理 表 では SUMIF 関 数 を 使 って T シャツ など 1 つの 条 件 で 合 計 を 求 めましたが こ こでは 駿 河 台 店 T シャツ など 2 つの 条 件 で 合 計 を 求 める 必 要 があります このように 2 つ 以 上 の 条 件 で 合 計 させるには SUMIFS 関 数 を 使 います SUMIFS( 合 計 対 象 範 囲, 条 件 範 囲 1, 条 件 1, 条 件 範 囲 2, 条 件 2, ) 1 まず 駿 河 台 店 の T シャツ の 売 上 合 計 を 出 します H11 セルを 選 択 し fx ボタンをクリック 2 SUMIFS を 検 索 選 択 します 3 合 計 対 象 範 囲 には 合 計 させたい 範 囲 を 入 力 します データ シートの 合 計 が 書 かれた G3 ~ G100 セルを 選 択 します コピーする 時 にずれないように F4 キーで 絶 対 参 照 にしておきます 4 あとは 条 件 とそれを 探 す 範 囲 をペアで 設 定 していきます 条 件 1 には 店 舗 名 駿 河 台 店 を 設 定 しましょう 条 件 範 囲 1 に 店 舗 名 が 記 されている 範 囲 (つまり データ シートの B3 ~ B100 絶 対 参 照 に します)を 条 件 1 に 条 件 である 駿 河 台 店 を 入 力 します 店 舗 名 は 直 接 記 入 せずに 表 の 見 出 し ( H10)セル をクリックして 入 力 します 19

ここで 重 要 なポイントがあります 駿 河 台 店 が 記 入 された H10 セルをクリックして 選 択 したら F4 キーを 2 回 押 して H$10 にしておいてください 理 由 は 後 ほど 説 明 します 5 条 件 2 には 商 品 名 を 設 定 します 上 と 同 様 に 探 す 範 囲 ( データ シート D3 ~ D100 絶 対 参 照 にします)と 条 件 となる 商 品 名 ( T シャツ やはり 直 接 入 力 せず 表 の 見 出 しの G11 セルをクリック)を 入 力 します ここでも G11 セルをクリックしたら F4 キーを 3 回 押 して $G11 にしておいてください できたら OK をクリック 駿 河 台 店 の T シャツ の 売 上 合 計 が 求 められました 20

2.5 複 合 参 照 さて 入 力 した SUMIFS 関 数 をコピーする 前 に 複 合 参 照 について 説 明 します Excel には セルをコピーしたときに 参 照 先 がそれに 合 わせてずれる 相 対 参 照 と ずれない 絶 対 参 照 の 2 つの 参 照 の 仕 方 があります 複 合 参 照 とは その 2 つを 組 み 合 わせた 参 照 方 法 で わかりやすく 言 えば 縦 にだけずれる ある いは 横 にだけずれる 参 照 の 仕 方 です たとえば A1 セルを 選 択 して F4 キーを 連 続 して 押 すと 下 のように $ の 位 置 が 変 わっていきます 相 対 参 照 絶 対 参 照 複 合 参 照 A1 固 定 なし $A$1 行 列 固 定 A$1 行 だけ 固 定 $A1 列 だけ 固 定 A1 に 戻 る この 行 だけ 固 定 (= 縦 にだけずれる) 列 だけ 固 定 (= 横 にだけずれる) 参 照 の 仕 方 が 複 合 参 照 です この 縦 にだけずれる 横 にだけずれる 複 合 参 照 はどんな 時 に 使 うかというと 縦 と 横 に 連 続 してデ ータをコピーする 時 です 先 ほどの SUMIFS 関 数 の 場 合 も 複 合 参 照 なしでは 表 全 体 にコピーすることができません 下 の 図 をご 覧 ください これから 矢 印 の 方 向 に 縦 横 にコピーして 表 全 体 を 関 数 で 埋 めようとしています このとき 店 舗 の 参 照 先 は 縦 にずれてはいけないが 横 にはずれるように 設 定 しないとすべての 見 出 しを 参 照 できません 逆 に 商 品 名 の 参 照 先 は 横 にずれてはいけないが 縦 にはずれるように 設 定 しないとすべ ての 見 出 しを 参 照 できません ( 下 図 の 点 線 ) 21

そのため 先 ほど 駿 河 台 店 のセルは H$10( 行 だけ 固 定 ) という 横 にだけずれる 複 合 参 照 に T シャツ のセルは $G11( 列 だけ 固 定 ) という 縦 にだけずれる 複 合 参 照 にしたのです では 作 業 の 続 きに 戻 り H11 セルの SUMIFS 関 数 を 店 舗 別 売 上 表 全 体 にコピーしましょう まずは 縦 方 向 にコピーします 革 ジャン の 行 まで うまくコピーができました 参 照 する 店 舗 名 は 縦 にずれず 商 品 名 は 縦 にずれたからです そのまま さらに 今 度 は 横 方 向 へコピーします こちらも 中 野 店 の 列 まで 問 題 なくコピーできました 参 照 する 店 舗 名 は 横 にずれ 商 品 名 は 横 にずれなかったからです 複 合 参 照 がきちんと 設 定 できたため 縦 横 へのコピーが 簡 単 にできました 最 後 に オート SUM ボタン+コピーで 店 舗 と 商 品 ごとの 合 計 も 出 しておきましょう さらに 先 の 1.3 で 扱 ったように 金 額 の 入 ったセルには 桁 区 切 り(カンマ)を 入 れます 以 上 のように 複 合 参 照 の 概 念 を 理 解 しておくと 関 数 や 数 式 を 行 ( 縦 )と 列 ( 横 )にまたがって 一 気 に コピーでき とても 効 率 が 上 がります ぜひ 活 用 できるようにしましょう 22

2.6 条 件 付 き 書 式 最 後 に 条 件 付 き 書 式 を 使 って 出 来 上 がった 表 にアクセントをつけましょう 条 件 付 き 書 式 とは 任 意 の 条 件 に 従 って 自 動 的 にセルの 書 式 を 変 える 機 能 で 欲 しい 情 報 だけを 目 立 たせることができます たとえば 順 位 にしたがって 自 動 的 にセルに 色 をつけることができます 店 舗 別 売 上 表 の 売 上 が 第 1 位 と 最 下 位 の 店 舗 のセルに 自 動 的 に 色 をつけてみましょう 1 2 店 舗 ごとの 合 計 ( H16 ~ K16 セル)をすべて 選 択 します ホーム タブの 条 件 付 き 書 式 上 位 / 下 位 ルール 上 位 10 項 目 をクリック 3 初 期 設 定 では 上 位 10 位 までになっているので 10 を 1 に 変 えます 書 式 は 濃 い 赤 ~ のままで OK 第 1 位 のセルに 赤 く 色 がつきました 和 泉 店 の 売 上 が 最 も 多 いことが 一 目 でわかるようになりました 今 度 は 同 じ 範 囲 から 逆 に 最 下 位 の 店 舗 の 売 上 に 色 をつけてみましょう 23

条 件 付 き 書 式 上 位 / 下 位 ルール 下 位 10 位 の 10 を 1 にし 書 式 は 濃 い 緑 ~ にしてみます 最 下 位 の 中 野 店 の 合 計 売 上 に 緑 色 の 色 がつきました 練 習 として 同 じ 表 の 商 品 ごとの 売 上 の 第 1 位 と 最 下 位 に それぞれ 赤 と 緑 に 色 をつけてみましょう また 特 定 の 値 を 基 準 にして 色 をつけることもできます 商 品 管 理 表 で 注 文 が 必 要 と 表 示 した 24

残 り 在 庫 が 10 未 満 のセルに 自 動 的 に 色 をつけてみましょう 1 2 T シャツ~ 革 ジャンの 残 り 在 庫 の 欄 D11 ~ D15 をすべて 選 択 します 条 件 付 き 書 式 セルの 強 調 表 示 ルール 指 定 の 値 より 小 さい をクリック 3 次 の 値 より 小 さいセルを 書 式 設 定 に 10 を 入 れます 書 式 は 濃 い 赤 のままで OK 残 り が 10 未 満 のセルに 赤 く 色 がつきました このように 条 件 付 き 書 式 は 順 位 や 値 など 特 定 の 条 件 でセルを 目 立 たせたい 時 にとても 便 利 な 機 能 です 以 上 で 表 はひとまず 完 成 です 次 の 章 では これらの 表 をもとにグラフを 作 っていきましょう 25

3 グラフを 作 ろう ここからは 完 成 した 表 を 使 ってグラフを 作 っていきます この 講 習 では 棒 グラフや 折 れ 線 グラフ 円 グラフなどの 基 本 的 なグラフ 以 外 の 少 し 複 雑 なグラフについて 扱 います 3.1 積 み 上 げ 棒 グラフ はじめに 店 舗 別 売 上 表 を 使 って 積 み 上 げ 棒 グラフ を 作 ってみます 積 み 上 げ 棒 グラフは 通 常 の 棒 グラフのように 全 体 だけではなく それらを 構 成 する 要 素 の 割 合 を 表 示 比 較 することができます ここでは 店 舗 ごとの 総 売 り 上 げとともに 各 商 品 による 内 訳 を 表 してみましょう 1 店 舗 別 売 上 表 の 項 目 名 を 含 む 各 要 素 の 値 ( 合 計 部 分 はのぞく)が 記 入 されている 部 分 ( G10 ~ K15 セル)を 選 択 します 2 挿 入 タブ グラフの 種 類 の 中 から 積 み 上 げ 縦 棒 を 選 択 します( 積 み 上 げ 横 棒 も 表 す 内 容 は 変 わりません) 3 グラフが 出 来 ましたが 商 品 ごと の 店 舗 別 内 訳 で 描 きたいものとは 要 素 が 逆 になっています こ のような 時 は グラフツール デザイン タブ 行 / 列 の 切 り 替 え ボタンをクリックします 要 素 が 逆 転 し 店 舗 ごと の 商 品 別 内 訳 をあらわす 積 み 上 げ 縦 棒 グラフが 描 けました 店 舗 ごとの 内 訳 ( 積 み 上 げ 縦 棒 グラフ) など わかりやすいタイトルをつけておきましょう 26

3.2 レーダーチャート 複 数 の 要 素 を 比 較 するには 星 のような 形 をした レーダーチャート も 有 効 です 積 み 上 げ 棒 グラフの グラフの 種 類 を 変 更 して レーダーチャートに 作 り 替 えてみましょう 1 先 ほど 作 った 積 み 上 げ 棒 グラフを 選 択 し グラフツール デザイン タブの グラフの 種 類 の 変 更 ボタン をクリック 2 様 々なグラフに 変 更 することができますが ここは レーダー の 中 の マーカー 付 レーダー を 選 びます ( マーカー 付 は 星 形 の 頂 点 にあたる 部 分 が 強 調 されます ) データの 2 通 りの 取 り 方 がプレビューされるので ここではよりふさわしい 右 の 方 を 選 び OK レーダーチャートが 作 成 できました 店 舗 ごとの 内 訳 (レーダーチャート) など わかりやすいタイ トルをつけましょう レーダーチャートは 星 形 の 面 積 を 持 った 形 であらわされるので データ 同 士 の 傾 向 の 違 いが 視 覚 的 にとら えやすいグラフです 27

3.3 ドーナツグラフ 次 に 店 舗 別 売 上 表 を 使 って ドーナツグラフ を 作 ってみましょう ドーナツグラフは 多 重 になった 円 グラフで 大 分 類 と 小 分 類 など 複 数 のデータ 項 目 を 比 較 することができます 表 には 分 類 項 目 がないので グラフを 作 る 前 に 5 つの 商 品 を アウター と インナー の 2 つに 分 類 し その 合 計 を 出 しておきます 1 T シャツとスエットを インナー とし インナー 全 体 の 売 上 の 合 計 を 出 します スエットの 合 計 のとなりの M12 セルに オート SUM ボタンを 使 って T シャツとスエットの 売 上 の 合 計 を 足 します(ボタンを 押 し L11 と L12 を 範 囲 選 択 ) わかりやすいように その 右 の N12 セルに インナー 合 計 と 書 いておきましょう 2 同 様 に それ 以 外 の 3 商 品 を アウター とし M15 セルに オート SUM ボタンでアウターの 合 計 を 足 し( L13 ~ L15 ) N15 セルに アウター 合 計 と 書 いておきます 図 のようにセルの 下 に 罫 線 を 引 いておくと わかりやすくなります それでは ドーナツグラフを 作 っていきます 1 セルを 選 択 します 複 数 の 範 囲 にわたるので Ctrl キーをうまく 使 いながら 選 びます 商 品 名 が 書 いてある G11 ~ G15 セルと 個 別 分 類 別 の 合 計 が 入 っている L11 ~ M15 セル の2 箇 所 を Ctrl キーを 押 しながら 同 時 選 択 します 2 挿 入 タブ グラフ のグループの 中 の 円 グラフのボタンを 押 し ドーナツグラフ を 選 択 します 28

ドーナツグラフができました 3 見 やすくするため 体 裁 を 整 えましょう グラフの 色 のついた 部 分 をダブルクリックすると 画 面 右 側 に 書 式 設 定 画 面 が 現 れます 一 番 下 の ドーナツの 穴 を 50%まで 小 さくしてみると グラフ 部 分 が 大 きくな り 見 やすくなります 4 次 に どこが 何 のデータを 示 しているかわかりづらいため データラベルをつけます グラフツール デザイン タブの 左 端 の グラフ 要 素 を 追 加 データラベル その 他 のデータ ラベルオプション を 選 択 すると グラフにデータラベルが 付 きます 29

上 の 図 のように ラベルには 画 面 右 側 の 書 式 設 定 (ラベルオプション)から 値 やパーセンテージや 系 列 名 など 好 きな 項 目 を 自 由 に 組 み 合 わせて 表 示 させることができます 上 の 図 では 内 側 のグラフには 分 類 名 と パーセンテージ を 外 側 には セルの 値 をそれぞれ 表 示 さ せました 5 ドーナツの 外 側 の 系 列 名 インナー 合 計 と アウター 合 計 は もともと 表 に 含 まれていないので 自 分 でラベルを 作 ります 挿 入 タブからテキストボックスと 矢 印 などを 使 って 下 図 のように 作 ります 分 類 ごとに 色 の 系 統 を 統 一 すると より 見 やすくなります 下 の 例 では 青 系 緑 系 に 分 けて 区 別 しました グ ラフタイトルも 適 当 なものを 記 入 します より 相 手 に 伝 わるグラフになるように 色 々と 工 夫 してみましょう グラフについては ここでおしまいです ( 巻 末 の 付 録 に 散 布 図 の 作 り 方 があります) 30

4 印 刷 してみよう この 章 では データ シートの 印 刷 設 定 をしながら 印 刷 の 際 に 役 に 立 つ 設 定 や 機 能 を 紹 介 します 4.1 レイアウトの 設 定 ここからは データ シートにある 売 上 データ(4 月 ) を A4 サイズの 用 紙 にきれいに 印 刷 するた めの 設 定 をしていきます Excel の 初 期 設 定 では A4 サイズの 用 紙 に 縦 方 向 に 印 刷 されるようになっています 変 更 したい 場 合 は ページレイアウト タブから 用 紙 の 向 きやサイズ 余 白 などを 自 由 に 設 定 することができます まず データ シートの 売 上 データ(4 月 ) 表 だけが 印 刷 されるように 印 刷 範 囲 に 設 定 します 印 刷 範 囲 は 改 ページプレビュー を 使 うと 簡 単 に 設 定 できます 1 2 表 示 タブ 改 ページプレビュー にします 印 刷 される 範 囲 が 白 く それ 以 外 の 部 分 がグレーになります 青 い 線 で 印 刷 された 時 のページの 境 界 線 が 示 されているので 線 を 自 由 な 位 置 にドラッグして 印 刷 範 囲 を 設 定 します 31

練 習 用 の 部 分 は 印 刷 しなくてよいので 一 番 右 側 の 青 い 線 を 表 の 右 端 までドラッグします 1 ページ 目 と 2 ページ 目 の 境 目 は 月 の 約 半 分 にあたる 15 日 と 16 日 の 間 (50 51 行 目 )に 持 ってきます できたら ファイル タブ 印 刷 を 選 び 右 側 に 表 示 されるプレビュー 画 面 で 確 認 しましょう 1 ページ 目 2 ページ 目 の 両 方 をチェックします 左 上 の 矢 印 ボタンで 元 の 編 集 画 面 に 戻 れます 印 刷 がページの 左 上 に 寄 っているのが 気 になる 場 合 は ページレイアウト タブの ページ 設 定 ダ イアログを 開 き 余 白 タブにある 水 平 中 央 のそれぞれにチェックを 入 れると ページの 真 ん 中 に 印 刷 されます 32

改 ページプレビューを 終 了 するには 表 示 タブから 標 準 を 選 択 します 33

4.2 ヘッダー フッター 次 に ヘッダーとフッターを 設 定 します Word などと 同 じように Excel でも 印 刷 したページの 上 下 にペ ージ 番 号 や 日 付 タイトルなどの 情 報 を 入 れることができます 1 挿 入 タブの ヘッダーとフッター をクリック ヘッダーの 入 力 画 面 になります ページレイアウト 表 示 から ページの 上 下 をクリックして 記 入 することもできます 2 3 リボンにある 各 種 ボタンを 使 って さまざまな 情 報 を 記 入 できます では ヘッダーの 右 側 に 日 付 を 入 れ 中 央 に Excel 応 用 講 習 会 データ と 記 入 してみましょう ヘッダーが 終 わったら ページの 下 の 方 にあるフッターに 移 ります 直 接 移 動 しても リボンの フッタ ーに 移 動 ボタンを 使 ってもかまいません フッターの 中 央 に ページ 番 号 / 全 体 のページ 数 と 表 示 されるように 入 力 してみましょう デザイ ン タブの ページ 番 号 と ページ 数 ボタンで 入 力 し 間 に / (スラッシュ)を 書 き 入 れます できたら プレビュー 画 面 で 確 認 してみましょう 下 のようにうまく 入 れられたでしょうか 34

4.3 印 刷 タイトルの 設 定 もうひとつ ぜひ 覚 えておきたいのが 印 刷 タイトル の 設 定 です この 売 上 データ(4 月 ) のように 長 さがある 表 を 印 刷 する 場 合 上 のプレビューでもわかるように 2 ページ 目 以 降 は 表 の 見 出 し がなく 一 行 目 からいきなりデータで 始 まります これでは データが 何 を 表 しているのかわかりにくいことがあります そのようなとき 印 刷 タイトル を 設 定 しておくと 2 ページ 目 以 降 も 表 の 見 出 しが 印 刷 されるので と ても 見 やすくなります 1 ページレイアウト タブから 印 刷 タイトル を 選 択 します 2 タイトル 行 にカーソルを 置 き 見 出 しに 使 いたい 行 の 行 番 号 (ここでは 2 )をクリックします 3 入 力 できたら OK 印 刷 タイトルが 設 定 できたら プレビューで 確 認 してみましょう 2 ページ 目 にも 見 出 し 行 が 表 示 されて います 35

4.4 印 刷 それでは 設 定 の 済 んだ データ シートを 印 刷 してみましょう 1 ファイル タブ 印 刷 を 選 択 2 プレビューやプリンター 等 の 設 定 を 確 認 し 印 刷 ボタンをクリック 印 刷 が 開 始 されます 思 い 通 りのレイアウトで 印 刷 できたでしょうか ヘッダー フッターや 印 刷 タイトルもしっかりと 付 いて いるか 確 認 してみてください これで この 講 習 の 内 容 はおしまいです 必 要 に 応 じてファイルを 保 存 し Excel を 終 了 してください おつかれさまでした 36

5 付 録 5.1 散 布 図 作 った 表 のデータを 使 って 散 布 図 を 作 ってみましょう 散 布 図 は たとえば 身 長 と 体 重 の 関 係 数 学 と 理 科 の 点 の 関 係 のように 2 つのデータ の 間 の 相 関 関 係 を 表 すグラフです ここでは 商 品 の 単 価 と 売 上 個 数 の 関 係 をみてみましょう 散 布 図 は データが 隣 同 士 に 並 んでいると 簡 単 に 作 れます 商 品 コード 表 の 単 価 のとなりに 売 上 個 数 をコピ ーしておきます 1 並 べた 単 価 と 売 上 個 数 を 使 って 散 布 図 を 作 ります C2 ~ D7 セルを 選 択 し 挿 入 タブ グラフの 中 から 散 布 図 を 選 び 左 上 ( 基 本 的 なタイプ) のものをクリックします 37

2 散 布 図 が 描 けました 相 関 関 係 をさらにはっきりさせるために データ 間 のもっとも 近 いところを 通 る 近 似 曲 線 を 引 いてみ ましょう グラフツール デザイン タブの 左 端 グラフ 要 素 の 追 加 から 近 似 曲 線 線 形 を 選 ぶと 近 似 曲 線 が 引 けます また グラフを 選 択 すると 右 上 にあらわれる + マークからも 近 似 曲 線 を 引 くことができます 右 下 がりの 直 線 から 単 価 が 高 いほど 売 上 個 数 は 低 くなる という 関 係 が 読 み 取 れます 実 際 の 分 析 ではより 多 くのデータ 数 が 必 要 です データラベルを 付 けると よりわかりやすくなります ドーナツグラフの 時 と 同 様 グラフツール デザイン グラフ 要 素 の 追 加 データラベル で 付 けられます 上 の 図 では その 他 のオプション からラベルの 種 類 を セルの 値 にし 商 品 名 のセル( B3 ~ B7 )を 選 択 してあります タイトルには 単 価 と 売 上 個 数 の 関 係 ( 散 布 図 ) と 記 入 しておきましょう これで 散 布 図 の 完 成 です 38

5.2 シートの 保 護 非 表 示 Excel で 作 成 したシートは 他 人 に 改 変 や 削 除 をされないよう 保 護 することができます データ シートを 他 人 に 書 き 換 えられないように 保 護 してみましょう Excel のシート 保 護 の 考 え 方 は 一 見 すると 少 しわかりづらいのですが シート 全 体 を 保 護 する 一 部 のセルを 除 外 ( ロック 解 除 )しておいてから シート 全 体 を 保 護 する この 2 通 りがある と 考 えると 簡 単 です ではまず データ シート 全 体 を 保 護 しましょう 1 ファイル タブをクリックし 情 報 の 一 番 上 の ブックの 保 護 から 現 在 のシートの 保 護 を 選 びます シート 名 のタブを 右 クリックしても 選 べます 2 一 番 上 の ロックされたセル 範 囲 の 選 択 のチェックをはずし OK ここでパスワードを 設 定 する こともできます データ シート 全 体 が 選 択 できなくなり 保 護 されました 保 護 を 解 除 するには もう 一 度 ファイ 39

ル ブックの 保 護 現 在 のシートの 保 護 をクリックします 次 に 一 部 のセルをあらかじめ 除 外 し それ 以 外 の 部 分 を 保 護 する 方 法 です 売 上 データ(5 月 )の 中 だけ 選 択 入 力 ができるようにしてみましょう 売 上 データ(5 月 )の 範 囲 のセルを 選 択 し ホーム タブ 書 式 セルのロック ( これは 解 除 /オンの 切 り 替 えボタンで これで 解 除 したことになります)を 選 択 します あるいは セルの 書 式 設 定 保 護 タブの ロック のチェックを 外 しても 同 じです これで 先 程 のようにシートを 保 護 すると ロックを 解 除 したセルだけ 選 択 編 集 できるようになります また シートそのものを 非 表 示 にすることもできます 40

左 下 の データ シートのタブを 右 クリックし 非 表 示 を 選 択 データ タブが 見 えなくなります 他 のシートのタブを 右 クリック 再 表 示 で 再 び 表 示 させることができます シートを 非 表 示 にすることで 他 人 に 改 変 されるのを 防 いだり 多 すぎるシートを 整 理 したりすることが できます 41