情報リテラシー演習

情報リテラシー演習

第6週

Excelの使い方

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

データの入力規則(1/5)

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

データの入力規則(2/5)

• 規則を設定したい範囲を選択

データの入力規則(3/5)

• 別のワークシートから取ってくる場合 – 入力値の種類 • リスト – 元の値 • =性別!A:A

データの入力規則(4/5)

• 直接入力設定する場合 – 入力値の種類 • リスト – 元の値 • 男,女 • 列挙 – 半角カンマで

データの入力規則(5/5)

• 入力欄にプルダウンボタン

– リストから値を選択可能に – キーボード入力の時は

クロス集計

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

クロス集計(ピボットテーブル)の例

• 着目するデータの軸 – 軸1: 性別 → 行に配置 – 軸2: 評価 → 列に配置 出席番号 名前 性別 中間 期末 評点 合否 評価 1 安井兎絵男 男 42 69 55.5 否 不 2 樺木丘佳子 女 81 79 80.0 合 優 3 砂司須瀬素 女 72 86 79.0 合 良 4 多智津照人 男 83 82 82.5 合 優 5 菜似縫音之 女 60 71 65.5 合 可 6 羽檜冨配歩 女 88 87 87.5 合 優 7 舞魅巫苺萌 女 100 92 96.0 合 秀 8 矢射弓獲誉 女 99 98 98.5 合 秀 9 良里瑠例呂 男 77 89 83.0 合 優 10 倭夷宇衛ヲ 男 71 91 81.0 合 優 データの個数 / 評価 列ラベル 行ラベル 不 可 良 優 秀 総計 女 1 1 2 2 6 男 1 3 4 総計 1 1 1 5 2 10 それぞれの軸の 対応する項目を集計

ピボットテーブル(1/7)

• サンプル(資料002)

ピボットテーブル(2/7)

• フィールドを

ピボットテーブル(3/7)

• 行と列の交差部分の条件で集計される

ピボットテーブル(4/7)

• Σ値の集計方法を変えたい場合

ピボットテーブル(5/7)

• 表示順を変えたい場合は

ピボットテーブル(6/7)

• 元データを更新した場合

– 自動で変更されない – 手動で「更新」が必要

ピボットテーブル(7/7)

ソート順序のカスタマイズ(1/6)

• ソート

– 標準では「可秀不優良」の順(文字コード順) – 評価の優良順に自動で並んでほしい

ソート順序のカスタマイズ(2/6)

• 「ファイル」→「オプション」→「詳細設定」 →「全般」→「ユーザー定義リストの編集」

ソート順序のカスタマイズ(3/6)

• 「新しいリスト」に

– 「不,可,良,優,秀」のように昇順に – カンマ区切りでリスト項目を「追加」 – 改行で区切っても良い

ソート順序のカスタマイズ(4/6)

• ソートしてみると

ソート順序のカスタマイズ(5/6)

• もし上手く並ばない時は

– 並べ替え規則を手動で設定する – 「その他の並べ替えオプション」 – →「その他のオプション」

ソート順序のカスタマイズ(6/6)

• 自動並べ替えのチェック外して • 並べ替え規則を設定

ピボットグラフ

• ピボットテーブルと同じ操作でグラフも付く

– 結果サンプル(資料004) 「ピボットグラフツール」→「デザイン」

→「グラフの種類変更」から

他のアプリとのデータ交換

• テキストのみのファイル形式を用いる

– CSV – Comma Separated Value – TSV – Tab Separated Value

• 「名前を付けて保存」で – 「ファイルの種類」選ぶと保存できる • 読み込みは後述 CSV or TSV Excel アドレス帳 年賀状 作成ソフト その他 市販ソフト等 自作 プログラム

CSV

TSV

アドレス帳

• Windows7だとユーザ名のフォルダにある

ユーザ名の フォルダを開く

連絡先

• メールアドレスのほか住所等が記録できる

アドレス帳のエクスポート(1/5)

アドレス帳のエクスポート(2/5)

• CSV選んでエクスポート

アドレス帳のエクスポート(3/5)

アドレス帳のエクスポート(4/5)

• エクスポートするフィールドを選択 – 名 – 姓 – 自宅の番地 – 自宅の市区町村 – 自宅の郵便番号 – 自宅の都道府県 • 選択したら「完了」

アドレス帳のエクスポート(5/5)

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

CSVをExcelで開く

テキストファイルウィザード(1/5)

CSVをExcelで開く

テキストファイルウィザード(2/5)

• 文字コードを適切に(通常は自動識別) – 文字化けしてたら、適切なコードに – JIS,シフトJIS,UTF-8等 ここが文字化け してないか確認

CSVをExcelで開く

テキストファイルウィザード(3/5)

• 区切り文字を適切に – プレビューが縦線で区切られるよう TSVかCSVかによって 区切り文字を適切に選択

CSVをExcelで開く

テキストファイルウィザード(4/5)

• 必要なら、各列にデータ形式を設定 – 主には文字列とか日付とか 学籍番号のように ゼロで始まる数字列を 文字列として扱いたい場合等は 適切に列のデータ形式を設定

CSVをExcelで開く

テキストファイルウィザード(5/5)

• 正常に読めた場合

• 文字コードが合ってない場合とか

差し込み印刷(1/15)

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

差し込み印刷(2/15)

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

差し込み印刷(3/15)

• ウイザード形式の場合

– 質問に答えながらどんどん「次へ」

差し込み印刷(4/15)

• 宛名のフォント

差し込み印刷(5/15)

差し込み印刷(6/15)

• 「既存の住所録ファイル」→「参照」 • 「デスクトップ」→「すべてのファイル」

差し込み印刷(7/15)

差し込み印刷(8/15)

• とりあえず出来たが

差し込み印刷(9/15)

• 「差し込み印刷」→「結果のプレビュー」OFFに

差し込み印刷(10/15)

• 「フィールドの対応」で対応付けを行う

– 住所1 → 自宅の番地

差し込み印刷(11/15)

• 足らないフィールドをカーソル位置へ挿入

– 「差し込みフィールドの挿入」から

• 自宅の都道府県 • 自宅の市区町村

差し込み印刷(12/15)

差し込み印刷(13/15)

• ページを送って、他の人の結果も確認 • 問題なければ印刷

差し込み印刷(14/15)

• 印刷が不要な人は

差し込み印刷(15/15)

• 全く新規にひな形を起こしたい場合は

– 「差し込み印刷の開始」

– →「差し込み印刷ウイザード」から

マクロ機能

• 強力な自動化機能

– 操作を記録、再生できる – 記録内容はVBAになってる

– VBA(Visual Basic for Application)

• プログラミング言語 – プログラミングによる自動操作が可能 – 関数を自分で作ったりもできる • とても便利だけど – プログラミングの授業は半期くらい必要 – 詳しいことは書店でVBAの参考書を

マクロの記録(1/4)

• 記録開始したいセルに移動

• 「表示」→「マクロ」→「マクロの記録」 • 適当にマクロ名を付けて「OK」

マクロの記録(2/4)

• 必要に応じて

– ショートカットキー割り当て – 相対参照で記録

マクロの記録(3/4)

• マクロ割当可能なキー(CTRL+～) – 灰: 標準で割り当て済みのキー – 黒: 標準で未割当のキー(未割当だとベルが鳴る) 0 1 2 3 4 5 6 7 8 9 A B C D E F 40 A B C D E F 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

マクロの記録(4/4)

• 適当に一連の操作を行う

マクロの表示

• 実行 – 記録した操作の再生 • 編集 – 記録した操作の編集 • オプション – キー割り当ての変更

マクロの編集

• 記録した操作の編集が出来る

マクロ記録の例

隣接列の入れ替え

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

マクロ記録結果

隣接列の入れ替え(絶対参照)

Sub Macro1() ' Macro1 Macro Columns("D:D").Select Selection.Cut Columns("C:C").Select

Selection.Insert Shift:=xlToRight

End Sub D列～D列までを選択 選択範囲(Selection)をカット C列からC列までを選択 選択範囲に挿入 以前にあった内容は右にずらす Sub 関数名()で定義開始 End Subで定義終了 ' で始まるとコメント 資料202: Macro1

マクロ記録結果

隣接列の入れ替え(相対参照)

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 アクティブセルを 1列右へ移動 アクティブセルと 同列～同列までを選択 選択範囲に挿入 以前にあった内容は右にずらす 選択範囲(Selection)をカット アクティブセルを 1列左へ移動 アクティブセルと 同列～同列までを選択 資料202: Macro2

マクロ記録の例

2行を1行にまとめる

• 行う操作(カッコ書きは相対位置) – 2行を1行にまとめる • 記録する操作の手順(相対参照で記録) – 「マクロ記録開始」 • Ctrl+← 左端へ移動 • ↓ 1行下へ移動 • Shift+Ctrl+→ 行末までを選択 • Ctrl+x カット • ↑ 1行上へ移動 • Ctrl+→ 行末へ移動 • → 次の列(空白状態)へ移動 • Ctrl+v 貼り付け • Ctrl+← 行頭へ移動 • ↓ 1行下へ移動 • Menu+d r Enter 行全体の削除 – 「マクロ記録終了」 Menuキー +D 資料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.End(xlToLeft).Select ' Ctrl+← ActiveCell.Offset(1, 0).Range(“A1”).Select ' ↓

Selection.EntireRow.Delete ' Menu+d r Enter

End Sub

ユーザ定義関数

• マクロの編集画面に記述する

ユーザ定義関数の例

加算関数

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

ユーザ定義関数の例

うるう年判定関数

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) と同義

マクロの危険性

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