6/4/2011

UNIVERSIDAD
EAN
GUÍA DE EXCEL INTERMEDIO

Guía Práctica:
Tablas Dinámicas y Macros en Excel 2007
Para Pymes

Facultad de Ingeniería | Ing. Jose Velosa

 Guía de Excel Intermedio 2011

Contenido
1 Herramientas de Análisis............................................................................................................. 3
1.1 Introducción ........................................................................................................................ 3
2 TABLAS DINÁMICAS..................................................................................................................... 3
2.1 Crear una Tabla Dinámica ................................................................................................... 3
2.1.1 Opciones de TDs ........................................................................................................ 10
3 MACROS .................................................................................................................................... 11
3.1 Crear una Macro................................................................................................................ 11
4 REFERENCIAS ............................................................................................................................. 16

2 Universidad EAN
1 Herramientas de Análisis
Un grupo de herramientas orientadas en facilitar el análisis de gran cantidad de datos, son las
Tablas Dinámicas, Filtros, Autofiltros y esquemas. Estas herramientas condensan la información y
extraen de un gran cumulo de datos información relevante y especifica.

1.1 Introducción
Las pequeñas empresas que dominan la tecnología tienen un 20% más de ventajas sobre las que lo
hacen anualmente. El uso eficiente de los medios que disponen pueden logran en estas empresas
ventajas importantes en la toma de decisiones y mejoras en el núcleo del negocio.

2 TABLAS DINÁMICAS
Las tablas dinámicas, consisten en un resumen de un conjunto de datos, organizados por algún
criterio que se extraen de una BD ya organizada con anticipación. Los datos que la tabla dinámica
muestra pueden ser formulados de la BD inicial (sumar, promediar, hallar máximos o mínimos,
etc).
En particular las tablas Dinámicas (también llamadas Pivot Tables) son un aplicativo o herramienta
de uso extensivo en áreas donde se requiere en análisis rápido, fiable y condensado de Datos e
información. Las tablas dinámicas establecen un ambiente interactivo al interior de una hoja de
Excel que sirve para visualizar información en nuevas tablas vista de diferentes maneras.

Desde sus primeras aplicaciones en office 2000, las TDs han cambiado drásticamente tanto en si
ambiente como en las posibilidades de uso. Actualmente, la versión desarrollada en esta guía es la
versión de Microsoft Office Excel ® 2007, tiene varias herramientas importantes:

 Menú personalizado
 Gráficos dinámicos
 Editor de formulas en tablas dinámicas

2.1 Crear una Tabla Dinámica

Preliminares:
Para crear una TD, es necesario tener una tabla de Excel, base de datos o datos organizados en
filas y columnas; las filas son cada uno de los registros, claramente diferenciado entre otros y
totalmente excluyentes, no es ideal repetir registros. Las columnas son los atributos o propiedad
de la información.
Además los datos de la tabla dinámica deben:
 Todas las columnas deben estar identificadas con un nombre único
 Todos los registros deben ser excluyentes (no deben haber repetidos)
 En tablas extensas no deben haber Registros en blanco
 Guía de Excel Intermedio 2011

Gráfica No. 1: Hoja de Datos para formular una TD

Pasos Para Crear Una TD:

1. Defina la necesidad y el uso que le va a dar a la tabla:
Esto quiere decir que información necesita entregar?, quien la va utilizar?, necesita post calcular
los datos?, cuales son los riesgos y las ventajas frente a otras herramientas?, como los filtros y las
ayudas de las barras de estado.

2. Organice los datos de la hoja de Datos Base en forma de columnas:

 Verifique que todas las columnas tengan un nombre único
 que no existan filas en blanco, las columnas deben contener datos con formato igual
 no debe incluir subtotales
 y que todos los registros tengan un indicador común

Gráfica No. 2: Requisitos de la BD para una tabla dinámica

4 Universidad EAN
 Guía de Excel Intermedio 2011

Gráfica No. 3: Insertar TD desde el menú insertar

3. Inserte la tabla dinámica desde la barra insertar allí mismo podrá insertar un grafico
dinámico, que explicaremos más adelante.

Active la barra de menú insertar, el primer icono de izquierda a derecha corresponde a la inserción
de tablas dinámicas.

Antes de para insertar la TD, ubique la área de datos que quiere que se convierta en área de
trabajo de la tabla dinámica, selecciónela toda o ubíquese en una celda al interior de la misma.

Gráfica No. 4: Ventana de selección de datos de la TD

Al seleccionar tabla dinámica del menú, se activa la ventana de creación de tabla dinámica y se resalta el
área que servirá de origen de la tabla dinámica, tenga en cuenta que la TD es una forma de visualizar los
datos que están contenidos en la hoja de Base de Datos.

5 Universidad EAN
 Guía de Excel Intermedio 2011

Examinemos un ejemplo: si ya se dispone de una base de datos de la facturación del año pasado podemos
hacer una tabla de dinámica de estos datos para responder algunas preguntas como:
¿Cuánto vendió en cada mes?, y en los últimos ocho meses?
¿Cuál es el producto más rentable?, cual es el vendedor, cuales son las ordenes pendientes de pago ¿ y los
sectores con más oportunidades de venta?

Como empresario esta información debe ser parte importante del direccionamiento de su empresa, pero en
una tabla o en registros planos no es de mucha utilidad sin que sobre ella se pueda hacer un análisis rápido y
efectivo modificando la presentación de los mismos

Es ideal que mientras se conoce más de la herramienta la generación de la tabla dinámica debe hacerse en
nueva hoja de calculo

4. Organice los datos que necesita al interior del esquema de la tabla dinámica. Miremos
algunas de las partes más importantes:

Gráfica No. 5: partes de una hoja de una tabla dinámica

Si sobre las casillas de selección de datos, hacemos clip izquierdo del mouse, podemos seleccionar
algunos elementos importantes para personalizar:

6 Universidad EAN
 Guía de Excel Intermedio 2011
 El formato de las celdas deberá ser ajustado
dependiendo del tipo de dato que contenga

 Es necesario actualizar la tabla después de hacer

un cambio en la hoja de datos fuente, ya que no
se reflejará hasta que se actualicen.

 la lista de campos también desaparecerá cuando

nos ubiquemos en una celda que no haga parte de
la tabla dinámica.

Las Opciones de la tabla dinámica son:

Diseño y Formato
Totales y Filtros
Mostrar
Impresión
 Datos

Para hacer la organización de la tabla dinámica es necesario que los campos (Nombres que coinciden con los
títulos de las columnas), ocupen cualquiera de los cuatro campos disponibles de la tabla y en especial el
campo de datos, con el dato o información que estamos indagando.
1. Campos de Pagina
2. Campos de Fila
3. Campos de Columna
4. Campo de datos (Valores)

Los espacios de colocación están tanto en la

lista de campos, como en el área de trabajo

Gráfica No. 6: Relación de campos

7 Universidad EAN
 Guía de Excel Intermedio 2011
Los campos los podemos posicionar arrastrándoles con el mouse, botón derecho y los soltamos
sobre cada uno de los espacios de localización:

Gráfica No. 7: Ubicación de los campos en los espacios de la tabla dinámica

Por ejemplo de este movimiento de campos deberíamos ver la siguiente tabla

Donde los rótulos de fila son los meses y los datos o valores es la suma de
lo facturado según la base de datos. Por medio de la ayuda del botón
izquierdo podemos darle formato de moneda:

Y con doble clic sobre el campo de suma Facturado

cambiarle el nombre a uno más acorde con la
información contenida:

8 Universidad EAN
 Guía de Excel Intermedio 2011

Taller No. : 1
Tablas Dinámicas – Gráficos Dinámicos

INFORME DE VENTAS 2010

En vista del excelente trabajo en la dirección de la empresa, la asamblea anual de socios de de FUTURE
OFFICE, ha vuelto a designar a ustedes, un informe más detallado de ventas del 2010.

La base de datos entregada tiene que ser ajustada para ello:

A. Agregue dos columnas tituladas FACTURADO Y PAGADO, después de la columna VALOR, En la

columna FACTURADO irán los valores negativos de la columna VALOR. Y en la columna PAGADO
estarán los valores positivos.
B. Agregue dos columnas tituladas MES y DIA; después de la columna FECHA, en la columna MES
separe el digito correspondiente al mes extraído de la columna fecha , de similar manera para el
DIA.
C. Remplace el numero del mes por el nombre correspondiente nombre del MES. Ej. Para 07
corresponde JULIO.

EL GERENTE le encomienda las siguientes tablas dinámicas (una en cada hoja)

1. Tabla dinámica organizada por VENDEDOR ( en FILA) y el valor PAGADO de la factura (en DATOS).
Con mes en (PAGINA).

Manipular la tabla para saber:

 Vendedor que menos vendió en total el mes de AGOSTO :
 Vendedor que mas vendió en el año (suma total):
 Vendedor que más vendió en promedio (promedio)
 Graficar dinámica las ventas promedio, la venta máxima y la venta mínima de los vendedores.

Pregunta:
¿Si tuviera que tomar una decisión de recompensar a un vendedor basado en la información anterior a
quien recomendaría?

2. Tabla dinámica organizada en orden por el mes ( en FILA), vendedor (COLUMNA) y entrega el
valor FACTURADO (DATOS).
Manipular la tabla para:
 Hacer Grafica dinámica tipo columnas agrupadas
 Conocer Tendencia de cada vendedor por mes
 Conocer Tendencia general ( Consolidado)

Preguntas:
¿Cual es el comportamiento de las ventas totales?, ¿ En que meses de vende más y que meses se vende
menos?
Le recomiendan enviar a vacaciones a 3 vendedores basado en la información de ventas del año pasado.
¿Cual seria su recomendación de programación de vacaciones del mes de julio?

3. Tabla dinámica organizada nombre de tercero (en FILA) derecha, mes (en FILA) izquierda, y el
valor pagado de la suma factura (en DATOS). vendedor (en PAGINA):

Manipular la tabla para saber :

9 Universidad EAN
 Guía de Excel Intermedio 2011
 Cliente que más compra
 Mes del año que compra más ese cliente

Pregunta:
¿Cuales son los meses que más compra ese cliente, y quien de los vendedores debería atenderlo?

4. Tabla dinámica organizada DÍA (en FILA), MES (en COLUMNA) y el valor FACTURADO de la
factura en (DATOS). Con pagina del vendedor y sector
Preguntas:
¿Que día se FACTURA más y que día menos en el año?.
¿Qué mes que día cancela más el sector gobierno?

5. Tabla dinámica organizada por mes (en fila), vendedor (en columna), nombre de cuenta en
pagina y suma de facturado en datos.
 Hacer la gráfica dinámica
Pregunta:
¿Qué vendedor hace más descuentos comerciales, en todo el año? y en que meses? y cuáles son sus
cliente?

2.1.1 Opciones de TDs

Las tablas dinámicas tienen opciones no solo de sumar y contar datos. Sino también de presentar
promedios, número máximo, número mínimo entre otras operaciones:

Por defecto los valores numéricos son presentados o resumidos en forma de suma en la tabla dinámica, y
los datos texto resumidos en cuenta

Para cambiar de método de resumen hacemos doble clic sobre el campo que queremos cambiar así, para el
ejemplo se origino una tabla dinámica ordenada por vendedor y lo que sus clientes pagaron, es de notar que
la notación entre paréntesis es en valor negativo:

Gráfica No. 8: Cambio de la forma de presentar los datos

10 Universidad EAN
 Guía de Excel Intermedio 2011

3 MACROS
La automatización de acciones, de cálculos, formato o generación de datos nuevos o procesados, en Excel
se puede hacer de diferentes formas:

1. Filtros o tablas dinámicas

2. Formato condicional
3. Validación y
4. Macros
1
Las macros son programas escritos en lenguaje Visual Basic , que el encaso de Excel sirven para realizar
acciones orientadas a la toma de decisiones automáticas, basadas en acciones previamente gravadas o
programadas.

Las macros sirven para:

1. Realizar acciones repetidas previamente gravadas de forma más rápida.
2. Crear nueva información o nuevas tablas ordenadas
3. Realizar acciones complejas basadas en cálculos o acciones del usuario; estas acciones van desde
imprimar hasta exportar los datos a otro programa.
4. Presentar en formato o con restricciones definidas por el usuario.
5. Utilizar ventanas personalizadas o uso de interfaz a usuario final

3.1 Crear una Macro

Preliminares:
Antes de crear una macro es necesario evaluar cual es la razón que o los usos que le va a dar , ya que hay
acciones que no necesitan hacerse por medio de macros ya sea porque son demasiado complejas, no se
realizan con mucha frecuencia o no son los usuario finales que la van a utilizar.

Para ello es importante contestar algunas preguntas:

 Quien va a utilizar la macro?
 Cuál es el ambiente? Que datos, hojas y plantillas deben existir antes de hacer una macro?
 Cuando deberá empezar y cuándo terminará de ejecutarse?
 Que se deberá hacer con la información generada?
 Donde se almacena y cuando dejara de utilizarse la macro por cambios en el ambiente donde
corre?
 Como se debe corregir o que documentación anexan o se debe generar para poder detectar errores
o cambios sin tener que hacer una nueva macro.

Las estrategias que se pueden realizar para crear macro son:

 Gravar la macro por medio de acciones que se almacenan en forma de código, que posiblemente el
usuario no experto en la programación pude utilizar con facilidad, Estas acciones son simples y la
toma de decisiones es muy baja yq que la macro se ejecuta siempre de la misma forma.

 Escribir sobre el editor de Visual Basic , por medio de codificación del programa las instrucciones
que el programa utilizar. Estas instrucciones requieren que el usuario conozco el lenguaje de
programación y conozca con claridad la relación con el programa e instrucciones de Excel.

1
lenguaje de programación dirigido por eventos especialmente diseñado para Microsoft por el alemán Alan Cooper

11 Universidad EAN
 Guía de Excel Intermedio 2011
En esta guía, se enseñara la forma de hacer, ejecutar y modificar las macros grabadas por el usuario
previamente para mejorar la rapidez, este método facilita la creación de macros para personas no muy
conocedoras de la programación.

Pasos Para Crear Una Macro en Excel:

1. Defina : cuál es el uso que se le va a dar la macro, por ejemplo automatizar la generación
de formato, generar gráficos, actualizar o generar información, administrar la impresión o
la documentación, etc.(Para el ejmplo vamos a utilizar la plantilla de una factura para
generar nueva información cada vez que se quiera grabar antes de hacer otra factura en la
plantilla)
2. Determine los campos donde vamos a extraer la información y donde la vamos a alojar.

Gráfica No. 9: Plantilla Factura

Los datos que vamos a extraer son:

 Número de la factura
 Nombre del cliente
 Total y fecha de entrega.

Esta Información se encuentra en otra hoja llamada Facturación,:

12 Universidad EAN
 Guía de Excel Intermedio 2011

3. Grabar Macro: Para poder iniciar la grabación es necesario encender el menú de

programación esto lo podemos hacer desde el menú de inicio del programa, el las
opciones de Excel:

Ya activado este menú podremos ver la siguiente herramienta en el área de trabajo:

Allí en la primera cajetilla aparece el botón de grabar macro:

Al ejecutarlo en la barra inferior de estado podremos ver si ya se están grabando las acciones.

Al iniciar una macro nos solicitará un nombre que identificara el código del programa de Visual
Basic y estará alojada al interior de la hoja de cálculo donde se inicio la grabación

Es ideal darle un nombre que identifique que es lo que hace la macro y utilizar el campo de la
descripción para data logar las acciones previas y posteriores a la ejecución de la misma.

13 Universidad EAN
 Guía de Excel Intermedio 2011
Al dar aceptar se iniciará la grabación, la forma de identificar que se está grabando es en la barra
del menú y en la barra de estado:

Gráfico No. 1: Macro Grabando

De este momento hasta que terminemos la grabación todo lo que realizamos quedará grabado
para luego ser ejecutado a petición del usuario. La forma de detenerlo es en el botón detener
grabación, arriba en la barra de programación.

Luego de grabar la macro, debería haber quedado algo así:

Gráfico No. 2: Plantilla de hoja después de terminar

14 Universidad EAN
 Guía de Excel Intermedio 2011
4. Correr la Macro: para saber que quedo bien la macro se deberán hacer cambios en el
formato de factura y ejecutar la macro así:

En el icono de Macros de la barra del Programador, al ejecutarlo se presentará una ventana para
seleccionar que Macros son los que elegiremos correr, buscamos el nombre de la macro
anteriormente grabada y la ejecutaremos. El programa realizará los pasos previamente grabados.

Gráfico No. 3: Selección de las macro a ejecutarse

Otra opción en esta ventana es la de mostrar el código en visual Basic de la macro para una
posterior edición, reparación o cambio:

15 Universidad EAN
 Guía de Excel Intermedio 2011
Gráfico No. 4: Código Visual Basic de la Macro “Facturar”.

Tenga en cuenta al hacer una macro:

 Siempre inicie o ejecute la macro desde el mismo lugar que la grabo
 Si hace cambios de posición del formato base o del lugar donde lleva información es necesario
rehacer la macro o modificarla desde el editor de visual Basic
 Siempre deje información de cómo se hizo, o la lógica de la macro para cuando pasado un tiempo
tenga que modificarse
 Proteja los archivos donde mantiene la macro
 Para poder ejecutar una macro debe bajar el nivel de seguridad

4 REFERENCIAS
http://mpcarvajexcelavanzadojp.wikispaces.com/file/view/TABLAS+DINAMICAS+2003.pdf

16 Universidad EAN