表計算ソフトを活用した授業時間数の集計 <Excel2007> システムの完成版はダウンロードして活用 することができます 広島県立教育センター
月別の授業時数を集計する簡易なシステムを作成してみましょう 1Excel を起動します 2 シート名を 4 月 とするために, シート見出し Sheet1 を右クリックし, 名前の変更 をクリックします 3 Sheet1 が反転するので, 4 月 と入力します 右クリック 4 見出しを作成します セル F1 H1 J1 及びセル E4 からセル J4 に見本のとおりに文字列を入力します さらに, セル A3 とセル A4 をセル結合し, 文字列を入力します セル B 3 とセル B4, セル C3 とセル C4, セル D3 とセル D4 もセル結合し, 文字列を入力します 5 セル A5 A6 B5 に数式を入力します セル A6 B5 の数式を 35 行目まで複写します ( セル A5) =IF($I$1="","",1) 月の値が入力されていなかったら, 何も表示せず, そうでない場合 ( 月の値が入力された場合 ) は 1 を表示する ( セル A6) =IF($I$1="","",A5+1) 月の値が入力されていなかったら, 何も表示せず, そうでない場合 ( 月の値が入力された場合 ) はセル A5 の値に 1 を加算し, 表示する ( セル B5) =IF(A5="","",DATE($G$1+1988,$I$1,A5)) 月の値が入力されていなかったら, 何も表示せず, そうでない場合 ( 月の値が入力された場合 ) はセル G1 年, セル I1 月, セル A5 日のシリアル値を表示する シリアル値とは,1988 年の 1 月 1 日を 1 として, 経過日数を数値で表示したものです 6B 列に曜日が表示されるように, セルの書式設定を変更します セル B5 から B35 をドラッグして範囲指定します 右クリックして, メニューから セルの書式設定 を選択 し, 表示形式 のタブをクリックします 分類で ユーザー定義 を選択し, 種類の欄に aaa と 入力します OK をクリックすると, シリアル値が曜日に変更されて 表示されます セル G1 に 22, セル I1 に 4 とすれば, セル A6 には 木 と表示されます 半角英数で aaa と入力す - 1 -
7セル A33 以降の日付について, 数式を修正します セル A33 には, 29 が表示されますが,29 日以降の日付は月によって異なります 例えば 4 月は 30 日まで,5 月は 31 日まで,2 月は 28 日までであったり,29 日までであったりします 年月によって, 日付が正しく表示できるようにします セル A6 の関数式をセル A35 まで複写します 併せて, セル B5 の関数式もセル B35 まで複写しておきます (1 ページの 5 で複写は完了しています ) セル A33 に,=IF($I$1="","",A32+1) が入力されていることを確認します 数式バーに表示された数式を次のように変更します ( セル A33) =IF($I$1="","", IF(MONTH(DATE($G$1+1988,$I$1,29))=$I$1,29,"")) セル A33 の日付のシリアル値に対する月のシリアル値とセル I1 の値を比較して, 同じであれば 29 を表示する セル A34, セル A35 についても同様に修正します ( セル A34) =IF($I$1="","", IF(MONTH(DATE($G$1+1988,$I$1,30))=$I$1,30,"")) ( セル A35) =IF($I$1="","", IF(MONTH(DATE($G$1+1988,$I$1,31))=$I$1,31,"")) 8D 列の休日欄に, 入力するデータをリストから選ぶことができるように設定します セル D5 からセル D35 までをドラッグして, 範囲指定します データ タブ データツール データの入力規則 をクリックします 設定 タブで, 入力値の種類を リスト, 元の値を 休 とします OK をクリックします 9 曜日が 土 日 の場合と休日欄に 休 が表示された場合に, その行のA 列からJ 列までのセルが桃色に表示されるように設定します ( セルの条件付き書式 ) セル A5 からセル J35 までをドラッグして, 範囲指定します ホーム タブ スタイル 条件付き書式 セルの強調表示ルール その他のルール をクリックします - 2 -
数式を使用して, 書式設定をするセルを決定 をクリックします 数式を入力します =AND($A5>0,OR(WEEKDAY($B5)=1,WEEKDAY($B5)=7,$D5=" 休 ")) 書式をクリックして, 塗りつぶし でセルの背景色を桃色に設定し, OK をクリックします 新しい書式ルール で OK をクリックします 曜日が 土 日 の場合と休日欄に 休 が表示され た場合にセルが桃色に変わります ワークシートやセルに設定したルールは, ホーム タブ スタイル 条件付き書式 ルー ルのクリア で解除できます 10 表に罫線を引いておきます 11 必要なデータを入力してみましょう 年, 月 このシステムでは日付を計算する関数を利用しているので, 年度ではなく年で入力する必 要があります 平成 22 年度の1 月は平成 23 年 1 月, 以下 2 月,3 月も同様に入力しま す 行事 休日欄 時間割 時間割は正規の時間割を入力しておき, 変更があった場合に入力し直せばよいので, 予め 入力しておきます 1 2 3 4 5 6 月 社会 学び 算数 音楽 国語 書写 火 国語 体育 家庭 算数 理科 理科 水 音楽 算数 情報 国語 社会 体育 木 国語 算数 社会 道徳 図画工作 図画工作 金 英語活動 学級活動 理科 算数 国語 クラブ 児童会 12 授業時数を集計する表を作成します セル L3 から L17 に科目名を, セル L18 に 計 の文字列を入力します セル M3 に次の関数式を入力して, 時間割の欄から 国語 の授業を集計するようにします =COUNTIF($E$5:$J$35,L3) 数式をセル M17 まで複写します 数式を入力しておくと, 時間割が変更した場合でも, 自動的に時間数の集計を修正することができます 時間割を変更し, 時間数が変わることを確認しておきましょう セル M18 には, 次の関数式を入力し, 授業時数の計が求まるようにしておきます =SUM(M3:M17) - 3 -
13 時間割が変更した場合に, リストから科目名を選択できるようにしておきます 2ページの 8で設定したように, データの入力規則で設定します セル E5 からセル J35 をドラッグして範囲指定します データ タブ データツール データの入力規則 をクリックします 設定 タブで, 入力値の種類を リスト, 元の値を科目名を入力しているセルの範囲とします セル L2 からセル L17 をドラッグして指定します セル L2 は空白を選択できるようにするためです OK をクリックします 4 月のシートが完成です 時間数の集計が正しく行われていることを確認しておきましょう 14セルの保護設定をします シートやセルの書式を設定したり, 関数式を入力したりした際には, それらの書式や関数式が消去されないように保護設定をすることができます セル G1 と I1, セル C5 からセル J35 まで, セル L2 からセル L18 までを範囲指定し, 右クリックで セルの書式設定 を選択します ( 離れているセルを範囲指定する場合は Ctrl キーを使います ) 保護 タブをクリックし, ロック を解除します セル G1 と I1, セル C5 からセル J35 までとセル L2 からセル L18 までは, 必要なデータを入力しなければならないセルなので, 予めロックを解除しておく必要があります シート全体を範囲指定し, 校閲 タブ 変更 シートの保護 をクリックします パスワードを入力します (2 度入力します ) 忘れてしまった際には, 保護の解除が不能となるので, 覚えやすいパスワードを設定する方がよいでしょう 保護設定されたセルを変更しようとすると, 次のようなメッセージが表示されます 変更しない場合は OK をクリックし, 変更する場合は 校閲 タブ 変更 シートの保護の解除 をクリックし, パスワードを入力して保護を解除します 完成版のシステムでは, シートの保護のパスワードを半角 数字 4 桁の 0000 で設定しています - 4 -
15 完成した 4 月のシートを複写し, シート名を変更して,5 月から 3 月までのシートを作成し,4 月から 順に整列させておきます 以上で,1 年分のシートが完成です 16 年間の授業時数を集計するシートを作成します ( 串刺し計算 ) 新しいシートを挿入し, シート名を 集計 にします A 列に科目名を入力します セル B3 には, シート 4 月 から 12 月 までのセル M3 に求まっている値が集計できるように関数式を入力します セル B3 をクリックします ア ) 数式バーに =sum( と入力します イ ) シート見出しの 4 月 をクリックし,Shift キーを押しながら, シート見出しの 3 月 をクリックします ウ ) シートの範囲指定が完了したことと確認し, セル M3 をクリックします エ ) ) を入力し, Enter キーを押します セル B3 には, =SUM('4 月 :3 月 '!M3) の数式が入力されます オ ) セル B3 の数式を, B17 まで複写すると, 科目ごとの時間数が集計されます カ ) セル A18 には 計 を, セル B18 には,=SUM(B3:B17) の関数式を入力し, 年間の授業時数の合計を求めるようにします 1 年分の授業時数を集計するシステムが完成しました 学期ごとに集計したい時は, 区切りになる月のシートを 2 枚作成し, 必要なデータ ( 時間割 ) を入力します 集計用のシートも複数枚作成して, 区切りごとの集計を行うようにしましょう ( 例 )2 学期制 (10 月途中で前期と後期が分かれる ) の場合を例に示します 注意 110 月のシートを 2 枚用意します 1 枚は 10 月前半 とし, もう1 枚を 10 月後半 とします 2 集計のシートも 2 枚用意し,1 枚目は 前期集計,2 枚目を 後期集計 とします 35ページの 16のイ ) で 前期集計 のセル B3 には,=SUM('4 月 :10 月前半 '!M3) が入力されるように, 4 月 と 10 月前半 のシート見出しをクリックします 後期集計 のセル B3 には,=SUM('10 月後半 :3 月 '!M3) が入力されるように, 10 月後半 と 3 月 のシート見出しをクリックします - 5 -
Microsoft,Windows 2000,Windows NT,Office,Internet Explorer は米国 Microsoft Corporat ion の米国およびその他の国における登録商標または商標です その他, 本書に掲載したプログラム名, システム名,CPU などは一般に各社の登録商標です 本文中では, マークは省略しました また, 一般に使われている名称を用いている場合があります --------------------------- 平成 22 年 4 月日初版発行 発行広島県立教育センター 739-0144 東広島市八本松南 1 丁目 2-1 (082)428-2655 - 6 -