10 may 20224 Min. de lectura

Ejemplo de INDICE y AGREGAR

Vamos a ver un ejemplo de la función INDICE junto con AGREGAR.

Anteriormente, hemos visto un ejemplo de la función INDICE junto con COINCIDIR.

Bien, tenemos una serie de provincias con algunas de sus localidades, junto con un total.

En una celda vamos a poner una provincia, y, queremos ver las localidades que pertenecen a esa localidad.

En la celda E2, vamos a crear una lista desplegable con las tres provincias que tenemos, para ello, vamos a la pestaña de datos, hacemos clic en validación de datos, en la ventana que se abre, desplegamos permitir, y, elegimos lista, y, en origen, escribimos las tres provincias.

Seleccionamos, por ejemplo, Huelva.

Queremos ver las localidades de Huelva.

Pues, empezamos el ejercicio.

Tenemos que decir, que para encontrar las localidades de una provincia, debemos de saber su posición, para usarlo con la función INDICE, porque ya sabemos que a la función INDICE, hay que indicarle el número de fila y de columna.

Por ejemplo, si escribo el signo igual, seguido de la función INDICE, abro un paréntesis, selecciono el rango donde nos vamos a mover, que es el rango de localidades, que es el argumento array.

=INDICE(B2:B10

Punto y coma, el siguiente argumento es número de fila, vamos a poner que nos devuelva la fila 2 dentro del rango, que es la primera localidad de Huelva, y, como argumento número de columna, ponemos 1.

=INDICE(B2:B10;2;1)

Aceptamos, y, tenemos la primera localidad de Huelva.

Antes de arrastrar fijo el rango de las localidades.

=INDICE($B$2:$B$10;2;1)

Y ahora, debo de ir cambiando el argumento número de fila para que me devuelva la siguiente localidad de Huelva.

Pues, ahora, lo vamos a hacer con INDICE y AGREGAR.

La función AGREGAR va a ser el argumento número de fila de la función INDICE.

Lo vamos a hacer en una celda aparte, y, cuando veamos que funciona, usaremos la función INDICE.

Pues, para encontrar las posiciones, vamos a usar la función AGREGAR.

Escribimos el signo igual, seguido del nombre de la función, y, abrimos un paréntesis.

=AGREGAR(

Se abre una ventana donde debemos de seleccionar una operación.

En este caso, vamos a seleccionar K.ESIMO.MENOR, que nos va a devolver el valor más pequeño que le indiquemos, pues, la seleccionamos, que es el número 15, y, tabulamos.

Punto y coma, y, omitimos valores de error.

Punto y coma, y, seleccionamos el rango de donde vamos a sacar el valor más pequeño, que es provincias.

Punto y coma, ahora, debemos de rellenar el argumento K de K.ESIMO,MENOR, que es el número menor que queremos que nos devuelva, pues ponemos 1.

=AGREGAR(15;6;A2:A10;1)

Aceptamos.

Vemos que nos devuelve un error, cosa que es correcto, porque queremos que nos devuelva el primer valor menor dentro de un rango donde hay texto.

Si cambiamos A2:A10, por C2:C10, veremos cómo nos devuelve el primer valor menor, porque el rango es número.

Pero, tenemos que hacerlo con el rango de provincias, por lo que vamos a ello.

Vamos a realizar la función AGREGAR en una nueva celda, cuando veamos que funciona, lo insertaremos dentro de la función INDICE, como argumento número de fila.

Escribimos el signo igual, seguido de la función, y, abrimos un paréntesis.

Se abre la ventana donde tenemos que elegir qué operación vamos a realizar, que es la función K,ESIMO,MENOR, que es el número 15, pues la seleccionamos y pulsamos tabular.

Punto y coma, e, ignoramos valores, de error.

Punto y coma, abrimos un paréntesis, y, como argumento array de la función K.ESIMO.MEMOR, seleccionamos las provincias.

Escribimos el signo igual, y señalamos la celda con la provincia.

Cerramos el paréntesis, y, ponemos las referencias en absoluta.

Es decir, vamos a comparar cada provincia del rango de provincias con la provincia seleccionada.

=AGREGAR(15;6;($A$2:$A$10=$F$2)

Lo siguiente va a ser ver los resultados de la fórmula que acabamos de escribir.

Para ello, seleccionamos la formula.

Y pulsamos F9.

Vemos que aparecen VERDADEROS, y, FALSOS.

Aparece verdadero cuando la provincia, en este caso, es Huelva, y, falso cuando no lo es.

Lo siguiente va a ser deshacernos de los valores falsos.

Ya sabemos que verdadero es uno, y, falso es cero.

Pulsamos CTRL-Z para deshacer.

Bien, si divido los resultados obtenidos entre sí mismo, obtendremos un error cuando la división sea por cero, y, un uno cuando sea por 1.

=AGREGAR(15;6;($A$2:$A$10=$E$2)/($A$2:$A$10=$E$2)

Vamos a seleccionar la formula, y, volvemos a pulsar F9 para ver los resultados.

Podemos ver los resultados que hemos dicho anteriormente, error cuando la división es por cero, y, uno cuando la división es por uno.

Pero, lo que queremos es que cuando la división sea uno, nos devuelva la posición, en otras palabras, viendo el modelo queremos que en el primer uno aparezca 3, que es la posición de la primera localidad de Huelva, y, así con las siguientes.

¿Qué ocurre si multiplico los unos devuelto por el número de fila, pues, me va a devolver la fila, entonces, a la formula anterior lo vamos a multiplicar por la fila, para ello, usamos la función FILA.

=AGREGAR(15;6;($A$2:$A$10=$F$2)/($A$2:$A$10=$F$2)*(FILA(A2:A10))

Seleccionamos la fórmula que hemos seleccionado anteriormente, junto con la función FILA, y, pulsamos F9.

Y vemos en los valores verdaderos los números de filas que pertenecen a la provincia de Huelva.

Lo siguiente es el argumento K de la función K.ESIMO.MENOR, que es el valor más pequeño que queremos que nos devuelva.

Ponemos, de momento, 1.

=AGREGAR(15;6;($A$2:$A$10=$F$2)/($A$2:$A$10=$F$2)*(FILA(A2:A10));1)

Pues nos devuelve 3, que es primer elemento menor de los tres valores obtenidos, que son el 3, 6, y 10.

Si ahora arrastro, me devolverá el mismo valor, porque tenemos que cambiar el argumento K a dos para que nos devuelva el segundo elemento menor que es el seis.

Para solventar este problema, podemos crear un índice, pues, lo vamos a hacer, pero ¿Cuántos elementos debe de tener este índice?

Esto lo podemos de saber usando la función CONTAR.SI junto con la función matricial SECUENCIA, pueden hacer clic en en enlace de SECUENCIA para ver una explicación detallada de esta función matricial, que veremos en el modulo avanzado.

Voy a usar la función CONTAR.SI para contar las veces que aparece la provincia seleccionada en el rango de provincias.

=CONTAR.SI(A2:A10;F2)

En este caso, teniendo la provincia de Huelva seleccionada, nos devuelve 3.

Ahora, con la función SECUENCIA vamos a crear una matriz derramada, el primer argumento de esta función es el número de filas, pues este número va a ser el resultado de la función CONTAR.SI, y, como columna ponemos 1.

=SECUENCIA(CONTAR.SI(A2:A10;F2);1)

Vemos que nos devuelve una matriz desbordada con los números 1, 2, y, 3.