Excel : Formule magique pour réconciliation bancaire

Publié le 26 novembre 2018
par Sophie Marchand M.Sc., CPA, CGA, MVP
Réconciliation bancaire avec mise en for

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

 

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
 
 

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

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 :

 

Le CFO masqué - Commentaires formation - Excel – Trucs de pro

CFO-Masque_Formations-en-ligne_FBLa 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é.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top