1. 日 付 を 入 れます 日 付 にはいろんなからくりがあります やりながら 覚 えてい きましょう B2 に 4/1( 火 ) と 表 示 させます 1B2 をクリ ックします 2 半 角 で 4/1 と 入 れてエンターを 押 します 4 月 1 日 と 表 示 されます 3もう 一 度 B

Transcription

1 時 数 計 算 時 数 計 算 は 教 務 では 必 須 の 仕 事 でしょう 今 回 は 入 力 シートに 時 間 割 を 入 れて( 国 語 は 国 算 数 は 算 というように)いくと 自 動 的 に 計 算 し 集 計 表 に 月 ごとの 時 数 を 出 すというものです 平 成 20 年 度 で 作 ってみましたので 関 数 がよくわからなくても このまま 使 うということもできるかと 思 います 尚 サンプルデータは 適 当 に 入 れたもの なので その 辺 はお 含 み 下 さい 入 力 シートでは まず 日 付 関 係 からやっていきます 曜 日 を 入 れること 土 日 及 び 祝 日 は 赤 に 長 期 休 業 は 薄 緑 に 自 動 的 に 色 が 変 わるようにします そして 時 間 割 を 入 れると 右 側 にその 日 の 時 間 数 が 出 るようにします 一 日 に2 回 あると 2 と 表 示 されます チ ェックの 欄 では 算 を 産 などと 誤 って 入 れた 時 エラーを 表 示 するように 工 夫 しま した 図 46 集 計 表 では 月 ごとの 時 数 が 表 になって 出 るようにした ここでは 異 なるシート 間 の 関 数 の 扱 いについて 学 習 します ここでも 関 数 を 駆 使 して できるだけコピーを 使 って 楽 がで きるように しっかりと 式 の 作 り 方 を 学 習 します 図 47 それではCDから 時 数 集 計 表 問 題 を 開 いてください ~エクセル 18~

2 1. 日 付 を 入 れます 日 付 にはいろんなからくりがあります やりながら 覚 えてい きましょう B2 に 4/1( 火 ) と 表 示 させます 1B2 をクリ ックします 2 半 角 で 4/1 と 入 れてエンターを 押 します 4 月 1 日 と 表 示 されます 3もう 一 度 B2 をクリックし 右 クリックで セルの 書 式 設 定 ユーザー 定 義 を 選 択 します 4 種 類 のところに m/d(aaa) と 入 力 し ます 5エンターキーを 押 します 4/1( 火 ) となり ます ちなみに aaa は 火 aaaa は 火 曜 日 ddd は Tue dddd は Tuesday となっ ています いずれもユーザー 定 義 で 設 定 します それでは 右 クリックで 3 月 31 日 までドラッグ し 連 続 データでコピーして 下 さい 3 月 31 日 は 火 曜 日 ですね 図 48 図 条 件 書 式 曜 日 によって 色 を 変 えていく 条 件 書 式 に 入 るにあたって WEEKDAY 関 数 を 学 習 します WEEKDAY 日 付 の 曜 日 を 調 べる 関 数 =WEEKDAY( 日 付 の 値, 種 類 ) です 曜 日 を 調 べて 1 から 7 の 数 値 を 返 します ここでは 種 類 を 2 にします 空 いているセル AC2 に =WEEKDAY(B2,2) と 入 れてみてください 2 と 返 ってきます 種 類 日 月 火 水 木 金 土 それでは 条 件 書 式 に 入 ります 条 件 書 式 というのはある 条 件 の 時 ( 今 回 の 場 合 は 土 曜 日 日 曜 日 の 時 ) 一 定 の 書 式 にする( 今 回 の 場 合 はその 行 をピンクにする)という 書 式 で す ここでは 1 曜 日 による 色 分 け 2 祝 日 による 色 わけ 3 長 期 休 業 と3つの 条 件 を 同 時 にやってしまいます D の 列 を 見 て 下 さい や のついているセルがありますね は 祝 日 または 振 替 休 日 は 長 期 休 業 です 条 件 書 式 は 条 件 1 土 曜 日 か 日 曜 日 だったら ピンク 条 件 2 〇 だったらピンク だったら 薄 緑 という 式 を 作 ります 14 月 1 日 の 番 号 からチェック A2 から AB2 までを 選 択 します 2メニューバーから 書 式 をクリック 2サブメニューから 条 件 書 式 をクリック 3 条 件 1 セルの 値 が の を クリックして 数 式 が にする 4その 右 の 空 白 に =WEEKDAY($B2,2)>5 と 入 れる 5 書 式 (F)をクリック ( 図 50)6パターンをクリック 薄 いピンクを 選 んで OK 7 追 加 をクリック ~エクセル 19~

3 図 50 8 条 件 2のところも 数 式 にして =$D2= をクリックし 書 式 を 先 ほど 同 じピンクを 選 択 して O K 9さらに 追 加 をクリック 10 条 件 3 の と こ ろ も 数 式 に し て =$D2= をクリックし 書 式 を 先 ほど 同 じく 今 度 は 薄 い 緑 を 選 択 して OK ( 図 51) 最 後 にもう 一 図 51 度 OK 以 上 です 4 月 1 日 は がついていますので 薄 緑 になります 11 再 度 4 月 1 日 の 番 号 からチェック(A2 から AB2)までを 選 択 します 2ハンドルを 右 ク リックして 一 番 下 までドラッグ 12 書 式 のみコピーをクリック 祝 日 夏 休 みもうまく 色 がかわっていますか 図 52 ~エクセル 20~

4 3. 月 の 数 字 を 入 れる あとで 月 ごとに 集 計 する 時 に 一 発 で 関 数 が 使 えるように 下 準 備 をしておきます C の 列 を 開 けていますので そこの4 月 でしたら4と 入 るようにします あとで 4のつくものを 合 計 しなさい という 集 計 に 使 います MONTH 関 数 ( 日 付 から 月 を 取 り 出 す) =MONTH( 日 付 ) です 1 から 12 が 返 ってきます 同 様 に YEAR や DAY もつかえます 1C2 をクリックします 2 =MONTH(B2) と 入 力 します 3エン ターキーを 押 します 図 53 4もう 一 度 C2 をクリックします ハンドルを 右 クリックしながら 一 番 下 までドラッグし 書 式 なしコピーをクリック 以 上 です ( 図 53) 4. 時 間 割 の 駒 が 何 であるかを 探 し 図 54 て 数 を 表 示 する 前 の 成 績 処 理 の と こ ろ で COUNTIF 関 数 を 学 習 しました ここではこの 関 数 を 使 います 4 月 1 日 ( 本 来 は 休 みです がそこは 抜 きにして)の 国 語 がいくつあるか 数 えるところで 説 明 します 国 語 の 数 を 表 示 する L2 には E2 から J2 の 範 囲 で L1( 国 語 )の 数 を 数 えなさいという 命 令 になります 社 会 算 数 と 横 に そして 3 月 31 日 まで 一 気 に 縦 に 数 えさせるために うまく 絶 対 値 を 使 います 条 件 に 合 うものの 数 を 数 える =COUNTIF( 範 囲, 数 える 対 象 ) まず 考 えてみて それから 説 明 します 何 もデータがないと 合 っているかどうかわかりませんので 4 月 1 日 に 適 当 に 駒 を 入 れて 下 さい 図 月 1 日 の 国 語 の 時 数 L2 をクリックしま す 2 =COUNTIF( と 入 力 し E2 から J2 までをドラッグします 3ここで 絶 対 値 をつけてしまいます ファンクションキーのF4を 3 回 押 して =COUNTIF($E2:$J2 とします ( 図 55)4キーボードから, を 入 力 します 5L1 の 国 をクリック 6 ファンクションキーのF4を2 回 押 し =COUNTIF($E2:$J2,L$1 とし エンターキーを 押 します 7L2 を 再 度 クリックし ハンドルを 右 クリックしながら 欠 の 列 までドラッグし 書 式 ~エクセル 21~

5 なしコピー 8L2 から AA2 ま で 選 択 し ハンドルを 右 クリッ クしながら 一 番 下 の 行 までド ラッグし 書 式 なしコピー ちょっと0がうるさいので0だ ったら 表 示 しないようにしま す セルはどこにあってもかま いません 9メニューバーのツ ール オプションをクリック 10 表 示 タブから ゼロ 値 のチ ェックマークを 外 す ( 図 56) 以 上 です 図 入 力 が 正 しいかチェックをする 時 間 割 を 入 れる 時 算 を 産 などと 間 違 っていれると 数 値 に 反 映 されません 後 で 合 わなくて 困 ることのないように チェック 機 能 をつけましょう 考 え 方 としては その 日 の 授 業 数 ( 入 れた 駒 数 )と 検 索 した 数 が 一 致 するかどうかを 確 かめます もし1 時 間 産 が 入 っていると 授 業 数 は6なのに 駒 数 は5ということでエラーを 出 します 範 囲 にある 空 白 外 のセル 数 を 数 える =COUNTA( 範 囲 ) 範 囲 にある 数 を 合 計 する =SUM( 範 囲 ) この COUNTA 関 数 と SUM 関 数 そして IF 関 数 で 合 っていたら 何 も 表 示 されず 間 違 っていた 場 合 のみ NG と 表 示 させます AB2 をクリック そこに =IF(COUNTA(E2:J2)=SUM(L2:AA2),"","NG") と 入 力 してい きます 意 味 は 授 業 数 と カウントした 数 が 同 じだったら "" 何 もないものを 表 示 つまり 何 も 表 示 しない 同 じでなかったら NG と 表 示 しなさいということです エンターキーを 押 しても 何 も 出 ないと 思 いますが 適 当 に 間 違 えた 文 字 を 入 れてみてくだ さい NG と 出 るはずです 図 集 計 する 小 さな 表 では 一 枚 のシートに 全 て 演 算 結 果 を 出 すのですが 表 が 大 きくなると 別 シ ートにまとめた 方 が 見 やすいものになります ここでは 集 計 表 の 式 をかんがえていきま しょう 尚 サンプルデータがないとやりにくいので お 料 理 番 組 みたく 途 中 まで 作 った のがありますので 時 数 計 算 途 中 データあり のファイルを 開 いて 下 さい ~エクセル 22~

6 大 きな 山 がありますが がんばっていきましょう ここでは4(4 月 )だったら 4 月 分 の 国 語 を 合 計 するという SUMIF 関 数 を 学 習 します これを 使 うことによって 12 回 足 し 算 をすることなく 縦 ( 月 ごと)にも 横 ( 教 科 )にも コピーして 計 算 させることができます 条 件 に 合 うものを 合 計 する =SUMIF( 範 囲, 検 索 条 件, 合 計 範 囲 ) ここで 範 囲 は 入 力 シートの 月 の 列 全 部 検 索 条 件 は 集 計 表 の 月 範 囲 は 国 語 の 列 全 部 で す これで4 月 だったら 入 力 シートの4の 部 分 の 国 語 を 合 計 するというものです 月 の 数 字 が 入 っている 範 囲 は 全 てのセルの 計 算 で 同 じところを 参 照 しますから 行 も 列 も 絶 対 番 地 です 集 計 表 の 月 の 数 字 は 上 下 は 5 月 6 月 と 動 きますが 算 数 社 会 になっ ても 参 照 するところはいつでも A 列 です ですから 行 は 絶 対 番 地 ですが 列 は 相 対 番 地 です 合 計 範 囲 は 行 はいつでも 4 月 1 日 から 3 月 31 日 までですから 絶 対 番 地 列 は 社 会 算 数 と 右 へずれれば 合 計 するとこも 右 へずれます ですから 列 は 相 対 番 地 です 1B2 をクリック 2 =SUMIF( と 入 力 3 入 力 シートのタブをクリック 4C2 から C366 をドラッグ ( 数 式 バーには =SUMIF( 入 力 シート!C2:C366 と 表 示 )5ファンクションキ ーのF4を 押 す ( 数 式 バーには =SUMIF( 入 力 シート!$C$2:$C$366 と 表 示 )6キーボー ドから, を 入 力 7 集 計 表 をクリック8A2 をクリック ファンクションキーのF4を 3 回 押 す 9キーボードから, を 入 力 10 入 力 シートのタブを 再 度 クリック 11L2 から L366 までをドラッグ 12ファンクションキーのF4を 2 回 クリック ( 数 式 バーには =SUMIF( 入 力 シート!$C$2:$C$366, 集 計 表!$A2, 入 力 シート!L$2:L$366 と 表 示 )13エン ターキーをクリック 14B2 をクリック 15ハンドルを 右 クリックして 欠 Q2 までドラッ グ 16 書 式 なしコピーをクリック 17B2 から Q2 まで 選 択 18 右 クリックしながら 3 月 までドラッグ 19 書 式 なしコピーをクリック 20 0 がうるさいので メニューバーか ら オプション 表 示 0 値 のチェックを 外 す ( 図 58) 図 58 ~エクセル 23~

7 7. 合 計 を 出 す 図 59 一 番 下 に 合 計 を 出 します もうおわかりでしょう 1B14 をクリック 2 シグマをク リック 3エンターキーを 押 す 4もう 一 度 B14 をクリック ハンドルを 右 クリックで 一 番 右 までドラッグして 書 式 なしコピーをクリック ( 図 59) 8. 非 表 示 にする 入 力 シートのタブをクリックしてください C と D の 列 は 作 業 情 報 なので 隠 しておきましょう 1 一 番 上 の 列 番 号 の C と D をクリック 2 青 くなったところで 右 クリッ ク ( 図 60)3 表 示 しない をクリック 再 び 表 示 させる 時 は B と E の 列 をクリックし 列 番 号 (B と E の 境 目 )でダブルクリックします 図 59 ~エクセル 24~