Excel で2 つの列間のすべての一致／重複をカウントする

2 つのデータ列を比較し、両方に含まれるすべての一致または重複をカウントするのは、多くのユーザーにとって日常的な作業です。たとえば、2 つの名前のリストがあり、一部の名前が第1 列と第2 列の両方に存在しているとします。このような場合、下記のスクリーンショットのように、2 つの列のどこにあっても一致するすべての名前をカウントしたいときがあります。本チュートリアルでは、その目的をExcel で実現するための数式をいくつかご紹介します。

• SUMPRODUCT 関数と COUNTIF 関数で2 つの列間のすべての一致をカウントする
• COUNT 関数と MATCH 関数で2 つの列間のすべての一致をカウントする
• SUMPRODUCT 関数、ISNUMBER 関数、MATCH 関数で2 つの列間のすべての一致をカウントする

SUMPRODUCT 関数と COUNTIF 関数で2 つの列間のすべての一致をカウントする

2 つの列間のすべての一致をカウントするには、SUMPRODUCT 関数と COUNTIF 関数を組み合わせるのが便利です。一般的な構文は次のとおりです：

• range1, range2この2 つの範囲には、すべての一致をカウントしたいデータが含まれています。

以下の数式を空白セルに入力またはコピーし、Enterキーを押して結果を取得してください：

数式の説明：

=SUMPRODUCT(COUNTIF(A2:A12,C2:C12))

• COUNTIF(A2:A12,C2:C12)：この COUNTIF 関数は、列 C の各名前が列 A に含まれているかをチェックします。含まれている場合は「1」、含まれていない場合は「0」を返します。具体的には、次のような結果になります：{1;1;0;0;0;1;0;0;1;0;1}。
• SUMPRODUCT(COUNTIF(A2:A12,C2:C12))=SUMPRODUCT({1;1;0;0;0;1;0;0;1;0;1})：SUMPRODUCT 関数はこの配列内のすべての要素を合計し、結果として5 を得ます。

COUNT 関数と MATCH 関数で2 つの列間のすべての一致をカウントする

COUNT 関数と MATCH 関数を組み合わせれば、2 つの列間で一致する値の個数を簡単に取得できます。一般的な構文は次のとおりです：

• range1, range2この2 つの範囲には、すべての一致をカウントしたいデータが含まれています。

次の数式を空白セルに入力またはコピーし、正しい結果を得るためにCtrl + Shift + Enterキーを同時に押してください（スクリーンショット参照）：

数式の説明：

=COUNT(MATCH(A2:A12,C2:C12,0))

• MATCH(A2:A12,C2:C12,0)：この MATCH 関数は、列 A の各名前を列 C 内から検索し、一致する値の位置を返します。見つからない場合はエラー値が表示されるため、次のような配列が得られます：{11;2；#N/A；#N/A；#N/A;6;1；#N/A；#N/A；#N/A;9}。
• COUNT(MATCH(A2:A12,C2:C12,0))= COUNT({11;2;#N/A;#N/A;#N/A;6;1;#N/A;#N/A;#N/A;9})：COUNT 関数はこの配列内の数値をカウントし、結果は5 になります。

SUMPRODUCT 関数、ISNUMBER 関数、MATCH 関数で2 つの列間のすべての一致をカウントする

Excel では、SUMPRODUCT 関数、ISNUMBER 関数、MATCH 関数を組み合わせて、2 つの列に含まれる一致する値を検索し、その件数をカウントすることも可能です。一般的な構文は次のとおりです：

• range1, range2この2 つの範囲には、すべての一致をカウントしたいデータが含まれています。

以下の数式を空白セルに入力またはコピーして結果を出力し、計算結果を返すためにEnterキーを押してください（スクリーンショット参照）：

数式の説明：

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A12,C2:C12,0))))

• MATCH(A2:A12,C2:C12,0)：この MATCH 関数は、列 A の各名前を列 C 内から検索し、一致する値の位置を返します。見つからない場合はエラー値が表示されるため、次のような配列が得られます：{11;2；#N/A；#N/A；#N/A;6;1；#N/A；#N/A；#N/A;9}。
• ISNUMBER(MATCH(A2:A12,C2:C12,0))= ISNUMBER({11;2;#N/A;#N/A;#N/A;6;1;#N/A;#N/A;#N/A;9})：ここでは、ISNUMBER 関数が配列内の数値を TRUE に、それ以外の値（例：#N/A）を FALSE に変換します。その結果、次のような配列が得られます：{TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE}。
• --(ISNUMBER(MATCH(A2:A12,C2:C12,0)))=--({TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE})：この二重マイナス記号（--）は、TRUE を1 に、FALSE を0 に変換し、次のような結果を返します：{1;1;0;0;0;1;1;0;0;0;1}。
• SUMPRODUCT(--(ISNUMBER(MATCH(A2:A12,C2:C12,0))))=SUMPRODUCT({1;1;0;0;0;1;1;0;0;0;1})：最後に、SUMPRODUCT 関数がこの配列内のすべての要素を合計し、結果として5 が得られます。

関連する関数：

関連記事：