エクセルの関数のことで？

エクセルの関数を使い、ランダム文章を作成しているのですが、改行のことで質問します。

セルA2には
=CONCAT(B2:B200)
があるとします。

セルB2には
=IFERROR(INDEX(2:2,AGGREGATE(15,6,COLUMN(C2:I2)/(C2:I2<>""),RANDBETWEEN(1,COUNTA(C2:I2))))&CHAR(10)," ")

セルB3には
=IFERROR(INDEX(3:3,AGGREGATE(15,6,COLUMN(C3:I3)/(C3:I3<>""),RANDBETWEEN(1,COUNTA(C3:I3))))&CHAR(10)," ")

同じようにセルB4～B200まで同じように続くとします。
CからIの行には各データ（文章）が入っています。

ただし、行によって、C～Iにデータのない空白行もあります。

そこで質問ですが、空白行に改行を入れるにはどうすればいいのでしょうか？

今のままでは、セルB2を見てみると・・・・

今日は、雨が降っています。
明日は、晴れるといいですね。
では、さようなら

こんな感じとなります。

これを・・・

今日は、雨が降っています。

明日は、晴れるといいですね。

では、さようなら

このような感じで空白行には、改行を入れて表示させたいのですが、どうすればいいか分かりません。

どなたか教えていただけないでしょうか？

よろしくお願いします。

質問者からの補足コメント

• 回答ありがとうございます。
  
  この場合、改行はできるのですが、２行続けて空白が続いた場合でも同じように表示されてしまいます。
  -----------------------------
  B2 今日は、雨が降っています。
  B3
  B4
  B5 明日は、晴れるといいですね。
  B6
  B7 では、さようなら
  -----------------------------
  
  このように２行続けて空白がある場合は以下のように表示させたいです。
  
  -----------------------------
  今日は、雨が降っています。
  
  
  明日は、晴れるといいですね。
  
  では、さようなら
  -----------------------------
  
  ちょっと言葉が足らずすいませんでした。

  No.1の回答に寄せられた補足コメントです。 補足日時：2021/07/04 16:30

• 回答ありがとうございます。確かにNo.5さんの方法で出来るのですが、B200まで行かない場合は、最後に空のセルが続くと改行だらけとなります。goomaniaさんの方法を試したのですが #VALUE!とエラーがでるようです。これで解決できればよかったのすが・・・。

  No.6の回答に寄せられた補足コメントです。 補足日時：2021/07/05 22:17

• d-q-t-p様
  
  なんか教えてもらっているのにすいません。
  
  自分は、エクセルの関数とかよくわからなくて、質問しようにも上手く伝える事が出来ずなさけないです。
  
  気を悪くしたら本当にごめんなさい。
  
  今さらですが、エクセルのバージョンを確認したんですが、2016となっていました。
  
  =TEXTJOIN(CHAR(10),FALSE,B2:B200)
  
  もし、よろしかったら、こちらのやり方で最後の改行を削除するにはどうすればいいか教えていただけないでしょうか？

  No.7の回答に寄せられた補足コメントです。 補足日時：2021/07/06 10:13

• goomania様
  
  教えてもらった方法でできました。
  
  ただ、「λ」が入るのでB列を見ているとなんか違和感を感じます。
  
  「λ」が入らない方法があったら教えてください。

  No.8の回答に寄せられた補足コメントです。 補足日時：2021/07/06 10:42

No.10 ベストアンサー 回答者： goomania 回答日時： 2021/07/08 10:59

No.9です。

>=TEXTJOIN(CHAR(10),FALSE,B2:B200)
>こちらのやり方で最後の改行を削除するにはどうすればいいか

とのお尋ねですが、d-q-t-pさんからの回答がないようなので、このご質問がクローズになる前に、稚拙ではありますが私のアイディアを投稿しておきます。
B2の数式が

=IFERROR(INDEX(2:2,AGGREGATE(15,6,COLUMN(C2:I2)/(C2:I2<>""),RANDBETWEEN(1,COUNTA(C2:I2))))," ")

で下方向にコピーされているとして、
A2の数式を以下のように修正すればよいと思います。

=TEXTJOIN(CHAR(10),FALSE,INDIRECT("B2:B"& MAX(INDEX(ROW(B2:B200)*(B2:B200<>" "),0))))

この回答へのお礼

goomania様
今試したとろ上手くできました。
色々と答えていただきまして本当にありがとうございました。

お礼日時：2021/07/08 13:50

No.9 回答者： goomania 回答日時： 2021/07/06 20:59

No.8です。

>「λ」が入るのでB列を見ているとなんか違和感を感じます。
>「λ」が入らない方法があったら教えてください。

方法を二つほど考えてみました。
まず一つ目は「λ」を別の文字にする方法です。例えば「♪」に変更すれば各センテンスが以下のように少し楽しげな感じになり、λよりはましになると思います。
「今日は、雨が降っています。♪」
「明日は、晴れるといいですね。♪」
「では、さようなら♪」
この場合、複数ある数式の中の「λ」を全て「♪」に変更すればOKです。
通常のセンテンスに登場しないという理由で「♪」を選択したのですが、お遊び半分に見えてしまいます。

お勧めは、「センテンス中には全角スペースは存在しない」という前提が付きますが、「λ」を「全角スペース」に変更する方法です。
この方法だと、B列には見かけ上センテンスの末尾には何も表示されません。この場合も数式中の「”λ”」を全て「"　"(全角スぺース)」に変更すればOKです。つまり、B2に
=IFERROR(INDEX(2:2,AGGREGATE(15,6,COLUMN(C2:I2)/(C2:I2<>""),RANDBETWEEN(1,COUNTA(C2:I2))))&"　"&CHAR(10)," ")
下方向へコピー
A2に
=SUBSTITUTE(SUBSTITUTE(LEFT(CONCAT(B2:B200),FIND("Ω",SUBSTITUTE(CONCAT(B2:B200),"　","Ω",COUNTIF(B2:B200,"*　*"))))," ",CHAR(10)),"　","")
となります。

二つ目は
C列～I列からランダムに選択されてB列に表示されるセンテンスに一定のルールを設ける方法です。
そのルールは以下の2つです。
(1)B列に表示されたセンテンスの末尾に必ず「。」があること
(2)B列に表示されたセンテンスの中に末尾以外には「。」がないこと。
従って、ご質問者がお示しになった例でいうと、
「今日は、雨が降っています。」→OK
「明日は、晴れるといいですね。」→OK
「では、さようなら」→NG　　これを、「では、さようなら。」に修正する必要があります。
この場合、B2に記述する数式は
=IFERROR(INDEX(2:2,AGGREGATE(15,6,COLUMN(C2:I2)/(C2:I2<>""),RANDBETWEEN(1,COUNTA(C2:I2))))&CHAR(10)," ")
となり、最初にご質問者が示されたものと変わりありません。
これを下方向にコピーするものとします。
A2に記述する数式は
=SUBSTITUTE(LEFT(CONCAT(B2:B200),FIND("Ω",SUBSTITUTE(CONCAT(B2:B200),"。","Ω",COUNTIF(B2:B200,"*。*"))))," ",CHAR(10))
となり、B列にルールを設定したので数式は前回回答より若干短くなります。

No.8 回答者： goomania 回答日時： 2021/07/06 08:16

No.6です。

>goomaniaさんの方法を試したのですが #VALUE!とエラーがでるようで
>す。これで解決できればよかったのすが・・・。

数式の記述に何等かの誤りがあるものと推測されます。
FIND関数で検索文字が見つからないのではないでしょうか?
FIND関数の記述は正しく記述されていますか?
B列に表示されているセンテンスの末尾に「λ」が表示されていますか?

添付画像をご覧ください。
添付画像①ではB2に

=IFERROR(INDEX(2:2,AGGREGATE(15,6,COLUMN(C2:I2)/(C2:I2<>""),RANDBETWEEN(1,COUNTA(C2:I2))))&"λ"&CHAR(10)," ")

を記述し、
添付画像➁ではA2に

=SUBSTITUTE(SUBSTITUTE(LEFT(CONCAT(B2:B200),FIND("Ω",SUBSTITUTE(CONCAT(B2:B200),"λ","Ω",COUNTIF(B2:B200,"*λ*"))))," ",CHAR(10)),"λ","")

を記述しています。

当方では#VALUE!は再現されません。

お書きになった数式をコピペして、補足コメントにアップできませんか?

No.7 回答者： d-q-t-p 回答日時： 2021/07/06 04:23

結局バージョンは秘密ですか。

> B200まで行かない場合は、最後に空のセルが続くと改行だらけとなります。
そんなものは何とでもなるでしょう。提示した数式にしか反応しないの
はいかがなものかと思います。

私はこれ以上回答しないことにします。

No.6 回答者： goomania 回答日時： 2021/07/05 21:41

ご質問の真意が伝わらないため、一部の回答者さんが回答に苦慮している様子が見て取れます。

一応、「空白行があったら、生成される文章の空白行の位置に行数分の改行を入れたい」というのが真意だと推測しました。

従って、No.5さんがお示しになった方法で解決できると思います。

ただし、C列～I列のデータが200行目までフルにある場合は問題ないのですが、例えば150行目でデータが終わっている場合、B151セル～B200セルは空白セルになるので、50行の連続改行が文章の末尾につくことになります。
「文章中の改行はOKだが、末尾の連続改行は不要」というケースもあるかも知れません。
これを実現する方法を記します。

B2セルの数式は、

=IFERROR(INDEX(2:2,AGGREGATE(15,6,COLUMN(C2:I2)/(C2:I2<>""),RANDBETWEEN(1,COUNTA(C2:I2))))&"λ"&CHAR(10)," ")

に修正し、これを下方向へコピーしているものとします。

A2に以下の数式を記述するということになります。

=SUBSTITUTE(SUBSTITUTE(LEFT(CONCAT(B2:B200),FIND("Ω",SUBSTITUTE(CONCAT(B2:B200),"λ","Ω",COUNTIF(B2:B200,"*λ*"))))," ",CHAR(10)),"λ","")

※上記数式に登場する「λ」「Ω」は通常の日本語センテンスに登場しない文字を意味しているだけで、「これでなくてはいけない」というものではありません。

No.5 回答者： d-q-t-p 回答日時： 2021/07/05 09:34

ひとまず B列の数式から改行記号を抜いて

A2の数式を
=TEXTJOIN(CHAR(10),FALSE,B2:B200)

この回答へのお礼

回答ありがとうございます。とりあえずやりたい事ができました。

お礼日時：2021/07/05 22:10

No.4 回答者： d-q-t-p 回答日時： 2021/07/05 08:52

Excelのバージョンはいくつなのですか？　CONCATを使っていると

いうことは Excel2019か Excel for365なんでしょうけど だったら
今更 AGGREGATEなんて使う意味がありません。

数式だけでなく やろうとしていることを一から説明し直された方が
いいかと思います。

No.3 回答者： mike_g 回答日時： 2021/07/04 16:44

どのように「ちょっと言葉が足らず」だけだったンですか？

質問文の
》 今のままでは、セルB2を見てみると・・・・
のときの、B2に空白行が無かったことが「ちょっと言葉が足らず」？
私は此処でサヨナラします。

No.2 回答者： mike_g 回答日時： 2021/07/04 16:16

B列の各セルの(最終行の)末尾にも[改行コード]である Alt+J を実行しています。

No.1 回答者： mike_g 回答日時： 2021/07/04 15:06

添付図参照(Excel 2019)

↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓