Formule générique
(=ROW(INDIRECT(start&":"&end)))
Sommaire
Pour créer un tableau de nombres comme (1; 2; 3; 4; 5), vous pouvez utiliser une formule basée sur les fonctions ROW et INDIRECT. Cette technique est le plus souvent utilisée dans les formules matricielles qui nécessitent un tableau numérique pour un traitement quelconque. Dans l'exemple illustré, la formule en D5 est:
(=ROW(INDIRECT(B5&":"&C5)))
qui renvoie un tableau comme (1; 2; 3; 4; 5).
Remarque: lorsqu'il est entré dans une seule cellule, Excel n'affichera que le premier élément du tableau. Utilisez F9 dans la barre de formule pour voir le résultat réel du tableau. Normalement, vous utiliserez cette formule dans une formule matricielle plus grande, entrée avec Ctrl + Maj + Entrée.
Explication
Remarque: dans Excel 365, la nouvelle fonction SEQUENCE est un moyen meilleur et plus simple de créer un tableau de nombres. La méthode expliquée ci-dessous fonctionnera dans les versions précédentes.
Le cœur de cette formule est une chaîne qui représente des lignes. Par exemple, pour créer un tableau avec 10 nombres, vous pouvez coder en dur une chaîne en INDIRECT comme ceci:
=ROW(INDIRECT("1:10"))
La fonction INDIRECT interprète ce texte comme signifiant la plage 1:10 (10 lignes) et la fonction ROW renvoie le numéro de ligne pour chaque ligne de cette plage à l'intérieur d'un tableau.
L'exemple illustré utilise une version plus générique de la formule qui récupère respectivement les numéros de début et de fin de B5 et C5, de sorte que la solution ressemble à ceci:
=ROW(INDIRECT(B5&":"&C5)) =ROW(INDIRECT(1&":"&5)) =ROW(INDIRECT("1:5")) =ROW(1:5) =(1;2;3;4;5)
La raison pour laquelle INDIRECT est utilisé dans la formule est de se prémunir contre les changements de feuille de calcul. Sans INDIRECT, l'insertion ou la suppression de lignes peut modifier la référence de plage, par exemple:
=ROW(1:5)
deviendra:
=ROW(1:4)
Si la ligne 1 est supprimée. Étant donné que INDIRECT fonctionne avec une référence construite avec du texte, il n'est pas affecté par les modifications de la feuille de calcul.
Numéros de ligne relatifs dans une plage
Si vous avez besoin d'un tableau composé des numéros de ligne relatifs d'une plage, vous pouvez utiliser une formule comme celle-ci:
=ROW(range)-ROW(range.firstcell)+1
Voir cette page pour une explication complète.
Valeurs négatives
La fonction ROW ne gère pas les nombres négatifs, vous ne pouvez donc pas mélanger des nombres négatifs pour le début et la fin . Cependant, vous pouvez appliquer des opérations mathématiques au tableau créé par ROW. Par exemple, la formule suivante créera ce tableau: (-5; -4; -3; -2; -1)
=ROW(INDIRECT(1&":"&5))-6
Nombres dans l'ordre inverse, n à 1
Pour créer un tableau de nombres positifs dans l'ordre décroissant, de n à 1, vous pouvez utiliser une formule comme celle-ci:
=ABS(ROW(INDIRECT("1:"&n))-(n+1))
