Contact: 514-605-7112 / info@lecfomasque.com

Excel : Formule magique pour réconciliation bancaire

Publié le : 26 novembre 2018

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.

Réconciliation bancaire avec mise en for

 

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).

Apparition

 

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.

Code

 

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.

Fréquence

 

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).

MOD

 

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
Formule associiation

 

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.

Réconciliation bancaire

 

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


Microsoft Most Valuable Professional

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, Finance corporative et Compléments avancés. 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.

 

Découvrez quelles formations vous conviennent

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

 

Sophie Marchand

Sophie Marchand, M.Sc., CPA, CGA, MVP, est détentrice d’une M.Sc. en finance corporative et d’un titre comptable CPA, CGA. Elle est également MVP Office Apps and Services (Excel) et MVP Data Platform (Power BI). Le titre de MVP est un titre honorifique remis par Microsoft et signifie « Most Valuable Professional ». Il est attribué à des experts de solutions Microsoft qui se distinguent non seulement par leurs compétences mais également par leur implication dans la communauté. Seuls 4 professionnels possèdent cette double-qualification dans le monde. Mme Marchand cumule de nombreuses années d’expérience dans le milieu des affaires et se spécialise en modélisation financière et en intelligence d’affaires avec Excel et Power BI. Après avoir longtemps offert ses services comme consultante, elle se consacre désormais à la formation, sous toutes ses formes (formations en classe, formations en ligne, formations en entreprise, webinaires, animation de groupe d'usagers, rédaction d'articles de blogue, animation de forum en ligne et plus encore). Vous pouvez d'ailleurs la retrouver à la tête du groupe d'usagers Montreal Modern Excel and Power BI.

Laisser un commentaire