UNIQUE関数で重複しない値をリスト化して表内の該当セルを塗りつぶす

Excel2021とMicrosoft 365のExcelでは、UNIQUE（ユニーク）関数を使用することができます。

UNIQUE関数を使うと、指定したデータ範囲から重複を削除したデータを作成できます。

UNIQUE 関数（Microsoft）

入力規則のリストにUNIQUE関数のスピル範囲演算子を使って範囲を指定すると、データを追加しても自動的に重複しない値がドロップダウンリストに追加されます。

ここでは、作成したリストを入力規則のドロップダウンに指定して、条件付き書式で指定した値のセルを塗りつぶします。

また、該当セルを含む行全体を塗りつぶす方法も解説します。

テーブルの作成

以下のような表があります。

テーブルに変換します。

テーブルにしたほうが簡易なので、ここではテーブルにした場合の方法を紹介しますが、必ずしもテーブルにしなければならないというわけではありません。

テーブルに変換しない場合の方法も解説しています。

表内のセルをアクティブにして、［挿入］タブの［テーブル］グループにある［テーブル］をクリックします。

ショートカットキーは、［Ctrl］+［T］です。

［テーブルの作成］ダイアログボックスが表示されます。自動的に範囲が選択されますので、確認して問題がなければ［OK］ボタンをクリックします。

以下のようにテーブルに変換さされ、自動的にスタイルが設定されます。

スタイルをクリア

スタイルを設定したくない場合は、［テーブルデザイン］タブの［テーブルスタイル］グループにある［その他］ボタンをクリックして、

［テーブルのスタイル］から［なし］、または［クリア］を選択します。

以下のようなテーブルになります。

テーブル内のセルをアクティブにすると、［テーブルデザイン］タブが有効になります。

参考テーブルの主な機能については、以下の記事で解説しています。

UNIQUE関数で重複のないリストを作成

テーブルの［都道府県］列のリストをUNIQUE関数を使って作成します。

UNIQUE 関数（Microsoft）

=UNIQUE(配列,[列の比較],[回数指定])  [列の比較]と[回数指定]は省略可能です。

テーブルの場合とテーブルではない表の場合とで分けて解説します。

テーブルの場合

G列にリストを作成します。セル［G1］に［リスト］と入力して、セル［G2］をアクティブにします。

数式バーに半角で［=u］まで入力すると、以下のように関数の候補が表示されます。

この中に［UNIQUE］があれば使用できます。

関数候補の一覧から［UNIQUE］を選択して、キーボードから［Tab］キーを押します。

以下のように数式バーにUNIQUE関数が入力されます。

テーブルの都道府県の範囲を選択します。数式バーには、以下のように入力されます。

数式バーの［配列］は、範囲選択せずに直接入力してもかまいません。

テーブル名は、［テーブルデザイン］タブの［プロパティ］グループの［テーブル名］で確認できます。

［Enter］キーを押します。自動的に下のセルにも数式が表示されます。

重複しない都道府県のリストが作成できました。

テーブルは、自動拡張の機能があるので、テーブルに行を追加すると、リストも自動的に追加されます。

参考もし、テーブルが自動拡張されない場合は、設定を確認してください。

入力規則の参照リストをテーブルにしておくのも便利です。

テーブルではない表の場合

テーブルではない表（範囲）の場合は、リストを作成するのはテーブルと同じようにUNIQUE関数のみでOKです。

セル［G2］に以下のように入力します。テーブルの時と同じようにUNIQUE関数の配列を指定するだけです。

［Enter］キーを押すと、自動的に下のセルにも表示され、リストを作成できます。

データが追加されたら自動的にリストに反映されるように数式を修正

テーブルでなくても、関数を使ってデータが追加されたときに、自動的にリストに反映させるようにしておくと便利です。

OFFSET関数とCOUNTA関数を使用します。

数式バーを以下のように修正します。

新規データを追加すると、以下のように自動的にリストに反映されます。

・OFFSET（オフセット）関数

OFFSET 関数（Microsoft）

指定した参照から指定した行数、列数の範囲への参照を返します。

=OFFSET(参照,行数,列数,[高さ],[幅])  [高さ]と[幅]は省略可能です。

［参照］のセルから［行数］と［列数］分移動したセルを始点にして、［高さ］（行）と［幅］（列）のセル参照を返す関数です。

・COUNTA（カウントエー）関数

COUNTA 関数（Microsoft）

範囲内の空白でないセルの個数を返します。

OFFSET関数の［高さ］をCOUNTA関数で求めます。［都道府県］の列に空白があると、参照できなくなりますので気をつけてください。

セル［B1］を差し引いた［COUNTA(B:B)-1］で求められるということです。

数式バーに複数の関数を使用している場合に、関数の内容を確認したり、修正したい場合は、［関数の引数］ダイアログボックスを表示すると分かりやすいかもしれません。

例えば、OFFSET関数の内容を確認したい場合は、数式バーのOFFSET内にカーソルを置いて、［関数の挿入］ボタンをクリックします。

OFFSETの［関数の引数］ダイアログボックスが表示されます。

参考リストは、テーブルの場合も、テーブルではない場合も、別シートに作成してかまいません。

以下の記事でも、OFFSET関数を使った解説をしています。OFFSET関数は、とても便利な関数です。

入力規則のリストの値にスピル範囲演算子で範囲を指定

セル［E2］に入力規則でドロップダウンから、都道府県名を選択できるようにします。

セル［E2］を選択して、［データ］タブの［データツール］グループにある［データの入力規則］をクリックします。

［データの入力規則］ダイアログボックスが表示されますので、［入力値の種類］で［リスト］を選択します。

［元の値］には、以下のように入力します。

［#］は、［スピル範囲演算子］と呼ばれるものです。［=G2#］とすると、UNIQUE関数で求められた配列全体を指定できます。

これで、セル［E2］の入力規則は完成です。

リストの範囲が拡張されたら、自動的に入力規則のリストも拡張されます。

参考以下の記事内でも少し解説しています。UNIQUE関数を利用する場面は、いろいろあると思います。

また、SORT関数と組み合わせて使用すると、より便利になると思います。ただ、SORT関数では日本語の並べ替えは文字コード順になります。

条件付き書式で指定した値のセル（行単位）を塗りつぶす

セル［E2］で選択された文字列のセルを条件付き書式で塗りつぶします。

セル［B2］からセル［B19］までを範囲選択して、［ホーム］タブの［スタイル］グループにある［条件付き書式］をクリックします。

［セルの強調表示ルール］-［指定の値に等しい］をクリックします。

［指定の値に等しい］ダイアログボックスが表示されますので、以下のように入力します。セル［E2］をクリックすると、入力できます。

［OK] ボタンをクリックします。［書式］は、任意のものを選択してください。後で修正することもできます。

セル［E2］の値と等しいセルの値に塗りつぶしが設定されます。

該当セルの行を塗りつぶす

該当セルを含む行に対して塗りつぶしを設定したい場合は、条件付き書式の数式を修正します。

条件付き書式を設定する場合は、以下の3つの要件が必要になります。このことを踏まえておくと分かりやすいと思います。

［ホーム］タブの［スタイル］グループにある［条件付き書式］をクリックして、［ルールの管理］をクリックします。

［条件付き書式ルールの管理］ダイアログボックスが表示されます。作成した条件付き書式を選択して、［ルールの編集］をクリックします。

［書式ルールの表示］は、条件付き書式を設定した範囲内のセルをアクティブにして表示すると［このテーブル］か［現在の選択範囲］が選択されていると思いますが、そうでない場合は、［このワークシート］を選択すると条件付き書式が表示されます。

［書式ルールの編集］ダイアログボックスが表示されますので、［数式を使用して、書式設定するセルを決定］を選択します。

そして、テキストボックスに以下の数式を入力します。［OK］ボタンをクリックします。

［$］の入力は、直接入力してもいいですが、［F4］キーを使うと便利です。

［条件付き書式ルールの管理］ダイアログボックスに戻りますので、［適用先］の範囲を修正します。

［ダイアログボックス拡大/縮小］ボタンをクリックして、セル範囲を指定します。

セル［A2］からセル［C19］までを指定します。

ここでは、入力された範囲のみを指定していますが、データが追加されることを予想して［適用先］の行範囲は少し拡大して指定しておくほうがいいと思います。

［OK］ボタンをクリックします。

以下のように、該当セルを含む行範囲に塗りつぶしが設定されるようになります。

参考以下の記事では、条件付き書式でデータが追加されたら自動的に罫線が引かれるようにする方法を解説しています。

条件付き書式のオン/オフを切り替える方法も解説しています。印刷時に条件付き書式を非表示にしたい場合などに便利です。

SUMIF関数の引数にスピル範囲演算子を使用して自動集計

重複しないリストを作成したら、項目ごとの集計も簡単に表示できます。

セル［H2］に以下のように入力して、［Enter］キーを押します。範囲選択する必要はありません。

ピボットテーブルで求めていたものが、簡単に求められます。

SUMIF 関数（Microsoft）

=SUMIF(条件範囲,条件,[合計範囲])

テーブルであれば、以下のようにテーブル範囲を指定しても構いません。列全体を指定するより、いいと思います。

ピボットテーブルで作成してみました。当たり前ですが、同じ結果ですね。

参考SUMIF関数については、以下の記事で解説しています。

UNIQUE関数は、新しい関数です。Excel2019以前のバージョンで表示すると、動作しませんので気を付けてください。

以下は、Excel2019で開いています。