Nous allons examiner la fonction AGGREGATE. Elle fait la même chose que la formule ARRAY ci-dessus sans être un tableau. AGGREGATE prend le format général suivant
AGGREGATE(Formula Number, Option Number, range/array, parameter)
Les formules 14 et 15 exécutent les fonctions LARGE et SMALL et effectuent des calculs de tableau dans le cadre de la fonction AGGREGATE. Par conséquent, n'utilisez pas de références complètes aux colonnes et aux lignes dans la fonction AGGREGATE, sinon vous risquez de vous retrouver avec des calculs excessifs. Cela peut à son tour ralentir votre système. La formule utilisée jusqu'à présent ressemble donc à ceci
AGGREGATE(15,
Le numéro de l'option peut être lu par vous-même lorsque vous le tapez. J'ai choisi 6 pour qu'il ignore les erreurs, ce qui est important pour le processus. Donc la formule devient :
AGGREGATE(15,6,
La partie suivante consiste donc à construire la liste de ce que vous voulez. Vous allez être intéressé par le numéro de ligne où vous avez spécifié le camion pour commencer. Nous allons le faire en divisant le numéro de ligne du calcul du tableau par un VRAI ou un FAUX. La chose importante à noter ici est qu'Excel convertit les VRAIS en 1 et les FAUX en 0 lorsqu'ils sont envoyés dans une opération mathématique. Ainsi, le calcul du tableau ressemblera à quelque chose comme :
ROW(B1:B6)/(B1:B6=233)
or if your truck number is stored as text
ROW(B1:B6)/(B1:B6="233")
Donc ce qui se passe, c'est que chaque fois que la ligne examinée ne correspond pas à 233, le diviseur devient FAUX. Le numéro de ligne est alors divisé par FALSE, ce qui convertit FALSE en 0. Cela signifie que vous obtenez une erreur de division par 0. Si nous ajoutons cela à la formule utilisée jusqu'à présent, l'option 6 ignorera toutes ces erreurs et ne laissera qu'une liste de numéros de ligne où une correspondance a été trouvée. La formule ressemble donc maintenant à ceci
AGGREGATE(15,6,ROW(B1:B6)/(B1:B6=233),
Il ne vous reste donc plus que l'option du paramètre. Normalement, j'utilise ce processus pour saisir le premier élément de la liste, donc je le règle sur 1. Si je l'utilise pour générer une liste, je le réglerai sur ROW(A1) et copier la formule vers le bas pour obtenir une liste 1, 2, 3, etc. Dans votre cas, vous allez avoir besoin de savoir plusieurs choses. La première est de savoir quelle est l'occurrence de votre camion. Est-ce le premier de la liste ? le dernier de la liste ? le seul de la liste ? vous pouvez obtenir quel camion de la liste vous utilisez en utilisant la formule suivante COUNTIF une fois que vous avez trouvé votre position dans la liste acceptable, vous pouvez en soustraire 1 pour obtenir la rangée de camions précédente, et vous pouvez y ajouter 1 pour obtenir la rangée de camions suivante. La formule COUNTIF se présente comme suit :
COUNTIF(The range to count in, what it is that is being counted)
Donc la formule pour votre cas serait :
COUNTIF($B$1:B1,B1)
Au fur et à mesure que cette formule est abaissée, remarquez que l'extrémité de la fourchette augmente, mais que le début reste inchangé. Ceci peut maintenant être placé dans la formule AGGREGATE. Pour obtenir le camion précédent, il faut
AGGREGATE(15,6,ROW($B$1:$B$6)/($B$1:$B$6=B1),COUNTIF($B$1:$B1,$B1)-1)
Pour le prochain camion, vous voudrez :
AGGREGATE(15,6,ROW($B$1:$B$6)/($B$1:$B$6=B1),COUNTIF($B$1:$B1,$B1)+1)
Oui, c'est bien que j'aie le rang mais tu as demandé la différence de rang. CEPENDANT GRÂCE À UNE MISE À JOUR VOUS VOULEZ VRAIMENT LE TEMPS !
Il me suffirait d'intégrer cette fonction dans une fonction INDEX et d'extraire les informations que je recherche en fonction du numéro de ligne. La fonction INDEX fonctionne de deux manières différentes. S'il s'agit d'une seule ligne dans une colonne ou une ligne, c'est 1D et s'il couvre plusieurs lignes et colonnes, c'est 2D. Pour 1D, il suffit d'indiquer à quel niveau de la liste vous voulez chercher. En 2D, vous devez indiquer la ligne ET la colonne à consulter. Voici donc deux informations intéressantes. Si vous mettez 0 pour la référence de la ligne ou de la colonne, alors il regarde la ligne ou la colonne entière au lieu d'une spécifique. L'autre information intéressante est que l'INDEX renvoie réellement une plage/cellule de référence, et non le contenu réel de la cellule directement. Il renverra arbitrairement F4 et ensuite F4 tirera le contenu de la cellule F4. Cela vous permet de faire des choses comme INDEX(...):INDEX(...) où les indices pourraient être utilisés pour définir le début et la fin d'une plage pour une autre formule. Ainsi, INDEX prend la forme de :
1D
INDEX(1D range to look in, how far into the list to look)
2D
INDEX(2D range to look in, what row to look at, what column to look at)
=INDEX($C$1:$C$6,AGGREGATE(15,6,ROW($B$1:$B$6)/($B$1:$B$6=B1),COUNTIF($B$1:$B1,$B1)-1))
l'heure d'arrivée suivante serait :
=INDEX($C$1:$C$6,AGGREGATE(15,6,ROW($B$1:$B$6)/($B$1:$B$6=B1),COUNTIF($B$1:$B1,$B1)+1))
et dans le cas où vous êtes le premier camion/seul camion, vous pouvez simplement renvoyer "" ou "Premier camion" en utilisant une fonction IFERROR comme suit :
=IFERROR(INDEX($C$1:$C$6,AGGREGATE(15,6,ROW($B$1:$B$6)/($B$1:$B$6=B1),COUNTIF($B$1:$B1,$B1)-1)),"First Truck")
Vous pourriez faire la même chose pour le dernier camion. Sinon, vous pourriez ajouter un texte indiquant DERNIER CAMION comme ceci :
=IFERROR(INDEX($C$1:$C$6,AGGREGATE(15,6,ROW($B$1:$B$6)/($B$1:$B$6=B1),COUNTIF($B$1:$B1,$B1)+1)),"Last Truck")
Dans l'exemple ci-dessous, la ligne d'en-tête a été incluse, de sorte que les plages de référence sont décalées d'une ligne par rapport au texte ci-dessus. Ajustez la référence en fonction de vos besoins.
![POC]()
Grâce à la réponse de Ron Rosenfeld qui fait une belle comparaison temporelle au lieu de compter, je remarque que cette méthode de comptage ne fonctionne que si les camions sont triés chronologiquement.
