Parfois, nous souhaitons insérer des lignes vierges pour séparer les débuts ou les fins de semaine dans Google Sheets. Comment pouvons-nous le faire?
Bien sûr, insérer des lignes vierges entre chaque changement de semaine dans une colonne de dates peut être utile pour améliorer la lisibilité et rendre les impressions des rapports plus propres.
Si vous disposez d’un grand ensemble de données, le faire manuellement peut être fastidieux. C’est pourquoi nous avons créé des formules pour automatiser le processus pour vous.
Abordons cela sous deux perspectives différentes:
- Insérer des lignes vierges à chaque changement de semaine dans des données existantes.
- Générer une séquence de dates séparées par chaque changement de semaine.
Dans cet article, nous partagerons deux formules qui répondent à ces objectifs, en commençant par les données existantes.
Les formules ont les caractéristiques clés suivantes :
- Ce sont des formules tableau.
- Elles n’utilisent pas de cellules auxiliaires.
- Vous pouvez augmenter ou diminuer le nombre de lignes vierges à insérer à chaque changement de semaine.
- Vous pouvez utiliser n’importe quel jour de la semaine comme séparateur.
Comment insérer des lignes vierges pour séparer les débuts ou les fins de semaine dans Google Sheets
Cliquez sur le lien ci-dessous pour copier mon exemple de feuille.
Maintenant, passons aux exemples.
Dans l’exemple suivant, j’ai un relevé de passif exceptionnel dans Google Sheets trié par dates de facturation. Je souhaite insérer des lignes vierges pour séparer chaque début de semaine, qui est le lundi.
Le relevé contient quatre colonnes : ID, Date de facture, Numéro de facture et Montant.
Dans la capture d’écran suivante, vous pouvez voir les données de l’exemple dans A1:D et le résultat de la formule dans F1:I. La formule en F1 remplit tout le résultat.
Prérequis
Pour utiliser la formule, vous devez connaître les éléments suivants :
- Plage de données : La plage de cellules contenant vos données, à l’exclusion de la ligne d’en-tête.
- Plage de la ligne d’en-tête : La plage de cellules contenant la ligne d’en-tête.
- Plage de colonne de dates : La plage de cellules contenant la colonne de dates.
- Début de la semaine : Le jour de la semaine que vous souhaitez utiliser comme séparateur. Utilisez l’un des nombres de 11 à 17, où 11 est le lundi et 17 est le dimanche.
Formule
La formule suivante dans Google Sheets insère des lignes vierges pour séparer les semaines qui commencent le lundi. En d’autres termes, elle insère une ligne vierge après chaque semaine qui se termine le dimanche.
=ARRAYFORMULA(LET(
range, A2:D,
header, A1:D1,
dt, B2:B,
at, 11,
helper, WEEKNUM(DATEVALUE(dt),at),
REDUCE(header,TOCOL(UNIQUE(helper),3), LAMBDA(a,v,IFERROR(VSTACK(a,FILTER(range,helper=v),))))
))Pour insérer une ligne vierge pour séparer les semaines qui commencent le dimanche et se terminent le samedi, remplacez 11 dans la formule par 17.
Comment insérer deux lignes vierges sous chaque changement de semaine
Pour insérer deux lignes vierges sous chaque changement de semaine dans une colonne de dates, ajoutez une virgule supplémentaire à la dernière partie de la formule. Par exemple :
=ARRAYFORMULA(LET(
range, A2:D,
header, A1:D1,
dt, B2:B,
at, 11,
helper, WEEKNUM(DATEVALUE(dt),at),
REDUCE(header,TOCOL(UNIQUE(helper),3), LAMBDA(a,v,IFERROR(VSTACK(a,FILTER(range,helper=v),,))))
))Les formules ci-dessus peuvent être utilisées pour insérer des lignes vierges pour séparer les débuts ou les fins de semaine dans Google Sheets. Les formules sont flexibles et peuvent être personnalisées pour répondre à vos besoins spécifiques.
Explication de la formule
En bref, la formule ci-dessus est une combinaison des fonctions WEEKNUM, UNIQUE et REDUCE.
- WEEKNUM : Retourne le numéro de semaine d’une date, en donnant le jour de la semaine où la semaine commence. Dans ce cas, la semaine commence le lundi.
- UNIQUE : Retourne une liste unique de valeurs à partir d’un tableau.
- REDUCE : Réduit un tableau de valeurs à une seule valeur en appliquant une fonction à chaque élément du tableau et en accumulant les résultats.
Voyons comment la formule insère des lignes vierges pour séparer les débuts ou les fins de semaine.
Numéros de semaine
Pour tester la formule, vous pouvez garder uniquement les données d’exemple dans A1:D. Ensuite, insérez la formule suivante dans la cellule F2 pour retourner le numéro de semaine des dates de A2:A, avec le début de la semaine le lundi :
=ARRAYFORMULA(WEEKNUM(DATEVALUE(B2:B),11))La fonction DATEVALUE garantit que la fonction WEEKNUM ne retourne que le numéro de semaine des cellules non vides. Habituellement, la fonction WEEKNUM renvoie un numéro même si la cellule est vide, ce qui peut provoquer des erreurs de formule.
Numéros de semaine uniques
Ensuite, insérez la formule UNIQUE suivante dans la cellule G2 :
=TOCOL(UNIQUE(F2:F),3)La fonction TOCOL garantit que les numéros de semaine uniques ne contiennent pas de valeur d’erreur.
Fonction Lambda
Enfin, insérez la formule REDUCE suivante dans la cellule I2, qui est la partie complexe à comprendre :
=REDUCE(A1:D1,G2:G11,LAMBDA(a,v,IFERROR(VSTACK(a,FILTER(A2:D,F2:F=v),))))La fonction REDUCE fonctionne en appliquant une fonction lambda à chaque élément du tableau et en accumulant les résultats.
La fonction lambda de cette formule prend deux arguments : a et v.
- a est l’accumulateur, qui est le résultat de l’application de la fonction lambda à tous les éléments précédents du tableau.
- v est l’élément actuel du tableau.
L’array ici est G2:G11, qui contient les numéros de semaine uniques et l’élément actuel du tableau est G2. La valeur actuelle dans l’accumulateur est A1:D1 (valeur initiale dans l’accumulateur).
La fonction lambda renvoie toutes les lignes de la plage de données (A2:D) ayant le même numéro de semaine (v) en faisant correspondre v dans F2:F.
FILTER(A2:D,F2:F=v)La fonction REDUCE applique ensuite la fonction VSTACK à l’accumulateur et au résultat de la fonction lambda, puis à une cellule vide.
VSTACK(a,FILTER(A2:D,F2:F=v),)Cela empile les trois tableaux verticalement, créant un nouveau tableau qui contient la valeur initiale (ligne d’en-tête) et toutes les lignes de la plage de données qui ont le même numéro de semaine que G2, puis une ligne avec des valeurs d’erreur en raison de la virgule que nous avons placée dans VSTACK.
La fonction REDUCE continue ensuite d’itérer sur le tableau des numéros de semaine uniques (G2:G11), en appliquant la fonction lambda et la fonction VSTACK à chaque élément du tableau.
Le IFERROR supprime les erreurs et renvoie des cellules vides. Le résultat est un seul tableau qui contient toutes les lignes de la plage de données, avec des lignes vierges insérées pour séparer les débuts de semaine.
Générer une séquence de dates et séparer les débuts ou les fins de semaine avec des lignes vierges
Parfois, nous voulons générer une séquence de dates et séparer les débuts ou les fins de semaine avec une ou plusieurs lignes vierges. Dans ce cas, nous pouvons utiliser la formule ci-dessus en remplaçant la plage de données par une formule SEQUENCE. Nous n’avons pas besoin de spécifier la plage de dates car nous n’avons qu’une seule colonne.
Par exemple, si vous souhaitez générer une séquence de 100 dates dans un ordre séquentiel à partir de la date spécifiée dans la cellule A1. La formule suivante s’en chargera :
=ARRAYFORMULA(LET(
range, SEQUENCE(100,1,A1),
header, "text",
at, 11,
helper, WEEKNUM(range,at),
REDUCE(header,TOCOL(UNIQUE(helper),3), LAMBDA(a,v,VSTACK(a,FILTER(range,helper=v),)))
))où :
- range est SEQUENCE(100,1,A1)
- header est le titre que vous souhaitez dans la première cellule. Ici, il est spécifié comme « texte ».
- at est 11, ce qui sépare les dates en fonction du lundi comme jour de début de semaine.
Pour utiliser la formule, entrez-la dans une cellule de Google Sheets et appuyez sur Entrée. La formule générera une séquence de 100 dates, séparées par des lignes vierges au début de la semaine.
Liens connexes :
