【excel vba】エクセルファイル内にある数式の内「関数名(IF,SUM等)」のみを、同ファイル内の新しいシートに一覧化したいです。

Question

vba初心者です。(ネットからコードを拾ってきてちょっと改造できる程度)
excel 2003を使用しています。

【前提】
・「数式」「数値」「文字列」等がセルに入力されたエクセルファイルを使用する
　⇒「関数名」のみを表示する。(文字列や数値が入力されたセルは無視)
・「数式」セルには「関数」が使われているものと、そうでないものがある
・１セル内に複数の関数が使用されている場合あり(新出の関数名であればすべて抽出したい)
・検索対象シート：ブック内のすべてのシート


【質問】
findメソッドで「IF」や「SUM」というように直接関数名を指定して検索するのではなく、「関数」というククリで検索はできるのでしょうか？

その検索結果を同ファイル内に新しいシート(Sheet1)を作成し、「関数名」を一覧表示するという流れ(以下にまとめました)にしたいです。

【手順】
(1)Book1内で「関数」検索をする
(2)「関数」が見つかった場合は「Sheet1」シートを作成(関数が見つからない場合は,msgbox  "該当なし")
(3)検索した「関数名」をSheet1のA1セルに入力する
(4)Book1内すべて(複数シート有り)の関数名を抽出するまで連続検索をする
　⇒A1→A2→A3→…の様に、A列の上から順に入力していく
※関数名の重複がないように一覧化できれば最高です。
※シート毎に、抽出した関数名を分けなくて大丈夫です。(あくまでファイル全体で使用されている関数名の一覧)
(5)msgbox "終了"


解決方法をご存知の方、ご教示願えませんでしょうか。

宜しくお願いいたします。

KenKen_SP · Accepted Answer

#1 です。

なんか質問の形式をとった作成依頼っぽい気がしなくもない。一応
突っ込み入れときます。

こんな感じでできる気がする。適当に参照設定をして試してみて下さい。
ユーザー定義関数（日本語名を含む）があっても大丈夫かと。

余談：

vArray = Range("A1:C1").Formula

で Value 同様数式の2次元配列が得られますから、速度面が問題になる
ようなら配列処理に改造してみて。


Sub sample()

　　Dim dic　　　　 As Dictionary
　　Dim reg　　　　 As RegExp
　　Dim regMatch　　As Match
　　Dim s　　　　　 As String
　　Dim rHasFormula As Range
　　Dim r　　　　　 As Range
　　Dim sh　　　　　As Worksheet
　　　　
　　Set reg = New RegExp
　　Set dic = New Dictionary
　　
　　reg.Global = True
　　reg.Pattern = "([^!-@]+)"

　　For Each sh In ActiveWorkbook.Worksheets
　　Do
　　　　On Error Resume Next
　　　　' 23: xlErrors or xlLogical or xlNumbers or xlTextValues
　　　　Set rHasFormula = sh.Cells. _
　　　　　　　　　　　　　SpecialCells(xlCellTypeFormulas, 23)
　　　　On Error GoTo 0
　　　　If rHasFormula Is Nothing Then Exit Do
　　　　
　　　　For Each r In rHasFormula.Cells
　　　　　　' // 数式のセル参照文字をR1C1相対参照に固定する
　　　　　　s = Application.ConvertFormula(r.Formula, _
　　　　　　　　　　　　　　　　　　　　　 xlA1, _
　　　　　　　　　　　　　　　　　　　　　 xlR1C1, _
　　　　　　　　　　　　　　　　　　　　　 xlRelative)
　　　　　　For Each regMatch In reg.Execute(s)
　　　　　　　　Select Case UCase$(regMatch.Value)
　　　　　　　　' // セル参照文字やブール値を除外
　　　　　　　　Case "R", "C", "RC", "TRUE", "FALSE"
　　　　　　　　Case Else
　　　　　　　　　　' // Dictionary でカウントしつつ重複のないリストにする
　　　　　　　　　　 dic(regMatch.Value) = dic(regMatch.Value) + 1
　　　　　　　　End Select
　　　　　　Next
　　　　Next
　　　　
　　　　Exit Do
　　Loop
　　Next sh
　　
　　' // 出力(面倒なので適当）
　　With ThisWorkbook.Worksheets("Result")
　　　　.Activate
　　　　.Cells.Delete
　　　　With .Range("A1:B1")
　　　　　　.Font.Bold = True
　　　　　　.Value = Array("Function", "Count")
　　　　End With
　　　　.Range("A2").Resize(dic.Count).Value = Application.Transpose(dic.Keys)
　　　　.Range("B2").Resize(dic.Count).Value = Application.Transpose(dic.Items)
　　　　.Columns("A:B").AutoFit
　　End With

　　Set reg = Nothing
　　Set dic = Nothing
　　
End Sub

KenKen_SP · Answer

こんにちは。

数式をもったセルは SpecialCells で参照できます。

　　On Error Resume Next
　　' 23: xlErrors or xlLogical or xlNumbers or xlTextValues
　　Set rHasFormula = ActiveSheet.Cells. _
　　　　　　　　　　　SpecialCells(xlCellTypeFormulas, 23)
　　On Error Goto 0

この結果、オブジェクト変数 rHasFormula が Nothing であれば、

　　「関数を使ったセルは無し」

とみなせます。あとは、数式をもったセル rHasFormula を For Each で
順次処理していくわけです。

数式から関数を抽出するには正規表現（RegExp）を使うのが楽でしょう。
このとき数式内の A1、B1 などのセル参照式はマッチングに都合が悪い
ですから、予め

　　s = Application.ConvertFormula(数式, xlA1, xlR1C1, xlRelative)

と相対参照の R1C1 形式に変換しておきます。これでセル参照文字は RC[]
といった自明の文字に固定できますから、除外し易くなりますよね。
マッチングパターンは、

　　reg.Pattern = "([a-zA-Z]+)"　　　または
　　reg.Pattern = "([^!-@]+)"

とかでしょうか、、工夫してみて下さい。

重複なしのリストは、Scripting.Dictionary を使います。

mt2008 · Answer

何を関数とするかが難しそうですね。アドインで使用できる関数は増やせますし、ユーザ関数も作れます。こんなのはどうでしょう。予めSheet1を追加して、A列に検索したい関数一覧を入れておきます。 Excel標準で使用できる関数一覧ならネットでいくらでも見つかりますからそこからコピペで……。それ以外の関数は地道に入力。その上で、下記のマクロで検索。使用している関数の隣に１が入ります。但し、このマクロは不完全です。関数名+「(」の有無で検索していますが、例えばセル式が「TODAY()」の場合、TODAYでもDAYでもカウントしてしまいます。この辺りは正規表現をうまく使えば何とかなりそうな気がします。 Sub SampleMacro1() 　Dim countsheet As String 　Dim ws As Worksheet 　　countsheet = "Sheet1" '<-- 集計シート名　Application.ScreenUpdating = False 　For Each ws In Worksheets 　　If ws.Name <> countsheet Then 　　　Call SelectFormula(ws, countsheet) 　　End If 　Next 　Worksheets(countsheet).Select 　Application.ScreenUpdating = True 　MsgBox "終了" End Sub Sub SelectFormula(wws As Worksheet, wCountsheet As String) 　Dim rng As Range 　Dim targetRange As Range 　　On Error GoTo Errtrap 　'数式セルのみ選択、数式セルが無い場合はErrTrapへ　Set targetRange = wws.Cells.SpecialCells(xlCellTypeFormulas, 23) 　For Each rng In targetRange 　　'- Sheet1 A列の関数一覧との比較　　For i = 1 To Worksheets(wCountsheet).Range("A65536").End(xlUp).Row 　　　rtn = InStr(rng.Formula, Worksheets(wCountsheet).Cells(i, 1) & "(") 　　　'-- 使用されている関数があればB列に1をたてる　　　If rtn > 0 Then 　　　　Worksheets(wCountsheet).Cells(i, 2) = 1 　　　End If 　　Next i 　Next Errtrap: End Sub

【excel vba】エクセルファイル内にある数式の内「関数名(IF,SUM等)」のみを、同ファイル内の新しいシートに一覧化したいです。

こんにちは。

何を関数とするかが難しそうですね。

#1 です。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング