エクセルで同じ値を探す関数【簡単】

エクセルのCOUNTIF関数で、範囲内に含まれる同じ値を簡単に見つける方法をご紹介します。検索値と同じ値を抽出する方法についても解説しているので、ぜひ参考にしてみてください。

反対に一意の値のみ取得する方法については、重複を削除する方法で解説しています。

同じ値を探す方法

COUNTIF関数で同じ値の個数を取得する方法、条件付き書式で同じ値に自動で色が付くようにする方法、検索と置換で同じ値に簡易的に色付けする方法、の３つをご紹介します。

COUNTIF関数で探す

COUNTIF関数は指定した条件範囲から、検索条件と一致するセルの個数を返します。

同じ値を探すポイントは、条件範囲を[F4]キーで固定することです。

例えば COUNTFI($B$2:$B$8,B2) をオートフィルでコピーすると検索条件のみ１行ずつ移動するので、それぞれの値が範囲内に存在する個数を取得できます。

IF関数と組み合わせてると、同じ値を含むセルに「重複」と表示させることも可能です。

例えば IF(COUNTFI($B$2:$B$8,B2)>1,”重複”,””) は同じ値の個数が１より大きいなら「重複」の文字列を、そうでないなら空白を出力します。

重複値のみを表示させる場合、データタブの[フィルター]を適用させます。

個数で絞り込む場合は[数値フィルター]から[指定の値以上]を選択し、抽出条件に 2 を指定してください。文字列で絞り込む場合は、[重複]にチェックを入れて[OK]を押します。

条件付き書式で探す

まず同じ値を探すデータ範囲を選択し、ホームタブの[条件付き書式]を開きます。

次に[セルの強調表示ルール]から、[重複する値]をクリックします。

次の値を含むセルを[重複]に指定し、任意の書式を設定します。最後に[OK]ボタンをクリックすると、同じ値を含むセルに色が付きました

同じ値を含むセルの行全体に色付けする場合、色付けする全ての範囲を選択してください。

ホームタブの[条件付き書式]から、[新しいルール]をクリックします。

ルールの種類から、[数式を使用して、書式設定するセルを決定]を選択します。

入力する数式は COUNTIF($B$2:$B$8,$B2)>1 です。COUNTIF関数の条件範囲は行・列ともに固定し、検索条件は列のみ固定してください。

任意の書式を設定して[OK]ボタンを押すと、同じ値の個数が１より大きい行に色が付きました。

検索と置換機能で探す

ホームタブの[検索と選択]から、[検索]を開きます。

検索する文字列を入力して[すべて検索]をクリックすると、同じ値の個数が表示されました。[次を検索]をクリックすると、２つめの文字列のセルに移動します。

検索と置換の[置換]タブに移動し、[オプション]を開きます。

置換後の文字列には検索文字列と同じ値を入力し、置換後の書式のみ設定して[すべて置換]をクリックします。シート内の同じ値に、一括で色付けできました。

検索場所を[ブック]に変更すると、ブック内のすべての同じ値を検索可能です。

同じ値を抽出する関数

XLOOKUP関数は検索範囲から検索値を検索し、一致した値を結果の範囲から返します。

例えば XLOOKUP(E2,B2:B8,C2:C8) なら、商品名から”にんじん”と同じ値を検索します。同じ値が見つかったら結果の範囲から値を返すので、返り値は 350 です。

 ※Excel 2019以前のバージョンの場合、VLOOKUP関数で代用してください。

FILTER関数は条件に従ってフィルタ処理を行い、値を結果の範囲から返します。

例えば FILTER(B2:C8,A2:A8=E2) なら分類を”果物”でフィルタ処理するので、分類が同じ値をすべて抽出可能です。

※Excel 2019以前のバージョンの場合、FILTER関数の代用方法 をご参照ください。