Apuntebs De Excel Avanzado

BertusSoft

APUNTES DE EXCEL

AVANZADO

Formación a PYMES

Alberto Alarcón

04/01/2010

1

MANUAL DE EXCEL AVANZADO 3

Introducción 3

Gráficos Especiales 3

Gráficos de Línea vs. Gráficos de Dispersión XY 3

Gráficos de Dispersión XY 5

Esquemas. 9

Descripción de Esquemas 9

Creación de un Esquema 11

Funciones financieras. 14

Introducción 14

Funciones Financieras 15

NPER 15

PAGOINT 15

PAGOPRIN 16

VA 16

VNA 17

VF 17

Funciones para calcular la tasa de rendimiento 18

Introducción 18

TASA 18

TIR 19

TIRM 19

Funciones para calcular depreciaciones 20

Introducción 20

DB 20

DDB 21

DVS 21

SLN 22

SYD 22

Solver 23

Descripción 23

Optimización 23

Herramienta Solver 24

Instalación del Solver 24

Ejercicios 25

Problema N° 1 25

Problema N° 2 30

Informe de Respuestas 38

Informe de sensibilidad 40

Informe de Límites 41

Conclusiones 42

Opciones de Solver 43

2

Opciones para modelos no-lineales 45

Introducción a Estadística Aplicada a través de Excel 47

Distribuciones de Frecuencia e Histogramas 47

Finalidad de las distribuciones de frecuencias. 48

Interpretación de las distribuciones de frecuencias. 48

Formalización de las distribuciones de frecuencia 49

Distribuciones de frecuencias con la función FRECUENCIA del Excel 50

Introducción 50

Sintaxis 50

Observaciones 50

Ejemplo N° 1 51

Ejemplo N° 2: 53

Distribuciones de frecuencia e histogramas con herramientas de análisis 62

Herramientas de análisis estadístico 62

Funciones de hojas de cálculo relacionadas 63

Acceder a las herramientas de análisis de datos 63

Varianza de dos factores con varias muestras por grupo 65

Varianza de dos factores con una sola muestra por grupo 65

Correlación 65

Covarianza 66

Estadística descriptiva 66

Suavización Exponencial 66

Prueba t para varianza de dos muestras. 66

Análisis de Fourier 66

Histograma 67

Media móvil 67

Generación de números aleatorios. 67

Jerarquía y percentil 67

Regresión 67

Muestreo 68

Prueba t 68

Prueba t para dos muestras suponiendo varianzas iguales 68

Prueba t para dos muestras suponiendo varianzas desiguales 68

Prueba t para medias de dos muestras emparejadas 68

Prueba z 68

Histograma 69

Introducción 69

Descripción 69

Distribuciones de frecuencia e histogramas con tablas dinámicas. 72

Ejercicio N° 1: 73

Ejercicio N° 2: 76

Ejercicio N° 3 79

Ejercicio N° 4 93

GLOSARIO DE TERMINOS 98

3

MANUAL DE EXCEL AVANZADO

Introducción

Como su título lo sugiere estos apuntes son de técnicas avanzadas de Excel,

es decir, que no corresponden a un excel básico ni a un excel intermedio, en

general están dirigidas a la gestión. Estos apuntes se han hecho pensando en

usuarios con vasta experiencia en Excel, que ya han superado el “segundo grado”

en manejo de hoja de cálculos.

Se supone que quien estudia en estos apunte ya sabe como construir una

hoja de cálculo simple, como escribir fórmulas y que pasa cuando se copian.

Como se imprime una hoja de cálculo y como se graba. Como se imprime una

hoja de cálculo y como se graba. Saben como definir, usar e interpretar tablas

dinámicas. Como crear, definir e interpretar escenarios.

En estos apuntes se seleccionaron las técnicas que se estima necesita un

ingeniero o un ejecutivo para la gestión, es decir, estos apuntes profundizan en

todos aquellos comandos u opciones que son poco usados, no porque no sean

útiles sino porque casi nadie los conoce, pero que se estima son necesarios para

el ejecutivo moderno en la toma de decisiones o en el control.

Este manual trata las siguientes materias:

 Gráficos especiales,

 Esquemas,

 Funciones financieras,

 Solver,

 Estadísticas aplicadas a través de Excel.

Todos estos puntos son desarrollados en forma Teórica y práctica y con

ejemplos que les puedan servir a los estudiantes de Ingeniería, a los ingenieros y

a los ejecutivos en la gestión.

Gráficos Especiales

Gráficos de Línea vs. Gráficos de Dispersión XY

Una PYME fabrica solamente tres tipos de muebles: Escritorios, Sillas y Estantes.

Mediante un gran esfuerzo reinvirtiendo las utilidades y capacitando a su personal

4

ha logrado ir duplicando la producción. La producción en los últimos años se

muestra en la siguiente tabla:

PRODUCCION DE UNA PYME

AÑOS ESCRITORIOS SILLAS ESTANTES

1980 268 323 194

1990 536 646 388

1996 804 969 582

2000 1072 1292 776

Si esta tabla se grafica mediante un gráfico de Líneas el resultado se muestra en

la página siguiente:

PRODUCCION DE UNA PYME

0

200

400

600

800

1000

1200

1400

1980 1990 1996 2000

AÑOS

PRODUCTOS

ESCRITORIOS

SILLAS

ESTANTES

Como se puede observar este gráfico está con graves errores, ya que el aumento

de la producción es el mismo para todos los años indicados, sin embargo, la

diferencia entre los años no es la misma, por lo tanto debería salir una curva

exponencial. Esto se soluciona usando gráficos tipo de Dispersión XY. Basta con

cambiar el tipo de gráfico para que aparezcan las curvas correctas, como se

muestra en la figura siguiente:

5

PRODUCCION DE UNA PYME

0

200

400

600

800

1000

1200

1400

1975 1980 1985 1990 1995 2000 2005

AÑOS

PRODUCTOS

ESCRITORIOS

SILLAS

ESTANTES

Los gráficos Dispersión XY son los indicados cuando la variable del eje de las X

no representa incrementos constantes.

Gráficos de Dispersión XY

Usando los gráficos de dispersión se puede tener gráficos como el siguiente:

6

Esta roseta se llama figura de Lissajous, en honor del físico del siglo XIX que las

estudio por primera vez. Estas figuras aparecen al superponer movimientos

oscilatorios. Lissajous usaba un aparato muy complejo, con dos diapasones y

espejitos que reflejaban la luz. Ahora se pueden obtener las mismas figuras en el

computador usando gráficos de Dispersión XY.

Para construir este tipo de gráficos se usa una tabla como la figura siguiente:

Los pasos para hacer esta tabla son los siguientes:

 En la columna A se generan los números del 1 al 100,

 La columna B debe quedar libre,

 En la celda C1 se escribe la fórmula: =SENO(2*G$1*PI()*A1/10)

 En la celda D1 se escribe la fórmula: =COS(2*G$1*PI()*A1/10)

 Se extiende el rango C1:D1 hasta la fila 100

 En la celda G1 se escribe el valor 2

 En la celda G2 se escribe el valor 5

 Se grafica el rango C1:D100

Para hacer este tipo de gráficos hay unas diferencias con los gráficos normales,

por lo tanto lo detallamos paso a paso.

 Se coloca el cursor en D1 o en cualquier celda del rango anterior,

 Se toman las opciones Insertar/Gráfico, entonces aparece el Asistente

para Gráficos.

 En el primer paso se indica el tipo de gráfico Dispersión XY y el subtipo de

la segunda fila, segunda columna.

 Se da un clic en Siguiente.

7

 En el segundo paso del asistente indicamos Series en columnas.

 Se da un clic en Siguiente para pasar a la etapa de Opciones de gráfico.

 En la ficha Eje se desmarcan todas las opciones.

 En la ficha Líneas de división, también se desmarcan todas las opciones.

 En la ficha Leyenda se desmarca la opción Mostrar leyenda.

 Se da un clic en Siguiente.

 Se marca la opción Colocar gráfico en una hoja nueva.

 Se da un clic en Finalizar.

El resultado será similar al de la figura siguiente:

Este gráfico se puede optimizar un poco, por ejemplo, eliminándole el fondo gris,

esto se hace de la siguiente forma:

 Se da un clic sobre el fondo del gráfico, usando el botón derecho del

mouse.

 Del menú contextual que aparece, se toma la opción Formato de área de

trazado, aparece el cuadro que se muestra a continuación:

8

 Dentro de área se da la opción Ninguna.

 Hacemos un clic en Aceptar.

La figura queda como se muestra a continuación:

Las fórmulas de la tabla fueron escritas de forma tal que variando el contenido de

G1 y/o G2, las curvas pueden variar de inmediato, por ejemplo si coloco 5 en G1 y

en G2, aparece la curva que se muestra en la página siguiente:

9

En cambio la Figura de Lissajous, se obtiene colocando un 5,1 en G1 y un 5 en

G2, al efectuar

...