MICROSOFT OFFICE EXCEL 2013

1. MICROSOFT OFFICE EXCEL 2013 AVANZADO Quito, 10 de Enero del 2014 Antonio Correa Email : support@consultingcorrea.com

2. AGENDA • Antecedentes Cuando Microsoft lanzó su primera hoja electrónica pensó básicamente en fórmulas y datos estadísticos, pero por las necesidades de los usuarios ha mejorado haciéndolo una herramienta eficiente y eficaz. • Objetivo del Curso Es para que ejecutivos y personal en organizaciones y empresas, al momento de migrar a Microsoft Office 2007 o 2010, pueda utilizar la nueva interfaz facilitándole su trabajo.

3. AGENDA • Microsoft Excel 2013 • Validación de datos • Auditoría de fórmulas • Controles de formularios Active x • Macros. • Automáticas • Semiautomáticas • Manuales • Visual Basic • Estructuras de programación • If • Do while • For • With

4. VALIDACIÓN DE DATOS • Evita la entrada de datos no válidos en una o varias celdas. • Existen las siguientes validaciones: • Número entero • Número decimal • Listas • Fecha • Hora • Longitud de texto • Personalizado

5. VALIDACIÓN DE DATOS En este ejemplo en PERMITIR hemos seleccionado Número entero, en datos se escoge los operadores de relación en este ejemplo entre que permita valores del 1 al 100. Nota Excel los maneja por separados los enteros y los decimales

6. VALIDACIÓN DE DATOS Se puede personalizar el mensaje de entrada y el mensaje de error. Cuando doy clic en A1 automáticamente se despliega el mensaje de entrada. Cuando se digita un valor errado se despliega una nueva ventana con el error, en este mensaje existen tres estilos Detener, Advertencia e información El botón Borrar todos elimina las validaciones de las celdas seleccionadas

7. AUDITORÍA DE FORMULAS Rastrear Precedentes Rastrear Dependientes

8. AUDITORÍA DE FORMULAS Quitas flechas quita las flechas trazadas por Rastrear Precedentes y Rastrear Dependientes Mostrar Formulas Muestra la formula en cada celda en vez del valor resultante

9. AUDITORÍA DE FORMULAS Rastrear Error Traza flechas e indica en donde comienza el error, en este ejemplo seleccione F10 y seleccione Rastrear error las flechas rojas indican las celdas con error. Comprobación de errores Permite ir evaluando la formula de la celda donde se generó el error

10. AUDITORÍA DE FORMULAS Ventana Inspección Supervisa los valores de determinadas celdas al mismo tiempo que se realizan cambios en la hoja. En al opción Agregar Inspección agrega las celda cuyos valores se necesita inspeccionar Evaluar Fórmula Las celdas que contiene fórmula evalúa cada una de las partes independientemente

11. CONTROLES DE FORMULARIOS Los controles de formularios son objetos que permiten realizar formularios. Para poder utilizarlos se necesita desplegar la ficha del PROGRAMADOR en la cinta de opciones.

12. CONTROLES DE FORMULARIOS Los controles de formularios o controles active x son objetos que permiten realizar formularios. Para poder utilizarlos se necesita desplegar la ficha del PROGRAMADOR en la cinta de opciones. 1 2 3

13. CONTROLES DE FORMULARIOS Cuadro combinado Estos controles son para hacer formularios simples y rápidos. En los controles de formulario selecciono cuadro combinado y dibujo. Con clic derecho escojo formato de control luego en Rango de Entrada seleccionar la lista de datos, en Vincular con la celda seleccionar la celda que identifica que provincia seleccionó en este ejemplo como es Guayas en la celda c1 se puso el valor 2 es identificado por número.

14. CONTROLES DE FORMULARIOS A excepción del control etiqueta y cuadro de grupo todos se vinculan con una celda para saber que opción seleccionó El control Cuadro de grupo sirve para separar los botones de opción como por ejemplo: Género, Estado Civil etc.

15. CONTROLES DE ACTIVE X Para dar características a un control active X, hay que utilizar la ventana de propiedades, todos los controles se manejan de la misma manera Cuadro combinado

16. CONTROLES ACTIVE X Todos los controles Active X son los que permiten ser utilizados dentro de macros o aplicaciones dentro de visual basic. La propiedad name sirve para hacer mención a estos controles dentro de macros.

17. MACROS INTRODUCCIÓN: Macro es un conjunto de comandos y funciones que se almacenan en un módulo de Microsoft Visual Basic, se ejecuta siempre que sea necesario realizar una tarea. FUNCION: Fórmula ya escrita que toma un valor o valores (parámetros), realiza un operación y devuelve resultados. MODULO: Colección de declaraciones, instrucciones y procedimientos almacenados como una unidad con nombre. VISUAL BASIC: Versión visual de lenguaje de programación básica de alto nivel. Microsoft a desarrollado Visual Basic para crear aplicaciones en windows. EDITOR DE VISUAL BASIC: Entorno en el que se pueden modificar las macros que se hayan grabado y escribir nuevas macros y progrmas Visual Basic para aplicaciones. NOTA: UN LIBRO QUE CONTENGA MACROS SIEMPRE TIENE QUE SER GRABADO COMO TIPO LIBRO DE EXCEL HABILITADO PARA MACROS

18. MACROS • Existen tres tipos de macros: • Automáticas: Son aquellas que se generan utilizando la grabadora de macros de Excel • Semiautomáticas: aquellas que utilizamos la grabadora de macros de Excel y luego modificamos en el ambiente Visual Basic. • Manuales: Son aquellas que generamos totalmente en el ambiente Visual Basic y desde este ambiente ejecutamos. • Nota: En los dos últimos casos podemos ejecutar paso a paso cada instrucción de una macro.

19. MACROS Las macros se almacenan en este ejemplo en la carpeta Módulos en modulo1, modulo2, modulondentro de Visual Basic y estas pueden ser ejecutadas desde Excel o dentro de Visual Basic

20. MACROS Para grabar un macro hay dos opciones Hay que recalcar que cuando se usa grabación de macros funciona para aquellas tareas que van a ser fijas es decir en las mismas posiciones, los mismos rangos, las formulas de tal manera que se ejecutan las veces que sean y siempre utilizarán los mismas celdas, rangos y formulas.

21. MACROS • Cuando vamos e empezar a grabar una macro nos pide los siguientes datos: • Nombre de la macro, este no puede tener espacios en blanco • Teclas abreviadas como por ejemplo CTRL+k en este caso se utiliza la k minúscula o CTRL+SHIFT+K mientras que en este caso se utiliza la k mayúscula • Se escoge donde este presente la macro en el libro actual, o en un nuevo libro, o en el libro personal propio de Excel, si se graba en este último la macro puede ser utilizada en cualquier instancia y sobre cualquier libro. • Y por último se puede poner una descripción para indicar que tare es lo que realiza la macro, el autor, fecha etc.

22. MACROS Para detener la grabación de la macro existen tres opciones

23. MACROS Generar manualmente o dar mantenimiento a una macro en visual basic Dentro de Visual Basic en Modulo2 se creo la macro llamada Saludo_de_bienvenida que inicia con Sub Saludo_de_bienvenida() y termina con End Sub, todas las instrucciones que se necesiten que se ejecute se digitaran dentro del SUB y END SUB. Para este ejemplo hemos puesto comentarios con apostrofe se transforma en color verde éstas, visual basic no los toma en cuenta Utilizamos la función msgbox para que se despliegue una venta que dice Bienvenido a trabajar con Excel

24. MACROS Para ejecutar una macro se lo puede hacer desde Excel En la ficha programador en el grupo codigo hay el comando macros al dar clic se abre la ventana de la derecha En la ficha vista en el grupo macros se escoge la opción ver macros y se abre la ventana de la derecha.

25. MACROS Para ejecutar una macro se lo puede hacer desde Visual Basic También se puede ejecutar presionando la tecla F5 o par chequear paso a paso la tecla F8. Hay que recalcar que para ejecutar la macro de nuestro ejemplo tenemos que haber dado clic entre SUB y ENS SUB.

26. MACROS Para ejecutar una macro se lo puede hacer desde Excel

27. MACROS OBJETOS DE EXCEL EN VISUAL BASIC • WORKBOOKS Sirve para manipular libros del excel • Workbooks ("nombre.xlsx") Selecciona el libro pero este debe estar abierto • Activeworkbook Hace mención al libro activo • Activeworkbook.name devuelve el nombre del libro activo • Workbooks.pathdevuelve el path (directorio) donde se encuentra el libro activo • Workbooks.openfilename("path\nombre.xlsx") Abre un libro de excel • Workbooks.addAgrega un nuevo libro • Workbooks ("nombre.xlsx").activate Selecciona un libro que esta abierto • Workbooks ("nombre.xlsx").closesavechanges:=true Cierra un libro guardando los cambios • Workbooks ("nombre.xlsx").closesavechanges:=false Cierra un libro sin guardar • Workbooks.save Guarda el libro con el nombre actual • Workbooks ("nombre.xlsx").saveasfilename("path\nuevo nombre.xlsx") Guarda el libro activo con nuevo nombre

28. MACROS OBJETOS DE EXCEL EN VISUAL BASIC • WORKSHEETS Sirve para manipular hojas de un libro del excel • Worksheets ("nombre hoja") Selecciona la hoja con dicho nombre. • Activesheet Hace mención a la hoja activa • Worksheets.name devuelve el nombre de la hoja activa • Worksheets.addagrega una nueva hoja • Worksheets ("nombre de la hoja").select Selecciona la hoja con dicho nombre • Worksheets.add.name = "nombre de la hoja" Crea una nueva hoja con un nombre específico

29. OBJETOS DE EXCEL EN VISUAL BASIC MACROS • RANGE Sirve para manipular rangos de celdas • Range ("c5").select Selecciona la celda c5 • Range ("c5:c50").select Selecciona el rango c5 a c50 • Range("b:b").selectSelecciona la columna B • Range ("5:5").select Selecciona la fila 5 • Range("rango").clearcontents Elimina el contenido del rango especificado • Range ("c5").value = 50 Asigna el valor de 50 en la celda c5 • Range ("c5").formula = "=sum(d5:d100)" Este permite aplicar una formula de sumatoria del rango d5 hasta d100 en la celda c5. • Copiar un rango • Range ("c5:c50").copy / cutSelecciono el rango que deseo copiar o corta • Range("k5").select • Activesheet.paste CELLS(FILA,COLUMNA) Objeto que también permite manipular rangos sino que en este caso indica las coordenadas de la celda que se desea trabajar en donde fila y columna son números Ejemplo cells(3,3).value = 45 En este ejemplo asigno el valor de 45 a la celda c3

30. MACROS PROGRAMACION DE CODIGO EN VISUAL BASIC • ESTRUCTURAS DE CONTROL DE FLUJO If condición then Código so condición es cierta Else Código so condición es falsa Endif For variable = valor inicial to valor final step valor a incrementar Código Exitfor(esta instrucción obliga a terminar el lazo o bucle) Código Next variable Do While condición Código mientras condición sea cierta Exit do(esta instrucción obliga a terminar el lazo o bucle) Código mientras condición sea cierta loop

31. MACROS PROGRAMACION DE CODIGO EN VISUAL BASIC • ESTRUCTURAS DE CONTROL DE FLUJO Select case variable case valor1, valor3 Código si variable = valor1 o valor3 case valor2 Código si variable = valor2 case else Código si variable no es igual a ninguna de las anteriores Endselect Withobjeto .propiedad = valor Withsubobjeto .propiedad = valor Endwith Endwith

32. MACROS • FUNCIONES msgbox Muestra un mensaje en un cuadro de diálogo, espera a que el usuario haga clic en un botón y devuelve un tipo Integer correspondiente al botón elegido por el usuario. Sintaxis MsgBox(prompt[, buttons][, title][, helpfile, context]) La sintaxis de la función MsgBox consta de estos argumentos con nombre: Parte Descripción prompt Requerido. Expresión de cadena que representa el prompt en el cuadro de diálogo. La longitud máxima de prompt es de aproximadamente 1024 caracteres, según el ancho de los caracteres utilizados. Si prompt consta de más de una línea, puede separarlos utilizando un carácter de retorno de carro (Chr(13)) o un carácter de avance de línea (Chr(10)), o una combinación de caracteres de retorno de carro – avance de línea (Chr(13) y Chr(10)) entre cada línea y la siguiente. buttons Opcional. Expresión numérica que corresponde a la suma de los valores que especifican el número y el tipo de los botones que se pretenden mostrar, el estilo de icono que se va a utilizar, la identidad del botón predeterminado y la modalidad del cuadro de mensajes. Si se omite este argumento, el valor predeterminado para buttons es 0.

33. MACROS • FUNCIONES msgbox title Opcional. Expresión de cadena que se muestra en la barra de título del cuadro de diálogo. Si se omite title, en la barra de título se coloca el nombre de la aplicación. Valores El argumento buttons tiene estos valores: Constante Valor Descripción VbOKOnly 0 Muestra solamente el botón Aceptar. VbOKCancel 1 Muestra los botones Aceptar y Cancelar. VbAbortRetryIgnore 2 Muestra los botones Anular, Reintentar e Ignorar. VbYesNoCancel 3 Muestra los botones Sí, No y Cancelar. VbYesNo 4 Muestra los botones Sí y No. VbRetryCancel 5 Muestra los botones Reintentar y Cancelar. VbCritical 16 Muestra el icono de mensaje crítico. VbQuestion 32 Muestra el icono de pregunta de advertencia. VbExclamation 48 Muestra el icono de mensaje de advertencia. VbInformation 64 Muestra el icono de mensaje de información. VbDefaultButton1 0 El primer botón es el predeterminado. VbDefaultButton2 256 El segundo botón es el predeterminado.

34. MACROS PROGRAMACION DE CODIGO EN VISUAL BASIC • FUNCIONES Msgbox VbDefaultButton3 512 El tercer botón es el predeterminado. VbDefaultButton4 768 El cuarto botón es el predeterminado. VbApplicationModal 0 Aplicación modal; el usuario debe responder al cuadro de mensajes antes de poder seguir trabajando en la aplicación actual. VbSystemModal 4096 Sistema modal; se suspenden todas las aplicaciones hasta que el usuario responda al cuadro de mensajes. VbMsgBoxHelpButton 16384 Agrega el botón Ayuda al cuadro de mensaje. VbMsgBoxSetForeground 65536 Especifica la ventana del cuadro de mensaje como la ventana de primer plano. VbMsgBoxRight 524288 El texto se alínea a la derecha. VbMsgBoxRtlReading 1048576 Especifica que el texto debe aparecer para ser leído de derecha a izquierda en sistemas hebreo y árabe.

35. MACROS • FUNCIONES msgbox El primer grupo de valores (0 a 5) describe el número y el tipo de los botones mostrados en el cuadro de diálogo; el segundo grupo (16, 32, 48, 64) describe el estilo del icono, el tercer grupo (0, 256, 512) determina el botón predeterminado y el cuarto grupo (0, 4096) determina la modalidad del cuadro de mensajes. Cuando se suman números para obtener el valor final del argumento buttons, se utiliza solamente un número de cada grupo. Nota Estas constantes las especifica Visual Basic forApplications. Por tanto, el nombre de las mismas puede utilizarse en cualquier lugar del código en vez de sus valores reales. Valores devueltos Constante Valor Descripción vbOK 1 Aceptar vbCancel 2 Cancelar vbAbort 3 Anular vbRetry 4 Reintentar vbIgnore 5 Ignorar vbYes 6 Sí vbNo 7 No

36. MACROS • FUNCIONES Inputbox Muestra un cuadro de diálogo para que el usuario escriba información. Devuelve la información escrita en el cuadro de diálogo. Sintaxis expresión.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type) expresión Variable que representa un objeto Application. Parámetros Nombre Obligatorio/Opcional Tipo de datos Descripción Prompt Obligatorio String Mensaje que se mostrará en el cuadro de diálogo. Puede ser una cadena, un número, una fecha o un valor Boolean (Microsoft Excel automáticamente convierte el valor en una cadena String antes de mostrarla). Title Opcional Variant El título del cuadro de entrada. Si este argumento se omite, el título predeterminado será "Entrada". Default Opcional Variant Especifica un valor que aparecerá en el cuadro de texto cuando se muestre inicialmente el cuadro de diálogo. Si este argumento se omite, el cuadro de texto permanecerá vacío. Este valor puede ser un objeto Range. Left Opcional Variant Especifica la posición X del cuadro de diálogo con respecto a la esquina superior izquierda de la pantalla, en puntos (punto: unidad de medida que hace referencia al alto de un carácter impreso. Un punto es igual a 1/72 de pulgada, o aproximadamente 1/28 de centímetro.). Top Opcional Variant Especifica la posición Y del cuadro de diálogo con respecto a la esquina superior izquierda de la pantalla, en puntos

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