UNIQUE関数で重複しない値を取り出す:Excel関数

スポンサードリンク

作成:2020/2/6, 更新:2023/3/12

UNIQUE関数で1列の重複しないデータを抽出する	UNIQUE関数で複数列の重複しないデータを抽出する
UNIQUE関数で[回数指定]を使った例	UNIQUE関数で[列の比較]を指定した例
クロス集計表を作成する

UNIQUE関数が利用できるExcelの種類(バージョンについて)

UNIQUE関数はExcel for Microsoft 365(サブスクリプション型)で使える関数です。
(Office365は2020/4/22からは Microsoft365となりました)
ちなみに、Excel2016(永続ライセンス版),Excel2019(永続ライセンス版)では使用できませんので、ご注意ください。
なお、2021/10/5から市販されている Excel2021(永続ライセンス版)でも使用できます。

	Spill(スピル)	UNIQUE関数
Excel2016	×	×
Excel2019	×	×
Excel2021	○	○
Excel for Microsoft365	○	○

重複しないデータを返すことができます。　　　topへ
　ユニーク
=UNIQUE(配列,[列の比較],[回数指定])

UNIQUE関数の構文

=UNIQUE(配列,[列の比較],[回数指定])
引数の表現(日本語)がわかりにくいのでよく見てください。

配列	一意の値を表示する範囲または配列
列の比較	比較の方法を論理値で指定します。 FALSE (または省略した場合) は、行を比較し、一意の行を返します。 TRUEは、列を比較し、一意の列を返します。
回数指定	範囲または配列で1回だけ出現する行または列を論理値で指定します。 FALSE (または省略した場合) は、範囲または配列の重複しないすべての行または列を返します。 TRUEは、範囲または配列から1回だけ出現するすべての個別の行または列を返します。

ここで使用したサンプルデータ

ここで使用するデータを表で表示しますので、コピーしてお使いください。

	B	C	D
2	商品名	産地	数量
3	スイカ	熊本	30
4	マンゴー	鹿児島	10
5	マンゴー	鹿児島	20
6	スイカ	鹿児島	10
7	ポンカン	種子島	20
8	スイカ	宮崎	30
9	ポンカン	屋久島	20
10	たんかん	屋久島	10
11	マンゴー	宮崎	30
12	ポンカン	鹿児島	25
13	サワーポメロ	鹿児島	10
14	たんかん	種子島	30
15	サワーポメロ	鹿児島	10
16	たんかん	種子島	30
17	ポンカン	鹿児島	20
18	マンゴー	宮崎	20
19	たんかん	鹿児島	30
20	ポンカン	屋久島	30
21	サワーポメロ	鹿児島	20
22	スイカ	熊本	20

UNIQUE関数で1列の重複しないデータを抽出する

商品名で重複のない値(一意の値)を抽出します。
F3セルに　=UNIQUE(B3:B22)　と入力しました。
重複なしの商品名のリストが作成できました。

データリストがテーブルに変換されている場合
テーブルの名前は　テーブル1　としています。
F3セルの数式は　=UNIQUE(テーブル1[商品名])　となります。

抽出された各商品名の数量を合計してみます。
G3セルに　=SUMIF(B3:B22,F3#,D3:D22)　と入力するだけで、スピルの機能ですべて計算ができます。
数式中の　F3# はスピルの機能で求められた範囲 (F3:F7は=UNIQUE(B3:B22) で求められたものです)を指定しています。
# はスピル範囲演算子と呼ばれます。
なお、テーブルでの構造化参照では　=SUMIF(テーブル1[商品名],F3#,テーブル1[数量])　となります。

UNIQUE関数で複数列の重複しないデータを抽出する

商品名と産地のセットで重複しないデータ(一意のリスト)を作成します。
F3セルに　=SORT(UNIQUE(B3:C22))　と入力しました。
なお、データを見やすくするために、SORT関数を使って抽出データを並べ替えています。SORT関数については SORT関数をご覧ください。
商品名と産地のセットで重複しないデータを抽出することができました。

データリストがテーブルに変換されている場合
テーブルの名前は　テーブル1　としています。
F3セルの数式は　=SORT(UNIQUE(テーブル1[[商品名]:[産地]]))　となります。

ついでですので、各商品と産地での数量を計算してみます。
H3セルに　=SUMIFS(D3:D22,B3:B22,F3:F14,C3:C22,G3:G14)　と入力するだけで、スピルの機能ですべて計算されます。

テーブルの場合はちょっとややこしくなります。
=SORT(UNIQUE(テーブル1[[商品名]:[産地]]))　で取り出した配列の各列を計算に利用する方法が難しいと思います。
ここでは Excel for Microsoft365で使用できるようになった TAKE 関数を使っています。
=SUMIFS(テーブル1[数量],
テーブル1[商品名],TAKE(F3#,,1),
テーブル1[産地],TAKE(F3#,,-1))

UNIQUE関数で[列の比較]を指定した例　(2021/08/18)

構文：=UNIQUE(配列,[列の比較],[回数指定]) の[列の比較]を TRUE と指定した例です。
ここでは、サンプルのデータを縦横入れ替えてコピーしたものを利用します。
B8セルに　=UNIQUE(B2:V2,TRUE)　と入力しています。
商品名の列B2:V2　から、重複しない一意のデータが抽出されます。
UNIQUE関数で1列の重複しないデータを抽出するの行が列に変わったものです。

B8セルに　=UNIQUE(B2:V3,TRUE)　と入力しています。
UNIQUE関数で複数列の重複しないデータを抽出するの行と列が変わったものになります。

UNIQUE関数で[回数指定]を使った例

ここでは、商品名と産地の組み合わせが1回だけの組み合わせを抽出します。
構文：=UNIQUE(配列,[列の比較],[回数指定]) の[回数指定]を TRUE と指定する例です。

E:Fセルは説明のために、1回だけ出現しているのを確認するために、E3セルには =B3&"_"&C3 、F3セルに =COUNTIF($E$3:$E$22,E3) と出現回数を求めています。
F列が 1 となるデータの組み合わせが UNIQUE関数で抽出できたのがわかります。

H3セルには　=UNIQUE(B3:C22,,TRUE)　と入力しています。
[列の比較]は省略しています(入力していません)。
[回数指定]で TRUE と指定しました。1回だけ出現するデータが抽出できました。

UNIQUE関数を利用して、クロス集計表を作成する

商品名と産地のクロス集計表を作成してみます。
ピボットテーブルを使うと下図のようになります。

データの増減に対応するために、テーブルに変換しています。
重複しない「商品名」を取り出します。
F3セルに　=UNIQUE(テーブル1[商品名])　と入力しています。
G2セルに　=TRANSPOSE(UNIQUE(テーブル1[産地]))　と入力しています。
列方向へ重複しない産地を並べるために、TRANSPOSE関数を使っています。
合計値は　=SUMIFS(テーブル1[数量],テーブル1[商品名],F3#,テーブル1[産地],G2#)　で求めます。
(F3#　の#はスピル範囲演算子と呼ばれるものです：スピル範囲全体を参照することができます。)
テーブルに新しいデータを追加すると、集計表にも自動で新しいデータが反映されます。
SUMPURODUCT関数でも集計ができるのでは?・・・と思ったのですが、スピルには対応していないようです。
G3セルに =SUMPRODUCT(($D$3:$D$22)*($B$3:$B$22=$F3)*($C$3:$C$22=G$2))　と入力して、数式をG3:K7セルにコピーしました。