1

Contenido

Introducción ............................................................................................................................................................XV

Desde dónde Iniciar ......................................................................................................................................... XIX

CAPITULO 1: Nivelación de conceptos y técnicas de trabajo con Excel

Fórmulas con referencias a celdas ...................................................................................................................2 Análisis de fórmulas copiadas en sentido vertical..................................................................................2 Referencias a filas constantes ...............................................................................................................2 Análisis de fórmulas copiadas en sentido horizontal .............................................................................3 Referencias a columnas constantes ......................................................................................................3 Referencias a celdas constantes ...........................................................................................................3Crear y emplear nombres de celdas y rangos ..................................................................................................5 Asignar nombres a celdas ...................................................................................................................5 Utilizar los nombres de celdas para elaborar fórmulas ..........................................................................6 Conceptos para la formulación con nombres a rangos .........................................................................6 Formulación entre celdas y rangos .......................................................................................................6Validación de datos en celdas .........................................................................................................................7 Cómo asignar la validación de datos a una celda .................................................................................7 ¿Cómo ingresar datos a celdas validadas? ............................................................................................8Análisis de las funciones lógicas SI, Y y O ........................................................................................................8 Análisis lógico de la función SI condicional ..........................................................................................8 Función SI condicional simple ..............................................................................................................8 Función SI condicional anidada ..........................................................................................................10 Funciones Y y O .................................................................................................................................12 La función BUSCARV reemplaza la función SI condicional ..................................................................13Manejo óptimo de la información relacionando tablas ..................................................................................15 Definición de tablas ...........................................................................................................................16 Relación de tablas .............................................................................................................................17 Diagramas de relación de tablas ........................................................................................................19 Relacionar tablas en Excel ..................................................................................................................20 Análisis de las relaciones ....................................................................................................................22 Un caso de relación “muchos a muchos” ...........................................................................................22 Algunas características de los manejadores de Bases de Datos ............................................................23 Cuando se requiere que un campo llave represente un rango de valores .............................................23Sistemas de información ..............................................................................................................................24 Fuentes de información de bases de datos .........................................................................................24 Información obtenida mediante procesos internos en Excel ....................................................................24 Información obtenida desde fuentes de Datos Externas .......................................................................26Estructure bases de datos... ¡A su medida! ...................................................................................................27 Reconocimiento de la estructura de una base de datos ......................................................................27Creación de informes de referencias cruzadas o tablas dinámicas ..................................................................27 Conceptos previos a la elaboración de tablas dinámicas .....................................................................28

2

Pasos a seguir para la elaboración de tablas dinámicas .......................................................................30 Sugerencias para el manejo de tablas bases de datos .........................................................................32 Valide los datos de entrada ...........................................................................................................32 Adicione campos calculados ...........................................................................................................33 Algo más sobre las estructuras de tablas bases de datos ....................................................................33Creación de Gráficos Dinámicos....................................................................................................................36

CAPITULO 2: Introducción a las Macros y a Excel Visual Basic

Las Macros en Excel......................................................................................................................................40 ¿Qué sucede al abrir un archivo con Macros? ....................................................................................40 Las Macros como listados y listados de instrucciones ..........................................................................41 Tres sugerencias para crear un buen listado de instrucciones ..............................................................42 Un primer ejemplo ......................................................................................................................42 Un segundo ejemplo .....................................................................................................................43 Antes de grabar la Macro, ejecute una prueba de escritorio ...............................................................43 Grabe sus primeras Macros ................................................................................................................43 Grabación de la Macro del primer ejemplo .......................................................................................44 ¿Qué sucede al ingresar información a celdas?....................................................................................45Un primer acercamiento a la interfaz de Excel Visual Basic ............................................................................46 Dónde quedan grabadas las Macros? ................................................................................................46 La ventana de Macros .................................................................................................................46 La ventana de Visual Basic .........................................................................................................47 Principales herramientas del editor de Visual Basic .............................................................................47 Otros detalles de la interfaz Visual Basic .............................................................................................48 Ejecutar las Macros Paso a Paso .........................................................................................................49 El Modo Interrupción ........................................................................................................................50El concepto de la programación dirigida a objetos ........................................................................................50Un modelo general de objetos .....................................................................................................................50 Objetos, propiedades y métodos........................................................................................................51 Objetos únicos ............................................................................................................................51 Objetos de grupo o colecciones .........................................................................................................51 La jerarquía de los objetos .................................................................................................................52El modelo de objetos en Excel ......................................................................................................................52 Objetos, propiedades y métodos........................................................................................................53 Planteamiento de un ejemplo..........................................................................................................53 Objetos únicos ............................................................................................................................54 Objetos únicos del grupo de objetos que se refieren a celdas o rangos ........................................................54 Objetos de grupo ..............................................................................................................................55 Objetos de grupo que se refieren a hojas ............................................................................................56 Objetos de grupo que se refieren a celdas ...........................................................................................56 Uso de la jerarquía de objetos de Excel ..............................................................................................57 Referencia implícita al libro activo ..................................................................................................57 Referencia implícita a la hoja de cálculo ...........................................................................................57 Algo más sobre propiedades .............................................................................................................58 Asignación de valores a propiedades .................................................................................................58 Tipos de valores que se asignan a las propiedades de objetos Excel ....................................................59 Algo más sobre métodos ...................................................................................................................59 Sintaxis para asignar valores a argumentos de métodos .....................................................................60Objetos que refieren a celdas y sus diferentes formas de expresarse ..............................................................61 Reconocimiento de los objetos de Excel que refieren una celda o rango .............................................61 Los objetos ActiveCell, Selection y Range (“...”) ...............................................................................61 El objeto CurrentRegion (región actual) ..........................................................................................62 Reconocimiento de los objetos de Excel que refieren a una celda (o rango) a partir de la celda activa ...................................................................................................................63 Referencia a una celda (o rango) que se desvía un número conocido de filas y/o columnas a partir de la celda activa ..............................................................................................................63 Referencia a la última celda (de la derecha, de arriba, de la Izquierda o de abajo) a partir de la celda activa ..............................................................................................................64Tabla de resumen de los principales objetos empleados en Excel Visual Basic .................................................65

3

Algo más sobre la interfaz de Visual Basic .....................................................................................................66 Acerca de la ventana Examinador de Objetos .....................................................................................66 ¿Qué hacer cuando una Macro muestra un Mensaje de Error? ...........................................................67 Errores en tiempo de ejecución ........................................................................................................67 Errores de sintaxis ......................................................................................................................68Creación de funciones personalizadas fx .......................................................................................................69 Aspectos generales para la creación de cualquier función personalizada .............................................70 Estructuras condicionales en funciones personalizadas .......................................................................71 Ubique textos explicativos en sus funciones personalizadas ................................................................72 Creación de funciones personalizadas más complejas .........................................................................73

CAPITULO 3: Técnicas Detalladas de Interacción con Excel

Otras formas de desplazamiento ..................................................................................................................78 Seleccionar una celda ........................................................................................................................78 Seleccionar una celda en forma absoluta ...........................................................................................78 Desplazarse a una celda en forma relativa ........................................................................................79 Digitar la coordenada de la celda o rango que se quiere seleccionar ...................................................80 Seleccionar la “Última Celda” de: la Derecha, Arriba, la Izquierda o Abajo ..........................................81 Líneas de desplazamiento con las teclas .............................................................................................83Seleccionar Rangos con Límites Desconocidos...............................................................................................83 Seleccionar un rango vectorial, donde la primera celda es la celda activa ............................................83 Si el primer punto es un Rango Horizontal ......................................................................................85 Si el primer punto es un Rango Vertical ..........................................................................................86 Selección de Regiones .......................................................................................................................87 Selección de Rangos Múltiples ...........................................................................................................88Otras formas de Copiar, Cortar, Mover y Pegar ..............................................................................................89Detalles importantes de Pedado Especial ......................................................................................................91Interacción con Hojas ...................................................................................................................................91 Otras formas de seleccionar hojas ......................................................................................................91 Selección múltiple de hojas ................................................................................................................92Desplazamiento entre áreas del Libro de Trabajo ...........................................................................................93 Crear Vistas Personalizadas ................................................................................................................93 Mostrar las Vistas Personalizadas .......................................................................................................94 Otras formas de desplazamiento .......................................................................................................95Personalización de las Barras de Herramientas ..............................................................................................97 Crear una nueva Barra de Herramientas .............................................................................................97 Insertar botones que ejecutan instrucciones de Excel ..........................................................................98 Insertar botones que ejecutan macros de Excel ..................................................................................98Novedades importantes de MS-Excel 2002 ...................................................................................................99 Etiquetas Inteligentes ........................................................................................................................99 Al copiar arrastrando ..................................................................................................................99 Al Pegar .................................................................................................................................101Novedades importantes de MS-Excel 2003 .................................................................................................102 Creación de listas.............................................................................................................................103 Visualización alterna de listas ...........................................................................................................104

CAPITULO 4: Técnicas avanzadas para el planteamiento, estructuración y auditoría de fórmulas

Fundamentos en la formulación .................................................................................................................108 Combinar expresiones con paréntesis ...............................................................................................108 Referencia a celdas dentro de fórmulas ............................................................................................110 Algunos detalles sobre las funciones fx ....................................................................................................................112Formas rápidas de crear y aplicar nombres a celdas y rangos en modelos ya formulados ............................114

4

Crear nombres de celdas y rangos en forma automática...................................................................116 Reemplace en las fórmulas las referencias de celdas y rangos por sus correspondientes nombres ......117Formulación en lenguaje natural ................................................................................................................119 Reglas para la formulación en Lenguaje Natural ...............................................................................120 Optimización para la formulación de interseccines implícitas ............................................................123 Ventajas y desventajas de la formulación en lenguaje natural vs. la utilización de nombres a celdas y rangos .............................................................................................................................123Técnicas y aplicaciones de la formulación matricial ....................................................................................124 Conceptos que fundamentan la formulación matricial......................................................................124 Formulación matricial simple ...........................................................................................................126 Aplicación de las fórmulas matriciales sobre celdas individuales...........................................................127 Formulación matricial compleja .......................................................................................................128 Aplicación detallada de las fórmulas matriciales dentro de funciones fx ............................................129Ejecución de consultas en tablas .................................................................................................................132Control de longitudes de rangos dentro de fórmulas ..................................................................................133 Control del punto final de un rango vectorial ..............................................................................................133 Control de los puntos inicial y final de un rango vectorial ............................................................................134 Control de los puntos inicial y final de un rango matricial .................................................................136Control dinámico de los rangos de validación que permiten crear menús inteligentes ..................................137 Control de la longitud del rango que alimenta la validación de datos en celdas ................................137 Control de múltiples menús de rangos que alimentan una validación de datos .................................138Herramientas que facilitan la revisión de fórmulas y sus posibles errores ......................................................139 Opciones para auditar relaciones entre celdas ..................................................................................140 Opciones para auditar errores ..........................................................................................................141 Errores plenamente identificados por Excel ....................................................................................142 Errores de lógica .......................................................................................................................143 Opciones para revisión de validación de datos en celdas ..................................................................143 Otras opciones ................................................................................................................................144

CAPITULO 5: Funciones de Excel y Visual Basic aplicadas a modelos financieros

Herramientas y funciones financieras ..........................................................................................................148 Equivalencias entre montos de dinero ..............................................................................................148 Equivalencias entre tasas de interés..................................................................................................149 Creación de una función personalizada fx para el manejo de tasas anticipadas .................................151Un modelo de Evaluación Financiera para un Proyecto de Inversión ............................................................152 Formulación del flujo de fondos .................................................................................................................153 Formulación del flujo de fondos incremental ....................................................................................155 Otra manera de plantear el flujo de fondos incremental ...................................................................156 Herramientas para la toma de decisiones financieras...................................................................................157 La tasa interna de retorno, TIR ..................................................................................................157 La tasa interna de retorno modificada, TIRM ................................................................................158 El valor presente neto, VPN ......................................................................................................159 El periodo de recuperación de inversión ..........................................................................................161Combine datos de entrada y obtenga reportes inmediatos de resultados ...................................................163 Visualice los resultados de posibles combinaciones de dos variables ............................................................163 Visualice los resultados de posibles combinaciones de muchas variables......................................................164 Planteamiento de un modelo para la aplicación de Escenarios .............................................................164 Tipos de reportes emitidos por los Escenarios ..................................................................................167Encuentre el dato de entrada que optimiza los resultados ...........................................................................171 Obtenga el valor de una variable de entrada que satisfaga un resultado exacto ................................171Optimización de recursos con Solver ..........................................................................................................172 Optimización de una inversión .........................................................................................................174

CAPITULO 6: Manipulación de listados Bases de Datos

Algo más en el momento de ordenar .........................................................................................................178 Cuando Excel no identifica los campos de una lista ..........................................................................178 Cuando se requiere ordenar por un criterio diferente al alfabético o al numérico ..............................178 Cuando requiere ordenar por más de los tres criterios mostrados en la ventana Ordenar ..................179

5

Plantee sus criterios de filtrado y consulte información de sus listas bases de datos .....................................180 Un paseo sobre las opciones de AutoFiltro .......................................................................................180 Filtros personalizados en campos numéricos ....................................................................................181 Filtros personalizados en campos no numéricos ...............................................................................181 La opción “Los diez más…” de los Autofiltros ..................................................................................182 Porqué, cuándo y cómo usar los Filtros Avanzados ...........................................................................183 Limitante de los Autofiltros en campos no numéricos.......................................................................184 Limitante de los Autofiltros en campos numéricos............................................................................186 Ejemplos de rangos de valores determinados por un límite ..................................................................186 Ejemplos de rangos de valores determinados por dos límites ................................................................187 Ejemplos de rangos de valores determinados por tres límites ................................................................ 187 Ejemplos de rangos de valores determinados por cuatro límites ............................................................188Visualice su lista base de datos agrupada y totalizada .................................................................................188 Planteamiento de un Subtotal simple ...............................................................................................189 Incremente el nivel de detalle de su información ..............................................................................190 Si desea hacer varias operaciones sobre un solo campo ..............................................................................191Convierta la información en bruto en potentes reportes dinámicos .............................................................192 Analice la estructura de su Base de Datos y diseñe sus reportes ........................................................192 Al ubicar más de un campo en el área de FILAS o el área de COLUMNAS .........................................193 Si desea hacer varias operaciones sobre un solo campo ....................................................................193Planteamiento de un prototipo para el análisis de ventas ............................................................................194Edición de Tablas dinámicas .......................................................................................................................196 Agrupe elementos de campo ...........................................................................................................196 Ordene los datos de sus Tablas Dinámicas ........................................................................................198 Filtre la información a visualizar en las tablas dinámicas ...................................................................202 Controle la visualización de los totales en sus tablas dinámicas.........................................................203Detalles adicionales sobre edición de tablas dinámicas ................................................................................205 Visualizar detalles de un elemento de campo ...................................................................................205 La ventana Opciones de tabla dinámica ...........................................................................................206 Preferencias en el momento de crear una tabla dinámica .................................................................206Análisis de datos comparativos con tablas dinámicas ..................................................................................208 Tabla dinámica que muestra resultados acumulados ........................................................................209 Tabla dinámica que muestra participaciones porcentuales ................................................................209 Tabla dinámica que muestra el crecimiento ......................................................................................210 Un informe dinámico comparativo de un presupuesto .....................................................................210 Soluciones de análisis mediante campos calculados en tablas dinámicas .................................................211 Inserción de elementos calculados en tablas dinámicas ........................................................................212 Un informe de gráfico dinámico comparativo de un presupuesto .....................................................214Síntesis de cálculos especiales en tablas dinámicas ......................................................................................215

CAPITULO 7: Técnicas y herramientas de programación

El manejo de variables ................................................................................................................................218 Dimensionamiento de variables .......................................................................................................218 Formas de asignar valores a variables ...............................................................................................219 Datos que provienen desde valores constantes ...................................................................................219 Datos que provienen desde propiedades de Objetos de Excel ...............................................................219 Datos que ingresa el usuario mientras se ejecuta la Macro..................................................................220 Variables tipo objeto (Object) ..........................................................................................................221 Declaración de variables tipo Object en forma general y en forma particular ...........................................222 Variables tipo rango (Range) ............................................................................................................222 Declaración forzosa de variables ......................................................................................................223 Errores de Visual Basic que refieren a los tipos de datos ...................................................................223 Concatenar textos, variables y propiedades de objetos .....................................................................224Cajas de mensaje (MsgBox) ........................................................................................................................226 Botones de control sobre las cajas de mensajes (MsgBox) .................................................................227

6

Cajas de entrada de información (InputBox) ...............................................................................................230 InputBox como función Visual Basic ...........................................................................................................230 InputBox como método de la aplicación de Excel .............................................................................232Estructuras que controlan el flujo de la ejecución de las instrucciones .........................................................233Estructuras que modifican el flujo del listado de instrucciones .....................................................................233 Estructura de control If-Then-Else ....................................................................................................234 Estructura de control Select-Case ....................................................................................................235 Instrucción GoTo de VBA .................................................................................................................236 Instrucción Exit de VBA ..............................................................................................................................238Estructuras que repiten grupos de instrucciones .........................................................................................239 Estructura For-Next para repetir instrucciones un número conocidode veces .....................................239 Estructura Do Until-Loop para repetir instrucciones hasta que se cumpla una condición ...................240 Estructura para un recorrido simple .................................................................................................242 Estructura para el recorrido entre dos rangos de diferentes hojas .....................................................243 Estructura para el recorrido entre dos rangos de la misma hoja ........................................................245 Estructura For-Each-Next .................................................................................................................247 Aplicación For-Each-Next para colecciones de objetos tipo Range .....................................................247 Aplicación For-Each-Next para colecciones de objetos tipo Worksheet ..............................................248

CAPITULO 8: Ejemplos prácticos de Macros en tareas repetitivas

Terminología, convenciones y métodos .......................................................................................................252 Terminología ...................................................................................................................................252 Convenciones para la elaboración de listados de instrucciones .........................................................253 Parámetros para la exposición de los ejemplos ............................................................................................255Macro que elabora la tabla de amortización de un crédito ..........................................................................255 Objetivo general y definición de tareas .............................................................................................256 Tarea 1: Ingreso de datos constantes ...............................................................................................256 Listado de instrucciones ..............................................................................................................256 Optimización del código ..............................................................................................................257 Tarea 2: Ingreso de datos variables ...................................................................................................258 Tarea 3: Llenado de la tabla y formulación .......................................................................................259 Listado de Instrucciones .............................................................................................................259 Identificación de ciclos de instrucciones ............................................................................................260 Optimización del código ........................................................................................................................261 Unión de las subrutinas en una sola Macro .................................................................................................262Macro que carga un archivo plano y actualiza un informe ..........................................................................264 Objetivo general y definición de tareas .............................................................................................265 Tarea 1: Captura del archivo plano y ubicación de la información .....................................................266 Listado de instrucciones ..............................................................................................................266 Optimización del código ..............................................................................................................269 Tarea 2: Depuración de la información de acuerdo a la estructura requerida por la base de datos .....271 Listado de instrucciones ..............................................................................................................271 Optimización del código ..............................................................................................................274 Tarea 3: Actualización de la Tabla dinámica ......................................................................................275 Listado de instrucciones ..............................................................................................................275 Optimización del código ..............................................................................................................276 Ajuste final . ....................................................................................................................................278 Corrección de valores numéricos que se importan como texto ...............................................................278 Corrección de caracteres de miles en valores numéricos importados ........................................................279Macro que organiza la estructura de un archivo importado ........................................................................281 Objetivo general y definición de tareas .............................................................................................281

7

Tarea 1: Instrucciones que no se requieren repetir ............................................................................283 Optimización del código ..............................................................................................................283 Tarea 2: Instrucciones que se requieren repetir .................................................................................284 Optimización del código ..............................................................................................................286Macro que modifica la estructura de un informe ........................................................................................287 Objetivo general y definición de tareas .............................................................................................288 Tarea 1: Instrucciones que no se requieren repetir ............................................................................290 Optimización del código ..............................................................................................................291 Tarea 2: Instrucciones que se requieren repetir .................................................................................292 Optimización del código ..............................................................................................................294 Tarea 3: Instrucciones que se requieren después del ciclo .................................................................296 Ajuste final ....................................................................................................................................297

CAPITULO 9: Controles sobre las hojas y formularios

Los controles sobre las hojas ......................................................................................................................302 Dibujar controles sobre las hojas ......................................................................................................302 Principales propiedades de los controles ..........................................................................................303 Propiedades que son comunes en los controles ...................................................................................304 Propiedades particulares de los controles .........................................................................................306 Principales eventos de los controles .................................................................................................306 Acceso al área de Visual Basic donde se almacenan los eventos ........................................................307Diseño de un formulario sobre la hoja ........................................................................................................307Creación de formularios desplegables .........................................................................................................310 Un ejemplo para la planeación del diseño de un formulario .............................................................310 Dibujo y diseño del Formulario ........................................................................................................312Desarrollo del código para el correcto funcionamiento del formulario .........................................................313 En el evento de activar el formulario ................................................................................................314 En el evento “después de actualizar” el cuadro combinado de las referencias (cboRefs) ....................315 En el evento de cambiar el valor del botón de número para las cantidades (spnCant). ......................319 En el evento de cambiar el valor del cuadro de texto para las cantidades (txtCant). ...........................320 En el evento de hacer Click en el botón Cancelar (btnCancelar).........................................................321 En el evento de hacer Click en el botón Aceptar (btnAceptar) ...........................................................322