Al hilo del post anterior (ver aquí) se me planteó la posibilidad de aplicar un doble/múltiple criterio sobre distintos campos.
Sobre la imagen anterior vemos nuestra 'base de datos' (TblDATOS), y en la parte superior las condciones a aplicar sobre los campos 'Cliente' y 'Cod comercial'.
Sabiendo que las posibles correspondencias entre uno y otro son:
10 A
11 A
12 A
13 B
20 B
21 B
22 C
De tal forma que el objetivo es acumular las 'Ventas' por 'Tipo' y 'Periodo' según los Clientes y Comerciales seleccionados.
En el ejemplo de la imagen hemos seleccionado los clientes A y B, y de otra parte los Comerciales 13,22 y 10... por tanto, deberemos acumular para cada 'Tipo' y 'Periodo' los siguientes registros:
10 y A
más los siguientes
13 y B
dejando fuera el Comercial 22 ya que no coincide con ningún cliente seleccionado (A y B).
De alguna manera es como si generaramos una tabla dinámica con estos filtros:
Comprobamos que el resultado de esos cruces, en total, es de 3.979.
Pero, ¿y con fórmulas?.
Lamentablemente no es posible usando la combinación matricial:
=SUMA(SUMAR.SI.CONJUNTO(....))
ya que de esta manera se acumularía con condiciones lineales ¿¿qué, cómo...perdón??. Quiero decir acumularía, para nuestro ejemplo (fíjate en cómo están listadas las condiciones):
A y 13
más
B y 22
lo que no retornaría nada!!.
Nos quedaría BDSUMA con el rango auxliar de criterios que sí admitiría nuestras condiciones!!
Pero no es esta la solución que busco hoy... ;-)
Hoy aplicaremos las funciones CUBO (puedes leer algo más aquí).
Asi que lo primero que haremos será Cargar nuestra TblDATOS al Modelo de datos (a Power Pivot).
Desde la Ficha Power Pivot > grupo Tablas > botón Agregar al Modelo de datos. Lo que nos llevará la información al editor de Power Pivot.
Ya en nuestro editor, desde al área de cálculo de la tabla, añadiremos una medida (muy sencilla) que sume el campo de unidades:
S_Ventas:=SUM(TblDATOS[Ventas])
Ya con la medida creada, lo que nos facilitará el trabajo para acumular ventas, generamos nuestra tabla de reporte siguiente:
Con las siguientes formulaciones.
Para los encabezados escribimos primero en J7:
=CONJUNTOCUBO("ThisWorkbookDataModel";"[TblDATOS].[Periodo].children";"Periodos";5)
donde generamos un listado 'virtual' de elementos únicos extraidos del campo 'Periodo'.
En I8 repetimos la operación para el campo 'Tipos':
=CONJUNTOCUBO("ThisWorkbookDataModel";"[TblDATOS].[Tipo].children";"Tipos")
obteniendo un listado 'virtual/interno' de elementos únicos extraidos del campo 'Tipo'.
Con los conjuntos generados, procedemos a extraerlos con la función MIEMBRORANGOCUBO.
En J8, apoyándonos en el CONJUNTO indicado en el paso anterior (celda J7) escribimos:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";$J$7;SECUENCIA(1;12))
que retornará todos los elementos existentes (hasta 12, por ser el valor indicado con SECUENCIA) del conjunto de elementos de 'Periodo'.
De igual forma en I9 (apoyándonos en el conjunto previo de la celda I8) escribimos:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";$I$8;SECUENCIA(3))
que devolverá los elementos únicos del campo 'Tipo', cargados en el CONJUNTO descrito en la celda I8.
Nos toca definir dos últimos conjuntos, uno para los Clientes y otro para los Comerciales...
En la celda I6 creamos el conjunto:
=CONJUNTOCUBO("ThisWorkbookDataModel";"[TblDATOS].[Cod Comercial].[All].["&TblAgencias[Cod comercial]&"]";"Comercial")
Donde montamos un conjunto con los distintos Comerciales listado en D2:D4 (la tabla de Comerciales con los que operar).
El segundo y último conjunto en la celda I7:
=CONJUNTOCUBO("ThisWorkbookDataModel";"[TblDATOS].[Cliente].[All].["&TblGRUPO[Cliente]&"]";"Cliente")
Obteniendo el conjunto de Clientes deseado: A y B.
Con los conjuntos de criterios montados, y los miembros de los conjuntos de Tipo y Periodo desplegados, podemos recuperar valores cruzados y acumulados para todas esas condiciones... Así en J9:U11 escribimos:
=VALORCUBO("ThisWorkbookDataModel";$I9;J$8;$I$7;$I$6;"[Measures].[S_Ventas]")
Donde recuperamos el dato de nuestra medida S_Ventas teniendo en cuenta todos los conjuntos anteriores, esto es, todos los cruces de datos anteriores y fijados en la hoja de cálculo (bien por CONJUNTOS, o bien por detalle de elementos de CONJUNTOS) en las celdas: ;$I9;J$8;$I$7;$I$6
El resultado es el esperado!!!. Compáralo con la tabla dinámica anterior y verificaremos que es idéntico ;-) Éxito!!
Sobre la imagen anterior vemos nuestra 'base de datos' (TblDATOS), y en la parte superior las condciones a aplicar sobre los campos 'Cliente' y 'Cod comercial'.
Sabiendo que las posibles correspondencias entre uno y otro son:
10 A
11 A
12 A
13 B
20 B
21 B
22 C
De tal forma que el objetivo es acumular las 'Ventas' por 'Tipo' y 'Periodo' según los Clientes y Comerciales seleccionados.
En el ejemplo de la imagen hemos seleccionado los clientes A y B, y de otra parte los Comerciales 13,22 y 10... por tanto, deberemos acumular para cada 'Tipo' y 'Periodo' los siguientes registros:
10 y A
más los siguientes
13 y B
dejando fuera el Comercial 22 ya que no coincide con ningún cliente seleccionado (A y B).
De alguna manera es como si generaramos una tabla dinámica con estos filtros:
Comprobamos que el resultado de esos cruces, en total, es de 3.979.
Pero, ¿y con fórmulas?.
Lamentablemente no es posible usando la combinación matricial:
=SUMA(SUMAR.SI.CONJUNTO(....))
ya que de esta manera se acumularía con condiciones lineales ¿¿qué, cómo...perdón??. Quiero decir acumularía, para nuestro ejemplo (fíjate en cómo están listadas las condiciones):
A y 13
más
B y 22
lo que no retornaría nada!!.
Nos quedaría BDSUMA con el rango auxliar de criterios que sí admitiría nuestras condiciones!!
Pero no es esta la solución que busco hoy... ;-)
Hoy aplicaremos las funciones CUBO (puedes leer algo más aquí).
Asi que lo primero que haremos será Cargar nuestra TblDATOS al Modelo de datos (a Power Pivot).
Desde la Ficha Power Pivot > grupo Tablas > botón Agregar al Modelo de datos. Lo que nos llevará la información al editor de Power Pivot.
Ya en nuestro editor, desde al área de cálculo de la tabla, añadiremos una medida (muy sencilla) que sume el campo de unidades:
S_Ventas:=SUM(TblDATOS[Ventas])
Ya con la medida creada, lo que nos facilitará el trabajo para acumular ventas, generamos nuestra tabla de reporte siguiente:
Con las siguientes formulaciones.
Para los encabezados escribimos primero en J7:
=CONJUNTOCUBO("ThisWorkbookDataModel";"[TblDATOS].[Periodo].children";"Periodos";5)
donde generamos un listado 'virtual' de elementos únicos extraidos del campo 'Periodo'.
En I8 repetimos la operación para el campo 'Tipos':
=CONJUNTOCUBO("ThisWorkbookDataModel";"[TblDATOS].[Tipo].children";"Tipos")
obteniendo un listado 'virtual/interno' de elementos únicos extraidos del campo 'Tipo'.
Con los conjuntos generados, procedemos a extraerlos con la función MIEMBRORANGOCUBO.
En J8, apoyándonos en el CONJUNTO indicado en el paso anterior (celda J7) escribimos:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";$J$7;SECUENCIA(1;12))
que retornará todos los elementos existentes (hasta 12, por ser el valor indicado con SECUENCIA) del conjunto de elementos de 'Periodo'.
De igual forma en I9 (apoyándonos en el conjunto previo de la celda I8) escribimos:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";$I$8;SECUENCIA(3))
que devolverá los elementos únicos del campo 'Tipo', cargados en el CONJUNTO descrito en la celda I8.
Nos toca definir dos últimos conjuntos, uno para los Clientes y otro para los Comerciales...
En la celda I6 creamos el conjunto:
=CONJUNTOCUBO("ThisWorkbookDataModel";"[TblDATOS].[Cod Comercial].[All].["&TblAgencias[Cod comercial]&"]";"Comercial")
Donde montamos un conjunto con los distintos Comerciales listado en D2:D4 (la tabla de Comerciales con los que operar).
El segundo y último conjunto en la celda I7:
=CONJUNTOCUBO("ThisWorkbookDataModel";"[TblDATOS].[Cliente].[All].["&TblGRUPO[Cliente]&"]";"Cliente")
Obteniendo el conjunto de Clientes deseado: A y B.
Con los conjuntos de criterios montados, y los miembros de los conjuntos de Tipo y Periodo desplegados, podemos recuperar valores cruzados y acumulados para todas esas condiciones... Así en J9:U11 escribimos:
=VALORCUBO("ThisWorkbookDataModel";$I9;J$8;$I$7;$I$6;"[Measures].[S_Ventas]")
Donde recuperamos el dato de nuestra medida S_Ventas teniendo en cuenta todos los conjuntos anteriores, esto es, todos los cruces de datos anteriores y fijados en la hoja de cálculo (bien por CONJUNTOS, o bien por detalle de elementos de CONJUNTOS) en las celdas: ;$I9;J$8;$I$7;$I$6
El resultado es el esperado!!!. Compáralo con la tabla dinámica anterior y verificaremos que es idéntico ;-) Éxito!!
No hay comentarios:
Publicar un comentario
Nota: solo los miembros de este blog pueden publicar comentarios.