(Gスプレッドシート)複数列・複数範囲を指定してセル単位で重複を除く

 GoogleスプレッドシートではUNIQUE関数でデータの重複を除くことができますが、複数の列を対象にした場合は行全体で重複が判定され、重複を除いた行が残ります。1セル単位で重複を判定し除いた結果にはなりません
 そこで少々実験的ですが、複数の列を指定した場合でも1セル単位で値の重複を除いた結果を得る方法を示します。

  • UNIQUE関数の引数として複数の列を縦につないだものを指定することで、セル単位で重複を除いた結果を得ることができます。
  • 他にUNIQUE関数とTEXTJOIN関数などの組み合わせによりセル単位で重複を除いた結果を得ることもできます。この方法では複数の範囲を引数として自然に指定できるのがメリットですが、各セルに含まれる文字数の合計が約50000字に達するとエラーになります。

手順

UNIQUE関数の引数として複数の列を縦につないだものを指定する方法

f:id:accs2014:20191123012404p:plain:right:w500

 B2:D5の計12個のセルに範囲に10個の値が記されており、2つのセルは空白です。
 これらのセルから重複を除いた結果を抽出するものとします。

 B7セルに次のように入力します。

=UNIQUE({B2:B5;C2:C5;D2:D5})

 UNIQUE関数の引数として3つの列を縦につないだものを与えるというわけです。
 記述がシンプルなのがメリットですが、対象となる列が多いと入力が面倒になります。
 

f:id:accs2014:20191123012408p:plain:right:w500

 実行結果です。
 空白を含めて8つの値が抽出されました。
 
 なお、空白を除く場合の例は次のようになります。

=UNIQUE(QUERY({B2:B5;C2:C5;D2:D5},"WHERE Col1<>''"))

UNIQUE関数とTEXTJOIN関数を併用する方法

f:id:accs2014:20191123012413p:plain:right:w500

 上記と同じ例です。
 これらのセルから重複を除いた結果を抽出するものとします。

 B7セルに次のように入力します。

=UNIQUE(TRANSPOSE(SPLIT(TEXTJOIN(",",,B2:D5),",",,)))

 TEXTJOIN関数は複数の行列にまたがって文字列の結合ができますので、これにより一旦すべての値をカンマでつなぎ、SPLITとTRANSPOSEで縦に並べ替えてUNIQUEを適用する、というわけです。対象範囲が何列あっても簡単に指定でき、必要であれば複数の範囲を個々の引数として指定できる(下記備考参照)のがメリットですが、式はちょっと複雑です。 
 

f:id:accs2014:20191123012417p:plain:right:w500

 実行結果です。

 ただし、TEXTJOIN関数で結合できる文字数は50000字まですので、各セルに含まれる文字数の合計が約50000に達するとエラーになります。大量のデータを扱う場合は別の対策が必要です

 以下は備考です。なお本記事では全体的に引数のTRUEとFALSEを必要がない限り省略しており、ちょっと気持ち悪いかもしれませんがご容赦願います。

  • TEXTJOIN関数とSPLIT関数で区切り文字としてカンマを使っていますが、セルの値にカンマが含まれる場合は不具合が生じますので別の記号に変えてください。
  • TEXTJOIN関数は引数として複数の範囲を指定できるので、次のような記述が可能です。B2:D5とF2:G3の全セルからセル単位で重複を除くには次のようにします。

    =UNIQUE(TRANSPOSE(SPLIT(TEXTJOIN(",",,B2:D5,F2:G3),",",,)))

  • 空白を除く場合は次のようにします。

    =UNIQUE(TRANSPOSE(SPLIT(TEXTJOIN(",",,B2:D5),",")))

  • TEXTJOIN関数は「左上→右上→左下→右下」の順番に文字列をつないでいきますので、値の出現順もこれに沿ったものになります。「左上→左下→右上→右下」の順番にしたい場合は対象範囲をさらにTRANSPOSEで囲みます。つまり次のようになります。

    =UNIQUE(TRANSPOSE(SPLIT(TEXTJOIN(",",,TRANSPOSE(B2:D5)),",",,)))