『複数のセルの内容を複数行にして１つのセルに表示する』について教えてください。

添付画像のように、複数のセルの内容を複数行にして１つのセルに表示、尚且つ、１，２，３，４と連続してかえしたいのですが、
INDEX,ROW,INTを組わせて色々試してみたのですが、どうもうまくいきません。
何卒、ご援助お願いします。

No.3 ベストアンサー 回答者： CoalTar 回答日時： 2016/05/12 16:36

No.1です

前回の質問があったんですね
CONCATENATE関数を使っているようなので習って
=SUBSTITUTE(TRIM(CONCATENATE(INDEX(C:C,ROW(C1)*3-2)," ",INDEX(C:C,ROW(C1)*3-1)," ",INDEX(C:C,ROW(C1)*3)))," ",CHAR(10))
3行分を一つのセルに収めています

この回答へのお礼

ありがとうございます。
とはいうものの、
まだ、検討は終わったのですが、
組み込みがまだできていません。

教えて頂いた方向ですすめたいと思っています。

お礼日時：2016/05/15 18:04

No.4 回答者： tom04 回答日時： 2016/05/12 17:03

こんにちは！

すでに回答は出ていますが、VBAでの一例です。
元データはSheet1のB・C列1行目からあり、Sheet2のA・B列に表示するとします。

Alt＋F11キー　→　メニュー　→　挿入　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻り（VBE画面を閉じて）マクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から//
Dim i As Long, k As Long, cnt As Long
Dim myStr As String, wS As Worksheet
Set wS = Worksheets("Sheet2")
wS.Range("A:B").ClearContents
With Worksheets("Sheet1")
For i = 1 To .Cells(Rows.Count, "C").End(xlUp).Row
If .Cells(i, "B") <> "" Then
cnt = cnt + 1
wS.Cells(cnt, "A") = .Cells(i, "B")
myStr = .Cells(i, "C")
k = i
Do
k = k + 1
If .Cells(k, "B") <> "" Or _
k = .Cells(Rows.Count, "C").End(xlUp).Row + 1 Then Exit Do
If .Cells(k, "C") <> "" Then
myStr = myStr & vbCrLf & .Cells(k, "C")
End If
Loop
wS.Cells(cnt, "B") = myStr
i = k - 1
End If
Next i
End With
wS.Activate
End Sub 'この行まで//

※　関数でないのでデータ変更があるたびにマクロを実行する必要があります。
※　B列のデータ（数値）が入っている行のC列は空白でない！という前提です。m(_ _)m

この回答へのお礼

ありがとうございます。

お礼日時：2016/05/15 18:03

No.2 回答者： fujillin 回答日時： 2016/05/12 16:17

こんにちは

前回（？）と同じ内容だけれど、式をオートフィルできるようになさりたいという意味でしょうか？
https://oshiete.goo.ne.jp/qa/9273131.html
（セルの位置関係が変わっているようではありますが・・・）

＞INDEX,ROW,INTを組わせて色々試してみたのですが、
＞どうもうまくいきません。
参照関係に規則性がありますので、計算しようと思えば可能ですよ。
但し、式が長くなる傾向は否めませんので、効率的なのかどうかはわかりませんが。


すでにANo1様が素晴らしい回答をなさっていらっしゃいますので、それをパクらせていただいて、おまけとして作業列を使わない例を・・・
セルの位置関係が影響しますので、関係が変わる場合は計算を調整する必要があります。
今回例示のＣ列を参照する場合として、14行目から1～3行目を参照するには
　1行目は　ROW()*3-41　→　14*3-41＝1
　2行目は　ROW()*3-40　→　14*3-40＝2
　3行目は　ROW()*3-39　→　14*3-39＝3
のような式にしておけば、15行目からの参照では4～6行目を参照するようになります。
（ANo1様の回答でも同じような参照設定をなさっていますよね）

これを提示されている式に代入すれば、式は長くはなりますが・・・
E14に以下を代入して、下方にオートフィルすることで作業列なしでも計算可能になります。
=SUBSTITUTE(TRIM(INDIRECT("C"& ROW()*3-41)&" "&INDIRECT("C"& ROW()*3-40)&" "&INDIRECT("C"& ROW()*3-39))," ",CHAR(10))

この回答へのお礼

ありがとうございます。

お礼日時：2016/05/15 18:05

No.1 回答者： CoalTar 回答日時： 2016/05/12 14:35

回答を入力する表２にしたいのかな？

作業列D1セルに
=IF(B1<>"",C1,INDEX(D:D,ROW()-1)&" "&C1)
下へオートフィル

E14セルから連番
F14セルに
=SUBSTITUTE(TRIM(INDEX(D:D,E14*3))," ",CHAR(10))
下へオートフィル