Excelで数値を時間数に変換する関数について

office365を使っている者です。
Excelで数値を時間数の表示に関数で変換する方法があればご教示願います。

例えば，あるセルにおいて，=VLOOKUP($B2,出力!$B:$Z,13,0)+VLOOKUP($B2,出力!$B:$Z,14,0)という数式（以下「式A」）で4.17という数値を取得したとします。（vlookupで出力シートから時間の入ったセルを引っ張り，それらを足しています）
その同じセルにおいて，4.17を４時間10分又は4:10に関数で変換する方法があれば，それぞれにつきご教示いただけると幸いです。

式Aから「.」より前の部分をLEFT,FIND等で抽出し，「時間」の文字列を足し，「.」より後の部分に60をかけて小数点以下を四捨五入し，それに「分」の文字列を足すというような数式でもできそうですが，なんとも原始的な気がします。
また，セルの書式設定で時刻にしても，適切に表示されません。

よろしくお願いいたします。

質問者からの補足コメント

• 失礼しました。
  Mround関数で希望通り四捨五入ができたようです。

  No.2の回答に寄せられた補足コメントです。 補足日時：2024/08/22 16:22

• 失礼しました。
  MROUND関数でうまく処理できたようです。

  No.3の回答に寄せられた補足コメントです。 補足日時：2024/08/22 16:23

No.2 ベストアンサー 回答者： fujillin 回答日時： 2024/08/21 19:02

こんばんは

ご提示の「4.17」の数値が何を意味するのか説明がないので、勝手に想像しました。
時間単位での数値と考えると「4.17」は「4時間10分12秒」に該当します。
（以下は、これで良いと仮定しての回答です）

端数分の「12秒」をどう扱うのかも不明ですけれど、普通にシリアル値（＝エクセルの時刻値）に換算するなら
　=式Ａ/24
とすればシリアル値に変換できますので、セルの書式設定を「時刻」あるいは「hh:mm」や「hh時間mm分」等にしておけばご希望の表示になります。

シリアル値ではなく文字列としてセルに表示したいのであれば、さらにTEXT関数を用いることで、文字列として「4時間10分」を得ることも可能です。

この回答へのお礼

ありがとうございます。
教えていただいたとおりにすると4時間10分はうまくでましたが，1.58だと1時間34分と出てしまいます。1時間35分で表示させたいのですが，良い方法があればご教示願います。（四捨五入ができていないようです）

お礼日時：2024/08/22 16:10

No.6 回答者： mike_g 回答日時： 2024/08/23 07:17

[No.2お礼]へのコメント、

qazxcvfr4さん、
》 1.58だと1時間34分と出てしまいます。
》 1時間35分で表示させたいのです
参考までに、その理由を“論理的に”説明願います。m(_._)m

この回答へのお礼

今回の質問させていただいた件では，0.58と表示された時間は，1時間34分ではなく１時間35分だったからです。

お礼日時：2024/08/23 16:38

No.5 回答者： fujillin 回答日時： 2024/08/22 16:55

No2です。

＞1.58だと1時間34分と出てしまいます。1時間35分で表示させたいのですが，
＞良い方法があればご教示願います。
数値の意味に関しては、No2の解釈で良いものと仮定しての回答になりますが・・

例えば、1.583 を同様に時分秒に換算すれば、「1:34:59」になります。
これを、時分で表示すれば、そのまま「1時間34分」と表示されます。
シリアル値の場合、通常の数値とは異なり、指定の表示値以下の部分を四捨五入してくれるようなことはありません。
「年月日」の値で「年月」だけを表示しているような場合に、月の後半は翌月表示になってしまったら、かえって不都合が生じますよね？


分単位で四捨五入なさりたければ、自分で計算すればよいだけのことです。
1分単位にすれば良いののなら、例えば上記の「1.583」であれば
　=MROUND(1.583/24,"0:1:0"*1)
のようにして四捨五入すれば、「1時間35分」の値に換算されます。

上記のような計算を行えば、
　1.575（＝1:34:30）は「1時間35分」に
　1.574（＝1:34:26）は「1時間34分」に
表示されるようになります。

この回答へのお礼

ご教示いただきありがとうございます。

お礼日時：2024/08/23 16:29

No.4 回答者： angkor_h 回答日時： 2024/08/22 16:43

No.3です。

> 四捨五入ができていないのか，うまく表示されないことがあります。
シリアル値に対する「年月日時分秒」表示では、四捨五入はされず、
「YYYY/MM/DD hh:mm:ss」の指定部分を表示するだけです。
四捨五入したい場合は、
シリアル値に対して、ユーザーが行わないといけません。

秒を四捨五入して分単位で表示したい場合は、
シリアル値に対して常に「0.5分=30秒」を足せばよいです。
　0.5分=(1/24)*(1/60)*(1/2)=0.000347222

ただ、これは見た目(表示だけ)をそのようにしたもので、
元の数値(シリアル値)自体が変わっているので、
他の統計に使う際は、0.5分加算前のシリアル値を使わないといけません。

この回答へのお礼

ありがとうございます。

お礼日時：2024/08/23 16:28

No.3 回答者： angkor_h 回答日時： 2024/08/21 19:03

> 4.17という数値を取得したとします。

日付を示すシリアル値は、
1日が「1」なので、1時間は「1/24」になります。
数値「4.17」「4.17時間」としたシリアル値に変える場合は、
×(1/24)をすればよいです。
そして、そのセルを書式設定で、「h:m」を指定します。

この回答へのお礼

ありがとうございます。しかし，一つ前の方のお礼にも書きましたが，四捨五入ができていないのか，うまく表示されないことがあります。

お礼日時：2024/08/22 16:12

No.1 回答者： zongai 回答日時： 2024/08/21 18:57

=TEXT(A1/24,"h""時間""m""分""")

このあたりではどうでしょうか？
数値「1」は1日（24時間）なので、24で割ることで24時間の内の4.17時間であり、あとは書式（TEXT関数）で時間にします。

他によくあるのはこんな式でしょうか。
=INT(A1)&"時間"&TEXT((A1-INT(A1))*60,"0")&"分"
=INT(A2)&"時間"&TEXT(MOD(A2,1)*60,"0")&"分"

この回答へのお礼

ありがとうございます。
しかし，A1というのが「VLOOKUP($B2,出力!$B:$Z,13,0)+VLOOKUP($B2,出力!$B:$Z,14,0)」のことだと思いますが，そうすると式が非常に長くなってしまいます。。

お礼日時：2024/08/22 16:07