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

esc0rp 发表于 2024-10-2 17:23:54

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

<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-axegupay5k/2a1087b1c4d64f83870bf36c55160e28~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727993062&x-signature=gTrh7s7AIqNT%2FTNCU5ynyme9CNs%3D" style="width: 50%; margin-bottom: 20px;"></div>
曾经幻想过您持有海量数据，并且能够从中得到顶部或底部 N 值的图表，更好的是，您能够在顶部 N 和底部 N 值的图表之间切换，就像打开/关闭开关同样简单？
这便是咱们今天要做的。针对哪些想要制作 Excel 仪表板的人来讲，这是必须认识的一件事，由于决策者爱好认识特定 KPI 中表现最佳和最差的元素。以下是本教程结束时咱们将得到的内容：
目录Excel RANK() 函数 – 简介设置数据设置表单控件自动化流程——重新审视 RANK 函数设置图表
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/bb8578552e53424d81a2ce6acfa89d14~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727993062&x-signature=PNUQjNQfW1xNlCux5v3YfGCps7o%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">Excel RANK() 函数 – 简介</h1>
倘若您爱好 Excel 仪表板，此功能迟早会作为您的后袋配件。让咱们认识一下这个函数的功效：
排名（数字，参考，[顺序]）
number：您想要与列表或数组中的其他数字进行比较以得到排名的数字
ref：简单来讲，位置是数据集中的数字集合。这肯定是一个范围。
order：可选参数。这会根据列表的升序或降序方式对数字进行排名。它能够是零 0 或一 1。默认状况下它是 0。
下载 Excel 教程工作簿
链接: https://pan.baidu.com/s/1NZI3JzPv6JITWWxJABzxMQ?pwd=dak3 提取码: dak3 复制这段内容后打开百度网盘手机App，操作更方便哦
<h1 style="color: black; text-align: left; margin-bottom: 10px;">设置数据</h1>
过程1：获取所需数据。就我而言，它是从 1 月到 12 月的 12 个月的简单营销数据。
第 2 步：在 C 列中给出标题“排名”或任何其他适当的标题以供理解。我给了排名。
第 3 步：因为我的数据从第 2 行起始，况且我的排名列亦从同一行起始，因此事情对我来讲很容易。将以下公式放入单元格 C2 中
=排名(B2,$B$2:$B$13)
此公式将单元格 B2 中的值与 B2 到 B13 范围内的其他数字进行检测，并按照它们进行排名。
双击填充手柄，它将在单元格中填充营销列中每一个数字的排名。因为咱们无说到次序参数，Excel 会将其视为零，将最低数字赋予最高值。在咱们的例子中，最高值为 1914696，因此呢该数字的排名为 1。
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/6661124bbde74662862c68cb489e20c5~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727993062&x-signature=HAo%2B4XibSOar4qTixZkbwEsGMl0%3D" style="width: 50%; margin-bottom: 20px;"></div>
过程 4：转到单元格 F1 并创建标题N Rank。
过程5：在单元格F2中输入1，在F3中输入2，在F4中输入3，在F5中输入4，在F6中输入5。5 由于咱们需要营销数据中的前 5 名或后 5 名。倘若咱们想要更少或更加多，咱们能够在这儿调节。
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/357899fad2c347edbcea47fea8a21a35~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727993062&x-signature=Sf8KhPGMqZeUnHwf4eWZuzpR%2FZU%3D" style="width: 50%; margin-bottom: 20px;"></div>
第 6 步：此刻咱们需要从 1 到 5 的每一个排名的营销价值和相应的月份。为此，咱们能够运用 INDEX 和 MATCH 函数的组合。转到单元格 E2 并输入以下公式：
=INDEX($A$2:$B$13,MATCH(F2,$C$2:$C$13,0),2)
INDEX 函数从数据集中获取特定值。但倘若数据集中有多于 1 列，咱们必须为其供给行号和列号。日前，咱们正在寻找营销价值，咱们选取了 A2 到 B13 范围内的数据，营销价值位置于第二列。这便是为何咱们输入值 2，您能够在公式末尾看到该值。
然则，咱们仍然需要行号。为此，咱们运用 MATCH 函数。请记住，F 列拥有排名值。咱们期盼 Excel 查询单元格 F2 中 C2 到 C13 范围内的值，并返回该值所在的行号。匹配函数将与行号一块运用。
一旦咱们有了这个行号，索引号此刻就能够运用它来获取营销值，由于独一缺少的参数是获取营销值的行号。倘若 INDEX + MATCH 工作对您来讲仍然陌生，请不要担心。我此刻会尽快写有些关于它们的文案。
向下拖动填充柄以填充单元格，直到单元格 E6。
第7步：此刻咱们有了营销值，咱们亦需要相应的月份。为此，请转到单元格 D2 并输入以下公式：
=INDEX($A$2:$B$13,MATCH(F2,$C$2:$C$13,0),1)
它与上面讨论的公式完全相同。独一的区别是最后有 1。因为咱们正在寻找月份，因此呢月份列在所选范围的第1列中，1。
以下动画将引导您完成以上过程：
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/86ff9bdcbc7a414b8eb11ef2cb7de989~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727993062&x-signature=pKA4UAguTE8W17K6RNmTcCZHc%2BQ%3D" style="width: 50%; margin-bottom: 20px;"></div>
此刻咱们已然设置好要运用的数据了。还剩下两件事：表单控件图表<h1 style="color: black; text-align: left; margin-bottom: 10px;">设置表单控件</h1>
第 1 步：倘若尚未启用，请启用“研发人员”选项卡。瞧瞧下面的动画演练
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/b60c0452894840f9a39aba2bc0b7121d~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727993062&x-signature=tJeovZ4Dw2iuxTQsWDUJi%2Fj5HFI%3D" style="width: 50%; margin-bottom: 20px;"></div>
过程2：转到研发人员选项卡>控件组>单击插进下拉按钮>在表单控件下>单击选项按钮。绘制按钮。倘若第1次尝试不完美，请不要担心，您能够稍后进行调节。
第 3 步：右键单击插进的按钮 > 编辑文本并输入 销量最高的 5 个月
过程 4：再次右键单击 > 设置控件格式 > 在单元格链接中将绝对位置放入单元格 H7，即 $H$7。单击“确定”。此刻该按钮已与单元格 H7 链接。
过程 5：在过程 2 之后插进另一个按钮。而后将其文本更改为Bottom 5 Months by sale
过程 6：我的第二个按钮已然链接到单元格 H7，但倘若您的按钮无链接到单元格 H7，那样您能够根据上面的过程 4 进行操作。
此刻，倘若您单击两个按钮中的任何一个，单元格 H7 中的值将在 1 和 2 之间变化。咱们能够充分利用此功能。
以下动画将引导您快速完成以上简单过程：
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/bf5bdc0f9bd244359777e14fdbd1a216~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727993062&x-signature=4w0vw3N6gjhJ9otu8HAN%2BuNm0as%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">自动化流程——重新审视 RANK 函数</h1>
截止日前咱们已然有了名单。按营销额排名前 5 个月。然而，咱们仍然错失了营销额最低的 5 个月。咱们能够经过更改 Rank 列中说到的函数中的一个参数来容易得到它。还记得 RANK 函数中的次序参数吗？是时候运用它了！
咱们能够运用 order 参数获取前 5 个和后 5 个值，此刻咱们运用 INDEX 和 MATCH 函数的原由就更有道理了。
转到单元格 C2 并将现有公式更改为：
=排名(B2,$B$2:$B$13,IF($H$7=1,0,1))
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/93d5d79b10de46e3abcc6acdf1cf8af8~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727993062&x-signature=wem2MpKuYGHvRvYOBIBxKy3Jd30%3D" style="width: 50%; margin-bottom: 20px;"></div>
重视到这个公式和旧公式之间的区别了吗？让我把这两个公式放在一块以便更好地理解：
旧公式：=RANK(B2,$B$2:$B$13)
新公式：=RANK(B2,$B$2:$B$13,IF($H$7=1,0,1))
咱们添加了这部分： IF($H$7=1,0,1)
还记得倘若单击插进的按钮，单元格 H7 中的值就会更改 1-2 吗？咱们将运用它来自动化咱们的次序参数。因此呢，倘若 H7 中有 1，则 IF 函数将向公式发送 0，倘若不是 1。这般，咱们的次序就会从升序变为降序，从而排名前 5 名和后 5 名。
拖动填充柄以运用更新的公式填充全部范围。此刻单击按钮，就可看到排名立即更改其在列中的次序。这亦将更改包括前/后 N 个值的第二个列表中的值。
此刻，运用此类动态数据绘制的图表亦会出现变化。啊啊啊我爱好细节！
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/81001661e63d404cacf0ebee60602003~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727993062&x-signature=UbdfygVuL6qB8rwfib09gpQP7E4%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">设置图表</h1>
选取列表中的数据（包括 5 个条目的第二个数据集），而后转到“插进”选项卡 > 图表组 > 折线图 > 二维柱形图 > 聚集柱形图，它将插进图表。此刻，按照您的需要进行摆列，而后单击按钮，您就会看到图表随着您的点击而舞动！
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p26-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/7667a5cd8c254eeaad792bfe578d0dd2~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727993062&x-signature=qWs1G93hbjfGotoJJusa4t0T67M%3D" style="width: 50%; margin-bottom: 20px;"></div>
惊人的！
因此呢，在这儿，您能够在图表上绘制营销量前 5 个月和后 5 个月的工作呼气变化，随时能够在您点击通告时进行切换。经过一个小的美容提高，这便是我所持有的：
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/a8e477bc4a844783b201dc9da67321a9~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727993062&x-signature=Mw7AL1zfNoh1hjgBYboiA0ZUU8Y%3D" style="width: 50%; margin-bottom: 20px;"></div>

4zhvml8 发表于 2024-11-9 23:04:47

哈哈、笑死我了、太搞笑了吧等。

页: [1]

天涯论坛's Archiver

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