Guia de Practicas Excel

Centro de Capacitación en Competencias Tecnológicas

GUÌA DE PRACTICAS CURSO DE EXCEL AVANZADO

1.1 Ambi ente Excel INICIO Al acceder a Microsoft Excel 2013 nos muestra esta pantalla en la que podemos localizar localizar dos partes, la parte izquierda de color verde dónde nos va a mostrar los archivos utilizados recientemente, en este caso nos indica que todavía no hemos creado ningún libro, es decir, no hemos realizado ningún ejercicio, de manera que la lista parece vacía. Podemos acceder a la opción “abrir otros otros libros” para acceder a ejercicios realizados y almacenado almacenado

en nuestro equipo, de esta forma podemos colocarlos en pantalla y continuar con su edición. En la parte derecha, de color blanco, tenemos diferentes opciones para ejecutar de forma automática, ya sea un libro en blanco, para crear un ejercicio nuevo, o visualizar un pequeño tutorial de funcionamiento aplicación. Después disponemos de diferentes plantillas predefinidas, como por ejemplo para crear un presupuesto, un calendario, tareas escolares, etc.,


Accesos Rápido Pestañas

Nombre de archivo y aplicación

Iconos De La Pestaña Activa

a. Formato y Relleno de Series. 1.2

Ejercic io 1

1.3

Ejercicio2

Barra de título

Iconos De Consultas


1.4

Tarea Tarea 1: Trabajar con series de datos

Creamos una lista personalizada con los siguientes valores: valores: L, M, X, J y V. La tabla debe comenzar solo con los siguientes datos:

Realizamos las siguientes series de datos. Comenzamos introduciendo en la celda C3 el día 01/09/2010. La serie debe terminar en el día 30/09/2010. En esta serie no deben aparecer sábados ni domingos, sólo días laborables. Introducimos en la celda B3 el valor X (Miércoles).En cada una de las celdas de la columna B debe aparecer el día de la semana correspondiente a la celda de la columna C contigua. Guardamos el libro con el nombre SERIES DE DATOS.


1.5

Tarea Tarea 2: Apli car for mato num érico

Elaboramos la siguiente hoja de cálculo. Tecleamos los datos de la columna B y realizamos una copia en la columna

C.

Posteriormente aplicamos formato NUMÉRICO a los datos de la columna C hasta que se adecuen al modelo propuesto. Guardamos el libro con el nombre FORMATO_NUMÉRICO . 1.6

Tarea Tarea 3: Apli car for mato fuente, tramas y bor des

Elaboramos la siguiente hoja de Excel aplicando las opciones de FORMATO necesarias hasta que sea exactamente como aparece en la imagen.

Guardamos el libro con el nombre de

Ejercicio 3.


1.7

Tarea 4: Formato con dici onal

Introducimos la siguiente tabla para posteriormente aplicar formato condicional según los valores de las celdas.

Calculamos la NOTA MEDIA, utilizando una fórmula, sabiendo que la NOTA MEDIA será la suma de Prueba Práctica + Prueba Teórica dividido entre 2. Aplicar distintos FORMATO CONDICIONAL a cada columna para que el resultado sea como el que refleja la siguiente imagen:


1.8

Tarea 5: Formato con dici onal

Cree este tablero con formato condicional, utilice dos nuevas reglas, para ello utilice una fórmula que determine las celdas para aplicar el formato. Observe que la suma de la fila y columna con café oscuro siempre es par. Pistas: fila() es una función que devuelve el número de fila, columna() es una función que devuelve el número de columna, Residuo(dividendo, divisor) es una función que devuelve el residuo


2 Clase 2 2.1

Ejercicio 1.

Solución


2.2 Ejercicio 2

Solución


2.3 Ejercicio 3

Solucion


2.4 Ejercicio 4

Solución


2.5

Tarea 1

2.6

Tarea 2

2.7

Tarea 3


2.8

Tarea 4

2.9

Tarea 5


2.10 Tarea 6

2.11 Tarea 7


3 Clase 3 3.1 Ejercicio 1

Función BuscarH, utilice esta función para resolver el ejercicio.

3.2 Ejercicio 2

Función BuscarV, utilice esta función para resolver el ejercicio.


3.3 Ejercicio 3

3.4. Ejercicio 4 Del siguiente ejercicio que se muestra a continuación responda a cada Ejercicio en Hojas Distintas Pregunta 1: Contar las veces que Ascensores J & C ha hecho Aportaciones comprendidas entre 150 y 300, y haya algún dato en la columna Devolución. Pregunta 2: Contar las veces que Ascensores J & C ha hecho Aportaciones mayores que 300 o menores que 200 y haya algún dato en la columna Devolución. Pregunta 3: Contar las veces que cualquier empresa distinta de Ascensores J & C haya hecho Aportaciones mayores que 200 y haya algún dato en la columna Devolución. Pregunta 4: Contar las veces que cualquier empresa, excluidas Ascensores J & C y Decoraciones Eder, haya hecho Aportaciones mayores que 200 y haya algún dato en la columna Devolución. Pregunta 5: Contar celdas no vacías de Devolución y: 1 - Ascensores J & C tenga Aportación entre 150 y 300, o 2 - Que Decoraciones Eder tenga Rendimiento=6, o 3 - Que Decoraciones Eder tenga Beneficio >3500, o 4 - Pascual Reina tenga Aportación >84.


3.4

Ejercic io 5: Operaciones matriciales

Suma y resta Para poder sumar o restar matrices, éstas deben tener el mismo orden, es decir, deben tener el mismo número de filas y de columnas. Para sumar o restar se suman o restan los términos que ocupan el mismo lugar en las matrices. 3.4.1

Ejemplos ilustrativos


Los cálculos en Excel se muestran a continuación: a) Escribir las matrices A y B. Seleccionar las casillas en donde se calculará la respuesta, que para este ejemplo es E4:F5

b) Digitar el =, seleccionar las celdas de la matriz A (B1:D2), digitar el +, y seleccionar las celdas de la matriz B (G1:I2), es decir, digite la fórmula =B1:D2+G1:I2

c) Presione CTRL+SHIFT+ENTER al mismo tiempo


d) Los demás cálculos se muestran en la siguiente figura:

3.4.2

Multipli cación de un escalar por u na matriz



Los cálculos en Excel se muestran a continuación: a) Escribir la matriz y el escalar. Seleccionar las casillas donde se calculará la multiplicación

b) Escribir la fórmula B4*B1:C2, que representa la multiplicación de 2 (B4) por la matriz A (B1:C2)

c) Presione CTRL+SHIFT+ENTER al mismo tiempo

d) Los demás cálculos se muestran en la siguiente figura:


Multiplicación entre matrices Para poder multiplicar dos matrices, el número de columnas de la primera matriz deber ser igual al número de filas de la segunda matriz. La matriz resultado del producto quedará con igual número de filas de la primera matriz y con igual número de columnas de la segunda matriz.

Propiedades de la multiplicación de matrices:



Los cálculos en Excel se muestran a continuación: a) Escribir las matrices. Seleccionar las celdas donde se calculará la multiplicación

b) Insertar función. En la ventana de Insertar función, En seleccionar una categoría, escoger Matemáticas y trigonométricas. En Seleccionar una función, escoger MMULT.


c) Clic en Aceptar en la ventana de Insertar función para que aparezca la ventana Argumentos de función. En la ventana Argumentos de función, en la casilla Matriz 1, seleccionar las celdas de la matriz A (B1:D2), y en la casilla Matriz 2, seleccionar las celdas de la matriz B (G1:H3).

d) Presione CTRL+SHIFT+ENTER al mismo tiempo

Los cálculos en Excel se muestran en la siguiente figura:

3) Los cálculos en Excel se muestran en la siguiente figura:


3.4.4

Potencia de matrices

La potencia es una multiplicación abreviada Ejemplo ilustrativo Dada la matriz

Solución: Los cálculos en Excel se muestran en la siguiente figura:

3.4.5

INVERSA

Calcula las funciones inversas de las matrices


3.5 Ejercicio 6


4

Clase 4

4.1 Ejercicio 1 Validación de Datos

Solución


4.2 Ejercicio 2 Buscar Objetivo

¿Qué descuento habrá que aplicar para que el precio final no supere los 3000 ? ¿Y qué pronto pago? Dímelo utilizando buscar objetivo

4.3 Ejercicio 3


4.4 Ejercicio 4

4.5 Ejercicio 5

Solucion


4.6

Ejercic io 6: Uso de Solver para análisi s de datos del siguiente problema

Una empresa fabrica dos modelos de fundas de sofá, A y B, que dejan unos beneficios de 40 y 20 $ respectivamente. Para cada funda del modelo A se precisan 4 horas de trabajo y 3 unidades de tela. Para fabricar una del modelo B se requieren 3 horas de trabajo y 5 unidades de tela. La empresa dispone de 48 horas de trabajo y 60 unidades de tela. Si a lo sumo pueden hacerse 9 fundas del modelo A. ¿Cuántas fundas de cada modelo han de fabricarse para obtener el máximo beneficio y cual sería este? PLANTEAMIENTO: Variables: A = Cantidad de fundas del tipo “A” a fabricar. B = Cantidad de fundas del tipo “B” a fabricar.

Función Objetivo: Z = 40A + 20B

(beneficio a maximizar)

Restricciones: Se recomienda elaborar una tabla donde se refleje toda la información disponible para visualizar mejor las restricciones del problema :

A

B

Disponibilidad

Horas de trabajo

4

3

48

Unidades de tela

3

5

60

Cantidad máxima a fabricar

9

Restricción 1: 4A + 3B ≤ 48 (horas de trabajo) Restricción 2: 3A + 5B ≤ 60 (unidades de tela) Restricción 3: A lo sumo pueden hacerse 9 fundas del modelo “A”. A ≤ 9 Tarea 7 Análisis de datos con Herramienta de análisis

Se deben fabricar 9 fundas del tipo “A” y 4 del tipo “B” generando un beneficio máximo de

440,00 $.


4.7 Tarea 7 : Uso de Herrami entas de datos para demostrar hipó tesis


4.8

Tarea 8

Para calcular el precio sin IVA se ha utilizar la tabla de tarifas. Las tarifas serán: SI la cantidad de Huéspedes = 1 Entonces cantidad de días * tarifa individual SI NO SI cantidad de huéspedes = 2 Entonces cantidad de días * tarifa doble SI NO cantidad de días * tarifa familiar El IVA será: Precio sin IVA x 13% El Precio Con IVA se obtendrá sumando las casillas anteriores La Cantidad A Descontar es el porcentaje correspondiente del precio sin IVA aplicando la tabla de descuentos. Para aplicar esta tabla tendrás que utilizar la función BUSCARV. El Precio Final será el precio con IVA menos la cantidad a descontar. Realiza un gráfico circular con el número de habitación y la cantidad de huéspedes. Con el número de habitación y el precio final realiza un gráfico de columnas. Con el número de habitación y el precio final realiza un gráfico de columnas. Luego crear validación para los datos que se debe introducir o modificar. Dejar Sin Bloquear solo aquellas celdas que se necesiten modificar datos.


5

Clase 5

5.1 Ejercicio 1 Consolidar datos

Datos a consolidar


5.2 Ejercicio 2

5.3 Ejercicio 3


1. Ordena la información de la siguiente manera: Hoja de VENTAS: 1 Nivel Producto 2 Nivel Ciudad 3 Nivel Vendedor Hoja de GRAVETAL: 1 Nivel Producto 2 Nivel Zona 3 Nivel Vendedor 1. En la hoja de CONCESIONARIO debes inmovilizar los rótulos. Ejercicio 4

Abrir la hoja de GRAVETAL, realizar los siguientes ejercicios utilizando filtros automáticos y personalizados: Cada punto debe quedar en una hoja nueva. 1. Cuáles fueron las ventas y comisiones la ZONA A, G y H 2. Cuáles fueron las ventas y comisiones del vendedor BEDOYA, BONILLA o PEREZ en la zona C o J 3. Cuáles fueron las ventas y comisiones del ACEITE GIRASOL del 29 de Febrero del 2004 4. Mostrar las ventas entre Bs. 500,000 y Bs.2,000,000 del ACEITE S&S, SOY SABOR y OLEOPALMA 5. Mostrar las comisiones inferiores a Bs.150,000 del vendedor VASQUEZ 6. Mostrar los productos de la ZONA H, del vendedor GUTIERREZ, ECHEVERRY y PINEDA, con ventas entre Bs.250,000 y Bs.750,000 con comisiones mayores a Bs.50,000 7. Mostrar las ventas del 11 de Junio del 2004 de las ZONAS G, J y D, del vendedor CANO, ARANGO o MARTINEZ 8. Mostrar las zonas en que se vendió la MARGARINA CAMPI LIGHT 9. Mostrar las ventas del vendedor CANO superiores a Bs.750,000


10. Mostrar las ventas del ACEITE SOY SABOR del vendedor FERNANDEZ, ARANGO, DIAZ y SÁNCHEZ inferiores a Bs. 1,000,000 y con comisión entre Bs.70,000 y Bs.140,000

5.4 Ejercicio 4

solución 1


solución 2

solución 3


6

Clase 6

6.1 Ejercicio 1

Solucion


6.2 Ejercicio 2


Solución

6.3 Ejercicio 3


Solución

6.4 Ejercicio 4

Guia de Practicas Excel

Recommend Documents