Vous avez une table de données quotidiennes dans Google Sheets et vous souhaitez extraire uniquement les lignes correspondant au dernier jour de chaque mois ? Pas de souci, nous avons une formule pour cela !
Utilisez SORTN ou VLOOKUP pour extraire les lignes du dernier jour de chaque mois
Avec l’utilisation des fonctions SORTN ou VLOOKUP, vous pouvez écrire une formule qui permettra de retourner les lignes correspondant au dernier jour de chaque mois à partir de votre table de données quotidienne dans Google Sheets. Vous n’avez pas besoin d’utiliser cette formule pour chaque ligne ou d’ajouter une colonne auxiliaire.
Dans l’exemple ci-dessous, les données d’échantillon dans la plage A1:B contiennent des entrées quotidiennes. Le résultat attendu dans la plage D2:F ne contient que les lignes du dernier jour de chaque mois à partir des données quotidiennes dans A1:B.
Si plusieurs entrées tombent sur la date de fin de mois, la formule ne renverra que la dernière entrée en fonction de la position de la ligne. Parcourez simplement le résultat et les données d’échantillon pour comprendre cela.
SURTN pour extraire les lignes du dernier jour de chaque mois à partir de la table de données quotidiennes
Avant de commencer, assurez-vous que les données sont triées par ordre chronologique en fonction de la colonne de date.
Voici les étapes pour extraire le dernier jour de chaque mois à partir d’une table dans Google Sheets :
1. Ajouter une colonne de date et d’année avec les données
Logique : En utilisant SORTN, nous allons supprimer les doublons des données en fonction d’une colonne de fin de mois. Nous avons donc besoin d’une telle colonne dans la table existante. La formule ci-dessous le fait :
=ARRAYFORMULA({EOMONTH(A2:A20,0),A2:B20})Sélectionnez D2:D20, allez dans le menu Format > Nombre > Format de nombre personnalisé, et saisissez mmmm-yyy dans le champ donné. C’est facultatif.
Note : D2:D20 agit comme une colonne de mois et d’année.
2. Trier par mois et année (colonne de fin de mois) puis par ligne
Supposons que nous ayons unique les résultats ci-dessus à l’aide de SORTN. Nous obtiendrons ainsi une table contenant la première entrée de chaque mois. Nous devons donc trier ces données par mois et année (colonne de fin de mois) en ordre croissant, puis par numéro de ligne de 2 à 20 en ordre décroissant. Avec l’aide des fonctions SORT et ROW, nous pouvons répondre à nos besoins.
Formule générique :
SORT(Step_1_Formula,1,VRAI,LIGNE(A2:A20),FAUX) =sort({eomonth(A2:A20,0),A2:B20},1,VRAI,LIGNE(A2:A20),FAUX)
Note : Nous pouvons supprimer ARRAYFORMULA dans SORT. Vous ne le verrez donc pas ici avec Step_1_Formula.
Cela place les lignes à extraire en haut de chaque groupe de mois. Maintenant, nous pouvons extraire les lignes du dernier jour de chaque mois à partir des données quotidiennes.
3. Utilisez SORTN pour extraire les lignes du dernier jour de chaque mois à partir des données quotidiennes dans Google Sheets
Parmi les quatre modes de liaison de 0 à 3, nous pouvons utiliser le mode de liaison 2 ici dans SORTN. Cela permet de supprimer les lignes en double en fonction d’une colonne sélectionnée.
Formule générique :
=sortn(step_2_formula,9^9,2,1,VRAI)Explication de la formule générique :
- 9^9 – Selon les données actuelles, il y a 4 lignes dans le résultat. Nous pouvons utiliser 4 à la place de 9^9 dans la formule. Cependant, lorsque nous ne sommes pas certains du nombre de lignes dans le résultat, il n’est pas sûr d’utiliser un nombre fixe comme 4. L’utilisation de 9^9 (un nombre arbitrairement grand) est donc sûre pour retourner toutes les lignes du résultat.
- 2 – Mode de liaison pour supprimer les doublons.
- 1 – Position de la colonne, en fonction de laquelle nous voulons obtenir une table unique.
- VRAI – Pour trier la colonne 1 par ordre croissant.
Voici la formule basée sur la formule générique précédente :
=sortn(sort({eomonth(A2:A20,0),A2:B20},1,VRAI,LIGNE(A2:A20),FAUX),9^9,2,1,VRAI)Cela extraira les lignes du dernier jour de chaque mois à partir de la table de données quotidiennes dans Google Sheets. Veuillez consulter l’image 1 ci-dessus pour le résultat.
Il y a trois colonnes dans le résultat. Il s’agit d’une colonne de mois et d’année (fin du mois), d’une colonne de date de fin de mois et d’une colonne de montant.
Vous pouvez limiter le nombre de colonnes dans le résultat en utilisant la fonction QUERY comme suit :
=query(Final_Formula,"Select Col1,Col2,Col3")Dans la clause SELECT, incluez uniquement les colonnes requises. Par exemple, utilisez la formule suivante pour renvoyer les 2e et 3e colonnes :
=query(sortn(sort({eomonth(A2:A20,0),A2:B20},1,VRAI,LIGNE(A2:A20),FAUX),9^9,2,1,VRAI),"Select Col2,Col3")Comment puis-je adapter la formule à une plage ouverte ?
Il y a deux étapes à suivre pour adapter la formule à une plage ouverte :
- Rendez toutes les plages ouvertes dans la formule
Final_formula. - Remplacez
eomonth(A2:A,0)pariferror(eomonth(datevalue(A2:A),0)).
Ainsi, la formule pour une plage ouverte sera la suivante :
=sortn(sort({iferror(eomonth(datevalue(A2:A),0)),A2:B},1,VRAI,LIGNE(A2:A),FAUX),9^9,2,1,VRAI)Note : Ici aussi, vous pouvez envelopper la formule avec QUERY pour sélectionner uniquement les colonnes souhaitées.
Vlookup pour extraire les lignes du dernier jour de chaque mois à partir de la table de données quotidiennes
Il s’agit d’une alternative à la formule SORTN ci-dessus.
Jusqu’à l’étape 2, il n’y a pas de changements. Cela signifie que nous avons besoin de la formule Step_2_formula ici. Nous utiliserons la formule Step_2_formula comme plage de recherche pour extraire les lignes du dernier jour de chaque mois à partir de la table de données quotidiennes dans Google Sheets.
Permettez-moi de vous présenter d’abord la formule générique :
=arrayformula(ifna(vlookup(search_keys,step_2_formula,3,0)))Nous avons seulement besoin des clés de recherche à utiliser dans la fonction VLOOKUP. Les clés de recherche sont simplement les valeurs uniques de mois et d’année (fin de mois).
Clé de recherche :
=unique(ArrayFormula(iferror(eomonth(datevalue(A2:A),0))))Maintenant, écrivons la formule en suivant la formule générique :
=arrayformula(ifna(vlookup(D2:D,sort({iferror(eomonth(datevalue(A2:A),0)),A2:B},1,VRAI,LIGNE(A2:A),FAUX),3,0)))C’est tout.
Merci pour votre attention. Profitez-en !
