08-04-2013 - thc.ing.puc.clthc.ing.puc.cl/thc/portals/0/taller 4/tutorial de ms excel iic2100... ·...

08-04-2013

1

Estudio de Costos:Tutorial de Microsoft Excel

para Ingeniería

Profesores Hugo Mora, Ignacio CasasAbril 2013

IIC2100 Taller de Herramientas Computacionales para Ingeniería

Introducción

Objetivos del Taller


Estudio de costos para un análisis financiero-económico (gestación, puesta

en marcha, operación, expansión):

Inversiones, costos fijos, costos variables, impuestos, …

Revisión Cadena de Valor de Porter.

Proyectos TIC: análisis del TCO (“Total Cost of Ownership”).

Introducción a MS-Excel como herramienta de apoyo.

Ejercicios y Taller con MS Excel.

IntroducciónExcel

MacrosVisual Basic de Aplicaciones

08-04-2013

2

Introducción

MS Excel


Introducción a MS Excel con ejemplos.

Aplicación en Taller 4 (segundo módulo de hoy).

IntroducciónExcel


Introducción

Encuesta


Qué conocimiento tiene de Excel:

1: Se abrirlo y escribir2: Se hacer fórmulas y cálculos3: Conozco tablas dinámicas, filtros, etc.

¿Ha usado previamente el Solver de Excel?

¿Tiene experiencia previa usando el Visual Basic de Aplicaciones?

IntroducciónExcel


08-04-2013

3

Introducción

Uso de Excel


Excel es ampliamente usado en las empresas para procesamiento analítico. Es una herramienta fundamental para los Ingenieros.

Permite construir sistemas de apoyo a la toma de decisiones para aumentar la utilización de información dentro de una organización.

IntroducciónExcel


Introducción

Uso de Excel


• Las técnicas de modelación para la gestión se pueden implementar fácilmente en programas de hojas de cálculo (o planillas) de MS Excel.

• Muchos profesionales usan hojas de cálculo como MS Excel pero no explotan todo su gran potencial.

• Cuando los datos que se manejan crecen “demasiado”, se hacen inmanejables en MS Excel. Es necesario entonces pasar a las “Bases de Datos” tales como MS Access.

IntroducciónExcel


08-04-2013

4

Tutorial de Excel

Pantalla Principal


Menú Office

Celda A1

Menú Hojas de cálculo

Barra de fórmulas

Barras de herramientas

Nombre Celda

Hoja de cálculo

IntroducciónExcel


Tutorial de Excel

Una celda se identifica por la columna y la fila, por ejemplo, celda B3.

Para identificar un rango de celdas se usan dos puntos, por ejemplo, B6:D10.

Se le puede poner un nombre a unacelda o a un rango de celdas, por ejemplo, la celda B15 se llama “miCelda”y el rango B17:C19 se llama “miRango”.

Las celdas pueden contener números, texto o fórmulas. La celda B21 contiene un texto, la celda B22 contiene un número y la celda B23 contiene unafórmula.

Celdas


IntroducciónExcel


08-04-2013

5

Tutorial de Excel

En el tabulador Fórmulas está el Administrador de nombres para Nombres Definidos. Para crear uno nuevo se utiliza el botón “Asignar nombre a un rango”

A veces es más fácil e intuitivo trabajar con nombres que con rangos de celdas.

Nombre de Celdas y Rangos


IntroducciónExcel


Tutorial de Excel

Para navegar rápido por los datos utilice la combinación fin flecha o control + flecha.

Para seleccionar un rango utilice shift mientras realiza fin flecha.

Para ingresar una fórmula o números en varias celdas a la vez, primero selecciónelas y luego presione control + enter.

Para seleccionar celdas no contiguas utilice control mientras las selecciona con el mouse.

Para seleccionar una columna: control + barra espaciadora, una fila: shift + barra Espaciadora

Desplazamiento y Selección


IntroducciónExcel


08-04-2013

6

Tutorial de Excel

Para copiar una celda o un rango de celdas, primero se selecciona lo que se quiere copiar,se elige Copiar del tabulador de Inicio (o control + c), se selecciona dónde se quiere pegar y se elige Pegar del tabulador de inicio (o control + v).

Para copiar una serie de números o una fórmula en varias celdas contiguas, se puede utilizar la esquina inferior derecha de la celda seleccionada haciendo doble clicko desplazarla con el botón izquierdo apretado.

Se puede utilizar el comando Pegado Especial(flecha bajo el botón pegar) del tabulador de iniciopara pegar sólo valores, sólo formulas o sólo formatos, ´por ejemplo.También se pueden trasponer filas o columnas.

Copiar y Pegar


IntroducciónExcel


Tutorial de Excel

Para aplicar un formato, primero se selecciona a lo que se quiere aplicar el formato, luego en el tabulador de inicio se seleccionan los formatos que se desean aplicar.

También existe la posibilidad de utilizar la opción de click derecho y luego seleccionarformato de celdas, donde se pueden cambiar variadas opciones de formato para las celdas,incluyendo alineación, colores, fuentes, bordes y forma de mostrar los valores.

Aplicar Formatos


IntroducciónExcel


08-04-2013

7

Tutorial de Excel

Excel es capaz de autocompletar una serie de números, fechas y otros patrones cuando los reconoce.

Sólo se necesita escribir unos cuantos números de la serie que se desea completar, seleccionarlos y arrastrar el cuadrado negro que aparece en la parte inferior derecha de la última celda seleccionada.

Arrastrar hacia abajo

Autocompletar


IntroducciónExcel


Tutorial de Excel

Para insertar una función se debe usar el botón fx en la barra de fórmulas.

Existen una serie de funciones para:• Matemáticas y Trigonometría• Finanzas• Estadística• De búsqueda• De fecha y hora• Lógicas• Etc.

Ayuda sobre esta función entrega Información sobre el uso de una función.

Ir busca una función por nombre.

Insertar Funciones


IntroducciónExcel


08-04-2013

8

Tutorial de Excel

Si se selecciona la función Promedio se entra al siguiente submenú.

Se explica el funcionamiento de lafunción y los argumentos se puedenseleccionar directamente en la hoja de cálculo usando .

En Resultado de la fórmula se muestra el resultado de la fórmula con los datos que se hayan incluido hasta ese momento.

Insertar Funciones


IntroducciónExcel


Tutorial de Excel

• En esta hoja de cálculo se muestra el resultado de usar la función PROMEDIOsobre un conjunto de notas.

• En la barra de fórmulas aparece:

es decir, se está tomando el promediode las celdas en el rango E2:E9.

• En la celda E10 aparece sólo elvalor calculado, o sea, 79.8.

Insertar Funciones


IntroducciónExcel


08-04-2013

9

Tutorial de Excel

Algunas funciones matemáticas:

• EXP(x): Exponencial.• LN(x): Logaritmo Natural.• RAIZ(x): Raíz Cuadrada.• ALEATORIO(): Número Aleatorio.• FACT(x): Factorial.• COMBINAT(x, y): Combinatorial

(# de combinacionesde y elementos deun total de x, sin orden).

• TRASPONER(X): Trasponer.• MMULT(X, Y): Multiplicar.• MINVERSA(X): Inversa.

Algunas funciones requieren usar control + shift (ver Ejemplo Matrices).

Funciones Matemáticas


IntroducciónExcel


Tutorial de Excel

Funciones Estadísticas

Algunas funciones estadísticas:

• PROMEDIO(X): Promedio.• DESVEST(X): Desviación Estándar.• MAX(X): Función Máximo.• MIN(X): Función Mínimo.• CUARTIL(X, n): Quintiles.• PERCENTIL(X, k): Percentiles.• FRECUENCIA(X): Frecuencia.• DISTR.NORM(X, Cum): Distribución

Normal con s=1 y m=0.• DISTR.NORM.INV(X): Normal Inversa.• DISTR.NORM(X, m, s, Cum): Distrib.

Normal con s y m dados.


IntroducciónExcel


08-04-2013

10

Tutorial de Excel

Funciones de Regresión

Algunas funciones de regresión:

• INTERCEPTO(Y, X): Calcula el intercepto.• PENDIENTE(Y, X): Pendiente.• COEFICIENTE.R2(Y, X): R cuadrado.• ERROR.TIPICO.XY(Y, X): Error estándar.• COEF.DE.CORREL(Y, X): Correlación.• COVAR(Y, X): Covarianza.

y = 1,825x + 1,425

R2 = 0,8327

0

2

4

6

8

10

1 2 3 4


IntroducciónExcel


Tutorial de Excel

Referencia a Celdas

Una celda puede hacer referencia al valor en otra celda, por ejemplo, la celda A2 puede hacer referencia a la celda A1.

Al copiar una referencia a otra celda, Excel calcula la referencia relativa de la otra celda. Por ejemplo, al copiar B1 en B2, la referencia relativa en B2 es la celda A2.

Una referencia absoluta permite copiar la referencia a la misma celda sin importar dónde se copie. Para hacer una referencia absoluta a la celda A1 se debe colocar =$A$1. También se puede presionar F4 en la referencia.


IntroducciónExcel


08-04-2013

11

IntroducciónExcel


Tutorial de Excel

Tabla con Referencias

Se puede crear una tabla con los valores de la función:

Para lograr esto, se crea una fila con los valores de X y una columna con los valores de Y que se desean tener en la tabla y se copia la siguiente función:

desde la celda C25 al resto de las celdas en la tabla.


YXYXF 32),( 2

IntroducciónExcel


Tutorial de Excel

Tabla de Datos

Se puede crear la misma tabla utilizando la función Tabla de Datos del botón Análisis Y si del tabulador Datos.

Primero se crea una celda en el rincón superior izquierdo de la tabla que contenga una función que depende de X y de Y (ambas celdas deben estar fuera de la tabla).

Luego se selecciona la tabla completa y se usa el botón Tabla de Datos.


08-04-2013

12

IntroducciónExcel


Tutorial de Excel

Gráficos

Excel permite crear distintos tipos de gráficos de forma muy sencilla. Para abrir el asistente para gráficos ir al tabulador Insertar y seleccionar el gráfico que se desea utilizar.


IntroducciónExcel


Tutorial de Excel

Búsqueda en una Tabla

Las funciones BuscarH y BuscarV sirven para encontrar valores en tablas.

Se tiene una tabla de datos donde se debe encontrar el valor que se busca de acuerdo a un parámetro dado.

Ejemplo: Se tienen que colocar las notas a los estudiantes en formato A a F y se tienen los puntajes correspondientes.


08-04-2013

13

IntroducciónExcel


Tutorial de Excel

Texto a Columnas

La funcionalidad Texto a Columna permite insertar datos desde otro formato (por ejemplo en formato texto separado por tabs o por comas) para poder utilizarlos en Excel.

Cuando se tienen datos con una cierta estructura o separadores, se seleccionan los datos y en el tabulador Datos se utiliza el botón Texto en columnas.


IntroducciónExcel


Tutorial de Excel

Auto Filtro y Ordenar

La funcionalidad Auto Filtro del tabulador Datos botón Filtro permite mostrar sólo datos que cumplan un cierto criterio.Es posible hacer filtros personalizados y ordenar los resultados de forma ascendente o descendente. Al filtrar, no se muestran los resultados que no cumplan con los criterios seleccionados.


La funcionalidad Ordenardel tabulador Datos permite ordenar columnas de datos en una tabla. Al ordenar, todos los datos en la tabla se muestran ordenados.

08-04-2013

14

IntroducciónExcel


Tutorial de Excel

Subtotales

La funcionalidad Subtotal del tabulador Datos permite hacer cálculos subtotales en una tabla de datos.

Se puede calcular la suma, el promedio, el máximo, el mínimo, etc. del subconjunto de datos que cumplen con un cierto criterio.


Para calcular los subtotales es necesario tener los datos ordenados en los grupos en que se desea hacer el cálculo. En la figura se ordenan los datos por Edad y después por Sexo y se obtienen subtotales para cada Sexo en cada grupo de Edad.

Tutorial de Excel

Tablas Dinámicas

Una tabla dinámica permite filtrar, procesar, ordenar y mostrar datos en forma resumida.Tabulador Insertar, Botón Tabla Dinámica o Gráfico Dinámico según lo que se desea

Una tabla dinámica puede obtener sus datosde distintas fuentes, como una tabla en Excelo una base de datos en Access o SQLServer.

El asistente para tablas dinámicas ayuda acrear tablas en forma intuitiva. También esposible crear gráficos dinámicos de la misma manera que se crean las tablas dinámicas.


IntroducciónExcel

Visual Basic de Aplicaciones

08-04-2013

15

IntroducciónExcel


Tutorial de Excel

Condicional SI()

La función SI() permite usar condiciones al desarrollar una fórmula.

También se pueden usar las funciones lógicas Y() y O() para las condiciones.

La función SUMAR.SI() permite sumar si un cierto rango de celdas cumple con alguna condición.

Ejemplo Ventas: Encuentre las ventas que cumplen las condiciones dadas en las celdas H3 a K3.


IntroducciónExcel


Tutorial de Excel

Condicional SI()

Ejemplo Bonos: Se tienen una seriede bonos y se requiere saber los flujosque produce cada uno en base a su precio, cupón (interés anual quepaga un título de venta) y vencimiento.

La fórmula en la celda C11 viene dada por:


08-04-2013

16

IntroducciónExcel


Tutorial de Excel

Auditoría de Fórmulas

Permite ver desde qué celdas se obtienen los datos para calcular una fórmula en otra celda.


En la figura podemos ver las celdas quepreceden a C11

Si una celda depende de una celda queprecede o viceversa, se produce una fórmula inválida.

IntroducciónExcel


Tutorial de Excel

Formato Condicional

Formato Condicional Permite formatear un conjunto de celdas de acuerdo a ciertos criterios. Ir al tabulador Inicio, botón Formato Condicional.


08-04-2013

17

IntroducciónExcel


Tutorial de Excel

Análisis de Datos

Esta funcionalidad permite hacer diversos análisis a los datos avanzados en base a funciones que Excel trae pre-programadas. Algunos de los análisis que se pueden realizar son:

Se instala en Inicio Complementos Ir… (En Administrar Complementos de Excel)


IntroducciónExcel


Tutorial de Excel

Buscar Objetivo o Goal Seek

El Goal Seek permite encontrar el valor que debe tener un argumento para que la función entregue un valor dado. Se debe definir la celda a fijar, el valor y la celda que debe cambiar. Se encuentra en el tabulador Datos, Botón Análisis Y si.Ejemplos de cálculos posibles:• Un estudiante quiere saber qué puntaje debe obtener en la I3 para poder pasar con un 60%.• Encuentre el puntaje que debería obtener el alumno para que el curso completo tenga un promedio de 80 puntos.


08-04-2013

18

IntroducciónExcel


Tutorial de Excel

Solver

El Solver de Excel permite resolver problemas de optimización lineales y no lineales contínuos y enteros.

Hay que definir 3 cosas: • La función objetivo• Las variables• Las restricciones

El Solver también se puede usar a travésde VBA, o grabar en una macro.

Ejemplo: Fijación de precio de un producto.


IntroducciónExcel


Tutorial de Excel

Controles

Excel permite agregar controles a las hojas de cálculo que pueden interactuar con los datos y con otros controles que se encuentran en esa hoja.

Cada objeto tiene su nombre, por ejemplo, elBotón que se muestra en la hoja Controles seLlama Botón.

Se utiliza la barra de controles para insertarlos y manipularlos.

Ejemplo: Hacer los ejemplos en hoja Controles.


Pasar a modo diseño

Ver Código

Insertar Controles

Propiedades

08-04-2013

19

IntroducciónExcel


Tutorial de Excel

Macros

Excel permite automatizar tareas repetitivas utilizando un grabador de macrosTabulador Vista, Botón Macros, Grabar Macro

Mientras se graba la macro, todas las acciones que se realicen en Excel quedan guardadas en un procedimiento en lenguaje VBA. Se puede crear la macro con referencia absoluta o referencia relativa. Las macros se pueden correr directamente o con combinación de teclas. Para ver la macro presione alt + f11.


IntroducciónExcel


Tutorial de Excel

Tarea - Taller

En el Sitio SP del curso se encuentra publicado el documento Excel que contiene una parte de la tarea para esta clase-taller.

Espero que lo disfruten.


08-04-2013 - thc.ing.puc.clthc.ing.puc.cl/thc/portals/0/taller 4/tutorial de ms excel iic2100... ·...

Documents