ExcelのOFFSET関数で1列のデータを複数行・列の表形式に変換する方法

WordなどのテキストエディターからExcelシートにデータをコピーペーストすると、すべてのデータが1つの列に連続して並んでしまうことがあります。このようなバラバラに並んだデータを、見やすい表形式に整理したい場面は少なくありません。本記事では、OFFSET関数を使って、1列にまとまったデータを複数の行と列に整形する方法を解説します。

Transpose機能では対応できないケースとは

きちんと整理された表データを行と列で入れ替えるだけなら、「コピー」→「形式を選択して貼り付け」→「行列を入れ替える」（Transpose）で簡単に変換できます。

しかし、元データが1つの列にずらりと並んでいるだけの場合、この方法は使えません。まずはデータの並びに一定のパターンがあるかを確認しましょう。多くの場合、縦に連なるデータには規則性があります。そのパターンさえ把握できれば、数式で自動的に整形できます。

例：名前・生年月日・成績が1列に並んでいる場合

たとえば、次のように「名前」「生年月日（DOB）」「成績」の順番でデータがA列に並んでいるとします。

Karan
01/06/1987
A

Justin
09/08/1964
D

Bob
04/05/1996
B

Jason
08/09/1984
C

このデータを、各レコードが「名前・生年月日・成績」の3項目を持つ表として、行と列に分けて整理したいとします。ここで活躍するのがOFFSET関数です。

OFFSET関数の基本構文

OFFSET($A$1,(ROW()-f_row)*rows_in_set+INT((COLUMN()-f_col)/col_in_set), MOD(COLUMN()-f_col,col_in_set))

上の例のように、データがA1から始まるA列に並んでいる場合、実際に入力する数式は次のようになります。

=OFFSET($A$1,(ROW()-2)*3+INT((COLUMN()-3)),MOD(COLUMN()-3,1))

この数式では、データを「3行で1セット」として扱って分割します。列数は固定ではなく、データ全体の長さに応じて増えていきます。途中に空欄がある場合は、代わりに「0」が表示される点に注意してください。

具体的な操作手順

1. データをC2から表示させたい場合は、セルC2に上記の数式を入力します。
2. 数式を右方向に3列分、さらに下方向へオートフィルでコピーします。
3. セルの値が「0」になり始めたら、そこがデータの終端です。余計な0や空白部分は削除してください。

データが正しく整形されたら、先頭行に「名前」「生年月日」「成績」といった見出しを入力し、必要な場所に切り取り＆貼り付けで移動すれば完成です。

ぜひ参考にしてみてください。