excelのセルの並べ替え。

質問

Excelの操作で迷っています。詳しい方の知恵を拝借できたらと思います。

以下のようにデータの入力されたセルが並んでいます(アルファベット1文字が1セルです)。

A B C
D E
F G H I
J K

横に何セル並んでいるかは、行によってまちまちです。このようなデータが、数千行あります。これを、以下のように1列に並べ替えたいのです。
A
B
C
D
E
F
G

「行列を入れ替えてコピー」ではなかなかに大変なので、何か良い方法を探しています。よろしくお願いいたします

回答No.4

noname#204879 · ベストアンサー

１．Sheet2 のセル A1 に次式を入力して、此れを下方に（数値 0 以外
　　が出なくなるまで）ズズーッとドラッグ＆コピー
　　=OFFSET(Sheet1!A$1,(ROW(Sheet1!A1)-1)/4,MOD(ROW(Sheet1!A1)-1,4))
　　ただし、Sheet1 において、横に並んでいるセル数の最大値が例え
　　ば 10 ならば、上式の 4 を 10 に変更のこと
２．Sheet2 のＡ列全体を選択して、[コピー]→[値の貼り付け]を実行
３．Sheet2 の上端で[オートフィルタ]を設定して 0 を選択
　　（→ 0 の行だけが表示される）
４．最上の 0 のセルクリック後、Shift+Ctrl+↓を実行
５．選択範囲内でマウスの右クリックから[行の削除]→[OK]を実行
６．[オートフィルタ]の解除

お礼

ご教授下さった方法でなんとかできました。有り難う御座います。
あなたは私の睡眠時間を救ってくださった恩人です。有り難う御座いました。

回答No.3

maron--5

A　　　B　　　C　　　D　　　E　　　F　　　G
1　　　A　　　B　　　C　　　　　　　　　　 1　　　A
2　　　D　　　E　　　　　　　　　　　　　　 1　　　B
3　　　F　　　G　　　H　　　I　　　　　　 1　　　C
4　　　J　　　K　　　　　　　　　　　　　　 2　　　D
5　　　　　　　　　　　　　　　　　　　　　　 2　　　E
6　　　　　　　　　　　　　　　　　　　　　　 3　　　F
7　　　　　　　　　　　　　　　　　　　　　　 3　　　G
8　　　　　　　　　　　　　　　　　　　　　　 3　　　H
9　　　　　　　　　　　　　　　　　　　　　　 3　　　I
10　　　　　　　　　　　　　　　　　　　　　　4　　　J
11　　　　　　　　　　　　　　　　　　　　　　4　　　K

◆F列を作業列に
F1=IF(ROW(A1)>COUNTA($A$1:$D$4),"",SMALL(INDEX(SUBSTITUTE(($A$1:$D$4<>"")*1,0,10^4)*ROW($A$1:$D$4),),ROW(A1)))
★下にコピー
G1=IF(F1="","",INDEX($A$1:$D$4,F1,COUNTIF($F$1:F1,F1)))
★下にコピー

お礼

ご教授いただいた方法で試していたところ、どこかタイプミスしたようで???な状況になったのですが、この方法でもできるようです。
有り難う御座いました。

回答No.2

imogasi

質問者には不適かもしれないが、VBAでなら簡単です。
横（列方向）に最右のセルが捉えられるので、そこまで１セルづつ
値をとって、別シートや別列に１セルずつ下にずらして、代入していくだけ。右端に「達したら１行下の行に入る。
ーー
関数では下記でやりましたが、作業列を使わざるえない、非常な難問と思います。作業列は少し減らせるが。
作業列のない式はきわめて難しいものになるでしょう。
この課題は、質問者には手におえない質問でしょう。
ーー
例データ
第２行目からデータをいれ、は空白にしておく
A2:D７
A B C
D E
F G H I
J K
L M
N O P Q
作業列としてE列に
E1に０、E2に
=E1+COUNTA(A2:D2)　
と入れて下方向に式を複写する。
作業列をH列とし、H１に
=MATCH(ROW()-1,$E$1:$E$100,1)
ト入れて下方向に式を複写する。
作業列をI列とし、I1に
=COUNTIF($H$1:H1,H1)　と入れて式を下方向に複写する。
J1に
=INDEX($A$2:$D$100,H1,I1)
と入れて下方向に式を複写する。
結果
E列　E1:E６　作業列
0
3
5
9
11
13
H,I,J列　H,I列は作業列　I列は最終結果
1 1 A
1 2 B
1 3 C
2 1 D
2 2 E
3 1 F
3 2 G
3 3 H
3 4 I
4 1 J
4 2 K
5 1 L
5 2 M
6 1 N
6 2 O
6 3 P
6 4 Q
＝＝＝＝
Sub test01()
d = Range("A65536").End(xlUp).Row
k = 2
'MsgBox d
For i = 2 To d
c = Cells(i, "A").End(xlToRight).Column
'MsgBox c
For j = 1 To c
Cells(k, "h") = Cells(i, j)
k = k + 1
Next j
Next i
End Sub

お礼

コメントいただき有り難う御座いました。
やはりVBAなら簡単なのですね。関数を用いた方法でなんとか仕事はできたのですが、次はVBAももう少し勉強しようと思います。
丁寧な解答を頂き有り難う御座いました。

回答No.1

onntao

一度　"＆"　演算子や　CONCATENATE関数で全てを結合し
A1に結合データがあるとして　B1：B10000　範囲を指定しておき　
=MID($A$1,ROW(),1)　と　して　Ctrl+Enter　入力なんてのは？

ヘルプには、１セルあたりの入力文字の制限なども仕様としてありますが
;^_^A

お礼

コメントありがとうございます。
早速試して見たのですが、CONCATENATE関数は最大30セルまでしか一つに結合できないようです。マクロはよく分からないので、なんとか関数で出来る方法を探しているのですが、、
あぁ日曜もまるつぶれ(T_T)