excel. funciones -...

Excel. FuncionesAutor: Pablo Biaggioni

1

mailxmail - Cursos para compartir lo que sabes

http://www.mailxmail.com


Presentación del curso

Con este curso aprenderás la creación de planillas en Excel utilizando las funcionesque te ayuden a simplificar tu trabajo como, por ejemplo, la función suma.Aprenderás también a convertir datos en gráficos y a vincular hojas entre sí.

Aprenderás a utilizar la función (=), la función K.esimo.mayor, las funciones confechas, e introduciremos el concepto de función anidada. También veremos lafunción promedio, la función BUSCARV y referencias absolutas, así como la funciónY, la función Contar.si. Y te enseñaremos a utilizar la ayuda en Excel, que puede serde utilidad cuando te encuentres con problemas o funciones que no entiendas.

2




1. Excel. Introducción. Función sumatoria

Excel basado en problemas

Introducción

Este curso está dirigido a personas con conocimientos básicos de Excel y Word. Elaprendizaje se adquiere mediante problemas de los que se plantea una posiblesolución. En todos los casos, queda por cuenta del lector la realización de losejercicios propuestos.

Todos los nombres o marcas mencionadas son propiedad de sus respectivos dueñosy no tienen ninguna relación con el autor de este documento.

Problema 1: Gastos Mensuales

Este problema constituye un repaso de algunos conceptos básicos que el lectordebiera poseer. En caso de no poder resolver el problema se recomienda retomareste 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 deLUZ. Guarde nuevamente.d. En una hoja diferente obtenga series numéricas 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 funciónautosuma:

a. Abra el libro del problema anterior.b. Obtenga el total usando una calculadora. Recuerde ese valor.c. Use la función auto-suma para obtener el monto total.

3




Resolución:

Procedimiento para obtener el monto total:

Seleccione las celdas donde se encuentran los valores a sumar y haga clic enAutosuma

4




2. Excel. Cálculos básicos

Problema 3: El Edificio

En este ejemplo haremos algunos cálculos básicos que serán útiles en el problema 4.

En el edificio COX II hay 24 departamentos. Cada departamento debe pagar elmismo monto de gastos de expensas. Usted debe calcular (y obtener el resultado enuna única celda) el dinero que debe abonar cada unidad en base a los siguientesdatos:

- Honorarios administrador general: $330.- Viáticos: $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.

Resolución:

Crearemos una tabla para ingresar los datos y realizaremos la sumatoria de losgastos. A ese valor le sumaremos el 10% que vamos a calcular en otra celda. Luegodividiremos todo por el número de departamentos. Obtendremos una tabla similar ala siguiente:

El 10% del total se calcula multiplicando por 10 y dividiendo por 100 el valor deltotal 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.

5




3. Creación de datos y vinculación de hojas entre sí( 1 / 2 )

Problema 4: El Edificio - La Evolución

En este ejercicio realizaremos un gráfico para evaluar la evolución de datos. Creecinco tablas como la del problema anterior en la misma hoja. Asigne valoresarbitrarios a cada gasto y obtenga el total a pagar por departamento de cada mes.Luego obtenga una gráfica como la siguiente:

Resolución:

Simplemente se debe copiar y pegar cinco veces la tabla creada en el ejercicioanterior y cambia algunos valores según su criterio. Luego en la misma hojaarmaremos una tabla final como la siguiente:

Los valores obtenidos para cada mes pueden colocarse allí colocando =B10, dondeB10 es la celda donde se halla el valor que quiero en la celda nueva.

Para obtener la gráfica selecciono la tabla final, ingreso en el menú insertar ypresiono en Gráfico. En Tipo de Gráfico elijo Columnas y presiono Siguiente. A

continuación paso a la pestaña Serie y hago clic en el primer (flecha roja). Alhacer clic se cerrará la ventana de gráfico momentáneamente y debemos seleccionarla columna de los meses y presionar la tecla Intro.

6




Después haremos clic sobre el siguiente y al desaparecer la ventana de gráficoseleccionaremos la columna de los montos y presionaremos intro.

Proseguiremos con el tercer En este caso seleccionaremos ambas columnas ypresionaremos intro.

Continuamos presionando Siguiente, hacemos los retoques que creamos necesariosy presionamos siguiente nuevamente y culminamos apretando finalizar.

Problema 5: El Edificio - Varias hojas

En el presente ejercicio retomaremos el 4 para repasar la creación de gráficos yvincular hojas entre sí. El trabajo realizado para la creación del gráfico es muybueno, pero sus patrones desean que cada mes en una hoja diferente y el resumendel año junto con la gráfica en una hoja separada. Cada hoja debe tener el nombrecorrespondiente.

Resolución:

Trasladamos las tablas con copiar y pegar, cada una a su hoja (las nombramoscomo los meses que les corresponden), e insertamos una hoja nueva con el nombrede Resumen. En esta última hoja creamos el gráfico en base a una tabla que serácomo esta:

Hemos colocado la fórmula en algunas celdas para que el lector se de cuenta decómo debe proceder. La forma simple de obtener una fórmula como la exhibida escolocando el signo = en la celda, luego presionamos sobre la pestaña de libro dondese encuentra el valor a obtener, presionamos sobre la celda que lo contiene y ENTER(intro).

7




4. Creación de datos y vinculación de hojas entre sí( 2 / 2 )

Problema 6: Campeonato de Bolita

Nos enfrentaremos a una de las funciones más usadas: la función SI. La siguientetabla fue creada para un campeonato de BOLITA (canicas). En cada fecha se llevó acabo un encuentro y cada jugador sumó una cantidad de puntos.

1. Calcule la cantidad total de puntos sumado por cada jugador al final delcampeonato.2. Agregue una columna a la tabla, donde se indique el premio que gana cadacompetidor de acuerdo al criterio siguiente: Todos los jugadores que sumen almenos 170 puntos ganan $1000. Aquellos que sumen menos $200.3. En otra hoja del mismo libro diseñe la siguiente tabla:

4. Complete la tabla según el siguiente criterio:

Se paga un adicional de $200 a quien haya sacado más de 50 puntos el día 12 deDiciembre. El Total es la suma del Adicional más el Premio (calculado en la otrahoja).

Resolución:

Lo primero que hacemos es copiar la tabla en una hoja, calcular los puntos totalesde cada jugador y agregar la columna Premio. En la segunda celda de la columnapremio escribiremos =SI(H2>=170;1000;200), siendo H2 la celda donde se

8




encuentra el puntaje del jugador. Veremos la sintaxis de la función si:

= SI (¿Qué se debe cumplir?, valor asignado si se cumple, si no se cumple)= SI El signo igual indica que colocaremos una función 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 vistoshasta este punto. Es posible insertar la función SI (y todas las demás) de otra forma:

1. Ingrese en el menú Insertar y presione Función.2. Aparecerá un cuadro de diálogo donde debe elegir la función a insertar y presionar, Aceptar.

3. Se mostrará otra ventana en donde debemos colocar los valores correspondientesa la función. En nuestro ejemplo se vería así:

9




1 0




5. Función = (igual)

Problema 7: El Negocio

Aprenderemos a usar la función = HOY () y haremos un repaso de lo aprendido.Usted es dueño de una tienda. Necesita diseñar en Excel una tabla que le facilite elcálculo del dinero que ingresa y egresa. Tiene dos empleados; uno de los cuales esel encargado del cobro de los productos a los clientes (cajero/a); y por lo tanto esquien 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 egresacargando los datos en una tabla como la siguiente:

Diseñe las tablas anteriores de forma tal que se puedan agregar datos y se veanmodificados los totales de ingreso y de egreso. Use una hoja diferente para cadatabla. Para una mejor organización usted debe crear una tabla como la siguiente, lacual actualizará al final de cada día. Use fechas a partir del día de hoy.

En esta tabla el Saldo Inicial de cada día coincide con el Saldo Total del día anterior.El Saldo. Total de cada día es la sumatoria del Saldo Inicial de cada día 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 día el total de ingreso de ese día y el total deegresos:

1 1




Excel permite insertar la fecha actual con la función =HOY(). Para establecer losvalores de Ingreso y Egreso debe escribir el signo igual (=) y luego hacer clic con elratón en la celda que tiene el valor que necesita, en las hojas de cada uno de susempleados.

1 2




6. Función K.esimo.mayor

Problema 8: Función 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 máximos puntajes. Para ello usará la función K.ESIMO.MAYOR.

La sintaxis de la función es la siguiente: =K.ESIMO.MAYOR(Matriz ; k)

3. Por último necesita que la celda del jugador que va liderando la competencia sepinte 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 secalculó el puntaje del primero)

1 3




7. Fechas: funciones

Problema 9 fechas:

Veremos algunas funciones con fechas e introduciremos el concepto de funciónanidada.

PARTE I:

1. Ingrese en la celda A1 la fecha de su nacimiento.2. En la celda A4 aplique la función DIA, a la fecha del punto 1.3. En la celda A2 aplique la función MES, a la fecha del punto 1.4. En la celda A3 aplique la función AÑO, a la fecha del punto 1.5. En la celda A5 aplique use la función FECHA para obtener nuevamente la fecha desu nacimiento, empleando los valores obtenidos en 2,3 y 4.

Resolución parte I:

10/05 /1969. En esta celda escribiremos la fecha de nacimiento-=MES (A1). Esta es la fórmula para obtener el mes, en nuestro caso 5=AÑO (A1). Esta es la fórmula para obtener el año, en nuestro caso 1969=DIA(A1). Esta es la fórmula para obtener el día, en nuestro caso 10=FECHA (A3;A2;A4). Esta es la fórmula para obtener la fecha.

PARTE II:

1. En otra celda obtenga la misma fecha ingresada en A1 en la parte I usando lafunción FECHA, anidada con las funciones AÑO, MES y DIA.

Resolución II:

Una función anidada es una función en cuyo interior se coloca otra. En nuestro casola sintaxis correcta de la función sería: =FECHA(AÑO(A1);MES(A1);DIA(A1))

PARTE III:

1. Calcule su edad usando (=AÑO(HOY()) - AÑO(fecha de nacimiento))2. Calcule cuantos días pasaron desde su nacimiento de forma tal que se actualiceautomáticamente este dato usando (=HOY()-fecha nacimiento)3. Calcule cuantos días faltan para su próximo cumpleaños.

Resolución parte III:

Resolveremos el punto 3:

1 4




Asumimos que la fecha de cumpleaños es el 24/12/2008. Para obtener el valor delos días entre esas dos fechas la celda B1 debe tener un formato de celdas Generaotras posible solución es: = ("24/12/2008")-HOY (). El formato de la celda debe serGeneral para que dé el valor correcto.

1 5




8. Función promedio

Problema 10: Exámenes

Estudiaremos la función PROMEDIO y repasaremos parte de lo visto. Un instituto deidiomas ha tomado tres exámenes a sus alumnos. Calcule la nota final como elpromedio de las notas de les tres exámenes:

Cree y complete los valores de la siguiente tabla usando vínculos entre hojas:

Docente de todos los alumnos mayores de 18 años es Mónica. Docente de todos losmenores de edad es Gladis. Todos los alumnos cuya nota final supere 6 promuevena un nivel siguiente. El resto permanecen en el actual. Use una función SI "anidada"con una función SUMA.

Resolución:

Para el cálculo de la nota final usaremos la función PROMEDIO con la siguientesintaxis: =PROMEDIO (Primer Número; Segundo Número; Tercer Número), dondePrimer Número, Segundo Número y Tercer Número pueden ser los valores o lasceldas que contiene los valores a promediar. Se pueden promediar todos losnúmeros necesarios.

Otra forma de hacer lo mismo, que puede resultar mas simple cuando se trabaja congran cantidad de datos, puede verse en este ejemplo hecho con los datos delproblema:

1 6




D2 es la nota del primer examen y F2 la del ultimo.

El cálculo de la edad puede hacerse con la siguiente función: =HOY ()-C2 donde C2es la celda que contiene la fecha de nacimiento del individuo. El formato de la celdadebe ser Personalizado como yy.

Para estableces el nivel al que promueve un alumno usaremos la función SI con unasintaxis similar a esta: =SI (NOTA>6;NivelActual +1;NivelActual), donde Nivel Actuales la celda que indica el nivel actual de ese alumno y NOTA es la celda que contienela nota final del alumno.

Para establecer el Docente de cada alumno usaremos, nuevamente, la función SI.Como puede verse en la imagen la sintaxis de la función podría ser:=SI(D2>=18;"Monica";"Gladis") donde D2 es la celda que indica la edad del alumno.

1 7




1 8




9. Función BUSCARV y referencias absolutas

Problema 11: BUSCARV y referencias absolutas

Presentaremos la función BUSCARV e veremos la importancia de las referenciasrelativas y absolutas. Usted es responsable del pago del sueldo en una agencia deventas. El sueldo se establece en base a la categoría a la que pertenece cadavendedor.

Todos los meses usted recibe los datos de la tabla de la derecha y debe obtener elsueldo de cada vendedor en base a los datos de ella. Para lograrlo construye unatabla como la de la izquierda.

Calcule con una única función el sueldo de cada empleado.

Resolución:

Emplearemos la función BUSCARV que buscará en forma vertical (V) un valordeterminado por usted (puede indicarse una celda) en una columna indicada. Susintaxis general es del siguiente tipo:

BUSCAR V(VALOR; RANGOBUSQUEDA; COLUMNA Nº; Ord)

VALOR: ¿El valor de que celda debo encontrar?RANGO BUSQUEDA: ¿Dónde lo tengo que buscar? ¿En que matriz?COLUMNA Nº: ¿En que columna de esa matriz?

En nuestro ejemplo la solución para el primer vendedor podría ser:

=BUSCAR V(B2;E1:F6;2)

Sin embargo al estirar o copiar esa fórmula en las celdas del resto de los vendedoreslos valores no serán correctos. Esto es porque E1 se transformará en E2 al copiar enla celda B3, y F6 en F7. De la misma forma ocurrirá en el resto. La solución alproblema sería que la sintaxis de la función en la celda del sueldo del primervendedor 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 celdasno cambie el número de la fila de E1 a E2, ni de F6 a F7.

1 9




10. Función Y

Problema 12: Función Y

Emplearemos la función SI anidada con la función Y para resolver un problema.

Una escuela prepara el viaje de fin de curso. Los alumnos se separarán en grupo deacuerdo a la edad y la nota final.

Todo niño de 9 años de edad y cuya calificación final fuera mayor o igual a 7 reúnelas condiciones para integrar el Contingente 1; el resto integrará el Contingente 2.

Arma la planilla de cálculo como el modelo. Agrega dos alumnos más anteriores aJorge.

Determina la formula que de cómo resultado el contingente al que pertenecerá cadaalumno.

Resolución:

La función Y nos devuelve el valor VERDADERO si los datos lógicos que leIngresamos se cumplen.

En nuestro ejemplo, nosotros necesitamos saber si se cumple que: NOTA FINAL >=7y EDAD >=9

Para que la función Y nos devuelva VERDADERO o FALSO, según corresponda,debiéramos escribir algo así: Y(NOTA FINAL >=7; EDAD >=9)

La sintaxis de la función SI que usaremos anidada con la función Y se ve en laimagen (para el primer alumno de la lista: =SI(Y(B2>=7; C2>=9);"Contingente1";"Contingente 2")).

2 0




Problema 13: Contar SI

Continuamos usando el ejemplo anterior para ver la función CONTAR. SI. Empleandola lista de alumnos del problema 12 contar, por un lado, cuántos niños son menoresde 9 años y por otro cuantos obtuvieron una nota menor que 7.

Resolución:

La función CONTAR. SI busca en una rango de datos especificado por el usuario,cuántas veces se cumple el criterio que se le indica. En nuestro ejemplo la funciónquedaría de la siguiente forma para contar cuantos niños son menores de 9 años: =CONTAR. SI (C2:C8;"<9")

En RANGO debe especificarse la columna o fila donde se van buscar los datos quecumplan con el CRITERIO.

2 1




11. La ayuda

Usemos la ayuda

Muy pocas personas recuerdan que existe el menú? de Excel. Este menú contiene elacceso a la ayuda, tanto del programa como a la ayuda en línea. Esta ayuda puede ser de utilidad al usuario cuando se encuentra con problemas queno saber resolver o funciones que no entiende.

¿Cómo accedemos a la ayuda?

La forma más sencilla es presionando F1. Se desplegará así el Panel de Tareas quepermitirá hacer una búsqueda (similar a una búsqueda en la red).

Como ejercicio le proponemos que buscar las funciones que hemos usado parafamiliarizarse con ellas.

NOTA: Con este capítulo hemos llegado al final del curso.

2 2




excel. funciones -...

Documents