Manual de Excel Avanzado 2013

EXCEL

2013

NIVEL AVANZADO

ÍNDICE SESIÓN 1 ....................................................................................................................... 1 1. Base de datos Conceptos y alcances sobre base de datos .................................................................. 2 2. Funciones de Base de Datos ........................................................................................ 2 BDSUMA ..................................................................................................................... 3 BDMAX ....................................................................................................................... 3 BDMIN ........................................................................................................................ 3 BDPROMEDIO ............................................................................................................. 4 BDCONTAR ................................................................................................................. 4 BDCONTARA ............................................................................................................... 4 BDDESVEST ................................................................................................................. 5 BDVAR ........................................................................................................................ 5 3. Formulas Matriciales ................................................................................................... 7 Sintaxis Formulas Matriciales ...................................................................................... 9 4. Prácticas Dirigidas Sesión 1 ......................................................................................... 10 5. Prácticas Propuestas Sesión 1 ..................................................................................... 16

SESIÓN 2 ...................................................................................................................... 22 1. Tablas Dinámicas ....................................................................................................... 23 Origen de datos de la Tabla Dinámica ........................................................................ 23 Filtro Dinámico – Informes de Tabla Dinámica ........................................................... 24 Crear un Informe de Tabal Dinámica .......................................................................... 25 Crear y cambiar el diseño de los campos .................................................................... 26 Agregar campos ......................................................................................................... 28 Organizar los campos ................................................................................................. 29 Crear, editar o eliminar una fórmula de tabla o gráfico dinámico ............................... 30 Agregar un elemento calculado a un campo .............................................................. 31 Mostrar una lista de fórmulas .................................................................................... 32 Editar una Fórmula .................................................................................................... 33 Editar una Fórmula de campo calculado .................................................................... 33 Modificar una única fórmula para un elemento calculado ......................................... 33 Eliminar Fórmula ........................................................................................................ 34 Eliminar un campo calculado ..................................................................................... 34 Eliminar un elemento calculado ................................................................................. 34 2. Informes de gráficos dinámicos ................................................................................. 34 Pasos para crear un gráfico dinámico ......................................................................... 35 Modificar las características del gráfico respecto a su presentación ........................... 39 3. Prácticas Dirigidas Sesión 2 ........................................................................................ 40 4. Prácticas Propuestas Sesión 2 .................................................................................... 45

SESIÓN 3 ...................................................................................................................... 47 1.

Funciones Financieras .............................................................................................. 48 La aplicación de Funciones Financieras ..................................................................... 50 Función Valor Futuro ................................................................................................ 51 Función PAGO .......................................................................................................... 51 Función PAGOINT ..................................................................................................... 52

Función PAGOPRIM .................................................................................................. 52 Función NPER ........................................................................................................... 53 Función TASA ........................................................................................................... 53 Prácticas Dirigidas Sesión 3 ...................................................................................... 54 Prácticas Propuestas Sesión 3 ................................................................................... 58

SESIÓN 4 ...................................................................................................................... 61 Herramientas de datos ............................................................................................... 62 Auditoria de Fórmulas ................................................................................................ 64 Buscar Objetivo .......................................................................................................... 66 Tabla de datos ............................................................................................................ 67 Escenarios .................................................................................................................. 70 Instalando Solver ....................................................................................................... 73 Defina y solucione un Problema ................................................................................. 74 Prácticas Dirigidas Sesión 4 ........................................................................................ 80 Prácticas Propuestas Sesión 4 .................................................................................... 83

SESIÓN 5 ...................................................................................................................... 86 Formularios ................................................................................................................ 87 Pasos para activar los controles de formulario ........................................................... 88 Insertar Control de Formulario ................................................................................... 89 Prácticas Dirigidas Sesión 5 ......................................................................................... 98 Prácticas Propuestas Sesión 5 .................................................................................. 104

SESIÓN 6 .................................................................................................................... 107 Conceptos y descripción para su aplicación .............................................................. 108 Grabar macros ......................................................................................................... 109 Tipos de macros ....................................................................................................... 109 Aplicación en Excel de macros Funcionales .............................................................. 110 Manipulación de Macros .......................................................................................... 113 Prácticas Dirigidas Sesión 6 ...................................................................................... 115 Prácticas Propuestas Sesión 6 .................................................................................. 118

SESIÓN 7 .................................................................................................................... 120 Exámenes Finales .................................................................................................... 121

Excel 2013 – Nivel Avanzado

INFO UNI

Sesión 1 1. BASE DE DATOS Conceptos y alcances sobre Base de Datos 2. FUNCIONES DE BASE DE DATOS      

BDSUMA BDMAX BDMIN BDPROMEDIO BDCUENTA BDCONTARA

3. FORMULAS MATRICIALES 

CREACION DE FORMULAS MATRICIALES CON CONDICIONES MULTIPLES HACIENDO USO DE LAS FUNCIONES BASICAS SUMA, MAX, MIN Y PROMEDIO

Universidad Nacional de Ingeniería

Pág. N° 1


INFO UNI

1.- BASE DE DATOS: CONCEPTOS Y ALCANCES Una base de datos o banco de datos es un conjunto de datos pertenecientes a un mismo contexto y almacenados sistemáticamente para su posterior uso. En este sentido, una biblioteca puede considerarse una base de datos compuesta en su mayoría por documentos y textos impresos en papel e indexados para su consulta. Actualmente, y debido al desarrollo tecnológico de campos como la informática y la electrónica, la mayoría de las bases de datos están en formato digital (electrónico), y por ende se ha desarrollado y se ofrece un amplio rango de soluciones al problema del almacenamiento de datos. Se define una base de datos como una serie de datos organizados y relacionados entre sí, los cuales son recolectados y explotados por los sistemas de información de una empresa o negocio en particular. Tabla en las bases de datos, se refiere al tipo de modelado de datos, donde se guardan los datos recogidos por un programa. Su estructura general se asemeja a la vista general de un programa de hoja de cálculo. Una tabla es utilizada para organizar y presentar información. Las tablas se componen de filas y columnas de celdas que se pueden rellenar con textos y gráficos Las tablas se componen de dos estructuras: Registro: es cada una de las filas en que se divide la tabla. Cada registro contiene datos de los mismos tipos que los demás registros. Ejemplo: en una tabla de nombres y direcciones, cada fila contendrá un nombre y una dirección. Campo: es cada una de las columnas que forman la tabla. Contienen datos de tipo diferente a los de otros campos. En el ejemplo anterior, un campo contendrá un tipo de datos único, como una dirección, o un número de teléfono, un nombre, etc.

2.- FUNCIONES DE BASE DE DATOS Las funciones de bases de datos sirven para realizar cálculos en una hoja de cálculos Excel. Es posible realizar operaciones para obtener el total, promedio, mínimo o máximo, además de contar o sumar el contenido del campo (columna o fila) de una base de datos en particular, esto es siempre que y cuando los criterios de verificación se cumplan. Las funciones de Base de Datos (Database Functions, DBase Functions). Estas funciones permiten analizar y realizar cálculos en base a listas. En Excel "lista" es una tabla de datos que en su primera fila tiene encabezamientos. Cada una de estas funciones, denominadas colectivamente funciones BD. Se utiliza tres argumentos: 1.- base_de_datos 2.- nombre_de_campo 3.- criterios Estos tres argumentos se refieren a los rangos de la hoja de cálculo empleados en la función para la gestión de una base de datos. El siguiente resumen muestra la lista de cuáles son las funciones de Base de Datos predefinidas en Excel. BDPROMEDIO BDEXTRAER BDPRODUCTO BDSUMA


BDCONTAR BDMAX BDDESVEST BDVAR

BDCONTARA BDMIN BDDESVESTP BDVARP

Pág. N° 2


INFO UNI

01.- FUNCIÓN BDSUMA Esta función suma los números en el campo (columna) de los registros que coinciden con las condiciones especificadas. Base de Datos es el rango de celdas que compone la lista o base de datos. Una base de datos es una lista de datos relacionados.

02.- FUNCIÓN BDMAX Esta función devuelve el número máximo del campo (columna) de registros que coinciden con las condiciones especificadas.

03.- FUNCIÓN BDMIN Esta función devuelve el número menor del campo (columna) de registros que coinciden con las condiciones especificadas.


Pág. N° 3


INFO UNI

04.- FUNCIÓN BDPROMEDIO Esta función permite obtener el promedio de los valores de una columna, lista o base de datos que cumplen las condiciones especificadas.

05.- FUNCIÓN BDCONTAR La función BDCONTAR, permite el conteo de celdas que contienen números en el campo (columna) de registros de la base de datos que cumplen las condiciones especificadas.

06.- FUNCIÓN BDCONTARA La función BDCONTARA, permite el conteo de celdas que no están en blanco (columna) de los registros de la base de datos que cumplen las condiciones especificadas.


Pág. N° 4


INFO UNI

07.- FUNCIÓN BDDESVEST La función BDDESVEST, calcula la desviación (extravió, virada) estándar basándose en una muestra de las entradas seleccionadas de una base de datos.

08.- FUNCIÓN BDVAR La función BDVAR, calcula la varianza basándose en una muestra de las entradas seleccionadas de una base de datos.

Pasos para su aplicación: En la siguiente imagen se muestra una base de datos de un huerto. Cada registro contiene información acerca de un árbol. El rango A5:E11 se denomina Base de Datos y el rango A1:F3 Criterios


Pág. N° 5


INFO UNI

Función BDCONTAR Esta función examina los registros de los manzanos cuyo alto varía entre 10 y 16 metros y determina cuántos campos Edad de esos registros contienen números.

=BDCONTAR(A5:E11,"Edad",A1:F2)

Función BDCONTARA Esta función examina los registros de los manzanos cuyo alto varía entre 10 y 16 metros y determina el número de campos Ganancia de esos registros que no están en blanco.

=BDCONTARA(A5:E11,"Ganancia",A1:F2)

Función BDMAX Esta función permite maximizar un determinado rango de valores. Para el siguiente ejemplo obtener la ganancia máxima de manzanos y perales.

=BDMAX(A5:E11,"Ganancia",A1:A3)

Función BDMIN Esta función permite minimizar un determinado rango de valores. Para el siguiente ejemplo obtener la ganancia mínima de manzanos con un alto superior a 10 metros.

=BDMIN(A5:E11,"Ganancia",A1:B2)

Función BDSUMA Esta función permite la sumatoria de valores por un determinado criterio de la tabla. Ejemplo 1: La ganancia total de los Manzanos.

=BDSUMA(A5:E11,"Ganancia",A1:A2)

Ejemplo 2: La ganancia total del Manzano y Peral.

=BDSUMA(A5:E11,"Ganancia",A1:A3)

Ejemplo 3: La ganancia total de los Manzanos con un alto entre 10 y 16.

=BDSUMA(A5:E11,"Ganancia",A1:F2)


Pág. N° 6


INFO UNI

Función BDPROMEDIO Esta función permite obtener el promedio de valores por un determinado criterio de la tabla. Ejemplo: La edad media de todos los árboles en la base de datos.

=BDPROMEDIO(A5:E11,3,A5:E11)

Función BDDESVEST Es la desviación estándar estimada de la base de datos, de una muestra de la población. Ejemplo: Obtener la desviación estándar estimada en el rendimiento de manzanos y perales de la población total del huerto.

=BDDESVEST(A5:E11,"Rendimiento",A1:A3)

Función BDVAR Es la varianza estimada de la base de datos, de una muestra de la población. Ejemplo: Obtener la varianza estimada en el rendimiento de manzanos y perales de la población total del huerto.

=BDVAR(A5:E11,"Rendimiento",A1:A3)

FORMULAS MATRICIALES FORMULAS MATRICIALES (ARRAY) Las funciones matriciales en Excel permiten efectuar cálculos muy interesantes y proporcionan a la hoja de cálculo una enorme potencia. Las funciones matriciales, en general, abarcan varias celdas, aunque en algunos casos únicamente se centran en una única celda. Las fórmulas matriciales se identifican ya que se encuentran entre corchetes { } El concepto de Matriz viene de los lenguajes de programación y de la necesidad de trabajar con varios elementos de forma rápida y cómoda. Podríamos decir que una matriz es una serie de elementos formando filas (matriz bi-dimensional) o filas y columnas (matriz tri-dimensional). La siguiente tabla representa una matriz bidimensional:


Pág. N° 7


INFO UNI

La siguiente tabla representa una matriz tridimensional:

Observar por ejemplo el nombre del elemento 3,4 que significa que está en la posición de fila 3, columna 4. En Excel, podemos tener un grupo de celdas en forma de matriz y aplicar una fórmula determinada en ellas de forma que tendremos un ahorro del tiempo de escritura de fórmulas. En Excel, las fórmulas que hacen referencia a matrices se encierran entre corchetes { }. Hay que tener en cuenta al trabajar con matrices lo siguiente:  No se puede cambiar el contenido de las celdas que componen la matriz  No se puede eliminar o mover celdas que componen la matriz  No se puede insertar nuevas celdas en el rango que compone la matriz

La potencia de las fórmulas matriciales es que nos permiten analizar matrices de datos aplicando filtros complejos por columnas o filas. La fórmula más simple y parecida es el “SUMAR.SI” que actúa como una fórmula matricial con una sola condición. Una típica fórmula matricial es aplicar una serie de condiciones para obtener una suma final, pero se puede hacer con otras funciones como “PROMEDIO”, “MEDIA”, “MAX” o “MIN”.

CONSTANTES MATRICIALES Al igual que en las fórmulas normales podemos incluir referencias a datos fijos o constantes, en las fórmulas matriciales también podemos incluir datos constantes. A estos datos se les llama constantes matriciales y se debe incluir un separador de columnas (símbolo ;) y un separador de filas (símbolo \).

Para introducir una fórmula matricial: 1. Seleccionar el rango en el que la fórmula matricial dejará sus valores. Si la fórmula matricial no abarca más de una celda simplemente no situaremos en esa celda. 2. Escribir la fórmula propiamente dicha 3. Para validar no se ha de pulsar ENTER (Intro), sino que lo que hemos de pulsar es la combinación de tres teclas: CONTROL + SHIFT + ENTER

¿Qué es una fórmula matricial y para qué sirve? Explicación: A veces es práctico realizar dos operaciones en una sola celda, por ejemplo, multiplicar varias celdas y luego sumar su resultado. Otras veces es necesario aplicar una serie de transformaciones a un rango de celdas y luego poder sumar su resultado. En ambos casos, las fórmulas matriciales pueden resultar de utilidad.


Pág. N° 8


INFO UNI

SINTAXIS DE FORMULAS MATRICIALES Las formulas matriciales nos permiten realizar cálculos en base a criterio

I.- PARA APLICAR FORMULAS MATRICIALES CON UN CRITERIO SINTAXIS =SUMA((RANGO=”Criterio”)*1) Ejemplo Nº 1: Realizar un conteo del personal por género de sexo Femenino Ejemplo: Asignando el nombre SEXO al rango desde B4 hasta B202 =SUMA((SEXO="F")*1) Ejemplo Nº 2: Realizar un conteo del personal por género de sexo Masculino Ejemplo: Asignando el nombre SEXO al rango desde B4 hasta B202 =SUMA((SEXO="M")*1)

II.- PARA APLICAR FORMULAS MATRICIALES CON DOBLE O MÁS CRITERIOS SINTAXIS =SUMA((RANGO1=”Criterio1”)*(RANGO2=”Criterio2” ”)*(RANGO3=”Criterio3”)) Ejemplo: Cuantas Mujeres con MÁS DE 30 años, son VIUDAS y con más de 2 HIJOS =SUMA((SEXO="F")*(EDAD>30)*(ESTADO="VIUDO")*(HIJOS>2))

III.- FORMULAS MATRICIALES CON DOBLE O MÁS CRITERIOS CON SUMA SINTAXIS =SUMA((RANGO1=”Crit.1”)*(RANGO2=”Crit.2” ”)* … *(RANGO A SUMAR))

IV.- FORMULAS MATRICIALES CON CRITERIO CON OPERACIÓN DE PROMEDIO SINTAXIS =PROMEDIO(SI(RANGO=”Criterio”,RANGO A PROMEDIAR))

V.- FORMULAS MATRICIALES CON CRITERIO CON OPERACIÓN DE MAXIMIZAR =MAX(SI(RANGO=”Criterio”,RANGO A MAXIMIZAR))

VI.- FORMULAS MATRICIALES CON CRITERIO CON OPERACIÓN DE MINIMIZAR =MIN(SI(RANGO=”Criterio”,RANGO A MINIMIZAR))


Pág. N° 9


INFO UNI

PRÁCTICAS DIRIGIDAS NIVEL AVANZADO SESIÓN 1 Universidad Nacional de Ingeniería

Pág. N° 10


INFO UNI

PRÁCTICA 1- FUNCIONES DE BASE DE DATOS 1.- Digitar la siguiente tabla referente a un Reporte de I Semestre 2013 con un total de 14 registros o fila de datos. Seleccionar toda la tabla y asignarle el nombre REPORTE.

2.- Digitar, diseñar la siguiente tabla de doble entrada para obtener un Informe sobre los Ingresos del I Semestre 2013, según las interrogantes como: Cantidad de Ingresos, Ingresos Máximos, Ingresos Mínimos, Total de Ingresos y Promedio de Ingresos.

3.- Con los siguientes planteamientos vamos a completar la Tabla resumen (doble entrada), acerca del Informe sobre los Ingresos del I Semestre 2013 Celda H9 =BDCONTARA(REPORTE,$A$6,H6:H$7) hacer arrastres de mouse hasta la celda K9 Celda H10 =BDMAX(REPORTE,$C$6,H6:H7) hacer arrastres de mouse hasta la celda K10 Celda H11 =BDMIN(REPORTE,$C$6,H6:H7) hacer arrastres de mouse hasta la celda K11 Celda H12 =BDSUMA(REPORTE,$C$6,H6:H7) hacer arrastres de mouse hasta la celda K12 Celda H13 =BDPROMEDIO(REPORTE,$C$6,H6:H7) hacer arrastres de mouse hasta la celda K13


Pág. N° 11


INFO UNI

PRÁCTICA 2 - FUNCIONES DE BASE DE DATOS 1.- Digitar la siguiente tabla referente a Ventas del II Trimestre 2013 con un total de 50 registros o fila de datos. Seleccionar toda la tabla y asignarle el nombre DATOS.

En la siguiente imagen se podrá comprobar el último registro de la tabla de origen.

2.- Digitar la siguiente tabla para obtener un Informe según las interrogantes como N° de Ventas y Venta Máxima, etc., para utilizar las Funciones de base de datos – BD.

3.- Con los siguientes planteamientos vamos a completar la Tabla acerca del Informe de Ventas según los Vendedores: En la celda H7 para el Nº de Ventas =BDCONTARA(DATOS,$A$4,H4:H5) En la celda H8 para calcular la Venta Máxima =BDMAX(DATOS,$C$4,H4:H5) En la celda H9 para calcular la Venta Mínima =BDMIN(DATOS,$C$4,H4:H5) En la celda H10 para calcular la Venta Total =BDSUMA(DATOS,$C$4,H4:H5) En la celda H11 para calcular Venta Promedio =BDPROMEDIO(DATOS,$C$4,H4:H5) En la celda H12 calcular la comisión del 8% =H11*8% (el 8% del Promedio de ventas)


Pág. N° 12


INFO UNI

PRÁCTICA 1 - FÓRMULAS MATRICIALES I.- PARA APLICAR FORMULAS MATRICIALES CON UN CRITERIO =SUMA((RANGO=”Criterio”)*1)

II.- PARA APLICAR FORMULAS MATRICIALES CON DOBLE O MÁS CRITERIOS =SUMA((RANGO1=”Criterio1”)*(RANGO2=”Criterio2” ”)*(RANGO3=”Criterio3”))

1.- Si se tiene una serie de importes, podemos calcular la suma total con un reintegro del 23% en una sola celda, se podrá comprobar en la siguiente imagen:

2.- En la celda C8 digitar la siguiente fórmula =SUMA(C2:C7/1.23). Ejecutar la formula matricial pulsando las teclas CONTROL + SHIFT + ENTER

3.- En la siguiente imagen se podrá visualizar la tabla completa con Total de Importe


Pág. N° 13


INFO UNI

PRÁCTICA 2 - FÓRMULAS MATRICIALES 1.- Digitar la siguiente tabla que contiene información referente al Control de Utilidades del I Semestre del año 2013. Comprobar el contenido en la siguiente imagen.

2.- Digitar y diseñar la siguiente presentación de datos con el objetivo para calcular la Comisión de Utilidad del 6%, con respecto a los Importes del I Semestre 2013.

Seleccionar el relleno C21 hasta H29

Digitar =C8:H16*B9 Después pulsar las teclas CONTROL + SHIFT + ENTER


Pág. N° 14


INFO UNI

Obteniendo la siguiente respuesta respecto al rango de C21 hasta H29.

3.- Digitar y diseñar la siguiente presentación de datos con el objetivo para calcular las Ventas + Comisión del 6%, con respecto a los Importes del I Semestre 2013.

Para calcular las Ventas + Comisión del 6%, debemos de seleccionar un rango C34:H42 despues de digitar la fórmula =C8:H16+C21:H29 después pulsar las teclas CONTROL + SHIFT + ENTER para obtener las respuesta de Ventas + Comisión del 6%.


Pág. N° 15


INFO UNI

PRÁCTICAS PROPUESTAS NIVEL AVANZADO SESIÓN 1 Universidad Nacional de Ingeniería

Pág. N° 16


INFO UNI

PRÁCTICA PROPUESTA N° 1 - FUNCIONES DE BD 1.- Teniendo como referencia la siguiente tabla conformado por Ventas del 1er Semestre del año 2013 conformada por 55 registros (filas de datos). En la siguiente imagen se podrá comprobar los 8 primeros registros.

En la siguiente imagen se podrá comprobar los 4 últimos registros.

PRACTICA PROPUESTA: Preparar una tabla de doble entrada referente al campo PRODUCTOS para calcular N° de Ventas, Venta Máxima, Venta Mínima, Venta Total y Venta Promedio.

Preparar otras tablas de doble entrada en base a los campos MES, VENDEDORES, DISTRITO para calcular N° de Ventas, Venta Máxima, Venta Mínima, Venta Total y Venta Promedio.


Pág. N° 17


INFO UNI

PRÁCTICA PROPUESTA N° 2 - FUNCIONES DE BD

Funciones de Base de Datos - BD, para utilizar la Desviación Estándar En las funciones de Base de Datos - BD en el programa Excel se utiliza las funciones BDDESVEST y BDDESVETP para calcular DESVIACIÓN ESTÁNDAR.

PARTIENDO DEL CONCEPTO: La desviación estándar o también denominado desviación típica es una medida de dispersión para variables de razón (ratio o cociente) y de intervalo, de gran utilidad en la estadística descriptiva. Es una medida (cuadrática) de lo que se apartan los datos de su media, y por tanto, se mide en las mismas unidades que la variable.

Esta medida nos permite determinar el promedio aritmético de fluctuación de los datos respecto a su punto central o media. La desviación estándar nos da como resultado un valor numérico que representa el promedio de diferencia que hay entre los datos y la media. Para calcular la desviación estándar basta con hallar la raíz cuadrada de la varianza.

Su aplicación es muy importante para la administración de información para los informes matemáticos, estadísticos.

PRACTICA PROPUESTA: El profesor del curso deberá de explicar a sus alumnos la aplicación práctica de los conceptos acerca de DESVIACIÓN ESTÁNDAR teniendo como referencia la tabla anterior de la Practica Propuesta 1

Utilizar las funciones BD: BDDESVEST y BDDESVETP, crear los respectivos informes elaborando sus tablas de doble entrada o tablas simples. Si cree necesario, también añadir a su explicación y demostración de cálculos referente a Desviación Estándar los temas de Amplitud, Varianza, Frecuencia absolutas (fi), relativas (hi), acumuladas (Fi), acumuladas relativas (Hi) con sus respectivas tablas y acciones de cálculo.


Pág. N° 18


INFO UNI

PRÁCTICA PROPUESTA N° 1 - FÓRMULAS MATRICIALES 1.- Digitar la siguiente tabla referente Informe de Ventas desde el año 2000 hasta 2013 con un total de 250 REGISTROS o fila de datos. Seleccionar toda la tabla y asignarle el nombre REGISTROS.

2.- En la siguiente imagen se podrá visualizar los últimos registros de la tabla

Consideraciones para realizar distintas operaciones de cálculo utilizando formulas matriciales. a) Cuantos Vendedores de Estado Civil Soltero b) Cuantos Vendedores de género Femenino c) Contabilizar las Ventas del vendedor Rubio Ramírez Verónica d) Cuantos Vendedores de Estado Civil Casado y de género Femenino e) Cuantos Vendedores de Estado Civil Casado y de género Masculino f)

Cuantos Vendedores de Estado Civil Casado y de género Femenino, el cargo Plataforma, con la finalidad de obtener la sumatoria de Ventas

g) Calcular el acumulado de Ventas de los Vendedores de género Femenino h) Calcular el acumulado de Ventas de los Vendedores de género Masculino i)

Calcular el acumulado de Ventas y Bonificación de los Vendedores de género Femenino

j)

Calcular el acumulado de Ventas y Bonificación de los Vendedores de género Femenino, que tengan el cargo Online, estado civil Soltero

k) Hallar el promedio de las Ventas de los Vendedores de género Masculino l)

Hallar la mínima edad de los vendedores que tengan el cargo de Promotor


Pág. N° 19


INFO UNI

PRÁCTICA PROPUESTA N° 2 - FÓRMULAS MATRICIALES De la siguiente tabla que contiene 375 registros

Continuación de la tabla.

Preguntas para la Practica utilizando la herramienta Formulas matriciales. 1.- Calcular los Clientes según tipo de AFP

2.- Calcular los Clientes para cada Zona


Pág. N° 20


INFO UNI

3.-Calcular los Clientes para cada Semana

4.- Calcular los Clientes de AFP Integra en la zona A

5.- Calcular los Clientes de AFP BBVA Horizonte del 19 al 25 de Feb en la zona C

6.- Calcular los Clientes de la AFP HABITAT del mes de Febrero en la zona B

7.- Calcular los Clientes de AFPs PRIMA e INTEGRA en el mes de Febrero en la zona A

8.- Calcular la máxima captación de Afiliados entre las AFPs BBVA Horizonte, Profuturo y Prima en el mes de Enero 2013 9.- Calcular el Promedio de Captaciones en las zonas B o C de la AFP Profuturo

10) Calcular la menor captación realizadas por las AFP Profuturo y Horizonte en la zona A

11.- Contar la cantidad de días en que se captaron Clientes por las AFP’s BBVA Horizonte e Integra en el mes de Febrero.

12.- Calcular la menor captación realizadas por las AFP Profuturo y BBVA Horizonte en la zona A

SUGERENCIA.- Plantear algunas respuestas matriciales aplicadas hasta la pregunta 12 utilizando otras funciones, para comprobar los cálculos anteriores.


Pág. N° 21


INFO UNI

Sesión 2 1. INFORMES DE TABLAS DINAMICAS      

Operaciones más comunes(SUMA,MAX,MIN,PROMEDIO,CUENTA) Filtros / porcentajes Ordenar Agrupara /Desagrupar Campo calculado Elemento calculado

2. INFORMES DE GRÁFICOS DINÁMICOS  Pasos para crear Gráfico  Pasos para optimizar el gráfico: filtrar, asignar cantidades, cambiar de tipo de gráfico, etc.


Pág. N° 22


INFO UNI

TABLAS DINÁMICAS Una tabla dinámica consiste en la administración de un conjunto de datos, para generar un resumen, 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. En conclusión una tabla dinámica combina y compara en forma rápida grandes volúmenes de datos. Permitiendo el análisis multidimensional de los datos al girar las filas y las columnas creando diferentes formas de visualizar reportes con los datos de origen. Escogiendo desde lo general a lo específico. Las tablas dinámicas solo sirven para resumir los datos según la consulta realizada, pero no permiten modelar dentro de la tabla. La opción posible sería tomar los datos de la Tabla Dinámica Excel con la función IMPORTAR DATOS DINAMICOS e incorporarlos a nuestro modelo Excel. Las tablas dinámicas tienen alguna semejanza cuando realizamos consulta de referencias cruzadas, pero con más interactividad. Las tablas dinámicas, también llamadas pivot tables, son herramientas para el análisis de bases de datos (BD). Se encargan de resumir y ordenar la información contenida en la BD. Permiten analizar sólo una porción de la BD, es decir, con una BD con gran cantidad de campos o columnas, ayudan a visualizar únicamente la información relevante, con lo que el análisis se torna más sencillo. Las pivot tables están basadas en dos conceptos: sumarización y rotación. Sumarización: Se refiere a la capacidad de resumir datos del mismo tipo y con los mismos atributos. Rotación: Es la posibilidad de colocar, quitar y/o mover la cantidad de campos seleccionados a cualquier posición, enfoque, situación que se requiera, siempre y cuando estos campos estén contenidos en la base de datos.

Origen de Datos de la Tabla Dinámica Para crear una Tabla Dinámica es necesario contar con información organizada en una hoja de Excel. Esta información debe estar clasificada. Por ejemplo, imagina que tenemos una serie de datos de clientes junto con su dirección. Así, pudiéramos tener los datos ordenados y clasificados en la siguiente manera: Código de Cliente. Nombre de Cliente. Distrito Código Postal Cargo Sueldo …y etc. (mas campos)

Teniendo clasificada la información de esta forma, se pudieran hacer consultas para localizar cierto cliente o cierta dirección, ya sea usando la función de Búsqueda o haciendo uso de Filtros.


Pág. N° 23


INFO UNI

Sin embargo, para contabilizar los datos de manera rápida y clasificada, se requiere el uso de la Tabla Dinámica. Por ejemplo: Cuántos y cuáles son los clientes que existen por Códigos Postales. Cuántos y cuáles son los clientes que existen por Cargo. Cuántos y cuáles son los clientes que existen por Distrito. … y así por el estilo. O combinaciones de estos (Creación de matriz de datos): No importa la cantidad de información que se desee analizar, Excel tiene la capacidad de hacer un informe de datos inclusive si los datos ocupan toda la capacidad de espacio de una hoja de Excel. Su única limitante, será la capacidad del procesador y memoria de nuestra computadora.

FILTRO DINÁMICO - INFORMES DE TABLA DINÁMICA Un informe de tabla dinámica es una forma interactiva de resumir rápidamente grandes volúmenes de datos. Utilice un informe de tabla dinámica para analizar datos numéricos en profundidad y para realizar diferentes tipos de proyecciones sobre los datos. Un informe de tabla dinámica está especialmente diseñado para:      

Consultar grandes cantidades de datos de muchas maneras diferentes y cómodas para el usuario. Calcular el subtotal y agregar datos numéricos, resumir datos por categorías y subcategorías, y crear cálculos y fórmulas personalizados. Expandir y contraer niveles de datos para destacar los resultados y desplazarse hacia abajo para ver los detalles de los datos de resumen de las áreas de interés. Desplazar filas a columnas y columnas a filas para visualizar los resúmenes diferentes de los datos de origen. Filtrar, ordenar, agrupar y dar formato condicional a los subconjuntos de datos más útiles e interesantes para poder centrarse en la información que le interesa. Presentar informes electrónicos o impresos concisos, atractivos y con comentarios.

A menudo utilizamos un informe de tabla dinámica cuando deseamos comparar totales relacionados, sobre todo si tenemos una lista larga de números para sumar y deseamos realizar comparaciones distintas con cada número. En el informe de tabla dinámica mostrado a continuación, podemos ver fácilmente cómo se comparan la cantidad de vehículos de la semana 2 con las cantidades de cualquier otro vehículo o semana, o con las cantidades totales. 1 2

3 4

Datos de origen, en este caso de una hoja de calculo Los valores de origen del resumen de Cantidad por vehiculo en el informe de tabla dinámica El informe de tabla dinámica entero Resumen de los valores de origen


Pág. N° 24


INFO UNI

En los informes de tabla dinámica, cada columna o campo de los datos de origen se convierte en un campo de tabla dinámica que resume varias filas de información. En el ejemplo anterior, la columna Semana se convierte en el campo Semana y cada registro de Auto se resume en un solo elemento Auto. Un campo de valores, como Suma de Cantidad, proporciona los valores que van a resumirse. De forma predeterminada, los datos del área Valores resumen los datos de origen subyacentes en el informe de gráfico dinámico de la siguiente forma: los valores numéricos utilizan la función SUMA, y los valores de texto la función CONTAR.

CREAR UN INFORME DE TABLA DINÁMICA Para crear un informe de tabla o gráfico dinámico, necesita conectar con un origen de datos y especificar la ubicación del informe. 1. Seleccione una celda de un rango de celdas o coloque el punto de inserción dentro de una tabla de Microsoft Office Excel. Asegúrese de que el rango de celdas tiene encabezados de columna. 2. Siga uno de los procedimientos siguientes:  

Para crear un informe de tabla dinámica, en la ficha Insertar, en el grupo Tablas, haga clic en Tabla dinámica y, a continuación, en Tabla dinámica. Aparecerá el cuadro de diálogo Crear tabla dinámica. Para crear un informe de tabla dinámica o gráfico dinámico, en la ficha Insertar, en el grupo Tablas, haga clic en Tabla dinámica y, a continuación, en Gráfico dinámico.

Aparecerá el cuadro de diálogo Crear tabla dinámica

3. Seleccione un origen de datos. Siga uno de los procedimientos siguientes: Seleccione la tabla que desea analizar a. Selección de Mouse (clic) en Seleccione una tabla o rango. b. Escriba el rango de celdas o la referencia del nombre de tabla, en el cuadro Tabla o rango.


Pág. N° 25


INFO UNI

Si seleccionó una celda de un rango de celdas o si el punto de inserción estaba en una tabla antes de iniciar el asistente, el rango de celdas o la referencia del nombre de tabla se muestra en el cuadro Tabla o rango. Opcionalmente, para seleccionar un rango de celdas o una tabla, haga clic en Contraer cuadro de diálogo

para ocultar temporalmente el cuadro de diálogo, seleccione el rango en la hoja

de cálculo y, a continuación, presione Expandir diálogo

.

NOTA.- Si el rango se encuentra en otra hoja de cálculo (área de trabajo) del mismo libro o de otro libro, escriba el nombre del libro y de la hoja de cálculo utilizando la siguiente sintaxis: ([nombredellibro]nombredelahoja!rango).

CREAR Y CAMBIAR EL DISEÑO DE LOS CAMPOS EN UN INFORME DE TABLA DINÁMICA O GRÁFICO DINÁMICO Después de crear un informe de tabla dinámica o gráfico dinámico, utilice la lista de campos de tabla dinámica para agregar campos. Si desea cambiar un informe de tabla dinámica o gráfico dinámico, utilice la lista de campos para ordenar y quitar campos. De manera predeterminada, la lista de campos de tabla dinámica contiene dos secciones: una sección de campos en la parte superior para agregar o quitar campos, y una sección de diseño en la parte inferior para volver a organizar campos y ajustar su posición. Puede acoplar la lista de campos de tabla dinámica en algún lado de la ventana y cambiarle el tamaño horizontalmente. También puede desacoplarla, en cuyo caso podrá cambiarle el tamaño tanto vertical como horizontalmente. NOTAS:  Si no aparece la lista de campos de tabla dinámica, asegúrese de hacer clic en el informe de tabla dinámica o gráfico dinámico. Si aun así no aparece, en un informe de tabla dinámica, en la ficha Opciones, en el grupo Mostrar u ocultar, haga clic en Lista de campos, y en un informe de gráfico dinámico, en la ficha Analizar, en el grupo Datos, haga clic en Lista de campos. 

Si no aparecen los campos que desea utilizar en la lista de campos, actualice el informe de tabla dinámica o gráfico dinámico para que se muestren los nuevos campos, campos calculados, medidas, medidas calculados o dimensiones que haya agregado desde la última operación.


Pág. N° 26


INFO UNI

Lista de campos de tabla dinámica Es importante comprender el modo en que funciona la lista de campos de tabla dinámica y el modo en que puede organizar diferentes tipos de campos para obtener los resultados deseados al diseñar un informe de tabla dinámica o gráfico dinámico.

Cómo funciona la lista de campos de tabla dinámica

1 2 3 4

5

Un origen de datos externo contiene datos estructurados organizados como uno o varios campos (denominados también columnas) que se muestran en la lista de campos. Mueva un campo al área de filtro de informe en la lista de campos, lo que a su vez moverá el campo al área de filtro de informe en el informe de tabla dinámica. Mueva un campo al área de etiqueta de columna en la lista de campos, lo que a su vez moverá el campo al área de etiqueta de columna en el informe de tabla dinámica. Mueva un campo al área de etiqueta de fila en la lista de campos, lo que a su vez moverá el campo al área de fila de columna en el informe de tabla dinámica. Mueva un campo al área de valores en la lista de campos, lo que a su vez moverá el campo al área de valores en el informe de tabla dinámica.


Pág. N° 27


INFO UNI

AGREGAR CAMPOS Para agregar campos al informe, siga uno o varios de estos procedimientos: o

o

Active la casilla de verificación situada junto a cada nombre de campo en la sección de campos. El campo se coloca en el área predeterminada de la sección de diseño, pero puede organizar los campos si lo desea. De forma predeterminada, los campos no numéricos se agregan al área de etiquetas de fila, los campos numéricos se agregan al área de valores y las jerarquías OLAP de fecha y hora se agregan al área de etiquetas de columna. Selección de mouse (clic) con el botón secundario del mouse (ratón) y seleccione el comando correspondiente, Agregar a filtro de informe, Agregar a etiqueta de columna, Agregar a etiqueta de fila y Agregar a valores, para colocar el campo en un área específica de la sección de diseño. SUGERENCIA También puede hacer clic y mantener presionado el botón del mouse en un nombre de campo y después arrastrar el campo entre la sección de campos y un área de la sección de diseño. Para agregar un campo varias veces, repita la operación.


Pág. N° 28


INFO UNI

ORGANIZAR LOS CAMPOS Puede cambiar el orden o la posición de los campos existentes utilizando alguna de las cuatro áreas situadas en la parte inferior de la sección de diseño: Informe de tabla dinámica

Descripción

Valores

Se utilizan para mostrar datos numéricos de resumen.

Etiquetas de fila

Se utilizan para mostrar campos como filas en el lado del informe. Una fila en una posición inferior se anida con otra fila que está justo encima de ella.

Etiquetas de columna

Se utilizan para mostrar campos como columnas en la parte superior del informe. Una columna en una posición inferior se anida con otra columna que está justo encima de ella.

Filtro de informe

Se utiliza para filtrar todo el informe en función del elemento seleccionado en el filtro de informe.

Gráfico dinámico

Descripción

Valores

Se utilizan para mostrar datos numéricos de resumen.

Campo de eje (categorías)

Se utiliza para mostrar los campos como un eje en el gráfico.

Etiquetas de campos de leyenda (series)

Se utilizan para mostrar los campos en la leyenda del gráfico.

Filtro de informe

Se utiliza para filtrar todo el informe en función del elemento seleccionado en el filtro de informe.

Funciones de resumen disponibles en informes de tabla dinámica Las funciones de resumen se utilizan en subtotales automáticos y tablas dinámicas (informe de tabla dinámica: informe de Excel interactivo de tablas cruzadas que resume y analiza datos, como registros de una base de datos, de varios orígenes, incluidos los que son externos a Excel.). En las tablas dinámicas, están disponibles las siguientes funciones de resumen para todos los tipos de datos de origen salvo para OLAP (Procesamiento analítico en línea, Online Analytical Processing) (OLAP: tecnología de base de datos que se ha optimizado para la elaboración de consultas e informes, en lugar de procesar transacciones. Los datos OLAP se organizan jerárquicamente y se almacenan en cubos en lugar de tablas). Función

Resumen

Suma

La suma de los valores. Esta es la función predeterminada para datos numéricos.

Contar

El número de los valores de datos. La función de resumen Contar funciona del mismo modo que la función de la hoja de cálculo CONTARA. Contar es la función predeterminada para datos no numéricos.

Promedio Máx

El promedio de los valores. El valor máximo.


Pág. N° 29


Función Mín

INFO UNI

Resumen El valor mínimo.

Producto

El producto de los valores.

Contar números

El número de valores de datos que son números. La función de resumen Contar núm funciona del mismo modo que la función de la hoja de cálculo CONTAR.

DesvEst

Un cálculo de la desviación estándar de una población, donde la muestra es un subconjunto de toda la población.

DesvEstP

Un cálculo de la desviación estándar de una población, donde la población son todos los datos que van a resumirse.

Var

Un cálculo de la varianza de una población, donde la muestra es un subconjunto de toda la población.

Varp

La varianza de una población, donde la población son todos los datos que van a resumirse.

CREAR, EDITAR O ELIMINAR UNA FÓRMULA DE TABLA DINÁMICA O DE GRÁFICO DINÁMICO Si las funciones de resumen y los cálculos personalizados no proporcionan los resultados que desea, puede crear sus propias fórmulas en campos calculados y elementos calculados. NOTAS.- No puede crear fórmulas en un informe de tabla dinámica o de gráfico dinámico conectado a una fuente de datos OLAP. Para obtener resultados óptimos en un informe de gráfico dinámico, trabaje en el informe de tabla dinámica asociado, donde podrá ver los valores de datos individuales que calcula la fórmula.

CREAR UNA FÓRMULA 1. Determine si desea un campo calculado o un elemento calculado dentro de un campo.  Utilice un campo calculado si piensa utilizar los datos de otro campo en la fórmula.  Utilice un elemento calculado si desea que la fórmula utilice datos de uno o más elementos específicos dentro de un campo. 2. Realice uno de los procedimientos siguientes: Agregar un campo calculado a. Haga clic en el informe de tabla dinámica. b. En la ficha ANÁLISIS, en el grupo CÁLCULOS / Campos, elementos y conjuntos / Campo calculado. c. En el cuadro Nombre, escriba un nombre para el campo. d. En el cuadro Fórmula, escriba la fórmula para el campo.


Pág. N° 30


INFO UNI

Para utilizar los datos de otro campo en la fórmula, selección de mouse (clic) en el campo en el cuadro Campos y, a continuación, haga clic en Insertar campo. Por ejemplo, para calcular una proyección para el mes de Octubre del 15% para cada valor del campo Cantidad, podría escribir =Cantidad * 1.15. /// Selección de Mouse (clic) en Aceptar.

AGREGAR UN ELEMENTO CALCULADO A UN CAMPO a. Si los elementos del campo están agrupados, en la ficha ANÁLISIS, en el grupo AGRUPAR, haga clic en Desagrupar. b. Haga clic en el campo donde desee agregar el elemento calculado. c. En la ficha ANÁLISIS, en el grupo CÁLCULOS / Campos, Elementos y conjuntos Elemento calculado. d. En el cuadro Nombre, escriba un nombre para el elemento calculado. e. En el cuadro Fórmula, escriba la fórmula para el elemento.

Para utilizar los datos de un elemento de la fórmula, haga clic en el elemento en la lista Elementos y, a continuación, en Insertar elemento (el elemento debe ser del mismo campo que el elemento calculado). f. Selección de mouse (clic) en Aceptar. g. Si ha desagrupado elementos en el paso a, vuelva a agruparlos si lo desea. 3. Para los elementos calculados, puede especificar diferentes fórmulas, celda por celda. Siga este procedimiento: a. Haga clic en la celda donde desee cambiar la fórmula. Para cambiar la fórmula en varias celdas, mantenga presionada la tecla CTRL y haga clic en las celdas. b. En la barra de fórmulas, escriba los cambios en la fórmula.


Pág. N° 31


INFO UNI

4. Si tiene varios elementos o fórmulas calculados, ajuste el orden de cálculo haciendo lo siguiente: a. Haga clic en el informe de tabla dinámica. b. En la ficha ANÁLISIS, en el grupo CÁLCULOS haga clic en Campos, Elementos y conjuntos, a continuación, haga clic en Orden de resolución.

c. Selección de mouse (clic) en una fórmula y, posteriormente, haga clic en Subir o en Bajar. d. Continúe hasta que las fórmulas aparezcan en el orden en que desee que se calculen.

MOSTRAR UNA LISTA DE FÓRMULAS Para mostrar una lista de todas las fórmulas utilizadas en el informe de tabla dinámica actual, haga lo siguiente: 1. Haga clic en el informe de tabla dinámica. 2. En la ficha ANÁLISIS, en el grupo CÁLCULOS, selección de mouse (clic) en la opción CAMPOS, ELEMENTOS Y CONJUNTOS, a continuación, haga clic en CREAR LISTA DE FÓRMULAS.


Pág. N° 32


INFO UNI

EDITAR UNA FÓRMULA 1. Determine si la fórmula está en un campo calculado o en un elemento calculado. Si la fórmula está dentro de un elemento calculado, determine si se trata de la única fórmula del elemento calculado haciendo lo siguiente: a. Haga clic en el informe de tabla dinámica. b. En la ficha ANÁLISIS, en el grupo CÁLCULOS, haga clic en CAMPOS, ELEMENTOS Y CONJUNTOS y, a continuación, haga clic en Crear lista de fórmulas. c. En la lista de fórmulas, busque la fórmula que desee cambiar bajo Campo calculado o Elemento calculado. Si hay varias fórmulas para un elemento calculado, la fórmula predeterminada escrita al crear el elemento contiene el nombre de elemento calculado en la columna B. Para las demás fórmulas de un elemento calculado, la columna B contiene tanto el nombre del elemento calculado como los nombres de los elementos con los que se intersecta. 2. Siga uno de los procedimientos siguientes:

EDITAR UNA FÓRMULA DE CAMPO CALCULADO a. Selección de mouse (clic) en el informe de tabla dinámica. b. En la ficha ANÁLISIS, en el grupo CÁLCULOS, selección de mouse (clic) en CAMPOS, ELEMENTOS Y CONJUNTOS y, a continuación, haga clic en Campo calculado. c. En el cuadro Nombre, seleccione el campo calculado cuya fórmula desea cambiar. d. En el cuadro Fórmula, modifique la fórmula / Selección de mouse (clic) en Modificar.

MODIFICAR UNA ÚNICA FÓRMULA PARA UN ELEMENTO CALCULADO a. Selección de mouse (clic) en el campo que contenga el elemento calculado. b. En la ficha ANÁLISIS, en el grupo CÁLCULOS haga clic en CAMPOS, ELEMENTOS Y CONJUNTOS y, a continuación, selección de mouse (clic) en Elemento calculado. c. En el cuadro Nombre, seleccione el elemento calculado. d. En el cuadro Fórmula, modifique la fórmula / Selección de mouse (clic) en Modificar.


Pág. N° 33


INFO UNI

Editar fórmulas individuales para celdas específicas de un elemento calculado a. Selección de mouse (clic) en la celda donde desee cambiar la fórmula. Para cambiar la fórmula en varias celdas, mantenga presionada la tecla CTRL y selección de mouse (clic) en las celdas. b. En la barra de fórmulas, escriba los cambios en la fórmula. 3. Si tiene varios elementos o fórmulas calculados, ajuste el orden de cálculo haciendo lo siguiente: a. Selección de mouse (clic) en el informe de tabla dinámica. b. En la ficha ANÁLISIS, en el grupo CÁLCULOS haga clic en CAMPOS, ELEMENTOS Y CONJUNTOS y, a continuación, haga selección de mouse (clic) en Orden de resolución. c. Selección de mouse (clic) en una fórmula y, posteriormente, haga clic en Subir o en Bajar. d. Continúe hasta que las fórmulas aparezcan en el orden en que desee que se calculen.

ELIMINAR UNA FÓRMULA SUGERENCIA Si no desea eliminar una fórmula de forma permanente, puede ocultar el campo o elemento. Para ocultar un campo, arrástrelo fuera del informe. 1. Determine si la fórmula está en un campo calculado o en un elemento calculado. Los campos calculados aparecen en la Lista de campos de tabla dinámica. Los elementos calculados aparecen como elementos dentro de otros campos. 2. Siga uno de los procedimientos siguientes:

ELIMINAR UN CAMPO CALCULADO a. Selección de mouse (clic) en el informe de tabla dinámica. b. En la ficha ANÁLISIS, en el grupo CÁLCULOS, haga selección de mouse (clic) en CAMPOS, ELEMENTOS Y CONJUNTOS y, a continuación, Selección de mouse (clic) en Campo calculado. c. En el cuadro Nombre, seleccione el campo que desea eliminar. d. Selección de mouse (clic) en Eliminar.

ELIMINAR UN ELEMENTO CALCULADO a. Selección de mouse (clic) en el campo que contiene elemento que desee eliminar. b. En la ficha ANÁLISIS, en el grupo CÁLCULOS haga selección de mouse (clic) en CAMPOS, ELEMENTOS Y CONJUNTOS y, a continuación, selección de mouse (clic) en Elemento calculado. c. En el cuadro Nombre, seleccione el elemento que desea eliminar. d. Selección de mouse (clic) en Eliminar.

INFORMES DE GRÁFICOS DINÁMICOS Los informes de tabla dinámica son útiles para resumir, analizar, explorar y presentar datos de resumen. Los informes de gráfico dinámico nos ayudan a visualizar los datos de resumen de un informe de tabla dinámica para que pueda detectar fácilmente comparaciones y tendencias. Tanto los informes de tabla dinámica como los informes de gráfico dinámico permiten tomar decisiones bien fundamentadas sobre datos críticos para su empresa. Los gráficos dinámicos se caracterizan por crear un informe con grandes volúmenes de datos que se administran en varios niveles en el eje X.


Pág. N° 34


INFO UNI

Un informe de gráfico dinámico proporciona una representación gráfica de los datos de un informe de tabla dinámica, que en este caso se denomina informe de tabla dinámica asociado. Un informe de gráfico dinámico es interactivo, lo que significa que se puede ordenar y filtrar para mostrar subconjuntos de los datos de la tabla dinámica. Cuando se crea un informe de gráfico dinámico, se muestran filtros de informe de gráfico dinámico en el área del grafico para poder ordenar y filtrar los datos subyacentes del informe de gráfico dinámico. Los cambios que realice en el diseño de campo y los datos en el informe de tabla dinámica asociado se reflejarán inmediatamente en el informe de gráfico dinámico. Un informe de gráfico dinámico muestra series de datos, categorías, marcadores de datos y ejes, al igual que el grafico estándar. Puede cambiar asimismo el tipo de gráfico y otras opciones como los títulos, la ubicación de la leyenda, las etiquetas de datos y la ubicación del gráfico.

Pasos para crear un gráfico dinámico: 1.- Digitar la siguiente tabla:

2.- Seleccionar una celda (la celda B3) de la tabla conformada por el rango A2:E16. Menú INSERTAR / grupo Tablas – TABLAS DINÁMICAS / GRÁFICO DINÁMICO

Estas acciones se podrán comprobar en la siguiente imagen.


Pág. N° 35


INFO UNI

3.- Se activa la siguiente ventana

En la opción Tabla o rango, nos indica el rango que reconoce la tabla A2:E16 Además se habilita la opción Nueva hoja de cálculo. Escoger la opción Aceptar

Se activa la siguiente ventana: Conformada en Tabla, gráfico y el Panel (para administrar información con el objetivo de obtener la tabla dinámica).

Desde el área del Panel: Escoger los campos Empresa, Región (como campos de contenido literal – criterio), después escoger un campo de contenido numérico. Comprobar en la siguiente imagen.


Pág. N° 36


INFO UNI

4.- Ahora procederemos a filtrar el campo literal criterio EMPRESA, escoger los criterios Data Print, Diseños Arenas y Tools Studio.

Del campo literal criterio REGIÓN, escoger los criterios Arequipa, Chiclayo, Lima. Se podrá comprobar en la siguiente imagen.

Seleccionar cualquier área del grafico pulsar el botón derecho de mouse, escoger la opción Agregar etiqueta de Datos. Estas acciones se podrán comprobar en la siguiente imagen.


Pág. N° 37


INFO UNI

Después de filtrar los campos Empresa y Región Se obtiene el siguiente gráfico como en la siguiente imagen.

Pulsar el botón derecho de mouse en un espacio vacío y escoger la opción Cambiar tipo gráfico

Ejemplos de tipos de gráficos:


Pág. N° 38


INFO UNI

MODIFICAR LAS CARACTERÍSTICAS DEL GRÁFICO RESPECTO A SU PRESENTACIÓN En la pestaña DISEÑO podrás encontrar todas las opciones relativas al aspecto del gráfico. Por ejemplo, podrás decidir que ejes mostrar o si quieres incluir una cuadrícula de fondo para poder leer mejor los resultados. Todo esto lo encontraras en la sección Ejes:

1.- DISEÑOS DE GRÁFICO: Está conformado por 2 opciones: 1.1. Agregar elemento de gráfico: Está conformado por Ejes, Títulos de ejes, Titulo del gráfico, Etiquetas de datos, Tabla de datos, Barra de error, Líneas de la cuadricula, Leyenda, Líneas, Línea de Tendencia.

1.2 Diseño Rápido: Está conformado por 11 diseños

2.- ESTILOS DE DISEÑO: Cambia el estilo visual general del gráfico. Está conformado por 14 estilos.

3.- DATOS: Está conformado por 2 opciones 3.1. Cambiar entre filas y columnas: Intercambia los datos del eje. Los datos que se han colocado en el eje X se moverán al eje Y, viceversa. 3.2. Seleccionar datos: Cambia el rango de datos incluidos en el gráfico.

4.- TIPO: Está conformado por la opción Cambia a un tipo diferente de gráfico.

5.- UBICACIÓN: Está conformado por la opción Mover Gráfico, mueve este grafico a otra hoja o pestaña del libro.


Pág. N° 39


INFO UNI

PRÁCTICAS DIRIGIDAS NIVEL AVANZADO SESIÓN 2 Universidad Nacional de Ingeniería

Pág. N° 40


INFO UNI

PRÁCTICA 1 - TABLA DINÁMICA 1.- Digitar la siguiente tabla referente al Reporte: I Trimestre 2013 con un total de 381 REGISTROS o fila de datos. Seleccionar toda la tabla y asignarle el nombre REGISTROS.

En la siguiente imagen se podrá comprobar los últimos registros de la tabla.

2.- Consideraciones para la creación de Informes. 2.1.- Crear un Informe: En base al campo BANCO y al campo IMPORTE, comprobar en la siguiente imagen. Teniendo como referencia los clientes, aunque el informe no será necesario activar el campo.

2.2.- Crear un Informe: En base al campo TRANSACCIÓN y al campo IMPORTE, comprobar en la siguiente imagen. Teniendo como referencia los clientes, aunque el informe no será necesario activar el campo.


Pág. N° 41


INFO UNI

2.3.- Crear un Informe: En base a los campos CLIENTE, TRANSACCIÓN e IMPORTE, comprobar en la siguiente imagen.

FILTRAR LOS SIGUIENTES CLIENTES: A & B Equipos Médicos, Casa de Cambios JC, Comercial Los Reyes, Compunet S.R.L., Infomarket SRL, Metalcen SRL, Panadería El Trigal. Después crear una Tabla de doble entrada (CLIENTES como FILA y TRANSACCIÓN como COLUMNA), comprobar en la siguiente imagen.

2.4.- Crear un Informe: En base al campo CLIENTE y al campo IMPORTE, filtrar los mismos clientes (7 criterios) del informe anterior, para proyectar Ventas, para los próximos 2 meses, teniendo como referencia que estos importes son acumulados del 1er trimestre del presente año 2013 En la siguiente imagen tenemos la base de nuestro informe es decir vamos a escoger los campos CLIENTE e IMPORTE.


Pág. N° 42


INFO UNI

Utilizando campo calculado: Ficha ANALIZAR / grupos CÁLCULOS, / opción CAMPOS, ELEMENTOS Y CONJUNTOS / escoger la opción CAMPOS CALCULADO.

Proyectar: Primera Proyección 9% del Importe trimestral (mes de Abril) Proyectar: Segunda Proyección 12% del Importe trimestral (mes de Mayo)

En la siguiente imagen se podrá comprobar nuestras acciones de cálculo respecto al informe trimestral

2.5.- Crear un Informe: En base al informe anterior de proyecciones vamos a actualizar la proyección del segundo mes que era 12% ahora será el 15.3%. En la siguiente imagen se podrá visualizar el cálculo anterior es decir la Segunda Proyección es 12% del Importe trimestral

En la siguiente imagen se podrá visualizar el nuevo cálculo respecto a la Segunda Proyección será de 15.3%

En la siguiente imagen se podrá comprobar nuestras acciones de cálculo respecto al informe trimestral actualizado al 15.3% para Segunda Proyección es decir el segundo mes proyectado (Mayo 2013).


Pág. N° 43


INFO UNI

PRÁCTICA 2 - TABLA DINÁMICA – GRÁFICO DINÁMICO 1.- Crear un informe grafico en base a la selección de los campos BANCO, IMPORTE Y COMISIÓN. Teniendo en cuenta que los campos Importe y Comisión son datos numéricos y tienen una distancia entre sus cantidades será necesario crear un tipo de grafica denominada EJE SECUNDARIO, con el objetivo de compartir en nuestra grafica ambas cantidades.

En la siguiente imagen se tiene la tabla dinámica en base a los campos BANCO, IMPORTE Y COMISIÓN

En la siguiente imagen podemos comprobar las acciones necesarias para optimizar la lectura de los dos campos numéricos IMPORTE Y COMISIÓN Por ejemplo: El eje secundario del campo COMISIÓN será necesario cambiar el ancho del intervalo a 500% (queda a su criterio escoger un valor %)

En la siguiente imagen tenemos nuestro gráfico con los contenidos de los campos BANCO, IMPORTE Y COMISIÓN. Respecto al campo COMISION se está escogiendo la opción DAR FORMATO A SERIE DE DATOS para obtener nuestro informe como gráfico de doble entrada.


Pág. N° 44


INFO UNI

PRÁCTICAS PROPUESTAS NIVEL AVANZADO

SESIÓN 2 Universidad Nacional de Ingeniería

Pág. N° 45


INFO UNI

PRÁCTICA PROPUESTA 1 - TABLA DINÁMICA Con información referente a la siguiente tabla,, crear informes y gráficos.

Consideraciones: Para el desarrollo de la siguiente práctica propuesta utilizando las herramientas de tabla Dinámica. Recuerde que la tabla dinámica se debe mostrar en una nueva hoja

1) Mostrar las ventas por año y trimestre para cada uno de los productos, en cada región 2) a) Realizar una tabla que obtenga el promedio de ventas por REGIÓN para cada vendedor. Por cada o todos los años. b) Graficarlo. 3) Crear una nueva tabla dinámica en otra hoja que permita visualizar: Cantidades vendidas por producto y por región. 4) Mediante un gráfico dinámico mostrar el promedio de ventas de cada región por año.


Pág. N° 46


INFO UNI

PRÁCTICA PROPUESTA 2 - TABLA DINÁMICA 1.- Digitar la siguiente tabla referente al Reporte: I Trimestre 2013 con un total de 381 REGISTROS o fila de datos. Seleccionar toda la tabla y asignarle el nombre REGISTROS.

En la siguiente imagen se podrá comprobar los últimos registros de la tabla.

Consideraciones para crear los Informes de Tabla Dinámica y Gráfico Dinámico. 1.- Crear un Informe: Cual es el importe que se registró en cada Transacción Bancaria como ingreso por cada tipo de cuenta y además especificar en qué Banco fue este dinero depositado. 2.- En base al informe anterior asignar formato de Moneda en Soles de tipo Contabilidad. Además reemplazar los espacios vacíos correspondientes al tipo de Transacción y el Banco, rellenar estos espacios al no haber deposito por el mensaje Sin Ingresos. 3.- Crear un informe en la misma hoja (es decir en el informe actual). Escoger la opción Hoja de cálculo existente. Escoger el campo IMPORTE. Ahora deseamos que se separe los ingresos por cada BANCO, además respecto al tiempo expresado en mes por el campo FECHA MES. Ahora vamos a incluir en nuestro informe dentro de cada mes el tipo de TRANSACCIÓN. 4.- Crear un nuevo informe en base a los campos BANCO y TRANSACCIÓN, para que nos muestre un reporte porcentual de acuerdo al total acumulado por columna.


Pág. N° 47


INFO UNI

Sesión 3 FUNCIONES FINANCIERAS Descripción y aplicación de las funciones financieras en el enfoque de la perspectiva, representación empresarial

Funciones:       

VA VF PAGO PAGOINT PAGOPRIN NPER TASA


Pág. N° 48


INFO UNI

FUNCIONES FINANCIERAS El administrador financiero juega un papel importante en la empresa, sus funciones y su objetivo pueden evaluarse con respecto a los Estados financieros Básicos. Sus tres funciones primarias son:   

El análisis de datos financieros. La determinación de la estructura de activos de la empresa. La fijación de la estructura de capital.

La nueva perspectiva empresarial ya no se basa en la maximización de las utilidades esta ha cambiado por un enfoque de la maximización de la riqueza.

1. Análisis de datos financieros Esta función se refiere a la transformación de datos financieros a una forma que puedan utilizarse para controlar la posición financiera de la empresa, a hacer planes para financiamientos futuros, evaluar la necesidad para incrementar la capacidad productiva y a determinar el financiamiento adicional que se requiera. 2. Determinación de la estructura de activos de la empresa El administrador financiero debe determinar tanto la composición, como el tipo de activos que se encuentran en el Balance de la empresa. El término composición se refiere a la cantidad de dinero que comprenden los activos circulantes y fijos.

3. Determinación de la estructura de capital Esta función se ocupa del pasivo y capital en el Balance. Deben tomarse dos decisiones fundamentales acerca de la estructura de capital de la empresa. Al determinar la estructura de activos de la empresa, se da forma a la parte del activo y al fijar la estructura de capital se están construyendo las partes del pasivo y capital en el Balance. También debe cumplir funciones específicas como:  Evaluar y seleccionar clientes  Evaluación de la posición financiera de la empresa  Adquisición de financiamiento a corto plazo La importancia de la función financiera depende en gran parte del tamaño de la empresa. En empresas pequeñas la función financiera la lleva a cabo normalmente el departamento de contabilidad, a medida que la empresa crece la importancia de la función financiera da por resultado normalmente la creación de un departamento financiero separado; una unidad autónoma vinculada directamente al presidente de la compañía, a través de un administrador financiero. Contabilidad vs Finanzas Para muchos la función financiera y contable de un negocio es virtualmente la misma. Aunque hay una relación estrecha entre estas funciones, la función contable debe considerarse como un insumo necesario de la función financiera.


Pág. N° 49


INFO UNI

LA APLICACIÓN DE LAS FUNCIONES FINANCIERAS: VA (Valor Actual o Valor Presente) Devuelve el valor actual de una inversión. El valor actual es el valor que tiene actualmente la suma de una serie de pagos que se efectuarán en el futuro. Por ejemplo, cuando pide dinero prestado, la cantidad del préstamo es el valor actual para el prestamista.

Sintaxis: VA(tasa;nper;pago;vf;tipo) Tasa es la tasa de interés por período. Nper es el número total de períodos de pago en una anualidad. Pago es el pago efectuado en cada período, que no puede variar durante la anualidad. Generalmente el argumento pago incluye el capital y el interés, pero no incluye ningún otro arancel o impuesto. Si se omite el argumento pago, deberá incluirse el argumento vf. Vf es el valor futuro o un saldo en efectivo que se desea lograr después de efectuar el último pago. Si el argumento vf se omite, se supone que el valor es 0 (por ejemplo, el valor futuro de un préstamo es 0). Si se omite el argumento vf, deberá incluirse el argumento pago. Tipo es el número 0 ó 1 e indica cuándo vencen los pagos. Defina tipo como

Si los pagos vencen

0 u omitido

Al final del período

1

Al inicio del período

Observaciones:  Mantenga uniformidad en el uso de las unidades con las que especifica los argumentos tasa y tiempo nper (periodo).  Las siguientes funciones se aplican a anualidades: PAGO.INT.ENTRE

PAGOPRIN

PAGO.PRINC.ENTRE

VA

VF

TASA

VF.PLAN

TIR.NO.PER

PAGOINT

VNA.NO.PER

PAGO 

Una anualidad es una serie de pagos constantes en efectivo que se realiza durante un período continuo. Por ejemplo, un préstamo para comprar un automóvil o una hipoteca constituye una anualidad.



En las funciones de anualidades, el efectivo que paga, por ejemplo, depósitos en cuentas de ahorros, se representa con números negativos; el efectivo que recibe, por ejemplo, cheques de dividendos, se representa con números positivos. Por ejemplo, un depósito de 1,000 $ en el banco, se representaría con el argumento -1000 si usted es el depositario y con el argumento 1000 si usted es el banco.


Pág. N° 50


INFO UNI

VF (Valor Futuro) 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;va;tipo) Tasa es la tasa de interés por período. Nper es el número total de períodos de pago en una anualidad. Pago es el pago que se efectúa cada período y que no puede cambiar durante la vigencia de la anualidad. Generalmente, el argumento pago incluye el capital y el interés pero ningún otro arancel o impuesto. Si se omite el argumento pago, se deberá incluir el argumento va. Va es el valor actual o el importe total de una serie de pagos futuros. Si se omite el argumento va, se considerará 0 (cero) y se deberá incluir el argumento pago. Tipo es el número 0 ó 1 e indica cuándo vencen los pagos. Si se omite el tipo, se calculará como 0. Defina tipo como 0

Si los pagos vencen Al final del período

1 Al inicio del período Observaciones  Mantenga uniformidad en el uso de las unidades con las que especifica los argumentos tasa y nper.  En todos los argumentos el efectivo que paga, por ejemplo depósitos en cuentas de ahorros, se representa con números negativos; el efectivo que recibe, por ejemplo cheques de dividendos, se representa con números positivos.

PAGO Calcula el pago de un préstamo basándose en pagos constantes y en una tasa de interés constante. Devuelve el pago periódico de una anualidad

Sintaxis: PAGO(tasa;nper;va;vf;tipo) Tasa es el tipo de interés del préstamo. Nper es el número total de pagos del préstamo. Va es el valor actual, o la cantidad total de una serie de futuros pagos. También valor bursátil. Vf es el valor futuro o un saldo en efectivo que se desea lograr después de efectuar el último pago. Si el argumento vf se omite, se supone que el valor es 0 (es decir, el valor futuro de un préstamo es 0). Tipo es el número 0 (cero) o 1, e indica cuándo vencen los pagos. Defina tipo como 0 u omitido 1

Si los pagos vencen Al final del período Al inicio del período

Observaciones  El pago devuelto por PAGO incluye el capital y el interés, pero no incluye impuestos, pagos en reserva ni los gastos que algunas veces se asocian con los préstamos.  Mantenga uniformidad en el uso de las unidades con las que especifica los argumentos tasa y nper. Universidad Nacional de Ingeniería

Pág. N° 51


INFO UNI

PAGOINT Devuelve el pago de intereses de una inversión durante un período determinado. La función nos retribuye, 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; núm_per; va; [vf]; [tipo]) La sintaxis de la función PAGOINT tiene los siguientes argumentos: Tasa Obligatorio. La tasa de interés por período. Período Obligatorio. El período para el que se desea calcular el interés; debe estar entre 1 y el argumento núm_per. Núm_per Obligatorio. El número total de períodos de pago en una anualidad. Va Obligatorio. El valor actual o la suma total de una serie de futuros pagos. Vf Opcional. El valor futuro o un saldo en efectivo que se desea lograr después de efectuar el último pago. Si el argumento vf se omite, se supone que el valor es 0 (por ejemplo, el valor futuro de un préstamo es 0). Tipo Opcional. El número 0 ó 1; indica cuándo vencen los pagos. Si tipo se omite, se considera 0. Defina tipo como 0 u omitido 1


PAGOPRIM Devuelve el pago de capital de una inversión durante un período determinado Devuelve el pago sobre el capital de una inversión durante un período determinado basándose en pagos periódicos y constantes, y en una tasa de interés constante.

Sintaxis: PAGOPRIN(tasa; período; núm_per; va; [vf]; [tipo]) Nota La sintaxis de la función PAGOPRIN tiene los siguientes argumentos: Tasa Obligatorio. La tasa de interés por período. Período Obligatorio. El período, que debe estar entre 1 y el valor de núm_per. Núm_per Obligatorio. El número total de períodos de pago en una anualidad. Va Obligatorio. El valor actual, es decir, el valor total que tiene actualmente una serie de pagos futuros. Vf Obligatorio. El valor futuro o un saldo en efectivo que se desea lograr después de efectuar el último pago. Si el argumento vf se omite, se supone que el valor es 0 (es decir, el valor futuro de un préstamo es 0). Tipo Opcional. El número 0 ó 1 e indica cuándo vencen los pagos. Defina tipo como 0 u omitido 1



Pág. N° 52


INFO UNI

NPER Devuelve el número de períodos de una inversión 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;[vf];[tipo]) La sintaxis de la función NPER tiene los siguientes argumentos: Tasa Obligatorio. La tasa de interés por período. Pago Obligatorio. El pago efectuado en cada período; debe permanecer constante durante la vida de la anualidad. Por lo general, pago incluye el capital y el interés, pero no incluye ningún otro arancel o impuesto. Va Obligatorio. El valor actual o la suma total de una serie de futuros pagos. Vf Obligatorio. El valor futuro o un saldo en efectivo que se desea lograr después de efectuar el último pago. Si el argumento vf se omite, se supone que el valor es 0 (por ejemplo, el valor futuro de un préstamo es 0). Tipo Opcional. El número 0 ó 1 e indica cuándo vencen los pagos.Defina tipo como Si los pagos vencen Defina tipo como Si los pagos vencen 0 u omitido Al final del período 1 Al inicio del período

TASA Devuelve la tasa de interés por período de una anualidad Devuelve la tasa de interés por período de una anualidad. TASA se calcula por iteración y puede tener cero o más soluciones. Si los resultados sucesivos de TASA no convergen dentro de 0,0000001 después de 20 iteraciones, TASA devuelve el valor de error #¡NUM!

Sintaxis: TASA(núm_per; pago; va; [vf]; [tipo]; [estimar]) La sintaxis de la función TASA tiene los siguientes argumentos: Núm_per Obligatorio. El número total de períodos de pago en una anualidad. Pago Obligatorio. El pago efectuado en cada período, que no puede variar durante la vida de la anualidad. Generalmente el argumento pago incluye el capital y el interés, pero no incluye ningún otro arancel o impuesto. Si se omite el argumento pago, deberá incluirse el argumento vf. Va Obligatorio. El valor actual, es decir, el valor total que tiene actualmente una serie de pagos futuros. Vf Obligatorio. El valor futuro o un saldo en efectivo que se desea lograr después de efectuar el último pago. Si el argumento vf se omite, se supone que el valor es 0 (por ejemplo, el valor futuro de un préstamo es 0). Tipo Opcional. El número 0 ó 1 e indica cuándo vencen los pagos. Defina tipo como 0 u omitido 1



Pág. N° 53


INFO UNI

PRÁCTICAS DIRIGIDAS NIVEL AVANZADO


Pág. N° 54


INFO UNI

PRACTICA Nº 1 – FUNCIONES FINANCIERAS El objetivo de la siguiente práctica es la aplicación de FUNCIONES FINANCIERAS

1.

Se deposita S/. 500 cada mes durante 1 año, a una tasa de interés del 32% anual. ¿Cuál será el valor actual equivalente a ese dinero?

Para comprobar estas respuestas generar la línea de tiempo:

2.

Si dentro de 6 meses se va a requerir 1500 nuevos soles. ¿Cuánto se deberá depositar si el banco paga 1% mensual?

3.

El banco nos hace un préstamo de 15000 nuevos soles a una tasa de interés del 30% para pagarlo en dos años ¿ Cuanto se tendrá que pagar al final?


Pág. N° 55


INFO UNI

Para comprobar estas respuestas generar la línea de tiempo hasta los 24 meses:

4.

Se debe pagar S/.200 cada mes durante 10 meses, a una tasa de interés del 5% mensual; ¿Cuanto se tendrá que pagar al final de los 10 meses?

Para comprobar estas respuestas generar la línea de tiempo hasta los 10 meses:


Pág. N° 56


INFO UNI

PRACTICA Nº 2 - FUNCIONES FINANCIERAS El objetivo de la siguiente práctica es la aplicación de FUNCIONES FINANCIERAS

Administrar funciones Financieras, conversión de tasas y tiempo. Además por cada planteamiento el objetivo es su demostración mediante fórmulas y la creación de Tabla con la Línea de Tiempo con todos los datos del problema a resolver. DATOS PARA PLANTEAMIENTO: 1.- Se deposita S/. 240.00 cada mes durante 1 año, a una tasa de interés del 7.28 % anual ¿Hallar él VA – Capital Inicial equivalente a ese dinero?

2.- Consideraciones: A.- En la celda B3 hallar la Tasa Mensual =POTENCIA(1+tasa anual,1/Año)-1 B.- Para hallar N° Cuotas Actualizar =Periodo x N° Cuotas Actualizar C.- Utilizando la función VA

D.- En la siguiente tabla nuestro objeto es la demostración del Valor Actual realizado anteriormente que equivale a 2773.


Pág. N° 57


INFO UNI


Pág. N° 58


INFO UNI

PRACTICA PROPUESTA Nº 1 - FUNCIONES FINANCIERAS El objetivo de la siguiente práctica propuesta es la aplicación de FUNCIONES FINANCIERAS para calcular el Valor Presente o Actual en base a un importe como Valor Futuro.

1.- Se tiene el Valor Futuro de S/. 1800.00, con una Tasa de Interés mensual de 1.3% en un periodo (nper) de 6 meses, nuestro objetivo es calcular el Valor Presente.

En la siguiente imagen se representa los datos para calcular el Valor Presente.

Nota.- Explicar las respuestas utilizando las funciones financieras.

2.- Demostrar el cálculo obtenido como Valor Presente construyendo la tabla considerada como línea de tiempo financiera. Tal como se muestra en la siguiente imagen


Pág. N° 59


INFO UNI

PRACTICA PROPUESTA Nº 2 - FUNCIONES FINANCIERAS El objetivo de la siguiente práctica propuesta es la aplicación de FUNCIONES FINANCIERAS para calcular el Valor Futuro en base a un importe como Valor Presente.

1.- Se tiene el Valor Presente de S/. 7500.00, con una Tasa de Interés Anual de 21% en un periodo (n-per) de 2 años, nuestro objetivo es calcular el Valor Futuro. Previamente se tendrá que hacer la conversión de la tasa anual a mensual y la conversión del tiempo de años a meses En la siguiente imagen se representa los datos para calcular el Valor Futuro.

2.- Demostrar el cálculo obtenido como Valor Futuro construyendo la tabla considerada como línea de tiempo financiera. Tal como se muestra en la siguiente imagen


Pág. N° 60


INFO UNI

Sesión 4 1. HERRAMIENTAS DE DATOS     

Consolidar Auditoria de Formulas Buscar Objetivo Tabla de Datos Escenarios

2. SOLVER       

Instalar Solver Cargar un modelo de problema con Solver Cómo restablecer Solver Agregar una restricción en Solver Cambiar o eliminar una restricción en Solver Cuadro de diálogo Opciones de Solver Configurar el tiempo máximo de solución y las iteraciones de Solver


Pág. N° 61


INFO UNI

HERRAMIENTAS DE DATOS CONSOLIDAR DATOS Cuando se necesitan tomar decisiones los cuadros o listas que se trabajan deberán en ocasiones ser resumidos para su mejor análisis, esto es consolidar, cuando se consolidan datos de varias listas o de varias hojas de cálculo deberán ser idénticos. Sólo los datos a operar, su estructura de tabla Titulares, nombre de los campos es decir, las cantidades podrán ser diferentes. Pasos para su aplicación 1.- Digitar las sgtes. Tablas, cambiar de nombre a la Hoja1 como Costos A, a la Hoja2 como Costos B

2.- Seleccionar la Hoja3, renómbralo como Cons Costos, seleccionar la celda A3

3.- Menú Datos / Consolidar

4.- En la ventana que se activa, en la opción FUNCION, seleccionar la función de resumen para consolidar datos. En nuestro caso escoger SUMA. En la opción Referencia seleccionar el área de origen de las tablas, es decir la Hoja Costos A, rango B3:C9, después la Hoja Costos B con el rango B3:C9. Activar los rótulos Fila Superior y Columna izquierda.


Pág. N° 62


INFO UNI

Realizar las operaciones mencionadas, tal como se aprecia en la siguiente imagen.

Observe el acabado final, los meses se han consolidado uno al lado del otro, esto es porque se han usado los elementos de una lista y el programa Excel automáticamente asume que necesita una consolidación horizontal de este tipo. Cuando las tablas son pequeñas es posible realizar una consolidación horizontal.

Para el siguiente ejemplo no activar la casilla Crear vínculos con los datos de origen, entonces el programa Excel haría una consolidación vertical, es decir sumaria Enero + Febrero.


Pág. N° 63


INFO UNI

AUDITORIA DE FORMULAS Es ésta una herramienta para controlar y auditar las fórmulas en las hojas. Otra herramienta útil y poco conocida en esta barra es la ventana de inspección.

Por ejemplo si digitamos la siguiente tabla

Vamos a calcular su comisión equivalente a 2.89%

PRECEDENTES son celdas que afectan el valor de la celda inspeccionada. Muestran flechas que indiquen las celdas afectan al valor de la celda seleccionada actualmente.

DEPENDIENTES son las celdas afectadas por la celda seleccionada. Muestra flechas que indiquen las celdas afectadas por el valor de la celda seleccionada actualmente.

QUITAR TODAS LAS FLECHAS quitar las flechas trazadas por Rastrear precedentes o Rastrear dependientes.


Pág. N° 64


INFO UNI

VENTANA DE INSPECCIÓN.- Supervisa los Valores de determinadas celdas al mismo tiempo que se realizan cambios en la hoja. Los valores se muestran en una ventana separada que se mantiene visible, independientemente del área que se muestre del libro. Abrimos la ventana de inspección apretando el ICONO en la barra de AUDITORÍA DE FÓRMULAS. Ahora seleccionamos las celdas que queremos inspeccionar, por ejemplo B2, B3, hasta B7 y seleccionamos la opción "Ventana Inspección". Se podrá seleccionar individualmente.

Seleccionar la opción AGREGAR INSPECCIÓN: Se activa una ventana donde se podrá seleccionar la celda donde está la formula, por ejemplo la celda B2, escoger la opción AGREGAR.

Otro ejemplo de agregar inspección desde la celda B8 (sumatoria del rango B2 hasta B8).


Pág. N° 65


INFO UNI

BUSCAR OBJETIVO Si conoce el resultado que desea de una fórmula, pero no del valor de entrada que necesita la fórmula para obtener dicho resultado, puede utilizar la función Buscar objetivo. Por ejemplo, utilice Buscar objetivo para cambiar el tipo de interés de la celda B3 hasta que el valor del pago de B4 sea igual a 800 $. El valor de la celda B4 es el resultado de la fórmula =PAGO(B3/12,B2,B1).

Búsqueda de objetivo para determinar la tasa de interés de la celda B3 basándose en el pago de la celda B4.

1. En la ficha Datos, en el grupo Herramientas de datos, haga clic en Análisis Y si y, después, en Buscar objetivo.

Comprobar estas acciones en la siguiente imagen

2. En el cuadro Definir la celda, escriba la referencia de la celda que contenga la fórmula que desee resolver. (En el ejemplo esta es la celda B4.)

Comprobar estas acciones en la siguiente imagen

3. En el cuadro Con el valor, especifique el resultado que desee. (En el ejemplo sería 800.)

4. En el cuadro Cambiando la celda, indique la referencia de la celda que contenga el valor que desee ajustar. (En el ejemplo esta es la celda B3.) NOTA A esta celda debe hacer referencia la fórmula en la celda especificada del cuadro Definir la celda


Pág. N° 66


INFO UNI

TABLA DE DATOS Las tablas de datos forman parte de una serie de comandos a veces denominados herramientas de análisis Y si (análisis Y si: proceso de cambio de los valores de celdas para ver cómo afectan esos cambios al resultado de fórmulas de la hoja de cálculo. Una tabla de datos es un rango de celdas que muestra cómo afecta el cambio de algunos valores de las fórmulas a los resultados de las mismas. Las tablas de datos constituyen un método abreviado para calcular varias versiones en una sola operación, así como una manera de ver y comparar los resultados de todas las variaciones distintas en la hoja de cálculo. Puede crear tablas de datos de una o dos variables, dependiendo del número de variantes que desee probar. Tablas de datos de una variable Utilice una tabla de datos de una variable si desea ver de qué manera afectan distintos tipos de interés al pago mensual de una hipoteca. En el siguiente ejemplo, la celda D2 contiene la fórmula de pago, =PAGO(B3/12;B4;-B5), que hace referencia a la celda de entrada B3.

Celda de entrada

Lista de valores que se sustituyen en la celda de entrada, B3.

Tabla de datos de dos variables Una tabla de datos de dos variables puede mostrar cómo afectan los distintos tipos de interés y plazos del préstamo al pago de una hipoteca. En el siguiente ejemplo, la celda C2 contiene la fórmula de pago, =PAGO(B3/12;B4;-B5), que utiliza dos celdas variables, B3 y B4.

Celda de entrada de columna

Celda de entrada de fila

Lista de valores que se sustituyen en la celda de entrada de fila, B4.

Lista de valores que se sustituyen en la celda de entrada de columna, B3.

Cálculos de las tablas de datos Las tablas de datos se actualizan cuando se actualiza una hoja de cálculo, aunque no hayan cambiado. Para acelerar el cálculo de una hoja de cálculo que contenga una tabla de datos, pueden cambiarse las opciones de Calcular para que se actualice automáticamente la hoja de cálculo pero no las tablas de datos.


Pág. N° 67


INFO UNI

Crear una tabla de datos de una variable Debe diseñar las tablas de datos de una variable de forma que los valores de entrada aparezcan en una columna (orientación por columnas) o en una fila (orientación por filas). Las fórmulas que se utilicen en la tabla de una variable deberán hacer referencia a una celda de entrada (celda de entrada: celda en la que se sustituye cada valor de entrada de una tabla de datos. Cualquier celda de una hoja de cálculo puede ser la celda de entrada. Aunque no es necesario que la celda de entrada sea parte de la tabla de datos, las fórmulas de las tablas de datos deben hacer referencia a la celda de entrada.). 1. Escriba la lista de valores que desea sustituir en la celda de entrada debajo de una columna o en una fila. 2. Siga uno de los procedimientos siguientes:  Si la tabla de datos está orientada por columnas, escriba la fórmula en la fila situada encima del primer valor y una celda a la derecha de los valores de la columna. Escriba las fórmulas adicionales a la derecha de la primera fórmula.  Si la tabla de datos está orientada por filas, escriba la fórmula en la columna situada a la izquierda del primer valor y una celda por debajo de los valores de la fila. Escriba las fórmulas adicionales debajo de la primera fórmula. 3. Seleccione el rango de celdas que contenga las fórmulas y los valores que desee sustituir. 4. En la ficha Datos, en el grupo Herramientas de datos, haga clic en Análisis Y si y, después, en Tabla de datos. Estos pasos se podrán comprobar con la siguiente imagen

5. Siga uno de los procedimientos siguientes:  Si la tabla de datos está orientada por columnas, escriba la referencia de celda de la celda de entrada en el cuadro Celda de entrada (columna).  Si la tabla de datos está orientada por filas, escriba la referencia de celda de la celda de entrada en el cuadro Celda de entrada (fila).

Agregar una fórmula a una tabla de datos de una variable Las fórmulas que se utilicen en la tabla de datos de una variable deberán hacer referencia a la misma celda de entrada. 1. Siga uno de los procedimientos siguientes:  Si la tabla de datos está orientada por columnas, escriba la nueva fórmula en una celda en blanco situada a la derecha de una fórmula existente en la fila superior de la tabla.  Si la tabla de datos está orientada por filas, escriba la nueva fórmula en una celda en blanco situada debajo de una fórmula existente en la primera columna de la tabla.


Pág. N° 68


INFO UNI

2. Seleccione la tabla de datos, incluida la columna o la fila que contenga la nueva fórmula. 3. En la ficha Datos, en el grupo Herramientas de datos, haga clic en Análisis Y si y, después, en Tabla de datos. 4. Siga uno de los procedimientos siguientes:  Si la tabla de datos está orientada por columnas, escriba la referencia de celda de la celda de entrada en el cuadro Celda de entrada (columna).  Si la tabla de datos está orientada por filas, escriba la referencia de celda de la celda de entrada en el cuadro Celda de entrada (fila).

CREAR UNA TABLA DE DATOS DE DOS VARIABLES Las tablas de datos de dos variables solamente utilizan una fórmula con dos listas de valores de entrada. La fórmula deberá hacer referencia a dos celdas de entrada diferentes. 1. En una celda de la hoja de cálculo, escriba la fórmula que haga referencia a las dos celdas de entrada. En el ejemplo siguiente, en el que los valores iniciales de la fórmula se especifican en las celdas B3, B4 y B5, debe escribir la fórmula =PAGO(B3/12;B4;-B5) en la celda C2. 2. Escriba una lista de valores de entrada en la misma columna, debajo de la fórmula. En el ejemplo siguiente, debe escribir los diferentes tipos de interés en las celdas C3, C4 y C5. 3. Escriba la segunda lista en la misma fila, a la derecha de la fórmula. En el ejemplo siguiente, debe escribir los plazos del préstamo (en meses) en las celdas D2 y E2. 4. Seleccione el rango de celdas que contenga la fórmula y los valores de fila y de columna. En el ejemplo siguiente, debe seleccionar el rango C2:E5. 5. En la ficha Datos, en el grupo Herramientas de datos, haga clic en Análisis Y si y, después, en Tabla de datos. 6. En el cuadro Celda de entrada (fila), escriba la referencia de la celda de entrada para los valores de entrada de la fila. En el ejemplo siguiente, debe escribir la celda B4 en el cuadro Celda de entrada (fila). 7. En el cuadro Celda de entrada (columna), escriba la referencia de la celda de entrada para los valores de entrada de la columna. En el ejemplo siguiente, debe escribir B3 en el cuadro Celda de entrada (columna). 8. Selección de mouse (clic) en Aceptar.

Acelerar los cálculos en una hoja de cálculo con tablas de datos 1. Selección de mouse en Archivo de Excel y, a continuación, haga selección de mouse (clic) en la categoría Fórmulas. 2. En la sección Opciones de cálculo, bajo Cálculo de libro, haga clic en Automático excepto para tablas de datos. SUGERENCIA Como alternativa, en la ficha Fórmulas, en el grupo Cálculo, haga clic en la flecha de Opciones para el cálculo y, a continuación, haga clic en Automático excepto en las tablas de datos. NOTA Cuando se selecciona esta opción de cálculo, las tablas de datos se omiten al actualizar el resto del libro. Para actualizar manualmente las tablas de datos, seleccione la fórmula y presione F9.


Pág. N° 69


INFO UNI

ESCENARIOS Los escenarios forman parte de una serie de comandos a veces denominados herramientas de análisis Y si (análisis Y si: proceso de cambio de los valores de celdas para ver cómo afectan esos cambios al resultado de fórmulas de la hoja de cálculo. Un escenario es un conjunto de valores que Microsoft Office Excel guarda y puede sustituir automáticamente en la hoja de cálculo. Puede utilizar los escenarios para prever el resultado de un modelo de hoja de cálculo. Puede crear y guardar diferentes grupos de valores en una hoja de cálculo y, a continuación, pasar a cualquiera de estos nuevos escenarios para ver distintos resultados. Crear escenarios Por ejemplo, usted podría utilizar un escenario si quisiera preparar un presupuesto, pero no supiera con exactitud sus ingresos. En este caso, con un escenario podría definir valores diferentes para dichos ingresos y, seguidamente, pasar de un escenario a otro para realizar un Análisis Y Si.

En el ejemplo anterior, cuyo escenario podría denominarse Peor opción, establezca el valor de la celda B1 como 50 000 $ y el de la celda B2 como 13 200 $

Al segundo escenario podría denominarlo Mejor opción y cambiar los valores de B1 a 150 000 $ y de B2 a 26 000 $. Informes de resumen de escenario Para comparar varios escenarios, puede crear un informe que los resuma en la misma página. El informe puede enumerar los escenarios unos junto a otros o resumirlos en un informe de tabla dinámica

CREAR UN ESCENARIO 1. En la ficha Datos, en el grupo Herramientas de datos, selección de mouse (clic) en Análisis Y si y, después, en Administrador de escenarios.


Pág. N° 70


INFO UNI

2. Selección de mouse (clic) en Agregar.

3. En el cuadro Nombre del escenario, escriba un nombre para el escenario. 4. En el cuadro Celdas cambiantes, especifique las referencias de las celdas que desee cambiar. NOTA Para preservar los valores originales de las celdas cambiantes, cree un escenario que utilice los valores originales de las celdas antes de crear escenarios que cambien los valores. 5. En Protección, seleccione las opciones que desee. 6. Haga clic en Aceptar. 7. En el cuadro de diálogo Valores del escenario, introduzca los valores que desee para las celdas cambiantes.

8. Para crear el escenario, selección de mouse (clic) en Aceptar. 9. Si desea crear escenarios adicionales, repita los pasos 2 a 8. Cuando termine de crear los escenarios, haga clic en Aceptar y en Cerrar en el cuadro de diálogo Administrador de escenarios.

MOSTRAR UN ESCENARIO Cuando se muestra un escenario, se cambian, modifican los valores de las celdas que se guardan como parte de ese escenario. 1. En la ficha Datos, en el grupo Herramientas de datos, haga clic en Análisis Y si y, después, en Administrador de escenarios. 2. Selección de mouse (clic) en el nombre del escenario que desee mostrar. 3. Selección de mouse (clic) en Mostrar.


Pág. N° 71


INFO UNI

CREAR UN INFORME RESUMEN DE ESCENARIO 1. En la ficha Datos, en el grupo Herramientas de datos, haga clic en Análisis Y si y, después, en Administrador de escenarios. 2. Selección de mouse (clic) en Resumen.

3. Haga clic en Resumen o en Informe de tabla dinámica de escenario. 4. En el cuadro Celdas de resultado, escriba las referencias de las celdas que hacen referencia a las celdas cuyos valores cambian los escenarios. Separe las referencias múltiples mediante comas. NOTA Las celdas de resultado no se necesitan para generar un informe resumen de escenario, pero sí se necesitan en un informe de tabla dinámica.

SOLVER DEFINIR Y RESOLVER UN PROBLEMA CON SOLVER Es un paquete agregado a Excel, que sirve para optimizar modelos matemáticos, sujeto a restricciones. El resuelve problemas lineales, no lineales y enteros. Todos estos problemas se presentan especialmente en las Ciencias Administrativas y en general en todas las áreas de las ciencias de la ingeniería. En una asignatura llamada Investigación de Operaciones, se construyen modelos para el análisis y la toma de decisiones administrativas Hasta hace poco tiempo se utilizaban complejos algoritmos, entre ellos el método Simplex y el dual. Con la tecnología computacional apareció software para resolver los problemas lineales, no lineales y enteros. El más conocido es el LINDO, pero hoy tenemos la oportunidad de resolverlos muy fácilmente por EXCEL y el paquete agregado llamado SOLVER. Solver forma parte de una serie de comandos a veces denominados herramientas de análisis Y si. Con Solver, puede encontrar un valor óptimo (mínimo o máximo) para una fórmula de una celda, denominada la celda objetivo, sujeta a restricciones o limitaciones en los valores de otras celdas de fórmula en una hoja de cálculo. Solver trabaja con un grupo de celdas llamadas celdas de variables de decisión, o simplemente celdas de variables, que participan en el cómputo de fórmulas en las celdas objetivo y de restricción. Solver ajusta los valores en las celdas de variables de decisión para cumplir con los límites en las celdas de restricción y producir el resultado deseado para la celda objetivo. Utilice Solver para determinar el valor máximo o mínimo de una celda cambiando otras celdas. Por ejemplo, puede cambiar el importe del presupuesto previsto para publicidad y ver el efecto sobre el margen de beneficio.


Pág. N° 72


INFO UNI

INSTALANDO SOLVER: Antes previamente comprobar si la herramienta se ha instalado o añadido, seleccionar la Ficha o Menú DATOS, la herramienta SOLVER ocupa el espacio final hacia la derecha, se podrá comprobar en la siguiente imagen. (no está instalado la herramienta Solver)

1.- Menú ARCHIVO / OPCIONES

De la Ventana que se activa escoger la opción COMPLEMENTOS

Esta acción se podrá comprobar en la siguiente imagen.

Se activa una ventana escoger la opción IR

De la ventana que se activa, escoger la opción SOLVER / ACEPTAR


Pág. N° 73


INFO UNI

Después de realizar este proceso se activa un grupo denominado Análisis con la herramienta SOLVER, se podrá comprobar en la siguiente imagen. Esto significa que el programa ha sido añadido.

Ejemplo de una evaluación de Solver En el siguiente ejemplo, el nivel de publicidad de cada trimestre afecta al número de unidades vendidas, determinando indirectamente el importe de los ingresos por ventas, los gastos asociados y los beneficios. Solver puede modificar los presupuestos trimestrales de publicidad (celdas B5:C5), con una restricción total máxima de 20.000 $ (celda F5), hasta que el valor total de beneficios alcance el importe máximo posible. Los valores en las celdas ajustables se utilizan para calcular los beneficios de cada trimestre, por tanto los valores están relacionados con la fórmula en la celda objetivo D7, =SUMA(B7:C7).

Celdas ajustables Celdas restringidas

Celda objetivo DEFINA Y SOLUCIONE UN PROBLEMA. 1. En el cuadro Celda objetivo, escriba una referencia de celda o un nombre para la celda objetivo. La celda objetivo debe contener una fórmula.

2. Siga uno de los procedimientos siguientes:  Si desea que el valor de la celda objetivo sea el valor máximo posible, haga clic en Máx.  Si desea que el valor de la celda objetivo sea el valor mínimo posible, haga clic en Mín.  Si desea que la celda objetivo tenga un valor determinado, haga clic en Valor de y, a continuación, escriba el valor en el cuadro.


Pág. N° 74


INFO UNI

3. En el cuadro Cambiando celdas, escriba un nombre o referencia para cada celda ajustable. Separe con comas las referencias no adyacentes. Las celdas ajustables deben estar directa o indirectamente relacionadas con las celdas objetivo. Pueden especificarse 200 celdas ajustables como máximo. 4. Si desea que Solver proponga automáticamente las celdas ajustables basadas en la celda objetivo, haga clic en Autorredistribuir. 5. En el cuadro Sujeto a las restricciones, especifique todas las restricciones que desee aplicar. a. En el cuadro de diálogo Parámetros de Solver en Sujeto a las restricciones / Agregar.

b. En el cuadro Referencia de la celda, escriba la referencia de celda o el nombre del rango de celdas para los que desee restringir el valor. c. Haga clic en la relación ( <=, =, >=, int o bin ) que desee que haya entre la celda a la que se hace referencia y la restricción. Si hace clic en int, en el cuadro Restricción aparecerá entero. Si hace clic en bin, en el cuadro restricción aparecerá Binario. d. En el cuadro Restricción, escriba un número, una referencia de celda, un nombre o una fórmula. e. Siga uno de los procedimientos siguientes:  Para aceptar una restricción y agregar otra, haga clic en Agregar.  Para aceptar la restricción y volver al cuadro de diálogo Parámetros de Solver, haga clic en Aceptar. NOTAS  Solamente pueden aplicarse las relaciones ent y bin en las restricciones en celdas ajustables.  Si se activa la casilla de verificación Adoptar un modelo lineal en el cuadro de diálogo Opciones de Solver, no habrá límite en el número de restricciones. En problemas no lineales, cada celda ajustable puede tener hasta 100 restricciones, además de límites y restricciones enteras en las variables. Cambiar o eliminar una restricción a. En el cuadro de diálogo Parámetros de Solver, en Sujeto a las restricciones, haga clic en la restricción que desee cambiar o eliminar. b. Haga clic en Cambiar y realice los cambios que desee, o haga clic en Eliminar. 6. Haga clic en Resolver y siga uno de los procedimientos siguientes:  Para mantener los valores de la solución en la hoja de cálculo, haga clic en Utilizar solución de Solver en el cuadro de diálogo Resultados de Solver.  Para restaurar los datos originales, haga clic en Restaurar valores originales. NOTAS  Puede interrumpir el proceso de solución presionando ESC. Microsoft Office Excel actualiza la hoja de cálculo con los últimos valores encontrados para las celdas ajustables.  Para crear un informe basado en su solución después de que Solver encuentre una solución, puede hacer clic en un tipo de informe en el cuadro Informes y, a continuación, hacer clic en el cuadro Correcto. El informe se crea en una nueva hoja de cálculo del libro. Si Solver no encuentra una solución, la opción de crear un informe no está disponible.  Para guardar sus valores de celda ajustados como un escenario que puede mostrar más tarde, haga clic en Guardar escenario en el cuadro de diálogo Resultados de Solver y, a continuación, escriba un nombre para el escenario en el cuadro Nombre del escenario.


Pág. N° 75


INFO UNI

DESPLAZARSE POR LAS SOLUCIONES DE PRUEBA EN SOLVER 1. Después de definir un problema, haga clic en Opciones en el cuadro de diálogo Parámetros de Solver. 2. En el cuadro de diálogo Opciones de Solver, active la casilla de verificación Mostrar resultado de iteraciones para ver los valores de cada solución de prueba y, a continuación, haga clic en Aceptar. 3. En el cuadro de diálogo Parámetros de Solver, haga clic en Resolver. 4. En el cuadro de diálogo Mostrar solución de prueba, siga uno de los procedimientos siguientes:  Para detener el proceso de solución y ver el cuadro de diálogo Resultados de Solver, selección de mouse (clic) en Detener.  Para continuar el proceso de solución y ver la siguiente solución de prueba, selección de mouse (clic) en Continuar.

CAMBIAR LA FORMA EN QUE SOLVER ENCUENTRA SOLUCIONES 1. En el cuadro de diálogo Parámetros de Solver, haga clic en Opciones. 2. En el cuadro de diálogo Opciones de Solver, elija una o más de las siguientes opciones: Tiempo de solución e iteraciones a. En el cuadro Tiempo máximo, escriba el número de segundos que desea permitir como tiempo de solución. b. En el cuadro Iteraciones, escriba el número máximo de iteraciones que desea permitir. Grado de precisión En el cuadro Precisión, escriba el grado de precisión que desee. Cuanto menor sea el número, mayor será la precisión. Tolerancia de enteros En el cuadro Tolerancia, escriba el porcentaje de error que desea permitir en la solución. Grado de convergencia En el cuadro Convergencia, escriba la cantidad de cambio relativo que desea permitir en las últimas cinco iteraciones antes de que se detenga Solver con una solución. Cuanto menor sea el número, menor será el cambio relativo que se permite. NOTA

Puede hacer clic el botón Ayuda del cuadro de diálogo para obtener más información sobre otras opciones. 3. Selección de mouse (clic) en Aceptar. 4. En el cuadro de diálogo Parámetros de Solver, haga clic en Resolver o en Cerrar.

GUARDAR O CARGAR UN MODELO DE PROBLEMA 1. Haga clic en Opciones. 2. Haga clic en Guardar modelo o en Cargar modelo. 3. Cuando guarde un modelo, especifique la referencia de la primera celda de un rango vertical o de las celdas vacías en que desee colocar el modelo de problema. Cuando cargue un modelo, especifique la referencia de todo el rango de celdas que contenga el modelo de problema. SUGERENCIA Puede guardar las últimas selecciones (acciones, tareas), realizadas en el cuadro de diálogo Parámetros de Solver de una hoja de cálculo, haciendo clic en el menú Archivo y, a continuación, haciendo clic en Guardar. También puede definir más de un problema en una hoja de cálculo, haciendo clic en Guardar modelo para guardar problemas individualmente.


Pág. N° 76


INFO UNI

DESARROLLANDO CON SOLVER: Por ejemplo: Una empresa quiere maximizar sus ventas respecto al Inventario disponible de sus productos. El objetivo de la maximización, por ejemplo el inventario no puede ser menor que cero (negativo), la condición es que siempre sean mayores o iguales a cero. 1.- En base a la siguiente información, desarrollar con SOLVER.

2.- Planteamiento para la solución con Solver: En la celda H9: Calcular la suma multiplicada entre Cantidad / Pedidos x Partes Requeridas =SUMAPRODUCTO($E$5:$G$5,E9:G9) En la celda I9: Calcular el Inventario restante =C9 – H9 (Partes disponibles – Partes Utilizadas) En E19: Multiplicar la Utilidad-Unidad x Cantidad-Precios =E3 x E5 (para Utilidad / Tipo de PC) En I19: Sumar Utilidad / Tipo de PC =SUMA(E19:G19)

3.- Desarrollo con Solver: En la opción Establecer objetivo: escoger la celda $I$19 (que representa la Utilidad Total) En la opción Para: Escoger la opción Max (por el modelo del problema a desarrollar) Cambiando las celdas de variables: Escoger el rango E5:G5 (Cantidad / Pedidos) En la opción Sujeto a las restricciones: Desde la opción Agregar escoger el rango E5:G5 >= 0 y el rango I9:I17 >= 0 (de esta forma se restringe los rangos para que sean mayores de cero). Después escoger la opción Resolver: Obtenido como respuesta


Pág. N° 77


INFO UNI

CÓMO RESTABLECER SOLVER Esta opción en Solver se utiliza para anular cualquier planteamiento (respuesta previa) para generar otro nuevo planteamiento. Por ejemplo al planteamiento (solución anterior)

Después de escoger la herramienta SOLVER: Se obtiene el planteamiento anterior (solución previa), se obtiene la siguiente ventana. Usted deberá de escoger la opción Restablecer todo Se activa la siguiente ventana, escoger la opción Aceptar (para anular el planteamiento anterior)

AGREGAR UNA RESTRICCIÓN EN SOLVER Con esta opción de Solver se podrá añadir condicionantes para buscar un planteamiento para un determinado problema. Teniendo como referencia datos y el planteamiento anterior


Pág. N° 78


INFO UNI

Se activa la siguiente ventana de dialogo, conformado por 3 áreas: A.- Referencia de celda: Es para escoger un rango de celdas B.- Operadores de relación: C.- Restricción: Es para especificar un valor como cero.

CAMBIAR O ELIMINAR UNA RESTRICCIÓN EN SOLVER: Utilizaremos esta opción para anular, borrar alguna restricción realizada previamente, tal como se comprueba en la siguiente imagen.

CUADRO DE DIÁLOGO OPCIONES DE SOLVER


Pág. N° 79


INFO UNI



Pág. N° 80


INFO UNI

PRACTICA Nº 1 – FUNCIONES DE ANÁLISIS Práctica para hallar la MINIMIZACIÓN DE COSTO UNITARIO hacia los Centros de Distribución, con el objetivo de conseguir el menor costo posible para realizar este transporte.

1.- Digitar y diseñar la siguiente tabla:

2.- Consideraciones: A.- Sumar las cantidades por Plantas (por cada fila): Lima – Cercado / Lima – Chorrillos / Lima – Ate Vitarte B.- Sumar las cantidades por centros de Distribución (por cada columna): Trujillo / Arequipa / Piura / Junín / Cusco C.- Para proyectar solución con SOLVER, en la celda D13 utilizar la función SUMAPRODUCTO para el rango de Centros de Distribución.

Completar este planteamiento a la celda Objetivo con la herramienta de Análisis SOLVER, en búsqueda de minimizar los costos para distribución a otras plantas


Pág. N° 81


INFO UNI

PRACTICA Nº 2 – FUNCIONES DE ANÁLISIS El objetivo de la siguiente práctica es la aplicación de FUNCIONES DE ANÁLISIS – TABLA DE DATOS 1.- El siguiente problema está planteado como TABLA DE DOBLE ENTRADA

Digitar la siguiente tabla: Para calcular las comisiones en base a determinadas Comisiones

Consideraciones: A.- En una celda B6 haremos nuestro planteamiento =B16*C16 B.- En la celda B16 se representa una cantidad como fila C.- En la celda C16 se representa una cantidad como columna Seleccionar el rango B6:H15 para aplicar la solución con la función Tabla de Datos.

2.- Una entidad financiera desea ver como los distintos tipos de interés afectan el pago mensual de una hipoteca. Se pide diseñar una tabla de datos de una variable que nos permita realizar la evaluación de los pagos de dicho préstamo. Utilizar la Heraamienta TABLA DE DATOS

3.- En los datos que se muestran a continuación, mostrar como afectan al pago mensual de una hipoteca los distintos tipos de interés y plazos. Utilizar la Heraamienta TABLA DE DATOS


Pág. N° 82


INFO UNI


Pág. N° 83


INFO UNI

PRACTICA PROPUESTA Nº 1 – FUNCIONES DE ANÁLISIS El objetivo de la siguiente práctica es la aplicación de FUNCIÓN DE ANÁLISIS – SOLVER

Se tiene un TALLER DE FABRICACIÓN DE EMBARCACIONES: Se desea saber cuántos veleros y cuantos botes hay que fabricar para obtener la máxima utilidad, sabiendo que se dispone de una cantidad limitada de materiales para la fabricación de estas embarcaciones:  Hay disponible 800 metros de tela para velas, 1100 kg de fibra de vidrio, y 120 motores.  Un velero utiliza 4 metros de tela, 8 kg de fibra de vidrio, y cero motores.  Un bote usa 0 metros de tela, 4 kg de fibra de vidrio y un motor.

Planteamiento: 1.- Asignando cantidades: Tal como la siguiente imagen  2.- Calcular Utilidad Total: Veleros de Producción x Utilidad Unidad + Botes a motor x Utilidad Unidad 3.- Respecto a las restricciones: Veleros de Producción x Cantidad de Veleros + Botes a motor x Cantidad requerida Botes a motor.

Después del planteamiento, utilizar la herramienta de análisis SOLVER para maximizar la utilidad para fabricar embarcaciones.


Pág. N° 84


INFO UNI

PRACTICA PROPUESTA Nº 2 – FUNCIONES DE ANÁLISIS El objetivo de la siguiente práctica es el planteamiento de distintos problemas como modelo de Programación Lineal, para la posterior aplicación al entorno del programa Excel utilizando la FUNCIÓN DE ANÁLISIS – SOLVER

PROBLEMA 1.Una compañía elabora los productos A, B y C. Cada producto se procesa en tres departamentos: I, II y III. El total disponible de horas de trabajo por semana por cada departamento es de 700, 1020 y 820 horas, respectivamente. Los requisitos de tiempo (en horas por unidad) y la ganancia por cada unidad del producto son:

PROBLEMA 2.La empresa de Carpintería y Ebanistería RODRIGO SRL ensambla dos tipos de reposteros de cocina de madera: regulares y de lujo. Los reposteros regulares están pintados de blanco y los de lujo están barnizados. Tanto el pintado como el barnizado se llevan a cabo en el mismo departamento. La capacidad mensual del departamento de ensamble puede producir un máximo de 200 reposteros regulares y 180 reposteros de lujo. El barnizado de un repostero de lujo se lleva el doble de tiempo que pintar uno regular. Si el departamento de pintura / barnizado se dedicara únicamente a las unidades de lujo, terminaría 200 unidades al mes. La empresa de Carpintería y Ebanistería RODRIGO calcula que las utilidades por unidad de los reposteros regulares y de lujo son de S/.50 y S/.80 respectivamente.


Pág. N° 85


INFO UNI

Sesión 5 FORMULARIOS     

Agregar ficha Programador. Controles de formulario. Entender los controles en una hoja de trabajo. Agregar un control a una hoja de trabajo. Vincular un control a un valor de celda.


Pág. N° 86


INFO UNI

FORMULARIOS Los formularios en Excel está conformado por controles (botones) denominados Control de Formularios, cada uno de estos controles tienen como objetivo diseñar una presentación de entorno grafico denominado Aplicaciones. Los controles o botones de Formulario nos permiten realizar lectura de información para posteriormente realizar operaciones de cálculo. Es posible que Ud. Desee utilizar herramientas para automatizar tareas que normalmente tendría que hacer manualmente, incluso por más que use formulas o funciones ya que el objetivo es lograr que nuestro trabajo sea interactivo y cambiante según nuestras necesidades y requerimientos. A este tipo de trabajos se denomina interactividad en la hoja. Cuando se convierte un libro de Excel en una plantilla de formulario, se utiliza el libro como guía para crear una nueva plantilla de formulario. La estructura de tabla del libro se vuelve a crear como una tabla de diseño en la plantilla de formulario. PARA DISEÑAR LA PRESENTACIÓN DE UN FORMULARIO: 1.- Menú ARCHIVO / OPCIONES

De la Ventana que se activa escoger la opción PERSONALIZAR CINTA DE OPCIONES / escoger la opción DESARROLLADOR Esta acción se podrá comprobar en la siguiente imagen.

2.- Escoger la Ficha o Menú DESARROLLADOR

Del grupo Controles escoger la opción INSERTAR / Controles de formulario Estas acciones se podrán comprobar en la siguiente imagen.


Pág. N° 87


INFO UNI

Pasos para activar los controles de Formulario: Insertar CUADRO COMBINADO (Control de Formulario) Este control de formulario se utiliza leer un determinado rango de información. 1.- Menú PROGRAMADOR del grupo Controles escoger la opción INSERTAR / de CONTROLES DE FORMULARIO / escoger la opción CUADRO COMBINADO.

Estas acciones de activar el control (botón) cuadro combinado comprobar en la siguiente imagen.

2.- Por ejemplo escoger el control CUADRO COMBINADO

Después de definir SU TAMAÑO (ARRASTRES DE MOUSE), aplicar botón derecho de mouse sobre el botón y escoger FORMATO DE CONTROL.. Se activa la siguiente ventana:

A.- Se utiliza para seleccionar un rango (por ejemplo un listado de cargos) haciendo arrastres de mouse.


Pág. N° 88


INFO UNI

B.- La opción vincular con la celda, se utiliza para escoger cualquier celda de índice (referencia enumerativa) C.- Líneas de unión verticales, se utiliza para escoger las líneas de presentación (por ejemplo 4) Estos pasos se podrán comprobar en la siguiente imagen.

Insertar CASILLA DE VERIFICACION (Control de Formulario) Este control de formulario se utiliza para activar o desactivar un mensaje que posteriormente se podrá utilizar funciones lógicas para la asignación de un mensaje de tipo literal, numérico.

Después de escoger el botón casilla de verificación, definir su tamaño haciendo arrastres de mouse.

Asignar la configuración del control casilla de verificación, pulsando botón derecho de mouse, escoger la opción FORMATO DE CONTROL. Para digitar un texto que reemplace Casilla de verificación 1. Ejemplo Diseñador(a), tal como la siguiente imagen.


Pág. N° 89


INFO UNI

De la ventana que se activa escoger el menú CONTROL, de la opción VALOR escoger la opción SIN ACTIVAR, desde la opción VINCULAR CON LA CELDA; escoger una celda para generar la lectura.

Es importante escoger la celda de destino desde la opción vincular porque nos permite realizar operaciones de comparación utilizando función lógica.

Por ejemplo control por CARGO, para asignar un REINTEGRO al trabajador.

Cuando seleccionamos la casilla del cargo Diseñador se activa el mensaje como VERDADERO en la celda F2. Esto se podrá comprobar en la siguiente imagen.

Cuando seleccionamos la casilla del cargo Diseñador se activa el mensaje como FALSO en la celda F2. Esto acción se podrá comprobar en la siguiente imagen.


Pág. N° 90


INFO UNI

Para realizar operaciones de cálculo, digitar la siguiente información del rango B4 y C6.

Utilizando la función lógica SI para comparar el contenido del mensaje VERDADERO se le asignara el respectivo REINTEGRO por CARGO. En la celda C4: =SI(F2=VERDADERO,350," ") En la celda C5: =SI(F2=VERDADERO,300," ") En la celda C6: =SI(F2=VERDADERO,250," ") Comprobar estas acciones de operaciones de cálculo, en la siguiente imagen.

Insertar CONTROL DE NÚMERO (Control de Formulario) Este control de formulario se utiliza para escoger algunas cantidades de acuerdo a un rango previamente configurado para una posterior lectura.


Pág. N° 91


INFO UNI

Después de activar el CONTROL DE NÚMERO haciendo arrastres de mouse, para asignar un tamaño y ubicación, después pulsar el botón derecho de mouse para escoger la opción Formato de control… Esta acción se podrá comprobar en la siguiente imagen.

Por ejemplo en la siguiente imagen se escoge el rango de 1 a 30 con un incremento de 1

Para ejecutar el control de formulario CONTROL DE NÚMERO, podemos seleccionar el botón para aumentar o disminuir cantidades de 1 a 30. Esta acción se podrá comprobar en la siguiente imagen.

Insertar CUADRO DE LISTA (Control de Formulario) Este control de formulario denominado CUADRO DE LISTA se utiliza para seleccionar un rango de datos que posteriormente se podrá escoger del listado generando un índice enumerativo.


Pág. N° 92


INFO UNI

Para utilizar el control CUADRO DE LISTA, digitar el siguiente rango de B2 hasta B6.

Después de seleccionar el control CUADRO DE LISTA, pulsar el botón derecho de mouse para escoger la opción Formato de Control

Esta acción se podrá comprobar en la siguiente imagen.

Seleccionar el rango B2 hasta B6, desde la opción Rango de entrada. Vincular con la celda escoger la celda B8 para generar un índice enumerativo por cada opción que se escoja del rango de B2:B6

Después de escoger la opción de listado del rango seleccionado B2:B6, después de escoger Programador(a), en la celda B8 se activa el índice enumerativo. Esta acción se podrá comprobar en la siguiente imagen.


Pág. N° 93


INFO UNI

Insertar BOTÓN DE OPCIÓN (Control de Formulario) El control de formulario denominado BOTÓN DE OPCIÓN se utiliza para activar o desactivar una opción que posteriormente se podrá utilizar funciones lógicas para la asignación de un mensaje de tipo literal, numérico, operaciones de cálculo.

1.- Seleccionar el control BOTÓN DE OPCIÓN, digitar los textos Turno A, Turno B y Turno C, definir una posición tal como se demuestra en la siguiente imagen.

2.- En cada Botón de opción, pulsar el botón derecho de mouse, para escoger la opción Formato de control

3.- Se activa la siguiente ventana del cual se podrá escoger la celda para activar la lectura del BOTÓN DE OPCIÓN, este proceso se direcciona a una celda para cada lectura de cada botón de opción.


Pág. N° 94


INFO UNI

Insertar CUADRO DE GRUPO (Control de Formulario) El control de formulario denominado CUADRO DE GRUPO se utiliza como un contenedor, receptor de las herramientas botón de opción y casilla de verificación.

Por ejemplo respecto a la acción generada anteriormente respecto a BOTÓN DE OPCIÓN que se digito textos referente a turno A, B y C.

Al activar el control CUADRO DE GRUPO, definir su tamaño y ubicación, además se podrá digitar por ejemplo turno

Después tenemos que mover el área de CUADRO DE GRUPO hacia el área de los botones de opción conformado por los turno A, B y C.

Obteniendo como respuesta la siguiente presentación.


Pág. N° 95


INFO UNI

Insertar ETIQUETA (Control de Formulario) El control de formulario denominado ETIQUETA se utiliza para digitar información de tipo comentario, añadir información adicional a una determinada tarea utilizando los controles de formulario. Los texto que se digiten es denominado TEXTO ESTATICO.

En la siguiente imagen podemos comprobar el formato de control de una etiqueta

Insertar BARRA DE DESPLAZAMIENTO (Control de Formulario) El control de formulario denominado BARRA DE DESPLAZAMIENTO se utiliza para generar una lectura de datos numéricos previa configuración realizada de acuerdo a un rango.


Pág. N° 96


INFO UNI

Ejemplo de cómo aplicar la barra de desplazamiento: 1.- Seleccionar el control de BARRA DE DESPLAZAMIENTO, definir su tamaño y ubicación después pulsar el botón derecho de mouse para escoger la opción Formato de control…

Estas acciones asignadas el control de BARRA DE DESPLAZAMIENTO se podrán comprobar en la siguiente imagen.

2.- Por ejemplo vamos a configura las acciones que se le designara al control de BARRA DE DESPLAZAMIENTO. Valor Actual: 8 (digitar cualquier número entre el valor mínimo y máximo) Valor Mínimo: 1 (digitar el valor mínimo) Valor Máximo: 40 (digitar el valor máximo) Incremento: 1 Cambio de Página: 1 Vincular con la celda: D3 (esta celda es el enlace para registrar el índice numérico).

Para probar las acciones configuradas al control BARRA DE DESPLAZAMIENTO.


Pág. N° 97


INFO UNI



Pág. N° 98


INFO UNI

PRACTICA Nº 1 – FORMULARIOS Tema: Los Formularios en Excel contienen herramientas que nos permite el acceso a controlar datos en entono gráfico en base a controles de formulario – botones. A continuación se explica en detalle la aplicación de algunos botones de control.

En la celda E7 digitar la siguiente formula:

=SI(E4=1,H5,SI(E4=2,H6,SI(E4=3,H7,SI(E4=4,H8,SI(E4=5,H9,H10))))) Se podrá comprobar en la siguiente imagen.

Cada vez que se escoja un botón que representa un cargo se activa automáticamente en la celda E7 el sueldo que le corresponde a cada trabajador según su cargo.


Pág. N° 99


INFO UNI

EJEMPLO DE BOTÓN DE OPCIÓN POR TURNO:

Cada vez que se selecciona un Turno cambia la lectura de la celda J15. Teniendo como referencia el sueldo del trabajador que equivale a S/. 650.00 Hallar la Bonificación por turno (por horarios), tal como en la imagen: En la celda H21 calcular la Bonificación según Turno:

=SI(J15=1,H15*H19,SI(J15=2,H16*H19,H17*H19))

En la celda H23 calcular el Sueldo que incluya Bonificación:

=SI(J15=1,(H15*H19)+H19,SI(J15=2,(H16*H19)+H19,(H17*H19)+H19))


Pág. N° 100


INFO UNI

Ejemplo para controlar CARGOS de un CENTRO DE COMPUTO: Utilizando el control CASILLA DE VERIFICACIÓN.

Cada control de casilla de verificación esta anexada a la celda H5.

Por ejemplo en la celda B9 con el cargo del Supervisor, este proceso se repite para cada cargo: En B9: =SI(H5=VERDADERO,500," ") En B10: =SI(H6=VERDADERO,400," ") En B11: =SI(H7=VERDADERO,350," ") En B12: =SI(H8=VERDADERO,300," ") En B13: =SI(H9=VERDADERO,250," ")

También tenemos en la celda E9, se controla el reintegro de un empleado:

=SI(H5=VERDADERO,500,SI(H6=VERDADERO,400,SI(H7=VERDADERO,350,SI(H8 =VERDADERO,300,SI(H9=VERDADERO,250," ")))))


Pág. N° 101


INFO UNI

PRACTICA Nº 2 – FORMULARIOS 1.- Digitar las siguientes tablas, que representan las 4 mejores ventas en distintos Centros Comerciales.

2.- Digitar el nombre de cada centro Comercial, tal como se aprecia en la siguiente imagen, con el objetivo de su posterior lectura. Para determinadas ventas de cada Centro Comercial.

3.- En la celda D9 debemos de activar el Control Cuadro Combinado (en base a arrastres de mouse, definir el tamaño), desde la Ficha Formulario – Opción Insertar del área o grupo Controles.

4.- Después de definir el tamaño para el Cuadro Combinado

Al hacer clic derecho de mouse en el botón denominado Cuadro Combinado, escoger la opción Formato de

control…


Pág. N° 102

Excel 2013 – Nivel Avanzado Se activa la siguiente ventana, escoger como rango de entrada los nombres de centros Comerciales

INFO UNI

Ahora podemos probar la lectura del botón cuadro combinado. Se genera el VÍNCULO CON LA CELDA por cada lectura que se realice desde el cuadro combinado.  Se comprueba en la sgte. imagen.

5.- Según lectura de vínculo de celda, mostrar datos de cada tabla. Ubicarse en la celda D11

Después se podrá copiar la formula para la lectura de los PRODUCTOS. En la celda E11 podemos aplicar la siguiente formula para obtener la lectura de VENTAS. =SI($B$9=1,B3,SI($B$9=1,E3,H3)) 6.- Después que se tenga la tabla completa (de Productos y Ventas) procederemos a crear el gráfico.


Pág. N° 103


INFO UNI


Pág. N° 104


INFO UNI

PRACTICA PROPUESTA Nº 1 – FORMULARIOS El objetivo de la siguiente práctica es la aplicación de FORMULARIOS 1.- Digitar la siguiente información:

2.- Seleccionar los rangos de los Artefactos y Precios para asignarle un nombre respectivo.

Por ejemplo en la siguiente imagen se le selecciona el rango F4:F18 y asignarle el nombre MARCA1

Por ejemplo en la siguiente imagen se le selecciona el rango G4:G18 y asignarle el nombre PRECIO1

Nota.- De esta forma se habrá asignado 8 nombres a determinados rangos de artefactos y precios: Artefactos: MARCA1, MARCA2, MARCA3 y MARCA4 Precios: PRECIO1, PRECIO2, PRECIO3 y PRECIO4


Pág. N° 105


INFO UNI

3. En la celda C4 utilizar el control Cuadro Combinado para leer un artefacto que represente una MARCA de un TELEVISOR.

Repetir el proceso con el resto de los artefactos.

En la celda D4 utilizar la función de Búsqueda INDICE para localizar el precio del Artefacto, en la celda D4 =INDICE(PRECIO1,H4), comprobar en la siguiente imagen.

1. En la celda D10, utilizar el Control de Número, para administrar el N° DE CUOTAS (que es en el rango de 1 a 12 meses) 2. En la celda D11, utilizar el Barra de desplazamiento para administrar el INTERÉS (que este en el rango de 10% hasta 40%) 3. En la celda C13 calcular el PAGO MENSUAL, utilizando la función financiera PAGO

Al final la práctica deberá tener el siguiente contenido en el aspecto de diseño y operaciones de cálculo.


Pág. N° 106


INFO UNI

Sesión 6 MACROS              

Ficha Programador. Dos tipos de macros: Procedimientos y Funciones. Grabar macros. Ver el código de una macro. Partes de la ventana VBA Explorador de Proyectos Ventana de Propiedades Hoja de Procedimientos Modificar macros. Ejecutar macros. Asignar un botón a una macro. Guardar libros que contienen macros. Seguridad de las macros. Creación de funciones personales básicas en el Editor de Visual Basic.


Pág. N° 107


INFO UNI

MACROS Las operaciones tradicionales que se pueden realizar en Excel las podemos automatizar, de manera que se mantenga un registro que las contenga y posteriormente hacer referencia a ellas para la simplificación de tareas. Una Macro son una serie de pasos que se almacenan y se pueden activar con alguna tecla de control y una letra o palabra asignándolo a un botón. Una macro son un conjunto de instrucciones que sirven para automatizar procesos. Una macro está compuesta por una serie de comandos que puede usar para automatizar una tarea repetitiva, y se puede ejecutar cuando haya que realizar la tarea. Las macros son grupos de instrucciones que tienen un seguimiento cronológico usadas para economizar tareas; una macro no es más que un conjunto de expresiones (instrucciones) tales como "borrar archivo", "añadir registro", “administrar el formato de una Tabla”, etc., y que se almacenan por lo general en un área de grafico (rectángulo, elipse, bisel, etc.), que serán convertidos en botón. Por ejemplo, todos los días empleo frecuentemente en mis celdas los mismos pasos: Tamaño de Fuente (Tamaño de la letra), Negrita, Fuente (Tipo de letra) y Color de Fuente (Color de Letra), para no estar repitiendo estos pasos los puedo almacenar en una macro y cuando ejecute la macro los pasos antes mencionados se ejecutaran las veces que yo lo desee Una macro son un conjunto de instrucciones que sirven para automatizar procesos. Refiriéndonos a Excel, supongamos que realizamos frecuentemente la acción de seleccionar un rango para aplicarle negrita, cambio de fuente y centrado. En lugar de hacer estas acciones manualmente, se puede elaborar una macro invocarla para que ejecute los tres procesos automáticamente. Objetos, propiedades y métodos. A la hora de trabajar con macros en Excel, deben tenerse claros ciertos conceptos de lo que se llama programación orientada a objetos (OOP). Las macros de Excel. Se trata de un trabajo muy práctico con múltiples ejemplos de pequeñas macros muy útiles, (buscar datos repetidos en una tabla en base a filtros avanzados, comparación entre columnas, conversión de datos, etc.). La ejecución de la macro se puede hacer de distintas formas. Si tiene asignado una combinación de teclas de método abreviado con éstas se puede iniciar la ejecución. Se puede ejecutar una macro seleccionándola de una lista en el cuadro de diálogo Macro. Para que una macro se ejecute cada vez que haga clic en un botón determinado o presione una combinación de teclas específica, asigne la macro a un botón de la barra de herramientas, a un método abreviado de teclado o a un objeto gráfico de una hoja de cálculo. En resumen una macro consiste 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 la tarea. Para esto Excel cuenta con dos herramientas básicas: las macros y los módulos.


Pág. N° 108


INFO UNI

Para explicar, expresar, manifestar lo que es una macro, vamos a hacer una analogía, semejanza: Entre un micrófono y una grabadora

Entre un computador y la grabadora de macros

La grabadora de macros funciona de manera similar a una grabadora de cintas. Una graba lo que la persona dice, otra graba lo que el usuario hace.

Grabar Macro Antes de grabar o escribir una macro hay que tener en cuenta los siguientes puntos: 1. Planifique los pasos y los comandos que desea que ejecute la macro 2. Si se comete algún error mientras se graba la macro, también se grabarán las correcciones que se realicen. 3. Cada vez que se grabe una macro, ésta se almacenará en un nuevo módulo adjunto a un libro. Al grabar una macro, Excel almacena información sobre cada paso dado cuando se ejecuta una serie de comandos. A continuación, se ejecuta la macro para que repita los comandos. Si se comete algún error mientras se graba la macro, también se graban las correcciones que se realicen. Visual Basic almacena cada macro en un nuevo módulo adjunto a un libro.

En el programa Excel las macros se dividen en: 1.- Macros Funcionales (con asistente): Las macros funcionales: Las macros funcionales es la aplicación de tareas que se realizan mediante la grabación de una determinada acción, tarea, trabajo específico como: realizar operaciones de cálculo (utilizando formulas y funciones), aplicar búsquedas en base a filtros (lineal, avanzado), impresión, grabar archivos, etc.


Pág. N° 109


INFO UNI

Nota.- Entonces para utilizar las macros funcionales se utilizara las acciones clásicas que realizamos en Excel es decir operaciones de formato, formulas, funciones, filtros, etc. Con el entorno de la grabación de secuencias, del cual se ejecutara por medio de un objeto de botón.

2.- Macros ActiveX (codificación): Las macros ActiveX son un grupo de instrucciones programadas bajo entorno VBA (Visual Basic para aplicaciones), cuya tarea principal es la automatización de tareas repetitivas y la resolución de cálculos complejos. El lenguaje VBA, es un lenguaje de programación basado en el Visual Basic, enfocado a la realización de programas sobre las herramientas Excel, Access, mediante macros en VBA podemos crear nuevas funciones para nuestras hojas Excel, personalizar estilos y formatos, crear programas para la resolución de cálculos complejos, automatizar tareas... ¿Para qué se utilizan las macros? Mediante macros VBA podemos crear múltiples aplicaciones que realicen los cálculos automáticamente y obtengamos resultados en apenas unos segundos. Las macros VBA trabajan en diversos programas, los más conocidos son los programas que se incluyen en el paquete Office (Excel, Access...) pero además las macros VBA también trabajan en diversos programas como SolidWorks, Autocad. (software creado para el diseño de ingeniería) lo cual proporciona una enorme versatilidad de utilización, así como una comunicación entre diversos programas.

APLICACIÓN EN EXCEL: MACROS FUNCIONALES 1. Menú Inicio / Opciones / Personalizar Cinta de opciones 2. Desde la opción Comandos disponibles en: Escoger la opción Comandos más utilizados 3. Desde la opción Personalizar la cinta de opciones: Debemos de escoger la opción PESTAÑAS PRINCIPALES 4. Activar la casilla DESARROLLADOR (Todas las acciones mencionadas se podrán comprobar en la siguiente imagen)


Pág. N° 110


INFO UNI

5.- Después escoger el menú Programador / escoger Seguridad de macros

Se activara la siguiente ventana:

Escoger la opción Habilitar todas las macros (no recomendado…) / Aceptar

6.- Después debemos de grabar el archivo con el formato de macro. Escoger un destino (ejemplo Bibliotecas Documentos) / Digitar un nombre (ejemplo: Practica) En la opción Tipo: Libro de Excel habilitado para macros.


Pág. N° 111


INFO UNI

7.- Después procederemos a activar la acción de grabar Desde el Menú Vista / Escoger la opción Macros / Grabar macro…

También: Se podrá activar la acción de grabar desde la barra de estado de Excel

También: Se podrá activar la macro desde la Ficha Programador / Grabar macro

8.- Asignar un nombre a la Macro (digitar un nombre o escoger una letra par la combinación de teclas) / Aceptar

9.- Aplicar la formula correspondiente (funciones: aritméticas, estadísticas, lógicas, de base de datos, etc. /// formatos para la tabla, fuente, etc.) para automatizar tareas que se ejecutaran desde un botón. 10.- Menú Vista: Escoger la opción Macros Detener grabación


Pág. N° 112


INFO UNI

11.- Dibujar un Polígono, Forma, etc. Aplicar contextual de Mouse, se activa la siguiente ventana, escoger la opción Asignar macro

Nota: Este proceso permitirá la ejecución de tareas (formulas, formatos, etc.)

Manipulación de Macros Tras grabar una macro, se puede ver el código de macro con el Editor de Visual Basic para corregir errores o modificar lo que hace la macro. Por ejemplo, si la macro de ajuste de texto también tiene que aplicar el formato de negrita al texto, se puede grabar otra macro para aplicar el formato de negrita a una celda y, a continuación, copiar las instrucciones de esa macro a la macro de ajuste de texto. El Editor de Visual Basic es un programa diseñado para que los usuarios principiantes puedan escribir y editar fácilmente código de macro, y proporciona mucha Ayuda en pantalla. No es preciso saber cómo se programa o se utiliza el lenguaje de Visual Basic para realizar cambios sencillos en las macros. El Editor de Visual Basic permite modificar macros, copiarlas de un módulo a otro, copiarlas entre diferentes libros, cambiar el nombre de los módulos que almacenan las macros o cambiar el nombre de las macros.

EDITAR MACRO: Para abrir la macro o el módulo de Visual Basic para poder realizar ediciones del código que se generó. Si se grabó en el Libro de macros personal se tiene que utilizar el comando Mostrar del menú Ventana para aparecer el libro "Personal" que está oculto. Menú VISTA / MACROS / VER MACROS Estas acciones se podrán comprobar en la siguiente ventana

Para completar el proceso de Editar la Macro escoger una de las macros de la lista, por ejemplo Informe, escoger la opción Modificar


Pág. N° 113


INFO UNI

Se activa la siguiente ventana con la codificación de VBA realizada con la grabación de Macros en el entorno de Excel.

También podemos Eliminar la macro o el procedimiento seleccionado. Con las Opciones se muestra la siguiente caja de diálogo:

Se habilita la siguiente ventana, escoger la opción SI

Al pulsar la tecla F11 o Menú Programador / Visual Basic


Pág. N° 114


INFO UNI



Pág. N° 115


INFO UNI

PRACTICA Nº 1 – MACROS El objetivo de la siguiente práctica es la aplicación de MACROS FUNCIONALES

1.- Digitar la siguiente tabla

Consideraciones: Ppara aplicar la Macro Funcional: En base Filtro Avanzado aplicar las siguientes macros fucnionales: 

Generar una busqueda (filtraje) por los campos CLIENTE y VENDEDOR - mostrandonos los datos de la tabla



Generar una busqueda (filtraje) por los campos MES, VENDEDOR y LUGAR - mostrandonos los datos de la tabla


Pág. N° 116


INFO UNI

PRACTICA Nº 2 – MACROS El objetivo de la siguiente práctica es aplicar la función de datos de BASE DE DATOS BD y la asistencia de MACROS FUNCIONALES, crear un informe en base a los campos y criterios.

1.- Digitar la siguiente tabla: Contenido de la tabla es 50 registros o filas de datos.

2.- En base a funciones de Base de Datos crear un Informe para el campo VENDEDOR, crear macros para administrar operaciones de cálculo.


Pág. N° 117


INFO UNI


Pág. N° 118


INFO UNI

PRACTICA PROPUESTA Nº 1 – MACROS Crear una macro que permita realizar un filtro avanzado, el cual se debe asignar a una autoforma, con el texto Obtener Saldos, para ser ejecutado. El rango de criterio debe ser validado de acuerdo a los datos de la hoja DATOS 1.- En la siguiente imagen se podrá visualizar la imagen de origen.

2.- En la siguiente imagen tenemos el contenido de la hoja DATOS

3.- En la siguiente imagen tenemos el perfil de nuestro informa para generar las macros.


Pág. N° 119


INFO UNI

Sesión 7 

En esta última sesión de clases se revisara trabajos, según el avance del libro u otras prácticas que el profesor que dicte el curso estime necesario.



Examen del Curso: La evaluación correspondiente lo deberá plantear el profesor según su avance y criterio acerca de los temas planteados en el curso.


Pág. N° 120


INFO UNI

EXÁMENES FINALES NIVEL AVANZADO


Pág. N° 121


INFO UNI

EXAMEN Nº 1 TEMA: INFORMES DE TABLA DINÁMICA; Administrar el Informe de Ventas del mes de Agosto 2013 utilizando las herramientas de Tabla Dinámica. A.- Digitar la siguiente tabla: Conformado por 137 registros.

B.- Consideraciones para administrar la tabla o Base de Datos utilizando Tablas Dinámicas: 1. Crear un informe: Para obtener los Importes de los pedidos de los Vendedores por Departamento. 2. En base al Informe anterior actualizar datos referentes a un Departamento, Vendedor, Fecha de Pedido, Número de Pedido e Importe de Pedido (respecto al Vendedor escoger algunos que existan en la tabla añadir otros Vendedores que no estén en la Tabla). Comprobar este informe con la acción de Conteo. 3. Cuáles son los importes de los pedidos de los Vendedores de dos Departamentos (regiones) según su criterio. Usted deberá de escoger 2 Departamentos de los 4 Departamentos (regiones).


Pág. N° 122


INFO UNI

4. Como referencia al informe anterior, crear un informe en base a la Herramienta Insertar Segmentación de Datos. (Campos: Vendedor, Importe de Pedido, Departamento. Escoger 2 Departamentos). 5. Preparar un informe en base al Total de Ventas (pedido), Máximo, Mínimo y Conteo de Vendedores. Todo en base al campo Vendedor y Departamento (región). 6. Seleccionar los campos: Vendedor, Importe de Pedido, Fecha de Pedido. Mediante Herramienta Agrupar: Filtrar un informe del 1 al 10 de Agosto 2011 (Fecha). Comprobar el informe generado entre el rango de fechas. 7. Crear un campo para calcular un 80% como adelanto de pago en base al Importe de Pedido. Crear otro campo para hallar la diferencia respecto al Importe por Pedido, con el cálculo anterior. 8. En base al Importe de Pedido correspondiente al mes de Agosto realizar proyecciones: - Para el mes de Diciembre 14% como Ganancia en base al Importe de Pedido de Agosto. - Para el mes de Enero 7.42% como Perdida en base al Importe de Pedido de Agosto. - Para el mes de Febrero 8.25% como Ganancia en base al Importe de Pedido de Agosto. - Sumar las Proyecciones realizadas para el próximo Trimestre (Setiembre, Octubre, Noviembre) 9. Actualizar los cálculos realizados anteriormente en base a las proyecciones - Para el mes de Setiembre 17.5% como Ganancia en base al Importe de Pedido de Agosto. - Para el mes de Noviembre 5.26% como Perdida en base al Importe de Pedido de Agosto. 10. Realizar un gráfico dinámico en base a los campos numéricos y los campos criterio. Queda a su criterio el tipo, modelo de gráfico.

EXAMEN 2 Crear un Formulario: Con las siguientes características: En la Hoja2: Digitar la siguiente tabla conformada por DESTINO y COSTO

Crear un Formulario: Con las siguientes características:

En la Hoja2: Digitar la siguiente tabla conformada por DESTINO y COSTO

Asignar nombre al rango B3:B10 Asignar nombre al rango C3:C10


Pág. N° 123


INFO UNI

De acuerdo a estos nombres de rango se hará lectura del DESTINO y KILOGRAMOS En la Hoja1: Diseñar la siguiente presentación de datos.

Leer el Kilogramos (de la Hoja 2) Leer el Destino (de la Hoja 2)

LA CONDICIÓN NORMAL No tiene ningún costo de Express Comprobar en la siguiente imagen

LA CONDICIÓN EXPRESS

Calcular el 50% del COSTO Comprobar en la siguiente imagen


Pág. N° 124


INFO UNI

Para Calcular Delivery Tiene un incremento de S/. 10.00 Comprobar en la siguiente imagen

CONSIDERACIONES FINALES: El Subtotal es la sumatoria de Costo + Express + Delivery El IGV es el 18% del Subtotal El Total es la sumatoria de Subtotal + IGV


Pág. N° 125

Manual de Excel Avanzado 2013

Recommend Documents