Si le preocupa que la eliminación de celdas vacías de una columna pueda dañar sus datos, deje la columna original como está y extraiga las celdas que no estén vacías en otro lugar. Este método es útil cuando está creando una lista personalizada o una lista desplegable de validación de datos y desea asegurarse de que no haya espacios en blanco en ella.
Con la lista de fuentes en A2:A11, ingrese la siguiente fórmula de matriz en C2, presione Ctrl + Mayús + Intro para completarlo correctamente, luego copie la fórmula en varias celdas. El número de celdas en las que copie la fórmula debe ser igual o mayor que el número de elementos de su lista.
=IFERROR(INDEX($A$2:$A$11, SMALL(IF(NOT(ISBLANK($A$2:$A$11)), ROW($A$1:$A$10),""), ROW(A1))),"")
Cómo funciona la fórmula
Delicado a primera vista, en una inspección más cercana, la lógica de la fórmula es fácil de seguir. En lenguaje sencillo, la fórmula en C2 se ve así: Devuelve el primer valor en el rango A2:A11 si esa celda no está vacía. En caso de error, devuelve una cadena vacía («»).
Para los usuarios cuidadosos de Excel que tienen curiosidad acerca de los aspectos prácticos de cada nueva fórmula, aquí está el desglose detallado:
La función ÍNDICE devuelve un valor de $A$2:$A$11 basado en el número de fila especificado (no un número de fila real, un número de fila relativo en el rango). En un escenario más simple, podríamos poner ÍNDICE ($ A $ 2: $ A $ 11, 1) en C2 y nos daría un valor en A2. El problema es que tenemos que ocuparnos de 2 cosas más:
- Asegúrese de que A2 no esté vacío
- Darles la vuelta en 2cuando valor no en blanco en C3, 3calle valor no en blanco en C4 y así sucesivamente.
Ambas tareas son manejadas por la función SMALL(array,k):
SMALL(IF(NOT(ISBLANK($A$2:$A$11)), ROW($A$1:$A$10),""), ROW(A1))
En nuestro caso, matriz el argumento se genera dinámicamente de la siguiente manera:
NOT(ISBLANK($A$2:$A$11))identifica qué celdas en el rango objetivo no están vacías y devuelve VERDADERO para ellas, de lo contrario FALSO. La matriz resultante de VERDADERO y FALSO pasa a la prueba lógica de la función SI.- SI evalúa cada elemento de la matriz VERDADERO/FALSO y devuelve un número correspondiente para VERDADERO, una cadena vacía para FALSO:
IF({TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE}, ROW($A$1:$A$10),"")
ROW($A$1:$A$10) solo se necesita devolver una matriz de números del 1 al 10 (ya que hay 10 celdas en nuestro rango) de la cual el IF puede elegir un número para los valores VERDADEROS.
Como resultado, obtenemos la matriz {1;»»;3;»»;5;6;»»;8;»»;10} y nuestra función PEQUEÑA compleja se convierte en esta simple:
SMALL({1;"";3;"";5;6;"";8;"";10}, ROW(A1))
Como ves, matriz El argumento contiene solo los números de celda no vacíos (nota, estos son relativo las posiciones de los elementos en la matriz, es decir, A2 es el elemento 1, A3 es el elemento 2 y así sucesivamente).
En el k argumento, colocamos ROW(A1) que le dice a la función SMALL que devuelva el número más pequeño de 1. Debido al uso de la referencia de celda relativa, el número de fila aumenta en incrementos de 1 a medida que copia la fórmula. Entonces en C3, k cambiará a ROW(A2) y la fórmula devolverá el número de 2cuando celda que no está vacía y así sucesivamente.
Sin embargo, en realidad no necesitamos los números de celdas no vacías, necesitamos sus valores. Así que seguimos adelante y anidamos la función PEQUEÑA en el núm_fila argumento de ÍNDICE que lo obliga a devolver un valor de la fila correspondiente en el rango.
Como toque final, incluimos la construcción completa en la función IFERROR para reemplazar errores con cadenas vacías. Los errores son inevitables porque no puede saber cuántas celdas que no están en blanco hay en el rango objetivo, así que copie la fórmula en una mayor cantidad de celdas.
Dado lo anterior, podemos construir esta fórmula genérica para extraer valores ignorando los espacios en blanco:
{=SI ERROR(ÍNDICE(rangoBAJO(SI(NO(ESTÁ EN BLANCO(rango)), FILA ($ A $ 1: $ A $ 10), «»), FILA (A1))), «»)}
Donde «rango» es el rango con los datos originales. Tenga en cuenta que ROW ($ A $ 1: $ A $ 10) y ROW (A1) son partes constantes y nunca cambian, independientemente de dónde comiencen los datos y cuántas celdas incluyan.
