Lors d’une formation que j’ai offerte récemment, une apprenante m’a demandé si j’avais une formule magique pour effectuer des réconciliations bancaires dans Excel. Ma réponse semble l’avoir plus ou moins satisfaite puisque je lui ai répondu, oui, sûrement, mais ça dépend… Ça dépend en effet des informations dont vous disposez et des éléments que vous cherchez à réconcilier. Ce que je souhaitais plutôt dire c’est qu’il n’y a pas une formule unique qui convient à toutes les réconciliations bancaires. Dans cet article, je propose une approche pour un cas précis, soit une liste de montants sans aucune autre information à réconcilier. J’espère qu’il saura vous inspirer ! Si vous avez des cas différents à me soumettre, allez-y ! Mettez-moi au défi 😉.
Réconciliation bancaire dans Excel
Ci-dessous, vous retrouverez les données de départ dans la colonne Montant et les réconciliations dans la colonne Asso (pour Associations). Les autres colonnes comprennent des calculs intermédiaires. Je les ai insérées pour vous expliquer ma procédure mais bien évidemment, vous pourriez également les imbriquer dans une seule et même fonction.
Examinons donc de plus près ces colonnes de calculs intermédiaires.
Apparition
J’utilise d’abord une fonction NB.SI afin de déterminer l’ordre d’apparition de chaque montant. Par exemple, on constate que le montant 458 apparaît pour la première fois sur la ligne 7 (apparition =1) et apparaît pour une deuxième fois sur la ligne 11 (apparition = 2).
Code
J’utilise ensuite la fonction SI pour afficher la fusion du montant et de son ordre d’apparition. Ainsi, à la ligne 7, on voit 458-1 et à la ligne 11, on voit 458-2.
Fréquence
Je calcule aussi la fréquence de chaque montant dans la liste entière. Par exemple, on peut voir que le montant -716 apparaît 3 fois au total dans la liste.
MOD
J’utilise ensuite la fonction MOD pour savoir si l’ordre d’apparition peut être divisé entièrement par 2 (retourne 0) ou non (retourne 1).
Asso
Au final, j’utilise une fonction SI imbriquée dans la colonne Asso pour effectuer mes associations (match).
Je vérifie d’abord si, à la fois:
- l’ordre d’apparition est égal à la fréquence
- et si la fonction MOD retourne 1
- dans lequel cas je n’affiche rien
- en effet, dans ce cas, on se retrouve en présence de la dernière apparition du montant dans la liste et si ce montant est impair (MOD retourne 1), cela signifie que ce montant n’a pas d’équivalent dans la liste
Sinon, je vérifie si:
- la fréquence est plus grande ou égale à 2
- dans lequel cas, j’affiche x.
- en effet, dans les autres cas, on va conclure à une association, en autant que la fréquence ne soit pas égale à 1
Mise en forme conditionnelle
J’ai aussi ajouté une mise en forme conditionnelle pour faire ressortir en gris pâle toutes les cellules des lignes où la colonne G est égale à x.
Résultat final de ma réconciliation bancaire dans Excel
Ci-dessous, j’ai remplacé les 2 derniers montants par la valeur 267. On voit que ceci a permis d’associer le 4ième 267 avec le 3ième et de laisser le 5ième sans association.
Vous aimeriez être un Ninja Excel et devenir la référence ultime de votre bureau, ou tout le moins ne plus avoir l’impression de stagner ? Développez des solutions innovatrices en explorant des fonctions et fonctionnalités souvent méconnues mais fort utiles avec nos formations pour devenir Ninja d’Excel. |
|---|
Formule magique pour réconciliation bancaire dans Excel
En partant de l’exemple précédent, vous pouvez adapter les formules à votre situation, pour faire d’autres types de réconciliations. Par exemple, peut-être qu’au-delà du montant, vous devez également réconcilier un numéro de chèque ou un nom de client. Il vous faudra alors bonifier la procédure ci-dessus.
Fichier d’accompagnement VIP à télécharger
Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.
Formation complémentaire
Pour apprendre des façons créatives d’utiliser Excel pour créer des solutions surprenantes et régler des problématiques qui semblent parfois sans solution, suivez la formation Excel – Trucs de pro.
Voici quelques commentaires d’apprenants ayant suivi cette formation :
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
