FÓRMULAS MATRICIALES

En ocasiones, nos puede interesar realizar dos operaciones en una única celda, por ejemplo multiplicar varias celdas y luego sumar su resultado, también necesitamos aplicar una serie de transformaciones a un rango para sumar su resultado, en esos casos podemos recurrir a la las fórmulas matriciales.

La característica de las fórmulas matriciales es que nos permiten analizar matrices de datos aplicando filtros complejos por filas o columnas y las podemos utilizar para realizar dos tipos de cosas:

• Ejecutar varias operaciones y devolver un único valor en la celda donde se la introduce.
• Ejecutar varias operaciones y devolver múltiples valores en distintas celdas.

Admiten otros tipos de funciones como promedio, tendencia, mediana, mínimo, máximo, contar, k ésimo mayor o menor, percentil, etc.

Trabajando con Fórmulas Matriciales (Paso a paso)

Deseamos conocer como trabajar con fórmulas matriciales en Excel. Para ello, sobre las ventas de nuestra empresa, deseamos conocer el precio total con IVA incluido por lo que lo calcularemos de forma habitual y mediante fórmula matricial.

ABRIR UN NUEVO LIBRO DE EXCEL

1.1. Descargar hoja de cálculo con los datos y descomprimir el archivo .zip, o también,

1.2. Ir a: Cinta de opciones Ficha: Archivo Nuevo. Mostrará el cuadro de diálogo de archivo nuevo.

1.2.1. Ir a: Plantillas disponiblesDoble clic en Libro en blanco; o Ir a: Vista previa de Libro en blanco Pulsar sobre CREAR. Mostrará un libro nuevo.

1.2.2. Insertar los datos según se indica:

Subir

MÉTODO 1 (CÁLCULO HABITUAL)

Insertamos las fórmulas del IVA (considerando el 18%) y del total para la suma de la base imponible más el IVA.

Subir

FÓRMULAS MATRICIAL CON UN RESULTADO

De manera más rápida podemos calcular el importe total con IVA incluido.

3.1. Ir a: Celda D10Escribir: =SUMA(B2:B8*1,18). Sumamos los valores de Base Imponible y lo multiplicamos por el tipo de IVA (16%).

3.2. Antes de salir de la edición de fórmula deberemos de aplicar la fórmula matricial (si hemos salido de edición de celda, pulsar F2).

3.3. Pulsar la tecla Control y mantenerla pulsada Pulsar la tecla Mayús. y mantener las 2 teclas pulsadas Pulsar la tecla Enter. Insertará la fórmula entre llaves quedando como: {=SUMA(B2:B8*1,18)}.

3.4. Ir a: Celda D10 y verificar como muestra el cálculo de manera simplificada (2.863,08 €).

Subir

FÓRMULA MATRICIAL CON VARIOS RESULTADOS

Algunas funciones de Excel devuelven matrices de valores o requieren una matriz de datos como argumento. Para calcular varios resultados con una función matricial, debemos de introducir la matriz en un rango de celdas con el mismo número de filas y columnas que los argumentos de la matriz.

Disponemos de la serie de ventas de nuestra empresa de los 5 años anteriores y deseamos conocer la tendencia (valores de la línea recta para los importes), para ello emplearemos la función TENDENCIA.

4.1. Insertar los datos según se indica:

4.2. Seleccionar el rango: D2:D6. Rango donde insertar la fórmula matricial.

4.3. INSERTAR LA FUNCIÓN TENDENCIA. Escribir: =TENDENCIA(B2:B6;A2:A6) No pulsar la tecla Intro ni hacer clic con el ratón en otra celda ya que no lo aplicaría para los diferentes años.

4.4. Pulsar la tecla Control y mantenerla pulsada Pulsar la tecla Mayús. y mantener las 2 teclas pulsadas Pulsar la tecla Enter. Insertará la fórmula entre llaves quedando como: {=TENDENCIA(B2:B6;A2:A6)}.

4.5. Verificar como al introducir la función como fórmula matricial, genera los cinco resultados separados y basados en los cinco años y las ventas de ellos.

Subir

EDITAR FÓRMULAS MATRICIALES

En Excel, las fórmulas que hacen referencia a matrices se encierran entre corchetes { }. Cada argumento matricial tiene el mismo número de filas y de columnas.

Hay que tener en cuenta al trabajar con matrices lo siguiente:

• No se puede cambiar el contenido de las celdas que componen la matriz de datos.
• No se puede eliminar o mover celdas que componen la matriz de datos.
• No se puede insertar nuevas celdas en el rango que compone la matriz de datos

5.1.3. Modificar la fórmula matricial como deseamos.

5.1.4. Pulsar la tecla Control y mantenerla pulsadaPulsar la tecla Mayús. y mantener las 2 teclas pulsadasPulsar la tecla Enter. Insertará la fórmula entre llaves.

Subir

SELECCIONAR UN RANGO DE FÓRMULAS MATRICIALES

Deseamos seleccionar un rango de celdas que contenga una fórmula matricial.

6.1. Clic sobre una celda cualquiera del rango. Celda que contiene la fórmula matricial.

6.2. Ir a: Cinta de opciones: InicioModificarBuscar y seleccionar Ir a...; O también Pulsar la tecla F5. Mostrará el cuadro de diálogo de Ir a.

6.3. Clic Botón Especial. Muestra opciones especiales de ir a.

6.4. Clic sobre Matriz actual. Opción deseada.

6.5. Clic botón Aceptar. Cierra ventana y nos muestra la matriz de datos.

Subir

ELIMINAR FÓRMULAS MATRICIALES

Podemos eliminar una fórmula matricial según sea para un resultado o para varios resultados.

7.1. Seleccionar la celda que incluye la fórmula matricial Pulsar la tecla Suprimir. Borrará la fórmula matricial y el valor del rango.

Nota.- Si deseamos eliminar una fila que forma parte de la matriz, deberemos de eliminar primero la fórmula matricial y después la fila.

Subir

VALORES REPETIDOS Y ÚNICOS

Disponemos de un listado con las provincias donde se han vendido nuestros productos y deseamos conocer las provincias que aparecen repetidas y sin repetir.

8.1. Insertar los datos según se indica:

8.5. Verificar como se muestran los valores no repetidos.

8.9. Verificar como se muestran los valores repetidos.

Subir

CALENDARIO (Fórmula matricial)

Deseamos insertar un nuestra hoja un calendario mensual que nos permite consultar por mes y año, el día del mes. Aplicamos el método de “John Walkenbach” de fórmulas matriciales.

9.3. FORMULA MATRICIAL. Seleccionar el rango: B5:H10. Rango donde insertar los días del calendario.

9.4. Ir a: Barra de fórmula Escribir: =SI(MES(FECHA(AÑO(B3); MES(B3); 1)) <> MES(FECHA(AÑO(B3); MES(B3);1) - (DIASEM(FECHA(AÑO(B3);MES(B3);1))-1) + {-1;0;1;2;3;4;5;6}*7 + {1\2\3\4\5\6\7}); ”"; FECHA(AÑO(B3);MES(B3);1) - (DIASEM(FECHA(AÑO(B3);MES(B3);1))-1) + {-1;0;1;2;3;4;5;6}*7 + {1\2\3\4\5\6\7}) Pulsar la tecla Control y mantenerla pulsada Pulsar la tecla Mayús. y mantener las 2 teclas pulsadas Pulsar la tecla Enter. Insertará la fórmula entre llaves como matricial.

9.5. FORMATO CELDA DÍAS. Seleccionar el rango: B5:H10 Botón derecho del ratón Formato de celdas. Mostrará el cuadro de diálogo de Formato de celdas.

9.5.1. Ir a: Ficha Número Categoría: Personalizada Tipo: Escribir: d. Aplicará formato fecha que muestre sólo el número del día de la fecha.

9.6. FORMATO CONDICIONAL. Seleccionar el rango B5:H10. Rango con formato condicional para aplicar relleno a las celdas con los días del mes.

9.7. Ir a: Ficha Inicio Grupo: Estilos Formato Condicional Nueva regla. Mostrará el cuadro de diálogo de nueva regla de formato.

9.7.1. Ir a: Seleccionar un tipo de regla Clic sobre: Utilice una fórmula que determine las celdas para aplicar formato.

9.7.2. Ir a: Dar formato a los valores donde esta fórmula sea verdadera Escribir: =B5<>“”. Fórmula para aplicar formato en las celdas del mes seleccionado.

9.7.3. Pulsar el botón Formato Ficha: RellenoAplicar el color deseado. Para identificar visualmente las celdas del mes.

9.7.4. Pulsar el botón Aceptar. Cierra la ventana y aplica el formato deseado.

9.7.5. Pulsar el botón Aceptar. Cierra la ventana de nueva regla de formato y aplica el formato condicional.

9.8. MES DE REFERENCIA. Seleccionar el rango B3:H3 Ir a: Ficha Inicio Grupo: Alineación Combinar y centrar. Combinará las celdas del rango.

9.9. Ir a: Celda B3 Escribir: =FECHA.MES(B2;C2). Función que sumara o restará los meses, para la consulta del calendario (al completar el proceso).

9.10. CONTROL BARRA DE DESPLAZAMIENTO. Ir a: Celda B2 Escribir: 01/01/2000. Como fecha de referencia inicial para el calendario.

9.10.1. Para insertar el control necesitamos tener activa en la cinta de opciones, la ficha de programador. Ir a: Ficha Archivo Opciones. Mostrará el cuadro de diálogo de Opciones de Excel.

9.10.2. Ir a: Categoría: Personalizar cinta de opciones Ir a: Ficha principal Activar la casilla: Programador. Activara la ficha en la cinta de opciones.

9.10.3. Pulsar el botón: Aceptar. Cierra ventana e inserta la ficha de programador.

9.11. Ir a: Ficha Programador Grupo: Controles Insertar Controles de formulario Barra de desplazamiento Clic sobre cualquier celda. Se inserta el control en la hoja.

9.12. PROPIEDADES DEL CONTROL. Situar el cursor del ratón sobre el control Botón derecho Formato de Control. Muestra el cuadro de diálogo.

9.13. Ir a: Ficha Control Valor actual= Escribir:159 (para el año 2013) Valor mínimo= Escribir: 0 (para fecha mínima 2000) Valor máximo= Escribir: 400 (para fecha máxima 2033) Incremento= Escribir: 1 Vinular con la celda= Escribir C2 (inserta la referencia del control).

9.14. POSICIÓN CONTROL. Situar el cursor sobre el control botón derecho del ratón Arrastrar para cubrir el rango B2:H2. Ocultando los datos de referencia en B2:C2.

Verificar como al pulsar sobre el control se actualiza el calendario mes a mes aumentando o reduciendo la fecha.

Subir

OTROS EJEMPLOS

• =SUMA((A2:A10="Enero")*(B2:B10="Madrid")*C2:C10). Suma las ventas de Enero y la delegación Madrid. Columna A, mes; columna B, delegación y columna C, venta. Fórmula insertada como matricial.
• =SUMA((A2:A10="Enero")*(B2:B10<>"Madrid")*C2:C10). Suma las ventas de enero y todas las delegaciones excepto Madrid. Columna A, mes; columna B, delegación y columna C, venta. Fórmula insertada como matricial.
• =SUMA((A2:A10="Enero")*(B2:B10="Madrid")). Cuenta las ventas de Enero y las delegaciones de Madrid. Columna A, mes; columna B, delegación y columna C, venta. Fórmula insertada como matricial.
• =SUMA((A2:A10="Enero")*(B2:B10="Madrid")+(B2:B10="Sevilla"). Cuenta las ventas de la delegación de Madrid o Sevilla y el mes enero. Columna A, mes; columna B, delegación y columna C, venta. Fórmula insertada como matricial.
• =SUMA((A2:A10="Enero")*(C2:C10>5000)*(C2:C10)). Suma las ventas de enero cuyo importe de las ventas sea mayor de 5000. Columna A, mes; columna B, delegación y columna C, venta. Fórmula insertada como matricial.
• =SUMA((C2:C10>=300)*(C2:C10<=400)*(C2:C10)). Suma las ventas con importe entre 300 y 400. Columna A, mes; columna B, delegación y columna C, venta. Fórmula insertada como matricial.
• =SUMA(C2:C10>=300)*(C2:C10<=400)). Cuenta las ventas con importe entre 300 y 400. Columna A, mes; columna B, delegación y columna C, venta. Fórmula insertada como matricial.

Subir