Excel。時間と分が別々のセルに入力された表で手早く合計時間を算出したい
＜SUM+QUOTIENT&MOD関数＞
なんでそんな表をつくったのという帳票をちょこちょこ見かけます。
次のような表もそのケースの一つです。
セルに、「2:35」と入力していれば、普通に和算すれば、手間暇かけずに、合計時間を算出することができたはずです。
ところが、この表。
時間と分が別々で入力されています。
このように、時間と分が別々のセルに入力されている場合、どのような数式をつくったら、手早く算出することができるのでしょうか？
「時間」を算出する数式から確認していくことにします。
B3の数式は、
=SUM(B1:B2)+QUOTIENT(SUM(D1:D2),60)
と設定しました。
考えなくてはいけない点は、分の繰り上げも含めて合算することですね。
そのため、単純に、SUM(B1:B2)だけにすることができません。
「QUOTIENT(SUM(D1:D2),60)」のQUOTIENT関数は、除算した商を算出する関数です。
D1:D2の合計値は80です。
この80を1時間60分なので、60で除算します。
結果は、1余り20ですね。
余りの部分は不要なので、それを除いた、商の値も追加することで、時間を算出することができます。
続いて、「分」を算出する数式をつくります。
D3の数式は、
=MOD(SUM(D1:D2),60)
と設定しました。
QUOTIENT関数は、除算した結果の「商」を算出しました。
それに対してMOD関数は、除算した結果の「余り」を算出することができます。
D1:D2の合計値を60で除算した余りをMOD関数で算出すれば、繰り上げした値を除いた余り、すなわち「分」を算出することができるというわけです。
ここで、間違いやすいのは、60で減算すればいいのではとして、数式をつくると、対応することができません。
15+25=40だとすれば、40-60で「-20」と算出されてしまいます。
このようなことから、D1:D2の合計値が、60未満の場合、「－（マイナス）」で算出されてしまうので、MOD関数をつかって、余りを算出する必要があるわけです。
どうしても帳票にExcelを合わせると、簡単なことも、色々考えないといけないことがありますので、できることなら、Excelの特徴や機能を優先した帳票をつくるようにするといいかもしれませんね。
