Tim a un classeur assez volumineux et il se demande s’il existe un moyen de faire un « inventaire » des fonctions utilisées dans les différentes formules du classeur, ainsi que des cellules dans lesquelles ces fonctions sont utilisées.

Cette tâche est un peu plus difficile qu’il n’y paraît à première vue. Vous pouvez définir des règles sur ce qui constitue une fonction Excel, mais ces règles peuvent devenir un peu hinky. Par exemple, vous pourriez penser qu’une fonction est définie par un mot majuscule suivi d’une parenthèse ouvrante. Cela n’a pas été vrai depuis un certain temps, cependant, car les noms de fonction peuvent désormais contenir des points.

Le moyen le plus simple que j’ai trouvé pour obtenir un inventaire des fonctions est de développer une macro qui parcourra chaque formule de chaque feuille de calcul du classeur et de vérifier cette formule par rapport à chaque fonction de feuille de calcul possible. La macro suivante le fera très bien:

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 examen attentif de la macro indique qu’elle lit dans un fichier texte (ExcelFunctions.txt) qui comprend une liste de toutes les fonctions de feuille de calcul d’Excel. Ce fichier peut être link: /excelribbon-ExcelFunctions.txt [téléchargé en utilisant ce lien], et il doit être stocké dans le même dossier que le classeur que vous analysez. (Cela signifie que le classeur que vous analysez doit déjà avoir été enregistré dans un dossier.)

Lorsque les noms de fonction de feuille de calcul sont chargés à partir du fichier texte, la macro ajoute une parenthèse ouvrante à chaque nom. Ces noms sont stockés dans un tableau, puis ce tableau est trié en fonction de la longueur du nom de la fonction, avec les noms de fonction les plus longs au début du tableau. Cela est dû à une certaine ambiguïté dans les noms de fonction. Par exemple, lorsque le tableau de nom de fonction est lu, vous aurez une fonction nommée LEFT (et une autre nommée T (. Lorsque vous regardez une formule, si le nom LEFT (est trouvé, alors le T (nom sera également trouvé. En regardant d’abord les noms de fonctions les plus longs, puis en supprimant ces noms de la chaîne de formule, vous éliminez la possibilité de « faux positifs ».

Il convient de noter que cette fonction ne fonctionnera qu’avec les fonctions de feuille de calcul répertoriées dans le fichier ExcelFunctions.txt. Vous pouvez modifier le fichier, comme vous le souhaitez, pour adapter ce qui se trouve. Il comprend actuellement toutes les fonctions de feuille de calcul standard pour Excel 2016, mais vous souhaiterez peut-être le modifier pour inclure les fonctions rendues disponibles par vos compléments, ou vous voudrez peut-être supprimer des fonctions que vous trouvez trop ésotériques. (Astuce:

Vous pouvez aussi simplement ajouter une apostrophe devant un nom de fonction dans le fichier texte et elle ne sera alors jamais incluse dans un inventaire.) Il y a un peu moins de 500 noms de fonction dans e e fichier maintenant; si vous en ajoutez trop, vous devrez modifier les éléments numériques déclarés dans la macro du tableau EFunc.

Lorsque la macro est terminée, vous trouverez votre inventaire dans le nouveau classeur créé par la macro. Vous devez noter que le temps nécessaire à la macro pour se terminer peut varier d’assez rapide à incroyablement lent. Plus votre classeur est volumineux (plus il contient de feuilles de calcul et plus il y a de formules sur ces feuilles de calcul), plus il faudra de temps pour terminer son travail.

N’oubliez pas qu’il doit vérifier chaque formule du classeur par rapport à près de 500 noms de fonction de feuille de calcul.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (5136) s’applique à Microsoft Excel 2007, 2010, 2013 et 2016.