エクセルデータの改列の方法について

質問

エクセルデータの改列の方法について

エクセルデータでA列のセルに以下のように「aaa」と「スペース」で区切られた数列のグループを、
___A__B__
1|aaa|1
2|465|2
3|231|3
4|124|4
5|221|5
6|・
7|・　
・（数は不明）
・
10|3|84
11|スペース
12|aaa|1
13|556|2
14|245|3
15|111|4
16|232|5
17|・
18|・
・（数は不明）
・
28|2|78
29|スペース
30|aaa|1
31|255|2
・
・

以下のようにaaaを頭とする複数の列に分割するにはどうしたらいいでしょうか。

___A__B__C__D・・・
1|aaa|__|aaa|__・・・
2|465|1|556|1・・
3|231|2|245|2・・
4|124|3|111|3・・
5|221|4|232|4・・・
6| ・
7| ・

各ファイル50ブロックの数字の列があり、更にファイルも100以上ありますので、
贅沢ですが、できれば作業が少ない方が。。と思っています。

どなたか教えていただけたら幸いです。
よろしくお願いします。

回答No.3

keithin · ベストアンサー

ツールメニューのマクロのセキュリティを中にしてエクセルを再起動する
（Excel2007を使っていたときはExcelのオプションで行う）
空の新規ブックを一冊用意する
ALT+F11を押す
現れた画面で挿入メニューから標準モジュールを挿入する
現れたシートに下記をコピー貼り付ける

Sub macro1()
　Dim h As Range
　Dim s As String
　Dim r As Long

　'実際には「aaa」じゃないのは次の行を直す
　Set h = Cells.Find(what:="aaa", after:=Cells.SpecialCells(xlCellTypeLastCell), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows)

　If h Is Nothing Then Exit Sub
　s = h.Address
　r = Cells.SpecialCells(xlCellTypeLastCell).Row

　Do
　　Set h = Cells.FindNext(h)
　　If h.Address = s Then Exit Do
　　Range(h, Cells(r, h.Column + 1)).Insert shift:=xlShiftToRight
　Loop

　Cells.SpecialCells(xlCellTypeBlanks).Delete shift:=xlShiftUp
　ActiveWorkbook.SaveAs Filename:=Application.Substitute(ActiveWorkbook.Name, ".xls", "r1.xls")

　’マクロの動作が確認できたら，次の行の先頭の ' を消去する
'　activeworkbook.close false

End Sub

ファイルメニューから終了してエクセルに戻る
作業ブックをとりあえず保存し，まだ開いたままにしておく

さっきのブックは開いたまま，目的のブックをＷクリックして開き，目的のシートを開く
目的のシートを開いたまま，さっき登録したマクロを実行する
2003までならツールのマクロのマクロで。
2007ではExcelのオプションから開発タブを表示して開発タブのマクロで。

2003までのエクセルを使っているなら，ツールバーにユーザー設定ボタン（ピースマークのアイコン）を追加してマクロを登録してクリックして実行させる
Excel2007を使っているなら，画面上端のクイックアクセスツールバーのカスタマイズでマクロを登録してクリックして実行させる

お礼

ありがとうございます。
マクロ機能は初めてですが、おしえていただいた通り入力したところ、
一瞬で完了しました。
感激です。
ありがとうございました。

回答No.5

imogasi

#4です
imogasi方式（作業列を使う抜き出し問題の私の唱える１方法）の応用でやってみた。
例データＡ１以下　Ｂ列は作業列
aaa 1
1 1
2 1
3 1
4 1
4 1
5 1
6 1
aaa 2
s 2
d 2
f 2
g 2
h 2
aaa 3
23 3
45 3
23 3
Ｂ列の式は　=COUNTIF($A$1:A1,"aaa")
これを下方向に式複写
結果上記Ｂ列
ーー
Ｄ列Ｄ１に
=IF(ROW()>COUNTIF($B$1:$B$100,COLUMN()-3),"",INDEX($A$1:$A$100,MATCH(COLUMN()-3,$B$1:$B$100,0)+ROW()-1))
この式を下方向に式複写。aaaから次のaaaまでのデータの最多行数分（Ｘ行とする）＋アルファ行数を式複写する。
COLUMN()-3はＤ列から結果を出すやり方のためこうする。
他シートのＡ列から結果をだすことも式を少し修正すれば出来る。
Ｄ１：Ｄｘまでを範囲指定して、セル範囲の右下で＋ハンドルを出し、右方向に引っ張って式の複写をする。
結果
aaa aaa aaa
1 s 23
2 d 45
3 f 23
4 g
4 h
5
6
ーーー
シート上で空白と見えるセルも式が入っている結果であることはわかりますね。作業列を使うことと、これがいやなところ。

回答No.4

imogasi

こういうセルの組み換えの問題はaaaガ現れる行が不定の場合関数では複雑になる。
関数では苦戦するのは当たり前。
ＶＢＡでやるのが良い・出来ないとどうしようもないが。
下記は泥臭いやり方ではあるが、行数が少なく意味がわかりやすいかと
標準モジュールに
Sub test01()
d = Range("A65536").End(xlUp).Row 'Ａ列最終行捕そく
c = 2 'C列＝２＋１列から右に出す
For i = 1 To d
If Cells(i, "A") = "aaa" Then 'セルの値がaaaなら
c = c + 1 '列を右に移す
j = 1 '行を（Ｃ列の）第１行目に移す
Cells(j, c) = "aaa" 'Ｃ列（第１行目）にaaaセット
j = j + 1
Else
Cells(j, c) = Cells(i, "A") 'C列にデータをセット
j = j + 1 '次に備え1行下をポイント
End If
Next i
End Sub

お礼

お礼が遅くなり申し訳ありませんでした。
無事解決することができました。
教えていただいた方法もVBAの参考書と見比べながら解読してみました。
親切に教えていただき、ありがとうございました。

回答No.2

tom04

こんばんは！
参考になるかどうか判りませんが・・・
↓の画像で説明します。

作業用の列を1列設けています。

作業列C2セルに
=IF(A2="","",COUNTIF($A$2:A2,"aaa"))

という数式を入れ、オートフィルで下へずぃ～～～！っとコピーします。

そして、E2セルに
=IF(COUNTIF($C$2:$C$10000,INT(COLUMN(B1)/2))<ROW(A1),"",INDEX($A$2:$A$10000,SMALL(IF($C$2:$C$10000=INT(COLUMN(B1)/2),ROW($A$1:$A$9999)),ROW(A1))))

F2セルに
=IF(E2="","",INDEX($B$2:$B$10000,SMALL(IF($A$2:$A$10000=E2,ROW($A$1:$A$9999)),COUNTIF(E$2:E2,E2))))

どちらも配列数式になりますので、この画面からコピー＆ペーストしただけではエラーになると思います。
各セルに貼り付け後、F2キーを押す、または貼り付けセルでダブルクリック、または数式バー内で一度クリックします。
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定します。

そして、E2・F2セルを範囲指定し、2列毎に右方向にコピーしていきます。

最後に今コピーした行全てを範囲指定し、最後の列のフィルハンドルで下へコピーすると
画像のような感じになります。

尚、数式は10000行目まで対応できるようにしていますが、
データ量が多い場合は配列数式ですので表示されるまで時間がかかるかもしれません。

以上、参考になれば良いのですが
的外れなら読み流してくださいね。m(__)m

お礼

ありがとうございます。
早速参考にさせていただきながら取り組みましたが、E2とF2のセルの範囲設定がうまくいかず、試行錯誤中です。
もう少しがんばってみます。
画像もつけていただいて、ありがとうございます。

回答No.1

sandy_7729

aaaは記載されているものとして記載しますね。

マクロ登録→検索→「aaa」→ENTER
でaaaの位置がactiveになるはずです。
その行を右クリックして行を挿入してください
マクロ終了ボタンを押す

ＶＢＡ使えるんですかね？
上記のマクロを開き
右クリックした位置に関数を割り当てて、全体をループ文で構成してください。

出来ない場合は
マクロ登録→検索→「aaa」→ENTER→マクロ終了→行挿入
を複数回繰り返して下さい。

お礼

ありがとうございます。やはりマクロですよね。
勉強します。。

ちなみに、記載に間違いがありました。申し訳ありません。

__A__B__C__D・・・
1|aaa|1|aaa|1・・・
2|465|2|556|2・・
3|231|3|245|3・・
4|124|4|111|4・・
5|221|5|232|5・・・
6| ・
7| ・

でした。