以下の要件についてご協力いただけないでしょうか。
列 A と列 B に範囲を保持しています。各範囲の関連値は列 C に記載されています。たとえば、以下を参照してください。
| Column A | Column B | Column C |
| 1 | 10 | A |
| 15 | 20 | B |
| 21 | 30 | C |
ここで、たとえば 17 などの数字を挿入した別の列 E があります。ここで、この数字がどの範囲に該当するかを確認して、列 C から関連する値を取り込むことができるようにしたいと思います。この例では、17 は行 2 にある 15 から 20 の範囲に該当し、列 C の値は B です。したがって、17 という数字の場合、出力は B になります。25 を入力した場合、出力は C になります。ただし、12 を入力した場合、この数字はどの範囲にも該当しないため、出力は #N/A になります。
このタイプの出力を取得するために使用できる Excel の数式があるかどうか教えてください。コーディングよりも数式を使用することを好みます。この点についてご指導いただきありがとうございます。
よろしくお願いいたします、スリダル
答え1
はい、可能です。ただし、機能させるにはテーブルに追加の行が必要です。
私はその行を追加しました
| 0 | 0 | #N/A |
をテーブルの最初の行として追加したので、テーブルはA1:C4。
関数を使用してINDEX、返す必要のある行を決定します。
行を計算するには、SUMPRODUCTINDEX が使用する行を計算します。
完全な数式は次のようになります。
=INDEX(C1:C4,SUMPRODUCT(--(A1:A4<=D1),--(B1:B4>=D1),ROW(C1:C4)))
チェックインする値D1
式を実行するとD1含む17:
=INDEX(C1:C4,SUMPRODUCT(--(A1:A4<=17),--(B1:B4>=17),ROW(C1:C4)))
=INDEX(C1:C4,SUMPRODUCT(--({0,1,15,21}<=17),--({0,10,20,30}>=17),{1,2,3,4}))
=INDEX(C1:C4,SUMPRODUCT(--({True,True,True,False}),--({False,False,True,True}),{1,2,3,4}))
=INDEX(C1:C4,SUMPRODUCT({1,1,1,0}),({0,0,1,1}),{1,2,3,4}))
=INDEX(C1:C4,(1*0*1 + 1*0*2 + 1*1*3 + 0*1*4))
=INDEX(C1:C4,3)
="B"
数値が範囲外の場合、SUMPRODUCTには値の行が等しくない部分はないので0、範囲内の最初の値、つまり#N/Aが返されます。同じ数式ですが、範囲内12にあります。D1:
=INDEX(C1:C4,SUMPRODUCT(--(A1:A4<=12),--(B1:B4>=12),ROW(C1:C4)))
=INDEX(C1:C4,SUMPRODUCT(--({0,1,15,21}<=12),--({0,10,20,30}>=12),{1,2,3,4}))
=INDEX(C1:C4,SUMPRODUCT(--({True,True,False,False}),--({False,False,True,True}),{1,2,3,4}))
=INDEX(C1:C4,SUMPRODUCT({1,1,0,0}),({0,0,1,1}),{1,2,3,4}))
=INDEX(C1:C4,(1*0*1 + 1*0*2 + 0*1*3 + 0*1*4))
=INDEX(C1:C4,0)
="#N/A"
技術的には#該当なし、これはすべての可能な答えの配列ですが、数式を配列数式として入力していないため、最初の値のみが返されます。