guia mini curso excel avanzado

GUIA MINI CURSO EXCEL AVANZADO

Anlisis y Si, Bsqueda de Objetivo, EscenariosAnlisis y SiProceso de cambio de los valores de celdas para ver cmo afectan esos cambios al resultado de frmulas de la hoja de clculo. Se calcula una frmula D en base a valores A,B,C y luego se recalcula con diferentes valores de A,B o C. Por ejemplo, variar el depsito 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 funcin Tabla.

Tablas de 1 entrada, fila columna Ejercicio 1: Calcular el valor futuro de una inversin anual del 10%, por unperodo de 10 aos, con un depsito inicial de US$ 25.000, y un pago anual de US$ 10.000. Frmula de Valor Futuro VF(tasa;nper;pago;va;tipo) tasa: tasa de inters por perodo, si tasa es anual y pagos mensuales, dividir tasa por 12 nper: nmero de pagos pago: importe del pago va: valor actual tipo: 1: el pago vence al principio del perodo: 0:al final

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

1 de 76


2 de 76


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

Seleccionar el rango, incluyendo la frmula y el rango de datos: (B9:C17)

3 de 76

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 frmula, que es depsito anual, o sea C4:

4 de 76

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 frmula de tabla es: {TABLA(Celda_Referencia_Filas;Celda_Referencia_Columnas)} {}= Matriz o arreglo

Considerar InflacinSe desea saber como se ve la inversin futura en dlares o moneda de hoy, es decir considerar la inflacin. Estos valores sern menores. n Valor futuro con inflacin = Valor actual x (1+i) I=tasa de inflacin de un perodo, en decimal o porcentaje n=numero de perodosn Valor actual = (Valor futuro con inflacin)/(1+i)

5 de 76


Marcar el rango:

6 de 76

GUIA MINI CURSO EXCEL AVANZADO Celda de entrada es el depsito anual:

Se calculan los datos de la tabla:

Tabla de 2 EntradasCrear la siguiente tabla:

7 de 76


Datos, Tabla, elegir C2 (inters) y C4 (depsito anual)

8 de 76


9 de 76


Buscar ObjetivoEjemplo: Se desea determinar el ahorro anual para realizar una renovacin de equipos de aqu a 5 aos por un valor de 50.000 dlares. Con un inters 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 bsqueda de objetivo. Al finalizar indica si Buscar Objetivo encontr una solucin:

Para aceptar la solucin dar Aceptar y Cancelar para ignorar esa solucin.

10 de 76

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

11 de 76

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 ms exacto con estos parmetros de iteracin.

Mnimo de Unidades VendidasSe desea determinar el nmero 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

12 de 76


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

Resolver EcuacionesBuscar Objetivo permite resolver ecuaciones de una variable. En el ejemplo siguiente primero se debe cambiar la opcin de exactitud de las iteraciones.

Se debe buscar el valor de la celda A2 (o X) que haga la ecuacin 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.

13 de 76


Bsqueda de Objetivos con GrficosSi existen datos en un grfico 2D de barras, columnas, lneas o XY, se puede utilizar Bsqueda de Objetivo usando el Mouse, arrastrando el marcador de datos del grfico a una nueva posicin. Si este corresponde a una frmula el Buscador de Objetivo encuentra los valores adecuados que se deben ingresar en la frmula. Ejemplo: Se desea ahorrar cierta suma anualmente de modo que en 10 aos se pueda tener $ 150.000. Si se asume una tasa de inters constante, cuanto se debe ahorrar o depositar anualmente para alcanzar la meta indicada? Se supone una tasa de inters de 5%, e inicialmente un valor de depsito anual de $ 5.000. La frmula corresponde a VF: Devuelve el valor futuro de una inversin basndose en pagos peridicos constantes y en una tasa de inters constante. Sintaxis: VF(tasa;nper;pago;va;tipo). El tipo de depsito es cero, o sea pago al final del perodo, por ello en el ao cero (al inicio) es cero: Se grafica hasta 10 aos, pero se ponen datos mas all de 10 aos para tener un rango de clculo ms amplio.

14 de 76


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:

15 de 76

GUIA MINI CURSO EXCEL AVANZADO Al soltar aparece:

La celda es B2, el valor anual a ahorrar.

Al dar aceptar el grfico se modifica:

16 de 76


El valor de depsito Anual Buscado es $ 11.926

EscenariosQue es: Un conjunto particular de valores, suposiciones, hiptesis que se definen en un modelo de clculo en Excel. Los anlisis 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: Anlisis Hipotecario: Este tipo de anlisis se utiliza para decidir el monto del pie, el perodo del prstamo, 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.

17 de 76


El pago mensual corresponde a la frmula: =PAGO(Tasa_Interes/12;Plazo*12;Precio_Casa-Pie_Inicial) Con pago extra es: =B12+Pago_Extra_Mes El plazo corregido corresponde a la frmula: =NPER(Tasa_Interes/12;C12;Precio_Casa-Pie_Inicial)/12. o sea el nmero de perodos para financiar el prstamo (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 pagara en total si se aumenta el plazo al mximo permitido, no hay pie y no hay pago extra mensual. Que pasa en una alternativa de trmino medio, o sea plazo razonable, pie y un valor adecuado adicional. Preparacin de hoja para crear escenarios

18 de 76


Utilizaremos 3 escenarios Mejor Pie Inicial: 20.000 Plazo: 20 Pago Extra: 100 Medio Pie Inicial 15.000 Plazo 25 Pago Extra -50 Peor Pie Inicial: 10.000 Plazo 30: Pago Extra: 0

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

19 de 76


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

20 de 76


Ingresar los otros 2 escenarios. Mostrar el Peor:

21 de 76

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:

22 de 76


Slo las celdas de resultados

Tabla Dinmica 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

23 de 76


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)

24 de 76


SOLVERUtilizando SolverMargen cero para 2 productos. Se debe determinar el nmero 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 disminucin de costos al elaborar los dos productos juntos Margen cero pero agregando disminucin de costos por elaboracin y transporte de ambos productos juntos. Se considera una disminucin de costo de $ 1 por cada unidad vendida del otro producto. La frmula 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:

25 de 76


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).

26 de 76


Solver pudo encontrar una solucin en que los mrgenes de ambos productos son cero.

27 de 76

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 informacin acerca de las mismas.

Informe de Sensibilidad Facilita informacin acerca de la sensibilidad de la solucin a que se realicen pequeos cambios en la frmula 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.

28 de 76

GUIA MINI CURSO EXCEL AVANZADO Informe de Lmites Muestra una lista con la celda objetivo y las celdas ajustables con sus valores correspondientes, los lmites inferior y superior as como los valores del objetivo. No se genera este informe para los modelos que tengan restricciones enteras. El lmite inferior es el valor mnimo que puede tomar la celda ajustable mientras se mantienen todas las dems celdas ajustables fijas y se contina satisfaciendo las restricciones. El lmite superior es el valor mximo.

29 de 76


El Problema del TransporteSu objetivo bsico es minimizar los costos de transporte de mercaderas desde varias plantas de produccin hacia varias bodegas distribuidas en un pas. 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)

30 de 76


31 de 76


32 de 76


Tablas DinmicasIntroduccinUtilicemos el archivo Cuentas Banco:

Datos, Informe de Tablas y Grficos Dinmicos

Siguiente

33 de 76


Detecta automticamente el rango de la tabla Siguiente:

Hay dos posibilidades: Diseo o Finalizar. Diseo:

34 de 76


35 de 76


Finalizar: Utilizar Lista de campos de tabla dinmica

El men de Lista de campos de tabla dinmica permite distribuir los campos directamente en la hoja de clculo. Elegir un campo, un rea y dar clic en Agregar a

36 de 76


No es obligatorio enviar todos los campos, slo los que correspondan al anlisis a realizar Para sacar campos simplemente arrastrarlos fuera de la tabla En el rea de datos: Valores numricos: se les aplica la funcin suma Valores no numricos: se aplica la funcin contar

Agrupar tems de una Tabla Dinmica Utilicemos el archivo Ventas por Fecha:

En el caso de fechas, Excel puede crear los grupos automticamente:

37 de 76

GUIA MINI CURSO EXCEL AVANZADO Agrupar por Meses Crear la tabla: Fecha: agregar a filas Ventas: agregar a rea de datos

Botn derecho en Fecha: Agrupar y Mostrar Detalle, Agrupar Seleccionar Aos y Meses:

38 de 76

GUIA MINI CURSO EXCEL AVANZADO O puede ser:

Campos o tems Calculados Campo CalculadoEs un nuevo campo creado a partir de los campos de la tabla. Slo puede pertenecer al rea de datos

tem CalculadoUsa los contenidos de otros tems dentro de un campo. Slo puede pertenecer a las reas de pgina, filas o columnas. Los ejemplos utilizan el archivo Ventas Vendedor Regin Meses:

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

39 de 76

GUIA MINI CURSO EXCEL AVANZADO Barra de Tablas Dinmicas, Tabla Dinmica, Frmulas, Campo Calculado:

Dar Nombre a campo: PrecioProm, en Frmula despus 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 Dinmica:

40 de 76


Campo aparece como Suma de PrecioProm.

Se puede cambiar nombre a Precio Promedio, sobre el nombre, botn derecho, configuracin de campo, nombre: Precio Promedio:

41 de 76


El campo es agregado en el diseo al rea de datos:

Este campo no se puede mover a las reas de pginas, filas o columnas.

Crear un tem CalculadoEl tem calculado usa los contenidos de tems en un mismo campo El archivo Ventas Vendedor Regin 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 Dinmica, Frmula, Elemento Calculado

42 de 76


Nombre: Trim I Frmula: =Jan+Feb+Mar o ir insertando los elementos e ingresando los signos +. Finalizar con Sumar , Aceptar. La frmula puede usar tems de otros campos, pero no funciones de hojas de clculo. El tem calculado es agregado al final. Despus se puede mover hacia la posicin deseada, arrastrndolo o mediante: Barra de Tabla, Ordenar, Subir.

Formatear toda el rea de datos con botn derecho, Formato de Celdas, Moneda. Destacar con negrita datos de nuevo campo:

43 de 76


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

NotasPara 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 posicin se puede hacer con el mismo asistente, donde desea situar, elegir y finalizar.

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

44 de 76


Seleccionar Presupuesto.mdb Si se desea hacer un anlisis de los presupuestos remanentes seleccionamos por ejemplo: Divisin, Departamento, Categora, Mes y Remanente

45 de 76


Siguiente, siguiente, siguiente, Finalizar:

Se puede volver a revisar o cambiar en el botn: Obtener datos Siguiente, Hoja de Clculo nueva: Opciones antes de crear la tabla:

46 de 76


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 Disear la tabla:

47 de 76


Leer directamente desde datos externosAyuda ms aun en el caso de problemas de memoria. Es vlido para los campos de pgina de la tabla y permite consultar directamente la base de datos al seleccionar cada elemento: Doble clic sobre DIVISIN, Campo de la Tabla Dinmica y Avanzadas:

Desmarcar Recuperar y Deshabilitar

Desaparece la opcin (Todas) del campo Divisin y al seleccionarlo no se ve Mostrar Todo 48 de 76


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

Utilizar un archivo de conexin de datos odcDebe crearse primero el archivo odc: Datos, Obtener Datos Externos, Importar Datos, Conectar con nuevo origen

49 de 76


Elegir DSN (Data Source Name)

Elegir Access

Seleccionar la base de datos, siguiente:

50 de 76


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, Diseo:

51 de 76


Rangos de Consolidacin MltiplesArchivo: Ventas de Pacific Instruments

52 de 76


Solicita los rangos

Se debe especificar cada rango de datos.

53 de 76


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

Despus de ingresar todos los rangos, Siguiente, Hoja de Clculo Nueva, Finalizar:

54 de 76


55 de 76


Rangos de Consolidacin Mltiples, Campos de Pgina PersonalizadosArchivo: Libros por Trimestres Crear tabla dinmica con rangos de consolidacin mltiples:

Agregar los rangos Definir 2 campos de pgina 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.

56 de 76


Siguiente, dilogo de crear la tabla, Finalizar Se crea la tabla con 2 campos de pgina: En Pgina 1 puede elegir los aos y en Pgina 2 los trimestres

57 de 76


MACROS

Grabacin de MacrosUna Macro es una lista o conjunto de instrucciones que le indican a Excel realizar una o mas acciones sin intervencin 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: Otro: Definir opciones especiales de impresin en la opcin configurar pgina e imprimir el documento. Ingresar una serie de datos en una fila de una hoja de clculo Centrar los datos en cada celda Aplicar un borde y dar formato a la fila.

Las macros pueden ser interactivas y solicitar informacin al usuario y actuar de acuerdo a esa informacin. Formas de crear una macro: Grabar la macro Construirla ingresando instrucciones en un mdulo

En ambos casos las instrucciones son codificadas en el lenguaje de programacin Visual Basic para Aplicaciones (VBA). La alterativa de grabar la macro, sirve slo para grabar acciones que posteriormente se repetirn al ejecutar la macro. Existen tareas que no se pueden grabar, por ejemplo la creacin de ventanas de dialogo, creacin de nuevos comandos o funciones y procesamiento de informacin en varios libros de Excel.

Que Se Puede Hacer con VBAVBA es un lenguaje de programacin muy completo con innumerables usos. A continuacin una lista con algunas cosas que se pueden hacer con macros de Excel Insertar una cadena de texto o frmula: Si se desea ingresar un nombre, direccin u otros datos repetidamente una macro puede ingresarlos automticamente. Esto tambin lo hace la funcin de auto correccin.

58 de 76

GUIA MINI CURSO EXCEL AVANZADO Automatizar un procedimiento frecuente: Preparacin 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 mens y ejecutarlo con una combinacin de teclas o un clic del mouse. Crear un botn personalizado de barras de herramientas: Escribir o grabar una macro y crear en una barra de herramientas un botn personalizado que la ejecute. Tambin 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 funcin: Se pueden crear funciones personalizadas o adecuadas a nuestro trabajo, que permiten simplificar mucho las frmulas. Varias operaciones matemticas o que incluyan funciones de Excel. Crear aplicaciones completas manejadas por macros: Las macros de Excel pueden presentar cuadros de dilogo personalizados y agregar nuevos comandos a los mens y barras de herramientas. Crear programas complementarios personalizados para Excel: Mucho de los complementos agregados a Excel son basados en Macros. .

Desventajas de VBAGran cantidad de comandos, sobre 1000, cuesta tener una visin amplia de VBA. Ello dificulta el aprendizaje. Cuesta mucho encontrar el mtodo o propiedad adecuados para realizar ciertas acciones. Cdigo ms largo que otros lenguajes de Macros.

59 de 76


Dos Tipos de Macros VBAEs bsico entender una definicin clave: Una Macro de VBA puede ser de 2 tipos: 1. Procedimiento Sub 2. Funcin

Procedimientos SubSe 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. Definicin 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 especficas pero no devuelven ningn 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 instruccin Sub debe incluir un par de parntesis vaco.

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 parntesis (). Los parntesis estn vacos a menos que contengan argumentos. La sentencia End Sub indica el fin del procedimiento. Las lneas entre Sub y End Sub comprenden el cdigo del procedimiento. La macro tambin incluye un comentario: Inserta la fecha actual en la celda activa, el cual es precedido de apstrofe y queda en color verde. El comentario

60 de 76

GUIA MINI CURSO EXCEL AVANZADO puede ir despus de los comandos ya que al encontrar apstrofe VBA ignora el resto del texto.

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

Presionar la combinacin de teclas del procedimiento o teclas de mtodo abreviado (si tiene uno). Macro Opciones, en este caso no lo tiene:

61 de 76

GUIA MINI CURSO EXCEL AVANZADO Si el editor de Visual Basic esta activo, dejar el cursor en cualquier parte dentro del cdigo 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 VBAEl segundo tipo de procedimientos VBA es Function. Function siempre entrega un valor nico (igual que una funcin de la hoja de clculo). Un procedimiento Function de VBA puede ser ejecutado por otros procedimientos de VBA o ser usado en frmulas, tal como se usan las funciones incluidas en Excel. Definicin 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 funcin puede devolver adems un valor. Un procedimiento Function acepta argumentos, como pueden ser constantes, variables o expresiones que le pasa el procedimiento que efecta la llamada. Si un procedimiento Function no tiene argumentos, la instruccin Function debe incluir un par de parntesis vacos. Una funcin devuelve un valor asignndolo a su nombre en una o ms instrucciones del procedimiento. A continuacin se muestra un cdigo de ejemplo:

62 de 76


El procedimiento se llama RaizCubica y tiene un solo argumento. Calcula la raz cbica de su argumento. La diferencia respecto a un procedimiento Sub es que empieza con Function y termina con End Function. Se utiliza como una funcin ms

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

63 de 76

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

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

64 de 76


Creando macros de VBAExcel provee dos mtodos para crear macros: Activar la grabadora de macros y grabar las acciones: (Herramientas, Macros)

Ingresar el cdigo directamente en un mdulo de VBA

Grabacin de MacrosHerramientas, Macros, Grabar nueva macro:

65 de 76


66 de 76

GUIA MINI CURSO EXCEL AVANZADO Nombre de la Macro: Excel propone nombres como Macro1, Macro2 Mtodo Abreviado: Combinacin de teclas que ejecuta la macro, es opcional. La combinacin siempre usa la tecla Ctrl., se puede presionar adems Mayus (Shift), si se quiere una letra con maysculas. Los comandos son sensibles a maysculas y minsculas. Descripcin: Por defecto pone fecha y nombre, es modificable. Para comenzar a grabar las acciones dar Aceptar, aparece en seguida la barra de Detener Grabacin con la opcin detener grabacin

Al finalizar de grabar las acciones en Excel se presiona el botn Detener grabacin o Herramientas, Macro, Detener Grabacin:

Nota: Al grabar Macro siempre resulta un Procedimiento Sub. Una funcin 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 combinacin de teclas Ctrl+Mayus+F, ingresando F en el cuadro de mtodo abreviado. Dar Aceptar, se muestra la barra llamada Detener Grabacin Realizar las acciones: Tipo de letra Arial 16, Negrita, Color de Texto Azul Detener grabacin. Se ha completado la creacin de la macro. 67 de 76


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 cdigo creado en base a la grabacin anterior se guarda en el Mdulo1 en el libro actual. Analizando, el cdigo consiste en: Sub MacroFormato1() MacroFormato1 Macro Macro grabada el 21-08-2007 por Juan Schleyer Acceso directo: Ctrl+Mays+F With Selection.Font .Name = Arial .FontStyle = Negrita .Size = 16 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False

68 de 76

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 apstrofe 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 caracterstica de los tipos de letra aun cuando estas no se hayan cambiado.

69 de 76


Testeando la MacroPara retornar a Excel se puede mediante Alt-F11 de nuevo o el botn 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 despus se comienza a grabar, si se realiza despus, estas celdas quedan definidas dentro de la macro y siempre va a formatear las mismas celdas, por lo cual no seria una macro de propsito general. Si se abre otro libro y se invoca Macros, por defecto aparece la opcin Macros en todos los libros abiertos. Si en Macros en se elige Este libro y el nuevo no contiene macros, no se podra llamar la macro Macroformato1.

70 de 76


Editando la MacroPor ejemplo podemos cambiar el tipo de letra y tamao, se puede re grabar la macro, pero como es una simple modificacin es preferible editarla con Alt-F11 y seleccionar el mdulo1 o Herramientas, Macro, Macros, Modificar Se pueden eliminar las lneas .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-Mays-F

71 de 76


La macro funciona igual.

72 de 76


Otro Ejemplo de macro VBAMacro Ejemplo 2 Consiste en convertir frmulas en valores. Este es un proceso de 2 pasos: 1. Edicin, Copiar 2. Edicin, Pegado especial, Valores La macro a realizar combina estos pasos en uno slo. Se le asignar la combinacin Ctrl+Mayus+V Pasos para crear la macro 1. Ingresar una frmula cualquiera en una celda, por ejemplo en A3: A1+A2 2. Seleccionar la celda que tiene la frmula 3. Herramientas, Macro, Grabar Nueva Macro. Excel despliega el dilogo grabar macro:

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

9. Presionar el botn Detener Grabacin

73 de 76


Selection.Copy: Copiar la seleccin Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _SkipBlanks:=False, Transpose:=False Pega el contenido del portapapeles en la seleccin 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:

Grabacin de Macros, Absoluta o RelativaNormalmente al grabar se guardan las referencias reales a las celdas, (grabacin 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 posicin de la celda activa. Grabar en forma relativa, presionar botn indicado:

Sin referencia relativa:

Con referencia relativa:

(presionado).

Notar que el botn queda como estaba relativo o no, para la siguiente macro.

74 de 76

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 PersonalUna de las opciones para grabar macros es libro de macros personal. Las macros grabadas en este libro estn siempre disponibles ya que se inicia con Excel.

Al utilizar esta opcin 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 automticamente con Excel) Este libro est oculto y se muestra con Ventana, Mostrar:

75 de 76


En ventana se muestra el libro:

Al utilizar este libro el cdigo de macros se almacena en:

Al salir de Excel si se usa el libro de macros personal

76 de 76