Excelで時数管理①～年間の予定を埋める・・・・・からのつづき
前回は年間の可能時数を割り出し、概算の年間予定を立てるため、１年間の暦と時間枠を作成し、その日付ごとの時間枠に時数に関係のない予定（休日や行事）を埋める・・・・・・という作業を行いました。（ややこしくてわからない方は「Excelで時数管理①～年間の予定を埋める」を見てね）
こんな感じで埋めていく。
今回は、年間の可能時数を算出する作業を行います。
このような表を予定表の横に作成しました。
1週間の時間割りですね。日曜日も授業があることを考えてすべての曜日を含んだ表にしました。
作成する場合は、まず、月曜日の1時間目の枠から作成しましょう。
３つに分かれているのは、1学期～3学期を表します。その上に全学期の集計が表示されるようにしています。
それでは、1学期の月曜日、1時間目の可能時数を集計する数式を作成します。
集計というよりはカウントと言ったほうがいいのかもしれないですね。
カウントというとCOUNT関数がありますが、COUNTIF関数は、２つ以上の範囲の複数条件を満たすカウントはできないので、当ブログで扱ってなかったのですがSUMPRODUCTという関数を使います。
SUMPRODUCT関数は配列内の対応する要素間の積を計算し、それらの値の合計を求める関数ということで、ほとんどわけがわからないですが、今回はこの関数を説明とは別な使い方をします。
引数はこのようになります。
SUMPRODUCT(配列1,配列2,配列3,..)
この「配列」に範囲指定するとその積を計算し、うんぬん・・・ということなのですが、ここに条件式を入れると、条件を満たしたとき「1」を返し、満たさないときは「0」という値を返してくれるという別な顔も持っています。これを利用し、複数条件を満たすカウントをしようということです。
例えばこんな使い方をします。
=SUMPRODUCT((A1:A10="国語")＊(B1:B10<=70))
A列には「教科」、B列には点数が入力されてるものとします。
A1が国語の場合、条件を満たしているので「1」になります。そしてB1が70以下の場合、これまた条件を満たしているので「1」になります。
引数の内容を見るとこの条件どうしを掛け算（＊）していますね。したがって条件を満たしたものどうしだと・・・「1×1」、つまり答は「1」になります。
一方が条件を満たさないと値として「0」が返されますから、「1×0」で、答は「0」になります。つまり条件をすべて満たす場合のみ、「1」という答になるのです。
これを指定された範囲分
繰りかえして集計してくれます。指定された範囲に「国語」で「70以下」のデータがいくつ存在するかカウントすることができます。
このSUMPRODUCT関数の特性を活かし、時間割表の月曜日、1時間目、1学期の可能時数をカウントする式を作成しましょう。
可能時数は、年間を通して、授業が何時間行えるかを学期が始まる前に算出し、概算の見通しを立てるため使います。したがって、年度初めに配布される時数に関係のない「／」（休日）や「ク」（クラブ）などの予定を表に入力し、その予定
以外のセルをカウントすることになります。
基本的には図の赤く囲った部分、つまり空白をカウントします。ただ、気をつけるのは、予定表には、のちのち実際に行った教科の情報も入力する予定なので（次回以降説明します。）、「国」や「算」といった時数に関係のある値が入力されたセルもカウントするように数式を作成します。
月曜日、1時間目の1学期のセルを選択します。
条件は、予定表の曜日が「月」で、時数に関係のない「児,行,／,委,ク」が入力されたセル以外をカウントするということになりますのでこのような数式になります。
=SUMPRODUCT(($B$9:$B$130=P$8)*($C$9:$C$130<>"／")*($C$9:$C$130<>"行")*($C$9:$C$130<>"児")*($C$9:$C$130<>"ク")*($C$9:$C$130<>"委"))
長ーい数式になりました。（選択範囲は絶対参照にしましょう。）
条件一つ目の「B9」から「B130」は予定表の曜日列の1学期の範囲です。
そこに入力された値で「P8」（時間割の曜日のセル）と同じ値、つまり「月」ならば条件一致で値「1」が返されます。
条件二つ目の「C9」から「C130」は予定表の1時間目で1学期の範囲です。
「<>」は（比較演算子で「以外」という意味）そこに入力された値で「／」以外の値であれば条件一致で「1が返されます。
それ以降の数式はそれぞれ「行」「児」「ク」「委」以外であれば条件一致で「1」が返されることになります。
このすべての条件6個が一致すれば、「1×1×1×1×1×1」という計算になるので、「1」という値が返ります。
一つでも満たさないと、「0」が計算に入ってくるので、他の条件を満たしても「0」という結果になります。
このひとつひとつの計算を指定した範囲分繰り返し合計してくれます。すべて条件を満たした「1」という答えが何個あるかということです。
同様に2学期、3学期も設定しましょう。1学期をコピーして範囲をそれぞれ変更しましょう。
2学期は・・・・・・
=SUMPRODUCT(($B$131:$B$283=P$8)*($C$131:$C$283<>"／")*($C$131:$C$283<>"行")*($C$131:$C$283<>"児")*($C$131:$C$283<>"ク")*($C$131:$C$283<>"委"))
3学期は・・・・・・
=SUMPRODUCT(($B$284:$B$373=P$8)*($C$284:$C$373<>"／")*($C$284:$C$373<>"行")*($C$284:$C$373<>"児")*($C$284:$C$373<>"ク")*($C$284:$C$373<>"委"))・・・・・こんな感じに範囲を変更します。
まだ予定表が入力されていなければ、選択範囲のすべての月曜日がカウントされるはずです。なぜなら、月曜日で「／」「行」「児」「ク」「委」以外だからです。確認の意味で数えてみるのもいいでしょう。
３学期まで設定が終わったら、上のセルに1～3学期の合計の数式を入れておくといいですね。
そして、1時間目を7時間目までコピーします。
ここからはまた地道に条件の範囲を修正していきます。
1学期の2時間目なので、曜日の条件はそのままで、それ以降の範囲が変更されます。図の例では2時間目はD列になってます。
2時間目、1学期は・・・・・
=SUMPRODUCT(($B$9:$B$130=P$8)*($D$9:$D$130<>"／")*($D$9:$D$130<>"行")*($D$9:$D$130<>"児")*($D$9:$D$130<>"ク")*($D$9:$D$130<>"委"))・・・・・と、こんな感じになります。
3時間目以降も条件式の列名の変更をしていきます。
7時間目まで修正したら、あとは、日曜日までコピーします。
ふー。地道な作業ですね。でも、毎回この作業を行うわけではありません。これが完成すれば未来永劫使えるかもしれないですから。（言い過ぎか（笑）。）
まだ、予定表が空白ならば、どの時間も1週間の合計が365になるはずです。
それでは予定表に入力してテストしてみましょう。画面の関係上、月曜日の1時間目から3時間目の1学期の範囲にデータを入力してみます。
4月6日の1時間目に「国」（国語）2時間目に「／」（休み）3時間目は「ク」（クラブ）を入力しました。
図の左が入力前、図の右が入力後。
「国」が入力された1時間目は変化がありません。そして時数に関係のない「／」と「ク」が入力された2時間目、3時間目は時数が減っています。
ただしく、数式が設定されました。
これで年間の予定を入力し終えた時、この時間割には年間の可能時数だけ残るというわけです。次回まで適当に予定を入力してみます。
さて、これでこの時間割が完成したわけではありません。次回は、時間割りを完成させて、概算の時数を入力してみます。
Excelで時数管理③～概算時数時間割表を完成させる・・・・・へつづく
