いきなり答える備忘録

Google Workspace・Microsoft 365・LibreOfficeなどに関するメモ

(Excel)UNIQUE関数で空白を除いた結果を得る方法

 ExcelのUNIQUE関数で重複を除いた結果を取得する際に、空白(空白セルや空文字列)を無視して何らかの値があるセルだけを取得する方法についてです。

  • FILTER関数やTOCOL関数を併用することで、UNIQUE関数の結果から空白を除くことができます。

データが1列の場合

FILTER関数を併用する方法

 次の画像ではD3セルに「=UNIQUE(B3:B9)」と入力することで、B列に入力されている品名から重複を除いた結果を得ています。
 ただしUNIQUE関数では空白セル(未入力のセル。画像内のB6セル)は結果から除外されず、「0」と表示されます。また、空文字列(「=""」や「'」と入力したときに生じる長さ0の文字列)もそのまま残ります。

 


 そこでFILTER関数を併用することで空白セルを除外した結果を得ています。
 この方法では空文字列も除外されます。

 D3セル

=UNIQUE(FILTER(B3:B9,B3:B9<>""))

 FILTER関数を使ってB3:B9の範囲から空白を除いた結果を得ています。
 それをUNIQUE関数の引数とすることで、結果的にUNIQUE関数の結果から空白を除いています。

TOCOL関数を併用する方法

 こちらではTOCOL関数を併用して空白セルを除いています。
 ただしこの方法では空文字列(「=""」や「'」)は除かれないので注意してください。

 D3セル

=UNIQUE(TOCOL(B3:B9,1))

 TOCOL関数は表のデータを縦1列に並べ替える関数ですが、第2引数を「1」とすることで空白セルを除外することができます。
 この「空白セルを除外する」という機能(だけ)を利用して、その結果をUNIQUE関数の引数としています。
 結果的にUNIQUE関数の結果から空白セルが除かれます。



 前述のとおり、TOCOL関数で除外できるのは純粋な空白セルだけで、空文字列は除かれません。
 よって実用的にはFILTER関数の方が使いやすいと思いますが、使い分けとして覚えておいてよいかと思います。

データが複数列の場合

 複数列のデータにおいて、重複行を除いたうえですべての列が空白(空白セルや空文字列)の行を除く方法を紹介します。また、セル単位で値の重複を除いたうえで空白を除く方法についても紹介します。


 次の画像では上記例と同様にFILTER関数を使いB列とC列の両方が空白である行を除き、さらにUNIQUE関数で重複を除いた結果を得ています。UNIQUE関数の機能により、両方の列が同じ値である行(具体的には「もも、なす」の行)が重複するものと判定され、重複が除かれています。
 ただし、一方のみ空白セルである行は空白セルが「0」になって残っていることが注意点です。

 E3セル

=UNIQUE(FILTER(B3:C9,B3:B9&C3:C9<>""))

 FILTER関数の第2引数(条件)を「B3:B9&C3:C9<>""」とすることで両方の列が空白である行が除かれます。もちろん3列以上でも同じようにできます。



 さらに次の画像では空白セルが「0」となるのを防いでいます。

 E3セル

=UNIQUE(FILTER(IF(B3:C9="","",B3:C9),B3:B9&C3:C9<>""))

 1つ上の例との違いはFILTER関数の第1引数(対象範囲)を「IF(B3:C9="","",B3:C9)」としていることで、これによりあらかじめ空白セル(未入力)をすべて空文字列("")に置き換えています。こうすることで空白セルが「0」になるという現象を防いでいます。



 次の画像では、(行単位ではなく)セル単位で重複を判定して重複を除いた結果を得ています。

 E3セル

=LET(x,TOCOL(IF(B3:C9="","",B3:C9)),UNIQUE(FILTER(x,x<>"")))

 式が長くなるのでLET関数を利用しています。
 「TOCOL(IF(B3:C9="","",B3:C9))」つまり「2つの列の空白セルを空文字列に置き換えたうえで縦1列に並べ直したもの」をxと名付けています。
 そしてFILTER関数とUNIQUE関数によりxから空文字列を除き、さらに重複を除いた結果を表示しています。
 なお、データが2列しかないので「=UNIQUE(VSTACK(FILTER(~),FILTER(~)))」のような式も考えられます。また、このデータ例ではB列とC列の間にデータの重複がないので「=VSTACK(UNIQUE(FILTER(~)),UNIQUE(FILTER(~)))」でもいいですが、それだと2つの列の間で重複がある場合に結果にも重複が残るので注意が必要です。



 上の例では品名1(果物)と品名2(野菜)がごっちゃになって表示されていますが、次の画像では値の表示順を変えて、品名1が先に、品名2が後に表示されるようにしています。

 E3セル

=LET(x,TOCOL(IF(B3:C9="","",B3:C9),,TRUE),UNIQUE(FILTER(x,x<>"")))

 TOCOL関数の第3引数を「TRUE」とすることで左側の列から順に値を取得するようにしています(デフォルトでは上の行から取得)。
 結果的にB列の値が先に、C列の値が後に固まって表示されます。