Operaciones con colores en Excel

En la nota Usando funciones XLM (Excel 4) en hojas de cálculo, mostraba cómo se podía obtener el número de color del fondo de una celda. Esto implicaba el uso de "macrofunciones" (funciones del lenguaje de macro XLM que existió hasta la versión 4) dentro de nombres. Esta técnica nos permitía realizar operaciones como sumar o contar, basándonos en los colores del fondo de un rango de celdas.
No tenía intenciones de volver sobre el tema, hasta que hace unos días un compañero de trabajo me manda un cuaderno Excel con una lista de cerca de 4.000 clientes de la empresa. La tabla estaba ordenada alfabéticamente y cada nombre de cliente tenía un color distinto de acuerdo a las condiciones de crédito (al contado: azul; 30 días: verde; 60 días: amarillo; etc.). Su tarea era sumar los saldos de los clientes por condiciones de crédito y calcular el promedio, para lo cual había que contar el número de clientes en cada grupo.
Después de señalarle que esa hoja era uno de los mejores ejemplos de lo que no se debe hacer en Excel (preferir la estética a la utilidad) intenté explicarle la técnica a usar con las macrofunciones. Como podrán imaginar mi compañero no estaba del mejor ánimo para explicaciones, después de haber invertido horas en poner fondos de color por tipo de crédito para descubrir al final que no puede hacer nada con la lista.
Decidí que lo mejor sería escribir unas UDF (funciones definidas por el usuario) que hagan la tarea.
Empezamos por una función que de cómo resultado el color del fondo de la celda:

Function extraer_color(miCelda As Range)
    extraer_color = miCelda.Interior.ColorIndex
End Function

Aplicamos la fórmula a algunas celdas con fondo de color

La celda A6 no tiene ningún fondo y de ahí el resultado. Podemos cambiar el código de esta manera para que en caso de no haber fondo el resultado sea 0

Function extraer_color(miCelda As Range)
   Select Case miCelda.Interior.ColorIndex
     Case xlNone
       extraer_color = 0
     Case Else
       extraer_color = miCelda.Interior.ColorIndex
   End Select
End Function

Nuestra próxima función nos permitirá contar por color:

Function contar_por_color(RangoColor As Range, CeldaColor As Range)
 Dim rngCelda As Range

 For Each rngCelda In RangoColor
  If rngCelda.Interior.ColorIndex = CeldaColor.Interior.ColorIndex Then
   contar_por_color = contar_por_color + 1
  End If
 Next
End Function

La función tiene dos variables: RangoColor, que es el rango dónde queremos contar por color y CeldaColor, que es la celda que contiene el color del criterio.

Finalmente, una función para contar por color:

Function sumar_por_color(RangoColor As Range, CeldaColor As Range, RangoSumar As Range)
Dim rngCelda As Range
Dim colOffset As Long

colOffset = RangoSumar.Column - RangoColor.Column

For Each rngCelda In RangoColor
If rngCelda.Interior.ColorIndex = CeldaColor.Interior.ColorIndex Then
sumar_por_color = sumar_por_color + rngCelda.Offset(0, colOffset).Value
End If
Next
End Function



En esta función usamos un tercer argumento para el rango que contiene los valores a sumar. Los rangos RangoColor y RangoSumar deben tener la filas en común.
La variable colOffset calcula la distancia, en número de columnas, entre el rango con los valores y el rango con los fondos de color. Naturalmente, este número puede ser positivo, si los números están a la derecha de los colores, o negativo si lo están a la izquierda.

El cuaderno con las funciones puede descargarse aquí

Technorati Tags: MS Excel