Agregar por Color en Excel (Fórmula y VBA)

Excel tiene algunas características en realidad geniales, pero no tiene una función que pueda sumar los títulos de las celdas según el color de la celda.

Por ejemplo, tengo el conjunto de datos como se muestra a continuación y quiero obtener la suma de todas las celdas de color naranja y amarillo.

Desafortunadamente, no hay una función integrada para hacer esto.

¡Pero nunca digas nunca!

En este tutorial, te mostraré tres técnicas simples que puedes usar para: suma por color en excel†

¡Vamos a sumergirnos!

SUMA celdas por color usando filtro y SUBTOTAL

Empecemos por lo más claro.

A continuación, tengo un conjunto de datos donde tengo los nombres de los empleados y sus números de ventas.

Y en este conjunto de datos, quiero obtener la suma de todas las celdas de color amarillo y naranja.

Aunque no hay una función integrada en Excel para sumar títulos según el color de la celda, existe una alternativa claro que le permite filtrar las celdas según el color de la celda.

Para este método, ingrese la sucesivo fórmula en la celda B17 (o cualquier celda en la misma columna debajo del conjunto de datos de la celda coloreada).

=SUBTOTAL(9,B2:B15)

En la fórmula SUBTOTAL mencionado, utilicé 9 como primer argumento, que le dice a la función que quiero obtener la suma del rango donado como segundo argumento.

Pero, ¿por qué no usar simplemente la fórmula SUM?

Esto se debe a que cuando tengo la fórmula SUBTOTAL y filtro las celdas para mostrar solo aquellas celdas que tienen un color específico, la fórmula SUBTOTAL solo me muestra la suma de las celdas visibles (poco que la fórmula SUMA no puede hacer).

Entonces, una vez que tenga la fórmula SUBTOTAL, siga los pasos a continuación para obtener la SUMA según el color de la celda:

1. Aspirar una celda en el conjunto de datos
2. Haga clic en la pestaña Datos

3. En el peña Ordenar y filtrar, haga clic en el icono Filtrar. Esto aplicará un filtro al conjunto de datos y podrá ver el icono de filtro en los encabezados.

4. En la celda Encabezado de ventas, haga clic en el icono Filtro

5. Ir a la opción Filtrar por color
6. Seleccione el color por el que desea filtrar el conjunto de datos.

Una vez que haga esto, notará que el resultado del subtotal cambia y ahora le da solo la suma de las celdas que son visibles (estas son solo aquellas celdas con el color con el que filtró el conjunto de datos).

Del mismo modo, si filtra por un color diferente en el conjunto de datos (por ejemplo, naranja en puesto de amarillo), la función SUBTOTAL se ajustará en consecuencia y le dará la suma de todas las celdas con color naranja.

Consejo profesional: El hato de teclado para aplicar un filtro a un conjunto de datos es Control + Shift + L (mantenga presionadas las teclas Control y Shift, luego presione L). Si estás en Mac, usa Comando + Shift + L

SUM celdas por color con VBA

Dije que no hay una fórmula integrada en Excel para sumar según el valía del color de la celda. Sin incautación, puede crear su propia fórmula para esto con VBA.

Con VBA, puede crear una función personalizada que puede proseguir en el backend y luego usarla como cualquier otra función regular en la hoja de trabajo.

A continuación se muestra el código VBA que crea esa función personalizada que le permite anexar por color en Excel.

'Code created by Sumit Bansal from https://trumpexcel.com/
'This VBA code created a function that can be used to sum cells based on color
Function SumByColor(SumRange As Range, SumColor As Range)
Dim SumColorValue As Integer
Dim TotalSum As Long
SumColorValue = SumColor.Interior.ColorIndex
Set rCell = SumRange
For Each rCell In SumRange
If rCell.Interior.ColorIndex = SumColorValue Then
TotalSum = TotalSum + rCell.Value
End If
Next rCell
SumByColor = TotalSum
End Function

Para usar esta función personalizada de VBA, primero debe copiar y pegar este código en el backend del editor de VB.

Cuando haya terminado, puede usar esta función en la hoja de trabajo.

A continuación se muestran los pasos para anexar este código al editor de VB.

1. Haga clic en la pestaña Desarrollador en la cinta (si la pestaña Desarrollador no está visible, haga clic aquí para ver cómo obtenerla)
2. Haga clic en el icono de Visual Basic. Esto abrirá el editor Visual Basic de Excel.

3. Haga clic en la opción Insertar en el menú
4. Haga clic en Módulos. Esto insertará un nuevo módulo y podrá verlo en el explorador de proyectos (un panel a la izquierda que muestra todos los objetos). Si no ve el panel Explorador de proyectos, haga clic en Ver y luego haga clic en Explorador de proyectos

5. Copie el código VBA mencionado y péguelo en la ventana de código del módulo recién insertado

6. Cierra el editor de VB

Ahora que tiene el código en el backend de Excel, puede usar la función que creamos (SumByColor) en la hoja de trabajo.

Para que esta función funcione, necesito una celda en la hoja de trabajo que contenga el mismo color para el que quiero obtener la suma.

En nuestro ejemplo, lo hice con las celdas D2 y D3, donde D2 es el color amarillo y D3 es el color naranja.

Ahora puedo usar la sucesivo fórmula en estas celdas:

=SumByColor($B$2:$B$15,D2)

La fórmula mencionado tiene dos argumentos:

• El rango de celdas con el color que quiero anexar.
• Remisión a una celda que contiene el color (para que la fórmula pueda designar el índice de color y usarlo como condición para anexar los títulos)

Tenga en cuenta que la fórmula es dinámica y se actualiza automáticamente si realiza cambios en el conjunto de datos (como cambiar un valía o aplicar/eliminar color en algunas celdas). Y si encuentra que la fórmula no se actualiza, presione la tecla F9 y se actualizará.

Entregado que usamos un código VBA en el ejemplar de trabajo, debe protegerse como un ejemplar de trabajo con macros (con extensión .XLSM).

Consejo profesional: Si anexar celdas en función de su color de fondo es poco que debe hacer con suficiente frecuencia, le sugiero que copie y pegue este código VBA de fórmula personalizada en el ejemplar de macros personal. De esta modo, eso estaría apto en todos sus libros de trabajo en su sistema.

SUM Celdas por color con GET.CELL

El postrer método que quiero mostrarte implica una fórmula oculta de Excel (que no mucha parentela conoce).

Este método usa la función GET.CELL, que nos puede dar el valía del índice de color de las células teñidas.

Y una vez que tenemos el valía del índice de color de cada celda, podemos usar una fórmula de suma simple para obtener solo la suma de las celdas que tienen un color específico.

No es tan elegante como la función personalizada de VBA que mencioné anteriormente, pero si no desea usar VBA, entonces podría ser la opción adecuada para usted.

GET.CELL es una antigua función de Macro 4 guardada en Excel por razones de compatibilidad, pero no encontrará muchos detalles al respecto (porque rara vez se usa).

A continuación, tengo un conjunto de datos en el que tengo celdas coloreadas que quiero sumar.

Para que esta técnica funcione, primero debemos crear un rango con nombre que use la función GET.CELL para devolver el valía de color de una celda coloreada.

Estos son los pasos para hacer esto:

1. Haga clic en la pestaña Fórmulas en la cinta
2. En el peña Nombres definidos, haga clic en ‘Administrador de nombres’

3. En el cuadro de diálogo Administrador de nombres, haga clic en Nuevo

4. En el cuadro de diálogo Nuevo nombre, ingrese el nombre: SumColor. en

5. En el campo Se refiere a, ingrese la sucesivo fórmula: =GET.CELL(38,$B2)

6. Haga clic en Aceptar
7. Cerrar el cuadro de diálogo Administrador de nombres

Los pasos anteriores han creado un rango con nombre que ahora podemos usar en el ejemplar de trabajo.

Nota: La función GET.CELL toma dos argumentos, el primero es un número que le dice a la función qué información necesitamos, y el segundo es la narración de celda de esa celda. En este caso, tengo 38 como primer argumento, lo que nos daría el valía del índice de color de la celda referenciada.

Ahora el segundo paso es obtener los títulos del índice de color de todos los colores de la columna B.

A continuación se muestran los pasos para hacer esto:

1. En la celda C1, ingrese el encabezado – Índice de color (o como quiera llamarlo)

2. En la celda C2, ingrese la sucesivo fórmula: =SumColor

3. Aplique la misma fórmula a todas las celdas de la columna C (puede usar el compensador de relleno o simplemente copiar y pegar la celda C2)

Los pasos anteriores le darán un valía que representaría el índice de color de la celda en la columna B (la celda de la izquierda).

SumColor es el rango con nombre que creamos y usa la función GET.CELL para obtener el valía del índice de color de la celda de la izquierda. Puede usar esta fórmula en cualquier columna, pero siempre debe comenzar en la segunda celda de esa columna. Por ejemplo, en puesto de la columna C, podría tener esto en la columna H o J, pero desde las segundas celdas de estas columnas.

Ahora que tenemos un número único para cada color, podemos usarlo para obtener la SUMA de celdas según su color.

A continuación se muestran los pasos para hacer esto:

1. En las celdas E2 y E3, colorea las celdas para las que quieres obtener la suma. En mi caso tengo color amarillo en la celda E2 y naranja en la celda E3

2. En la celda F2, ingrese la sucesivo fórmula: =SUMIF(C2:C15,SumColor,B2:B15)

3. Copie la celda y péguela en la celda F3 (esto además puede copiar la fórmula y ajustar las referencias).

Los pasos anteriores le darán la suma de todas las celdas con el color en la columna de ventas adyacente F.

Usamos una fórmula SUMAR.SI, que suma todos los títulos en la columna B, si el índice de color de la celda de la izquierda (en la columna F) es el mismo que el de la columna C.

Si adecuadamente esta es ciertamente una forma un poco más larga de sumar celdas por color (en comparación con SUBTOTAL o VBA), hace el trabajo y solo requiere que realice esta configuración una vez.

Tenga en cuenta que aunque la fórmula es dinámica, si realiza cambios (como cambiar las celdas de color en la columna B o eliminar el color), es posible que el cambio no se refleje inmediatamente en la fórmula SUMAR.SI. Una alternativa simple a esto sería ir a la celda con la fórmula, hacer clic en la tecla F2 para ir al aeropuerto y luego presionar la tecla Intro. Esto obligaría a la fórmula a retornar a calcular y obtendrá el resultado actualizado.

Entonces, estos son tres métodos que puede usar para anexar por color en Excel. Si adecuadamente SUBTOTAL es suficiente simple y directo, personalmente me gusta más el método VBA.

¡Espero que hayas antitético útil este tutorial!