Curso Avanzado de Excel 2016

ASOCIACIÓN DE ANTIGUOS ALUMNOS ETSII | UPM

José Andrés Otero

Tabla de contenido
TABLA DE CONTENIDO 2

MÓDULO 1: EMPEZAR A TRABAJAR CON EXCEL 2016 5

1.1 LA INTERFAZ DE EXCEL 2016. P ERSONALIZAR EL ENTORNO DE TRABAJO. 5

1.2 CREAR Y GUARDAR LIBROS DE DATOS. USO DE PLANTILLAS. TIPOS DE ARCHIVOS. 9
1.3 MÉTODOS ABREVIADOS DE TECLADO DE E XCEL 11
1.4 O BTENER AYUDA EN EXCEL 2016 12

MÓDULO 2: TRABAJAR CON DATOS 14

2.1 TIPOS DE DATOS. INSERTAR Y BORRAR DATOS EN UNA HOJA DE CÁLCULO. 14

2.2 BARRA DE FÓRMULAS. INTRODUCCIÓN DE FÓRMULAS SENCILLAS. 15
2.3 GENERAR SERIES DE DATOS. OPCIONES DE AUTO-RELLENO Y DE RELLENO RÁPIDO DE CELDAS. 15
2.4 DIVIDIR TEXTO EN COLUMNAS. 17
2.5 IMPORTAR DATOS DESDE ACCESS /CONEXIONES DEL LIBRO 18
2.6 SELECCIONAR CELDAS. COPIAR, CORTAR Y PEGAR. PORTAPAPELES DE OFFICE 19
2.7 BUSCAR Y REEMPLAZAR DATOS. 20
2.8 O RDENAR DATOS. APLICAR FILTROS. FILTROS AVANZADOS 21
2.9 VALIDACIÓN DE DATOS. 23
2.10 CONSOLIDAR DATOS EN VARIAS HOJAS DE CÁLCULO 24
2.11 AGRUPAR DATOS Y SUBTOTALES. 25

MÓDULO 3: TRABAJAR CON TABLAS 27

3.1 CREAR UNA TABLA 27

3.2 CAMBIAR EL FORMATO DE UNA TABLA. ELIMINAR DUPLICADOS. 28
3.3 CREAR UN ESTILO RÁPIDO DE TABLA 29
3.4 FILTRAR DATOS EN UNA TABLA. QUITAR DUPLICADOS 30

EJERCICIO DE REPASO 1 31

MÓDULO 4: TRABAJAR CON HOJAS Y LIBROS. 32

4.1 AÑADIR Y ELIMINAR HOJAS EN UN LIBRO DE TRABAJO. M ODIFICAR LAS ETIQUETAS 32

4.2 APLICAR UN FONDO A LA HOJA. 33
4.3 INSERTAR / ELIMINAR FILAS Y COLUMNAS. INSERTAR CELDAS. 34
4.4 ANCHO DE COLUMNA Y ALTO DE FILA. AUTOAJUSTE. 36

MÓDULO 5: DAR FORMATO Y ESTILO A LAS CELDAS. 39

5.1 FORMATOS DE CELDA. FORMATOS DE TEXTO. 39

5.2 COLOR Y EFECTO DE RELLENO. ALINEACIÓN Y ORIENTACIÓN. 40
5.3 APLICAR BORDES A LAS CELDAS. 41
5.4 CREAR Y APLICAR ESTILOS DE CELDA. PERSONALIZACIÓN DE LOS FORMATOS. 42
5.5 OCULTAR Y BLOQUEAR CELDAS 43

EJERCICIO DE REPASO 2 45

MÓDULO 6: NAVEGACIÓN POR LA HOJA DE CÁLCULO. 46

6.1 MOVERSE POR LAS HOJAS 46

6.2 INMOVILIZAR PANELES 46
6.3 LAS DIFERENTES VISTAS DEL LIBRO. CONFIGURAR MÁRGENES, ENCABEZADOS Y PIES DE PÁGINA PARA
IMPRESIÓN. 47
6.4 USO DEL ZOOM. 48
6.5 TRABAJAR CON VARIAS VENTANAS. DIVIDIR Y O RGANIZAR LAS VENTANAS 49
6.6 CREAR VISTAS PERSONALIZADAS. 51

MÓDULO 7: REALIZAR CÁLCULOS CON EXCEL: FÓRMULAS Y FUNCIONES 52

7.1 CREAR Y EDITAR UNA FÓRMULA. COPIAR Y PEGAR FÓRMULAS. DEFINICIÓN Y USO DE NOMBRES DE
RANGOS. 52
7.2 REFERENCIAS DE CELDA EN LAS FÓRMULAS. REFERENCIAS RELATIVAS, ABSOLUTAS Y MIXTAS. REFERENCIAS
CIRCULARES Y CONTROL DE CÁLCULO. 54
7.2.2 R EFERENCIAS CIRCULARES Y CONTROL DE CÁLCULO: 54
7.3 AUDITORIA DE FÓRMULAS Y COMPROBACIÓN DE ERRORES 55
7.4 INSERTAR FUNCIONES EN EXCEL. - FUNCIONES DISPONIBLES. COMBINACIÓN DE FUNCIONES 57
7.5 RANGOS TRIDIMENSIONALES (3D) 58

EJERCICIO DE REPASO 3 59

MÓDULO 8: PRESENTAR DATOS DE FORMA VISUAL. 60

8.1 CREAR GRÁFICOS BASADOS EN DATOS DE LA HOJA DE CÁLCULO. TIPOS DE GRÁFICOS. 60

8.2 EDICIÓN Y PERSONALIZACIÓN DE UN GRÁFICO EXISTENTE. AÑADIR Y ELIMINAR SERIES DE DATOS. 63
8.3 CREAR UNA PLANTILLA DE GRÁFICOS. 66
8.4 CREAR Y EDITAR MINI-GRÁFICOS EN UNA CELDA. ANÁLISIS INSTANTÁNEO DE DATOS. 66
8.5 INSERTAR Y EDITAR GRÁFICOS SMARTA RT. 70

EJERCICIO DE REPASO 4 72

MÓDULO 9: TRABAJO CON TABLAS DINÁMICAS 73

9.1 CREACIÓN Y MODIFICACIÓN DE TABLAS DINÁMICAS. CÁLCULOS EN TABLAS DINÁMICAS. 73

9.2 ASIGNACIÓN DE FILTROS A TABLAS DINÁMICAS. SEGMENTACIÓN DE DATOS Y ANÁLISIS DE TIEMPO. 74
9.3 CAMPOS CALCULADOS EN TABLAS DINÁMICAS 75
9.4 CREAR INFORMES DE TABLAS DINÁMICAS. GRÁFICOS DINÁMICOS. 76

EJERCICIO DE REPASO 5 77

MÓDULO 10: IMPRIMIR, REVISAR Y COMPARTIR DOCUMENTOS. 78

10.1 A DMINISTRAR COMENTARIOS. CONTROL DE CAMBIOS. 78

10.2 R EVISAR LA ORTOGRAFÍA. OPCIONES DE AUTOCORRECCIÓN. 79
10.3 PROTECCIÓN DE CELDAS, HOJAS Y DOCUMENTOS. 81
10.4 R ECUPERAR DOCUMENTOS 84
10.5 CONFIGURAR PÁGINA PARA IMPRESIÓN 85
10.6 ENVIAR POR CORREO ELECTRÓNICO. OPCIONES PARA COMPARTIR FICHEROS EN LÍNEA. 88

MÓDULO 11: HERRAMIENTAS DE ANÁLISIS DE DATOS EN EXCEL Y CONTROL DE

FORMULARIOS. 91

11.1 HERRAMIENTAS DE ANÁLISIS DE DATOS 91

11.2 USO DE LA HERRAMIENTA SOLVER. 94
11.3 FORMULARIO DE DATOS. CONTROLES DE FORMULARIO EN EXCEL 95

MÓDULO 12: MACROS E INTRODUCCIÓN A VBA. 98

12.1 INTRODUCCIÓN A LAS MACROS 98

12.2 GRABACIÓN Y REPRODUCCIÓN DE UNA MACRO 98
12.3 INTRODUCCIÓN A VISUAL BASIC PARA APLICACIONES (VBA) 99
Módulo 1: Empezar a trabajar con Excel 2016
Excel es una de las hojas de cálculo más utilizada, conocida y extendida en todo el mundo, tanto
a nivel personal como para negocios. Permite organizar grandes cantidades de datos, realizar
operaciones con ellos y presentarlos en forma de gráficos o tablas de manera que se facilite su
análisis.

1.1 La interfaz de Excel 2016. Personalizar el entorno de trabajo.

Lo primero que vemos al entrar en Excel 2016 es la ventana de inicio rápido (1).

En esta primera actividad se presenta la interface de Excel 2016 y se aprenderá a personalizar

los principales elementos del entorno de trabajo (2).

Desde la ventana de inicio rápido abrimos un Libro en Blanco nuevo. A continuación, vamos a ir
descubriendo cada uno de los elementos del entorno de trabajo.

5
1.1.1 Barra de Herramientas de Acceso Rápido. Personalización
La barra de Herramientas de Acceso rápido está a la izquierda de la barra de título y en ella se
encuentran los iconos que representan a las acciones más comunes. Podemos personalizarla,
para que sea lo más útil posible.

1. Añade otros comandos de interés a la barra mediante el cuadro de Opciones de Excel,

seleccionando comandos disponibles de alguna de las fichas de la cinta de opciones. (1)
2. Empleamos el menú contextual de la propia barra de herramientas de acceso rápido para
añadir o eliminar opciones. También se puede personalizar presionando en el icono de
punta de flecha de la propia barra.
3. Restablecer la configuración de la barra a su estado original mediante el botón de
Restablecer de las opciones de Excel.

1.1.2 Cinta de opciones

La cinta de opciones se organiza en distintas fichas de opciones, que a su vez agrupan
herramientas en distintos grupos. Esta estructura está pensada para facilitar la localización de
los comandos necesarios para realizar una determinada acción. Vemos las herramientas
principales que contiene cada una de las fichas de la cinta.

Existen algunas fichas de herramientas que sólo aparece cuando un determinado tipo de
elemento está seleccionado, a estas se les denomina fichas contextuales (1). En muchos de los
grupos existe un iniciador del cuadro de diálogo, que permite ejecutar de una manera
avanzadas herramientas relacionadas con el mismo.

6
La cinta de opciones puede minimizarse o maximizarse desde su menú contextual. Puede
además personalizarse con Personalizar la cinta de opciones (2), accesible desde su menú
contextual o desde el cuadro de opciones de Excel. Accediendo de cualquiera de las maneras,
se realizarán los siguientes ejercicios:

1. Cambiar el orden de las fichas en la cinta de opciones. Cambiar el estatus de visible / no

visible de alguna de ellas.
2. Añadir nueva pestaña y un nuevo grupo a la cinta. Cambiar el nombre del grupo.
3. Modificar los grupos de alguna de las fichas de la cinta de opciones.
4. Finalmente, se reestablecerá la cinta de opciones a su estado original.
5. Explorar las opciones de presentación de la cinta de opciones (Mostrarla, ocultarla, ubicarla
encima o debajo de las herramientas de acceso rápido.) (3). A este menú se accede desde la
esquina superior derecha de la barra de título.

1.1.3 La barra de estado

La barra de estado está en la parte inferior de la interfaz, justo por debajo del área de trabajo
(1).

En este ejercicio se revisarán las funciones principales que se muestran en esta barra: los
controladores del Zoom, el indicador del estado de las celdas (Listo, modificar, introducir), el
grabador de Macros y alguna de las opciones matemáticas más usadas (2), como Suma,
Promedio, recuento, min o max. También vemos cómo se puede personalizar mediante su menú
contextual (3).

7
1.1.4 Personalizar el entorno de Excel
Excel permite cambiar el fondo de office y el color del tema, entre otras modificaciones del
entorno del programa. Los comandos que permiten estas personalizaciones se pueden
encontrar en el cuadro de Opciones de Excel (1), al que se accede desde la vista Backstage
(Archivo).

Además, se puede decidir sobre la presencia de muchos de los elementos de la interfaz (como
la minibarra de herramientas, las opciones de análisis rápido o la vista previa) (2). Además,
podemos seleccionar el estilo de información que se muestra en pantalla al ponerse sobre
alguna de las herramientas (3,4).

8
En las opciones de Excel es posible también el exportar las personalizaciones de la interface en
un fichero.

1. Exportamos el fichero con todas las personalizaciones de la interface de usuario. Esta

posibilidad está en las Opciones de Excel, Personalizar Cinta de Opciones, Importar o
Exportar.
2. Eliminar alguna herramienta de la cinta de opciones, como por ejemplo, el Portapapeles.
Comprobamos que efectivamente se ha eliminado la herramienta.
3. Restauramos la interface de usuario con el fichero almacenado previamente y
comprobamos que efectivamente ha sido recuperada la herramienta.

1.2 Crear y guardar libros de datos. Uso de plantillas. Tipos de

archivos.
Un libro en Excel es un documento creado con Excel que incluye una o más hojas de cálculo que
se pueden utilizar para introducir y organizar distintos tipos de información.

Puede crearse en blanco o partiendo de una de las plantillas que el programa ofrece al usuario
(1). Excel permite la búsqueda de plantillas en línea mediante el uso de palabras clave. Las
plantillas que ya han sido descargadas se almacenan en el equipo y se muestran en primer lugar
en la vista Nuevo.

A la hora de Guardar el libro es importante evaluar el formato que se utiliza, sobre todo en caso
de que el destinatario no disponga de Excel (2). Además, es posible almacenar un libro como
plantilla de Excel para usarlo como base para nuevos libros.

Las propiedades del documento para los libros facilitan su organización e identificación y
permite realizar búsquedas en función de esas propiedades. Parte de esta información puede
ser modificada por el usuario, mientras que otra no puede ser editada.

9
Para practicar el trabajo con Libros de Excel, realizaremos los ejercicios que se describen a
continuación.

1 Creamos un nuevo libro en blanco desde la vista Backstage. Cerramos el libro.

2 Buscamos plantillas en línea de horarios, presupuestos, calendarios, Gantt… otros ejemplos
de interés.
3 Vemos propiedades del documento en el backstage (3). Mostramos todas las propiedades
(4). Lanzamos el panel de propiedades del documento, desde la opción propiedades del
backstage (5). Desde este panel, lanzamos el cuadro de propiedades avanzadas del
documento. Añadimos desde aquí una propiedad nueva del documento, como por ejemplo,
el campo cliente.
4 Practicamos el uso de Guardar frente a Guardar Como (extensión xlsx), tanto la primera vez
como las veces sucesivas. Ver cómo cambia el nombre en la barra de título.
5 Almacenamos un archivo con formato Plantilla de Excel para poder utilizarlo como base
para nuevos libros.
6 Configurar las Opciones de Auto-recuperación del documento, dentro de las Opciones de
Excel. Ver cómo se puede cambiar la ubicación por defecto de los archivo de auto-
recuperación.
7 Abrir un libro de ejemplo ([Link]) y guardarlo como página web. Elegir la
opción publicar y verlo en el explorador. Guardar el fichero como página web, como PDF y
en formato Excel 97-2003.
8 Guardar como Libro de Excel 97 -2003 xls. Ver el comprobador de compatibilidad.
9 Exportamos el Archivo como documento PDF. De esta manera se puede enviar tablas de
datos a usuarios que no tengan Excel instalado. El formato XPS es la alternativa de Microsoft
al PDF.

10
1.3 Métodos abreviados de teclado de Excel
Si presionamos Alt. Se muestran los atajos de teclado correspondiente a las herramientas de la
cinta de opciones (1,2):

También siguen funcionando los atajos típicos de Office (Ctrl +). Podemos ver los recordatorios
al situarnos encima de la herramienta (3,4):

Se destacan alguno de los métodos de teclado abreviados que se emplean durante el curso:

Ctrl + C Copiar
Ctrl + X Cortar
Ctrl + V Pegar
Ctrl+Alt+V Pegado Especial
Ctrl + Z Deshacer
Ctrl + Y Rehacer
Ctrl + G / F12 Guardar documento
Ctrl. + A Abrir (Backstage)
Ctrl + N Negrita
Ctrl + K Cursiva
Ctrl + S Subrayado
Ctrl +Mayús+% Formato porcentaje
Alt+ Enter Comenzar nueva línea en celda
F2 Editar el contenido de una celda
Tab Avanza celda activa a la derecha
Mayús+Tab Avanza celda activa a la izquierda
Enter Avanza celda activa hacia abajo

11
 Mayús+Enter Avanza celda activa hacia arriba
Ctrl +flechas Desplaza celda activa hasta la última celda en dirección
de la flecha
Ctrl + . Desplaza celda activa por las 4 esquinas de selección
Ctrl + Clíc mouse Selección discontinua
Ctrl +barra espaciadora Seleccionar toda una columna
Mayús+barra espaciadora Seleccionar toda una fila
Ctrl+Mayús+barra espaciadora Seleccionar todo un rango delimitado
Mayús+Flechas Seleccionar en dirección a la flecha
Ctrl +Mayús+Flechas Seleccionar hasta la última celda en dirección a la flecha
Ctrl.+Mayusculas+L Habilitar la función de filtrado de datos para la selección
F1 Ayuda
F7 Revisión Ortográfica
F9 Calcular todas las fórmulas
F11 Insertar Gráfico con los datos seleccionados
Ctr.+I Ir a
Ctrl.+L y Ctrl+B Opciones de Buscar y Reemplazar
Mayúsculas + F2. Insertar Nuevo Comentario
Ctrl+ F2 Imprimir
Alt+F11 Editor VBA
Ctrl+Av Pág Moverse a la hoja siguiente
Ctrl+Re Pág Moverse a la hoja anterior
Mayús+F3 Insertar función en una celda
F4 Transformar la referencia en absoluta/relativa al
introducir una fórmula
Alt+= Insertar Autosuma
Ctrl+J Rellenar una fórmula hacia abajo
Ctrl+D Rellenar una fórmula hacia la derecha
Ctrl + F1 Minimizar cinta de opciones
Ctrl+0 Ocultar columnas
Ctrl+9 Ocultar filas

1.4 Obtener ayuda en Excel 2016

La ayuda en línea puede lanzar directamente desde Excel, presionando en la interrogación de
la esquina superior derecha de la barra de título.

12
Se puede acceder también por la web: [Link]

13
Módulo 2: Trabajar con datos
En este módulo vamos a trabajar con la introducción, formato y ordenación de datos en las
celdas de la hoja de cálculo, empleando las múltiples opciones que nos facilita la herramienta.

2.1 Tipos de datos. Insertar y borrar datos en una hoja de cálculo.

Al introducir un dato en una celda se puede observar que las cadenas de texto se alinean
directamente a la izquierda, mientras que los valores numéricos lo hacen a la derecha. En caso
de que sean valores numéricos, el grupo de herramientas Número (1) nos permite definir su
formato. Empleando la flecha emergente de este grupo se puede arrancar el menú emergente
de Número (2), dentro de las opciones de Formato de Celdas.

1. A lo largo de este apartado se va a crear una lista de precios de los productos que se ofrecen
en una papelería. Incluirá información del Referencia, Categoría, Producto, IVA aplicado,
Coste, stock, Fecha Fabricación, Precio sin IVA y Precio con IVA. Abrimos un fichero nuevo e
introducimos estos campos.
2. Introducir un valor para la referencia del producto. Por ejemplo 11010101. Si queremos que
el valor sea identificado como cadena, podemos poner un apóstrofe delante. También se
puede cambiar en el grupo de herramientas Número. De esta manera ya no puede ser
empleado para operaciones numéricas
3. Introducir un valor con decimales para el precio de los productos. Ver en la opción número
como variar el número de decimales. Ver como el valor se refleja en la barra de fórmulas.
4. Eliminamos datos con Suprimir.
5. Probar a introducir el precio de los productos en Euro (formato contabilidad y moneda).
Revisar en una celda libre formatos de celdas (cortas y largas). Añadir en una celda el IVA a
aplicar, en formato de tanto por ciento.
6. Editar celdas (presionando F2 o haciendo clic en la celda en cuestión). ¿Qué pasa si
insertamos un valor en la celda sin darle a F2? Simultáneamente vemos cómo moverse por
la Hoja con la tecla Enter o con las flechas.

14
2.2 Barra de fórmulas. Introducción de fórmulas sencillas.
En este apartado vamos a introducir fórmulas sencillas, mediante la barra de fórmulas (1). Para
ello es necesario poner “=” antes de la expresión.

1. Vamos a abrir el fichero [Link], que contiene un catálogo de productos

2. Definir una expresión de suma con constantes, para calcular el PVP sin IVA. El margen será
del 50%
3. Definir una expresión con variables, para lo mismo. De esta manera calcularemos también
el PVP con IVA.
4. Probamos a aplicar formatos seleccionando varias celdas con ALT y con Ctrl.
5. Activar Mostrar fórmulas en el grupo Auditoría de Fórmulas. Esto permite ver las celdas a
las que hace referencia la expresión.

2.3 Generar series de datos. Opciones de auto-relleno y de

relleno rápido de celdas.
Excel 2016 permite rellenar automáticamente celdas con series de datos: días, meses, números
progresivos… Se trata de las series predefinidas en Excel. Con estas, tan solo introduciendo el
primer elemento de la serie en una celda u arrastrando a las celdas donde se desea aplicar la
selección, se puede completar todo el rango.

1. Abrimos un documento de Excel nuevo (sin cerrar el anterior). Con este practicamos a
generar series mediante la herramienta de auto-relleno:

15
Series textuales - Extender los días de la semana de lunes a domingo. Ver las opciones que
ofrece Excel de auto-relleno (1)

Series numéricas – En el fichero en blanco, la empleamos para construir secuencias de números

que se incrementen de uno en uno, de dos en dos, etc. Vamos a emplearlas para auto-rellenar
la serie numérica de la referencias de los productos, en la hoja [Link]. (2) Probamos
también como auto-rellenar el precio viendo cómo se extienden también cifras con decimales.

Además de aplicar las series predefinidas, Excel permite definir listas personalizadas para cubrir
sus necesidades más frecuentes, dependiendo del ámbito en el que usted trabaje. Se pueden
definir en Opciones de Excel, Avanzadas, Modificar Listas Personalizadas (1). Una lista
personalizada sólo puede contener texto combinado con números. Para crear una lista
personalizada que contenga únicamente números tendrá que crear primero una lista de
números con formato de texto. Para ello hay que poner apóstrofe (‘) antes en las celdas que
usamos como referencia.

1. Vamos a crear una lista de ejemplo con nombres de productos presentes en el fichero
[Link], como por ejemplo ROTULADOR BABER AMARILLO, ROTULADOR BABER
ANARANJADO, ROTULADOR BABER AZULROTULADOR BABER AZUL CELEST, ROTULADOR
BABER CAFÉ (2)
2. Vemos que sucede al extender la lista. (3)
3. Definimos una serie numérica, mediante una lista personalizada, que permita rellenar con
números primos.

16
Por su parte, el relleno rápido es una funcionalidad que permite rellenar automáticamente los
datos cuando detecta un patrón. Dicho patrón puede emplearse para rellenar series. En este
caso, vamos abrir la hoja [Link]. Empleando el relleno rápido vamos a realizar las
siguientes tareas:

1. Extraer los nombres, los apellidos y las iniciales de los Jugadores. Utilizar bien las opciones
de relleno flash (1) o la herramienta de relleno automático para ello (2).
2. Transformar los nombres a mayúsculas.
3. Vemos como habilitar o deshabilitar el relleno rápido con las opciones de Excel. También
podemos forzar a que se ejecute la herramienta en el grupo de Datos, Relleno Rápido.

2.4 Dividir texto en columnas.

Esta funcionalidad permite dividir en varias columnas el contenido de las celdas.

1. Abrir de nuevo la lista de jugadores seleccionados del mundial ([Link].).

Activamos la herramienta Texto en Columnas (1), dentro de las Herramientas de datos,

17
 para separar los nombres y apellidos de los jugadores. Emplearemos el carácter de espacio
como delimitador (2) (3)
2. Ver que pasa al hacerlo con ancho fijo.
3. Repetirlo sobre el fichero de seleccionados con dorsal. Trasformar los dorsales a formato de
número. (4)

Ahora vamos a importar datos empleando la herramienta Desde Texto, del grupo Obtener Datos
Externos, de la ficha de datos. Con esta manera importamos datos del fichero [Link]. ¿Qué
sucede?

2.5 Importar datos desde Access /Conexiones del libro

Una base de datos organiza la información relacionada en tablas las cuales están compuestas
por columnas y filas. Las tablas empleadas por las bases de datos para organizar los datos son
uy parecidas a las tablas en Excel. Es por ello que es frecuente el uso de Excel para el
almacenamiento de datos. Sin embargo, Excel no es una base de datos en sí, por lo que es
recomendable emplear una base de datos como ACESS si se necesitan emplear los recursos que
sólo un gestor de bases de datos puede ofrecer.

Sin embargo, es posible importar datos de Access a Excel, sin tener conocimientos a penas de
bases de datos. Vamos a hacer un ejemplo, empleando el fichero de Access
Compañ[Link]. Antes de importarlo a Excel, revisamos ligeramente la base de datos
en Access, para ver su estructura.

18
 1. Seleccionamos la opción Desde Access, del grupo de Obtener Datos Externos, de la
ficha de datos. (1)
2. Si la base de datos origen tiene varias tablas, en la siguiente ventana deberemos elegir
la tabla que queremos importar. En este caso seleccionaremos todas las tablas. (2)
3. Finalmente nos aparecerá una ventana que permite decidir dónde y con qué formato
deseamos ver los datos (3). Comprobamos el resultado de la importación.
4. Para comprobar la actualización automática de los datos, cuando empleamos una
conexión, modificamos los datos originales del fichero [Link], que importamos
como texto en el ejercicio anterior. Fijamos que la conexión de datos se actualice cada
1 minuto. ¿Qué sucede?

Al importar los datos desde una base de datos, se establece una conexión, de tal manera que la
base de datos original actúa como origen de datos externo. Una conexión de datos es un
conjunto de información que describe cómo localizar, iniciar sesión y obtener acceso al origen
de datos externos (4,5). Los datos relativos a la conexión se almacenan en el libro o en un archivo
de conexión, que suele tener como formato el de conexión de datos de Office (ODC). Las
conexiones activas se pueden ver, configurar y actualizar mediante las herramientas del grupo
Conexiones, del menú de datos (6).

2.6 Seleccionar celdas. Copiar, cortar y pegar. Portapapeles de

Office
Al pegar un elemento aparece una etiqueta inteligente que ofrece una vista dinámica de las
diferentes opciones de pegado de que dispone Excel (1). Al situar el ratón sobre cada opción se
podrán ver los resultados antes de pegar definitivamente los datos.

19
1. Abrir el fichero [Link]. Copiar nombres de productos en la tabla de precios de la
librería. Usar para ello las opciones de herramientas Portapapeles, en Inicio. (2)
2. Continuar la operación con Ctrl+C y Ctrl.+V. Emplear la barra de fórmulas para editar los
valores pegados.
3. Copiar y pegar formulas. Vemos como se actualizan las referencias en las formulas. ¿Qué
pasa si seleccionamos la opción valores frente a fórmulas a la hora de pegar?
4. Copiar las formulas mediante el controlador de auto-relleno.
5. Emplear la herramienta cortar. En este caso se borra el contenido de la celda en origen, a
diferencia de copiar.
6. Empleando lo visto hasta ahora, completar el fichero de lista de precios.

El portapapeles de Office almacena hasta los últimos 24 elementos copiados por el usuario. Si
hay varios elementos en el portapapeles, podremos seleccionar cual se va a pegar.

1. Se probará el portapapeles de Office. Para ello se copian dos celdas seguidas, y se lanzará el
panel lateral del portapapeles, empleando el lanzador del grupo de herramientas
correspondiente (3)
2. Con la flecha al lado de cada elemento del portapapeles vemos como pegarlo y como
eliminarlo.
3. Probamos la opción PEGAR TODO del portapapeles. También BORRAR TODO.
4. En las opciones del Portapapeles activamos la opción de que aparezca al presionar Ctrl+C
dos veces y la que muestre automáticamente el portapapeles. (4)
5. Ver como aparecen las opciones en la barra de tareas al meter en el Portapapeles.
Comprobar que el portapapeles no guarda fórmulas pues no admite el comando Pegado
Especial.

2.7 Buscar y reemplazar datos.

La herramienta Buscar y reemplazar en Excel permite buscar elementos en un libro, como un
número o una cadena de texto en particular.

20
1. Abrir el libro de precios [Link] que completamos en el ejercicio anterior.
2. Empleando Buscar todos, en el cuadro de Buscar y Reemplazar (1), seleccionar todas las
celdas que tengan el valor CUADERNO. Los substituimos por LIBRETA. Ver como aparecen
los elementos seleccionados en el cuadro de Buscar y reemplazar (2).
3. Utilizar Reemplazar para cambiar el IVA del 21% al 18%. Substituirlo de nuevo por el 21%
mediante Reemplazar todos. Hacemos pruebas con el Formato a la hora de establecer el
valor de búsqueda.
4. Ver como la opción de Buscar Fórmulas destaca las fórmulas y como la de constante destaca
las constantes.

2.8 Ordenar Datos. Aplicar Filtros. Filtros avanzados

El orden de los datos es fundamental pues facilita su visualización y es necesario para su
comprensión. En Excel pueden establecerse hasta tres criterios de ordenación distintos: por
valores, por color de celda o por color de Fuente. También se puede establecer más de un nivel
de Ordenación.

21
1. Abrir el documento [Link]
2. Seleccionamos el conjunto de datos y los ordenamos de A -> Z y de Z -> A, según el nombre
del país (1) o (2). Ordenar por gasto total con Orden Personalizado en Ordenar y Filtrar. Ver
las opciones de ordenación que proporciona Excel: ordenar por colores de celda, de texto
etc
3. Para ver el funcionamiento de la ordenación a dos niveles (3), vamos a forzar el mismo valor
en gasto total en varios países. Introducimos entonces un segundo nivel de ordenación de
acuerdo con el gasto medio por persona, y vemos que sucede.
4. Ver como se emplea Ampliar la Selección Activa, cuando no se seleccionan todos los datos
de la tabla (Sólo la columna en cuestión). Si lo hacemos en esta hoja, no nos deja por la
cabecera, pues tiene cedas de otros tamaños. La solución es copiar la tabla en otra hoja y
hacerlo allí.

Por su parte, los filtros (1) son la opción más adecuada para localizar celdas que cumplan ciertas
condiciones. Así podemos obtener, agrupadas de manera provisional, las celdas cuyos
contenidos contengan uno o más criterios determinados (2). A diferencia de la herramienta
Ordenar, con los filtros no se modifica de manera definitiva el orden de la tabla.

1. Seguimos con el fichero de Gasto Turístico. Activamos Filtro y ponemos opción Mayor que
sobre la columna de Gasto total. Confirmar el Autofiltro personalizado, estableciendo una
opción de mayor que 1000.

22
2. Poner filtro entre 1000 y 5000
3. Usar el botón Borrar de la ficha de Ordenar y Filtrar para deshacer la aplicación del filtro.
4. Aplicar otros filtros numéricos como superior al promedio (ver previamente el valor de
promedio en la Barra de Estado).
5. Combinar alguno de los filtros anteriores con las casillas de verificación en el menú de
autofiltro, sobre el nombre de los países. Podemos dejar de ver las opciones de Resto de
Paises mediante el filtro y dejar sólo los que son realmente países

Para los filtros avanzados, definir otras dos celdas: una con el nombre del rótulo Gasto total y
otro con la condición >1000.

1. Aplicarlo como filtro avanzado, seleccionando la tabla y el criterio. Repetir la opción

activando Copiar en otro lugar.
2. Obsérvese que la opción de filtrado avanzado no puede deshacerse. Para borrar el resultado
deberá eliminar el contenido de las celdas con supr.
3. La opción de “solo registros únicos” que aparece en el filtro vale para eliminar duplicados.

2.9 Validación de datos.

La validación de datos puede usarse para restringir el tipo de datos o los valores que los
usuarios escriben en una celda.

23
1. Volver a la hoja de la lista de precios de [Link]
2. Activar en Datos, Validación de datos, la opción de Permitir para garantizar que los datos
sean menor que un precio máximo identificado (1). De esta manera vemos como no se
permite introducir valores incorrectos. Aplicamos esta opción para no permitir que se
introduzcan precios mayores que un valor determinado.
3. Cambiar el mensaje de error a advertencia. (2)
4. En la punta de flecha de Validación de datos, activamos la opción de rodear con un círculo
los datos no válidos. (3)
5. Ver otros posibles criterios para la validación de datos, como la fecha, la longitud de texto
etc
6. Creamos una lista desplegable para definir la categoría de cada producto. Para ello, con la
herramienta de Validación de datos, seleccionamos Lista (5). Previamente definimos una
lista con los tipos de productos que podemos permitir. Vemos como se crea una lista
desplegable en la celda (6).

2.10 Consolidar datos en varias hojas de cálculo

Excel permite combinar datos procedentes de distintas hojas de cálculo independientes en una
única hoja resumen, que podremos emplear a modo de informe. Esta opción se denomina
consolidar y permite, por ejemplo, fusionar datos procedentes de distintos empleados o de
distintos centros de trabajo de nuestra empresa. La herramienta Consolidar está accesible en la
ficha Datos, del grupo Herramientas de Datos (1). La herramienta permite la consolidación de
dos maneras distintas: (i) por posición, si los datos de origen tienen el mismo orden, lo que
sucederá si se emplea la misma plantilla, y (ii) por categoría, cuando los datos de una serie de
hojas de cálculo que tienen diferentes diseños pero tienen las mismas etiquetas de rangos.

El ejercicio se va a realizar con el fichero [Link].

24
 1. Creamos una hoja nueva en blanco dentro del fichero.
2. En la hoja nueva seleccionamos la herramienta consolidar. Dentro del cuadro
emergente de la herramienta consolidar, tenemos que seleccionar como referencias
las tablas de datos de cada una de las comunidades autónomas (2).
3. Tras seleccionar cada una de las referencias tenemos que presionar el botón agregar.
Inicialmente lo hacemos sin activar las opciones de Usar rótulos ni de crear vínculos
con los datos de origen. Empleamos la función Suma. Comparamos el resultado con la
hoja de Totales ya calculados.
4. Ahora seleccionamos la opción de usar rótulos. Vemos la diferencia. ¿Qué pasa si
excluimos los rótulos de la selección y activamos esta opción?
5. Probamos el efecto de aplicar otras funciones a la consolidación. (3)
6. ¿Qué sucede si cambiamos el rótulo de las filas en una de las páginas y tratamos de
repetir la consolidación?

Excel nos permite que la consolidación se actualice de manera automática cuando el origen de
datos cambia, o hacerlo de manera manual.

Consolidar datos por categoría es similar a la opción de crear una tabla dinámica, que se verá
más adelante. Sin embargo, las tablas dinámicas permiten reorganizar fácilmente las categorías,
algo que no es posible con la herramienta de consolidación.

2.11 Agrupar datos y subtotales.

La herramienta Subtotal esquematiza una lista de manera que pueda mostrar u ocultar las filas
de detalle de cada subtotal. Permite establecer sumas parciales, promedios, productos, contar
o mostrar valores máximos y mínimos. Al establecer un subtotal o más de uno, el programa
incluye en la hoja los controles habituales de un esquema. Eligiendo uno u otro nivel de esquema
se visualizará la tabla más o menos expandida.

25
1. Abrir el fichero [Link]
2. Utilizar las opciones de Subtotal del grupo Esquema, de la ficha Datos (1), para calcular los
totales de alumnos de Grado, Máster y Doctorado. Mostrar cómo se muestran/ocultan más
o menos niveles.
3. Añadir el cálculo del promedio desactivando previamente la opción reemplazar los
subtotales existentes para tener un nivel más en el esquema.
4. Establecemos subtotales en otros cambios de sección, para ver qué sucede.
5. Finalmente, quitamos todos los subtotales para eliminar la vista de esquema.

26
Módulo 3: Trabajar con Tablas
Las tablas permiten administrar y analizar los datos de manera independiente al resto de datos
de la Hoja de Cálculo. El uso de tablas facilita la aplicación de formato y la organización, el filtrado
y la presentación de datos.

3.1 Crear una tabla

1. Abrir [Link]
2. Crear una tabla mediante la opción de tabla, en el grupo Tabla, de la ficha Insertar (1).
3. Crear una tabla aplicando Dar Formato como tabla a los datos ya seleccionados. Para ello
se debe seleccionar la primera celda, y manteniendo pulsada la tecla MAYUSCULAS,
seleccionar la última de las celdas de la tabla (2). Ver como aparece la ficha contextual de
herramientas de tabla.
4. Vamos a extender de manera artificial el tamaño de la tabla, copiando de nuevo toda la tabla
por debajo del último elemento. Esto nos permite ver que al descender la barra de
desplazamiento vertical, la cabecera de la columna se corresponde con la cabecera de la
tabla. Eliminamos las filas añadidas.

27
5. Eliminar la tabla mediante la herramienta Convertir en rango, del grupo herramientas (3).
Podemos comprobar que la tabla ha desaparecido y que ahora ya no sale la ficha conceptual
herramientas de tabla cuando nos situamos sobre una de las celdas que contienen los datos
originales.

3.2 Cambiar el formato de una tabla. Eliminar duplicados.

Vamos a modificar el formato de la tabla creada en el apartado anterior, trabajando con la
ficha contextual herramientas de tabla (1).

1. Cambiar el nombre de la tabla

2. Cambiar el tamaño de la tabla arrastrando la flecha que aparece en la esquina inferior
derecha (2). Cambiarlo de nuevo empleando la herramienta de Cambiar tamaño de la tabla,
del grupo Propiedades, en la ficha contextual.
3. Practicamos con las opciones de estilo de la tabla: Activar/desactivar la Fila de encabezado,
Filas con bandas, de totales… Vemos los efectos que estas opciones producen en la tabla.
4. Aplicar alguno de los estilos rápidos de tabla (3).

28
3.3 Crear un estilo rápido de tabla

1. Acceder a la opción de Nuevo estilo de tabla, dentro de la galería de estilos rápidos (1). Lo
hacemos partiendo del estilo que más nos interese.
2. Vamos a cambiar los bordes exteriores de la tabla. Para ello seleccionamos aplicar estilo a
Toda la Tabla. Después, en el cuadro de formato de celda, seleccionamos los bordes que
nos interesa (2). Vemos cómo se va modificando en la vista previa.
3. Aplicar Estilo a Encabezado, Formato. Aplicar un efecto de relleno degradado de dos colores.
4. Aplicar Estilo a Primera franja de filas, a la Segunda Franja de líneas y a la Fila de Totales.
5. Probamos otras opciones para definir nuestro estilo particularizándolo para otros elementos
de la tabla. Cuando esté listo el formato vemos el efecto en la tabla.
6. Aplicamos el mismo efecto a otra tabla, dentro del mismo documento.

29
3.4 Filtrar datos en una tabla. Quitar Duplicados
Igual que hemos visto en el módulo anterior el filtrado de datos para rangos, es posible hacerlo
también dentro de una tabla. Para ello hay que activarlo en las opciones de estilo de tabla. (1)

1. Continuamos trabajando con el mismo fichero de precios. Aplicamos un filtro numérico al

Precio Unitario, para seleccionar los artículos que valgan más que un euro. (2)

2. Aplicando un filtro de texto, nos quedamos con los productos que empiecen por D.

3. Vamos a añadir una nueva fila al final, en la que copiamos el contenido de la última fila de la
tabla original. Probamos el comando Quitar Duplicados. Comprobamos que funciona con la
última fila añadida. Vemos que se puede seleccionar las columnas afectadas por la eliminación
de duplicados. Excel nos informa de los valores duplicados que han sido eliminados

Hay que destacar la diferencia entre Quitar duplicados y el filtro de datos. El filtro de datos con
la opción de filtrar valores únicos, oculta los duplicados temporalmente, pero no los elimina de
la tabla. (Para ello marcar en Filtro Avanzadas y marcar “Solo registros únicos”, sin ninguna
condición de filtro)

Además hay que destacar que la herramienta de quitar duplicados solo afecta a la tabla. Para
ello, valiéndonos de la herramienta de auto-relleno, ponemos números desde 1 al tamaño de la
tabla. Se aplica el quitar duplicados y vemos como no afecta a la columna de los números.

Para acabar este módulo, vamos a definir una tabla y un Estilo Rápido que nos parezca adecuado
para el fichero [Link].

30
Ejercicio de Repaso 1
Dada la siguiente Tabla en Excel, correspondiente a un registro de operaciones de una
empresa, se propone realizar las siguientes actividades:

1. Introducir una tabla similar, en la que:

- El formato de todos los campos debe ser como se indica

- los valores de la columna de Mes, Producto, Vendedor y C. Autónoma deben poder

elegirse de una lista de valores predefinidos, tal y como se indica para el caso de las C.
Autónomas.

- La columna de años sólo debe permitir que se introduzcan valores entre el 1 de enero
de 2018 y el día de hoy.

- Para rellenar los campos numéricos, podemos emplear la función [Link].

Se puede comprobar en la ayuda online de Excel su funcionamiento.

Se debe emplear el mecanismo de rellenar automáticamente, siempre que sea posible.

2. Definir un estilo propio de tabla, que se corresponda con la imagen.

3. Añadir una nueva pestaña a la cinta de opciones, incluyendo en ella las herramientas que
has utilizado para las operaciones anteriores.

31
Módulo 4: Trabajar con hojas y libros.
4.1 Añadir y eliminar hojas en un libro de trabajo. Modificar las
etiquetas
4.1.1 Añadir Hojas
Al crear un nuevo libro este tendrá sólo una hoja. Esto puede cambiarse desde el cuadro de
opciones de Excel, sin embargo, lo más normal es añadir directamente las hojas a los libros que
las requieran. Existen múltiples maneras de añadir hojas a nuestro libro, como vemos a
continuación.

Abrimos el fichero de [Link].

1. Cambiar en las Opciones de Excel el número de hojas que se Incluyen al crear nuevos libros
(1)
2. Insertar una nueva hoja desde la ficha de Inicio, grupo Celdas, Insertar. (2)
3. Insertar una nueva hoja haciendo clic con el botón derecho sobre la pestaña de la última
hoja creada. Este menú permite insertar hojas, gráficos, macros e incluso plantillas de Excel,
entre otros. (3)
4. También podemos Mover o copiar hojas desde el menú contextual de la hoja. También
podemos mover copiar en el comando Formato del grupo Celdas. Ver que si se copia una
hoja aparece un número entre paréntesis, para indicar que es una copia. Mover hojas
directamente, arrastrando las etiquetas.
5. Finalmente insertar una hoja nueva con el icono +, a la derecha de las etiquetas de hoja.
Esta es la manera más rápida. (4)

32
4.1.2 Eliminar hojas (y Ocultarlas)
Se trata de un proceso irreversible, por lo que hay que estar muy seguro antes de hacerlo. Si la
hoja no está vacía, nos sale un cuadro en el que nos advierte de esta circunstancia, al intentar
borrarla.

1. Borrar una hoja vacía. Intentar borrar una hoja con datos (Cancelamos antes de borrar). Esta
operación puede hacerse con la herramienta Eliminar, del grupo Celdas. Hacerlo también
con el menú contextual de la etiqueta de hojas.
2. Vamos a ocultar una hoja con el menú de Celdas, Formato, Ocultar o Mostrar. Volver a
mostrarla con el mismo menú, seleccionando la hoja. Hacerlo también con el menú
contextual.

4.1.3 Modificar las etiquetas de las hojas

Las etiquetas identifican a las hojas en la parte inferior del Área de trabajo. Excel permite
cambiar los nombres de las hojas y aplicar distintos colores a cada una de las etiquetas. De esta
manera será más fácil su identificación.

1. Pulsar el comando Formato, del grupo Celdas y elegir la opción Cambiar el nombre de la
hoja.
2. En el mismo menú, seleccionar la opción Color de la etiqueta. Elija uno de los colores
estándar. Mientras la hoja esté seleccionada, el color se mostrará atenuado. Seleccionar
otra hoja para ver el color real. Cambiar de nuevo el color, en este caso, aplicando un color
personalizado, con la opción de Más Colores.
3. Hacer lo mismo empleando el menú contextual de la etiqueta. Igualmente, se puede editar
el nombre haciendo doble clic sobre la etiqueta. El tamaño de la pestaña se adapta a la
longitud del nombre de la hoja.

4.2 Aplicar un fondo a la hoja.

El fondo de la hoja puede personalizarse, empleando cualquier imagen almacenada en el
equipo. Se trata de un efecto de visualización que no se imprimen. Tampoco si se publica como
página web. Este ejercicio lo vamos a hacer con el fichero [Link]

33
1. Sobre una página en blanco, seleccionar en el grupo Configurar Página, en la pestaña Diseño
de Página, el comando Fondo (1). Seleccionar el fondo de la ETSI Industriales para su
aplicación.
2. Dependiendo del tamaño de la imagen, esta se repetirá por la hoja hasta rellenarla.
3. Se pueden ocultar también las líneas de división, en el grupo de Diseño de Página, Opciones
de Hoja, Líneas de División, Ver. Comprobar el efecto de la activación y desactivación de
esta opción.
4. En el mismo menú de Configurar Página es posible Eliminar el fondo, aunque esta opción
sólo está disponible cuando el fondo está aplicado.
5. Repita la aplicación de Fondos, de esta vez buscando alguno que le interese con el buscador
bing, integrado en la propia herramienta (por ejemplo industria). Buscar alguna imagen
pequeña buscando el efecto mosaico.
6. Copie la página en otro libro para comprobar que el fondo no se exporta.
7. Por último, vemos que es posible insertar también una imagen mediante la opción Insertar
Imagen.

4.3 Insertar / Eliminar filas y columnas. Insertar celdas.

Las celdas se identifican por un número de fila y una letra de columna. Excel cuenta con 16384
columnas de ancho por 1048576 filas de alto.

4.3.1 Insertar filas y columnas

Se trata de insertar filas y columnas de datos intermedios entre los ya rellenados. Se realiza
desde el comando Insertar del grupo de herramientas Celdas (1), en inicio, o desde el menú
contextual de las filas o de las columnas.

1. Abrir [Link]. Insertamos un nuevo alumno inventado.

2. Ver el menú inteligente que aparece al insertar una fila (2). Elegir un color de fuente
determinado para una de las filas y ver cómo afecta si se elige el mismo formato que arriba
o que abajo. En Opciones de Excel, Avanzadas, Copiar, Cortar y Pegar podemos decidir si
debe aparecer la opción de Mostrar botones de opciones de inserción, o no.
3. Meter los datos del nuevo alumno y usar tabulador para ir avanzando
4. Añadimos una nueva columna para poner como parte de la evaluación continua los puntos
de clase.
5. Es importante comprobar que al añadir nuevas filas/columnas las referencias del resto de
celdas se actualizan.

34
4.3.2 Insertar celdas
Al insertar una celda es necesario especificar como deben reestructurarse el resto de las celdas
que componen la hoja.

1. Probar a insertar una celda en una posición arbitraria de la tabla y ver qué pasa con cada
una de las opciones de inserción que nos da la herramienta (1).
2. Copiar una celda con una fórmula y con el menú contextual del botón derecho, seleccionar
Insertar celdas copiadas. De esta manera también se inserta la celda.

4.3.3 Eliminar Filas, Columnas y Celdas

Puede hacerse bien mediante el menú contextual de la fila, la columna o la celda, o mediante
la función Eliminar del grupo de herramientas Celdas. Esto provocará que se desplacen filas o
columnas.

Si empleamos el botón Suprimir, en lugar de la opción de Eliminar, se elimina sólo el contenido

de la celda.

1. Duplicar la lista de nombres de los alumnos, para eliminarlos después. Se utilizará tanto el
menú contextual como el del grupo de herramientas celdas para ello. (1)
2. Repetir la prueba por filas, columnas y por celdas. Ver en este caso las opciones que ofrece
el menú eliminar.

35
4.4 Ancho de columna y alto de fila. Autoajuste.
En una hoja, todas las columnas presentan un ancho estándar de 10,71 puntos (80 pixeles). El
alto de las filas por defecto es de 15 puntos (o 20 pixeles). Ajustaremos su tamaño con las
opciones de Ancho de Columna (o de filas) incluido en el comando formato del grupo de
Herramientas Celdas, en la ficha Inicio. Tambien se puede hacer directamente arrastrando la
línea divisoria que la separa de la siguiente.

4.4.1 Ancho de Columna y Ancho de Fila

1. Al hacer clic en la línea divisoria entre las cabeceras de las columnas (o filas) veremos su
anchura (o alto). (1,2)
2. Asignar un Ancho Predeterminado para toda la hoja, mediante Inicio, Celdas, Formato (3).
Poner el valor de 12 (4). Ver como sólo afecta a las columnas sin datos. Cambiar el ancho de
una columna con datos, usando la opción Ancho de Columna del mismo menú.
3. Repetir los pasos para el alto de fila. En este caso no se puede poner una altura
predeterminada.
4. Repetir en ambos casos arrastrando directamente la línea divisoria entre filas o entre
columnas. (5)

36
4.4.2 Autoajuste de Filas y Columnas
El autoajuste permite modificar la anchura de las columnas y la altura de las filas, de acuerdo
con su contenido. Estas opciones se encuentran también en el comando formato del grupo de
herramientas Celdas. También se puede hacerlo haciendo doble clic sobre la línea que separa
las cabeceras. Vamos a hacer pruebas sobre la misma hoja de notas.

1. Primero aumentamos muchísimo el tamaño del texto en una celda. Comprobamos que la
altura de las filas se ajusta automáticamente, pero no su ancho. Podemos aprovecharnos de
la vista previa activa al seleccionar el tamaño de la fuente. Esta característica permite ver
cómo quedaría la hoja con el cambio, pero si necesidad de aplicarlo. Con la tecla Esc
podemos volver al valor anterior. El autoajuste de filas tiene sentido cuando se ha
modificado manualmente su altura, sino es mejor dejarlo en el valor por defecto.
2. Aplicamos el autoajuste tanto haciendo doble clic sobre la línea que separa las cabeceras,
como con el menú de Formato de Celdas.
3. Se pueden autoajustar varia filas o columnas a la vez, empleando la opción correspondiente
del menú de Formato de Celdas.
4. Otra manera de hacer que una columna tenga el mismo ancho que la otra, es copiar una
celda de la columna de origen y pegar su “ancho” en la columna de destino, empleando la
opción de Mantener ancho de columnas de origen en el menú de Pegado.

4.4.3 Ocultar columnas y filas

Es posible ocultar temporalmente filas, columnas e incluso hojas de un libro. De esta manera,
aunque contengan referencias a otras celdas, estas no se verán afectadas, pues el elemento
sigue formando parte de la hoja aunque no se encuentre visible.

Para volver a mostrar una columna o una fila oculta debemos seleccionar las dos columnas o
filas adyacentes y usar las opciones Mostrar del comando Formato.

1. Ocultar una columna. Ver como no desaparece y como el nombre se reserva en el título de
columnas. (1)
2. Cuando la columna está oculta, podemos seleccionar en Buscar y Seleccionar -> Ir a, y
seleccionar una celda de la columna oculta. Lo hacemos para comprobar el que
efectivamente está ahí.

37
3. Volvemos a mostrarla seleccionando las dos columnas o filas adyacentes, aplicando la
herramienta Mostrar.
4. Eliminar una columna directamente, ver como en este caso no se reserva el nombre. Hacer
ambas pruebas con la columna de la nota del trabajo para ver qué pasa con las referencias.

38
Módulo 5: Dar formato y estilo a las celdas.
En este módulo se pondrán en práctica las distintas opciones que ofrece Excel para dar formato
a las celdas que componen una hoja de cálculo. Esto se refiere tanto al texto que contienen,
como al relleno y los bordes de las mismas, a la modificación de la orientación, a crear y aplicar
estilos personalizados así como a ocultar y bloquear celdas.

5.1 Formatos de celda. Formatos de texto.

El formateo de las celdas se lleva a cabo con las herramienta de la ficha de inicio de la cinta de
opciones

Excel permite seleccionar de entre la lista de fuentes instalada en el equipo (tipo de letra), aquel
más apropiado en cada caso. Además permite cambiar atributos como Negrita, Cursiva y
Subrayado.

Todas las herramientas relacionadas están en la ficha Inicio, grupo Fuente, Formato de Celdas.
También están en el grupo Celdas, Formato, Formato de Celdas, seleccionando la ficha Fuente.

Vamos a trabajar sobre el fichero [Link]

1. Seleccionar las celdas de la cabecera y configurarlas con Arial, Negrita, tamaño 14. Hacerlo
de las 5 maneras siguientes: Con las herramientas de Fuente (1), con las de Celdas (4), con
el menú contextual de la celda (2), lanzando la opción de Formato de Celdas desde el
lanzador situado en la esquina de fuente (3) y con la mini-barra de herramientas que aparece
al pulsar el botón derecho del ratón. Autoajustar los encabezados, si es necesario.
2. Cambiar el color de la fuente, accediendo de nuevo de cualquiera de las maneras. Si lo
realizamos a través del cuadro Formato de Celdas, podemos tener una idea de vista previa.
Este cuadro también nos permite seleccionar Más colores, además de los que aparecen por
defecto.
3. Dar formato al resto de celdas de la tabla.

39
5.2 Color y efecto de relleno. Alineación y orientación.
5.2.1 Color y efecto de relleno de las celdas
En este ejercicio se estudiará cómo aplicar el denominado color de relleno, es decir, como
seleccionar el color del fondo de las celdas. Se realiza mediante la herramienta Color de relleno
del grupo Fuente, de Inicio. El icono simula un bote de pintura (1).

1. Seleccionar el color de relleno adecuado para la cabecera de la tabla de notas. Obsérvese

como la vista previa permite ver el efecto del relleno antes de aplicarlo definitivamente
2. Repetir la opción para las distintas columnas de la lista de notas.
3. Repetir la asignación de colores para la hoja [Link]. En este caso podemos definir
bloques de celdas en función del tipo de artículo, para facilitar su localización.
4. Sobre este mismo libro, definir un efecto degradado. Para ello es necesario abrir la opción
Celdas, Formato, Formato de Celdas. En ese punto seleccionar la ficha Relleno, Efectos de
relleno. Definir un degradado con dos colores (2). Obsérvese que el efecto se aplica a cada
celda seleccionada.
5. En el mismo menú de Relleno, aplíquese sobre otro grupo de celdas el relleno con trama.
Seleccione un Estilo de trama adecuado y observe como esta opción favorece la legibilidad
de las celdas.

5.2.2 Alineación y Orientación. Ajustar Texto.

Por defecto, el texto introducido en una celda se alinea a la izquierda, y los datos numéricos, a
la derecha. Verticalmente se sitúan siempre en la parte inferior de la celda. Estas opciones se
pueden ajustar mediante los iconos del grupo de herramientas Alineación en inicio. También
pueden seleccionarse en la pestaña de Alineación de Formato de celdas. En ese mismo menú
se puede seleccionar la sangría y la alineación.

En caso de que los textos sobrepasen las dimensiones de las celdas, en la pestaña de Alineación
también permite Controlar el texto, pudiendo seleccionar entre Ajustar o Reducir el texto, o
Combinar celdas.

40
1. Comprobar con una celda vacía la alineación por defecto en Excel, tanto con texto como con
números.
2. Probar las distintas opciones de Alineación presentes en la ficha Inicio (1). Abrir el cuadro
Formato de Celdas, Alineación y centrar todos los encabezados de las columnas.
3. Aumentar la altura de una fila y probar las opciones de alineación vertical. En este caso
abriremos el cuadro de Formato de Celdas desde el grupo Celdas.
4. Observar que desde el menú contextual de la celda se puede acceder también al cuadro de
Formato de Celdas (3). En este cuadro se puede aplicar sangría, combinados con las distintas
alineaciones. Ver como el texto de una celda invade las contiguas, si están libres.
5. En el menú de Formato de Celdas, Observar las opciones de Orientación en el menú, fijando
distintos valores de grados para la orientación. Se puede hacer también arrastrando el
rombo rojo del esquema.
6. Observar las opciones de Orientación en el Grupo de Formato de celdas (2). Aplicarlas de
nuevo sobre la cabecera la tabla, para probar su efecto. No es posible inclinar texto en celdas
con sangrías.
7. En caso de una celda que incluya texto que sobresalga de los límites de una celda, emplear
las opciones de Ajustar Texto del cuadro Formato de Celdas, para ver qué sucede. Probar
sucesivamente Ajustar Texto (el texto se reparte en múltiples líneas dentro de una
columna), Reducir hasta ajustar (el tamaño de la fuente se reduce hasta adaptarse al as
dimensiones de la celda) y Combinar Celdas. En este último caso se debe seleccionar
previamente las celdas que se desean combinar.

5.3 Aplicar bordes a las celdas.

El fondo de las hojas se inicializa por defecto en color blanco, empleando un trazo muy fino para
las líneas de división entre fila y columnas. Sin embargo, el usuario puede añadir o modificar los
bordes de las celdas. Esto puede llevarse a cabo desde la ficha Borde, del cuadro Formato de
Celdas, así como en el grupo de herramientas Fuente, de la ficha Inicio.

41
1. Vamos a añadir bordes a las celdas que hacen de encabezado de las columnas. Elegir en el
desplegable de Bordes en el grupo Fuente la opción Borde Inferior Grueso (1).
2. Abrir la ficha Borde del cuadro de Formato de celdas para personalizar un borde (2). Añadir
un borde con línea discontinua de un color de su elección. Ver cómo se puede pre visualizar
el resultado en el cuadro.
3. Aplicar líneas interiores a las celdas, en color negro. Añadir una línea diagonal (u otras líneas
interiores a las celdas del encabezado).
4. Utilizar las opciones de dibujo manual de bordes personalizados, que se agrupan bajo el
título Dibujar Bordes en la herramienta Bordes. Revisar las opciones de Color de línea y de
Estilo de línea asociadas a dibujar bordes. Emplear borrar bordes para deshacer los bordes
creados.

5.4 Crear y aplicar estilos de celda. Personalización de los

formatos.
Excel permite definir estilos de celda personalizados, que se pueden replicar en todas las celdas
que se desee. El estilo incluye todo lo relacionado al formato de la celda: fuente, formatos de
número, bordes y sombreado. El trabajo con estilos ser realiza desde el botón de Estilos de celda
del grupo Estilos. Ahí aparecen una serie de estilos prestablecidos, que pueden duplicarse y
modificar para crear nuevos estilos. Los estilos predefinidos dependen del tema seleccionado.
También pueden crearse nuevos estilos desde cero.

42
1. Vamos a trabajar de nuevo con el encabezado del fichero anterior. Sobre él aplicaremos
alguno de los encabezados predefinidos. Emplearemos la vista previa para ver como
resultaría la aplicación, sin llegar a aplicarlos definitivamente. (1)
2. Duplicar uno de los estilos personalizados de encabezado. En la ventana de Estilo se puede
seleccionar que elementos se incluyen en él (2). En este caso, no se desean incluir las
opciones de Fuente. Una vez duplicado, se debe modificar para cambiar el color del borde y
el relleno de la celda. Aplicar de nuevo el estilo personalizado a la cabecera de la tabla.
Comprobar que los estilos de fuente efectivamente no se aplican.
3. Para eliminar el estilo aplicado, seleccionamos las celdas de interés y aplicamos el estilo
Normal.

5.5 Ocultar y Bloquear celdas

Es posible ocultar una celda de tal manera que ésta continua en el área de trabajo pero su
contenido no se muestre en la barra de fórmulas.

Excel permite además bloquear celdas. Esto es útil para evitar que el usuario modifique fórmulas
permitiendo introducir datos en las variables de las mismas. Las celdas se encuentran bloqueada
por defecto, por lo que deberemos seleccionar aquellas celdas en las que queremos permitir
que el usuario introduzca nuevos datos y desbloquearlas, antes de proteger toda la hoja.

43
1. Abrir nuevamente el cuadro de Formato de celda y seleccionar la pestaña Proteger. Esta
pestaña permite seleccionar si la celda debe estar oculta y/o bloqueada. Vamos a hacer que
esté Bloqueada y Oculta (1)
2. Para que la ocultación sea efectiva, es necesario proteger la Hoja. Esto se realiza con la
opción Proteger Hoja, del comando Formato. La herramienta solicita una contraseña que el
usuario puede facilitar a otros usuarios para que estos puedan desprotegerla. Si no la
proporcionamos cualquier usuario podrá desprotegerla. Al realizar esto, si se percibe el
efecto de la selección anterior: al seleccionar la celda su contenido no aparece en la barra
de fórmulas. (2)
3. Pruebe con otra celda que no esté oculta, para comprobar que esta efectivamente aparece
en la barra de fórmulas.
4. A continuación seleccionamos una celda que incluya variables y, en el menú de Formato de
Celdas, desactivamos la opción Bloqueada. Posteriormente Protegemos la hoja y
comprobamos que sólo podemos modificar la celda que anteriormente habíamos
desbloqueado. Usar esta opción para evitar que nadie modifique la fórmula de cálculo de
nota media en la hoja [Link].

44
Ejercicio de Repaso 2
Se pide diseñar una hoja de parte de trabajo que deberá tener el siguiente aspecto:

Se trata de conseguir esta apariencia en cuanto a las dimensiones de filas y columnas, los
rellenos, formatos de bordes, así como aplicar las fórmulas que sumen las horas semanales y las
totales de cada tipo.

Generar una segunda hoja para que acumule las del mes anterior.

45
Módulo 6: Navegación por la hoja de cálculo.
El desplazamiento por los libros y hojas de Excel puede resultar complicado en el caso de trabajar
con hojas que contengan muchos datos. El desplazamiento puede cambiar la selección de la
celda o no.

En este módulo veremos cómo navegar por la hoja de cálculo.

6.1 Moverse por las hojas

El movimiento por la hoja puede hacerse de múltiples maneras:

• Con el ratón
• Con las teclas de desplazamiento (derecha, izquierda, arriba y abajo, AvPág, RePág, Inicio y
Fin). En el caso del movimiento con AvPág, RePág, Inicio y Fin, por defecto nos lleva al final
de la hoja. Salvo que exista alguna celda con contenido en la fila (o columna) activas.
• Con las barras de desplazamiento
• Con la función Ir a (Ctr.+I), que forma parte de del comando de Buscar y Seleccionar del
grupo Modificar (1,2). Con esta opción también podemos ir a otra hoja o incluso a otro libro
que tengamos abierto. Para ello introducimos la celda con el formato:
Nombredelahoja!:Celda. En este mismo menú exista la opción Ir a especial, que permite
buscar sólo celdas visibles o la última celda en la que se han introducido datos (3)

Probar todas estas opciones sobre la hoja [Link]

6.2 Inmovilizar paneles

En tablas muy grandes suele ser interesante mantener visibles las cabeceras de ciertas filas y
columnas. Esto se puede hacer con la función Inmovilizar, del grupo Ventana, de la ficha Vista.
Este menú ofrece tres opciones: Inmovilizar la fila superior, inmovilizar la primera columna, o
inmovilizar paneles partiendo de la selección actual. En este caso, se crean hojas de cálculo
independientes por las que nos podemos desplazar, mientras que las inmovilizadas permanecen
siempre visibles.

46
 1. En la hoja de [Link], inmovilizar primero la fila del encabezado y después la
primera columna (1). El elemento afectado por la inmovilización al aplicar los comandos
de Inmovilizar fila superior e Inmovilizar primera columna solo depende de la primera
celda que se encuentre a la vista.
2. Tratamos inmovilizamos el nombre del alumno. Para ello tenemos que ajustar esta
columna para que sea la primera que aparece a la vista.
3. A continuación mostramos la opción de Inmovilizar Paneles (2). En este caso hay que
seleccionar una celda. A partir de esta, las filas y columnas situadas por encima y a la
izquierda de la seleccionada serán inmovilizadas.
4. Si queremos bloquear filas y/o columnas enteras, hay que seleccionar la fila situada
debajo o la columna situada a la derecha del punto que queremos inmovilizar.

Hay que recordar que en caso de una tabla, los encabezados siempre permanecen visibles.

6.3 Las diferentes vistas del libro. Configurar márgenes,

encabezados y pies de página para impresión.
Excel soporta tres modos de visualización: la vista Normal, empleada por defecto; la vista Diseño
de Página, que muestra la hoja tal y como se imprimiría; y la vista previa de salto de página,
que muestra preliminarmente la posición de los saltos de página. La vista se selecciona en la
ficha Vista, o a la derecha de la barra de estado.

47
1. Por defecto, las hojas se muestran en la vista Normal. Vamos a cambiarlo a la vista Diseño de
Página, empleando grupo de herramientas Vistas de libro (1). Aquí podemos preparar la
impresión del libro, usar reglas para medir, establecer márgenes y encabezados…

2. En esta vista añadimos un encabezado a nuestra página. Modificamos a nuestro antojo los
márgenes de las hojas. Acceder a la pestaña de Diseño de Página para ver cómo podemos
cambiar aquí también estos aspectos de la configuración.

3. Vamos a las herramientas de Diseño de página, Página (2), en el grupo Ajustar área de
Impresión. Por defecto, en este grupo se selecciona el ancho y el alto de manera automática. Lo
vamos a modificar para que se limite a una página, en ambos casos. Vemos que sucede con la
escala. Podemos lanzar también la ventana de Configurar Página (3), para ajustar este y otros
parámetros. Comprobamos en la barra de estado que ahora el libro sólo tiene una página.
Forzamos a que tenga dos páginas. Vemos además en el cuadro de Configurar Página, en Hoja,
como podemos hacer que se repita la fila de encabezado en todas las páginas.

4. Abrimos la Vista previa de Salto de Página. Ahora vemos las divisiones de las páginas para la
impresión de la hoja Excel. Su inserción es automática y depende de la configuración de la página
(márgenes, encabezados…). Más adelante jugaremos con los saltos de página.

6.4 Uso del Zoom.

El Zoom representa el porcentaje de visualización de una hoja de cálculo. Se puede justar en el
grupo Zoom de la ficha Vista o en la barra de estado.

48
1. Comprobamos que inicialmente el Zoom aplicado por defecto es del 100%.
2. Modificar el valor de Zoom tanto con desde la ficha Vista (1) como desde la barra de estado
(2). Lanzamos el Cuadro de Zoom (3) para ver los porcentajes de ampliación
predeterminados.
3. También se puede ajustar la ventana a las celdas que previamente se hayan seleccionado.
Esto es similar a la opción Ampliar selección del grupo de Zoom. Probamos el efecto de esta
opción seleccionando celdas con distintas disposiciones. En este mismo grupo, el control de
100% permite volver de forma rápida a ese valor.
4. Con el zoom ampliado, cambiamos la hoja del libro y vemos como a estas no le han afectado
los cambios en el Zoom. Realizamos de nuevo el cambio de Zoom con el control deslizante
de la barra de estado.

6.5 Trabajar con varias ventanas. Dividir y Organizar las ventanas

6.5.1 Trabajar con varias ventanas
Excel permite mostrar de manera simultánea el mismo libro en varias ventanas. Esto puede ser
muy útil, sobre todo, si contamos con dos monitores El control de las mismas se realiza desde
el grupo Ventana, de la ficha Vista (1).

1. Abrimos dos libros de Excel simultáneamente. Comprobamos que aparecen los dos
(minimizados) en la barra de tareas de Windows. Con los iconos de la barra podemos
cambiar directamente de libro. De la misma manera, podemos ver cómo se puede
seleccionar fácilmente el libro que queremos abrir con la herramienta Cambiar Ventanas
(2) del grupo Ventana.
2. Para trabajar con distintas zonas del mismo libro a la vez, se puede utilizar el botón Nueva
Ventana. De esta manera se abrirá una nueva ventana con el mismo libro. Los cambios
aplicados en cualquiera de las ventanas se aplicarán a todo el libro. Probamos esta opción
varias veces, viendo cómo se van renombrando las nuevas ventanas. Probar de nuevo
Cambiar Ventanas. Las ventanas se pueden cerrar con el aspa de la esquina superior
derecha o con Ctr.+F4.
3. También es posible Ocultar Ventanas para que temporalmente no sean visibles en el área
de trabajo. Para que vuelvan a parecer hay que activar el icono Mostrar, dentro del mismo
grupo.

49
6.5.1 Dividir y Organizar las Ventanas
Para comparar dos hojas (del mismo libro o de libros diferentes), la herramienta Organizar del
grupo de Ventana permite mostrar en pantalla todas las ventanas abiertas, con distintas
distribuciones posibles: horizontal, vertical, cascada o mosaico.

También permite mostrar dos hojas del mismo libro o de libros diferentes, la una junto a la otra,
con la opción ver en paralelo. Es especialmente interesante comprobar el desplazamiento
sincrónico, con el que ambas hojas se desplazan a la vez.

1. Abrimos en una nueva ventana la copia del libro [Link]

2. Probamos la opción Ver en paralelo. En este caso comprobamos que se ha activado
Desplazamiento sincrónico. Deshabilitamos esta opción y vemos lo que sucede.
3. Modificamos el tamaño de la ventana de uno de los libros. Después activamos Restablecer
posición para ver cómo se vuelven a ordenar correctamente.
4. En uno de los libros activamos Nueva Ventana. Así comprobamos como se pueden ver dos
páginas del mismo libro en paralelo.
5. Abrimos dos ventanas más. Finalmente presionamos Organizar todo que permite controlar
la disposición de las ventanas de los distintos libros abiertos. Vemos que sucede al
seleccionar las opciones Mosaico, Cascada, Horizontal…

Por último, Excel nos permite visualizar varias partes distintas de un mismo libro en la pantalla
a la vez. Esto se realiza mediante la opción Dividir, también incluida en el grupo Ventana. Cada
una de las partes distintas en las que se divide el libro tendrá sus barras de desplazamiento
horizontal y vertical independientes (1). Para modificar el punto en el que se realiza la división
basta con seleccionar una nueva celda. Manteniendo seleccionada la línea de división, ésta
puede ser desplazada e incluso eliminada.

1. Seleccionamos la celda A1 y activamos la división de la hoja, haciendo clic en Dividir, de la

ficha de Vista. Vemos como nos podemos desplazar de manera independiente por cada una

50
 de las subdivisiones. A diferencia de la opción de inmovilizar, mediante Dividir se establecen
copias completas de la hoja en cada subdivisión.
2. Desplazamos las líneas de división, llegando a eliminar la vertical.
3. Eliminamos todas las líneas presionando de nuevo la ficha de Vista. Seleccionamos una celda
por la que queramos hacer la división y volvemos a activar la División.

6.6 Crear vistas personalizadas.

Es posible crear y aplicar vistas personalizadas que incluyan información sobre la presentación
de la hoja. Esto incluye el ancho de columnas, el alto de filas, los márgenes, encabezados etc
Una vez que hemos guardado la vista personalizada, ésta puede ser aplicada a otras hojas de
trabajo. La función de vistas personalizadas no está disponible si la hoja incluye tablas.

1. Vamos a configurar una hoja ocultando alguna columna, ponemos una orientación
horizontal para la hoja y un encabezado de vuestra elección.
2. Con esta configuración Agregamos una nueva Vista Personalizada (en la ficha de Vista).
3. Posteriormente cambiamos alguna de las opciones de la página, como el pie de página,
ocultamos una columna distinta etc
4. Volvemos a aplicar la lista anteriormente generada. Comprobamos que la hoja recupera la
opción preestablecida.
5. Por último, en el mismo cuadro de vistas personalizadas eliminamos la vista que acabamos
de aplicar.

51
Módulo 7: Realizar cálculos con Excel: fórmulas
y funciones
El objetivo de este módulo es permitir crear listas de datos en las que estos se actualizan
periódicamente. Para ello se incluyen fórmulas que realizan cálculos.

Una fórmula puede contener funciones (fórmulas ya definidas en Excel que toman un conjunto
de valores de rentrada, realizan una operación y devuelven otro valor), referencias (nombres de
las celdas según sus coordenadas en la hoja de cálculo), operadores (símbolos que especifican
el tipo de operación) y constantes (valores fijos).

En este módulo vamos a trabajar con la hoja: [Link]

7.1 Crear y editar una fórmula. Copiar y pegar fórmulas.

Definición y uso de nombres de rangos.
7.1.1 Crear una Fórmula
Repasamos la introducción de fórmulas, que ya ha sido explicada en módulos anteriores.

1. Hacer la suma de Hombres y mujeres empleando constantes. Ver que no cambia el resultado
si cambia la celda de origen.
2. Hacer la suma mediante referencias a celdas, seleccionándolas previamente (se denomina
semiseleccion, cuando tiene el borde centelleante). Ver si que ahora cambia al cambiar el
origen.
3. Copiar la fórmula en otras filas y ver como se actualiza.
4. Ver cómo la fórmula aparece en la Barra de fórmulas cuando se selecciona la celda. Ver
como se han actualizado las referencias en las columnas sucesivas en las que se aplica la
fórmula.
5. Lo pegamos en la columna de la derecha y vemos como se aplica mal. Corregir la fórmula.

7.1.2 Definición y uso de nombres de rangos

52
1. Creamos un rango con el total de una columna, esto se hace seleccionando las celdas y
presionando Definir Nombre (en la ficha de Fórmulas) después (1). Comprobamos que el
rango se ha definido correctamente con el Administrador de Nombres (2).
2. Crear otros dos rangos con los totales del barrio para hombres y mujeres, respectivamente.
Definir fórmulas que sumen columnas con rangos, tanto del tipo SUMA(G8:G28) como
SUMA(TotalPalacio), empleando directamente el nombre del rango.
3. Pegar en otros barrios las sumas por columnas y ver como NO se mantienen las referencias,
en caso de que se usen rangos para ello.

7.1.3 Precedencia de Operadores en Fórmulas

Precedencia indica el orden en el que se ejecutan los cálculos en una fórmula si esta contiene
varios operadores. En este caso, primero se realizan las operaciones entre paréntesis, después
se aplica la lista de prioridad de operadores, y por último, se asigna la prioridad de izquierda a
derecha.

1. Ver la Precedencia (prioridad) de operadores, calculando: 5*4+6, 5*(4+6), 3/3^2, (3/3)^2,

2^2^3 .

7.1.4 Herramienta de Autocompletar Fórmulas

La función Autocompletar fórmulas detecta fácilmente las funciones que se desean emplear y
proporciona ayuda para completar los argumentos necesarios para obtener la fórmula correcta.

1. Comprobar en Opciones de Excel, Avanzadas, Fórmulas, Autocompletar Fórmulas, que está

activada la opción.
2. Hacer una suma mostrando como se despliega la función de Autocompletar (1). Hacer doble
clic para seleccionar el operador. Con ALT+ flecha abajo se elimina
3. Ver la etiqueta inteligente de Opciones de Autocorrección que aparece (2). Nos va a sugerir
añadir adyacentes, si no los hemos seleccionado todos. Si lo hacemos sobre una tabla, nos
puede decir de colocar la suma en una fila de totales de la tabla.

53
7.2 Referencias de celda en las fórmulas. Referencias relativas,
absolutas y mixtas. Referencias Circulares y Control de Cálculo.

7.2.1 Referencias Relativas, Absolutas y Mixtas:

Las referencias pueden ser relativas (A1), absolutas ($A$1) o mixtas ($A1 o A$1). El símbolo de
dólar indica que la referencia es absoluta y que no debe ser modificada en ningún caso.
Inicialmente, las fórmulas darán el mismo resultado, independientemente de cómo sean las
referencias. La diferencia radica al copiar esas fórmulas en otra celda. Vamos a comprobarlo con
el siguiente ejercicio:

1. Ponemos 3 números en una fila y hacemos una suma relativa.

2. Copiamos y pegamos la fórmula y vemos que la referencia se actualiza de manera incorrecta.
3. Hacemos que la fórmula sea totalmente absoluta y vemos que se mantiene.
4. Finalmente hacemos que sea mixta y vemos que pasa al copiarla en otra fila u otra columna.

Ahora vamos a trabajar con [Link]

1. Convertimos el primer rango en tabla. Añadimos 3 columnas nuevas a la tabla.

2. En la primera calculamos el total de Hombres y Mujeres en cada rango de edad con la
función Autosuma de la Biblioteca de Funciones. En la segunda el porcentaje de hombres
sobre el total en cada rango de edad. En la tercer el porcentaje de cada rango de edad sobre
el total del barrio. En este segundo caso conviene usar referencias absolutas (sobre la celda
total). Vemos como al hacerlo sobre tablas se completan las columnas automáticamente
aplicando las fórmulas.
3. Hacer de nuevo el cálculo empleando referencias a columnas: [@Hombres]/[@Total]
4. Introducimos algún valor en otra hoja del libro y vemos cómo podemos hacer referencias a
otras hojas, de tipo =Hoja2!B2

7.2.2 Referencias Circulares y Control de Cálculo:

Se denomina referencia circular al hecho de que una fórmula utilice la celda que la contiene
como uno de sus parámetros, ya sea de manera directa o indirecta. Normalmente produce un
error en Excel, aunque puede ser desactivado y empleado como elemento avanzado de cálculo.

54
Vamos a hacer una prueba de cálculo iterativo.

1. Definimos una referencia circular en cualquier posición de la tabla para ver como la
herramienta devuelve un error (1).
2. Activamos el cálculo iterativo en la categoría Fórmulas del cuadro de Opciones de Excel (2).
Fijar el número máximo de iteraciones a uno. ¿Qué pasa cuando se actualiza el valor de la
celda referenciada? Fijarse en cómo se actualiza al cambiar cualquier otra celda.
3. En el mismo menú, pasar de cálculo de libro automático a Manual (3). Ver como es necesario
pulsar F9 o el comando Calcular Ahora para que se actualicen las fórmulas del libro.
4. Acceder a las Opciones de Cálculo también en la ficha Fórmulas (4).

7.3 Auditoria de fórmulas y comprobación de errores

En este apartado se trabajará con las posibilidades de la ficha de auditoría de Fórmulas del menú
de fórmulas.

55
Se dice que una celda es precedente si a ella se refieren las fórmulas de otras celdas. Las celdas
dependientes son aquellas que se refieren a otras celdas.

Trabajaremos sobre el mismo fichero [Link]

1. Se busca mostrar el uso de las herramientas Rastrear Precedentes y Rastrear dependientes,

del grupo Auditoría de Fórmulas (1). Activamos ambas opciones en una de las fórmulas. Las
flechas que introducen esas herramientas se eliminan con Quitar flechas (2) (3). Estas
herramientas permiten comprobar fórmulas de la hoja de cálculo. Activar Mostrar Fórmulas
para comprobarlo.
2. Probamos a Quitar un solo nivel de precedentes, cuando anidamos dos.
3. La ventana de inspección (4) permite visualizar la fórmula o fórmula de una misma hoja de
cálculo, con el fin de facilitar su revisión, control y confirmación de su cálculo. Se trata de
seleccionar un par de celdas en la Ventana de Inspección y ver cómo se van actualizando sus
valores.

La herramienta Comprobación de errores se utiliza para localizar e identificar los errores que
pueden cometerse al introducir fórmulas. Excel nos alerta así de posibles errores, aunque no es
necesario hacerle caso.

1. Forzar una división por cero en la tabla, por ejemplo, poniendo el valor de total a cero. (5)
2. Abrir en Auditoría de Fórmulas la herramienta de Comprobación de errores (6) para
obtener más explicaciones.
3. Ver la ayuda sobre el error y Mostrar los pasos de cálculo.
4. En el menú inteligente del error también se puede omitir. Una vez omitido, podemos
comprobar que Excel no detecta más errores.
5. En Opciones de Excel, Fórmulas, tenemos un botón que permite Presionar Reestablecer
errores Omitidos
6. Ver cómo funciona Rastrear Error, en el menú emergente de Comprobación de Errores.

56
7.4 Insertar funciones en Excel. - Funciones disponibles.
Combinación de funciones
Las funciones son fórmulas ya definidas en Excel que realizan cálculos usando valores de la hoja
como argumentos. Empezaremos por comprobar el uso del cuadro de diálogo Insertar Función.

Este ejercicio lo haremos con [Link] y con [Link]

1. Abrimos Insertar Función en la ficha de Biblioteca de Funciones (1), del menú de fórmulas.
También se puede abrir desde el icono fx de la barra de fórmulas (2), así como con
Mayúsculas+F3. Buscamos suma, lo analizamos un poco y sumamos una de las columnas de
la tabla.
2. En el cuadro Insertar Función (3), repasamos las que tienen que ver con multiplicar
números, las de tipo fecha y hora… Dentro de estas, abrir la de Fecha, que representa la
fecha en código de Excel.
3. Calcular el promedio de otra columna. Si lo hacemos sobre la de horas de utilización,
podemos primero promediar todo el rango (seleccionando con la teca¡la MAYUSCULAS) y
después, seleccionar celda a celda con Ctrl. Ver la ayuda de la función.
4. Destacar que todas las fórmulas se actualizan de manera automática. Copiar la fórmula que
incluye la función promedio.
5. Usar la función matemática Potencia, para calcular la potencia 5 de Pi(), la función
redondear, para calcular cierto número de decimales a uno de los resultados de la suma
anterior, la función Aleatorio. Calcular la longitud de una circunferencia de radio 5.

57
6. Funciones del comando Autosuma - que son las consideradas más habituales. Vamos
aplicando una tras otra en una columna. Sumamos las fuentes que se pueden considerar
como renovables. Ver, de todas las renovables, cual aporta más.
7. Functiones de Texto - Utilizar la función Código, de las funciones de Texto, que devuelve el
código ASCII de la letra introducida. Usar la función Mayúsculas, aplicada sobre una celda
que tenga como contenido hola. Usar la función Reemplazar, con la que es posible
reemplazar caracteres de texto, para cambiar provisión por previsión.
8. Funciones Lógicas – Usar la función Y, para decidir, para cada alumno de [Link],
tiene el examen y la nota final como aprobados. Esto se escribe como =Y(M2 >=5;K2 >= 5).
Utilizar la función SI para que se escriba en la columna de la derecha Suspenso o aprobado,
según la condición anterior sea verdadera o falsa. (=SI(N2;"Aprobado";"Suspenso"). Usar
[Link] para contar los aprobados y [Link] para ver la nota media de los aprobados
y de los suspensos.

7.5 Rangos Tridimensionales (3D)

Excel permite extender las fórmulas a más de una hoja, dentro de un mismo libro. A este tipo
de rangos de aplicación se les denomina Rangos 3D.

Abrimos el libro [Link]. Vamos a sumar alguno de los campos de los

índices de producción industrial a lo largo de las distintas hojas que forman el libro. Para ello,
tenemos que poner expresiones que extiendan el rango de hojas, como se muestra en (1). El
rango se puede generar también seleccionando el primer elemento a sumar y el último,
manteniendo la tecla Mayúsculas pulsada. Como vemos en (2), también se pueden seleccionar
rangos de celdas, creando bloques 3D.

Los rangos 3D no se pueden aplicar a todas las funciones. Algunas de las que los aceptan son
SUMA, PROMEDIO, CONTAR, MAX, MIN, PRODUCTO…

58
Ejercicio de Repaso 3
Dada la lista de Proveedores y productos alimentarios que se proporciona en el Fichero
[Link], se pide:

a) Completar la columna de ¿En Stock?, de tal manera que en cada celda aparezca Si o
No, dependiendo de si hay Unidades en Existencia del producto o no.
b) Completar la columna DiasDesdePedido, con los días que han transcurrido (hasta la
fecha de hoy), desde que hicimos el último pedido.
c) Generar una matriz de datos que indique, para cada Proveedor (A, B, C…), el número
de productos distintos que tenemos en catálogo. Completarlo en la hoja Resumen.
d) Obtenemos información similar a la descrita en c), empleando Subtotales y las
opciones de ordenación de datos.
e) Obtenemos información similar a la descrita en c), empleando la herramienta Filtro
Avanzado. Lo copiamos en una serie de tablas nueva.
f) Generar una tabla en la que se indiquen, para cada tipo de producto (Bebidas,
Condimentos, Frutas/Verduras…), el número total de unidades en stock
g) Obtenemos información similar a la descrita en f), empleando Subtotales y las
opciones de ordenación.
h) Aplicamos la herramienta Filtro Avanzado, para obtener información similar. Lo
copiamos en una tabla nueva.
i) Empleando la Función BUSCARV, vamos a identificar qué proveedor distribuye un
determinado producto, primero si lo mentemos por su ID, y en segundo lugar, si se
introduce por su nombre.
j) Sobre las funciones anteriores, vamos a añadir un control más. Si el valor introducido
en uno de los dos campos no se encuentra en la tabla, devolveremos el texto:
“Producto no Encontrado”.
k) Emplear las herramientas de bloquear / ocultar las celdas para que el usuario no
pueda modificar ni ver cómo se computan los campos de En Stock ni de
DiasDesdePedido, pero que sí pueda modificar las unidades de stock o la fecha de
recepción de los mismos.
l) Añadir en la columna AVISOS el mensaje “No quedan unidades de [Poner aquí el
nombre del producto] en stock”. Si hay stock, no debe aparecer ningún mensaje de
aviso.
m) Añadir en la columna HacerPedido el mensaje “Hacer un pedido de [Poner aquí el
nombre del producto] en stock”. Si hay stock, no debe aparecer ningún mensaje de
aviso. El mensaje debe tener un
n) Completar el campo de Producto Elegido al Azar, elegido con ALEATORIO de entre los
productos de la hoja. ¿Cómo harías para que el valor se calcule sólo cuando tu
quieres?

59
Módulo 8: Presentar datos de forma visual.
Los gráficos son fundamentales para mostrar los datos almacenados en la tabla de una manera
más agradable y más fácil de entender. En este módulo abordamos como crear, editar y
personalizar gráfico. Como crear plantillas personalizadas para gráficos así como el trabajo con
una herramienta nueva de Excel denominada mini-gráficos.

8.1 Crear gráficos basados en datos de la hoja de cálculo. Tipos de

Gráficos.
Abrimos la hoja Temperaturas_Madrid_2017.xlsx

8.1.1 Crear Gráficos

1. Seleccionamos los datos presentes en la hoja. En la ficha de Insertar, seleccionamos Gráficos

recomendados (1), del grupo Gráficos. Esta herramienta nos propone un tipo de gráfico u
otro según el tipo de datos que vayamos a representar.
2. Seleccionamos la opción de gráfico de Columna Agrupada. Los datos que se muestran
aparecen sobre la tabla rodeados de un marco azul en la hoja original, el eje horizontal está
enmarcado en lila y el vertical en rojo (2).
3. Tirando de esos marcos se puede cambiar los datos. De esta manera, vamos a mostrar sólo
la temperatura media. Por otro lado, mostraremos sólo la temperatura del verano.

60
4. Observa como al seleccionar el gráfico aparecen dos fichas contextuales específicas de
Herramientas de Gráficos: Diseño y Formato. Ahora vamos a volver a hacer que la gráfica
represente todos los datos de la tabla. En la Sub-ficha Diseño, de Herramientas de gráficos
(4), seleccionamos la opción Seleccionar Datos (5).
5. En este cuadro vamos a definir de nuevo el origen de los datos. Podemos Agregar o
Modificar alguna de las Series. Seleccionamos el Nombre de la serie, en este caso, el
encabezado de cada columna que queremos representar, y los valores de la serie. Como
valores de la serie seleccionamos toda la columna. Podemos cambiar también el orden de
las Series.
6. Vemos los efectos que los cambios han tenido en el gráfico.

8.1.2 Tipos de Gráficos

Vamos a modificar el gráfico para probar los siguientes tipos de gráficos recomendados,
empleando los mismos datos: gráfico de líneas, XY (Dispersión). Vemos el resultado. En lugar
de usar la herramienta de gráficos recomendados, si tenemos claro el tipo de gráfico que
queremos, podemos seleccionarlo directamente. En este caso, la vista previa del gráfico también
resulta muy útil.

61
62
8.2 Edición y personalización de un gráfico existente. Añadir y
eliminar series de datos.

8.2.1 Editar Gráfico

En este ejercicio vamos a ver cómo trabajar con la apariencia de los gráficos, buscando que estos
tengan una imagen más profesional. Esto se consigue trabajando con las sub-pestañas de Diseño
y Formato que aparecen al seleccionar un gráfico. Con ellas podemos modificar cualquier
componente del gráfico.

63
Vamos a seguir trabajando con el fichero de temperaturas del apartado anterior.

1. Cambiamos el estilo del gráfico, pulsando el botón de punta de flecha, en la galería Estilos
de diseño, sub-ficha de Diseño. Seleccionamos el Estilo 8. (El último de la primera fila)
2. Pulsando el botón flotante + a la derecha del gráfico, activamos la flecha de Leyenda, para
modificar su posición de forma que ésta se encuentre a la derecha del gráfico (1). Las mismas
opciones de control de los elementos del gráfico las tenemos en la sub-ficha de Diseño,
Agregar Elementos al Gráfico.
3. En el mismo panel modificamos el nombre del gráfico, sus etiquetas, ejes, leyenda, línea de
tendencia… Practicamos con todas estas opciones.
4. Añadimos un fondo al gráfico, desde el panel Formato del área de gráfico (2), al que se
accede desde el iniciador de cuadro de diálogo del grupo Estilos de Forma, de la sub-ficha
Formato del gráfico. Seleccionamos un relleno con imagen o textura. En el mismo menú
seleccionamos de entre las texturas disponibles, una que nos guste, preferentemente,
oscura.
5. En la sub-ficha Formato, en Selección Actual (3), seleccionamos la opción Leyenda. Una vez
seleccionado el elemento (en este caso la leyenda), podemos cambiar su estilo seleccionado
directamente con las opciones de Estilo de Forma, que están en el mismo menú. Vamos a
cambiar de esta manera el relleno de la leyenda. Desde el mismo grupo de herramientas le
añadimos un efecto bisel.
6. Ahora cambiamos los títulos de los ejes del gráfico. El titulo vertical ponemos Grados y en el
horizontal, Meses.

64
7. En el botón flotante + añadimos el título de gráfico. En el botón de punta de flecha que
aparece al lado de Título de gráfico podemos seleccionar su posición. Ponemos uno
apropiado para nuestro ejemplo.
8. Nos aseguramos que en la sub-pestaña Formato esté seleccionado el Título de Gráfico.
Entonces aplicamos un Estilo de WordArt que nos guste (preferentemente con fondo claro)

8.2.2 Cambiar el tipo de Gráfico

1. Una vez seleccionado el gráfico, vamos a volver a cambiar el tipo de gráfico. Esto lo hacemos
en la sub-ficha Diseño, presionando el botón Cambiar tipo de gráfico, del grupo Tipo.
2. Convertimos nuestro gráfico al tipo Líneas 3D, de la categoría Línea. Comprobamos cómo
cambia el tipo de gráfico pero no el formato que acabamos de aplicar.
3. Seleccionamos las líneas horizontales de división y presionamos Aplicar Formato a la
Selección, también en el menú de Selección Actual. De esta manera se hable el panel lateral
de Dar formato a las líneas de división secundarias. Cambiamos el color
4. Añadiremos líneas principales verticales, mediante el icono flotante + (Elementos de
gráfico), seleccionamos Líneas de la cuadrícula, y activamos Vertical principal primario.
5. Seleccionar entre los elementos del gráfico el plano posterior. Es el que se muestra por
debajo de la cuadrícula. En el panel de formato lateral aparece ahora el contenido de
Formato de plano lateral. Vamos a elegir un relleno sólido, con el color que queramos.
Jugamos además con la trasparencia.
6. Pulsamos sobre alguno de los nombres de los rótulos horizontales (los de los meses) y vemos
como el panel pasa a ser Dar Formato al eje. Establecemos un fondo para los rótulos.
Probamos otras opciones. Evaluamos las opciones que aparecen en los otros dos iconos del
panel: Dar formato al eje y opciones de eje.

65
8.2.3 Substituir y Eliminar Datos
Los gráficos se actualizan de manera automática al modificar los valores en la tabla
correspondiente, así como al eliminar una fila o columna entera o los datos de parte de la tabla,
sin eliminar ninguna fila o columna.

1. Comprobamos que sucede al modificar valores de la tabla, poniendo un valor negativo en

una de las celdas.
2. Eliminamos una fila y posteriormente una columna. ¿Qué sucede con los mensajes de error?
3. Eliminamos algunos datos de la tabla sin eliminar la fila ni la columna al a que pertenecían.

8.3 Crear una plantilla de gráficos.

Excel nos permite generar una plantilla a partir de un gráfico. Para ello tan solo tenemos que
pulsar sobre el gráfico y seleccionar Guardar Como Plantilla (1). Es importante guardarla en la
ubicación que nos propone Excel por defecto. Para aplicar la plantilla, tan sólo tenemos que
seleccionar los datos de interés y vamos a Insertar Gráfico Recomendado. De ahí, seleccionamos
Todos los Gráficos, y en la segunda de las opciones, que es plantilla, tenemos la plantilla que
acabamos de guardar (2).

Vamos a comprobar el funcionamiento exportando una plantilla del gráfico que acabamos de
generar, a otro fichero de [Link].

8.4 Crear y editar mini-gráficos en una celda. Análisis instantáneo

de datos.
La función mini-gráficos permite incrustar gráficos en una celda de dimensiones reducidas, que
reproduzca la tendencia principal de los datos. Esto ayuda mucho a la rápida interpretación de
los datos.

66
8.4.1 Crear Mini-gráficos en una celda

1. Abrimos el fichero Temperaturas_Madrid_2017.xlsx

2. Creamos un mini-gráfico seleccionando una columna del dichero. Después, en la ficha de
Insertar, seleccionamos el tipo de mini-gráfico que nos interesa: lineal, de columnas y de
ganancias y pérdidas (2). En cualquiera de los casos tenemos que indicar en qué posición se
ubicará el mini-gráfico, y si no se ha hecho antes, en que rango se encuentran los valores a
representar.
3. Insertamos un mini-gráfico de cada tipo, con cada una de las columnas de la tabla.
4. Vemos como aparece le menú contextual de Herramientas para mini-gráficos (3). Este menú
nos permite poner a nuestro gusto el aspecto del mini-gráfico.
5. Comprobamos que es posible escribir texto encima de los mini-gráficos, ya que estos actúan
como fondo de la celda.
6. Ahora seleccionamos varias columnas a la vez como origen de los datos y el mismo número
de celdas como destino. Vemos como se generan los 3 mini-gráficos simultáneamente.

8.4.2 Editar Mini-gráficos

Una vez creado el mini-gráfico, es posible cambiar el tipo de mini-gráfico, cambiar su formato o
destacar ciertos puntos en él. Todo esto se realiza desde la correspondiente ficha contextual.

1. Destacamos el punto más alto del mini-gráfico. Vemos como se representa en el gráfico
de columnas y en el de líneas.

67
 2. Cambiamos el color del mini-gráfico, con la punta de flecha del comando Color de mini-
gráfico, situado junto a la galería de estilos.
3. Cambiamos también el color del punto más alto: Haciendo clic en la punta de flecha del
comando Color del marcador (1) (2), pulsamos la opción Punto alto y elegimos uno de los
colores de la paleta. Probamos con opciones para otros marcadores, así como para mini-
gráficos de otro tipo.

8.4.3 Formato Condicional

El formato condicional de celdas permite analizar de una manera muy visual los datos de una
hoja. Permite marcar excepciones o tendencias, que son representadas de una manera muy
clara con degradados de color, barras de datos o iconos.

La herramienta permite que se definan las reglas condicionales en función de cada problema y
asociar un formato determinado a dicha regla.

68
Abrimos el fichero Excel [Link]

1. Vamos a destacar en el fichero los alumnos aprobados. Para ello empezamos por seleccionar
el rango de celdas que queremos analizar. Pulsamos el botón Formato Condicional del grupo
Estilo y ahí la opción mayor que (1).
2. En el cuadro Es mayor que (3) tenemos que definir el valor usado como límite para la
condición y el formato que se generará. Vamos a definir que las celdas mayores (o iguales a
5) tengan un Relleno verde con texto verde oscuro. De la misma manera, haremos que las
celdas que tengan un valor menor que 5 tengan un relleno rojo claro con texto en rojo
oscuro.
3. En relación con la nota de los trabajos, vamos a analizarlos según una escala en la que las
notas más altas tomen un valor más claro, y las más bajas, un valor más oscuro. Para ello
seleccionamos en estilo la opción de escalas de color (2), y dentro de estas, la regla de
colores que mejor venga.
4. Ahora vamos a coger las notas de la PEC y resaltaremos empleando las Reglas superiores e
inferiores (4), los alumnos que se encuentran entre el 20% con mejores notas. Vemos cómo
se pueden definir formatos personalizados en las reglas.
5. Ahora vamos a borrar todas las reglas de formato personalizadas que hemos creado. Esto
se hace con la opción de Borrar reglas del mismo menú.
6. Ahora vamos a crear una Nueva regla, empleando esta opción del menú. Se abre el cuadro
de Nueva regla de formato (5). En el que se indica el tipo de regla que vamos a crear y el
formato que le vamos a dar a las celdas. Vamos a utilizar iconos indicadores para las celdas
que la cumplan una determinada condición. En este caso usamos los iconos triangulares
para clasificar en tres categorías a los alumnos en función de su rendimiento.
7. Por último abrimos la opción de Administrar Reglas para ver las que están definidas.
8. Por último, estableceremos un formato condicional en función de una fórmula: Pondremos
un fondo determinado sobre los nombres de los alumnos que estén aprobados en la nota
final, a pesar de tener menos de un 5 en el examen (6).

69
8.4.4 El nuevo análisis rápido
Excel 2016 ofrece la posibilidad de realizar un análisis de datos instantáneos que permite pre-
visualizar y convertir los propios datos en un gráfico, en un mini-gráfico o en una tabla, así como
aplicar un formato condicional. Todo esto de una manera muy directa.

Para este ejercicio volvemos al fichero de temperaturas…

1. Presionamos en el icono de Análisis Rápido (1) que aparece en la esquina inferior derecha
del rango seleccionado. Comprobamos que desde este menú se puede obtener una vista
previa y aplicar los formatos condicionales evaluados en el apartado anterior, así como
generar mini-graficos o gráficos de una forma muy directa.
2. Una vez abierto el análisis rápido de datos, Seleccionamos la opción Barra de Datos (2).
Vemos como esta se superpone a los valores de las celdas.
3. También podemos activar la función de cálculos totales. Vamos a calcular los promedios por
columnas.
4. Finalmente vamos a usar este mismo menú para convertir los datos en una tabla.

8.5 Insertar y editar gráficos SmartArt.

Por último vamos a tratar los gráficos SmartArt. Se trata de una representación visual que se
puede crear de una forma rápida y sencilla para transmitir de una forma eficaz mensajes o ideas.
Se trata de una funcionalidad compartida con Excel, Outlook, PowerPoint y Word, dentro de
Office 2016.

1. Vamos a abrir la hoja [Link] y vamos a insertar un SmartArt con el que

esquematizar las distintas titulaciones de la escuela en función de su tipo.
2. Una vez insertado aparecerán las sub-fichas de Diseño y Formato, dentro de la ficha
contextual de SmartArt.
3. Modificamos el esquema básico mediante cambios bien en el panel de texto como sobre el
propio diagrama.
4. Si fuese necesario, vemos como añadir viñetas al diagrama.

70
5. Por último lo editamos, cambiando los colores, el relleno, o las formas que componen el
diagrama, desde el grupo de herramientas de Estilos SmartArt.

71
Ejercicio de Repaso 4
Con la información recogida en el fichero [Link], se pide generar dos gráficas
como las que se muestran aquí:

72
Módulo 9: Trabajo con Tablas dinámicas
Las tablas dinámicas facilitan el resumir, analizar, explorar y presentar los datos. Se trata de una
herramienta esencial con la que podemos llegar a trabajar como si se tratase de una base de
datos, en la que sus filas son registros de la misma. Las tablas dinámicas son muy flexibles y se
pueden ajustar rápidamente en función de cómo se tengan que mostrar los resultados. También
puede crear gráficos dinámicos a partir de tablas dinámicas que se actualicen automáticamente
al hacerlo las tablas dinámicas.

9.1 Creación y modificación de tablas dinámicas. Cálculos en

tablas dinámicas.

1. Crear una nueva tabla dinámica con los datos del fichero de
[Link]. Esto se realiza en la herramienta Tabla dinámica del
grupo Tablas de la ficha Insertar (1). Insertamos la tabla dinámica en una Nueva hoja de
cálculo, seleccionando el rango en el que se encuentran los datos de entrada.

73
 Debes tener en cuenta para analizar los datos con la herramienta de tabla dinámica es
que deben tener una cabecera que identifique qué atributo estás representando en
cada columna
2. Al insertar la tabla dinámica se activa la ficha contextual Herramientas de Tabla
Dinámica (2) y se abre el panel lateral de Lista de campos de tabla dinámica (3).
3. En el panel lateral tenemos que decidir la distribución de las distintas secciones. Tanto
como filas, como columnas o como Valores. Esta es la organización de la tabla dinámica.
• Filtros de informe: permitirá filtrar la tabla entera seleccionando uno o varios
elementos de la lista del filtro que se hayan aplicado.
• Columnas: permitirá organizar la información por columnas
• Filas: permite organizar la información por filas
• Valores: serán los valores de cálculo. Se pueden visualizar los valores como suma,
máximo, media, contar valores…
4. En este caso vamos a hacer que el informe ofrezca el total de unidades (y el %) así como
el volumen de negocio en euros (y el %) de cada vendedor. Utilizamos la opción de
Formato de Número para poner el valor del campo en euros.
5. Creamos otra tabla que represente el número de unidades que se han vendido de cada
tipo de vehículo. Queremos saber también, para cada tipo de vehículo, el máximo de
unidades que se han vendido en una sola operación (calculamos el valor máximo del
campo unidades) Añadimos una columna que muestre el promedio del importe de cada
operación, según el tipo de vehículo.
6. Ahora vamos a crear una tabla que muestre, para cada vendedor y para cada tipo de
coche, las unidades vendidas. Para ello ponemos vendedor y modelo como Filas. ¿Qué
pasa si cambiamos el orden de ambos campos en este punto?
7. Creamos una nueva tabla dinámica que represente las unidades vendidas de cada
vehículo en cada provincia. Para ello ponemos el Modelo en la columna, la Provincia
como Fila y en valores, la Suma de Unidades vendidas.
8. Vamos a ver, en una nueva tabla, el número de operaciones por cada mes. Sobre esta
tabla, empleando la opción de Herramientas de Tabla Dinámica, Analizar, empleamos la
herramienta de Agrupar para ir haciendo categorías de datos por mes. Vemos también
como en diseño podemos habilitar y deshabilitar totales y sub-totales.
9. Vemos a demás en la sub-ficha de diseño como podemos aplicar distintos diseños a las
tablas dinámicas
10. Por último, observamos como si cambiamos algunos datos en su ubicación original, al
aplicar Actualizar, en la sub-ficha de Analizar, de las Herramientas de Tabla Dinámica.
11. Excel nos ofrece también la opción de recomendar una Tabla Dinámica. Este es también
un buen punto de partida para empezar a trabajar sobre el tema. La herramienta se
activa en la ficha Insertar, Tablas Dinámicas.

9.2 Asignación de filtros a tablas dinámicas. Segmentación de

Datos y Análisis de Tiempo.
Además de poder aplicar filtros al modo tradicional, como se hace con las tablas no dinámicas,
las tablas dinámicas permiten la segmentación de los datos. Esta opción se activa con la
herramienta Insertar Segmentación de datos (1), del grupo Filtrar de la sub-ficha Analizar. Una
vez activado, tenemos que seleccionar las casillas correspondientes a los campos de la tabla

74
dinámica para los que queremos crear una segmentación. Vemos que aparecen uno o varios
marcos (dependiendo del número de campos seleccionados) que pueden ser arrastrados al lado
de la tabla (2). También podemos comprobar como seleccionado el cuadro de segmentación se
activa una ficha contextual correspondiente a las Opciones de la Segmentación de Datos. Entre
otras opciones, vemos cómo se puede poner a nuestro gusto el formato del cuadro de
segmentación de datos (3).

En el caso de tener datos de fechas (como en el caso del ejemplo anterior), podemos segmentar
los datos por éstas con una herramienta especial denominada Insertar escala de tiempo (4).
Vemos como añadirlo en una de nuestras tablas y como adaptarlo para filtrar por día, mes,
trimestre o año.

9.3 Campos Calculados en tablas dinámicas

Las tablas dinámicas nos permiten hacer uso de campos calculados, que son columnas que
obtienen su valor de la operación realizada entre algunas de las otras columnas existentes en la
tabla dinámica.

Dentro de la ficha de Herramientas de tabla dinámica, Analizar, tenemos la herramienta

Cálculos, elementos y conjuntos. Ahí seleccionamos la opción de Campo calculado. Esto abre
un cuadro de diálogo Insertar campo calculado (1). Le daremos un nombre al nuevo campo y la
fórmula que obtenemos para calcular el nuevo campo.

75
 1. En la tabla que mida las ventas por comercial, calculamos el dinero que habrán recibido
por las comisiones de ventas, si llevan el 0,5% del volumen total.

9.4 Crear informes de tablas dinámicas. Gráficos Dinámicos.

De la misma manera que sucede con las tablas dinámicas, es posible generar gráficas dinámicas
que permiten realizar un análisis de datos interactivo y que permiten visualizar los datos de
resumen para facilitar comparaciones, tendencias y patrones.

1. Sobre las tablas dinámicas creadas en el ejercicio anterior, vamos a crear un Gráfico
dinámico, del grupo Herramientas.
2. Se abre el cuadro de Insertar gráfico, en el que elegimos el gráfico que se insertará. En
este caso, vamos a representar en un gráfico circular las cantidades de modelos
vendidos de cada una de las marcas. Vemos que podemos ajustar el Estilo de diseño del
gráfico.
3. Vemos cómo se pueden aplicar filtros sobre el propio gráfico. También se pueden
eliminar series del gráfico, con el botón Lista de campo del grupo Mostrar u ocultar, de
la sub-ficha Analizar de Herramientas del gráfico dinámico.
4. Comprobamos como se actualiza el gráfico dinámico al modificar los datos.

Comprobamos al final que es posible insertar un gráfico dinámico directamente de los datos sin
haber creado previamente el informe de tabla dinámica. Esto se puede hacer desde la ficha
Insertar, pulsando el botón de Gráfico dinámico, y seleccionando Gráfico dinámico y tabla
dinámica.

76
Ejercicio de Repaso 5
Dada la lista de Proveedores y productos alimentarios que se proporciona en el Fichero
[Link], se pide extraer la siguiente información mediante tablas dinámicas:

a) Generar una tabla que indique, para cada Proveedor (A, B, C…), el número de productos
distintos que tenemos en catálogo. Completarlo en la hoja Resumen.

b) Generar una tabla en la que se indiquen, para cada tipo de producto (Bebidas, Condimentos,
Frutas/Verduras…), el número total de unidades en stock.

c) Generar gráficos dinámicos que reflejen la información anterior.

77
Módulo 10: Imprimir, Revisar y Compartir
documentos.
10.1 Administrar comentarios. Control de cambios.
La inserción de comentarios puede resultar muy útil a modo de recordatorio, como notificación
o para contener instrucciones, en caso de que terceros utilicen nuestra hoja. Un comentario en
Excel no ocupa una celda en sí. Tan sólo aparecerá un triángulo rojo en su esquina superior para
mostrar que la celda contiene un comentario (1). El comentario se mostrará al situar el puntero
sobre la celda afectada o mediante la opción de Mostrar todos, en la ficha Revisar, grupo
Comentarios (2). Los comentarios se insertan con la opción Nuevo Comentario, en la ficha
Revisar, así como en el menú contextual de la celda en la que lo queremos introducir.

El nuevo comentario también se puede introducir con la opción Mayúsculas + F2. También
podemos navegar por todos los comentarios (Anterior y Siguiente) y modificar cualquiera de
ellos, mediante el comando Modificar Comentarios. También es posible cambiar el formato del
comentario. Una vez seleccionado, en el menú contextual del comentario podemos abrir la
opción de Formato de comentario (3).

78
 1. Vamos a añadir en el fichero [Link] un comentario sobre dónde debe
introducirse el valor de IVA, así como para indicar que algún producto está
descatalogado. Comprobamos las opciones de modificación y navegación de
comentarios.
2. Habilitamos con el menú contextual la opción de Formato de Comentario, y cambiamos
el tipo de letra y el color del cuadro. Para que salga esta opción es necesario hacer clic
sobre el marco del comentario, no sobre su texto.
3. Por último, los eliminamos con Eliminar comentario. Así mismo, el comentario puede
eliminarse mediante el menú contextual.

10.2 Revisar la ortografía. Opciones de autocorrección.

Excel permite realizar la revisión ortográfica de un libro empleando la función Ortografía del
grupo de herramientas Revisión. Al ejecutar esta función, la herramienta revisa el libro y
muestra un cuadro (Ortografía) con los términos que no ha encontrado en el diccionario.

Empleando los botones Omitir, Omitir todo, Cambiar, Cambiar todas podemos decidir que se
hace ante la detección de la palabra incorrecta. Además, en este mismo cuadro podemos
cambiar el idioma del diccionario. También podemos agregar al diccionario palabras que no
encuentre en él. Esto afectará a todos los programas de Office.

1. Comprobamos cómo funciona la opción en el fichero [Link],

que ha sido especialmente modificado para ello.
2. En Opciones de Excel, Revisión, podemos ver las opciones marcadas por defecto para
la corrección de Ortografía (1).

79
Excel tiene unas opciones de Autocorrección por defecto, que afectan a las correcciones
automáticas que realiza el programa mientras escribimos. Principalmente, de tipo ortográfico.

Desde las opciones de Excel, en la categoría Revisión, se puede seleccionar el cuadro de

Opciones de Autocorrección (2). Ahí podemos ver el tipo de opciones que se pueden seleccionar
(3).

80
 1. Añadimos una palabra con una falta de ortografía, como lapiz, y hacemos que la
herramienta de autocorrección la transforme por lápiz (3).
2. Escribimos en una celda “productos. prueba” y comprobamos la opción de Poner en
mayúscula la primera letra de una oración.
3. Escribimos la palabra “MAyusculas” y comprobamos como corrige la existencia de dos
mayúsculas seguidas.
4. ¿Qué pasa si pretendemos escribir algo después de pág.? En el mismo menú de opciones
vemos las excepciones de autocorrección. Añadimos pág. como excepción para que no
poner letra mayúscula después de la abreviatura. Comprobamos que pasa ahora. (4)
5. Finalmente, vemos en las opciones de Referencia (también en el grupo de Revisión)
como podemos buscar términos en el diccionario de la Real Academia Española de la
Lengua. (5)

10.3 Protección de celdas, hojas y documentos.

10.3.1 Proteger una hoja
En ocasiones necesitamos proteger una hoja de tal manera que un usuario no pueda
modificarla. Esto se consigue con la herramienta Proteger hoja del grupo Cambios, también
en la ficha Revisar (1). Con esta herramienta trabajamos ya anteriormente en el apartado
de Ocultar y bloquear celdas.

81
Al seleccionar la herramienta proteger hoja, aparece un cuadro de diálogo del mismo nombre
(2). En éste podemos seleccionar los permisos que les damos a los otros usuarios de la página,
así como establecer una contraseña de desbloqueo. Comprobamos qué sucede si intentamos
introducir un dato en una celda mientras la protección está activa (3). En la posición donde está
la herramienta de Proteger hoja, está ahora la opción de desprotegerla. Para ello tendremos
que volver a meter la contraseña.

82
Desde la vista backstage podemos proceder también a proteger y/desproteger la hoja. Vemos
que al pulsar la opción de Proteger hoja actual en ese menú se abre de nuevo el mismo cuadro.
En esta ocasión vamos a volver a proteger la hoja pero activando la opción pertinente para
permitir que el usuario pueda aplicar formato a celdas. Comprobamos que ahora, a pesar de
estar protegida, es posible cambiar el formato a las celdas.

10.3.2 Proteger la estructura del libro

Además de proteger una hoja, es posible proteger la estructura del libro, de tal manera que no
se pueda mover, eliminar o añadir hojas, modificar su nombre, pasar de oculto a visible, etc. Un
ejemplo de la utilidad de esta opción es la inclusión en el libro de datos que no se quieren facilitar
al destinatario, pero que se emplean en fórmulas que si son de interés. En este caso podemos
definir los valores privados en una hoja que ocultamos. En la hoja visible podemos utilizar los
datos, e incluso si ocultamos las fórmulas, el usuario no puede ser consciente de cómo se calcula
el resultado.

Otra manera de proteger el libro es marcarlo como final. Esto se realiza también en la misma
vista de backstage que las anteriores). De esa manera, las herramientas de edición y escritura
estarán desactivadas, pudiendo evitar modificaciones no intencionadas del mismo. Sin embargo,
esta protección puede ser eliminada también por cualquiera. Se trata, por lo tanto, de una
herramienta meramente informativa.

10.3.3 Añadir una firma

En el mismo menú de Información de backstage que permite bloquear hojas o el libro, es posible
Cifrar el libro con contraseña para que nadie pueda acceder a él. Para desproteger el libro es
necesario, una vez abierto, en el mismo cuadro de cifrar documento, suprimir el texto recibido
como entrada y darle a Aceptar.

83
Si queremos garantizar la seguridad y la autenticidad del documento, la mejor manera es incluir
en él una firma. Ésta puede ser escrita (mediante insertar, Línea de firma (1)) o digital (2). En
este segundo caso la debe proporcionar un proveedor especializado.

10.4 Recuperar Documentos

Aunque se recomienda ir guardando los cambios que introducimos en nuestras hojas Excel de
manera periódica, puede suceder que el equipo se apague o que debamos reiniciarlo de
repente. Si se da esa situación sin haber guardado antes los cambios introducidos, Excel tiene
una función de recuperación que permite regresar a la versión más reciente.

84
En Opciones de Excel, en la categoría Guardar (1), podemos configurar cada cuanto tiempo
queremos que el programe almacene información de auto-recuperación e indicar el directorio
donde se guardará la información de recuperación.

1. Podemos forzar un cierre repentino de a herramienta con el Administrador de Tareas de

Windows (Ctrl.+Alt+Supr) y forzar la finalización de Excel.
2. Volvemos a abrir Excel y comprobamos que ahora aparece una nueva opción de Mostrar
Archivos Recuperados (2). Si lo seleccionamos, aparecerá a la izquierda el panel de
Recuperación de documentos (3). En este seleccionamos el archivo de interés y haciendo
clic en la punta de flecha que aparece junto al nombre del archivo, podemos decidir abrirlo
o guardar. El nombre con el que aparece el archivo es el mismo que el original, pero con un
término (Autorecuperado) que se le ha añadido.
3. Podemos decidir guardar el archivo sobre-escribiendo el nombre original. Para ello tenemos
que seleccionar la ubicación del fichero original.

10.5 Configurar Página para impresión

Vamos a ver las opciones que tenemos para configurar un libro con la finalidad de imprimirlo.

Excel permite definir áreas de impresión. Se trata de grupos concretos de celdas de una hoja
que suelen imprimirse habitualmente. También permite definir márgenes y otras propiedades
de las hojas. También es posible insertar saltos de página en posiciones determinadas.

1. Abrimos el fichero [Link]

2. Seleccionamos las celdas que queremos imprimir y en Diseño de Página, Área de Impresión,
activamos Establecer Área de Impresión (1).
3. Seleccionamos otra parte del libro y desde ahí, Agregar al área de impresión. A un área de
impresión solo se le pueden añadir celdas adyacentes. Si no lo son se creará un nuevo área
de impresión

85
4. En esta misma ficha de Diseño de Página, tenemos las opciones de Configurar Página,
Ajustar área de Impresión y Opciones de la hoja, todas ellas relacionadas con la impresión.
Vemos las opciones de Configurar página, presionando en su lanzador.
5. En el cuadro de Configurar Página, es posible configurar los márgenes, encabezados y pies
de página. En la ficha de márgenes vemos cómo se pueden definir y como se puede centrar
la página, horizontal y verticalmente. Vemos en la vista previa cual es el efecto de cada una
de estas opciones.
6. Estas mismas opciones están disponibles en la vista Backstage, Imprimir (2). Desde ahí se
puede lanzar también el cuadro de Configurar Página.
7. Podemos ver el efecto del Escalado. Podemos ver como ajustar a una página. En la vista
previa también podemos ver como haciendo clic sobre los iconos de la parte inferior derecha
de la hoja podemos mostrar directamente los márgenes. En esta vista se pueden modificar
directamente, arrastrando los límites (3).
8. Por último, en el menú de configuración (4) podemos elegir que se imprima todo el libro,
que se imprima solo el área seleccionada (el área activa) o imprimir la hoja activa entera,
ignorando el área de impresión. Vemos que sucede en este caso.

86
En ocasiones es necesario dividir una hoja de tal manera que las páginas generadas se ajusten a
nuestras necesidades En la vista de Ver salto de página, dentro de las Vistas del libro, podemos
ver los saltos de página. Los que inserta la herramienta automáticamente aparecen como líneas
azules discontinuas. Podemos arrastrar los límites de las páginas y definir nuevos saltos de
página. Los saltos de página se insertan en el botón saltos del grupo Configurar página. Los saltos
se insertan por encima y a la izquierda de la posición seleccionada. Los saltos manuales (o
cuando modificamos uno automático) se muestran con líneas azul continuas. Cuando hacemos
esto, la escala de la hoja se ajusta automáticamente.

1. Inicialmente, borramos toda el Área de Impresión

2. Vemos la vista de Ver salto de página
3. Arrastramos uno de los salto de página e insertamos otro automáticamente. Vemos cómo
funciona esta herramienta. Vamos insertando saltos de página para definir distintas zona de
la tabla de la manera que nos interesa (1,2,3)
4. Comprobamos cómo funciona la herramienta Quitar salto de página, situándonos sobre
alguno de los que acabamos de incrustar. También vemos que sucede al activar la opción de
Restablecer todos los saltos de página.

87
10.6 Enviar por correo electrónico. Opciones para compartir
ficheros en línea.
Excel permite enviar directamente por correo electrónico el libro con el que estamos trabajando.
Podemos enviarlo directamente como un dato adjunto en el correo, en el mismo formato o
como PDF o XPS. Si se selecciona la opción de enviarlo como PDF, se crea automáticamente una
copia del libro en este formato.

Además de enviarlo por correo electrónico, es posible almacenarlo en alguna ubicación en la

nube y compartirlo después. Por ejemplo, podemos guardar un libro en SkyDrive y permitir que
otros usuarios puedan abrirlo y modificarlo de manera colaborativa (1).

En la ficha de Revisar, el grupo Cambios, podemos ajustar las opciones de Compartir Libro. Para
que varios usuarios puedan modificar el libro a la vez, hay que activar la opción que aparece en
la ficha Modificación (2). Posteriormente, en la ficha de Uso Avanzado (3) podemos ver algunas
opciones más. Entre ellas, podemos asegurarnos que Excel guarda un historial e cambios cada
diez días. También opciones relativas a la activación del mismo libro por parte de más de un
usuario, como por ejemplo que pasa en caso de que ambos usuarios introduzcan cambios
conflictivos.

Una vez guardado en una posición en la nube (en este caso en Skydrive), podemos Invitar a
personas tanto por correo electrónico como enviándole un vínculo de uso compartido (4). Los
vínculos pueden ser tanto de visualización como de edición. De la misma manera puede ser
publicado en redes sociales.

Hay que tener en cuenta que los libros compartidos tienen algunas excepciones. No pueden
tener formato condicional, ni celdas combinadas, ni esquemas, ni tablas de datos, ni informes
de tablas dinámicas…

En la ficha Revisar, pulsando el botón de Control de Cambios del grupo Cambios, podemos
activar la opción Resaltar cambios. Activamos Todos y de Todos (o de algunos usuarios
específicos). De esta manera las celdas modificadas son resaltadas (5). En la misma opción
podemos Aceptar o Rechazar los cambios.

88
89
90
Módulo 11: Herramientas de análisis de datos
en Excel y control de formularios.
Existen tres tipos de herramientas de análisis de hipótesis en Excel: escenarios, tablas de datos
y Buscar objetivo. Tablas de datos y escenarios consisten en el uso de conjuntos de valores de
entrada para calcular resultados dependiendo de ciertas condiciones. Buscar objetivo funciona
de manera totalmente opuesta: utiliza un único resultado en base al cual calcula los valores de
entrada posibles que podrían producir ese resultado.

11.1 Herramientas de Análisis de Datos

11.1.1 Tablas de datos
Las tablas de datos ayudan a explorar un conjunto de resultados posibles, mostrando todos los
resultados en una tabla, dependiendo de los valores de entrada. De esta manera, el uso de tablas
de datos permite examinar fácilmente una variedad de posibilidades de un vistazo. Como es
posible centrarse en sólo una o dos variables, los resultados son fáciles de leer y compartir en
formato de tabla.

Una tabla de datos permite trabajar con hasta dos variables. Si desea analizar más de dos
variables se deben utilizar escenarios en su lugar. Aunque está limitado a sólo una o dos variables
(una para la celda de entrada de fila y otra para la celda de entrada de columna), una tabla de
datos puede incluir tantos valores de variables diferentes como se desee.

Como ejercicio, vamos a calcular una tabla de datos que muestre como varía la cuota mensual
de un préstamo hipotecario de 200000 en función de los años de amortización y la tasa de
interés anual. La fórmula que computa el interés mensual es la siguiente:
PAGO(C1/12;C2*12;200000). (1)

91
Para ello ponemos la fórmula en una posición central (2). A partir de esa posición, en la misma
fila ponemos los años a los que queremos realizar el pago, y en la columna por debajo, la tasa
de interés. Después seleccionamos la herramienta Tabla de Datos (3), dentro de la ficha de
datos. En este caso tenemos que indicar la posición original de las referencias a las celdas de
entrada para la fórmula que pusimos en el paso anterior, y seleccionamos (incluyendo la fila y
columnas) el rango en el que queremos generar la tabla (4).

11.1.2 Análisis de escenarios

Un escenario es un grupo de celdas (hasta 32) que almacenan variables que se pueden aplicar a
los resultados de un modelo. El análisis de escenarios permite calcular cómo cambios en estas
variables afectan al resultado final del problema, resolviendo preguntas del tipo “y si”.

Para su empleo, tenemos que activar la herramienta Administrador de escenarios, que se

despliega en el botón de Análisis de hipótesis, en la ficha de Datos (1). Este botón lanzará el
cuadro de Administrador de escenarios, donde aparece la opción de Agregar un nuevo
escenario.

Un escenario es un conjunto de valores que Excel guarda y puede sustituir automáticamente en

la hoja de cálculo. Puede crear y guardar diferentes grupos de valores como escenarios y, a
continuación, cambiar entre estos escenarios para ver distintos resultados.

Una vez que se han creado todos los escenarios que se necesitan, se puede crear un informe de
resumen de escenario que incorpora información de todos los escenarios.

Escenarios se administran con el Administrador de escenarios Asistente (2) del grupo

de Análisis de hipótesis en la pestaña datos.

92
Vamos a realizar un ejercicio que permita realizar una estimación de el volumen de ahorro
anual en función de distintas variables (3).

1. Agregamos un nuevo escenario. Le damos un nombre, elegimos las celdas cambiantes y

establecemos el modo de protección. Vamos a crear dos escenarios distintos, cada uno
con un valor distinto para la celda D3.
2. Le damos el valor que va a tomar D3.
3. Agregamos un segundo escenario, en este caso, con otro valor de D3.
4. Observamos cómo cambian los valores al cambiar el escenario que Mostramos en el
Administrador de escenarios. Hay que destacar que los escenarios son fácilmente
editables, una vez creados.
5. Pulsamos ahora el botón Resumen, dentro del mismo cuadro de diálogo. Vemos cómo
podemos crear un Resumen del escenario que muestra los valores de las celdas
cambiantes.

11.1.3 Buscar Objetivo

La herramienta Buscar Objetivo permite resolver el problema inverso al anterior. Si conoce el
resultado que se desea obtener de una fórmula, pero no estamos seguros de qué entrada debe
pasársele a la fórmula para obtener dicho valor, se puede usar la herramienta Buscar objetivo
(2). Por ejemplo, suponemos que necesitamos un préstamo. Sabemos la cantidad de dinero que
se desea pedir, en cuanto tiempo se desea terminar de pagar el préstamo y que cuota máxima
nos podemos permitir cada mes. Podemos usar la función Buscar objetivo para determinar qué
tipo de interés es el máximo que nos podemos permitir para poder cumplir con los objetivos del
préstamo (4).

93
Con esta herramienta Excel ajusta automáticamente el valor de una celda para obtener un
resultado determinado en otra. Evidentemente, la casilla donde quiera obtener el resultado ha
de depender directamente o indirectamente de la casilla en la cual se le ajusta el valor. Hay que
tener en cuenta que la casilla que cambia debe contener obligatoriamente un número, no se
puede utilizar una casilla con fórmula.

Se plantea el siguiente ejercicio, de acuerdo con el modelo realizado en el problema anterior.

Sobre mi presupuesto mensual, ¿Cuáles deberían ser mis ingresos mensuales para conseguir un
ahorro de 10000 euros al año?

11.2 Uso de la herramienta Solver.

El Análisis de hipótesis es el proceso de cambiar valores en celdas para ver cómo afectan esos
cambios al resultado de fórmulas de la hoja de cálculo. Para su activación se debe instalar como
complemento, dentro de las Opciones de Excel.

La herramienta Solver tiene similitudes con Buscar Objetivo, y también se utiliza también para
obtener un determinado resultado en una celda. Sin embargo, esta herramienta, permite
establecer más de una celda ajustable. También permite establecer restricciones, de manera
que se indica a Solver que cuando haga los ajustes en las casillas variables, se ha de limitar a las
condiciones establecidas en cada restricción. De la misma manera que en Buscar Objetivo, las
casillas variables han de contener valores numéricos y han de intervenir directa o
indirectamente en la fórmula de la casilla donde se quiera obtener el resultado final.

94
Se plantea el siguiente ejercicio: Un fabricante de bicicletas cuenta con un total de 80 Kg. de
acero y 120 Kg. de aluminio. Con este stock quiere hacer bicicletas de paseo y de montaña que
quiere vender, respectivamente a 2000 y 1500 euros cada una para sacar el máximo beneficio.
Para la de paseo empleará 1 Kg. De acero y 3 Kg. de aluminio, y para la de montaña 2 Kg. de
ambos metales. ¿Cuántas bicicletas de paseo y de montaña deberá fabricar para maximizar las
utilidades?

11.3 Formulario de Datos. Controles de formulario en Excel

Un formulario es un documento diseñado con una estructura estándar y un formato
determinado que hace que sea más fácil capturar, organizar y editar la información. Los
formularios contienen controles, como cuadros o listas desplegables, que pueden que sea más
fácil para las personas que use la hoja de cálculo para escribir o modificar datos

11.3.1 Formulario de Datos

Un formulario de datos permite generar un Formulario de manera automática para escribir o
mostrar una fila completa de información en un intervalo o una tabla sin desplazarse
horizontalmente.

95
Vamos a Trabajar con en fichero [Link].

1. Habilitamos en la cinta de opciones la herramienta Formulario

2. Seleccionamos el rango y algunas filas más, para permitir añadir nuevos elementos.
Hacemos clic en la herramienta y exploramos todas las posibilidades que tiene.
3. Hacemos una búsqueda según diversos criterios. Por ejemplo, buscamos todos los
elementos de un Proveedor determinado (*E)
4. Realizamos distintas pruebas como añadir un nuevo elemento, lo eliminamos…

11.3.1 Añadir Controles de Formulario

Vamos a añadir controles para las hojas de diálogo que son útiles para seleccionar elementos de
una lista, facilitar la entrada de datos y mejorar su aspecto. Muchos de estos controles también
pueden vincularse con celdas de la hoja de cálculo.

Los controles de formulario son controles originales que son compatibles con versiones
anteriores de Excel. En muchos casos se combinarán con la ejecución de una macro, ante la
acción del usuario.

Añadimos los siguientes elementos:

1.- Cuadros de Lista, para facilitar la selección por parte del usuario de un elemento de una lista.

2. Cuadros combinados. Combina un cuadro de texto con un cuadro de lista para crear un
cuadro de lista desplegable

Definimos tanto un cuadro de lista como un cuadro compacto para permitir seleccionar una
determinada categoría de productos de la tabla. Asociados a esta, hacemos una búsqueda del
proveedor en la tabla.

Vemos cómo podemos usar INDICE para buscar en el rango asociado la cadena de texto que nos
interesa, y de ahí con BuscarV, sobre la tabla principal, el elemento en cuestión.

BUSCARV(E116;B5:L83;2;FALSO)

3. Control de número, para facilitar la estructura de un número por parte de un usuario.

Usamos un control de número para recorrer las filas de la tabla, devolviendo en otra casilla el
nombre del producto en esa fila, empleando la función índice:

=INDICE(BD;E115;2)

Después, podemos relacionarlo con =BDCONTARA(BD;K115;K115:K116) para contar todos los

elementos de una determinada categoría.

4. Botón de opción, para seleccionar si el resultado del control de número debe mostrar el
nombre del producto o el proveedor. En este caso, tenemos que tener dos para que seleccione
una o la otra.

5. Casilla de Verificación, que no debemos confundir con el botón de opción.

96
6. Barras de desplazamiento, que se desplaza sobre un intervalo de valores cuando el usuario
hace clic en las flechas.

7. Botón, que ejecuta una macro que realiza una acción cuando el usuario hace clic en él. En el
apartado siguiente conectamos una de las macros básicas que realizamos a una acción sobre
este botón.

Los controles ActiveX ofrecen más flexibilidad que los proporcionados por los controles de
formulario. Además, tienen propiedades que puede usar para personalizar su apariencia,
comportamiento, fuentes y demás características

97
Módulo 12: Macros e Introducción a VBA.
12.1 Introducción a las Macros
Una macro es un conjunto de acciones sobre un Libro Excel que una vez grabadas (o
programadas) se pueden ejecutar todas las veces que desee. De esta manera permiten
automatizar tareas repetitivas que realizamos en Excel, añadir nuevas funcionalidades a la
herramienta (por ejemplo, puede desarrollar nuevos algoritmos para analizar datos y, a
continuación, usar las funcionalidades de gráficos de Excel para mostrar los resultados) así
como conectar las distintas herramientas de Microsoft Office. En este último caso facilitaría, por
ejemplo, el envío automático mediante Outlook de correos a direcciones almacenadas en una
hoja Excel o el intercambio de información con una base de datos en Access, entre otras
opciones. También es útil su uso cuando queremos dotar a nuestros libros de una interactividad
extra, como se verá en los ejercicios de ejemplo.

Cuando se crea una macro se graban los clics del mouse y las pulsaciones de las teclas. Después
de crear una macro puede modificarla para realizar cambios menores en su funcionamiento.
También es posible crear macros desde cero mediante el lenguaje de macros VBA (Visual Basic
for Applications). Es importante ser consciente de que el uso de macros, además de ser una
potente herramienta, puede tener aparejada ciertos riesgos para la seguridad de nuestro PC,
por lo que sólo se debe permitir el uso de macros procedentes de fuentes fiables.

A continuación veremos cómo grabar y reproducir macros.

12.2 Grabación y Reproducción de una macro

La forma más sencilla y directa de crear una macro es mediante la grabadora de macros de Excel.
Esta grabadora graba las acciones realizadas sobre un libro Excel, de tal manera que
posteriormente pueden ser reproducidas. Las acciones son además traducidas a instrucciones
en VBA, permitiendo su modificación posterior si tenemos conocimientos de programación. Las
herramientas de macros están en la ficha de Desarrollador (1), que no se encuentra habilitada
por defecto en Excel. Debe ser por lo tanto activada en el menú de Personalizar la cinta de
opciones del cuadro de Opciones de Excel.

La opción de grabar una macro se encuentra disponible tanto en la ficha del desarrollador como
en la barra de estado (2). Una vez activada la grabadora, todas las acciones que realicemos sobre
la hoja Excel quedarán almacenadas. Una vez finalizamos la tarea, procederemos a guardar la
macro, dándole un nombre y si queremos, una combinación de teclas con la que realizar un
acceso rápido a las mismas.

Una vez almacenada podemos reproducirla (3). Si lo hacemos podemos comprobar como es el
efecto que se produce sobre la hoja. ¿Qué pasa si realizamos la reproducción partiendo de una
celda distinta? A este respecto, se probará que sucede si activamos la opción Usar referencias
relativas, del grupo de Código de la ficha de Desarrollador, antes de proceder a la grabación de
la macro.

98
Desde la herramienta Macros (en su cuadro de diálogo correspondiente) podremos decidir,
además de la macro que se va a ejecutar, su edición. Si seleccionamos esta opción se abrirá el
editor de Visual Basic (4), en el que podremos revisar la traducción a instrucciones de
programación de la macro almacenada así como proceder a su modificación. A la vista de este
código, lo primero que veremos es la instrucción Sub que es la abreviación de la palabra
subrutina. Una subrutina es un conjunto de instrucciones que se ejecutarán una por una hasta
llegar al final, lo que se indica con la instrucción End Sub.

Las subrutinas nos ayudan a agrupar varias instrucciones de manera que podamos organizar
adecuadamente el código. Para finalizar este ejercicio revisamos el código de la macro realizada
en el apartado anterior.

12.3 Introducción a Visual Basic para aplicaciones (VBA)

En este apartado se resuelven una serie de ejercicios con los que se plantean los conceptos
generales del trabajo con las macros y de la programación con lenguaje VBA.

99
Ejercicio 1
Este ejercicio tiene por objetivo la implementación mediante VBA de una hoja para el cálculo de
la cuota mensual de un préstamo bancario.

100
En el ejercicio hay que destacar los siguientes elementos del lenguaje VBA:

- Introducción de Valores y de fórmulas en las celdas seleccionadas, mediante los campos

FormulaLocal y Value de un rango de celdas.
- Definición de variables con Dim.
- Uso de cuadros de diálogo para mostrar mensajes mediante MsgBox y para recibir
parámetros por parte del usuario con InputBox.
- Uso de las funciones propias de Excel mediante el objeto WorksheetFunction, que se usa
como contenedor de las funciones de hoja de cálculo que pueden llamarse desde Visual
Basic.

Ejercicio 2

En este ejercicio se muestran como modificar la estructura del libro añadiendo nuevas hojas,
contando el número de horas existentes y cambiándole el nombre alguna de ellas.

Cabe destacar como se elige la celda activa (Mediante el método Activate del objeto
Worksheets, que contiene todas las hojas del libro).

101
Ejercicio 3
El objetivo de este ejercicio es practicar con los mecanismos de VBA para recorrer rangos de
celdas, realizando operaciones con sus valores. En este caso se trabajará sobre el fichero
[Link]. Para que funcione correctamente la hoja que contiene los
datos debe llamarse Cambiado.

En este ejercicio hay que destacar el uso de la estructura de programación For Each Next que
nos permite en este caso recorrer todas las celdas en el rango seleccionado. Esto nos permite ir
evaluando el valor almacenado en ellas. En este caso, para calcular el valor máximo de toda la
columna G de la hoja. Por otro lado, se muestra el uso de CELLS para seleccionar todas las celdas
de una fila en concreto, mediante denominación de coordenadas (fila, columna).

Como ejercicio extra se propone hacer que la Macro devuelva el nombre del vendedor que ha
vendido más unidades.

Ejercicio 4
En este ejercicio se va a practicar con más detalle el recorrido de un rango de celdas así como
la entrada de datos (y la interface) con el usuario. Se debe realizar con el fichero de ejemplo
[Link]. Sobre este se solicita que el usuario introduzca el nombre de
un vendedor que es buscado en la lista de operaciones.

102
En este ejercicio se destaca el uso de estructuras de programación de VBA, como If Then Else,
para ejecutar condicionalmente un grupo de instrucciones en función del valor de una
expresión. También se usa de nuevo For Each In Next, para evaluar todas las celdas en un
rango.

103
Respecto a la interface con el usuario, se emplean MsgBox con opciones de plantear preguntas
de respuesta (Yes/No) así como cuadros de entrada para leer un nombre.

Como ejercicio extra se plantea modificar la macro para que se muestre la operación de venta
más alta, de entre las del vendedor seleccionado.

Ejercicio 5
En este ejercicio se van a cambiar las propiedades de la celda, tales como el tipo de letra, su
alineamiento, estilo de bordes, color de fondo etc mediante una macro VBA.

Se añade también un comentario mediante NoteText. Se propone como ejercicio extra el probar
con otros colores, estilos de borde etc. Por otro lado, se desea que en la celda se escriba una
cadena que se solicita al usuario que introduzca mediante un cuadro de diálogo.

Ejercicio 6
Este ejercicio muestra mediante un ejemplo el uso de gráficos en VBA. Se debe realizar sobre
un fichero nuevo, en el que generamos dos columnas de datos entre las celdas A1 y B21. Se
propone como ejercicio extra la realización de otros tipos de gráficos. Los tipos disponibles
pueden consultarse aquí: [Link]
vba/articles/xlcharttype-enumeration-excel

104
105