[[20170408223428]]　『セル内改行データの分割とデータの並び替え方法』（くまくま）　ページの最後に飛ぶ

『セル内改行データの分割とデータの並び替え方法』（くまくま）

エクセル初心者です。
既成のエクセル表のデータを並べ替える方法をご教示頂きたく存じます。

元データ表(B列、C列は一つのセルに改行で複数のデータが入っております。）

A列　 B列　　　　C列
1 　A,B,C 　　　　　a,b
2 　A,B 　　　　　 a,c,d,e
3 　A,B,C,D,E 　　　　　 e

成形後データ表（タイプ1）
A列　　　　　　B列　　　　　C列
1　　　　　　 A 　　　　　　 a

                 B                b
                 C
2                A                a
                 B                c
                                  d
                                  e
3                A                e
                 B
                 C
                 D
                 E

成形後データ表（タイプ2）

A列　　　　　　B列　　　　　C列
1　　　　　　　A　　　　　　　 a

                 A                 b
                 B                 a
                 B                 b
2                A                 a
                 A                 c
                 A                 d
                 A                 e
                 B                 a
                 B                 c
                 B                 d
                 B                 e
3                A                 e
                 B                 e
                 C                 e
                 D                 e
                 E                 e

元データを成形後データ表のように並べ替えたいと考えております。
改行で一つのセルにまとめて入っているデータをセル分割して、縦にする方法、（タイプ1）
また、A列のIDごとにB列とC列の全組み合わせパターンを作成する方法（タイプ2）はありますでしょうか。
関数またはマクロでなるべく手作業がない方法を探しています。
区切り位置、関数や並び替えを使用して何とか作成することはできましたが、元データが2000行あり、自身の方法では途中作業用の行の挿入等で数万行程を使用し、エクセルの処理にものすごく時間がかかった為、ご相談させて頂きました。
ご教示のほどよろしくお願い致します。

＜使用 Excel：Excel2013、使用 OS：Windows8 ＞

 >くまくま さん

 >改行で一つのセルにまとめて入っているデータを
 >セル分割して、縦にする方法（タイプ1）

 Sheet1（元データ）から、Sheet2（データ転記先）に、
 データ（値のみ）を転記しています。
 とりあえず、タイプ１だけを処理するコードを
 下記に記述します。

 >元データが2000行あり
 下記のコードを使用した場合の処理時間は、
 どのくらいでしたか？

 ■画像
http://imgur.com/42urqfi

 Moduleに、次のコードを記述してください。
 '==================================
 Option Explicit

 Sub Test()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim fr As Long, x As Variant
  Dim v1 As Variant, v2 As Variant, max As Long
  Dim c As Long, i As Long, j As Long

  Set sh1 = ThisWorkbook.Sheets("Sheet1") 'データ元
  Set sh2 = ThisWorkbook.Sheets("Sheet2") 'データ転記先

  ReDim z(1 To sh1.Rows.Count, 1 To 3) As Variant
  fr = sh1.Cells(sh1.Rows.Count, "B").End(xlUp).Row
  x = sh1.Range("A1:C" & fr).Value

  For i = 1 To fr
    ReDim vB(1 To 32767) As Variant 'セルに入る最大文字数は、32767
    ReDim vC(1 To 32767) As Variant
    v1 = x(i, 2) & vbLf '語末に改行を追加
    v2 = x(i, 3) & vbLf '語末に改行を追加
    Call GetArray(v1, v2, vB, vC, max)

    For j = 1 To max
      c = c + 1
      If j = 1 Then z(c, 1) = x(i, 1)
      z(c, 2) = vB(j)
      z(c, 3) = vC(j)
    Next j
    Erase vB: Erase vC
  Next i

  With sh2
    .Cells.Clear '一旦消去
    .Range("A1:C1").Resize(c).Value = z 'データを転記
    .Cells.ColumnWidth = 7 '列幅の設定
    .Cells.Font.Bold = True '太字
  End With
  MsgBox "転記終了"
  Set sh1 = Nothing: Set sh2 = Nothing
 End Sub
 Private Sub GetArray(ByVal v1 As Variant, _
                     ByVal v2 As Variant, _
                     vB As Variant, _
                     vC As Variant, _
                     ByRef max As Long)
  Dim p As Long, q As Long, n As Long
  p = 0: q = 0
  Do While InStr(v1, vbLf) <> 0 '改行が存在するなら
    p = p + 1: n = InStr(v1, vbLf)
    vB(p) = Left(v1, n - 1)
    v1 = Mid(v1, n + 1, Len(v1) - n + 1)
  Loop
  Do While InStr(v2, vbLf) <> 0 '改行が存在するなら
    q = q + 1: n = InStr(v2, vbLf)
    vC(q) = Left(v2, n - 1)
    v2 = Mid(v2, n + 1, Len(v2) - n + 1)
  Loop
  If p >= q Then
     max = p
  Else
     max = q
  End If
 End Sub
 '==================================

（マリオ） 2017/04/09(日) 06:38

 マリオさんのサービス精神盛りだくさんのコードの後ですが、組替転記のみならこんな程度でも。
 できあがりの見栄え含めた化粧直しは、質問者さんのほうで、いかようにでも。

 Sample1 が　タイプ1、Sample2 が　タイプ2 です。
 いずれも　Sheet1 が元シート、組替は Sheet2 に。

 Sub Sample1()
    Dim v As Variant
    Dim c As Range
    Dim i As Long
    Dim w As Variant
    Dim d As Variant
    Dim x As Long
    Dim n(1 To 2) As Long

    Dim n1 As Long
    Dim n2 As Long

    With Sheets("Sheet1")
        ReDim v(1 To .Rows.Count, 1 To 3)
        For Each c In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
            v(i + 1, 1) = c.Value
            For x = 1 To 2
                w = Split(c.Offset(, x).Value, vbLf)
                n(x) = i
                For Each d In w
                    n(x) = n(x) + 1
                    v(n(x), x + 1) = d
                Next
            Next
            i = WorksheetFunction.Max(n(1), n(2))
        Next
    End With

    With Sheets("Sheet2")
        .Cells.ClearContents
        .Range("A1").Resize(i, UBound(v, 2)).Value = v
        .Select
    End With

 End Sub

 Sub Sample2()
    Dim v As Variant
    Dim c As Range
    Dim i As Long
    Dim w1 As Variant
    Dim d1 As Variant
    Dim w2 As Variant
    Dim d2 As Variant

    With Sheets("Sheet1")
        ReDim v(1 To .Rows.Count, 1 To 3)
        For Each c In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
            v(i + 1, 1) = c.Value
            w1 = Split(c.Offset(, 1).Value, vbLf)
            w2 = Split(c.Offset(, 2).Value, vbLf)
            For Each d1 In w1
                For Each d2 In w2
                    i = i + 1
                    v(i, 2) = d1
                    v(i, 3) = d2
                Next
            Next
        Next
    End With

    With Sheets("Sheet2")
        .Cells.ClearContents
        .Range("A1").Resize(i, UBound(v, 2)).Value = v
        .Select
    End With

 End Sub

（ β） 2017/04/09(日) 08:49

マリオ様
早急なご回答誠にありがとうございます。
教えて頂きました方法を2000行あるデータで試しましたが、3秒でsheet2への転記が完了致しました。
自分では一日がかりの大変な作業でしたので本当に感激いたしました。
本当にありがとうございます。
マクロは全く未経験者ですが、マクロの凄さに衝撃を受け、今日から勉強することに致しました。
マリオ様から教えて頂いたコードを解読できる様になりたい。。
また、掲示板で質問させて頂くことがあるかと思いますが、よろしければどうぞまたご教示の程宜しくお願い致します。
本当にありがとうございます！

（くまくま） 2017/04/09(日) 11:13

β様
早速のご回答誠にありがとうございます。
タイプ1とタイプ2の両方のバージョンを教えて頂き大変ありがとうございます。
タイプBにつきましては自身で頭を抱えていた部分でありましたので、マクロで一瞬でsheet2に転記された時には驚愕でした。
もしよろしければご教示頂きたいのですが、
元データは実際2000行ほどございまして、今後も最終行に追加されていくデータ表でございます。
β様から頂いたコードで3行以降も転記する為にはどの部分を変更したらよろしいしょうか。
本来なら自分で調べるべきですが、明日の仕事で使わせて頂きたいと考えており、ヒントだけでも
頂けますと大変幸甚でございます。
素人考えで
ReDim v(1 To .Rows.Count, 1 To 3)の to 3　の部分を　to 2000　に変更すればと思いましたが、
やはり無理でした。
どうかよろしくお願いいたします。
（くまくま） 2017/04/09(日) 11:24

 >タイプB

 タイプ2 のことですね。

 >3行以降も転記する

 Sample1 のことなのか、Sample2 のことなのか、不明ですが、いずれであっても、
 元シートの A列にあるデータの最終セルの行まで、データが存在する限り Sheet2 に展開しているつもりです。

 たとえば　元シートに　10行目まであるのに、2行目までしか展開されないということでしょうか？
 それは考えられないのですが？

 >ReDim v(1 To .Rows.Count, 1 To 3)

 これは、マリオさんのコードでも同様なんですが、最終的に Sheet2 に書きこむイメージを、そっくり配列に納めるために
 その配列を初期化しているところですが、行数として、1～1048576 と、エクセルで許容されている最大行数分を確保しています。
 1 To 3 は　列数　です。　つまり、A～C の 3列分ということなんですが・・

（β） 2017/04/09(日) 12:12

 >くまくま さん
 『Sheet1』『Sheet2』『Sheet3』
 の3シートを用意して、
 『Sheet1』は、元データを入力するシート

 『Sheet2』は、βさんのSub Sample1()を使って
 転記する転記先シートとして使用する。
 『Sheet2』はタイプ1専用にする。

 『Sheet3』は、βさんのSub Sample2()を使って
 転記する転記先シートとして使用する。
 『Sheet3』はタイプ2専用にする。
 ★なお、βさんのSub Sample2()のコードの
 With Sheets("Sheet2")の部分を
 With Sheets("Sheet3")に修正する！

 >βさん
 Split
 と
 WorksheetFunction.Max
 をイメージできなかった為、
 ｺｰﾄﾞが長くなってしまいました。
 まだまだ、未熟です。
 勉強になりました(=^・^=)
（マリオ） 2017/04/09(日) 12:57

＞β様
大変失礼致しました。sample1,sample2の両方共2000行全て転記できました。
マクロ実行の操作方法時にちゃんと実行できていなかったのだと思います。
素人が早とちりしまして、ご説明のお手間を取らせてしまいました。申し訳ありません。大変失礼致しました。
今勉強真っ只中ですが、1 To 3 は　列数を仰っておられたのですね。。お恥ずかしい限りです。
すごいです！！ありがとうございます！！

＞マリオ様
アドバイスありがとうございます。
コードの修正部分まで教えて頂きまして大変恐縮です。
sheet2にタイプ1、sheet3にタイプ2となんとも贅沢な方法ができました！

お二方には本当に助けて頂きました。
ありがとうございました!！

（くまくま） 2017/04/09(日) 13:51

 >βさん

 当然、おわかりのことですので、恐縮ですが…、

 Sub Sample1()
 で、
 For Each c In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
 として、Sheet1の値取得を★何度も繰り返してますね。

 例えば、
 Dim x As Variant
 x=.Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value
 などとすれば、
 Sheet1の値取得を★1回で済ませられるので、
 処理を★さらに速くすることができますね。
 Sheet1のデータが、100万行、1万列なら、
 βさんも、そのようにするでしょうが…。
 今回は、2000行、3列で、割とﾃﾞｰﾀ数が少ないので、
 そのようにしてないだけですよね…。

 ベータさんの処理は、★D列、E列、F列、…と増えても、
 少しｺｰﾄﾞを修正するだけで対応できるで、優れてますね(*^_^*)

（マリオ） 2017/04/09(日) 14:48

 マリオさん

 その昔、私も、ある回答者さんのコードに対して同様のことを申し上げたことがあります。
 ループのつど、値を動的に取得するのは、効率が悪い　と。

 思い返すたびに赤面です。

 For/Next 構造で　限界値の取得、これは　最初の１回だけなんです。
 逆に、ループ内で　この限界値を変更しても、ループは、最初の限界値しか見ていません。
 また、コレクションから値を取り出す場合も同様です。

 以下を試してみてください。

 Sub Test0()
    Dim n As Long
    Dim x As Long

    n = 5

    For x = 1 To n
        MsgBox x
        n = 1000
    Next

 End Sub

 Sub Test1()
    Dim c As Range

    Cells.Clear
    Range("A1:A5").Value = "hoge"

    For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
        MsgBox c.Address
        Range("A5").Clear
    Next

 End Sub

 Sub Test2()
    Dim r As Range
    Dim c As Range

    Set r = Range("A1:A5")

    For Each c In r
        MsgBox c.Address
        Set r = Range("F1:F10")
    Next

 End Sub

 ★ついでといってはなんですが・・・・

 マリオさんのコード、動的配列に対して　Erase vB: Erase vC

 これは何の目的で記述していますか？（まぁ、想像はできますが）
 それと、この結果 vB や vC は、どうなると考えていますか？（ローカルウィンドウ等で確認すると、何か発見できるかも）

 Sub Test3()
    Dim v1 As Variant
    Dim v2(1 To 5, 1 To 3)

    ReDim v1(1 To 5, 1 To 3)

    Erase v1
    Erase v2

    Stop    'ここでローカルウィンドウを見てください

 End Sub

（β） 2017/04/09(日) 15:24

β様
教えて頂いたコードを少しでも理解したく解読しておりますが、どうしてもわからない部分があり
ご教示いただけないでしょうか。マリオ様との会話中に申し訳ございません。

sample1の　With Sheets("Sheet1")　部分にて

For Each c In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
⇒ここはA1からA3（A列のデータが入っている最終行）の範囲でその下に書かれている処理を繰り返す。のような
　意味かなと考えております。

v(i + 1, 1) = c.Value
⇒v(i + 1, 1)にc(A列のセル内)の値を代入する？と読み取ったのですが、
　この i はどのような動きをするのでしょうか。この後にも

n(x) = i
i = WorksheetFunction.Max(n(1), n(2))

とiについての記述が出てきますが、上から下に順にコードが読まれる認識でおりまして、
v(i + 1, 1) = c.Value　の i がわからないことにはその後の i も何なのか決められないのかなと思っています。
わかりにくい説明と勉強し始めたばかりの基礎を身に付けていない状態での質問で大変申し訳ないのですが、
解説いただけないでしょうか。上記の認識が全くの的外れでしたら、まだ自分にはレベルが高すぎるものと
諦めますのでご指摘いただけますと有り難いです。

どうか宜しくお願い致します。

（くまくま） 2017/04/09(日) 19:36

 このトピは、あくまで　くまくまさんの質問トピですから、回答者間のやりとりなんて無視していただいていいですよ。
 （もちろん、参考になる部分があれば、役立ててください）

 で、質問の件、わかりやすく説明ができるかどうか、心もとないですが。

 >For Each c In .Range("A1", .Range("A" & Rows.Count).End(xlUp))

 はい。くまくまさんの解釈でＯＫです。

 ある領域からセルを１つずつ　変数 c に取り出しています。
 ある領域とは　.Range(開始セル,終了セル)
 開始セルはいいですね。 A1 です。
 終了セルが　.Range("A" & Rows.Count).End(xlUp) 
 これは　A1048567 のセルから上を眺めて、最初に値があるセル。　つまり A列の最後のデータのセルということになります。

 >v(i + 1, 1) = c.Value

 v という変数は、中身が　縦 1048576行、横　3列の縦横のマス目に区切られたメモリー。
 ２次元配列　と呼んでいます。

 v(4,2) と指定すると、配列内の 4行目の2列目の場所　ということになります。

 変数 i は Dim i As Long で規定していますが、この規定段階で　値は 0 になっています。
 コード内では、配列内にセット済みの行番号を i にいれています。
 最初は 0 ですから　v(i + 1, 1) は v(0 + 1, 1) つまり v(1, 1) になります。
 あるとき、配列内に　20行目までセットしていたとします。
 そうすると、次にセットすべきところは　21行目ですよね。 i はセット済みの行数 20 になっていますので
 v(i + 1, 1) は v(20 + 1, 1) つまり v(21, 1) になります。

 >n(x) = i 
 >i = WorksheetFunction.Max(n(1), n(2))

 A列に相当する配列内 1列目のセットは↑でのべたようなからくりで制御していますが　B列,C列（配列内の 2列目、3列目）をどう制御するか。
 いろんな方法がありますが、私がアップしたコードでは、直前の　A列の番号に対して、分解されて配列内にセット済みの行数を　変数 i で保持していると申し上げました。
 それが 20 であれば、これからの A列の番号に対して分解してセットする行は 21行目から　ということになりますね。
 しかも、B列、C列（配列内の2列目、3列目）にセットするためには、それぞれ、独立した２つの変数で制御することが必要になります。
 変数1 と　変数2 といったように　変数を2つもってもいいのですが、私のコードでは、これら２つの変数も
 ２つの要素を持った１次元配列にしています。n(1) と n(2) です。

 ループで For x = 1 To 2 として、最初は n(x) つまり n(1) という変数、２回目は n(x) つまり n(2) という変数を相手にしています。
 最初に、この　n(x)  これは　n(1) であったり n(2) であったりするわけですが、そこに　これまでセット済みの行数 i つまり 20 を入れます。
 で、１行セットする前に n(x) = n(x)+1 として、21,22,23 と増やしていきます。

 v(n(x),x+1) 、x は 1 か 2 ですね。
 1　の場合は v(n(1),1+1) つまり v(21,2) といったように　配列内の 2列目の所定の行のマス目に値をいれます。
 2　の場合も同様、 n(x) つまり n(2) に　20 をいれておいて、１行セットする前に1つ増やして 21,22,23・・・
 v(n(x),x+1) は v(n(2),3) ですから　配列内の3列目の所定の行に値をセットします。

 こうして　A列の特定の番号に関する分解セットがおわれば、2列目の最後の行番号は n(1) に、
 3列目の最後の行番号は n(2) に入っていますね。

 さて、2列目、3列目　両方を見たときの最後の行番号は？
 n(1) と n(2) の、いずれか大きいほうですね。
 ですからシート関数の MAX を使って、そのいずれか大きいほうを 変数 i にいれ、次の展開に備えます。

 わかりにくいですかね・・・？

 わからなければ遠慮なく、つっこんで聞いていたたいてＯＫです。

（β） 2017/04/09(日) 20:50

β様
とても詳しくわかりやすいご説明誠にありがとうございます！
これほどまでにご丁寧に解説していただき感謝感激でございます。

＞変数 i は Dim i As Long で規定していますが、この規定段階で　値は 0 になっています。
⇒これはLong(長整数型）で変数を宣言した場合かつ、その後特に i の範囲を明示的に規定してないので　0 という
ことでしょうか。

ご教示頂いたことから、以下のように読み進めて見ましたが、認識が合っているかご確認いただけないでしょうか。まずは以下例で考えてみたいと思います。

元データ
A列　 B列　　　　C列
1 　A,B,C 　　　　　a,b
2 　A,B 　　　　　 a,c,d,e
3 　A,B,C,D,E 　　　　　 e

With Sheets("Sheet1")
ReDim v(1 To .Rows.Count, 1 To 3)
⇒変数vはA列、B列、C列の範囲をとる（行は一番下まで）

For Each c In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
⇒ A1からA列のデータが入っている最終セルを順に変数cに取り出す

v(i + 1, 1) = c.Value
⇒最初は i　は　0　なので　v(i + 1, 1)はv(0 + 1, 1)なのでv(1, 1)となり、A1セルを表す。
　A1セルに　c　の値（一番最初はA1 ）1を代入する。

　For x = 1 To 2
　⇒変数 x は　1　から　2　の値をとる

　w = Split(c.Offset(, x).Value, vbLf)
　⇒　c　（今はA1）を列 x （今は1）だけ移動したセル（B1)の値を改行（alt enter)で分割して　変数wに代入　

　n(x) = i
　⇒　x は　1　で　i は　0 　なので　n(1)=0

　　For Each d In w
　　⇒　W（先ほど分割したB1セルの値（A,B,C）を順に変数dに取り出す　

　　n(x) = n(x) + 1
　　⇒n(1)は先程　n(1)=0だったので　n(1) = 0 + 1　で　n(1) = 1

　　v(n(x), x + 1) = d
　　⇒ v(1,2)にd(今はB1セルの1個目の値 A）を代入

 　 Next
　　⇒For Each d In w　の所から　wの数の分繰り返す。
　　　今は　n(1) = 1　なので、n(1) = n(1) + 1　で　n(1) = 1 + 1 で　n(1) = 2　となり
　　　v(2,2)にd(今はB1セルの2個目の値 B） を代入
　　　またFor Each d In w　の部分から繰り返して
　　　v(3,2)にd(今はB1セルの3個目の値 C） を代入
　　　Wの数の分繰り返したので抜ける

 　Next
   ⇒For x = 1 To 2　から繰り返す。次はx = 2
  w = Split(c.Offset(, x).Value, vbLf)
　⇒　c　（今はA1）を列 x （今は2）だけ移動したセル（C1)の値を改行（alt enter)で分割して変数wに代入
  n(x) = i
　⇒　x は　2　で　i は　0 　なので　n(2)=0   
　　
　　For Each d In w
　　⇒　W（先ほど分割したC1セルの値（a,b）を順に変数dに取り出す

　　n(x) = n(x) + 1
　　⇒n(2)は先程　n(2)=0だったので　n(2) = 0 + 1　で　n(2) = 1

　　v(n(x), x + 1) = d
　　⇒ v(1,3)にd(今はC1セルの1個目の値 a）を代入

 　 Next
　　⇒For Each d In w　の所から　wの数の分繰り返す。
　　　今は　n(2) = 1　なので、n(2) = n(2) + 1　で　n(2) = 1 + 1 で　n(2) = 2　となり
　　　v(2,3)にd(今はC1セルの2個目の値 b） を代入
　　　Wの数の分繰り返したので抜ける

     i = WorksheetFunction.Max(n(1), n(2))
     ⇒  n(1)が3, n(2)が2なので、大きい方3を i に代入

  ⇒For Each c In .Range("A1", .Range("A" & Rows.Count).End(xlUp))から繰り返し
⇒  A1からA列のデータが入っている最終セルを順に変数cに取り出す

v(i + 1, 1) = c.Value
⇒今 i　は　3　なので　v(i + 1, 1)はv(3 + 1, 1)なのでv(4, 1)となり、A4セルを表す。
　A4セルに　c　の値（二週目なのでA2 ）2を代入する。

　For x = 1 To 2
　⇒変数 x は　1　から　2　の値をとる

　w = Split(c.Offset(, x).Value, vbLf)
　⇒　c（今はA2）を列 x （1）だけ移動したセル（B2)の値を改行（alt enter)で分割して変数wに代入　

　n(x) = i
　⇒　x は　1　で　i は　3 　なので　n(1)=3

　　For Each d In w
　　⇒　W（先ほど分割したB2セルの値（A,B）を順に変数dに取り出す　

　　n(x) = n(x) + 1
　　⇒n(1)は先程　n(1)=3だったので　n(1) = 3 + 1　で　n(1) = 4

　　v(n(x), x + 1) = d
　　⇒ v(4,2)にd(今はB2セルの1個目の値 A）を代入

 　 Next
　　⇒For Each d In w　からw の分だけ繰り返し
　　　v(5,2)にd(今はB2セルの2個目の値 B） を代入

 　Next
   ⇒For x = 1 To 2　から繰り返す。次はx = 2
　　　同じように進み、C2 セルの値を分割してC4,C5,C6,C7セルにa,c,d,eが入る

     i = WorksheetFunction.Max(n(1), n(2))
     ⇒  n(1), n(2)で、大きい方を i に代入

  ⇒For Each c In .Range("A1", .Range("A" & Rows.Count).End(xlUp))から繰り返し
   以下省略

End With

このような考え方であっていますでしょうか。。
長くなってしまい申し訳ありません!大変お手数ですが、誤認識あればご指摘頂きたく存じます。
どうぞよろしくお願い致します。

（くまくま） 2017/04/10(月) 00:03

 最初の配列定義のことに関してのみ整理しておきますね。

 元データ

 A列　 B列　 　　　C列 
 1 　A,B,C 　　　　　a,b 
 2 　A,B 　　　　　 a,c,d,e 
 3 　A,B,C,D,E 　　　　　 e

 With Sheets("Sheet1") 
 ReDim v(1 To .Rows.Count, 1 To 3) 
 ⇒変数vはA列、B列、C列の範囲をとる（行は一番下まで）

 まず、配列なんてものを使わなくても、シート上の行、列に区切られた各セルの領域、
 これそのものが、『配列のようなもの』ですから、そこに直接書き込んでもいいわけです。

 でも、エクセル処理の中で、きわめて負荷が大きいのもの１つが　セルへの値書き込みです。
 書き込むセル数にも、若干は影響されますが、基本的には　『書き込む回数』がポイントになります。

 たとえば A1:C100 といった領域に　１セル毎書きこみますと　300回。
 一方、書きこむべき300セルの内容をメモリー内の配列に格納しておいて、一挙に書きこむと『1回』。

 このために、今回の処理では　配列を使いました。

 そうすると、この結果を収めるためには、どれだけのサイズの配列が必要かということになります。
 メモリーを節約するために、できるだけ無駄なサイズになるのを避けたほうが好ましい。
 たとえば、上記サンプルデータを加工したものを、そのままのサイズで転記するということであれば
 3行の配列になりますね。
 こういった場合は、A列のデータ最終行を

    With Sheets("Sheet1")
        必要行数 = .Range("A" & Rows.Count).End(xlUp).Row
    End With

 あるいは

    With Sheets("Sheet1")
        必要行数 = .Range("A1").End(xlDown).Row
    End With

 このようにして取得します。
 前者は、A1048567 のセルを選択して Ctrl/↑　を押す行為、後者は A1 を選択して Ctrl/↓　を押す行為です。

 しかしながら、今回のテーマでいえば、タイプ1 であれば 1 行目のデータに関しては 3行、タイプ2 であれば　6行（3 x 2) 必要です。
 これが何行もあるわけですから、最終的な行数を把握するには、行全体を見たうえで合計値をとることが必要ですね。
 そうしてもいいのですが、行数を取得するコードが煩雑になってしまう。

 なので、『横着』に、行数を　エクセル最大行数である 1048576 行　にしています。（1 から Rows.Count つまり 1048576)
 列数が多ければ、この宣言をしたとたんにメモリーオーバになる可能性があるんですが 必要なのは 3列と決まっていて
 3列程度なら、横着な行数規定でも問題ないので。結果としては、配列の下のほうは未使用になりますが、最後に転記する際には
 実際に配列に書きこまれた行数分のみの転記にして、転記効率悪化を防いでいます。

 それが　.Range("A1").Resize(i, UBound(v, 2)).Value = v　の部分です。

（ β） 2017/04/10(月) 08:50

 引き続き、それ以降の確認に対するコメントです。

 For Each c In .Range("A1", .Range("A" & Rows.Count).End(xlUp)) 
 ⇒ A1からA列のデータが入っている最終セルを順に変数cに取り出す

 ●はい。その通りです。A列データ最終セルについては、これまで申し上げた通り
 .Range("A" & Rows.Count).End(xlUp) を使っています。

 v(i + 1, 1) = c.Value 
 ⇒最初は i　は　0　なので　v(i + 1, 1)はv(0 + 1, 1)なのでv(1, 1)となり、A1セルを表す。 
 A1セルに　c　の値（一番最初はA1 ）1を代入する。

 ●はい。できあがりイメージの配列を転記結果のシートになぞらえれば　その通り A1セルです。
 ただし、正確な表現としては 『配列内の A1 のいあたる場所』に代入するということになります。

 For x = 1 To 2 
 ⇒変数 x は　1　から　2　の値をとる

 ●はい。その通りです。

 w = Split(c.Offset(, x).Value, vbLf) 
 ⇒　c　（今はA1）を列 x （今は1）だけ移動したセル（B1)の値を改行（alt enter)で分割して　変数wに代入

 ●例で説明します。仮に c が A1 だったとします。
 c.Offset(,1) なら B1 です。B1 の値は "A"改行"B"改行"C" になっていますね。（エクセルシート上の改行コードは vbLf という特殊文字）
 Split(指定の文字列,区切り文字) を実行すると、指定の文字列が区切り文字で分解されて、それらの要素が　要素番号 0 から始まる
 １次元配列になります。
 つまり w は w(0 To 2) という　３つの要素を持つ配列になり、要素0 が "A"、要素1 が "B"、要素2 が　"C" になります。

 n(x) = i 
 ⇒　x は　1　で　i は　0 　なので　n(1)=0

 ●最初はそうですね。　処理が進むと i は 20 になっているかもしれません。その場合は、代入される値は 20 になりますね。

 For Each d In w 
 ⇒　W（先ほど分割したB1セルの値（A,B,C）を順に変数dに取り出す

 ●　はい。その通りです。

 n(x) = n(x) + 1 
 ⇒n(1)は先程　n(1)=0だったので　n(1) = 0 + 1　で　n(1) = 1

 ●今までの説明と重複しますが、最初の n(x) に入れている値は、それ以前に配列に書きこまれた行数です。
 まず、その値が入っている n(x) に対して、実際に配列に値を追加していくために、その行 + 1(次の行）に書きこむ必要があります。
 なので、 n(x) = n(x) + 1 にしているわけです。

 v(n(x), x + 1) = d 
 ⇒ v(1,2)にd(今はB1セルの1個目の値 A） を代入

 ●はい。そういった制御ですね。

 Next
 ⇒For Each d In w　の所から　wの数の分繰り返す。
 （同じような確認が続いていますのでまとめます）

 ●For/Next の構文は For で規定された回数、ないしは For で規定された処理を　その下のコードで実行し
 既定の回数分 Next で繰り返します。基底回数が終われば、Next の下に行きます。
 コードで実行している配列内処理は、くまくまさんが確認コメントで書いておられるようなことですね。

（ β） 2017/04/10(月) 09:14

もう、おなかいっぱいかも知れないけど、
遅ればせながら、頭の体操。。。

Sub test()

    Dim v As Variant
    Dim maxRow As Long, maxRow2 As Long
    Dim r As Long
    Dim vResult() As Variant
    Dim i As Long, j As Long, k As Long

    'セルの値を変数に吐き出す
    v = Sheets(1).Range("A1").CurrentRegion.Value

    '組み合わせの個数を事前に数える
    maxRow = UBound(v, 1)
    For r = 1 To maxRow
        v(r, 2) = Split(v(r, 2), vbLf)
        v(r, 3) = Split(v(r, 3), vbLf)
        maxRow2 = maxRow2 + (UBound(v(r, 2)) + 1) * (UBound(v(r, 3)) + 1)
    Next
    '結果を書き込む変数の用意
    ReDim vResult(1 To maxRow2, 1 To 3)

    '組み合わせを変数に書き出す
    r = 0
    For i = 1 To maxRow
        For j = 0 To UBound(v(i, 2))
            For k = 0 To UBound(v(i, 3))
                r = r + 1
                vResult(r, 1) = v(i, 1)
                vResult(r, 2) = v(i, 2)(j)
                vResult(r, 3) = v(i, 3)(k)
            Next
        Next
    Next

    '変数に書き出した結果をシートに書き込む
    Sheets(2).Range("A1").Resize(maxRow2, 3).Value = vResult
End Sub

※タイプ1は使い道が無いような気がするので考慮しません。（必要ならタイプ2から加工）
※1行1データで行きたいので、1列目も空白は作ってません。
（見せるときは見せるときで加工。
今回はデータベースとして利用可能なデータに変換がテーマだろうと勝手に推測。
要望は『見せる』がテーマだろうけど、
そこはデータベースがあればいつでも瞬時に作成可能なので、
マクロが作れるようになれば他愛もないことだろうと思うので。）

（まっつわん） 2017/04/10(月) 10:13

 Ｔｏ　まっつわん　さん

 >頭の体操。。。

 まっつわんさんがよくつかわれる枕詞で、常には、あぁ、そうか！　と感心することが多いのですが
 今回は、どこが　『頭の体操』でしょうか？

 コメントしたように　先にサイズ計算をして配列規定したほうがいいけど、面倒なので　最大値で規定する
 というところを、先に　サイズ計算を　ちゃんとして規定。

 それだけで、あとは、配列格納ロジックは　私のものと変わらないように思いますが？

 『頭の体操』ではなく、手を抜かずに、ちゃんとサイズを正確に把握して
 それを用いるべきだあ　という指摘なら、そういう考え方も、もちろんあるとは思いますが。

（β） 2017/04/10(月) 11:14

β様
ご連絡遅くなり申し訳ありません。わかりにくい長文お読みに頂き誠にありがとうございます。
更にはまたまた詳しく解説して頂き大変恐縮でございます‼
セルに書き込む回数がエクセル処理に影響を与えるのですね。最終的に同じものを書き込むとしても、
メモリー内の配列に一度格納すると書き込み回数が一回で済むというのは、単純に感想となってしまいますがおもしろいですね‼
今回は転記するものが３列しかないので、最終行を先に計算するよりも３列x全行を指定とのこと納得致しました。
Range("A1").Resize(i, UBound(v, 2)).Value = v　の部分、解説頂き、大変有り難いです！
書き込むときを配慮してのコードなのですね。
エクセルへの負荷やメモリーの節約などコードを作るにあたり色々と考えることがあるということ大変勉強になりました。
またFor/next構文は、最初どのような順番で処理されるのか悩みましたが、(For/nextの中に更にFor/nextがある。。)、私の考え方で大丈夫とのこと、安心致しました。

β様のお力で何とかsample1は挫折することなく読みとくことができました‼
本当にありがとうございます。
Sample2も教えて頂いたことを何度も読み返し、解読してみます。
またご質問してしまうかもですが、見かけた時はとどうぞ宜しくお願い致します。
（くまくま） 2017/04/11(火) 08:26

まっつわん様
おっしゃる通り、加工可能なデータベースの作成です。すごい。
１セルに改行で複数データはなかなか使いにくく。。

最初に実際に何行必要か計算するパターンということですね！
どのようなコードを使うのか全く想像できていなかったので、大変助かります。

これから教えて頂いたコードをなんとか解読させて頂きます。
またわからない点あれば質問させて頂きたく、今後もどうぞみかけましたら宜しくお願い致します。

（くまくま） 2017/04/11(火) 08:37

ご無沙汰しております。
皆様のお力で、同一セル内の改行された複数データの分割をマクロで行うことで大幅な業務短縮を図ることができました。ありがとうございます。
業務で使用するにあたり、以下の様な場合においてもデータの分割が必要と判明し、ご相談させて頂いております。どうかご教示の程宜しくお願い致します。やはり同一セル内の改行データの分割でございます。

元データ

A列　　　　　B列　　　　　　C列　　　　　　D列
1 　 Aグループ　　　　Bグループ　　　　タイプ1
　　　　　A1　　　　　　　　B1　　　　　　a_123
　　　　　A2　　　　　　　　B2 　　　　　　b_123
　　　　　A3

2 　　　 A1 　　　　　 Bグループ　　　　　a_123

成形後

A列　　　　　B列　　　　　　C列　　　　　　　D列
1　　　　　Aグループ　　　　Bグループ　　　タイプ1

2　　　　　A1　　　　　　　Bグループ　　　　a_123

・セル内のデータは改行で区切られております。
・B列、C列については、グループが作成されているデータの組み合わせの場合　は、一番上にそのグループ名が記入されています。グループが作られていないデータの場合はデータだけの記入となっています。
・D列も同様に、グループが作成されているとセル内の一番上がグループ名となり、
そのグループを構成しているデータが下に羅列されます。

作成されているグループは以下とします。

Aグループ：A1、A2、A3
Bグループ：B1、B2

タイプ1：a_123、b_123

やりたいこと

B列、C列について
　グループ化されているものについてはグループ名のみ転記する。
　グループ化されているかどうかは、別シートにグループ表を作成し、
　それを参照して、
　セル内のデータの組み合わせが完全一致するかどうかで判断する。
　少しわかりにくいですが、
　セル内のデータが以下の場合、

　Aグループ
　A1
　A2
　A3
　A4
　
　転記後は

　Aグループ
　A4
　
グループが組まれているものに関しては、グループ名のみ
グループが組まれていないものにかんしては、その値（データ）をそのまま転記
尚、基本はグループ名（改行）グループを構成しているデータ１（改行）データ２（改行）、、、
となっていますが、たまにグループ名のみが記入されているセルもあります。
（上記例のｃ２セル）
グループ名かデータかの判断は、グループ表のグループ名と完全一致するかどうかでしか判断できないです。

D列について

　D列も改行で複数データが同一セルに入っております。
　グループが作成されている場合は、一番上がグループ名、
　改行した次からはそのグループを構成しているデータ名が羅列されます。
　グループが作成されてない場合は、データ名がそのまま入っています。

　B列、C列と同様、グループが作成されていれば、グループ名のみ転記、
　グループが作成されていないデータに関しては、データを全て転記としたいです。
　ただ、D列に関しては、グループ化されているかの基準は、
　セル内の最初のデータが、a_ かｂ_ で始まる場合は、データと見なし、全てを
　転記
　セル内の最初のデータがa_ 、b_　の文字列から始まらない場合はグループ名と
　みなし、最初のグループ名だけ転記するとしたいです。

このような複雑なことを自動化することは可能でしょうか。
どうかよろしくお願い致します。

（くまくま） 2017/04/15(土) 00:51

 このトピも長くなってスクロールするのも大変になってきています。

[[20170408223428]]　『セル内改行データの分割とデータの並び替え方法』（くまくま）　

 といったリンクを貼った、新規トピを立ち上げて質問されてはいかがでしょう。

 その際には、

 ・今回のテーマは当初のテーマの内、成形後データ表（タイプ1）のほうだと思いますが、そこを明確にしてください。

 ・元データですが、セル内に　『Aグループ』といったものも入っているのでしょうか？
 　はいっているとしたら

 　Ａグループ
 　　A1
 　　A2
 　　A3

 これって

 というように　A1,A2,A3 が　２つずつはいっているのと同じように思うのですが？

 そこについても明確に、回答側で誤解がないようにしてください。

 ・グループとそのメンバの紐つけ表は、どこに、どんなレイアウトで存在するのか、それを明確にしてください。

（β） 2017/04/15(土) 09:53

 新しいトピでは以下も明確にされたほうが　回答がつきやすいと思います。

 たとえば　セルに

 Aグループ
 A1
 A2
 A3

 こうあったときの　このセル内のデータの意味。
 これは　Aグループ　『プラス』A1,A2,A3 なのか？（つまり　要素でみれば　6　要素なのか）
 それとも、Aグループ　の要素として A1,A2,A3 を　単に　記入しているのか？

 もし、後者だった場合に、セルの中身が

 Aグループ
 A2

 こんな内容だったとしたら、それは、どういう意味なのか。要素としては、いくつあるのか。
 展開すると、どういう形になるのか、その形になる理由（ルール）はなんなのか。

 そういうことも明確にされたらいいと思います。

（β） 2017/04/15(土) 14:05

β様
いつも大変お世話になっております。
頂きましたアドバイスに習い、新しいトピを４月１５日（土）15：29にたてさせて頂きましたので、もしよろしければそちらも拝見していただけますと大変幸甚でございます。
ご質問いただきました点を新たに追加して説明させて頂いております。
情報、及び説明の不十分大変失礼いたしました。
どうか引き続き宜しくお願い致します。
（くまくま） 2017/04/15(土) 15:37

コメント返信：

[ 一覧(最新更新順) ]

[[20170408223428]] 『セル内改行データの分割とデータの並び替え方法』（くまくま） ページの最後に飛ぶ

[[20170408223428]]　『セル内改行データの分割とデータの並び替え方法』（くまくま）　ページの最後に飛ぶ