CONDICIONAL COMPUESTO Evalúa dos condiciones al mismo tiempo y siempre va acompañado de las letras Y o O Y: Las dos condiciones se tienen que cumplir para ser verdadero O: Alguna de las dos condiciones se debe de cumplir para ser verdadero =SI(Y(condicion1;condición2);V;F) V: Si las dos condiciones se cumplen se asigna un valor F: Si ninguna de las dos condicones se simple se asigna otro valor =SI(O(condicion1;condición2);V;F) V: Si alguna de las dos condiciones se cumplen se asigna un valor F: Si ninguna de las dos condicones se simple se asigna otro valor NOTA: Entre las dos condiciones se abre y se cierra paréntesis Ejemplo: Por medio de la función lógica Compuesta, determinar si cada uno de los aspirantes a la carrera militar, son aceptados o rechazados, dependiendo de las condiciones que deben cumplir Condición 1: debe ser mayor de 18 años Condición 2: Debe medir como mínimo 1.70 M Condición 3: No puede pesar más de 80 Kilos
CONSOLIDACIÓN DE DATOS Permite realizar el total, mínimo, máximo, promedio de varias hojas que tengan la misma estructura. Ej.: tener en cada hoja las ventas de cada mes y luego realizar un consolidado del año. PARA REALIZAR LA CONSOLIDACION 1. Tener el archivo abierto de las hojas que se desean consolidar 2. Ubicarse en una hoja en blanco de ese archivo 3. Ubicarse en una celda en blanco donde se desee la consolidación 4. Ir al menú DATOS por la opción CONSOLIDAR 5. En FUNCION seleccionar la función a utilizar en la consolidación 6. En REFERENCIA dar clic en la flecha roja (para seleccionar la hoja y el rango donde se encuentran los datos a consolidar)
7. Seleccionar la hoja donde se encuentran los datos y el rango completo a consolidar incluyendo los títulos 8. Dar clic en la flecha roja 9. Realizar los pasos 9, 10 y 11 hasta terminar de agregar todas la hojas a consolidar 10.Activar los cuadros de fila superior, columna izquierda y crear vínculos con los datos de origen 11.ACEPTAR NOTA: Si se equivocaron en alguna referencia, en el cuadro de TODAS LAS REFERENCIAS, seleccionar la que se encuentra mala y luego ELIMINAR
FILTROS AVANZADOS Permiten crear un informe de una base de datos, en la misma hoja o en una hoja independiente con la información solicitada en determinado momento cumpliendo ciertos criterios Pasos
1. Copiar en una hoja nueva los campos de título que se desean filtrar y los criterios que deben cumplir en el informe 2. Ubicar el cursor donde se desea obtener la información, tener en cuenta que debemos dejar una fila entre los campos de título y el informe que se desea crear 3. Clic en el menú datos 4. Clic en Filtro 5. Clic en filtro avanzado 6. Seleccionar copiar a otro lugar 7. Clic en el campo rango de la lista 8. Clic en la etiqueta de la hoja donde se encuentra la base de datos 9. Seleccionar las columnas de los campos de título que se desea generar el informe 10. Clic dentro del campo rango de criterios 11. Seleccionar los campos de título y los criterios que deben cumplir para generar el informe 12. Clic dentro del campo copiar a: 13. Clic en la celda donde se desea generar el informe 14. Clic en el botón aceptar
FORMATO CONDICIONAL Permiten dar formato de letra y color a los valores de una columna seleccionada
PARA REALIZAR FORMATO CONDICIONAL 1. 2. 3. 4. 5. 6. 7. 8.
Seleccionar la columna a la cual le va a aplicar el formato Clic en el menú FORMATO Clic en FORMATO CONDICIONAL Seleccionar el signo a utilizar y digitar el valor deseado Clic en el botón de FORMATO Seleccionar el estilo de letra y color deseado Clic en el botón Aceptar Clic en el botón AGREGAR, hasta terminar de aplicar los formatos (solo permite 3 formatos) 9. Clic en el Botón ACEPTAR
PARA QUITAR FORMATO CONDICIONAL 1. 2. 3. 4. 5. 6.
Seleccionar la columna a la cual se le va a quitar el formato Clic en el menú FORMATO Clic en FORMATO CONDICIONAL Clic en el botón ELIMINAR Activar las condiciones a eliminar Clic en el botón ACEPTAR
PARA REALIZAR VALIDACION 1. 2. 3. 4. 5. 6.
Seleccionar la columna en blanco a la cual se va a realizar la validación Clic en el menú DATOS Clic en VALIDACION Clic en la ficha CONFIGURACION En la lista desplegable PERMITIR, seleccionar el criterio de validación En la ficha MENSAJE ENTRANTE, digitar el título y el mensaje entrante que se desee cuando se comience a digitar el dato
7. En la ficha MENSAJE DE ERROR, seleccionar el estilo, digitar el título y el mensaje de error ESTILO LIMITE: no deja digitar otro dato hasta que se digite el dato correctamente ESTILO ADVERTENCIA: muestra el mensaje con el error pero le pregunta si desea continuar o no ESTILO INFORMACIÓN: Informa sobre el error pero deja continuar 8. ACEPTAR
PARA QUITAR VALIDACION 1. 2. 3. 4. 5.
Seleccionar la columna a la cual se desea quitar la validación Clic en el menú DATOS Clic en VALIDACION Clic en el botón BORRAR TODOS Clic en el botón ACEPTAR
EJERCICIO DE APLICACIÓN Una empresa X, realiza préstamos a sus empleados a 0% de Interés, pero los pagos, se deben realizar entre 1 y 20 cuotas iguales
Aplicar FORMATO CONDICIONAL a la columna Saldo, teniendo en cuenta: 1. Saldos menores a cero, deben mostrarse en color Rojo y en negrita 2. Saldos iguales a cero, deben mostrase en color Verde y en negrita 3. Saldos mayores a cero, deben mostrarse en color azul y en negrita
Aplicar VALIDACION DE DATOS al rango (E5:N11), teniendo en cuenta 1. Solo debe permitir el ingreso de datos numéricos mayores a 0 2. El mensaje de entrada debe tener como título “Valor de la cuota”, y el mensaje entrante debe decir “Ingrese el Valor de la Cuota a Pagar” 3. El mensaje de salida debe tener como título “Valor de la Cuota” y el mensaje de error debe decir “Sólo permite el ingreso de datos numéricos mayores a 0”
FORMULARIOS Permite extraer de una base de datos la información consultada desde una plantilla o un formulario Pasos Lo primero es crear la base de datos y marcar las columnas donde se desea realizar las consultas Pasos para marcar las columnas: 1. 2. 3. 4. 5. 6.
Seleccionar la columna Clic en el menú insertar Clic en Nombre Clic en Crear Activar la casilla fila superior Clic en Aceptar
El siguiente paso es crear la plantilla o formulario, desde donde se realizará la consulta, una vez creada la plantilla, utilizamos la función INDICE, para realizar las consultas La función a utilizar es la siguiente: =Indice(Nombre campo de Título; Posición Celda Registros) Donde Posición es una celda que se asigna al formulario para crear la secuencia de los registros y donde se vinculan los índices de todos los campos de título. El paso a seguir es igual para cada uno de los campos de título que se desean consultar Pasos para crear barras de desplazamiento y botones Primero debemos activar la barra de formularios
Para activar barra de herramientas 1. Clic en el menú ver 2. Clic en barras de herramientas 3. Activar la barra de formularios
PASOS PARA CREAR UN BOTÓN DE CONTROL DE NÚMERO 1. Clic en la herramienta control de número 2. Abrir el botón sobre la celda que se desea agregar 3. Doble clic sobre el botón para configurar el botón
4. 5. 6. 7.
Especificar el valor mínimo o sea donde comienzan los registros Especificar el valor máximo hasta donde llegan los registros Especificar los incrementos entre registros Clic en el botón Aceptar
Ejercicio de Aplicación Crear una base de datos con los campos:
Identificación Apellidos Nombres Dirección Teléfono Valor préstamo Número de cuotas Valor cuota Saldo
Ingresar 20 registros y crear la plantilla o formulario que permita realizar la consulta de cada uno de los registros y nos muestre el estado de la cuenta Crear las formulas financieras que permitan calcular el pago de la cuota, teniendo en cuenta que la tasa de interés anual es del 19.65%
FUNCIONES FINANCIERAS
PAGO 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) ó PAGO(tasa;nper;va;vf;tipo) Tasa es el interés anual del préstamo dividido el numero de periodos pagados durante el año. Nper es el número total de pagos del préstamo. Va es el valor actual o lo que vale ahora la cantidad total de una serie de pagos futuros, también se conoce como el principal. Tipo es el número 0 (cero) ó 1 e indica el vencimiento de los pagos.
PAGOINT Devuelve el interés pagado en un período específico por una inversión basándose en pagos periódicos constantes y en una tasa de interés constante. Sintaxis PAGOINT(tasa;período;nper;va) ó PAGOINT(tasa;período;nper;va;vf;tipo)
NPER Devuelve el número de períodos de una inversión basándose en los pagos periódicos constantes y en la tasa de interés constante. Sintaxis NPER(tasa;pago;va) ó NPER(tasa;pago;va;vf;tipo)
VF Devuelve el valor futuro de una inversión basándose en pagos periódicos constantes y en una tasa de interés constante. Sintaxis
VF(tasa;nper;pago)
TALLER FUNCIONES FINANCIERAS 1.
Doña Carmen la del taller de confecciones, desea renovar sus viejas máquinas, por lo tanto solicitó un préstamo por valor de $ 25000000 Millones de pesos, la entidad financiera le ofreció una línea de crédito a microempresas a una tasa de interés anual del 12.8% para cancelar en cuotas mensuales durante 5 años. Calcular: La cuota que debe pagar cada mes si fuera Anticipada y Vencida La cuota que pagaría si el pago lo efectuará cada dos meses El interés pagado en cada uno de los meses durante la vigencia del crédito
2.
Juan Camilo, desea hacer un préstamo por valor de $ 3000000 de pesos, a una tasa de interés anual de 19.5% pero sólo puede pagar cuotas de $ 110727,931380568 pesos Calcular en cuántas cuotas pagaría la deuda
3.
Doña Rosalba desea hacer un ahorro programado ahorrando $10000 pesos Semanales durante 8 años y la entidad financiera le paga una tasa de interés anual de 13.8%. Calcular el monto ahorrado durante los 8 años
MACROS Una macro consiste en una en una serie de comandos y funciones que se almacenan en un módulo de Microsoft Visual Basic y que puede ejecutarse siempre que sea necesario realizar una tarea Una macro permite sistematizar tareas que se realizan frecuentemente en Microsoft Excel, con el fin de minimizar el número de operaciones a realizar de modo que se ejecuten por medio de un botón, una combinación de teclas o un comando Al grabar la Macro, Excel almacena información sobre cada paso dado cuando se ejecuta una serie de comandos. Luego se ejecuta la macro para que repita los comandos Pasos para grabar una macro
1. 2. 3. 4. 5. 6. 7. 8.
Clic en el menú HERRAMIENTAS Clic en MACRO Clic en GRABAR NUEVA MACRO Asignar UN NOMBRE a la macro Especificar el LUGAR DONDE SE DESEA GUARDAR LA MACRO Describir LAS FUNCIONES de la macro Clic en el botón ACEPTAR A partir de este momento se deben ingresar todos los datos, formatos y comandos que se espera que la macro ejecute 9. Clic en el menú HERRAMIENTAS 10. Clic en MACRO 11. Clic en DETENER GRABACIÓN
Pasos para crear el botón de la macro 1. 2. 3. 4. 5.
Clic en el menú HERRAMIENTAS Clic en PERSONALIZAR Clic en la ficha COMANDOS Seleccionar el menú MACROS del cuadro CATEGORÍAS Clic sostenido sobre la opción PERSONALIZAR BOTÓN del cuadro de COMANDOS y arrastrar hasta la barra o menú donde se desea agregar el botón 6. Clic en el BOTÓN o comando creado 7. Clic en el botón MODIFICAR SELECCIÓN del cuadro de diálogo PERSONALIZAR 8. Clic en NOMBRE para asignar un NOMBRE AL BOTÓN 9. Clic en CAMBIAR IMAGEN, para ASIGNAR UNA IMAGEN AL BOTON 10. Clic en el botón CERRAR del cuadro de diálogo personalizar Pasos para ejecutar la macro
1. Clic en el botón de la MACRO 2. Seleccionar EL NOMBRE de la macro 3. Clic nuevamente en el BOTÓN DE LA MACRO Crear cuadro de diálogo con mensaje sobre la macro 1. 2. 3. 4. 5. 6. 7. 8.
Clic en el menú HERRAMIENTAS Clic en MACRO Seleccionar LA MACRO que se desea agregar el cuadro de texto Clic en el botón MODIFICAR Presionar la tecla ENTER y devolverse una LINEA Escribir MSGBOX “escribir mensaje entre comillas” Clic en el menú ARCHIVO Clic en CERRAR Y VOLVER A MICROSOFT EXCEL
TABLAS DINÁMICAS Permite crear informes independientes de una base de datos, mostrando sólo la información que se necesite en determinado momento. Un informe de tabla dinámica es una tabla interactiva que combina y compara rápidamente grandes volúmenes de datos. Podrá girar las filas y las columnas para ver diferentes resúmenes de los datos de origen, y mostrar los detalles de determinadas áreas de interés.
Pasos 1. Ubicarse SOBRE la base de datos 2. Clic en el menú DATOS 3. Clic en INFORME DE TABLAS Y GRÁFICOS DINÁMICOS 4. Seleccionar EL LUGAR donde se encuentran los datos que se van a analizar 5. Seleccionar EL TIPO DE INFORME que se desea crear 6. Clic en el botón siguiente 7. Seleccionar EL LUGAR donde se desea situar el informe de tabla dinámica 8. Clic en el botón DISEÑO 9. Arrastrar LOS CAMPOS DE FILA, COLUMNA O DATOS según el tipo de informe que se desee crear 10. Doble clic SOBRE EL CAMPO DE DATOS para seleccionar los criterios del informe 11. Clic en el botón aceptar 12. Clic en las LISTAS DESPLEGABLES DE LOS CAMPOS DE FILA Y COLUMNA, para seleccionar los criterios que se desean mostrar
TALLER DE EXCEL AVANZADO
PROF. OMAR WILSON ÁLVAREZ RUIZ TABLAS DINÁMICAS Abrir el archivo de GRAVETAL y guardarlo con el nombre de TABLAS DINÁMICAS DE GRAVETAL 1. Crear una tabla dinámica donde se muestre el total de ventas de ACEITE GIRASOL en la ZONA 2. Con la tabla dinámica anterior modificar el total de ventas por el promedio de ventas. 3. Crear una nueva tabla dinámica con la base de datos original. Se debe mostrar el total de comisión y la venta máxima de cada vendedor por producto 4. Crear una nueva tabla dinámica con la base de datos original. Se debe mostrar el vendedor MARTINEZ en la ZONA H que productos vendió y cual fue el promedio de ventas y de comisión, 5. Modificar la tabla dinámica de VENTAS MARTINEZ ZONA H donde se muestre también total de ventas y comisión. 6. Realizar una nueva tabla en la hoja 5 donde muestre cuantos productos existen en cada zona de cada uno de los vendedores. 7. En una hoja nueva crear un informe donde se muestren las ventas realizadas por VASQUEZ, FERNANDEZ Y BONILLA de aceite SOY SABOR en cada una de las zonas 8. Mostrar en un informe de tabla dinámica el PROMEDIO DE VENTAS por producto en cada una de las ZONAS de cada uno de los VENDEDORES
SUBTOTALES Permite hallar totales parciales y generales de una base de datos Primero se deben ordenar los datos que se desean totalizar 1. Seleccionar la COLUMNA que se desea ordenar 2. Clic en el botón ORDEN ASCENDENTE 3. Clic en el botón ORDENAR HALLAR SUBTOTALES 1. Clic en el menú DATOS 2. Clic en SUBTOTALES 3. En el campo PARA CADA CAMBIO EN, seleccionar el campo de título que se desea ordenar 4. En el campo USAR FUNCIÓN, seleccionar la función o criterio que deben cumplir los datos que se desean ordenar 5. En el campo HALLAR SUBTOTALES A, seleccionar el campo que se desea totalizar 6. Clic en el botón ACEPTAR HALLAR SUBTOTALES ORDENANDO DATOS POR VARIOS CRITERIOS Clic en cualquier celda de de la base de datos Clic en el menú DATOS Clic en ORDENAR Ordenar los datos en cada una de las columnas que se desea totalizar Clic en el botón ACEPTAR HALLAR SUBTOTALES 7. Clic en el menú DATOS 8. Clic en SUBTOTALES 9. En el campo PARA CADA CAMBIO EN, seleccionar el campo de título que se desea ordenar 10. En el campo USAR FUNCIÓN, seleccionar la función o criterio que deben cumplir los datos que se desean ordenar 11. En el campo HALLAR SUBTOTALES A, seleccionar el campo que se desea totalizar 12. Clic en el botón ACEPTAR 13. Seguir los pasos 7 al 11 para organizar el segundo criterio 14. desactivar la casilla REEMPLAZAR SUBTOTALES ACTUALES 15. Clic en el botón ACEPTAR
TALLER DE EXCEL AVANZADO SUBTOTALES PROF. OMAR WILSON ALVAREZ RUIZ SUBTOTALES VENDEDORES CARROS Abrir el archivo de VENDEDORES CARROS, copiar la información en las siguientes 10 hojas para realizar cada punto en una hoja diferente guardar el archivo con el nombre de SUBTOTALES VENDEDORES CARROS y el nombre de cada uno 1. ¿Cual fue el total del IVA y del valor neto de cada uno de los modelos? 2. ¿Cual es el valor mínimo de comisión y descuento por cada uno de los vendedores en cada marca de vehículo y color? 3. ¿Cual es el promedio del precio de cada modelo y el valor máximo de comisión por cada color? 4. ¿Cuantos vehículos existen por cada vendedor? 5. ¿Cual es la retención mayor de cada marca de los vehículos y el promedio de descuento de cada modelo? 6. ¿Cual fue el IVA total de cada vendedor en cada uno de los modelos? 7. ¿Cual fue el total y promedio de precio, comisión, iva, retención, descuento y valor neto por modelo, color y marca? 8. ¿Cuantos modelos existen por cada color?
9. ¿Cual fue el valor neto máximo por cada marca y color? 10.¿Cual fue el promedio del IVA por vendedor, el total de comisión por color y el valor máximo de precio por modelo?