Excel 複数列を1列にする方法

質問

列の終わりがバラバラなのですが、複数列を1列にしたいのです。
手作業ではなく、簡単にできる方法はないでしょうか？
手でコピペするには列がかなり多く、画面におさまりきらない長い列なのできっちりコピペできたか不安があります。
また、よそへ提出する書類になるのでなるべく手作業を省くのが目的です。
ぴったり隙間なくくっつかなくてかまいません。複数セル空白が入ってもかまいません。

Excelに不慣れですが、上司にこういうことできないの？と言われて困っています。
ご存知の方教えていただけますでしょうか？
よろしくお願いいたします。

回答No.1

mshr1962 · ベストアンサー

シート1（元）からシート2で1列にするとして
シート2のA1の数式を
=OFFSET(シート1!$A$1,MOD(ROW()-1,8),INT((ROW()-1)/8))
とすれば8行毎に、隣の列の値をコピーできます。
最期にシート2のA列をコピーして、形式を選択して貼り付けで値とすれば置き換わります。
※1.値の無いセルは0が表示されます。もしコピーするのが文字列だけの場合なら数式の最後に &"" を付ければ空白になります。
※2.数式中の8の部分が、移動する列の最大行に変更してください。

お礼

ありがとうございます！コピペで使わせていただきました。大変助かりました。皆様回答頂きましたが、一番はやく回答頂いたのでmshr1962さんをBAとさせていただきます。

回答No.5

imogasi

Sub test01()
Set Sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
r = Array(1, 3, 4, 6) 'A,C,D,F列の例の場合 統合する対象列の列番号でを列挙
llr = 2 '統合の中間段階の最後行
For c = 0 To UBound(r) '各列の繰り返し
lr = Sh1.Cells(10000, r(c)).End(xlUp).Row '各列の最下行の行番号
Sh1.Range(Cells(2, r(c)), Cells(lr, r(c))).Copy sh2.Cells(llr, "A")
llr = llr + lr - 1
Next c
End Sub
仮定は各列１００００行以下
各列には一番上に１行の見出しがある。
Ｓｈｅｅｔ１の各行ー＞Ｓｈｅｅｔ２のＡ列に統合。
関数の回答より文字数が少ない。
よくデータテストをしてから使うこと。

回答No.4

bunjii

>手でコピペするには列がかなり多く、画面におさまりきらない長い列なのできっちりコピペできたか不安があります。
質問の画像ではA列からC列までの3列ですが実際の列数は何列ですか？
また、抜き出す列の行数は質問の画像のように各列で空欄のセルが中間に無いと考えて良いのですか？
尚、稀に行と列を誤って表現する人がいますので確認します。
A、B、C、Dと上に表示されているのが列記号で、左に上から1、2、3、4と表示されているのが行番号です。
従って、「列が多い」と言うのは「列記号の数が多い」と言うことなので認識を再確認してください。

提示の画像で3列（A、B、C）を対象に最大1000行を処理し、E列へ抜き出す数式です。（Excel 2013で検証、Excel 2007以降のバージョンで再現可能）
E1=IFERROR(INDEX(OFFSET(A$1,0,0,MAX(INDEX(N(NOT(ISBLANK(A$1:A$1000)))*ROW(A$1:A$1000),0))),ROW(A1)),IFERROR(INDEX(OFFSET(B$1,0,0,MAX(INDEX(N(NOT(ISBLANK(B$1:B$1000)))*ROW(B$1:B$1000),0))),ROW(B1)-MAX(INDEX(N(NOT(ISBLANK(A$1:A$1000)))*ROW(A$1:A$1000),0))),IFERROR(INDEX(OFFSET(C$1,0,0,MAX(INDEX(N(NOT(ISBLANK(C$1:C$1000)))*ROW(C$1:C$1000),0))),ROW(C1)-MAX(INDEX(N(NOT(ISBLANK(A$1:A$1000)))*ROW(A$1:A$1000),0))-MAX(INDEX(N(NOT(ISBLANK(B$1:B$1000)))*ROW(B$1:B$1000),0))),"")))
E1セルを下へ必要数コピーします。
数式を見てもどのような論理になっているか理解できないかも知れません。
Excelの高度な使い方を学習して理解できるようになってください。

回答No.2のマクロの方が理解し易いと思いますので併せて学習すると良いでしょう。

>Excelに不慣れですが、上司にこういうことできないの？と言われて困っています。
「私にはできません。」と答える勇気も必要です。
仕事の効率化に第三者の知恵を無料で求めるのは如何なものでしょうか？

お礼

回答をありがとうございました。
＞仕事の効率化に第三者の知恵を無料で求めるのは如何なものでしょうか？
これについては、大変心苦しく思っております。集計する内容を把握しているのは私だけ、また案件の内容によって外部に出せない状況でした。
せめて私のできる範囲で別ジャンルでお答えするなどできればと思っております（自己満足ですが・・・）

回答No.3

msMike

完全自動化ではないけど、次のようなのは如何でせう？
以下は Sheet2 における作業です。
１．「終わりがバラバラな」列の最大行数を見付けておく(此処では列Ａの 8)
２．セル A1 に次式を入力
￣￣ =OFFSET(Sheet1!A$1,MOD(ROW(A1)-1,8),(ROW(A1)-1)/8)
３．そのセルを下方にズズーッとセル A24 (←8x3) までオートフィル
４．列Ａ全体を選択して[コピー]→[値の貼り付け]を実行
５．列Ａ全体を選択したままで、Ctrl+G 「エイヤッ！」と叩き付け
６．[セル選択]を実行
７．"定数"に目玉入れの後、“数値”のみにチェックを残し、その他の全チェックを外して、[OK]をクリック
８．[ホーム]→[セル <削除>]→"上方向にシフト"→[OK]

お礼

詳しい手順をありがとうございました・・・！
なんとか提出が間に合ったようです、皆様のおかげです。

回答No.2

aokii

マクロになります。
i = 1
For 列 = 1 To 255
For 行 = 1 To Cells(65536, 列).End(xlUp).Row
Sheets("sheet2").Cells(i, 1) = Sheets("sheet1").Cells(行, 列)
i = i + 1
Next
Next

または、
http://officetanaka.net/excel/function/tips/tips66.htm

お礼

回答ありがとうございます！
もしかして隙間もできずに収集できたのかもしれません。。。でも難しくて私には無理でした；；お時間割いていただきありがとうございます！！