Cuaderno De Prácticas De Excel.docx 1n704a

Cuaderno de Prácticas Microsoft Excel

Práctica 1: Tabla Crear la siguiente tabla con las fórmulas necesarias para que al modificar la celda B1 de la hoja de cálculo calcule la tabla de multiplicar correspondiente al número introducido en dicha celda.

Práctica 2: Presupuesto Crear la siguiente hoja, con las formulas necesarias para que calcule el total del presupuesto.

Práctica 3: Gastos Familiares Crear la siguiente tabla con las fórmulas necesarias para que calcule los totales de cada mes y el total de trimestre. ♦ Fuente: Book Antigua ♦ Bordes y sombreados ♦ Tamaño de la fuente: 10 y 14

Práctica 4: Gestión de empresas Crear la siguiente tabla con las fórmulas necesarias para que calcule los totales de cada mes y el total de trimestre. ♦ Fuente: Copperplate Gothic Bold o Tahoma ♦ Bordes y sombreados

=D9 * E9

=F9*16%

=SUMA (F9:F13) =SUMA (G9:G13) =F16+G16

Práctica 5: Cotizaciones bursátiles Seguimiento de las cotizaciones bursátiles. Si el precio de un valor de la columna de Pérdidas y Ganancias se incrementa en más o igual a un 20%, dicho valor aparecerá en negrita sobre un fondo azul. Si el precio de un valor descendente a negativo, la cifra aparecerá en negrita sobre un fondo rojo. Calcular además el total de las columnas compra y última. ♦ Fuente: Copperplate Gothic Bold o Tahoma

Aplicación del formato condicional

Práctica 6: Ocupación hotelera Una empresa hotelera que posee 15 hoteles desea saber la rentabilidad de cada uno de ellos en el año anterior. Para ello es necesario realizar los siguientes pasos: 1. Hay que realizar libro, con los estudios trimestrales según se muestra en las imágenes. En las columnas diferencia, %Ocupación y %Diferencia las fórmulas que se indican, en la columna Rentabilidad, debe aparecer el mensaje .NO RENTABLE. Si el % de ocupación es inferior al 50%. 2. Calcular lo mismo para los dos trimestres posteriores suponiendo que el aumento de la ocupación real es de un 5% más para cada hotel con respecto al último trimestre.

=D4-E4

=E4/D4

=100% - G4

Práctica 7: Acciones

Práctica 8: Papelería

Práctica 9: Referencias absolutas

Práctica 10: Presupuesto El libro que vamos a crear generará un presupuesto: 1.- En la celda A2 teclee MODELO PARA GENERAR PRESUPUESTOS 2.- Teclee Descuento en la celda A4 3.- En la celda A5 teclee IVA Continúe introduciendo los valores que aparecen en la figura siguiente:

Las fórmulas que vamos a introducir son las siguientes: -

Importe = Cantidad* Precio

-

Descuento = Total * Descuento reflejado en (B4)

-

Total = Importe – Descuento (en la columna E)

A continuación introduciremos la fórmula que calcula el importe. 4.- Teclee en la celda D9 la siguiente fórmula: = B9 * D9 5.- Pulse la tecla Enter.

6.- Arrastre el controlador de relleno de la cela D9 hasta la celda D14 7.- Compruebe el resultado de la columna Importe fijándose en la columna de la izquierda de este punto.

8.- En la celda E9 teclee = D9*B4 9.- Pulse Enter 10.-Arrastre el controlador de relleno de la celda E9 hasta la celda E14 Como puede ver el resultado es erróneo. Al arrastrar el controlador de relleno, la fórmula asignada en la celda E9 ha incrementado en uno la referencia de la fila. Para la referencia de la columna D la operación de incremento de fila es correcta pero en la referencia de la columna B el incremento no se tiene que dar. Para estos casos tenemos que utilizar referencias absolutas o mixtas. 11.- Haga clic en la celda E9 y pulse la tecla de función F2 para editar la fórmula. 12.- Pulse dos veces en la tecla de función F4 para insertar un $ entre la columna y fila de la referencia B4 Con esta operación hemos bloqueado el incremento de la fila al arrastrar el controlador de relleno. 13.- Pulse la tecla Enter 14.- Arrastre el controlador de relleno de la celda E9 a la celda E14 15.- Compruebe el resultado 17.- Realice los demás cálculos de la columna TOTAL, SUMA TOTALES, IVA , Y TOTAL CON IVA. 18.- Calcule el PRECIO MAXIMO, PRECIO MÍNIMO Y PRECIO MEDIO

Si se ha realizado correctamente todos los pasos de este ejercicio el resultado ha de ser el siguiente:

20.- Ahora vamos a darle un formato más adecuado. Para ello cambia los fondos, los bordes, los tipos de letras. Las cantidades deben ir en pesos mexicanos y con separador de miles. Puede quedar más o menos así:

Práctica 11: Presupuesto La empresa XXX S.A se dedica a la distribución de bebidas, la empresa desea controlar a través de una hoja de cálculo. Las características del libro deben ser: 1. Una hoja PRESENTACIÓN con el enunciado del problema 2. Una hoja PROVEEDOR, con la relación de bebidas que distribuye. 3. Una hoja CUESTIONES con los resultados de las cuestiones que plantea en los apartados a y b. Se pide: a. El número de bebidas diferentes de cada proveedor. b. El número de bebidas que hemos recibido de cada tipo. c. Cálculo total del nº de bebidas compradas (es un 20% más de las que se han recibido), presupuestadas (es un 50% más de las que se han recibido).

Práctica 12: Buscar V La peluquería Cortilava, S.A. ha diseñado un libro de trabajo, llamado CORTILAVA, con objeto de presupuestar sus servicios a la clientela. En la hoja denominada Servicios se recogen los diversos servicios (permanente, lavar, cortar, etc.), sus precios bases así como los descuentos ofrecidos para algunos de los servicios. En la hoja denominada clientes se realiza el presupuesto para cada cliente. Se tecleará el nombre del cliente, introduciendo a continuación las iniciales de los servicios escogidos en cualquier orden. La hoja deberá estar diseñada de forma que al teclear la inicial del servicio aparezca automáticamente el resto de la información. Así mismo, en la fila 10 se deberá calcular el total de los servicios previamente escogidos.

Práctica 13: Información turística Información turística, es un servicio que permite a un obtener el precio de un viaje, seleccionando para ello los códigos de: destino, transporte, guía y elementos culturales a visitar.

Práctica 14: Las funciones: Promedio, Máxima, Mínima, Moda, Contar, Contar. Si y Mediana Vamos a realizar un nuevo ejercicio que nos servirá para estudiar 5 nuevas funciones de Excel. Elaboraremos una supuesta tabla con los alumnos de una escuela. Los datos que tendremos son las notas de los tres trimestres. A partir de ahí, realizaremos una serie de cálculos utilizando las funciones que vamos a estudiar. Vamos primero a ver sus sintaxis, y a continuación su aplicación en el ejemplo: =PROMEDIO(Número1;Número2;......) Función que nos devolverá la media aritmética de los números o el rango encerrado entre paréntesis =MAX(Números) =MIN(Números) Estas funciones devuelven los valores máximo y mínimo respectivamente de una lista de números. =MODA(Números) Valor que más se repite en un rango

=CONTAR(Rango) Cuenta las veces que aparece un elemento númerico en una lista. =CONTARA(Rango) Cuenta las veces que aparece un elemento de texto en una lista =CONTAR.SI(Rango) Cuenta las celdas no vacías de un rango

=MEDIANA(Números) Número que se encuentra en medio de un conjunto de números, es decir, la mitad de los números es mayor que la mediana y la otra mitad es menor.

Ejemplos: =PROMEDIO(12;12;13) devolverá 12,33333 =PROMEDIO(A1:D13) devolverá el promedio del rango A1:D13

A continuación observa la siguiente tabla. Las celdas en color blanco contendrán las fórmulas.

Calcular lo siguiente: En la columna E el promedio final. En la columna F la evaluación en forma de texto. Haremos servir la función =SI para lo siguiente:  Si el promedio es de 0 a 69, aparecerá la palabra Insuficiente  Si el promedio es de 70 a 79, aparecerá la palabra Aprobado  Si el promedio es de 80 a 95, aparecerá la palabra Notable  Si el promedio es de 96 a 100, aparecerá la palabra Excelente En las celdas inferiores calcularemos la Mediana, Notas máxima y mínima, y moda Finalmente, colocaremos unas celdas que nos informarán de: El número de alumnos que hay  El número de insuficientes, aprobados, notables y excelentes que hay  Qué porcentaje representa cada uno de los anteriores

Práctica 15: Funciones estadísticas básicas 1. Ingresar a Excel y capturar en la Hoja 1 el formato como se muestra en la (Imagen X)

Imagen X

2. Capturar la siguiente imagen en la misma hoja, solo que debajo de la tabla anterior capturar la (imagen XI), y calcular los datos del recuadro marcado en la misma.

Imagen XI

Práctica 16: Factura En este ejemplo vamos a crear dos hojas y utilizarlas conjuntamente.  Artículos: esta hoja contendrá un listado de artículos  Factura: modelo de factura con fórmulas que buscarán artículos en la anterior hoja La primera hoja tiene la siguiente lista de artículos:

La segunda hoja tendrá el siguiente aspecto: Escribiremos un código de artículo de la hoja anterior y nos aparecerá su descripción automáticamente en la celda de la derecha. También nos aparecerá el precio unitario. Luego introduciremos la cantidad deseada y Excel nos calculará el total de cada fila, y el total de toda la columna. En la celda E12 puede aparecernos un descuento de la factura sólo si en la celda B13 escribimos la palabra

CONTADO. En ese caso, la fórmula de la celda E12 efectuará un 5% de descuento del total de la celda E11. FORMULAS UTILIZADAS

Práctica 17: Personal con listas Personal, es una hoja de cálculo que nos permite obtener información acerca de un empleado. Para ello deberemos seleccionar el código del empleado y automáticamente nos dará su información (apellidos, nombre, departamento, categoría y sueldo). El sueldo de cada empleado se obtendrá de la tabla que se adjunta, de forma automática, mediante una lista de validación que contendrá los datos de categoría. Crear los comentarios descritos en la práctica.

Práctica 18: Un gráfico sencillo Con los datos que figuran en la hoja de cálculo, crear los siguientes gráficos: • • • •

Columnas Circular Cilíndrico Áreas

.

Práctica 19: Protección de hojas 1. Ingresar a Excel y crear el siguiente formato como se muestra la imagen (Imagen XVII).

Imagen XVII 2. Calcular los rangos de celdas siguientes:

3. Proteger la hoja, permitiendo modificar los siguientes rangos de celdas:

Práctica 20: autofiltros y macros 1. Ingresar a Excel y capturar la siguiente tabla como se muestra en la (Imagen XXIII).

Imagen XXIII 2. Calcular el promedio de los siguientes rangos de celdas:

3. Crear un auto filtro 4. Filtrar a los alumnos con promedio superior a 7 y guarda el libro.

1. Con la tabla de la practica 16, crear una macro que con un meta comando me de automáticamente un auto filtro.

2. Con la misma tabla crear una macro que con un meta comando me de el formato a la tabla como se muestra a continuación:

Imagen XXIV

Práctica 21: Funciones de fecha y hora 1. Ingresar a Excel y capturar la siguiente tabla como se muestra en la (Imagen XXV). Sin capturar el rango de celdas “E3:E4”.

Imagen XXV 2. En la hoja 2 Crear la siguiente tabla como se muestra en la (Imagen XXVI). 3. Utilizando las funciones de Texto calcular los rangos de celdas siguientes:

Imagen XXVI

Práctica 22: Funciones de búsqueda 1. Ingresar a Excel y capturar la siguiente tabla como se muestra en la (Imagen XXVII). Colocarle a la hoja el nombre de “Películas”.

Imagen XXVII 2. En la hoja 2 capturar la siguiente tabla como se muestra en la (Imagen XXVIII), y nombrarle Socios a la hoja.

Imagen XXVIII 3. Crear en la hoja 3 el siguiente formato como se muestra en la (Imagen XXIX). Y nombrarle Factura.

Imagen XXIX 4. Con los códigos de video y el de socio, extraer los siguientes rangos de celdas con la función “Buscar V”:

Práctica 23: Función Pago

Práctica 24: Función Y Formato

Práctica 25: Función SI

Práctica 26: Función SI Una agencia de turismo ofrece comisiones a sus vendedores, de acuerdo al sector que pertenezcan. Completar la tabla de datos de acuerdo a las consignas que aparecen debajo de la misma.

1. Al rango A4:D4 aplicarle: a. Combinar y centrar b. Relleno de color Azul claro c. Negrita d. Aumentar tamaño de fuente a 14 2. Al rango A10:D10 aplicarle: a. Negrita b. Centrar y Alinear en el medio c. Ajustar texto d. Relleno de color Azul claro 3. Al rango A10:D18 aplicarle Bordes/Todos los bordes 4. Colocar los montos de venta en formato Moneda. 5. COMISIÓN: utilizando la función SI, calcular la comisión que corresponda de acuerdo al sector que pertenezca 6. TOTALES: calcular el total del monto de ventas y el total de comisiones 7. Resolver lo pedido en cada caso en las celdas A21 hasta A26

Práctica 27: Función SI Una empresa lleva en una planilla de Excel el registro de sus ventas. Completar la planilla utilizando las funciones y fórmulas necesarias en cada caso.

Práctica 27: Función Anidada En el partido de Tres de Febrero se llevó a cabo un campeonato interbarrial de futbol. Esta es la tabla con los equipos y los resultados obtenidos:

En la columna RESULTADOS DEL GRUPO 1 debe aparecer: "Ganó" si la cantidad de goles es mayor a la del equipo del grupo 2 "Empató", si la cantidad de goles es igual a la del equipo del grupo 2 "Perdió", si la cantidad de goles es menor a la del equipo del grupo 2

Práctica 28: Subtotales

Práctica 29: Filtros

Práctica 30: Gráficos

Práctica 31: Gráficos

Práctica 32: TABLA DINAMICA Crea una tabla dinámica para saber cuántos alumnos tenemos por lugar de nacimiento y por turno

Práctica 33: TABLA DINAMICA Realiza una gráfica dinámica del listado de alumnos de la práctica anterior. Debe quedar así:

Práctica 33: GRAFICO DINAMICA Elabora un gráfico dinámico con los siguientes datos:

Práctica 34: MACROS 1- Crea una macro en Excel que me permita cambiar la orientación del papel a Horizontal al imprimir llama a la macro ImprimirHorizontal. 2- Crea un botón personalizado para esta macro. 3- Crea una macro en Excel que me permita cambiar la orientación del papel a Vertical al imprimir llama a la macro ImprimirVertical. 4- Crea un botón personalizado para esta macro. 5- Crea un macro que me permita insertar una nueva hoja al libro de trabajo. Llámala HOJANUEVA 6-Crear una macro que realice lo siguiente: - Escribir el texto MACRO EN EXCEL en la celda C6. - Poner este texto con una orientación vertical. - Ponerlo centrado en la celda. - Cambiar el tipo de letra a ARIAL. - Cambiar el tamaño de la letra a 14. - Poner el fondo de la celda en color azul. - Poner bordes gruesos a la celda de color azul fuerte. - Poner la letra en Cursiva y negrita. - Poner el color de la letra en color violeta. 7- Crea un botón personalizado para esta macro. 8- Crear una macro que me escriba en la fila nº1 todos los meses del año, y a partir de la fila 2 y en la columna A todos los días de la semana. Todos deben tener el tipo de letra Courier con tamaño 12, estar en negrita y doble subrayado, además de tener bordes cada una de las celdas. 9- Asigna dicha macro para poder ser ejecutada con la combinación de teclas CTRL + E.