区切り位置によるセル分割について

EXCEL初心者の為、説明が不足している点多々あるかと思いますが、詳しいかた教えて下さい。

下記のように、E列に「、」で区切られた文字列が並んでおり、「、」ごとにセル内の文字列を分け、すぐ下の行に配置し、かつA～D列の内容をE列で区切った文字列にそのままコピーしたいです。


どのような関数やVBAを使えば処理できるか教えて頂けますでしょうか。

No.2 回答者： ママチャリ 回答日時： 2021/07/09 22:47

こんな感じだな！！

（しかし、同じようなマクロばっかり、書いてるな~）

Sub Macro1()
Dim i As Long
Dim a As Variant
Dim rep As Long
For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
a = Split(Cells(i, "E").Value, "、")
rep = UBound(a)
If rep > 0 Then
Rows(i).Copy
Rows(i + 1).Resize(rep).Insert Shift:=xlDown
Cells(i, "E").Resize(rep + 1) = WorksheetFunction.Transpose(a)
Application.CutCopyMode = False
End If
Next i
End Sub

No.1 回答者： fujillin 回答日時： 2021/07/08 18:07

こんにちは

＞どのような関数やVBAを使えば処理できるか教えて頂けますでしょうか。
関数でもできないことはありませんが、かなり面倒です。
例えば、
　=LEN(E2)-LEN(SUBSTITUTE(E2,"、",""))+1
で、E2セルの文字を分解したときの個数が求められるので、これを利用して累積配列を作成しておき（配列は当然昇順になります）、記入する行数で上記配列を検索すると、何番目のデータが該当するか（＝記入すべきか）わかります。
A～D列はその行番号のデータを参照するだけで良いですが、E列に関してはさらに、上記の番号と実際の行数との差から、分解したときの順番を求めて参照することになります。
一発で求めようとすると、とても複雑な式になるでしょうから、作業列等を利用して、順に求めてゆくのがよさそうに思います。

VBAを利用する場合は、さほど難しいものにはならないと思います。
別シートに書き写すものとするなら、E列の文字列に対してSplit関数で
　DivStr = Split(Range("E2").Value, "、")
とすれば、”、”で区切った文字配列（0スタート）が得られるので、
0～Ubound(DivStr)までをループしながらコピーすれば1行分の処理が終わります。
各行に対して順にこの処理を繰り返せば、ご質問内容を実現できるでしょう。