☰

Obtener las referencias de las celdas que no están en blanco.

tag-icon • tag-icon microsoft-excel worksheet-function
Obtener las referencias de las celdas que no están en blanco.

Necesito ayuda con una función. En la columna AI tiene una lista de nombres, pero están separados por celdas en blanco. ¿Hay alguna forma de mostrar estos nombres en la columna B pero sin las celdas en blanco?

El resultado debería verse así:

ingrese la descripción de la imagen aquí

Respuesta1

Las siguientes fórmulas matriciales funcionarán incluso si alguno de los nombres es idéntico. Ingréselos B2con Ctrl+ Shift+ Entery luego rellénelos.

Este utiliza un rango fijo basado en sus datos de muestra:

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

Este utiliza un rango dinámico, que se ajusta automáticamente a medida que agrega más nombres a la columna 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)),"")

La misma fórmula dinámica que la anterior en forma ampliada:

=
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 puede ver, esta segunda fórmula es simplemente la primera con todos los A$10reemplazados por INDEX(A:A,MATCH("*",A:A,-1)).

Explicación:

La IF()función es 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, dado que Excel expande automáticamente las constantes en matrices de constantes de longitud coincidentes, se convierte en:

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

que se evalúa como:

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

La SMALL()función entonces se convierte en:

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

y como SMALL()ignora las cadenas, esto equivale a:

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

Tenga en cuenta que los números son los índices de los nombres que no están en blanco. Para B1 SMALL()devoluciones de celda 2, para B2devuelve 4, etc. Para B6y bajar devuelve el #NUM!error. (Es por eso que existe una IFERROR()función. Convierte estos errores en espacios en blanco).

Finalmente, la INDEX()función extrae los nombres usando los índices.

Respuesta2

A continuación se muestra una forma de enumerar valores excluyendo celdas en blanco. Veresta página de ayudapara más detalles.

La fórmula para hacer esto requiere una fila encima de la primera fila de datos. Los títulos cumplen ese propósito en la siguiente tabla.

ingrese la descripción de la imagen aquí

Esta fórmula matricial se ingresa en 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 fórmula matricial, se debe ingresar con CtrlShiftEnter, en lugar de solo Enter. Si se ingresa correctamente, Excel rodeará la fórmula con llaves {} en la barra de fórmulas.

Después de ingresar la fórmula, haga clic en la celda B2 y complete la fórmula.

En caso de que sea necesario enumerar nombres duplicados, esta fórmula matricial solo eliminará los espacios en blanco y no los duplicados:

=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))),"")

información relacionada