こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

エクセルで複数ファイルのセルの数式を一部置換

同じフォルダ内にある複数のエクセルファイルを1セルの一部分の数式を一括で置換したいのですが・・・
例えば =int(sum(a1:a10))→=int(average(a1:a10))
sumをaverageに置換可能なのでしょうか?
ファイルが大量にあるため全部のファイルをまとめて置き換えが出来ればと思いまして!よろしくお願いします。

投稿日時 - 2005-06-10 14:47:49

QNo.1440616

暇なときに回答ください

質問者が選んだベストアンサー

次のマクロを実行すると、ご希望の置換え状態になるかと思います。
宜しかったら、新規ブックでテストしてみてください。

一応、ご存知とは思いますが、操作手順を詳しく書いておきます。

1.Alt + F11 で VBE(Visual Basic Editor)を開きます。
2.VBE のメニューから[挿入] -->[標準モジュール] を指定します。
3.モジュールウィンドウに下記コードをコピーして貼り付けます。
4. コードの 3~5 行目の「設定事項」を実情に合わせて変更します。
 (今は、例えばの =int(sum(a1:a10))→=int(average(a1:a10))にしています。)
5.Alt + Q (または、右上隅の×)でウィンドウを閉じ、シートに戻ります。
6.もし、保存して何回か使用するときは、メニューから[ツール]-->[マクロ]-->
 [セキュリティ]で「セキュリティレベル」を「中」にして[OK]します。
7.実行するときは、Alt + F8 (メニューから[ツール]-->[マクロ]-->[マクロ])で
 「フォルダ内全ブック全シート計算式置換え」を指定し、[実行]ボタンを押します。

これで、フォルダ内の全ブックの計算式が指定したものに全部置換えになると思います。

・ 検索対象ブックのシート内で、他ブックのセル参照が設定されている場合は、
  その旨のダイアログボックスが表示されます。(手動で対応)

・ ブックやシートが保護によって書き換えられない状態が、無いものとします。

・ 検索を終了すると、検索対象ブック数と、置換え総件数が表示されます。


Sub フォルダ内全ブック全シート計算式置換え()
'--- 設定事項 -----------------
Const Mypath = "C:\DATA\" '             <--- 検索フォルダ名を指定
Const OldStr = "=INT(SUM(A1:A10))" '       <--- 置換え旧計算式(部分可)
Const NewStr = "=INT(AVERAGE(A1:A10))" '   <--- 置換え新計算式(部分可)
'-----------------------------
Dim WB As Workbook
Dim Rng As Range
Dim FName As String
Dim Bcnt As Integer
Dim Dcnt As Integer
Dim Scnt As Integer
Dim N As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
FName = Dir(Mypath & "*.xls", vbNormal)
Do While FName <> ""
  Set WB = Workbooks.Open(Mypath & FName)
  Bcnt = Bcnt + 1
  Scnt = 0
  Windows(WB.Name).Visible = False
  For N = 1 To WB.Worksheets.Count
    Set Rng = WB.Worksheets(N).Cells.Find(OldStr)
    If Not Rng Is Nothing Then
      Scnt = Scnt + 1
      Do
        Rng.Formula = Replace(Rng.Formula, OldStr, NewStr)
        Dcnt = Dcnt + 1
        Set Rng = WB.Worksheets(N).Cells.FindNext(Rng)
      Loop Until Rng Is Nothing
    End If
  Next N
  If Scnt = 0 Then
    WB.Close SaveChanges:=False
  Else
    Windows(WB.Name).Visible = True
    WB.Close SaveChanges:=True
  End If
  FName = Dir
  If FName = ThisWorkbook.FullName Then FName = Dir
Loop
Application.DisplayAlerts = True
Application.ScreenUpdating = True
If Bcnt = 0 Then
  MsgBox "指定したフォルダに、ブックは見つかりませんでした。", , "検索完了"
Else
  MsgBox Bcnt & " のブックを検索し " & Dcnt & " 箇所を置換えました。", , "置換え完了"
End If
Set WB = Nothing
Set Rng = Nothing
End Sub

投稿日時 - 2005-06-10 16:59:17

お礼

まず最初にお礼が遅くなりまして申し訳ございません。週末は自宅に不在でしたのでさきほどこちらのサイトをみました。
このマクロを実行すると、驚きました。一部を改正しただけで複数のファイルが一度で置き換えを行うことができました。勉強になりました。ありがとうございました。

投稿日時 - 2005-06-13 09:49:06

ANo.3

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(3)

ANo.2

[Ctrl]+[G](ジャンプ)→[ジャンプ]→[セル選択]→[選択オプション]→[数式]をチェック→[OK]
数式の入っているセルが選択される。

[Ctrl]+[H](置換)→[検索と置換]→[置換]→[検索する文字列]→「sum」と入力→[検索後の文字列]→「average」と入力→[すべて置換]→[閉じる]

これで可能かと思いますが、お試し下さい。

投稿日時 - 2005-06-10 15:23:45

お礼

まず最初にお礼が遅くなりまして申し訳ございません。Sakemanさんがおっしゃてるのは、たぶん単独のファイルの置き換えだと思います。私の質問は、数千もある大量のエクセルファイルを一度の操作で簡単に関数の数式を置き換えしたかったのです。私の質問がわかりにくかったもしれません。すいませんでした。何かありましたらよろしくお願いします。ありがとうございました。

投稿日時 - 2005-06-13 09:56:07

ANo.1

そういう業務向けのオンラインソフトがあります。
こちらが簡単そうです。

OfficeGrep
http://www.vector.co.jp/soft/win95/util/se190183.html

--
VBAを利用して操作を自動化することも可能です。
[ツール]-[マクロ]-[新しいマクロの記録]で個人用マクロブックに記録、それぞれのファイルを開いて実施とか、ファイルを開く~保存までもVBAで記述とか。

参考URL:http://www.vector.co.jp/soft/win95/util/se190183.html

投稿日時 - 2005-06-10 15:17:30

お礼

ありがとうございました。また何かありましたらよろしくお願いします。

投稿日時 - 2005-06-13 09:58:26

あなたにオススメの質問