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