CONTAR CELDAS POR COLOR EN EXCEL CON TYPESCRIPT

Hola a todos:

Después de las últimas semanas publicando contenidos de Power Query, hoy he querido dar un giro y retomar las publicaciones en Typescript. En concreto hoy intentaré mostrar una forma de contar celdas por colores.

Esto que en VBA resulta super sencillo, tanto en una UDF como en un procedimiento Sub, en Typescritp, es complicado. Para empezar no se puede programar una «Custom Function» o función definida para contar el color dado que no es posible recuperar el color con ninguno de los métodos disponibles.

Ante este problema he intentado hacerlo con un script y apuntando a funciones como getFormat(), get.Fill() y getColor haciendo referencia a un rango determinado.

Pues bien, antes de nada indicar que, salvo error mío, no es posible recuperar en un array el formato de color interior de una celda. El método sí es factible con getValues (tenéis otros ejemplos en ExcelSignum), pero no con getColor. ¿La razón?, la desconozco, pero yo no lo he conseguido.

Y esto supone un grave problema, porque de ser posible permitiría cargar previamente todos los formatos del rango para posteriormente en un loop y con una condición contar el número de celdas con un color determinado. Sería muy rápido y eficiente.

Pero como no se puede hacer (de momento, espero), he ideado otra técnica para hacerlo pero obteniendo el color en el bucle, lo que hace que el proceso se ralentice bastante y encima sea necesario que utilizar un loop anidado para recorrer filas y columnas. Por otra parte indicar que la instrucción for-next no se puede usar con un string.

Teniendo en cuenta todo esto y después de las pruebas realizadas paso a montar el ejemplo:

Imaginad este rango con colores:

Y queremos contar cuantas celdas hay de color amarillo.

Para ello, os propongo el siguiente código:

async function main(workbook: ExcelScript.Workbook) {
    let MiHoja = workbook.getWorksheet("Hoja1");
    let MiRango = MiHoja.getRange("A1:B20");
    let Fin = MiRango.getRowCount();
    let Final = MiRango.getColumnCount();
    //capturamos formato del color de la celda I2
    let MiColor = MiHoja.getRange("D2").getFormat().getFill().getColor();
    //creamos contadores
    let nFila = 0;
    let nColumn = 0;
    let nItem = 0;
    // Creamos bucle anidado para procesar todas las filas de cada columna
    for (let i = 0; i < Final; i++) {
        for (let j = -1; j < Fin; j++) {
            //Obtenermos formato de color de cada celda      
            let MiRangoColor = MiHoja.getRangeByIndexes(nFila, nColumn, 1, 1).getFormat().getFill().getColor();
            // Si el formato es igual al de la celda D2 entonces contamos
            if (MiRangoColor == MiColor) {
                nItem = nItem + 1
            }
            nFila = nFila + 1
        }
        nFila = 0
        nColumn = nColumn + 1
    }
    //Pasamos el resultado a la celda D3
    MiHoja.getCell(2, 3).setValues([
        [nItem]
    ])
}

Donde el resultado que obtendremos es de 3

Como podéis observar en el código la rapidez del script es bastante lenta, pero se debe a lo que he comentado anteriormente. Por otra parte, si alguien de vosotros sabe o conoce otra técnica que sea más eficiente, por favor que me lo indique.

Por el momento, informaré en los foros sobre esta limitación y la posibilidad de que se implemente una solución.

Espero que en la medida de lo posible os pueda ser de utilidad.

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.



¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies