cs-8. 表計算ソフトウエア

cs-8. 表計算ソフトウエアを 用いたデータの扱い （コンピューターサイエンス） URL: https://www.kkaneko.jp/cc/cs/index.html 金子邦彦 1

• https://www.kkaneko.jp/cc/cs/index.html

アウトライン Excel でのデータ整理，データ分析 • 散布図での色分け，クラスタ分析 • Excel のルックアップ • 絶対参照，相対参照 2

Office 365 の種類 • Office 365 のオンライン版 WEBブラウザで使う． https://portal.office.com 各自の ID とパスワードでサインインが必要． • Office 365 のアプリ版 前もってインストールが必要． インストールでは，大量の通信が行われる． （時間がかかる．通信費用にも注意） ２種類ある．この授業では，どちらを使用しても問題 ない 3

• https://portal.office.com

Office 365 オンライン版で Excel を起動 【要点】 Web ブラウザで，次のページを開き，各自の ID とパスワードでサインイン https://portal.office.com 4

• https://portal.office.com

Office 365 オンライン版で Excel を起動 ① Web ブラウザで，次のページを開く https://portal.office.com ② 電子メールアドレスを入れる．「次へ」をクリック． （例）[email protected] 5

• https://portal.office.com

Office 365 オンライン版で Excel を起動 ③ パスワードを入れ，「サインイン」をクリック パスワードは，各自が設定したもの ④ Excel を使いたいときは，メニューで Excel を選ぶ さまざまなメニュー 6

Office 365 オンライン版で Excel を起動 ⑤ Excel のブックの種類を選ぶ この授業では「新しい空白のブック」を使う ⑥ Excel の画面が開く 7

8-1 散布図での色分け， クラスタ分析 （コンピューターサイエンス） URL: https://www.kkaneko.jp/cc/cs/index.html 金子邦彦 8

Excel の散布図 元データ 散布図 この２列で散布図 9

Excel の散布図（色分け） 元データ 散布図 この４列で散布図 setosa は B列，versicolor は C列， virginica は D列（違う列） 10

クラスタ分析 • データを使う • データの密集（クラスタという）を見ることによ る分析 11

Titanic データセット タイタニック号のデータ 救出、客室種類、性別、年齢、料金、家族の有無など 1309名分 12

① Titanic データセットから，救出 (survived)， 年齢 (age)，料金 (fare) の列だけを抜き出し 13

② 救出が 0 の行が先に，救出が 1 の行が後に 来るように並べ替え ・A, B, C 列を昇順で並べ替える操作 （このとき，A 列の値を基準として，全体を 並べ替え） 14

オンライン版 Excel での並べ替え A, B, C 列を昇順で並べ替える操作 （このとき，A 列の値を基準として，全体を並べ替え） ② ① 範囲選択 リボンで「データ」→ 「昇順で並べ替え」 15

アプリ版での並べ替え A, B, C 列を昇順で並べ替える操作 （このとき，A 列の値を基準として，全体を並べ替え） ① 範囲選択 ② リボンで「データ」→「昇順」 16

③ 救出が 1 の行についてのみ，C 列のデータ を D 列に移す • 範囲を選び，右クリックメニューで「切り取 り」 • 移す先の一番上のセルをクリックし，貼り付 けの操作 17

④ B, C, D 列から散布図を作成 （範囲選択のとき，先頭行は選ばないようにする ２行目からを範囲にする） １００人分のデータで みたとき ８９１人分のデータで みたとき オレンジ（救出された）は上側に， 青（救出されなかった）は下側に 偏る傾向があるかもしれない 18

8-2 Excel のルックアップ （コンピューターサイエンス） URL: https://www.kkaneko.jp/cc/cs/index.html 金子邦彦 19

ルックアップはなぜ大切なのか データを正確にコピー＆ペーストし て、さまざまな用途に活用 20

ルックアップの例 購入リスト 氏名 AA BB CC 商品 数量 単価 合計 みかん 3 ? メロン 2 ? りんご 5 ? 商品リスト ? ? ? 商品名 みかん りんご メロン 単価 50 100 500 商品の単価は商品リストに載っている． それを、単価フィールドに転記したい 21

「ルックアップ」で行いたいことの例 AA さんは、みかんを 3個 AA, BB, CC さんの値段が 分かる表を作りたい BB さんは、メロンを 2個 CC さんは、りんごを 5個 22

① １行目 氏名 AA 商品 数量 単価 合計 みかん 3 50 ? BB メロン 2 ? CC りんご 5 参照 ? 商品名 みかん りんご メロン 単価 50 100 500 この範囲で 同じ行の 値を検索し、 別の列の値 23

② ２行目 氏名 AA 商品 数量 単価 合計 みかん 3 50 BB メロン 2 CC りんご 5 500 ? ? 参照 ? 商品名 みかん りんご メロン 単価 50 100 500 この範囲で 同じ行の 値を検索し、 別の列の値 24

③ ３行目 氏名 AA 商品 数量 単価 合計 みかん 3 50 BB メロン 2 CC りんご 5 ? ? 500 100 参照 ? 商品名 みかん りんご メロン 単価 50 100 500 この範囲で 同じ行の 値を検索し、 別の列の値 25

ルックアップの例 ◇ 参照する手がかりとして、「みかん」、「メロン」、 「りんご」の列を使う ◇ 「みかん」、「りんご」、「メロン」の中から値を検索 し、同じ行の別の列にある値を参照する 氏名 AA 商品 数量 単価 合計 みかん 3 50 BB メロン 2 CC りんご 5 500 100 ? ? ? 商品名 みかん りんご メロン 単価 50 100 500 26

Excel の VLOOKUP の例 セル D2 のルックアップの式 =VLOOKUP(B2, $G:$H, 2, FALSE) Enterキーを押すと 50 27

Excel の VLOOKUP の例 セル D2, D3, D4 に入れる、ルックアップの式 =VLOOKUP(B2, $G:$H, 2, FALSE) =VLOOKUP(B3, $G:$H, 2, FALSE) =VLOOKUP(B4, $G:$H, 2, FALSE) Enterキーを押すと 50 500 100 28

なぜ Excel の VLOOKUP を使うのか 自動で検索して、自動で参照するから。 正確（ミスを防げる）、素早くできる 29

演習 ① Excel で、次のようにデータを入力 ※ 「3」や「50」などの数値は、必ず半角に 30

② セル D2 に次の数式を入力 =VLOOKUP(B2, $G:$H, 2, FALSE) 数式バーが便利 ① セル D2 をクリックして ② 入力して Enter キーを押す 「50」を確認 31

Excel の VLOOKUP の使い方の例 =VLOOKUP(B2, $G:$H, 2, FALSE) 「2」は，範囲「$G:$H」 の中の2列目という意味 32

Excel の VLOOKUP の使い方の例 =VLOOKUP(B2, $G:$H, 2, FALSE) 氏名 AA BB CC 「$G:$H」の中の 「みかん」を探せ １列目から 商品 数量 単価 合計 商品名 みかん 3 ? みかん 50 りんご メロン 2 500 ? メロン りんご 5 100 ? この列の値を検索 「$G:$H」の 中の２列目の 値を参照 単価 50 100 500 この列の値を参照 33

③ セル D2 の式を，セル D3, D4 にコピー ・まず，セル D2 を右クリックし、右クリックメニューで「コピー」 ・セル D3，D4 を選び，右クリックメニューで「貼り付け」 34

④ セル D2, D3, D4 の値が 50, 500, 100 になっていることを確認 35

8-3 Excel の絶対参照， 相対参照，ルックアップ （コンピューターサイエンス） URL: https://www.kkaneko.jp/cc/cs/index.html 金子邦彦 36

相対参照と絶対参照 =VLOOKUP(B2, $G:$H, 2, FALSE) B2 は 相対参照 $G や $H は 絶対参照 $ をつけたら： 絶対参照 $ をつけなかったら： 相対参照 37

演習 ① セル D2 を左クリックすると、数式が表示されるので確認する =VLOOKUP(B2, $G:$H, 2, FALSE) 38

② セル D3 を左クリックすると、数式が表示されるので確認する =VLOOKUP(B3, $G:$H, 2, FALSE) 39

③ セル D4 を左クリックすると、数式が表示されるので確認する =VLOOKUP(B4, $G:$H, 2, FALSE) 40

絶対参照と相対参照 • 絶対参照 セルのコピー＆ペーストで、 番地は自動調整されない • 相対参照 セルのコピー＆ペーストで、 番地は自動調整される 41

演習 ① セル D2 では，セル B2 を参照していることを確認 試しに、セル B2 をダブルクリックしたあと，セル B2 の値を「みか ん」から「りんご」に書き換えて、Enter キーを押す 「100」を確認 42

② セル E2 に次の数式を入力 =C2*D2 数式バーが便利 ① セル E2 をクリックして ② 入力して Enter キーを押す 「300」を確認 43

③ セル E2 の式を，セル E3, E4 にコピーする ・まず，セル E2 を右クリックし、右クリックメニューで「コピー」 ・セル E3，E4 を選び，右クリックメニューで「貼り付け」 44

④ 合計を確認 セル E2, E3, E4 の値が 300, 1000, 500 になっている 45

演習 B列の商品を，すべて「メロン」に書き換える． 単価，合計が自動で更新されることを確認 46

演習 余裕のある人は，次のような場合を考えてみる 氏名 XX YY ZZ AA BB 元データ 商品 価格 カレー うどん うどん カレー そば 価格のデータ ? ? ? ? ? できあがり 氏名 XX YY ZZ AA BB 商品 価格 カレー 400 うどん 250 うどん 250 カレー 400 そば 280 商品 価格 うどん 250 そば 280 カレー 400 47