Excel

Ordenar texto y números con fórmula

Sort Text Numbers With Formula

Fórmula de Excel: ordenar texto y números con fórmulaFórmula genérica
= COUNTIF (data,'<='&A1)+( COUNT (data)* ISTEXT (A1))
Resumen

Para ordenar dinámicamente los datos con números y texto en orden alfabético, puede usar una fórmula para generar un rango numérico en una columna auxiliar, luego usar INDICE y COINCIDIR para mostrar valores basados ​​en el rango. En el ejemplo que se muestra, la fórmula en C5 es:

redondeo a números enteros en Excel
|_+_|

donde 'datos' es el rango con nombre B5: B13.



Explicación

Esta fórmula primero genera un valor de rango usando una expresión basada en CONTAR.SI:

|_+_|

cual es explicado con más detalle aquí . Si los datos contienen todos los valores de texto o todos los valores numéricos, la clasificación será correcta. Sin embargo, si los datos incluyen tanto texto como números, debemos 'cambiar' el rango de todos los valores de texto para tener en cuenta los valores numéricos. Esto se hace con la segunda parte de la fórmula aquí:

|_+_|

Aquí, usamos la función COUNT para obtener un recuento de valores numéricos en los datos, luego multiplicamos el resultado por el resultado lógico de ISTEXT, que prueba si el valor es texto y devuelve VERDADERO o FALSO. Esto efectivamente cancela el resultado COUNT cuando estamos trabajando con un número en la fila actual.

Manejo de duplicados

Si los datos contienen duplicados, la fórmula se puede modificar como se muestra a continuación para asignar un rango secuencial a los valores que aparecen más de una vez:

|_+_|

Esta versión ajusta la lógica de la función CONTAR.SI inicial y agrega otro CONTAR.SI con una referencia en expansión para incrementar los duplicados.

Mostrar valores ordenados

Para recuperar y mostrar valores ordenados en orden alfabético usando el valor de rango calculado, E5 contiene lo siguiente Fórmula INDICE y MATCH :

|_+_|

donde 'datos' es el rango con nombre B5: B13, y 'rango' es el rango con nombre C5: C13.

Para obtener más información sobre cómo funciona esta fórmula, mira el ejemplo aquí .

Lidiando con espacios en blanco

Las celdas vacías generarán un rango de cero. Suponiendo que desea ignorar las celdas vacías, esto funciona bien porque la fórmula INDICE y COINCIDIR arriba comienza en 1. Sin embargo, verá errores # N / A al final de los valores ordenados, uno por cada celda vacía. Una manera fácil de manejar esto es envolver la fórmula INDICE y COINCIDIR en IFERROR de esta manera:

|_+_|Autor Dave Bruns


^