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

Descargar el fichero: CeldaColor.xlsm

Deseamos contar y sumar los valores de ciertas celdas según su color. Para ello, hemos de utilizar MacroFunciones también denominadas Funciones Diseñadas por el Usuario, que se programan como si se tratara de una macro, en el Editor de Visual Basic. En este Post aprenderemos a manejar los 56 colores básicos que podemos poner como fondo a una celda. Esto se hace con la instrucción ColorIndex.

Hoja 1

En la primera hoja tenemos tres macros, con tres botones. La primera que deberíamos ejecutar es 'Limpia' que borra el contenido y los formatos de las columnas C y E. Al efectuar la limpieza observamos que las columnas F y G nos dan error, ya que la fórmula que contienen deja de funcionar. Luego veremos como se resuelve este error.

El botón 'Lista Colores' lanza la macro denominada 'marcas' que genera, en la columna C, una lista con los números del 1 al 56. A cada una de esas celdas las dota del color correspondiente según su código de color entre 1 y 56.

Observar que al lanzar esta macro la columna G ya nos está dando el código de color gracias a una función que luego veremos. Por el contrario en la columna F obtenemos un código extraño (-4142).

Código:

Sub marcas()
'Pone un tono de color a cada celda entre 1 y 56
Dim i As Integer
For i = 1 To 56
With Range("C5").Offset(i, 0)
.Value = i
.Interior.ColorIndex = i
.Interior.Pattern = xlSolid
End With
Next
End Sub

El botón 'Detecta Color' lanza la macro denominada DetectaColor.

Código:

Sub DetectaColor()
'Detecta el tono de color de cada celda
'y pone su número a la derecha de la celda
Dim i As Integer
Dim ColorCelda As Integer
Range("C5").Select
For i = 1 To 56
Selection.Offset(1, 0).Select
ColorCelda = Selection.Interior.ColorIndex
Selection.Offset(0, 2).Value = ColorCelda
Next
Range("A1").Select
End Sub

Vamos a crear una función que nos proporcione el número del color entre 1 y 56. La función se denomina Num_Color.

Código:

Function num_color(Celda As Range)
num_color = Celda.Interior.ColorIndex
End Function

Esta función estará disponible en el Asistente de Funciones dentro de la categoría de funciones 'Definidas por el usuario'. En la columna F determinamos con esta función el código de colore correspondiente a cada celda de la columna C. Si la función se aplica para calcular el color de una celda vacía nos da el resultado -4142. Si se aplica sobre una celda con cierto color nos da un número entre 1 y 56. Pero si se cambia posteriormente el color de esa celda la fórmula no se actualiza automáticamente, ni siquiera pulsando la tecla F9 de recálculo manual. Este es el motivo por el que al limpiar (borrar) los valores de la columna C, en la columna F obtuviéramos errores. Para que la función refresque debemos editarla, o pulsando F2 y luego pulsando Enter, o haciendo doble clic con el ratón sobre ella y luego pulsando Enter.

Una forma parcial de paliar esto es realizando una pequeña modificación de la fórmula, que es la que podemos ver en la columna G. En la celda G6 escribimos:

=num_color(C6)+AHORA()*0

Lo que hacemos es añadir una función volatil. La función =AHORA() nos proporciona la fecha y hora actuales del sistema. Con este truco conseguimos que las fórmulas de la columna G se actualicen al pulsar la tecla F9. Haga la prueba, cambie el color de una celda de la columna C y pulse F9, verá como en la columna G se actualiza el número que corresponde al color modificado.

Hoja 2

En la Hoja2 vamos a desarrollar funciones (macrofunciones) que permiten contar y sumar según los colores asignados a las celdas de un rango.

Primero generamos en la columna C una serie de 20 celdas con diferentes colores cuyo código de color va entre 3 y 8, de forma aleatoria. Cada una de las 20 celdas del rango C5:C24 contiene un valor numérico generado de forma aleatoria entre 100 y 200. La macro que permite generar los valores de la columna C se denomina color_aleatorio. Se ejecuta pulsando sobre el botón rectangular incrustado en la Hoja2 que tiene por titulo "Genera Colores Aleatorios".

Código:

Sub color_aleatorio()
'Pone a cada celda un tono de color
'aleatoriamente entre 3 y 8
Dim i As Integer
For i = 1 To 20
With Range("C4").Offset(i, 0)
.Value = Int(Rnd() * 100) + 100
.Interior.ColorIndex = Int(Rnd() * 6) + 3
.Interior.Pattern = xlSolid
End With
'ahora generamos la columna D
Range("C4").Offset(i, 1).Value = Int(Rnd() * 500) + 500
Next
End Sub

Función que permite CONTAR según color

Queremos contar cuantos colores existen de cada tipo en la columna C. Cuantos rojos, azules, amarillos, rosas o azules claro existen. En total deben sumar 20 que son las celdas coloreadas de la columna C.

Método 1

La función contar_color nos permite contar los colores que existen en RangoColor que sean iguales al color de CeldaColor.

Código:

Function contar_color(RangoColor As Range, CeldaColor As Range)
Dim Celda As Range
For Each Celda In RangoColor
If Celda.Interior.ColorIndex = CeldaColor.Interior.ColorIndex Then
contar_color = contar_color + 1
End If
Next
End Function

En la columna G utilizamos la función contar_color para determinar cuantas celdas de la columna C son de color rojo, verde, azul, amarillo, rosa o azul claro. En la celda G6 calculamos las celdas de color rojo con la expresión:

=contar_color($C$5:$C$24;F6)+AHORA()*0

En la celda G12 contamos los valores obtenidos y comprobamos que son 20, tantos como celdas existen en el rango C5:C24. En la celda G13 comprobamos que cuadra la suma.

=CONTAR(C:C)=G12

Método 2

Creamos la columna auxiliar B que utiliza la función =num_color que ya hemos visto en la Hoja1. La fórmula de la celda B5 es:

=num_color(C5)+AHORA()*0

De esta forma conseguimos tener en la columna B el código del color correspondiente de la columna C.

• Las celdas rojas tendrán código 3
• las verdes tendrán código 4
• las azules tendrán código 5
• las amarillas tendrán código 6
• las rosas tendrán código 7 y
• las de color azul claro tendrán código 8

En la columna H utilizamos la función =CONTAR.SI para determinar el número de celdas que existen de cada color. En la celda H6 escribimos la expresión que nos da las celdas de color rojo:

=CONTAR.SI($B$5:$B$24;F6)

En la celda H13 comprobamos mediante una fórmula matricial que ambos métodos arrojan los mismos resultados.

{=SUMA(--(G6:G11=H6:H11))=6}

Función que permite SUMAR según color

Ahora deseamos sumar los importes de las celdas en función del color. Queremos saber cuanto suman las de color rojo, y las de color verde, y las de color azul, ....

Método 1

La función sumar_color nos permite sumar el valor numérico de las celdas de un rango según que el color de esa celda se corresponda con el de una celda concreta denominada CeldaColor.

En la columna I calculamos la suma de las celdas de la columna C por cada color. En la celda I6 calculamos la suma de las celdas de color rojo:

=sumar_color($C$5:$C$24;F6)+AHORA()*0

Código:

Function sumar_color(RangoColor As Range, CeldaColor As Range)
Dim Celda As Range
For Each Celda In RangoColor
If Celda.Interior.ColorIndex = CeldaColor.Interior.ColorIndex Then
sumar_color = sumar_color + Celda.Value
End If
Next
End Function

Método 2

En la columna J calculamos la suma por colores con la función =SUMAR.SI. En la celda J6 tenemos la expresión:

=SUMAR.SI($B$5:$B$24;F6;$C$5:$C$24)

Método 3

El método 3 considera que puede suceder que lo que pretendamos sumar no esté precisamente en el mismo rango que se encuentran las celdas coloreadas. En este caso, las celdas coloreadas se encuentran en la columna C y las celdas que queremos sumar están en la columna D. Es necesario que el rango que se desea sumar (D5:D24) sea de la misma dimensión que el rango en el que se encuentran las celdas coloreadas (C5:C24).

Esto se hace con la función sumar_color2.

Código:

Function sumar_color2(RangoColor As Range, _
CeldaColor As Range, RangoSuma As Range)
Dim Celda As Range
Dim col As Long
col = RangoSuma.Column - RangoColor.Column
For Each Celda In RangoColor
If Celda.Interior.ColorIndex = CeldaColor.Interior.ColorIndex Then
sumar_color2 = sumar_color2 + Celda.Offset(0, col).Value
End If
Next
End Function

En nuestro caso utilizamos la columna K para generar estas sumas. La celda K6 tiene la siguiente expresión:

=sumar_color2($C$5:$C$24;F6;$D$5:$D$24)+AHORA()*0

Método 4

Para sumar los importe de la columna D según los colores de la columan C utilizamos la función =SUMAR.SI. Esto se hace en la columna L. La celda L6 tiene la siguiente expresión:

=SUMAR.SI($B$5:$B$24;F6;$D$5:$D$24)

Aquí nuevamente hemos utilizado la columna auxiliar B, que es donde calculamos el código de color de las celdas de la columna C.

Método 5

El método 5 es una variante del método 3. En muchos casos el tercer argumento coincidirá con el primero. Esto es, el RangoSuma será igual en muchos casos al RangoColor, por lo que vamos a convertir este tercer argumento en optativo. Si no se indica nada en este tercer argumento (RangoSuma) se supondrá que lo que queremos sumar esta precisamente en las celdas coloreadas (RangoColor).

Código:

Function sumar_color3(RangoColor As Range, _
CeldaColor As Range, Optional RangoSuma As Range)
Dim Celda As Range
Dim col As Long
If RangoSuma Is Nothing Then
col = 0
Else
col = RangoSuma.Column - RangoColor.Column
End If
For Each Celda In RangoColor
If Celda.Interior.ColorIndex = CeldaColor.Interior.ColorIndex Then
sumar_color3 = sumar_color3 + Celda.Offset(0, col).Value
End If
Next
End Function

Esto se calcula en la columan M. Concretamente en la celda M6 tenemos la fórmula:

=sumar_color3($C$5:$C$24;F6)+AHORA()*0

Expresión que nos permite utilizar únicamente dos argumentos, y en la que sumamos los valores de la columna C cuya celda es de color rojo. Al no utilizar el tercer argumento que es optativo, lo que estamos haciendo es indicar a la fórmula que lo que deseamos sumar está en la columna C que es donde se encuentran las celdas coloreadas.

Observaciones

• Si cambiamos un color es necesario pulsar F9 (tecla de recálculo manual) para que se actualicen los cálculos. Para que esto funcione es por lo que hemos añadido en las funciones la suma de la función =AHORA() multiplicada por cero.
• Las formulas que cuentan y suman según colores NO funcionan si los colores se obtienen con Formato Condicional.
• Las funciones creadas por el usuario pueden llevar argumentos optativos. Para ello se debe utilizar la palabra clave Optional. En el siguiente ejemplo, se suman las variables a y b. La variable b es optativa y si no se pone nada se considera que vale 1.

Hoja 3

En la Hoja3 deseamos sumar los valores que contienen celdas de determinado color y que junto a ellas en la columna de su derecha contienen una palabra concreta. En este ejemplo queremos sumar el valor de las celdas rojas que contienen al lado la palabra "Activo".

Primero vamos a generar las columnas B, C y D. Se crea una tabla con 200 filas que contienen colores aleatorios, con importes de venta aleatorios y donde se genera en la columna D la palabra "Activo" de forma aleatoria con una probabilidad del 40%, que se puede cambiar fácilmente en la macro.

Sub GeneraColorVentas()
'Pone a cada celda un tono de color
'aleatoriamente entre 3 y 8
Dim i As Integer
Dim n As Integer 'número de valores
n = 200
Application.ScreenUpdating = False
Application.Calculation = xlManual
For i = 1 To n
   With Range("C4").Offset(i, 0)
      .Value = Int(Rnd() * 100) + 100
      .Interior.ColorIndex = Int(Rnd() * 6) + 3
      .Interior.Pattern = xlSolid
   End With
   'generamos los números de la columna B
   Range("C4").Offset(i, -1).Value = i
   'ahora generamos las ventas
   Range("C4").Offset(i, 0).Value = Int(Rnd() * 1500) + 500
   'generamos los Activos con probabilidad del 40%
   If Rnd() < 0.4 Then
      Range("C4").Offset(i, 1).Value = "Activo"
   Else
      Range("C4").Offset(i, 1).ClearContents
   End If
Next
Application.ScreenUpdating = True
Calculate
Application.Calculation = xlAutomatic
End Sub

En la columna F calculamos las ventas por colores. Esto se hace con la función =suma_color.
En la columna G calculamos las ventas por colores pero solo de las celdas de la columna C que contengan en la celda contigua de la columna D la palabra 'Activo'.

La función =suma_color_texto es la que nos permite calcular los valores de la columna G.

Function sumar_color_texto(RangoColor As Range, CeldaColor As Range, Texto As String) As Double
Dim Celda As Range
For Each Celda In RangoColor
   If Celda.Interior.ColorIndex = CeldaColor.Interior.ColorIndex _
   And Celda.Offset(0, 1).Value = Texto Then
      sumar_color_texto = sumar_color_texto + Celda.Value
   End If
Next
End Function