|
|
Office 365中提供了几个新的函数，如Fileter、Sort、 Sortby、Randarry等，都是功能强大的函数，使老版本中需要极为复杂的数组公式才能解决的问题轻易得到解决。同时这几个函数都可以自动得到动态数组，动态数组也是Offic 2019和Office 365中提供的新功能。输入公式时，只需像普通公式一样完成公式输入并确定后，Excel会智能地自动按公式引用的数据区域扩充公式结果范围。如果动态数组引用的数据源是“表”的话，数据源表发生删除或扩充等变化时，动态数组会自动跟随变化。
本贴主要介绍Sortby函数在综合排名中的应用，并附带介绍动态数组。Sortby函数有个小弟，就是Sort函数，Sort函数按指定的某一列数据或某一行数据进行排序。而Sortby函数则是按多列或多行数据进行排序，且公式中排序参数的顺序就是排序的优先级别顺序。Sortby能智能地根据引用数据判断是按行还列的排序。Sortby的语法是：
SORTBY （array，by_array1，[sort_order1]，[by_array2，sort_order2],...）
参数 描述
Array 要进行排序的数组或区域
必需
by_array1 第一排序依据的列或行
必需
[sort_order1] 排序方式，1表示升序，-1表示降序，默认（省略）为升序
可选
[by_array2] 第二排序依据的列或行
可选
[sort_order2] 同上
……
如果只有第一排序要求，函数就和Sort相同，可直接使用Sort。
下面结合一个学生成绩表来说明Sortby函数是如何进行排序的，如图 1，是一个学生考试成绩表，数学和语文。排名时要求按总分进行排名，总分相同进按语文成绩排名。图中有相同填充色的学生总分相同。
图 1数据源
在旧版的Excel中，一般可用下面的数组公式实现：
=MATCH(D2*100+B2,LARGE(D$2:D$27*100+B$2:B$27,ROW($1:$26)),)
就是把总分列（D列）的数据增加一个加权系数，这里是100，也可以更大。加权系数与总分相乘后，再加上语文分数后用large来排序，最后用match来精确找到当前行学生在排序后位置。如果还有其他考虑成绩，如英语，第二排序依据加上数学，那公式中，总分的加权系数就要变成至少10000，语文的加权系数为100，即用总分乘以10000，语文成绩乘以100，与数学成绩一起相加来排序。
而函数Sortby则极为简单，如图 2，J2中输入公式：
=SORTBY(A2:F27,D2:D27,-1,B2:B27,-1)
回车后，公式结果自动按数据源大小扩充（Excel 365中称为溢出），得到J2:M27排序后数据。这里的公式在Excel中称为动态数组，动态数组的辨别是，当光标位于数据区域中任一单元格时，动态数组的范围会自动显示一个边框，且当光标在动态数组中的第一个单元格中时，如图中的J2，公式编辑栏中的公式是黑色的，可以编辑；而在其他单元格中时，公式编辑栏的公式是灰色的，不可编辑。如图 2所示。
图 2 Srotby排序结果
要在原数据表中显示名次，就是如何找出当前行在Sortby排序后的结果中的位置。这里提供两个公式供参考，也许高手能编写出更好的公式：
公式一：
=VLOOKUP(A2,IF({1,0},SORTBY(A$2:D$27,D$2:D$27,-1,B$2:B$27,-1),ROW($1:$26)),2,)
如图中的E列。
公式二：
=MIN(IF(A2=SORTBY(A$2:D$27,D$2:D$27,-1,B$2:B$27,-1),ROW($1:$26),4^8))
如图中的F列
附老版的数组公式在G列。
三个公式的截图见图 3
不过上面用了SORTBY函数的两个公式，完成公式输入后，并没有溢出为动态数组，所以引用中加了混合引用后，再下拉的。
另外，旧版的数组公式，在Excel 365中，无需再同时按Ctrl+Shift+Enter三键输入数组公式，而可像普通公式一样输入，并下拉填充。经测试，文件保存的在其他版本中打开时，数组公式会自动显示出来，且公式编辑栏中显示大括号。而旧版中保存包含数组公式的文件在Excel 365中打开是，也是显示有大括号的，只有重新编辑后才能去掉大括号。
附：Excel原文件
动态数组与sortby函数.rar
(11.81 KB, 下载次数: 13)
|
|
