Microsoft Excel 2003
Nivel Avanzado http://kybele.es/BBVA/excel/ David Granada
[email protected]
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
2
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
Personalización del Entorno de Trabajo Barra de título Barra de menú
Barra de herramientas Barra de fórmulas Barra de formato
Recuperación de archivos
Panel de tareas
Barra de desplazamiento Barra de etiquetas
3
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
4
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 5
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
6
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 J
7
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
8
fijo de decimales
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
9
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
10
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
11
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
12
Manejo de Excel Ejercicio:
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
Abrir el libro
13
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. 14
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
15
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
16
Fórmulas: operadores SÍMBOLO
OPERADOR
PRECEDENCIA
^
Exponenciación
1
*
Multiplicación
2
/
División
2
+ -
Suma Resta
3 3
&
Concatenación
4
=
Igual a
5
Mayor que
5 En cualquier momento podemos romper el orden de precedencia de operadores utilizando paréntesis
17
Uso de Operadores: ejemplos FÓRMULA = Part- & 23A =A1&A2 =6^3 =216^(1/3) =A1$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
52
Formatos Condicionales Para eliminar TODAS las opciones de formato de una
celda
Menú Edición à Borrar à Todo
53
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 …
54
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
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 56
EJEMPLO Estilos
Uso de Estilos
Permiten asignar nombres a combinaciones de atributos de
formato. Cada libro posee seis estilos predefinidos NOMBRE NORMAL
PORCENTUAL
MILLARES
MILLARES [0]
MONEDA
MONEDA[0]
57
DESCRIPCIÓN
EJEMPLO
valores predeterminados de todos los atributos de formato
1234
formato numérico de porcentaje, sin cifras decimales
12%
formato numérico con separadores de millares y 2 cifras decimales ídem redondeado al entero más próximo formato numérico Contabilidad, con símbolos monetarios y separadores de millares ídem redondeado al entero más próximo
5.121,234 5.121 1215,56 € 1216 €
Uso de Estilos
Una buena recomendación es agregar la lista de estilos a la barra de herramientas
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 58
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
59
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
60
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
Paneles Panel horizontal
Panel vertical
62
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
63
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
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
65
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
66
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.
67
Encabezados y Pies de Página En estas secciones se puede incluir información
esencial del documento.
68
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
69
Opciones de Hoja 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: La ficha
Área de
impresión Impresión de títulos Orden de las páginas 70
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
71
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
72
Vistas Personalizadas
Una buena recomendación es agregar la lista de vistas personalizadas a la barra de herramientas
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
73
Opciones de Impresión Ejercicio:
Matriculación Turismos para que ofrezca el siguiente aspecto (en la vista preliminar)
En el libro de trabajo de Prácticas, configurar la hoja denominada
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: …
74
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
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 HerramientasàProtegeràProteger Hoja En este cuadro de diálogo, es posible proteger el documento con una palabra clave, definida por el usuario 76
Protección de celdas y hojas de cálculo 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 77
Protección de celdas y hojas de cálculo Otra forma es a través de las Opciones de
Seguridad.
Menú Herramientas / Opciones/ Seguridad
78
Protección de Datos Ejercicio:
Cambiar las propiedades del Archivo
lectura
Prácticas, a Solo
Abrir el libro en Excel e intentar guardar el archivo. Si no es posible, guardar el archivo como Práctica
protegida
No
Abrir éste último y comprobar que permite modificar
79
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
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
80
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
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
81
Categorías de funciones Ø Funciones de manipulación de
texto: Opciones para modificar el texto contenido en las celdas.
82
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 83
número en un número
83
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
84
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 Si se omite, sustituye todas las ocurrencias
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 &
84
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
85
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
86
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.
87
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 88
romano
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;
decimales
89
num_decimales) à elimina num_decimales
Funciones Matemáticas Ejercicio
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
A partir del libro Prácticas, ir a la hoja
90
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)
91
CONTAR.SI y SUMAR.SI Ejercicio
Prácticas, ir a la hoja Base de Datos. Hallar el número de clientes que viven en Córdoba, Santander, Granada, Mérida A partir del libro
Use las funciones de texto MAYUCS, MINUC, NOMPROPIO
para evitar problemas con la distinción entre minúsculas y mayúsculas J
En el mismo libro, hallar el número de Unidades
vendidas por localidad
92
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
93
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
94
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
95
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.
96
Funciones Lógicas FUNCIÓN
97
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
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 98
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.
99
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
100
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.
101
Funciones Comunes ESBLANCO(celda) à devuelve VERDADERO si la celda
102
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
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 en la que se encuentra la celda. Valor numérico, no el nombre de la columna.
1 cuando la celda tiene valor para los valores negativos 0 en caso contrario.
devuelve el valor de la celda a la que hacemos referencia.
' 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.
"Columna "Color
"Contenido "Prefijo
103
Número
1
Texto
2
Valor Lógico
4
Valor de Error
16
Matriz
64
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.
indica la fila en la que se encuentra la celda.
b si la celda está en blanco r si contiene texto v si contiene otro valor como por ejemplo un valor numérico.
ancho de la columna en la que se encuentra la celda. El valor se redondeará al entero más próximo.
formato de la celda representado por unos valores de tipo texto.
"Fila
"Tipo
"Ancho
"Formato
104
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
105
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
106
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
107
Funciones de Búsqueda y Referencia
EJEMPLOS Buscar
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
108
=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)
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 109
Funciones de Búsqueda y Referencia Ejercicio
Prácticas y, a partir de los datos de la hoja NotaAlumnos, escribir la nota final de los alumnos de acuerdo a:
Ir al libro
La tabla de equivalencias para el Sistema Educativo Español La tabla de equivalencias para el Sistema Educativo
Colombiano
110
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()
111
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 112
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
113
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
114
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.
115
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
116
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
117
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.
118
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)
119
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
120
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
121
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
122
Funciones de Fecha y Hora
EJEMPLO Fechas
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
123
Es recomendable utilizar la función FECHA() cuando queramos introducir una fecha como una constante
Funciones de Fecha Ejercicio:
Crear una nueva hoja con el nombre Funciones de
124
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 2010, 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
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
125
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.
126
Funciones Estadísticas FUNCIÓN
127
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
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
128
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
129
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
130
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
131
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
132
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
133
133
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)
134
=SUMA((A1:D1*{1,2,3,4})) ↔ =SUMA(A1*1,B1*2,C1*3,D1*4)
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}
135
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
136
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
137
Fórmulas Matriciales
EJEMPLO MATRICES
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
138
Fórmulas Matriciales: ejemplos 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 Vaya recorriendo las hojas del libro
139
EJEMPLO MATRICES
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
140
Funciones Matriciales Ejercicio 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 En el libro
141 09/03/12
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
142
Funciones con Matrices Ejercicio
Invertir las siguientes matrices Multiplicar estas matrices
143
1
4
7
2
4
7
2
5
8
2
5
8
3
6
9
3
6
9
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.
144
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
145
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 ó 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
Funciones de Bases de Datos Ejercicio Abrir el libro de trabajo 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
183
Filtros
EJEMPLO 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
184
Filtros Avanzados
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
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)
185
Filtros Avanzados
EJEMPLO 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 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
186
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
187
Filtros Ejercicio
Precio Petroleo Diario del libro Practicas definir los siguientes filtros
A partir de la hoja
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
188
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
Macros Ejecutar una macro
Herramientas à Macro à Macros …
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
Asignar macro Con el botón derecho sobre el botón podemos: Aparece el cuadro de diálogo
Modificar Texto Asignar macro …
Macros Ejercicio
Prácticas Abrir el submenú Macro del menú Herramientas. Crear una nueva hoja de cálculo en el libro 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
192
Macros (II) Ejercicio
Escribir en la celda D1 Cabecera, en la celda E1 de y
193
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
Macros y Filtros Avanzados Ejercicio Abrir el libro
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
194
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
195
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ú
196
Herramientas à Auditoría de Fórmulas
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
197
Validación de Datos Ejercicio:
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
En la hoja
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()
198
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
199
Tipos de Subtotales Tipo de Operación
200
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)
Subtotales Ejercicio
Realizar una copia de la hoja
del libro Practicas
Base de Datos
Generar los subtotales para cada localidad de
acuerdo a las unidades vendidas.
A partir de los datos de la hoja
Tabla dinámica, obtener:
Datos de
Subtotales de Suma para los Totales de acuerdo a
la sucursal Subtotales de Cuenta para los Totales de acuerdo al mes en curso 201
Subtotales
EJEMPLO Libro Pedidos
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
202
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?
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.
Definimos la fórmula dando un valor
203
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.
204
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€
205
Escenarios
EJEMPLO 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 Para que las variables se muestren como un nombre y no con la referencia a la celda correspondiente, debemos nombrar dichas celdas
206
Escenarios Ejercicio
Escenarios del libro Practicas, crear un escenario inicial donde las celdas cambiantes serán:
A partir del modelo de negocio construido en la hoja
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
207
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
208
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?
Lista o base de datos de Microsoft Excel
Marcar
¿ Que tipo de informe
desea crear? Marcar
Tabla dinámica
Clic en Siguiente
209
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)
210
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
Tablas Dinámicas: filtrar Cada campo incluye un desplegable que permite
filtrar los valores mostrados
Ejemplo: mostramos sólo las dos primeras semanas
212
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
213
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 …
DOBLE CLIC
Tablas Dinámicas … DatosPeajes-2 cree una tabla como la que se muestra en la figura
Utilizando los datos de la hoja
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)
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
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
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
El color azul indica que el campo está filtrado
En esta ocasión mostramos sólo los 2 meses en los cuales el peaje pagado por las motocicletas ha reportado mayor recaudación
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
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)
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
222
Tablas Dinámicas Ejercicio
Datos.Ventas del libro TablasDinamicas, cree la siguiente tabla dinámica
A partir de la hoja
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
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)
225
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
226
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ú
227
Tabla Dinámica à Mostrar Página
Tablas Dinámicas Ejercicio:
Datos.Pedidos cree una nueva tabla dinámica como la siguiente
A partir de los datos de la hoja
228
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
229
Tablas Dinámicas Ejercicio:
Datos.Pedidos cree una nueva Tabla Dinámica, esta deberá presentar el siguiente aspecto:
Utilizando de nuevo la hoja
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%
230
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
231
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
232
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
Sucursal (Todas)
Gráfico Dinámico Suma de Total
2500
2000
1500
Mes ene-07 feb-07
1000
500
0 Cacao
233
Café Producto
Te
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
Coloque campos de página aquí Gráfico Dinámico Suma de Total
1000 900 800 700 600 500 400 300 200 100 0
Mes ene-07 feb-07 feb-07 ene-07 Barcelona
Madrid Cacao
234
Valencia
Barcelona
Madrid
Valencia
Café
Producto
Barcelona
Madrid Te
Sucursal
Valencia
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
235
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
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. 236
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
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
237
Asistente para Gráficos: tipo de gráfico 2 1
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
238
Asistente para gráficos: datos de origen No necesariamente todos los datos han de ser contiguos, podemos escoger rangos alternos
3
4
239
EXCEL se refiere a los ejes como Eje X (Datos) - Eje Y (categorías)
Asistente para gráficos: opciones de Gráfico
5
6
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
240
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 241
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
242
Gráficos Ejercicio:
Datos para Gráfico, crear un gráfico de barras con los datos de la matriz de frutas
A partir de los datos de la hoja
VENTA DE FRUTAS 120
Producción
100
87 76
80
50
40 20
59
232521 2
74
32
104 95
86
77
75
60
100
93 68
59 54
50
41 34
34 25
910
23 3
23 3
21 3
3
243
Fresas
re em br D e ici em br e
ub
N
Meses Plátano
ov i
br e
O ct
m
o os t Se
pt ie
lio
Ag
Ju
io Ju n
o M ay
ril Ab
br er o M ar zo
Fe
En
er o
0
Melocotones
4
Gráficos Ejercicio:
Datos para Gráficos, crear un gráfico de columnas con los datos de Producción de Café
A partir de los datos de la hoja
Comparación por Zonas 10 0 90 80
Producción
70 60 50 40 30 20 10 0
4to trim.
3er trim.
2do trim. Trim estre
244
1er trim.
Este Oeste Norte
Gráficos Ejercicio
Datos para Gráficos, crear un gráfico de líneas en 3D con los datos de Producción de Café
A partir de los datos de la hoja
PRODUCCION DE CAFÉ
245
Este Oeste Norte
3er trim.
Trimestre s
1er trim.
100 80 60 Producción 40 20 0 Zona s
Este Oeste Norte
1er trim.
2do trim.
3er trim.
4to trim.
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
Gráficos Precio Promedio Mensual
A partir de los datos de la
100,00
hoja Precio Petróleo Diario crear un gráfico de líneas con los datos del Precio del Petróleo
90,00 80,00 70,00
Precio
60,00 50,00 40,00 30,00 20,00 10,00
Di c ie m
br e
20 04 Ab r Ag i l os Di t c ie o m br e Ab r Ag i l os Di c ie to m br e Ab r Ag i l os Di c ie to m br e Ab ri l
0,00
Precio Promedio Mensual
246
Ejercicio
Gráficos Ejercicio
PNB Países Europeos del libro Prácticas crear un gráfico de circular con efecto 3D como el mostrado en la figura
A partir de los datos de la hoja
Para rotar el gráfico · Verticalmente: Formato à Serie de Datos Seleccionada à Opciones · Horizontalmente: Gráfico à Vista en 3D
PNB Paises Europeos Año 2002
15.176
9.072 14.458
9.191 17.609
5.000
17.640 14.508
18.382
13.554
14.674
247
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
248
Líneas de Tendencia
249
Líneas de Tendencia Ejercicio:
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
A partir de los datos del libro
Precio
Tendencia Petróleo 2008 96,00 94,00 92,00 90,00 88,00 86,00 84,00 82,00 80,00 78,00 76,00
93,34 88,73
80,23
79,52
Serie1
250
Linea de Tendencia
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
251
Gráficos Combinados Ejercicio
A partir del gráfico de la matriz de frutas, crear un gráfico
combinado
VENTA DE FRUTAS 120
50
40
87
50
41
23
54 34
23
34
25 3
Plátano
Melocotones
Fresas
m br e ici em br e D
re
ov ie
ub N
O ct
br e m
o
pt ie
os t Se
Ag
lio Ju
io Ju n
M ay o
Meses
252
3 21
23 3
ril
9
10
59
32
25
Ab
4 7 2
Fe
En
er o
0
21
77
68
59
60
104 95
86
75
br er o M ar zo
Producción
76
80
20
100
93
100
4 3
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.
253
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
Mercado Compartido
Tamaño de
70
Burbujas: Porcentaje
60
Ventas (millones)
50 40 30 20 10 0 0
254
1
2
3
4
5
Nº de Competidores
6
7
8
9
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
Diciembre Noviembre Octubre
Enero 130 80 30
Febrero Marzo Plátano
-20
Abril
Fresas Melocotones
Septiembre
Mayo
Agosto
Junio Julio
255
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 Beneficio de Venta Vinos 6 4 2 0 Esperado
-2
Real
-4 1er trim.
256
Real 2do trim.
3er trim.
Esperado 4to trim.
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 Esperado
Real
8 6
Valores
4 2
4,13 3,58
2,02
0 -2 -4
257
3,38
5,57 4,81
1er trim.
2do trim.
3er trim. -2,55 -3,21
4to trim.
Otros tipos de gráfico: Relleno de áreas Ejercicio:
¿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
Tomar los datos de la tabla
¿Has hecho paella alguna vez? 70% 60%
60% 55%
50% 40%
45% 40% Si No
30% 20% 10% 0% Hombres
258
Mujeres
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
259
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
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
260
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
261
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
262
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
263
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
264
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
265
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
266
Algunos complementos importantes Ejercicio
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 A partir de la hoja
267
Microsoft Excel 2003 Nivel Avanzado
268