FUNCIONES DE MATRICES DINÁMICAS EN EXCEL
Microsoft ha incorporado algunas cuestiones imperceptibles a simple vista, pero que son la base para el funcionamiento de nuevas funciones. Un cambio en el Motor de cálculos internos de Excel, ha provocado cambios sobre todo en la manera en la que se utilizan las fórmulas matriciales en Excel.
- Con este nuevo motor de cálculo interno, todas las funciones se tratan de la misma forma. Lo que implica que para las fórmulas matriciales ya no es necesario utilizar la combinación de teclas Control + Shift + Enter.
- Excel ha puesto a disposición de los suscriptores de Microsoft 365, 7 nuevas Funciones de Matrices Dinámicas.
- Aparece un nuevo concepto Intervalo o Rango de Desbordamiento.
- Si una fórmula devuelve un solo valor, este se mostrará en la celda donde se resuelve la función.
- Si la fórmula devuelve más de un resultado respuesta, se usará el Rango de desbordamiento para mostrar todos los resultados, el cual puede ocupar varias filas y/o columnas.
Rango de desbordamiento
En el siguiente ejemplo, al introducir la función =MAYUSC(E7:E19) la función se derrama para mostrar el resultado. Se puede observar como la función se introduce en la primera celda, mientras que, si se selecciona una celda del rango de desbordamiento, se mostrará el contenido de la celda atenuado. Esto es porque en realidad en dicha celda no hay nada. Si se desea modificar algo, hay que hacer en la celda donde se ha introducido originalmente la función.Error de #¡Desbordamiento!
Referenciar al Rango de desbordamiento
NUEVAS FUNCIONES DE MATRICES DINÁMICAS
Las 7 nuevas funciones de Matrices Dinámicas En Excel son:
- ORDENAR. Permite ordenar el contenido de un rango o matriz.
- ORDENARPOR. Permite ordenar el contenido de un rango o matriz, en función de los valores de otro rango o matriz.
- FILTRAR. permite filtrar un rango de datos en función de los criterios que se definan.
- UNICOS. Permite devolver una lista de valores únicos a partir de los valores de una lista o rango. Podríamos decir que reemplaza al Quitar duplicados.
- SECUENCIA. permite generar una lista de números secuenciales en una matriz, como 1, 2, 3, 4.
- MATRIZALEAT. devuelve una matriz de números aleatorios. Se puede especificar el número de filas y columnas para rellenar, valores máximos y mínimos y si se deben devolver números enteros o valores decimales.
FUNCIÓN ORDENAR
La función ORDENAR permite ordenar un rango o tabla en base a una columna, de forma ascendente o descendente, por filas o columnas.
Sintaxis:
=ORDENAR(matriz;[índice_ordenacion];[criterio_ordenación];[por_col])
Donde:
- matriz: es obligatorio. Es el rango o tabla que se desea ordenar.
- Índice_ordenación: es opcional. Es la columna en base a la cual se ordenará la matriz o tabla. Si no se especifica se toma la primera columna.
- Criterio_ordenación: Permite establecer el orden en el cual se ordenará: 1- Ascendente (valor por defecto) / -1: Descendente.
- por_col: permite determinar si se ordenará en función de las filas o de las columnas. Falso (valor por defecto) → Filas / Verdadero → Columnas.
Ejemplo
En el siguiente ejemplo, se desea ordenar el rango de datos A7:F78, en función de la columna IMPORTE, de forma ASCENDENTE.
La fórmula es:
=ORDENAR(A7:F78;6;1)
Donde:
- A7:F78: es el rango para ordenar
- 6: hace referencia a la columna de IMPORTE, columna por la cual se desea ordenar y que ocupa dicha posición dentro del rango A7:F78.
- 1: indica que el orden será Ascendente.
FUNCIÓN ORDENARPOR
La función ORDENARPOR permite ordenar un rango o tabla en base a una o varias columnas de forma ascendente o descendente.
Sintaxis:
=ORDENARPOR(matriz;por_col;[criterio_ordenación];[por_col2;criterio_ordenación2]…)
Donde:
- matriz: es obligatorio. Es el rango o tabla que se desea ordenar.
- por_col: Es la columna en base a la cual se ordenará la matriz o tabla.
- criterio_ordenación: Permite establecer el orden en el cual se ordenará: 1- Ascendente (valor por defecto) / -1: Descendente.
- por_col2; criterio_ordenación_2: son opcionales. Se establecen de a pares, columna-criterio, para ordenar por más de una columna a la vez.
Ejemplo
Siguiendo con el ejemplo anterior, ahora se desea ordenar el rango de datos A7:F78, en función de la columna ZONA, de forma ASCENDENTE y por IMPORTE de forma DESCENDENTE.
La fórmula es:
=ORDENARPOR(A7:F78; D7:D78;1;F7:F78;-1)
Donde:
- A7:F78: es el rango para ordenar
- D7:D78: hace referencia a la columna de ZONA, columna por la cual se desea ordenar el rango.
- 1: indica que el orden será Ascendente.
- F7:F78: hace referencia a la columna de IMPORTE, columna por la cual se desea ordenar dentro de cada zona.
- -1: indica que el orden será Descendente.
FUNCIÓN FILTRAR
La función FILTRAR permite filtrar un rango de datos en función de los criterios definidos.
Sintaxis:
=FILTRAR(matriz;rango_criterio;[valor_si_vacío])
Donde:
- matriz: es obligatorio. Es el rango o tabla que se desea filtrar.
- rango_criterio: es obligatorio. La columna y el criterio de selección que se aplicará sobre esa columna.
- Valor_si_vacío: Permite establecer el valor a devolver en caso de que el resultado de la función Filtrar, sea vacío. Si este argumento no se especifica y Filtrar no arroja ningún resultado, entonces se mostrar el error #CALC
Ejemplo 1
Siguiendo con el ejemplo anterior, ahora se desea filtrar el rango de datos A7:F78, para mostrar solo los registros de la ZONA NORTE.
La fórmula es:
=FILTRAR(A7:F78;D7:D78=”Norte”;”No hay resultados”)
Donde:
- A7:F78: es el rango para ordenar
- D7:D78: hace referencia a la columna de ZONA, columna por la cual se desea filtrar el rango.
- “Norte”: es el criterio que deben cumplir las celdas para ser mostradas.
- “No hay resultado”: lo que devuelve la función Filtrar en el caso de que el resultado sea vacío.
Ejemplo 2
Siguiendo con el ejemplo anterior, ahora se desea filtrar el rango de datos A7:F78, para mostrar solo los registros de la ZONA NORTE y cuyo VENDEDOR sea Antonio. Para ello, se debe unir los criterios mediante el operador *.
La fórmula es:
=FILTRAR(A7:F78;(D7:D78=”Norte”)*(C7:C78=”Antonio”);”No hay resultados”)
Donde:
- A7:F78: es el rango para ordenar
- D7:D78: hace referencia a la columna de ZONA, columna por la cual se desea filtrar el rango.
- “Norte”: es el criterio que deben cumplir las celdas para ser mostradas.
- C7:C78: hace referencia a la columna de VENDEDOR, columna por la cual se desea filtrar el rango.
- “Antonio”: es el criterio que deben cumplir las celdas del rango Vendedor para ser mostradas.
- “No hay resultado”: lo que devuelve la función Filtrar en el caso de que el resultado sea vacío.
Ejemplo 3
Si, por el contrario, lo que se desea obtener son las filas donde se cumpla que la ZONA es NORTE o el VENDEDOR es Antonio, entonces los criterios se deben unir mediante el operador +
La fórmula es:
=FILTRAR(A7:F78;(D7:D78=”Norte”)+(C7:C78=”Antonio”);”No hay resultados”)
Donde:
- A7:F78: es el rango para ordenar
- D7:D78: hace referencia a la columna de ZONA, columna por la cual se desea filtrar el rango.
- “Norte”: es el criterio que deben cumplir las celdas para ser mostradas.
- C7:C78: hace referencia a la columna de VENDEDOR, columna por la cual se desea filtrar el rango.
- “Antonio”: es el criterio que deben cumplir las celdas del rango Vendedor para ser mostradas.
- “No hay resultado”: lo que devuelve la función Filtrar en el caso de que el resultado sea vacío.
