連載
» 2016年08月18日 05時00分 公開

Excelの醍醐味! 明日からできるデータの並べ替え、検索、置換、抽出Excelマクロ/VBAで始める業務自動化プログラミング入門(7)(3/5 ページ)

[薬師寺国安,PROJECT KySS]

データの置換

 セルに入力されたデータの一部を置換するには、Replaceメソッドを使用する。何万件もあるデータをいちいち手動で書き換えていたら、それこそ時間ばかりが経過して日が暮れる。非効率この上ない。そういったデータを書き換える場合にReplaceメソッドは力を発揮する。

Replaceメソッドの使い方

 Replaceメソッドの書式は下記の通りだ。

Replaceメソッドの書式

Rangeオブジェクト.Replace What:={検索する文字(必須)},Replacement:={置き換える文字(必須)},LookAt:={LookAt}


表9 {LookAt}に指定する値
設定値 説明
xlWhole 完全一致
xlPart 部分一致(既定値)

データを置換してみよう

 図7のようなデータがあったとしよう。

図7 社員データがあり複数の「宇和島蜜柑」の氏名が存在する

 図7の「宇和島蜜柑」の名前を「宇和島鯛めし」に置換してみる。「検索する文字列」の「D3」セルに「検索する文字列」を入力して「実行」ボタンをクリックする。入力ダイアログボックスが表示されるので、「鯛めし」と入力して「OK」ボタンをクリックする。

 すると、「宇和島蜜柑」が「宇和島鯛めし」に全て置換される。{LookAt}に指定する値は部分一致の「xlPart」を指定している。これは既定値であるため、指定しなくても構わない。

 この置換を行うマクロはリスト5のようになる

Sub 置換()
  If Range("D3").Value = "" Then
    MsgBox "検索文字列を入力してください。"
  Else
    Dim resultStr As String
    resultStr = InputBox( _
      Prompt:="置換文字列を入力してください。", _
      Title:="検索文文字列は=" & Range("D3").Value)
    Range("D7:D19").Replace _
      What:=Range("D3").Value, _
      Replacement:=resultStr, _
      LookAt:=xlPart
  End If
End Sub
リスト5 置換

 「検索する文字列」が入力されていない場合(3行目)は、警告するメッセージボックスを表示させる(4行目)。入力されていた場合(5行目)は、置換文字列を入力する入力ボックスを、「InputBox」を用いて表示させる(7〜9行目)。

 Replaceメソッドの「What」にD3セルの値を指定し(11行目)、「Replacement」に入力ボックスに入力した値を保持している変数resultStrの値を指定する(12行目)。「LookAt」には「部分一致」を表す「xlPart」を指定した(13行目)。

 実行結果は図8のようになる。

図8 社員データの「宇和島蜜柑」の氏名が「宇和島鯛めし」に全て置換された

※自分で考えてマクロを書いてみよう

 なお、「読み」は以前の「うわじまみかん」のままになっている。これも「うわじまたいめし」に置換する必要がある。これについては、ぜひ読者の皆さまに実際に自分でリストを見ながらVBEを起動してマクロを書くことに挑戦していただきたい。


ReplaceFormatプロパティで検索結果のセルの背景色を変更

 次は、「検索する文字列」に入力された文字列を検索して、その該当するセルの背景色を変更させてみる。セルの背景色など「セルの書式」(ここでの「書式」は、表示形式)については、連載第5回の「セルの数値、フォント、文字位置、背景色、けい線など表示形式/書式設定の使い方」を参照してほしい。

ReplaceFormatプロパティの使い方

 検索した「セルの書式」を変更するには、ReplaceFormatプロパティを使用する。ReplaceFormatプロパティの書式(使い方)は下記の通りだ。どのセルが変更の対象となったかを一目瞭然で確認できる。

ReplaceFormatプロパティの書式(使い方)

Applicationオブジェクト.ReplaceFormat.Font.FontStyle={フォントのスタイル}

Applicationオブジェクト.ReplaceFormat.Interior.Color={セルの塗りつぶし色}

Wnd With


  • {フォントのスタイル}には「Bold」(太字)、「Italic」(斜体)、「Underline」(下線)などが指定できる
  • {セルの塗りつぶし色}にはRGBカラーを指定

ReplaceFormatプロパティを使ってみよう

 では、図8の「宇和島鯛めし」の文字を「宇和島蜜柑」に置換し、文字を「斜体」に、セルの塗りつぶしを「赤」にしてみよう。その後「書式を元に戻す」ボタンで元の状態に戻す。マクロはリスト6のようになる。

Sub 検索結果のセルの書式を変える()
  With Application.ReplaceFormat
    .Font.FontStyle = "Italic"
    .Interior.Color = RGB(240, 0, 0)
  End With
  
  If Range("D3").Value = "" Then
    MsgBox "検索文字列を入力してください。"
  Else
    Dim resultStr As String
    resultStr = InputBox( _
      Prompt:="置換文字列を入力してください。", _
      Title:="検索文文字列は=" & Range("D3").Value)
    Range("D7:D19").Replace _
      What:=Range("D3").Value, _
      Replacement:=resultStr, _
      LookAt:=xlPart, _
      ReplaceFormat:=True
  End If
End Sub
リスト6 検索結果のセルの書式を変える

 Application.ReplaceFormatで書式を設定しておく(2〜5行目)。置換した後の文字を「斜体」にし(3行目)、セルの塗りつぶし色を「赤」にしておく(4行目)。その後、検索と置換を実行し、「ReplaceFormat:=True」と指定して書式の適用を実行する。

 実行した画面は図9になる。

図9 社員データの「宇和島鯛めし」の氏名が斜体で「宇和島蜜柑」に全て置換され、セルの書式が変更された

 次に「書式を元に戻す」ボタンで、「宇和島蜜柑」を「宇和島鯛めし」に置換して、セルの書式を元に戻す。マクロはリスト7だ。

Sub 書式を元に戻す()
  With Application.ReplaceFormat
    .Font.FontStyle = "Normal"
    .Interior.Color = RGB(255, 255, 255)
  End With
  
  If Range("D3").Value = "" Then
    MsgBox "検索文字列を入力してください。"
  Else
    Dim resultStr As String
    resultStr = InputBox(
      Prompt:="置換文字列を入力して下さい。", _ 
      Title:="検索文文字列は=" & Range("D3").Value)
    Range("D7:D19").Replace 
      What:=Range("D3").Value, _
      Replacement:=resultStr, _
      LookAt:=xlPart, _
      ReplaceFormat:=True
  End If
End Sub
リスト7 書式を元に戻す

 リスト6とほとんど同じ内容なので説明は省略する。ReplaceFormatプロパティで、文字のスタイルを「Normal」に(3行目)、セルの塗りつぶし色を「白」にしている(4行目)点が、リスト6と異なる点だ。実行すると、図10のようになる。

図10 社員データの「宇和島鯛蜜柑」の氏名が「宇和島鯛めし」に全て置換され、セルの書式が元に戻った

Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。