Utiliser la fonction Query de Google Sheets nous permet d’extraire tous les enregistrements, c’est-à-dire les lignes, du mois précédent. Que ce soit le mois en cours ou le mois précédent, vous pouvez filtrer les données en utilisant Query. Les fonctions scalaires nécessaires sont month(), year() et now().
Nous pouvons utiliser ces fonctions dans une colonne de date ou de DateTime pour filtrer les enregistrements du mois précédent dans Google Sheets. En fait, un sujet très similaire est déjà expliqué sur ce blog – Comment utiliser la fonction Query pour calculer les données du mois en cours dans Google Sheets. Mais cela ne concerne pas le mois précédent.
De plus, contrairement à l’extraction des lignes du mois en cours, lors du filtrage du mois précédent, vous rencontrerez un problème, en particulier lorsque le mois en cours est janvier (expliqué en détail ci-dessous).
J’ai donc pensé à écrire cet article. Commençons par un exemple, comme d’habitude.
Obtenir toutes les lignes du mois précédent dans Google Sheets en utilisant la fonction Query
Données d’exemple : Relevé de compte (SOA) – Plage de données de A1:F10.
Note importante : Selon l’horloge de mon système, le mois en cours est décembre.
Si vous utilisez le tableau ci-dessus à 6 colonnes, vous devez modifier les dates de la colonne 2 si le mois en cours selon votre horloge système n’est pas décembre.
Dans ce cas, veuillez modifier les mois # 11 (mois précédent) et 12 (mois en cours) en fonction du mois de votre horloge système. Changez également l’année si elle est différente.
Requête pour obtenir les lignes du mois précédent dans Google Sheets
C’est la solution la plus simple et ne fonctionnera pas si le mois en cours est janvier.
La formule Query Google Sheets suivante extraira toutes les lignes du mois précédent (les lignes contenant les dates du mois précédent dans la deuxième colonne de la plage).
Veuillez noter que le tableau ci-dessus est dans la plage A1:F10 de la feuille nommée ‘SOA’.
Vous pouvez utiliser la requête suivante pour obtenir les enregistrements/lignes du mois précédent dans la même feuille ou dans n’importe quelle feuille du même fichier.
=query(SOA!A1:F, »Select * where month(B)=month(now())-1″,1)
Voici l’explication de la syntaxe de la formule Query ci-dessus :
- Select * renvoie toutes les colonnes. Vous ne voulez que les colonnes 1-2 ? Alors, par exemple, utilisez Select A,E pour obtenir les colonnes INV # et BALANCE.
- Pour obtenir le mois précédent dans Query, nous pouvons utiliser dynamiquement month(B)=month(now())-1 dans la clause WHERE.
- Le nombre 1 à la fin est utilisé pour retourner la ligne d’en-tête avec la sortie. Si vous ne voulez pas de ligne d’en-tête, modifiez la plage en SOA!A2:F et mettez 1 à 0.
Comment résoudre le problème de janvier alors ?
Requête pour obtenir les lignes du mois dernier en tenant compte de l’année également
Dans la requête ci-dessus pour filtrer les données du mois précédent, j’ai utilisé un exemple de « Relevé de compte » (SOA). Il s’agit d’un petit ensemble de données.
Si vous utilisez un ensemble de données de vente volumineux avec plusieurs lignes, il peut y avoir des lignes contenant le même mois mais dans des années différentes.
Par exemple, des enregistrements du 01/12/18 et du 05/12/19. Dans de tels cas, nous devons inclure la fonction scalaire year() pour améliorer davantage le filtre Query.
Normalement, nous pensons pouvoir utiliser la requête suivante en utilisant la fonction scalaire year().
=query(SOA!A1:F, »Select * where month(B)=month(now())-1 and year(B)=year(now()) »,1)
Mais cela renverra de mauvaises sorties si le mois en cours est janvier !
Pouvez-vous expliquer la raison ? Oui ! Lisez la suite.
Google Sheets Query pour extraire toutes les lignes du mois précédent lorsque le mois en cours est janvier
Si le mois en cours est janvier, year(B)=year(now()) ne correspondra pas. Il doit être year(B)=year(now())-1.
Pour tous les autres mois, nous pouvons utiliser la comparaison précédente – la première, c’est-à-dire year(B)=year(now()).
De même, month(now())-1 ne fonctionnera pas.
Alors comment résoudre dynamiquement le problème de filtre du mois et de l’année précédents ?
Vous pouvez utiliser la logique SI dans la requête Google Sheets, comme expliqué précédemment ici – Comment utiliser la fonction SI dans la formule Query de Google Sheets.
Mais le tutoriel lié ci-dessus n’est pas codé pour notre filtre ci-dessus qui implique le mois et l’année. Voici la logique SI que nous pouvons utiliser dans la requête.
=if(month(today())<>1, »month(B)=month(now())-1 and year(B)=year(now()) », »month(B)=11 and year(B)=year(now())-1″)
Nous pouvons utiliser la déclaration IF ci-dessus dans la requête comme suit.
=query(SOA!A1:F, »Select * where « &if(month(today())<>1, »month(B)=month(now())-1 and year(B)=year(now()) », »month(B)=11 and year(B)=year(now())-1″),1)
Mise à jour : Voici une nouvelle formule.
=query(SOA!A1:F, »Select * where month(B)= »&month(eomonth(today(),-1))-1& » and year(B)= »&year(eomonth(today(),-1)))
De cette manière, nous pouvons utiliser Google Sheets Query pour extraire toutes les lignes du mois précédent.
Résumé des enregistrements du mois dernier
C’est juste un conseil supplémentaire.
Supposons que vous souhaitiez additionner le montant du crédit (colonne SOA D).
La formule pour additionner le montant total du crédit du mois dernier qui tient compte du mois et de l’année.
Modifiez select * en select D puis entourez toute la requête avec Sum.
=sum(query(SOA!A1:F, »Select D where « &if(month(today())<>1, »month(B)=month(now())-1 and year(B)=year(now()) », »month(B)=11 and year(B)=year(now())-1″),1))
Cela peut aussi fonctionner.
=sum(query(SOA!A1:F, »Select D where month(B)= »&month(eomonth(today(),-1))-1& » and year(B)= »&year(eomonth(today(),-1))))
Si simple, n’est-ce pas ?
