関数の内容が不明

質問

添付のExcelシートのC6にB６とマッチする値をD2:I2から検索しその右にある日付を入力したいのですが下記の計算式で何とかいけたのですが別シートで同じような値検索があるのでぜひ
計算式の詳細内容説明を解る方宜しくお願いします。

=OFFSET(A2,SUMPRODUCT(ROW($D$2:$I$2)*($D$2:$I$2=B6))-2,SUMPRODUCT(COLUMN($D$2:$I$2)*($D$2:$I$2=B6)))

回答No.1

bunjii · ベストアンサー

>計算式の詳細内容説明を解る方宜しくお願いします。
OFFSET関数は次のような指定をします。
=OFFSET(参照セル,参照セルからの行の隔たり,参照セルからの列の隔たり)
提示の数式では次のように計算しています。
参照セル=A2
行の隔たり=SUMPRODUCT(ROW($D$2:$I$2)*($D$2:$I$2=B6))-2 → 0
参照のセル範囲が2行目のみなので無意味です。
直接 0 と指定して良いでしょう。
列の隔たり=SUMPRODUCT(COLUMN($D$2:$I$2)*($D$2:$I$2=B6)) → 6
D2からI2のセルにB6と同じであるセルの位置を計算しています。
SUMPRODUCT関数は指定した配列値の合計を計算しています。
COLUMN関数は列記号（A、B、C等）を列番号（1、2、3等）に変換します。
従って、次のような計算を行っています。
=SUMPRODUCT(COLUMN($D$2:$I$2)*($D$2:$I$2=B6))
　　　　↓
=SUMPRODUCT({4,5,6,7,8}*{0,0,1,0,0}) → 6

ご提示の数式を簡略化すると次のようになります。
=OFFSET(A2,0,SUMPRODUCT(COLUMN($D$2:$I$2)*($D$2:$I$2=B6)))
SUMPRODUCT関数の代わりにMATCH関数で簡素化すると次のようになります。
=OFFSET(A2,0,MATCH(B6,$2:$2,0))
MATCH関数は次のような指定をします。
=MATCH(検査値,検査範囲,照合の種類）
MATCH関数は1行または1列の検査範囲から検査値の位置を探しますので、2次元以上の配列からの検索はできません。

お礼

有難うございます。説明と簡素化の式までご教示頂き非常に助かりました。