Excel。IF関数で時間内かどうかの判断をしたら、おかしな結果になるのでどうしよう。
＜IF+TIMEVALUE関数＞
MOS(マイクロソフト オフィス スペシャリスト)の試験を受けるときには、
その模擬問題をやってから挑戦していただくのですが、
その模擬試験の管理する表を作成していて、
模擬試験時間内に終わったのかどうかの判断をしようとしたら、ちょっと苦労するのと、
さらに判断がおかしいし…
ということで、
今回は、時間内なのか？
それとも、タイムオーバーなのか？を判断する表を作るときの注意点を書いていきます。
では、下記の表があります。
このように作りたいわけですね。
F列は、E列の所要時間と比べて、50分以内なら、時間内。
そうでなかったら、タイムオーバーと表示する数式をF列に作っております。
では、F5をクリックして、IF関数ダイアログボックスを表示しましょう。
論理式には、50分以内なので、E5<=0:50 なんか、正しくありません。と表示されていますが、
先へいきましょう。
真の場合には、”時間内”
儀の場合には、”タイムオーバー”
として、OKボタンを押してみると…
エラーとなりましたね。
数式には、直接時刻を入力することが出来ないので、正しくないとExcelがいってきた訳ですね。
では、どうしたらいいのか？というと、
”0:50”とダブルコーテーションで囲んであげる必要があります。
そうすることで、文字型になるので、数式で使うことが出来るのです。
ですので、論理式を、E5<=”0:50”と修正してみましょう。
数式は、
=IF(E5<="0:50","時間内","タイムオーバー")
となりますので、オートフィルを使って数式をコピーします。
これで完成。と思ったら…。
なんと、全部判断が時間内になっているではありませんか！
50分以内なはずなのに？
この原因は、先ほどのダブルコーテーション。
これを付けたら文字になったわけですよね。
E列の数値よりも当然大きいわけですね。
E5は、0:49と49分に見えますが、実際のデータとしては、0.0340278 ですから。
つまり1以下。
当然文字よりも小さい数値なので、”時間内”という結果が算出されるというわけです。
では、どうしたらいいのでしょうか？
そこで登場するのが、救世主。
TIMEVALUE関数
なのです。この関数は、文字列になっている時刻を、本来の時刻に変換してくれる関数なのです、
しかも表示は時刻表示形式にしてくれるのです。
つまり、時間どうし比較できるようになるのです。
では、数式を修正していきましょう
F5の数式
=IF(E5<="0:50","時間内","タイムオーバー")
を
=IF(E5<=TIMEVALUE("0:50"),"時間内","タイムオーバー")
と修正して、オートフィルで数式をコピーしてみましょう。
今度は、ちゃんと、判断されて、タイムオーバーが表示されましたね。
このように、時間をつかった比較判断の場合には、
TIMEVALUE関数を使ってあげるといいかもしれませんね。
