La duplication de lignes en fonction des dates de début et de fin dans Google Sheets peut être réalisée en utilisant la fonction REDUCE comme outil principal. Sa logique est similaire à celle de la saisie des dates manquantes.
L’avantage principal de cette méthode REDUCE réside dans sa fonctionnalité de tableau, ce qui élimine la nécessité de cellules d’aide, de colonnes ou de lignes supplémentaires.
Cependant, il faut prendre en compte un inconvénient avant de l’utiliser. La fonction REDUCE peut entraîner un ralentissement des performances ou cesser de fonctionner avec de grandes quantités de données.
Gardez cette limitation à l’esprit lorsque vous automatisez la duplication de lignes en fonction des dates de début et de fin dans Google Sheets.
La duplication de lignes en fonction des dates de début et de fin dans Google Sheets
À des fins de test, nous utiliserons des données d’exemple dans la plage A1:F4 comme suit (A1:F1 contient les étiquettes des champs).
Ces données comprennent les colonnes pour l’Article, la Quantité, De, À, Destination et Statut, qui se trouvent respectivement dans les colonnes A, B, C, D, E et F. En substance, elles représentent l’état de l’approvisionnement en matériel.
Tout d’abord, plongeons dans la formule et comprenons comment l’adapter à votre tableau avec une mise en page différente. Ensuite, nous passerons à la partie expliquant la formule intrigante.
`=ArrayFormula(REDUCE( TOCOL(,1), C2:C4, LAMBDA(a, v, VSTACK(a, LET( seq, SEQUENCE(OFFSET(v, 0, 1)-OFFSET(v, 0, 0)+1, 1, OFFSET(v, 0, 0)), lkp, IF(seq, ROW(v)), fnl, HSTACK( VLOOKUP( lkp, HSTACK(ROW(C2:C4), A2:F4), SEQUENCE(1, COLUMNS(A2:F4), 2) ), seq ), CHOOSECOLS(fnl, 1, 2, 7, 5, 6)) ) ) ))
Remarque : La formule se trouve dans la cellule A8 dans la capture d’écran ci-dessus. De plus, veuillez formater la colonne des dates (dans ce cas, la plage C8:C16) en tant que date en appliquant Format > Nombre > Date.
Adapter la formule à une plage de tableau différente
Dans cette formule, C2:C4 représente la plage de dates de début, et A2:F4 constitue la plage de tableau complète.
Vous devez spécifier ces plages de référence, c’est-à-dire la plage de dates de début et la plage de tableau complète. La formule suppose que la plage de dates de fin est la colonne suivante à la date de début et utilise cette information.
En plus de ce qui précède, vous devez apporter une autre modification à la formule, spécifiquement dans la dernière partie : CHOOSECOLS(fnl, 1, 2, 7, 5, 6).
Dans ce contexte, 1, 2, 5 et 6 font référence aux colonnes correspondantes dans la plage de tableau A2:F4. Les colonnes 3 et 4 n’ont pas besoin d’être spécifiées car elles représentent les colonnes de dates de début et de fin.
Le tableau est composé d’un total de 6 colonnes. La 7e colonne représente la colonne de dates étendue ; j’ai remplacé 3 et 4 par 7.
En résumé, les colonnes réelles sont CHOOSECOLS(fnl, 1, 2, 3, 4, 5, 6), mais nous avons utilisé CHOOSECOLS(fnl, 1, 2, 7, 5, 6)
Maintenant, j’espère que vous pouvez facilement dupliquer des lignes en fonction des dates de début et de fin respectives dans Google Sheets.
Logique de la formule et explication détaillée
Nous avons utilisé la fonction REDUCE pour dupliquer des lignes en fonction des dates de début et de fin, en utilisant une fonction lambda avec trois composantes principales.
Logique de la formule :
La formule se compose de trois composantes : SEQUENCE et IF logiques, VLOOKUP et CHOOSECOLS.
Dans la première partie, l’objectif est d’étendre les dates de début et de fin du premier enregistrement (ligne) dans une seule colonne et de renvoyer les numéros de ligne correspondants.
Par exemple, si la date de début dans la cellule C2 est le 1er janvier 2024 et la date de fin dans la cellule D2 est le 5 janvier 2024, l’extension donnera les dates du 01/janv, 02/janv, 03/janv, 04/janv et 05/janv.
La formule renvoie les numéros de ligne (par exemple, 2) répétés 5 fois dans une colonne et les dates de 01 à 05 dans une autre colonne.
Ces numéros de ligne servent de clés de recherche dans VLOOKUP pour récupérer les valeurs des lignes correspondantes dans le tableau A2:F4. Le résultat est ajouté horizontalement aux dates étendues renvoyées par la première partie, créant ainsi un tableau à 7 colonnes.
La troisième partie consiste à réorganiser les colonnes renvoyées par VLOOKUP, ainsi que les dates qui ont été ajoutées.
Le résultat à chaque itération (chaque expansion) est empilé verticalement à l’aide d’un accumulateur, et c’est le résultat final.
Cette logique sous-tend la formule qui duplique des lignes en fonction des dates de début et de fin dans Google Sheets. Passons à la décomposition de la formule.
Décomposition de la formule :
REDUCE(TOCOL(,1), C2:C4, LAMBDA(a, v, …
La fonction REDUCE itère sur chaque élément du tableau C2:C4 (colonne de dates de début) et exécute une fonction lambda. Elle prend une valeur initiale (TOCOL(,1)) et un tableau (C2:C4).
Syntaxe : REDUCE(valeur_initiale, tableau_ou_plage, fonction_lambda)
La valeur initiale TOCOL(,1), en fait une fonction TOCOL, représente une cellule vide, indiquant à la fonction REDUCE d’ignorer les cellules vides, évitant ainsi qu’une cellule vide ne reste en haut de la colonne du résultat.
Dans la fonction lambda, « a » est la valeur initiale dans l’accumulateur, et « v » est l’élément actuel du tableau. La fonction lambda commence par VSTACK(a, indiquant que REDUCE empile le résultat à chaque itération verticalement.
Voici l’explication de la fonction lambda utilisée dans le REDUCE pour étendre les dates en fonction des dates de début et de fin, une partie clé de la duplication des enregistrements.
1. Partie SEQUENCE et IF logique :
Examinons ce que fait la fonction lambda avec le premier élément du tableau, c’est-à-dire la valeur dans la cellule C2.
SEQUENCE(OFFSET(v, 0, 1)-OFFSET(v, 0, 0)+1, 1, OFFSET(v, 0, 0))
Où :
- lignes : OFFSET(v, 0, 1)-OFFSET(v, 0, 0)+1 équivaut à date_fin – date_debut + 1.
- colonnes : 1.
- début : OFFSET(v, 0, 0), représentant la date de début.
- pas : omis.
Cela suit la syntaxe de SEQUENCE :
SEQUENCE(lignes, [colonnes], [début], [pas])
La formule ci-dessus étend les dates de début et de fin. Nous utilisons la fonction LET pour nommer cette expression de valeur « seq ».
IF(seq, ROW(v))
Cette partie IF renvoie les numéros de ligne correspondant à « seq ». La fonction LET attribue le nom « lkp » à cette valeur, ce qui signifie la valeur de recherche.
2. Partie VLOOKUP :
VLOOKUP(lkp, HSTACK(ROW(C2:C4), A2:F4), SEQUENCE(1, COLUMNS(A2:F4), 2))
La fonction VLOOKUP recherche « lkp », qui sont les numéros de ligne des dates de début et de fin étendues, dans la plage HSTACK(ROW(C2:C4), A2:F4). Elle renvoie tous les enregistrements correspondants dans toutes les colonnes, sauf la première colonne. Le résultat est empilé horizontalement avec les dates étendues.
La sortie est nommée « fnl » dans la fonction LET.
3. Partie CHOOSECOLS :
CHOOSECOLS(fnl, 1, 2, 7, 5, 6)
Cette partie CHOOSECOLS sélectionne les colonnes à l’exception des colonnes de date de début et de fin. Au lieu de cela, elle choisit la colonne étendue.
Cela conclut l’explication de la formule conçue pour dupliquer des lignes en fonction des dates de début et de fin dans Google Sheets.
Quels sont les avantages de la duplication de lignes en fonction des dates de début et de fin dans Google Sheets ?
La duplication de lignes en fonction des dates de début et de fin dans Google Sheets présente plusieurs avantages. Voici les plus importants :
- Analyse de données ciblée : La filtrage des dates permet de visualiser les données sur des jours spécifiques. Des formules comme MOIS, ANNÉE, NUMSEMAINE ou plage de semaines dans une colonne d’aide permettent de réduire les données à des années, des mois, des semaines spécifiques ou des plages de dates personnalisées.
- Agrégation des données avec les tableaux croisés dynamiques : Les données dupliquées peuvent être agrégées efficacement à l’aide des tableaux croisés dynamiques dans Google Sheets. Cela permet l’agrégation par jour, mois, année-mois, trimestre et autres intervalles de temps personnalisables.
- Gain de temps pour la saisie des données : La saisie de données en fonction des dates de début et de fin permet de gagner du temps, et des formules peuvent être appliquées pour étendre automatiquement le jeu de données. Cela simplifie le processus de saisie des données et réduit les risques d’erreurs.
Ce sont quelques-uns des avantages immédiats de la duplication des enregistrements en fonction des dates de début et de fin dans Google Sheets, offrant une meilleure focalisation des données, des capacités d’analyse et une efficacité accrue dans la saisie des données.
Conclusion
Dans ce tutoriel, nous avons utilisé VLOOKUP avec REDUCE pour dupliquer des enregistrements en fonction des dates de début et de fin. Alternativement, nous pouvons omettre l’utilisation de VLOOKUP et dépendre de OFFSET à des fins similaires.
Cependant, il est important de noter que l’utilisation de OFFSET peut affecter les performances de la formule. Nous avons limité l’utilisation de OFFSET uniquement pour obtenir les dates de début et de fin.
Cette limitation est nécessaire car REDUCE ne gère pas simultanément deux tableaux, ce qui nous empêche de spécifier à la fois les dates de début et de fin dans la même fonction.
Ressources :
- How to Insert Duplicate Rows in Google Sheets
- Assign the Same Sequential Numbers to Duplicates in a List in Google Sheets
- Expand Dates and Assign Values in Google Sheets (Array Formula)
