Lista Ordenada Personalizada con Fórmula – Excel y Google Sheets
Descargar el Libro de Ejemplo
Este tutorial mostrará cómo ordenar dinámicamente los datos según un orden personalizado en Excel y Google Sheets.
Función ORDENARPOR en Excel 365
La función ORDENARPOR produce una matriz dinámica de datos ordenados.
En este ejemplo, usamos la Función ORDENARPOR para tomar una tabla de datos y producir un array dinámico que la ordena por Equipo siguiendo una Lista Ordenada Personalizada:
=ORDENARPOR(B3:D8;COINCIDIR(B3:B8;F3:F5;0))
Nota: La Función ORDENARPOR se escribe sólo en la celda H3. El resto de la tabla se rellenará automáticamente.
La tabla ordenada es dinámica, lo que significa que si alguno de los valores de Equipo o Nombre de Jugador cambia o se añaden/eliminan registros, la tabla de salida cambiará automáticamente de tamaño y se volverá a ordenar.
Nota: La función ORDENARPOR no está disponible en Excel 2019 o anterior. Lea a continuación las opciones de ordenación disponibles para versiones anteriores de Excel.
Para explicar cómo funciona esta fórmula, vamos a dividirla en pasos:
Nuestra fórmula final es:
=ORDENARPOR(B3:D8;COINCIDIR(B3:B8;F3:F5;0))Primero, definimos la tabla de datos que necesita ser ordenada. Será B3:D8.
Luego, necesitamos definir el orden de clasificación. Utilizaremos la función COINCIDIR para encontrar el orden en que aparece cada nombre de equipo en la lista ordenada personalizada. Esta parte de la fórmula es:
=COINCIDIR(B3:B8;F3:F5;0)Sumando los valores de los rangos de celdas, obtenemos:
COINCIDIR({"Azul";"Rojo";"Amarillo";"Rojo";"Azul";"Amarillo"};{"Rojo";"Azul";"Amarillo"};0)La Función COINCIDIR reemplaza todos los nombres de Equipo con el orden en que aparecen en la Lista Ordenada Personalizada, utilizando una coincidencia de texto exacta:
={2;1;3;1;2;3}Esta matriz de valores se utiliza como orden de clasificación por la función principal ORDENARPOR:
=ORDENARPOR(B3:D8;{2;1;3;1;2;3})La tabla ordenada se produce entonces
Lista Ordenada Personalizada – Pre-Excel 365
Como la Función ORDENARPOR no está disponible para los usuarios de Excel antes de la versión Excel 365, ahora mostraremos cómo realizar la misma tarea utilizando un proceso más manual.
La herramienta de ordenación incorporada en Excel se puede utilizar para ordenar por una Lista de Ordenación Personalizada seleccionando la ‘Lista Personalizada…’ de las opciones de ordenación en la herramienta de ordenación:
Una nueva Lista Personalizada debe añadirse como una lista separada por comas. En este ejemplo, añadimos:‘Amarillo, Azul, Rojo’
La lista personalizada aparecerá en la selección de ordenación
La tabla de datos se ordena por los valores de la lista personalizada
Tenga en cuenta que esta ordenación personalizada se guarda en el libro y está disponible para ser utilizada de nuevo. Si no desea añadir una lista personalizada a Excel, puede utilizar un método con una columna auxiliar.
Lista Personalizada con Columna Auxiliar – Pre-Excel 365
En el siguiente ejemplo, utilizamos la función COINCIDIR en una columna auxiliar para definir un orden personalizado haciendo referencia a una lista personalizada:
=COINCIDIR(B3;$G$3:$G$5;0)
Observe el uso del símbolo $ al hacer referencia a la Lista de Ordenación Personalizada . Lea nuestro artículo sobre Bloqueo de referencias de celdas para obtener más información sobre este tema.
Una vez añadida la columna de ayuda, se puede ejecutar un proceso de ordenación manual utilizando el Asistente de Ordenación:
Esto produce la tabla ordenada siguiendo el Orden Personalizado:
Si se realiza algún cambio en los valores del Equipo o de la Lista de Ordenación Personalizada, será necesario volver a ejecutar el asistente de ordenación para actualizar el orden de la tabla de datos.
Lista Ordenada Personalizada con Fórmula en Google Sheets
La función ORDENARPOR no está disponible en Google Sheets, pero en su lugar se puede utilizar su función SORT, que es más potente que la función SORT de Excel 365. Nos permite utilizar listas de ordenación personalizadas con fórmulas. Nos permite utilizar listas de ordenación personalizadas para ordenar dinámicamente rangos de datos.
En este ejemplo, utilizamos la Función SORT de Google Sheets para tomar una tabla de datos y producir un array dinámico que ordene los valores del Equipo mediante una Lista de Ordenación Personalizada:
=SORT(B3:D8;COINCIDIR(B3:B8;F3:F5;0);VERDADERO)
La Función SORT de Google Sheets ordena los datos por los números de columna especificados o por orden personalizado en los datos de entrada y nos permite establecer si el orden es ascendente o no.
