excel 综合案例解析：使用多函数快速从高到低分提取科目成绩中前N名的姓名

    今天一起来学习一个小案例：根据选择的科目，提取该科目前N名（以前3名为例）的学生姓名。

    最终结果是这样的

    要注意的是：从高到低提取科目成绩的前三名，而且科目成绩中有并列的情况。解题思路是这样的：

    1． 获取科目对应列的数值；

    2．找出科目第三名的数值；

    3．筛选出科目前三名的数据；

    4．对筛选后的科目成绩执行排序；

    5．提取、合并姓名。

    公式会比较繁杂，可以先分步写公式，最后再合并，慢慢练习，自能驾轻就熟。

    第一步：获取选择科目的成绩。使用CHOOSECOLS获取科目所有成绩，在O5单元中输入，=CHOOSECOLS (C3:J20,2)。C3:J20是包括姓名和各科目成绩在内的数据区域，2是语文成绩在数据区域的第2列。

    这时，选择其它科目时，数值不会变化，所以第2个参数不能写死，要根据选择的科目而动态变化，需要使用Match函数函数动态获取科目位于数据区域第几列。

    接下来，把2改成MATCH(L5,C2:J2,0)，L5是选择的科目，C2:J2是表头区域，0是精确匹配模式。

    完整公式=CHOOSECOLS(C3:J20,MATCH(L5,C2:J2,0)。

第二步：找出科目第三名的数值。LARGE函数找出数组中第k个最大值。

    在Q5单元中输入，=LARGE(O5:O21,3)。O5:O21是第一步获取的科目成绩，3是第3个。

    但有些科目成绩中有并列成绩的情况，所以需要先去重复值再提取K个最大值，把公式修改为=LARGE(UNIQUE(O5:O21),3)。

    第三步：筛选出科目前三名的数据。筛选肯定要使用FILTER，在S5单元中输入，=FILTER(C3:J20, O5:O21>=Q5)。筛选区域C3:J20，包含整个数据表区域；条件区域O5:O21是第一步获取的科目成绩；筛选条件是>=Q5；Q5是LARGE计算出来的科目中第三名成绩。

    第四步，对筛选后的科目成绩执行排序。使用Sort函数对筛选出来的结果进行排序，对第几列排序不是固定的，不能写死，要与选择的科目位于第几列一致，一样是使用Match函数。

    在U9单元格中输入，=SORT(S5:Z7,MATCH(L5,C2:J2,0),-1)。S5:Z7,是筛选出科目前三名的数据区域，MATCH(L5,C2:J2,0)是依据第几列进行排序，-1是降序排列。

    第五步，提取、合并姓名。先用CHOOSECOLS提取出姓名，再用TEXTJOIN合并姓名。

    在W14单元格中输入=TEXTJOIN("、",True,CHOOSECOLS(U9:AB11,1))。U9:AB11是执行排序后的数据区域，1是提取数据区域中第1列，也就是姓名；用"、"做分隔符，True是忽略空单元格，也可以省略。

    到这里分步公式已经写好，验证一下，下拉选择任一科目，结果在同步变化；同时与科目成绩验证，结果也是正确的。

    接下来，把分步公式一步一步的合并起来。

    首先，把最后一步公式中的U9:AB11替换为SORT(S5:Z7,MATCH(L5,C2:J2,0),-1)，公式变成= TEXTJOIN("、",,CHOOSECOLS(SORT(S5:Z7,MATCH(L5,C2:J2,0),-1),1))；

    接着，再把公式中的S5:Z7，替换为FILTER(C3:J20, O5:O21>=Q5)，公式变成=TEXTJOIN("、",,CHOOSECOLS(SORT(FILTER(C3:J20, O5:O21>=Q5),MATCH(L5,C2:J2,0),-1),1))；

    接着再把公式中的Q5和O5:O21替换为对应的公式。

    最终公式成为：=TEXTJOIN("、",,CHOOSECOLS(SORT(FILTER(C3:J20,CHOOSECOLS(C3:J20,MATCH(L5,C2:J2,0))>=LARGE(UNIQUE(CHOOSECOLS(C3:J20,MATCH(L5,C2:J2,0))),3)),MATCH(L5,C2:J2,0),-1),1))。

    下次分享本案例的简化写法，敬请关注！