いきなり答える備忘録

Google Workspace(旧G Suite)・Microsoft 365・LibreOfficeなどに関するメモ

(Excel)TOCOL/TOROW関数をエラーや空白セルの削除に使う

 Excelの新関数、TOCOL/TOROW関数についてです。
 これらの関数には、エラーや空白を削除するという機能があります。一見地味ですが、1列(行)だけのデータに対してエラーや空白セルを削除する場合にも便利です。ここではTOCOL関数の例を紹介します。

  • はじめから1列(行)だけのデータに対してエラーや空白セルを削除するためだけにTOCOL/TOROW関数を使う、という使い方ができます。

手順

 画像ではF2セルにTOCOL関数を入力し、B2:D3の範囲に縦1列に並べ替えた結果を得ています。同時に第2引数(無視するセル)を指定して、空白セルを取り除いています。

 F2セル

=TOCOL(B2:D3,1)

 第2引数の「1」は空白セルを除くという意味です(空文字列は除かれません)。「2」にすればエラーのセルが除かれますし、「3」にすれば空白セルとエラーのセルの両方が除かれます。


 これは一見何気ないオマケ機能にみえますが、対象となるデータがはじめから1列の場合にもFILTER関数なしでフィルタがかけられます。
 FILTER関数は複数列の値を抽出できる代わりに、対象データが1列だけであっても第2引数(条件)でその列を指定する必要があります。しかしTOCOL関数ではその条件となる列の指定を省略できます。

 D2セル

=TOCOL(B2:B7,1)


 もう少し凝った例を見てみましょう。
 次の画像ではTEXTAFTER関数を使ってB3:B8の範囲に記録されたアドレスのうちドメイン(@より後ろ)だけを抽出し、さらにFILTER関数でエラーのセルを除いています。


 D3セル

=LET(x,TEXTAFTER(B3:B8,"@"),FILTER(x,NOT(ISNA(x))))

 TEXTAFTER関数では指定した区切り文字(ここでは「@」)がない場合に#N/Aエラーが返りますので、これをFILTER関数で除いています。
 強力な新関数の出現によって、昔よりはるかに短くまとめられるようにはなっていますが、構造的には複雑です。一発で書き下せるのは中級者以上といっていいと思います。


 しかしTOCOL関数を使えばさらに簡単です。抽出対象と条件付けの対象を別々に記す必要がないからです。

 D3セル

=TOCOL(TEXTAFTER(B3:B8,"@"),2)

 TOCOL関数の第2引数を「2」とすることでエラーのセルを除いています。
 FILTER関数と比べて式の意図が見えにくくなるおそれはありますが、使いどころによっては重宝すると思います。