Unidad II: Fórmulas y funciones

1. Facultad de Ingeniería Universidad Autónoma de Querétaro Ma. Teresa García Ramírez Unidad II: Fórmulas y funciones

2. Fórmulas • Extender una fórmula • Funciones matriciales • Con Arreglos • Nombrar Celdas Contenido celdas

3. Calcular fórmulas. Referencias a celdas y rangos. Usar nombres. Funciones. Fórmulas con arreglos. Técnicas para contar y sumar. Funciones de Base de Datos Tablas Dinámicas Contenido

4. Las fórmulas son aquellas que inician con el signo =. • Las fórmulas pueden incluir • Valores numéricos • Funciones • Para un funcionamiento adecuado de las fórmulas usar paréntesis. Fórmulas

5. Extender una fórmula • Se coloca el cursor en el cuadro negro que aparece del lado inferior derecho de la celda y se arrastra el cursor hacia el lado que se quiera extender la fórmula. Fórmulas Extensión de la fórmula de F2

6. Problemas posibles al extender una fórmula. Si se desea mantener fijo un valor de referencia a una celda puede no funcionar correctamente. Por ejemplo, Fórmulas ¿Porqué el resultado es cero?

7. Problemas posibles al extender una fórmula. Si se desea mantener fijo un valor de referencia a una celda puede no funcionar correctamente. Por ejemplo, Fórmulas El valor de la celda se incrementa

8. Las referencias absolutas son aquellas señalan una un renglón y columna específica y que no se puede modificar. Para ello se antepone el signo de $ tanto al nombre de la columna como al número de renglón, por ejemplo: $C$5 Referencias Mixtas, contienen una parte absoluta y la otra relativa por ejemplo: C$5, el número de renglón no se modifica, o $C5, donde el nombre de columna no se modifica. Por default excel usa referencias Relativas, C5, donde al extender una fórmula estos se van tomando diferentes valores. Referencias Absolutas y Relativas

9. Para evitar que se modifique el índice de la celda, se debe de anteponer el signo de $ al índice de la celda. Esto es: Fórmulas Se incluye el signo de $

10. Si accedemos a alguna celda donde se extendió la fórmula se observa lo siguiente: Fórmulas

11. Otra forma para evitar problemas en la extensión de una fórmula es usar nombres de variables. • Para asignar nombres a variables se posiciona en cuadro de nombres. • Se escribe el nombre de la celda. • Para usarse se pone en la fórmula el nombre de la celda. Fórmulas

12. También se pueden extender fórmulas o series a partir del menú inicio->modificar y rellenar. Para rellenar se debe tener un valor inicial y a partir de este se hace el rellenado con series de diferentes tipos o extendiendo una fórmula. Fórmulas

13. Al extender una fórmula también se puede indicar el relleno al presionar sobre el icono de menú que aparece. Rellenar formatos sólo no copia la fórmula. Rellenar sin formato no copia color, tipo de letra etc. pero si copia la fórmula. Fórmulas

14. Para nombrar celdas se da click en la celda a la cual se le quiere asignar un nombre. Posteriormente se escribe del lado izquierdo de la barra de fórmulas el nombre de la celda. Nombrar Celdas Nombre de la Celda B2

15. Ahora se puede usar el nombre de la celda en alguna fórmula, por ejemplo: Nombrar Celdas La fórmula es equivalente a =A2*B2

16. Nombrar un conjunto de celdas: se selecciona el rango de celdas y se introduce el nombre. Nombrar Celdas

17. Las celdas también se pueden nombrar desde el menú de fórmulas->nombres definidos. Nombrar Celdas Crear nuevo nombre

18. Creando fórmulas con referencias a diferentes hojas. • Posicionarse en la celda a introducir la fórmula, presionar el signo de =. • Ir a la hoja donde se tiene el dato para seleccionarlo, presionar enter. • Al regresar a la celda de la fórmula aparece lo siguiente: • Ahora agregar el signo de la operación y seleccionar otro valor que puede ser de otra hoja. Fórmulas con referencias…

19. Crear una hoja de cálculo con nombres de productos, costo de compra, porcentaje incremento, costo de venta. Con esta información y utilizando nombres de celdas y de grupos de celdas, calcular: • El costo de venta. • El costo de compra promedio. • El costo de venta promedio. Ejercicio

20. Funciones

21. Generación de números aleatorios. • Aleatorio() • Genera números aleatorios entre 0 y 1 por lo que es necesario multiplicar por el número máximo en que se desea obtener valores. • Aleatorio()*100 genera números entre 0 y 100 • Aleatorio()*50+50 genera números entre 50 y 100. • Si se desean enteros se antepone la palabra entero(aleatorio()*100) Fórmulas y Funciones

22. Redondeos. • entero(num): redondea al entero inferior más próximo • redondear(num,numdecimales): redondea al número de decimales especificado, (1 indica a la siguiente decima, 0 indica el siguiente número entero superior o inferior). • redondea.par(num), redondea.impar(num). • redondear.mas(num,numdecimales), redondear.menos(num,numdecimales) Fórmulas y Funciones

23. Funciones fechas

24. Funciones de Fecha Fecha inicial y final deben estar en formato fecha

25. Funciones de Fecha dias.lab(fecha_ini,fecha_fin,[vacaciones])

26. Calcular los días laborados en el mes de mayo. Calcular los días entre una fecha previa y el día de hoy. Obtener el mes de la fecha de hoy. Obtener el día de la fecha de hoy. Funciones de Fecha

27. Funciones TEXTO

28. Función ENCONTRAR Y HALLAR(texto_buscado;dentro_del_texto;núm_inicial) • Busca un texto dentro de otro y devuelve la posición del texto buscado. Realiza la búsqueda leyendo el texto de izquierda a derecha a partir de la posición inicial indicada en núm_inicial. Encontrar distingue entre mayúsculas y minúsculas y no admite caracteres comodínes (? un solo carácter, * cualquier número de caracteres, ~ carácter de escape). Mientras que hallar si. • Ejemplo: =HALLAR("Mar“,"Mirando el mar, me envuelve una emoción intensa...";1) devuelve 12 • Ejemplo: =HALLAR("M?r“,"Mirando el mar, me envuelve una emoción intensa...";1) devuelve 1 • Función IGUAL(texto1;texto2) • Devuelve un valor lógico (verdadero/falso) según las dos cadenas de texto comparadas sean iguales o no. • Ejemplo: =IGUAL("esto es igual?“,"es igual esto?") devuelve FALSO • Normalmente se utiliza para comparar los valores almacenados en dos celdas. Funciones de Texto

29. Función IZQUIERDA(texto,núm_de_caracteres) • Devuelve el número de caracteres especificados desde el principio de la cadena de texto. • Ejemplo: =IZQUIERDA("El sol no puede competir con el brillo de tu mirada";6) devuelve "El sol" • Función LARGO(texto) • Devuelve el número de caracteres que tiene la cadena de texto, es decir su longitud. • Ejemplo: =LARGO("El sol no puede competir con el brillo de tu mirada") devuelve 51 • Función LIMPIAR(texto) • Limpia el texto de caracteres no imprimibles. • Ejemplo:Si escribimos =LIMPIAR(CARACTER(7)&“Quitar caracteres especiales"&CARACTER(7)), desaparecerán los caracteres no imprimibles. Funciones de Texto

30. Función MAYUSC(texto) • Convierte a mayúsculas la cadena de texto. • Ejemplo: =MAYUSC("convierteme a mayuscula") devuelve "CONVIERTEME A MAYUSCULA" • Función MINUSC(texto) • Convierte a minúsculas la cadena de texto. • Ejemplo: =MINUSC("VENGA Y AHORA A MINUSCULA") devuelve "venga y ahora a minuscula" •  Función MONEDA(número,núm_de_decimales) • Convierte a texto un número usando el formato de moneda. • Ejemplo: =MONEDA(25;2) devuelve "25,00 € " Funciones de Texto

31.  Función NOMPROPIO(texto) • Convierte la primera letra de cada palabra del texto a mayúscula y el resto de la palabra a minúsculas. • Ejemplo: =NOMPROPIO(“teresa garciaramirez") devuelve “Teresa GarciaRamirez“ • REEMPLAZAR(texto_original,num_inicial,núm_de_caracteres;texto_nuevo) • Reemplaza parte de una cadena de texto por otra. • Ejemplo: =REEMPLAZAR("Si este es el texto original, será modificado";21;8;" Por este ") devuelve "Si este es el texto Por este , será modificado“ • Función REPETIR(texto,núm_de_veces) • Repite el texto un número de veces determinado. • Ejemplo: =REPETIR("Como te repites ";5) devuelve "Como te repites Como te repites Como te repites Como te repites Como te repites " Funciones de Texto

32.   Función SUSTITUIR(texto;texto_original;texto_nuevo;núm_de_ocurrencia) • Reemplaza en texto, el texto_original por el texto_nuevo. • Ejemplo: =SUSTITUIR("El precio total del proyecto conlleva...";"precio";"coste") devuelve "El coste total del proyecto conlleva..." •  Función TEXTO(valor;formato) • Convierte un valor en texto. • Ejemplo: =TEXTO(25;"0,00 €") devuelve "25,00 €" • Función VALOR(texto) • Convierte un texto que representa un número en número. • Ejemplo: =VALOR("254") devuelve 254 en formato numérico. Funciones de Texto

33. Se utilizan conjuntos de celdas y ciertas condiciones en la fórmula para manejo de la información de la hoja de cálculo. Por ejemplo: • =suma(b1:b50) • =promedio(b1:b50) • =mediana(b1:b50) • =contar(b1:f20) Funciones de Texto

34. Funciones lógicas

35. Funciones Lógicas

36. Comprobar si las condiciones son verdaderas o falsas y realizar comparaciones lógicas entre expresiones son elementos comunes de varias tareas. Para crear fórmulas condicionales, puede utilizar las funciones Y, O, NO, y SI. Formato Y, O y NO Y(valor lógico 1, valor lógico2, …) Fórmulas Condicionales

37. La función SI utiliza los siguientes argumentos. • Donde • prueba_lógica: condición que se desea comprobar. • valor_si_verdadero: valor que se devolverá si la condición se cumple. • valor_si_falso: valor que se devolverá si la condición no se cumple. Fórmulas Condicionales

38. Introduce 3 valores numéricos en una columna • Introduce las cadenas “Perecederos” y “No perecederos”. • Realiza las siguientes Tareas • ¿Es valor1 mayor que valor2 y menor que valor3? • =Y(A13>A14,A13<A15) • ¿Es valor1 mayor que valor2 o mayor que valor3? • valor1 más valor2 no es igual a valor3 • ¿No es celdaX igual a “Perecederos”? • ¿No es celdaX igual a “Perecederos” o celdaY igual a “No Perecederos"? Ejercicio

39. Funciones matemáticas

40. Funciones Matemáticas y Trigonométricas

41. Funciones Matemáticas y Trigonométricas

42. Fórmulas con arreglos

43. Al utilizar las funciones matriciales, se obtiene muchas de las veces un solo valor (minversa), pero esta función debería regresar una matriz, para extender la fórmula y obtener el resultado completo se realiza lo siguiente. Introducir la fórmula para encontrar la inversa de una matriz. (R=2.75) Fórmulas para funciones matriciales

44. Para extender la fórmula: • Seleccionar las columnas y renglones para el resultado incluyendo el de la fórmula. • Posicionarse en área de fórmula o presionar F2 Fórmulas para funciones matriciales Área de Fórmula

45. Para extender la fórmula: • Finalmente presionar al mismo tiempo las teclas CTRL+SHIFT+ENTER. Con esta combinación de teclas se extiende una fórmula matricial la cual no se puede modificar y para reconocerla observamos que se añaden las llaves ({ … }) Fórmulas para funciones matriciales

46. Fórmulas sumar y contar

47. Encontrar el total de ventas por zona. Fórmulas y Funciones

48. Incrementar el impuesto. Fórmulas y Funciones

49. Calcular el promedio de ventas por zona. Fórmulas y Funciones

50. Obtener solo las ventas de la zona norte. • Se usa sumar.si( …) • Obtener las ventas de la zona Este. Fórmulas y Funciones