CONDICIONAL COMPUESTO

Evalúa dos condiciones al mismo tiempo y siempre va acompañado de las letras Y o OY: Las dos condiciones se tienen que cumplir para ser verdaderoO: 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 valorF: 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 valorF: 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ñosCondición 2: Debe medir como mínimo 1.70 MCondició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 CONSOLIDACION1. Tener el archivo abierto de las hojas que se desean consolidar2. Ubicarse en una hoja en

blanco de ese archivo3. 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 roja9. Realizar los pasos 9, 10 y 11 hasta terminar de agregar todas la hojas a

consolidar10.Activar los cuadros de fila superior, columna izquierda y crear vínculos

con los datos de origen11.ACEPTARNOTA: 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ú datos4. Clic en Filtro5. Clic en filtro avanzado6. Seleccionar copiar a otro lugar7. Clic en el campo rango de la lista8. Clic en la etiqueta de la hoja donde se encuentra la base de datos9. Seleccionar las columnas de los campos de título que se desea generar el informe10. Clic dentro del campo rango de criterios11. Seleccionar los campos de título y los criterios que deben cumplir para generar el

informe12. Clic dentro del campo copiar a:13. Clic en la celda donde se desea generar el informe14. 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. Seleccionar la columna a la cual le va a aplicar el formato2. Clic en el menú FORMATO 3. Clic en FORMATO CONDICIONAL4. Seleccionar el signo a utilizar y digitar el valor deseado5. Clic en el botón de FORMATO6. Seleccionar el estilo de letra y color deseado7. Clic en el botón Aceptar8. 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. Seleccionar la columna a la cual se le va a quitar el formato2. Clic en el menú FORMATO 3. Clic en FORMATO CONDICIONAL4. Clic en el botón ELIMINAR 5. Activar las condiciones a eliminar6. Clic en el botón ACEPTAR

PARA REALIZAR VALIDACION1. Seleccionar la columna en blanco a la cual se va a realizar la validación2. Clic en el menú DATOS3. Clic en VALIDACION4. Clic en la ficha CONFIGURACION5. En la lista desplegable PERMITIR, seleccionar el criterio de validación6. 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 errorESTILO LIMITE: no deja digitar otro dato hasta que se digite el dato correctamenteESTILO ADVERTENCIA: muestra el mensaje con el error pero le pregunta si desea continuar o noESTILO INFORMACIÓN: Informa sobre el error pero deja continuar

8. ACEPTAR

PARA QUITAR VALIDACION1. Seleccionar la columna a la cual se desea quitar la validación2. Clic en el menú DATOS 3. Clic en VALIDACION4. Clic en el botón BORRAR TODOS5. 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. Seleccionar la columna2. Clic en el menú insertar3. Clic en Nombre4. Clic en Crear5. Activar la casilla fila superior6. 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ú ver2. Clic en barras de herramientas3. Activar la barra de formularios

PASOS PARA CREAR UN BOTÓN DE CONTROL DE NÚMERO

1. Clic en la herramienta control de número2. Abrir el botón sobre la celda que se desea agregar3. Doble clic sobre el botón para configurar el botón

4. Especificar el valor mínimo o sea donde comienzan los registros5. Especificar el valor máximo hasta donde llegan los registros6. Especificar los incrementos entre registros7. 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. Clic en el menú HERRAMIENTAS2. Clic en MACRO3. Clic en GRABAR NUEVA MACRO4. Asignar UN NOMBRE a la macro5. Especificar el LUGAR DONDE SE DESEA GUARDAR LA MACRO6. Describir LAS FUNCIONES de la macro7. Clic en el botón ACEPTAR8. A partir de este momento se deben ingresar todos los datos, formatos y comandos

que se espera que la macro ejecute9. Clic en el menú HERRAMIENTAS10. Clic en MACRO11. Clic en DETENER GRABACIÓN

Pasos para crear el botón de la macro

1. Clic en el menú HERRAMIENTAS2. Clic en PERSONALIZAR3. Clic en la ficha COMANDOS4. Seleccionar el menú MACROS del cuadro CATEGORÍAS5. 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ón6. Clic en el BOTÓN o comando creado7. Clic en el botón MODIFICAR SELECCIÓN del cuadro de diálogo

PERSONALIZAR8. Clic en NOMBRE para asignar un NOMBRE AL BOTÓN9. Clic en CAMBIAR IMAGEN, para ASIGNAR UNA IMAGEN AL BOTON10. 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 MACRO2. Seleccionar EL NOMBRE de la macro3. Clic nuevamente en el BOTÓN DE LA MACRO

Crear cuadro de diálogo con mensaje sobre la macro

1. Clic en el menú HERRAMIENTAS2. Clic en MACRO3. Seleccionar LA MACRO que se desea agregar el cuadro de texto4. Clic en el botón MODIFICAR5. Presionar la tecla ENTER y devolverse una LINEA6. Escribir MSGBOX “escribir mensaje entre comillas”7. Clic en el menú ARCHIVO8. 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 datosPrimero se deben ordenar los datos que se desean totalizar

1. Seleccionar la COLUMNA que se desea ordenar2. Clic en el botón ORDEN ASCENDENTE 3. Clic en el botón ORDENAR

HALLAR SUBTOTALES

1. Clic en el menú DATOS2. Clic en SUBTOTALES3. En el campo PARA CADA CAMBIO EN, seleccionar el campo de título que

se desea ordenar4. En el campo USAR FUNCIÓN, seleccionar la función o criterio que deben

cumplir los datos que se desean ordenar5. En el campo HALLAR SUBTOTALES A, seleccionar el campo que se

desea totalizar6. Clic en el botón ACEPTAR

HALLAR SUBTOTALES ORDENANDO DATOS POR VARIOS CRITERIOS

Clic en cualquier celda de de la base de datosClic en el menú DATOSClic en ORDENAROrdenar los datos en cada una de las columnas que se desea totalizarClic en el botón ACEPTAR

HALLAR SUBTOTALES

7. Clic en el menú DATOS8. Clic en SUBTOTALES9. En el campo PARA CADA CAMBIO EN, seleccionar el campo de título que

se desea ordenar10.En el campo USAR FUNCIÓN, seleccionar la función o criterio que deben

cumplir los datos que se desean ordenar11.En el campo HALLAR SUBTOTALES A, seleccionar el campo que se

desea totalizar12.Clic en el botón ACEPTAR13.Seguir los pasos 7 al 11 para organizar el segundo criterio14.desactivar la casilla REEMPLAZAR SUBTOTALES ACTUALES15.Clic en el botón ACEPTAR

TALLER DE EXCEL AVANZADO

SUBTOTALES

PROF. OMAR WILSON ALVAREZ RUIZSUBTOTALES 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?