S8_GuiaTablaDinamica

(1)

S8 Guía de uso intermedio

Tablas Dinámicas Excel

(Excel pivot tables)

Archivo descargado de http://www.lawebdelprogramador.com Archivo descargado de http://www.lawebdelprogramador.com

Una Tabla Dinámica, es

Una Tabla Dinámica, es un resumen de datos agrupados, que un resumen de datos agrupados, que

puntualizan en forma concreta datos de una información

(2)

(3)

Indice

INDICE ...

INDICE ...33

1. TABLAS DINÁMICAS EN EXCEL – CREAR UNA

1. TABLAS DINÁMICAS EN EXCEL – CREAR UNA TABLA DINÁMICA.TABLA DINÁMICA.

...

...44

2. TABLAS DINÁMICAS EN EXCEL – ORDENAR FILAS, COLUMNAS, DATOS

...

...88

3. TABLAS DINÁMICAS EN EXCEL – CONSOLIDAR DATOS.

...

...1111

... ...1313 4. MANEJO DE PEQUEÑAS BASES DE DATOS CON TABLAS DINÁMICAS. ...

4. MANEJO DE PEQUEÑAS BASES DE DATOS CON TABLAS DINÁMICAS. ...1616

5. MANEJO DE PEQUEÑAS BASES DE DATOS

5. MANEJO DE PEQUEÑAS BASES DE DATOS CON TABLAS DINÁMICAS -CON TABLAS DINÁMICAS

-ACTUALIZACIÓN DE DATOS ...

ACTUALIZACIÓN DE DATOS ...2020

6. MANEJO DE PEQUEÑAS BASES DE DATOS

6. MANEJO DE PEQUEÑAS BASES DE DATOS CON TABLAS DINÁMICAS – REFERENCIASCON TABLAS DINÁMICAS – REFERENCIAS

DINÁMICAS CON NOMBRES (NAMES). ...

DINÁMICAS CON NOMBRES (NAMES). ...2323

7. TABLAS DINÁMICAS EN EXCEL – AGRUPAR DATOS ...

7. TABLAS DINÁMICAS EN EXCEL – AGRUPAR DATOS ...2626

8. TABLAS DINÁMICAS EN EXCEL - FUNCIÓN IMPORTARDATOSDINAMICOS ...

8. TABLAS DINÁMICAS EN EXCEL - FUNCIÓN IMPORTARDATOSDINAMICOS ...3232

9. TABLAS DINÁMICAS EN EXCEL – MANEJO DE CAMPOS Y RANGOS ...

9. TABLAS DINÁMICAS EN EXCEL – MANEJO DE CAMPOS Y RANGOS ...3535

10. TABLAS DINÁMICAS EN EXCEL – CAMPOS Y ELEMENTOS CALCULADOS. ...

10. TABLAS DINÁMICAS EN EXCEL – CAMPOS Y ELEMENTOS CALCULADOS. ...4343

11. TABLAS DINÁMICAS EN EXCEL – ELIMINAR ELEMENTOS CADUCOS ...

11. TABLAS DINÁMICAS EN EXCEL – ELIMINAR ELEMENTOS CADUCOS ...4949

12. TABLAS DINÁMICAS EN EXCEL – GRÁFICOS ...

(4)

1. Tablas Dinámicas en Excel – Crear

una T

abla

Dinámica

.

Supongamos que tenemos esta tabla (que será nuestra "base de datos") de

ventas de agentes. En cada línea

ventas de agentes. En cada línea aparece el nombre del agente, el mes, elaparece el nombre del agente, el mes, el

año, el monto de ventas (cree una hoja similar a la siguiente usando los datos

de la planilla pivotsp1.xls)

Para generar una tabla dinámica (tabla pívot o pívot table en su versión

inglesa), activamos el menú Datos--->Informe de tablas y

(5)

Se abre un diálogo de tres pasos para construir la tabla dinámica

En el primer paso, en nuestro ejemplo, elegimos la alternativa más sencilla (y la

más común), "lista o base de datos

más común), "lista o base de datos de Microsoft Excel", es decir que de Microsoft Excel", es decir que los datoslos datos

s encuentran en una hoja de

s encuentran en una hoja de Excel (en el mismo cuaderno o en Excel (en el mismo cuaderno o en otro),otro),

En el segundo paso

Debemos señalar la ubicación del rango de datos.

Debemos señalar la ubicación del rango de datos. En una próxima notaEn una próxima nota

veremos como construir una referencia dinámica para este rango,

En el tercer

(6)

En nuestro caso utilizamos le existente Hoja

En nuestro caso utilizamos le existente Hoja 2, señalando la celda A12, señalando la celda A1

Excel abre una plantilla vacía donde construiremos nuestra tabla

Excel abre una plantilla vacía donde construiremos nuestra tabla dinámicadinámica

Los elementos con los cuales construimos la tabla (llamados dimensiones y

medidas), aparecen en el cuadro pequeño a la derecha de la plantilla (lista de

campos de tabla...).

Todo lo que hay que hacer

Todo lo que hay que hacer es arrastrar estos elementos al lugar es arrastrar estos elementos al lugar dondedonde

queremos que aparezcan en la tabla. Naturalmente el elemento "ventas" lo

pondremos en el área de datos.

pondremos en el área de datos. La palabra "campos" que aparece en la zonaLa palabra "campos" que aparece en la zona

de filas y

de filas y de columnas se refiere a los de columnas se refiere a los encabezamientoencabezamientos de las columnas ens de las columnas en

nuestra "base de datos".

Por ejemplo en esta tabla

(7)

hemos ubicado los campos "año" y "mes" en la zona de las

hemos ubicado los campos "año" y "mes" en la zona de las filas y el filas y el campocampo

"nombre" en la zona de las columnas.

Si queremos cambiar la disposición de la

Si queremos cambiar la disposición de la tabla dinámica, todo lo que hay quetabla dinámica, todo lo que hay que

hacer es arrastrar los

hacer es arrastrar los campos fuera de la tabla y campos fuera de la tabla y disponerlos de otra manera.disponerlos de otra manera.

Por ejemplo, para comparar los totales de

Por ejemplo, para comparar los totales de ventas por agente y por ventas por agente y por año,año,

ponemos el campo "nombre" en la zona de las filas

ponemos el campo "nombre" en la zona de las filas y el campo "año" en la y el campo "año" en la zonazona

de las columnas

(8)

2. Tablas Dinámicas en Excel – Ordenar

Filas, Columnas, Datos

Los elementos de la tabla dinámica son campos, que podemos ordenar como

filas o columnas, y datos.

Otros elementos que debemos conocer:

1 – la

1 – la barra de herramientas de las tablas barra de herramientas de las tablas dinámicasdinámicas

En esta nota sólo nos

En esta nota sólo nos referiremos icono "actualizar datos" (el signo dereferiremos icono "actualizar datos" (el signo de

exclamación "

exclamación "!!"). Por definición las tablas "). Por definición las tablas dinámicas no se actualizan con losdinámicas no se actualizan con los

cambios en la base de datos. Para

cambios en la base de datos. Para actualizar la tabla debemos pulsar el iconoactualizar la tabla debemos pulsar el icono

"

"!!".".

2 – Al apretar el botón derecho del mouse, cuando el cursor se encuentra en la

zona de la tabla

zona de la tabla dinámica, se abre un menú.dinámica, se abre un menú.

Aquí nos referiremos sólo al menú "configuración de

Aquí nos referiremos sólo al menú "configuración de campo". Para nuestrocampo". Para nuestro

ejemplo veremos cómo usar las opciones de "Resumir por…" y el

ejemplo veremos cómo usar las opciones de "Resumir por…" y el botónbotón

"Número".

(9)

"Resumir por…" determina el tipo de operación que

"Resumir por…" determina el tipo de operación que utilizará Excel: suma,utilizará Excel: suma,

cuenta, promedio, etc. Importante saber: la primera

cuenta, promedio, etc. Importante saber: la primera opción de Excel es usar laopción de Excel es usar la

operación suma. Si alguno de los datos

operación suma. Si alguno de los datos no es reconocido por Excel comono es reconocido por Excel como

número, la función utilizada será cuenta. Esto

número, la función utilizada será cuenta. Esto también es cierto si hay también es cierto si hay algunaalguna

celda en blanco en el

celda en blanco en el campo de los datos.campo de los datos.

"Número" permite formar las celdas con los mismos formatos de del

"Número" permite formar las celdas con los mismos formatos de del menúmenú

Formato de celdas.

A esta altura ya

A esta altura ya estamos en condiciones de generar tablas dinámicas a partir estamos en condiciones de generar tablas dinámicas a partir

de listas en hojas de Excel.

En la próxima entrada veremos como usar referencias dinámicas. Por qué es

importante esto? Si agregamos datos a nuestra lista,

importante esto? Si agregamos datos a nuestra lista, digamos otro año dedigamos otro año de

ventas, y actualizamos la tabla dinámica, veremos que los datos nuevos no

aparecen en la tabla.

Esto se debe a que no hemos actualizado la referencia al rango en la definición

de la tabla dinámica.

En el archivo Pivot2, se

En el archivo Pivot2, se agrega las ventas del año 2agrega las ventas del año 2003. Sin embargo, tambié003. Sin embargo, tambiénn

después de pulsar el icono de

después de pulsar el icono de actualización los datos no aparecen en la tablaactualización los datos no aparecen en la tabla

dinámica.

Para que esto suceda debemos hacer lo siguiente:

1 – Pulsar el botón "Tabla dinámica" en la barra.

(10)

2 – En el diálogo que se abre, pulsar la opción "asistente"

3 - En el nuevo diálogo apretar el botón "atrás"

4 – Corregir el rango para incluir toda la

4 – Corregir el rango para incluir toda la listalista

5 – Al

(11)

3. Tablas Dinámicas en Excel –

Consolidar Datos.

Uno de los

Uno de los método de consolidar datométodo de consolidar datos en Excel es usando tabs en Excel es usando tablas dinámicaslas dinámicas

(pivot tables).

Consideremos el siguiente ejemplo del

Consideremos el siguiente ejemplo del archivo conspivsp01.xls. Tenemos unarchivo conspivsp01.xls. Tenemos un

cuaderno Excel con una hoja por cada agente

cuaderno Excel con una hoja por cada agente de ventas. En cada una de de ventas. En cada una de estasestas

hojas anotamos las ventas por mes y por

hojas anotamos las ventas por mes y por producto de cada agente.producto de cada agente.

Para consolidar las ventas de todos los agentes (en nuestro caso habrá sólo dos) en una

sola hoja de cálculo usando tablas dinámicas, procedemos de la siguiente manera:

1 – Abrimos una hoja en blanco (en nuestro ejemplo será "consolidado") y activamos el

menú Datos--->Informe de Gráficos y Tablas Dinámicos. En el diálogo que se abre

elegimos la opción "rangos de

(12)

2 – En el siguiente paso debemos instruir a Excel cuantos campos de página queremos.

Para nuestro ejemplo elegimos "crear un solo campo de página"

3 – En el tercer paso debemos informar a Excel dónde se encuentran los rangos que

contienen los datos. Hacemos esto apunto a los rangos en las hojas adecuadas (agente1

y agente2) y apretando el botón "agregar"

4 – Finalmente debemos decirle a Excel dónde ubicar la tabla dinámica (que consolida

los datos)- En nuestro ejemplo la ubicamos en la celda A6

(13)

Apretamos "finalizar" y obtenemos la tabla dinámica.

Esta tabla muestra los totales para cada producto y por cada mes de todos los agentes de

ventas. Si queremos ver los datos de un agente en particular apretamos el botón "Página

1" y elegimos entre "elemento 1" (agente 1) y "elemento 2" (agente 2).

(14)

Dado que los encabezamientos de las columnas tienen un formato de fecha,

podemos agrupar los datos en trimestres en forma

podemos agrupar los datos en trimestres en forma automática. Para hacer estoautomática. Para hacer esto

damos los siguientes pasos:

1 – Hacemos clic con el

1 – Hacemos clic con el botón derecho del mouse en el botón derecho del mouse en el encabezamiento delencabezamiento del

campo "columna", y apretamos "agrupar"

2 – Seleccionamos "trimestres" y cancelamos la selección de "meses" (con un clic del 2 – Seleccionamos "trimestres" y cancelamos la selección de "meses" (con un clic del mouse).

(15)

3 – Apretamos "aceptar" y la

(16)

4.

4. Manejo de pequeñas bases de datos

Manejo de pequeñas bases de datos

con tablas dinámicas.

Es un hecho bastante común manejar pequeñas bases de batos en Excel. Por lo general

se trata de tablas de hasta mil o dos mil líneas y hasta unas 10 o 15 columnas. Excel

ofrece mucha flexibilidad y es muy fácil montar este tipo de base de datos.

Hay muchas consideraciones que hacer sobre este tema, pero en esta entrada nos

limitaremos a los aspectos prácticos.

Una de las mejores funcionalidades de Excel son las tablas pívot, llamadas tablas

dinámicas en la versión en español.

Haremos una rápida revisión del tema, en su versión más sencilla. El archivo del

ejemplo es pivotsp1.xls.

Supongamos que tenemos esta tabla (que será nuestra "base de datos") de ventas de

agentes. En cada línea aparece el nombre del agente, el mes, el año, el monto de ventas

Para generar una tabla dinámica (tabla pívot o pívot table en su versión inglesa),

activamos el menú Datos--->Informe de tablas y gráficos dinámicos

Se abre un diálogo de tres pasos para construir la tabla dinámica

(17)

En el primer paso, en nuestro ejemplo, elegimos la alternativa más sencilla (y la más

común), "lista o base de datos de Microsoft Excel", es decir que los datos s encuentran

en una hoja de Excel (en el mismo cuaderno o en otro),

En el segundo paso

Debemos señalar la ubicación del rango de datos. En una próxima nota veremos como

construir una referencia dinámica para este rango,

En el tercer y último paso debemos decir a Excel dónde poner la tabla dinámica

En nuestro caso utilizamos le existente Hoja 2, señalando la celda A1

(18)

Excel abre una plantilla vacía donde construiremos nuestra tabla dinámica

Los elementos con los cuales construimos la tabla (llamados dimensiones y medidas),

aparecen en el cuadro pequeño a la derecha de la plantilla (lista de campos de tabla...).

Todo lo que hay que hacer es arrastrar estos elementos al lugar donde queremos que

aparezcan en la tabla. Naturalmente el elemento "ventas" lo pondremos en el área de

datos. La palabra "campos" que aparece en la zona de filas y de columnas se refiere a

los encabezamientos de las columnas en nuestra "base de datos".

Por ejemplo en esta tabla

(19)

hemos ubicado los campos "año" y "mes" en la zona de las filas y el campo "nombre"

en la zona de las columnas.

Si queremos cambiar la disposición de la tabla dinámica, todo lo que hay que hacer es

arrastrar los campos fuera de la tabla y disponerlos de otra manera.

Por ejemplo, para comparar los totales de ventas por agente y por año, ponemos el

campo "nombre" en la zona de las filas y el campo "año" en la zona de las columnas

(20)

5.

5. Manejo de pequeñas bases de datos

Manejo de pequeñas bases de datos

con tablas dinámicas

-

Actualización de

Datos

En el apartado anterior sobre manejo de pequeñas bases datos en Excel, vimos que a

partir de una lista que cumpla ciertas condiciones mínimas, Excel puede construir tablas

dinámicas con las cuales podemos realizar análisis de datos en forma eficiente y

sorprendentemente rápida.

La lista debe tener encabezamientos en su primer fila, de tal manera que Excel los pueda

reconocer como tales.

reconocer como tales. El archivo EjempEl archivo Ejemplo es pivolo es pivotsp1.xls.tsp1.xls.

Los elementos de la tabla dinámica son campos, que podemos ordenar como filas o

columnas, y datos.

Otros elementos que debemos conocer:

1 – la barra de herramientas de las tablas dinámicas

En esta nota sólo nos referiremos icono "actualizar datos" (el signo de exclamación "

!

").").

Por definición las tablas dinámicas no se actualizan con los cambios en la base de datos.

Para actualizar la tabla debemos pulsar el icono "

!

".".

2 – Al apretar el botón derecho del mouse, cuando el cursor se encuentra en la zona de

la tabla dinámica, se abre un menú.

(21)

Aquí nos referiremos sólo al menú "configuración de campo". Para nuestro ejemplo

veremos cómo usar las opciones de "Resumir por…" y el botón "Número".

"Resumir por…" determina el tipo de operación que utilizará Excel: suma, cuenta,

promedio, etc. Importante saber: la primera opción de Excel es usar la operación suma.

Si alguno de los datos no es reconocido por Excel como número, la función utilizada

será cuenta. Esto también es cierto si hay alguna celda en blanco en el campo de los

datos.

"Número" permite formar las celdas con los mismos formatos de del menú Formato de

celdas.

A esta altura ya estamos en condiciones de generar tablas dinámicas a partir de listas en

hojas de Excel.

En la próxima entrada veremos como usar referencias dinámicas. Por qué es importante

esto? Si agregamos datos a nuestra lista, digamos otro año de ventas, y actualizamos la

tabla dinámica, veremos que los datos nuevos no aparecen en la tabla.

Esto se debe a que no hemos actualizado la referencia al rango en la definición de la

tabla dinámica.

Si revisamos el archivo pivotsp2.xls, verán que he agregado las ventas del año 2003. Sin

embargo, también después de pulsar el icono de actualización los datos no aparecen en

la tabla dinámica.

Para que esto suceda debemos hacer lo siguiente:

1 – Pulsar el botón "Tabla dinámica" en la barra.

(22)

2 – En el diálogo que se abre, pulsar la opción "asistente"

3 - En el nuevo diálogo apretar el botón "atrás"

4 – Corregir el rango para incluir toda la lista

5 – Al apretar "Finalizar", los nuevos datos serán incluidos en la tabla dinámica

(23)

6.

6. Manejo de pequeñas bases de datos

Manejo de pequeñas bases de datos

con tablas dinámicas –

Referen

cias

dinámicas con nombres (NAMES).

En el apartado anterior sobre

En el apartado anterior sobre tablas dinámicastablas dinámicas vimos que cada vez quevimos que cada vez que

agregamos datos a la base de datos,

agregamos datos a la base de datos, debemos actualizar la referencia al rango.debemos actualizar la referencia al rango.

De no hacer esto, las nuevas líneas de datos no aparecerán en la tabla

dinámica.

En ese ejemplo hicimos esto manualmente. Excel nos permite construir

referencias dinámicas, que se actualizan con los cambios en el

referencias dinámicas, que se actualizan con los cambios en el tamaño de latamaño de la

base de datos. La técnica para

base de datos. La técnica para hacer esto es utilizar "nombres" (NAMES).hacer esto es utilizar "nombres" (NAMES).

Los nombres pueden referirse no sólo a

Los nombres pueden referirse no sólo a rangos sino también a fórmulas. Pararangos sino también a fórmulas. Para

crear el rango dinámico en nuestro

crear el rango dinámico en nuestro caso definiremos un nombre que contendrácaso definiremos un nombre que contendrá

la formula DESREF (Offset en la versión inglesa). El archivo de este ejemplo se

lla pivotsp3.xls.

La sintaxis de esta fórmula es

La sintaxis de esta fórmula es la siguiente; DESREF(referenciala siguiente; DESREF(referencia

;filas;columnas;alto;ancho) donde:

referencia

referencia: la celda en el ángulo superior derecho de la lista (en nuestro caso: la celda en el ángulo superior derecho de la lista (en nuestro caso

será A1);

filas

filas: para nuestro uso será siempre 0: para nuestro uso será siempre 0

columnas

columnas: para nuestro uso será siempre 0: para nuestro uso será siempre 0

alto

alto: la cantidad de filas en nuestra lista: la cantidad de filas en nuestra lista

ancho

ancho: la cantidad de : la cantidad de columnas en nuestra lista.columnas en nuestra lista.

Para convertir esta fórmula en

Para convertir esta fórmula en dinámica, usaremos la funcióndinámica, usaremos la funciónCONTARACONTARA parapara

determinar los valores de los parámetros alto

determinar los valores de los parámetros alto y ancho. Esta es nuestra fórmula:y ancho. Esta es nuestra fórmula:

=DESREF(Hoja1!$A$1,0,0,CONTARA(Hoja1!$A:$A),CONTARA(Hoja1!

$1:$1))

(24)

como se puede ver comenzamos en la celda A1, contamos cuantos valores hay

en la columna A (que representan la cantidad de líneas de la tabla) y también

contamos la cantidad de valores en la

contamos la cantidad de valores en la línea 1 (que representan la línea 1 (que representan la cantidad decantidad de

columnas en la lista).

Ahora sólo nos queda reemplazar la referencia al rango de datos en la lista por

el nombre que acabamos de definir. Los pasos a

el nombre que acabamos de definir. Los pasos a seguir son:seguir son:

1 – activamos la Hoja2 y

1 – activamos la Hoja2 y ubicamos el mouse en alguna de las celdas de ubicamos el mouse en alguna de las celdas de lala

tabla dinámica

2 – abrimos el

2 – abrimos el menú Tabla Dinámica--->Asistentemenú Tabla Dinámica--->Asistente

3 – pulsamos el botón "atrás"; en

(25)

con el nombre (la forma

con el nombre (la forma más práctica es pulsar F3)más práctica es pulsar F3)

A partir de este momento, la referencia de la tabla dinámica se adaptará

automáticamente de acuerdo a la cantidad de líneas que

automáticamente de acuerdo a la cantidad de líneas que agreguemos, o queagreguemos, o que

borremos, a la base de

(26)

7.

Tablas Dinámicas en Excel – Agrupar

datos

En el mes de

En el mes de marzo publiqué una serie de notas sobre elmarzo publiqué una serie de notas sobre el manejo de pequeñasmanejo de pequeñas

bases de datos en Excel usando tablas dinámicas

bases de datos en Excel usando tablas dinámicas (pivot tables). En estas notas(pivot tables). En estas notas

cubría aspectos funcionales de las tablas dinámicas como actualización de

datos y referencias

datos y referencias dinámicasdinámicas

En esta nota veremos otras funcionalidades de las

En esta nota veremos otras funcionalidades de las tablas dinámicas quetablas dinámicas que

ayudan a hacer el trabajo

ayudan a hacer el trabajo con ellas más eficiente.con ellas más eficiente.

Para este ejercicio usaremos una tabla td_trucos.xls que contiene

Para este ejercicio usaremos una tabla td_trucos.xls que contiene las ventas,las ventas,

día por día, de

día por día, de cuatro departamentos de una empresa imaginaria durante lacuatro departamentos de una empresa imaginaria durante la

primera mitad del año.

Nuestra tabla tiene 725 líneas (181 días * 4 departamentos + 1 línea para los

encabezamientos).

Con facilidad creamos un informe que nos muestra el

Con facilidad creamos un informe que nos muestra el total de ventas del mediototal de ventas del medio

año por departamento

El campo Fechas lo hemos puesto en el

El campo Fechas lo hemos puesto en el área de páginas de manera queárea de páginas de manera que

podemos elegir una fecha determinado y ver las

podemos elegir una fecha determinado y ver las ventas por departamentoventas por departamento

El menú de Tablas Dinámicas ofrece la posibilidad

(27)

campos que Excel puede reconocer como fechas. Para usar esta funcionalidad

el campo de fechas debe encontrarse en el

el campo de fechas debe encontrarse en el área de las filas área de las filas o de las columnas.o de las columnas.

Por lo tanto

Por lo tanto movemos el campo Fechas al área de columnasmovemos el campo Fechas al área de columnas

Ahora seleccionamos el rótulo Fecha, abrimos el menú de tablas

Ahora seleccionamos el rótulo Fecha, abrimos el menú de tablas dinámicas ydinámicas y

elegimos la opción "agrupar y mostrar detalle"

Al elegir esta opción se abre un diálogo donde podemos definir la forma de

agrupar los datos. Las opciones van desde segundos a años. También

podemos definir las fechas de comienzo y fin.

(28)

Por ejemplo, si

Por ejemplo, si elegimos "trimestres" obtenemoselegimos "trimestres" obtenemos

También podemos elegir más de

También podemos elegir más de una opción simultáneamente, por ejemplouna opción simultáneamente, por ejemplo

trimestre y mes

Con este resultado

Como habrán notado, no existe la

Como habrán notado, no existe la opción de agrupar por semanas. Pero Excelopción de agrupar por semanas. Pero Excel

nos permite hacer esto, si seleccionamos "días" y en la

nos permite hacer esto, si seleccionamos "días" y en la ventanilla "número deventanilla "número de

días" ponemos "7"

(29)

El resultado es

Nuestro ejemplo tiene una limitación potencial, ya que

Nuestro ejemplo tiene una limitación potencial, ya que el número de columnasel número de columnas

de una hoja de Excel es 256 y el número de días del año es 365 (o 366 si es

bisiesto).

Es decir que si tuviéramos las ventas de todo el año, día por día, no podríamos

poner el campo Fecha en el

poner el campo Fecha en el área de columnas, para luego agruparlo.área de columnas, para luego agruparlo.

Lo que podemos hacer en este caso, es

Lo que podemos hacer en este caso, es poner el campo Fecha en el poner el campo Fecha en el área deárea de

filas,

(30)

agrupar, por ejemplo por mes

y luego arrastrar los

(31)

(32)

8

8 . T

. T

abl

as D

iná

mica

s en

Exce

l -

Fun

ció

n

IMPORTARDATOSDINAMICOS

Si queremos crear una referencia a una

Si queremos crear una referencia a una de las celdas de la de las celdas de la tabla dinámicatabla dinámica

fuera de ella, Excel

fuera de ella, Excel utiliza automáticamente la funciónutiliza automáticamente la función

IMPORTARDATOSDINAMICOS.

Por ejemplo, si queremos crear una referencia al total de ventas del 2005 en la

celda A12

El objetivo de IMPORTARDATOSDINAMICOS es extraer datos de

El objetivo de IMPORTARDATOSDINAMICOS es extraer datos de la tablala tabla

dinámica, basados en los argumentos de la función, cuya sintaxis es

IMPORTARDATOSDINAMICOS(c

IMPORTARDATOSDINAMICOS(campo_datos,tabla_dinampo_datos,tabla_dinámica,campo1,ámica,campo1,

elemento1,campo2,elemento2,…)

Por lo general queremos crear la referencia a la celda sin el uso de esta

función. Existen dos maneras de hacer esto:

1 - Crear

1 - Crear la referencia manualmente, es decir seleccionar A12 y la referencia manualmente, es decir seleccionar A12 y escribir "=B10"escribir "=B10"

en la barra de las fórmulas

2 – Cancelar la

2 – Cancelar la opción "generar getpivotdata". Esta opción es poco conocida.opción "generar getpivotdata". Esta opción es poco conocida.

Existe un icono en que nos permite activar o desactivar la opción. Para instalar

el icono hacemos lo siguiente:

a. Abrimos el

a. Abrimos el menú Herramientas—Personalizar menú Herramientas—Personalizar

b. En la pestaña Comandos elegimos Datos. Al final de la lista aparece el icono

Generar Getpivodata

(33)

c. Arrastramos el icono a

c. Arrastramos el icono a alguna de las barras de alguna de las barras de herramientas (lo más lógicoherramientas (lo más lógico

es instalarlo en la

es instalarlo en la barra de Tablas Dinámicas)barra de Tablas Dinámicas)

Este icono funciona como un interruptor. Con

Este icono funciona como un interruptor. Con un clic desactivamos la opción yun clic desactivamos la opción y

al crear una referencia veremos sólo la dirección de la celda

(34)

Un detalle a notar es que referencias directas heredan el formato de la celda de

referencia; referencias con Getpivotdata reciben el formato "General".

(35)

9.

9. Tablas Dinámicas en Excel – Manejo

Tablas Dinámicas en Excel – Manejo

de campos y rangos

Para las funcionalidades que veremos en esta nota hemos ampliado el

Para las funcionalidades que veremos en esta nota hemos ampliado el archivoarchivo

‘tablas dinamicas

‘tablas dinamicas campos y rcampos y rangos.xls’, angos.xls’, agregándole datos agregándole datos del período dedel período de

ventas del año anterior y

ventas del año anterior y el campo "Año".el campo "Año".

Cuando agregamos líneas o columnas a la base de datos,

Cuando agregamos líneas o columnas a la base de datos, debemos modificar debemos modificar

la información para que los nuevos datos sean

la información para que los nuevos datos sean incluidos en la tabla dinámica.incluidos en la tabla dinámica.

Para esto activamos el asistente de tablas

Para esto activamos el asistente de tablas dinámicasdinámicas

Apretamos el botón "Atrás"

Seleccionamos el nuevo rango y

Seleccionamos el nuevo rango y apretamos "Finalizar"apretamos "Finalizar"

Como podemos ver, la lista de

(36)

Para presentar una comparación, mes por mes, de los

Para presentar una comparación, mes por mes, de los dos años, arrastramos eldos años, arrastramos el

campo "Año" al área de campos de columna

El total de las filas es innecesario y por lo tanto

El total de las filas es innecesario y por lo tanto lo eliminamos quitando lalo eliminamos quitando la

marca de la casilla "Totales de fila" en el diálogo de opciones de tabla

Rehacemos ahora la tabla colocando el campo Año en el

Rehacemos ahora la tabla colocando el campo Año en el área de página,área de página,

Departamento en el área de filas y Mes en el área de columnas

(37)

En esta situación, si agregamos Unidades al área

En esta situación, si agregamos Unidades al área de datos, Excel ubica losde datos, Excel ubica los

nuevos datos en una línea por

nuevos datos en una línea por debajo del campo Ventasdebajo del campo Ventas

Si queremos que los datos de

Si queremos que los datos de Ventas y Unidades aparezcan unos al lado deVentas y Unidades aparezcan unos al lado de

los otros, arrastramos Datos a la zona de columnas, a la izquierda de Mes

Alternativamente podemos arrastrar Mes por debajo de Datos y

Alternativamente podemos arrastrar Mes por debajo de Datos y obtener estaobtener esta

tabla

Ahora veremos otras posibilidades de presentación de datos en las

Ahora veremos otras posibilidades de presentación de datos en las tablastablas

dinámicas

(38)

Volvamos al

Volvamos al ejemploejemplo, que hemos adaptado al tema que , que hemos adaptado al tema que vamos a tratar vamos a tratar

Si queremos presentar las ventas de cada departamento como porcentaje del

total hacemos lo siguiente:

1 - Seleccionamos alguna de las celdas de

1 - Seleccionamos alguna de las celdas de datos de la tabla, abrimos el datos de la tabla, abrimos el menúmenú

de tablas dinámicas y activamos la opción "configuración de campo"

2 - Pulsamos el botón

2 - Pulsamos el botón Opciones, en la ventanilla "Mostrar datos como"Opciones, en la ventanilla "Mostrar datos como"

elegimos "% de columna" y

(39)

Excel calcula el porcentaje de cada departamento en forma automática. Si

queremos exhibir el campo de las ventas junto

queremos exhibir el campo de las ventas junto con el porcentaje de cadacon el porcentaje de cada

departamento, hacemos lo siguiente:

1 – Agregamos nuevamente el campo de ventas al área

1 – Agregamos nuevamente el campo de ventas al área de datosde datos

2 - Seleccionamos alguna de las celdas del

2 - Seleccionamos alguna de las celdas del nuevo campo y volvemos a aplicar nuevo campo y volvemos a aplicar

el método anterior

Por supuesto, existe también la opción "%

Por supuesto, existe también la opción "% de fila". Para dar de fila". Para dar un ejemploun ejemplo

modificamos la tabla dinámica poniendo el campo Departamento en el campo

de columnas

(40)

Volvemos sobre el método anterior pero elegimos "% de

Volvemos sobre el método anterior pero elegimos "% de la fila"la fila"

Y obtenemos esta tabla

tablas dinamicas campos y rangos

(41)

Otras posibilidades son:

Diferencia de

Al elegir esta opción se

Al elegir esta opción se abre una ventanilla donde debemos elegir el campo y elabre una ventanilla donde debemos elegir el campo y el

elemento para realizar la comparación. En nuestro caso queremos comparar

ventas de cada mes en relación a

ventas de cada mes en relación a eneroenero

El resultado no es de

El resultado no es de lo más presentable, pero esta opción puede ser lo más presentable, pero esta opción puede ser muymuy

práctica cuando trabajamos con tablas de gran tamaño.

Otra opción práctica es "Total en"

Otra opción práctica es "Total en" (que en inglés se llama ti(que en inglés se llama tiene el nombre másene el nombre más

apropiado Running Total). Aquí aplicamos la técnica de

apropiado Running Total). Aquí aplicamos la técnica de poner dos veces elponer dos veces el

mismo campo en el área de datos

(42)

Y así obtenemos una tabla con las

(43)

10.

10. Tablas Dinámicas en Excel – Campos

Tablas Dinámicas en Excel – Campos

y Elementos calculados.

Las tablas dinámicas son un tipo de objeto que "reside" en la hoja de cálculos pero el

rango ocupado por la tabla tiene un comportamiento distinto a los rangos normales de

Excel. El rango ocupado por la tabla dinámica no puede ser modificado directamente en

la hoja. Por ejemplo, no podemos agregar líneas o columnas, o fórmulas en las distintas

celdas.

Cuando queremos hacer este tipo de modificaciones en una tabla dinámica tenemos dos

posibilidades:

1 - seleccionar la tabla y copiarla a otra ubicación (ya sea en la misma hoja, en otra o en

otro cuaderno) usando Edición – Pegado Especial – Valores. Este método tiene la

ventaja de dar mucha flexibilidad en el manejo de los datos (formatos, fórmulas, etc);

pero tiene la gran desventaja de romper el vínculo dinámico entre la tabla y los datos

originales.

2 – Crear campos y/o elementos calculados. De esto nos ocuparemos en esta nota.

Basándonos en nuestra tabla de datos hemos creado esta tabla dinámica

En el área de Página hemos puesto los meses, de manera que podemos ver los datos de

cada mes con un clic.

Ahora digamos que queremos calcular el precio promedio de las unidades vendidas

(ventas / unidades = precio promedio) y también agrupar los meses por bimestres (enero

+ febrero = bimestre 1).

El precio promedio consiste en crear un nuevo campo, dividiendo los elementos de un

campo por los de otro. Esto es un

campo calculado

. Los pasos a dar son . Los pasos a dar son los siguientes:los siguientes:

1 – cliqueamos en algún lugar de la tabla y en el asistente de Tablas Dinámicas

activamos Fórmulas – Campos Calculados

(44)

2 - En la ventanilla "nombre" anotamos "Precio Promedio" y en la ventanilla "Fórmula"

=Ventas/Unidades (con la ventanilla activada, hacemos doble clic a Ventas, luego

anotamos el símbolo "/" y luego doble clic a Unidades)

3 - Después de pulsar "Aceptar" y realizar algunos ajustes al formato, obtenemos esta

tabla dinámica

(45)

Como ven, hemos agregado un nuevo campo a la tabla: "Precio Promedio".

Para demostrar el uso de

elementos calculados

, reorganizamos nuestra tabla dinámica, reorganizamos nuestra tabla dinámica

poniendo los meses como campos de fila y los departamentos en el área de Página

Para crear el elemento calculado "Bimestre 1" procedemos de la siguiente manera:

1 - Seleccionamos la celda A4 (donde aparece "Mes", el nombre del campo con cuyos

elementos crearemos un elemento calculado), en el asistente de Tablas Dinámicas

activamos el menú Fórmulas – Elementos Calculados

2 - En el diálogo que se abre seleccionamos "Mes" en la ventanilla "Campos"; en la

ventanilla "elementos" vemos los meses (los elementos del campo). En la ventanilla

nombre escribimos Bimestre 1; luego seleccionamos la ventanilla Fórmula y anotamos

=enero+febrero (lo que se hace con un doble clic sobre el nombre del elemento). Luego

apretamos Enter y volvemos a seleccionar el campo Mes

(46)

3 - Repetimos la operación para el Bimestre 2 (marzo+abril) y pulsamos "Aceptar".

Excel tiene dos problemas relacionados con los elementos calculados:

1 - Son agregados automáticamente al final de la lista de elementos del campo

2 – El total general incluye los elementos calculados, por lo tanto da como resultado el

doble de lo que debería ser.

El primer problema lo resolvemos moviendo los elementos a la posición deseada. Una

de las formas de hacer esto es copiar Bimestre 1 y pegarlo inmediatamente debajo de

"febrero" (o escribir manualmente Bimestre 1). Excel reorganiza la tabla

automáticamente

(47)

El problema del total general lo podemos tratar de dos maneras. Una es eliminar el total

general para las columnas en Opciones de Tablas.

La otra es crear

La otra es crear un nuevo elemento, Total, que suma Bimestre 1 y un nuevo elemento, Total, que suma Bimestre 1 y Bimestre 2Bimestre 2

Obteniendo

(48)

En resumen:

Un

campo calculado

es un nuevo campo creado realizando operaciones con camposes un nuevo campo creado realizando operaciones con campos

existentes.

Un

elemento calculado

es un nuevo elemento en un campo, creado con los elementoses un nuevo elemento en un campo, creado con los elementos

existentes del campo.

Para eliminar los campos o elementos calculados tenemos que usar el asistente de

Tablas Dinámicas. Por ejemplo, para eliminar el campo Precio Promedio abrimos el

diálogo de Fórmulas en Campos Calculados

En la ventanilla Nombre elegimos el campo que queremos eliminar y luego pulsamos el

botón Eliminar.

(49)

1

1 . T

. T

abl

as

Diná

mica

s e

n E

xce

l –

Elim

inar

elementos caducos

En el apartado anterior, notarán

En el apartado anterior, notarán que ciertos elementos siguen apareciendo enque ciertos elementos siguen apareciendo en

las listas desplegables a pesar de que

las listas desplegables a pesar de que los hemos borrado de la base los hemos borrado de la base de datos.de datos.

Estos elementos persisten aún después de haber actualizado la tabla.

los meses de mayo y junio siguen apareciendo a

los meses de mayo y junio siguen apareciendo a pesar de haber sido borradospesar de haber sido borrados

de la lista de datos.

Para que los elementos que han sido borrados de la lista de datos (los

elementos "caducos") desaparezcan hacemos lo siguiente:

1 - Eliminamos los elementos calculados que hubiéramos definido (en nuestro

caso Bimestre 1, Bimestre 2 y

caso Bimestre 1, Bimestre 2 y Total). Para eso abrimos el Total). Para eso abrimos el menú Formulas delmenú Formulas del

asistente de tablas dinámicas, elegimos el elemento calculado que queremos

borrar y pulsamos el botón Eliminar.

(50)

2 - Quitamos el

2 - Quitamos el campo que contiene los elementos arrastrándolo fuera delcampo que contiene los elementos arrastrándolo fuera del

rango de la tabla

rango de la tabla dinámicadinámica

3 - Actualizamos la tabla

3 - Actualizamos la tabla dinámica con el botón de actualizar dinámica con el botón de actualizar

4 - Agregamos de nuevo el

4 - Agregamos de nuevo el campo a la tabla. Sólo campo a la tabla. Sólo los elementos existentes enlos elementos existentes en

la base de datos aparecen ahora en

la base de datos aparecen ahora en la lista desplegable de elementos della lista desplegable de elementos del

campo.

(51)

(52)

12. Tablas Dinámicas en Excel – Gráficos

Excel nos permite crear un gráfico

Excel nos permite crear un gráfico basado en una tabla dinámica con un basado en una tabla dinámica con un solosolo

clic. En nuestro ejemplo

Apretamos el icono de gráficos en

Apretamos el icono de gráficos en la barra de herramientas de tablas la barra de herramientas de tablas dinámicasdinámicas

y obtenemos un gráfico en una nueva hoja

Si queremos presentar el gráfico en

Si queremos presentar el gráfico en la misma hoja de la la misma hoja de la tabla dinámica,tabla dinámica,

cambiamos su ubicación (clic en el botón derecho del

cambiamos su ubicación (clic en el botón derecho del mouse) a la hoja de mouse) a la hoja de lala

tabla

(53)

El gráfico esta ligado a la tabla dinámica de manera que todo cambio en la

tabla afecta inmediatamente al gráfico, y viceversa. Como se

tabla afecta inmediatamente al gráfico, y viceversa. Como se puede ver, en elpuede ver, en el

gráfico aparecen los mismos botones de campos que aparecen en la tabla.

Por ejemplo, si cambiamos la selección de departamentos en el

Por ejemplo, si cambiamos la selección de departamentos en el gráfico paragráfico para

mostrar sólo los departamentos 1 y 2

(54)

al apretar Aceptar veremos los cambios también en la

al apretar Aceptar veremos los cambios también en la tabla dinámicatabla dinámica

Los gráficos basados en tablas dinámicas son menos flexibles que los gráfi

Los gráficos basados en tablas dinámicas son menos flexibles que los gráficoscos

basados en tablas de datos corrientes de Excel.

basados en tablas de datos corrientes de Excel. Si cambiamos formatos en elSi cambiamos formatos en el

gráfico original (por ejemplo el color de una serie), al actualizar la tabla

gráfico original (por ejemplo el color de una serie), al actualizar la tabla volveránvolverán

a aparecer los formatos originales. Otra limitación es que ciertos tipos de

gráficos no son permitidos. Si intentan convertir el gráfico a uno del tipo XY

recibirán esta advertencia

(55)

Tampoco podemos crear un gráfico usando sólo parte de las

Tampoco podemos crear un gráfico usando sólo parte de las celdas en la tablaceldas en la tabla

dinámica. Aún cuando seleccionemos sólo un rango de la tabla,

dinámica. Aún cuando seleccionemos sólo un rango de la tabla, Excel generaráExcel generará

un gráfico basado en todos los

un gráfico basado en todos los datos presentes en la tabla dinámica.datos presentes en la tabla dinámica.

Para sobreponernos a estas limitaciones lo que tenemos que hacer,

básicamente, es quebrar el vínculo entre el gráfico y la tabla dinámica. Esto lo

podemos hacer de varias

podemos hacer de varias maneras:maneras:

1 – Seleccionar la tabla

1 – Seleccionar la tabla y usar Copiar—Pegado Especial—Valores para copiar y usar Copiar—Pegado Especial—Valores para copiar

los datos en formar estática en alguna otra ubicación. Luego a partir de estos

datos generar el gráfico deseado. Si copiamos sólo una parte de la tabla, no

hace falta usar Pegado Especial—Valores. También con Pegar (Ctrl+V)

obtenemos datos estáticos.

2- Seleccionar toda la tabla,

2- Seleccionar toda la tabla, copiar (Ctrl+C) y lcopiar (Ctrl+C) y luego Pegado Especial—Valores.uego Pegado Especial—Valores.

3 – Copiar el dato seleccionado de una tabla dinámica a un gráfico sin datos

creado previamente.