☰

傳回 N 個最高值的儲存格引用（位址）的函數

tag-icon • tag-icon microsoft-excel worksheet-function
傳回 N 個最高值的儲存格引用（位址）的函數

我正在製作一個電子表格來追蹤選舉結果並返回當選代表。我們採用的是比例代表制，改進的聖拉古法。選舉結果被輸入電子表格，然後除以給定數字（1.4、3、5 等，根據適用法律的規定，最多 15，總共八個商），得出商來分配席位。使用條件格式，電子表格現在會突出顯示八個最高的數字，顯示誰獲得了哪個位置。這一切都按預期進行。

我們需要 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(如果($C$2:$J$21=價值, 行($C$2:$J$21)))
找出第一行的編號value出現。同樣地，
=MIN(如果($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 $C$21:$J$21=C24,ROW($C$2:$J$21))) $2:$J$21=C24,列($C$2:$J$21)))))
進入儲存格B24，按Ctrl+ Shift+ Enter，然後向下拖曳/填滿到B31。

筆記：

  • CELL("address", …)傳回絕對地址（帶有美元符號）。如果您不需要它們，您可以使用 
    =替換（單元格（等等等等等等)、「$」、「」)
  • 這不能很好地處理重複值。例如，如果E3還包含 8（除了D4），它將報告它們都在D3（第一行和第一列）中。這可能很難修復，但可以解決測試 是否發生過這樣的巧合，允許手動修正。 
  • 雖然這可以在一個單元格中完成，但從長遠來看，如果您使用輔助列作為行和列值，可能會簡化您的工作。

這個答案部分是基於一個技巧這個答案巴里·胡迪尼。

相關內容