『複数列を一列にまとめる』と言っても様々な解釈がありますが、今回ピックアップするのは上の図のようなもの。
つまり、複数列 が それぞれに複数行の値をもつ 書式を 一列にまとめる。
それを 関数をつかって解決する方法です。
しかも、各列のデータ行が増えたときにも自動で対応したいですね。
複数列を一列にまとめる
下のサンプルを使って、トップの画像のようにE列 一列にまとめます。
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関数で算出します。
結果は；
▲「0、1、2」が繰り返された！
列ズレの規則性にハマる関数
つづいて、列ズレの「0, 0, 0、1, 1, 1、2, 2, 2」を発生させる数式です；
行番号 を データの数 で割った数から 0.1を引いて INT関数で「切り捨て」します。
結果は以下のようになります；
▲「0, 0, 0、1, 1, 1、2, 2, 2」が発生！
OFFSET関数に「行ズレ」「列ズレ」を当てはめる
いよいよ OFFSET関数に「行ズレ」「列ズレ」の数式を当てはめます。
結果の数式は；
以下のように表示されれば成功です。
ためしに、A～C列の4行目にデータを入れてみてください。
E列の数式を下の行に3行分コピーするだけで配列が変わります；
▲データ行が増えても問題なし
以上で『エクセルで複数列を一列にまとめる方法』は終了。
その他の関連記事と合わせて仕事や趣味に役立ててください。
関連記事・広告
