Sélection de zone INDEX et identité de référence

Documentation HotXLS · Fidélité de calcul et de conversion

La version 2.384.101 étend INDEX(source,row_num,column_num,area_num) pour accepter le quatrième argument sur les tableaux calculés et composer les vraies références multi-zones prises en charge via les noms définis et LET ; les sélecteurs de ligne, de colonne et de zone peuvent broadcaster ensemble

Un tableau calculé ou plusieurs zones de référence

Une constante ou un tableau calculé constitue une seule zone source, donc INDEX(SEQUENCE(2,2),2,2,1) renvoie 4 ; omettre complètement le quatrième argument sélectionne aussi la zone 1, tandis qu'un quatrième argument explicitement vide est un sélecteur de zone invalide

Une union de références peut contenir plusieurs zones réelles de feuille de calcul sur la même feuille ; area_num choisit la zone dans l'ordre de l'union, et les numéros de ligne et de colonne s'appliquent à l'intérieur de cette zone sélectionnée

Pour les exemples ci-dessous, Source!A1:B2 contient {1,2;3,4} et Source!D1:E2 contient {10,20;30,40} ; Other!A1:B2 contient {100,200;300,400}

Noms définis, LET et CHOOSE

Un nom défini local pris en charge dont l'expression est (Source!$A$1:$B$2,Source!$D$1:$E$2) conserve ses deux zones ; les noms imbriqués gardent cette identité, et AREAS(NamedAreas) renvoie 2

INDEX(NamedAreas,2,2,2) renvoie 40, tandis que ISREF(INDEX(NamedAreas,0,0,2)) renvoie TRUE ; ROW, COLUMN, ROWS, COLUMNS et les consommateurs d'agrégats continuent d'utiliser les coordonnées de la référence sélectionnée

Les noms de références relatives se résolvent à chaque position consommatrice, et un nom local de feuille de calcul prime sur un nom de portée classeur de même orthographe ; préserver l'identité de référence ne fige pas un nom relatif à sa première position d'évaluation

LET(areas,(Source!A1:B2,Source!D1:E2),INDEX(areas,2,2,2))
LET(areas,(Source!A1:B2,Source!D1:E2),ISREF(INDEX(areas,0,0,2)))

Ces expressions renvoient respectivement 40 et TRUE ; une liaison à SEQUENCE(2,2) reste un tableau de valeurs calculées et n'acquiert pas d'identité de référence

Un CHOOSE scalaire qui choisit une vraie référence conserve cette référence sélectionnée, y compris un choix sur une autre feuille : INDEX(CHOOSE(2,Source!A1:B2,Other!A1:B2),2,2) renvoie 400 et le résultat de référence scalaire correspondant passe ISREF

Un CHOOSE avec un sélecteur tableau combine les valeurs selon l'orientation du sélecteur au lieu de construire une union de zones de référence ; CHOOSE({1,2},Source!A1:B2,Source!D1:E2) renvoie {1,20;3,40}, et un sélecteur de zone INDEX supplémentaire valant 2 renvoie toujours #REF!

Trois sélecteurs tableaux

Les sélecteurs de ligne, de colonne et de zone acceptent des valeurs scalaires ou des tableaux ; les dimensions singleton broadcastent, des orientations ligne/colonne opposées forment un produit cartésien, des orientations identiques apparient les positions ; les positions non appariées dans des formes inégales contiennent des erreurs #N/A individuelles

INDEX((Source!A1:B2,Source!D1:E2),{1;2},2,{1,2})
INDEX((Source!A1:B2,Source!D1:E2),2,2,{2;1})

La première expression renvoie {2,20;4,40} et la seconde {40;4} ; les valeurs sélectionnées conservent leurs représentations de chaîne, Boolean, numérique et d'erreur typée

Si un sélecteur au moins est un tableau, le résultat est un tableau de valeurs et non une référence ; un zéro de ligne ou de colonne, ou un sélecteur de ligne ou de colonne omis, choisit le premier membre de cet axe à chaque position du résultat

Cela diffère de l'extraction purement scalaire : deux zéros scalaires de ligne et de colonne renvoient la zone sélectionnée entière, mais INDEX((Source!A1:B2,Source!D1:E2),0,0,{1,2}) renvoie {1,10}, et non deux tableaux deux par deux imbriqués ; sa somme vaut 11

Coercition de zone et erreurs

Les sélecteurs de zone tronquent les valeurs numériques fractionnaires vers zéro et acceptent le texte numérique et les valeurs Boolean ; 1.9, "1" et TRUE sélectionnent la zone 1

La zone zéro, FALSE, un quatrième argument explicitement vide, les valeurs négatives et les fractions qui tronquent vers zéro renvoient #VALUE! ; le texte non numérique renvoie aussi #VALUE!, tandis qu'une zone positive au-delà du nombre de zones disponibles renvoie #REF!

Les erreurs typées des sélecteurs se propagent avec leurs codes d'erreur existants ; pour un sélecteur tableau, une zone invalide produit une erreur uniquement à sa position de résultat correspondante, donc INDEX(SEQUENCE(2,2),2,2,{1,2}) renvoie {4,#REF!}

Les erreurs existantes ne sont pas remplacées par les vérifications de bornes ultérieures : INDEX(#N/A,1,1,2) renvoie #N/A, et INDEX(SEQUENCE(2,2),#N/A,1,#DIV/0!) conserve le #N/A du sélecteur de ligne

Une référence invalide qualifiée par une feuille locale existante reste #REF!, y compris Source!#REF! et 'Plan! O''Brien'!#REF! ; un nom défini contenant cette erreur de référence locale conserve aussi #REF! quand on le passe à INDEX ou ISFORMULA

Le jeton d'erreur locale s'utilise avec les opérateurs pourcentage ou intersection implicite, et les espaces après le séparateur de feuille sont acceptés ; les espaces entre le nom de la feuille de calcul et son séparateur sortent de la syntaxe acceptée

Les chaînes de formules entre guillemets telles que "Source!#REF!" restent du texte ; un jeton de référence qualifié par feuille à l'intérieur d'une constante tableau sort de la grammaire source prise en charge

Les sélecteurs de ligne et de colonne gardent leurs règles existantes d'extraction par zéro et de validation des valeurs négatives ; la zone zéro ne signifie jamais l'extraction de la source entière

Stockage, dépendances et limites

Le calcul de spill automatique XLSX peut faire grandir ou rétrécir les résultats de sélecteurs tableaux et libère les successeurs obsolètes détenus par l'ancre ; les expressions prises en charge s'évaluent aussi dans une destination CSE fixe explicite et survivent à l'enregistrement et à la réouverture XLSX

Un sélecteur nommé peut exiger un stockage dynamique conservateur même si son résultat évalué est scalaire ; un marqueur dynamique à une cellule ne change ni la valeur ni l'identité d'un résultat de référence pris en charge

Les références sources sélectionnées et les noms imbriqués participent au suivi des dépendances, donc les éditions ultérieures de la source sont observées après recalcul ; le parcours des sélecteurs et les payloads de matrices générés consomment les budgets existants de pas d'évaluation et de mémoire de tableaux

Limites

Une union directe dont les zones appartiennent à des feuilles différentes renvoie #VALUE!, même si le numéro de zone demandé n'en sélectionnerait qu'une ; un CHOOSE scalaire choisissant une seule référence reste l'alternative inter-feuilles prise en charge

Une union de tableaux calculés, de constantes ou d'expressions de valeurs n'est pas une vraie référence multi-zones ; les unions de tableaux calculés liées par LET renvoient #VALUE!, et la syntaxe directe d'union de tableaux de tableaux n'est pas acceptée comme forme source

Ce contrat couvre les formes locales testées de références et de tableaux calculés ; il n'implique pas la prise en charge de chaque graphe de références externes, de chaque fonction produisant des références ni de conteneurs de tableaux imbriqués arbitraires

Un qualificateur de feuille inconnu n'est pas normalisé en erreur de référence locale connue ; Excel peut créer un graphe de liens externes pour une formule telle que NoSuchSheet!#REF!, et reproduire ce graphe sort de ce contrat de calcul local