ejercicios excel

Departamento de Tecnología. UD. 1 Hoja de Cálculo

I.E.S. Guadalerzas

1. EJERCICIOS HOJA DE CÁLCULO. Los Ejercicios se pueden realizar tanto con Openofficeorg.Calc o Openofficeorg.Calc o con Excel. Excel. Alumno1 –EJERCICIO..? Una vez realizados todos los ejercicios se comprimen en un solo fichero con nombre: Alumno1 -HOJA DE CÁLCULO ¡NO SE CORREGIRÁN ACTIVIDADES QUE NO CUMPLAN LOS REQUISITOS ANTERIORES! SI SE DETECTAN ACTIVIDADES COPIADAS ENTRE GRUPOS, NO SERÁN VÁLIDAS PARA NINGUNO DE ELLOS. Los ejercicios 1 al 4 se hacen todos dentro de la misma hoja de cálculo cá lculo pero en diferentes hojas. https://www.youtube.com/playlist?list=PLp-d73DwawiwvtxtgokufIvQ7kN6b8F5w

EJERCICIO 1. 1. 2. 3.

CALCULOS BÁSICOS

Crea un libro nuevo Renombra la primera hoja del libro como “Ejercicio 1” Vamos a practicar el auto rellenado. Para ello coloca los datos en los encabezados de las columnas como se te indica.

Para rellenar con series en Excel. Pestaña de inicio y al final Para rellenar series en calc: Editar, Rellenar, Series

4.

Columna A, empezando en la fila 3: Introduce 25 valores en una progresión aritmética (de un valor se obtiene el siguiente sumando) que comience en 5 de razón 3 5. Columna B, empezando en la fila 3: Introduce 25 valores en una progresión geométrica (de un valor se obtiene el siguiente multiplicando) que comience en 3 y de razón 2 6. Realiza las operaciones indicadas en la cabecera de cada columna. Para la raíz utiliza la función RAIZ(numero) 7. En la segunda parte de este ejercicio, se trata de aplicar “formato “formato condicional”, condicional”, 8. Las celdas que sean mayores que (“MAYOR QUE”) el valor recogido en J1 han de poner el valor de la celda tachado. 9. Las celdas que sean menores que (“MENOR ( “MENOR QUE”) QUE” ) el valor recogido en J2 han de poner sus valores en rojo negrita y el fondo de celda verde. 10. Ojo que ese valor se pueda cambiar, no siempre 15 y 60.

EJERCICIO 2.

CALCULOS BÁSICOS

11. Renombra siguiente hoja del libro como “Ejercicio 2” 12. El siguiente ejercicio pretende calcular el total de alumnos matriculados en el primer curso, así como el porcentaje de hombres y mujeres. 13. Para su realización seguimos los siguientes pasos: 14. Introducir la tabla que se muestra a continuación teniendo en cuenta que las celdas sombreadas nos indican la columna y la fila de la hoja de cálculo. 1 2 3 4

A

B

TITULACION

MATRICULAS PRIMER CURSO REPITEN NUEVOS Total Mujeres Total Mujeres

C

Grado en Veterinaria

10

2

D

232

E

F

G

H

Total

TOTAL %Mujeres %Hombres

124

1

Departamento de Tecnología. UD. 1 Hoja de Cálculo 5 6 7 8 9 10

Grado en Ciencia y Tecnol. Alimentos Grado en Biología Grado en Química Grado en Física Grado en Bioquímica Grado en Ciencias Ambientales

13 22 22 9 8 4

10 10 9 1 5 4

38 155 168 53 14 99

I.E.S. Guadalerzas 27 98 91 18 7 58

15. Calcular el valor de la columna Total (Repiten + Nuevos). - La fórmula que se utilizará es una suma y por tanto se utilizará el operador “+”. Para introducir la fórmula se selecciona la celda F4 (correspondiente al total de alumnos de la titulación Grado en Veterinaria), escribimos el signo igual (obligatorio siempre que se vaya a introducir una fórmula), y a continuación escribimos la fórmula: =B4+D4. =B4+D4. También podemos ir seleccionando con el ratón las celdas que se van a sumar: - Escribir el signo =. - Seleccionar con el ratón la celda B4. - Escribir el signo +. - Seleccionar con el ratón la celda D4. - Para introducir la misma fórmula para el resto de las titulaciones seleccionar la celda F4, copiar la formula, y pegarla en el resto de celdas (desde F5 a F10), o bien hacer clic con el botón izquierdo del ratón en el cuadro de relleno de la celda seleccionada y arrastrarla hasta la celda F10. 16. Calcular el porcentaje de Mujeres y de Hombres sobre el Total calculado. - El porcentaje de mujeres se calcula dividiendo el total de mujeres entre el total de alumnos, la fórmula introducida en la celda G4 será =(C4+E4)/F4. =(C4+E4)/F4. - A continuación debemos dar formato de porcentaje (pulsando el botón % de la barra de herramientas) lo cual equivale a multiplicar el resultado por 100. - El porcentaje de hombres se calculará mediante la fórmula =1-G4. =1-G4. - Copiar estas fórmulas para el resto de las titulaciones.


Renombra siguiente hoja del libro como “Ejercicio 3” Crea la siguiente factura de teléfono, elige los colores que te parezcan. Los campos relativos a la columna “Sumas” así “Sumas” así como la media de gasto, han de ser todos calculados.


CALCULOS BÁSICOS

REFERENCIAS ABSOLUTAS Y MIXTAS

Renombra siguiente hoja del libro como “Ejercicio 4” Antes de hacer el ejercicio infórmate de las referencias absolutas y las referencias mixtas, es decir, comprende las referencias en que se fija la fila o la columna ($A1, A$1) o ambas ($A$1). El ejercicio consiste en crear una tabla de multiplicar como la que se muestra a continuación en la que cada celda contiene el producto de la fila por la columna correspondiente.

2


4. 5.

6.

7.

I.E.S. Guadalerzas

Primera parte: Crea una tabla como la siguiente. Ha de funcionar de la siguiente manera, si se introduce un número en la celda F1, ha de actualizarse toda la tabla con los valores correspondientes a la tabla de dicho número (pero todo de forma automática). A la hora de generar la tabla hay que tener en cuenta que se han de hacer el menor número de pasos posibles, por lo que generando la fila de 1x… y arrastrándola hacia abajo, ha de generarse el resto de la tabla. Para hacer el ejercicio, es necesario utilizar algunas referencias Absolutas. Segunda parte

8. 9.

Copia la tabla adjunta debajo de la anterior, Obtén una tabla resultado donde el valor de cada celda es el producto de la fila por la columna, para ello debe crear una fórmula en la celda B4 utilizando el tipo de referencias adecuado y propagarla al resto de la tabla. Solución La fórmula que hay que introducir en cada celda es el producto del valor contenido en la columna A y en su misma fila, por el valor contenido en la celda de su misma columna y fila 3 tal y como muestran las flechas en la tabla anterior donde el valor de la celda D5=A5*D3. Así los resultados a obtener serían: - B4=A4*B3 - C4=A4*C3 - D4=A4*D3 - B5=A5*B3 - B6=A6*B3 - Etc. 10. Si observamos las fórmulas anteriores nos daremos cuenta que las referencias a la columna A y a la fila 3 nunca cambian. 11. Sin embargo si sólo se usan referencias relativas al copiar la fórmula de la celda B4=A4*B3, al resto obtendríamos los siguientes resultados: Al copiarla hacia la derecha: C4=B4*C3, D4=C4*D3, E4=D4*E3, etc. Es decir, la referencia a la columna A en el primer multiplicando va cambiando, la solución sería fijar esta columna. Al copiarla hacia abajo: B5=A5*B4, B6=A6*B5, B=A7*, etc. En este caso la referencia a la fila 3 en el segundo multiplicando va cambiando, la solución será fijar esta fila. Por tanto la fórmula correcta a introducir en la celda B4 sería =$A4*B$3. 





EJERCICIO 5. 1. 2.

3. 4. 5.

FUNCIÓN “SI”

Crea en la columna D un campo llamado “Calificación en texto” Tenemos la siguiente relación de alumnos con su respectiva calificación final en una materia que exige un 7 para aprobar. La condición es: Si la calificación es mayor a 7.0 debe decir APROBADO, si no, debe decir SUSPENSO. La fórmula es: =SI( C2>7;”APROBADO”;”SUSPENSO”) ¿Cómo harías para que el primer alumno no salga suspenso?. Hazlo. Mediante el formato condicional, haz que las celdas con “APROBADO” estén en verde y las que no en rojo.

3


EJERCICIO 6.

I.E.S. Guadalerzas

FUNCIÓN SI ANIDADA

1.

Tenemos un listado de alumnos y sus calificaciones finales, las condiciones para obtener una beca son: 1. Si la calificación final está entre 9.5 y 10, obtiene un 80% de beca 2. Si la calificación final está entre 8.5 y 9.4, obtiene un 40% de beca 3. Si la calificación final está entre 8.0 y 8.4, obtiene un 25% de beca 4. Si la calificación final es menor a 7.9, no se otorga beca (“SIN BECA”). 2. La fórmula se obtiene a partir de la anidación de si; (Aquí tienes una página que lo explica muy bien) y (aquí un video)

EJERCICIO 7.

CONTAR.SI SUMAR SI MAX

1.

Realiza una tabla como la siguiente, fíjate que has de introducir otros 15 valores más en la tabla. 2. Para rellenar la columna de coste por minuto, hay que utilizar una fórmula condicional: SI(Prueba lógica; valor si verdadero; valor si falso) . En la que la prueba lógica será que la celda de tipo de llamada sea =”LOCAL”. 3. Para la estadística final hay que emplear, por ejemplo, la fórmula =CONTAR.SI(rango;"LOCAL") Las celdas con el símbolo € han de tener formato moneda con dos decimales. No olvides poner formato moneda. REGISTRO DE LLAMADAS DEL DEPARTAMENTO Coste llamada Local:

0.07 €

Coste llamada Nacional:

0.15 €

Coste llamada Móviles FECHA DE LA LLAMADA

TIPO LLAMADA

0.3 €

DURACION EN MINUTOS COSTE x MINUTO COSTE LLAMADA

23/10/10

LOCAL

3

€

€

23/10/10

MÓVIL

56

€

€

…

…

€

€

COMPLETAR con 15 más

4.

1. Total de minutos a pagar: 2. Total de llamadas LOCALES:

€ € Contar.Si

3. Cantidad de minutos LOCALES:

€

4. Total de llamadas NACIONALES:

€

5. Cantidad de minutos NACIONALES:

€ Función SUMAR.SI

6. Total de llamadas MÓVILES:

€

7. Cantidad de minutos MÓVILES:

€

8. Total de llamadas:

€

9. Coste total de llamadas:

€

10. Duración de llamada más larga:

€ Función MAX

11. Llamada más corta ¿? Para calcular el Coste por minuto; SI(TIPO DE LLAMADA=”LOCAL”; COSTE LOCAL; SI(TIPO DE LLAMADA=”MÓVIL”; COSTE MÓVIL; (SI TIPO DE LLAMADA=”NACIONAL”;COSTE NACIONAL”;0))). También tenéis la opción de hacerlo con BUSCARV, pero donde indicamos los costes de cada tipo de llamada ha de poner exactamente lo mismo que en el tipo de llamada.

EJERCICIO 8.

FUNCIÓN SI

4


I.E.S. Guadalerzas

1.

Teniendo el siguiente marcador de unos partidos de futbol, elabora una fórmula que califique si el equipo LOCAL tuvo: VICTORIA, DERROTA o EMPATE.

2.

Aplica formato condicional, para que la fila completa de las “DERROTAS”, esté en rojo, la de las “VICTORIAS” en Verde y los “EMPATES” en azul.

EJERCICIO 9. 1. 2.

TRABAJAR CON FUNCIONES

Abre el archivo P10_funciones. Agrega las hojas al libro de trabajo actual.

5


EJERCICIO 10.

I.E.S. Guadalerzas

RAÍCES Y CONDICIONES

1.

Renombra siguiente hoja del libro como “Ejercicio 7” Vamos a realizar una hoja de cálculo que resuelva ecuaciones de 2º Grado, para ello, plantea un entorno como el siguiente. A,B,C, son los coeficientes de la ecuación, que los rellenará el usuario de la hoja de cálculo (son distintos para cada problema) Solución 1 y Solución 2, han de mostrar la solución que hemos calculado de forma auxiliar, por lo que será una referencia a dichas celdas. Si no existe dicha solución (caso de que el radicando sea negativo) ha de mostrar el mensaje “SIN SOLUCIÓN” en color Ro jo. Para esto hay que emplear la fórmula condicional. 2. Pasos: 1. Calcular el radicando (b2-4AC).

6


2. 3.

I.E.S. Guadalerzas

La Raíz solo la podremos calcular si el Radicando es positivo: a. SI (celda radicando>=0;Raíz;”sin solución”). Solución 1 a. SI (celda radicando>0;Resultado de aplicar la fórmula de la ecuación de segundo grado con suma; ”sin solución”). √   Para el que no se acuerde    b. Igual pero con la resta

EJERCICIO 11.

REPASO y FRECUENCIA

1.

El archivo Europa contiene datos sobre los países de Europa. Copia y pega los mismos en una hoja de tu libro actual. 2. Convierte los datos en una tabla. Comprueba que al convertirlo en tabla, puedes filtrar y ordenar valores por la cabecera de la columna. 3. Da formato a las columnas de Superficie y Población para que los números tengan puntos de separación de miles. 4. Calcula los totales de Superficie y Población. 5. Utiliza el formato condicional para que los países con densidad mayor de 100 hab/km salgan en letra roja. 6. En la columna G debe poner: “Pequeño”, “Mediano” o “Grande” según si la columna C es inferior a 100.000 (“Pequeño”); entre 100.000 y 500.000 (“Mediano”); o mayor de 500.000 (“Grande”). Utiliza para hacer esto la función SI (utiliza varios SI anidados). 7. En las celdas de la K6 a la K11 vamos a contar el número de países que están en el rango de densidades marcado en las celdas k6 a k11. Utiliza la función FRECUENCIA. Como FRECUENCIA devuelve una matriz, debe terminarse con Ctrl + Mayús + Intro. Aquí tienes un ejemplo de utilización 8. Calcula el tamaño máximo y mínimo (Celdas K15 y K16). 9. Ordena la hoja por la población de mayor a menor. 10. Haz un gráfico de barras que incluya el país y la población. Modifica el tamaño de letra de los nombres de los países en el gráfico y ponlo en 6. Colorea las barras de rojo si la población es mayor de 60.000.000, de amarillo si está entre 60.000.000 y 40.000.000 y déjalo del color que ha salido si es inferior a 40.000.000. Aquí tienes un ejemplo. Inserta junto a población las columnas auxiliares que necesites, y rotúlalas con el nombre del contenido, pon la fórmula que las genera, y una vez hecho el gráfico ocúltalas para que no distorsionen la información. a. Al ocultar las filas, desaparecen los datos del gráfico, para que se muestren nuevamente.

EJERCICIO 12. Nombre

Primer Apellido

FILTROS Segundo Apellido

Edad Sexo

Nº de hijos

Salario Mensual

Salario Anual

Andrés Luisa Juan Enrique Juan Andrés Carmen María

Conde Corrales Salve García Jiménez Jiménez López López

Sintas Fernández Duque Sánchez García Martínez Bastida Gómez

43 35 28 34 29 41 30 31

M F M F F M M F

1 0 2 3 0 6 0 1

1.202 2.705 1.112 992 1.154 793 751 1.190

16.828 37.864 15.566 13.883 16.155 11.107 10.518 16.660

Leandro

Pérez

Gómez

28

M

0

980

13.715

Anastasio

Marín

Solá

55

M

4

3.209

44.932

Ana

Martínez

Esteve

29

F

1

1.803

25.243

En esta práctica vamos a utilizar los filtros. 1. Selecciona la tabla anterior completa. 2. En Calc, ve a Datos “Filtro Automático” 3. En Excel ve a Datos “Filtro” 4. Utilizando los filtros, haz capturas de pantalla con los resultados para los siguientes filtros. (FILTRAS, CAPTURAS, PAINT, RECORTAS, PEGAS EN LA HOJA DE ÉXCEL INDICANDO QUÉ FILTRO ES) 3. FILTRAR

7


I.E.S. Guadalerzas

A. Los empleados que no tienen hijos. B. Empleados que ganan entre 10.000 y 20.000 euros al año. C. FILTRO AVANZADO: Mostrar los empleados menores de 40 años y que ganen más de 1.000 euros mensuales. D. FILTRO AVANZADO: Mostrar las empleadas mayores de 30 años.

EJERCICIO 13. 1.

2.

3.

4.

5.

6. 7.

8.

HACER SÓLO EN UN LIBRO. Recuerda llamarlo “apellidos + nombre +ejercicio 12”. El objetivo es elabora un crucigrama como el de la figura, que ha de comprobar que la palabra completa es la solución correcta. Cambia el nombre de la Hoja 1 a “crucigrama” y el de la hoja 2 por “solución”. Fíjate en la imagen de al lado, crea primero una estructura de crucigrama como la de la figura, al menos ha de tener 15 palabras relacionadas con el tema de las hojas de cálculo. En la hoja 2 escribe en la primera columna números del 1 al 15 y en la segunda las soluciones por orden. Debajo del crucigrama, como en la imagen, pon las definiciones asociadas a cada número. Validación de las respuestas. a) Mostrar, al lado de la definición, BIEN en verde si es correcta, MAL en rojo si no lo es y nada si hay algún hueco:  Para Bien o Mal hay que comparar el resultado de “CONCATENAR” las celdas en las que se ha de poner la palabra y la “palabra correcta”. SI(Concatenar…=”Palabracorrecta”; “BIEN”;”MAL”).  Utilizar formato condicional para poner de distinto color BIEN y MAL.;  Para detectar si hay algún hueco utilizar la función “CONTAR.BLANCO”.  Se comprueba si hay algún hueco en blaco, y si no se verifica si todas las letras juntas forman la palabra correcta. b) Poner en Rojo o verde la palabra dentro del crucigrama .  Utilizando lo que has aprendido de formato condicional aplícalo para poner la palabra en el crucigrama roja o verde del mismo modo. Cuando termines todo el trabajo, oculta la hoja con las soluciones, bloquea todas las celdas excepto donde hay que rellenar el crucigrama y protege las hojas como sabes, también protege el libro como se indica aquí para evitar que vean las soluciones.

EJERCICIO 14. 1.

2.

3.

4.

CONCATENAR, PROTEGER HOJA.

FUNCIONES LÓGICAS (Y)

Los empleados de una agencia de turismo VENDEDOR VIAJES CANTIDADES VIAJE (se deben cumplir VENDIDOS ambas condiciones) estudiantil vendieron viajes durante el mes de A 150 15000 marzo. B 40 400 Se analizan dos variables: a) que la cantidad de viajes vendidos sea C 170 17000 mayor a 100 Y D 30 3000 b) que las cantidades recibidas sean mayores E 50 5000 a 500 € F 25 250 SI CUMPLEN AMBAS CONDICIONES, EL G 200 20000 EMPLEADO RECIBE COMO PREMIO UN VIAJE (“PREMIO”). EN CASO CONTRARIO, NADA (" ") . (Aquí tienes un documento que te puede ayudar ) Pon formato condicional en fondo verde para toda la fila de los premiados.

8


EJERCICIO 15. 1. 2.

3. 4.

2.

2.

3.

4.

FUNCIONES LÓGICAS (O)

En una tienda de productos informáticos se lleva un registro de las ventas realizadas. Para celebrar el 10º aniversario del establecimiento, en la venta de ordenadores e impresoras se concede un 10% de descuento y un 5% en los demás artículos. En la columna Descuento aparecerá el tipo de dto. aplicado en función del artículo comprado. Por tanto, para que el descuento sea del 10% se ha de cumplir una de dos posibles alternativas: que el artículo comprado sea un ordenador o que sea una impresora:

EJERCICIO 17. 1.

FUNCIONES LÓGICAS (Y)

Renombra una hoja nueva como “calificaciones”. A continuación se presentan los resultados de los ejercicios teórico y práctico de un examen. En la columna Apto / No apto aparecerá la palabra APTO sólo si de ambos ejercicios se ha obtenido una nota igual o superior a 5. De lo contrario, se mostrará NO APTO. Reproduce una tabla igual o similar en tu hoja. En caso de que el examinando sea apto, dicha palabra aparecerá en negrita y color blanco, sobre fondo marrón oscuro (formato condicional)

EJERCICIO 16. 1.

I.E.S. Guadalerzas

BUSCAR OBJETIVOS

Supongamos que tenemos una pequeña empresa, y que estamos desarrollando nuestro presupuesto de resultados para el año 2012. En nuestro presupuesto, detallamos los distintos conceptos de Ingresos y Gastos, y calculamos el porcentaje sobre las ventas de cada uno de ellos (En la columna D dividimos cada uno de las cifras de la columna C entre C4 la cual fijamos) Una vez detallado nuestro presupuesto, vemos que obtenemos un resultado antes de impuestos del 8,86% sobre las ventas Presentamos el presupuesto a nuestros socios y acordamos que este año nos gustaría obtener por lo menos un 10% de resultado neto antes de impuestos. Analizamos cada una de las partidas y decidimos que el único sitio que podemos recortar es el de la partida de Gastos de viaje. La pregunta es, ¿Que importe deberíamos tener en la celda C9 que detalla los gastos de viaje, para que la celda D13 arroje un porcentaje sobre ventas del 10%? a. Pestaña “Datos” ”Análisis si”Buscar objetivo. Escribe el resultado debajo, y pon también una captura de pantalla en la que se vean los valores empleados en la función pegar objetivo ( recorta solo la zona de la fórmula).

9


EJERCICIO 18. 1. 2.

I.E.S. Guadalerzas

FECHA Y HORA

Abre la hoja de nombre “Gimnasio”. En el asistente de funciones, (botón fx), estudia lo que hacen las funciones de fecha, por ejemplo ¿cuál te da la fecha de hoy, o cuál te devuelve los años de una fecha….

3. 4. 5. 6.

Copia el contenido del libro Gimnasio en tu Libro de Calc en la hoja Gimnasio. Aquí tienes un enlace que puede ayudarte con los cálculos. Rellena el código de los socios utilizando la opción rellenar o arrastrando del controlador de relleno Calcula la columna Cuota teniendo en cuenta las categorías de los socios, correspondiendo 25€ a la categoría “A” y 18 € a la categoría “B”. (Utiliza la función SI) 7. Inserta una columna entre Fecha de Nacimiento y Barrio, con el nombre Edad, y calcúlala. Busca para ello dentro de las funciones de Fecha y hora. La edad cambiará con la fecha actual. 8. En la columna Tipo de Socio se debe mostrar el texto “Adulto” si el socio tiene más de 30años, de lo contrario se debe mostrar el texto “Joven”. 9. Calcula el tiempo de permanencia del socio, en días, meses y años, a la fecha actual. 10. Para poder agilizar la consulta de información, por ejemplo saber cuántos tienen una edad, o llevan un número de años de socio podemos convertir los datos en una tabla. 11. Selecciona todos los datos sin cabecera. (podríamos hacerlo también con la cabecera, pero al tener celdas combinadas dará problemas. 12. Insertar tabla (Excel) o Datos-Filtro Automático (Calc). Cuando pregunte que si primera fila encabezado, decir que no. FUNCIONES QUE SE PUEDEN UTILIZAR: HOY(), AÑOS(…), FRAC.AÑO(…), SIFECHA(…)

EJERCICIO 19.

GRÁFICOS I

1.

Reproduce la siguiente hoja:

2.

Calcula los totales para la columna TOTAL VENTAS. Realiza el gráfico de barras correspondiente al total de ventas de los diferentes meses. Realiza el gráfico de barras apiladas de los meses enero, febrero y marzo. Realiza el gráfico de sectores para las ventas mensuales de forma que veamos qué fracción de nuestras ventas se realizó en cada uno de los meses. Realiza un gráfico de líneas sobre la variación que experimentan los dos productos a lo largo de todos esos meses. Realiza un gráfico de columnas tridimensional donde aparezcan las ventas del Producto1 y el Producto 2, durante todos los meses. Para ello ten en cuenta los siguientes datos: TITULO: VENTAS PRODUCTO 1 Y 2. TITULO EJE (X) : MESES. TITULO EJE (Y): UNIDADES VENDIDAS. Haz que la leyenda aparezca en la esquina superior derecha. Haz que aparezca el valor en cada columna (etiqueta de datos). Pon el siguiente formato al Título del gráfico: Borde color verde, sombreado, área naranja, efectos de relleno 2 colores. Formato leyenda, igual que el del título. Formato serie de datos “Producto 1”, trama diagonal verde hacia abajo. Formato serie de datos “Producto 2”, área naranja. Formato eje (x), alineación vertical. Color de fuente Verde. Área de trazado amarilla. Formato área el gráfico azul claro. Efectos de relleno 2 colores.

3. 4. 5. 6. 7.

     

     

10

Departamento de Tecnología. UD. 1 Hoja de Cálculo 

EJERCICIO 20.

I.E.S. Guadalerzas

Borde del área del gráfico, grueso y verde oscuro.

GRÁFICOS II

1. 2.

Los GRÁFICOS DE LÍNEAS son útiles sobre todo para comprobar la evolución de una serie de valores. Abre un nuevo Libro de Excel (llámalo Gráficos) y crea la siguiente tabla, e incluye las medallas obtenidas por España en las últimas 8 Olimpiadas: 3. Crea un gráfico que muestre en una línea las medallas de oro obtenidas a lo largo de las 8 olimpiadas. Introduce en el gráfico realizado los siguientes cambios de Nº de medallas formato: Oro Plata Bronce El texto de los rótulos del eje X estará alineado en Los Ángeles 1984 vertical. El tamaño del texto de los dos ejes (X e Y) se Seúl 1988 Barcelona 1992 cambiará a 8 ptos. Atlanta 1996 La escala del eje Y variará de 2 en 2 y no de 5 en 5. El título del eje X se desplazará a la derecha para que Sidney 2000 Atenas 2004 no interfiera con los valores del eje. Pekin 2008 Cambia el color de la línea del gráfico a rojo. Elimina el sombreado gris del área de trazado (la Londres 2012 delimitada por los dos ejes) 



 

 

4.

GRÁFICO DE COLUMNAS Útil sobre todo para comparar dos o más series de valores (en este caso, los resultados de los tres partidos). Crea un gráfico de columnas que compare el número de medallas de oro, plata y bronce conseguidas en cada olimpiada. En este caso, insertarás el gráfico en una hoja nueva. En primer lugar, selecciona el rango A2:D7 de la Hoja 1. Luego ve a Insertar , Gráfico.  Paso 1: aparece por defecto seleccionado el gráfico de Columnas, y el subtipo 1, así que pulsa Siguiente (si quieres, también puedes seleccionar un modelo 3D).  Paso 2: aparece el rango y la disposición de los datos (en columnas). Pulsa Siguiente.  Paso 3: como título del gráfico escribe Comparativa de medallas Como título del eje de ordenadas (Y), Nº de medallas . A continuación, selecciona la ficha Rótulos de datos y activa la casilla Valor (de esta forma, el nº de medallas de cada tipo aparecerá en el gráfico).  Paso 4: sitúa el gráfico en una hoja nueva. Pulsa Terminar . 5.

GRÁFICO DE CIRCULAR O DE TARTA es útil sobre todo para comparar dos o más series de valores (en este caso, los resultados de los tres partidos). Sirve para representar, en términos de porcentaje, las distintas partes de un todo. Sólo permite representar una serie de valores cada vez. Este gráfico nos servirá para comprobar la distribución de las medallas en las olimpiadas de 1984. Lo crearemos en una hoja aparte. En la Hoja 1 selecciona el rango de celdas A2:D3. Luego ve a Insertar , Gráfico, Como hoja nueva.  Paso 1: selecciona el gráfico Circular y el subtipo 1  Paso 2: aparece el rango y la disposición de los datos (en filas). Pulsa Siguiente.  Paso 3: como título del gráfico escribe Olimpiadas 1984. En estos gráficos, obviamente, no existen ejes.  Paso 4: sitúa el gráfico en una hoja nueva y pulsa Terminar .

EJERCICIO 21. 1. 2. 3. 4.

5. 6. 7.

GRÁFICOS III

Completa los datos de la siguiente tabla. Para completar el cálculo utiliza la función BUSCARV. Se aplicará un descuento de 3000€ al TOTAL CON IVA si dicho total es superior a 25000€. Para la elección del modelo, color y accesorios se ha de preparar una lista desplegable en el que podamos seleccionar los mismos. a. Datos b. Validación de datos c. Lista y seleccionar la lista de letras de cada tipo. El apartado “valores” hace referencia a los vehículos. El cuadro debajo de valores hace referencia al presupuesto. El gráfico de barras hace referencia a los precios de los modelos.

11


8.

9.

El gráfico de sectores hace referencia al TANTO POR CIENTO sobre EL PRECIO ANTES DE IVA Y DESCUENTO de los tres parámetros que intervienen en el precio: MODELO, COLOR, ACCESORIOS. El valor CANTIDAD hace referencia al número de modelos. Para que aparezcan los valores

EJERCICIO 22. 1.

I.E.S. Guadalerzas

GRÁFICOS-LÍNEA DE TENDENCIA

Los resultados comparativos de la evolución del precio de un producto en el extranjero y en nuestro país a lo largo una serie de meses se han colocado en una hoja de cálculo

12


I.E.S. Guadalerzas

2.

Se constata que el precio se va encareciendo a un ritmo superior en el extranjero, por lo que, si la evolución continúa al mismo ritmo, llegará un momento en que ambos precios se igualarán y, a partir de entonces, será más económica la compra en el mercado nacional. 3. Como es lógico, interesa calcular cuándo se produce el encuentro. Para ello, trazaremos el gráfico de la evolución de precios, obtendremos las líneas de regresión (líneas de tendencia en Excel) y calcularemos dónde se cruzan. 4. Seleccionamos B2:D11 y accedemos a Insertar + Dispersión + Dispersión con líneas rectas y marcadores. 5. Hacemos doble clic en la leyenda y, en Opciones de leyenda, ponemos una marca en Inferior. 6. Para hallar la línea de tendencia, hacemos clic con el botón derecho en la línea de la serie Nacional (marrón), elegimos Agregar línea de tendencia y ponemos: a. Opciones de línea de tendencia → Tipo de tendencia o regresión: Lineal / Nombre de la línea de tendencia → Personalizado: Lin_Nac / Extrapolar → Adelante: 20 / Presentar ecuación en el gráfico: (activado). Repetimos la operación con la línea de la serie Extranjero (azul), poniendo los mismos valores excepto el nombre: b. Opciones de línea de tendencia → Tipo de tendencia o regresión: Lineal / Nombre de la línea de tendencia → Personalizado: Lin_Ext / Extrapolar → Adelante: 20 / Presentar ecuación en el gráfico: (activado). 7. Arrastra las ecuaciones a lugares que permitan asociarlas fácilmente con sus correspondientes líneas de regresión. 8. Se puede apreciar en el gráfico qué aproximadamente hacia el mes 20 se va a producir la inversión de precios. 9. Para calcular el punto exacto. a. Utiliza la celda F3 como x (pondremos valores en ella) b. Escribe la ecuación de extranjero en G3 (utilizando F3 como x) c. Escribe del mismo modo la ecuación de Nacional. d. Escribe en I3 la fórmula que diferencia ambas rectas. e. Utilizando la función buscar objetivo Datos + Análisis Y si + Buscar objetivo. Busca el valor de la celda mes que hace que esa diferencia sea 0. 10. En unas celdas diferentes vuelve a escribir “Mes-ExtranjeroNacional-Diferencia” pero esta vez en lugar de escribir las

13


I.E.S. Guadalerzas

ecuaciones, utiliza la función pronóstico para que genere los valores de cada ámbito. a. Utilizando la función buscar objetivo vuelve a realizar la búsqueda del mes en que se produce la inversión de precios.

EJERCICIO 23. 1. 2. 3. 4.

5. 6.

7. 8. 9.

Crea una hoja nueva que se llame “Alquiler”. Dale el siguiente formato (exceptuando los valores de los recuadros grises): Oculta las líneas de la cuadrícula. Dicha opción está en el pestaña “vista” Mediante validación de datos se ha de poder elegir el apartado “Coche” y el apartado “Extras”, tomando como referencia para las listas los cuadros de la parte superior. Si no recuerdas cómo se hace dicha validación aquí tienes una página y aquí puedes ver un video explicativo. El precio, tanto del vehículo como del extra, se ha de rellenar automáticamente, mediante la función “BuscarV” (el rango de búsqueda es el de los nombres de los vehículos/extras y sus precios). Para rellenar el Código del producto de forma automática no podemos utilizar la función Buscar V, porque el código se encuentra a la izquierda del valor que estamos buscando, por lo que debemos utilizar la función “ÍNDICE” que devuelve el contenido de una celda en un rango indicándole la fila y la columna. La columna será la 1 (seleccionando la columna de código y modelo o de código y extras), y para encontrar la fila, utilizo la función coincidir, que me dice en qué número de fila (dentro de la matriz) está el valor buscado. Aquí tienes un ejemplo para ayudarte. Y aquí tienes otro ejemplo Se pueden alquilar hasta tres coches. Para cada coche se puede elegir UN extra. Tanto el precio del coche como de los extras es por día. DESCUENTO: Si el número de Coches alquilados es igual a tres o se alquilan al menos dos coches SEAT LEÓN, se aplica un descuento del 10% Para el resto de campos aplica la función más adecuada de las que ya conoces.


REPASO-FUNCIÓN ÍNDICE

VALIDACIÓN DATOS

Vamos a generar una plantilla para hacer facturas, que se autocomplete. Para ello: Inventar una empresa que se dedique a la venta de algún producto. Crear las siguientes hojas dentro del libro para este ejercicio. Hoja1: Renombra a “Factura”. Pestaña color Rojo. Ocultar las líneas de cuadrícula Hoja2: Renombra a “Productos”. Pestaña color Azul Hoja3: Renombra a “Clientes”. Pestaña color Verde Hoja4: Renombra a “Tipo de clientes”. Pestaña color Naranja.

14


4.

I.E.S. Guadalerzas

En cada una de dichas hojas. Hoja Clientes: (al menos 10)

Nombre

Dirección

Localidad+C.P.

Hoja Productos: (al menos 20)

Telefono

CIF

Código A0025 (inventar)

Hoja Tipos de clientes (al menos 3)

Tipo de cliente Elegimos de un desplegable los tipos de clientes guardados en la hoja “Tipo de Clientes”. Denominación

Tipo de cliente Pequeño Mediano

Precio Unitario

Descuento % %

5.

En la hoja “Factura” crea una plantilla para facturas: Dale el formato de factura que quieras (puedes buscar algún modelo en internet). Ha de contener el membrete de la empresa (Todos sus datos) pon una imagen como logotipo. 6. En la hoja cliente selecciona todos los nombres de clientes, y asígnale el nombre de “nombreclientes” a ese rango. 7. En la hoja productos selecciona todos los nombres de productos, y asígnale el nombre de “nombreproductos” a ese rango. 8. En la hoja Tipos de clientes selecciona todos los tipos de clientes, y asígnale el nombre de “tiposclientes” a ese rango. 9. En la zona de la factura destinada a los datos del cliente agregar un desplegable con los nombres de todas las empresas clientes (utilizaremos cuadros de lista , se generan en Excel mediante Datos->Validar->Lista). Cuando pregunta el rango de valores para la lista, ponemos “=nombreclientes”. a. El resto de datos del cliente. Se autocompletarán al elegir del desplegable el nombre de la empresa. Pista: Hay que buscar dentro de la hoja clientes, por el nombre del cliente que hemos seleccionado. 10. El cuerpo de la factura estará compuesto de 15 filas, en cada una de las cuales con un cuadro de lista se podrán elegir los productos. Al elegir el producto, se cumplimentará automáticamente el código del producto y el precio unitario. En la misma fila también habrá celda para la cantidad y el total. 11. Cálculo total de la factura: Subtotal € Descuento € IVA 21% € TOTAL €  

El descuento dependerá del tipo de cliente que le hayamos puesto. Pista: Buscar dentro de la lista de clientes el tipo de cliente y el resultado buscarlo en la hoja tipo de cliente.

12. Para que no aparezca lo de VALOR o N/A en las celdas, hay que generar en las celdas donde busca datos (como código producto o precio unitario) una fórmula del tipo: 13. SI(la celda donde selecciono el cliente o producto tiene algo (<>””); buscarv;””) 14. Para que no ponga nada en el caso de no haber seleccionado un cliente o algún producto. 15. Para los que lo hacéis en CALC:

15


I.E.S. Guadalerzas

16. Crear tres “Macros” a) Una que limpie todos los datos que hemos puesto en la factura (clientes, productos y fecha) b) Una que guarde el documento en un PDF en el escritorio llamado “FACTURA”. c) Otra que ponga la fecha actual en la celda de fecha. Para ello pon la fecha, con el formato que quieras, en una celda fuera de la zona de factura y graba una macro que copie dicha celda en la celda de fecha de la factura. Macros en Excel: Videotutorial, pero lo crea como botón, podemos hacerlo asignando la macro a una figura que insertemos. No hace falta, como hace en el tutorial, editar la macro para borrar cosas. Crea dos botones (o dibuja dos figuras a las que les asignes la macro), una para “ imprimir” y otra para “limpiar la factura”

¡Guarda el libro como libro de macros, porque si no, no las guardará! EJERCICIO 25. COMBINAR EXCEL CON OTRAS APLICACIONES. COMBINAR CORRESPONDENCIA. 1. 2. 3. 4.

5.

Imagina que continuamos con la empresa del ejercicio anterior, y hemos desarrollado un nuevo producto. Redacta (en un procesador de textos), en la que presentes dicho nuevo producto, puedes utilizar esta como modelo. Agrega un encabezado con tus datos y tu logotipo. Al final de la carta invita a que revisen los datos suyos que constan en vuestro archivo y para ello generáis una estructura como esta: NOMBRE: <> o CALLE: <> o …. o Cada uno de los campos se ha de rellenar automáticamente para cada una de las cartas que vayamos a dirigir. Para hacer esto hay que combinar correspondencia, utilizando como destinatarios los datos almacenados en la hoja “Clientes” la hoja de cálculo generada en el ejercicio anterior. Video1 Video2

EJERCICIO 26. 1. 2.

TABLAS DINÁMICAS. I

Este ejercicio se hace en un libro junto con el siguiente. Aquí tenéis un tutorial para saber qué son las tablas dinámicas y ver su utilidad

16


EJERCICIO 27. 1. 2.

3.

I.E.S. Guadalerzas

TABLAS DINÁMICAS. II

Las tablas dinámicas nos permiten filtrar información masiva de manera rápida y sencilla con unos resultados fiables 100% (no así cuando usamos fórmulas). El siguiente ejercicio que presentamos contiene información de 2.000 clientes de una compañía de telecomunicaciones. La información disponible es algo de información personal (sexo, año de nacimiento, provincia) e información interna (importe de la factura, fecha de la factura, tipo de servicio prestado). Esta información se encuentra en una tabla inicial con los datos en bruto. En la siguiente imagen se puede ver una muestra de la tabla de datos que usaremos para nuestro análisis. (archivo usuarios.xlsx)

17


4. 5.

I.E.S. Guadalerzas

Copia los datos junto El ejercicio consiste en obtener los datos necesarios de la tabla para poder hacer las siguientes tablas dinámicas: a) Importe por mes y servicio b) Segmentación de servicios por edad c) Segmentación de servicios por sexo d) Importe por localidad e) Diferencias porcentuales entre localidades f) Top 20 mayores facturas acumuladas

Revisar dinamicas/

http://www.excelyvba.com/ejercicio-de-tablas-

1.1. Hacer una encuesta en google docs y analizar los resultados, como la que hicieron para valorar cuál era la mejor oferta

1.2. EJERCICIO 5 Renombra siguiente hoja del libro como “Ejercicio 5” Captura los siguientes números, y cópialos en la columna A: 43, 42, 39, 27, 32, 36, 36, 36, 37, 38, 38, 25, 25, 24, 22, 45

1.

Cópialos en la columna B y ordenalos del mayor al menor (descendente). 6. Utiliza las fórmulas necesarias para: 2. Calcula el número de datos, es decir, cuentalos 3. Encuentra el número menor 4. Encuentra el número mayor 5. Calcula el promedio 6. Calcula la desviación estándar 7. Calcula la varianza 8. Encuentra la mediana 9. Encuentra la moda ¿Cómo se hace ? 1. Para ordenar: Selecciona el rango donde están los números, elige la opción de menú Datos->Ordenar y selecciona el orden apropiado. (Los siguientes comando son para Calc, en Excel, has de buscar su equivalente.) 2. Para contar el número de datos vamos a utilizar la función FILAS. Para ello puedes escribir en la celda B17 la expresión =FILAS(A1:A16). 3. Para el mínimo usa la función MIN (cuidado con el acento) 4. Para el promedio usa la función PROMEDIO

43 42 39 27 32 36 36 36 37 38 38 25 25 24 22 45 Cuenta Mínimo Promedio D. Estándar Varianza Mediana Moda

45 43 42 39 38 38 37 36 36 36 32 27 25 25 24 22 16 22 34,06 7,32 53,53 36 36

18


I.E.S. Guadalerzas

5. Para la desviación estándar usa la función DESVESTA 6. Para la varianza usa la función VAR 7. Para la mediana usa la función MEDIANA 8. Para la moda usa la función MODA 7.

1.3. EJERCICIO 8 RESULTADO ELECCIONES Partido Votantes PRI 506 PAN 622 PRD 520 Ecuación: PT 210 Y= 4x2-3x-2 VERDE 800 La idea es que vayas dando valores a "x", para calcular el valor de Y= 4x2-3x-2 PPS 338 X Y "y", es decir que lo hagas con una formula. PORCENTAJE DE PRODUCCIÓN DE ENERGÍA 2… Crea una tabla como la que se muestra en ELÉCTRICA EN ESPAÑA SEGÚN SU FUENTE. PARA el ejemplo, llenando los valores de "x" Hacia abajo y calculando EL DIA 12/09/10 "y" según se indica en la formula Nuclear 17 % Fuel/Gas Carbón 7,4 % Ciclo Combinado 26 % Eolica 19.3 % 1.4. EJERCICIO 9 Hidráulica 13,6 Resto renovables 16.1 Renombra siguiente hoja del libro como “Ejercicio 8” Intercambios internacionales 0,7 Partiendo de las siguiente tablas, crea el climograma Renombra siguiente hoja del libro como “Ejercicio 8” Hay que crear 3 gráficas en este ejercicio. RESULTADO ELECCIONES: De barras PRODUCCIÓN ELÉCTRICA: CIRCULAR ECUACIÓN: Lineal con líneas suaves de aproximación cúbica.

(temperaturas y precipitaciones en el mismo) correspondiente para cada una de ellas. Título: Climograma clima ecuatorial Precipitaciones: línea de dispersión Temperaturas: Grafico de barras

Meses E F M A M J J A S O N D

Precipitaciones Temperaturas 104

26,3

148

26,6

250

26,2

329

26

262

25,7

255

24,9

93

23,7

298

23,6

657

24,2

461

24,4

255

25,5

64

25,9

19


I.E.S. Guadalerzas

En el ejercicio 1 repartido en clase tenéis que realizar los siguientes pasos: (en relación al pdf empresa_de_helados. 1. Pasar todas los precios de pesetas a euros (Utilizar un celda donde aparezca el tipo de cambio 1€ = 166,386 ptas, y hacer referencia a est a celda para la conversión) 1. En la tabla de "Unidades de producto" sólo la última columna. 2. En la tabla de "Costes variables por unidad", toda la tabla son precios, hay que crear un igual pero en euros (referencias a celdas). 3. En la tala de "Consumo de energía eléctrica mensual" la columna de precio. 4. En la tabla de "Coste fijo trimestral", toda la tabla, podéis añadir una fila al final para el cambio. 5. En la tabla de "Beneficio anual", toda la tabla, podéis añadir una fila en la parte inferior, para el cambio. 2. Calcular el Consumo de electricidad medio, en ca da trimestre(función PROMEDIO()). 3. Realizar un gráfico de este consumo, gráfico de líneas. 4. Calcular el coste de electricidad en cada trimestre. 5. Calcular el promedio de cada uno de los costes variables. 6. Calcular lo que representa, en %, cada uno de los costes variables respecto al total. 7. Realizar un gráfico de los promedios de cada uno de los costes variables, gráfico circular. 8. Calcular los costes totales y los ingresos de cada trimestre, empleando las ecuaciones dadas en el enunciado (Recomendable crearse una tabla, campos: los 4 trimestres, en las filas las diferentes ecuaciones.). 9. Calcular el beneficio de cada trimestre y el anual. 10. Realizar un gráfico de barras con los beneficios de los años anteriores y el calculado. 11. Guardar el ejercicio en la carpeta de 3ªevaluación, en formato xls y ods. http://www.escuelassj.com/mod/resource/view.php?id=6456 climograma esta guardado

el documento

20


I.E.S. Guadalerzas

Ya sabéis que la fecha de la Semana Santa es variable pues depende de las fases de la Luna. Existe un algoritmo que nos permite averiguar, para cualquier año, cuando podemos celebrar el Domingo de Resurrección. Aquí

tenéis

dos

imágenes

con

instrucciones

para

realizar

la

actividad.

21


I.E.S. Guadalerzas

http://javiermozo-excel-explicado.blogspot.com.es/2012/01/la-funcion-frecuencia.html función coincidir https://www.youtube.com/playlist?list=PLp-d73Dwawiwouxr48G-bM_myBZc0BmQ5 https://sites.google.com/site/ejerciciosoffice/ejercicios-de-excel-grado-superiorhttp://juegosexcel.com/categoria/generales/

8. 9.

Faltan ejercicios de gráficos, y de y y o y buscar v

http://javiermozo-excel-explicado.blogspot.com.es/search?updated-min=2013-0101T00:00:00%2B01:00&updated-max=2014-01-01T00:00:00%2B01:00&max-results=24

Tabla dinámica

http://www.tecnoruiz.es/tercero.html http://mariademolina.blogspot.com.es/search/label/Calc

22


I.E.S. Guadalerzas

EJERCICIO LÍNEA DE TENDENCIA

23

ejercicios excel

Recommend Documents