いきなり答える備忘録

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

(Excel)TOCOL関数の使い方

 Excelで複数行複数列のデータを縦1列に並べ替える、TOCOL関数の使い方についてです。
 TOROW関数も並べる方向が違うだけでほぼ同じです。

  • 「=TOCOL(範囲)」という式で、複数行複数列のデータを縦1列に並べ替えることができます。

機能と構文

 TOCOL関数の機能は「指定した範囲を縦1列に並べ替える」というものです。
 構文は次のとおりです。

TOCOL(範囲[, 削除する値, スキャン順])

 第1引数だけが必須です。
 第2引数と第3引数に設定できる値は次のとおりです。 

第2引数の設定値
効果
0すべての値を保持する(既定値)
1空白セルを削除する
2エラーを削除する
3空白セルとエラーを削除する

第3引数の設定値
効果
FALSE上の行から順に値をスキャンする(既定値)
TRUE左の列から順に値をスキャンする

基本的な使用例

 次の画像ではG2セルに式を入力し、B2:E3の範囲に並ぶ8つの値を縦1列に並べ替えた結果を得ています。

 G2セル

=TOCOL(B2:E3)

 TRANSPOSE関数などと同様に指定した範囲を並べ替えるというだけの関数で、基本的にはとても単純です。

第2引数(削除する値)の効果と使用例

 第2引数には次の4種の値を指定でき、省略した場合は0を指定したものとみなされます。

効果
0すべての値を保持する(既定値)
1空白セルを削除する
2エラーを削除する
3空白セルとエラーを削除する

 以下では0以外を指定する例を見てみます。


 まずは1を指定した例です。
 並べ替えの対象とするB2:E3の範囲には空白セルが1つ含まれていますが、並べ替えに伴い削除されているのがわかります。

 G2セル

=TOCOL(B2:E3,1)

 空白セル(値がない)は削除されますが、空文字列(="")は削除されないので注意が必要です。



 次に2を指定した例です。
 B2:E3の範囲には2つのエラー(#N/Aと#VALUE!)が含まれていますが並べ替えと共に削除されているのがわかります。

 G2セル

=TOCOL(B2:E3,2)

 なお空白セルが0に変換されているのが気になるかもしれませんが、これについては「=TOCOL(IF(B2:E3="","",B2:E3),2)」とすれば空白セルは空文字列になります。



 こちらは3を指定した例です。
 並べ替えと共に空白セルとエラー、合わせて3つのセルの内容が削除されているのがわかります。

 G2セル

=TOCOL(B2:E3,3)

 この場合も空文字列は削除されません

第3引数(スキャン順)の効果と使用例

 第3引数には次の2種の値を指定でき、省略した場合はFALSEを指定したものとみなされます。

効果
FALSE上の行から順に値をスキャンする(既定値)
TRUE左の列から順に値をスキャンする



 まずはFALSEを指定した例です(結果は一番最初の画像と同じですが、比較のため再掲します)。
 対象となるB2:E3セルの範囲を上の行から順にスキャンしていくので、上の方にある値が並べ替え後には上に並びます。

 G2セル

=TOCOL(B2:E3,,FALSE)

 


 次の画像はTRUEを指定した例です。
 対象となるB2:E3セルの範囲を左の列から順にスキャンしていくので、左の方にある値が並べ替え後には上に並びます。

 G2セル

=TOCOL(B2:E3,,TRUE)

応用例

 利用例が思いつきにくそうな関数ですが、検索やデータ変換に重要な役割を果たします。

フィルタ代わりにする

 エラーや空白セルを削除するだけならFILTER関数よりずっと簡単にできるので、もともと1列の配列に対して使っても便利です。

クロス抽出の逆

 クロス表内の値から見出し行(列)を検索・抽出するのは厄介でしたが、TOCOL関数を使って実現できます。詳しくは次の記事で紹介しています。

www.officeisyours.com

アンピボットなど

 データの形式を変換するのに役立ちます。具体例として次のようなものが挙げられます。

www.officeisyours.com

www.officeisyours.com