Microsoft excel parte 2

UNIVERSIDAD TÉCNICA DE MACHALA

FACULTAD DE CIENCIA QUIMICAS

Y DE LA SALUD

ESCUELA DE ENFERMERÍA

PRIMER SEMESTRE “C”

MÓDULO DE MICROSOFT EXCEL

Parte dos

2010

DOCENTE:

Mgs. Diana Yadira Gómez Montoya

2014

UNIVERSIDA TÉCNICA DE MACHALAFACULTAD DE CIENCIAS QUÍMICA Y DE LA SALUD


MICROSOFT EXCEL

FUNCIONES

Mediante el uso de los operadores matemáticos y lógicos estudiados anteriormente es probable realizar casi todas las tareas que encierran cálculos. Sin embargo para facilitar la vida del usuario, Excel posee una serie de funciones predefinidas que economizan el trabajo.

En este módulo de Excel el estudiante aprenderá a familiarizarse con las funciones de texto, funciones de fecha, funciones financieras y funciones matemáticas para que pueda resolver ejercicios complejos que incluyan el uso de estas funciones.

En esta unidad aprenderá la forma de utilizar dichas funciones.

Contenido de la Unidad:

Asistentes de funciones. De Fecha: fecha (date), ahora (now), hoy (today). Matemáticas: suma (sum), sumar.si (sumif), sumar.si.conjunto (sumifs),

producto (product), raíz de orden n (power), redondear (round), aleatorio (rand) aleatorio.entre (randbetween), entero (int), producto (product).

Objetivo del Aprendizaje

Aprender a usar las funciones que ofrece el programa Excel de tal forma que obtenga el máximo provecho que brinda este programa.

El material incluye de cada uno de los temas, el contenido necesario para que el estudiante luego de leerlo y practicar en forma individual esté en capacidad de realizar las tareas asignadas para ello.

Mgs. Diana Yadira Gómez Montoya 2



ASISTENTE DE FUNCIONES

En Microsoft Excel puede crearse una amplia diversidad de fórmulas, desde fórmulas que ejecuten una simple operación aritmética hasta fórmulas que analicen un modelo complejo de fórmulas.

Una fórmula puede contener funciones, que son fórmulas predefinidas que ejecutan operaciones simples o complejas. Para ejecutar simultáneamente varias operaciones y que se genere uno o varios resultados.

Una función como cualquier dato se puede escribir directamente en la celda si conocemos su sintaxis, pero Excel dispone de una ayuda o asistente para utilizarlas, así nos resultará más fácil trabajar con ellas.

Insertar una función con el asistente

Si queremos introducir una función en una celda debemos seguir los siguientes pasos:

Situarse en la celda donde queremos introducir la función. Hacer clic en la pestaña Fórmulas. Elegir la opción Insertar función.

O bien, hacer clic sobre el botón de la barra de fórmulas.

Aparecerá el siguiente cuadro de diálogo Insertar función:




Excel 2010 nos permite buscar la función que necesitamos escribiendo una breve descripción de la función necesitada en el recuadro Buscar una función: y a

continuación hacer clic sobre el botón , de esta forma no es necesario conocer cada una de las funciones que incorpora Excel ya que él nos mostrará en el cuadro de lista Seleccionar una función: las funciones que tienen que ver con la descripción escrita.

Para que la lista de funciones no sea tan extensa podemos seleccionar previamente una categoría del cuadro combinado O seleccionar una categoría: esto hará que en el cuadro de lista sólo aparezcan las funciones de la categoría elegida y reduzca por lo tanto la lista. Si no estamos muy seguros de la categoría podemos elegir Todas.




En el cuadro de lista Seleccionar una función: hay que elegir la función que deseamos haciendo clic sobre ésta.

Observe como conforme seleccionamos una función, en la parte inferior nos aparecen los distintos argumentos y una breve descripción de ésta. También disponemos de un enlace Ayuda sobre esta función para obtener una descripción más completa de dicha función.

A final, hacer clic sobre el botón Aceptar.

Justo debajo de la barra de fórmulas aparecerá el cuadro de diálogo Argumentos de función, donde nos pide introducir los argumentos de la función: Este cuadro variará según la función que hayamos elegido, en nuestro caso se eligió la función SUMA( ).

En el recuadro Número1 hay que indicar el primer argumento que generalmente será una celda o rango de celdas tipo A1:B4. Para ello, hacer clic sobre el botón para que el cuadro se haga más pequeño y podamos ver toda la hoja de cálculo, a continuación seleccionar el rango de celdas o la celda deseadas como primer argumento (para seleccionar un rango de celdas haciendo clic con el botón izquierdo del ratón sobre la primera celda del rango y sin soltar el botón arrástralo hasta la última celda del rango) y pulsar la tecla INTRO para volver al cuadro de diálogo.

En el recuadro Número2 habrá que indicar cuál será el segundo argumento. Sólo en caso de que existiera.

Si introducimos un segundo argumento, aparecerá otro recuadro para el tercero, y así sucesivamente.




Cuando tengamos introducidos todos los argumentos, hacer clic sobre el botón Aceptar.

Si por algún motivo insertáramos una fila en medio del rango de una función, Excel expande automáticamente el rango incluyendo así el valor de la celda en el rango. Por ejemplo: Si tenemos en la celda A5 la función =SUMA(A1:A4) e insertamos un fila en la posición 3 la fórmula se expandirá automáticamente cambiando a =SUMA(A1:A5).

En la pestaña Inicio o en la de Fórmulas encontrarás el botón Autosuma que nos permite realizar la función SUMA de forma más rápida.

Ejemplo: Se realiza una factura en la que calcularemos el subtotal y total de la misma como se representa en la figura.

Todas las funciones tienen un nombre que comienzan con el carácter = y requieren de uno o varios argumentos (que se ingresan entre paréntesis) que pueden ser datos de naturaleza diversa según el caso y/o la función.

FUNCIONES DE FECHA

Las funciones de fecha se utilizan para mostrar información acerca de fechas y horas.




FUNCIÓN AHORA –NOW

Esta función nos devuelve la fecha y la hora actual del sistema con formato de fecha y hora.

Sintaxis:

=AHORA()=NOW()

Ejemplo: Escribir en una celda para mostrar la fecha actual

=HOY()

=TODAY()

Este fecha transformada a formato general representa un número el cual en Excel para funciones de fecha lo conocen como número de serie.

FUNCIÓN AÑO




Esta función tiene como parámetro un número de serie o a la celda con formato de fecha o número y devuelve el año en formato año entre el rango de 1900-9999.

Sintaxis:

=AÑO(NUM_DE_SERIE)=YEAR(SERIAL_NUMBER)

FUNCIÓN DÍA

Devuelve el día del mes correspondiente al número de serie proporcionado o a la celda con formato de fecha o número.

Sintaxis:

= DIA(NÚM_DE_SERIE)=DAY(SERIAL_NUMBER)

Ejemplo:

Ejemplo:




Ejemplo: Calcule la edad de cada persona.

=ENTERO(($B$3-D6)/365.25)

=INT(($B$3-D6)/365.25)

FUNCIONES MATEMÁTICAS

FUNCIÓN SUMA (SUM)

La función devuelve como resultado la suma de sus argumentos.

Sintaxis:

=SUMA(número1, número2, ...) =SUM(number1, number2, ...)

Elementos de la Fórmula Descripción

= Operador que indica el inicio de la fórmula




SUMA (español)SUM (inglés)

Devuelve como resultado la suma de sus argumentos.

número1number1

Celda constitutiva para realizar la operación. (se puede utilizar también como celda inicial de un conjunto de celdas a ser utilizadas en la función)

número2number2

Celda constitutiva para realizar la operación. (se puede utilizar también como celda final de un conjunto de celdas a ser utilizadas en la función).

Ejemplo: Sumar los valores de las ventas

Se podrán utilizar tantas celdas sean necesarias expresadas como valores independientes o como un conjunto de elementos conocido como rango.

FUNCIÓN SUMA CONDICIONADA - SUMAR.SI (SUMIF)

Esta función devuelve como resultado la suma de las celdas que cumplen una condición dada.

Sintaxis:

=SUMAR.SI(rango;criterio;rago_suma)=SUMIF(range; criteria; sum_range)


= Operador que indica el inicio de la fórmulaSUMAR.SI (español)SUMIF (inglés)

Devuelve como resultado la suma de las celdas que cumplen una condición dada.

rango Área donde voy a encontrar la condición.




rangecriteriocriteria

Condición a ser evaluada

rango_sumasum_range

Área donde voy a encontrar el resultado

Para trabajar con ésta función se necesitan de datos adicionales como por ejemplo una tabla de datos a la que se hace referencia.

Ejemplo: Calcular el total de las ventas de los monitores

FUNCIÓN SUMA CON VARIAS CONDICIONES SUMAR.SI.CONJUNTO (SUMIFS)

Esta función devuelve como resultado la suma de las celdas que cumplen un determinado conjunto de condiciones o criterios.

Sintaxis:

=SUMAR.SI.CONJUNTO(rango_suma,rango1_criterio,criterio1,rango2_criterio,criterio2…)=SUMIFS (sum_range; rango1_criterio, criterio1, rango2_criterio, criterio2…)


= Operador que indica el inicio de la fórmulaSUMAR.SI.CONJUNTO (español)SUMIFS (inglés)

Devuelve como resultado la suma de las celdas que cumplen un determinado conjunto de condiciones o criterios.

rango_suma Área donde voy a encontrar el resultado.




sum_rangerango_criterios1criteria_range1

Área donde voy a encontrar la primera condición.

criterio1criteria1

Primera condición a ser evaluada

rango_criterios2criteria_range2

Área donde voy a encontrar la segunda condición.

criterio2criteria2

Segunda condición a ser evaluada

Para trabajar con ésta función se necesitan de datos adicionales como por ejemplo una tabla de datos a la que se hace referencia.

Ejemplo 1: De los datos de la Tabla 2, calcular el total del monto de depósitos realizados a partir del 27/11/2000. Los datos de la tabla constan desde la celda A12 hasta la C172




En resumen esta función suma las celdas del rango_suma siempre y cuando las celdas de rango_criterio cumplan con las condiciones de criterio.

Ejemplo 2: Con los datos que se indican en la siguiente tabla (imagen) calcular el total de la cantidad vendida por el vendedor Juan en el mes de enero.




=SUMAR.CONJUNTO.SI(C2:C9;B2:B9:”Enero”;D2:D9;”Juan”)

SUMA PRODUCTO (SUMPRODUCT)

Esta función multiplica los componentes correspondientes de las matrices dadas, y devuelve la suma de los productos.

Sintaxis:

=SUMAPRODUCTO(matriz1;matriz2;array3;...) =SUMPRODUCT(matriz1;matriz2; rango3;…)

Matriz1.- El argumento de la primera matriz cuyos componentes desea multiplicar y después sumar.

Matriz2, array3. Argumentos opcional matriz 2-255 cuyos componentes desea multiplicar y después sumar.

Ejemplo: Calcular las notas de los estudiantes de un total de 4.2 puntos.




La calificación total de cada pregunta se ve en la fila 11 y la evaluación del examen de cada estudiante con un valor entre 0(mínima) y 1(máxima) a partir de la fila 13 en adelante. Se debe dejar congelado (constante) el rango C11:L11 antes de arrastrar para encontrar los resultados de las notas finales de cada estudiante.

Se puede ver para el último estudiante que si todos los valores tienen uno (1) entonces la nota total es la máxima (4,2) en la celda M22.

Ejemplo: realizar el cálculo de una factura.

No hace falta una columna adicional llamada Valor donde estará el producto de la cantidad por el precio unitario, para luego sumar y ver la cantidad que se debe pagar. Se puede usar =SUMPRODUCT(C4:C8;D4:D8) de los 2 rangos (CANT. Y PRECIO UNIT.) para encontrar el Total a Pagar de 1010 en este caso.




FUNCIONES MATEMÁTICAS

FUNCIÓN PRODUCTO

Multiplica el valor de los argumentos.

Sintaxis:

=PRODUCTO(número1,número2,…) =PRODUCT(number1, number2,….)

Ejemplo: Halle el producto de la cantidad por el precio unitario

FUNCIÓN RAÍZ CUADRADA

El resultado es la raíz cuadrada de un número positivo.

Sintaxis:

=RAIZ(número)




=SQRT(number)

Ejemplo: calcular la raíz cuadrada de 9

RAÍZ ENÉSIMA

Ejemplo: calcular la raíz quinta del valor 32.

FUNCIÓN REDONDEAR (ROUND)

La función redondea un valor numérico a un número de decimales especificado.

Sintaxis:

=REDONDEAR(número, num_de_decimales) =ROUND(número, num_de_decimales)




FUNCIÓN ALEATORIO (RAND)

La función ALEATORIO/RAND genera números aleatorios entre 0 y 1 sin incluir sus límites.

Sintaxis:

=ALEATORIO( )

=RAND( )

FUNCIÓN ALEATORIO.ENTRE (RANDBETWEEN)

La función ALEATORIO.ENTRE/RANDBETWEEN genera números aleatorios entre un número de inicio y un número de fin incluyendo sus límites.

Sintaxis:

=ALEATORIO.ENTRE(NUMERO_INICIO;NUMERO_FIN)=RANDBETWEEN(NUMERO_INICIO; NUMERO_FIN)

Ejemplo: generar un número aleatorio entre 1 y 10

=ALEATORIO.ENTRE(1;10)




=RANDBETWEEN(1;10)

FUNCIONES FINANCERAS

Excel es una de las herramientas más potentes para trabajar con información y cálculos financieros, ofrece una amplia gama de funciones prediseñadas para este propósito.

FUNCIÓN PAGO (PMT)

Calcula el pago de un préstamo basándose en pagos constantes y en una tasa de interés constante.

Sintaxis:

=PAGO(tasa;nper;va;vf;tipo)=PMT(rate,nper,pv,fv,type)

Donde:

Tasa (rate) es la tasa de interés del préstamo. Nper es el número total de pagos del préstamo. Va (pv) es el valor actual o lo que vale ahora la cantidad total de una serie de

pagos futuros. Vf (fv) es el valor futuro o saldo en efectivo que desea lograr después de

efectuar el último pago. Si el argumento vf (fv) se omite, se asume que el valor es 0 (por ejemplo, el valor futuro de un préstamo es 0).

Tipo (type) es el número 0 (cero) o 1 e indica el vencimiento de pagos.

Defina tipo como Si los pagos vencen0 u omitido Al final del período1 Al inicio del período

Observaciones

El pago devuelto por la función PAGO (PMT) incluye el capital y el interés, pero no incluye impuestos, pagos en reserva ni los gastos que algunas veces se asocian con los préstamos.




Mantenga uniformidad en el uso de las unidades con las que especifica los argumentos tasa (rate) y nper. Si efectúa pagos mensuales de un préstamo de 4 años con un interés anual del 12 por ciento, use 12%/12 para el argumento tasa y 4*12 para el argumento nper. Si efectúa pagos anuales del mismo préstamo, use 12 por ciento para el argumento tasa y 4 para el argumento nper.

Sugerencia: Para encontrar la cantidad total que se paga durante la duración del préstamo, multiplique el valor devuelto por PAGO (PMT) por el argumento nper.

Ejemplos:

La siguiente fórmula devuelve el pago mensual de un préstamo de $ 10.000 con una tasa de interés anual del 8 por ciento pagadero en 10 meses:

=PAGO(B13/12; 10; 10000) es igual a -1.037,03 $

=PMT(B13/12; 10; 10000)

Usando el mismo préstamo, si los pagos vencen al comienzo del período, el pago es:

=PAGO(B3/12; 10; 10000; 0; 1) es igual a -1.030,16 $

=PMT(B3/12; 10; 10000; 0; 1)




La siguiente fórmula devuelve la cantidad que se deberá pagar cada mes si presta $ 5.000 durante un plazo de cinco meses a una tasa de interés del 12 por ciento:

=PAGO(B3/12;B2; -B1) es igual a $1030,20

=PMT(B3/12;B2; -B1)

Puede utilizar PAGO (PMT) para determinar otros pagos anuales.

Por ejemplo, si desea ahorrar $ 50.000 en 18 años, ahorrando una cantidad constante carda mes, puede utilizar PAGO (PMT) para determinar la cantidad que debe ahorrar, asumiendo que podrá devengar un 6 por ciento de interés en su cuenta de ahorros.

=PAGO(6%/12; 18*12; 0; 50000) es igual a -129,08 $

=PMT(6%/12; 18*12; 0; 50000)




FUNCIÓN PAGO PRINCIPAL (PAGO PRIN) (PPMT)

Retorna el pago principal de un período dado basado en períodos, pagos constantes, y tarifa de interés constante.

Sintaxis:

=PAGOPRIN(tasa,No.pagos,nper,va,[vf],[tipo])=PPMT(rate,per,nper,pv,[fv],[type])

Donde:

Tasa (rate) es la tasa de interés del préstamo. No. Pagos (per) especifica el periodo y debe ser en el rango de 1 hasta nper. Nper es el número total de pagos del préstamo. Va (pv) es el valor actual o lo que vale ahora la cantidad total de una serie de




Ejemplo: La siguiente fórmula devuelve el pago principal de un préstamo de $ 10.000 con una tasa de interés anual del 8 por ciento pagadero en 10 meses para el período número 1.

=PAGOPRIN(8%/12;1;10;10000) es igual a $ -970,37 =PPMT(8%/12;1;10;10000) es igual a $ -970,37




Ejemplo:

FUNCIÓN PAGO INTERÉS PAGOINT - IPMT

Retorna el pago de interés de un período dado basado en períodos, pagos constantes, y tarifa de interés constante.

=PAGOINT(tasa,No.pagos,nper,va,[vf],[tipo])=IPMT(rate,per,nper,pv,[fv],[type])




Donde:

Tasa (rate) es la tasa de interés del préstamo. No. Pagos (per) especifica el periodo y debe ser en el rango de 1 hasta nper. Nper es el número total de pagos del préstamo. Va (pv) es el valor actual o lo que vale ahora la cantidad total de una serie de




Ejemplo: La siguiente fórmula devuelve el pago que se debe pagar de interés por un préstamo de $ 10.000 con una tasa de interés anual del 8 por ciento pagadero en 10 meses para el período número 1.

=PAGOINT(8%/12;1;10;10000) es igual a $ -,37

=IPMT(8%/12;1;10;10000) es igual a $ -970,37

TABLA DE AMORTIZACIÓN

Para realizar una Tabla de Amortización se deben usar las funciones financieras Pago, Pago (PMT)(que calcula el pago de un préstamo basándose en pagos constantes y a una tasa de interés constante), Principal PPMT (recuperación del capital) y Pago del Interés (IPMT) (recuperación de interés).




La Tabla de Amortización es una tabla en la que se calcula para cada período (cuota) los resultados de la cuota constante a pagar, el pago principal, el pago de interés, y los acumulados. El gráfico ilustra un ejemplo.

En la columna Períodos se genera la serie desde 1 hasta el número total de cuotas, (o la que se requiera según el ejercicio).

En la columna Pago Mensual se usa la función Pago (PMT) para calcular la cuota constante a cancelar al banco. Como los pagos son mensuales el interés anual que está en la celda C9 debe ser dividida entre 12. Es de notar que las tres celdas deben estar absolutas antes de copiar hacia las celdas inferiores. El signo menos antes de $C$8 permite generar un resultado positivo del Pago Mensual en lugar de un negativo.

La función Pago principal (PPMT) en la columna Capital Recuperado requiere 4 datos: La tasa de interés dividida entre 12 porque los pagos son mensuales, el número del período o cuota, el número total de cuotas y el préstamo cambiado de signo. Se debe notar que todas las celdas están absolutas, excepto la celda A14 que es el número del período. Cada valor que resulta en la columna PPMT es diferente mes a mes (período a período) desde un número menor hasta un número mayor.




La función IPMT en la columna Interés Pagado requiere de 4 datos también y son exactamente iguales a los que se pusieron en PPMT. Los resultados que aparecen van desde los más grandes a los más pequeños, lo que se interpreta que el banco se preocupa de recuperar lo más rápidamente posible los intereses antes que el capital.

Para las dos últimas columnas donde se acumula el capital y el interés, se usan dos fórmulas diferentes en las dos primeras celdas. En la primera celda del Capital Acumulado se copia el primer valor de Capital Recuperado con la fórmula =C14, mientras que en la segunda celda se usa la fórmula =E14+C15 que acumula los pagos. Esta segunda celda es la que se debe arrastrar para generar toda la columna de la tabla.

En forma análoga se trabajaría con la última columna que es el Interés Acumulado, es




decir, primero se copia el contenido de la celda =D14 que es el primer pago y luego se acumula con la fórmula =F14+D15 para el segundo pago, con lo que se arrastra esta última celda para generar toda la tabla.

ACTIVIDADES:

Resuelva las siguientes actividades:

• Con la siguiente tabla de datos, calcule las ventas de cada uno de los productos.

• Usando la función adecuada, evalúe la siguiente ecuación:




• Usando la siguiente tabla de datos, obtenga la raíz cuadrada de las ventas.

• Usando la siguiente tabla, y usando la función adecuada redondee los valores de la raíz cuadrada con 3 decimales.




En la siguiente tabla de datos, genere números aleatorios comprendidos del 200 al 800 para los meses de Enero, Febrero y Marzo.

EJEMPLOS DESARROLLADOS

• Con la siguiente tabla de datos, calcule las ventas de cada uno de los productos.




Para calcular las ventas de cada uno de los productos, lo que tenemos que hacer es realizar una multiplicación de la Cantidad por el Valor Unitario usando la función que corresponda, en este caso será Producto (Product), por lo tanto, ubicamos el cursor en la celda que corresponda y posteriormente aplicamos la función, así:

Aplicado para toda la tabla tendremos el siguiente resultado:

Usando la función adecuada, evalúe la siguiente ecuación:




Para realizar la evaluación de la ecuación presentada, ubicamos el cursor en la primera celda correspondiente, y con el uso de la función Potencia (Power) procedemos a ir reemplazando los valores, de la siguiente manera:

=POTENCIA ((A6^4+2);(1/3))+5*A6

=POWER ((A6^4+2); (1/3)) + 5*A6




Aplicando para toda la tabla, tendremos el siguiente resultado:

Ahora, podemos llegar al mismo resultado sin usar la función, únicamente reemplazando los valores de la siguiente manera:

= ((A6^4+2)^(1/3))+5*(A6)




• Usando la siguiente tabla de datos, obtenga la raíz cuadrada de las ventas.

Para obtener la raíz cuadrada de las ventas, ubicamos el cursor en la celda correspondiente y aplicamos la función correspondiente para obtener la raíz cuadrada que es Sqrt (Sqrt), de la siguiente manera:




Aplicado para toda la tabla tendremos el siguiente resultado:

Usando la siguiente tabla, y usando la función adecuada redondee los valores de la raíz cuadrada con 3 decimales.




Para redondear los valores de la raíz cuadrada, ubicamos el cursor en la celda correspondiente y aplicamos la función que corresponde, en este caso será Redondear (Round), así:

Aplicando a toda la tabla tendremos el siguiente resultado:

Copiamos la misma fórmula para las demás celdas y tendremos el siguiente resultado:




Copiamos la misma fórmula para las demás celdas y tendremos el siguiente resultado:

Cuando se trabaja con la función Aleatorio.entre (Randbetween), y para que no se cambien los valores, lo que debemos hacer es copiar los valores (todas las celdas que tiene la función) y volver a pegarla como valor, de la siguiente manera:




Una vez que se ha copiado, en el menú Inicio (Home), elegimos la opción de Pegar (Paste) y Pegado Especial (Paste Special)

0

• En la ventana que nos aparece, elegimos la opción Valor (Value), así:

Presionamos la tecla Aceptar (OK), y tendremos el siguiente resultado:




ACTIVIDADES:

Resuelva los ejercicios planteados:

• Usando la siguiente tabla de datos unifique en una columna el nombre y el apellido de los empleados.

Usando las funciones de fecha, calcule la edad de los funcionarios que se encuentran en la siguiente tabla:




El Gerente de la Empresa AAA, desea realizar un préstamo al Banco de 30.000 dólares, el Banco le concede dicho préstamo para que pague al 15% de interés a 6 años plazo, y debe pagar trimestralmente. Se necesita saber a cuanto asciende la cuota trimestral que debe cancelar el Gerente?.

Con los datos del ejercicio anterior, se necesita saber cuanto de dinero está pagando el Sr. Gerente por concepto de Capital y de Interés para el primer período.

Con la siguiente tabla de Notas se necesita saber a cuanto asciende la Nota Total de cada uno de los estudiantes.

Con la siguiente tabla de datos, se necesita saber a cuanto ascienden las ventas por cada uno de los productos.


Microsoft excel parte 2

Automotive

Transcript of Microsoft excel parte 2