ÍNDICE

❯ TEMA 1. Introducción
❯ TEMA 2. Funciones complejas con Excel
❯ TEMA 3. Trabajo con libros
❯ TEMA 4. Formatos y estilos
❯ TEMA 5. Trabajando con datos
❯ TEMA 6. Listas de datos
❯ TEMA 7. Tablas dinámicas
❯ TEMA 8. Macros
❯ Caso práctico

ARAGONESA DE SERVICIOS TELEMÁTICOS

Héctor Berná
Isabel Granel
Jesús Pérez
Manuel Peris
Pascual Soriano
1.- Entorno de trabajo: La ventana de Excel
En esta primera lección del curso repasaremos el entorno de trabajo de Excel
2016.

La estructura de Excel 2016 no ha cambiado mucho con respecto a las

anteriores versiones de Excel (como por ejemplo Excel 2013 o 2010), sin
embargo el salto que se ha realizado en los puestos de trabajo de la DGA ha
sido muy grande al pasar directamente del paquete O ce 2003 al 2016. Por
lo tanto, la estructura de la antigua Excel 2003 ha cambiado signi cativamente a la nueva estructura de menús
de la Excel 2016.

Cuando se abre la aplicación, aparece la pantalla principal de Excel con la estructura grá ca de la Hoja.

En la parte superior de la ventana de Excel 2016 aparecen los siguientes elementos.

• Archivo: Después de abrir Archivo se muestra la vista Backstage de Microsoft O ce. Esta vista es donde se
administran los archivos y sus datos, creando, guardando, inspeccionando metadatos o información personal
ocultos y con gurando opciones. En síntesis, se trata de todo lo que se hace a un archivo que no se hace en
él.

• Barra de herramientas de acceso rápido: Es totalmente personalizable y en ella se ubicarán los botones de
comando más utilizados por cada usuario.

• Barra de títulos: Aparece el nombre que se le ha dado al archivo una vez guardado en disco. En la parte
derecha se ubican los clásicos botones de minimizar, restaurar y cerrar.

• La Cinta de Opciones: Está formada por chas (Inicio, Insertar Diseño de Página, Fórmulas, Datos, Revisar y
Vista). Cada cha al seleccionarlas se distribuye a nuestra vista en menús de funciones en forma de
cajas. Cada caja tienen de comandos o botones más importantes y también una echa para poder ver más
opciones.

• Barra de fórmulas: Consta de Cuadro de nombres y Barra de fórmulas. El cuadro de nombres está situado en
el extremo izquierdo de la barra de fórmulas e identi ca el nombre de la celda, elemento grá co u objeto
seleccionado. La barra de fórmulas se utiliza para escribir o editar valores o fórmulas en celdas o grá cos.
Muestra el valor o la fórmula de la celda activa.

• Ayuda de Excel: Como novedad en Excel 2016, está la opción de buscar directamente en el diálogo de ayuda
de Excel.
En la parte principal e inferior de la ventana de Excel 2016 aparecen los siguientes elementos.

• Barra de mensajes de seguridad: Muestra alertas de seguridad. Cuando al abrir un documento hay contenido
activo que puede no ser seguro, por ejemplo documentos que contienen macros sin rmar.

• Área de trabajo: Es la hoja de cálculo propiamente dicha. Es una estructura matricial de las y columnas en
cuya intersección, llamada celda, se introducen los datos.

• Barras de desplazamiento: Están situadas en la parte derecha e inferior de la ventana, su función es ayudar a
moverse con rapidez por la zona del área del trabajo. A la izquierda de la barra de desplazamiento horizontal
están situadas unas etiquetas de tipo cha que permiten moverse por las diferentes hojas del libro.

• Barra de estado: Situada en la parte inferior de la ventana. Ofrece información como número de celdas
seleccionadas, activación o desactivación de las macros, etc. En esta zona también están ubicados el selector
de vistas y la barra deslizante del zoom.
Otra posibilidad de ejecutar acciones la tenemos a través de los menús contextuales, que aparecen
seleccionando y pulsando el botón de la derecha del ratón. Dependiendo cuál sea la selección el menú será
diferente.
2.- Crear un documento en Excel
Un libro es un archivo que contiene una o más hojas de cálculo para ayudarle a organizar los datos. Puede
crear un libro desde un libro en blanco o una plantilla.

Para crear un nuevo libro en blanco:

1. Seleccione Archivo > Nuevo.

2. Seleccione Libro en blanco.

SUGERENCIA: Presione Ctrl+N para crear rápidamente un libro nuevo.

Para crear un nuevo libro con una plantilla:

1. Seleccione Archivo > Nuevo.

2. Seleccione o busque una plantilla de la lista de plantillas disponibles y, a continuación, seleccione Crear.
3.- Guardar un documento en Excel
Para guardar un documento en Excel tiene varias opciones:

a) Haga clic en la pestaña Archivo > Guardar

b) En la barra de herramientas de acceso rápido

c) o bien, presione Ctrl+G.

Si ha guardado el trabajo antes, ha acabado.

En cambio, si es la primera vez que guarda este archivo, tendrá que realizar lo siguiente.

1. Haga clic en la pestaña Archivo > Guardar como, y elija dónde desea guardar el libro y desplácese a una
carpeta.

Importante, la opción de guardar en OneDrive no está operativa en la instalación del paquete o ce del
puesto de trabajo de la DGA, por lo que no podrá utilizar.

2. En el cuadro Nombre de archivo, escriba un nombre para el libro.

3. Haga clic en Guardar.

4.- Personalizar la barra de herramientas de acceso

rápido
La barra de herramientas de acceso rápido es una barra de herramientas que se encuentra en la parte superior
izquierda de la Excel. Es totalmente personalizable y en ella se ubicarán los botones de comando más
utilizados por cada usuario. Contiene un conjunto de comandos independientes de la cha en la cinta de
opciones que se muestra. Esta barra se puede mover desde una de las dos ubicaciones posibles y se le
pueden agregar botones que representan comandos.

Solo se pueden agregar comandos a la barra de herramientas de acceso rápido. El contenido de la mayoría de
las listas, como los valores de sangría y espaciado, así como los estilos individuales que aparecen también en
la cinta de opciones, no se pueden agregar a esta barra de herramientas. Sin embargo, puede personalizar la
cinta de opciones como usted desee. Por ejemplo, puede crear chas personalizadas y grupos personalizados
para que contengan los comandos que usa con más frecuencia.

Remarcar que la barra de herramientas de acceso rápido no se puede mostrar en varias líneas y no se puede
aumentar el tamaño de los botones.

Se pueden agregar comandos de diversas maneras.

Agregar un comando a la barra de herramientas de acceso rápido desde la cinta de opciones

1. En la cinta de opciones, haga clic en la pestaña o el grupo correspondiente para mostrar el comando que
desee agregar a la barra de herramientas de acceso rápido.

2. Haga clic con el botón secundario, y, a continuación, haga clic en la opción Agregar a la barra de
herramientas de acceso rápido del menú contextual.
Agregar un comando a la barra de herramientas de acceso rápido que no está en la cinta

1. Haga clic en Personalizar barra de herramientas de acceso rápido. Aparece una lista de sugerencias de
comandos más utilizados para agregar a la barra.

2. Si no encuentra el comando deseado, haga clic en Personalizar barra de herramientas de acceso rápido >
Más comandos.

3. En la lista Elegir comandos de, haga clic en Comandos que no están en la cinta de opciones.

4. Encuentre el comando en la lista y, a continuación, haga clic en Agregar. Si no encuentra el comando que
desea en los comandos que no están en la lista de la cinta de opciones, pruebe a con gurar comandos de
selección de para todos los comandos.

Quitar un comando de la barra de herramientas de acceso rápido

❯ Haga clic con el botón secundario en el comando que desee quitar de la barra de herramientas de
acceso rápido y, a continuación, haga clic en Quitar de la barra de herramientas de acceso rápido en el
menú contextual.

Cambiar el orden de los comandos en la barra de herramientas de acceso rápido

1. Haga clic con el botón secundario en la barra de herramientas de acceso rápido y a continuación haga clic
en Personalizar la barra de herramientas de acceso rápido en el menú contextual.

2. En Personalizar la barra de herramientas de acceso rápido, haga clic en el comando que desee mover y, a
continuación, haga clic en la echa Subir o Bajar.

Agrupar los comandos mediante la incorporación de un separador entre los comandos

Los comandos se pueden agrupar mediante el uso de separadores para hacer que la barra de herramientas de
acceso rápido aparente tener secciones.

1. Haga clic con el botón secundario en la barra de herramientas de acceso rápido y, a continuación, haga clic
en Personalizar la barra de herramientas de acceso rápido en el menú contextual.

2. En la lista Comandos disponibles en, haga clic en Comandos más utilizados.

3. Haga clic en <Separador> y, a continuación, en Agregar.

4. Para colocar el separador donde desee, haga clic en la echa Subir o Bajar.

Mover la barra de herramientas de acceso rápido

La barra de herramientas de acceso rápido puede colocarse en una de estas dos ubicaciones:

❯ Esquina superior izquierda, en la barra de título. (ubicación predeterminada)

❯ Debajo de la cinta de opciones.

Si no desea que la barra de herramientas de acceso rápido se muestre en su ubicación actual, puede moverla
a la otra ubicación. Si observa que la ubicación predeterminada situada junto al icono de un programa está
demasiado lejos del área de trabajo como para que su uso resulte cómodo, quizá desee acercarla al área de
trabajo. La ubicación situada debajo de la cinta invade el área de trabajo. Por lo tanto, si desea maximizar el
área de trabajo, es posible que pre era mantener la barra de herramientas de acceso rápido en su ubicación
predeterminada.

1. Haga clic en personalizar barra de herramientas de acceso rápido (en el extremo derecho del acceso
rápido).

2. En la lista, haga clic en Mostrar debajo de la cinta de opciones o Mostrar encima de la cinta de opciones.
5.- Explorando Excel
En este video veremos una descripción del entorno de Excel 2016.
6.- Usos y excepciones en la DGA
Consejo de uso: Personalizar la barra de herramientas de acceso rápido

La personalización de la barra de herramientas de acceso rápido será muy útil y le ayudará trabajar más rápido
con Excel.

Cada persona se acostumbra a utilizar un conjunto de comandos de Excel que repite en su día a día con la
Excel.

En unos casos serán comandos de trabajo con datos como Ordenar, Filtro, Agrupar, etc…

En otros casos serán comandos de formatos y diseño como Bordes, Inmovilizar, Formato, etc..

Y sin olvidar los comandos de Archivo como Abrir, Guardar como, Vista previa de impresión, etc..

Excepción: Guardar en OneDrive no está disponible

Como ya hemos comentado, la opción que ofrece Excel de guardar los archivos directamente en el OneDrive
no está disponible en la instalación de O ce de la DGA.
1.- Presentación
Independientemente de otras muchas utilidades, las fórmulas son realmente la razón de ser de las hojas de
cálculo. En Excel, cada celda funciona como una calculadora.

Una fórmula es una expresión que introducimos en una celda para realizar algún tipo de cálculo. Debe ir
precedida siempre por el símbolo "igual" (=) para que Excel pueda interpretarla como tal en lugar de como una
simple cadena de texto.

Las fórmulas pueden contener referencias a otras celdas, operadores y constantes, así como también
"funciones". Estos son algunos sencillos ejemplos de fórmulas:

❯ =A1+B1
❯ =10+20
❯ =SUMA(A1:A10)

Cuando Excel encuentra una fórmula, automáticamente la evalúa y muestra su resultado en la propia celda. No
obstante, la fórmula en si misma permanecerá visible en la barra de fórmulas y podremos verla cuando
seleccionemos la celda.

Gracias a las fórmulas podemos realizar operaciones y cálculos repetitivos más o menos complejos, desde
sencillas operaciones matemáticas (suma, resta, multiplicación, división, porcentaje, potencia, etc.) hasta
operaciones más complejas mediante el uso de las más de 400 funciones (fórmulas prede nidas o
abreviadas) disponibles en Excel (sumar un amplio rango de datos, obtener distintas estadísticas de los datos,
realizar todo tipo de cálculos matemáticos y trigonométricos, o de tipo nanciero, cientí co, de ingeniería…).

Podemos además operar tanto con datos numéricos, como con datos de tipo fecha y hora o incluso con
cadenas de texto.

Aunque las fórmulas pueden operar con valores jos (constantes) introducidos en la propia fórmula (=10+20),
lo usual y recomendable, para no tener que modi car las fórmulas cada vez que queramos cambiar un dato, es
utilizar los datos existentes en otras celdas de la hoja, de ahí que lo habitual en una fórmula sea ver
referencias a otras celdas o rangos de la hoja (=A1+B1, =SUMA(A1:A10), ...).

Las fórmulas conectan por tanto unas celdas con otras, de forma que cualquier cambio que se produzca en
una celda provoca que automáticamente se recalculen las celdas dependientes de ésta.
En el presente tema se repasarán los distintos operadores que tenemos para construir fórmulas, así como la
mecánica para utilizar las distintas funciones de que dispone Excel, ayudados en caso necesario por el
Asistente para funciones, realizando un repaso por las distintas categorías de funciones y mostrando el uso y
estructura de algunas de las consideradas como más útiles o habituales:

❯ Operadores: aritméticos (+, -, *, /, %, ^), de comparación (<, >, =, <=, >=, <>) de concatenación de texto
(&)
❯ Funciones Matemáticas y estadísticas: SUMA, ENTERO, ALEATORIO.ENTRE, PROMEDIO, CONTAR,
CONTARA, CONTAR.BLANCO, MAX, MIN, VAR, ...
❯ Funciones de Fecha y hora: HOY, AHORA, DIA, MES, AÑO, FECHA, DIAS.LAB, ...
❯ Funciones Lógicas: SI, Y, O, NO
❯ Funciones de Búsqueda y referencia: BUSCARV
❯ Funciones de Texto: MAYUSC, NOMPROPIO, SUSTITUIR, BLANCOS, IZQUIERDA, DERECHA, LARGO,
HALLAR, ...

Muy importante a la hora de confeccionar una fórmula, para no tener que reescribirla cada vez y poder
copiarla o arrastrarla a todo un rango de datos, es entender y dominar el uso del símbolo $ para poder de nir
direcciones o referencias absolutas ($A$1), relativas (A1) o mixtas ($A1, A$1) según se necesite en cada
caso. En esta línea, como alternativa al uso de las referencias absolutas, se explorará la opción de Asignar
nombre a un rango de celdas, lo que nos permitirá referenciar celdas o rangos particulares de la hoja mediante
el nombre que le hayamos asignado, haciendo las fórmulas mucho más claras y sencillas.

También se explorarán otras utilidades u opciones que tenemos a la hora de trabajar con fórmulas, como la
Auditoría de fórmulas para ayudarnos a comprender mejor cómo se obtiene el resultado de una fórmula y
poder depurar posibles errores, o incluso la posibilidad de cambiar en determinadas circunstancias del modo
Automático al modo de cálculo Manual.

En general, todas estas utilidades y opciones se encuentran accesibles desde la pestaña Fórmulas de la cinta
de opciones de Excel.

Al nal de todos los vídeos explicativos contenidos en este tema, se mostrará un ejemplo práctico paso a
paso más completo.

Empecemos...
2.- Introducción a las fórmulas
Empezaremos con un breve vídeo de introducción a las fórmulas.

HEMOS VISTO...

Las partes de una fórmula de Excel

Una fórmula puede contener algunos o todos los elementos siguientes: funciones, referencias, operadores y
constantes.

1. Funciones: la función PI() devuelve el valor de Pi: 3,14159...

2. Referencias: A2 devuelve el valor de la celda A2.
3. Constantes: números o valores de texto escritos directamente en una fórmula
como, por ejemplo, 2.
4. Operadores: el operador ^ (acento circun ejo) eleva un número a una potencia y el operador * (asterisco)
multiplica números.

En algunos casos, el orden en el que se ejecuta el cálculo puede afectar al valor devuelto de la fórmula. Por
tanto, es importante comprender cómo se determina el orden y cómo podemos cambiar el orden para obtener
los resultados deseados.

Prioridad de operadores en las fórmulas de Excel

Si se combinan varios operadores en una única fórmula, Excel sigue las reglas matemáticas generales para
los cálculos, que son: signo menos (1º), porcentaje (2º), exponente (3º), multiplicación y división (4º), y
suma y resta (5º). Si una fórmula contiene operadores con la misma prioridad, por ejemplo, si una fórmula
contiene un operador de multiplicación y división, Excel evalúa los operadores de izquierda a derecha.

El uso de paréntesis nos permite cambiar ese orden de cálculo.

Usar paréntesis en las fórmulas de Excel

Para cambiar el orden de evaluación, deberemos escribir entre paréntesis la parte de la fórmula que se
calculará en primer lugar. Por ejemplo, la siguiente fórmula da un resultado de 11 porque Excel efectúa la
multiplicación antes que la suma. La fórmula multiplica 2 por 3 y después suma 5 al resultado.

=5+2*3

Por el contrario, si se usan paréntesis para cambiar la sintaxis, Excel sumará 5 y 2 y después multiplicará el
resultado por 3, con lo que se obtiene 21.

=(5+2)*3

En el siguiente ejemplo, los paréntesis que rodean la primera parte de la fórmula indican a Excel que calcule
B4+25 primero y después divida el resultado por la suma de los valores de las celdas D5, E5 y F5.

=(B4+25)/SUMA(D5:F5)

Mejor usar referencias a celdas que constantes en las fórmulas de Excel

Una referencia identi ca una celda o un rango de celdas en una hoja de cálculo e indica a Excel dónde debe
buscar los valores o los datos que desea usar en una fórmula. Las referencias permiten usar datos de
distintas partes de una hoja de cálculo en una fórmula, o bien usar el valor de una celda en varias fórmulas.
También puede hacerse referencia a las celdas de otras hojas en el mismo libro y de otros libros (las
referencias a celdas de otros libros se denominan vínculos o referencias externas).

Una constante es un valor que no se calcula, sino que permanece igual siempre. Si usamos constantes en una
fórmula en vez de referencias a celdas (por ejemplo, =30+70+110), el resultado sólo cambia si modi camos la
fórmula. En general, es más sencillo y menos propenso a errores de escritura poner estos valores en celdas
individuales donde se puedan cambiar fácilmente si fuera necesario y luego hacer referencia a esas celdas en
las fórmulas.
3.- Operadores
Continuando con la introducción a las fórmulas, el siguiente vídeo nos termina de mostrar los operadores
aritméticos que podemos utilizar dentro de una fórmula, además de los de comparación y de texto, así como
distintas formas o expresiones que podremos utilizar para hacer referencia a rangos de celdas.

Además de las operaciones que podemos realizar utilizando los operadores básicos, Excel dispone de más de
400 funciones de todo tipo (matemáticas, estadísticas, nancieras, etc.). Las funciones no son más que
fórmulas u operaciones prede nidas que podemos utilizar variando en cada momento los parámetros según
necesitemos.

HEMOS VISTO...

Tipos de operadores para usar en fórmulas

Los operadores especi can el tipo de cálculo que deseamos ejecutar en los elementos de una formula. Hay
cuatro tipos diferentes de operadores de cálculo: aritméticos, de comparación, de concatenación de texto y
de referencia.

❯ Operadores aritméticos

Para ejecutar las operaciones matemáticas básicas como suma, resta, multiplicación o división, combinar
números y generar resultados numéricos, usar los siguientes operadores aritméticos.

Operadores aritméticos
Operador aritmético Signi cado Ejemplo
+ (signo más) Suma =3+3
– (signo menos) Resta Negativo = 3 – 3 = -3
* (asterisco) Multiplicación =3*3
/ (barra oblicua) División =3/3
% (signo de porcentaje) Porcentaje = 3% * 150
^ (acento circun ejo) Exponenciación =3^3

❯ Operadores de comparación
Se pueden comparar dos valores con los siguientes operadores. Cuando se comparan dos valores usando
estos operadores, el resultado es un valor lógico, ya sea VERDADERO o FALSO.

Operadores de comparación
Operador de comparación Signi cado Ejemplo
= (signo igual) Igual a = A1=B1
> (signo mayor que) Mayor que = A1>B1
< (signo menor que) Menor que = A1<B1
>= (signo mayor o igual que) Mayor o igual que = A1>=B1
<= (signo menor o igual que) Menor o igual que = A1<=B1
<> (signo distinto de) No es igual a = A1<>B1

❯ Operador de concatenación de texto

Usar la "y" comercial (&) para concatenar (unir) una o varias cadenas de texto con el n de generar un solo
elemento de texto.

Operador de concatenación
Operador de
Signi cado Ejemplo
texto
= "Turbo" & "compresor" da como resultado
"Turbocompresor".
Conecta o concatena dos valores
& (ampersand) para generar un valor de texto
Si A1 contiene "Apellido" y B1 contiene "nombre",
continuo
entonces = A1&", "&B1 da como resultado "Apellido,
nombre".

❯ Operadores de referencia

Combinar rangos de celdas para los cálculos con los siguientes operadores.

Operadores de referencia
Operador de
Signi cado Ejemplo
referencia
Operador de rango, que genera una referencia a todas las celdas
: (dos puntos) B5:B15
entre dos referencias, éstas incluidas
; (punto y
Operador de unión, que combina varias referencias en una sola = SUMA(B5:B15 ; D5:D15)
coma)
Operador de intersección, que genera una referencia a las
(espacio) B7:D7 C6:C8
celdas comunes a dos referencias

Algunos ejemplos más de referencias de celdas:

Ejemplos de referencias de celdas

Para hacer referencia a Usar
La celda de la columna A y la la 10 A10
El rango de celdas de la columna A y de las las 10 a 20 A10:A20
El rango de celdas de la la 15 y de las columnas B a E B15:E15
Todas las celdas de la la 5 5:5
Todas las celdas de las las 5 a 10 5:10
 Para hacer referencia a Usar
Todas las celdas de la columna H H:H
Todas las celdas desde la columna H hasta la J H:J
El rango rectangular de celdas de las columnas A a E y de las las 10 a 20 A10:E20

Qué es una FUNCIÓN

Imaginemos tener que sumar un rango de 200 celdas con una fórmula del tipo =A1+A2+A3+A4+... Las
funciones nos permiten realizar cálculos complejos de forma sencilla y abreviada ahorrándonos tiempo y
esfuerzo. La función SUMA, por ejemplo, toma las referencias de celda o intervalos que especi quemos y los
suma.

=SUMA( A1:A200 )

En este ejemplo toma el rango de celdas de A1 a A200 y devuelve la suma de todas ellas.

Así pues, podemos decir que una función es una fórmula prede nida que ejecuta cálculos utilizando valores
especí cos, denominados argumentos, en un orden determinado o estructura. Las funciones pueden
utilizarse para ejecutar operaciones simples o complejas.

Todas las funciones en Excel tienen la misma estructura básica:

NOMBREFUNCION( argumento1; argumento2; ... )

No obstante, cada función tiene una sintaxis propia, que de ne el número, el tipo y el orden en el que tienen
que ir sus argumentos. Éstos deben ir siempre separados por punto y coma (;). Algunos de estos argumentos
pueden ser opcionales para la función, es decir, no es obligatorio indicarlos si no es necesario, aunque el
primer o primeros argumentos de una función son obligatorios casi siempre. Por ejemplo, la función SUMA
admite varios argumentos, pero sólo es obligatorio el primero:

❯ =SUMA( A1:A10 ) Suma los valores de las celdas A1:A10.

❯ =SUMA( A1:A10 ; C1:C10 ) Suma los valores de las celdas A1:A10, así como las celdas C1:C10.

La estructura de esta función sería la siguiente:

No obstante, algunas funciones no precisan de ningún argumento, como HOY(), AHORA(), PI(),
ALEATORIO(),...
Las funciones se pueden usar de forma independiente o como operando dentro de una fórmula, o incluso
como argumento dentro de otra función. En este último caso hablamos de funciones anidadas o anidamiento.

Excel tiene más de 400 funciones que podemos explorar en la pestaña Fórmulas. Desde esta pestaña
podemos examinar la Biblioteca de funciones de Excel, organizada por categorías.

Cómo introducir una FUNCIÓN

Para introducir una función tenemos varias opciones:

a) Podemos empezar a escribir directamente su nombre, si la conocemos.

Cuando empezamos a escribir el nombre de una función, Excel nos muestra una lista con todas las funciones
que empiecen con las letras que escribamos.

Una vez tecleado el paréntesis de apertura también nos mostrará una etiqueta interactiva (etiqueta
IntelliSense) indicando los argumentos necesarios y los opcionales (entre corchetes) en su caso.

b) Otra opción es utilizar el Asistente para funciones mediante el comando Insertar función que tenemos en
la barra de herramientas o justo en la barra de fórmulas:

El comando Insertar función nos abre el asistente clásico para funciones de Excel, que nos permite buscar la
función que queremos usar.
Nos ayuda además a rellenar los argumentos de la función si no conocemos bien su sintaxis o lo que signi ca
cualquiera de ellos.

c) También podemos agregar la función seleccionándola desde la biblioteca de Excel.

Obtener ayuda sobre una función

A pesar de todas las facilidades que nos proporciona Excel para usar las funciones, disponemos además de
una ayuda en línea muy completa sobre cada una de las funciones, con ejemplos de uso, la cual nos
puede resultar a veces de mucha utilidad, sobre todo a la hora de utilizar algunas de las funciones más
complejas de Excel. A esta ayuda podemos acceder por ejemplo desde el enlace que aparece en la parte
inferior del propio asistente de funciones.
4.- Funciones matemáticas y estadísticas
Los actuales procesadores de textos, hojas de cálculo, etc., aportan tal cantidad de funciones que para su
utilización integral se necesitaría una formación exhaustiva.

Centrándonos en Excel, nos encontramos con que éste cuenta con más de 400 funciones capaces de operar
sobre un conjunto de cifras introducidas en la hoja. Así, desde una simple suma hasta una función estadística
compleja, se puede calcular casi cualquier cosa.

Sin embargo, el trabajo de la organización no hace necesaria la utilización de todas las posibles funciones. La
realidad es que, en la mayoría de las organizaciones y en la mayoría de los puestos de trabajo, las
necesidades habituales quedan aseguradas por no más del 5% de las funcionalidades prestadas por los
actuales paquetes o máticos.

No obstante, lo que para algunos puede resultar básico para su trabajo, para otros puede resultar avanzado o
incluso innecesario. Por ejemplo, determinadas funciones de tipo nanciero, de ingeniería, estadísticas o
trigonométricas, están pensadas para usos muy especializados y que requieren incluso de conocimientos
especí cos sobre alguna materia o disciplina (banca, nanzas, ingeniería, estadística, informática, etc.).

Con objeto de profundizar en la mecánica y uso de las funciones, los siguientes vídeos nos van a mostrar el
uso de algunas de las más representativas o que pueden resultar de mayor utilidad para un uso generalizado
dentro de una organización.

No obstante, más allá del alcance de este curso y habiendo aprendido la mecánica de trabajo con las
funciones, se anima al lector a investigar realizando un recorrido por toda la biblioteca de funciones de Excel,
pues ello le puede llevar a identi car algunas otras funciones que le podrían resultar útiles en su trabajo del
día a día.

El siguiente vídeo nos muestra algunas funciones de tipo matemático y estadístico. Por ejemplo:

❯ Usar la función SUMA para sumar todos los números en un intervalo de celdas
❯ Usar la función PROMEDIO para obtener el promedio de números en un intervalo de celdas.
❯ Usar la función MIN para obtener el número más pequeño de un rango de celdas.
❯ Usar la función MAX para obtener el número más grande de un rango de celdas.
❯ Usar la función CONTAR.BLANCO para contar el número de celdas en blanco de un rango especi cado.
❯ Etc.

Todas ellas están estructuradas del mismo modo.

HEMOS VISTO...

Usar Autosuma

La función SUMA es quizás la más utilizada en una hoja de cálculo. Por ello, Excel proporciona un botón
exclusivo en la barra de herramientas denominado S Autosuma, en las pestañas de Inicio y de Fórmula de la
cinta de opciones. Es la manera más sencilla de agregar una fórmula SUMA a la hoja de cálculo.

Para ello debemos seleccionar una celda vacía debajo del rango que deseamos sumar, o bien directamente el
rango de datos, y hacer clic en el botón Autosuma.

Autosuma detectará automáticamente el rango que se va a sumar y creará la fórmula.

Esto también funciona horizontalmente si se selecciona una celda a la derecha del rango que se quiere sumar.
El botón Autosuma nos permite de igual manera aplicar otras funciones diferentes a la suma, como el
Promedio, Máx, Mín, etc.

Trabajar con decimales

Es importante tener en cuenta que, a la hora de trabajar con números decimales, por defecto Excel no trabaja
con la precisión de lo que vemos en pantalla sino que trabaja con toda la precisión del valor real de la celda.

Por ejemplo, si la celda A1 contiene el valor 23,7825 y aplicamos por ejemplo un formato monetario de dos
decimales a la celda, en pantalla veremos el valor 23,78 €. Si utilizamos la celda A1 en cualquier cálculo, Excel
utilizará el valor real contenido en la celda (23,7825) en lugar de lo que se ve por pantalla (23,78).

Por ello, en determinados cálculos nos puede interesar reducir el nº de decimales con los que operar. Para ello
Excel dispone de varias funciones que permiten aplicar distintos tipos de redondeo o de truncamiento, como
REDONDEAR, TRUNCAR, ENTERO, etc.

Información útil

Si en un determinado libro de trabajo nos interesa trabajar únicamente con la precisión de lo que vemos en
pantalla, es posible establecer una con guración avanzada para ello, yendo a Archivo > Opciones >
Avanzadas y marcando la opción "Establecer precisión de pantalla".

A partir de ese momento, los decimales que no se vean en pantalla se perderán y el valor real de cada celda
coincidirá exactamente con lo que veamos en pantalla. Es decir, si aplicamos un formato numérico con 2
decimales a una celda, cualquier cálculo que realicemos con esa celda utilizará la misma precisión de 2
decimales que vemos en pantalla, lo que nos evitaría estar constantemente trabajando con la función
REDONDEAR. Esto puede resultar muy útil en la Administración por ejemplo a la hora de manejar importes en
cálculos contables (presupuestos, subvenciones, pagos, ...) para no desviarnos con los céntimos de euro.
5.- Funciones de fecha y hora
El siguiente vídeo nos muestra brevemente algunas funciones para trabajar con fechas y horas que pueden
resultar interesantes a la hora de plantear hojas de cálculo para la realización de algún tipo de seguimiento o
control donde las fechas son relevantes, como por ejemplo:

❯ Calcular un plazo o fecha de vencimiento a partir de una fecha de entrada, sumado días, meses o
años.
❯ Calcular la antigüedad de un empleado en años, meses y días acumulados.
❯ Calcular el vencimiento del próximo trienio de un empleado.
❯ Calcular los días de retraso respecto a la fecha actual (HOY()).
❯ Calcular saldos horarios a partir de las horas de entrada y horas de salida.
❯ Realizar un cálculo u otro en función del día de la semana de una fecha dada.
❯ Calcular días laborales, con o sin días festivos.

INFORMACIÓN ÚTIL...

Aunque muchas de las conversiones de formato de fecha y hora que hace Excel son transparenrtes para
nosotros, entender cómo trabaja Excel de forma interna con las fechas y con las horas nos ayudará a tener un
mejor dominio del tema.

Entender cómo opera Excel con FECHAS

Hacer operaciones con fechas sin ayuda de un calendario es bastante engorroso, pero Excel tiene una forma
muy práctica de resolver estos cálculos.

Internamente, Excel le asigna un número correlativo a cada fecha a partir del 01/01/1900. De esta manera, el
01/01/1900 para Excel equivale al número 1; el 02/01/1900, al número 2; el 31/01/1900, al número 31; el
01/02/1900, al número 32; etc. El 14/02/2020 es el día número 43875 para Excel.

De este modo, si le pedimos a Excel que sume 45 días a la fecha 14/02/2020, simplemente sumará 43875+45,
lo que nos dará 43920, que es el número de serie correspondiente al 30/03/2020.

Por tanto, podemos ver las operaciones con fechas como simples operaciones con números enteros.
Podemos sencillamente sumar o restar días a una fecha para obtener la fecha resultante, sin tener que lidiar
con los meses y los años. También podemos restar dos fechas para saber la cantidad de días que hay entre
ellas.

Saber que Excel maneja las fechas internamente como números de serie nos ayuda a entender por qué a
veces esperamos una fecha y obtenemos un número o esperamos un número y obtenemos una fecha. Es
solamente un problema relacionado con el formato de número asignado a la celda, que se soluciona
cambiándolo de formato General a Fecha o viceversa.

Sabiendo esto, además, deja de ser un misterio el argumento "núm_de_serie" que aparece en muchas
funciones de fecha. Por ejemplo, la función MES da como resultado el número del mes de la fecha que se le
introduce como argumento. Pero la guía que ofrece Excel nos dice que debemos usar como argumento un
"núm_de_serie", que la mayoría de los usuarios no logran entender.

El núm_de_serie al que se hace referencia es obviamente el número de serie interno de la fecha que deseamos
usar como argumento. Pero no es necesario que sepamos el número de serie de una fecha para usar esta
función. Si usamos la referencia a una celda que contiene una fecha, Excel obtendrá el número de serie por sí
mismo.

No obstante, Excel también acepta las fechas escritas entre comillas como argumentos de funciones. Por
ejemplo:

=MES("30/03/2020") devolverá 3.

Entender cómo opera Excel con HORAS

Si hacer cálculos con días, meses y años de forma manual es complejo, mucho más lo es hacer cálculos
manuales con horas.

Como es de esperar, Excel utiliza un sistema similar al que usa con las fechas para manejar las horas.
Partiendo de la base de que las fechas son representadas por números enteros y que las horas son fracciones
del día, no es difícil entender que Excel representa las horas como números decimales entre cero y uno.

La hora 12:00:00 (el mediodía) es considerado por Excel internamente como 0,5, ya que, en un día de 24 horas,
12 horas representan la mitad. La hora 06:00:00, equivale para Excel al número 0,25 (la cuarta parte del día) y
así. Como un día se compone de 24 horas, para Excel, una hora equivale a la fracción 1/24, es decir:
0,0416666666666667.

De esta manera, Excel tiene la capacidad de representar la fecha y hora de un acontecimiento como un único
número, donde su parte entera representa al día y su parte decimal a la hora.

De esta manera, por ejemplo, el número de serie 43875,567 equivale al 14/02/2020 a las 13:36:29.

Ejemplo:
En la celda D36 hemos escrito =((D35-D32)-(D34-D33))*24, que calcula la hora de inicio y nalización de la
jornada de una persona y después resta el tiempo que utilizó para comer. El *24 al nal de la fórmula
convierte en horas la parte fraccionaria del día que Excel ve.

Si esta fórmula pudiese hablar, diría "Tome la hora de salida y réstela de la hora de entrada, después reste las
horas del almuerzo y multiplique el resultado por 24 para convertir las fracciones de Excel en horas", o =((Hora
de salida - Hora de entrada)-(Fin del almuerzo - Inicio del almuerzo))*24.

Una vez introducida la fórmula deberemos asegurarnos de dar formato de número a la celda (Inicio > Formato
de celdas > Número > Número > 2 decimales).
6.- Funciones lógicas
Las funciones lógicas sirven para comprobar si se cumplen o no ciertas condiciones y poder así determinar el
cálculo a realizar en función de si el resultado de dicha comprobación es VERDADERO o FALSO. Una de las
funciones más útiles e interesantes de Excel es la función SI:

Sintaxis: SI( prueba_lógica; [valor_si_verdadero]; [valor_si_falso] )

HEMOS VISTO...

Las funciones lógicas sirven para comprobar si se cumplen o no ciertas condiciones y poder así determinar el
cálculo a realizar en función de si el resultado de dicha comprobación es VERDADERO o FALSO.

La función SI

La función SI es una de las funciones más populares de Excel. Nos permite realizar comparaciones lógicas
entre un valor y un resultado esperado. Por esto, una instrucción SI puede tener dos posibles resultados.
Devolverá el primer resultado si la comparación es Verdadera y el segundo si la comparación es Falsa.
La función SI se puede usar para evaluar texto o valores. También se puede usar para evaluar errores. No está
limitado exclusivamente a comprobar si un elemento es igual a otro.

Las funciones Y, O, NO, ...

Otras funciones lógicas como Y, O o NO, nos permiten "expandir" o ampliar la "prueba_lógica" y por tanto la
utilidad de la función SI. Mediante la combinación de estas funciones podemos llegar a de nir condiciones
lógicas más complejas que una simple comparación entre dos valores.

Usar cadenas de texto en fórmulas. La cadena vacía ""

Si vamos a usar cadenas de texto como argumentos dentro de una función, tendremos que escribir el
texto entre comillas, como por ejemplo en:

❯ =SI( A2>B2; "Presupuesto excedido"; "Correcto" )

❯ =SI( A4=500; B4-A4; "" )

Este último ejemplo utiliza la cadena vacía "" (comillas comillas) para indicar que no devuelva ningún valor.
Por tanto, si queremos comprobar si una celda está vacía (no tiene valor) o devolver un valor nulo, podemos
utilizar la cadena vacía ("").

La única excepción es el uso de VERDADERO o FALSO, que Excel entiende de forma automática.

Anidamiento de funciones

En algunos casos, puede que debamos utilizar una función como uno de los argumentos de otra función. En el
video anterior hemos visto por ejemplo la función Y anidada dentro de la función SI, como uno de los
argumentos:

Otro ejemplo de anidamiento sería el siguiente:

Una práctica también habitual es anidar varias funciones SI unas dentro de otras, para los argumentos
valor_si_verdadero y valor_si_falso, para elaborar pruebas más complejas. En el siguiente ejemplo podemos
ver hasta 4 niveles de anidamiento, ya que cada función SI forma parte de los argumentos o está anidada
dentro de la anterior (los 4 paréntesis del nal de la fórmula son bastante signi cativos).

❯ =SI( L4>=9; "Sobresaliente"; SI( L4>=7; "Notable"; SI( L4>=6; "Bien"; SI( L4>=5; "Su ciente"; "Suspenso" )
)))

INFORMACIÓN ÚTIL...

Funciones de Información que devuelven valores lógicos: funciones ES

Las funciones ES son útiles en fórmulas cuando se desea comprobar el resultado de un cálculo. Combinadas
con la función SI, estas funciones proporcionan un método para localizar por ejemplo errores en fórmulas o
celdas vacías. Estas funciones pertenecen a la categoría de "Información".
7.- Funciones de búsqueda y referencia: BUSCARV
Otra de las funciones más interesantes y útiles en Excel es la función de búsqueda BUSCARV. Se utiliza
para encontrar un valor o índice en una tabla, el cual podremos utilizar en sucesivas fórmulas o funciones.

Sintaxis: BUSCARV( valor_buscado; matriz_buscar_en; indicador_columnas; [ordenado] )

HEMOS VISTO...

La función BUSCARV

BUSCARV es una de las funciones más utilizadas en Excel. Nos permite buscar un determinado valor en la
primera columna de un rango de datos (habitualmente una tabla o lista) y, si encuentra una coincidencia, la
función nos devolverá la información que se encuentra en una determinada columna a la derecha del valor
encontrado. Por tanto, la primera columna del rango hará siempre las funciones de índice de búsqueda. Su
sintaxis es la siguiente:
Lo vemos mejor con el siguiente ejemplo, en el que utilizamos la función BUSCARV para recuperar la
cali cación correspondiente a una serie de notas a partir de una tabla que utilizamos como índice.

La traducción de la fórmula empleada en este ejemplo sería la siguiente:

Tipos de búsqueda con BUSCARV: búsqueda exacta y búsqueda por aproximación

Es importante entender qué signi ca el último argumento de la función BUSCARV (ordenado) de cara a
plantear una búsqueda. Tal como se explica en la ayuda de la función:

❯ Si indicamos FALSO, BUSCARV intentará busca el valor exacto en la primera columna.

❯ Si indicamos VERDADERO, BUSCARV dará por sentado que la primera columna está ordenada, ya sea
alfabéticamente o numéricamente, y que representa intervalos, de forma que buscará la la del
intervalo más próximo al valor buscado. Como se trata de un argumento opcional, si no especi camos
nada éste será el método predeterminado que use BUSCARV.

Si BUSCARV no puede encontrar ninguna coincidencia en la tabla, entonces devolverá el valor #N/A (no
disponible).

Comparemos, utilizando el mismo ejemplo anterior, el resultado de realizar la búsqueda por aproximación
(VERDADERO) y la búsqueda exacta (FALSO) y observemos cómo la opción de búsqueda exacta no resulta
apropiada en este caso.

Es importante, a la hora de plantear una tabla en la que vamos a realizar búsquedas por aproximación
(VERDADERO) con BUSCARV, recordar que la columna primera debe estar ordenada de menor a mayor para
que BUSCARV pueda localizar correctamente el intervalo correspondiente al valor buscado.

Estos serían otros ejemplos de tablas en los que se utilizaría la búsqueda por aproximación:
En cambio, las tablas que vayamos a utilizar para realizar búsquedas exactas (FALSO) no es necesario que
estén ordenadas; BUSCARV recorrerá la primera columna hasta encontrar el valor exacto buscado. En caso de
que no encontrara ningún valor coincidente, devolvería como resultado #N/A.

La siguiente tabla, por ejemplo, la usaríamos normalmente para realizar búsquedas exactas con BUSCARV,
para simular por ejemplo un cálculo de nómina de un funcionario de un grupo dado:

La función BUSCARH

La función BUSCARH funciona con la misma mecánica que la función BUSCARV pero buscando en horizontal
(H) en lugar de en vertical (V).
Funciones de Texto
Palabras mal escritas, espacios innecesarios, pre jos o caracteres no deseados, mayúsculas y minúsculas
incorrectas, etc., pueden deslucir los datos y causan una mala impresión.

No siempre tenemos control sobre el formato y tipo de datos que se importan desde un origen de datos
externo, como una base de datos, un archivo de texto o una página web. Antes de poder analizar los datos, a
menudo necesitamos ponerlos en limpio.

Afortunadamente, Excel tiene muchas características que nos ayudan a obtener los datos en el formato
exacto que queramos. A veces, la tarea es sencilla y hay una función especí ca que hace el trabajo
rápidamente por nosotros, como el corrector ortográ co o si queremos quitar las las duplicadas. Pero otras
veces es posible que tengamos que manipular una o más columnas con una fórmula para convertir los valores
importados en valores nuevos. Para esta tarea disponemos de diversas funciones de la categoría de Texto.

Por ejemplo, si queremos eliminar algunos caracteres como guiones, puntos o espacios, podemos crear una
columna auxiliar para limpiar los datos mediante una fórmula, rellenar la nueva columna, convertir las
fórmulas de dicha columna en nuevos valores y, después, quitar la columna original.

Ejemplo 1: Normalizar y unir cadenas de texto

Para ver la utilidad de algunas de las funciones de Texto, en el siguiente ejemplo hemos simulado la
importación de una lista de datos externa. Se trata de una pequeña muestra para ilustrar un ejemplo de todo
esto. La lista contiene una serie de DNIs y nombres y apellidos, la cual queremos “normalizar” para dejarla tal
y como se ve justamente a la derecha de la hoja.

En el ejemplo observamos:

1. Los DNI’s aparecen con y sin puntos separadores y con la letra en mayúsculas y minúsculas. Lo que
haremos en este caso será: eliminar los puntos (“.”) con la función SUSTITUIR y el resultado convertirlo a
mayúsculas con la función MAYUSC.
2. Los nombres y apellidos aparecen en mayúsculas y minúsculas, incluso mezcladas, con los apellidos
juntos en algunos casos y con espacios super uos en otros. Lo que haremos para normalizar los datos y
que aparezcan en la forma “Apellido1 Apellido 2, Nombre” será: concatenar el Apellido1 y el Apellido2,
 separados por un espacio en blanco, utilizando para ello el operador &. Al resultado de esta
concatenación le eliminaremos los posibles espacios super uos mediante la función ESPACIOS. A
continuación concatenaremos el resultado con el Nombre, al que también le eliminaremos los posibles
espacios super uos, separándolos con una coma y un espacio (“, “). Todo esto lo transformaremos
nalmente mediante la función NOMPROPIO, que se encarga de transformar la inicial de cada palabra en
mayúsculas y el resto en minúsculas.

Las fórmulas resultantes que nos proporcionarán el resultado que hemos visto anteriormente serían por tanto
las siguientes:

❯ DNI: =MAYUSC( SUSTITUIR( B3; "."; "" ) )

❯ APELLIDOS, NOMBRE: =NOMPROPIO( ESPACIOS( D3&" "&E3 )&", "&ESPACIOS( C3 ) )

Ejemplo 2: Dividir cadenas de texto

En el ejemplo siguiente, lo que hacemos es utilizar otras funciones de texto para separar en dos columnas
diferentes las partes de una cadena de texto, pues hemos utilizado una lista importada de Internet en la que el
código y la descripción vienen juntos en una única cadena, separados por un espacio en blanco.

1. Para sacar la parte del código, utilizaremos la función IZQUIERDA. Para saber en cada caso cuántos
caracteres por la IZQUIERDA tenemos que extraer, nos basaremos en la posición que ocupa el primer
espacio en blanco. Para HALLAR la posición de dicho espacio en blanco dentro de la cadena, utilizaremos
la función HALLAR. Si la función nos devuelve por ejemplo un 4, sabemos que tendremos que extraer 3
caracteres por la izquierda, y si nos devuelve un 3, tendremos que extraer 2 caracteres, es decir, siempre
un número menos que lo que nos devuelva la función HALLAR.
 2. Para sacar la descripción, utilizaremos la función DERECHA para extraer el número adecuado de
caracteres por la derecha. Para saber cuántos caracteres hay que extraer, utilizaremos la función LARGO,
que nos dice el número total de caracteres que tiene una cadena, y de nuevo la función HALLAR, para
hallar la posición del primer espacio en blanco y saber así cuántos caracteres tenemos que excluir de la
extracción.

Las fórmulas resultantes serían las siguientes:

❯ CÓDIGO: =IZQUIERDA( B3; HALLAR( " "; B3 )-1 )

❯ DESCRIPCIÓN: =DERECHA( B3; LARGO( B3 )-HALLAR( " "; B3 ) )
8.- Auditoría de fórmulas
El siguiente video nos muestra las opciones de la utilidad de “Auditoría de fórmulas” que nos pueden ayudar a
comprobar que las fórmulas que hemos introducido son precisas, a encontrar el origen de un error, o a
comprender por ejemplo cómo calcula Excel el resultado nal de una fórmula anidada.

HEMOS VISTO...

Auditoría de fórmulas de Excel dispone de varias opciones o comandos que nos pueden ayudar a comprobar
que las fórmulas que hemos introducido son precisas, a encontrar el origen de un error y corregirlo, o a
comprender por ejemplo cómo calcula Excel el resultado nal de una fórmula anidada.

Mostrar las relaciones entre las fórmulas y las celdas

Podemos usar los comandos de Rastrear precedentes y Rastrear dependientes para mostrar grá camente las
relaciones entre estas celdas y las fórmulas, mediante echas de seguimiento o rastreo, tal como se muestra
a continuación.

❯ Las celdas precedentes son las celdas o rangos a los que hace referencia una fórmula.
❯ Las celdas dependientes son las celdas que contienen fórmulas que hacen referencia a otras celdas.
Tal como vemos en el ejemplo anterior, si la celda D11 contiene la fórmula "=BUSCARV( C11; $H$4:$I$8; 2;
VERDADERO )", la celda C11 y el rango H4:I8 son precedentes de la celda D11 y, a su vez, la celda D11 es
dependiente de la celda C11 y del rango H4:I8.

Las echas azules muestran celdas sin errores. Las echas rojas mostrarían celdas que causan errores. Las
echas de rastreo se pueden quitar mediante el comando Quitar echas.

Mostrar las fórmulas en lugar de los resultados

Lo que vemos normalmente en las celdas de la hoja de cálculo es el resultado de las fórmulas. Excel nos
ofrece la interesante posibilidad de cambiar entre mostrar los resultados o mostrar las fórmulas en todas las
celdas de la hoja a la vez, activando o desactivando la opción Mostrar fórmulas de la barra de herramientas.

Otras opciones que podemos utilizar para auditar fórmulas

En algún momento veremos alguna fórmula que tiene un error y que Excel mostrará con #TipoError. Estos
errores pueden ser a veces difíciles de corregir. Afortunadamente, tenemos utilidades en Excel que nos
facilitan la Comprobación de errores en fórmulas.

A veces, es difícil comprender cómo calcula el resultado nal una fórmula porque hay varios cálculos
intermedios o anidados y pruebas lógicas. Mediante el cuadro de diálogo Evaluar fórmula, podemos ver paso
a paso como Excel va evaluando las distintas partes o secciones de una fórmula.

Por último, la Ventana Inspección es una forma útil de inspeccionar o auditar fórmulas en hojas de cálculo de
gran tamaño, ya que nos permite agregar, dentro de dicha ventana, celdas de diferentes partes de la hoja de
cálculo para poder inspeccionarlas de un vistazo sin necesidad de tener que desplazarnos repetidamente.
9.- Ejercicio con funciones varias
A continuación podemos ver, paso a paso, un ejemplo más completo en el que se utilizarán varias de las
funciones vistas hasta el momento.

HEMOS VISTO...

En el ejemplo anterior:

❯ Se han utilizado operadores matemáticos para realizar ciertos cálculos sencillos (suma,
multiplicación, ...)
❯ Se ha utilizado la función BUSCARV para localizar los pluses correspondientes a cada jugador en base
a los resultados de cada jugador.
❯ Se han utilizado referencias relativas y absolutas a la hora de copiar fórmulas.
❯ Se han de nido nombres personalizados para cada uno de los rangos de celdas de las tablas de
pluses y se han usado posteriormente en las fórmulas.
❯ Se ha visto cómo crear una fórmula en un solo paso y también en varios pasos, creando columnas
intermedias que luego se pueden ocultar.
❯ Se ha utilizado la función SI para determinar si un jugador debe ser renovado o no, anidando la función
O y la función Y dentro de la función SI para establecer la condición lógica que queremos probar.

Copiar fórmulas: las referencias absolutas, relativas y mixtas

Es importante ser consciente de que, al copiar o arrastrar una fórmula, las referencias del tipo A1
(referencias relativas) cambiarán para ajustarse a la posición relativa de cada fórmula dentro de la hoja. Por
ejemplo, si tenemos una fórmula que "suma el rango de las 10 celdas que están inmediatamente por encima
de la fórmula", al copiar o arrastrar esa fórmula a otras celdas, las referencias se ajustarán para que la fórmula
siga teniendo el mismo signi cado, sumar el rango de las 10 celdas que están por encima de la fórmula.

No obstante, a veces necesitamos que, al copiar o arrastrar una fórmula, todas las fórmulas resultantes
apunten a una misma celda o rango de celdas, independientemente de la posición que ocupe cada fórmula en
la hoja. En este caso tendríamos que utilizar referencias del tipo A$1$ (referencias absolutas). El signo $ es
la forma de "bloquear" referencias e indicarle a Excel que, a la hora de copiar la fórmula, esas referencias no
debe modi carlas.
En el siguiente ejemplo ya visto para la función BUSCARV, podemos ver cómo en la fórmula =BUSCARV( C4;
$H$4:$I$8; 2; VERDADERO ) "Buscar la nota en la tabla de cali caciones y devolver el dato de la segunda
columna de la tabla", la referencia a la "nota" es relativa, porque cada fórmula debe utilizar una nota distinta,
la que se encuentra justo a la izquierda en su misma la. Sin embargo, la referencia a la "tabla de
cali caciones" es absoluta, pues todas las fórmulas deben buscar en el mismo rango jo de celdas,
independientemente de la la en la que se encuentre cada fórmula. Podemos verlo clara y grá camente
gracias a la Auditoría de fórmulas.

Con menos frecuencia, pero en ocasiones también puede ser necesario utilizar referencias mixtas, jando con
el signo $ únicamente la referencia a la columna ($A1) o únicamente la referencia a la la (A$1).

Como ya hemos visto en el ejemplo del vídeo anterior, si en una hoja identi camos alguna celda o rango
singular que va a ser usado repetidamente en una o varias fórmulas de la hoja o libro, suele ser buena
práctica, por claridad y por comodidad, asignarles un nombre personalizado y usarlo en las fórmulas, en lugar
de tener que acordarnos de escribir las referencias como absolutas.

Dar nombres personalizados a celdas o rangos y usarlos en las fórmulas

Pese a que muchos usuarios la desconocen, asignar un nombre personalizado a una celda o rango de celdas,
es una práctica básica de Excel que mejora mucho la productividad. Al utilizar nombres como sustitutos de
las referencias estándar, además de que serán más fáciles de recordar, nuestras fórmulas serán mucho más
fáciles de entender y mantener. Una vez le hayamos dado un nombre a una celda o rango, podremos utilizar
ese nombre dentro de una fórmula en lugar de su referencia, como por ejemplo en "=SUMAR( MisVentas )" en
lugar de "=SUMAR( C20:C30 )".

Para dar un nombre a una celda o un rango de celdas, basta con seleccionar la celda o rango, ir al "Cuadro de
nombres", escribir el nombre que queremos darle y presionar Enter, tal como hemos visto en el vídeo anterior.
A partir de ese momento, cuando utilicemos esa celda o rango en una fórmula podremos escribir su nombre, o
simplemente al seleccionarla aparecerá directamente el nombre que le hayamos dado. En el vídeo anterior
hemos visto como se daba un nombre a cada una de las tablas de pluses (PUNTOS, ASISTENCIAS, MINUTOS,
PERSONALES) y posteriormente se utilizaban esos nombres dentro de las fórmulas.

Por último, en caso necesario podremos administrar los nombres usados en nuestro libro (buscar, modi car,
eliminar, crear) desde el Administrador de nombres de la barra de herramientas.

INFORMACIÓN ÚTIL...

Opciones de cálculo automático y manual

Cada vez que introducirmos o modi camos algún dato, Excel recalcula automáticamente todas las fórmulas
de la hoja. Excel trabaja rápido..., pero en hojas complejas o de gran tamaño, con muchas fórmulas, y si
además nuestro ordenador no es muy potente, cada recálculo completo de la hoja o libro podría costarle a
Excel unos pocos segundos. Si estuviéramos introduciendo o modi cando varios datos en la hoja, habría que
esperar esos pocos segundos entre dato y dato a que Excel respondiera, lo que podría resultar bastante
molesto e ine ciente.

Para estos casos, Excel nos permite cambiar las Opciones para el cálculo y pasar a modo Manual. De esta
forma, Excel sólo realizará los cálculos cuando se lo indiquemos, mediante el comando Calcular ahora o la
tecla F9, o pasando de nuevo al modo Automático.
Fin del tema
¡Enhorabuena! A modo de resumen, el siguiente esquema re eja los aspectos que dereríamos haber aprendido
sobre el tema de "Funciones complejas con Excel":

❯ Presentación del tema

❯ Introducción a las fórmulas
❯❯ Las partes de una fórmula de Excel
❯❯ Prioridad de operadores en las fórmulas de Excel
❯❯ Usar paréntesis en las fórmulas de Excel
❯❯ Mejor usar referencias a celdas que constantes en las fórmulas de Excel
❯ Operadores
❯❯ Tipos de operadores para usar en fórmulas
❯❯ Qué es una FUNCIÓN
❯❯ Cómo introducir una FUNCIÓN
❯❯ Obtener ayuda sobre una función
❯ Funciones matemáticas y estadísticas
❯❯ Usar Autosuma
❯❯ Trabajar con decimales
❯ Funciones de fecha y hora
❯❯ Entender cómo opera Excel con FECHAS
❯❯ Entender cómo opera Excel con HORAS
❯❯ Ejemplo
❯ Funciones lógicas
❯❯ La función SI
❯❯ Las funciones Y, O, NO, ...
❯❯ Usar cadenas de texto en fórmulas. La cadena vacía ""
❯❯ Anidamiento de funciones
❯❯ Funciones de Información que devuelven valores lógicos: funciones ES
❯ Funciones de búsqueda y referencia: BUSCARV
❯❯ La función BUSCARV
❯❯ Tipos de búsqueda con BUSCARV: búsqueda exacta y búsqueda por aproximación
❯❯ La función BUSCARH
❯ Funciones de Texto
❯❯ Ejemplo 1: Normalizar y unir cadenas de texto
❯❯ Ejemplo 2: Dividir cadenas de texto
❯ Auditoría de fórmulas
❯❯ Mostrar las relaciones entre las fórmulas y las celdas
❯❯ Mostrar las fórmulas en lugar de los resultados
❯❯ Otras opciones que podemos utilizar para auditar fórmulas
❯ Ejercicio con funciones varias
❯❯ Copiar fórmulas: las referencias absolutas, relativas y mixtas
❯❯ Dar nombres personalizados a celdas o rangos y usarlos en las fórmulas
❯❯ Opciones de cálculo automático y manual
1.- Presentación
En este tema se van a abordar las acciones relacionadas con el trabajo con libros y hojas:

❯ Trabajo con varios rangos de datos en una hoja

❯ Trabajo con varios rangos de datos en varias hojas
❯ Consolidar hojas
❯ Consolidar libros
❯ Protección de hojas y bloqueo de celdas.
2.- Trabajo con varios rangos de datos en una hoja
Si trabajamos en la misma hoja de un libro y queremos uni car datos partiendo de estructuras idénticas de
modelos resulta muy sencillo utilizando la técnica de copiar y pegar. Una de las ventajas es que la
información se muestra actualizada al momento.

OJO: hemos de tener en cuenta que las estructuras a uni car han de ser idénticas para que funcione
correctamente.

Por ejemplo, teniendo las siguientes estructuras NO será posible unir datos, ya que en la estructura de ORIGEN
1 disponemos de información mensual y en ORIGEN 2 la información es anual:

Sin embargo, en la siguiente pantalla podemos ver que los datos en ambos orígenes son mensuales por lo que
SÍ es posible la uni cación obteniendo unos resultados globales:

En el siguiente vídeo vamos a aprender como trabajar con más de un rango de datos dentro de una misma
hoja.
3.- Trabajo con varios rangos de datos en varias hojas
Otra opción que podemos encontrar es trabajar con las estructuras de datos, pero estando incluidas en
diferentes hojas del libro. Aquí el procedimiento implica indicar siempre desde qué hoja/s estamos
capturando los datos, en vez de utilizar únicamente la/s celda/s implicada/s en la operación.

En el vídeo siguiente vamos a aprender como trabajar con más de un rango de datos ubicadas en diferentes
hojas.
4.- Consolidar hojas
La consolidación de hojas nos permite unir información que tenemos en diferentes hojas, pero añadiendo
mayor detalle que en los casos vistos en los puntos anteriores. La consolidación está muy presente cuando
trabajamos con hojas enlazadas entre las que existe algún tipo de relación de dependencia.

Antes de consolidar debemos tener en cuenta que:

❯ Cada rango de datos:

❯❯ Debe poseer en la primera la el encabezado de cada columna
❯❯ Las celdas han de estar con guradas con el mismo formato
❯❯ No debe poseer celdas vacías
❯ Cada tabla a consolidar debe estar en hojas de cálculo diferentes
❯ La hoja donde contruiremos la consolidación debe estar vacía.
❯ Todos los rangos deben poseer la misma estructura.

Para aplicar la consolidación debemos elegir la opción de menú Datos y en la sección Herramientas de datos
elegir Consolidar.

Como ya hemos indicado, para poder llevar a cabo la consolidación, los rangos de datos deben ser idénticos
en estructura. Hay que destacar que es importante marcar la opción Crear vínculos con los datos de origen
para que cualquier modi cación que realicemos en los datos seleccionados de las hojas origen se re eje
automáticamente en los datos consolidados.

El mayor detalle consiste en mostrar los datos por niveles, esto es, no solo vamos a visualizar el resultado
global de la suma (o cualquier operación que estemos realizando) de los datos de todas las hojas
seleccionadas si no que podremos ver desglosadas las cantidades origen de cada hoja.

Por ejemplo, si deseamos consolidar los datos de ORIGEN 1 y ORIGEN 2, cuya información está en dos hojas
separadas denominadas "ORIGEN 1" y "ORIGEN 2" respectivamente y es la siguiente:

Lo primero que deberemos hacer es crear una hoja vacía que nos aloje la consolidación, una vez en ella, nos
ubicamos en la primera celda superior izquierda y seleccionamos Datos, opción Consolidar, que se
corresponde con el siguiente icono , para poder especi car la función que usaremos y los datos que
se van a consolidar como podemos ver en el siguiente cuadro de diálogo:

Una vez pulsemos sobre Aceptar, el proceso se llevará a cabo y nos mostrará la información consolidada. Si

nos jamos bien, aparece un icono de un símbolo suma en la parte izquierda de cada la como este .
Al pinchar sobre él, nos desglosará los orígenes de datos utilizados en la consolidación:

En este vídeo vamos a aprender con otro ejemplo práctico como consolidar datos en las hojas.
5.- Consolidar libros
Disponemos también de la consolidación de dos o más libros. Al igual que en el caso anterior, los rangos de
datos han de tener la misma estructura. La operativa es sencilla, simplemente hemos de crear un libro nuevo y
en su hoja, al introducir la fórmula, seleccionar los datos de los libros que deseemos, tal y como hemos
explicado en el apartado 4.- Consolidar hojas. OJO: hemos de abrirlos previamente para hacer la selección.

Como resultado, el nuevo libro se quedará vinculado a los datos de los cheros/libros origen de datos. De este
modo, cuando realicemos un cambio en algún dato del libro origen, al abrir el libro nuevo estos vínculos
actualizarán la información.Podemos revisar y/o modi car los vínculos a través del menú Datos, sección
Conexiones, opción Editar vínculos.

La consolidación de libros es igual que la de hojas y su n también. O sea, nos permite uni car información
que tengamos en diferentes libros en vez de en diferentes hojas.

El siguiente vídeo nos muestra cómo consolidar datos en los libros.

6.- Protección de hojas y bloqueo de celdas
Para de nir cómo y dónde se puede introducir información en nuestra hoja utilizaremos la protección de hojas
y el bloqueo de celdas. Partiendo de un modelo compuesto de datos y fórmulas podemos evitar errores, por
ejemplo al introducir datos en una zona de fórmulas, mediante desbloqueo de celdas y protección de hoja.

El desbloqueo de celdas permite indicar en cuales se pueden introducir datos, para ello se seleccionan dichas
celdas y mediante el menú Inicio, sección Celdas, opción Formato, Bloquear celda.

Una vez realizado hemos de proceder a proteger la hoja a través del menú Revisar, sección Cambios, opción
Proteger hoja. A partir de ese momento solo podremos añadir información en la zona de celdas indicada
anteriormente.

La protección de hoja nos permite de nir muchas operativas en su con guración (insertar las, eliminar
columnas…) que se adapten a nuestras necesidades.

Para eliminar la protección simplemente hemos de ir al menú Revisar , sección Cambios y pinchar sobre
Desproteger hoja. En caso que hubiéramos incluido una contraseña en la protección, nos la solicitará antes de
proceder a la desprotección de la hoja.

En el siguiente vídeo vamos a aprender cómo proteger hojas y bloquear celdas.

7.- Sugerencias de uso
1.- Antes de realizar cualquier consolidación de datos debemos comprobar estos puntos:

❯ Los rangos de datos origen deben estar en hojas de cálculo distintas, no se recomienda que esté
ubicado en la hoja de cálculo sobre la que se va a mostrar la consolidación.
❯ Para evitar problemas las tablas/conjunto de datos que se van a consolidar deben tener la misma
estructura:
❯❯ Mismo número de las y columnas de cabecera
❯❯ Mismo tipo de datos en las celdas
❯ Podemos facilitar la operación de consolidación asignando un nombre a los rangos de datos de
origen.

2.- Es recomendable que las tablas que se van a consolidar posean encabezado en la la superior y en la
columna izquierda ya que aunque no es un impedimento para consolidar facilitará mucho la tarea posterior de
revisión de resultados.

3.- Si partimos de una consolidación de datos no ordenados, por ejemplo si en la columna izquierda tenemos
meses aleatorios, la consolidación saldrá desordenada. Como opción para solucionar estos casos, al añadir el
valor Referencia cuando estamos con gurando la consolidación, podemos agregar un número al principio al
nombre de las hojas de cálculo 1Marzo, 2Abril, 3Mayo. Esto es porque cuando agregamos los rangos los
ordena por orden alfabético y cuando se despliega la tabla consolidada, las columnas aparecen en el orden
como este el rango que seleccionamos de las hojas.

4.- Si tenemos que modi car el rango de alguna de las tablas de referencia de la consolidación lo podemos
hacer directamente en el cuadro de diálogo de Consolidar.

COMBINACIONES DE TECLAS
ALT+D Activar la pestaña Datos
ALT+R Activar la pestaña Revisar (ortografía, idiomas, cambios)
CTRL+Re Pag Salta a la hoja anterior a la actual
CTRL+Av Pag Salta a la hoja siguiente a la actual
CTRL+1 Mostrar cuadro de diálogo de Formato de celdas
8.- Resumen
Al nalizar el Tema 3 hemos aprendido a trabajar con rangos de datos o estructuras de datos tanto en
diferentes hojas como en diferentes libros, siendo capaces de obtener resultados uni cados de una manera
sencilla y ágil.

La consolidación de datos en Excel signi ca recopilar, agrupar y organizar los datos ubicados en distintas
hojas de cálculo en una hoja de cálculo principal o maestra. Pudiendo pertenecer al mismo libro de Excel o a
diferentes entre sí.

Este proceso requiere del previo análisis de los datos originales con el n de determinar cuál es el método
más conveniente para consolidar y, además, para revisar la coherencia en los datos.

Siempre hemos de tener en cuenta que la consolidación mantiene la forma y características (estructura) de
los datos originales.
1.- Opciones de formato
Cuando se introduce un dato en una celda de una Hoja de cálculo, Excel establece el formato en función del
tipo de dato, si es numérico lo alinea a la derecha y si es texto a la izquierda. Este formato se denomina
estándar.

Para aplicar otro formato, hay que seleccionar la celda o el rango que queramos modi car y, utilizando los
comandos de los grupos de la cha Inicio, cambiar el aspecto de la celda: fuente, alineación, número.

Los grupos de fuente, alineación y número permiten acceder al cuadro de diálogo Formato de Celda que
muestra todas las posibilidades del formato. A este mismo cuadro de diálogo se puede acceder pulsando
sobre la celda con el botón derecho del ratón, desde el menú contextual Formato de Celdas o, desde la cha
Inicio, el comando Formato – Formato de celdas (grupo Celdas).

En la cha de Inicio también se encuentra el grupo Estilos que agrupa las opciones: Formato Condicional,
Formato como tabla y Estilos de celda.
2.- Formato Número
En Excel, puede aplicar formato a números en celdas para cosas como monedas, porcentajes, decimales,
fechas, números de teléfono o números de la seguridad social.

Para dar formatos número están disponibles en la cha Inicio, en el grupo Número. Para ver todos los
formatos de número disponibles, hacer clic en el iniciador del cuadro de diálogo situado junto a Número.

1. Seleccione una celda o un rango de celdas.

2. En la pestaña Inicio, seleccione Número en la lista desplegable.

O bien, puede elegir una de estas opciones:

❯ Presione CTRL + 1 y seleccione número.

❯ Haga clic con el botón derecho en la celda o el rango de celdas, seleccione Formato de celdas… y elija
Número.

❯ Seleccione el iniciador del cuadro de diálogo junto a número y, después, seleccione número.

3. Seleccione el formato que desee.

Para ver todos los formatos de número disponibles, haga clic en el selector de cuadro de diálogo junto a
Número, en el grupo Número de la pestaña Inicio.
A continuación, se muestra una tabla con la descripción de cada tipo de formato.

Formato Descripción

General Formato de número predeterminado que Excel aplica cuando se escribe un número. La mayor parte de
los números a los que se aplica el formato con la opción General se muestran tal y como se escriben.
No obstante, si la celda no es lo su cientemente ancha para mostrar todo el número, el
formato General redondea los números con decimales. El formato General también usa la notación
cientí ca (exponencial) para los números grandes (12 o más dígitos).

Número Se usa para la presentación de números en general. Se puede especi car el número de posiciones
decimales que se va a usar, el uso de un separador de miles y el modo en que se muestran los números
negativos.

Moneda Se usa con los valores monetarios y muestra el símbolo de moneda predeterminado junto a los
números. Se puede especi car el número de posiciones decimales que se va a usar, el uso de un
separador de miles y el modo en que se muestran los números negativos.

Contabilidad Este formato también se usa para valores monetarios, pero alinea los símbolos de moneda y las comas
decimales en una columna.

Fecha Muestra los números de serie que representan fechas y horas como valores de fecha, según el tipo y la
con guración regional (ubicación) especi cados. Los formatos de fecha que comienzan con un
asterisco (*) responden a cambios de la con guración regional de fecha y hora, que se especi can en el
Panel de control. Los formatos sin asterisco no se ven afectados por la con guración del Panel de
control.

Hora Muestra los números de serie que representan fechas y horas como valores de hora, según el tipo y la
con guración regional (ubicación) especi cados. Los formatos de hora que comienzan con un
asterisco (*) responden a cambios de la con guración regional de fecha y hora, que se especi ca en el
Panel de control. Los formatos sin asterisco no se ven afectados por la con guración del Panel de
control.

Porcentaje Multiplica el valor de la celda por 100 y muestra el resultado con un símbolo de porcentaje (%). Puede
especi car el número de posiciones decimales que desea usar.

Fracción Muestra un número como fracción, según el tipo de fracción que se especi que.

Cientí co Muestra un número en notación exponencial, en el que se reemplaza parte del número por E+n, donde E
(exponente) multiplica el número anterior por 10 elevado a n. Por ejemplo, un formato Cientí co de 2
decimales muestra 12345678901 como 1,23E+10; 1,23 se multiplica por 10 elevado a la décima
potencia. Se puede especi car el número de posiciones decimales que se desea usar.

Texto Este formato trata el contenido de una celda como texto y lo muestra tal como se escribe, incluso si se
escriben números.

Especial Muestra un número como un código postal, un número de teléfono o un número de seguridad social.

Personalizado Permite modi car una copia de un código de formato de número existente. Use este formato para crear
un formato personalizado que se agregue a la lista de códigos de formato de número.
3.- Formato Fuente
En muchas ocasiones es interesante que los textos o números aparezcan más visibles especialmente cuando
se trabaja con una hoja de cálculo grande. Se puede cambiar los formatos de fuente predeterminados como el
tipo de fuente, el color, estilo, tamaño o aplicar efectos de formato. Estos cambios se realizarán utilizando los
comandos del grupo Fuente de la cha Inicio.

Si desea que el texto o los números de una celda aparezcan en negrita o cursiva o tengan un subrayado
simple o doble, seleccione la celda y, en la pestaña Inicio, elija el formato que desee.

Para aplicar un estilo de fuente distinto, haga clic en la echa situada junto a la fuente predeterminada que es
Calibri y elija el estilo que desee.

Para aumentar o disminuir el tamaño de la fuente, haga clic en la echa situada junto al tamaño
predeterminado que es 11 y elija otro tamaño de texto. Puede escribir cualquier número entre 1 y 409.
Para cambiar el color de fuente, haga clic en Color de fuente y elija un color.

Para agregar un color de fondo, haga clic en Color de relleno situada junto a Color de fuente.

Para aplicar un formato tachado, superíndice o subíndice, haga clic en el selector de cuadro de diálogo y
seleccione una opción en efectos.
4.- Formato Alineación
El Formato Alineación permite establecer una justi cación horizontal o vertical, así como la orientación de
textos y números.

La alineación del texto hace referencia a dónde escribe el texto dentro de la celda, y la orientación al cómo lo
hace.

❯ Alineación Horizontal: puede colocar el texto de una celda para que esté centrado, alineado a la
izquierda o alineado a la derecha. Si se trata de una línea de texto larga, puede aplicar Ajustar texto
para que todo el texto sea visible.

Seleccione el texto que desee alinear y, en la pestaña Inicio, elija la opción de alineación que desee.

❯ Alineación Vertical: el texto será colocado en la parte superior, central o inferior de la celda, o bien lo
justi cará por todo el alto de ésta.
❯ Orientación: coloca el texto de manera que pueda leerse en vertical o bien de abajo a arriba o
viceversa.

Combinando estas tres opciones, podrán conseguirse diferentes efectos en las celdas de la Hoja de Cálculo.
5.- Formato Bordes
Mediante el Formato Bordes se pueden aplicar diferentes bordes a la selección de celdas que tengamos en la
pantalla.

A la hora de seleccionar los bordes de las celdas hay que tener en cuenta el orden en que se van dando las
órdenes.

1. Primero hay que determinar el estilo del borde.

2. Seleccionar el color y,

3. Finalmente en qué parte o partes de la celda se van a aplicar estos bordes.

En caso de que se quisiera hacer algún cambio a los bordes ya establecidos previamente, este orden deberá
ser el mismo.

Las opciones de la cha formato son las siguientes:

❯ Línea: Seleccionar una opción en Estilo para especi car el estilo y el grosor de la línea para un borde.
❯ Preestablecidos: Seleccionar una opción de borde prede nida para aplicar bordes o quitarlos de las
celdas seleccionadas.
❯ Color: Seleccionar un color en la lista para cambiar el color de las celdas seleccionadas.
❯ Borde: Hacer clic en un estilo de línea en el cuadro Estilo y después haga clic en los botones
preestablecidos o Borde para aplicar bordes a las celdas seleccionadas.
6.- Formato Relleno
El Formato Relleno permite establecer, además de los colores de fondo de la celda o rango de celdas
seleccionadas, diferentes efectos de relleno para dicha selección.

En esta sección hay que diferenciar entre los conceptos de color y trama.

❯ Color: hace referencia al color de fondo que va a tener la celda o rango de celdas seleccionados.
❯ Trama: las tramas son los efectos de relleno que pueden darse a la celda o rango de celdas
seleccionados, además del color de fondo que tendrán éstas. Estos efectos pueden ser muy útiles si
se va a imprimir en blanco y negro y quieren diferenciarse partes de la Hoja de Cálculo.

Las opciones de la cha formato relleno son las siguientes:

❯ Color de fondo: Fondo para las celdas seleccionadas utilizando la paleta de colores.
❯ Efectos de relleno: Este botón aplica degradado, textura y rellenos de imagen a las celdas
seleccionadas.
❯ Más colores: Agrega colores que no están disponibles en la paleta de colores.
❯ Color de Trama: Selecciona un color de primer plano en el cuadro Color de Trama para crear una trama
que utilice dos colores.
❯ Estilo de Trama: Seleccionar una trama en el cuadro Estilo de Trama para aplicar formato a las celdas
seleccionadas con una trama que utiliza los colores seleccionados en los cuadros Color de fondo y
Color de Trama.
❯ Muestra: Muestra las opciones de trama, color y efectos de relleno seleccionados.
7.- Estilos de celda
Son combinaciones de formatos prediseñados por Microsoft O ce Excel. Los estilos son una combinación de
las diferentes opciones de formato: rellenos de celda, líneas, fuente, formato de número y alto y ancho de las
celdas.

Puede aplicar formato a celdas enteras y a los datos que se encuentran dentro de una celda o un grupo de
celdas. Para entenderlo mejor, imagine que las celdas son el marco de una imagen y que la imagen que se
encuentra dentro del marco son los datos. En el formato de las celdas, se puede incluir la adición de bordes, el
color o sombreado y el cambio de tamaño o estilo de la celda.

Aplicar estilos de Excel

Seleccione las celdas.

Seleccione Inicio > estilos y seleccione un estilo.

Modi car un estilo de Excel

Seleccione las celdas con el estilo de Excel.

Haga clic en el estilo aplicado en Inicio > estilos. Seleccione Modi car.
8.- Formato como tabla
Recordamos que en la cha de Inicio también se encuentra el grupo Estilos que agrupa la opción Formato
como tabla.

Una vez que has ingresado información a una hoja de cálculo, puedes darle formato de tabla para mejorar la
presentación de los datos. Microsoft O ce Excel proporciona un gran número de estilos de tabla (o estilos
rápidos) prede nidos que puede utilizar para dar formato rápidamente a una tabla. Se pueden realizar ajustes
adicionales en el formato de tabla seleccionando opciones de estilos rápidos para los elementos de la tabla,
como las las de encabezado y de totales, la primera y la última columna, y las las y columnas con bandas.

Por ejemplo, digamos que tienes preparado tu presupuesto anual y quieres mejorar su diseño para ver mejor la
información allí registrada, ¿cómo lo puedes hacer?

Cómo insertar una tabla:

1- Selecciona las celdas a las cuales deseas darles formato de tabla.

2- En la pestaña Inicio, haz clic en el comando Dar formato como tabla.

3- Se desplegará un menú en que verás diferentes estilos predeterminados. Haz clic sobre el tema que quieres
usar.
4- Al escoger un estilo, se abrirá un cuadro de diálogo. Allí selecciona el cajón La tabla tiene encabezados y
después haz clic en el botón Aceptar. El estilo de la tabla se establecerá inmediatamente en las celdas
seleccionadas.
9.- Formato Proteger
Se establece el formato de protección que va a tener la celda o celdas seleccionadas:

❯ Bloqueada: Evita que las celdas seleccionadas se cambien, muevan, cambien de tamaño o se
eliminen.
❯ Oculta: Oculta una fórmula de una celda para que no aparezca en la barra de fórmulas cuando la celda
está seleccionada.

Estas 2 opciones no tienen efecto si la hoja no está protegida. Para proteger una hoja hay que seguir los
siguientes pasos:

1- En la cha Revisar, en el grupo Cambios, hacer clic en Proteger hoja.

2- Asegurarse de que la casilla de veri cación Proteger hoja y contenido de celdas bloqueadas está activada.

3- En el cuadro Permitir a los usuarios de esta hoja de cálculo, seleccionar las actividades que se van a
permitir que los usuarios ejecuten en una hoja protegida que contiene celdas bloqueadas.

También se puede proteger una hoja de cálculo mediante la opción Proteger hoja del menú contextual de su
pestaña.
10.- Borrar formato
Para nalizar las opciones del formato, explicaremos como borrar los formatos de las celdas.

Si cambia de opinión después de aplicar cualquier formato, para deshacer la acción, seleccione el texto y, en
la cha Inicio, haga clic en Borrar > Borrar formatos.
11.- Usos y excepciones en la DGA
El formato de las celdas es algo que utilizará todos los días. A continuación repasamos los comandos más
utilizados.

El formato de número de € en español es muy útil cuando utiliza presupuestos.

Los comandos de alineación a la izquierda, centrar, a la derecha y justi cado los tendrá que utilizar en todas
sus hojas de cálculo. Recuerde que el comando de Ajustar texto es recomendable cuando se trata de una
línea de texto larga para que todo el texto sea visible.

La función de Combinar y centrar también le será muy útil cuando quiera poner un mismo encabezado para
varias columnas o las.

En el siguiente ejemplo, primero se escribe POBLACIÓN sobre la celda E2.

y luego se combina con las celdas E2:G2.

Para poder resaltar los datos sumarizados de una la o una columna, realice tablas con líneas especí cas en
estas celdas. Cambie también el color de la celda y el tamaño de la fuente. Se muestra un ejemplo con la
población de todas las entidades singulares del municipio de Arén.
1.- Introducción al Formato Condicional
El Formato Condicional cambia el aspecto de una celda o rango de celdas en
función de una condición (o criterio) que pongamos. Estas condiciones (o
criterios) estarán basados en valores de celdas o fórmulas que
especi quemos en cada momento. De esta manera, si la condición es
verdadera, el rango de celdas basa el formato en dicha condición; si la
condición es falsa, el rango de celdas no tiene formato basado en dicha
condición.

El formato condicional resulta útil para:

❯ Comprobar visualmente los datos con nes analíticos y de presentación.

❯ Encontrar fácilmente excepciones.
❯ Reconocer tendencias importantes en los datos.

Se pueden implementar y administrar varias reglas de formato condicional que apliquen un formato visual
enriquecido, en forma de degradados de color, barras de datos y conjuntos de iconos, a los datos que cumplan
los criterios establecidos como condición.

Para aplicar formato condicional se pueden seleccionar diferentes reglas de formato:

 ❯ Aplica formato a todas las celdas según sus valores. En esta regla las celdas se resaltan utilizando los
siguientes estilos de formato:
❯ Aplicar formato únicamente a las celdas que contienen valores concretos de texto, número, o fecha y
hora.
❯ Aplicar formato únicamente a los valores de rango inferior o superior.
❯ Aplicar formato únicamente a los valores por encima o por debajo del promedio.
❯ Aplicar formato únicamente a los valores únicos o duplicados.
❯ Utilizar una fórmula que determine las celdas para aplicar formato.

En el siguiente video se detalla cómo se aplica el Formato Condicional con las Resaltar reglas de celdas, las
Barras de datos y la Escala de color.
2.- Reglas del Formato Condicional
Hasta ahora hemos visto cómo se puede aplicar el formato condicional de forma rápida. Primero se
selecciona un rango de celdas. Y luego de la Ficha Inicio, del Grupo Estilos - Formato Condicional se
selecciona una de las opciones de la lista. (Resaltar reglas de celdas, Escala de 2 y 3 colores, Barra de datos).
También se puede aplicar un Formato Condicional rápido con un Conjuntos de iconos.

Además, existe la posibilidad de aplicar un Formato condicional avanzado. Para ello, igual que con el visto
hasta ahora, se selecciona un rango de celdas, y luego de la Ficha Inicio del Grupo Estilos - Formato
Condicional – Nueva Regla.

Al crear una nueva regla aparece el cuadro de diálogo “Nueva regla de formato” dónde se deberá seleccionar
un tipo de regla y en función de la regla escogida, cambiar los elementos de descripción de la misma.

Reglas sencillas

Por ejemplo si se selecciona la regla tipo “Aplicar formato únicamente a las celdas que contengan” en Editar
una descripción de regla, en el cuadro de lista “Dar formato únicamente a las celdas con”, contiene varias
opciones como:

❯ Valor de la celda: si se quiere aplicar formato por número, fecha u hora. Se selecciona un operador de
comparación y se escribe un número, fecha u hora. También se puede escribir una fórmula que
devuelva un valor de número, fecha u hora. Las fórmulas deben comenzar por (=)
❯ Texto especí co: si se quiere aplicar formato por texto. Es necesario un operador de comparación y, a
continuación, un texto.
❯ Fechas: si se quiere aplicar formato por fecha.
❯ Celdas en blanco o Sin espacios en blanco: si se quiere aplicar formato a celdas en blanco o sin
espacios en blanco.
❯ Errores o Sin errores: si se quiere aplicar formato a celdas con valores de error o sin error.

Utilizar una fórmula para determinar las celdas a aplicar formato condicional

Hasta ahora se han visto ejemplos de aplicar un formato a la celda en base al valor de la misma celda. Si el
formato condicional es más complejo, se puede utilizar una fórmula lógica para especi car los criterios de
formato. Esto permite por ejemplo cambiar el formato de la celda en base al criterio del valor de otras celdas.
Para utilizar una fórmula en el formato condicional:

1. Se debe seleccionar el rango de celdas al que se le quiere aplicar el formato condicional.

2. En la Ficha Inicio, en el Grupo Estilos, hacer clic en la echa situada junto a Formato condicional y, después,
en Nueva regla. Aparecerá el cuadro de diálogo “Nueva regla de formato”.

3. En Seleccionar un tipo de regla, hacer clic en “Utilizar una fórmula que determine las celdas para aplicar
formato”.

4. En Editar una descripción de regla, en el cuadro de lista “Dar formato a los valores donde esta fórmula sea
verdadera”, escribir una fórmula. Se debe comenzar la fórmula con un signo igual (=) y la fórmula debe
devolver un valor lógico de VERDADERO (1) o FALSO (0)

5. Hacer clic en el botón Formato para mostrar el cuadro de diálogo “Formato de celdas”.

6. Seleccionar el número, fuente, borde o formato de relleno que se desea aplicar cuando el valor de la celda
cumple la condición y, a continuación, hacer clic en Aceptar.

Administrar reglas de formato condicional

Se pueden crear tantas reglas como se necesiten. Por eso, es interesante conocer cómo se pueden
administrar.
Cuando se aplican dos o más reglas de formato condicional a un rango de celdas, estas reglas se evalúan por
orden de prioridad en función de cómo aparezcan en el cuadro de diálogo “Administrador de reglas de formato
condicionales” (Ficha Inicio, Grupo Estilos - Formato Condicional - Administrar Reglas…)

Además de dar prioridad a las reglas, el Administrador permite:

❯ Añadir una nueva regla.

❯ Editar las reglas creadas.
❯ Eliminar reglas.
3.- Borrar Formato Condicional
En la Ficha Inicio, en el Grupo Estilos, se elige la echa situada junto a Formato condicional y, después, Borrar
reglas.

Existen cuatro opciones que se activarán dependiendo de la selección:

❯ Borrar reglas de Toda la hoja.

❯ Borrar reglas de las celdas seleccionadas.
❯ Borrar reglas de esta tabla.
❯ Borrar reglas de esta tabla dinámica.
4.- Resumen Formato Condicional
El formato condicional es muy útil para identi car en rangos de datos muy amplios elementos que nos llamen
la atención, y que fácilmente podamos leer e interpretar los datos.

A continuación se muestra un video que muestra las reglas de Formato Condicional vistas anteriormente.
5.- Usos y excepciones en la DGA
El Formato Condicional es útil para comprobar visualmente los datos con nes analíticos y de presentación.

En un rango de datos muy amplio se ve fácilmente cuales toman color o tienen un icono por algún motivo que
de namos, encontrar excepciones en un listado importante, reconocer tendencias de datos.

En la DGA se trabajan con listas de datos muy amplias, por lo que este tipo de formato es muy útil. Se muestra
a continuación, se muestra un ejemplo de un listado de los municipios de Huesca con su población.

Se han creado varias reglas de Formato condicional.

En la columna de total de población, se aplica un formato de barras de datos. Esto muestra visualmente la
población de cada municipio.

En la columna de denominación del municipio, se han aplicado varias reglas en función de la población. Si la
población es mayor de 1.000 habitantes, se muestra en verde la celda del municipio. Si la población es menor
de 100 habitantes, se muestra en rojo.
1.- Trabajando con datos
En esta unidad 5, vamos a aprender el manejo de diferentes herramientas que incorpora Excel 2016 para
trabajar con datos.

En primer lugar, veremos las Herramientas de datos, comandos que nos permiten tratar los datos incluidos en
las celdas y realizar operaciones como analizar, consolidar, validar y quitar elementos contenidos en las
tablas.

A continuación, analizaremos el menú Previsión que incorpora herramientas que facilitan el análisis de
grandes cantidades de datos y permiten obtener una previsión del comportamiento de los mismos.

Por último, aprenderemos a utilizar el comando Solver. Solver trabaja sobre un conjunto de celdas que
contienen valores variables y que están relacionadas directa o indirectamente a través de fórmulas con un
valor objetivo. Modi cando dichas variables Solver permite calcular el valor óptimo (valores máximos,
mínimos o valor exacto).

La ubicación de estas herramientas puede verse en la siguiente gura:

El comando Solver no está preinstalado en la imagen del Gobierno de Aragón. En esta unidad se mostrará
como añadir la opción Solver a la pestaña Datos.
2.- Herramientas de datos
Microsoft Excel 2016 presenta diferentes herramientas para el tratamiento de datos, que los agrupa en el
menú Herramientas de datos.

❯ Cuando se copian o se extraen datos de una base de datos de alguna aplicación, muchas veces los
datos se agrupan en las separados por algún tipo de elemento como punto y coma, coma, espacio en
blanco, etc. Texto en columnas permite distribuir los datos de cada la en columnas, donde cada
columna representa un tipo de información diferente.
❯ Relleno rápido rellena automáticamente los datos de una columna cuando detecta un patrón. Por
ejemplo, puede usar relleno rápido para separar los nombres y apellidos de una sola columna, o
combinar nombres y apellidos de dos columnas diferentes. Relleno Rápido se puede ejecutar de forma
manual desde el menú de Herramientas de Datos, o pulsando Ctrl+E. Se puede activar de forma
automática Herramientas > Opciones > Avanzadas > Opciones de edición y seleccionando la casilla
Relleno rápido automático.
❯ La utilidad Quitar duplicados permite eliminar datos ( las) repetidas
❯ En muchas ocasiones las hojas se preparan para que otros usuarios introduzcan datos en
determinadas celdas. Una herramienta muy útil en este caso es la validación de datos que permite
aceptar los datos que introduce el usuario si cumplen unas condiciones, por ejemplo, que sea un
número entero, una fecha o un valor de una lista concreta. Se puede añadir un mensaje de entrada
para facilitar que el usuario e introduzca los datos correctamente y el mensaje de error a mostrar
cuando lo ha hecho de forma errónea.
❯ Consolidar es combinar o resumir información de varias hojas de trabajo. Permite organizar la
información de diferentes hojas, realizando operaciones sencillas sobre los datos. No obstante, esta
funcionalidad se suele realizar utilizando la opción de Tablas Dinámicas que veremos en la unidad 7.
❯ Una Relación es una conexión entre dos tablas que contienen datos, donde una columna de cada tabla
es la base de la relación.

El siguiente video describe en detalle como utilizar las utilidades de Validación de datos y Texto en columnas

A continuación se profundiza en el uso de cada una de las herrmamientas del grupo Herramientas de datos.
3.- Texto en columnas
Cuando se copian o se extraen datos de una base de datos de alguna aplicación, muchas veces los datos se
agrupan en las separados por algún tipo de elemento como punto y coma, coma, espacio en blanco, etc.
Texto en columnas permite distribuir los datos de cada la en columnas, donde cada columna representa un
tipo de información diferente.

Ejemplo, tomemos un chero CSV y copiamos el contenido en una hoja, como se muestra en la siguiente
gura:

A continuación, seleccionamos la columna donde están los datos y pulsamos en Datos --> Herramientas de
Datos --> Texto en Columnas
En este caso, al ser un chero CSV los datos están separados por comas. Elegimos la opción Delimitados y
después Separación por comas. De ancho Fijo se utiliza cuando los datos no se encuentran separados por
ningún carácter, sino que las columnas son de ancho jo

Finalmente, se da a Finalizar y se obtiene la lista con los datos separados por columnas:
4.- Relleno rápido
El comando Relleno rápido rellena automáticamente los datos cuando detecta un patrón.

Veamos un ejemplo. Tenemos una pequeña lista con nombres de personas y apellidos en columnas diferentes
y queremos poner en una columna el apellido y el nombre separados por comas.

Ponemos el título de la nueva columna, en este caso le llamamos Primer Apellido, Nombre

Seleccionamos el rango de las al que queremos aplicar el Relleno rápido, C1:C8.

Pulsamos sobre Relleno rápido. Como vemos, se ha tomado el patrón de la Celda C1 que en este caso ha sido:

C1=B1+”, ”+A1

Y lo ha aplicado al resto de celdas del rango seleccionado

Si ahora, por ejemplo, quisiéramos sacar en la columna D las iniciales, podríamos escribir en la columna D1 la
primera letra de la celda A1 y la primera letra de la celda A2, con un punto al nal en cada una de ellas y
separadas por un espacio.

En este caso puede verse como el patrón no lo ha cogido de forma correcta. Para solucionarlo, habría que
deshacer y añadir otra celda de la que tomar el patrón, en este caso D2.

Se vuelve a repetir el proceso y en este caso vemos que sí que se ha rellenado de forma correcta
5.- Quitar duplicados
La herramienta Quitar duplicados elimina de forma permanente las con datos duplicados en las columnas
seleccionadas. Por ejemplo, si tomamos la siguiente lista:

Elegimos todo el rango de datos y pulsamos sobre Quitar duplicados

Si en el menú que aparece, seleccionamos todas las columnas, eliminará las las que tienen los mismos
campos en todas las columnas.
En este caso había 4 las duplicadas.

Ahora bien, si seleccionamos alguna columna en la que no queremos comprobar si los valores no están
repetidos

En nuestro ejemplo, quitamos de la selección el mes de febrero

En este caso el número de las repetidas eran 6, es decir, se han eliminado dos las que el mes de febrero
tenían valores diferentes.

ATENCION!!!

Antes de eliminar los duplicados, es recomendable copiar los datos originales a otra hoja de cálculo para que
no pierda ninguna información de forma accidental.
6.- Validación de datos
La validación de datos nos ayuda a evitar la introducción de datos incorrectos en la hoja de cálculo de manera
que podamos mantener la integridad de la información en nuestra hoja Excel.

De manera predeterminada, las celdas de nuestra hoja están listas para recibir cualquier tipo de dato, ya sea
un texto, un número, una fecha o una hora. Sin embargo, los cálculos de nuestras fórmulas dependerán de los
datos contenidos en las celdas por lo que es importante asegurarnos que el usuario ingrese el tipo de dato
correcto.

Veámoslo con un ejemplo.

A continuación, se muestra una tabla con datos de población de cada comunidad autónoma y porcentaje de
población en paro (datos de enero 2020). A partir de estos datos se calcula la población en paro de cada
comunidad, multiplicando ambas columnas.

En seguida se aprecia como hay un error en Cantabria, debido a que el separador de decimales, en lugar de ser
una coma (“,”) es un punto (“.”) y por tanto, Excel no lo identi ca como un número.

Y si se aprecia en Canarias, se obtiene un valor desproporcionado. Si se analiza, es debido a que la tasa de

paro no es un porcentaje sino un número entero.

Estos errores podrían haberse solucionado utilizando la validación de datos, al introducir los datos de la tasa
de paro.

En este caso, la validación que debería haberse puesto es la siguiente:

ATENCION!!!

La validación de datos no corrige errores ya cometidos al introducir datos. Previene que, al introducir nuevos
datos, se cometan errores

La pestaña Mensaje de entrada puede utilizarse como ayuda para destacar un mensaje que oriente al usuario
el tipo de información que ha de añadir en la celda.

La pestaña Mensaje de error se utiliza para con gurar el mensaje de advertencia que el dato introducido no es
válido.
Como utilizar el criterio de validación Lista

Imaginémonos que sobre el listado anterior queremos añadir a cada comunidad las siglas del partido que
gobierna, y lo queremos hacer a través de validación y obteniendo los datos de una lista previamente de nida.

La lista la tenemos de nida en esa misma hoja o en otra hoja y a ella tendremos que referenciar.
De esta forma, cuando vayamos a introducir un valor en cada celda de la columna G (desde Andalucía hasta
Ceuta) solo nos dejará seleccionar un valor de la lista.
7.- Consolidar
Consolidar es combinar o resumir información de varias hojas de trabajo. Permite organizar la información de
diferentes hojas, realizando operaciones sencillas sobre los datos.

Las hojas pueden estar en el mismo libro que la hoja de cálculo maestra o en otros libros. Al consolidar datos,
se ensamblan datos para que pueda actualizarlos y agregarlos de forma más sencilla según sea necesario.

Veámoslo con un ejemplo.

En un libro tenemos tres hojas correspondientes a gastos de empleados, una hoja por cada mes y queremos
consolidar la información en una nueva hoja que se llamará Datos consolidados (se podría consolidar en
cualquiera de las hojas ya existentes, los datos se modi carían como quedará en el resultado nal).

Si le damos al botón Consolidar se mostrará el siguiente cuadro de diálogo en donde se deberá especi car
cada uno de los rangos de las diferentes hojas. Puedes hacer clic en el botón de selección de referencia para
seleccionar los rangos.

Una vez especi cada la referencia se debe pulsar el botón Agregar. De la misma manera se deben agregar
cada una de las hojas hasta tener todas las referencias enlistadas.

Finalmente debes marcar los cuadros de selección de rótulos en la Fila superior y en Columna Izquierda de
manera que los datos sean presentados adecuadamente
Al pulsar el botón Aceptar, Excel realizará la consolidación de los datos y los colocará en la celda que estaba
seleccionada al inicio del proceso. En nuestro caso, se había creado una pestaña nueva denominada Datos
consolidados.

Esta funcionalidad se puede (y normalmente se suele) realizar utilizando la opción de Tablas Dinámicas que
veremos en la unidad 7.
8.- Relaciones
Excel no es una base de datos como pueda ser Access. Sin embargo, desde Excel 2013, se ofrece la
posibilidad de relacionar diferentes tablas incluidas en un mismo libro para formar un modelo de datos
relacional.

El comando Relaciones permite crear o editar las relaciones entre tablas para mostrar los datos relacionados
de distintas tablas del mismo informe.

Tenemos dos tablas en dos hojas en un mismo libro, una con nuestros clientes y otra con las facturas
emitidas a cada cliente.

Las queremos relacionar a través del campo Cliente presente en las dos tablas.
Para que el modelo de datos sea correcto, en Columna relacionada (principal) hay que incluir la columna de la
tabla que no tenga celdas repetidas. En el ejemplo, es la columna cliente de la tabla cliente, es decir, para
cada cliente solo se de ne una vez un identi cativo, su dirección, persona de contacto, etc. Pero cada cliente
puede tener varias facturas, como se ve en la tabla de facturas.

Una vez establecida la relación, se pulsa Aceptar y Excel genera un modelo de datos con las diferentes tablas
que se puede utilizar para realizar diferentes acciones, como por ejemplo, crear una tabla dinámica con
información de ambas tablas. El diseño de las tablas dinámicas se verá en la Unidad 7.

ATENCION!!

Por defecto, relaciones aparece desactivado incluso teniendo diferentes conjuntos de datos.

Eso es debido a que los datos no son considerados como parte de una tabla.

Una forma sencilla de convertirla en tabla es utilizar el comando Inicio -->Dar formato tabla
A continuación, se habilitará el comando Relaciones
9.- Usos y excepciones en el Gobierno de Aragón
Las Herramientas de datos son utilidades que aporta Excel para trabajar con gran cantidad de datos y
permiten realizar funciones como consolidar, validar, quitar duplicados o completar la información, de una
forma sencilla.

Excel 2016 complementa estas herramientas con otras utilidades como, por ejemplo:

❯ Obtener datos externos, muy similar a texto en columnas para facilitar la importación de datos.
❯ Tablas dinámicas que ayudan a consolidar los datos para su análisis, y que se verá en detalle en la
unidad 7.
❯ Power Pivot y Power Query son herramientas de modelado y conexión de datos. Estas herramientas
quedan fuera del alcance ya que están solo disponibles con la licencia Microsoft O ce Profesional. La
licencia distribuida en el Gobierno de Aragón en la mayoría de los puestos de trabajo es Microsoft
O ce Estándar.
1.- Análisis de hipótesis
La herramienta de Análisis de hipótesis en Excel, dentro de la pestaña Datos, se utiliza para analizar cómo
afectan los cambios de valores de celdas en el valor resultado de una celda dependiente de dichos valores a
través de fórmulas.

Por ejemplo, se puede especi car el resultado que se desea que produzca una fórmula y, a continuación,
determinar qué conjuntos de valores devolverán ese resultado.

Excel incluye tres tipos de análisis de hipótesis: Escenarios, Buscar objetivo, y Tablas de datos.

El siguiente vídeo muestra como podemos utilizar estas herramientas de Análisis de hipótesis

Además de estas tres herramientas, puede instalar complementos que ayuden a realizar Análisis de hipótesis,
como el complemento Solver. El complemento Solver, que se verá mas adelante en esta unidad, es similar a
Buscar objetivo, pero puede adaptarse a más variables.
2.- Escenarios
Un escenario es un conjunto de valores que Excel guarda y puede sustituir automáticamente en la hoja de
cálculo. Se pueden crear y guardar diferentes grupos de valores como escenarios y, a continuación, cambiar
entre estos escenarios para ver los distintos resultados.

Veamos como usar los escenarios con un ejemplo:

Supongamos que tenemos un producto del que hemos hecho diferentes casos de negocio estimando el
número de unidades que se pueden vender en función del descuento que apliquemos al producto. Queremos
de nir tres escenarios: pesimista, normal y optimista, en función del total percibido por las unidades vendidas.

Para de nir un escenario abrimos el menú Análisis de hipótesis --> Administrador de escenarios --> Agregar
escenario.

Damos un nombre al escenario y de nimos las Celdas cambiantes que hacen cambiar el resultado nal, en
este caso el Nº de unidades (B3) y el Descuento (B5).

Si pinchamos Aceptar, se nos pide que introduzcamos los valores en esas celdas:
En este caso, es el escenario normal, suponemos que se van a vender 1500 unidades si aplicamos un
descuento del 15%. Pulsamos Aceptar y volvemos a la pantalla donde están todos los escenarios que
hayamos de nido.

Una vez tengamos de nidos todos los escenarios, se puede crear un informe de Resumen de escenario
(Administrador de Escenarios --> Botón Resumen…) que incluirá la información de cada escenario, con los
valores de las celdas variables que hemos introducido y el resultado nal de cada uno de ellos.
Un escenario puede presentar múltiples variables, hasta un máximo de 32 valores.
3.- Tablas de datos
Las Tablas de datos y los Escenarios toman conjuntos de valores introducidos y determinan los posibles
resultados. Una Tabla de datos trabaja solo con una o dos variables, pero puede aceptar distintos valores para
las mismas.

Tomamos la hoja que hemos visto en Escenarios y queremos ver cuál sería el resultado aplicando diferentes
valores de descuento.

A continuación, añadimos en otro punto de la hoja, lo que será la tabla con los diferentes valores de descuento
que queremos analizar:

En lo que utilizaremos como cabecera de la columna de los valores resultantes (en este caso E3),
referenciamos la celda que contiene el resultado a calcular, en este caso es el Total recaudado, la celda B8.

Seleccionamos el rango de la tabla resultante, es decir $D$3:$D$12, pulsamos tabla de datos e introducimos
la celda variable:
En este caso B5, se da aceptar y se obtiene una tabla con los valores resultantes para cada porcentaje de
descuento.

Los datos resultantes no tienen ningún formato de nido, por lo que es recomendable dar formato a la tabla.
4.- Buscar objetivo
Buscar objetivo funciona de forma distinta a Escenarios y Tablas de datos, pues se deja jo el resultado que
se quiere obtener y determina el valor en un determinado campo que pueden producirlo.

Tomemos el ejemplo anterior, queremos calcular el valor del descuento a aplicar para obtener un total
facturado de 10.000€.

Se da a aceptar y se obtiene el siguiente resultado:

5.- Previsión
El valor previsión permite realizar un cálculo, en base al histórico dado, de los valores pronosticados para
fechas futuras.

Consideremos una hoja de precios de alquileres de adosados en el periodo de 2019 hasta septiembre 2020 y
queremos hacer una previsión del precio en marzo de 2021.

Excel hace una Previsión de sobre qué valores se puede mover los precios desde octubre 2020 hasta la fecha
que hemos escogido, marzo 2021, en este caso con un grado de con anza de un 95%
Obteniendo la siguiente tabla:
1.- Introducción a Solver
Solver es una herramienta de análisis incluida en Microsoft Excel, que permite calcular el valor óptimo de una
celda en función de diferentes factores o variables incluidos en otras celdas cumpliendo una serie de
restricciones o limitaciones que pueden de nirse.

Permite resolver problemas de programación lineal, en donde a partir de una función lineal a optimizar
(encontrar el máximo o mínimo) y cuyas variables están sujetas a unas restricciones expresadas como
inecuaciones lineales, el n es obtener valores óptimos bien sean máximos o mínimos.

La herramienta Solver no está activada en la imagen del Gobierno de Aragón. En la página 2 Activar Solver se
muestra como activarla.

El siguiente vídeo muestra como utilizar Solver.

2.- Activar Solver
Solver es un complemento de Excel, por lo que puede no estar visible en un primer momento en la cinta de
opciones de la pestaña Datos. Para activarlo hay que seguir los siguientes pasos:

❯ Ir a Opciones en la pestaña Archivo

❯ En Complementos (1), en la sección de la derecha abajo, al lado de administrar se selecciona primero

Complementos de Excel (está seleccionada por defecto) (2) y después se pulsa en Ir (3)
Aparece el cuadro de la gura siguiente donde se selecciona Solver (1) y se pulsa Aceptar (2):
A continuación, se instala automáticamente la herramienta Solver.

En la cinta de opciones de la pestaña Datos aparecerá el menú Análisis con el comando Solver.
3.- Trabajando con Solver
Para trabajar con Solver hay que tener muy claro los conceptos básicos de Solver y su funcionamiento. Estos
conceptos básicos son los siguientes:

❯ Celda objetivo: es la celda en la que se representa el objetivo del problema. Este objetivo puede ser
obtener el valor máximo posible, el mínimo posible o un valor exacto
❯ Celdas variables: son aquellas celdas que Solver va a poder modi car para llegar al resultado de la
celda objetivo.
❯ Limitaciones o restricciones: son las limitaciones o restricciones que se pueden con gurar para que
Solver resuelva el problema. Es decir, por ejemplo, que las variables sean enteros y mayores que cero.
❯ Datos no variables. Existen otros datos que no son modi cables.

Estos conceptos pueden verse con un ejemplo adicional.

Una oristería quiere hacer tres cestas mezclando Crisantemos, Margaritas y Gardenias.

Tiene un número limitado de unidades de cada elemento y unos objetivos máximos y mínimos de venta en
cada tipo de cesta. Cada tipo de cesta a su vez tiene un bene cio diferente. Se quiere obtener el bene cio
máximo.

Es decir, el objetivo es maximizar la ganancia que se incluirá en la celda $F$11. Esa será la Celda objetivo.

Los valores incluidos en las matrices $B$5:$E$7 y $B$9:$D$11 son Datos no variables. Permanecen intactos
en el ejercicio.

Ahora se añade una la que serán las unidades de cada cesta a vender y que constituirán las Celdas variables,
son las celdas que modi cará Solver para lograr el objetivo de la celda $F$11:
Para calcular la ganancia máxima se aplicará la siguiente formula:

=SUMAPRODUCTO(B13:D13;B11:D11)

Es decir, suma el producto de las unidades calculadas de cada cesta por la ganancia unitaria.

Por último, hay que calcular el número de unidades de cada tipo de or, ya que no puede superar el máximo
disponible.

Este cálculo se ha hecho en la columna F. La fórmula a utilizar en la celda F5 es la siguiente:

=SUMAPRODUCTO(B5:D5;$B$13:$D$13)

Adicionalmente, se ha incluido el coste de cada unidad de or, aunque ese dato no será utilizado.

¡Ya se está listo para utilizar la función solver!.

En primer lugar, se de ne la Celda objetivo, $F$11 y al buscar el valor máximo, se selecciona Máx
A continuación, se eligen las Celdas variables, las celdas que Solver ha de calcular para lograr el valor
objetivo, en este caso $B$13:$D$13

Y ahora se establecen las Restricciones.

En primer lugar, el número de unidades a vender de cada cesta ha de ser superior al inferior establecido e
inferior al máximo número de cestas a vender en cada caso. Además, el número de cestas de cada tipo ha de
ser un número entero.

❯ $B$13<=$B$9: Menor que el número máximo

❯ $B$13>=$B$10: Mayor que el número mínimo
❯ $B$13 ha de ser entero
Se ha de repetir el proceso con cada tipo de cesta. Las restricciones se añaden con el botón Agregar

También hay que añadir la restricción que el número total de cada tipo de or en todas las cestas, ha de ser
menor que el número disponible. Es decir, en todas las cestas no puede haber mas de 2.500 crisantemos.

Se elige el método de resolución GRGNonlinear y se pulsa el botón Resolver para que Solver calcule los
valores óptimos de las Celdas Variables para conseguir el valor máximo en la Celda Objetivo.

Se obtiene el siguiente resultado

Dando una ganancia máxima que es de 395€
4.- Uso de Solver en el Gobierno de Aragón
Solver no es una herramienta muy usada en el Gobierno de Aragón, de hecho se encuentra desactivada en las
imágenes instaladas en los puestos de trabajo, y hay que seguir los pasos indicados en el punto 2 de esta
unidad para que aparezca en nuestro menú de Excel.

Ahora bien, como hemos visto, Solver es una herramienta muy potente para realizar complejos cálculos en los
que se quiere obtener el valor óptimo de una celda cuyo resultado depende de múltiples variables a las que se
pueden imponer diferentes condiciones.

A lo largo de esta unidad, hemos visto un vídeo y se ha reforzado con una explicación en modo texto como
utilizar esta herramienta para familiarizar al alumno y convencerle en su uso.
LISTAS DE DATOS
Una lista de Excel es una colección de datos que guardan alguna relación entre sí y que está organizada de
manera que puedan efectuarse fácilmente determinadas operaciones sobre su contenido. Este tipo de
operaciones son del estilo de las que podemos realizar con una base de datos, como ordenar, ltrar por
criterios, consultar datos, etc.. y además podemos seguir calculando con estos datos.

1.- Obtener datos externos

Es una utilidad de Excel 2016, incluida en la pestaña Datos, es fundamental comentar el tema de la obtención
de datos de fuentes externas a Excel para poder trabajar con ellos. Las típicas fuentes para poder trabajar con
ellos son:

❯ Desde Microsoft Access

❯ Desde una página web
❯ Desde un archivo de texto (archivos txt, csv, etc)
❯ Desde otras fuentes (bases de datos sql, archivos xml, etc)

Lo bueno de poder obtener datos de una fuente externa es que no necesitas trabajar exclusivamente en dicha
fuente externa, sino que puedes seleccionar una o varias partes de los datos y trabajar con ellos en Excel para
hacer un reporte, un análisis de datos o cualquiera de las cosas que hemos visto en las clases anteriores.

Otra de las ventajas que tiene es que pueden actualizarse los datos de manera automática al abrirse el libro de
Excel o, teniéndolo abierto, refrescarse dado un número de segundos que especi quemos.

Para realizar el paso de datos de una fuente externa a una hoja de cálculo de Excel, lo haremos a través de la
opción del menú “obtener datos externos”

Ahora tenemos que seleccionar el formato de la fuente externa de la que queremos importar los datos. En
este caso lo hacemos desde un archivo de texto, esta misma opción nos serviría para archivos en formato csv.
Una vez seleccionado el archivo, tenemos que indicar si queremos los campos delimitados (por comas,
espacios, etc) o de un ancho jo. Para los campos delimitados nos pedirá que seleccionemos los separadores
(coma, espacio, etc)

Una vez nalizado, nos pedirá dónde situar los datos, podemos ponerlos en la misma ahoja de cálculo o en
otra nueva
2.- Obtener datos con una conexión existente
La ventaja principal de la conexión a datos externos es que puede analizar periódicamente estos datos en
Microsoft O ce Excel sin copiar los datos de forma repetida, lo que es una operación que puede requerir
tiempo y resultar propensa a errores. Después de conectarse a los datos externos, también puede actualizar
automáticamente (o actualizar) los libros de Excel desde el origen de datos original cada vez que se actualice
el origen de datos con información nueva.

En la pestaña datos, haga clic en conexiones existentes. Excel abrirá el cuadro de diálogo conexiones
existentes

Excel abrirá el cuadro de diálogo conexiones existentes. En la lista desplegable Mostrar, siga uno de estos
procedimientos:

❯ Para mostrar todas las conexiones, haga clic en todas las conexiones. Esta opción está seleccionada
de forma predeterminada.
❯ Para mostrar solo la lista de conexiones recientemente usadas, haga clic en conexiones en este libro.
❯ Para mostrar solo las conexiones disponibles en el equipo, haga clic en archivos de conexión en este
equipo.
❯ Para mostrar solo las conexiones disponibles de un archivo de conexión accesible desde la red, haga
clic en archivos de conexión en la red.
Seleccione la conexión que desee y, a continuación, haga clic en abrir. Le preguntará dónde situar los datos

Si no ve la conexión que desea, puede crear una conexión. Haga clic en examinar más y, a continuación, en el
cuadro de diálogo Seleccionar origen de datos, haga clic en nuevo origen para iniciar el Asistente para la
conexión de datos para que pueda seleccionar el origen de datos al que desea conectarse.

Puede conectarse a archivos simples en local y también puede hacerlo a bases de datos en la red,
introduciendo el nombre o la IP del servidor y las credenciales.
3.- Ordenar y ltrar
Ordenar una lista de datos, consiste en dividir o separar la información por catgoría, ya sea por letra, mayor a
menor, etc

Filtrar una lista de datos, consiste en dejar a la vista sólo cierta información y no toda la tabla.

Aquí tiene el vídeo explicativo:

Cuando ordena información en una hoja de cálculo, puede ver los datos de la forma que desee y encontrar
valores rápidamente. Puede ordenar un rango o una tabla de datos en una o más columnas de datos; por
ejemplo, puede ordenar los empleados primero por departamento y luego, por apellido.

¿La forma de ordenar en Excel?

Seleccionar los datos que desea ordenar. Seleccione un rango de datos, como A1:L5 (varias las y columnas)
o C1:C80 (una sola columna). El rango puede incluir títulos (encabezados) creados para identi car las las o
las columnas.

Ordenación rápida

1. Seleccione una sola celda de la columna que desea ordenar.

2. En la pestaña Datos, en el grupo Ordenar y ltrar, haga clic en para clasi car en orden ascendente
(de A a Z o del número menor al mayor).
3. Haga clic en para clasi car en orden descendente (de Z a A o del número mayor al menor).

Especi car criterios para ordenar

Use esta técnica para elegir la columna que desee ordenar, además de otros criterios como la fuente o los
colores de la celda.

1. Seleccione una sola celda en cualquier lugar del rango que desee ordenar.

2. En el grupo Ordenar y ltrar de la pestaña Datos, haga clic en Ordenar.

Aparecerá el cuadro de diálogo Ordenar.

3. En la lista Ordenar por, seleccione la primera columna en la que desea ordenar.

4. En la lista Ordenar según, seleccione Valores, Color de celda, Color de fuente o Icono de celda.

5. En la lista Orden, seleccione el orden en el que desea aplicar la operación de ordenación: de forma
alfabética o numérica, ascendente o descendente (es decir, de la A a la Z o de la Z a la A para texto o bien, de
menor a mayor o de mayor a menor para los números).

Filtrar datos en una tabla Excel

Cuando coloca sus datos en una tabla, los controles de ltrado se agregan a los encabezados de tabla
pulsando el menu Filtro.
Para realizar un ltrado rápido, haga lo siguiente:

1. Haga clic en la echa del encabezado de tabla de la columna que desea ltrar.

2. En la lista de texto o números, desactive la casilla (Seleccionar todo) de la parte superior de la lista y, a
continuación, active las casillas de los elementos que desea mostrar en su tabla.

Sugerencia: Para ver más elementos en la lista, arrastre el controlador de la esquina inferior derecha de la
galería de ltros para ampliarla.

3. Haga clic en Aceptar.

La echa de ltrado del encabezado de tabla cambia a este icono para indicar que hay un ltro
aplicado. Haga clic en el ltro para cambiarlo o borrarlo.
Filtrar por texto o números especí cos

1. Haga clic en la echa del encabezado de tabla de la columna que desea ltrar.

2. Si la columna tiene números, haga clic en Filtros de número. Si la columna tiene entradas de texto, haga clic
en Filtros de texto.

3. Elija la opción de ltrado que desee y, a continuación, introduzca sus condiciones de ltrado.

Por ejemplo, para mostrar números por encima de una cantidad determinada, elija Mayor o igual que y, a
continuación, introduzca el número que está pensando en el cuadro adyacente.

Para ltrar por dos condiciones, introduzca las condiciones de ltrado en ambos conjuntos de cuadros y elija
Y para que ambos sean verdaderos y O para que cualquiera de las condiciones sea verdadera.

Filtrar elementos por color

Si ha aplicado diferentes colores de celda o de fuente o un formato condicional, puede ltrar por los colores o
los iconos que se muestran en la tabla.

1. Haga clic en la echa del encabezado de tabla de la columna que tiene formato de color o
formatocondicional aplicado.

2. Haga clic en Filtrar por color y elija el color de celda, el color de fuente o el icono por el que desea ltrar.
Usar Auto ltro para ltrar los datos en Excel 2016

Use Auto ltro para buscar valores, o para mostrar u ocultar valores, en una o más columnas de datos. Puede
ltrar valores eligiendo opciones en una lista o buscar para encontrar los datos que desea ver. Cuando se
ltran datos y los valores en una o más columnas no reúnen los criterios de ltrado, se ocultan las las
completas.

1. Seleccione los datos que desea ltrar.

2. En la pestaña Datos, haga clic en Filtrar.

3. Haga clic en la echa en el encabezado de columna y siga uno de estos procedimientos:

o Seleccionar valores especí cos: active (Seleccionar todo) para desactivar todas las casillas y luego
seleccione solo los valores que desea ver.

o Buscar valores: en el cuadro Buscar, escriba texto o números que desee ver.
4. Haga clic en Aceptar para aplicar el ltro.

Sugerencias para ampliar la búsqueda

Cuando busque datos, puede usar ? para representar cualquier carácter único o * para representar una serie de
caracteres.

Por ejemplo, para encontrar todos los elementos relacionados con bicicletas, escriba “*bicicletas” en el
cuadro Buscar. El ltro mostrará todos los elementos que incluyan las palabras "bicicletas", como bicicletas
de paseo, bicicletas de carretera y bicicletas de montaña.
4.- Agrupar y subtotales
Saber cómo agrupar datos en Excel 2016 puede servirte para organizar la información y visualizarla de la
manera en que necesites, ya que esta opción te permitirá esconder o ver diferentes secciones de una hoja de
cálculo.

El comando Subtotal te permitirá crear grupos automáticamente y usar funciones como Suma o Porcentaje
para resumir datos en una hoja de cálculo. Por ejemplo, si tienes un gran inventario, pero solo estás
interesado en una parte de él, esta función agrupará la información para que sea más fácil encontrarla.

Aquí tiene el vídeo explicativo:

Cómo agrupar columnas o las

Paso 1:

Selecciona las las o columnas que quieres agrupar.

Paso 2:

En la pestaña Datos de la Cinta de opciones, haz clic en el comando Agrupar.

Paso 3:

Las columnas o las seleccionadas se agruparán.

Al formar un grupo de celdas, en la parte superior de la hoja de cálculo aparecerá un signo Menos (-) con el
cuál podrás esconder el grupo.
Para ver nuevamente las las o columnas escondidas, haz clic en el signo Más (+), que estará en la parte
superior de la hoja de calculo.

Cómo desagrupar columnas o las

Paso 1:

Selecciona el grupo de las o columnas que quieras desagrupar.

Paso 2:

En la pestaña Datos, haz clic en el comando Desagrupar.

Cómo crear un subtotal

Paso 1:

Organiza la información a partir de la cual crearás el subtotal.

Paso 2:

Selecciona las columnas o las de las que crearás subtotales y, en la pestaña Datos, haz clic en el comando
Subtotal.
Paso 3:

Se abrirá el cuadro de diálogo de Subtotal. Haz clic en el campo Para cada cambio en: para seleccionar la
columna donde quieres que se aplique el subtotal.

Paso 4:

Despliega el menú de Usar función haciendo clic en la echa que estará junto a esta función para elegir la
función que deseas aplicar a los grupos que se creen en la columna seleccionada.

Paso 5:

En el espacio de Agregar subtotal a, selecciona la columna donde quieres que el subtotal aparezca.

Paso 6:

Haz clic en el botón Aceptar para guardar las modi caciones hechas. La hoja de cálculo será resumida en
grupos y el subtotal estará debajo de cada grupo creado.

Al crear subtotales, la hoja de cálculo se divide en diferentes niveles. Al lado izquierdo encontrarás los
botones de los niveles. Al hacer clic en ellos, podrás controlar cuanta información estará visible. Recuerda que
Excel puede crear hasta ocho niveles.
Cómo remover subtotales

Paso 1:

Selecciona las columnas o las de las que removerás los subtotales y, en la pestaña Datos, haz clic en en el
comando Subtotal.

Paso 2:

Se abrirá el cuadro de diálogo de Subtotal. Allí, haz clic en el botón Quitar todos.

Paso 3:

Todo el contenido de la hoja de cálculo se desagrupará y los subtotales serán removidos.

5.- Usos y excepciones en DGA

5.1.- Usos habituales

La herramienta Obtener datos externos nos va a permitir llevar a Excel datos que proceden de otras fuentes de
datos, como archivos de texto o bases de datos, para su posterior tratamiento y análisis con la hoja de
cálculo.

Los ltros Excel facilitan la búsqueda de un subconjunto de datos dentro de un rango para poder trabajar con
el mismo.

Excel 2016 nos ofrece una variedad bastante amplia de opciones para la correcta gestión y organización de
los datos con el n de presentar informes, resúmenes o requerimientos administrativos de una forma clara y
sencilla. Excel 2016 incluye dos funcionalidades para hacer de la tarea de gestión algo práctico y es la
posibilidad de estructurar la información a través de los grupos y del subtotal.

5.2.- Excepciones

Se ha validado que el nuestro puesto de trabajo corporativo no tendremos ningún problema para utilizar todas
opciones mostradas.
1.- Presentación
Veamos la siguiente tabla de datos:

Observa las columnas Fecha, Vendedor, Producto y Importe. ¿Puedes identi car rápidamente qué producto es
el más rentable? ¿O bien, qué vendedor es el vendedor más destacado?, o ¿cuál ha sido el mes donde se ha
facturado más?.

Una tabla dinámica permite visualizar estos cálculos de forma muy rápida tanto en forma de tabla como en
forma de grá ca.
Una tabla dinámica es una herramienta que permite calcular, resumir y analizar datos presentándolos de una
forma que facilita realizar comparaciones, y visualizar patrones y tendencias en ellos.

En esta unidad vamos a ver:

❯ Como crear una tabla dinámica

❯ Las principales herramientas que tiene una tabla dinámica con la que poder trabajar con los datos a
presentar y el formato de la tabla
❯ Herramientas de ltrado como son la segmentación de datos y la escala de tiempos
❯ Un ejemplo concreto donde sintetizaremos lo aprendido en las lecciones anteriores.
2.- Crear una tabla dinámica
El siguiente video muestra los pasos a seguir para crear una tabla dinámica a partir de una tabla de datos.
3.- Herramientas para personalizar una tabla dinámica
Una vez creada una tabla dinámica se puede personalizar el contenido de la misma. Al posicionar el cursor
sobre una celda de la tabla, se habilita el menú Herramientas de tabla dinámica, que contiene dos pestañas:
Analizar y Diseño.

El siguiente video muestra las funcionalidades de las principales herramientas incluidas en ambas pestañas:

Adicionalmente, al pulsar sobre el botón derecho en una celda de la tabla dinámica, se abre un menú con
diferentes opciones, algunas ya comentadas en el video. El menú es diferente si la celda seleccionada es de
un campo la o de un campo valor.

En la gura siguiente se ven ambos casos siendo en la imagen de la izquierda pulsando sobre el campo la y
en la imagen de la derecha, pulsando sobre el campo valor.

❯ Copiar. Se puede copiar parte de la tabla dinámica o la tabla entera

❯❯ Para copiar una parte de la tabla, basta con seleccionar una celda o el rango de celdas que se
quiera copiar
❯❯ Para copiar toda la tabla hay que pulsar el botón derecho sobre la parte superior izquierda de
la tabla. Una vez allí, con el botón izquierdo se pulsa copiar. Si se pega en otra celda de la
misma hoja o en otra hoja, se copia la tabla con sus vínculos, es decir que cualquier
actualización afectaría a la tabla copiada (al pulsar actualizar). Ahora bien, también se pueden
pegar solo los valores, formatos, … con pegado especial.
❯ Formato de celdas y formato de números, para dar formato a las celdas y a los números como se ha
visto en el Tema 4 de este curso
❯ Actualizar. Es la misma herramienta que la vista en el video de esta lección. Permite actualizar la tabla
con los valores actuales de la tabla de datos
❯ Ordenar. Permite ordenar la columna en la que está la celda desde la que se ha pulsado el botón
izquierdo del ratón.
❯ Filtro. Solo disponible si se pulsa sobre un campo la. Similar al campo de la tabla dinámica, permite
mostrar u ocultar los elementos seleccionados. También presenta la opción de mostrar el top 10 (ó 5 ó
3, es decir se puede elegir el número de elementos a mostrar) de valores superiores o inferiores.
❯ Subtotal. Misma herramienta que la mostrada en el video
❯ Expandir o contraer. Permite aportar mayor o menor detalle de los datos añadiendo campos en la
tabla. En el ejemplo, se expande el concepto Luz aportando el gasto de los departamentos A y B.

❯ Agrupar y Desagrupar. La agrupación de datos en una tabla dinámica ayuda a mostrar un subconjunto
de datos para analizar
❯ Mover. Permite modi car el orden de presentación de los conceptos en el campo las, bien los valores
dentro de un mismo campo, o bien el orden de presentación de dos o mas campos. Es decir, en el
ejemplo de arriba, podría hacer que Transporte fuera el primer concepto o presentar el desglose de
gastos por departamento y dentro de cada departamento, por concepto.
❯ Resumir valores por…. De forma predeterminada, los datos en el área Valor siguen la siguiente regla:
los valores numéricos usan la función SUMA y los valores de texto usan la función CONTAR. Sin
embargo, con esta herramienta se puede cambiar la operación a realizar, que puede ser cualquiera de
las siguientes:
❯ Mostrar valores como… Permite presentar los valores de distintas formas. Las opciones de cálculo
disponibles son las siguientes:
❯ Con guración de campo de valor. Realiza la misma funcionalidad que la herramienta Resumir Valores
por… explicada anteriormente.
❯ Opciones de tabla dinámica. Permite modi car diferentes opciones de:
❯❯ Diseño y formato
❯❯ Qué se quiere mostrar en pantalla
 ❯❯ Qué se quiere que aparezca al imprimir
❯❯ Opciones de actualización al abrir
❯❯ Etc.
❯ Mostrar / ocultar listas de campos. Herramienta ya explicada en el vídeo

Otra herramienta muy útil es añadir un campo calculado. Los campos calculados son columnas que obtienen
su valor de la operación realizada entre algunas de las otras columnas existentes en la tabla dinámica. La
herramienta Campo calculado se encuentra en la pestaña Análizar – Menú Cálculos – Botón Campos,
Elementos y Conjuntos y opción Campos calculados

Imaginémonos que los importes vistos en las tablas anteriores son importes sin IVA y se quiere añadir el IVA.
Se procedería de la siguiente forma. Se multiplicaría el IVA sobre el campo Valores.

Obteniendo una columna adicional de los importes con IVA

Al resultado total, podemos quitarle las columnas originales de forma que solo gure el importe total con IVA.
Se quitaría el Importe sin IVA del campo Valores de la tabla dinámica
4.- Filtros en tablas dinámicas
Las tablas dinámicas son ideales para tomar grandes conjuntos de usuarios y crear resúmenes de detalles en
profundidad. A veces, desea la exibilidad agregada de poder ltrar aún más los datos sobre la marcha para
una pequeña parte de la tabla dinámica. Excel 2016 presenta varios métodos para ltrar datos:

1. Filtrado de los campos incluidos en las las y columnas.

2. La opción de auto ltro que se usa en cualquier tabla Datos – Filtro. Esta opción se encuentra desactivada
si se posiciona el cursor dentro de la tabla. Para poder activarla hay que posicionar el cursor en cualquier
celda adyacente a la tabla. En la siguiente gura son las celdas sombreadas en amarillo.
3. Agregando ltros al campo de ltro de la tabla dinámica. Aparece un ltro encima de la tabla dinámica
con la que seleccionar los elementos del campo de ltro que queremos visualizar en la tabla.

4. Opción Filtro al pulsar con el botón izquierdo del ratón sobre un campo la, explicada en el punto 3 de
esta lección: 3. Herramientas para personalizar una tabla dinámica
5. Por último, Excel 2016 permite insertar una o varias segmentaciones de datos para ltrar los datos de
forma rápida y e caz. Las segmentaciones de datos tienen botones en los que puede hacer clic para ltrar
los datos y que permanecen visibles con los datos, para que siempre sepa qué campos se muestran o se
ocultan en la tabla dinámica ltrada. El siguiente video muestra como insertar dos tipos de
segmentaciones: De cualquier tipo de dato y de escala de tiempos.
5.- Ejemplo práctico completo
El siguiente video muestra un ejemplo completo para aprender a crear, con gurar, personalizar y trabajar con
una tabla dinámica.
6.- Uso de tablas dinámicas
Las tablas dinámicas son las herramientas de Excel mas utilizadas para el análisis de datos.
Permiten resumir y ordenar información. analizando una gran cantidad de columnas y ayudando a visualizar
únicamente la información relevante, con lo que el análisis se torna más sencillo.

Su uso es compatible con la imagen incluida en los puestos de trabajo del Gobierno de Aragón, por lo que, os
animamos a perder el miedo y empezar a utilizarlas en la operativa diaria.
1.- Presentación
En este tema se van a tratar las acciones relacionadas con macros. Las macros son un conjunto de
instrucciones que nos permiten automatizar tareas que usamos de forma frecuente en Excel. Concretamente
vamos a ver los temas:

❯ ¿Qué es una macro?

❯ Elaborar una macro sencilla

Existen dos métodos de creación de macros:

❯ Utilizando la grabadora de macros que nos permite grabar las acciones para poder reutilizarlas
posteriormente.
❯ Empleando el lenguaje de desarrollo Visual Basic para Aplicaciones (VBA).

Para optar por el último modo de creación debemos tener conocimientos mínimos de programación. En este
curso abordaremos las macros desde la grabadora de macros.

En la lección 8.2 podremos ver un caso práctico con macros.

En el caso de Excel el lenguaje empleado para el uso de Macros es VBA (Visual Basic para
Aplicaciones), Excel cuenta con un "Editor de Visual Basic" o también conocido como "Editor de
VBA" que permite la creación, y/o mantenimiento de las Macros que se tengan disponibles.
2.- ¿Qué es una macro?
Las macros son un conjunto de comandos o instrucciones que realizamos y que Excel repite por aprendizaje.
Su utilidad reside en permitirnos ejecutar acciones repetitivas cómodamente evitando el error humano y
ahorrándonos tiempo.

Podemos acceder desde dos puntos:

❯ Menú Desarrollador, sección Código, opción Macros

❯ Menú Vista, sección Macros, opción Macros

Si no disponemos del menú Desarrollador debemos activarlo, para ello iremos al menú Archivo, pinchamos
sobre Opciones y seleccionamos Personalizar cinta de opciones. En la sección derecha de la pantalla
mostrada seleccionaremos la opción Desarrollador y pincharemos sobre Aceptar.
3.- Grabar macros
Vamos a aprender cómo grabar macros. Podemos preguntarnos ¿y cómo funciona esto?. Pues bien,
imaginemos que tenemos una grabadora de sonido, presionamos el botón grabar (o llamado REC) y la
grabadora comienza a grabar todos los sonidos que haya en el ambiente, ¿no?. Pues exactamente igual se
comporta la grabadora de macros, solo que en lugar de grabar sonidos, graba todo lo que hacemos en
pantalla. Esta es la idea básica.

La grabación de macros nos permite memorizar la operación o conjunto de operaciones que queremos
replicar. Una vez grabada la macro se puede almacenar en diferentes sitios:

❯ En el libro que tenemos abierto en ese momento, o sea, el libro actual. Su utilización estará limitada a
este libro.
❯ En el libro de macros personal. Si lo guardamos aquí la ventaja es que estarán disponibles para
cualquier libro u hoja que tengamos.

El libro de macros personal es un archivo oculto en la carpeta Windows AppData llamado

personal.xlsb y se carga cada vez que se inicia Excel. Si nunca hemos creado una macro, este
fichero no existirá en nuestro sistema.

Al grabar una macro debemos incluir una descripción detallada que nos permita recordar o visualizar a simple
vista qué es lo que realizaba esta macro.

La grabadora de macros de manera predeterminada graba las macros con referencias absolutas, por ello es
importante que tengamos claro el concepto de referencias. Una referencia siempre identi ca a una celda, o
rango de celdas, de una manera única. Existen dos tipos:

❯ Referencias relativas: guarda una relación la-columna donde estamos por lo que al utilizarlas fuera
de las celdas origen de grabación las acciones se generarán en relación a la celda activa
seleccionada. Por ejemplo, si estamos en la celda A1 y nos movemos a la celda A5, la macro grabará
el movimiento de 5 celdas hacia abajo. Si ejecutamos la macro en la celda B1, bajará 5 celdas y
llegaremos a la B5.

❯ Referencias absolutas: permanecen siempre jas desde el momento en que se crean, no cambia su
relación la-columna. Por ejemplo, siguiendo el caso anterior si ejecutamos la macro en la celda B1,
bajará 5 celdas pero siempre desde A1 que es la celda origen de grabación, o sea, nos posicionará en
A5 no en B5.
 Al guardar un libro con macros, Excel abre una ventana indicando "Las siguientes características
no se pueden guardar en libros sin macros: <nombreMacro>. Para guardarun archivo con estas
características, haga clic en No y, a continuación, elija un tipo de archivo habilitado para macros en la
lista Tipo de archivo. Para seguir guardando el archivo como libro sin macros, haga clic en Sí". Debemos
hacer clic en No para poder guardar la macro como un libro habilitado para macros donde la
extensión del tipo de archivo es *.xlsm.

En el siguiente vídeo vamos a ver con un ejemplo qué es una macro y cómo podemos crearla mediante la
grabación de acciones .
4.- Elaborar una macro sencilla
Hemos de tener en cuenta antes de grabar una macro planear bien los pasos y comandos que deseamos
ejecutar. Si cometemos un error al grabar una macro también se guardarán las correcciones que realicemos.
En caso contrario, debemos eliminarla y crear una nueva desde cero.

Si vamos a grabar varias macros a la vez es recomendable hacerlo desde el menú Desarrollador. Ya hemos
visto anteriormente como activarlo si no nos aparece entre las pestañas de menú.

Para comenzar la grabación, debemos pinchar en el botón Macros (recordemos que está en Menú Vista,
sección Macros, opción Macros) y en el menú que aparece pinchamos en Grabar macro. Nos aparecerá una
ventana como la siguiente imagen:

Los campos a completar hacen referencia a:

❯ Nombre de la macro: aquí asignaremos el nombre a nuestra macro.

❯ Método abreviado: podemos elegir una letra como atajo de teclado para ejecutar la macro. Aunque no
es obligatorio, es recomendable para facilitar y agilizar las ejecuciones. Hay que tener en cuenta que
en un mismo libro no vamos a poder asignar la misma combinación de teclas de método abreviado
(nos permite ejecutar la macro) más de una vez, ni pueden ser iguales a las que posee Excel
implícitamente. Si queremos asignar la combinación Ctrl+[letra] y ya existe o ya la hemos creado
previamente para otra macro de ese libro, excel nos recomendará la opción Ctrl+Mayus+[letra].
❯ Guardar macro en: podemos indicar dónde deseamos que se almacene la macro (como ya hemos
visto en el apartado 3). Bien puede ser en:
❯❯ Este libro: libro que tenemos abierto en ese momento. Solo se ejecutará al abrir ese libro
concreto.
❯❯ Libro de macros personal: se almacena en el archivo oculto personal.xlsb que se carga cada
vez que iniciamos Excel, por tanto estará disponible para cualquier libro que
creemos/abramos.
❯❯ Libro nuevo: se guardará en todos los libros nuevos que creemos a partir de este momento.
❯ Descripción: escribiremos un texto que nos ayude a reconocer la funcionalidad de la macro,
explicando para qué sirve. Cuando tengamos muchas macros, será complicado conocer qué hace cada
una.

Rellenados los campos y pulsado el botón Aceptar, la macro comenzará a grabar nuestras acciones. Debemos
tener cuidado de no hacer acciones innecesarias, realizando los pasos en orden, con calma y siempre con la
menor cantidad de pasos posibles.

El siguiente paso es detener la grabación para evitar grabar acciones innecesarias, para ello pincharemos de
nuevo en el botón Macros y seleccionaremos la opción Detener la grabación.

Ya estaremos en disposición de ejecutar la macro. Pinchando sobre el botón Macros y seleccionando Ver
macros. En la ventana que se nos muestra veremos el nombre de nuestra macro. Para ponerla en acción,
basta con que presionemos el botón Ejecutar tras seleccionar el nombre de la macro.

A la hora de grabar macros hemos de estar atentos en qué posición estamos ubicados al
comenzar la grabación y el tipo de referencias que estamos utilizando. Ya que en ejecuciones
posteriores desde celdas diferentes, su comportamiento variará y no realizará correctamente las
acciones indicadas.

En el vídeo siguiente vamos a ver el proceso de crear una macro sencilla aplicando la información vista a lo
largo del tema.
5.- Sugerencias de uso
1.- Tener en cuenta siempre los 3 pasos esenciales para la creación de macros:

❯ Analizar
❯ Ensayar
❯ Grabar

Cualquier acción equivocada que se realice a partir del momento de grabación quedará registrada como parte
de nuestra macro. De ahí que un correcto ensayo antes de grabar la macro sea interesante y fundamental.
Esto nos evitará abortar el proceso de grabación deteniéndola y teniendo que volver a comenzar la grabación
desde el principio.

2.- Es importante tener en cuenta las referencias absolutas y relativas a la hora de grabar macros.El resultado
obtenido al ejecutar macros si la referencia es absoluta tiene en cuenta la celda en la que teníamos ubicado el
cursor al momento de iniciar la grabación de la macro.

3.- Poner siempre nombres descriptivos y descripciones detalladas en la creación de macros para poderlas
identi car posteriormente con facilidad.

4.- Utilizar los atajos de teclado o métodos abreviados cuando trabajemos con macros para poder utilizar esa
combinación de teclas y realizar nuestro trabajo más rápido.

5.- Con gurar correctamente la seguridad de las macros en nuestro excel. A través de las macros, nuestro
ordenador se puede infectar por cargar macros externas que contengan código malicioso. Debemos tener
cuidado si abrimos cheros de Excel que no hemos creado nosotros. Por defecto, la con guración del tipo de
seguridad es Deshabilitar todas las macros con noti cación, pero podemos modi carla de la siguiente forma:

❯ Le damos a Archivo y seleccionamos Opciones.

❯ Se nos abre la ventana Opciones de Excel, elegimos Centro de con anza.
❯ Ahora, en el apartado Centro de con anza de Microsoft Excel le daremos al botón Con guración del
Centro de con anza…
❯ En Centro de con anza, en el menú elegiremos Con guración de macros y elegiremos, marcándolo, la
opción de seguridad que nos interese.
❯ Le damos a Aceptar.
6.- Todo lo que realicemos con macros, no puede deshacerse con CTRL + Z . De manera que debemos tomar
precauciones. Siempre que trabajemos con macros sobre algún archivo, es recomendable crear una copia de
seguridad del mismo. Así evitaremos sorpresas desagradables durante las pruebas.

7.- Podemos crear un botón para a macro en la barra de herramientas de acceso rápido, ubicada en la zona
izquierda de la parte superior de Excel, para faciliar nuestro trabajo y la ejecución de la macro. Para ello:

❯ Le damos a Archivo y seleccionamos Opciones.

❯ Se nos abre la ventana Opciones de Excel, elegimos Barra de herramientas de acceso rápido.
❯ Ahora, en el apartado Comandos disponibles en: elegiremos de la lista Macros.
❯ Seleccionamos nuestra macro (o la que deseemos) y pinchamos el botón Agregar.
❯ Una vez agregado, podemos cambiarle el icono que le asigna por defecto pinchando sobre el botón
Modi car, seleccionando uno y asignándole el nombre que deseemos en Nombre para mostrar. Una
vez elegido, pinchamos Aceptar.
❯ Pinchamos Aceptar en el cuadro de diálogo principal y ya visualizaremos nuestro icono de la macro en
la barra de herramientas de acceso rápido.

COMBINACIONES DE TECLAS
CTRL+SHIFT+F Ejecutar la macro sin tener que utilizar el menú Macro
ALT+F11 Abrir el editor de VBA
ALT+F8 Ver las macros almacenadas
6.- Resumen
Al nalizar el Tema 8: Macros, hemos aprendido que las macros son el conjunto de acciones realizadas en una
hoja de cálculo que quedan grabadas en Excel de manera que simplemente haciendo clic desde el menú
correspondiente o utilizando la combinación de teclas asignadas en el método abreviado, repiten el proceso
grabado. También:

❯ Conocer qué pueden aportar las macros a la automatización de tareas

❯ Saber cómo crear y almacenar macros
❯ Descubrir todo el proceso de grabación de macros

Macros: Caso práctico

En este tema vamos a visualizar un vídeo que nos muestra un ejemplo de creación de macros paso a paso
para a anzar los conceptos presentados en la lección previa.