jueves, 6 de octubre de 2016

Cómo Sumar Diagonales de una Matriz en Excel

Expondré hoy la forma de sumar los valores de ambas diagonales principales de una matriz cuadrada localizada en cualquier lugar.

La idea surge de la pregunta planteada por un lector:
Agradecería que me ayudara a sumar los elementos de las diagonales principales de cualquier matriz cuadrada, es decir dada una matriz cuadrada, entonces como hallar la suma de los elementos de las diagonales principales usando excel


En un primer paso introductorio, antes de resolver la cuestión planteada, obtendremos un listado de dichos elementos de la matriz.
Para esto emplearemos la función INDICE y FILAS con rangos corridos... Veamos cómo.
Listado de la diagonal principal 1:

Cómo Sumar Diagonales de una Matriz en Excel


La fórmula añadida en F4 será:
=INDICE(B4:D4;;FILAS($4:4))
observemos como trabajamos con un rango corrido $4:4 que aplicado sobre la función FILAS,nos devuelve para cada fórmula arrastrada:
FILAS($4:4) = 1
FILAS($4:5) = 2
FILAS($4:6) = 3
llegando a nuestro objetivo para cada quedarnos con la posición de columna 1, 2 ó 3 que empleamos como argumento de la función INDICE.


De forma similar para obtener la segunda diagonal principal.
Listado de la diagonal principal 2:

Cómo Sumar Diagonales de una Matriz en Excel


En este caso la fórmula añadida en F9 será:
=INDICE(B4:D4;;FILAS(4:$6))
observemos como trabajamos con un rango corrido 4:$6 que aplicado sobre la función FILAS,nos devuelve para cada fórmula arrastrada:
FILAS(4:$6) = 3
FILAS(5:$6) = 2
FILAS(6:$6) = 1
llegando a nuestro objetivo para cada quedarnos con la posición de columna 3, 2 ó 1 que empleamos como argumento de la función INDICE.


Vista esta introducción, vayamos ahora al cálculo directo de la suma de estos valores.

Para conseguir la suma de la diagonal principal construimos la siguiente fórmula:
=SUMA(SI(FILAS(B4:D6)+FILA(B4:D6)-MAX(FILA(B4:D6))=COLUMNAS(B4:D6)+COLUMNA(B4:D6)-MAX(COLUMNA(B4:D6));B4:D6;0))
(ejecutada matricialmente!!!)

Cómo Sumar Diagonales de una Matriz en Excel


La fórmula matricial es algo larga pero muy simple en cuanto a la idea.
Se trata de sumar solo aquellos elementos de la matriz cuadrada cuando coincidan ambos lados de la igualdad:
FILAS(B4:D6)+FILA(B4:D6)-MAX(FILA(B4:D6))=COLUMNAS(B4:D6)+COLUMNA(B4:D6)-MAX(COLUMNA(B4:D6))
que vemos en detalle en la imagen siguiente (por separado):

Cómo Sumar Diagonales de una Matriz en Excel


Si nos fijamos bien, comprobamos cómo ambas matrices se cruzan/coinciden en los elementos de la diagonal principal...

Finalmente, para conseguir la suma de la segunda diagonal principal construimos la siguiente fórmula:
=SUMA(SI(FILAS(B4:D6)-FILA(B4:D6)+MIN(FILA(B4:D6))=COLUMNA(B4:D6)-MIN(COLUMNA(B4:D6))+1;B4:D6;0))
(ejecutada matricialmente!!!)

Cómo Sumar Diagonales de una Matriz en Excel


El funcionamiento es idéntico a la anterior, obviamente con matrices coincidentes distintas:

Cómo Sumar Diagonales de una Matriz en Excel



Algo interesante de estas funciones matriciales es que sirven para cualquier situación o localización de la matriz cuadrada.

Como comentario final, y objetivo práctico del post, remarcar la importancia de las funciones empleadas FILA, FILAS, COLUMNA o COLUMNAS para determinar la posición de concreta de cada elemento... numerándolos tal y como necesitábamos par lograr las coincidencias en la igualdades o pruebas lógicas de la función SI; controlando así qué sumar y qué no.

7 comentarios:

  1. Hola Ismael,

    Otra manera de hacerlo. Si es más sencilla o complicada lo dejo a tu elección.

    Dos fórmulas, una para cada diagonal:

    =SUMAPRODUCTO(((FILA(Matriz)-MIN(FILA(Matriz)))=(COLUMNA(Matriz)-MIN(COLUMNA(Matriz))))*Matriz)

    =SUMAPRODUCTO(((FILA(Matriz)-MIN(FILA(Matriz)))=(MAX(COLUMNA(Matriz))-COLUMNA(Matriz)))*Matriz)

    Basicamente lo que hacen ambas fórmulas es generar otra matriz de VERDADEROs y FALSOS de forma que los VERDADEROS estén en cada una de las diagonales. Se multiplica esta nueva matriz por la matriz que se quiere calcular (nombrada como "Matriz") y se suma con el encapsulador universal de fórmulas matriciales que es SUMAPRODUCTO.

    Un saludo,

    Daniel

    ResponderEliminar
  2. Hola!

    Para sumar la diagonal, puedes usar (no CSE):

    =SUMA(INDICE(A1:C3;N(SI(1;{1;2;3}));N(SI(1;{1;2;3}))))

    o también

    =SUMAPRODUCTO(A1:C3;M.UNIDAD(3))

    Para la otra diagonal, usar:

    =SUMA(INDICE(A1:C3;N(SI(1;{3;2;1}));N(SI(1;{1;2;3}))))

    ¡Bendiciones!

    ResponderEliminar
    Respuestas
    1. Muchas gracias John,
      muy buena la de emplear la matriz unidad (M.UNIDAD)!!

      Un saludo

      Eliminar
  3. Buenas tardes,

    Tengo una matriz en excel

    de 5x5

    por favor me ayudan con una forma de extraer todas las diagonales y saber cuales son los números que le corresponden cada ubicacion.


    1 2 3 4 5
    6 7 8 9 10
    11 12 13 14 15
    16 17 18 19 20
    21 22 23 24 25

    Hay unas particularidades con los números 3, 6, 13, 19

    las casillas que le corresponden a las diagonales del 3 son ( 3, 7,11,9 y 5)

    las casillas que le corresponden a las diagonales del 6 son ( 6,2,12,18, y 24)

    las casillas que le corresponden a las diagonales del 13 son ( 1,7,13,19, y 25)
    las casillas que le corresponden a las diagonales del 19 son ( 1,7,13,19, 25,23 y 15)

    ResponderEliminar
    Respuestas
    1. Hola
      muy interesante cuestión...
      Publicaré un post en el blog en cuanto me sea posible

      Un cordial saludo

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.