En el siguiente ejemplo, tenemos un modelo donde tenemos un comercial, un producto, y, unas ventas, es decir, un comercial vende un producto.
Tenemos otro modelo, donde tenemos el comercial, el producto, y, el porcentaje que le corresponde de comisión por la venta de un producto.
Primero, queremos calcular la comisión de cada comercial para cada producto.
Lo voy a hacer con el condicional SI anidado, para ello, escribo el signo igual, pongo el condicional SI, abro un paréntesis.
Como argumento prueba lógica, selecciono el rango F4:F19, que son los comerciales, donde aparece el porcentaje, y, lo igual al de K4, el primer comercial.
=SI(F4:F19=K4
Punto y coma, como argumento valor si verdadero, ponemos otro condicional SI, seleccionamos el rango G4:G19, que son los productos donde se encuentran los porcentajes, y, lo igualamos al valor de la celda L3, que es el primer producto, es el argumento prueba lógica.
=SI(F4:F19=K4;SI(G4:G19=L3
Punto y coma, como argumento valor si verdadero del segundo condicional SI, seleccionamos el rango H4:H19, que son los porcentajes.
=SI(F4:F19=K4;SI(G4:G19=L3;H4:H19
Punto y coma, como argumento valor si falso, del segundo SI, ponemos un texto en blanco, y, cerramos paréntesis del segundo SI.
=SI(F4:F19=K4;SI(G4:G19=L3;H4:H19;"")
Punto y coma, y, como argumento valor si falso del primer SI, ponemos otro texto en blanco, y, cerramos paréntesis del primer SI.
Aceptamos, y, obtenemos una matriz desbordada con blanco donde no hay coincidencia, y, el porcentaje de descuento donde hay coincidencia.
Vamos a usar la función CONCAT, después del signo igual, esta función une cadenas, e, ignora las celdas que están vacías, por lo que el porcentaje que hemos obtenido quedara en una sola celda.
=CONCAT(SI(F4:F19=K4;SI(G4:G19=L3;H4:H19;"");""))
Ahora, debemos de multiplicar este porcentaje por las ventas del comercial Jaime, para ello, voy a usar la función SUMAR.SI.CONJUNTO, suelo usar esta función, aunque solo tenga un criterio, la diferencia es que la función SUMAR.SI, el argumento rango de suma es el último, y, en la función SUMAR.SI.CONJUNTO, el argumento rango de suma es el primero.
Después de la expresión anterior, ponemos el símbolo de multiplicación (*), y ponemos la función SUMAR.SI.CONJUNTO, como argumento rango de suma, seleccionamos las ventas.
=CONCAT(SI(F4:F19=K4;SI(G4:G19=L3;H4:H19;"");""))*SUMAR.SI.CONJUNTO(D4:D59
Punto y coma, como argumento rango de criterios1, seleccionamos el rango B4:B59, que son los comerciales donde tenemos las ventas.
=CONCAT(SI(F4:F19=K4;SI(G4:G19=L3;H4:H19;"");""))*SUMAR.SI.CONJUNTO(D4:D59;B4:B59
Punto y coma, como argumento criterio1, seleccionamos la celda K4.
=CONCAT(SI(F4:F19=K4;SI(G4:G19=L3;H4:H19;"");""))*SUMAR.SI.CONJUNTO(D4:D59;B4:B59;K4
Punto y coma, como argumento rango de criterios2, seleccionamos el rango C4:C59, que son los productos vendidos.
=CONCAT(SI(F4:F19=K4;SI(G4:G19=L3;H4:H19;"");""))*SUMAR.SI.CONJUNTO(D4:D59;B4:B59;K4;C4:C59
Punto y coma, como argumento criterio2, seleccionamos la celda L3, cerramos paréntesis y aceptamos.
=CONCAT(SI(F4:F19=K4;SI(G4:G19=L3;H4:H19;"");""))*SUMAR.SI.CONJUNTO(D4:D59;B4:B59;K4;C4:C59;L3)
Ya tenemos la comisión del primer producto para el primer comercial.
Ahora, fijamos las siguientes referencias antes de copiar, donde K4, solo debemos de fijar la columna, para que al copiar hacia la derecha no se actualice, pero al copiar hacia abajo, la fila se actualice, y, la celda L3, solo fijamos la fila, para que, al copiar hacia la derecha, la columna se actualice, pero al copiar hacia abajo, la fila no se actualice.
=CONCAT(SI($F$4:$F$19=$K4;SI($G$4:$G$19=L$3;$H$4:$H$19;"");""))*SUMAR.SI.CONJUNTO($D$4:$D$59;$B$4:$B$59;$K4;$C$4:$C$59;L$3)
Aceptamos, pulsamos CTRL mas C para copiar la función, señalamos todo el rango, y, pulsamos CTRL mas V para pegar, y, ya tenemos las comisiones de cada comercial para cada producto.
Ahora, queremos calcular el total de comisiones para cada vendedor, es decir, la suma de cada porcentaje de cada producto por las ventas del comercial, para ello, vamos a usar la función BUSCARX.
En la celda K11, escribimos el signo igual, seguido de BUSCARX, y, abrimos un paréntesis.
=BUSCARX(
Como argumento valor buscado, concatenamos los rangos B4:B59, y, C4:C59.
=BUSCARX(B4:B59&C4:C59
Punto y coma, como argumento matriz de búsqueda, concatenamos el rango F4:F19, y, G4:G19.
=BUSCARX(B4:B59&C4:C59;F4:F19&G4:G19
Punto y coma, como argumento matriz devuelta, seleccionamos el rango H4:H19, cerramos paréntesis, y, aceptamos.
=BUSCARX(B4:B59&C4:C59;F4:F19&G4:G19;H4:H19)
Obtenemos una matriz desbordada con los porcentajes que le corresponde a cada vendedor y el producto vendido por dicho comercial.
Para ello, después de la expresión anterior, ponemos el símbolo de asterisco (*), que es igual que el operador lógico Y, es decir, devuelve VERDADERO si todas las condiciones se cumplen, abrimos un paréntesis, para poner la primera condición, donde seleccionamos el rango B4:B59, y, lo igualamos a la celda J11, que es el primer comercial, y, cerramos paréntesis.
=BUSCARX(B4:B59&C4:C59;F4:F19&G4:G19;H4:H19)*(B4:B59=J11)
Ponemos de nuevo el símbolo de asterisco, abrimos otro paréntesis, seleccionamos el rango D4:D59, que son las ventas, cerramos paréntesis y aceptamos.
=BUSCARX(B4:B59&C4:C59;F4:F19&G4:G19;H4:H19)*(B4:B59=J11)*(D4:D59)
Obtenemos una matriz desbordada con las comisiones, pues, después del signo igual ponemos la función SUMA, y, obtenemos el total de comisiones para el comercial Jaime.
=SUMA(BUSCARX(B4:B59&C4:C59;F4:F19&G4:G19;H4:H19)*(B4:B59=J11)*(D4:D59))
Fijamos las siguientes referencias.
=SUMA(BUSCARX($B$4:$B$59&$C$4:$C$59;$F$4:$F$19&$G$4:$G$19;$H$4:$H$19)*($B$4:$B$59=J11)*($D$4:$D$59))
Arrastramos, y, tenemos las comisiones para cada comercial.
Comments