Excel 如何选取降序累计成本占总成本60%的对应数据条目
成本条目优先级筛选(累计达总支出60%)实现方案
筛选规则对齐
完全按照要求执行逻辑:从成本最高的条目开始,逐次累加成本值,直到累计总和≥所有条目总支出的60%,全程不允许跳过高值条目选低值凑数,和示例中选中香蕉、柠檬、橙子这类高成本条目的逻辑完全匹配。
带预置辅助列场景的快速实现
如果已经提前做好了三类辅助计算列,可直接套公式出结果:
- 现有列的预置逻辑
- 原始列:逐行存储条目名称、对应成本值
- 降序累计列:把所有条目按成本从高到低排好后,逐行累加的累计成本金额
- 占比列:逐行存储每行累计金额占所有条目总成本的比例
- 对应公式写法
- 先定位需要截取的截止位置:因为占比是逐行递增的,直接匹配占比列里第一个≥60%的行号就行,公式为
=MATCH(TRUE, 占比列范围>=0.6, 0)。这个行号就是我们需要的、从最高成本开始连续选的条目总数。 - 再结合原参考的INDEX+MATCH逻辑,截取对应区间的条目即可。如果是Excel 365/2021及以上版本,完全可以不用提前手动做降序、算累计值的辅助列,用动态数组一步算完,公式为:
公式运行逻辑:先把所有条目按成本从高到低排序,逐行累加求和,找到第一个累计占比达到60%的位置,自动截取前方对应的所有符合要求的条目,不需要手动维护辅助列。=TAKE( SORT(条目+成本的原始区域, 2, -1), MATCH( TRUE, SCAN(0, SORT(成本值列, 1, -1), LAMBDA(acc, curr, acc+curr))/SUM(成本值列) >= 0.6, 0 ) )
- 先定位需要截取的截止位置:因为占比是逐行递增的,直接匹配占比列里第一个≥60%的行号就行,公式为
旧版Excel兼容分步方案
对于不支持动态数组的旧版本Excel,用辅助列分步算就可以:
- 第一步:先算全量条目的总成本,公式是
=SUM(成本值所在的全部区域) - 第二步:加一个排名辅助列,输入
=RANK.EQ(当前行的成本值, 所有成本值区域, 0),给每个条目算降序排名——成本最高的排第1,次高的排第2,以此类推 - 第三步:再加一个累计占比辅助列,输入
=SUMPRODUCT((排名辅助列 <= 当前行排名)*成本值所在区域) / 之前算好的总成本单元格,拉到每一行填充后,找到第一个累计占比≥60%的行,对应的所有排名靠前的条目就是最终筛选结果。
特殊场景适配
- 碰到成本值相同的并列情况:RANK.EQ会给同成本的条目打一样的排名,算累计值的时候会自动把同档位的并列成本全部算进去,不会漏条目
- 不需要硬凑刚好60%的累计值:按逐次累加的规则,取到第一个覆盖60%的分组就停,不需要为了凑精确值多纳几个低价值条目进来
内容的提问来源于stack exchange,提问作者Simon
相关产品推荐
相关产品推荐

