在Google Sheets中计算m:s:ms格式田径成绩的平均时间

我之前处理田径赛事数据的时候也踩过这个坑——Google Sheets对这种m:s:ms格式的时间字符串完全不买账，没法直接用AVERAGE函数计算。下面给你两个实用的解决方案，都是我实际用过的，处理141条数据完全没问题：

方法一：用内置公式批量转换计算 #

这个方法不需要额外脚本，直接用Google Sheets自带的函数就能搞定，步骤很清晰：

1. 转换单条成绩为秒数：
   在空白列（比如B列）的第一个单元格（B1）输入以下公式，把A1替换成你存放成绩的单元格：

   =(INDEX(SPLIT(TRIM(A1), ":"), 1)*60) + INDEX(SPLIT(TRIM(A1), ":"), 2) + (INDEX(SPLIT(TRIM(A1), ":"), 3)/1000)
   

   公式解释：

   • TRIM(A1)：先清理成绩字符串里可能存在的多余空格
   • SPLIT(A1, ":")：把m:s:ms按冒号拆分成分钟、秒、毫秒三个部分
   • 最后把分钟转成秒，加上秒数，再把毫秒转成小数秒，得到总秒数（数值型，可用于计算）

2. 批量转换所有成绩：
   把鼠标移到B1单元格右下角，当出现十字形填充柄时，按住左键下拉到B141，所有成绩就自动转换成可计算的秒数了。

3. 计算平均值并转回m:s:ms格式：

   • 在任意空白单元格（比如C1）输入=AVERAGE(B1:B141)，得到平均秒数
   • 如果需要把平均值转回m:s:ms格式，在D1输入：

     =TEXT(C1/86400, "mm:ss.000")
     

     这里86400是一天的总秒数，Google Sheets的时间本质是天数占比，所以要除以这个数再用TEXT格式化。

方法二：自定义函数（适合格式复杂的情况） #

如果你的成绩格式有一些不规范的地方（比如毫秒位数不固定、部分数据缺前导零），用自定义函数会更灵活：

1. 添加自定义脚本：
   打开Google Sheets，点击顶部菜单栏的「扩展程序」→「Apps脚本」，在弹出的编辑器里粘贴以下代码：

   function TIME_TO_SEC(timeStr) {
     // 先清理字符串中的多余空格和异常字符
     const cleanedStr = timeStr.trim().replace(/[^0-9:]/g, "");
     const parts = cleanedStr.split(":");
     
     // 处理格式不规范的情况，确保有三个部分
     const minutes = parts.length >=1 ? parseInt(parts[0]) : 0;
     const seconds = parts.length >=2 ? parseInt(parts[1]) : 0;
     const milliseconds = parts.length >=3 ? parseInt(parts[2]) : 0;
     
     return minutes * 60 + seconds + milliseconds / 1000;
   }
   

   点击编辑器顶部的保存按钮，给这个脚本起个名字（比如TrackTimeConverter）。

2. 使用自定义函数：
   回到表格，在B1单元格输入=TIME_TO_SEC(A1)，下拉填充到B141，之后的平均值计算和格式转换和方法一一样，用AVERAGE(B1:B141)和TEXT函数即可。

内容的提问来源于stack exchange，提问作者Colin Koenig