演習 : Excel 初級講座 初心者のための表計算ソフト活用講座 平成 29 年 7 月 27 日 ( 水 ) オートフィル機能 1 オートフィル機能 数値や文字が入力されたセルをドラッグするだけで データを自動入力できる機能 11 番 2 番を入力す る 21 と 2 が入力された セルをドラッグして選 択する 3 選択した範囲のセル の右下 ( 角 ) にカーソル ( マウス ) を合わせる 4 カーソルが + に変わったらドラッグして 番 号を入力したいセルまでカーソルを移動する Pg. 1
四則演算 列幅を広げる 1B 列と C 列の境目にカーソルを持って行く 2 カーソルが白い矢印から 左右開きの矢印に変わった場所で ダブルクリック 列幅の自動調整 ドラッグ 手動で広げたり 縮めたりできる 罫線をひく 1 罫線を引く範囲をドラッグして指定する (B2:E8) 2 ホーム タブのリボンから罫線を選択する セルの結合 1 結合するセルの範囲を指定する 2 ホーム タブのリボンからセルの結合を選択する ( 今回はセルの結合して中央揃え ) Pg. 2
行の挿入 1 7 行目 で右クリックする 2 メニューから 挿入 をクリックする 7 行目 (6 行目の下 ) に空白行が挿入される 合計 平均 関数の活用 SUM 関数 合計を求める AVERAGE 関数 平均を求める 2 関数の挿入 ボタンをクリックする 1 結果を表示させたいセルを選択する 関数の分類 を 最近使用した関数 すべて表示 に切り換えると全ての関数の中から選択 できる 3 関数名 から合計を求める場合は SUM を選択 ダブルクリック 平均を求める場合は AVERAGE を選択 ダブルクリック ) する Pg. 3
4 数値 1 の右側のボタンをクリックする 5 E3 セル ~ E6 セルまで ドラッグして 選択し Enter キーを押す 6 OK ボタンをクリックする Pg. 4
オートフィル機能 2 絶対参照 ( 絶対セル番地 ) の設定 絶対参照 : 行 列ともに固定させる参照方法 $ 記号をつけることにより コピーしてもそのセル番地がずれない 1 オートフィル機能でコピーした時 に ずれて欲しくないセル番地で F4 キーを押す 2 F4 キーを押すと 自動的に $ 記号が挿入 ( 付加 ) される ちなみに 数式をコピーしたとき コピー先でそこのセル範囲に合わせて行番号と列番号が変 化する参照の仕方 ( 通常の場合 ) を相対参照という グラフの作成 1 B2 セル ~ N4 セルまで ( グラフに必要な範囲 ) をドラッグして選択する 2 メニューの 挿入 タ ブをクリックする 3 グラフの種類 折れ 線 を選択する Pg. 5
4 ポイントの付いてい るグラフを選択する 5 グラフができあがる グラフタイトル 35 30 25 20 15 10 5 0 1 月 2 月 3 月 4 月 5 月 6 月 7 月 8 月 9 月 10 月 11 月 12 月 平均最高気温 ( ) 平均最低気温 ( ) 6 グラフタイトルをダブルクリックして タイトル名を変更します グラフ要素を追加する場合は グラフを選択後 リボン上の グラフツール - デザイン - グラフ要素の追加 で行います Pg. 6
入力規則 氏名を姓と名に分割する ( セル内の文字をスペースで分割する ) 1 D4 セル~ D21 セルまで選択( ドラッグ ) する 2 リボンの データ - 区切り位置 を選択( クリック ) する 3 カンマやタブなど に 印がついてい ることを確認して 次へ ボタンをクリ ックする 4 区切り文字 は スペース のみをチェッ クして 次へ ボタンをクリックする 5 表示先 は E4 セルを指定する 6 完了 ボタンを押す Pg. 7
2 つのセルに入力された文字 ( テキスト ) を結合する フリガナ ( 姓 ) とフリガナ ( 名 ) をくっつける 1 H4 セルで 式を作ります =I4&J4 姓と名の間にスペース ( 空白 ) をいれたい場合は =I4& &J4 2 H4 セルに 姓と名がくっついて表示されます 3 H4 セルの式を オートフィル機能でコピーします リスト ( 入力規則 ) を使って 性別 を入力する 1 G4 セル ~ G21 セルまで選択 ( ドラッグ ) する 2 リボンの データ - データの入力規則 を選択 ( クリック ) する 3 入力値の種類 を リスト を選択する 4 元の値 に以下の様に入力する 男, 女, は半角 5 OK ボタンをクリックする Pg. 8
リスト ( 入力規則 ) を使って 出身小学校 を入力する 1~2 は前ページと同じです 3 元の値 には 右側のボタンをクリックして M17 セル~ M20 セルまでドラッグして Enter キー を押す ( 範囲が自動入力される ) 4 OK ボタンをクリックする MAX MIN RANK IF 関数 最高点 ( 最大値 ) 最低点 ( 最小値 ) を求める 合計 平均と操作は共通です 1 結果を表示させたいセルを選択する 2 関数の挿入 ボタンをクリックする 関数の分類 を 最近使用した関数 す べて表示 に切り換えると全ての関数の中か ら選択できる 3 関数名 から 最大値を求める場合には MAX を選択する ( ダブルクリック ) 最小値を求める場合には MIN を選択 ( ダブルクリック ) する 4 範囲を指定する Pg. 9
順位を付ける RANK 関数 範囲内における順位を求める 1 M4 セルに以下の式を設定する =RANK(K4,$K$4:$K$21,0) 数値 順位を求めたいセル ( 合計点 ) 参照 順位をつける範囲コピーした時に 範囲がずれないように 絶対参照させる ($ 記号を追加する )! 順序 0 または省略 降順 1 など 昇順 2 OK ボタンをクリックする 3 K5 セル以降は オートフィル機能を使ってコピーする Pg. 10
平均点が 80 点以上の生徒には 印が表示されるように設定する IF 関数 条件によって表示させるものを変えることができる 1 N4 セルに以下の式を設定する =IF(L4>=80," ","") 論理式 条件 真の場合 条件が成り立つ場合 や文字を表示させるには ( タ フ ルクォーテーション ) で挟みます! 偽の場合 条件が成り立たない場合 ( その他 ) 何も表示させたくない場合には と設定します! 2 OK ボタンをクリックする 3 L5 セル以降は オートフィル機能を使ってコピーする Pg. 11
科目の得点が 35 点未満や 80 点以上のセルの文字色やセルの塗りつぶし色を自動で行わせる 1 F4 セル ~ J21 セルまで選択 ( ドラッグ ) する 2 リボンの ホーム - 条件付き書式 - セルの強調表示ルール - その他 のルール を選択 ( クリック ) する 3 条件を設定する セルの値 次の値より小さい 35 ( セルの値 次の値より大きい 80 ) 4 書式 ボタンを押す 5 フォント 塗りつぶし タブで色を選択する 35 点未満は 赤系統 ( ピンクっぽい ) の色 80 点以上は 青色系統の色 6 OK ボタンを押す 2 条件付き書式を解除するには リボンの ホーム - 条件付き書式 - ルールのクリア を選択します Pg. 12
COUNT 関数 データの個数を数える COUNT 関数 指定された範囲内で数値データの個数を数える COUNTA 関数 指定された範囲内でのデータの個数を数える 合計 平均と操作は共通です 1 結果を表示させたいセルを選択する 2 関数の挿入 ボタンをクリックする 関数の分類 を 最近使用した関数 す べて表示 に切り換えると全ての関数の中か ら選択できる 3 関数名 から 数値を数えさせる場合には COUNT を選択する ( ダブルクリック ) データの個数を数えさせる場合には COUNTA を選択 ( ダブルクリック ) する 4 範囲を指定する COUNTIF 関数 指定された範囲内で条件に合ったデータの個数を数える 3 関数名 から 条件にあうデータの個数を数えさせる場合には COUNTIF を選択する ( ダブルクリック ) 範囲 個数を数える範囲 検索条件 数えさせたい条件 や文字を表示させるには ( タ フ ルクォーテーション ) で挟みます! Excel2007 以降では, 自動で入力されます Pg. 13
VLOOKUP 関数 学校番号を入力すると 学校名が表示されるよう設定する VLOOKUP 関数 指定された値を指定された範囲内で検索し その検索された値と同じ行のデータを表示する 1 C4 セルに以下の式を設定する =VLOOKUP(B4,F2:G49,2,FALSE) 検索値 検索する値 ( 今回は入力された学校番号 ) 範囲 検索する範囲検索する値が表の左端になるように設定すること! 表示させたいデータ ( 今回は学校名 ) を必ず含むこと 列番号 検索値の列 ( 左端 ) も含めて何列目 ( 何番目 ) か 検索方法 完全一致させたい場合には FALSE を設定する 2 OK ボタンをクリックする VLOOKUP 関数のポイント 検索は 指定した表の 左端列 で行われる 取り出したいデータ位置は 指定した表の 左端から何列目か で指定する VLOOKUP 関数は 検索結果を表示したいセル に入力する Pg. 14
おまけ セル内で ( 強制的に ) 改行するには 改行したい文末で Alt キーを押しながら Enter キーを押します ファイルにパスワードをかける 大事なデータを 他人に見られないように ファイルにパスワードを設定することができます 1 ファイル - 名前を付けて保 存 を選択します 2 保存画面の右下の ツール (L) ボ タンをクリックします 3 ツール (L) をクリックした時に表示されるプルダウン メニューの中から 全般オプション (G) をクリックし ます 4 全般オプション にて 読み取りパスワード (O) 書 き込みパスワード (M) を設定することができます 5 確認のために 再度パスワードを入力します (2 回 ) 読み取りパスワード ファイルを開くこと ( 閲覧 ) を制限します 書き込みパスワード 上書き保存を制限します 読み取り専用で開いた場合 上書き保存はできませんが 名前を付けて保存 をすることはできます Pg. 15
タイトル行を各ページに印刷するための設定 表が複数ページにまたがる場合 タイトルを各ページに印刷することができます 1 メニューの ページレイアウト をクリックし リボンの 印刷タイトル をクリックします 2 ページ設定 画面で 印刷タイトルの タイト ル行 の欄に 各ページに印刷したいタイトルの 行を指定します 左側の行番号をドラッグすることで 行指定でき ます 3 OK ボタンをクリックします 印刷プレビュー で確認して見ましょう 改ページプレビュー で 1 ページに収める範囲を調整できます 印刷時に 1 ページに納める範囲を微調整するには 1 表示 タブの 改ページプレビュー を選択 ( クリック ) します Pg. 16
2 青い点線が改ページ ( ページを折り返す ) 部分です カーソル ( マウスの矢印 ) を重ね 上下の矢印に変わった時に ドラッグして移動させ 調整することが出来ます ウィンドウ枠固定で行 列を固定表示するワークシートの行項目や列項目が画面に表示されなくなった場合に 特定の行や列を固定して表を見やすくすることができます 行または列を固定表示する 1 固定表示したい行の 1 行下の行番号をクリックします ここでは 3 行目までを固定表示するため 4 行目をクリックします 列を固定表示する場合は 固定したい列の 1 つ右の列番号をクリックします Pg. 17
2 リボンから 表示 タブ - ウィンドウ枠の固定 - ウィンドウ枠の固定 をクリック シートを下方向にスクロールして 行が固定されていることを確認してください 固定表示を解除するには リボンから 表示 タブ - ウィンドウ枠の固定 - ウィンド ウ枠固定の解除 をクリックします 行と列を固定表示する方法 1 固定表示したいセルの右斜め下のセルをクリックします ここでは 1~5 行目と A~B 列を固定表示するため セル C6 をクリックします 2 リボンから 表示 タブをクリックし ウィンドウ グループの ウィンドウ枠の固定 を クリックします シートをスクロールして 行と列が固定されていることを確認してください Pg. 18
データの抽出 ( オートフィルタ ) 1 リスト内のセルをどれでもいいので 1 つ選択します 2 データ タブ - フィルタ をクリックします 先頭の各項目名に がつきます 3 都道府県名で 東京都 を抽出する場合は 都道府県名の をクリックし 全ての選択 のチェックを外し 東京都にチェックを入れます 東京都のデータの抽出ができます 4 フィルタを解除する場合は データ タブ 並べ 替えとフィルタブループの クリア をクリック します Pg. 19
条件付でデータまたは数値を抽出 ( フィルター ) する 1 前ページの 1~2 の操作後 列に数値が含まれる場合は [ 数値フィルター ] をクリック します 列にテキストが含まれる場合は [ テキストフィルター ] をクリックします 2 使用したいフィルターオプションを選び フィルター条件を入力します たとえば 指定 の数値よりも大きい数値を表示するには [ 指定の値以上 ] を選び 隣接するボックスに基準 となる数値を入力します 3 2 つの条件に基づいてフィルターするには 両方のボックスにフィルター条件を入力しま す 両方の条件が満たされるようにするには [AND] を選び どちらかの条件が満たされる ようにするには [OR] を選びます Pg. 20
ワークシートに保護をかける設定 ( 他のユーザ - が誤って変更しないように ) 予め関数や数式などを設定しているワークシートで 他のユーザーに ( 誤って ) 変更して欲しくない 部分に保護をかけておくことができます 1 ワークシートを保護する前に 他のユーザーによる編集が可能なセルのロックを解除します B5 E5 H5 出席番号を入力するセルを複数選択する B5 セルをクリックした後 Ctrl キーを押しながら それぞれ E5 H5 とクリックします 2 選択されたセル上で ( どのセルでも構いません ) 右クリックすると メニューがでます その項目の中で セルの書式設定 を選択 ( クリ ック ) します 3 セルの書式設定の中で 保護 のタブに切り替え ロック をオフにします オフにする 初期値は シートの保護 をかけた時に 全てのセルに ロック がかかる ( 入力 変更ができない ) 設定 になっています ロック の項目のチェックを外すと シートの保護 をかけても 入力や変更が可能となります 4 メニューの 校閲 をクリックし リボンの シートの保護 をクリック します Pg. 21
5 シートの保護を解除するためのパスワード 欄に パスワードを入力します 6 OK ボタンをクリックすると パスワードの確認が出ますので 再度入力します これで シートに保護がかかり ロック が外されていないセルは 入力も変更もできなくなりま す 仮に 誤って入力や Delete がされた場合には 下記の様なメッセージが表示されます シートの保護を解除するには 7 メニューの 校閲 をクリックし リボンの シートの保護 をクリ ックします 8 パスワード欄に シートの保護 の設定時に入力した パスワード を入力します シートの保護 が解除されました Pg. 22
VLOOKUP 関数を用いて 点数に応じた値変換 テストの点数に応じて 評定 (5 段階評価等 ) に自動変換する場合に便利です 1 右図のように 参照する表を作成します 0~34 点 1 35~49 点 2 50~64 点 3 65~79 点 4 80~100 点 5 2 C7 セルに以下の式を設定する =VLOOKUP(C6,$N$4:$P$8,3) IF 関数を 4 つ組み合わせても可能です =IF(C6>=80,5,IF(C6>=65,4,IF(C6>=50,3,IF(C6>=35,2,1)))) Pg. 23