メディア個別 【エクセル関数】CEILING関数を使えば時刻の切り上げ計算も簡単 | editeur エディトゥール

editeur

検索
サービス終了のお知らせ
第20回 | デキる男の必修科目──大人のエクセル一問一答

【エクセル関数】CEILING関数を使えば時刻の切り上げ計算も簡単

金額や時刻の計算を行う際、場合によっては端数を切り上げた計算結果を求めたい場合も多いだろう。なかでも特に面倒なのが時刻の切り上げ計算。時給や時間に応じたサービス料金の計算など必要なシーンは結構多いが、手動で計算するのはかなり面倒なものだ。そこで役立つのが関数を使った計算方法だ。数式のコツをおぼえておけば、面倒な切り上げ計算も自動で簡単に済ませることができるようになる。さっそく、今回の問題に挑戦してみよう。

問題:30分単位で300円加算される駐車場の駐車料金を、入庫時刻と出庫時刻をもとに関数を使い計算せよ。

難易度:☆☆☆☆★
実用度:☆☆☆★★
目標ステップ数:4

解答:

指定した単位で数値の切り上げを行うには「CEILING(シーリング)」関数を用いる。エクセル2013以降では、このほかに拡張版となる「CEILING.MATH(シーリング・マス)」関数を使うこともできるが、基本的な使い方は同様だ。今回の問題では、時刻を30分単位で切り上げるためにCEILING関数を使っている。この場合、時刻の記述方法がポイントとなる。さっそく標準解答の手順を見ていこう。

●STEP1

数式を入力したいセル(ここではE2)を選択し、「数式バー」に「=CE」と入力。表示される関数の候補から「CEILING」をクリック。

●STEP2

出庫時刻から入庫時刻を引いた実時間が入力されているセル(ここではD2)をクリックしてから、半角で「,"0:30")」と入力。数式全体が「=CEILING(D2,"0:30")」となったら、Enterキーを押す。なおセルD2の「実時間」は「=C2-B2」という数式で求めている。

●STEP3

これで、30分単位で切り上げた「単位時間」が計算される。続けて、駐車料金の計算を行う。駐車料金を計算したいセル(ここではF2)を選択し、「数式バー」に半角で「=E2/"0:30:0"*300」と入力しEnterキーを押す。

●STEP4

30分300円の駐車料金が計算される。数式が入力された「単位時間」と「駐車料金」のセルを選択。選択範囲の右下にポインタをあわせダブルクリックすると、オートフィル機能が働き表組の空欄が埋まる。

【今回のまとめ】

エクセルでは、セルに「10:30」というように半角で入力することで、自動的に時刻として認識される。しかしその際、入力された時刻は「シリアル値」という数値に変換される点に注意が必要だ。時刻の場合は0時を「0」、24時を「1」として、24時間を0~1までの少数に変換される。時刻の計算を考える際には、このルールを踏まえて行うようにしよう。なお、表組で時刻を正しく表示させたいなら、「数値の書式」で「時刻」を選ぶ。今回の表組では、秒を省いた時刻の表示形式にしている。

今回用いたCEILING関数は、「=CEILING(数値,基準値)」という基本構文となる。CEILING関数では、単位となる「基準値」で指定した値の倍数のうち、もっとも近い値に「数値」を切り上げる。

今回の問題の場合は「30分」を単位とする。時刻を数式で用いる場合は「=CEILING(D2,"0:30")」というように、「””」で囲む点に注意が必要だ。

さらに今回のポイントとなるのは、STEP3で行っている駐車料金の計算方法だろう。「=E2/"0:30:0"*300」という数式は「セルE2に入力されている時刻を30分で割った結果に300を掛ける」という意味になる。この場合も時刻を「””」で囲むわけだが、計算で時刻を使う場合は「"0:30:0"」というように、秒単位まで記述しておく必要がある点をおぼえておこう(ちなみに、「"0:30"」というように省略しても、自動的に「"0:30:0"」と補われる)。

また、今回のように切り上げ計算をする際は問題ないのだが、先ほども書いたようにエクセルでは時刻を少数のシリアル値で管理しているため、端数のある時刻の計算を行う場合、まれに誤差が生じることがある点も、おぼえておくと良いだろう。

Text by Toshiro Ishii

ピックアップ

editeur

検索