情報機器の操作 ( 第 10 回 ) 産業技術科学科多田知正 htada@kyokyo-u.ac.jp 1
はじめに この講義の WWW ページ http://teched.kyokyo-u.ac.jp/~htada/class/sousa/ 演習で使うデータ等はここにおいておきます 2
予告 今日でExcelの話は終わりです 来週は 課題作成の日 となります 方法は前回と同じです これまでにやったことを復習しておいてください 3
演習 ( 前回の復習 ) サンプルファイル (excel-sample3.xlsx) をダウンロードして開く リンクを右クリックして 対象をファイルに保存 B 列 ( 0 系 ) に数値を記入する 数値は Wikipedia 新幹線 0 系電車 で調べること D 列 ( 差 ) の各セルに適切な表示形式を設定する D2 セルは後ろに 日 を付ける 他のセルは B 列や C 列の設定を参考にすること 4
演習 ( つづき ) E 列 ( 差 (%) ) に以下のように表示形式を設定する パーセンテージで表す ( 小数点以下 1 桁 ) 正の値の場合は黒色, 負の値の場合は赤色にする ユーザ定義 の中の例を参考に 5
ピボットテーブル 6
ピボットテーブル これを使えるかどうかで Excel の初心者と中級者が分かれる 初心者はまず使わない そもそも存在自体知らない 使い出したらやめられない 実はすごく便利 7
演習 名簿ファイルその 1(meibo.xlsx) をダウンロードして開く 血液型 の欄により血液型別の人数を求め, 血液型別人数 シートに記入する A 型 :? 名 B 型 :? 名 O 型 :? 名 AB 型 :? 名 合計は自動的に出ます (100 名になるはず ) 8
演習 ( ではない ) 出身地 の欄により出身地の都道府県別の人数を求める 京都府 :? 名 大阪府 :? 名 : って言われたらどう思いますか? 都道府県ってめっちゃ数あるやん 時間かかるし面倒 9
ピボットテーブルの威力 ピボットテーブルを使えば, こんな作業は 一瞬 です 10
ピボットテーブルとは 一言でいうと, データの集計をグループごとに行う機能 11
ピボットテーブルの用語 フィールド データの列のこと 一番上のセルは フィールド名 を表す 電話番号 というフィールド 12
ピボットテーブルの作り方 (1) 集計の対象となるデータ範囲をマウスで選択する 13
ピボットテーブルの作り方 (1.5) データ範囲全体を選択する場合, 対象範囲の中のセル ( どこでも良い ) を 1 つだけ選択しても良い 自動的に全体が選択される 14
ピボットテーブルの作り方 (2) ここをクリック OK をクリック 新しいシートが作られ, ピボットテーブルが置かれます 15
データを選択する際の注意点 フィールド名 を空白にしないこと こんなエラーがでます 選択範囲に空行を含めないこと 空行とは データの入っていない行 のこと ここをクリックしてシート全体を選択するのは NG 面倒でもマウスで範囲を選択すること またはセルを 1 つだけ選択 16
演習 名簿 シートに移動 ピボットテーブルを作成する 17
ピボットテーブルの使い方 ここにあるフィールド名をマウスでつかんで 集計したいフィールドを 値 にドラッグします グループ分けを行う場合 行 か 列 にドラッグします データの絞込みを行う場合 フィルタ にドラッグします 18
集計方法の変更 データの集計方法を変更します データのある場所で右クリック 19
演習 今作成したピボットテーブルで以下の作業をする 身長 を 値 のところにドラッグする ピボットテーブルが 合計 / 身長 となることを確認 ピボットテーブルの上で右クリックする 値の集計方法 データの個数 を選択 ピボットテーブルの数値がデータの個数 ( 人数 ) になったことを確認 出身地 を 行 のところにドラッグする 出身地別に人数が求められることを確認 20
演習 ( つづき ) 性別 を 列 のところにドラッグする さらに男女別に人数が求められることを確認 血液型 を フィルタ のところにドラッグする ピボットテーブルの上に 血液型 ( すべて ) と表示される ( すべて ) の横のをクリック 複数のアイテムを選択 にチェック AB O のチェックを外して OK をクリック ( すべて ) が ( 複数のアイテム ) に変わったことを確認 血液型 A B の人のみの集計になっている 21
既存のシートに置く場合 ピボットテーブルを新しいシートではなく既存のシートに置くことができます 一つのシートに複数のピボットテーブルを置くことも可能 こっちをチェック ここをクリックして場所を選択 22
ピボットテーブルの複製 実はピボットテーブルはコピー & ペーストができます ピボットテーブルの範囲を選択して普通にコピペするだけ ピボットテーブル全体を含むように選択する 貼り付けたピボットテーブルは, 元のピボットテーブルと同様に操作できます 複数のピボットテーブルを同じシート上に置く場合, これが一番早い 23
演習 ピボットテーブル全体 ( A1 から D37 まで ) をマウスで選択 右クリックして コピー を選択 F1 セルで右クリックして貼り付け ピボットテーブルの複製ができる 元のピボットテーブルをクリック 列 に入っている 性別 をドラッグして外に出す 星座 を 列 のところにドラッグする ドラッグできないことを確認 コピーしたピボットテーブル全体をマウスで選択して Delete キーを押す ピボットテーブルが削除される 元のピボットテーブルをクリック もう一度 星座 を 列 のところにドラッグする エラーが出ないことを確認 24
注意点 一枚のシートに複数のピボットテーブルを置く場合 ピボットテーブルの操作によって, 大きさが変わることに注意 ほかのピボットテーブルと重なるような操作をしようとしても無視される 25
演習 ピボットテーブル全体を選択 右クリックして コピー を選択 P1 セルで右クリックして貼り付け ピボットテーブルの複製ができる コピーしたピボットテーブルをクリック フィルタ に入っている 血液型 をドラッグして外に出す 列 に入っている 星座 をドラッグして外に出す コピーしたピボットテーブルの Q4 セルを右クリックする 値の集計方法 平均 を選択 出身地別の身長の平均が求められていることを確認 26
演習 ( つづき ) 同様の手順で, ピボットテーブルをさらに適当な場所にコピペして, 以下を作成する 男女別の BMI の最大値と最小値を求めるピボットテーブル BMI の意味が気になる人は Google 先生に聞くこと 27
データの並べ替え 表中のデータを並べ替えることができます データのある場所で右クリック 28
演習 出身地別の身長の平均を求めたピボットテーブルを選択 Q4 セルで右クリック 並べ替え 降順 を選択 平均身長の高い順に並んでいることを確認 29
グループ化 複数のグループをまとめることができます グループ化したい場所で右クリック ここをクリック 単位 ( グループの範囲 ) を決めて OK をクリック 30
演習 ピボットテーブルを適当な場所にコピペして以下を作成する 年齢別 (10 歳ごと ) の人数の分布を求めるピボットテーブル 31
グループ名の書き換え グループ名を直接書き換えることができます こちら ( 集計結果 ) は書き換えできません 32
注意 グループ名を変えようとしてこういうエラーが出る時があります グループ名を空白 (Null) にしたのが原因 これが出ると他の操作ができなくなります 知らないと結構焦る グループ名に何か値を入れると解消します 33
演習 年齢別の人数を求めたピボットテーブルを選択 グループ名を次のように書き換える 10-19 10 代 20-29 20 代 30-39 30 代 : 34
ピボットグラフ ピボットテーブルの内容をグラフで表示 ここをクリック グラフの種類を決めて OK をクリック 35
演習 年齢別の人数を求めたピボットテーブルを選択 ピボットグラフを作成する 普通の棒グラフで良い ピボットテーブルをコピペして, 以下を作成する 体重別 (5kg ごと ) の人数の分布を求める 今作成したピボットテーブルを選択 ピボットグラフを作成 普通の棒グラフで良い 体重別の人数を求めたピボットテーブルをクリックし 性別 を 列 のところにドラッグする 体重の分布が男女別になることを確認 ピボットテーブルの 女 と書かれたセルを右クリック 並べ替え 降順 を選択 男女の並びが逆になることを確認 36
自分で使い方を調べる 37
お疲れさまでした これで Excel について, 知っておくべきことはほぼ学びました もう教えることはありません でもまだ知らない機能がいっぱいある? 大丈夫です, なぜなら... 38
Google 先生に聞こう Excel の情報は特にネット上に多く存在する それだけ 使っている人 困っている人 が多いということ 必要になったらその時に調べる 大抵のことはわかる ( はず ) 39
演習 名簿ファイルその 2(meibo2.xlsx) をダウンロードして開く 次のスライドからの課題を順に行う 方法は Google で検索して調べること 検索結果の中からわかりやすいものを選んでそこに書いている方法でやってみる 一番上のページが一番わかりやすいとは限らない 赤字は検索キーワードのヒント 40
課題 : 画面の分割 画面を分割して独立にスクロールできるようにする Excel 2016 画面分割 結果を確認したら分割を解除する 41
課題 : 項目名の固定 表をスクロールしたときに項目名の欄がスクロールしないようにする Excel 2016 項目名固定 この行だけ動かない 42
課題 : データの並べ替え 名簿を氏名の 50 音順に並べ替える Excel 2016 並べ替え できたら Ctrl-Z で元に戻す 43
課題 : データの絞り込み 血液型が A 型の人の行のみが表示されるようにする Excel 2016 データ抽出 できたら Ctrl-Z で元に戻す 44
課題 : 電話番号の修正 電話番号 2 の欄に電話番号の間にハイフンを入れたものを出力する式を書く 例 :0321219316 03-2121-9316 Excel 2016 電話番号ハイフン挿入関数 45
課題 : 文字列の日付への変換 生年月日 の列のセルをクリックして表示形式を見る 文字列 になっている ( 日付形式ではない ) ことを確認 生年月日 2 の欄に日付形式の生年月日を求める式を書く Excel 2016 文字列 日付形式 変換 関数 46
課題 : 年齢の記入 年齢 の欄に今日現在の年齢を求める式を書く Excel 2016 生年月日年齢 47
課題 : 住所の挿入 住所 1 住所 2 住所 3 の欄に住所を求める式を書く 住所は郵便番号を元に調べる 郵便番号と住所の対応は 郵便番号 シートに書かれている VLOOKUP 関数 48
課題 : 行の塗り分け 性別によって行を塗り分ける 男 の場合 女 の場合 Excel 2016 行色条件 49
課題 : ローマ字の氏名 ( 余裕のある人限定 ) 氏名ヨミ の隣に 氏名ローマ字 の列を作成し, 氏名のローマ字表記を記載する 名, 姓の順にする すべて大文字で表記する 例 : 荒木清次 SEIJI ARAKI どんな手を使っても良い できるだけ楽な方法で 50
おしまい 51