MICROSOFT OFFICE EXCEL 2010

1. MICROSOFT OFFICE EXCEL 2010 Quito, 5 de Mayo del 2014 Antonio Correa Email : support@consultingcorrea.com Celular : 0998179809

2. AGENDA • Antecedentes Microsoft cuando lanzó su primer sistema operativo y aplicación estaba orientado a Ingenieros de Sistemas, ahora en la actualidad es una herramienta necesaria para todos, facilitando el uso de los comandos con mejoras mediante la interfaz gráfica. • Objetivo del Curso Es para que ejecutivos y personal en organizaciones y empresas, al momento de migrar a Microsoft Office 2010, pueda utilizar la nueva interfaz facilitándole su trabajo.

3. AGENDA • Microsoft Excel 2010 • Tipos de datos, Series • Copia de fórmulas referencias relativas y absolutas. • Filtros Automático. • Funciones y fórmulas personales y de programa. • Nombres • Vínculos • Buscar objetivo • Tabla de datos • Subtotales • Formato Condicional • Esquemas • Plantillas • Tablas y gráficos dinámicos para reportes gerenciales • Proteger libros y hojas • Gráficos

4. INTERFAZ USUARIO OFFICE 1 2 3 FICHA GRUPO COMANDO

5. FICHAS CUANDO SON NECESARIAS 3 2 1 SELECCIONAR ELEMENTO DEL DOCUMENTO APARECE LAS HERRAMIENTAS CONTEXTUALES Y LAS FICHAS ESTAS FICHAS TIENEN OTROS CONTROLES

6. FICHAS DEL PROGRAMA Las fichas de programa reemplazan a las fichas estándar Ej.: Vista Preliminar

7. MENU La ficha ARCHIVO está ubicado en la esquina superior izquierda de la ventana y abre el menú como se muestra en la figura.

8. BARRA DE HERRAMIENTAS DE ACCESO RAPIDO ACCESO RAPIDO Aparece de forma predeterminada en la parte superior de la ventana, y proporciona acceso rápido a herramientas que se usan con mas frecuencia.

9. INICIADORES DE CUADROS DE DIALOGO 1 Son pequeños iconos que aparecen en ciertos grupos, al dar click se abre un cuadro de diálogo o un panel de tareas que contiene más opciones relacionadas a dicho grupo.

10. Microsoft Excel 2010

11. TIPOS DE DATOS

12. REFERENCIAS ABSOLUTAS EXISTEN TRES TIPOS DE REFENCIAS ABSOLUTAS EN LAS FORMULAS Referencia absoluta total $columna$fila, en nuestro ejemplo $g$1 que esta en la formula c4+c4*$g$1, si copio y pego en cualquier parte de la hoja, cambiara c4 pero $g$1 no.

13. REFERENCIAS ABSOLUTAS Referencia absoluta con respecto a la columna $columna fila, en nuestro ejemplo $E2 que esta en la formula $E2*B2, si copio y pego hacia la derecha o izquierda de la comuna de la hoja, cambiara B2 pero $E2 no, pero si copio hacia arriba o abajo cambia la fila. En la celda H2 pegué y el PVP sigue siendo el mismo $E2 el que si cambio fue a D2

14. REFERENCIAS ABSOLUTAS Referencia absoluta con respecto a la fila columna$fila, en nuestro ejemplo B$5 que esta en la formula B$5*B2, si copio y pego hacia arriba o debajo de la fila 5, cambiara B2 pero B$5 no, pero si copio hacia la derecha o izquierda cambia la columna. En la celda B8 pegué la fórmula de B6 y el PVP sigue siendo el mismo B$5 el que si cambio fue a B4.

15. GENERACION DE SERIES Vamos a realizar el ejemplo más sencillo Generar los numero del 1 al 10 1.- Digitar el número 1 en una celda 2.- Colocar el puntero del mouse en la esquina inferior derecha de la celda donde esta el número. (forma del mouse +) 3.- Teniendo presionado el clic derecho arrastrar 10 filas hacia abajo y le aparece el siguiente menú contextual 4.- Dar clic en la opción Rellenar Serie 5.- Si Desea rellenar con incrementos en el paso 4 seleccionar la opción Series donde puede digitar el incremento el límite hasta don desea rellenar y si desea en columna o fila

16. FILTRO AUTOMATICO Las dos imágenes de la izquierda nos indican por donde ingresamos al filtro automático por la ficha Inicio y Datos. La imagen de la derecha son las opciones que se pueden utilizar al momento de seleccionar la columna que se desea filtrar. Cuando se selecciona una columna de texto, fecha, numero aparecen opciones rápidas para generar filtro personalizado en filtro de texto, filtro de número y filtro de fecha respectivamente, lo cual facilita muchísimo al usuario final (Gráfico de la derecha), además puede buscar, ordenar y buscar por color de relleno o fondo

17. FILTRO AUTOMATICO • Si en una columna se tiene celas con el fondo o relleno de diferentes colores se puede filtrar por color de la celda como en el ejemplo de la figura También podemos buscar un conjunto de letras y facilita el trabajo de filtrar porque nos despliega aquellos datos que contengan esas palabras

18. FUNCIONES Y FORMULAS • Cuando se elabora una fórmula, al abrir paréntesis se resalto con un color • En una fórmula que esta elaborando si le falta cerrar el último paréntesis este le completa automáticamente con mensaje de error

19. FUNCIONES Y FORMULAS • Cuando Ud. digita una función el excel filtra la lista de funciones que comienzan con la combinación de letras que vaya digitando. • Además a la derecha se despliega una mini explicación acerca de la función. • También tiene el asistente de funciones en el caso de que no se acuerde como se trabaja con la mismay lo puede usar por la ficha FORMULA o por la barra de edición o formulas

20. FUNCIONES Y FORMULAS Funciones nuevas Ej.: promedio.si.conjunto(), etc.

21. NOMBRES 2 1 En Excel se asigna nombre a una celda o rango y sirve para hacer cálculos o movilizarse rápidamente dentro del libro. En la imagen 1 se puede administrar los nombres eliminar, agregar, editar En la imagen 2 se presenta para generar un nuevo nombre

22. USO DE VINCULOS Los vínculos se da cuando se usa datos de otros libros en un nuevo libro con los cuales realiza operaciones. LOCAL CCI 2014 TOTAL DE VENTAS 2014 LOCAL EL RECREO 2014 En nuestro ejemplo en el libro TOTAL DE VENTAS sumamos las venta del LOCAL CCI y del LOCAL EL RECREO, que son dos libros y como podemos ver la formula de ENERO Y CHOMPAS vemos que aparece el nombre del libro la hoja y la referencia (ESTO ES CUANDO ESTAN ABIERTOS LOS LIBROS VINCULADOS).

23. USO DE VINCULOS Los vínculos se da cuando se usa datos de otros libros en un nuevo libro con los cuales realiza operaciones. LOCAL CCI 2014 TOTAL DE VENTAS 2014 LOCAL EL RECREO 2014 Pero cuando tiene cerrado los libros vinculados,en el libro TOTAL DE VENTAS sumamos las venta del LOCAL CCI y del LOCAL EL RECREO, que son dos libros y como podemos ver la formula de ENERO Y CHOMPAS vemos que aparece el path, carpetas o directorio, nombre del libro, nombre de la hoja y la referencia o celda (ESTO ES CUANDO ESTAN CERRADOS LOS LIBROS VINCULADOS).

24. USO DE VINCULOS En la ficha DATOS del grupo CONEXIONES se tiene el comando EDITAR VINCULOS, aparece una ventana en donde podemos abrir desde aquí los libros vinculados, cambiar de libro origen (pero siempre tiene que estar en la misma posición con el mismo N° de columnas y el mismo N° de filas para poder cambiar de origen, además si no deseamos tener vinculados podemos romper los vínculos.

25. BUSCAR OBJETIVO Buscar objetivo es una herramienta de Análisis y Si en la ficha Datos. Esta herramienta nos permite determinar el valor que debería tomar una celda que esta formando parte de una fórmula, la misma que se desea que llegue a tomar un valor específico. En nuestro ejemplo la celda objetivo es la función pago que se aplico en la celda B5 que nos dio un valor de 126.97, con el buscar objetivo deseo saber cuanto de capital me pueden dar de préstamo, si se tiene la capacidad de pagar mensualmente -109. Por lo tanto la celda cambiante es B1, la misma que esta considerara en la función pago de la celda B4

26. TABLA DE DATOS TABLA DE DATOS es una herramienta de Análisis y Sí que permite generar una tabla de los posibles valores que puede llegar a tener una fórmula, en donde los valores que están la primera fila y la primera columna forman parte de la misma. Para nuestro ejemplo en la fila tenemos diferentes tasas de interés y en la columna diferentes años en la intersección de las dos está la función pago, por ficha DATOS en Tabla de Datos se aparece una ventana que nos solicita seleccionar celda de entrada (fila) en nuestro caso B2 y celda de entrada (columna) en nuestro caso B3. Puede darse el caso que Ud. necesite para mas años u otra tasa de interés, simplemente en cualquier datos de la columna y/o en cualquier dato de la fila tasa, el excel automáticamente le calcula.

27. SUBTOTALES Subtotales sirve para aplicar funciones por grupos de una columna dentro de la lista. Este se encuentra en la ficha Datos en el grupo Esquema En la pantalla hay que seleccionar la columna que deseamos que nos saque subtotales, luego la función para finalmente seleccionar la columna que deseamos que nos totalice. Con el botón Borrar Todo le deja su lista original intacta, se quitan los subtotales. Si desactivo Reemplazar subtotales actuales, puede aplicar otras funciones las cuales se van agregando a los subtotales anteriores Ej.: Suma, Promedio, contar etc. En nuestro ejemplo necesito saber cuanto se a recaudado por matriculas en cada una de las escuelas, entonces selecciono PARA CASA CAMBIO EN (Escuela), USAR FUNCION (suma), AGREGAR SUBTOTAL en la columna (valor de la matrícula)

28. SUBTOTALES Para nuestro ejemplo sacamos subtotales aplicando la función suma en la columna valor de la matrícula por cada escuela. Nos genera automáticamente esquemas o agrupaciones en este ejemplo se aplico el segundo nivel de esquemas. El nivel 3 es el mas detallado.

29. FORMATO CONDICIONAL FORMATO CONDICIONAL : es dar características al contenidos de las celdas bajo ciertas condiciones, lo que ayuda a dar un valor agregado a la presentación de la información y también como switchs que indican el estado de la información.

30. FORMATO CONDICIONAL • Existen los siguientes tipos de formato: • Resaltar reglas de celdas: son condiciones simples que nos permiten hacer lo siguiente: • Cuando el valor de la celda es mayor que un valor • Cuando el valor de la celda es menor que un valor • Cuando el valor de la celda esta entre una valor mínimo y máximo • Aquellas celdas que tengan un texto específico. • Una fecha específica • Duplicar valores resalta aquellas celdas que se repitan el contenido. • Ejemplo: Resaltar de fondo color celeste aquellas notas > 6 del 1° quimestre.

31. FORMATO CONDICIONAL • Existen los siguientes tipos de formato: • Reglas superiores e inferiores: son condiciones que aplican funciones que nos permiten hacer lo siguiente: • 10 superiores : los 10 valores mas altos de una lista • 10 superiores en porcentaje • 10 inferiores : los 10 valores mas bajos de una lista • 10 inferiores en porcentaje • Por encima del promedio de un rango seleccionado • Por debajo del promedio de un rango seleccionado • Ejemplo : Resaltar de fondo color verde y texto rojo aquellas celdas que sean mayor al promedio de todas las notas del 1° quimestre.

32. FORMATO CONDICIONAL Este tipo de formato permite rellenar de un color el fondo de la celda dependiendo del valor que contiene, cuando mayor es el valor mayor es la cantidad que cubre la celda. Ejemplo : Resaltar de fondo color celeste utilizando barra de datos en la columna notas del 1° QUIMESTRE, es como tipo gráfico. Los valores mayores tienen las celdas mas llenos de color.

33. FORMATO CONDICIONAL Este tipo de formato condicional se utiliza para ver la variación de los valores por color dando un color al valor mínimo, otro color al punto medio y otro color al valor máximo, entonces se produce un degrade entre el máximo y punto medio otro degrade entre mínimo y punto medio Ejemplo : En la columna 1° quimestre se aplica Rojo valor a la nota mínima, amarillo a la nota que esta en la mitad del máximo y mínimo, y Verde la nota con valor máximo.

34. FORMATO CONDICIONAL Este tipo de formato condicional se utiliza iconos para tres diferentes rangos de variación de los valores por un icono al rango1, otro icono al rango2 y otro icono al rango3 Ejemplo : En la columna de notas del 1° quimestre se aplica el icono circulo color verde aquellas notas >= 8, icono triángulo color amarillo nota >= 5 y nota < 8, al resto icono rombo de color rojo

35. FORMATO CONDICIONAL Dentro de las opciones de formato condicional Ud. Puede : 1.- Nueva Regla : aplica formato de forma personal 2.- Borrar Reglas : Ud. Puede eliminar las reglas de toda la hoja, o sólo de lo seleccionado 3.- Administrar reglas : Le permite agregar, editar o eliminar reglas existentes. 1 2 3

36. ESQUEMA O AGRUPACION Esquema es una herramienta de Excel que permite ocultar o mostrar filas o columnas. Estos comandos están en la ficha DATOS AGRUPA DESAGRUPA • La opción AGRUPA permite generar nivel de agrupamiento. • La opción AUTOESQUEMA, genera automáticamente esquemas cuando existe autosumas tanto en filas como en columnas • La opción DESAGRUPAR quita el nivel de agrupamiento seleccionando las filas o columna que están aplicadas esquemas. • BORRAR ESQUEMA, automáticamente elimina todos los esquemas de hoja

37. ESQUEMA Y AGRUPACION En este ejemplo está aplicado 5 niveles de esquema en las columnas, también se lo puede aplicar al mismo tiempo por filas Nivel 5 Nivel 4 Nivel 3 Nivel 2 Nivel 1

38. PLANTILLAS La herramienta plantilla nos permite generar un modelo de formato o diseño de libro, el cual puede ser utilizado las veces que se necesite. Lo que resalta de este tema es que Ud. Al momento de guardar el libro le pide lugar a guardar y el nuevo nombre, sin preocupación de que se pierda la información del libro anterior. Para generar una plantilla generamos el diseño, y luego le guardamos como en esta ventana el dato TIPO seleccionar PLANTILLA DE EXCEL, se puede guardar en cualquier parte, o dejar en la carpeta TEMPLATES O PLANTILLAS, que es donde por default grabaría excel.

39. TABLAS Y GRAFICOS DINÁMICOS Utilice la tabla dinámica para resumir, analizar, explorar y presentar datos de resumen. Esta en la ficha Insertar del grupo Tablas. En la primera pantalla podemos seleccionar los datos del mismo Excel o de una base de datos, la segunda opción más aconsejable es trabajar en una hoja nueva. Clic en Aceptar

40. TABLAS Y GRAFICOS DINÁMICOS • Se nos presenta nuevas fichas. • Una ventana a la izquierda donde se van a desplegar la información requerida. • Una ventana de campos, los rótulos de las columnas se llaman ahora campos. • El cuadrante 1 se arrastra los campos para aplicar filtros más generales. • El cuadrante 2 se despliega los datos en columnas. • El cuadrante 3 se despliega los campos por filas • El cuadrante 4 sirve para aplicar funciones a los campos numéricos. 1 2 3 4

41. TABLAS Y GRAFICOS DINÁMICOS Para nuestro ejemplo se filtra de la siguiente manera : Se desea saber solo del genero masculino se arrastra el campo GENERO cuadrante 1 Por tipo de sustentación arrastro el campo SUTENTACION al cuadrante 3 De todas las escuelas arrastro el campo ESCUELA al cuadrante 2 Se desea saber cuanto se recaudó de matrículas cuadrante 4. Al dar clic en la ficha Opciones del grupo Herramientassobre Gráfico Dinámico se genera el gráfico Se puede filtrar por columna o por fila. SE puede ordenar 1 2 3 4

42. TABLAS Y GRAFICOS DINÁMICOS • Las fichas del gráfico dinámico son diferentes a las de la tabla dinámica. • Además en la gráfica aparece botones se selección que le permite filtrar por el campo que desee. • Los datos de la tabla están vinculados con el gráfico, por lo que se cambia en la tabla y se actualiza en el gráfico y viceversa. • A la gráfica puede aplicar formato

43. CELDAS Y BLOQUEOS • De fábrica todas las celdas están bloqueadas • Para bloquear / desbloquear una celda clic derecho seleccionar Formato de celda selecciona Proteger • Seleccionar activar bloqueado o no. • Para que funcione el bloqueo se debe proteger la hoja. • Para que no se elimine una hoja se bloquea el libro. • También se puede asignar una contraseña a un rango para poder modificar

44. GRAFICOS En la ficha de Insertar podemos ya seleccionar el tipo de gráfico o seleccionar en Otros. Luego podemos seleccionar el subtipo. No es necesario seleccionar todo el rango, basta con dar un clic en una celda que contiene los datos a graficar.

45. GRAFICOS Título de gráfico R cuadrado o coeficiente de determinación Líneas de tendencia o análisis de regresión Areadel grafico Areade trazado Eje de valores, vertical o y Margen de error Título del ejede valores Líneas de división secundariasverticales Series Líneas de división principales horizontales Partes de la Gráfica Líneas de división secundarias horizontales Leyenda Eje de categoría, horizontal o X Líneas de división principales verticales Titulo del eje de categorías

46. GRAFICOS Se nos genera el gráfico, con una presentación simple del Excel. Para poder dar características a nuestro gráfico, se nos presenta la fichas Diseño, Presentación y Formato de las herramientas del gráfico. Esta herramienta se activa siempre que Ud. haya seleccionado el gráfico.

47. GRAFICOS En la ficha Diseño en el grupo Datos en el comando Seleccionar Datos nos aparece una ventana donde podemos seleccionar nuevos datos, o agregar una nueva serie. En nuestro ejemplo deseamos agregar loas datos del mes de Abril, entonces en el botón Cambiar fila/columna podemos cambiar a los meses de categorías a series.

48. FIN Suerte a todos Ud. con las nuevas herramientas de Office.