情報リテラシー演習
第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 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)
• ソート(sort: 並べ替え) – 標準では「可秀不優良」の順(文字コード順) – 評価のスコア順に自動で並んでほしい 「↑」は昇順 「↓」は降順の意味ソート順序のカスタマイズ(2/6)
• 「ファイル」→「オプション」→「詳細設定」 →「全般」→「ユーザー定義リストの編集」
ソート順序のカスタマイズ(3/6)
• 「新しいリスト」の「リストの項目」へ
– 「不,可,良,優,秀」のように昇順で「追加」 – リスト項目はカンマまたは改行で区切る
ソート順序のカスタマイズ(4/6)
ソート順序のカスタマイズ(5/6)
• もし上手く並ばない時は
• 並べ替え規則を手動で設定する • 「その他の並べ替えオプション」
ソート順序のカスタマイズ(6/6)
• 自動並べ替えのチェック外して • 並べ替え規則を設定
他のアプリとのデータ交換
• テキストのみのファイル形式を用いる
– CSV – Comma Separated Value – TSV – Tab Separated Value
• 「名前を付けて保存」で – 「ファイルの種類」選ぶと保存できる • 読み込みは後述 CSV or TSV Excel アドレス帳 年賀状 作成ソフト その他 市販ソフト等 自作 プログラム
CSV
TSV
アドレス帳
• Windows7だとユーザ名のフォルダにある
ユーザ名の フォルダを開く
アドレス帳
• explorer.exe で shell:contacts を開く explorer.exeのアドレス欄 「田+R」から 「ファイルを指定して実行」 「shell:contacts」と入力連絡先
• メールアドレスのほか住所等が記録できる
アドレス帳のエクスポート(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)
• 足らないフィールドをカーソル位置へ挿入 – 「差し込みフィールドの挿入」から • 自宅の都道府県 • 自宅の市区町村 a差し込み印刷(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列(1つ左の列)の見出しで左クリックし選択 • 「切り取ったセルの挿入」 – 「マクロ記録終了」 資料202: Macro1マクロ記録結果
隣接列の入れ替え(絶対参照)
Sub Macro1() ' Macro1 Macro Columns("D:D").Select Selection.Cut Columns("C:C").SelectSelection.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
マクロ記録の例
1行を2行に分割する
• 行う操作(カッコ書きは相対位置) – 2行を1行にまとめる • 記録する操作の手順(相対参照で記録) – 「マクロ記録開始」 • Ctrl+← 左端へ移動 • ↓ 1行下へ移動• Menu i Enter r Enter 1行挿入
• ↑→→→→→ 1行上5マス右へ移動 • Shift+Ctrl+→ 行末へ移動 • Ctrl+x 切り取り • Ctrl+← 左端へ移動 • ↓ 1行下へ移動 • Ctrl+v 貼り付け • ↓ 1行下へ移動 – 「マクロ記録終了」 Menuキー +i 資料202: Macro4
マクロ記録結果
1行を2行に分割する
Sub Macro4()
' Macro4 Macro
Selection.End(xlToLeft).Select ' Ctrl+←
ActiveCell.Offset(1, 0).Range("A1").Select ' ↓
Selection.EntireRow.Insert , _
CopyOrigin:=xlFormatFromLeftOrAbove ' Menu i Enter r Enter
ActiveCell.Offset(-1, 5).Range("A1").Select ' ↑→→→→→
Range(Selection, Selection.End(xlToRight)).Select ' Shift+Ctrl+→
Selection.Cut ' Ctrl+x
Selection.End(xlToLeft).Select ' Ctrl+←
ActiveCell.Offset(1, 0).Range("A1").Select ' ↓
ActiveSheet.Paste ' Ctrl+v
ActiveCell.Offset(1, 0).Range("A1").Select ' ↓
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) と同義