Excel经典问题:资源分配

分享一个经典案例。

如图所示，左边是客户及其对应产品需求，比如客户1需要产品A，客户2需要产品C……

右边是可分配资源，比如产品A有4台，其编号分别是A001,A002,A003,A004,如何将这4个产品编号分配给左边的客户？

• COUNTIF+单边锁定

在给出完整公式之前，先搞清楚要用到的一个核心知识点，用COUNTIF单边锁定统计重复值出现的次数：

=COUNTIF($B$2:B2,B2)

COUNTIF第一参数的起始单元格锁定，截至单元格不锁定，向下填充时动态获取统计的数据区域，最终统计出各个产品由上往下出现的次数。

这种只锁定数据区域起始或截至边的方式通常称为单边锁定。

• 方案1，FILTER+CHOOSEROWS+COUNTIF

以下公式适用于高版本Excel:

=IFERROR(CHOOSEROWS(FILTER(F:F,E:E=B2),COUNTIF($B$1:B2,B2)),"待分配")

第一步用FILTER筛选出B2(也就是产品A)对应的所有产品编号。

第二步用CHOOSEROWS从FILTER筛选出来的数组中选取对应的行，COUNTIF返回的1.2.3….作为CHOOSEROWS的第二参数。

第三步，如果第二步返回错误值，则说明没有足够的资源来分配，所以用IFERROR把错误值转换为“待分配”。

• 方案2，VLOOKUP+COUNTIF+辅助列

低版本Excel中没有FILTER,CHOOSEROWS这两个函数，可以搭配辅助列完成。辅助列公式：

=COUNTIF($E$2:E2,E2)&E2

COUNTIF返回的出现次数连接产品，使得重复出现的”产品A”变成“1产品A”,”2产品A”,”3产品A”……

分配的公式：

=IFERROR(VLOOKUP(COUNTIF($B$2:B2,B2)&B2,D:F,3,0),"待分配")

第一步用COUNTIF单边锁定返回的次数连接B2中的”产品A”,得到与辅助列中对应的“1产品A”,”2产品A”,”3产品A”……

第二步用VLOOKUP查找“1产品A”并返回对应的产品编码。

第三步用IFERROR把错误值转换为“待分配“。