平 成 27 年 度 下 半 期 パソコン 教 室 Excel 編 Excel 2010 平 成 28 年 1 月 12 日 ( 火 ) 千 船 会 場 千 船 病 院 食 堂 20 日 ( 水 ) 高 槻 会 場 愛 仁 会 看 護 助 産 専 門 学 校 5F 情 報 科 学 室 28 日 ( 木 ) 明 石 会 場 明 石 医 療 センター 附 属 看 護 学 校 3F 情 報 処 理 室 愛 仁 会 本 部 医 療 情 報 部 企 画 部 学 術 部 TQM 推 進 室
基 本 機 能 紹 介 ウィンドウ 枠 の 固 定 並 べ 替 え フィルタ 関 数 ウィンドウ 枠 の 固 定 画 面 いっぱいの 表 があるとき 左 右 上 下 にスクロールしてしまうと 見 出 しが 見 えなくなるが その 見 出 しを 固 定 していつでも 見 出 しが 見 れる 状 態 に 出 来 る 機 能 です 例 (このブックのwindow 枠 固 定 シートを 例 に 説 明 する ) 見 出 し 行 見 出 し 列 上 記 見 出 しにする 時 の 操 作 方 法 1 行 列 見 出 の 交 差 する 右 下 のセルをクリック 2 表 示 タブ ウィンドウ 枠 の 固 定 ウィンドウ 枠 の 固 定 ウィンドウ 枠 の 固 定 の 解 除 上 記 1~2まで 同 じ 作 業 後 3ウィンドウ 枠 固 定 の 解 除 なっているとこをクリック
並 べ 替 え 表 のなかの 任 意 の 項 目 順 に 並 び 替 える 機 能 です 例 (このブックのwindow 枠 固 定 シートを 例 に 説 明 する ) <ひとつの 項 目 のみの 場 合 > 下 の1 月 の 売 り 上 げ 個 数 の 多 い 順 番 に 並 べかえる 11 月 の 列 の 文 字 が 入 っているとこであればどこでもいいのでクリック 例 では 味 の 素 の1 月 の 個 数 100をクリックしてる 2 データタブ => 降 順 ボタン をクリック 売 り 上 げ 個 数 下 のように 並 びましたか?
< 複 数 の 項 目 を 使 って 並 べ 替 えたい 場 合 > 下 の 例 で 区 分 順 の 更 に 同 一 区 分 の 中 で1 月 売 上 個 数 降 順 11 月 の 列 の 文 字 が 入 っている 任 意 の 場 所 をでクリック 例 では 味 の 素 の1 月 の 個 数 100をクリックしてる 2 データタブ => 並 べ 替 え をクリック 売 り 上 げ 個 数 3 最 優 先 されるキーに 区 分 を 選 ぶ=>レベルの 追 加 をクリック=> 次 に 優 先 されるキー に1 月 を 選 ぶ=>2 段 目 の 順 序 を 降 順 にする 下 のように 並 びましたか?
フィルタ (このブックのwindow 枠 固 定 シートを 例 に 説 明 する ) データの 中 から 条 件 に 合 うデータだけを 抽 出 して 表 示 する 機 能 です 下 の 例 で 区 分 がパンのものを 抽 出 してみよう 1 行 番 号 をクリックして 抽 出 したい 見 出 し 行 全 体 を 選 択 する 2 データタブ => フィルタ をクリック 下 のようにした 三 角 がでましたでしょうか?
区 分 の 下 三 角 をクリックして リストからパンを 選 ぶ
関 数 を 使 う 合 計 平 均 オートフィルと 絶 対 参 照 累 積 比 率 合 計 (sum) 1 合 計 を 出 したいセルをクリック 2 ホームタブ オートSUM エンターキー 入 力 オートフィル はまち と えび も 同 じ 合 計 関 数 を 入 力 する 時 にオートフィル 機 能 を 使 う 図 の 位 置 にカーソルをあわせるとカーソルが 十 字 に 変 わるのでそこでダブルクリック 平 均 (AVERAGE) 1 平 均 を 出 したいセルをクリック 2 ホームタブ オートSUM 右 側 の をクリック 平 均 をクリック エンターキーを 入 力
オートフィルと 絶 対 参 照 sum 関 数 を 考 えてみる A B C D E F G 1 4 月 5 月 6 月 7 月 8 月 9 月 合 計 2 3 4 2 1 1 2 =SUM(A2:F2) SUM( 開 始 セル : 終 了 セル)で 開 始 セルから 終 了 セルまでの 全 てのセルを 合 計 する 上 記 の 例 ではA2+B2+C2+D2+E2+F2 となる 下 の 表 で3 行 目 と4 行 目 に 同 じ 合 計 関 数 を 入 力 するとき オートフィルを 使 う A B C D E F G 1 4 月 5 月 6 月 7 月 8 月 9 月 合 計 2 3 4 2 1 1 2 13 3 5 2 3 4 1 5 20 4 1 7 3 1 4 2 18 式 の 中 身 A B C D E F G 1 4 月 5 月 6 月 7 月 8 月 9 月 合 計 2 3 4 2 1 1 2 =SUM(A2:F2) 3 5 2 3 4 1 5 =SUM(A3:F3) 4 1 7 3 1 4 2 =SUM(A4:F4) 計 算 式 において 絶 対 参 照 と 相 対 参 照 とは 絶 対 参 照 とは 行 列 ともに 固 定 させる 参 照 方 法 相 対 参 照 とは 基 準 セル(その 式 を 入 れてるセル)から 見 て 行 列 に 相 対 位 置 で 表 す 参 照 方 法 通 常 の 今 作 成 したような 式 は 絶 対 参 照? 相 対 参 照? 答 えは 相 対 参 照 です 連 続 してオートフィルを 使 って 作 成 したSUM 関 数 を 良 く 見 ると 行 にあたる 数 字 の 赤 の 部 分 が1ずつ 加 算 されています =SUM(A2:F2) =SUM(A3:F3) =SUM(A4:F4) エクセルの 世 界 では 表 になっているので 連 続 して 式 を 入 力 するケースが 多 いから 相 対 参 照 が 標 準 になりました 相 対 参 照 を 詳 しく 知 りたい 方 は 最 終 ページのコラムを 参 照 ください
絶 対 参 照 の 具 体 的 な 指 定 方 法 1 合 計 を 出 したいセルをクリック 2 ホームタブ オートSUM F4キー 入 力 すると$が 付 く エンターキー 入 力 A B C D E F G 1 4 月 5 月 6 月 7 月 8 月 9 月 合 計 2 3 4 2 1 1 2 =SUM($A$2:$F$2) 3 5 2 3 2 1 5 4 1 7 3 1 4 2 オートフィルでコピーすると... A B C D E F G 1 4 月 5 月 6 月 7 月 8 月 9 月 合 計 2 3 4 2 1 1 2 13 3 5 2 3 4 1 5 13 4 1 7 3 1 4 2 13 式 の 中 身 コピーしても 絶 対 参 照 の 場 合 3 行 目 4 行 目 であっ ても 計 算 結 果 は2 行 目 の 合 計 を 表 示 する A B C D E F G 1 4 月 5 月 6 月 7 月 8 月 9 月 合 計 2 3 4 2 1 1 2 =SUM($A$2:$F$2) 3 5 2 3 4 1 5 =SUM($A$2:$F$2) 4 1 7 3 1 4 2 =SUM($A$2:$F$2) 絶 対 参 照 $ が 付 いていると 相 対 参 照 の 合 計 式 をコピーしたときのように 計 算 開 始 位 置 計 算 終 了 位 置 は 変 化 しない
絶 対 参 照 の 補 足 説 明 絶 対 参 照 の 指 定 には 行 と 列 を 固 定 する 事 の 他 列 のみ 固 定 や 行 のみ 固 定 ができる 完 全 な 絶 対 参 照 ( 行 と 列 の 固 定 ) =SUM($A$2 ~ 列 固 定 行 固 定 行 だけ 絶 対 参 照 =SUM(A $2 ~ 列 行 固 定 列 だけ 絶 対 参 照 =SUM($A2 ~ 列 固 定 行
うなぎの 全 国 平 均 から 見 た 割 合 を 出 してみよう 相 対 参 照 と 絶 対 参 照 を 理 解 できたところで 関 数 用 シートのうなぎの 全 国 平 均 から 見 た 割 合 を 解 説 します 1 全 国 平 均 割 合 の4 月 をクリック 2イコール = を 入 力 3うなぎの4 月 実 績 をクリック(クリックするとC22と 入 る) 4スラッシュ /"を 入 力 5 分 母 に 当 たる 全 国 平 均 をクリックしてF4キーを 一 度 押 す 65 月 から8 月 までコピーして 完 成 させる 絶 対 参 照 図 に 位 置 にカーソルをあわせるとカーソルが 十 字 に 変 わるのでドラックして 9 月 のところでドロップする
累 積 比 率 の 概 要 説 明 累 積 比 率 = 累 積 数 / 売 上 数 の 合 計 ( 累 積 数 とは1 行 目 のりんごから 該 当 行 までの 合 計 となる) 今 回 のテキストの 例 では 具 体 的 に 下 図 のような 数 値 を 求 める 85 (リンゴの 数 のみ) 135 (リンゴとスイカの 合 計 ) 170 (リンゴからバナナまでの 合 計 ) ~ 246 (リンゴからぶどうまでの 合 計 ) 累 積 数 の 計 算 式 : =SUM ( $B$2 : B2 ) 'りんご 売 上 数 ( 位 置 固 定 ) 計 算 行 の 売 上 個 数 累 積 比 率 の 計 算 式 : =C2 / $B$13 計 算 行 の 累 積 数 売 上 合 計 ( 位 置 固 定 ))
実 際 に 後 のパレート 図 で 使 う 累 積 比 率 を 求 めよう パレート 図 の 事 前 資 料 にあったように 累 積 比 率 とは その 項 目 の 累 積 数 の 全 体 に 対 する 比 率 を 示 した 値 です 手 順 : 1 累 積 数 をSUM 関 数 とオートフィルを 使 い 完 成 させる 2 求 めた 累 積 数 と 売 上 数 の 合 計 を 使 って 累 積 比 率 を 完 成 させる 1 累 積 値 をSUM 関 数 とオートフィルを 使 い 完 成 させる りんごの 累 積 数 でオートSUM F4 ( 合 計 開 始 行 はりんごに 固 定 したいので 絶 対 参 照 にする) 一 旦 SUM 関 数 の 合 計 開 始 位 置 を 固 定 するために 絶 対 参 照 に 変 えた 位 置 にカーソルを 移 動 させてクリックする 半 角 でコロンを 入 れて 合 計 終 了 セルのりんごの 行 をもう 一 度 クリックしてエンターキーを 押 して 確 定 する 出 来 たりんごの 累 積 数 の 式 をオートフィルを 使 って 最 終 行 までコピーする
下 のようになりましたでしょうか 2 求 めた 累 積 数 と 売 上 数 の 合 計 を 使 って 累 積 比 率 を 完 成 させる りんごの 累 積 比 率 をクリックし 半 角 で = 入 力 後 りんごの 累 積 数 をクリック 半 角 で/ を 入 力 続 いて 合 計 の 売 上 個 数 をクリックして F4 キー 入 力 ( 合 計 位 置 は 固 定 したいので 絶 対 参 照 )
出 来 たりんご 行 の 式 をオートフィルを 使 って 最 終 行 までコピー 今 回 は 理 解 がしやすいように 累 積 数 を 求 めてから 累 積 率 を 求 めたが 実 際 のグラフ 作 成 時 は 累 積 率 を 直 接 求 めたほうが グラフを 作 りやすいので 下 図 のように 計 算 したほうがよい 累 積 比 率 = 累 積 数 合 計
コラム 相 対 参 照 とは 1 頁 目 の 式 より sumの 前 にある =(イコールは) 省 略 しています 式 の 中 身 A B C D E F G 1 4 月 5 月 6 月 7 月 8 月 9 月 合 計 2 3 4 2 1 1 2 13 3 5 2 3 4 1 5 20 4 1 7 3 1 4 2 18 A B C D E F G 1 4 月 5 月 6 月 7 月 8 月 9 月 合 計 2 3 4 2 1 1 2 SUM(A2:F2) 3 5 2 3 4 1 5 SUM(A3:F3) 4 1 7 3 1 4 2 SUM(A4:F4) 合 計 欄 の1 段 目 SUM(A2:F2)を2 段 目 に 複 写 する 場 合 を 考 えます 普 通 に 考 えれば 2 段 目 もSUM(A2:F2)と 入 り 式 がSUM(A2:F2)なので 合 計 したら13になるように 思 えます しかし 実 際 は SUM(A3:F3) となります その 理 由 は 計 算 式 の 開 始 終 了 セル( 参 照 セル)の 考 え 方 が 工 夫 されているからです エクセルでは 計 算 式 の 開 始 終 了 セルは 1 段 目 であれば 合 計 式 ( SUM(A2:F2) ) から 見 て 左 に6 個 移 動 した 場 所 を 計 算 開 始 位 置 と 考 え 同 じように 式 が 入 力 されている 場 所 から 左 に 一 つ 移 動 した 位 置 を 計 算 終 了 位 置 と 考 えます 次 に1 段 目 の 合 計 式 を2 段 目 に 複 写 した 例 で 考 えます 計 算 式 を2 段 目 に 複 写 した 場 合 式 の 複 写 もとはG2セルで 複 写 された 場 所 はG3セルとなり 複 写 前 より1 段 下 にずれます 複 写 した 結 果 参 照 方 式 の 概 念 通 りだとします 開 始 セルはコピー 後 の 合 計 式 (G3)セルからみて 左 に6 個 移 動 し 場 所 です 終 了 セルも 同 じように 考 えると 合 計 式 (G3)セルから 左 に 一 つ 移 動 した 場 所 となります エクセル 内 部 ではこのように 開 始 終 了 セルを 入 力 セルからの 相 対 位 置 を 参 照 しています ゆえに 式 を 単 純 に 複 写 するだけで この 例 ではSUM(A2:F2)ではなくSUM(A3:F3)という 結 果 が 得 られるのです このように 実 際 に 計 算 式 が 入 力 されているセルの 相 対 位 置 でセル 参 照 する 方 式 を 相 対 参 照 といいます
1.フィルターを 使 い 冷 凍 食 品 を 抽 出 降 順 に 並 べる 1 区 分 冷 凍 食 品 円 グラフを 作 成 する 冷 凍 食 品 の 売 り 上 げ 価 格 の 割 合 の 円 グラフを 作 成 してみる 2 表 内 の 並 べ 替 えたい 数 値 にカーソルを 合 わせる 3 データ 降 順 ボタン
4 下 記 の 表 になる 2. 商 品 名 と 売 上 金 額 の 円 グラフを 挿 入 1 商 品 名 売 上 金 額 を 選 択 (Ctrlを 押 しながら 選 択 する) 2 挿 入 円 2-D 円 3 下 記 の 円 グラフができる
3.データラベルの 編 集 をする 1 グラフツール レイアウト データラベル その 他 のデータラベルオプション グラフツールを 使 うときは 必 ずグラフを 選 択 しておくこと ( 表 示 されなくなります) 3ラベルの 内 容 分 類 名 パーセンテージ 引 き 出 し 線 を 表 示 する ラベルの 位 置 自 動 調 整 にチェックを 入 れる
4 下 記 の 円 グラフになる 5 今 回 は 円 グラフ 内 に 分 類 名 を 入 れたため 凡 例 は 消 しておく ( 選 択 して デリート) 4. 円 グラフにタイトルをつける (ワードアートを 使 用 ) 1 売 上 金 額 冷 凍 食 品 売 上 金 額 比 率 に 変 更
2 グラフツール 書 式 ワードアートのスタイル すきなワードアートを 選 択 完 成!! 同 じ 要 領 で 棒 グラフも 作 成 できます!! 2. 商 品 名 と 売 上 金 額 の 円 グラフを 挿 入 2 挿 入 円 2-D 円 2-D 円 を 2-D 棒 に 変 えるだけ!!
このような 棒 グラフができる 同 じ 要 領 で 進 めると 下 記 の 棒 グラフが 完 成 する
1. 集 計 結 果 を 表 にまとめる パレート 図 を 作 成 する 項 目 売 上 個 数 ミカン 11 パイナップル 8 バナナ 35 レモン 20 いちご 15 モモ 13 メロン 3 マンゴー 5 スイカ 50 ぶどう 1 りんご 85 2. 集 計 結 果 を 降 順 に 並 べ 替 える 1 表 内 の 並 べ 替 えたい 数 値 にカーソルを 合 わせる 2 データ 降 順 をクリックする 下 のように 並 びましたか? 項 目 売 上 個 数 りんご 85 スイカ 50 バナナ 35 レモン 20 いちご 15 モモ 13 ミカン 11 パイナップル 8 マンゴー 5 メロン 3 ぶどう 1
3. 合 計 値 累 積 比 率 を 計 算 し 表 を 整 える( 累 積 比 率 0の 行 を 追 加 ) *オートSUM 使 用 方 法 累 積 比 率 の 計 算 方 法 は 関 数 資 料 参 照 1 合 計 売 上 個 数 の 欄 にカーソルを 合 わせて ホーム オートSUM Enter(エンター)キー を 押 す 項 目 売 上 個 数 りんご 85 スイカ 50 バナナ 35 レモン 20 いちご 15 モモ 13 ミカン 11 パイナップル 8 マンゴー 5 メロン 3 ぶどう 1 合 計 246 2 累 積 比 率 を 計 算 する 項 目 売 上 個 数 累 積 比 率 りんご 85 0.345528 スイカ 50 0.54878 バナナ 35 0.691057 レモン 20 0.772358 いちご 15 0.833333 モモ 13 0.886179 ミカン 11 0.930894 パイナップル 8 0.963415 マンゴー 5 0.98374 メロン 3 0.995935 ぶどう 1 1 合 計 値 246 3タイトル 行 の 下 に 累 積 比 率 0%の 行 を 作 成 するため 行 を 入 れたい1 行 下 (ここではりんごの 行 )を 選 択 する 右 クリック 挿 入 下 方 向 にシフト を 選 択 し OK をクリック 挿 入 した 行 の 累 積 比 率 に0を 入 力 する
項 目 売 上 個 数 累 積 比 率 もし この 行 を 追 加 しなかったら 0 累 積 比 率 の 折 れ 線 グラフが 原 点 からスタートしません りんご 85 0.345528 スイカ 50 0.54878 バナナ 35 0.691057 レモン 20 0.772358 いちご 15 0.833333 モモ 13 0.886179 ミカン 11 0.930894 パイナップル 8 0.963415 マンゴー 5 0.98374 メロン 3 0.995935 ぶどう 1 1 合 計 値 246 4. 累 積 比 率 の 表 示 形 式 をパーセンテージにし 小 数 点 第 一 位 までを 表 示 する 1 累 積 比 率 の 数 値 をドラッグして 範 囲 選 択 し ホーム パーセントスタイル をクリックする 2 累 積 比 率 をドラッグして 範 囲 選 択 し(1で 選 択 されていればそのまま) ホーム 小 数 点 以 下 の 表 示 桁 数 を 増 やす をクリックする
累 積 比 率 が% 小 数 点 第 一 位 までの 表 示 に 変 わりましたか? 項 目 売 上 個 数 累 積 比 率 0% りんご 85 34.6% スイカ 50 54.9% バナナ 35 69.1% レモン 20 77.2% いちご 15 83.3% モモ 13 88.6% ミカン 11 93.1% パイナップル 8 96.3% マンゴー 5 98.4% メロン 3 99.6% ぶどう 1 100.0% 合 計 値 246 5.グラフを 作 成 する 表 の 合 計 値 以 外 を 範 囲 選 択 し 挿 入 縦 棒 集 合 縦 棒 をクリックする 以 下 のグラフができましたか?
6.グラフのデータ 範 囲 を 変 更 する 1 売 上 個 数 の 棒 グラフ(ここでは 青 の 棒 グラフ)をクリックする 2 項 目 売 上 個 数 を 空 白 以 外 の 範 囲 にそれぞれ 変 更 する * 角 にカーソルを 合 わせドラッグする 7.グラフを 整 える 1 売 上 個 数 の 棒 グラフ(ここでは 青 の 棒 グラフ)を 右 クリックし データ 系 列 の 書 式 設 定 をクリックし 要 素 の 間 隔 を0にして 閉 じる (バーをスライドさせる or 0を 入 力 する) *この 段 階 では 青 の 棒 グラフの 間 に 赤 の 棒 グラフがあるため 接 するグラフに 見 えない グラフとグラフの 間 隔 をなくし 接 する 棒 グラフにする
2 累 積 比 率 の 折 れ 線 グラフ(ここでは 赤 の 棒 グラフ)を 右 クリック 系 列 グラフの 種 類 の 変 更 マーカー 付 折 れ 線 を 選 択 OK をクリックする 3 累 積 比 率 の 棒 グラフ(ここでは 赤 の 棒 グラフ)を 右 クリックし データ 系 列 の 書 式 設 定 をクリックする 第 2 軸 ( 上 / 右 側 ) 閉 じる をクリックする ( 左 の 軸 は 売 上 個 数 の 軸 で 累 積 比 率 の 軸 はないため 追 加 する) 累 積 比 率 の 軸 を 追 加 する
4 累 積 比 率 の 軸 の 最 大 値 を100%にする 第 2 軸 縦 ( 値 ) 軸 (ここでは 右 側 の 累 積 比 率 の 軸 )を 右 クリック 軸 の 書 式 設 定 最 大 値 を 固 定 1 を 入 力 して 閉 じる をクリックする 累 積 比 率 の 軸 の 最 大 値 を100%にする 5 売 上 個 数 の 軸 を 今 回 の 合 計 値 ( 累 積 比 率 =100%)である246に 設 定 する 第 1 軸 縦 ( 値 ) 軸 (ここでは 左 側 の 累 積 比 率 の 軸 )を 右 クリック 軸 の 書 式 設 定 最 小 値 を 固 定 0 最 大 値 を 固 定 246(ここでの 合 計 値 ) を 入 力 して 閉 じる をクリックする 売 り 上 げ 個 数 の 軸 の 最 小 値 を0 最 大 値 を246( 今 回 の 合 計 値 )にする
6 累 積 比 率 の 折 れ 線 グラフ(ここでは 赤 のマーカー 付 折 れ 線 グラフ)が 選 ばれている 状 態 で グラフツール レイアウト 軸 第 2 横 軸 その 他 の 第 2 横 軸 オプション をクリックし 軸 位 置 = 目 盛 にし OKをクリックする これで 折 れ 線 グラフが 原 点 から 始 まる 折 れ 線 グラフを 原 点 からスタートさせる 7 第 2 横 軸 ラベルが 勝 手 に 表 示 されて 見 づらいので ラベルをなしにしてすっきりさせる 累 積 比 率 の 折 れ 線 グラフ(ここでは 赤 のマーカー 付 折 れ 線 グラフ)が 選 ばれている 状 態 で グラフツール レイアウト 軸 第 2 横 軸 ラベルなしで 軸 を 表 示 をクリックする グラフツールを 使 うときは 必 ずグラフを 選 択 しておくこと ( 表 示 されなくなります) 第 2 横 軸 を 表 示 ラベルなしで 表 示 する
以 上 で 完 成 です!!
教 材 データダウンロード 手 順 1インターネットを 開 きアドレスバーにhttps://goo.gl/zY5MBbを 入 力 してエンターキーを 押 す 2 下 のような 画 面 が 開 いたら ダウンロードしたいファイルをクリックする 3ダウンロードボタンをクリックする(ボタンがない 場 合 は 画 面 上 の 方 にカーソルを 移 動 させる) 4 保 存 の 右 の を 押 し 自 分 の 分 かる 場 所 に 保 存 する *お 使 いのブラウザのバージョンによってはダウンロードができない 場 合 があります ダウンロードできない 場 合 は 愛 仁 会 本 部 医 療 情 報 部 までご 連 絡 下 さい