¿Cómo sumar por color en Excel? (2 métodos útiles)
Los 2 métodos principales para sumar por colores en Excel son los siguientes:
- Uso de la fórmula SUBTOTAL en Excel y función de filtro por color.
- Aplicar la fórmula GET.CELL definiendo el nombre en la pestaña de fórmulas y aplicando la fórmula SUMIF en Excel para resumir los valores por códigos de color.
Analicemos cada uno de ellos en detalle:
Puede descargar esta plantilla de Excel Suma por color aquí - Plantilla de Excel Suma por color# 1 - Suma por color usando la función subtotal
Para comprender el enfoque para calcular la suma de valores por colores de fondo, consideremos la siguiente tabla de datos que proporciona detalles de las cantidades en dólares estadounidenses por región y mes.
- Paso 1: Supongamos que nos gustaría resaltar aquellas celdas que tienen valores negativos para fines de indicación, esto se puede lograr aplicando formato condicional o resaltando las celdas manualmente como se muestra a continuación.
- Paso 2: Ahora, para lograr la suma de las celdas coloreadas en Excel, ingrese la fórmula para SUBTOTAL debajo de la tabla de datos. La sintaxis de la fórmula SUBTOTAL se muestra a continuación.
La fórmula que se ingresa para calcular la suma es
= SUBTOTAL (9, D2: D13)
Aquí el número '9' en el argumento núm_función se refiere a la funcionalidad de suma y el argumento de referencia se da como el rango de celdas a calcular. A continuación se muestra la captura de pantalla del mismo.
- Paso 3: Como se ve en la captura de pantalla anterior, se ha calculado una suma de la cantidad en USD. Para calcular las cantidades resaltadas en un color de fondo rojo claro. Aplique el filtro a la tabla de datos yendo a Datos y luego seleccionando un filtro.
- Paso 4: luego seleccione filtrar por color y elija el color de celda rojo claro en 'Filtrar por color de celda'. A continuación se muestra la captura de pantalla para describir mejor el filtro.
- Paso 5: Una vez que se haya aplicado el filtro de Excel, la tabla de datos se filtrará solo para celdas de fondo rojo claro y la fórmula de subtotal aplicada en la parte inferior de la tabla de datos mostrará la suma de las celdas de colores que se filtran como se muestra a continuación.
Como se muestra en la captura de pantalla anterior, el cálculo de la celda coloreada se logra en la celda E17, fórmula de subtotal.
# 2 - Suma por color usando la función Get.Cell
El segundo enfoque se explica para llegar a la suma de las celdas de color en Excel que se analiza en el siguiente ejemplo. Considere la tabla de datos para comprender mejor la metodología.
- Paso 1: Ahora resaltemos la lista de celdas en la columna Monto en USD que estamos dispuestos a llegar a la suma deseada de celdas coloreadas como se muestra a continuación.
- Paso 2: Como podemos ver en la captura de pantalla anterior, a diferencia del primer ejemplo, aquí tenemos varios colores. Por lo tanto, usaremos la fórmula = GET.CELL definiéndola dentro del cuadro de nombre y no usándola directamente en Excel.
- Paso 3: Ahora, una vez que aparezca el cuadro de diálogo para 'Definir nombre', ingrese el Nombre y la fórmula para GET.CELL en 'Refer to' como se muestra en la siguiente captura de pantalla.
Como se ve en la captura de pantalla anterior, el nombre ingresado para la función es 'CellColor' y la fórmula = GET.CELL (38, 'Example 2! $ D2) debe ingresarse en' Se refiere a '. Dentro de la fórmula, el 38 numérico se refiere a la información del código de celda y el segundo argumento es el número de celda D2 se refiere a la celda de referencia. Ahora haga clic en Aceptar.
- Paso 4: Ahora ingrese el Nombre de la función 'CellColor' en la celda al lado de la celda codificada por colores que se definió en el cuadro de diálogo como se explica en el paso 3.
Como se puede ver en la captura de pantalla anterior, se ingresa la función 'CellColor' que devuelve el código de color para el color de la celda de fondo.
Del mismo modo, la fórmula se arrastra para toda la columna.
- Paso 5: Ahora para llegar a la suma de los valores por colores en Excel, ingresaremos la fórmula SUMIF. La sintaxis de la fórmula SUMIF es la siguiente: -
Como se puede ver en la captura de pantalla anterior, los siguientes argumentos se ingresan en la fórmula SUMIF: -
- El argumento de rango se ingresa para el rango de celdas E2: E13
- Los criterios se ingresan como G2, cuyos valores resumidos deben recuperarse.
- El rango de celdas se ingresa para ser D2: D13
La fórmula SUMIF se arrastra hacia abajo para todos los números de código de color para los que se deben sumar valores.
Cosas para recordar
- Al usar el enfoque por fórmula SUBTOTAL, esta funcionalidad permite a los usuarios filtrar solo por un color de filtro a la vez. Además, esta funcionalidad se puede utilizar para agregar solo una columna de valores por colores de filtro. Si hay más de una columna con diferentes colores por filas en la columna diferente, el SUBTOTAL solo puede mostrar el resultado correcto para un filtro por color en una columna específica.
- La fórmula GET.CELL en combinación con el enfoque SUMIF elimina esta limitación, ya que esta funcionalidad podría usarse para resumir por colores para múltiples códigos de color en el fondo de la celda.
