unidad ii: fórmulas y funciones
Post on 02-Jan-2016
56 Views
Preview:
DESCRIPTION
TRANSCRIPT
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)
top related