Trucos y Cursos de Excel
  • Inicio
  • Evolución de Excel
    • Origen de la hoja de cálculo
    • Antecedentes Microsoft Excel
    • Excel 1.0
    • Excel 2.0
      • Excel 2.1.
    • Excel 3.0
      • Excel 3.1
    • Excel 4.0
    • Excel 5.0
    • Excel 7.0 (Excel 95)
    • Excel 8.0 (Excel 97)
    • Excel 9.0 (Excel 2000)
    • Excel 10.0 (Excel 2002 / XP)
    • Excel 11.0 (Excel 2003)
    • Excel 12.0 (Excel 2007)
    • Excel 14.0 (Excel 2010)
    • Excel 15.0 (Excel 2013)
    • Excel 16.0 (Excel 2016)
  • Blog
  • Temarios
    • Excel Básico
    • Excel Avanzado
    • Excel Completo
    • Funciones Excel
    • Excel Macros y VBA
    • PowerPivot para Excel
    • Power View para Excel
    • Power Query para Excel
    • Power Map para Excel
    • Business Intelligence y Excel
    • Excel Financiero
    • Letras, Bonos y Obligaciones
    • Excel Bolsa
    • Excel Derivados Financieros
  • Cursos
  • Servicios
  • Demos
  • Contactar

 

Demostración curso: "Excel Avanzado" 2007, 2010, 2013, 2016 y 365

AnteriorÍndiceSiguiente
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:

 ABCD
1ClienteBase ImponibleIVATotal
2Empresa 1303,88  
3Empresa 2347,90  
4Empresa 3258,44  
5Empresa 4286,84  
6Empresa 5332,28  
7Empresa 6648,50  
8Empresa 7248,50  
9  Total Ventas: 
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.

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 Botón copiar.; 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 Icono botón pegar.; 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 Botón copiar.; 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 Icono botón pegar.; 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 €).

Hoja Excel, celda D9 con el resultado del total de ventas 2.863,08 (=SUMA(D2:D8)).
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).

Barra de fórmulas con fórmula matricial, {=SUMA(B2:B8*1,18)}

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 €).

Hoja Excel, celda D10 con fórmula matricial, {=SUMA(B2:B8*1,8)}(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:

 ABCD
1AñoBase Imponible Tendencia
21303.000,88  
32347.000,90  
43258.000,44  
54286.000,84  
65332.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)}.

Hoja Excel con rango D2:D6 con los resultados de la fórmula matricial TENDENCIA (en cada fila).

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

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.

Hoja Excel con Formula matricial editada en la barra de fórmulas (sin llaves)

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.

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 Botón buscar 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.

Cuadro de diálogo: Ir a especial con la opción matriz actual seleccionada

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.

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.

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:

 ABCD
1Ventas Sin RepetidosCon Repetidos
2Madrid   
3Sevilla   
4Madrid   
5León   
6Toledo   
7Toledo   
8Cuenca   
9Oviedo   
10Oviedo   

 

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.

Hoja Excel con rango C2:C5 mostrando Zaragoza, Cuenca, León y Sevilla como no repetidos.

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.

Hoja Excel con rango D2:D7 mostrando Oviedo, Toledo y Madrid como valores 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.

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.

Rango B4:H4 mostrando el encabezado de los días de la semana.

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.

Rango B6:H10 con fórmula matricial de los días de la semana

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.

Rango B5:H10 con 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.

Resultado del calendario mensual.

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
AnteriorÍndiceSiguiente
Barra de navegación por la página
Fórmulas matriciales
  • Cálculo habitual
  • Fórmula matricial: Un resultado
  • Fórmula matricial: Varios resultados
  • Editar fórmula matricial
  • Seleccionar rango matricial
  • Eliminar fórmula matricial
  • Valores únicos y repetidos
  • Calendario (Fórm. matricial)
  • Otros ejemplos
Barra de navegación: Consultar también
Consultar también
  • Insertar datos
  • Insertar fórmulas
  • Fórmulas avanzadas
  • Cálculo automático
  • Insertar funciones
  • Errores en fórmulas
  • Listas personalizadas
  • Rellenar
  • Nombres

©2016 - trucosycursos.es

Política de privacidad

Scroll Up
error:
DSGVO Logo El sitio web utiliza cookies propias y de terceros para recopilar información que ayuda a optimizar su visita a sus páginas web. 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... Aceptar Rechazar Configuración