Vamos a ver un ejemplo, donde vamos a seleccionar un encabezado, y, queremos mostrar los valores únicos, y, ordenados de ese encabezado junto con los totales.
Tenemos el siguiente modelo:
Lo primero que vamos a hacer es crear una lista desplegable con los encabezados, excepto, cantidad, precio, y, total.
Nos colocamos, por ejemplo, en la celda K1, vamos a la pestaña datos, dentro del grupo herramientas de datos, hacemos clic en validación de datos.
Desplegamos permitir, y, seleccionamos lista.
Hacemos clic en el siguiente icono.
Seleccionamos los datos, y, hacemos clic en el siguiente icono.
Hacemos clic en aceptar.
Si vamos a la celda K1, y, desplegamos veremos los encabezados.
Ahora, empieza nuestro ejercicio.
Vamos a seleccionar un encabezado, y, debe de devolvernos una matriz con los elementos de dicho encabezado. Lo primero que debemos de saber es que posición ocupa el encabezado que buscamos, tenemos cinco encabezados, pues, vamos a usar la función COINCIDIR, con una coincidencia exacta.
Escribimos el signo igual, seguido del nombre de la función, y, abrimos un paréntesis.
=COINCIDIR(K1;A1:H1;0)
Por ejemplo, si elijo el encabezado producto, me devuelve la posición 5.
¿Qué tenemos que hacer?
Bien, debemos de empezar la lista que queremos a partir de la fila 2, porque la fila 1, es el encabezado, y, esa no la queremos.
Pues, ya sabemos la fila y la columna donde tenemos que empezar, es decir, fila numero 2, y, columna numero 1.
Tenemos una función llamada DIRECCION, con la que obtenemos la dirección de una celda dentro de nuestra hoja de cálculo.
Esta función tiene varios argumentos, pero nosotros solo vamos a necesitar los dos primeros argumentos, que son fila y columna.
Por ejemplo, si en una celda, escribo el signo igual, seguido de la función DIRECCION, abro un paréntesis, como argumento fila, pongo 2, y, como argumento columna pongo el resultado de la función COINCIDIR, obtengo dicha dirección, como referencia absoluta.
=DIRECCION(2;L1)
Si cambiamos el encabezado veremos como vemos dicha referencia.
Para este ejemplo, vamos a usar la función FILTRAR.
El primer argumento es array, es decir, la matriz con la que vamos a trabajar.
Es aquí donde debemos de indicar la columna correcta, según, el encabezado que hayamos seleccionado.
Sabemos que para el argumento array, si vamos a filtrar por una columna determinada, debemos de especificar donde empieza y donde acaba, por ejemplo, A1:A100.
Escribimos el signo igual seguido de la función filtrar, y, abrimos un paréntesis.
=FILTRAR(
Vamos con el argumento array, donde tenemos que usar la función DIRECCION para que sea dinámico.
El comienzo ya lo hemos visto anteriormente, que es DIRECCION(2;M1).
=FILTRAR(DIRECCION(2;M1)
Pero, debemos de especificar un rango.
Ponemos los dos puntos.
=FILTRAR(DIRECCION(2;M1):
Volvemos a poner la función DIRECCION.
=FILTRAR(DIRECCION(2;M1):DIRECCION(
Pero, hasta que fila debe de ir, es decir, cual es la última fila ocupada, pues podemos usar la función CONTARA, por ejemplo, vamos a aplicar esta función a la columna A, ya que todas las columnas tienen las mismas filas, y, volvemos a especificar la columna donde tenemos el resultado de la función COINCIDIR.
=FILTRAR(INDIRECTO(DIRECCION(2;M1)):INDIRECTO(DIRECCION(CONTARA(A:A);M1))
El siguiente argumento de la función filtrar, es el criterio, es decir, el argumento include, o, incluir, pues ponemos verdadero, y, nos devolverá todas las filas.
=FILTRAR(DIRECCION(2;M1):DIRECCION(CONTARA(A:A);M1);VERDADERO)
Aceptamos.
Obtenemos un error.
Este error es porque estas referencias obtenidas con la función DIRECCION están en formato de texto, por lo que no la podemos usar.
Pero tenemos otra función, que se usa mucho con la función DIRECCION, que es la función INDIRECTO.
Esta función nos devuelve la referencia de una cadena de texto, es decir, podíamos decir que nos valida la referencia y la podemos usar.
Pues envolvemos la función DIRECCION dentro de INDIRECTO.
=FILTRAR(INDIRECTO(DIRECCION(2;M1)):INDIRECTO(DIRECCION(CONTARA(A:A);M1));VERDADERO)
Aceptamos, y, ya hemos rescatado nuestra columna.
Pero, los valores se repiten, por lo que vamos a usar la función UNICOS después del signo igual.
=UNICOS(FILTRAR(INDIRECTO(DIRECCION(2;M1)):INDIRECTO(DIRECCION(CONTARA(A:A);M1));VERDADERO))
Por último, ordenamos con los valores predeterminados.
=ORDENAR(UNICOS(FILTRAR(INDIRECTO(DIRECCION(2;M1)):INDIRECTO(DIRECCION(CONTARA(A:A);M1));VERDADERO)))
Probemos.
Si seleccionamos un encabezado podemos ver los valores únicos de dicho encabezado.
Lo siguiente es calcular los totales para esos elementos obtenidos.
Para ello, vamos a usar la función SUMAR.SI.
El primer argumento de esta función es rango de criterios, pues el rango de criterios es la función filtrar, que acabamos de crear.
=SUMAR.SI(INDIRECTO(DIRECCION(2;$M$1)):INDIRECTO(DIRECCION(CONTARA($A:$A);$M$1));
Punto y coma, el criterio es el encabezado que hemos seleccionado.
=SUMAR.SI(INDIRECTO(DIRECCION(2;$M$1)):INDIRECTO(DIRECCION(CONTARA($A:$A);$M$1));K5#
Vemos como después de K5, usa el operador de rango derramado, de esta manera, siempre seleccionara los datos que hay.
Punto y coma, y rango de suma es la columna de total.
=SUMAR.SI(INDIRECTO(DIRECCION(2;$M$1)):INDIRECTO(DIRECCION(CONTARA($A:$A);$M$1));K5#;H2:H1475)
Aceptamos, y, vemos los totales para los elementos que vemos.
Por último, vamos a crear un contador con la función SI, donde vamos a preguntar que si el valor de la celda K5, que es el primer elemento, es distinto a blanco, pues señalamos una celda donde no haya nada y sumamos 1, en caso contrario, que ponga un texto en blanco.
Ambas referencias debemos de dejarla en relativa, para que el contador vaya tomando el valor anterior y le sume 1, y para que compruebe cada elemento.
Con esto, vamos a conseguir un contador empezando desde 1, pero solo para los elementos que vemos.
=SI(K5<>"";J4+1;"")
Aceptamos, y, vemos ese contador dinámico.
Comentários