El siguiente ejemplo, que vamos a ver, es una actividad que una alumna me pidió ayuda para poder resolverlo, y, entre los dos lo hicimos.
Bien, un hotel nos ha solicitado calcular las comisiones, que deben de cobrar cada comercial, o, agente por las reservas realizadas en dicho hotel.
Dentro de la hoja de cálculo, tenemos un cuadro, donde debemos de seleccionar un agente, a continuación, debemos de calcular la facturación de dicho agente, las comisiones 1, 2, y 3, y, el total.
Debajo de este cuadro, tenemos el cuadro de listado de reserva.
En la parte derecha de la hoja de cálculo, tenemos las comisiones que corresponden con cada agente, el precio de cada habitación, el régimen, comisión por número de reservas, y, la comisión por día de facturación.
Lo primero que nos pide es:
· En la celda C7 debemos de seleccionar un agente sobre el que deseamos calcular su comisión.
Lo primero que vamos a hacer es crear una desplegable en la celda C7, donde podamos el elegir un agente.
Para ello, teniendo la celda seleccionada vamos a la pestaña datos, dentro del grupo herramientas de datos, hacemos clic en validación de datos.
Desplegamos permitir, y, elegimos lista.
En la ventana origen, seleccionamos los agentes.
Aceptamos.
· En el rango A17:H39 se encuentran las reservas que los diferentes agentes han realizado en el periodo de cómputo de la liquidación.
· El importe total facturado por todos los agentes se calcula en la celdilla H15.
· En la celda C8 se calcula la facturación obtenida por el agente seleccionado en la celda C7.
· En la celda C9 se calcula la comisión1, que es la comisión por facturación del agente. El porcentaje de comisión que se lleva cada agente se encuentra en el rango L3:M6 y el importe facturado se calcula en C8.
· En la celda C10 se calcula la comisión2, la cual consiste en un determinado porcentaje del importe facturado por el agente. El agente tendrá derecho a dicha comisión si cumple con el criterio establecido para dicha comisión, el cual se encuentra indicado en el rango L20:M20. En esta ocasión se establece que si el agente ha realizado al menos 6 reservas tendrá derecho a una comisión del 1%.
· En la celda C11 se calcula la comisión3, la cual consiste en un determinado porcentaje del importe facturado por el agente en una fecha determinada. El agente tendrá derecho a dicha comisión si cumple con los criterios establecidos en el rango M24:M25. En esta ocasión se establece una comisión del 5% (celda M25) para la facturación realizada el 28/02/2012 (celda M24) (día de Andalucía).
· En la celdilla C12 se calcula la comisión total a la que tiene derecho el agente.
Pues, en base a este modelo, debemos de solucionar las siguientes preguntas:
1.
Cuál sería la fórmula de la celdilla H17, la cual calcula el importe de la primera de las reservas.
Para ello hay que tener en cuenta el precio por noche de cada tipo de habitación (DO, DS. JS y S) indicado en el rango M9:P10, el número de noches reservadas (D17:D39), y el régimen alimentario contratado (AL, AD, MP y PC) por cada cliente (G17:G39), cuyos precios por persona se encuentran en el rango L13:M16. Recordar que el régimen se aplica a todos los días reservados y que son precios por persona. Dicha fórmula debe poder copiarse al rango H18:H39 de forma que se calculen el importe de todas las reservas.
Bien, nos colocamos en la celda H17.
Vemos que hay dos noches, con un tipo de habitación DO, con el pax 1, y, con un régimen de AL.
Si nos fijamos en la parte derecha, vemos los precios de los distintos tipos de habitación.
Tenemos los precios de los diferentes regímenes.
Pues, vamos a realizar el primer calculo, lo primero que voy a hacer es usar la función SI.CONJUNTO, para calcular el precio de la habitación, donde voy a comparar el valor de la celda E17, con cada valor del rango M9:P9, y, si hay coincidencia, pues que ponga el valor inferior, es decir, el precio de la habitación.
=SI.CONJUNTO(E17=M9;M10;E17=N9;N10;E17=O9;O10;E17=P9;P10)
Aceptamos, y, ya tenemos el precio de la habitación.
Ahora, tenemos que sumar el régimen, para ello, después de la función anterior, ponemos el singo +, y, vuelvo a hacer uso de la función SI.CONJUNTO para extraer el precio del régimen, en este caso, los datos se encuentran en vertical.
=SI.CONJUNTO(E17=M9;M10;E17=N9;N10;E17=O9;O10;E17=P9;P10)+SI.CONJUNTO(G17=L13;M13;G17=L14;M14;G17=L15;M15;G17=L16;M16)
Por último, este resultado, lo multiplicamos por el número de noches, que la pondremos después del signo igual, ponemos el símbolo de multiplicación, y, las operaciones anteriores, la cerramos entre paréntesis, por la prioridad de operadores.
=D17*(SI.CONJUNTO(E17=M9;M10;E17=N9;N10;E17=O9;O10;E17=P9;P10)+SI.CONJUNTO(G17=L13;M13;G17=L14;M14;G17=L15;M15;G17=L16;M16))
Aceptamos.
Ya tenemos el precio de la primera fila, ahora, debemos de arrastrar hacia abajo, pero antes, debemos de fijar algunas celdas, debemos de fijar aquellos rangos de celdas que no van a cambiar al copiar.
=D17*(SI.CONJUNTO(E17=$M$9;$M$10;E17=$N$9;$N$10;E17=$O$9;$O$10;E17=$P$9;$P$10)+SI.CONJUNTO(G17=$L$13;$M$13;G17=$L$14;$M$14;G17=$L$15;$M$15;G17=$L$16;$M$16))
Ya tenemos rellena la columna de importe alojamiento.
En la celda H15, calculamos el total, que lo haremos con la función SUMA, de la siguiente manera:
=SUMA(H17:H39)
Indicar cual sería la fórmula de la celdilla C8, la cual calcula el importe facturado por el agente seleccionado en la celda C7.
Para este caso, vamos a hacer uso de la función SUMAR.SI, porque solo vamos a especificar un registro.
=SUMAR.SI(A17:A39;C7;H17:H39)
Aceptamos, y, y ya tenemos el total del agente seleccionado.
Lo siguiente que debemos de hacer, es indicar cual sería la fórmula de la celdilla C9, la cual calcula el importe de la comisión1. El agente tendrá derecho a dicha comisión si cumple con el criterio establecido para dicha comisión, el cual se encuentra indicado en el rango L3:M6.
El porcentaje se aplicará sobre lo facturado por el agente.
Pues igual que antes, hacemos uso de la función SI.CONJUNTO, para saber el porcentaje que corresponde a dicho agente.
=SI.CONJUNTO(C7=M3;L3;C7=M4;L4;C7=M5;L5;C7=M6;L6)
Aceptamos, y, vemos el porcentaje correspondiente.
Ahora, lo multiplicamos por el total facturado por dicho agente.
=C8*SI.CONJUNTO(C7=M3;L3;C7=M4;L4;C7=M5;L5;C7=M6;L6)
Lo siguiente, es indicar cual sería la fórmula de la celdilla C10, la cual calcula el importe de la comisión2. El agente tendrá derecho a dicha comisión si cumple con el criterio establecido para dicha comisión, el cual se encuentra indicado en el rango L20:M20. En esta ocasión se establece que si el agente ha realizado al menos 6 reservas tendrá derecho a una comisión del 1,5% sobre el total facturado.
En este caso, para saber las veces que aparece el agente seleccionado en el listado de reservas, vamos a hacer uso de la función CONTAR.SI.
=CONTAR.SI(A17:A39;C7)
Ahora, debemos de preguntar, que si el resultado de la función CONTAR.SI es igual o mayor a seis, vamos a multiplicar lo facturado por el agente por 1,5%, para ello, vamos a hacer uso del condicional SI, donde si se cumple la condición, que se realice la operación, en caso contrario, que ponga un texto en blanco.
=SI(CONTAR.SI(A17:A39;C7)>=6;C8*1,5%;"")
Lo siguiente es indicar cual sería la fórmula de la celdilla C11, la cual calcula el importe de la comisión3. El agente tendrá derecho a dicha comisión si cumple el criterio establecido para dicha comisión, el cual se encuentra indicado en el rango M24:M25. En esta ocasión se establece una comisión del 5% (celda M25) para la facturación realizada el 28/02/2012 (celda M24).
Para ello, vamos a hacer uso de nuevo de la función CONTAR.SI, donde vamos a ver cuántas veces aparece dicha fecha para el agente seleccionado.
=CONTAR.SI(C17:C39;M24)
Lo siguiente es multiplicar este resultado por la facturación de dicho agente.
=CONTAR.SI(C17:C39;M24)*C8
Por último, encerramos entre paréntesis esta función, y, lo multiplicamos por el 5%.
=(CONTAR.SI(C17:C39;M24)*C8)*5%
Por último, en la celda de total, calculamos la suma de las cantidades a cobrar por el agente seleccionado, podemos, por ejemplo, realizar un auto suma, para ello, nos colocamos en la celda C12, y, pulsamos ALT más igual.
Comments