[[20170608170221]] 『空欄のセル以外の指定範囲の値を全て改行とカンマ』(Edmond) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『空欄のセル以外の指定範囲の値を全て改行とカンマで繋げて抽出したい』(Edmond)

お世話になります。いつもマクロで作ってしまうのですが、関数で作らなくてはならず、それだと全く分からなくて調べておりますが、どうしても分からなくて質問させてください。

Sheet1のC82:C182がブランクでない場合、対応するE列にそれぞれ決まった文字を入れるという式がE82:E182まで入っておりますので、見た目は空欄でも式は入っております。

Sheet2のA4のセル一つにE82:E182の空欄以外のデータを抽出して、全てカンマで繋ぎ、セル内で改行したいのですが、どうしたら良いでしょうか。

EX)

<Sheet1>
   C      D    E      Eの式
82 キャド    N/A   CAD      =If(C82<>"","CAD","")
83        N/A            =If(C83<>"","CAM","")
84 CAE    N/A   CAE      =If(C84<>"","CAE","")
85 3次元CAD N/A   3D CAD    =If(C85<>"","3D CAD","")


<Sheet2>
  A
4  CAD,
  CAE,
  3D CAD

上記のように抽出し、セル内で改行し、カンマで繋ぎたいと思っております。

< 使用 Excel:Excel2007、使用 OS:Windows7 >


=E82&IF(E82="","",",")&E83&IF(E83="","",",")&E84&IF(E84="","",",")〜  ず〜〜っと
(11) 2017/06/08(木) 18:06

100セルで平均3文字で 300文字+100コロン  400文字程度
1024文字以内ならなんとか
1セルに対して数式約20文字2000文字 数式32767文字

なんとか治まるのかな
(11) 2017/06/08(木) 18:13


E95セルの"3D CAD" を、"3D_CAD" のように、空白を含まない文字列に変えては駄目でしょうか?
一旦、全ての文字列を空白区切りで連結後、TRIM関数を使うことで、空白を1つずつにできるのです。これを利用すれば、以下のような数式で実現できるのですが…。
 =SUBSTITUTE(TRIM(SUBSTITUTE(CONCATENATE(Sheet1!E82," ",Sheet1!E83," ",Sheet1!E84," ",Sheet1!E85),"  "," "))," ",","&CHAR(10))
(???) 2017/06/08(木) 18:18

11さんの方法でも、私の方法でも、100セル分ずらずらと式を延ばすのは、現実的ではないように思います。マクロでユーザー定義関数を作って対応する方が良いのではないでしょうか。
(???) 2017/06/08(木) 18:21

 作業列は使ってはいけないの?
 F82=IF(E82="","",E82)
 F83=IF(E83="",F82,F82&","&CHAR(2)&E83)
 Sheet2!A4=SUBSTITUTE(Sheet1!F182,CHAR(2),CHAR(10))
(稲葉) 2017/06/08(木) 18:28

 今さらだけど、E列の式のほうが現実的じゃないですよね・・・。
(稲葉) 2017/06/08(木) 18:39

皆さん有難うございました。マクロだと簡単なのですが、人のために作っていて、それだとその人が自分では直せないので、関数でできないか調べていました。E列が例だと単純なのですが、実際は英語のセンテンスなのでブランクが幾つも入ってくるので制約も色々とあります。作業列で対応することにします。どうも有難うございました。
(Edmond) 2017/06/09(金) 10:10

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.