エクセルでセル内の特定数値を他セルの数値で置換

質問

エクセルで下記のようなことを行いたいです。アドバイスお願い致します。

●処理前
Ａ１⇒　2151　　Ｂ１⇒　RSS1234　　Ｃ１⇒　RSS1234+RSX1234
Ａ２⇒　3412　　Ｂ２⇒　RSS5678　　Ｃ２⇒　RSS5678+RSX5678
Ａ３⇒　5486　　Ｂ３⇒　RSS3456　　Ｃ３⇒　RSS3456+RSX3456
・
・
・

●処理後要望
Ａ１⇒　2151　　Ｂ１⇒　RSS2151　　Ｃ１⇒　RSS2151+RSX2151
Ａ２⇒　3412　　Ｂ２⇒　RSS3412　　Ｃ２⇒　RSS3412+RSX3412
Ａ３⇒　5486　　Ｂ３⇒　RSS5486　　Ｃ３⇒　RSS5486+RSX5486
・
・
・
Ａ列が空白になるまで上記置換が行われる。

となるようなVBAを作りたいのです。
アドバイスお願い致します。

回答No.4

kagakusuki · ベストアンサー

置換をしたい範囲を選択しているシート上にあるC列～Ｄ列としますと、次の様なVBAマクロは如何でしょうか？

Sub Macro()

Dim SN As String
Dim LR As Long

SN = ActiveSheet.Name
ActiveSheet.Select
If Application.WorksheetFunction.Count(Columns("A:A")) = 0 Then Exit Sub
LR = Application.WorksheetFunction.Match(9E+307, Columns("A:A"))
Range("B1:D" & LR).Replace What:="=", Replacement:=" ="
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(SN).Select
Sheets(Sheets.Count).Range("C1:D" & LR).FormulaR1C1 = _
"=SUBSTITUTE('" & SN & "'!RC,MID('" & SN & "'!RC2,MIN(" & _
"IF(ISNUMBER(FIND(0,'" & SN & "'!RC2)),FIND(0,'" & SN & "'!RC2),LEN('" & SN & "'!RC2)+1)," & _
"IF(ISNUMBER(FIND(1,'" & SN & "'!RC2)),FIND(1,'" & SN & "'!RC2),LEN('" & SN & "'!RC2)+1)," & _
"IF(ISNUMBER(FIND(2,'" & SN & "'!RC2)),FIND(2,'" & SN & "'!RC2),LEN('" & SN & "'!RC2)+1)," & _
"IF(ISNUMBER(FIND(3,'" & SN & "'!RC2)),FIND(3,'" & SN & "'!RC2),LEN('" & SN & "'!RC2)+1)," & _
"IF(ISNUMBER(FIND(4,'" & SN & "'!RC2)),FIND(4,'" & SN & "'!RC2),LEN('" & SN & "'!RC2)+1)," & _
"IF(ISNUMBER(FIND(5,'" & SN & "'!RC2)),FIND(5,'" & SN & "'!RC2),LEN('" & SN & "'!RC2)+1)," & _
"IF(ISNUMBER(FIND(6,'" & SN & "'!RC2)),FIND(6,'" & SN & "'!RC2),LEN('" & SN & "'!RC2)+1)," & _
"IF(ISNUMBER(FIND(7,'" & SN & "'!RC2)),FIND(7,'" & SN & "'!RC2),LEN('" & SN & "'!RC2)+1)," & _
"IF(ISNUMBER(FIND(8,'" & SN & "'!RC2)),FIND(8,'" & SN & "'!RC2),LEN('" & SN & "'!RC2)+1)," & _
"IF(ISNUMBER(FIND(9,'" & SN & "'!RC2)),FIND(9,'" & SN & "'!RC2),LEN('" & SN & "'!RC2)+1)" & _
"),4),'" & SN & "'!RC1)"
Sheets(SN).Range("C1:D" & LR).Value = Sheets(Sheets.Count).Range("C1:D" & LR).Value
Application.DisplayAlerts = False
Sheets(Sheets.Count).Delete
Application.DisplayAlerts = True
Range("B1:D" & LR).Replace What:=" =", Replacement:="="

End Sub

　尚、上記のマクロは、あくまで

>⇒Ｂ列には、必ず数値が含まれております。
>置換をしたい範囲のセル（Ｄ列までとすれば、Ｂ～Ｄ列）には、ＶＢが走る前の初期状態ではいずれも必ず同じ４桁の数値が含まれている状態に致します。

という条件が満たされている場合においてのみ有効なマクロです。
　もしB列に入力されている文字列が、例えば

R2S1234

等の様に4桁ではない数字（この場合は「2」）が、4桁の数字よりも前の部分に含まれているものであった場合には、正しい結果を得られませんので注意して下さい。

回答No.5

keithin

＞Ｂ列～〇列のセル内に含まれる特定の4桁の数値を

最初のご相談の情報提供と違う事が書かれていますが、今度は「特定の」例えば9999がＢ列以右の「全ての行の全てのセルに共通で」記載があるということで、今度こそ間違いないんですね。
いずれにしても回答済みのマクロからほとんど何も変える必要がありません。

sub macro1()
　dim c as string, cx as string
　dim i as integer
　dim r as long

　for i = 1 to len(range("B1"))
　　if mid(range("B1"), i, 1) like "[0-9]" then
　　　cx = cx & mid(range("B1"), i, 1)
　　end if
　next i

　for r = 1 to range("A65536").end(xlup).row
　　cells(r, "B").resize(1, 255).replace _
　　　what:=cx, replacement:=cells(r, "A"), lookat:=xlpart
　next r
end sub

＞どのような条件を明確にすれば良いかさえ分からない

「あなたのエクセルの、いま目に見えてる事実」と「それがどうなって欲しいのかの姿」を目に見えるままありのまま正しく丁寧に情報提供すればいいんです。

回答No.3

MarcoRossiItaly

＞説明不足大変失礼致しました。逆に分かりにくくなるのではと思っておりましたが、お言葉に甘えて……

補足を読んでもまだ、条件が足りていません。したがって誰かが勝手に仮定なりをしなければ、答えを出すことはムリです。「不可能」なのですから、分かりやすさ以前の問題です。

＞……人間が一行ずつやるなら（Ｄ列までとするなら）Ｂnセル～Ｄnセルを範囲選択して、特定数値である9999を……

まさにそういう、人間が手作業で行うなら、どういうルールでの処理になるのか？ということです。基本原則が不明だということです。

●補足のデータ例では、B 列～ D 列の 9 セルに全て「9999」と書いてありますね。ところが質問文における同じ範囲には、「1234」、「5678」、「3456」と 3 種類も出てきますよ。
●補足を見る限り、各文字列中の何文字目に置換対象の文字列が出てくるのかも一定していないようです。

人間は、「何となく」といった感覚で、本当は複雑な条件が組み合わさった作業でも、簡単にこなしてしまったりします。しかし、同じことを機械に理解させるには、数学の問題を解くのと同じで、具体的かつ過不足のない条件を与える必要があります。問題が複雑になれば、それだけ数学力なり論理力が求められるようになります。

例えば「回文を作成せよ」という問題があれば、(1)文末の句点がある場合のみそれを除去、(2)文字列中の全ての文字の位置を昇順で付番、(3)番号の降順で文字を並べ替え、(4)(1)で句点を除去していた場合のみそれを付加、といったふうに、「紛れ」のないルールを明示します。そうでなければ、永遠に答えには到達できません。

条件さえはっきりして、その条件の数がそれほど多くはないとすれば、置換するコードを書くこと自体は簡単です。

補足

ご気分を害されるような書き込みになってしまったことを心より謝ります。
申し訳ございません。

どのような条件を明確にすれば良いかさえ分からない私は本来質問するのは失礼に当たるのかも知れません。
ただどうしても困っており助けて頂きたいという思いで投稿致しました。

何卒ご容赦のほどお願い致します。

回答No.2

kagakusuki

どの様な処理を行えば良いのかという条件に、不明な点がありますので、追加情報を御教え願います。

●A列に自然数以外の値（文字列、負の数、小数点以下の桁を含んでいる数値）が入力されていた場合には、どの様にすれば良いのでしょうか？

●B列のセルに数値が含まれていない場合には、どの様にすれば良いのでしょうか？

●B列のセルに

=A20+ABC1234+DEF5678+GHI987654

等の様に、複数種類の数値が入力されていた場合には、どの数値を置換対象の数値とすれば良いのでしょうか？

　以上の点に関して御教え願います。

補足

ご丁寧にありがとうございます。助かります。

ご確認について、今回の仕様の要望について返答させて頂きます。

●Ａ列に自然数以外の値（文字列、負の数、小数点以下の桁を含んでいる数値）が入力されていた場合には、どの様にすれば良いのでしょうか？

⇒今回は最後ブランクになるまで必ず自然数4桁のみが入ります。よって入力に自然数以外の値は考慮する必要はございません。

●B列のセルに数値が含まれていない場合には、どの様にすれば良いのでしょうか？

⇒Ｂ列には、必ず数値が含まれております。
置換をしたい範囲のセル（Ｄ列までとすれば、Ｂ～Ｄ列）には、ＶＢが走る前の初期状態ではいずれも必ず同じ４桁の数値が含まれている状態に致します。（例．9999）

●B列のセルに
=A20+ABC1234+DEF5678+GHI987654
等の様に、複数種類の数値が入力されていた場合には、どの数値を置換対象の数値とすれば良いのでしょうか？

⇒VBを走らせる前の初期状態として、必ず同じ4桁の数値が入っている状態にしております。
4桁の数値を9999とすれば、ご質問の例では
=A9999+ABC9999+DEF9999+GHI9999
のように数値部分は必ず9999になります。

ご丁寧にありがとうございます。
ご検討宜しくお願い致します<m(__)m>

回答No.1

keithin

＞エクセルでセル内の特定数値を他セルの数値で置換

というご相談ですが、現実にやりたいのは「Ｂ列やＣ列にある不特定の数字」をＡ列の特定の数字に置き換えたい、となっています。
たとえばＢ１を見て「１２３４」という数字がここでは対象になると「特定して」、Ｂ１，Ｃ１の１２３４を２１５１に置き換えたいとか。そういった「あなたが何をしたいのか（ルール）の説明」が足りてません、という事です。

で。
説明を待っていても始まらないので、勝手にそーいうルールだとします。

作成例：
sub macro1()
　dim r as long
　for r = 1 to range("A65536").end(xlup).row
　　cells(r, "B").resize(1, 2).replace _
　　　what:=right(cells(r, "B"), len(cells(r, "A"))), _
　　　replacement:=cells(r, "A"), _
　　　lookat:=xlpart
　next r
end sub

＃参考
ご質問に書かれてる（「説明のための（事実と違う？）」）サンプルからでは、実はこーでもいいと読み取れます。

sub macro2()
　dim r as long
　for r = 1 to range("A65536").end(xlup).row
　　cells(r, "B") = "RSS" & cells(r, "A")
　　cells(r, "C") = cells(r, "B") & "+RSX" & cells(r, "A")
　next r
end sub

実際にヤリタイ事に応じて、適切に応用してください。

補足

驚速でのご返答アドバイスありがとうございます。

説明不足大変失礼致しました。
逆に分かりにくくなるのではと思っておりましたが、お言葉に甘えて実際にやりたいことを書きますと、

・Ａ列セルに4桁の数値が入っている。
・Ｂ列～〇列のセルに色々な式や文字列が入っている。
・Ｂ列～〇列のセルの中には特定の4桁の数値が入っている。
・上記条件で、同じ行の単位でＢ列～〇列のセル内に含まれる特定の4桁の数値をＡ列の数値に置き換えたい。

です。

改めて例を希望に近い形で書きますと、

●変換前
　　　　Ａ列　　　Ｂ列　　　　　Ｃ列　　　　Ｄ列　・・・・
１行　　1111　　=RSS9999　　xy9999　　b9999
２行　　2222　　bdr9999x　　c9999t　　=RSS9999
３行　　3333　　bne9999y　　9999yr　　kys9999+ki9999
・
・

●変換後
　　　　Ａ列　　　Ｂ列　　　　　Ｃ列　　　　Ｄ列　・・・・
１行　　1111　　=RSS1111　　xy1111　　b1111
２行　　2222　　bdr2222x　　c2222t　　=RSS2222
３行　　3333　　bne3333y　　3333yr　　kys3333+ki3333
・
・
Ａセルが空白になるまで。

となり、人間が一行ずつやるなら（Ｄ列までとするなら）
Ｂnセル～Ｄnセルを範囲選択して、特定数値である9999をＡnセル数値で置換機能で置換を繰り返す非常に簡単な置換作業になります。

しかし、変換したい対象が数百行×数十列なのでプログラムでやらないと厳しいと思ったのですが、知識不足で頓挫しました。

大変お手数をおかけいたしますが、アドバイス頂けると助かります。
もし可能であれば、行は空白になるまで、列はＤ列までという前提で教えて頂けるとより助かります。

宜しくお願い致します。