Resumen
Para configurar COUNTIFS (o COUNTIF) con un rango variable, puede usar la función OFFSET. En el ejemplo que se muestra, la fórmula en B11 es:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Esta fórmula cuenta las celdas que no están en blanco en un rango que comienza en B5 y termina 2 filas por encima de la celda donde reside la fórmula. La misma fórmula se copia y pega 2 filas debajo de la última entrada en los datos como se muestra.
Explicación
En el ejemplo que se muestra, la fórmula en B11 es:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Trabajando desde adentro hacia afuera, el trabajo de configurar un rango variable se realiza mediante la función OFFSET aquí:
OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range
OFFSET tiene cinco argumentos y se configura así:
- referencia = B $ 5, comience en la celda B5, fila bloqueada
- filas = 0, compensar cero filas desde la celda inicial
- cols = 0, compensar cero columnas de celda inicial
- altura = FILA () - FILA (B $ 5) -1 = 5 filas de altura
- ancho = 1 columna de ancho
Para calcular la altura del rango en filas, usamos la función FILA como esta:
ROW()-ROW(B$5)-1 // work out height
Dado que ROW () devuelve el número de fila de la celda "actual" (es decir, la celda en la que vive la fórmula), podemos simplificar así:
=ROW()-ROW(B$5)-1 =11-5-1 =5
Con la configuración anterior, OFFSET devuelve el rango B5: B9 directamente a COUNTIFS:
=COUNTIFS(B5:B9,"") // returns 4
Observe que la referencia a B $ 5 en la fórmula anterior es una referencia mixta, con la columna relativa y la fila bloqueada. Esto permite que la fórmula se copie en otra columna y siga funcionando. Por ejemplo, una vez copiada a C12, la fórmula es:
=COUNTIFS(OFFSET(C$5,0,0,ROW()-ROW(C$5)-1,1),"")
Nota: OFFSET es una función volátil y puede causar problemas de rendimiento en hojas de trabajo grandes o complejas.
Con INDIRECTO y DIRECCION
Otro enfoque es utilizar una fórmula basada en las funciones INDIRECTO y DIRECCIÓN. En este caso, ensamblamos un rango como texto, luego usamos INDIRECTO para evaluar el texto como referencia. La fórmula en B11 sería:
=COUNTIFS(INDIRECT(ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())),"")
La función ADDRESS se usa para construir un rango como este:
ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())
En la primera instancia de ADDRESS, proporcionamos el número de fila como el valor codificado 5 y proporcionamos el número de columna con la función COLUMN:
=ADDRESS(5,COLUMN()) // returns "$B$5"
En el segundo caso, proporcionamos el número de fila "actual" menos 2, y la columna actual con la función COLUMNA:
=ADDRESS(ROW()-2,COLUMN()) // returns "$B$9"
Después de concatenar estos dos valores juntos, tenemos:
"$B$5:$B$9" // as text
Tenga en cuenta que esta es una cadena de texto. Para convertir a una referencia válida, necesitamos usar INDIRECTO:
=INDIRECT("$B$5:$B$9") // returns $B$5:$B$9 as valid range
Finalmente, la fórmula en B11 se convierte en:
=COUNTIFS($B$5:$B$9,"") // returns 4
Nota: INDIRECTO es una función volátil y puede causar problemas de rendimiento en hojas de trabajo grandes o complejas.
