Sumar las celdas de la diagonal de una tabla

Puede descargar el archivo suma_diagonal.xlsm

Deseamos sumar los valores de las celdas de la diagonal de una tabla. La tabla que usamos de ejemplo está formada por números aleatorios que varían si pulsamos la tecla F9 de recálculo manual.

Con la función INDIRECTO

Vamos a resolverlo utilizando la función INDIRECTO.

En este ejemplo la celda de la primera esquina (celda D6) se encuentra en la fila 6, columna 4, por lo que en la función INDIRECTO  de la celda O2 hemos tenido que restar -2.

=INDIRECTO("F"&FILA()&"C"&FILA()-2;0)

Si la tabla estuviera en otro sitio y la primera esquina estuviera en la columna 10 tendríamos que sumar 4.  Veamos el motivo:

Si la celda de la primera esquina es la celda J6 los valores serían

• Fila 6
• Columna 10
• Tendremos que sumar: 10 - 6 = 4

Con Macro

Podemos crear una macro utilizando código VBA para Excel que nos pregunte por los dos extremos de la diagonal. Primero nos preguntará por la esquina superior izquierda de la tabla.

Luego nos preguntará por la esquina inferior derecha de la tabla.

La tabla ha de ser cuadrada, en nuestro ejemplo estamos trabajando con una tabla 10x10. Se trata de una tabla de 10 filas y 10 columnas, que tendrá necesariamente 10 celdas en la diagonal.

Veamos el código del procedimiento que permite realizar la suma de la diagonal proporcionando las dos esquinas.

 Sub Informa_suma_diagonal()  
 Dim total, E1 As Range, E2 As Range  
 Dim i As Long, fila As Long, columna As Long  
 Set E1 = Application.InputBox(prompt:="Seleccione la celda superior izquierda de la tabla." & _  
  vbLf & "La tabla debe ser cuadrada.", Title:="Suma de la diagonal de una tabla", Type:=8)  
 'el 8 se usa cuando se toma una referencia a una celda como un objeto Range  
 Set E2 = Application.InputBox("Seleccione la celda inferiror derecha de la tabla." & _  
  vbLf & "Las dos esquinas de la tabla deben estar en la misma diagonal.", "Suma de la diagonal de una tabla", , , , , , 8)  
 If E2.Row - E1.Row <> E2.Column - E1.Column Then 'si la tabla no es cuadrada finaliza el programa  
   MsgBox "ERROR: estas dos esquinas no pertenecen a la misma diagonal." & _  
    vbLf & "El programa finalizará.", , "Suma de la diagonal de una tabla"  
   End 'finaliza la ejecución del programa ya que se ha detectado un error  
 End If  
 total = 0  
 For i = 1 To E2.Row - E1.Row + 1 'hasta el número de elementos de la diagonal  
   fila = E1.Row + i - 1 'contador de fila que comienza en la fila de la esquina superior izquierda  
   columna = E1.Column + i - 1 'contador de columna que comienza en la columna de la esquina superior izquierda  
   total = total + Cells(fila, columna).Value  
 Next i  
 MsgBox "La suma de la diagonal es " & total, , "Suma de la diagonal de una tabla"  
 End Sub  

Con función programada

Posiblemente es más útil trabajar con una función creada por el usuario. La función tiene dos parámetros que son las dos esquinas de la diagonal.

• Esquina_1 es la esquina superior izquierda de la diagonal
• Esquina_2 es la esquina inferior derecha de la diagonal

Si las dos celdas que se proporcionan no están en la misma diagonal se mostrará un aviso de error diciendo:

ERROR: no diagonal

 Function SumaDiagonal(Esquina_1 As Range, Esquina_2 As Range) As Variant  
 Dim total As Double  
 Dim i As Long, fila As Long, columna As Long  
 total = 0  
 For i = 1 To Esquina_2.Row - Esquina_1.Row + 1  
   fila = Esquina_1.Row + i - 1  
   columna = Esquina_1.Column + i - 1  
   If Application.WorksheetFunction.IsNumber(Cells(fila, columna).Value) Then  
     total = total + Cells(fila, columna).Value  
   End If  
 Next i  
 If Esquina_2.Row - Esquina_1.Row <> Esquina_2.Column - Esquina_1.Column Then  
   SumaDiagonal = "ERROR: no diagonal"  
 Else  
   SumaDiagonal = total  
 End If  
 End Function  

Alternativa propuesta

Le propongo que intente crear una macro que ponga de color amarillo, o el que usted prefiera, las celdas de la diagonal de una tabla. A la macro la tendríamos que proporcionar las dos esquinas de la diagonal, o bien, seleccionar una única celda de la tabla y luego que se señalara toda la tabla, tal como hace el atajo de teclado CONTRO+SHIFT+* de esta forma podríamos ahorrar que la macro nos pida parámetros.

Luego tendríamos que usar las ideas que se proporcionan en el siguiente post del blog:

Contar y Sumar los valores de las celdas según su Color