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