¿Cómo clasificar números fácilmente en Excel omitiendo las celdas vacías?
Al trabajar con datos en Excel, es habitual encontrarse con listas que contienen celdas vacías. Si utiliza funciones estándar de clasificación de Excel como RANGO o RANGO.EQ en dichas listas, las celdas vacías suelen generar errores o resultados no deseados en la clasificación. Esto complica la interpretación de sus datos, especialmente si desea conservar las celdas vacías sin mostrar valores de error ni rangos arbitrarios. Clasificar eficazmente los números omitiendo automáticamente las celdas vacías mejora la claridad y usabilidad de sus resultados, haciendo que su hoja de cálculo luzca más profesional y sea más fácil de leer.
En este artículo encontrará instrucciones paso a paso para utilizar fórmulas y macros de VBA y llevar a cabo esta tarea. Las soluciones ampliadas incluyen además explicaciones de los parámetros, consejos prácticos y sugerencias para resolver problemas comunes.
- Clasificar valores omitiendo celdas vacías en orden ascendente mediante fórmulas
- Clasificar valores omitiendo celdas vacías en orden descendente mediante una fórmula
- Clasificar valores omitiendo celdas vacías mediante VBA
Clasificar valores omitiendo celdas vacías en orden ascendente mediante fórmulas
En situaciones en las que necesite asignar clasificaciones en orden ascendente pero desee ignorar las celdas vacías, un enfoque habitual consiste en utilizar varias columnas auxiliares y lógica de fórmulas para garantizar que los valores vacíos queden excluidos de la clasificación.
Escenario aplicable: Utilice este método cuando quiera generar clasificaciones incrementales (de menor a mayor) manteniendo la posición de las celdas vacías, especialmente en rangos continuos donde las entradas faltantes no deben afectar la numeración de los rangos.
Para realizar una clasificación ascendente omitiendo celdas vacías, siga estos pasos, que implican dos columnas auxiliares para construir el resultado:
1. Seleccione una celda vacía adyacente a sus valores; por ejemplo, la celda B2 si su lista comienza en A2, e introduzca la siguiente fórmula:
=IF(ISBLANK($A2),"",VALUE($A2&"."&(ROW()-ROW($B$2))))La fórmula devuelve una celda vacía si A2 está vacía; de lo contrario, genera números decimales a partir del valor de A2, añadiendo .0, .1, .2, .3, etc., a medida que arrastra el controlador de relleno hacia abajo para copiar la fórmula en todas las filas con datos.
Explicación de parámetros y consejos:
- $A2: La primera celda que se va a ordenar. Ajústela si su lista comienza en una fila diferente.
- $B$2: La celda en la que introduce esta fórmula. ¡Preste atención a las referencias absolutas (por ejemplo, $A2 y $B$2) para asegurarse de que la fórmula funcione correctamente al rellenar hacia abajo!
2. En la columna siguiente, por ejemplo C2, introduce esta fórmula para generar una lista ordenada de valores auxiliares:
=SMALL($B$2:$B$8,ROW()-ROW($C$1))Esta fórmula extrae secuencialmente los valores más pequeños, los siguientes más pequeños y los posteriores del rango B2:B8 (nota: cámbielo si sus datos se extienden más allá) al copiar la fórmula hacia abajo.
Explicación de parámetros:
- $B$2:$B$8: El rango en el que se aplica la fórmula auxiliar anterior (la primera).
- $C$1: celda inmediatamente superior a aquella en la que introduce la fórmula; este desplazamiento controla el orden de clasificación.
3. En la celda D2, introduce la siguiente fórmula para asignar rangos dejando intactas las celdas vacías:
=IFERROR(MATCH($B2,$C$2:$C$8,0),"")Esta fórmula busca una coincidencia del valor en B2 dentro de los resultados ordenados en C2:C8. Si encuentra coincidencia, muestra el rango; si no (por ejemplo, en celdas vacías), no muestra nada, manteniendo así un aspecto limpio para las celdas en blanco. Arrastre el controlador de relleno hacia abajo para aplicarla a todas las filas pertinentes.
Parámetros:
- $B2: La celda que contiene el valor auxiliar para la clasificación.
- $C$2:$C$8: El rango de valores auxiliares ya ordenados.
Precauciones: Si añade o elimina datos, recuerde actualizar todos los rangos en cada fórmula para ajustarlos al nuevo tamaño de sus datos. Para listas muy extensas, considere utilizar rangos dinámicos o tablas de Excel y así reducir los ajustes manuales de rango.
Resolución de problemas: Si faltan rangos o están mal alineados, asegúrese de que todos los rangos de las fórmulas auxiliares coincidan correctamente. Una mala alineación entre columnas provocará clasificaciones incorrectas o errores no deseados.
Clasificar valores omitiendo celdas vacías en orden descendente mediante una fórmula
Cuando desee asignar rangos en orden descendente (el valor más alto recibe el rango 1), existe un método más rápido que emplea una sola fórmula. Este enfoque resulta especialmente útil para puntuaciones de exámenes, objetivos de ventas y conjuntos de datos similares, donde las celdas vacías indican información ausente o no disponible, y no desea que ocupen posiciones en el ranking ni muestren errores.
Seleccione una celda en la misma fila que la primera entrada de datos, donde desee que aparezca el resultado, e introduzca:
=IF(ISNA(RANK(A2,A$2:A$8)),"",RANK(A2,A$2:A$8))Tras introducir la fórmula, use el controlador de relleno para copiarla hacia abajo junto a sus datos. Esta fórmula comprueba si la función RANGOdevuelve un error (por ejemplo, cuando)A2 está vacía); en ese caso, deja el resultado en blanco en lugar de mostrar “#N/A”. Si la celda contiene un valor válido, muestra el rango correspondiente.
Parámetros:
- A2: La celda que se va a ordenar (ajuste según su rango de datos).
- A$2:A$8: El rango completo de sus datos (utilice una referencia absoluta para poder copiarla).
Recordatorios sobre errores: Si sigue viendo errores “#N/A”, asegúrese de que las referencias de la fórmula coincidan con su rango de datos deseado y de que no haya valores no numéricos en las celdas que se están ordenando.
Clasificar valores omitiendo celdas vacías mediante VBA
Para usuarios familiarizados con macros que deseen automatizar la clasificación de un rango con celdas vacías —ya sea en orden ascendente o descendente—, una macro personalizada de VBA puede simplificar enormemente el proceso al eliminar la necesidad de múltiples columnas auxiliares y el mantenimiento continuo de fórmulas.
Cómo usarlo:
1. Vaya a la pestaña Programador y haga clic en Visual Basic para abrir el editor de Microsoft Visual Basic para Aplicaciones. Si la pestaña Programador no es visible, consulte esta guía: Mostrar la pestaña Programador en Excel.
2. En la nueva ventana de Microsoft Visual Basic para Aplicaciones, haga clic en Insertar > Módulo y pegue cualquiera de los siguientes códigos en la ventana del módulo:
- Para realizar una clasificación ascendente omitiendo celdas vacías:
Sub RankSkipBlank_Ascending() Dim WorkRng As Range Dim Cell As Range Dim NumArr() As Double Dim Ws As Worksheet Dim OutputCell As Range Dim i As Long, j As Long On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Please select the range to rank", xTitleId, WorkRng.Address, Type:=8) Set Ws = WorkRng.Worksheet Set OutputCell = Application.InputBox("Please select the first cell to output the ascending ranking", xTitleId, Type:=8) If OutputCell Is Nothing Then Exit Sub j = 0 ReDim NumArr(1 To WorkRng.Rows.Count) For Each Cell In WorkRng If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then j = j + 1 NumArr(j) = Cell.Value End If Next Cell Dim temp As Double Dim k As Long For i = 1 To j - 1 For k = i + 1 To j If NumArr(i) > NumArr(k) Then ' ← CHANGE HERE temp = NumArr(i) NumArr(i) = NumArr(k) NumArr(k) = temp End If Next k Next i Dim RankArr() As Double ReDim RankArr(1 To j) For i = 1 To j RankArr(i) = NumArr(i) Next i Dim RankValue As Long Dim r As Long: r = 0 For Each Cell In WorkRng r = r + 1 If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then RankValue = 0 For k = 1 To j If Cell.Value = RankArr(k) Then RankValue = k ' 1 = smallest Exit For End If Next k OutputCell.Offset(r - 1, 0).Value = RankValue Else OutputCell.Offset(r - 1, 0).Value = "" End If Next Cell End Sub - Para realizar una clasificación descendente omitiendo celdas vacías:
Sub RankSkipBlank_Descending() Dim WorkRng As Range Dim Cell As Range Dim NumArr() As Double Dim Ws As Worksheet Dim OutputCell As Range Dim i As Long, j As Long On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Please select the range to rank", xTitleId, WorkRng.Address, Type:=8) Set Ws = WorkRng.Worksheet Set OutputCell = Application.InputBox("Please select the first cell to output the descending ranking", xTitleId, Type:=8) If OutputCell Is Nothing Then Exit Sub j = 0 ReDim NumArr(1 To WorkRng.Rows.Count) For Each Cell In WorkRng If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then j = j + 1 NumArr(j) = Cell.Value End If Next Cell Dim temp As Double Dim k As Long For i = 1 To j - 1 For k = i + 1 To j If NumArr(i) < NumArr(k) Then temp = NumArr(i) NumArr(i) = NumArr(k) NumArr(k) = temp End If Next k Next i Dim RankArr() As Double ReDim RankArr(1 To j) For i = 1 To j RankArr(i) = NumArr(i) Next i Dim RankValue As Long Dim r As Long: r = 0 For Each Cell In WorkRng r = r + 1 If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then RankValue = 0 For k = 1 To j If Cell.Value = RankArr(k) Then RankValue = k Exit For End If Next k OutputCell.Offset(r - 1, 0).Value = RankValue Else OutputCell.Offset(r - 1, 0).Value = "" End If Next Cell End Sub
3. Pulse F5 para ejecutar la macro. Aparecerá un cuadro de diálogo que le pedirá que seleccione el rango que desea ordenar. A continuación, otro cuadro de diálogo le preguntará por la primera celda en la que deben colocarse los resultados de la ordenación. La macro generará entonces los rangos a partir de la celda seleccionada, y cualquier celda vacía del rango de origen se dejará en blanco.
Consejos:
- Si no ocurre nada, asegúrese de que las macros están habilitadas y de que dispone de permiso para ejecutar código en su libro.
- La ejecución de una macro de VBA no se puede deshacer. Asegúrese de hacer una copia de seguridad o respaldar sus datos antes de ejecutarla.
Las mejores herramientas de productividad para Office
Potencie sus habilidades en Excel con Kutools para Excel y experimente una eficiencia como nunca antes.Kutools para Excel ofrece más de 300 funciones avanzadas para aumentar su productividad y Ahorrar tiempo.Haga clic aquí para obtener la función que más necesita...
Office Tab aporta una interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil
- Active la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abra y cree varios documentos en nuevas pestañas dentro de la misma ventana, en lugar de hacerlo en ventanas separadas.
- ¡Aumente su productividad en un 50 % y elimine cientos de clics del ratón cada día!
Todos los complementos de Kutools en un solo instalador.
Kutools for Office es la suite que incluye complementos para Excel, Word, Outlook y PowerPoint, además de Office Tab Pro, ideal para equipos que trabajan en distintas aplicaciones de Office.
- Suite integral— complementos para Excel, Word, Outlook y PowerPoint + Office Tab Pro
- Un instalador, una licencia— configuración en minutos (compatible con MSI)
- Rendimiento mejorado en conjunto— productividad optimizada en todas las aplicaciones de Office
- Prueba gratuita de 30 días con todas las funciones— sin registro ni tarjeta de crédito
- La mejor relación calidad-precio— ahorre frente a la compra individual de complementos