Download - Apuntes excel 2010 jun2012
2 2
Agenda
Empezar a trabajar con EXCEL Personalización del Entorno de Trabajo
Edición de Celdas
Fórmulas y Funciones (introducción)
Selección de Celdas Trabajar en Modo Grupo
Referencias a Celdas
Nombrar Celdas
Opciones Avanzadas de Formato y Presentación
Alineación de Texto
Formatos Condicionales
Precisión de Pantalla
Uso de Estilos
Paneles
Definición de Esquemas
Impresión y Presentación
Vistas Personalizadas
Protección de Celdas y Hojas
Funciones Funciones de Texto
Funciones Matemáticas
Funciones Lógicas
Funciones (…)
Funciones de Información
Funciones de Búsqueda y Referencia
Funciones de Fecha y Hora
Funciones Estadísticas
Funciones Matriciales
Funciones Financieras
Herramientas para Análisis
Trabajar con Datos Datos Consolidados
Filtros
Filtros Avanzados
Macros
Validación de Datos
Subtotales
Función Buscar Objetivo
Escenarios
Tablas Dinámicas
Trabajar con Gráficos
Extracción de Datos y Libros Compartidos
3 3
Barra de título
Fichas Grupos
Grupo de formato
Barra de fórmulas
Barra de etiquetas
Barra de desplazamiento
Ayuda de Excel
Personalización del Entorno de Trabajo
Barra de herramientas de acceso rápido
4 4
Mostrar y personalizar las barras de Herramientas
Una importante utilidad en Excel (como en el resto de aplicaciones integradas en la suite de Office), es la personalización de barras de herramientas y la cinta de opciones.
Permite tener a mano las herramientas de uso más comunes.
Botón Derecho sobre cualquier barra o sobre la cinta:
Personalizar barra de herramientas de acceso rápido
Personalizar cinta de opciones
Archivo Opciones
5 5
Menú Herramientas
Para modificar las barras o la cinta, es necesario activar la opción de personalización Ficha Archivo Opciones Personalizar barra de
herramientas de acceso rápido
Ficha Archivo Opciones Personalizar cinta de opciones
Botón Derecho sobre cualquier lugar de la cinta Personalizar…
A partir de ahí, podemos: Añadir/Eliminar botones para comandos
Cambiar el aspecto de los botones
Crear/Eliminar/Reorganizar barras/fichas EJEMPLO
barra
6 6
Mostrar y personalizar las barras y cintas
Ejercicio:
Crear un nuevo libro de EXCEL
En la hoja por defecto (Hoja1), crear una nueva ficha, llamada “miFicha”
Dicha ficha deberá tener los siguientes elementos: De la ficha “Archivo”, la opción: Nuevo
De la ficha “Inicio”, grupo Celdas, la opción Insertar
De la ficha “Datos”, grupo Esquema, las opciones Autoesquema y Borrar esquema
De la ficha “Vista”, grupo Ventana, la opción Inmovilizar
7 7
Edición de Celdas
Hay varias formas de editar una celda Si hacemos clic sobre ella y empezamos a escribir estaremos
modificando el contenido de la celda usando la barra de fórmulas
Podemos seleccionar la celda y pulsar F2
Podemos seleccionar la celda y hacer clic sobre la barra de fórmulas
Cambiar comportamiento predeterminado: Ficha Archivo Opciones Avanzadas
Marcar la opción Permitir editar directamente en las celdas
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
8 8
Edición de Celdas
Seleccionar rangos de celdas para editar Cuando EXCEL llega al final de la columna, comienza por la primera
fila de la siguiente columna
Introducir los mismos datos en varias celdas: Seleccionar el rango de celdas
Introducir el valor
Pulsar CTRL + ENTER
Introducir puntos decimales automáticamente Ficha Archivo Opciones Avanzadas
Insertar automáticamente un punto decimal
9 9
Edición de Celdas
AUTORELLENO Si arrastramos con el botón derecho en lugar del
izquierdo se mostrará un pequeño menú de opciones de relleno. Ejemplos: Lunes, 1, Enero, 01/01/2012, etc.
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 verticalmente: una celda en blanco rompe la serie
10 10
Edición de Celdas
Para introducir saltos de línea en una celda… ()
Usar ALT + ENTER
Podemos usar las opciones de auto corrección para
simplificar la entrada de datos
Ficha Archivo Opciones Revisión 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
11 11
Edición de Celdas
Inserción de fracciones Podemos mostrar el valor como una fracción usando las
opciones de formato
Al seleccionar la celda, la barra de fórmulas muestra el valor computado
En general, la barra de fórmulas siempre muestra el valor real almacenado
Las opciones de formato SÓLO se utilizan para presentar el valor en la celda
12 12
Edición de Celdas
Inserción de porcentajes Para evitar problemas, es recomendable utilizar el
símbolo ‘%’ a la hora de introducir un porcentaje en una celda
EXCEL lo reconocerá automáticamente
Insertar, Eliminar y Mover hojas
Cambiar nombre de las hojas Doble clic sobre la etiqueta
Cambiar el color de las etiquetas Clic derecho sobre la etiqueta escogida
13 13
Manejo de Excel
Ejercicio: Abrir el libro Prácticas. Insertar una nueva hoja en blanco
Renombrar la hoja como Ejercicio 1
Introducir un conjunto de números con formato de moneda
Calcular la suma de los números
Guardar el trabajo en disco
14 14
Fórmulas y funciones
Las fórmulas constituyen el núcleo de cualquier hoja de cálculo. De hecho, mediante fórmulas, se llevan a cabo todos los cálculos que se necesitan.
Las funciones permiten hacer más fácil el uso de EXCEL e incrementar la velocidad de cálculo. Si se las compara con las fórmulas, son más rápidas, ocupan menos espacio en la barra de fórmulas y reducen los errores (ya que no es necesario conocer la sintaxis de la fórmula).
Las funciones actúan sobre los datos contenidos en una celda o conjunto de celdas igual que las fórmulas lo hacen sobre los números.
15 15
Fórmulas
Operaciones matemáticas que constan de operadores, expresiones, etc.
Ver “Precedencia de Operadores” Como ya hemos visto, se usará “:” para definir rangos
de celdas
16 16
Fórmulas
Introducción de fórmulas Se puede hacer tanto desde la Barra de Fórmulas 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
17 17
Fórmulas: operadores
SÍMBOLO OPERADOR PRECEDENCIA
^ Exponenciación 1
* Multiplicación 2
/ División 2
+ Suma 3
- Resta 3
& Concatenación 4
= Igual a 5
< Menor que 5
> Mayor que 5 En cualquier momento podemos romper el orden de
precedencia de operadores utilizando paréntesis
18
Uso de Operadores: ejemplos
FÓRMULA TIPO RESULTADO
=”Part-”&”23A” TEXTO “Part-23A”
=A1&A2 Concatena el valor de ambas celdas
=6^3 NÚMERO 6 al cubo 216
=216^(1/3) NÚMERO Raíz Cúbica de 216 6
=A1<A2 LÓGICO VERDADERO / FALSO
=A1<=A2 LÓGICO …
=A1<>A2 LÓGICO …
18
Si tienes problemas para modificar una fórmula, conviértela a texto
(eliminando el signo ‘=‘ al principio), realiza las modificaciones y
vuelve a convertir el texto en fórmula
19 19
Auditoría de Fórmulas
EXCEL proporciona un auditor de fórmulas capaz de localizar errores en las mismas
Ficha Fórmulas Grupo Auditoría de fórmulas Rastrear celdas precedentes Rastrear descendientes Señalar datos incorrectos
EJEMPLO
Auditoría de Fórmulas
20
Errores trabajando con fórmulas
#DIV/0! intento de dividir por 0 La celda que contiene el dividendo está en blanco Reparar:
= SI(C2=0;””;(C2-B2)/C2)
= SI(ESERROR((C2-B2)/C2);””;(C2-B2)/C2)
#N/A cuando alguna función de búsqueda no es capaz de encontrar un resultado
#NOMBRE? la fórmula contiene … … un rango de celdas o un nombre no definido
… un texto que es interpretado como nombre no definido (p.e.: un nombre de función incorrecto)
… una función incluida en un complemento no instalado
21
Errores trabajando con fórmulas
#NULO! la fórmula trata de usar la intersección de 2 rangos que no interseccionan =SUMA(B5:B14 A16:F16) #NULO! =SUMA(B5:B14 A9:F9) B9
#NUM! Pasar un argumento no numérico por error Pasar un argumento incorrecto (=RAIZ(-1))
Una función que funciona realizando iteraciones no es capaz de converger (TASA …)
El resultado es demasiado grande (o pequeño)
22
Errores trabajando con fórmulas
#REF! uso de una referencia inválida Se borra una celda referenciada por la fórmula Se copia la fórmula a una celda que invalida la
referencia relativa (p.e.: copio “=A1-1” de A2 a A1)
Corto y pego una fórmula en una celda que es referenciada en la propia fórmula
#VALOR! Tipo incorrecto de un argumento (p.e.: sumar un número
y una cadena de texto)
Se pasa como argumento un rango cuando debería ser un valor único
Olvidar insertar una fórmula en formato matricial
23 23
Funciones
Las funciones aceptan datos de entrada o argumentos, y devuelven un resultado después de operar con esos datos
Pueden verse como fórmulas predefinidas
En la mayoría de los casos, el resultado es un valor numérico Pero también pueden devolver resultados de tipo texto,
referencias, valores lógicos, matrices o información sobre la hoja de cálculo.
Los argumentos son la información entre paréntesis que pasamos a la función para que realice su tarea
Cada dato es un argumento
Se usará “;” para distinguir los distintos parámetros que se pasan a una función
Ejemplo: SUMA (B5; A3; D3:E6)
Podemos usar nombres de celdas a la hora de escribir fórmulas (ver Nombrar Celdas…) Mientras estamos editando la fórmula
Ficha Fórmula Utilizar en la fórmula
F3 mientras se edita la fórmula
24
Funciones: argumentos
24
ARGUMENTOS
25 25
Botón AUTOSUMA
El botón AUTOSUMA proporciona un acceso rápido a las funciones más comunes Los argumentos de la función son deducidos a partir
de la posición de la celda
26
Funciones
Asistente de Funciones El Asistente de Funciones sirve de guía a
través de todo el proceso de introducción de una función y proporciona una breve explicación, tanto de la función, como de cada uno de sus argumentos. Ficha Fórmula Botón Insertar función
Botón Insertar Función de la barra de fórmulas
Botón AUTOSUMA Más Funciones
26
27 27
Funciones
27
Búsqueda de Función Descripción
Por Categorías
Ayuda contextual
Argumentos de Función Obligatorios/Opcionales
28 28
Funciones
Es recomendable usar la ayuda en línea para conocer cómo utilizar cada función
Introducir el nombre de la función en MIN y
si EXCEL la reconoce, automáticamente la
pasará a MAY
29 29
Selección de Celdas
CTRL + ESPACIO toda la columna MAY + ESPACIO toda la fila CTRL + E todas las celdas de la hoja CTRL + * todas las celdas adyacentes que
contengan valores CTRL + MAY + ↓ todas las celdas adyacentes en
esta columna La columna entera si no hay celdas adyacentes
CTRL + MAY + todas las celdas adyacentes en esta fila
F4 Repite la última acción que se ha realizado en Excel
30 30
Modo Grupo
Podemos verlo como una forma sencilla de trabajar con datos consolidados Seleccionar un conjunto de hojas
Clic sobre la primera y, manteniendo apretada la tecle CRTL, clic sobre el resto
Liberamos la tecla CTRL y …
… al realizar modificaciones sobre cualquiera de ellas, los cambios se replican en el resto
Ejemplos: repetir encabezados en varias hojas, redimensionar filas y/o columnas en varias hojas, etc.
31 31
Modo Grupo
Podemos utilizar esta opción para definir fórmulas que computan las mismas celdas de varias hojas (hojas resumen o acumuladores) Comenzar a introducir la fórmula en la hoja resumen
Seleccionar la celda que contiene el valor que se quiere computar en la primera hoja
Manteniendo MAY pulsado hacer clic sobre la última hoja del intervalo
Pulsar INTRO
La fórmula computa el valor de la misma celda (relativa) en el rango de hojas
Ejercicio
Cree un nuevo libro de EXCEL
Inserte 13 hojas y nombre las 12 primeras como los 12 meses del año
Utilice la edición en modo grupo para: Que todas ellas presenten el siguiente aspecto
Que la hoja 13 calcule los totales de ventas a partir de las 12 restantes
32
33 33
Seleccionar celdas especiales
Podemos buscar celdas que contengan fórmulas o aquellas que dependan de la actual o … Ficha Inicio Buscar y seleccionar Ir a Especial Ctrl + I Especial… Si la lanzamos teniendo seleccionado un rango, la
búsqueda se restringe a las celdas del rango
En caso contrario, se realiza sobre todas las celdas de la hoja
34 34
Referencias a celdas
Las referencias a celdas identifican una celda o grupo de celdas de un libro
Se usan para vincular el resultado de una fórmula a las celdas referenciadas
La referencia de la celda activa se muestra en el cuadro situado a la izquierda de la Barra de referencias
Los rangos de celdas se introducen por medio del operador dos puntos (:), que separa la referencia a la primera celda del rango, de la de la última celda
Ejemplo: (B2:D3)
Ver estilos de referencias F1C1
Archivo – Opciones - Fórmulas
35 35
Referencias a celdas EJEMPLO
Prácticas!Referencias
Referencias Relativas: se refieren a las celdas por sus posiciones en relación con la celda que contiene la fórmula A1
Referencias Absolutas: sirven para evitar que las referencias a celdas cambien cuando se copia la fórmula a una nueva posición $A$1
Referencia mixta: referencia donde tan sólo una de las dos dimensiones, fila o columna, permanece constante A$1, $A1
Las referencias relativas se convierten en absolutas introduciendo el carácter dólar ($) antes de la letra de la columna o el número de fila, que se quieren mantener invariables.
Uso de F4 para convertir
entre tipos de referencias
36 36
Referencias a celdas
Las referencias múltiples consisten en referencias sencillas separadas por el carácter punto y coma (;). Ejemplo: (B2:D3;C5:D6)
Se pueden introducir también referencias a celdas o rangos que sean la intersección de dos o más rangos Especificar cada rango separándolos por un espacio
Ejemplo: (B5:D8 B4:C8).
37 37
Referencias Tridimensionales
Hacen referencia a una celda o un rango de celdas de otra hoja dentro del mismo libro. La sintaxis es: NOMBRE_HOJA!CELDA (una celda)
NOMBRE_HOJA!CELDA1:CELDA2 (un rango) Ejemplo: (Hoja1!B5:C6).
Si referencia una celda o un rango de otro libro. La sintaxis es:
‘RUTA[LIBRO.xls]HOJA’!RANGO RUTA: acceso al fichero en la estructura de directorios
del sistema.
LIBRO: nombre del fichero.
HOJA: nombre de la hoja.
38
Operadores para Referencias (Resumen)
38
OPERADOR FUNCIONALIDAD
: (dos puntos) Especificar un rango
; (coma) Construye un rango uniendo otros dos
(espacio) Intersección entre dos rangos de celdas:
devuelve el rango de celdas comunes a los dos
rangos proporcionados
! (exclamación) Especificar una hoja distinta de la actual
[] (corchetes) Especificar un libro distinto del actual
39 39
Referencias a Celdas
Ejercicio: En el libro Prácticas, insertar una nueva hoja Trabajar
con Referencias En esta hoja, generar una tabla de multiplicar con una
sola fórmula que se copiará a toda la tabla (10 x 10).
Darle el siguiente formato a las celdas a las filas y columnas que contengan los números multiplicados: Tipo de letra: Arial 12 Negrita
Formato de texto: Centrar
40 40
Referencias a celdas: nombrar celdas
Se pueden dar nombres a celdas y de conjuntos de celdas.
Podemos utilizar nombres de celdas para evitar tener que trabajar con referencias absolutas
Para crear nombres, conviene tener en cuenta ciertas reglas: Deben empezar por una letra o por el carácter
subrayado (_) Tras este primer carácter, se puede usar cualquier
conjunto de letras, números y caracteres especiales. No se pueden utilizar espacios en blanco. Como
alternativa, se puede emplear un carácter de subrayado o un punto.
41 41
Nombrar Celdas
Para definir nombres se usa la ficha Fórmulas Asignar nombre Definir nombre… Seleccionar un nombre.
Seleccionar el ámbito (libro u hoja).
Seleccionar la celda, rango o rango múltiple al que desee asignar el nombre.
Hacer clic en Aceptar.
Este mismo grupo permite modificar o eliminar nombres ya definidos
Otra opción es seleccionar la celda o rangos de celdas a las que se desea dar un nombre, y luego hacer clic sobre el cuadro de nombres de la Barra de Referencias. La referencia a la celda activa se sustituye por el nombre tecleado. CTRL + F3
cuadro Administrador de Nombres
42 42
Nombrar Celdas
Todos los nombres de celdas son accesibles desde la Barra de Referencias Por defecto todos los nombres definidos en una hoja
están disponibles para todas las demás (son nombres a nivel de libro)
Si queremos nombres a nivel de hoja, anteponer el nombre de la hoja o definirlo en el ámbito
Hoja1!PruebaNombre Si disminuimos el zoom (CTRL + Rueda Ratón) y tenemos
nombres definidos para rangos de varias celdas, EXCEL mostrará el nombre de los rangos para proporcionar una visión global de la hoja
Otra forma de ver los nombres definidos es usar la opción Ficha Fórmulas Utilizar en la fórmula Pegar Nombres Pegar lista
EJEMPLO
Prácticas!Listado_de_Libros
43 43
Nombrar Celdas
Podemos dar nombre a las celdas de forma automática, usando rótulos de fila, de columna o ambos Seleccionar el rango de celdas, incluyendo los encabezados de fila
y/o columna
Seleccionamos Ficha Fórmula Crear desde la selección Seleccionar si queremos utilizar el encabezado de la fila o la
columna para dar nombre al resto de celdas
Se pueden definir nombres para constantes y/o fórmulas No aparecerán en el cuadro de nombres porque no tienen una
ubicación “tangible”
Ejemplo: Diametro = 2 * Pi * radio
Podemos utilizar los nombres definidos a la hora de crear nuevas fórmulas
44
Usar nombres en fórmulas
Si definimos nombres sobre celdas utilizadas en fórmulas, podemos actualizar las fórmulas para sustituir las referencias por los nuevos nombres Seleccionar el rango sobre el que se desea
aplicar los nombres Ficha Fórmulas Insertar Asignar nombre
Aplicar nombres…
44
45 45
Nombrar Celdas
Ejercicio : A partir del ejercicio de la tabla de multiplicar,
asignar los siguientes nombres a los rangos correspondientes: “Tabla_del _7” en filas
“Tabla_del_9” en columnas
“Tabla_multiplicar”, todos los elementos de la tabla
Con base a estos nombres, realizar las siguientes operaciones: Suma de toda la tabla del 9
Promedio de los elementos de la tabla del 7 y los elementos de la tabla del 9
Contar los elementos que hay en toda la tabla de multiplicar
46 46
Creación semiautomática de nombres
Ejercicio :
A partir del libro Prácticas, ir a la hoja Ejercicio Formato
Seleccione los datos de la tabla y cree nombres para las celdas usando la fila superior y la columna izquierda de la tabla
Usando dichos nombres, obtenga la suma total por meses en la columna G Puede usar F3 mientras edita la fórmula para acceder a todos los
nombres de rangos de celdas definidos
Defina una constante Impuesto con valor 0,16 (recuerde aplicar formato porcentual)
Utilice la fórmula anterior para hacer una copia de la tabla, pero ésta incluyendo precios con IVA
47 47
Agenda
Empezar a trabajar con EXCEL Personalización del Entorno de Trabajo
Edición de Celdas
Fórmulas y Funciones (introducción)
Selección de Celdas Trabajar en Modo Grupo
Referencias a Celdas
Nombrar Celdas
Opciones Avanzadas de Formato y Presentación
Alineación de Texto
Formatos Condicionales
Precisión de Pantalla
Uso de Estilos
Paneles
Definición de Esquemas
Impresión y Presentación
Vistas Personalizadas
Protección de Celdas y Hojas
Funciones Funciones de Texto
Funciones Matemáticas
Funciones Lógicas
Funciones (…)
Funciones de Información
Funciones de Búsqueda y Referencia
Funciones de Fecha y Hora
Funciones Estadísticas
Funciones Matriciales
Funciones Financieras
Herramientas para Análisis
Trabajar con Datos Datos Consolidados
Filtros
Filtros Avanzados
Macros
Validación de Datos
Subtotales
Función Buscar Objetivo
Escenarios
Tablas Dinámicas
Trabajar con Gráficos
Extracción de Datos y Libros Compartidos
48 48
Alineación de Texto
A esta opción se accede a través del cuadro de diálogo Formato de Celdas ó Ficha Inicio Formato Formato de celdas
Esta ficha incluye varias opciones Horizontal
Vertical
Orientación
Control de Texto
Dirección del Texto
49 49
Alineación de Texto
TEXTO INCLINADO (Encabezamiento de Tablas)
1. Seleccione las celdas que desee formatear y elija el comando Celdas del menú Formato
2. En la ficha Bordes, aplique bordes verticales a la izquierda, derecha y centro del rango de celdas
3. En la ficha Alineación, use los controles Orientación para seleccionar el ángulo deseado (60º)
4. En la lista desplegable Horizontal de la sección alineación de texto, seleccione la opción centrar. Excel gira los bordes izquierdo y derecho con el texto
5. Arrastre el borde inferior de la cabecera de la fila 1 hacia abajo para dejar espacio suficiente para encajar los rótulos sin recurrir al ajuste
6. Seleccione todas las columnas. Seleccione Formato, Columna, Autoajustar a la selección para reducir todas las columnas al menor ancho posible
Aunque los rótulos inclinados parezcan
borrosos, este efecto desaparece cuando
imprimimos la hoja de cálculo
50 50
Alineación de Texto
BORDES INCLINADOS (Encabezamiento de Tablas)
1. Seleccione la celda que desea formatear e introduzca unos diez espacios en blanco. Podrá corregirlo posteriormente.
2. Introduzca el rotulo que desea y que corresponderá a la columna
3. Mantenga pulsada la tecla ALT mientras pulsa ENTER dos veces para crear dos saltos de línea en la celda
4. Introduzca el segundo rótulo, que corresponderá a las filas y pulse INTRO
5. Seleccione la celda, elija Formato, Celdas y haga clic en la ficha Bordes
6. Seleccione un estilo de línea y haga clic en el botón de borde inclinado de arriba izquierda abajo derecha.
7. Haga clic en la fecha Alineación, active la opción Ajustar Texto y haga clic en Aceptar
51 51
Alineación de Texto
Ejercicio : A partir del libro Prácticas ir a la hoja Ejercicio
Formato Dar formato de Texto Inclinado a la fila de
Títulos (Ahorros, Corriente …)
Escribir en la celda A5, los títulos Mes y Productos
Usar la opción de bordes inclinados para separar los dos títulos de la celda A5
52 52
Formatos Condicionales
Son formatos “dormidos” que aparecen cuando una celda o un grupo de celdas alcanzan un estado determinado.
Podemos tener dos tipos de formato Por valor de celda
Por fórmula: podemos utilizar cualquier fórmula cuyo resultado sea verdadero o falso Usamos referencias relativas que apuntan a la
primera celda del rango (celda superior-izquierda)
Ejemplo: =A2>$C$1
Seleccionar el conjunto de celdas Ficha Inicio Formato Condicional
Si varias condiciones son ciertas para una celda, sólo se aplica el formato asociado con la primera condición
53
Formatos Condicionales
Para eliminar TODAS las opciones de formato de una celda Ficha Inicio Grupo Modificar Borrar Borrar todo
Es posible utilizar conjuntos de iconos condicionales, barras de datos o escalas de color
54 54
Formatos Condicionales
Ejercicio:
Insertar una copia de la hoja Ejercicio Formato en el libro Prácticas
Definir los siguientes formatos condicionales: En fondo rojo las ventas inferiores a 1000€. En fondo rosa todas las celdas que caen por debajo
de la media de todos los valores de la tabla.
Finalmente, aplicar fondo gris a las celdas alternas con los nombres de meses.
Es decir, la primera gris, la segunda blanca, y así sucesivamente (usar las funciones RESIDUO y FILA) Utilice al ayuda de estas funciones si necesita saber cómo
funcionan …
Precisión de Pantalla
Opción para decirle a Excel que queremos que nos trate los datos tal y como los vemos en pantalla (así como el redondeo)
Ficha Archivo Opciones Avanzadas Establecer precisión de pantalla
Dos detalles muy importantes: El cambio es
irreversible Es aplicado a todo
el libro
55
56 56
Precisión de Pantalla
Ejercicio:
Crear un libro nuevo.
Insertar los números: 81, 27, 9, 3 en diferentes celdas En una celda distinta realizar la división de 27 entre 81
En otra celda la división de 9 entre 27
En otra celda la división de 3 entre 9
Realizar la suma de estas divisiones
Posteriormente formatear las celdas de la división para que solo se vea 1 cifra decimal
Activar la opción de Precisión de Pantalla
Volver a realizar la suma
Comparar los resultados
57 57
Uso de Estilos
Permiten asignar nombres a combinaciones de atributos de formato.
Cada libro posee diferentes estilos predefinidos
NOMBRE EJEMPLO
NORMAL 1234
PORCENTUAL 12%
MILLARES 5.121,234
MILLARES [0] 5.121
MONEDA 1215,56 €
MONEDA[0] 1216 €
EJEMPLO
Estilos
58 58
Uso de Estilos
Se pueden crear nuevos estilos o modificar los existentes
Opciones de estilos: Ficha Inicio Grupo Estilos
Podemos Definir un nuevo estilo utilizando celdas formateadas
previamente Ficha Inicio Grupo Estilos Nuevo estilo de celda
Especificar nuevo nombre Aceptar
Definir un nuevo estilo desde cero Aplicar Formato
Aplicar un estilo
Modificar un estilo los cambios se reflejan en todas las celdas sobre las que se aplicó dicho estilo !!!
Eliminar un estilo Clic dx sobre el estilo
Eliminar
Una buena recomendación es agregar la lista de estilos a la barra de herramientas
59 59
Uso de Estilos
Podemos reutilizar los estilos que definimos en otro libro Abrir los dos libros: el actual y aquel en que definimos
el conjunto de estilos
Ficha Inicio Grupo Estilos Combinar estilos…
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
60 60
Uso de Estilos
Ejercicio: Abrir el libro Prácticas e ir a la hoja Precio Petróleo
Diario
Crear un nuevo estilo con el nombre “Mi Estilo”, con las siguientes opciones: Tipo de fuente: Comic Sans
Trama: Azul
Tamaño: 12
Aplicar este estilo al grupo de títulos de la hoja
61
Paneles
Los paneles permiten ver de manera simultanea varias partes de la hoja de cálculo Panel horizontal o vertical: mover los rectángulos que
están junto a las barras de scroll (Dividir Ventana)
Ambos paneles: situarse en la fila o columna posterior a las que se desean inmovilizar y seleccionar la ficha Vista Inmovilizar
61
62 62
Paneles
Panel horizontal
Panel vertical
63 63
Paneles
Ejercicio: En el libro Prácticas ir a la hoja Precio Petroleo
Diario Crear un panel que permita visualizar siempre los
meses, de forma horizontal, y los días en forma vertical
64
Definición de Esquemas
El uso de esquemas permite añadir o contraer la apariencia de una hoja de cálculo, de forma que la información se pueda ver con más o menos detalle.
EXCEL puede crear un esquema de modo automático. Para ello busca celdas con fórmulas que sean un resumen de las filas (por encima de ellas) o bien de las columnas (a su izquierda).
Para construir esquema/s utilizar: Ficha Datos Agrupar Autoesquema.
64
EJEMPLO
Esquemas
65 65
Definición de Esquemas
Ejercicio: A partir del libro Prácticas ir a la hoja de
cálculo Esquema Crear subtotales por Trimestre y también un
total Semestral, para cada vendedor. Crear un esquema para esta hoja
Hallar el promedio de grupo para cada mes Hallar el promedio total (ambos grupos) de
forma mensual Volver a crear
un esquema con estos datos
66 66
Configurar Página
Muestra la ficha Página en el cuadro de diálogo Configurar página.
Estas opciones se manejan a través de la ficha Diseño de página Grupo Configurar página
67 67
Configuración de Hoja
Permite personalizar la forma en la que se va a visualizar una hoja de cálculo cuando se imprime.
68 68
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.
69 69
Encabezados y Pies de Página
En estas secciones se puede incluir información esencial del documento.
70 70
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
71 71
Opciones de Hoja
La ficha Hoja del cuadro Configurar página, controla parámetros específicos de la hoja activa.
Es posible especificar opciones de hoja distintas para cada hoja de un libro de cálculo
Algunas de estas opciones son: Área de
impresión Impresión de
títulos
Orden de las páginas
72 72
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
73 73
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
74 74
Vistas Personalizadas
Podemos almacenar las opciones de presentación de la hoja para recuperarlas en cualquier momento Ficha Vista Grupo Vista de diseño Vistas
Personalizadas
Antes de comenzar, defina la vista por defecto con un nombre (p.e: Vista Normal)
Realice las modificaciones de presentación necesarias y asócielas con una nueva vista y un nuevo nombre
Una buena recomendación es agregar la lista de vistas personalizadas a la barra de herramientas
75 75
Opciones de Impresión
Ejercicio:
En el libro de trabajo de Prácticas, configurar la hoja denominada Matriculación Turismos para que ofrezca el siguiente aspecto (en la vista preliminar)
En cada página se repite como encabezado de fila la lista de
fabricantes y los nombres de provincia.
Deberá configurar también los saltos de página
· Página 1: Álava – LLeida (AUDI – HONDA)
· Página 2: Álava – Lleida (HYUNDAI – OPEL)
· Página 3: Álava – Lleida (PEUGEOT – OTROS)
· Página 4: La Rioja – Final (AUDI – HONDA)
· Página 5: …
76
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 Sólo 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.
76
77
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
77
78
Protección de celdas y hojas de cálculo
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 Ficha Archivo Información
Proteger libro En este cuadro de diálogo,
es posible proteger el documento con una palabra clave, definida por el usuario
78
79
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 de Celdas
Las celdas de una hoja de cálculo pueden tener dos tipos de protección: La que no permite modificar el
contenido de la celda pero muestra la fórmula que contiene
La sólo muestra el valor final de la fórmula
Protección de celdas y hojas de cálculo
80
Protección de Datos
Ejercicio: Cambiar las propiedades del Archivo Prácticas, a Solo
lectura Abrir el libro en Excel e intentar guardar el archivo.
Si no es posible, guardar el archivo como Práctica No protegida
Abrir éste último y comprobar que permite modificar el valor de las celdas (porque NO es el archivo de sólo lectura, al crear la copia estamos creando un nuevo libro desprotegido)
Activar la protección usando la ficha Archivo Información Proteger libro Proteger hoja actual
Cerrar y abrir de nuevo el libro y comprobar si se permite modificar las celdas
81 81
Agenda
Empezar a trabajar con EXCEL Personalización del Entorno de Trabajo
Edición de Celdas
Fórmulas y Funciones (introducción)
Selección de Celdas Trabajar en Modo Grupo
Referencias a Celdas
Nombrar Celdas
Opciones Avanzadas de Formato y Presentación
Alineación de Texto
Formatos Condicionales
Precisión de Pantalla
Uso de Estilos
Paneles
Definición de Esquemas
Impresión y Presentación
Vistas Personalizadas
Protección de Celdas y Hojas
Funciones Funciones de Texto
Funciones Matemáticas
Funciones Lógicas
Funciones (…)
Funciones de Información
Funciones de Búsqueda y Referencia
Funciones de Fecha y Hora
Funciones Estadísticas
Funciones Matriciales
Funciones Financieras
Herramientas para Análisis
Trabajar con Datos Datos Consolidados
Filtros
Filtros Avanzados
Macros
Validación de Datos
Subtotales
Función Buscar Objetivo
Escenarios
Tablas Dinámicas
Trabajar con Gráficos
Extracción de Datos y Libros Compartidos
82 82
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
Funciones Financieras
83
Categorías de funciones
Funciones de manipulación de texto:
Opciones para modificar el texto contenido en las celdas.
84 84
Funciones de Texto
CODIGO(texto) Devuelve el código ASCII del primer carácter del texto
MONEDA(num; num_decimales) cadena de texto
MONEDA(45,899;2) 45,90 €
LARGO(valor) número de caracteres del valor resultante
ESPACIOS elimina espacios al principio, al final de la celda y entre medias, dejando sólo un espacio entre palabras
LIMPIAR elimina caracteres de control
IGUAL(cadena1;cadena2) compara las dos cadenas y devuelve verdadero o falso. Reconoce MAY/MIN pero ignora formatos
MAYUSC, MINUSC, NOMPROPIO
NOMPROPIO(“juan josé”) Juan José
T(valor) Devuelve el texto al que se refiere el argumento valor
VALOR(texto) Convierte una cadena de texto que representa un número en un número
EJEMPLOS
85 85
Funciones de Texto
HALLAR(texto_buscado;dentro_del_texto;núm_inicial)
No distingue entre MAY y MIN
Admite caracteres comodín (*, ?, etc)
ENCONTRAR(texto_buscado;dentro_del_texto;núm_inicial)
Distingue MAY/MIN y no admite comodines
DERECHA(texto;núm_de_caracteres) los num caracteres empezando por la derecha
IZQUIERDA(texto;núm_de_caracteres) los num caracteres empezando por la izquierda
EXTRAE(texto;posición_inicial;núm_de_caracteres)
SUSTITUIR(texto;texto_original;texto_nuevo; núm_de_ocurrencia)
REEMPLAZAR(texto_original;núm_inicial;núm_caracteres;texto_nuevo)
CONCATENAR (texto1;texto2; ...) ⇆ Uso de ‘&’
Si se omite, sustituye todas las ocurrencias
EJEMPLOS
86
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”
87 87
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
Funciones Financieras
88
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.
89
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 número en formato romano
EJEMPLOS
90
Funciones Matemáticas
REDONDEO: Excel ofrece varias funciones para esta tarea
REDONDEAR(num;num_decimales) Redondea un número 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 más próximo por arriba
REDONDEA.IMPAR: Redondea al impar más próximo por arriba
REDOND.MULT(num, mult): Redondea (hacia abajo) un número hasta el múltiplo del número especificado
ENTERO(num) entero inferior más próximo
TRUNCAR (num; num_decimales) elimina num_decimales decimales
91
Funciones Matemáticas
Ejercicio A partir del libro Prácticas, ir a la hoja Ejercicio de Formato En la columna H, haremos un redondeo par de las cifras de
Ahorro
En la columna I, haremos un redondeo siempre hacia arriba de los valores de corriente
En la columna J haremos un redondeo a -2 cifras decimales de los valores de Tarjeta de Crédito
En la columna K, haremos un redondeo a múltiplos de 100 de las cifras de Hipoteca
92
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)
EJEMPLOS
93
CONTAR.SI y SUMAR.SI
Ejercicio A partir del libro Prácticas, ir a la hoja Base de Datos. Hallar el número de clientes que viven en Córdoba,
Santander, Granada, Mérida Use las funciones de texto MAYUCS, MINUC, NOMPROPIO
para evitar problemas con la distinción entre minúsculas y mayúsculas
En el mismo libro, hallar el número de Unidades vendidas por localidad
94
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
95
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
96 96
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
Funciones Financieras
97
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.
98
Funciones Lógicas
FUNCIÓN DESCRIPCIÓN
SI(prueba_logica; valor_si_verdadero; valor_si_falso)
Devuelve un valor u otro, según se cumpla o no la condición o prueba lógica
FALSO() Devuelve el valor lógico Falso
VERDADERO() Devuelve el valor lógico Verdadero
NO(valor_lógico) Invierte el valor lógico proporcionado
Y(valor_logico1;valor_logico2;...) Devuelve VERDADERO si todos los valores son verdaderos
O(valor_logico1;valor_logico2;...) Devuelve VERDADERO si alguno de los valores es verdadero
99
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
100
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.
101
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
Funciones Financieras
102
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.
103
Funciones Comunes
ESBLANCO(celda) devuelve VERDADERO si la celda referenciada está en blanco
ESERR(celda) devuelve VERDADERO si el valor es cualquier valor de error excepto #N/A
ESERROR(celda) devolverá VERDADERO si la celda a la que estamos haciendo referencia es un error y FALSO en caso que no lo sea
ESLOGICO(celda) Devuelve VERDADERO si el valor es un valor lógico
ESNOD(celda) Devuelve VERDADERO si el valor es el valor de error #N/A (valor no disponible)
ESNOTEXTO Devuelve el valor VERDADERO si el valor no es de tipo texto
ES.PAR | ES.IMPAR (celda) Devuelve el valor VERDADERO si el número es par|impar
EJEMPLOS
104
Funciones Comunes
TIPO(valor) Devuelve un número que indica el tipo de datos de un valor
CELDA(Tipo de información; Celda) Devuelve información acerca del formato, la ubicación o el contenido de una celda "Direccion”
referencia de la celda en formato texto, $A$1.
"Columna“ columna en la que se encuentra la celda. Valor numérico, no el nombre de la columna.
"Color” 1 cuando la celda tiene color para los valores negativos 0 en caso contrario.
"Contenido” devuelve el valor de la celda a la que hacemos referencia.
"Prefijo” ' si la celda contiene texto alineado a la izquierda " si la celda está alineada a la derecha ^ si el contenido de la celda está centrada \ si la celda tiene texto con alineación de relleno por último nos devolverá texto vacío si tiene otro valor.
Número 1
Texto 2
Valor Lógico 4
Valor de Error 16
Matriz 64
105
Funciones Comunes
CELDA(Tipo de información; Celda) Devuelve información acerca del formato, la ubicación o el contenido de una celda
"Proteger” si la celda está protegida devolverá un 1 si no lo está un 0.
"Fila” indica la fila en la que se encuentra la celda.
"Tipo” b si la celda está en blanco r si contiene texto v si contiene otro valor como por ejemplo un valor numérico.
"Ancho” ancho de la columna en la que se encuentra la celda. El valor se redondeará al entero más próximo.
"Formato” formato de la celda representado por unos valores de tipo texto.
106
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
Funciones Financieras
107
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
108
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
109
Funciones de Búsqueda y Referencia
BUSCARH(valor_buscado;matriz_buscar_en; indicador_filas; ordenado) Busca el valor en la primera fila del rango especificado
y devuelve el valor que hay en esa misma columna en la fila especificada (relativa)
ORDENADO
VERDADERO u omitido se realiza una búsqueda aproximada: si no se encuentra el valor, se devuelve el máximo valor menor que el buscado (la primera fila debe estar ordenada)
FALSO coincidencia exacta: si no se encuentra, devuelve #N/A
=BUSCARH("Ejes";A1:C4;2;VERDADERO) Busca “Ejes” en la fila 1 y devuelve el valor de la fila 2 que está en la misma columna (4)
EJEMPLOS
Buscar
110
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
EJEMPLOS
Buscar
111
Funciones de Búsqueda y Referencia
Ejercicio Ir al libro Prácticas y, a partir de los datos de la hoja
NotaAlumnos, escribir la nota final de los alumnos de acuerdo a: La tabla de equivalencias para el Sistema Educativo Español
La tabla de equivalencias para el Sistema Educativo Colombiano
112
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 suplementos Utilice para ello la función BUSCARH()
113
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
EJEMPLOS
Buscar
114
Funciones de Búsqueda y Referencia
Ejercicio
En la hoja Base de Datos del libro Practicas defina una nueva fórmula que permita saber cuántas unidades se han vendido a un cliente concreto Utilice la función BUSCAR
115
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
116
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.
117
Funciones de Búsqueda y Referencia
Ejercicio
En la hoja Base de Datos del libro Practicas defina una nueva fórmula que permita saber cuántas unidades se han vendido a un cliente concreto En esta ocasión utilice las funciones COINCIDIR e
INDICE
118
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
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
119
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 A1, es decir en la celda D3.
120
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 relativa a (B1) =DIRECCION(1;2;4;falso) devuelve una referencia relativa a
(F(1)C(2))
121
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
122
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
Funciones Financieras
123
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
124
HOY() fecha actual (dd/mm/aaaa)
AHORA() hora actual
FECHA(año,mes,día) construye un valor de tipo fecha
DIASEM(fecha; tipo) p.e: DIASEM(FECHA(18;06;2008))
AÑO | MES | DIA (fecha)
HORA | MINUTO (hora)
FECHA.MES(fecha; meses) fecha exacta de hoy en X meses
FIN.MES(fecha; meses) fecha exacta del final de mes en X meses
DIA.LAB(fecha_inicial; dias_lab) fecha de hoy en X días laborables
DIAS.LAB(fecha_ini;fecha_fin; festivos) días laborables entre dos fechas, sin contar los festivos especificados
SIFECHA(fecha_ini; fecha_fin; tipo) La diferencia que hay entre dos fechas. El tipo puede ser: Y, M, D, YM, MD
Funciones de Fecha y Hora EJEMPLO
Fechas
Es recomendable utilizar la función
FECHA() cuando queramos introducir
una fecha como una constante
125
Funciones de Fecha
Ejercicio: Crear una nueva hoja con el nombre “Funciones de
Fecha”
Calcular los años, meses y días totales que ha vivido una persona. Además, dar la edad exacta especificando años, meses y días vividos.
Calcular el número de días laborables del año 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
126
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
Funciones Financieras
127
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.
128
Funciones Estadísticas
FUNCIÓN DESCRIPCIÓN
MEDIA.ARMO(número1;número2;...)
Media armónica de un conjunto de números positivos
MAX(número1;número2;...) Valor máximo de la lista de valores
MEDIANA(número1;número2;...) Mediana de la lista de valores
PROMEDIO(número1;número2;...) Media aritmética de la lista de valores
VAR(número1;número2;...) Varianza de una lista de valores
MODA(rango) Valor que más se repite
K.ESIMO.MAYOR(matriz;k) Valor k-ésimo mayor de matriz
•La ecuación para la media armónica es:
129
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
130
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 19 el promedio de los productos usando la función Promedio
Calcular en la fila 21 el promedio de los productos usando la función PromedioA
Comparar los resultados
131
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
EJEMPLO
132
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 (18,30,65)
Utilice la función FRECUENCIA para saber cuantas edades caen en cada uno de los intervalos que acaba de definir
133 133
Funciones Comunes
Funciones Matemáticas
Funciones de Texto
Funciones Lógicas
Funciones de Información
Funciones de Búsqueda y Referencia
Funciones de Fecha y Hora
Funciones Estadísticas
Funciones Matriciales
Funciones Financieras
134
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
135
Funciones con Matrices
Se reconocen porque van encerradas entre llaves
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(1*5, 4*2, 2*9)
=SUMA((A1:D1*{1,2,3,4})) ↔ =SUMA(A1*1,B1*2,C1*3,D1*4)
136
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}
137
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”
138
Fórmulas Matriciales
Seleccionar una matriz Seleccionar el rango manualmente Estando sobre una de las celdas del rango
Ficha Inicia Buscar y seleccionar Ir a e special 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
139
Fórmulas Matriciales
Crear fórmulas a partir de un rango =A1:A3 + (CTRL + MAY + ENTER) Las matrices quedan vinculadas, cualquier cambio en una
celda de la original se refleja en la nueva
Si queremos desvincularlas: editar la fórmula, convertir las referencias a valores (F9) y volver a forma matricial
Fórmulas que devuelven un valor =SUMA(LARGO(DiasSemana))+(CTRL + MAY + ENTER)
Las fórmulas matriciales también sirven para eliminar cálculos intermedios
F9
Convertir celdas a valores
Usado con frecuencia cuando trabajamos con
una hoja ajena
EJEMPLO
MATRICES
140
Fórmulas Matriciales: ejemplos…
Vaya recorriendo las hojas del libro Matrices donde se muestran varios ejemplos del uso de fórmulas matriciales y trate de entender su funcionamiento
Realice modificaciones en las fórmulas y observe su efecto sobre los resultados
EJEMPLO
MATRICES
141
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
142 22/05/2012
Funciones Matriciales
Ejercicio En el libro Prácticas, realizar al final una copia de la
hoja de Esquema Transponer la tabla de Esquemas
143
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
144
Funciones con Matrices
Ejercicio Invertir las siguientes matrices
Multiplicar estas matrices
1 4 7
2 5 8
3 6 9
2 4 7
2 5 8
3 6 9
145 145
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
Funciones Financieras
146
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.
147
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 2500€ 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
148
Funciones Financieras
Los argumentos más comunes de las funciones financieras incluyen: Valor actual (va): la cantidad principal. Es el valor de una inversión o préstamo,
el desembolso inicial … (>0 ó <0)
Valor futuro (vf): Valor de la inversión o préstamos una vez realizados todos los pagos.
es el valor actual más los intereses.
Valor futuro que desea lograr una vez realizados todos los pagos, i.e. al final de la operación (0 por defecto)
Si invierto 5.000 durante 5 años al 6% anual, al final obtengo 6.312 (va)
Si compro un coche por 15.000, a pagar en 3 años al 7% anual, pagaré finalmente 16.673 (va)
Pago: el importe pagado periódicamente en una inversión o préstamo, cuando este importe es constante (cantidad negativa)
Pago 1, Pago 2, … Pago n: pagos periódicos cuando difieren en la cantidad
Número de períodos (nper): el número total de pagos o períodos de una inversión.
Tipo: cuando se hace al pago (0 = al final del período, 1 = al principio)
Tasa: tasa de descuento o interés por periodo (convertir a meses)
El valor actual de un préstamo es el importe del
préstamo, mientras que el valor futuro es 0 (es lo que
nos quedará al finalizar)
149
Funciones Financieras
FUNCIÓN PAGO: cuota periódica (intereses más amortización) que se necesita para amortizar un préstamo en un número dado de períodos.
PAGO Tasa interés; Nper número de períodos o pagos; Va valor actual del préstamo Vf es el valor futuro o un saldo en efectivo que se desea lograr tras
el último pago (si es una operación de ahorro, sería lo que esperamos tener al final de los periodos de pago). Si vf se omite, se asume que vale 0, es decir, tras el último pago no queda
ningún saldo pendiente, lo que ocurre cuando se trata de un préstamo.
Tipo indica el vencimiento de pagos anticipado – al principio del periodo(1) vencido – al final del periodo(0)
150
Pago de un Préstamo
Ejercicio Crear un libro nuevo que llamaremos Funciones
Financieras y resolver el siguiente ejercicio:
Se desea calcular el valor de la cuota mensual para un préstamo de 180.000€ con un Euríbor del 4,75%, a un plazo de 20 años
Usar las mismas unidades para todos los valores:
Convertir las tasas anuales a tasas mensuales
Expresar num_periodos en meses
151
Pago de un Préstamo
Ejercicio: A partir del ejercicio anterior, realizar una tabla para
observar cual sería la cuota a pagar: Importes: 100.000, 176.00 a 189.000€
Periodos: de 10 a 25 años
Definir un formato condicional para que resalten las cuotas que estén entre 1200 y 1250 euros mensuales
152
Ahorrando
Ejercicio Se desea ahorrar 350.000€ en 18 años, ahorrando una
cantidad constante cada mes.
¿Qué cantidad se debe ahorrar, si se asume que se puede devengar un 2,5% de interés anual en la cuenta de ahorros?
153
Funciones Financieras
PAGOINT Calcula cuanto pagamos de interés en un período
específico siempre que trabajamos con cuotas periódicas constantes.
PAGOINT(tasa;periodo;nper;va;vf;tipo)
PAGOPRIN Calcula la parte correspondiente a la amortización de
capital de un pago perteneciente a un préstamo con cuotas periódicas constantes y a interés constante
PAGOPRIN(tasa;periodo;nper;va;vf;tipo)
154
Funciones Financieras
Ejercicio Calcular la cantidad que corresponde al pago
de intereses y a la amortización de capital para un préstamo de cuota fija a 35 años sobre 250.000€, con un interés del 4,9%
A partir del apartado anterior, realizar la tabla de pago donde se detalle, período a período, cuanto se deberá pagar por intereses y cuanto por amortización de capital
155
Funciones Financieras
PAGO.INT.ENTRE : Similar al PAGO.INT, pero permite especificar un intervalo de tiempo (especificando dos periodos) en lugar de uno solo de los periodos
PAGO.PRINC.ENTRE: Similar al PAGO.PRINC, pero permite establecer dicho monto entre dos períodos determinados
156
Funciones Financieras
Ejercicio Con base al ejercicio anterior, calcular cuanto se
pagará de intereses y de principal o amortización durante el 10 año de pago de la deuda.
(Nota: datos de tipo obligatorio)
157
Funciones Financieras
FUNCIÓN VA (Valor Actual) devuelve el valor actual de una inversión valor a día de hoy de la suma de una serie de
pagos que se efectúan en el futuro Por ejemplo, cuando pide dinero prestado, la
cantidad del préstamo es el valor actual para el prestamista
VA(tasa;num_per;pago;val_futuro;tipo)
Si además de los pagos periódicos hay un pago/aportación final usaremos el argumento valor_futuro
Suele utilizarse para evaluar la rentabilidad de una
inversión: si el valor actual es superior al coste de la
inversión, es una buena inversión
EJEMPLO
VA
158
Funciones Financieras
Ejercicios Calcular el valor actual de un préstamo con una cuota
de 750€ mensuales al 4% de interés anual y con un plazo de 10 años
Se desea comprar una póliza de seguros que pague 500€ al final de cada mes durante los próximos 20 años. Para recibir dicha cantidad ha de invertir 60.000€ y el dinero pagado devengará un interés del 8% anual. Determinar si la compra de la póliza es una buena inversión.
159
Funciones Financieras
FUNCION VF (Valor Futuro): Determina el valor en una fecha futura de una
inversión basándose en pagos periódicos constantes y en una tasa de interés constante.
Podemos verla como el contrario de VA
VF(tasa;nper;pago;va;tipo)
160
Funciones Financieras
Ejercicio Se contrata un plan de pensiones depositando
2000€ al principio de cada año. La tasa media de los intereses será el 9%. Si ahora tenemos 30 años, ¿Cuánto dinero habremos acumulado a los 65?
Ahora suponga que, además, comenzó el plan aportando 7500€ de los que disponía y repita el cálculo
161
Funciones Financieras
FUNCION TASA: la tasa de retorno de una inversión que genera una
serie de pagos periódicos o un único pago total
Determinar el interés de una inversión o préstamo
TASA(num_per; pago; valor_actual; valor_futuro; tipo; estimación)
Usaremos pago si queremos calcular la tasa en una serie de pagos periódicos iguales
Usaremos valor futuro si queremos calcular la tasa para un pago único
La estimación le sirve a EXCEL como indicio para el cálculo de la tasa
162
Funciones Financieras
Ejercicios: Cuál es la tasa de un préstamo de 8.000€ a
cuatro años con pagos mensuales de 200€ Suponga que está considerando una inversión
que producirá 1000 € anuales en los próximos 5 años. La inversión es de 3000 €. ¿Cuál es la tasa de retorno?
163
Funciones Financieras
FUNCION NPER: Nº periodos necesarios para amortizar un préstamo,
fijada una cuota periódica y un interés constante
NPER(tasa;pago;valor_futuro;tipo)
Si el argumento pago es demasiado pequeño para
amortizar la cantidad, la función devuelve un valor de error. Por tanto, el pago mensual debe ser al menos igual al interés del periodo multiplicado por el capital. En caso contrario nunca se amortizará el préstamo
164
Pago de un Préstamo
Ejercicio:
¿Cuantos años deberé pagar mi hipoteca si tengo estimado solicitar 100.000€, la tasa de interés es constante en 4,75% y tengo estimado pagar 1000€ mensuales?
165
Funciones Financieras
TIR - Tasa Interna de Retorno: Devuelve la tasa de interés para la que el valor actual
de los pagos es exactamente igual al coste de la inversión
Equivale a la tasa de interés producida por un proyecto de inversión con pagos (valores negativos) e ingresos (valores positivos) que ocurren en períodos regulares.
Si es mayor que la tasa de mercado es una buena inversión TIR(RANGO de VALORES) debe haber un valor negativo, que
es el que identifica el importe de la inversión, el resto son las cuotas o beneficios que se esperan obtener
166
Funciones Financieras
Ejercicio: Ha decidido abrir un restaurante con un costo inicial
estimado de 120.000€. A lo largo de los próximos cinco años espera recibir una renta neta de 25.000€, 27.000€, 35.000€, 38.000€ y 40.000€ respectivamente.
Crear una hoja de cálculo con los datos de la inversión y el porcentaje de beneficios de este negocio
167
Funciones Financieras
Parámetros para el cálculo de Depreciaciones Coste: coste inicial del bien Vida: periodo de tiempo a lo largo del cual se deprecia
el bien (número de periodos)
Período: periodo individual sobre el que se desea realizar los cálculos
Valor Residual: valor que aún posee el bien después de la depreciación
168
Funciones Financieras
Cálculo de Depreciaciones SLN: Calcula la depreciación de un bien por el método
directo (uniforme) durante un único periodo de tiempo
SLN(costo;valor_residual;vida)
Ejemplo:
Calcular la depreciación anual, por el método directo, de un coche que costó 8000€ nuevo y que tiene una vida útil de 10 años, al cabo de los cuales vale 500€
169
Funciones Financieras
Cálculo de Depreciaciones DDB: Calcula la depreciación de un bien por doble (n)
disminución de saldo, siendo mayor los primeros períodos que los últimos
DDB(coste;valor_residual;vida;período;factor) factor = 2 por defecto
Ejemplo: Ha comprado un portátil de última generación por
valor de 5000€, cuya vida útil es de 5 años y con valor residual de 100€. Calcular la depreciación del equipo para el primer mes (doble) y para el primer año de vida (triple)
170
Préstamo Balón
Un préstamo en el que se realiza un gran pago antes de que acabe el periodo Si se trata de un préstamo a 3 años, el pago o cuota mensual se
calcula como si fuera un préstamo normal a 3 años.
La idea es realizar un pago balón antes de que finalice el período del préstamo, de cara a reducir la cantidad que se paga en concepto de intereses, mientras que se mantienen pequeños los pagos iniciales.
Este tipo de préstamo implica cierto auto-control y planificación para asegurar que se dispondrá de la cantidad para afrontar el pago balón en su momento
Ejemplo de uso No dispongo ahora del dinero, pero sé que dispondré de él más
adelante
Solicito un préstamo balón y voy pagando sólo los intereses
EJEMPLO
Calculadora-PagoBalon
171
Préstamo Balón
PAGO BALÓN SIN REDONDEO Obtenemos el Valor Actual del dinero que representan los pagos
que quedan por hacer
VA(D7/12;(D8-D9);-D14)
Más los intereses
(1+D7/12)*VA(D7/12;(D8-D9);-D14)
TABLA DE AMORTIZACIÓN PAGO
Si es el pago balón (C24=$D$9+1) importe restante + intereses del mes
Si no pago regular
INTERESES Se calculan cada mes sobre el importe que queda por pagar
EJEMPLO
Calculadora-PagoBalon
172
Herramientas para Análisis
Existe un conjunto de herramientas y funciones complementarias diseñadas para el análisis de datos, que no están disponibles en Excel
Si la utilidad está instalada, observará una opción denominada Análisis de Datos en la Ficha Datos.
En caso contrario, ir a Ficha Archivo Opciones Complementos Administrar complementos de Excel Ir
Seleccionar de la lista el ítem Herramientas para análisis para que se proceda a su instalación y activación
* (puede necesitarse el CD de instalación de Office 2010)
173
Herramientas de Análisis Estadístico
Tras instalarlas, se dispone, entre otras, de las siguientes opciones: Estadística Descriptiva: Produce una tabla de
medidas estadísticas a partir de un rango Histogramas: Representa de forma gráfica las
frecuencias de un rango Jerarquía y Percentiles: Clasifica un rango de datos
y los ordena
174
Herramientas de Análisis Estadístico
Estadística Descriptiva Produce una tabla que describe los datos usando
algunos valores estadísticos clásicos Para cada variable en el rango de entrada se devuelve una
lista de estadísticas Cada una se podría obtener combinando fórmulas
Las opciones K-ésimo mayor y menor proporcionan información adicional Por defecto ya se muestran el mayor y el menor
Las salidas son valores constantes, no son fórmulas, por lo tanto, si los datos cambian, deberán regenerarse las estadísticas
175
Herramientas de Análisis Estadístico
Jerarquía y Percentil Crea una tabla que muestra un ranking de ordinales y
percentiles para cada valor del rango de datos
176
Herramientas de Análisis Estadístico
Histograma Gráfico que representa el número de medidas o
frecuencia para cada intervalo (o clase) Podemos especificar el rango de intervalos para los
que obtener el histograma Debemos definir las clases en orden ascendente No tienen por qué ser uniformes
Podemos dejar que EXCEL lo haga por nosotros =(MAX(rango_datos) – MIN(rango_datos)) / 10
El resultado se puede ordenar de acuerdo a la frecuencia de cada intervalo
De nuevo, la salida son constantes, así que es preciso regenerar si cambian los datos de origen
177
Funciones Estadísticas
Ejercicio En el libro Prácticas, realizar una copia de la hoja Ejercicios de Formato
Crear los datos resumen que proporciona la herramienta Estadística Descriptiva para cada uno de los productos
Crear una Jerarquía de Ventas teniendo en cuenta el Total de ventas mensual
Crear un Histograma con los datos de Ventas Anuales en los siguientes rangos:
0 -500€
500 – 1000€
1000 – 1500 €
…..
7500 – 8000€
178 178
Agenda
Empezar a trabajar con EXCEL Personalización del Entorno de Trabajo
Edición de Celdas
Fórmulas y Funciones (introducción)
Selección de Celdas Trabajar en Modo Grupo
Referencias a Celdas
Nombrar Celdas
Opciones Avanzadas de Formato y Presentación
Alineación de Texto
Formatos Condicionales
Precisión de Pantalla
Uso de Estilos
Paneles
Definición de Esquemas
Impresión y Presentación
Vistas Personalizadas
Protección de Celdas y Hojas
Funciones Funciones de Texto
Funciones Matemáticas
Funciones Lógicas
Funciones (…)
Funciones de Información
Funciones de Búsqueda y Referencia
Funciones de Fecha y Hora
Funciones Estadísticas
Funciones Matriciales
Funciones Financieras
Herramientas para Análisis
Trabajar con Datos Datos Consolidados
Filtros
Filtros Avanzados
Macros
Validación de Datos
Subtotales
Función Buscar Objetivo
Escenarios
Tablas Dinámicas
Trabajar con Gráficos
Extracción de Datos y Libros Compartidos
179
Consolidar Datos
Es la utilidad para combinar valores de un conjunto de hojas de cálculo de un mismo libro o de libros distintos Operaciones que implican varias hojas y/o libros de trabajo
Se puede combinar información de hasta 255 hojas
Podemos consolidar datos utilizando fórmulas que usen referencias externas
=SUMA(Hoja2:Hoja10!A1)
=[Region1.xls]Hoja1!B2+[Region2.xls]Hoja1!B2
Podemos usar la opción Consolidar de la ficha Datos Por posición: Las hojas consolidadas deben tener el mismo formato
Por categoría: Consolida los datos de acuerdo a rótulos de categorías
180
Consolidar Datos
Consolidación por Posición EXCEL aplica la función de consolidación a las mismas
celdas de cada hoja de apoyo
Deben tener exactamente la misma estructura
Utilización Seleccionar rango donde se introducirán los datos
consolidados
Abrir el cuadro de diálogo Ficha Datos Consolidar Seleccionar el rango de datos a consolidar en cada
hoja
En este caso, es preferible introducir los rótulos
nosotros mismos. Cuando dejamos que EXCEL lo
haga pueden producirse resultados no deseados
181
Datos Consolidados
Consolidación por Categoría EXCEL aplica la función de consolidación a un conjunto de celdas
distinto en cada hoja de apoyo
Utilización [Introducir los encabezados de columna (filas)] Seleccionar rango donde se introducirán los datos consolidados
(basta con la celda inicial) Abrir el cuadro de diálogo Ficha Datos Consolidar Seleccionar el rango de datos a consolidar en cada hoja,
incluyendo los encabezados de fila y columna Seleccionar Columna Izquierda en la
opción Usar Rótulos en del cuadro de diálogo Si seleccionamos la opción Crear vínculos con los datos de origen,
EXCEL usa fórmulas para los datos consolidados, que define como subtotales (datos consolidados son actualizables)
De otro modo, los datos consolidados no son actualizables
182
Consolidados
Ejercicio: A partir de los datos de ventas de productos
lácteos del libro Consolidación Insertar una nueva hoja llamada Datos Consolidados Consolidar los datos de las Hojas 1 a 4:
Realizar primero la consolidación por posición
Realizar luego la consolidación por categoría
183
Categorías de funciones
Funciones de bases de datos: En realidad son como sus homónimas,
salvo que operan sólo sobre celdas que cumplan ciertos criterios
Dichos criterios deben expresarse utilizando celdas
Resultan muy útiles para el análisis de información de listas que cumplen criterios particulares, porque sus homólogas no son capaces de ignorar celdas aunque los datos se muestren filtrados
EJEMPLO
Bases de Datos - BD
184
Definición de Criterios con caracteres comodín: ejemplos
=”=January” ↔ ‘=January => contiene exactamente …
January => empieza por …
<>C* => lo que sea menos algo que empiece por ‘C’
>=L => empieza por una letra que vaya de la L a la Z
*campo* => cualquiera que contenga la palabra campo …
Sm* => empiezan por SM …
s*s => empieza por s y hay más Ss después (da igual en qué posiciones)
s?s => empieza por s y tiene una s como tercer carácter (no sólo palabras de 3 caracteres)
="=s*s” => empieza y termina por s
<>*c => no acaban en c
=???? => cuatro letras
<>???? => no contienen 5 letras (más o menos)
<>*c* => no contienen Cs
~? => contienen un único signo de interrogación (la tilde convierte el signo de interrogación en un carácter normal)
= => contienen un blanco
<> => cualquier entrada que no esté en blanco
=“=c” => sólo el carácter ‘c’
No distingue MAY de MIN. Por ejemplo, el
criterio se* devolverá celdas que
contengan Sevilla, sereno y SEAT
185
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
186
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: Ficha Datos Filtro
Si sólo queremos filtrar por algunas columnas, seleccionar sus encabezados y activar el autofiltro
Podemos refinar o personalizar los criterios proporcionados por defecto
Salvo en los casos que veremos, las
fórmulas que actúan sobre datos filtrados no
se ajustan para ser computadas sólo sobre
los datos visibles
EJEMPLO
AutoFiltros
187
Filtros Avanzados
Permite definir filtros que soporten: Criterios que impliquen más de dos condiciones
para una misma columna (Enero y Febrero y Marzo …)
Criterios calculados Extracción de filas para copiar en otra parte Mostrar sólo valores únicos (evitar duplicados)
Si queremos extraer datos a otra hoja usando
un filtro avanzado, tenemos que definir el filtro
desde la hoja DESTINO haciendo referencia a
las celdas de la hoja ORIGEN
188
Filtros Avanzados
Los criterios deben definirse utilizando celdas (similar a las funciones de Base de Datos) Una o más filas, donde la primera contiene nombres de campos y
el resto los criterios
Los criterios definidos en distintas columnas se combinan con una operación Y
Los criterios definidos en una misma columna se combinan en un operación O
Suelen usarse filas por encima de los datos
No es preciso definir un criterio para todas las columnas
No se autoactualizan
EJEMPLO
Filtros Avanzados
Provincia empieza por ‘Ba’ y hay más de 500 AUDI matriculados O
Provincia empieza por ‘M’ y hay más de 100 AUDI matriculados
189
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
190
Filtros
Ejercicio A partir de la hoja Precio Petroleo Diario del libro
Practicas definir los siguientes filtros Autofiltro para mostrar los 10 mejores precios del mes de
Diciembre de 2004
Quitar el Autofiltro
Aplicar un filtro avanzado para el año 2005, donde:
El valor del mes de Febrero sea mayor de 32
El valor del mes de Abril sea mayor de 40
El valor del mes de Septiembre sea mayor de 50
El valor de la suma de los meses de Julio a Septiembre del 2005 sea mayor a 100
Macros
Permiten automatizar tareas y unirlas en una sola
Crear una macro Ficha Vista Macros Grabar macro …
Realizar las acciones deseadas y pulsar el botón
Detener grabación al concluir
Macros
Ejecutar una macro Ficha Vista Macros Ver macros Ejecutar
También es posible mediante la ficha Programador
Macros
Activar la ficha Programador : Archivo Opciones Personalizar cinta de opciones Seleccionar Programador
Macros
Asociar un botón a la ejecución de una macro Ficha Programador Insertar Formulario Seleccionar la opción del botón
El puntero del ratón se transforma en una cruz.
Debemos hacer clic sobre la zona de la hoja donde
queramos insertar el botón
Aparece el cuadro de diálogo Asignar macro
Con el botón derecho sobre el botón podemos: Modificar Texto
Asignar macro …
195
Macros
Ejercicio Crear una nueva hoja de cálculo en el libro Prácticas Abrir el submenú Macro del menú Herramientas.
opción Grabar nueva macro... Aparece el cuadro de diálogo Grabar macro. Escribir el nombre de la macro, EjemploMacro y pulsa Aceptar. Ahora estamos grabando, vamos a realizar las acciones necesarias
para dar formato la hoja de trabajo. Presionar sobre el botón Negrita de la barra Formato. Presionar sobre el botón Cursiva de la barra Formato. Escoger el tipo de fuente Abbess. Escoger el tamaño de la fuente en 14 puntos.
Se ha definido un nuevo formato para una cabecera de datos, por tanto se finaliza la grabación de la macro. Presionar sobre el botón detener de la barra Macro, o acceder al menú
Herramientas - Macro - Detener grabación
196
Macros (II)
Ejercicio Escribir en la celda D1 Cabecera, en la celda E1 de y
en la celda F1 prueba.
Seleccionar las celdas anteriores D1, E1 y F1 y ejecuta la macro que grabaste
Observar como las celdas seleccionadas adoptan el formato automáticamente
Añadir un botón a la hoja y asóciale la macro grabada
Comprobar el funcionamiento del botón
197
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
198
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
199
Validación de Datos
Seleccionar rango de celdas a controlar (TODO el rango, aunque aún no tenga valores introducidos)
Ficha Datos Validación de datos
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 Ficha Fórmulas Grupo Auditoría de Fórmulas
200
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
201
Validación de Datos
Ejercicio:
En la hoja Validación del libro Practicas, aplicar a las celdas en azul un criterio de validación que limite el valor de la celda a valores numéricos. Utilizar el estilo Límite para el mensaje de error, que impide introducir datos no válidos en la celda.
A las celdas en verde aplicar un criterio de validación que limite la longitud de las entradas de texto a siete caracteres. Utilizar el estilo Advertencia para el mensaje de error, que permite cancelar la introducción de datos o introducir el valor no válido en la celda seleccionada.
A las celdas en amarillo un criterio de validación que restringe a un número entero que esté entre 1 y 10. Utilizar el estilo Información
A las celdas en rosa aplicar un criterio de validación que limita los datos válidos a una lista de valores. Cuando se seleccione una de las celdas del rango, aparece una lista desplegable. Utiliza el estilo Límite para el mensaje de error, lo que impide introducir datos no válidos en la celda.
Usar una fórmula para no permitir valores repetidos en las celdas en naranja Usar la función CONTAR.SI()
Usar una fórmula para permitir sólo valores que empiecen por el carácter “a” Usar la función IZQUIERDA()
202
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
EJEMPLO
Alternativamente, puede utilizar la opción Subtotal de la ficha Datos para realizar la misma función de forma más intuitiva Los datos de la lista deben estar
ordenados previamente
203
Tipos de Subtotales
Tipo de Operación Operación
1 PROMEDIO
2 CONTAR
3 CONTARA
4 MAX
5 MIN
6 Multiplicación (PRODUCTO)
7 Desviación estándar (DESVEST)
8 Desviación estándar de la población total (DESVESTP)
9 SUMA
10 Varianza (VAR)
11 Varianza de la población total (VARP)
204
Subtotales
Ejercicio Realizar una copia de la hoja Base de Datos
del libro Practicas Generar los subtotales para cada localidad de
acuerdo a las unidades vendidas.
A partir de los datos de la hoja Datos.CafeCacaoTe de Tabla dinámica, obtener: Subtotales de Suma para los Totales de acuerdo a
la sucursal
Subtotales de Cuenta para los Totales de acuerdo al mes en curso
205
Subtotales
Otra aplicación de la función SUBTOTAL es realizar cálculos sobre listas de datos filtrados A la hora de realizar los cálculos ignora aquellas
celdas ocultas como resultado de un filtrado
Si usamos la función SUMA, estamos
incluyendo en el cálculo las celdas
intermedias
EJEMPLO
Libro Pedidos
206
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? Lanzamos la opción Ficha Datos Análisis Y si 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.
207
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.
208
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€
209
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
Ficha Datos Análisis Y si Administrador de escenarios
Definir, mostrar, ocultar, resumir escenarios
EJEMPLO
Escenarios
Para que las variables se muestren como un nombre
y no con la referencia a la celda correspondiente,
debemos nombrar dichas celdas
210
Escenarios
Ejercicio A partir del modelo de negocio construido en la hoja Escenarios
del libro Practicas, crear un escenario inicial donde las celdas cambiantes serán: el valor de los ingresos por visita de cliente (C5)
los costes derivados de dicha visita (C6)
El segundo escenario va a incrementar los costes por visitas en un 5%, pero baja los ingresos en el 5%
Un tercer escenario, donde a partir del escenario anterior, los costes incrementarán un 3% Extraer la Tabla Resumen de los Escenarios
Repetir el proceso pero antes dar nombre a las celdas para que la Tabla Resumen mejore su legibilidad
211
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
212
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 la ficha Insertar, seleccionar Tabla dinámica
213
Tablas Dinámicas (creación)
Rango de Datos Seleccionar el rango de la tabla,
incluyendo la fila de titulo
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)
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
215
Tablas Dinámicas: filtrar
Cada campo incluye un desplegable que permite filtrar los valores mostrados Ejemplo: mostramos sólo las dos primeras semanas
216
Tablas Dinámicas: rediseñar la tabla
También podemos modificar la forma de nuestra tabla dinámica arrastrando los campos en las diferentes áreas:
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 …
Tablas Dinámicas …
Utilizando los datos de la hoja DatosPeajes-2 cree una tabla como la que se muestra en la figura
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 de valor… 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: configuración de campos
Las dos opciones anteriores se pueden obtener también mediante un clic derecho sobre el campo y…
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 Ficha Opciones Campos, elementos y conjuntos
Campo calculado…
Tablas Dinámicas: campos calculados
Añadimos un nuevo 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 Filtro
En esta ocasión mostramos sólo los 2 meses en los cuales el peaje pagado por los autos ha reportado mayor recaudación
Tablas Dinámicas: agrupar
Podemos definir distintos niveles de agrupamiento: Seleccionar los encabezados de las columnas que
queremos agrupar
Ficha Opciones Agrupar selección
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 filtro de informe 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)
227
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
Tablas Dinámicas
Ejercicio A partir de la hoja Datos.Ventas del libro
TablasDinamicas, cree la siguiente tabla dinámica
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
230
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
(si se le plantean varias opciones, elija por cual desea definir
niveles de agrupamiento)
231
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
232
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 filtro de informe
la Sucursal como fila
El Mes como columna
Use la ficha Opciones Opciones Mostrar páginas de filtro de informes… para mostrar la información referente a las ventas de cada tipo de producto en una página distinta
233
Tablas Dinámicas
Ejercicio: A partir de los datos de la hoja Datos.Pedidos cree
una nueva tabla dinámica como la siguiente
234
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
235
Tablas Dinámicas
Ejercicio: Utilizando de nuevo la hoja Datos.Pedidos cree una nueva Tabla
Dinámica, esta deberá presentar el siguiente aspecto: Ficha Opciones Campos, elementos y conjuntos Campo
calculado… añadir de nuevo el campo Total
Clic derecho sobre el campo Mostar valores como Porcentaje del total general
Ficha Opciones Campos, elementos y conjuntos Campo calculado… añadir de nuevo el campo Total, pero esta vez incrementando su valor en un 5 y un 10%
236
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
237
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 dinámicos que se incluye en la ficha Opciones de tabla dinámica
Realice modificaciones en el gráfico y/o la tabla y compruebe cómo se reflejan automáticamente en el otro objeto
238
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
239
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
240 240
Agenda
Empezar a trabajar con EXCEL Personalización del Entorno de Trabajo
Edición de Celdas
Fórmulas y Funciones (introducción)
Selección de Celdas Trabajar en Modo Grupo
Referencias a Celdas
Nombrar Celdas
Opciones Avanzadas de Formato y Presentación
Alineación de Texto
Formatos Condicionales
Precisión de Pantalla
Uso de Estilos
Paneles
Definición de Esquemas
Impresión y Presentación
Vistas Personalizadas
Protección de Celdas y Hojas
Funciones Funciones de Texto
Funciones Matemáticas
Funciones Lógicas
Funciones (…)
Funciones de Información
Funciones de Búsqueda y Referencia
Funciones de Fecha y Hora
Funciones Estadísticas
Funciones Matriciales
Funciones Financieras
Herramientas para Análisis
Trabajar con Datos Datos Consolidados
Filtros
Filtros Avanzados
Macros
Validación de Datos
Subtotales
Función Buscar Objetivo
Escenarios
Tablas Dinámicas
Trabajar con Gráficos
Extracción de Datos y Libros Compartidos
241
Creación de diagramas y gráficos
Excel puede crear gráficos a partir de los datos de una hoja de cálculo.
El usuario puede incrustar un gráfico en una hoja de cálculo o crear el gráfico en una hoja especial para gráficos (hoja de gráficos).
En cualquier caso, el gráfico queda vinculado a los datos a partir de los cuales fue creado, por lo que si en algún momento los datos cambian, el gráfico se actualizará de forma automática.
Los gráficos de Excel contienen muchos objetos que pueden ser seleccionados y modificados individualmente. SUGERENCIA: incluyendo una fila vacía al
final del rango de datos utilizado para crear el
gráfico, aseguramos que futuras
“ampliaciones” serán mostradas en el gráfico
242
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 la ficha Insertar / Grupo 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
243
Asistente para Gráficos: tipo de gráfico
1
2
Podemos crear un gráfico con las propiedades que nos
convengan y luego asignar sus propiedades a un tipo de
gráfico personalizado:
Ficha Diseño Guardar como plantilla
244
Asistente para gráficos: datos de origen, series y categorías
3
No necesariamente todos los datos han de ser
contiguos, podemos escoger rangos alternos
245
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 de formato
246
Barra de Herramientas gráficas
En la ficha Formato de las Herramientas de gráficos se encuentra un desplegable que permite elegir un elemento del gráfico y modificar el formato empleado.
Acceso a elementos difíciles de
seleccionar con el ratón
247
Gráficos
Ejercicio: A partir de los datos de la hoja Datos para Gráfico, crear un
gráfico de barras con los datos de la matriz de frutas
VENTA DE FRUTAS
29
23
87
3425
54
34
3
23 21
37 10
2532
4150
5968
7786
95104
4
76
21
5059
3
75
23
93100
3 4
0
20
40
60
80
100
120
Ene
ro
Febre
ro
Mar
zoAbr
il
May
o
Junio
Julio
Ago
sto
Sep
tiem
bre
Oct
ubre
Nov
iem
bre
Dicie
mbr
e
Meses
Pro
du
cció
n
Plátano Fresas Melocotones
248
Gráficos
Ejercicio: A partir de los datos de la hoja Datos para Gráficos, crear un
gráfico de columnas con los datos de Producción de Café
Comparación por Zonas
0
10
20
30
40
50
60
70
80
90
100
1er trim.2do trim.3er trim.4to trim.
Trimestre
Pro
du
cció
n
Este
Oeste
Norte
249
Gráficos
Ejercicio A partir de los datos de la hoja Datos para Gráficos, crear un
gráfico de líneas en 3D con los datos de Producción de Café
1e
r tr
im.
3e
r tr
im.
Este
Oe
ste
No
rte
020406080
100
Producción
Trimestre
s
Zona
s
PRODUCCION DE CAFÉ
Este
Oeste
Norte
Este 20,4 27,4 90 20,4
Oeste 30,6 38,6 34,6 31,6
Norte 45,9 46,9 45 43,9
1er trim. 2do trim. 3er trim. 4to trim.
250
Gráficos
Precio Promedio Mensual
0,00
10,00
20,00
30,00
40,00
50,00
60,00
70,00
80,00
90,00
100,00
Diciem
bre 200
4
Abr
il
Ago
sto
Diciem
bre
Abr
il
Ago
sto
Diciem
bre
Abr
il
Ago
sto
Diciem
bre
Abr
il
Pre
cio
Precio Promedio Mensual
Ejercicio A partir de los datos de la
hoja Precio Petróleo Diario crear un gráfico de líneas con los datos del Precio del Petróleo
251
Gráficos
Ejercicio A partir de los datos de la hoja PNB Países Europeos del libro
Prácticas crear un gráfico de circular con efecto 3D como el mostrado en la figura
PNB Paises Europeos Año 2002
14.674
18.382
17.640
5.000
9.191
15.1769.072
14.458
17.609
14.508
13.554
Para rotar el gráfico
·Clic derecho -> Giro 3D…
252
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
253
Líneas de Tendencia
254
Líneas de Tendencia
Ejercicio: A partir de los datos del libro Precio Petróleo Diario, hacer un
gráfico de Dispersión y representar la línea de tendencia en el Precio para el Año 2008
Tendencia Petróleo 2008
79,5280,23
88,73
93,34
76,00
78,00
80,00
82,00
84,00
86,00
88,00
90,00
92,00
94,00
96,00
Pre
cio
Serie1 Linea de Tendencia
255
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 Clic derecho sobre la serie Dar formato a serie de datos…
256
Gráficos Combinados
Ejercicio A partir del gráfico de la matriz de frutas, crear un gráfico
combinado
VENTA DE FRUTAS
29
23
87
3425
54
34
3
23 213
4
76
21
5059
3
75
23
93100
3 47 10
2532
4150
5968
7786
95104
0
20
40
60
80
100
120
Ene
ro
Febre
ro
Mar
zoAbr
il
May
o
Junio
Julio
Ago
sto
Sep
tiem
bre
Oct
ubre
Nov
iem
bre
Dicie
mbr
e
Meses
Pro
du
cció
n
Plátano Melocotones Fresas
257
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.
258
Otros tipos de gráfico: Gráficos de Burbuja
Ejercicio: Tomar los datos de la tabla Comparación de Productos de la hoja
Datos para Gráficos y crear un gráfico de Burbujas como el que se muestra a continuación Eje X: Nº de Competidores
Eje Y: Ventas
Tamaño de Burbujas: Porcentaje
259
Otros tipos de gráfico: Gráficos Radiales
Ejercicio: Tomar los datos de la tabla Matriz de Frutas y crear un gráfico
radial como el que se muestra a continuación
Comparación de Producción de Frutas
-20
30
80
130Enero
Febrero
Marzo
Abril
Mayo
Junio
Julio
Agosto
Septiembre
Octubre
Noviembre
Diciembre
Plátano
Fresas
Melocotones
260
Otros tipos de gráfico: Gráficos Cónicos
Ejercicio: Tomar los datos de la tabla Beneficios de Venta de Vinos y crear
un gráfico cónico como el que se muestra a continuación
1er
trim. 2do
trim. 3er
trim. 4to
trim.
Esperado
Real
-4
-2
0
2
4
6
Beneficio de Venta Vinos
Esperado
Real
261
Otros tipos de gráfico: Gráficos Superpuestos
Ejercicio: Tomar los datos de la tabla Beneficios de Venta de Vinos y crear
un gráfico de barras con superposición del 60% como el que se muestra a continuación
Beneficios Ventas Vinos
2,02
4,13
-2,55
5,57
3,38 3,58
-3,21
4,81
-4
-2
0
2
4
6
8
1er trim. 2do trim. 3er trim. 4to trim.
Valo
res
Esperado Real
262
Otros tipos de gráfico: Relleno de áreas
Ejercicio: Tomar los datos de la tabla ¿Has hecho pellas alguna vez? y
crear un gráfico de barras usando imágenes para el relleno tal como se muestra a continuación
263 263
Agenda
Empezar a trabajar con EXCEL Personalización del Entorno de Trabajo
Edición de Celdas
Fórmulas y Funciones (introducción)
Selección de Celdas Trabajar en Modo Grupo
Referencias a Celdas
Nombrar Celdas
Opciones Avanzadas de Formato y Presentación
Alineación de Texto
Formatos Condicionales
Precisión de Pantalla
Uso de Estilos
Paneles
Definición de Esquemas
Impresión y Presentación
Vistas Personalizadas
Protección de Celdas y Hojas
Funciones Funciones de Texto
Funciones Matemáticas
Funciones Lógicas
Funciones (…)
Funciones de Información
Funciones de Búsqueda y Referencia
Funciones de Fecha y Hora
Funciones Estadísticas
Funciones Matriciales
Funciones Financieras
Herramientas para Análisis
Trabajar con Datos Datos Consolidados
Filtros
Filtros Avanzados
Macros
Validación de Datos
Subtotales
Función Buscar Objetivo
Escenarios
Tablas Dinámicas
Trabajar con Gráficos
Extracción de Datos y Libros Compartidos
264
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
Ficha Datos Desde…
265
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
266
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
267
Importar ficheros de texto
Ejercicio
Importar a Excel, los datos del archivo
Departamentos.txt, el cual tiene delimitado sus campos por un ancho fijo
268
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
269
Libros de Trabajo Compartidos
Excel no está pensado para que varios usuarios trabajen simultáneamente sobre un mismo libro o fichero, sin embargo proporciona esta posibilidad limitando algunas funcionalidades
Ficha Revisar Compartir libro
Mostrará otros usuarios que
estén trabajando con este mismo libro
270 270
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
271
Algunos complementos importantes
Ejercicio A partir de la hoja Listado de Libros del libro
Prácticas utilice el asistente de suma condicional para saber cuantos libros de un determinado género y autor hay en el listado
Una vez resuelto el problema anterior, mejore la solución definiendo listados de valores únicos del género y nacionalidad y utilícelos como criterios para la fórmula