excel_avanzado 17.pdf

Manual de Microsoft Excel Avanzado

Ing. Ivn Ortega Aranda 1

MANUAL DE MICROSOFT EXCEL AVANZADO

ndice

I. TABLAS ...............................................................................................................2

1.1 Tablas de una variable ....................................................................................2

1.2 Aadir mas frmulas a la tabla ...................................................................4

1.3 Tablas de dos variables ..............................................................................4

II. TABLAS DINMICAS (Pivot Tables) ...........................................................6

III. ADMINISTRACIN DE ESCENARIOS ..........................................................9

IV. MACROS ............................................................................................................11

V. ADMINISTRACIN DE VISTAS (Custom views) .....................................13

VI. ADMINISTRACIN DE INFORMES .............................................................14

VII. IMPORTACIN Y EXPORTACIN DE DATOS. ....................................16

7.1 Word.............................................................................................................16

7.2 Power Point .................................................................................................17



I. TABLAS Suponga que tenemos una frmula en la que involucramos varias variables. Si queremos ver qu resultados tenemos al cambiar el valor de una de las variables tendremos que hacerlo de una por una, o si queremos ver cmo se comporta el resultado en un rango de valores para una variable tendramos que escribir varias veces la misma frmula y hacer comparaciones. Para este problema tenemos las tablas de datos, que nos permitirn ver los rangos con ms claridad y facilidad.

1.1 Tablas de una variable Por ejemplo, suponga que tiene un recurso y desea ver su depreciacin para un periodo determinado. Utilizamos la frmula DB( ) y calculamos el periodo 8, lo que nos da $3,260.14. Si queremos ver cmo se va depreciando del periodo 5 al 10 tendremos que cambiar en la celda B5 los valores y anotar los resultados. O podemos hacer una tabla y ver cmo se va comportando la depreciacin:



1. Escriba en su hoja los valores que desea poner en la frmula. Lo puede hacer de las siguientes maneras:

Si quiere poner los valores en un rengln, inicie el rengln una celda arriba y a la derecha de la frmula

Si es por columna, inicie la columna una celda abajo y a la izquierda de la frmula.

2. Seleccione el rango que incluye los valores y la frmula (En el ejemplo:

A10:B16) 3. Seleccione Data/Table. Ver la siguiente ventana:

4. Si los valores estn por rengln, seleccione Row input cell y ponga la

direccin de la celda de la frmula que va a analizar (el Periodo). Si estn por columna, seleccione Column input cell y escriba ah la celda. En este ejemplo en la frmula el periodo est en la celda B5.

5. Presione OK.



1.2 Aadir mas frmulas a la tabla Puede aadir ms frmulas a su tabla si desea ver los resultados que obtiene para los mismos datos en varias frmulas. Por ejemplo, deseamos cambiar el tipo de cambio de Pesos a Dlares, por lo que tendremos la siguiente frmula: =B10/9.5

Para crear la nueva tabla siga los pasos anteriores asegurndose de que en el rango que seleccione en el paso 2 incluya los valores (del 5 al 10) y las dos frmulas (el rango ser A10:C16)

1.3 Tablas de dos variables Tambin puede crear una tabla que tenga dos variables de entrada. As puede ver el cambio de la depreciacin de los periodos 5 al 10 y cunto vara si cambia el valor final del recurso.



1. Escriba los valores en una columna bajo la frmula y en un rengln a la derecha de la frmula.

2. Seleccione el rango que incluye los valores de entrada y la frmula

(B10:E16) 3. Seleccione Data/Table 4. En Row input cell escriba la celda que corresponde con los valores por

rengln de la tabla (B3, Valor final). En Column input cell escriba la celda que corresponde con los valores por columna de la tabla (B5, Periodo).

5. Presione OK



II. TABLAS DINMICAS (Pivot Tables) Una Tabla Dinmica es una tabla interactiva en la que puede resumir grandes cantidades de datos. Puede rotar sus renglones y columnas para ver diferentes resmenes, filtrar los datos, etc. Utilize una tabla dinmica cuando necesite comparar totales, cuando necesite que Excel ordene, subtotalize y totalize por usted. Usted necesitar practicar con las tablas dinmicas para ver cmo se comportan, quitar y poner campos y comparar resultados. Las tablas dinmicas son complejas pero si las domina, podr ahorrarse mucho tiempo y visualizar su informacin de muchas maneras sin tener que hacer usted mismo los cambios y frmulas. Seleccione una celda de la tabla y escoja el men Data/Pivot Table and Pivot Chart Report.... Inmediatamente iniciar un wizard.

Seleccione Microsoft Excel list or database y Pivot Table Presione Next

Seleccione el rango de la tabla, incluyendo encabezados. Presione Next

Si desea que su tabla se genere en una hoja nueva seleccione New worksheet. Si no, seleccione Existing worksheet y seleccione la celda superior izquierda de la nueva tabla.

Presione Layout



Arrastre los campos para formar la tabla. Haga doble click sobre los campos que ya ha puesto en su tabla para cambiar sus opciones.

En Name escriba el Nombre como quiera que aparezca en la tabla. Seleccione en la lista el tipo de resumen que requiera (Sum, Average, etc.). Con el botn Number seleccione cmo se mostrarn sus datos en la tabla (el formato).

Presione OK para regresar a Layout Al terminar presione OK y en la ventana siguiente Finish El resultado es:

En los combos usted puede filtrar los datos de la tabla. Por ejemplo en ALUMNO en lugar de ver a todos (All) seleccione al alumno 1.



Se mostrar la barra de herramientas PivotTable:

Con el botn Pivot table wizard regresar al paso 3 del wizard y podr

cambiar su tabla en Layout. O puede hacerlo arrastrando los nombres de las columnas y renglones hacia fuera y dentro de la barra de herramientas.



III. ADMINISTRACIN DE ESCENARIOS Imagine que tiene una hoja para el clculo de las depreciaciones de un recurso, pero tiene varios recursos a los cuales desea analizar sus depreciaciones. Excel le permite utilizar una misma hoja para el registro de los recursos y cambiar sus datos segn lo requiera, sin necesidad de tener una hoja para cada recurso. Los escenarios se utilizan para esto. Usted puede especificar valo res para cada uno de sus recursos, darle un nombre y despus seleccionar el nombre de una lista. 1. Seleccione Tools/Scenarios. Presione Add para aadir un escenario.

Aparecer la siguiente ventana:

2. Escriba el nombre del escenario en Scenario name 3. En Changing cells ponga las referencias a las celdas que cambiarn en el

escenario. Seleccione rangos o ponga comas entre celdas que no son continuas (En este ejemplo $D$3, $B$2:$B$6)

4. En la caja de Comentarios escriba la descripcin del escenario. 5. Presione OK. Se ver la siguiente ventana en la que pondr los valores de

las celdas que cambiarn.



6. Para aadir el escenario presione Add. 7. Presione Close para regresar a la hoja. Ahora que tiene varios escenarios puede ver los valores que cambian al seleccionarlos y presionar el botn Show

Con Add aadir un nuevo escenario. Con Delete borra el escenario seleccionado Con Edit cambia los valores del escenario Con Merge fusiona los escenarios de otro libro al libro abierto. Con Summary crea una tabla de todos los escenarios y sus correspondientes datos.



IV. MACROS Los macros le permiten grabar acciones repetitivas para que con slo una combinacin de teclas se ejecuten. Seleccione el men Tools/Macro/Record new Macro

Escriba el nombre del Macro. En Shortcut key seleccione la combinacin de teclas que ejecutarn el macro. Recuerde que ya hay algunas macros definidas, por ejemplo Ctrl.+C es Copy, Ctrl.+B es Bold. Si usted selecciona una combinacin que ya est se reemplazar.

Si quiere que el macro est disponible en cualquier libro de Excel, en Store macro in seleccione Personal Macro Workbook. Seleccione New Workbook para guardar el macro en un libro nuevo o This Workbook para guardarlo en el libro que est abierto. Presione OK cuando haya seleccionado sus datos y se empezar a grabar el macro. Aparecer en pantalla la siguiente barra de herramientas:

Si selecciona celdas cuando est grabando el macro, el macro seleccionar las mismas celdas porque por default tiene referencia absoluta de celdas. Si quiere que se seleccionen las celdas tomando como base la celda seleccionada al momento de ejecutar el macro seleccione referencia relativa Puede seleccionar el botn Referencia relativa tantas veces como sea necesario para cambiar de referencia relativa a absoluta o viceversa. Al terminar presione el botn Stop Recording. Para borrar un macro o editarlo seleccione el men Tools/Macro/Macros

Seleccione el macro que desea borrar o editar y presione Edit o Delete. Al editar un macro usted lo har en Visual Basic, por lo que tiene que tener un poco de conocimiento de este lenguaje para cambiar su macro.



Con Options puede cambiar la combinacin de teclas del macro. Con Run ejecutar el macro sin necesidad del teclado y con Step Into ejecutar el macro paso a paso por medio de Visual Basic.



V. ADMINISTRACIN DE VISTAS (Custom views) Las vistas cambian la manera en que los libros, hojas, objetos y ventanas son desplegados en la pantalla. Usted puede definir un conjunto de formatos y opciones de impresin y salvarlos como una vista. Despus puede cambiar de una vista a otra para desplegar o imprimir su libro de diferentes maneras. Las opciones que se graban en una vista son: anchos de columna, opciones de despliegue, posicin y tamao de las ventanas en la pantalla, split, frozen panes, la hoja activa y la celda seleccionada, renglones y columnas escondidos, filtros y opciones de impresin. Las vistas incluyen todo el libro. Por ejemplo si usted esconde una columna antes de crear una vista y despus la deja de esconder si selecciona la vista sta columna se volver a esconder. Si incluy opciones de impresin en la vista, la vista incluye el rea de impresin que haya definido o la hoja entera si no ha definido un rea de impresin especfico. Para crear una vista: 1. Haga los cambios que necesite a su libro. 2. Seleccione View/Custom Views .

3. Presione Add . Ver la siguiente ventana:

4. Escriba el nombre y seleccione Print settings si desea que en la vista se

aadan las opciones de impresin que haya especificado. Seleccione Hidden rows, columns... si desea que se aadan a la vista los renglones escondidos, columnas y filtros.

5. Presione OK Para desplegar una vista, seleccione View/Custom Views , seleccione la vista que quiere utilizar y presione Show. Para borrarla presione Delete y para aadir otra vista presione Add.



VI. ADMINISTRACIN DE INFORMES Excel provee una manera sencilla de imprimir sus informes si tiene un libro con varias hojas y diferentes conjuntos de datos. El comando Report Manager utiliza las vistas y escenarios que haya creado y las imprime en conjunto. Esto le permitir definir una serie de informes o reportes y con una sola opcin imprimirlas evitndole la molestia de cada vez cambiar el formato a celdas y columnas, seleccionar reas de impresin o datos. Para crear un Informe: 1. Seleccione View/Report Manager. (Si no est la opcin Report Manager, en

el men Tools/Add-Ins seleccione Report Manager y presione OK). 2. Aparecer la siguiente ventana:

Presione Add para aadir un informe, Print para imprimirlo, Edit para modificarlo y Delete para borrarlo. 3. Presione Add y en la siguiente ventana pngale un nombre a su Informe en

Report Name.

4. En Sheet seleccione la hoja que desea incluir y en View y Scenario escoja

la Vista o Escenario que va a utilizar. 5. Presione Add. Se incluir la hoja, la vista y el escenario en Sections in the

Report



6. Repita los pasos 4 y 5 las veces que sea necesario. 7. Si es necesario presione Move Up o Move Down si va a cambiar de

posicin de alguna hoja de las secciones del informe. 8. Presione Delete si desea borrar la seccin seleccionada. 9. Si va a numerar las hojas impresas de forma continua seleccione Use

continuous Page Numbers. Tenga en cuenta que se numerarn de acuerdo al orden que tengan en la lista.

10. Cuando finalize presione OK. Su informe se habr aadido a la lista y para imprimirlo slo presione Print.



VII. IMPORTACIN Y EXPORTACIN DE DATOS. Excel puede importar datos de Word y Power Point de una manera sencilla. Muchas de las opciones de estos programas son muy similares o iguales (por ejemplo las barras de herramientas, mens o el corrector de ortografa) lo que hace que Microsoft Office pueda intercambiar informacin entre sus programas sin problemas para usted.

7.1 Word Intercambiando datos entre Word y Excel no siempre es sencillo, ya que dependiendo de cmo vaya a pegar la informacin, es difcil predecir el resultado. Para ayudarle, los siguientes datos le dirn exactamente qu esperar cuando intercambie informacin entre Word y Excel. Exportando un rango de Excel a Word Cuando copia un rango en Excel y despus selecciona en Word Edit/Paste Special, el cuadro de dilogo le mostrar 8 tipos de datos para usar al pegar. La siguiente tabla le ayudar a decidir qu opcin elegir: Microsoft Excel Worksheet Object

Incrusta el rango como una hoja de trabajo

Formatted Text (RTF) Pega el rango como una tabla de Word y guarda su formato

Unformatted Text Pega el rango como texto normal con tabs separando cada columna y retornos de carro separando cada rengln

Picture Inserta el rango como una imagen. Use este tipo en lugar de Bitmap para guardar memoria.

Bitmap Inserta el rango como una imagen bitmap. La nica ventaja sobre Picture es que le muestra exactamente cmo se vea el rango de Excel

Picture (Enhanced Metafile)

Inserta el rango como una imagen con formato Metafile Mejorado

HTML Format Inserta el rango con formato HTML Unfomatted Unicode Text

Pega el rango sin ningn formato

Importando texto desde Word Copiando texto de Word al ClipBoard y despus pegndolo a Excel es sencillo. La caja de dilogo de Edit/Paste Special le muestra seis tipos para pegar:



Microsoft Word Document object

Incrusta el texto como un objeto

Picture (Enhanced Metafile)

Pega el texto como una imagen

HTML Pega el texto en formato HTML. Cada prrafo lo pega a una celda.

Unicode Text Copia el texto con formato Text Copia el texto sin ningn formato Hyperlink Copia el texto en una sola celda con una liga hacia

el documento. Seleccione Paste si slo va a copiar el texto en Excel y Paste link si desea que al modificar el texto en Word tambin se haga en Excel. Seleccione Display as icon si quiere que el texto no se vea en la hoja, sino slo un cono que al seleccionarlo ver el texto pero en Word.

7.2 Power Point Intercambiando datos entre Excel y Power Point es similar a los de Word. Slo tiene que recordar los siguientes puntos:

Cuando est pegando un rango, el comando Paste Special de Power Point le muestra las mismas opciones de Word. Solo que en Power Point tiene la opcin Device Independent Bitmap que es pegarlo como una imagen independiente y en Word tiene la opcin Unformatted Unicode Text que no se encuentra en Power Point.

Power Point tiende a insertar hojas y grficas como objetos pequeos. Para ver el rango necesitar incrementar el tamao del objeto.

excel_avanzado 17.pdf

Documents