COMO CREAR UNA TABLA DINAMICA

Crear una tabla dinámica

Una tabla dinámica consiste en el resumen de un conjunto de datos, atendiendo a varios criterios de agrupación, representado como una tabla de doble entrada que nos facilita la interpretación de dichos datos. Es dinámica porque nos permite ir obteniendo diferentes totales, filtrando datos, cambiando la presentación de los datos, visualizando o no los datos origen, etc...
Para aquellos que tengais conocimientos de Access es lo más parecido a una consulta de referencias cruzadas, pero con más interactividad.
Veamos cómo podemos crear una tabla dinámica a partir de unos datos que ya tenemos.
Para crear una tabla dinámica, Excel nos proporciona el Asistente para tablas y gráficos dinámicos.
Supongamos que tenemos una colección de datos de los artículos del almacen con el número de referencia y el mes de compra, además sabemos la cantidad comprada y el importe del mismo.
Vamos a crear una tabla dinámica a partir de estos datos para poder examinar mejor las ventas de cada artículo en cada mes.
Para acceder al asistente accedemos al menú Datos - Informe de tablas y gráficos dinámicos.
Aparece la primera pantalla de tres de las que se compone el asistente para tablas dinámicas, donde nos solicita que indiquemos la situación de los datos a analizar y qué tipo de informe vamos a crear.


En nuestro caso indicamos que vamos a seleccionar los de la Lista de Excel y que vamos a crear una Tabla dinámica.
En la segunda pantalla seleccionamos el rango dónde tenemos los datos a utilizar en la tabla dinámica.
Debemos seleccionar todas las celdas incluyendo las cabeceras.


A continuación nos aparece el tercer paso del asistente...






En esta última pantalla debemos indicar dónde vamos a situar el resultado de la tabla dinámica. Hemos escogido situarla en una Hoja de cálculo nueva.
Si pulsamos sobre Diseño... accedemos a un cuadro de diálogo donde podemos personalizar la forma en que van a verse los datos en la tabla dinámica.

En la imagen podemos apreciar que nos aparece una estructura formada por las filas, columnas, datos y además un campo para el nº de página. Y en el lateral derecho tenemos los campos disponibles de la tabla, los cuales podemos arrastrar a las diferentes partes del diagrama de la izquierda.
Por ejemplo podríamos contruir una tabla dinámica con la siguiente estructura:
- En el campo Fila arrastramos REF, las referencias de nuestros artículos.
- En el campo Columna arrastramos MES.
- Y en Datos arrastramos IMPORTE
Nos quedaría una estructura como podemos ver en la imagen de abajo.


Tras realizar la tabla dinámica este sería el resultado obtenido.







Podemos ver que la estructura es la que hemos definido anteriormente, en el campo fila tenemos las referencias, en el campo columnas tenemos los meses y en el centro de la tabla las sumas de los importes.
Con esta estructura es mucho más fácil analizar los resultados.
Una vez creada la tabla dinámica nos aparece en la hoja, la barra de herramientas Tabla dinámica:

(La podemos visualizar y ocultar con la opción de menú Ver -- Barras de Herramientas.)
En esta barra nos encontramos botones útiles como el botón para darle a la tabla un formato predefinido, y otros que veremos a lo largo del tema..
También aparece el cuadro Lista de campos de tabla dinámica que nos permite modificar la estructura de la tabla añadiendo campos a las distintas zonas, o bien utilizando el botón Agregar a en combinación con el cuadro de lista que aparece al lado del botón, o simplemente arrastrándo el campo sobre la zona deseada.
De igual forma podemos quitar un campo de un zona arrastrándolo fuera de la tabla.
Con esto vemos que en un segundo podemos variar la estructura de la tabla y obtener otros resultados sin casi esfuerzos.
Esta lista de campos se puede mostrar u ocultar con el botón de la barra Tabla dinámica.
Si arrastrásemos a la zona de datos los campos cantidad y total, obtendríamos la siguiente tabla, más compleja pero con más información:



Eliminar una tabla dinámica.
Para eliminar una tabla dinámica simplemente debemos seleccionar la tabla en su totalidad y presionar la tecla Supr o con la tabla seleccionada hacer clic con el botón derecho del ratón y seleccionar Eliminar del menú Pop-up.
Aplicar filtros a una tabla dinámica
Otra característica útil de las tablas dinámicas es permitir filtrar los resultados y así visualizar unicamente los que nos interesen en un momento determinado. Esto se emplea sobre todo cuando el volumen de datos es importante.
Los campos principales vemos que están acompañados, en su parte derecha, de una flecha indicando una lista desplegable.
Por ejemplo, si pulsamos sobre la flecha del campo Mes nos aparece una lista como vemos en la imagen con los distintos meses disponibles en la tabla con una casilla de verificación en cada uno de ellos para indicar si los queremos ver o no, más una opción para marcar todas las opciones en este caso todos los meses.
Si dejamos marcados los meses Enero y Febrero, los otros meses desaparecerán de la tabla, pero no se pierden, en cualquier momento podemos visualizarlos volviendo a desplegar la lista y marcando la casilla (Mostrar todo).
Para cerrar este cuadro debemos pulsar en Aceptar o sobre Cancelar para cerrar y dejarlo como estaba.
Aplicando el filtro a varios campos podemos formar condiciones de filtrado más complejas, por ejemplo podemos seleccionar ver los articulos con referencia 1236 de Abril.

Obtener promedios en una tabla dinámica
Por defecto, al crear una tabla dinámica, Excel nos genera unos totales con sumatorio, puede interesarnos modificar esas fórmulas por otras como pueden ser sacar el máximo o el mínimo, el promedio, etc.
Para hacer esto debemos situarnos en cualquier celda de la zona que queremos rectificar y hacer clic con el botón derecho del ratón, nos aparece un menú Pop-up con diferentes opciones, debemos escoger la opción Configuración de campo... y nos aparece un cuadro de diálogo como el que vemos en la imagen.
En este cuadro de diálogo podemos escoger cómo queremos hacer el resumen, mediante Suma, Cuenta, Promedio, etc.
También podemos abrir el cuadro de diálogo con el botón de la barra Tabla dinámica.

Tablas dinámicas en tres dimensiones
Crear una tabla en tres dimensiones no es más que añadir un campo más a la tabla de origen y luego utilizarlo como campo principal para el resumen.
Por ejemplo, para continuar con nuestra simulación añadiremos un campo más de "Semana" y ahora podremos organizar por mes y semana.
Para crear la tabla dinámica en tres dimensiones procederíamos del mismo modo que hemos visto anteriormente, es decir:
- Accedemos a Datos - Informe de tablas y gráficos dinámicos.
- Selecionamos el rango de datos, ahora con el campo de semana. (Puede que nos pregunte si queremos utilizar los datos de la anterior tabla dinámica, contestamos que NO).
- Y en la estructura de la tabla la única diferencia es utilar el campo Página para la tercera dimensión.
Por ejemplo si colocamos en Página el campo Mes, en Columna el campo Semana, veremos los resultados de un mes en cada página.
Gráficos con tablas dinámicas
Para crear una gráfica de nuestra tabla dinámica debemos hacer clic con el botón derecho del ratón sobre cualquier celda de la tabla, y del menú Pop-up que nos aparece debemos seleccionar Gráfico dinámico, nos genera un gráfico de barras por defecto en una nueva hoja de gráficos. También podemos utilizar el botón de la barra Tabla dinámica.
Para cambiar el formato del gráfico a otro tipo de gráfico que nos agrade más o nos convenga más según los datos que tenemos, debemos hacer clic con el botón derecho del ratón sobre el gráfico y del menú Pop-up que nos aparece escoger la opción Tipo de gráfico... y escoger el gráfico que más nos convenga, la mecánica a seguir para trabajar con gráficos es la misma que se vió en el tema de gráficos.

En este tema estudiaremos algunas características avanzadas de Excel que nos pueden ser de utilidad cuando queramos realizar algún trabajo un poco más profesional o que se salga un poco de lo corriente.
Diferentes formas de cambiar un formato
Para modificar el formato de una o varias celdas, podemos elegir entre las distintas formas que nos proporcina Excel, es interesante conocerlas todas para luego elegir la que nos resulte más cómoda.
Estas son las diferentes formas que podemos utilizar:

Utilizando la barra de formato
Ya hemos visto, en temas anteriores, que en la barra de herramientas Formato, tenemos varios iconos para asignar de forma rápida un determinado formato a los números incluidos en las celdas .
Los formatos incluidos en la barra son los más comunes.

Utilizando las teclas de acceso rápido
También podemos escoger un formato utilizando las teclas de acceso rápido que exponemos en la siguiente tabla:
Teclas de Acceso Rápido Efecto
CTRL+MAYÚS+$ Aplicar el formato Moneda con dos decimales (los números negativos aparecen en rojo).
CTRL+MAYÚS+% Aplicar el formato Porcentaje sin decimales.
CTRL+MAYÚS+^ Aplicar el formato numérico Exponencial con dos decimales.
CTRL+MAYÚS+# Aplicar el formato Fecha con el día, mes y año.
CTRL+MAYÚS+@ Aplicar el formato Hora con la hora y minutos e indicar a.m. o p.m.
CTRL+MAYÚS+! Aplicar el formato Número con dos decimales, separador de miles y signo menos (-) para los valores negativos.
Esta forma me permite aplicar también en un sólo paso un formato, incluye formatos que no tenemos en la barra Formato, pero es tal vez más complicada de utilizar ya que requiere que memoricemos las combinaciones de teclas.

Utilizando el cuadro de diálogo Formato de celdas
Esta última forma es menos rápida ya que requiere de varios pasos (abrir el cuadro de diálogo, elegir la pestaña adecuada y luego el formato deseado), pero permite utilizar muchos más formatos y sobre todo nos permite ver todo el amplio abanico de formatos que nos ofrece Excel, además de permitir que definamos nuestro propio formato personalizado.
Para abrir el cuadro de diálogo Formato de celdas podemos elegir entre:
- Seleccionar del menú Formato la opción Celdas...
- Hacer clic con el botón derecho sobre la celda y escogiendo del menú contextual la opción Formato de celdas...
- Presionar la combinación de teclas Ctrl + 1.
Al realizar cualquiera de estas operaciones nos aparece el cuadro de diálogo como el que vemos en la imagen donde podemos escoger entre los diversos formatos numéricos.





Definir formatos personalizados

Excel provee una gran cantidad de formatos numéricos, pero es posible que en algún momento necesitemos algún formato que no esté disponible en los estándares de Excel.
Para subsanar esta carencia Excel dispone de una herramienta para crear formatos personalizados.
Por defecto todas las celdas de Excel tienen el formato de número general. Su funcionamiento se basa en que lo que escribimos es lo que tenemos, es decir se adapta a lo que hayamos escrito, ya sea texto, números con decimales, porcentajes, etc.
Para crear un nuevo formato debemos acceder al cuadro de diálogo Formato de celdas, seleccionar la Categoría: Personalizada y seguidamente en el cuadro de texto Tipo: debemos escribir el código del formato que deseamos crear.

Para crear un formato de número personalizado, primero debemos conocer las reglas de los códigos para crear un formato.
El formato se compone de cuatro secciones separadas por ; (punto y coma).
____________ ;____________ ; ____________ ; ____________
La primera sección define el formato que tendra el número en la celda si es positivo; la segunda, si el número es negativo, la tercera, si el número vale cero; la cuarta si la celda contiene texto.
0 : Reserva un dígito para un número, si no se completa el tamaño definido por el formato se completa con ceros.
# : Reserva un dígito para un número, pero si no se completa el tamaño definido por el formato se deja en blanco.
? : Reserva un dígito. Añade espacios en ceros insignificantes.
. : Punto decimal.
% : Símbolo porcentual.
, : Separador de millares.
E- E+ e- e+ : Notación científica.
$ - + / ( ) : : Muestra estos caracteres. No necesitan comillas.
\ : Muestra el siguiente caracter en el formato.
* : Repite el siguiente caracter hasta completar el ancho de la celda.
_ : Deja un espacio
"texto" : Muestra el texto escrito entre la comillas dobles.
@ : Espacio reservado para texto.
[color] : Muestra el caracter en el color especificado. Puede ser Negro, Azul, Cian, Verde, Magenta, Verde, Rojo y Amarillo.
[color n] : Muestra el correspondiente color de la paleta de colores, n puede ser un número entre 0 y 56.
[valor condicion] : Permite escoger tu pròpio criterio para cada sección de un formato numérico.
m : Muestra el mes como número sin ceros (1-12).
mm : Muestra el mes como número con ceros (01-12).
mmm : Muestra el nombre del mes como abreviación (Ene, Feb)
mmmm : Muestra el nombre del mes por completo (Enero, Febrero)
mmmmm : Muestra la inicial del mes (E, F)
d : Muestra el día como número sin ceros (1-31).
dd : Muestra el día como número con ceros (01-31).
ddd : Muestra el nombre del día como abreviación (Lun, Mar)
dddd : Muestra el nombre del día por completo (Lunes, Martes)
yy o yyyy : Muestra el año en dos dígitos (00-99) o cuatro (1900-9999)
h o hh : Muestra la hora como números sin ceros (0-23) o como números con ceros (00-23).
m o mm : Muestra los minútos como números sin ceros (0-59) o como números con ceros (00-59).
s o ss : Muestra los segundos como números sin ceros (0-59) o como números con ceros (00-59).
AM/PM : Muestra la hora en formato de 12 horas, si no se indica esta opción se muestra la hora en formato 24 horas.

Por ejemplo, puedes utilizar el siguiente formato personalizado:
€#,##[Verde];(0,00€)[Rojo];"Cero";""
Este formato contiene cuatro secciones separadas por el signo de punto y coma y utiliza un formato diferente para cada sección.
Con este formato estamos indicando que los números positivos (1ªsección) se escriben en verde, llevan el signo del euro delante, no se muestran los ceros no significativos y solo se muestran 2 decimales (#.##); los números negativos se escriben en rojo, entre paréntesis con el € detrás y con dos decimales siempre; los valores cero se sustituyen por la palabra cero y los textos por la cadena nula, es decir que si en la celda hay texto, ese texto desaparecerá.
Para practicar estas operaciones te aconsejamos realizar

El formato condicional

El formato condicional sirve para que dependiendo del valor de la celda, Excel aplique un formato especial o no sobre esa celda.
El formato condicional suele utilizarse para resaltar errores, para valores que cumplan una determinada condición, para resaltar las celdas según el valor contenido en ella, etc...
Cómo aplicar un formato condicional a una celda:
- Seleccionamos la celda a la que vamos a aplicar un formato condicional.
- Accedemos al menú Formato y escogemos la opción Formato condicional...
Nos aparece un cuadro de diálogo Formato condicional como el que vemos en la imagen .


En este cuadro indicaremos una condición y el formato que deberán tener los valores que cumplan esa condición.
Podemos escoger entre una condición basada en el Valor de la celda o basada en una Fórmula.
Si nos basamos en Valor de la celda podemos escoger entre varias opciones como pueden ser un valor entre un rango mínimo y máximo, un valor mayor que, un valor menor que y condiciones de ese estilo.
Los valores de las condiciones pueden ser valores fijos o celdas quie contengan el valor a comparar.
Si pulsamos sobre el botón Formato... entramos en un cuadro de diálogo donde podemos escoger el formato con el que se mostrará la celda cuando la condición se cumpla. El formato puede modificar, el color de la fuente de la letra, el estilo, el borde de la celda, el color de fondo de la celda, etc.
Si pulsamos sobre Agregar >> podemos aplicar más de una condición. Como máximo podemos aplicar tres condiciones distintas.
Si pulsamos sobre Eliminar... nos aparece un cuadro de diálogo donde podemos escoger la condición a eliminar.
Al pulsar sobre Aceptar aplicaremos el formato condicional sobre la celda.
Si pulsamos sobre Cancelar, no se aplicarán los cambios efectuados en el formato condicional.
Si el valor incluido en la celda no cumple ninguna de las condiciones, no se le aplicará ningún formato especial.

Cómo localizar celdas con formatos condicionales?
Para averiguar cuales son las celdas que tienen un formato condicional podemos utilizar el cuadro de diálogo Ir a que abrimos con el menú Edición - Ir a... o pulsando F5.
En el cuadro de diálogo Ir a... debemos hacer clic sobre el botón Especial... y en esta pantalla seleccionar Celdas con formatos condicionales.
Al pulsar sobre Aceptar se nos remarcarán aquellas celdas que contengan formato condicional.

Para practicar estas operaciones te aconsejamos realizar Ejercicio Crear un formato condicional.

La validación de datos
La validación de datos es muy similar al formato condicional, salvo que esta característica tiene una función muy concreta y es validar el contenido de una celda; pudiendo incluso mostrar un mensaje de error o aviso si llegara el caso.
Para aplicar una validación a una celda.
- Seleccionamos la celda que queremos validar.
- Accedemos al menú Datos - Validación...
Nos aparece un cuadro de diálogo Validación de datos como el que vemos en la imagen donde podemos elegir entre varios tipos de validaciones.



En la sección Criterio de validación indicamos la condición para que el datos sea correcto.
Dentro de Permitir podemos encontrar Cualquier valor, Número entero, Decimal, Lista, Fecha, Hora, Longitud de texto y personalizada. Por ejemplo si elegimos Número entero, Excel sólo permitirá números enteros en esa celda, si el usuario intenta escribir un número decimal, se producirá un error.
Podemos restringir más los valores permitidos en la celda con la opción Datos:, donde, por ejemplo, podemos indicar que los valores estén entre 2 y 8.
Si en la opción Permitir: elegimos Lista, podremos escribir una lista de valores para que el usuario pueda escoger un valor de los disponibles en la lista. En el recuadro que aparecerá, Origen: podremos escribir los distintos valores separados por ; (punto y coma) para que aparezcan en forma de lista.
En la pestaña Mensaje entrante podemos introducir un mensaje que se muestre al acceder a la celda. Este mensaje sirve para informar de qué tipos de datos son considerados válidos para esa celda.
En la pestaña Mensaje de error podemos escribir el mensaje de error que queremos se le muestre al usuario cuando introduzca en la celda un valor incorrecto.
Además de definir condiciones de validación para los datos, podemos controlar que una página esté libre de errores, si quieres saber más sobre cómo localizar y depurar errores haz clic aquí


Para practicar estas operaciones te aconsejamos realizar el Ejercicio Crear una validación de datos.

Personalizar las barras de herramientas y los menús.

Excel al igual que otras aplicaciones dentro del paquete Office nos permite crear nuestras propias barras de herramientas y nuestros propios menús.
Cuando estamos obligados a trabajar con una aplicación tan genérica (dentro del mundo de las hojas de cálculo) como Excel y la utilizamos para unas necesidades muy específicas suele ocurrir que aunque las herramientas están disponibles no lo están mediante acceso rápido por menú o por barra de herramientas.
Muchas veces, de las barras de herramientas que nos ofrece Excel, sólo utilizamos unos pocos comandos, el resto no los utilizamos nunca, esto hace que tengamos en la pantalla iconos que no sirven más que para ocupar espacio innecesario.
Además, si estamos diseñando una hoja de cálculo compleja, incluso con macros, puede ser útil poder definir nuestra propia barra de menús.
Por ello surge la necesidad de poder cambiar la barra de menús, de poder crear nuestras propias barras de menús y de herramientas.
Existen diversas formas de personalizar un libro de trabajo:
- Cambiando la posición de las barras.
- Ocultando/mostrando diferentes barras.
- Añadiendo/quitando botones de las barras.
- Modificando la funcionalidad del botón.
- Cambiando la imagen asociada a un botón.
- Creando menús y barras propios.
Para cambiar la posición de la barra de menús o de una barra de herramientas, sólo tenemos que hacer clic sin soltar el botón del ratón sobre cualquier zona libre de la barra, el puntero del ratón tomará el aspecto , a continuación arrastramos la barra hasta la posición deseada, si la arrastramos sobre los laterales de la pantalla, se colocará en vertical.

Ocultar y mostrar una barra de herramientas.
Para mostrar y ocultar un barra de herremientas seleccionamos del menú Ver, Barras de herramientas, se despliega la lista de todas las barras de herramientas de que disponemos tanto estándares como personalizadas.
A la izquierda del nombre de cada barra tenemos una marca que indica que la barra se está mostrando en este momento, para ocultarla sólo tenemos que hacer clic sobre su nombre.
Si la barra no está visible y la queremos mostrar, también hacemos clic sobre su nombre y aparecerá la marca indicando que ahora la barra está visible.
En la imagen de la derecha puedes ver la lista de las barras de herramientas.
Crear una barra de herramientas personalizada.
Para personalizar las barras de herramientas de Excel debemos acceder mediante el menú Ver - Barras de herramientas - Personalizar..., nos aparece el cuadro de diálogo Personalizar que vemos en la imagen de la derecha.
Las barras marcadas son las que se ven.

Para crear una nueva barra de herramientas debemos presionar sobre el botón Nueva... del cuadro de diálogo Personalizar.
A continuación nos abre un cuadro de diálogo para introducir el nombre de la nueva barra de herramientas.
Escribimos el nombre de la nueva barra y pulsamos Aceptar para cerrar el cuadro de diálogo.


Seguidamente vemos que en el cuadro de diálogo Personalizar nos ha añadido nuestra barra al final de la lista de barras de herramientas y además podemos ver la nueva barra flotante completamente vacía de botones sobre nuestra hoja de cálculo, al lado del cuadro de diálogo Personalizar.

Añadir/quitar botones a una barra de herramientas.
Ya sea para la nueva barra de herramientas o para modificar una barra existente podemos añadir o quitar botones.
Para esto debemos tener a la vista la barra a modificar y cambiar la pestaña del cuadro de diálogo Personalizar en la pestaña Comandos.
En esta pestaña podemos ver todos los botones (comandos) disponibles clasificados por categorías.
Hacemos clic en una categoría y aparecen a la derecha todos los comandos incluidos en esa categoría.
Para agregar botones (comandos) a la barra de herramientas, seleccionamos el botón de la lista Comandos: y con la tecla izquierda del ratón presionada, arrastramos el botón a la barra de herramientas donde queramos que se añada el botón.

Para eliminar un botón de la barra de herramientas debemos hacer clic con el botón derecho del ratón sobre el botón a eliminar y seleccionar Eliminar.
Reorganizar los botones en una barra de herramientas.
También podemos cambiar de posición los botones, seleccionando el botón y arrastrándolo a lo largo de la barra.
Podemos insertar un separador de botones, haciendo clic con el botón derecho del ratón a partir de donde queramos se añada un separador y seleccionar la opción Iniciar un grupo del menú contextual.
Si queremos quitar el separador debemos volver a seleccionar Iniciar un grupo para deseleccionar la opción.
Otra forma de reorganizar los botones es utilizando el cuadro de diálogo Personalizar.
En la pestaña Comandos disponemos del botón Reorganizar comandos... que sirve para organizar los menús o las barras de herramientas de Excel.


Primero debemos seleccionar el tipo de barra (Barra de menús o Barra de herramientas), después seleccionar la barra de menús o barra de herramientas que deseamos reorganizar y luego reorganizar las opciones utilizando los botones que aparecen en la sección Controles:
Agregar... : Sirve para agregar algún comando que no está.
Eliminar : Elimina el comando que esté seleccionado. Es de acceso inmediato, no pide confirmación.
Subir : Sirve para subir un nivel el comando en la barra de menús.
Bajar : Sirve para bajar un nivel el comando en la barra de menús.
Modificar selección : Sirve para hacer algunas modificaciones sobre el comando seleccionado, como por ejemplo cambiar la imagen relacionada con el comando.
Restablecer... : Sirve para dejar el menú conforme estaba antes de hacer las modificaciones.
Más opciones.
La última pestaña del cuadro de diálogo Personalizar es la de Opciones donde podemos escoger entre las distintas opciones disponibles como por ejemplo si queremos que los menús se muestren siempre con todas las opciones marcaremos Mostrar siempre los menús completos.




Para practicar estas operaciones te aconsejamos realizar Ejercicio Crear barra de herramientas personalizada.

Enlazando y consolidando hojas de trabajo

Enlazar hojas de trabajo.
El concepto de enlazar en Excel es el hecho de utilizar fórmulas de varias hojas para combinas datos. Al enlazar hojas de trabajo estamos creando una dependencia de una con respecto a la otra, apareciendo así dos conceptos:
- el libro de trabajo dependiente: es el que contiene las fórmulas.
- el libro de trabajo fuente: es el que contiene los datos.
La cuestión que debemos plantearnos antes de enlazar hojas de trabajo, es si realmente nos hace falta complicarnos tanto o sencillamente podemos hacerlo todo en una misma hoja.
No existe una respuesta genérica, dependerá de la envergadura de los datos y de las fórmulas; si las hojas las van a utilizar varias personas, etc...
Para crear un libro de trabajo dependiente debemos crear fórmulas de referencias externas, es decir fórmulas que hacen referencia a datos que se encuentran en una hoja externa a la que está la fórmula.
Cómo crear fórmulas de referencias externas.
Para crear fórmulas de referencia externa debemos seguir los siguientes pasos:
- Abrir el libro de trabajo fuente (el libro que contiene los datos).
- Abrir el libro de trabajo dependiente y seleccionar la celda donde queremos incluir la fórmula.
- Introducir la fórmula de la forma que hemos hecho siempre, cuando se llegue al punto de escoger las celdas de datos, activar el libro de trabajo fuente y seleccionar las celdas necesarias.
- Terminar la fórmula y pulsar Intro.
Las referencias al libro externo las gestiona automáticamente Excel, incluso si cambiamos el nombre del archivo donde están los datos desde Archivo - Guardar como... las referencias también se cambiarían.
Si en el libro de trabajo dependiente queremos ver todos los vínculos a las páginas fuente podemos hacerlo accediendo al menú Edición y pulsando sobre vínculos... Nos aparece el cuadro de diálogo Modificar vínculos donde podemos hacer modificaciones sobre los vínculos.


Actualizar valores : Actualiza la lista con los vínculos.
Cambiar origen : Podemos modificar el origen del vínculo seleccionado.
Abrir origen : Abre el libro del vínculo seleccionado.
Romper vínculo : Quita los vínculos de las fórmulas externas.
Comprobar estado : Comprueba el estado del origen.
Pregunta inical : Podemos incluir una advertencia al abrir el libro dependiente, indicando que tenemos enlaces externos.
Consolidar hojas de trabajo.
El concepto de consolidar hojas de trabajo viene muy ligado al concepto de enlace que acabamos de ver. Cuando hablamos de consolidar hojas de trabajo estamos entablando una relación entre varias hojas de trabajo, por tanto es muy posible que existan enlaces entre esas hojas de trabajo.
Un ejemplo donde se puede utilizar la consolidación de hojas de trabajo puede ser una compañía que dispone de varios departamentos, cada uno de ellos con un presupuesto, para conocer el presupuesto total de la empresa, crearíamos una hoja dependiente y los libros fuentes serían los libros de los departamentos con sus prepuestos.

Si pulsamos sobre Datos - Consolidar... nos aparece el cuadro de diálogo Consolidar como vemos en la imagen donde podemos escoger:
- la Función: a utilizar,
- en Referencia: vamos seleccionando las celdas de uno de los libros fuentes,
- pulsamos sobre Agregar para añadir las celdas seleccionadas a la lista de todas las referencias,
- repetimos los dos últimos pasos para seleccionar las celdas de los diferentes libros fuentes,
- finalmente pulsamos en Aceptar para finalizar la fórmula.

Si marcamos la casilla Crear vínculos con los datos de origen, los datos de los libros fuente se incluirán en el libro consolidado a modo de esquema, y si cambiamos un valor en el origen, ese cambio quedará automáticamente reflejado en el libro consolidado.

Para practicar estas operaciones te aconsejamos realizar Ejercicio Consolidar hojas de trabajo.

Excel e Internet

Excel nos ofrece la posibilidad de compartir nuestras hojas de trabajo en la Web y también recoger información de Internet.
Convertir una hoja en página web.
Para poder exponer ("colgar") en Internet nuestro trabajo realizado en Excel, debemos convertirlo a un formato reconocible por los navegadores (html o xml). Para hacer esto debemos seguir los siguientes pasos:
- Pulsar sobre el menú Archivo.
- Seleccionar la opción Guardar como página Web...

- En el cuadro de diálogo Guardar como tendremos seleccionado el tipo Página Web en Guardar como tipo: .
En esta pantalla disponemos de dos posibles modos de guardar la página Web:
Podemos guardar la página de forma estática, es decir que los usuarios de Internet tengan acceso al libro de trabajo pero que no puedan interactuar con la página. Esta opción es la que está por defecto.
O podemos guardar la página web de forma dinámica o interactiva, seleccionando la opción para que la página Web tenga interactividad con el internauta, aunque para que la página funcione correctamente el usuario deberá tener instalado en su equipo los componentes Web de Office y utilizar una versión de Internet Explorer 4 o superior.
En este caso el usuario podrá cambiar el valor de una celda y automáticamente se recalcularán las fórmulas, pero esos cambios sólo se aplicarán a la página visualizada en esa sesión, si cerramos el navegador y volvemos a conectar con la página, aparecerán los datos originales, no los cambiados en la sesión anterior.
Una vez hayamos elegido la opción que más nos interesa según nuestras necesidades podemos pulsar sobre Guardar y tendremos nuestra hoja Excel guardada como una página web con la extensión .htm en lugar del clásico .xls.
Una vez tenemos nuestra hoja convertida a formato html tendremos que subirla a un servidor de Internet para que pueda ser vista por los internautas.
Crear hiperenlaces
Podemos crear hiperenlaces en nuestra hoja de cálculo Excel ya sean a nuestro propio libro u hoja actual o bien a otros libros e inclusive a páginas de Internet.
Un hiperenlace o hipervínculo no es más que un acceso directo a un punto en concreto del mismo documento o a otro documento y también, a una página en Internet.
Si en una celda de Excel escribimos una dirección web, por ejemplo www.aulaclic.es, Excel por defecto nos crea el hiperenlace y lo deja resaltado como enlace.
Un hiperenlace puede estar hecho sobre texto, sobre un gráfico o una imagen.
Para hacer esto debemos seguir los siguientes pasos:
- Seleccionar el objeto (texto, gráfico, imagen) sobre el cual vamos a crear el enlace.
- Pulsar sobre el menú Insertar - Hipervínculo... o Ctrl+Alt+K.
Nos aparece el cuadro de diálogo Insertar hipervínculo que vemos en la imagen de abajo.


En la columna Vincular a: podemos seleccionar el tipo de enlace que vamos a crear, si es un enlace a página Web existente, si es a un punto dentro del mismo documento, si es a un documento nuevo o a una dirección de correo electrónico.
En el recuadro Dirección: escribimos la dirección del hiperenlace o si está en nuestro disco duro podemos buscarla como cuando buscamos un fichero para abrirlo ayudándonos del recuadro Buscar en:.
Finalmente pulsamos en Aceptar.
Se mostrará la barra de herramientas Web parecida a la que puedes encontrar en tu navegador.
El texto con el enlace quedará subrayado y si haces clic sobre él, se abrirá el enlace. Para volver a la hoja inicial, sólo tienes que utilizar el botón Atrás de la barra Web.

Para practicar estas operaciones te aconsejamos realizar el Ejercicio Crear un hipervínculo.

Crear consultas web
Otra herramienta que puede ser de utilidad es la de crear consultas Web, es decir tener una página Web en nuestra hoja de cálculo actualizada desde Internet. Para crear una consulta Web debemos seguir los siguientes pasos:
- Accedemos al menú Datos y seleccionamos la opción Importar datos externos y dentro del submenú elegimos la opción Nueva consulta web...
- Debemos escribir la dirección Web la cual queremos consultar.


- Debemos hacer clic sobre las flechas amarillas que aparecen en la esquina superior izquierda para seleccionar lo que queremos importar.
- Una vez seleccionado lo que vamos a importar pulsamos sobre Importar.
- Por último nos aparece un cuadro de diálogo para indicar dónde vamos a insertar la consulta Web.
Después de pulsar Aceptar, nos aparecerá lo seleccionado en nuestra hoja de cálculo.