tutorial de excel2-2012
Post on 13-Feb-2015
20 Views
Preview:
TRANSCRIPT
Microsoft Excelpara Ingeniería
Agosto 2012
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
Introducción
¿Qué veremos hoy?
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
Introducción a Excel con ejemplos.
Deberán realizar un taller en el segundo módulo.
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
Introducción
Encuesta
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
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
Visual Basic de AplicacionesAccess
Proyecto
Introducción
Uso de Excel
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
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
Visual Basic de AplicacionesAccess
Proyecto
Introducción
Uso de Excel
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
• Las técnicas de modelación de 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
Visual Basic de AplicacionesAccess
Proyecto
Tutorial de Excel
Pantalla Principal
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
Menú Office
Celda A1
Menú Hojas de cálculo
Barra de fórmulas
Barras de herramientas
Nombre Celda
Hoja de cálculo
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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.
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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.
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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.
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
YXYXF 32),( 2
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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.
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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.
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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.
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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.
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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.
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
La funcionalidad Ordenardel tabulador Datos permite ordenar columnas de datos en una tabla. Al ordenar, todos los datos en la tabla se muestran ordenados.
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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.
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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 mismamanera que se crean las tablas dinámicas.
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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.
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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:
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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.
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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.
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.
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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)
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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.
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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.
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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.
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
Pasar a modo diseño
Ver Código
Insertar Controles
Propiedades
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
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.
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
Tutorial de Excel
Tarea - Taller
En el Sitio Web del curso se encuentra publicado el archivo que contiene la tarea para esta clase-taller.
Espero que lo disfruten.
IIC2100 Taller de Herramientas Computacionales para Ingeniería Módulo de Ingeniería Industrial y de Sistemas
IntroducciónExcel
Visual Basic de AplicacionesAccess
Proyecto
top related