德鲁克说，如果卓有成效有唯一秘诀的话，那就是聚焦。什么叫聚焦？简单地说，就是分清主次，抓住重点。
ABC分类法正是一种分清主次，抓住重点和聚焦的方法。
关于ABC分类法理论和思路，大家耳熟能详，我就不过多介绍了（可参考我关于ABC-XYZ矩阵的相关文章），本文主要聊一聊如何用Excel进行ABC分类。
原ABC分类方法的三个问题。
我曾经讲过用Excel进行ABC分类的方法和相关函数操作（见许栩《供应链管理专家都是技术控：PMC总监手把手教你Excel函数》第5章），为什么今天还要聊这个话题呢？
前段时间，我向一位伙伴介绍ABC分类法的实操，使用了我书中介绍的方法。这位伙伴提出三个问题，让我觉得这个方法尽管可以用，但还有改进的空间。
第一个问题，进行分类之前要先排序（降序），这非常麻烦。
第二个问题，有个BUG，如果有一个产品干了70%或以上的业绩，就没有A类了；如果干了90%或以上的业绩，连B类都没有，全是C类。
第三个问题，采用LOOKUP函数区分金额ABC和品项ABC有点复杂，不好理解（LOOKUP判断项要求升序排列，排序后呈现的与ABC分类标准相反）。
我一看，这三个问题确实存在，我确实没考虑这三个场景。在感谢他之后，我重新设计公式，基本解决了这三个问题。
下面，我以一组模拟的数据为例，分别介绍原来的方法和重新设计的方法。（提供Excel两张原表，请留言，或私 L）
如下图，产品的销售列表，8周销售业绩，共70行，也就是70个产品（当然，实际工作中产品SKU可能有700种，7000种）。
原ABC分类方法的步骤。
原数据见上图。
本例设定的ABC标准是：金额的70%并且品项数的前10%为A类，两个条件同时满足；金额和品项数都占20%的为B类，两个条件同时满足；金额的10%并且品项数的前70%为C类，同样，两个条件同时满足。
为了解决两个条件同时满足问题，我的算法是先不管品项，确定金额的ABC；再不管金额，确定品项的ABC；最后，根据金额的ABC和品项的ABC，用LOOKUP函数计算确定正式的ABC。
这一步，原方法和新方法都是一样的，如下图所示。
原ABC分类方法九个步骤：
1、计算每个产品8周的总销量，简单求和。
2、选择产品编码、8周及合计全部数量，以合计（总销量）进行降序排序（如下图所示）。当数据更新后，重新ABC分类时，这一步人工排序不可少，这正是那位伙伴提出的第一个问题。每次都要排序，太麻烦。
3、排序后，计算每个产品的金额占比。每个产品的销售金额，除以全部70个产品的总金额。
4、计算金额的累计占比。对每个产品的金额占比进行累计求和，用SUM函数固定首行的方式进行，本例公式为“=SUM(L$3:L3)”。
5、计算金额ABC。使用LOOKUP函数，公式为“=LOOKUP(M3,$U$3:$V$5)”，如下图所示。注：采用这种方法，如果出现单个或极少数产品干了70%或以上的业绩，就会出现那位伙伴说的第二个问题，ABC分类失效（没有A类或没有AB两类）。另，这里也会出现他所说的第三个问题（LOOKUP升序排序后与标准相反，不容易理解，很难解释清楚）。
6、计算每个产品的品项占比。一个编码就是一个品项，每个品项的占比都是1除以70（全部品项数）。
7、计算累计品项占比。对每个产品的品项占比进行累计求和，计算方法和公式与计算金额的累计占比相同。
8、计算品项ABC。使用LOOKUP函数，公式为“=LOOKUP(P3,$W$3:$X$5)”，计算方法与计算金额ABC相同。此处也会出现第三个问题。
9、计算正式ABC。金额ABC及品项ABC之后，再用LOOKUP计算正式ABC，具体公式前面已有说明。
更新后ABC分类方法的步骤。
仍用上面的原数据。
1、计算每个产品8周的总销量，简单求和。
2、销量自动排序（降序）。在L列（合计数的后一列），用LARGE函数对销量进行自动降序排序。——排序后，销售从高到低在L列展示，其它各行各列均不变。后续数据更新，或增减产品，直接动源数据或增减行数拖动公式即可，这就解决了那位伙伴的第一个问题。
公式为“=LARGE($K$3:$K$72,ROW(E1))”。LARGE函数是求第k个最大值，ROW是行号函数，第1行ROW（E1）是求第1个最大值。当向下拖动公式时，ROW（E1）依次变为ROW（E2）、ROW（E3）…，即求第2个、第3个、…最大值，这样就完成了从大到小的降序排序，如上图所示。
3、计算销量累计占比。公式为“=SUM(L$3:L3)/SUM($L$3:$L$72)”，前一个SUM用了固定首行，计算累计金额（对LARGE 函数排序后的金额）；前一个SUM是计算全部产品的总金额。
注：第2、第3步计算的目的，是为了第4步计算A类B类的阈值。
4、计算金额ABC分类A类B类的判定阈值。70%对应一个值，等于或大于这个值的就是A类（即金额占比70%）；20%对于另一个值，等于或大于那个值的就是B类（即金额占比20%）；剩下的全是C类。——以判定阈值判定ABC，解决那位伙伴的第二个问题，就算有单个或极少数产品干了70%或以上的，也是从A类开始判定。
所有的A类销售金额加起来要大于或等于总金额的70%，怎么算呢？如上图所示，A类阈值的计算公式为“=MAX(IF($M$3:$M$72>=T4,$L$3:$L$72))”。解释如下：
(1)、IF($M$3:$M$72>=T4，对金额累计占比（M列）进行判断，找到符合大于或等于A类标准70%（>=T4）的。因累计占比累计的是自动排序后的销售金额（L列），所以，当累计占比大于或等于70%时，就代表前面的销售金额加起来大于或等于总金额的70%。
(2)、MAX(IF($M$3:$M$72>=T4,$L$3:$L$72))对自动排序后的金额（L列）进行比较，因为是降序排序，当累计占比大于或等于70%时，所以，符合条件的数里面，最大的就是第一个大于或等于70%的数。
B类阈值的计算方法相同，条件判断值为A、B类之和（90%），公式为“=MAX(IF($M$3:$M$72>=T4+T5,$L$3:$L$72))”。
6、计算金额ABC。使用IF函数判断即可，大于或等于A类阈值的，为金额A类；大于或等于B类阈值的，为金额B类；剩下的全部为金额C类。公式为“=IF(K3>=$V$4, "A",IF($K3>=$V$5, "B", "C"))”。——用IF函数进行条件判断，直观好理解，解决了那位伙伴的第三个问题。
7、计算每个产品的金额百分位数。百分位数简单地说，就是某个值在数据集中的排名,以数据集的百分比表示。比如某个值的百分位数是90%，那么代表这个数据集中90%的数小于或等于它。（百科：第p百分位数是这样一个值，它使得至少有p%的数据项小于或等于这个值，且至少有(100-p)%的数据项大于或等于这个值。）
计算公式为“=PERCENTRANK.EXC($K$3:$K$72,K3)”，如上图所示。
8、计算品项ABC。使用IF函数判断，百分位数大于或等于90%的，为品项A类；大于或等于70%的，为品项B类；剩下的全部为品项C类。公式为“=IF(O3>=90%,"A",IF(O3>=70%,"B","C"))”。
9、计算正式ABC。同原ABC分类方法，公式为“=LOOKUP(N3&P3,$W$3:$X$11)”。
总结一下。
ABC分类的目的是区分重点和一般，分清主次，从而聚焦资源，抓主要矛盾，集中优势兵力打歼灭战。或者说抓重点，抓大放小，提升资源利用效率。
用Excel进行ABC分类，计算金额ABC时，如果不想每次手动排序，那么，可以先用LARGE函数进行自动排序，再用MAX+IF数组计算A类B类的阈值，最后用IF函数进行判断。计算品项ABC时，如果不想用LOOKUP函数，可以用PERCENTRANK.EXC函数计算百分位数，再用IF函数判断。