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.
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.
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:
