• Ir a navegación principal
  • Ir al contenido principal
  • Ir al pie de página

  • Inicio
  • Cursos Inglés
  • Otros Idiomas
  • Cursos Tecnologías
  • Sesiones Webinar
  • Formación para Empresas
  • Nosotros
  • Contacto

EXCEL, OFIMÁTICA, Sin categoría / 22 enero, 2021

FUNCIONES DE MATRICES DINÁMICAS EN EXCEL

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!

Si en el trayecto del rango de derrame hay una celda que contiene información, entonces se mostrará el error #¡DESBORDAMIENTO!. Al borrar dicha celda del rango, la función se mostrará el resultado automáticamente.

Referenciar al Rango de desbordamiento

Es posible hacer referencia a un Rango de desbordamiento, para ello, escribe la primera celda en la que comienza el rango de desbordamiento seguido del carácter almohadilla (#). La referencia al Rango de desbordamiento puede ser utilizada en muchas funciones de Excel y nos evita tener que escribir el rango entero. Por ejemplo, para contar la cantidad de celdas con contenido dentro tomando como referencia el rango F7:F19, se puede hacer referencia al rango de desbordamiento dentro de la función de conteo de la siguiente forma:
Funciones de Matrices Dinámicas

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.
Funciones de Matrices Dinámicas - Ordenar

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.
Funciones de Matrices Dinámicas - OrdenarPor

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.
Funciones de Matrices Dinámicas - Filtrar

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.
Funciones de Matrices Dinámicas - Filtrar

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.
Funciones de Matrices Dinámicas - Filtrar

Archivado en:EXCEL, OFIMÁTICA, Sin categoría Etiquetado con:Función Filtrar, Función Ordenar, Función OrdenarPor, Funciones de Matrices Dinámicas, Rango de desbordamiento

Footer

Learning House

Centro de formación en idiomas y tecnologías

Para Empresas y Particulares

CONTÁCTANOS

640 777 036
www.learning-house.com
info@learning-house.com

Copyright © 2021 - Learning House

Este sitio utiliza cookies propias y de terceros para recopilar información con el objetivo de optimizar la visita a las páginas del sitio. No se utilizarán las cookies para recoger información de carácter personal. Usted puede permitir su uso o rechazarlo, también puede cambiar su configuración siempre que lo desee. Para más información sobre las Cookies Leer más Acepto
Política de Cookies

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necesarias
Siempre activado

Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.

No necesarias

Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.

Learning House
  • CURSOS INGLÉS
  • OTROS IDIOMAS
  • CURSOS TECNOLOGÍAS
  • Sesiones Webinar
  • Formación para Empresas
  • Nosotros
  • Contacto