¡Descarga Utilización de funciones lógicas y búsqueda en Excel y más Resúmenes en PDF de Matemática Elemental solo en Docsity!
PROCESO DE GESTIÓN DE FORMACIÓN PROFESIONAL INTEGRAL
GUÍA DE APRENDIZAJE 2 DE EXCEL
IDENTIFICACIÓN DE LA GUIA DE APRENDIZAJE
TECNÓLOGO GESTIÓN BANCARIA Y DE ENTIDADES FINANCIERAS
● Código del Programa de Formación: 121202
● Nombre del Proyecto ( si es formación Titulada)
● Fase del Proyecto: Planeación
● Actividad de Proyecto(si es formación Titulada)
● Competencia: APLICAR TECNOLOGÍAS DE LA INFORMACIÓN TENIENDO EN CUENTA LAS
NECESIDADES DE LA UNIDAD ADMINISTRATIVA.
● Resultados de Aprendizaje Alcanzar: UTILIZAR HERRAMIENTAS OFIMÁTICAS REQUERIDAS
PARA EL DESEMPEÑO DE LAS FUNCIONES SEGÚN EL CARGO ASIGNADO
● Duración de la Guía: 1 sesión
2. PRESENTACIÓN
La elaboración de fórmulas en Excel no solo se limita a los operadores suma (+), resta (-), multiplicación (*)
y la división (/) ó como también las funciones matemáticas, sino que podemos comparar valores de diferente
tipo que retornan valores de falso o verdadero y esto lo permite las funciones lógicas.
Estas funciones de Excel permiten ejecutar alguna acción dependiendo de si la comparación que hagamos
es verdadera o falsa. La mas utilizada es la función lógica SI pero hay otras funciones que nos van a
permitir complementar mejor el planteamiento en caso de que existan mas de dos comparaciones con otros
tipo de datos como por ejemplo en caso de generar errores.
Otra de las funciones importantes que se manejan dentro de Excel es Realizar búsquedas cuando existen
grandes cantidades de datos en un libro, una hoja o un rango de celdas de Ms Excel, es un procedimiento
que se debe hacer teniendo en cuenta ciertas características tales como la ubicación, como está
estructurada esa información, si están ordenados, si hay celdas en blanco, si contienen imágenes o si
contienen errores solo por mencionar algunas. Por esta razón es importante hacer uso adecuado de las
funciones que nos brinda Excel para ese propósito.
3. FORMULACIÓN DE LAS ACTIVIDADES DE APRENDIZAJE
3.1 Actividades de reflexión inicial
En la vida cotidiana es muy común encontrarnos con situaciones en las que debemos actuar de un modo u
otro, dependiendo de las condiciones, por ejemplo cuando hacemos una compra con una tarjeta de crédito.
Tenemos en este caso dos posibilidades: se aprueba la compra del artículo o se rechaza por diferentes
circunstancias. Se deberá tomar alguna acción dependiendo de este resultado. Ya esta acción la define el
usuario que sea pagar en efectivo o utilizar otra tarjeta. pero lo mas importante es tener presente las
posibilidades a realizar en caso de suceder la acción de compra.
En el caso del área financiera o contable son innumerables los casos en que podemos aplicar condiciones a
alguna acción que se llegue a presentar por ejemplo, en manejo de inventarios, nómina, cartera o costos.
Reflexión : En qué situaciones de la vida rutinaria de un cliente o empresa que tiene un producto cualquiera
en una entidad financiera, por ejemplo, una cuenta de ahorros, un crédito, un seguro, etc., se pueden llegar
a presentar diferentes alternativas de solución y en las que ese cliente tenga que llegar a escoger dos o mas
posibles además trate de esquematizarlo en un diagrama de procesos o de flujo sencillo y al final de esta
actividad socialice con sus compañeros en el espacio destinado por su instructor para ese propósito.
3.2 Actividades de contextualización e identificación de conocimientos necesarios para el
aprendizaje
Al tener abierto un libro o archivo en Excel, ¿cómo podemos ver las diferentes funciones que dispone este
programa para ser utilizadas por un usuario?.
Si existen varias categorías de funciones cual considera usted son útiles para la ocupación de su programa
de formación?
Que funciones conoce y cuales ha trabajado?
Plantee una situación de su vida cotidiana en la que se pueda aplicar el uso de alguna de las funciones de
Excel.
3.3. Actividades de apropiación del conocimiento (Conceptualización y teorización)
Funciones Lógicas
Algunas de las funciones que se van a tratar en esta guía están dentro de la categoria de las Lógicas de Ms
Excel. Todas ellas retornan valores de falso o verdadero, también permiten realizar comparaciones y
dependiendo del resultado de esa comparación se pueden definir las acciones a realizar que pueden ser:
mostrar un valor, aplicar una fórmula o porque no realizar otra comparación que también es llamado como
funciones anidadas.
Veamos con el siguiente ejemplo de un reporte o boletín de notas de una institución educativa elaborada en
Excel como queda la fórmula:
En este ejemplo queremos que en la columna F que
corresponde a las OBSERVACIONES, se obtenga la
palabra APROBÓ en caso de que la nota definitiva,
que tiene la columna E, sea mayor o igual a 3.0 en
caso contrario, es decir cuando la nota definitiva sea
menor que 3.0 entonces debe mostrar la palabra
PERDIÓ.
La fórmula que debemos ingresar en la celda F6 será
la siguiente:
=SI(E6>=3,0;”APROBÓ”;”PERDIÓ”)
Y luego de copiar la fórmula al resto de estudiantes del
boletín, obtenemos el resultado como se vé en la
imagen, los estudiantes que tienen la observación de
“PERDIÓ” son los que tienen notas definitivas menores
que 3 unicamente.
Nota: En la fórmula el valor que retorna por verdadero
o falso, si es de tipo texto, se debe encerrar entre
comillas dobles. En caso contrario por ejemplo si es un
dato numérico o una fórmula no se usan las comillas.
En este otro ejemplo se quiere obtener el valor del IVA pero este depende del tipo de producto. Hay dos
tipos de productos A y B. La información esta en la columna C de la hoja.
Para el producto A se le aplica un IVA del 19% y para los productos tipo B un IVA del 5% al subtotal que lo
tenemos guardado en la columna E.
La fórmula que debemos ingresar a la celda
F14 que es el valor del IVA del primer
producto seria:
=SI(C14=”TIPO A”;E1419%;E145%)
Para luego obtener los siguientes resultados después de copiarlos al resto de productos:
falso prueba lógica verdadero
prueba lógica
verdadero
falso
Una vez ingresada la fórmula, los productos TIPO A serán grabados con un IVA del 19% y los del TIPO B
con un IVA del 5% respectivamente.
La siguiente función lógica que vamos a tratar es la función Y en Excel que en la versión inglesa seria AND
En esta función se pueden hacer una o mas comparaciónes (pruebas lógicas) y solo retornará el valor de
VERDADERO si todas las pruebas lógicas son verdaderas. Solo con tener una prueba lógica FALSA el
resultado será FALSO
Este es el aspecto de la ventana usando el asistente de funciones de Excel para esta función:
Podemos agregar hasta 255 comparaciones
en la función.
En el siguiente ejemplo vemos que existen
tres comparaciones dentro de la función
ingresada en la celda C1. Excel evalua si cada una de ellas es Verdadera o Falsa, para que el resultado sea
verdadero todas deben ser verdaderas.
=Y(12>5;7=7;9<=14)
Otro ejemplo de la función lógica Y :
=Y(12>5;7=7;9<>9)
Como podemos observar el valor retornado por la función Y es FALSO porque hay una comparación de las
tres que es falsa.
verdadero
verdadero verdadero verdadero
verdaderoverdadero
falso
=SI(Y(D14=”Soltero”;E14=”Femenino”;F14>0);350000;0)
Para que esta condición sea verdadera las tres comparaciones deben ser verdaderas para que se asigne el
subsidio de 350000 pesos en la columna G
Con frecuencia también debemos incluir una función SI dentro de otra función SI. Es lo que se denomina
funciones SI anidadas. Cuando el resultado final depende únicamente de dos acciones usamos una sola
función SI, pero ahora imaginemos que pueden presentarse mas de dos alternativas de solución como en el
siguiente planteamiento de ejemplo:
Una persona dependiendo de la edad es considerada con las categorías siguientes:
Menor a 18 años: “Menor de edad”
Mayor o igual a 18 años y menor de 65 años: “Mayor de edad”
Mayor o igual a 65 años: “Tercera edad”
Ahora tenemos tres posibles acciones, y la función SI no podrá resolver por sí sola este problema, por lo
que necesitamos recurrir a la función SI anidada la cual nos permitirá resolver cualquier situación en las que
necesitemos evaluar más de una prueba lógica y ejecutar más de dos acciones.
=SI(A2 < 18, "Menor de edad", SI(A2 < 65, "Mayor de edad", "Tercera edad"))
Funciones de Búsqueda y Referencia
prueba lógica
verdadero
PL2 verdadero falso
PL1 verdadero
falso
Excel nos proporciona 26 funciones de búsqueda y referencia para la versión 365 y nos va a permitir no solo
retorna un valor buscado, sino que encuentra referencias y así determinar la posición que ocupa esos datos
en las celdas.
Esta es la imagen de la ventana donde podemos encontrar el listado de las funciones de la categoría de
búsqueda y referencia al momento de usar el asistente de funciones.
Cuando se requiera buscar en una sola fila o columna y encontrar un valor desde la misma posición en una
segunda fila o columna vamos a usar la función BUSCAR.
Se pueden hacer dos tipos de búsqueda en esta función: forma vectorial o forma matricial. La
recomendación es usar la forma vectorial. Los tipos de argumentos los escogemos al momento de
seleccionar esta función.
Usemos la primera opción que es la vectorial.
Tenemos la siguiente hoja de cálculo con las notas, promedio, faltas y nombres de un curso de estudiantes.
Se quiere obtener la información en la celda K4 de los nombres de un determinado alumno y el promedio de
este solo con ingresar el código en la celda K3 como se muestra en la imagen.
- El número de columna del rango que contiene el valor devuelto. Por ejemplo, si especifica B2:D
como el rango, B se debe contar como la primera columna, C como la segunda y así
sucesivamente.
- Opcionalmente, puede especificar VERDADERO si desea una coincidencia aproximada o FALSO si
desea una coincidencia exacta del valor devuelto. Si no especifica nada, el valor predeterminado
siempre será VERDADERO o la coincidencia aproximada.
Ahora coloque todas las respuestas anteriores de la siguiente forma:
= BUSCARV (valor de búsqueda; rango que contiene el valor de búsqueda; el número de columna del rango
que contiene el valor devuelto; Coincidencia aproximada (TRUE) o Coincidencia exacta (FALSE)).
Para nuestro ejercicio usamos la función BUSCARV de la siguiente forma:
=BUSCARV(K3;B3:H13; 7 ;FALSO)
Obtenemos:
Veamos cómo usar la función BUSCARH
FUNCIÓN BUSCARH
Hay cuatro partes de la información que necesita para crear la sintaxis de BUSCARH:
- El valor que desea buscar, también conocido como el valor de búsqueda.
- El rango donde se encuentra el valor de búsqueda. Recuerde que el valor de búsqueda debe estar
siempre en la primera fila del rango para que BUSCARH funcione correctamente.
- El número de fila del rango que contiene el valor devuelto.
- Opcionalmente, puede especificar VERDADERO si desea una coincidencia aproximada o FALSO si
desea una coincidencia exacta del valor devuelto. Si no especifica nada, el valor predeterminado
siempre será VERDADERO o la coincidencia aproximada.
Ahora coloque todas las respuestas anteriores de la siguiente forma:
= BUSCARH (valor de búsqueda; rango que contiene el valor de búsqueda; el número de fila del rango que
contiene el valor devuelto; Coincidencia aproximada (TRUE) o Coincidencia exacta (FALSE)).
Para nuestro ejercicio usamos la función BUSCARH de la siguiente forma:
=BUSCARH(C11;B2:M8 7 ;FALSO)
Aplicando la función se obtiene
Funciones Financieras en Excel
Se resolvió de las dos maneras, según los depósitos se abonen al inicio del periodo ( tipo 1), o al final del
periodo (tipo 0)
Celda B6 = VF((B1/12);B2;B3;B4;B5)
Función TASA
Calcula la tasa de interés por periodo de una anualidad
Sintaxis TASA(nper;pago;va;vf;tipo;estimar)
Nper es él numero total de periodos de pago en una anualidad
Pago es el pago que se efectúa en cada periodo y que no puede cambiar durante la vida de anualidad.
Generalmente el argumento pago incluye el capital y el interés, pero no incluye ningún otro arancel o
impuesto.
Va es el valor actual de la cantidad total de una serie de pagos futuros
Vf es el valor futuro o saldo en efectivo que desea lograr después de efectuar él ultimo pago. Si el
argumento vf se omite, se asume que el valor es cero( por ejemplo el valor futuro de un préstamo es cero)
Tipo : es el valor debe ser 0 o 1 e indica el vencimiento de los pagos
Tipo :0 al final del periodo
Tipo :1 al inicio del periodo
Estimar es la estimación de la tasa de interés, si el argumento estimar se omite se supone que es 10%
Ejemplo
Función PAGO
Calcula el pago de un préstamo basándose en pagos constantes y una tasa de interés constante.
Sintaxis PAGO(tasa;nper;va;vf;tipo)
Tasa : es la tasa de interés del préstamo
Nper :es le numero total de pagos del préstamo
Va: es el valor actual
Vf : es el valor futuro. Si el argumento vf se omite, se asume que es 0 (o el valor futuro de un préstamo es
cero)
Tipo : es un numero 0 o 1 e indica el vencimiento de pagos
Tipo :0 al final del periodo
Tipo :1 al inicio del periodo
Observaciones : El pago devuelto incluye el capital y el interés
Celda B6= PAGO(B2/12;B3;B1;B4;B5 ) En el caso de producirse el pago al inicio del periodo
Función NPER
Calcula el numero de pagos de un préstamo, basado en pagos constantes , periódicos y a una tasa de
interés constante
Sintaxis NPER (tasa;pago;va;vf;tipo)
Tasa es la tasa de interés por periodo
Pago es el pago efectuado en cada periodo, debe permanecer constante durante la vida de la anualidad
Va es el valor actual o la suma total de una serie de futuros pagos
Celda B7 = PAGOINT=(B2/12;B3;B4;B1;B5;B6) E n este caso se puede apreciar en la celda B7 que se
calcula la parte correspondiente a intereses que incluye la cuota calculada anteriormente de $ 113.750 en el
primer pago es decir en el mes 1
Función PAGOPRIN
Calcula el pago sobre el capital de una inversión durante un periodo determinado, basándose en una tasa
de interés constante y pagos periódicos constantes
Sintaxis PAGOPRIN(tasa;periodo;nper;va;vf;tipo)
Tasa: es la tasa de interés del periodo
Periodo: es el periodo para el que se desea calcular la amortización y deben estar entre 1 y el argumento
nper
Nper : es numero total de pagos del préstamo Va: es el valor actual de una serie de pagos futuros
Vf : es el valor futuro de una serie de pagos futuros. Si se omite se calcula como cero
Tipo : es un numero 0 o 1 e indica el vencimiento de pagos
Tipo :0 al final del periodo
Tipo :1 al inicio del periodo
Celda B7= PAGOPRIN=(B2/12;B3;B4;B1;B5;B6) En este caso se puede apreciar en la celda B7 que se
calcula la parte correspondiente a amortización que incluye la cuota calculada anteriormente de $ 928, 566
en el primer pago es decir en el mes 1
En este caso se calcula en la celda B7 la amortización que integra la cuota de pago en él ultimo mes es
decir el mes 15 Como puede apreciarse produciendo la suma de amortización e interés en ambos casos se
obtiene el valor de la cuota a pagar.
El sistema desarrollado para calcular el préstamo donde el valor de la cuota es constante.
3.4. Actividades de transferencia del conocimiento
Actividad 1.
Apreciado aprendiz descargue el libro de Excel con el nombre FUNCIONESLOGICAS Y BUSQUEDA .XLS
a su computador.
Actividad 2
Apreciado aprendiz descargue el libro de Excel con el nombre EJERCICIOS FUNCIONES FINANCIERAS a
su computador.
Actividad 3
El instructor socilizará la actividad con el grupo donde cada aprendiz explicará uno de los ejercicios
escogidos al azar y se compararan los resultados obtenidos.
4. ACTIVIDADES DE EVALUACIÓN
Tome como referencia la técnica e instrumentos de evaluación citados en la guía de Desarrollo Curricular
Evidencias de Aprendizaje Criterios de Evaluación Técnicas e Instrumentos de
Evaluación
Evidencias de Conocimiento : El aprendiz identifica la
manera de utilizar y aplicar
las funciones presentadas en
esta guía.
Observación
Evidencias de Desempeño: El aprendiz atiende todas las
indicaciones dadas en la guía
Observación
Evidencias de Producto: Las evidencias entregadas
son implementadas de
acuerdo a las indicaciones
dadas.
Listas de Chequeo
5.GLOSARIO DE TÉRMINOS
EL DOCUMENTO
Nombre Cargo Dependencia Fecha
Autor (es) Luis Fernando García B. Instructor CSF - Banca 05/02/
8. CONTROL DE CAMBIOS (diligenciar únicamente si realiza ajustes a la guía)
Nombre Cargo Dependencia Fecha Razón del
Cambio
Autor (es)
