ms excel 2010

128
MICROSOFT EXCEL 2010 [email protected] CELULAR: 91672977 DOCENTE INGENIERÍA CIVIL INDUSTRIAL UVM CONSULTOR PROCESOS MODELOS BPMN, DIAGNÓSTICO, COSTEO ABC Qué es una hoja de cálculo? Para qué se utilizan? Por qué 2010, si ya existe la 2013? => Vers. 2010 es la más utilizada

Upload: luissolari

Post on 26-Dec-2015

77 views

Category:

Documents


8 download

DESCRIPTION

Introducción a MS Excel 2010

TRANSCRIPT

Page 1: MS Excel 2010

MICROSOFT EXCEL [email protected] CELULAR: 91672977DOCENTE INGENIERÍA CIVIL INDUSTRIAL UVMCONSULTOR PROCESOS MODELOS BPMN, DIAGNÓSTICO, COSTEO ABC

-Qué es una hoja de cálculo?-Para qué se utilizan?-Por qué 2010, si ya existe la 2013? => Vers. 2010 es la más utilizada

Page 2: MS Excel 2010

HOJAS DE CÁLCULO I

Unidad mínima de información => Celda Celda => Posee coordenadas

Fila => Se utilizan números Columna => Se utilizan letras

Se nombran por (Columna)(Fila) => B4 Dentro de una celda podemos encontrar:

Texto Números Fechas fórmulas

Page 3: MS Excel 2010

HOJAS DE CÁLCULO

Fórmulas => Operación realizada con datos de otras celdas y cuyo resultado se muestra en la celda

Toda fórmula comienza con un “=”, pero podrían utilizarse también “+”, “-”

Principalmente se utilizan MS Excel (pagada) y Calc de Open Office (es gratis) http://www.openoffice.org/es/)

Page 4: MS Excel 2010

MS EXCEL

Libro de trabajo es un archivo MS Excel => Contiene varias hojas

Para ejecutar Excel :1. Start=>MS Office=>MS Excel2. Acceso directo sobre Desktop

Para Salir de Excel:1. Menú File => Exit2. Botón de cerrar X de ventana

Page 5: MS Excel 2010

COMPONENTES DE EXCEL

Cuadro de nombres

Barra título

Barra de herramientas de acceso rápido

Barra de fórmulas

ZoomBarra de estado

Etiquetas de Hojas

Zona de pestañas

Minimizar, Maximizar / restaurar, Cerrar

Herramientas de pestaña

Barra de desplazamiento vertical y horizontal

Botones de desplazamiento libro

Cabeceras de fila

Selección libro completo

Hoja de cálculo

Page 6: MS Excel 2010

TRUCOS EN EXCEL

Modo de acceso por teclas: Pulsar tecla AltÞ Aparecen pequeños recuadros junto a pestañas

y opciones indicando la tecla (o conjunto de teclas) que deberás pulsar para acceder a esa opción sin mouse

Þ Se sale pulsando tecla Alt Doble clic sobre pestañas=>Barra se

oculta para ganar más espacio. Haciendo clic sobre una pestaña vuelven a aparecer

Ejercicio: pruebe lo anterior

Page 7: MS Excel 2010

INTRODUCIR DATOS EN EXCEL I

Situar el curso sobre la celda donde se van a introducir los datos y teclear datos

Lo ingresado aparece en dos lados: Celda activa y Barra de Fórmulas

Para introducir datos puedes realizar…. Hacer clic sobre el cuadro de aceptación de la barra de

fórmulas (celda activara seguirá donde mismo estaba) Presionar Return (celda activa cambia de lugar) Teclas de movimiento (celda activara cambia de lugar) Hacer clic sobre la X de la barra de Fórmula Presionar Esc

Ejercicio: Pruebe lo anterior

Page 8: MS Excel 2010

INTRODUCIR DATOS EN EXCEL II

Return => cambia de celda y baja una línea

Alt + Return => Baja una línea, pero no cambia de celda, pudiendo ingresar datos en diferentes filas

Ejercicio: Pruebe ingresar “Hola y Saludos” de las dos formas antes mencionadas

Page 9: MS Excel 2010

MODIFICAR DATOS

Si aún no se ha validado la introducción de datos y se comete un error utilice tecla de Retroceso para borrar el carácter

Editar celda ya ingresada: Seleccione la celda adecuada y presione F2 o ir a barra de fórmula o doble clic sobre celda. Barra de estado cambia de listo a modificar

Se puede escribir directamente sobre la celda y sobreescribir su contenido

Ejercicio: Realice lo que se mencionó anteriormente

Page 10: MS Excel 2010

DESPLAZAMIENTOS HOJA

CTRL + Inicio = Comienzo Hoja CTRL + END = Fin Hoja CTRL + Flecha arriba = Margen superior región

datos CTRL + Flecha abajo = Margen inferior región

datos CTRL + Flecha izquierda= Margen izquierda

región datos CTRL + Flecha derecha= Margen derecho región

datos Ejercicio: Pruebe lo anterior

Page 11: MS Excel 2010

SELEC

Seleccionar un rango: Ctr+Shift+*

Page 12: MS Excel 2010

TIPOS DE DATOS

Caracteres: Letras o símbolos: ‘hola Numéricos: Enteros o reales Lógicos: Verdadero o falso Fechas Fórmulas: comienzan con =, + o - Ejercicio: Ingrese cada uno de estos

tipos de datos

Page 13: MS Excel 2010

INGRESAR FÓRMULA

Se ingresa = (o también +, -) Se hace clic sobre primera celda Se agrega operador Se hace clic sobre segunda celda Y se presiona Enter para que se

resuelva la fórmula

Page 14: MS Excel 2010

EJERCICIO

Ingresar una columna con cabecera de columna x y una serie del 1 al 10 con bajo ella

Ingresar una columna adyacente con cabecera de columna con nombre “y1” con la fórmula x^3-2*x+1

Ingresar una segunda columna adyacente con cabecera de columna con nombre “y2” con la fórmula x^4-2*x^3+1

Page 15: MS Excel 2010

RASTREAR FÓRMULAS I

Ver fórmulas y no resultados: Menú Formulas=>Show Formulas

Rastrear fórmulas: Hacer clic sobre fórmulas Menú Fórmulas=>Trace Precedents

Determinar si alguna fórmula utiliza una celda: Hacer clic sobre fórmulas Menú Fórmulas=>Trace Depends

Rastrear un error Hacer clic sobre fórmulas Menú Fórmulas=>Error Checking

Evaluar una fórmula Hacer clic sobre fórmulas Menú Fórmulas=>Evaluate Formula

Hacer seguimiento de valores de celda en varios o el mismo libro de trabajo Watch Window

Page 16: MS Excel 2010

EXTENSIONES

xls => Versión desde 2 hasta 2003 xlsx => Versión 2007 en adelante xlsm => Versión 2007 en adelante con vba

(macros) xlsb => Versión 2007 en adelante para archivos

que poseen una gran cantidad de datos. Reduce tiempo de apertura. Pero su problema es que no se pueden recuperar parcialmente

xltx=>Plantilla Excel, cuando repetimos un tipo de planilla

Page 17: MS Excel 2010

GUARDAR

Existen dos formas de guardar un libro de trabajo:

1. Guardar como. Cuando se guarda un archivo por primera vez, o se guarda una copia de uno existente en otra ubicación. Además se puede cambiar el nombre del archivo

2. Guardar. Cuando guardamos los cambios que haya podido sufrir un archivo, sobreescribiéndolo.

Page 18: MS Excel 2010

GUARDAR PLANTILLA EXCEL (XLTX)

Cuando utilizamos en forma frecuente una misma plantilla, podría ser conveniente agregarla en nuestras plantillas

La ubicación de las plantillas privadas, se modifica en MS Word en Herramientas => Opciones =>Archivos

Creamos la plantilla y la guardamos como xltx

Si deseamos utilizarla, nos vamos a Menú Archivo => Nuevo => Mis PLantillas

Page 19: MS Excel 2010

IR A UNA DETERMINADA POSICIÓN

Anotar en el cuadro de nombres la dirección, ejemplo: C99

Menú Home=>Find & Select=>Go To… Tecla de función: F5 Ctrl + I

Page 20: MS Excel 2010

DESACTIVAR LA OPCIÓN REEMPLAZAR TEXTO MIENTRAS ESCRIBE

Muchas veces Excel modifica nuestras fórmulas cuando las estamos escribiendo y por ello se presentan errores

Ejemplo de lo anterior es cuando nombramos una celda “dolar” y Excel lo corrige por “dólar” (acento) y la fórmula nos muestra un mensaje de error

Por ello para desactivar esta función haga lo siguiente:

Menú File=>Options=>Proofing => AutoCorrect => desactivar Replace text as you type

Page 21: MS Excel 2010

1.- ASOCIAR NOMBRE A UNA CELDA O RANGO

Para qué? Para que la fórmula sea más legible, ejemplo: “2*dolar” en vez de “2*A10”.

Se puede definir un nombre de celda a nivel de libro u hoja, Existen estas formas alternativas de hacerlo:

Nombre válido en todo el libro: Ir a celda y escribir en “cuadro de nombres” el nombre de la celda y presionar “enter”.

Sólo hoja activa: Sobre la celda clic derecho y desde el menú contextual elegir “Define Name...” Y elegir si es válido para todo el libro o sólo la hoja activa

Pestaña “Formulas”=>”Name Manager”=> New... Y elegir si es válido para todo el libro o sólo la hoja activa

Ejercicio: Realizar lo anteriormente mencionado con euro y dolar

Page 22: MS Excel 2010

NOMBRAR UN RANGO

Un rango es un conjunto de celdas Su notación es “primera celda superior:

última celda inferior” (ejemplo A4:C7) Celda superior + F8 + Celda inferior Rangos continuos y Rangos

discontinuos (CTRL)

Page 23: MS Excel 2010

NOMBRAR UN RANGO DINÁMICO I

Cuando definimos un nombre a un rango y este crece, debemos ir ampliándolo cada vez. Pero hay un truco para evitar esto:En el casillero de Refers To box (definición de rango), ingrese una fórmula Offset que el rango, basado en el número de elementos de la columna, e.g.:

=OFFSET(Sheet1!$A$1;0;0;COUNTA(Sheet1!$A:$A);1)

En este ejemplo el rango comienza en celda A1, los argumentos utilizados en esta función son:

Reference cell: Sheet1!$A$1 Rows to offset: 0 Columns to offset: 0 Number of Rows: COUNTA(Sheet1!$A:$A) Number of Columns: 1 Note: for a dynamic number of columns, replace the 1 with: COUNTA(Sheet1!$1:$1)

Page 24: MS Excel 2010

NOMBRAR UN RANGO DINÁMICO IISi es a partir de la celda b31:=OFFSET(Prices!$B$1,30,0,COUNTA(Prices!$B:$B)- COUNTA(Prices!$B1:$B30),2)Or, name the start cell for the range, and use that to find and count the cells above it:=OFFSET(Prices!$B$1,ROW(StartCell)-1,0,COUNTA(Prices!$B:$B) – COUNTA(OFFSET(Prices!B1,0,0,ROW(StartCell)-1,1)),2)

Page 25: MS Excel 2010

EJEMPLO RANGO DINÁMICO

Sub buscar02() Dim array01() Dim i As Integer, j As Integer Dim numLineas As Integer, numColumnas 'Lee el rango y lo asigna al array With Sheet2.Range("datos") ReDim array01(1 To .Rows.Count, 1 To .Columns.Count) array01 = Sheet2.Range("datos") 'Escribe lo leido For i = 1 To .Rows.Count For j = 1 To .Columns.Count Sheet2.Cells(i, j) = array01(i, j) Next j Next i End With End Sub

Page 26: MS Excel 2010

2.- COPIAR Y PEGAR

Utilizando herramienta de pestaña Utilizando menú contextual Ctrl+C (copiar) y Ctrl + v (pegar) Mouse + CtrlUtilizar portapapeles:Ejercicio: Pegado Especial

Page 27: MS Excel 2010

PEGADO ESPECIAL

Todo: Para copiar tanto la fórmula como el formato de la celda. Fórmulas: Para copiar únicamente la fórmula de la celda pero no el formato de

ésta. Valores: Para copiar el resultado de una celda pero no la fórmula, como

tampoco el formato. Formatos: Para copiar únicamente el formato de la celda pero no el contenido. Comentarios: Para copiar comentarios asignados a las celdas (no estudiado en

este curso). Validación: Para pegar las reglas de validación de las celdas copiadas (no

estudiado en este curso). Todo excepto bordes: Para copiar las fórmulas así como todos los formatos

excepto bordes. Ancho de las columnas: Para copiar la anchura de la columna. Formato de números y fórmulas: Para copiar únicamente las fórmulas y todas

los opciones de formato de números de las celdas seleccionadas. Formato de números y valores: Para copiar únicamente los valores y todas los

opciones de formato de números de las celdas seleccionadas.

Page 28: MS Excel 2010

CORTAR Y PEGAR

Ctrl+X (Cortar) Ctrl + V (pegar) Herramientas de pestaña Con Mouse arrastrar Menú contextual

Page 29: MS Excel 2010

3.- BUSCAR OBJETIVO

Se tiene una fórmula y se desea cambiar una celda para cambiar el valor de la fórmula y obtener un determinado valor.

Ejemplo: Se tiene Nota01 y se desea determinar qué Nota02 se debe obtener para obtener promedio 4,0?

Menú Data=>What-If Analysis=>Goal Seek... Definir la celda: Celda que posee fórmula, (promedio en este

ejemplo) Con el valor: 4,0 (valor buscado en este ejemplo) Para cambiar la celda: celda donde está Nota02

Page 30: MS Excel 2010

ADMINISTRAR OBJETIVOS I

Se desea plantear sensibilidad respecto a un determinado problema con variadas alternativas que se desean comparar

Datos => Administrador de escenarios => Agregar… => Agregar un nombre al escenario y definir las celdas cambiantes, que en este caso (préstamo) es la tasa de interés y la cantidad de períodos (G42:G43)

En Administrador de escenarios se elige cualquiera de los escenarios y se presiona botón Mostrar y muestra los distintos escenarios creados

Page 31: MS Excel 2010

ADMINISTRAR OBJETIVOS II

Al crear resumen de escenarios se presenta una tabla con cada uno de ellos

En celdas de resultado, se selecciona la mensualidad o resultado que nos interesa

Page 32: MS Excel 2010

TABLA DE DATOS I

Se analizan una o dos variables Se calcula el pago del préstamo en base a un monto solicitado,

tasa de interés y cantidad de cuotas Se haca referencia a el pago en otra celda y se anota hacia

abajo los porcentajes Se marca todo y se va a Datos => Análisis Y si => Tabla de

datos Por estar en columnas se agrega en Celda entrada (columna) el

porcentaje de tasa de interés de la tabla inicial

Page 33: MS Excel 2010

TABLA DE DATOS II

En la Celda de entrada (fila) se hace clic sobre los períodos de la tabla inicial

En la Celda de entrada (columna) se hace clic sobre la tasa de interés de la tabla inicial

Page 34: MS Excel 2010

4.- SEPARAR TEXTO EN COLUMNAS

Los datos están ubicados en una sola celda, pero los necesitamos en dos celdas, ejemplo Jorge Perez aparece en una sola celda y debemos separarlo en nombre y apellido

Data=>Text to Columns Delimitados: Cuando entre nombre y

apellido hay algún espacio en blanco, coma u otro carácter

De ancho fijo: Cuando el nombre y apellido ocupan siempre la misma posición

Page 35: MS Excel 2010

5.- SERIES

Existen dos formas de crear series (A/B):A. Colocar inicio y próximo número y arrastrar con el mouseB. Menú Home=>Fill=>Series...

1. Introducir incremento y límite

Ejercicio: Crear serie que comience en 3 y termina en 129 con

incremento de 3, en filas Crear serie que comience en 1000 y termine en 2 con

decremento de 2, en filas

Page 36: MS Excel 2010

6.- QUITAR CELDAS REPETIDAS

Menú Data => Remove Duplicates Seleccionar columna donde están

repetidos los valores y activar si es que existen headers

Ejercicio: 1 Pedro Pérez 2 Juan Muñoz 3 Pedro Pérez 4 Pedro Pérez

Page 37: MS Excel 2010

7.- ORDENAR DATOS

ordenar por una columna u ordenar por diferentes columnas a la vez, siguiendo una jerarquía

Pestaña Data=>Sort ascendente/descendente Se ordena según la columna que contenga la celda activa

Ventana con: Ampliar Selección/Continuar con la selección actual

Pestaña Datos=>Ventana Ordenar Se puede ordenar en base a más de un criterio

Page 38: MS Excel 2010

8.- VALIDACIÓN DE DATOS

Nos permite asegurarnos que los valores que se introducen en las celdas son los adecuados. Pudiendo mostrar un mensaje de error o aviso si nos equivocamos

Pestaña Datos=>Validación de Datos Permitir=>Lista Sólo se pueden ingresar los

números de la lista Se puede ingresar mensaje de entrada y de error Si no se ingresa el mensaje de error o se ingresa

de tipo “Información”, se puede utilizar la opción “Rodear con un círculo datos no válidos”

Page 39: MS Excel 2010

9.- BUSCAR Y REEMPLAZAR DATOS I

Menú Home=> Find & Select : Abrirá una ventana de Find Replace

Si tenemos un rango seleccionado, sólo buscará en esa selección

En opciones podemos determinar dónde buscar (hoja/libro de trabajo), tipo de búsqueda (por fila/columna) y dónde buscar (fórmulas, valores, comentarios)

Page 40: MS Excel 2010

BUSCAR Y REEMPLAZAR DATOS II

Se pueden utilizar wildcards tales como “*” y “?” s*d encuentra sad, started s?d encuentra sad

Para encontrar símbolos * y ?, se utiliza ~? y ~* Match case es para separar búsqueda de

mayúsculas y minúsculas Match entire cell contents para buscar

exactamente lo buscado en una celda y no parte de ella

Replace es para reemplazar lo encontrado

Page 41: MS Excel 2010

VER UN MISMO LIBRO EN UNA VENTANAS

Menú Vista => Nueva Ventana Menú Vista => Organizar Todo Menú Vista => Dividir

Page 42: MS Excel 2010

VER VARIOS LIBROS EN UNA MISMA VENTANA

Abrir varios libros Menú Vista => Organizar Todo Menú Vista => Dividir (Arrange All) Elegir tipo de layout Tiled, Horizontal,

Vertical, cascade

Page 43: MS Excel 2010

DOS HOJAS EN VERTICAL

Abrir dos hojas (del mismo o de diferentes libros de trabajo)

Ubicarlos una al lado del otra (vertical) o utilizar “View Side by Side”

Activar “Synchronous Scrolling”

Page 44: MS Excel 2010

GUARDAR WORKSPACE

Se pueden guardar el layout de ventanas

Menú View=>Save Workspace

Page 45: MS Excel 2010

CAMBIAR ENTRE VENTANAS

Si se poseen dos o más libros abiertos se desea cambiar entre ventanas para ello se debe...

Menú View=>Switch Windows

Page 46: MS Excel 2010

INMOVILIZAR PANELES

Si columna a inmobilizar está en B y filas en 1: Menú Vista=>Inmovilizar Inmovilizar primera fila o columna

Si datos están en otro lugar: Marcar datos Menú Vista => Inmovilizar Paneles

Page 47: MS Excel 2010

EJERCICIO 01

Repaso: 1.- Defina una celda con el nombre x y otra con el nombre y, y

represente la adición de éstas en otra celda que haga referencia a x e y. 2.- Utilice auditoría para comprobar la fórmula 3.- Con cuál tecla se modifican los datos ingresados 4.- Genere una serie de 1000 a 0 en filas con paso 5 5.- Ordene los números 3,4,5,6; 5,6,1,2;1,5,6,2 según columna 1 y luego

columna 2. Con cabeceras de columnas A, B, C y D 6.- Cómo se puede ir a celda f505? 7.- Obtenga el día de hoy con hora y fecha, cuál es el último laboral de

mayo? 8.- Validar ingreso de números entre 1 y 9. Si no debe indicarse mensaje

de error 9.- Abra dos libros de trabajo y ubica las ventas en forma horizontal,

vertical y vertical sincronizado

Page 48: MS Excel 2010

10.- DROP DOWN PARA ELEGIR VALORES

De forma de no cometer errores en ingreso se elige de una lista desplegable (drop down) una lista de valores: Seleccionar celdas que posean drop

down Menú Data=>Data Validation=>Ficha

settings => List Seleccionar columna de fuente de

datos

Page 49: MS Excel 2010

11.- TIME SERIES

Si se desea realizar una serie de tiempo, ejemplo comenzar el 21.01.2013 y terminar el 30.01.2013 se pueden utilizar series

Colocar la fecha inicial y dejar esa celda como activa (haz clic sobre esa celda)

Menú Home=>Fill=>Series=>Date Elegir una Date unit: día, semana, mes,

año

Page 50: MS Excel 2010

12.- SPECIAL BUTTONS: FORM, CAMERA I En Excel existen botones o fichas que se

deben activar primero antes de ser utilizadas File => Customize Ribbon => All Commands

(del drop down) => Buscar Form... Al otro lado en Customize the Ribbon agregar:

New Tab: Es una nueva ficha de herramientas New Group: Es dentro de ficha de herramientas

Page 51: MS Excel 2010

12.- SPECIAL BUTTONS: FORM, CAMERA II Form (formulario automático)

Arrastrar Form... al nuevo tab/group Aceptar y volver a planilla Crear encabezados de columnas Hacer clic sobre la tabla y llamar a Form Ingresar Datos... Buscar Datos...

Page 52: MS Excel 2010

13.- SPECIAL BUTTONS: FORM, CAMERA III

Camera Arrastrar Camera... al nuevo tab/group Aceptar y volver a planilla Marcar área de origen y hacer clic sobre

botón camera Ir a destino y pegar celdas

Page 53: MS Excel 2010

14.- LISTAS PERSONALIZADAS

A veces se requieren listas personalizadas de elementos que se repiten, ejemplo lista de naves, costos de personal, etc.

Menú File=>Options=> Advanced => Al final en General existe un botón con “Edit Custom Lists...”

Agregar los elementos de la lista ya sea escribiéndolos o importándolos

Clic sobre Ok, cerrar Excel Options y volver a la planilla

Escribir el primer elemento de la lista y arrastras celda

Page 54: MS Excel 2010

PERSONALIZAR SIGLAS

Cuando se repite mucho una palabra se puede automatizar y reemplazar automáticamente, ejemplo CSAV por Compañía Sudamericana de Vapores

Menú File => Options => Proofing => Autocorrect Options... => Autocorrect => Activar “Replace text as you type”

Ingresar en Replace “CSAV” Ingresar en With “Compañía Sudamericana de

Vapores” Problema: Si se define un nombre de celda “dolar”

Excel la corregirá por “dólar” y no se referenciará a la misma celda

Page 55: MS Excel 2010

15.- FUNCIONES DE CONDICIÓN

=IF(condición;Verdadero;Falso) IF(b3>=10;”Valor >= 10”;”Valor <10 ”) IF(OR(c3>=10;c4>=10);100;0) OR(expr01;expre02) y AND(expr01;expr02)

Si existe un error mostrará un mensaje o valor =IFERROR(celda;valor o mensaje si hay

error) Si es mensaje se debe utilizar comillas

“mensaje”

Page 56: MS Excel 2010

FORMATO CONDICIONAL

Marcar rango de datos Pestana inicio=>Formato condicional Distintas opciones: barras de datos,

escalas de color, conjunto de íconos, valores repetidos

Page 57: MS Excel 2010

17.- REFERENCIAS MIXTAS Y GLOBALES Cuando se copia una fórmula hacia

abajo se actualizan las referencias, pero a veces se deben dejar fijas algunas celdas Mixtas: $A23 o A$23 Globales: $A$23 Se marca la referencia y se presiona la

tecla F4

Page 58: MS Excel 2010

EJERCICIOS02

Consultar hoja Ejercicios02

Page 59: MS Excel 2010

18.- PROTEGER HOJA I

Se protege una hoja para restringir el acceso de edición de la hoja

Seleccionar celdas donde el usuario ingresará valores. Botón derecho del mouse sobre ellas => Format cels =>Protection => desactivar “Locked”

Si se desea esconder fórmulas se debe activar Hidden, así cuando se haga Formulas=>show formulas no mostrará la fórmula

Pestaña Celdas=>Formato=>Proteger hoja Botón derecho del mouse=>menú

contextual=>Proteger/Desproteger hoja

Page 60: MS Excel 2010

PROTEGER HOJA II

Realizar ejercicios de proteger hoja y drop downs

Page 61: MS Excel 2010

INSTRUIR, ENSEÑAR Y EDUCAR

Ideas sobre enseñar y educar Instruir y enseñar son acciones que van desde fuera hacia

adentro y se expresan en contenidos programáticos, educar, en cambio es una acción que va desde dentro hacia

afuera del individuo y sus contenidos significativos representan actitudes, conductas, realización de valores e ideales.

Educar es una acción provocada o motivada indirectamente para que nazca y renazca constantemente en el ser que se educa ese impulso auto educativo que es la expresión de la propia voluntad de perfeccionamiento. Por eso, ningún docente puede educar cabalmente; sólo puede poner al educando en la senda e indicarle la dirección en que el mismo proseguirá desarrollándose

Page 62: MS Excel 2010

ENLAZANDO Y CONSOLIDANDO HOJAS DE TRABAJO

Utilizar fórmulas de varias hojas/libros para combinar datos:

Hoja/Libro de trabajo dependiente: es el que contiene fórmulas

Hoja/Libro de trabajo independiente: es el que contiene datos

Pregunta antes de enlazar hojas/libros de trabajo: Realmente nos hace falta complicarnos tanto o sencillamente hacerlo todo en una misma página?

Notación =Hoja3!C3

Page 63: MS Excel 2010

FORMATO DE CELDAS

Excel nos permite cambiar la apariencia de los datos

Fuente: Marcar dato y haz clic en flecha que se encuentra al pie de la sección Fuente => Se abrirá el diálogo de formato de celdas:

Fuente, estilo y Tamaño

Page 64: MS Excel 2010

ALINEACIÓN

Haz clic en la flecha que se encuentra al pie de la sección Alineación: Aparece la ficha Alineación Horizontal: horizontal, general

(num=>derecha, texto=>izq),Izquierda (sangría), centrar, derecha (sangría),

rellenar, justificar, centrar en selección, distribuído (sangría)

Page 65: MS Excel 2010

BORDES

Ejercicio con bordes

Page 66: MS Excel 2010

FORMATO NUMÉRICO

Ejercicio con formatos

Page 67: MS Excel 2010

AUTOAJUSTAR

Autoajustar:Si hemos modificado la altura de una fila, podemos redimensionarla para ajustarla a la entrada más alta de la fila, utilizando dos métodos distintos: Pestaña Celdas=>Formato=>Tamaño

celda=>Alto de fila/Autoajustar alto de fila Llevar el puntero del mouse al borde de la

columna o fila y cambiarlo por mano

Page 68: MS Excel 2010

CAMBIAR NOMBRE A HOJA, MOVER/COPIAR HOJA, COLOR DE ETIQUETA

Doble clic sobre el nombre de la hoja para renombrarla

Menú contextual: Mover y Copiar hoja Mover hoja dentro del libro activa Borrar hoja Insertar hoja Hide / Unhide hoja

Page 69: MS Excel 2010

VISIBILIDAD FILAS, COLUMNAS

Marcar fila/columna y podemos: Insertar Borrar Borrar contenidos Ocultar / mostrar Arrastrarla y moverla de lugar Copiar (ctrl + arrastrar mouse)

Page 70: MS Excel 2010

INSERTAR / ELIMINAR FILAS O COLUMNAS Pestaña Celdas=>Insertar / Eliminar Botón derecho del mouse=>menú

contextual=>Insertar/Eliminar Filas/Columnas Observación filas: Añadir filas a nuestra hoja de cálculo no

hace que el número de filas varíe, seguirán habiendo 1.048.576 filas, lo que pasa es que se eliminan las últimas, tantas como filas añadidas. Si intentas añadir filas y Excel no te deja, seguro que las últimas filas contienen algún dato

Observación columnas: Añadir columnas a nuestra hoja de cálculo no hace que el número de columnas varíe, seguirán habiendo 16384 columnas, lo que pasa es que se eliminan las últimas, tantas como columnas añadidas. Si intentas añadir columnas y Excel no te lo permite, seguro que las últimas columnas contienen algún dato

Page 71: MS Excel 2010

VISTAS DE EXCEL

Normal Diseño de página (Page Layout): Para

ver y ajustar las celdas y objetos de nuestro documento

Vista Previa Salto de página (Page break preview)

La pestaña Vista permite personalizar qué elementos mostrar

Page 72: MS Excel 2010

ZOOM

Zoom 100% Zoom to Selection: Ampliar Selección:

aumenta el zoom a 400%, centrándolo en la celda u objeto seleccionado

Page 73: MS Excel 2010

EN LA VISTA NORMAL, LAS LÍNEAS DE LOS SALTOS DE PAGINA I

se muestran automáticamente después de que: a) haces uso de la "vista previa de saltos de pagina b) muestras una impresión preliminar c) imprimes la hoja

Estas se borran en File => Options => Advanced => Display options for this worksheet => Desactivar “Show page breaks”

Esto podría tener efectos colaterales si haces uso de macros que se basen en la determinación o búsqueda de esos saltos de pagina

Page 74: MS Excel 2010

EN LA VISTA NORMAL, LAS LÍNEAS DE LOS SALTOS DE PAGINA II

Page 75: MS Excel 2010

ESQUEMAS Y GRÁFICOS EN EXCEL

Insertar una imagen Insertar Clip Art Insertar formas Insertar estructuras Insertar Screenshot

Page 76: MS Excel 2010

LENGUAJE EN MS EXCEL

Para cambiar de idioma en MS Excel se debe comprar el módulo de idioma e instalarlo

Luego en menú File => Options => Language establecer el idioma de preferencia

Cerrar Excel y volver a abrir

Page 77: MS Excel 2010

CONFIGURAR PÁGINA I

Antes de imprimir una hoja de cálculo, es conveniente que configuremos la página, para modificar factores que afectan a la presentación de las páginas impresas, como la orientación, encabezados y pies de página, tamaño del papel, ...

Page Layout => Flecha de Grupo Page Up (esquina inferior derecha Print area: Definir sólo lo que se desea imprimir Print titles Print Page order

Page 78: MS Excel 2010

CONFIGURAR PÁGINA II

Agregar pie de página Agregar cabecera de página

Page 79: MS Excel 2010

EJERCICIOS03

Ver hoja ejercicios03

Page 80: MS Excel 2010

20.- FUNCIONES

Una función es una fórmula predefinida por Excel (o por el usuario) que opera con uno o más valores y devuelve un resultado que aparecerá directamente en la celda o será utilizado para calcular la fórmula que la contiene

Las fórmulas pueden contener más de una función, y pueden aparecer funciones anidadas dentro de la fórmula.

La sintaxis de cualquier función es: = nombre_función(argumento1;argumento2;...;argumentoN)

Siguen las siguientes reglas: - Si la función va al comienzo de una fórmula debe empezar por el signo =. - Los argumentos o valores de entrada van siempre entre paréntesis. No dejes

espacios antes o después de cada paréntesis. - Los argumentos pueden ser valores constantes (número o texto), fórmulas o

funciones. - Los argumentos deben de separarse por un punto y coma ;. Ejemplo: =SUMA(A1:C8) Tenemos la función SUMA() que devuelve como resultado

la suma de sus argumentos. El operador ":" nos identifica un rango de celdas, así A1:C8 indica todas las celdas incluidas entre la celda A1 y la C8

Page 81: MS Excel 2010

AUTOSUMA Y FUNCIONES MÁS FRECUENTES

Marcar rango a sumar luego Menú Home=>AutoSum/Average/Count numbers/Max/Min

Para insertar cualquier otra función hacer clic sobre fx en la barra de fórmulas

O sobre Menú Formulas=>Elegir tipo de fórmulas

Page 82: MS Excel 2010

FUNCIONES

Sumar.Si: Suma elementos si cumplen cierta condición

Números romanos: roman(número) Unir texto o concatenar:

concatenate(texto1;texto2); Mayúscula: upper(celda) Minúscula: lower(celda) INDEX(array;Row;Col) entrega un valor

de una matriz

Page 83: MS Excel 2010

FUNCIONES DE FECHA Y HORA

Investigar las siguientes funciones: Castellano: Ahora(), Año(), dia(),

dia.lab(), fin.mes() Inglés: Today(), year(), day(), month(),

workday(), Eomonth() Ejercitar estas funciones

Page 84: MS Excel 2010

REDONDEAR NÚMEROS

A veces se requiere redondear valores hacia arriba o hacia abajo o al múltiplo inferior/superior

Page 85: MS Excel 2010

BÚSQUEDA AUTOMÁTICA

Buscar un elemento dentro de una matriz (vlookup o buscarV)

Vlookup( lo que se busca; Matriz donde se busca; De cuál columna 1, 2, 3, etc. Coincidencia debe ser aproximada (TRUE)

o exacta (FALSE) )

Page 86: MS Excel 2010

REFERENCIAS RELATIVAS, MIXTAS Y ABSOLUTAS

Cuando se copia la fórmula se puede perder la referencia a la cual hace mención => F4

Referencia relativa: A1 Referencia mixta: A$1 o $A1 Referencia absoluta: $A$1 Ejercicios

Page 87: MS Excel 2010

21.- CONTAR.SI() O COUNTIF()

Cuenta el número de celdas dentro de un rango que cumplen un criterio especificado

=CONTAR.SI(rango;criterios) Rango pueden ser una o más celdas que se van a

contar. Pueden ser números o texto. Los valores en blanco no se cuentan

Criterios puede ser un número, referencia de celda o cadena de caracteres, ejemplo 32, “>32”, B4, “manzanas” o “32”. Se pueden utilizar los wildcard (comodín) de “?” (un carácter)

y “*” (varios caracteres). Si desea buscar un “?” o “*” escribir una tilde delante del

signo “~”. No se distingue entre minúsculas o mayúsculas

Page 88: MS Excel 2010

SUMAR.SI

Se suman los valores si se cumple un criterio específico

Page 89: MS Excel 2010

CONEXIÓN CON MYSQL (NS5)

Mediante una conexión con ODBC a Mysql del NS5 se obtienen los datos directamente de la base de datos, sin necesidad de importarlos

Esto permite trabajar más rápido y los datos están siempre actualizados

Page 90: MS Excel 2010

EJERCICIO04

Hacer ejercicio04 en planilla Excel

Page 91: MS Excel 2010

=SUMAPRODUCT(MATRIZ01;MATRIZ02) Se requiere multiplicar el contenido de dos

matrices y luego sumar su resultado. Ejemplo columna “costo unitario” con “cantidad vendida por ítem” y se desea determinar la “suma total vendida

Page 92: MS Excel 2010

EJEMPLO DE PRUEBA

Page 93: MS Excel 2010

29.- GRÁFICOS

Gráfico incrustado u hoja de gráfico Seleccionar serie de datos (variable dependiente,

si es y=x, entonces sólo los valores de y. Luego Pestaña Insertar=>Seleccionar uno de los Gráficos

Seleccionar origen de datos Botón derecho del mouse para menú Pestaña=>Presentación Pestaña=>Diseño Modificar tamaño del gráfico Pestaña=>Diseño => Mover gráfico

Page 94: MS Excel 2010

FORMULAS DE TRENDLINE

Page 95: MS Excel 2010

PARA OBTENER LA FÓRMULA DE TRENDLINE Cuando se agrega un trendline a un gráfico no se

obtiene su fórmula en la celda de excel, sino sólo en el gráfico, para ello se utiliza lo siguiente…

y = (c2 * x^2) + (c1 * x^1) + b c2=INDEX(LINEST(y; x^{1\2});1) c1=INDEX(LINEST(y; x^{1\2});1;2) b=INDEX(LINEST(y; x^{1\2});1;3)

Page 96: MS Excel 2010

29.- MINIGRÁFICOS O SPARKLINES

Minigráficos ( a partir de MS Excel 2010) pequeño gráfico representativo en una única celda

que representará a una única serie de datos. De esta forma podrás ver de una ojeada la tendencia que representan unos determinados valores

Menú Insertar=>Minigráficos Aparece Pestaña «Herramientas para

minigráfico»=>Diseño No se pueden borrar con la tecla borrar/delete, se

debe hacer con «Herramientas para minigráfico»=>Diseño=> Borrar o Ficha Inicio=>Borrar todo

Page 97: MS Excel 2010

HIPERVINCULOS

Es como un botón que al hacerle un clic abre una página web, envia un email o abre un documento

Puede ser texto, una imagen (Menú Insertar => Shapes)

Sobre texto o imagen presione botón derecho del mouse para obtener menú contextual y elegir hipervínculo de él

Puede seleccionar Página web o archivo Seleccionar hoja del libro activo Crear documento nuevo Enviar un email

Page 98: MS Excel 2010

30.- ESQUEMAS Y VISTAS I

Existen unos requisitos previos para que Excel 2010 pueda crear automáticamente el esquema:

- Las filas sumario (sumatoria de columnas) deben estar por encima o por debajo de los datos, nunca entremezclados

- Las columnas sumario (sumatoria de columnas) deben estar a la derecha o a la izquierda de los datos, nunca entremezclados

Si la disposición de los datos no se corresponde con estas características nos veremos obligados a definir el esquema manualmente.

Page 99: MS Excel 2010

ESQUEMAS Y VISTAS II

Menú Datos=>Agrupar => Autoesquema

Page 100: MS Excel 2010

INCLUIR SUBTOTALES

ordenar los datos según alguna agrupación, ejemplo departamento Marcar datos y Menú Data=>Subtotal “At each change in” colocar departamento “Use function” colocar la función que

aglutinará datos, ejemplo “sum” “Add subtotal to” agregar subtotal en

donde se sumarán los datos Con Remove All se retiran los subtotales

Page 101: MS Excel 2010

ESTRUCTURA DE ESQUEMAS

Region Enero Febrero Marzo 1rTrimestreVina 12 10 9 31Valparaíso 14 11 10 35V Region 26 21 19 66La Serena 18 13 12 43Coquimbo 20 14 13 47IV Region 38 27 25 90Chile 64 48 44 156

Es una sumatoria de la columna de arriba

Los valores son sumatorias de la fila

Page 102: MS Excel 2010

CÓMO HACER EL ESQUEMA AUTOMÁTICO? Clic sobre esquina izquierda superior de los

datos Menú Datos=>Agrupar=>Autoesquema Vemos en la esquina superior izquierda unos

números que nos indican cuántos niveles tiene el esquema.

Por columnas podemos ver que existen dos niveles: - La tabla desplegada por completo - y la tabla con los datos trimestrales.

Page 103: MS Excel 2010

ESQUEMA MANUAL

Marcar filas Vina y Valparaíso, Menú Datos=>Agrupar

Marcar filas La Serena y Coquimbo, Menú Datos=>Agrupar

Marcar todas filas sin Chile, Menú Datos=>Agrupar

Page 104: MS Excel 2010

ESQUEMAS

Borrar esquema, Menú Datos=>Desagrupar => Borrar esquema

Esconder/Mostrar esquema Ctrl+8 (del teclado numérico)

Page 105: MS Excel 2010

ES BUENO SABER DE TABLAS DE EXCEL Es un conjunto de datos organizados en filas o

registros, en la que la primera fila contiene las cabeceras de las columnas (los nombres de los campos), y las demás filas contienen los datos almacenados

Cada fila es un registro de entrada, por tanto podremos componer como máximo una lista con 255 campos y 65535 registros

En versiones más antiguas de Excel, las tablas se denominaban Listas de datos. Incluso es posible que en algunos cuadros de diálogo, se referirá a las tablas como listas.

Page 106: MS Excel 2010

TABLAS I: CÓMO HACER UNA TABLA?

Marcar los datos, encabezados de filas y columnas

Menú Insertar=>Tabla Activaremos la casilla de verificación “La

lista tiene encabezados” Aparece la pestaña “Herramientas de tabla”

Diseño Para deshacer la tabla:

“Herramientas de tabla” Diseño => Convertir en rango

Page 107: MS Excel 2010

TABLA III: ¿CÓMO HACER FILTRADO AVANZADO?

Ficha Datos=>Filtro =>Avanzadas Se define „criterios de filtrado“ copiando las

cabezas de filas. Bajo ellas se ingresan los criterios. En la misma fila es la condición „AND“ y en filas distintas es „OR“

Se puede seleccionar copiar en otra área de la hoja los resultados de la selección

Sólo registros unicos se refiere a si existen dupletas de datos, se mostrarán solo una vez

Page 108: MS Excel 2010

TABLAS IV: AGREGAR DATOS DE UNA TABLA

Para modificar o introducir nuevos datos en la tabla podemos teclear directamente los nuevos valores al final de la tabla

P en la última fila presionar la tecla “TAB” Para modificar rango de tabla => ir a punta

derecha abajo y moverla o botón “Cambiar tamaño de la tabla”

Crear nueva fila: Estando en última fila presionar TAB

Filtro de datos, borrar filtro

Page 109: MS Excel 2010

TABLA V: TOTALES EN UNA TABLA

Hacer clic sobre la tabla En la nueva pestaña „Herramientas de

tabla“ activar el checkbox fila de totales

En la fila de totales seleccionar la función deseada

Page 110: MS Excel 2010

TABLA VI: OPERACIONES CON COLUMNAS Al insertar elementos en una tabla, se

incrementa y anexan elementos a ella Si se desea procesar una columna de

una tabla, se hace referencia a ella mediante: nombreTabla[nombreColumn], ejemplo:

Page 111: MS Excel 2010

TABLA VII: OPERACIONES CON COLUMNAS Si no se desea utilizar la notación ampliada

de nombreTabla[nombreColumn], se debe definir un rango con cada columna y luego se inserta la tabla

Apenas se agrega un elemento nuevo se amplia tabla y se amplían rangos automáticamente

Page 112: MS Excel 2010

IMPORTAR DATOS DE TEXTO O ARCHIVO PLANO A MS EXCEL

Menú File => Open o también en Menú Data=>From Text

Determinar separador de datos: Espacio en blanco, semicolon (;), etc.

Dar formato a columnas, según necesario, ejemplo columna de fechas se debe identificar si es DMY o YMD (year month day)

Page 113: MS Excel 2010

ERRORES EN EXCEL (VER HOJA ERRORES) ##### se produce cuando el ancho de una columna no

es suficiente o cuando se utiliza una fecha o una hora negativa.

#VALUE! cuando se ha introducido un tipo de argumento o de operando incorrecto, como puede ser sumar textos.

#¡DIV/0! cuando se divide un número por cero. #NAME? cuando Excel no reconoce el texto de la fórmula. #NUM! cuando un valor no está disponible para una

función o fórmula. #¡REF! se produce cuando una referencia de celda no es

válida.

Page 114: MS Excel 2010

PRUEBA Y REPASO VER HOJA EJERCICIOS06

Desarrolle los ejercicios de la hoja Ejercicios06

Page 115: MS Excel 2010

TABLA DINÁMICA I

Una tabla dinámica consiste en el resumen de un conjunto de datos, atendiendo a varios criterios de agrupación, representado como una tabla de doble entrada que nos facilita la interpretación de dichos datos

Es dinámica porque nos permite modificar su estructura al ir obteniendo diferentes totales, filtrando datos, cambiando la presentación de los datos, visualizando o no los datos origen, etc.

Page 116: MS Excel 2010

TABLA DINÁMICA II

La tabla de origen no debe contener filas o columnas vacias

Hacer un clic sobre la tabla Menú Insert=>Pivot Table (Tabla Dinámica) En la „Lista de campo“ existen 4 campos:

Report Filter, Column Labels, Row Labels, Values

En Values van los datos que serán sumados, promediados, etc.

Page 117: MS Excel 2010

TABLA DINÁMICA III

Report Filter: Crea un filtro con drop down

Column Labels: Son los encabezados de columna

Row Labels: Son los encabezados de Fila

Values: Son los datos y acá se puede seleccionar la operación sobre los datos, ejemplo SUM, Average, etc.

Page 118: MS Excel 2010

TABLA DINÁMICA IV Se arrastran campos a las áreas y se

genera la siguiente tabla

Page 119: MS Excel 2010

TABLA DINÁMICA V

Modificar celdas vacías => botón derecho mouse sobre la tabla=> „PivotTable Options...“ => For empty cells rows…

Para modificar formato de valores: botón derecho del mouse sobre la tabla=>Value Field Setting… (Configuración de campo valor...)=> Botón Number Format (Formato de número)

En la tabla se puede cambiar „Row Lebels“ y „Column Labels“ Si se modifica la tabla origen se debe actualizar la tabla

dinámica Si se ingresa filas o columnas en la tabla de origen, no se

actualiza la tabla dinámica Existe la opción de que cuando se abra el libro se actualice la tabla dinámica

Page 120: MS Excel 2010

TABLA DINÁMICA VI

En la pestaña Diseño se pueden realizar diferentes cambios en formato… activar/desactivar Totales generales y

Subtotales, Insert Blank line after each item

Page 121: MS Excel 2010

TABLA DINÁMICA VI

Slider nos permite automatizar categorías por botones

Hacer clic sobre tabla dinámica Menú Insert => Slicer => Elegir campo

Con Ctrl+ elegir otro campo se pueden seleccionar varios campos

Page 122: MS Excel 2010

TABLA DINÁMICA VII

Los filtros en una tabla dinámica se manejan haciendo clic sobre la punta de flecha

Page 123: MS Excel 2010

GRÁFICOS DINÁMICOS

Hacer clic sobre la tabla dinámica En Menú Options de “PivotTable Tools”

=> PivotChart Hacer clic en los filtros

Page 124: MS Excel 2010

CONSOLIDAR

Se poseen varias hojas de planilla con iguales estructuras, pero definidas en diferentes unidades de tiempo y se desean consolidar, sumando/promediando sus valores

Cada tabla está en un libro/hoja distinto, pero en igual posición

Hacer clic en celda de destino de consolidación Del menú Data => Consolidate => Definir los rangos

y agregar a la lista Activar Use labels in Top row Sólo se puede activar “Create links to source data” si

son de libros distintos. Se actualiza información

Page 125: MS Excel 2010

CREAR VINCULO A DATOS DESDE INTERNET

Se desea crear un vínculo con una página web que lea determinados valores, ejemplo valor del dólar, uf, euro, etc

Menú Data=>From Web => Ir a página Web deseada => De esta página hacer clic sobre flechas amarillas (aparece ticket verde)=> Presionar botón import

Clic sobre el botón Properties…=> Refresh data when opening the file

Page 126: MS Excel 2010

SUMA DE TIEMPO

MS Excel suma en base a horas en días, por ello se puede cometer un error…

Page 127: MS Excel 2010

CALCULAR DIFERENCIA DE TIEMPO I

Para presentar la diferencia horaria en el formato estándar de tiempo

Page 128: MS Excel 2010

CALCULAR DIFERENCIA DE TIEMPO II

Para presentar la diferencia horaria en algún tipo de unidad horaria (hora, minuto, segundo)