Función contar si conjunto en una tabla dinámica

[Diego1991]

Buenos días, en el archivo que estoy trabajando debo hallar la cantidad de puntos de venta coberturados por cada vendedor, para eso uso la función contar si conjunto donde cuento cada código de cliente por cada vendedor, esto lo hago a un lado de la tabla dinámica porque no sé si es posible hacerlo dentro de la misma. No pudo subir el archivo que estoy trabajando porque sólo se me permite un maximo de 2mb, pero adjunto una foto de como se ve mi archivo, gracias por la ayuda.

[GabrielRaigosa]

Hola,

Dentro de la misma tabla no se puede hacer, podria usar otra tabla, usando la opcion de recuento distinto.

Al momento de hacer la tabla ve una opcion que dice "AGREGAR AL MODELO DE DATOS"

Esto permite el conteo de codigos unicos por vendedor,

Adjunto ejemplo.

Datos.xlsx

[Diego1991]

Muchas gracias por la ayuda.

[Diego1991]

Hola @GabrielRaigosa estaba revisando el archivo que me mandaste y comparándolo con el que estoy trabajando, en el mío hay vendedores que no vendieron a ciertos clientes por lo que el total de venta figura como 0 y me lo cuenta como un punto coberturado cuando no debería ser así, no sé si en el método que me mencionaste es posible poner como criterio que cuente sólo los números mayores a 0, yo lo hacía en una formula aparte poniendo ">0".

[GabrielRaigosa]

Hablando de ejemplo que le envié:

 

Use la opción del filtro de valor, alli selecciona >0

[Diego1991]

Caso.xlsxMuchas gracias por tu tiempo @GabrielRaigosa. Reduje la data que tenía en mi archivo original ya que tengo más sucursales, dejé sólo 1 para que pese menos y puse los datos, el método ineficiente que utilizo yo para hallar la cobertura y el método que me dijiste que debía utilizar pero que no he podido lograr que funcione correctamente. Espero que viendo el archivo con el que estoy trabajando puedas ayudarme. De antemano muchas gracias por tu ayuda.

[GabrielRaigosa]

¿Cuando se refiere a las ventas cero son estas?

¿Las que dicen soles sin IGV?

Si ese es el caso la tabla dinámica no va servir por para un cliente "x" puede que en un periodo tenga una venta ">0" y una venta "=0", por que lo que se está es contando el codigo independiente del valor de esa columna.

Bueno no se si entendí bien.

Suponiendo que quiero ese conteo de codigos únicos para ventas mayores que cero pero dependiendo del valor IGV de la base de datos se me ocurre agregar un campo adicional en la base de datos para determinar cuales registros se van a contar, es un filtro que usaré en la tabla dinámica.

Adjunto archivo, le cambie el formato a "xlsb" solo para reducir el tamaño, revise el adjunto.

Caso.xlsb

[Diego1991]

Acabo de revisar el adjunto, disculpa por no aclarar que "soles sin IGV" eran los valores de venta, y sí, los valores 0 que están en esa columna son los clientes que no compraron y por lo tanto no deben ser contados. Haciendo la comparación entre mi método y el suyo hay una diferencia en la cobertura total al parecer el campo adicional no funciona para lograr contar sólo a los clientes que compraron. ¿Será que es imposible hacerlo en una tabla dinámica y siempre he de hacerlo de la manera en que lo he venido haciendo hasta ahora?

[GabrielRaigosa]

Hola,

Las ventas negativas afectan la condición que he puesto ">0"

Pero no afectan su sistema de conteo que se basa en la suma de ventas.

¿Como se interpretan estos negativos?

[Diego1991]

Hola, gracias por responder. Los negativos son ventas que en algún momento se realizaron pero han sido devueltas por diferentes motivos por lo que tampoco entrarían al conteo, en la hoja donde está el método que realizo puede ver que considero toda la columna "soles sin igv" incluído los negativos y el 0 porque coloco el criterio ">0", por lo cual ya no estarían siendo contados.

[GabrielRaigosa]

Hola,

El valor dentro en la tabla dinámica es el resultado de suma del campo que tiene las ventas, resultado de los valores + y valores -.

Su conteo externo se hace sobre los valores de la tabla dinámica, no se hace sobre la base de datos, es decir que el negativo resta dentro de la tabla diámica.

La última propuesta solo hace el conteo sobre las ventas positivas "+" de la base de datos, estrictamente mayores que "0", no usa los menores que cero.

Entonces en su conteo las "devoluciones" se cuenta como "cobertura", en el archivo propuesto no se consideraria como una cobertura los registros que corresponden a una devolución.

Así es que el conteo de códigos unicos propuesto no dará ese resultado esperado.

Se podría pensar en una solución usando Power Query.

 

Caso.xlsb

[GabrielRaigosa]

Si una venta en febrero "+", tiene una devolución "-" en ese periodo de una venta de un mes anterior y la devolución produce como resultado dentro de la tabla dinámica un valor negativo, el conteo que usted hace consideraria que en febrero no hay cobertura, por que ventas (+) sumado a "devoluciones" (-), daria un valor menor que cero.

¿Ese resultado seria lo que usted espera?

[Diego1991]

Revisé el archivo que me enviaste, tomando como ejemplo el primer vendedor Alcarraz Mendoza en el mes de febrero, el recuento que se hace en la columna D desde D7 hasta D90 (donde sí aparecen los valores 0), es 35 al igual que en la columna J desde J7 hasta J90 (el que tú hiciste). Pero en el conteo que hago utilizando la formula contar si conjunto aparece el resultado de 33 mientras que en el otro que hiciste sigue apareciendo 35, por lo que parecería que estaría contando los valores 0 a pesar de que no aparecen en esa tabla, no entiendo bien porque es que sigue suce diendo eso.

Caso (1).xlsb

[GabrielRaigosa]

Ahora viendo como es la situación hago una TD pero tomando como origen una Tabla hecha con PowerQuery, una consulta sobre los datos de origen y esá será la fuente de datos para la dinámica

Es decir, se agrupan los datos de la base de datos con las siguentes campos:

• Vendedor
• Codigo Cliente
• Año
• Mes
• Campo para el calculo la suma de las ventas

La tabla donde se muestra el "consolidado", no es obligatoria verla en una hoja, pues desde la consulta se puede generar la Tabla dinámica.

Entonces desde su tabla origen elabora la consulta y desde la consulta la dinámica.

 

 

Caso Con PowerQuery.xlsb

[GabrielRaigosa]

Si lo suyo son las bases de datos y aun no conoce el Power Query le animo a que investigue un poco sobre esa herramienta, en Youtube encuentra muchos videos.

Frecuentemente introducen mejoras en esta herramienta el potencial es inmenso.

 

[Gerson Pineda]

Saludos a ambos!

 

@Diego1991

Otra alternativa desde la Tabla dinámica, es con una formula Dax

Ventas mayor a cero Dax.zip

[Diego1991]

Muchas gracias @GabrielRaigosa , con la tabla hecha en Power Query se puedo obtener el resultado, la verdad no sé usar herramienta, tomaré su consejo e investigaré un poco más sobre eso.

[Diego1991]

Gracias @Gerson Pineda, le agradecería que pueda explicarme un poco que es una fórmula Dax, usándola pudo llegar al resultado pero no tengo idea de en que momento o cómo la ha utilizado, le agradecería si pudiera explicarme como es que lo hizo por favor.

[Gerson Pineda]

Hola Diego

Hace 14 horas, Diego1991 dijo:

Gracias @Gerson Pineda, le agradecería que pueda explicarme un poco que es una fórmula Dax, usándola pudo llegar al resultado pero no tengo idea de en que momento o cómo la ha utilizado, le agradecería si pudiera explicarme como es que lo hizo por favor.

La TD es uno de los inventos mas potentes en Excel, pero ademas de eso, Microsoft agrego una herramienta para llevarlo a lo mas alto en el análisis de cálculos y procesamiento de grandes cantidades de datos, ese complemento se llama Power Pivot, desde aquí pude realizar la formula, Dax es un lenguaje muy potente, con esto quedaron atrás los elementos y campos calculados de la TD

Para ver la formula, te vas a la pestaña de Power Pivot

1. Medidas / Administrar medidas
2. Administrar y en la parte inferior de la tabla, están las medidas
3. O en el panel o lista de campos de la TD, en la parte inferior de los nombres de campos, ademas desde ahí puedes dar clic derecho y das modificar medida

Es un mundo aparte y bastante extenso para realizar cálculos avanzados en las TD, se necesita como todo, leer para aprender

 

Saludos