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.