Obtém as referências das células que não estão em branco

Question

Preciso de ajuda com uma função. Na Coluna A, tenho uma lista de nomes, mas eles são separados por células em branco. Existe alguma maneira de mostrar esses nomes na coluna B, mas sem as células em branco?

O resultado deve ser assim:

    

Answer 1

As seguintes fórmulas de array funcionarão mesmo se qualquer um dos nomes for idêntico. Digite-os em B2 com Ctrl + Deslocamento + Enter , depois preencha.

Este usa um intervalo fixo com base nos seus dados de amostra:

{=IFERROR(INDEX(A:A,SMALL(IF(A$1:A$10<>"",ROW(A$1:A$10),""),ROW()-ROW(A$1)+1)),"")}

Este usa um intervalo dinâmico, ajustando-se automaticamente à medida que você adiciona mais nomes à coluna A:A :

=IFERROR(INDEX(A:A,SMALL(IF(A$1:INDEX(A:A,MATCH("*",A:A,-1))<>"",ROW(A$1:INDEX(A:A,MATCH("*",A:A,-1))),""),ROW()-ROW(A$1)+1)),"")

A mesma fórmula dinâmica acima, em um formato expandido:

=
IFERROR(
  INDEX(
    A:A
  , SMALL(
      IF(
        A$1:INDEX(A:A,MATCH("*",A:A,-1))<>""
      , ROW(A$1:INDEX(A:A,MATCH("*",A:A,-1)))
      , ""
      )
    , ROW()-ROW(A$1)+1
    )
  )
, ""
)

Como você pode ver, esta segunda fórmula é simplesmente a primeira com todos os A$10 substituídos por INDEX(A:A,MATCH("*",A:A,-1)) .

Explicação:

A função IF() é equivalente a:

IF(
  {"";"Camilo Georgi";"";"Carla Suarez Navarro";"";"Belinda Bencic";"";"Grace Min";"";"Johanna Larsson"}<>""
, {1;2;3;4;5;6;7;8;9;10}
, ""
)

que, como o Excel expande automaticamente as constantes em matrizes constantes de comprimento correspondentes, torna-se:

IF(
  {FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}
, {1;2;3;4;5;6;7;8;9;10}
, {"";"";"";"";"";"";"";"";"";""}
)

que é avaliado como:

{"";2;"";4;"";6;"";8;"";10}

A função SMALL() torna-se então:

SMALL({"";2;"";4;"";6;"";8;"";10},ROW()-1+1)

e como SMALL() ignora strings, isso é equivalente a:

SMALL({2;4;6;8;10},ROW())

Observe que os números são os índices dos nomes não em branco. Para a célula B1 SMALL() retorna 2 , para B2 retorna 4, etc. Para B6 e menor, retorna o erro #NUM! . (É por isso que existe uma função IFERROR() . Ela converte esses erros em espaços em branco.)

Finalmente, a função INDEX() puxa os nomes usando os índices.

    

Answer 2

Aqui está uma maneira de listar os valores e excluir as células em branco. Veja esta ajuda página para mais detalhes.

A fórmula para fazer isso requer uma linha acima da primeira linha de dados. Os títulos servem esse propósito na tabela abaixo.

EstafórmuladematrizéinseridaemB2:

=IFERROR(INDEX($A$2:$A$10,MATCH(0,COUNTIF($B$1:B1,$A$2:$A$10&"")+IF($A$2:$A$10=",1,0),0)),"")

Como uma fórmula de matriz, ela deve ser digitada com Ctrl Deslocar Enter , em vez de apenas Enter . Se inserido corretamente, o Excel colocará a fórmula entre chaves {} na barra de fórmulas.

Após a introdução da fórmula, clique na célula B2 e preencha a fórmula.

Caso haja nomes duplicados que precisem ser listados, essa fórmula de matriz só removerá espaços em branco e não removerá duplicatas:

=IFERROR(INDEX(A:A,SMALL(INDEX(NOT(ISBLANK($A$2:$A$10))*ROW($A$2:$A$10),0),COUNTBLANK($A$2:$A$10)+ROW(C1))),"")