试做2023年微软Excel世界锦标赛决赛的EVE题目
前言¶
我很喜欢玩游戏，而玩游戏的时候又希望尽可能地把游戏玩得明白。自从多年前玩了《上古卷轴5：天际 (The Elder Scrolls V: Skyrim)》并写了一篇《潜行弓研究笔记》之后，我就在整理游戏资料的道路上一去不复返了。而整理游戏数据，自然是用Excel最好。于是到后来，玩Excel也变成了我玩游戏不可或缺的一部分。
由于深谙我的Excel教忠实教众属性，不久前阿箱向我推荐了一个视频，我打开一看，居然是2023年微软Excel世界锦标赛决赛的录像。并且，是的，它完全是按照电竞比赛的风格来举办的。
看到这个视频后的第一反应：啊？啥玩意儿？世界上还有这么怪的比赛？
看到这个视频后的第二反应：所以谁说Excel不是游戏，你看它连电竞比赛都有。吾心甚慰。
Excel的电竞比赛或许是所有电竞比赛中最奇怪的一个。虽然它也像其他所有电竞比赛一样有解说有主持，并且也像其他所有电竞比赛一样用激昂的语气介绍选手和解说比赛，但仔细一看，解说穿的是写满了Excel公式的T恤，而选手——无论来自哪国，年龄多大，有着什么样的绰号——都是一副老实宅男模样。而台下偏偏又有一群观众像其他所有电竞比赛一样热情地为自己所支持的选手加油，种种要素汇在一起，让这个比赛充满了奇怪的气息。
不过Excel世界锦标赛虽然是电竞比赛，实际的比赛内容更像是上机考试。每一轮比赛中，选手都会拿到一个excel文件，上面有若干道题，每题都有一定的分数，选手要做的就是在规定的时间内尽可能拿到更多的分数，最后得分最多者获胜。于是比赛的过程就是看每位选手如何埋头答题。
虽然Excel也有电竞比赛这件事本身很有趣，但我实际看了一会儿后，却不觉得把Excel办成电竞比赛是个好主意，至少目前这个形式不行。因为观众看不到每一场比赛具体的题目，比赛过程中几位选手的屏幕还会很快速地来回切换，也看不出来选手具体是如何解答的——这就不像电子游戏的比赛一样，观众都知道游戏规则，也能看懂选手都在干什么。观众能看明白的，实际上只有各位选手的实时分数——由此可见，只要把多于一个人聚在一起并且找个形式随便比个大小，就能成为一个比赛了，而且观众还买账。不过Excel电竞比赛现场票或许也会是所有电竞比赛的现场票中最有实际用处的一个，因为每位选手的座位前都有一个该选手的屏幕投影，展示了选手完整的答题过程，这只有现场的观众才能看得清。
不过促使我写这篇博文的并不是对Excel世界锦标赛的感想，而是赛事组免费放出了最后一轮比赛的题目，供大家同乐。事实上，所有比赛题目都会在官网上放出，只不过都有20美元的售价——我很怀疑是否真的有这么多nerd愿意买——但最后一轮比赛的“EVE Online” Case是免费放出的，可以在此处下载。
EVE Online是一个太空题材的沙盒网络游戏，我多年前也玩过一阵子。该游戏一直被戏称是Excel游戏，2023年的时候更是加上了官方Excel插件，拿EVE做Excel世界锦标赛的题目，实在是再合适不过了（我甚至怀疑这场决赛就是EVE赞助的）。题目的内容是用Excel计算EVE中挖矿、造船以及相应成本和收益的问题，这可实在是太适合玩游戏必列Excel的我了。于是接下来的几天晚上，我埋头攻克这张Excel，最终总算是答对了所有题目，也有不少收获，在此写一篇博文作为记录。
在叙述具体的解答过程之前，先对题目的形式做一个简单介绍。一个excel文件——也就是一轮比赛——的题目分为Questions和Bonus。Questions是一般题目，其中又分为7个Level，每个Level又有20-30道小题，每道小题有一个分数。越往后的Level分数越多。不过不同小题之间就是几个变量的值有所不同，每个Level解决的是同一类问题，因此用同一组公式就可以一并解决——当然这同时也就意味着，如果这个Level解得不对，里面所有小题基本都拿不到分了。Bonus则是抢答题，给最先答对此题的几名选手加分。Bonus共有5道题，分数也是越往后越多，限定的选手数目分别是5/4/3/2/1，即前5个答对第1题的选手获得第1题的分数，前4个答对第2题的选手获得第2题的分数，以此类推。最后，整个excel文件限时30分钟解答，不过我不参加比赛，就慢悠悠地把所有题目都解答了。
以下简单介绍我解答这些题目的思路。我的完整解题过程可以在此处下载。此外，解答其中绝大多数题目都需要使用一些新函数（不然写函数会写得很痛苦），所以Excel必须使用365版本。如果用比Excel 365更老的版本打开我的excel文件，数字很有可能无法显示。
Questions¶
整理数据¶
在实际解答问题之前，把题目所需的必要数据整理一下是个好习惯。我整理了舰船信息、行星矿物信息、行星分布及资源信息、采矿效率、市场矿物价格等等，分在了3张sheet里，这样做题时更方便查找和筛选。当然，在实际开始做题前是无法得知我们都需要什么信息的，所以这些数据都是随着做题的进度逐步整理而成。
Level 1¶
这道题很简单，用每种船所需的Tritanium数量，乘该矿物价格，四舍五入精确到千即可。
计算价格只用一个VLOOKUP函数就能搞定，但在后面的计算中需要大量使用XLOOKUP函数，因此不如在这道题开始就熟悉XLOOKUP函数的使用。
至于四舍五入，使用ROUND函数，位数写0即可，下同。
Level 2¶
需要将每种舰船所使用的7种矿物数量分别乘它们的价格，计算总价格。并且因为厂家收10%的加工费，还要再乘1.1，最后四舍五入精确到千。
这道题也简单，只要知道这种 A1×B1+A2×B2… 的计算需要使用SUMPRODUCT函数即可。
Level 3¶
这道题就比较难了，最大的难点在于如何将单元格背景颜色转换成其所对应的舰船名称——不得不说这道题记录数据的习惯真是太不好了，哪有在Excel里用背景颜色记录数据的啊，真是个反面教材。
对于如何把背景颜色转换成舰船，我实在没有什么好的办法。理论上写VBA是可以的，但问题是我不会写，而且我相信这既然是Excel比赛而不是VBA编程比赛，一定有用Excel自身的功能就可以解决的办法。上网搜索了一下，多数搜索结果说使用GET.CELL函数，但这是一个低版本的函数，已经不能在当前版本上直接使用了，搜索结果说要先定义名称再使用，但或许我对Excel的定义名称功能也不太会用，没能试成功。最后，我使用了替换中的“从单元格选择格式”，使用笨办法，将几个颜色的单元格内容分别替换成了对应的舰船名称。得亏舰船只有5种，要是来个100种，我就得直接放弃了。
接下来就是统计每道小题中，每种舰船数量都有多少。这个统计需要使用到SUMIFS函数。
然后，根据每种舰船的7种矿物数量，计算每道小题中，7种矿物分别需要多少数量。依旧要使用SUMPRODUCT函数，里面的两个数组则需要嵌套两个XLOOKUP函数做查找，前一个查找所在小题的5种舰船数量，后一个查找5种舰船对应矿物的需求量。
之后，再用XLOOKUP函数列出每个小题所对应的日期中，7种矿物的市场价格。
最后，用SUMPRODUCT函数将每种矿物数量与其价格相乘求和，再乘1.1（加工费），四舍五入到百万即可。
Level 4¶
这道题就涉及到行星区域图了，最好是在做题之前，将行星区域图整理成一维表，记录坐标、行星种类和矿石数量，再将行星上的矿石单位从units转换成m3，因为接下来计算时基本上都是使用m3。
要整理这样的一维表，首先依旧是需要将背景颜色转换成对应的行星，于是我又逐一使用了一遍替换中的格式功能（也幸好行星只有4种）。坐标则可用XLOOKUP函数分别搜索对应单元格的横坐标和纵坐标，再组合文本成为完整坐标。units用图上的数字乘1000即可，再用各种行星的units与m3的换算关系，将矿石数量的单位由units转换成m3。
（顺便一提，“Case” sheet中第45行的举例是错的，是6000 units，而不是6000 m3，如果换算成m3，应是600。微软Excel世界锦标赛决赛的excel文件都描述得这么不严谨，看来这比赛的电竞化程度还是弱了一点。）
整理完后再回到Level 4的题目。首先，用XLOOKUP函数列出每道小题的坐标所对应的行星及其总矿石量。然后再用这个函数，根据每种行星的7种矿物分布，计算每道小题的每种矿物总量（这里面还要再嵌套一个XLOOKUP函数查找对应矿物，这也是XLOOKUP函数的一个经典用法）。最后再用SUMPRODUCT函数，与2023/11/27的矿价相乘求和，四舍五入到百万即可。
Level 5¶
这道题我认为很难。需要计算每道小题所给出的行星序列中总矿物量，并且还要考虑不同行星采矿等级，以及驳船的总承载量。
为了方便计算，先把采矿等级换算成有效开采的比例，也就是我在“整理数据”一节中所说的“采矿效率”。这样计算起来会更快。
为了答这个问题，首先，需要使用XLOOKUP函数把每个小题中列的一串坐标都转换成行星类别。然后再用两个表，分别列出对应的总矿石量，和对应的采矿技能。这样，就可以计算每个小题的每个坐标分别的有效开采量。
接下来就是要考虑驳船的总承载量，有效开采量合计不能超过27500 m3。在Level 5中，只有一道小题的有效开采量合计超过了27500 m3，我就手动处理了这一道小题，但从规范角度来说，每道小题都应该用MIN函数处理一下。接下来的Level 6就必须要这样做。
然后，需要把每个小题的这一串坐标按行星归类，计算4种行星分别的有效开采量。依旧是使用SUMIFS函数即可。
再然后，使用SUMPRODUCT函数嵌套XLOOKUP函数，把每个行星的矿物数量按7种矿物分门别类。
最后，再用SUMPRODUCT函数与2023/11/27的矿价相乘求和，四舍五入到百万即可。
Level 6¶
这道题也很难，而且我认为不光是难，它的题目表述得也很迷惑。
这道题要求规划路线，规则是这样表述的：
The player will select the next asteroid to mine based on the following rules: start mining with the most profitable and largest asteroids until the barge is full.
- Highest ISK/m3 value - note that this value will be the same for all asteroids of the same type.
- Largest size of an asteroid - to minimize transportation costs.
- Assume that the players will mine the asteroids according to these rules until the barge is full, even if in rare cases the last flight might be unprofitable.
第3条没什么问题，但第1、2条之间是否有优先级，题目并没有说明。描述中也只是说“most profitable and largest asteroids”，这是一种并列关系。在玩家选择下一个行星时，是否是先严格考虑第1条，即便最高ISK/m3的行星上只有很少的矿石，然后再考虑第二高ISK/m3的行星，即便这类行星上的矿石总量非常高？或者他是综合考虑ISK总量最高的行星？由于题目表述不清，我不得不按照这两种情况分别计算了一遍，最后发现第1、2条确实是有优先级的，应当是先采最高ISK/m3的行星，直到所有行星都采完，然后再去第二高ISK/m3的行星。
（顺便说一句，玩家在规划路线时，按照先1后2的规则选择下一个行星，其实是很没有效率的一个方式，要多走很多路。应该在出发前先计算好要去哪些坐标，再规划一个最短路径，这样才更科学。）
另外一个令我困惑的问题是，是否要求开采的units是整数。一方面是因为，行星上的矿石量就是以units来记录的，在计算中它是不是总得有点用处；另一方面是因为，玩家开采的矿石如果把因技能不够产生的废料也包括进去（也就是说玩家总共开采的矿石，而不是有效开采的），很多时候会产生几位小数，这不符合实际操作。于是我把这个可能也计算了一遍，后来发现我想多了，只要直接计算有效开采的m3就好了。
累死累活总算是明白了出题人的意思，接下来就一步一步地解题。
第一步就是要计算4种行星的ISK/m3。这个不难，使用SUMPRODUCT函数，用每种行星的7种矿物数量与对应的矿价相乘求和，算出ISK/m3从高到低依次是Omber、Kernite、Veldspar、Scordite。
也就是说，玩家会先选择Omber行星，把Omber行星依据含矿量从高到低依次拜访一遍，然后再把Kernite行星依据含矿量从高到低依次拜访一遍。于是，我就把对应的坐标按开采顺序排列了一下，并列出了它们的行星类别、横纵坐标、含矿量等。由于可以直接判断出Kernite行星走不完全部坐标就可以装满驳船，我就没有继续计算Veldspar和Scordite行星的坐标，不过这个习惯不太好，回头到了Bonus 5的时候，我还是需要把所有坐标都计算一遍。
第二步，就是使用XLOOKUP函数分别列出每道小题中各个坐标的行星所对应的技能等级和开采效率，再用SUMPRODUCT函数计算每个坐标的有效开采量。
接下来就是考虑驳船的总承载量。每道小题在走完Omber和Kernite的所有坐标后，有效开采量总和都会超过27500 m3。因此，自H16行星开始，就使用了一个MIN函数，取有效开采总量和驳船剩余容量的最小值。再分别乘Omber和Kernite的ISK/m3，驳船开采矿物的总价值就计算出来了。
第三步，就要减去路程中的运输成本，在此题中是按单元格计算。这一步也困惑了我好久，因为它要求不能穿过尚未开采的行星。这要如何自动计算呢？我思考了半天，又上网查了半天，也没有查到excel对于这种情况有什么办法。
于是，我先是不考虑这个要求，计算单元格之间的距离（横坐标差值加纵坐标差值），发现答案不对。我又一个一个手动数格子，数了好几遍，确认我数的确实是最短路径，然而离奇的事来了，答案居然还是不对。难道手动数也能数错的吗？
我研究了半天我的答案与正确答案之间的差距，发现有些小题对了，有些小题却是错的。由于我是每个坐标之间都计算一次距离，很快就发现了哪一步的距离与正确答案不一致，手动改数，终于是改正确了。但问题在于，这个距离我怎么数也跟正确答案不一致。我苦苦研究了好半天，突然发现一件事：这格子莫非是可以斜着走的？
不得不说这题的出题人实在是太缺德了，一般人都会默认共边的格子才算相邻格子，共点是不算的，我觉得这可以说是一种常识了。如果认为斜着走格子也算一格，应该在题目中明确说明，不然一般人谁会想到题目会允许斜着走呢？
如果斜着走算一格的话，那么不同坐标之间的距离，只需要计算横坐标距离和纵坐标距离中相对较大的一个就是了。顺便说一下，行星区域图中的横坐标是用字母表示的，如果要把字母转换成数字，可以使用COLUMN函数嵌套INDIRECT函数，利用Excel的横坐标是字母坐标的特点，将字母转换成数字。
于是我就计算了不同坐标的长边距离当做坐标间的距离，与答案一对，发现都是正确的。但问题又来了，我还是没有考虑“不能穿过尚未开采的行星”的要求。忽略了这个要求也能算出正确答案，那这个要求究竟是干什么的？令人困惑。
总而言之，我觉得这道题出得非常不好，是所有题目里面我最不喜欢的题之一（是的，另外还有一道）。
Level 7¶
这道题目标注的难度是Hard，但我个人认为还挺简单的，里面所涉及的难点都在前面的题目中出现过了。
首先，依旧是用格式替换功能，把这一大堆五颜六色的格子都与行星类型对应。然后用SUMIFS函数计算每道小题中4种行星的总含矿量。再根据每个小题给出的技能等级，与采矿效率相乘，计算每个小题中4种行星的实际可开采量。
下一步，就是把各类行星的矿石量转换成7种矿物的含量，依旧是用SUMPRODUCT函数。
最后，就是列出Thrasher和Griffin的各种矿物用量，计算分别能造多少艘船。首先是要尽可能多地造Thrasher（这道题就表述得非常清楚），即是将每道小题的Tritanium、Pyerite、Mexallon、Isogen矿物总量分别除以造Thrasher所需用量，取最小值，用INT函数向下取整。然后再用剩余矿物量，采用同样的方法计算Griffin数量即可。
Bonus¶
因为是抢答题，Bonus的难度都不大。做完Questions后，再做Bonus就很简单了。
Bonus 1¶
用建造Megathron所需的Pyerite和Isogen量，分别除以Omber行星中两种矿物含量，取最大值四舍五入即可。
Bonus 2¶
使用SUMPRODUCT函数嵌套XLOOKUP函数，将建造Drake的矿物量与2023年10月每一天的矿价相乘求和，取最小值，再乘1.1，四舍五入到千即可。
Bonus 3¶
用与Bonus 2相同的计算方式，计算Megathron从2023/5/1到2023/11/27每一天的价格。但要先买进，再卖出，也就是说在计算利润时，不能简单地用其中的最大值减去最小值。因此，需要计算每一天的价格与之前所有天数中价格最小值的差值作为当天卖出的最大利润，再取所有天数的利润最大值，乘1.1，四舍五入到百万即可。
Bonus 4¶
列出2023/11/1的矿价，使用Level 6中计算ISK/m3的方式，再次计算一遍4种行星的ISK/m3，取最大值，四舍五入到整数即可。
Bonus 5¶
我最不喜欢的题之二就是这道题，巧的是它也跟讨厌的Level 6有关。
题目本身是很简单的，计算Level 6所有小题中未开采的矿石总量。我寻思把Level 6中的实际开采量除以采矿效率（也就是说把废料也加上），用所有坐标行星矿石的总和减去开采过的这些就是了。结果居然不对，而且差了不少。我反复检查了多次，公式没有问题，题目也很简单易懂，究竟是哪里出了问题呢？
这个问题我苦思冥想了好多天都没想出来，一度想放着这道题不管，直接发博文得了，然而看那道题有个叉，又感觉很不爽。于是这些天我一直实验各种可能性，直到前两天的晚上，才实验出要把所有坐标的行星都乘上采矿效率，也就是说都要计算出有效开采量，再减去Level 6中已开采的有效开采量。
我觉得这个思路极不科学。Level 6中的玩家是把驳船装满27500 m3后就回营地，并没有吃饱了撑的把所有坐标行星的矿石都开采一遍，然后再把它们丢在行星上不管。既然如此，未开采的矿石量就不应该考虑技能，玩家完全可以未来再开采这些坐标，并且再把技能升一升。
结果就因为这个原因，我又对着这个文件多琢磨了好多天。唉，气人。
总结¶
这张Excel卷子总的来说还是挺有意思的，我全神贯注做了好几个晚上，连真正的游戏都不想打开了。就是其中有些地方不太满意，出题的水平还比不上我的中学老师。办电竞比赛出这样的题可不成，好在选手实际比赛时，没有一名在30分钟内完成Level 5，也就省去了在Level 6纠结的工夫。
为了做完这个Excel卷子，我花了不知道多少小时，还一边做一边查，看来我是无缘参加Excel电竞比赛了。不过收获还是不小的，在做题过程中学到了几个十分好用也十分实用的Excel函数：XLOOKUP、SUMPRODUCT、SUMIFS。在我做这些题之前，都从来没有听说过这些函数。希望将来也有机会多学到一些简单又好用的Excel函数，这样后面再整理游戏数据时就能更快乐了。
此外，还有一件尚未清楚的事情，就是能否用Excel函数实现把单元格背景颜色对应到相应数据的功能，目前使用替换功能逐一替换的方式还是太不聪明了。另外未来如果有机会，还应该学习一下Excel的名称用法，以及在Excel中使用VBA编程。
最后，希望赛事组未来能多放一些题目出来让大家玩一玩（或者EVE来出也行），最好还能有一些复盘（其实就是讲题）。本身微软举办Excel世界锦标赛的目的就是希望更多人领会到Excel的乐趣，我虽然认同Excel很有乐趣，但若看了个比赛什么也没看出来，还什么也没学到，就没意义了。如果能多一些复盘，让更多人增长一些Excel知识，那就很好了。