エクセルの置換

質問

m行n列の表があります。
第1行はタイトルで、
第2行の各列には　(2，a)セルに関するデータが　それぞれ入力されています。
第3行以降は、a列にのみコードが入力されていますが、b列以降は空欄です。

第2行のデータ中の　(2，a)セルの文字を、　各行について　(m，a)セルの文字に置換しながら第2行を各行にコピーして、(3，b)から(m，n)の空欄を埋めて表を完成させたいのです。

できれば、第2行の各列の書式も含めて第3行以降の各列にコピーするというVBAを教えていただきたくよろしくお願いします。

回答No.4

imogasi · ベストアンサー

#1です。
当初（実行前）
A1:D１０
座席案内表
住所１ 氏名 座席 案内
東京都 山田
神奈川県 小島
大阪府 淀橋
東京都 秋葉
東京都 小川
神奈川県 大島
神奈川県 中島
大阪府 木村
（プログラム）
Sub test02()
m = Range("A65536").End(xlUp).Row
x = Array("都", "道", "府", "県")
y = Array(3, 5, 5, 8)
MsgBox m
For i = 3 To m
Cells(i, "C") = "座席区分：" & Cells(i, "A")
Cells(i, "D") = Cells(i, "A") & "の席にご着席ください"
For k = 0 To 3
p = InStr(Cells(i, "D"), x(k))
If p <> 0 Then
Cells(i, "D").Characters(1, p).Font.ColorIndex = y(k)
End If
Next k
Next i
End Sub
（実行後）A1:D10の部分
座席案内表
住所１ 氏名 座席 案内
東京都 山田 座席区分：東京都 東京都の席にご着席ください
神奈川県 小島 座席区分：神奈川県 神奈川県の席にご着席ください
大阪府 淀橋 座席区分：大阪府 大阪府の席にご着席ください
東京都 秋葉 座席区分：東京都 東京都の席にご着席ください
東京都 小川 座席区分：東京都 東京都の席にご着席ください
神奈川県 大島 座席区分：神奈川県 神奈川県の席にご着席ください
神奈川県 中島 座席区分：神奈川県 神奈川県の席にご着席ください
大阪府 木村 座席区分：大阪府 大阪府の席にご着席ください
なお、上記プログラムでは、府県の文字には色をつけてます。
For k = 0 To 3
p = InStr(Cells(i, "D"), x(k))
If p <> 0 Then
Cells(i, "D").Characters(1, p).Font.ColorIndex = y(k)
End If
Next k
の部分は府県名に色分けをしているところです。文字データだけなら
この部分不要。
これでよろしいでしょうか。（色づけを除くと）実質２行で、あっけないものです。補足を読んだが、これ以上理解が進みません。

お礼

ありがとうございました。
D列の都道府県名前後の “ ” マークが抜けたりしていますが、もとの表の列構成（列の数、列の内容）が決まっているときは、これでほぼOKです。
ｐが0でないときに以下の処理をする、なども参考になりました。

以下は欲を言えばということですが、基本的な考え方としては大事なことだと思います。
列の構成を変更しても修正の必要がない、すなわち汎用性の高いマクロが目標です。技術やプログラムの開発で個別対応が必要な場合もありますが、より汎用性の高いものが望まれることが多いと思います。特に特許などでは個別対応の表現にしてしまうとずいぶん価値が下がってしまいます。
今回のマクロでは、例えばプログラム7行目の“座席区分：”という文字は“C2セルからA2セルを差引いた文字列”とすれば、プログラムの中に具体的な文字“座席区分：”は不要でしょうし、プログラム8行目も同様です。
VBAを適用するエクセル表のどの列についても同じことが言えるので、具体的な文字列を含まないでかつ列番号にｍなどを当てはめて処理できればVBAの汎用性は一挙に高まります。また「D列の“ ”マークが抜けている」というようなケースごとのケアレスミスも防げるでしょう。

これは1回目の補足の中で、「私が思うには、・・・」以下の数行で述べた内容そのものになります。シンプルなのがベストでしょう、たぶん。もっとも始めから承知の上、かも。でもシンプルだからこそ、人の意図しているところを正確に掴んで体現するのが難しいということもあります。

マクロは面白そうだし、工夫のし甲斐があるようです。参考になるサイトや例もたくさんあって助かりますが、すぐに横道にそれて入り浸ってしまうのが私の悪い癖でたいへん時間がかかります。でも、これも今後の基礎固めに役立つのかも知れません。
今回の目的にドンぴしゃの例はまだ見つかっていませんが、ReplaceやFind を利用した近いものもあるようです。
私がVBAを使いこなせるにはもう少し基礎と経験が必要で、まだ時間がかかります。VBA上級者の imogasi さんにもう一考いただいて、すっきりした上級者のVBAをご教示いただければありがたい・・・と期待しています。

補足

できましたー。ちょっと時間がかかったが、2行目とA列だけが特別、(2，1)セルがキーセルで、他には特別なセルや文字列はありません。A列を含めて途中に空欄があってもOKです。自作VBAのほぼ第1作品、記念作です。
他のサイトを含め、いつも利用するばかりでしたので、参考までに記念作を披露します。他所で参考にしたのは2行だけで、他は imogasi さんの回答の中からの応用です。
だいぶん道草食いましたが、知識は増え、今後いろんな場面でVBAの応用ができそうに思います。もっともこの欄で、回答者の側に立てるまでにはあと100倍（2桁）は早く組めないといけませんね。

Sub Test()

m = Range("A65536").End(xlUp).Row
n = Range("IV2").End(xlToLeft).Column

For i = 3 To m
For k = 2 To n

If Cells(i, 1) <> "" Then
If Cells(2, k) Like "*" & Cells(2, 1) & "*" Then

Cells(i, k) = Replace(expression:=Cells(2, k), Find:=Cells(2, 1), Replace:=Cells(i, 1), compare:=vbTextCompare)

End If
End If

Next k
Next i

End Sub

回答No.3

imogasi

＃１，２です。
まだ良く説明が理解できません。
以下個別に１つづつ、補足してください。
第３行から第ｍ行までについて
（１）A列はそのまま、VBAで何も手を加えないのですね？
（２）B列もそのままですね？
　　　　ただし、質問文の「B列以降は空欄です」と矛盾するが。
（３）C列　－　A列の文字列をそのまま持ってくる？？。
「座席区分」の文字を添えるのか？。
（４）D列　ー　A列の文字列をダブルクオートで囲む＋”の積にご着席ください”を入れるで良いか。
（５）空欄とは３－ｍ行で、かつC列ーD列のことか？
（６）書式とは、どんな書式ですか。文字列の書式には色やフォントサイズぐらいしか考え着かないが。
（７）第１行は見出しですね。第２行目の役割は何でしょう。例示ですか？

補足

分かりにくい説明のようで恐縮です。以下問い合わせ番号に対応して補足します。
（1）A列は個別に手入力されます。VBAでは何も手を加えない、というか、情報の提供元となります。
（2）B列も手を加えません。ご指摘のとおり、最初の質問文とは矛盾してしまっています。申し訳なし。
また、実際にはE列にも手入力の担当欄があったりしますが、今回の例では省略しています。B2セルやE2セル(但し今回は省略)には、置換の対象となるべきA2セルと同一の文字列（東京都）が入っていないので、B列やE列はもともとVBAの操作対象外です。
B列も今回省略したE列もVBAの操作対象外となることから、最初の質問文では安易にB列以降は空欄と書いてしまいました。正しくありませんでした。
また逆に、案内2などの操作対象となる列が加わることもありますが、その場合にはその列の第2行のセルには2Aセルと同じ文字列（今回は東京都）が含まれます。
（3）C列は、A列の内容によって　　座席区分：神奈川県　とか　座席区分：大阪府、または座席区分：東京都　　などの文字列で構成されます。
（4）D列もC列と同様に、”神奈川県”の席にご着席ください　　などの文字列で構成されます。
他の文字や記号が使われることもありますが、第2行で、　2Aセルの東京都の文字列と　同じ文字列を含む列だけがVBAの操作対象となり、そのセルの中の　東京都　の文字列部分だけが神奈川県や大阪府の文字列に置換えられて、第3行以降にコピーされます。
（5）空欄とはこの例では、ご指摘のとおりです。B列も、今回省略したE列の担当欄もVBAの操作対象にはならないことから、最初の質問文では大雑把に「B列以降は空欄です」と書いてしまいました。
（6）書式とは、文字色、フォントサイズ、太字、背景色です。
（7）第2行は例示です。1行手入力するだけで、何種類であっても、またどんな並び順であってもA列の文字列にしたがって正しく表の空欄が埋められて行きます。但し今回省略した担当欄で、未定のため空欄になっているところもありますが、もともとVBAの操作対象外ですから、空欄のまま放置されます。
なお、C列やD列の何行目かに予め何か手入力されていても、それは無視して、この例ではC列とD列はVBAの操作対象であることを優先し、東京都の文字を単純にA列の文字で置換えたもので満たされます。
一覧表として管理したり、切り離して使う場合でも数を間違えずにチェックできます。

回答No.2

imogasi

＃１です。
私の回答はCells(2,"A")
A2　セルの内容を全てに代入してます。
＞(2，a)セルの文字を
の具体的な意味内容が理解できなかったので、手を抜いたまでです。
テストしてくださったときのA2セルがスペースであったために、テスト結果で何も見えないのでしょう。
そこは自分に合わせて修正することができるお方という仮定でした。
今もわからないままなので、本当に表にセットしたい中身を説明してくだされば（一部実例でも上げるとか）考えますが。

補足

なるほどねー　A2セルが空欄…。その通りですね。

一部の例を示します。この例に先のマクロを適用すると、既に記入されている氏名欄も東京都に置き換えられてしまいますが、既に記入されているセルは置換えないようにお願いします。
なお、この補足欄ではスペースが無視されてしまうようですが、住所１、氏名、座席、案内の4列があり、それに対応して第2行が埋められています。第3行以降は住所1の他、氏名欄がBCDEFGHIで埋められています。

住所１ 氏名 座席 案内
東京都 A 座席区分：東京都 ”東京都”の席にご着席ください
神奈川県 B
大阪府 C
東京都 D
東京都 E
神奈川県 F
神奈川県 G
大阪府 H
東京都 I

回答No.1

imogasi

抽象化と一般性を失わない記述で質問を述べられていて、プログラマに適した才能のおありの方と思いますが、回答があがりませんね。
私の質問文の理解では
Sub test02()
m = Range("A65536").End(xlUp).Row
MsgBox m
n = Range("IV2").End(xlToLeft).Column
MsgBox n
For i = 3 To m
For j = 2 To n
Cells(i, j) = Cells(2, "a")
Cells(i, j).NumberFormat = "#,##0"
Next j
Next i
End Sub
となりましたが、この内容だと、質問されるようなものと思えないので、自信ありません。
良ければ間違っているところ（というか実行結果がこうなるが、そうではなくて、こうありたい）を補足してみてください。

お礼

補足欄の記入、間違えました。
何の反応もなかったというのは、表のセルがひとつも埋められなかったということです。正しく埋められるか、間違って埋められるかも含めて、反応がなかったということです。
メッセージボックスの簡単な使い方は参考になりました。今後使える場面では応用してみようと思っています。

補足

早々に回答をもらっておきながら、補足やお礼が遅くなってしまいました。
私はエンジニアとしての経験はあっても、プログラムについては初心者の域を出ないので思うように検討が進まない上に、落ち着いて考えられるまとまった時間が取りにくかったもので遅くなってしまいました。

今回教えてもらったVBAは何の反応もなく動きませんでした。なぜ反応がないのかは分かりません。

私が思うには、セル(2,B) にセル(2,A)と同じ文字列が含まれているかどうかを判定し、
もし含まれているのなら、その文字列部分だけを置換してセル(3,B) に書き、もし含まれていないならセル(2,B)の内容をそのままセル(3,B)に書く。
これをn列繰り返し、またm行繰り返せば良いのだと思います。
そうは思いながらも、経験が足りないので思うようにプログラムが組めず、デバッグが必要な領域から抜け出せないで悪戦苦闘している状況です。

プログラムでは1文字違うだけで“デバッグ”が要求されますが、どの文字が違っているのか、あるいは全体が間違っているのかなど、初心者にはつかめません。そこで上級者のプログラムを参考にさせてもらえればありがたいと思い質問している次第です。

Range　と　Cells　の使い分けはどういう意味があるのかなという新たな疑問も持ってしまいました。一歩ずつ時間がかかります。