• ベストアンサー

【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 "終了" 解決方法をご存知の方、ご教示願えませんでしょうか。 宜しくお願いいたします。

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

  • ベストアンサー
  • KenKen_SP
  • ベストアンサー率62% (785/1258)
回答No.3

#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

tomom1m1
質問者

お礼

KenKen_SP さん ご回答ありがとうございます。 お礼が遅くなってごめんなさい。 作成までしていただいてありがとうございます。 実は#1のご回答を基に作成していたところ、自分の知識ではうまくいかず途方にくれていました… #3でご回答いただいたコードを実行した結果、ほぼ私の意図通りの処理内容でした。 超感謝しています。ありがとうございました。 しかし、関数の中身で別ファイルのセルを参照していると"Result"シートの"function"列に意図しない文字列が抽出されるのです。 具体的には… 数式:=CONCATENATE([ジャンル.xlsx]Sheet1!$D$2,'[問題集.xlsx]Sheet2(2)'!$A$1) "function"列 CONCATENATE ジャンル xlsx Sheet 問題集 という感じです。 もれなくすべての関数名を抽出できていますし、「意図しない文字列」は目視でも確認できるのですが、ちょっと気になって。 正直、KenKen_SPさんのコードもまだ完全には解析できていないので、vbaの勉強も兼ねて上記問題を解決できるように考えてみます。 お手数お掛けしてすみませんでした。 本当に助かりました。ありがとうございました。

その他の回答 (2)

  • mt2008
  • ベストアンサー率52% (885/1701)
回答No.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

tomom1m1
質問者

お礼

mt2008 さん ご回答ありがとうございます。 お礼が遅くなってしまってすみません。 コードを読み解くのに時間が掛ってしまいました… > 何を関数とするかが難しそうですね。 そうなんです。散々考えてアイディアが浮かびませんでした… > Excel標準で使用できる関数一覧ならネットでいくらでも見つかりますからそこからコピペで……。それ以外の関数は地道に入力。 私も最初は、すべての関数を検索すれば良いかなっと思っていたのですが、網羅するのが難しいなと。 今回検索対象となるファイルが約800あるのですが、全ファイルのユーザー関数を地道に抽出するのは大変そうです。(ユーザー関数が使用されているかも今確認できません) でも全関数の一覧ができれば、'- Sheet1 A列の関数一覧との比較 でうまいことフラグが立ちますね。(自分で適当にファイルを作成して試してみました。) 自分なりに対応策を考えてみます。 マクロを組んで頂きありがとうございます。 勉強になりました。とても感謝しています。

  • KenKen_SP
  • ベストアンサー率62% (785/1258)
回答No.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 を使います。

関連するQ&A

  • Excel2003でif関数を使うとSUM関数で反映されません

    A1セルに金額を入力すると、B1に1と表示させる為に=IF(A1,"1","")といった数式を入れております。 さらにC1にBセルの合計値を表示させる為に=SUM(B1)C2には=SUM(B1:B2)と入力しているのですが、B列のセルにIF関数を使った数字『1』が表示されてもC列セルに反映されないので困っています。 ここで質問ですが、A列セルに金額が入力されるとB列セルに数字『1』が表示され、更にC列セルにB列セルの合計値が表示される様な関数等はありますでしょうか?解り難い説明ですいません。 因みにA、B、C列共に1~31までのセルがあります。

  • エクセル 別シートから一覧を抽出したい

    部品一覧表を作成しているのですが、2シートあり 1入力シート・2部品コードシートとあります。 部品コードシートにはB列に部品番号・C列部品名・D列部品番号・E列部品名と2行ずつ使い番号・品名がB~Wまで47行分入力されています。 B1:C47、D1:E47…と2列47行にはそれぞれ【A】、【B】、…とセルの名前の定義をつけました。現在【H】までありますが、今後増える可能性があります。 入力シートに、セルの名前を指定したときに部品コードのシートから 一覧を抽出したいのですが、どの関数を使えばよいのかわかりません。 入力シート                 |部品コードシート G   H                  | A Bコード C部品名  Dコード E部品名 4式入力用にあけています。     |1 1800  ユニットA   1501 電源A 5コード 部品名             |2 1801  ユニットB   1502 電源B 6                       |3 G4に関数を入れてG6~G52まで部品コードのシートA1~A47を一気に表示 させたいのです。部品コードシートの行数が変わることはありません。 マクロを使わないと、関数では難しいでしょうか?

  • エクセル関数でデーターを抽出する数式について

    検索値の名称と抽出表の一覧から名称が一部しか一致しないものを抽出したいが、セルの位置がわからない。 一応私が考えてた数式ではダメでしたが、参考まで VKOOLUP(B1.A10・・・.1.TRUE) 例 上記の数式を入力したセルを(A1)、入力するセルを(B1)、抽出表の一覧A10・・・以下に作成する。 A1 ○○   B1 ○○ 支店 A10 ○○ A11 △△ A12 ×× ・ ・ ・ ・

  • エクセルの数式から参照ブックとシートを抜き出したい

    あるセルが他のブックやシートを参照していたら、数式の中からブック名とシート名を抜き出したいです。 できれば、VBAを使う方法が知りたいです。(他の方法でも、あれば教えてください。) -- ・数式例1 ='[Book1]Sheet4 (2)'!R[-2]C ・欲しい結果例1 ブック名=Book1 シート名=Sheet4 (2) ・数式例2 =Sheet1!R[-1]C ・欲しい結果例2 ブック名="" シート名=Sheet1 ・数式例3 =A1 ・欲しい結果例3 ブック名="" シート名="" ・数式例4 =IF([Book1]Sheet2!R[-3]C="",Sheet1!R[-3]C,[Book1]Sheet2!R[-3]C) ・欲しい結果例4 ブック名=Array("Book1","","Book1") シート名=Array("Sheet2","sheet1","Sheet2") -- 例は単純にしましたが、実際には数式はもっと複雑で長いです。 列によって参照する場所が違い、どの列からどのブックやシートを参照しているのかを知りたくて、質問しました。 よろしくお願いいたします。

  • エクセル(Excel2003)のIF関数について

    Excelの数式初心者です。 IF関数でつまづいたので質問させていただきます。 SHEET2のセルA1に、 SHEET1のセルA1が○だったら1、それ以外だったら0を表示させたいです。 SHEET2のセルA1に、 =IF(SHEET1!A1=○,"1","0") と入れましたが、#NAME! とエラーが出てしまいました。 ○や×は認識してくれないということなのでしょうか? 初歩的な質問かもしれませんが、どなたかご教示いただけますと助かります。 Excel2003を使用しております。 よろしくお願い致します。

  • エクセル シート名を参照する関数は?

    エクセルで、売上を管理しているファイルがあります。 4~3月までの各月ごとのシートに、取引先ごとの売上額・仕入額・原価率・担当者名etcが1行ずつ入力されてあります。 そして、担当者別シートで担当者ごとの取引先データをLOOKUP関数やINDEX関数を使って抽出・合計し、グラフ化して管理しています。 ↓こんな状態です。(合計額の表)    A   B  C  D  E F G 4月 50000 40000 80% ・・・・・ 5月 6月 今の状態では、5月になればA~Gまでの4月の列をコピペして LOOOKUP関数等で参照しているシート名「4月」を「5月」にひとつずつ入力しなおしています。 これが結構面倒なので、 セルに「5月」と入力したら、5月のシートを参照しにいく、 セルに「6月」と入力したら、6月のシートを参照しにいく・・・ というように作り直したいのですが、 このような、シート名を参照する関数はあるのでしょうか?

  • Excel2003 IF関数

    シート1のA列に値を入力して行き、終了したら同じようにシート2のA列に値を入力して行きます。 この時、シート1に入力した値とシート2に入力した値は同じでなければなりません。 シート2は再チェック用なので、シート1と値が異なる入力をした場合、 値を入力するセル(A列)の隣のセル(B列)に、 IF関数、真の場合『OK』・偽の場合『入力エラー』と反映されるようにしております。 =IF(B1=seat!B1,"OK","入力エラー") 『OK』の場合は表示させる必要は無いので、条件書式を使用し、 フォント色を白くして、表示させないようにしております。 ただ、シート1を入力してからシート2に入力を始めるため、 シート2のB列には既にに『入力エラー』の表示がでてしまっています。 ※値を入力しないセルもあるのでその場合は『OK』が表示されますが、 条件書式によりフォント色を白くしておりますので、表示されません。 シート2には何も入力をしていないので、『入力エラー』と表示されるのは当然なのですが、 シート2に値を入力してから、シート1,2で異なる値の場合だけ『入力エラー』と表示されるのが理想です。 IF関数はこのような場合適していないのでしょうか? 何か良い方法がございましたらご教示頂けますと幸いです。 宜しくお願い致します。

  • エクセルの関数の数式がわかりません。

    A列 B列 1 10 2 20 3 30 a 4 40 エクセルの関数の数式がわかりません。 B1~B4のどこかにaの入力があった場合、aの入力がある行と同じ行のA列に入力されている数字を別のセルに表示させたいです。 例えば、上記のようにB3にaがあるので、A3の30を、別のセルに自動で30と出るようにしたいです。 B列には、ランダムにaを入力しますので、aが別の行にある場合もあります。 この場合の数式を教えてください。 よろしくお願い致します。

  • VBA マクロ セルと同じファイル名を選びたい

    Excelでマクロを組んでいます。 以下のような場合にどのように組めばいいのか、悩んでいます。 是非、ヒントを下さい。 Excelファイル「book1」でマクロを組んでいるとします。 1)book1のセルA1に入力されている文字列と同じファイル名のbookを選択できないか 2)セルA1から順にA2、A3、A4と文字列の入力がないセルまで自動的にセル番地を横へおくり、1)の操作を繰り返すことができないか ちなみに検索対象となるbookはすべて同じフォルダの中に格納されています。 よろしくお願いします。

  • エクセルVBAとワークシート関数が違う答え?

    エクセル2013です。 A1セルからA100セルにすべて1%と入力されています。 BIセルに =SUM(A:A) と数式が入っており、100%と表示されています。 A,B列のセルの書式はすべてパーセテージです。 101行以下には何も入っていません。 C1セルに =B1=1 と数式が入っており、TRUEが表示されています。 標準モジュールに Sub test01() MsgBox Range("B1").Value = 1 End Sub と書きました。 実行したところFalseが帰ってきました。 なぜ、TRUEにならないのでしょうか? なお、 Sub test02() MsgBox Val(Range("B1").Value) = 1 End Sub とすると、TRUEが帰ります。 でも、Val関数はStringを数値化するものですよね? B1セルはもともと数値なのでVal関数で答えが変わるのも不思議です。 どなたか、わかるように教えていただけないでしょうか?

専門家に質問してみよう