Excel For Masters Macros y Aplicaciones VBA

Descripción

Microsoft Excel 2010 es una aplicación que está orientada a diversas áreas del conocimiento; cada vez se incluyen nuevas características de automatización que faciliten la labor y generen lo que Microsoft denomina productividad, es decir, manejar Excel con eficiencia produciendo libros con cuadros estadísticos, gráficos, tablas dinámicas conectadas a servidores, formularios automatizados y al final crear macros con programación Visual Basic (VBA).

Este es el objetivo del libro: presentar al usuario las herramientas, comandos y funciones avanzadas que le permitan generar productividad.

Desde el primer capítulo iniciamos con la automatización usando plantillas y controles de formularios, seguimos con funciones avanzadas hasta pasar a la conexión de bases de datos, finalmente llegamos a las macros, instrucciones simplificadas para crear funciones definidas por el usuario y controles con programación Visual Basic. Cerramos el libro con programación VBA Visual Basic for Application, con procedimientos y ventanas de formularios. Cada capítulo cuenta con un laboratorio práctico para que pueda realizar casos prácticos con ejemplos y plantillas.

Índice

Capítulo 1

CREANDO SOLUCIONES A MEDIDA …………………………………………………………………………………15

Plantillas………………………………………………………………………………………………………………………….15

Crear plantillas personalizadas………………………………………………………………………………………21

Formularios y controles …………………………………………………………………………………………………….23

Controles ……………………………………………………………………………………………………………………25

Formato de controles …………………………………………………………………………………………………..26

Laboratorio de práctica……………………………………………………………………………………………………..30

Ejercicio 1: Crear plantillas personalizadas ……………………………………………………………………..30

Ejercicio 2: Crear plantillas personalizadas ……………………………………………………………………..35

Ejercicio 3: Crear formularios………………………………………………………………………………………..36

Ejercicio 4: Crear formularios………………………………………………………………………………………..40

Capítulo 2

HERRAMIENTAS DE ANÁLISIS …………………………………………………………………………………………45

Tabla de datos………………………………………………………………………………………………………………….45

Tablas de datos de una entrada …………………………………………………………………………………….45

Tabla de datos de dos variables……………………………………………………………………………………..45

Cálculos de las tablas de datos………………………………………………………………………………………46

Crear una tabla de datos de una variable …………………………………………………………………………….46

Agregar una fórmula a una tabla de datos de una variable……………………………………………….47

Crear una tabla de datos de dos variables……………………………………………………………………………47

Acelerar los cálculos en una hoja de cálculo con tablas de datos………………………………………..48

Borrar una tabla de datos …………………………………………………………………………………………….49

Creación de tablas con expresiones o funciones de tipo XY ……………………………………………………49

Buscar Objetivo………………………………………………………………………………………………………………..50

Uso de Buscar Objetivo con funciones…………………………………………………………………………….50

Uso de buscar objetivo en fórmulas ……………………………………………………………………………….52

Escenarios……………………………………………………………………………………………………………………….53

Crear escenarios …………………………………………………………………………………………………………53

Informes de resumen de escenario…………………………………………………………………………………54

Crear un escenario……………………………………………………………………………………………………….54

Mostrar un escenario …………………………………………………………………………………………………..55

Crear un informe resumen de escenario………………………………………………………………………….55

Informe de escenario……………………………………………………………………………………………………55

Herramienta Solver…………………………………………………………………………………………………………..56

Cargar el complemento Solver ………………………………………………………………………………………56

Ejemplo de una evaluación de Solver……………………………………………………………………………..57

Definir un modelo de optimización…………………………………………………………………………………58

Celda objetivo……………………………………………………………………………………………………………..58

Celdas cambiantes……………………………………………………………………………………………………….58

Restricciones……………………………………………………………………………………………………………….58

Defina y solucione un problema …………………………………………………………………………………….58

Agregar una restricción ……………………………………………………………………………………………….59

Laboratorio de práctica……………………………………………………………………………………………………..61

Ejercicio 1: Creación de Tabla de 2 entradas……………………………………………………………………61

Ejercicio 2: Creación de Tabla en una expresión ……………………………………………………………….62

Ejercicio 3: Doblar Capital …………………………………………………………………………………………….63

Ejercicio 4: Solver Método Simplex…………………………………………………………………………………64

Capítulo 3

HERRAMIENTAS DE AUDITORÍA ……………………………………………………………………………………..67

Evaluar fórmulas ………………………………………………………………………………………………………………67

Buscador de Rangos…………………………………………………………………………………………………….67

Evaluar fórmulas …………………………………………………………………………………………………………68

Comentarios…………………………………………………………………………………………………………………….70

Insertar comentarios ……………………………………………………………………………………………………70

Revisar sus comentarios……………………………………………………………………………………………….71

Editar un comentario……………………………………………………………………………………………………72

Eliminar un comentario ………………………………………………………………………………………………..72

Imprimir los comentarios………………………………………………………………………………………………73

Auditoría de fórmulas……………………………………………………………………………………………………….74

Rastrear precedentes……………………………………………………………………………………………………74

Rastrear dependientes………………………………………………………………………………………………….75

Quitar flechas……………………………………………………………………………………………………………..75

Gestión de Errores ……………………………………………………………………………………………………………75

Valores de error…………………………………………………………………………………………………………..75

Autocorrección de errores…………………………………………………………………………………………….76

Corrección de errores …………………………………………………………………………………………………..76

Rastrear error……………………………………………………………………………………………………………..77

Activar o desactivar reglas de revisión de errores…………………………………………………………….77

Laboratorio de práctica……………………………………………………………………………………………………..78

Ejercicio 1: Evaluar fórmulas …………………………………………………………………………………………78

Ejercicio 2: Evaluar fórmulas …………………………………………………………………………………………79

Ejercicio 3: Comentarios……………………………………………………………………………………………….80

Ejercicio 4: Auditoría de fórmulas…………………………………………………………………………………..82

Ejercicio 5: Comprobar errores………………………………………………………………………………………84

Capítulo 4

COLABORAR CON OTROS USUARIOS ………………………………………………………………………………87

Protección de rangos, hojas y libros…………………………………………………………………………………….87

Protección del libro………………………………………………………………………………………………………87

Marcar como final (Libro sólo de lectura) ……………………………………………………………………….88

Cifrar con contraseña (Contraseña para el libro) ……………………………………………………………..89

Proteger hoja actual (Proteger la hoja)…………………………………………………………………………..90

Proteger la estructura del libro (Proteger la estructura hoja y ventanas)…………………………….93

Restringir permisos por personas (Permisos a personas) …………………………………………………..94

Compartir Libro …………………………………………………………………………………………………………..97

Permitir que los usuarios modifiquen rangos por contraseña…………………………………………….98

Laboratorio de práctica……………………………………………………………………………………………………..99

Ejercicio 1: Proteger Hoja ……………………………………………………………………………………………..99

Ejercicio 2: Desbloquear los Sueldos……………………………………………………………………………….100

Ejercicio 3: Ocultar las fórmulas…………………………………………………………………………………….101

Ejercicio 4: Proteger con contraseña ………………………………………………………………………………101

Capítulo 5

ACCESO A DATOS ……………………………………………………………………………………………………………103

Utilizar Microsoft Query…………………………………………………………………………………………………….103

Origen de Datos…………………………………………………………………………………………………………..103

Obtener más información sobre Microsoft Query …………………………………………………………….103

Tipos de bases de datos a los que se puede tener acceso ………………………………………………….104

Microsoft SQL Server Analysis Services……………………………………………………………………………104

Proveedor OLAP…………………………………………………………………………………………………………..104

Controladores ODBC ………………………………………………………………………………………………………..104

Seleccionar datos de una base de datos………………………………………………………………………….104

Conectar con un origen de datos………………………………………………………………………………………..105

¿Qué es un origen de datos? …………………………………………………………………………………………105

Definir un origen de datos mediante Microsoft Query …………………………………………………………..105

Asistente para consultas para definir una consulta ……………………………………………………………….108

Asistente para consultas……………………………………………………………………………………………….108

Seleccionar datos específicos de un campo……………………………………………………………………..109

Abrir una consulta guardada desde Excel………………………………………………………………………..109

Trabajar con los datos en Excel …………………………………………………………………………………………..109

Aplicar formato a los datos recuperados……………………………………………………………………………..110

Actualizar datos externos…………………………………………………………………………………………………..111

Laboratorio de práctica……………………………………………………………………………………………………..112

Ejercicio 1: Acceso a una tabla de Access………………………………………………………………………..112

Ejercicio 2: Actualizar los datos de la tabla ……………………………………………………………………..113

Ejercicio 3: Consulta de campos específicos…………………………………………………………………….114

Ejercicio 4: Crear una tabla dinámica……………………………………………………………………………..116

Ejercicio 5: Crear un gráfico de la tabla dinámica …………………………………………………………….118

Capítulo 6

BASE DE DATOS ………………………………………………………………………………………………………………119

Tablas y Filtros………………………………………………………………………………………………………………….119

Insertar una tabla……………………………………………………………………………………………………………..119

Filtros avanzados………………………………………………………………………………………………………………120

Criterios en una columna………………………………………………………………………………………………121

Criterios en varias columnas………………………………………………………………………………………….121

Criterios en varias columnas en las que debe cumplirse alguno de los criterios ……………………121

Criterios en los que cada uno de los conjuntos incluye criterios para varias columnas…………..122

Criterios en los que cada conjunto incluye criterios para una columna………………………………..122

Criterios para buscar valores de texto que incluyen algunos caracteres pero no otros…………..122

Criterios creados como resultado de una fórmula…………………………………………………………….123

Filtrar buscando valores superiores a la media de todos los valores incluidos en el rango……..123

Funciones de base de datos……………………………………………………………………………………………….124

Ejemplo de la Función BDCONTAR………………………………………………………………………………….124

Ejemplo de la Función BDSUMA …………………………………………………………………………………….124

Lista de funciones de base de datos……………………………………………………………………………….125

Laboratorio de práctica……………………………………………………………………………………………………..126

Ejercicio 1: Convertir una lista de rango en una Tabla……………………………………………………….126

Ejercicio 2: Activar Subtotales ……………………………………………………………………………………….127

Ejercicio 3: Uso de Referencias especiales de Celda ………………………………………………………….128

Ejercicio 4: Filtros Avanzados ………………………………………………………………………………………..129

Ejercicio 5: Base de Datos……………………………………………………………………………………………..130

Capítulo 7

TABLAS Y GRÁFICOS DINÁMICOS ……………………………………………………………………………………131

Tablas dinámicas………………………………………………………………………………………………………………131

Elementos de una tabla dinámica ………………………………………………………………………………….133

Insertar una Tabla Dinámica……………………………………………………………………………………………….134

Distribuir los campos en la tabla dinámica………………………………………………………………………135

Estilos de tabla y formatos de números…………………………………………………………………………..150

Gráficos dinámicos………………………………………………………………………………………………………152

Laboratorio de práctica……………………………………………………………………………………………………..155

Ejercicio 1: Crear tablas dinámicas simples……………………………………………………………………..155

Ejercicio 2: Crear tablas dinámicas simples……………………………………………………………………..156

Ejercicio 3: Crear tablas dinámicas simples……………………………………………………………………..156

Ejercicio 4: Aplicar formatos de números………………………………………………………………………..157

Ejercicio 5: Agrupar fechas……………………………………………………………………………………………157

Ejercicio 6: Expandir y contraer……………………………………………………………………………………..159

Ejercicio 7: Modificar la distribución de las tablas…………………………………………………………….160

Ejercicio 8: Añadiendo segmentación de datos………………………………………………………………..161

Ejercicio 9: Añadiendo columna de porcentajes……………………………………………………………….163

Ejercicio 9: Insertar gráficos ………………………………………………………………………………………….164

Ejercicio 10: Insertar gráficos………………………………………………………………………………………..167

Capítulo 8

MACROS…………………………………………………………………………………………………………………………169

Macros……………………………………………………………………………………………………………………………169

La ficha Programador…………………………………………………………………………………………………..170

Ámbito de las macros…………………………………………………………………………………………………..171

Grabar una macro ……………………………………………………………………………………………………….171

Ejecutar la macro ………………………………………………………………………………………………………..172

Guardar un libro con macros…………………………………………………………………………………………173

Referencias de celdas en una macro……………………………………………………………………………………174

Referencia absoluta……………………………………………………………………………………………………..174

Referencia relativa……………………………………………………………………………………………………….174

Cambiar las referencias al grabar la macro …………………………………………………………………….175

Ver la macro en su editor………………………………………………………………………………………………175

Asociar las macros a objetos………………………………………………………………………………………………176

Asociar macros a autoformas o imágenes ………………………………………………………………………176

Asociar macros a controles de formulario……………………………………………………………………….177

Asociar macros a comandos de la barra de acceso rápido…………………………………………………177

Crear Fichas, Grupos y Comandos para Macros……………………………………………………………….179

Laboratorio de práctica……………………………………………………………………………………………………..182

Ejercicio 1: Crear una macro sencilla de ámbito general……………………………………………………182

Ejercicio 2: Crear una macro sencilla de ámbito general……………………………………………………183

Ejercicio 3: Crear una macro para abrir una plantilla………………………………………………………..184

Ejercicio 4: Crear una macro para abrir una plantilla………………………………………………………..184

Ejercicio 5: Asignar macros a comandos…………………………………………………………………………185

Ejercicio 6: Crear una macro sencilla de ámbito local ……………………………………………………….187

Ejercicio 7: Crear una macro sencilla de ámbito local ……………………………………………………….188

Ejercicio 8: Asignar macros a objetos……………………………………………………………………………..189

Ejercicio 9: Asignar macros a Inicio rápido………………………………………………………………………189

Ejercicio 10: Grabar un libro habilitado para macros………………………………………………………..190

Capítulo 9

MACROS Y APLICACIONES………………………………………………………………………………………………191

Laboratorio de práctica……………………………………………………………………………………………………..191

Ejercicio 1: Macros y gráficos………………………………………………………………………………………..191

Ejercicio 2: Macros y subtotales…………………………………………………………………………………….193

Ejercicio 3: Macros y Filtros avanzados…………………………………………………………………………..198

Capítulo 10

FUNCIONES DEFINIDAS POR EL USUARIO ……………………………………………………………………….203

El Editor de VBA ……………………………………………………………………………………………………………….203

Ingresar al editor…………………………………………………………………………………………………………203

Elementos del editor…………………………………………………………………………………………………….204

Administrar módulos……………………………………………………………………………………………………207

Personalizar el editor……………………………………………………………………………………………………209

Creación de funciones personalizadas…………………………………………………………………………………210

Crear una función personalizada……………………………………………………………………………………210

Sintaxis ………………………………………………………………………………………………………………………211

Empleando más de un argumento …………………………………………………………………………………213

Emplear rangos como argumentos ………………………………………………………………………………..214

Usar argumentos opcionales…………………………………………………………………………………………215

Agregar descripción a las funciones……………………………………………………………………………….217

Crear categorías y asignar categorías a funciones……………………………………………………………218

Laboratorio de práctica……………………………………………………………………………………………………..220

Ejercicio 1: Reconocer el editor de VBA …………………………………………………………………………..220

Ejercicio 2: Agregar objetos al proyecto………………………………………………………………………….221

Ejercicio 3: Grabar el libro con los objetos……………………………………………………………………….222

Ejercicio 4: Crear funciones sencillas………………………………………………………………………………222

Ejercicio 5: Crear funciones con 2 o más argumentos ………………………………………………………224

Ejercicio 6: Crear funciones con rangos…………………………………………………………………………..226

Ejercicio 7: Crear funciones con argumentos opcionales……………………………………………………228

Ejercicio 8: Organizar sus funciones en categorías……………………………………………………………229

Capítulo 11

COMPLEMENTOS…………………………………………………………………………………………………………231

Complementos…………………………………………………………………………………………………………………231

Revisar los complementos disponibles ……………………………………………………………………………231

Cargar complementos………………………………………………………………………………………………….232

Crear complementos para funciones UDF ……………………………………………………………………….233

Distribuir un complemento……………………………………………………………………………………………234

Laboratorio de práctica……………………………………………………………………………………………………..235

Ejercicio 1: Crear complementos ……………………………………………………………………………………235

Ejercicio 2: Cargando el complemento ……………………………………………………………………………236

Ejercicio 3: Revisando el código……………………………………………………………………………………..237

Ejercicio 4: Distribuir complementos………………………………………………………………………………238

Capítulo 12

FUNDAMENTOS DE PROGRAMACIÓN …………………………………………………………………………….239

Insertando un Módulo Estándar…………………………………………………………………………………………239

Procedimientos ……………………………………………………………………………………………………………….239

Sintaxis ………………………………………………………………………………………………………………………239

Ejecutar un procedimiento ……………………………………………………………………………………………241

Funciones………………………………………………………………………………………………………………………..241

Sintaxis ………………………………………………………………………………………………………………………242

Pasando argumentos a procedimientos …………………………………………………………………………242

Cuadros de mensajes………………………………………………………………………………………………………..244

MSGBOX …………………………………………………………………………………………………………………….244

INPUTBOX…………………………………………………………………………………………………………………..247

Variables, constantes y tipos de datos…………………………………………………………………………………248