前 5 名客户(总欠款额)

前 5 名客户(总欠款额)

我想从数据列表中获取前 5 个值,但有一个问题。假设列表中有客户姓名和应付金额。但客户姓名重复。因此,我需要从此列表中获取前 5 名客户。因此,首先我们必须添加,然后使用大型公式来获取前 5 名。但是有没有一个公式可以让我们在一个公式中完成这个任务?我目前正在对其进行透视,然后使用大型函数来完成此任务。

独特客户案例:

在此处输入图片描述

回头客案例(问题):

在此处输入图片描述

点击上方图片可放大查看

文件链接:

https://drive.google.com/open?id=1J2on7BHYTfbMFp0mRbTElCA2vT7tw3QQ

答案1

数据透视表是一种很好的方法,但之后您不需要一个大公式。只需让数据透视表为您进行排序即可获得前 5 名。

在数据透视表顶部的下拉菜单中选择“更多排序选项”,然后按欠款金额总和排序。

在此处输入图片描述

答案2

我根据与 OP 关联的数据集提出了此解决方案。请查看屏幕截图。

在此处输入图片描述

注意:

我使用的数据范围是 A282:C296,因此您可以根据需要进行调整。

我已经对客户姓名以及他们的前 5 名进行了排序。

我使用的公式在单元格 I282 中,用以下公式完成Ctrl+Shift+Enter最后把它拖下来。

{=MAX((B282:B296=G282)*C282:C296)}

单元格 G282 是输出中的客户名称 A。

笔记,您的姓名列表有重复,但我使用了您挑选的五个姓名来获取其最高金额。例如,名称 A 有 2 个值并且最高值是 5,35,454。 这就是为什么我的清单(红色)有几种不同的价值。

希望这对你有帮助。

答案3

您可以仅使用公式来创建以下数据排名:

在此处输入图片描述

预处理

A列和B列的数据是原始数据,没有公式。

在 D4 中 -获取唯一名称

=LOOKUP(2,1/(COUNTIF($D$3:D3,$A$4:$A$22)=0),$A$4:$A$22)

确保 $A$4:$A$22 的两个实例被替换为足够大的数组以覆盖所有数据,并且如果数据集增长,则需要更多数组。

向下拖动,直到有足够多的行来列出每个唯一的名称。

在 E4 中 - 对每个人所欠金额进行求和:

=SUMIFS($B$4:$B$22,$A$4:$A$22,D4)

再次确保两个数组足够大以覆盖所有数据,并且如果数据集增长,则数组需要更大。

向下拖动至 D 列旁边。

在 F4 中 -不跳过值的排序

=IF(E4="","",RANK(E4,$E$4:$E$19)+COUNTIF($E$4:E4,E4)-1)

确保范围 $E$4:$E$19 指的是整个相邻列数据范围,然后向下拖动到 E 列旁边。

结果

在 H4:H8 中输入原始数字,没有公式。

在 I4 - 索引匹配中按等级顺序查找名称:

=INDEX($D$4:$D$19,MATCH(H4,$F$4:$F$19,0))

在 J4 - 索引匹配中查找相应的金额:

=INDEX($E$4:$E$19,MATCH(H4,$F$4:$F$19,0))

将这两个拖到 H 列中列出的 1-5 旁边。

相关内容