[[20160208105233]] 『ブック全体の特定の文字列を複数置換したい』(さんま) ページの最後に飛ぶ

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

 

『ブック全体の特定の文字列を複数置換したい』(さんま)

特定の文字列をブック全体置換したいです。

置換したい文字列が複数あります。

別ブックの参照シートのA列に置換後の文字列、B列に置換前の文字列が入っています。

それにならってアクティブブックの全シートのC列の値を置換したいです。

宜しくお願い致します。

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


 置換したい文字は、文章の一部分ですか? 
 それとも置換対象の文字のみ記入されていますか?

 できるだけ具体的にデータ提示頂ければ誤解等なく、スムーズに問答できるのですが・・・
(稲葉) 2016/02/08(月) 11:11

稲葉さん、コメントありがとうございます。

置換したい文字は、文章の一部分ですか? 

 それとも置換対象の文字のみ記入されていますか?

↑置換対象の文字のみ記入されてます。

やりたいことは新しいシステムにデーターを移行する作業をしようとしてます。
C列に記入されている 多数のメーカー名を別ブック参照シートに明記してある数字に置き換えたいのです。

すみませんが、宜しくお願い致します。

(さんま) 2016/02/08(月) 11:16


 別ブックのA列とB列を入れ替えて、
 置換対象が入力されている表に一列追加し、
 VLOOKUPで表示させた後、値に置き換える方法はどうですか?
(稲葉) 2016/02/08(月) 11:38

稲葉さんコメントありがとうございます。

その方法も考えたのですが、ボタン一つで作業ができればとかんがえておりました。

やはり難しいでしょうか??
(さんま) 2016/02/08(月) 13:02


別ブックに変換辞書定義せず、自ブック内に置けば簡単なのですが。
まずは、ご自分で調べて、できるところまで作成して欲しいですね。

 Private Sub CommandButton1_Click()
    Dim myDIC As Object
    Dim i As Long
    Dim j As Long
    Dim D As Variant
    Dim K As Variant

    Set myDIC = CreateObject("Scripting.Dictionary")
    myDIC.CompareMode = vbTextCompare

    With Workbooks.Open(ActiveWorkbook.Path & "\別ブック.xlsx", False, True)
        With .Sheets("参照シート")
            For i = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
                If Not myDIC.Exists(.Cells(i, "A").Value) Then
                    myDIC.Add .Cells(i, "A").Value, .Cells(i, "B").Value
                End If
            Next i
        End With
        .Close
    End With

    D = myDIC.Items
    K = myDIC.keys

    For i = 1 To Sheets.Count
        If Sheets(i).Name <> Me.Name Then
            With Sheets(i)
                For j = 0 To myDIC.Count - 1
                    .Range("A1", .Cells.SpecialCells(xlLastCell)).Replace K(j), D(j), xlPart
                Next j
            End With
        End If
    Next i
 End Sub
(???) 2016/02/08(月) 13:16

 一回限りだよね?
 問答してる時間、というか10分の作業で済むことを人の手を借りてなんとかしようってのがおかしい・・・
 関数なら間違えたことに気づくけど、VBAで出力した結果を検証する時間のほうが長いと思うのだが。
(稲葉) 2016/02/08(月) 14:12

まぁ、手作業するには対象文字列数が多いのだろう、と推測。

ところで、A列とB列の関係を逆に考えていました。すいませんが、"A"と"B"を逆にしてください。
なお、C列に限らず、全セルを変換するコーディングになっています。都合が悪い場合は、
Range指定してる箇所を調整してください。
(???) 2016/02/08(月) 14:31


稲葉さん、???さん、コメントありがとうございます。

ブック一つに対して変換作業を行う回数は1回限りなんですが、変換作業するブックが300あたりあります。

それと???さんの推測通り、対象文字列が50弱あるので。。。。

なるほど!! A列B列が反対なんですか。調整します。

C列と指定しましたが、全て対象でも問題ないのでそもまま使わせて頂きます。

それと、
If Sheets(i).Name <> Me.Name Then

↑のMEの対象がありません。と出てしまうので、Me.Nameを  Activesheet にしてみたのですが、
うまくいきません。。。
このVBAの置き場所はActivebookでいいのですよね???

(さんま) 2016/02/08(月) 14:55


ボタンひとつで…、との事でしたから、シートにActiveXのボタンを配置し、ここにコーディングする事を想定しました。
Meがエラーになるということは、標準モジュールに貼り付けていませんか? ActiveBookの場合、後から開いたブックがアクティブになるので、間違いですよ。

ブックが多数あるというならば、辞書のあるブックにマクロを記述し、ボタン一発で指定フォルダ以下全てのブックの文字列置換を
する方が楽だと思いますよ。300回もボタン貼って、マクロコピーして、ボタンを押す作業をするのでしょうか?
(???) 2016/02/08(月) 15:10


???さん、コメントありがとうございます。

考察不足でした。。その通りですね。。

私の考えではエクセルのリボンにVBAを登録しておいてその作業の時にvbaを呼び出して作業!
という考えでした。

確かに???さんの言う通り、一回の作業で済めば一番いいです。

とりあえず、自分なりにVBAを考えてみたいと思います。

アドバイス有難うございました。

(さんま) 2016/02/08(月) 15:20


置換の件ですが、置換する文字列が完全一致の場合に置換することもできるんですか?
ただ今作成平成日付を西暦に変換するVBAも組んでいるのですが、実行すると
例えば
H22→H2+2と認識されてしまい19902となってしまいます。

完全一致の場合と指定することはできますか???

(さんま) 2016/02/08(月) 18:18


 横から失礼します。

 >>ただ今作成平成日付を西暦に変換するVBAも組んでいるのですが、実行すると ・・・H22→H2+2と認識されてしまい19902となってしまいます。 

 そのコードをアップしてもらわないことにはコメントができませんねぇ。
 正しくロジックを組めば正しく変換されるはずですので。

 あぁ、それと、変換対象の 平成なんとか というデータは 文字列なんですかね?
 それとも、日付型データで、表示書式で 平成何とか と表示されているだけなんですかね?

(β) 2016/02/08(月) 22:05


私のサンプル中で、myDIC.CompareMode = vbTextCompare という行があります。
これは変換対象をテキスト扱い、つまり大文字小文字の区別無く変換する指定。
この行を消せば、デフォルトのバイナリ指定になり、完全一致した場合だけ置換されます。
(または、vbBinaryCompare)

H22の文字列ですが、セル中の文字列の一部ではなく、指定単語のみ格納されているならば、
変換文字列の先頭に「'」を付けてみてはいかがでしょうか。
(???) 2016/02/09(火) 09:21


βさま、???様、コメントありがとうございます。

???様が作成して頂いたvbaを少し変更して作ってみましたが、やはり問題解決されません。。。

β様
あぁ、それと、変換対象の 平成なんとか というデータは 文字列なんですかね?

 それとも、日付型データで、表示書式で 平成何とか と表示されているだけなんですかね?
↑文字列です。H22 という形で文字列で入っております。

???様
私のサンプル中で、myDIC.CompareMode = vbTextCompare という行があります。
これは変換対象をテキスト扱い、つまり大文字小文字の区別無く変換する指定。
この行を消せば、デフォルトのバイナリ指定になり、完全一致した場合だけ置換されます
↑これを削除してみたのですが、できませんでした。。。

ちなみに指定単語のみの格納です。
’とつけるということは表中のすべてのH〇〇というところを’H〇〇にするということでしょうか???

Sub 西暦変換()

    Dim myDIC As Object
    Dim i As Long
    Dim j As Long
    Dim D As Variant
    Dim K As Variant

    Set myDIC = CreateObject("Scripting.Dictionary")
   ' myDIC.CompareMode = vbTextCompare

    With Workbooks.Open("\\モジュール\西暦に変更.xlsx", False, True)
        With .Sheets("西暦変換")
            For i = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
                If Not myDIC.Exists(.Cells(i, "A").Value) Then
                    myDIC.Add .Cells(i, "A").Value, .Cells(i, "B").Value
                End If
            Next i
        End With
        .Close
    End With

    D = myDIC.Items
    K = myDIC.keys

    For i = 1 To Sheets.Count
            With Sheets(i)
                For j = 0 To myDIC.Count - 1
                    .Range("A1", .Cells.SpecialCells(xlLastCell)).Replace D(j), K(j), xlPart
                Next j
            End With
    Next i
 End Sub

やはり
H22→H2+2と認識されてしまい19902となってしまいます。

(さんま) 2016/02/09(火) 13:40


H2+2になんて、どうやっても変わらないのですが…? 再現する変換元と先、セルに入力されている文字列と書式設定を教えてください。
また、問題の文字列を新規ブック、新規シートに転記し、シートモジュールに以下のマクロを貼り、再現確認してみてください。

 Sub test()
    Range("A1", Cells.SpecialCells(xlLastCell)).Replace "平成22", "H22", xlPart
 End Sub
(???) 2016/02/09(火) 14:22

???様、コメントありがとうございます。

問題の文字列を新規ブック、新規シートに転記し、シートモジュールに以下のマクロを貼り、再現確認してみてください。
↑再現確認してみたところ、特になにも起こりませんでした。

再現する変換元と先、セルに入力されている文字列と書式設定を教えてください。

再現する変換元データー
A列  B列
1965  S40
1966  S41
1967  S42
1968  S43
.    .
.    .
.    .
2016  H28

変換先データー
A列、B列は日付等と関係ない文字列がはいっています。

C列
H24
H22
H20
S41
S48
.
.
.

↑このように形式は標準で入っております。

例えばH22ならば2010に変換したいのですが、19902(つまりH2と2と認識される)になってしまうというわけです。

すみませんがアドバイス宜しくお願い致します。

(さんま) 2016/02/09(火) 14:36


定義データを見せてもらって、ようやく理解…。

年の古いものから並んでいますが、それだとH22より前に、H2とマッチしてしまいますよね?
変換表を降順で並び替えてから、置換実行してみてください。
(???) 2016/02/09(火) 14:53


???様、コメント有難うございます。

なんと!!そういうことですか!!!
ただ今変換表を降順に並び替えてから実行したところ、思った通りに出来ました!

無事に解決しました。
本当にありがとうございました。
感謝しております。

また何かありましたら宜しくお願い致します。

(さんま) 2016/02/09(火) 15:31


上手くいったようなので、フォルダ以下の全てのブックの文字列置換する例なぞ。

ネットワークドライブ上のファイルを対象に変換するようですが、速度のためと、
失敗した場合を考えて、ローカルにファイルコピーしてから試してください。

西暦変換シートにActiveXのボタンを貼り付け、以下のマクロとしてください。
マクロ入りになるので、ブックはxlsmファイル保存してください。
実行すると、カレント以下のサブフォルダも含め、全て文字列置換します。

 Private Sub CommandButton1_Click()
    Dim myDIC As Object
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim DT As Variant
    Dim KY As Variant
    Dim cFiles As Variant

    Set myDIC = CreateObject("Scripting.Dictionary")
    ' myDIC.CompareMode = vbTextCompare

    With Sheets("西暦変換")
        For i = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
            If Not myDIC.Exists(.Cells(i, "B").Value) Then
                myDIC.Add .Cells(i, "B").Value, .Cells(i, "A").Value
            End If
        Next i
    End With

    DT = myDIC.Items
    KY = myDIC.keys

    cFiles = Split(CreateObject("WScript.Shell").Exec("CMD /C DIR /A:-D/B/S """ & ActiveWorkbook.Path & "\*.xls*""").StdOut().ReadAll(), vbNewLine)
    For i = 0 To UBound(cFiles) - 1
        If Not Mid(cFiles(i), InStrRev(cFiles(i), "\") + 1) Like "*" & Me.Parent.Name Then
            With Workbooks.Open(cFiles(i))
                For j = 1 To .Sheets.Count
                    With .Sheets(j)
                        For k = 0 To myDIC.Count - 1
                            .Range("A1", .Cells.SpecialCells(xlLastCell)).Replace KY(k), DT(k), xlPart
                        Next k
                    End With
                Next j
                .Save
                .Close
            End With
        End If
    Next i
 End Sub
(???) 2016/02/09(火) 16:03

コメント返信:

[ 一覧(最新更新順) ]


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