Compter les jours depuis le dernier paiement est sans aucun doute l’une des tâches les plus courantes à effectuer dans Google Sheets.
Nous pouvons simplifier cette tâche en utilisant une formule de tableau. Voici comment faire.
Comme la différence de dates est une tâche courante, il existe bien sûr une fonction dédiée dans les feuilles de calcul. Google Sheets ne fait pas exception.
Dans Google Sheets, il existe deux fonctions à cette fin, et les deux fonctionneront également avec la formule de tableau. Mais il y a une limitation. J’y viendrai plus tard.
J’ai un tableau comme celui-ci dans Sheets.
Première colonne (colonne A) – Colonne de date pour enregistrer (pour saisir) les dates des transactions.
Deuxième colonne (colonne B) – Montant dû.
Troisième colonne (colonne C) – Montant payé.
Quatrième colonne (colonne D) – Colonne du solde courant (pour cela, j’ai déjà une formule de tableau, je mettrai le lien après quelques paragraphes ci-dessous).
Colonne E – Nécessite une formule de tableau pour renvoyer le nombre de jours depuis le dernier paiement dans chaque ligne.
Pourquoi avons-nous besoin d’une formule personnalisée?
La colonne C est la colonne des paiements, et vous pouvez voir les dates de paiement correspondantes dans la colonne A.
Comme vous pouvez le voir, il y a des cellules vides (aucun paiement) entre les paiements.
S’il n’y a pas d’autres dates entre les dates de paiement (si tous les paiements sont dans des lignes adjacentes), nous pouvons utiliser les deux fonctions dédiées suivantes.
- DAYS.
- DATEDIF.
Note: Pour apprendre ces fonctions, veuillez consulter le Guide complet sur l’utilisation des fonctions de date dans Google Sheets.
Nous pouvons également utiliser l’opérateur de soustraction pour renvoyer les jours depuis la date du dernier paiement dans Google Sheets.
Une autre option est la fonction DATEDIFF de Query, mais cela ne conviendra pas à notre tableau.
Formule de tableau pour compter les jours depuis le dernier paiement dans Google Sheets
Passons à ma solution étape par étape. Avant cela, pour le solde courant, j’ai utilisé la formule suivante dans la cellule D1.
={"Solde en cours";ArrayFormula(if(len(A2:A),(SUMIF(ROW(A2:A),"<= "&ROW(A2:A),B2:B)-SUMIF(ROW(A2:A),"<= "&ROW(A2:A),C2:C)),))}Pour expliquer la formule, veuillez lire ce guide – Comment calculer le solde courant dans Google Sheets.
Ma formule de tableau qui compte les jours depuis le dernier paiement est relativement simple.
La formule est longue car j’ai remplacé certains paramètres par des expressions. Il est donc préférable d’apprendre la formule étape par étape.
Vous trouverez les étapes sous les titres ci-dessous.
Formule pour renvoyer les numéros de ligne des dates de paiement
Voici notre première étape.
Comme vous pouvez le voir à partir des lignes surlignées, les numéros de ligne à extraire sont 3, 4, 6, 7, 8, 9, 11 et 13.
Nous pouvons l’obtenir en filtrant les numéros de ligne A2:A si les valeurs de C2:C sont supérieures à 0.
Step_1_formula
=query(filter(row(A2:A),C2:C>0),"Select * offset 1",0)Nous ne voulons pas les numéros de ligne de la première date de paiement car il est inutile de trouver la différence de date avec une autre date car c’est le premier paiement.
C’est pourquoi j’ai utilisé Query avec Filter. Il décale d’une ligne.
J’ai inséré la formule ci-dessus dans la cellule G1. Veuillez consulter la capture d’écran à l’étape 2 ci-dessous.
Création de deux colonnes de dates à partir d’une seule colonne de paiement
Cette deuxième étape est la plus importante pour écrire la formule de tableau pour compter les jours depuis le dernier paiement dans Google Sheets.
Cette étape nous aidera à renvoyer la différence de date depuis le dernier paiement.
Le but de la première étape est de répartir les résultats de l’étape 2 sur les lignes correspondantes. Nous apprendrons cela plus tard.
Il y a deux formules.
Dans la cellule H1;
Step_2.1_formula
={FILTER(A2:A,C2:C>0);0}Dans la cellule I1;
Step_2.2_formula
={0;filter(A2:A,C2:C>0)}La première et la deuxième formules (formules H1 et I1) filtrent la plage de cellules A2:A si le montant payé (C2:C) est supérieur à 0.
La première formule insère une ligne contenant 0 en bas, et la deuxième formule insère 0 en haut de la ligne.
Vous pouvez vous reporter à la capture d’écran ci-dessus pour comprendre (voir les flèches).
Ici, la logique réside dans la formule pour compter les jours depuis le dernier paiement dans Google Sheets. Expliquons cette partie à l’étape suivante.
Formule de tableau pour compter les jours depuis les dates de paiement précédentes dans Google Sheets
C’est notre troisième étape, et il ne reste plus qu’une étape.
Soustrayez les dates de la colonne I des dates de la colonne H pour obtenir les jours depuis les derniers paiements.
Mais nous ne voulons pas la différence de date dans les cellules J1 et J9 (veuillez consulter l’image ci-dessous) car chaque ligne a une cellule vide (I1 et H9).
Nous pouvons donc utiliser cette formule générique.
if(step_2.1_formula*step_2.2_formula,step_2.1_formula-step_2.2_formula,)Corresponding Formula in Cell J1 (Step_3_draft_formula)
=ArrayFormula(if({filter(A2:A,C2:C>0);0}*{0;filter(A2:A,C2:C>0)},{filter(A2:A,C2:C>0);0}-{0;filter(A2:A,C2:C>0)},))Explication et résultat
Si la multiplication de step_2.1_formula par step_2.2_formula renvoie une valeur supérieure à 0, soustrayez le résultat de step_2.2_formula du résultat de step_2.1_formula.
Comme vous pouvez le voir, si vous considérez la colonne G, il y a deux cellules vides dans la colonne J. Les cellules en question sont J1 et J9.
Si je modifie directement la formule ci-dessus, certains d’entre vous pourraient être confus. Je vais donc la modifier en fonction de la formule générique ci-dessous.
=filter(step_3_draft_formula,step_3_draft_formula<>"")La formule à utiliser dans J1 est donc la suivante.
Step_3_formula:
=filter(if({filter(A2:A,C2:C>0);0}*{0;filter(A2:A,C2:C>0)},{filter(A2:A,C2:C>0);0}-{0;filter(A2:A,C2:C>0)},),if({filter(A2:A,C2:C>0);0}*{0;filter(A2:A,C2:C>0)},{filter(A2:A,C2:C>0);0}-{0;filter(A2:A,C2:C>0)},)<>"")RechercheV pour organiser les jours depuis le dernier paiement dans les lignes correspondantes
Dans cette dernière étape, combinons toutes les pièces.
Nous avons maintenant tous les détails nécessaires.
Cela signifie que nous avons le nombre de jours depuis le dernier paiement dans la colonne J et les numéros de ligne correspondants dans la colonne G.
Nous pouvons les combiner pour créer un tableau (plage) pour Vlookup. Ainsi, en utilisant les numéros de ligne A2:A comme clés de recherche, nous pouvons distribuer les jours.
Formule générique à utiliser dans la cellule E2:
=ArrayFormula(IFNA(vlookup(row(A2:A),{step_1_formula,step_3_formula},2,0)))Cette partie de la formule {step_1_formula,step_3_formula}, combine les colonnes G et J.
Vlookup essaie de faire correspondre tous les numéros de ligne dans A2:A dans ce tableau et renvoie le résultat de la deuxième colonne.
Voici la forme de la formule.
=ArrayFormula(IFNA(vlookup(row(A2:A),{query(filter(row(A2:A),C2:C>0),"Select * offset 1",0),filter(if({filter(A2:A,C2:C>0);0}*{0;filter(A2:A,C2:C>0)},{filter(A2:A,C2:C>0);0}-{0;filter(A2:A,C2:C>0)},),if({filter(A2:A,C2:C>0);0}*{0;filter(A2:A,C2:C>0)},{filter(A2:A,C2:C>0);0}-{0;filter(A2:A,C2:C>0)},)<>"")},2,0)))Formule finale:
Nous voulons la formule dans la cellule E1, pas dans la cellule E2. Insérons donc le titre « Jours depuis le dernier paiement » à l’intérieur.
={"Jours depuis le dernier paiement";ArrayFormula(IFNA(vlookup(row(A2:A),{query(filter(row(A2:A),C2:C>0),"Select * offset 1",0),filter(if({filter(A2:A,C2:C>0);0}*{0;filter(A2:A,C2:C>0)},{filter(A2:A,C2:C>0);0}-{0;filter(A2:A,C2:C>0)},),if({filter(A2:A,C2:C>0);0}*{0;filter(A2:A,C2:C>0)},{filter(A2:A,C2:C>0);0}-{0;filter(A2:A,C2:C>0)},)<>"")},2,0)))}La formule ci-dessus est la formule finale. Maintenant, vous pouvez supprimer toutes les formules de colonne auxiliaire dans la plage G1:J.
Sample_Sheet_13321
C’est tout. Merci de rester avec nous. Profitez-en!
Ressources:
- Google Sheets – Allouer les reçus de paiement aux factures
- Créer un rapport d’analyse d’âge à l’aide d’un tableau croisé dynamique dans Google Sheets
- Carte thermique personnalisée dans Google Sheets pour l’analyse d’âge
- Analyse d’âge à l’aide de la fonction QUERY dans Google Sheets [Guide étape par étape]
