Excel関数で重複を除いてデータの件数を求める方法
2017/12/20
カテゴリ：Tips
タグ：Excel
重複を除いたデータの件数、すなわちデータの種類を、Excel の関数を用いて求める方法を紹介します。
対応Excelバージョン
この手順は、Excel 2016・2019・2021、Microsoft365(Office 365)に対応しています。
概要
ここでは、以下のような動物の名前が入力されたセル範囲を対象に、動物が何種類含まれるかを調べます。
「データの種類を求める」という関数そのものはありませんが、複数の関数を組み合わせることでデータの種類を求めることができます。その方法として2つの方法を紹介します。
方法1
SUMPRODUCT関数とCOUNTIF関数を用いて求めます。
=SUMPRODUCT(1/COUNTIF(データ範囲,データ範囲))
方法2
SUM関数とCOUNTIF関数で配列数式※を用いて求めます。数式を入力して確定する際、［Enter］+［Ctrl］+［Shift］を同時に押すことで配列数式となります。※Excel2021以降は配列数式としなくても［Enter］を押すだけで同様の結果が表示されます。
{=SUM(1/COUNTIF(データ範囲,データ範囲))}
結果
いずれの方法を用いても12種類という結果が得られます。
解説
まず、COUNTIF関数によって求められる値を確認します。
=COUNTIF(データ範囲,検索値)
下図のように、COUNTIF関数では各セルの動物がデータ範囲内にいくつ存在するかが求められます。例えば、「イヌ」のセル（水色に塗ったセル）に着目すると、いずれも「イヌ」の数と同じ「4」が返ってきているのがわかります。
続いて、1/COUNTIFによって求められる値を確認してみると、下図のような結果が得られます。「イヌ」のセルではいずれも1/4=0.25となっているのがわかります。
=1/COUNTIF(データ範囲,検索値)
ここで、「イヌ」のセル4箇所の値を合計すると、0.25×4=1であり、「イヌ」として「1」という値を返しています。どの動物についても合計すると1になるので、1/COUNTIFで求めた値を合計すると動物の種類が求められます。
ここまでの過程を1行の数式で計算するとき、COUNTIF関数の検索値にもデータ範囲そのものを指定した上で、方法1ではSUMPRODUCT関数を、方法2ではSUM関数による配列数式を用いています。
ダウンロード
この統計Tipのテキストファイルのダウンロードはこちらから → tips_14.xlsx
このコンテンツは、Excel 2016を用いて作成しています。
最終更新日：2024年9月24日
