Anterior
Índice
Saltar al contenido
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:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Cliente | Base Imponible | IVA | Total |
| 2 | Empresa 1 | 303,88 | ||
| 3 | Empresa 2 | 347,90 | ||
| 4 | Empresa 3 | 258,44 | ||
| 5 | Empresa 4 | 286,84 | ||
| 6 | Empresa 5 | 332,28 | ||
| 7 | Empresa 6 | 648,50 | ||
| 8 | Empresa 7 | 248,50 | ||
| 9 | Total Ventas: |
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.
INSERTAR FÓRMULAS
2.1. Ir a: Celda C2Escribir: =B2*18/100. Multiplicamos la Base Imponible por el 18% del IVA, mostrando el valor de 48,62.
2.2. Ir a: Celda D2Escribir: =B2+C2. Sumamos a la Base Imponible el IVA, mostrando el valor de 352,50.
2.3. Ir a: Celda D9Escribir: =SUMA(D2:D8). Sumamos los valores totales de cada cliente.
PEGAR FÓRMULAS
2.4. IVA. Ir a: Celda C2 Clic sobre ella. La seleccionamos para copiarla.
2.5. Ir a: Cinta de opciones Ficha: InicioGrupo: PortapapelesCopiar 
; O botón derecho del ratón/Tecla contextualCopiar; O Atajo de teclado Control+C.
2.6. Ir a: C3 Clic sobre ella y mantener pulsadoArrastrar hasta C8Soltar el Clic. Rango donde pegamos la fórmula.
2.7. Ir a: Cinta de opciones Ficha: InicioGrupo: PortapapelesPegar 
; O botón derecho del ratón O Atajo de teclado Control+V. Insertará en el rango el valor del IVA.
2.8. TOTAL. Ir a: Celda D2 Clic sobre ella. La seleccionamos para copiarla.
2.9. Ir a: Cinta de opcionesFicha: InicioGrupo: PortapapelesCopiar ; O botón derecho del ratón/Tecla contextualCopiar; O Atajo de teclado Control+C.
2.10. Ir a: D3 Clic sobre ella y mantener pulsadoArrastrar hasta D8Soltar el Clic. Rango donde pegamos la fórmula.
2.11. Ir a: Cinta de opcionesFicha: InicioGrupo: PortapapelesPegar ; O botón derecho del ratón O Atajo de teclado Control+V. Insertará en el rango el valor de total.
2.12. Verificar como en D9 se muestra el total de las ventas con IVA incluido (2.863,08 €).
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 €).
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:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Año | Base Imponible | Tendencia | |
| 2 | 1 | 303.000,88 | ||
| 3 | 2 | 347.000,90 | ||
| 4 | 3 | 258.000,44 | ||
| 5 | 4 | 286.000,84 | ||
| 6 | 5 | 332.000,28 |
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.
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
CON UN RESULTADO
5.1.1. Ir a: Celda D10Clic sobre ella. Celda que contiene la fórmula matricial.
5.1.2. Ir a: Barra de FórmulasClic en su interior. Se ocultara la llave de apertura y cierre en la fórmula.
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.
CON VARIOS RESULTADOS
5.2.1. Seleccionar: El Rango D2:D6. Celdas que contienen la fórmula matricial.
5.2.2. Ir a: Barra de FórmulasClic en su interior. Se ocultara la llave de apertura y cierre en la fórmula.
5.2.3. Modificar la fórmula matricial como deseamos.
5.2.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.
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.
ELIMINAR FÓRMULAS MATRICIALES
Podemos eliminar una fórmula matricial según sea para un resultado o para varios resultados.
PEGAR FÓRMULAS
Para eliminar una fórmula matricial con un único resultado, deberemos:
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.
CON VARIOS RESULTADOS
Para eliminar una fórmula matricial con varios resultados, deberemos:
7.2. Seleccionar el rango que incluye la fórmula matricial Pulsar la tecla Suprimir. Borrará la fórmula matricial y los valores 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.
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:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Ventas | Sin Repetidos | Con Repetidos | |
| 2 | Madrid | |||
| 3 | Sevilla | |||
| 4 | Madrid | |||
| 5 | León | |||
| 6 | Toledo | |||
| 7 | Toledo | |||
| 8 | Cuenca | |||
| 9 | Oviedo | |||
| 10 | Oviedo |
SIN REPETIDOS
8.2. Seleccionar el rango: C2:C10. Rango donde insertar la fórmula matricial para obtener las ventas sin repetidos.
8.3. INSERTAR LA FUNCIÓN ANIDADA. Escribir: =SIERROR(INDICE(A2:A10;K.ESIMO.MAYOR((CONTAR.SI(A2:A10;A2:A10)=1)*FILA()-1;FILA(INDIRECTO("1:"&FILAS(A2:A10)))));"") No pulsar la tecla Intro ni hacer clic con el ratón en otra celda ya que no lo aplicaría la fórmula matricial.
8.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.
8.5. Verificar como se muestran los valores no repetidos.
REPETIDOS
8.6. Seleccionar el rango: D2:D10. Rango donde insertar la fórmula matricial para obtener las ventas sin repetidos.
8.7. INSERTAR LA FUNCIÓN ANIDADA. Escribir: =SIERROR(INDICE(A2:A10;K.ESIMO.MAYOR((CONTAR.SI(A2:A10;A2:A10)<>1)*FILA()-1;FILA(INDIRECTO("1:"&FILAS(A2:A10)))));"") No pulsar la tecla Intro ni hacer clic con el ratón en otra celda ya que no lo aplicaría la fórmula matricial.
8.8. 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.
8.9. Verificar como se muestran los valores repetidos.
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.
INSERTAR FÓRMULAS
9.1. SERIE DE DÍAS. Ir a: Celda B4 Escribir: Lun. Como el primer día de la semana.
9.2. Ir a: Celda B4 Situar el cursor del ratón sobre el punto inferior derecho / Clic y mantener pulsado / Arrastrar hasta la celda H4. Insertará la serie de los días de la semana en formato corto.
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.
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.