Excel Avanzado

Macros, Vba en Excel y muchos ejemplos de nuestro Curso de Excel Avanzado

Excel Avanzado

Función para Sumar Datos por Color

| 6 comentarios

La presente UDF, nos permitirá sumar datos, los cuales los diferenciaremos por color. A continuación crearé una breve base de datos, en donde se diferenciarán a los clientes por su género (hombre y mujer), en este caso particular los hombres serán de color verde y las mujeres serán de color naranja. Así se evidencia en la base de datos:

tabla8

(Para mayor visualización de la imagen, hacer click en la misma)

Como se puede observar, los diversos ingresos de los clientes están entre S/1,000 y S/10,000. Como queremos hallar los ingresos totales por los hombres y las mujeres. Se puede hallar por medio de filtros, pero como podemos tener bases de datos más grandes o diversas, es más útil utilizar la próxima función, la cual se programa de la siguiente forma:

formula6

(Para mayor visualización de la imagen, hacer click en la misma)

La programación se puede interpretar de la siguiente manera:

Function sumacolor(Color As Range, Range As Range) As Long

Dim celda As Range
Dim colornumeros As Integer
Dim sumcol

colornumeros = Color.Interior.ColorIndex

Primero definimos los campos que tomarán la función, primero definimos que el primer campo tomará los colores de dicha celda, y el segundo campo solo escogerá los valores de un rango de datos, los cuales deberán coincidir con el primer campo.

Luego definimos a las variables que utilizaremos, y le diremos al Excel que la variable "colornumeros" será igual a los color que se encuentren en la respectiva celda que escogeremos.

For Each celda In Range
If celda.Interior.ColorIndex = colornumeros Then
sumcol = WorksheetFunction.Sum(celda.Value) + sumcol
End If

Next celda

sumacolor = sumcol

End Function

Una vez que ya definimos la celda a escoger, decimos que si la celda coincide con el color que estamos buscando, la incluya en la sumatoria, luego con "next" indicamos que busque a la siguiente celda y si esta coincide la agrega a la sumatoria, de esta forma se seleccionarán a todas las celdas del mismo color que se encuentren en el rango seleccionado.

formula7

(Para mayor visualización de la imagen, hacer click en la misma)

En nuestro caso en color, seleccionamos la celda que usaremos como referencia, en este caso la "F3", luego en Range seleccionamos todo el campo que queremos sumar, en este caso los ingresos de los clientes. De esta forma encontramos los siguientes resultados, los cuales son la sumatoria de los ingresos por mujeres y hombres.

Para mayor información o si el lector desea agregar nuevos campos, adjunto el presente archivo: UDF - SumaColor

Sumar por color de Celda  (alternativo)

De forma alternativa podemos usar COLOR en lugar de COLORINDEX, con ello tendríamos que cambiar:

Dim colornumeros As Integer

colornumeros = Color.Interior.ColorIndex

Por:

Dim colornumeros2 As String

colornumeros2 = Color.Interior.Color

Esto se produce por que COLORINDEX reconoce una limitada cantidad de colores (56)

El código quedaría de la siguiente forma:

Function sumacolor(Color As Range, Range As Range) As Long
Dim celda As Range
Dim colornumeros2 As String
Dim sumcol
colornumeros2 = Color.Interior.Color
For Each celda In Range
If celda.Interior.Color = colornumeros2 Then
sumcol = WorksheetFunction.Sum(celda.Value) + sumcol
End If
Next celda
sumacolor = sumcol
End Function

Por: Carlos Celi

6 comentarios

  1. Gracias,

    Realmente útil

    Saludos

  2. excelente ejercicio, no obstante no pude hacerlo para que me sume de manera horizontal, es decir sumas de colores en filas.

    A1=ROJA B1=BLANCA C1=ROJA D1= BLANCA E1=ROJA F1=ROJA

    No pude modificar la formula de bva.

    gracias

  3. La función no hace distinción entre las tonalidades de blanco y canela, y lo toma todo como si fuera todos del mismo color

    • Gracias por su observación Luis, se añadió una mejora en el artículo explicando como usar COLOR en lugar de COLORINDEX a fin de obtener un mejor control de los colores.

  4. si realizo cambio de color de la celda de referencia posteriormente el valor no cambia en forma automática tengo que correr de nuevo la macro.

  5. HOLA, COMO DEFINO EL FORMATO DEL RESULTADO DE LA SUMA POR COLORES, LO NECESITO CON DECIMALES POR FAVOR

Deja una respuesta

Los campos requeridos estan marcados con *.