先日、食品加工業のお客さまが新しいPOS(Point Of Sale)システムを導入されたのですが、さっそく困ったとのことで相談がありました。
従業員の打刻機(タイムカード)と連動しているPOSシステムで、従業員ごとの出退勤記録をCSVファイルで出力してくれるようです。そこまでは良かったのですが。。。
通常、CSVファイルをPCにダウンロードして、ダブルクリックするとEXCELが開きます。お客さまはそのデータを加工して、月間の勤務時間を計算し、時給をかけて月給を計算しようとしていたようです。
と、この段階で電話がありました。
「時間を計算しようとしたらワケわかんない時間が出るんですよ〜(泣)」
「んで、時給をかけても、どう考えても給料が少ないんですよ〜(泣)」
とのこと。
これはEXCELで時間を計算しようとしたときの「あるあるネタ」、よくあることなのです。が、初めてこの状況になるとパニックになるのも仕方ありません。
というわけで、ここからEXCELの時間計算の仕組みと対処方法を解説します。
このように、たとえば、
10時間(セルB16に 10:00 が入力されている)と、
15時間(セルC16に 15:00 が入力されている)を足したい場合、
合計を求めるセル(ここではセルD16ということにしましょう)に、
= B16 + C16
と入力しますよね。ここまでは何も問題ありません。
で、結果はもちろん 10時間 + 15時間 = 25時間だよね、25:00だよね!と思いきや、セルD16にどう表示されるかというと、
1:00
です。
これは、EXCELの表示形式が24時間制になっているためです。時計と同じで、24時を過ぎた時点で時間はゼロにリセットされるのです。
というわけで、25:00は24:00から1時間経過後なので、EXCELは当たり前のように1:00と表示しているのです。
では、25:00と表示させるにはどうしたらいいのか?
答えは、「セルの書式設定」をいじる、です。
では、合計を表示したいセルD16で右クリックをして、「セルの書式設定」を選んでください。
このように、表示形式が「分類 : ユーザー定義」「種類 : h:mm」となっていると思います。
この「種類」の部分を、
[h]:mm
に書き換えて、OKボタンをクリックすると。。。。望み通りセルD16には25:00と表示されます。
ここの設定で使っている文字の意味と最大値は次の通りです。
- hは時(Hour) → 最大 24
- mは分(Minute) → 最大 60
- sは秒(Second) → 最大 60
この最大値を突破するのが [ ](角括弧・大括弧)です。
この角括弧(大括弧)でhなどの文字をくくってやると、24や60といった最大値を超えて数値を表示することができるようになります。
ちなみに、この文字は1つか2つを並べて書く決まりになっていて、
1つだけ、hと指定すると、3:00
2つ、hhと指定すると、03:00
のように、何桁で表示するのかを指定することができます。
。。。さて、合計時間の表示は片付きましたね。
次の目標はこの合計時間に時給をかけて、月給を計算することでした。
では、いまセルD16に表示されている25:00に、例えば、時給1,000円をかけてみましょう。
ここでは月給を計算するためのセルをE16としますね。
答えは暗算でもできますよね、当たり前のように
25 × 1,000 = 25,000円になりま。。。せん(笑)
セルE16に表示される値は、
25000:00
です。
よりによって、にまんごせん時って(笑)
でも仕方ありません。EXCELは指示された通り、25時を1,000倍しただけですから。
さぁ、どうしましょう?
またもや答えは、「セルの書式設定」をいじる、です。
実は、今回はそれだけでは片付かないのですが、まずは書式設定を変えましょう。
セルE16で右クリックして、「セルの書式設定」を開いてみてください。
EXCELは賢いので、先ほどセルD16で設定した、[h]:mm を引き継いでいるはずです。
しかし今回は時間ではなく、金額を表示したいんでしたよね。
こちらの望みをEXCELに伝えるため、「分類」の中の「通貨」を選択してください。
(または、「数値」を選択しても構いません。「数値」の場合は「桁区切り(,)を使用する」にチェックを入れると見やすいですよ)
そして「セルの書式設定」の「OK」ボタンをクリックすると、
セルE16の表示は。。。。
¥1,042
となっているはずです。
さっきよりダメダメになってるよ!
にまんごせん時 の方がまだマシだよ!
と思いましたか?
安心してください、前進してますよ(笑)
<ここから、ややこしいのが苦手な方は飛ばしてください>
実はEXCELの中では、時間をシリアル値というもので管理しています。
シリアル値というのは、1日を1で表す数値です。
(正確には、1900年1月1日を1として、それからの経過日数を表した数値ですが、1日が1で考えた方が分かりやすいです)
25時というのは1日と1時間なので、1と1を24で割ったものの合計がシリアル値になります。
1 + ( 1 ÷ 24 ) = 1.04166… ≒ 1.042
1.042 × 1,000 = 1,042 ですので、セルE16には ¥1,042が表示されたわけですね。
シリアル値になっている時間データを〇〇時間という形にするにはどうしたらいいのか。
これがわかれば問題は解決しますね。
答えは簡単です。シリアル値に24をかければいいんです。
<ややこしいのが苦手な方、おかえりなさい♪>
というわけで、
セルD16の値(25:00)に24をかけた上で、
時給の1,000円をかけてあげればいいのです。
セルE16に、
= D16 * 24 * 1000
と入力してください。
どうですか?
¥25,000
と、正しく表示されたと思います。
ややこしい部分もあったかもしれませんが、
EXCELにはこちらの意図をしっかりと伝えてあげなくては思い通りに動いてくれません。
逆にいうと、こちらの意図さえしっかりと伝えられれば、
EXCELはフルにその能力を発揮してくれる、ということです。
やや長文でしたが、拙い説明に付き合っていただき、ありがとうございました。
また実際の例を元に、EXCELの解説をUPさせていただきますね。