Ejemplos3

Jaime Franco Jimenez
22 ene 2024
19 Min. de lectura

Este ejemplo, va a consistir en crear un gráfico dinámico, a través de una matriz dinamina creada previamente, en esa matriz, van a aparecer solo los países que elijamos a través de una validación de datos, y, debajo de ellos, aparecerán las ventas realizadas por esos países, y, el promedio de los países seleccionados.

Bien, tenemos el siguiente modelo de datos.

El listado es algo más grande.

Vamos a crear una lista de países únicos, para ello, vamos a usar la función UNICOS, y, ORDENAR, para que los países estén ordenados.

Con la función UNICOS, obtenemos una matriz desbordada con los países únicos.

=UNICOS(País)

Usamos la función ORDENAR, con los argumentos predeterminados, para ordenar los países.

=ORDENAR(UNICOS(País))

Lo siguiente va a ser ir a una hoja en blanco, donde vamos a crear nuestro grafico dinámico.

Lo primero que vamos a hacer es crear de forma dinámica la impresión de los países, para ello, vamos a empezar poniendo lo siguiente:

Si utilizo la función CONTARA, para saber el número de países que hay, seria:

=CONTARA(DATOS!I2#)

Si nos damos cuenta, al seleccionar la matriz completa, en vez de poner de I2 a I25, pone el operador de rango derramado (#), que quiere decir que selecciona desde la fila 2 hasta la ultima celda con datos, nos devuelve el valor 24, es decir, hay 24 países, también, podemos poner la referencia I2, y, manualmente, poner el operador de rango derramado (#).

Pero, la idea no es poner todos los países, sino los países que nosotros queramos, por lo que la función CONTARA no nos va a valer.

Si usamos la función INDICE de la siguiente manera, donde como argumento matriz, seleccionamos la lista de países únicos, como argumento número de fila, ponemos 1, y, como argumento número de columna, ponemos también 1, aunque, podemos omitir el argumento número de columna, y, de forma predeterminada pondrá 1, obtendremos el primer país, que es Alemania.

=INDICE(DATOS!I2#;1;1)

Si en el argumento número de fila de la función INDICE, en vez de poner 1, usamos la función SECUECIA, donde le decimos que genere una matriz desbordada, de una fila, cinco columnas, empezando por el número 1, que vaya incrementando 1, hasta 5, pues, tendremos los cinco primeros países.

=INDICE(DATOS!I2#;SECUENCIA(1;5;1;1);1)

Lo siguiente que vamos a hacer es crear una validación de datos (lista desplegable), compuesta desde el numero 1 hasta el numero 10, que van a ser los países que vamos a poder elegir.

Para ello, vamos al a pestaña datos, dentro del grupo herramientas de datos, hacemos clic en validación de datos.

Se abre la ventana de validación de datos.

Desplegamos permitir, y, seleccionamos lista.

En la ventana origen escribimos los valores desde el 1 hasta el 10, separados por punto y coma.

Aceptamos, y, ya tenemos nuestra lista de valores.

Lo siguiente, va a ser es volver a la función, y, en la función SECUENCIA, en el argumento columna de la función SECUENCIA, vamos a poner el valor que haya seleccionado en la validación de datos, de la siguiente manera:

=INDICE(DATOS!I2#;SECUENCIA(1;C2;1;1);1)

Bien, si ahora cambiamos el valor de la lista creada, veremos que solo quedan el numero de países indicado en la validación de datos.

Con esto ya tenemos la lista de países creadas.

Lo siguiente va a ser calcular las cantidades vendidas por los países seleccionados, para ello, vamos a usar la función SUMAR.SI, donde como argumento rango de criterios, usamos el nombre de rango pais, para que seleccione la columna de los países, como criterio, señalamos la celda B4, junto con el operador de rango derramado (#), para que seleccione desde la celda B4 hasta la última celda ocupada, en este caso, seleccionara los países, que están en columnas, y, como argumento rango de suma, ponemos el nombre de rango cantidad, quedaría de la siguiente manera:

=SUMAR.SI(País;B4#;Cantidad)

El resultado:

Que ocurre, si cambiamos el número de países.

Pues, que, de forma automática, se añaden las cantidades vendidas por esos países nuevos.

Lo siguiente, va a ser crear los porcentajes de cada país seleccionado, respecto, al total de todos los países, para ello, debemos de dividir desde B5 hasta la última columna, por lo que usaremos el operador de rango derramado (#), y, lo dividimos entre la suma de todos los valores de los países seleccionados, donde usaremos la función SUMA.

=B5#/SUMA(B5#)

Si nos damos cuenta, estamos usando funciones que devuelven matrices dinámicas, también, podemos observar que el tiempo de formulación es más rápido.

Como resultado, tenemos la diferencia porcentual de cada pais, respecto al total.

Podemos comprobar que, si cambiamos el valor de la validación de datos, los datos se actualizan de forma automática.

Lo siguiente va a ser crear nuestro gráfico, en blanco, es decir, sin datos, para ello, vamos a usar un gráfico de columnas agrupadas.

Vamos a la ficha insertar, dentro del grupo gráficos, inserto el grafico de columna agrupadas, de momento, sin datos.

¿Por qué el grafico en blanco?

Si seleccionamos los datos de la matriz, seria un gráfico, que seria correcto, pero que no sería dinámico, siempre presentaría los mismos datos, y, lo que nosotros queremos es que cuando seleccionemos los países, esos sean los países para graficar, para ello, debemos de hacer uso, en mi caso, de la función DESREF.

La función DESREF, lo que hace es que, a partir de una celda inicial, nos podemos ver un numero de filas, y, columnas que le indiquemos.

La función DESREF, tiene 4 argumentos.

· Ref, es la celda de donde vamos a empezar.

· Filas, numero de filas a bajar.

· Columnas, numero de columnas a movernos, si el valor es positivo, nos movemos hacia la derecha, y, si es negativo hacia la izquierda.

· Alto, numero de filas a tomar.

· Ancho, numero de columnas a tomar.

Por ejemplo, si estando en la celda A2, escribo la siguiente función, donde como argumento filas, ponemos 2, como argumento columnas, ponemos 1, como alto, vamos a poner 2, y, como argumento ancho, pues, ponemos 2.

=DESREF(A2;2;1;2;2)

Me genera una matriz, de la siguiente manera:

Es decir, desde A2, baja dos filas, se queda en la misma columna, ahora, toma un alto de dos filas, por eso, toma los valores de 8, y, 9, y un ancho, también, de dos, de ahí que tome los valores de 13 y 14.

Pero si el resultado que queremos es que sea una suma, ponemos delante la función SUMA.

=SUMA(DESREF(A2;2;1;2;2))

Y el resultado:

Que es la suma de 8, 9, 13 y 14.

Una vez que ya sabemos para que vale la función DESREF, ya podemos entrar de lleno en nuestro grafico dinámico.

Lo primero que vamos a ser es crear un nombre de rango, se va a llamar columnas_dinámicas, y, con la función DESREF, vamos a conseguir que solo queden seleccionados los países que hayamos seleccionado en la validación de datos.

Pero, antes de crear el nombre de rango, voy a crearlo en una celda, para ver que funciona correctamente.

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

=DESREF(

Como celda de partida, es la celda B6.

=DESREF(Resuelto!B6;

Nos movemos cero filas, y, cero columnas, por lo que nos quedamos en la celda B6.

=DESREF(Resuelto!B6;0;0;

El argumento alto lo vamos a omitir, por que lo que necesitamos es el ancho, ya que los países se encuentran en columnas, por lo ponemos punto y coma, para pasar al argumento ancho, donde seleccionamos la celda C3, que es quien indica el número de países.

=DESREF(Resuelto!B6;0;0;;Resuelto!C3)

Aceptamos.

Vemos que tenemos una matriz desbordada con los valores de los países seleccionados.

Si cambiamos el numero de países, veremos como se actualizan los valores.

Lo que tenemos que hacer ahora, es seleccionar la función, y, pulsar CTRL más C para copiar, pero antes, vamos a fijar las referencias para que no se actualicen al copiar.

=DESREF(Resuelto!$B$6;0;0;;Resuelto!$C$3)

Ahora, vamos a la pestaña formulas, dentro del grupo nombres definidos, elegimos asignar nombre.

Se abre la ventana nombre nuevo, como nombre, ponemos, columnas dinámicas, y, en la ventana se refiere a, pulsamos CTRL mas V para pegar.

Aceptamos.

Es importante, delante poner el numero de hoja, esta función significa que tomara los datos de dicha RESUELTO, esto es cuando tenemos más de una hoja.

Lo siguiente será crear otra etiqueta dinámica, la podemos llamar, paises_dinámicos, pero en este caso, para que se seleccionen los países seleccionados, la formula a usar es la misma que la anterior, con la excepción de que tenemos que cambiar el numero de fila, la formula quedaría:

Aceptamos.

Ya tenemos creada nuestras etiquetas dinámicas.

Vamos a seleccionar el gráfico, vemos que se habilitan las pestañas de diseño de gráfico, y, formato, dentro de la pestaña diseño de gráfico, hacemos clic en seleccionar datos.

Se abre la ventana seleccionar origen de datos, pues, hacemos clic en agregar.

Se abre la ventana modificar serie.

En valores de la serie, vamos a poner el nombre de rango columnas_dinamicas, decir, que cuando vamos a usar un nombre de rango dentro de un rango, debemos de especificar el nombre del archivo, mas la extensión, y, el signo de exclamación.

En caso, de no acordarnos de que nombre de rango, podemos pulsar la tecla de F3, y, se abrirá una ventana donde aparecerán todos los nombres de rangos creados, seleccionamos el que queremos y aceptamos.

=GRAFICO_DINAMICO.xlsx!columnas_dinamicas

Aceptar.

Vemos que aparecen seis columnas, que equivale a los valores de los seis países que hemos seleccionado, si cambiamos, si ponemos, por ejemplo, 4, el resultado sería:

Lo siguiente va a ser que aparezcan los nombres de los países seleccionados en la leyenda horizontal, para ello, volvemos al grupo diseño de gráfico, y, hacemos clic en seleccionar datos.

Se abre la ventana seleccionar origen de datos.

En etiquetas del eje horizontal, vamos a poner los nombres de los países seleccionados.

Hacemos clic en editar.

Se abre la ventana rótulos del eje, y, en rango de rotulo del eje, ponemos la siguiente expresión, es decir, el nombre de rango paises_dinamicos, recordar, que debemos de poner el nombre del libro, seguido de la extensión, y, el signo de exclamación.

=GRAFICO_DINAMICO.xlsx!paises_dinamicos

Aceptamos, y, dentro de la ventana etiquetas del eje horizontal, podemos ver los nombres de los países.

Aceptar.

Vemos el resultado:

Ya aparece el país en cada columna.

Bien, ya tenemos nuestro grafico dinámico, a partir de una matriz dinámica, lo que vamos a ser ahora, es crear, o, calcular el promedio, lo haremos con la siguiente expresión, donde vamos a usar el condicional SI, donde preguntamos, que si el valor de la celda B6, es distinta a blanco, entonces, que calcule el promedio desde B6 hasta la última celda ocupada, donde usamos el operador de rango derramado (#), en caso contrario, que ponga un texto en blanco.

=SI(B6<>"";PROMEDIO(B6#);"")

Con esta formula conseguimos que solo ponga el promedio en aquellas celdas que no estén vacías, por lo que la hacemos dinámica.

También, debemos de fijas la columna B dentro de la función PROMEDIO, para que, al copiar hacia la derecha, no se actualice.

=SI(B6<>"";PROMEDIO($B6#);"")

Lo hacemos con el condicional SI, por si hubiera países que no tuviera ventas, porque si no lo hacemos de esta manera, el calculo de la media aritmética no seria correcta.

Como resultado, vemos debajo de la cantidad de cada pais, el promedio, que es el mismo para cada pais.

Ahora, vamos a añadir ese promedio al gráfico, para que veamos de un simple vistazo, cuales son aquellos países que han sobrepasado el promedio o no, por lo que debemos de crear otro nombre de rango, con la expresión anterior, para ello, seleccionamos la expresión, pulsamos CTRL mas C para copiar, vamos a la pestaña de fórmulas, dentro del grupo nombres definidos, hacemos clic en asignar nombre.

Como nombre, ponemos, promedio, en la ventana se refiere a, tenemos que usar la misma función DESREF, usada anteriormente, lo único que debemos de cambiar es la celda de partida, que, en este caso, es la celda B8, la celda de los promedios.

=DESREF(Resuelto!$B$8;0;0;;Resuelto!$C$3)

Aceptamos.

Vamos a la pestaña diseño de gráfico, y, hacemos clic en seleccionar datos.

Se abre la ventana seleccionar origen de datos, pues, hacemos clic en agregar.

Se abre la ventana modificar serie, pues, en valores de la serie, ponemos el nombre del libro, seguido de la extensión, ponemos el signo de exclamación, y, el nombre de rango promedio.

Aceptamos, y, como resultado, vemos el promedio añadido, pero como grafico de columnas agrupadas.

La columna naranja es el promedio, la vamos a cambiar por una línea, para ello, hacemos clic con botón alternativo de ratón dentro de una columna, y, seleccionamos cambiar tipo de gráfico de series.

Se abre la siguiente ventana cambiar tipo de gráfico.

Hacemos clic en combinado.

En la ventana elija el tipo de gráfico y el eje para la serie de datos, cambiamos la serie2, a un gráfico de líneas.

Aceptamos, y, podemos ver el promedio como una línea horizontal del mismo tamaño.

Aceptamos, y, ya tenemos también el promedio dinámico.

Bien, pues con esto, ya tenemos nuestro ejemplo realizado.

En el siguiente ejemplo, vamos a trabajar con el modelo de contoso, mas exactamente, con una serie de tiendas, debido a que son muchas tiendas.

Tenemos una serie de tiendas, y, una serie de productos vendidos en dichas tiendas con sus cantidades.

El total de tiendas son tres, lo que ocurre es que se repiten.

Vamos a ir haciendo el ejercicio, y, lo vamos explicando.

Desde la ficha de programador, vamos a insertar un cuadro combinado, o, lo que es lo mismo una lista desplegable.

Si no tenemos activa la ficha programador, o, desarrollador, si tenemos la versión 2013, debemos de habilitarla, para ello, hacemos clic en archivo, y, seleccionamos opciones.

Se abre la ventana opciones de Excel.

En la ventana de la izquierda seleccionamos personalizar cinta de opciones.

En la ventana de la derecha, donde vemos las pestañas de nuestro Excel, buscamos programador, o, desarrollador y la marcamos.

Aceptamos.

Ya debemos de verla en nuestra cinta de opciones.

Lo siguiente va a ser rellenar este cuadro combinado.

Para ello hacemos clic con el botón alternativo del ratón sobre el control, y, elegimos formato de control.

En la ventana rango de entrada, seleccionamos las tres tiendas.

En vincular con la celda, debemos de elegir una celda donde se colocará el número que pertenece a la tienda seleccionada, esto quiere decir, que a la primera tienda tiene asignado el numero 1, la segunda, el numero 2, y, la tercera el número 3.

Esto quiere decir que no podemos trabajar directamente con la tienda seleccionada desde el cuadro combinado.

Lo vamos a poner, por ejemplo, en A5.

Si seleccionamos una tienda, podemos ver como en A5, se coloca el número que le corresponda.

Lo siguiente es filtrar la columna producto, y, cantidad por la tienda seleccionada.

¿Qué problema se presenta?

A la función filtrar, le debemos de especificar el criterio por el que filtrar, que es el argumento include, que debe de ser el nombre de la tienda seleccionada, pero no tenemos el nombre de la tienda a buscar, sino un número.

Tenemos una función llamada SI.CONJUNTO, que es igual que un SI anidado, pero con un solo SI, pues, vamos a usar esta función para convertir el numero de tienda en el nombre de la tienda.

El primer argumento de esta función es prueba lógica 1, que en nuestro caso, es si A5 es igual a 1.

=SI.CONJUNTO(A5=1

El siguiente argumento es valor si verdadero 1, pues en caso de ser 1, debe de poner el valor de la celda A2, es decir, el nombre de la primera tienda.

=SI.CONJUNTO(A5=1;A2

Los siguientes argumentos son prueba lógica 2, prueba lógica 3, etc.

Pues vamos haciendo la misma pregunta pero cambiando el valor.

=SI.CONJUNTO(A5=1;A2;A5=2;A3;A5=3;A4)

Aceptamos, y, vemos ahora el nombre de la tienda que corresponde con el número.

Pues esta función va a ser el argumento include de la función filtrar.

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

=FILTRAR(

Como argumento array, seleccionamos las columnas producto y cantidad.

FILTRAR(D2:E20;

Y el argumento include ya lo tenemos.

=FILTRAR(D2:E20;C2:C20=SI.CONJUNTO(A5=1;A2;A5=2;A3;A5=3;A4))

Aceptamos, y, tenemos una matriz desbordada con los resultados.

Lo siguiente va a ser insertar un grafico de barras apiladas, para ello, seleccionamos los datos, y, vamos a la pestaña insertar, e, insertamos un grafico de barras 2D.

Ponemos etiquetas de datos, para ello, seleccionamos el grafico, en la parte derecha arriba, hacemos clic en el símbolo de (+), y, marcamos etiquetas de datos.

Quitamos líneas de división.

Pero vemos que no estas etiquetas de datos, no están ordenadas, sino graficadas tal cual aparece en el informe.

Pues vamos a ordenarla, para ello, en la función filtrar, vamos a usar la función ordenar, y, ordenamos por la columna de cantidad de manera ascendente, para ello, después del signo igual escribimos la función ordenar.

=ORDENAR(

Como argumento matriz, ya lo tenemos, que es la función FILTRAR.

=ORDENAR(FILTRAR(D2:E20;C2:C20=SI.CONJUNTO(A5=1;A2;A5=2;A3;A5=3;A4));

El siguiente argumento de la función ordenar es ordenar_indice, es decir, porque columna queremos ordenar, en este caso, queremos ordenar por cantidad, pues ponemos el número 2, que es la columna cantidad.

=ORDENAR(FILTRAR(D2:E20;C2:C20=SI.CONJUNTO(A5=1;A2;A5=2;A3;A5=3;A4));2;

Como ultimo argumento, que es criterio_ordenacion, ordenamos de manera ascendente.

=ORDENAR(FILTRAR(D2:E20;C2:C20=SI.CONJUNTO(A5=1;A2;A5=2;A3;A5=3;A4));2;1)

Aceptamos, y, tenemos ordenadas las etiquetas de datos.

Otro problema que se nos presenta es que si elegimos, por ejemplo, la primera tienda, como resultado tenemos seis líneas, pero si elegimos la segunda tienda, como resultado tenemos siete líneas, y, en el grafico dicha línea se ve vacía.

No podemos usar el operador de rango derramado en una serie de un gráfico.

Vamos a crear un nombre de rango a partir del operador de rango derramado.

Para ello, vamos a la pestaña formulas.

Dentro del grupo nombres definidos, hacemos clic en asignar nombre.

Se abre la ventana editar nombre, pues le ponemos un nombre.

En se refiera a, debemos de poner la expresión a usar, pero como es una matriz desbordada, que ocupa dos columnas, debemos de alguna manera, indicarle que debe de tomar los valores de la segunda columna de esta matriz desbordada, pues para ello, vamos a usar la función índice, donde como argumento matriz es la celda G6, que es donde comienza la matriz, y, a continuación, ponemos el operador de rango derramado, de esta manera van a quedar seleccionados todos los datos que hay desde G6, hasta la última fila ocupada, por lo que la convertimos en dinámica.

El siguiente argumento es numero de fila, pero este argumento no nos interesa, por lo que escribimos punto y coma, y, pasamos al siguiente argumento, que es numero de columna, es aquí, donde debemos de indicar cual es la columna que queremos obtener, como es la columna de cantidad, ponemos el número 2.

Cerramos paréntesis, y, aceptamos.

Esto va a ser que se seleccione desde la celda H6 hasta la última fila ocupada, y, un nombre de rango si lo podemos usar para crear una serie de un gráfico.

Para ello, seleccionamos el grafico.

Se habilitan las pestañas de diseño de gráfico, y, formato.

Hacemos clic en diseño de gráfico.

Hacemos clic en seleccionar datos.

Se abre la ventana seleccionar origen da datos.

En la ventana inferior izquierda, en entradas de leyendas (series), seleccionamos la serie 1, y, hacemos clic en modificar.

Hay que decir que para poner el nombre de rango, primero debemos de poner el nombre del libro con su extensión, seguido del signo de exclamación, y, a continuación, el nombre de rango.

Aceptamos, y, si probamos veremos que ahora se grafica solo los datos que vemos en el informe.

También, lo podemos hacer con la función desref.

Sabemos que esta función tiene cinco argumentos, que es punto de partida, numero de filas a bajar o subir, numero de columnas a movernos, alto, y, ancho.

Vamos a probar el uso de esta función en una celda, para ver que funciona correctamente.

Pues, en una celda escribo el signo igual, seguido de la función DESREF, y, abro un paréntesis.

El primer argumento es referencia, es decir, el punto de partida, pues vamos a salir desde la celda G5.

=DESREF(G5;

El siguiente argumento es filas, es decir, cuantas filas tenemos que bajar, en este caso, pues va a ser una fila, es decir, que desde G5, se va a colocar en G6.

=DESREF(G5;1;

El siguiente argumento es columnas, pues de moverse hacia la derecha una columna, es decir, se va a posicionar en la celda H6, el primer valor.

=DESREF(G5;1;1

El siguiente argumento es alto, es decir, cuantas filas tengo que tomar, debe de ser un número, por ejemplo, si escribo 3, me va a tomar tres filas, la pregunta es ¿cuántas filas he de tomar?, bien, tenemos una función llamada CONTAR, que nos cuenta el número de celdas numéricas dentro de un rango, en el argumento de esta función podemos especificar un rango, quiere decir, que si como argumento escribo G6 seguido del operador de rango derramado, es decir, G6#, me devolverá el número de celdas numéricas que hay desde G6 hasta la ultima celda ocupada, pues lo ponemos como argumento de la función CONTAR.

=DESREF(G5;1;1;CONTAR(G6#);

Como argumento ancho, ponemos 1, para que se quede en la columna donde estamos.

=DESREF(G5;1;1;CONTAR(G6#);1)

Aceptamos, y, tenemos una matriz desbordada con las cantidades.

Si seleccionamos una tienda con siete valores, gracias al operador de rango derramado, podemos ver como se rellena la matriz desbordada.

Como ya hemos visto que funciona correctamente, seleccionamos la función y copiamos con CTRL más C.

Lo siguiente que vamos a hacer es crear otro nombre de rango con esta función.

Pues, seguimos los pasos descritos anteriormente, para crear el primer nombre de rango.

Seleccionamos el grafico, vamos a seleccionar datos, seleccionamos la serie que ya tenemos creada, quitamos el nombre de rango que hay, y, ponemos este nombre de rango.

Podemos ver que todo sigue igual, porque los resultados son los mismos.

Si nos fijamos en la hoja de ventas, podemos ver que cada producto tiene asociado una subcategoría, pues queremos verla.

Para ello, voy a usar la función buscarx, pero tenemos que usar un buscarx dentro de otro, como un si anidado.

Lo primero que tengo que hacer es buscar el producto en la hoja de producto, y, que me devuelva el id subcategoría.

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

=BUSCARX(

El primer argumento del segundo BUSCARX es valor buscado, pues seleccionamos desde G6 hasta G12.

=BUSCARX(BUSCARX(G6:G12

El siguiente argumento es matriz de búsqueda, pues seleccionamos la columna B de la hoja producto.

=BUSCARX(BUSCARX(G6:G12;Producto!B:B;

El siguiente argumento es matriz devuelta, pues seleccionamos la columna C de la hoja producto.

=BUSCARX(BUSCARX(G6:G12;Producto!B:B;Producto!C:C)

Aceptamos, y, tenemos una matriz desbordada con los ID de subcategoría.

Pero, no queremos ver el ID, sino el nombre de la subcategoría, por lo que vamos a usar otro BUSCARX, y, como primer argumento, que es valor buscado, va a ser este BUSCARX que acabamos de crear.

La expresión quedaría:

=BUSCARX(BUSCARX(G6:G12;Producto!B:B;Producto!C:C);SubcategoriaProducto!A:A;SubcategoriaProducto!B:B)

Aceptamos, y, podemos ver el nombre de la subcategoría de cada producto.

Si en vez de un desplegable, queremos usar botones de opción, también lo podemos hacer.

Funciona igual que el cuadro combinado, asignándole un numero a cada botón.

Para ello, vamos a la pestaña de programador, desplegamos insertar, y, elegimos botón de opción.

Lo colocamos en la hoja de cálculo, le borramos el nombre, tenemos que copiar este botón dos veces más, para ello, basta con seleccionarlo con el botón alternativo del ratón, elegimos copiar, y, lo pegamos en horizontal.

Encima de cada botón en una celda, voy a poner el nombre de cada tienda.

Lo único que tenemos que hacer es seleccionar un botón de opción con botón alternativo del ratón, y, seleccionar formato de control, y, vincularlo con la misma celda que hicimos para el cuadro combinado.

Como hemos ordenado, la barra superior siempre tendrá el valor mayor, y, la barra inferior el valor menor, pero queremos que esas barras aparezcan de otro color, es decir, la barra de mayor tamaño de un color diferente al resto, así como la barra de menor tamaño.

Para ello, vamos a usar el condicional SI, de forma matricial, y, como argumento prueba lógica, como vamos a trabajar con la matriz derramada que empieza en G6, voy a usar de nuevo la función índice para tomar la columna de cantidades, pregunto si el valor máximo de las cantidades es igual a una de las cantidades, pues que ponga el valor, en caso contrario, voy a preguntar lo mismo pero comparando con el valor mínimo, y, en caso de no haber coincidencias que ponga un texto en blanco.

=SI(MAX(INDICE(G6#;;2))=INDICE(G6#;;2);INDICE(G6#;;2);SI(MIN(INDICE(G6#;;2))=INDICE(G6#;;2);INDICE(G6#;;2);""))

Tenemos una matriz desbordada con el valor máximo y mínimo.

Pero usamos varias veces la función índice, por lo que vamos a crear una función LET.

=LET(a;INDICE(G6#;;2);SI(MAX(a)=a;a;SI(MIN(a)=a;a;"")))

La sintaxis queda más corta.

Fijamos G6, y, creamos un nombre de rango.

Seleccionamos la función y copiamos con CTRL más C.

Cuando creemos el nombre de rango, en la ventana se refiere a, pegamos la función con CTRL más V.

Pues, vamos a agregar esta serie al gráfico, para ello, seleccionamos el grafico, hacemos clic en seleccionar datos, dentro de la pestaña diseño de gráfico.

Se abre la ventana seleccionar origen da datos.

En la ventana inferior izquierda, en entradas de leyendas (series), hacemos clic en agregar.

Igual que antes, tenemos que poner el nombre del libro, seguido de la extensión, signo de exclamación, y, nombre de rango, si por cualquier motivo no nos acordamos del nombre de rango, podemos pulsar la tecla F3, y, se abrirá una ventana con los nombres de rangos creados, lo seleccionamos y aceptamos.

Aceptamos.

Podemos ver que se han añadido dos columnas, una al lado de la de mayor valor, y, otra al lado de la columna de menor valor.

Quitamos las etiquetas de datos de estas columnas.

Podemos, si queremos, cambiar el color a estas columnas.

Pero, debemos de superponer estas columnas, es decir, las columnas que acabamos de añadir se deben de poner encima de las de su lado.

Para ello, hacemos clic con el botón alternativo del ratón sobre una de estas columnas, y, seleccionamos dar formato a serie de datos.

Se abre una ventana, donde vemos superposición de series, que es la separación que hay entre columna y columna, pues, ponemos el 100%, que quiere decir, que se suba una columna encima de la otra.

Ya tenemos terminado nuestro ejemplo.

Tenemos una serie de provincias junto con unos centros comerciales, y, una serie de productos vendidos en cada centro y provincia.

Si nos fijamos en el modelo, podemos ver, por ejemplo, que Almería tiene dos ventas en el centro comercial Nervión, que Huelva tiene tres ventas en el centro Metromar, es decir, que las provincias han vendido en el mismo centro comercial, pero suponemos que en distintas fechas.

Queremos crear un informe donde en vertical deben de aparecer las provincias, en horizontal los centros comerciales, y, en medio los productos que se han vendido en dicho centro comercial.

Pues lo primero que vamos a hacer es con la función UNICOS, crear una lista en vertical de provincias únicas.

=UNICOS(D3:D15)

Ahora, con la función TRANSPONER, y, UNICOS vamos a traernos los centros comerciales únicos en horizontal.

=TRANSPONER(UNICOS(C3:C15))

Para este ejemplo, vamos a hacer uso de la función FILTRAR.

Como argumento array, señalamos el rango con los productos, desde D3 a D15.

=FILTRAR(D3:D15;

Como argumento include con el símbolo de concatenar, unimos el rango provincia y centro.

=FILTRAR(D3:D15;B3:B15&C3:C15

Y lo igualamos a F3 concatenado con G2.

=FILTRAR(D3:D15;B3:B15&C3:C15=F3&G2)

Aceptamos, como este centro, Centro comercial Nervión, en esta provincia, Almería, ha vendido dos productos, tenemos una matriz desbordada con ambos productos.

Es decir, el resultado aparece en dos celdas, cuando queremos que aparezca en la misma celda, porque ahora tenemos que copiar hacia abajo, y, nos va a dar problemas.

Pues para conseguir este resultado, si somos suscriptor de office 365, vamos a usar la función UNIRCADENAS.

Con la función UNIRCADENAS, especificamos una vez el delimitador, decidimos si queremos ignorar celdas vacías, y, especificamos las cadenas a unir.

Lo bueno de la función UNIRCADENAS, es que en el argumento texto, podemos especificar una función, en nuestro caso, vamos a usar la función FILTRO que hemos creado anteriormente.

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

=UNIRCADENAS(

Como argumento delimitador vamos a poner el signo ampersan y dejamos un espacio.

=UNIRCADENAS(" & "

Ignoramos celdas vacías.

=UNIRCADENAS(" & ";VERDADERO

Y como argumento texto1 es la función filtrar.

=UNIRCADENAS(" & ";VERDADERO;FILTRAR(D3:D15;B3:B15&C3:C15=F3&G2))

Aceptamos.

Podemos ver que en la misma celda ya aparecen los dos valores.

Ahora, tenemos que copiar tanto hacia abajo, como hacia la derecha.

Para ello, tenemos que fijas los rangos de búsquedas dentro de la función FILTRAR, fijarlo como absoluto.

=UNIRCADENAS(" & ";VERDADERO;FILTRAR($D$3:$D$15;$B$3:$B$15&$C$3:$C$15=F3&G2))

De la celda F3, debemos de fijas la columna para que al copiar hacia la derecha no se actualice.

De la celda G2, debemos de fijar la fila, para que al copiar hacia abajo la fila no se actualice.

Voy a rellenar el argumento si vacío de la función filtrar con un texto en blanco.

Me coloco en la función.

Presiono CTRL más C para copiar.

Selecciono todas las celdas incluida esta.

Pego en el resto de las celdas con CTRL más V.

Ya tenemos lo que queríamos.

Lo siguiente es que no disponemos de disponemos de la función filtrar, pero igualmente, debemos de hacerlo.

Pues vamos a ver cómo hacerlo.

Cuando tenemos que especificar más de una condición en una función, por ejemplo, como es el caso que vamos a usar el condicional SI, podemos hacerlo abriendo un paréntesis, escribiendo la primera condición, cerramos paréntesis, ponemos el símbolo de asterisco, que es igual que el operador lógico Y, es decir, devuelve verdadero, si todas las condiciones se cumplen, abrimos otro paréntesis, y ponemos una segunda condición, cerrando el paréntesis.

En este caso, que vamos a usar el condicional SI, solo debemos de usar un condicional, no haría falta usar un SI anidado.

Voy a escribir el signo igual, y, abro un paréntesis, donde voy a poner una primera condición, que es que la provincia sea igual a la primera de las provincias únicas, es decir, a Almería.

=SI(($B$3:$B$15=$F3

Cierro el paréntesis.

=SI(($B$3:$B$15=$F3)

Obtengo una matriz desbordada con verdadero donde hay coincidencia, y, falso donde no la hay.

Ahora, tenemos que poner la siguiente condición, que es que el centro comercial sea igual al valor de la celda G2, que es el centro comercial Nervión.

Para ello, tenemos que usar el símbolo de asterisco, que es igual que el operador lógico Y, abrimos un paréntesis, y, ponemos la segunda condición.

Cerramos paréntesis.

=(B3:B15=F3)*(C3:C15=G2)

Obtenemos otra matriz desbordada pero ahora con 1 donde hay coincidencia, y, 0 donde no la hay.

Preguntamos si el resultado de ambas condiciones es igual a 1, que nos devuelva el valor de la columna D, en caso contrario, que ponga un texto en blanco.

=SI((B3:B15=F3)*(C3:C15=G2)=1;D3:D15;"")

Obtenemos los productos que corresponde a Almería, y, el centro comercial Nervión.

Ahora, volvemos, igual que antes, a usar UNIRCADENAS.

=UNIRCADENAS(" & ";VERDADERO;SI(($B$3:$B$15=$F3)*($C$3:$C$15=G$2)=1;$D$3:$D$15;""))

Antes de copiar tenemos que fijar algunas referencias.

Copiamos hacia abajo y hacia la derecha, y, vemos los mismos resultados que antes.

Ahora, lo vamos a hacer pero con una sintaxis mas larga, pero el resultado será el mismo.

Lo primera que voy a hacer es usar la función ENCONTRAR para hallar la primera provincia en el rango de provincias.

=ENCONTRAR(F3;B3:B15)

Obtengo la posición de la primera letra del texto buscado.

Ahora, voy a preguntar si el resultado de ENCONTRAR es número con la función ESNUMERO.

=ESNUMERO(ENCONTRAR(F3;B3:B15))

En este caso, obtengo verdaderos y falsos.

Lo siguiente que voy a hacer, es usar es usar un SI anidado, para preguntar que si el valor devuelto por ESNUMERO es verdadero, donde en caso afirmativo, en el argumento valor si verdadero de este primer SI, vuelvo a abrir un nuevo SI, donde como argumento prueba lógica, pregunto si en la columna de centros comerciales, es decir, desde C3 a C15, es igual al valor de G2, pues que me devuelva la columna D, es decir, la de productos, desde D3 a D15, en caso contrario, que me devuelva un texto en blanco.

=SI(ESNUMERO(ENCONTRAR(F3;B3:B15));SI(C3:C15=G2;D3:D15;"");"")

Obtengo una matriz desbordada con los productos de Almería y el centro comercial Nervión.

Volvemos a usar UNIRCADENAS.

=UNIRCADENAS(" & ";VERDADERO;SI(ESNUMERO(ENCONTRAR($F3;$B$3:$B$15));SI(C3:C15=G2;D3:D15;"");""))

Ya lo tenemos.

Fijamos B3:B15 dentro de la función ENCONTRAR como absoluta, porque no deben de cambiar al copiar.

Fijamos C3:C15 del segundo SI, porque tampoco debe de cambiar al copiar.

Fijamos D3:D15, también, como absoluta.

Ahora, fijamos la columna de F3, como absoluta, porque al copiar hacia la derecha, la columna no se debe de actualizar, ya que las provincias, siempre van a estar en la columna F.

Por último, fijamos la fila de G2, porque al copiar hacia abajo, la fila no puede actualizarse, porque los centros comerciales, siempre van a estar en la fila 2.