今回はExcelワークシート関数の新関数の一つであるUNIQUE関数を紹介します。
UNIQUE関数とは何か
UNIQUE関数はサブスクリプションのOfficeや、購入型のOffice2020以降で使用できます。UNIQUE関数はエクセルのワークシート関数で、リストまたはセル範囲から一意の値を抽出するために使用されます。
この関数の構文は次のとおりです。
UNIQUE(配列,[列の比較],[回数指定])
必須の引数は”配列”のみで、これは対象となるセル範囲または配列です(VBA使用時には配列を使用できる)。
[列の比較]はオプションの引数で、列で抽出するか行で抽出するかを指定します。こういったデータテーブルは通常縦に並んでいますので、デフォルトはTRUEですが、FALSEを指定すれば横並びのテーブルから一意の値を抽出できます。
[回数指定]: これもオプションの引数で、デフォルトは FALSEです。これをTRUEにすると、1度でも重複が起きている場合には、その値はリストから排除されます(詳細は後述)。
これだけではイメージができないと思いますので、詳細は後述します。
関数の具体的使い方
まずは最も簡単な使い方として、UNIQUE(配列)として1つの引数だけを使います。恐らく、ほとんどがこの使い方だと思います。
今回使用するのは以下のデータテーブルです。シート名は”元データ”です。
まずはこのテーブルから、商品名のUniqueなリストを作成します。これはつまり、”商品名”に何が入っているかを確認するという作業です。数式は以下の通りです。
=UNIQUE(元データ!A2:A170)
以下のように、Uniqueなリストが作成できました。
このとき、関数はA1セルのみに力されています。Unique関数には”スピル“機能があるので、A1セルにしか数式を入力していないのに、シートの下の方までデータが入っています。
Excelにおけるスピル機能について説明します。英単語のスピル(spill)は「こぼれる」とか「溢れる」という機能です。スピル機能は、1つのセルに数式を入力すると、自動的に隣接するセルに「こぼれ」る機能と言うことができます。上の例では、A1セルに、入力した数式から、A列のA2以降のセルにもA1セルと同等の数式による結果が自動的に表示されています。これにより、大量のデータを扱う際に、各セルに手動で数式をコピー＆ペーストすることなく計算でき、更にExcelの計算量を大幅に削減することができます(Excelは1万個のセルに1万個の数式が入っていると、保存などをする度に1万個の数式の計算結果を更新しようとする)。詳細は以下の記事を参照ください。
同様に、”受注番号”、”エリア”、”担当者”についてもUniqueなリストを作成してみました。
元リストが変わったら？(動的リストの作成)
以上、最も基本的な使い方を紹介しました。次に、元データがどんどん変更されていくようなケースでの対応方法を紹介します。
上で商品名のリストを作成した時の数式を再掲します。
=UNIQUE(元データ!A2:A170)
ここでは元データシートの”A2:A170″という固定された範囲を指定していますので、例えば以下のように171行目に新たなデータが追加されても、Unique関数の結果に反映されません。
このように元データが更新されるようなケースでも使える、つまり動的なUniqueリストの作成方法を紹介します。
テーブルの設定
最も簡単なのは元データをテーブルにすることです。以下のようにテーブルを設定し、名前が”テーブル1″だとすると、
Unique関数を以下のように書けます。
=UNIQUE(テーブル1[商品名])
表示結果は以下のように変わっていません。
この時、テーブルの最下行の1行下にデータを追加すると、テーブルの特性から自動的にテーブルの範囲が拡張されます。
そして、Unique関数の範囲をテーブルで指定しておくと、以下のようにUniqueリストにも更新内容が反映されます。
列全体の指定
業務によっては、元データにテーブルを設定できないこともあると思います。その場合の対応の１つとして、以下のように列全体を指定することで動的なリストを作成することができます。
ここで注意しないといけないのは、列全体を参照するとどうしてもリストの下部の空白も含まれてしまい、Unique関数はその空白を”0″として表示してしまうという点です。
もし0の表示を消す必要がある場合には、複雑になってしまいますがIF関数を使って0の場合は空白として表示させることもできます。
なおIF関数の使い方は以下を参照ください。
複数の関数を組み合わせる
また、元データにテーブルを設定できない場合のもう1つの対応方法として、INDIRECT関数・ADDRESS関数・COUNTA関数との組み合わせによる方法もあります。
UNIQUE関数の引数として指定している関数の組み合わせ部分が非常に複雑なので今後詳細記事を掲載しますが、結果としては以下の通り、テーブルを設定している時と同様に動的なリストを作成できました。
その他のUnique関数の引数の使い方
上ではUNIQUE関数の引数の内”配列”だけを使いました。残りの2つの引数についても、使用頻度は低いと思いますが、説明します。
UNIQUE(配列,[列の比較],[回数指定])
まず[列の比較]については、“行方向”で見るか、”列方向”で見るか、という指定です。デフォルトではFALSEになっていて、FALSEは”行方向”を意味します。ではTRUEにして”列方向”で見るとどうなるでしょうか。
例えば元データが以下のように横方向に伸びていることを想定します。
このようなケースでは以下のように[列の比較]引数をTRUEとすることで、以下のようにUniqueリストを作成できます。
続いて[回数の指定]引数ですが、こちらもデフォルトではFALSEとなっています。これをTRUEにすると、“1回だけ出現するアイテムを返す”という意味になります。
例えば商品名それぞれが何回登場しているかをCOUNTIFを使って調べると以下のようになります。
1回だけ出現しているのは”夕張メロン”だけですので、[回数の指定]引数をTRUEにすると、以下のように夕張メロンだけが表示されます。
UNIQUE関数の存在意義
元々Excelには”重複を削除“と言う機能も存在します。その機能を使うことでも似たような動作を実現することはできますが、UNIQUE関数の存在意義は、元のデータセットを変更することなく、UNIQUEリストを動的に表示することができる点です。
まずは”元のデータセットを変更することなく”の部分から説明します。”重複を削除”機能は、当然ながら、元データ自体にフィルターを設定するので、元データに変更を生じさせてしまうか、テーブル全体を別シートにコピーして作業することになってしまいます。
また”動的リストの作成”の項目で説明したように、元データが変わってもUniqueリストをその都度アップデートできるのは大きなメリットです。この特性によって、これまでExcelでは実現不可能だった”自動更新されるドロップダウンリスト”や”自動更新されるピボットテーブル”も作れるようになります。こちらについては今後解説記事を掲載します。
コメント