Hemos trabajado y visto algunos trucos con las novedosas fórmulas desbordadas (para Microsoft 365), y hoy avanzaremos un poco más viendo un gráfico desbordado!!.
No te preocupes... es un gráfico estándar referenciado a un rango desbordado ;-)
Para mostrar el ejemplo nada mejor que generar un sencillo histograma sobre unos datos de ventas (con campos País, Producto y Ventas).
Para generar la tabla de rangos del histograma emplearemos la función desbordada SECUENCIA (leer algo más aquí), apoyándonos, para dinamizarlo un 'poquito' en tres celdas:
-Celda G1: longitud del intervalo
-Celda G2: inicio de los tramos (valor más bajo)
-Celda I2: la fórmula =MULTIPLO.SUPERIOR.MAT(MAX(TblVentas[Ventas]);G1)
que muestra el múltiplo superior al intervalo anterior dado en G1 del valor máximo de la base de datos.
En el paso siguiente, en la celda F6 escribimos nuestra fórmula desbordada:
=SECUENCIA(REDONDEAR.MAS((I2-G2)/G1;0);1;G2;G1)
el argumento REDONDEAR.MAS((I2-G2)/G1;0) nos devuelve el número de filas ajustado necesario para tener nuestros tramos de acuerdo a las condiciones dadas en G1, G2 y I2, esto es, dados la longitud del intervalo, inicio y fin de los rangos del histograma.
el segundo argumento (1) nos dice que la secuencia a generar tendrá una sola columna.
el tercer argumento (G2) indica cuál es el inicio o primer valor de la secuencia.
y el cuarto argumento (G1) apunta al 'paso' de la secuencia, i.e., la diferencia entre cada elemento de la secuencia.
Mientras, en G6 escribimos la fórmula desbordada:
=+F6#+G1
muy simple, ya que apoyándonos en la anterior, sumamos al elemento de cada fila obtenido el valor de G1, es decir, de la longitud definida del intervalo
Por último, en H6 añadimos la poco empleada función FRECUENCIA referenciandonos al rango desbordado anterior:
=FRECUENCIA(TblVentas[Ventas];G6#)
donde con el primer argumento indicamos el rango de datos a contabilizar; y en el segundo argumento (G6#) nos referimos al rango con los tramos... sabiendo que al primer valor de ese rango equivalen los elementos encontrados inferiores o iguales a éste; y el último los mayores a dicho valor.
Esta fórmula de FRECUENCIA se podría sustituir por:
=CONTAR.SI.CONJUNTO(TblVentas[Ventas];">"&F6#;TblVentas[Ventas];"<="&G6#)
también con un comportamiento desbordado...
OJO con las desigualdades empleadas!!!.
Vamos con la parte del gráfico desbordado.
Primero y fundamental, de otra manera no funcionaría, crearemos dos Nombres definidos con las siguientes fórmulas desbordadas referenciadas:
Desde =Hoja1!$F$6#/1000&" k- "&Hoja1!$G$6#/1000&" k"
Dtbon =DESREF(Hoja1!$H$6#;;;CONTARA(Hoja1!$H$6#)-1;1)
Con el primer nombre 'Desde' obtenemos un concatenado (tipo '1k-1,5k') basado en las diferentes filas existentes en el rango desbordado obtenido con nuestras fórmulas previas... GRAN TRUCO!!
Mientras que con el segundo 'Dtbon' conseguimos eliminar el último dato que incorpora FRECUENCIA para los valores mayores al último dato del intervalo... que sabemos, por los límites matemáticos empleados en nuestras fórmulas, será siempre cero.
Revisa la forma de crear nombres definidos en la categoría del blog
Pero basicamente accede a la ficha Fórmulas > Nombres definidos > Asignar nombre e incluye las fórmulas anteriores...
Listos para insertar el gráfico.
Selecciona el rango F5:H13... realmente no importa demasiado ya que lo cambiaremos en un paso posterior.
Desde la ficha Insertar > Gráficos > Gráficos 2-D > Columnas agrupadas
Entraremos a la edición de datos del gráfico, para lo que haremos clic derecho y buscaremos la opción Seleccionar datos... y dejaremos una sola serie de valores (no importa cuál sea, aunque mejor dejaremos 'Distribución') y una de etiquetas
En el último paso añadiremos los nombres definidos creados previamente...
Así Modificaremos la serie 'Distribución' para que tenga esta forma:
Hoja1!Dtbon
De igual forma modificaremos la serie de etiquetas con la fórmula:
Hoja1!Desde
Con estas últimas acciones nuestro gráfico ha quedado asociado al rango desbordado anterior... y cualquier cambio en nuestras condiciones iniciales (celdas G1, G2 o I2) se reflejará automáticamente en nuestro gráfico.
No te preocupes... es un gráfico estándar referenciado a un rango desbordado ;-)
Para mostrar el ejemplo nada mejor que generar un sencillo histograma sobre unos datos de ventas (con campos País, Producto y Ventas).
Para generar la tabla de rangos del histograma emplearemos la función desbordada SECUENCIA (leer algo más aquí), apoyándonos, para dinamizarlo un 'poquito' en tres celdas:
-Celda G1: longitud del intervalo
-Celda G2: inicio de los tramos (valor más bajo)
-Celda I2: la fórmula =MULTIPLO.SUPERIOR.MAT(MAX(TblVentas[Ventas]);G1)
que muestra el múltiplo superior al intervalo anterior dado en G1 del valor máximo de la base de datos.
En el paso siguiente, en la celda F6 escribimos nuestra fórmula desbordada:
=SECUENCIA(REDONDEAR.MAS((I2-G2)/G1;0);1;G2;G1)
el argumento REDONDEAR.MAS((I2-G2)/G1;0) nos devuelve el número de filas ajustado necesario para tener nuestros tramos de acuerdo a las condiciones dadas en G1, G2 y I2, esto es, dados la longitud del intervalo, inicio y fin de los rangos del histograma.
el segundo argumento (1) nos dice que la secuencia a generar tendrá una sola columna.
el tercer argumento (G2) indica cuál es el inicio o primer valor de la secuencia.
y el cuarto argumento (G1) apunta al 'paso' de la secuencia, i.e., la diferencia entre cada elemento de la secuencia.
Mientras, en G6 escribimos la fórmula desbordada:
=+F6#+G1
muy simple, ya que apoyándonos en la anterior, sumamos al elemento de cada fila obtenido el valor de G1, es decir, de la longitud definida del intervalo
Por último, en H6 añadimos la poco empleada función FRECUENCIA referenciandonos al rango desbordado anterior:
=FRECUENCIA(TblVentas[Ventas];G6#)
donde con el primer argumento indicamos el rango de datos a contabilizar; y en el segundo argumento (G6#) nos referimos al rango con los tramos... sabiendo que al primer valor de ese rango equivalen los elementos encontrados inferiores o iguales a éste; y el último los mayores a dicho valor.
=CONTAR.SI.CONJUNTO(TblVentas[Ventas];">"&F6#;TblVentas[Ventas];"<="&G6#)
también con un comportamiento desbordado...
OJO con las desigualdades empleadas!!!.
Vamos con la parte del gráfico desbordado.
Primero y fundamental, de otra manera no funcionaría, crearemos dos Nombres definidos con las siguientes fórmulas desbordadas referenciadas:
Desde =Hoja1!$F$6#/1000&" k- "&Hoja1!$G$6#/1000&" k"
Dtbon =DESREF(Hoja1!$H$6#;;;CONTARA(Hoja1!$H$6#)-1;1)
Con el primer nombre 'Desde' obtenemos un concatenado (tipo '1k-1,5k') basado en las diferentes filas existentes en el rango desbordado obtenido con nuestras fórmulas previas... GRAN TRUCO!!
Mientras que con el segundo 'Dtbon' conseguimos eliminar el último dato que incorpora FRECUENCIA para los valores mayores al último dato del intervalo... que sabemos, por los límites matemáticos empleados en nuestras fórmulas, será siempre cero.
Revisa la forma de crear nombres definidos en la categoría del blog
Pero basicamente accede a la ficha Fórmulas > Nombres definidos > Asignar nombre e incluye las fórmulas anteriores...
Listos para insertar el gráfico.
Selecciona el rango F5:H13... realmente no importa demasiado ya que lo cambiaremos en un paso posterior.
Desde la ficha Insertar > Gráficos > Gráficos 2-D > Columnas agrupadas
Entraremos a la edición de datos del gráfico, para lo que haremos clic derecho y buscaremos la opción Seleccionar datos... y dejaremos una sola serie de valores (no importa cuál sea, aunque mejor dejaremos 'Distribución') y una de etiquetas
En el último paso añadiremos los nombres definidos creados previamente...
Así Modificaremos la serie 'Distribución' para que tenga esta forma:
Hoja1!Dtbon
De igual forma modificaremos la serie de etiquetas con la fórmula:
Hoja1!Desde
Con estas últimas acciones nuestro gráfico ha quedado asociado al rango desbordado anterior... y cualquier cambio en nuestras condiciones iniciales (celdas G1, G2 o I2) se reflejará automáticamente en nuestro gráfico.
No hay comentarios:
Publicar un comentario
Nota: solo los miembros de este blog pueden publicar comentarios.