月初，高中学校的体协指导老师联系我，问说下周日是否有时间来当个裁判？接力赛缺个裁判员。具体要求就是做技术支持，即时给出比赛数据，制作成绩证书。觉得难度不大，虽然没有学过 Excel，但是现学现卖应该是没有问题的。所以接下了这个 case，并开始了准备工作。
先说结果，34 个队伍，204 名运动员，没有人对比赛成绩提出疑议。相比起去年人工计算时差、排名算了几天的情况，今年在比赛结束后 30 分钟左右，就发出了所有成绩排名。相比起同类小型赛事，没有制作成绩证书。本次比赛给每位运动员都制作了成绩证书，部分跑友还将证书一并发在了朋友圈，或者微信群中，大家对这次的比赛满意度较高。
首先介绍一下赛事背景，因为这是一场马拉松接力赛，正常马拉松全程是 42.195 公里，如果把它办成一场接力赛。一个队伍 6 个人，每个人需要跑 7 公里。而在我们区里，正好有这样的一个 U 字形场地，来回一圈刚刚好是 7 公里左右。
裁判组会分为 3 组，每组裁判配 4 个人，3 个人分工按下秒表计时，1 个人摄像记录对应的队伍。所以这样就会生成两组数据，一组是计时时间，一组是对应的队伍名字。而我的工作，就是将这两组数据汇总对应起来，然后填入 Excel 表格中对应的位置，最后计算出相关的时差、配速数据。
表格制作
明确需求
基本情况介绍完了之后，再看看老师安排的需求：
- 因为报过来的是总时间，所以需要计算每名运动员的个人计时
- 然后自动求出每位运动员的配速
- 在比赛结束后，给出个人全体排名、团队排名、男子组排名、女子组排名
- 给每位运动员制作完赛成绩证书
设计表格
需求明确之后，就可以开始设计表格了。先填入所有需要记录的关键信息，如下图所示。
其中跑团战队名称、姓名、性别前面三项是固定的，在此之前已经通过其他表格收集了这些信息，所以现在要做的，就是将这三项信息复制进来，得到以下结果。
参赛号码
由于每个战队都是 6 名队员，所以号码编排就是用「队伍序号+棒次序号」即可。在第一个号码中填入 0101，但是会发现 0101 被 Excel 识别为了数字，而自动忽略了前面的 0，所以点击第 2 列上面的 B，按下 ⌘+1，调整整列单元格的格式，将它设置为文本，并重新输入就可以正常显示 0101 了。
然后拉到 0106，之后如法炮制再生成到 0206，本以为这样选中两大组数据之后，Excel 就能根据这个规则自动生成其他号码，但是经过测试发现只会在 0101 到 0206 之间循环，并没有按照预期结果生成序号。
既然 Excel 在这方面不给力，那么就用 Python 写个 BIBnumberGenerator.py 脚本来实现。两分钟编写调试之后，就得到了以下的参赛号码生成代码，只是使用了简单的两层 for 循环就实现功能。
teamCount = 34
memberCount = 6
for teamNum in range(1,teamCount+1):
for memberNum in range(1,memberCount+1):
teamPrefix = '' if teamNum >= 10 else '0'
print(teamPrefix + str(teamNum) + '0' + str(memberNum))
在终端里使用 python3 BIBnumberGenerator.py 执行脚本，输出结果。后面跟着的 pbcopy 是 macOS 自带的命令，配合管道符号将 python 脚本生成的序号，直接复制到系统剪切板，然后粘贴到 Excel 表格中即可。
用时时间
用时时间，是最难处理的一个单元格。由于裁判组那边给的是从出发到交接棒的总时间，所以在这里需要使用公式计算时差。为了实现这个需求，就需要获取当前行的时间，再减去上一行的时间，这样才能计算时差。如下图所示，令 fx = E3-E2，这是可以获取到正确时间的。但是有一个问题：这个公式并不通用，在其它行输入这个公式，永远都是等于 31:34。难不成每个单元格都手打一遍公式？这是非常低效的做法，所以必须要找到更好的解决方法来实现。
早有耳闻，Excel 的公式很强大，既然计时时间的位置是固定的，能不能通过单元格的绝对位置来获取值呢？答案当然是可以的，使用 ROW() 函数即可。获取到当前行之后，就可以配合列号间接获取值。所以新的问题是：如何通过行号 + 列号，获取单元格内的值，看到 如何在Excel中基于行号和列号获取单元格值？文章中介绍，使用下面这个公式，就可以通过行号 + 位置取值：
=INDIRECT(ADDRESS(行号,列号))
其中行号可以通过 ROW() 函数获得，而计时时间列是固定的 5，使用 ADDRESS() 获取当前行第 5 列的绝对引用，再通过 INDIRECT() 获取引用单元格值，所以不难构造出如下公式：
=INDIRECT(ADDRESS(ROW(),5))
公式演变过程如下图所示，先获取到了 $E$3 绝对地址，再通过 INDIRECT() 间接访问 $E$3 获取到 01:12:09。
接下来就是上一行的单元格时间获取。既然有了上面这个例子，那么只要在 ROW() 后面加一个 -1，就能够访问到上一行的计时时间了。如下图所示，修改后的公式用时变成了 40:35.
验证两个时间都可正常获取到之后，就可以做一个减法了，很自然地构造出如下公式，使用当前行时间减去上一行的时间，成功计算出了前后棒时差是 31:34.
=INDIRECT(ADDRESS(ROW(),5))-INDIRECT(ADDRESS(ROW()-1,5))
但是此时还不能高兴地太早，还有一个问题：如果上下两队队名不一样，难道也就这么减下去了吗？那这个表格就出错了。所以还需要一个判断条件，判断上下两行的队名是否相等，如果相等才做减法，否则就直接返回当前行的计时时间，使用伪代码表示如下：
if (当前行队名 == 上一行队名):
return 当前行计时时间 - 上一行计时时间
else (当前行队名 != 上一行队名):
return 当前行计时时间
根据微软官方的 IF 函数介绍，通过介绍可以了解到，IF 函数的基本语法如下：
IF(判断语句, 结果为真的表达式, 结果为假的表达式)
对于后面两个表达式，在上文中已经给出了答案。现在的问题又转为了，如何构造判断语句？显然这是一道送分题，只需要将上文的知识点，将列号替换一下即可。在 Excel 表格中队名所对应的列号是 1，所以就可以构造出如下的判断语句：
=IF(INDIRECT(ADDRESS(ROW(),1))-INDIRECT(ADDRESS(ROW()-1,1)),"同队","不同队")
可以看出，该公式很好地实现了是否同队的判断功能，接下来要做的就只是把「同队」和「不同队」替换为相应的公式即可，替换后的公式如下所示：
=IF(INDIRECT(ADDRESS(ROW()-1,1))=INDIRECT(ADDRESS(ROW(),1)), # 判断队名是否相等
INDIRECT(ADDRESS(ROW(),5))-INDIRECT(ADDRESS(ROW()-1,5)), # 如果相等上下行时间相减
INDIRECT(ADDRESS(ROW(),5))) # 如果不相等返回当前行的计时时间
可以看出，该公式和预期的一样，完美地实现了所需要的功能。对于野牛战队 0201 号运动员，因为上下两队队名不相等，所以直接返回了 0201 行的计时时间 32:46 作为用时。
在基本测试通过后，删除所有的「计时时间」，开始模拟现场实际输入时间的状态。但是刚刚输入一个时间之后，不对的情况就产生了。如下图所示，第一个运动员输入计时时间之后，可以正确计算出用时。但是此时第二行的用时，却变成了「########」。稍微思考一下就可以想到，这是因为第二行还没录入时间，使用了一个空值去减第一行的计时时间，所以得出了「########」这样的异常结果。
所以此处还可以进行一下优化，对 IF 语句后面两个表达式进行修改，只有当前行不为空，才进行减法计算，否则返回空值。重复利用上面提到的知识点，构造出如下的公式：
队名相同时的表达式
IF(INDIRECT(ADDRESS(ROW(),5))="","",INDIRECT(ADDRESS(ROW(),5))-INDIRECT(ADDRESS(ROW()-1,5)))
队名不同时的表达式
IF(INDIRECT(ADDRESS(ROW(),5))="","",INDIRECT(ADDRESS(ROW(),5))))
整合后最终的表达式
=IF(INDIRECT(ADDRESS(ROW()-1,1))=INDIRECT(ADDRESS(ROW(),1)),
IF(INDIRECT(ADDRESS(ROW(),5))="","",INDIRECT(ADDRESS(ROW(),5))-INDIRECT(ADDRESS(ROW()-1,5))),
IF(INDIRECT(ADDRESS(ROW(),5))="","",INDIRECT(ADDRESS(ROW(),5))))
如下图所示，更新之后的公式，即使当前行没有填入数值也不会报错了，至此最复杂的时差计算公式终于完成了。为了方便读者理解，此处一并提供伪代码表达式，辅助理解：
if (当前行队名 == 上一行队名):
if(当前行计时时间为空):
return ""
else:
return 当前行计时时间 - 上一行计时时间
elif (当前行队名 != 上一行队名):
if(当前行计时时间为空):
return ""
else:
return 当前行计时时间
配速
在上一步骤中计算出用时之后，配速就是送分题了。直接引用前面的「用时时间」除以距离，就可以得到配速了。这个公式虽然简单，但是还是有一个细节点需要注意。点击「配速」所在的列，按下 ⌘+1 调整单元格格式，选择自定义格式，在自定义格式中输入：mm′ss″，这是标准的角分符号。不要为了省事直接使用半角英文的 '和 "来表示，如果要做事就得认真做，做好每一个细节。
到此为止，表格的制作终于可以暂告一个段落，删除所有测试数据，得到一张干净的参赛运动员表格，等到了比赛的时候直接拿到现场使用即可。接下来要做的工作是制作成绩证书。
比赛现场
早上 5:50 出发，赶往比赛场地，开始布置现场。总裁判长分发免责声明书和签到表，等待各队队伍从各区县赶来。早上 7:40 基本完成签到，合影留念之后就要开跑了，下图是此次比赛的大合影图。
这时就要开始信息统计的第一步工作了。核对签到表与 Excel 成绩表，确保每个队伍人员的名字正确无误，如果有出现替补换人的情况，也需要在 Excel 表格中及时进行修改。早上 8:00 随着比赛开始，人名核对也完成了，接下来就是等着裁判组给出第一棒的成绩，然后让 Excel 自动计算数据，并及时反馈到运动员群里。
由于这次的计时方式比较特别，计时裁判和记序裁判各自独立工作，所以报到作者这里的会有两份数据，一个是时间数据，另一个是过线运动员所属团队数据，老师让学姐帮我一起处理数据，她负责将两份数据对应起来，我负责输入数据，两份数据如下所示。至于为什么不使用芯片计时，单纯是因为贵，一套下来就是上万块钱。
棒次表，可以看出「秀屿健跑一队」是毫无疑问的冠军。
计时表，可以看出第一名和最后一名，整整差了一个小时之多。
赛后数据处理
当比赛完成后，就要开始输出四项排名了，分别是：个人排名、团队排名、男子排名、女子排名。使用 Excel 的高级筛选功能，就可以很简单地实现这个需求，接下来就来看看具体是如何操作。
全体排名
全体排名需要使用 Excel 的 RANK() 函数，根据微软的 RANK() 文档 得知，它的函数格式如下所示：
RANK(number,ref,[order])
Number 是需要排序的数据，ref 是全体的数据，其中 [order] 参数有 0 和 1 两种选项，0 是降序，1 是升序，默认是使用降序。如下图所示，是比赛全部完成后的成绩表格。以用时作为排序的依据，用时在第 6 列，采用升序的方式排列。
其中数据源的单元格，需要用绝对引用，而不是默认的相对引用。如果使用相对引用，公式是无法对整列通用的。关于这两种引用的区别，可以阅读官方的 在相对引用、绝对引用和混合引用间切换 进行了解。经过多次修改之后，可以得到如下的公式：
=RANK(INDIRECT(ADDRESS(ROW(),6)),$F$2:$F$205,1)
团队排名
到了团队排名这里，就需要使用到高级筛选了。观察上图的表格可以看到，团队总成绩是在第 I 列，而且第 I 列中，只有最后一名队友才有该数据。所以思路就是使用高级筛选，筛选出所有I 列非空的行，并将其复制到其他的位置，避免污染原始的数据源。
在功能栏上选择「数据 > 高级」，点击打开高级筛选。选择第二个「将筛选结果复制到其他位置」，可以看到此处要求输入「列表区域」和「条件区域」两个参数。列表区域，也就是筛选运动员们的成绩已经有了。但是还差个条件区域，所以暂时关闭该窗口，在 Excel 表格中创建一个条件区域。
Excel 中的条件区域只有一个要求，筛选条件的表头与原表格匹配。例如此处要求筛选团队总成绩，那么条件区域的表头也要是「团队总成绩」，筛选的具体条件跟在表头下方即可。表头明确了，但是内容呢，怎么才能表达非空这个条件？使用高级条件筛选 文档说使用：<>即可。所以在旁边新建筛选区域，内容如下图框中所示。
筛选区域制作好后，就可以使用高级筛选，选择出合适的数据了。点击区域选择框后面的「选择」图标，就可以手动选择区域，免去了手动输入的麻烦。还有一点要注意的是，选择区域的时候要连表头一起选择，否则会导致筛选不到数据。筛选得到的数据如下图所示。
但是此时数据是未排序状态，所以需要对其进行排序。此时再使用 RANK() 函数排序，是不太合适的。为了让运动员们可以更直观地看出队伍排名，需要直接对行进行排序。，选择所有团队，点击「数据 > 排序」，列条件选择「团队总成绩」，顺序按照「升序」排列，点击「确定」即可完成排序。
排序完成后的数据如下所示，排序完成后，就是简单的数据清理工作了。保留跑团名称和总成绩，删除不必要的列，增加一个团队总成绩表头，增加一个团队排名的递增序列。
最终效果如下图所示：
男女排名制作
用类似的方法，制作男/女个人排名。如下图绿色区域所示，重新设置筛选条件区域，表头设置为「性别」，下方单元格改为「男」，重新进行高级复制筛选，得到如图中 O 到 U 列的效果。
同样适用数据中的排序功能，对全部的男运动员成绩排序，删除无用的两个序列，并在最后添加名为「男子排名」的递增数列即可。
修改筛选性别为女，重复以上两个步骤，生成女子排名。
最后得到如下所示的四种排名：
细节优化
隔行变色
在上个步骤中已经生成好了四种排名，但对于大量数量的表格，没有隔行变色的话，会让阅读者查看数据的时候较为麻烦，可能会出现对错数据的情况。所以可以在此基础上，添加隔行变色样式。
选中需要隔行变色的表，在功能栏的「开始」菜单下，点击「套用表格格式」，选择合适的样式。此处有一个小技巧，可以提高选表的效率，点击表中任意单元格，再按下 command+A 全选，即可选择出所属的大表。
间隔变色设置好之后，效果如下图所示，重复以上步骤，对剩余的三个表格进行同样操作。
如果部分读者知道新建规则，使用 =mod(row(),2) 公式来实现隔行变色，请不要这样操作。微信不会解析这样的规则公式，使用公式实现隔行变色，在微信里表现不出任何效果。
表格分表
在表格发在群里之后，有人问说：只看到了个人成绩，其他几个排名成绩在哪里呢？看了一下，才知道原来是只顾着上下滑动，忘记了左右滑动也有表格了。为了从源头上解决这个问题，而不是在群里不停地解释，可以对四个表格进行分表。
点击底部的新建工作表，创建三个新表，并将团队排名、男子排名、女子排名剪切到新表，并给新表的进行重命名。在微信上的预览效果如下，顶部有四个按钮，可以以最直观的方式提醒阅读者：点击就可以切换不同的表进行查阅。
制作成绩证书
设计证书
打开 OmniGraffle 或者 Photoshop 开始制作证书模版，使用什么软件都可以，按照个人习惯就行。因为作者只学过 OmniGraffle，所以就直接用它来做了。如果读者心中有更好的选项，可以自行选择。此处使用的是 OmniGraffle 制作，它的参考线功能可以保证所有的文字都准确对齐，而且是精度 0.1 像素级别的对齐。由于时间仓促，所以证书设计地也比较简单，边上没有其他的点缀，但是大家还是比较满意的。明年时间充足的时候，再更用心地制作一份新的证书模版吧。由于此处证书设计自由度比较大，所以这部分自由发挥，此处就简单介绍一下就过了。
电子签名
将制作好的证书模版导出 JPG 图片，添加电子签名。此处可以使用预览中的签名功能，选择在 iPhone 上进行签名，因为在 iPhone 上签名是可以显示字的。如果使用触控板签名的话，那就是手指对着触控板盲签，笔画上可能会产生一些偏移，导致签名不那么好看。如果有 Apple Pencil 的话，可以得到更好的签名效果。
在签名完成后，将电子签名添加到成绩证书模版上，这样一份正式的成绩证书模版就制作好了。
Python 脚本制作
成绩证书模版有了，接下来就是制作成绩证书的事情了，使用 Python 的 PIL 库进行操作。在 GeeksForGeeks 上看到了 Adding Text on Image using Python – PIL 这篇文章。里面给出了两个具体的图片添加文字的例子，可以直接拿来模仿使用。要特别提一句的是，GeeksForGeeks 网站上提供了很多可以直接运行的 Demo，它是一个学习 CS 技术相关主题的平台，如果平时在 Google 上使用英文搜索问题，恰好能看到它出现在结果里，那么是很值得进去看看的。
将 GeeksForGeeks 上提供的 Demo 复制下来，然后稍微做一点修改，就得到了可以满足需求的 Certificate.py。主要的修改体现在使用 for 循环从文件中读取成绩，将字体更换为苹方。
使用 sys 从命令行的第一个参数读取成绩记录，第二个参数设置男子组 or 女子组。在第一版的代码中，原先是没有第二个参数的，在实际赛后制作成绩证书的时候，忘记把组别进行修改，导致第一版的成绩证书组别错误。所以为了从根本上解决这个问题，直接将组别设置为了命令行参数。
import sys
from PIL import Image
from PIL import ImageDraw
from PIL import ImageFont
file = str(sys.argv[1])
group = str(sys.argv[2])
ranks = open(file, 'r')
content = ranks.readlines()
for text in content:
# 以制表符为间隔分割单行内容
text = text.split('\t')
print('正在生成' + text[1] + '的成绩证书...')
img = Image.open('template.jpg')
I1 = ImageDraw.Draw(img)
Font = ImageFont.truetype('PingFang.ttc', 40)
# 名字
I1.text((382, 635), text[1].rstrip(), font=Font, fill=(0, 0, 0))
# 参赛号码
I1.text((382, 720), text[0], font=Font, fill=(0, 0, 0))
# 枪响成绩
I1.text((382, 810), text[2], font=Font, fill=(0, 0, 0))
# 成绩排名
I1.text((382, 895), '第 ' + text[4].rstrip() + ' 名（' + group + '）', font=Font, fill=(0, 0, 0))
img.save(text[1] + ".png")
在 12 行适当地添加了提示语句，在 16 行更换了使用的字体。至于如何确定使用的字体文件名，可以打开字体册，选中需要使用字体，右键选择「在访达中显示」。
访达中对应显示的文件名，就是在 Python 代码中需要使用的文件名，例如下图中的 PingFang.ttc，只要将它连同后缀名一起填入即可。
更换好了字体之后，接着就是确定文字位置。这对于 OmniGraffle 来说，就是一道送分题。将导出的 JPG 模版复制到剪切板，打开 OmniGraffle 粘贴这张图片，并将该图片的位置设置为 x=0,y=0。然后添加一个矩形框，拉到模版上合适的位置，OmniGraffle 就会在左上角显示出矩形框的像素位置。这样重复操作几次，就可以得到所有需要自动生成的文本位置。如果想深入学习一下 OmniGraffle，scomper 也少数派上提供了 用 OmniGraffle 画一个精美的图示 付费栏目，有兴趣的话可以前往了解。
最后使用 img.save(text[1] + ".png") 语句，将生成的成绩证书文件名保存为人名，方便大家在赛后快速找到自己的证书。此处有一个优化点，就是可以在前面带上参赛号码和队伍名，这样做有两个好处。一是可以将同一组的人显示在一起，让找自己的证书更加方便。二是如果碰上重名的情况，也可以用参赛号码进行区分。这个优化点将会留到明年使用，今年暂且就这样了。
测试脚本
新建 rank 文件夹，放置 Certificate.py 生成脚本、man.txt 测试数据、template.jpg 证书模版。其中 man.txt 的内容是：路人甲\t0314\t50:30\t240，由于无法很好地显示制表符，此处直接使用 \t 表示，实际在 man.txt 中是不可见的制表符。
进入终端，切换到 rank 目录，输入 python3 Certificate.py man.txt 男子组 回车执行，终端正常给出了处理提示。如果出现了缺少库的报错，可以使用 pip3 install Pillow 安装相关库，就可以正常运行了。
生成的成绩证书如下图所示，效果还是比较好的。
生成证书
上一小节中以及生成好了各种成绩排名，接下来就是根据拍卖信息，生成电子成绩证书了。不要包括表头，选中排名成绩表，复制到剪切板。
新建纯文本文件，保存到 Certificate.py 相同目录下即可。最后得到如下所示的目录结构，Certificate.py 会读取 man.txt 里的内容，使用 template.jpg 作为模版，读取每行的内容添加到 template.jpg ，并保存为相应的运动员名字。
根据第二小节介绍的 Python 脚本原理，在命令行中切换到 Certificate.py 文件所在目录。并使用以下命令格式生成成绩证书：
python3 Certificate.py [成绩文件名] [性别组名]
最后生成效果如下所示，之所以看起来图片比较少，是因为同姓氏的图片覆盖掉了，实际上应该是有 136 张图片的。
随便挑选两张查看，效果如下：
男子组制作好之后，就是女子组了，但是在此之前需要对图片进行打包，以免和接下来生成的女子组混在一起。部分对 macOS 比较熟悉的用户可能知道，如果是使用右键对文件进行压缩，在其他平台上接收文件，可能会多出一个 _MACOS 的文件夹，该文件下包含了一些元信息，如果不想生成这个多余的文件夹，可以使用终端里的 zip 命令进行打包。命令格式是：zip 压缩包名.zip 压缩文件名。
所以直接在目录下使用如下命令进行压缩打包：
zip 男子组成绩证书.zip *.png
打包好之后，就可以删除所有已经生成的成绩证书。回头看看，在第二小节将模版图片导出 JPG，也是考虑到在这个步骤中避免被误删。在工作目录下，使用如下通配符删除命令，删除所有 png 文件：
rm *.png
重复以上步骤，生成女子组成绩证书，命令如下：
pbpaste > woman.txt
python3 Certificate.py woman.txt 女子组
zip 女子组成绩证书.zip *.png
rm *.png
最后生成好的 zip 压缩包发在运动员群里，搞定收工。
赛后总结
本次协办这场赛事，让作者从 0 入门开始学习了一点 Excel，也真正认识到了 Excel 的强大之处。之前都是道听途说，这次终于是有了实际体验。以后还有类似的需求，必定也是找 Excel 实现。本文以 0 基础的 Excel 新手的视角，配合编程的思维，逐级递进地介绍了如何编写复杂的 Excel 公式。希望读者在以后有实际类似需求的时候，也可以通过 Google + 官方文档的方式，逐渐实现自己的需求。
在编写最复杂的用时时间的过程中，可以通过文章看出，也是逐步调试出那么长的公式，而不是一蹴而就。实际上在此之前，作者还使用了其他的一些实现方式，如何绝对引用等。由于不完全满足需求，才逐步换到了公式法，前前后后也是折腾了几天才折腾出来。
除了技术上的收获，在这次赛事中也感受到了大家对运动的热情。作者日常也是个骑行爱好者，前年参加了海洋杯国际自行车公开赛，虽然是骑游组去游玩为主的，但是很被那种运动的气氛所感染。由于疫情，已经两年半没有大型赛事可办了。这次久违的中型比赛，让作者又体验了当年赛事的热闹气氛，所以也打算接下来试着开始跑跑步，换点新的运动方式。
后期改进
本文也算是对本次协办赛事的总结，实际跑一遍流程之后，发现很多可以改进的地方。例如上面的细节优化这部分，就是在全部结束之后，大家有反馈过来一些问题，才想起来要这么做。相信经过这次的实践，明年的比赛技术支持会做地更好。比如以下几个点可以改进或者升级：
- 设计更好的看的成绩证书
- 制作团队的成绩证书，团队名下面跟上所有队员的名字
相关资源
GitHub JamesHopbourn Apple-Automation
