Análisis de ventas aplicando funciones de Excel ejemplo 2

Hola a todos aquí les dejo un nuevo post  en que realizaremos un formato interactivo para análisis de ventas en base a la problemática planteada.

Problemática planteada:

José trabaja en una mueblería en la que desempeña el puesto de supervisor  y tiene a su cargo un equipo de ventas conformado por 4 vendedores  dos para ciudad de Reynosa (José y marcos) y dos para la ciudad de Victoria (Luis y Adrian).  Don Luis el dueño de la mueblería le envía a José vía correo electrónico un archivo de Excel con un formato prediseñado con los registros de las ventas realizadas en el primer semestre 2015 de la ciudad de Reynosa y Victoria y le pide lo siguiente:

1.- En la celda H1 crear una lista desplegable con los meses del año, que servirá como parámetro para la consulta de los datos de ventas mensuales por vendedor y por ciudad.

2.- En el rango de celdas I5 a la I8 ingresar una función que le cuente el número de ventas que hizo cada vendedor para  el mes seleccionado.

3.- En el rango de celdas J5 a la J8 ingresar una función que sume el total de ventas que realizo cada vendedor para el mes seleccionado.

4.- En el rango K5 a la K8 ingresar una función que calcule el pago de una comisión para cada vendedor en base a los siguientes criterios; si las ventas realizadas  por el vendedor superan los $7,000 se le otorgara una comisión del 10%, y si las ventas son menores a $7,00 se les otorga una comisión del 5% sobre el total de las ventas realizadas en el mes seleccionado.  

5.- En el rango M5 a la M8 ingresar una función que sume el sueldo base más las comisiones que se le haya otorgado a cada vendedor en el mes seleccionado, tomando en cuenta que el sueldo base es fijo para todos los meses.

6.- En las celdas H12 y H13 ingresar una función que cuente el total de ventas que se realizaron para cada ciudad según el mes seleccionado.

7.- En las celdas I12 e I13 ingresar una función que sume todas las ventas que se realizaron para cada ciudad según el mes seleccionado.

A continuación se muestra el formato prediseñado que Don Luis le envió a José para que realice las actividades antes descritas:

Una vez leída las instrucciones de las actividades que le fueron solicitadas a José procede a llevar a cabo los procedimientos para dar solución a cada inciso.

1.- En la celda H1 crear una lista desplegable con los meses del año, que servirá como parámetro para la consulta de los datos de ventas mensuales por vendedor y por ciudad.

En primera instancia se procederá a crear la lista desplegable   

1.- Clic en el menú datos

2.- Clic en la opción validación de datos, se nos cargara en pantalla una ventana.

3.- Seleccionamos la pestana configuración.

4.- En la opción permitir seleccionamos lista.

5.- Se nos activara una caja de texto origen, en este parte escribiremos los meses de enero a diciembre separado por comas y damos clic en aceptar.

Con esto ya hemos creado la lista desplegables para este inciso como se muestra en la siguiente imagen:

Nota: Los meses se deben escribir exactamente como están el formato de las ventas, ya que de lo contrario al momento de asignarlo como parámetro no será encontrado en la búsqueda de datos.

2.- En el rango de celdas I5 a la I8 ingresar una función que le cuente el número de ventas que hizo cada vendedor para  el mes seleccionado.

Como nos podemos dar cuenta en este enunciado la función que se requiere para llevar a cabo este cálculo debe ser una función que permite establecer dos criterios, es decir, esta función debe contar las ventas realizadas siempre y cuando cumplan con el mes y el vendedor establecido en los criterios, para esto vamos a utilizar la función CONTAR.SI.CONJUNTO que nos permitirá realizar el conteo de las ventas en base a dos criterios .

3.- En el rango de celdas J5 a la J8 ingresar una función que sume el total de ventas que realizo cada vendedor para el mes seleccionado.

Para realizar este cálculo vamos a utilizar la función SUMAR.SI.CONJUNTO que realizara la suma de las ventas siempre y cuando se cumplan los criterios del mes y el vendedor establecido en los parámetros de la formula.

4.- En el rango K5 a la K8 ingresar una función que calcule el pago de una comisión para cada vendedor en base a los siguientes criterios; si las ventas realizadas  por el vendedor superan los $7,000 se le otorgara una comisión del 10%, y si las ventas son menores a $7,00 se les otorga una comisión del 5% sobre el total de las ventas realizadas en el mes seleccionado.  

Para realizar este cálculo vamos a utilizar la función SI que nos determinara que calculo se hace en relación a las condiciones establecidas en el inciso.

5.- En el rango M5 a la M8 ingresar una función que sume el sueldo base más las comisiones que se le haya otorgado a cada vendedor en el mes seleccionado, tomando en cuenta que el sueldo base es fijo para todos los meses.

Para realizar este cálculo solo usaremos la formula básica de suma que es sumar el valor de la celda de las comisiones con el del sueldo base.

6.- En las celdas H12 y H13 ingresar una función que cuente el total de ventas que se realizaron para cada ciudad según el mes seleccionado.

Para realizar este cálculo vamos utilizar nuevamente la función CONTAR.SI.CONJUNTO que nos permite realizar conteos en base a más de un criterio.

7.- En las celdas I12 e I13 ingresar una función que sume todas las ventas que se realizaron para cada ciudad según el mes seleccionado.

De igual forma para resolver este inciso vamos a utilizar la función SUMAR.SI.CONJUNTO que nos permite realizar sumas de datos con más de un criterio.

Una vez definido que funciones se harán uso para resolver cada inciso re procede a capturar cada una en la hoja de Excel, la cuales quedaran como se muestran en la siguiente imagen:

 

En la anterior imagen se muestran todas las funciones con sus respectivas sintaxis, un aspecto importante al que hay que poner atención es la parte de las funciones donde se adicionan signos de pesos en los argumentos, al agregar el signo de pesos a los rangos hacemos que los rangos sean referencias absolutas, es decir jamás cambiaran siempre serán del mismo tamaño, esto se hace con el fin proteger los rango especificados cuando se requiera arrastra la función para que se realice los cálculos de las siguientes filas y no se tengan errores por que los rangos se modificaron al arrastrar las funciones.

Ya que se terminaron de capturar todas las funciones nos quedara los resultados como se muestran en la siguiente imagen:

Aquí está el formato ya terminado con todas las funciones ingresadas y los resultados que retorna cada función, como se puede observar en la celda H1 se encuentra el mes que será el que se estará cambiando para que los datos se calculen en base al mes seleccionado de la lista desplegable. Así mismo este formato tiene la capacidad para que se puedan agregar más registros de venta en la tabla de ventas para el próximo semestre que resta del año 2015.

Como podemos darnos cuenta,  Excel cuenta con funciones que nos ayudan a minimizar el trabajo  tedioso de realizar los cálculos manualmente e ir llenado el formato para cada mes, ya que en nuestra vida diaria tanto en el hogar como en el trabajo se nos pueden presentar situaciones similares como las que se plantean en la problemática de este post y que se resuelven de forma rápida minimizando tiempo y trabajo haciendo más interactivo el uso de Excel.