Rick a deux feuilles de travail qu’il doit comparer pour mettre en évidence les différences. La comparaison ne doit pas comparer ce qui est affiché, mais les formules dans chacune des cellules. De cette façon, Rick espère découvrir où les formules diffèrent sur chaque feuille de calcul.

Il existe plusieurs façons de comparer les formules. Dans certaines versions d’Excel, vous avez accès à un complément de comparaison qui peut gérer la tâche à votre place. Si vous utilisez Office Professionnel Plus 2013 ou Office 365 ProPlus, vous pouvez utiliser le complément de comparaison de feuilles de calcul.

Vous trouverez des informations sur ce complément ici:

https://support.office.com/en-us/article/Overview-of-Spreadsheet-Compare-13fafa61-62aa-451b-8674-242ce5f2c986

Si vous utilisez Office Professionnel Plus 2013 ou Office 365, vous pouvez également utiliser le complément Spreadsheet Inquire. Les informations sur ce complément se trouvent ici:

https://support.office.com/en-nz/article/What-you-can-do-with-Spreadsheet-Inquire-ebaf3d62-2af5-4cb1-af7d-e958cc5fad42

Reconnaissant que tout le monde n’utilise pas l’une de ces versions d’Excel ou ne souhaite pas utiliser de complément, vous pouvez faire d’autres choses. Dans Excel 2013 et les versions ultérieures, il existe une fonction de feuille de calcul pratique appelée FORMULATEXT. Vous pouvez utiliser cette fonction pour récupérer la formule stockée dans une cellule, de cette manière:

=FORMULATEXT(A7)

Cela renvoie la formule contenue dans la cellule A7 (dans ce cas). Si la cellule ne contient pas de formule, elle renvoie une erreur # N / A. Vous pouvez utiliser ce comportement pour créer dans une «feuille de calcul de comparaison» un indicateur indiquant si les formules sont égales ou non. Créez simplement la nouvelle feuille de calcul et placez-la dans la cellule A1:

=IF(FORMULATEXT(Sheet1!A1)=FORMULATEXT(Sheet2!A1),"","Different")

Copiez la formule aussi loin vers le bas et aussi loin à droite que vous le souhaitez. Il marque les différences entre les cellules correspondantes sur Sheet1 et Sheet2.

N’oubliez pas que FORMULATEXT a été introduit dans Excel 2013, cette approche ne fonctionnera donc pas dans les anciennes versions d’Excel. Si vous utilisez une autre version (ou, même, si vous utilisez Excel 2013), vous pouvez utiliser une macro pour marquer les différences entre les feuilles de calcul. Il existe de nombreuses approches macro que vous pouvez utiliser; ce qui suit est un court moyen de faire la comparaison.

Sub ComparaFormulas1()

Dim Check As Worksheet     Dim Master As Worksheet     Dim c As Range

Set Check = ActiveSheet     Set Master = Worksheets("Master")



For Each c In Check.UsedRange         If c.HasFormula Then             If c.Formula <> Master.Range(c.Address).Formula Then                 c.Interior.Color = RGB(255, 0, 0)

End If         End If     Next c End Sub

Pour utiliser cette macro, affichez le classeur que vous souhaitez comparer. Il suppose que vous souhaitez comparer aux mêmes cellules dans une feuille de calcul appelée «maître».

(Vous pouvez évidemment modifier cela dans la macro si votre feuille de calcul « standard » a un nom différent.) Chaque cellule de la feuille de calcul actuelle est comparée à la cellule correspondante de la feuille de calcul « principale ». Si les cellules contiennent des formules et que ces formules sont différentes, la couleur d’arrière-plan de la cellule devient rouge dans la feuille de calcul actuelle.

Une telle approche modifie évidemment le formatage de la feuille de calcul comparée. Si vous préférez ne pas modifier la mise en forme, mais souhaitez simplement une liste de cellules avec des différences, vous pouvez utiliser la variante suivante sur la macro:

Sub ComparaFormulas2()

Dim Check As Worksheet     Dim Master As Worksheet     Dim c As Range     Dim sTemp As String     Dim lDif As Long

Set Check = ActiveSheet     Set Master = Worksheets("Master")

sTemp = ""

lDif = 0

For Each c In Check.UsedRange         If c.HasFormula Then             If c.Formula <> Master.Range(c.Address).Formula Then                 lDif = lDif + 1                 sTemp = sTemp & vbCrLf & lDif & ": " & c.Address             End If         End If     Next c     If lDif > 0 Then         sTemp = "These were the differences" & vbCrLf & sTemp     Else         sTemp = "There were no differences"

End If     MsgBox sTemp End Sub

Vous pouvez également créer une fonction définie par l’utilisateur (UDF) qui accepte des plages pour les comparaisons. De cette façon, vous pouvez l’utiliser de différentes manières.

Function CompareFormulas3(rng1 As Range, rng2 As Range)

Dim x As Long

If rng1.Count <> rng2.Count Then         'Range sizes do not match         CompareFormulas = CVErr(xlValue)

Else         CompareFormulas = True    ' Assume all the same         For x = 1 To rng1.Count             If rng1(x).Formula <> rng2(x).Formula Then                 'Formulas do not match                 CompareFormulas = False                 x = rng1.Count    ' No need to keep comparing             End If         Next x     End If End Function

Si vous voulez simplement confirmer qu’une plage de cellules dans les deux feuilles de calcul a des formules identiques, vous pouvez simplement utiliser quelque chose comme:

=CompareFormulas3(Sheet1!A1:Z1000,Sheet2!A1:Z1000)

La fonction renvoie TRUE si toutes les cellules ont des formules identiques, FALSE si l’une des cellules a des formules différentes ou erreur #Value si les 2 plages ne sont pas de la même taille.

Si vous souhaitez mettre en évidence des différences, vous pouvez utiliser l’UDF dans une règle de mise en forme conditionnelle. En supposant que vous souhaitiez appliquer le format conditionnel aux cellules de Sheet1, spécifiez simplement que la règle doit utiliser une formule, puis utilisez-la comme formule:

=NOT(CompareFormulas3(Sheet2!A1,A1))

Si l’une des cellules de la feuille Sheet1 ne correspond pas aux cellules correspondantes de la feuille Sheet2, elles sont mises en forme en fonction de la mise en forme que vous avez définie dans la règle de mise en forme conditionnelle.

_Note: _

Si vous souhaitez savoir comment utiliser les macros décrites sur cette page (ou sur toute autre page des sites ExcelTips), j’ai préparé une page spéciale qui comprend des informations utiles.

lien: / excelribbon-ExcelTipsMacros [Cliquez ici pour ouvrir cette page spéciale dans un nouvel onglet de navigateur].

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (13400) s’applique à Microsoft Excel 2007, 2010, 2013, 2016, 2019 et Excel dans Office 365.