Tim hat eine Arbeitsmappe, die ziemlich groß ist, und er fragt sich, ob es eine Möglichkeit gibt, ein „Inventar“ darüber zu erstellen, welche Funktionen in den verschiedenen Formeln in der Arbeitsmappe verwendet werden, zusammen mit den Zellen, in denen diese Funktionen verwendet werden.

Diese Aufgabe ist etwas schwieriger, als es zunächst scheinen mag. Sie können einige Regeln festlegen, was eine Excel-Funktion ausmacht, aber diese Regeln können etwas unübersichtlich werden. Sie könnten beispielsweise denken, dass eine Funktion durch ein Großbuchstaben gefolgt von einer öffnenden Klammer definiert wird. Dies ist jedoch seit einiger Zeit nicht mehr der Fall, da Funktionsnamen jetzt Punkte enthalten können.

Der einfachste Weg, ein Funktionsinventar zu erhalten, besteht darin, ein Makro zu entwickeln, das jede Formel in jedem Arbeitsblatt in der Arbeitsmappe durchläuft und diese Formel mit jeder möglichen Arbeitsblattfunktion vergleicht. Das folgende Makro macht das ganz gut:

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

Eine sorgfältige Prüfung des Makros zeigt, dass es eine Textdatei (ExcelFunctions.txt) einliest, die eine Auflistung aller Arbeitsblattfunktionen von Excel enthält. Diese Datei kann „über diesen Link heruntergeladen“ sein und sollte im selben Ordner wie die Arbeitsmappe gespeichert sein, die Sie analysieren. (Dies bedeutet, dass die Arbeitsmappe, die Sie analysieren, bereits in einem Ordner gespeichert sein muss.)

Wenn die Arbeitsblattfunktionsnamen aus der Textdatei geladen werden, fügt das Makro jedem Namen eine öffnende Klammer hinzu. Diese Namen werden in einem Array gespeichert, und dann wird dieses Array nach der Länge des Funktionsnamens sortiert, wobei die längsten Funktionsnamen am Anfang des Arrays stehen. Dies geschieht aufgrund einiger Unklarheiten in den Funktionsnamen. Wenn beispielsweise das Funktionsnamen-Array eingelesen wird, haben Sie eine Funktion mit dem Namen LEFT (und eine andere mit dem Namen T (). Wenn Sie sich eine Formel ansehen und der Name LEFT (gefunden wird, wird auch der Name T () gefunden. Wenn Sie zuerst die längsten Funktionsnamen betrachten und diese Namen dann aus der Formelzeichenfolge entfernen, wird die Möglichkeit von „False Positives“ ausgeschlossen.

Beachten Sie, dass diese Funktion nur mit funktioniert Diese Arbeitsblattfunktionen sind in der Datei ExcelFunctions.txt aufgeführt. Sie können die Datei nach Bedarf ändern, um den Speicherort anzupassen. Sie enthält derzeit alle Standardarbeitsblattfunktionen für Excel 2016, Sie können sie jedoch auch so ändern, dass sie Funktionen enthält, die von verfügbar gemacht werden Ihre Add-Ins, oder Sie möchten möglicherweise Funktionen löschen, die Sie zu esoterisch finden. (Hinweis:

Sie können auch einfach ein Apostroph vor einem Funktionsnamen in die Textdatei einfügen, das dann nie enthalten ist in einem Inventar.) Es gibt knapp 500 Funktionsnamen in th e Datei jetzt; Wenn Sie zu viele weitere hinzufügen, müssen Sie die im Makro für das EFunc-Array deklarierten Zahlenelemente ändern.

Wenn das Makro fertig ist, finden Sie Ihr Inventar in der neuen Arbeitsmappe, die das Makro erstellt. Sie sollten beachten, dass die Zeit, die das Makro für die Fertigstellung benötigt, von recht schnell bis erstaunlich langsam variieren kann. Je größer Ihre Arbeitsmappe ist – je mehr Arbeitsblätter sie enthält und je mehr Formeln in diesen Arbeitsblättern enthalten sind – desto länger dauert es, bis die Arbeit abgeschlossen ist.

Denken Sie daran, dass jede Formel in der Arbeitsmappe mit fast 500 Arbeitsblattfunktionsnamen verglichen werden muss.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (5136) gilt für Microsoft Excel 2007, 2010, 2013 und 2016.