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

解決済みの質問

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

vba初心者です。(ネットからコードを拾ってきてちょっと改造できる程度)
excel 2003を使用しています。

【前提】
・「数式」「数値」「文字列」等がセルに入力されたエクセルファイルを使用する
 ⇒「関数名」のみを表示する。(文字列や数値が入力されたセルは無視)
・「数式」セルには「関数」が使われているものと、そうでないものがある
・1セル内に複数の関数が使用されている場合あり(新出の関数名であればすべて抽出したい)
・検索対象シート:ブック内のすべてのシート


【質問】
findメソッドで「IF」や「SUM」というように直接関数名を指定して検索するのではなく、「関数」というククリで検索はできるのでしょうか?

その検索結果を同ファイル内に新しいシート(Sheet1)を作成し、「関数名」を一覧表示するという流れ(以下にまとめました)にしたいです。

【手順】
(1)Book1内で「関数」検索をする
(2)「関数」が見つかった場合は「Sheet1」シートを作成(関数が見つからない場合は,msgbox "該当なし")
(3)検索した「関数名」をSheet1のA1セルに入力する
(4)Book1内すべて(複数シート有り)の関数名を抽出するまで連続検索をする
 ⇒A1→A2→A3→…の様に、A列の上から順に入力していく
※関数名の重複がないように一覧化できれば最高です。
※シート毎に、抽出した関数名を分けなくて大丈夫です。(あくまでファイル全体で使用されている関数名の一覧)
(5)msgbox "終了"


解決方法をご存知の方、ご教示願えませんでしょうか。

宜しくお願いいたします。

投稿日時 - 2009-04-10 00:17:41

QNo.4867078

すぐに回答ほしいです

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

#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

投稿日時 - 2009-04-10 18:16:08

お礼

KenKen_SP さん

ご回答ありがとうございます。
お礼が遅くなってごめんなさい。

作成までしていただいてありがとうございます。
実は#1のご回答を基に作成していたところ、自分の知識ではうまくいかず途方にくれていました…

#3でご回答いただいたコードを実行した結果、ほぼ私の意図通りの処理内容でした。
超感謝しています。ありがとうございました。

しかし、関数の中身で別ファイルのセルを参照していると"Result"シートの"function"列に意図しない文字列が抽出されるのです。

具体的には…
数式:=CONCATENATE([ジャンル.xlsx]Sheet1!$D$2,'[問題集.xlsx]Sheet2(2)'!$A$1)

"function"列
CONCATENATE
ジャンル
xlsx
Sheet
問題集

という感じです。
もれなくすべての関数名を抽出できていますし、「意図しない文字列」は目視でも確認できるのですが、ちょっと気になって。

正直、KenKen_SPさんのコードもまだ完全には解析できていないので、vbaの勉強も兼ねて上記問題を解決できるように考えてみます。

お手数お掛けしてすみませんでした。
本当に助かりました。ありがとうございました。

投稿日時 - 2009-04-11 10:54:25

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

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

回答(3)

ANo.2

何を関数とするかが難しそうですね。
アドインで使用できる関数は増やせますし、ユーザ関数も作れます。

こんなのはどうでしょう。
予めSheet1を追加して、A列に検索したい関数一覧を入れておきます。
Excel標準で使用できる関数一覧ならネットでいくらでも見つかりますからそこからコピペで……。それ以外の関数は地道に入力。

その上で、下記のマクロで検索。使用している関数の隣に1が入ります。
但し、このマクロは不完全です。関数名+「(」の有無で検索していますが、例えばセル式が「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

投稿日時 - 2009-04-10 15:22:33

お礼

mt2008 さん

ご回答ありがとうございます。
お礼が遅くなってしまってすみません。
コードを読み解くのに時間が掛ってしまいました…

> 何を関数とするかが難しそうですね。
そうなんです。散々考えてアイディアが浮かびませんでした…

> Excel標準で使用できる関数一覧ならネットでいくらでも見つかりますからそこからコピペで……。それ以外の関数は地道に入力。
私も最初は、すべての関数を検索すれば良いかなっと思っていたのですが、網羅するのが難しいなと。
今回検索対象となるファイルが約800あるのですが、全ファイルのユーザー関数を地道に抽出するのは大変そうです。(ユーザー関数が使用されているかも今確認できません)

でも全関数の一覧ができれば、'- Sheet1 A列の関数一覧との比較 でうまいことフラグが立ちますね。(自分で適当にファイルを作成して試してみました。)

自分なりに対応策を考えてみます。
マクロを組んで頂きありがとうございます。
勉強になりました。とても感謝しています。

投稿日時 - 2009-04-11 11:36:04

ANo.1

こんにちは。

数式をもったセルは 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 を使います。

投稿日時 - 2009-04-10 11:45:52

あなたにオススメの質問