別ファイルにある値の件数を「重複は除外しカウント」する

「重複を除いてカウント」にするにはSUMPRODUCT関数＋COUNTIF関数でできる、と前のページで説明しました。

しかし、参照元の値が別のブック（ファイル）にある場合にはこの方法はエラーになってしまいます。

そこでここでは、別ブック上にあるデータを「重複を除いてカウントする方法」を説明します。

【目次】

本記事はアフィリエイト広告を含みます

１．まずはやりたいことを確認

始めに、やりたいことを例を含めて確認します。

やりたいことは、「別ファイルの値を重複を除いてカウントする」です。

前のページでは「同じファイルにある値を重複を除いてカウントする数式」を紹介しましたが、別ファイルで同じことをしようとするとエラーになってしまうことがあります。

そこでここでは、エラーにならない数式を紹介します。

ひとつ例を見てみましょう。

上の表は、Book1にある品名の一覧です。「レモン」、「りんご」、「ねぎ」という品名が３種類、全部で7つ表示されています。

上はBook2のシートです。つまり、品名の載ったシートとこのシートは、ファイルが別ということです。

そして、このBook2のセルA1に「Book1の一覧に何種類の品名があるか？」を表示させたいとします。

この時、前のページでIF関数の含む条件は次の数式でできると説明しました。

=SUMPRODUCT(1/COUNTIF(「範囲」,「範囲」))

しかし結果は「#VALUE!エラー」になってしまいました。

このような状態が「他のファイル（例ではBook1）のデータ（例ではセルB2からB8）を重複を除いてカウントする」です。

では、他のファイルにある値にIF関数で含む条件にできる数式を紹介し、作り方を解説します。

他ブック上にデータがある時、COUNTIF関数では#VALUE!エラーになってしまいます。原因は「COUNTIF関数が閉じたファイルの中身を見られないから」です。

ですから、この場合には別の関数を使えばいいのです。

使うのは「SUM関数とROUNDDOWN関数、MATCH関数とROW関数の組み合わせ」です。

具体的に、数式の形は次のようになります。

{=SUM(ROUNDDOWN((MATCH(① カウントしたい範囲&"",① カウントしたい範囲&"",)/ROW(② ①と同じ1行目からの行範囲)),0))}

では、先ほどの例の条件で数式を作ってみます。条件は「Book1にある品名の一覧から重複を除いてカウントする」です。

（１）結果を表示させたいセルをクリックし、「=SUM(ROUNDDOWN((MATCH(」と入力後、fxボタンを押す

（２）MATCH関数の引数検索値にカーソルをあて、カウントしたいBookの範囲をなぞって選択、その後「&""」を入力する

（３）MATCH関数の引数範囲に検査値の値をコピーして貼り付ける

（４）数式バーに「,)/ROW(」と入力する

（５）結果を表示させたいシートのセルA１から、カウントしたい範囲（①）の行数と同じになる範囲までを入力後、「)),0))」と入力する

※ ①が２～８行目の範囲なら７行なのでA1:A7となる

（６）Shift＋Ctrl＋Enterキーを押せば完了

例では、数式は次のようになります。

{=SUM(ROUNDDOWN((MATCH([Book1.xlsx]Sheet1!$B$2:$B$8&"",[Book1.xlsx]Sheet1!$B$2:$B$8&"",)/ROW(A1:A7)),0))}

このように「SUM関数とROUNDDOWN関数、MATCH関数とROW関数の組み合わせ」を使えば、重複を除いてカウントすることができます。

以上、参考になれば幸いです。

【口コミ・評判のいいExcel本の紹介】

リンク