16
Guía Práctica MS-Excel Ing. Reynaldo Sucari León 3 MS-EXCEL GRAFICOS Y MACROS 1. MOSTRAR DATOS EN GRAFICO Seleccione el rango de datos que desea incluir en el gráfico. Haga clic en el comando Insertar del menú Grafico. Microsoft Excel muestra el cuadro de dialogo Asistente para gráficos. Tipos estándar elija un tipo de gráfico. elija un subtipo de grafico. Clic en Siguiente>. elija en Rango de datos Series en: Columnas Clic en Siguiente>. luego en el paso 3 de 4 clic en Siguiente> . En el paso 4 de 4 clic en Finalizar. Microsoft Excel inserta un grafico en la hoja de cálculo. Revisar Tipos Personalizados, en la misma ventana de Asistente para gráficos. Encontrara más gráficos al gusto del usuario Desplazar un Gráfico Para desplazar, cambiar el tamaño o eliminar un gráfico, selecciónelo haciendo clic en cualquier parte del gráfico y arrástrelo hacia una nueva posición. Objetivos: Crear Gráficos. Añadir texto al gráfico. Editar el texto de un gráfico. Insertar una imagen en el gráfico.

Excel Avanzado

Embed Size (px)

DESCRIPTION

Excel Avanzado

Citation preview

Page 1: Excel Avanzado

Guía Práctica MS-Excel

Ing. Reynaldo Sucari León 3

MS-EXCELGRAFICOS Y MACROS1. MOSTRAR DATOS EN GRAFICO

2.1. CREAR UN GRAFICO EN LA HOJA DE CÁLCULO

ÿ Seleccione el rango de datos que desea incluir en el gráfico.

ÿ Haga clic en el comando Insertar del menú Grafico. Microsoft Excel muestra elcuadro de dialogo Asistente para gráficos.

ÿ Tipos estándar elija un tipo de gráfico.ÿ elija un subtipo de grafico. Clic en

Siguiente>.ÿ elija en Rango de datos Series en: Columnasÿ Clic en Siguiente>.

ÿ luegoen elpaso 3 de 4 clic en Siguiente> .

ÿ En el paso 4 de 4 clic en Finalizar. MicrosoftExcel inserta un grafico en la hoja de cálculo.

ÿ Revisar Tipos Personalizados, en la misma ventana de Asistente para gráficos.Encontrara más gráficos al gusto del usuario

Desplazar un Gráfico

ÿ Para desplazar, cambiar el tamaño o eliminar ungráfico, selecciónelo haciendo clic en cualquierparte del gráfico y arrástrelo hacia una nuevaposición.

Objetivos:ÿ Crear Gráficos.ÿ Añadir texto al gráfico.ÿ Editar el texto de un gráfico.ÿ Insertar una imagen en el gráfico.

Page 2: Excel Avanzado

Informática Guía Práctica

Ing. Reynaldo Sucari León4

Agregar datos al gráfico

ÿ Cuando el gráfico este en una hoja de cálculo, haga clic en el para ver los rangoscodificados con colores.

Arrastre el controlador de relleno para seleccionarlos datos que desea agregar.

Los datos del gráfico permanecen vinculados a los datos fuente y se actualizanautomáticamente cuando cambian en la hoja de cálculo...y los nuevos datos aparecerán inmediatamente en el gráfico.

Nota: Si agrega datos incorrectos, puede quitarlos con el botón Deshacer. Si deseaeliminar una serie de datos utilice el controlador de relleno o selecciónela en el gráficoy presione la tecla Delete (Supr).

Selección de rangos contiguos y no contiguos

Si deseamos crear un gráfico que represente las ventas realizadas por cada sucursalen los trimestres primero y segundo es necesario seleccionar un rango contiguo.ÿ Seleccione el rango A2:C6 e Inserte un gráfico.

M Excel crea un gráfico que representa lasventas realizadas por cada sucursal en lostrimestres Trim1 y Trim2.

Page 3: Excel Avanzado

Guía Práctica MS-Excel

Ing. Reynaldo Sucari León 5

ÿ Ahora si solo necesita representar los datos de los trimestres Trim1, Trim3 yTrim4, seleccione un rango no contiguo.

ÿ Primero seleccione el rango A2:B6, mantenga pulsada la tecla Ctrl. Mientrasselecciona el rango D2:E6.

…Luego ir a menú Insertar /Gráficoen rango de datos columnas, paracrear un nuevo gráfico querepresente solamente los datos de lasventas realizadas en los trimestresTrim1, Trim3 y Trim4.

3.2. TIPOS DE GRAFICO

Microsoft Excel le proporciona 14 tipos de gráficos para la representación de losdatos. Con los datos anteriores crear nuevos tipos y subtipos de gráficos.

Grafica de Barras Grafica de lineasSe usa para representar datos individuales Muestra tendencia o cambios enlos datos en un

Periodo de tiempo y a intervalos regulares.

Grafico Circular. Se usa para representar las relaciones de las partes con el todo enuna única serie de datos

Page 4: Excel Avanzado

Informática Guía Práctica

Ing. Reynaldo Sucari León6

3.3. AÑADIR TEXTO AL GRAFICO

M Excel permite añadir dos tipos de texto a un gráfico, texto asignado y texto noasignado (texto libre y sirve para incluir comentarios que capten interés del lector).

Texto asignado

ÿ Para incluir texto asignado en un gráfico, haga clic dentro del grafico, luegoejecute el comando Opciones de gráfico del menú Grafico. Microsoft Excelmuestra el cuadro de diálogo Opciones de gráfico:

El cuadro de dialogo presentadiversas pestañas. Titulo, Eje,Líneas de División, Leyenda,Rótulos de datos, Tabla de datos

La pestaña Título

ÿ Seleccione esta pestaña e introduzca lo sgte:ÿ En titulo del gráfico, Exportaciones Galerías Apurimac.ÿ En eje de categorías (X),

Productos.ÿ En eje de categorías (Y), Ventas.

Grafico con títulos, eje de categorías (X), y ejede valores (Y) .Gráfico al que se le ha ocultado los rótulos deleje de categorías (eje Y).

Page 5: Excel Avanzado

Guía Práctica MS-Excel

Ing. Reynaldo Sucari León 7

La pestaña Eje

ÿ En el mismo cuadro de Opciones deGráfico, M Excel añade rótulos enlos ejes X e Y. estos ejes aparecena lo largo de los ejes.

La pestaña Rótulos de datos

Los rótulos de datos aparecen junto a las marcas de datos y sirven para identificarlas,o para señalar su valor exacto.

ÿ Para añadir estos rótulos al grafico hagaclic en la pestaña Rótulos de datos y,seguidamente, seleccione la opciónMostrar valor para que aparezcan losvalores de todos los puntos de datos.

La pestaña Leyendaÿ Haga clic en esta pestaña y M Excel le

mostrará varias opciones que le permitancambiar la ubicación de la leyendadentro del gráfico.

Texto no asignado

ÿ Pulse la tecla Esc para deseleccionar cualquier objeto del gráfico.ÿ Escriba la frase “Exportación realizada en el año 1999”.ÿ Observe que al escribir el texto, este aparece en la barra de fórmulas.

Page 6: Excel Avanzado

Informática Guía Práctica

Ing. Reynaldo Sucari León8

ÿ Para cambiar la posición de este texto, desplace el puntero del Mouse sobre elborde del cuadro que rodea al texto y arrástrelo hacia una nueva posición.

Dar formato al texto

Excel permite modificar el formato de cualquier texto que aparezca en el gráfico,tanto si es texto asignado o no.

ÿ En el menú Gráfico, Opciones de gráfico, en la pestaña Titulo Agregar en titulo;“Exportación realizada en el año 1999”, en eje; X “sucursales”, y en Y “ventas”,luego Aceptar.

ÿ Desplace el puntero del mouse sobre eltítulo de gráfico y haga clic. En el menúcontextual que aparece y seleccione laopción Formato del titulo del gráfico.

En este cuadro de dialogo, aplique una trama,modifique la fuente o alinee el texto del título.Finalmente haga clic en Aceptar.

3.4. INSERTAR UNA IMAGEN EN EL GRÁFICO

ÿ Sitúe el puntero del mouse en una zona vacía del área de trazado y haga clic con elboton derecho del mouse (menú contextual).

ÿ Seleccione la opción Formato de área de Trazado.

Page 7: Excel Avanzado

Guía Práctica MS-Excel

Ing. Reynaldo Sucari León 9

ÿ En Tramas haga clic en Área/Efectos de relleno.

ÿ En el cuadro de Efectos de relleno, enla ficha Imagen, clic en SeleccionarImagen.

ÿ Seleccione una imagen, clic enInsertar.

ÿ Finalmente, haga clic en Aceptar dosveces. Hasta ver el grafico con laimagen insertada

ÿ Alrededor del área de trazado hay unazona en blanco que incluye el texto y los ejes (Área del Grafico). Si lo deseaspuedes cambiarlo utilizando el mismo procedimiento anterior.

EJERCICIOS

Page 8: Excel Avanzado

Informática Guía Práctica

Ing. Reynaldo Sucari León10

MACROS EN EXCELOBJETIVOS

• Definiciones• Automatizar tareas a través del uso de las macros.• Grabar• Ejecutar• Manipular macros.• Tipos de Macros en Excel

INTRODUCCIÓNLas operaciones tradicionales que se pueden realizar en Excel para Windows laspodemos automatizar, de manera que se mantenga un registro que las contenga yposteriormente hacer referencia a ellas para la simplificación de tareas.

Excel es un programa muy potente, pero la mayoría de las personas lo maneja deuna forma muy básica, Excel cuenta con un lenguaje de programación llamadoVisual Basic, y permite hacer o resolver los problemas de forma más rápida ysencilla, este curso es de un nivel alto, pero es fácil de aprender ya que semanejaran términos sencillos.

Una Macro son una serie de pasos que se almacenan y se pueden activar conalguna tecla de control y una letra, un botón o manualmente.

Por ejemplo, si repetidamente se emplea una serie de pasos como: Cambiar elTamaño de Fuente o Tamaño de la letra, poner Negrita, cambiar la Fuente o Tipode letra y el Color de Fuente o Color de Letra, para no estar repitiendo estos pasosse pueden almacenar en una macro y se ejecutara la macro las veces que sedesee.

En resumen una macro consiste en una serie de comandos y funciones que sealmacenan en un módulo de Microsoft Visual Basic y que puede ejecutarsesiempre que sea necesario realizar la tarea.

Para esto Excel para Windowscuenta con dos herramientasbásicas: las macros y losmódulos. En esta sección serevisarán las macros. Paraexplicar lo que es una macro,vamos a hacer una analogíaentre un micrófono y una cintacon la computadora y lagrabadora de macros.

Page 9: Excel Avanzado

Guía Práctica MS-Excel

Ing. Reynaldo Sucari León 11

La grabadora de macros funciona de manera similar a una grabadora de cintas.Una graba lo que la persona dice, otra graba lo que el usuario hace.

Grabar MacroAntes de grabar o escribir una macro hay que tener en cuenta los siguientespuntos:

1. Planifique los pasos y los comandos que desea que ejecute la macro2. Si se comete algún error mientras se graba la macro, también se grabarán

las correcciones que se realicen.3. Cada vez que se grabe una macro, ésta se almacenará en un nuevo módulo

adjunto a un libro.

Al grabar una macro, Excel almacena información sobre cada paso dado cuandose ejecuta una serie de comandos. A continuación, se ejecuta la macro para querepita los comandos. Si se comete algún error mientras se graba la macro, tambiénse graban las correcciones que se realicen. Visual Basic almacena cada macro enun nuevo módulo adjunto a un libro.

Para grabar una macro seleccione del menú Herramientas el comando Grabarmacro y después del submenú Grabar nueva macro.

Al hacerlo aparece la siguiente caja de diálogo:

Page 10: Excel Avanzado

Informática Guía Práctica

Ing. Reynaldo Sucari León12

Se debe asignar un Nombre de la macro junto con una Descripción, aunqueExcel para Windows siempre asigna estos predeterminados.

Almacenar en son los posibles lugares donde se puede alojar la macro que se estágrabando, ya sea en Este libro, en un Nuevo libro o en el Libro de macros personal.

En la opción Método Abreviado aparece que se activara con la tecla Control(CTRL) + laletra que usted indique, Ejemplo m. La macro se activara cuando este lista con la teclaControl + m

Al presionar Aceptar la grabación se inicia. En este momento, todas lasacciones que realice se estarán registrando por Excel para Windows. Parafinalizar la grabación seleccione del menú Herramientas el comando Grabarmacro y después del submenú Finalizar grabación, o presione el botón queaparece al estarse grabando una macro.

REFERENCIAS RELATIVAS

Las macros se pueden grabar ya sea con referencias absolutas o relativas. Para indicarlo,seleccione del menú Herramientas el comando Grabar macro y después del submenúUsar referencias relativas.

A menos que esta opción esté desactivada, la Grabadora de macros usaráreferencias relativas, no absolutas, para las celdas de una hoja de cálculo.Botón de finalizar Grabación Botón de Referencias Relativas

EJECUTAR UNA MACRO

Para entender la forma de ejecutar una macro podemos utilizar otra vez la analogíacon la grabadora. La voz que se reproduce en la grabadora se escucha en labocina, y de la misma forma, la macro que se reproduce en la grabadora se ejecutaen Excel para Windows.

Page 11: Excel Avanzado

Guía Práctica MS-Excel

Ing. Reynaldo Sucari León 13

La ejecución de la macro se puede hacer de distintas formas. Si tiene asignadouna combinación de teclas de método abreviado con éstas se puede iniciar laejecución.

Se puede ejecutar una macro seleccionándola de una lista en el cuadro de diálogoMacro. Para que una macro se ejecute cada vez que haga clic en un botóndeterminado o presione una combinación de teclas específica, asigne la macro aun botón de la barra de herramientas, a un método abreviado de teclado o a unobjeto gráfico de una hoja de cálculo.

Otra forma de ejecutar una macro es utilizando del menú Herramientas elcomando Macro.Al seleccionar esta opción aparece la siguiente caja de diálogo:

De las macros listadas, se selecciona la deseada y se presiona Ejecutar. Tambiénse puede ejecutar Paso a paso para revisar los procedimientos que va realizando.Además de estas opciones, se tienen otras más que se revisarán más adelante.

MANIPULACIÓN DE MACROSTras grabar una macro, se puede ver el código de macro con el Editor de VisualBasic para corregir errores o modificar lo que hace la macro. Por ejemplo, si lamacro de ajuste de texto también tiene que aplicar el formato de negrita al texto, sepuede grabar otra macro para aplicar el formato de negrita a una celda y, acontinuación, copiar las instrucciones de esa macro a la macro de ajuste de texto.

El Editor de Visual Basic es un programa diseñado para que los usuariosprincipiantes puedan escribir y editar fácilmente código de macro, y proporcionamucha Ayuda en pantalla. No es preciso saber cómo se programa o se utiliza ellenguaje de Visual Basic para realizar cambios sencillos en las macros. El Editor deVisual Basic permite modificar macros, copiarlas de un módulo a otro, copiarlasentre diferentes libros, cambiar el nombre de los módulos que almacenan lasmacros o cambiar el nombre de las macros.

Page 12: Excel Avanzado

Informática Guía Práctica

Ing. Reynaldo Sucari León14

Con el comando Macro del menú Herramientas, la misma que se vio en el temaanterior, podemos realizar varias acciones más.

Editar abre la macro o el módulo de Visual Basic para poder realizar ediciones del códigoque se generó. Si se grabó en el Libro de macros personal se tiene que utilizar elcomando Mostrar del menú Ventana para aparecer el libro "Personal" que está oculto.

También podemos Eliminar la macro o el procedimiento seleccionado.

SEGURIDAD DE MACROSExcel incluye protecciones contra virus susceptibles de ser transmitidos pormacros. Si se comparten macros con otros usuarios, se puede certificar esasmacros con una firma digital de modo que los demás usuarios pueden comprobarque proceden de una fuente fidedigna. Al abrir un libro que contiene macros, sepuede comprobar su origen antes de habilitarlas.

TIPOS DE MACROS

Macros automáticasExisten varias macros previamente creadas para Excel para Windows. Estándisponibles en el menú Herramientas opción Complementos. Al seleccionar estaopción aparece la siguiente caja de diálogo:

Con esta opción se especifican las macros que están disponibles y listas parausarse al iniciarse Excel para Windows. Se pueden instalar o eliminar las macrosautomáticas que vienen con Excel para Windows, o las que se hayan creado.

Aquí se muestran las Macros automáticas disponibles. Pueden seleccionarse oborrarse las casillas de verificación de las macros automáticas a fin de instalar oeliminar las mismas en Excel para Windows. Con el botón Examinar se muestrauna caja de diálogo similar a la de Abrir archivo donde puede seleccionarse unamacro automática para ser instalada en Excel para Windows.

Además de la macros grabadas se pueden crear códigos completos de macroinstrucciones que son una serie de instrucciones que permiten automatizarprocesos Sencillos, Avanzados y/o Complejos. Otra utilidad de este tipo de macros

Page 13: Excel Avanzado

Guía Práctica MS-Excel

Ing. Reynaldo Sucari León 15

es que permite, ajustar una macro (como las que se crearon en la sección anterior)a las necesidades del trabajo que se necesite realizar. Haciendo el trabajo de 5 a 8veces más rápido el trabajo cotidiano e inclusive en algunas ocasiones más rápido.

Algunos ejemplos de lo que podemos hacer con las macros son:• Procesos de captura de información.• Procesos de impresión.• De análisis de datos.• De manejo de base de datos.• Elaboración de reportes.• Simplificación de tareas.• Tareas que son repetitivas, que hay que realizar varias veces el mismo

procedimiento.• Un sistema de información.• Sistemas administrativos.

Están disponibles en el menú Herramientas opción Asistente.

MACROS DE EXCELLas macros se pueden agrupar en dos categorías principalmente:Macros de funcionesMacros de comandos

Macros de funciones

Si ejecuta frecuentemente una tarea en Microsoft Excel, puede automatizarlamediante una macro. Una macro consiste en una serie de comandos y funcionesque se almacenan en un módulo de Visual Basic y que puede ejecutarse siempreque sea necesario ejecutar la tarea. Una macro se graba igual que se grabamúsica en un casete. A continuación, se ejecuta la macro para que repita loscomandos.

Con el Editor de Visual Basic, se pueden modificar macros, copiar macros de unmódulo en otro, copiar macros entre diferentes libros, cambiar de nombre a losmódulos que almacenan las macros o cambiar de nombre a las macros.

Macros de comandosLa creación de estas macro funciones consiste en poner una serie deARGUMENTOS, en las cuales podemos ir poniendo los datos que lleva unafunción normal de Excel para Windows y al final le indicamos que operacioneshacer con estos ARGUMENTOS y de esta manera se optimiza el uso de variasfórmulas para llegar a un resultado. Es decir la creación de una función simplifica eluso de varias fórmulas, dejándolas como una sola.

Dentro de la codificación de macros de Excel para Windows se tiene dos gruposde códigos. Visual Basic y Macros de Excel

Page 14: Excel Avanzado

Informática Guía Práctica

Ing. Reynaldo Sucari León16

MACROS DESDE VISUAL BASIC

Es un lenguaje (Codificación), que se puede utilizar, como lenguaje común entrelos diferentes programas que se tiene en el Microsoft Office, cabe mencionar queestá basado en un Lenguaje llamado Visual Basic, pero no tiene mucho que vercon él.

La macros de Visual Basic están basadas en módulos, que por tal hecho sevuelven más complicadas, teniendo como ventaja que son compatibles dentro devarias aplicaciones, de esta manera se tendría un lenguaje común de macros entrevarios programas, con lo cual se logra que se pierda menos tiempo en realizar unamacro en alguno de estos programas. Por esta razón es que las macros de VisualBasic son vistas en un curso en particular.

EjerciciosCree las siguientes macros:

1. Macro "archivo" que se active con Control + a y que permita abrir un archivo2. Macro "seleccion" que seleccione las celdas A1:B10 de la primera hoja del

libro abierto3. Macro "letra" que ponga letra tipo arial4. Macro "color" que ponga letra de color Azul5. Macro "tamaño" que ponga letra de tamaño 146. Macro "Todo" que una las macros anteriores

Page 15: Excel Avanzado

Guía Práctica MS-Excel

Ing. Reynaldo Sucari León 17

ALGUNOS EJEMPLOS DE MACROS

Alineación izquierda/derechaSub Ajustar_izq_der()If Selection.HorizontalAlignment = xlRight Then Selection.HorizontalAlignment = xlLeftElse Selection.HorizontalAlignment = xlRightEnd IfEnd Sub

Convertir pesetas a euroSub Convertir()Set Area = Selection For Each Cell In Area z = Round(Cell / 166.386, 2) Cell.Value = z Cell.NumberFormat = "#,##0.00" Next CellEnd Sub

Pegar formatoSub PegarFormato()Selection.PasteSpecial Paste:=xlFormatsApplication.CutCopyMode = FalseEnd Sub

Pegar valorSub PegarValor()Selection.PasteSpecial Paste:=xlValuesApplication.CutCopyMode = FalseEnd Sub

Dos decimalesSub DosDec()Dim Area As RangeSet Area = SelectionFor Each Cell In Area z = Round(Cell, 2) Cell.Value = z Cell.NumberFormat = "#,##0.00"Next CellEnd Sub

Separador de milesSub SeparadorMil()Dim Area As RangeSet Area = SelectionIf Area.NumberFormat = "#,##0" Then Area.NumberFormat = "#,##0.00"Else Selection.NumberFormat = "#,##0"End IfEnd Sub

Suprimir filas vacíasSub SuprimirFilasVacias()LastRow = ActiveSheet.UsedRange.Row - 1 + _ ActiveSheet.UsedRange.Rows.CountFor r = LastRow To 1 Step -1 If Application.CountA(Rows(r)) = 0 Then

Page 16: Excel Avanzado

Informática Guía Práctica

Ing. Reynaldo Sucari León18

Rows(r).Delete End IfNext rEnd Sub

AutofilterSub FilterExcel()Selection.AutoFilterEnd Sub

GridsSub Grids()If ActiveWindow.DisplayGridlines = True Then ActiveWindow.DisplayGridlines = FalseElse ActiveWindow.DisplayGridlines = TrueEnd IfEnd Sub

Cambiar A1 a RC (columnas tiene números en vez de letras)Sub Rc()If Application.ReferenceStyle = xlR1C1 Then Application.ReferenceStyle = xlA1Else Application.ReferenceStyle = xlR1C1End IfEnd Sub

Modificar paleta de coloresSub Paleta()ActiveWindow.Zoom = 75ActiveWorkbook.Colors(44) = RGB(236, 235, 194)ActiveWorkbook.Colors(40) = RGB(234, 234, 234)ActiveWorkbook.Colors(44) = RGB(236, 235, 194)End Sub

Mostrar todas las hojasSub MostrarHojas()Set wsHoja = WorksheetsFor Each wsHoja In ActiveWorkbook.Worksheets If wsHoja.Visible = False Then wsHoja.Visible = True End IfNext wsHojaEnd Sub