いまさら聞けないExcelの使い方講座

【Excel】時間計算の応用編！エクセルで時間の計算処理を効率化する2つのテクニック

トップスタジオ

2021年1月13日 06:55

Excelで時間の計算がうまくできない

　Excelは、金額を集計して売上表を作成したり、名簿のような大量のデータを管理したりできるだけでなく、時間の計算もできるので重宝している人も多いと思います。皆さんは、Excelを使って時間の計算をする際、端数処理をしなければならない場面に遭遇して困った経験はありませんか。

　滞在時間に応じて利用料金が決まる施設において、その利用金額を求める例を考えてみましょう。1時間あたりの利用料は500円で、滞在時間は30分単位で管理します。例えば10:00に入館して12:14に退館した場合、実際の滞在時間は2時間14分ですが、30分単位で切り上げて2時間30分とします。この場合の利用料金は2時間30分（2.5時間）×500円で1,250円になります（①）。なお、入館時間はあらかじめ30分単位で入力するものとし、端数処理は不要とします。

　このような計算処理では、ポイントとなるテクニックが2つあります。1つ目は時間の切り上げを行うことです。先ほどの「施設利用管理表」の例で、滞在時間「2時間14分」を「2時間30分」としました。このような場合、CEILING関数を使って、30分単位で時間を切り上げる処理を行います。

　2つ目は、時刻データを数値データへ変換することです。「施設利用管理表」の例にある「利用料金」は、「2時間30分（2.5時間）×500円＝1,250円」という式で求められると説明しましたが、「2:30」という時間データのままでは計算できないため、「2.5（時間）」という数値に変換する必要があります。

　今回は、時間の計算で知っていると便利な、この2つのテクニックを解説します。

❶CEILING関数を使って時間データの切り上げをしよう

　はじめに、CEILING関数を使って時間データを切り上げる方法を解説します。まず、「施設利用管理表」の入力項目について説明しましょう。「入館」列（①）には利用開始時間、「退館」列（②）には施設を退館した時刻、「滞在時間」列（③）には実際に滞在した時間がすでに記入されています。なお、「滞在時間」は「退館－入館」で求められます。ここでは、その隣の「滞在時間切り上げ」列（④）に30分単位で切り上げた滞在時間を入力してみましょう。先ほども解説しましたが、「2:14（2時間14分）」滞在した場合、30分単位で時間を切り上げて、「2時間30分」滞在したものとします。

　では、「滞在時間切り上げ」列に、実際に滞在した時刻を切り上げた値を求めていきます。ここでは、CEILING関数を使います。CEILING関数は、1つ目の引数で指定した数値を2つ目の引数で指定した基準値の倍数のうち最も近い値に切り上げる関数で、書式は次のとおりです。

　言葉で説明すると難しく感じるかもしれませんね。実際の値を当てはめて考えてみましょう。先ほどと同じ例で解説すると、「2:14」という時刻データを30分単位で切り上げる場合、1つ目の引数には「2:14」、2つ目の引数には「"0:30"」を指定します。0:30の倍数で一番近い値で切り上げるので、2:30になるというわけです。

　CEILING関数の書式を理解できたら、「施設利用管理表」に数式を入力します。セルE5に「=CEILING(D5,"0:30")」（⑤）と入力します。1つ目の引数には、切り上げたい数値（実際の滞在時間）が入力されたセルを指定します。繰り返しになりますが、今回は30分単位で切り上げたいので、2つ目の引数には「"0:30"」と指定します。ダブルクォーテーションで囲むことを忘れないでくださいね。

　［Enter］キーを押して数式を確定させます。セルE5には「3:30」（⑥）と表示されました。

　オートフィルを使って、セルE5に入力した数式をE列の他のセルにコピーします。セルE5の右下にマウスを乗せるとマウスポインターの形が変わるので、セルE9までドラッグ（⑦）します。

　E列に、実際の滞在時間を30分単位で切り上げた値を入力できましたね（⑧）。

❷時刻データを数値データに変換しよう

　前項では、「施設利用管理表」の「滞在時間切り上げ」列に、実際に滞在した時間を切り上げた値を求めました。最終的に利用料金（G列）を求めるには、時刻データを数値データに変換する必要があります。例えば、30分（00:30）は0.5時間として計算する必要があるからです。

　Excelは時刻や日付のデータを「シリアル値」という値で管理し、シリアル値は24時間（24:00）を「1」として扱います。例えば、12時はシリアル値だと「0.5」、6時は「0.25」になります。したがって、シリアル値で表現されている時刻データに24を掛ければ、数値で表せることになります。少し複雑ですが「時刻データ（シリアル値）を数値に変換するには24を掛ければよい」と覚えておいてください。

　実際に時刻データを数値データに変換してみます。セルF5に「=E5*24」（①）と入力して、［Enter］キーを押します。

　続けて、セルF5を選択した状態（②）で、［ホーム］タブ（③）の［数値］グループにあるドロップダウンリストメニューから［標準］（④）を選択します。

　すると、セルF5には「3.5」（⑤）と表示されます。「3:30」という時刻データを「3.5」という数値に変換できました。

　オートフィルを使って、セルF5に入力した数式をF列の他のセルにコピーします。セルF5の右下にマウスを乗せるとマウスポインターの形が変わるので、セルF9までドラッグ（⑥）します。

　F列に、滞在時間を数値表記にして表示できました。

　最後に利用料金を求めましょう。先ほども説明したとおり、ここでは1時間あたり500円の利用料がかかるので、「滞在時間数値表記×500円」の式で求められます。セルG5に「=F5*500」（⑦）と入力し、［Enter］キーを押します。

　セルG5に利用料金を求めることができました（⑧）（なお、G列には事前に「通貨」の表示形式が設定されています）。オートフィルを使って、セルG5に入力した数式をG列の他のセルにコピーします（⑨）。

　これで完成です。滞在時間を切り上げ、切り上げた時間をもとに利用料金を計算できましたね。