Cómo usar un rango dinámico en Excel con COUNTIF e INDIRECTO
Qué entender
- La función INDIRECTA cambia el rango de referencias de celda en una fórmula sin modificar la fórmula.
- Use INDIRECTO como razonamiento para COUNTIF para hacer un rango dinámico de celdas que cumplan con los criterios concretados.
- Los criterios los establece la función INDIRECTO y unicamente se cuentan las celdas que cumplen los criterios.
Este producto enseña de qué forma emplear la función INDIRECTO en fórmulas de Excel para cambiar el rango de referencias de celdas usadas en una fórmula sin la necesidad de modificar la fórmula en sí. Esto garantiza que se usen exactamente las mismas celdas, aun en el momento en que cambie el papel de cálculo. La información se aplica a Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel para Mac y Excel En línea.
Use un rango dinámico con la fórmula COUNTIF – INDIRECT
La función INDIRECTO se puede emplear con múltiples funcionalidades que admiten una referencia de celda como razonamiento, como las funcionalidades SUMA y CONTAR.SI.
La utilización de INDIRECTO como razonamiento para CONTAR.SI crea un rango dinámico de referencias de celdas que la función puede contar si los valores de las celdas cumplen con un método. Lo realiza transformando los datos de artículo, en ocasiones llamados cadenas de artículo, en una referencia de celda.
Este caso se apoya en los datos que se detallan en la imagen de arriba. La fórmula COUNTIF – INDIRECT construída en el tutorial es:
=COUNTIF(INDIRECT(E1&»:»&E2),»>diez»)
género de datos = «código»>
En esta fórmula, el razonamiento de la función INDIRECTO tiene dentro:
- La celda se refiere a E1 y E2, que poseen los datos de artículo D1 y D6.
- El operador de rango, los 2 puntos (:) entre comillas dobles (» «) que transforma los 2 puntos en una cadena de artículo.
- 2 símbolos de unión (&) que se usan para concatenar o juntar los 2 puntos con las referencias de celda E1 y E2.
El resultado es que INDIRECTO transforma la cadena de artículo D1:D6 en una referencia de celda y la pasa a la función CONTAR.SI a fin de que se cuente si las celdas a las que se se refiere son mayores que diez.
La función INDIRECTA admite cualquier entrada de artículo. Estas tienen la posibilidad de ser celdas en el papel de cálculo que poseen artículo o referencias de celdas de artículo que se ingresan de manera directa en la función.
Cambiar dinámicamente el rango de la fórmula
Recuerde, el propósito es hacer una fórmula con un rango dinámico. Un rango dinámico se puede cambiar sin modificar la fórmula en sí.
Al cambiar los datos de artículo situados en las celdas E1 y E2, de D1 y D6 a D3 y D7, el rango totalizado por la función se puede cambiar de manera fácil de D1:D6 a D3:D7. Esto suprime la necesidad de modificar de manera directa la fórmula en la celda G1.
La función CONTAR.SI en este caso solo cuenta las celdas que poseen números si son mayores que diez. Si bien 4 de las cinco celdas en el rango de D1:D6 poseen datos, solo tres celdas poseen números. La función ignora las celdas que están en blanco o que poseen datos de artículo.
Contar artículo con COUNTIF
La función CONTAR.SI no se restringe a contar datos numéricos. Asimismo cuenta las celdas que poseen artículo verificando si encajan con un artículo preciso.
Para esto, se ingresa la próxima fórmula en la celda G2:
=COUNTIF(INDIRECT(E1&»:»&E2),»two»)
género de datos = «código»>
En esta fórmula, la función INDIRECTA se refiere a las celdas B1 a B6. La función CONTAR.SI suma el número de celdas que tienen el valor de artículo 2 en ellos.
En un caso así, el resultado es 1.
COUNTA, COUNTBLANK y también INDIRECTO
Otras 2 funcionalidades de recuento de Excel son COUNTA, que cuenta las celdas que poseen cualquier género de datos mientras que ignora solo las celdas en blanco o vacías, y COUNTBLANK, que cuenta solo las celdas en blanco o vacías en un rango.
Ya que las dos funcionalidades tienen una sintaxis afín a la función CONTAR.SI, se tienen la posibilidad de substituir en el ejemplo previo con INDIRECTO para hacer las próximas fórmulas:
=COUNTA(INDIRECT(E1&»:»&E2))
=COUNTBLANK(INDIRECT(E1&»:»&E2)
Para el rango D1:D6, CONTARA devuelve una contestación de 4, en tanto que 4 de las cinco celdas poseen datos. COUNTBLANK devuelve una contestación de 1 puesto que solo hay una celda en blanco en el rango.
¿Por qué razón utilizar una función INDIRECTA?
El beneficio de utilizar la función INDIRECTO en todas y cada una estas fórmulas es que se tienen la posibilidad de insertar novedosas celdas en cualquier una parte del rango.
El rango cambia dinámicamente en las distintas funcionalidades y los desenlaces se actualizan consecuentemente.
Sin la función INDIRECTO, sería preciso modificar cada función para integrar las 7 celdas, incluyendo la novedosa.
Los resultados positivos de la función INDIRECTA son que los valores de artículo se tienen la posibilidad de insertar como referencias de celda y que actualiza dinámicamente los rangos toda vez que cambia el papel de cálculo.
Esto causa que el cuidado general de el papel de cálculo sea considerablemente más simple, en especial para hojas de cálculo muy enormes.
