会社に関連付けられたスコアを取得しようとしていますが、最新のエントリのみを取得したいです。スコアがまったくない場合は、セルを空白にしたいです。
| 企業 | スコア | 日付 |
|---|---|---|
| あ | 3.5 | 2020年2月1日 |
| あ | 4.3 | 2019年1月16日 |
この場合、会社 A のスコアが必要な場合は、3.5 を返すようにします。会社 D を検索する場合は、セルを空白のままにしておきます。
ご協力いただければ幸いです。よろしくお願いいたします!!
答え1
動的配列数式 SORTBY と FILTER がある場合:
=@SORTBY(FILTER(B:B,A:A=E1,""),FILTER(C:C,A:A=E1,""),-1)
答え2
使用XLOOKUP()：
=XLOOKUP(F2, SORTBY(A2:A41, C2:C41), SORT(C2:C41), "", 0, -1)
SORTBY()数式内の検索配列 A2:A41 を、値を取得する列である列 C で並べ替えます。XLOOKUP()上から下へ検索した場合に検索値の最後のインスタンスがランダムなエントリではなく、必要なインスタンスになるように並べ替えます。
これを実現するには、戻り値の列 (この場合は列 C (数式では C2:C41)) を昇順で並べ替えます。つまり、値の最小値が最初になり、最大値が最後になります。その列には日付があり、Excel は日付を数値として扱うため、問題は発生しません。
したがって、この時点で、Excel は、作成された仮想データ範囲の最後にある、参照範囲値の各セットの最高値の日付、つまり最新の日付を持つデータ範囲を参照します。
XLOOKUP()次に、最後から最初 (下から上) に検索するように指示され、指定された検索値が見つかると、数式の仮想データ範囲内のそれらの値のセットの最後の値を見つけます。したがって、対応するレコードが仮想結果列にも返されます。このSORT()ときも並べ替えが行われ、これ以上の並べ替えは不要になったため、また 2 つの並べ替えが同じ「並べ替え基準」列によって行われたため、列 C のレコードは列 A の元のレコードと一致します。つまり、2 つの列のペアの行は、ごちゃ混ぜにならずに一緒に保持されます。
そこで、ルックアップ配列と結果配列の仮想データ範囲を作成し、その中でペアが一致したままになるようにしました。2 つの配列を並べ替えて、各ルックアップ値が類似の値でグループ化されるようにしました。結果配列による並べ替えにより、最後の配列が目的の配列になることが保証されます。また、XLOOKUP()最後から最初まで検索できるため、見つけることができます。
実際には、同一の参照値の各セットに対して、降順で並べ替え、日付が最も高いものから最も低いものへと並べ替えることができます。その後、XLOOKUP()何らかの理由があれば、 を使って最初から最後まで検索することができます。