Prácticos Excel Grupal

Ejercicio Referencias absolutas, relativas y mixtas

- Diseñar en la hoja1 una planilla que permita registrar las comisiones de un grupo de vendedores de una firma comercial con el siguiente formato:

	A	B	C	D	E	F	G	H
1
2
3	Vendedor	Cant.Vend.	Monto	Comisión
4
5
6
7

- Renombrar la hoja1 a Comisiones

- Se  deben  ingresar  los  datos  correspondientes a: Nombre del Vendedor, Cantidad Vendida y Monto para cada uno de los miembros del equipo de vendedores, que está compuesto por 15 personas.

- La comisión por ventas asciende al 10% del monto vendido. Ese porcentaje debe consignarse en una celda separada de la planilla de liquidación de comisiones.

- Se debe calcular el monto total vendido por la firma y el monto total de comisiones abonadas.

- Además, se debe calcular en una quinta columna el porcentaje vendido por cada vendedor con relación al total vendido por la firma.

- Aplicar formatos de bordes, tramas y colores a las celdas. Los montos de ventas y comisiones deben presentar dos decimales. Los totales deben consignar el rótulo correspondiente.

Ejercicio Referencias absolutas, relativas y mixtas

- Se pretende diseñar en la hoja2, una planilla que permita registrar las ventas de un comercio con el siguiente formato:

	A	B	C	D	E	F	G	H
1								0,25
2								% desc.
3	Artículo	Cantidad	Precio	Importe	Descuento	Total
4
5
6
7

Ingresar   diez   artículos,   la   cantidad   vendida   de   cada   uno   de   ellos   y el   precio  unitario correspondiente.

- Las columnas restantes se calculan considerando que:

Importe: Cantidad * Precio

Descuento: Importe * Porcentaje de descuento consignado

Total: Importe – Descuento

- En función de lo expresado anteriormente, para calcular el  Importe  del primer artículo (celda D4)  la  fórmula  sería  =B4*C4.  Luego,  se  copia  esa  fórmula  para  el  resto  de  las  filas  y  se comprueba el resultado.

- Para  calcular  el   Descuento,  se  define  la  siguiente  fórmula  para  el  primer  artículo  =D4*H1. Copiar la misma para el resto de las filas. Si se observa, el resultado obtenido es erróneo. Para comprobarlo, recorrer la fórmula a lo largo de las filas donde se ha copiado. Se advierte que las referencias al ser relativas fueron adaptándose a las filas a las que se copiaba y para la primera parte de la fórmula (D4) es correcto pero para la segunda (H1), no. En ésta se ha incrementado la  referencia  de  la  fila  y  eso  ha  determinado  que  la  fórmula  referencie  celdas  que  no corresponden. Para estos casos se debe utilizar referencias absolutas o mixtas, es decir, se fija aquella parte del argumento que no debe variar con el signo $. En este caso, lo que no debe variar es la fila, por lo que se debe fijar su referencia: H$1. (Para editar la fórmula se presiona la tecla F2 y luego se modifica). Copiarla para el resto de las filas que corresponda.

- Para obtener el Total, se resta el monto de descuento al importe.

- Luego, para terminar, se suma la columna Total.

• Las fórmulas y funciones utilizadas serían:

Celda	Fórmula/funciones
D4	=B4*C4
E4	=D4*H$1
F4	=D4-E4
F15	=SUMA(F4:F14)

- Aplicar formatos de borde, tramas y número que se consideren apropiados.

• Guardar la planilla con el nombre Referencias Absolutas, Relativas y Mixtas.xls

Ejercicio Porcentajes

- Transcribir la planilla que se muestra a continuación:

	A	B	C	D	E	F	G	H
1
2
3	Región	Abril	Mayo	Junio
4	Norte	1000	1300	1300
5	Este	1300	1600	1600
6	Oeste	1400	1500	1400
7	Sur	1100	1100	1100
8	Total

Insertar tres columnas luego de Región para agregar los datos correspondientes a los meses de Enero, Febrero y Marzo.

Insertar tres filas en la parte superior para agregar los títulos:

Cuadro I

Líneas Aéreas

Informe Mensual de Ventas por Regiones

Obtener totales de ventas por mes y por región. Agregar una última columna “Proyección”, que consigne un supuesto de ventas para el mes de Julio calculado en un 10% más de lo vendido en Junio  para  cada  región,  excepto  la  Sur,  que  se  estima  se  incrementará  en  un  15%.  Estos porcentajes se deben consignar en celdas separadas, con sus correspondientes rótulos.

Calcular el porcentaje de ventas que cada región tiene en el total vendido del semestre.

Ejercicio Funciones

Se pretende realizar un ejercicio que permita aplicar algunas funciones estadísticas de Excel.

A partir de los datos de los alumnos de un curso con las notas de los tres primeros trimestres, se pretende realizar una serie de cálculos estadísticos, utilizando alguna de las funciones cuya sintaxis se presenta a continuación:

=PROMEDIO(Número1;Número2;......)

Función  que  nos  devolverá  la  media  aritmética  de los números o el rango encerrado entre paréntesis

=MAX(Números) =MIN(Números)

Estas  funciones  devuelven  los  valores  máximo  y mínimo respectivamente de una lista de números.

=MODA(Números)

Valor que más se repite en un rango

=CONTAR(Rango)

Cuenta    las    veces    que    aparece    un    elemento numérico en una lista.

=CONTARA(Rango)

Cuenta las veces que aparece un elemento de texto en una lista

=CONTAR.SI(Rango)

Cuenta las celdas no vacías de un rango. Cuenta  las  celdas,  dentro  del  rango,  que  no  están en blanco y que cumplen con el criterio especificado.

=MEDIANA(Números)

Número que se encuentra en medio de un conjunto de  números,  es  decir,  la  mitad  de  los  números  es mayor que la mediana y la otra mitad es menor. Ejemplos:

=PROMEDIO(12;12;13) devolverá 12,33333

=PROMEDIO(A1:D13) devolverá el promedio del rango A1:D13

• Para comenzar, generar una tabla con los siguientes encabezados

	A	B	C	D	E	F
1	Alumno	1er. Trim.	2do. Trim.	3er. Trim.	Nota	Evaluación

Luego se cargan los siguientes datos:

2	Javier Pérez	2,5	3,0	5,0
3	Ana Valle	9,5	8,0	4,5
4	Juan Clos	6,0	6,5	6,0
5	Pedro Muñoz	7,0	4,0	5,5
6	Silvia Clos	1,5	8,0	6,0
7	Luis Rodó	6,0	5,5	8,5
8	Joaquín Vila	4,5	3,5	9,0
9	Elena Sánchez	9,0	6,5	4,0
10	Antonio Sati	0,5	6,0	2,0
11	Elvira Santi	7,0	7,5	6,0
12
12 13 14 15	Nota máxima Nota mínima Nota más repetida Mediana de notas
16
17
18					Nº alumnos:
19
20
21	Nº %
22 23 24	Insuficientes Aprobados Notables Excelentes

- A partir de la tabla, se debe calcular (celdas sombreadas):

En la columna E la nota final, que será la media de los tres trimestres.

En la columna F la evaluación en forma de texto, considerando los siguientes criterios:

Si la nota media es de 0 a 3,99, deberá consignar la palabra Insuficiente

Si la nota media es de 4 a 6,99, deberá consignar la palabra Aprobado

Si la nota media es de 7 a 9,5, deberá consignar la palabra Notable

Si la nota media es de 9,6 a 10, deberá consignar la palabra Excelente

En las celdas inferiores se calculará la mediana, notas máxima y mínima, y moda

Finalmente, se definirán las funciones que permitan conocer:

El número de alumnos

El número de insuficientes, aprobados, notables y excelentes

• El porcentaje que representa cada uno de los anteriores sobre el total

Celda	Fórmula/funciones
E2	=PROMEDIO(B2:D2)
F2	=SI(E2<3,99;"Insuficiente";SI(E2<6,99;"Aprobado";SI(E2<9,5;"Notable";SI(E2<10;"Excelente"))))
B21	=CONTAR.SI($F$2:$F$11;"Insuficiente")
C21	=B21/$F$17
F17	=CONTARA(A2:A11)

- Guardar la planilla con el nombre Funcion Si Anidado.xls en su carpeta correspondiente.

Ejercicio Funcion Sumar.si

Se pretende desarrollar una planilla que permita ingresar los montos asignados por créditos a las áreas que se detallan, calcular la diferencia entre lo asignado y ejecutado y luego los porcentajes correspondientes.

	A	B	C	D	E	F	G
1
2	Área	Crédito	Ejecutado	Diferencia	% ejecutado	% sin ejec.
3	Economía	15.500	15.000
4	Obras Públicas	22.400	19.700
5	Educación	12.800	9.300
6	Economía	25.000	23.200
7	Obras Públicas	34.000	28.900
8	Educación	15.500	13.600
9
10	Totales
11	Economía
12	Obras Públicas
13	Educación

Adicionalmente,  se  debe  calcular  el  monto  de  crédito  total  para  cada  área.  Para  el caso  de  Economía,  la fórmula sería:

B11  =SUMAR.SI(A3:A8;A11;B3:B8)

Rango: Rango  de  celdas  donde se busca el criterio

Criterio: que determina qué celdas se van a sumar

Rango Suma: Rango de celdas que se van   a   sumar   sólo   si coinciden con el criterio

Copiar  la  función  anterior  para  calcular  los  totales de crédito de las otras áreas y de las otras dos columnas.

Analizar el resultado. Efectuar las modificaciones necesarias (de referencia) para que sea correcto.

Ejercicio 7 Función Si Y  

En  este  caso  se  pretende  llevar  un  control  de  entradas  y  salidas  de  dinero  al  estilo  de  una cuenta corriente. En la columna del Haber se indicarán las entradas de dinero y en la columna del Debe las salidas. La última columna llevará el control del saldo, que se calculará sumando al saldo anterior  la  última  posible  entrada  y  restándole  la  última  posible  salida  de  dinero.  Transcribir  la siguiente tabla:

	A	B	C	D	E
1	Fecha	Concepto	Debe	Haber	Saldo
2		Saldo anterior		325.000	325.000
3	20/08/02	Ingresos		150.000	475.000
4	21/08/02	Gastos varios
5	24/08/02	Cajero automát.
6	25/08/02	Intereses a favor

La celda  E3  debería  contener  una  fórmula  que  refleje  los  movimientos.  En  principio,  se  podría definir una fórmula sencilla como ésta:  =E2-C3+D3, es decir, el saldo anterior (E2) más lo que entra (C3) menos lo que sale (D3) y funcionaría. El problema se presenta cuando se copia esta fórmula  hacia  abajo,  para  el  resto  de  las  filas.  Como  no  existen  datos  o  las  celdas  están  en blanco, el resultado sería el siguiente:

	A	B	C	D	E
1	Fecha	Concepto	Debe	Haber	Saldo
2		Saldo anterior		325.000	325.000
3	20/08/02	Ingresos		150.000	475.000
4	21/08/02	Gastos varios			475.000
5	24/08/02	Cajero automát.			475.000
6	25/08/02	Intereses a favor			475.000

El saldo siempre se repetiría debido a que las celdas a su izquierda están vacías y siempre haría el mismo cálculo con el saldo anterior. Como estéticamente no queda bien, se tratará de mejorar su aspecto utilizando la función =SI().

En la misma celda E3 se escribe: =SI(O(C3>0;D3>0);E2-C3+D3;"")

Si se analiza la fórmula, dentro de la función  SI aparece la letra  O y entre paréntesis hay dos condiciones  separadas  por  punto  y  coma.  Este  operador  O determina que se tiene que cumplir una  de  las  dos  condiciones  para  que  se  calcule  E2-C3+D3,  de  lo  contrario,  no  se  visualiza ninguna cifra en la celda (""). Las condiciones son que en C3 haya algún número (C3>0) o que en D3 haya algún número (D3>0), es decir, si se ingresa alguna cantidad positiva. En ese caso,  se ejecuta la fórmula E2-C3+D3. En caso contrario no aparecerá nada en la celda. Si ahora se copia la función hacia abajo, sólo aparecerá el saldo de la fila que tenga datos:

	A	B	C	D	E
1	Fecha	Concepto	Debe	Haber	Saldo
2		Saldo anterior		325.000	325.000
3	20/08/02	Ingresos		150.000	475.000
4	21/08/02	Gastos varios
5	24/08/02	Cajero automát.
6	25/08/02	Intereses a favor

Ingresar ahora los montos de los conceptos que aún no fueron cargados y comprobar si el cálculo del saldo es correcto.

Diseñar  una  planilla  semejante  para  registrar  los  movimientos  correspondientes  al mes  de Septiembre.

Guardar la planilla con el nombre Funcion SI con Y, Si con O.xls

Ejercicio Repaso

Completar la siguiente tabla de datos:

	A	B	C	D	E	F
1	Cód.empleado	Apellido y Nombre	Área	Turno	Sueldo básico	Categoría
2	1003		Ventas	Tarde		A
3	1005		Administración	Mañana		A
4	1007		Producción	Mañana		C
5	1008		Ventas	Tarde		B
6	1010		Producción	Noche		B
7	1011		Administración	Mañana		A
8	1012		Mantenimiento	Noche		C
9	1015		Mantenimiento	Mañana		C
10	1018		Ventas	Tarde		B
11	1020		Producción	Tarde		B
12	1032		Ventas	Tarde		C
13	1033		Administración	Mañana		B
14	1035		Producción	Tarde		C

Definir el rango de criterios y las funciones que permitan efectuar las siguientes operaciones:

- Calcular el monto total de los sueldos abonados al personal de Ventas.

- Calcular el monto total de los sueldos abonados al personal de Producción y Mantenimiento.

- Calcular la cantidad de empleados que trabajan en turno Tarde del sector Ventas.

- Calcular la cantidad de empleados que trabajan en la categoría B del sector Producción.

- Calcular el promedio de los sueldos abonados y el sueldo máximo y mínimo de la empresa.