domingo, 2 de noviembre de 2008

Funciones matriciales en Excel

Puede descargar los siguientes archivos.


Las funciones matriciales en Excel permiten efectuar cálculos muy interesantes y proporcionan a la hoja de cálculo una enorme potencia. Las funciones matriciales, en general, abarcan varias celdas, aunque en algunos casos únicamente se centran en una única celda. Las fórmulas matriciales se identifican ya que se encuentran entre corchetes {}. Veamos algunas aplicaciones. 

Hoja 'Formula'

Las fórmulas matriciales pueden abarcar varias celdas o una sola. En esta hoja vamos a ver la potencias de cálculo de este tipo de expresiones cuando arrojan el resultado en una sola celda.


Para introducir una fórmula matricial se han de seguir tres pasos:
  1. Seleccionar el rango en el que la fórmula matricial dejará sus valores. Si la fórmula matricial no abarca más de una celda simplemente no situaremos en esa celda.
  2. Escribir la fórmula propiamente dicha
  3. Para validar no se ha de pulsar INTRO, sino que lo que hemos de pulsar es la combinación de tres teclas: CONTROL+MAYUSCULAS+INTRO
Al ver una función matricial en Excel la verá ente llaves {}. Estas llaves no las debe introducir usted al escribir la fórmula, las introducirá Excel al validar la función pulsando CONTROL+SHIFT+INTRO.

Recuerde que la tecla de MAYUSCULAS también se la llama SHIFT.


18 comentarios:

  1. Excelente ejemplo práctico sobre un tema que no tenía claro. Hasta ahora me calentaba la cabeza con complicadas funciones de base de datos. Esto simplifica muchas cosas, aunque requiere una abstracción algo mayor.

    Gracias.

    ResponderEliminar
  2. Excelentes ejemplos, no sabia que se podia hacer tanto con las fórmulas matriciales, muchas gracias

    ResponderEliminar
  3. Dudas.- Al desarrollar una matriz que no sea cuadrada en cualquiera de las celdas de resultados me aparece #valor en lugar del resultado, MI PREGUNTA es:Estos arreglos son para matrices cuadradas únicamente por que matrices 3x2, 4x3, 5x2 etc, el resultado en las celdas es #valor? Por favor sáquenme de mi duda.- Gracias. Manuel Antonio Castillo Olivera.

    ResponderEliminar
  4. Las funciones matriciales existen en cualquier tamaño (de una celda, en forma de una columna, una fila, un rango rectangular o cuadrado).

    Pero existen algunas funciones matriciales que necesariamente han de ser cuadradas, por ejemplo, =MINVERSA que actúa sobre una matriz cuadrada y su resultado es otra matriz cuadrada. Véase la hoja 'Sistemas', en el rango C21:E23.

    ResponderEliminar
  5. Gracias por compartir sus conocimientos, interesante el tema. Le confeso que alguna vez usé formulas matriciales bien complejas, pero con ayuda. Lo que no logro entender, es, como se sabe en que casos me serviría utilizar las func. matriciales? Cuando hay varias columnas a consultar o buscar?, Cuando hay varias filas y/o columnas a cruzar? No se si me explico en cuanto a miduda?. Saludos desde Asunción, PAraguay.

    ResponderEliminar
  6. Hola Robert.

    Existen algunas funciones matriciales definidas por Excel que tienen una utilidad concreta. Por ejemplo, FRECUENCIA, DESREF, y las matemáticas que comienzan por M: MDETERM, MINVERSA, MMULT.

    Luego tienes otras funciones matriciales que crea el usuario y se se basan en manejar rangos, y operar con ellos. Muchas de estas funciones son del tipo: =SUMA(rango1*rango2*filtro) donde filtro puede ser rango3<100.

    En general lo que puedas hacer con varias columnas es posible que se pueda implementar en una función matricial.

    Un cordial saludo.

    ResponderEliminar
  7. muchas gracias Adolfo por tus excelentes ayudas. Mira tengo el siguiente inconveniente: Tengo un presupuesto en pesos y deseo pasarlo todo a dolares, (es decir cada entrada del presupuesto dividida por la tasa de cambio). Como se puede hacer a través de una macro?? o cual sería la mejor colución?

    ResponderEliminar
  8. Buenos días Adolfo, la verdad que las formulas estan perfectas. Pero en mi caso no las puedo adaptar. Necesito poner la formula matricial en una hoja distinta a la que tengo la matriz y las referncias y no lo puedo ahcer. Me podrias ayudra?
    Muchas gracias

    Sol

    ResponderEliminar
  9. Buenos días Adolfo,
    Las formulas estan perfectas, solo que no las puedo adaptar a lo que yo necesito. Quiero poner la formula en una hoja distinta a la que tengo la matriz y no logro hacerlo.
    Me podrias ayudar?
    Muchas gracias

    SOL

    ResponderEliminar
  10. Hola Sol.

    Las funciones matriciales funcionan estupendamente aunque estés trabajando con rangos de otra hoja.

    Por ejemplo, puedo escribir esta fórmula en la Hoja2 referida a rangos de la Hoja1:

    {=SUMA(Hoja1!C6:C9*(Hoja1!D6:D9<=1000))}

    Las llaves {} se ponden para indicar que se trata de una fórmula matricial, pero no se han de escribir.

    También puedes operar con nombres de rango que es lo más aconsejable. La fórmula anterior escrita en la Hoja2 puede trabajar con rangos de la Hoja1, de la siguiente forma:

    {=SUMA(Habitantes*(altitud<=1000))}

    La fórmula anterior nos daría la suma de los habitantes de una serie de ciudades que cumplen que su altitud es menor o igual a 1.000 metros.

    Espero que ahora lo consigas.

    Adolfo Aparicio

    ResponderEliminar
  11. Adolfo, excelente la nota y muchas gracias por el archivo que aclara tanto el uso de matrices!

    Muchas gracias!!

    ResponderEliminar
  12. Estimado Adolfo, excelente artículo!
    He logrado aplicar las funciones matriciales para obtener el máximo de una serie de datos que cumpla con varias condiciones a la vez. Pero al tratar de hacerlo para sacar el mínimo me da siempre cero. Lo único que hago es cambiar "max" por "min"
    Me podrías dar una mano con esto?
    Abrazo,
    Agustín, desde Uruguay

    ResponderEliminar
  13. Que tal, como aplica un formula de matriz para la funcion buscarv

    ResponderEliminar
  14. ¿se pueden utilizar este tipo de funciones para reordenar rangos? Me explico: tengo un rango de celdas asi: (cada coma es una columna)
    Antonio, rojo, verde
    Maria, azul
    Luis, rojo, gris, blanco
    Y quisiera ordenarlo de la siguiente forma:
    Antonio, rojo
    Antonio, verde
    Maria, azul
    Luis, rojo
    Luis, gris
    Luis, blanco

    ResponderEliminar
    Respuestas
    1. Hola Fernando.
      Mira el siguiente post.
      Convertir una tabla de doble entrada en una base de datos (sin repetición)
      Y aún mejor mira el siguiente.
      Convertir una Tabla de doble entrada en una base de datos
      Usando el método que se comenta en el enlace anterior lo he probado con tus datos y ha funcionado.
      Comienzo con la siguiente tabla.
      http://i.imgur.com/Kb076OO.png
      Y al final llego a esta otra.
      http://i.imgur.com/L7qy9nn.png
      Finalmente eliminas las dos columnas que no te sirven y ya lo tienes.
      Un saludo.

      Eliminar
  15. adolfo tengo una duda, tengo una tabla donde en una columna tengo las zonas y en otra los nombres de los trabajadores y en otra el valor de lo vendido en el mes necesito saber si en una formula puedo darle el valor de lo vendido y que obtenga el nombre y la zona del vendedor saludos desde colombia gracias

    ResponderEliminar