excel avanzado practicas

PROGRAMA DIGITAL JUNIOR

Prácticas de MS Excel - Nivel Avanzado TABLA DE CONTENIDOS TEMA: FORMATOS AVANZADOS....................................................................................................... 2 TEMA: FÓRMULAS Y FUNCIONES .................................................................................................... 4 TEMA: FILTROS AUTOMÁTICOS..................................................................................................... 12 TEMA: GRÁFICOS ................................................................................................................................... 13 TEMA: EJERCICIOS INTEGRADOS ................................................................................................ 25

Tema: Formatos Avanzados

Ejercicio 1:

Utilizar para este ejercicio el archivo “FormatoCondicNOTAS.xls”

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.xls.

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 cuanto asciende el Monto Total de dinero que

recibirá si las Vtas. Semanales superan los $12.500.

6. Aplicar los formatos personalizados que permita visualizar:

a. en el rango de rótulos B1:H1 tal como se observa en la figura.

Lun

Mar

Mié

Jue

Vie

Sáb

Dom

b. en el rango B2:H11 para visualizar el signo monetario y el punto separador de

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”.

Tema: Fórmulas y Funciones

Ejercicio 1:

Referencias Mixtas.

Para realizar este ejercicio utilizaremos los datos que se encuentran en la planilla

“TABLASMULTIPLICAR.xls”:

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.

Ejercicio 2:

Referencias Mixtas.

En el libro “REF-ELECTRODOMÉSTICOS.xls” 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 titulo 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:

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.xls” 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 4:

Funciones SI, Y, O, NO

En el archivo “DATOSPERSONALES.xls” 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".

Ejercicio 5:

El libro de trabajo será “NOTAS.xls”.

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 6:

Función ELEGIR.

Utilizar el archivo “EVENTOS-CATEGORÍAS.xls” 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

DIA, aparezca el día de la semana con su nombre, según las fechas de las celdas

mencionadas anteriormente.

En el Ejercicio 2 da la siguiente tabla de Categorías:

Categoría % Aumento

1 10% 2 8%

3 6%

b. Deberá calcular en la columna Aumento el porcentaje que corresponda sobre el

sueldo según la categoría del empleado.

Ejercicio 7:

Funciones BUSCARV y BUSCARH. En el archivo “DATOS-EMPLEADOS.xls” 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 8: Fórmulas y Funciones. Para realizar esta práctica utilice el archivo “SUELDOS.xls”.

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.

Ejercicio 9:

Trabajar en el libro “CálculosVarios-Fechas.xls”

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. Años en el rango C4:C7.

b. Meses en el rango D4:D7.

c. Días en el rango E4:E7.

d. Semanas en el rango F4:F7.

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 10:

Funciones de Fechas.

En el archivo “CálculosaAtiguedad.xls”, 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:

cuantos días, meses y años hace que trabaja.

a. C2: función que devuelva fecha actual.

b. D6:D13: calcular cuantos años hace que trabaja.

c. E6:E13: calcular cuantos meses hace que trabaja.

d. F6:F13: calcular cuantos 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.

Guardar como Cálculosantiguedad-2.xls.

Así se debería ver una vez terminada:

Ejercicio 11:

Funciones de búsqueda, lógicas y uso de fórmulas.

El archivo “SWTOURS.xls” 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) En B5 la cotización del Dólar (pesos por dólar) de la fecha y en E5 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 un Código de destino

en forma manual.

b) En función del Código de destino ingresado, deberá calcular los siguientes datos.

i) En D5 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.

ii) En F5 visualizar automáticamente el costo del pasaje en $ del destino seleccionado.

iii) En G5 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

viaje de IDA o de IDA y VUELTA.

*Validar Datos corresponde al temario de examen Excel Experto.

Tema: Filtros Automáticos

Para realizar los primeros filtros utilizará el archivo “VENTAS.xls”. 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.

Para realizar los siguientes filtros utilizará el archivo “CURSOS.xls”. 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. CODIGOCURSO: A1-01 / J1-01

CUOTA: 1 / 2

FECHAPAGO: MES DE MARZO DE 1999

10. CUOTA: 1 / 2

IMPORTE: ENTRE 70 Y 100

11. CODIGOCURSO: G1-01 / G2-21

FECHA PAGO: MES DE MARZO DE 1999

12. IMPORTE: entre 90 y 200

COD ENCARGADO: 2 / 3

FECHA PAGO: MES DE MAYO DE 1999

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.


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.


Aprobados No aprobados

10 Pts 9-8 Pts 7-6 Pts 5-4 Pts 3-2 Pts 1-0 Pts

Diciembre 0 2 5 2 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.

Trim1Trim2

Trim3

Trim4 25.000

30.000

35.000

40.000

45.000

Ventas Trimestrales por Producto

Cuero 30000 34000 29000 35000Zapatos 40000 40000 27000 42000

Trim1 Trim2 Trim3 Trim4

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

• Sólo se muestra sombreado el plano inferior del gráfico

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.

30

40

34

40

29

27

35

42

20

35

50

65

80

Vent

asM

illar

es

Trim1 Trim2 Trim3 Trim4

Trimestres

Ventas Totales por Trimestre

ZapatosCuero

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 y grosor

• Los bordes del gráfico se encuentran redondeados y con sombra

• El formato 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

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.

Porcentaje de Ventas Anual

$ 30.000

$ 34.000

$ 29.000

$ 35.000$ 55.000

$ 29.000$ 128.000

Trim 1

Trim 2

Trim 3

Trim 4

Cueros

Carteras

Zapatos


• En el gráfico principal, se muestran los valores correspondientes a las ventas para cada

producto. En el gráfico asociado, se muestra 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 gama de colores conveniente para representar las series

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.

Unidades en Stock

60

50

55

10

-10

-15 -5 5 15 25 35 45 55 65

Pro

duct

os

Unidades

Puntilla

Cinta

Hilo

Encaje

Tintura


• 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 del área de trazado

• No se muestran las líneas de división

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.

Clasificación Mundial F1

1

43

1 12

12

3

10

65 5

2

6

43

87

6

123456789

1011

BA MA AU SM EU

Circuito

Cla

sific

ació

n FerrariMcLarenWilliamsHonda


• 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

Ejercicio 7:

100

0

400 500

300 300

100

0

500 400

200

100

0

50

100

150

200

250

300

350

400

450

500

Tone

lada

s

Avena Trigo Maiz Girasol Soja 1° Soja 2°

Detalle

Vtas. Agrícolas 2006 - 2007

Abr-06 Abr-07


• El gráfico debe ocupar 8 columnas y 20 filas

• 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.

• 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.

Ejercicio 8:

Ventas Agrícolas Abril 2006

1006%

30019%

1006%

20013%

40025%

50031%



• Las leyendas deben ubicarse en la parte superior, eliminar el borde y color del área.

• Cambiar el color del área del gráfico.

• Mostrar como rótulos para el gráfico, el valor y el porcentaje, en el interior de cada

sector.

• Separar los sectores de mayor valor.

Ejercicio 9:

0

300

600

900

1200

1500

1800

2100

Ventas 2007

Abr-07 0 500 300 0 400 100May-07 0 300 400 0 500 200Jun-07 0 200 300 0 400 300Jul-07 0 400 200 0 600 100



• Sobre este gráfico de áreas 3D modifique la vista cambiando la elevación a 1 y el giro a

12.

• Elimine el color del área de los planos laterales.

• Oculte las leyendas.

• 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 16 filas y 8 columnas.

• Para el título principal del gráfico debe utilizar fuente Arial, tamaño 16.

Ejercicio 10:

Toneladas Vendidas 2007

0

100

200

300

400

500

600

700

Abr-07 May-07 Jun-07 Jul-07

GirasolTrigoAvenaSoja 2°Soja 1°Maiz


• Utilice como color de fondo un sombreado 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 el color Verde Mar 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.

Ejercicio 11: Generar gráficos a partir de rangos discontinuos

Encuesta

47%8%

27%63%

18%

Sí, muy conforme

Sí, conforme

MedianamenteconformeNo conforme

Los datos de las respuestas los encontrará en el archivo Encuesta.xls.

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.


• Título: Encuesta.

• El área de trazado se ha triplicado respecto de su tamaño predeterminado.

• 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 colores de las pociones del gráfico original son amarillo, celeste y naranja claro. De

modo de poder visualizar el valor del porcentaje.

• Se ha dispuesto la leyenda en el lateral derecho.

Tema: Ejercicios Integrados

Ejercicio 1:

Función Buscarv, Concatenar, Es error, Formato Condicional, Insertar Comentario

El libro de trabajo será “LISTADOEMPLEADOS.xls”.

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"

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.xls”

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 DESCRIPCION (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.

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.xls”.

1. En la solapa FUNCIONES LOGICAS 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.

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.

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?

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.

Nota: Algunos de los ejercicios que se encuentran en las prácticas de Excel han sido

tomados como modelo, o inspiraron la temática de otros ejercicios. Podemos

mencionar algunos autores como:

Tiznado, Marco Antonio con la edición de la colección “Sc@pe” de McGrawHill

Caraballo, Silvana – Cicala, Rosa – Díaz, Bibiana – Santurio, Wilson con la edición de los Textos “Informática I y II” de Ediciones Santillana.

Technology

excel avanzado practicas