Utilización de Herramientas para la Productividad en Excel: Funciones Lógicas y Consulta, Ejercicios de Finanzas Públicas

¡Descarga Utilización de Herramientas para la Productividad en Excel: Funciones Lógicas y Consulta y más Ejercicios en PDF de Finanzas Públicas solo en Docsity!

[ HERRAMIENTAS PARA LA PRODUCTIVIDAD ] 1

Herramientas para la productividad

EXCEL 2007

SEMANA 7

[ POLITÉCNICO GANCOLOMBIANO EN ALIANZA CON WHITNEY INTERNATIONAL SYSTEM ]

2 [ POLITÉCNICO GANCOLOMBIANO EN ALIANZA CON WHITNEY INTERNATIONAL SYSTEM ]

Funciones Lógicas

Las funciones lógicas permiten obtener resultados de forma segura, estas funciones son ideales para crear tablas con datos seguros.

Función sí

La función si evalúa una condición y devuelve un valor verdadero si la condición se cumple o un valor falso si no se cumple.

Ejemplo: Generar un bono de 50.000 para todas las cantidades mayores a 300, para el resto de valores 0.

En este ejemplo la condición es que la cantidad sea mayor a 300, el valor verdadero es 50000 que corresponde al valor que se utiliza para las cantidades mayores a 300, el valor falso es 0 que será el valor utilizado cuando no se cumple la condición.

Estructura de la función si.

[ POLITÉCNICO GANCOLOMBIANO EN ALIANZA CON WHITNEY INTERNATIONAL SYSTEM ]

4 [ POLITÉCNICO GANCOLOMBIANO EN ALIANZA CON WHITNEY INTERNATIONAL SYSTEM ]

1. Hacer clic en la ficha formulas, a continuación grupo Biblioteca de funciones, elegir la librería Lógicas y pulsar en SI
2. Completar la prueba lógica, valor verdadero y valor falso según corresponda. El asistente no requiere que el usuario ponga paréntesis, comillas o separador de lista.

Ejemplos:

1. Si la cantidad es mayor a 50 entonces, al subtotal aplicar el porcentaje de descuento sino, cero

[ HERRAMIENTAS PARA LA PRODUCTIVIDAD ] 5

2. Si la fecha de pago es mayor al 10 de junio de 2010 entonces el 1,5% del valor sino cero. (cuando la fecha se debe poner en la función debe introducirse con la función fecha)
3. La categoría para los tipos A, es Principal, para el resto es General

Función SI anidado

Si anidado permite evaluar más de una condición dentro de la misma función, se usa para casos en los que el resultado final dependa de varias condiciones.

[ HERRAMIENTAS PARA LA PRODUCTIVIDAD ] 7

11. Cerrar un paréntesis por cada SI que se utilizó.

Si anidado desde el asistente de funciones

1. Hacer clic en la ficha formulas, a continuación grupo Biblioteca de funciones, elegir la librería Lógicas y pulsar en SI
2. Crear la condición uno ó prueba lógica
3. Crear el valor que le corresponde si se cumple la primera condición
4. Hacer clic en la opción Valor si Falso del asistente.
5. Seleccionar del cuadro de nombres (barra de formulas) la función SI para indicar al asistente que se realizará un nuevo SI dentro del actual.
6. El asistente queda vacio pero en la barra de formulas se construye el SI anidado.

[ POLITÉCNICO GANCOLOMBIANO EN ALIANZA CON WHITNEY INTERNATIONAL SYSTEM ]

8 [ POLITÉCNICO GANCOLOMBIANO EN ALIANZA CON WHITNEY INTERNATIONAL SYSTEM ]

7. Repetir el ciclo hasta máximo 8 condiciones.
8. Al final de la última condición se pone el valor falso que Excel usará cuando no se cumpla ninguna de las condiciones.

Función SI con Y - O

La función SI con Y-O permite evaluar dos o más condiciones al mismo tiempo. Se usa para casos en los que el resultado final depende de 2 o más criterios.

Ejemplo:

Crear un descuento del 3% del Valor, si la sucursal es Norte y la Cantidad es mayor a 10, para el resto cero.

Para este caso es necesario cumplir 2 condiciones, que la sucursal sea Norte y que la cantidad sea mayor a 10, si una de estas condiciones se deja de cumplir toda la respuesta es falsa.

[ POLITÉCNICO GANCOLOMBIANO EN ALIANZA CON WHITNEY INTERNATIONAL SYSTEM ]

10 [ POLITÉCNICO GANCOLOMBIANO EN ALIANZA CON WHITNEY INTERNATIONAL SYSTEM ]

Función Consultar

La función de consultar permite buscar un dato en común dentro de un rango de datos para devuelve una información específica

La función consultar es efectiva a la hora de comparar rangos de datos o ubicación de valores dentro de grandes matrices.

Existen dos opciones para consultar según la ubicación de los datos en el rango de búsqueda

La consulta puede ser V (vertical) o puede ser H (Horizontal) dependiendo del rango de datos en el que Excel busca la información

CONSULTAV: Para rangos en los que los datos están en columna

[ HERRAMIENTAS PARA LA PRODUCTIVIDAD ] 11

CONSULTAH: Para rangos en los que los datos están en fila

Estructura de la función consultar

1 - Valor Buscado : Corresponde al dato en común, un dato para que

Excel lo pueda ubicar dentro de un rango.

2 - Matriz buscar en : Es el rango ó nombre del rango en el cual Excel

buscará el dato en común ó valor buscado, es importante saber que el valor buscado siempre se realiza por la primera columna del rango seleccionado.

[ HERRAMIENTAS PARA LA PRODUCTIVIDAD ] 13

como es el saldo lo que se busca se digitaría en la función el número 3 que corresponde al número de columna con la información que se necesita.

Si la función es ConsultarH: Indicador_Fila , es porque los datos están en fila dentro del rango seleccionado, se debe contar el número de filas seleccionadas y digitar el número de la columna de la cual se necesita obtener la información.

En ejemplo se pretende buscar el Valor del gasto de acuerdo al mes, el rango de datos en el que se está buscando tiene siete filas, como es educación lo que se busca se digitaría en la función el número 4 que corresponde al número de fila con la información que se necesita.

4 - Ordenado : Esta opción permite obtener información exacta o

aproxima dependiendo la información de la columna 1.

En el siguiente ejemplo se muestra una consulta de sucursal por Id Oficina, esta consulta debe tener un grado de coincidencia exacta puesto que cada sucursal depende de un Id exacto como 10, 20 etc, por otro lado cada cliente se le asigna una categoría de acuerdo al saldo, en la tabla de categorías existen valores que van desde 0 a 2000000 de 2000000 hasta 5000000 de 5000000 hasta 10000000 y mayores de 10000000, esta consulta debe tener un grado de coincidencia aproximada para que según el valor del saldo le asigne la categoría correspondiente.

[ POLITÉCNICO GANCOLOMBIANO EN ALIANZA CON WHITNEY INTERNATIONAL SYSTEM ]

14 [ POLITÉCNICO GANCOLOMBIANO EN ALIANZA CON WHITNEY INTERNATIONAL SYSTEM ]

Cuando en la columna de Id Oficina existe un valor inexacto la función marca #N/A por que debe ser exacto. Cuando en la columna saldo existe un valor que está entre 0 y 2000000 le asigna Básico que es el más próximo, si el valor está entre 2000000 y 5000000 le asigna Junior y así sucesivamente según se ajuste el valor.

Proteger Información

Proteger la información puede resultar útil para mantener la integridad de los datos o restringir el acceso a datos archivos importantes.

Es posible proteger el archivo, la estructura del libro, hojas o celdas.

Proteger Archivo

Si se restringe mediante contraseña, se dará entrada al archivo sólo si tiene una clave de acceso, así sólo el usuario con la contraseña podrá ver o modificar el libro protegiéndolo de accesos no autorizados.

[ POLITÉCNICO GANCOLOMBIANO EN ALIANZA CON WHITNEY INTERNATIONAL SYSTEM ]

16 [ POLITÉCNICO GANCOLOMBIANO EN ALIANZA CON WHITNEY INTERNATIONAL SYSTEM ]

Proteger Libro

Proteger el libro significa que el usuario no puede realizar ninguna operación con hijas como insertar, Mover, ocultar, mostrar etc, es ideal para mantener una estructura del archivo y evitar perdida de datos.

También es posible proteger la opción de manejo de ventanas como organizar, minimizar, etc.

Asignar una contraseña al libro:

1. Hacer clic en la ficha Revisar, grupo cambios y a continuación en proteger libro.
2. Activar la opción Proteger estructura y ventanas
3. Escribir la contraseña si se desea que esta protección, no la quite cualquier usuario.
4. Dar clic en el botón Aceptar

Proteger Libros compartidos

Antes de protegerlo se debe cancelar el uso de libro compartido (desactive la casilla de verificación Permitir modificación por varios usuarios a la vez desde el la opción Compartir libro que se encuentra en la ficha del grupo Cambios).

[ HERRAMIENTAS PARA LA PRODUCTIVIDAD ] 17

1. Hacer clic en la ficha Revisar, grupo cambios y a continuación en Proteger y compartir libro.
2. Activar la casilla de verificación Compartir con control de cambios.
3. Digitar la contraseña si se desea que otros usuarios no desactiven el uso de libro compartido, dar clic en el botón Aceptar.
4. Volver a escribir la contraseña y dar clic en Aceptar.

Proteger Hoja

Una hoja de Excel se protege para que el usuario no ingrese o modifique información en las celdas, no obstante es posible asignar permisos al usuario así como permitir que ingrese o modifique datos en algunas celdas.

1. Hacer clic en la ficha Revisar, grupo cambios y a continuación en Proteger hoja.