Comment pouvons-nous étendre les dates, comme les dates de début et de fin de projet, et attribuer des valeurs, telles que des noms de tâches (pour un projet), dans Google Sheets? Nous pouvons utiliser la fonction SEQUENCE (pour étendre les dates) et VLOOKUP (pour attribuer des valeurs) dans Google Sheets. Une autre façon de faire est de remplacer SEQUENCE par la fonction ROW. Cependant, SEQUENCE, étant une fonction relativement nouvelle, est plus simple à utiliser dans ce cas.
Mise à jour :
L’expansion des dates et l’attribution de valeurs deviennent nettement plus simples avec l’introduction de la fonction LAMBDA dans Google Sheets. J’ai inclus cette solution à la fin du tutoriel.
Ancienne méthode pour étendre les dates et attribuer des valeurs dans Google Sheets
Il y a deux inconvénients à l’expansion des dates et à l’attribution des valeurs en utilisant cette méthode traditionnelle (la méthode utilisée avant le lancement de LAMBDA et des fonctions auxiliaires associées) dans Google Sheets. Quels sont-ils?
- La date de début de la « Tâche 2 » doit être supérieure à la date de fin de la « Tâche 1 », et cette condition s’applique également aux autres tâches.
- La colonne « Description » (colonne C) ne doit pas être vide.
Si vous avez deux tâches qui tombent dans la même période ou se chevauchent, la seule option est d’afficher les tâches séparées par des virgules.
Exemple :
Supposons que j’ai deux tâches pendant la période du 02/03/2020 au 05/03/2020. Je peux les représenter comme suit :
| Début | Fin | Description |
|---|---|---|
| 02/03/2020 | 03/03/2020 | Tâche 1 |
| 04/03/2020 | 05/03/2020 | Tâche 2 |
Si des tâches sont vides, filtrez les données vers une nouvelle plage et utilisez ces données comme source :
=FILTER(A1:C, C1:C <> "")
Si vous trouvez les solutions ci-dessus insatisfaisantes, vous pouvez recourir à des onglets d’aide. Référez-vous à l’onglet « Note » de mon échantillon de feuille de calcul à la fin de cet article pour plus d’informations.
Étapes
Voici les instructions étape par étape pour étendre les dates et attribuer des valeurs dans Google Sheets.
Vous trouverez toutes les étapes dans mon exemple de feuille de calcul, partagé à la fin de cet article.
1. Trouvez les dates minimales et maximales :
Pour obtenir les dates les plus petites et les plus grandes dans la plage A1:B, commencez par saisir les formules suivantes :
Dans la cellule G2 : =MIN(A2:B)
Dans la cellule H2 : =MAX(A2:B)
2. Étendez les dates minimales et maximales :
Générez une liste de dates séquentielles (valeurs de date) entre les dates minimum (G2) et maximum (H2), incluses :
Dans la cellule I2 : =SEQUENCE(DAYS(H2, G2)+1, 1, G2)
Sélectionnez la plage I2:I et appliquez le format Format > Nombre > Date.
3. Table d’aide à partir des données source :
Aplatissez les données de la plage A2:B4 dans les colonnes K2:K et copiez-collez les tâches correspondantes dans les colonnes L2:L.
Insérez une ligne après chaque tâche et saisissez les dates qui devraient être end_date_above + 1. Référez-vous à la capture d’écran ci-dessous pour plus de précisions.
Notes :
- Si la date de début d’une tâche coïncide avec la date de fin de la tâche précédente + 1, il n’est pas nécessaire d’insérer une ligne.
- L’automatisation de cette modification manuelle des données source est possible à l’aide d’une formule. Référez-vous à la cellule K2 dans l’onglet ‘Étape 3 Also Auto’ pour un exemple.
4. Recherche verticale :
Insérez la formule VLOOKUP suivante dans la cellule J2 :
=ArrayFormula(VLOOKUP(I2:I, K2:L, 2, 1))
Avec cette étape, nous avons étendu les dates et attribué des valeurs dans Google Sheets.
Maintenant, combinons les formules et supprimons les lignes indésirables du résultat.
5. Combinez les formules des étapes 1, 2 et 4 :
Voici les étapes pour combiner les formules des étapes 1, 2 et 4.
Tout d’abord, combinez les formules des étapes 2 (I2) et 4 (J2) comme suit dans la cellule M2 (ignorez toute erreur possible) :
={SEQUENCE(DAYS(H2, G2)+1, 1, G2), ArrayFormula(VLOOKUP(I2:I, K2:L, 2, 1))}
Ceci est comme {formule de l’étape 2, formule de l’étape 4}.
Ensuite, modifiez-la et remplacez les références de cellule H2 et G2 par les formules des cellules correspondantes :
={SEQUENCE(DAYS(MAX(A2:B), MIN(A2:B))+1, 1, MIN(A2:B)), ArrayFormula(VLOOKUP(I2:I, K2:L, 2, 1))}
Faites un autre changement à cette formule. Remplacez I2:I par la formule I2 (étape 2) :
={SEQUENCE(DAYS(MAX(A2:B), MIN(A2:B))+1, 1, MIN(A2:B)), ArrayFormula(VLOOKUP(SEQUENCE(DAYS(H2, G2)+1, 1, G2), K2:L, 2, 1))}
Une fois de plus, modifiez-la pour remplacer les références de cellules restantes H2 et G2 par les formules correspondantes :
={SEQUENCE(DAYS(MAX(A2:B), MIN(A2:B))+1, 1, MIN(A2:B)), ArrayFormula(VLOOKUP(SEQUENCE(DAYS(MAX(A2:B), MIN(A2:B))+1, 1, MIN(A2:B)), K2:L, 2, 1))}
Enfin, utilisez QUERY pour supprimer (filtrer) les lignes indésirables (les lignes qui ne contiennent pas de tâches dans la deuxième colonne) :
=QUERY({SEQUENCE(DAYS(MAX(A2:B), MIN(A2:B))+1, 1, MIN(A2:B)), ArrayFormula(VLOOKUP(SEQUENCE(DAYS(MAX(A2:B), MIN(A2:B))+1, 1, MIN(A2:B)), K2:L, 2, 1))},"SELECT * WHERE Col2 <> ''")
N’oubliez pas de supprimer les valeurs des colonnes G à J. Gardez la table d’aide (K2:L) telle quelle.
Nouvelle formule LAMBDA pour étendre les dates et attribuer des valeurs dans Google Sheets
Veuillez remonter en haut et vous référer à l’image #1 pour les données d’exemple dans les cellules A1:C1.
Ci-dessous, vous trouverez une nouvelle méthode basée sur LAMBDA pour étendre les dates et attribuer des valeurs dans Google Sheets. Je recommande d’utiliser cette solution plutôt que la méthode précédente.
Pour mettre en œuvre cette nouvelle approche, saisissez la formule basée sur LAMBDA suivante dans la cellule E2 après avoir effacé E2:F :
=ArrayFormula(SPLIT(TOCOL(MAP(A2:A, B2:B, C2:C, LAMBDA(a, b, c, IF(a*b, SEQUENCE(1, b-a+1, a)&"|"&c,))), 1), "|"))
Sélectionnez E2:E et appliquez le format Format > Nombre > Date.
Pour un exemple illustratif, veuillez vous référer à l’onglet « Lambda Eg 1 » de mon échantillon de feuille de calcul.
Anatomie de la formule et logique
Conservez uniquement les données d’exemple dans les cellules A1:C de votre feuille de calcul. Décortiquons la formule étape par étape.
Initialement, l’expansion de deux dates, par exemple A2 et B2, en utilisant la fonction SEQUENCE est simple. Le défi réside dans la réplication de cette opération dans chaque ligne sans faire glisser la formule manuellement vers le bas.
Placez la formule suivante dans la cellule D1 (elle renvoie des valeurs de date ; ignorez le formatage) :
=IF(A2*B2, SEQUENCE(1, B2-A2+1, A2),)
Pour répéter cette formule dans chaque ligne de la plage A2:C4, utilisez la fonction d’aide Lambda MAP. Dans la cellule D1, saisissez :
=MAP(A2:A4, B2:B4, C2:C4, LAMBDA(a, b, c, IF(a*b, SEQUENCE(1, b-a+1, a),)))
Attribuez des tâches à chaque date dans la sortie en la modifiant comme suit :
=ArrayFormula(MAP(A2:A4, B2:B4, C2:C4, LAMBDA(a, b, c, IF(a*b, SEQUENCE(1, b-a+1, a)&"|"&c,))))
Assurez-vous de l’utiliser comme une formule matricielle.
Aplatissez la sortie (de plusieurs colonnes à une colonne) en utilisant la fonction TOCOL, qui filtre également les lignes vides en une seule étape :
=ArrayFormula(TOCOL(MAP(A2:A4, B2:B4, C2:C4, LAMBDA(a, b, c, IF(a*b, SEQUENCE(1, b-a+1, a)&"|"&c,))), 1))
Divisez le résultat en deux colonnes au délimiteur « | ». Ouvrez les plages A2:A4, B2:B4 et C2:C4 dans la formule, respectivement, par A2:A, B2:B et C2:C.
Exemple Sheet
