Seleção de área do INDEX e identidade de referência

Documentação HotXLS · Fidelidade de cálculo e conversão

A versão 2.384.101 estende INDEX(source,row_num,column_num,area_num) para aceitar o quarto argumento com matrizes calculadas e compor referências reais de múltiplas áreas com suporte por meio de nomes definidos e LET; seletores de linha, coluna e área podem sofrer difusão juntos

Uma matriz calculada ou múltiplas áreas de referência

Uma constante ou matriz calculada é uma única área de fonte, de modo que INDEX(SEQUENCE(2,2),2,2,1) retorna 4; omitir completamente o quarto argumento também seleciona a área 1, enquanto um quarto argumento explicitamente vazio é um seletor de área inválido

Uma união de referências pode conter várias áreas reais de planilha na mesma folha; area_num seleciona a área na ordem da união, e os números de linha e coluna se aplicam dentro dessa área selecionada

Nos exemplos abaixo, Source!A1:B2 contém {1,2;3,4} e Source!D1:E2 contém {10,20;30,40}; Other!A1:B2 contém {100,200;300,400}

Nomes definidos, LET e CHOOSE

Um nome definido local com suporte cuja expressão é (Source!$A$1:$B$2,Source!$D$1:$E$2) retém as duas áreas; nomes aninhados retêm essa identidade, e AREAS(NamedAreas) retorna 2

INDEX(NamedAreas,2,2,2) retorna 40, enquanto ISREF(INDEX(NamedAreas,0,0,2)) retorna TRUE; ROW, COLUMN, ROWS, COLUMNS e consumidores de agregação continuam usando as coordenadas de referência selecionadas

Nomes de referência relativos são resolvidos em cada posição de consumo, e um nome local de planilha tem precedência sobre um nome de escopo da pasta de trabalho com a mesma grafia; preservar a identidade de referência não congela um nome relativo na primeira posição de avaliação dele

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)))

Essas expressões retornam 40 e TRUE respectivamente; uma vinculação a SEQUENCE(2,2) continua sendo uma matriz de valores calculada e não adquire identidade de referência

Um CHOOSE escalar que seleciona uma referência real retém essa referência selecionada, incluindo uma escolha em outra planilha: INDEX(CHOOSE(2,Source!A1:B2,Other!A1:B2),2,2) retorna 400 e o resultado de referência escalar correspondente passa em ISREF

Um CHOOSE com seletor de matriz combina valores pela orientação do seletor em vez de construir uma união de áreas de referência; CHOOSE({1,2},Source!A1:B2,Source!D1:E2) retorna {1,20;3,40}, e um seletor de área adicional de 2 no INDEX ainda retorna #REF!

Três matrizes de seletor

Seletores de linha, coluna e área aceitam valores escalares ou matrizes; dimensões singleton sofrem difusão, orientações opostas de linha e coluna formam um produto cruzado, e orientações correspondentes emparelham posições; posições sem par em formas desiguais contêm erros #N/A individuais

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

A primeira expressão retorna {2,20;4,40} e a segunda retorna {40;4}; os valores selecionados retêm suas representações de string, Boolean, numérica e de erro tipado

Se qualquer seletor é uma matriz, o resultado é uma matriz de valores em vez de uma referência; uma linha ou coluna zero, ou um seletor de linha ou coluna omitido, seleciona o primeiro membro desse eixo em cada posição de resultado

Isso difere da extração somente escalar: dois zeros escalares de linha e coluna retornam a área selecionada inteira, mas INDEX((Source!A1:B2,Source!D1:E2),0,0,{1,2}) retorna {1,10}, e não duas matrizes aninhadas de dois por dois; a soma desse resultado é 11

Coerção de área e erros

Seletores de área truncam valores numéricos fracionários em direção a zero e aceitam texto numérico e valores Boolean; 1.9, "1" e TRUE selecionam a área 1

Área zero, FALSE, um quarto argumento explicitamente vazio, valores negativos e frações que truncam para zero retornam #VALUE!; texto não numérico também retorna #VALUE!, enquanto uma área positiva além da contagem de áreas disponíveis retorna #REF!

Erros tipados de seletor propagam com seus códigos de erro existentes; para um seletor de matriz, uma área inválida produz um erro apenas na posição de resultado correspondente, de modo que INDEX(SEQUENCE(2,2),2,2,{1,2}) retorna {4,#REF!}

Erros existentes não são substituídos por verificações de limites posteriores: INDEX(#N/A,1,1,2) retorna #N/A, e INDEX(SEQUENCE(2,2),#N/A,1,#DIV/0!) retém o #N/A do seletor de linha

Uma referência inválida qualificada por uma planilha local existente permanece #REF!, incluindo Source!#REF! e 'Plan! O''Brien'!#REF!; um nome definido que contém esse erro de referência local também retém #REF! quando passado a INDEX ou ISFORMULA

O token de erro local pode ser usado com operadores de porcentagem ou de interseção implícita, e espaço em branco após o separador de planilha é aceito; espaço em branco entre o nome da planilha e o separador dele está fora da sintaxe aceita

Strings de fórmula entre aspas, como "Source!#REF!", permanecem texto; um token de referência qualificado por planilha dentro de uma constante de matriz está fora da gramática de fonte com suporte

Seletores de linha e coluna mantêm suas regras existentes de extração com zero e validação de valores negativos; área zero nunca significa extração da fonte inteira

Armazenamento, dependências e limites

O cálculo automático de despejo XLSX pode aumentar ou reduzir resultados de matrizes de seletor e libera os seguidores obsoletos de propriedade da âncora; expressões com suporte também avaliam dentro de um destino CSE fixo explícito e sobrevivem a salvamento e reabertura XLSX

Um seletor nomeado pode exigir armazenamento dinâmico conservador mesmo quando o resultado avaliado dele é escalar; um marcador dinâmico de uma célula não altera o valor nem a identidade de um resultado de referência com suporte

Referências de fonte selecionadas e nomes aninhados participam do rastreamento de dependências, de modo que edições posteriores da fonte são observadas após o recálculo; a travessia de seletores e os payloads de matriz gerados consomem os orçamentos existentes de etapas de avaliação e de memória de matriz

Limites

Uma união direta cujas áreas pertencem a planilhas diferentes retorna #VALUE!, mesmo quando o número de área solicitado selecionaria apenas uma delas; um CHOOSE escalar selecionando uma referência continua sendo a alternativa entre planilhas com suporte

Uma união de matrizes calculadas, constantes ou expressões de valor não é uma referência real de múltiplas áreas; uniões de matrizes calculadas vinculadas por LET retornam #VALUE!, e a sintaxe direta de união de matriz de matrizes não é aceita como forma de fonte

Este contrato cobre as formas testadas de referência local e de matriz calculada; ele não implica suporte a todo grafo de referências externas, toda função que produz referência ou contêineres aninhados arbitrários de matriz

Um qualificador de planilha desconhecido não é normalizado como um erro conhecido de referência local; o Excel pode criar um grafo de link externo para uma fórmula como NoSuchSheet!#REF!, e corresponder a esse grafo está fora deste contrato de cálculo local