ティムはかなり大きなワークブックを持っており、ワークブックのさまざまな数式で関数が使用されている「インベントリ」と、それらの関数が使用されているセルを作成する方法があるかどうか疑問に思っています。

このタスクは、最初は少し難しいように思われるかもしれません。何がExcel関数を構成するかについていくつかのルールを作成できますが、それらのルールは少し厄介になる可能性があります。たとえば、関数は大文字の単語とそれに続く開き括弧で定義されていると考えるかもしれません。ただし、関数名にピリオドを含めることができるようになったため、これはしばらくの間当てはまりませんでした。

関数インベントリを取得するために私が見つけた最も簡単な方法は、ワークブックの各ワークシートの各数式をステップスルーし、その数式をすべての可能なワークシート関数と照合するマクロを開発することです。次のマクロはそれをうまく行います：

Sub FormulaInventory()

Dim EFunc(500) As String     Dim iEFCnt As Integer     Dim sFile As String     Dim sTemp As String     Dim SourceBook As Workbook     Dim TargetBook As Workbook     Dim TargetSheet As Worksheet     Dim w As Worksheet     Dim c As Range     Dim iRow As Integer     Dim J As Integer     Dim K As Integer     Dim L As Integer

' Read functions from text file     sFile = ActiveWorkbook.Path & "\ExcelFunctions.txt"

iEFCnt = 0     Open sFile For Input As #1     While Not EOF(1)

Line Input #1, sTemp         sTemp = Trim(sTemp)

If sTemp > "" Then             iEFCnt = iEFCnt + 1             EFunc(iEFCnt) = sTemp & "("

End If     Wend     Close #1

' Sort functions; longest to shortest     For J = 1 To iEFCnt - 1         L = J         For K = J + 1 To iEFCnt             If Len(EFunc(L)) < Len(EFunc(K)) Then L = K         Next K         If L <> J Then             sTemp = EFunc(J)

EFunc(J) = EFunc(L)

EFunc(L) = sTemp         End If     Next J

' Create and setup new workbook     Set SourceBook = ActiveWorkbook     Set TargetBook = Workbooks.Add     Set TargetSheet = TargetBook.Worksheets.Add     TargetSheet.Name = "Inventory"

TargetSheet.Cells(1, 1) = "Function Inventory for " & SourceBook.Name     TargetSheet.Cells(3, 1) = "Function"

TargetSheet.Cells(3, 2) = "Worksheet"

TargetSheet.Cells(3, 3) = "Cell"

TargetSheet.Range("A1").Font.Bold = True     TargetSheet.Range("A3:C3").Font.Bold = True     With TargetSheet.Range("A3:C3").Cells.Borders(xlEdgeBottom)

.LineStyle = xlContinuous         .Weight = xlThin     End With

' Perform actual inventory     iRow = 4     For Each w In SourceBook.Worksheets         For Each c In w.Cells.SpecialCells(xlCellTypeFormulas)

sTemp = c.Formula             For J = 1 To iEFCnt                 If InStr(sTemp, EFunc(J)) Then                     TargetSheet.Cells(iRow, 1) = Left(EFunc(J), Len(EFunc(J)) - 1)

TargetSheet.Cells(iRow, 2) = w.Name                     TargetSheet.Cells(iRow, 3) = Replace(c.Address, "$", "")

iRow = iRow + 1                     sTemp = Replace(sTemp, EFunc(J), "")

End If             Next J         Next c     Next w End Sub

マクロを注意深く調べると、Excelのすべてのワークシート関数のリストを含むテキストファイル（ExcelFunctions.txt）が読み込まれていることがわかります。このファイルは linkこのリンクを使用してダウンロードにすることができ、分析しているブックと同じフォルダーに保存する必要があります。 （これは、分析しているブックが既にフォルダーに保存されている必要があることを意味します。）

ワークシートの関数名がテキストファイルから読み込まれると、マクロは各名前に開き括弧を追加します。これらの名前は配列に格納され、この配列は関数名の長さに従ってソートされ、最も長い関数名が配列の先頭に配置されます。これは、関数名があいまいなために行われます。たとえば、function-name配列が読み込まれると、LEFT（という名前の関数とT（という名前の関数があります。数式を見ると、LEFT（nameが見つかると、T（nameも見つかります。最初に最長の関数名を確認し、次にそれらの名前を数式文字列から削除することで、「誤検知」の可能性を排除します。

この関数はでのみ機能することに注意してください。 ExcelFunctions.txtファイルにリストされているワークシート関数。必要に応じてファイルを変更して、場所を調整できます。現在、Excel 2016のすべての標準ワークシート関数が含まれていますが、によって利用可能になった関数を含めるように変更することもできます。アドイン、または難解すぎる関数を削除することもできます（ヒント：

テキストファイルの関数名の前にアポストロフィを追加するだけでも、含まれることはありません。インベントリ内にあります。）thには500未満の関数名があります。今すぐeファイル;追加する数が多すぎる場合は、EFunc配列のマクロで宣言されている要素の数を変更する必要があります。

マクロが完了すると、マクロが作成する新しいワークブックにインベントリが表示されます。マクロが完了するまでにかかる時間は、非常に速いものから驚くほど遅いものまでさまざまであることに注意してください。ワークブックが大きいほど（含まれるワークシートが多く、それらのワークシートの数式が多いほど）、作業を完了するのに時間がかかります。

ワークブック内のすべての数式を約500のワークシート関数名と照合する必要があることを忘れないでください。

_ExcelTips_は、費用効果の高いMicrosoftExcelトレーニングのソースです。

このヒント（5136）は、Microsoft Excel 2007、2010、2013、および2016に適用されます。