Faça uma fórmula de matriz no excel calcular apenas em linhas ativas

Question

Quando o Excel calcula fórmulas sobre uma coluna (por exemplo, uma pesquisa), é inteligente o suficiente para calcular apenas até a última linha ativa, em vez de também ser executado em todas as linhas em branco.

Com fórmulas de matriz, parece que esse comportamento muda e é calculado em toda a coluna (um milhão de linhas no Excel 2010). Existe uma maneira de forçar uma fórmula de matriz para calcular apenas sobre o intervalo ativo? (além de substituir referências de coluna por uma referência generosa que cubra o intervalo ativo, por exemplo, A: 1: A10000 substituindo A: A em uma planilha que você sabe que nunca terá 10000 linhas.

Por exemplo, existe uma função que você pode executar em uma linha que retornará a referência da última linha ativa? Algo como = Last (A: A) que retornaria uma referência ou um número de linha

Se você tiver uma planilha com um intervalo contíguo de cima para baixo, suponho que você poderia usar =INDIRECT("B1:B"&COUNTA(B:B))) , mas não é ideal (sendo volátil, não atualizando quando colunas são adicionadas à planilha e somente trabalhando em planilhas com uma coluna que não contém linhas em branco.

    

Answer 1

Infelizmente, o uso dos INDIRECT e OFFSET voláteis é quase ubiquamente recomendado em tais casos nos vários locais do Excel, embora exista um perfeitamente bom, minimamente volátil (na verdade, volátil "na pasta de trabalho aberta "apenas, como eu entendo) set-up usando INDEX .

Para determinar a última linha não vazia em uma coluna, coluna A, será útil saber o tipo de dados das entradas nessa coluna e também se existem cadeias nulas ("") presentes.

Embora existam setups disponíveis que funcionem independentemente do tipo de dados das entradas em questão, eles são necessariamente de um tipo tal que eles devem processar cada elemento dentro do intervalo passado para eles. Como tal - e particularmente se o intervalo ao longo do qual é necessário calcular é bastante grande - eles podem ser extremamente pesados em termos de recursos (a segunda sugestão de kyle é um bom exemplo, tendo que processar mais de um milhão de células, independentemente de se última célula não vazia está na linha 1 ou na linha 1048576).

Também vale a pena armazenar o valor da última linha não vazia como um Nome Definido, LRow .

As definições possíveis para LRow são as seguintes:

1) Se houver nenhuma string nula e todas as entradas forem não numéricas , você pode definir LRow como:

=MATCH("Ω",A:A)

2) Se houver nenhuma string nula e todas as entradas forem numéricas , você pode definir LRow como:

=MATCH(9.9E+307,A:A)

3) Se houver nenhuma string nula e as entradas forem de um tipo de dados misturado , isto é, algumas numéricas, outras não numéricas, você pode definir LRow como:

=MAX(MATCH("Ω",A:A),MATCH(9.9E+307,A:A))

Se houver cadeias nulas dentro do intervalo, as soluções se tornarão necessariamente mais complexas e não será mais possível evitar uma construção na qual cada célula do array seja processada individualmente. Como tal, o melhor que podemos fazer é minimizar o intervalo que passamos para ser processado.

Por isso, temos:

4) Se houver strings nulas , você pode definir LRow como:

=MATCH(1,0/(LEN(A1:INDEX(A:A,MAX(MATCH("Ω",A:A),MATCH(9.9E+307,A:A))))>0))

(Você poderia, claro, tornar isso ainda mais eficiente se o tipo de dados for consistentemente numérico ou não numérico, como acima).

que requer entrada de array (CSE) e que, em geral, reduzirá muito o intervalo sendo processado, especialmente comparado a algo como:

=MATCH(1,0/(LEN(A:A)>0))

que é desastroso em termos de eficiência.

Note que as funções adicionais dentro do primeiro são de muito pouco detrimento: é muito mais importante reduzir o número de células sendo processadas do que se preocupar com uma chamada de função adicional ou duas.

Agora você pode usar isso em uma construção INDEX para definir dinamicamente seu intervalo. Por exemplo:

=A1:INDEX(A:A,LRow)

que, como mencionado, é pouco volátil.

Atenciosamente