エクセルの条件文で抽出したセルに改行を入れたい

質問

条件文で作成した文字列内に改行を入れる方法を教えてください。

下記のようなエクセル（以後、数百行あります）があり、○がついているところの１行目にある文字をＫ２セルに入力したいと思います。
　　Ａ　 Ｂ　 Ｃ　 Ｄ　 Ｅ　 Ｆ　 Ｇ　 Ｈ　 Ｉ　 Ｊ　Ｋ　
１　あ　い　う　 え　 お　 か　 き　 く　け　 こ　　
２　○ 　　　　 　○　　　　　　　○　　　 ○ 　○
３　　　○　　　　　 ○　　 　○　 ○

Ｋ２セルには、○印のついている
あ
え
き
け
こ

と改行して出力されるように関数を組むにはどうしたらいいのでしょうか？
=IF(A2=○,A1&"","")&IF(B2=○,B1,"")&IF(C2=○,C1,"")&IF(D2=○,
D1,"")&IF(E2=○,E1,"")&IF(F2=○,F1,"")&IF(G2=○,G1,"")&IF(H2=○,H1,"")&IF(I2=○,I1,"")&IF(J2=○,J1,"")

と入力してみましたがうまくいかず、これではうまくいったとしても
改行なしで
あえきけこ
と出てしまう気がします・・・。

一晩悩みましたがどうしてもうまくいかないのでどなたか教えてください。
よろしくお願いいたします。

回答No.5

mu2011 · ベストアンサー

関数では不可能と思いましたので、ＶＢＡにしてみました。
一例です。
（１）シートタブ上で右クリック→コード表示
（２）以下のサンプルコードを貼り付け、範囲="K2:k5"を調整→ＶＢＥ終了
（３）マクロ→サンプルを実行
Sub サンプル()
範囲 = "k2:k5"
For i = 1 To Range(範囲).Cells.Count
a = Application.WorksheetFunction.CountIf(Range(Cells(i + 1, 1), Cells(i + 1, 10)), "○")
If a > 0 Then
sw = 0
For k = 1 To 10
If Cells(i + 1, k) = "○" Then
If sw = 0 Then
Range(範囲).Cells(i) = Cells(1, k)
sw = 1
Else
Range(範囲).Cells(i) = Range(範囲).Cells(i) & vbLf & Cells(1, k)
End If
End If
Next
End If
Next
End Sub

補足

ご回答ありがとうございます。
これをやってみたら出来たのですが、抽出されて出来た文字列をコピーして秀丸に貼り付けると、
"あ
う
お
き
く
こ"
になってしまいます。
これを
ダブルクオーテーション抜きの
あ
う
お
き
く
こ
にする方法はないでしょうか？

実際にはあいうえおではなく、リンクタグを取り扱っているので、
<a href="http://***.com">●●●</a>m<br>
という文字列が、秀丸にコピペしたとき
"<a href=""http://***.com"">●●●</a>m<br>"
のようにリンク内のタグのクオーテーションがダブルクオーテーションになってしまいます。
エクセル上は問題ないのですがコピペの仕方が悪いのでしょうか？

回答No.7

mu2011

NO5です。
改行コードが入っているセルをコピー＆貼り付けでは二重引用符が付加されるみたいです。
セル内容（数式バー内容）をコピー＆貼り付けではこの引用符が付加されないみたいですが、１セル単位しかできないので今一です。
よって、改行コードを削除してコピー＆貼り付けでは駄目でしょうか。
抽出列選択→編集→置換→検索文字列欄にctrl＋Ｊキー押下（改行コードは表示されない）、置換文字列欄は未入力→すべて置換

補足

ご回答ありがとうございました。
なかなかうまくいきませんが、とても助かりました。

回答No.6

noname#180098

なかなか解決しませんね。
＃２のお礼に対するアドバイスです。

文字の長さを統一されてはいかがでしょうか。
文字の後ろや前にスペースを足すことで解決しそうなんですけど。

文字の長さを揃える際、表示を等幅フォントにすると文字数と幅が一致しますので、関数を入力したセルのフォントを変更してみてください。
例：「MS Pゴシック」→「MS ゴシック」

フォント名に「P」が付いている物はプロポーショナルフォントを示します。
これは文字の幅によってフォントの横サイズを調整しているフォントです。
iiiii（iを５つ）
zzzzz（zを５つ）
↑この2行の幅が違って見える場合はプロポーショナルフォントを使用して表示しています。

お礼

文字の長さ統一は出来そうにないくらい各セルばらばらなんです。
ですがありがとうございました。

回答No.4

ookami1969

表の形が「行列逆なら」やり易いですよね。

なので 一旦 別のシートに 行列を入れ替えて貼り付けて
フィルタで抽出してはいかがでしょうか？

お礼

ご回答ありがとうございました。
数個やってみたのですがうまくいきませんでした。
エクセル自体も数百個あるので難しい感じです。

回答No.3

imogasi

IF関数のネストは泥臭い。それにIFのネスト数制限もある。
ーーー
かといって、関数では良い方法が思い浮かばない。
多分回答が出ないのではないか。
ーーーー
やむを得ずVBAでユーザー関数を作った。
意味がわかる人にはこんなに簡単。
Function aa(b)
s = ""
For Each cl In b
If cl = "○" Then
s = s & cl.Offset(-cl.Row + 1, 0) & Chr(10)
End If
Next
aa = s
End Function
標準モジュールに入れる。
ーーー
そのユーザー関数の使い方
結果A-K列
あ い う え お か き く け こ
○ &nbsp; ○ ○ ○
"あ
え
き
こ
"
○ ○ ○ ○
"い
お
き
け
"
ーーー
K列のK2の式　=aa(A2:J2)
下方向に式を複写。
ーー
K列は、書式ーセルー配置ー「折り返して全体を表示する」
且つ行番号部で行の下線をダブルクリックして、行高を確保しないと
結果が正しく見えない。これは絶対関数ではできない。
ーー
質問の＞改行なしで・・はこの点やったのかな。
ただ質問者の式を見る限りCHAR(10)は見えないが。問題はそこだろう。
上記VBAでも、最後の改行が余分だがそのママにしておく。

お礼

ご回答ありがとうございます。
VBAが分からないので難しかったけれど、CHAR(10)のことが分かってよかったです。

回答No.2

noname#180098

改行は関数の中でしなくても良いと思います。と言いますか、できません。

縦にセルを結合して十分な長さ（？）を確保して、セルの幅を一文字分まで狭くしてはいかがでしょう。
「セルの書式設定」から「配置」の項目で「折り返して全体を表示する」を選ぶと一文字毎に改行されたような効果を得られます。

さて、関数ですが
IF(A2="○",A1,"")
と「○」に対してダブルクォーテーションで囲む必要があります。
ダブルクォーテーションで囲われない文字は文字列として認識されませんので注意しましょう。

関数を組み合わせて思うように動作しないときは、まずは組み合わせる前の関数の動作を確認しましょう。
意外と簡単に原因を見つけられますよ。

お礼

ご回答ありがとうございます。
実際にはあ、い、う、え、おという文字列ではないのでセルの幅を短くして回避は難しかったです。
ありがとうございました。

回答No.1

ki-ro-ro

CHAR(10)
を追加して改行するとか。

=IF(A2="○",A1&CHAR(10),"")&IF(B2="○",B1&CHAR(10),"")&IF(C2="○",C1&CHAR(10),"")&IF(D2="○",D1&CHAR(10),"")&IF(E2="○",E1&CHAR(10),"")&IF(F2="○",F1&CHAR(10),"")&IF(G2="○",G1&CHAR(10),"")&IF(H2="○",H1&CHAR(10),"")&IF(I2="○",I1&CHAR(10),"")&IF(J2="○",J1&CHAR(10),"")

お礼

ご回答ありがとうございました。
こちらやってみましたが変わりませんでした。
でも改行はctrl+Jだと思っていたのでとても参考になりました。
ありがとうございました。