☰

インデックス/積和計算エラー

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
インデックス/積和計算エラー

私は 24 時間近く Excel の問題に取り組んできました。ほぼ希望どおりの結果を出す数式があるのですが、返されるはずの結果が返ってきません。

=INDEX(Data!D:D,SUMPRODUCT((Data!C:C=Total!A5)*(Data!A:A="Total")*(Data!B:B="Total")*(ROW(Data!D:D))))

Data! ワークシートの列 D のインデックスの行は、一致が発生する実際の行であると想像しますが、最初の値を除くすべての値については、返される値はその 3 行下から取得されます。

はい、その数式を適用した最初のセルでは、Data! の列 D から正しい値が返されます。ただし、数式が適用された後続のセルでは、一致した値の 3 行下にある Data! の列 D の値が返されます。

数式から 3 を引く (適切な行を返す) あらゆる組み合わせを試しましたが、効果はありませんでした。それでも、数式が適用された最初のセルが期待どおりの値を返す理由を説明できません。

もちろん、どんな助けでも大歓迎です...

答え1

あなたが探しているものが第一、第二などTotal!A5 に一致するものを検索するか、最初に A5 に一致するものを検索してから、次に A6 に一致するものを検索するなど、さまざまな場合に対応します。この提案されたソリューションは前者向けです。

私は、一致した複数の結果を取得するために数学的排除法を使用することを好みます。一致しないものを排除すると、残るのは一致する結果のセットになります。また、私はせっかちなので、完全な列参照の計算の遅れがSUMPRODUCT煩わしいと感じたので、範囲参照を最初の約 1K 行に切り詰めました。

=IFERROR(INDEX(Data!$D$2:$D$999, SMALL(INDEX(ROW($1:$998)+((Data!$C$2:$C$999<>Total!$A$5)+(Data!$A$2:$A$999<>"Total")+(Data!$B$2:$B$999<>"Total"))*1E+99,,),ROW(1:1))),"")

これは標準です（非配列ROW(1:998))式。一致するすべての戻り値をキャッチするために、必要に応じて記入してください。これを自分の目的のために書き写すときは、内の位置 Data!D2:D999は、ワークシート上の実際の行番号ではありません。 は、ROW(1:1)単に下方向に入力するにつれて増加するカウンターです。 の方がCOUNTIF適切な場合があり、複数の参照値で一致する複数の戻り値を取得するために使用できます。

関連情報