Manual De Excel

Curso: Excel Producción y Administración

Dirigido a: Profesionales del área de Administración y Producción, que deseen utilizar métodos avanzados de Excel para el cálculo de costos, análisis estadísticos, programación de la producción, control de existencias en almacén, y manejo de resúmenes de cuadros y archivos combinados, que ayuden en la toma de decisiones.

Objetivo: Aplicar las funciones y las herramientas de análisis estadístico.

Desarrollar análisis con bases de datos externas.

Calculo de costos y diagramas de producción. Diagrama de Pareto

Resolver casos de simulación de eventos.

Resolver problemas de programación lineal con Solver

Agilizar múltiples labores en Excel creando macros.

Requisitos: Tener conocimientos previos de Excel a nivel intermedio (Excel I y II).

Duración: 24 horas.

Contenido:

1. Análisis Estadístico de datos:

a. Uso de funciones estadísticas:

i. PROMEDIO, MAX, MIN, CONTAR, CONTARA, MEDIANA, MODA, VAR, DESVEST

ii. SUMAR.SI, CONTAR.SI, SUMAR.SI.CONJUNTO, SUMAR.SI.CONJUNTO

iii. FRECUENCIA, ESTIMACION.LINEAL, TENDENCIA, K.ESIMO.MAYOR, K.ESIMO.MENOR, JERARQUIA, PRONOSTICO

b. Uso de Funciones Matriciales

c. Gráficos Estadísticos

i. De barras y líneas combinadas con escalas múltiples

ii. Histograma de Frecuencias

iii. Líneas de Tendencia (pronósticos)

2. Detección de errores en el ingreso de datos:

a. Auditoría de Formulas

b. Validación de datos

c. Formatos condicionales

d. Detección y eliminación de datos duplicados

3. Obtener información de Bases de datos Externas:

a. Manejo de múltiples ventanas en Excel

b. Formulas con referencias externas y remotas

c. Dividir textos en columnas

Curso: Excel Producción y Administración

4. Análisis Y si: (para cálculos de costos, y de producción)

a. Buscar Objetivo

b. Tabla de datos

c. Administrador de escenarios

d. Diagrama Spider

e. Gráfica del Punto de Equilibrio

5. Control de Existencia en Almacenes

a. Detección de stock mínimo

b. Consolidación de existencias en almacenes múltiples

c. Diagrama de Pareto

6. Simulación de proyectos (en producción y comercialización)

a. Uso de la función ALEATORIO para la simulación de eventos

7. Planteamiento y solución de problemas en programación lineal

a. Uso de la herramienta Solver para resolver la función objetivo

8. Uso de macros para la automatización de instrucciones

a. Uso de la grabadora de macros

b. Modificar macros en el Editor de Visual Basic

Contenido

1. Usar Funciones avanzadas para calcular valores 6

Función SUMAR.SI 6

Ejercicio Nº 1: Cálculos condicionales en bienes inmuebles 6

La Función CONTAR.SI 7

2. Fórmulas Matriciales: 8

a. Cómo crear un total basándose en varias condiciones 8

Las Funciónes K.ESIMO.MENOR, y K.ESIMO.MAYOR 9

Las Funciones REDONDEAR.MENOS, y REDONDEAR.MAS 9

La Función ALEATORIO 10

La Función HOY 10

La Función AHORA 10

Ejercicio Nº 2: Cálculos de Stock con la función Suma 11

Ejercicio Nº 3: Control de Embarques con la función BuscarV e indirecto 12

3. Validación en el Ingreso de datos 14

a. Que es la validación 14

b. Cómo validar el ingreso de datos en un rango de celdas 14

c. Realizar una auditoría en un rango validado 16

4. Formato Personalizado de Celdas 20

a. Cuando crear formatos personalizados 20

b. Como crear formatos personalizados 20

Crear un formato de número personalizado 21

Ejemplo: 22

c. Códigos de formato de número para fechas y horas 23

d. Códigos de formato de número para posiciones decimales, espacios, colores y condiciones 24

Decimales y dígitos significativos 24

Separador de millares 25

Color 25

Condiciones 25

e. Códigos de formato de número para texto y espaciado 26

Agregar caracteres 26

Incluir una sección de texto 26

Agregar espacio 26

Repetir caracteres 26

f. Eliminar un formato de número personalizado 27

g. Si las fechas introducidas en la hoja de cálculo no tienen el mismo aspecto 27

Capitulo 2 28

1. Buscar Objetivo 28

Ejercicio Nº 1: Cálculo de Utilidades en un Proyecto de Producción y Venta 28

2. Tablas para Análisis de sensibilidad: 30

a. Tabla de una variable de entrada 30

b. Tabla de dos variables de entrada 31

3. Administrar Escenarios 34

Ejercicio Nº 2: Obtención de diferentes escenarios para un mismo cuadro 36

4. Diagrama Spider para analizar porcentajes de desviación 37

Ejercicio Nº 3: Obtención de un Diagrama Spider 37

5. Calculo y Grafica del Punto de Equilibrio 41

Ejercicio Nº 4: Obtención del Punto de Equilibrio 41

Capitulo 3 47

1. Líneas de Tendencia en los Gráficos 47

a. Utilidad de las líneas de tendencia 47

b. Tipos de gráfico que admiten líneas de tendencias 47

c. Agregar una línea de tendencia a una serie de datos 48

2. Análisis de Regresión 51

a. Cálculo Grafico de Pronóstico con las líneas de tendencia 51

Ejercicio Nº 5: Calculo grafico de Ventas Futuras 51

b. Cálculo Matemático de Valores Futuros 52

c. La función ESTIMACION.LINEAL 53

d. La función PRONOSTICO 53

Ejercicio Nº 6: Calculo del pronóstico de accidentes de transito 54

Capitulo 4 58

1. La Herramienta Solver de Excel 58

a. La herramienta Solver 58

b. Optimización con restricciones. 58

c. Para habilitar la herramienta Solver 59

d. Consideraciones previas a tener en cuenta 59

e. Problemas de Programación Lineal 59

• Maximizar Ganancias: 59

Ejercicio Nº 7: Maximizar ganancias en la producción de tortas 59

• Minimizar Gastos: 63

Ejercicio Nº 8: Minimizar gastos en transporte de alumnos 63

Ejercicio Propuesto: 66

Respuesta de este ejercicio: 66

f. Problemas No Lineales 67

Ejercicio Nº 9: Construir un almacén minimizando los costos de construcción 67

Solver y Programación Lineal 69

Ejercicios adicionales: 70

Otra Solución utilizando Tablas con variable de una entrada: 74

Códigos ASCII para caracteres especiales: 78

Capitulo 1

1. Usar

...