☰

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

tag-icon • tag-icon microsoft-excel worksheet-function
Obtenha as referências das células que não estão em branco

Preciso de ajuda com uma função. Na Coluna AI temos uma lista de nomes, mas eles estã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 ficar assim:

insira a descrição da imagem aqui

Responder1

As fórmulas de matriz a seguir funcionarão mesmo se algum dos nomes for idêntico. Insira-os B2com Ctrl+ Shift+ Entere 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 conforme 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 forma expandida:

=
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 todas as A$10substituídas por INDEX(A:A,MATCH("*",A:A,-1)).

Explicação:

A IF()função é 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 de 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 avalia como:

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

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

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 que não estão em branco. Para célula B1 SMALL()retorna 2, pois B2retorna 4, etc. Para B6e inferior retorna o #NUM!erro. (É por isso que existe uma IFERROR()função. Ela converte esses erros em espaços em branco.)

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

Responder2

Esta é uma maneira de listar valores e excluir células em branco. Veresta página de ajudapara mais detalhes.

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

insira a descrição da imagem aqui

Esta fórmula de matriz é inserida em B2:

=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 inserida com CtrlShiftEnter, em vez de apenas Enter. Se inserido corretamente, o Excel colocará a fórmula entre colchetes {} na barra de fórmulas.

Depois que a fórmula for inserida, clique na célula B2 e preencha a fórmula.

Caso haja nomes duplicados que precisam ser listados, esta fórmula de matriz removerá apenas 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))),"")

informação relacionada