Fundo
Estou com um problema em que desejo fazer referência a um intervalo nomeado e exibir seus valores. O intervalo é uma lista de valores, mas não sei de antemão quantos elementos existem na lista. Digamos que haja entre 1 e 8 valores na lista.
Minha maneira de fazer isso é usar uma fórmula de matriz em um intervalo longo de 8 e usar IFNA() para mascarar a saída #N/A. É aceitável que eu tenha algum espaço vazio na saída, mas exibir #N/A não parece tão profissional. O ISNA não funciona e suspeito que seja por causa da fórmula da matriz que altera o comportamento do ISNA. Exemplo abaixo na imagem.
Limitações na solução
Os dados serão usados em tabelas dinâmicas em um estágio posterior, portanto, simplesmente mascarar os dados como invisíveis não funcionará - eu realmente não quero obter nenhum valor #N/A. Uma string vazia está bem; string vazia também não é uma resposta correta, mas pelo menos falha com um pouco mais de elegância.
A solução deve ser relativamente fácil de manter para outras pessoas, pois estou desenvolvendo o modelo para alguns colegas e eles podem querer fazer mais alterações posteriormente. Portanto, quero evitar fórmulas muito longas e uso extensivo do gerenciador de nomes.
Pergunta
Qual é a maneira "correta" de simplesmente copiar o intervalo de tamanho variável sem obter N/A?
... onde "adequado" significa fórum simples e compacto, fácil de manter e que não contém dados ocultos.
Responder1
Em vez disso, use INDEX. Coloque-o na primeira célula da saída e copie o número desejado de linhas:
=IFERROR(INDEX(rng_1,ROW(1:1)),"")
O ROW(1:1) irá iterar à medida que é copiado puxando o próximo na linha. Quando acabar, gerará um erro e ""será colocado em seu lugar.
Responder2
Qual é a maneira correta de simplesmente "copiar o array" de um intervalo de tamanho variável sem obter N/A?
Não acho que exista um, mas talvez alguns outros métodos possam funcionar para o seu problema, como a formatação condicional da coluna com a matriz ou o uso de uma fórmula que não seja de matriz.
Formatação condicional:
Formate condicionalmente uma coluna/intervalo para alterar a formatação do texto da célula de erro para branco ou qualquer coisa que combine com a cor da sua célula.
Exemplo de fórmula:
Use uma fórmula não matricial e faça com que a fórmula lide com o erro.
=IFERROR(IF(INDEX(Pets,ROWS($J$1:J1),COLUMNS($J$1:J1))="","",INDEX(Pets,ROWS($J$1:J1),COLUMNS($J$1:J1))),"")
Responder3
Com a ajuda da resposta de Scott Craners, decidi o seguinte:
Deve ser um pouco menos sujeito a erros, já que o ROWScomando faz referência à mesma área em que a saída foi gravada. Desta forma não se deve inserir linhas destruindo muito a fórmula sem perceber.
Não estou satisfeito com esta solução, mas acho que terá que servir ...