Microsoft Excel 2013 (Intermedio)

Este curso ha sido desarrollado por CAE, S.A. Las marcas, logotipos, diseños de marcas, títulos y/o nombres comerciales que aparecen en el curso son propiedad de sus respectivos propietarios. CAE, S.A. es propietaria del contenido y del desarrollo del curso. Se prohíbe cualquier uso de dicho material distinto al relacionado con este curso.

Queda prohibida la reproducción total o parcial de esta obra y sus ejercicios adicionales por cualquier medio o procedimiento y la distribución de ejemplares de ella mediante alquiler o préstamo. Impreso en España Imprime: Registro Propiedad Intelectual en trámite (2015)

COMPUTER AIDED ELEARNING S.A. ©

Domicilio social: Avenida de Francia, 34 46023 - VALENCIA

www.cae.net

ÍNDICE Lección 1: Fórmulas Qué son las fórmulas Referencias a celdas Referencias a otras hojas Mover y copiar fórmulas Nombres en las fórmulas Test Ejercicio práctico

Lección 2: Funciones Qué son las funciones Suma automática Insertar una función Insertar referencias Funciones anidadas Test Ejercicio práctico

Lección 3: Formato avanzado de celdas Formatos de número Formatos personalizados Estilos de celda Buscar por formato Test Ejercicio práctico

Lección 4: Temas y formato condicional Trabajar con temas Aplicar formatos condicionales Formato condicional avanzado Insertar dibujos e imágenes Test Ejercicio práctico

Lección 5: Edición de hojas Pegado especial Organizar con paneles Ocultar filas, columnas y hojas Comparar libros Test Ejercicio práctico

Lección 6: Crear gráficos Introducción Creación del gráfico Cambiar el aspecto Elementos del gráfico Formato de elementos Añadir datos Test Ejercicio práctico

Lección 7: Colocar datos en la Web Insertar hipervínculos Moverse entre los vínculos Guardar como página web Otros formatos de archivo Test Ejercicio práctico

Lección 8: Matrices y referencias externas Fórmulas de matriz Constantes matriciales Referencias externas (vínculos) Trabajo con referencias externas Test Ejercicio práctico

Glosario

Fórmulas 1. QUÉ SON LAS FÓRMULAS

U

na fórmula relaciona valores o datos entre sí a través de ciertos operadores, y da como resultado otro valor. Las fórmulas pueden tener varias formas, ya que pueden utilizar referencias, funciones, texto, valores y nombres para realizar diferentes tareas. Por ejemplo, ahora te muestro un ejemplo de fórmula. Puedes ver cómo relaciona los valores (los números 8, 2 y 1) a través de los operadores (los operadores son, en este caso, la multiplicación y la suma) y obtiene un nuevo valor como resultado.

Para introducir una fórmula en una celda de Excel, lo primero que tenemos que saber es que, para indicar que vamos a introducir una fórmula, debemos comenzar a escribirla con el signo =. Para acabar, se o el botón haría lo mismo que al introducir datos: pulsar la tecla de la barra de fórmulas. Introducir Por defecto, en las celdas que tienen fórmulas, se ve el resultado de las fórmulas en la hoja de cálculo, mientras que la fórmula en sí aparece en la barra de fórmulas.

En la imagen anterior, observa como en la celda de la hoja de cálculo aparece 25, que es el resultado de la fórmula que has escrito (es decir, el resultado de hacer las operaciones que has indicado en la fórmula). En la barra de fórmulas está la fórmula tal como la has escrito. El resultado de una fórmula puede ser un valor numérico, un valor lógico (un valor lógico es un tipo de datos de Excel que solo puede tomar dos valores: puede ser VERDADERO o FALSO) o un texto.

Copyright © Computer Aided Elearning, S.A.

1

Fórmulas

Los operadores son símbolos que indican el tipo de operación que se tiene que hacer en la fórmula. Los operadores que puedes usar para crear tus fórmulas son:  Operadores aritméticos: sirven para hacer operaciones matemáticas, dando como resultado un número.  Suma +  Resta  Multiplicación *  División /  Porcentaje %  Función exponencial ^  Operadores comparativos o de comparación: se utilizan para comparar dos valores entre sí (por ejemplo, podrían comparar si un valor es igual a otro valor), y devuelven un valor lógico; es decir, devuelven VERDADERO o FALSO, dependiendo de si la comparación es verdad o no.  Igual =  Distinto  Mayor >  Mayor o igual >=  Menor <  Menor o igual =40 =10>3 ="Una" & " prueba" 2

Resultado 22 14 0,05 5 220 FALSO VERDADERO Una prueba

Fórmulas

Todos los operadores, menos % y -, actúan sobre dos valores. Esto quiere decir que, a partir de dos valores, obtienen un nuevo valor como resultado. El operador - puede actuar sobre dos valores (restar un número de otro) o sobre uno solo. Cuando actúa sobre un único valor, significa valor negativo. Si una fórmula tiene operadores que tienen la misma prioridad, se ejecutarán de izquierda a derecha. Si quieres cambiar el orden en el que se ejecutarán los operadores, utiliza los paréntesis.

Para introducir un número negativo en una celda, podías escribir un signo menos (-) delante del número o colocar el número entre paréntesis ( ). Pues bien, para indicar números negativos en una fórmula, no debes utilizar los paréntesis, sino que tienes que escribir el signo - delante del número. Por ejemplo, si en una fórmula indicas =2*3, el resultado será el valor negativo -6. Cuando introduces una fórmula que tiene varios operadores, los operadores tienen una prioridad u orden de cálculo, es decir, el orden en que se ejecutan los distintos operadores. El orden de evaluación de operadores, ordenados de mayor a menor prioridad es:

Por ejemplo, la fórmula =2*2+6 produce un resultado de 10 (primero hace la multiplicación, que da como resultado 4, y luego hace la suma), mientras que si pones paréntesis de esta manera =2*(2+6) se obtiene el valor 16 como resultado (primero se suman 2 más 6, y luego se multiplica el resultado por 2). Otro ejemplo podría ser =1+2*5, que da como resultado 11, y =(1+2)*5, que da 15. Además, cada operador actúa sobre unos tipos de datos determinados. Si, al escribir las fórmulas, introduces un tipo de dato distinto del esperado, Excel intentará convertir el valor al tipo esperado. Por ejemplo, en la fórmula ="20"+"30" los números se consideran como si fueran texto, al escribirlos entre comillas ("), pero esta fórmula da como resultado 50, porque Excel convierte los valores de texto a números. Sin embargo, si introducimos la fórmula ="20" & "30", ahora se concatenan los dos valores de texto y no se realiza ninguna conversión, porque el operador utilizado (&) actúa sobre caracteres de texto, dando lugar a 2030 como resultado.

Copyright © Computer Aided Elearning, S.A.

3

Fórmulas

Otro ejemplo sería ="1/4/2010"-"1/3/2010", que daría como resultado el valor 31. Como las fechas se guardan internamente como un número, la fórmula con el operador de resta es correcta. La conversión de datos también se realiza cuando el operador espera un valor lógico, tomándose el valor 0 como el valor lógico FALSO y el resto de números como VERDADERO. Si la fórmula que has escrito no se puede calcular correctamente, se indica con un mensaje de error en la celda. Los mensajes de error comienzan con el signo #. Los distintos valores de error que puede mostrar Excel al introducir fórmulas son:  #¡DIV/0!: error de división por cero, en la fórmula se ha puesto una división que divide un número por cero.  #N/A: se produce cuando un valor no está disponible para una fórmula.  #¿NOMBRE?: se ha utilizado un nombre que Excel no reconoce.  #¡NULO!: se ha indicado una intersección no válida de dos áreas.  #¡NUM!: número usado de forma incorrecta o no válida.  #¡REF!: referencia a una celda no válida.  #¡VALOR!: operando o argumento equivocado. Aparece si los valores que acompañan a los operadores de una fórmula no se pueden convertir al tipo correcto.  ######: es un marcador de columna, que indica que el tamaño de la columna es demasiado pequeño para que quepan los datos que hay. También aparece cuando se utiliza una fecha o una hora negativas.

Excel puede corregir muchos errores de sintaxis a medida que escribes las fórmulas. Por ejemplo, si falta algún paréntesis (es decir, que no hay el mismo número de paréntesis de abertura que de cierre), Excel te avisará de esto y te propondrá una posible solución.

4

Fórmulas

2. REFERENCIAS A CELDAS

C

uando un dato se debe utilizar varias veces para realizar cálculos, puede ser interesante escribirlo una sola vez en una celda y después hacer referencia a él en varias fórmulas.

Por ejemplo, en la imagen anterior puedes ver cómo se hace referencia a las celdas A4 y A6 en las fórmulas que hay en la columna C. Al escribir fórmulas que hacen referencia a estas celdas, estas fórmulas harán las operaciones que indiques con los valores que tengan estas celdas. Las referencias pueden identificar celdas o grupos de celdas en una hoja de cálculo. Algunos ejemplos de referencias a una celda pueden ser A1, D7 o F5, mientras que algunos ejemplos de referencias de grupos (rangos de celdas) son B4:C8 o A2:D6.

Así, en el primer caso tenemos que la fórmula indica que se tiene que sumar el contenido de la celda A4 y el contenido de la celda A6. Los valores de estas celdas son 10 y 3, por lo que el resultado de la fórmula es el valor 13, que es lo que se ve en la celda. Si ahora cambiaras el valor que hay en la celda A4 o el de la celda A6, los resultados de las fórmulas en las que aparecen estas celdas cambiarían. Las referencias indican la posición de las celdas en las que se deben buscar datos. Para hacer una referencia, tienes que escribir el nombre de la columna y la fila que definen la celda. En Excel hay tres tipos de referencias:  Relativas: en este tipo de referencias, la posición de la celda se basa respecto a la celda en la que estemos introduciendo la fórmula. Si cualquiera de las celdas cambia de posición, las referencias se actualizan para ajustarse a la nueva situación. Estas referencias se indican mediante el nombre de la columna y de la fila: A1, E5, B2:C4,...

Copyright © Computer Aided Elearning, S.A.

5

Fórmulas

 Absolutas: se indica la posición exacta donde se encuentra la celda en la hoja de cálculo. Para indicar que una referencia es absoluta, se pone el signo $ antes de la letra de columna y del número de fila. Por ejemplo: $B$1, $D$5,... Estas referencias indicarán siempre estas celdas de la hoja, aunque copies o muevas las celdas, o se inserten o eliminen filas o columnas delante de la celda.  Mixtas: este tipo es una combinación de las dos anteriores. Indica a Excel que tiene que encontrar la celda combinando la referencia de una fila o columna exacta con una fila o columna relativa. Para indicar este tipo de referencia, debemos escribir el signo $ antes de la fila o la columna absoluta. Por ejemplo: $A1, B$3,... Cuando hayas escrito una fórmula, podrás cambiar entre los distintos tipos de referencias que hay (relativa, absoluta y mixta) poniendo el cursor de texto en la referencia que quieras cambiar y pulsando la tecla . Por ejemplo, si tienes una referencia a la celda D2 y pulsas varias veces , esta referencia irá cambiando de la siguiente manera: D2 (relativa)  $D$2 (absoluta)  D$2 (mixta)  $D2 (mixta) Al escribir una fórmula o editar una celda que tiene una fórmula, Excel recuadra las celdas a las que se hace referencia en la fórmula con un color, que es el mismo color con el que aparece la referencia a la celda en la fórmula. Esto te puede ayudar a la hora de trabajar con las referencias. Excel te facilita el trabajar con referencias en una fórmula al poner en varios colores las celdas a las que se hace referencia en la fórmula.

6

Fórmulas

Excel tiene unos operadores que nos ayudan a la hora de construir referencias: 

Rango ( : ): crea una referencia a todas las celdas que hay entre las dos celdas que escribas. Por ejemplo, B1:C4, donde B1 es la esquina superior izquierda y C4 la inferior derecha.



Unión ( , ): crea una referencia que incluye las dos referencias. Por ejemplo: C3,C5



Intersección ( ): este operador es simplemente un espacio que crea una referencia para las celdas comunes a las dos referencias. Por ejemplo, A3:C3 B2:B4

Otra forma más sencilla de introducir referencias en las fórmulas es mediante la selección de las celdas con el ratón. Una vez has escrito el signo = en la celda, solo tienes que hacer clic en la celda a la que quieras hacer referencia o arrastrar sobre el rango de celdas para la referencia; estas celdas pasan automáticamente a formar parte de la fórmula. En Excel hay otro estilo de referenciar a las celdas, llamado F1C1 (el estilo de referencia predeterminado, que hemos estado utilizando hasta ahora, se llama estilo A1). En este estilo de referencia F1C1, la posición de la celda se indica mediante una F seguida de un número de fila y una C seguida del número de columna. Para indicar una referencia relativa en este estilo, hay que ponerla como F[2]C[1], que indica la celda que está 2 filas más abajo y una columna más hacia la derecha, o F[-2]C[-3], que indica la celda que está dos filas más hacia arriba y tres columnas más hacia la izquierda. Una referencia absoluta podría ser F3C5, que indica la celda que está en la tercera fila y la quinta columna, mientras que una referencia mixta podría ser F2C[1] o F[1]C4. Para indicar este estilo de referencia, accede a Opciones, desde la vista Backstage, ponte en la categoría Fórmulas y activa la casilla Estilo de referencia F1C1. Este modo de referencia normalmente no se usa mucho en Excel; solo se utiliza a veces cuando se crean macros.

Copyright © Computer Aided Elearning, S.A.

7

Fórmulas

3. REFERENCIAS A OTRAS HOJAS Si el nombre de la hoja tiene algún espacio en blanco o algún carácter no alfabético (es decir, que no sea letra ni número: el signo -, coma (,), punto (.), punto y coma (;), mayor (>), menor (