【Excel】2段ずつの表を一瞬で1行1件にリスト化★

2023年6月27日 18:00

こんにちは、HARUです！

ビジネスの現場では、１件の情報が２段ずつや３段ずつに積みあがった表に出くわすことがあります。

Excel上は入力するだけで加工や集計が行われず、印刷したりPDFに変換したりPowerPointなどに貼り付けたりしたときの見やすさを優先するならこれでも良いですが、いざこの表のデータをまとめて整形したいとなったときは、やはり1行1件のデータベース形式になっていることが理想です。

とはいえこれを１つずつ加工していくのは相当面倒ですよね。

そこでこの記事では、複数段の表を一瞬でデータベース構造に変換するテクニックをご紹介します。

必要な作業は驚くほどにシンプルなので、ぜひ最後までご一読ください！

なお、今回使ういくつかの関数はすべて、本記事執筆時点でMicrosoft365にのみ仮実装されている関数です。

以前の記事で取り上げた「スピル」動作に触れておくことでより理解が深まりますので、まずそちらをご覧いただくことをおススメします！
↓スピルの挙動と活用術はこちら↓

基本動作

今回のテーマでおさえておきたい関数は４つあります。

TOROW：配列内のデータを１行に並べる
TOCOL：配列内のデータを１列に並べる
WRAPROWS：１列(行)に並んだデータを指定の列数で改行する
WRAPCOLS：１列(行)に並んだデータを指定の行数で改列する

４つのグループで構成される営業課の人員表をサンプルに、早速基本動作を見ていきましょう。

TOROW関数

TOROW関数を挿入し、第１引数「array(配列)」に対象のデータ範囲を参照します。

残りの引数は省略可能です。
結果を見てみると、元々４行４列だったデータが１行に並びます。

ただし、元の表で空白セルとなっている部分は「０」が返されます。
そこでTOROW関数の第２引数「ignore(無視)」で、"1"(空白を無視する)を選択します。

これにより、空白セルを無視してデータを並べられます。

さて、今回は「赤井」→「森下」→「佐々木」のように、元データを横方向に見て並べています。

対して「赤井」→「鹿島」→「五十嵐」のように、縦方向に見て並べたいときは、第３引数「scan_by_column(スキャン)」を"TRUE"(列でスキャンする)に設定します。

これにより、元データを縦方向にみた「赤井」→「鹿島」→「五十嵐」の純に並びます。

TOCOL関数

TOCOL関数の基本構成はTOROW関数と同じです。

①TOCOL関数を挿入し、第１引数「array(配列)」に対象のデータ範囲を参照する。
②第２引数「ignore(無視)」で"1"(空白を無視する)を選択する。
③第３引数「scan_by_column」で"TRUE"(列でスキャンする)を選択する。

これにより、前述のTOCOL関数で指示したのと同じ要件でデータが１列に並びます。

重複の削除

もし２つのグループを兼務しているなどで同じ名前が２回登場し、この重複を解消した状態で並べたいときは、TOCOL関数(TOROW関数)をUNIQUE関数でネストしましょう。

WRAPROWS関数

1列(行)に並んだデータを指定の列数で改行します。

①WRAPROWS関数を挿入し、第１引数「vector(1列(行))」に対象のデータ範囲を参照する。

②第２引数「wrap_count(列数)」に、横に何件並べたら次の行へ移動するか、その列数を指示する。
今回は横に4件並べてから改行したいので、"4"列とする。

第３引数は省略可能です。
結果、１列だったデータが横方向に並べられ、指定した"4"列目で次の行に移るように表示されます。

なお、たとえば第２引数「wrap_count(列数)」を"5"にすると、スピル範囲のセル数が15個となり、全12件の対象データに対し過剰な3つはエラーが返ります。

ここで、第３引数「pad_with(埋める)」の出番です。
対象のデータ以上にスピル範囲のセル数が多い場合にどの文字で埋めるかという指示を出すことができます。

今回は””と入力し、何も表示しないように設定します。

これにより、エラー表示が解消されます。

このようにマトリクス構造で取得するスピル範囲には往々にして余分なセルが発生しますので、省略可能でも第３引数はあらかじめ設定しておくことをおすすめします。

WRAPCOLS関数

1列(行)に並んだデータを指定の行数で改列します。
WRAPCOLS関数の基本構成はWRAPROWS関数と同じです。

①WRAPROWS関数を挿入し、第１引数「vector(1列(行))」に対象のデータ範囲を参照する。
②第２引数「wrap_count(行数)」に、縦に何件並べたら次の列へ移動するか、その行数を指示する。
今回は縦に5件並べてから改列したいので、"5"行とする。
③第３引数「pad_with(埋める)」に""を入力する。