UTN – PROGRAMA DIGITAL JUNIOR
PROGRAMA DIGITAL JUNIOR Prácticas de MS Excel - Nivel Avanzado
TABLA DE CONTENIDOS
TEMA: FORMATOS AVANZADOS....................................................................................................... 2 TEMA: VALIDACIÓN DE DATOS ........................................................................................................ 4 TEMA: FÓRMULAS Y FUNCIONES .................................................................................................... 5 TEMA: FILTROS AUTOMÁTICOS ..................................................................................................... 15 TEMA: GRÁFICOS ................................................................................................................................... 16 TEMA: EJERCICIOS INTEGRADOS ................................................................................................ 27
-1-
UTN – PROGRAMA DIGITAL JUNIOR
Tema: Formatos Avanzados Ejercicio 1: Utilizar para este ejercicio el archivo “FormatoCondicNOTAS.xlsx” 1. En la Hoja 1, mostrar en color rojo las notas de los alumnos mayores al promedio del trimestre 2. En la Hoja 2, mostrar en rojo los alumnos que alcanzaron un promedio mayor o igual a 6, y no tienen ninguna nota inferior a 4 puntos 3. En la Hoja 3, mostrar en rojo los alumnos que alcanzaron un promedio mayor o igual a 6 y no tienen ninguna nota inferior a 4 puntos. Mostrar en azul los que alcanzaron el promedio pero tienen notas inferiores a 4 puntos
Ejercicio 2: Formatos personalizados, formatos condicionales, fórmulas y funciones. Una empresa de Vtas de comidas rápidas, tiene 10 locales de venta al público. Posee una planilla donde registra las Vtas. Diarias realizadas por semana en cada local. Del total semanal registrado en cada uno de los locales, reserva un 12 % con destino al Dpto. de Marketing. 1.
Abrir el archivo Fast-foot.xlsx.
2.
Calcular el Total Semanal en cada local y lo destinado al Dpto. de Marketing con las referencias de la planilla.
3.
Averiguar la cantidad de días donde las Vtas. por local, no superan los $1500.
4.
La gerencia de Vtas. estableció que en caso de haber 4 o más días con montos de Vtas. por local inferior a $1500, se debe “Informar al Dpto. de Marketing” para intensificar la publicidad; en caso contrario “Enviar felicitaciones”.
5.
El Dpto. de Marketing necesita saber a cuánto asciende el Monto Total de dinero que recibirá si las Vtas. Semanales superan los $12.500. Aplicar los formatos personalizados que permitan visualizar:
Dom
Sáb
Vie
en el rango B2:H11 para visualizar el signo monetario y el punto separador de
Jue
b.
Mié
en el rango de rótulos B1:H1 tal como se observa en la figura. Mar
a.
Lun
6.
miles sin decimales c.
en el rango I2:J11 para visualizar el signo monetario, el punto separador de miles con un decimal
d.
en el rango K2:K11 establecer el formato personalizado de color verde para valores mayor o igual a 4, o negro para valores menores a 4.
7.
Con formato condicional aplicar color rojo al cartel “Informar al Dpto. de Marketing”. -2-
UTN – PROGRAMA DIGITAL JUNIOR 8.
Aplicar a la columna Total semanal un formato condicional semáforo que cumpla las siguientes condiciones: a.
Verde: valor mayor o igual a 15.000
b.
Amarillo: valor mayor o igual a 10.000 y menor a 15.000
c.
Rojo: valores inferiores a 10.000
-3-
UTN – PROGRAMA DIGITAL JUNIOR
Tema: Validación de Datos
Ejercicio 1: El archivo Reservas.xlsx presenta un formulario para consultar la disponibilidad de habitaciones y tarifas de un hotel. Se requiere que valide la siguiente información. 1. En C2 se debe validar que el dato ingresado sea un texto de 50 caracteres como máximo. 2. En B7 y B10 se debe validar que los datos ingresados sean fechas comprendidas entre el 01/12/08 y el 30/03/09. En caso que el usuario introduzca un valor fuera del rango, se deberá mostrar un mensaje estilo “información” con la siguiente leyenda: “Lo lamentamos, No disponemos tarifas para el período requerido”. 3. En B13 se debe calcular la cantidad de noches de la reserva en función de las fechas de ingreso y egreso. En caso de que alguna fecha no se haya indicado, B13 deberá permanecer en blanco. 4. En D7 se debe validar que el usuario ingrese una categoría de habitación existente. Para ello se deberá sugerir, como lista de valores, los datos dispuestos en la hoja “Auxiliar” rango A2:A4. 5. En D11 se debe validar que el dato ingresado sea un número entero comprendido entre 1 y 4 (inclusive). Además se deberán mostrar los siguiente mensajes: Mensaje entrante: “Indique cantidad de personas por habitación” Mensaje de error: “Disculpe, No poseemos habitaciones con dicha capacidad”. Estilo “Límite”. 6. En F7 se debe validar que el dato ingresado sea “Sí” o “No”. 7. En B8 debe mostrarse la leyenda “Los campos Mail, Fecha de Ingreso y Egreso son REQUERIDOS”, en el caso que algunas de las celdas requeridas estén vacías. Realice lo anterior valiéndose de fórmulas y funciones. 8. Oculte la hoja “Auxiliar”.
-4-
UTN – PROGRAMA DIGITAL JUNIOR
Tema: Fórmulas y Funciones Ejercicio 1: Referencias Mixtas. Para
realizar
este
ejercicio
utilizaremos
los
datos
que
se
encuentran
en
la
planilla
“TABLASMULTIPLICAR.xlsx”: En esta planilla se necesita crear las tablas de multiplicar del 1 al 10, se puede decir que la tabla del 1 se encuentra armada, ya que los valores ingresados en la planilla corresponden a dicha tabla. 1. Colocar en negrita los datos ingresados en la fila1 y en la columna A. 2. En la celda B2 ingresar el cálculo correspondiente, considerando que el resultado debe ser la multiplicación del valor de la celda B1 por el valor de la celda A2. 3. Deberá modificar la fórmula de manera tal que podamos copiarla en toda la planilla, teniendo en cuenta que si copiamos la fórmula hacia abajo deberán quedar fijos los valores de la primera fila (Fila1) y si copiamos la fórmula hacia la derecha los valores que deberán quedar fijos son los que corresponden a la columna A. 4. Copiar la fórmula en el resto de las celdas. Una vez resuelta la misma deberá verse de la siguiente manera.
-5-
UTN – PROGRAMA DIGITAL JUNIOR
Ejercicio 2: Referencias Mixtas. En el libro “UsoRefMixtas-Electrodom.xlsx” resolver utilizando el concepto de celdas mixtas. 1. Averiguar el valor de la cuota según el plan de pago sin tener en cuenta los intereses. Ingresar el cálculo correspondiente de manera tal que pueda copiarse en toda la planilla. Debes poder copiar la fórmula al desplazarte hacia la derecha y luego hacia abajo. 2. Insertar tres filas en la parte superior de la planilla colocar el título Venta de Electrodomésticos, centrado en la selección de las columnas. 3. Completar la planilla con bordes y sombreados de celdas a tu gusto. 4. Grabar como R-REF-ELECTRODOMÉSTICOS
Ejercicio 3: Funciones Entero, Truncar, Redondear En un nuevo libro de Excel realice la siguiente tabla:
Valor
Entero
Truncar 0
Redondear Truncar 1
1,15 2,54 -1,15 -2,54 1. En la columna Entero, aplique la función entero a los valores dados. 2. En la columna Truncar 0, aplique la función Truncar a los valores dados, de forma que no se muestren dígitos decimales. 3. Estas funciones se comportan en forma similar, no obstante: ¿Qué diferencia existe entre ellas al trabajar con valores negativos? 4. En la columna redondear, aplique la función redondear a los valores dados. 5. ¿Cómo se comporta la función redondear? 6. En la columna Truncar 1, aplique la función Truncar a los valores dados, de forma que se muestre un solo dígito decimal. 7. ¿Qué diferencia existe entre el resultado de aplicar la función redondear, respecto de aplicar la función truncar a un decimal?
-6-
UTN – PROGRAMA DIGITAL JUNIOR
Ejercicio 4: El Juego de Basas es un juego de cartas en el cual antes de iniciar la mano cada jugador deberá arriesgar un número de basas que intentará hacer en dicha mano. Realizar una basa implica poner en la mesa la carta más alta. La cantidad de basas que pueden realizarse en una mano equivale a la cantidad de cartas que se repartan en dicha mano. Al finalizar la ronda, los puntajes se contabilizan de la siguiente manera: a.
Si lo que arriesgó el jugador resultó ser lo mismo que lo hecho, suma 10 puntos más la cantidad de basas realizadas.
b.
Si lo que arriesgó es menor de lo que hizo, resta los puntos que hizo.
c.
Si lo que arriesgó es mayor a lo que hizo resta lo que arriesgó.
Utilizar el archivo “JUEGOBASAS.xlsx” y calcular el puntaje de cada jugador teniendo en cuenta las reglas del juego. Sugerencia: resolver este ejercicio mediante la utilización de las funciones MÁX., y Si.
Ejercicio 5: Funciones Si, Y, O, NO En el archivo “DATOSPERSONALES.xlsx” deberá resolver: 1.
En (a) Columna GRUPOS. Si la persona tiene estudios universitarios y es soltero, mostrar la leyenda "grupo A", de lo contrario mostrar la leyenda "grupo B".
2.
En (b) Columna EXTRAS. Si la persona tiene estudios Universitarios y es Casado o tiene hijos, calcular un bono extra del 5% con respecto al salario (extra= salario * 0,5). De lo contrario, calcular un bono extra del 2% con respecto al salario (extra = salario * 0,2).
3.
En (c) Columna INSCRIPTOS. Si la persona no tiene estudios Universitarios (U) y su Edad es mayor a 35 años o el Estado Civil es Casado (C) o Viudo (V), se deberá mostrar la leyenda "Inscripto 1", de lo contrario "Inscripto 2".
4.
En (d) Columna PERFILES. Si la persona tiene estudios Universitarios (U) o Terciarios (T) y edad mayor o igual a 30, mostrar "Perfil 1". De lo contrario, si tiene estudios Secundarios (S) y edad es menor a 20 y no está casado (C), mostrar "Perfil 2". De lo contrario mostrar "Perfil "3".
-7-
UTN – PROGRAMA DIGITAL JUNIOR Ejercicio 6: El libro de trabajo será “NOTAS.xlsx”. 1. Calcular el promedio de los alumnos teniendo en cuenta las siguientes condiciones: a. El promedio se calcula como la suma de las notas de Oral y Escrito divido 2. b. Para el cálculo de la columna RECUPERA hay que tener en cuenta el promedio. Si el promedio es mayor o igual a 5 entonces en la columna RECUPERA deberá obtener la frase “APROBADO”. c.
Si el promedio es menor a 5 entonces hay que evaluar que parte del examen recupera. Si el ORAL y el ESCRITO son menores a 5 entonces en la columna RECUPERA deberá obtener la frase “RECUPERA TODO”. Si el ORAL o el ESCRITO son menores a 5, entonces las frases serán “RECUPERA ORAL” o “RECUPERA ESCRITO”.
2. Nota: Si el Tipo de Alumno es Regular y el promedio es menor a 5, debe tomar la en cuenta la nota de concepto y promediarla nuevamente. Si sigue siendo menor a 5 entonces evaluará que recupera.
Ejercicio 7: Para realizar esta práctica utilice el archivo “SUELDOS.xlsx” 1. En la celda I2 calcule el Bonus Hijos. Realice una fórmula que calcule el % de bonus hijos establecido en la celda G24 y multiplíquelo por el sueldo fijo. 2. Copie la fórmula para todos los empleados. 3. Calcule el Bonus Adicional en los rangos de celdas J2:J7; J9:J11; J13:J19. Realice una función que evalúe el cargo del empleado. Si el cargo es “EMP1” o “EMP2” o “EMP3” el bonus adicional es el 1% del sueldo fijo. Si el cargo es “CADETE” el bonus es el 2% del sueldo fijo. Si el cargo es “GERENTE” el bonus es el 3% del sueldo fijo. 4. En los rangos de celdas K2:K7; K9:K11; K13:K19, calcule el sueldo final como una fórmula del sueldo fijo (Columna E) + premios (Columna F) + bonus hijos (Columna I) + bonus adicional (Columna J). 5. En las celdas
L8; L12; L20, calcule el Total de los empleados de Administración,
Gerencia e Informática utilizando la función suma. Seleccione los rangos correctamente. 6. En las celdas E21; F21; I21; J21; K21, calcule los totales de sueldo fijo (Columna E), premios (Columna F), bonus hijos (Columna I), bonus adicional (Columna J) y sueldo final (Columna k) utilizando la función suma. Seleccione los rangos correctamente.
-8-
UTN – PROGRAMA DIGITAL JUNIOR
Ejercicio 8: El presente ejercicio trabaja con criterios avanzados para la función Sumar.SI. Se aconseja resolver primeramente el ejercicio 18 del tema Funciones correspondiente a las prácticas de Excel Nivel Fundamentos.
El libro “La Pampa.xlsx” contiene en la hoja “detalle” información histórica respecto de las precipitaciones caídas para el mes de junio en la provincia de “La Pampa”. En base a esta información, en la hoja Estadísticas, calcule lo siguiente. 1. En la celda B3 deberá ingresar manualmente parte del nombre de una ciudad. En base al valor recientemente ingresado, en la celda B4 deberá calcular la cantidad de mm de lluvia que cayeron en todas las ciudades cuyo nombre contiene el criterio ingresado en B3. Para ello, le sugerimos valerse de comodines. 2. En la celda B19 deberá ingresar manualmente una fecha. En base al valor recientemente ingresado, en la celda B10 deberá calcular el acumulado de lluvias para todas las ciudades hasta la fecha indicada (inclusive).
Ejercicio 9: Función Elegir Utilizar el archivo “EVENTOS-CATEGORÍAS.xlsx” donde se encuentran los Ejercicios 1 y 2, en los cuales deberá resolver las siguientes consignas. En el Ejercicio 1 encontrará fechas ingresadas para próximos eventos dentro de las celdas A5, A6, A7. a.
Deberá utilizar las funciones que correspondan de manera tal que en la columna DÍA, aparezca el día de la semana con su nombre, según las fechas de las celdas mencionadas anteriormente.
En el Ejercicio 2 se presenta la siguiente tabla de Categorías:
b.
Categoría
% Aumento
1 2
10% 8%
3
6%
Deberá calcular en la columna Aumento el porcentaje que corresponda sobre el sueldo según la categoría del empleado.
-9-
UTN – PROGRAMA DIGITAL JUNIOR
Ejercicio 10: Funciones Buscarv y Buscarh En el archivo “DATOS-EMPLEADOS.xlsx” encontrará datos de los empleados; se pide que: a.
Busque el SUELDO, OBRA SOCIAL, TURNO Y FRANCO de cada empleado según corresponda la categoría de los mismos.
Ejercicio 11: El presente ejercicio propone hacer un repaso de ciertos aspectos relevantes en el uso de las funciones de búsqueda BUSCARV / BUSCARH 1. En base a la tabla de posiciones que se muestra en la siguiente figura en la celda B14 se requiere conocer la posición que ocupa en el ranking ATP el jugador ingresado en la celda A14. a.
Teniendo en cuanta lo requerido, indique cuál es el error que presenta esta sintaxis: Sintaxis: =BUSCARV(A14;A6:B10;1;FALSO)
b.
Por lo tanto cuando se emplea la función BUSCARV el valor a buscar debe estar dispuesto en la ………….. columna de la matriz de búsqueda (indicar el nro de columna).
2. La siguiente tabla muestra la información de las fechas en las que se producen cambios en la cotización del barril de petróleo. En base a esta información en la celda B33 se requiere conocer la cotización del barril vigente para la fecha ingresada en A33. Note
que
el
usuario
puede
requerir
conocer
la
cotización del barril para una fecha no especificada en la tabla. Esto se debe a que la tabla sólo muestra la información de las fechas en las cuales se producen cambios en el valor de la cotización. Por lo tanto, si la fecha ingresada por el usuario no se encuentra determinada en la tabla, debe considerarse el valor del barril dispuesto para la fecha inmediata anterior.
-10-
UTN – PROGRAMA DIGITAL JUNIOR
a.
Teniendo en cuenta lo requerido, indique cuál es el error que presentan las siguientes sintaxis: Sintaxis 1: =BUSCARV(A33;A23:B28;2;FALSO) Sintaxis 2: =BUSCARV(A33;A23:B28;2;0) Sintaxis 3: =BUSCARV(A33;A23:B28;2;)
b.
Además del error de sintaxis ya corregido, persiste en la matriz de búsqueda un detalle que traerá problemas en la búsqueda del valor deseado. ¿Cuál es?
c.
En base a lo analizado, determine si las siguientes afirmaciones son verdaderas o falsas:
Cuando se utiliza la función Buscarv con el cuarto parámetro definido en Verdadero, los datos de la primera columna de la matriz de búsqueda deben estar ordenados en forma ascendente.
Cuando se utiliza la función Buscarv con el cuarto parámetro definido en Verdadero, los datos de la primera columna de la matriz de búsqueda deben estar ordenados en forma descendente.
Cuando se utiliza la función Buscarv con el cuarto parámetro definido en Falso, no es necesario definir ningún ordenamiento en particular en la primera columna de la matriz de búsqueda. Incluso lo datos pueden estar desordenados.
3. Indique el valor que le asignaría al 4to. parámetro de la función Buscarv en base a los siguientes requerimientos: a.
Si se requiere que el valor a buscar se encuentre definido en forma exacta en la matriz de búsqueda, entonces el valor del parámetro será …………
b.
Si no es preciso encontrar el valor exacto, y en dicho caso, se requiere quedarse con el valor inmediato inferior, entonces el valor del parámetro será …………
c.
Si no es preciso encontrar el valor exacto, y en dicho caso, se requiere quedarse con el valor inmediato superior, entonces el valor del parámetro será …………
Ejercicio 12: Funciones Estadísticas: K.esimo.mayor, K.esimo.menor, Jerarquía Trabajar con el libro “competencias.xlsx” En la hoja TC200 se disponen los tiempos finales que presentaron los automóviles participantes de una carrera de esta competencia. 1. A partir del Tiempo (D2:D10), le pedimos que en la columna Posición (E2:E10) calcule la posición alcanzada por cada automóvil, teniendo en cuenta que la primera posición corresponde al menor tiempo, y así sucesivamente. -11-
UTN – PROGRAMA DIGITAL JUNIOR 2. Luego de realizado lo anterior, le pedimos que calcule el tiempo correspondiente a la primera posición (B14), a la segunda posición (B15) y a la tercera posición (B16) En la hoja TC2000 (2) se vuelven a listar tiempos de la competencia. 1. Le pedimos que calcule la posición alcanzada por cada automóvil (E2:E10) y los tiempos que ameritan el podio en la carrera (B4, B15, B16). 2. Analice los resultados que se obtuvieron en esta oportunidad. ¿Cómo se comporta la función Jerarquía ante la presencia de valores duplicados? ¿Cómo se comporta la función K.esimo.menor ante la presencia de valores duplicados? Tenga presente este comportamiento, a la hora de utilizar estas funciones. En la hoja Globo se disponen los tiempos finales que presentaron los participantes de una competencia de globo aerostático. 1. A partir del Tiempo (B2:B10), le pedimos que en la columna Posición (C2:C10) calcule la posición alcanzada por cada participante, teniendo en cuenta que la primera posición corresponde al mayor tiempo de permanencia en el aire, y así sucesivamente. 2. Luego de realizado lo anterior, le pedimos que calcule el tiempo correspondiente a la primera posición (B14), a la segunda posición (B15) y a la tercera posición (B16) 3. Analice las funciones utilizadas para calcular lo anterior. ¿Qué varió en la forma de utilizar la función Jerarquía? ¿Qué función le convino utilizar para calcular los valores del podio: k.esimo.menor o k.esimo.mayor? ¿Por qué?
Ejercicio 13: Trabajar en el libro “CálculosVarios-Fechas.xlsx” 1. En la Hoja 1 calcular los vencimientos de acuerdo con la unidad de tiempo dada en A4:A6. Sumar a la fecha de Inicio las cantidades expresadas en la columna “Valor”. 2. En la Hoja 2, en la planilla A3:F7 se muestran en la columna A una serie de fechas de Inicio y en la Columna B fechas de Fin. Calcular la diferencia entre la Fecha Fin y la Fecha Inicio y expresarlo sólo en: a. b. c. d.
Años Meses Días Semanas
en en en en
el el el el
rango rango rango rango
C4:C7 D4:D7 E4:E7 F4:F7
-12-
UTN – PROGRAMA DIGITAL JUNIOR 3. En la tabla A10:E14 realizar la diferencia entre Fecha Fin y la Fecha Inicio y mostrar la misma en cantidad de años, cantidad de meses y cantidad de días. De tal manera que al concatenar se pueda observar por ejemplo el resultado del rango C13:E13 Se sugiere utilizar la función SIFECHA. 4. En la hoja 3, dado el año y el mes, calcular el primer día o la primera fecha (C3:C4) y el último día (D3:D4). a.
En E3:E4 calcular cuántos días tiene el mes.
b.
En F3:F4 calcular si el año es bisiesto o no.
c.
Dada una fecha, en el rango B8:B10 calcular a qué bimestre pertenece ese día.
Ejercicio 14: Funciones de Fechas En el archivo “CÁLCULOSANTIGUEDAD.XLSX”, encontrará una plantilla con apellidos de empleados y su fecha de ingreso a la empresa. Se pide que a partir de la fecha actual se calcule la antigüedad en la empresa de cada uno de los empleados que figuran en B6:B13 pero en forma detallada: cuántos días, meses y años hace que trabaja. a.
C2: función que devuelva fecha actual
b.
D6:D13: calcular cuántos años hace que trabaja
c.
E6:E13: calcular cuántos meses hace que trabaja
d.
F6:F13: calcular cuántos días hace que trabaja
En C11 colocar la fecha del día anterior al actual (al hacerlo en F11 debería aparecer automáticamente un 1 y 0 en D11 y E11). En C12 colocar la fecha actual (al hacerlo debería aparecer 0 en D11, E11 y F11). En B11 y B12 colocar el apellido del alumno
Aspecto final:
-13-
UTN – PROGRAMA DIGITAL JUNIOR
Ejercicio 15: Funciones Búsqueda y Referencia, Lógicas, El archivo “SWTOURS.xlsx” contiene los datos de una empresa de turismo. 1. En la hoja “DESTINOS”, tiene asentado los precios de los pasajes aéreos entre Buenos Aires y el destino turístico expresado en Dólares. Además la tabla tiene una columna que indica si el destino posee o no descuento. 2. En la hoja “CONSULTA” se necesita acceder rápidamente a la cotización final del pasaje para la información de los clientes. a.
Ingresar en B5 la cotización del Dólar (pesos por dólar) de la fecha y en F5 el porcentaje
de
descuento
que
estipula
la
empresa.
Estos
datos
se
cargan
manualmente. Se requiere: 3.
En la hoja “DESTINOS”, calcular el valor de los pasajes en $ en función de la cotización del Dólar.
4.
En la hoja “CONSULTA”: a.
En C5 insertar un comentario, indicando que se deberá ingresar el código de destino. En función del número ingresado, deberá calcular los siguientes datos:
b.
En D5 visualizar automáticamente el destino del vuelo.
c.
en E5 validar los valores, 1 para viaje de IDA solamente y 2 para viaje de IDA y VUELTA. Como mensaje entrante visualizar “Ingrese 1 para IDA ó Ingrese 2 para IDA y VUELTA”. Mensaje de error a su elección.
d.
En G5 visualizar automáticamente el costo del pasaje en $ del destino seleccionado.
e.
En H5 visualizar automáticamente la cotización final del pasaje en $ considerando si al destino seleccionado se le aplica el descuento, y teniendo en cuenta si se trata de un viaje de IDA o de IDA y VUELTA. Tenga presente que el importe de los pasajes dispuestos en la hoja DESTINOS, corresponden a vuelos de IDA, este importe se debe multiplicar por 2 para el caso de los pasajes IDA y VUELTA.
-14-
UTN – PROGRAMA DIGITAL JUNIOR
Tema: Filtros Automáticos
Para realizar los primeros filtros utilizará el archivo “VENTAS.xlsx”. Seleccione el rango de celdas, de la planilla “Ventas 2006”. Active la herramienta Auto filtro. 1. Listar todas las ventas de productos de la marca GAFA. 2. Listar todas las ventas realizadas con TARJETA. 3. Listar todas las ventas realizadas en los locales ABASTO y LINIERS, cuyos vendedores sean AVERGARA y DACOSTA. 4. Listar todas las ventas realizadas entre el 1 de julio del 2006 y el 31 de agosto del 2006. 5. Listar todas las ventas de la marca WHIRLPOOL que se hayan realizado en efectivo. 6. Listar todas las ventas realizadas en el mes de diciembre del 2006 del local Caballito. 7. Listar todas las ventas de aquellos productos que el nombre contenga la palabra HELADERA o FREEZER, realizadas por los vendedores MNATALINI o LZEPPA. 8. Listar todas las ventas de aquellos productos cuyo nombre comience con C. 9. Resaltadas con fondo amarillo se muestran las ventas que presentaron alguna dificultad en la emisión de la factura. Aplique el filtro adecuado, tal que pueda visualizar sólo estas operaciones.
Para realizar los siguientes filtros utilizará el archivo “CURSOS.xlsx”. Basado en la lista otorgada utilice los filtros automáticos para aplicar los criterios. Realice copias de la hoja de la lista de manera que obtenga una hoja por cada criterio.
9. CÓDIGOCURSO: A1-01 / J1-01
11. CÓDIGOCURSO: G1-01 / G2-21
CUOTA: 1 / 2
FECHA PAGO: MES DE MARZO DE 1999
FECHAPAGO: MES DE MARZO DE 1999 12. IMPORTE: entre 90 y 200 10. CUOTA: 1 / 2
COD ENCARGADO: 2 / 3
IMPORTE: ENTRE 70 Y 100
FECHA PAGO: MES DE MAYO DE 1999
-15-
UTN – PROGRAMA DIGITAL JUNIOR
Tema: Gráficos
Ejercicio 1: Identificar el tipo de gráfico conveniente. 1. Indique qué tipo de gráfico deberá utilizar si lo que se quiere graficar es la evolución de oyentes que tuvieron 2 programas radiales que comparten el mismo horario, durante los meses de Abril y Junio. Los datos se presentan de la siguiente forma: Programa 1 Programa 2 Abril
2000
1800
Mayo
2600
2300
Junio
2300
2400
El dato representa la cantidad promedio mensual de oyentes del programa. 2. Indique qué tipo de gráfico debe utilizar si desea conocer el porcentaje de alumnos "aprobados", "no aprobados" y "ausentes", para cada una de las instancias de examinación. Los datos se presentan de la siguiente forma: Aprobados
No Aprobados
Ausentes
Diciembre
7
5
2
Marzo
4
3
0
3. Con los mismos datos del ejercicio anterior, indique qué gráfico debe utilizar si lo que necesita conocer es la cantidad de alumnos "aprobados", "no aprobados" y "ausentes", para cada una de las instancias de examinación y la proporción que guarda dicha cantidad en función del total de alumnos. 4. Con los mismos datos del ejercicio anterior, indique qué tipo de gráfico debe utilizar si desea conocer el porcentaje de alumnos "aprobados", "no aprobados" y "ausentes", para la instancia de examinación Marzo. 5. Indique qué gráfico debe utilizar si desea conocer para los alumnos “aprobados” y “no aprobados” el porcentaje de alumnos por categoría de nota obtenida.
-16-
UTN – PROGRAMA DIGITAL JUNIOR
Los datos se presentan de la siguiente forma: Aprobados 10 Pts Diciembre
9-8 Pts 0
No aprobados 7-6 Pts
2
5-4 Pts 5
3-2 Pts 2
1-0 Pts 2
1
Dados los siguientes modelos de gráficos deberá interpretarlos y diseñarlos generando los datos necesarios, respetando los colores y formatos. Ejercicio 2: El presente gráfico representa el monto total de ventas ($) por trimestre, para cada uno de los productos que comercializa una curtiembre.
Tome nota de los siguientes aspectos de diseño:
El formato, el rango y el incremento de los valores correspondientes al eje ventas.
Se muestra una tabla con los valores específicos para cada producto por trimestre. -17-
UTN – PROGRAMA DIGITAL JUNIOR
Muestra sombreado el plano inferior del gráfico y color degradado en los laterales.
La serie Zapatos está representada con cilindros
El titulo de gráfico está ubicado a la izquierda del mismo, con tamaño de fuente 11 y tiene color de relleno.
Ejercicio 3: El siguiente gráfico representa la contribución de las ventas por producto al total de ventas trimestrales de la curtiembre. El rótulo corresponde a las ventas trimestrales de cada producto comercializado.
Cuero Zapatos
Trim1 30000 40000
Trim2 34000 40000
Trim3 29000 27000
Trim4 35000 42000
Tenga en cuenta los siguientes detalles de diseño:
El formato, el rango y el incremento de los valores correspondientes al eje ventas.
Formato de las barras, colores, bordes y grosor.
Los bordes del gráfico se encuentran redondeados y con sombra.
El formato con sombra del recuadro de la leyenda.
Se muestran los nombres de los ejes y junto al eje Ventas, se dispuso el detalle de la unidad de medida. -18-
UTN – PROGRAMA DIGITAL JUNIOR
Ejercicio 4: El presente gráfico muestra el porcentaje de ventas anuales correspondientes a los 3 productos que comercializa la empresa: “Carteras”, “Cueros” y “Zapatos”. En particular para el producto “Cuero”, por ser el más representativo para la compañía, se muestra en detalle la contribución de dicho producto por trimestre al total de ventas anual.
Cuero Zapatos $
55.000
Carteras $
29.000
Trim1 $
30.000
Trim2 $
34.000
Trim3 $
29.000
Trim4 $
35.000
Tenga en cuenta los siguientes detalles de diseño:
En el gráfico principal, se muestran los valores correspondientes a las ventas para cada producto. En el gráfico asociado, se muestran los valores de la venta de cuero para cada trimestre. Verifique el formato de presentación de dichos valores.
Como leyenda sólo se dejaron las referencias a los trimestres.
Piense de qué manera puede agregar al gráfico principal las descripciones de los productos.
Elija una textura de relleno conveniente para representar las series.
Tamaño del gráfico 8 cm de alto por 15 cm de ancho.
-19-
UTN – PROGRAMA DIGITAL JUNIOR Ejercicio 5: El presente gráfico ilustra el stock de insumos que tiene un taller de ropa. En el taller se toma como punto crítico las 20 unidades del producto, a partir de entonces es necesario realizar una reposición del mismo. Las unidades en stock con valores negativos indican que hay productos vendidos que requieren insumos sin existencia en stock. El valor indica la cantidad de unidades que se requieren para satisfacer los pedidos.
PRODUCTOS Tintura Encaje Hilo Cinta Puntilla
Cantidad 60 50 55 10 -10
Puntilla
Tenga en cuenta los siguientes detalles de diseño:
El formato, el rango y el incremento de los valores correspondientes al eje “unidades”.
El eje de categoría X corta al eje Y en las 20 unidades.
La forma en la cual se han dispuesto los nombres de los productos.
Elija un color acorde para la serie de datos de los productos que superan las 20 unidades y otro color para los productos que se encuentran debajo del límite de reposición.
Color y borde del área de trazado.
No se muestran las líneas de división. -20-
UTN – PROGRAMA DIGITAL JUNIOR Ejercicio 6: El presente gráfico muestra el mejor resultado alcanzado por las distintas escuderías participantes del mundial de F1, discriminado por cada uno de los circuitos.
Escudería BA MA AU SM EU Ferrari 1 4 3 1 1 McLaren 2 1 2 3 10 Wiliams 6 5 5 2 6 Honda 4 3 8 7 6
Clasificación Mundial F1 11 10
10
9
Clasificación
8
8
7
7
6
Ferrari
6
6
5
5
4
4
5
Honda
3
2
2
1
1 BA
Wiliams
4
3
3
3
2
2
1 MA
McLaren
1 AU
SM
1 EU
Circuito
Tenga en cuenta los siguientes detalles de diseño:
El rango de los valores correspondientes al eje “clasificación”.
No se muestran las líneas de división.
Se muestran líneas de máximos y mínimos con estilo punteado.
El área de trazado deberá ser de color gris
-21-
UTN – PROGRAMA DIGITAL JUNIOR Ejercicio 7: El gráfico representa las ventas de los distintos productos agrícolas en los años 2006 y 2007.
2006 100 400 300 100 500 200
Avena Trigo Maíz Girasol Soja 1º Soja 2º
2007 0 500 300 0 400 100
Vtas Agrícolas 2006-2007 500 450
Toneladas
400 350 300 250 200
150 100 50 0
400
100
500
300 300
0
Avena
500
100
400 200 100
0
Trigo
Maiz
Girasol
Soja 1º
Soja 2º
Detalle
2006
2007
Tenga en cuenta los siguientes detalles de diseño:
El gráfico tiene un tamaño de 15 cm de alto por 18 cm de ancho
El tipo de fuente de los títulos es Tahoma, tamaño 14 para el título principal.
El tipo de fuente para los títulos de los ejes es Tahoma, tamaño 12.
-22-
UTN – PROGRAMA DIGITAL JUNIOR
Las leyendas deben mostrarse en el plano inferior del gráfico, fuente Tahoma, tamaño 14.
Elimine los planos laterales e inferior.
No se deben visualizar las líneas de división principales.
El valor en que se cruzan los ejes es 100.
Las columnas tienen aplicado un efecto bisel,
Ejercicio 8: El gráfico representa las ventas de los distintos productos agrícolas en el año 2006.
Avena Trigo Maíz Girasol Soja 1º Soja 2º
2006 100 400 300 100 500 200
Tenga en cuenta los siguientes detalles de diseño:
Las leyendas deben ubicarse en la parte superior, eliminar el borde y color del área. Verificar que se vean en dos columnas.
-23-
UTN – PROGRAMA DIGITAL JUNIOR
El titulo deberá estar en la parte inferior derecha del gráfico.
Cambiar el color del área del gráfico a un degradado en verde.
Mostrar como rótulos para el gráfico, el valor y el porcentaje, en el interior de cada sector en dos líneas
Separar los sectores de mayor valor.
Ejercicio 9: Ventas correspondientes al año 2007 de los distintos productos agrícolas.
Tenga en cuenta los siguientes detalles de diseño:
Sobre este gráfico de áreas 3D apilada
Aplique color a los planos laterales.
Oculte la leyenda
Muestre la tabla de datos.
Modifique la escala de valores, cambiando la unidad menor por 100 y la unidad mayor por 300.
El tamaño del gráfico debe abarcar 23 filas y 8 columnas.
Para el título principal del gráfico debe utilizar fuente Arial, tamaño 16.
-24-
UTN – PROGRAMA DIGITAL JUNIOR Ejercicio 10: Para realizar este ejercicio, una vez confeccionado el gráfico deberá cambiar el tipo de grafico para las series soja1, soja2 y Maíz.
Girasol abr-07 may-07 jun-07 jul-07
Trigo 0 0 0 0
Avena
Soja 1º
500 300 200 400
0 0 0 0
Soja 2º 400 500 400 600
Maíz 100 200 300 100
300 400 300 200
Toneladas Vendidas 2007 700
600
500 Girasol Trigo Avena Soja 2° Soja 1° Maiz
400
300
200
100
0 Abr-07
May-07
Jun-07
Jul-07
Tenga en cuenta los siguientes detalles de diseño:
Utilice como color de fondo un degradado en color gris, desde el centro.
Modifique las series de datos Maíz, Soja 1° y Soja 2°.
Modifique el color de la serie maíz, utilizando un color verde tanto para la línea como para el marcador. El marcador debe tener como tamaño 8 puntos.
Sobre la serie Soja 1° cambie el tamaño del marcador a 8 puntos y active la opción Sombreado.
Sobre la serie Soja 2° el tamaño del marcador es 7 puntos y el grosor de la línea debe tener un punto más.
-25-
UTN – PROGRAMA DIGITAL JUNIOR Ejercicio 11: Generar gráficos a partir de rangos discontinuos
Los datos los encontrará en el archivo Encuesta.xlsx. El gráfico deberá confeccionarse en base a las respuestas positivas. Omitir a las personas que no supieron dar una respuesta. En el subgráfico de torta se requiere agrupar a las personas que contestaron Sí, indistintamente del tenor de satisfacción manifiesto. Tenga en cuenta los siguientes detalles de diseño:
Título: Encuesta.
El área de trazado se ha aumentado de tamaño.
Se ha especificado que se muestre el porcentaje correspondiente a cada respuesta.
Los porcentajes del gráfico principal se encuentran contenidos en la porción de torta correspondientes. Los porcentajes del gráfico secundario tienen líneas guía.
Los colores de las pociones del gráfico original son amarillo, celeste y naranja.
Se ha dispuesto la leyenda en el lateral derecho.
-26-
UTN – PROGRAMA DIGITAL JUNIOR
Tema: Ejercicios Integrados Ejercicio 1: Función Buscarv, Concatenar, Es error, Formato Condicional, Insertar Comentario El libro de trabajo será “LISTADOEMPLEADOS.xlsx”. Consigna: Debe utilizar la función BuscarV para obtener información de los empleados, aplicar formato condicional en base al sueldo fijo y un comentario en la celda que muestra el N° de Legajo del empleado. 1.
En la solapa “Listado” se cuenta con la lista de empleados identificados por su legajo con datos sobre su apellido, nombre, cargo, sueldo fijo y premios. En la solapa BuscarV es donde debe trabajar. A partir del ingreso del número de legajo del empleado en la celda A2, debe utilizar la función BuscarV para poder traer a dicha hoja los siguientes datos: a.
En la celda B2 Apellido y Nombre, separados por una coma.
b.
En la celda C2 Cargo.
c.
En la celda D2 Sueldo Fijo.
d.
En la celda E2 Premios.
Por ejemplo si ingresa el legajo número 1505 deberán aparecer los datos del empleado LOPEZ, JORGE. Pruebe dicha función ingresando otros números de legajo. Tenga presente que si el código de legajo ingresado es inexistente, las celdas B2:E2 deberán permanecer vacías. 2. Aplique formato condicional a la columna Sueldo Fijo (F2:F19). Si el valor se encuentra entre 0 y 500 el fondo de la celda deberá ser de color rojo. Si el valor se encuentra entre 501 y 1000 el fondo de la celda deberá ser de color amarillo. Si el valor es mayor a 1001 el fondo de la celda deberá ser de color verde. 3. En la celda A2 insertar un comentario expuesto con el siguiente texto "Ingresar manualmente el N° de legajo".
-27-
UTN – PROGRAMA DIGITAL JUNIOR Ejercicio 2:
Funciones Lógicas, Funciones de búsqueda, Funciones de texto, Funciones de información. Para realizar las consignas de esta práctica se utilizará el archivo “FACTURA.xlsx” 1. En el rango de celdas B2:K2, de la hoja CLIENTES, crear el N° de Cliente extrayendo los ocho dígitos del centro del N° de CUIT utilizando la función que corresponda. 2. Seleccionar todas las hojas del libro y cambiar el formato de la fuente por Verdana tamaño 9 puntos. 3. Inmovilizar la columna A de la hoja CLIENTES. 4. Organizar las hojas del libro de manera tal que queden en el siguiente orden, FACTURA, CLIENTES, PRODUCTOS. 5. Ordenar la lista de productos por DESCRIPCIÓN en forma Ascendente. Utilizando la Hoja FACTURA se solicita que: 6. En la celda B1 ingresar la fecha de manera tal que se actualice automáticamente, aplicarle a la misma celda el siguiente formato dd/mm/yy. 7. En la celda B3 se ingresará manualmente el código de cliente. 8. En las celdas B4; B5; B6 deberá utilizar la función BUSCARH para que a partir del ingreso del código de cliente se puedan obtener los datos del mismo. Utilice como matriz los datos de la hoja clientes. a.
Considerar que deben mostrarse las celdas calculadas en blanco, siempre que el Código del Cliente sea erróneo.
b.
El nombre del cliente debe mostrarse en mayúscula.
9. Los valores a ingresar en el rango de celdas A12:A22, serán ingresados manualmente, eligiéndolos de la hoja PRODUCTOS. 10. Utilizar la función BUSCARV para que a partir del ingreso del código de producto se puedan obtener la DESCRIPCIÓN (B12:B22) y el PCIO. UNITARIO (D12:D22). Utilice como matriz los datos de la hoja Productos. c.
Utilizar las mismas consideraciones del punto 8 ítem a.
d.
La descripción debe mostrarse en minúscula.
e.
Copiar solo las fórmulas en los rangos correspondientes.
-28-
UTN – PROGRAMA DIGITAL JUNIOR 11. Para poder calcular el total (E12:E22) debe ingresar primero la cantidad (C12:C22). Ingrese los valores que desee. El total se calcula con una fórmula que realice el producto del precio unitario por la cantidad. 12. Calcule el Subtotal (E23) aplicando la función SUMA sobre la columna TOTAL. 13. Calcule el RECARGO (E24). Si la forma de pago es EFECTIVO el recargo es 0, si es TARJETA. Debe utilizar la función ELEGIR para obtener el porcentaje de recargo que la tarjeta propone y calcularlo para obtener el recargo. 14. Calcular el TOTAL (E25) de la factura con una fórmula que sume el subtotal + recargo. 15. Proteja la hoja FACTURA, considerando que una vez protegida la hoja se deberán poder ingresar, modificar, o eliminar los datos de las celdas B3, A12:A22, C12:C22, F3 y F4. Ejercicio 3: Funciones Lógicas, Funciones de búsqueda, formato condicional y Gráficos Consigna: Para realizar las consignas solicitadas debe utilizar el archivo “PRODUCTOS.xlsx”. 1.
En la solapa FUNCIONES LÓGICAS realizar las fórmulas necesarias, para poder realizar los cálculos solicitados. a.
Calcular el Descuento en pesos en base a la columna DESCUENTO sobre el PRECIO.
b.
Calcular el Total aplicando la fórmula PRECIO * CANTIDAD – DTO.
c.
Si la CANTIDAD es mayor a 20 calcular el 10% de descuento adicional sobre TOTAL en la columna TOTAL A PAGAR. (El valor obtenido tiene que incluir el 10% de dto.)
d.
En la columna EVALUACION VENTA Si TOTAL A PAGAR es un valor entre 1 y 300 la venta es “MALA”, Si TOTAL A PAGAR es un valor entre 301 y 500 la venta es “BUENA”, Si TOTAL A PAGAR es un valor mayor a 500 la venta es “MUY BUENA”.
2.
En la solapa BUSCARV aplicar la función BuscarV para que en base al código de producto (A2) se obtenga en la celda B2 el NOMBRE DEL PRODUCTO, C2 PRECIO, y D2 Proveedor. Considerando que si el resultado de la función buscarv es erróneo, la celda en la que se muestra el resultado debe quedar en blanco, utilizando como matriz la lista que se encuentra en la hoja clientes.
-29-
UTN – PROGRAMA DIGITAL JUNIOR 3.
En la solapa Formato Condicional aplicar Formato Condicional con las siguientes condiciones. CANTIDAD ENTRE 1 Y 10 --- FONDO ROJO CANTIDAD ENTRE 11 Y 30 --FONDO VERDE CANTIDAD MAYOR A 31 --- FONDO AZUL.
4.
En la solapa Gráficos realizar un gráfico de Columnas en 3D tomando los valores de referencia. Modifique los colores de la serie Enero y Abril. Muestre el valor en el punto mayor de las golosinas.
Ejercicio 4 Importación de datos, funciones matemáticas y estadísticas, configuración de página.
Para realizar esta práctica utilizará el archivo “VENTAS.txt”. Importar Datos: 1. En un archivo nuevo de Excel, importe los datos del archivo “Ventas.txt”. 2. Colóquele a la hoja el nombre “Ventas 2006”. 3. Aplíquele algún auto formato.
Funciones estadísticas y matemáticas:
4.
Insertar una nueva Hoja en el libro y diseñar la siguiente planilla, respetando las referencias de celdas del modelo.
5.
En la celda C1 calcular el total de Vtas.
6.
Ingresar los valores del rango de celdas B3:B8 y calcular el total facturado en D1
Configurar Página: 1. La hoja a configurar será la planilla de la hoja VENTAS 2006. 2. Seleccionar como tamaño de papel, Oficio. -30-
UTN – PROGRAMA DIGITAL JUNIOR 3. Colocar como margen Superior e Inferior 2.5cm y como margen Izquierdo y Derecho 1cm y 1cm como margen para el encabezado y pie de página. 4. En la sección central colocar como encabezado de página, el nombre de la hoja y como pie de página, el número de página con el siguiente formato Pág. 1 de x. 5. Repetir en cada comienzo de página los títulos de la planilla. 6. Ajustar los Saltos de Página de manera tal que el total de Páginas a imprimir sean 9. 7. Establecer como área de impresión el rango de celdas que abarca la primera página de la hoja VENTAS 2006.
-31-
