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

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

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

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



複数列を一列にまとめる

下のサンプルを使って、トップの画像のように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)

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


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

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

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

INTROW( )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行分コピーするだけで配列が変わります;


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

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

関連記事・広告






-関数, EXCEL&VBA
-, , ,

© 2022 h1r0-style.net