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

...