CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZANDO CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 1
Contenido Ejercicio 2.1.1 – Esquemas automaticos..................................................................................................... 4 Ejercicio 2.1.2 – Esquemas manuales ........................................................................................................ 4 Ejercicio 2.2 – Subtotales............................................................................................................................ 5 Ejercicio 2.3 – Funciones de Base de Datos................................................................................................ 6 Ejercicio 2.4 – BuscarV ................................................................................................................................ 7 Ejercicio 2.5 – Escenarios.......................................................................................................................... 10 PRACTICA 1 – Esquematizacion. ............................................................................................................... 11 PRACTICA 2 – Esquematizacion ................................................................................................................ 13 PRACTICA 3 – Subtotales .......................................................................................................................... 15 PRACTICA 4 – Funciones Estadisticas y Funciones Base de Datos............................................................ 16 Algunas Funciones Estadísticas...................................................................................................... 16 Algunas Funciones de bases de datos............................................................................................ 17 Practica 4.1 - Ejercicio........................................................................................................................... 18 Practica 4.2 - Ejercicio........................................................................................................................... 19 PRACTICA 5 – Funcion BuscarV................................................................................................................. 20 PRACTICA 6 – Funcion BuscarH................................................................................................................. 21 PRACTICA 7 – Funcion BuscarH o BuscarV................................................................................................ 22 PRACTICA 8 – Escenarios: Vacaciones ...................................................................................................... 23 PRACTICA 9 – Escenarios: Divisas de productos....................................................................................... 23 PRACTICA 10 – Escenarios: Analisis de Carolina ....................................................................................... 24 EVALUACION BLOQUE 2 ........................................................................................................................... 25 Ejercicio 3.1 – Buscar Objetivo.................................................................................................................. 27 Ejercicio 3.2 – Tabla de Datos ................................................................................................................... 27 PRACTICA 11 – Buscar Objetivo ................................................................................................................ 29 Ejercicio 3.3 Funcion Si ............................................................................................................................. 29 Ejercicio 3.4 – Funcion Si anidada............................................................................................................. 30 PRACTICA 12 – Evaluacion ........................................................................................................................ 30 Ejercicio 3.4.1 – Sumar Si .......................................................................................................................... 31 PRACTICA 13 – Funcion condicional. ........................................................................................................ 31 Ejercicio 3.5 – Formato condicional.......................................................................................................... 32 PRACTICA 14 – Formato condicional ........................................................................................................ 32 Ejercicio 3.5 Tablas Dinamicas .................................................................................................................. 32 CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 2
PRACTICA 15 – Tablas Dinamicas.............................................................................................................. 34 Ejercicio 3.7 – Graficos Dinamicos ............................................................................................................ 35 PRACTICA 16 – Grafico Dinamico.............................................................................................................. 36 Ejercicio 3.8 – Añadir imágenes a una grafica. ......................................................................................... 36 PRACTICA 17 – Añadir imagen a grafica ................................................................................................... 37 PRACTICA 18 – Buscar objetivos ............................................................................................................... 38 Practica 18.1 - Ejercicio......................................................................................................................... 39 Solver .................................................................................................................................................... 40 Practica 18.2 - Ejercicio......................................................................................................................... 42 Practica 18.3 - Ejercicio......................................................................................................................... 44 Practica 18.4 - Ejercicio......................................................................................................................... 45 PRACTICA 19 – Condiciones logicas vs Funcion Si().................................................................................. 48 PRACTICA 20 – Condicion Logica Y............................................................................................................ 48 PRACTICA 21 – Funcion BuscarV............................................................................................................... 49 PRACTICA 22 – Base de Datos, BuscarV, Ordenacion, Tabla dinamica .................................................... 50 PRACTICA 23 – Administracion de Escenarios.......................................................................................... 51 Practica 23.1 - Ejercicio......................................................................................................................... 52 Practica 23.2 - Ejercicio......................................................................................................................... 53 PRACTICA 24 – Funciones Condicionales y de Busqueda ......................................................................... 55 Practica 24.1 - Ejercicio......................................................................................................................... 63 Practica 24.2 - Ejercicio......................................................................................................................... 64 Practica 24.3 - Ejercicio......................................................................................................................... 68 Practica 24.4 - Ejercicio. ....................................................................................................................... 69 Practica 24.5 - Ejercicio......................................................................................................................... 71 Practica 24.6 - Ejercicio . ....................................................................................................................... 73 CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 3
Ejercicio 2.1.1 – Esquemas automaticos Aprendera a realizar esquemas automaticos en Excel 1. Realice la siguiente tabla en Excel: Relacion de Gastos Enero Febrero Marzo Abril Mayo Junio Total Agua 200 180 210 590 Luz 180 180 180 180 180 180 1080 Telefono 250 270 272 275 275 281 1623 Renta 1500 1500 1500 1500 1500 1500 9000 Total 2130 1950 2132 1955 2165 1961 12293 2. Seleccione todo el rango de la tabla, incluyendo las columnas y filas de totales. 3. Vaya a la pestaña DATOS, ubique el icono AGRUPAR, de un click en la flecha hacia abajo y seleccione AUTOESQUEMA. NOTA: Vera que que Excel selecciona automáticamente todas las filas seleccionadas y aplica la agrupación correspondiente. Ejercicio 2.1.2 – Esquemas manuales Aprendera a realizar esquemas manuales en Excel. 1. Realice la siguiente tabla en Excel: Municipio o Entidad Habitantes Porcentaje Delegacion Federativa Tijuana Baja California 1410700 1.37 Juarez Chihuahua 1313338 1.27 Izcatepec DF 1820888 1.76 Gustavo A Madero DF 193161 1.16 Ecatepec de Morelos Edo Mex 1688258 1.63 Nezahualcoyotl Edo Mex 1140528 1.1 Leon Guanajuato 1278087 1.24 Guadalajara Jalisco 1600940 1.66 Zapopan Jalisco 155790 1.12 Puebla Puebla 1485941 1.44 2. Seleccione un rango de filas como por ejemplo Jalisco. 3. Vaya a la pestaña de DATOS, y busque el icono AGRUPAR. De un click para agrupar dichas filas. NOTA: Agrupar manualmente significa que usted deberá seleccionar que filas son las que desea esquematizar. Sin embargo, estas filas deben estar contiguas; si acaso tiene filas dispersadas por toda la tabla, es recomendable hacer una ORDENACION primero por la columna que vaya a agrupar para poder realizar el esquema. CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 4
Ejercicio 2.2 – Subtotales Practicara sobre como poner subtotales. 1. Realice la siguiente tabla en Excel: Plantel Matricula Alumno CNCI Ajusto 12301656 SOFIA LIZETH RENDON RENDON CNCI Ajusto 12301936 FERNANDO ULISES BARRON GONZALEZ CNCI Aragon 1800873 MARIAN ANABEL RODRIGUEZ GOMEZ CNCI Aragon 1801225 FERNANDO RICO MONTOYA CNCI Aragon 1801305 JAVIER RAMIREZ NAJERA CNCI Aragon 1800728 ARGENIS GONZALEZ HERNANDEZ CNCI Aragon 1801758 RICARDO IVAN MATA GRANADOS CNCI Atlaticlo 9201440 JUAN CARLOS VELAZQUEZ ROMERO CNCI Atlaticlo 9200442 JESUS FRANCO CASTAÑEDA CNCI Atzcaoitzalco 4601638 FRANCISCO ALFONSO GRUZ GARCIA CNCI Atzcaoitzalco 4601632 KARLA IRENE BERLIN RODRIGUEZ CNCI Atzcaoitzalco 4600938 ANDREA GONZALEZ CONTRERAS CNCI Atzcaoitzalco 4601821 REYNALDO LOPEZ HERNANDEZ CNCI Cuicuilco 6302167 MAXIMILIANO VILLEGAS BATALLA CNCI Cuicuilco 6301817 MATILDE APARICIO CALVILLO CNCI Cuicuilco 6301708 ALEJANDRO GONZALEZ PEREZ CNCI Cuicuilco 6301832 ALDO ROSAS RANGEL CNCI Cuicuilco 6302150 JOSE FRANCISCO PALACIO ACEVEDO CNCI Cuicuilco 6301031 RENE OROPEZA SOTELO CNCI Cuicuilco 6301059 HUGO ALBERTO CABALLERO ORTEGA CNCI Cuicuilco 6301174 ODON JORGE ALEJANDRO DIAZ CNCI Cuicuilco 6300903 DAVID MORENTIEL JOSE CNCI Cuicuilco 6301590 NANCY MAR ALVAREZ CNCI Cuicuilco 3602301 ANGEL URIEL RIVERA NUÑEZ CNCI Cuicuilco 6301720 CLAUDIA MICHELLE LIRACHUNUÑEZ CNCI Cuicuilco 6301865 MIGUEL ALVARO MARTINEZ ARROYO CNCI Cuicuilco 6301931 DIEGO ALONSO AGUILA CASTAÑON CNCI Cuicuilco 6302060 JOSE LUIS CUELLAS NIÑO CNCI Cuicuilco 6302072 JESUS EDUARDO PALACIOS JUAREZ CNCI Cuicuilco 6301965 ADRIAN ARANDA DEITA CNCI Cuicuilco 6301389 ENRIQUE JESUS LEON MEDINA CNCI Cuicuilco 6301874 JAIME HECTOR TOVAR VIVAR CNCI Cuicuilco 6301837 GAMALIEL GUSTAVO GARCIA FLORES CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 5
CNCI Cuicuilco 3602740 VIRIDIANA CASTILLO CHAVEZ CNCI Cuicuilco 6302045 LILIANA RAMON DAMIAN CNCI Cuicuilco 6301842 JOS HIRVING POMPA RODRIGUEZ CNCI Cuicuilco 6302140 ALEXANDRA OVALLE RODRIGUEZ CNCI Cuicuilco 6302074 SERGIO OMAR ESPINOZA GOMEZ 2. A continuación, seleccione todo el rango de la tabla 3. Vaya a la pestaña DATOS, ubique el icono de SUBTOTAL. Haga click ahí. 4. Aparecera una caja de dialogo que le pregunta por algunas opciones: a. PARA CAMBIO EN. Esto le indica la agrupación según el cambio de dato en una fila. Si seleccionamos PLANTEL, agrupara por planteles. b. USAR FUNCION. Aquí le indicamos a Excel que tipo de subtotal queremos: CONTAR, SUMA, PROMEDIO, MAXIMO, MINIMO, PRODUCTO. c. AGREGAR SUBTOTAL A. Aquí indicamos en que columna queremos que aparezca el subtotal. Marque todas las que apliquen. d. De ACEPTAR. 5. Ahora Excel aplicara los cambios y mostrara la tabla con subtotales insertando filas según el tipo de plantel, y agrupándolos como esquema. Ejercicio 2.3 – Funciones de Base de Datos Aprendera a usar algunas de las funciones de bases de datos con las que cuenta Excel para manipular la información y obtener esta en base a criterios de consulta. 1. Realice la siguiente tabla: Nombre Edad Sexo Grado Tipo de 16 m Caliifcacion Evaluacion Emmanuel Rodriguez 17 m 1 9 extraordinario 20 m 2 7 ordinario Jonatan Vazquez 16 f 3 8 ordinario 20 f 1 8 extraordinario Abraham Gaytan 18 m 3 8 extraordinario 18 m 2 7 ordinario Paola Linderos 16 f 3 6 ordinario 1 7 extraordinario Alejandra Bautista Adolfo Ferruzca Rolando Campos Daniela Peña EDAD SEXO GRADO CALIFICACION EVALUACION 2 8 ordinario 17 m BDCONTAR CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 6
BDCONTARA BDMAX BDMIN BDSUMA BDPROMEDIO BDPRODUCTO Vera que en la parte superior esta la tabla de información. En la siguiente sección esta una tabla mas pequeña que es donde están los CRITERIOS de consulta. Notemos que tenemos los mismos títulos que en las columnas superior y en la celda inferior, tenemos el dato que queremos consultar. En la parte inferior, tenemos un listado de las funciones que vamos a aplicar. Casi todas las funciones se construyen asi: Función(rango_de_la_tabla,columna_donde_se_buscara,rango_criterios) BDCONTAR(A5:F13,B5,B15:B16) 2. Realice las funciones indicadas siguiendo el mismo ejemplo. NOTA: Consulte su Guia de Estudio donde se explica con detalle lo que hace cada una de las funciones, o bien, revise la ayuda de Excel en el apartado de funciones. Tambien puede pedirle a su profesor ayuda en cualquier duda. Ejercicio 2.4 – BuscarV Aprendera a usar las funciones de búsqueda con que cuenta Excel. 1. Realice la siguiente tabla: Clave Autor Titulo Precio 123321 Alejandro Dumas Los tres mosqueteros 350.00 135426 Arthur Conan Doyle Las aventuras de Sherlock Holmes 345.00 124578 Benito Perez Galdos Marianela 234.00 235678 Charles Dickens Cuento de Navidad 345.00 987654 Charles Dickens Historia de dos ciudades 234.00 986532 Charles Dickens Oliver Twist 432.00 CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 7
876521 Edgar Allan Poe El gato negro 34.00 124567 Edgar Allan Poe Los crimenes de la Rue Morge 123.00 234590 Fedor Dostoiewski Crimen y castigo 234.00 102938 Fernando de Rojas La celestina 345.00 457812 Franz Kafka La metamorfosis 456.00 567890 Gustave Flaubert Madame Bovary 321.00 124576 Jack Londo Colmillo Blanco 432.00 113366 JM Barrie Peter Pan 321.00 124577 Johann Wolfang Goethe Fausto 21.00 987066 Julio Verne De la tierra a la Luna 21.00 667788 Julio Verne La vuelta al mundo en 80 dias 321.00 335578 Leon Tolstoi Ana Karenina 231.00 123345 Leon Tolstoi Guerra y Paz 231.00 345677 Lewis Carroll Alicia en el pais de las maravillas 234.00 345789 Lope de Vega Fuenteovejuna 234.00 987234 Mark Twain El principe y el mendigo 343.00 567432 Mark Twain Las aventuras de Huckleberry Finn 34.00 789654 Mark Twain Las aventuras de Tom Sawyer 54.00 786655 Mary Shelley Frankenstein 223.00 443366 Miguel de Cervantes Saavedra Don Quijote de la Mancha 123.00 347890 Miguel de Unamuno La tia Tula 123.00 235473 Miguel de Unamuno Niebla 189.00 123654 Moliere Las preciosas ridiculas 178.00 876555 Moliere Tartufo CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 8
289.00 445677 Oscar Wilde El fantasma de Canterville 389.00 665523 Oscar Wilde 907866 Oscar Wilde El retrato de Dorian Gray 289.00 678955 Ruben Dario 456733 Tomas Moro La importancia de Llamarse Ernesto 186.00 554677 Victor Hugo 334466 William Shakespeare Azul 278.00 889906 William Shakespeare 234567 William Shakespeare Utopia 378.00 986543 William Shakespeare Los miresables 27.00 El mercader de venecia 387.00 El Rey Lear 188.00 Romeo y Julieta 288.00 Sueño de una noche de verano 178.00 Nota: puede abreviar el tamaño de la tabla no anotando todos los libros. 2. En una segunda hoja del libro de Excel, realice la siguiente tabla: Clave Autor Titulo Precio Los crimenes de la Rue Morgue 124567 Edgar Allan Poe Fausto La tia Tula 124577 Johan Wolfgang Goethe Don Quijote de la Mancha Los miserables 347890 Miguel de Unamuno Las Aventuras de Huckleberry Finn Las Aventuras de Tom Sawyer 443366 Miguel de Cervantes Saavedra El gato negro De la Tierra a la Luna 554677 Victor Hugo 567432 Mark Twain 789654 Mark Twain 875521 Edgar Allan Poe 987066 Julio Verne 3. Lo que vamos a buscar es el precio de los libros que aparecen en esta segunda hoja, por lo que la formula deberá ir en la celda PRECIO de cada fila. 4. La función a usar es BUSCARV (en ingles VLOOKUP), asi la formula queda: =BUSCARV(A3,Datos!A2:D41,4), donde A3, es el valor buscado –en este caso, la clave del libro-, DATOS!A2:D41, es el rango de toda la tabla que esta en la hoja1, que se renombro como DATOS; y 4, es la columna –precio- que tiene el valor que quiero que me regrese. 5. Lo mismo tenemos que hacer con el resto de la tabla. CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 9
Ejercicio 2.5 – Escenarios Aprendera a crear escenarios para responder la pregunta QUE PASA SI? Los escenarios muestran diferentes situaciones sobre una misma base de información. 1. Estamos planeando nuestras vacaciones y tenemos un presupuesto de 10,000 pesos. Vamos a seleccionar 3 distintos destinos para saber cual nos conviene mejor. 2. Comenzamos con el primer destino. Realice la siguiente tabla: Presupuesto para viajar Huatulco Dias Traslado 32 Hospedaje Alimentos 3000 3280 Eventos Suovenirs 2000 1000 3000 3000 500 500 9500 6780 3. Ahora, nos vamos a la pestaña DATOS, y ubicamos el icono ANALISIS Y SI? 4. Seleccionamos la opción ADMINISTRACION DE ESCENARIOS y aparecera una caja de dialogo con un cuadro de lista que contendra los distintos escenarios. (Al principio aparecera vacia). Damos un click en AGREGAR 5. Aparece otro cuadro de dialogo que nos pregunta como se llamara este escenario. Tecleeamos DESTINO1. 6. Despues, aparece una caja indicando las celdas cambiantes, seleccionaremos unicamente las celdas de la columna C (numero 3), incluyendo su titulo (para identificarlo). Ejemplo: C1:C7 7. Y damos ACEPTAR. Nuestro primer escenario ha sido creado. 8. Ahora modificaremos la columna C, cambiando los valores: Chiapas 3 5400 3000 500 8900 9. Una vez terminado, repetimos los pasos 3 al 7. Poniendo como nombre de escenario DESTINO2. 10. Por ultimo, volvamos a cambiar los valores de la columna C, con esta informacion: CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 10
Rivera Maya 3 dias 2 noches 11250 500 11750 11. Y volvemos a repetir los pasos del 3 al 7 poniendo como nombre de escenario DESTINO3. 12. Ahora, en nuestra caja de dialogo de ADMINISTRACION DE ESCENARIO, tenemos 3 destinos. En la parte inferior de esta ventana, tenemos un boton que dice MOSTRAR. Si seleccionamos DESTINO1 y luego pulsamos MOSTRAR, veremos los valores que introducimos al inicio. 13. De esta manera, podemos interpretar la pregunta: ¿Qué PASA SI VOY DE VACACIONES AL DESTINO1? ¿Qué PASA SI VOY DE VACACIONES AL DESTINO2? PRACTICA 1 – Esquematizacion. Trabajas en una fabrica que cuenta con 3 plantas de produccion. Se te encarga hacer un resumen de la produccion de las 3 plantas para mostrarlas al gerente. Como sabes que se trata de un resumen, necesitas agrupar la informacion y solo en caso de que lo pida, mostrarle el detalle. CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 11
CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 12
PRACTICA 2 – Esquematizacion Trabajas como profesor en una Universidad y necesitas entregar calificaciones de tus alumnos. Durante el curso, dictaste algunas practicas y ejercicios, asi como algunas otras actividades que sumadas nos dan la calificacion del alumno. Sin embargo, el formato que usaste (horizontal) no es el adecuado para la escuela que lo requiere de manera vertical. Crea primero la hoja de DATOS ORIGINAL y copiaras esta a otra hoja donde haras el esquema. Pero al hacerlo deberas usar la opcion de TRANSPONER para que dicho esquema quede correcto. Esta es la hoja original, y vemos que los nombres de los alumnos estan en filas y la informacion con las practicas en columnas. Y al final debe quedar asi: CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 13
Usando la opcion de TRANSPONER, hacemos que los alumnos queden como columnas y los puntos como filas para poder hacer la esquematizacion correctamente. CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 14
PRACTICA 3 – Subtotales Ahora vamos a practicar nuevamente con los subtotales. En este caso, tenemos un listado de nuestra musica favorita: Realiza unos subtotales para saber: a) Cuantas canciones/melodias tienes según el genero. b) Cuantas canciones según el interprete. CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 15
PRACTICA 4 – Funciones Estadisticas y Funciones Base de Datos A continuacion debera practicar con las funciones de base de datos que vio en los ejercicios anteriores. Esta practica necesita 2 libros de trabajo de Excel: BASES2.XLS y BASES3.XLS que practicamente contienen la misma informacion. Aunque en la practica se menciona que ya existen, en caso contrario usted debe crear los mismos tecleando la informacion que aparece en la imagen de mas abajo. Algunas Funciones Estadísticas. Página 16 =CONTAR(Rango de casillas). Cuenta la cantidad de casillas numéricas que hay en el rango. =CONTARA(Rango de casillas). Cuenta la cantidad de casillas alfanuméricas del rango indicado. =MODA(Rango de casillas). Devuelve el valor más común del rango. =PROMEDIO(Rango de casillas). Devuelve la media de los valores del rango de casillas indicado. CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO
Algunas Funciones de bases de datos. =BDCONTAR(Rango o nombre de la tabla;Número de columna;Criterio). Rango o Nombre de la tabla= Rango de casillas o nombre de la tabla. Número columna= Columna donde se encuentran los datos sobre los que se realiza la operación. Criterio= Rango donde se encuentra el criterio,(previamente indicado en forma de tabla). Cuenta el número de casillas de la columna Número de columna de tabla que cumplen la condición de criterio. =BDSUMAR(Rango o nombre de tabla;Número de columna;Criterio). Suma las casillas numéricas de la columna Número de Columna de tabla, que cumplan la condición del criterio. =BDPROMEDIO(Rango o nombre de tabla;Número Columna;Criterio). Calcula la media de los valores de la columna Número columna de tabla, que cumplan el criterio. =BDMAX(Rango o nombre de tabla;Número Columna;Criterio). Devuelve el valor máximo de la columna Número columna indicada de tabla, que cumpla la condición del criterio. =BDMIN(Rango o nombre de tabla;Número Columna;Criterio). Devuelve el valor mínimo de la columna Número columna de tabla, que cumpla la condición del criterio. • Criterio, son siempre como mínimo dos casillas, una que es el nombre de la columna y otra que es la condición que se ha de cumplir. • Número Columna es la posición que ocupa una determinada columna dentro de la tabla. Por ejemplo en ejercicio que se verá posteriormente, Lenguaje ocupa la columna cuatro. CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 17
Practica 4.1 - Ejercicio. Recupere el archivo BASE2.XLS, haga las operaciones siguientes: Ponga nombre a la tabla, no hace falta incluir la columna Media. Contar el nº de exámenes de todas las asignaturas, función =CONTAR. Contar nº de alumnos, función =CONTARA. Mostrar las notas más comunes de cada asignatura, función =MODA. Calcular la media de cada asignatura, función =PROMEDIO. Nombre Apellidos Clase Lenguaje Matemáticas Historia Media Txetxu Arazabaleta A 5 7 9 7,00 Itziar Bengoetxea B 7 5 5 5,67 Prudencia Gara C 3 8 5 5,33 Leocadia Garcia B 6 5 8 6,33 Estrellita Gutiérrez C 6 6 7 6,33 Romualdo Gutiérrez B 8 4 6 6,00 Godofredo Martineti A 7 5 6 6,00 Sigfrido Martínez C 5 7 8 6,67 Gumersindo Martínez C 7 6 5 6,00 Leovigildo Mastuerzo B 8 9 8 8,33 Heladio Parralo A 6 3 9 6,00 Urko Pérez A 7 4 5 5,33 Higinia Pérez A 3 4 7 4,67 Teodófilo Peritarpei A 6 3 9 6,00 Macarena Pitu B 6 5 6 5,67 Quintiliano Sánchez C 3 8 5 5,33 Romualdo Santemesmasses B 9 3 7 6,33 Ataulfo Segarra A 5 5 6 5,33 Demetrio Sinforoso C 7 7 3 5,67 Abunciana Torrelles A 8 7 3 6,00 Hug Urdiales B 8 6 7 7,00 Idoia Zumalacarregi C 4 6 8 6,00 Media General Lenguaje Matemática Historia s 6,1 5,6 6,5 Nº Exámenes 66 Nº Alumnos 22 Notas más Lenguaje Matemática Historia Comunes s 7 55 CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 18
Practica 4.2 - Ejercicio. Sobre la misma tabla, recupere BASE3.XLS. Inserte la siguiente tabla de criterios: Lenguaje Matemáticas Historia <=5 <=5 <=5 Lenguaje Matemáticas Historia >5 >5 >5 Mostrar los aprobados de cada asignatura. Mostrar los suspensos de cada asignatura, Utilice la función =BDCONTAR(Tabla;Columna Asignatura;Rango de Criterio). Recuerde que criterio son como mínimo dos casillas. Por ejemplo para contar los aprobados de Lenguaje, el rango del criterio serán las casillas ocupadas por Lenguaje y >5. Notas Suspens Aprobad os os Lenguaje 7 15 Matemática 11 11 s Historia 7 15 Inserte la tabla de criterios: Clase Clase Clase A BC Mostrar la media por clase y asignatura. Función =BDPROMEDIO(Tabla;columna asignatura;Rango de Criterio). Mostrar la nota máxima por clase y asignatura. Función =BDMAX(Tabla;Columna asignatura; Rango de criterio). CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 19
Media Por Clase y Asignatura Clase A Clase B Clase C Lenguaje 5,9 7,4 5,0 Matemáticas 4,8 5,3 6,9 Historia 6,8 6,7 5,9 Nota Máxima Por Clase y Asignatura Clase A Clase B Clase C 7 Lenguaje 89 8 Matemática 7 9 8 s Historia 98 PRACTICA 5 – Funcion BuscarV Usted trabaja en una empresa de ventas, y cuenta con un grupo de vendedores que cada mes le reportan el monto de lo vendido. Sobre eso aplicaremos un bono extra según la cantidad que haya vendido como estimulo por su trabajo. Aunque las cantidades presentadas en el ejemplo no correspondan a la realidad, lo que buscamos es identificar el monto de lo vendido al mes para aplicar dicho bono e identificar a nuestro vendedor. El dato a buscar es el sueldo. Página 20 Debera mostrar el porcentaje y el nombre del vendedor como se ve en la imagen. CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO
PRACTICA 6 – Funcion BuscarH - Copiar en la hoja 1 del libro de trabajo los siguientes datos: - Utiliza estos datos para realizar la misma factura pero en lugar de utilizar la función Buscarv utiliza la función BuscarH. - Una vez acabada de rellenar toda la factura con la función Buscarh. - Haz una trasnposición de esta matriz en la hoja 3 del libro de trabajo, utilizando la función Transponer. - Despues de transponer los datos en la hoja 3 vuelve a transponer los datos en la misma hoja 3 para que su aspecto sea el mismo que en la hoja 1. CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 21
PRACTICA 7 – Funcion BuscarH o BuscarV Realiza en un nuevo libro de trabajo lo siguiente. Esta es una factura, que aunque esta en ingles puede ser facilmente entendida. Lo que debes hacer es que cuando la persona teclee el numero de producto, automaticamente aparezca su descripcion, precio unitario. Al poner la cantidad, nos mostrara el precio final, el subtotal, y lo demas. En una segunda hoja del libro pondremos la siguiente informacion de nuestro inventario de productos: CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 22
PRACTICA 8 – Escenarios: Vacaciones Siguiendo con el ejemplo del viaje (ver ejercicio de Escenarios), establece tres escenarios o destinos turisticos a los que puedes ir con un presupuesto de 10,000.00 pesos; desglosando cuanto gastarias en transporte, en hospedaje, en alimentos, etc. PRACTICA 9 – Escenarios: Divisas de productos Elabora una tabla con el caso de una empresa mexicana que todas sus ventas las hace en Europa, pero su materia prima la compra en Estados Unidos; por lo tanto sus ingresos son en Euros, sus gastos de produccion son en Dolares y por estar en Mexico, el resto de sus ingresos y gastos son en Pesos. Por lo tanto, el tipo de cambio es muy importante. Hay tres posibilidades o escenarios de tipo de cambio: 1. Euro = 16.50; Dólar = 12.30 2. Euro = 16.80; Dólar = 12.10 3. Euro = 15.10; Dólar = 12.30 Para mas informacion sobre como realizar esta practica consulta tu Guia de estudio (pag 66). CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 23
PRACTICA 10 – Escenarios: Analisis de Carolina Carolina esta haciendo su presupuesto semestral y desea saber como le ira el resto del año. Para ello, tiene las siguientes preguntas: 1. ¿Que pasa si tengo pocos ingresos y muchos gastos? 2. ¿Que pasa si tengo muchos ingresos y pocos gastos? 3. ¿Que pasa si tengo pocos ingresos y pocos gastos? 4. ¿Que pasa si tengo ingresos normales y gastos normales? Para realizar esta proyeccion, necesitamos escenarios segun los ingresos y gastos. Primero necesitamos poner la informacion como sigue: Ahora necesita crear 2 escenarios: INGRESOS NORMALES y GASTOS NORMALES. Modifique la informacion de tal forma que los ingresos sean bajos y los gastos igualmente. Cree 2 escenarios mas para INGRESOS BAJOS y GASTOS BAJOS. Modifique nuevamente la informacion para que ahora los ingresos sean altos y los gastos igual. Cree 2 escenarios para INGRESOS ALTOS y GASTOS ALTOS. Por ultimo, muestre los escenarios al profesor como: INGRESOS ALTOS y GASTOS BAJOS CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 24
INGRESOS BAJOS y GASTOS ALTOS INGRESOS NORMALES y GASTOS ALTOS INGRESOS NORMALES y GASTOS BAJOS INGRESOS BAJOS y GASTOS NORMALES. Etc. EVALUACION BLOQUE 2 Retoma la relacion de libros, con su clave, nombre de autor, titulo y precio para aplicarle los siguientes temas: 1. Agrupar en esquema, obteniendo un subtotal de libros escritos por cada autor. 2. Aplicar las funciones BDCONTAR y BDCONTARA 3. Elaborar los escenarios en la lista de precios, suponiendo que los precios estan en dolares y tienes tres diferentes tipos de cambio. a. Escenario 1: dólar = 12.60 b. Escenario 2: dólar = 12.20 c. Escenario 3: dólar = 13.00 CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 25
BLOQUE 3 CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 26
Ejercicio 3.1 – Buscar Objetivo Vamos a suponer que necesitamos llenar una factura, y que en la parte inferior debemos escribir los siguientes datos: El problema es que sabemos que el valor de la mercancia es de 116 pesos ya CON IVA INCLUIDO. Dicho de otra manera, sabemos que el total es de 116 y que el IVA es el 16%. Nos hace saber cual es el precio de la mercancia. Es como hacer la formula al reves. Consulta tu Guia de Estudio pagina 70 a la 72. Ejercicio 3.2 – Tabla de Datos Elaboraremos una tabla de dos valores,con el siguiente ejemplo: PASO 1: Utilizando la funcion Pago(), capturamos la siguiente informacion: CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 27
PASO 2: Establecer las filas y columnas de nuestra tabla. La idea es que la formula que estamos usando, tome diversos valores y no de el resultado. ¿Cuánto debemos pagar modificando la tasa de interes y la cantidad de meses a pagar, por ejemplo a 6, 12 y 24 meses? PASO 3: Seleccionar el rango de la tabla e ir a la pestaña DATOS. Seleccionar la flechita del icono ANALISIS Y SI, para desplegar el menu y seleccionar TABLA DE DATOS. Introduce la celda de entrada para la fila. En este caso es el numero de meses para pagar y esta en B2 Introduce la celda de entrada para la columna. Que es la tasa de interes anual esta en B1. Aplica y ve los resultados. CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 28
PRACTICA 11 – Buscar Objetivo En el siguiente balance, aplica buscar objetivo para encontrar la cantidad que te hace falta para que el total sea igual a CERO. Asignale formato de numeros negativos con color rojo, como aprendiste anteriormente. Ejercicio 3.3 Funcion Si Tenemos la siguiente relacion de alumnos con su respectiva calificacion final. La condicion es: Si la calificacion es mayor a 7.0 debe decir APROBADO, si no, debe decir REPROBADO. La formula es: Página 29 =SI( C2>7,”APROBADO”,”REPROBADO”) ¿Cómo modificas para que el primer alumno no salga reprobado? CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO
Ejercicio 3.4 – Funcion Si anidada Tenemos un listado de alumnos y sus calificaciones finales, las condiciones para obtener una beca son: 1. Si la calificacion final esta entre 9.5 y 10, obtiene un 80% de beca 2. Si la calificacion final esta entre 8.5 y 9.4, obtiene un 40% de beca 3. Si la calificacion final esta entre 8.0 y 8.4, obtiene un 25% de beca 4. Si la calificacion final es menor a 7.9, no se otorga beca. La sintaxis es la siguiente de la formula: =SI(D7>=9.5,”80%”, SI(D7>=8.5,”40%”,SI(D7>=8,”25%”,”sin beca”))) PRACTICA 12 – Evaluacion Resuelve lo siguiente: Condicion Formula en Excel Cuando las dos celdas son iguales, se muestra la palabra “igual”. Cuando las dos celdas son diferentes, se muestra la frase “No es igual” Si C6 es mayor que 100, mostrar C6. De lo contrario mostrar 100 Si B5 es menor que, o igual a 10, mostrar B5. De lo contrario mostrar la palabra “Maximo” Si el valor mas grande en el rango, es mayor que o igual a la mitad de la suma del rango, entonces mostrar el valor mas grande. De lo contrario mostrar la mitad de la suma del rango. Si B8 no es igual a D6, verificar para ver si B8 es menor que 10. Mostrar 10 si lo es y B8 si no lo es. De lo contrario, mostrar D6, el que en caso sera igual a B8 CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 30
Ejercicio 3.4.1 – Sumar Si Otra funcion de condicion es la funcion SUMAR.SI() que permite hacer una suma condicional. La funcion es: = SUMAR.SI( rango, criterio, rango_suma) Ejemplo: =SUMAR.SI(A1:A9,”Alejandra”,B1:B9) PRACTICA 13 – Funcion condicional. Desarrolla la funcion condicional para el siguiente problema: Teneidno el siguiente marcador de unos partidos de futbol, elabora una formula que califique si el equipo LOCAL tuvo: VICTORIA, DERROTA o EMPATE. EQUIPO LOCAL MARCADOR EQUIPO VISITANTE MARCADOR RESULTADO España 1 Uruguay 2 Derrota Chile 5 Mexico 1 Victoria Suiza 3 Sudafrica 0 Victoria Honduras 0 Francia 0 Empate Brasil 2 Argentina 3 Derrota Portugal 0 Corea del Sur 0 Empate Costa de Marfil 3 Grecia 1 Victoria Corea del Norte 0 Nigeria 2 Derrota CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 31
Ejercicio 3.5 – Formato condicional Esta opcion del menu Formato, hace que el analisis de datos sea sencillo, pues basta indicar una condicion y el formato deseado del texto en caso de que se cumpla. Aquí estamos aplicando el formato a las siguientes condiciones: 1. Si la calificacion esta entre 1 y 6.9 el texto es cursiva 2. Si la calificacion esta entre 7.0 y 8.5 el fondo es rojo 3. Si la calificacion esta entre 8.6 y 10 el fondo es verde. NOTA: El formato condicional aparecio a partir de Office 2003. NOTA 2: Puedes revisar tu Guia de Estudio (pag. 80-82) para mas informacion. PRACTICA 14 – Formato condicional Elabora un listado con todos los integrantes de tu grupo, elige un listado de materias que hayan cursado con sus respectivas calificaciones, y aplica un formato condicional como el anterior. Ejercicio 3.5 Tablas Dinamicas Los informes de tabla dinamica permiten presentar desde distintos puntos de vista una misma informacion, usando para ello funciones como la suma o el promedio. Tenemos la siguiente tabla de datos: 1. ¿Cuál es el total que ha vendido cada vendedor? Página 32 2. ¿Cuál es el total de la venta por pais? CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO
3. ¿Quiénes son los cinco mejores vendedores? 4. ¿Cuáles son las ventas por mes? Mueve los datos para responder las preguntas anteriores. Revisa tu Guia de Estudio paginas 83 a la 87 para ver los pasos. CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 33
PRACTICA 15 – Tablas Dinamicas Aplicando el tema anterior, crea una tabla dinamica para saber cuantos alumnos tenemos por lugar de nacimiento y por turno. CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 34
Ejercicio 3.7 – Graficos Dinamicos Es la misma idea de las tablas dinamicas pero con un grafico. Se recomienda que para que los graficos sean faciles de entender, la tabla dinamica no debe tener mas de dos campos de filas y dos de columnas. Realiza una grafica dinamica del listado de alumnos de la practica anterior. Debe quedar asi: 1. Ilumina el rango de datos 2. Ve a la pestaña INSERTAR, ubica el icono de TABLA DINAMICA, das un click en la flecha inferior derecha, para aparecer GRAFICO DINAMICO. 3. Aplicas las opciones según la ventana de dialogo. 4. Arrastras el campo LUGAR DE NACIMIENTO como serie eje (o la primer columna de la tabla dinamica). 5. Arrasta el campo SEXO en la segunda columna y veremos nuestro grafico. CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 35
PRACTICA 16 – Grafico Dinamico Elabora un grafico dinamico con los siguientes datos. Ejercicio 3.8 – Añadir imágenes a una grafica. Usaremos la opcion efectos de relleno dentro de las graficas. El procedimiento es el mismo de crear una grafica, la diferencia se encuentra en la pestaña TRAMAS en el boton efectos de relleno CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 36
Debe quedar mas o menos asi: PRACTICA 17 – Añadir imagen a grafica Con la siguiente informacion elabora una grafica como la que se muestra: Y el resultado seria como: Página 37 CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO
PRACTICA 18 – Buscar objetivos Buscar objetivo. Con esta herramienta Excel ajusta automáticamente el valor de una casilla para obtener un resultado determinado en otra. Evidentemente, la casilla donde quiera obtener el resultado ha de depender directamente o indirectamente de la casilla a la cual se le ajusta el valor. La casilla que cambia ha de contener obligatoriamente un número, no se puede utilizar una casilla con fórmula. Ejemplo. Copie la tabla siguiente. Total Compra, Total Venta y Beneficios se han de calcular. CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 38
Calcular que cantidad se necesitaría para obtener unos beneficios de 55.000 pesetas. 1. Active opción Herramientas/ Buscar Objetivo. 2. En el cuadro de diálogo que aparece, en caja de texto Definir la celda: ponga la casilla en la que quiera un determinado resultado, en este caso la $B$6, Beneficios. En este apartado, sólo se pueden utilizar casillas que contengan fórmula. 3. En la siguiente caja de texto, Con el valor: ponga el valor que quiera obtener, 55.000. 4. Cambiando celda: esta caja de texto sirve para definir que casilla se puede ajustar para conseguir el resultado en la casilla indicada en Definir la celda. Recuerde que aquí sólo puede indicar casillas con valores numéricos y que intervengan directa o indirectamente en el resultado final. Ponga la casilla donde hay la cantidad D2. 5. Pulse sobre Aceptar. Excel ajusta el valor de la casilla D2 para que en la casilla B6 se obtenga 55.000. Calcule que precio de venta, tendría que haber para conseguir un beneficio de 120.000 pts. Calcule a que precio se tendría que comprar, para obtener unos beneficios de 90.000 pts. • Para hojas con gran cantidad de fórmulas, puede ser de gran ayuda utilizar las herramientas de Auditoría. De esta manera verá rápidamente de que casillas depende la casilla en la que quiere obtener el resultado. Practica 18.1 - Ejercicio. Recupere el archivo OBJETIVO1.XLS Cálculos. Total Ventas. Ventas * Precio Venta. Total Compras * Precio Compra. Compras. Total Salarios. Trabajadores * Salario. Beneficios. Total Ventas - (Total Compras + Total Salarios). Utilice la opción Buscar Objetivos para calcular unos beneficios de 450.000, cambiando: . Salario CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 39
. Cantidad de Venta . Precio Venta Recuerde volver a poner el valor original en la casilla variable antes de hacer el siguiente cálculo. Por ejemplo, antes de definir como variable la casilla Cantidad de venta, vuelva a poner el valor 130.000 en la casilla Salario. Ventas Compras Precio Precio Compra Trabajadores Salario Venta 25000 7900 220 150 30 130000 Total Ventas 5500000 Total Compras 1185000 Total Salario 3900000 Beneficios 415000 Solver Solver, es parecido a Buscar Objetivo, se utiliza también para obtener un determinado resultado en una casilla. Esta herramienta, permite establecer más de una casilla ajustable. También permite establecer restricciones, con esto, se indica a Solver que cuando haga los ajustes en las casillas variables, se ha de limitar a las condiciones establecidas en cada restricción. De la misma manera que en Buscar Objetivo, las casillas variables han de contener valores numéricos y han de intervenir directa o indirectamente en la fórmula de la casilla donde se quiera obtener el resultado final. Para utilizar este método, siga los pasos siguientes: 1. Active opción Herramientas/Solver, se desplegará este cuadro: CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 40
Celda objetivo: Casilla donde se quiere el resultado. Máximo: Que obtenga en Celda Objetivo el valor máximo posible. Mínimo: Que obtenga en Celda Objetivo el valor mínimo posible. Igual a: Valor que se desea obtener. Cambiando las celdas: Casillas que pueden variar su contenido para obtener el valor buscado en Celda Objetivo, si hay más de una casilla, se separan con un punto y coma. También se puede definir un rango con la notación Casilla Inicial:Casilla final. Estimar: Si se activa esta opción, Excel utilizará todas las casillas sin fórmula que impliquen el resultado de Celda Objetivo. Sujetas a las restricciones: En este apartado, se agregan las condiciones que ha de respetar Solver a la hora de hacer los ajustes en las casillas variables. 2. Después de llenar los apartados anteriores, pulse el botón Resolver. 3. Aparecerá otro cuadro que muestra los resultados calculados por Solver, pulse sobre Aceptar si quiere conservar este resultado, Cancelar para que vuelvan los valores originales. • No siempre será posible encontrar una solución. Ejemplo: Copie o recupere el ejercicio OBJETIVO1.XLS. Se tiene que conseguir unos beneficios de 550.000 pts., variando las siguientes casillas: . Precio Compra . Trabajadores . Salario Y poniendo las restricciones siguientes: Precio Compra, >=120 y <=160 Trabajadores, >=28 y <=35 Salario, >=120.000 y <=130.000 1. Active opción Herramientas/Solver. 2.En apartado Celda Objetivo, $B$7, casilla Beneficios. 3. En apartado Valor de la Celda Objetivo, haga clic en botón de radio Valores de: ponga la cantidad de, 550.000. CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 41
4. En Cambiando Celdas: ponga las casillas $D$2;$E$2;$F$2. 5. En Sujetas a las Restricciones: pulsar Agregar, 5.1. En Referencia Celda: ponga la primera casilla variante,$D$2. Puede hacer clic sobre esta casilla. 5.2. Escoja el signo <=. 5.3 A Restricción: ponga el valor 160 y Botón Agregar. Haga lo mismo para poner el valor mayor o igual que 120. 6. Repita los apartados del paso nº 5 para poner el resto de restricciones. 7. Pulse sobre Aceptar. 8. Pulse el botón Resolver, y en el cuadro que aparece pulse sobre Aceptar si quiere guardar los resultados y Cancelar si quiere restaurar los valores originales. Siguiendo los pasos anteriores, obtenga el valor máximo. Varíe todas las casillas implicadas en resultado de la Celda Objetivo, botón Estimar. Indique las siguientes restricciones: . Precio Venta >=200 y <=300. . Precio Compra >= 120 y <= 160. . Trabajadores >= 28 y <= 30 . Salario >= 120.000 y <= 130.000 . Ventas <=30.000 . Compras >=7.000 Practica 18.2 - Ejercicio Recupere archivo OBJETIVO2.XLS El ejercicio trata de ver las semanas que se tardará en pagar un equipo de música teniendo en cuenta los gastos y el ahorro semanal. CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 42
Cálculos. Total Refrescos Refrescos * Precio Refresco. Total No No Refresco * Precio No Refrescos Refrescos Total Comida Pizzas, Hamburguesas * Precio Comida. Gastos Total Refrescos + Total No Refrescos + Total Comida Ahorros Asignación Semanal - Gastos Total Semanas Precio Equipo Hi-Fi / Gastos. Los cálculos anteriores muestran que para pagar el equipo Hi-Fi con lo que se ahorra actualmente, se tardarán 500 semanas. Utilice el solver para obtener un resultado final entre 40 y 52 semanas. Las casillas variables serán las correspondientes al número de Refrescos, No Refrescos y Pizzas Hamburguesas. Las restricciones son: Refrescos, >=3 No Refrescos, >=2 Pizzas, Hamburguesas, >=2 Total Semanas , >=45 y <=52 Solución antes de aplicar Solver. Asignación Semanal Totales 1500 6500 Total Refrescos 1950 Total No 2800 Cantidades Consumidas 5 Refrescos 6250 3 Total Comida Refrescos 4 Gastos 250 No Refrescos Ahorro Pizzas, 125000 Hamburguesa Precio Equipo 500 s Hi-Fi Semanas Precios Precio 300 CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 43
Refrescos 650 700 Precio No Refrescos Precio Comida Practica 18.3 - Ejercicio. Recupere archivo OBJETIVO3.XLS Cálculos. Total Salario Salario1 + Salario2 Plan Pensiones Total Salario * %Pensiones Vacaciones Total Salario * %Vacaciones Total Gastos Mes Suma de gastos Mensuales Total Gastos Año Seguro Coche + Seguro Piso Total Ingresos Total Salario * 12 Total Gastos (Total Gastos Mes * 12) + Total Gastos Año. Beneficios Total Ingresos - Total Gastos Utilice el solver, para encontrar el beneficio máximo estableciendo las restricciones siguientes. . Los gastos de alimentación serán superiores a 20.000. . Los gastos de luz y teléfono serán superiores a 18.000 . El plan de pensiones será una cantidad entre 30.000 y 35.000 . La cantidad destinada mensualmente a vacaciones tiene que ser más grande o igual a 25.000 . El porcentaje dedicado al plan de pensiones tiene que ser superior o igual al 10% de ingresos. . Los beneficios serán menores o iguales a 280.000. Se pueden cambiar los valores de las casillas siguientes. . Alimentación. . Luz y teléfono. . % Plan de pensiones. . % Vacaciones. CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 44
Solución antes de aplicar Solver. Ingresos 140000 % Plan 15% Salario 1 Pensiones 10% % Vacaciones Salario 2 95000 Total Salarios 235000 Gastos Gastos Anuales Mensuales 60000 Seguro Coche 80000 Hipoteca 45000 Coche 25000 Seguro Piso 53000 Alimentación 23000 Luz, Teléfono 35250 Total Gastos Año 133000 Plan de pensiones 23500 Vacaciones 211750 Total Gastos Mes Total Ingresos 2820000 Total Gastos 2674000 Beneficios 146000 Practica 18.4 - Ejercicio. Recupere archivo OBJETIVO4.XLS Cálculos. Precio Hora Si Horas Diarias > 8 se pagará a 825 sino se pagará a 800. Kg. Recogidos Persona/ Si Número Personas > 20 cada una recoge 20 Hora. Kg. Sino recoge 30 Kg. Kg. Recogidos Día Personal * Horas Diarias * Kg. Recogidos Persona/ Hora. Días Recogida. 1ª Pasada Kg. Previstos 1ª Pasada/ Total Kg. Día. 2ª Pasada Kg. Previstos 2ª Pasada/ Total Kg. Día Total 1ª Pasada + 2ª Pasada CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 45
Kg. Perdidos Día. Sí Días Recogida > 7 Pérdida del 0,8% de los 1ª Pasada Kg. Recogidos por día. Sino un 0,5% de los Kg. Recogidos por Día. 2ª Pasada Si Días Recogida > 4 Pérdida del 3% de los Total Kg. Perdidos. Kg. Recogidos día Sino 5% de los Kg. 1ª Pasada. Recogidos Día. 2ª Pasada. Kg. Perdidos día 1ª Pasada * Días Recogida 1ª Pasada. Kg. Perdidos día 2ª Pasada * Días Recogida 2ª Pasada. Ingresos (Kg. Recogidos 1ª Pasada - Total Kg. Perdidos 1ª Pasada 1ª Pasada) * Precio Kg. 1ª Pasada. 2ª Pasada (Kg. Recogidos 2ª Pasada - Total Kg. Perdidos 2ª Pasada) * Precio Kg. 2ª Pasada. Total Total Horas Ingresos 1ª Pasada + Ingresos 2ª Pasada. Total Gastos Personal * Total Días Recogida * Horas Beneficios diarias Total Horas * Precio Hora Total Ingresos - Total Gastos. Utilice la función =SI en precio hora para determinar si se paga a 800 o a 825. Utilice la función =SI en Kg. Recogidos Persona/Hora. Utilice la función =SI en Kg. Perdidos día. Utilice el solver para calcular el valor máximo en beneficios. Las restricciones son las siguientes. . Personal contratado tiene que estar entre 15 y 23 persones. . El total de días a recoger tiene que estar entre 15 y 20. . Las horas diarias pueden ser 8 o 9. Las casillas variables son. Página 46 . Personal contratado. CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO
. Horas diarias. Campaña Frutera Kg. Previstos 55000 Precio Kg. 65 1ª Pasada 23000 1ª Pasada 35 2ª Pasada 2ª Pasada Personal Precio Hora Horas Diarias 800 17 8 Kg. Recogidos Kg. Recogidos Día Persona/Hora 4080 30 Kg. Perdidos Días de recogida Día Total Kg. Perdidos 1ª Pasada 2ª Pasada 13,4803922 32,64 440 Total Días 5,6372549 122,4 690 19,1176471 Ingresos Total Horas Total Gastos 1ª Pasada 2ª Pasada 3546400 2600 2080000 Total 780850 4327250 Beneficios 2247250 CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 47
PRACTICA 19 – Condiciones logicas vs Funcion Si() En una empresa, tenemos un vendedor John Smith que promociona 3 productos. Al mes, veremos si ademas de su sueldo base merece una comision si las ventas de dichos productos superan los 50,000. Esto es, se dara un bono de 1000 por cada venta superada de 50,000. Podria obtener hasta 3 bonos! Al empleado se le paga el 10% del total de lo vendido mas un bono si hubiera. Aquí debes aplicar la condicion de bono o la funcion SI() para saber si lo obtiene o no. PRACTICA 20 – Condicion Logica Y En el mismo caso que la practica anterior, ahora para que el empleado obtenga su bono, ademas de superar las ventas por producto de 50,000 debera haber traido 20 nuevos clientes. Solo asi obtendra su bono. CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 48
PRACTICA 21 – Funcion BuscarV Por otro lado, tenemos a un empleado al que se le hara un descuento para impuestos basado en su sueldo. A mayor sueldo, mayor porcentaje de descuento. Descubre cual seria dicho porcentaje y cuanto recibiria al final CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 49
PRACTICA 22 – Base de Datos, BuscarV, Ordenacion, Tabla dinamica 1- Copiar los siguientes datos en la hoja de calculo : 2- Calcula las columna del Precio Venta, Importe Venta y Importe Comisión utilizando las funciones que has aprendido durante el curso. (BuscarV) 3- Poner todos los datos numéricos de la hoja con los formatos que creáis conveniente. 4- Ordena la hoja por la columna del Vendedor. 5- Crea una tabla dinámica, en una hoja nueva, donde se resuma la suma de los campos Ventas, Importe Ventas y Importe Comisión, agrupando en las columnas los campos Zona y Grupo Producto, y en las filas, el campo Año. 6- Crea otra tabla dinámica como la anterior, en una nueva, pero resumiendo los promedios. 7- Crea una tabla dinámica, en la misma hoja, donde quede resumido el MÁXIMO de Ventas, agrupando en las filas el campo Vendedor y en las columnas, el campo Producto. CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Página 50
Search
