Fó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:
=COUNTIF(data,"<="&B5)+(COUNT(data)*ISTEXT(B5))
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:
=COUNTIF(data,"<="&B5)
que se explica 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í:
+(COUNT(data)*ISTEXT(B7))
Aquí, usamos la función COUNT para obtener un conteo 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:
=COUNTIF(data,"<"&B5)+(COUNT(data)*ISTEXT(B5))+COUNTIF($B$5:B5,B5)
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 la siguiente fórmula INDICE y COINCIDIR:
=INDEX(data,MATCH(ROWS($E$5:E5),rank,0))
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, consulte 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:
=IFERROR(INDEX(data,MATCH(ROWS($E$5:E5),rank,0)),"")
