Parfois, vous souhaiterez écrire une formule pour retourner les points de départ et d’arrivée d’une liste de nombres ou de dates dans Google Sheets. Cela peut vous aider à identifier les valeurs manquantes dans la séquence.
Il est facile de trouver les points de départ et d’arrivée à partir d’une séquence de nombres ou de dates dans Google Sheets.
Le point de départ est le plus petit nombre de la séquence, et le point d’arrivée est le plus grand nombre de la séquence. Vous pouvez les trouver en utilisant les fonctions MIN et MAX et en les reliant avec un tiret.
Cependant, il y aura plusieurs points de départ et d’arrivée lorsqu’il y a des nombres manquants dans la séquence. Cela est dû au fait que les nombres manquants créeront des lacunes dans la séquence, ce qui entraînera plusieurs points de départ et d’arrivée.
Par exemple, si vous considérez la liste {5, 6, 7, 35, 36, 39, 40, 41, 42, 43}, les points de départ et d’arrivée seront les plages 5-7, 35-36 et 39-43.
Pour générer ce type de plage, nous devons coder une formule complexe qui est également facile à utiliser.
Formule matricielle pour retourner les points de départ et d’arrivée à partir d’une liste dans Google Sheets
J’ai deux formules, une pour les nombres et l’autre pour les dates.
Les formules sont presque identiques. La seule différence est la mise en forme supplémentaire des dates ajoutée à la formule qui retourne les points de départ et d’arrivée à partir d’une liste de dates.
Voici la formule pour retourner les plages de dates à partir d’une liste de dates, en mettant en évidence la partie que vous devriez supprimer lorsque vous l’utilisez avec une colonne numérique.
Formule principale :
=ARRAYFORMULA(
LET(
list, E2:E,
seq, SPLIT(FLATTEN(SPLIT(TEXTJOIN("|",TRUE,IFNA( XLOOKUP( SEQUENCE(MAX(list)-MIN(list)+1,1,MIN(list)), list, list ),",")),",")),"|"),
fltr, FILTER(seq,CHOOSECOLS(seq,1)<> "|"),
BYROW(fltr,LAMBDA(r, TEXTJOIN("-",TRUE,TO_DATE(MIN(r)),IF(MAX(r)=MIN(r),,TO_DATE(MAX(r))))))
)
)Oui, vous devez supprimer les fonctions TO_DATE de la formule lorsque vous l’utilisez avec une liste numérique.
Mis à part cela, vous devez effectuer seulement un changement dans la formule. Il faut remplacer E2:E par la plage qui contient la liste dans votre feuille.
Si vous êtes quelqu’un qui est intéressé par l’apprentissage des formules avancées de Google Sheets, vous voudrez peut-être savoir comment cette formule retourne plusieurs points de départ et d’arrivée à partir d’une liste en une seule étape.
Je vais expliquer cela en premier. Puis je vous montrerai comment utiliser le résultat retourné pour compter ou additionner les valeurs d’une autre colonne se situant dans cette plage.
Anatomie de la formule
Il y a quatre étapes clés impliquées dans la formule ci-dessus. Les voici :
Étape 1
La première formule de l’étape, dans la cellule H2, est une formule XLOOKUP matricielle.
Formule :
=ARRAYFORMULA(XLOOKUP(SEQUENCE(MAX(F2:F)-MIN(F2:F)+1,1,MIN(F2:F)),F2:F,F2:F))Syntaxe :
XLOOKUP(clé_recherche, plage_recherche, plage_resultat, [valeur_absente], [mode_correspondance], [mode_recherche])Où :
- clé_recherche : est SEQUENCE(MAX(F2:F)-MIN(F2:F)+1,1,MIN(F2:F)), qui est la séquence remplie en utilisant les valeurs MIN et MAX de la liste.
- plage_recherche : est F2:F
- plage_resultat : est F2:F
La formule correspond aux clés de recherche dans la plage de recherche et renvoie les valeurs correspondantes de la plage de résultat. Le but est de supprimer les valeurs manquantes dans la liste de la séquence.
Note : La sortie sera des valeurs de date si la liste contient des dates. C’est pourquoi nous avons utilisé TO_DATE dans notre formule principale pour les formater à nouveau en dates.
Il s’agit d’une des étapes principales pour obtenir les points de départ et d’arrivée à partir d’une liste de dates ou de nombres dans Google Sheets.
Étape 2
Veuillez consulter le résultat de la première étape dans la plage H2:H16. La fonction XLOOKUP place #N/A là où la séquence présente des lacunes. À l’étape suivante, nous les remplacerons par une virgule, puis nous regrouperons toutes les valeurs avec TEXTJOIN. Le séparateur sera une barre verticale.
Voici la formule de la deuxième étape utilisée dans la cellule I2 :
=ARRAYFORMULA(TEXTJOIN("|",TRUE,IFNA(H2:H,",")))Étape 3
À l’étape suivante, nous allons diviser deux fois le résultat précédent avec SPLIT, puis les regrouper avec FLATTEN. J’ai placé la formule suivante dans la cellule I3 :
=ARRAYFORMULA(SPLIT(FLATTEN(SPLIT(I2,",")),"|"))Étape 4
La formule suivante dans la cellule I11 filtre les lignes contenant une barre verticale dans la première colonne :
=FILTER(I3:N9,I3:I9<>"|")Étape 5 (Fin)
Nous devons simplement extraire les valeurs minimales et maximales de chaque ligne du résultat de l’étape 4 et les réunir avec un tiret.
Nous pouvons utiliser le LHF BYROW suivant pour cela :
=BYROW(I11:N12,LAMBDA(r,TEXTJOIN("-",TRUE,MIN(r),MAX(r))))De cette manière, nous pouvons obtenir les points de départ et d’arrivée à partir d’une liste de dates ou de nombres dans Google Sheets.
Agréger les données correspondant aux points de départ et d’arrivée générés à partir d’une liste
Au tout début de ce tutoriel, j’ai expliqué le principal objectif de retourner les points de départ et d’arrivée à partir d’une liste de nombres ou de dates dans Google Sheets. Il s’agit de trouver les valeurs manquantes dans la séquence.
L’autre objectif est de résumer les données en fonction de la plage retournée par la formule. Je vais l’expliquer avec deux exemples. Je suis sûr que vous trouverez cela intéressant.
Dans l’exemple suivant, nous avons des dates d’achat, des articles et des quantités d’achat dans la plage A1:C.
Notre formule dans la cellule E2 retourne les points de départ et d’arrivée de la liste de dates (dates d’achat) dans la plage A2:A. Je ne répète pas la formule, vous pouvez simplement remplacer E2:E dans ma formule principale par A2:A.
Notre attention se porte sur les formules des colonnes F et G.
SOMME.SI.ENS : sommes des valeurs dans une plage comprise entre les points de départ et d’arrivée (plage de dates) dans Google Sheets
J’ai utilisé la formule SUMIF suivante dans la cellule F2 pour additionner les valeurs qui se situent entre les points de départ et d’arrivée, également appelée plage de dates, dans la cellule E2.
Syntaxe : SUMIF(plage, critère, [plage_summe])
=SUMIF(
ESTENTRE($A$2:$A,CHOOSECOLS(SPLIT(E2,"-"),1),CHOOSECOLS(SPLIT(E2,"-"),2)),
VRAI,
$C$2:$C
)Où :
- plage : ESTENTRE($A$2:$A,CHOOSECOLS(SPLIT(E2, »-« ),1),CHOOSECOLS(SPLIT(E2, »-« ),2))
- critère : VRAI
- plage_summe : C$2:$C
J’ai copié et collé cette formule dans les cellules F3 et F5.
La formule dans la cellule F4 est =SUMIF($A$2:$A,E6,$C$2:$C), que j’ai aussi copiée et collée dans la cellule F6.
Note : Le séparateur dans la fonction SPLIT est un tiret moyen (–), pas un tiret (-).
NOMBRE.SI.ENS : comptage des dates correspondant aux points de départ et d’arrivée (plage de dates) dans Google Sheets
Nous pouvons compter les valeurs correspondant aux points de départ et d’arrivée retournés à partir des dates d’achat dans la plage A2:A. Pour cela, nous pouvons utiliser la fonction COUNTIF.
Syntaxe : COUNTIF(plage, critère)
J’ai utilisé la formule COUNTIF suivante dans la cellule G2 :
=COUNTIF(
ESTENTRE($A$2:$A,CHOOSECOLS(SPLIT(E2,"-"),1),CHOOSECOLS(SPLIT(E2,"-"),2)),
VRAI
)Où :
- plage : ESTENTRE($A$2:$A,CHOOSECOLS(SPLIT(E2, »-« ),1),CHOOSECOLS(SPLIT(E2, »-« ),2))
- critère : VRAI
J’ai copié et collé cette formule dans les cellules G3 et G5.
La formule COUNTIF dans la cellule G4 est =COUNTIF(A2:A,E4), que j’ai également copiée et collée dans la cellule G6.
