La clasificación de datos incorporada en Excel es asombrosa, pero no es dinámica. Si ordena los datos y luego les agrega datos, deberá ordenarlos nuevamente.
Ordenar datos en orden alfabético
En esta publicación, le mostraré varias formas de ordenar datos en orden alfabético usando fórmulas. Esto significa que puede agregar datos y automáticamente los clasificará por usted.
Cuando los datos son todo texto sin duplicados
Suponga que tiene los datos que se muestran a continuación:
En este ejemplo, todos los datos están en formato de texto (sin números, espacios en blanco o duplicados). Para ordenar esto, usaré una columna de ayuda. En la columna junto a los datos, use la siguiente fórmula CONTAR.SI:
= CONTAR.SI ($ A $ 2: $ A $ 9, "<=" y A2)
Esta fórmula compara un valor de texto con todos los demás valores de texto y devuelve su rango relativo. Por ejemplo, en la celda B2, devuelve 8, ya que hay 8 valores de texto que son inferiores o iguales al texto "EE. UU." (Orden alfabético).
Ahora, para ordenar los valores, use la siguiente combinación de funciones ÍNDICE, COINCIDIR y FILAS:
= ÍNDICE ($ A $ 2: $ A $ 9, COINCIDIR (FILAS ($ B $ 2: B2), $ B $ 2: $ B $ 9,0))
Esta fórmula simplemente extrae los nombres en orden alfabético. En la primera celda (C2), busca el nombre del país que tiene el número más bajo (Australia tiene 1). En la segunda celda, devuelve Canadá (que tiene el número 2) y así sucesivamente …
¿Alérgico a las columnas auxiliares?
Aquí hay una fórmula que hará lo mismo sin la columna auxiliar.
= ÍNDICE ($ A $ 2: $ A $ 9, COINCIDIR (FILAS ($ A $ 2: A2), CONTAR.SI ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), 0))
Esta es una fórmula de matriz, así que use Control + Mayús + Entrar en lugar de Enter.
Lo dejo para que lo decodifique.
Pruébelo usted mismo … Descargar archivo de ejemplo
Esta fórmula funciona bien si tiene valores de texto o alfanuméricos.
Pero falla estrepitosamente si:
- Tiene duplicados en los datos (intente ponernos dos veces).
- Hay espacios en blanco en los datos.
- Tiene una combinación de números y texto (intente poner 123 en una de las celdas).
Cuando los datos son una combinación de números, texto, duplicados y espacios en blanco
Ahora bien, este es un poco complicado. Usaré 4 columnas auxiliares para mostrarte cómo funciona (y luego te daré una fórmula enorme que lo hará sin las columnas auxiliares). Suponga que tiene los datos que se muestran a continuación:
Puede ver que hay valores duplicados, espacios en blanco y números. Por lo tanto, usaré columnas auxiliares para abordar cada uno de estos problemas.
Columna auxiliar 1
Ingrese la siguiente fórmula COUNTIF en la columna auxiliar 1
= CONTAR.SI ($ A $ 2: $ A $ 9, "<=" y A2)
Esta fórmula hace lo siguiente:
- Devuelve 0 para espacios en blanco.
- En el caso de duplicados, devuelve el mismo número.
- El texto y los números se procesan en paralelo y esta fórmula devuelve el mismo número para el texto y el número (por ejemplo, 123 e India obtienen 1).
Columna auxiliar 2
Ingrese la siguiente función IS en la columna auxiliar 2:
= - ISNUMBER (A2)
Columna auxiliar 3
Ingrese la siguiente fórmula en la columna auxiliar 3:
= - ESBLANCO (A2)
Columna auxiliar 4
Ingrese la siguiente fórmula en la columna auxiliar 4
= SI (ESNUMERO (A2), B2, SI (ESBLANCO (A2), B2, B2 + $ C $ 10)) + $ D $ 10
La idea de esta fórmula es separar espacios en blanco, números y valores de texto.
- Si la celda está en blanco, devuelve el valor en la celda B2 (que siempre sería 0) y agrega el valor en la celda D10. En pocas palabras, devolverá el número total de celdas en blanco en los datos.
- Si la celda es un valor numérico, devolverá el rango comparativo y sumará el número total de espacios en blanco. Por ejemplo, para 123 devuelve 2 (1 es el rango de 123 en los datos y hay 1 celda en blanco)
- Si es texto, devuelve el rango comparativo y suma el número total de valores numéricos y espacios en blanco. Por ejemplo, para la India, agrega el rango comparativo del texto en el texto (que es 1) y agrega el número de celdas en blanco y el número de valores numéricos.
Resultado final: datos ordenados
Ahora usaremos estas columnas auxiliares para obtener la lista ordenada. Aquí está la fórmula:
= SI.ERROR (ÍNDICE ($ A $ 2: $ A $ 9, COINCIDIR (PEQUEÑO ($ E $ 2: $ E $ 9, FILAS ($ F $ 2: F2) + $ D $ 10), $ E $ 2: $ E $ 9,0)) , "")
Este método de clasificación ahora se vuelve infalible. Le he mostrado el método para 8 elementos, pero puede extenderlo a tantos elementos como desee.
Pruébelo usted mismo … Descargar archivo de ejemplo
Una fórmula para ordenarlo todo (sin columnas auxiliares)
Si puede manejar fórmulas extremas, aquí hay una fórmula todo en uno que ordenará los datos en orden alfabético (sin ninguna columna auxiliar).
Aquí está la fórmula:
= SI.ERROR (ÍNDICE ($ A $ 2: $ A $ 9, COINCIDIR (PEQUEÑO (NO ($ A $ 2: $ A $ 9 = "") * SI (ESNÚMERO ($ A $ 2: $ A $ 9), CONTARADO ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9) + SUM (- ISNUMBER ($ A $ 2: $ A $ 9))), FILAS ($ A $ 2: A2) + SUMA (- ESBLANCO ($ A $ 2: $ A $ 9))), NO ($ A $ 2: $ A $ 9 = "") * SI (ISNUMBER ($ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9) + SUM (- ISNUMBER ($ A $ 2: $ A $ 9))), 0)), "")
Ingrese esta fórmula en una celda y arrástrela hacia abajo para obtener la lista ordenada. Además, dado que esta es una fórmula de matriz, use Control + Mayús + Entrar en lugar de Enter.
Esta fórmula tiene una utilidad en el mundo real. ¿Qué piensas? Me encantaría aprender de ti. ¡Deja tus huellas en la sección de comentarios!
