この記事では、UNIQUE関数を使わずに重複しない一意の値を取り出す方法についてご説明します。
【前提】UNIQUE関数はExcel2019やExcel2013などでは使えない
UNIQUE関数はOffice365(のExcel)から使えるようになった関数であり、現在Office365(のExcel)とExcel2021でしか使うことができません。
【解決方法】INDEX関数、MATCH関数、COUNTIF関数を組み合わせて使えば実現可能
Excel2019やExcel2016といったExcel2019以前のバージョンのExcelで重複しない一意の値を取り出すのにどうすればよいのかというと、INDEX関数、MATCH関数、COUNTIF関数を組み合わせて使うことで実現することができます。
INDEX関数、MATCH関数、COUNTIF関数を組み合わせて使う方法は、これからご説明していきます。
Excelファイルの例
今回は次のExcelファイルを用意しました。
以上のExcelファイルは都道府県の一覧が左の表に入っているExcelファイルです。
この都道府県の一覧にはいくつかの県が重複して存在しており、以下の県が重複しています。
・東京都
・茨城県
・神奈川県
この重複した県が含まれる一覧から、ユニークで抽出した県を右の表に出力させます。
ユニークで抽出した県を右の表に出力させた結果は下の画像の通りになります。
Excelファイルの作成の流れ
今回例で挙げたExcelファイルで言うと、左の表の1つ目の都道府県名のセルは「B2」、右の表の1つ目のセルは「E2」になります。
今回例で挙げたExcelファイルで言うと、セル「E3」をセル「E11」までコピーします。
Excelファイル作成の解説
【STEP.1】左の表の1つ目の都道府県名の値を、右の表の1つ目のセルから参照させる
左の表の1つ目の都道府県名の値を、右の表の1つ目のセルから参照させます。
埋め込んだ計算式は次の通りです。
セル「E2」に、左の表の1つ目の都道府県名「東京」が表示されました。
この値をベースに、次のSTEP.2ではE列のセルにB列のセルの値をユニークで取得していきます。
【STEP.2】左の表の都道府県名をユニークで取得する計算式をセル「E3」に埋め込む
左の表の都道府県名をユニークで取得する計算式をセル「E3」に埋め込みます。
埋め込んだ計算式は次の通りです。
- =IFERROR(INDEX(B$2:B$11,MATCH(0,COUNTIF(E$2:E2,B$2:B$11),0)),””)
- セルB2からセルB11の中からセルE2の値を検索します。
もし存在していればセルE2と同じ値以外のセルをB2からB11のセルを上から下まで探していきます。
見つかったらそのセル(複数存在している場合、位置は複数の中の一番下にあるセル)の値を出力します。
ただし、もし存在していなければ空白を出力します。
- 検索文字列(セルE2の値)以外の値のセルを、セルB2からセルB11の範囲を対象に上から下に探して見つかったセルの値
※複数存在している場合、複数の中の一番下にあるセルの値
①COUNTIF関数
COUNTIF関数は、検索する値が検索先にいくつ存在するのかを数えて、その数を返す関数です。
今回はセルE2の値が、セルB2からセルB11の中にいくつあるのかを数えてその数を返します。
COUNTIF関数の1つ目の引数1つ目の引数には、検索する値のセルを指定します。
セルE2の値がいくつあるのかを数えるために、引数にセルE2を指定します。
COUNTIF関数の2つ目の引数2つ目の引数には、検索範囲のセルを指定します。
今回は、セルE2の値をセルB2からセルB11の範囲で検索するので、B$2:B$11と指定しています。(指定は絶対参照で)
②MATCH関数
MATCH関数は、検索文字列を、検索したい範囲のセルの中から検索し、何行目にあるのかを検索する関数です。
今回は、COUNTIF関数が対象とするセルの範囲を対象に、COUNTIF関数が0を返すセルが何行目にあるのかを探して、特定後にその行数を返します。
検索文字列に対してCOUNTIFが0を返す行位置が特定できれば、検索文字列でない文字列が入ったセルの行位置、つまり検索文字列以外の値が入ったセルが特定できるので、ユニークな値を取得することができます。
MACTH関数の1つ目の引数MACTH関数の1つ目の引数には検索文字列を指定します。
今回は1つ目の引数に0を指定しています。
なぜ0を指定しているのかというと、セルB2からB11のセルの中からE2以外の値を探すためです。
値が0の場合は、E2の値以外の値であることを指します。
セルB2からB11のセルの中からE2以外の値が見つかったらその値をE列のセルに出力します。
MACTH関数の2つ目の引数MACTH関数の2つ目の引数には、COUNTIF関数の戻り値を指定します。
COUNTIF関数の戻り値はCOUNTIF関数が検索した値がいくつあるのかを数えた数ですが、その戻り値が0である場合は第1引数で指定した0と値が一致するので、もしCOUNTIF関数の戻り値が0の場合はその0を返すセルの位置を返します。
例えば(B2からB4までのセルの値全てがE2と一致していて)セルB5の値がセルE2の値と一致していなければ、「茨城県」を返します。(セルB2から数えて下に4行目の意)
MACTH関数の3つ目の引数には照合する種類を指定します。
この照合する種類には3つあります。
今回は完全に一致する値を検索したいので0を指定しています。
③INDEX関数
Index関数は、行と列を指定したセルの値を取得します。
また、Match関数で取得した行位置が空白の場合は、そのまま空白を返します。
今回は、行はMatch関数で取得した行位置を指定しています。
ただし、今回はIndex関数に列を指定していません。
なぜなら今回は(複数の列ではなく)A列の1列だけを対象にしているからです。
1列だけを対象にする場合は列番号の指定は省略することができます。(第3引数は無し)
INDEX関数の1つ目の引数INDEX関数の1つ目の引数には、セルB2からセルB11を指定します。(範囲の指定)
INDEX関数の2つ目の引数INDEX関数の2つ目の引数には、Match関数で取得した行位置を指定します。
④IFERROR関数
IFERROR関数は、計算式がエラーを返す場合はそのセルに何かしらの値をセルに表示させる関数です。
今回は計算式がエラーを返す場合は空白を表示させるよう「””」をIFERROR関数の2つ目の引数に指定しています。
エラーではない場合は1つ目の引数の値をセルに出力します。
IFERROR関数の1つ目の引数IFERROR関数の1つ目の引数にはエラーが出るか判定させる値を指定します。
今回はIndex関数のエラー判定を行うため1つ目の引数に指定しています。
IFERROR関数の2つ目の引数IFERROR関数の2つ目の引数には、1つ目のセルの値がエラーの場合にそのままエラーは表示させずに、その代わりに表示させる値を指定します。
今回は、1つ目のセルの値がエラーの場合は空白「””」を表示させるよう、2つ目の引数に空白「””」を指定しています。
【STEP.3】STEP.2の計算式が埋め込まれたセルを、表の最後の行位置までコピーする
STEP.2の計算式が埋め込まれたセルを、表の最後の行位置までコピーします。
STEP.2の計算式が埋め込まれたセルを、表の最後の行位置までコピーすることで、セルB2からセルB11までの値から重複しないユニークな値(都道府県名)を抽出することができました。
最後に
本記事では、UNIQUE関数を使わずに重複しない一意の値を取り出す方法についてご説明しました。
Index関数とMatch関数、COUNTIF関数を組み合わせて使うことで、値の中から重複しない一意の値を取り出すことができます。
今回紹介したIndex関数とMatch関数、COUNTIF関数を組み合わせた計算式は複雑に思えるかもしれませんが、UNIQUE関数が使えないバージョンのExcelを使用している場合は参考にしてみてくださいね。
Excelのスキル向上やExcelの基礎知識をしっかりと学びたいなら
Excelのスキルを習得したい、Excelの基礎知識をもっと理解したい、そう考えているなら「無期限サポート付きExcel講座【すごい改善】」がおすすめです。
Excelのスキルの基礎を身につけるなら【すごい改善】で無期限サポート付きがあるので、これで「Excelのスキルや基礎」を学ぶのにおすすめですよ。
→ 受講後、何度でも無期限でメールで質問できるアフターサポートがついているExcelマスター講座はこちら
