情報リテラシー演習
第5,6週
Excelの使い方
• 今週、来週 • 表計算ソフトの使い方 • Word 同様 • 単なる機能は触って試 せば分かるはず • 解説書等を読めばもっ と分かるはずExcel
• 表計算(Spread Sheet) • 計算結果の自動更新 • グラフの作成 • 作表も得意 • でも本来の目的とは少し違うかも? 「資料004」 「資料003」Excel2016の画面構成
• Excel特有の部分 アクティブセル 行見出し 全選択 ボタン アクティブセル名 A1ならA列の1行目 注目セル内容 関数ボタン 数式 バー ワークシート選択タブ ブック名(=Excelのファイル名) 列見出し ワークシート作成ボタン基本操作
• 移動(以下のいずれか) • セルをクリック • カーソルキーまたは以下の操作 • 編集(以下のいずれか) • セルをダブルクリック • F2 Shift+Enter Enter Tab Shift+Tab F2 キー入力等 Enter, Tab 等セルの選択(1/2)
• 単一セル • セルをクリック • 複数セル • セルをドラッグ • 単一行または列 • 見出しをクリック • 複数行または列 • 見出しをドラッグセルの選択(2/2)
• 追加選択
入力
• 数値(標準で右揃え) • 数字のみで構成される • 「123」とか • 文字列(標準で左揃え) • 数字以外で始まる • 「abcd」とか • 「あいうえお」とか • 「候補1」とか • 表示形式が「文字列」 • 「'」で始まる • 「'123」とか シングルクォーテーション 数値だと消える 文字列なら 先頭の0が消えない入力
• 数式(自動的に計算してくれる) • 「=」ではじめる • 計算 • 「=1+2-3*4/5」とか • セルの参照 • 「=A1+B1」とか • 配列数式 • 数式入力時に Ctrl + Shift + Enter • { } で数式が囲まれる この例は関数を用いた 行列の転置演算子の優先順位
• かっこ • 「(」~「)」 • 参照演算子 • 「:」 (コロン) • 「 」(スペース1つ) • 「,」(カンマ) • 負の値 • 「-」 (マイナス) • パーセンテージ • 「%」 • べき乗 • 「^」 • 乗除算 • 「*」、「/」 • 加減算 • 「+」、「-」 • 文字列の結合、連結 • 「&」 • 比較演算子 • 「=」、 「<>」、 「<」、 「>」、 「<=」、 「>=」 • 「数式の概要」を参照 優先順位高 優先順位低参照の書式(同一シート)
• セル番地 • 列と行の見出しを続けて読む • A1・・・A列の1行目 • B2・・・B列の2行目 • 例 • =A1 セル番地 数式の一部として入力参照の書式(同一ブックの他のシート)
• 構成要素 • ワークシート名 • セル番地 • 例 • =Sheet2!A1 ワークシート名 セル番地 シート名とセル番地の間は ! で区切る 数式の一部として入力参照の書式(開いている他のブック)
• 構成要素 • ブックのファイル名 • ワークシート名、セル番地 • 例 • =[サンプル.xlsx]Sheet1!A1 ブックのファイル名 拡張子必要 ワークシート名 セル番地 数式の一部として入力 ブックのファイル名は [~] で囲む シート名とセル番地の 間は! で区切る参照の書式(閉じている他のブック)
• 構成要素 • ブックのパス、ブックのファイル名、 • ワークシート名、セル番地 • 例 • ='C:¥Users¥YU¥Desktop¥[サンプル.xlsx]Sheet1'!A1 ブックの絶対パス ブックのファイル名 拡張子必要 ワークシート名 セル番地 数式の一部として入力 パス名、ファイル名、シート名は '~' (シングルクォーテーション) で囲む ブックのファイル名は [~] で囲む シート名とセル番地の 間は! で区切る サンプル.xlsxファイルのある場所を記述する
パス(PATH)とは?
• ファイルの位置関係の表記方法 • ドライブ、フォルダ、ファイルの入れ子構造を 「¥」(=パス区切り)で繋げて書き表す • 例: 「C:¥ユーザー¥kou¥デスクトップ¥掛算九九.xlsx」 パスの root (= 根っ子) を起点に 書き表すのが絶対パス Windows の場合、 パスの root はドライブ名パス(PATH)とは?
• ファイルの位置関係の表記方法 • ドライブ、フォルダ、ファイルの入れ子構造を 「¥」(=パス区切り)で繋げて書き表す • 例: 「C:¥ユーザー¥kou¥デスクトップ¥掛算九九.xlsx」 パスの root (= 根っ子) を起点に 書き表すのが絶対パス Windows の場合、 パスの root はドライブ名作業フォルダと相対パス
• 作業フォルダ C:¥ユーザー¥kou¥デスクトップ 「.」は現在のフォルダ 「..」は親フォルダ を意味する このフォルダ内で 作業している ..¥マイピクチャ¥sample.png 作業フォルダを起点に 書き表すのが相対パス作業フォルダと相対パス
• 作業フォルダ C:¥ユーザー¥kou¥デスクトップ 「.」は現在のフォルダ 「..」は親フォルダ を意味する このフォルダ内で 作業している ..¥マイピクチャ¥sample.png 作業フォルダを起点に 書き表すのが相対パス同じ意味のパス
• 相対関係が同じなら同じパス • 例えば作業フォルダが 「C:¥ユーザー¥kou¥デスクトップ」の時 以下のようなパスはすべて同じ意味になる • C:¥ユーザー¥kou¥マイピクチャ¥sample.png • ..¥マイピクチャ¥sample.png • .¥..¥マイピクチャ¥sample.png • ..¥マイピクチャ¥.¥sample.png • ..¥デスクトップ¥..¥マイピクチャ¥sample.png相対参照
• 記入したセルとの相対位置を意味する • A1 のように列名・行番号を並べる A B 1 2 =A1 B列に対する A列は 1つ左の列 2行目に対する 1行目は 1つ上の行絶対参照
• ワークシート原点からの絶対位置を意味する • $A$1 のように列名・行番号の前に $ を付ける A B 1 2 =$A$1 $A はシートの1列目 $1 は シートの1行目コピー&ペースト時の位置関係
相対参照では 数式セルと 参照セルの位置関係が 維持されている。 絶対参照では ワークシートと 参照セルの位置関係が 維持されている。相対参照と絶対参照(1/4)
• 相対参照をコピー&ペーストまたはオートフィ ルすると
相対参照と絶対参照(2/4)
• 「$」を付けると絶対参照
• 行のみに付けると、行が固定される
相対参照と絶対参照(3/4)
• 「$」を付けると絶対参照
• 列のみに付けると、列が固定される
相対参照と絶対参照(4/4)
• 「$」を付けると絶対参照
• 行列ともに付けると、完全に固定される
絶対~と相対~
• 何かを参照する際にどこを起点とするか? • 絶対パス、絶対参照 • 原点を起点にして考える • パスのルート(root: 根っ子) • ワークシートの原点(左上) • 相対パス、相対参照 • 注目している物を起点にして考える • 作業フォルダ • アクティブセルオートフィル
オートフィル(1/2)
• 連続値の自動生成 • セルの右下 • フィルハンドル • ドラッグ • アイコンから • メニュー出して • 連続データオートフィル(2/2)
• 連続値を選択して • フィルハンドル • ドラッグ • 繰り返しは • セルのコピー掛算九九
掛算九九の表(1/13)
• 行の値を縦にオートフィルして作成(A2~A10)
掛算九九の表(2/13)
• B2セルに値を参照した数式「=A2*B1」を入力
掛算九九の表(3/13)
• 縦にオートフィルしてみると
• 相対参照のため参照先が自動的にずれる • リボンから「数式」→「数式の表示」で確認
掛算九九の表(4/13)
• A列と1行は固定したいから「$」で絶対参照
• B2セルに「=$A2*B$1」と入力
掛算九九の表(5/13)
• そのまま横にオートフィルすると
掛算九九の表(6/13)
• 列見出しをA~Jまでドラッグして選択 • 右クリックでポップアップメニューを出し • 「列の幅」→「4」と入力
掛算九九の表(7/13)
• 九九全体を選択し「ホーム」→「罫線」
• 「格子」、「太い外枠」の順に適用
1 2
掛算九九の表(8/13)
• 見出し部分にも「太い外枠」を適用
• ワークシート名をダブルクリックし
掛算九九の表(9/13)
• 不要なシート(Sheet2, Sheet3)がある場合
掛算九九の表(10/13)
• ファイルタブから「印刷」でプレビュー プレービュー中の ページ番号 プレービューの 拡縮掛算九九の表(11/13)
• 「ページ設定」から「ヘッダ/フッタ」
掛算九九の表(12/13)
• 「ページ設定」→「余白」で余白の調整
掛算九九の表(13/13)
• 「印刷プレビューを閉じる」 • 「名前を付けて保存」 • 「デスクトップ」へ • 「掛算九九.xlsx」 以上、「資料001」を参照ExcelからWordへの貼り付け
• Excelで「コピー」、Wordで「貼り付け」 ドラッグで 範囲選択してから コピー 「形式を選択して貼り付け」 した場合貼り付け方法
• 貼り付け • 元ファイルとは別の複製 • リンク貼り付け • 元ファイルを参照(変更 に同期) 元の書式を保持 貼り付け先のスタイルを使用 リンク(元の書式を保持) リンク(貼り付け先のスタイルを使用) 図 テキストのみ保持OLE (Object Linking and Embedding)
• 異なるアプリケーション間でオブジェクトのリンクや埋め込みを行う仕組み
• 「Microsoft Excel ワークシート オブジェクト」形式の場合は この仕組みを用いて貼り付けが行われている
• Wikipedia / Object Linking and Embedding
• http://ja.wikipedia.org/wiki/Object_Linking_and_Embedding • MFC の OLE
「Microsoft Excel ワークシート オブジェ
クト」形式の貼り付け(1/4)
• Excelのブック(ファイル)をWordへDrag&Drop
• コピーが挿入される
「Microsoft Excel ワークシート オブジェ
クト」形式の貼り付け(2/4)
• 別の方法 • 「貼り付け」→「形式を選択して貼り付け」 →「Microsoft Excel ワークシート オブジェクト」 ドラッグで 範囲選択してから コピー「Microsoft Excel ワークシート オブジェ
クト」形式の貼り付け(3/4)
• ワークシート オブジェクトとして挿入した場合 • ダブルクリックで編集可能 • 「リンク貼り付け」だと元ファイルが編集される リボンが Excelのものに 適当にワークシート外を クリックすると元に戻る 表をダブルクリック「Microsoft Excel ワークシート オブジェ
クト」形式の貼り付け(4/4)
• 「ワークシート オブジェクト」の編集と開く 「編集」だと Word 内で操作 「開く」だと 元のアプリで操作 右クリックグラフの作成
関数の使い方(1/2)
• 「関数の挿入」ボタン • 使いたい関数探して「OK」 キーワードで 関数を検索 関数選ぶと 簡単な説明が出る 詳細は ヘルプを参照 OKで関数挿入 分類で 関数を絞り込み関数の使い方(2/2)
• 挿入すると引数設定のダイアログ出る
• コロンで範囲指定
• 「A1:A10」ならA1~A10までという意味 • 関数を知っているなら手書きしても良い
良く使う関数?
• 合計: SUM, SUMIF • 平均: AVERAGE • 分散: VAR • 標準偏差: STDEV • セル数: COUNT • 最大値: MAX • 最小値: MIN • 数学関係• PI, RADIANS, DEGREES • SIN, COS, TAN
• ASIN, ACOS • ATAN, ATAN2 • POWER, SQRT,
• EXP, LN, LOG, LOG10 • MOD
グラフ用データの作成
• SIN関数を描いてみる • A1:A17にオートフィルで0~15の値を作る • B2セルを「=SIN(A2)」にして縦にオートフィル • 系列名として • A1に「rad」 • B1に「sin」グラフの作成
• A1:B17を選択した状態で
• 「挿入」→「グラフの挿入」
グラフの設定
• 枠、軸、プロット、凡例等
• 右クリックからメニュー出して各種設定
• 書式、目盛、ラベル、近似曲線等
表やグラフをWordへ貼り付け
• Excel上で表やグラフを選択し「コピー」 • Word上で「貼り付け」
貼り付け方法
• 埋め込む : 元ファイルとは別の複製 • データをリンク : 元ファイルを参照(変更に同期) 貼り付け先のテーマを使用しブックを埋め込む 元の形式を保持しブックを埋め込む 貼り付け先のテーマを使用しデータをリンク 元の書式を保持しデータをリンク 図複数系列のグラフ
• 単純に選択する範囲を増やせばよい
• C2セルに「=COS(A2)」を入力しオートフィル • A1:C17を選択し
系列の詳細な設定
• 「デザイン」→「データの選択」
軸の表示桁数等の調整
• 軸上で右クリックしメニューを出し
軸の表示範囲の調整
• 同様に
• 「軸のオプション」→最小値、最大値
出納帳の例とか
• 例えば、以下のような数式にしておく 自動で計算された ここはオートフィルで ここもオートフィルで 残高の推移を グラフにしてみたり 「資料003」を参照行・列の挿入とか
• 見出し上で右クリックして「挿入」 • 追加した行や列を編集 • 日付と残高で「散布図」作ってみたり 見出しを斜めに してみたり行幅、列幅の自動調整
• 行・列見出しの境界をダブルクリック
行幅、列幅の一括調整
• 行または列を選択して境界をドラッグ • 又は見出し右クリックで「列の幅」「行の高さ」 見出しを ドラッグして選択 右クリック 境界をドラッグ 選択範囲が すべて同じ幅に• 曲面 z = sin( 𝑥2 + 𝑦2) • 原点からの距離をSIN関数に与えている • B2セルに「=SIN(SQRT($A2^2+B$2^2))」を入力 • べき乗演算子は ^ • 平方根は SQRT 関数 • あとはB2:N14をオートフィルで埋める
• A1:N14を選択
• 曲面の方程式を変えてみる • 座標の刻みを小さくしてみる
比較演算子
• 比較結果として真偽値を得る • 真なら TRUE • 偽なら FALSE 数学 Excel = = ≠ <> < < ≦ <= > > ≧ >=文字列の結合
• 数式中の文字列 • 「"」(ダブルクォーテーション)で囲む • 文字列の結合演算子「&」 • 演算子前後の値を繋げた文字列を得る " 2 ふ ↑Shift +文字列の比較
• 数値との比較 • 「文字列>数値」が成立 • 文字列同士の比較 • 先頭から文字単位で文字コードを比較 • より大きな文字コードでより長い文字列が大 文字列としては “1234” より “234” の方が大きい 常に文字列>数値 より大きな文字コードが大 より長い文字列が大文字コードの例(Shift JIS 1バイト目)
• 半角1文字を1バイト(=8bit)のコードで表す • 全角1文字は2バイト(例: 1=824Fh,あ=82A0h) 制御文字 全角文字1バイト目用(60通り) 16進数 0 1 2 3 4 5 6 7 8 9 A B C D E F 10進数 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 0 0NUL SOH STX EXT EOT ENQ ACK BEL BS HT LF HM CL CR SO SI10 16DLE DC1 DC2 DC3 DC4 NAK SYN ETB CAN EM SUB ESC → ← ↑ ↓
20 32SPC ! " # $ % & ' ( ) * + , - . / 30 480 1 2 3 4 5 6 7 8 9 : ; < = > ? 40 64@ A B C D E F G H I J K L M N O 50 80P Q R S T U V W X Y Z [ ¥ ] ^ _ 60 96` a b c d e f g h i j k l m n o 70 112p q r s t u v w x y z { | } ~ DEL 80 128 90 144 A0 160 。 「 」 、 ・ ヲ ァ ィ ゥ ェ ォ ャ ュ ョ ッ B0 176ー ア イ ウ エ オ カ キ ク ケ コ サ シ ス セ ソ C0 192タ チ ツ テ ト ナ ニ ヌ ネ ノ ハ ヒ フ ヘ ホ マ D0 208ミ ム メ モ ヤ ユ ヨ ラ リ ル レ ロ ワ ン ゙ ゚ E0 224 F0 240
• コンピュータの中
• 文字も数値(文字コード)として記録されている • 文字コードに対応したフォントを表示している
This is a text file.
これはテキストファイルです。
0000000: 5468 6973 2069 7320 6120 7465 7874 2066 This is a text f 0000010: 696c 652e 0d0a 82b1 82ea 82cd 8365 834c ile...これはテキ 0000020: 8358 8367 8374 8340 8343 838b 82c5 82b7 ストファイルです 0000030: 8142 0d0a 。..
• 先頭から1文字づつ比較 • 文字単位の大小関係が決着した所で比較終了 • 比較する文字がない場合、長いほうが大 a = a b = b c = c d > a = a b < c c > b a = a b < c c > b d > abc = abc a = a b = b c = c abcd > abc
abc < acb abcd < acb
同一
右が大
左が大
名前の定義
• セルに名前を付けて参照し易く出来る • 選択セルを「数式」→「名前の定義」で命名 名前の有効範囲は ブックまたはワークシート内を 選択可能定義した名前を利用した参照
• 定義した名前を用いて数式を記述できる
• セル名で参照するより意味が分かり易くなる
名前が定義されている場合
名前の管理
• 定義した名前の一覧・修正・削除
定義した名前を数式へ入力する際は 「数式で使用」ボタンから選択可能
IF関数
• IF文 • プログラムで条件分岐に使われる • Excelでは関数扱い(IF関数) • 数式の一部として用いる • 書式(詳細は「IF関数」参照) • IF(論理式, 真の場合, 偽の場合) • 使用例 文字列を返す例 数値を返す例 論理式 真 偽 処理 処理IF関数の使用例
• 成年・未成年の判別
論理演算関数
• AND, OR, NOT 関数 • 条件を論理演算する場合に使う • AND(条件式1, 条件式2, …) • OR(条件式1, 条件式2, …) • NOT(条件式) 条件1 条件2 AND ORFALSE FALSE FALSE FALSE FALSE TRUE FALSE TRUE TRUE FALSE FALSE TRUE TRUE TRUE TRUE TRUE
条件 NOT
FALSE TRUE TRUE FALSE
うるう年の判別
• うるう年の定義 • 4で割り切れるもの(X)はうるう年 • そのうち100で割り切れるもの(Y)は除外 • そのうち400で割り切れるもの(Z)は含む • うるう年を1,通常年を0として • 例えば次のように書ける • =IF(X, IF(Y, IF(Z, 1, 0), 1), 0) • =IF(Z, 1, IF(Y, 0, IF(X, 1, 0)))• =IF(OR(AND(X, NOT(Y)), Z), 1, 0)
X Y Z
うるう年の判別(式の解説1/3)
• =IF(X, IF(Y, IF(Z, 1, 0), 1), 0) • =IF(X, , 0) IF(Y, , 1) IF(Z, 1, 0) 外側から順に判別している X Y Z
うるう年の判別(式の解説2/3)
• =IF(Z, 1, IF(Y, 0, IF(X, 1, 0)))
• =IF(Z, 1, ) IF(Y, 0, ) IF(X, 1, 0) 内側から順に判別している X Y Z
うるう年の判別(式の解説3/3)
• =IF(OR(AND(X, NOT(Y)), Z), 1, 0) • =IF( , 1, 0) OR( ) AND( ), Z X, NOT(Y) 外側の青領域 内側の青領域 青領域全体 論理演算により うるう年とそれ以外に分離 X Y Zうるう年の判別結果
ここには名前の定義で ワークシート内のみ有効な 西暦, X, Y, Z という名前を付けています MOD関数は 剰余を求める関数。 剰余が0なら 割り切れるということ。• セルの個数を数える • COUNT(値1, 値2, …) • 数値セルや数値の個数 • COUNTA (値1, 値2, …) • 空白でないセルや空白でない値の個数 • COUNTBLANK (値1, 値2, …) • 空白セルや空白の値の個数 • COUNTIF (範囲, 条件) • 条件に合致するセルの個数 • COUNTIFS (範囲1, 条件1, 範囲2, 条件2, …) • 複数の条件に合致するセルの個数(Excel2007以降)
COUNT系関数(2/2)
• 値を合計する • SUM(数値1, 数値2, …) • セル範囲に含まれる数値をすべて合計 • SUMIF(範囲, 検索条件, 合計範囲) • 指定された検索条件に一致するセルの値を合計 • SUMIFS(合計対象範囲, 条件範囲1, 条件1, 条件 範囲2, 条件2, ...) • セル範囲内で、複数の検索条件を満たすセルの値を 合計(Excel2007以降)
SUM系関数(2/2)
集計処理の例(成績表)
成績表の例(集計) (1/3)
• 中間・期末テストの成績(資料201)
• 評点(中間期末の合計点を100点換算)
成績表の例(集計) (2/3)
• やり方はいくつもある • 評点(E2セルの場合) • =(C2+D2)/2 • =SUM(C2:D2)/2 • =SUM(C2:D2)/COUNT(C2:D2) • =AVERAGE(C2:D2) • =C2*50/100+D2*50/100 • 中間、期末で点数配分が異なる場合もある • 平均(C13セルの場合) • =(C2+C3+C4+C5+C6+C7+C8+C9+C10+C11)/10 • =SUM(C2:C11)/10 • =SUM(C2:C11)/COUNT(C2:C11) • =AVERAGE(C2:C11) • なるべく関数で一発計算できるように • 残りはオートフィルで成績表の例(集計) (3/3)
• 集計方法の一例(資料202)
成績表の例(桁数調整) (1/2)
• 調整したい部分選択「ホーム」→「桁数調整」
成績表の例(桁数調整) (2/2)
成績表の例(合否判定) (1/5)
• 評点60点以上合格、それ未満不合格
• IF関数も利用して合否の表示
成績表の例(合否判定) (2/5)
• やはりやり方はいくつもある • 合否判定(F2セルの場合) • =IF(AVERAGE(C2:D2)>=60, "合", "否") • =IF(E2>=60, "合", "否") • =IF(60<=E2, "合", "否") • =IF(E2<60, "否", "合") • 境界値には注意(未満、以下、以上、超) • なるべく計算済みの結果を利用すると楽成績表の例(合否判定) (3/5)
• 合否人数の集計 • =COUNTIF(F2:F11, "合") • =COUNTIF(F2:F11, "否") • 合計人数は迷うところ • 人数を数えるか、合否の合計を取るか • =COUNTA(F2:F11) • =SUM(F15:F16) • 何のための集計項目か? • 入力した数式に間違いがないか確認の意味? • であれば合否の合計の方がよい? • 人数合計、合否人数合計の2項目あった方がベターかも?成績表の例(合否判定) (4/5)
• 判定方法の一例(資料204)
成績表の例(合否判定) (5/5)
成績表の例(得点分布) (1/4)
• 「得点分布」ワークシート(資料205)
• 10点区切りで人数集計(○点以上、○+10点未満) • 90~100点の区間だけは90点以上100点以下
成績表の例(得点分布) (2/4)
• やはりやり方はいろいろだが • D2セル(中間の0~10点)の場合 • =COUNTIF(成績!C2:C11, ">=0")-COUNTIF(成績!C2:C11, ">=10") • =COUNTIFS(成績!C2:C11, ">=0", 成績!C2:C11, "<10") • オートフィルしたいのだが • 「成績!C2:C11」の行範囲が自動的に変わる • $をつけて行のみ絶対参照にして固定 • 列は中間、期末、評点で自動で変わってほしい • 文字列(得点範囲)が自動で変わらない成績表の例(得点分布) (3/4)
• 絶対参照を使う • =COUNTIFS(成績!C2:C11, ">=0", 成績!C2:C11, "<10") • ↓ • =COUNTIFS(成績!C$2:C$11, ">=0", 成績!C$2:C$11, "<10") • 文字列連結演算子「&」を使う • =COUNTIFS(成績!C$2:C$11, ">=0", 成績!C$2:C$11, "<10") • ↓• =COUNTIFS(成績!C$2:C$11, ">="&$A2, 成績!C$2:C$11, $B2&$C2)
• あとはオートフィルで • 人数合計はSUM関数
成績表の例(得点分布) (4/4)
成績表の例(ヒストグラム) (1/9)
成績表の例(ヒストグラム) (2/9)
• タイトルをクリックして編集
成績表の例(ヒストグラム) (3/9)
• 「グラフツール」,「デザイン」,「データの選択」
成績表の例(ヒストグラム) (4/9)
成績表の例(ヒストグラム) (5/9)
成績表の例(ヒストグラム) (6/9)
• 凡例の調整
背景の塗りつぶしと 枠線の描画も設定
成績表の例(ヒストグラム) (7/9)
• 同様にして、期末、評点も作成(資料207)
比較対象となるグラフは 軸のスケールを統一する
成績表の例(ヒストグラム) (8/9)
• 同様に中間~評点(D1:F11)を選択しグラフ化
成績表の例(ヒストグラム) (9/9)
• あとは見易く、分かり易く調整を
• タイトル、軸ラベル、3-Dの表示角、等々 • サンプル(資料208)
成績表の例(評価) (1/5)
• 秀優良可不可の5段階評定を行う • それぞれ90,80,70,60,0点以上 昇順でソートされた 基準得点と評価の対応表を用意する 検索値 点数に対応した評価を探してきて 表示したい成績表の例(評価) (2/5)
• VLOOKUP関数を使う(詳細はヘルプ参照) • VLOOKUP(検索値,範囲,列番号,検索の型) • 範囲の左端の列から検索値を探す • 見つかった行の列番号で指定されたデータ返す • 検索の型 • TRUEにすると • 検索値未満の最大値を見つける • FALSEにすると • 完全に一致するものを見つける • みつからなければ #N/A エラー値を返す 検索値と 比較する値 戻す値 列番号2成績表の例(評価) (3/5)
• 「成績」ワークシートG2セル
• =VLOOKUP(E2,評価基準!A$2:B$6,2,TRUE) • 範囲の行は絶対参照で固定する
成績表の例(評価) (4/5)
• 評価基準に人数分布を作成
• C2セル
• =COUNTIF(成績!G$2:G$11,B2)
成績表の例(評価) (5/5)
成績表の例(オートフィルター)
• オートフィルター
• ソート(並べ替え)やフィルタリング(データ抽出)を 簡単に実行出来る
• 並べ替えると元の並び順が失われる!!! • 元に戻すには
• 「元に戻す」を使う
• あらかじめ元の並び順の情報を入れておく
• 見出し行をスクロールさせないようにできる
• 大きな表、表示する際に便利
• 見出し行に合わせ「表示」,「ウインドウ枠の固定」
スクロールしても 見出し行が残る
得点分布の集計(別の方法)
• FREQUENCY関数を使う方法 • 配列数式を使うので若干難解? • 分析ツールのヒストグラム機能を使う方法 • 集計結果を生成する • 元の表を変えたら要作り直し • Google等で検索してみましょう • 「Excel FREQUENCY関数」 • 「Excel 分析ツール ヒストグラム」• 以下の状況でどうやって見つける? • FREQUENCY関数なんて知らない • 分析ツールなんて知らない • 検索キーワードを工夫する • 「Excel 成績表 人数分布」 • ↑検索キーワードをスペースで並べると • AND (=すべてのキーワードを含む)検索になる
データの入力規則(1/5)
• データの誤入力防止に効果的 • 値の範囲指定 • 入力可能データをリストから選択 • 等々 • サンプルデータ(資料211)データの入力規則(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)
• サンプル(資料212)
ピボットテーブル(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)
• 自動並べ替えのチェック外して • 並べ替え規則を設定
• ピボットテーブルと同じ操作でグラフも付く
• 結果サンプル(資料214) 「ピボットグラフツール」→「デザイン」 →「グラフの種類変更」から
他のアプリとのデータ交換
• テキストのみのファイル形式を用いる
• CSV – Comma Separated Value • TSV – Tab Separated Value
• 「名前を付けて保存」で • 「ファイルの種類」選ぶと保存できる • 読み込みは後述 CSV or TSV Excel アドレス帳 年賀状 作成ソフト その他 市販ソフト等 自作 プログラム
• Windows7だとユーザ名のフォルダにある
ユーザ名の フォルダを開く
• explorer.exe で shell:contacts を開く
explorer.exeのアドレス欄
「田+R」から
「ファイルを指定して実行」
• メールアドレスのほか住所等が記録できる
アドレス帳のエクスポート(1/5)
アドレス帳のエクスポート(2/5)
• CSV選んでエクスポート
アドレス帳のエクスポート(3/5)
アドレス帳のエクスポート(4/5)
• エクスポートするフィールドを選択 • 名 • 姓 • 自宅の番地 • 自宅の市区町村 • 自宅の郵便番号 • 自宅の都道府県 • 選択したら「完了」アドレス帳のエクスポート(5/5)
• 保存したCSVは他のアプリと相互に利用可能 • 例えば「筆まめ」「筆王」等の年賀状作成ソフト • 携帯電話のアドレス帳管理ソフト • 等々 • エクスポート結果のサンプル(資料301)テキストファイルウィザード(1/5)
テキストファイルウィザード(2/5)
• 文字コードを適切に(通常は自動識別) • 文字化けしてたら、適切なコードに • JIS,シフトJIS,UTF-8等 ここが文字化け してないか確認 文字化けしている場合 文字コードを変更テキストファイルウィザード(3/5)
• 区切り文字を適切に• プレビューが縦線で区切られるよう
TSVかCSVかによって
テキストファイルウィザード(4/5)
• 必要なら、各列にデータ形式を設定 • 主には文字列とか日付とか 学籍番号のように ゼロで始まる数字列を 文字列として扱いたい場合等は 適切に列のデータ形式を設定テキストファイルウィザード(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)
• 適当に一連の操作を行う
• 実行 • 記録した操作の再生 • 編集 • 記録した操作の編集 • オプション • キー割り当ての変更
• 記録した操作の編集が出来る