sesión 5 grp i comp ii

Comunícate con nosotros. 481614 // PAC Presencial: [email protected] // PAC Virtual: [email protected]

MICROSOFT EXCEL 2013

Tema N° 09:

9.1. Funciones de Búsquedas

9.1.1 Búsquedas Verticales

9.1.2 Búsquedas Horizontales

mailto:[email protected]



¿Qué función en Excel nos permite consultar información para mostrar

resultados en base a la información consultada?

¿En qué casos puedes utilizar estas funciones de búsquedas?

¿Cómo se puede diferenciar el uso de una función SI Anidada y funciones

de búsquedas? ¿Qué criterios se debe evaluar para su respectivo uso?

Conocimientos

Previos

¿Qué soluciones podemos efectuar para analizar múltiples criterios de

evaluación?

¿Cómo podemos determinar y capturar la información de una fila o columna

en una tabla de datos con solo un dato de ella?

Conflicto Cognitivo

Utiliza la función de búsquedas de datos para organizar la información de

manera adecuada de una hoja de cálculo.

Capacidad del Tema




9.1 Funciones de Búsqueda:

Nos permite buscar un valor dentro de un rango de celdas y como resultado nos devolverá el valor correspondiente del rango de resultados que especifiquemos. La función BUSCAR se puede utilizar en forma vectorial o en forma matricial. Entre las principales funciones empleadas en Excel tenemos la función BUSCARV y la función BUSCARH.

9.1.1 Función BUSCARV

La función BUSCARV es la más utilizada ya que permite buscar elementos en una tabla en forma vertical, es decir desde la fila más superior hasta la última fila de la tabla. Su sintaxis es:

=BUSCARV(Valor_buscado, Matriz_buscar_en,Indicador_columnas,

Ordenado)

1) Valor_buscado: se entiende como el criterio que se va a buscar en la primera columna de la matriz de tabla que puede ser un valor o una referencia.

2) Matriz_buscar_en: Dos o más columnas de datos. Usa una referencia a un rango o un nombre de rango. Estos valores pueden ser texto, números o valores lógicos. Las mayúsculas y minúsculas del texto son equivalentes

3) Indicador_columnas: es un número entero que específica que columna de la matriz auxiliar deseas mostrar en la tabla general.

4) Ordenado: Es el valor lógico que especifica si la función Excel BUSCARV va a buscar una coincidencia exacta o aproximada:

Por ejemplo, dado una tabla con el listado de trabajadores, se les solicita que busquen para cada trabajador de acuerdo al CODIGO, sus Apellidos, Nombres, Edad y Ciudad.

Imagen 1:Asistente de función BuscarV




Imagen 2: Ejercicio aplicando Funciones de Búsquedas.

En el ejemplo podemos distinguir la tabla con todos los trabajadores, pero nos ubicamos en la celda B17 y allí digitamos la función BUSCARV para hallar los APELLIDOS del trabajador 2830.

=BUSCARV (B16,DATOS,2,FALSO)

De donde:

B16 Es el código a ubicar en la tabla A2:E12 (DATOS) Es el rango de datos de la tabla.

Valor 2 Es la posición del apellido en la tabla Valor FALSO Indica que es una búsqueda Exacta

Tabla 1: Argumentos Búsquedas Verticales

Completando los demás datos quedaría de la siguiente manera:

Utilizamos la función

BUSCARV

Le a

sig

na

mo

s u

n n

om

bre

a la

ta

bla

: D

AT

OS




Imagen 3: Sintaxis Búsquedas Verticales

9.1.2 Función BUSCARH

La función BUSCARH es también importante y muy utilizada ya que permite buscar elementos en una tabla en forma horizontal, es decir desde la columna inicial hasta la columna final de la tabla.

Su sintaxis es:

=BUSCARH(Valor_buscado, Matriz_buscar_en, Indicador_Filas,

Ordenado)

La función es similar a la función BUSCARV con la diferencia que el tercer parámetro o argumento ya no es Nro-Columna sino cambia a ser Nro-Fila.

Por ejemplo, se solicita mostrar para cada año el Precio de las Cotizaciones de las Acciones, teniendo las siguientes consideraciones: precio mayor, precio menor e intervalo.

=BUSCARV(B15,DATOS,2,FALSO)



=BUSCARV (B15,DATOS,5,FALSO)




Imagen 4: Sintaxis Búsquedas Horizontales

En la tabla podemos apreciar los años organizados en columnas distintas, por lo que la función a utilizar seria BUSCARH, y se pide para un año dado (ejemplo 2008) hallar los precios dados.

Nos ubicamos en la celda D11 para encontrar el precio más alto para el año 2008 y la función seria

=BUSCARH (D9, TABLA, 2, FALSO)

En donde:

D9 Es el año a consultar B3:G6 Es la tabla o matriza consultar( TABLA ) 2 Es el indicador de posición de la fila Máximo (1 esta los años, 2 Máximo, 3

Mínimo, 4 Intervalo). FALSO Búsqueda exacta

Tabla 2: Argumentos Búsquedas Horizontales

=BUSCARH(D8,TABLA,2,FALSO)






Ejemplo 01: Un profesor tiene una tabla con las notas de un alumno puestas en números y quiere completarla poniendo las notas en palabras

Para hacer esto cuenta con otra tabla de equivalencias:

Imagen 6: Ejemplo1. Tabla de equivalencias.

Aplicando la formula:

Donde B2: Es la primera nota de la tabla1 de la hoja1.

ESCALA: es el nombre que se ha asignado a la tabla2 de la hoja2 (asignación de nombres a un conjunto de valores).

2: es el indicador de columna de la tabla2 de la hoja2 que deseas mostrar en la tabla1.

A continuación se arrastra la función hasta completar la tabla con el puntero de relleno.

Ingresa los datos de las tablas en diferentes hojas

= BuscarV ( B2, ESCALA, 2)

Imagen 5: Ejemplo1. Calificación




Imagen 7: Ejemplo1. Calificación Resultados

El significado de la “V” en el nombre de la función refiere a que hace una búsqueda vertical que en la versión 2010 se denomina BUSCARV.

Ejemplo 02: Se quieren obtener la cantidad de frutas vendidas por la empresa FrutasFrescas.SAC, para ello utilizaremos la función Excel BUSCARV. En la imagen anexa se muestra la tabla de donde se desean obtener los datos, solo debes ingresar por teclado el código y se debe mostrar el nombre de la fruta y la cantidad vendida en las celdas señaladas.

Imagen 8: Ejemplo2. Frutas

=BUSCARV(C13;FRUTAS;2)

C13: Representa el valor buscado que en este caso es el código a ingresar por teclado.

FRUTAS: Representa la matriz donde se van a buscar los datos.

En este caso la matriz de búsqueda está en otra hoja, pero puede estar en cualquier lado, incluso dentro de otra tabla.

Este valor se ingresa por teclado.

Aplicar la formula BUSCARV.




2: Representa el indicador de columnas de donde se va a extraer la información

El resultado final del ejemplo sería algo como lo que se muestra a continuación:

Imagen 9: Ejemplo2. Frutas Resultados




Autoevaluación:

1) ¿Qué diferencia o similitud existe entre las Funciones de Búsquedas verticales y

Búsquedas Horizontales.

__________________________________________________________________

__________________________________________________________________

__________________________________________________________________

2) ¿Mayormente una base de datos de una determinada empresa como se ubican sus

campos de manera horizontal o vertical? ¿Y cómo le darías nombre a una

determinada base de datos, describe los pasos?

__________________________________________________________________

__________________________________________________________________

__________________________________________________________________

3) Describe los argumentos de la sintaxis que debo utilizar para elaborar mis funciones

de búsquedas verticales u horizontales. ¿En qué argumento existe diferencia?

__________________________________________________________________

__________________________________________________________________

__________________________________________________________________




Práctica del Tema A Transcribe los siguientes datos en una hoja de Excel y utilizando la función de búsquedas calcular el Producto,

Plan, Sede. La celda Código debes validarla para que se visualice todos los códigos existentes en la base de

datos.

Imagen 10: Ejercicio Propuesto Productos

B Formato Condicional:

Aquellos productos IBM del Plan3 y que sean de ICAasignar fuente Azul claro en negrita con relleno

Anaranjado claro.



Comunícate con nosotros.

481614//PAC Presencial:[email protected]//PAC Virtual:[email protected]

Referencias Bibliográficas del Tema

1. Funciones en Excel (2012) en http://exceltotal.com/funciones/

2. Aprenda Excel Paso a Paso (2013), “Manejo de la Función Si”, en

http://expertoenexcel.wordpress.com/tag/funciones-logicas-en-excel/

3. Funciones Lógicas 2010 (2012), Funciones Lógicas en

http://teleform.wordpress.com/2012/09/26/funciones-logicas-en-excel-2010/.

4. Microsoft. (s.f.). Plataforma Educativa de Microsoft IT Academy Program.

Obtenido de http://itacademy.microsoftelearning.com/spain/



http://expertoenexcel.wordpress.com/tag/funciones-logicas-en-excel/

http://teleform.wordpress.com/2012/09/26/funciones-logicas-en-excel-2010/



MICROSOFT EXCEL 2013

Tema N° 10:

Funciones Financieras

1. Función VF

2. Función VA

3. Función TASA

4. Función NPER

5. Función PAGO





Utiliza las funciones Financieras básicas en una hoja de cálculo de

Excel.

Capacidad del Tema

¿Qué funciones en Excel me permite analizar grandes cantidades de datos, para evaluar varias condiciones? ¿Por qué?

Conocimientos

Previos

¿Qué pasaría si deseo comprar un producto pero no tengo el dinero

suficiente? ¿Sera lo mismo comprar en efectivo o al crédito? ¿Se generarían

intereses? ¿Cuál sería el monto final a devolver?

Conflicto Cognitivo





Funciones Financieras 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 que te

ayudarán a realizar tareas sencillas con relación a tus finanzas; permitiendo ejecutar una variedad de cálculos financieros, incluyendo los cálculos de rendimiento, evaluaciones de inversión, tasas de interés, tasa de retorno, depreciación de activos y los pagos. También ayudan en la contabilidad de pequeñas empresas y grandes. Las principales funciones financieras comúnmente utilizadas en Excel son: VF, PAGO, VA, TASA, NPER,

las cuales forman parte de las operaciones comúnmente utilizado en el Interés Compuesto.

Las funciones financieras calculan información financiera como, por ejemplo, el valor neto presente, pagos, tasas y el tiempo en pagar un préstamo. Por ejemplo, puede calcular los pagos mensuales requeridos para comprar un auto a una determinada tasa de interés utilizando la función pago. Definiciones Básicas:

Interés: Se entiende como una cantidad que se paga por hacer uso de dinero solicitado en préstamo o bien por la cantidad

obtenida al invertir un capital. Diferencia entre Interés Simple y Compuesto:

¿Cuánto pagare de intereses por comprarme este grabadora? .. uhmm

Imagen 11: El valor del dinero en el tiempo.

Imagen 12: Compra al crédito genera intereses





Interés Simple: Interés Simple solo capitaliza una vez y se trabaja con Tasas nominales. El interés solo se calcula en base al capital inicial. Interés Compuesto: Se capitaliza varias veces a lo largo del tiempo. El préstamo e interés crece en forma exponencial mientras termines de pagar el préstamo. Se trabaja con tasas efectivas. Ejemplo: (Diferencia entre Interés Simple e Interés Compuesto) Mi padre me prestó S/. 100.00 nuevos soles el cual será devuelto en 5 meses para lo cual me cobra una tasa del 4%. ¿Cuál será el interés que se genere y el dinero a devolver?

Imagen 13: Cálculo del Interés Simple





¿En dónde pago más?, ¿Cuál fue el monto a devolver en 5 meses?

El Valor Futuro crece en forma exponencial a lo largo del tiempo en el interés compuesto, mientras en el Interés simple la capitalización se realiza una sola vez, al final de la operación.

Función Descripción

NPER( tasa; pago; va; vf; tipo) Devuelve el número de pagos de una inversión

PAGO( tasa; nper; va; vf; tipo) Devuelve el pago de un préstamo basado en pagos y tasas de interés constantes

TASA( nper; pago; va; vf; tipo; estimar)

Devuelve la tasa de interés por periodo de un préstamo o una inversión

VA( tasa; nper; pago; vf; tipo) Devuelve el valor actual de una inversión

VF( tasa; nper; pago; vf; tipo) Devuelve el valor futuro de una inversión basada en pagos periódicos y constantes más una tasa de interés constante

Tabla 3: Funciones Financieras Básicas

Para acceder a las funciones Financieras de Excel hacemos clic en la ficha Fórmulas y dentro del Grupo Biblioteca de Funciones elegimos Insertar Función, luego en el cuadro de diálogo elegimos la Categoría Financieras y la función deseada.

Imagen 14: Cálculo del Interés Compuesto





Imagen 15: Asistente de Funciones Financieras

Luego al hacer clic en la función deseada nos va a mostrar los argumentos a tener en cuenta para obtener el resultado solicitado.

Imagen 16: Argumentos de F. Financieras

Estos argumentos suelen ser los mismos para la mayoría de las funciones financieras, siendo a la vez cada uno de estos argumentos, Funciones financieras. Estos argumentos en tener en cuenta son:

Argumento Descripción

VA Valor presente o actual de la inversión

VF Valor futuro

PAGO Serie uniforme, Cuota o Renta a realizar.





TASA Tasa de interés

NPer Número de periodos

Tipo Especifica si el tipo de interés es vencido (al final del periodo valor = 1) ó anticipado (al inicio del periodo valor = 0)

Tabla 4: Definición F. Financieras Básicas

Entre las principales funciones financieras tenemos:

1. Función VF.-

Esta función financiera devuelve el valor futuro de una inversión para un determinado periodo de tiempo.

=VF(Tasa, NPer, Pago, VA, Tipo) ggg

Un ejemplo utilizando función VF. Si usted invierte 5000 soles a una tasa de interés de 10% anual, ¿Cuál será el valor equivalente en 4 años? Solución: Las variables identificadas en el ejemplo son las siguientes:

NPer = 4 años VA = - s/. 5000 (negativo porque es egreso no ingreso) Tasa = 10% anual VF = ¿? La función quedaría así: =VF( 10%, 4, , -5000, 0) cuyo resultado será: VF = S/. 7320.50 soles Utilizando el cuadro de diálogo de la función VF sería:

Imagen 17: Valor Final





Imagen 18: Argumentos Función Valor Final

2. Función VA

Esta función financiera devuelve el valor actual, presente (lo que vale ahora una serie de pagos futuros).

=VA(Tasa, NPer, Pago, VF, Tipo) .

Un ejemplo utilizando función VA. Dentro de 3 años usted va a pagar 12096 soles, de un crédito que recibe hoy a una

tasa de interés anual de 20%, ¿Cuál es el valor del crédito? Solución: Las variables identificadas en el ejemplo son las siguientes: NPer = 3 años VF = - s/. 12090 (negativo porque es un pago o sea egreso) Tasa = 20% anual VA = ¿? La función quedaría así: =VA( 20%, 3, , -12096, 0) cuyo resultado será: VF = S/. 7000 soles Utilizando el cuadro de diálogo de la función VA sería:

Imagen 19: Valor Inicial





Imagen 20: Argumentos función Valor Actual

3. Función PAGO

Calcula en pago de un préstamo basándose en pagos constantes y en una tasa de interés constante. Nper es el número total de pagos del préstamo, Va es el valor actual o lo que vale ahora la cantidad de una serie de pagos

futuros, Vf es el valor futuro o el saldo en efectivo que desea lograr tras efectuar el último pago ( si se omite se toma el valor cero 0) y tipo indica con 0 ó 1 el vencimiento del pago, 0 (por defecto) significa que los pagos se hacen al final del periodo, 1 que se hacen al principio del periodo.

=PAGO(Tasa, NPer, Va, Vf, Tipo)

Por ejemplo: Se va a realizar un estudio de un Préstamo Hipotecario, el préstamo solicitado es de 9,000.00 soles pagadero en 15 meses. El banco nos ofrece un interés mensual del 3.5%. Partiendo de estos datos, y considerando que el interés no va a cambiar, calcular el valor del pago a realizar mensual. Solución: Tasa Interés = 3.5% mensual Periodo = 15 meses VA = - S/. 9,000.00 soles (valor actual del préstamo solicitado, negativo por ser egreso)

Imagen 21: Calculando el Pago





=PAGO ( 3.5%, 15, - 9000) El resultado S/. 781.43 soles mensuales. Utilizando el cuadro de diálogo de la función PAGO sería:

Imagen 22: Argumentos Función Pago

4. Función TASA

Devuelve la tasa de interés por periodo de una anualidad. Tipo indica el vencimiento de los pagos (0 al inicio del periodo, 1 al final) y estimación es la estimación de la tasa de interés.

=TASA( NPer, pago, VA, VF, tipo, estimación) .

Por ejemplo, Enrique realizó un préstamo de S/. 8,000.00 nuevos soles a 8 meses y terminó pagando S/. 11,000.00 nuevos soles. ¿Cuál fue la tasa que la entidad financiera le cobró? Solución Nper = 8 meses Va = S/. 8,000.00 soles (valor actual del préstamo) Vf = S/. 11,000.00 soles (valor final del préstamo)

Imagen 23: Cálculo Tasa





Imagen 24: Argumentos Función TASA

=TASA (8, 0, -8000, 11000) El resultado Tasa de Interés 4% Mensual

5. Función NPER

Devuelve el número de periodos de una inversión, donde: tasa es la tasa de interés por periodo, pago es el pago efectuado en cada periodo, Va es el valor actual o la suma total de una serie de futuros pagos, Vf es el valor final, futuro o el saldo en efectivo que se desea lograr después del último pago (si se omite se toma el valor 0) y tipo indica con 0 o 1 el vencimiento del pago, 0(por defecto) significa que los pagos se hacen al final del periodo, 1 que se hacen al principio del periodo.

=NPER( TASA, -pago, -VA, VF, tipo) .

Por ejemplo: Una inversión genera el 1% de interés mensual, con cuotas de S/. 100 soles y un desembolso inicial de S/ 1000 soles, en donde el monto a un futuro es de S/. 10000 soles. ¿Cuál es el periodo de tiempo que existe para obtener el reintegro de la inversión? Solución Los argumentos son los siguientes: Tasa = 1% mensual VF = S/.10,000.00 soles VA = -S/1,000.00 (negativo por ser egreso) Pago = -S/ 100 soles. NPer =¿?

Imagen 25: Plazo de un préstamo.





Imagen 26: Argumentos Función NPER

= NPER(1%, -100, -1000, 10000). El resultado 60.082123 meses (60 meses=5años).





Autoevaluación 4) ¿Qué elementos conforman el Interés compuesto?

___________________________________________________________

____________________________________________________________

___________________________________________________________

5) ¿Qué tipo de interés se aplica cuando uno realiza un préstamo en una

entidad bancaria?

___________________________________________________________

_______________________________________________________

_______________________________________________________

6) Diferencia entre Interés Simple e Interés Compuesto

___________________________________________________________

_______________________________________________________

_______________________________________________________




Práctica del Tema 1. ¿Cuál es el monto acumulado en una cuenta con principal constante de

28,000 soles y con una TEA de 15% durante un plazo de 7 años?

2. Se pide calcular el valor del depósito que efectuó hace 8 años con una tasa

de interés efectiva del 3.0 % trimestral, si hoy recibió 25 000 soles.

3. ¿Cuál es la tasa efectiva mensual que dio la entidad financiera para que se

triplique sus ahorros en 17 años?

4. Se pide calcular el monto del depósito de un capital de US$ 2,800 que

colocado a la tasa de interés efectiva del 2.0 % trimestral, se retira al cabo

de 2 años 9 meses y 20 días.




Referencias Bibliográficas del Tema

ALIAGA VALDEZ, Carlos; ALIAGA CALDERÓN, Carlos. Funciones y

herramientas de Excel para la gestión financiera. Lima: Ciencia, Investigación

y Tecnología, 2008.

Microsoft Excel 2007. Editorial: Megabyte, 2007, Lima

Excel Básico. Editorial: Macro, 2007, Lima

Funciones Financieras en Excel 2010 (2010), Funciones Financieras en

http://www.taringa.net/posts/ciencia-educacion/9454221/Funciones-Financierasen-

Excel-2010.html

Microsoft. (s.f.). Plataforma Educativa de Microsoft IT Academy Program.

Obtenido de http://itacademy.microsoftelearning.com/spain/



http://www.taringa.net/posts/ciencia-educacion/9454221/Funciones-Financierasen-Excel-2010.html

http://www.taringa.net/posts/ciencia-educacion/9454221/Funciones-Financierasen-Excel-2010.html

sesión 5 grp i comp ii

Documents