エクセルのばらばらのセルを整列するには？

質問

エクセルのばらばらのセルを整列するには？

現在エクセルに　
郵便番号　郵便番号　
住所　　　　住所
氏名　　　　氏名
スペース　スペース
郵便番号　郵便番号
住所　　　　住所
氏名　　　　氏名　
スペース　スペース
郵便番号　郵便番号
住所　　　住所
氏名　　　氏名　
スペース　スペース
となっているんですが、これを
郵便番号、住所、氏名
郵便番号、住所、氏名
郵便番号、住所、氏名
郵便番号、住所、氏名
郵便番号、住所、氏名
のように横一列に自動で整形したいのですが、
どうしたらいいでしょうか？

回答No.9

layy · ベストアンサー

W = Cells(Y, X)
If W = "" Or Len(W) = 0 Then 　　　　　　　←　セルの値が何かあるかどうか
If J > 0 Then
I = I + 1
Print #1, I & "," & Chr(34) & J & Chr(34) & "," & Z　　　←　ＣＳＶファイルへ出力するコマンド、「＆」は文字連結　(2)
J = 0　　　　　　　　　　　　　　　　　　　　　　　　　　
Z = ""　　
End If
Else
J = J + 1
Z = Z & "," & Chr(34) & W & Chr(34)　　　　　　　　　　　←　退避させる処理　　(1)
End If

(1)において、
１セル目は：空っぽ””（＝Ｚ）とＡ１セル郵便番号の連結を行いＺへ退避
２セル目は：郵便番号（＝Ｚ）　とＡ２セル住所　の連結文字列を行いＺへ退避
３セル目は：郵便番号　と住所（＝Ｚ）　とＡ３セル氏名の連結文字列を行いＺへ退避

４セル目は：空白行であるため（(2)）、Ｚの内容を出力します。Ｚはクリアします。

これの繰り返しです。
Ｉは全体いくつの住所データが存在したかをカウントアップ、しています。（＝整合性チェックに使ってください。）
Ｊは有効な情報をいくつ連結させたかをカウントアップ、しています。

(1)の後に
Print #1, I & "," & Chr(34) & J & Chr(34) & "," & Z
を記載すると全体の動きが把握できるかと思います。

お礼

全部は分かりませんが、すこし何をやってるのか解りました。
有難うございました。

回答No.8

layy

対応例）
必要としている範囲のセルを１つずつ判定し、文字連結します。
必要なときに改行して出力。連結していた文字列はクリアします。

Public Sub AA()
Dim W As String
Dim Z As String
Dim I As Long
Dim J As Long
Dim X As Long
Dim Y As Long

I = 0
J = 0
Z = ""

Sheets(1).Select
Range("A1").Select
Open ThisWorkbook.Path & "\" & "編集結果.CSV" For Output As #1
For X = 1 To 3
For Y = 1 To 200
W = Cells(Y, X)
If W = "" Or Len(W) = 0 Then
If J > 0 Then
I = I + 1
Print #1, I & "," & Chr(34) & J & Chr(34) & "," & Z
J = 0
Z = ""
End If
Else
J = J + 1
Z = Z & "," & Chr(34) & W & Chr(34)
End If
Next Y
If J > 0 Then
I = I + 1
Print #1, I & "," & Chr(34) & J & Chr(34) & "," & Z
End If
Next X
Close #1

MsgBox ("完了")

End

End Sub

－－－－－－－－－－－－－－－－－
For X = 1 To 3
For Y = 1 To 200
の「３」はＡ列（Ａ１に対する１番目）からＣ列（３番目）を意味します。
「２００」は同じ考えで行。
Chr(34)は「”」のことです。

ブックと同じフォルダに"編集結果.CSV"を作ります。
1,"4",,"郵便","住所","住所２","氏名"
2,"3",,"郵便","住所","氏名"
3,"3",,"郵便","住所","氏名"
こんな感じで出来上がります。
順、項目数、その内容。項目数列が４のときは住所が２つとかになる想定です。
見出しはありません。

できるだけ簡単なコマンドを使っています。
処理ロジックは理解していただきたくお願いします。
（体裁は勘弁してください。）

※関数を使うとほとんどが決まった動きしかしないので
パターン変わると使えないこと多いです。

間に空白行２行あっても大丈夫です。

お礼

項目中に空白があっても改行しない処理はどこでやっているのでしょうか？

補足

有難うございます。
動かすと、全体で項目が４つそろっていると上手く縦から横にならびますが、
４項目中3項目、住所が１個だけとかだと、
改行されて出力されてしまいます。
参考にさせて頂き考えてみます。

回答No.7

layy

このズレだと空白から空白が１行分、または郵便番号から郵便番号が１行分という見方になりますので回答例にある３行セットの考え方が使えなくなりそうです。でもできないこともないです。空白はそのままでよいでしょう。例外はこれくらいでしょうか。少し時間下さい。

回答No.6

layy

補足）

＞スペースの量が不規則なのでそれを正してから出ないとだめですよね？
これだと不規則なところもなんとかしてね、と解釈されてしまいがち。
本題は縦２列情報を横情報にする事なので、
揃っている前提でその先を教えてほしい、というところが本来の筋ではないでしょうかね？。（＝空白行不規則なところを揃えるのは別の質問でもいい話では？）

今回の質問を見ると、
コピーの貼り付けで「行と列を逆にして貼り付け」を繰り返せば手作業でたぶんできます。
またその時にコピーの範囲が３行×３列ずつとか一定で決まっていると
範囲セル位置が変わっていくだけなので、マクロ化対応が可能になってきます。

不規則な空白行があるかどうかは重要なポイントなのです。

郵便番号、住所、氏名、かならず３行そろっていること、もそうです。
住所、氏名、だけというパターンがあっては自動化は困難になってきます。

補足

申し訳ありません。
空白を消してみると、A列とB列が違ってしまうのです。

郵便番号　郵便番号　
住所　　　　住所
住所２　　　氏名
氏名　　　郵便番号
郵便番号　　住所　
住所　　　　氏名
氏名

A列B列が同じ行で揃うように自動で揃う処理をしなければならないです。。

今回はむずかしいでしょうか・・・

回答No.5

tom04

こんばんは！
一気に！という方法ではないので、参考になるかどうか判りませんが・・・
↓の画像で説明させていただきます。
2段階でまとめるようにしてみました。

まず、A・B列の空白を無視して、D・E列に表示させます。
それをG～I列に表示するようにしてみました。

D1セルに
=IF(COUNTA(A$1:A$1000)<ROW(A1),"",INDEX(A$1:A$1000,SMALL(IF(A$1:A$1000<>"",ROW($A$1:$A$1000)),ROW(A1))))

これは配列数式になってしまいますので、この画面から直接D1セルにコピー＆ペーストしただけではエラーになると思います。

D1セルに貼り付け後、F2キーを押す、又はD1セルでダブルクリック、又は数式バー内で一度クリックします。
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定します。
数式の前後に｛　｝マークが入り配列数式になります。
このD1セルをとなりのE1セルまでオートフィルでコピーし、そのまま下へオートフィルでコピーします。

そして、G1セルに
=INDEX($D$1:$E$1000,INT(ROW(A2)/2)*3+COLUMN(A1)-3,IF(MOD(ROW(A1),2)=0,2,1))&""
（これは配列数式ではありません）
という数式を入れ、I1セルまでコピーし、そのまま下へコピーすると
画像のような感じになります。

最後にG～I列全てを範囲指定し　→　コピー　→　「形式を選択して貼り付け」　→　好みの位置に貼り付け
他の列を削除して完了です。
尚、数式は元データの1000行目まで対応できるようにしています。

以上、長々と書きましたが
参考になれば幸いです。m(__)m

回答No.4

noname#204879

[回答番号：No.3]への補足、

後で気付いたことですが、空白行数が一定ではないのですね。
それなら、先に空白行だけを一気に削除（これは簡単にできます！）しておくことをお薦めします。

回答No.3

noname#204879

添付図参照
D1: =OFFSET(A$1,(ROW(A1)-1)*2,)
D2: =OFFSET(B$1,(ROW(A1)-1)*2,)
E1: =OFFSET(A$2,(ROW(A1)-1)*2,)
E2: =OFFSET(B$2,(ROW(A1)-1)*2,)
F1: =OFFSET(A$3,(ROW(A1)-1)*2,)
F2: =OFFSET(B$3,(ROW(A1)-1)*2,)
範囲 D1:F2 を下方にズズーッとドラッグ＆ペースト

回答No.2

keithin

んでは，簡単な数式だけで出来る別の手で。

手順：
Ａ列のデータをDEF列に並べます。
D1に
=IF(ISERROR(E2),NA(),A1)
E1に
=IF(ISERROR(F2),NA(),A1)
F1に
=IF(AND(A1<>"",A2=""),A1,NA())
を記入，D1:F1を選んで下向けにつるつるっとオートフィルドラッグ。

D:F列を列選択
Ctrl+Gを押し
現れたダイアログでセル選択のボタンをクリックして
現れたダイアログで数式のエラー値を選んでOKし
すかさず編集メニューの削除で上に詰めます。
（一番下にゼロが幾つか並んだのは，手で削除してください）

Ｂ列についても同様にHIJ列に並べ替えて，あとは合わせてください。
並べ替えたあと全体をコピーし，その場で型式を選んで貼り付けの「値」で貼り付けて結果を値に変えてやれば，コピーも移動も自由です。

＃更に別の手
オートフィルタなどを使って空白行を全て除去してデータを上に詰め，先の回答の数式を少し自力で改修していただいても出来ます。
データを一度WORDにコピーして，置換の機能を使って「何個かまとまった改行（空白行）」を「１行の空白行」に置換（実際には段落記号３連続を段落記号２連続に置換，を数回繰り返すなど）して元のご相談に掲示されたスタイルのデータに整備し，それをエクセルに貼り戻して前回の数式そのままを使うといった方法もあります。
どんなやり方でも結果が出れば勝ちですから，色々手を動かしてみてください。

＃それで。
＞たぶん、下のスペースの量が一定でないからだと思います。

何て言うか，とっても「ひとごと」みたいですね。それでそんな具合に「実はこうなんです，ほんとはこうだったんです」と後出しされたら，出来なくて当たり前ですし回答する側も「またどうせ違うんじゃない？」と，マジメに回答するだけ無駄じゃない？とも思っちゃいますよね。
とは言え，今回のご質問に関して言えば最初の例示もあまりに「簡単に書かれすぎている」ので，どうせ実際は違うんだろうなこのままじゃ出来ないだろうなと，１回無駄にする気持ちでこれで出来たらむしろお互いラッキーと思って回答してましたから特に構いません。少なくとも，ご質問を投げっぱなしでそのままどこかにトンズラされちゃう方では無かった事だけでも，判って良かったです。

補足

＞とっても「ひとごと」みたいですね
申し訳ありません。
先ほどの関数が難しくて、それが動かない理由はスペースが一定じゃないからかなという意味です。

後出しになって申し訳ないのですが、整列などの手動操作なしで、自動で行いたいんです。
スペースの量が不規則なのでそれを正してから出ないとだめですよね？
それをスペース量を自動でパターン化する方法をとったあと、例の数式をつかえばいいのでしょうか？

回答No.1

keithin

ＡＢ列にその格好でデータがあります。
=INDEX($A:$B,INT((ROW(A1)-1)/2)*4+COLUMN(A1),2-MOD(ROW(A1),2))
と式を入れて，右にあと２つ，そして下にコピーするとできます。

補足

=INDEX($A:$B,INT((ROW(A1)-1)/2)*4+COLUMN(A1),2-MOD(ROW(A1),2))
むずかしい式ですね。。
最初の２個までは綺麗に揃いましたが、それ以上は上手く揃いません。
たぶん、下のスペースの量が一定でないからだと思います。

郵便番号　郵便番号　
住所　　　　住所
氏名　　　　氏名
スペース　スペース
郵便番号　郵便番号
住所　　　　住所
氏名　　　　氏名　
スペース　スペース
スペース　スペース
郵便番号　郵便番号
住所　　　　住所
氏名　　　　氏名　とか・・・