将带小数的数值转换为代表时间的方法

gouweicao78

原帖：http://www.officefans.net/cdb/viewthread.php?tid=85476&page=1&extra=page%3D1

c2gNZgD4.rar (10.46 KB, 下载次数: 74)

2007-11-28 02:11 上传

点击文件名下载附件
[分享]将带小数的数值转换为代表时间的方法

A1:A18是一些设置了2位小数格式的数值，比如3.21、3.00等分别表示3分21秒、3分0秒。现欲求平均时间。

注意：整数部分不得超过2位数

解法一：数学计算法  

=SUM(INT(A1:A18)+MOD(A1:A18,1)*100/60)/COUNT(A1:A18)/60/24 
  取得小数部分*100/60，换算成与整数相同进制（单位为：分钟） 
  最后再除以60得到小时再除以24得到天数——真正的时间序列值 ——58 个字符

  将*100改为/1%，结合*100/60就是/60%，简化如下： 

=SUM(INT(A1:A18)+MOD(A1:A18,1)/60%)/COUNT(A1:A18)/1440 ——54个字符

解法二：文本处理法  
   

=AVERAGE(--("0:"&SUBSTITUTE(TEXT(A1:A18,"#.00"),".",":"))) 
  利用Text将A列数据统一为0.00的格式（注意A2是3，而不是3.00） 
  利用Substitute将小数点替换为冒号，并在前面加0:变成时间，最后--转换为数值后求平均值 ——58个字符

=AVERAGE(--SUBSTITUTE(TEXT(A1:A18,"0!:00.!:00"),".",))
  利用Text将"0:"也做到格式中，注意感叹号为强制显示下一字符的用法，也可用\——54个字符

节衣缩食，勒紧裤带，省略15个字符：

=AVERAGE(--TEXT(A1:A18/1%,"0!:00!:00"))
  利用/1%（即*100）将数据放大100倍，小数点终于退休了，也就不用Substitute替换了，大大缩短了公式——39个字符

=AVERAGE(--TEXT(A1:A18/1%,"0!:00"))/60
  利用时间进制，先求得小时数，再/60，又省了1个字符——38个字符

=AVERAGE(TEXT(A1:A18/1%,"0!:00")/60)
  利用四则运算可以转化文本型数字为数值型的特性，两颗洁白的门牙--号也光荣下岗了——36个字符

[此贴子已经被作者于2007-11-28 2:29:12编辑过]

学良

谢谢gouweicao78版主分享!

[此贴子已经被作者于2007-11-28 8:15:55编辑过]

xmxm18

学习

xowi99

将*100改为/1%，结合*100/60就是/60%

AVERAGE(TEXT(A1:A18/1%,"0!:00")/60)

学习了

gouweicao78

再补充一个，虽然字数没有再少，但有利于大家对时间格式的理解：

=AVERAGE(TEXT(A1:A18/1%,"0时00分")/60)

也就是说，利用Excel支持的时间格式“h 时mm分”来完成，可惜Excel不支持“m分ss秒”自动识别为数值，所以不能再减那一个字符了。——这就不需要用感叹号!或者斜杠\来占位了。

Excel支持的时间日期格式还有：XX年XX月XX日、XX月XX日（年份默认为当年）。

zhuying0511

学习了...不过好像不怎么看的懂,再仔细看看。

山桑弓

已下载，TEXT函数用得好，/1%，/60用得精彩，谢谢。