それぞれが数値に関連付けられているテキスト文字列のコレクションがあります。「条件を満たす」エントリの関連付けられた数値を合計する必要があります。テキスト エントリは、指定されたターゲット文字列を 1 つ以上含むことで条件を満たします。エントリには、ターゲット文字列が複数含まれている場合や、ターゲット文字列が複数回含まれている場合があります。ただし、エントリがターゲットまたはターゲットの組み合わせのいずれかに一致するものを含むことで条件を満たしている場合、エントリの関連付けられた値を 1 回だけ合計します。
たとえば、セル A1:A3 にそれぞれapple、、、bananaが含まれpear、B1:B3 にそれぞれ数値が含まれているとします1。検索対象は、aおよびですp。3 つのテキスト エントリは、それぞれに少なくとも 1 つの対象のインスタンスが少なくとも 1 つ含まれているため、条件を満たします。列 B の関連する値を合計すると、結果が返されます3。
私は SUMIF とワイルドカード ターゲットを使用してこれを試しました。この例の式は次のとおりです。
=SUM(SUMIF(A:A, {"*a*", "*p*"}, B:B))
ただし、これにより、複数のターゲットに一致するエントリが二重にカウントされます。 この場合、3 つすべてに が含まれa、2 つにも が含まれるpため、合計は になります5。
エントリを二重カウントせずにこれを達成するにはどうすればよいですか?
答え1
数式内にハードコードするのではなく、ワークシート内の実際のセルに条件を設定すると、より柔軟になります。
使用する場合垂直、連続したセル範囲（例：H1:H2）をこの目的に使用し、範囲は垂直範囲では、これを使用できます配列数式**:
=SUM(IF(MMULT(0+ISNUMBER(SEARCH(TRANSPOSE(Criteria),Range)),ROW(Criteria)^0)>0,Values))
基準を数式内に含めたい場合は、次のようになります。
=SUM(IF(MMULT(0+ISNUMBER(SEARCH({"p","a"},Range)),{1;1})>0,Values))
よろしく
答え2
比較的簡単な解決法をご紹介します。関連付けられている値がすべて の場合1、 という望ましい結果が生成されます3が、正しい値が選択されたことを示すために異なる値を割り当て、一致しないエントリも念のため含めました。
エントリのリストは列 C にあり、それに関連付けられた値は列 D にあります。結果は E1 にあります。
ワイルドカードを使用できるのは特定の関数のみなので、ターゲット文字列には SEARCH を使用します。
複数の OR 条件を処理する一般的な方法は、各テストの結果を加算することです。ただし、項目が複数の条件を満たす場合、二重カウントが発生します。これを解決するには、集約された条件テストの合計が 0 より大きいかどうかを確認し、その合計を関連する値と共に使用します。
ターゲット検索文字列を配列として扱うのは複雑になります。なぜなら、これらの種類の数式に使用される一般的な関数は、次の用語に適用する前に配列全体の結果を計算するからです。そのため、各条件を個別に扱いました。条件をさらに追加するには、テストのISNUMBER(SEARCH("target",range))前に括弧内に条件ごとに別の条件を追加するだけです>0。
SUMPRODUCT は、通常の非配列数式を使用して配列スタイルの計算を実行します。
E1 の式は次のとおりです。
=SUMPRODUCT(((ISNUMBER(SEARCH("p",C1:C4))+ISNUMBER(SEARCH("a",C1:C4)))>0)*D1:D4)
答え3
これは本当に公式になると思っていたのですSUMPRODUCT(--が、うまくいきませんでした。でも、これはうまくいくはずです -
=SUM(IF(FREQUENCY(IF(NOT(ISERR(SEARCH({"d","g"},A2:A10))),ROW(A2:A10)),ROW(A2:A10))>0,1))
は{"d","g"}検索文字列の配列になります。
これは配列数式なので、入力したらctrl+ shft+を押す必要がありますentr。すると、関数全体を囲む数式バーに中括弧が表示されます。
単一の列を検索する場合にのみ機能することに注意してください。
ワイルドカードを考慮すると、これはうまく機能しない可能性があります。必要なのは正規表現でしょうか?