Vous voulez apprendre à allouer des reçus de paiement aux factures dans Google Sheets ? Pas de soucis ! Grâce à la fonction Vlookup dans Google Sheets, vous pouvez facilement faire correspondre les paiements reçus avec les factures correspondantes.
Allouer des paiements uniques en utilisant Vlookup dans Google Sheets
Imaginons que vous avez reçu les paiements suivants :
(Tableau n°2)
Invoice # | Montant du recu
--------------------------
RA-1564 | $2,500.00
RA-1565 | $5,000.00Pour compiler les rapports des paiements et des factures, insérez cette formule dans la cellule E2 de la feuille Sheet1:
=RECHERCHEV(C2:C,Sheet2!A2:B,2,FALSE)
La formule Vlookup recherche les numéros de facture (clés de recherche) dans le tableau n°2 et, s’ils correspondent, elle renvoie le montant du reçu depuis la deuxième colonne du tableau n°2.
Allouer des paiements multiples en utilisant une combinaison de Vlookup et de Query dans Google Sheets
Pour expliquer cette méthode, modifions le tableau n°2 comme suit :
(Tableau n°2 – Copie)
Invoice # | Montant du recu
--------------------------
RA-1564 | $2,500.00
RA-1565 | $5,000.00
RA-1564 | $2,000.00Dans ce cas, la formule Vlookup précédente ne fonctionnera pas correctement pour allouer les paiements reçus !
Vous devez d’abord résumer les montants des reçus, c’est-à-dire le tableau n°2 – Copie ci-dessus, et utiliser ce résumé comme « plage » (tableau de recherche) dans Vlookup.
La formule Query suivante génère le résumé du tableau n°2 – Copie. La sortie sera similaire au tableau n°2, sans factures répétées :
=QUERY(Sheet2!A1:B,"SELECT A, SUM(B) WHERE A IS NOT NULL GROUP BY A",1)
Remplacez simplement Sheet2!A2:B par cette Query dans la formule Vlookup de la cellule E2 de la feuille Sheet1.
La formule à utiliser dans la cellule E2 de Sheet1 est la suivante :
=RECHERCHEV(C2:C,QUERY(Sheet2!A1:B,"SELECT A, SUM(B) WHERE A IS NOT NULL GROUP BY A",1),2,FALSE)
Allouer des reçus de paiement aux factures dans Google Sheets
Imaginons que vous ayez un mélange des deux exemples précédents ainsi que des montants de paiement en une seule fois.
Voici le tableau n°3 :
(Tableau n°3)
Customer A | Paiement
---------------------
Customer A | $8,000.00
Customer B | $5,000.00
Customer B | $3,000.00Comme il n’y a pas de numéro de facture pour allouer ces montants de reçus, vous pouvez utiliser les noms des clients comme référence pour allouer les montants de paiement complets, partiels ou sur compte aux factures.
La formule d’allocation des paiements pour la cellule E2 de la feuille Sheet1, qui doit être copiée-collée vers le bas (ce n’est pas une formule de tableau comme dans les deux exemples précédents), est la suivante :
=MIN(D2,SUMIF(Sheet2!$A$2:$A,$A$2:$A,Sheet2!$B$2:$B)-SUMIF($A$1:A1,A2,$E$1:E1))
Cette formule garantit que vous n’allouez pas une somme supérieure au montant de la facture en cas de paiements partiels.
De plus, la valeur du reçu (paiement global) s’ajuste dans chaque ligne. Si le montant alloué (crédit) est inférieur au montant de la facture (débit), la formule MIN renvoie le montant alloué.
Qu’en est-il de l’allocation des nouveaux paiements ?
Supposons que vous ayez déjà alloué les paiements reçus en utilisant cette formule. La prochaine fois que vous recevez des paiements, insérez-les juste en dessous de la dernière ligne non vide du tableau n°3.
Par exemple, si vous recevez un montant de 10 000,00 $ de la part du client A, vous pouvez l’insérer dans la 7e ligne (insérez 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 de la feuille Sheet1.
Formule Google Sheets pour le solde impayé
Insérez cette formule dans la cellule F2 et oubliez-la !
=SI(LONGUEUR(A2:A),D2:D-E2:E,)
Et voilà ! C’est tout ce qu’il y a à savoir sur l’allocation des reçus de paiement dans Google Sheets. Pour plus de conseils et de tutoriels, assurez-vous de visiter Crawlan.com.
