Curso de
EXCEL
Prof. Viviana Lloret http://aulamatic.blogspot.com
EXCEL-PLANILLA DE CÁLCULO
Una hoja de cálculo es un programa que permite realizar cálculos aritméticos, estadísticos, financieros, etc., con una serie de datos previamente introducidos. Excel es el máximo exponente de las hojas de cálculo existentes en el mercado actual. Con Excel podemos generar presupuestos, gráficos estadísticos, facturas, análisis de bolsa, ventas, inventarios, etc. Excel incorpora incorpora cantidad de funciones funciones que nos facilitará el cálculo de operaciones complejas.
Pantalla Principal Cuadro de nombres de celdas
Cursor
Barras de herramientas
Barra de fórmulas
Columnas
Filas Nombre de las hojas
Como vemos las filas se designan con números y las columnas con letras, la intersección de una fila y una columna determina una celda, por ejemplo A1, B5, etc Llamamos Rango a un conjunto de celdas. En la imagen anterior se señaló el rango G10: H11 (está formado por las celdas G10, G11, H10, H11); el E7: E10 (formado por las celdas E7, E8, E9, E10). El signo que se utiliza para separar las dos celdas son los dos puntos.
Rango A1:A5
Rango B1:C4
Para tipear texto en una celda: celda: Clic en ella, tipear el texto, ENTER Para eliminar el contenido de una celda: celda: Clic en ella, SUPRIMIR . Para modificar parcialmente el contenido de una celda, clic en ella, oprimir F2, con lo cual el cursor se ubica dentro de ella. Para modificar ancho de columna, columna, seleccionar la columna (clic en la cabecera), clic botón derecho, ancho de columna.
Prof. Lloret Viviana
EXCEL
Para que un texto quede centrado entre un número de columnas, por ejemplo centrar el texto en la fila 1 entre las columnas A y D, selecciono A1: D1, clic en el botón combinar y centrar. Para modificar estilo, tamaño, tipo de fuente: FORMATO/ CELDA/FUENTE. Para trazar un borde inferior, superior, etc. a un conjunto de celdas, selecciono, FORMATO/ CELDA /BORDES Y SOMBREADO. Para aplicar formato a un conjunto de datos, seleccionar rango, clic en FORMATO/ CELDA. MENÚ FORMATO/ CELDA Número: Formateamos el contenido de la celda de acuerdo al tipo de dato: General, Números (elegir cantidad de decimales). Moneda: $ español (argentina). Fecha, Porcentaje, Texto.
Alineación: Alineación del texto: Puede ser horizontal o vertical. Control del texto: Ajustar texto: Si el texto escrito supera el ancho de la celda, y éste no se quiere modificar, seleccionar celda, clic en FORMATO / FORMATO DE CELDAS/ ALINEACION / AJUSTAR TEXTO (Con lo cual el texto figurará en dos líneas). Reducir hasta ajustar : Reduce el tamaño de la fuente. Combinar celdas: Transforma un conjunto de celdas en una sola. Fuente: Desde aquí se puede modificar el estilo, tamaño, color, etc. Bordes: Para colocar todo tipo de bordes. Tramas: Sombreado de celdas. Menú Insertar Los archivos de Excel se denominan libro, cada libro tiene de manera predeterminada 3 hojas. Para insertar hoja de cálculo: Insertar/Hoja de cálculo. Para eliminar una hoja de cálculo: botón derecho sobre la etiqueta de la hoja, Eliminar.
Prof. Lloret Viviana
EXCEL
Otras opciones del menú Insertar: Gráfico, Símbolo, Función, Hipervínculo, comentario. Una celda puede contener valores numérico (números, porcentaje, moneda), alfanuméricos (textos), fórmulas. Para copiar el contenido de una celda o conjunto de celdas (en la misma hoja), seleccionar celdas a copiar/copiar, clic en la celda a pegar: ENTER o PEGAR. Para copiar el contenido de una celda o conjunto de celdas (en otra hoja), seleccionar celdas a copiar/copiar, clic en la hoja a copiar, clic en la celda a pegar: ENTER o PEGAR.
Cálculos en Excel En la siguiente planilla, se pueden observar algunos cálculos que se utilizan frecuentemente, ellos son: Suma (+): En las celdas B8, D8; E8, F8 Producto (*): En la celda D3 Resta (-): En la celda F3 División: (/), Potenciación (^) (No están presentes en esta planilla) Porcentaje de un valor dado (%): En la celda E3.
Dirección Absoluta
Luego se copió la fórmula ingresada en D3 mediante la técnica de arrastrar en las celdas D4, D5, D6 y D7. Lo mismo se hizo con el resto de las fórmulas. En la tabla inferior se calculó el descuento teniendo en cuenta el porcentaje ingresado en la celda E13, con lo cual para poder, después, arrastrar el cálculo se debió dejar fija dicha celda (Dirección absoluta) anteponiendo a la letra y al número que identifica columna y fila el signo $ ( $E$13) Para realizar cálculos siempre primero escribo = en la celda donde debe figurar el resultado 1 y luego escribo la fórmula.
Prof. Lloret Viviana
EXCEL
celda, colocar el cursor donde muestra la imagen, cuando éste toma la forma de cruz fina, clic y arrastrar al resto de las celdas. Resulta útil, esta técnica, al momento de escribir series tales como lunes, martes,… o Enero, Febrero,…, o series numéricas. En el caso de las dos primeras, escribimos el primer elemento de la serie, luego clic con el botón izquierdo y arrastrar para que se autocomplete la serie. En el caso de series numéricas, es necesario completar los dos primeros elementos,(por ejemplo 2, 4; de ese modo se indica que la serie es de 2 en 2) seleccionarlos y arrastrar para que se genere la serie. Para sumar: • a) =SUMA (rango a sumar) b) Selecciono el rango a sumar, más la celda que contendrá el resultado, clic en autosuma ( Σ ). Para calcular un porcentaje de una cantidad dada, multiplicamos • la celda que contiene la suma por el porcentaje. Ejemplo: la fórmula ingresada en E3 (planilla anterior) Para calcular qué porcentaje es una cantidad A de una cantidad • B, primero a la celda que guardará el resultado se le da formato porcentaje, luego escribo = A / B.
Funciones
en Excel
SUMA Sintaxis
= SUMA (rango)
Ejemplo: Para sumar el total de simpatizantes de fútbol, en la planilla anterior se utilizó: =SUMA (B2:B9) PROMEDIO Sintaxis
=PROMEDIO (rango)
Ejemplo: Para calcular el promedio de simpatizantes de fútbol, en la planilla anterior se utilizaría: =PROMEDIO (B2:B9) MAXIMO Sintaxis
= MAX (rango)
Prof. Lloret Viviana
EXCEL
Ejemplo: Para calcular el menor número de simpatizantes de fútbol, utilizaría: =MIN (B2:B9) Ejemplo referido a la planilla que se muestra en la presente página:
En la celda (C6) deberá calcular el margen de temperaturas. Es decir la diferencia existente entre la marca de temperatura más elevada y la más baja. En C6 escribimos = MAX (D2:D92) – MIN (D2:D92)
CONTARA Sintaxis ricos)
= CONTARA (rango) (el rango debe contener datos alfanumé-
Ejemplo: Para calcular la cantidad de equipos encuestados utilizaría: =CONTARA (A2:A9) CONTAR Sintaxis
= CONTAR (rango) (el rango debe contener datos numéricos)
Ejemplo: Para calcular la cantidad de equipos encuestados podría utilizaría: =CONTAR (B2:B9) SI Sintaxis = SI (condición; valor si condición es verdadera; valor si condición es falsa)
En la columna H, rango (H2:H92), deberá calcular para cada una de las marcas si la temperatura del registro es superior o inferior a la temperatura promedio del día. En caso que la temperatura sea superior o igual al promedio deberá mostrarse la leyenda “Superior”, en caso contrario deberá mostrarse la leyenda “Inferior”. En H2 escribimos: =SI (D2 >=$D$93; “superior”;”Inferior”)
Para condiciones compuestas Y Sintaxis
=Y (condición_1; condición_2;...; condición_n)
La condición será verdadera si se cumplen todas simultáneamente. Ejemplo: En la columna “Alerta Nevadas”, rango (G2:G92), deberá alertarse mediante la leyenda “Verdadero” en caso de que las condiciones climáticas indiquen que existe una alta posibilidad de nevadas. En caso contrario deberá mostrarse la leyenda “Falso”. La alerta deberá activarse para las marcas cuya temperatura sea inferior a los -5 °C y la humedad superior al 90%.
En G2 escribimos: =Si (y (D2<-5; E2 > 90%); VERDADERO; FALSÓ) otra forma
Prof. Lloret Viviana
EXCEL
La condición será verdadera si se cumple al menos una. CONTAR.SI Sintaxis
=CONTAR.SI (RANGO A EVALUAR;”Condición”)
SUMAR.SI Sintaxis
=SUMAR.SI (Rango a evaluar;”condición”; rango a sumar)
=HOY () Para establecer la fecha actual: =HOY () Para establecer el número de días entre dos fechas (la celda que contiene la fórmula debe tener formato número): Se resta a la celda que contiene la fecha mayor la menor. ENTERO
Prof. Lloret Viviana
EXCEL
Si se desea calcular en años: =DIAS360 (Fecha_inicial;Fecha_final;1)/360 Si se desea calcular en meses =DIAS360 (Fecha_inicial; Fecha_final; 1)/30 DIASEM Sintaxis
= DIASEM (FECHA; TIPO)
Devuelve un número de 1 a 7 que identifica el día de la semana. Si tipo = 1 el domingo está representado por 1 y el sábado por 7. Ejemplo: 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. (Se ha dejado otro formato en la columna fecha para que se pueda apreciar la validez de la fórmula aplicada). Se utilizo la función Diasem y si anidada. El razonamiento empleado fue el siguiente:
En la primera condición que se evalúa al igual a 6 se pregunta si el día ingresado en la celda es viernes, en caso de ser verdadero para llegar al lunes se suman tres días, si la condición es falsa se vuelve a preguntar si el día es sábado, en caso de ser verdadera esta última condición se suma 2, si son falsas las dos condiciones anteriores significa que el día puede ser de domingo a jueves, razón por la cual sólo se suma 1, en resumen:
=SI ( diasem(A21;1)=6;A21+3;si (d iasem(A21;1)=7;A21+2;A21+1 ) )
BUSCARV Sintaxis
=BuscarV (valor_buscado; matriz_buscar_en; columna; 0 ó 1) Dada una matriz o rango en donde figura el número de legajo (valor por el cual se buscará el nombre y apellido _debe ir en la primer columna-), el nombre y el apellido, se desea ingresar en la celda A11 el número de legajo y que aparezca automáticamente el nombre y apellido. Valor_buscado: En nuestro ejemplo se halla en A11.
Prof. Lloret Viviana
EXCEL
PI Sintaxis
=PI ()
Devuelve el valor 3.141592……. CONCATENAR Sintaxis =CONCATENAR (texto1; texto2; texto3;…) La función concatenar se utiliza para unir dos o más texto, también suele utilizarse el símbolo &. Ejemplo:
También podría haber utilizado en A10: = D5 & “ “& A5 En la columna Juegos, Archivo Medallero.xls se desea, para cada competencia olímpica, mostrar en forma unificada la información actualmente disponible en las columnas K y L. La función necesaria es = concatenar(K3; L3) , Luego se arrastra para copiar en el resto de las celdas pertenecientes a dicho rango. PRODUCTO Sintaxis =PRODUCTO (número1; número2; número3;….) POTENCIA Sintaxis
=POTENCIA (base; exponente)
NO Sintaxis =No (celda) Esta función devuelve el valor opuesto al de celda. Ejemplo:
Nompropio Sintaxis: =Nompropio (texto) Convierte en mayúscula la primera letra de cada palabra.
Prof. Lloret Viviana
EXCEL
SI ANIDADOS Sintaxis
=SI (Condición; valor si V; Si (condición; valor si V; valor si F))
Ejemplo: En la siguiente planilla figuran los promedios de los alumnos de Informática de tercer año, se requiere que en la celda figure aprobado, si el promedio es mayor o igual a 6, a diciembre, si el promedio es menor que 6 pero mayor o igual a 4, y a marzo si el promedio es menor que 4. ESBLANCO Sintaxis:
=esblanco (referencia)
CONTAR.BLANCO Sintaxis: =Contar.blanco (rango) Cuenta la cantidad de celdas que no contienen información en un determinado rango.
ESERROR Sintaxis:
=eserror (referencia)
Prof. Lloret Viviana
EXCEL
Para hallar valores acumulados Para hallar valores acumulados Ejemplo: La columna “Total Acumulado” H8:H17 muestra el costo acumulado de las tareas que anteceden la fila incluyendo el costo de la tarea de dicha fila. Ej.: El total acumulado para la tercera tarea es la suma de los valores dispuestos en la columna “Total Tarea”, (F8:F17) correspondientes a las tareas 1, 2 y 3. En la celda H8 repetir el valor de la celda F8. Para ello: En H8 escribo =F8 En H9 escribo =H8 + F9* En H10 escribo =H9 + F10
…………………………………..
* A partir de esta celda se puede utilizar la técnica de arrastrar, hacia abajo, desde el ángulo inferior derecho . EXTRAE Sintaxis =EXTRAE (texto; posición_inicial; número de caracteres) Devuelve los caracteres de una cadena de texto dado la posición inicial y la cantidad de caracteres. En la celda B39 se utilizó: =EXTRAE (A39; 1; 1) En la celda C39 se utilizó: =EXTRAE (A39; 1; 3) En la celda D39 se utilizó: =EXTRAE (A39; 4;3) , a partir de la cuarta letra extraigo 3. Ejercicios
Consignas: Un importador desea controlar las existencias de mercaderías y sus vencimientos a partir dela presente planilla, Se requiere: Teniendo en cuenta la fecha actual, indicar con la leyenda “VENCIDO” al artículo cuya fecha de vencimiento se halla superado. En los demás casos indicar “OK”. Si el stock actual de un artículo es inferior la stock mínimo correspondiente, deberá aparecer un cartel “REPONER” con fuente color azul sobre un sombreado rojo. En los demás casos, la celda del artículo debe permanecer en blanco. Si el stock actual de un artículo es igual o mayor a un 50% por encima del stock mínimo recomendado, se deberá activar un alerta de stock alto, por lo que se debe i ndicar “OFERTAR” en fuente color rojo, sombreado de celda amarillo. En los demás casos, la celda debe permanecer vacía. Insertar en el título principal un comentario que indique:”Verificar con el stock real”. Ordenar los artículos de la planilla en forma alfabética ascendente por el origen, y l uego descendente por el ID.Artículo. Calcular cantidad de artículos vendidos. Calcular cantidad de artículos a reponer. Calcular cantidad de artículos de Argentina. Calcular cantidad de artículos in ninguna alerta de stock. Calcular cantidad de artículos sin código i ndicado. Configurar la hoja para i mpresión, con un encabezado de página que en el centro de la hoja indique automáticamente la fecha de impresión.
Ejemplo: FORMATO CONDICIONAL Para resaltar algún resultado especial en un rango de celdas, se utiliza Formato Condicional: clic en FORMATO / FORMATO CONDICIONAL: Se selecciona rango, escribe la condición y se establece un formato, si se necesita más de un criterio, clic en agregar
En el archivo archivo_guia.xls encontrarás los siguientes ejercicios:
Ejemplo: FORMATO CONDICIONAL Para resaltar algún resultado especial en un rango de celdas, se utiliza Formato Condicional: clic en FORMATO / FORMATO CONDICIONAL: Se selecciona rango, escribe la condición y se establece un formato, si se necesita más de un criterio, clic en agregar
En el archivo archivo_guia.xls encontrarás los siguientes ejercicios:
Para calcular la edad de los empleados se utilizó: =Entero ((Hoy ()-B5)/360) Aclaración: Se utiliza entero para que el resultado no sea, por ejemplo, 47.45 sino 47. Si utilizáramos formato número sin decimales, solamente, nos quedaría 48. Debemos dividir por 360 porque al restar dos fechas obtenemos como resultado la cantidad
Para calcular años Antigüedad =dias360 (C5; hoy (); 1)/360 (También se podías haber restado a la fecha actual la fecha de ingreso) Para calcular monto antigüedad =SI (E5>20; D5*50%; Si (E5>10; D5*25%; D5*10%))
FILTRO Filtrar Empleados de acuerdo antigüedad: DATOS/FILTRO/ AUTOFILTRO / PERSONALIZAR 1) Seleccionar: mayor o igual que 20 2) Seleccionar mayor o igual que 10 y menor que 20 3) seleccionar menor que 10.
LIQUIDACION DE COMISIONES DE LOS VENDEDORES Fecha actual
16/06/2007 Porcentajes de ventas por vendedor respecto del total de cada mes
Nro. De legajo 3311 725 231 564 891 689 Totales PORCENTAJE COMISION PROMEDIO DE LASCOMISIONES PAGADAS PROMEDIO DE LAS VENTAS EFECTUADAS VENTA MAXIMA DEL TRIMESTRE VENTA MINIMA DEL TRIMESTRE MAXIMA COMISION PAGADA MINIMA COMISION PAGADA CANTIDAD DE VENDEDORES
Apellido y Ventas de Ventas de nombre Mayo Junio Gómez Raúl $7.500,00 $8.000,00 Pérez Hernán $8.900,00 $7.400,00 Martínez José $14.700,00 $8.900,00 Gaspar Martín $7.500,00 $4.700,00 Álvarez Adriana $12.300,00 $7.600,00 Rodríguez Ana $9.800,00 $8.900,00 $60.700,00 $45.500,00 6% $1.527,68 $8.850,00 $14.700,00 $4.500,00 $1.936,68 $1.272,00 6
Ventas de Julio $6.500,00 $5.990,00 $7.000,00 $9.000,00
Importe comisión $1.320,00 $1.337,40 $1.836,00 $1.272,00
Premio
Mayo
$0,00 $0,00 $1.000,00 $0,00
12,36% 14,66% 24,22% 12,36%
17,58% 16,26% 19,56% 10,33%
13,96% 12,86% 15,03% 19,33%
$24.400,00 $1.464,00
$0,00
20,26%
16,70%
9,66%
$13.578,00 $32.278,00 $1.936,68 $46.568,00 $152.768,00 $9.166,08
$1.000,00
16,14% 100,00%
19,56% 100,00%
29,16% 100,00%
$4.500,00
Total del vendedor $22.000,00 $22.290,00 $30.600,00 $21.200,00
Junio
Julio
PROMEDIO DE LAS COMISIONES PAGADAS "=Promedio(G7:G12)" PROM EDIO DE LAS VENTAS EFECTUADAS "=Promedi o(C7:E12)" VENTA MAXIMA DEL TRIMESTRE "=MAX(C7:E12)" VENTA M INIM A DEL TRI MESTRE "=M IN(C7:E12)" MAXIMA COMISION PAGADA "=MAX(G7:G12)" MINIMA COMISION PAGADA "=MIN(G7:G12)" C ANTIDAD DE VENDEDORES "=CONTAR(C7:C12)" FECHA ACTUAL "=HOY()" PORCENTAJE POR VENDEDOR RESPECTO DEL TOTAL Ejemplo en H7 "=C7 / $C$13" (Aplicar al rango H7:J12 Formato porcentaje, con 2 decimales) Premio= $ 1000 para aquellos vendedores cuyo total trimestral supera los $25.000,00 "=SI(F7>25000;1000;0)
Gráficos en Excel Ejemplos Cuadro de deudores Morosos Unidad 1A 1C 2B
Abril Mayo Junio $ 235,00 $ 125,00 $ 200,50 $ 120,00 $ 80,00 $ 50,00 $ 70,00 $ 50,00 $ 95,50 $ 425,00 $ 255,00 $ 346,00
Ventas por Rubros
Seleccionar rango de datos a graficar (E23:H26, ver ejemplo página 9, Práctica Excel), Clic en Insertar / Gráfico, se abre el asistente para gráficos, elegir Columnas, /siguiente/ elegir Serie en :Columnas/ Siguiente, en Título escribir Deudores Morosos, en Leyenda clic en abajo / siguiente, clic en Como objeto en hoja 1/ Terminar.
Seleccionar rango de datos a graficar, Clic
Gráficos en Excel Ejemplos Seleccionar rango de datos a graficar (E23:H26, ver ejemplo página 9, Práctica Excel), Clic en Insertar / Gráfico, se abre el asistente para gráficos, elegir Columnas, /siguiente/ elegir Serie en :Columnas/ Siguiente, en Título escribir Deudores Morosos, en Leyenda clic en abajo / siguiente, clic en Como objeto en hoja 1/ Terminar.
Cuadro de deudores Morosos Unidad 1A 1C 2B
Abril Mayo Junio $ 235,00 $ 125,00 $ 200,50 $ 120,00 $ 80,00 $ 50,00 $ 70,00 $ 50,00 $ 95,50 $ 425,00 $ 255,00 $ 346,00
Ventas por Rubros Habitaciones Restaurante Bar y Cafetería Frigobar
81% 8% 10% 1%
Seleccionar rango de datos a graficar, Clic en Insertar / Gráfico, se abre el asistente para gráficos, elegir Circular / siguiente en Rango de datos, Serie en Columnas / siguiente / en Título escribir Ventas por Rubro, en Leyenda sacar clic en Mostrar Leyenda, en Rótulos de datos clic Nombre categoría, y en Porcentaje, clic en Mostrar líneas guías / siguiente /Como objeto en Hoja 1/ Finalizar.
Filtrar una lista o planilla Filtrar significa ocultar los registros que no cumplen con una cierta condición. Para ello clic en alguna celda de la tabla, para que Excel la reconozca, Clic en Datos/ Filtro/ Autofiltro. Para eliminar filtro: Datos / Filtro / Autofiltro / sacar tilde. Ejemplo:
En la figura tenemos un listado de Temas musicales, junto a su autor, Intérprete, Duración, etc. Aplicamos un autofiltro, con lo cual en la primera fila aparecen unas flechas,(figura inferior) hacemos clic en Intérprete y elegimos Páez Fito
Tema
Autor
Intérprete
Volumen Nº
Dame un talismán
Páez F.
Páez F.
1
Tres agujas
Páez F.
Páez F.
2
11 y 6
Páez F.
Páez F.
5
Rojo como un corazón
Páez F.
Páez F.
7
Bailando hasta que se vaya la noche
Páez F.
Páez F.
9
Cable a tierra
Páez F.
Páez F.
9
Gente sin swing
Páez F.
Páez F.
11
Duración (seg)
200 137 177 196 248 200 255
Lista filtrada por intérprete (Fito Paez)
Ordenar Para ordenar clic en alguna celda de la tabla, para que Excel la reconozca, Clic en Datos/ Ordenar (Se puede ordenar teniendo en cuenta tres criterios, en este caso, elegimos por autor.
Lista ordenada por autor
E
jer citación
EJERCICIO Nº 1 Confeccionar la siguiente planilla: CANTIDAD 20 20 20 20 1
ARTICULO remeras short medias zapatillas botiquín
PRECIO UNITARIO 10 35 7 50 25 SUBTOTAL DTO. 20% TOTAL
TOTALES
Calcular TOTALES, SUBTOTALES y DESCUENTO. • El trabajo debe figurar en una hoja llamada FACTURA • Centrar títulos y cambiarles tipo y tamaño de letra. • Colocar formato de moneda donde sea necesario. • Subrayar el Total General. •
RECORDAR Para calcular un porcentaje debemos seguir los siguientes pasos: 1. Primero ubicamos en la celda donde deberá figurar el resultado. 2. Para empezar la fórmula colocar el signo Igual. 3. Escribir la dirección de la celda que contiene el valor del que se desea calcular el porcentaje. 4. A continuación el signo *.
5. Luego el valor del porcentaje y el signo %. 6. Una vez realizados todos los pasos anteriores, apretar Enter.
EJERCICIO N° 2 • Se desea hacer la liquidación de sueldos de la empresa AARON S.R.L. Sólo se ingresarán el SUELDO BRUTO ,las AUSENCIAS de cada empleado y los RETIROS A CUENTA. El resto de los cálculos deberán ser efectuados por el programa. • La planilla de sueldos deberá armarse en la hoja1 (SUELDOS) y los recibos en la hoja2 (RECIBOS). PLANILLA DE SUELDOS DEL MES DE AGOSTO DE 2000 Sueldo Básico Premio (10%) Ausencias Dto. P/Ausencias (10%) SUELDO BRUTO Dto. Jubilación (15%) Dto. O. Social (3%) Anticipos SUELDO NETO
Rodriguez 960 1
Aulone 1530 2
Lopez 1200 0
Hans 1050 0
100
500
200
200
TOTAL DE SUELDOS
RECIBO DE SUELDOS AARÓN S.R.L.
Rawson 213 - Capital
BENEFICIARIO: TAREA:
FECHA DE INGRESO: PERÍODO DE LIQUIDACIÓN:
DETALLE
REMUNERACION
DESCUENTOS
Sueldo Básico Premio Total Descuentos Anticipos SUELDO A COBRAR EJERCICIO N° 3 La siguiente tabla muestra la cantidad de pasajeros que viajaron durante el año 2000 por la Empresa "LINE ALL”.
LINE ALL Primera Ejecutivo Turista • • • • •
1º Trimestre 987 1210 3275
2º Trimestre 1007 986 3284
3º Trimestre 870 1390 2876
4º Trimestre 1135 2358 3409
Calcular el total de pasajeros en cada clase. Destacar las celdas que contienen estos resultados con un borde rojo. La cantidad promedio de pasajeros que viajaron en cada trimestre. El porcentaje de pasajeros que viajan en cada clase, con detalle trimestral y anual. El máximo número de pasajeros que viajaron en primera clase. La menor cantidad de pasajeros que viajaron durante el último trimestre.
•
•
Graficar los datos de cada uno de los trimestres. Trimestre 1y2: Gráfico de Barras. Llamar a la hoja "1y 2º TRIMESTRE" Trimestre 3: Gráfico de Columnas. Llamar a la hoja "3º TRIMESTRE" Trimestre 4: Gráfico Circular. llamar a la hoja "4to. Trimestre” Realizar un gráfico circular de los totales de pasajeros. Llamar a la hoja "TOTAL ".
EJERCICIO N° 4 • Ingresar las ventas realizadas por seis vendedores de una empresa durante los primeros meses del año, siguiendo la siguiente estructura: NOMBRE
ENERO
FEBRE-
MARZO
ABRIL
MAYO
JUNIO
RO
Vendedor 1 Vendedor 2 Vendedor 3 Vendedor 4 Vendedor 5 Vendedor 6 • • • • • • • • • • •
Agregar cifras y nombres en cada caso. ¿Cuál es el monto mensual de ventas de la Empresa? ¿Cuál es el total de ventas de cada vendedor? ¿Qué vendedor tiene mejor promedio de ventas? ¿Cuál de todas las cifras es la mayor y cuál es la menor? ¿Cuál es la comisión que le corresponde a cada vendedor, calculada sobre un 3%? ¿Cuál es el total de comisiones que debe pagar la Empresa? Modificar la fuente, el tamaño, el color de las letras y el color de fondo de la línea de títulos. Centralizarlos y colocarlos en negritas. Configurar todas las cifras con dos decimales. Establecer en la columna COMISIONES formato de moneda. Colocar bordes en la tabla para una mejor presentación.
EJERCICIO N°5
Consorcio Pedro Goyena 1504 Gastos del mes de Sueldos Cargas Sociales Gas Luz Alquiler Portería Seguro Calderas Desinsección Reparación buzón
Jul-05 1.825,26 777,66 512,85 125,33 320 250 85 17
Abono ascensores TOTAL
250
Facturación por unidad UF 1 2 3 4 5 6 7 8 9 10 11 12 13 14
Importe del Saldo Antemes rior 2,20% $45,81 8,00% 7,80% 7,80% 7,80% $162,40 7,80% 7,30% 7,90% $303,98 8,20% 7,90% 8,20% 8,20% 3,60% $341,46 7,30% $149,91
Propietario
%
María Ovejero Juan Pérez Sol López Jorge Fernández Mario Sogari Nélida García Lucía Ferraro Carmen López Hernán Crespo Matías Domingo Néstor Campos Isabel Gómez Carlos Ferraro Rafael Paz TOTAL Intereses Punitorio
Punitorio
Importe a pagar
3,00%
Ejercicio 6) Cree los siguientes libros e inserte la cantidad de hojas que se especifican en el siguiente cuadro. Mantenga el nombre que se especifica para cada hoja del libro: Crear Libros Cambiar nombre a las hojas e insertar las necesarias LIBRO Hoja-1 Hoja-2 Hoja-3 Hoja-4 Academia Personal Asesoría Comunidad
Alumnos Bancos Luz 1-A
Clientes Diario Agua 1-B
Recibos Proveedores Teléfono 1-C
Caja Clientes Alquileres 2-A
Hoja-5
Hoja-6
Profesorado Préstamos 2-B
Impuestos 2-C
b) En el libro Academia, alterne el orden en que se muestran la hoja Clientes y Recibos. Es decir el nuevo orden deberá ser: Academia - Alumnos – Recibos- Clientes- Caja - Profesorado
c). En el libro Asesoría oculte la hoja “Préstamos”
Ejercicio 7: Realice los cálculos necesarios para generar la liquidación de sueldos del personal de su empresa a). Calcule el valor de la retención de la obra social, sindicato y jubilación. Para realizar dichos cálculos, tome el valor del sueldo básico y los porcentajes correspondientes que se encuentran en la parte superior de cada título, por ejemplo el 3% del sueldo básico para calcular la obra social. Utilice fórmulas a su criterio aplicando referencias relativas, absolutas o mixtas. b). Calcule el total de las retenciones de los empleados utilizando la función suma sobre Obra Social, Sindicato y Jubilación. c). Calcule el sueldo final realizando el cálculo de Sueldo Básico – Total Retenciones d). Copie las fórmulas para todos los empleados de la empresa. e). Calcule el total de la liquidación utilizando la función suma sobre Sueldo Final. f). Aplique formato a las celdas de números para que tengan formato Moneda con 2 decimales.(Verifique los resultados obtenidos con la planilla.
Ejercicio 8) Realice los cálculos necesarios para calcular las ventas de su empresa y las ganancias obtenidas en el primer semestre. a). Realice una fórmula que permite calcula las ventas del mes de febrero teniendo en cuenta que aumentan un x% en relación a las del mes de enero. Los valores porcentuales que se encuentran debajo de cada mes corresponden siempre a un aumento de las ventas en relación al mes de enero. Debe realizar el cálculo de manera tal que sirva para todos los meses. Debe aplicar referencias mixtas b). De la misma manera que en punto anterior calcule el costo para todos los meses. c). Calcule el total de las ventas y de los costos del semestre. d). Calcule la ganancia realizando una fórmula que realice la resta entre las ventas y el costo. Copie la fórmula para todos los meses. e). Calcule el total de las ganancias. f). Aplique formato a las celdas de números para que tengan formato Moneda con 2 decimales.
Ejercicio 9: Realizar un gráfico de columnas para mostrar las ventas registradas de caballos. 1. Realice un gráfico de columnas tomando los valores de la tabla del Archivo “VentasGráfico.xls” 2. Agregar como Título del Gráfico “Ventas 2004”, en el eje X “Países”, en el eje Y “Cantidad”. 3. Mostrar la leyenda a la derecha del gráfico.
Ejercicio 10: Se debe calcular el porcentaje de hombres y mujeres que cursan en
las Carreras de ingeniería de la Universidad Tecnológica Nacional. 1. Calcular el valor de la columna Total (Repiten + Nuevos). Para ello realice una fórmula que sume las columnas Total de Repiten + Total de Nuevos. 2. Copie la fórmula para todas las carreras. 3. Calcular el porcentaje de hombres y mujeres. El porcentaje de mujeres se calcula dividiendo el total de mujeres entre el total de alumnos. Expresar los valores en Porcentaje. 4. Copie el resultado para todas las carreras.
Ejercicio 11: Realizar un Gráfico de Torta representando el porcentaje de cada sector
SECTOR
SUELDO FINAL
ADMINISTRACION
15500
GERENCIA
38000
INFORMATICA
12000
Ejercicio 12: Usted está llevando el control de gastos de su viaje en la planilla via je.xls Deberá realizar los cálculos que le faltan a la misma: 1. El total de gatos realizado por día. Se entiende por gasto, los conceptos de alojamiento, transporte, comidas y regalos. 2. Cual es el total del gasto agrupado por concepto. Ej. El total del gasto correspondiente al concepto “Alojamiento” es la suma de cuanto se gastó en alojamiento en el transcurso de todos los días. 3. Se requiere saber cual es el valor medio, mínimo y máximo, correspondientes los conceptos “comidas” y “regalos”.
Ejercicio13:Dada la siguiente planilla, se pide: VENTAS DE PRODUCTOS ENLATADOS BUENOS AIRES CORDOBA SANTA FE SAN LUIS LA PAMPA LA RIOJA CHUBUT TIERRA DEL FUEGO TOTAL DE VENTAS % DE LAS VENTAS
$ $ $ $ $ $ $ $
JUAN 15.000,00 20.000,00 35.000,00 5.000,00 8.000,00 3.000,00 2.000,00 1.500,00
$ $ $ $ $ $ $ $
LUIS 2.000,00 3.500,00 3.500,00 3.600,00 6.000,00 10.000,00 6.000,00 1.500,00
$ $ $ $ $ $ $ $
LAURA 5.000,00 65.000,00 32.000,00 2.000,00 2.000,00 3.500,00 7.000,00 5.000,00
CAROLINA $ 8.000,00 $ 6.000,00 $ 10.000,00 $ 35.000,00 $ 4.100,00 $ 6.500,00 $ 1.500,00 $ 3.500,00
TOTAL VTAS $ 30.000,00 $ 94.500,00 $ 80.500,00 $ 45.600,00 $ 20.100,00 $ 23.000,00 $ 16.500,00 $ 11.500,00
CANTIDAD DE PROVINCIAS EVALUADAS PROMEDIO VENTAS SANTA FE PROMEDIO VENTAS CORDOBA PROMEDIO VENTAS SAN LUIS MAXIMA VENTA LA PAMPA MINIMA VENTA DE JUAN PROMEDIO DE VENTAS DE BUENOS AIRES Y CORDOBA
Copiar la hoja “Ventas 2004” y cambiarle el nombre a la hoja copiada por “Ventas 2005” Eliminar las hojas 2 y 3 Aplicar relleno de celdas con colores negro y grises. Cambiar la fuente a fuente Verdana de 10 puntos. Aplicar bordes a toda la tabla. Fuente en negrita para el “Total de Ventas”. Realizar la suma de las ventas por provincia y por vendedor. El texto “Ventas de Productos Enlatados” debe estar combinado en relación a la tabla. Calcular la cantidad de provincias evaluadas Calcular el promedio de ventas para la provincia de Santa Fe, Córdoba y San Luis Calcular la máxima venta en la provincia de La Pampa y la mínima venta de Juan. Calcular el promedio de ventas de Buenos Aires y Córdoba. Aplicar formato condicional a las ventas, para que se muestren en rojo y negrita las ventas entre 500 y 3000 y en amarillo y negrita las ventas entre 3001 y 5000. A todos los datos resultantes aplicarle un formato numérico con 2 (dos) decimales. Ocultar las ventas de la provincia de Tierra del Fuego. Calcular el porcentaje del total de las ventas de cada vendedor en relación al total de las ventas del año. Utilizar referencias relativas y absolutas. En la hoja “Ventas 2005”, modificar las ventas de Luis sabiendo que serán un 10% más que las de Juan. Las ventas de Laura serán un 15% menos que las de Luis. Las ventas de Carolina serán un 5% más que las de Juan. Calcular el porcentaje del total de las ventas de cada vendedor en “Ventas 2005” en relación al total de las ventas del año. Utilizar referencias relativas y absolutas. Ejercicio Nº 14: Dadas las siguientes planillas se pide: En la hoja Clientes: Clasificar la base de datos por Apellido y nombre en forma ascendente. Filtrar la base de datos para que sólo veamos a los Gerentes Administrativos.
En la hoja Cuadros: Calcular Totales de Rubros 1. Activo 2. Pasivo 3. Patrimonio Neto 4. Utilidad Bruta= Ventas – Costos 5. Utilidad Op. Ordinarias = Ut. Bruta – gastos 6. Utilidad Neta = ut. Op. Ordinarias – imp. Ganancias Porcentajes de columnas Participación sobre Total 1. de cada línea que compone el activo sobre el Total Activo 2. de cada línea que compone el Pasivo sobre el Total Pasivo 3. de cada línea que compone el Patrimonio Neto sobre el Total Patrimonio Neto. 4. del Total Pasivo y Total Patrimonio Neto respecto de la suma de ambos. 5. de cada línea del Resultado del Ejercicio respecto de las Ventas. Formato: Ancho columna A: 25 y de B a C: 11. TNR 16, Negrita, Centrado entre columnas: La Suisse Towers Hotel. Arial 12, negrita, centrar entre columnas: Estado Patrimonial y Resultados del Ejercicio. Letra color azul. Arial 10, Negrita, Cursiva: títulos de rubros (Activo, Activo Corriente, Activo no corriente, Pasivo, Pasivo Corriente, Pasivo no corriente, patrimonio Neto). Importes: con separador de miles, sin decimales. Porcentaje: estilo porcentual con dos decimales. En la hoja Ventas: Gráfico Circular con título, rótulo, y porcentaje de las Ventas por Rubros. Gráfico Circular 3D con título, rótulo y porcentaje de las Ventas de Habitación por tarifa. Hoja “Cuadros” LA SUISSE TOWERS HOTEL ESTADO PATRIMONIAL Rubros Activo Activo Corriente Disponibilidades Créditos Bienes de Cambio Activo No Corriente Bienes de Uso Total Activo Pasivo Pasivo Corriente Deudas comerciales Deudas Bancarias Deudas Fiscales Deudas Previsionales
Importes 350 57 65 9150
27 310 150 380
Partic s/Total
Pasivo No Corriente Deudas Previsiones Total Pasivo Patrimonio Neto Capital Reservas Resultados Total Patrimonio Neto
4438 35 4050 150 82
RESULTADOS DEL EJERCICIO Rubros Ventas Costos Utilidad Bruta Gastos Comerciales Gastos Administrativos Gastos Financieros Utilidad Op. Ordinarias Imp. Ganancias Utilidad Neta
Importes % Partic s/Vtas 5222 194 335 1090 186 2342
Hoja de cálculo“Clientes” RAZON SOCIAL Elevado Torre Hotel Hotel Aconcagua
COD. DIRECCION POSTAL LOCALIDAD PROV. Suipacha 959 1014 Buenos Aires Sarmiento 450 6080 Plumerillo Mendoza
Hotel El Hermitaneo Hotel El Hermitaneo Hotel El Hermitaneo Hotel El Hermitaneo Hotel Presidente Hotel Presidente
Pte Alvear 1010 Pte Alvear 1010 Pte Alvear 1010 Pte Alvear 1010 Callao 1254 Callao 1254
5050 Córdoba 5050Córdoba 5050Córdoba 5050 Córdoba 3300 Rosario 3300 Rosario
Hoja de cálculo “Ventas” VENTAS POR RUBROS Habitaciones
4254
Córdoba Córdoba Córdoba Córdoba Santa Fé Santa Fé
APELLIDO Y NOMBRES PUESTO Campos, Julian Gerente Administrativo Moreno, Mario Gerente Administrativo Bizarro, Clemente Director General Gimenez, Ana Gerente Recursos Hum. Gomez, Juan Gerente Administrativo Roma, Pedro Gerente Ventas Blanco,Carlos Gerente Ventas Diaz, Paula Gerente Administrativo
Restaurante Bar y Cafetería Frigobar
428 502 38
VENTAS DE HABITACION POR TARIFA Full Rate 850,8 Corp Rate 2127 Convenio 1276,2
Ejercicio Nº15: Aplicando la función =SI y teniendo en cuenta el sueldo completar la columna préstamo. NOMBRE Juan María Jose Ana Carlos Jorge Beatriz Claudia
SUELDO PRESTAMO 1.250 1.800 1.500 2.000 1.750 3.000 1.990 2.100
CONDICION menor o igual que mayor que
SUELDO PRESTAMO 2.000 10.000 2.000 30.000
