Google Sheets – Comment allouer les reçus de paiement aux factures

Vous voulez savoir comment allouer les reçus de paiement aux factures dans Google Sheets ? Pas de soucis, vous êtes au bon endroit !

Détails de la facture (Données d’exemple) pour l’allocation

Copiez-collez ce relevé de compte (état de compte) dans un nouveau fichier Google Sheets (plage : Feuille1!A1:F12).

Tableau n°1 :
Client | Date de la facture | Numéro de la facture | Débit | Crédit | Solde

Avant d’allouer les paiements dans la colonne « Crédit » et de trouver le solde impayé dans la colonne « Solde », essayons de faire correspondre les reçus.

Faire correspondre un seul reçu de paiement à l’aide de la fonction Vlookup dans Google Sheets

Supposons que vous avez reçu les paiements suivants.

(range : Feuille2!A1:B4)

Tableau n°2 :
Numéro de la facture | Montant du reçu

Essayez de compiler les deux rapports (Tableau n°1 et Tableau n°2).

Dans la cellule Feuille1!E2, c’est-à-dire dans la première ligne de la colonne « Crédit », insérez cette formule.

=ARRAYFORMULA(SI(ESTNA(VLOOKUP(C2:C,Feuille2!A2:B,2,0)), "", VLOOKUP(C2:C,Feuille2!A2:B,2,0)))

La formule Vlookup recherche dans le Tableau n°2 les clés de recherche (numéros de facture) du Tableau n°1 pour trouver une correspondance.

S’il trouve une correspondance, il renverra la valeur (montant du reçu) de la deuxième colonne du Tableau n°2.

Vous pouvez continuer à ajouter de nouveaux reçus dans le Tableau n°2. La fonction Vlookup les fera automatiquement correspondre et insérera le montant dans le Tableau n°1.

Avant de commencer à allouer les reçus de paiement aux factures, abordons un autre problème que cette correspondance peut poser.

Il s’agit des paiements partiels ou des paiements sur compte. Il peut y avoir plusieurs reçus de paiement pour la même facture, ainsi qu’un paiement unique.

Faire correspondre plusieurs reçus de paiement à l’aide d’une combinaison de Vlookup et de la fonction Query dans Google Sheets

À des fins d’explication, je modifie le Tableau n°2 précédent.

(range : Feuille2!A1:B5)

Tableau n°2 (Copie) :
Numéro de la facture | Montant du reçu

Dans ce cas, la formule Vlookup précédente n’allouera pas correctement (ou ne correspondra pas) les paiements reçus !

Vous devez d’abord récapituler les reçus, c’est-à-dire le Tableau n°2 (Copie) ci-dessus, et utiliser celui-ci comme « plage » (table de recherche) dans Vlookup.

La formule de requête suivante génère le résumé du « Tableau n°2 » (copie). La sortie sera similaire au « Tableau n°2 » sans répétition des numéros de facture.

=QUERY(Feuille2!A1:B,"Sélectionnez A, Somme(B) where A is not null group by A",1)

Remplacez simplement Feuille2!A2:B par cette requête dans Vlookup.

Formule à utiliser dans Feuille1!E2 :

=ARRAYFORMULA(SI(ESTNA(VLOOKUP(C2:C,QUERY(Feuille2!A1:B,"Sélectionnez A, Somme(B) where A is not null group by A",1),2,0)), "", VLOOKUP(C2:C,QUERY(Feuille2!A1:B,"Sélectionnez A, Somme(B) where A is not null group by A",1),2,0)))

Allocation des reçus de paiement aux factures dans Google Sheets

Supposons que vous ayez un mélange des deux exemples précédents (reçus) ainsi que des reçus forfaitaires. Comment obtenir le solde du compte client correctement en allouant de tels reçus de paiement ?

Examinons d’abord en détail le problème.

Nous avons reçu des paiements de différents clients comme suit.

Tableau n°3 :
(range : Feuille2!A1:B6)

Client | Reçu

Nous n’avons aucun numéro de facture pour allouer ces montants de reçus. De plus, le montant peut correspondre ou non, car certains montants de reçus sont forfaitaires et d’autres sont des paiements partiels.

Comme nous avons les noms des clients en commun dans le Tableau n°1 et le Tableau n°3, nous pouvons utiliser ces informations pour allouer les montants des reçus de paiement complets/partiels/sur compte aux factures.

La formule d’allocation de paiement pour Feuille1!E2 doit être copiée-collée vers le bas (ce n’est pas une formule de tableau par rapport aux deux exemples précédents).

=MIN(D2, SOMME.SI(Feuille2!$A$2:$A,$A$2:$A,Feuille2!$B$2:$B)-SOMME.SI($A$1:A1,A2,$E$1:E1))

Comment cette formule Google Sheets alloue-t-elle correctement les paiements reçus ?

Explication de la formule :

Il y a deux formules Sumif en cours d’utilisation et une formule Min.

Formule Sumif n°1 :

=SOMME.SI(Feuille2!$A$2:$A,$A$2:$A,Feuille2!$B$2:$B)

Essayez cette première formule Sumif dans la cellule G2 et copiez-collez vers le bas.

Elle additionne les reçus de paiement par client, puis les renvoie pour chaque client dans la colonne G.

Vous devez essayer la formule pour la comprendre.

Remarque : En apportant de légères modifications, la combinaison précédente de Vlookup et de Query peut faire la même chose (peut remplacer la formule Sumif n°1) et fonctionner encore mieux en tant que formule de tableau. Mais nous avons besoin de la formule Sumif ci-dessus.

Pour votre référence, voici cette combinaison. Vous pouvez l’ignorer.

(dans la cellule J2 pour auto-populer)
=ARRAYFORMULA(SI(ESTNA(VLOOKUP(A2:A,QUERY(Feuille2!A1:B,"Sélectionnez A,Sum(B) where A is not null group by A",1),2,0)), "", VLOOKUP(A2:A,QUERY(Feuille2!A1:B,"Sélectionnez A,Sum(B) where A is not null group by A",1),2,0)))

Le just above Sumif en tant que tableau peut faire la même chose. Encore une fois, ignorez-le.

(dans la cellule J2 pour auto-populer)
=ARRAYFORMULA(SOMME.SI(Feuille2!$A$2:$A,$A$2:$A,Feuille2!$B$2:$B))

Ignorez simplement le Sumif Array et les formules de combinaison Query appliquées en J2. Nous ne voulons pas les deux pour allouer les paiements.

Formule Sumif n°2 :

=SOMME.SI($A$1:A1,A2,$E$1:E1)

Ce Sumif est la clé pour allouer les montants des reçus de paiement aux factures. Nous ne pouvons pas le tester comme précédemment, mais je peux expliquer ce qu’il fait.

Il renvoie la somme cumulative (total partiel) des valeurs dans la même colonne (colonne E) jusqu’à la ligne actuelle, comme le total de $E$1:E1 dans E2, le total de $E$1:E2 dans E3, etc.

Le total partiel renvoyé sera par client car nous avons utilisé Sumif pour cela.

Nous pouvons combiner les deux formules Sumif ci-dessus avec Min. Voyez ci-dessous.

Formule générique pour allouer les reçus de paiement dans Google Sheets :

La formule finale (version générique) dans la cellule E2 est comme suit :

=MIN(D2, Formule Sumif n°1 - Formule Sumif n°2)

Qu’est-ce que cela fait ?

Cela expliquera :

Dans E2 : min(3578.80,11500-0)
Dans E3 : min(2967.92,11500-3578)
Dans E4 : min(12742.84,11500-6546.72)

Le Min s’assure que vous n’allouez pas un montant supérieur au montant de la facture en cas de reçus sur compte.

De plus, la valeur du reçu (reçu forfaitaire) est ajustée à chaque ligne. Si le montant alloué est inférieur au montant de la facture, Min renverra le montant alloué.

Qu’en est-il de l’allocation des nouveaux paiements ?

Supposons, en utilisant ma formule, vous avez déjà alloué les paiements reçus. La prochaine fois que vous recevez des paiements, insérez-les juste en dessous de la dernière ligne non vide dans le Tableau n°3.

Par exemple, j’ai reçu un montant de 10 000,00 € du client A. Je peux les mettre dans la 7e ligne (insérer le nom du client « A » en A7 et le montant de 10 000,00 € en B7).

Le paiement sera automatiquement alloué (ajusté) dans le relevé de compte dans Feuille1.

Formule Google Sheets pour le solde impayé

Saisissez cette formule dans la cellule F2 et oubliez-la !

=ARRAYFORMULA(SI(ESTVIDE(A2:A),D2:D-E2:E,))

C’est tout pour l’allocation des reçus de paiement dans Google Sheets.