Para ampliar los conceptos del post anterior, vamos a ver un ejemplo sobre el cual he recibido muchas consultas: sumar según el color de fondo de la celda.
La primera vez que me plantearon este problema lo resolví mediante macros, pero ya que estamos en el tema de las udf, lo resolveremos de esta forma.
Para comenzar, vamos paso a paso:
Mi función recibirá dos argumentos
a) el rango a sumar
b) el color que debe sumar.
Si el color de la celda coincide con el color que el usuario me pasa como segundo argumento, entonces sumo.
Ahora bien, Excel reconoce los colores mediante números. Para dejarnos anotados estos numeros muchas personas graban una macro mientras colorean la celda, haciéndose factible entonces visualizar el dato numérico que Excel generó.
Pero mas sencillo nos resultaría armar una tabla de colores y aplicar una UDF para conocer que color tiene de fondo la celda. Vamos a VBA (alt + f11), menu insertar / modulo y escribimos lo siguiente:
Public Function DetectarColor(Celda As Range)
'ColorIndex es el numero de color que aplica Excel:
DetectarColor = Celda.Interior.ColorIndex
End Function
con esa línea de código obtenemos el siguiente resultado:'ColorIndex es el numero de color que aplica Excel:
DetectarColor = Celda.Interior.ColorIndex
End Function
mi udf en acción: detecta cada color de celda y me devuelve el numero de ese color
el -4142 corresponde al "no color", es decir, a la opción "sin relleno". Con un condicional podríamos volverlo a Cero u otro número mas conveniente, pero lo dejamos ahí.
Ya tengo una fución que me detecta el color. Ahora vayamos a la función que suma si encuentra ese color dentro del rango:
Public Function SumarSegunColor(RangoSuma As Range, CeldaColor As Range)
'recibo dos argumentos: el rango a sumar y una celda coloreada.
'estas variables son para uso interno de la funcion
Dim QueColor As Integer
Dim Celda As Range
Dim Suma
'llamo a la primer funcion para detectar el color de la celda pasadada
'como segundo argumento
QueColor = DetectarColor(CeldaColor)
'recorro el rango:
For Each Celda In RangoSuma.Cells
'si el color coincide con el resultado de la primer función
If Celda.Interior.ColorIndex = QueColor Then
'y si el valor que contiene la celda es numérico, procedo a la suma:
If IsNumeric(Celda.Value) Then
Suma = Suma + Celda.Value
End If
End If
Next Celda
'devuelvo el resultado:
SumarSegunColor = Suma
'destruyo la variable de objeto:
Set Celda = Nothing
End Function
'recibo dos argumentos: el rango a sumar y una celda coloreada.
'estas variables son para uso interno de la funcion
Dim QueColor As Integer
Dim Celda As Range
Dim Suma
'llamo a la primer funcion para detectar el color de la celda pasadada
'como segundo argumento
QueColor = DetectarColor(CeldaColor)
'recorro el rango:
For Each Celda In RangoSuma.Cells
'si el color coincide con el resultado de la primer función
If Celda.Interior.ColorIndex = QueColor Then
'y si el valor que contiene la celda es numérico, procedo a la suma:
If IsNumeric(Celda.Value) Then
Suma = Suma + Celda.Value
End If
End If
Next Celda
'devuelvo el resultado:
SumarSegunColor = Suma
'destruyo la variable de objeto:
Set Celda = Nothing
End Function
El ejemplo en imagenes:
a J1 le apliqué el color de fondo que quiero incluir en la suma
Incluí en la funcion un condicional mas: al margen del color, suma si encuentra un valor numérico. Esto es para evitar errores.
Para contar las celdas que poseen un determinado color, haríamos el siguiente código, agregando un tercer parámetro cuyo valor será 1 o 0: si es 1 solo cuenta numeros, si es 0, cuenta cualquier celda que NO esté vacía:
Public Function ContarSegunColor(RangoSuma As Range, CeldaColor As Range, Tipo As Byte)
'recibo tres argumentos: el rango a contar, una celda coloreada y si
'sumo solo numeros (1) o cualquier dato (0)
'estas variables son para uso interno de la funcion
Dim QueColor As Integer
Dim Celda As Range
Dim Contador
'llamo a la primer funcion para detectar el color de la celda pasadada
'como segundo argumento
QueColor = DetectarColor(CeldaColor)
'recorro el rango:
For Each Celda In RangoSuma.Cells
'si el color coincide con el resultado de la primer función
If Celda.Interior.ColorIndex = QueColor Then
'aqui evaluo el tercer argumento y procedo en consecuencia
If Tipo = 1 Then
If IsNumeric(Celda.Value) Then
Contador = Contador + 1
End If
ElseIf Tipo = 0 Then
Contador = Contador + 1
End If
End If
Next Celda
'devuelvo el resultado:
ContarSegunColor = Contador
'destruyo la variable de objeto:
Set Celda = Nothing
End Function
los resultados:'recibo tres argumentos: el rango a contar, una celda coloreada y si
'sumo solo numeros (1) o cualquier dato (0)
'estas variables son para uso interno de la funcion
Dim QueColor As Integer
Dim Celda As Range
Dim Contador
'llamo a la primer funcion para detectar el color de la celda pasadada
'como segundo argumento
QueColor = DetectarColor(CeldaColor)
'recorro el rango:
For Each Celda In RangoSuma.Cells
'si el color coincide con el resultado de la primer función
If Celda.Interior.ColorIndex = QueColor Then
'aqui evaluo el tercer argumento y procedo en consecuencia
If Tipo = 1 Then
If IsNumeric(Celda.Value) Then
Contador = Contador + 1
End If
ElseIf Tipo = 0 Then
Contador = Contador + 1
End If
End If
Next Celda
'devuelvo el resultado:
ContarSegunColor = Contador
'destruyo la variable de objeto:
Set Celda = Nothing
End Function
la primer funcion devuelve 6, por que el ultimo argumento es 0 y cuenta también las celdas que poseen texto (zzz)
De nuevo Excel nos demuestra que es realmente infinito y sus fronteras solo se encuentran delimitadas por nuestras necesidades y conocimientos.
Como hago para que se recalcule el valor de la función cuando modifico el color o valor de una celda del rango?
ResponderEliminarMuchas Gracias
Estimado: si el recalculo en Excel se encuentra activado (siempre es así, salvo que manualmente o mediante una macro lo hayas cambiado), cada vez que se modifica el valor de una celda todas las formulas se recalculan de forma automática, sin necesidad de hacer nada.
ResponderEliminarLa mala noticia es que Excel no "dispara" un evento especial cada vez que se cambien los colores de las celdas, y no resulta posible que una formula se recalcule con el simple hecho de cambiar de color de fondo o de fuente.
Pero podemos hacer un pequeño truco: te vas a un modulo y escribís:
Private Sub Recalcular(H as String)
Sheets(H).Calculate
End Sub
El Sub de arriba recibe un argumento, que es el nombre de la hoja en donde tenes las funciones que suman por color.
Ahora te vas a la ventana de código de la hoja y en los eventos Worksheet_Change y Worksheet_SelectionChange pones:
Recalcular(Activesheet.Name)
Y listo. Esto fuerza a Recalcular el libro (instrucción: calculate) cada vez que el usuario cambie la selección actual o bien modifique algún valor de hoja.
De esta forma, por decirlo de alguna manera, estaríamos creando los eventos necesarios paradetectar cambios y que las funciones se actualicen cada vez que Excel detecte cambios,
Proba y cualquier cosa me avisas, que te armo un archivo y te lo envio.
Gracias por tu mensaje.
End Sub
O bien, para hacerla mas fácil, te vas a la ventana de código de la hoja y en los eventos Worksheet_Change y Worksheet_SelectionChange solamente ingresas:
ResponderEliminarActivesheet.Calculate
Y listo, mas sencillo y sin la necesidad de crear procedimientos aparte.