EJERCICIO 6

Ventas comercial

Objetivo: Utilización de funciones comunes.

El siguiente ejercicio consiste en controlar las ventas por comercial de una empresa de alimentación. 1. Crearemos un libro llamado “Ventas Comercial” con 2 hojas de datos: “Comerciales” y “Gráficos”. En la hoja “Comerciales” construiremos la siguiente tabla con los datos indicados:

2. Introducir la fórmula adecuada en la columna B para que se presente el número de ventas de cada comercial.

3. Introducir la fórmula adecuada en la columna C para que se presente el total de ventas de cada comercial.

4. Crear un gráfico en la hoja “Gráficos” que presente las ventas de cada comercial.

Solución:

1. Lo primero que haremos será cambiar el nombre a cada hoja, para ello damos clic con el botón derecho de ratón sobre la pestaña de la hoja y elegimos “Cambiar nombre”, introduciendo el nombre indicado a cada hoja. La tercera hoja que crea Excel por defecto deberemos borrarla. Como la segunda hoja es para contener el gráfico, podemos crearla cuando introduzcamos el gráfico.

A continuación introduciremos la hoja “Comerciales” como se indica, dando formato moneda a la columna H y la Columna J.

2. Tenemos que contar el número de ventas de cada comercial. Para ello utilizamos en la celda la B2 la función CONTAR.SI que cuenta las celdas, dentro del rango, que no están en blanco y que cumplen con el criterio especificado. Esta función tiene los siguiente argumentos:

• Rango: es el rango dentro del cual desea contar el número de celdas que no están en blanco.

• Criterio: es el criterio en forma de número, expresión o texto, que determina las celdas que se van a contar.

Por ejemplo, para la celda B2 tenemos que contar en el rango E2:E20 las celdas que son iguales a A2: B2 = CONTAR.SI(E2:E20,A2).

Para B3:

B3 = CONTAR.SI(E2:E20,A3).

Observamos que podemos copiar la fórmula de la celda B2 al resto de la columna B, pero utilizando adecuadamente las referencias absolutas y relativas. Deberemos de usar referencias absolutas para el rango y relativas para el criterio:

B2 = CONTAR.SI($E$2:$E$20,A2).

Copiando el valor de la celda B2 al resto de la columna B obtendremos los siguientes resultados:

3. Para calcular los valores de la columna C, deberemos usar la función de suma condicional SUMAR.SI que suma las celdas en el rango que coinciden con el argumento criterio. Tiene los siguientes argumentos:

• Rango: es el rango de celdas que desea evaluar.

• Criterio: es el criterio en forma de número, expresión o texto, que determina qué celdas se van a sumar.

• Rango_suma: son las celdas que se van a sumar.

Las celdas contenidas en rango_suma se suman sólo si las celdas correspondientes del rango coinciden con el criterio. Si rango_suma se omite, se suman las celdas contenidas en el argumento rango. Aplicando la fórmula anterior tendremos:

C2 = SUMAR.SI(E2:E20,A2,J2:J20)

C3 = SUMAR.SI(E2:E20,A3,J2:J20)

Podemos introducir la fórmula en cada casilla de la columna C o copiar el valor de C2 al resto de la columna, modificando su fórmula con las referencias absolutas adecuadas, quedando:

C2 = SUMAR.SI($E$2:$E$20,A2,$J$2:$J$20)

El resultado deseado será el siguiente:

Nota: De la forma que hemos configurado nuestra hoja de cálculo no podremos añadir nuevos datos para los vendedores, la forma de solucionarlo será cambiar el final 51 de los rangos (ahora está en la fila 20) y colocar el número 65536, que es la última fina que introduce Excel en su hojas. Las fórmulas quedan:

B2 = CONTAR.SI($E$2:$E$65536,A2)

C2 = SUMAR.SI($E$2:$E$65536,A2,$J$2:$J$65536)

4. Para crear el gráfico pedido deberemos seleccionar las columnas de datos que queremos que aparezcan. En nuestro caso seleccionaremos A2:A11 y C2:C11 y a continuación pulsamos