Chaque mois on reçoit des modèles Excel construits à base de RECHERCHEV et de SIERREUR empilés partout. Ça marche. Mais ça casse, ça induit en erreur et ça prend 3 fois plus de temps à maintenir. Voici les 5 formules qui changent vraiment la donne pour un DAF.
#1 - CNUM (Nettoyage)
Tout commence là. Avant de sommer, filtrer ou faire un TCD, vos données doivent être numériques. Le problème : quand vous exportez un grand livre depuis votre ERP ou votre outil comptable, les montants arrivent souvent en texte. La cellule affiche "1 234,56" mais Excel la traite comme du texte et votre SOMME renvoie 0 sans crier gare.
CNUM convertit du texte en nombre. Mais il a un talon d'Achille : il plante dès qu'il rencontre un espace insécable (CAR(160)), que la plupart des exports ajoutent automatiquement comme séparateur de milliers. La solution en deux temps :
=--SUBSTITUE(A2;CAR(160);"")
ou
=CNUM( SUBSTITUE(A2;CAR(160);"") )
Grand livre exporté : toute la colonne montants est en texte. Une simple SOMME renvoie 0. Appliquez cette formule sur la colonne entière avant tout traitement.
Le double « -- » (double moins) est un raccourci qui force la conversion en nombre en une seule opération. C'est équivalent à CNUM() mais plus compact. À utiliser en priorité quand vous traitez de gros volumes.
#2 - RECHERCHEX (RETROUVER)
Si vous utilisez encore RECHERCHEV, remplacez-le partout par RECHERCHEX. Sans exception. RECHERCHEV a trois défauts structurels : il ne peut chercher qu'à droite, son numéro de colonne codé en dur casse dès qu'on insère une colonne, et il n'a pas de gestion native de la valeur si introuvable.
RECHERCHEX résout tout ça. Il cherche dans n'importe quelle direction, référence des plages entières (donc robuste aux insertions de colonnes), et intègre une valeur par défaut en 4e argument. Et surtout, il s'imbrique naturellement pour calculer des écarts en une seule formule :
=RECHERCHEX(411;A2:A5;B2:B5) -RECHERCHEX(411;A2:A5;C2:C5)
Variation N vs N-1 du compte 411
Balance comptable avec soldes N et N-1 : récupérer la variation de chaque compte en une seule formule, sans colonne intermédiaire et sans risque si on ajoute une colonne dans le tableau.
Le 5e argument contrôle le mode de recherche : -1 pour une recherche approximative décroissante, parfait pour appliquer un barème IS ou des tranches de cotisations. Plus besoin de formule SI imbriquée pour chaque tranche.
#3 - INDEX + EQUIV (CROISER)
RECHERCHEX est excellent pour une recherche sur un seul axe. Mais quand vous avez besoin de croiser une ligne ET une colonne typiquement dans un tableau de paie ou un reporting multi-entités c'est INDEX + EQUIV qui prend la main.
Le principe : deux EQUIV localisent la position (un pour la ligne, un pour la colonne), et INDEX va chercher la valeur à l'intersection. L'avantage supplémentaire : EQUIV accepte les jokers, ce qui vous affranchit de devoir taper l'intitulé exact d'une rubrique.
=INDEX(B2:D4; EQUIV("Lefèvre";A2:A4;0); EQUIV("Mutuelle*";B1:D1;0))
Part patronale mutuelle de Lefèvre
Livre de paie avec salariés en lignes et rubriques en colonnes : extraire la participation employeur mutuelle d'un salarié précis, sans se soucier de l'intitulé exact de la colonne.
Le joker « * » dans EQUIV est sous-utilisé. "Mutuelle*" trouvera "Mutuelle (part patronale)", "Mutuelle employeur", etc. Indispensable quand les intitulés de colonnes varient selon les exports.
#4 - SOMME.SI.ENS (AGREGER)
La colonne vertébrale de tout reporting financier. SOMME.SI.ENS additionne une plage selon plusieurs critères simultanés fournisseur, période, entité, statut. Ce qui la rend particulièrement puissante pour les DAF : on peut borner une période avec deux critères sur la même colonne date, sans créer de colonne "mois" intermédiaire.
=SOMME.SI.ENS(C2:C5;plage à sommer
B2:B5;"DELL";critère fournisseur
A2:A5;">="&"01/03/2026"; borne début
A2:A5;"<="&"31/03/2026" borne fin )
Journal des achats : total facturé par un fournisseur sur mars uniquement les factures d'avril dans le même tableau doivent être exclues. Deux critères sur la colonne date, aucune colonne supplémentaire.
La syntaxe ">="&cellule_date permet de rendre la borne dynamique en pointant sur une cellule contenant la date. Votre tableau de reporting devient alors entièrement paramétrable : changez la période en une cellule, tout se recalcule.
#5 - SI.NON.DISP (FIABILISER)
La formule la plus négligée de cette liste et pourtant la plus importante pour la fiabilité de vos modèles. Quand une recherche ne trouve pas ce qu'elle cherche, elle renvoie #N/A. Réflexe classique : enrober tout ça dans SIERREUR pour faire propre.
Problème : SIERREUR masque toutes les erreurs y compris #DIV/0!, #REF! et toutes les vraies erreurs de logique que vous voulez voir. Vous pensez avoir un tableau propre, mais vous avez juste un tableau muet. SI.NON.DISP ne neutralise que le #N/A les vraies erreurs restent visibles.
=SI.NON.DISP(RECHERCHEX(607;A2:A3;B2:B3);"Hors référentiel"
affiché si #N/A uniquement
#DIV/0! et #REF! restent visibles
Rapprochement balance / référentiel de comptes : un compte absent du plan comptable renvoie un #N/A légitime à masquer sans pour autant cacher une éventuelle erreur de formule sur les comptes présents.
SI.NON.DISP pour les lookups (vous attendez un #N/A possible). SIERREUR uniquement quand vous acceptez vraiment d'absorber n'importe quelle erreur et jamais trop tôt dans la construction du modèle.
Voici donc un petit récap des formules Excel que j'utilise au quotidien en tant que DAF.
A toute !
Commentaire (0)