情報リテラシー演習 第6週 Excelの使い方3.

Presentation on theme: "情報リテラシー演習 第6週 Excelの使い方3."— Presentation transcript:

1 情報リテラシー演習 第6週 Excelの使い方3

2 Excelの使い方 一応シラバスでは 先々週 先週 今週 並び替えとオートフィルタ グラフ表示の編集 セル入力の基本操作 クロス集計
数式の記述法 よく利用される関数 範囲指定とグラフの描画 表とグラフの作成演習 先週 IF関数を用いた判定と表示 その他の関数 判定表の作成演習 ヒストグラムの作成演習 今週 並び替えとオートフィルタ グラフ表示の編集 クロス集計 上記機能の演習

3 データの入力規則(1/5) データの誤入力防止に効果的 値の範囲指定 入力可能データをリストから選択 等々 サンプルデータ(資料001)

4 データの入力規則(2/5) 規則を設定したい範囲を選択 「データ」→「データの入力規則」

5 データの入力規則(3/5) 別のワークシートから取ってくる場合 入力値の種類 リスト 元の値 =性別!A:A

6 データの入力規則(4/5) 直接入力設定する場合 入力値の種類 リスト 元の値 男,女 列挙 半角カンマで

7 データの入力規則(5/5) 入力欄にプルダウンボタン リストから値を選択可能に キーボード入力の時は ALT+↑またはALT+↓

8 クロス集計 IT用語辞典e-Word「クロス集計」によると Excelの場合
与えられたデータのうち、2つないし3つ程度の項目に着目してデータの分析や集計を行なうこと。 1つ(ないし2つ)の項目を縦軸に、もう1つの項目を横軸において表を作成して集計を行なう。 Excelの場合 ピボットテーブルの機能がこれに該当 Pivot : 回転軸 詳しいことはヘルプ参照

9 ピボットテーブル(1/7) サンプル(資料002) 「挿入」→「ピボットテーブル」

10 ピボットテーブル(2/7) フィールドを 行列ラベル、Σ値にドラッグ＆ドロップ

11 ピボットテーブル(3/7) 行と列の交差部分の条件で集計される 条件に合致する値の数や値の合計等

12 ピボットテーブル(4/7) Σ値の集計方法を変えたい場合 Σ値をクリックして「値フィールドの設定」

13 ピボットテーブル(5/7) 表示順を変えたい場合は ラベルを選択して枠線上でドラッグ

14 ピボットテーブル(6/7) 元データを更新した場合 自動で変更されない 手動で「更新」が必要 「ピボットテーブルツール」→「オプション」から

15 ピボットテーブル(7/7) その他デザインの変更等も簡単にできる

16 ソート順序のカスタマイズ(1/6) ソート 標準では「可秀不優良」の順(文字コード順) 評価の優良順に自動で並んでほしい

17 ソート順序のカスタマイズ(2/6) 「ファイル」→「オプション」→「詳細設定」 →「全般」→「ユーザー定義リストの編集」

18 ソート順序のカスタマイズ(3/6) 「新しいリスト」に 「不,可,良,優,秀」のように昇順に カンマ区切りでリスト項目を「追加」
改行で区切っても良い

19 ソート順序のカスタマイズ(4/6) ソートしてみると 定義した並び順でソートされる

20 ソート順序のカスタマイズ(5/6) もし上手く並ばない時は 並べ替え規則を手動で設定する 「その他の並べ替えオプション」
→「その他のオプション」

21 ソート順序のカスタマイズ(6/6) 自動並べ替えのチェック外して 並べ替え規則を設定 ここまでの結果(資料003)

22 ピボットグラフ ピボットテーブルと同じ操作でグラフも付く 結果サンプル(資料004) 「ピボットグラフツール」→「デザイン」
→「グラフの種類変更」から 「100%積み上げ縦棒」に変更しています

23 他のアプリとのデータ交換 テキストのみのファイル形式を用いる 「名前を付けて保存」で 読み込みは後述
CSV – Comma Separated Value TSV – Tab Separated Value 「名前を付けて保存」で 「ファイルの種類」選ぶと保存できる 読み込みは後述 Excel 自作 プログラム CSV or TSV アドレス帳 その他 市販ソフト等 年賀状 作成ソフト

24 CSV カンマで列、改行で行を区切った形式

25 TSV タブで列、改行で行を区切った形式

26 アドレス帳 Windows7だとユーザ名のフォルダにある ユーザ名の フォルダを開く

27 連絡先 メールアドレスのほか住所等が記録できる 新規作成は「新規作成」→「連絡先」

28 アドレス帳のエクスポート(1/5) 「エクスポート」でCSVに書き出せる

29 アドレス帳のエクスポート(2/5) CSV選んでエクスポート エクスポート先「参照」

30 アドレス帳のエクスポート(3/5) デスクトップにファイル名「住所録」で

31 アドレス帳のエクスポート(4/5) エクスポートするフィールドを選択 選択したら「完了」 名 姓 自宅の番地 自宅の市区町村 自宅の郵便番号
自宅の都道府県 選択したら「完了」

32 アドレス帳のエクスポート(5/5) 保存したCSVは他のアプリと相互に利用可能 エクスポート結果のサンプル(資料101)
例えば「筆まめ」「筆王」等の年賀状作成ソフト 携帯電話のアドレス帳管理ソフト 等々 エクスポート結果のサンプル(資料101)

33 CSVをExcelで開く テキストファイルウィザード(1/5)
「Officeボタン」→「開く」で「すべてのファイル」

34 CSVをExcelで開く テキストファイルウィザード(2/5)
文字コードを適切に(通常は自動識別) 文字化けしてたら、適切なコードに JIS,シフトJIS,UTF-8等 ここが文字化け してないか確認

35 CSVをExcelで開く テキストファイルウィザード(3/5)
区切り文字を適切に プレビューが縦線で区切られるよう TSVかCSVかによって 区切り文字を適切に選択

36 CSVをExcelで開く テキストファイルウィザード(4/5)
必要なら、各列にデータ形式を設定 主には文字列とか日付とか 学籍番号のように ゼロで始まる数字列を 文字列として扱いたい場合等は 適切に列のデータ形式を設定

37 CSVをExcelで開く テキストファイルウィザード(5/5)
正常に読めた場合 文字コードが合ってない場合とか ダブルクリック、ドラッグ＆ドロップの場合なるかも

38 差し込み印刷(1/15) Wordの機能 Excel等で作成したデータを定型に流しこめる 文面の一部だけが異なる文書の作成に便利
宛名、あいさつ文(○○様)等 文面の一部だけが異なる文書の作成に便利 ひな形 <<顧客名>>様へ 　この度は弊社の製品<<購入製品>>をお買い上げいただきありがとうございました。・・・・ 佐藤様へ 　この度は弊社の製品○△□をお買い上げいただきありがとうございました。ご不明な点や不具合等ございましたらお気軽に弊社サポートセンターまでご連絡くださいませ。 ひな形へ データ埋め込み 鈴木様へ 　この度は弊社の製品×○△をお買い上げいただきありがとうございました。ご不明な点や不具合等ございましたらお気軽に弊社サポートセンターまでご連絡くださいませ。 データ ＋ 高橋様へ 　この度は弊社の製品○△□をお買い上げいただきありがとうございました。ご不明な点や不具合等ございましたらお気軽に弊社サポートセンターまでご連絡くださいませ。 顧客名 住所 購入製品 佐藤 ○○県○○市 ○△□ 鈴木 ××県××市 ×○△ ： ・・・ 　・・・ 　　・・・

39 差し込み印刷(2/15) 例: はがきの宛名 一から自分で作る場合や足らない項目 「差し込み印刷」→「はがきの印刷」
はがきの場合は、ウイザード形式になってる 一から自分で作る場合や足らない項目 差し込みフィールドを追加して埋めて行く

40 差し込み印刷(3/15) ウイザード形式の場合 質問に答えながらどんどん「次へ」 まずはがきの種類

41 差し込み印刷(4/15) 宛名のフォント 宛名の縦書き、横書き

42 差し込み印刷(5/15) 差出人の住所を作成

43 差し込み印刷(6/15) 「既存の住所録ファイル」→「参照」 「デスクトップ」→「すべてのファイル」
住所録のファイル(CSV, TSVやXLSX)を開く

44 差し込み印刷(7/15) とりあえず設定は完了

45 差し込み印刷(8/15) とりあえず出来たが 宛名の住所が出てない

46 差し込み印刷(9/15) 「差し込み印刷」→「結果のプレビュー」OFFに 差し込みフィールドが見える

47 差し込み印刷(10/15) 「フィールドの対応」で対応付けを行う 住所1 → 自宅の番地 郵便番号 → 自宅の郵便番号

48 差し込み印刷(11/15) 足らないフィールドをカーソル位置へ挿入 「差し込みフィールドの挿入」から 自宅の都道府県 自宅の市区町村

49 差し込み印刷(12/15) 「結果のプレビュー」ONに戻す

50 差し込み印刷(13/15) ページを送って、他の人の結果も確認 問題なければ印刷 ・・・・・・

51 差し込み印刷(14/15) 印刷が不要な人は 「アドレス帳の編集」からチェック外す

52 差し込み印刷(15/15) 全く新規にひな形を起こしたい場合は 「差し込み印刷の開始」 →「差し込み印刷ウイザード」から
基本は同じなので詳細はヘルプ参照

53 マクロ機能 強力な自動化機能 とても便利だけど 操作を記録、再生できる 記録内容はVBAになってる
VBA(Visual Basic for Application) プログラミング言語 プログラミングによる自動操作が可能 関数を自分で作ったりもできる とても便利だけど プログラミングの授業は半期くらい必要 詳しいことは書店でVBAの参考書を

54 マクロの記録(1/4) 記録開始したいセルに移動 「表示」→「マクロ」→「マクロの記録」 適当にマクロ名を付けて「OK」
資料201:サンプルデータ マクロの記録(1/4) 記録開始したいセルに移動 「表示」→「マクロ」→「マクロの記録」 適当にマクロ名を付けて「OK」

55 マクロの記録(2/4) 必要に応じて ショートカットキー割り当て 相対参照で記録

56 マクロの記録(3/4) マクロ割当可能なキー(CTRL+～) 灰: 標準で割り当て済みのキー
黒: 標準で未割当のキー(未割当だとベルが鳴る) 1 2 3 4 5 6 7 8 9 A B C D E F 40 G H I J K L M N O 50 P Q R S T U V W X Y Z 70 a b c d e f g h i j k l m n o 80 p q r s t u v w x y z

57 マクロの記録(4/4) 適当に一連の操作を行う 「表示」→「マクロ」→「記録終了」

58 マクロの表示 実行 記録した操作の再生 編集 記録した操作の編集 オプション キー割り当ての変更

59 マクロの編集 記録した操作の編集が出来る マクロを手書きする際の参考にもなる

60 マクロ記録の例 隣接列の入れ替え 行う操作(カッコ書きは相対位置) 記録する操作の手順 C列(初期列)とD列(1つ右の列)の入れ替え
資料202: Macro1 マクロ記録の例 隣接列の入れ替え 行う操作(カッコ書きは相対位置) C列(初期列)とD列(1つ右の列)の入れ替え 記録する操作の手順 初期状態: C1を選択 「マクロ記録開始」 D列(1つ右の列)の見出しを左クリックし選択 「カット」 C列(１つ左の列)の見出しで左クリックし選択 「切り取ったセルの挿入」 「マクロ記録終了」

61 マクロ記録結果 隣接列の入れ替え(絶対参照)
資料202: Macro1 マクロ記録結果 隣接列の入れ替え(絶対参照) Sub Macro1() ' Macro1 Macro Columns("D:D").Select Selection.Cut Columns("C:C").Select Selection.Insert Shift:=xlToRight End Sub Sub 関数名()で定義開始 ' で始まるとコメント D列～D列までを選択 選択範囲(Selection)をカット C列からC列までを選択 選択範囲に挿入 以前にあった内容は右にずらす End Subで定義終了

62 マクロ記録結果 隣接列の入れ替え(相対参照)
資料202: Macro2 マクロ記録結果 隣接列の入れ替え(相対参照) アクティブセルを 1列右へ移動 アクティブセルと 同列～同列までを選択 Sub Macro2() ' Macro2 Macro ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select Selection.Cut ActiveCell.Offset(0, -1).Columns("A:A").EntireColumn.Select Selection.Insert Shift:=xlToRight End Sub 選択範囲(Selection)をカット アクティブセルと 同列～同列までを選択 選択範囲に挿入 以前にあった内容は右にずらす アクティブセルを 1列左へ移動

63 マクロ記録の例 2行を1行にまとめる 行う操作(カッコ書きは相対位置) 記録する操作の手順(相対参照で記録) 2行を1行にまとめる
資料202: Macro3 マクロ記録の例 2行を1行にまとめる 行う操作(カッコ書きは相対位置) 2行を1行にまとめる 記録する操作の手順(相対参照で記録) 「マクロ記録開始」 Ctrl+← 左端へ移動 ↓ 1行下へ移動 Shift+Ctrl+→ 行末までを選択 Ctrl+x カット ↑ 1行上へ移動 Ctrl+→ 行末へ移動 → 次の列(空白状態)へ移動 Ctrl+v 貼り付け Ctrl+← 行頭へ移動 Menu+d r Enter 行全体の削除 「マクロ記録終了」 +D Menuキー

64 資料202: Macro3 マクロ記録結果 2行を1行にまとめる Sub Macro3() ' Macro3 Macro Selection.End(xlToLeft).Select ' Ctrl+← ActiveCell.Offset(1, 0).Range(“A1”).Select ' ↓ Range(Selection, Selection.End(xlToRight)).Select ' Shift+Ctrl+→ Selection.Cut ' Ctrl+x ActiveCell.Offset(-1, 0).Range(“A1”).Select ' ↑ Selection.End(xlToRight).Select ' Ctrl+→ ActiveCell.Offset(0, 1).Range(“A1”).Select ' → ActiveSheet.Paste ' Ctrl+v Selection.EntireRow.Delete ' Menu+d r Enter End Sub

65 ユーザ定義関数 マクロの編集画面に記述する 関数名に代入すると戻り値になる

66 ユーザ定義関数の例 加算関数 Function myAdd(x, y) myAdd = x + y End Function
戻り値は関数名に代入 End Functionで関数定義終了 資料202: 加算関数の例

67 ユーザ定義関数の例 うるう年判定関数 Function isLeapYear(year)
isLeapYear = (year Mod 4 = 0 And year Mod 100 <> 0) Or year Mod 400 = 0 End Function Mod : 剰余演算子 X Mod Y で Excel の MOD(X, Y) と同義 And 演算子 X And Y で Excel の AND(X, Y) と同義 Or 演算子 X Or Y で Excel の OR(X, Y) と同義 資料202: うるう年判定関数の例

68 マクロの危険性 ウィルスが仕込まれている可能性も 信頼できないファイル マクロを有効にしてはいけない マクロが含まれているが
安全性が確認できないため 許可しないと有効にならない