EXCEL-FINANCIERO.pdf

 CURSO Aplicado a Análisis Financiero

Profesor: Material exclusivamente para el aprendizaje del alumno queda totalmente prohibido la reproducción del manual. LIMA PERÚ -2017

Omar Inga Sudario  [email protected]

MANUAL DEL

ESTUDIANTE

EXCEL 2016

APLICATIVOS DIGITALES

EXCEL FINANCIERO

:. Profesor: Inga Sudario,Omar .:

Ms Office Excel 2016 Professional

Vive la programación en Ti...!

DEDICATORIA

Esta guía les dedico a mis padres quienes me han apoyado para poder llegar a esta instancia de mis estudios les debo todo lo que tengo en la vida ya que ellos comparten conmigo, Mi derrota y celebran mis triunfos. El lenguaje de amor de Dios hacia nosotros es por lo general sin palabras. Hay tanto por apreciar en la naturaleza, en las personas, en cada amanecer, que no son necesarias las palabras. Los mares, los ríos, las montañas nos gritan lo bueno que es Dios con nosotros. Con una promesa así, yo me siento como un niño. No me preocupo por ser perfecto o pretender ser alguien quien no soy. Sé que mi personalidad, mis locuras y hasta los errores hacen que Dios me ame más.

AVIVAMIENTO JCP

Profesor: Omar Inga Sudario [email protected]

1

EXCEL FINANCIERO

Repaso de Conceptos Basico




Prueba De Entrada.Características Del Programa, Descripción del Entorno Ingreso De Datos.  Tipos De Datos.  Edición De Datos.  Selección De Celdas y Rangos.  Manejo De Filas y Abrir y Cerrar Libros.Columnas

Edición de hojas, columnas y f i l a s ,


elaboración de formulas Manejo de Filas y Columnas.  Insertar.  Eliminar.  Ocultar.  Mostrar. Manejo De Hojas.

 Insertar.  Eliminar.  Ocultar. Mostrar

2


EXCEL FINANCIERO

Microsoft Excel 2016


Es un programa de hoja de cálculo que permite realizar cualquier tipo de operación matemática, proyectos, esquemas empresariales y una serie de cosas más. Excel no solo trabaja con herramientas de cálculos, también incluye una serie de gráficos mejorados para realizar estadísticas visuales. En esta versión, Excel 2016 presenta una manera más limpia y fácil de trabajo con datos si antes creías que Excel era rápido, ahora te sorprenderás con las diversas cosas que puede hacer con tan solo un clic o un toque, si un toque, pues Excel está preparado para trabajar de manera táctil en PC, Tablet o Smartphone.


Características generales Formulas en lenguaje natural Auto formato de tabla dinámica Protección de hoja calculo Auto relleno lista Funciones

Otra forma de iniciar Excel 2016 es mediante el menú Inicio.

1. Clic Botón Iniciar. 2. Todos Los Programas. 3. Microsoft Office 2016. 4. Clic Excel 2016.


3


EXCEL FINANCIERO


Otra forma: Presionamos la Tecla Windows + R, escribimos el ejecutable Excel y damos clic en el Botón Aceptar.

        


Interfaz de excel 2016

Partes de la interfaz de Excel

Minimizar Maximizar Cerrar Herramientas de acceso Cinta de opciones Cuadro de nombres Celda Grupos Zoom


       

Diseño de vidas Barra horizontal Barra vertical Encabezado de columnas Encabezado de filas Etiquetas Barra de titulo Pestanas o fichas

4


EXCEL FINANCIERO

Guardar un libro de cálculo Paso 1: para guardar el libro haz clic en la



pestaña Archivo. Paso 2: Elige la opción Guardar como

Aparecerá el siguiente cuadro de diálogo:

 En el recuadro Nombre de archivo, escribe el nombre que quieres ponerle a tu archivo. Y por último haz clic sobre el botón Guardar.

 PROTEGER UN DOCUMENTO

Menu archivo guardar como , clic en herramientas selecionamos opciones generales ,nos muestra un cuadro solicitando la contraseña,Ingrese las dos contraseñas que nos piden


1

EXCEL FINANCIERO




Referencias y Func ione s Básicas Referencia de Celda.  relativas.  Absolutas.  Mixtas. Referencias Entre Hojas y Libros. Funciones Básicas.  Suma  Promedio  Max  Min Funciones de Cuenta.  Contara  Contar  Contar. Blanco

Formato de libro


Nombrar.

 Celdas.  Rangos.  Función K.Esimo.Menor  Función K.Esimo.Mayor

1


EXCEL FINANCIERO

REFERENCIAS Estas son las tres maneras que tenemos de hacer referencia a una celda. Pero aparte de esto podemos decir que hay tres tipos:  Referencias Relativas (Son aquellas que varían si cambia su posición)  Referencias Absolutas (Son aquellas que se mantienen fijas


independientemente de su posición)

 Referencias Mixtas o híbridas (Son aquellas en las que se mantiene fija o

bien la columna o bien la fila)


1. Referencias Relativas:

2. Referencias Absolutas: Una referencia es absoluta cuando siempre se hace

referencia a una celda en Una ubicación específica.

 Cuando seleccionamos la celda B10 que es el IGV presionamos la tecla “F4”  Celda B10(de forma que lo visualice como $B$10) con lo cual indicamos LA REFERENCIA


2


EXCEL FINANCIERO

3. Referencias Mixtas: Una referencia es mixta cuando solo fijamos la letra o el



número, es decir, la fila o La columna. Una referencia mixta, tiene una columna absoluta y una fila relativa o una columna relativa y una fila absoluta. Veamos un ejemplo

Referencia a una celda en otra hoja de Excel

Hagamos un ejemplo muy sencillo. En una de las hojas de mi libro de Excel tengo el presupuesto para los gastos de los meses de enero y febrero.

En la hoja Resumen deseo mostrar el gasto de teléfono del mes de ALQUILER por lo que utilizo la siguiente fórmula:


3


EXCEL FINANCIERO

FUNCIONES Las funciones de Excel son una característica muy importante que debes aprender a utilizar adecuadamente para obtener el mayor provecho de la herramienta. OBJETIVOS

- Conocer lo básico de EXCEL - Aprender los operadores matemáticos. - Conocer la sintaxis y ejemplos y algunas funciones más específicas Funciones básicas


   

 Suma: devuelve el total todo el valor asignados =SUMA(RANGO)


SINTAXIS

Ejemplo

 



Promedio: Devuelve el Promedio de los Argumentos. SINTAXIS

=promedio(RANGO)

Ejemplo:


4


EXCEL FINANCIERO

REDONDEAR: Redondea un numero entero al número de decimales Especificado. SINTAXIS

=redondear(numero;num_decimales)

 MAX: Devuelve el valor máximo de una lista de valores. Sintaxis

=Max(rango)

SINTAXIS

 MIN: Devuelve el valor mínimo de una lista de valores. =Min(rango)

SINTAXIS

=Moda(rango)

SINTAXIS



 MODA: Devuelve el valor más frecuente o el que más se repite en un rango de datos.

CONTARA: Cuenta las celdas no vacías de un rango. SINTAXIS

Sinta =Contara(rango)


5


EXCEL FINANCIERO

CONTAR: Cuenta el número de celdas de un Rango que contiene números. Sintaxi

SINTAXIS

=Contar(rango)

 CONTAR.BLANCO: Cuenta el Numero de celdas en blanco dentro de un rango SINTAXIS

Sintaxis:=Contar.blanco(rango )

Se pide Contar cuantos alumnos no tienen promedio Contar la cantidad de Caracteres que contiene una Celda



Ejemplo:

Ejercicio


6

EXCEL FINANCIERO




Referencias y Func ione s Básicas Referencia de Celda.  relativas.  Absolutas.  Mixtas. Referencias Entre Hojas y Libros. Funciones Básicas.  Suma  Promedio  Max  Min Funciones de Cuenta.  Contara  Contar  Contar. Blanco

Formato de libro


Nombrar.  Celdas.  Rangos.  Función K.Esimo.Menor  Función K.Esimo.Mayor

7


EXCEL FINANCIERO

Ejercicio 01



ud debe contar con el siguiente archivo solicite asu estimado profesor Calcular ventas del vendedor

 Escribir las formulas correspondientes  Subtotal: ______________________  Igv 18%: __________________________  Descuento 2%: ____________________  Pago neto: _________________________

Ejercicio 2

Calcular la tasa de intereses de todos los bancos.

Escribir las formulas, tomar como referencia: _____________________________ : ___________________________


8


EXCEL FINANCIERO



Ejercicio 03

 Escribir las formulas correspondientes  Monto __________________________________  Retencion: ______________________________  Neto: ___________________________________  Total trabajador: ________________________  Total de horas: ______________________

Ejercicio 04 ubicarse en al hoja evaluaciones finales

Escribir la formula que empleo  Alumnos con promedio: ______________________  Alumnos sin promedio: _______________________  Total de alumnos: ____________________________


9


EXCEL FINANCIERO



Ejericio 05

Ingresar formulas:  Total de clientes ______________________  Total monto: _________________________  Promedio de montos: _________________  Clientes sin region: ___________________

Ejercicio 06

Ingresar formulas:  Mejor promedio ______________________  Peor promedio: _________________________  2 promedio mayor: ______________________  Numero de alumnos: ______________________


10


EXCEL FINANCIERO



Tarea 01. Desarrollar dicho ejercicio aplique lo aprendido aplicar referencia mixta

Ingresar formulas:  Total de descuentos ______________________  Neto a cobrar ______________________

Tarea 02

Ingresar formulas:    

Descuento afp ______________________ Descuento salud ______________________ Liquido a pagar ______________________ Cantidad de empleados: ______________


11


EXCEL FINANCIERO



Tarea 03

Ingresar formulas:  Bon1 = Será el 5%  Bon2 = Un tercio del básico  Total = Suma de Básico, Bon1 y Bon


12

EXCEL FINANCIERO




Funciones logicas y anidadas        

Funciones lógicas Funciones si simple Funciones si anidada Funciones y simple Funciones y anidada Funciones o simple Funciones o anidada Comentarios



13


EXCEL FINANCIERO

Funciones lógica Las funciones lógicas en Excel se utilizan en la toma de decisiones. En base al resultado de una función decidiremos si ejecutar o no cierta acción requerida. Función SI Devuelve el valor si la condición especificada es VERDADERA y otro valor si el argumento es FALSO



 Ejemplo 01: "Si los empleados tienen hijos se les otorgará una bonificación de S/. 200, y aquellos que no tienen hijos, S/. 100"

 Ejemplo 02 En la columna observación nos debe mostrar el siguiente mensaje Si la nota es mayor a 10 “APROBADO” caso contrario “DESAPROBADO”

 Ejemplo 03 Si la camiseta es de Perú tendrá un costo de S/. 150,00 Caso contrario las demás camisetas tendrán un costo de S/. 230,00


14


EXCEL FINANCIERO

Función anidad del “SI” (Cuando se trabaja con varias condiciones) Ejemplo 01: si la categoría es "A" será "Excelente", si la categoría es "B" será "Bueno", si es "C" será "Regular"





 Ejemplo 02 si el promedio es >18 es excelente; >15 bueno; >10 regular; malo

 Ejemplo 03 Si el producto es: LECHE - 2.50; ARROZ - 3.00; ACEITE - 1.50 HARINA -- 0.50


15


EXCEL FINANCIERO

Función Y Dos o más criterios que tenga se deben cumplir para aplicarse como verdadero o falso



 Ejemplo 01 "Si el postulante sabe INGLES y COMPUTACION, se le considerará como VERDADERI, FALSO

 Ejemplo 03 Calcular la bonificación por carga familiar de 100 soles para aquellos trabajadores casados y que además tengan hijos

Función O Es una de las funciones lógicas de Excel y como cualquier otra función lógica solamente devuelve los valores VERDADERO o FALSO máximo 255  Ejemplo 01: 5 solos, si se paga con VISA o DINERS, caso contrario O

 Ejemplo 02 Si el color es ("AZUL" O "MARRON" O "ROJO") tendrá un precio de S/. 250, Caso contrario tendrá un precio de S/. 130


16


EXCEL FINANCIERO

Función “si “; “y “; “o “



 Ejemplo 01 Si la categoría es ("A"O "B") Y demás el cargo debe ser Sistemas tendrá un pago de 1200, Caso contrario solo será de 900

Comentarios en Excel

Excel permite agregar comentarios a cualquier celda de una hoja. Estos comentarios son útiles para hacer una revisión, dar alguna instrucción o simplemente una explicación detallada del contenido de una celda. Agregar un comentario a una celda

Modificar o eliminar un comentario Al seleccionar una celda que contiene un comentario se habilitarán los botones Modificar comentario y Eliminar.

Al hacer clic en el botón Modificar comentario se mostrará de nuevo el recuadro del comentario permitiendo hacer la edición. Si por el contrario se desea eliminar el comentario será suficiente con pulsar el botón Eliminar.


17

EXCEL FINANCIERO




Funciones logicas y anidadas


       

Funciones lógicas Funciones si simple Funciones si anidada Funciones y simple Funciones y anidada Funciones o simple Funciones o anidada Comentarios

18


EXCEL FINANCIERO

Ejercicio 01



 Calcular el AFP teniendo en cuenta que si el sueldo es mayor a 500 será el 12% del sueldo y en caso contrario será el 9% del sueldo  Bonificación Si el área es "A" será del 8% en base al sueldo en caso contrario 6%

Ingrese la función necesaria para calcular  AFP: ___________________________  Bonificación: ____________________

Ejercicio 02  INSENT_CATEG: Si la categoría es "A" su incentivo será de 7% del sueldo, caso contrario solo tendrá un incentivo de 3%.  INSENT_CARG: Si cargo es Sistemas tendrá un incentivo de cargo de 5%, caso contrario solo le pertenecerá el 2%

Ingrese la función necesaria para calcular  Insent_categoria: ________________________________________  Insent_cargo: _____________________________________________  Total, sueldo: _____________________________________________


19


EXCEL FINANCIERO

Ejercicio 03 Si el vendedor vendió más de 1000, que gane el doble. Si vendió


menos, que lo deje igual


Ingrese la función necesaria para calcular  Sueldo Final: ________________________________________  Total, de vendedor: ___________________________________

Ejercicio 04. Si la edad es: 0 - 14 -- "NIÑO"; 15 - 17 -- "JOVEN" ; 18 - 30 -"ADULTO"; 31 "ANCIANO"  Si el participante es: “NIÑO" - S/. 10.00; "JOVEN" --S/. 15.00 "ADULTO" ---S/. 25.00; "ANCIANO" -- S/. 8.00

Ingrese la función necesaria para calcular  Participante: -________________________________________  Pago_ticket: __________________________________________

Ejercicio 05 Sueldo: Si el cargo es SISTEMAS -2300; SECRETARIA -800; AUXILIAR 100 SUPERVISOR – 230  Pago turnó: Si el turno es MAÑANA -30; NOCHE – 70  Bonificación: Si el cargo es SISTEMAS -8%; SECRETARIA -3%; AUXILIAR -5%; SUPERVISOR --2% y todo por el sueldo  Aumento turno: Si el turno es MAÑANA -8%; NOCHE --15% del sueldo


20



EXCEL FINANCIERO

Ingrese la función necesaria para calcular sueldo: -________________________________________ Pago turno: _____________________________________ bonificación: ____________________________________ Aum_turno: _____________________________________


   

Ejercicio 06 En el campo EVALUACION, se debe considerar: Si el PROMEDIO es: >=150 Entonces FAVORABLE Sino DESFAVORABLE.  En el campo OBSERVACION, se debe considerar: Si el PROMEDIO se encuentra entre: 90 - 110 MALO ;111 - 149 INSUFICIENTE ;150 - 160 APTO 161 - 170 REGULAR;171 - 180 NOTABLE ;181 - 190 EXCELENTE

Ingrese la función necesaria para calcular  Evaluación: _____________________________________  Observación: ____________________________________

Ejercicio 07 Si el nivel es mayor a cero y rendimiento es mayor que 5 se otorga Beca


21


EXCEL FINANCIERO

Ingrese la función necesaria para calcular  Se otorga beca: -____________________________________  Cuantos tienen beca: --______________________________

Ejercicio 08 Si el alumno realizo un determinado PAGO y ademas tiene la OBS de



SI , Entonces indicara un mensaje "Tiene Regalo", caso contrario no mostrara ningun mensaje.

Ingrese la función necesaria para calcular  Cuantos alumnos tienen regalo: ______________________  Regalo: -____________________________________________

Ejercicio 09 Si la producción se encuentra en (500 y 1000) tendrá una observación de "Permitido", caso contrario será "No Permitido".

Ingrese la función necesaria para calcular 

Total, de permitido: ______________________________________

EJERCICIO 10 Si la asistencia es ("A" O "T") tendrá 10 puntos, Caso contrario tendrá solo 3 puntos.


22


EXCEL FINANCIERO

Ingrese la función necesaria para calcular  Puntaje: _____________________________________________  Cantidad de puntaje 3: ________________________________ EJERCICIO 11 Si el destino es ("Trujillo" o "Huancayo") tendrá un descuento de



S/. 5.00, Caso contario no tendrá ningún descuento.

EJERCICIO 12 Si la categoría es "A" o es "B". Tendrá una asignación familiar de S/. 150, caso contrario será de solo S/. 50.

TAREA 01 En una hoja nueva desarrollar la evaluación de ventas


23


EXCEL FINANCIERO

Escribir la fórmula que inserto para hallar el ejercicio  Comisión: ___________________________________________  Aumento: ___________________________________________  Retención: _________________________________________  Total: ______________________________________________ TAREA 02



En una hoja nueva desarrollar la evaluación de ventas

Escribir la fórmula que inserto para hallar el ejercicio  totales: ______________________________________________  descuento: ___________________________________________  neto a pagar: __________________________________________ TAREA 03 Desarrollar el pago por carga adicional


24

Funciones de fecha y hora  Las funciones días. lab  La función días de semana  Ahora  año  Día  Fecha. Mes Hora Minuto segundo  Num.semana


EXCEL FINANCIERO




25


EXCEL FINANCIERO

FUNCIONES DE FECHAS Puedes indicar en Excel funciones para trabajar con fechas y horas, esta herramienta dispone de un gran número de fórmulas para realizar cálculos con estos parámetros.



Función ahora

Función hora Devuelve la hora de un valor de hora. Comprendido entre 0 (12:00 a.m.) y 16 (4:20:00 p.m.) Función minuto Devuelve los minutos de un valor de hora Función segunda Devuelve los segundos de un valor de hora

Función año Devuelve el año correspondiente a una fecha. El año se devuelve

como número entero comprendido entre 1992 y 2014. Función mes Devuelve el mes de una fecha representada por un número de serie. El mes se expresa entre 1 (enero) y 12 (diciembre). Función día Devuelve el día de una fecha especificada


26


EXCEL FINANCIERO

Función dias360 Calcula el número de días entre dos fechas basándose en un


año de 360 días (doce meses de 30 días)

Función fecha: Nos convierte tres números en formato de DD/MM/AAAA


FECHA (Año; Mes; Día).

Función fecha. Mes Use FECHA.MES para calcular las fechas de vencimiento que caen en el mismo día del mes que el día de emisión.

Función fin. Mes Devuelve el número de serie del último día del mes, anterior o posterior a la fecha inicial del número de mes indicado. Use FIN.MES para calcular las fechas de vencimiento que caen en el último día del mes.


27


EXCEL FINANCIERO

Función Dias.Lab La función DIAS.LAB nos permite especificar un conjunto de


días de vacaciones que serán excluidos de los días laborables contabilizados. Fecha inicial (obligatorio): Fecha final (obligatorio):


Función Dia.Lab Devuelve un número que representa una fecha que es el número de días laborables antes o después de una fecha (la fecha inicial)

Función si fecha (Año; mes; día) Devuelve la fecha en formato fecha

Sintaxis. La función SIFECHA es una función en Excel a la cual no tenemos acceso desde el catálogo de funciones y sin embargo ha estado presente en las últimas 7 versiones del programa incluyendo la versión 2016 de Excel. Esta función nos ayuda a obtener la diferencia entre dos fechas y además nos permitirá elegir el tipo de dato que deseamos como resultado y que pueden ser días, meses o años. La sintaxis de la función SIFECHA es la siguiente.


28


EXCEL FINANCIERO


Calcular las Edades


Validacion de datos La validación de datos se usa para controlar el tipo de datos o los valores que los usuarios pueden escribir en una celda. Por ejemplo, es posible que desee restringir la entrada de datos a un intervalo determinado de fechas

Pasos para ingresar  Ingresar a la pestaña datos  Grupo herramientas  Ubicarse en validacion de datos

Configuramos la validacion de datos selecionamos numero entero  Minimo=o  Maximo =20

 Resultado 


29

Funciones de fecha y hora  Las funciones días .lab  La función días de semana  Ahora  año  Día  Fecha. Mes Hora Minuto segundo  Num.semana


EXCEL FINANCIERO




30


EXCEL FINANCIERO


Ejercicio 01 Ud. debe solicitar el laboratorio a su estimado profesor:

Ingrese la función necesaria para calcular  Muestra la fecha completa: ____________________________________


Ejercicio 02

Ingrese la función necesaria para calcular  Hallar el día: _______________________________________  Hallar el mes: ______________________________________  Hallar la edad: ______________________________________ Ejercicio 03

Desarrolle de la siguiente manera    

REV REV REV REV

01 - Mostrar la fecha de revisión 2 meses antes de la revisión 3 02- Mostrar la fecha de revisión 1 meses antes de la revisión 3 04 - Mostrar la fecha de revisión 1 meses después de la revisión 3 05 - Mostrar la fecha de revisión 2 meses después de la revisión 3


31


EXCEL FINANCIERO

Ejercicio 04


Ingrese la función necesaria para calcular  Total, de días: _______________________________________


Ejercicio 05

Ingrese la función necesaria para calcular Hallar el total de días incluyendo las fiestas______________________ Hallar la fecha de supervisión_____________________________________

Ejercicio 06

Tener en Cuenta lo Siguiente hallar el día que nació considerando como día de inicio lunes hallar la semana del año considerando como día de inicio lunes:


32


EXCEL FINANCIERO


Ejercicio 07

Ingrese la función necesaria para calcular


Hallar la edad: __________________________________________________ Hallar tiempo de servicio en años _______________________________ Hallar tiempo de servicio en meses _____________________________

Ejercicio 08

Desarrolle de la siguiente manera (VALIDACION)  Sexo: M, F  Carrera: administración, contabilidad, computación,  DNI:8 dígitos “Solo debe permitir el ingreso de 8 dígitos Numéricos”

Ingrese la función necesaria para calcular  Mencione los pasos que realizo _______________________________________________________________


33


EXCEL FINANCIERO



TAREA 01

Tener en cuenta los Siguientes datos:  Tardanza: calcular los minutos de tardanzas del empleado en referencia a la hora de ingreso, si el empleado llego puntual debe mostrar un mensaje “puntual” caso contrario los minutos de tardanzas.  Saludos: si el empleado llego puntual debe mostrar un mensaje de felicitaciones caso contrario nada “”

TAREA 02

Escribir la fórmula que introdujo para hallar el ejercicio  Yo Tengo:__________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ _____________________________________________________


34

EXCEL FINANCIERO




Herramientas Analices de Datos


       

Tablas. Tablas Dinámicas. Columnas Calculadas. Gráficos Dinámicos. Columnas Calculadas. Porcentaje Total. Obtener Datos Externos. Bloc de Notas.

35


EXCEL FINANCIERO

Tablas Dinámicas.



Una tabla dinámica consiste en el resumen de un conjunto de datos, atendiendo a varios Criterios de agrupación, representado como una tabla de doble entrada que nos facilita La interpretación de dichos datos. Es dinámica porque nos permite ir obteniendo Diferentes totales, filtrando datos, cambiando la presentación de los datos, visualizando O no los datos origen, etc. Se tiene la siguiente tabla.

 Seleccionara una celda de la tabla.  Clic en la Ficha Insertar, grupo tablas  Clic en la Tabla Dinámica Nos muestra una ventana

 Seleccionamos el Rango De La Tabla luego le damos clic en aceptar  Y escogemos si deseamos crear la tabla en una nueva hoja o en la misma.  Se abrirá un panela lado derecho con todos los campos que presenta nuestra

 tabla y un gráfico en la hoja que representa a la tabla dinámica


36

EXCEL FINANCIERO



Arrastrar Referencia al cuadro Filtro De Informe Arrastrar Mes al cuadro Etiqueta de Fila Arrastrar Cantidad, Precio y Total al cuadro Valores Tu panel de la derecha se debe mostrar de esta manera.


   

Y nuestra tabla dinámica es la siguiente

 En el filtro referencia, por defecto muestra a todos, Ud. puede filtrar según el número que desea.  En la Etiqueta de Fila Ud. Puede escoger el mes que desea mostrar.


37


EXCEL FINANCIERO

Campos calculados Campo calculado (es el campo de un informe de tabla dinámica o de grafico dinámico que utiliza una fórmula que usted cree. Los campos calculados pueden realizar cálculos utilizando el contenido de otros campos del informe de tabla dinámica o de grafico dinámico). Para crear el siguiente campo calculado Desactivar Cantidad y Precio Se desea crear un campo impuestos, el cual se pagara el 2% del total.



Ir a la Ficha analizar de la Herramienta tabla dinámica. Grupo Cálculos, clic en Campos, elementos y Conjuntos y clic en Campo

 Clic en aceptar  Nos muestra automáticamente una columna nueva con el nombre impuesto  el resultado será el siguiente


38


EXCEL FINANCIERO



Para sacar el porcentaje de cada mes, según el total que viene a ser 100%  Arrastramos el campo total al cuadro de valores  Clic sobre el campo arrastrado y asemos clic en configuración de campo de valores  Y nos muestra la siguiente ventana

 Clic en aceptar  Tendremos una tabla como esta

Eliminar un campo calculado

 Ir a la ficha Opciones analizar de la Herramienta Tabla Dinámica  Grupo Cálculos, clic en Campos, elementos y conjuntos y clic en campo calculado.  Seleccionar el campo y dar clic en eliminar  Luego en dar clic en aceptar

Grafico dinámico Relacionado con las tablas dinámicas, Excel ofrece ahora gráficos dinámicos, que se adaptan a las modificaciones que se introduzcan en la tabla. Sin embargo, además, pueden usarse para resolver un problema que antes tenía una solución laboriosa y compleja:


39


EXCEL FINANCIERO

Crear un gráfico.     

Una vez creada su tabla dinámica. Grupo Herramienta, Clic en Grafico Dinámico Nos muestra una ventana Seleccionar el grafico deseado



Crear un grafico

Cada vez que Ud. modifica su tabla dinámica El grafico también cambiara

Ejemplo 02

La Asociación de Tenistas Profesionales, clasifica los jugadores cada semana según los puntos acumulados en los torneos en los que han participado. Para los diez primeros del ranking hemos recopilado los datos siguientes: nacionalidad, fecha nacimiento, altura, peso, nº torneos jugados, puntos obtenidos y ranking. como se muestra en la siguiente Tabla.


40


EXCEL FINANCIERO


1- Construir 2 tablas dinámicas que nos muestre:  El número de jugadores por cada país.


 El ranking de cada jugador, teniendo en cuenta que el campo país se debe poner externo a la tabla como celda desplegable.

IMPORTAR DATOS A EXCEL

Es difícil pensar en capturar manualmente todos los registros de una tabla de Access así que la herramienta nos provee de varios comandos para importar datos. En esta ocasión nos enfocaremos especialmente en cómo importar datos de ACCES A Excel de una manera sencilla. A continuación, describiré este proceso de importación de datos.      

Ficha datos Grupo datos externos Nos muestra una ventana Seleccionamos una de las Opciones del cual obtendremos los datos Ejemplo Access o texto etc.


41


EXCEL FINANCIERO





Herramientas analices de datos


 Tablas.       

Tablas Dinámicas. Columnas Calculadas. Gráficos Dinámicos. Columnas Calculadas. Porcentaje Total. Obtener Datos Externos. Bloc de Notas.

42


EXCEL FINANCIERO

EJERCICIO 01. Ubicarse en la Hoja Liquidación.



Ud. debe tener el siguiente cuadro de liquidación.

Insertar las siguientes formulas utilizando campos calculados.  Para calcular el Importe inserte la siguiente Formula =[Sueldo]*[Meses]  Para el Bono es el 2% del Importe ¿Qué formula inserto? __________________________________________________________  Para el seguro es el 15% del sueldo ¿Qué formula inserto? __________________________________________________________  ¿Que formula inserto para calcular el Sueldo Neto? __________________________________________________________

Activar la fila de totales.  ¿Cuál es el monto total? ________________________________________  ¿Cuál es el AFP mínimo? ________________________________________  ¿Cuál es bono máximo? ________________________________________  ¿Cuál es el promedio del importe? _________________________________________  ¿Cuál es total de meses? _________________________________________  ¿Cuál es el sueldo total _________________________________________  ¿Cuantos liquidados tenemos? _________________________________________


43


EXCEL FINANCIERO

EJERCICIO 02 Ubicarse en la Hoja Clientes Ud. debe tener el siguiente cuadro de Clientes.

Cod

Empresa

Categ Contacto

Edad Cargo Contacto

Zonal

Pais

C

Zbyszek Piestrzeniewicz

25

Propietario

martes-agosto-1996

Este

Polonia

2350

JYV 0002 Océano Atlántico Ltda.

E

Yvonne Moncada

31

Agente de ventas

jueves-marzo-1999

Este

Argentina

1267

JYV 0003 Laughing Bacchus

A

Yoshi Tannamuri

20

Asistente de marketing

lunes-mayo-1996

Este

Canadá

2780

JYV 0004 Coyote Import Store

A

Yoshi Latimer

25

Representante de ventas

martes-agosto-1992

Norte

Estados Unidos

3670

JYV 0005 Chop-suey Chinese

D

Yang Wang

25

Propietario

lunes-enero-1990

Sur

Suiza

1700

JYV 0006 B's Beverly

A

Victoria Ashworth

21


domingo-noviembre-1998

Sur

Reino Unido

2300

JYV 0007 Around the Horn

D

Thomas Hardy

24


domingo-enero-1994

Oeste

Reino Unido

2350


Fecha Ingreso

JYV 0001 Wolski Zajazd

Saldo


EJERCICIO 01:  Ubicarse en la Hoja Clientes  Crear una Tabla Dinámica en la Hoja País.  En las filas se debe mostrar el País, en las columnas las Zonas, sumar el saldo. Y Filtrar por Códigos.  Se debe mostrar Así:

EJERCICIO 02:  Crear una tabla en la Hoja Zonal.  En las Filas se debe mostrar las Zonas, en las Columnas los Países, Sumar el Saldo. Y Filtrar por Categorías.  Se debe mostrar así:



EJERCICIO 03:  Crear una tabla en la Hoja País por Categorías.  En las Filas se debe de mostrar el País, en las Columnas la Categoría, Sumar el Saldo. Y Filtrar por Zona. Se debe mostrar de esta manera Profesor: Omar Inga Sudario [email protected]

44




EXCEL FINANCIERO



EJERCICIO 04:



 Crear una tabla en la Hoja Zonas por Categoría.  En las Filas se debe mostrar la Zona, en las Columnas la Categoría, Sumar el Saldo. Y Filtrar por País.  Se debe mostrar así:

EJERCICIO 05:  Crear una tabla en la Hoja Categoría por Zona.  En las Filas se debe mostrar la Categoría, en las Columnas las Zonas, Sumar el Saldo. Y Filtrar por Contacto.  Se debe mostrar así:

CAMPOS CALCULADOS:

EJERCICIO 06:  Crear una tabla en la Hoja Saldos de Cargos.  En las Filas se debe mostrar los Cargos, sumar el Saldo. Y Filtrar por Empresa.  Se debe crear un campo Dscto que será el 10% del Saldo  Crear un campo Saldo Neto que se restara el Saldo con el Dscto.  Renombrar los campos creados por Descuento y Neto.  Se debe mostrar así:


45


EXCEL FINANCIERO

    

Crear una tabla en la Hoja Porcentaje Total. En las Filas se debe mostrar la Zona y el País, Sumar el Saldo. Agregar de nuevo el campo Saldo. Renombrarlo por % del Total. Se debe mostrar así:



EJERCICIO 07:

GRÁFICOS DINÁMICOS

EJERCICIO 08:    

De la tabla anterior Crear un gráfico circular 3D Personalizar para que se muestre sus etiquetas Resultado.

 Tener en cuenta que cada tabla dinámica debe tener un tipo de grafico dinamice estilo 07 Profesor: Omar Inga Sudario [email protected]

46


EXCEL FINANCIERO

Tarea 01 Construir a partir de los siguientes datos, las tablas dinámicas que muestren la siguiente información:



Desarrollar los Siguientes Casos: Tabla dinámica 1: Cantidad de personas por departamento. Las cuatro tablas dinámicas deben tener Gráficos.

Tabla dinámica 2: Cantidad de personas por departamento y delegación

Tabla dinámica 3: Suma y promedio de sueldo por departamento.

Tabla dinámica 4: Sueldo más alto por departamento y cargo.


47

ANALISIS DE UNA LISTA DE Datos


EXCEL FINANCIERO




 Base de Datos  Ordenación de registros  Filtros automáticos  Subtotales

48


EXCEL FINANCIERO

ANALISIS DE UNA LISTA DE DTOS Las actividades a desarrollar en la Unidad son: Actividad 01: “AUTOFILTROS”: Desarrolla las consultas realizadas para los casos indicados usando filtros automáticos o autofiltros.


Actividad 02: “SUBTOTALES”: Desarrolla los casos indicados realizando agrupamientos y creando así Resúmenes personalizados.

BASE DE DATOS


Las bases de datos en Excel son una colección organizada de información Relacionada entre sí, dividida en campos los cuales nos proporcionan datos Detallados e individuales de un universo de datos. Los elementos de una base de datos se consiguen detallar como:  Los campos: que son las columnas de la base de datos y contienen siempre un mismo tipo de información.  Los registros: que son las filas de la base de datos y contienen un conjunto de registros relacionados a un mismo elemento.

ORDENACION DE REGISTROS En una base de datos, los registros deben ser ordenados frecuentemente, quizás los queramos ordenar alfabéticamente, de mayor a menor, por colores por íconos. En Excel se tiene 2 métodos para ordenar datos


49


EXCEL FINANCIERO

Solución:  Haz clic en alguna celda dentro del campo mes (Ej. celda A2).  En la ficha Datos, grupo Ordenar y Filtrar, haz clic en el botón Ordenar de A -Z.



 Te darás cuenta que los registros ya se ordenaron.

Ejemplo 02: Utilizando una ficha de datos o formulario, dar de alta a los nuevos empleados en la base de datos.

 Ordenar la lista por apellido.  Ordenar por puntuación acumulada como criterio primario y por puntuación como criterio secundario, de mayor a menor.


50


EXCEL FINANCIERO

 Ordenación de Registros por más de un Campo En nuestro ejemplo anterior tenemos ordenado la base de datos por MES Ahora tenemos los registros agrupados por mes y se encuentran en orden alfabético. Para ordenar por más de un campo, por ejemplo, ordenar la base de datos Por Ciudad y luego por Meses, se realiza lo siguiente:

Solución:



 Haz clic en cualquier celda dentro de la base de datos  En la ficha Datos, grupo Ordenar y Filtrar, haz clic en el botón Ordenar  Insertamos los campos para ordenar como se ve en la imagen  Luego le damos clic en aceptar

El resultado será el siguiente:

FILTROS AUTOMATICOS Y AUTO FILTROS

Cuando manejamos bases de datos es muy usual que realicemos consultas, esto exige que en muchos casos extraer registros bajo ciertas condiciones. Los autofiltros permiten seleccionar registros de una base de datos que consiguen cumplir con determinadas condiciones. Con la siguiente base de datos


51


EXCEL FINANCIERO

resuelve los siguientes ejercicios.  Haz clic en cualquier celda, dentro de la base de datos  En la ficha Datos, grupo Ordenar y Filtrar, haz clic en el botón Filtro.  Filtrar a las perdonas de la categoría A Y d y que su edad sea mayor a 23



 El resultado será el siguiente

Ejemplo 02 nos ubicamos en la hoja filtro II - Filtrar sólo los empleados que pertenecen al Departamento de Diseño… “8 registros”

Ejemplo 03 Mostrar los empleados de la zona Norte y oriente con ventas mayores a 3500


52


EXCEL FINANCIERO

FILTROS AVANZADOS, son otra forma de filtrar y se utiliza con criterios complejos consisten en Especificar las condiciones necesarias de Filtrado en un RANGO



Ejemplo 01 Filtra los empleados cuyo nombre comience con J y sean mayores de 20 y los que comiencen con R y de sueldo mayor de 1500

Ejemplo 02 En la hoja "Filtro avanzado II” seleccione aquellos registros que cumplan con las siguientes condiciones:  Mujeres mayores a 30 años  Región VIII y XIII

Rpta:41 Registros Rpta:57 Registros

 Puntaje mayor 50 y menor 75 Rpta:51 Registros


53


EXCEL FINANCIERO

SUBTOTALES Cuando tenemos una lista o base de datos grande muchas veces necesitamos crear resúmenes personalizados, Microsoft Excel crea esquemas que logran mostrar y ocultar filas de detalle de cada subtotal.

Ejercicio01



 Calcular la Total, promedio, cantidad por Ciudad.  Lo primero que debes realizar, es ordenar la base de  Datos, en nuestro ejercicio es por Ciudad  Ubicarse en la Hoja subtotal 01  Pestaña datos grupo esquema  clic en sub total Nos muestra el siguiente cuadro

 Clic en aceptar


54


EXCEL FINANCIERO



Ejercicio 02. A partir de la siguiente lista de empresas que cotizan en bolsa:

Insertar automáticamente subtotales de forma que visualicemos el valor cierre en soles total por sectores de empresas (previamente se deberá ordenar la base de datos en sectores).  Además, sería interesante destacar el valor máximo y mínimo de la rentabilidad total, así como la suma de la variación del Ibex.  Con los subtotales obtenidos, crear un gráfico circular con subgráfico de barras que muestre los promedios de las cotizaciones de las empresas ordenadas por sectores


55


EXCEL FINANCIERO

Consolidación de datos Consolidar datos en varias hojas de cálculo Para resumir y registrar resultados de hojas de cálculo independientes, puede consolidar datos de cada una de estas hojas en una hoja de cálculo maestra Al consolidar datos, lo que se hace es ensamblarlos de modo que sea más fácil actualizarlos y agregarlos de una forma periódica o específica

Ejemplo 01 Nos ubicamos en la hoja consolidación de datos


En la celda C3, Pestaña datos grupo herramientas de datos clic en consolidación.


 Nos muestra la siguiente ventana

El resultado será el siguiente


56


EXCEL FINANCIERO




ANALISIS DE UNA LISTA DE DATOS    

Base de Datos Ordenación de registros Filtros automáticos Subtotales

57


EXCEL FINANCIERO

EJERCICIO 01: Ud. debe contar con el laboratorio solicite a su estimado profesor



 Ordenar por Local

EJERCICIO 02: Filtrar sólo los empleados cuyo apellido comience con C y sus sueldos estén comprendidos entre 25,000 y 44,000  Cuantos registros hay: _________________________________________________

 Filtrar todo el empleado que sean del cargo aux. Cuantos registros hay: ________________________________________________  Filtrar a todo el cliente que pertenecen al departamento de investigación y sean de la sección impresoras Cuantos registros hay: ________________________________________________

EJERCICIO 03: Muestre a todas las solteras que estudian en las escuelas de Sistemas y Educación; con deudas inferiores a 100 y superiores a 400 nuevos soles, con un ponderado no menor de 14, y además que practiquen vóley como deporte  Cuantos registros hay: _________________________________________________


58


EXCEL FINANCIERO

 Muestre a todos los alumnos con promedio ponderado entre 15 y 17, que provienen de las ciudades de Chiclayo y Lima; además que pertenezcan a la escuela de Derecho y cuyas edades se encuentren entre 30 y 40 años Cuantos registros hay: _________________________________________________  Muestres a las 15 alumnas(a) con la deuda más baja de la base de datos. Cuantos registros hay: _________________________________________________  Muestre a todos los alumnos solteros que su edad sea mayor a 25 del ciclo III Cuantos registros hay: _________________________________________________


 Muestre a los alumnos de sistema y educación con modalidad de ingreso directo estado civil soltero Cuantos registros hay: _________________________________________________


EJERCICIO 04: Mostrar las personas de chile y argentina de sexo masculino que estén tramitando autorización de viaje de menor, los 3 últimos meses del año 2010 Rpt: 6 registros Cuantos registros hay: _________________________________________________

 Mostrar las personas del continente americano que estén tramitando visa de estudiante que son de sexo masculino y q la fecha de trámite se haya realizado en el año 2010 y su ciudad sea MIAMI, buenos aires rpt; 12 registros Cuantos registros hay: _________________________________________________

EJERCICIO 05: Ud. Debe trabajar con filtros avanzados  Muestres los clientes que su sueldo sea mayor que 5000000 y su edad mayor a 25 años de sexo f Cuantos registros hay: _________________________________________________


59


EXCEL FINANCIERO

 Muestres los clientes de la ciudad de rioacha y Cali y su edad mayor a 18 años Cuantos registros hay: _________________________________________________  Muestre a los clientes de la ciudad de Cali de sexo femenino y del barrio paraíso Cuantos registros hay: _________________________________________________  Muestre a los clientes de sexo masculino del barrio de paraíso y Cuantos registros hay: _________________________________________________


Nota: Debe contener macros para realizar todo tipo de consultas


EJERCICIO 06: Ud. Debe trabajar con filtros avanzados

Nota:

Debe mostrar todas las consultas independientemente es decir cada consulta con su propia tabla en una hoja “Mostrar todas las consultas realizadas” y también Grabar macro. 1. Realice un filtro avanzado para mostrar los empleados de las secciones de fotocopiadoras o Fax que trabajen en los departamentos de contabilidad 2. Liste los empleados auxiliares que su salario sea menor de 23500 3. Liste a los empleados que comenzaron 01/01/1990 y que tengan bajas por enfermos mayor o igual que 4 4. listar a los empleados cuyo número de identificación sea mayor o igual a 1700 y cuyo nombre empiece por L. 5. Liste a los empleados de 1991 que no hayan tenido vacaciones


60


EXCEL FINANCIERO



Ejercicio 07: Ud. Debe trabajar con subtotales

Ordenar el puesto A-Z Mostrar la suma, máximo y mínimo del sueldo en referencia al puesto”  Promedio de banca por puesto: ________________________________  Máximo de asesor: ___________________________________

EJERCICIO 08 Ud. Debe trabajar con subtotales

Mostrar la cuenta de producto por presentación Cantidad de advantege en cajas: _________________________________ Cantidad de ampicilina en cajas: _________________________________ Cantidad de deaxcor en cajas; ___________________________________ Cantidad de rintal en gotas: ______________________________________ Cantidad de vecotide en inhalador: _______________________________ Cantidad de ventolin:_____________________________________________ Cuenta general: __________________________________________________ Total, de unidades despachadas; _________________________________


61


EXCEL FINANCIERO



EJERCICIO 09: consolidar datos de abril, mayo y junio En la Hoja Resumen

 Crear un gráfico en 3D que muestra la información del consolidado


62

EXCEL FINANCIERO




TEMAS DE EVALUACION


      

Funciones matemáticas Funciones estadísticas Función de texto Función de búsqueda Funciones lógicas Funciones de fecha Tablas dinámica

63


EXCEL FINANCIERO

Ejercicio 01 Evalué la Condición. Si la forma de pago es al contado tendrá un



descuento de 25%.del total caso contrario 0

 Totales: ___________________________________________________  Descuento______________________________________________  Neto a pagar: _________________________________________

Ejercicio 02

 Sueldo: ______________________________________________  Incentivo___________________________________________  Pago neto: _________________________________________

Ejercicio 03


64

Vive la programación en Ti...!      

EXCEL FINANCIERO

Bonificación: ___________________________________________________ Gratificación______________________________________________ Descuento: ______________________________________________________ Boni_hijo: _____________________________________________________ Retención: ______________________________________________________ Total: _______________________________________________________



Ejercicio 04

 Promedio: ___________________________________________________  Observación______________________________________________  Puesto: ______________________________________________________

EJERCICIO 05 calcular dicho ejercicio

   

sueldo: ___________________________________________________ bonificación______________________________________________ descuento: _______________________________________________ total, apagar______________________________________________


65


EXCEL FINANCIERO




 Tener en cuenta las siguientes Información

 AREA ALMACEN VENTAS PRODUCCION OTROS

BASICO S/. 1,500.00 S/. 1,200.00 S/. 1,100.00 S/. 900.00

 TOTAL, PRODUCCION <80 S/. 10.00 >=80 S/. 25.00 >=100 S/. 35.00 >=200 S/. 50.00  AREA ALMACEN VENTAS PRODUCCION OTROS

SNP 2.0% 2.5% 3.0% 1.5%

GANANCIA

AFP 1.5% 2.0% 2.5% 2.2%



66

Vive laNos programación Ti...! EXCEL FINANCIERO amamos en unos a otros porque él nos Amó primero 1Juan 4:19

Síguenos en FB

Escuadrón Vida Perú Amor en Acción

Cuando uno da amor, cuando uno da atención la gente se habré la gente dice yo quiero escuchar el mensaje que va traer la respuesta no solamente a una necesidad inmediata sino a una necesidad eterna eso es Escuadrón Vida Perú Si Dios está en tu vida, el amor que brindes no será el tuyo sino el de Dios. Te será más fácil perdonar y amar sin reservas. Hoy comienza a vivir una vida plena llena del amor de Dios.

Quiero decirte que Dios ha estado, está y seguirá estando contigo siempre todos los días hasta el fin del mundo


Sudario.omar@gmail.

Escuadrón Vida Perú

Fundador: 67 Omar Inga Sudario


¡Únete a la causa patrocinando a algún niño con un regalo o cualquier otra colaboración que guste y así poder ser Canal de Bendición!


v

EXCEL-FINANCIERO.pdf

Recommend Documents