(Microsoft Word - \202v\202a\202s\203G\203N\203Z\203\213.doc)

Size: px

Start display at page:

Download "(Microsoft Word - \202v\202a\202s\203G\203N\203Z\203\213.doc)"

まなみやまる
9 years ago
Views:

1 表計算ソフトを活用した授業時間数の集計 <Excel2007> システムの完成版はダウンロードして活用することができます広島県立教育センター

月別の授業時数を集計する簡易なシステムを作成してみましょう 1Excel を起動します 2 シート名を 4 月とするために, シート見出し Sheet1 を右クリックし, 名前の変更をクリックします 3 Sheet1 が反転するので, 4 月と入力します右クリック 4 見出しを作成しますセル F1 H1 J1 及びセル E4 からセル J4 に見本のとおりに文字列を入力しますさらに,

そうでない場合 ( 月の値が入力された場合 ) は 1 を表示する ( セル A6) =IF($I$1="","",A5+1) 月の値が入力されていなかったら, 何も表示せず, そうでない場合 ( 月の値が入力された場合 ) はセル A5 の値に 1 を加算し, 表示する ( セル B5) =IF(A5="","",DATE($G$1+1988,$I$1,A5))

2 月別の授業時数を集計する簡易なシステムを作成してみましょう 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 -

3 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 をクリックします曜日が土日の場合と休日欄に休が表示され

月も同様に入力します行事休日欄時間割時間割は正規の時間割を入力しておき, 変更があった場合に入力し直せばよいので, 予め入力しておきます 1 2 3 4 5 6 月社会学び算数音楽国語書写火国語体育家庭算数理科理科水音楽算数情報国語社会体育木国語算数社会道徳図画工作図画工作金英語活動学級活動理科算数国語クラブ児童会

4 数式を使用して, 書式設定をするセルを決定をクリックします数式を入力します =AND($A5>0,OR(WEEKDAY($B5)=1,WEEKDAY($B5)=7,$D5=" 休 ")) 書式をクリックして, 塗りつぶしでセルの背景色を桃色に設定し, OK をクリックします新しい書式ルールで OK をクリックします曜日が土日の場合と休日欄に休が表示された場合にセルが桃色に変わりますワークシートやセルに設定したルールは, ホームタブスタイル条件付き書式ルールのクリアで解除できます 10 表に罫線を引いておきます 11 必要なデータを入力してみましょう年, 月このシステムでは日付を計算する関数を利用しているので, 年度ではなく年で入力する必要があります平成 22 年度の1 月は平成 23 年 1 月, 以下 2 月,3 月も同様に入力します行事休日欄時間割時間割は正規の時間割を入力しておき, 変更があった場合に入力し直せばよいので, 予め入力しておきます月社会学び算数音楽国語書写火国語体育家庭算数理科理科水音楽算数情報国語社会体育木国語算数社会道徳図画工作図画工作金英語活動学級活動理科算数国語クラブ児童会 12 授業時数を集計する表を作成しますセル L3 から L17 に科目名を, セル L18 に計の文字列を入力しますセル M3 に次の関数式を入力して, 時間割の欄から国語の授業を集計するようにします =COUNTIF($E$5:$J$35,L3) 数式をセル M17 まで複写します数式を入力しておくと, 時間割が変更した場合でも, 自動的に時間数の集計を修正することができます時間割を変更し, 時間数が変わることを確認しておきましょうセル M18 には, 次の関数式を入力し, 授業時数の計が求まるようにしておきます =SUM(M3:M17) - 3 -

13 時間割が変更した場合に, リストから科目名を選択できるようにしておきます 2ページの 8で設定したように, データの入力規則で設定しますセル E5 からセル J35 をドラッグして範囲指定しますデータタブデータツールデータの入力規則をクリックします設定タブで, 入力値の種類をリスト, 元の値を科目名を入力しているセルの範囲としますセル L2 からセル L17

J35 まで, セル L2 からセル L18 までを範囲指定し, 右クリックでセルの書式設定を選択します ( 離れているセルを範囲指定する場合は Ctrl キーを使います ) 保護タブをクリックし, ロックを解除しますセル G1 と I1, セル C5 からセル J35 までとセル L2 からセル L18 までは, 必要なデータを入力しなければならないセルなので,

5 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 -

6 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 -

7 Microsoft,Windows 2000,Windows NT,Office,Internet Explorer は米国 Microsoft Corporat ion の米国およびその他の国における登録商標または商標ですその他, 本書に掲載したプログラム名, システム名,CPU などは一般に各社の登録商標です本文中では, マークは省略しましたまた, 一般に使われている名称を用いている場合があります平成 22 年 4 月日初版発行発行広島県立教育センター東広島市八本松南 1 丁目 2-1 (082)

エクセルの基礎を学びながら、金額を入力すると自動的に計算され、1年分の集計も表示される「おこづかい帳」を作りしょう

エクセルの基礎を学びながら、金額を入力すると自動的に計算され、1年分の集計も表示される「おこづかい帳」を作りしょう Excel2007 Windows7 出納簿を作って毎日の現金の入金出金を記入し差引残高を表示させましょう 1. Excel を起動しましょう... 1 2. タイトルと項目を入力しましょう... 1 3. No. を入力しましょう... 1 4. 罫線を引きましょう... 2 5. タイトルの書式設定をしましょう... 2 6. 項目の書式設定をしましょう... 3 7. 桁区切りスタイルを設定しましょう...