94 返回查询数据中指定条件对应的前三名数据（FILTER、SORT、TAKE）

供应链管理人员在月底做数据分析的时候，有时候需要对查询数据中的指定条件对应的前几项数据显示出来，举例说明：如查询某供应商对应商品的采购记录，采购金额前七名的数据是什么？一般情况下是手动操作通过筛选对应的供应商，再筛选金额前几项，再复制出来；

如果使用建模的思路来的话，可以把建立一个查询页面，录入对应的条件，自动返回需要的结果，这样就避免了每次都手动筛选，古老师今天用Office 365 的方法来设计，简单好理解。

建模思路

步骤一：把查询条件如“供应商A”通过筛选函数FILTER筛选出来

步骤二：把筛选结果进行金额从大到小排序，用到排序函数SORT

步骤三：返回查询条件中对应的数据行数，如查询前3项，就返回3行对应的数据；

操作步骤

Step1：表2：输入查询条件的标题行如“供应商编号、采购金额前几”，并输入对应的条件，如下图所示；为了方便截图，此处显示在一个页面内；如果不想一次一次输入，可以利用Excel的数据验证创建下拉框；

Step2：返回结果区域，复制表1的标题行，在I2录入函数：

=FILTER(B:D,B:B=F2)，此时的结果金额没有排序，内容也非常多。

Step3：对返回结果区域进行降序排序，I2录入函数：

=SORT(FILTER(B:D,B:B=F2),3,-1),SORT函数参数非常好理解，第一个为需要排序的区域，也就是步骤1返回的结果，第2个参数参与排序的列号，这里金额在第3列，录入3，第4个参数是升序还是降序，分别用1和-1表示。这里用-1表示降序；

Step4：对返回结果区域进行保留前N项，I2录入函数：

=TAKE(SORT(FILTER(B:D,B:B=F2),3,-1),G2),TAKE函数参数第一个为需要保留的区域，也就是步骤2返回的结果，第2个参数需要保留的的行号，这里录入的7，所以就保留7行；返回结果如下图；

到了这一步骤基本就结束了，但是对于一些要求非常高的领导，可能会说返回区域没有加边框，为什么不加边框呢？因这个区域的边框如果直接加的话就会变成定量，当查询结果发生变化，边框就不全了，如下图：

如可解决这个问题呢？

自动边框：

需要创建自动边框的话，可以用条件格式来判断，因为本身不影响数据，只是格式问题，所以思路就是单元格里面为空值就没有边框，有值就有边框，转换成条件就是当单元格""时候返回边框就可以了；

条件格式一般就是先设置一个单元格的格式，再用格式刷快速的刷到需要覆盖的数据区域就可以了，选中I1单元格，依次点开始条件格式新建规则使用公式创建规则为符合此公式的值设置格式条件框录入：=I1""，同时设置对应的格式，加上外边框，再把此单元格的格式用格式刷刷到需要自动显示边框的格式区域，一个自动边框就设置好了；

思路总结：

当源数据中出现一对多的情况下，用筛选函数FILTER，需要对结果排名，再嵌套SORT函数，对结果需要进行取舍保留的时候，用TAKE函数；

我是古哥：

从事制造行业18年，在企业运营、供应链管理、智能制造系统等方面具有丰富的实战经验。企业智能化，柔性化计划运营管理专家，擅长通过企业流程优化规范，企业管理、导入计划运营提升企业效率；对提高企业准时交货率，降低企业库存，输出智能制造人才有丰富的经验。学习PMC生产计划，关注古哥计划！