Curso Excel 2003 Avanzado 6a5p3l

Excel 2003 Avanzado

http://capacitateexcel.blogspot.com

Índice Índice ............................................................................................................. 1 Funciones ...................................................................................................... 3 Insertar una función ............................................................................................... 3 Operadores numéricos ........................................................................................... 4 La sintaxis de una función ..................................................................................... 5 Tipo de argumentos de una función ....................................................................... 6 Funciones Matemáticas ......................................................................................... 6 Funciones de búsqueda .......................................................................................... 7 Funciones lógica .................................................................................................... 9 Funciones de texto ............................................................................................... 10 Usando nombres en las fórmulas ......................................................................... 12 Como asignar un nombre a una celda o rango ...................................................... 12 Filtros y Validación de Datos ..................................................................... 14 Formato condicional ............................................................................................ 14 Formato condicional basado en una fórmula ........................................................ 15 Validación de datos ............................................................................................. 16 Trabajar con Listas .............................................................................................. 17 Ayuda cuando se repiten datos de entrada en una lista ......................................... 17 Filtros .................................................................................................................. 18 Usando Autofiltro ................................................................................................ 18 Autofiltro personalizado ...................................................................................... 20 Filtro Avanzado ................................................................................................... 20 Caracteres comodines para filtro avanzado .......................................................... 23 Ejemplos de criterios de filtro avanzado .............................................................. 23 Tablas dinámicas ........................................................................................ 26 Los campos que contiene una base: ..................................................................... 26 Creando una tabla dinámica con el asistente ........................................................ 27

1

Excel 2003 Avanzado


Elementos de Diseño de una tabla dinámica ........................................................ 29 http://capacitateexcel.blogspot.com/2010/05/tablas-dinamicas-iii.html ....... 30 Opciones de Tablas Dinámicas ............................................................................ 31 http://capacitateexcel.blogspot.com/2010/05/tablas-dinamicas-iv.html ................ 33 Cambiar el modo en que se resumen los datos ..................................................... 33 Agrupaciones en tablas dinámicas ....................................................................... 34 Cálculos personalizados ...................................................................................... 35 http://capacitateexcel.blogspot.com/2010/05/tablas-dinamicas-v.html.................. 36 Creando campos y elementos calculados ............................................................. 36 Creando un campo calculado ............................................................................... 37 Creando un elemento calculado ........................................................................... 38 Crear listas de fórmulas ....................................................................................... 39 Gráfico dinámico ................................................................................................. 40 Creando un gráfico dinámico a partir de una tabla dinámica ya existente ............. 40 Análisis What-if ........................................................................................... 42 Función Buscar Objetivo ........................................................................... 42 Tablas de una y dos variables .................................................................. 45 Qué es una tabla de datos para What-if ................................................................ 45 Tabla de una variable........................................................................................... 45 Tabla de dos variables ......................................................................................... 48 Macros ......................................................................................................... 51 Grabar una macro ................................................................................................ 51 Ejecutar una macro con el método abreviado ....................................................... 52 Ejecutar una macro .............................................................................................. 53 Asignar una macro a un botón ............................................................................. 54 La barra de herramientas de Macros .................................................................... 56 Crear menú de macros ......................................................................................... 57

2

Excel 2003 Avanzado


Funciones Una función es una fórmula creada con Visual Basic para realizar cálculos complicados. Las funciones tiene un nombre propio y existen multitud de funciones en Excel. Existen funciones que realizan cálculos financieros, estadísticos, matemáticos, e incluso funciones de búsqueda y de texto. Insertar una función

Las funciones pueden ser introducidas mediante el teclado o por el asistente de funciones. Para insertar una función por medio del asistente de fórmulas, necesitamos hacer lo siguiente: 1. En la barra de herramientas hacemos clic en Insertar> Insertar función y con ello aparece la pantalla de Pegar función la cual muestra las diversas categorías de las funciones de Excel 2003. 2. Por ejemplo, Si deseamos usar la función de suma seleccionamos SUMA de la ventana de Nombre de la función.

3

Excel 2003 Avanzado


3. Hacemos clic en el botón de Aceptar y tenemos otra pantalla para insertar los parámetros de la función.

4. Ahora tenemos la pantalla para la entrada de los datos o el vínculo a los datos a sumar, en los espacios de texto colocamos los valores a sumar o el vinculo a las celdas a sumar.

http://capacitateexcel.blogspot.com/2010/05/que-es-una-funcion.html Operadores numéricos

Los operadores son una parte importante en las fórmulas pues con ellos podemos establecer criterios al momento de hacer operaciones.

4

Excel 2003 Avanzado


Operadores utilizados en las fórmulas Operador

+ *

Nombre Suma Resta Multiplicación

/

División

^ & = > < >= <= <>

Exponencial Concatenación Igual a Mayor que Menor que Mayor e igual a Menor e igual a diferente a

http://capacitateexcel.blogspot.com/2010/05/operadores-aritmeticos.html La sintaxis de una función

Las funciones tienen reglas de sintaxis para un correcto funcionamiento, ver el siguiente ejemplo:

=SUMA(A1:B12;100) Signo igual

Nombre de la función

Argumentos separados por punto y coma, y encerrados entre paréntesis

En el ejemplo se suman los valores del rango A1:B12 y el número 100. Entonces podemos decir que esta función tiene dos argumentos a sumar, el rango y el valor de 100.

5

Excel 2003 Avanzado


Tipo de argumentos de una función

Los argumentos de una función pueden ser: 

Números: con o sin separador decimal, aceptando hasta una precisión de 15 decimales.



Texto: los argumentos en formato de texto deberán ir obligatoriamente entre comillas dobles, pudiendo contener hasta 256 caracteres.



Valores Lógicos: éstos son Verdadero o Falso.



Referencias: pueden ser referencias a celdas individuales o a rangos de celdas.

Funciones Matemáticas

SUMAR.SI (rango;criterio,rango_suma) Suma los valores de rango_suma que cumplan con el criterio indicado en la columna de rango.

Nota: El criterio_rango puede ser un vínculo a una celda con el criterio a evaluar

CONTAR.SI (rango;condición)

6

Excel 2003 Avanzado


Cuenta el número de celdas no vacías que cumplan con la condición dada.

CONTAR (rango) Cuenta cuantos números hay en el rango.

MAX (número1, número2) Devuelve el valor máximo de los valores en la sección de argumento.

MIN (número1, número2) Devuelve el valor mínimo de los valores en la sección de argumento.

ENTERO (número) Redondea un número dado como argumento hasta el entero más próximo. =ENTERO(4.5) es 4

REDONDEAR (número, decimales) Redondea un número dado como argumento al número de decimales especificado. =REDONDEAR(4.5,0) es 5

http://capacitateexcel.blogspot.com/2010/05/operadores-aritmeticos.html Funciones de búsqueda

BUSCAR (valor buscado, vector de comparación, vector resultado) Devuelve un valor del vector resultado (una columna del rango) que se corresponden en posición al valor buscado dentro del vector de comparación, que debe ser del mismo tamaño.

7

Excel 2003 Avanzado


Resultado 5%

BUSCARV (valor buscado, matriz de comparación, indicador columna, ordenado) Encuentra el valor buscado dentro de una vector de comparación (serie de columnas) y devuelve el valor que se encuentra en la celda que se muestra en el indicador de columna. Ordenado, es una indicación de que la primera columna de la matriz esta ordenada.

Resultado 301

Nota: Si queremos que la fórmula nos devuelva un dato exacto en vez del más cercano, debemos de agregarle a la fórmula el criterio de “falso” al final de ésta, obligando a la misma a hallar el dato real y no una aproximación.

BUSCARH (valor buscado, matriz de comparación, indicador fila, ordenado)

8

Excel 2003 Avanzado


Encuentra el valor buscado dentro de una matriz de comparación (serie de filas) y devuelve el valor que se encuentra en la celda que se indica en el indicador de fila. Ordenado, es una indicación de que la primera fila de la matriz esta ordenada.

http://capacitateexcel.blogspot.com/2010/05/buscar-y-buscarv.html Funciones lógica

SI (prueba lógica, valor si es verdadero, valor si es falso) La función SI, evalúa una condición establecida como argumento (prueba lógica), si el resultado de ésta es verdadero, la celda presenta el segundo de los argumentos, si es falso presentará el tercero. Ejemplo 1. Deseamos evaluar si la calificación de un alumno es de aprobado o no aprobado. =SI(Nota<5,”No Aprobado,”Aprobado”) Ejemplo 2. Se compara el presupuesto vs. real

Y(valor lógico1, valor lógico2…, es_verdadero, es_falso) La función Y evalúa una o más condiciones establecidas como argumento. Si todas son verdaderas, la función devuelve el segundo argumento si es verdadero y el tercero en su defecto.

9

Excel 2003 Avanzado


O (valor lógico1, valor lógico2… es_verdadero, es_falso) La función O, evalúa una o más condiciones establecidas como argumento. Si el resultado de al menos una de éstas es verdadero, la celda presenta el segundo de los argumentos y si es falso, el tercero. Ejemplo; Se evalúa si al empleado le corresponde bono anual o permiso.

http://capacitateexcel.blogspot.com/2010/05/funciones-logicas.html Funciones de texto

IZQUIERDA (texto, número de caracteres) Devuelve a partir de la izquierda de un texto, el número de caracteres especificados en el argumento número de caracteres.

10

Excel 2003 Avanzado


DERECHA (texto, número de caracteres) Devuelve a partir de la derecha de un texto, el texto que se especifica con el argumento número de caracteres.

EXTRAE (texto, posición inicial, no. de caracteres) Devuelve el número de caracteres especificados un texto fuente a partir de una posición determinada por el argumento posición inicial (cuenta de izquierda a derecha).

CONCATENAR(texto1,texto2) Concatena o une dos textos.

http://capacitateexcel.blogspot.com/2010/05/funciones-de-texto.html

Nota: la concatenación de dos textos puede hacerse con el símbolo de &, usándolo de la siguiente manera: =”Auto”&”Rojo” resulta en AutoRojo.

11

Excel 2003 Avanzado


Usando nombres en las fórmulas

Las celdas son identificadas por la posición que ocupan en la hoja de cálculo considerando la columna y renglón que la conforman, es decir, si está en la columna A y en la fila 6 la celda se le denomina A6. A las celdas también se les puede asignar un nombre para identificarlas con el dato que contiene, como por ejemplo, TAREAS, PRECIOS, etc. Esta asignación tiene sus ventajas: a) Teniendo nombre una celda o un rango es más fácil averiguar el origen ya que al poner el cursor en el rango o hacer clic en F2 al mismo tiempo que está seleccionada, mostrará los nombres de las celdas que intervinieron en la operación. b) Se puede localizar de manera más rápida, por ejemplo, si se busca la celda con el nombre asignado PRECIO, sólo se tendrá que buscar por medio del comando IR A (F5) y este comando nos llevará a la celda o rango así denominada. c) Se pueden realizar operaciones con mayor lógica, por ejemplo, si en la celda A4 se encuentra el PRECIO y en la celda A6 se encuentra el DESCUENTO, en vez de que la fórmula sea =A4*A6 ésta será =PRECIO*DESCUENTO.

Como asignar un nombre a una celda o rango

1. Para crear un nombre de celda seleccionamos la celda o celdas a nombrar. 2. Hacemos clic en el cuadro de nombre de la barra de fórmulas. 3. Escribimos el nombre sin espacios y luego hacemos clic en Enter.

12

Excel 2003 Avanzado


4. Para editar el rango de un nombre de celda en la barra de herramientas hacemos clic en Insertar > Nombre > Definir. 5. Seleccionamos el nombre a modificar, en este caso es PRECIO y en la sección que dice Se refiere a: editamos el nuevo rango.

http://capacitateexcel.blogspot.com/2010/05/rangos.html

13

Excel 2003 Avanzado


Filtros y Validación de Datos Formato condicional

Con

el

cuadro

de

dialogo

“Formato

Condicional” podemos aplicar un formato a una celda o rango que cumplan con una condición definida por el . Veamos un ejemplo. Deseamos poner color a aquellas celdas de la columna unidades, que sean mayores a 5.

Para utilizar la herramienta “Formato Condicional” hacemos lo siguiente: 1. Seleccionar la celda o rango de celdas. 2. Desde el menú seleccionar Formato>Formato Condicional. Excel despliega el cuadro de formato condicional. (ver figura).

3. En el primer cuadro de lista seleccionamos entre las opciones de Valor de la celda (para formato condicional simple) o Formula (si el formato esta basado en una fórmula). 4. Especificamos la condición.

14

Excel 2003 Avanzado


5. Hacemos clic en el botón de Formato y especificamos el formato a aplicar si la condición es verdadera. 6. Para agregar una condición adicional, hacemos clic en el botón Agregar y repetimos los pasos 3 a 5.

http://capacitateexcel.blogspot.com/2010/05/formato-condicional.html Formato condicional basado en una fórmula

Cuando deseamos aplicar un formato condicional basado en el resultado de una fórmula, en el cuadro de condición, seleccionamos Formula, en el siguiente cuadro podemos especificar una fórmula, ya sea seleccionando una celda con una fórmula o tecleando la fórmula directamente en el cuadro de texto. La fórmula tecleada en el cuadro de Formula debe de empezar con el signo de igual (=).

Nota: Es importante que la fórmula introducida retorne Verdadero o Falso. Si la fórmula evaluada es verdadera la condición es satisfecha y el formato condicional es aplicado, en caso contrario sería de resultar una solución falsa.

http://capacitateexcel.blogspot.com/2010/05/formato-condicional-basado-enuna.html

15

Excel 2003 Avanzado


Validación de datos

La herramienta validación de datos es una aplicación para evaluar si el dato a introducir en una celda cumple con el criterios establecidos por el para dicha celda. 1. Seleccionamos la celda o rango a validar. 2. En la barra de menú hacemos clic en Datos >Validación. 3. En la pestaña de Configuración hacemos clic el cuadro de lista y seleccionamos una de las opciones.

Mensaje necesita

entrante.dar

un

En

ocasiones

mensaje

al

se

informándole el tipo de dato que puede insertar en la celda, para ello vamos a la pestaña

de

Mensaje

entrante

donde

pondremos el Título y el Mensaje de entrada. Mensaje de salida.- Como en el caso del mensaje de entrada, existe una pestaña de Mensaje de error, en la cual puede ser configurado para que arroje un mensaje de error cuando el intente entrar un dato no valido para dicha celda.

http://capacitateexcel.blogspot.com/2010/05/validacion-de-datos.html

16

Excel 2003 Avanzado


Trabajar con Listas

Una lista es una colección de información organizada que consiste en filas y columnas las cuales pueden ser valores, texto o la combinación de ambos. Podemos ver una lista también como una base de datos plasmada en una hoja de cálculo. Ayuda cuando se repiten datos de entrada en una lista

Auto completado: Cuando empezamos a teclear en una celda, Excel escanéa de arriba hacia abajo de manera automática buscando si antes ya se había tecleado el mismo dato, de haberse hecho esto, Excel nos ofrece la opción hallada para poder darle entrada o seguir tecleando (. Elegir de la lista: Otra manera de insertar un valor ya existente en la lista es haciendo clic en el botón derecho del ratón y seleccionar la opción de Elegir Lista, seguidamente Excel nos despliega una lista con lo tecleado hasta el momento (ver gráfico).

Nota: Con Alt + Flecha Baja obtenemos el mismo resultado que “Elegir de la lista”.

http://capacitateexcel.blogspot.com/2010/05/listas.html

17

Excel 2003 Avanzado


Filtros

En Excel existen dos maneras de filtrar una lista: Auto Filtro: consiste en seleccionar alguno de los datos desplegados en las modalidades de: 1. Todas: presenta todas las filas. 2. Las 10 más...: Presentar todas las filas situadas entre los límites superior e inferior que especifique, ya sea por elemento o porcentaje; por ejemplo, los importes hasta el 10% de las ventas. 3. Personalizar...: Aplica dos valores de criterio de la columna actual o utiliza operadores de comparación distintos de “Y” (el operador predeterminado). 4. Vacías: Presentar sólo las filas que contienen una celda vacía en la columna. 5. No vacías: Presentar sólo las filas que contienen un valor en la columna. Nota: Las opciones Vacías y No vacías sólo están disponibles si la columna que desea filtrar contiene una celda vacía1

Filtro Avanzado: este filtro puede manejar dos o más. Usando Autofiltro Para utilizar la herramienta de Auto Filtro hacemos clic en Datos>Autofíltro

Cuando una lista tiene habilitado el Autofíltro Excel coloca un cuadro de lista en cada uno de los títulos de nuestra lista.

Nota: Auto filtro tiene como límite, mostrar con el cuadro de lista solo los primero 1,000 datos diferentes.

1

Fuente: Ayuda de Microsoft Excel 2003

18

Excel 2003 Avanzado


http://capacitateexcel.blogspot.com/2010/05/filtros.html

19

Excel 2003 Avanzado


Autofiltro personalizado Con esta opción se gana mas flexibilidad filtrando pues se toma en consideración mas un criterio. Con ello podemos filtrar valores con el criterio igual a, valores entre un rango, valores discrecionales (uno u otro) y aproximaciones usando los símbolos ? y *.

http://capacitateexcel.blogspot.com/2010/05/autofiltro-personalizado.html Filtro Avanzado

Con el filtro avanzado Excel permite que filtremos datos con muchos más criterios que auto filtro o el filtro personalizado. Por ejemplo, con el filtro avanzado uno puede filtrar datos combinando criterios de las columnas que conforman nuestra base de datos, veamos un ejemplo:

Se desea filtrar de un reporte de ventas trimestrales, las ventas de enero, con los productos que empiecen con la letra S y que en unidades vendidas sean mayores a 125, hacemos lo siguientes:

En este ejemplo filtramos las ventas del mes de enero mayores a 50 y todos los nombres de productos que contengan la letra “T”.

20

Excel 2003 Avanzado


Del siguiente reporte de ventas trimestral necesitamos las ventas de enero que sean mayores a 50 unidades y los productos que contengan la letra T. El orden de resolución es la primera fila de arriba abajo.

Los criterios que se encuentren en la misma fila deben de cumplirse al mismo tiempo y la búsqueda se hace de arriba a abajo.

21

Excel 2003 Avanzado


Operadores de Comparación Operador

Tipo de Comparación

=

Igual a

>

Mayor que Mayor o igual que

>= < <=

Menor a Menor o igual que

<>

Diferente

http://capacitateexcel.blogspot.com/2010/05/filtro-avanzado.html

22

Excel 2003 Avanzado


Caracteres comodines para filtro avanzado

Para buscar valores de texto que comparten algunos caracteres pero no otros, utilice un carácter comodín. Un carácter comodín representa uno o varios caracteres no especificados. Utilice

Para buscar Un único carácter en la misma posición que el

? (signo de interrogación)

signo de interrogación. Por ejemplo, Gr?cia buscará "Gracia" y "Grecia". Cualquier número de caracteres en la misma

* (asterisco)

posición del asterisco. Por ejemplo, *este buscará "Nordeste" y "Sudeste"

~ (tilde) seguida de

Un signo de interrogación, un asterisco o una tilde.

?, *, o ~

Por ejemplo, fy91~? buscará "fy91?".

Ejemplos de criterios de filtro avanzado

Los criterios de filtro avanzado pueden incluir varias condiciones aplicadas a una sola columna, varios criterios aplicados a varias columnas y condiciones creadas como resultado de una fórmula. Varias condiciones en una sola columna Si incluye dos o más condiciones en una sola columna, escriba los criterios en filas independientes, una directamente bajo otra. Por ejemplo, el siguiente rango de criterios presenta las filas que contienen "Davolio", "Buchanan" o "Suyama" en la columna Vendedor.

23

Excel 2003 Avanzado


Una condición en dos o más columnas Para buscar datos que cumplan una condición en dos o más columnas, introduzca todos los criterios en la misma fila del rango de criterios. Por ejemplo, el siguiente rango de criterios presenta todas las filas que contienen "Producto" en la columna Tipo, "Davolio " en la columna Vendedor y valores de ventas superiores a $1,000

Una condición en una columna u otra Para buscar datos que cumplan una condición de una columna o una condición de otra, introduzca los criterios en filas diferentes del rango. Por ejemplo, el siguiente rango de criterios presenta todas las filas que contienen "Producto" en la columna Tipo, "Davolio " en la columna Vendedor o valores de ventas superiores a $1,000.

Uno de dos conjuntos de condiciones para dos columnas Para buscar filas que cumplan uno de dos conjuntos de condiciones, donde cada conjunto incluye condiciones para más de una columna, introduzca los criterios en filas independientes. Por ejemplo, el siguiente rango de criterios presenta las filas que contienen Davolio en la columna Vendedor y valores de ventas superiores a $3,000 y también muestra las filas del vendedor Buchanan con valores de ventas superiores a $1,500.

Condiciones creadas como resultado de una fórmula Puede utilizar como criterio un valor calculado resultado de una fórmula. Si emplea una fórmula para crear un criterio, no utilice un rótulo de columna como rótulo de criterios; conserve este rótulo vacío o utilice uno distinto a un rótulo de columna de la lista. Por ejemplo, el siguiente rango de criterios

24

Excel 2003 Avanzado


presenta filas que contienen un valor en la columna G superior al promedio de las celdas E5:E14; no utiliza rótulo de criterios2.

Notas 

La fórmula que utilice con el fin de generar una condición debe utilizar una referencia relativa para hacer referencia al rótulo de columna (por ejemplo, Ventas) o al campo correspondiente del primer registro. Todas las demás referencias de la fórmula deben ser referencias absolutas y la fórmula debe evaluarse contra VERDADERO o FALSO. En el ejemplo, "G5" hace referencia al campo (columna G) del primero registro (fila 5) de la lista.



En la fórmula puede utilizar un rótulo de columna en lugar de una referencia relativa a celda o un nombre de rango. Si Microsoft Excel presenta el error #¿NOMBRE? en la celda que contiene el criterio, puede ignorarlo, ya que no afecta a la forma en que se filtra la lista.

2


25

Excel 2003 Avanzado


Tablas dinámicas Una tabla dinámica es un reportador robusto el cual nos permiten resumir grandes volúmenes de datos y presentarlos de una manera elegante y ordenada. Con la tabla dinámica podemos hacer operaciones y presentaciones sin arriesgar la base de datos fuente.

Reporte Mensual de Ahorros Los campos que contiene una base:

Dato: Contienen un dato de valor o numérico el cual es susceptible a ser sumado en la imagen anterior el campo Saldo es de este tipo (ver cuadro de arriba). Categoría: Este campo describe los datos ofreciendo información sobre el dato numérico como puede ser la columna Sucursal (ver cuadro de arriba). Una tabla dinámica puede tener cualquier número de valores y categorías.

http://capacitateexcel.blogspot.com/2010/05/tablas-dinamicas-introduccion.html

26

Excel 2003 Avanzado


Creando una tabla dinámica con el asistente

Ahora vamos a construir una Tabla Dinámica a partir de Datos almacenados en otro libro de Excel Para la ejecución de la tabla dinámica necesitamos emplear el asistente para tablas y gráficos dinámicos. 1. Clic en Datos > Informe de Tablas y gráficos dinámicos.

En este paso se nos pide seleccionar entre las opciones de la fuente de los datos a utilizar y que tipo de informe deseamos hacer seleccionamos Lista o base de datos de Microsoft Office Excel. Nota: Primero debe de estar abierto el libro donde se encuentran los datos a consolidar. 2. Seleccionamos el rango de datos a utilizar por medio del Asistente para tablas dinámicas.

27

Excel 2003 Avanzado


3. En este paso existe la opción de colocar la tabla en una hoja nueva, en la hoja actual o en otra creada.

Concluido este paso pulsar Diseño para presentar el asistente en el modo de diseño como consecuencia, surge una nueva barra de herramientas, que contiene una serie de botones con los nombres de los encabezados de columna de datos.

http://capacitateexcel.blogspot.com/2010/05/tablas-dinamicas-ii.html

28

Excel 2003 Avanzado


Elementos de Diseño de una tabla dinámica

Página: Los botones que se colocan en esta área son los primeros que se filtran. Fila: Son los elementos que queremos colocar como filas en la tabla dinámica, que suelen ser también las categorías, por ejemplo Ciudad, nombre del cliente, etc. Columna: Como en el caso anterior, colocamos los datos que forman parte de la columna de la tabla, puede ser el mes, la sucursal, etc. Datos: En este caso se colocan los datos que se organizan y se resumen, este tipo de datos suelen ser numéricos o susceptibles de hacer operaciones aritméticas con ellos, en el caso de utilizar datos no numéricos por defecto contará los datos, esto lo veremos mas adelante.

En el asistente para tablas y gráficos dinámicos podemos tomar los diferentes botones de los campos y colocarlos el área que queramos.

Para hacer nuestra tabla dinámica podemos utilizar el Asistente para tablas y gráficos dinámicos o la lista de campos de tabla dinámica.

29

Excel 2003 Avanzado


Campo de página.- Un campo de página es un cuadro de lista desplegable situado en la parte superior de la tabla dinámica, que permite filtrar los datos que se muestran en la tabla dinámica permitiendo seleccionar páginas individuales. Una tabla dinámica puede no tener campos de página, tener uno sólo o muchos. Campos de fila.- Los campos de fila son campos de una lista o de una tabla de origen a los cuales se les ha asignado una orientación de fila en la tabla dinámica. Puede ser uno o mas elementos, por ejemplo presentar Región y Vendedor como Campos de filas.

Campos de columna.- Un campo de columna es un campo de una lista o una tabla de origen al que se ha asignado una orientación de columna en una tabla dinámica. En el ejemplo podemos presentar en columnas los vendedores o los meses. Área de datos (Coloque datos aquí).- El área de datos es la parte de la tabla dinámica que contiene los datos resumidos. Las celdas del área de datos muestran los datos resumidos de los elementos de los campos de fila y de columna. En el ejemplo, las ventas o unidades suelen ser los datos.

http://capacitateexcel.blogspot.com/2010/05/tablas-dinamicas-iii.html

30

Excel 2003 Avanzado


Opciones de Tablas Dinámicas

Las tablas dinámicas en Excel ofrecen varias opciones que nos ayudan a determinar como queremos presentar la operación. Para poder acceder a ellas hacemos clic en el botón de opciones en el paso 3 de 3 o haciendo clic con el botón derecho del Mouse y con el puntero en cualquier parte de la tabla dinámica, seleccionamos opciones.

Nombre Permite mantener o cambiar el nombre de la tabla. Totales Generales de columnas Los resúmenes obtenidos con la ejecución de tablas dinámicas necesitan generalmente de los totales de filas y columnas o por lo menos, los totales en una de las dos opciones. Por defecto las dos opciones aparecen activadas. Auto formato de tabla Esta casilla se encuentra activada automáticamente, el formato que se aplica a la tabla es el que tenemos por defecto en el menú Formato. Para usar otros formatos definidos por Excel, vamos a la barra de herramientas y seleccionamos: Formato > Auto formato Subtotales de elementos ocultos de página

31

Excel 2003 Avanzado


Si activamos esta casilla Excel incluirá en el subtotal los importes de los datos ocultos, por ello se encuentra desactivado desde un principio. Combinar Rótulos Si se activa esta casilla se centran los nombres de los elementos, sería como utilizar el botón de “combinar y centrar”. Mantener Formato Si seleccionamos esta opción Excel conservará el formato y este no cambiará al actualizar la tabla. Repetir rótulos de elementos en cada página impresa Seleccionamos esta opción si queremos que aparezcan los títulos de la tabla en cada impresión. Diseño de Página El campo de Página tiene dos configuraciones: 1.-Hacia abajo, luego horizontalmente. 2.-Horizontalmente, y luego hacia abajo. Por defecto toma la primera opción. La segunda opción solo se puede apreciar cuando hay más de un elemento en el campo de página.

Campos por columnas Dentro de la zona Página, permite elegir el número de botones que van a aparecer en una misma columna. Optar por 0 (cero), muestra los botones en la misma columna. Para valores erróneos, mostrar Si se activa esta casilla Excel pone el valor o texto a capturado en el cuadro de texto. Para celdas vacías, mostrar Por defecto se encuentra activada y mostrará celdas vacías, caso contrario mostrará cero. Guardar datos con el diseño de la tabla

32

Excel 2003 Avanzado


Por defecto se encuentra activada, si se desactiva no se podrá configurar de nuevo la tabla dinámica ni cambiar las acciones que realiza como contar, sumar etc. Habilitar información detallada Manteniendo el casillero activado, y con un doble clic sobre cualquier celda del área de datos, se crea una nueva hoja y crea una lista donde se muestra el detalle. Cada vez que se ejecute de la misma manera un doble clic sobre la celda seleccionada del área, se abrirá una nueva hoja. Actualizar al abrir Al activar la casilla, la Tabla dinámica se actualizará cada vez que se abra el libro. Actualizar cada x minutos Al activar la casilla, la tabla se actualizará cada determinado tiempo. Opciones de datos externos Corresponde a tablas dinámicas creadas con datos externos, sin usar datos del libro de Excel.

http://capacitateexcel.blogspot.com/2010/05/tablas-dinamicas-iv.html

Cambiar el modo en que se resumen los datos

Cuando un campo de datos de una tabla dinámica es numérico, el resumen automático realizado por la tabla dinámica es una suma, si el campo no es numérico entonces el cálculo será una cuenta. Hay otras formas de resumir un campo de datos y se detallan a continuación:

33

Excel 2003 Avanzado


Función

Se utiliza para….

Suma

La suma de los valores

Contar

Número de registros.

Promedio

Valor medio de los valores

Máx

Valor maximo de los valores

Min

Valor mínimo de los valores

Producto

Producto de los datos subyacentes

Contar números Número de registros (filas) que contienen números.

Desvest Desvestp

Var

Desviación estándar estimada de poblaciones en la que los datos subyacentes representan la muestra. Desviación estándar de población en la que los datos subyacentes representan a toda la población.

Varianza de población en la que los datos subyacentes representan la muestra.

Agrupaciones en tablas dinámicas

Una importante función de las tablas dinámicas es la posibilidad de poder agrupar elementos, para una mejor comprensión veamos el siguiente ejemplo. Tenemos una base la cual tiene los saldos mensuales de ahorro, nos piden que presentemos la información por trimestres, a lo que optamos por la opción de agrupar de la siguiente manera: 1. Seleccionamos los meses a agrupar ENE-FEB-MAR. 2. Nos vamos a Datos >Agrupar y esquema >Agrupar

3. Para deshacer la agrupación nos vamos a Datos >Agrupar y esquema >Desagrupar. Todo esto se puede hacer seleccionando las celdas a agrupar y con el botón de la derecha del ratón hacemos clic en Agrupar y esquema >Agrupar (Desagrupar).

34

Excel 2003 Avanzado


Nota: Podemos cambiar el nombre de los nuevos campos creados llamados Grupo1,Grupo2,etc.. poniéndole por ejemplo Trim-1,Trim-2, etc... Cálculos personalizados

Qué son los cálculos personalizados Los cálculos personalizados son métodos para resumir valores del área de datos de un informe de tabla dinámica utilizando los valores de otras celdas del área de datos. 1. Colocar el puntero en alguno de los datos. 2. Clic en el botón de la derecha del ratón. 3. Clic en Configuración de campos. 4. Clic en el botón de opciones. 5. Desde la lista de Mostrar datos como podemos optar por algunas de las funciones.

Las funciones para cálculos personalizados son:

35

Excel 2003 Avanzado


Función

Resultado

Muestra los datos como la diferencia del valor de Elemento base en Campo Diferencia de base. Muestra los datos como un porcentaje del valor de Elemento base en Campo % de base. % de la diferencia Muestra los datos como la diferencia de porcentaje del valor de Elemento de base en Campo base.

Total en

% de la fila

% de la columna % del total

Índice

Muestra los datos de elementos (elemento: subcategoría de un campo en informes de tabla dinámica y de gráfico dinámico. Por ejemplo, el campo "Mes" podría tener los elementos "Enero", "Febrero", etc.) sucesivos en Campo base como un total. Muestra los datos de cada fila o categoría como un porcentaje del total de la fila o categoría.

Muestra todos los datos de cada columna o serie como un porcentaje del total de la columna o serie. Muestra los datos como un porcentaje del total general de todos los datos o puntos de datos en el informe.

Calcula datos como se indica a continuación: ((valor en celda) x (Suma total de sumas totales)) / ((Suma total de fila) x (Suma total de columna))

http://capacitateexcel.blogspot.com/2010/05/tablas-dinamicas-v.html Creando campos y elementos calculados

Después de haber creado la tabla dinámica podemos agregar dos tipos de cálculos a la tabla y éstos son: 

Campo calculado.- Es un nuevo campo obtenido a partir de otros campos de la base de datos.



Elemento calculado.- Un elemento calculado es un nuevo elemento que utiliza otros elementos.

36

Excel 2003 Avanzado


Creando un campo calculado

Para crear un campo calculado hacemos lo siguiente: Tenemos el reporte anual de ventas de una empresa, la cual contiene la columna de los vendedores, mes y las ventas en importe y en unidades, la dirección nos pide el precio unitario de las ventas por vendedor. 1. Movemos el puntero del ratón a cualquier parte de la tabla dinámica. 2. Usando la barra de herramientas seleccionamos Tabla dinámica >Fórmulas >Campo calculado

3. Con ello Excel despliega el cuadro de Insertar campo calculado

37

Excel 2003 Avanzado


4. Ponemos un nombre en cuadro de Nombre (por defecto aparece con Campo1) por ejemplo Precio unitario, y en la sección de fórmula ponemos la siguiente fórmula =Ventas / Unidades.

5. Hacemos clic en Aceptar para insertar el nuevo campo calculado y tenemos el precio unitario de las ventas. Creando un elemento calculado

Para crear un elemento calculado hacemos lo siguiente: Necesitamos obtener el primer trimestre del año que es enero, febrero y marzo. 1. Movemos el puntero del Mouse a cualquier parte de la tabla dinámica. 2. Usando la barra de herramientas seleccionamos Tabla dinámica >Fórmulas >Elemento calculado 3. Con ello Excel despliega el cuadro de Insertar elemento calculado 4. Escribimos un nombre en cuadro de Nombre (por defecto aparece con Campo1) por ejemplo Precio unitario, y en la sección de fórmula ponemos la siguiente fórmula =Ene+Feb+Mar

38

Excel 2003 Avanzado


5. Hacemos clic en Aceptar para insertar el nuevo campo calculado y tenemos primer trimestre del año “Trim-1”.

Nota: Cuando ponemos elementos calculados a una tabla dinámica, esta sección ya no se puede presentar como campo de página.

Crear listas de fórmulas

Permite visualizar todos las fórmulas de los elemento y campos calculados que se hayan realizado sobre la tabla dinámica. Para poder visualizar las fórmulas seleccionamos cualquier celda de la tabla, nos vamos a la barra de herramientas de Tabla dinámica y hacemos clic en Tabla dinámica>Fórmulas>Crear lista de fórmulas, y se crea una nueva hoja con las fórmulas que contiene la tabla.

39

Excel 2003 Avanzado


Gráfico dinámico

Un gráfico dinámico es la representación gráfica de los datos de la tabla dinámica. Los datos que se muestran son aquellos que actualmente se presentan en la tabla dinámica, veamos un ejemplo.

Creando un gráfico dinámico a partir de una tabla dinámica ya existente

Crear un gráfico dinámico es sencillo, solo hay que verificar que la tabla dinámica esté activa. 1. Ponemos el puntero en cualquier parte de la tabla dinámica. 2. Hacemos clic con el botón derecho del ratón, seleccionamos Gráfico dinámico y el gráfico se genera en otra hoja de Excel en el mismo libro de trabajo.

40

Excel 2003 Avanzado


41

Excel 2003 Avanzado


Análisis What-if Función Buscar Objetivo En ocasiones necesitamos que una fórmula tenga a un determinado resultado, por lo que optamos por cambiar variable a la fórmula a manera de prueba y error para llegar al resultado deseado. Para ello Excel dispone de una opción en el menú Herramientas llamada Buscar objetivo, con la que es posible cambiar el valor de una celda de entrada específica para que una fórmula dependiente de dicha celda consiga el resultado deseado. Para comprender el funcionamiento de ésta herramienta veamos el siguiente ejemplo:

Adquirimos una deuda que tiene los siguientes supuestos: 1. Monto del préstamo $350,000 2. Tiempo del préstamo: 360 meses = 30 años 3. Tasa de interés anual 2.5%

42

Excel 2003 Avanzado


Hemos preparado la hoja de cálculo que muestra el cálculo del pago mensual, el pago total y el interés total al final del préstamo. Podemos ver que el monto total del préstamo es de $497,852 y queremos saber de cuanto debe ser el préstamo para pagar sólo 430,000. Para ello hacemos lo siguiente: 1. Seleccionamos la celda C11, hacemos clic en Herramientas >Buscar objetivo.

2. En este momento aparece la ventana de buscar objetivo con las siguientes opciones:

Definir la celda: Referencia de la celda con el importe objetivo y que tiene relación a la celda cambiante para buscar una solución. Con el valor: Permite escribir el valor objetivo para la celda anterior. Para cambiar la celda: Se debe indicar la celda que contiene el valor que se desea ajustar para lograr el importe objetivo.

El importe del préstamo debe de ser de 302,298 para pagar un total de 430,000. Ahora tenemos la opción de: 

Aceptar y remplazar el dato original con el buscado.

43

Excel 2003 Avanzado




Cancelar para restaurar con el dato original previo a utilizar Buscar objetivo

Otros puntos a tomar en consideración con Buscar objetivo. Puede suceder que Excel no puede hallar una solución que satisfaga el objetivo, por lo que tenemos las siguientes opciones: 

Aceptar y remplazar el dato original con el buscado.



Cancelar para restaurar con el dato original previo a utilizar Buscar objetivo

Nota: Una celda objetivo debe tener una fórmula que dependa de una celda cambiante, una celda cambiante no debe de contener una fórmula.

44

Excel 2003 Avanzado


Tablas de una y dos variables Qué es una tabla de datos para What-if

Una tabla de datos es una colección de celdas que muestran el efecto que tiene cualquier cambio en alguna de las variables de una fórmula. La tabla de datos es una manera muy conveniente de comparar opciones de una variable. Excel maneja análisis de datos con tablas de una y dos variables. Tabla de una variable

Una tabla de una variable muestra el resultado de una o más fórmulas cuando se usan múltiples valores para una sola variable, veamos un ejemplo.

PRESTAMO Importe del Prestamo Interes Años Mensualidades Pago mensual

$325,900.00 9.5% 15 12 $3,801.21

Tenemos un préstamo por $325,900 con un interés anual de 9.5% y pagos mensuales de $3,801.21 por pagar durante 15 años, : =PAGO(Interés Anual /12, Años * Mensualidades, -Importe del Prestamos) 1. Creamos la tabla de datos de una sola variable utilizando como celda objetivo el pago mensual:

45

Excel 2003 Avanzado


2. Ponemos los valores que deseamos analizar de la variable interés en el rango B13 a B19. 3. Vinculamos la celda C12 con el valor de la celda objetivo que es la C8. 4. Seleccionamos el rango de las celdas B12 a la celda C19. 5. En la barra de herramientas seleccionamos Datos > Tabla y aparece la siguiente pantalla:

6. En éste cuadro Celda de entrada (columna) introducimos el vinculo a la variable a cambiar para lograr el valor en la celda objetivo que en este caso es la celda C5.

46

Excel 2003 Avanzado


Veamos ahora un ejemplo en el cual deseamos ver como afecta los cambios en la variable a modificar en cada una de las partes de la fórmula.

1. Vinculamos la celda F3 a la celda C11, la celda G3 a C12 y H3 a C13. 2. Seleccionamos el rango de la celda E3 a la celda H13. 3. En la barra de herramientas hacemos clic en Datos > Tabla y seleccionamos la celda donde esta la variable a analizar que en este caso es la celda C5, interés.

47

Excel 2003 Avanzado


4. Ahora ya tenemos la tabla con los resultados en las demás fórmulas.

Tabla de dos variables

Este procedimiento hace que una tabla de datos muestre resultados producidos al sustituir el valor de dos variables en una fórmula.

48

Excel 2003 Avanzado


1. Vincular la celda E4 a la celda objetivo que en este caso es el interés total. 2. En la parte de fila colocar los valores a analizar de una de las variables (celdas F4 a H4). 3. En la columna E colocar los valores de la otra variable a analizar (celdas E5 a E14). 4. Seleccionar el rango de celdas que contiene la fórmula, la fila y la columna de valores. 5. Elegir el comando Tabla del menú Datos. Aparecerá el cuadro de diálogo Tabla. 6. Introducir la referencia a la celda variable para la que desee que se sustituya la fila de valores en el recuadro Celda de entrada (fila). 7. Introducir la referencia a la celda variable para la que desea que se sustituya la columna de valores en el cuadro Celda de entrada (columna). 8. Hacer clic en el botón Aceptar.

Excel sustituye los valores y muestra los resultados en las intersecciones de fila/columna correspondientes. Veamos el ejemplo anterior pero ahora analizando el resultado cambiando las variables de interés y de años.

49

Excel 2003 Avanzado


En la fila F3 a la H3, ponemos los valores posible de la variable años, y en la fila E4 a E13 los posibles valores del interés. En la celda E3 debemos de poner a manera de vínculo cualquier celda con una fórmula pudiendo ser Pago mensual o Pago Total (Celdas C11 o C12).

50

Excel 2003 Avanzado


Macros Si ejecutamos frecuentemente una tarea en Microsoft Excel, podemos automatizarla mediante una macro. Una macro consiste en una serie de comandos y funciones que se almacenan en un módulo de Visual Basic y que puede ejecutarse siempre que sea necesario ejecutar la tarea. Una macro se graba igual que se graba música en un disco compacto. Antes de grabar o escribir una macro, necesitamos planificar los pasos y los comandos que deseamos que ejecute la macro. Si se comete algún error mientras se graba la macro, también se grabarán los que se realicen. Cada vez que se grabe una macro, ésta se almacenará en un nuevo módulo adjunto a un libro. Grabar una macro

1. Muestre la hoja de cálculo en la que desea grabar la macro. La macro será almacenada en un módulo aparte del libro y se podrá ejecutar en cualquier hoja de cálculo mientras el libro de trabajo se encuentre abierto. 2. Seleccione Macro en el menú Herramientas y, a continuación, haga clic en Grabar nueva macro. Verá el siguiente cuadro de diálogo.

3. En el cuadro Nombre de la macro, escriba un nombre para la macro.

51

Excel 2003 Avanzado


El primer carácter del nombre de la macro debe ser una letra. Los demás caracteres pueden ser letras, números o caracteres de subrayado. No se permiten espacios en un nombre de macro, para ello podemos utilizar un carácter de subrayado como separador de palabra. 4. En el cuadro Guardar macro en, haga clic en la ubicación en que desea almacenar la macro. 5. Si desea que la macro esté disponible siempre que se utilice Excel, almacene la macro en el libro de macros personales en la carpeta INICIAR. 6. Para incluir una descripción de la macro, escriba la descripción en el cuadro Descripción. Haga clic en Aceptar. 7. Ejecute las acciones que desee grabar. 8. En la barra de herramientas Detener grabación, haga clic en Detener grabación. Ejecutar una macro con el método abreviado

Para ejecutar la macro presionando un método abreviado, escriba una letra en el cuadro Tecla de método abreviado. Puede utilizarse Control + letra (para letras minúsculas) o Control + Mayus + letra (para letras mayúsculas), donde letra es cualquier tecla del teclado. La tecla de método abreviado que se utilice no puede ser ni un número ni un carácter especial. La tecla de método abreviado suplantará a cualquier tecla de método abreviado predeterminada en Microsoft Excel mientras esté abierto el libro que contiene la macro. Si se seleccionan celdas mientras se está ejecutando una macro, ésta seleccionará las mismas celdas independientemente de la celda que se haya seleccionado en primer lugar, ya que graba referencias absolutas de celda. Si desea tener una macro para seleccionar celdas independientemente de la posición que tenga la celda activa cuando se ejecute la macro, configure el grabador de macros para que grabe referencias relativas de celda. En la barra de herramientas Detener grabación, haga clic en Referencia.

Microsoft Excel continuará grabando macros con referencias relativas hasta que termine la sesión con Microsoft Excel o hasta que haga clic otra vez en Referencias relativas.

52

Excel 2003 Avanzado


Ejecutar una macro

1. Abra el libro que contiene la macro. 2. Seleccione Macro en el menú Herramientas y, a continuación, haga clic en Macros. 3. Seleccione la macro a utilizar para que aparezca en el cuadro Nombre de la macro. 4. Haga clic en Ejecutar.

53

Excel 2003 Avanzado


Asignar una macro a un botón

Puede ejecutarse una macro desde un botón, desde una barra de herramientas integrada o desde una barra de herramientas personalizada. Para obtener más información acerca de cómo crear una barra de herramientas personalizada, haga clic en.

1. En el menú Herramientas, haga clic en Personalizar.

2. Elija del cuadro de Categorías la opción Macros. 3. En el cuadro de Comandos aparece el botón de Personalizar botón (tiene una carita amarilla). 4. Sitúe el puntero del ratón sobre el botón Personalizar botón, haga clic y sin soltarlo arrástrelo a la barra de herramientas. 5. Ya insertado en la barra de herramientas procedemos a asignarle una macro. Sitúe el puntero del ratón sobre el botón insertado y haga clic en el botón derecho del ratón. Aparece un menú contextual como el de la imagen.

54

Excel 2003 Avanzado


6. Seleccione la opción Asignar macro. 7. Seleccione el nombre de la macro y haga clic en el botón Aceptar. 8. Para finalizar pulse el botón Cerrar.

55

Excel 2003 Avanzado


La barra de herramientas de Macros

Ejecutar macro: Ejecuta una macro creada con anterioridad. Detener grabación: Detiene la ejecución o grabación de una macro, este botón aparece cuando se está grabando una macro. Seguridad: Establece el nivel de seguridad ante macros que pudiesen contener virus. Grabar macro: Comienza la grabación de una macro. Editor de Visual Basic: al Editor de Visual Basic desde el que se puede modificar una macro. Referencia relativa3: Una referencia relativa en una fórmula, como A1, se basa en la posición relativa de la celda que contiene la fórmula y de la celda a la que hace referencia. Si cambia la posición de la celda que contiene la fórmula, se cambia la referencia. Si se copia la fórmula en filas o columnas, la referencia se ajusta automáticamente. De forma predeterminada, las nuevas fórmulas utilizan referencias relativas. Por ejemplo, si copia una referencia relativa de la celda B2 a la celda B3, se ajusta automáticamente de =A1 a =A2.

Cuadro de controles: Aparece un cuadro con una serie de controles que se pueden utilizar para crear una macro. Modo diseño: Activa el modo diseño de formularios, se utiliza cuando va a realizar un programa de Visual Basic desde Excel.

3


56

Excel 2003 Avanzado


Crear menú de macros

Así como podemos crear barra de herramientas podemos crear menús personalizados que contengan las macros creadas por nosotros, veamos un ejemplo: 1. Seleccione el menú Herramientas y seguidamente la opción Personalizar.

2. Hace clic en la pestaña Comandos. 3. Selecciona en el cuadro de Categorías Nuevo menú y del cuadro de comandos Nuevo menú. 4. Sitúe el puntero del ratón sobre el botón Nuevo menú, haga clic y sin soltarlo arrástrelo a la barra de herramientas. 5. Sitúe el puntero del ratón sobre el botón insertado y haga clic en el botón derecho del ratón, Aparece el menú contextual. 6. Seguidamente con el botón izquierdo del ratón seleccione una macro de la barra de herramientas y sin soltarlo arrastre el botón al nuevo menú, haga esto tantas veces como macros desee en el nuevo menú. 7. Para finalizar, editamos el nombre del Nuevo menú y hacemos clic en Cerrar.

57

Curso Excel 2003 Avanzado 6a5p3l

Overview 3e4r5l

More details w3441

Related Documents 3m3m1z

Curso Excel 2003 Avanzado 6a5p3l

Excel Avanzado Curso Entero 526o5u

Curso Completo Excel-2003 1j645a

Curso Avanzado Excel Vba Macros b3014

Curso Excel Basico Intermedio Avanzado 4n4w33

Excel Avanzado 29l2n