E16_UD12

Vista previa del material en texto

UNIDAD - FUNCIONES 
 
Función PAGO 
En este apartado veremos cómo trabajar con la función pago. 
Esta función nos sirve para calcular una serie de cuotas para pagar un determinado capital, con 
un interés y con una serie de plazos. 
 
Nos situamos en la celda donde queremos que nos aparezca la cuota mensual, C6. Suponemos 
que tenemos un préstamo de 140.000 €, con un interés anual del 3,4 %, que podemos pagar en 
20 años, es decir en 240 cuotas mensuales. La función pago nos va ayudar a calcular la cuota 
Vamos a ver aquí cómo trabajaríamos con la función pago para que nos calcule la cuota 
mensual 
Cuándo trabajamos con funciones podemos trabajar o bien con el asistente de funciones o 
bien trabajar directamente en la barra de formulas y funciones 
Yo comienzo escribiendo el símbolo igual y luego pongo el nombre de la función y al empezar 
vemos que ya nos aparece la función 
 
Vamos a ver que argumentos tiene está función 
 
El primer argumento que me está solicitando es la tasa. En este caso está haciendo referencia 
a los que haya en la celda C4 
 
Debemos tener en cuenta que esto es un interés anual y yo estoy calculando una cuota 
mensual con lo cual tendré que dividirlo entre doce y eso sería mi primer argumento 
 
El siguiente es el número de plazos, el número de cuotas en qué se va a apagar este capital y 
en este caso lo tenemos en C5 
 
El tercer argumento nos está solicitando el capital sobre el que estamos trabajando 
 
Los otros dos argumentos son opcionales Hacen referencia al resto que queremos que quede 
en el último plazo o al fijo y sí queremos qué es esa cantidad fuera inicial o final. En nuestro 
caso no lo vamos a utilizar no lo necesitamos para calcular la cuota mensual 
Cerramos el paréntesis de la función y pulsamos enter y vemos que arroja una cuota mensual 
de -804,77 
 
El valor de esta función siempre lo arroja en negativo por qué consideras que es un 
decrecimiento de la deuda 
Si queremos que nos lo den en positivo solo tenemos que aquí en la función delante de la 
referencia al capital y ponerle el – y así nos devolverá el valor en positivo. 
 
 
 
Funciones de búsqueda 
 
Las funciones de búsqueda me sirven para buscar un valor y conforme a ese valor devolver otra 
serie de resultados. 
Existen 3 funciones de búsqueda, “buscar”, que busca en toda una matriz, “buscar v”, que busca 
en vertical o en una columna y “buscar h” que busca en horizontal o en una fila. 
Nosotros lo que queremos en el ejemplo que tenemos, es que, al introducir aquí el número de 
cuenta de un cliente, nos aparezca debajo el nombre de la empresa, la dirección, la ciudad y el 
país. 
 
Lo que vamos a buscar en la tabla es el valor que ponga en la celda N.º de cuenta, para luego 
devolver el valor de los otros campos. 
Vamos a ver la forma de hacerlo. 
Nos situamos en la celda en la que vamos a poner la fórmula para que nos devuelva el nombre 
de la empresa. 
 
Nos vamos a la barra de funciones, igual a BUSCAR y vemos que nos aparecen ya las tres 
opciones de búsqueda. 
 
En este caso vamos a seleccionar, “buscar V” porque vamos a trabajar buscando en la columna 
del número de cuenta. Vamos a ver aquí que argumentos tiene esta opción. 
 
El primer argumento es “valor buscado”. El valor que vamos a buscar en este caso es el que 
escribamos en la celda de nº de cuenta. Pues entonces vamos a buscar el valor de esa celda. 
 
Pasamos al siguiente argumento “matriz buscar en”. La matriz donde lo va a buscar es en toda 
esta tabla. Entonces seleccionamos la matriz de búsqueda. En este caso, la tenemos nombrada 
como clientes. 
 
Ahora viene “indicador en columnas”, aquí lo que me está preguntando es que sabe que 
tenemos que buscar en esa matriz, concretamente en la primera columna, porque siempre el 
valor que busquemos tiene que estar en la primera columna. Si buscásemos por otro valor, 
tendríamos que coger ese campo y llevármelo a la primera columna. 
Ahora entonces la función sabe lo que tiene que buscar, pero necesita saber que queremos que 
escriba en esa celda. El dato que queremos que aparezca en esa celda, nombre de empresa, es 
la segunda columna de la matriz, entonces escribimos 2. 
 
A continuación lo que Excel denomina “ordenado”, admite dos valores, “verdadero” o “falso”, 
“0” ó “1”. Esto quiere decir si estamos buscando una coincidencia aproximada o exacta. En este 
caso, una coincidencia, porque si no podría darnos los datos de otro cliente. En este caso le 
ponemos 0. 
 
Y cerramos nuestra función. Vemos que en efecto nos está dando el nombre de la empresa. 
 
SI cambiamos el valor, nos cambia el nombre de la empresa. 
Tendríamos que repetir el proceso para el campo dirección: buscar v, campos buscado, 
seleccionamos la matriz, en este caso la columna 3 y coincidencia exacta. 
Tendríamos que hacer lo mismo para la ciudad. Pero si nos damos cuenta entre las dos funciones 
creadas, lo único que cambia es el número de columna. 
 
Entonces lo que podríamos hacer es copiar la función, irnos al campo de la ciudad, que vemos 
es la cuarta columna. 
Vamos a la barra de funciones, pegamos nuestra función, y cambiamos la segunda columna por 
la cuarta columna. Lo mismo con el campo “país” y lo sustituimos por la quinta columna. Y ya 
nos está dando todos los datos. 
Si cambiamos el número de cuenta y ponemos 1370 nos estaría dando los datos que le 
corresponden a este cliente. 
 
 
Funciones SI anidadas 
 
Cuando trabajamos con funciones lógicas “si”, en ocasiones necesitamos más de un “si” para 
poder definir qué salida queremos darle. Vamos a ver un ejemplo en el que tenemos varias 
funciones “si” anidadas. 
 
Tenemos una tabla con unos números de cliente y unos días de estancia, y queremos que la 
tabla nos indique que tipo de cliente es en función de los días de estancia que ha tenido. 
Queremos que sean de tipo A, aquellos que estén más de 20 días. Tipo B aquellos que estén 
menos de 20, pero más de 10. Y tipo C los que estén 10 o menos días. 
Vamos a la barra de funciones. Igual a SI y nos aparecen ya los argumentos de esta función. 
 
Lo primero que nos solicita es la prueba lógica, la condición que se tiene que cumplir. 
Comenzamos por los primeros, los tipos A. Bien pues si lo que hay en esta celda es mayor o igual 
que 20, en ese caso “punto y coma” porque ya hemos acabado la prueba lógica, 
 
“Valor si verdadero”, como es una cadena texto lo entrecomillamos, TIPO A. Punto y coma. 
 
“Valor si falso”, aquí es donde vamos a anidar nuestra siguiente función SI, porque si la función 
comprueba que no es de tipo A, tenemos que darle las opciones que sea tipo B o C. Ahora 
sabiendo que la estancia es menor de 20, tenemos que saber si es mayor o menor de 10. Pues 
SI, el valor de la celda de días de estancia tiene que ser mayor de 10 y menor que 20, pero menor 
de 20 ya lo hemos comprobado, así que mayor que 10. 
 
Valor si verdadero: en ese caso, tipo B. Punto y coma. 
 
Y ya sólo nos queda una posible salida que es el “valor si falso” de esta segunda función. Porque 
si no es A, ni es B, tiene que ser C. Entonces, tipo C. Cerramos nuestra segunda función Si y la 
primera. 
 
Vemos que en esta primera nos arroja tipo B. 
 
Vamos a pasar la función a toda la tabla. Comprobamos que todos son correctos. De esta forma 
anidamos funciones SI. 
 
Una función Si dentro de otra, siempre se anida en el “valor si Falso”. 
Un truco para calcular cuántos “si” anidados necesitamos, es ver cuantas posibles salidas tengo. 
En el supuesto que hubiese cliente tipo A, tipo B, tipo C, tipo D y tipo E. Serían 5 posibles salidas. 
En ese caso, necesitaría 4 funciones SI. Siempre voy a necesitar un Si menos de las posibles 
salidas. 
 
Función COINCIDIR 
 
La función COINCIDIR busca un valor en una matriz o tabla y devuelve la posición en la que se 
encuentra ese valor. 
En este ejemplo, vemos una serie de nombres y sus puntuaciones.Vamos a utilizar la función coincidir para cuando busque un nombre me dé la posición en la lista. 
Vamos a tomar como ejemplo Jorge. Queremos que nos aparezca la posición de este nombre en 
esta lista. 
 
Nos situamos en la casilla, igual a CONICIDIR, ya nos aparece la función y vamos a ver los 
argumentos. 
¿Valor buscado? Este nombre. 
 
¿Dónde lo estoy buscando? En esta matriz. Punto y coma. 
 
Y el tipo de coincidencia que vemos ya nos da valores, menor que, coincidencia exacta o mayor 
que. Nosotros cogemos 0, coincidencia exacta. 
 
Cerramos nuestra función y vemos que Jorge ocupa la séptima posición en la lista. 
 
Lo comprobamos. Y en efecto, es el séptimo elemento en esta lista. ¿Para qué nos puede servir 
esto? Pues si por ejemplo queremos saber qué posición ocupa la puntuación máxima, en este 
caso sería la de Raquel, podemos hacer en este caso hacer coincidir la función coincidir con la 
función máxima. 
Si aquí ponemos igual a función coincidir, 
 
¿Cuál es el valor que busco en este caso? Pues el valor máximo de toda esta lista. De forma que 
utilizaremos la opción MAX, el máximo de este rango. Cerramos. Punto y coma. 
 
Pasamos al siguiente argumento. ¿Dónde tiene que buscar el coincidir? Seleccionamos rango. 
 
Y la coincidencia se la damos exacta. Cerramos nuestra función y nos dice que la posición de la 
puntuación máxima está en cuarto lugar. 
 
Lo comprobamos, y efectivamente. 
 
Función PROMEDIO 
 
La media de un grupo de números se calcula sumando todos los valores y dividiendo por el 
número total de datos. Es lo que se llama el valor promedio. Y en Excel utilizaremos la función 
promedio para calcular este dato. 
 
En el ejemplo que tenemos, vemos las ventas desde el 2010 hasta el 2016 y quiero saber cuál 
ha sido el promedio de ventas en esos 7 años, tendríamos que aplicar la función promedio. 
 
Los únicos argumentos que nos solicita son los números de los que tiene que hallar esa media 
aritmética, que es lo que calcula esta función. Marcamos entonces el rango del que queremos 
que se extraiga esa media, cerramos la función y obtenemos nuestro promedio de ventas, que 
sería 19344,29 € 
 
 
Función JERARQUIA 
 
La función jerarquía nos devuelve la importancia de un número en una determinada lista de 
datos. En este ejemplo, vamos a ver de los importes de ventas de cada año, que posición o 
jerarquía ocupan en el total de ventas. 
 
Disponemos una columna específica para el cálculo de la jerarquía, y ponemos la función. 
 
Igual a jerarquía y los argumentos que nos está pidiendo es el número del que queremos hallar 
la jerarquía, punto y coma 
 
La referencia o lista que integran todos los elementos que queremos buscar esa jerarquía. 
 
Y el orden que puede ser ascendente o descendente. Es decir, va a considerar como si esta lista 
de referencia estuviera colocada en orden ascendente o descendente. Si ponemos 0, el valor 1 
sería el mayor de la lista, mientras que si pongo 1 sería el menor. Vamos a probar y dejarlo orden 
0. 
 
Cerramos la función y observamos que sería el 7º elemento en la jerarquía. Efectivamente 
comprobamos que es el valor más pequeño. 
 
Si en lugar de 0 en el orden ponemos un 1, me da a dar que es el primer elemento, porque es el 
menor. 
Extendemos la función al resto de celdas, pero antes vamos a convertir la referencia en una 
referencia absoluta, para que al arrastrar la función no vaya también desplazándose. Con F4 
cambiamos a referencia absoluta y aquí también. 
 
Ahora extendemos el resto de la función al resto de las celdas. Nos indica que el de mayor valor 
sería este y el de menor peso sería este otro. Vemos como funciona así esta función, nos 
devuelve el peso o importancia de un valor dentro de una lista. 
 
Función ESIMO.MENOR 
 
Esta función, lo que va a hacer es devolvernos el valor más pequeño dentro de un rango de 
datos. Además, podemos utilizar esta función para devolvernos valores con una posición relativa 
específica dentro de un conjunto de datos, es decir, no solo puedo sacar el valor menor, sino 
que también puedo sacar el segundo valor menor y el tercer valor menor…. Y así sucesivamente. 
Vamos a ver un ejemplo de cómo funciona esta función. Tenemos un conjunto de números 
aleatorios y vamos a ver cómo sacamos estos tres datos. 
 
Nos venimos a esta celda donde queremos el valor menor. 
Igual a K, y aquí ya nos lo reconoce, Kesimo menor. 
 
Esta función tiene dos argumentos, el argumento matriz y el argumento “k”. 
La matriz es el rango de datos en el que vamos a buscar esos valores. 
 
Y “k” nos pide la posición dentro de la matriz de los datos que se van a devolver, determinada a 
partir del menor de los valores. Si le ponemos 1, nos va a dar el menor valor. 
 
Efectivamente lo comprobamos. 
¿Cómo hacemos para sacar el segundo valor menor? Pues exactamente igual: igual a, 
seleccionamos la matriz, pero en este caso el argumento “k” como lo que queremos es el 
segundo valor menor, le ponemos 2. 
 
Porque es el segundo empezando por el menor. Lo comprobamos y en efecto es 6. 
 
Aquí sería lo mismo, pero utilizando tres, como argumento “k”. Y ya tenemos los tres valores 
inferiores de nuestra lista. 
 
Función ESIMO MAYOR 
La función “K.esimo mayor”, hace justamente lo contrario que la “kesimo menor”. Si aquella nos 
devolvía el menor valor de una matriz, ésta lo va a hacer con el valor mayor o el enésimo valor 
mayor. 
Vamos a averiguar cuál es el valor más alto de nuestra lista. 
Nos situamos en el celda y escribimos igual a “Kesimo mayor”, seleccionamos matriz, el 
argumento k, como en este caso queremos el mayor, sería 1. 
 
Comprobamos que da el dato correcto. 
Si a continuación, ponemos la función “k esimo mayor”, pero en este caso el argumento “k” 
ponemos 2, nos da el segundo mayor valor de la lista. 
 
Y aquí exactamente igual, pero con el tercer valor más alto de la lista. Y vemos que estos serían 
los 3 valores superiores de nuestra lista de datos. 
 
 
Función contar.Blanco 
 
La función contar.blanco, lo que va a hacer es contar las celdas que hay vacías dentro de un 
determinado rango. Si por ejemplo en esta lista de clientes que tenemos aquí quisiéramos ver 
cuantas celdas vacías hay podríamos utilizar esta función. 
 
 Vamos a verlo. 
Nos situamos en la celda de debajo de Celdas en blanco ponemos igual a y vemos que ya nos 
despliega las funciones de la familia contar. 
 
Seleccionamos contar blanco y vemos que tiene un único argumento, lo único que nos está 
pidiendo es el rango donde tiene que contar las celdas. 
 
Seleccionamos el rango, cerramos la función y nos da 8 celdas en blanco. 
 
Vamos a contar a ver si es correcto. 
Esto es lo que hace la función contar blanco, cuenta las celdas en blanco que hay dentro de un 
determinado rango. 
 
Funciones de redondeo: residuo 
 
La función residuo nos devuelve el resto de una división. Es una función que tiene dos 
argumentos, el número que quiero dividir y el divisor del número. 
Vamos a ver un ejemplo. La función residuo y vemos sus dos argumentos, número y número 
divisor. 
 
Vamos a poner 897 y el divisor que sea 5. Cerramos la función y el resto es 2. 
 
Es una función que nos va a devolver el resto de una división. 
Puede servir para comprobar si un número es divisible por otro. ¿Cuándo será divisible? Cuando 
su resto sea 0. 
Tenemos una tabla, y vamos a comprobar si los números de la columna B son divisibles por estos 
otros. 
Nos situamos en la celda bajo Residuo en la tabla y ponemos la fórmula: igual residuo. 
 
El primer número que queremos dividir será este. 
 
Y el divisor que será este. 
 
Arroja un resto de 2, con lo cual no es divisible, arrastramos y vemos que los únicos números 
divisibles por su divisor son son dos, que son los que arrojan un resto 0 
 
 
 
Funciones de fecha y hora 
 
En este apartado veremos las principalesfunciones de fecha y hora de Excel. Existen muchas, de 
forma que veremos las más utilizadas. En Excel hay dos tipos de funciones de fecha y hora: las 
que no les ponemos argumento y nos devuelven valores actuales, o las que nos sirven para hacer 
cálculos, por ejemplo, saber cuántos días hay en un intervalo entre dos fechas. 
Vamos a ir viendo funciones con estos ejemplos que tenemos aquí. 
 
La función ahora nos va a devolver la fecha y hora actuales con un formato adecuado. 
Escribimos AHORA y podemos observar que no tiene argumentos, simplemente cerramos el 
paréntesis, y nos devuelve el día y la hora actuales. 
 
La función HOY nos devuelve únicamente la fecha. Nos fijamos que esta función tampoco tiene 
argumentos, abrimos y cerramos paréntesis y nos devuelve la fecha. 
 
La función año nos sirve para extraernos el año de dentro de una fecha. Escribimos AÑO, la fecha 
se escribe entrecomillada, por ejemplo 5 de Julio de 2014. Cerramos comillas y paréntesis. Y 
efectivamente nos extrae el año. 
 
Vamos a copiar la fecha que hemos utilizado para usar en otros ejemplos. 
La función MES hace lo mismo, pero devolviéndonos el dato del mes. Ponemos la función y 
usamos la fecha copia anteriormente. 
 
Y la función DÍA nos devolverá el día. Efectivamente nos devuelve el día 5, del mes 7, del 2014. 
 
La función días.Lab nos va a devolver el número total de días laborables entre dos fechas. Si 
ponemos, igual a días. Lab, nos va a pedir como primer argumento la fecha inicial, por ejemplo, 
la que teníamos copiada, punto y como, la fecha final, que imaginemos queremos durante un 
año, punto y coma, y podemos poner un tercer argumento que serían las vacaciones, si aparte 
de los fines de semanas existen días de vacaciones podríamos ponerlos. 
 
En nuestro caso no los pondremos, así que cerramos nuestra función y nos arroja el valor de 
260. 
 
Es decir, entre las dos fechas que hemos puesto hay 260 días laborales. 
La función hora nos devuelve la hora como un número, es decir, si yo le doy hora, minuto y 
segundo me extrae la hora. Ponemos la función y como argumento las 21 horas, 15 minutos y 
35 segundos. Cerramos, y me extrae que la hora es las 21 horas. 
 
Vamos a copiarlo para usarlo en otras funciones. 
La función minuto hace lo mismo, pero extrae el minuto. Aquí nos tiene que extraer 15. 
 
Y la función segundo nos extraerá los segundos. 
 
En este apartado hemos visto cómo funcionan las principales funciones de fecha y hora. 
Por ampliar algo estas funciones, podemos ir a la función año y en lugar de fecha, ponerle que 
nos calcule el año ahora y nos extrae 2018. 
 
Podemos jugar así con todas estas funciones. 
 
Función DIASEM 
Podemos identificar el día de la semana a la que perteneció un día de una fecha pasada: la 
función nos va a devolver un número que nos identificará un día de una semana, ya veremos 
más adelante que podemos identificarlos de varias maneras. 
Vamos con ello. 
La función es DIASEM, nos pide como primer argumento la fecha, por ejemplo, el 30 de marzo 
de 1984, y en el segundo argumento, el tipo, vemos que tenemos diferentes opciones. Si 
ponemos 1, la semana comenzaría en el domingo. Si ponemos el 2 comenzaríamos en el 0 que 
sería el lunes…. Y así tenemos distintas opciones. 
 
En nuestro ejemplo vamos a coger el 2. Cerramos nuestra función y nos extraerá que día de la 
semana fue el 30 de marzo de 1984 
 
Nos arroja un 5, que es un viernes. 
Para esto nos sirve la función diasem, para mostrarnos el número del día de la semana a la que 
perteneció una fecha 
 
 
Funciones encontrar y extraer 
 
La función que vamos a ver sirve para encontrar una cadena de texto dentro de otra cadena de 
texto y nos da la posición en la que está. 
En nuestro ejemplo tenemos unos textos y queremos buscar este carácter dentro de esos textos. 
 
Hay que señalar que la función encontrar distingue mayúsculas de minúsculas. En este caso, 
queremos saber dónde se encuentra la E mayúscula dentro del texto. Iniciamos la función, 
encontrar… 
 
Lo primero que me pide es que texto estoy buscando, es la E mayúscula, dentro del texto que 
tengo en la celda B2y el argumento número inicial hace referencia a partir de que posición 
quiero comenzar a buscar. Por defecto es el 1, y así comienza desde el principio. 
 
En efecto vemos que el carácter que buscamos está en primera posición dentro del texto. Si 
estiramos esta fórmula, observamos que nos va dando las posiciones de los distintos caracteres 
dentro de ese texto. 
 
Hay otra función que es la función extrae, que nos va a servir para extraer un determinado 
número de caracteres de una cadena de texto. 
En nuestro ejemplo queremos extraer la palabra función y que nos aparezca en la celda D4. 
 
Comenzamos, función extrae, lo primero que me pide es el texto del que vamos a extraer la 
cadena de caracteres. 
A continuación, nos pide a partir de qué posición queremos comenzar a extraer. Contamos y 
obtenemos que a palabra función comienza en la posición 15. Y ahora nos pide a partir de esa 
posición cuantos caracteres extraigo. En nuestro caso son 7. Cerramos y nos arroja 
efectivamente la palabra que buscamos. 
 
Si queremos extraer “de la”, comenzamos igual, el texto el mismo, a partir de la posición 9 y 
sería 9 caracteres. Y nos extraería esos caracteres. 
Con esto hemos visto la función encontrar y extraer. 
 
Funciones izquierda y derecha 
 
Las funciones izquierda y derecha son aquellas que nos permiten extraer caracteres del principio 
o del final de una cadena de texto. Si queremos extraer los caracteres del principio, tenemos 
que usar la función izquierda. Para extraer los del final la función derecha. 
 
 Vamos a sacar los 4 primeros caracteres de esa cadena. Comenzamos con la función izquierda. 
Primer argumento que solicita es el texto del que queremos extraer y lo siguiente el número de 
caracteres que queremos extraer. En nuestro caso 4, cerramos y vemos que efectivamente nos 
saca los cuatro primeros. 
 
Si por ejemplo cambiamos y ponemos 8, veríamos que nos extrae ese número de caracteres. 
La función derecha es exactamente igual, pero extrae del final de la cadena. Marcamos el texto 
y 10 caracteres que queremos extraer. Cerramos la función y vemos que es correcto. 
 
Con esto terminamos las funciones izquierda y derecha.