エクセルで複数列を一列にまとめる方法

『複数列を一列にまとめる』と言っても様々な解釈がありますが、今回ピックアップするのは上の図のようなもの。

つまり、複数列がそれぞれに複数行の値をもつ書式を 一列にまとめる。
それを関数をつかって解決する方法です。

しかも、各列のデータ行が増えたときにも自動で対応したいですね。

複数列を一列にまとめる

下のサンプルを使って、トップの画像のようにE列一列にまとめます。

OFFSET関数に規則性をもたせる

この問題を解決するには複数の関数を使うのですが、なかでも OFFSET関数がその中核となります。

OFFSET関数と聞くと「うっ、難しいヤツ、、、」と思う方もいると思いますが、仕組みは意外と単純です。

カンタンに説明すると『基準となるセルから、何行・何列ずれた位置にあるセルの値を返す』関数です。
書式は；

OFFSET（参照, 行数, 列数 [,高さ] [,幅]）

のように表されます。

参照：　基準となるセル位置
行数：　基準のセルから（下に）ずらす行数
列数：　基準のセルから（右に）ずらす列数

「高さ」と「幅」は、対象の行や列を複数にする場合のみ使用するので、今回は省略します。
※「高さ」と「幅」が OFFSET関数のハードルを高くしていると思う（私見ですが）

今回のポイントは、基準セルからずれる「行数」「列数」に関数をつかって規則性を持たせることです。

行数のずれの規則性を捉える

すべての基準になるセルを A1（絶対参照で「$A$1」）とします。
各列にはデータが3行ずつ入っているので、それぞれの基準からの行の「ズレ」は、、、

上のように「0、1、2、」となります。
E列に全ての値を 一列にまとめる には、以下のような考え方になります。

▲行ズレ「0、1、2」を3回繰り返す

各列のデータ行が3行の時は「0、1、2」、4行あれば「0、1、2、3」となるのが行数の規則性だとわかりました

列数のずれの規則性を捉える

行数と同様に、セルA1を基準としたときの列数のズレを確認してみましょう。

A列はズレ「0」、B列はズレ「1」、C列はズレ「2」となります。

ただ注意するべきは、E列に一列にまとめる場合は以下のような配列になります。

▲E列は上から「0, 0, 0、1, 1, 1、2, 2, 2」となる

これで行数と列数のズレの規則性が判明しました。
これら行ズレと列ズレの組み合わせを一列にまとめて考えると、以下のようになります。

OFFSET関数を完成させる

行ズレの規則性にハマる関数

行ズレの規則性を関数で表すためには、MOD関数とデータの行数をカウントする COUNTA関数を使います。

このサンプルで、行ズレの「0、1、2」を繰り返す数式は；

＝MOD（ROW（）-1, COUNTA（A：A））

となります。
つまり、行番号（から１を引いた数）をデータの数で割ったときの「余り」をMOD関数で算出します。
結果は；

▲「0、1、2」が繰り返された！

列ズレの規則性にハマる関数

つづいて、列ズレの「0, 0, 0、1, 1, 1、2, 2, 2」を発生させる数式です；

＝INT（ROW（）／ COUNTA（A：A)－0.1)

行番号をデータの数で割った数から 0.1を引いて INT関数で「切り捨て」します。
結果は以下のようになります；

▲「0, 0, 0、1, 1, 1、2, 2, 2」が発生！

OFFSET関数に「行ズレ」「列ズレ」を当てはめる

いよいよ OFFSET関数に「行ズレ」「列ズレ」の数式を当てはめます。
結果の数式は；

＝OFFSET（$A$1, MOD（ROW（）-1, COUNTA（A：A）), INT（ROW（）／COUNTA（A：A）-0.1））

以下のように表示されれば成功です。

ためしに、A～C列の4行目にデータを入れてみてください。
E列の数式を下の行に3行分コピーするだけで配列が変わります；

▲データ行が増えても問題なし

以上で『エクセルで複数列を一列にまとめる方法』は終了。
その他の関連記事と合わせて仕事や趣味に役立ててください。