Ordem Alfabética automática utilizando Intervalo dinâmico nomeado

Luiz Antônio de Almeida · 2 de outubro de 2015

- Por favor! Quem poderia ajudar-me?

- Fiz uso de uma orientação dada aqui neste fórum (publicada em 02/out/2012) pelo membro Osvaldo sobre Ordenar Alfabeticamente de forma automática utilizando Intervalo Dinâmico Nomeado que auxiliou-me bastante.

- Porém, estou com uma dificuldade para adaptar a minha necessidade ao que foi proposto pelo Osvaldo.

- Trata-se de ordenar alfabeticamente (de forma automatica) no Excel - no meu caso - uma lista com 20 nomes. Ocorre que, vez ou outra esta lista fica incompleta, por exemplo com 18 ou 14 nomes e, utilizando a fórmula recomendada pelo Osvaldo para eliminar o espaço em branco no início da lista, permanece. Sendo que neste caso - com uma lista incompleta - o normal seria que o espaço em branco ficasse no final da lista.

Exemplo:

1- Ademir Freitas 1-

2- Livia Mendes 2-

3- Carlos da Silva 3- Ademir Freitas

4- Antônio Ribeiro 4- Carlos da Silva

5- José Santos 5- Livia Mendes

- O exemplo acima, com cinco nomes, ilustra o que está ocorrendo com meu arquivo após utilizar a fórmula recomendada pelo Osvaldo: "Intervalo Dinâmico Nomeado", uma fórmula matricial que ordena os nomes de cima para baixo deixando espaço em branco no final quando esta lista não estiver completa.

=DESLOC(Plan1!$A$1;0;0;CONT.VALORES(PLAN1!$A:$A);1)

e

=SE(LIN()>CONT.VALORES(A:A);"";INDICE(lista;CORRESP(MENOR(CONT.SE(lista;"<"&lista);LIN(1:1));CONT.SE(lista;"<"&lista);0)))

- Imagino que não estou conseguindo porque para fazer a lista com os nomes, estou utilizando mais de uma planilha no mesmo arquivo. Será que procede?

- Vou anexar o meu arquivo para melhor compreensão de quem porventura proponha-se ajudar-me.

- Desde já, muito agradecido pela atenção,

Exemplo Desloc Intervalo Dinamico.xlsx

3 de outubro de 2015

Olá, Luiz.

A origem do seu problema está na fórmula abaixo que você utilizou para nomear o intervalo dinâmico:

=DESLOC(Chamada!$P$7;0;0;CONT.VALORES(Chamada!P:P);1)

A função CONT.VALORES conta células preenchidas, conta inclusive fórmulas que retornam vazio, como é o caso do intervalo considerado (coluna 'P'). Essa função retorna 20, no entanto há somente 18 nomes na sua lista e mais duas fórmulas retornando vazio. Essa função é aplicável nos casos em que a lista de nomes é formada inserindo manualmente os nomes, mas se aplicada a listas formadas a partir de fórmulas poderá gerar esse tipo de erro.

Faça um teste: substitua 'P:P' na fórmula por $P$7:$P$24, que é o intervalo que contém os nomes. E veja os resultados na coluna 'B'.

Para corrigir substitua a fórmula acima por esta. Coloquei o intervalo até 'P200' mas você pode ampliar se houver previsão de mais nomes. Não coloque a referência da coluna inteira, assim >> 'P:P'

=DESLOC(Chamada!$P$7;0;0;SOMARPRODUTO(--(Chamada!$P$7:$P$200<>""));1)

Aproveite para ajustar a fórmula que está em 'B3', coloque esta e arraste para baixo (esta é matricial também):

=SEERRO(SE(LIN()-7>SOMARPRODUTO(--($P$7:$P$200<>""));"";ÍNDICE(lista;CORRESP(MENOR(CONT.SE(lista;"<"&lista);LIN(1:1));CONT.SE(lista;"<"&lista);0)));"")