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

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

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

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

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

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

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

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

ピボットテーブル(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) 自動並べ替えのチェック外して 並べ替え規則を設定 ここまでの結果(資料003)

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

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

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

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

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

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

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

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

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

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

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

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

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) 「既存の住所録ファイル」→「参照」 「デスクトップ」→「すべてのファイル」 住所録のファイル(CSV, TSVやXLSX)を開く

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

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

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

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

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

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

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

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

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

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

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

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

マクロの記録(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

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

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

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

マクロ記録の例 隣接列の入れ替え 行う操作(カッコ書きは相対位置) 記録する操作の手順 C列(初期列)とD列(1つ右の列)の入れ替え 資料202: Macro1 マクロ記録の例 隣接列の入れ替え 行う操作(カッコ書きは相対位置) 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 Sub 関数名()で定義開始 ' で始まるとコメント D列～D列までを選択 選択範囲(Selection)をカット C列からC列までを選択 選択範囲に挿入 以前にあった内容は右にずらす End Subで定義終了

マクロ記録結果 隣接列の入れ替え(相対参照) 資料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列左へ移動

マクロ記録の例 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キー

資料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

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

ユーザ定義関数の例 加算関数 Function myAdd(x, y) myAdd = x + y End 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) と同義 資料202: うるう年判定関数の例

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