Cuaderno de ejercicios y practicas de Excel avanzado, Ejercicios de Competencias en MS Microsoft Excel

¡Descarga Cuaderno de ejercicios y practicas de Excel avanzado y más Ejercicios en PDF de Competencias en MS Microsoft Excel solo en Docsity!

CUADERNO DE

EJERCICIOS Y PRACTICAS

EXCEL AVANZANDO

Contenido

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 Delegacion

Entidad Federativa Habitantes Porcentaje Tijuana Baja California 1410700 1. Juarez Chihuahua 1313338 1. Izcatepec DF 1820888 1. Gustavo A Madero DF 193161 1. Ecatepec de Morelos Edo Mex 1688258 1. Nezahualcoyotl Edo Mex 1140528 1. Leon Guanajuato 1278087 1. Guadalajara Jalisco 1600940 1. Zapopan Jalisco 155790 1. Puebla Puebla 1485941 1.

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.

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

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.

135426 Arthur Conan Doyle Las aventuras de Sherlock Holmes 345.

124578 Benito Perez Galdos Marianela 234.

235678 Charles Dickens Cuento de Navidad 345.

987654 Charles Dickens Historia de dos ciudades 234.

986532 Charles Dickens Oliver Twist 432.

876555 Moliere Tartufo

• Ejercicio 2.1.1 – Esquemas automaticos.....................................................................................................
• Ejercicio 2.1.2 – Esquemas manuales
• Ejercicio 2.2 – Subtotales
• Ejercicio 2.3 – Funciones de Base de Datos
• Ejercicio 2.4 – BuscarV
• Ejercicio 2.5 – Escenarios
• PRACTICA 1 – Esquematizacion.
• PRACTICA 2 – Esquematizacion
• PRACTICA 3 – Subtotales
• PRACTICA 4 – Funciones Estadisticas y Funciones Base de Datos - Algunas Funciones Estadísticas...................................................................................................... - Algunas Funciones de bases de datos.
  • Practica 4.1 - Ejercicio.
  • Practica 4.2 - Ejercicio.
• PRACTICA 5 – Funcion BuscarV
• PRACTICA 6 – Funcion BuscarH.................................................................................................................
• PRACTICA 7 – Funcion BuscarH o BuscarV................................................................................................
• PRACTICA 8 – Escenarios: Vacaciones
• PRACTICA 9 – Escenarios: Divisas de productos
• PRACTICA 10 – Escenarios: Analisis de Carolina
• EVALUACION BLOQUE
• Ejercicio 3.1 – Buscar Objetivo..................................................................................................................
• Ejercicio 3.2 – Tabla de Datos
• PRACTICA 11 – Buscar Objetivo
• Ejercicio 3.3 Funcion Si
• Ejercicio 3.4 – Funcion Si anidada
• PRACTICA 12 – Evaluacion
• Ejercicio 3.4.1 – Sumar Si
• PRACTICA 13 – Funcion condicional.
• Ejercicio 3.5 – Formato condicional
• PRACTICA 14 – Formato condicional
• Ejercicio 3.5 Tablas Dinamicas
• PRACTICA 15 – Tablas Dinamicas
• Ejercicio 3.7 – Graficos Dinamicos
• PRACTICA 16 – Grafico Dinamico
• Ejercicio 3.8 – Añadir imágenes a una grafica.
• PRACTICA 17 – Añadir imagen a grafica
• PRACTICA 18 – Buscar objetivos
  • Practica 18.1 - Ejercicio.
  • Solver
  • Practica 18.2 - Ejercicio
  • Practica 18.3 - Ejercicio.
  • Practica 18.4 - Ejercicio.
• PRACTICA 19 – Condiciones logicas vs Funcion Si()
• PRACTICA 20 – Condicion Logica Y............................................................................................................
• PRACTICA 21 – Funcion BuscarV
• PRACTICA 22 – Base de Datos, BuscarV, Ordenacion, Tabla dinamica
• PRACTICA 23 – Administracion de Escenarios
  • Practica 23.1 - Ejercicio.
  • Practica 23.2 - Ejercicio.
• PRACTICA 24 – Funciones Condicionales y de Busqueda
  • Practica 24.1 - Ejercicio.
  • Practica 24.2 - Ejercicio.
  • Practica 24.3 - Ejercicio.
  • Practica 24.4 - Ejercicio.
  • Practica 24.5 - Ejercicio.
  • Practica 24.6 - Ejercicio
• 876521 Edgar Allan Poe El gato negro 34.
• 124567 Edgar Allan Poe Los crimenes de la Rue Morge 123.
• 234590 Fedor Dostoiewski Crimen y castigo 234.
• 102938 Fernando de Rojas La celestina 345.
• 457812 Franz Kafka La metamorfosis 456.
• 567890 Gustave Flaubert Madame Bovary 321.
• 124576 Jack Londo Colmillo Blanco 432.
• 113366 JM Barrie Peter Pan 321.
• 124577 Johann Wolfang Goethe Fausto 21.
• 987066 Julio Verne De la tierra a la Luna 21.
• 667788 Julio Verne La vuelta al mundo en 80 dias 321.
• 335578 Leon Tolstoi Ana Karenina 231.
• 123345 Leon Tolstoi Guerra y Paz 231.
• 345677 Lewis Carroll Alicia en el pais de las maravillas 234.
• 345789 Lope de Vega Fuenteovejuna 234.
• 987234 Mark Twain El principe y el mendigo 343.
• 567432 Mark Twain Las aventuras de Huckleberry Finn 34.
• 789654 Mark Twain Las aventuras de Tom Sawyer 54.
• 786655 Mary Shelley Frankenstein 223.
• 443366 Miguel de Cervantes Saavedra Don Quijote de la Mancha 123.
• 347890 Miguel de Unamuno La tia Tula 123.
• 235473 Miguel de Unamuno Niebla 189.
• 123654 Moliere Las preciosas ridiculas 178.

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 3 2 Traslado 3000 3280 Hospedaje 2000 Alimentos 1000 Eventos 3000 3000 Suovenirs 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:C
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:

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.

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:

Usando la opcion de TRANSPONER, hacemos que los alumnos queden como columnas y los puntos como filas para poder hacer la esquematizacion correctamente.

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.

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

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.

Practica 4.2 - Ejercicio.

Sobre la misma tabla, recupere BASE3.XLS.

Inserte la siguiente tabla de criterios:

Lenguaje Matemáticas Historia <=5 <=5 <= Lenguaje Matemáticas Historia

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 os

Aprobad os Lenguaje 7 15 Matemática s

Historia 7 15

Inserte la tabla de criterios:

Clase Clase Clase A B C

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

Media Por Clase y Asignatura Clase A Clase B Clase C Lenguaje 5,9 7,4 5, Matemáticas 4,8 5,3 6, Historia 6,8 6,7 5,

Nota Máxima Por Clase y Asignatura

Clase A Clase B Clase C Lenguaje 8 9 7 Matemática s

Historia 9 8 8

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. Debera mostrar el porcentaje y el nombre del vendedor como se ve en la imagen.