ORDENAR, ORDENARPOR y FILTRAR – Funciones de Matrices Dinámicas en Excel

ORDENAR, ORDERNAPOR y FILTRAR - Funciones de Matrices Dinámicas en Excel

En un tutorial anterior vimos los conceptos básicos de lo que se viene en Excel a raíz del cambio en el motor de cálculos en Excel. Vimos el tema de las Matrices dinámicas en Excel (Dynamic Array Functions) y los conceptos de Rango de desbordamiento (Spill range) y cómo hacer referencia a dicho rango.

Recordemos que Microsoft ha liberado 7 nuevas funciones de Matrices dinámicas, las cuales actualmente en versión previa a los usuarios registrados en el programa Office Insider:

  • ORDENAR. La función ORDER ordena el contenido de una matriz o rango.
  • ORDENARPOR. La función SORTBY ordena el contenido de un rango o una matriz basándose en los valores de una matriz o rango correspondientes.
  • FILTRAR. La función FILTER le permite filtrar un rango de datos basándose en los criterios que defina.
  • UNICOS. La función UNIQUE devuelve una lista de valores únicos en una lista o un rango.
  • SECUENCIA. La función SEQUENCE le permite generar una lista de números secuenciales en una matriz, como 1, 2, 3, 4.
  • MATRIZALEAT. La función RANDARRAY devuelve una matriz de números aleatorios entre 0 y 1.
  • SIMPLE. La función SINGLE, devuelve un valor único mediante lógica conocida como intersección implícita. SOLO puede devolver un valor, el rango de celda o un error.

En este tutorial veremos el uso de las funciones ORDENAR, ORDENARPOR y FILTRAR.

Ver video Funciones ORDENAR, ORDENARPOR y FILTRO

Suscríbete al canal de EXCELeINFO en YouTube para aprender más de Excel y macros.

Función ORDENAR

La función ORDENAR nos permitirá ordenar un rango o una Tabla en Excel en base a criterios por columnas.

Sintaxis de la función ORDENAR

La función ORDENAR tiene 4 parámetros los cuales se reflejan de la siguiente manera:

=ORDENAR(Matriz, Ordenar_índice, Criterio_ordenación, Por_col)

Matriz. Es el rango o Tabla que deseamos ordenar.

Ordenar_índice. Es el número de columna por el cual se ordenará la matriz.

Criterio_ordenación. Es un número que indicará si el orden es ascendente (1) o descendente (-1). El valor predeterminado es 1.

Por_col. Aquí indicaremos la dirección de ordenación. Si es por columnas elegimos TRUE o FALSE para ordenar por fila.

En el siguiente ejemplo ordenaremos un rango de celdas en base a la columna Zona. Usamos la siguiente fórmula:

=ORDENAR(A11:D36,COINCIDIR(G8,A10:D10,0),1,FALSO)

Usamos la función Ordenar para ordenar el rango.

Figura 1. Usamos la función Ordenar para ordenar el rango.

Ordenar seleccionando dinámicamente la columna

En el ejemplo anterior vimos que usamos la función COINCIDIR para elegir dinámicamente el número de la columna a filtrar. Para seleccionar la columna a filtrar lo haremos usando Validación de datos.

  • Elegimos la celda G8.
  • Nos dirigimos a la pestaña Datos > Validación de datos.
  • En la sección Permitir elegimos Lista e ingresamos =$A$10:$D$10.
  • Aceptar.

Ahora cada vez que elegimos el nombre de la columna a filtrar, el ordenamiento se aplicará dinámicamente.

Función ORDENAR en Excel

Figura 2. Función ORDENAR en Excel.

Función ORDENARPOR

La función ORDENARPOR nos permitirá ordenar un rango o Tabla en base a varias columnas.

Sintaxis de la función ORDENARPOR

La función ORDENARPOR tiene los siguiente parámetros:

=ORDENARPOR(Matriz, Por_matriz1, Orden1, Por_matriz2, Orden2, …)

Matriz. Es el rango o Tabla el cual se ordenará.

Por_matriz1. En la primer columna por la que se ordenará la matriz.

Orden1. Si elegimos 1 se ordenará de manera ascendente, y si elegimos -1 será de manera descendente.

Por_matriz2. Es la columna por la cual se ordenará la matriz.

Orden2. Si elegimos 1 se ordenará de manera ascendente, y si elegimos -1 será de manera descendente.

En el siguiente ejemplo ordenaremos el rango en base a la columna Zona y luego por la columna Cantidad. Usamos la siguiente fórmula:

=ORDENARPOR(A5:D30,A5:A30,1,D5:D30,1)

Elegir la primera columna y segunda columna dinámicamente

Así como en el ejemplo anterior usaremos dos listas de validación par elegir la primer columna y luego la segunda columna.

La lista de validación enlistará los nombres de las columnas a ordenar. Luego usamos la función INDIRECTO para hacer referencia a los valores de cada columna. Primero deberemos crear nombres definidos por cada columna.

  • Elegimos el rango A4:D30.
  • Nos dirigimos a la pestaña Fórmulas > Crear dese la selección.
  • Ahora en el cuadro de nombres vemos los nombres de las columnas.

Cada columna tiene un nombre definido que hace referencia a los valores de cada una.

Figura 2. Cada columna tiene un nombre definido que hace referencia a los valores de cada una.

Para desplegar los nombres de las columnas asignaremos dos listas de validación de datos en las celdas Q1 y Q2.

    • Elegimos la celdas Q1 y Q2.
    • Nos dirigimos a la pestaña Datos > Validación de datos.
    • En la sección Permitir elegimos Lista e ingresamos =$A$10:$D$10.
  • Aceptar.

En la celda P5 ingresamos la siguiente fórmula:

=ORDENARPOR(A5:D30,INDIRECTO(Q1),1,INDIRECTO(Q2),1)

Ordenamos un rango en base a dos columnas.

Figura 3. Ordenamos un rango en base a dos columnas usando la función ORDENARPOR.

Función FILTRAR

La función FILTRAR nos permite filtrar un rango o Tabla en base a un criterio.

Sintaxis de la función FILTRAR

La función FILTRAR tiene 3 parámetros los cuales reflejamos de la siguiente manera:

=FILTRAR(Array, Include, If_empy)

Array. Es el rango o Tabla a filtrar.

Include. Es la columna por la cual se aplicará el filtro y la comparación.

If_empty: Es el valor que se devolverá si el filtro no devuelve ningún valor.

En el siguiente ejemplo aplicaremos un filtro del rango siempre y cuando la columna Zona contenga la palabra “Norte”. Usaremos la siguiente fórmula:

Filtrar un rango con la función FILTRAR.

Figura 4. Filtrar un rango con la función FILTRAR.

Filtrar en base dos criterios

Usaremos el símbolo del asterisco para filtrar el rango cuando la columna Zona contiene los valores de “Sur” y la columna Fruta tiene los valores de “Manzana”. Usamos la fórmula:

=FILTRAR(A12:D37,(A12:A37=G10)*(C12:C37=G9),”No hay valores”)

Filtrar sumando dos criterios

Ahora haremos un filtro cuando la columna Zona es igual a “Sur” y le añadiremos el filtro cuando la columna Fruta es igual a “Manzana”. Usaremos el signo de más (+) para anexas ambos filtros. Usamos la fórmula.

=ORDENAR(FILTRAR(A12:D37,(C12:C37=L9)+(A12:A37=L10),”No hay valores”),1,1)

Es importante anotar que este cambio en el Motor de cálculos en Excel y las nuevas funciones de Matrices dinámicas están en versión previa para un conjunto de usuarios anotados en el Programa Office Insider. El programa de Office Insider es gratis para los usuarios de Office 365 que desean tener acceso a características previas de la aplicaciones de Office.

Descarga el archivo de ejemplo

Descargar el ejemplo ORDENAR, ORDENARPOR y FILTRAR – Funciones de Matrices dinámicas en Excel.zip

Si te gustó este tutorial por favor regístrate en nuestra Lista de correo y Suscríbete a nuestro canal de YouTube para que estés siempre enterado de lo nuevo que publicamos.

You may also like...