Guía práctica para el manejo de hojas de cálculo en Excel, Apuntes de Informática

¡Descarga Guía práctica para el manejo de hojas de cálculo en Excel y más Apuntes en PDF de Informática solo en Docsity! CUADERNILLO DE INFORMÁTICA 3º AÑO EES Nombre del alumno:-------------------------- Microsoft Excel 3º año 8 Profesora Sandra Versaci La planilla de cálculo EXCEL ¿Qué es una hoja de cálculo? Básicamente, una hoja de cálculo es un documento que ha sido dividido en filas y columnas. Excel y sus aplicaciones de hojas de cálculo derivadas, han sido diseñadas para facilitar el manejo de números y cálculos. Varios comandos y botones en un menú, facilitan arreglar y dar formato a las columnas y números y calcular los totales, promedios, porcentajes, presupuestos y complejas fórmulas científicas y financieras. El aspecto de una aplicación de hoja de cálculo, proviene de los libros de contabilidad que durante siglos se han estado usando para conservar registros. La mayor ventaja de una hoja de cálculo de computadora sobre esos libros de contabilidad, es su capacidad de actualizar automáticamente los totales y otros cálculos, a medida que se ingresan nuevos datos. 1. La pantalla inicial Al iniciar Excel aparece una pantalla inicial como ésta. Indica sus nombres en los recuadros correspondientes La barra de título Contiene el nombre del documento sobre el que se está trabajando en ese momento. Cuando creamos un libro nuevo se le asigna el nombre provisional Libro1, hasta que lo guardemos y le Cuadernillo de Informática 3º año 11 Profesora Sandra Versaci Barra de Fórmula Muestra los contenidos de una celda cuando es seleccionada, bien sea texto, números o una fórmula de cálculo. celda activa Tienen alrededor de ella un borde oscuro y los encabezados de las filas y las columnas se ven en relieve o en color. La celda activa recibe los datos que ingresemos. Seleccionando una celda, la convierto en activa rango Conjunto de celdas. En este ejemplo el rango de celdas es: A1 : B2 entrar datos Seleccionar la celda, escribir sus datos y pulsar la tecla ENTER. 3. El puntero del mouse en Excel En Excel el puntero del mouse puede adoptar formas diferentes. La forma indica la acción a realizar. Forma Usada para: Seleccionar una celda o rango o Arrastrar la celda o el rango seleccionados. En Excel 2002/2003, se puede arrastrar una selección cuando el puntero se encuentra encima del borde de lo que se está seleccionando, con la forma Mover. Llenar (copiar valores dentro de las celdas a través de las que arrastra) o llenar series (copia un diseño de valores, como el llenado de los días de la semana) Ingresar o editar datos. El cursor (línea vertical dentro de la celda) titila. Redimensionar columna Redimensionar fila Copiar los formatos de celdas con la herramienta, Copiar Formato Cuadernillo de Informática 3º año 12 Profesora Sandra Versaci Enero 124 Febrero 98 Marzo 90 Escribe en esta celda la fórmula =b2+b3+b4 TOTAL 85 90 120 97 98 100 TOTAL San Luis Córdoba Buenos Aires EJERCICIO 1 Trabajar con la planilla “Formatos-Manejo de filas y columnas1.xls” Las consignas a cumplir son: 1. Eliminar del libro la hoja llamada “Hoja1”. 2. Copiar y pegar la planilla que se muestra en la hoja “Formatos” en una hoja insertada a la derecha de la hoja "Formatos”. 3. Cambiar el nombre de la hoja nueva por “Datos del Personal”. 4. Guardar los cambios realizados en el documento. 4. Ingresando datos Tipos de datos: En una Hoja de Cálculo, los distintos TIPOS DE DATOS que podemos introducir son: VALORES CONSTANTES, es decir, un dato que se introduce directamente en una celda. Puede ser un número, una fecha u hora, o un texto. FÓRMULAS, es decir, una secuencia formada por: valores constantes, referencias a otras celdas, nombres, funciones, u operadores. Se pueden realizar diversas operaciones con los datos de las hojas de cálculo como +, -, x, /,, etc... La fórmula se escribe en la barra de fórmulas y debe empezar siempre por el signo =. EJERCICIO 2: Copiar la siguiente planilla y resolver los cálculos. 5- El formato de los datos Uno de los aspectos más importantes para desarrollar un trabajo es darle un formato para que la presentación acompañe a los datos que se quieran mostrar. Un formato siempre irá aplicado a una celda o rango de celdas. Por lo tanto, un formato se podrá aplicar antes de introducir información en celdas o posteriormente cuando ya haya datos en ellas. Esto último es lo que normalmente se hace para ajustar el tamaño de la fuente, el ancho de las columnas, etc. Siempre habrá que seleccionar previamente el rango al que aplicar el formato. Este rango puede ser un rango disjunto. Desde la pestaña Inicio puedes modificar Fuente, Alineación y formato de los números. Investiga las posibilidades de cada grupo. 🖊 Investiga y anota detrás de la hoja las opciones que ofrece el menú FORMATO de CELDAS a la que accederás con el iniciador de cuadro de diálogo.  Modifica el aspecto de tu planilla anterior para que se vea como en el modelo Para los otros cálculos usas la misma fórmula, con el nombre de la celda correspondiente Cuadernillo de Informática 3º año 13 Profesora Sandra Versaci EJERCICIO 3 En el archivo “Formatos-Manejo de filas y columnas1.xls” se encuentra en la hoja “Formatos” una planilla con datos del personal del colegio, sobre la misma, se solicitará a continuación realizar los cambios necesarios para lograr que dicha planilla se vea como el modelo que se muestra al final del documento. 1. Ubicarse en la celda A1 para agregar una fila arriba de la planilla. Pulsar para ello en el grupo Celdas de la pestaña Inicio, botón INSERTAR – FILA Ingresar en las celdas A1, B1, C1,D1 y E1 respectivamente los siguientes títulos: a. Apellido b. Nombre c. Fecha Ingreso d. Cargo e. Sueldo 2. Seleccionar el rango A1:E1 y aplicar los siguientes formatos: Fuente Comic Sans, tamaño 12, Estilos negrita, subrayado, color rojo, relleno verde claro. 3. Ajustar el ancho de la columna a los títulos ingresados . (doble clic para ancho exacto) 4. Alinear hacia la derecha los datos de la columna "Nombre". 5. Alinear al centro los datos de la columna "Fecha Ingreso" y asignarle formato Fecha tipo dd- mm-aa. 6. A la columna de sueldos asignarle formato Moneda, símbolo monetario "$" y con dos decimales. EJERCICIO 4 El objetivo del presente ejercicio es crear una planilla en Excel, de las características del siguiente modelo: 1. Formato de la fila 1 a. Altura de 27,75 Pts. (Inicio – Celdas – Formato – Alto de Fila) b. Las celdas B1:C1 que contienen el dato “Buenos Aires”, tanto como las celdas D1:E1 que contienen el dato “Mar del Plata”, se encuentran combinadas. Seleccionarlas y pulsar para combinarlas. c. Estilo de la fuente: negrita. d. El contenido de las celdas se encuentra centrado horizontal y verticalmente(Inicio – Alineación). e. Relleno de las celdas: gris (Formato de Celdas – Trama). Un rango es un conjunto de celdas Cuadernillo de Informática 3º año 16 Profesora Sandra Versaci Barra de fórmulas =SUMA(RANGO) – Calcula la suma de los valores del rango. =PROMEDIO(RANGO) – Calcula el promedio de los valores del rango. =MAX(RANGO) – Calcula el valor mayor de todos los valores del rango. =MIN(RANGO) - Calcula el valor menor de todos los valores del rango. =CONTAR(RANGO) – Cuenta la cantidad de valores que hay en el rango. =CONTARA(RANGO) – Cuenta las celdas no vacías del rango. =CONTAR.BLANCO(RANGO) – Cuenta las celdas vacías del rango.  Prueba en tu computadora ¿Son correctas las siguientes relaciones? Fórmula Resultado o equivalencia V o F =6+10/2 El resultado es 8. =12-10*2 El resultado es -8 =4+6^2 El resultado es 100 =2*5^2+10 Es equivalente a 60 =25*2-4+3 Es equivalente a 25 Una función es una fórmula predefinida por Excel que opera con uno o más valores y devuelve un resultado que aparecerá directamente en la celda. Para que las funciones cumplan su cometido es necesario, en la mayoría de los casos, facilitarles unos datos. Estos datos son los que se conocen como argumentos de la función La sintaxis de cualquier función es: nombre_función(argumento1;argumento2;...;argumentoN) Respetan las siguientes reglas: - Debe empezar por el signo =. - Los argumentos o valores de entrada van siempre entre paréntesis. No dejes espacios antes o después de cada paréntesis. - Los argumentos pueden ser valores constantes (número o texto), fórmulas o funciones. - Los argumentos deben de separarse por un punto y coma ; Ejemplo: =SUMA(A1;N2;C3) - Los rangos se indican con el nombre de la primer celda, el signo : y el nombre de la última celda Ejemplo: =SUMA(A1:C8) Una función puede insertarse escribiendo directamente en la barra de fórmulas o haciendo clic sobre el botón de la misma barra. Las más usadas las encontrarás en el botón autosuma Algunas funciones importantes: Modificando cualquiera de las celdas a las que se hace referencia en una fórmula, automáticamente la fórmula se recalcula. Cuadernillo de Informática 3º año 17 Profesora Sandra Versaci Cuando introducimos una fórmula en una celda puede ocurrir que se produzca un error. Dependiendo del tipo de error puede que Excel nos avise o no. Cuando nos avisa del error, aparece un cuadro de diálogo que nos permite aceptar o rechazar la sugerencia. En otras ocasiones, en lugar del resultado, aparecen en la celda símbolos como los que se muestran a continuación, expresando el origen del error. ##### se produce cuando el ancho de una columna no es suficiente o cuando se utiliza una fecha o una hora negativa. #¡NUM! cuando se ha introducido un tipo de argumento o de operando incorrecto, como puede ser sumar textos. #¡DIV/0! cuando se divide un número por cero. #¿NOMBRE? cuando Excel no reconoce el texto de la fórmula. #N/A cuando un valor no está disponible para una función o fórmula. #¡REF! se produce cuando una referencia de celda no es válida. #¡NUM! cuando se escriben valores numéricos no válidos en una fórmula o función. #¡VALOR! similar a #¡NUMERO!, cuando el tipo de argumento solicitado por la función, es distinto al ingresado por el usuario. Por ejemplo, al ingresar un argumento lógico cuando la función requiere un rango, o un número cuando la función espera texto. #¡NULO! cuando se especifica una intersección de dos áreas que no se intersectan. EJERCICIO 7 1. Abrir el archivo “FORMULAS Y FUNCIONES.XLS” para completar el ejercicio de la Hoja1. 2. Aplicar formato número con separador de miles, sin decimales, números negativos en color rojo, sobre Ventas de Octubre (Columna D) y Ventas de Noviembre (Columna H) 3. Seleccionar el rango de celdas B5:B12 y aplicarle formato Reducir hasta ajustar. 🖊 Anota para qué sirve este formato que encontrarás en FORMATO de CELDAS – ALINEACIÓN 4. En el rango de celdas E5:E12 calcular el total de ventas realizado en el mes de octubre por cada artículo, =Precio Octubre (columna C) * Ventas Octubre (columna 5. En el rango de celdas G5:G12 calcular el precio actualizado de cada artículo en el mes de noviembre =Precio Octubre (columna C) *Porcentaje Aumento (columna F). 6. En las celdas I5:I12 calcular el total de ventas realizado de Noviembre por cada artículo, =Precio Noviembre (columna G) * Ventas Noviembre (columna H). EJERCICIO 8 Este ejercicio se desarrollará utilizando la Hoja3. Cuadernillo de Informática 3º año 18 Profesora Sandra Versaci 1. En el Rango de celdas E6:E13 calcular el precio de venta de cada artículo, tendiendo en cuenta que para realizar dicho cálculo deberá utilizar los valores del precio de costo de cada artículo (C6:C13) por el % de recargo (D6:D13) que se desea aplicar como ganancia. 2. Agrega una columna para calcular IVA, que resulta de multiplicar el precio de venta por el 21% 3. En otra columna calcula el precio final: precio de venta más el IVA 4. Realiza los cálculos sólo en la primera fila de la planilla. Luego arrastra con el Mouse para copiar la fórmula a las celdas inferiores. 5. En el rango de celdas G6:G13 calcular el total vendido, utilizando los valores de la columna Precio Venta (col E) por los valores de la columna Unidades vendidas (col F). REFERENCIAS A CELDAS Y RANGOS Referencias relativas : Se basa en la posición relativa de la celda que contiene la fórmula y de la celda a la que hace referencia. Si cambia la posición de la celda que contiene la fórmula, se cambia la referencia. Si se copia la fórmula en filas o columnas, la referencia se ajusta automáticamente. Referencias absolutas: Siempre hace referencia a una celda en una ubicación específica. Si cambia la posición de la celda que contiene la fórmula, la referencia absoluta permanece invariable. Si se copia la fórmula en filas o columnas, la referencia absoluta no se ajusta. Ej =$A$1. Referencias mixtas Una referencia mixta tiene una columna absoluta y una fila relativa, o una fila absoluta y una columna relativa. Una referencia de columna absoluta adopta la forma $A1, $B1, etc. EJERCICIO 9 Copiar la siguiente planilla VENTAS PORC.AUM. MODULO ARTICULO STOCK MIN PREC.UNIT. PREC.ACTUALIZ. MANTECA 10 $ 1,40 YOGURT 20 $ 1,20 2% LACTEOS QUESOS 10 $ 6,00 LECHE 50 $ 1,00 CREMA 20 $ 2,30 PIZZA 30 $ 1,00 5% MASAS TAPA TARTA 20 $ 1,50 TAPA EMP. 30 $ 1,20 PASTELITOS 15 $ 1,20 1. Calcular el precio actualizado de los productos lácteos, teniendo en cuenta que sufren un 2% de aumento en sus precios, el mismo se encuentra en la celda A6. =$A$6 * Precio unitario Cálculo de porcentajes: 1. ¿Cuánto es el 15% de 1200? X% de un N° =x% * N° Ej: 15%*1200 2. ¿Qué porcentaje es 250 de 1000? N° / total=….% Ej:250/1000= ... % Cuadernillo de Informática 3º año 21 Profesora Sandra Versaci  Copia y pega la planilla terminada en la hoja 2, ordenando por el valor de ventas en forma descendente, agregando un segundo nivel de orden ascendente por vendedor. VII - Introducción de fechas y horas Una fecha para Excel es un número comprendido entre el 1, que corresponde al 1 de enero de 1900 y el 2958465 que corresponde al 31 de diciembre del 9999. Por tanto podremos operar para calcular el tiempo transcurrido entre dos fechas. Se deben introducir siguiendo el esquema día mes año. Entre el día, el mes y el año debe aparecer el separador / o bien el guión -. Por ejemplo 23/12/98. Las horas son para Excel números comprendidos entre el 0, que corresponde a las cero horas, y el uno, que corresponde a las 24 horas. Igual que con las fechas se puede operar con las horas. Para introducir una hora habrá que poner la hora seguida de dos puntos y los minutos. Formatos de fecha y hora Los formatos de fecha y hora se pueden cambiar en el cuadro de diálogo Formato de celdas en la ficha de Número, eligiendo las categoría Fecha y la categoría Hora, o en el botón del grupo Número en la pestaña Inicio. Como ocurre con los formatos de número, las fechas y las horas se rigen por sus propios códigos. Para crear un formato de fecha personalizado, utilizaremos la letra d para el día, la letra m para el mes y a para el año. Para crear un formato de hora, usaremos h para hora, m para minuto y s para segundos. Cuadernillo de Informática 3º año 22 Profesora Sandra Versaci EJERCICIO 14.1 1. Dada la siguiente lista de personas determinar los años, días y minutos de vida. Para ello deberás ingresar la fecha actual y realizar los cálculos con las funciones correspondientes  Para calcular días =DIAS 360(F INICIAL; FECHA FINAL)  Para calcular horas =DIAS 360(F INICIAL; FECHA FINAL)*24  Para calcular años =DIAS 360(F INICIAL; FECHA FINAL)/360 2. Ordenar alfabéticamente la planilla. 3. Ordenar por fecha cuando encuentra nombres repetidos Nombre Fecha de nacimiento Años Días Minutos Juan 15/08/79 Pedro 05/07/68 Juan 25/07/85 Magali 06/04/89 Andrea 05/12/66 Pedro 29/01/29 Horacio 13/06/57 EJERCICIO 14.2 En el ejercicio anterior utilizar filtros para mostrar:  los 3 mayores  los 3 menores  los que tienen más de 30 años  los que tienen menos de 50 añoS Los filtros nos permiten visualizar sólo determinados datos en una planilla, aquellos que cumplen con un requisito especificado. Se aplican desde la pestaña DATOS en la opción FILTRO. Verás al activarlo que aparece una flecha a la derecha de cada título de columna. 🖊 Pulsa en esa flecha y anota las opciones disponibles. EJERCICIO 15 1. Sitúate en la celda A1 y pulsa sobre , selecciona la categoria de fecha y hora y elige la función AHORA().Pulsa el botón Aceptar. 2. Aparece un cuadro de diálogo indicando que la función no tiene argumentos. Pulsa de nuevo sobre Aceptar. 3. Sitúate en la celda B2 y escribe el día de hoy en número. Ej. 5 4. Sitúate en la celda B3 y escribe el mes actual en número. Ej. 8 5. Sitúate en la celda B4 y escribe el año actual en número. Ej. 2007 6. Sitúate en la celda C5 y pulsa sobre , escoge la función FECHA() y pulsa el botón Aceptar. 7. Selecciona como argumentos las celdas B4 --> para año, B3 --> para mes y B2 --> para día, pulsa Aceptar. 8. Vamos a calcular nuestra edad. 9. Sitúate en la celda D1 y escribe tu fecha de nacimiento en formato (dia/mes/año) 10. En la celda E1 escribe =HOY() Cuadernillo de Informática 3º año 23 Profesora Sandra Versaci 11. En la celda E2 selecciona la función DIAS360, como fecha inicial la celda D1 (fecha nacimiento), como fecha final E1 (el día de hoy) y en método escribe Verdadero. 12. Como resultado nos aparece los días transcurridos desde la fecha D1 y la fecha E1. 13. Ahora en la celda F3 escribe =E2/360 para obtener los años. 14. El resultado aparece con decimales, para que nos salga solo la parte entera podemos utilizar la función =ENTERO(E2/360). 15. Hemos utilizado cuatro de las funciones más utilizadas y que ofrecen muchas posibilidades. FUNCIONES LÓGICAS: Y – O Cuadernillo de Informática 3º año 26 Profesora Sandra Versaci Por ejemplo si en una copiamos una celda cuyo contenido en Enero en las celdas adyacentes, las celdas copiadas continuarán la secuencia de meses, Febrero, Marzo,... Si por lo contrario copiamos una celda cuyo contenido es una fecha como por ejemplo 22/07/68, las celdas copiadas seguirán la serie de fechas, 23/07/68, 24/07/68,... Lo mismo ocurre con horas, si copiamos una celda con por ejemplo la hora 10:00, las copiadas seguirán 11:00, 12:00,.... Cuando copiamos fechas las opciones de relleno pasan a ser las siguientes: Existen también otras series como podrían ser las numéricas. Por ejemplo, supongamos que en la celda A1 tenemos el valor 1 y en B1 el valor 2, ahora seleccionamos las dos celdas y con el controlador de relleno copiamos las celdas hacia la derecha, pues en las celdas adyacentes se seguirá la serie, 3, 4, 5,... Si en vez de 1 y 2 tenemos 2 y 4 y copiamos con este método la serie de las celdas adyacentes seguirá con números pares. Cuando copiamos series numéricas, las opciones de relleno incluyen Rellenar serie pero no Rellenar meses. Formato condicional: Es el formato que Excel aplica automáticamente a las celdas si la condición que se especifica es cierta. Para aplicarlo procedemos de la siguiente forma: Seleccionamos la celda a la que vamos a aplicar un formato condicional. - Accedemos al menú Formato condicional de la pestaña Inicio. Elegimos la opción Nueva regla que permite crear una regla personalizada para aplicar un formato concreto a aquellas celdas que cumplan determinadas condiciones. Nos aparece un cuadro de diálogo Nueva regla de formato como el que vemos en la imagen. En este cuadro seleccionaremos que se aplique el formato únicamente a las celdas que contengan un valor, aunque puedes escoger otro diferente. En el marco Editar una descripción de regla deberemos indicar las condiciones que debe cumplir la celda y de qué forma se marcará. Cuadernillo de Informática 3º año 27 Profesora Sandra Versaci Para agregar otra condición, haga clic en Agregar y repita los pasos Pueden especificarse hasta tres condiciones. Si ninguna de las condiciones que se han especificado es verdadera, las celdas conservarán los formatos existentes. De esta forma si nos basamos en el Valor de la celda podemos escoger entre varias opciones como pueden ser un valor entre un rango mínimo y máximo, un valor mayor que, un valor menor que y condiciones de ese estilo. Los valores de las condiciones pueden ser valores fijos o celdas que contengan el valor a comparar. Si pulsamos sobre el botón Formato... entramos en un cuadro de diálogo donde podemos escoger el formato con el que se mostrará la celda cuando la condición se cumpla. El formato puede modificar, el color de la fuente de la letra, el estilo, el borde de la celda, el color de fondo de la celda, etc. Al pulsar sobre Aceptar se creará la regla y cada celda que cumpla las condiciones se marcará. Si el valor incluido en la celda no cumple ninguna de las condiciones, no se le aplicará ningún formato especial. EJERCICIO 17 1. Realizar la siguiente tabla de gastos utilizando el autollenado. 2. Destacar en rojo los días domingo 3. Validar los datos de modo que no puedan ingresarse gastos mayores a 85$ Gastos hogareños Período 17 de junio a 5 de julio Fecha Día Gastos 17-Jun Domingo $18,50 18-Jun Lunes $20,50 19-Jun Martes $36,20 La validación de datos sirve para restringir la entrada de los mismos en el rango seleccionado. Se aplica desde la pestaña DATOS y permite:  Indicar a Excel qué tipo de datos y dentro de qué valores vamos a permitir.  Mostrar un mensaje al usuario para informar sobre los datos permitidos.  Mostrar un mensaje de error para informar al usuario cuando ha ingresado valores no permitidos. 20-Jun Miércoles $10,56 21-Jun Jueves $54,23 22-Jun Viernes $44,36 23-Jun Sábado $38,13 24-Jun Domingo $25,16 25-Jun Lunes $84,25 26-Jun Martes $52,65 27-Jun Miércoles $70,45 28-Jun Jueves $80,35 29-Jun Viernes $72,13 30-Jun Sábado $56,54 01-Jul Domingo $28,30 02-Jul Lunes $12,50 03-Jul Martes $9,60 04-Jul Miércoles $40,58 05-Jul Jueves $27,65 Promedio de gastos Total de gastos EJERCICIO 18 1. Se desea saber las ventas totales bimestrales de una empresa de turismo. Construir en la Hoja2 la siguiente Planilla se para calcular las ventas bimestrales. Cuadernillo de Informática 3º año 28 Profesora Sandra Versaci 2. Los importes a calcular se tomarán de la Hoja1, donde debes construir la planilla que con cada uno de los meses y aplicar las funciones correspondientes. Mayor venta: Mes de mayor venta: Menor venta: Mes de menor venta: EJERCICIO 19 Liquidación de sueldos Empleado Gaspar Meiten Rumenigue Bolton Fecha de ingreso 03/03/88 05/08/80 06/04/83 08/09/87 Horas de trabajo 124 134 150 119 Sueldo básico Presentismo Antigüedad Sueldo bruto Jubilación Obra Social Sueldo a cobrar Precio por hora: $12,32 Sueldo básico: Horas trabajadas x precio por hora. Presentismo: 12% del sueldo básico. Antigüedad: $24.36 por año trabajado. Sueldo bruto: Sueldo básico + presentismo + antigüedad. Jubilación: 11% del sueldo bruto. Obra Social: 3% del sueldo bruto. Sueldo a cobrar: Sueldo bruto - jubilación - obra social. Validar los datos para que no se permita el ingreso de más de 150 horas de trabajo Ventas N ú m e ro Meses Vtas totales 1 Febrero $752,36 2 Marzo $804,23 3 Abril $954,25 4 Mayo $654,32 5 Junio $704,58 6 Julio $659,46 7 Agosto $700,45 8 Septiembre $602,48 9 Octubre $745,16 10 Noviembre $802,49 Bimestre Ventas torales Feb/Mar Abr/May Jun/Jul Ago/Sep Oct/Nov Cuadernillo de Informática 3º año 31 Profesora Sandra Versaci =CONTAR.SI(rango; “condición”) Ej: =CONTAR.SI (B5:B12; “>10”) EJERCICIO 25 A partir de la siguiente tabla, contar todos los meses que superaron el gasto previsto. Utilizar para ello la función CONTAR.SI. Esta función devuelve la cantidad de celdas que cumplen con una condición determinada, en este caso la cantidad de meses que superan el gasto previsto. Gastos mensuales previstos:$2.987 Total anual de gastos: Meses con gastos superiores al previsto: EJERCICIO 26 Una empresa de turismo desea saber la cantidad de salidas y llegadas a una determinada fecha. Para ello cuenta con la siguiente tabla: Destino Salida Llegada Días Precio/unidad Cantidad Total Mar del Plata 15-Sep 01-Oct $ 287,00 3 Cordoba 11-Oct 10-Nov $ 246,25 4 Salta 28-Sep 05-Oct $ 478,21 2 Rosario 05-Nov 24-Nov $ 198,45 1 San Clemente 20-Oct 19-Nov $ 200,13 6 Sta. Teresita 09-Ago 14-Ago $ 254,54 7 Bariloche 06-Jul 20-Jul $ 548,36 2 Necochea 17-Sep 12-Oct $ 235,98 4 Ventas totales: Salidas luego del 15-Sep : Llegadas antes del 01-Oct: EJERCICIO 27 Utilizar la función CONTAR.SI para saber cuantos pagaron menos del 47% del valor del auto. Control de cuotas - Automóviles Apellido Automovil Precio total Precio/cuota Cuotas pagas Total pagado Porcentaje pagado Guzmán Gol 1.6 $13.540 $185,56 50 Mangone Golf 1.8 $18.763 $245,62 69 Loberche Escort GL $12.834 $174,26 25 Tenca Renault 19 $15.623 $205,26 10 Perez Renault Clio $14.982 $198,15 47 Alonso Fiat Bravo $19.734 $314,65 58 Riquelme Fiat Uno $8.734 $142,48 38 Comizo Polo Nafta $14.374 $184,26 14 Cantidad que pagaron más del 40% del valor del auto: Cantidad que pagaron más del 75% del valor del auto: Cantidad que pagaron menos del 25% del valor del auto: Mes Gastos Enero $3.064 Febrero $3.548 Marzo $2.154 Abril $2.578 Mayo $2.654 Junio $1.587 Julio $2.568 Agosto $3.586 Septiembre $457 Octubre $3.987 Noviembre $4.874 Diciembre $2.456 Cuadernillo de Informática 3º año 32 Profesora Sandra Versaci =SUMAR.SI(rango que cumple la condiciòn; “condiciòn”; rango para sumar) Ej: =SUMAR.SI( B2:B7; “BUENOS AIRES”; E2:E7) EJERCICIO 28 Utilizar la función SUMAR.SI para realizar los cálculos en la siguiente planilla: Esta función devuelve la suma de los valores que cumplen con una condición determinada. 1. Ingresos de clientes de Buenos Aires: 2. Ingresos de clientes con los que trabajamos desde 1997: 3. Ingresos de clientes de La Plata: 4. Ingresos de clientes con perfil particular: 5. Ordene alfabéticamente la planilla según el apellido de los clientes 6. Muestre con diferente formato los apellidos de acuerdo al perfil: Comercial en verde, Particular en gris 7. Calcule los subtotales de los ingresos de los clientes, de acuerdo a la ciudad de residencia Cliente Ciudad de residencia Perfil Año de Alta Ingresos 2000 Cevallos Buenos Aires Comercial 1996 1546 Pereira Mar del Plata Comercial 1996 1254 Chiclana Buenos Aires Particular 1998 3198 Volpe La Plata Comercial 1997 4578 Martinez Buenos Aires Particular 1994 6431 Farsi La Plata Particular 1995 2469 Vitale Buenos Aires Comercial 1999 2465 EJERCICIO 29 Calcular el importe a pagar en concepto de cuota de seguro para los siguientes automóviles. 1. Copiar la tabla utilizando correctamente las opciones de formato de celda. 2. Calcular cuánto debe pagar de seguro cada auto sabiendo que si es mayor a 1995 paga $100 y si es menor $80 y si además tuvo accidente se debe sumar $50. Auto Año Accidente Importe a Pagar VW Gol 2000 NO Renault 21 1994 SI Fiat Palio 1997 SI VW Golf 1994 NO Fiat Marea 2000 NO F. Regatta 1997 SI Renault 11 1990 NO Renault 9 1994 SI VW Polo 2001 NO Cantidad de autos de año mayor a 1995: Importe a pagar de autos de año 2000: Promedio de importes de año mayor a 1999: Cuadernillo de Informática 3º año 33 Profesora Sandra Versaci 3. Mostrar los datos de la columna accidente en rojo cuando es NO y en azul cuando es SI 4. Validar los datos de manera que no se permita el ingreso de años menores a 1990 (redactar mensaje entrante y mensaje de error adecuados) 5. Calcular subtotales del importe a pagar de acuerdo al año del vehículo 6. Copiar y pegar la planilla, ordenando la segunda en forma ascendente por año. EJERCICIO 30 Un colegio requiere una planilla para calcular la calificación de sus alumnos, para ello cuenta con una tabla de equivalencias entre la nota numérica y la nota final. Mostrar usando filtro avanzado el nombre y la calificación numérica de los alumnos  con calificación BUENO  de los que tienen calificación EXCELENTE Calificación del computación XI- Función > BUSCARV La función BUSCARV toma un valor que indicamos en una celda cualquiera de la hoja y lo busca en la primera columna de la tabla. Cuando lo encuentra, devuelve el valor que está en la misma fila pero en la columna que indicamos en la función. Sintaxis: valor buscado: es el valor buscado en la primera columna de la tabla. matriz de comparación: es la tabla donde se efectúa la búsqueda. indicador columnas: es un número que especifica la columna de la tabla de donde se devuelve un valor. ordenado: es un valor lógico (VERDADERO o FALSO) que indica que la primera columna de la tabla donde se buscan los datos esta ordenada o no. Si omite este argumento se considera VERDADERO, es decir, se considera que la columna uno de la tabla esta ordenada. Si no se está seguro poner siempre FALSO. EJERCICIO 31 Una empresa de viajes posee una tabla de datos de clientes. Determinar el destino y el precio a pagar (P. unitario por Cantidad) para el cliente solicitado en la consulta. Aplicar autoformato a la tabla =BUSCARV(valor buscado; tabla; indicador columnas; [ordenado]) Ej: =BUSCARV( S7; A2:F5; 3; FALSO) Tabla de equivalencias 0 PESIMO 30 MALO 45 REGULAR 60 BUENO 75 MUY BUENO 90 EXCELENTE Alumno Calif. Numérica Calificación Bengoles 98 Agnese 56 Elffman 87 Libertella 72 Ballestrini 65 Sánchez 15 Cuadernillo de Informática 3º año 36 Profesora Sandra Versaci Materia Trim. 1 Trim. 2 Trim. 3 Estado Computación 7 7 8 Matemática 4 6 9 Física 3 5 10 Química 4 7 6 Historia 9 8 9 TOTAL Aprobadas Desaprobadas ingreso finales 8ºA 5 3 8ºB 6 8 8ºC 3 7 8ºD 9 14 9ºA 8 6 9ºB 4 4 9ºC 5 6 9ºD 6 5 Totales Curso en que hubo más pedidos: Curso en que hubo más vacantes: EJERCICIO 36 Realice un gráfico circular para evaluar el porcentaje de sueldos de cada sector utilizando la Hoja3 del archivo Gráficos. 1. Realice un gráfico circular tomando los valores de sueldo de cada sector. 2. Debe mostrar los porcentajes de cada sector en cada porción del gráfico. 3. Cambie los colores de las porciones de cada sector. 4. Ubique las leyendas en el margen inferior del gráfico. 5. Aplique un borde con puntas redondeadas EJERCICIO 37 Realice un gráfico de Área Apilada que muestre los gastos que representan la siembra y cosecha de cereales, utilizando la Hoja4 del mismo archivo. 1. El tamaño del gráfico será de 6 columnas y 17 filas. 2. Cambie el color de cada serie de datos. 3. Oculte las leyendas y mostrar la tabla de datos. 4. Coloque como título del gráfico “Total de Gastos” 5. Todo el gráfico utiliza fuente Arial, tamaño 7, sólo el título del gráfico tendrá tamaño 10. 6. El área del gráfico será de color azul con efecto sombreado, oscuro en la base y más claro en la parte superior. 7. Cambie el color del área de trazado del gráfico utilizando como efecto de relleno una textura a su elección. 8. Aplique un borde con puntas redondeadas de color azul. EJERCICIO 38 Copie y complete la siguiente planilla 1. La columna estado debe decir "Aprobado" si el promedio es 7 o más, caso contrario "Desaprobado". 2. Realizar un gráfico de tipo columna agrupada con efecto 3D para determinar las notas trimestrales de cada materia. Cuadernillo de Informática 3º año 37 Profesora Sandra Versaci  La serie datos para el Trim. 1 debe estar con relleno degradado, el Trim. 2 con efecto de textura y el Trim. 3 con efecto trama.  El título del gráfico debe ser "Promedios", el eje x debe decir "Materias" y el z debe decir "Notas"  Ubicar el gráfico en una hoja nueva.  Cada serie de datos debe contener el valor correspondiente. 3. Realizar otro gráfico de tipo circular con efecto 3D que refleje las cantidades totales de aprobados y desaprobados. 4. Los gráficos deben tener un tamaño de fuente que permita identificar cada valor Ejercicio integrador Primera Parte La empresa Millet Brokers, dedicada a realizar inversiones en el mercado de acciones, realiza diariamente un informe del resultado de sus transacciones financieras. Debes confeccionar una planilla como la que se adjunta en el modelo, utilizando para ello el archivo INVERSIONES A modo de guía te apuntamos algunos detalles del diseño que deberás efectuar: 1. El alto de las filas está definido en 20 2. La columna Fecha, muestra los días hábiles consecutivos a partir del 10/08/03. El formato de las mismas es dd/mm/aa 3. El Precio cierre de la acción se encuentra en formato monetario con 2 decimales 4. El Valor Anterior se corresponde con el precio cierre de la acción del día anterior. Deberías ingresar la fórmula correspondiente. Tiene en cuenta que el formato sigue siendo monetario con 2 decimales 5. La Cantidad de acciones es un valor que se mantuvo constante para todas las fechas que muestra el informe. Es decir para todos los días el valor es de 1.000 unidades. Define el formato correspondiente tal que muestre el separador de miles 6. El rendimiento se calcula en todos los casos como el Precio Cierre menos el Valor Anterior, todo esto multiplicado por la Cantidad de acciones del portafolio Cuadernillo de Informática 3º año 38 Profesora Sandra Versaci 7. El formato de la columna Rendimiento es tal como lo muestra el ejemplo, definiendo en color rojo los saldos negativos 8. Muestra la columna “F” de nombre "Porcentual",que ha sido ocultada y luego elimínala 9. Tendrás que insertar una columna y llamarla Diferencia, la misma deberá estar ubicada a la izquierda inmediata de la columna Rendimiento. Su contenido deberás calcularlo como la diferencia (resta) entre el precio de cierre de cada día y el precio cierre del primer día del listado 10. Se requiere insertar la columna que muestra el nombre de la acción sobre la cual se realiza el análisis. En este caso es Acindar. 11. Será necesario aplicar un recuadro al informe resaltando en trazo más grueso el borde del mismo y definir un sombreado para las filas y columnas de los títulos Segunda Parte Para imprimir el informe anterior, también es necesario que realices algunos ajustes, a continuación te detallamos una guía: 1. El tamaño de la hoja es A4 y el informe se muestra en forma apaisada 2. Define los márgenes de forma que la tabla quede centrada 3. Es necesario que aparezca en el extremo superior izquierdo el nombre de la compañía financiera Millet Brokers - Inversionistas, tal como lo muestra el modelo 4. También es conveniente que en el extremo inferior derecho aparezca el número de página tal como lo muestra el modelo adjunto Tercera Parte Aplicando fórmulas tendrás que resolver las siguientes consignas 1. Calcular el promedio del precio cierre 2. Calcular la fecha de inicio y de finalización de las operaciones 3. Nombra a la hoja 1 como “Informe” 4. Elimina las hojas que no contiene información Ejercicios de repaso Ejercicio1: Función Si 1. Determinar cuántos tipos de productos tienen un precio unitario mayor al promedio gral. 2. Agregar una columna en donde figuren los textos: "Máxima precaución" para categ. A "Precaución moderada" para categ. B "Precaución de rutina" para categ. C Código Cantidad Precio Unit. Inflamable (Categ.) a1 12568 0,23 A a2 2356 0,25 B a3 4588 0,36 A b1 9522 0,14 C b2 12544 0,18 A b3 1020 0,6 C c1 15402 0,27 A c2 6598 0,78 B c3 8787 1,02 B