- ホーム >
- 応用操作 >
- エクセル（Excel）編 >
FLOOR関数 ～ 勤務時間を15分単位で切り捨てる ～
昨今、働き方改革によって労働時間の管理が厳しくなってきました。Excelとは関係ないようですが、Excelで勤務表や勤怠表を作成しているケースは、かなりあるのではないでしょうか。
専用のソフトを導入すると高額になるため、小規模な事業所などではExcelで自作したり、インターネットからテンプレートをダウンロードして利用されていることと思います。
COUNTIF関数 で学習したとおり、簡単な勤務表であれば、リストメニュー（ドロップダウンリスト）やCOUNTIF関数を組み合わせて、勤務日数や人数を集計することができます。
本項では、こうした勤務表などで使われるその他の便利な関数について学習していきたいと思います。
COUNTIF関数での集計は、COUNTIF関数の性質上、文字列を集計することしかできないため、例えば「日勤」という文字の個数がいくつになるかを集計していました。そのため、ある期間やその日の「日勤者」が何名で「休み」が何名といったシフト管理（人数管理）には適した関数になります。
ただし、勤務者の「勤務時間」を求める場合はなかなか難しくなってきます。労働基準監督署が求めるような細かい勤務時間の計算は割愛しますが、まず、時間計算についておさらいしておきましょう。
時間の計算は、時間計算 で学習したとおり、Excelでは時刻と時間を混同してしまいがちです。
下図のように、出勤時間と退勤時間を入力して勤務時間を求める場合、単純に「退勤時間」－「出勤時間」を計算するだけなので、「=D3-C3」の数式で「8:00」が求められます。
一見すると「8時間」で正しい勤務時間が算出されているように見えますが、このセル「8：00」は「8時間」を意味しているのではなく、「午前8時」を意味しています。
そのため、時給計算をする場合など、時給×「8：00」をすると、結果は「0:00」と表示されてしまいます。時刻である「午前8時」を750倍した計算を行っており、軽く24時を超えるため表示できなくなっているのです。
この場合は、表示形式を「標準」や「通貨」にしても「250」となり、明らかに時給計算の値とはなりません。（この場合はシリアル値を750倍した値になります）
詳しくは、時間計算 を再度見直していただければと思いますが、勤務時間という「時刻」ではなく「時間」を表す場合には、時間単位（h）に変換する必要があります。
時間単位はシリアル値×「24」で変換することができます。したがって、時刻の「8：00」は時間単位で「8」となります。（「8：00」のシリアル値「0.3333」×「24」）
さらに気を付けなければいけないのは「分」の取り扱いです。同様にシリアル値に「24」を掛けることで求めることができますが、「11：15」の場合の「15分」は時間に変換すると「0.25」であり、時間単位は「11.15」とはなりません。「11：15」は「11.25」、「23：45」は「23.75」となります。
このことを念頭に置いたうえで、下図のような、もう少し複雑な時間計算をしてみましょう。
上図は、出勤時間、退勤時間、休憩時間を入力し、実労働時間を求めています。また、労働時間に対して時給を掛けて、一週間の給与を計算しています。
どうしても端数が出てしまうので、15分未満の端数時間に対して「15分切捨て」の場合と「15分切上げ」の場合で分け、それぞれに関数を適用しています。
関数については後述しますので、まずは、実労働時間を時間単位で求めてみましょう。
表に「拘束時間」という行を追加しました。この行は単純に「退勤時間」から「出勤時間」を引いた時間です。ただし、時間単位の表示に変換しています。
「朝倉いずみ」さんの「9/6」の拘束時間は、前述のとおり「24」を掛けて、表示形式を「標準」にして表すことができます。この場合は、「8.23」時間となります。
同様に他の日付も計算すると下図のようになります。
このように「出勤時間」と「退勤時間」さえ入力できれば、とても簡単な計算式で時間計算をすることができます。そして、労働時間は休憩時間が含まれないので、休憩時間を入力します。
休憩時間も開始時間と終了時間を入力してきっちり計算してもいいですが、多くの場合は30分単位もしくは1時間単位なので、ここでは時間単位で直接打ち込んでいます。「1.00」は1時間、「0.50」は30分休憩したことを意味しています。
この休憩時間を拘束時間から引くと「労働時間」を求めることができます。
「朝倉いずみ」さんの「9/6」の労働時間は「7.23」時間ということになります。この値は時間単位なので、時給を掛ければ1日の日給を計算することが可能です。
例えば、時給が「1,000円」だとすると「7233.3333」となり、日給「7,233円」を支払うことになります。
ただ、このように小数点が出る場合の切り上げ方や切り下げ方については、法的な話になるので触れませんが、ひと昔前までは、端数の切り捨てが行われていました。
いま現在では、労働に対して1分単位まできっちり報酬は支払わなければならない義務があるようなのですが、よくあるのが「15分」単位での切り捨てです。
上図の例では、「7.23」時間で「7時間14分」の労働時間となっています。14分の報酬は「233.3円」です。これが15分単位で切り捨てとなると、日給は「7,000」になります。1分のために4／1近い報酬が貰えないのです。残業代などの申請で15分単位、30分単位という経験がある方は多いのではないでしょうか。
本来であれば1分単位まできっちり貰わなければなりませんが、ここではこうした分単位での切り捨て、切り上げを関数を使って計算してみましょう。
四捨五入や切り捨て、切り上げの関数といえば、ROUND関数、ROUNDDOWN関数、ROUNDUP関数が頭に浮かぶと思いますが（詳しくは基本操作編 RONUD関数 を参照していください）、本項では異なる関数を使います。
FLOOR（フロア）関数
FLOOR関数は指定した桁数で切り捨てる関数になります。ROUND関数は「桁」を指定するのに対し、FLOOR関数（後述するCEILING関数も含む）は任意の値（基準値）を指定することができます。そのため、ROUND関数より応用の効く関数となっています。
では、FLOOR関数を使って端数を切り捨ててみましょう。FLOOR関数を選択すると、下図のような引数の画面が表示されます。
数値の欄は、切り捨てたい値のセルを指定します。基準値の欄は、切り捨てる基準となる値を指定しますが、この概念は少し難解で「指定した基準値の倍数に最も近い値に切り捨てる」というものです。
言葉の意味は難しいですが、今回の場合の「15分」を例にすると、15の倍数は「0、15、30、45、60（ゼロを含む）」となり、このどれかの倍数に近い値で切り捨てるということになります。
したがって、14分なら「0分」、17分なら「15分」、35分なら「30分」に切り捨てるという意味になります。まずは、わかりやすく「時刻表示」のまま「15分」で切り捨ててみましょう。
下図のように、数値にセルを指定し、基準値に15分となる「"0:15"」と時刻を指定しています。時刻表示で指定するには「""」で囲みます。
すると結果は、15分単位で切り捨てられて「9：00」となりました。オートフィルでコピーすると、下図のようにすべての時刻が15分単位で切り捨てられていることがわかります。
では、もとの表にもどって今度は「時間単位」の値を15分単位で切り捨ててみましょう。
前述のとおり、時間単位で「15分」は「0.25時間」となります。そのため、下図のように労働時間のセル「D7」に対してFLOOR関数の式は「=FLOOR(D7,0.25)」となります。
すると、15分が切り捨てられた労働時間「7.00時間」が計算されました。また同様にオートフィルでコピーすると、下図のようにすべての時刻が15分単位で切り捨てられていることがわかります。
合計金額のセルを見ると、15分切り捨てると1時間以上の差があることがわかります。この合計に時給を掛けると週の給与を計算することができます。
このようにFLOOR関数を使うと、任意の値に合わせて端数を切り捨てることができます。時間計算だけではなく、例えば、100円単位で端数を切り捨てる、契約5件ごとに手当を支給するといった計算も可能です。前者の場合は基準値に「100」、後者は「5」を指定するだけで計算していくことができます。
では、次は逆に切り上げる関数です。
CEILING（シーリング）関数
CEILING関数は指定した桁数で切り上げる関数になります。概念はFLOOR関数と同じで、切り下げか切り上げかの違いになり、「指定した基準値の倍数に最も近い値に切り上げる」というものです。
使い方も同じです。下図のように、CEILING関数の引数の画面はFLOOR関数と同じで、基準値を指定するだけです。
今度は、15分単位で切り上げてみましょう。基準値は同様に「0.25」を指定します。労働時間のセル「D13」に対してCEILING関数の式は「=SEILING(D13,0.25)」となります。
すると、15分が切り上げられた労働時間「1.25時間」が計算されました。この場合は、わずか1分超過しているために15分切り上げとなっています。また同様にオートフィルでコピーすると、下図のようにすべての時刻が15分単位で切り上げられていることがわかります。
合計金額のセルを見ると、こちらも15分の切り上げで1時間ほどの差が出ていることがわかります。この合計に時給を掛けると週の給与を計算することができます。
このようにCEILING関数を使うと、任意の値に合わせて端数を切り上げることができます。
CEILING関数の用途は、時間計算だけではなく、在庫管理などでよく利用されます。例えば、12個入りのダース（箱）の商品があるとすると、100個その商品を仕入れたい場合に「何箱」注文すればよいかを簡単に求めることができます。
正解は、1箱12個入りなので、100個確保したい場合は最低「9箱=108個」注文する必要があります。この場合の関数式は「=CEILING(100,12)」となり、12の倍数で100に近い「108」となります。（FLOOR関数では「96」になります）
この「108」に対して1箱の単位である「12」で割ると「9」を求めることができます。したがって、「=(CEILING(100,12))/12」で一度に必要単位数を求めることができます。
数が少なければ暗算で計算できますが、必要数が1,000以上になるとなかなか計算が難しくなります。下図のように、CEILING関数を利用すれば発注単位数を簡単に求めることができます。
以上で、切り捨て、切り上げの関数については終了です。これらの関数は、あまりメジャーどころではありませんが、使い方も非常に簡単なうえ、分計算や金額の端数の扱い方、在庫管理など多くの場面で利用されています。ぜひ使ってみてください。
更新履歴
- 2021年9月15日
- ページを公開。
参考文献・ウェブサイト
当ページの作成にあたり、以下の文献およびウェブサイトを参考にさせていただきました。
- 文献
- なし
- サイト
- なし
- 管理人のつぶやき
- Twitterのフォローお願いします！