エクセルのデータを関数で一行にまとめたい

質問

エクセルで下記のような数字で区切られたデータがあり、（文字データの行数は決まっていません。）
1
あ
い
う
え
お
２
か
き
く
け
3
さ

これを
1　あいうえお
2　かきくけ
3　さ
というふうに関数で一行にしたいのです。
現在は、１～２の間の行数、２～３の間の行数をB列に関数で表示できました。画像参照
あとはA列で数字の行なら、B列の行数分文字列を連結してC列に表示させれば目的達成なのですが、そこから上手い手を思いつけずに、別の表を作って行数を検索値にVLOOKUPで
式を表示させています。（D列）
当たり前ですが文字列として表示されて式としては認識されていません。
これを式として扱える方法はありますでしょうか？
それか一行として表示するもっとスマートなやり方はありますでしょうか？
一度VBAで目的は達成しているのですが私以外にメンテできる人物がいないためなるべく関数かエクセルの標準機能をつかってできないかと考えております。

回答No.2

mt2008 · ベストアンサー

作業列を使ってもいいのですか。

添付の図の場合、データはA1:A13まで入っていますが、その隣のB列とC列を作業列に使っています。
B2に　=MAX(A$1:A2)
C2に　=IF(ISNUMBER(A2),"",C1&A2)

と入れて13行目までコピーします。
後はVlookupで目的の文字列を表示させるだけ

=VLOOKUP(1,B:C,2)　……1の下の文字列の場合

お礼

添付画像が見づらい状態での迅速な回答ありがとうございます。
作業列作っても大丈夫です。

出かけていたためお礼が遅くなり失礼しました。

mt2008さんの式だと私の関数の知識でもなんとか理解できそうです。
ありがとうございます

回答No.4

MackyNo1

添付画像が小さくてレイアウトなどが良くわからないのですが、A1セル以下に例示のデータが入力されていて、最大連結個数が１０個までなら、以下のような配列数式を使用すれば表示することができます。

C1セルに以下の数式を入力して、CtrlキーとShiftキーを押しながらEnterして下方向にオートフィルコピーしてください。

=IF(ISNUMBER(A1),LEFT(A2&A3&A4&A5&A6&A7&A8&A9&A10&A11,MIN(ISTEXT(OFFSET(A1:A10,1,0))*1000+ROW($A$1:$A$10))-1),"")

＃最終的にどのようにデータをまとめたいのかわからなかったので、ひとまず上記の式はA列の数字の右側のセルに連結文字を表示するようにしていますが、例えばまとめたい表が空白行が無い方が良い場合などは、さらに数式を工夫する必要があります。
また実際のデータが、例示のようにひらがなや英文字と限定されているなら、他の対処法も考えられますので、操作の目的を含めて補足説明されるとよいと思います。

お礼

添付画像が見づらい状態での回答ありがとうございます。
皆様に詳しく説明できてない状態で申し訳ないですが、
MackyNo1さんが自分のイメージに一番近い状態です。

ただご指摘のとおり最大連結個数が10までかどうか、多分10以上はないだろうという自分の判断
で質問させていただいたので、ご回答頂いた皆様の式をアレンジして自分で考えてみようと思います。

ありがとうございました。

回答No.3

noname#204879

貴方の添付した図はワークシート範囲が広過ぎるために内容が見え辛い。
[No.1]のように必要最小限の範囲を添付すれば見易いでしょ！

お礼

すみません。参照の式がとても長くなってしまったため
見づらい画面のまま投稿してしまいました。
今後気をつけます。
ありがとうございます。

回答No.1

noname#204879

添付図参照
F1:　=IF(COLUMN(A1)>MATCH(LOOKUP($C1+1,$A:$A),$A:$A,0)-MATCH($C1,$A:$A,0)-1,"",INDEX($A:$A,MATCH($C1,$A:$A,0)+COLUMN(A1)))
此れを右方へズズーッとドラッグ＆ペースト
D1: =F1&G1&H1&I1&J1&K1
範囲 D1:K1 を下方へズズーッとドラッグ＆ペースト

お礼

添付画像が見づらい状態での迅速な回答ありがとうございます。
出かけていたためお礼が遅くなり失礼しました。
MACH,INDEXともにあまり使わない関数なので勉強になります。
早速試してみますね。