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

質問

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

回答No.3

ja7awu · ベストアンサー

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

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

１．Alt + F11 で VBE(Visual Basic Editor)を開きます。
２．VBE のメニューから[挿入] -->[標準モジュール] を指定します。
３．モジュールウィンドウに下記コードをコピーして貼り付けます。
４. コードの 3～5 行目の「設定事項」を実情に合わせて変更します。
　(今は、例えばの　=int(sum(a1:a10))→=int(average(a1:a10))にしています。)
５．Alt + Q （または、右上隅の×）でウィンドウを閉じ、シートに戻ります。
６．もし、保存して何回か使用するときは、メニューから[ツール]-->[マクロ]-->
　[セキュリティ]で「セキュリティレベル」を「中」にして[OK]します。
７．実行するときは、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

お礼

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

回答No.2

sakeman

[Ctrl]＋[G]（ジャンプ）→[ジャンプ]→[セル選択]→[選択オプション]→[数式]をチェック→[ＯＫ]
数式の入っているセルが選択される。

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

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

お礼

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

回答No.1

neKo_deux

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

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

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

参考URL：

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

お礼

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