Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda...

268
Microsoft Excel 2003 Nivel Avanzado Diana Marcela Sanchez [email protected] http://kybele.es/BBVA/excel/

Transcript of Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda...

Page 1: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

Microsoft Excel 2003

Nivel Avanzado

Diana Marcela Sanchez

[email protected]

http://kybele.es/BBVA/excel/

Page 2: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

2 2

Agenda

Empezar a trabajar con EXCEL Personalización del Entorno de Trabajo

Edición de Celdas

Fórmulas y Funciones (introducción)

Selección de Celdas Trabajar en Modo Grupo

Referencias a Celdas

Nombrar Celdas

Opciones Avanzadas de Formato y Presentación Alineación de Texto

Formatos Condicionales

Precisión de Pantalla

Uso de Estilos

Paneles

Definición de Esquemas

Impresión y Presentación

Vistas Personalizadas

Protección de Celdas y Hojas

Funciones Funciones de Texto

Funciones Matemáticas

Funciones Lógicas

Funciones (…)

Funciones de Información

Funciones de Búsqueda y Referencia

Funciones de Fecha y Hora

Funciones Estadísticas

Funciones Matriciales

Funciones Financieras

Herramientas para Análisis

Trabajar con Datos Datos Consolidados

Filtros

Filtros Avanzados

Macros

Validación de Datos

Subtotales

Función Buscar Objetivo

Escenarios

Tablas Dinámicas

Trabajar con Gráficos

Extracción de Datos y Libros Compartidos

Page 3: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

3 3

Barra de título

Barra de menú

Barra de herramientas

Barra de formato

Barra de fórmulas

Barra de etiquetas

Barra de desplazamiento

Panel de tareas Recuperación de archivos

Personalización del Entorno de Trabajo

Page 4: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

4 4

Mostrar y personalizar las barras de Herramientas

Una importante utilidad en Excel (como en el resto de aplicaciones integradas en la suite de Office), es la personalización de barras de herramientas.

Permite tener a mano las herramientas de uso mas comunes.

Botón Derecho sobre cualquier barra

Personalizar

Page 5: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

5 5

Menú Herramientas

Para modificar las barras, es necesario activar la opción de personalización Menú Herramientas Personalizar Barras de

Herramientas

Menú Ver Barras de Herramientas ó

Botón Derecho sobre cualquier lugar de una barra Personalizar

A partir de ahí, podemos: Añadir/Eliminar botones para comandos

Reorganizar Menús y Submenús

Cambiar el aspecto de los botones Crear/Eliminar barras

Page 6: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

6 6

Mostrar y personalizar las barras de Herramientas

Ejercicio:

Crear un nuevo libro de EXCEL

En la hoja por defecto (Hoja1), crear una barra de Herramientas, llamada “miBarra”

Dicha barra deberá tener los siguientes elementos: De la categoría “Archivo”, la opción: Nuevo

De la categoría “Insertar”, las opciones Filas y Columnas

De la categoría “Datos”, las opciones Autoesquema y Borrar Autoesquema

De la categoría “Ventana”, la opción Inmovilizar secciones

Page 7: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

7 7

Edición de Celdas

Hay varias formas de editar una celda Si hacemos clic sobre ella y empezamos a escribir

estaremos modificando el contenido de la celda usando la barra de fórmulas

Podemos seleccionar la celda y pulsar F2

Podemos seleccionar la celda y hacer clic sobre la barra de fórmulas

Cambiar comportamiento predeterminado: Menú Herramientas Opciones Modificar Marcar la opción Modificar en Celda

Al pulsar F2, iremos directamente a la barra de fórmulas

Importante: cuando estamos en modo “Edición” hay muchas opciones que aparecen deshabilitadas.

… y muchas veces no nos damos cuenta de que estamos en modo “Edición

Page 8: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

8 8

Edición de Celdas

Seleccionar rangos de celdas para editar Cuando EXCEL llega al final de la columna, comienza por

la primera fila de la siguiente columna

Introducir los mismos datos en varias celdas: Seleccionar el rango de celdas

Introducir el valor

Pulsar CTRL + ENTER

Introducir puntos decimales automáticamente Menú Herramientas Opciones Modificar

Número fijo de decimales

Page 9: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

9 9

Edición de Celdas

AUTORELLENO Si arrastramos con el botón derecho en lugar del

izquierdo se mostrará un pequeño menú de opciones de relleno

AUTOCOMPLETAR Podemos acceder a los anteriores valores de la columna

con la opción Elegir de la lista desplegable del menú contextual para la celda

Sólo reconoce valores adyacentes: una celda en blanco rompe la serie

Page 10: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

10 10

Edición de Celdas

Para introducir saltos de línea en una celda

Usar ALT + ENTER

Podemos usar las opciones de auto corrección para

simplificar la entrada de datos

Menú Herramientas Opciones de Autocorrección

Podemos añadir abreviaturas para el texto que

introduzcamos con frecuencia

Las entradas que añadamos estarán disponibles para

cualquiera de las aplicaciones de Office

Page 11: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

11 11

Edición de Celdas

Inserción de fracciones Podemos mostrar el valor como una fracción usando las

opciones de formato

Al seleccionar la celda, la barra de fórmulas muestra el valor computado

En general, la barra de fórmulas siempre muestra el valor real almacenado

Las opciones de formato SÓLO se utilizan para presentar el valor en la celda

Page 12: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

12 12

Edición de Celdas

Inserción de porcentajes Para evitar problemas, es recomendable utilizar el

símbolo ‘%’ a la hora de introducir un porcentaje en una celda

EXCEL lo reconocerá automáticamente

Insertar, Eliminar y Mover hojas

Cambiar nombre de las hojas Doble clic sobre la etiqueta

Cambiar el color de las etiquetas Clic derecho sobre la etiqueta escogida

Page 13: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

13 13

Manejo de Excel

Ejercicio: Abrir el libro Prácticas. Insertar una nueva hoja en blanco

Renombrar la hoja como Ejercicio 1

Introducir un conjunto de números con formato de moneda

Calcular la suma de los números

Guardar el trabajo en disco

Page 14: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

14 14

Fórmulas y funciones

Las fórmulas constituyen el núcleo de cualquier hoja de cálculo. De hecho, mediante fórmulas, se llevan a cabo todos los cálculos que se necesitan.

Las funciones permiten hacer más fácil el uso de EXCEL e incrementar la velocidad de cálculo. Si se las compara con las fórmulas, son más rápidas, ocupan menos espacio en la barra de fórmulas y reducen los errores (ya que no es necesario conocer la sintaxis de la fórmula).

Las funciones actúan sobre los datos contenidos en una celda o conjunto de celdas igual que las fórmulas lo hacen sobre los números.

Page 15: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

15 15

Fórmulas

Operaciones matemáticas que constan de operadores, expresiones, etc.

Ver “Precedencia de Operadores” Como ya hemos visto, se usará “:” para definir rangos

de celdas

Page 16: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

16 16

Fórmulas

Introducción de fórmulas Se puede hacer tanto desde la Barra de Referencias

como en la propia celda activa.

Comenzar tecleando el signo igual (=). Así se indica al programa que lo que se va a introducir en la celda activa es una fórmula.

Teclear valores numéricos, referencias a celdas, funciones o nombres, todos ellos separados por los correspondientes operadores (por ejemplo: +, -, *, /).

Terminar la introducción de la fórmula pulsando ENTER

Page 17: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

17 17

Fórmulas: operadores

SÍMBOLO OPERADOR PRECEDENCIA

^ Exponenciación 1

* Multiplicación 2

/ División 2

+ Suma 3

- Resta 3

& Concatenación 4

= Igual a 5

< Menor que 5

> Mayor que 5

En cualquier momento podemos romper el orden de

precedencia de operadores utilizando paréntesis

Page 18: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

18

Uso de Operadores: ejemplos

FÓRMULA TIPO RESULTADO

=”Part-”&”23A” TEXTO “Part-23A”

=A1&A2 Concatena el valor de ambas celdas

=6^3 NÚMERO 6 al cubo 216

=216^(1/3) NÚMERO Raíz Cúbica de 216 6

=A1<A2 LÓGICO VERDADERO / FALSO

=A1<=A2 LÓGICO …

=A1<>A2 LÓGICO …

18

Si tienes problemas para modificar una fórmula, conviértela a texto

(eliminando el signo ‘=‘ al principio), realiza las modificaciones y

vuelve a convertir el texto en fórmula

Page 19: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

19 19

Auditoría de Fórmulas

EXCEL proporciona un auditor de fórmulas capaz de localizar errores en las mismas

Menú Ver Barras de Herramientas Auditoría de Fórmulas Rastrear celdas precedentes Rastrear descendientes Señalar datos incorrectos

EJEMPLO

Auditoría de Fórmulas

Page 20: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

20

Errores trabajando con fórmulas

#DIV/0! intento de dividir por 0 La celda que contiene el dividendo está en blanco Reparar: = SI(C2=0,””,(C2-B2)/C2)

= SI(ESERROR((C2-B2)/C2),””,(C2-B2)/C2)

#N/A cuando alguna función de búsqueda no es capaz de encontrar un resultado

#NOMBRE? la fórmula contiene … … un rango de celdas o un nombre no definido

… un texto que es interpretado como nombre no definido (p.e.: un nombre de función incorrecto)

… una función incluida en un complemento no instalado

Page 21: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

21

Errores trabajando con fórmulas

#NULO! la fórmula trata de usar la intersección de 2 rangos que no interseccionan =SUMA(B5:B14 A16:F16) #NULO! =SUMA(B5:B14 A9:F9) B9

#NUM! Pasar un argumento no numérico por error Pasar un argumento incorrecto (=RAIZ(-1))

Una función que funciona realizando iteraciones no es capaz de converger (TASA …)

El resultado es demasiado grande (o pequeño)

Page 22: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

22

Errores trabajando con fórmulas

#REF! uso de una referencia inválida Se borra una celda referenciada por la fórmula Se copia la fórmula a una celda que invalida la

referencia relativa (p.e.: copio “=A1-1” de A2 a A1)

Corto y pego una fórmula en una celda que es referenciada en la propia fórmula

#VALOR! Tipo incorrecto de un argumento (p.e.: sumar un número

y una cadena de texto)

Se pasa como argumento un rango cuando debería ser un valor único

Olvidar insertar una fórmula en formato matricial

Page 23: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

23 23

Funciones

Las funciones aceptan datos de entrada o argumentos, y devuelven un resultado después de operar con esos datos

Pueden verse como fórmulas predefinidas

En la mayoría de los casos, el resultado es un valor numérico Pero también pueden devolver resultados de tipo texto,

referencias, valores lógicos, matrices o información sobre la hoja de cálculo.

Page 24: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

Los argumentos son la información entre paréntesis que pasamos a la función para que realice su tarea

Cada dato es un argumento

Se usará “;” para distinguir los distintos parámetros que se pasan a una función

Ejemplo: SUMA (B5; A3; D3:E6)

Podemos usar nombres de celdas a la hora de escribir fórmulas Mientras estamos editando la fórmula Insertar Nombre Pegar

F3 estando sobre la barra de fórmulas

24

Funciones: argumentos

24

ARGUMENTOS

Page 25: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

25 25

Botón AUTOSUMA

El botón AUTOSUMA proporciona un acceso rápido a las funciones más comunes Los argumentos de la función son deducidos a partir

de la posición de la celda

Page 26: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

26

Funciones

Asistente de Funciones El Asistente de Funciones sirve de guía a

través de todo el proceso de introducción de una función y proporciona una breve explicación, tanto de la función, como de cada uno de sus argumentos. Menú Insertar Función

Botón Insertar Función

Botón AUTOSUMA Más Funciones

26

Page 27: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

27 27

Funciones

27

Búsqueda de Función Descripción

Por Categorías

Ayuda contextual

Argumentos de Función Obligatorios/Opcionales

Page 28: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

28 28

Funciones

Es recomendable usar la ayuda en línea para conocer cómo utilizar cada función

Introducir el nombre de la función en MIN y

si EXCEL la reconoce, automáticamente la

pasará a MAY

Page 29: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

29 29

Selección de Celdas

CTRL + ESPACIO toda la columna MAY + ESPACIO toda la fila CTRL + E todas las celdas de la hoja CTRL + * todas las celdas adyacentes que

contengan valores CTRL + MAY + ↓ todas las celdas adyacentes en

esta columna La columna entera si no hay celdas adyacentes

CTRL + MAY + todas las celdas adyacentes en esta fila

F4 Repite la última acción que se ha realizado en Excel

Page 30: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

30 30

Modo Grupo

Podemos verlo como una forma sencilla de trabajar con datos consolidados Seleccionar un conjunto de hojas Clic sobre la primera y, manteniendo apretada la tecle CRTL,

clic sobre el resto

Liberamos la tecla CTRL y …

… al realizar modificaciones sobre cualquiera de ellas, los cambios se replican en el resto

Ejemplos: repetir encabezados en varias hojas, redimensionar filas y/o columnas en varias hojas, etc.

Page 31: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

31 31

Modo Grupo

Podemos utilizar esta opción para definir fórmulas que computan las mismas celdas de varias hojas (hojas resumen o acumuladores) Comenzar a introducir la fórmula en la hoja resumen

Seleccionar la celda que contiene el valor que se quiere computar en la primera hoja

Manteniendo MAY pulsado hacer clic sobre la última hoja del intervalo

Pulsar INTRO

La fórmula computa el valor de la misma celda (relativa) en el rango de hojas

Page 32: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

Ejercicio

Cree un nuevo libro de EXCEL

Inserte 13 hojas y nombre las 12 primeras como los 12 meses del año

Utilice la edición en modo grupo para: Que todas ellas presenten el siguiente aspecto

Que la hoja 13 calcule los totales de ventas a partir de las 12 restantes

32

Page 33: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

33 33

Seleccionar celdas especiales

Podemos buscar celdas que contengan fórmulas o aquellas que dependan de la actual o … Menú Edición Ir a … Especial Si la lanzamos teniendo seleccionado un rango, la

búsqueda se restringe a las celdas del rango

En caso contrario, se realiza sobre todas las celdas de la hoja

Page 34: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

34 34

Referencias a celdas

Las referencias a celdas identifican una celda o grupo de celdas de un libro

Se usan para vincular el resultado de una fórmula a las celdas referenciadas

La referencia de la celda activa se muestra en el cuadro situado a la izquierda de la Barra de referencias

Los rangos de celdas se introducen por medio del operador dos puntos (:), que separa la referencia a la primera celda del rango, de la de la última celda

Ejemplo: (B2:D3)

Ver estilos de referencias

L1C1

Page 35: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

35 35

Referencias a celdas EJEMPLO

Prácticas!Referencias

Referencias Relativas: se refieren a las celdas por sus posiciones en relación con la celda que contiene la fórmula A1

Referencias Absolutas: sirven para evitar que las referencias a celdas cambien cuando se copia la fórmula a una nueva posición $A$1

Referencia mixta: referencia donde tan sólo una de las dos dimensiones, fila o columna, permanece constante A$1, $A1

Las referencias relativas se convierten en absolutas introduciendo el carácter dólar ($) antes de la letra de la columna o el número de fila, que se quieren mantener invariables.

Uso de F4 para convertir

entre tipos de referencias

Page 36: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

36 36

Referencias a celdas

Las referencias múltiples consisten en referencias sencillas separadas por el carácter punto y coma (;). Ejemplo: (B2:D3;C5:D6)

Se pueden introducir también referencias a celdas o rangos que sean la intersección de dos o más rangos Especificar cada rango separándolos por un espacio

Ejemplo: (B5:D8 B4:C8).

Page 37: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

37 37

Referencias Tridimensionales

Hacen referencia a una celda o un rango de celdas de otra hoja dentro del mismo libro. La sintaxis es: NOMBRE_HOJA!CELDA (una celda)

NOMBRE_HOJA!CELDA1:CELDA2 (un rango) Ejemplo: (Hoja1!B5:C6).

Si referencia una celda o un rango de otro libro. La sintaxis es:

‘RUTA[LIBRO.xls]HOJA’!RANGO RUTA: acceso al fichero en la estructura de directorios

del sistema.

LIBRO: nombre del fichero.

HOJA: nombre de la hoja.

Page 38: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

38

Operadores para Referencias (Resumen)

38

OPERADOR FUNCIONALIDAD

: (dos puntos) Especificar un rango

; (coma) Construye un rango uniendo otros dos

(espacio) Intersección entre dos rangos de celdas: devuelve el rango de

celdas comunes a los dos rangos proporcionados

! (exclamación) Especificar una hoja distinta de la actual

[] (corchetes) Especificar un libro distinto del actual

Page 39: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

39 39

Referencias a Celdas

Ejercicio: En el libro Prácticas, insertar una nueva hoja Trabajar

con Referencias En esta hoja, generar una tabla de multiplicar con una

sola fórmula que se copiará a toda la tabla (10 x 10).

Darle el siguiente formato a las celdas a las filas y columnas que contengan los números multiplicados: Tipo de letra: Arial 12 Negrita

Formato de texto: Centrar

Page 40: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

40 40

Referencias a celdas: nombrar celdas

Se pueden dar nombres a celdas y de conjuntos de celdas.

Podemos utilizar nombres de celdas para evitar tener que trabajar con referencias absolutas

Para crear nombres, conviene tener en cuenta ciertas reglas: Deben empezar por una letra o por el carácter

subrayado (_) Tras este primer carácter, se puede usar cualquier

conjunto de letras, números y caracteres especiales. No se pueden utilizar espacios en blanco. Como

alternativa, se puede emplear un carácter de subrayado o un punto.

Page 41: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

41 41

Nombrar Celdas

Para definir nombres se usa el comando Insertar Nombre Definir Seleccionar la celda, rango o rango múltiple al que desee asignar el

nombre.

Elegir el comando Insertar Nombre Definir, con lo cual se abre un cuadro de diálogo.

Hacer clic en Agregar o en Aceptar.

Este mismo menú permite modificar o eliminar nombres ya definidos

Otra opción es seleccionar la celda o rangos de celdas a las que se desea dar un nombre, y luego hacer clic sobre el cuadro de nombres de la Barra de Referencias. La referencia a la celda activa se sustituye por el nombre tecleado. CTRL + F3

cuadro Definir Nombre

Page 42: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

42 42

Nombrar Celdas

Todos los nombres de celdas son accesibles desde la Barra de Referencias Por defecto todos los nombres definidos en una hoja

están disponibles para todas las demás (son nombres a nivel de libro)

Si queremos nombres a nivel de hoja, anteponer el nombre de la hoja

Hoja1!PruebaNombre Si disminuimos el zoom (CTRL + Rueda Ratón) y tenemos

nombres definidos para rangos de varias celdas, EXCEL mostrará el nombre de los rangos para proporcionar una visión global de la hoja

Otra forma de ver los nombres definidos es usar la opción Insertar Nombres Pegar Lista

EJEMPLO

Prácticas!Listado_de_Libros

Page 43: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

43 43

Nombrar Celdas

Podemos dar nombre a las celdas de forma automática, usando rótulos de fila, de columna o ambos Seleccionar el rango de celdas, incluyendo los encabezados de fila

y/o columna

Seleccionamos Insertar Nombre Crear Seleccionar si queremos utilizar el encabezado de la fila o la

columna para dar nombre al resto de celdas

Se pueden definir nombres para constantes y/o fórmulas No aparecerán en el cuadro de nombres porque no tienen una

ubicación “tangible”

Ejemplo: Diametro = 2 * Pi * radio

Podemos utilizar los nombres definidos a la hora de crear nuevas fórmulas

Ejemplos – Referencias

Page 44: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

44

Usar nombres en fórmulas

Si definimos nombres sobre celdas utilizadas en fórmulas, podemos actualizar las fórmulas para sustituir las referencias por los nuevos nombres Seleccionar el rango sobre el que se desea

aplicar los nombres Insertar Nombre Aplicar

44

Page 45: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

45 45

Nombrar Celdas

Ejercicio : A partir del ejercicio de la tabla de multiplicar,

asignar los siguientes nombres a los rangos correspondientes: “Tabla_del _7” en flias

“Tabla_del_9” en columnas

“Tabla_multiplicar”, todos los elementos de la tabla

Con base a estos nombres, realizar las siguientes operaciones: Suma de toda la tabla del 9

Promedio de los elementos de la tabla del 7 y los elementos de la tabla del 9

Contar los elementos que hay en toda la tabla de multiplicar

Page 46: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

46 46

Creación semiautomática de nombres

Ejercicio :

A partir del libro Prácticas, ir a la hoja Ejercicio Formato

Seleccione los datos de la tabla y cree nombres para las celdas usando la fila superior y la columna izquierda de la tabla

Usando dichos nombres, obtenga la suma total por meses en la columna G Puede usar Insertar Nombre Pegar mientras edita la

fórmula para acceder a todos los nombres de rangos de celdas definidos

Defina una constante Impuesto con valor 0,16 (recuerde aplicar formato porcentual)

Utilice la fórmula anterior para hacer una copia de la tabla, pero ésta incluyendo precios con IVA

Page 47: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

47 47

Agenda

Empezar a trabajar con EXCEL Personalización del Entorno de Trabajo

Edición de Celdas

Fórmulas y Funciones (introducción)

Selección de Celdas Trabajar en Modo Grupo

Referencias a Celdas

Nombrar Celdas

Opciones Avanzadas de Formato y Presentación Alineación de Texto

Formatos Condicionales

Precisión de Pantalla

Uso de Estilos

Paneles

Definición de Esquemas

Impresión y Presentación

Vistas Personalizadas

Protección de Celdas y Hojas

Funciones Funciones de Texto

Funciones Matemáticas

Funciones Lógicas

Funciones (…)

Funciones de Información

Funciones de Búsqueda y Referencia

Funciones de Fecha y Hora

Funciones Estadísticas

Funciones Matriciales

Funciones Financieras

Herramientas para Análisis

Trabajar con Datos Datos Consolidados

Filtros

Filtros Avanzados

Macros

Validación de Datos

Subtotales

Función Buscar Objetivo

Escenarios

Tablas Dinámicas

Trabajar con Gráficos

Extracción de Datos y Libros Compartidos

Page 48: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

48 48

Alineación de Texto

A esta opción se accede a través del cuadro de diálogo Formato de Celdas

Esta ficha incluye varias opciones Horizontal Vertical

Orientación Control de Texto

Dirección del Texto

CTRL + F1

cuadro Formato de Celdas

Page 49: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

49 49

Alineación de Texto

TEXTO INCLINADO (Encabezamiento de Tablas)

1. Seleccione las celdas que desee formatear y elija el comando Celdas del menú Formato

2. En la ficha Bordes, aplique bordes verticales a la izquierda, derecha y centro del rango de celdas

3. En la ficha Alineación, use los controles Orientación para seleccionar el ángulo deseado (60º)

4. En la lista desplegable Horizontal de la sección alineación de texto, seleccione la opción centrar. Excel gira los bordes izquierdo y derecho con el texto

5. Arrastre el borde inferior de la cabecera de la fila 1 hacia abajo para dejar espacio suficiente para encajar los rótulos sin recurrir al ajuste

6. Seleccione todas las columnas. Seleccione Formato, Columna, Autoajustar a la selección para reducir todas las columnas al menor ancho posible

Aunque los rótulos inclinados parezcan

borrosos, este efecto desaparece cuando

imprimimos la hoja de cálculo

Page 50: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

50 50

Alineación de Texto

BORDES INCLINADOS (Encabezamiento de Tablas)

1. Seleccione la celda que desea formatear e introduzca unos diez espacios en blanco. Podrá corregirlo posteriormente.

2. Introduzca el rotulo que desea y que corresponderá a la columna

3. Mantenga pulsada la tecla ALT mientras pulsa ENTER dos veces para crear dos saltos de línea en la celda

4. Introduzca el segundo rótulo, que corresponderá a las filas y pulse INTRO

5. Seleccione la celda, elija Formato, Celdas y haga clic en la ficha Bordes

6. Seleccione un estilo de línea y haga clic en el botón de borde inclinado de arriba izquierda abajo derecha.

7. Haga clic en la fecha Alineación, active la opción Ajustar Texto y haga clic en Aceptar

Page 51: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

51 51

Alineación de Texto

Ejercicio : A partir del libro Prácticas ir a la hoja Ejercicio

Formato Dar formato de Texto Inclinado a la fila de

Títulos (Ahorros, Corriente …)

Escribir en la celda A5, los títulos Mes y Productos

Usar la opción de bordes inclinados para separar los dos títulos de la celda A5

Page 52: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

52 52

Formatos Condicionales

Son formatos “dormidos” que aparecen cuando una celda o un grupo de celdas alcanzan un estado determinado.

Podemos tener dos tipos de formato Por valor de celda

Por fórmula: podemos utilizar cualquier fórmula cuyo resultado sea verdadero o falso Usamos referencias relativas que apuntan a la primera celda del

rango (celda superior-izquierda)

Ejemplo: =A2>$C$1

Seleccionar el conjunto de celdas Menú Formato Formato Condicional

Si varias condiciones son ciertas para una celda, sólo se aplica el formato asociado con la primera condición

Page 53: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

53

Formatos Condicionales

Para eliminar TODAS las opciones de formato de una celda Menú Edición Borrar Todo

Page 54: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

54 54

Formatos Condicionales

Ejercicio:

Insertar una copia de la hoja Ejercicio Formato en el libro Prácticas

Definir los siguientes formatos condicionales: En fondo rojo las ventas inferiores a 1000€. En fondo rosa todas las celdas que caen por debajo

de la media de todos los valores de la tabla.

Finalmente, aplicar fondo gris a las celdas alternas con los nombres de meses.

Es decir, la primera gris, la segunda blanca, y así sucesivamente (usar las funciones RESIDUO y FILA) Utilice al ayuda de estas funciones si necesita saber cómo

funcionan …

Page 55: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

Precisión de Pantalla

Opción para decirle a Excel que queremos que nos trate los datos tal y como los vemos en pantalla (asi como el redondeo)

Herramientas / Opciones: Calcular

Dos detalles muy importantes: El cambio es

irreversible

Es aplicado a todo el libro

55

Page 56: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

56 56

Precisión de Pantalla

Ejercicio:

Crear un libro nuevo.

Insertar los números: 81, 27, 9, 3 en diferentes celdas En una celda distinta realizar la división de 27 entre 81

En otra celda la división de 9 entre 27

En otra celda la división de 3 entre 9

Realizar la suma de estas divisiones

Posteriormente formatear las celdas de la división para que solo se vea 1 cifra decimal

Activar la opción de Precisión de Pantalla

Volver a realizar la suma

Comparar los resultados

Page 57: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

57 57

Uso de Estilos

Permiten asignar nombres a combinaciones de atributos de formato.

Cada libro posee seis estilos predefinidos

NOMBRE DESCRIPCIÓN EJEMPLO

NORMAL valores predeterminados de todos los atributos de formato 1234

PORCENTUAL formato numérico de porcentaje, sin cifras decimales 12%

MILLARES formato numérico con separadores de millares y 2 cifras decimales 5.121,234

MILLARES [0] ídem redondeado al entero más próximo 5.121

MONEDA formato numérico Contabilidad, con símbolos monetarios y separadores de millares

1215,56 €

MONEDA[0] ídem redondeado al entero más próximo 1216 €

EJEMPLO

Estilos

Page 58: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

58 58

Uso de Estilos

Se pueden crear nuevos estilos o modificar los existentes

Opciones de estilos: Formato Estilo

Podemos Definir un nuevo estilo utilizando celdas formateadas

previamente Formato Estilo Especificar nuevo nombre Agregar

Definir un nuevo estilo desde cero Formato Estilo Especificar nuevo nombre Modificar

Aplicar un estilo

Modificar un estilo los cambios se reflejan en todas las celdas sobre las que se aplicó dicho estilo !!!

Eliminar un estilo

Una buena recomendación es agregar la lista de estilos a la barra de herramientas

Page 59: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

59 59

Uso de Estilos

Podemos reutilizar los estilos que definimos en otro libro Abrir los dos libros: el actual y aquel en que definimos

el conjunto de estilos

Menú Formato Estilo Combinar …

Una buena práctica es manejar un libro donde se van definiendo todos los estilos, de manera que siempre sabemos cual debemos utilizar para combinar

Page 60: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

60 60

Uso de Estilos

Ejercicio: Abrir el libro Prácticas e ir a la hoja Precio Petróleo

Diario

Crear un nuevo estilo con el nombre “Mi Estilo”, con las siguientes opciones: Tipo de fuente: Comic Sans

Trama: Azul

Tamaño: 12

Aplicar este estilo al grupo de títulos de la hoja

Page 61: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

61

Paneles

Los paneles permiten ver de manera simultanea varias partes de la hoja de cálculo Panel horizontal o vertical: mover los rectángulos que

están junto a las barras de scroll (Dividir Ventana)

Ambos paneles: situarse en la fila o columna posterior a las que se desean inmovilizar y seleccionar el menú Ventana Inmovilizar paneles

61

Page 62: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

62 62

Paneles

Panel horizontal

Panel vertical

Page 63: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

63 63

Paneles

Ejercicio: En el libro Prácticas ir a la hoja Precio Petroleo

Diario Crear un panel que permita visualizar siempre los

meses, de forma horizontal, y los días en forma vertical

Page 64: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

64

Definición de Esquemas

El uso de esquemas permite añadir o contraer la apariencia de una hoja de cálculo, de forma que la información se pueda ver con más o menos detalle.

EXCEL puede crear un esquema de modo automático. Para ello busca celdas con fórmulas que sean un resumen de las filas (por encima de ellas) o bien de las columnas (a su izquierda).

Para construir esquema/s utilizar el menú Datos / Agrupar y esquema / Autoesquema.

64

Page 65: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

65 65

Definición de Esquemas

Ejercicio: A partir del libro Prácticas ir a la hoja de

cálculo Esquema Crear subtotales por Trimestre y también un

total Semestral, para cada vendedor. Crear un esquema para esta hoja

Hallar el promedio de grupo para cada mes Hallar el promedio total (ambos grupos) de

forma mensual Volver a crear

un esquema con estos datos

Page 66: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

66 66

Configuración de Hoja

Permite personalizar la forma en la que se va a visualizar una hoja de cálculo cuando se imprime.

Estas opciones se manejan a través de la pestaña Página del cuadro Configurar Página

Page 67: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

67 67

Configuración de Márgenes

De esta forma podemos ajustarnos a requisitos de impresión ó introducir el máximo de información posible en una página.

Page 68: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

68 68

Encabezados y Pies de Página

En estas secciones se puede incluir información esencial del documento.

Page 69: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

69 69

Encabezados y Pies de Página

Excel cuenta con varios códigos para representar información valiosa sobre el archivo tratado que pueden ser incluidos tanto en el pie como en el encabezado

Page 70: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

70 70

Opciones de Hoja

La ficha Hoja del cuadro Configurar página, controla parámetros específicos de la hoja activa.

Es posible especificar opciones de hoja distintas para cada hoja de un libro de cálculo

Algunas de estas opciones son: Área de

impresión Impresión de

títulos

Orden de las páginas

Page 71: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

71 71

Opciones de Hoja

Algunas utilidades que pueden ayudar a comprobar la configuración de página son: la Vista Preliminar y la Vista de Salto de Página

Page 72: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

72 72

Impresión

La ventana de impresión maneja la impresora que ejecutará la opción de impresión de acuerdo a los parámetros de Configuración de Página del libro

Page 73: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

73 73

Vistas Personalizadas

Podemos almacenar las opciones de presentación de la hoja para recuperarlas en cualquier momento Menú Ver Vistas Personalizadas

Antes de comenzar, defina la vista por defecto con un nombre (p.e: Vista Normal)

Realice las modificaciones de presentación necesarias y asócielas con una nueva vista y un nuevo nombre

Una buena recomendación es agregar la lista de vistas personalizadas a la barra de herramientas

Page 74: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

74 74

Opciones de Impresión

Ejercicio:

En el libro de trabajo de Prácticas, configurar la hoja denominada Matriculación Turismos para que ofrezca el siguiente aspecto (en la vista preliminar)

En cada página se repite como encabezado de fila la lista de

fabricantes y los nombres de provincia.

Deberá configurar también los saltos de página

· Página 1: Álava – LLeida (AUDI – HONDA)

· Página 2: Álava – Lleida (HYUNDAI – OPEL)

· Página 3: Álava – Lleida (PEUGEOT – OTROS)

· Página 4: La Rioja – Final (AUDI – HONDA)

· Página 5: …

Page 75: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

75

Protección de celdas y hojas de cálculo

Utilidad para restringir la visibilidad de ciertas partes de una página u hoja de cálculo

Una primera forma de proteger un documento es declararlo como de Solo lectura, para que pueda leerse pero no modificarse.

Si se ejecuta el comando Guardar, el ordenador pedirá un nombre diferente para almacenar el archivo, pues el original no podrá ser modificado.

75

Page 76: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

76

Protección de celdas y hojas de cálculo

EXCEL dispone de dos niveles de protección La hoja de cálculo y el libro de trabajo La celda

Para que la protección surta efecto, ambos niveles de protección tienen que estar activados.

Por defecto, EXCEL bloquea (protege) todas las celdas y gráficos

Por defecto, todas las celdas están protegidas, pero no las hojas de cálculo o el libro de trabajo.

La protección está desactivada hasta que seleccione HerramientasProtegerProteger Hoja

En este cuadro de diálogo, es posible proteger el documento con una palabra clave, definida por el usuario

76

Page 77: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

77

Todas las celdas son bloqueadas de forma predeterminada

Desactivar la protección de las celdas en el cuadro de diálogo Protección, que se abre con el menú Formato / Celdas

Las celdas de una hoja de cálculo pueden tener dos tipos de protección: La que no permite modificar el

contenido de la celda pero muestra la fórmula que contiene

La sólo muestra el valor final de la fórmula

Protección de celdas y hojas de cálculo

Page 78: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

78

Protección de celdas y hojas de cálculo

Otra forma es a través de las Opciones de Seguridad. Menú Herramientas / Opciones/ Seguridad

Page 79: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

79

Protección de Datos

Ejercicio: Cambiar las propiedades del Archivo Prácticas, a Solo

lectura Abrir el libro en Excel e intentar guardar el archivo.

Si no es posible, guardar el archivo como Práctica No protegida

Abrir éste último y comprobar que permite modificar el valor de las celdas (porque NO es el archivo de sólo lectura, al crear la copia estamos creando un nuevo libro desprotegido)

Activar la protección usando el menú Herramientas Proteger Proteger Hoja

Cerrar y abrir de nuevo el libro y comprobar si se permite modificar las celdas

Page 80: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

80 80

Agenda

Empezar a trabajar con EXCEL Personalización del Entorno de Trabajo

Edición de Celdas

Fórmulas y Funciones (introducción)

Selección de Celdas Trabajar en Modo Grupo

Referencias a Celdas

Nombrar Celdas

Opciones Avanzadas de Formato y Presentación Alineación de Texto

Formatos Condicionales

Precisión de Pantalla

Uso de Estilos

Paneles

Definición de Esquemas

Impresión y Presentación

Vistas Personalizadas

Protección de Celdas y Hojas

Funciones Funciones de Texto

Funciones Matemáticas

Funciones Lógicas

Funciones (…)

Funciones de Información

Funciones de Búsqueda y Referencia

Funciones de Fecha y Hora

Funciones Estadísticas

Funciones Matriciales

Funciones Financieras

Herramientas para Análisis

Trabajar con Datos Datos Consolidados

Filtros

Filtros Avanzados

Macros

Validación de Datos

Subtotales

Función Buscar Objetivo

Escenarios

Tablas Dinámicas

Trabajar con Gráficos

Extracción de Datos y Libros Compartidos

Page 81: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

81 81

Funciones Comunes

Funciones de Texto

Funciones Matemáticas

Funciones Lógicas

Funciones de Información

Funciones de Búsqueda y Referencia

Funciones de Fecha y Hora

Funciones Estadísticas

Funciones Matriciales

Page 82: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

82

Categorías de funciones

Funciones de manipulación de texto:

Opciones para modificar el texto contenido en las celdas.

Page 83: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

83 83

Funciones de Texto

CODIGO(texto) Devuelve el código ASCII del primer carácter del texto

MONEDA(num; num_decimales) cadena de texto

MONEDA(45,899;2) 45,90 €

LARGO(valor) número de caracteres del valor resultante

ESPACIOS elimina espacios al principio, al final de la celda y entre medias, dejando sólo un espacio entre palabras

LIMPIAR elimina caracteres de control

IGUAL(cadena1;cadena2) compara las dos cadenas y devuelve verdadero o falso. Reconoce MAY/MIN pero ignora formatos

MAYUSC, MINUSC, NOMPROPIO

NOMPROPIO(“juan josé”) Juan José

T(valor) Devuelve el texto al que se refiere el argumento valor

VALOR(texto) Convierte una cadena de texto que representa un número en un número

Page 84: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

84 84

Funciones de Texto

HALLAR(texto_buscado;dentro_del_texto;núm_inicial)

No distingue entre MAY y MIN

Admite caracteres comodín (*, ?, etc)

ENCONTRAR(texto_buscado;dentro_del_texto;núm_inicial)

Distingue MAY/MIN y no admite comodines

DERECHA(texto;núm_de_caracteres) los num caracteres empezando por la derecha

IZQUIERDA(texto;núm_de_caracteres) los num caracteres empezando por la izquierda

EXTRAE(texto;posición_inicial;núm_de_caracteres)

SUSTITUIR(texto;texto_original;texto_nuevo; núm_de_ocurrencia)

REEMPLAZAR(texto_original;núm_inicial;núm_caracteres;texto_nuevo)

CONCATENAR (texto1;texto2; ...) ⇆ Uso de ‘&’

Si se omite, sustituye todas las ocurrencias

Page 85: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

85

Funciones de Texto

Ejercicio A partir de los datos de la hoja Funciones de Texto,

del libro Prácticas Extraer la primera palabra del texto de las celdas de

A2-A5 con una única fórmula A modo de ejemplo más elaborado, puedes ver la

fórmula para extraer la última palabra en la columna C Reemplazar y Sustituir:

Utilizar la función Reemplazar para cambiar “Francisco” por “José Manuel”

Utilizar la función Sustituir para cambiar “Francisco” por “Ana”

Page 86: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

86 86

Funciones Comunes

Funciones de Texto

Funciones Matemáticas

Funciones Lógicas

Funciones de Información

Funciones de Búsqueda y Referencia

Funciones de Fecha y Hora

Funciones Estadísticas

Funciones Matriciales

Page 87: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

87

Categorías de funciones

Funciones matemáticas y trigonométricas:

Se emplean para ejecutar todo tipo de cálculos, tanto simples como complejos. Para ello, Excel implementa una gran cantidad de funciones: SUMA, PRODUCTO, ALEATORIO, REDONDEAR, TRUNCAR, FACT (factorial), LOG (logaritmo) y RESIDUO.

Page 88: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

88

Funciones Matemáticas

SUMA

PRODUCTO y SUMAPRODUCTO

= SUMAPRODUCTO (A1:A4;B1:B4) ≡ = SUMA (A1:A4*B1:B4)

RESIDUO(dividendo; divisor) resto

COMBINAT (numElemtos; tamGrupos)

ALEATORIO y ALEATORIO.ENTRE(min;max)

MULTIPLO.[SUPERIOR|INFERIOR] (num; objetivo)

=MULTIPLO.SUPERIOR(4,42;0,05) redondea a .5 superior

POTENCIA(número;potencia)

RAIZ(número)

FACT(número)

ABS(número)

NUMERO.ROMANO(numero) devuelve el numero en formato romano

Page 89: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

89

Funciones Matemáticas

REDONDEO: Excel ofrece varias funciones para esta tarea

REDONDEAR(num;num_decimales) Redondea un numero a un número específico de decimales (el más cercano).

num_decimales < 0 Redondear a la izda. de la coma

REDONDEAR.MAS: Siempre redondea hacia arriba

REDONDEAR.MENOS: Siempre redondea hacia abajo

REDONDEA.PAR: Redondea al par mas próximo por arriba

REDONDEA.IMPAR: Redondea al impar mas próximo por arriba

REDOND.MULT(num, mult): Redondea (hacia abajo) un numero hasta el múltiplo del número especificado

ENTERO(num) entero inferior más próximo

TRUNCAR (num; num_decimales) elimina num_decimales decimales

Page 90: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

90

Funciones Matemáticas

Ejercicio A partir del libro Prácticas, ir a la hoja Ejercicio de Formato En la columna H, haremos un redondeo par de las cifras de

Ahorro

En la columna I, haremos un redondeo siempre hacia arriba de los valores de corriente

En la columna J haremos un redondeo a -2 cifras decimales de los valores de Tarjeta de Crédito

En la columna K, haremos un redondeo a múltiplos de 100 de las cifras de Hipoteca

Page 91: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

91

Funciones Matemáticas y Trigonométricas

CONTAR.SI: Esta función permite contar cuantas celdas cumplen con un criterio determinado, dentro de un rango específico. Únicamente tiene en cuenta las celdas que no están en blanco

CONTAR.SI(RANGO; CRITERIO) CRITERIO 32, "32", ">32", "manzanas“, B4, etc.

SUMAR.SI: Es similar a la función CONTAR.SI pero primero evalúa las celdas del rango que cumplen con el criterio dado y luego suma los valores del parámetro rango_suma

SUMAR.SI(RANGO; CRITERIO;rango_suma)

Page 92: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

92

CONTAR.SI y SUMAR.SI

Ejercicio A partir del libro Prácticas, ir a la hoja Base de Datos. Hallar el número de clientes que viven en Córdoba,

Santander, Granada, Mérida Use las funciones de texto MAYUCS, MINUC, NOMPROPIO

para evitar problemas con la distinción entre minúsculas y mayúsculas

En el mismo libro, hallar el número de Unidades vendidas por localidad

Page 93: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

93

Funciones Matemáticas y Trigonométricas

COMBINAT: Determina el número de posibles combinaciones o grupos que puedan realizarse a partir de un conjunto de elementos.

ALEATORIO.ENTRE: devuelve un numero aleatorio escogido en el rango especificado

Page 94: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

94

Funciones Matemáticas

Ejercicio En una nueva hoja en el libro Prácticas: Determinar cuantos equipos de 12 jugadores de fútbol

se pueden crear con 17 jugadores.

Determinar la probabilidad de ganar la lotería teniendo en cuenta que cada opción tiene 6 números entre un total de 49

Page 95: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

95 95

Funciones Comunes

Funciones de Texto

Funciones Matemáticas

Funciones Lógicas

Funciones de Información

Funciones de Búsqueda y Referencia

Funciones de Fecha y Hora

Funciones Estadísticas

Funciones Matriciales

Page 96: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

96

Categorías de funciones

Funciones lógicas:

Se emplean para verificar una o varias condiciones. Una vez evaluadas, se devuelve un valor si el resultado es verdadero u otro en el caso de que sea falso. Las más empleadas son SI, Y y O. Pueden usarse combinadas entre sí y anidadas tantas veces como sea necesario.

Page 97: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

97

Funciones Lógicas

FUNCIÓN DESCRIPCIÓN

SI(prueba_logica; valor_si_verdadero; valor_si_falso)

Devuelve un valor u otro, según se cumpla o no la condición o prueba lógica

FALSO() Devuelve el valor lógico Falso

VERDADERO() Devuelve el valor lógico Verdadero

NO(valor_lógico) Invierte el valor lógico proporcionado

Y(valor_logico1;valor_logico2;...) Devuelve VERDADERO si todos los valores son verdaderos

O(valor_logico1;valor_logico2;...) Devuelve VERDADERO si alguno de los valores es verdadero

Page 98: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

98

Funciones Lógicas

Ejercicio A partir de la hoja de cálculo Funciones Lógicas del libro

Practicas, se pide calcular automáticamente el precio total que debe pagar el cliente, teniendo en cuenta los siguientes condicionantes: Hay tres clases de habitaciones: A, B y C, con sus

correspondientes precios y suplementos extras. Si el cliente que abandona la habitación es menor de 10 años y

ha estado alojado más de 5 días se le aplica un descuento de 15 € por día y si es mayor de 65 años y ha estado alojado más de 3 días le corresponde un descuento de 25 € diarios.

En cualquier otro caso, no se aplica ningún descuento. La fórmula para calcular el precio total es: PT = (Precio de la habitación * número de días)

+ suplemento – descuento

Page 99: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

99

Funciones Lógicas

Ejercicio:

Continuando con la Hoja del ejercicio anterior realice el siguiente ejercicio:

Hay tres posibles tipos de enfermedades: ciática, lumbalgia y dorsalgia. Si el paciente padece lumbalgia o dorsalgia, el plazo de revisión

será de tres meses y la duración del tratamiento de seis meses, independientemente de su edad.

Si por el contrario, el paciente padece ciática … y es mayor de 55 años, tendrá que volver a revisión en un mes y la

duración del tratamiento será de un año.

en otro caso la revisión será a los dos meses, y la duración del tratamiento será de un año.

Page 100: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

100

Funciones Comunes

Funciones Matemáticas

Funciones de Texto

Funciones Lógicas

Funciones de Información

Funciones de Búsqueda y Referencia

Funciones de Fecha y Hora

Funciones Estadísticas

Funciones Matriciales

Page 101: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

101

Categorías de funciones

Funciones para obtener información:

Proporcionan información sobre el tipo de dato almacenado en la celda. La respuesta de Excel puede hacer referencia tanto a los formatos como a la ubicación y/o el contenido de la celda en cuestión. Es recomendable consultar la ayuda sobre cada una de las funciones para interpretar correctamente la información que devuelve el programa.

Page 102: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

102

Funciones Comunes

ESBLANCO(celda) devuelve VERDADERO si la celda referenciada está en blanco

ESERR(celda) devuelve VERDADERO si el valor es cualquier valor de error excepto #N/A

ESERROR(celda) devolverá VERDADERO si la celda a la que estamos haciendo referencia es un error y FALSO en caso que no lo sea

ESLOGICO(celda) Devuelve VERDADERO si el valor es un valor lógico

ESNOD(celda) Devuelve VERDADERO si el valor es el valor de error #N/A (valor no disponible)

ESNOTEXTO Devuelve el valor VERDADERO si el valor no es de tipo texto

ES.PAR | ES.IMPAR (celda) Devuelve el valor VERDADERO si el número es par|impar

Page 103: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

103

Funciones Comunes

TIPO(valor) Devuelve un número que indica el tipo de datos de un valor

CELDA(Tipo de información; Celda) Devuelve información acerca del formato, la ubicación o el contenido de una celda "Direccion”

referencia de la celda en formato texto, $A$1.

"Columna“ columna en la que se encuentra la celda. Valor numérico, no el nombre de la columna.

"Color” 1 cuando la celda tiene valor para los valores negativos 0 en caso contrario.

"Contenido” devuelve el valor de la celda a la que hacemos referencia.

"Prefijo” ' si la celda contiene texto alineado a la izquierda " si la celda está alineada a la derecha ^ si el contenido de la celda está centrada \ si la celda tiene texto con alineación de relleno por último nos devolverá texto vacío si tiene otro valor.

Número 1

Texto 2

Valor Lógico 4

Valor de Error 16

Matriz 64

Page 104: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

104

Funciones Comunes

CELDA(Tipo de información; Celda) Devuelve información acerca del formato, la ubicación o el contenido de una celda

"Proteger” si la celda está protegida devolverá un 1 si no lo está un 0.

"Fila” indica la fila en la que se encuentra la celda.

"Tipo” b si la celda está en blanco r si contiene texto v si contiene otro valor como por ejemplo un valor numérico.

"Ancho” ancho de la columna en la que se encuentra la celda. El valor se redondeará al entero más próximo.

"Formato” formato de la celda representado por unos valores de tipo texto.

Page 105: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

105

Funciones Comunes

Funciones Matemáticas

Funciones de Texto

Funciones Lógicas

Funciones de Información

Funciones de Búsqueda y Referencia

Funciones de Fecha y Hora

Funciones Estadísticas

Funciones Matriciales

Page 106: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

106

Categorías de funciones

Funciones de búsqueda y referencia:

Se emplean para localizar valores en la hoja de cálculo. Son especialmente útiles las de BUSCAR, BUSCARH y BUSCARV.

Mención especial merece la función HIPERVINCULO, que permite acceder a cualquier documento, ya sea almacenado en la propia máquina o en la red

Page 107: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

107

Funciones de Búsqueda y Referencia

BUSCARV y BUSCARH: Buscan información almacenada en tablas, dando la posibilidad de referenciar otra fila o columna

BUSCARV: Opera sobre columnas

BUSCARH: Opera sobre filas

Page 108: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

108

Funciones de Búsqueda y Referencia

BUSCARH(valor_buscado;matriz_buscar_en; indicador_filas; ordenado) Busca el valor en la primera fila del rango especificado

y devuelve el valor que hay en esa misma columna en la fila especificada (relativa)

ORDENADO

VERDADERO u omitido se realiza una búsqueda aproximada: si no se encuentra el valor, se devuelve el máximo valor menor que el buscado (la primera fila debe estar ordenada)

FALSO coincidencia exacta: si no se encuentra, devuelve #N/A

=BUSCARH("Ejes";A1:C4;2;VERDADERO) Busca “Ejes” en la fila 1 y devuelve el valor de la fila 2 que está en la misma columna (4)

EJEMPLOS

Buscar

Page 109: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

109

Funciones de Búsqueda y Referencia

BUSCARV(valor_buscado;matriz_buscar_en; indicador_columnas; ordenado) Busca el valor en la primera columna del rango

especificado y devuelve el valor que hay esa misma fila en la columna especificada

ORDENADO

VERDADERO u omitido se realiza una búsqueda aproximada: si no se encuentra el valor, se devuelve el máximo valor menor que el buscado

FALSO coincidencia exacta: si no se encuentra, devuelve #N/A

=BUSCARV(“Romero";A1:C2456;2;VERDADERO) Busca “Romero” en la columna 1 y devuelve el valor de la columna 2 que está en la misma fila

Page 110: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

110

Funciones de Búsqueda y Referencia

Ejercicio Ir al libro Prácticas y, a partir de los datos de la hoja

NotaAlumnos, escribir la nota final de los alumnos de acuerdo a: La tabla de equivalencias para el Sistema Educativo Español

La tabla de equivalencias para el Sistema Educativo Colombiano

Page 111: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

111

Funciones de Búsqueda y Referencia

Ejercicio

Realice una copia de la hoja Funciones Lógicas Modifique la solución al ejercicio que resolvió sobre

dicha hoja para simplificar la fórmula que calcula los descuentos Utilice para ello la función BUSCARH()

Page 112: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

112

Funciones de Búsqueda y Referencia

BUSCAR(valor_buscado;matriz_buscar_en; matriz_resultado) Busca el valor especificado en el primer rango y

devuelve el valor que está en la misma posición en el segundo rango matriz_buscar_en: rango donde se debe buscar el

valor especificado. Debe ser sólo una columna o fila y debe estar ordenado

matriz_resultado: rango de donde recuperar el resultado a devolver, debe ser igual en tamaño al anterior

Page 113: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

113

Funciones de Búsqueda y Referencia

Ejercicio

En la hoja Base de Datos del libro Practicas defina una nueva fórmula que permita saber cuantas unidades se han vendido a un cliente concreto Utilice la función BUSCAR

Page 114: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

114

Funciones de Búsqueda y Referencia

COINCIDIR(valor_buscado;matriz_buscar_en; tipo_coincidencia) Devuelve la posición relativa de la celda que contiene el

valor buscado en el rango especificado

Tipo_coincidencia: [-1, 0, 1] 1 (valor por defecto) busca el máximo de entre los valores

menores o iguales que el que se busca

0 coincidencia exacta

-1 el mínimo de entre los mayores o iguales al buscado

Page 115: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

115

Funciones de Búsqueda y Referencia

INDICE(rango; fila, col) Devuelve el valor de la celda que se encuentre en la

intersección de “fila” y “col” en el rango especificado

Si el rango contiene sólo una fila o una columna, el argumento fila o col puede ser omitido

Ejemplo =INDICE(A3:B7;2;1) devuelve el valor de la celda

que se encuentra en la segunda fila de la matriz y en la primera columna, es decir A4.

Page 116: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

116

Funciones de Búsqueda y Referencia

Ejercicio

En la hoja Base de Datos del libro Practicas defina una nueva fórmula que permita saber cuantas unidades se han vendido a un cliente concreto En esta ocasión utilice las funciones COINCIDIR e

INDICE

Page 117: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

117

Funciones de Búsqueda y Referencia

FILA | COLUMNA(ref) nº de fila/col de la celda Si se omite la referencia, fila/col de la celda que contiene la fórmula

FILAS | COLUMNAS(rango) nº de filas/cols del rango seleccionado FILAS(100; 200;300\1000;2000;3000) 2

AREAS(ref) nº de áreas en el rango AREAS((B2:D4;E5;F6:I9)) 3

TRANSPONER(matriz) Debe introducirse como fórmula matricial. Seleccionar rango

comenzando por la celda de la fórmula. Presione F2 y, a continuación, CTRL+MAYÚS+ENTRAR.

Si la fórmula no se introduce como fórmula matricial, el resultado único es 1.

INDIRECTO(ref; a1) descubrir el contenido de una celda a partir de su referencia a1 = VERDADERO Referencia tipo A1 a1 = FALSO Referencia tipo L1C1

Page 118: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

118

Funciones de Búsqueda y Referencia

DESREF(ref;nfilas;ncolumnas;alto;ancho) Devuelve una celda situada a partir de una referencia

(ref) unas filas más abajo (nfilas positivo) o más arriba

(nfilas negativo) y unas columnas más a la derecha (ncolumnas positivo) o

más a la izquierda (ncolumnas negativo). Los parámetros alto y ancho indican el número de

celdas que se tienen que recuperar a partir de ahí.

Ejemplo: =DESREF(A1;2;3) devuelve el valor situado 2 filas

más abajo y 3 columnas a la derecha de la celda A, es decir en la celda D3.

Page 119: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

119

Funciones de Búsqueda y Referencia

DIRECCION(fila;columna;abs;a1;hoja) Crea una referencia de celda en forma de texto una vez

especificada la fila y la columna abs = especifica el tipo de referencia que devuelve.

1 u omitido devuelve una referencia absoluta 2 devuelve una referencia fila absoluta, columna relativa 3 devuelve una referencia fila relativa, columna absoluta 4 devuelve una referencia relativa

a1 = valor lógico que especifica el estilo de la referencia Hoja = texto que especifica el nombre de la hoja de cálculo o que

se utilizará como referencia externa.

Ejemplos =DIRECCION(1;2) devuelve una referencia absoluta a ($B$1) =DIRECCION(1;2;4) devuelve una referencia absoluta a (B1) =DIRECCION(1;2;4;falso) devuelve una referencia absoluta a

(F1C2)

Page 120: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

120

Funciones de Búsqueda y Referencia

Ejercicio

Vaya a la hoja Búsqueda y Referencia del libro Practicas y observe las distintas formas de usar las diferentes funciones que acabamos de ver para obtener un mismo resultado

Page 121: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

121

Funciones Comunes

Funciones Matemáticas

Funciones de Texto

Funciones Lógicas

Funciones de Información

Funciones de Búsqueda y Referencia

Funciones de Fecha y Hora

Funciones Estadísticas

Funciones Matriciales

Page 122: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

122

Categorías de funciones

Funciones de fecha y hora:

Evalúan, si es necesario, y devuelven los valores de fecha y hora.

Las fechas son tratadas por Excel como números de serie, por lo que el programa les asigna una equivalencia que no se corresponde con el cómputo humano.

Ejemplo: AÑO(2006) da como resultado 1905.

2:09:03 pm 23/10/2002 = 37552,5896180556

• 37552 días desde 1/1/1900

• 0,5896180556 intervalo entre medianoche y las 2:09:03 pm

Page 123: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

123

HOY() fecha actual (dd/mm/aaaa)

AHORA() hora actual

FECHA(año,mes,día) construye un valor de tipo fecha

DIASEM(fecha; tipo) p.e: DIASEM(FECHA(18;06;2008))

AÑO | MES | DIA (fecha)

HORA | MINUTO (hora)

FECHA.MES(fecha; meses) fecha exacta de hoy en X meses

FIN.MES(fecha; meses) fecha exacta del final de mes en X meses

DIA.LAB(fecha_inicial; dias_lab) fecha de hoy en X días laborables

DIAS.LAB(fecha_ini;fecha_fin; festivos) días laborables entre dos fechas, sin contar los festivos especificados

SIFECHA(fecha_ini; fecha_fin; tipo) La diferencia que hay entre dos fechas. El tipo puede ser: Y, M, D, YM, MD

Funciones de Fecha y Hora EJEMPLO

Fechas

Es recomendable utilizar la función

FECHA() cuando queramos introducir

una fecha como una constante

Page 124: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

124

Funciones de Fecha

Ejercicio: Crear una nueva hoja con el nombre “Funciones de

Fecha”

Calcular los años, meses y días totales que ha vivido una persona. Además, dar la edad exacta especificando años, meses y días vividos.

Calcular el número de días laborables del año 2011, especificando un grupo de festivos del año

Calcular número de días laborables que restan hasta las vacaciones del verano

Calcular la fecha que será dentro de 100 días laborables

Page 125: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

125

Funciones Comunes

Funciones Matemáticas

Funciones de Texto

Funciones Lógicas

Funciones de Información

Funciones de Búsqueda y Referencia

Funciones de Fecha y Hora

Funciones Estadísticas

Funciones Matriciales

Page 126: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

126

Categorías de funciones

Funciones estadísticas:

Sirven para llevar a cabo análisis estadísticos sobre el rango seleccionado. Algunas de las más interesantes son: CONTAR, MAX, MIN, PROMEDIO, PERCENTIL, FRECUENCIA, JERARQUIA, TENDENCIA y VAR.

Page 127: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

127

Funciones Estadísticas

FUNCIÓN DESCRIPCIÓN

MEDIA.ARMO(número1;número2;...) Media armónica de un conjunto de números positivos

MAX(número1;número2;...) Valor máximo de la lista de valores

MEDIANA(número1;número2;...) Mediana de la lista de valores

PROMEDIO(número1;número2;...) Media aritmética de la lista de valores

VAR(número1;número2;...) Varianza de una lista de valores

MODA(rango) Valor que más se repite

K.ESIMO.MAYOR(matriz;k) Valor k-ésimo mayor de matriz

Page 128: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

128

Funciones Estadísticas

CONTAR: Determina la cantidad de celdas que contienen un número y,

opcionalmente, cuales de ellas contienen los números proporcionados en la lista de argumentos.

CONTAR.BLANCO Determina la cantidad de celdas en blanco que hay en el rango

especificado

CONTARA Obtener la cantidad de celdas que contienen información sin

importar el tipo.

Con esta función puede obtener el número de entradas en un rango o de una matriz de números que incluyen valores lógicos, texto o de error (…)

FUNCIONES “A”

NO ignoran las celdas que contengan

valores de tipo texto

Page 129: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

129

Funciones Estadísticas

Ejercicio: en el libro Prácticas, insertar al final una copia de la hoja Ejercicios de Formato

Escribir el texto “Vacío” en las celdas vacías que tenga la tabla

Calcular en la fila 22 el promedio de los productos usando la función Promedio

Calcular en la fila 22 el promedio de los productos usando la función PromedioA

Comparar los resultados

Page 130: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

130

Funciones Estadísticas

FRECUENCIA: determina la frecuencia de ocurrencia de un rango de datos en una gran selección, es decir, toma los valores de un grupo y los busca dentro de una base de información.

FRECUENCIA (rango; datos) RANGO conjunto de valores entre los que se quiere contar

ocurrencias de datos datos matriz de intervalos

Se introduce como una fórmula matricial en las celdas en las que se desea que aparezca el resultado Seleccionar rango donde se quiere introducir la fórmula matricial

Escribir la fórmula y en vez de introducirla con ENTER, utilizar la combinación CTRL + MAY + ENTER

Page 131: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

131

Funciones Estadísticas

Ejercicio Utilice la función ALEATORIO.ENTRE para introducir

valores de edades (0 a 100) en una misma columna

Introduzca unos cuantos números en la columna adyacente que harán las veces de intervalos

Utilice la función FRECUENCIA para saber cuantas edades caen en cada uno de los intervalos que acaba de definir

Page 132: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

132

Fórmulas Matriciales

Matriz (ARRAY) Colección de objetos con los que puede operarse de

forma individual o colectiva

Una o dos dimensiones (filas y columnas)

Al trabajar con matrices, lo importante es ingresar las fórmulas en formato matricial

Este formato se establece con la siguiente combinación de teclas Ctrl+May+Enter (normalmente introduciríamos la

fórmula con Enter)

EXCEL no permite modificar una celda cuyo valor viene determinado por una fórmula matricial

Page 133: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

133 133

Funciones Comunes

Funciones Matemáticas

Funciones de Texto

Funciones Lógicas

Funciones de Información

Funciones de Búsqueda y Referencia

Funciones de Fecha y Hora

Funciones Estadísticas

Funciones Matriciales

Page 134: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

134

Funciones con Matrices

Se reconocen porque van encerradas entre llaves

Para introducirlas: CTRL + MAY + ENTER

Son fórmulas que permiten trabajar a la vez sobre los valores de un rango (o matriz)

El resultado de una fórmula matricial puede ser una nueva matriz o un valor único

={A1:A6*B1:B6} matriz de 6 celdas

={SUMA (A1:A6*B1:B6)} valor único

Definición de Matrices constantes

Seleccionar rango de celdas adecuado

=SUMA({valor1, valor2, valor3, …})

=SUMA({1, 4, 2} * {5, 2, 9}) SUMA{5,12,21,32} 70 ↔ =SUMA(1*5, 4*2, 2*9)

=SUMA((A1:D1*{1,2,3,4})) ↔ =SUMA(A1*1,B1*2,C1*3,D1*4)

Page 135: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

135

Funciones con Matrices

Matrices de una dimensión (debemos seleccionar el rango adecuado de celdas antes de introducirlas)

Horizontales ={1;2;3;4;5} + (CTRL + MAY + ENTER)

Verticales ={10\20\30\40\50\60} + (CTRL + MAY + ENTER)

Matrices de dos dimensiones (cada dimensión debe tener el mismo número de elementos)

={1;2;3\1;2;3}

Page 136: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

136

Trabajar con Matrices

Constantes matriciales con nombre

Podemos usar la constante en fórmulas matriciales Seleccionar rango de celdas adecuado =DiasSemana + (CTRL + MAY + ENTER)

INDICE(DiasSemana, 4) “Jueves”

Page 137: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

137

Fórmulas Matriciales

Seleccionar una matriz Seleccionar el rango manualmente Estando sobre una de las celdas del rango Edicion Ir_a Especial Matriz Actual

Para editar fórmulas, es preciso seleccionar el rango completo y pulsar F2 No podemos modificar celdas de un rango que define

una matriz Si que podemos usar opciones de formato

individualizadas para las celdas de la matriz

Page 138: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

138

Fórmulas Matriciales

Crear fórmulas a partir de un rango =A1:A3 + (CTRL + MAY + ENTER) Las matrices quedan vinculadas, cualquier cambio en una

celda de la original se refleja en la nueva

Si queremos desvincularlas: editar la fórmula, convertir las referencias a valores (F9) y volver a forma matricial

Fórmulas que devuelven un valor =SUMA(LARGO(DiasSemana))+(CTRL + MAY + ENTER)

Las fórmulas matriciales también sirven para eliminar cálculos intermedios

F9

Convertir celdas a valores

Usado con frecuencia cuando trabajamos con

una hoja ajena

EJEMPLO

MATRICES

Page 139: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

139

Fórmulas Matriciales: ejemplos

Vaya recorriendo las hojas del libro Matrices donde se muestran varios ejemplos del uso de fórmulas matriciales y trate de entender su funcionamiento

Realice modificaciones en las fórmulas y observe su efecto sobre los resultados

EJEMPLO

MATRICES

Page 140: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

140

Funciones Matriciales

SUMAPRODUCTO(RANGO1*RANGO2) Multiplica el valor de cada celda de un rango por la

celda correspondiente en el otro rango y suma el total de todos los productos

SUMAPRODUCTO(A1:A6*B1:B6)

TRANSPONER(MATRIZ) Cambia la orientación horizontal o vertical de una

matriz

Page 141: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

141 20/03/2012

Funciones Matriciales

Ejercicio En el libro Prácticas, realizar al final una copia de la

hoja de Esquema Multiplicar los promedios, por meses del Grupo A y el

grupo B, y sumar todos los productos

Transponer la tabla de Esquemas

Page 142: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

142

Funciones Matemáticas y Trigonométricas

Otras funciones con Matrices: MDETERM: Halla el determinante de una matriz

MINVERSA: Invierte una matriz. Aplica solo para matrices cuadradas cuyo determinante no sea cero

MMULT: Multiplica dos matrices. Las matrices deben ser compatibles para ser multiplicadas

Page 143: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

143

Funciones con Matrices

Ejercicio Invertir las siguientes matrices

Multiplicar estas matrices

1 4 7

2 5 8

3 6 9

2 4 7

2 5 8

3 6 9

Page 144: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

144

Funciones financieras

Ofrecen las operaciones contables más usuales, como el pago de intereses de un préstamo o una inversión, tasas, valores futuros, depreciaciones, etc.

Las más empleadas son las que tienen que ver con pagos de intereses en sus distintas variantes: PAGO, PAGOINT y PAGOPRIN.

Page 145: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

145

Valor del dinero en el tiempo

Supongamos que alguien decide darte una cantidad de dinero y te ofrece las siguientes opciones Recibir 800€ hoy

Recibir 9500€ en un año

Recibir 12000€ en 5 años Recibir 150€ por mes en los próximos 5 años

Para saber qué es lo que más te conviene, necesitas tener en cuenta el valor del dinero, no ahora, sino en el futuro

Page 146: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

146

Funciones Financieras

Los argumentos más comunes de las funciones financieras incluyen: Valor actual (va): la cantidad principal. Es el valor de una inversión o préstamo,

el desembolso inicial … (>0 ó <0)

Valor futuro (vf): Valor de la inversión o préstamos una vez realizados todos los pagos.

es el valor actual más los intereses.

Valor futuro que desea lograr una vez realizados todos los pagos, i.e. al final de la operación (0 por defecto)

Si invierto 5.000 durante 5 años al 6% anual, al final obtengo 6.312 (va)

Si compro un coche por 15.000, a pagar en 3 años al 7% anual, pagaré finalmente 16.673 (va)

Pago: el importe pagado periódicamente en una inversión o préstamo, cuando este importe es constante (cantidad negativa)

Pago 1, Pago 2, … Pago n: pagos periódicos cuando difieren en la cantidad

Número de períodos (nper): el número total de pagos o períodos de una inversión.

Tipo: cuando se hace al pago (0 = al final del período, 1 = al principio)

Tasa: tasa de descuento o interés por periodo (convertir a meses)

El valor actual de un préstamo es el importe del

préstamos, mientras que el valor futuro es 0 (es lo que

nos quedará al finalizar)

Page 147: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

147

Funciones Financieras

FUNCIÓN PAGO: cuota periódica (intereses más amortización) que se necesita para amortizar un préstamo en un número dado de períodos.

PAGO Tasa interés; Nper número de períodos o pagos; Va valor actual del préstamo Vf es el valor futuro o un saldo en efectivo que se desea lograr tras

el último pago (si es una operación de ahorro, sería lo que esperamos tener al final de los periodos de pago). Si vf se omite, se asume que vale 0, es decir, tras el último pago no queda

ningún saldo pendiente, lo que ocurre cuando se trata de un préstamo.

Tipo indica el vencimiento de pagos anticipado – al principio del periodo(1) vencido – al final del periodo(0)

Page 148: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

148

Pago de un Préstamo

Ejercicio Crear un libro nuevo que llamaremos Funciones

Financieras y resolver el siguiente ejercicio:

Se desea calcular el valor de la cuota mensual para un préstamo de 180.000€ con un Euríbor del 4,75%, a un plazo de 20 años

Usar las mismas unidades para todos los valores:

Convertir las tasas anuales a tasas mensuales

Expresar num_periodos en meses

Page 149: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

149

Pago de un Préstamo

Ejercicio: A partir del ejercicio anterior, realizar una tabla para

observar cual sería la cuota a pagar: Importes: 100.000, 176.00 a 189.000€

Periodos: de 15 a 25 años

Definir un formato condicional para que resalten las cuotas que estén entre 1200 y 1250 euros mensuales

Page 150: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

150

Ahorrando

Ejercicio Se desea ahorrar 350.000€ en 18 años, ahorrando una

cantidad constante cada mes.

¿Qué cantidad se debe ahorrar, si se asume que se puede devengar un 2,5% de interés anual en la cuenta de ahorros?

Page 151: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

151

Funciones Financieras

PAGOINT Calcula cuanto pagamos de interés en un período

específico siempre que trabajamos con cuotas periódicas constantes.

PAGOINT(tasa;periodo;nper;va;vf;tipo)

PAGOPRIN Calcula la parte correspondiente a la amortización de

capital de un pago perteneciente a un préstamo con cuotas periódicas constantes y a interés constante

PAGOPRIN(tasa;periodo;nper;va;vf;tipo)

Page 152: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

152

Funciones Financieras

Ejercicio Calcular la cantidad que corresponde al pago

de intereses y a la amortización de capital para un préstamo de cuota fija a 35 años sobre 250.000€, con un interés del 4,9%

A partir del apartado anterior, realizar la tabla de pago donde se detalle, período a período, cuanto se deberá pagar por intereses y cuanto por amortización de capital

Page 153: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

153

Funciones Financieras

PAGO.INT.ENTRE : Similar al PAGO.INT, pero permite especificar un intervalo de tiempo (especificando dos periodos) en lugar de uno solo de los periodos

PAGO.PRINC.ENTRE: Similar al PAGO.PRINC, pero permite establecer dicho monto entre dos períodos determinados

Page 154: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

154

Funciones Financieras

Ejercicio Con base al ejercicio anterior, calcular cuanto se

pagará de intereses y de principal o amortización durante el 10 año de pago de la deuda.

Page 155: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

155

Funciones Financieras

FUNCIÓN VA (Valor Actual) devuelve el valor actual de una inversión valor a día de hoy de la suma de una serie de

pagos que se efectúan en el futuro Por ejemplo, cuando pide dinero prestado, la

cantidad del préstamo es el valor actual para el prestamista

VA(tasa;num_per;pago;val_futuro;tipo)

Si además de los pagos periódicos hay un pago/aportación final usaremos el argumento valor_futuro

Suele utilizarse para evaluar la rentabilidad de una

inversión: si el valor actual es superior al coste de la

inversión, es una buena inversión

EJEMPLO

VA

Page 156: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

156

Funciones Financieras

Ejercicios Calcular el valor actual de un préstamo con una cuota

de 750€ mensuales al 4% de interés anual y con un plazo de 10 años

Se desea comprar una póliza de seguros que pague 500€ al final de cada mes durante los próximos 20 años. Para recibir dicha cantidad ha de invertir 60.000€ y el dinero pagado devengará un interés del 8% anual. Determinar si la compra de la póliza es una buena inversión.

Page 157: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

157

Funciones Financieras

FUNCION VF (Valor Futuro): Determina el valor en una fecha futura de una

inversión basándose en pagos periódicos constantes y en una tasa de interés constante.

Podemos verla como el contrario de VA

VF(tasa;nper;pago;va;tipo)

Page 158: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

158

Funciones Financieras

Ejercicio Se contrata un plan de pensiones depositando

2000€ al principio de cada año. La tasa media de los intereses será el 9%. Si ahora tenemos 30 años, ¿Cuánto dinero habremos acumulado a los 65?

Ahora suponga que, además, comenzó el plan aportando 7500€ de los que disponía y repita el cálculo

Page 159: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

159

Funciones Financieras

FUNCION TASA: la tasa de retorno de una inversión que genera una

serie de pagos periódicos o un único pago total

Determinar el interés de una inversión o préstamo

TASA(num_per; pago; valor_actual; valor_futuro; tipo; estimación)

Usaremos pago si queremos calcular la tasa en una serie de pagos periódicos iguales

Usaremos valor futuro si queremos calcular la tasa para un pago único

La estimación le sirve a EXCEL como indicio para el cálculo de la tasa

Page 160: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

160

Funciones Financieras

Ejercicios: Cuál es la tasa de un préstamo de 8.000€ a

cuatro años con pagos mensuales de 200€ Suponga que está considerando una inversión

que producirá 1000 € anuales en los próximos 5 años. La inversión es de 3000 €. ¿Cuál es la tasa de retorno?

Page 161: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

161

Funciones Financieras

FUNCION NPER: Nº periodos necesarios para amortizar un préstamo,

fijada una cuota periódica y un interés constante

NPER(tasa;pago;valor_futuro;tipo)

Si el argumento pago es demasiado pequeño para

amortizar la cantidad, la función devuelve un valor de error. Por tanto, el pago mensual debe ser al menos igual al interés del periodo multiplicado por el capital. En caso contrario nunca se amortizará el préstamo

Page 162: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

162

Pago de un Préstamo

Ejercicio:

¿Cuantos años deberé pagar mi hipoteca si tengo estimado solicitar 100.000€, la tasa de interés es constante en 4,75% y tengo estimado pagar 1000€ mensuales?

Page 163: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

163

Funciones Financieras

TIR - Tasa Interna de Retorno: Devuelve la tasa de interés para la que el valor actual

de los pagos es exactamente igual al coste de la inversión

Equivale a la tasa de interés producida por un proyecto de inversión con pagos (valores negativos) e ingresos (valores positivos) que ocurren en períodos regulares.

Si es mayor que la tasa de mercado es una buena inversión TIR(RANGO de VALOREs) debe haber un valor negativo, que

es el que identifica el importe de la inversión, el resto son las cuotas o beneficios que se esperan obtener

Page 164: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

164

Funciones Financieras

Ejercicio: Ha decidido abrir un restaurante con un costo inicial

estimado de 120.000€. A lo largo de los próximos cinco años espera recibir una renta neta de 25.000€, 27.000€, 35.000€, 38.000€ y 40.000€ respectivamente.

Crear una hoja de cálculo con los datos de la inversión y el porcentaje de beneficios de este negocio

Page 165: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

165

Funciones Financieras

Parámetros para el cálculo de Depreciaciones Coste: coste inicial del bien Vida: periodo de tiempo a lo largo del cual se deprecia

el bien (número de periodos)

Período: periodo individual sobre el que se desea realizar los cálculos

Valor Residual: valor que aún posee el bien después de la depreciación

Page 166: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

166

Funciones Financieras

Cálculo de Depreciaciones SLN: Calcula la depreciación de un bien por el método

directo (uniforme) durante un único periodo de tiempo

SLN(costo;valor_residual;vida)

Ejemplo:

Calcular la depreciación anual, por el método directo, de un coche que costó 8000€ nuevo y que tiene una vida útil de 10 años, al cabo de los cuales vale 500€

Page 167: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

167

Funciones Financieras

Cálculo de Depreciaciones DDB: Calcula la depreciación de un bien por doble (n)

disminución de saldo, siendo mayor los primeros períodos que los últimos

DDB(coste;valor_residual;vida;período;factor) factor = 2 por defecto

Ejemplo: Ha comprado un portátil de última generación por

valor de 5000€, cuya vida útil es de 5 años y con valor residual de 100€. Calcular la depreciación del equipo para el primer mes (doble) y para el primer año de vida (triple)

Page 168: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

168

Préstamo Balón

Un préstamo en el que se realiza un gran pago antes de que acabe el periodo Si se trata de un préstamo a 3 años, el pago o cuota mensual se

calcula como si fuera un préstamo normal a 3 años.

La idea es realizar un pago balón antes de que finalice el período del préstamo, de cara a reducir la cantidad que se paga en concepto de intereses, mientras que se mantienen pequeños los pagos iniciales.

Este tipo de préstamo implica cierto auto-control y planificación para asegurar que se dispondrá de la cantidad para afrontar el pago balón en su momento

Ejemplo de uso No dispongo ahora del dinero, pero sé que dispondré de él más

adelante

Solicito un préstamo balón y voy pagando sólo los intereses

EJEMPLO

Calculadora-PagoBalon

Page 169: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

169

Préstamo Balón

PAGO BALÓN SIN REDONDEO Obtenemos el Valor Actual del dinero que representan los pagos

que quedan por hacer

VA(D7/12;(D8-D9);-D14)

Más los intereses

(1+D7/12)*VA(D7/12;(D8-D9);-D14)

TABLA DE AMORTIZACIÓN PAGO Si es el pago balón (C24=$D$9+1) importe restante + intereses del

mes

Si no pago regular

INTERESES Se calculan cada mes sobre el importe que queda por pagar

EJEMPLO

Calculadora-PagoBalon

Page 170: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

170

Herramientas para Análisis

Existe un conjunto de herramientas y funciones complementarias diseñadas para el análisis de datos, que no están disponibles en Excel

Para verificar si estas funciones están instaladas o no, se puede ir al menú Herramientas. Si la utilidad está instalada, observará una opción denominada Análisis de Datos.

En caso contrario, ir a Herramientas Complementos y seleccionar de la lista el ítem Herramientas de análisis para que se proceda a su instalación y activación (puede necesitarse el CD de instalación de Office 2003)

Page 171: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

171

Herramientas de Análisis Estadístico

Tras instalarlas, se dispone de las siguientes opciones: Estadística Descriptiva: Produce una tabla de

medidas estadísticas a partir de un rango Histogramas: Representa de forma gráfica las

frecuencias de un rango Jerarquía y Percentiles: Clasifica un rango de datos

y los ordena

Page 172: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

172

Herramientas de Análisis Estadístico

Estadística Descriptiva Produce una tabla que describe los datos usando

algunos valores estadísticos clásicos Para cada variable en el rango de entrada se devuelve una

lista de estadísticas Cada una se podría obtener combinando fórmulas

Las opciones K-ésimo mayor y menor proporcionan información adicional Por defecto ya se muestran el mayor y el menor

Las salidas son valores constantes, no son fórmulas, por lo tanto, si los datos cambian, deberán regenerarse las estadísticas

Page 173: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

173

Herramientas de Análisis Estadístico

Jerarquía y Percentil Crea una tabla que muestra un ranking de ordinales y

percentiles para cada valor del rango de datos

Page 174: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

174

Herramientas de Análisis Estadístico

Histograma Gráfico que representa el número de medidas o

frecuencia para cada intervalo (o clase) Podemos especificar el rango de intervalos para los

que obtener el histograma Debemos definir las clases en orden ascendente No tienen por qué ser uniformes

Podemos dejar que EXCEL lo haga por nosotros =(MAX(rango_datos) – MIN(rango_datos)) / 10

El resultado se puede ordenar de acuerdo a la frecuencia de cada intervalo

De nuevo, la salida son constantes, así que es preciso regenerar si cambian los datos de origen

Page 175: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

175

Funciones Estadísticas

Ejercicio En el libro Prácticas, realizar una copia de la hoja Ejercicios de Formato

Crear los datos resumen que proporciona la herramienta Estadística Descriptiva para cada uno de los productos

Crear una Jerarquía de Ventas teniendo en cuenta el Total de ventas mensual

Crear un Histograma con los datos de Ventas Anuales en los siguientes rangos:

0 -500€

500 – 1000€

1000 – 1500 €

…..

7500 – 8000€

Page 176: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

176 176

Agenda

Empezar a trabajar con EXCEL Personalización del Entorno de Trabajo

Edición de Celdas

Fórmulas y Funciones (introducción)

Selección de Celdas Trabajar en Modo Grupo

Referencias a Celdas

Nombrar Celdas

Opciones Avanzadas de Formato y Presentación Alineación de Texto

Formatos Condicionales

Precisión de Pantalla

Uso de Estilos

Paneles

Definición de Esquemas

Impresión y Presentación

Vistas Personalizadas

Protección de Celdas y Hojas

Funciones Funciones de Texto

Funciones Matemáticas

Funciones Lógicas

Funciones (…)

Funciones de Información

Funciones de Búsqueda y Referencia

Funciones de Fecha y Hora

Funciones Estadísticas

Funciones Matriciales

Funciones Financieras

Herramientas para Análisis

Trabajar con Datos Datos Consolidados

Filtros

Filtros Avanzados

Macros

Validación de Datos

Subtotales

Función Buscar Objetivo

Escenarios

Tablas Dinámicas

Trabajar con Gráficos

Extracción de Datos y Libros Compartidos

Page 177: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

177

Consolidar Datos

Es la utilidad para combinar valores de un conjunto de hojas de cálculo de un mismo libro o de libros distintos Operaciones que implican varias hojas y/o libros de trabajo

Se puede combinar información de hasta 255 hojas

Podemos consolidar datos utilizando fórmulas que usen referencias externas

=SUMA(Hoja2:Hoja10!A1)

=[Region1.xls]Hoja1!B2+[Region2.xls]Hoja1!B2

Podemos usar la opción Consolidar del menú Datos Por posición: Las hojas consolidadas deben tener el mismo

formato

Por categoría: Consolida los datos de acuerdo a rótulos de categorías

Page 178: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

178

Consolidar Datos

Consolidación por Posición EXCEL aplica la función de consolidación a las mismas

celdas de cada hoja de apoyo

Deben tener exactamente la misma estructura

Utilización Seleccionar rango donde se introducirán los datos

consolidados

Abrir el cuadro de diálogo Datos Consolidar Seleccionar el rango de datos a consolidar en cada

hoja

En este caso, es preferible introducir los rótulos

nosotros mismos. Cuando dejamos que EXCEL lo

haga pueden producirse resultados no deseados

Page 179: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

179

Datos Consolidados

Consolidación por Categoría EXCEL aplica la función de consolidación a un conjunto de celdas

distinto en cada hoja de apoyo

Utilización [Introducir los encabezados de columna (filas)] Seleccionar rango donde se introducirán los datos consolidados

(basta con la celda inicial) Abrir el cuadro de diálogo Datos Consolidar Seleccionar el rango de datos a consolidar en cada hoja,

incluyendo los encabezados de fila y columna Seleccionar Columna Izquierda en la

opción Usar Rótulos en del cuadro de diálogo Si seleccionamos la opción Crear vínculos con los datos de origen,

EXCEL usa fórmulas para los datos consolidados, que define como subtotales (datos consolidados son actualizables)

De otro modo, los datos consolidados no son actualizables

Page 180: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

180

Consolidados

Ejercicio: A partir de los datos de ventas de productos

lácteos del libro Consolidación Insertar una nueva hoja llamada Datos Consolidados Consolidar los datos de las Hojas 1 a 4:

Realizar primero la consolidación por posición

Realizar luego la consolidación por categoría

Page 181: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

181

Categorías de funciones

Funciones de bases de datos: En realidad son como sus homónimas,

salvo que operan sólo sobre celdas que cumplan ciertos criterios

Dichos criterios deben expresarse utilizando celdas

Resultan muy útiles para el análisis de información de listas que cumplen criterios particulares, porque sus homólogas no son capaces de ignorar celdas aunque los datos se muestren filtrados

EJEMPLO

Bases de Datos - BD

Page 182: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

182

Definición de Criterios con caracteres comodín: ejemplos

=”=January” ↔ ‘=January => contiene exactamente …

January => empieza por …

<>C* => lo que sea menos algo que empiece por ‘C’

>=L => empieza por una letra que vaya de la L a la Z

*campo* => cualquiera que contenga la palabra campo …

Sm* => empiezan por SM …

s*s => empieza por s y hay más Ss después (da igual en qué posiciones)

s?s => empieza por s y tiene una s como tercer carácter (no sólo palabras de 3 caracteres)

="=s*s” => empieza y termina por s

<>*c => no acaban en c

=???? => cuatro letras

<>???? => no contienen 5 letras (más o menos)

<>*c* => no contienen Cs

~? => contienen un único signo de interrogación (la tilde convierte el signo de interrogación en un carácter normal)

= => contienen un blanco

<> => cualquier entrada que no esté en blanco

=“=c” => sólo el carácter ‘c’

No distingue MAY de MIN. Por ejemplo, el

criterio se* devolverá celdas que

contengan Sevilla, sereno y SEAT

Page 183: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

183

Funciones de Bases de Datos

Ejercicio

Abrir el libro de trabajo Tablas Dinámicas e ir a la hoja Datos.Pedidos

Insertar algunas filas en la parte superior que usará para definir criterios

Utilizando funciones de Bases de Datos, calcule: El número de unidades vendidas durante el mes de

Enero y el mes de Febrero. De las de Febrero, contabilizar sólo aquellas cuyo precio/unidad

sea mayor que 120

Page 184: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

184

Filtros

Los filtros son criterios que indican a Excel qué información ha de mostrar dentro de una lista.

Cuando se aplica un filtro, Excel sólo muestra los registros (filas) que cumplen con la condición.

Los filtros más sencillos son los AUTOFILTROS: Menú Datos AUTOFILTRO

Si sólo queremos filtrar por algunas columnas, seleccionar sus encabezados y activar el autofiltro

Podemos refinar o personalizar los criterios proporcionados por defecto

Salvo en los casos que veremos, las

fórmulas que actúan sobre datos filtrados no

se ajustan para ser computadas sólo sobre

los datos visibles

EJEMPLO

Filtros

Page 185: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

185

Filtros Avanzados

Permite definir filtros que soporten: Criterios que impliquen más de dos condiciones

para una misma columna (Enero y Febrero y Marzo …)

Criterios calculados Extracción de filas para copiar en otra parte Mostrar sólo valores únicos (evitar duplicados)

Si queremos extraer datos a otra hoja usando

un filtro avanzado, tenemos que definir el filtro

desde la hoja DESTINO haciendo referencia a

las celdas de la hoja ORIGEN

Page 186: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

186

Filtros Avanzados

Los criterios deben definirse utilizando celdas (similar a las funciones de Base de Datos) Una o más filas, donde la primera contiene nombres de campos y

el resto los criterios

Los criterios definidos en distintas columnas se combinan con una operación Y

Los criterios definidos en una misma columna se combinan en un operación O

Suelen usarse filas por encima de los datos

No es preciso definir un criterio para todas las columnas

No se autoactualizan

EJEMPLO

Filtros Avanzados

Provincia empieza por ‘Ba’ y hay más de 500 AUDI matriculados O

Provincia empieza por ‘M’ y hay más de 100 AUDI matriculados

Page 187: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

187

Criterios Calculados

Definimos una fórmula que devuelva un valor lógico a partir de la realización de algún cálculo sobre la lista de datos Los criterios se definen utilizando referencias relativas

a la primera fila de la lista de datos No utilizar nombres de los campos de la lista

Podemos definir tantos como queramos y combinarlos con criterios simples EJEMPLO

Filtros Avanzados

Page 188: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

188

Filtros

Ejercicio A partir de la hoja Precio Petroleo Diario del libro

Practicas definir los siguientes filtros Autofiltro para mostrar los 10 mejores precios del mes de

Diciembre de 2004

Quitar el Autofiltro

Aplicar un filtro avanzado para el año 2005, donde:

El valor del mes de Febrero sea mayor de 32

El valor del mes de Abril sea mayor de 40

El valor del mes de Septiembre sea mayor de 50

El valor de la suma de los meses de Julio a Septiembre del 2005 sea mayor a 100

Page 189: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

Macros

Permiten automatizar tareas y unirlas en una sola

Crear una macro Herramientas Macro Grabar nueva macro …

Realizar las acciones deseadas y pulsar el botón Detener al concluir

Page 190: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

Macros

Ejecutar una macro Herramientas Macro Macros …

Page 191: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

Macros

Asociar un botón a la ejecución de una macro Menú Ver Barras de Herramientas Formulario Seleccionar la opción del botón El puntero del ratón se transforma en una cruz.

Debemos hacer clic sobre la zona de la hoja donde queramos insertar el botón

Aparece el cuadro de diálogo Asignar macro

Con el botón derecho sobre el botón podemos: Modificar Texto

Asignar macro …

Page 192: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

192

Macros

Ejercicio Crear una nueva hoja de cálculo en el libro Prácticas Abrir el submenú Macro del menú Herramientas.

opción Grabar nueva macro... Aparece el cuadro de diálogo Grabar macro. Escribir el nombre de la macro, EjemploMacro y pulsa Aceptar. Ahora estamos grabando, vamos a realizar las acciones necesarias

para dar formato la hoja de trabajo. Presionar sobre el botón Negrita de la barra Formato. Presionar sobre el botón Cursiva de la barra Formato. Escoger el tipo de fuente Abbess. Escoger el tamaño de la fuente en 14 puntos.

Se ha definido un nuevo formato para una cabecera de datos, por tanto se finaliza la grabación de la macro. Presionar sobre el botón detener de la barra Macro, o acceder al menú

Herramientas - Macro - Detener grabación

Page 193: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

193

Macros (II)

Ejercicio Escribir en la celda D1 Cabecera, en la celda E1 de y

en la celda F1 prueba.

Seleccionar las celdas anteriores D1, E1 y F1 y ejecuta la macro que grabaste

Observar como las celdas seleccionadas adoptan el formato automáticamente

Añadir un botón a la hoja y asóciale la macro grabada

Comprobar el funcionamiento del botón

Page 194: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

194

Macros y Filtros Avanzados

Ejercicio Abrir la hoja Datos.Pedidos e insertar varias filas en la parte superior

En la primera copiar los nombres de campos

Seleccionar la primera y segunda filas y darles un nombre (Criterio_Y)

Ampliar la selección a la siguiente fila y darle un nuevo nombre (Criterio_O)

Grabar dos nuevas macros que activen un filtro avanzado tomando como criterios las celdas Criterio_Y y Criterio_O

Insertar dos autoformas y asígnarles la ejecución de cada una de las macros anteriores

Page 195: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

195

Validación de Datos

Sirve para asegurarse que las nuevas entradas o las entradas modificadas de una lista satisfacen ciertos criterios.

Se pueden especificar: los tipos de datos permitidos el rango de valores aceptables

incluso introducir una lista de valores correctos

Las reglas de validación pueden ser obligatorias o de advertencia

Si la celda contienen una fórmula en lugar

de un valor, la regla de validación no tiene

ningún efecto

Page 196: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

196

Validación de Datos

Seleccionar rango de celdas a controlar (TODO el rango, aunque aún no tenga valores introducidos)

Menú Datos Validación Escoger tipo de Validación

Un uso interesante de esta funcionalidad es detectar valores no válidos en una gran lista de datos Seleccionar los datos y definir criterio de validación Usar la auditoria de fórmulas para mostrar los datos

no válidos Menú Herramientas Auditoría de Fórmulas

Page 197: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

197

Validación de Datos: uso de fórmulas

Podemos especificar el criterio de validación usando una fórmula que devuelve un resultado lógico (V ó F) Escoger la opción Personalizada en el desplegable

Permitir Definimos la fórmula usando referencias relativas a la

primera celda del rango escogido

Ejemplos =ESTEXTO(A1) =ESNUMERO(A1)

= A2 > A1

Page 198: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

198

Validación de Datos

Ejercicio:

En la hoja Validación del libro Practicas, aplicar a las celdas en azul un criterio de validación que limite el valor de la celda a valores numéricos. Utilizar el estilo Límite para el mensaje de error, que impide introducir datos no válidos en la celda.

A las celdas en verde aplicar un criterio de validación que limite la longitud de las entradas de texto a siete caracteres. Utilizar el estilo Advertencia para el mensaje de error, que permite cancelar la introducción de datos o introducir el valor no válido en la celda seleccionada.

A las celdas en amarillo un criterio de validación que restringe a un número entero que esté entre 1 y 10. Utilizar el estilo Información

A las celdas en rosa aplicar un criterio de validación que limita los datos válidos a una lista de valores. Cuando se seleccione una de las celdas del rango, aparece una lista desplegable. Utiliza el estilo Límite para el mensaje de error, lo que impide introducir datos no válidos en la celda.

Usar una fórmula para no permitir valores repetidos en las celdas en naranja Usar la función CONTAR.SI()

Usar una fórmula para permitir sólo valores que empiecen por el carácter “a” Usar la función IZQUIERDA()

Page 199: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

199

Subtotales

Sirve para aplicar fórmulas de agregación a grupos de entradas de una lista. Ventas de un vendedor, gastos por acreedor, etc.

La creación de subtotales involucra tres partes: Seleccionar el campo sobre el que se pueden

identificar distintos grupos Seleccionar la función que se aplicará para el subtotal Seleccionar el campo para el que se ejecutará el

subtotal

Alternativamente, puede utilizar la opción Subtotales del menú Datos para realizar la misma función de forma más intuitiva Los datos de la lista deben estar ordenados

previamente

Page 200: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

200

Tipos de Subtotales

Tipo de Operación Operación

1 PROMEDIO

2 CONTAR

3 CONTARA

4 MAX

5 MIN

6 Multiplicación (PRODUCTO)

7 Desviación estándar (DESVEST)

8 Desviación estándar de la población total (DESVESTP)

9 SUMA

10 Varianza (VAR)

11 Varianza de la población total (VARP)

Page 201: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

201

Subtotales

Ejercicio Realizar una copia de la hoja Base de Datos

del libro Practicas Generar los subtotales para cada localidad de

acuerdo a las unidades vendidas.

A partir de los datos de la hoja Datos .CafeCacaoTe, obtener: Subtotales de Suma para los Totales de acuerdo a

la sucursal

Subtotales de Cuenta para los Totales de acuerdo al mes en curso

Page 202: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

202

Subtotales

Otra aplicación de la función SUBTOTAL es realizar cálculos sobre listas de datos filtrados A la hora de realizar los cálculos ignora aquellas

celdas ocultas como resultado de un filtrado

Si usamos la función SUMA, estamos

incluyendo en el cálculo las celdas

intermedias

EJEMPLO

Libro Pedidos

Page 203: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

203

Función Buscar Objetivo

Calcular el valor a introducir en una celda para que una fórmula produzca el resultado deseado

Sirve para encontrar una solución rápida a un problema numérico, sin la necesidad de iterar manualmente. Equivale a resolver el sistema de ecuaciones

Este es el resultado que deseo obtener con esta fórmula ¿Cuál es la entrada que me permite obtenerlo? Definimos la fórmula dando un valor intuitivo a la celda para la

que luego Excel tratará de encontrar el valor concreto que haga cumplirse la fórmula

Lanzamos la opción Herramientas Buscar Objetivo En Definir la celda se especifica la celda que contiene la

fórmula. Puede escribirse una referencia de celda o un nombre Con el valor contiene el resultado que se desea obtener de dicha

fórmula Cambiando la celda determina la celda cuyo valor EXCEL cambia

a fin de lograr el resultado deseado.

Page 204: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

204

Función Buscar Objetivo

Celda Objetivo Es la celda a la cual se le quiere imponer un valor. Tiene que ser una función o fórmula que sea dependiente de los datos contenidos en la Celda de dato.

Aquí se ingresa el valor que se le quiere imponer a la Celda Objetivo.

Celda de dato es la celda que se va a cambiar para hacer que la Celda Objetivo llegue al valor especificado. El contenido de esta celda tiene que ser un número (no fórmula) del cuál dependa la Celda Objetivo.

Page 205: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

205

Función Buscar Objetivo

Ejercicio Se desea conocer la hipoteca máxima a 30 años que se

puede afrontar con una tasa de interés del 6,5% si tiene que limitar las cuotas mensuales a 2000€

Page 206: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

206

Escenarios

El Administrador de Escenarios permite cambiar el valor de varias celdas para ver el hipotético resultado y guardarlo

Responde a la pregunta: ¿Qué pasaría si…?

Permite generar un informe resumen sobre los resultados que producen los diferentes escenarios

Herramientas Escenarios Definir, mostrar, ocultar, resumir escenarios

EJEMPLO

Escenarios

Para que las variables se muestren como un nombre

y no con la referencia a la celda correspondiente,

debemos nombrar dichas celdas

Page 207: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

207

Escenarios

Ejercicio A partir del modelo de negocio construido en la hoja Escenarios

del libro Practicas, crear un escenario inicial donde las celdas cambiantes serán: el valor de los ingresos por visita de cliente (C5)

los costes derivados de dicha visita (C6)

El segundo escenario va a incrementar los costes por visitas en un 5%, pero baja los ingresos en el 5%

Un tercer escenario, donde a partir del escenario anterior, los costes incrementarán un 3% Extraer la Tabla Resumen de los Escenarios

Repetir el proceso pero antes dar nombre a las celdas para que la Tabla Resumen mejore su legibilidad

Page 208: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

208

Tablas Dinámicas

Un informe de tabla dinámica es una tabla interactiva que combina y compara rápidamente grandes volúmenes de datos. Podemos verlas como informes dinámicos generados a partir de una Base

(o Lista) de Datos

Es posible girar las filas y las columnas para ver diferentes resúmenes de los datos de origen, y mostrar los detalles de determinadas áreas de interés.

En los informes de tabla dinámica, cada columna o campo de los datos de origen se convierte en un campo de tabla dinámica que resume varias filas de información. Un campo de datos, como Suma de Ventas, proporciona los valores que van a resumirse

La tabla dinámica puede ser configurada para que muestre todos o solo una parte de los datos utilizados para su construcción

Page 209: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

209

Tablas Dinámicas (creación)

Descargar el libro “TablasDinamicas.xls” 1. Poner el cursor en cualquier celda de los datos mostrados en la hoja

DatosPeajes-1, que contiene datos a cerca de la circulación de vehículos a través de una estación de peaje

2.En el menú Datos, seleccionar Informe de tablas y gráficos dinámicos

Aparece el siguiente cuadro:

¿Dónde están los datos que desea analizar? Marcar Lista o base de datos

de Microsoft Excel

¿ Que tipo de informe desea crear? Marcar Tabla dinámica

Clic en Siguiente

Page 210: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

210

Tablas Dinámicas (creación)

Rango de Datos a) Seleccionar el rango de la

tabla, incluyendo la fila de titulo b) Clic en siguiente

Ubicación de la Tabla Seleccionar si queremos incluir

la tabla en una hoja que ya existe o deseamos utilizar una hoja nueva (opción recomendada)

Page 211: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

Tablas Dinámicas (creación)

En este punto puede pulsar finalizar y observar el resultado …

… una tabla vacía que podemos “rellenar” arrastrando campos de la Lista de Campos a filas y/o columnas y/o encabezados de página .. y campos numéricos a la zona de datos

Page 212: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

212

Tablas Dinámicas: filtrar

Cada campo incluye un desplegable que permite filtrar los valores mostrados Ejemplo: mostramos sólo las dos primeras semanas

Page 213: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

213

Tablas Dinámicas: rediseñar la tabla

También podemos lanzar el asistente desde la barra de herramientas para rediseñar la tabla

En general, la barra de herramientas

proporciona las opciones para modificar la

tabla dinámica de forma más intuitiva

Page 214: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

Tablas Dinámicas: ver detalle

Haciendo doble clic sobre cualquier celda, EXCEL produce una hoja aparte de detalle, donde se enumeran qué valores han contribuido al valor de la celda en cuestión …

Page 215: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

Tablas Dinámicas …

Utilizando los datos de la hoja DatosPeajes-2 cree una tabla como la que se muestra en la figura

Page 216: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

Tablas Dinámicas: configuración de campos

Por defecto, el área de datos muestra la suma de valores para cada cruce de fila y columna …

… se puede seleccionar otro operador para mostrar los datos Botón derecho sobre cualquier celda Configuración

de Campo En lugar de mostrar la suma de lo recaudado por el pago de camiones a lo largo del

mes, se muestra el promedio (la media por semana)

RECUERDE que si hacemos doble clic sobre la

celda veremos

el detalle (lo recaudado cada semana)

Page 217: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

Tablas Dinámicas: configuración de campos

Siguiendo la misma idea, podemos mostrar la contribución de ese valor al total general …

Mostramos el porcentaje de la

recaudación y los beneficios que cada

mes representa sobre el total

Page 218: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

Tablas Dinámicas: campos calculados

También podemos añadir nuevos campos a la tabla que sean el resultado de realizar operaciones con los campos existentes Tabla Dinámica Fórmulas Campo Calculado

Añadimos un nuevo

campo (Costes) que

computa la diferencia

entre lo recaudado y

los beneficios

Page 219: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

Tablas Dinámicas: configurar campo

EXCEL permite aplicar filtros automáticamente del tipo: “sólo los 2 mejores”, etc … Clic Derecho sobre el nombre del campo

Configuración de Campo Avanzado

En esta ocasión mostramos sólo los 2 meses en los cuales el peaje pagado por las motocicletas ha reportado mayor recaudación

El color azul indica que

el campo está filtrado

Page 220: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

Tablas Dinámicas: agrupar

Podemos definir distintos niveles de agrupamiento: Seleccionar los encabezados de las columnas que

queremos agrupar

Tabla Dinámica Agrupar y Mostrar Detalle Agrupar

Para cambiar el nombre del nuevo campo bastará con posicionarnos sobre él y escribir el nuevo nombre

Page 221: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

Tablas Dinámicas: 3 dimensiones

Podemos utilizar el campo de página para definir tablas de 3 dimensiones como la de la imagen

En la parte superior seleccionamos el mes que nos interesa y en la parte inferior encontramos el detalle para ese mes (las 4 semanas correspondientes)

Page 222: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

222

Tablas Dinámicas

Observaciones Las tablas dinámicas no se

actualizan automáticamente, pero podemos invocar la actualización después de haber introducido información adicional

RECUERDE insertar las nuevas filas de datos entre

las filas existentes para que la tabla dinámica

compute los nuevos datos

Page 223: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

Tablas Dinámicas

Ejercicio A partir de la hoja Datos.Ventas del libro

TablasDinamicas, cree la siguiente tabla dinámica

Page 224: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

Tablas Dinámicas

Ejercicio (II) La tabla anterior mostraba, para cada cliente y semana, la suma del

campo PRECIO y la suma del campo ARTICULO

El primero sirve para calcular lo recaudado por ventas, pero el segundo no ofrece información útil ya que el campo ARTICULO contiene códigos de artículos (estamos sumando códigos)

Sería más interesante saber el volumen de ventas (cuántas ventas hemos realizado)

Para ello, seleccione la opción CUENTA para mostrar el campo ARTICULO y que la tabla muestre la apariencia siguiente

Page 225: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

225

Tablas Dinámicas

Ejercicio Cree una tabla dinámica a partir de la hoja

Datos.VentasDiarias donde se muestren las ventas realizadas diariamente

Realice los agrupamientos necesarios para que la tabla muestre la apariencia de la figura de la derecha

RECUERDE, para definir niveles de agrupamiento: Botón

Derecho sobre el campo por el que desea agrupar

Agrupar y Mostrar Detalle

Agrupar

(si se le plantean varias opciones, elija por cual desea definir

niveles de agrupamiento)

Page 226: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

226

Tablas Dinámicas: campos calculados

Ejercicio A partir de la hoja Datos.VentasComerciales cree una nueva Tabla

Dinámica

Utilice las opciones que permiten añadir campos calculados a la tabla para que su apariencia final sea como la que se muestra en la figura

Datos

Resultado

Page 227: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

227

Tablas Dinámicas

Ejercicio Con los datos contenidos en la Hoja

Datos.CafeCacaoTe, que contiene un Informe de Ventas, crear una Tabla Dinámica que tenga: los Productos a nivel de página

la Sucursal como fila

El Mes como columna

Use el menú Tabla Dinámica para mostrar la información referente a las ventas de cada tipo de producto en una página distinta Menú Tabla Dinámica Mostrar Página

Page 228: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

228

Tablas Dinámicas

Ejercicio: A partir de los datos de la hoja Datos.Pedidos cree

una nueva tabla dinámica como la siguiente

Page 229: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

229

Tablas Dinámicas

Ejercicio: Realice las modificaciones adecuadas para que la tabla

presente el siguiente aspecto (opciones Agrupar/desagrupar …) Seleccione los 3 primeros meses y cree un nuevo grupo

dándole el nombre de 1º Trimestre … Seleccione los primeros 70 productos (aprox.) y agrúpelos

dando los nombres Comestibles y No Comestibles a los grupos resultantes

Page 230: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

230

Tablas Dinámicas

Ejercicio: Utilizando de nuevo la hoja Datos.Pedidos cree una nueva Tabla

Dinámica, esta deberá presentar el siguiente aspecto: Tabla Dinámica Fórmulas Campo Calculado añadir de nuevo el

campo Total.

Tabla Dinámica configuración del Campo Opciones Mostar Como Porcentaje del Total

Tabla Dinámica -> Fórmulas -> Campo Calculado, añadir de nuevo el campo Total, pero esta vez incrementando su valor en un 5 y un 10%

Page 231: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

231

Gráficos Dinámicos

Los gráficos dinámicos son aquellos que se obtienen a partir de una tabla dinámica. Podemos crear la tabla y el gráfico a la vez o crear un

gráfico a partir de una tabla existente

Al igual que en las tablas, en los gráficos dinámicos es posible cambiar la posición de las categorías de datos dentro del gráfico Los cambios en uno se reflejan automáticamente en el

otro

Page 232: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

232

Gráficos Dinámicas

Ejercicio A partir de la última tabla dinámica que creo

para los datos de la hoja Datos.Pedidos, utilice el Asistente para Gráficos que se incluye en la barra de herramientas y cree un gráfico asociado a la tabla.

Realice modificaciones en el gráfico y/o la tabla y compruebe cómo se reflejan automáticamente en el otro objeto

Page 233: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

233

Categorías Multinivel

Ejercicio Utilice la Tabla Dinámica construida a partir de los datos del café, té y

cacao para obtener el siguiente gráfico de Cilindros en 3D

0

500

1000

1500

2000

2500

Cacao Café Te

Gráfico Dinámico

ene-07

feb-07

Sucursal (Todas)

Suma de Total

Producto

Mes

Page 234: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

234

Categorías Multinivel

Ejercicio De nuevo utilice la tabla dinámica con los datos del café té y

cacao para obtener el siguiente gráfico de Áreas en 3D

0

100

200

300

400

500

600

700

800

900

1000

Barcelona Madrid Valencia Barcelona Madrid Valencia Barcelona Madrid Valencia

Cacao Café Te

ene-07

feb-07

Gráfico Dinámico

ene-07

feb-07

Coloque campos de página aquí

Suma de Total

Producto Sucursal

Mes

Page 235: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

235 235

Agenda

Empezar a trabajar con EXCEL Personalización del Entorno de Trabajo

Edición de Celdas

Fórmulas y Funciones (introducción)

Selección de Celdas Trabajar en Modo Grupo

Referencias a Celdas

Nombrar Celdas

Opciones Avanzadas de Formato y Presentación Alineación de Texto

Formatos Condicionales

Precisión de Pantalla

Uso de Estilos

Paneles

Definición de Esquemas

Impresión y Presentación

Vistas Personalizadas

Protección de Celdas y Hojas

Funciones Funciones de Texto

Funciones Matemáticas

Funciones Lógicas

Funciones (…)

Funciones de Información

Funciones de Búsqueda y Referencia

Funciones de Fecha y Hora

Funciones Estadísticas

Funciones Matriciales

Funciones Financieras

Herramientas para Análisis

Trabajar con Datos Datos Consolidados

Filtros

Filtros Avanzados

Macros

Validación de Datos

Subtotales

Función Buscar Objetivo

Escenarios

Tablas Dinámicas

Trabajar con Gráficos

Extracción de Datos y Libros Compartidos

Page 236: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

236

Creación de diagramas y gráficos

Excel puede crear gráficos a partir de los datos de una hoja de cálculo.

El usuario puede incrustar un gráfico en una hoja de cálculo o crear el gráfico en una hoja especial para gráficos (hoja de gráficos).

En cualquier caso, el gráfico queda vinculado a los datos a partir de los cuales fue creado, por lo que si en algún momento los datos cambian, el gráfico se actualizará de forma automática.

Los gráficos de Excel contienen muchos objetos que pueden ser seleccionados y modificados individualmente. SUGERENCIA: incluyendo una fila vacía al

final del rango de datos utilizado para crear el

gráfico, aseguramos que futuras

“ampliaciones” serán mostradas en el gráfico

Page 237: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

237

Asistente para gráficos

Para crear un gráfico con el Asistente, deben seguirse los siguientes pasos: Seleccionar los datos a representar en el gráfico.

Seleccionar el menú Insertar / Gráfico o hacer clic en el botón de Asistente para Gráficos.

A continuación aparece el primero de una serie de cuadros de diálogo del Asistente para Gráficos, cuyas indicaciones deben seguirse para terminar creando el gráfico deseado

Page 238: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

238

Asistente para Gráficos: tipo de gráfico

1

2

Podemos definir nuevos tipos de gráficos utilizando las

opciones de gráficos elaborados por nosotros mismos:

Menú Contextual Tipo de Gráfico Tipos

Personalizados Definido por el Usuario Agregar

Page 239: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

239

Asistente para gráficos: datos de origen

3

4 4

EXCEL se refiere a los ejes como

Eje X (Datos) - Eje Y (categorías)

No necesariamente todos los datos han de ser

contiguos, podemos escoger rangos alternos

Page 240: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

240

Asistente para gráficos: opciones de Gráfico

6

5

6

5

EXCEL siempre escala el eje de valores desde 0, lo

que en ocasiones puede representar un problema.

Este comportamiento no puede modificarse con el

asistente, pero si a posteriori

Page 241: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

241

Selección de Objetos Gráficos

Los gráficos de Excel están compuestos por objetos tales como marcadores, leyendas, títulos, ejes, texto y la propia área del gráfico

El usuario puede configurar los gráficos, añadir objetos y dar formato a los ya existentes

Modificar los elementos de un gráfico Clic sobre el objeto con el botón derecho y usar el

menú contextual que permite cambiar sus características

Seleccionar el objeto y pulsar el botón de propiedades de formato en la barra de gráficos

Menú Gráfico Opciones de Gráfico

Page 242: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

242

Barra de Herramientas gráficas

La barra de herramientas Gráfico puede visualizarse en la pantalla por medio del menú contextual.

En esta barra está el botón Tipo de gráfico, que permite desplegar un menú donde es posible elegir y modificar el tipo de gráfico empleado.

Acceso a elementos difíciles de

seleccionar con el ratón

Page 243: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

243

Gráficos

Ejercicio: A partir de los datos de la hoja Datos para Gráfico, crear un

gráfico de barras con los datos de la matriz de frutas

VENTA DE FRUTAS

29

23

87

3425

54

34

3

23 21

37 10

2532

4150

5968

7786

95104

4

76

21

5059

3

75

23

93100

3 4

0

20

40

60

80

100

120

Ene

ro

Febre

ro

Mar

zoAbr

il

May

o

Junio

Julio

Ago

sto

Sep

tiem

bre

Oct

ubre

Nov

iem

bre

Dicie

mbr

e

Meses

Pro

du

cció

n

Plátano Fresas Melocotones

Page 244: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

244

Gráficos

Ejercicio: A partir de los datos de la hoja Datos para Gráficos, crear un

gráfico de columnas con los datos de Producción de Café

Comparación por Zonas

0

10

20

30

40

50

60

70

80

90

100

1er trim.2do trim.3er trim.4to trim.

Trimestre

Pro

du

cció

n

Este

Oeste

Norte

Page 245: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

245

Gráficos

Ejercicio A partir de los datos de la hoja Datos para Gráficos, crear un

gráfico de líneas en 3D con los datos de Producción de Café

1e

r tr

im.

3e

r tr

im.

Este

Oe

ste

No

rte

020406080

100

Producción

Trimestre

s

Zona

s

PRODUCCION DE CAFÉ

Este

Oeste

Norte

Este 20,4 27,4 90 20,4

Oeste 30,6 38,6 34,6 31,6

Norte 45,9 46,9 45 43,9

1er trim. 2do trim. 3er trim. 4to trim.

Page 246: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

246

Gráficos

Precio Promedio Mensual

0,00

10,00

20,00

30,00

40,00

50,00

60,00

70,00

80,00

90,00

100,00

Diciem

bre 200

4

Abr

il

Ago

sto

Diciem

bre

Abr

il

Ago

sto

Diciem

bre

Abr

il

Ago

sto

Diciem

bre

Abr

il

Pre

cio

Precio Promedio Mensual

Ejercicio A partir de los datos de la

hoja Precio Petróleo Diario crear un gráfico de líneas con los datos del Precio del Petróleo

Page 247: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

247

Gráficos

Ejercicio A partir de los datos de la hoja PNB Países Europeos del libro

Prácticas crear un gráfico de circular con efecto 3D como el mostrado en la figura

PNB Paises Europeos Año 2002

14.674

18.382

17.640

5.000

9.191

15.1769.072

14.458

17.609

14.508

13.554

Para rotar el gráfico

· Verticalmente:

Formato Serie de Datos Seleccionada Opciones · Horizontalmente:

Gráfico Vista en 3D

Page 248: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

248

Líneas de Tendencia

Una línea de tendencia describe la tendencia general de una serie de datos.

Puede ser una media móvil, una recta de regresión lineal o una línea generada con uno de los diversos ajustes de curvas no lineales

Para crear una línea de tendencia debemos: Crear un gráfico Seleccionar una serie y con el menú contextual hacer

clic en la opción Agregar línea de tendencia

Page 249: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

249

Líneas de Tendencia

Page 250: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

250

Líneas de Tendencia

Ejercicio: A partir de los datos del libro Precio Petróleo Diario, hacer un

gráfico de Dispersión y representar la línea de tendencia en el Precio para el Año 2008

Tendencia Petróleo 2008

79,5280,23

88,73

93,34

76,00

78,00

80,00

82,00

84,00

86,00

88,00

90,00

92,00

94,00

96,00

Pre

cio

Serie1 Linea de Tendencia

Page 251: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

251

Gráficos combinados

Permiten representar dos o más series de datos con formatos diferentes sobre un mismo gráfico. Esta característica facilita la comparación entre las distintas series

También puede utilizarse en caso de necesitar dos ejes de abscisas diferentes

Para introducir un gráfico combinado: Se construye un gráfico del mismo tipo para todas las

series Se selecciona la serie para la que se quiere otro tipo de

gráfico y se cambia a través de las opciones del menú contextual Gráfico Tipo de Gráfico

Page 252: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

252

Gráficos Combinados

Ejercicio A partir del gráfico de la matriz de frutas, crear un gráfico

combinado

VENTA DE FRUTAS

29

23

87

3425

54

34

3

23 213

4

76

21

5059

3

75

23

93100

3 47 10

2532

4150

5968

7786

95104

0

20

40

60

80

100

120

Ene

ro

Febre

ro

Mar

zoAbr

il

May

o

Junio

Julio

Ago

sto

Sep

tiem

bre

Oct

ubre

Nov

iem

bre

Dicie

mbr

e

Meses

Pro

du

cció

n

Plátano Melocotones Fresas

Page 253: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

253

Otros tipos de gráfico: Gráficos de Burbuja

Es un diagrama de dispersión en el que cada punto de datos ofrece tres elementos de información en lugar de dos.

En un gráfico de burbujas, la posición de la burbuja está determinado por los dos primeros valores, mientras que el tamaño de la burbuja dependerá del tercer atributo del punto.

Page 254: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

254

Otros tipos de gráfico: Gráficos de Burbuja

Ejercicio: Tomar los datos de la tabla Comparación de Productos de la hoja

Datos para Gráficos y crear un gráfico de Burbujas como el que se muestra a continuación Eje X: Nº de Competidores

Eje Y: Ventas

Tamaño de Burbujas: Porcentaje

0

10

20

30

40

50

60

70

0 2 4 6 8 10

Ven

tas (

millo

nes)

Nº de Competidores

Mercado Compartido

Page 255: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

255

Otros tipos de gráfico: Gráficos Radiales

Ejercicio: Tomar los datos de la tabla Matriz de Frutas y crear un gráfico

radial como el que se muestra a continuación

Comparación de Producción de Frutas

-20

30

80

130Enero

Febrero

Marzo

Abril

Mayo

Junio

Julio

Agosto

Septiembre

Octubre

Noviembre

Diciembre

Plátano

Fresas

Melocotones

Page 256: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

256

Otros tipos de gráfico: Gráficos Cónicos

Ejercicio: Tomar los datos de la tabla Beneficios de Venta de Vinos y crear

un gráfico cónico como el que se muestra a continuación

1er

trim. 2do

trim. 3er

trim. 4to

trim.

Esperado

Real

-4

-2

0

2

4

6

Beneficio de Venta Vinos

Esperado

Real

Page 257: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

257

Otros tipos de gráfico: Gráficos Superpuestos

Ejercicio: Tomar los datos de la tabla Beneficios de Venta de Vinos y crear

un gráfico de barras con superposición del 60% como el que se muestra a continuación

Beneficios Ventas Vinos

2,02

4,13

-2,55

5,57

3,38 3,58

-3,21

4,81

-4

-2

0

2

4

6

8

1er trim. 2do trim. 3er trim. 4to trim.

Valo

res

Esperado Real

Page 258: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

258

Otros tipos de gráfico: Relleno de áreas

Ejercicio: Tomar los datos de la tabla ¿Has hecho paella alguna vez? y

crear un gráfico de barras usando imágenes para el relleno tal como se muestra a continuación

¿Has hecho paella alguna vez?

60%

45%

40%

55%

0%

10%

20%

30%

40%

50%

60%

70%

Hombres Mujeres

Si

No

Page 259: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

259 259

Agenda

Empezar a trabajar con EXCEL Personalización del Entorno de Trabajo

Edición de Celdas

Fórmulas y Funciones (introducción)

Selección de Celdas Trabajar en Modo Grupo

Referencias a Celdas

Nombrar Celdas

Opciones Avanzadas de Formato y Presentación Alineación de Texto

Formatos Condicionales

Precisión de Pantalla

Uso de Estilos

Paneles

Definición de Esquemas

Impresión y Presentación

Vistas Personalizadas

Protección de Celdas y Hojas

Funciones Funciones de Texto

Funciones Matemáticas

Funciones Lógicas

Funciones (…)

Funciones de Información

Funciones de Búsqueda y Referencia

Funciones de Fecha y Hora

Funciones Estadísticas

Funciones Matriciales

Funciones Financieras

Herramientas para Análisis

Trabajar con Datos Datos Consolidados

Filtros

Filtros Avanzados

Macros

Validación de Datos

Subtotales

Función Buscar Objetivo

Escenarios

Tablas Dinámicas

Trabajar con Gráficos

Extracción de Datos y Libros Compartidos

Page 260: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

260

Información de fuentes externas

En general, casi siempre la información que queremos tratar está ya incluida en Excel, sin embargo es posible traer datos de otras fuentes como pueden ser otras bases de datos o archivos separados por comas

Extraer información de una base de datos, es posible gracias al componente ODBC

Page 261: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

261

Importar datos externos

Ejercicio

Importar la tabla Médicos, de la base de datos Clínica (BD Access) e insertar estos datos en una nueva hoja de cálculo

Page 262: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

262

Importar ficheros de texto

En esta importación la parte mas importante es conocer cual es el formato del archivo de entrada: Separado por comas,

Separado por espacios,

No formateado

En el último caso no será posible extraer la información de otras fuentes

Page 263: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

263

Importar ficheros de texto

Ejercicio

Importar a Excel, los datos del archivo

EMPLEADOS.txt, separado por “;”

Importar a Excel, los datos del archivo DEPARTAMENTOS.txt, el cual tiene delimitado sus campos por un ancho fijo

Page 264: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

264

Libros Compartidos

Es necesario que el libro se guarde como compartido antes de que cualquier otro usuario pueda abrirlo.

Existen riesgos inherentes a un libro compartido. Por eso, cuando alguien almacena cambios Excel no solo lo guarda el libro, también lo actualiza con las modificaciones hechas por otros usuarios

Page 265: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

265

Libros de Trabajo Compartidos

Excel no está pensado para que varios usuarios trabajen simultáneamente sobre un mismo libro o fichero

Por defecto si intentamos abrir un libro que está utilizando otro usuario recibiremos un mensaje de error CANCELAR / SOLO LECTURA / NOTIFICAR

No obstante, EXCEL incluye una funcionalidad que permite “compartir libros” Herramientas Compartir Libro

Mostrará otros usuarios que

estén trabajando con este mismo libro

Page 266: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

266 266

Algunos complementos importantes

Asistente para sumas condicionales Crea fórmulas utilizando las funciones SUMA y SI Podemos utilizarlo como guía para saber cómo

construir nuestras propias fórmulas

Page 267: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

267

Algunos complementos importantes

Ejercicio A partir de la hoja Listado de Libros del libro

Prácticas utilice el asistente de suma condicional para saber cuantos libros de un determinado género y autor hay en el listado

Una vez resuelto el problema anterior, mejore la solución definiendo listados de valores únicos del género y nacionalidad y utilícelos como criterios para la fórmula

Page 268: Microsoft Excel 2003 - kybele.etsii.urjc.es · Microsoft Excel 2003 Nivel Avanzado ... Agenda Empezar a trabajar con EXCEL ... las compara con las fórmulas, son más rápidas, ocupan

268 268

Microsoft Excel 2003

Nivel Avanzado