1. Excel. Funciones Autor: Pablo Biaggioni [http://www.mailxmail.com/curso-excel-funciones] Descubre miles de cursos como ste en www.mailxmail.com 1 mailxmail - Cursos para compartir lo que sabes

2. Presentacin del curso Con este curso aprenders la creacin de planillas en Excel utilizando las funciones que te ayuden a simplificar tu trabajo como, por ejemplo, la funcin suma. Aprenders tambin a convertir datos en grficos y a vincular hojas entre s. Aprenders a utilizar la funcin (=), la funcin K.esimo.mayor, las funciones con fechas, e introduciremos el concepto de funcin anidada. Tambin veremos la funcin promedio, la funcin BUSCARV y referencias absolutas, as como la funcin Y, la funcin Contar.si. Y te ensearemos a utilizar la ayuda en Excel, que puede ser de utilidad cuando te encuentres con problemas o funciones que no entiendas. Visita ms cursos como este en mailxmail: [http://www.mailxmail.com/cursos-informatica] [http://www.mailxmail.com/cursos-ofimatica] Tu opinin cuenta! Lee todas las opiniones de este curso y djanos la tuya: [http://www.mailxmail.com/curso-excel-funciones/opiniones] Cursos Valoracin Alumnos Vdeo Power point y sus mens Power point es un programa que nos permite realizar presentaciones para nuestras reuniones o proyectos con variedas de objetos, tanto de texto, grficos, imgenes y sonid... [23/03/05] 7.340 Introduccin a Microsoft Office Word Te mostraremos de una forma muy fcil y rpida la manera de crear documentos, guardarlos, insertar columnas y tablas, alinear, justificar, centrar textos, etc. descubrir... [04/10/05] 3.093 Cmo eliminar los ojos rojos con PhotoShop En esta prctica conoceremos los pasos necesarios para poder eliminar los ojos rojos de una fotografa.... [26/05/08] 739 Planificacin con MS Project Introdcete, paso a paso, en la planificacin que necesitars para conocer todo el programa de ms project. te servirn de ayuda las imgenes escogidas y las marcas que la... [23/06/06] 2.096 Microsoft Office El microsoft office posee 5 programas o paquetes: word, excel, power point, access y outlook. todos ellos estan formados por unas caractersticas determinadas y que ayuda... [13/06/06] 11.745 Cursos similares Descubre miles de cursos como ste en www.mailxmail.com 2 mailxmail - Cursos para compartir lo que sabes 3. 1. Excel. Introduccin. Funcin sumatoria [http://www.mailxmail.com/curso-excel-funciones/excel-introduccion-funcion-sumatoria] Excel basado en problemas Introduccin Este curso est dirigido a personas con conocimientos bsicos de Excel y Word. El aprendizaje se adquiere mediante problemas de los que se plantea una posible solucin. En todos los casos, queda por cuenta del lector la realizacin de los ejercicios propuestos. Todos los nombres o marcas mencionadas son propiedad de sus respectivos dueos y no tienen ninguna relacin con el autor de este documento. Problema 1: Gastos Mensuales Este problema constituye un repaso de algunos conceptos bsicos que el lector debiera poseer. En caso de no poder resolver el problema se recomienda retomar este curso en otra oportunidad. a. Realiza la siguiente lista de gastos mensuales usando valores imaginarios: b. Guarda el libro y cierra el programa. c. Recupere el libro guardado y agregue una fila (encima de GAS) con el gasto de LUZ. Guarde nuevamente. d. En una hoja diferente obtenga series numricas usando el puntero de estirar. e. Coloque nombre a cada hoja y elimine las que no us. Guarde el libro. Problema 2: Sumatoria Continuando con lo creado en el ejercicio anterior se ver como usar la funcin autosuma: a. Abra el libro del problema anterior. Descubre miles de cursos como ste en www.mailxmail.com 3 mailxmail - Cursos para compartir lo que sabes 4. b. Obtenga el total usando una calculadora. Recuerde ese valor. c. Use la funcin auto-suma para obtener el monto total. Resolucin: Procedimiento para obtener el monto total: Seleccione las celdas donde se encuentran los valores a sumar y haga clic en Autosuma Descubre miles de cursos como ste en www.mailxmail.com 4 mailxmail - Cursos para compartir lo que sabes 5. 2. Excel. Clculos bsicos [http://www.mailxmail.com/curso-excel-funciones/excel-calculos-basicos] Problema 3: El Edificio En este ejemplo haremos algunos clculos bsicos que sern tiles en el problema 4. En el edificio COX II hay 24 departamentos. Cada departamento debe pagar el mismo monto de gastos de expensas. Usted debe calcular (y obtener el resultado en una nica celda) el dinero que debe abonar cada unidad en base a los siguientes datos: - Honorarios administrador general: $330. - Viticos: $120. - Mantenimiento del ascensor: $90. - Seguro: $112. - Sueldo del encargado: $1.000. Al monto total debe sumar el 10 % del total para cubrir gastos de mantenimiento. Resolucin: Crearemos una tabla para ingresar los datos y realizaremos la sumatoria de los gastos. A ese valor le sumaremos el 10% que vamos a calcular en otra celda. Luego dividiremos todo por el nmero de departamentos. Obtendremos una tabla similar a la siguiente: El 10% del total se calcula multiplicando por 10 y dividiendo por 100 el valor del total que en este caso se encuentra en la celda B7. El total a pagar por departamento se calcula como la sumatoria del total mas el 10%, valores que se encuentran en las celdas B7 y B9 respectivamente. Descubre miles de cursos como ste en www.mailxmail.com 5 mailxmail - Cursos para compartir lo que sabes 6. 3. Creacin de datos y vinculacin de hojas entre s (1/2) [http://www.mailxmail.com/curso-excel-funciones/creacion-datos-vinculacion-hojas-si-1-2] Problema 4: El Edificio - La Evolucin En este ejercicio realizaremos un grfico para evaluar la evolucin de datos. Cree cinco tablas como la del problema anterior en la misma hoja. Asigne valores arbitrarios a cada gasto y obtenga el total a pagar por departamento de cada mes. Luego obtenga una grfica como la siguiente: Resolucin: Simplemente se debe copiar y pegar cinco veces la tabla creada en el ejercicio anterior y cambia algunos valores segn su criterio. Luego en la misma hoja armaremos una tabla final como la siguiente: Los valores obtenidos para cada mes pueden colocarse all colocando =B10, donde B10 es la celda donde se halla el valor que quiero en la celda nueva. Para obtener la grfica selecciono la tabla final, ingreso en el men insertar y presiono en Grfico. En Tipo de Grfico elijo Columnas y presiono Siguiente. A continuacin paso a la pestaa Serie y hago clic en el primer (flecha roja). Al hacer clic se cerrar la ventana de grfico momentneamente y debemos seleccionar la columna de los meses y presionar la tecla Intro. Descubre miles de cursos como ste en www.mailxmail.com 6 mailxmail - Cursos para compartir lo que sabes 7. Despus haremos clic sobre el siguiente y al desaparecer la ventana de grfico seleccionaremos la columna de los montos y presionaremos intro. Proseguiremos con el tercer En este caso seleccionaremos ambas columnas y presionaremos intro. Continuamos presionando Siguiente, hacemos los retoques que creamos necesarios y presionamos siguiente nuevamente y culminamos apretando finalizar. Problema 5: El Edificio - Varias hojas En el presente ejercicio retomaremos el 4 para repasar la creacin de grficos y vincular hojas entre s. El trabajo realizado para la creacin del grfico es muy bueno, pero sus patrones desean que cada mes en una hoja diferente y el resumen del ao junto con la grfica en una hoja separada. Cada hoja debe tener el nombre correspondiente. Resolucin: Trasladamos las tablas con copiar y pegar, cada una a su hoja (las nombramos como los meses que les corresponden), e insertamos una hoja nueva con el nombre de Resumen. En esta ltima hoja creamos el grfico en base a una tabla que ser como esta: Hemos colocado la frmula en algunas celdas para que el lector se de cuenta de cmo debe proceder. La forma simple de obtener una frmula como la exhibida es colocando el signo = en la celda, luego presionamos sobre la pestaa de libro donde se encuentra el valor a obtener, presionamos sobre la celda que lo contiene y ENTER (intro). Descubre miles de cursos como ste en www.mailxmail.com 7 mailxmail - Cursos para compartir lo que sabes 8. 4. Creacin de datos y vinculacin de hojas entre s (2/2) [http://www.mailxmail.com/curso-excel-funciones/creacion-datos-vinculacion-hojas-si-2-2] Problema 6: Campeonato de Bolita Nos enfrentaremos a una de las funciones ms usadas: la funcin SI. La siguiente tabla fue creada para un campeonato de BOLITA (canicas). En cada fecha se llev a cabo un encuentro y cada jugador sum una cantidad de puntos. 1. Calcule la cantidad total de puntos sumado por cada jugador al final del campeonato. 2. Agregue una columna a la tabla, donde se indique el premio que gana cada competidor de acuerdo al criterio siguiente: Todos los jugadores que sumen al menos 170 puntos ganan $1000. Aquellos que sumen menos $200. 3. En otra hoja del mismo libro disee la siguiente tabla: 4. Complete la tabla segn el siguiente criterio: Se paga un adicional de $200 a quien haya sacado ms de 50 puntos el da 12 de Diciembre. El Total es la suma del Adicional ms el Premio (calculado en la otra hoja). Resolucin: Lo primero que hacemos es copiar la tabla en una hoja, calcular los puntos totales de cada jugador y agregar la columna Premio. En la segunda celda de la columna premio escribiremos =SI(H2>=170;1000;200), siendo H2 la celda donde se encuentra el Descubre miles de cursos como ste en www.mailxmail.com 8 mailxmail - Cursos para compartir lo que sabes 9. puntaje del jugador. Veremos la sintaxis de la funcin si: = SI (Qu se debe cumplir?, valor asignado si se cumple, si no se cumple) = SI El signo igual indica que colocaremos una funcin y SI es su nombre. H2> = 170 El valor de la celda H2 es mayor o igual que 170. 1000 Valor que figurar en la celda si H2 es mayor o igual a 170. 200 Valor que figurar en la celda si H2 es menor que 170. Desde el inciso 3 en adelante es posible resolver usando los conocimientos vistos hasta este punto. Es posible insertar la funcin SI (y todas las dems) de otra forma: 1. Ingrese en el men Insertar y presione Funcin. 2. Aparecer un cuadro de dilogo donde debe elegir la funcin a insertar y presionar, Aceptar. 3. Se mostrar otra ventana en donde debemos colocar los valores correspondientes a la funcin. En nuestro ejemplo se vera as: Descubre miles de cursos como ste en www.mailxmail.com 9 mailxmail - Cursos para compartir lo que sabes 10. Descubre miles de cursos como ste en www.mailxmail.com 10 mailxmail - Cursos para compartir lo que sabes 11. 5. Funcin = (igual) [http://www.mailxmail.com/curso-excel-funciones/funcion-igual] Problema 7: El Negocio Aprenderemos a usar la funcin = HOY () y haremos un repaso de lo aprendido. Usted es dueo de una tienda. Necesita disear en Excel una tabla que le facilite el clculo del dinero que ingresa y egresa. Tiene dos empleados; uno de los cuales es el encargado del cobro de los productos a los clientes (cajero/a); y por lo tanto es quien actualiza con cada venta los ingresos en una tabla como la siguiente: El otro empleado es quien se ocupa; entre otras cosas; del pago a los proveedores. Recolecta todos los comprobantes y actualiza con cada pago el dinero que egresa cargando los datos en una tabla como la siguiente: Disee las tablas anteriores de forma tal que se puedan agregar datos y se vean modificados los totales de ingreso y de egreso. Use una hoja diferente para cada tabla. Para una mejor organizacin usted debe crear una tabla como la siguiente, la cual actualizar al final de cada da. Use fechas a partir del da de hoy. En esta tabla el Saldo Inicial de cada da coincide con el Saldo Total del da anterior. El Saldo. Total de cada da es la sumatoria del Saldo Inicial de cada da y el Ingreso, menos el Egreso. Para facilitar el llenado de la tabla anterior, usted decide crear; en la misma hoja; una tabla donde pueda leer al final del da el total de ingreso de ese da y el total de egresos: Descubre miles de cursos como ste en www.mailxmail.com 11 mailxmail - Cursos para compartir lo que sabes 12. Excel permite insertar la fecha actual con la funcin =HOY(). Para establecer los valores de Ingreso y Egreso debe escribir el signo igual (=) y luego hacer clic con el ratn en la celda que tiene el valor que necesita, en las hojas de cada uno de sus empleados. Descubre miles de cursos como ste en www.mailxmail.com 12 mailxmail - Cursos para compartir lo que sabes 13. 6. Funcin K.esimo.mayor [http://www.mailxmail.com/curso-excel-funciones/funcion-kesimomayor] Problema 8: Funcin K.ESIMO.MAYOR 1. La siguiente tabla detalla los resultados preliminares de un concurso de pesca. Usted desea crear una tabla donde se vean los resultados parciales de cada prueba (1, 2, 3 y 4) y el puntaje total. Cree la tabla y calcule los Puntajes Totales. 2. Lo siguiente que desea es conocer los tres mximos puntajes. Para ello usar la funcin K.ESIMO.MAYOR. La sintaxis de la funcin es la siguiente: =K.ESIMO.MAYOR(Matriz ; k) 3. Por ltimo necesita que la celda del jugador que va liderando la competencia se pinte de rojo. Para ello: a. Seleccione la columna de los puntajes. b. Ingrese en Formato/Formato Condicional... c. Coloque los valores como en la imagen y acepte. (=$B$9 es la celda donde se calcul el puntaje del primero) Descubre miles de cursos como ste en www.mailxmail.com 13 mailxmail - Cursos para compartir lo que sabes 14. 7. Fechas: funciones [http://www.mailxmail.com/curso-excel-funciones/fechas-funciones] Problema 9 fechas: Veremos algunas funciones con fechas e introduciremos el concepto de funcin anidada. PARTE I: 1. Ingrese en la celda A1 la fecha de su nacimiento. 2. En la celda A4 aplique la funcin DIA, a la fecha del punto 1. 3. En la celda A2 aplique la funcin MES, a la fecha del punto 1. 4. En la celda A3 aplique la funcin AO, a la fecha del punto 1. 5. En la celda A5 aplique use la funcin FECHA para obtener nuevamente la fecha de su nacimiento, empleando los valores obtenidos en 2,3 y 4. Resolucin parte I: 10/05/1969. En esta celda escribiremos la fecha de nacimiento- =MES (A1). Esta es la frmula para obtener el mes, en nuestro caso 5 =AO (A1). Esta es la frmula para obtener el ao, en nuestro caso 1969 =DIA(A1). Esta es la frmula para obtener el da, en nuestro caso 10 =FECHA (A3;A2;A4). Esta es la frmula para obtener la fecha. PARTE II: 1. En otra celda obtenga la misma fecha ingresada en A1 en la parte I usando la funcin FECHA, anidada con las funciones AO, MES y DIA. Resolucin II: Una funcin anidada es una funcin en cuyo interior se coloca otra. En nuestro caso la sintaxis correcta de la funcin sera: =FECHA(AO(A1);MES(A1);DIA(A1)) PARTE III: 1. Calcule su edad usando (=AO(HOY()) - AO(fecha de nacimiento)) 2. Calcule cuantos das pasaron desde su nacimiento de forma tal que se actualice automticamente este dato usando (=HOY()-fecha nacimiento) 3. Calcule cuantos das faltan para su prximo cumpleaos. Resolucin parte III: Descubre miles de cursos como ste en www.mailxmail.com 14 mailxmail - Cursos para compartir lo que sabes 15. Resolveremos el punto 3: Asumimos que la fecha de cumpleaos es el 24/12/2008. Para obtener el valor de los das entre esas dos fechas la celda B1 debe tener un formato de celdas Genera otras posible solucin es: = ("24/12/2008")-HOY (). El formato de la celda debe ser General para que d el valor correcto. Descubre miles de cursos como ste en www.mailxmail.com 15 mailxmail - Cursos para compartir lo que sabes 16. 8. Funcin promedio [http://www.mailxmail.com/curso-excel-funciones/funcion-promedio] Problema 10: Exmenes Estudiaremos la funcin PROMEDIO y repasaremos parte de lo visto. Un instituto de idiomas ha tomado tres exmenes a sus alumnos. Calcule la nota final como el promedio de las notas de les tres exmenes: Cree y complete los valores de la siguiente tabla usando vnculos entre hojas: Docente de todos los alumnos mayores de 18 aos es Mnica. Docente de todos los menores de edad es Gladis. Todos los alumnos cuya nota final supere 6 promueven a un nivel siguiente. El resto permanecen en el actual. Use una funcin SI "anidada" con una funcin SUMA. Resolucin: Para el clculo de la nota final usaremos la funcin PROMEDIO con la siguiente sintaxis: =PROMEDIO (Primer Nmero; Segundo Nmero; Tercer Nmero), donde Primer Nmero, Segundo Nmero y Tercer Nmero pueden ser los valores o las celdas que contiene los valores a promediar. Se pueden promediar todos los nmeros necesarios. Otra forma de hacer lo mismo, que puede resultar mas simple cuando se trabaja con gran cantidad de datos, puede verse en este ejemplo hecho con los datos del problema: Descubre miles de cursos como ste en www.mailxmail.com 16 mailxmail - Cursos para compartir lo que sabes 17. D2 es la nota del primer examen y F2 la del ultimo. El clculo de la edad puede hacerse con la siguiente funcin: =HOY ()-C2 donde C2 es la celda que contiene la fecha de nacimiento del individuo. El formato de la celda debe ser Personalizado como yy. Para estableces el nivel al que promueve un alumno usaremos la funcin SI con una sintaxis similar a esta: =SI (NOTA>6;NivelActual +1;NivelActual), donde Nivel Actual es la celda que indica el nivel actual de ese alumno y NOTA es la celda que contiene la nota final del alumno. Para establecer el Docente de cada alumno usaremos, nuevamente, la funcin SI. Como puede verse en la imagen la sintaxis de la funcin podra ser: =SI(D2>=18;"Monica";"Gladis") donde D2 es la celda que indica la edad del alumno. Descubre miles de cursos como ste en www.mailxmail.com 17 mailxmail - Cursos para compartir lo que sabes 18. Descubre miles de cursos como ste en www.mailxmail.com 18 mailxmail - Cursos para compartir lo que sabes 19. 9. Funcin BUSCARV y referencias absolutas [http://www.mailxmail.com/curso-excel-funciones/funcion-buscarv-referencias-absolutas] Problema 11: BUSCARV y referencias absolutas Presentaremos la funcin BUSCARV e veremos la importancia de las referencias relativas y absolutas. Usted es responsable del pago del sueldo en una agencia de ventas. El sueldo se establece en base a la categora a la que pertenece cada vendedor. Todos los meses usted recibe los datos de la tabla de la derecha y debe obtener el sueldo de cada vendedor en base a los datos de ella. Para lograrlo construye una tabla como la de la izquierda. Calcule con una nica funcin el sueldo de cada empleado. Resolucin: Emplearemos la funcin BUSCARV que buscar en forma vertical (V) un valor determinado por usted (puede indicarse una celda) en una columna indicada. Su sintaxis general es del siguiente tipo: BUSCAR V(VALOR; RANGOBUSQUEDA; COLUMNA N; Ord) VALOR: El valor de que celda debo encontrar? RANGO BUSQUEDA: Dnde lo tengo que buscar? En que matriz? COLUMNA N: En que columna de esa matriz? En nuestro ejemplo la solucin para el primer vendedor podra ser: =BUSCAR V(B2;E1:F6;2) Sin embargo al estirar o copiar esa frmula en las celdas del resto de los vendedores los valores no sern correctos. Esto es porque E1 se transformar en E2 al copiar en la celda B3, y F6 en F7. De la misma forma ocurrir en el resto. La solucin al problema sera que la sintaxis de la funcin en la celda del sueldo del primer vendedor fuera algo como esto: =BUSCAR V(B2;E$1:F$6;2) Note que se han colocado signos $ de forma que al estirar (copiar) a las otras celdas no cambie el nmero de la fila de E1 a E2, ni de F6 a F7. Descubre miles de cursos como ste en www.mailxmail.com 19 mailxmail - Cursos para compartir lo que sabes 20. 10. Funcin Y [http://www.mailxmail.com/curso-excel-funciones/funcion] Problema 12: Funcin Y Emplearemos la funcin SI anidada con la funcin Y para resolver un problema. Una escuela prepara el viaje de fin de curso. Los alumnos se separarn en grupo de acuerdo a la edad y la nota final. Todo nio de 9 aos de edad y cuya calificacin final fuera mayor o igual a 7 rene las condiciones para integrar el Contingente 1; el resto integrar el Contingente 2. Arma la planilla de clculo como el modelo. Agrega dos alumnos ms anteriores a Jorge. Determina la formula que de cmo resultado el contingente al que pertenecer cada alumno. Resolucin: La funcin Y nos devuelve el valor VERDADERO si los datos lgicos que le Ingresamos se cumplen. En nuestro ejemplo, nosotros necesitamos saber si se cumple que: NOTA FINAL >=7 y EDAD >=9 Para que la funcin Y nos devuelva VERDADERO o FALSO, segn corresponda, debiramos escribir algo as: Y(NOTA FINAL >=7; EDAD >=9) La sintaxis de la funcin SI que usaremos anidada con la funcin Y se ve en la imagen (para el primer alumno de la lista: =SI(Y(B2>=7; C2>=9);"Contingente 1";"Contingente 2")). Descubre miles de cursos como ste en www.mailxmail.com 20 mailxmail - Cursos para compartir lo que sabes 21. Problema 13: Contar SI Continuamos usando el ejemplo anterior para ver la funcin CONTAR. SI. Empleando la lista de alumnos del problema 12 contar, por un lado, cuntos nios son menores de 9 aos y por otro cuantos obtuvieron una nota menor que 7. Resolucin: La funcin CONTAR. SI busca en una rango de datos especificado por el usuario, cuntas veces se cumple el criterio que se le indica. En nuestro ejemplo la funcin quedara de la siguiente forma para contar cuantos nios son menores de 9 aos: = CONTAR. SI (C2:C8;"