Para el siguiente ejemplo, tenemos un modelo con venta de productos para el año 2021, y, las cantidades vendidas, este modelo esta en formato de tabla, y, se llama ventas.
Tenemos otra tabla, llamada productos, donde tenemos el producto, y, el precio de venta.
Vamos a agregar una columna a la tabla ventas, donde vamos a calcular, para ello, con la función BUSCARV, voy a buscar cada producto de la tabla ventas, en la tabla productos, y, que me devuelva la columna 2, con una coincidencia exacta.
En el argumento valor buscado, en vez de seleccionar la columna, seleccionamos la primera celda donde aparece el producto, vemos que la sintaxis cambia, quiere decir que va a comparar cada producto.
=BUSCARV([@Producto]
Como argumento matriz_tabla, seleccionamos la tabla productos.
=BUSCARV([@Producto];Productos;
Como indicador de columnas, ponemos 2, y, con una coincidencia exacta.
Cerramos paréntesis, y, aceptamos.
=BUSCARV([@Producto];Productos;2;FALSO)
Obtenemos una matriz desbordada con los precios de cada producto.
Ahora, debemos de multiplicar por la cantidad, pero igual que antes, no ponemos el nombre de la columna, sino que seleccionamos la celda con la primera cantidad.
=BUSCARV([@Producto];Productos;2;FALSO)*[@Cantidad]
Tenemos el total para cada fila.
Vamos a hablar de la clasificación, lo primero que vamos a hacer es calcular el total por producto, para ello, podemos usar la función SUMAR.SI.CONJUNTO, donde como argumento rango de suma, seleccionamos la columna que hemos añadido (Total).
=SUMAR.SI.CONJUNTO(Ventas[Total]
Como argumento rango de criterios1, seleccionamos la columna de producto de la tabla ventas.
=SUMAR.SI.CONJUNTO(Ventas[Total];Ventas[Producto]
Como argumento criterio1, seleccionamos la celda F9, donde se encuentra el primer producto.
Cerramos paréntesis, y, aceptamos.
=SUMAR.SI.CONJUNTO(Ventas[Total];Ventas[Producto];F9)
Lo ponemos en formato de moneda, sin decimales, y, tenemos el total para el primer producto.
Arrastramos, y, tenemos el total para cada producto.
Lo siguiente que vamos a hacer es clasificar los valores, tenemos una función llamada JERARQUIA.EQV, esta función nos va a devolver la clasificación de un numero respecto a los demás, esta función tiene tres argumentos, el primer de ellos es número, que es el numero que queremos clasificar, pues seleccionamos el primer número.
=JERARQUIA.EQV(G9
El siguiente argumento es referencia, que es el rango donde vamos a comparar, pues seleccionamos todos los valores.
=JERARQUIA.EQV(G9;G9:G13
El siguiente argumento, y opcional, es orden, por defecto, nos dará la posición 1, al numero mayor, pero en orden, podemos cambiarlo, lo voy a omitir, para que al numero mayor le dé la posición 1.
Cerramos paréntesis, y, aceptamos.
=JERARQUIA.EQV(G9;G9:G13)
Vemos que a la primera cantidad le asigna el valor 2, es decir, solo hay una cantidad mayor a ella.
Fijamos el argumento referencia, y, arrastramos.
=JERARQUIA.EQV(G9;$G$9:$G$13)
Vemos que el numero 1 corresponde a la cantidad mayor, y, el numero 5, a la cantidad menor.
Decir, que, si tenemos valores duplicados, se le asigna la misma posición.
Esto también podríamos hacerlo con una tabla dinámica, para ello, con una celda dentro del modelo, vamos a la pestaña de insertar, y, hacemos clic en tabla dinámica.
Se abre la ventana de tabla dinámica, marcamos hoja de calculo existente, y, la colocamos al lado del modelo.
Bajamos a filas, producto, y, a valores, bajamos dos veces la columna de total.
Hacemos clic en la flecha que apunta hacia abajo del segundo total, y, seleccionamos configuración de campo de valor.
Se abre la ventana de configuración de campo de valor, seleccionamos la pestaña mostrar valores como, desplegamos la ventana mostrar valores como, y, seleccionamos clasificar de mayor a menor.
Aceptamos.
Y, tenemos los mismos resultados.
Ahora, a la tabla de productos, le hemos añadido un fabricante.
Y, a la tabla de ventas, la columna de fabricante.
Vemos que hay productos que tienen el mismo fabricante.
Queremos clasificar por total, y, fabricante, quiere decir, que tenemos que hacer clasificaciones independientes, es decir, tenemos dos productos para el fabricante1, pues esos son los dos elementos que debemos de clasificar, después, tenemos otros dos productos para el fabricante2, son esos dos productos los que tenemos que clasificar, y, solo un producto para el fabricante3.
Esto lo vamos a hacer con la función SUMAPRODUCTO, para ello, escribimos la función, abrimos un paréntesis, y, abrimos otro paréntesis para poner la primera condición, donde seleccionamos el rango G9:G13, los fabricantes, y, lo igualamos al valor de G9, donde aparece el fabricante, cerramos paréntesis.
=SUMAPRODUCTO((G9:G13=G9)
Ponemos el símbolo de asterisco, que ya sabemos que es igual que el operador lógico Y, es decir, devuelve VERDADERO si todas las condiciones se cumplen, abrimos otro paréntesis, y, la siguiente condición que es que rango I9:I13, es decir, los totales, debe de ser mayor al valor de la celda I9, donde se encuentra el primer valor.
Cerramos paréntesis de la condición, y, paréntesis de la función.
Aceptamos.
=SUMAPRODUCTO((G9:G13=G9)*(I9:I13>I9))
Obtenemos como resultado cero, examinemos la función, la primera pregunta es si en el rango G9:G13, es igual al valor de G9.
Lo es, la segunda pregunta es si el rango I9:I13, es igual al valor de I9, y, también lo es.
Si selecciono las dos condiciones, y, pulso F9, vemos que nos devuelve todo ceros.
Voy a fijar las referencias de las condiciones, y, arrastro.
=SUMAPRODUCTO(($G$9:$G$13=G10)*($I$9:$I$13>I10))
Vemos que pone cero cuando hay coincidencia, y, la cantidad es mayor que la segunda, y, 1 cuando la cantidad es menor que la anterior, estos valores nos valdrían, pero es mejor verlo a partir de 1, por lo que, a la función anterior, le voy a sumar 1.
=SUMAPRODUCTO(($G$9:$G$13=G9)*($I$9:$I$13>I9))+1
Arrastramos.
Ya tenemos nuestra clasificación.
Vemos que, para el faricante2, el producto TV 32 pulgadas, se le asigna el valor 2, por que dicha cantidad es menor que para el producto frigorífico del fabricante2, por lo que ya tenemos nuestra clasificación por fabricante y total.
Igual que antes, podemos hacerlo con tablas dinámicas, para ello, con una celda dentro del modelo, vamos a la pestaña de insertar, y, hacemos clic en tabla dinámica, la colocamos al lado del modelo.
Bajamos a filas, fabricante, y, producto, y, a valores bajamos dos veces, la columna de total.
En la tabla dinámica, en la columna suma de total2, hacemos clic con botón alternativo de ratón, y, desplegamos mostrar valores como, y, seleccionamos clasificar de mayor a menor.
Se abre la ventana mostrar valores como, y, en la ventana campo base, seleccionamos la columna de producto.
Aceptamos, y, vemos que obtenemos la clasificación para cada producto dentro de cada fabricante.
Ahora, veamos como hacerlo en Power Pivot, para ello, nos llevamos ambos modelos a Power Pivot, para ello, con una celda dentro del primer modelo, vamos a la pestaña de Power Pivot, y, hacemos clic en agregar a modelo de datos.
Hacemos lo mismo para el segundo modelo.
Vemos que la fecha aparece junto con la hora, pero no tenemos hora, por lo que, desde la pestaña de inicio, desplegamos tipo de datos, y, seleccionamos:
La columna de cantidad la pongo en número entero.
Y, la columna de total en formato de moneda.
Vamos a la vista de diagrama, dentro de la pestaña de inicio.
Arrastramos la columna de producto de la tabla venta a la columna de producto de la tabla producto, se crea una relación de uno a varios, en la columna producto de la tabla producto los registros son únicos, mientras que en la columna producto de la tabla venta, se pueden repetir.
Volvemos a la vista de datos, a la tabla de venta.
En el área de cálculo, voy a crear una medida, llamada totales, donde usare la función SUM, para sumar la columna de total, y, lo ponemos en formato de moneda.
Totales:=SUM(Venta[Total])
Tenemos el total de la columna total.
Ahora, tenemos que crear nuestra tabla de clasificación.
En Excel hemos usado la función JERARQUIA.EQV, en DAX, tenemos la función RANKX, que devuelve la clasificación de un número en una lista de números.
Vamos a crear otra medida en el área de cálculo, la llamare clasificación, donde pondré la función RANKX.
Clasificacion:=RANKX(
El primer argumento es tabla, puede cualquier expresión que haga referencia a una tabla, necesitamos trabajar con la columna de productos, pero con todos los productos de la tabla producto, por lo que aplicare la función ALL a la columna de producto.
Clasificacion:=RANKX(ALL(Producto[Productos])
Entregará una tabla única de productos como una tabla.
El siguiente argumento es expresión, este argumento se evalúa para cada fila, y, genera los valores posibles para su clasificación, pues, entre corchetes, seleccionamos la medida de totales.
Clasificacion:=RANKX(ALL(Producto[Productos]);[Totales]
Los siguientes tres argumentos son opcionales, siendo el siguiente argumento valor, que nos va a devolver un único valor el cual queremos clasificar, el siguiente argumento es orden, que puede tener los valores 1, para VERDADERO, y, 0, para FALSO, y, el valor predeterminado es descendente, por lo que el número mayor será el primero en la clasificación, y, el ultimo argumento es ties, que es la enumeración que define cómo determinar la clasificación cuando hay asociaciones, puede tener le valor de omitir, que es el predeterminado, o, dense, si dejamos como predeterminado el valor omitir, por ejemplo, si el numero 3, aparece tres veces, y, tiene la clasificación de 5, entonces, al siguiente numero se le da una clasificación de 5 + 3, es decir, 8, y, el argumento dense es al contrario, es decir, si tenemos el numero 3 que aparece tres veces, y, su clasificación es 5, pues, el siguiente valor recibe la clasificación de 6.
De momento, omitimos los tres argumentos.
Cerramos paréntesis, y, aceptamos.
Como resultado tenemos 1.
Lo siguiente es crear una tabla dinámica, para ello, desde la pestaña de inicio, desplegamos tabla dinámica, y, seleccionamos tabla dinámica.
Se abre la ventana de crear tabla dinámica, seleccionamos hoja de cálculo existente y la colocamos al lado del modelo.
Aceptamos.
Nos llevamos a filas, productos, y, a valores la medida de totales, y, clasificación.
Vemos el producto, el total, y, su clasificación.
Voy a quitar los totales, para ello, con una celda dentro de la tabla dinámica, vamos a la pestaña de diseño, desplegamos totales generales, y, seleccionamos desactivado para filas y columnas
Cada vez que tenemos una medida dentro de otra, hay una función de cálculo dentro, la función CALCULATE, siempre lleva asociado un contexto de filtro, pero también nos trae un contexto de filtro existente, y, lo lleva a la medida, por este motivo, el calculo se realiza fila a fila, voy a crear otra medida, que va a ser igual que la medida clasificación, pero cambio la medida de totales, por la función SUM.
Clasificacion2:=RANKX(ALL(Producto[Productos]);SUM(Venta[Total]))
Nos la llevamos a valores de la tabla dinámica, y, vemos que nos devuelve para todo 1.
La primera cantidad, la función SUMA, hace el caculo correctamente, pero cuando itera sobre las siguientes cantidades, siempre obtiene la misma cantidad, esto lo podemos arreglar con la función CALCULATE aplicada a la función SUM, donde solo usamos el argumento expresión, que es la función SUM, no hace falta un contexto de filtro, porque ya viene de uno.
Clasificacion2:=RANKX(ALL(Producto[Productos]);CALCULATE(SUM(Venta[Total])))
Si volvemos a la tabla dinámica, vemos que ahora si se ha clasificado correctamente, por lo que podemos deshacernos de la medida de totales, o, simplemente desmárcala de la tabla dinámica.
Ahora, tenemos que clasificar los productos por fabricante, en la expresión anterior, hemos usado ALL para anular cualquier filtro aplicado a la columna productos, lo que devuelve todos los valores únicos de dicha columna, pero, si vamos a la expresión, y, dejamos solo la tabla, anulara cualquier filtro aplicado a dicha tabla, volvemos a Power Pivot, podemos usar el método abreviado ALT mas TAB, y, copiar la expresión anterior, creamos una medida nueva, y, la pegamos, pero en la función ALL, dejamos solo el nombre de la tabla.
Fabri_produc:=RANKX(ALL(Producto);CALCULATE(SUM(Venta[Total])))
Volvemos a la tabla dinámica, bajamos a filas el fabricante, y, a valores, la medida Fabri_produc.
Vemos los productos por fabricante, y, su clasificación.
Pues ya tenemos nuestro ejemplo completado.
Comments