manual excel avanzado agosto 2013

Download Manual Excel Avanzado Agosto 2013

Post on 28-Oct-2015

202 views

Category:

Documents

1 download

Embed Size (px)

TRANSCRIPT

Microsoft Word - Excel 2007 Avanzado.doc

EXCEL NIVEL AVANZADO

Manual de Referencia Rpida.

Marzo del 2013

Excel AvanzadoPgina 1

MANUAL DE EXCEL 2007.

NIVEL AVANZADO

El presente manual tiene como objetivo guiar al usuario en el manejo de Excel 2007, haciendo referencia a Herramientas Avanzadas para el manejo de grandes volmenes de informacin como lo son, funciones y procedimientos de anlisis; as como la automatizacin de procesos con Macros.

El manual fue desarrollado por los Profesores del Departamento de Capacitacin del Hospital Espaol, bajo la supervisin de la Lic. Elvira Diz.

Excel AvanzadoPgina 16

SOCIEDAD DE BENEFICENCIA ESPAOLA, I.A.P.

DEPARTAMENTO DE CAPACITACIN

NOMBRE:

EQUIPO:

CONTENIDO TEMTICO

TABLAS Y GRFICOS DINMICOS 5

MACROS EN EXCEL 30

CUADROS DE CONTROL ACTIVE X 45

CREANDO FORMULARIOS Y PROGRAMNDOLOS 53

FUNCIONES DE BASE DE DATOS 69

FUNCIONES FINANCIERAS 71

ESCENARIOS ANLISIS Y SI 73

BUSCAR OBJETIVO 75

SOLVER 78

TABLAS DINMICAS

Ficha insertar grupo tablas

Los informes de tablas dinmicas permiten al usuario resumir la informacin por categoras y subcategoras, totalizar los valores para facilitar la toma de decisiones y analizar los datos desde diferentes puntos de vista, permiten manipular grandes cantidades de datos y agregar nuevos campos con frmulas personalizadas. Es muy gil el manejo de los formatos para presentar comparaciones, facilitan la organizacin y el resumen de datos en bases de datos.

COMPONENTES DE LAS TABLAS DINMICAS

Campos de filas: Son los campos de base de datos ubicados en el rea de Fila, que representan las categoras por las cuales se resume la informacin.

Campos de columnas: Son los campos de la base de datos ubicados en el rea de Columnas, que representan las categoras por las cuales se resume la informacin

Campos de pgina: Son los campos de la base de datos ubicados en el rea de Pgina, que permiten mostrar los registros que cumplen un determinado criterio, muy similar al manejo de autofiltros.

Campo de datos: Es el campo de la base de datos ubicado en el rea de Datos que se va a totalizar, con las funciones para realizar totales. Normalmente, un campo de datos resume datos numricos, pero tambin puede contener texto. Los valores numricos de forma predeterminada utilizan la funcin de SUMA y los valores texto tienen como funcin predeterminada CONTAR.

Elementos calculados: Son aquellas subcategoras de un campo en el rea de filas o columnas basadas en frmulas creadas por los usuarios.

Campos calculados: Corresponden a los campos que se crean en el rea de datos con frmulas creadas por el usuario.

OBJETIVO DE UNA TABLA DINMICA

El objetivo de la tabla dinmica es aplicar herramientas de anlisis para la toma de decisiones, (costos, presupuestos, inventarios, ventas) en bases de datos robustas, grandes o complejas donde la manipulacin y administracin de la informacin es compleja.

CREAR UNA TABLA DINMICA

Al crear un informe de tabla o grfico dinmico, se conecta con un origen de datos y luego se especifica la ubicacin del informe.

1. Seleccionar una celda de la base de datos.

2. En la ficha Insertar, en el grupo Tablas, hacer clic en Tabla dinmica y, a continuacin, en Tabla dinmica.

3. En el cuadro Seleccione una tabla o un rango; si estaba ubicado en una celda de la base de datos, el rango se visualiza en este cuadro, si la ubicacin es una celda vaca sedebe seleccionar el rango de celdas o la referencia del nombre de tabla.

4. Si los datos de origen se encuentran en otro archivo en una fuente de conexin externa:

Hacer clic en Elegir conexin.

Se mostrar el cuadro de dilogo Conexiones existentes.

Seleccionar en la lista desplegable Mostrar, de la parte superior del cuadro de dilogo una de las categoras de conexiones existentes o, Todas las conexiones existentes (que es el valor predeterminado).

Seleccionar una conexin del cuadro de lista Seleccionar una conexin y hacer clic en Abrir.

5. En la parte inferior permite activar la ubicacin de la tabla dinmica.

Para poner el informe de tabla dinmica en una hoja de clculo nueva que empiece por la celda A1, hacer clic en Nueva hoja de clculo.

Para poner el informe de tabla dinmica en una hoja de clculo existente, seleccionar Hoja de clculo existente y, a continuacin, escribir la primera celda del rango de celdas donde se desea situar el informe de tabla dinmica.

6. Hacer clic en Aceptar.

Excel crea una tabla dinmica en blanco para insertar los campos en las diferentes ubicaciones.

Para ubicar los campos en la tabla dinmica se debe arrastrar los Encabezados de las columnas situados en la Lista de Campos de tabla dinmica con clic sostenido al rea de diseo donde aparecen los campos de rtulos de fila, columna, o datos, dependiendo de la informacin requerida.

Ejemplo:

1. Del cuadro lista de campos, arrastrar el campo artculos a rtulos de fila parte inferior.2. Del cuadro lista de campos, arrastrar el campo Ciudad a rtulos de columna.

3. Del cuadro lista de campos, arrastrar el campo cantidad a valores.

En la Lista de Campos el botn permite cambiar la forma de visualizacin de los campos y las reas de la tabla dinmica.

Para organizar los campos una vez elaborada la tabla dinmica se puede dar clic derecho en el nombre de campo en una de las reas y, a continuacin, seleccionar la opcin Mover y elegir una de las opciones siguientes:

MOVER CAMPOS EN UNA TABLA DINMICA

Subir Sube el campo una posicin en el rea.

Bajar Baja el campo una posicin en el rea.

Mover al principio Mueve el campo al principio del rea.

Mover al final Mueve el campo al final del rea.

Mover al filtro de informe Mueve el campo al rea de filtro de informe.

MOVER CAMPOS EN UNA TABLA DINMICA

Mover a rtulos de fila Mueve el campo al rea de etiquetas de fila.

Mover a rtulos de Mueve el campo al rea de etiquetas decolumna columna.

Mover a valores Mueve el campo al rea de valores.

CONFIGURACIN DE CAMPO

Para cambiar el nombre de un campo se debe situar sobre el rea de diseo y en la celda del ttulo del campo, ubicarse en la barra de frmulas y escribir el nuevo nombre o en la ficha Opciones, en el grupo Campo activo, hacer clic en el cuadro de texto Campo activo, escribir el nuevo nombre.

Adems se puede modificar el nombre situndose en la celda del nombre del campo y con clic derecho seleccionar la opcin Configuracin de campo; esta posibilidad permite controlar las diversas configuraciones de formato, impresin, subtotales y filtros de los campos de un informe de tabla dinmica. As mismo obtendremos el cuadro Configuracin de campo en la ficha Opciones, en el grupo Campo activo al dar doble clic sobre un nombre de un campo.

El cuadro Configuracin de campo cambia su aspecto dependiendo de la ubicacin del campo en el diseo de la tabla dinmica.

Para campos de Fila, Columna o Pgina:

Nombre personalizado: Muestra el nombre del campo actual en el informe de tabla dinmica, o el nombre del origen si no hay ningn nombre personalizado

Subtotales

Automticos: Utiliza la funcin predeterminada para el campo.

Ninguno: Muestra el campo sin ningn subtotal.

Personalizados: Habilita la seleccin de una de las funciones siguientes como subtotal.

FUNCIN DESCRIPCIN

SUMA La suma de los valores. Es la funcin predeterminada de los valores numricos.

CONTAR Cuenta las celdas que tengan un valor ya sea numrico o texto

PROMEDIO El promedio de los valores.

MX El valor mximo.

MN El valor mnimo.

PRODUCTO El producto de los valores.

CONTAR Cuenta las celdas que tengan un valor numrico.NMEROS

DESVEST Un clculo de la desviacin estndar de una poblacin, donde la muestra es un subconjunto de toda la poblacin.

Filtros:

Incluir nuevos elementos en el filtro manual:

Activar o desactivar esta casilla de verificacin para incluir o excluir nuevos elementos en un informe de tabla dinmica con un filtro aplicado.

Diseo e impresin

Mostrar etiquetas de elementos en formato de esquema: Activar esta casilla de verificacin para ver los elementos de los campos en formato de esquema.

Mostrar elementos del campo siguiente en la misma columna (forma compacta): Activar o desactivar esta casilla de verificacin para mostrar u ocultar los rtulos del campo siguiente en la misma columna de forma compacta.

Mostrar subtotales en la parte superior de cada grupo: Si se habilita esta casilla de verificacin permite mostrar los subtotales en la parte superior de cada grupo.

Mostrar etiquetas de elementos en formato tabular: Active esta casilla de verificacin para ver los ele