Seguimos trabajando con el modelo que suelo usar habitualmente.
Queremos calcular los ingresos totales, porcentaje por columna, porcentaje del total principal, y, porcentaje total de la fila principal.
Estos cálculos lo podemos hacer a través de una tabla dinámica, vamos a hacerlo, para ello, con una celda dentro del modelo, vamos a la pestaña de insertar, y, hacemos clic en tabla dinámica.
Marcamos hoja de cálculo existente, y, seleccionamos una celda donde colocarla.
Aceptamos.
Me llevo a filas en centro, y, producto, y, a valores, llevo total.
Cuando hemos bajado a fila, el centro y producto, es lo que se conoce como una relación padre-hijo, la lista única de centros es el padre, pero cada centro puede tener varios hijos, es decir, un centro puede tener varios productos.
Seguimos.
Para ver mejor el modelo, vamos a ponerlo en formato tabular, para ello, con una celda dentro de la tabla dinámica, vamos a la pestaña diseño, desplegamos diseño de informe, y, seleccionamos mostrar en formato tabular.
Selecciono una celda dentro de la columna suma de total, hacemos clic con botón alternativo de ratón, y, seleccionamos formato de número.
Se abre la ventana formato de celdas, en la ventana categoría, seleccionamos moneda, y, le damos dos decimales.
Ahora, tenemos que calcular el porcentaje del total de la columna, para ello, volvemos a bajar a valores, la columna de total.
Hacemos clic con botón alternativo de ratón sobre una celda en la columna suma de total2, y, desplegamos mostrar valores como, y, seleccionamos % del total de columnas.
Vemos el porcentaje que corresponde a cada venta de producto en el centro comercial, y, el porcentaje total del centro comercial, que es la parte que le corresponde respecto al total general.
Podemos ver en la fila del total general, que la suma de todos los porcentajes de cada centro es el 100%.
Ahora, vamos a calcular el porcentaje que corresponde a cada producto respecto al centro, es decir, si vemos el centro comercial Nervión, vemos los productos, y, el total por producto para dicho centro, pues vamos a calcular el porcentaje de esa cantidad por producto, respecto al total del centro comercial.
Volvemos a bajar la columna total a valores.
Volvemos a hacer clic con botón alternativo de ratón, desplegamos mostrar valores como, y, seleccionamos % del total principal.
Se abre la ventana mostrar valores como, seleccionamos la columna centro y aceptamos.
Podemos ver como al centro comercial Nervión, le corresponde el 100%, y, para los productos, es el porcentaje que le corresponde respecto a dicho total.
Vamos a volver a bajar total a valores.
Hacemos clic con botón alternativo de ratón, desplegamos mostrar valores como, y, seleccionamos % del total de filas principales.
Podemos ver los mismos porcentajes que la columna suma de total3, pero vemos que el total para el centro comercial Nervión, no es el 100%, sino el porcentaje que corresponde respecto al total general, es decir, es una combinación de la columna suma de total2, y, suma de total3.
Antes hemos hablado de la relación padre-hijo, en este caso, el porcentaje para cada producto, producto es el hijo, lo calcula con el total del padre, pero cuando llega al total del centro comercial.
No hay ningún padre, así que lo comparara con el total de la columna.
Sabemos que cuando introducimos datos nuevos, la tabla dinámica no se actualiza de forma automática, pues, es por esa razón que lo vamos a hacer con fórmulas.
Ahora, vamos a hacer lo mismo, pero con fórmulas.
Tengo el mismo modelo que la tabla dinámica, pero en Excel.
Lo primero que tenemos que calcular es el total por producto para cada centro comercial.
Para ello, vamos a usar la función SUMAR.SI.CONJUNTO, como argumento rango de suma, seleccionamos la columna de total.
=SUMAR.SI.CONJUNTO(Venta[Total]
Como argumento rango de criterios1, seleccionamos la columna de centro.
=SUMAR.SI.CONJUNTO(Venta[Total];Venta[Centro]
Como argumento criterio1, seleccionamos la celda G7, que es el centro comercial Nervión.
=SUMAR.SI.CONJUNTO(Venta[Total];Venta[Centro];G7
Como argumento rango de criterios2, seleccionamos la columna de producto.
=SUMAR.SI.CONJUNTO(Venta[Total];Venta[Centro];G7;Venta[Producto]
Como argumento criterio2, seleccionamos la celda H2, que es el primer producto para el centro comercial Nervión.
Cerramos paréntesis y aceptamos.
=SUMAR.SI.CONJUNTO(Venta[Total];Venta[Centro];G7;Venta[Producto];H2)
Tenemos el total para el producto aspiradora del centro comercial Nervión.
Arrastramos, y, tenemos los totales por producto, y, centro comercial, pero vemos que después del último producto, como total aparece cero, porque no hay coincidencias.
Ahí, debe de aparecer el total por centro comercial, pues, vamos a modificar la expresión anterior, con el condicional SI, voy a preguntar que, si el valor de la celda H2 es blanco, en ese caso, caso, con la función SUMAR.SI, que calcule el total, siempre que la columna centro sea igual al valor de G2, en caso contrario, que realice el SUMAR.SI.CONJUNTO.
Como H2, no es blanco, realizar el argumento valor si falso, igual, para las celdas H3, H4, H5, y, H6, entonces, llega a la celda H6, que, si es blanco, entonces, realizar el argumento valor si verdadero, donde calcula el total siempre que la columna centro sea igual al valor de la celda G7.
=SI(ESBLANCO(H2);SUMAR.SI(Venta[Centro];G2;Venta[Total]);SUMAR.SI.CONJUNTO(Venta[Total];Venta[Centro];G2;Venta[Producto];H2))
Aceptamos, y, arrastramos, y, vemos los totales por centro, que, si lo comparamos con los resultados de la tabla dinámica, vemos que son los mismos.
Antes de seguir, vamos a calcular el total general, para ello, voy a preguntar que si el rango H2:H37 es diferente a blanco.
=SI(H2:H37<>""
En ese caso, que me devuelva un texto en blanco.
=SI(H2:H37<>"";""
En caso contrario, que me devuelva el valor del rango I2:I37.
Cerramos paréntesis.
=SI(H2:H37<>"";"";I2:I37)
Como queremos el total, después del signo igual, usamos la función SUMA.
=SUMA(SI(H2:H37<>"";"";I2:I37))
Ya tenemos el total de la columna centro, que, si miramos la tabla dinámica, es el mismo.
Ahora, tenemos que calcular que porcentaje corresponde cada valor con el total general, es decir, el % total de columnas, esta operación es sencilla, porque solo debemos de dividir el valor de I2, entre el total general que hemos calculado anteriormente, y, la fijamos.
=I2/$I$38
Arrastramos, y, tenemos el porcentaje al que corresponde cada valor respecto al total.
Vamos a donde calculamos el total general por centro, fijamos H2:H37, y, arrastramos hacia la derecha.
=SUMA(SI($H$2:$H$37<>"";"";J2:J37))
Vemos que nos devuelve el 100%.
Lo siguiente es calcular el porcentaje del total principal.
¿Qué diferencia hay entre el cálculo anterior?
Tenemos las ventas de cada producto por centro comercial, pues, ahora, debemos de calcular el porcentaje que corresponde la venta de cada producto, respecto al total por centro comercial.
Tenemos que hacer dos preguntas, la primea de ellas, ¿es blanco H2?.
=SI(ESBLANCO(H2)
Si es blanco, vamos a dividir el total entre si mismo, lo que nos dará el 100%.
=SI(ESBLANCO(H2);I2/I2
Esto pasara cuando H2 sea blanco, pero si no lo es, debe de calcular el porcentaje de cada producto respecto al total, es decir, calcular las ventas con la función SUMAR.SI, siempre que la columna centro sea igual al valor de la celda G2.
Cuando arrastremos, la condición ESBLANCO, no se va a cumplir hasta llegar a la celda H7, quiere decir, que calculara el total para el centro comercial Nervión.
Cerramos paréntesis y aceptamos.
=SI(ESBLANCO(H2);I2/I2;I2/SUMAR.SI(Venta[Centro];G2;Venta[Total]))
Vemos que tenemos el mismo porcentaje que el obtenido en la tabla dinámica.
Arrastramos, y, podemos ver como al final de cada producto, aparece el 100% para centro comercial.
Si lo comparamos con la tabla dinámica, vemos que son los mismos.
Lo siguiente es calcular la mezcla entre los dos porcentajes calculados anteriormente, quiere decir, que debemos de calcular el porcentaje de cada producto respecto al total por centro comercial, y, el porcentaje de cada centro comercial debe de ser respecto al total general.
Para el cálculo del porcentaje respecto al total de cada centro comercial, usamos la expresión anterior.
=I2/$I$7
Tenemos el mismo porcentaje.
Ahora, debemos de calcular el porcentaje de cada centro respecto al total, para ello, pregunto de nuevo si es blanco H2, en ese caso, que calcule la suma del total para el centro comercial, y, lo divida entre la suma de la columna total.
=SI(ESBLANCO(H2);SUMAR.SI(Venta[Centro];G2;Venta[Total])/SUMA(Venta[Total])
En caso contrario, tiene que dividir el valor de I2, entre la suma total del centro comercial, que es mismo SUMAR.SI, que acabamos de usar.
=SI(ESBLANCO(H2);SUMAR.SI(Venta[Centro];G2;Venta[Total])/SUMA(Venta[Total]);I2/SUMAR.SI(Venta[Centro];G2;Venta[Total]))
Aceptamos, y, arrastramos, si comparamos con las dos columnas anteriores, vemos que los resultados son los mismos.
Pues, ya tenemos nuestro ejercicio realizado.
Comments