El artículo Cómo sumar fácilmente valores por color de fondo de celda en Excel le muestra una manera fácil de combinar funciones integradas para contar o sumar valores en función de los colores de relleno. Esta técnica no requiere ningún conocimiento particular, pero es limitada. No puede usarlo para evaluar celdas individuales en una fila; el filtrado es una característica exclusiva de las columnas. Además, Excel no proporciona ninguna característica o función para evaluar directamente las celdas por su color de relleno. En este artículo, te mostraré cómo. No es una solución clara y, sin un poco de conocimiento interno, probablemente no lo resolverá por su cuenta. Lo que hay que recordar es que estamos contando células, no registros.

VER: Office 365: una guía para líderes empresariales y tecnológicos (PDF gratuito) (República Tecnológica)

Uso Microsoft 365 en un sistema Windows 10 de 64 bits, pero puede usar versiones anteriores. Puede trabajar con sus propios datos o descargar el archivo de demostración .xlsx. (Incluyo el formato de la versión del menú, pero es posible que no funcione de manera confiable). Esta solución no es adecuada para la edición del navegador. Además, esta técnica funciona con colores de relleno aplicados directamente oa través de estilos; no funcionará con formato condicional; esta magia requiere un pase de Hail-Mary de nivel Stephen Hawking.

Este artículo se basa en una pregunta publicada por Vic Micallef.

Índice
  • Los datos y el problema
  • Cómo implementar GET.CELL
  • Cómo usar el código de color
  • Cómo contar códigos de color
  • variantes
  • Límites
  • Manténganse al tanto
  • Los datos y el problema

    Si eres una persona de base de datos, piensas en campos o columnas. Las filas son importantes al normalizar tablas en una base de datos relacional y luego al crear informes, pero está analizando columnas (principalmente). Las hojas son un poco diferentes y, a veces, necesita evaluar valores en columnas de una sola fila. En la mayoría de los casos, Excel maneja bien esta situación. Desafortunadamente, no hay una función o funcionalidad integrada (que yo sepa) que facilite la evaluación de las celdas en función de su color de relleno, en una fila. Antes de decirlo, no, reconstruir sus datos no siempre es una opción.

    Ahora veamos una hoja simple, Figura A, donde las celdas aleatorias tienen un color de relleno azul claro. Para nuestros propósitos, no importa lo que represente el color, pero es importante tener en cuenta que el color se aplicó directamente (y no condicionalmente). Para contar el número en una columna, podríamos usar el filtrado y la función SUBTOTAL(). Debido a que queremos contar las celdas llenas en cada fila, tendremos que trabajar un poco más (pero al menos no es ciencia espacial).

    Figura A

    Si buscas sobre este tema encontrarás soluciones que usan GET.CELL para hacer lo mismo que filtrar, pero ojo. Estas soluciones no cuentan celdas individuales en una fila (o registro). Por ejemplo, si cuenta el color de relleno azul en nuestra hoja de demostración, el recuento de la fila 3 es 1. Usaremos GET.CELL para crear una matriz. A continuación, usaremos COUNTIF() para devolver el recuento de un color de relleno específico para cada fila.

    Este siguiente paso es muy importante; no te lo saltes Si está utilizando el formato .xlsx, guarde el libro de trabajo como un archivo habilitado para macros.

    Cómo implementar GET.CELL

    CET.CELL es una función antigua que viene con limitaciones. Más importante aún, no puede hacer referencia a él directamente; en otras palabras, no puede escribir =GET.CELL(38,C3) y devolver el código de color de la celda C3. En su lugar, aplica la función a un rango con nombre. En segundo lugar, la forma en que lo uso solo cuenta las celdas adyacentes en una fila. Si inserta una columna entre el conjunto de datos y la matriz que creará más tarde, la función aún funciona, pero es una columna menos. Además, no puede usarlo para contar celdas llenas (individualmente) en una columna. Es una solución muy específica.

    Ahora pongamos en juego GET.CELL. Haga lo siguiente para crear un rango con nombre que se puede usar para devolver el color de relleno de cualquier celda:

    1. Haga clic en la pestaña Fórmulas, luego haga clic en Definir nombre en el grupo Nombres definidos y seleccione Definir nombre en la lista desplegable.
    2. En el cuadro de diálogo resultante, ingrese un nombre para el rango, como ColorCode.
    3. En el control Se refiere a, ingrese la siguiente expresión: =GET.CELL(38,Count!C3), donde Count es el nombre de la hoja y C3 es la celda ancla del conjunto de datos. (Ver Figura B).
    4. Haga clic en Aceptar.

    Figura B

    En este punto, nada ha cambiado visiblemente. Es hora de usar el rango ColorCode para devolver los códigos de las celdas llenas.

    Cómo usar el código de color

    Actualmente, no conoce el código de color de ninguna celda de nuestro conjunto de datos. Para remediar esto, crearemos una pequeña matriz, directamente adyacente al conjunto de datos. (Si deja una columna vacía entre el conjunto de datos y la matriz, esta solución arrojará resultados incorrectos).

    Para crear la matriz, ingrese la siguiente expresión en G3:

    =Código de color

    que devuelve el valor 37: este es el código de color de relleno para C3. Copie esta expresión en H3:J3. Como puedes ver en Figura C, ColorCode devuelve el color de relleno de las celdas C3, D3, E3 y F3. Esto es posible porque la referencia que utilizó al crear el rango de ColorCode, C3, es relativa. Si hiciera esta referencia absoluta, $C$3, siempre devolvería 37. Seleccione G3:J3 y copie las cuatro expresiones en las filas restantes, completando la matriz que se muestra en Figura D.

    Figura C

    Figura D

    La matriz no es dinámica; consulte la sección Límites a continuación. Todavía no sabemos la cantidad de celdas llenas de azul en ninguna fila. Llegaremos a eso a continuación.

    Cómo contar códigos de color

    Tal como está, la matriz devuelve los códigos de color de relleno para cada celda del conjunto de datos. Usando COUNTIF() podemos contar fácilmente las celdas azules en cada fila. Para hacer esto, ingrese =CONTAR.SI(G3:J3,37) en K3 y copia en K14. Los valores de la columna K, mostrados en Figura E, son un recuento del número de celdas llenas de azul en la fila correspondiente. Específicamente, 37 identifica las celdas llenas de azul y la función solo cuenta las celdas en la fila correspondiente. Solo hay una celda en la fila 3 donde el color de relleno es azul, 37. En la fila 2 hay dos celdas, y así sucesivamente.

    Figura E

    Probablemente desee ocultar u oscurecer la matriz en G3:J3. Generalmente recomiendo no enmascarar las celdas porque son fáciles de olvidar, lo que hace que la hoja sea más difícil de mantener. Si está bien, reduzca el ancho al mínimo como se muestra en Figura F y aplique el color de relleno posterior.

    Figura F

    variantes

    Puede usar GET.CELL para devolver mucha información. El valor que usamos, 38, devuelve el color de relleno de una celda. Por ejemplo, para sumar los valores, usaría 5 en lugar de 38 al crear el rango. Luego usará SUMAR.SI() en lugar de CONTAR.SI(). Una búsqueda de GET.CELL mostrará una lista (Microsoft ya no mantiene una que yo pudiera encontrar). También puede cambiar el código de relleno contado en la función COUNTIF().

    VER: Cómo agregar una lista desplegable a una celda de Excel (República Tecnológica)

    Límites

    Como mencioné, hay límites, varios. La matriz debe estar adyacente al conjunto de datos para que funcione correctamente. No funcionará contar celdas llenas en columnas. Dado que GET.CELL ya no es compatible, puede dejar de funcionar en cualquier momento. Los cambios no son dinámicos. Si cambia un color de relleno o actualiza ColorCode, la matriz no actualizar automáticamente. En ambos casos, debe volver a ingresar ColorCode, lo cual es un inconveniente. Por esta razón, no estoy convencido de que esta sea la solución más efectiva; Si tiene una idea mejor, comparta sus pensamientos en la sección de comentarios a continuación.

    Manténganse al tanto

    Debido a que esta solución es tan laboriosa, le mostraré una macro en un artículo de seguimiento.