前ページの例をさらに進めて、「通常時間」と「深夜時間」に分けて見ましょう。「深夜時間」とは一般に午後10時から午前5時を指しますので、ここでもその時間帯を「深夜」とします。

• 実際に計算したものを表示します。
  
  (画像をクリックすると、このサンプルがダウンロードできます)
  青枠が元の開始・終了時刻、赤枠の部分が算出結果です。Excelで起動できる方は青枠の時刻をいろいろ変えて見て下さい。これで見ると「SUMIF関数」を単純に使っているだけですが、H列から右側に中間計算をしている部分がたくさんあります。
• まず、「開始時刻」と「終了時刻」をそれぞれ「まるめ」たもの(時刻)を用意します。
  
  前ページのサンプル同様にNETの時刻のままのものと、15分単位、30分単位に切り捨てたものを用意します。前ページのサンプルでは、いきなり差し引きの時間数を算出してしまいましたが、ここでは「まるめ」た時刻にとどめます。開始時刻側は「CEILING関数」で15分か30分単位に切り上げし、終了時刻側は「FLOOR関数」で切り捨てます。
  
  
  
  

  関数	概略説明
  FLOOR関数	指定された基準値の倍数のうち、最も近い値かつ 0 に近い値に数値を切り捨てます。  引数は①数値、②基準値です。
  CEILING関数	基準値の倍数のうち、絶対値に換算して最も近い値に切り上げられた数値を返します。  引数は①数値、②基準値です。

  
  
  
  
• さらに上記の画面の右側で、通常時間、深夜時間の各時間帯に「どれだけ掛かっているのか」を個別に計算します。
  
  少々、長い計算式となります。
  赤枠の左側の青字部分が「各時間帯」を表わしています。例えば選択されている「開始5:00、終了22:00」はその時間帯となります。その隣の「0」「1」は、「0」だったら通常時間帯、「1」だったら深夜時間帯となります。
  左の黒字の時間数は、左からNET、15分単位、30分単位で青字の各時間帯に掛かっている時間数を表わしています。
  
• 計算式は、まず該当の時間帯に開始／終了時刻の範囲が「掛かっているか」を判断します。実際に入力された開始時刻がその時間帯の終了時刻より「前」で、かつ(AND)、入力された終了時刻がその時間帯の開始時刻より「後」であれば、その時間帯に掛かっていることになります。
• 掛かっているならば、入力値か時間帯開始の大きい方から、入力値か時間帯終了の小さい方までの時間数を算出し、「時間数(時間帯ごと)」にセットします。「MIN関数」を使っている方が終了時刻側で、まるめた元の「終了時刻」とその時間帯行の「終了時刻」の小さい方、「MAX関数」を使っている方が開始時刻側で、まるめた元の「開始時刻」とその時間帯行の「開始時刻」の大きい方、これで算出された「終了時刻」から「開始時刻」を差し引いてその時間帯行あたりの時間数が算出されます。
  
  
  
  

  関数	概略説明
  AND関数	すべての引数がTRUEと評価された場合はTRUEを返し、1つ以上の引数がFALSEと評価された場合はFALSEを返します。  引数は1つ以上の論理式で、最大255個まで指定できます。
  MIN関数	引数に指定した個々の値、セル参照、セル範囲、またはこれらの組み合わせのうち、最小の数値を返します。
  MAX関数	引数に指定した個々の値、セル参照、セル範囲、またはこれらの組み合わせのうち、最大の数値を返します。

  
  
  
  
• 最後に、各時間帯にセットされた各時間数を、「深夜」の値が「0」か「1」かで判断してそれぞれの時間数のを集計しているわけです。このような複雑な計算では、答えを収容するセルに単に計算式を用意すれば済むものではなく、欄外(デザイン的には行列を隠して見えなくする)に各種の中間計算を必要とします。
• ここではお見せしませんが、私がやっていた例では、「深夜」以外に会社規定休憩時間帯、勤務予定時間帯の「0」「1」を用意して、もっと細かい時間帯に分けて中間計算しており、「通常」「深夜」の時間数からは休憩時間帯を除外し、さらに勤務予定時間帯に掛かる時間数から「遅刻・早退」時間も算出します。全体の時間は「深夜」か「通常」かに分けるのはこのサンプルと同じですが、このサンプルでは休憩控除がありません。私の勤務先では24時間の中に休憩時間帯が6カ所定められているので、実就業の時間帯でそれぞれの休憩時間帯に掛かっている分を差し引きます。
  これとは別に勤務予定時間帯で休憩時間帯でない分の各時間帯の時間数も割り出します。これは遅刻・早退の時間数を把握するためです。
  これらの集計には「SUMIF関数」ではなく、「SUMPRODUCT関数」か「SUMIFS関数」を使用します。
  
  
  
  

  関数	概略説明
  SUMIF関数	指定した条件を満たす、セル範囲内の値を合計します。引数は以下の通りです。  ① 検索範囲  ② 検索条件  ③ 合計範囲
  SUMPRODUCT関数	対応する範囲または配列の積の合計を返します。 既定の演算は乗算ですが、加算、減算、除算も可能です。  引数は1組以上の配列引数です。
  SUMIFS関数	複数の検索条件に一致するすべての引数を合計します。  引数は第1引数で算出セル範囲を指定し、以降は次の①、②を1組として1組以上で最大127組を指定できます。  ① 条件セル範囲  ② 条件(値や条件式：大小判定や不等号式、ワイルドカード可)