[[20220623124307]]　『SUBSTITUTE関数が重なってしまうことを避けたい』（いいちこ）　ページの最後に飛ぶ

『SUBSTITUTE関数が重なってしまうことを避けたい』（いいちこ）

失礼します。
SUBSUTITUTE関数で特定の文字列を消去したいとき、
その文字列が10個あると

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(

このようになり、とても冗長でシートそのものが重くなります。

これを何とかして避けたいと思います。

皆様のお知恵をお借りできれば幸いです。

よろしくお願いいたします。

＜使用 Excel：Office365、使用 OS：Windows10 ＞

 VBAを使うとか？
（ねむねむ） 2022/06/23(木) 13:06

 正攻法はVBAだと思います
 データの形によりますがエディタに持ってって正規表現使って置換で対応したほうが早いかも…
（.:*.ゆ ゅ） 2022/06/23(木) 13:13

皆様ありがとうございます。

VBAを使うというお答えをいただきましたが、上司によりVBAは勘弁してくれという方針でしたので、できれば関数をうまくやりくりして解決できる方法をご教示ねがいます。

重ね重ね申し訳ありません。
（いいちこ） 2022/06/23(木) 13:15

 365だと LAMBDA関数が使えるので、再帰させれば式は短くできるかもしれませんが、
 結局、SUBSTITUTE関数を10回繰り返すのは同じなので、軽くはならないでしょうね

 PowerQueryでやるのがいいとおもいます
（´・ω・`） 2022/06/23(木) 13:58

 現在確認できないのだがスピル機能を使った場合、処理時間は変わらないだろうか？
（ねむねむ） 2022/06/23(木) 14:07

皆様、説明がへたくそですみません。例を作ってみました。
りんご、みかん、メロン、もも、プラム、ぶどう、あけび、びわ
これを以下の表に列挙するとします。

              A                     B　　　　　　　Ｃ
1　　りんご,みかん,あけび　　　　　　　　　　　　りんご
2　　みかん,メロン,もも,プラム　　　　　　　　　 ぶどう　　
3　　あけび,プラム,びわ　　　　　　　　　　　　　もも　
4  　メロン,もも,プラム,ぶどう　　　　　　　　　 メロン　　
5　　ぶどう,りんご,プラム　　　　　　　　　　　　　
※本当は300までずっと続いています。

「りんご、ぶどう、もも、メロン」この4つをSUBSTITUTEで""として消去したい場合
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"りんご",""),"ぶどう",""),"もも",""),"メロン","")
上記のようにすれば確かに消去できますが、10個あるときは、SUBSTITUTEが10個重なって非常に重くなってしまい難儀しております。
"りんご"のところに"C1:C5"とやってみましたが、うまくいきません。
私の技量ではSUBSTITUTEしか頭にありませんでしたが、ほかの関数でより賢くできるのであれば教えていただければ幸いです。
（いいちこ） 2022/06/23(木) 14:44

 例えば
 =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"りんご",""),"ぶどう",""),"もも",""),"メロン","")
 と入力して下へフィルコピーするのと
 B1セルに
 =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1:A300,"りんご",""),"ぶどう",""),"もも",""),"メロン","")
 と入力するのでは重さは変わらないだろうか？
 現在365で確認できないため試してみてもらえないだろうか？
（ねむねむ） 2022/06/23(木) 14:50

ねむねむさん
迅速な回答ありがとうございます。
やってみますね。
（いいちこ） 2022/06/23(木) 14:55

 [[20220608152005]] 『特定の文字列の表を除外した結果のセルを作りたいで...』（いちご）
 この辺りのねむねむさんが記載していたものは参考にはならないのでしょうか？
 式が解読できなかったので間違っていたらすみません
（なるへそ） 2022/06/23(木) 14:58

 ＞10個あるときは、SUBSTITUTEが10個重なって非常に重くなってしまい

 4個と10個で試してみましたけど、何も変わりませんでしたけどね。
 重いとも感じなかったし･･･

 ところで、消去した後にコンマが残るのは問題ないんですか？

 以上
（笑） 2022/06/23(木) 16:27

 ＞=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"りんご",""),"ぶどう",""),"もも",""),"メロン","")

 A列に「みかん,りんごジュース,もも」とあったとき、上の式だったら
「みかん,ジュース,」になりますけど、それも問題ないんですかね？

 以上
（笑） 2022/06/24(金) 08:53

＞ SUBSTITUTEが10個重なって非常に重くなってしまい
数式の長さと処理の重さが比例すると思い込んでませんか？

計算する数が同じなら配列にしようがスピルでやろうが 300セルくらいで処理に変化が
あるとは到底思えません。

Microsoft365なら SCANと MAPでまとめること自体は特に問題なくできます。

=LET(_keyRng,C1:C4,_map,MAP(A1:A5,LAMBDA(c,INDEX(SCAN(c,_keyRng,LAMBDA(a,b,SUBSTITUTE(a,b,""))),ROWS(_keyRng)))),SUBSTITUTE(TRIM(SUBSTITUTE(_map,","," "))," ",","))
（d-q-t-p） 2022/06/24(金) 13:14

コメント返信：

[ 一覧(最新更新順) ]

[[20220623124307]] 『SUBSTITUTE関数が重なってしまうことを避けたい』（いいちこ） ページの最後に飛ぶ

[[20220623124307]]　『SUBSTITUTE関数が重なってしまうことを避けたい』（いいちこ）　ページの最後に飛ぶ