![返回 N 个最高值的单元格引用(地址)的函数](https://linux22.com/image/1585586/%E8%BF%94%E5%9B%9E%20N%20%E4%B8%AA%E6%9C%80%E9%AB%98%E5%80%BC%E7%9A%84%E5%8D%95%E5%85%83%E6%A0%BC%E5%BC%95%E7%94%A8%EF%BC%88%E5%9C%B0%E5%9D%80%EF%BC%89%E7%9A%84%E5%87%BD%E6%95%B0.png)
我正在制作一个电子表格来跟踪选举结果并选出当选代表。我们采用比例代表制,应用改良的 Sainte-Laguë 方法。选举结果被输入到电子表格中,然后除以给定数字(1.4、3、5 等,最高可达 15,根据适用法律规定,总共得到 8 个商数),得到分配席位的商数。使用条件格式,电子表格现在突出显示八个最高数字,显示谁获得了什么位置。这一切都按预期进行。
我们需要 a) 返回八个成功候选人的列表,并且 b) 让该列表返回单元格引用(例如 D7),而不是数字。我尝试使用该LARGE
函数,并成功使用公式返回 C24:C31 中的前八个商,该公式本质上是。=LARGE($C$2:$J$21, ROW()-23)
但是,我无法让它返回这些商的网格坐标。
下面的屏幕截图显示了我想要实现的目标的模型。以绿色突出显示的单元格是条件格式的结果;它们是 C2:J21 中最大的八个值。以黄色突出显示的单元格是我想要实现的目标。要清楚;单元格引用 C2:J5 中的数据是由公式生成的,同样,C24:C31 中的值也是上面以绿色突出显示的八个值;它们是公式生成的。隐藏的第 6 行到第 21 行基本上是第 1 行到第 5 行的重复。我已将电子表格上传到Google 云端硬盘
答案1
这很棘手。我们可以使用
=MIN(IF($C$2:$J$21=价值,行($C$2:$J$21)))查找第一行的编号
value
出现。同样,=MIN(IF($C$2:$J$21=价值, 列($C$2:$J$21)))查找第一列的编号
value
出现。以上是数组公式。
如果中的前八个值C2:J21
是唯一的,
我们可以使用上面的方法在该网格中找到一个值。然后
索引($A$1:$J$21,行号,列号)将索引该单元格,我们可以使用
CELL("地址", INDEX(以上))获取该单元格的行和列地址。
因此,输入
=CELL("地址", INDEX($A$1:$J$21, MIN(IF($C$2:$J$21=C24,ROW($C$2:$J$21))), MIN(IF($C$2:$J$21=C24,COLUMN($C$2:$J$21)))))进入单元格
B24
,按Ctrl+ Shift+ Enter,然后向下拖动/填充至B31
。
笔记:
CELL("address", …)
返回绝对地址(带美元符号)。如果您不想要它们,您可以使用=SUBSTITUTE(单元格(等等等等等等),“$”,“”)
- 这不能很好地处理重复值。例如,如果
E3
还包含 8(除了D4
),它将报告它们都在D3
(第一行和第一列)。这可能很难修复,但可以测试 是否发生过这样的巧合,以便进行手动纠正。 - 虽然这可以在一个单元格中完成,但从长远来看,如果您使用辅助列作为行和列的值,它可能会简化您的生活。
这个答案部分基于这个答案由巴里·胡迪尼 (Barry Houdini) 创作。