別の列に特定のテキストが含まれている場合の合計

このチュートリアルでは、別の列に特定または部分的なテキストが含まれている場合に値を合計する方法を紹介します。以下のデータ範囲を例として、「T-Shirt」というテキストを含む製品の総数量を取得するために、SUMIF関数とSUMPRODUCT関数の両方がこのタスクをExcelで処理できます。

• SUMIF関数を使用して特定または部分的なテキストが含まれる場合の値を合計する
• SUMPRODUCT関数を使用して特定または部分的なテキストが含まれる場合の値を合計する

SUMIF関数を使用して特定または部分的なテキストが含まれる場合の値を合計する

別の列に特定のテキストが含まれている場合に値を合計するには、ワイルドカード（*）を使用したSUMIF関数を使用できます。一般的な構文は次のとおりです：

ハードコードされたテキストを使用した一般的な数式：

• 範囲: 基準を使用して評価したいデータ範囲；
• *テキスト*: 合計する基準。ここで、* ワイルドカードは任意の数の文字を見つけるために使用されます。特定のテキストを含むすべての項目に一致させるには、そのテキストを二つの * 文字で囲みます。（注意：テキストとワイルドカードをダブルクォーテーションで囲む必要があります。）
• 合計範囲: 合計したい、一致する数値を持つセルの範囲。

セル参照を使用した一般的な数式：

• 範囲: 基準を使用して評価したいデータ範囲；
• "*"&セル&"*"：合計する基準；
  • *：任意の数の文字を見つけるためのワイルドカード。
  • セル：検索したい特定のテキストを含むセル。
  • &：この連結演算子 (&) は、セル参照をアスタリスクと結合するために使用されます。
• 合計範囲: 合計したい、一致する数値を持つセルの範囲。

関数の基本操作を理解したら、必要な以下の数式のいずれかを使用し、Enterキーを押して結果を得てください：

注: このSUMIF関数は大文字小文字を区別しません。

SUMPRODUCT関数を使用して特定または部分的なテキストが含まれる場合の値を合計する

SUMPRODUCT関数もまた、ワイルドカードを使用せずにこの作業を完了するのに役立ちます。ここでは、ISNUMBERおよびSEARCH関数をSUMPRODUCT関数内にラップする必要があります。一般的な構文は次のとおりです：

• 合計範囲: 合計したい、一致する数値を持つセルの範囲；
• 基準: 合計する基準。これはセル参照や定義済みの特定のテキストでも可能です；
• 範囲: 基準を使用して評価したいデータ範囲；

以下の数式のいずれかを空白のセルに入力し、 Enterキーを押して結果を返してください：

この数式の説明：

=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("Tシャツ",$A$2:$A$12))))

• SEARCH("Tシャツ",$A$2:$A$12): SEARCH関数は、データ範囲A2:A12から特定のテキスト「Tシャツ」の位置を返します。そのため、次のような配列が得られます：{5;#VALUE!;#VALUE!;7;#VALUE!;7;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7}。
• ISNUMBER(SEARCH("Tシャツ",$A$2:$A$12))= ISNUMBER({5;#VALUE!;#VALUE!;7;#VALUE!;7;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7}): このISNUMBER関数は数値をテストするために使用され、新しい配列を返します：{TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}。
• $B$2:$B$12*(ISNUMBER(SEARCH("Tシャツ",$A$2:$A$12)))= {347;428;398;430;228;379;412;461;316;420;449}*{TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}: これらの二つの配列を乗算すると、数学的操作によりTRUEおよびFALSEの値が1と0に変換されます。したがって、二つの配列の乗算結果は次のようになります：{347;428;398;430;228;379;412;461;316;420;449}*{1;0;0;1;0;1;0;0;0;0;1}={347;0;0;430;0;379;0;0;0;0;449}。
• SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("Tシャツ",$A$2:$A$12)))) =SUMPRODUCT({347;0;0;430;0;379;0;0;0;0;449}): 最後に、SUMPRODUCT関数は配列内のすべての値を合計して結果を得ます：1605。

関連する関数：

さらに記事：