Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Excel 2016)
項目名との組み合わせ
投稿日時: 20/07/16 10:51:28
投稿者: radames1000

Office Professional Plus 2016です。
 
下記表を作成しておりA列に数式を入れていますが、冗長と感じているので
改善のアドバイスをいただけましたら幸いです。
   A        B    C    D
1         a    b    c
2 a×1,b×2,c×1  1    2    1
3 b×3            3    
4 b×2,c×0.5        2    0.5
 
条件:作業セルなし
一行目:項目名
A列:B〜D列に数値がある場合、項目名×数値ですべて表示。区切りは「,」。全列数値入力なしの場合は空白
※B〜D列は項目名以外数値のみで文字列は入りません。
下記数式を入力しています。
=SUBSTITUTE(TRIM(SUBSTITUTE(IF(B2<>"",B$1&"×"&B2&",","")&IF(C2<>"",C$1&"×"&C2&",","")&IF(D2<>"",D$1&"×"&D2&",",""),","," "))," ",",")
 
考え方
1.IFで数値の有無を判断。有であれば「項目名×数値,」
2.必ず最後に不要な「,」がつくのでスペースに変換
3.TRIMで最後の不要なスペースを削除
4.スペースを「,」に戻す
 
これらの考え方で改善点や別案などありましたらご教示ください。
よろしくお願いいたします。
 
別解
=LEFT(IF(B2<>"",B$1&"×"&B2&",","")&IF(C2<>"",C$1&"×"&C2&",","")&IF(D2<>"",D$1&"×"&D2&",",""),LEN(IF(B2<>"",B$1&"×"&B2&",","")&IF(C2<>"",C$1&"×"&C2&",","")&IF(D2<>"",D$1&"×"&D2&",",""))-1)
却下理由
1.数値が一つもない場合エラーになる。
2.冗長

回答
投稿日時: 20/07/16 13:15:22
投稿者: んなっと

もしかしたら...
 
B2
=IFERROR(LOOKUP(10^9,MID($A2,FIND(B$1,$A2)+2,ROW($1:$9))*1),"")
 
,b×2 の部分が ,123b×2 となるようなことがあると失敗。

投稿日時: 20/07/16 13:21:35
投稿者: radames1000

んなっとさん、ご回答ありがとうございます!
 
誤解させるような内容で申し訳ありません。
今回ご教示いただきたい数式はA列のものなんです。
BからD列に数値が入っているので、
それをA列にまとめたい、というものになります。
 
一応TRIMなどを使ってできてはいるものの、
もっと違った方法がないかと思っています。

回答
投稿日時: 20/07/16 13:36:08
投稿者: んなっと

TEXTJOINが使える環境なら...
 
A2
=TEXTJOIN(",",TRUE,INDEX(REPT(B$1:D$1&"×"&B2:D2,LEN(B2:D2)>0),))

投稿日時: 20/07/16 13:52:50
投稿者: radames1000

んなっとさん、たびたび申し訳ございません。
Professional PlusなのでTEXTJOINが使えないんです。

回答
投稿日時: 20/07/16 14:18:24
投稿者: んなっと

でしたら SUBSTITUTE(TRIM( か、作業列利用でしょう。
 
=SUBSTITUTE(TRIM(IF(LEN(B2),B$1&"×"&B2,"")&IF(LEN(C2)," "&C$1&"×"&C2,"")&IF(LEN(D2)," "&D$1&"×"&D2,""))," ",",")

投稿日時: 20/07/16 14:30:10
投稿者: radames1000

確かに最初からスペースにしておけばSUBSTITUTEが一度ですみますね。
条件式でのLENの使い方も勉強になります。
ありがとうございます。

投稿日時: 20/07/19 22:25:41
投稿者: radames1000

追記はないようですので、これでしめさせていただきます。
んなっとさん、ありがとうございました。