У Тима довольно большая рабочая книга, и он задается вопросом, есть ли способ провести «инвентаризацию» функций, используемых в различных формулах в книге, а также ячеек, в которых эти функции используются.

Эта задача немного сложнее, чем может показаться на первый взгляд. Вы можете установить некоторые правила относительно того, что составляет функцию 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

Тщательное изучение макроса показывает, что он читает текстовый файл (ExcelFunctions.txt), который включает в себя список всех функций рабочего листа Excel. Этот файл может быть link: /excelribbon-ExcelFunctions.txt [загружен по этой ссылке], и он должен храниться в той же папке, что и книга, которую вы анализируете. (Это означает, что книга, которую вы анализируете, уже должна быть сохранена в папке.)

Когда имена функций рабочего листа загружаются из текстового файла, макрос добавляет открывающую скобку к каждому имени. Эти имена хранятся в массиве, а затем этот массив сортируется в соответствии с длиной имени функции, с самыми длинными именами функций в начале массива. Это сделано из-за некоторой двусмысленности в названиях функций. Например, при чтении массива имен функций у вас будет одна функция с именем LEFT (и другая с именем T (. При просмотре формулы, если найдено имя LEFT (, то также будет найдено имя T (. Посмотрев сначала на самые длинные имена функций, а затем удалив эти имена из строки формулы, вы исключите возможность «ложных срабатываний».

Следует отметить, что эта функция будет работать только с те функции рабочего листа, которые перечислены в файле ExcelFunctions.txt. Вы можете изменить файл по своему усмотрению, чтобы адаптировать то, что находится в нем. В настоящее время он включает все стандартные функции рабочего листа для Excel 2016, но вы можете изменить его, чтобы включить функции, доступные благодаря ваши надстройки, или вы можете захотеть удалить функции, которые считаете слишком эзотерическими. (Подсказка:

Вы также можете просто добавить апостроф перед именем функции в текстовом файле, и тогда он никогда не будет включен в инвентаре.) В th e файл сейчас; если вы добавите слишком много других, вам нужно будет изменить числовые элементы, объявленные в макросе для массива EFunc.

Когда макрос будет завершен, вы найдете свой инвентарь в новой книге, которую создает макрос. Следует отметить, что время, необходимое для выполнения макроса, может варьироваться от довольно быстрого до очень медленного. Чем больше ваша книга — чем больше в ней листов и чем больше формул на этих листах — тем больше времени потребуется, чтобы завершить свою работу.

Помните, что он должен проверять каждую формулу в книге на соответствие почти 500 именам функций рабочего листа.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (5136) применим к Microsoft Excel 2007, 2010, 2013 и 2016.