Guia Mini Curso Excel Avanzado

GUIA GUIA MINI MINI CU CURS RSO O EX EXCE CELL AV AVAN ANZA ZADO DO

Análisis y Si, Búsqueda de Objetivo, Escenarios Análisis y Si Proceso de cambio de los valores de celdas para ver cómo afectan esos cambios al resultado de fórmulas de la hoja de cálculo. Se calcula una fórmula D en base a valores A,B,C y luego se recalcula con diferentes valores de A,B o C. Por ejemplo, variar el depósito anual de ahorro para ver el efecto en el valor futuro. Se puede hacer variando el valor de uno en uno y viendo el resultado o mediante el uso de la función Tabla.

Tablas de 1 entrada, fila columna Ejercicio 1: Calcular el valor futuro futuro de una inversión anual del 10%, por un

período de 10 años, con un depósito inicial de US$ 25.000, y un pago anual de US$ 10.000. Fórmula de Valor Futuro VF((tasa; VF tasa;nper; nper;pago;va;tipo) pago;va;tipo) tasa: tasa de interés por período, si tasa es anual y pagos mensuales, dividir tasa por 12 nper: número de pagos pago: importe del pago va: valor actual tipo: 1: el pago vence al principio del período: 0:al final

Solución: Valor futuro= VF(5%;10;-10000;-25.000;1)=172.790




Ejercicio 2: Analizar el cambio del valor futuro al variar el depósito anual. Si se requiere ver el efecto de un rango de valores en la fórmula se debe configurar una tabla de datos: Agregar a la hoja los datos que se quieren incluir en la fórmula. Si se quieren ingresar los valores en una columna, empezar una columna a la izquierda y una fila debajo de la ubicación de la fórmula:

Seleccionar el rango, incluyendo la fórmula y el rango de datos: (B9:C17)

GUIA MINI CURSO EXCEL AVANZADO Datos, Tabla:

Como los datos se ingresaron en columna se debe usar Celda de entrada (columna). Esta celda corresponde al valor a cambiar en la fórmula, que es depósito anual, o sea C4:

GUIA MINI CURSO EXCEL AVANZADO Al intentar borrar una celda de la tabla da el mensaje:

Para borrar la tabla debe seleccionarse todo su rango presionar Suprimir. La fórmula de tabla es: {TABLA(Celda_Referencia_Filas;Celda_Referencia_Columnas)} {}= Matriz o arreglo

Considerar Inflación Se desea saber como se ve la inversión futura en dólares o moneda de hoy, es decir considerar la inflación. Estos valores serán menores. n Valor futuro con inflación = Valor actual x (1+i) I=tasa de inflación de un período, en decimal o porcentaje n=numero de períodos n Valor actual = (Valor futuro con inflación)/(1+i)

GUIA MINI CURSO EXCEL AVANZADO

Marcar el rango:

GUIA MINI CURSO EXCEL AVANZADO Celda de entrada es el depósito anual:

Se calculan los datos de la tabla:

Tabla de 2 Entradas Crear la siguiente tabla:


Datos, Tabla, elegir C2 (interés) y C4 (depósito anual)



Buscar Objetivo Ejemplo: Se desea determinar el ahorro anual para realizar una renovación de equipos de aquí a 5 años por un valor de 50.000 dólares. Con un interés de 5%, determinar cual debe ser el monto del ahorro anual. Herramientas, Buscar Objetivo, definir la celda $C$8 con el valor $ 50.000, cambiando la celda $C$6 (Ahorro anual). O en otras palabras: que valor debe tener la celda $C$6 para que $C$8 sea igual a $ 50.000. El valor inicial de ahorro anual es cualquier valor dado

Al dar aceptar Excel comienza a iterar y despliega la pantalla de estado de la búsqueda de objetivo. Al finalizar indica si Buscar Objetivo encontró una solución:

Para aceptar la solución dar Aceptar y Cancelar para ignorar esa solución.

GUIA MINI CURSO EXCEL AVANZADO Precio requerido para obtener un margen de 30%.

Estos resultados son con 100 iteraciones y diferencia entre valores de 0,01

GUIA MINI CURSO EXCEL AVANZADO Si se mantiene 100 iteraciones pero el cambio se fija en 0,0001:

De un valor del precio de 47,3743se llega al final a 47,9276 como lo más exacto con estos parámetros de iteración.

Mínimo de Unidades Vendidas Se desea determinar el número de unidades que se deben vender para empezar a tener ganancias, o sea margen cero: Se supone un valor de unidades a vender de 30.00 inicialmente


Se encuentra un valor de 46455 unidades a vender para empezar a tener ganancias.

Resolver Ecuaciones Buscar Objetivo permite resolver ecuaciones de una variable. En el ejemplo siguiente primero se debe cambiar la opción de exactitud de las iteraciones.

Se debe buscar el valor de la celda A2 (o X) que haga la ecuación definida en la celda B2 igual a 1. El resultado de x es 094869. Debe notarse que el resultado solo se aproxima a 1 en base a la exactitud definida.


Búsqueda de Objetivos con Gráficos Si existen datos en un gráfico 2D de barras, columnas, líneas o XY, se puede utilizar Búsqueda de Objetivo usando el Mouse, arrastrando el marcador de datos del gráfico a una nueva posición. Si este corresponde a una fórmula el Buscador de Objetivo encuentra los valores adecuados que se deben ingresar en la fórmula. Ejemplo: Se desea ahorrar cierta suma anualmente de modo que en 10 años se pueda tener $ 150.000. Si se asume una tasa de interés constante, ¿cuanto se debe ahorrar o depositar anualmente para alcanzar la meta indicada? Se supone una tasa de interés de 5%, e inicialmente un valor de depósito anual de $ 5.000. La fórmula corresponde a VF: Devuelve el valor futuro de una inversión basándose en pagos periódicos constantes y en una tasa de interés constante. Sintaxis: VF(tasa;nper;pago;va;tipo). El tipo de depósito es cero, o sea pago al final del período, por ello en el año cero (al inicio) es cero: Se grafica hasta 10 años, pero se ponen datos mas allá de 10 años para tener un rango de cálculo más amplio.


Seleccionar el marcador de datos correspondiente al valor 10: Serie “Ahorros” punto “10”, valor $ 62.889. Arrastrar hacia arriba hasta que valor sea 150.000:

GUIA MINI CURSO EXCEL AVANZADO Al soltar aparece:

La celda es B2, el valor anual a ahorrar.

Al dar aceptar el gráfico se modifica:


El valor de depósito Anual Buscado es $ 11.926

Escenarios Que es: Un conjunto particular de valores, suposiciones, hipótesis que se definen en un modelo de cálculo en Excel. Los análisis del tipo Y Si, pueden tomar distintos valores de entrada y resulta laborioso cambiarlos uno a uno para ver los efectos. Para ello existe el administrador de escenarios de Excel. Un ejemplo: Análisis Hipotecario: Este tipo de análisis se utiliza para decidir el monto del pie, el período del préstamo, si se debe considerar un pago extra mensual. En los resultados se analiza el menor pago total, el ahorro total a el menor plazo que se obtiene considerando diferentes valores de pie y de pago extra mensual.


El pago mensual corresponde a la fórmula: =PAGO(Tasa_Interes/12;Plazo*12;Precio_Casa-Pie_Inicial) Con pago extra es: =B12+Pago_Extra_Mes El plazo corregido corresponde a la fórmula: =NPER(Tasa_Interes/12;C12;Precio_Casa-Pie_Inicial)/12. o sea el número de períodos para financiar el préstamo (diferencia entre precio de la casa y pie) considerando un valor de cuota aumentado con pago extra Aquí se pueden hacer varias preguntas correspondientes a varios escenarios. Por ejemplo: Cuanto se ahorra si se usa un plazo menor, se aumenta el pie y se considera un mayor pago extra. Cuanto se pagaría en total si se aumenta el plazo al máximo permitido, no hay pie y no hay pago extra mensual. Que pasa en una alternativa de término medio, o sea plazo razonable, pie y un valor adecuado adicional. Preparación de hoja para crear escenarios


Utilizaremos 3 escenarios Mejor Medio

Peor

Pie Inicial: 20.000 Plazo: 20 Pago Extra: 100

Pie Inicial: 10.000 Plazo 30: Pago Extra: 0

Pie Inicial 15.000 Plazo 25 Pago Extra -50

Herramientas, Escenarios, Agregar: Definir el Mejor escenario, Elegir celdas cambiantes: B7 a B9, ingresar comentarios:


Aceptar, e ingresar los valores, en este caso corresponden a los que estaban en la planilla


Ingresar los otros 2 escenarios. Mostrar el Peor:

GUIA MINI CURSO EXCEL AVANZADO Resumen de los escenarios: Elegir antes otros valores para comparar con los de los escenarios del informe:

Elegir Resumen:

Solo los valores de las celdas cambiantes:

GUIA MINI CURSO EXCEL AVANZADO Sólo las celdas de resultados

Tabla Dinámica de Escenarios: Herramientas, Escenario, Resumen

Combinar Escenarios: Abrir una nueva hoja o libro donde guardar los escenarios y mantener abierta las hojas desde las cuales se combina


Con Aceptar se ingresan los escenarios a esta hoja

Si se da mostrar muestra solo los valores. La idea es que los escenarios importados sean adecuados a la hoja de destino. (Modelo similar)


SOLVER Utilizando Solver Margen cero para 2 productos. Se debe determinar el número de unidades a vender de cada uno para lograr margen cero. Los costos variables de cada producto no dependen del otro. Se usa objetivo en forma separada.

Resultado: Producto A: 8023, Producto B: 7177 Considerar disminución de costos al elaborar los dos productos juntos Margen cero pero agregando disminución de costos por elaboración y transporte de ambos productos juntos. Se considera una disminución de costo de $ 1 por cada unidad vendida del otro producto. La fórmula de costo variable de A es: Unidades A * Costo Unitario A – 1*Unidades vendidas B y viceversa. La celda objetivo es B14, la ganancia de ambos productos. Las celdas cambiantes B4 y C4. Resultados sin incluir las restricciones:


Resolver:

La ganancia total es cero pero no la de cada producto. Agregar las restricciones: Las restricciones son ganancia de cada producto debe ser cero: B12 y C12=0. (Pueden haber hasta 100 restricciones).


Solver pudo encontrar una solución en que los márgenes de ambos productos son cero.

GUIA MINI CURSO EXCEL AVANZADO Informes:

Informe de Respuestas: Muestra una lista con la celda objetivo y las celdas ajustables con sus valores originales y sus valores finales, las restricciones y la información acerca de las mismas.

Informe de Sensibilidad Facilita información acerca de la sensibilidad de la solución a que se realicen pequeños cambios en la fórmula definida o en las restricciones. No se genera este informe para los modelos que tengan restricciones enteras. En modelos no lineales, el informe facilita los valores para las gradientes y los multiplicadores de Lagrange. En los modelos lineales, el informe incluye costos reducidos, otros precios, coeficiente de objetivos (con aumentos y disminuciones permitidos) y rangos de restricciones hacia la derecha.

GUIA MINI CURSO EXCEL AVANZADO Informe de Límites Muestra una lista con la celda objetivo y las celdas ajustables con sus valores correspondientes, los límites inferior y superior así como los valores del objetivo. No se genera este informe para los modelos que tengan restricciones enteras. El límite inferior es el valor mínimo que puede tomar la celda ajustable mientras se mantienen todas las demás celdas ajustables fijas y se continúa satisfaciendo las restricciones. El límite superior es el valor máximo.


El Problema del Transporte Su objetivo básico es minimizar los costos de transporte de mercaderías desde varias plantas de producción hacia varias bodegas distribuidas en un país. Las restricciones son las siguientes: •

•

•

La suma de las cantidades despachadas a cada bodega desde las plantas ser igual a la cantidad demandada por esta. Las cantidades a despachar desde cada bodega debe ser mayor o igual a cero. La cantidad total despachada desde cada planta a todas las bodegas no debe superar la capacidad de suministro de esta. (Igual o menor)




Tablas Dinámicas Introducción Utilicemos el archivo Cuentas Banco:

Datos, Informe de Tablas y Gráficos Dinámicos

Siguiente


Detecta automáticamente el rango de la tabla Siguiente:

Hay dos posibilidades: Diseño o Finalizar. Diseño:



Finalizar: Utilizar Lista de campos de tabla dinámica

El menú de Lista de campos de tabla dinámica permite distribuir los campos directamente en la hoja de cálculo. Elegir un campo, un área y dar clic en Agregar a


No es obligatorio enviar todos los campos, sólo los que correspondan al análisis a realizar Para sacar campos simplemente arrastrarlos fuera de la tabla En el área de datos: Valores numéricos: se les aplica la función suma Valores no numéricos: se aplica la función contar Agrupar Ítems de una Tabla Dinámica Utilicemos el archivo Ventas por Fecha:

En el caso de fechas, Excel puede crear los grupos automáticamente:

GUIA GUIA MINI MINI CU CURS RSO O EX EXCE CELL AV AVAN ANZA ZADO DO Agrupar por Meses Crear la tabla: Fecha:

agregar a filas Ventas: agregar a área de datos

Botón derecho en Fecha: Agrupar y Mostrar Detalle, Agrupar Seleccionar Años y Meses:

GUIA GUIA MINI MINI CU CURS RSO O EX EXCE CELL AV AVAN ANZA ZADO DO O puede ser:

Campos o Ítems Calculados Campo Calculado Es un nuevo campo creado a partir de los campos de la tabla. Sólo puede pertenecer al área de datos

Ítem Calculado Usa los contenidos de otros ítems dentro de un campo. Sólo puede pertenecer a las áreas de página, filas o columnas. Los ejemplos utilizan el archivo Ventas Vendedor Región Meses: Meses:

Crear un Campo Calculado Se desea agregar el campo Precio Promedio de Venta, es decir Valor de la venta dividido por el número de unidades. Mover el cursor a cualquier lugar dentro de la tabla

GUIA MINI CURSO EXCEL AVANZADO Barra de Tablas Dinámicas, Tabla Dinámica, Fórmulas, Campo Calculado:

Dar Nombre a campo: PrecioProm, en Fórmula después de signo igual insertar campo Ventas, signo / e insertar campo Unidades. (O doble clic sobre Venta y Unidades). Sumar y aceptar. Con Sumar pasa a la lista de campos de Tabla Dinámica:


Campo aparece como Suma de PrecioProm.

Se puede cambiar nombre a Precio Promedio, sobre el nombre, botón derecho, configuración de campo, nombre: Precio Promedio:


El campo es agregado en el diseño al área de datos:

Este campo no se puede mover a las áreas de páginas, filas o columnas.

Crear un Ítem Calculado El Ítem calculado usa los contenidos de ítems en un mismo campo El archivo Ventas Vendedor Región Meses tiene el campo Mes que consiste de cadenas de texto- Se puede crear un ítem calculado, (llamado Trim-I), que muestre la suma de los meses de Enero, Febrero y Marzo. Posicionarse sobre un dato de campo mes Tablas Dinámica, Fórmula, Elemento Calculado


Nombre: Trim I Fórmula: =Jan+Feb+Mar o ir insertando los elementos e ingresando los signos +. Finalizar con Sumar , Aceptar. La fórmula puede usar ítems de otros campos, pero no funciones de hojas de cálculo. El Ítem calculado es agregado al final. Después se puede mover hacia la posición deseada, arrastrándolo o mediante: Barra de Tabla, Ordenar, Subir.

Formatear toda el área de datos con botón derecho, Formato de Celdas, Moneda. Destacar con negrita datos de nuevo campo:


Botón derecho, Opciones de Tabla, desmarcar Totales generales de Columnas Continuar con el mismo proceso para los restantes trimestres.

Notas Para tomar los datos a partir de una Lista grabada en un archivo Excel, la lista debe estar abierta primero Mover tabla ya creada a otra hoja o posición se puede hacer con el mismo asistente, donde desea situar, elegir y finalizar.

Tablas a partir de bases de datos externas Optimizar memoria en el caso de tabla desde bases de datos Traer solo los campos a partir de los cuales se haría la tabla Cuando hay problemas de memoria bases de datos externas Se utiliza la base de datos Access 2003 Presupuestos.mdb


Seleccionar Presupuesto.mdb Si se desea hacer un análisis de los presupuestos remanentes seleccionamos por ejemplo: División, Departamento, Categoría, Mes y Remanente


Siguiente, siguiente, siguiente, Finalizar:

Se puede volver a revisar o cambiar en el botón: Obtener datos Siguiente, Hoja de Cálculo nueva: Opciones antes de crear la tabla:


Opciones de datos, aparece Actualizar cada x minutos

Opciones de datos externos : Consulta en segundo plano: prioridad del procesamiento Optimizar Memoria que es útil cuando base de datos es grande o hay errores o problemas de memoria insuficiente. Finalizar asistente Diseñar la tabla:


Leer directamente desde datos externos Ayuda más aun en el caso de problemas de memoria. Es válido para los campos de página de la tabla y permite consultar directamente la base de datos al seleccionar cada elemento: Doble clic sobre DIVISIÓN, Campo de la Tabla Dinámica y Avanzadas:

Desmarcar Recuperar… y Deshabilitar…

Desaparece la opción (Todas) del campo División y al seleccionarlo no se ve Mostrar Todo


Lo mismo para el campo DEPTO, aparece Accounting. Si ya existía una consulta guardada o se está realizando una consulta, se puede crear un informe de tabal dinámica a partir de esta:

Utilizar un archivo de conexión de datos odc Debe crearse primero el archivo odc: Datos, Obtener Datos Externos, Importar Datos, Conectar con nuevo origen…


Elegir DSN (Data Source Name)

Elegir Access

Seleccionar la base de datos, siguiente:


Archivos quedan en Mis documentos, Mis archivos de origen de datos. Crear la Tabla Datos, Obtener Datos Externos, Importar Datos, seleccionar el origen Presupuesto.odc, Abrir:

Crear Tabla: Asistente, Diseño:


Rangos de Consolidación Múltiples Archivo: Ventas de Pacific Instruments


Solicita los rangos

Se debe especificar cada rango de datos.


Se deben considerar los encabezados, pero no totales ya calculados.

Después de ingresar todos los rangos, Siguiente, Hoja de Cálculo Nueva, Finalizar:



Rangos de Consolidación Múltiples, Campos de Página Personalizados Archivo: Libros por Trimestres Crear tabla dinámica con rangos de consolidación múltiples:

Agregar los rangos Definir 2 campos de página En Todos los rangos, Seleccionar el primer rango e introducir en el campo uno: 2002, en campo dos Trim 1. Hacer lo mismo para los otros rangos: 2002 Trim 1, 2002 Trim 2, etc.


Siguiente, diálogo de crear la tabla, Finalizar Se crea la tabla con 2 campos de página: En Página 1 puede elegir los años y en Página 2 los trimestres


MACROS Grabación de Macros Una Macro es una lista o conjunto de instrucciones que le indican a Excel realizar una o mas acciones sin intervención necesaria del usuario. Son como programas computacionales, pero se ejecutan completamente dentro de Excel. Se usan para automatizar tareas que consumen mucho tiempo o repetidas con frecuencia por el usuario. Ejemplo de acciones de una macro: •

•

•

Ingresar una serie de datos en una fila de una hoja de cálculo Centrar los datos en cada celda Aplicar un borde y dar formato a la fila.

Otro: •

Definir opciones especiales de impresión en la opción configurar página e imprimir el documento.

Las macros pueden ser interactivas y solicitar información al usuario y actuar de acuerdo a esa información. Formas de crear una macro: •

•

Grabar la macro Construirla ingresando instrucciones en un módulo

En ambos casos las instrucciones son codificadas en el lenguaje de programación Visual Basic para Aplicaciones (VBA). La alterativa de grabar la macro, sirve sólo para grabar acciones que posteriormente se repetirán al ejecutar la macro. Existen tareas que no se pueden grabar, por ejemplo la creación de ventanas de dialogo, creación de nuevos comandos o funciones y procesamiento de información en varios libros de Excel.

Que Se Puede Hacer con VBA VBA es un lenguaje de programación muy completo con innumerables usos. A continuación una lista con algunas cosas que se pueden hacer con macros de Excel  Insertar una cadena de texto o fórmula: Si se desea ingresar un nombre, dirección u otros datos repetidamente una macro puede ingresarlos automáticamente. Esto también lo hace la función de auto corrección.

GUIA MINI CURSO EXCEL AVANZADO 

Automatizar un procedimiento frecuente: Preparación de un informe de resumen mensual. Si es un procedimiento directo se puede desarrollar una macro para ejecutarlo a partir de los datos.



Automatizar tareas repetitivas: Si se requiere repetir las mismas acciones en diferentes libros de Excel, se puede grabar una macro al realizar las acciones por una vez y luego repetirlas en los otros libros, ejecutando la macro. Crear un comando personalizado: Por ejemplo se pueden combinar varios comandos de los menús y ejecutarlo con una combinación de teclas o un clic del mouse. Crear un botón personalizado de barras de herramientas: Escribir o grabar una macro y crear en una barra de herramientas un botón personalizado que la ejecute. También puede ser un menú de botones de macros. Crear una pantalla o menú simplificado para usuarios que no conocen Excel: Se puede crear una plantilla para ingresote datos a prueba de errores. Desarrollar una nueva función: Se pueden crear funciones personalizadas o adecuadas a nuestro trabajo, que permiten simplificar mucho las fórmulas. Varias operaciones matemáticas o que incluyan funciones de Excel. Crear aplicaciones completas manejadas por macros: Las macros de Excel pueden presentar cuadros de diálogo personalizados y agregar nuevos comandos a los menús y barras de herramientas. Crear programas complementarios personalizados para Excel: Mucho de los complementos agregados a Excel son basados en Macros. .













Desventajas de VBA Gran cantidad de comandos, sobre 1000, cuesta tener una visión amplia de VBA. Ello dificulta el aprendizaje. Cuesta mucho encontrar el método o propiedad adecuados para realizar ciertas acciones. Código más largo que otros lenguajes de Macros.


Dos Tipos de Macros VBA Es básico entender una definición clave: Una Macro de VBA puede ser de 2 tipos: 1. Procedimiento Sub 2. Función

Procedimientos Sub Se puede considerar un procedimiento Sub como un nuevo comando que puede ser utilizado por el usuario en una macro o llamado por otra macro. Se pueden tener muchos procedimientos en un libro. Definición de VBA: Un procedimiento Sub es una serie de instrucciones Visual Basic, encerradas entre un par de instrucciones Sub y End Sub, que realizan acciones específicas pero no devuelven ningún valor. Un procedimiento Sub puede aceptar argumentos, como constantes, variables o expresiones que le pasa el procedimiento que ha efectuado la llamada. Si un procedimiento Sub no tiene argumentos, la instrucción Sub debe incluir un par de paréntesis vacío. El siguiente es un ejemplo de un procedimiento Sub. Cuando se ejecuta, inserta la fecha en la celda actual, pone la celda en negrita y ajusta el ancho de la columna.

Los procedimientos Sub siempre parten con Sub y el nombre de la macro (cada macro debe tener nombre único) y un par de paréntesis (). Los paréntesis están vacíos a menos que contengan argumentos. La sentencia End Sub indica el fin del procedimiento. Las líneas entre Sub y End Sub comprenden el código del procedimiento. La macro también incluye un comentario: ‘ Inserta la fecha actual en la celda activa, el cual es precedido de apóstrofe y queda en color verde. El comentario

GUIA MINI CURSO EXCEL AVANZADO puede ir después de los comandos ya que al encontrar apóstrofe VBA ignora el resto del texto.

Ejecutar un procedimiento sub de VBA: Herramientas, Macro, Macros, seleccionar desde la lista y Ejecutar

Presionar la combinación de teclas del procedimiento o teclas de método abreviado (si tiene uno). Macro Opciones, en este caso no lo tiene:

GUIA MINI CURSO EXCEL AVANZADO Si el editor de Visual Basic esta activo, dejar el cursor en cualquier parte dentro del código y presionar F5. Ir al Editor de VBA con Alt+F11

Otra forma de ejecutarlo es llamar el procedimiento desde cualquier otro procedimiento

Procedimiento Function de VBA El segundo tipo de procedimientos VBA es Function. Function siempre entrega un valor único (igual que una función de la hoja de cálculo). Un procedimiento Function de VBA puede ser ejecutado por otros procedimientos de VBA o ser usado en fórmulas, tal como se usan las funciones incluidas en Excel. Definición de VBA Un procedimiento Function es una serie de instrucciones de Visual Basic encerradas entre dos instrucciones Function y End Function . Un procedimiento Function es similar a un procedimiento Sub , aunque una función puede devolver además un valor. Un procedimiento Function acepta argumentos, como pueden ser constantes, variables o expresiones que le pasa el procedimiento que efectúa la llamada. Si un procedimiento Function no tiene argumentos, la instrucción Function debe incluir un par de paréntesis vacíos. Una función devuelve un valor asignándolo a su nombre en una o más instrucciones del procedimiento. A continuación se muestra un código de ejemplo:


El procedimiento se llama RaizCubica y tiene un solo argumento. Calcula la raíz cúbica de su argumento. La diferencia respecto a un procedimiento Sub es que empieza con Function y termina con End Function. Se utiliza como una función más

Nota. Para activar la ayuda de VBA: Posicionar el cursor sobre un comando y presionar F1 En la macro de fecha actual:

GUIA MINI CURSO EXCEL AVANZADO Aparece la Ayuda para el comando Sub:

Alt-F11, Ayuda, Ayuda de Microsoft Visual Basic:


Creando macros de VBA Excel provee dos métodos para crear macros: Activar la grabadora de macros y grabar las acciones: (Herramientas, Macros)

Ingresar el código directamente en un módulo de VBA

Grabación de Macros Herramientas, Macros, Grabar nueva macro:


GUIA MINI CURSO EXCEL AVANZADO Nombre de la Macro: Excel propone nombres como Macro1, Macro2 Método Abreviado: Combinación de teclas que ejecuta la macro, es opcional. La combinación siempre usa la tecla Ctrl., se puede presionar además Mayus (Shift), si se quiere una letra con mayúsculas. Los comandos son sensibles a mayúsculas y minúsculas. Descripción: Por defecto pone fecha y nombre, es modificable. Para comenzar a grabar las acciones dar Aceptar, aparece en seguida la barra de Detener Grabación con la opción detener grabación

Al finalizar de grabar las acciones en Excel se presiona el botón Detener grabación o Herramientas, Macro, Detener Grabación:

Nota: Al grabar Macro siempre resulta un Procedimiento Sub. Una función debe ser creada manualmente con el editor de Visual Basic (VBE). Grabar una Macro: Ejemplo 1 Cambiar el formato para el rango seleccionado: Font Arial 16, Negrilla, Color Azul. Ingresar un Texto, mantener seleccionada la celda Herramientas Macro, Grabar nueva macro Ingresar un nuevo nombre: MacroFormato1 Asignarle la combinación de teclas Ctrl+Mayus+F, ingresando F en el cuadro de método abreviado. Dar Aceptar, se muestra la barra llamada Detener Grabación Realizar las acciones: Tipo de letra Arial 16, Negrita, Color de Texto Azul Detener grabación. Se ha completado la creación de la macro.


Examinar la Macro: Hay dos formas Alt+F11 o Herramientas, Macro, Editor de Visual Basic

La ventana de Proyectos, despliega una lista de todos los libros y complementos abiertos. La lista se despliega como un árbol que se despliega o colapsa. El código creado en base a la grabación anterior se guarda en el Módulo1 en el libro actual. Analizando, el código consiste en: Sub MacroFormato1() ‘ ‘ MacroFormato1 Macro ‘ Macro grabada el 21-08-2007 por Juan Schleyer ‘ ‘ Acceso directo: Ctrl+Mayús+F ‘ With Selection.Font .Name = “Arial” .FontStyle = “Negrita” .Size = 16 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False

GUIA MINI CURSO EXCEL AVANZADO .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 5 End With End Sub .Striketrough= False: No Tachado .Underline = xlUnderlineStyleNone: Estilo de subrayado: ninguno (si no: single) .OutlineFont = False: Buscar en ayuda: poner cursor sobre outline y presionar F1:

Propiedad OutlineFont True si la fuente es una fuente de contorno. Boolean de Lectura/Escritura. Comentarios Esta propiedad no tiene efecto en Windows, pero se conserva su valor (puede establecerse y devolverse). En este caso no influye .Shadow = False: Sombra pero no tiene efecto en este tipo de letra La macro grabada es un procedimiento sub que hemos llamado MacroFormato1. Los comentarios precedidos de apóstrofe pueden eliminarse, sin efectos sobre el procedimiento. La macro graba acciones no efectuadas y define todas las propiedades tales como Strikethrough = False .Superscript = False y .Subscript = False. Excel define las propiedades de cada característica de los tipos de letra aun cuando estas no se hayan cambiado.


Testeando la Macro Para retornar a Excel se puede mediante Alt-F11 de nuevo o el botón Ver Microsoft Excel.

Luego la macro se puede probar con Ctrl+Mayus+F:

Se puede abrir otro libro, manteniendo abierto el que contiene la macro, seleccionar un rango y presionar Ctrl+Mayus+F, de igual forma se cambia el formato del rango. Las celdas a formatear se seleccionaron antes y después se comienza a grabar, si se realiza después, estas celdas quedan definidas dentro de la macro y siempre va a formatear las mismas celdas, por lo cual no seria una macro de propósito general. Si se abre otro libro y se invoca Macros, por defecto aparece la opción Macros en todos los libros abiertos. Si en Macros en se elige Este libro y el nuevo no contiene macros, no se podría llamar la macro Macroformato1.


Editando la Macro Por ejemplo podemos cambiar el tipo de letra y tamaño, se puede re grabar la macro, pero como es una simple modificación es preferible editarla con Alt-F11 y seleccionar el módulo1 o Herramientas, Macro, Macros, Modificar Se pueden eliminar las líneas .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone Ya que son propiedades no utilizadas. Asimismo los comentarios no son necesarios: La Macro queda como: Sub MacroFormato1() With Selection.Font .Name = “Algerian” .FontStyle = “Negrita” .Size = 24 .ColorIndex = 5 End With End Sub Al aplicar Ctrl-Mayús-F


La macro funciona igual.


Otro Ejemplo de macro VBA Macro Ejemplo 2 Consiste en convertir fórmulas en valores. Este es un proceso de 2 pasos: 1. Edición, Copiar 2. Edición, Pegado especial, Valores La macro a realizar combina estos pasos en uno sólo. Se le asignará la combinación Ctrl+Mayus+V Pasos para crear la macro 1. Ingresar una fórmula cualquiera en una celda, por ejemplo en A3: A1+A2 2. Seleccionar la celda que tiene la fórmula 3. Herramientas, Macro, Grabar Nueva Macro. Excel despliega el diálogo grabar macro:

4. Ingresar nombre de macro, método abreviado y comentario (opcional) 5. Dar aceptar para empezar grabación 6. Con la celda aun seleccionada, Edición, Copiar, para copiar la celda (o rango) al portapapeles. 7. Seleccionar Edición, Pegado Especial, Valores, Aceptar. 8. Presionar Esc para desactivar el modo Pegar:

9. Presionar el botón Detener Grabación


Selection.Copy: Copiar la selección Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _SkipBlanks:=False, Transpose:=False Pega el contenido del portapapeles en la selección actual y contiene varios argumentos de las opciones de pegado especial. Application.CutCopyMode = False El tercer argumento cancela el borde de guiones del modo pegar:

Grabación de Macros, Absoluta o Relativa Normalmente al grabar se guardan las referencias reales a las celdas, (grabación absoluta). Si grabamos y marcamos el rango B1:B10 se graba lo siguiente: Range(“B1:B10”).Select Al llamar a esta macro, siempre se marcará ese rango, no importando la posición de la celda activa. Grabar en forma relativa, presionar botón indicado:

Sin referencia relativa: Con referencia relativa: (presionado). Notar que el botón queda como estaba relativo o no, para la siguiente macro.

GUIA MINI CURSO EXCEL AVANZADO Inicialmente en A1, seleccionar B1:B10 Sub Macro1() ActiveCell.Offset(0, 1).Range(“A1:A10”).Select End Sub Offset Es positivo si se aumentan filas y columnas abajo a la derecha y negativo si se disminuyen filas y columnas arriba izquierda Desde la celda activa A1: Offset(0,1) 0 filas hacia abajo y 1 columna a la derecha y seleccionar el rango como si fuera desde A1. En modo relativo la macro parte utilizando la celda activa como su base y luego guarda referencias relativas a esa celda.

Libro de Macros Personal Una de las opciones para grabar macros es libro de macros personal. Las macros grabadas en este libro están siempre disponibles ya que se inicia con Excel.

Al utilizar esta opción se crea el archivo personal.xls en la ruta: C:\Documents and Settings\Usuario\Datos de programa\Microsoft\Excel\XLSTART (Los archivos guardados en este directorio se inician automáticamente con Excel) Este libro está oculto y se muestra con Ventana, Mostrar:

Guia Mini Curso Excel Avanzado

Recommend Documents