247 WPS新函数案例：各分厂业绩动态排名

一份考核表，需要对不同的工厂进行QCD考核，也就是质量（Quality）、成本（Cost）和交付（Delivery），这些是评估工厂绩效的重要指标，通过对 QCD 的有效管理和持续改进，可以提高工厂的竞争力和盈利能力。

先已经对各省对应工厂考核结果有了初步的统计，现在需要按省级来汇总，各省对应的工厂，全部合格（QCD三大指标都为合格）的数量有多少？这个汇总还需要动态排名，也就是当指标有变化的时候，排名也自动变化，按升序排序（合格率最低的排在第一位）；效果如下图1；

图1

需求分析：

源数据中已经有的信息就是各省各工厂的QCD三大指标的完成情况，没有对应的工厂的合格数量统计，所以需要先统计出工厂三大指标的合格数量是多少？满足合格数量是数字3就表示工厂合格了，有了这个信息就可以通过统计函数多条件统计各省各工厂的合格率了。

本次需求的难点就是动态合格率，所以解决的思路就是把这个统计结果作为辅助列，再通过排序函数排序合格率就实现了动态合格率，但这不符合需求，需求虽然没有明确说不准用辅助列，但一般情况下，不用辅助列的解决方案对于数据建肯定是最优。

要实现动态排名并且不用辅助列，就需要用到WPS的新函数，合并函数和选择列函数。先用合并函数把所以函数合并到一起，再用排序函数排序，最后用选择列函数进行分列。完成后就可以得到动态合格率了。整体的解决思路如下图：

图2

分别写公式：

源数据中统计工厂的合格数量，用到的函数就是COUNTIFS

录入函数：G3=COUNTIFS(D3:F3,"合格")

函数释义：统计D3:F3，满足条件“合格”的数量，并下拉填充，效果如下图3；

图3

对省区进行去重，录入函数：=UNIQUE(B3:B14)，得到省份的唯一值，如果后面还有新的数据产生，可以把公式更改为：

=DROP(UNIQUE(B3:B1400),-1)

新公式释义：预计了B3:B1400这个区域数据，对这个范围去重的话，返回结果最后一个是0，配合DROP函数进行去除，-1代表去除最后一行；

图4

工厂数量与合格数量的逻辑是一样的，都是多条件统计，统计出来后，用合格数量除以工厂数量就可以得到合格率。

工厂数量：J3=COUNTIFS(B3:B14,I3#)

函数释义：单条件统计，统计B列中省出现多少次，对应多少次就是多少工厂数量；

合格数量：K3=COUNTIFS(B3:B14,I3#,G3:G14,3)

函数释义：多条件统计，同时统计B列中省份出现的次数，和G列中出现3的次数，3代表全部合格；

合格率：L3=K3#/J3#

完成后的效果如下图5所示：

图5

对这个结果进行排序（辅助列解法），录入函数：

I12=SORT(I3:L6,4)

函数释义：对返回的结果（I3:L6)，的第4列（合格率）进行升序排序；效果如图6所示；

图6

合并公式：

不用辅助列的方法就是利用WPS的新函数HSTACK，分别把刚刚写的公式用HSTACK合并起来。根据这个函数的特点，只需要把刚才的公式一个一个串起就可以了；

录入函数：

=HSTACK(DROP(UNIQUE(B3:B1400),-1),COUNTIFS(B3:B14,DROP(UNIQUE(B3:B1400),-1)),COUNTIFS(B3:B14,DROP(UNIQUE(B3:B1400),-1),G3:G14,3))

公式看起来很长，其实只是B列的数据进行了多次引用，如果有LET函数定义一下，公式就会简单多了。效果如下图7所示；

图7

排序数据：

刚刚用HSTACK只是把公式合并了，并没有对数据进行排序，需求中明确说了需要动态排序，只是排序函数就可以用上了。根据排序需求：合格率低排在第1位，所以需要对刚刚的合并结果的第3列合格数排序，录入公式：

=SORT(HSTACK(DROP(UNIQUE(B3:B1400),-1),COUNTIFS(B3:B14,DROP(UNIQUE(B3:B1400),-1)),COUNTIFS(B3:B14,DROP(UNIQUE(B3:B1400),-1),G3:G14,3)),3)

图8

选择数据：

用合并函数加排序函数对数据进行了排名，这个结果是一个区域，也就是多行多列，无法进行运算，配合选择列函数就可以运算了，录入函数：

=CHOOSECOLS(SORT(HSTACK(DROP(UNIQUE(B3:B1400),-1),COUNTIFS(B3:B14,DROP(UNIQUE(B3:B1400),-1)),COUNTIFS(B3:B14,DROP(UNIQUE(B3:B1400),-1),G3:G14,3)),3),1),选择第1列，第1列数字为1，第2列数字为2，第3列数字为3，这里就一一录入了；

录入完成后就可以直接运算了录入=K3#/J3#，这样一个不用辅助列的动态排名就完成了，最后的效果如下图：

图9

我是古哥：

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