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 :

 

Mélissa Paquin
Écrit il y a 1 mois
Certains sujets aideront à améliorer les rapports actuels

Formation intéressante. Certains sujets aideront à améliorer les rapports actuels que j'utilise en tant qu'analyste financier.

Roger Martin Nguyen
Écrit il y a 5 mois
Exemples bien expliquées

Exemples bien expliquées et surtout, les exercices sont détaillées avec la démarche à suivre pour arriver au résultat.

Nathalie Tardif
Écrit il y a 5 mois
J'ai été cherché de nouvelles façons de faire qui vont optimiser mon travail.

Excellent! J'ai été cherché de nouvelles façons de faire qui vont optimiser mon travail. Merci beaucoup!

Lucie Dupuis
Écrit il y a 5 mois
La formation en ligne est idéale

Excellente formation qui donne des trucs pour des situations que nous pouvons et/ou avons rencontré dans notre travail. La formation en ligne est idéale car ça nous permet de réécouter/revoir les explications que nous n'avons pas bien saisi. La durée des modules n'est jamais trop longue.


CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

Laisser un commentaire

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

Scroll to Top