Excel : Formule magique pour réconciliation bancaire

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 :p.

Données bancaires à réconcilier

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 conslure à 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

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.

Formule magique

En partant de l’exemple précédent, vous pouvez adopter 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.

---

NOTRE OFFRE DE FORMATIONS

---

Le CFO masqué vous offre un vaste choix de formations Excel et Power BI, réparties dans 8 catégories: Excel – Tableaux de bord, Excel – Modélisation financière, Excel – VBA, Excel – Power Tools, Excel – Ninja, Power BI, Analytique d’affaires et Finance corporative. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et ils sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel. De plus, nos formations peuvent mener à l’obtention d’une accréditation.

Découvrez quelles formations vous conviennent

Pour info: 514-605-7112 ou info@lecfomasque.com