☰

N 個の最高値のセル参照 (アドレス) を返す関数

tag-icon • tag-icon microsoft-excel worksheet-function
N 個の最高値のセル参照 (アドレス) を返す関数

私は選挙結果を追跡し、選出された代表者を返すためのスプレッドシートを作成しています。私たちは比例代表制を採用しており、改良サント・ラグエ法選挙結果はスプレッドシートに入力され、特定の数値 (適用法で義務付けられているように、1.4、3、5 など、最大 15 の 8 つの商) で割って、議席を配分する商を算出します。条件付き書式を使用すると、スプレッドシートは上位 8 つの数字を強調表示し、誰がどのポジションを獲得するかを示します。これはすべて意図したとおりに機能します。

a) 8 つの成功した候補のリストを返すこと、b) そのリストが数値ではなくセル参照 (例: D7) を返すようにする必要があります。 関数を使用していろいろ試してみたところLARGE、基本的に という数式を使用して、C24:C31 の上位 8 つの商を正常に返すことができました。=LARGE($C$2:$J$21, ROW()-23)ただし、これらの商のグリッド座標を返すことはできません。

下のスクリーンショットは、私が実現したいことのモックアップを示しています。緑色で強調表示されたセルは、条件付き書式設定の結果として強調表示されたもので、C2:J21 の 8 つの最大値です。黄色で強調表示されたセルは、私が実現したいものです。明確にするために、セル参照 C2:J5 のデータは数式によって生成され、同様に C24:C31 の値は、上記の緑色で強調表示された 8 つの値と同じで、数式によって生成されています。非表示の行 6 から 21 は、基本的に行 1 から 5 の複製です。スプレッドシートをアップロードしました。グーグルドライブ

問題のスプレッドシートのスクリーンショット

答え1

これは難しいですね。

=MIN(IF($C$2:$J$21=価値、ROW($C$2:$J$21)))
最初の行の番号を見つけるにはvalue表示されます。同様に、
=MIN(IF($C$2:$J$21=価値、列($C$2:$J$21)))
最初の列の番号を見つけますvalueが表示されます。上記は配列数式です。

上位8つの値が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(最初の行と最初の列) にあると報告されます。これを修正するのは非常に難しいかもしれませんが、テスト そのような偶然が起こったかどうかを確認し、手動で修正できるようにします。 
  • これをすべて 1 つのセルで実行することもできますが、行と列の値にヘルパー列を使用すると、長期的には作業が簡素化される可能性があります。

この答えは、この答えバリー・フーディーニ著。

関連情報