Ut1 manual 2016

MICROSOFT OFFICE EXCEL AVANZADO

Unidad Tem谩tica 1: Uso avanzado de F贸rmulas y Funciones

Introducci贸n

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

Curso Excel Avanzado

El interés de Excel reside, principalmente, en su gran capacidad para combinar muchos datos y obtener, en otras celdas, algún tipo de cálculo interesante; más que usarse como almacenamiento de información.

En los siguientes apartados, trataremos los siguientes aspectos: Diferencia en cuanto a sintaxis y uso entre las fórmulas y funciones.

Recordaremos el uso de referencias a celdas, rangos, hojas y libros externos. También conoceremos y evaluaremos la utilidad de referencias absolutas y relativas, así como los nombres de rangos.

Tipos de fórmulas que podemos tener en función de sus operadores, así como la sintaxis que debemos seguir para escribirlas correctamente.

Encontrar y usar correctamente las funciones que Excel pone a nuestra disposición.

Utilidad de las fórmulas matriciales y aprenderemos a crearlas...

Tipos de errores y cómo ayudarnos de la barra Auditoría para combatirlos. Asimismo conoceremos la herramienta que nos permite validar los datos de entrada.

UT1: Uso avanzado de fórmulas y funciones

1

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

Curso Excel Avanzado

Fórmulas y Funciones Los datos calculados se introducen en las celdas mediante fórmulas que usan los contenidos de otras celdas o cantidades numéricas como argumentos de cálculo.

Podemos elaborar nuestras propias fórmulas, sin embargo, existen situaciones en que la sintaxis de la fórmula es muy compleja. En estos casos Excel dispone de una amplia lista de fórmulas predefinidas denominadas funciones, que podemos incorporar directamente a nuestros libros.

Por tanto, fórmulas y funciones responden a un mismo objetivo: realizar cálculos en una celda determinada de la hoja basados en datos que se introducen directamente en la celda actual o que se obtienen de otras celdas.

Fórmulas Una fórmula es un enunciado que maneja signos de operación y datos constantes o referencias de celdas: =1+2

=A1+A2

=4*C7

=5*A3/8

Funciones Una función es una fórmula preprogramada en la que no es preciso escribir la sintaxis completa de cálculo, sino tan sólo el código de la función y los argumentos sobre los que actúa: =SUMA(1;2) =SUMA(A1;A2 =PRODUCTO(4;C7)

=MEDIA(B5:B9)

Referencias Las referencias empleadas en fórmulas y funciones pueden referirse a celdas individuales, a rangos de celdas y a rangos tridimensionales (pertenecientes a varias hojas adyacentes del mismo libro). Además, se pueden establecer vínculos con otros archivos mediante el uso de referencias externas (celdas o rangos contenidos en otros libros).

Celda y Rango

UT1: Uso avanzado de fórmulas y funciones

2

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

Curso Excel Avanzado

Recordemos que para hacer referencia a una celda que se encuentra en la hoja activa, sólo teníamos que poner la letra de su columna y el número de su fila. Por ejemplo: =A5+A6+A7+B5+B6+B7.

Recordemos también que para hacer referencia a un rango de celdas que se encuentra en la hoja activa, teníamos que indicar las referencias de la primera y última celda del rango separadas por dos puntos (:). Por ejemplo: =SUMA(A5:B7).

A tener en cuenta …

El uso de referencias simplifica la escritura de fórmulas y funciones y garantiza la coherencia global de la hoja mediante referencia a valores constantes que pueden ir cambiando sin perjuicio para los resultados, que irán cambiando automáticamente

Referencias a otras hojas y referencias tridimensionales Recordemos que para hacer referencia a una celda o rango que se encuentra en una hoja distinta de la activa, anteponemos a su referencia el nombre de la hoja entre comillas simples (') seguida de signo de cierre de admiración (!). Por ejemplo: =SUMA('hoja3'!A5:B7) .

Cuando los rangos son tridimensionales, es decir, ocupan varias hojas contiguas de un mismo libro de trabajo, hay que especificar las referencias de las hojas extremas a modo de rango. Por ejemplo, =SUMA('hoja1:hoja4'!B2:C4) suma todos los rangos B2:C4 de todas las hojas que van desde hoja1 a hoja4.

Referencias Externas Recordemos que, cuando se incluyen referencias a otros libros, hay que incluir el nombre completo del archivo libro.

Por

o

libro

entre

corchetes,

incluida

la

extensión

del

ejemplo:

UT1: Uso avanzado de fórmulas y funciones

3

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

Curso Excel Avanzado

=SUMA('[libro27.xls]hoja3'!A5:B7) .

A tener en cuenta …

En la tabla de contenidos de la Ayuda de Excel disponemos del apartado Trabajar con datos >> Fórmulas >> Crear fórmulas >> Referencias, donde encontramos información sobre este tema.

Ejemplo: Vamos a ver el procedimiento a seguir para indicar referencias a rangos de celdas como argumentos de funciones.

UT1: Uso avanzado de fórmulas y funciones

4

Servicio Andaluz de Salud Consejer铆a de Salud Area de Gesti贸n Sanitaria Norte de C谩diz

UT1: Uso avanzado de f贸rmulas y funciones

Curso Excel Avanzado

5

Servicio Andaluz de Salud Consejer铆a de Salud Area de Gesti贸n Sanitaria Norte de C谩diz

UT1: Uso avanzado de f贸rmulas y funciones

Curso Excel Avanzado

6

Servicio Andaluz de Salud Consejer铆a de Salud Area de Gesti贸n Sanitaria Norte de C谩diz

UT1: Uso avanzado de f贸rmulas y funciones

Curso Excel Avanzado

7

Servicio Andaluz de Salud Consejer铆a de Salud Area de Gesti贸n Sanitaria Norte de C谩diz

UT1: Uso avanzado de f贸rmulas y funciones

Curso Excel Avanzado

8

Servicio Andaluz de Salud Consejer铆a de Salud Area de Gesti贸n Sanitaria Norte de C谩diz

UT1: Uso avanzado de f贸rmulas y funciones

Curso Excel Avanzado

9

Servicio Andaluz de Salud Consejer铆a de Salud Area de Gesti贸n Sanitaria Norte de C谩diz

UT1: Uso avanzado de f贸rmulas y funciones

Curso Excel Avanzado

10

Servicio Andaluz de Salud Consejer铆a de Salud Area de Gesti贸n Sanitaria Norte de C谩diz

UT1: Uso avanzado de f贸rmulas y funciones

Curso Excel Avanzado

11

Servicio Andaluz de Salud Consejer铆a de Salud Area de Gesti贸n Sanitaria Norte de C谩diz

UT1: Uso avanzado de f贸rmulas y funciones

Curso Excel Avanzado

12

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

Curso Excel Avanzado

REFERENCIAS RELATIVAS Y ABSOLUTAS

Referencia Absoluta En un bloque de viviendas, la nuestra está en el tercero, y Luís vive en el quinto. Alguien llama a nuestra puerta y nos pregunta: ¿Dónde vive Luís?

Respondemos: - Luís vive en el quinto piso.

Referencia Relativa En un bloque de viviendas, la nuestra está en el tercero, y Luís vive en el quinto. Alguien llama a nuestra puerta y nos pregunta: ¿Dónde vive Luís?

Respondemos: - Luís vive dos pisos más arriba Si copiamos o desplazamos una fórmula o función, ¿qué ocurrirá con el contenido que expresaban?, ya que éste puede hacer referencia a direcciones de rangos o celdas y no a valores constantes Por ejemplo, si en la celda A3 se ha escrito la fórmula =A1+A2, y dicha celda, la A3, se copia en la celda B3, ¿qué ocurrirá?

Tal y como se presenta el ejemplo, el contenido que hemos copiado en B3 pasará automáticamente a ser =B1+B2, ya que las referencias están expresadas en modo relativo En realidad, la fórmula =A1+A2 que teníamos en la celda A3 quiere decir "suma el contenido de las dos celdas situadas inmediatamente encima de ti".

A tener en cuenta …

Por tanto, las referencias con las que hemos estado trabajando hasta ahora son referencias relativas, y tienen un significado distinto en función del lugar desde el cual hagamos dicha referencia.

UT1: Uso avanzado de fórmulas y funciones

13

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

Curso Excel Avanzado

Por ejemplo, si indicamos la referencia A1 desde la celda A3, su significado es "la celda que está dos posiciones por encima". Sin embargo, si indicamos la referencia A1 desde la celda B3, su significado es "la celda que está dos posiciones por encima y una posición a la izquierda".

Por ello, debemos ser cuidadosos a la hora de copiar fórmulas o funciones que tienen referencias relativas, ya que el significado varía en función de donde volvamos a colocarlas.

Siguiendo con el ejemplo, podemos trabajar con referencias absolutas en lugar de relativas, es decir, queriendo indicar "suma el contenido de la celda A1 al contenido de la celda A2, en cualquier caso y estés donde estés".

Para que Excel interprete esto, la fórmula debe escribirse de la forma =$A$1+$A$2, ya que el símbolo "$" convierte las referencias en absolutas.

Al copiar o desplazar esta fórmula con referencias absolutas a otro lugar, el resultados e mantiene inamovible.

Ejemplo: Veamos la utilidad del uso de referencias absolutas y relativas.

UT1: Uso avanzado de fórmulas y funciones

14

Servicio Andaluz de Salud Consejer铆a de Salud Area de Gesti贸n Sanitaria Norte de C谩diz

UT1: Uso avanzado de f贸rmulas y funciones

Curso Excel Avanzado

15

Servicio Andaluz de Salud Consejer铆a de Salud Area de Gesti贸n Sanitaria Norte de C谩diz

UT1: Uso avanzado de f贸rmulas y funciones

Curso Excel Avanzado

16

Servicio Andaluz de Salud Consejer铆a de Salud Area de Gesti贸n Sanitaria Norte de C谩diz

UT1: Uso avanzado de f贸rmulas y funciones

Curso Excel Avanzado

17

Servicio Andaluz de Salud Consejer铆a de Salud Area de Gesti贸n Sanitaria Norte de C谩diz

UT1: Uso avanzado de f贸rmulas y funciones

Curso Excel Avanzado

18

Servicio Andaluz de Salud Consejer铆a de Salud Area de Gesti贸n Sanitaria Norte de C谩diz

UT1: Uso avanzado de f贸rmulas y funciones

Curso Excel Avanzado

19

Servicio Andaluz de Salud Consejer铆a de Salud Area de Gesti贸n Sanitaria Norte de C谩diz

UT1: Uso avanzado de f贸rmulas y funciones

Curso Excel Avanzado

20

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

Curso Excel Avanzado

Referencias Mixtas.

Las referencias mixtas tienen una coordenada absoluta y una relativa. Por ejemplo: $A1 ó A$1. La coordenada con signo de dólar es absoluta y la que no lo lleva es relativa.

Vayamos un poco más allá…

Otro método para definir referencias a celdas, rangos y hojas de un libro es haciendo uso de nombre, es decir, identificarlos mediante un nombre.

Así, si en el rango B1:B4 tenemos las cifras de ventas, podemos dar a este rango el nombre VENTAS, y podríamos usarlo en funciones de la forma: =SUMA(VENTAS).

Averigüe cómo podemos dar nombres a celdas o rangos de celdas

FÓRMULAS Tres son los tipos de fórmulas básicas existentes en Excel: Aritméticas, Lógicas y de Texto.

Fórmulas Aritméticas.

Efectúan cálculos sobre operandos numéricos, que pueden ser tanto cantidades constantes como referencias a celdas cuyo contenido sea numérico.

Las operaciones asociadas a este tipo de fórmulas son:

1. Suma, con el operador + 2. Resta, con el operador 3. Producto, con el operador * 4. División, con el operador / 5. Potencia, con el operador ^ 6. Porcentaje, con el operador % Algunos ejemplos son: =3+5 =A1+A2 =$A$1+A2 =(A1-A2)/100 =A1^2 =A1*75%

UT1: Uso avanzado de fórmulas y funciones

21

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

Curso Excel Avanzado

Fórmulas de Texto.

Actúan sobre operandos constituidos por cadenas de caracteres.

El operador único asociado a las fórmulas de cadena es el signo & Los textos literales escritos en estas fórmulas deben ir siempre entre comillas dobles (no así las referencias a las celdas).

Un ejemplo sería: ="NOMBRE: "&"Pedro" que daría como resultado: NOMBRE: Pedro Otro ejemplo sería: ="Saldo: "&D4 que daría como resultado: Saldo: 140 (Si en la celda D4 se tuviera el valor 140)

Fórmulas Lógicas.

Emplean operadores lógicos sobre operandos numéricos para producir un resultado VERDADERO o FALSO Los operadores empleados en las fórmulas lógicas son:

1. Igual a, con el operador = 2. Mayor que, con el operador > 3. Menor que, con el operador < 4. Mayor o igual a, con el operador >= 5. Menor o igual a, con el operador <= 6. Distinto de, con el operador <> Así, por ejemplo, si en B8 escribimos =B7<5, se producirá resultado VERDADERO si el contenido numérico de B7 es menor que 5, de lo contrario, B8 contendrá el valor FALSO.

A tener en cuenta...

Excel asocia el valor lógico 1 a VERDADERO y el valor 0 a FALSO, lo cual puede ser utilizado en las fórmulas aritméticas con su valor correspondiente.

Siguiendo el ejemplo anterior, si en B9 escribimos la expresión =5+B8, produciría el resultado 6 si B8 fuera verdadero, y 5 si fuera falso.

Orden de Prioridad.

UT1: Uso avanzado de fórmulas y funciones

22

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

Curso Excel Avanzado

El orden de prioridad utilizado por Excel en sus operaciones de fórmulas es:

1. Identificación del signo de un número 2. Porcentaje 3. Potencia 4. Producto/División 5. Suma/Resta 6. Concatenación de texto 7. Comparaciones (Igual a, mayor, menor, distinto Este orden de prioridad puede alterarse con el uso conveniente de paréntesis, utilizados en su puro y estricto sentido matemático y lógico: =(A7-B8)*((A10+8)/C4)

FUNCIONES

Los

elementos

básicos

que

componen

la

estructura de funcionamiento de una función son: su código o nombre, los argumentos y el separador de argumentos.

Código de la Función.

El código o nombre de la función siempre va precedido del signo igual. Por ejemplo: =SUMA

Argumentos.

Son los datos que usa la función para realizar los cálculos. Pueden ser números, cadenas de texto (van entre comillas dobles, sobre todo si tienen espacios), valores lógicos, referencias u otras funciones.

Separador de Argumentos.

Cuando una función posee varios argumentos, éstos se separan por un carácter especial (punto y coma).

Ejemplo:

UT1: Uso avanzado de fórmulas y funciones

23

Servicio Andaluz de Salud Consejer铆a de Salud Area de Gesti贸n Sanitaria Norte de C谩diz

Curso Excel Avanzado

Veamos muestra como insertar funciones usando el asistente.

UT1: Uso avanzado de f贸rmulas y funciones

24

Servicio Andaluz de Salud Consejer铆a de Salud Area de Gesti贸n Sanitaria Norte de C谩diz

UT1: Uso avanzado de f贸rmulas y funciones

Curso Excel Avanzado

25

Servicio Andaluz de Salud Consejer铆a de Salud Area de Gesti贸n Sanitaria Norte de C谩diz

UT1: Uso avanzado de f贸rmulas y funciones

Curso Excel Avanzado

26

Servicio Andaluz de Salud Consejer铆a de Salud Area de Gesti贸n Sanitaria Norte de C谩diz

UT1: Uso avanzado de f贸rmulas y funciones

Curso Excel Avanzado

27

Servicio Andaluz de Salud Consejer铆a de Salud Area de Gesti贸n Sanitaria Norte de C谩diz

UT1: Uso avanzado de f贸rmulas y funciones

Curso Excel Avanzado

28

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

Curso Excel Avanzado

A tener en cuenta...

Al introducir una función en una celda, podemos hacerla de dos formas: 1. Por escritura directa de la función, para lo cual hay que conocer la sintaxis completa.

2. Con el apoyo del Asistente de funciones, método más seguro, ya que se apoya en el uso de cuadros de diálogo y ventanas de ayuda.

Para incorporar directamente la lista de argumentos teóricos a una función a medio escribir, pulsaremos CTRL+MAYUS+A. Después sustituiremos cada argumento teórico por su valor.

En relación a las funciones, ¿Qué es el botón Autosuma? ¿Para qué sirve?

Los argumentos de una función pueden ser obligatorios u opcionales, según los casos. Incluso, algunas funciones pueden no necesitar argumentos, pero siempre han de escribirse los paréntesis para informar a Excel de que se trata de una función Por ejemplo: =PI() Reto a resolver: En base a las funciones SUMA y PRODUCTO, ¿cómo expresarías la fórmula =5+A6*H8?

Fórmulas Matriciales

Básicamente, una fórmula matricial es aquella que, usando argumentos matriciales, puede ejecutar varias operaciones y devolver un único resultado o varios resultados.

Las fórmulas matriciales actúan en dos o más conjuntos de valores denominados argumentos

UT1: Uso avanzado de fórmulas y funciones

29

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

Curso Excel Avanzado

matriciales. Cada argumento matricial tiene el mismo número de filas y de columnas.

Este tipo de fórmulas son una optimización de lo que hemos visto hasta ahora.

La mayoría de las veces, una gran parte de los datos que intervienen en una fórmula, están localizados en columnas que tienen la misma longitud que la que va a albergar los nuevos cálculos. Dicho de otra forma, los datos están "emparejados" por columnas.

Hasta ahora hemos visto que una buena técnica es crear la fórmula en la primera celda de la columna de resultados y usar el controlador de relleno para copiar la fórmula haciendo que las referencias relativas y absolutas cumplan con su función.

Existe una forma más óptima de llevar a cabo estos cálculos: mediante el uso de fórmulas matriciales, que admiten como operandos rangos de celdas y devuelven como resultado otro rango del mismo tamaño.

Aunque el resultado final es el mismo, el número de operaciones internas que Excel tiene que realizar para un caso y otro es completamente distinto, siendo éste último mucho menor frente al método tradicional.

Veamos cómo trabajar con fórmulas matriciales.

UT1: Uso avanzado de fórmulas y funciones

30

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

Curso Excel Avanzado

A tener en cuenta.

Podemos utilizar una fórmula matricial para realizar varios cálculos que generen un único resultado. Este tipo de fórmula matricial permite simplificar un modelo de hoja de cálculo sustituyendo varias fórmulas distintas por una sola fórmula matricial.

Para ello, hacemos clic en la celda en que queremos introducir la fórmula matricial y escribimos la fórmula matricial, pulsando al final Mayús+Control+Intro.

Por ejemplo, la siguiente calcula el valor total de una matriz de precios de cotización y acciones sin utilizar una fila de celdas para calcular y mostrar los valores individuales de cada cotización: Cuando se escribe la fórmula ={SUMA(B2:D2*B3:D3)} como fórmula matricial, multiplica las acciones y el precio correspondiente a cada cotización, y luego suma los resultados de estos cálculos.

UT1: Uso avanzado de fórmulas y funciones

31

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

Curso Excel Avanzado

Errores, Auditoría y Validación de Datos Es lógico que se cometan errores a la hora de trabajar en una hoja de cálculo.

Excel nos proporciona unas interesantes herramientas que nos pueden ayudar en el seguimiento de estos errores.

Podemos decir que existen dos tipos de errores en Excel:

-

Errores semánticos. Aquellos que Excel no detecta pero nosotros, como usuarios, sí.

Tienen que ver con el contenido y no con la forma.

-

Errores sintácticos. Son los que provocan la aparición de mensajes de advertencia.

Cuando hay un problema con una fórmula o función, se devuelve un valor de error. La siguiente tabla explica los distintos errores que pueden resultar de una fórmula errónea: Valor de error

Causa

########

Se produce cuando el ancho de una columna no es suficiente o cuando se utiliza una fecha o una hora negativa

#DIV/0!

Se produce cuando se divide un número por cero (0).

#N/A

Distintos significados dependiendo de las circunstancias( normalmente significa que no hay disponible un valor o que se utilizó un argumento no adecuado)

#NOMBRE?

Referencia a un nombre no válido. Se produce cuando Excel no reconoce el texto de la fórmula.

#NULO!

Este error se genera cuando se especifica una intersección de dos áreas que no se intersecan, no se cruzan. El operador de intersección es un espacio entre referencias ( por ejemplo, si las áreas con nombre Enero y Beneficios no se cruzan, =Enero Beneficios devuelve #¡NULO! )

#NUM!

#REF!

Se produce cuando se escriben valores numéricos no válidos en una fórmula o una función, la utilización incorrecta de un número (argumento numérico no aceptable, como RAIZ(-1) o la fórmula devuelve un número demasiado grande o demasiado pequeño para ser representado por Excel) Referencia de celda no válida

#¡VALOR!

Se produce cuando se utiliza un tipo de argumento o de operando incorrecto

UT1: Uso avanzado de fórmulas y funciones

32

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

Curso Excel Avanzado

Barra de herramientas Auditoría de Autoformas.

Nos permite revisar los cálculos de la hoja en busca del error.

Podemos abrirla desde el menú Herramientas >> Auditoría de fórmulas

A tener en cuenta ...

Muchos de los errores que afectan a las fórmulas y funciones tienen su origen en la naturaleza del contenido de la celda que sirve de operando. Pues bien, es posible filtrar la entrada de datos para minimizar la probabilidad de incurrir en estos fallos El menú Datos >> Validación nos proporciona un método eficaz para establecer qué tipos de datos están permitidos y cuáles no en una celda o rango.

Trabajaremos con el cuadro de diálogo Validación de datos

UT1: Uso avanzado de fórmulas y funciones

33

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

Curso Excel Avanzado

De las tres pestañas que tenemos disponibles, la más importante es la primera; las otras

dos simplemente configuran los mensajes de advertencia que el usuario verá en la

pantalla cuando intente violar las normas de inserción de datos.

UT1: Uso avanzado de fórmulas y funciones

34