
Responder1
As fórmulas de matriz a seguir funcionarão mesmo se algum dos nomes for idêntico. Insira-os B2
com 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$10
substituí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 B2
retorna 4, etc. Para B6
e 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.
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))),"")