Sesión 5 Grp I Comp II

MICROSOFT EXCEL 2013 Tema N° 09: 9.1. Funciones de Búsquedas 9.1.1 Búsquedas Verticales 9.1.2 Búsquedas Horizontales

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

Capacidad del Tema

Utiliza la función de búsquedas de datos para organizar la información de manera adecuada de una hoja de cálculo.

Conocimientos

Previos ¿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?

Conflicto Cognitivo

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


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)

Imagen 1:Asistente de función BuscarV

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.


Le asignamos un nombre a la tabla: DATOS

Utilizamos la 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 A2:E12 (DATOS) Valor 2 Valor FALSO

Es el código a ubicar en la tabla Es el rango de datos de la tabla. Es la posición del apellido en la tabla Indica que es una búsqueda Exacta

Tabla 1: Argumentos Búsquedas Verticales

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


=BUSCARV(B15,DATOS,2,FALSO) =BUSCARV(B15,DATOS,3,FALSO) =BUSCARV(B15,DATOS,4,FALSO) =BUSCARV (B15,DATOS,5,FALSO) 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.


=BUSCARH(D8,TABLA,2,FALSO) =BUSCARH(D8,TABLA,3,FALSO) =BUSCARH(D8,TABLA,4,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 B3:G6 2 FALSO

Es el año a consultar Es la tabla o matriza consultar( TABLA ) Es el indicador de posición de la fila Máximo (1 esta los años, 2 Máximo, 3 Mínimo, 4 Intervalo). Búsqueda exacta Tabla 2: Argumentos Búsquedas Horizontales


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 Ingresa los datos de las tablas en diferentes hojas

Imagen 5: Ejemplo1. Calificación

Para hacer esto cuenta con otra tabla de equivalencias:

Imagen 6: Ejemplo1. Tabla de equivalencias.

Aplicando la formula:

= BuscarV ( B2, ESCALA, 2)   

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.


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

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.

Este valor se ingresa por teclado. Aplicar la BUSCARV.

formula

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.




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.


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/

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

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


Capacidad del Tema

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

Conocimientos Previos

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

Conflicto Cognitivo

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


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, Imagen 11: El valor del dinero en el tiempo. 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: ¿Cuánto pagare de intereses por comprarme este grabadora? .. uhmm Interés: Se entiende como una cantidad que se paga por hacer uso de dinero solicitado Imagen 12: Compra al crédito genera intereses en préstamo o bien por la cantidad obtenida al invertir un capital. Diferencia entre Interés Simple y Compuesto:


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


Imagen 14: Cálculo del Interés Compuesto

¿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 NPER( tasa; pago; va; vf; tipo) PAGO( tasa; nper; va; vf; tipo) TASA( nper; pago; va; vf; tipo; estimar) VA( tasa; nper; pago; vf; tipo) VF( tasa; nper; pago; vf; tipo)

Descripción Devuelve el número de pagos de una inversión Devuelve el pago de un préstamo basado en pagos y tasas de interés constantes Devuelve la tasa de interés por periodo de un préstamo o una inversión Devuelve el valor actual de una inversión 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 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 VA VF PAGO

Descripción Valor presente o actual de la inversión Valor futuro Serie uniforme, Cuota o Renta a realizar. Comunícate con nosotros.

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

TASA NPer Tipo

Tasa de interés Número de periodos 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?

Imagen 17: Valor Final

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 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 Imagen 19: Valor Inicial 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 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) Imagen 21: Calculando el Pago

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) Comunícate con nosotros. 481614//PAC Presencial:[email protected]//PAC Virtual:[email protected]

=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) . Imagen 23: Cálculo Tasa

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 24: Argumentos Función TASA

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

5. Función NPER

Imagen 25: Plazo de un préstamo.

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 =¿? Comunícate con nosotros. 481614//PAC Presencial:[email protected]//PAC Virtual:[email protected]

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-FinancierasenExcel-2010.html

 Microsoft. (s.f.). Plataforma Educativa de Microsoft IT Academy Program. Obtenido de http://itacademy.microsoftelearning.com/spain/


Sesión 5 Grp I Comp II

Recommend Documents