mailxmail - Cursos para compartir lo que sabes

Excel. Funciones Autor: Pablo Biaggioni

1

mailxmail - Cursos para compartir lo que sabes

Presentación del curso Con este curso aprenderás la creación de planillas en Excel utilizando las funciones que 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 con fechas, e introduciremos el concepto de función anidada. También veremos la función promedio, la función BUSCARV y referencias absolutas, así como la función Y, la función Contar.si. Y te enseñaremos a utilizar la ayuda en Excel, que puede ser de utilidad cuando te encuentres con problemas o funciones que no entiendas.

2

mailxmail - Cursos para compartir lo que sabes

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. El aprendizaje se adquiere mediante problemas de los que se plantea una posible solución. En todos los casos, queda por cuenta del lector la realización de los ejercicios propuestos. Todos los nombres o marcas mencionadas son propiedad de sus respectivos dueños y 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 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 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ón autosuma: 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

mailxmail - Cursos para compartir lo que sabes Resolución: Procedimiento para obtener el monto total: Seleccione las celdas donde se encuentran los valores a sumar y haga clic en Autosuma

4

mailxmail - Cursos para compartir lo que sabes

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 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. 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 los gastos. A ese valor le sumaremos el 10% que vamos a calcular en otra celda. Luego dividiremos todo por el número 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.

5

mailxmail - Cursos para compartir lo que sabes

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. 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 gráfica como la siguiente:

Resolución: Simplemente se debe copiar y pegar cinco veces la tabla creada en el ejercicio anterior y cambia algunos valores según 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 gráfica selecciono la tabla final, ingreso en el menú insertar y presiono 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). Al hacer clic se cerrará la ventana de gráfico momentáneamente y debemos seleccionar la columna de los meses y presionar la tecla Intro.

6

mailxmail - Cursos para compartir lo que sabes Después haremos clic sobre el siguiente y al desaparecer la ventana de gráfico seleccionaremos la columna de los montos y presionaremos intro.

Proseguiremos con el tercer presionaremos intro.

En este caso seleccionaremos ambas columnas y

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 creación de gráficos y vincular hojas entre sí. El trabajo realizado para la creación del gráfico es muy bueno, pero sus patrones desean que cada mes en una hoja diferente y el resumen del año junto con la gráfica en una hoja separada. Cada hoja debe tener el nombre correspondiente. Resolución: 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 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 de cómo debe proceder. La forma simple de obtener una fórmula como la exhibida es colocando el signo = en la celda, luego presionamos sobre la pestaña de libro donde se encuentra el valor a obtener, presionamos sobre la celda que lo contiene y ENTER (intro).

7

mailxmail - Cursos para compartir lo que sabes

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 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 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 de Diciembre. El Total es la suma del Adicional más el Premio (calculado en la otra hoja). Resolución: 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

8

mailxmail - Cursos para compartir lo que sabes 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 vistos hasta 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 correspondientes a la función. En nuestro ejemplo se vería así:

9

mailxmail - Cursos para compartir lo que sabes

10

mailxmail - Cursos para compartir lo que sabes

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 el cálculo 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:

Diseñe 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 organización usted debe crear una tabla como la siguiente, la cual 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 de egresos:

11

mailxmail - Cursos para compartir lo que sabes

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

12

mailxmail - Cursos para compartir lo que sabes

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 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)

13

mailxmail - Cursos para compartir lo que sabes

7. Fechas: funciones Problema 9 fechas: Veremos algunas funciones con fechas e introduciremos el concepto de función anidada. 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 de su nacimiento, empleando los valores obtenidos en 2,3 y 4. Resolución parte I:

1 0 / 0 5 / 1 9 6 9. 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 la funció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 caso la 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 actualice automá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:

14

mailxmail - Cursos para compartir lo que sabes

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

15

mailxmail - Cursos para compartir lo que sabes

8. Función promedio Problema 10: Exámenes Estudiaremos la función PROMEDIO y repasaremos parte de lo visto. Un instituto de idiomas ha tomado tres exámenes a sus alumnos. Calcule la nota final como el promedio 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 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 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 siguiente sintaxis: =PROMEDIO (Primer Número; Segundo Número; Tercer Número), donde Primer Número, Segundo Número y Tercer Número pueden ser los valores o las celdas que contiene los valores a promediar. Se pueden promediar todos los números 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:

16

mailxmail - Cursos para compartir lo que sabes

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 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 función 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 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.

17

mailxmail - Cursos para compartir lo que sabes

18

mailxmail - Cursos para compartir lo que sabes

9. Función BUSCARV y referencias absolutas Problema 11: BUSCARV y referencias absolutas Presentaremos la función 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 categoría 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 función el sueldo de cada empleado. Resolución: Emplearemos la función 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: ¿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 vendedores los valores no serán 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 solución al problema sería que la sintaxis de la función 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 número de la fila de E1 a E2, ni de F6 a F7.

19

mailxmail - Cursos para compartir lo que sabes

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 de acuerdo 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úne las 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 a Jorge. Determina la formula que de cómo resultado el contingente al que pertenecerá cada alumno.

Resolución: La función Y nos devuelve el valor VERDADERO si los datos lógicos que le Ingresamos se cumplen. En nuestro ejemplo, nosotros necesitamos saber si se cumple que: NOTA FINAL >=7 y 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 la imagen (para el primer alumno de la lista: =SI(Y(B2>=7; C2>=9);"Contingente 1";"Contingente 2")).

20

mailxmail - Cursos para compartir lo que sabes

Problema 13: Contar SI Continuamos usando el ejemplo anterior para ver la función CONTAR. SI. Empleando la lista de alumnos del problema 12 contar, por un lado, cuántos niños son menores de 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ón quedaría de la siguiente forma para contar cuantos niños son menores de 9 años: = CONTAR. SI (C2:C8;"