运用 RANK() 函数和表单控件绘制顶部/底部“N”值的 Excel 图表

esc0rp

曾经幻想过您持有海量数据，并且能够从中得到顶部或底部 N 值的图表，更好的是，您能够在顶部 N 和底部 N 值的图表之间切换，就像打开/关闭开关同样简单？

这便是咱们今天要做的。针对哪些想要制作 Excel 仪表板的人来讲，这是必须认识的一件事，由于决策者爱好认识特定 KPI 中表现最佳和最差的元素。以下是本教程结束时咱们将得到的内容：

目录

Excel RANK() 函数 – 简介设置数据设置表单控件自动化流程——重新审视 RANK 函数设置图表

Excel RANK() 函数 – 简介

倘若您爱好 Excel 仪表板，此功能迟早会作为您的后袋配件。让咱们认识一下这个函数的功效：

排名（数字，参考，[顺序]）

number：您想要与列表或数组中的其他数字进行比较以得到排名的数字

ref：简单来讲，位置是数据集中的数字集合。这肯定是一个范围。

order：可选参数。这会根据列表的升序或降序方式对数字进行排名。它能够是零 0 或一 1。默认状况下它是 0。

下载 Excel 教程工作簿

链接:

https://pan.baidu.com/s/1NZI3JzPv6JITWWxJABzxMQ?pwd=dak3 提取码: dak3 复制这段内容后打开百度网盘手机App，操作更方便哦

设置数据

过程1：获取所需数据。就我而言，它是从 1 月到 12 月的 12 个月的简单营销数据。

第 2 步：在 C 列中给出标题“排名”或任何其他适当的标题以供理解。我给了排名。

第 3 步：因为我的数据从第 2 行起始，况且我的排名列亦从同一行起始，因此事情对我来讲很容易。将以下公式放入单元格 C2 中

=排名(B2,$B$2B$13)

此公式将单元格 B2 中的值与 B2 到 B13 范围内的其他数字进行检测，并按照它们进行排名。

双击填充手柄，它将在单元格中填充营销列中每一个数字的排名。因为咱们无说到次序参数，Excel 会将其视为零，将最低数字赋予最高值。在咱们的例子中，最高值为 1914696，因此呢该数字的排名为 1。

过程 4：转到单元格 F1 并创建标题N Rank。

过程5：在单元格F2中输入1，在F3中输入2，在F4中输入3，在F5中输入4，在F6中输入5。5 由于咱们需要营销数据中的前 5 名或后 5 名。倘若咱们想要更少或更加多，咱们能够在这儿调节。

第 6 步：此刻咱们需要从 1 到 5 的每一个排名的营销价值和相应的月​份。为此，咱们能够运用 INDEX 和 MATCH 函数的组合。转到单元格 E2 并输入以下公式：

=INDEX($A$2B$13,MATCH(F2,$C$2C$13,0),2)

INDEX 函数从数据集中获取特定值。但倘若数据集中有多于 1 列，咱们必须为其供给行号和列号。日前，咱们正在寻找营销价值，咱们选取了 A2 到 B13 范围内的数据，营销价值位置于第二列。这便是为何咱们输入值 2，您能够在公式末尾看到该值。

然则，咱们仍然需要行号。为此，咱们运用 MATCH 函数。请记住，F 列拥有排名值。咱们期盼 Excel 查询单元格 F2 中 C2 到 C13 范围内的值，并返回该值所在的行号。匹配函数将与行号一块运用。

一旦咱们有了这个行号，索引号此刻就能够运用它来获取营销值，由于独一缺少的参数是获取营销值的行号。倘若 INDEX + MATCH 工作对您来讲仍然陌生，请不要担心。我此刻会尽快写有些关于它们的文案。

向下拖动填充柄以填充单元格，直到单元格 E6。

第7步：此刻咱们有了营销值，咱们亦需要相应的月份。为此，请转到单元格 D2 并输入以下公式：

=INDEX($A$2B$13,MATCH(F2,$C$2C$13,0),1)

它与上面讨论的公式完全相同。独一的区别是最后有 1。因为咱们正在寻找月份，因此呢月份列在所选范围的第1列中，1。

以下动画将引导您完成以上过程：

此刻咱们已然设置好要运用的数据了。还剩下两件事：

表单控件图表

设置表单控件

第 1 步：倘若尚未启用，请启用“研发人员”选项卡。瞧瞧下面的动画演练

过程2：转到研发人员选项卡>控件组>单击插进下拉按钮>在表单控件下>单击选项按钮。绘制按钮。倘若第1次尝试不完美，请不要担心，您能够稍后进行调节。

第 3 步：右键单击插进的按钮 > 编辑文本并输入 销量最高的 5 个月

过程 4：再次右键单击 > 设置控件格式 > 在单元格链接中将绝对位置放入单元格 H7，即 $H$7。单击“确定”。此刻该按钮已与单元格 H7 链接。

过程 5：在过程 2 之后插进另一个按钮。而后将其文本更改为Bottom 5 Months by sale

过程 6：我的第二个按钮已然链接到单元格 H7，但倘若您的按钮无链接到单元格 H7，那样您能够根据上面的过程 4 进行操作。

此刻，倘若您单击两个按钮中的任何一个，单元格 H7 中的值将在 1 和 2 之间变化。咱们能够充分利用此功能。

以下动画将引导您 快速 完成以上简单过程：

自动化流程——重新审视 RANK 函数

截止日前咱们已然有了名单。按营销额排名前 5 个月。然而，咱们仍然错失了营销额最低的 5 个月。咱们能够经过更改 Rank 列中说到的函数中的一个参数来容易得到它。还记得 RANK 函数中的次序参数吗？是时候运用它了！

咱们能够运用 order 参数获取前 5 个和后 5 个值，此刻咱们运用 INDEX 和 MATCH 函数的原由就更有道理了。

转到单元格 C2 并将现有公式更改为：

=排名(B2,$B$2B$13,IF($H$7=1,0,1))

重视到这个公式和旧公式之间的区别了吗？让我把这两个公式放在一块以便更好地理解：

旧公式：=RANK(B2,$B$2B$13)

新公式：=RANK(B2,$B$2B$13,IF($H$7=1,0,1))

咱们添加了这部分： IF($H$7=1,0,1)

还记得倘若单击插进的按钮，单元格 H7 中的值就会更改 1-2 吗？咱们将运用它来自动化咱们的次序参数。因此呢，倘若 H7 中有 1，则 IF 函数将向公式发送 0，倘若不是 1。这般，咱们的次序就会从升序变为降序，从而排名前 5 名和后 5 名。

拖动填充柄以运用更新的公式填充全部范围。此刻单击按钮，就可看到排名立即更改其在列中的次序。这亦将更改包括前/后 N 个值的第二个列表中的值。

此刻，运用此类动态数据绘制的图表亦会出现变化。啊啊啊我爱好细节！

设置图表

选取列表中的数据（包括 5 个条目的第二个数据集），而后转到“插进”选项卡 > 图表组 > 折线图 > 二维柱形图 > 聚集柱形图，它将插进图表。此刻，按照您的需要进行摆列，而后单击按钮，您就会看到图表随着您的点击而舞动！

惊人的！

因此呢，在这儿，您能够在图表上绘制营销量前 5 个月和后 5 个月的工作呼气变化，随时能够在您点击通告时进行切换。经过一个小的美容提高，这便是我所持有的：