爱华网题外话：那晚看公司的歌唱比赛，看到他们还在使用手工来统计比赛分数，震惊啊！都什么时代了？手动？excel干嘛吃的？比赛之后久久不能平复，长久没有动excel，手痒痒，于是找到组委会要他们的规则来做表格。嗯，就这样吧。
【问题描述】——如此这般
歌唱比赛有11个男生和11个女生参加，评分分为仪容仪表（10），音准节拍（30），演绎技巧（30），音质（20），台风表情（10）和难度加分项（10）六项，括号内为单项评分上限。一共有7个评委。将各项计和后，计算出每个评委评出的总分，去掉一个最高分和一个最低分，剩下的分数计平均分为选手的最后得分。根据最后得分排出总排名，男生排名及女生排名。
【分析及思路】——见招拆招
1.为了明晰，将数据分在3个工作表——一个用于存放原始记录，一个用于汇总评分，最后一个用于输出排名。其实考虑过分类汇总，但是由于要输出排名，分类汇总就变得不好操作了。
2. 评分上限使用数据有效性实现。
3. 每个评委的评分计和使用offset函数完成区域偏移变换。
4. 使用max、min函数实现一个最高分和一个最低分的剔除。
5. 使用逻辑判断建立男女得分分列显示。
6. 使用rank函数列出总排名、男排名、女排名。
7. 使用countif函数还原排名用于建立唯一检索用排名。
8. 使用vlookup函数检索整理出排名信息。
【关键点】——十八班武器
使用到的函数：vlookup，if，rank，countif，sum，row，offset，max，min，count。
使用到的设置：数据有效性。
详解：
【建立工作表】——一切从这里开始
建立3个工作表，“原始记录”，“汇总评分”，“比赛结果”。
【标题行的设定】——得明白自己想要什么
由于“汇总评分”表比较容易建立出连续的信息，所以在这个表录入参赛的信息。“编号”——出场顺序；“姓名”；“曲目”；“性别”。
“汇总评分”表格中还需要计算各个评委给每个选手的打分，需要“评委1”到“评委7”。计算总分的“总分”，用于计算男排名的分列“男”，用于计算女排名的分列“女”。（怎么个便于法，下面会说到。）
为了便于检索方便查看，排名列放在编号的前面，检索列放在排名的前面。从左到右依次是“男检索”，“男排名”，“女检索”，“女排名”，“检索”和“总排名”。
“汇总评分”的基础建设已经完成，先放一放。虽然大多数的计算和公式都集中在这个表格，还是顺着数据流动的方向来做表格，这样思路才会顺畅。
【原始记录表】——让满是数字的世界清晰一些
到“原始记录”工作表，这里需要的信息是为了给数据录入员以清晰的辨认，这里需要的信息是“编号”，“姓名”，“项目编号”——各个评分分项的编号，“项目”——评分的分项，“评委1”到“评委7”，“评分上限”。
首先看“项目”，把六个项目按编号排好。有两个方法，其一是录入，这个就不多说了。其二是建立项目编号、项目及评分上限的对应表（辅助），然后输入编号，其他内容用vlookup函数来检索。这个方法的好处是，如果以后有项目的名称或评分上限需要修改，只要改动对应表就可以了。同理使用vlookup函数将“评分上限”填满。
然后标注每个人的数据区域。使用合并单元格把合并好编号、姓名的区域，以便数据录入员查看。在“编号”中输入每个选手的编号，“姓名”使用vlookup函数从“汇总评分”中检索，这样就可以通过修改一处地方达到全部相同内容的修改，减少修改录入的工作量，同时也避免出错。
=VLOOKUP(A2,评分汇总!$G$2:$H$23,2,0)
设置数据录入的有效性。选定整个录入区域并确保当前单元格在2行，【菜单】-【数据】-【有效性】；【允许】/小数；【数据】/介于；【最小值】/1；【最大值】/=$L2。$L2是“评分上限”列的数值。绝对引用列表示这一行都使用L列作为数据有效与否的判断依据。这样输入的数值超出上限Excel会提示重输，避免录入错误。
之后，可以选择录入一些数据便于检视之后的公式编写，这个不是必要的，但是很好的辅助效果。
现在开始最核心的部分——计算和统计。
【评委分数计和】——用偏移的刀划出每个人的豆腐块
转到“汇总评分”，先计算每个评委给选手的总分。总分就是要计和，使用sum函数完成。那么，怎么确定每个评委给每个选手评分的区域呢？回去看“原始记录”的表格，每个总分包括了6个数据，评委1给选手1的打分区域为“E2:E7”，评委1给选手2的打分区域为“E8:E13”，……这样每个计和的区域是一个变量，这就要用到offset函数了。Offset，意思是“偏移”，其得到的结果是一个位置，这个函数的语法是这样的：
Offset(基准位置,行偏移量,列偏移量,[区域高度],[区域宽度])
基准位置，是设定的参考原点，以这个位置为基础进行偏移操作；
行偏移量，当为正数时，是向下偏移的行数，当为负数的时候是向上偏移的行数；
列偏移量，当为正数时，是向右偏移的列数，当为负数的时候是向左偏移的列数；
这样就得到了偏移后单元格的位置。如果只是一个单元格，后面两个参数可以省略。
区域高度，当为正数时，是以偏移后的位置为基准向下包含的行数，当为负数的时候是向上包含的行数，需要注意的是，区域高度的最小值（绝对值）为1，表示只有1行；
区域宽度，当为正数时，是以偏移后的位置为基准向右包含的列数，当为负数的时候是向左包含的列数，需要注意的是，区域宽度的最小值（绝对值）为1，表示只有1列。
就这个例子而言，所要做的，就是把“原始记录”中的每六行为一组，在“汇总评分”中以一个单元格的形式出现。这样我们需要一个变量标识每行，有两个简单的方法——其一，使用row函数获取本行的行号，使用=row()就可以获取本行的行号，这个方法有一个毛病，如果在做好公式后在中间或表格顶插入行，就会破坏整个公式，导致整个表格废置。为此，我们使用第二个方法，使用编号来标识，简单且可靠。因为目前所有信息都是基于编号的，只要编号修改了，所有信息的指向依然随着编号而动。要做一个可靠的减少偏差的体系，就要尽可能的使所有信息都在同一个关联中，避免重复录入。呵呵，说多了，回到正题。我们有了这个变量就要弄清变量和我们最终结果的关系。假设我们这个变量是n，我们知道基准点是“原始记录”的E2，有一种常用的方法是列举几个例子，来找出其中的规律。n=1时，则行偏移量为0；n=2时，则行偏移量为6；n=3，则行偏移量为12；……。可以看出行偏移量可以表示为6*(n-1)。然后就是确定高度及宽度，容易看出高度是6，宽度是1。这样就得到了求和的公式：
=SUM(OFFSET(原始记录!E$2,($G2-1)*6,0,6,1))
原始记录!E$2，表示应用的单元格属于工作表“原始记录”，位置是E2，固定引用行是确保每次偏移计算的原点保持在第二行，确保偏移量计算的可靠性。
$G2，是编号所在单元格，固定列是确保公式复制到其他评委的时候依然使用这个单元格用于计算偏移量。
最后将单元格复制粘贴到整个评委评分计和区域。
【最终得分的计算】——这鱼，不要头不要尾，中间给我切平均了！
理清思路先，先要有个总分，使用sum函数完成；然后选出一个最高分，max函数可以解决；选出一个最低分，min函数可以完成；剩下的评委人数，使用count函数获得总评委数，然后减掉2人。嗯，得到公式：
=(SUM(K2:Q2)-MAX(K2:Q2)-MIN(K2:Q2))/(COUNT(K2:Q2)-2)
K2:Q2，是评委给出评分的区域。
复制粘贴一列，最终得分就计算出来了。
【男女得分的分列】——男的站左，女的站右，太监……
还记得之前将标题行列了“男”列和“女”列么？这里要用到逻辑值参加计算，所谓逻辑值，就是true和false，其中True=1，false=0。在“男”列中，首先判断这个选手是否是男的，是的话就就显示分数，否则显示为0；同理，在“女”列中进行相应操作。得到的公式为：
=$R2*($I2=S$1)
$R2，是选手的分数，固定列是为了方便公式的复制粘贴，即使变换了列，依然能找到这个分数；
$I2，是选手的性别，固定列的目的同上；
S$1，是用于性别判断，所谓的便于计算，就是这里可以使用标题行来作为判断依据，使得两列统一成一个公式。大家可以认为这是耍酷行为，因为录入更方便，更直观，更便于校验审阅。这个是我的一个习惯，希望尽可能地统一可以统一的公式。嘿嘿，又说远了。
($I2=S$1)，这是逻辑判断，要用括号括起来。
【排名】——参差不齐的队伍，你在什么位置？
这里其实很简单，rank函数包办一切。其语法如下：
rank(数字,排名区域,[序向])
数字，是需要获得其排名的数字；
排名区域，是整个需要排名的数字区域；
序向，0或忽略时为降序排列（从大到小），非0时为升序排列（从小到大）。
在“男”列和“女”列排名时要忽略到为0的单元格，用简单的if函数即可达到目的。
如果有一样分数的，他们的排名是并列的。
其实到这里就可以结束了。如果不要再额外的输出结果表格，剩下的事情可以使用排序和筛选来完成。秉承表格为傻瓜服务的原则，使表格变得更为傻瓜化，脱离排序和筛选的束缚，来吧，折腾折腾自己。
【建立检索列】——检索的世界不容许有相同的存在
因为排名有重复的可能，直接检索会导致部分重名的信息无法检索得出，所以要还原成无重复的排名以便检索。
这里是整个表格中最难的一部分，在制作表格的时候，还产生了小小的瓶颈。分析一下排名的规律，就知道需要做到两点：
1.区分出哪个排名是没有并列的。
2.在并列的那些排名中，如何把他们区分开来，并转换成数字。
第一点很容易，用countif函数统计排名出现的次数就可以了。出现1次的，表示没有并列的，可直接使用排名用于检索。
COUNTIF($F$2:$F$2,F2)
$F$2:$F$2，是排名的区域，使用绝对引用使得公式在复制的时候区域不会变化；
F2，是需要判断的排名。
关键是第二点，区分并列的排名。可以采用这种思路：从第一排名开始统计，记下其出现的次数，注意不是在整个区域内统计，而是逐个统计，并将统计过的排名列入统计范围。这样这些出现3次的并列排名就会有分别的标识1,2,3了。然后将其原排名加上标识再减去1就得到了还原的检索列了。
这样就可以用下面的公式来完成检索列：
=IF(COUNTIF($F$2:$F$2,F2)=1,F2,F2+COUNTIF($F$2:$F2,F2)-1)
可以简化一下：
=F2+COUNTIF($F$2:$F2,F2)-1
$F$2:$F2，表示固定最开始统计单元格，统计区域随着统计单元格（F2）的变化而逐渐变大。
“男排列”和“女排列”的需要多一个判断，就是要跳过空白单元格（空白单元格表示这个人性别和统计的类别不一致），使用if函数即可。
【输出结果】——千呼万唤始出来，便秘了？
输出结果，用vlookup函数完成，没有其他特殊的，之前说过，这里不赘述了。
