unidad ii: fórmulas y funciones

UNIDAD II: FÓRMULAS Y FUNCIONES

Facultad de IngenieríaUniversidad Autónoma de QuerétaroMa. Teresa García Ramírez

CONTENIDO CELDAS

Fórmulas Extender una fórmula

Funciones matriciales Con Arreglos

Nombrar Celdas

CONTENIDO

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

FÓRMULAS

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

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.

Extensión de la fórmula de

F2

FÓRMULAS

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,

¿Porqué el resultado es

cero?

FÓRMULAS

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,

El valor de la celda se

incrementa

REFERENCIAS ABSOLUTAS Y RELATIVAS

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.

FÓRMULAS

Para evitar que se modifique el índice de la celda, se debe de anteponer el signo de $ al índice de la celda. Esto es:

Se incluye el signo de $

FÓRMULAS

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

FÓRMULAS

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

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

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.

NOMBRAR CELDAS

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.

Nombre de la Celda B2

NOMBRAR CELDAS

Ahora se puede usar el nombre de la celda en alguna fórmula, por ejemplo:

La fórmula es equivalente a

=A2*B2

NOMBRAR CELDAS

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

NOMBRAR CELDAS

Las celdas también se pueden nombrar desde el menú de fórmulas->nombres definidos.

Crear nuevo

nombre

FÓRMULAS CON REFERENCIAS…

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.

EJERCICIO

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.

FUNCIONES

21

FÓRMULAS Y FUNCIONES

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)

22

FÓRMULAS Y FUNCIONES

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)

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

FUNCIONES DE FECHA

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.

27

FUNCIONES TEXTO

28

FUNCIONES DE TEXTO

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.

29

FUNCIONES DE TEXTO

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.

30

FUNCIONES DE TEXTO

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 € "

31

FUNCIONES DE TEXTO

 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 garcia ramirez") devuelve “Teresa

Garcia Ramirez“

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 "

32

FUNCIONES DE TEXTO

  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

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 LÓGICAS

35

FUNCIONES LÓGICAS

FÓRMULAS CONDICIONALES

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

La función SI utiliza los siguientes argumentos.

Donde1.  prueba_lógica: condición que se desea comprobar.2.  valor_si_verdadero: valor que se devolverá si la

condición se cumple.3.  valor_si_falso: valor que se devolverá si la

condición no se cumple.

EJERCICIO

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"?

FUNCIONES MATEMÁTICAS

40

FUNCIONES MATEMÁTICAS Y TRIGONOMÉTRICAS

Función DescripciónABS Devuelve el valor absoluto de un número

ACOS Devuelve el arcocoseno de un número

ATAN Devuelve la arcotangente de un número

ATAN2 Devuelve la arcotangente de las coordenadas "x" e "y"

COMBINAT Devuelve el número de combinaciones para un número determinado de objetos

COS Devuelve el coseno de un número

GRADOS Convierte radianes en grados

EXP Devuelve e elevado a la potencia de un número dado

FACT Devuelve el factorial de un número

M.C.D Devuelve el máximo común divisor

M.C.M Devuelve el mínimo común múltiplo

LN Devuelve el logaritmo natural (neperiano) de un número

LOG Devuelve el logaritmo de un número en una base especificada

LOG10 Devuelve el logaritmo en base 10 de un número

41

FUNCIONES MATEMÁTICAS Y TRIGONOMÉTRICAS

Función DescripciónMDETERM Devuelve la determinante matricial de una matriz

MINVERSA Devuelve la matriz inversa de una matriz

MMULT Devuelve el producto de matriz de dos matrices

MULTINOMIAL Devuelve el polinomio de un conjunto de números

PI Devuelve el valor de pi

POTENCIA Devuelve el resultado de elevar un número a una potencia

COCIENTE Devuelve la parte entera de una división

RADIANES Convierte grados en radianes

SENO Devuelve el seno de un ángulo determinado

RAIZ Devuelve la raíz cuadrada positiva de un número

TAN Devuelve la tangente de un número

TRUNCAR Trunca un número a un entero

FÓRMULAS CON ARREGLOS

FÓRMULAS PARA FUNCIONES MATRICIALES

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

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

Área de Fórmula

FÓRMULAS PARA FUNCIONES MATRICIALES

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 SUMAR Y CONTAR

47

FÓRMULAS Y FUNCIONES

Encontrar el total de ventas por zona.

48

FÓRMULAS Y FUNCIONES

Incrementar el impuesto.

49

FÓRMULAS Y FUNCIONES

Calcular el promedio de ventas por zona.

50

FÓRMULAS Y FUNCIONES

Obtener solo las ventas de la zona norte. Se usa sumar.si( …)

Obtener las ventas de la zona Este.

51

FÓRMULAS Y FUNCIONES

Obtener la mayor venta realizada en cualquier zona. Se usa max( …)

Obtener la menor venta realizada en la zona Norte. (se usa min(…))

52

FÓRMULAS Y FUNCIONES

Obtener el monto pagado solamente de las uvas, considerar el 16% de iva. Se usa sumar.si( …)

53

FÓRMULAS Y FUNCIONES

Contar el número de ventas superiores a 30000 pesos. Se usa contar.si( …)

La función contar.si se usa solo con un criterio para el rango de datos.

54

FÓRMULAS Y FUNCIONES

La función SUMAR.SI.CONJUNTO realiza la suma de un rango de acuerdo a uno o varios criterios determinado. Ejemplo:

55

FÓRMULAS Y FUNCIONES

Obtener las cantidades totales de cada una de las cuentas en las que el interés es mayor que 3% en 2000. Función sumar.si.conjunto(…)

=SUMAR.SI.CONJUNTO(B33:E33,B34:E34,">3%")

56

FÓRMULAS Y FUNCIONES

Obtener las cantidades totales de cada una de las cuentas en las que el interés es mayor que 3% en 2000 y mayores o iguales a 2% en 2001. =SUMAR.SI.CONJUNTO(B33:E33,B34:E34,"<3%",B3

5:E35,">=1%") Obtener las cantidades totales de cada una de

las cuentas en las que el interés estaba entre el 1% y el 3% para el año 2002 y era mayor que el 1% para el año 2001. =SUMAR.SI.CONJUNTO(B33:E33,B36:E36,">=1%",B

36:E36,"<=3%",B35:E35,">1%")

57

FÓRMULAS Y FUNCIONES

De la siguiente tabla obtener la cantidad de lluvia para los días en que la temperatura media era de almenos 40% y la velocidad media del viento inferior a 10 millas.

=SUMAR.SI.CONJUNTO(B42:E43,B44:E45,">=40",B46:E47,"<10")

58

FÓRMULAS Y FUNCIONES

Excel permite obtener el producto de más de dos números mediante la función PRODUCTO(num1,num2,…)

También se puede obtener el residuo de la división mediante la función RESIDUO(num, divisor)

59

FÓRMULAS Y FUNCIONES

Se pueden calcular subtotales de operaciones realizadas por ejemplo: SUBTOTALES(No. Función, Referencia)

Donde.

60

FÓRMULAS Y FUNCIONES

Calcular los subtotales del promedio de ventas en el primer y tercer cuatrimestre. SUBTOTALES(No. Función (1), Referencia)