Tim ha una cartella di lavoro piuttosto grande e si chiede se esiste un modo per fare un “inventario” di quali funzioni vengono utilizzate nelle varie formule della cartella di lavoro, insieme alle celle in cui vengono utilizzate tali funzioni.

Questo compito è un po ‘più difficile di quanto possa sembrare a prima vista. Puoi stabilire alcune regole su ciò che costituisce una funzione di Excel, ma queste regole possono diventare un po ‘complicate. Ad esempio, potresti pensare che una funzione sia definita da una parola maiuscola seguita da una parentesi aperta. Questo non è stato vero per un po ‘, tuttavia, poiché i nomi delle funzioni ora possono contenere punti.

Il modo più semplice che ho trovato per ottenere un inventario di funzioni è sviluppare una macro che passi attraverso ogni formula in ogni foglio di lavoro nella cartella di lavoro e controlli quella formula con ogni possibile funzione del foglio di lavoro. La seguente macro lo farà benissimo:

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

Un attento esame della macro indica che si legge in un file di testo (ExcelFunctions.txt) che include un elenco di tutte le funzioni del foglio di lavoro di Excel. Questo file può essere scaricato usando questo collegamento e dovrebbe essere archiviato nella stessa cartella della cartella di lavoro che stai analizzando. (Ciò significa che la cartella di lavoro che stai analizzando deve essere già stata salvata in una cartella.)

Quando i nomi delle funzioni del foglio di lavoro vengono caricati dal file di testo, la macro aggiunge una parentesi di apertura a ciascun nome. Questi nomi vengono memorizzati in una matrice, quindi questa matrice viene ordinata in base alla lunghezza del nome della funzione, con i nomi di funzione più lunghi all’inizio della matrice. Ciò viene fatto a causa di alcune ambiguità nei nomi delle funzioni. Ad esempio, quando viene letto l’array del nome della funzione avrai una funzione chiamata LEFT (e un’altra chiamata T (. Quando guardi una formula, se viene trovato il nome LEFT (allora verrà trovato anche il nome T (. Guardando prima i nomi di funzione più lunghi e poi rimuovendo quei nomi dalla stringa della formula, quindi elimini la possibilità di “falsi positivi”.

Va notato che questa funzione funzionerà solo con quelle funzioni del foglio di lavoro elencate nel file ExcelFunctions.txt. È possibile modificare il file, se lo si desidera, per adattare ciò che si trova. Attualmente include tutte le funzioni del foglio di lavoro standard per Excel 2016, ma è possibile modificarlo per includere funzioni rese disponibili da i tuoi componenti aggiuntivi, oppure potresti voler eliminare funzioni che ritieni troppo esoteriche. (Suggerimento:

Potresti anche semplicemente aggiungere un apostrofo davanti al nome di una funzione nel file di testo e non sarà mai incluso in un inventario.) Ci sono appena sotto i 500 nomi di funzioni in th e archivia ora; se ne aggiungi troppi, dovrai modificare gli elementi numerici dichiarati nella macro per l’array EFunc.

Quando la macro è completata, troverai il tuo inventario nella nuova cartella di lavoro creata dalla macro. Si noti che il tempo necessario per completare la macro può variare da abbastanza veloce a incredibilmente lento. Più grande è la tua cartella di lavoro, più fogli di lavoro contiene e più formule su quei fogli di lavoro, più tempo ci vorrà per completare il suo lavoro.

Ricorda che deve controllare ogni formula nella cartella di lavoro con quasi 500 nomi di funzioni del foglio di lavoro.

ExcelTips è la tua fonte di formazione economica su Microsoft Excel.

Questo suggerimento (5136) si applica a Microsoft Excel 2007, 2010, 2013 e 2016.