Excel XP avanzado
Este manual es propiedad de: PUBLICACIONES VÉRTICE S.L. C/ Ter 2-4-6 Pol. Ind. El Viso 29006 Málaga. Tfno: 902 53 24 32 www.editorialvertice.com
[email protected]
Y ha sido elaborado por: Antonio Manuel Campos Navas
ISBN: 978-84-92533-59-6 DEPÓSITO LEGAL: MA-694 -2008 No está permitida la reproducción total o parcial del presente manual bajo cualquiera de sus formas gráficas o audiovisuales sin la autorización previa y por escrito de los titulares del depósito legal. Impreso en España – Printed in Spain
Índice General
Ed. 3.1
ÍNDICE GENERAL
TEMA 1. FUNCIONES LÓGICAS 1.1. Introducción ...............................................................1 1.2. La función =Sumar.Si ..................................................2 1.3. La función =Contar.Si ..................................................3 1.4. La función disyunción =O.............................................3 1.5. La función conjunción =Y .............................................4 1.6. La función condicional =SI ...........................................5 1.6.1. Uso de la función =SI ........................................5 1.6.2. Uso de la función =SI con la función =O ..............7 1.6.3. Uso de la función =SI con la función =Y ...............7 1.7. El Formato Condicional ................................................9 Ideas clave .....................................................................11 Autoevaluación del Tema 1................................................12 Ejercicios ........................................................................14
TEMA 2. FUNCIONES PARA USO CON TEXTOS 2.1. Introducción ..............................................................17 2.2.1. Código ASCII....................................................17 2.2.2. Mayúsculas y Minúsculas ...................................18 2.2.3. Consulta sobre el texto......................................18 2.2.4. Extracción de textos..........................................18 2.2.5. Comparación de textos ......................................20 2.2.6. Concatenación de textos ....................................22
Excel XP avanzado
I
Ed. 3.1
Índice General
2.2.7. Modificación de textos .......................................22 2.2.8. Funciones de Conversión ...................................22 2.2. Funciones de Texto.....................................................23 2.3. Funciones de Información............................................23 2.4. Funciones de Fecha y Hora ..........................................25 2.5. Funciones de búsqueda y referencia .............................27 Ideas clave ......................................................................30 Autoevaluación del Tema 2.................................................31 Ejercicios .........................................................................33
TEMA 3. LOS OBJETOS EN EXCEL 3.1. Concepto de Objeto. Utilidad ......................................35 3.2. Insertar Imágenes: prediseñadas, desde archivo y desde escáner/cámara ............................................35 3.2.1. Imágenes Prediseñadas ....................................36 3.2.2. Imágenes desde Archivo ...................................37 3.2.3. Insertar imagen desde Escáner/Cámara..............38 3.3. Imágenes WordArt ....................................................39 3.4. Diagramas y Organigramas ........................................41 3.4.1. Diagramas ......................................................41 3.4.2. Organigramas..................................................43 3.5. Creación de Ecuaciones..............................................44 3.6. Barra de Herramientas Dibujo .....................................46 3.7. Insertar símbolos y comentarios..................................48 3.7.1. Insertar símbolos .............................................48 3.7.2. Insertar Comentarios .......................................49 Ideas clave .....................................................................50 Autoevaluación del Tema 3................................................52 Ejercicios ........................................................................54
II
Excel XP avanzado
Índice General
Ed. 3.1
TEMA 4. GRÁFICOS EN EXCEL 4.1. Tipos de gráficos .......................................................57 4.2. Crear un gráfico I: elección del tipo de gráfico ..............60 4.3. Crear un gráfico II: elección de los datos de origen...........62 4.4. Crear un gráfico III: opciones del gráfico .....................62 4.5. Crear un gráfico IV: insertar gráfico.............................64 4.6. Personalizar el gráfico: fuentes, tamaños, propiedades, escalas, formatos ...................................65 Ideas clave .....................................................................66 Autoevaluación del Tema 4................................................67 Ejercicios ........................................................................69
TEMA 5. BASES DE DATOS EN EXCEL (LISTAS) 5.1. Concepto de Base de Datos. Creación de una base de datos (lista) en Excel ............................................71 5.2. Ordenación de los datos .............................................73 5.3. Gestión de los datos en formato Formulario ..................76 5.4. Utilización de filtros: Autofiltros...................................78 5.5. Utilización de filtros: Filtro Avanzado ...........................81 5.6. Cálculos con bases de datos: subtotales.......................82 5.7. Control de datos: validación .......................................84 Ideas clave .....................................................................88 Autoevaluación del Tema 5................................................89 Ejercicios ........................................................................91
Excel XP avanzado
III
Ed. 3.1
Índice General
TEMA 6. PLANTILLAS Y MACROS EN EXCEL 6.1. Concepto de plantilla. Utilidad.....................................93 6.2. Uso de plantillas predeterminadas de Excel. Crear Plantillas de Libro .............................................94 6.2.1. Plantillas predeterminadas en Excel....................94 6.2.2. Crear Plantillas de Libro ....................................94 6.3. Crear plantillas personalizadas con Excel ......................96 6.4. Uso y modificación de Plantillas Personalizadas............101 6.5. Concepto y Creación de Macros .................................102 6.5.1. Ejemplo de macro asignada a un botón de Barra .......................................104 6.6. Ejecutar Macros.......................................................105 6.7. Nivel de Seguridad de Macros ...................................106 Ideas clave....................................................................107 Autoevaluación del Tema 6 ..............................................109 Ejercicios ......................................................................111
TEMA 7. FORMULARIOS Y ANÁLISIS DE DATOS EN EXCEL 7.1. Concepto de Formulario. Utilidad ...............................115 7.2. Análisis de la Barra de Herramientas Formularios ........115 7.3. Creación de Formularios ...........................................118 7.4. Análisis Hipotético Manual ........................................120 7.5. Análisis Hipotético con Tablas de Datos ......................121 7.5.1. Tablas de datos de una variable .......................121 7.5.2. Tablas de datos de dos variables ......................123 7.6. Buscar Objetivo.......................................................125 7.7. Solver ....................................................................126
IV
Excel XP avanzado
Índice General
Ed. 3.1
Ideas clave....................................................................130 Autoevaluación del Tema 7 ..............................................131 Ejercicios ......................................................................133
TEMA 8. REDES E INTERNET CON EXCEL 8.1. Correo electrónico y Excel ........................................135 8.2. Insertar Hipervínculos en hojas de cálculo ..................136 8.3. Guardar Hojas de Cálculo como páginas web ..............137 8.4. La Barra de Herramientas Web ..................................138 8.4.1. Crear y trabajar con un libro compartido ...........139 8.4.2. Áreas de trabajo compartidas ..........................140 Ideas clave....................................................................142 Autoevaluación del Tema 8 ..............................................143 Ejercicios ......................................................................145
Excel XP avanzado
V
Ed. 3.1
VI
Índice General
Excel XP avanzado
Ed. 3.1
Tema 1: Funciones lógicas
TEMA 1 FUNCIONES LÓGICAS 1.1. 1.2. 1.3. 1.4. 1.5. 1.6.
Introducción La función =Sumar.Si La función =Contar.Si La función disyunción =O La función conjunción =Y La función condicional =SI 1.6.1. Uso de la función =SI 1.6.2. Uso de la función =SI con la función =O 1.6.3. Uso de la función =SI con la función =Y 1.7. El Formato Condicional
1.1. Introducción Este tema tratará un grupo de funciones denominadas Funciones Lógicas. Éstas tienen como característica principal no ofrecer ningún resultado específico, sino que el mismo dependerá de una o varias condiciones lógicas. En primer lugar vamos a trabajar con dos funciones que utilizan condicionales: la función “=Sumar.Si” y “=Contar.Si”, aunque pertenecen a las categorías de funciones matemáticas y funciones estadísticas respectivamente. Se trata ya de funciones que implican una condición para dar una respuesta, aunque su diferencia con las funciones puramente “lógicas” radica en que aquéllas una vez cumplida la condición dan una respuesta limitada (sumar o contar), mientras que con las funciones lógicas la respuesta será la que el usuario sea capaz de decidir: desde simples operaciones aritméticas hasta complejas funciones anidadas dentro de las funciones lógicas (por ejemplo, podemos hacer que se calcule un porcentaje u otro de IRPF dependiendo del número de hijos del trabajador). En una función lógica siempre aparece al menos una condición (pregunta lógica), que podrá utilizar los siguientes operadores: =(igual), <>(distinto), >(mayor), <(menor), <=(menor o igual), >=(mayor o igual).
Excel XP avanzado
1
Tema 1: Funciones lógicas
Ed. 3.1
1.2. La función =Sumar.Si Esta función, perteneciente a la categoría “Funciones Matemáticas” suma, en un rango de celdas, sólo aquellos valores que cumplen una determinada condición. La sintaxis de la función es la siguiente: =SUMAR.SI(rango de lectura;”criterio”;rango de suma) (el argumento “criterio” no irá entre comillas cuando la condición sea un número) Veamos a continuación un ejemplo de uso de esta función donde se sumarán las cantidades pagadas por nóminas, agrupadas según el puesto de trabajo desempeñado (Directivo, Administrativo u Oficial).
Se trata de sumar las nóminas de los empleados de una empresa agrupándolas según el puesto que ocupa cada uno. Así, la función =SUMAR.SI(B2:B8;”Administrativo”;C2:C8) comprueba en el rango B2:B8 que celdas contienen el valor “Administrativo”, para finalmente sumar las cantidades correspondientes en el rango C2:C8.
2
Excel XP avanzado
Ed. 3.1
Tema 1: Funciones lógicas
1.3. La función =Contar.Si Esta función, perteneciente a las “funciones estadísticas” cuenta las celdas que cumplan una determinada condición. La sintaxis de la función es: =CONTAR.SI(Rango;”condición”) (el argumento “condición” no irá entre comillas si se trata de un número) Veamos un ejemplo de uso de esta función que pretende saber el número de empleados que ganan más de 2.000 € mensuales y el número de empleados cuyo sueldo es inferior a los 1.000 € mensuales:
1.4. La función disyunción =O Disyunción significa que hay que elegir entre una u otra opción, pero sólo entre una de las dos. Así, por ejemplo, en el lenguaje si utilizamos la frase “¿café o té?” comprendemos perfectamente que hemos de elegir una de las dos bebidas y no las dos.
Excel XP avanzado
3
Tema 1: Funciones lógicas
Ed. 3.1
Pues bien esa es la utilidad de la función =O, plantear una “pregunta” o “condición” entre dos valores (número o texto) de los cuales sólo se podrá cumplir uno de los dos. Por ejemplo, ante la pregunta ¿tiene ordenador en casa? cabe esperar una respuesta afirmativa o una respuesta negativa. La sintaxis de la función es la siguiente: =O(Condición1;Condición2;…) (pueden anidarse de 1 a 30 condiciones) Esta función devolverá el valor 1 (verdadero) si se cumple, al menos, una de las dos condiciones, y devolverá el valor 0 (falso) si no se cumple ninguna de las dos. Así, por ejemplo, si B4=7 y B5=4 y en B6 planteamos la función =O(B4>5;B5>5), el resultado de la función será 1 puesto que se cumple al menos una de las dos condiciones (B4>5) En realidad, como veremos en ejemplos posteriores, la función disyunción =O no suele utilizarse de forma independiente sino anidada (“dentro”) de otra función lógica que veremos en este tema: la función lógica =SI.
1.5. La función conjunción =Y En este caso Conjunción significa que se cumplen las dos condiciones. Así, por ejemplo, en el lenguaje si utilizamos la frase “voy a tomar café y tarta” comprendemos perfectamente que vamos a tomar los dos alimentos y no sólo uno de los dos. Pues bien esa es la utilidad de la función =Y, plantear una “pregunta” o “condición” entre dos valores (número o texto) de los cuales se van a cumplir los dos. Por ejemplo, ante la pregunta ¿tiene ordenador y conexión a Internet? puede responderse afirmativamente a las dos preguntas implícitas. La sintaxis de la función es la siguiente: =Y(Condición1;Condición2) (pueden anidarse de 1 a 30 condiciones)
4
Excel XP avanzado
Ed. 3.1
Tema 1: Funciones lógicas
Esta función devolverá el valor 1 (verdadero) si se cumplen las dos condiciones y devolverá el valor 0 (falso) si no se cumple ninguna de las dos o si sólo se cumple una. Así, por ejemplo, si B4=7 y B5=4 y en B6 planteamos la función =Y(B4>5;B5>5), el resultado de la función será 0 puesto que no se cumplen las dos condiciones y para que el resultado fuese “verdadero” tendrían que cumplirse las dos condiciones. En realidad, como veremos en ejemplos posteriores, la función conjunción =Y no suele utilizarse de forma independiente sino anidada (“dentro”) de otra función lógica que veremos en este tema: la función lógica =SI.
1.6. La función condicional =SI 1.6.1. Uso de la función =SI Se trata de la función lógica esencial cuando el resultado de una celda (fórmula) dependa del contenido de otra u otras. La sintaxis inicial de la función es: =SI(Condición;Valor si verdad;Valor si falso) Es decir, en el caso de que se cumpla la condición se aplicará el “Valor si verdad” y en el caso de que no se cumpla, se aplicará el “Valor si falso”. Por ejemplo, supongamos que en B3 tenemos un número y queremos saber si es par o impar. En B4 debemos introducir la función: =SI(RESIDUO(B3;2)=0;”PAR”;”IMPAR”) Si B3=14 el resultado de la función condicional será “PAR” (verdadero) puesto que el resto de dividir 14 entre 2 es igual a 0 y, por tanto, se trata de un número par. Si por el contrario B3=9 el resultado será “IMPAR” puesto que el resto no es igual a 0. Pero esta función lógica suele utilizarse de una forma que permite mayores posibilidades: anidar condicionales. Así, por ejemplo, suponer que vamos a realizar una hoja de facturación de una empresa, donde el tipo de IVA se introduce de la siguiente forma: 0 (0%), 1 (4%), 2 (7%), 3 (16%). Así, por ejemplo, se escribe 3 para aplicar un IVA del 16%.
Excel XP avanzado
5
Tema 1: Funciones lógicas
Ed. 3.1
A la hora de hacer los cálculos, no podemos utilizar directamente la celda donde se encuentra el tipo de IVA, ya que éste no es el correcto, sino su equivalente según las normas de la empresa. Por tanto debemos aplicar la función =SI, de forma que calcule los porcentajes adecuados según el tipo introducido en la celda correspondiente (y controlando la posibilidad de error) Es importante saber que Excel permite anidar hasta 7 condicionales dentro de la función condicional =SI. El ejercicio resuelto podría quedar de la siguiente forma:
De esta forma, escribiendo la función solamente una vez (en G3) puede copiarse a todas las celdas de la columna (si no la hubiésemos utilizado, tendríamos que introducirlas una por una). Además, permite controlar de una forma automática el IVA a aplicar según el Tipo introducido en la columna F, así como la posibilidad de error (como se muestra en la fila 8, debido a que hemos introducido un 7 como valor). Así, si introducimos un “1” como tipo de IVA calculará el 4% del “parcial”, si introducimos un “2” calculará el 7%... También podemos comprobar como cada uno de los condicionales anidados utilizados cumple estrictamente la sintaxis de la función:
6
Excel XP avanzado
Ed. 3.1
Tema 1: Funciones lógicas
=SI(Condición;Valor si verdad;Valor si falso). Por último, podemos observar cuatro paréntesis al finalizar la función; esto se debe a que no hemos “cerrado” ninguno de los condicionales anteriores y, por tanto, debe hacerse ahora (un paréntesis por condicional)
1.6.2. Uso de la función =SI con la función =O Como ya dijimos anteriormente, la forma más habitual de usar la función disyunción =O es anidada dentro de la función condicional =SI, ya que es la forma de “darle sentido” al resultado de la disyunción. Veamos un ejemplo. Supongamos que los vehículos nuevos han de pagar un impuesto para luchar contra la contaminación ambiental. Para calcular el % a pagar sobre el valor inicial del vehículo supongamos que los vehículos industriales y los diesel pagan un 0,75% y el resto el 0,5%. Así, el ejemplo podría quedar resuelto de la siguiente forma:
=C6*D6/100 =SI(O(A7=”Industrial”;B7=”Diesel”):0,75;0,5)
Así, en la fila 4 al tratarse de un “turismo” de motor “diesel” se cumple, al menos, una de las dos condiciones de la disyunción para aplicar el 0,75% (es Diesel). En cambio, en la fila 5, al no cumplirse ninguna de las dos condiciones de la disyunción (ser “Industrial” o “Diesel”) el porcentaje a aplicar será del 0,5%.
1.6.3. Uso de la función =SI con la función =Y Igualmente, la forma más habitual de usar la función =Y es anidada dentro de la función =SI.
Excel XP avanzado
7
Tema 1: Funciones lógicas
Ed. 3.1
Para comprenderlo, veamos un ejemplo: una empresa decide conceder primas a sus empleados en función de su sueldo y del nº de hijos que tienen, en base a la siguiente tabla:
Teniendo en cuenta la tabla anterior, el cálculo de las primas podría realizarse de la siguiente forma:
8
Excel XP avanzado
Ed. 3.1
Tema 1: Funciones lógicas
En la tabla vemos como el empleado con NIF 44444444P tiene un sueldo de 850 € y no tiene hijos. Si comprobamos en la función =SI cumple la condición “B7<1000” por lo que le corresponde una prima de 100 €.
1.7. El Formato Condicional El formato condicional es una forma especial de dar formato a las celdas y, al mismo tiempo, una forma especial de utilizar los condicionales en Excel. Consiste en aplicar a una celda un formato u otro en función de su contenido. Esto se verá en el ejemplo siguiente: Se tienen las notas de los alumnos de una clase, en cuya hoja se va a aplicar un formato condicional determinado en función de las notas obtenidas por cada alumno (Nota<5: rojo, negrita; Nota entre 5 y 7: normal; Nota entre 7 y 8,5: negrita; Nota>8,5: azul,negrita):
Para poder establecer este formato condicional (o cualquiera que queramos plantearnos) hemos de seleccionar la opción Formato Condicional del Menú Formato.
Excel XP avanzado
9
Tema 1: Funciones lógicas
Ed. 3.1
En este cuadro de diálogo se selecciona la lista desplegable a la derecha de “entre” y seleccionamos el operador deseado (por ejemplo, para “Nota<5: rojo, negrita” hemos de seleccionar la expresión “menor que”). A partir de aquí se introduce el valor sobre el que se ha de cumplir la condición (en nuestro ejemplo, introduciremos el valor 5, aunque dependiendo de cada caso podrá ser un texto, un número o una fórmula). A continuación, hemos de seleccionar el botón Formato para seleccionar la opción de “fuente” deseada (color de fuente, fuente, estilo, subrayado, tamaño… En nuestro ejemplo, seleccionaremos “color rojo y negrita”). Una vez que se utiliza el botón Aceptar del cuadro “Formato de celdas” hemos completado el primero de los formatos. Cuando existan varios formatos condicionales a aplicar utilizaremos el botón Agregar para añadir nuevos formatos (en nuestro caso para las notas entre 5 y 7, las notas entre 7 y 8,5…). Finalmente, se hace “clic” sobre el botón Aceptar para que se apliquen los formatos seleccionados. En nuestro ejemplo, el aspecto final del cuadro de diálogo formato condicional puede ser el siguiente:
Finalmente, puede comprobar como existe el botón Eliminar que podrá usar cuando quiera eliminar uno o varios formatos condicionales establecidos.
10
Excel XP avanzado
Ed. 3.1
Tema 1: Funciones lógicas
IDEAS CLAVE
•
Las Funciones Lógicas van a permitirnos que el contenido de una o varias celdas pueda tener diversos resultados dependiendo de una o varias condiciones que podemos establecer.
•
La Función =Sumar.Si (función de la categoría “Matemáticas”) permite sumar las celdas de un rango que cumplen la condición establecida. La sintaxis de la función es =SUMAR.SI(rango lectura;”criterio”;rango suma)
•
Otra función de este tipo es =Contar.Si cuya finalidad es contar aquellas celdas que cumplen con la condición establecida, siendo su sintaxis: =contar.si(rango;”condición”)
•
Ya dentro de las funciones lógicas específicas nos encontramos con la disyunción =O (verdadero ó 1 si se cumple alguna condición) y con la conjunción =Y (verdadero ó 1 si se cumplen todas las condiciones). Ambas funciones suelen utilizarse anidadas dentro de la función lógica =SI. Ambas sintaxis son similares: =O(condición1;condición2;…) ó =Y(condición1;condición2;…) pudiendo establecerse hasta 30 condiciones.
•
La función lógica más utilizada (en combinación con las anteriores o no) es la función =SI. Con esta función se puede establecer una condición y, dependiendo de que se cumpla la misma o no, dos fórmulas o resultados distintos. Además, pueden utilizarse hasta 7 condicionales anidados, combinados con =Y/=O para aumentar las posibles condiciones a establecer. Su sintaxis es la siguiente: =SI(condición;valor si verdad; valor si falso)
•
Por último, indicar que también pueden establecerse formatos condicionales. Es decir, que un formato se aplique sobre una celda o no dependiendo de que se cumpla una condición. Esto se realiza a través de la opción Formatos Condicionales del Menú Formato. A través del cuadro de diálogo que aparece pueden establecerse diversos formatos dependiendo de diversas condiciones.
Excel XP avanzado
11
Tema 1: Funciones lógicas
Ed. 3.1
AUTOEVALUACIÓN DEL TEMA 1
1. ¿Cuántos operadores diferentes se utilizan en las condiciones? a) b) c) d)
Tres. Cuatro. Cinco. Seis.
2. La función O es una: a) b) c) d)
Condición. Disyunción. Conjunción. Formato condicional.
3. La función Y es una a) b) c) d)
Condición. Disyunción. Conjunción. Formato condicional.
4. Cuando incluye una función “SI” dentro de otra función “SI” se denominan: a) b) c) d)
Enlazadas. Internas. Anidadas. Incluidas.
5. La función que permite sumar los valores del rango si se cumple la condición es: a) b) c) d)
12
=Suma =Sumar.Si =Contar.Si =Si
Excel XP avanzado
Ed. 3.1
Tema 1: Funciones lógicas
6. La función que permite contar el número de datos que cumple una condición es: a) b) c) d)
=Suma =Sumar.Si =Contar.Si =Si
7. ¿Qué número máximo de condiciones pueden establecerse en las funciones =O e =Y?: a) b) c) d)
7. 30. 15. Ninguna es correcta.
8. ¿Qué número máximo de condicionales anidados se pueden establecer (función =SI)?: a) b) c) d)
7. 30. 15. Ninguna es correcta.
9. ¿Qué número de condiciones nos encontramos en el formato condicional? a) b) c) d)
7. 30. 15. Ninguna es correcta.
10. Para establecer un Formato Condicional lo seleccionamos en el Menú: a) b) c) d)
Archivo. Edición. Insertar. Formato.
Excel XP avanzado
13
Tema 1: Funciones lógicas
Ed. 3.1
EJERCICIOS
1. Realice los ejercicios del tema que encontrará en los puntos 1.1, 1.2, 1.5 y 1.6. Cada ejercicio en una hoja distinta. Llame al nuevo Libro de trabajo “Funciones Lógicas”. 2. Un grupo de alumnos han obtenido las siguientes calificaciones:
A partir de estos datos ha de realizar las siguientes operaciones: Introduzca los datos anteriores en una hoja nueva del libro de trabajo “Funciones Lógicas”. Aplique un formato similar al que puede observar y configura la página de cara a una futura impresión de la hoja. Calcular el “Valor” para cada área de manera que: si la nota es inferior a 5 debe aparecer el texto “NM” (necesita mejorar), si la nota está entre 5 y 8 debe aparecer el texto “PA” (progresa adecuadamente) y si la nota es superior a 8 debe aparecer el texto “D” (destaca). Utilice la función =SI en combinación con =Y. Para calcular la Media por alumno (columna K) hemos de utilizar la función =Promedio.
14
Excel XP avanzado
Ed. 3.1
Tema 1: Funciones lógicas
En la columna L (Final) debe aparecer el texto “Promociona” o “No Promociona” dependiendo de que la nota media (columna K) sea igual o superior a 5 (Promociona) o sea inferior a 5 (No Promociona). Utilice la función =SI. Para las filas 17, 18 y 19 (en las columnas C, E, G, I y K) debe utilizar las funciones Promedio, Max y Min. Para las columnas numéricas (C, E, G, I y K) hemos de aplicar un formato condicional de forma que las notas inferiores a 5 aparezcan de color rojo-negrita, las notas entre 5 y 8 aparezcan en formato normal y las notas superiores a 8 aparezcan en azul-negrita. Para las columnas de texto (D, F, H, J y L) hemos de aplicar un formato condicional de forma que aparezcan en rojonegrita si el contenido es “NM” o “No Promociona”, en formato normal si el contenido es “PA” o “Promociona” y formato azul-negrita si el contenido es “D”. El resultado final, que puede imprimir, podrá ser similar al siguiente:
Excel XP avanzado
15
Tema 1: Funciones lógicas
16
Ed. 3.1
Excel XP avanzado
Ed. 3.1
Tema 2: Funciones para uso con textos
TEMA 2 FUNCIONES PARA USO CON TEXTOS 2.1. Introducción 2.2.1. Código ASCII 2.2.2. Mayúsculas y Minúsculas 2.2.3. Consulta sobre el texto 2.2.4. Extracción de textos 2.2.5. Comparación de textos 2.2.6. Concatenación de textos 2.2.7. Modificación de textos 2.2.8. Funciones de Conversión 2.2. Funciones de Texto 2.3. Funciones de Información 2.4. Funciones de Fecha y Hora 2.5. Funciones de búsqueda y referencia
2.1. Introducción No todas las funciones en Excel realizan cálculos matemáticos. A lo largo del tema vamos a estudiar varias categorías de funciones que no son fórmulas propiamente dichas (ya que no realizan cálculos directos), pero que son útiles dentro de la confección avanzada de Hojas de Cálculo. Tales categorías son: Texto, Información, Fecha y Hora, Búsqueda y Referencia. Este tipo de funciones pueden utilizarse de forma independiente pero, también, suelen utilizarse bastante en combinación con las funciones condicionales (particularmente la función =SI)
2.2. Funciones de Texto En esta categoría vamos a estudiar funciones que afectan y/o modifican directamente celdas con contenido de texto.
Excel XP avanzado
17
Tema 2: Funciones para uso con textos
Ed. 3.1
2.2.1. Código ASCII •
=CARÁCTER(número): devuelve un carácter según el número especificado (código ASCII: entre 1 y 255). Por ejemplo: al introducir la función =CARACTER(90), nos devolverá el carácter “Z”. Se trata de una función que puede ser útil en el uso de simbología que no aparece en el teclado; por ejemplo:
•
=CODIGO(celda): detecta el primer carácter de la celda e indica su código (código ASCII normalmente). Así, si en A5 aparece “Zamora” y en A6 introducimos =CODIGO(A5), el resultado será 90 (código de “Z”). Proceso inverso a la función anterior.
2.2.2. Mayúsculas y Minúsculas •
=MAYUSC(celda): convierte el texto especificado a mayúsculas. Una vez más, si en B10 introduce la función =MAYUSC(B4), Excel devolverá el resultado “JACINTO LÓPEZ GÓMEZ”.
•
=MINUSC(celda): convierte el texto especificado a minúsculas. Así, la función =MINUSC(B4) devolverá el resultado “jacinto lópez gómez”.
•
NOMPROPIO(celda): convierte a mayúsculas la primera letra de cada palabra de la celda indicada. En nuestro ejemplo, el contenido de la celda B4 ya tiene esta característica.
2.2.3. Consulta sobre el texto •
18
=LARGO(celda): mide la longitud de una cadena de texto. En nuestro ejemplo, si en B9 introducimos =LARGO(B4) nos devolverá el valor 19 que es la longitud de la cadena de caracteres (longitud
Excel XP avanzado
Ed. 3.1
Tema 2: Funciones para uso con textos
del nombre incluyendo los dos espacios en blanco). La función LARGO también suele utilizarse en combinación la función condicional =SI, controlando de esta forma que el texto introducido tenga la longitud correcta. Veamos un ejemplo:
=SI(O(LARGO(A3)<8;LARGO(A3)>9);”Error”;””) =SI(O(LARGO(E3)<10;LARGO(E3)>10);”Error”;””) =SI(O(LARGO(G3)<9;LARGO(G3)>9);”Error”;””) Tomando como ejemplo la función introducida en B3, ésta controla que el DNI no tenga ni menos de 8 caracteres ni más de 9 caracteres (es decir, que de forma correcta tenga 8 ó 9). En el caso de las celdas B4 y B6 al cumplir con el requisito correcto aparece el mensaje “Error”. •
=HALLAR(texto_buscado;dentro_del_texto;núm_inicial): busca una cadena de texto dentro de una segunda cadena de texto y devuelve el número de la posición inicial de la primera cadena desde el primer carácter de la segunda cadena de texto. Texto_buscado es el texto que desea encontrar. Puede utilizar los caracteres comodines, signo de interrogación (?) y asterisco (*) en el argumento texto_buscado. El signo de interrogación corresponde a un carácter cualquiera y el asterisco equivale a cualquier secuencia de caracteres. Si lo que desea encontrar es un asterisco o un signo de interrogación, escriba una tilde (~) antes del carácter. Dentro_del_texto es el texto en el que desea encontrar texto_buscado. Núm_inicial es el número de carácter en dentro_del_texto donde desea iniciar la búsqueda. No diferencia entre mayúsculas y minúsculas.
Excel XP avanzado
19
Tema 2: Funciones para uso con textos
•
Ed. 3.1
=BUSCAR(texto_buscado;dentro_del_texto;núm_inicial): es idéntica a la anterior sólo que ésta sí diferencia entre mayúsculas y minúsculas.
2.2.4. Extracción de textos •
=DERECHA(celda;número de caracteres): extrae, empezando por la derecha, el número de caracteres especificado de la celda indicada. Por ejemplo, si en B4 tenemos el nombre “Jacinto López Gómez” y en B5 introducimos la función =DERECHA(B4;5), ésta devolverá el valor “Gómez” (5 caracteres extraídos por la derecha)
•
=IZQUIERDA(celda;número posiciones): extrae, por la izquierda, de la celda indicada el número de posiciones indicado. De esta forma, continuando con nuestro ejemplo “Jacinto López Gómez” en B4, si introducimos en B8 la función =IZQUIERDA(B4;7) el resultado de la misma será “Jacinto” (extrae 7 caracteres comenzando por la izquierda).
•
=EXTRAE(celda;posición inicial;número caracteres): la función “Extrae” permite extraer un número de caracteres especificados a partir de una posición determinada. Siguiendo con el mismo ejemplo, si en B7 introducimos =EXTRAE(B4;9;5) nos devolverá “López” ya que hemos indicado que extraiga de la celda B4, a partir de la posición 9, 5 caracteres.
•
=REPETIR(“carácter”;nº de veces): repite el carácter indicado un número determinado de veces. Si, por ejemplo, introducimos =REPETIR(“*”;20) el resultado de aplicar la función será “********************” (repetir 20 veces el carácter “*”)
•
=REEMPLAZAR(texto_original;núm_inic;núm_de_carac teres;texto_nuevo): reemplaza parte de una cadena de texto, en función del número de caracteres que especifique, con una cadena de texto diferente. Texto_original es el texto en el que desea reemplazar algunos caracteres. Núm_inic es la posición del carácter dentro de texto_original que desea reemplazar por texto_nuevo.
20
Excel XP avanzado
Ed. 3.1
Tema 2: Funciones para uso con textos
Núm_de_caracteres es el número de caracteres de texto_ original que desea que REEMPLAZAR sustituya por texto_nuevo. Núm_bytes es el número de bytes de texto_original que desea que REEMPLAZARB reemplace por texto_nuevo. Texto_nuevo es el texto que reemplazará los caracteres de texto_original. Como ejemplo, suponer que la celda A2=abcdefghijk, A3=2009 y la celda A4=123456. Si se tiene: =REEMPLAZAR(A2;6;5;”*”) , reemplaza cinco caracteres a partir del sexto carácter, por tanto sería:(abcde*k). Se tiene ahora: =REEMPLAZAR(A3;3;2;”10”) el resultado sería:(2010). •
=SUSTITUIR(texto;texto_original;texto_nuevo; núm_de _ocurrencia): sustituye texto_nuevo por texto_original dentro de una cadena de texto. Texto es el texto o la referencia a una celda que contiene texto en el que desea sustituir caracteres. Texto_original es el texto que desea reemplazar. Texto_nuevo es el texto por el que desea reemplazar texto_original. Núm_de_ocurrencia especifica la instancia de texto_original que desea reemplazar por texto_nuevo. Si especifica el argumento núm_de_ocurrencia, sólo se remplazará esa instancia de texto_original. De lo contrario, todas las instancias de texto _original en texto se sustituirán con texto_nuevo. Suponga: A2=datos de ventas, A3=trimestre 1, 2008 y A4= trimestre1, 2011. La función:=SUSTITUIR(A2;”ventas”;”costo”) dará como resultado:A2=datos de costo.
•
=FONETICO(referencia): extrae los caracteres fonéticos de una cadena de texto.
Excel XP avanzado
21
Tema 2: Funciones para uso con textos
Ed. 3.1
2.2.5. Comparación de textos •
=IGUAL(texto1;texto2): compara dos valores de texto para ver si son iguales. Devuelve dos posibles resultados: “VERDADERO” si son iguales, y “FALSO” si no lo son. Utilizada en combinación con la función condicional =SI puede controlar determinado tipo de errores. Por ejemplo:
=SI(igual(c2;d2);”Error en puerto”;””)
Si son iguales los contenidos de C2 y D2 se mostrará el texto “Error en puerto”, mientras que si son diferentes quedará la celda en blanco (para ello se utiliza la expresión “”)
2.2.6. Concatenación de textos •
=CONCATENAR(texto1;texto2;...): une el contenido de dos o más celdas de texto. Así, por ejemplo, si B4 contiene la cadena “Buenos” y B5 contiene la cadena “días”, al aplicar la función =CONCATENAR(B4;B5) obtendremos el resultado “Buenosdías”. Puede tener cierta utilidad cuando se unen distintas celdas que contienen el nombre completo de una persona (nombre, apellido 1, apellido 2), celdas que contienen distintas partes de un código (por ejemplo, DNI por un lado y letra del NIF por otro)…
2.2.7. Modificación de textos •
22
=ESPACIOS(celda): elimina los espacios en blanco de la celda especificada. Así, continuando con el ejemplo anterior, si en B6 introducimos =ESPACIOS(B4), esta función devolverá el valor “JacintoLópezGómez”. Su utilidad puede estar en unir determinados códigos que no deben contener espacios en blanco.
Excel XP avanzado
Ed. 3.1
Tema 2: Funciones para uso con textos
•
=LIMPIAR(texto):Texto es cualquier información en una hoja de cálculo de la que desea quitar caracteres no imprimibles.
2.2.8. Funciones de Conversión •
=TEXTO(valor;formato): convierte un valor en texto, con un formato numérico específico.
•
=VALOR(texto): convierte una cadena de texto que representa un número en el número que representa. Ejemplo: =VALOR (“16:48:00”) es el número de serie equivalente a 4 horas y 48 minutos. Para ver el número como una hora, seleccione la celda y haga clic en Celdas en el menú Formato. Haga clic en la ficha Número y, a continuación, en Hora en el cuadro Categoría.
•
=DECIMAL(número;decimales;no_separar_millares): Redondea un número al número de decimales especificado, da formato al número con el formato decimal usando comas y puntos, y devuelve el resultado como texto. Número es el número que desea redondear y convertir en texto. Decimales es el número de dígitos a la derecha del separador decimal. No_separar_millares es un valor lógico que, si es VERDADERO, impide que DECIMAL incluya un separador de millares en el texto devuelto. Ejemplo: A2=1234.567, se tiene:=DECIMAL(A2;1) que dará:(1234.6)
•
=MONEDA(número;núm_de_decimales): convierte un número a texto usando formato de moneda.
2.3. Funciones de Información Este tipo de funciones muestran información sobre el contenido de una celda (si es un texto, si está en blanco...). Suelen utilizarse a menudo en combinación con la función =SI por lo que su uso está enfocado hacia el control de errores.
Excel XP avanzado
23
Tema 2: Funciones para uso con textos
Ed. 3.1
•
=ESBLANCO(celda): devuelve el valor VERDADERO si la celda a la que se hace referencia está en blanco y devuelve el valor FALSO, si la celda no lo está.
•
=ESERROR(celda): devuelve el valor VERDADERO si la celda a la que se hace referencia produce un error (por ejemplo, si una fórmula se ha creado de forma incorrecta) y devuelve el valor FALSO, si la celda es correcta.
•
=ESNUMERO(celda): devuelve el valor VERDADERO si la celda contiene un número y FALSO, si la celda contiene texto o está en blanco.
•
=ESTEXTO(celda): devuelve el valor VERDADERO si la celda contiene un texto y FALSO, si la celda contiene un número. Todas estas funciones normalmente se anidan dentro de la función lógica =SI, ya que de lo contrario suelen carecer de sentido. Veamos un ejemplo:
=SI(O(ESBLANCO(B6);ESTEXTO(B6));"Error en Ventas";"")
=D6*B6/100
=SI(O(ESERROR(E6);ESBLANCO(E6));"Error";"")
En el ejemplo anterior, la columna “Control ventas” controla que la entrada de “Ventas” sea un número (así, por ejemplo, en la celda B4 ha detectado un error al introducir un carácter en un número). La columna “Control IVA” controla al mismo tiempo que “Tipo IVA” no esté en blanco o que la fórmula no produzca un error, como en los casos de D5 y E4)
24
Excel XP avanzado
Ed. 3.1
Tema 2: Funciones para uso con textos
Como puede comprobar este grupo de funciones pueden ser muy útiles para el control y gestión de nuestras hojas de cálculo, especialmente si se combinan con una función condicional. •
=INFO(“tipo”): devuelve información acerca del entorno en uso (versión, sistema operativo, memoria…). “Tipo” puede ser: =INFO(“archivos”): devuelve el número de hojas activas en los libros abiertos. =INFO(“directorio”): devuelve la ruta de acceso (disco y carpeta/s) actual. =INFO(“memdisp”): devuelve la memoria disponible (en bytes) =INFO(“memtot”): devuelve la memoria total (en bytes) =INFO(“memusada”): devuelve la memoria en uso (en bytes) =INFO(“actual”): devuelve el método de recálculo en uso (automático o manual) =INFO(“sistema”): entorno operativo (pcdos o mac) =INFO(“version”): versión en uso de Microsoft Excel. =INFO(“versionso”): versión del sistema operativo en uso (Windows…)
2.4. Funciones de Fecha y Hora Las funciones de Fecha y Hoja son, en realidad, un tipo de funciones numéricas ya que para Excel estos dos conceptos son números (un número interno, aunque en pantalla suela aparecer con el formato deseado de fecha u hora). Las funciones más importantes de esta categoría son: •
=AHORA(): inserta la fecha y la hora actual (del sistema). En aquellas hojas donde nos interese que aparezca la fecha y hora del sistema introduciremos la función de la siguiente forma:
Excel XP avanzado
25
Tema 2: Funciones para uso con textos
Ed. 3.1
=AHORA(). Su uso se trata de algo bastante habitual ya que es necesario, en muchas ocasiones, que aparezca la fecha y la hora junto a la hoja a la hora de visualizarla y, sobre todo, de imprimirla. •
=DIAS360(fecha inicial;fecha final): permite calcular el número de días entre dos fechas, tomando como base el año comercial de 360 días. Se trata de una función de bastante interés ya que varias operaciones comerciales con Hojas de Cálculo se realizan en base al número de días que transcurre (como ocurre en las letras de cambio, préstamos personales…) Veamos un ejemplo aplicable a la gestión de letras de cambio:
=DIAS360(C8;D8)
El cálculo de fechas tomando como base el año natural (365 días) se realiza con una sencilla operación matemática: restando a la fecha de vencimiento, la fecha de libramiento (por ejemplo, si aplicamos la fórmula =D8-C8 el resultado será 31, ya que Marzo tiene 31 días en el año natural). Si aparece una fecha en lugar de un valor numérico, bastará con que, en la opción celda del Menú Formato, aplique un formato numérico correcto. •
26
=HOY(): inserta la fecha actual (fecha del sistema). En aquellas hojas donde nos interese que aparezca la fecha del sistema introduciremos la función de la siguiente forma: =HOY()
Excel XP avanzado
Ed. 3.1
Tema 2: Funciones para uso con textos
2.5. Funciones de búsqueda y referencia Este tipo de funciones permite visualizar información de una hoja de cálculo que se esté usando como una tabla. •
=BUSCARV(Valor buscado;matriz/rango;Nº Columna;orden): devuelve texto o valor de una tabla según el valor buscado. Veamos un ejemplo, donde podemos localizar los datos de un libro a través de su referencia:
=BUSCARV(B3;LISTA;2;FALSO) =BUSCARV(B3;LISTA;3;FALSO) =BUSCARV(B3;LISTA;4;FALSO)
(siendo LISTA el rango D1:G11) Esta función puede ser de gran utilidad cuando se utiliza Excel como una Base de Datos. En este tipo de libros se hace necesaria la búsqueda de información de una forma rápida y eficaz (la opción “Buscar” del menú “Edición” no es suficiente y, mucho menos, la búsqueda manual). En este ejemplo, basta con introducir la “Referencia” del libro para que automáticamente Excel muestre el título del libro (a través de la función “Buscarv” que busca la referencia en el rango “lista” y una vez localizada muestra el contenido de dicha fila en la columna número 2 del rango), el autor (columna número 3), el precio (columna número
Excel XP avanzado
27
Tema 2: Funciones para uso con textos
Ed. 3.1
4), el IVA (calculando el 4% del precio) y el PVP (sumando Precio+IVA). La estructura que tiene el ejemplo aquí tiene más que ver con ahorro de espacio. Puede, si lo desea, crear un “cuadro de búsqueda” (por ejemplo “Consulta por Referencia”) en una hoja de cálculo y una lista (base de datos) en otra hoja de cálculo del mismo libro. Un paso necesario será nombre toda la base de datos (lista) con un nombre de rango (Menú “Insertar”, Opción “Nombre”, Opción “Definir”) •
=COINCIDIR(Valor buscado;Matriz buscada): devuelve la posición (número de fila) de un valor de una matriz que coincida con el valor buscado. Se trata de otra de las funciones más útiles del tema ya que permite localizar información de forma sencilla (en este caso, localizando el número de fila donde se encuentra el valor buscado): El siguiente ejemplo, nos permitirá conocer la posición (nº fila) de una empresa para poder conocer su teléfono o fax:
=COINCIDIR(F3;A1:A7;0)
En este ejemplo se busca el contenido de F3 (“Venus”) en el rango de datos A1:A7 (también podría definirse el rango con un nombre de rango como en el ejemplo anterior), por lo que Excel devuelve el valor 6 (número de fila del rango y/o de la hoja de cálculo donde se encuentra el dato buscado). Bastaría en ese momento con visualizar la fila 6 para poder comprobar empresa, teléfono y fax. •
28
=HIPERVINCULO(“ruta”;”descripción”): esta función tiene como utilidad crear un vínculo de acceso directo a un fichero del
Excel XP avanzado
Ed. 3.1
Tema 2: Funciones para uso con textos
disco duro (de cualquier tipo: Excel o cualquier otra aplicación), de un servidor de red o de un servidor de Internet. Una creado el hipervínculo para “ejecutarlo” (acceder al fichero deseado) bastará con hacer “clic” sobre el mismo (que aparece de color azul y subrayado). Algunos ejemplos de Hipervínculos pueden ser: =hipervinculo(“c:\facturas 06\Resumen facturas 06.xls”;”Abrir Resumen 06”): abre el libro de Excel “Resumen facturas 06” que se encuentra en la carpeta “facturas 06” del disco duro “C”. El hipervínculo aparece en la celda como Abrir Resumen 06 (en azul) =hipervinculo(“\\Finanzas\Facturas\trim1.xls”;”Fras. Trim1”): abre el libro de Excel “trim1.xls” que se encuentra en la carpeta “Facturas” del servidor de red “Finanzas”). El Hipevículo aparece en la celda como Fras. Trim1 (en azul) =hipervinculo(“http://www.larepera.com/cartas/carta modelo.doc”;”Carta”): abre el documento “carta modelo. doc” en la carpeta “cartas” de la URL (dirección web) “http://www.larepera.com”. El Hipevículo aparece en la celda como Carta (en azul)
Excel XP avanzado
29
Tema 2: Funciones para uso con textos
Ed. 3.1
IDEAS CLAVE
30
•
Las funciones que vamos a tratar en este tema son funciones de Excel que no implican directamente un cálculo, al contrario que las funciones estudiadas en temas anteriores.
•
En primer lugar, nos encontramos con las Funciones de Texto que trabajan directamente con celdas que contienen un texto. Así, podemos realizar con ellas diversas operaciones tales como: extraer datos por la izquierda, extraer datos por la derecha, convertir a mayúsculas, convertir a minúsculas, contar el número de caracteres del texto, comparar dos textos…
•
Las Funciones de Información suelen utilizarse en combinación con la función condicional =SI para lograr el efecto deseado (su finalidad principal está en servir como funciones de control y detección de errores). Así estas funciones permiten detectar celdas que contienen un error (=eserror), celdas que están en blanco (=esblanco), celdas que contienen un número (=esnumero), celdas que contienen un texto (=estexto)… y realizar, en combinación con =SI, determinadas operaciones o mostrar determinados mensajes de aviso.
•
Las Funciones de Fecha y Hora trabajan específicamente con este tipo de datos. Así, podemos contar el número de días (según el año comercial) entre dos fechas con la función =dias360, visualizar la fecha del sistema y/o la hora del sistema (funciones =ahora e =hoy)
•
Las Funciones de Búsqueda y Referencia permiten básicamente la localización de información dentro de listas (bases de datos) en Excel. Así, la función =Buscarv permite localizar y presentar en pantalla datos buscados a través de esta función, mientras que la función =Coincidir muestra el número de fila donde se encuentra el contenido buscado. La función =Hipervinculo permite crear un acceso directo a un fichero que esté en nuestro disco, en otro ordenador o en una dirección URL (Internet)
Excel XP avanzado
Ed. 3.1
Tema 2: Funciones para uso con textos
AUTOEVALUACIÓN DEL TEMA 2
1. La función que permite mostrar información del sistema es: a) b) c) d)
=ahora. =hoy. =info. Ninguna es correcta.
2. La función que muestra la fecha y la hora de sistema es: a) b) c) d)
=ahora. =hoy. =info. Ninguna es correcta.
3. Para calcular la diferencia numérica entre dos fechas por el año natural: a) b) c) d)
Usamos la función =dias360. Restamos directamente las fechas. Usamos la función =ahora. Ninguna es correcta.
4. Para calcular la diferencia numérica entre dos fechas por el año comercial: a) b) c) d)
Usamos la función =dias360. Restamos directamente las fechas. Usamos la función =ahora. Ninguna es correcta.
5. La función =hipervinculo permite crear un acceso directo a: a) b) c) d)
Un fichero dentro del mismo disco. Un fichero en otro ordenador de la red. Un fichero de una dirección URL. Todas son correctas.
Excel XP avanzado
31
Tema 2: Funciones para uso con textos
Ed. 3.1
6. La función que nos permite localizar y presentar en pantalla un dato a partir del contenido de una celda es: a) b) c) d)
=Info. =Coincidir. =Buscarv. =Hipervinculo.
7. La función que nos presenta el número de fila donde se encuentra el dato buscado es: a) b) c) d)
=Info. =Coincidir. =Buscarv. =Hipervinculo.
8. La función que permite medir la longitud de un texto es: a) b) c) d)
=largo. =longitud. =info. Ninguna es correcta.
9. Para comparar dos textos tenemos la función: a) b) c) d)
=concatenar. =igual. =largo. =info.
10. Una función que permite controlar si una celda contiene un determinado contenido es: a) b) c) d)
32
=esblanco. =estexto. =esnumero. Todas son correctas.
Excel XP avanzado
Ed. 3.1
Tema 2: Funciones para uso con textos
EJERCICIOS
1. Realice en hojas diferentes los ejemplos de los puntos 2.1, 2.2, 2.3 y 2.4. Guárdelos en el libro de trabajo “Funciones sin cálculos”.
2. Cree un nuevo Libro de Trabajo llamado LIBRERÍA con las siguientes Hojas de Cálculo: Datos, Consulta 1 y Consulta 2. En la hoja Datos, introduzca la siguiente información (teniendo en cuenta que el IVA y el PVP se calculan mediante fórmula). Parte de los datos entre las filas 2 y 11 (código, título y autor) puede obtenerlos mediante “copiar y pegar” desde la hoja correspondiente del libro “Funciones sin cálculos”:
La columna “Iva €” calcula el 4% de Iva (el aplicable a libros en nuestro país) sobre el precio, mientras que la columna “PVP” calcula el precio de venta al público (suma del precio + Iva €).
En la hoja Consulta 1 realice el siguiente trabajo (teniendo en cuenta que Título, Autor, Editorial, Precio, Iva € y PVP han de “calcularse” mediante la función =BUSCARV utilizando la base de datos de la hoja Datos.
Excel XP avanzado
33
Tema 2: Funciones para uso con textos
Ed. 3.1
En la hoja Consulta 2 hemos de crear la siguiente información:
El número de posición (fila de esta obra en la hoja Datos) hemos de “calcularlo” utilizando la función COINCIDIR.
34
Excel XP avanzado
Ed. 3.1
Tema 3: Los objetos en Excel
TEMA 3 LOS OBJETOS EN EXCEL
3.1. Concepto de Objeto. Utilidad 3.2. Insertar Imágenes: prediseñadas, desde archivo y desde escáner/cámara 3.3. Imágenes WordArt 3.4. Diagramas y Organigramas 3.5. Creación de Ecuaciones 3.6. Barra de Herramientas Dibujo 3.7. Insertar símbolos y comentarios
3.1. Concepto de Objeto. Utilidad Los Objetos son elementos (imágenes, gráficos…) que se insertan en las Hojas de Cálculo, se guardan con el resto de datos, pero siempre son elementos independientes y superpuestos sobre la Hoja. Por otro lado hemos de indicar que los Objetos no son elementos exclusivos de Excel, sino que las aplicaciones con las que los creamos o insertamos se encuentran en todas las aplicaciones de Microsoft Office (de tal forma pueden insertarse objetos en Microsoft Word, Microsoft Access, Microsoft Excel…) A lo largo del tema vamos a estudiar todos aquellos tipos de Objetos que sirven para mejorar la presentación de una Hoja de Cálculo: Imágenes, Rótulos, Organigramas, Ecuaciones… Por otro lado, en el próximo tema, estudiaremos el tipo de objeto más adecuado para Excel y que permite presentar la información de una manera más clara: los Gráficos.
3.2. Insertar Imágenes: prediseñadas, desde archivo y desde escáner/cámara Las Imágenes incluyen un amplio abanico de objetos (fotografías, dibujos, logotipos, imágenes escaneadas…) que se utilizan, fundamentalmente, para mejorar la presentación de las Hojas de Cálculo.
Excel XP avanzado
35
Tema 3: Los objetos en Excel
Ed. 3.1
3.2.1. Imágenes Prediseñadas Microsoft Office contiene una amplia galería de imágenes para ser utilizadas desde cualquiera de las aplicaciones (Word, Excel…). Para utilizarlas ha de desplegar el Menú Insertar, seleccionar la opción Imagen y la subopción Imágenes Prediseñadas o hacer “clic” sobre el botón Insertar Imagen Prediseñada de la Barra de Herramientas Dibujo. Existe una amplia gama de imágenes que puede insertar con diversos formatos (.jpg, .gif, .bmp, .wmf…). En esta ventana (que aparece normalmente en el extremo derecho de la ventana de trabajo de Excel) hemos de seleccionar la lista desplegable “Buscar en” (donde seleccionaremos las ubicaciones donde localizar las imágenes en nuestro PC). Por otro lado, hemos de seleccionar también la lista desplegable “Los resultados deben ser” donde escogeremos los tipos de imágenes que queremos mostrar en los resultados. Finalmente, hacemos “clic” sobre el botón Buscar. Aparece ya un listado de imágenes en pequeño tamaño (puede observarlas a través de la correspondiente Barra de desplazamiento). Para seleccionar una ha de hacer “clic” sobre la lista desplegable de la imagen en cuestión y, en el menú desplegable que aparece, seleccionar la opción Insertar. La imagen seleccionada aparecerá insertada sobre la Hoja de Cálculo:
Al quedar la imagen insertada sobre la hoja podrá comprobar como aparecen unas marcas (círculos) alrededor de la misma (8 en total). Estas
36
Excel XP avanzado
Ed. 3.1
Tema 3: Los objetos en Excel
marcas, que podemos llamar “puntos de anclaje” (aparecen con sólo hacer “clic” sobre la imagen), sirven para: a) cambiar de tamaño la imagen (haciendo “clic” sobre el mismo –forma de doble flecha- y arrastrar); b) eliminar la imagen (pulsando la tecla SUPR); c) cambiar de posición la imagen (haciendo “clic” dentro de la imagen y arrastrándola a la nueva posición). Indicar, por último, que todas las opciones estudiadas para las imágenes (arrastrar, mover, eliminar…) son también válidas para el resto de objetos que se van a desarrollar a continuación tanto en este tema como en el siguiente al tratar con los gráficos. 3.2.2. Imágenes desde Archivo Una opción diferente de insertar imágenes es insertando directamente el archivo de la misma (.jpg, .gif…). Para ello, debe desplegar el Menú Insertar, seleccionar la opción Imagen y la subopción Desde Archivo o hacer “clic” sobre el botón “Insertar imagen desde archivo” de la Barra de Herramientas Dibujo. Nos encontramos con el siguiente cuadro de diálogo:
Excel XP avanzado
37
Tema 3: Los objetos en Excel
Ed. 3.1
Por defecto, muestra la carpeta “Mis imágenes” donde es posible que tenga almacenados diversos ficheros con este formato. No obstante Microsoft Office tiene una galería de imágenes amplia que depende, en parte, de la instalación inicial de la Suite. Para acceder a estas galerías ha de seleccionar sucesivamente la siguiente ruta en “Buscar en”: C:\Archivos de Programa\Microsoft Office\ClipArt. Dentro de la carpeta ClipArt encontrará, a su vez, diversas carpetas con imágenes para insertar en su Hoja de Cálculo. El aspecto final de una imagen insertada en una Hoja es idéntico al de las imágenes insertadas mediante la opción “Imágenes Prediseñadas”.
3.2.3. Insertar imagen desde Escáner/Cámara Con esta opción Excel abre la posibilidad de insertar imágenes que no están en ese momento en nuestro PC, sino que se encuentran en papel preparadas para ser escaneadas (utilizando un Escáner) o para ser transferidas desde una cámara digital hasta el ordenador.
Para ello, ha de seleccionar nuevamente el Menú Insertar, la opción Imagen y, finalmente, la subopción “Desde escáner o cámara…”. Una vez insertada una imagen (por cualquiera de los tres métodos anteriores) y siempre que la tenga seleccionada (que aparezcan los puntos de anclaje) aparece, al mismo tiempo en la pantalla, la Barra de Herramientas Imagen que permite realizar diversas operaciones de modificación y mejora de la Imagen insertada:
38
Excel XP avanzado
Ed. 3.1
Tema 3: Los objetos en Excel
3.3. Imágenes WordArt Con WordArt Microsot Office permite insertar texto con un formato de imagen, permitiendo diversos tipos de fuentes, tamaños y, sobre todo, lo que más caracteriza a esta aplicación: la creación de textos con orientaciones no convencionales (textos formando curvas, textos formando figuras geométricas…) Para crear un objeto de este tipo hemos de hacer “clic” sobre el botón “Insertar WordArt” de la Barra de Herramientas Dibujo o seleccionar el Menú Insertar, la opción Imagen y la subopción WordArt. En ambos casos obtiene el siguiente cuadro de diálogo:
Excel XP avanzado
39
Tema 3: Los objetos en Excel
Ed. 3.1
En el mismo, en primer lugar, seleccionar el formato del texto WordArt que desea crear/insertar en el documento. Una vez seleccionado el formato deseado, ha de hacer “clic” sobre el botón Aceptar. En el siguiente cuadro de diálogo hemos de escribir el texto deseado para la nueva imagen a insertar. Además, en la misma ventana, podemos seleccionar la Fuente deseada, el Tamaño de fuente y los estilos Negrita y cursiva: Finalmente, haga “clic” sobre el botón Aceptar para insertar la imagen-texto creada sobre la Hoja de Cálculo. Con esta imagen se pueden realizar las siguientes operaciones: modificar tamaño, eliminar, mover de posición…). Por ejemplo:
Mientras la imagen WordArt aparece en la Hoja con los “puntos de anclaje” podemos ver una nueva Barra de Herramientas, denominada WordArt. A través de la misma podemos obtener diversas formas de modificar y/o mejorar la imagen insertada:
40
Excel XP avanzado
Ed. 3.1
Tema 3: Los objetos en Excel
3.4. Diagramas y Organigramas 3.4.1. Diagramas Otra opción de interés, para estructurar determinados tipos de información en la Hoja de Cálculo, es la que se obtiene a través de la opción Diagrama del Menú Insertar o haciendo “clic” sobre el botón Insertar Diagrama u Organigrama de la Barra de Herramientas Dibujo. Los Diagramas y los Organigramas se utilizan para presentar información estructurada útil en situaciones tales como: estructuras organizativas de empresas, explicación de procesos de producción, explicación de campañas de marketing, etc. Una vez seleccionada la opción nos encontramos con el siguiente cuadro de diálogo:
Los botones que aparecen en la ventana son los siguientes (de izquierda a derecha): Organigrama (relaciones jerárquicas), Diagrama de Ciclo (ciclos continuos), Diagrama Radial (relaciones con un elemento fundamental), Diagrama Piramidal (relaciones en pirámide), Diagrama de Venn (para mostrar datos superpuestos), Diagrama de Círculos Concéntricos (planteamiento de pasos para lograr un objetivo)
Excel XP avanzado
41
Tema 3: Los objetos en Excel
Ed. 3.1
Una vez seleccionado el Diagrama deseado hemos de hacer “clic” sobre el botón Aceptar para insertar el mismo sobre la Hoja de Cálculo:
Aunque como se ha explicado anteriormente cada uno tiene una utilidad distinta, la dinámica de trabajo con los distintos Diagramas es similar. En primer lugar se introduce el contenido que va a tener el Diagrama (el texto) haciendo “clic” sobre cada zona donde vayamos a introducir un dato. A continuación, al igual que en los objetos anteriores, podemos realizar diversas modificaciones y/o mejoras a través de la Barra de Herramientas Diagrama que aparece cuando tenemos seleccionado el mismo (puntos de anclaje):
42
Excel XP avanzado
Ed. 3.1
Tema 3: Los objetos en Excel
Dos ejemplos de Diagramas finalizados podrían ser los siguientes:
3.4.2. Organigramas El Organigrama es uno de los Diagramas más utilizados ya que su estructura jerárquica permite introducir información diversa. Los organigramas son fundamentales en la creación de programas realizados con cualquier lenguaje de programación estructurada. Para crearlo, puede hacerlo de forma similar al punto anterior (eligiendo el tipo “Organigrama” en la Ventana “Galería de Diagramas”). También puede hacerlo a través de la subopción Organigrama, de la opción Imagen del Menú Insertar. El aspecto que presentará es el siguiente:
En cada “casilla” del organigrama podrá introducir el texto oportuno y, a través de la Barra de Herramientas Organigrama, podrá modificar
Excel XP avanzado
43
Tema 3: Los objetos en Excel
Ed. 3.1
y/o mejorar la presentación del Organigrama (insertando casillas, cambiando fuentes…):
Un ejemplo de Organigrama finalizado puede ser el siguiente:
3.5. Creación de Ecuaciones Un tipo de objeto especial que tiene Excel son las ecuaciones. No se trata de resolver operaciones de este tipo (para eso ya tenemos las funciones en Excel), sino de poder escribir fórmulas Matemáticas, Estadísticas… utilizando la simbología necesaria que, en muchas ocasiones, no sería posible hacer ya que se requiere un uso de simbología especial (por ejemplo, el signo de raíz cuadrada) que no se encuentra en el teclado.
44
Excel XP avanzado
Ed. 3.1
Tema 3: Los objetos en Excel
Esta aplicación se denomina Editor de Ecuaciones y puede ejecutarse desplegando el Menú Insertar, y seleccionando la opción Objeto. En el cuadro de diálogo que aparece seleccionaremos Microsoft Editor de Ecuaciones 3.0.
Se tiene la siguiente barra de símbolos, denominada de Herramientas Ecuación:
En ella, combinando el uso del teclado y de los botones específicos que incluyen numerosa simbología matemática, podremos crear, por ejemplo, la fórmula correspondiente a la ecuación de 2º grado:
A la hora de crear la fórmula anterior hay símbolos (como “x”, “b”…) que puede obtener directamente en el teclado, mientras que hay otros (raíz cuadrada, línea de división…) que podrá obtener a través de los botones correspondientes de la Barra de Herramientas Ecuación.
Excel XP avanzado
45
Tema 3: Los objetos en Excel
Ed. 3.1
3.6. Barra de Herramientas Dibujo Ya hemos utilizado la Barra de Herramientas Dibujo para diversas tareas en este tema (Imágenes, Diagramas…), pero la misma tiene diversas opciones para crear/dibujar diversos objetos tales como autoformas (figuras geométricas y similares, flechas…). Para activarla hemos de hacer “clic” sobre el botón “Dibujo” de la Barra de Herramientas Estándar (o seleccionando el Menú Ver, opción Barras de Herramientas, subopción Dibujo).
A través de esta Barra, podemos realizar diversas operaciones de dibujo tales como:
•
Autoformas: el despliegue de este botón permite acceder a la creación de multitud de objetos tales como líneas (flechas, curvas…), Conectores (flechas con conectores), Formas básicas (figuras geométricas, llaves…), flechas de bloque, diagramas de flujo (simbología para la creación de organigramas), cintas y estrellas (cuadros de texto con formas de cintas y estrellas en los que se puede escribir), llamadas (creació de diversos tipos de llamadas, también conocidas como “bocadillos”). Para crearlos, una vez seleccionado, hemos de hacer “clic” y arrastrar el ratón hasta que tengan el tamaño deseado. Por último, indicar que la mayor parte de ellos (formas básicas, diagramas, cintas y estrellas, llamadas…) puede incorporar texto haciendo “clic” con el botón derecho del ratón sobre el objeto creado y seleccionando la opción Agregar Texto.
46
Excel XP avanzado
Ed. 3.1
Tema 3: Los objetos en Excel
•
Cuadro de texto: con este botón creamos un objeto de características similares a las autoformas (en cuanto a creación y posibilidad de texto), pero con una forma determinada (cuadrado o rectángulo). En el mismo, como su nombre indica, suele introducirse texto.
•
Botones de creación de objetos varios: líneas, flechas, rectángulos y elipses.
•
Botones de modificación y/o mejora: estos botones sirven para mejorar el aspecto de los objetos creados desde la Barra de Dibujo (incluso los creados desde otras opciones del programa). Nos encontramos con botones que nos permiten seleccionar Colores (color de relleno, color de línea, color de fuente), Estilos de Líneas (líneas, guiones, flechas) y Estilos Especiales (estilos de sombra y estilos 3D)
Al finalizar la creación de cualquier tipo de objeto con la Barra de Herramientas Dibujo puede realizar sobre los mismos cualquier tipo de operación ya estudiada con anterioridad (puntos de anclaje, cambio de tamaño, eliminación…). Además, si hacemos un doble “clic” sobre cualquiera de estos objetos Excel nos muestra un cuadro de diálogo, con varias pestañas, donde podremos seleccionar opciones de modificación o mejora similares a las ya estudiadas:
Excel XP avanzado
47
Tema 3: Los objetos en Excel
Ed. 3.1
Algunos objetos creados con la Barra de Herramientas Dibujo podrían ser estos:
3.7. Insertar símbolos y comentarios Los dos últimos conceptos que vamos a estudiar en el tema no pueden considerarse objetos en sí mismos, aunque sí cumplen una función similar.
3.7.1. Insertar símbolos Esta opción permite insertar diversos tipos de caracteres: tipos especiales de letras, alfabetos de otras lenguas, pequeños dibujos, símbolos especiales (©, ®, £, ?, ?, ?…), etc. Puede obtenerse dicha opción a través del Menú Insertar, Opción Símbolos. Nos encontraremos con el siguiente cuadro de diálogo:
En el mismo puede seleccionar la Fuente deseada (dos fuentes con bastantes símbolos son: Wingdings y Webdings, aunque en cualquier fuente puede localizar símbolos especiales). Una vez localizado y seleccionado el símbolo deseado hemos de hacer “clic” sobre el botón Insertar. Podemos insertar todos los símbolos que necesitemos para, finalmente, hacer “clic” sobre el botón Cerrar.
48
Excel XP avanzado
Ed. 3.1
Tema 3: Los objetos en Excel
3.7.2. Insertar Comentarios Los Comentarios permiten añadir información al contenido de una celda sin ocupar realmente espacio. Para utilizar esta opción hemos de desplegar el Menú Insertar y seleccionar la opción Comentario. A partir de este momento nos encontramos con un cuadro de texto (fondo amarillo por defecto) donde introduciremos el texto deseado. Finalmente, haremos “clic” fuera del Comentario para que se inserte realmente.
Una vez introducido el comentario comprobará como, en la esquina superior derecha de la celda afectada, existe una pequeña “marca” de color rojo para indicarnos que en dicha celda hay insertado un comentario. Bastará hacer “clic” sobre dicha posición para que aparezca el texto introducido. Finalmente, indicar que para modificar, eliminar… el comentario hacemos “clic” con el botón derecho del ratón sobre la celda afectada para que aparezca el Menú Contextual correspondiente, donde podremos seleccionar opciones tales como Modificar Comentario, Eliminar Comentario o Mostrar/Ocultar Comentario.
Excel XP avanzado
49
Tema 3: Los objetos en Excel
Ed. 3.1
IDEAS CLAVE
50
•
Los Objetos son elementos que se Insertan en la Hoja de Cálculo, se guardan con ella, pero no forman parte de la misma (se trata de elementos independientes)
•
Un tipo de objeto importante son las Imágenes que podemos obtener a través del Menú Insertar, opción Imagen o a través de los botones correspondientes de la Barra de Herramientas Dibujo. Éstas se pueden Insertar como Imágenes Prediseñadas (seleccionándola de una galería de imágenes específica de Microsoft Office u obtenida de otra carpeta, sitio de red o Internet), Imágenes desde Archivo (insertando sobre la Hoja de Cálculo un fichero de imagen -.jpg, .gif…- seleccionado), Imágenes desde Escáner o Cámara Digital (obteniendo la imagen desde uno de estos dos dispositivos externos)
•
Otro tipo de objeto son los Rótulos WordArt. Se trata de textos con formas y orientaciones especiales que se obtienen desde la opción Imagen del Menú Insertar o desde el botón correspondiente de la Barra de Herramientas Dibujo.
•
Los Diagramas y los Organigramas son objetos que permiten presentar información textual de forma estructurada y ordenada y pueden obtenerse a través de la opción Diagrama del Menú Insertar o a través de la Barra de Herramientas Dibujo. Así, nos encontramos con la posibilidad de presentar información como un Organigrama jerárquico, como Diagramas de Venn, como Diagramas Radiales…
•
La opción Editor de Ecuaciones, que podemos obtener a través del Menú Insertar y la opción Objeto, no permite cálculos matemáticos (para eso tenemos las fórmulas y las funciones), sino que permite crear fórmulas (con simbología matemática que no se encuentra en el teclado) que, de otra forma, no sería posible.
•
Otra serie de objetos pueden crearse a partir de la Barra de Herramientas Dibujo. A través de la misma podemos crear Autoformas (formas geométricas, flechas…), Cuadros de Texto, Figuras diversas (elipse, rectángulo, línea…)… y modificar
Excel XP avanzado
Ed. 3.1
Tema 3: Los objetos en Excel
o mejorar el formato de los objetos (Color de Relleno, Color de Línea, Color de Fuente, Estilo de sombra, Estilo 3D…) •
Por último nos encontramos con dos opciones, que no son objetos propiamente dichos, pero que tienen su misma finalidad. Se trata de la opción Símbolos del Menú Insertar (permite insertar símbolos especiales, caracteres de otros alfabetos…) y de la opción Comentarios del Menú Insertar (para añadir un comentario, explicación… a la celda seleccionada)
Excel XP avanzado
51
Tema 3: Los objetos en Excel
Ed. 3.1
AUTOEVALUACIÓN DEL TEMA 3 1. Los Objetos se pueden utilizar: a) b) c) d)
Solamente desde Excel. Desde todas las aplicaciones de Microsoft Office. Solamente desde Word. Ninguna es correcta.
2. Las Imágenes Prediseñadas: a) b) c) d)
Se insertan desde una Galería de Imágenes. Se insertan como un archivo .jpg o .gif. Se insertan desde un dispositivo externo. Todas son correctas.
3. Las imágenes insertadas “Desde escáner o cámara”: a) b) c) d)
Se insertan desde una Galería de Imágenes. Se insertan como un archivo .jpg o .gif. Se insertan desde un dispositivo externo. Todas son correctas.
4. Las imágenes insertadas “Desde archivo”: a) b) c) d)
Se insertan desde una Galería de Imágenes. Se insertan como un archivo .jpg o .gif. Se insertan desde un dispositivo externo. Todas son correctas.
5. La aplicación que permite crear fórmulas matemáticas sin cálculo se denomina: a) b) c) d)
Imágenes WordArt. Editor de Ecuaciones. Diagramas y Organigramas. Insertar Comentarios.
6. La aplicación que permite crear textos con formas especiales se denomina: a) b) c) d)
52
Imágenes WordArt. Editor de Ecuaciones. Diagramas y Organigramas. Insertar Comentarios.
Excel XP avanzado
Ed. 3.1
Tema 3: Los objetos en Excel
7. La aplicación que permite añadir información o ayuda sobre una celda específica es: a) b) c) d)
Imágenes WordArt. Editor de Ecuaciones. Diagramas y Organigramas. Insertar Comentarios.
8. La opción que permite crear información estructurada de forma jerárquica, radial… es: a) b) c) d)
Imágenes WordArt. Editor de Ecuaciones. Diagramas y Organigramas. Insertar Comentarios.
9. Fuentes específicas de símbolos son: a) b) c) d)
Times New Roman. Webdings. Wingdings. b) y c) son correctas.
10. Las opciones que permiten crear figuras geométricas, flechas de bloque, estrellas, diagramas de flujo… se denominan (dentro de la Barra Dibujo): a) b) c) d)
Autoformas. Cuadros de Texto. Diagramas. Flechas.
Excel XP avanzado
53
Tema 3: Los objetos en Excel
Ed. 3.1
EJERCICIOS
1. Realice a continuación los distintos ejemplos planteados a lo largo del tema en los puntos: 3.2, 3.3, 3.4, 3.5, 3.6 y 3.7. 2. Cree un Organigrama titulado “Estructura de productos de la empresa”. En el primer nivel deben aparecer las diversas secciones de productos (por ejemplo, “Congelados”, “Productos frescos” y “Limpieza”) 3. Consulte un libro de Matemáticas o de Estadística para, posteriormente, crear algunas fórmulas con el Editor de Ecuaciones. Tres ejemplos de ecuación que puede intentar realizar son:
4. Utilice la opción “Insertar Imagen desde Archivo” o “Insertar Imágenes Prediseñadas” para insertar un logotipo de empresa sobre cualquiera de los libros de trabajo creados en temas anteriores. Un ejemplo de Hoja de Cálculo con Logotipo podría ser el siguiente:
54
Excel XP avanzado
Ed. 3.1
Tema 3: Los objetos en Excel
5. Con la aplicación WordArt intente crear el siguiente rótulo:
Excel XP avanzado
55
Tema 3: Los objetos en Excel
56
Ed. 3.1
Excel XP avanzado
Ed. 3.1
Tema 4: Gráficos en Excel
TEMA 4 GRÁFICOS EN EXCEL 4.1. 4.2. 4.3. 4.4. 4.5. 4.6.
Tipos de gráficos Crear un gráfico I: elección del tipo de gráfico Crear un gráfico II: elección de los datos de origen Crear un gráfico III: opciones del gráfico Crear un gráfico IV: insertar gráfico Personalizar el gráfico: fuentes, tamaños, propiedades, escalas, formatos…
4.1. Tipos de Gráficos Los Gráficos representan datos de una tabla (texto y números) en una imagen que combina información matemática, información de texto y un atractivo formato. Como veremos en los puntos siguientes el Asistente para Gráficos nos muestra un número importante de Gráficos que se utilizan según sus características y utilidad específica en relación a la información que se quiere representar. No obstante, puede decirse que existen cuatro tipos básicos de Gráficos (columnas, barras, líneas y sectores), a partir de los cuales, se derivan todos los demás. Los tipos de Gráficos que pueden utilizarse son los siguientes: •
Gráficos de columnas: el gráfico de columnas se utiliza para representar datos de una tabla, normalmente de dos columnas (información de texto e información numérica), y que permite comparar los datos de una forma sencilla.
Excel XP avanzado
57
Tema 4: Gráficos en Excel
58
Ed. 3.1
•
Gráficos de barras: es un tipo similar al de columnas, pero girándolas 90º hacia la izquierda. Tiene una utilidad similar al anterior, es decir, comparar datos.
•
Gráficos de líneas: se trata de un tipo de gráfico ideal para representar la evolución de una serie de datos a lo largo de un determinado espacio de tiempo.
•
Gráficos circulares: este tipo de gráfico es ideal cuando deseamos representar las series de datos mediante porcentajes, repartiéndose los sectores del círculo proporcionalmente a los mismos. Este gráfico es el primero que no está basado en el eje XY.
Excel XP avanzado
Ed. 3.1
Tema 4: Gráficos en Excel
•
Gráficos de dispersión (XY): ideal para comparar los valores deseados sin necesidad de representarlos mediante barras o similares.
•
Gráficos de áreas: permiten representar información numérica de forma similar a los de líneas, pero apareciendo el fondo de cada serie de datos coloreado.
•
Gráficos de anillos: se trata de un gráfico similar al circular, pero permite la representación de varias series de datos.
•
Gráficos radiales: este tipo de gráfico está basado también en un círculo, apareciendo los datos divididos en áreas separadas por radios.
•
Gráficos de superficie: se trata de un gráfico de tres dimensiones donde los datos se representan, mediante áreas coloreadas, sobre una superficie plana.
•
Gráficos de burbujas: similares a los de dispersión, los valores alcanzados por los datos se representan mediante burbujas.
Excel XP avanzado
59
Tema 4: Gráficos en Excel
Ed. 3.1
•
Gráficos de cotizaciones: gráfico enfocado principalmente hacia la representación de cotizaciones (bolsa)
•
Gráficos cónicos, cilíndricos y piramidales: tienen una utilidad similar a los gráficos de barras o de columnas, pero los datos son representados mediante los cuerpos geométricos nombrados.
4.2. Crear un gráfico I: elección del tipo de gráfico Para crear un gráfico, la primera operación que debe realizar es seleccionar los datos que van a permitir crear el mismo. Vamos a suponer que estamos estudiando la producción de carbón en el trienio 2003-2005 en las comunidades de la mitad norte del país:
A continuación, para iniciar el Asistente para Gráficos (aplicación que nos ayuda en el proceso de creación de un Gráfico) ha de hacer “clic” sobre el botón del mismo nombre de la Barra de Herramientas Estándar o seleccionar la opción Gráficos del Menú Insertar. Nos encontramos, a partir de este momento, con una serie de pasos (cuatro) que van a permitirnos finalmente crear nuestro gráfico. Una vez seleccionadas las celdas adecuadas y una vez que hemos accedido al Asistente para Gráficos nos encontramos con el primer paso del asistente: elección del tipo de gráfico.
60
Excel XP avanzado
Ed. 3.1
Tema 4: Gráficos en Excel
Como puede observar puede elegir entre los tipos de gráficos estudiados en el punto anterior. También dispone de la pestaña Tipos personalizados donde puede seleccionar entre gráficos del mismo tipo y características de los que encuentra en la pestaña Tipos estándar, pero con algunas mejoras por formato y efectos.
En nuestro ejemplo vamos a seleccionar un tipo de gráfico “Circular”. Una vez seleccionado, hemos de hacer “clic” sobre el botón Siguiente.
Excel XP avanzado
61
Tema 4: Gráficos en Excel
Ed. 3.1
4.3. Crear un gráfico II: elección de los datos de origen Esta última acción nos lleva al segundo paso del asistente: la elección de los datos de origen. Con respecto al rango de celdas que va a servir como partida para la creación del gráfico es una operación ya realizada, pues las hemos seleccionado antes de comenzar con el asistente. En nuestro ejemplo, la ventana puede mostrar el siguiente aspecto:
En este paso podemos indicar cuál es el rango de datos (celdas) a partir de las cuales va a crearse el Gráfico (puede desplazarse a la Hoja haciéndo “Clic” sobre el botón adecuado). Igualmente, puede indicarle al programa si los datos han de tomarse “por filas” o “por columnas”. En nuestro ejemplo, los datos ya han sido seleccionados y las series tienen que ser en columnas (podrá comprobar como si elige fila el gráfico no tiene sentido) por lo que ya puede hacer “clic” sobre el botón Siguiente.
4.4. Crear un gráfico III: opciones del gráfico Una vez que hemos seleccionado el botón “Siguiente” accedemos a la siguiente ventana (tercer paso del asistente): Opciones de Gráfico. A través de la misma, dependiendo del tipo de gráfico seleccionado en el paso 1, disponemos de diversas opciones para mejorar la presentación del Gráfico o incluir datos importantes para el mismo:
62
Excel XP avanzado
Ed. 3.1
Tema 4: Gráficos en Excel
En la pestaña Títulos, al tratarse de un gráfico de sectores, sólo puede asignar un título al mismo (en el caso de gráficos basados en el eje XY –barras, columnas, líneas…- puede además crear títulos para el eje X y para el eje Y) En la pestaña Rótulo de datos puede incorporar nueva información al gráfico tal como incluir nombres de las columnas, nombres de las filas, valores… Para el tipo de gráfico seleccionado hemos activado la casilla “Porcentaje” (habitual en los gráficos de sectores ya que la aparición de los porcentajes complementa al resto de la información) Por último, en la pestaña Leyenda, puede indicar si ésta aparece o no (es importante en un gráfico de sectores para asignar cada color a un dato) activando/desactivando la casilla correspondiente. También puede seleccionar la ubicación de la Leyenda (arriba, abajo…)
Por último seleccione, como en los pasos anteriores, el botón Siguiente.
Excel XP avanzado
63
Tema 4: Gráficos en Excel
Ed. 3.1
4.5. Crear un gráfico IV: Insertar gráfico En este último paso ha de decidir si desea que el gráfico se inserte como un objeto en su Hoja de Cálculo o, si lo prefiere, que se cree una Hoja nueva especial donde se insertará el Gráfico (en esta hoja no se podrá introducir información ya que será ocupada en su totalidad por el Gráfico).
Suele ser habitual seleccionar la opción “Como objeto en” para insertar el gráfico en la hoja actual. Por último, seleccione el botón Finalizar. El Gráfico quedará insertado en la Hoja donde se encuentran los datos que han permitido crearlo, pudiendo modificarlo de tamaño, cambiarlo de posición, eliminarlo… al igual que vimos en el tema anterior con el resto de objetos. Muy importante es saber que el Gráfico está vinculado a los datos a partir de los cuales ha sido creado, por lo que una modificación en los datos modificará automáticamente el Gráfico.
64
Excel XP avanzado
Ed. 3.1
Tema 4: Gráficos en Excel
4.6. Personalizar el gráfico: fuentes, tamaños, propiedades, escalas, formatos… Una vez creado el gráfico, siempre que esté seleccionado con sus puntos de anclaje, aparece en la Hoja la Barra de Herramientas Gráfico. A través de la misma puede modificar y o mejorar el aspecto del Gráfico seleccionado.
Por último indicar que si selecciona el botón Asistente para Gráficos (con el gráfico seleccionado) los pasos del asistente afectan al gráfico en cuestión y no a la creación de un nuevo gráfico.
Excel XP avanzado
65
Tema 4: Gráficos en Excel
Ed. 3.1
IDEAS CLAVE
66
•
Los Gráficos son los objetos más utilizados con las Hojas de Cálculo ya que permiten mostrar sus datos de una manera clara y atractiva.
•
Existen diversos tipos de gráficos, aunque puede decirse que existen cuatro tipos principales: Gráfico de Columnas, Gráfico de Barras, Gráfico de Líneas y Gráfico de Sectores. Además, existen otros tipos (similares a los anteriores) que utilizaremos según nuestras necesidades (de dispersión, personalizados, piramidales….)
•
Para crear un Gráfico hemos de hacer “clic” sobre el botón Asistente para Gráficos de la Barra de Herramientas Estándar o seleccionando la opción Gráfico del Menú Insertar. Este Asistente inicia una serie de pasos que nos permiten iniciar y crear el gráfico: 1) Elección del tipo de Gráfico; 2) Selección del rango de datos y de la orientación de los mismos (Filas o Columnas); 3) Modificación de parámetros del gráfico (opciones de leyenda, títulos…); 4) Inserción del Gráfico en Hoja actual o crear Hoja nueva.
•
Una vez creado el Gráfico, este actúa como cualquier Objeto: gracias a los puntos de anclaje puede modificarse de tamaño, cambiarse de posición, eliminarse…
•
Además, teniendo el gráfico seleccionado, disponemos de la Barra de Herramientas Gráfico que permite seleccionar distintas áreas, cambiar su formato (colores, fuentes…), cambiar el tipo de Gráfico… Igualmente, si seleccionamos el botón Asistente para Gráficos, podremos modificar las distintas opciones de los cuatro pasos del asistente para el gráfico seleccionado.
•
Una característica especial de los Gráficos es que se trata de un objeto vinculado a los datos de los que procede, por lo que cualquier modificación en los datos actualiza automáticamente el gráfico en cuestión.
Excel XP avanzado
Ed. 3.1
Tema 4: Gráficos en Excel
AUTOEVALUACIÓN DEL TEMA 4 1. Poder mostrar o no la leyenda en un gráfico lo encontramos en el: a) b) c) d)
Paso Paso Paso Paso
1. 2. 3. 4.
2. Seleccionar el tipo de gráfico está en el: a) b) c) d)
Paso Paso Paso Paso
1. 2. 3. 4.
3. Las series (rangos) para hacer el gráfico se pueden tomar por: a) b) c) d)
Filas. Columnas. a) y b) son correctas. Celdas.
4. En la Barra de Herramientas Gráfico podemos: a) b) c) d)
Visualizar/No visualizar la leyenda. Seleccionar un tipo de gráfico. Dar formato a un área del gráfico. Todas son correctas.
5. Que un gráfico se actualice automáticamente cuando se modifican los datos se denomina: a) b) c) d)
Recálculo. Vinculación. Formato. Todas son correctas.
6. El botón que permite acceder a los distintos pasos de creación del gráfico se denomina: a) b) c) d)
Asistente para Gráficos. Gráfico. Creación de Gráficos. Ninguna es correcta.
Excel XP avanzado
67
Tema 4: Gráficos en Excel
Ed. 3.1
7. El gráfico que muestra los porcentajes junto al mismo se denomina: a) b) c) d)
Gráfico Gráfico Gráfico Gráfico
de de de de
columnas. barras. líneas. sectores.
8. El gráfico que se utiliza para estudiar la evolución de los datos en un periodo determinado de tiempo se denomina: a) b) c) d)
Gráfico Gráfico Gráfico Gráfico
de de de de
columnas. barras. líneas. sectores.
9. Un gráfico puede insertarse: a) b) c) d)
En la hoja activa. En una hoja nueva. En otro libro. A) y b) son correctas.
10. Podemos iniciar el Asistente para Gráficos desde el Menú: a) b) c) d)
68
Archivo. Ver. Insertar. Formato.
Excel XP avanzado
Ed. 3.1
Tema 4: Gráficos en Excel
EJERCICIOS 1. Realice los gráficos planteados en los puntos 4.1, 4.2, 4.3, 4.4 y 4.5. 2. A partir de la hoja “Resumen” del libro “Ventas Clientes 06” intente crear el siguiente gráfico personalizado, insertándolo en una hoja nueva que llamará “Gráfico Resumen”:
3. Recuerde que creó una tabla para calcular la evolución del precio del aceite en un periodo determinado de años. Cree un gráfico de líneas para estudiar la evolución del precio del aceite en dichos años:
Excel XP avanzado
69
Tema 4: Gráficos en Excel
70
Ed. 3.1
Excel XP avanzado
Ed. 3.1
Tema 5: Bases de datos en Excel (listas)
TEMA 5 BASES DE DATOS EN EXCEL (LISTAS)
5.1. Concepto de Base de Datos. Creación de una base de datos (lista) en Excel 5.2. Ordenación de los datos 5.3. Gestión de los datos en formato Formulario 5.4. Utilización de filtros: Autofiltros 5.5. Utilización de filtros: Filtro Avanzado 5.6. Cálculos con bases de datos: subtotales 5.7. Control de datos: validación
5.1. Concepto de Base de Datos. Creación de una base de datos (lista) en Excel Una Base de Datos o Lista en Excel es una serie de datos con un encabezado para cada columna utilizada. Para que Excel considere los datos introducidos como una lista debemos cumplir algunas normas: • No deben dejarse celdas en blanco. • Es recomendable destacar (sombreado, bordes, colores…) la fila de encabezados. • La lista debe estar en una hoja independiente, y es recomendable no introducir otros datos en la misma. • Si desea tener siempre a la vista la fila de encabezados (lo cual es recomendable), seleccione la opción Inmovilizar Paneles del Menú Ventana (opción que se verá en el Tema 7) En general, los datos de una lista suelen introducirse por teclado, aunque podemos traer datos de una tabla de otras aplicaciones de Microsoft Office (por ejemplo, Word o Access) mediante las opciones de intercambio de información con el Portapapeles que ya conocemos (copiar y pegar…)
Excel XP avanzado
71
Tema 5: Bases de datos en Excel (listas)
Ed. 3.1
Un ejemplo de lista creada con Excel la encontramos en nuestro Libro de trabajo “Librería” que ya creamos en temas anteriores:
Aunque los datos podrían introducirse directamente por teclado, podemos ahorrar trabajo cuando haya datos que se repitan en varias celdas de la misma columna (por ejemplo, en el caso del campo PROVINCIA). Cuando estemos situados en la celda donde hemos de introducir un dato, debemos hacer un “clic” con el botón derecho del ratón (menú contextual) y a continuación elegir la opción Elegir de la lista desplegable. También puede obtenerse este menú utilizando la tecla de menú desplegable/menú contextual (extremo inferior derecha del teclado junto a la tecla “Control”). Excel mostrará los datos utilizados hasta ahora en una lista, de la que podemos seleccionar el deseado (A. Pérez Reverte en este ejemplo):
A través del mismo Menú contextual, también podemos seleccionar la opción Insertar Comentario, lo que nos permitirá introducir un comentario que sólo aparecerá en el momento de seleccionar la celda.
72
Excel XP avanzado
Ed. 3.1
Tema 5: Bases de datos en Excel (listas)
Otra forma de ayuda de Excel se produce cuando estamos introduciendo un dato por teclado en una celda. En ese momento Excel repite en pantalla la entrada que se haya producido anteriormente más parecida a la que se esté introduciendo en ese momento. Si la propuesta de Excel le satisface basta con pulsar el tabulador o la tecla Enter para que aparezca el contenido en la celda (al introducir “Al” en nuestro ejemplo el programa propondrá completar con “Alfaguara”. Bastará pulsar el tabulador en ese momento para aceptar la propuesta y continuar la introducción de datos) Esta opción se utiliza igualmente para las fórmulas y funciones, por lo que Excel repetirá automáticamente la fórmula de las filas anteriores en las celdas correspondiente de la fila actual.
5.2. Ordenación de los datos (lista) Cualquier listado o conjunto de datos es lógico que deseemos ordenarlos según un criterio específico (puede desear ordenar alfabéticamente por apellidos un listado de empleados de la empresa, ordenar numéricamente por precio un listado de productos...) En primer lugar, disponemos de un sistema básico de ordenación a través de los botones Orden Ascendente y Orden Descendente de la Barra de herramientas Estándar, situándonos primero en cualquier celda de la columna por la que queremos ordenar (por ejemplo, podemos ordenar los libros por título haciendo “clic” en la celda B2 y haciendo “clic” sobre el botón “Orden
Excel XP avanzado
73
Tema 5: Bases de datos en Excel (listas)
Ed. 3.1
ascendente”). El orden ascendente será más habitual en columnas de texto, mientras que ambos (ascendente y descendente) se utilizarán por igual en columnas numéricas (por ejemplo, ordenar precios de mayor a menor o de menor a mayor)
Vemos como los datos aparecen a continuación ordenados por la columna B (por Título)
Si queremos una forma más completa de realizar una ordenación de los datos, entonces debemos utilizar la Opción Ordenar del Menú Datos.
74
Excel XP avanzado
Ed. 3.1
Tema 5: Bases de datos en Excel (listas)
En el ejemplo de la izquierda estamos seleccionando en Orden Ascendente por Editorial (como primer criterio de ordenación), en Orden Ascendente por Autor (como segundo criterio de ordenación) y en Orden Ascendente por Título (como tercer criterio de ordenación). Indicar también que no es obligatorio cumplimentar los tres criterios de ordenación, y que normalmente debe estar activada la opción “Sí” en lo referente a si existe “fila de encabezamiento.
En los criterios de ordenación establecidos en el ejemplo de la derecha se ha decidido ordenar por Autor en orden Ascendente (como primer criterio), por PVP en orden Descendente (como segundo criterio) y por Título en orden Ascendente (como tercer criterio) si se produce que hay dos precios de venta al público iguales.
Puede comprobar también que existe un botón llamado Opciones. Lo utilizaremos cuando el criterio de ordenación no pueda ser una ordenación alfabética o numérica ascendente o descendente (por ejemplo, días de la semana o meses del año). En esta opción podemos seleccionar criterios de este tipo. Si lo que necesitamos es establecer nuestro propio criterio, debemos crear una lista a través de la pestaña Listas Personalizadas, que podemos encontrar en la Opción Opciones del Menú Herramientas.
Excel XP avanzado
75
Tema 5: Bases de datos en Excel (listas)
Ed. 3.1
5.3. Gestión de los datos en formato Formulario Excel permite la introducción, visualización y/o gestión (búsqueda, eliminación…) de los datos de una forma individual, es decir a través de una ventana que los muestra uno a uno en la pantalla (filas). Especialmente es útil este tipo de presentación cuando las listas son demasiado largas, ya que sería más cómodo y más rápido poder introducir los datos a través de una ficha individual. Esta opción podemos encontrarla en Excel a través del Menú Datos, Opción Formulario.
A través de esta opción que podemos denominar “Formularios” la entrada de datos será probablemente más rápida y más segura (muchas veces tener todos los datos a la vez en una lista puede llevar a equivocaciones). Además, nos encontramos con diversas opciones tales como:
76
•
Introducir un nuevo registro (fila): haga “clic” sobre el botón Nuevo y las casillas del formulario quedarán en blanco para introducir nuevos datos. Puede comprobar, en nuestro ejemplo, como no pueden introducirse datos en las celdas “Iva €” y “PVP” ya que se trata de fórmulas que se calcularán automáticamente.
•
Moverse entre los campos del Formulario (celdas): para movernos entre los distintos campos (celdas) de la Ficha Formulario, podemos utilizar el ratón o la tecla de salto de tabulación.
•
Eliminar un registro (fila): para eliminar un registro nos situamos sobre el mismo (es decir, que estemos visualizándolo en pantalla) y hacemos “clic” sobre el botón Eliminar.
Excel XP avanzado
Ed. 3.1
Tema 5: Bases de datos en Excel (listas)
•
Búsqueda de registros con criterio: una de las opciones más útiles de este formato de Formulario es la posibilidad de localizar uno o varios registros a través del botón Criterios. La ventana quedará con todos los campos (celdas) en blanco y donde podemos introducir el/los criterio/s deseado/s, pudiendo usar los operadores > (mayor que), < (menor que); <> (distinto), = (igual), >= (mayor o igual) y <= (menor o igual).
En este primer ejemplo, al establecer como criterio “Alfaguara” en el campo “Editorial”, Excel localizará en la base de datos (lista) todos los registros (filas) que cumplan el requisito “Editorial=Alfaguara”. Para visualizarlos, uno a uno, haremos “clic” sobre el botón Buscar Siguiente (Excel mostrará en pantalla el primer registro donde se cumple el criterio establecido). En pantalla podrá ver los datos correspondientes a la obra “El capitán Alatriste” (primer libro que cumple la condición)
En el segundo ejemplo, se pretende que Excel nos muestre aquellos libros cuyo Precio de Venta al Público (PVP) sea inferior a 15 €. Como puede comprobar hemos utilizado el operador “menor (<)” necesario para que pueda cumplirse la condición al hacer “clic” en el botón Buscar Siguiente.
Excel XP avanzado
77
Tema 5: Bases de datos en Excel (listas)
Ed. 3.1
Cuando finalice su trabajo, para regresar a la ventana normal, haga “clic” sobre el botón Cerrar.
5.4. Utilización de filtros: Autofiltros Hasta ahora hemos utilizado algunas opciones básicas sobre bases de datos (introducción de datos, ordenación, búsqueda…). Pero una base de datos avanzada es aquella que permite filtrar la información de tal forma, que podamos visualizar en cada momento la información que nos interese y no siempre todos los registros. Una primera forma de visualizar y/o filtrar la información es el Autofiltro y que podemos encontrar en el Menú Datos, Opción Filtros, subopción Autofiltro. Una vez seleccionada esta opción, Excel convierte la fila de encabezados en listas desplegables. Si hacemos “clic” sobre cualquiera de las listas desplegables podemos realizar varios tipos de selección que conllevarán métodos diferentes y resultados diferentes:
•
78
Seleccionar uno de los datos introducidos por teclado en la lista desplegable (por ejemplo, “Alfaguara” en el campo “Editorial”): de esta forma, se filtrarán todos los registros y sólo se visualizarán aquellos cuyo campo “Editorial” coincide con el dato seleccionado. Por ejemplo, veamos como queda la tabla
Excel XP avanzado
Ed. 3.1
Tema 5: Bases de datos en Excel (listas)
anterior filtrada a través del campo “Editorial” y seleccionando “Alfaguara”:
Es decir, sólo se muestran los libros de la Editorial “Alfaguara”, apareciendo la lista desplegable del campo “Editorial” de otro color (azul normalmente) para indicarnos que la lista está filtrada a través de dicho campo y estamos visualizando todos los registros (filas) Para volver a visualizar todos los registros nuevamente ha de hacer “clic” sobre la opción Todas de la lista desplegable: volveremos a visualizar todos los registros de la base de datos (lista) •
Seleccionar la Opción “Personalizar…”: lo que nos permitirá establecer los criterios del filtro como se muestra en este cuadro de diálogo:
Excel XP avanzado
79
Tema 5: Bases de datos en Excel (listas)
Ed. 3.1
Podemos establecer la condición en la lista desplegable de la izquierda (igual a, mayor que, menor que, comienza por…) y el valor de la comparación en el cuadro de la derecha. Podemos también, gracias a los operadores Y / O establecer un segundo criterio. En el ejemplo mostrado a la izquierda se pretenden visualizar aquellos registros cuyo PVP es igual o superior a 10 € e igual o inferior a 15 € (utilizando el operador “Y”), siendo el resultado el siguiente:
En el ejemplo situado a la derecha se pretenden visualizar aquellos libros de la Editorial “Alfaguara” y de la Editorial “Maeva” (para ello utilizamos el operador “O” ya que deseamos visualizar los libros de una editorial o los libros de otra editorial):
•
80
Seleccionar la opción “Diez mejores…”(sólo para campos numéricos): permite indicar a Excel si se muestran los valores superiores o inferiores deseados del campo seleccionado:
Excel XP avanzado
Ed. 3.1
Tema 5: Bases de datos en Excel (listas)
Aquí, podemos elegir entre “Superiores” o “Inferiores” e introducir un valor. Por ejemplo, al elegir “Superiores” e introducir el valor 7 en el campo “PVP” Excel nos mostrará los 7 libros con precio superior:
Como ya se dijo con anterioridad, siempre que desee volver a mostrar todos los registros de la base de datos (lista) basta con hacer “clic” sobre la opción “Todas” del campo filtrado (cuya flecha del menú desplegable aparece en azul)
5.5. Utilización de filtros: Filtro Avanzado Podemos mejorar aún más los filtros si utilizamos la subopción Filtros Avanzados de la opción Filtros del Menú Datos. A través de esta opción, según los datos introducidos por separado en otra zona de la hoja, podemos realizar filtros más específicos.
Así, en este ejemplo podemos comprobar como se ha filtrado la información estableciendo el criterio que el Título comience por la letra “E” (“E*” significa “que empiece por E”) Para lograrlo, el cuadro de diálogo correspondiente ha de mostrar el siguiente aspecto (cuadro que se obtiene al seleccionar la subopción “Filtro Avanzado” de la opción “Filtros”, Menú Datos):
Excel XP avanzado
81
Tema 5: Bases de datos en Excel (listas)
Ed. 3.1
Otro ejemplo podría establecer, además del criterio anterior (que el título comience con “E” usando el asterisco como carácter comodín: “E*”), que los libros fuesen de la Editorial Maeva. Con los mismos datos introducidos en “Filtro avanzado” el resultado sería el siguiente:
Finalmente, cuando desee visualizar de nuevo todos los datos de la base de datos (lista), ha de seleccionar el Menú Datos, la opción Filtro y la subopción Mostrar Todo.
5.6. Cálculos con Bases de datos: Subtotales Otra operación de interés que nos permite Excel sobre las listas son los cálculos agrupados por subtotales (para ello, la lista ha de estar ordenada). Supongamos, por ejemplo, una tabla donde nos interesa conocer la suma de los distintos saldos agrupados por provincias y, finalmente, también la suma total. Dicha tabla tendrá los siguientes campos: Empresa, Población, Provincia, Teléfono y Saldo. Además, se encuentran ordenados por el campo Provincia (primer criterio de
82
Excel XP avanzado
Ed. 3.1
Tema 5: Bases de datos en Excel (listas)
ordenación), Población (segundo criterio) y Empresa (tercer criterio). Guarde el libro como “Base de Datos empresas”.
Para realizar este tipo de operaciones, hemos de desplegar el Menú Datos y seleccionar la opción Subtotales. Nos encontramos con el siguiente cuadro de diálogo: En él podemos comprobar como se ha seleccionado el campo PROVINCIA como campo de agrupación, la operación Suma (podemos seleccionar entre varias operaciones matemáticas posibles) y el campo SALDO para realizar el cálculo. Según estos criterios, el resultado obtenido será el siguiente (el programa creará automáticamente las líneas “Total Cádiz”, “Total Granada”, “Total Málaga” y “Total General” con el resultado de los correspondientes cálculos en la columna saldo):
Excel XP avanzado
83
Tema 5: Bases de datos en Excel (listas)
Ed. 3.1
Podemos analizar este cuadro de diálogo a continuación: • “Para cada cambio en”: seleccionamos el campo que queremos agrupar (es decir, el campo a través del cual se va a realizar el cálculo) • “Usar función”: operación (cálculo) a aplicar sobre el campo indicado en el siguiente punto. Los cálculos posibles a realizar pueden ser: Sumar, Cuenta, Promedio (media aritmética), Máx (máximo), Mín (mínimo), Producto, Contar números, Desvest (desviación estándar), Desvestp, Var (varianza) y Varp. • “Agregar subtotal a”: seleccionamos el campo que se va a calcular (pueden ser uno o varios campos, pero siempre una sola operación) • Botón “Quitar todos”: anula la agrupación, los textos y todos los cálculos realizados desde esta opción de Menú.
5.7. Control de datos: Validación En este punto vamos a tratar la posibilidad de controlar la introducción de datos en una base de datos (lista). Podemos conseguir que sólo se admitan un tipo determinado de valores (por ejemplo, en el campo SALDO sólo deben admitirse números), que Excel muestre mensajes al respecto, ya sea al introducir el dato en la celda (que aparezca un mensaje indicando que ha de introducirse un número) o cuando se cometa un error (mensaje que indique que el dato introducido no es correcto).
84
Excel XP avanzado
Ed. 3.1
Tema 5: Bases de datos en Excel (listas)
En primer lugar, hemos de seleccionar las celdas sobre las que vamos a establecer una “Validación”. A continuación, hemos de desplegar el Menú Datos y seleccionar la opción Validación. En el cuadro de diálogo que aparece vamos a encontrarnos con las siguientes fichas: •
Pestaña Configuración: donde indicaremos, para las celdas seleccionadas, cuál es el tipo de valor permitido (cualquier valor, número, fecha, hora…) e incluso, establecer un límite mínimo y un límite máximo (por ejemplo, que los valores estén entre 10 y 20), un valor determinado…
•
Pestaña Mensaje Entrante: donde introduciremos el mensaje que debe aparecer al seleccionar la celda donde vamos a introducir un valor (por ejemplo, podemos indicar que “Ha de introducirse un valor de Fecha” para un campo de estas
Excel XP avanzado
85
Tema 5: Bases de datos en Excel (listas)
Ed. 3.1
características). El mensaje aparecerá en un cuadro amarillo (tipo Comentario) junto a la celda en la que estemos situados. •
Pestaña Mensaje de Error: donde introduciremos el mensaje que debe aparecer si se comete un error al introducir el dato (por ejemplo, que aparezca el mensaje de error “No es un número válido”, cuando en dicha celda puede introducirse solamente un valor numérico). Pueden establecerse tres Estilos: “Límite” (una “X” es el símbolo que lo identifica: si no se introduce el valor adecuado no permite la entrada de datos y la celda queda vacía), “Advertencia” (el símbolo que lo identifica es “!”: mediante un mensaje de aviso se puede aceptar o rechazar la entrada de datos) e “Información “ (su símbolo es una “i”: se avisa mediante un mensaje, sólo a título informativo, del error en la introducción de datos).
En nuestro ejemplo vamos a seleccionar las celdas numéricas de la columna E (Saldo) desde E2 hasta E8 y, después de acceder a la opción “Validación” del Menú “Datos”, vamos a aplicar las siguientes condiciones:
86
•
En la Pestaña Configuración vamos a “Permitir” valores numéricos no iguales a 0.
•
En la Pestaña Mensaje Entrante vamos a introducir como Título la palabra “¡Importante!” y como Mensaje Entrante “Sólo es válido un valor numérico”.
•
En la Pestaña Mensaje de Error vamos a establecer un Estilo “Límite” (no permite la entrada si no se cumple), como Título “AVISO” y como Mensaje de Error “No se permiten valores no numéricos”. Finalmente, haga “clic” sobre el botón Aceptar.
Excel XP avanzado
Ed. 3.1
Tema 5: Bases de datos en Excel (listas)
De esta forma, cuando nos situamos sobre una de las celdas de la columna del saldo E, el programa sólo nos avisará con el mensaje “Importante… Sólo es válido un valor numérico”. En el caso de no introducirlo aparecerá el mensaje “AVISO…No se permiten valores no numéricos” y no permitirá la entrada actual debido a que se ha establecido un Estilo “Límite”.
Excel XP avanzado
87
Tema 5: Bases de datos en Excel (listas)
Ed. 3.1
IDEAS CLAVE
88
•
Una forma concreta y habitual de introducir datos en una Hoja de Cálculo son las Bases de Datos o Listas. Éstas requieren la existencia de un encabezado (campos) y que no existan columnas o filas completamente en blanco.
•
Los datos pueden introducirse por teclado o a través de la opción Formulario del Menú Datos. A través de esta opción, no sólo vamos a introducir los datos de una forma más cómoda, sino que nos permite desplazarnos entre registros, eliminar uno y buscar según el criterio establecido.
•
Lógicamente, una Base de Datos suele contener un número elevado de registros por lo que se hace necesaria la ordenación de los mismos. Podemos hacerlo a través de los botones correspondientes de la Barra de Herramientas Estándar o a través del Menú Datos, opción Ordenar, donde podemos establecer hasta tres criterios de ordenación.
•
En otras ocasiones necesitaremos filtrar la información de tal forma que sólo aparezcan algunos registros según el criterio establecido. Para ello, podemos establecer dos tipos de Filtros: los Filtros Automáticos (que nos permiten seleccionar qué registros han de aparecer según el criterio establecido en la lista desplegable que aparece automáticamente junto al encabezado cuando seleccionamos esta opción) y los Filtros Avanzados (que nos permiten ampliar las posibilidades de filtrar la información mediante la introducción de criterios en la misma Hoja de Cálculo)
•
Otra operación de interés a realizar con Listas o Bases de Datos, es la creación de Subtotales (a través del Menú Datos). Una vez que la Lista está ordenada podemos hacer que Excel realice alguna operación matemática sobre los datos indicados (por ejemplo, que calcule el promedio obtenido de ventas por provincias). El programa creará automáticamente los rótulos (textos), las fórmulas y los resultados obtenidos.
•
Por último, indicar que también se pueden establecer controles a la hora de introducir los datos para evitar una entrada incorrecta de los mismos. Por ejemplo, en una celda correspondiente a las ventas realizadas no puede permitirse la entrada de un texto. Para utilizar esta opción seleccionaremos la opción Validación del Menú Datos.
Excel XP avanzado
Ed. 3.1
Tema 5: Bases de datos en Excel (listas)
AUTOEVALUACIÓN DEL TEMA 5 1. En una lista no pueden existir: a) b) c) d)
Celdas Filas o Celdas Celdas
con formatos. Columnas en blanco. con fórmulas. con textos.
2. Las listas pueden ordenarse en orden: a) b) c) d)
Ascendente. Descendente. No se pueden ordenar. A) y b) son correctas.
3. Puede ordenar una lista hasta por: a) b) c) d)
1 2 3 4
criterio de ordenación. criterios de ordenación. criterios de ordenación. criterios de ordenación.
4. La opción “Ordenar” se encuentra en el Menú: a) b) c) d)
Edición. Formato. Herramientas. Datos.
5. La opción “Formulario” aplicada sobre una lista permite: a) b) c) d)
Dar formato a la información. Disponer sus datos en forma de ficha. Imprimir los contenidos de la lista. Ninguna es correcta.
6. Si desea aplicar un filtro y que al lado de cada campo aparezca una lista desplegable seleccionará: a) b) c) d)
Autofiltro. Filtro Avanzado. Mostrar todo. Ninguna es correcta.
Excel XP avanzado
89
Tema 5: Bases de datos en Excel (listas)
Ed. 3.1
7. La opción personalizar de los Autofiltros le permite establecer: a) b) c) d)
Un criterio de filtro. Dos criterios de filtro. Tres criterios de filtro. Tantos criterios como necesite.
8. La opción “Diez mejores…” de los Autofiltros muestra: a) b) c) d)
Los n primeros o últimos registros. Los 10 últimos registros utilizados. Los 10 registros que más se acercan a una condición. Ninguna es correcta.
9. Por defecto, la opción “Filtro avanzado”: a) b) c) d)
Filtra la lista sin moverla. La duplica en otro lugar de la misma hoja. La duplica en otro lugar de otra hoja. Le solicita donde duplicar la lista.
10. La opción “Subtotales” le permite calcular: a) b) c) d)
90
Sumas. Promedios. Máximos. Todas son correctas.
Excel XP avanzado
Ed. 3.1
Tema 5: Bases de datos en Excel (listas)
EJERCICIOS
1. Realice los ejercicios del tema planteados en los puntos 5.1, 5.2, 5.3, 5.4, 5.5, 5.6 y 5.7. 2. A través de la ficha Formulario realice las siguientes operaciones en las Hojas:
3. Cree una lista con los empleados de una empresa (por ejemplo, 9 empleados) con los siguientes campos: DNI, NOMBRE, APELLIDOS, FECHA ALTA, PUESTO, SUELDO. Dé a dicha lista un formato de cuadrícula, y con la cabecera en negrita y sombreada. Guarde el libro de trabajo como “Base de Datos Empleados”. 4. Ordene la Lista anterior estableciendo los siguientes Criterios de Ordenación: 1º) Fecha Alta; 2º) Apellidos; 3º) Nombre. 5. Vuelva a ordenar la lista según los siguientes criterios: 1º) Puesto, 2º) Apellidos, 3º) Nombre. A continuación utilice la opción “Subtotales”, agrupando los sueldos según el puesto desempeñado. Es decir agrupando por el campo PUESTO y volcando la suma sobre el campo SUELDO. 6. Establezca criterios de validación y mensajes de error para los campos FECHA ALTA y SUELDO (evitando así que puedan introducirse datos que no corresponden a este tipo de campos) 7. Filtre la lista para obtener sólo los empleados que desempeñan el PUESTO “Administrativo”. Establezca un Filtro avanzado con este mismo criterio que copie el resultado del Filtro en otra hoja.
Excel XP avanzado
91
Tema 5: Bases de datos en Excel (listas)
92
Ed. 3.1
Excel XP avanzado
Ed. 3.1
Tema 6: Plantillas y Marcos en Excel
TEMA 6 PLANTILLAS Y MACROS EN EXCEL 6.1. Concepto de plantilla. Utilidad 6.2. Uso de plantillas predeterminadas de Excel. Crear Plantillas de Libro 6.2.1. Plantillas predeterminadas en Excel 6.2.2. Crear Plantillas de Libro 6.3. Crear plantillas personalizadas con Excel 6.4. Uso y modificación de Plantillas Personalizadas 6.5. Concepto y Creación de Macros 6.5.1. Ejemplo de macro asignada a un botón de Barra 6.6. Ejecutar Macros 6.7. Nivel de Seguridad de Macros
6.1. Concepto de Plantilla. Utilidad Hasta ahora siempre que ha comenzado a trabajar con Excel, creando un nuevo Libro de trabajo, ha utilizado la llamada Plantilla Normal. Una plantilla tiene una serie de características determinadas en lo que se refiere a fuente, tamaño, anchos de columna: en la plantilla por defecto de Excel (la Plantilla Normal) la fuente es Arial, el tamaño es 10, ancho de columna 10,71, alto de fila 12,75, etc. Pero estos valores iniciales pueden modificarse creando nuestras propias Plantillas (en este caso se denominan Plantillas de Libro o de Hoja). A partir de aquí, cuando abra un nuevo Libro de trabajo con la nueva plantilla, la Hoja tendrá las características que hayamos asignado a la nueva Plantilla y no los valores por defecto de la Plantilla Normal. Por otro lado, podemos crear también Plantillas Personalizadas que se utilizan para poder crear modelos de documentos básicos en la gestión empresarial (albaranes, facturas, horarios, cuadros de amortización, etc.). De esta forma, además de configurar opciones de características similares a las “Plantillas de libro”, podemos tener el formato preparado para no tener que repetirla cada vez que haya que utilizar, por ejemplo, una factura.
Excel XP avanzado
93
Tema 6: Plantillas y Marcos en Excel
Ed. 3.1
6.2. Uso de Plantillas predeterminadas en Excel. Crear Plantillas de Libro 6.2.1. Plantillas predeterminadas en Excel Además de la Plantilla Normal Excel ofrece a los usuarios una serie de plantillas útiles para facilitar su trabajo con la Hoja de Cálculo. Dichas plantillas pueden obtener a través de la opción Nuevo del Menú Archivo. Una vez seleccionada esta opción nos encontramos con la Ventana Nuevo Libro donde podemos hacer “clic” sobre el enlace “En mi PC”. Excel nos muestra el cuadro de diálogo Plantillas donde en la Pestaña “Soluciones de Hoja de Cálculo” nos muestra algunas plantillas de trabajo (si hace “clic” sobre el botón “Plantillas de Office Online” Excel conectará con la Web correspondiente en Internet para mostrar más plantillas):
Veremos también, en los próximos puntos cómo las Plantillas creadas por el usuario también se abrirán/utilizarán desde esta opción (Nuevo del Menú Archivo)
6.2.2. Crear Plantillas de Libro Las Plantillas de Libro van a tener un aspecto muy similar a la Plantilla Normal (cuando usted abre un nuevo libro y visualiza una hoja de cálculo nueva en blanco), pero aplicando la estructura, configuración, formatos… deseados para la nueva plantilla.
94
Excel XP avanzado
Ed. 3.1
Tema 6: Plantillas y Marcos en Excel
El proceso a seguir para crear una Plantilla de Libro de Trabajo es el siguiente: •
Abrir un nuevo libro de trabajo (con el Botón Nuevo de la Barra de Herramientas Estándar).
•
Aplique los cambios deseados: número de Hojas en el libro, ancho de columna, alto de fila, fuente por defecto, tamaño por defecto, márgenes, tamaño y orientación del papel, etc. En realidad, es este punto el que va a marcar las diferencias con la Plantilla Normal.
•
Seleccione la opción Guardar Como del Menú Archivo. En la lista desplegable “Guardar como Tipo” seleccione “Plantilla”.
•
Asigne un nombre a la nueva plantilla y haga “clic” sobre el botón Guardar (el archivo se guarda con una nueva extensión .XLT a diferencia de los libros de trabajo normales .XLS. Además, las plantillas se guardan por defecto en la carpeta específica: C:\Documents and Settings\Usuarios\Datos de Programa \Microsoft\Plantillas)
Para usar cualquier Plantilla de Libro creada por usted ha de seleccionar la opción Nuevo del Menú Archivo. En la ventana “Nuevo Libro” que aparece en el lateral derecho seleccione el Enlace “En mi PC”. En la ventana, ya estudiada con anterioridad, y en la Pestaña “General” seleccione su Plantilla para, finalmente, hacer “clic” sobre el botón Aceptar.
Excel XP avanzado
95
Tema 6: Plantillas y Marcos en Excel
Ed. 3.1
En el ejemplo que se muestra puede seleccionar la Plantilla Normal (llamada “Libro”) o la Plantilla llamada “Plantilla Comic 12” que permitiría usar un libro de trabajo en blanco donde la fuente por defecto es “Comic Sans”, tamaño 12 con un ancho de columna de 12 puntos y un alto de fila de 15 puntos quedará siempre fija.
6.3. Crear Plantillas personalizadas con Excel A lo largo del manual y de su propia práctica con Excel habrá creado numerosos Libros de Trabajo (listados de productos, control de ventas, bases de datos, etc.) que podrá necesitar en numerosas ocasiones. Con estos mismos libros habrá ocasiones donde le gustaría que la base de los mismos fuese siempre la misma y no se modificase al utilizar las opciones “Guardar” o “Guardar Como”. Para ello puede crear las llamadas Plantillas Personalizadas, que podrá usar y modificar pero que al guardarlas en disco se almacenarán con formato de libro de trabajo, por lo que la base de la plantilla (formatos, configuraciones, fórmulas…) quedará siempre en blanco. De esta forma, en una Plantilla Personalizada ya sólo tendrá que introducir los datos variables (nombres de clientes específicos, cantidades a calcular específicas…), pero no tendrá que aplicar formatos, configuraciones, crear fórmulas… cada vez puesto que ya han sido creados en la Plantilla. Para ello ha de seguir el siguiente proceso: •
96
Cree su plantilla introduciendo todos los datos fijos: rótulos, bordes, fuentes, tamaños, formatos de celda, anchos de columna, altos de fila, fórmulas, funciones, objetos. Es decir, aquellos datos que no van a modificarse, que van a ser siempre los mismos.
Excel XP avanzado
Ed. 3.1
Tema 6: Plantillas y Marcos en Excel
•
Aunque no es obligatorio es muy conveniente que proteja los datos, dejando sólo libres aquellas celdas donde se deben introducir los datos variables (por ejemplo, en la plantilla de una factura hemos de dejar libres las celdas donde hemos de introducir la referencia, el concepto, el precio…). Esta operación se realiza para evitar borrados o equivocaciones innecesarias.
•
En relación al punto anterior, si protege los datos, es necesario que utilice la opción Proteger del Menú Herramientas (para que la protección de celdas se haga efectiva)
•
Finalmente, como ya se estudió en puntos anteriores, guarde su plantilla a través de la opción Guardar Como del Menú Archivo. En el cuadro de diálogo seleccione “Plantilla” en la lista desplegable “Guardar como tipo”, asigne un nombre a la plantilla y, finalmente, haga “clic” sobre el botón Guardar.
Para comprender mejor el proceso vamos a convertir uno de los trabajos ya realizados en temas anteriores en una Plantilla de trabajo: •
Abra el libro de trabajo “Financieras” (ejercicio realizado en el tema 8 sobre el uso de funciones financieras, y que sería ideal convertir en plantilla para su uso habitual). Para evitar pérdidas innecesarias recordar que el proceso de protección seguirá los pasos siguientes: seleccionar la opción Guardar Como del Menú Archivo. Seleccione en “Guardar como tipo” la opción Plantilla, asigne el nombre “Plantilla Préstamos” y haga “clic” sobre el botón Guardar.
•
Elimine todas las hojas del libro a excepción de las dos hojas realizadas en los ejercicios 2 y 3 del tema 8: en una hoja creábamos una tabla financiera (introducción de una cantidad para que apareciesen automáticamente todas las posibilidades del préstamo en relación a diferentes intereses y años de amortización), y en la otra creábamos cuatro pequeñas tablas de simulación para calcular el interés, la cantidad a pagar, el tiempo o la cantidad a solicitar. Finalmente, ambas hojas presentarán el siguiente aspecto:
Excel XP avanzado
97
Tema 6: Plantillas y Marcos en Excel
•
Ed. 3.1
Para evitar que aparezca “0,00” en la Hoja superior o errores en las fórmulas (NUM, DIV 0) es necesario utilizar la función condicional =SI de la siguiente forma: En los datos superiores (Cantidad Solicitada): =SI(D1=””;””;ABS(PAGO(B$3%/12;$A4*12;$D$1;0))) En los “Cálculos Financieros” (hoja a la derecha), por ejemplo en B10: =SI(B8=””;””;ABS(PAGOINT(B8%/12;1;B7*12;B6)))
De esta forma utilizamos la función condicional =SI para evitar mensajes de error o que aparezcan ceros en la fórmulas que, si bien no afectan al resultado final, hacen que la presentación no
98
Excel XP avanzado
Ed. 3.1
Tema 6: Plantillas y Marcos en Excel
sea correcta. Para ello debe “preguntar” con esta función si el contenido de una celda (celda de control) está en blanco, para que el resultado de la función siga en blanco o se calcule el resultado (es decir =SI(celda=””;””;…) •
Es el momento de proteger algunas celdas para que no se pueda introducir información en ellas (puesto que ya están los títulos, fórmulas… en las mismas), y dejar “libres” otras celdas para la introducción de datos (sobre todo, aquellas celdas donde hay que introducir valores numéricos para que puedan producirse los cálculos). Recordar que el proceso de protección seguirá los pasos siguientes: En la primera hoja (tabla financiera) debe seleccionar la casilla D1 (donde va a introducir la cantidad) ya que es la única que va a quedar “libre”. A continuación seleccione el Menú Formato, Opción Celdas y, en la Pestaña Proteger, desactive la casilla “Proteger” y haga “clic” sobre Aceptar. Finalmente, seleccione el Menú Herramientas, Opción Proteger, Subopción Proteger Hoja y haga “clic” sobre Aceptar. A partir de ese momento sólo podrá introducir información en D1. En la segunda hoja hemos de seguir el mismo proceso seleccionando en primer lugar las celdas que han de quedar “libres”, es decir, las que deben permitir la introducción de datos: utilizando la tecla “Control” (a partir del segundo rango) seleccione los rangos B6:B8, B18:B20, E6:E8 y E18:E20 (“libere” dichas celdas y proteja el resto de la Hoja)
Excel XP avanzado
99
Tema 6: Plantillas y Marcos en Excel
•
Ed. 3.1
Todas las celdas que han quedado “libres” en las dos hojas deben permitir únicamente la entrada de datos numéricos (puesto que se trata de las cantidades que necesitan las fórmulas de las mismas hojas para realizar los cálculos). Seleccionar todas las celdas indicadas para, a continuación, seleccionar el Menú Datos y la opción Validación. En el cuadro de diálogo que aparece hemos de indicar que el único tipo de valor permitido es un número y, en Estilo “Límite”, que aparezca el mensaje “Sólo pueden introducirse valores numéricos”.
Ya ha finalizado su Plantilla “Plantilla Préstamos”. No se olvide guardar su trabajo (botón “Guardar” de la Barra de Herramientas Estándar o Menú Archivo, opción Guardar)
100
Excel XP avanzado
Ed. 3.1
Tema 6: Plantillas y Marcos en Excel
6.4. Uso y modificación de Plantillas Personalizadas Cuando necesite utilizar su Plantilla “Plantilla Préstamos”, como ya se estudió en puntos anteriores, debe seleccionar la Opción Nuevo del Menú Archivo. En la Ventana “Nuevo Libro” seleccione el Enlace “En mi PC”. Finalmente, seleccione “Plantilla Préstamos” en la Pestaña “General” y haga “clic” sobre el botón Aceptar.
Comprobará como en la Barra de Títulos se ha abierto un Libro de Trabajo nuevo denominado “Plantilla Préstamos1” (al igual que con la Plantilla Normal se denominaban Libro1, Libro2…). Es decir, ya está usted trabajando con un libro de trabajo (.XLS) y no con una plantilla. Cuando lo que necesite, en cambio, es modificar su Plantilla (para modificar algún formato, alguna fórmula…) no debe seguir este proceso. En este caso, debe utilizar la opción Abrir del Menú Archivo para, a continuación, seleccionar la ruta donde se guardan las plantillas de Excel (recuerde: C:\Documents and Settings\Usuarios\Datos de Programa\ Microsoft\Plantillas), seleccionar la Plantilla que se desea abrir para poder ser modificada (por ejemplo “Plantilla Préstamos”) y hacer “clic” sobre el botón Aceptar. Debe tener usted en cuenta que ahora no estamos trabajando con una Hoja de Cálculo de un Libro de Trabajo sino con la propia Plantilla original (.XLT) por lo que cualquier modificación en la misma se guardará en la plantilla y sus cambios serán permanentes en la misma.
Excel XP avanzado
101
Tema 6: Plantillas y Marcos en Excel
Ed. 3.1
6.5. Concepto y Creación de Macros Se habrá dado cuenta que muchas operaciones que realiza en Excel son rutinarias, es decir, que se repiten más o menos periódicamente. Pues bien, para no tener que hacer las mismas operaciones una y otra vez, las aplicaciones de Microsoft Office (Word, Excel y Access) permiten “guardar” un grupo de operaciones en un pequeño programa llamado “Macro” que bastará con ejecutar para que todas las operaciones se realicen. Por tanto, una macro es en realidad un programa que le va a permitir automatizar operaciones rutinarias y útiles. Además, las macros le van a permitir ahorrar tiempo y trabajo, además de reducir las posibilidades de error (puesto que sólo hay que realizar las operaciones una vez mientras se está creando la macro) Por otro lado, hemos de indicar que para crear macros no es necesario conocer ningún lenguaje de programación (Excel traduce automáticamente las macros creadas para que puedan ser ejecutables) pero, si se desea, pueden ampliarse las posibilidades de las macros gracias al lenguaje de macros que utiliza Excel: Visual Basic (VBA) Para crear una macro debe tener todos los libros cerrados a excepción del libro sobre el que va a crear la misma (libro en blanco normalmente). Después, debe seguir el siguiente proceso:
102
•
Seleccione el Menú Herramientas y la Opción Macro. Elija a continuación la Opción Grabar nueva macro.
•
En el cuadro de diálogo Grabar Macro, asigne un nombre a la nueva macro y, si lo desea, asígnele una combinación de teclas CTRL + ”carácter” (necesario si no va a asignar la macro a un botón o a una barra de herramientas). Asignar una combinación de teclas a la macro es muy útil para ejecutarla directamente cuando se desee sin tener que utilizar los menús (debe tener cuidado para no asignar combinaciones ya utilizadas por Excel: Control+N para Negrita, Control+G para Guardar, Control+P para Imprimir, etc.)
Excel XP avanzado
Ed. 3.1
Tema 6: Plantillas y Marcos en Excel
•
En el mismo cuadro de diálogo ha de seleccionar donde ha de guardarse la macro: Libro de macros personal, Este libro o Libro nuevo. Le recomendamos que utilice normalmente la opción Libros de macros personal si quiere disponer de la macro en todos los libros que utilice (aunque habrá casos donde sólo le interesará la macro para el libro actual: seleccione en ese caso “Este libro”). Finalmente, haga “clic” sobre el botón Aceptar.
•
Comprobará como aparece una nueva Barra de Herramientas: “Detener Grabación”. Es a partir de este momento cuando ha de utilizar todos los comandos que desee incorporar a la macro que está grabando (opciones de archivo, de configuración de página, de formato, de filas y columnas…). Cuidado con no utilizar ningún comando por accidente puesto que se grabará en la macro.
•
Cuando finalice, haga “clic” sobre el botón Detener Grabación (botón de la izquierda) de la Barra de Herramientas .
Una vez realizadas todas estas operaciones ya tiene usted creada la macro, pero lo normal es que el proceso no acabe ahí, sino que desee prepararla para que su posterior ejecución sea sencilla. Ya hemos visto como puede asignarse una combinación de teclas para ejecutar la macro (Control+tecla), pero otra forma útil de presentar las macros, de forma que su ejecución sea fácil, sería crear una Barra de Herramientas para incorporar a ella todas las Macros que se deseen. Para ello, ha de seguir el siguiente proceso: •
Seleccione el Menú Ver, Opción Barras de Herramientas. Elija a continuación la Opción Personalizar.
•
Haga “clic” sobre el Botón Nueva en la Pestaña Barras de herramientas e introduzca el nombre que desee asignarle. Finalmente, haga “clic” sobre Aceptar.
•
Seleccione la Pestaña Comandos y seleccione la Categoría Macros. Finalmente, haga “clic” sobre la opción Personalizar Botón y arrástrela hasta la nueva Barra de Herramientas.
•
A continuación, haga “clic” sobre el botón Modificar Selección. En primer lugar, seleccione la opción Cambiar imagen del botón y elija la imagen deseada. Después seleccione la opción
Excel XP avanzado
103
Tema 6: Plantillas y Marcos en Excel
Ed. 3.1
Asignar Macro (en el mismo botón). En el cuadro de diálogo que aparece seleccione el nombre de la Macro y haga “clic” sobre el botón Aceptar. Por último, seleccione la opción Nombre, e introduzca el texto que ha de aparecer como mensaje de ayuda junto al botón. Finalmente, haga “clic” sobre el botón Cerrar.
Otra forma de poder acceder a la Macro fácilmente, sería a través de un botón de macro (sobre todo, si la Macro ha sido guardada en un Libro). Para ello, abra el Libro donde desea insertar un botón de macro y siga el siguiente proceso: •
Active la Barra de Herramientas Formulario.
•
Seleccione el Botón “Botón”, sitúese en la zona de la hoja donde quiera Insertar el botón, haga “clic” y arrastre hasta darle el tamaño deseado. En ese momento, aparecerá el cuadro de diálogo Asignar Macro. Seleccione la macro que desea asignar al botón y haga “clic” sobre el botón Aceptar.
•
Para cambiar el texto del botón haga “clic” sobre el mismo e introduzca el texto deseado. Si en el futuro desea cambiar cualquier característica del botón de macro creado, haga “clic” sobre el mismo con el botón derecho (Menú contextual)
6.5.1. Ejemplo de macro asignada a un botón de Barra Vamos a desarrollar a continuación un ejemplo de creación de macro, asignándola posteriormente a un botón de Barra de Herramientas.
104
Excel XP avanzado
Ed. 3.1
Tema 6: Plantillas y Marcos en Excel
La macro a realizar va a permitir abrir un libro de trabajo, imprimir 3 copias del mismo y cerrarlo nuevamente. El proceso a seguir para crear dicha macro ha de ser el siguiente: •
Sobre un libro en blanco, seleccione el Menú Herramientas, Opción Grabar nueva Macro.
•
En el cuadro de diálogo Grabar Macro asignaremos el nombre Imprimir a la Macro, la combinación de teclas CTRL+i y la opción “Libro de macros personal” en la lista desplegable “Guardar macro en”. Finalmente, haga “clic” sobre el botón Aceptar.
•
Haga “clic” sobre el Botón Abrir, seleccione la carpeta adecuada y el libro de trabajo que desea abrir. Finalmente, haga “clic” sobre el Botón Abrir.
•
Una vez abierto, seleccione la Opción Imprimir del Menú Archivo. Indique “3” en el número de copias a imprimir y haga “clic” sobre Aceptar.
•
Seleccione la Opción Cerrar del Menú Archivo.
•
Finalmente, haga “clic” sobre el Botón Detener Macro.
•
Para asignar la macro a un Botón de Barra de Herramientas, seleccione la Opción Barras de Herramientas del Menú Ver y la Opción Personalizar. En la Pestaña Barras de Herramientas, haga “clic” sobre el Botón “Nueva” y asígnele el nombre Imprimir Fichero. En la Ficha Comandos, seleccione la Categoría Macros y siga el proceso de asignación del botón explicado anteriormente.
6.6. Ejecutar Macros Para ejecutar una macro dependerá de las opciones seleccionadas en el punto anterior. Puede hacerse de las siguientes formas: •
A través del Menú Herramientas, Opción Macros. Seleccione la macro que desea ejecutar y haga “clic” sobre el botón Ejecutar (en este mismo cuadro de diálogo se encuentra el botón Eliminar, si quiere borrar una macro)
Excel XP avanzado
105
Tema 6: Plantillas y Marcos en Excel
Ed. 3.1
•
A través de la combinación de teclas (CTRL+”carácter”) que haya asignado a la Macro en el proceso de creación de la misma.
•
A través del botón correspondiente de una Barra de Herramientas, si es que decidió asignarle un botón de este tipo.
•
A través del botón de macro en el libro donde lo creó.
6.7. Nivel de Seguridad de Macros Excel permite establecer el nivel de seguridad con el que se podrán abrir las macros de un determinado Libro de trabajo. Para ello, hemos de seleccionar el Menú Herramientas, la Opción Macros y la Subopción Seguridad: En este cuadro de diálogo podemos establecer los siguientes Niveles de Seguridad:
106
•
Muy Alto: sólo se ejecutan macros instaladas en ubicaciones consideradas “de confianza”.
•
Alto: se ejecutan las macros “de confianza”, deshabilitándose el resto.
•
Medio: se puede elegir entre ejecutar o no macros.
•
Bajo: admite la ejecución de todas las macros (no recomendable)
Excel XP avanzado
Ed. 3.1
Tema 6: Plantillas y Marcos en Excel
IDEAS CLAVE
• Además de utilizar la Plantilla Normal de Excel, puede usted crear sus propias Plantillas lo que le permitirá ahorrar trabajo y tiempo. •
Para modificar las características de la Hoja/Libro que se abra debe crear una Plantilla de Libro o de Hoja de trabajo. Para ello debe realizar todas las modificaciones necesarias (fuentes, tamaños, estilos, ancho de columna, alto de fila…). Cuando finalice ha de seleccionar la Opción Guardar Como del Menú Archivo y, en la lista desplegable “Guardar como tipo”, seleccionar “Plantilla”. Asigne un nombre a la plantilla y haga “clic” sobre el botón Guardar.
•
Si necesita crear un modelo de Hoja/Libro de trabajo habitual para usted y que necesita utilizar a menudo (por ejemplo, un modelo de factura, de presupuesto…) entonces debe crear una Plantilla personalizada. Para ello, debe introducir todos los datos fijos (títulos, formatos, fórmulas, bordes…), proteger las celdas necesarias (las que deben modificarse) y dejar libre otras (donde se deba introducir información), aplicar las “validaciones” necesarias (control de entrada de datos)… Finalmente, para guardarla lo hará a través de la Opción Guardar Como del Menú Archivo, seleccionando “Plantilla” en la lista desplegable “Guardar como tipo”, asignándole un nombre y haciendo “clic”, finalmente, sobre el Botón Guardar.
•
Para usar una Plantilla debe seleccionar la Opción Nuevo del Menú Archivo, seleccionar en la ventana “Nuevo Libro” el enlace “En mi PC” y, finalmente, la Plantilla que desea utilizar y el botón Aceptar. Se abre un nuevo libro de trabajo basado en la plantilla seleccionada.
•
Si lo que desea, en cambio, es modificar la Plantilla (no para usarla, sino para modificar sus formatos, fórmulas…) debe utilizar la Opción Abrir del Menú Archivo. En la ruta C:\Documents and Settings\Usuarios\Datos de Programa\Microsoft\Plantillas seleccione la Plantilla a modificar y haga “clic” sobre el Botón Abrir.
Excel XP avanzado
107
Tema 6: Plantillas y Marcos en Excel
108
Ed. 3.1
•
Las Macros son programas (creados en Visual Basic) que automatizan las operaciones que sean de interés para el usuario. Para crear una Macro ha de seleccionar la Opción Grabar Nueva Macro del Menú Herramientas. Una vez asignado nombre y combinación de teclas (opcional) realizará las operaciones que vayan a formar parte de la macro para, finalmente, hacer “clic” sobre el Botón Detener Grabación (Barra de Herramientas del mismo nombre)
•
Para ejecutar la Macro puede hacerlo a través de la Subopción Macros, dentro de la Opción Macros del Menú Herramientas. Puede hacerlo, mejor aún, a través de una combinación de teclas (indicada cuando se creó la macro) o a través de un Botón en una Barra de Herramientas.
Excel XP avanzado
Ed. 3.1
Tema 6: Plantillas y Marcos en Excel
AUTOEVALUACIÓN DEL TEMA 6 1. Las plantillas se guardan con la extensión: a) b) c) d)
.XLS .XLT .VBA .DOC
2. En una plantilla es conveniente: a) b) c) d)
Crear los formatos necesarios. Proteger los datos. Validar las entradas. Todas son correctas.
3. Para utilizar una Plantilla lo hará a través de la opción del Menú Archivo: a) b) c) d)
Nuevo. Abrir. Guardar. Guardar Como.
4. Modificará una Plantilla a través de la opción del Menú Archivo: a) b) c) d)
Nuevo. Abrir. Guardar. Guardar Como.
5. La ruta correcta de almacenamiento de las Plantillas es: a) C:\Documents and Settings\Usuarios\Datos de Programa\Microsoft\Plantillas. b) C:\Mis documentos\Plantillas. c) C:\Archivos de Programas\Microsoft Office\Plantillas. d) Ninguna es correcta. 6. Una macro es: a) b) c) d)
Un tipo de Fórmula. Un programa. Una opción de configuración. Un cálculo.
Excel XP avanzado
109
Tema 6: Plantillas y Marcos en Excel
Ed. 3.1
7. Las macros se crean a partir del Menú: a) b) c) d)
Archivo. Insertar. Formato. Herramientas.
8. Si se asigna una combinación de teclas a una macro, la primera tecla será: a) b) c) d)
Mayúsculas. Alt. Control. Alt Gr.
9. Las macros se pueden asignar a: a) b) c) d)
Menú Herramientas. Una Barra de Herramientas. Una combinación de teclado. Todas son correctas.
10. Las macros pueden almacenarse: a) b) c) d)
110
En la Hoja de trabajo. En el libro de trabajo. En el libro de macros personal. b) y c) son correctas.
Excel XP avanzado
Ed. 3.1
Tema 6: Plantillas y Marcos en Excel
EJERCICIOS 1. Realice los ejercicios planteados en los puntos 6.2.1, 6.3 y 6.5.1 2. Observe la siguiente Plantilla de una Factura:
Excel XP avanzado
111
Tema 6: Plantillas y Marcos en Excel
Ed. 3.1
Realice las siguientes operaciones:
Cree la plantilla anterior con todos los formatos y contenidos que aparecen. Guarde el diseño anterior como una plantilla llamada "Plantilla Factura". En la columna "Parcial" ha de crear la fórmula (para todas las filas) Parcial = Nº Unidades * Precio Unitario. "Total Bruto" será la suma de toda la columna parcial. "Descuento €" será el resultado de aplicar el "Descuento %" sobre el "Total Bruto" (Descuento % * Total Bruto / 100). La Base imponible será el resultado de restar al "Total”. "Iva €" será el resultado de aplicar el "Iva %" sobre la "Base Imponible" (Iva % * Base Imponible / 100) "Total Factura" será el resultado de sumar a la "Base Imponible" el "Iva €". Proteja todas las celdas donde no se pueden introducir datos (títulos y fórmulas) y libere las celdas donde sí pueda hacerlo (datos del cliente, nº de factura, código, concepto, nº unidades, precio, parcial, descuento % e iva %) Utilice la función =SI para evitar "ceros" y mensajes de error mientras las celdas están vacías. Utilice las opciones de validación para evitar que se introduzcan datos no numéricos donde sí deben ser de este tipo. Vuelva a guardar su trabajo (Botón Guardar). Cierre la Plantilla. Crear una factura ficticia usando la plantilla creada con anterioridad. 3. Crear una Barra de herramientas personalizada llamada PERSONAL.
112
Excel XP avanzado
Ed. 3.1
Tema 6: Plantillas y Marcos en Excel
4. Cree una macro, con un botón asignado a la barra PERSONAL, para poder utilizar la "Plantilla Factura" haciendo un "clic" sobre el mismo. 5. Suponga que en sus libros de trabajo utiliza a menudo la Fuente Times New Roman en tamaño 16 y negrita. Cree una macro (asignándola a un botón de la Barra de Herramientas PERSONAL) que le evite, cada vez que necesite dicha fuente, tener que activar las tres opciones. 6. Otra macro que puede realizar para su comodidad es aquella que le permita activar, por ejemplo, un tipo de letra y tamaño más normal. Así, cree una que le permita activar el tipo Arial, tamaño 11, con un ancho de columna 12 y un alto de fila 14 (asignar dicha macro a la Barra de herramientas PERSONAL).
Excel XP avanzado
113
Tema 6: Plantillas y Marcos en Excel
114
Ed. 3.1
Excel XP avanzado
Ed. 3.1
Tema 7: Formularios y Análisis de datos en Excel
TEMA 7 FORMULARIOS Y ANÁLISIS DE DATOS EN EXCEL 7.1. 7.2. 7.3. 7.4. 7.5.
Concepto de Formulario. Utilidad Análisis de la Barra de Herramientas Formularios Creación de Formularios Análisis Hipotético Manual Análisis Hipotético con Tablas de Datos 7.5.1. Tablas de datos de una variable 7.5.2. Tablas de datos de dos variables 7.6. Buscar Objetivo 7.7. Solver
7.1. Concepto de Formulario. Utilidad Hasta ahora toda la información que hemos introducido en nuestras hojas de cálculo ha sido siempre desde teclado (salvo alguna excepción con las bases de datos) pero, si profundizamos un poco más en Excel, podemos realizar presentaciones de Hojas de Cálculo más avanzadas donde la entrada de datos se produzca, además del teclado, desde listas desplegables, casillas de verificación, botones de opción… Para ello tenemos en Excel la Barra de Herramientas Formularios que va a permitirnos crear este tipo de entrada de la información.
7.2. Análisis de la Barra de Herramientas Formularios El primer paso, por tanto, para poder crear una Hoja-Formulario en Excel es activar la Barra de Herramientas Formularios. Para ello hemos de seleccionar “Formularios” en la Opción Barras de Herramientas del Menú Ver o haciendo “clic” con el botón derecho del ratón (para visualizar el menú contextual) sobre cualquier de las Barras en activo y seleccionando “Formularios”.
Excel XP avanzado
115
Tema 7: Formularios y Análisis de datos en Excel
Ed. 3.1
Vamos a analizar a continuación esta Barra de Herramientas con sus botones más importantes. Cualquiera de los botones se crea sobre la Hoja de Cálculo haciendo un “clic” y arrastrando hasta lograr el tamaño deseado: a) Etiqueta: permite la introducción de texto por teclado. b) Cuadro de Grupo: permite la introducción, dentro del cuadro, de casillas de verificación o de botones de opción. c) Botón: permite asignar un botón a una macro (crear un botón dentro de la hoja que ejecute una macro) d) Casilla de Verificación: las casillas de verificación permiten seleccionar una o varias opciones entre las existentes (por ejemplo si las opciones son: Airbag, Aire Acondicionado, Cierre Centralizado y ABS en las características de un coche, éste puede tener una o varias casillas activadas(seleccionadas a la vez) e) Botones de Opción: los botones de opción permiten seleccionar solamente una opción de entre todas las posibles (por ejemplo, si en el coche, las opciones son: color blanco, color rojo, color metalizado, color azul y color verde, éste puede tener solamente uno de los colores) f) Cuadro de Lista: nos permite elegir una opción de entre varias a través de una Barra de Desplazamiento. Los elementos del cuadro son utilizados de un rango externo (es decir, de un grupo de celdas que se encuentran en otra zona de la hoja de cálculo y vinculadas al cuadro de lista) g) Cuadro Combinado: un cuadro combinado nos permite elegir una opción de entre varias a través de una lista desplegable. Dicha lista ha de ser creada en otra zona de la hoja y vincularla al cuadro combinado.
116
Excel XP avanzado
Ed. 3.1
Tema 7: Formularios y Análisis de datos en Excel
h) Barra de desplazamiento: la barra de desplazamiento nos permite seleccionar un valor numérico situado entre dos límites establecidos por el usuario. i) Control de número: similar al anterior, sólo cambia la presentación del botón.
j) Propiedades: nos permite modificar las características del objeto seleccionado a través de un cuadro de diálogo. Se trata del botón, quizás más importante, ya que a través de él se configura cada uno de los objetos estudiados anteriormente (contenido, características, formato…) a través de diversas Pestañas tales como “Control” que permite configurar y controlar el funcionamiento del objeto.
k) Mostrar Código: accede a la ventana de Microsoft Visual Basic para modificar el objeto desde el lenguaje de macros. l) Alternar Cuadrícula: permite activar/desactivar las líneas de división de la Hoja sobre la que estamos trabajando. Quitar las líneas de división en pantalla es bastante habitual en trabajos terminados para conseguir una mejor presentación.
Excel XP avanzado
117
Tema 7: Formularios y Análisis de datos en Excel
Ed. 3.1
7.3. Creación de Formularios A la hora de crear una Hoja-Formulario es muy importante, como siempre, realizar todas las operaciones habituales de introducción de datos, creación de fórmulas, aplicación de formatos… y, además, crear los objetos necesarios desde la Barra de Herramientas Formularios. Veamos a continuación el siguiente ejemplo donde se presenta en una Hoja de Cálculo una Ficha-Nómina de un empleado de la empresa:
En esta Hoja-Formulario nos encontramos con las siguientes características:
118
•
Las celdas A1, A3, A4, A6, A8, A9, A10, B3, B4, C4, D4, D9 y C13 son textos introducidos por teclado.
•
Las celdas B8, B9 y B10 son valores numéricos introducidos por teclado (puede, si lo desea, mediante la opción de “Validación” controlar la entrada de valores: admitir sólo valores numéricos y mostrar un mensaje para no permitir otro tipo de entradas)
•
Las celdas C9, C10 y E9 son fórmulas: IRPF (C9)= Sueldo Base * Irpf % / 100; Seguridad Social (C10)= Sueldo Base * Seg Social % / 100; Sueldo Neto (E9)=Sueldo Base – Irpf € - Iva €.
Excel XP avanzado
Ed. 3.1
Tema 7: Formularios y Análisis de datos en Excel
•
El puesto es un “cuadro combinado” con los valores: Administrativo, Directivo, Oficial, Subalterno, Auxiliar y Comercial. Estos datos han de introducirse en un rango de celdas (en la misma hoja o en una hoja diferente) para asignarlos al cuadro combinado en la Pestaña “Control” del cuadro de diálogo “Formato de control” (se obtiene con un “clic” con el botón derecho del ratón –menú contextual- y seleccionando la opción “Formato de Control”)
•
El Estado Civil es un “Cuadro de Grupo” en el que se han insertado tres “Botones de Opción” (Casado, Soltero, Otro)
•
El Número de hijos es un “Control de Número” al que se le ha vinculado la celda C14 como resultado del mismo.
•
Otros Datos es un “Cuadro de Grupo” en el que se han insertado cuatro “Casillas de Verificación” (Coche propio, Habla inglés, Habla francés, Dominio Ofimática)
Para finalizar vamos a repasar tres conceptos importantes en la creación de Objetos pertenecientes a la Barra de Herramientas Formularios: •
Para crear el objeto-formulario, después de hacer “clic” sobre el botón deseado (cuadro de lista, cuadro combinado, casilla de verificación…), hacemos “clic” sobre la hoja y arrastramos hasta darle el tamaño deseado al mismo.
•
Para acceder al menú contextual del objeto ha de hacer “clic” con el botón derecho del ratón sobre el mismo. En dicho menú dispone de varias opciones importantes tales como “Modificar Texto”, “Asignar Macro” y “Formato de Control”.
Excel XP avanzado
119
Tema 7: Formularios y Análisis de datos en Excel
•
Ed. 3.1
Para acceder al cuadro de diálogo Formato de Control hemos de hacer “clic” con el botón derecho del ratón (menú contextual) y seleccionar la opción Formato de Control o hacer doble “clic” sobre el objeto (cuando tiene los puntos de anclaje).
7.4. Análisis Hipotético Manual Seguro que en la realización de los ejemplos y ejercicios de temas anteriores se ha planteado alguna vez preguntas tales como: ¿qué pasaría si determinado valor fuese distinto?, ¿qué ocurriría si…?. Incluso
120
Excel XP avanzado
Ed. 3.1
Tema 7: Formularios y Análisis de datos en Excel
es posible que haya realizado ya algún análisis hipotético cambiando algunos valores, por lo que las fórmulas (debido al recálculo automático) cambian de forma automática. Pues bien, en eso consiste un Análisis Hipotético Manual: cambiar uno o más valores de la hoja con la que se está trabajando de tal forma que las fórmulas que se ven afectadas se recalculan automáticamente. Por último indicar que, aunque se trata de una forma útil y sencilla de realizar un análisis hipotético, existen formas más avanzadas de realizarlo como veremos en los puntos siguientes.
7.5. Análisis Hipotético con Tablas de Datos Para mejorar nuestras posibilidades de análisis podemos crear una tabla a partir de unos datos determinados que contesten a la pregunta “¿Qué pasaría si…?”. Para explicar este tipo de Análisis Hipotético vamos a llevar a la práctica un ejemplo típico. La tabla resultante del Análisis Hipotético puede depender de una sola variable o de dos. Estudiemos a continuación ambos casos.
7.5.1. Tablas de datos de una variable Supongamos una empresa en la que los beneficios se calculan a partir de la fórmula: Beneficio = Ventas – Costes de Producción como se muestra a continuación:
Excel XP avanzado
121
Tema 7: Formularios y Análisis de datos en Excel
Ed. 3.1
Como puede comprobar se trata de una introducción sencilla de datos y dos filas de fórmulas (fila 7 y fila 8) donde se calcula: Costes Producción = Ventas * % Coste Producción / 100 y Beneficio Bruto = Ventas – Costes Producción. A partir de estos datos vamos a suponer que deseamos conocer los beneficios que se obtendrán si se reducen los costes de producción. Para ello, hemos de seguir los siguientes pasos:
122
•
Introduzca los datos necesarios para la nueva tabla de datos de la siguiente forma (los porcentajes estimados de coste de producción en la columna A y los beneficios obtenidos en la fila 10 –ha de repetir la misma fórmula de la fila 8):
•
Seleccione el rango completo de la nueva tabla (desde A10 hasta F15) para, a continuación, seleccionar el Menú Datos, Opción Tabla. En el cuadro de diálogo Tabla introduciremos en “Celda de entrada (columna)” el valor $D$3 (donde se encuentra el Coste de Producción real):
Excel XP avanzado
Ed. 3.1
Tema 7: Formularios y Análisis de datos en Excel
•
Finalmente, hacemos “clic” sobre el botón Aceptar. Excel calculará cuál sería el beneficio si el coste de producción fuese del 14%, del 13%, etc. (hasta el 10%), realizando un Análisis Hipotético partiendo del beneficio real y planteando ¿Qué pasaría si el coste de producción fuese del 14%?, ¿y si fuese del 13%?... El resultado obtenido será el siguiente:
7.5.2. Tablas de datos de dos variables También nos encontramos en ocasiones donde el análisis que se pretende depende de dos variables. Para estudiar este caso veamos un ejemplo de características similares al anterior, pero con una variable más (el porcentaje de gastos):
En este caso se ha incorporado una nueva variable: el porcentaje de Gastos. Para ello, en la fila 9, calculamos: Gastos = % Coste de
Excel XP avanzado
123
Tema 7: Formularios y Análisis de datos en Excel
Ed. 3.1
Producción * Ventas / 100. Así, tendremos dos variables (el coste de producción y los gastos) por lo que la nueva tabla tendrá el siguiente aspecto (incluyendo la fórmula del Beneficio Anual en A12):
Para realizar el análisis con las dos variables hemos de seleccionar la nueva tabla (desde A12 hasta F18) y seleccionar la Opción Tabla del Menú Datos: En dicho cuadro de diálogo hemos de introducir (con dos variables) una Celda de entrada para la fila (el % de gastos en este caso: $D$4) y una Celda de entrada para la columna (el % de Coste de producción: $D$3). Obtendremos, una vez que hagamos “clic” sobre el botón Aceptar, el siguiente resultado:
124
Excel XP avanzado
Ed. 3.1
Tema 7: Formularios y Análisis de datos en Excel
De esta forma, una vez más, contestamos a las preguntas del tipo ¿qué beneficio obtendríamos si el coste de producción fuese del 10% y los gastos fuesen del 5%?.. En esa situación Excel nos muestra que el Beneficio sería en ese caso de 7.380 €.
7.6. Buscar Objetivo La opción Buscar Objetivo nos permite buscar un valor adecuado para que se cumpla una determinada condición. Estudiemos esta opción con un ejemplo. Imaginemos que una empresa calcula los beneficios obtenidos durante el año en base a la siguiente fórmula: Ventas-Costes-Gastos-Impuestos:
Supongamos que la empresa se hace la siguiente pregunta: ¿cuál ha de ser el coste de producción para obtener un beneficio de 200.000 €?. Se trata de un planteamiento lógico en el mundo empresarial que podría resolverse de la siguiente forma: •
Seleccionar la opción Buscar Objetivo del Menú Herramientas. Nos encontramos con el siguiente cuadro de diálogo (Buscar Objetivo):
Excel XP avanzado
125
Tema 7: Formularios y Análisis de datos en Excel
Ed. 3.1
En el mismo tenemos que introducir los valores en “Definir la celda” ($B$12 porque es la posición donde se encuentra el Beneficio real), “Con el valor” (200000 ya que es el beneficio que se pretende alcanzar como hipótesis) y “Para cambiar la celda” ($B$3 porque es la posición donde tenemos el porcentaje de costes) •
Al hacer “clic” sobre el botón Aceptar obtenemos un nuevo cuadro de diálogo (Estado de la búsqueda de objetivo):
En este momento se encuentra con dos posibilidades: hacer “clic” sobre el botón Aceptar, con lo que las celdas afectadas (B12 y B3) cambiarán a los nuevos valores (el nuevo Beneficio 200.000 € y el nuevo Porcentaje de Costes 17%), o hacer “clic” sobre el botón Cancelar, con lo que las celdas afectadas mantendrán sus valores originales (185.854 € y 21% respectivamente). En el caso de “Aceptar” obtendría el siguiente resultado:
7.7. Solver Por último, la última estrategia de análisis es Solver. Esta opción va a permitirnos conocer el valor de una celda, dependiendo de más de una variable e, incluso, estableciendo condiciones.
126
Excel XP avanzado
Ed. 3.1
Tema 7: Formularios y Análisis de datos en Excel
Para comprenderlo mejor estudiaremos un nuevo ejemplo. Se trata de una empresa suministradora de material de papelería e informática, que desea conocer el número de unidades que ha de vender de cada producto para obtener un beneficio neto de 300.000 €. La tabla inicial podría ser la siguiente:
Para que Excel nos muestre el número ideal de unidades de cada producto a vender para obtener el “Total” de 300.000 € (estableciendo una serie de condiciones), hemos de seguir los siguientes pasos: •
Seleccionar la Opción Solver del Menú Herramientas. Nos encontramos con el siguiente cuadro de diálogo:
•
En la “Celda objetivo” seleccionaremos la celda $D$12 ya que es donde se calcula el total final.
Excel XP avanzado
127
Tema 7: Formularios y Análisis de datos en Excel
Ed. 3.1
•
Seleccione el “Valor de la celda objetivo”: podemos escoger entre los botones de opción “Máximo”, “Mínimo” o “Valores de” (que es la opción que seleccionaremos para introducir el beneficio esperado de 300.000 €)
•
En la opción “Cambiando las celdas” ha de indicar o seleccionar el rango de celdas que deben cambiar de valor (en nuestro ejemplo se trata del rango $C$6:$C$10, ya que son estas celdas las de las unidades vendidas que deben cambiar de valor para obtener el beneficio esperado)
•
En la opción “Sujetas a las siguientes restricciones”, utilizando el botón Agregar, podemos añadir las condiciones deseadas para que el objetivo se cumpla (por ejemplo: número de unidades mínimas y máximas que han de venderse de cada producto)
Con todas estas modificaciones el cuadro de diálogo “Parámetros de Solver” presentará el siguiente aspecto:
•
128
Finalmente haga “clic” sobre el botón Resolver. En unos segundos, nos encontramos con el siguiente cuadro de diálogo “Resultados de Solver”:
Excel XP avanzado
Ed. 3.1
Tema 7: Formularios y Análisis de datos en Excel
•
En este cuadro de diálogo podemos realizar las siguientes operaciones: a) seleccionar si se desean utilizar los valores calculados o si, por el contrario, se desean mantener los valores originales; b) elegir, si se desea, el tipo de informe que Excel creará automáticamente como Hoja independiente, informándonos sobre las operaciones realizadas con “Solver” (Respuestas, Sensibilidad o Límites) Finalmente haga “clic” sobre el botón Aceptar. El resultado obtenido puede ser el siguiente:
Hoja de datos final con las unidades necesarias para alcanzar el beneficio esperado
Informe de respuestas seleccionado en el cuadro de diálogo "Resultados de Solver", creándose una nueva Hoja
Excel XP avanzado
129
Tema 7: Formularios y Análisis de datos en Excel
Ed. 3.1
IDEAS CLAVE
130
•
Una nueva forma de presentar la información en una Hoja de trabajo son los Formularios. Los objetos con los que vamos a trabajar van a permitirnos presentaciones más atrayentes, además de un ahorro de tiempo y trabajo.
•
Para Crear un Formulario hemos de activar la Barra de Herramientas Formularios (a través del Menú Ver, Opción Barras de Herramientas o utilizando el botón derecho del ratón –menú contextual- sobre cualquier barra activa), cuyos botones nos permitirá crear objetos tales como: Casillas de Verificación, Botones de Opción, Cuadros de Lista, Cuadros Combinados…
•
Una vez insertado el objeto en la hoja (con “clic” y arrastrar) podemos modificar sus propiedades haciendo “clic” con el botón derecho del ratón sobre el mismo y, a continuación, seleccionando la Opción Formato de Control.
•
Otro concepto que hemos tratado en este tema es la posibilidad de realizar Análisis que permiten contestarnos a preguntas del tipo ¿qué pasaría si…?.
•
Un primer tipo de Análisis son las Tablas de Datos que Excel puede crear a partir de una o dos variables. Esta opción la encontramos en el Menú Datos, Opción Tabla.
•
Otro tipo de Análisis es Buscar Objetivo que nos permite buscar un valor predeterminado para que se cumpla una determinada condición. Esta opción la encontramos en el Menú Herramientas, Opción Buscar Objetivo.
•
Por último, nos encontramos con la Opción Solver que nos permite conocer el valor de una celda dependiendo de más de una variable e, incluso, estableciendo condiciones. La Opción Solver se encuentra también en el Menú Herramientas.
Excel XP avanzado
Ed. 3.1
Tema 7: Formularios y Análisis de datos en Excel
AUTOEVALUACIÓN DEL TEMA 7
1. Los Cuadros de Grupo permiten agrupar: a) b) c) d)
Botones de Opción. Casillas de Verificación. Las dos son correctas. Ninguna es correcta.
2. Si desea crear un campo que se abra en forma de un objeto desplegable, deberá usar: a) b) c) d)
Cuadro de Lista. Cuadro Combinado. Control de Número. Todas son correctas.
3. Si desea activar o desactivar las líneas de división de la hoja activa deberá usar: a) b) c) d)
Mostrar Código. Alternar Cuadrícula. Formato. Ver Líneas.
4. El Menú donde encontramos la opción para crear una Tabla de Datos es: a) b) c) d)
Datos. Tabla. Herramientas. Ninguna es correcta.
5. El Menú donde podemos encontrar la Opción Buscar Objetivo es: a) b) c) d)
Datos. Tabla. Herramientas. Ninguna es correcta.
Excel XP avanzado
131
Tema 7: Formularios y Análisis de datos en Excel
Ed. 3.1
6. El Menú donde podemos encontrar la opción Solver es: a) b) c) d)
Datos. Tabla. Herramientas. Ninguna es correcta.
7. Las opciones “Celda de entrada (fila)” y “Celda de entrada (columna)” se utiliza en la opción: a) b) c) d)
Solver. Tablas de Datos. Buscar Objetivo. Todas son correctas.
8. La opción que nos permite buscar un valor adecuado para que se cumpla una determinada condición es: a) b) c) d)
Solver. Tablas de Datos. Buscar Objetivo. Todas son correctas.
9. La opción que nos permite conocer el valor de una celda dependiendo de más de una variable e, incluso, estableciendo condiciones es: a) b) c) d)
Solver. Tablas de Datos. Buscar Objetivo. Todas son correctas.
10. La creación de un Informe automático la encontramos en la opción: a) b) c) d)
132
Solver. Tablas de Datos. Buscar Objetivo. Todas son correctas.
Excel XP avanzado
Ed. 3.1
Tema 7: Formularios y Análisis de datos en Excel
EJERCICIOS
1. Realice los ejercicios planteados en los puntos 7.3, 7.5, 7.6 y 7.7. 2. Cree el siguiente formulario sabiendo que los campos “Fuma” y “Bebe” son casillas de verificación, mientras que los campos “Sexo” y “Color Preferido” son listas desplegables:
Excel XP avanzado
133
Tema 7: Formularios y Análisis de datos en Excel
134
Ed. 3.1
Excel XP avanzado
Ed. 3.1
Tema 8: Redes e Internet con Excel
TEMA 8 REDES E INTERNET CON EXCEL
8.1. 8.2. 8.3. 8.4.
Insertar Hipervínculos en hojas de cálculo Guardar Hojas de Cálculo como páginas web La Barra de Herramientas Web Compartir datos y protección en red 8.4.1. Crear y trabajar con un libro compartido 8.4.2. Áreas de trabajo compartidas
8.1. Insertar Hipervínculos en Hojas de Cálculo Un Hipervínculo es un enlace que se crea y que nos lleva directamente a otra celda de nuestro libro de trabajo, a otro libro de trabajo en nuestro PC, a otro libro de trabajo en otro ordenador de la red o a otro libro de trabajo en un servidor de Internet. Para crear un Hipervínculo hemos de seguir el siguiente proceso: •
Introduzca el texto que va a servir como soporte del Hipervínculo.
Excel XP avanzado
135
Tema 8: Redes e Internet con Excel
•
Ed. 3.1
Seleccione la Opción Hipervínculo del Menú Insertar o haga “clic” en el botón Hipervínculo de la Barra de Herramientas Estándar. Nos encontramos con el siguiente cuadro de diálogo:
Como puede comprobar en la parte izquierda de este cuadro de diálogo puede (a partir del texto que se encuentra en la celda seleccionada) crear un hipervínculo con: un archivo o página web (permite enlazar con otro archivo o con una página de Internet); un lugar de este documento (permite realizar un “salto” a otra celda de la hoja u a otra hoja); crear nuevo documento (crea un nuevo documento a partir del hipervínculo seleccionado); Dirección de correo electrónico (el hipervínculo enlaza con el software de correo electrónico para enviar un mensaje a un a dirección de email)
8.2. Guardar Hojas de Cálculo como Páginas Web Podemos crear una Página Web a partir de la información creada en una Hoja de Cálculo. Para ello, estando situados en la hoja que queremos convertir en Página Web, hemos de realizar los siguientes pasos: •
136
Seleccionar la opción Guardar Como del Menú Archivo o directamente la opción Guardar como Página Web del mismo Menú Archivo.
Excel XP avanzado
Ed. 3.1
Tema 8: Redes e Internet con Excel
•
En el cuadro de diálogo “Guardar como tipo” seleccionar la opción de lista desplegable “PáginaWeb” o “Página Web de un solo archivo”. Asigne nombre al archivo, seleccione si desea guardar “Todo el libro” o “Selección: Hoja”, asigne un título a la página con el Botón “Cambiar título” y, finalmente, haga “clic” sobre el botón Guardar Como.
A partir de ese momento el libro o la hoja seleccionada quedan guardadas en el disco con formato HTML (lenguaje de hipertexto con el que se crean las páginas web). Podrá utilizar este archivo para que forme parte de una web o similar “subiendo” la página a un Servidor de Internet.
8.3. La Barra de Herramientas Web Esta Barra de Herramientas se puede abrir, al igual que todas las demás, desde la Opción Barra de Herramientas del Menú Ver o con el menú contextual (“clic” con el botón derecho del ratón sobre una barra de herramientas). En ambos casos el nombre de la barra es “Web” y presenta el siguiente aspecto:
Excel XP avanzado
137
Tema 8: Redes e Internet con Excel
Ed. 3.1
A través de la misma podemos realizar operaciones habituales del Web, desde Excel y accediendo al Navegador (Internet Explorer habitualmente). Los botones más importantes son: •
Página de inicio: permite acceder a la página de inicio que tengamos configurada en nuestro navegador.
•
Favoritos: permite acceder a las opciones de “Favoritos” de nuestro navegador donde el usuario suele tener almacenadas páginas como “Favoritas” para un acceso más rápido y seguro.
•
Lista desplegable: permite introducir la ruta de la página web a visualizar.
8.4. Compartir datos y protección en red En las nuevas versiones de Excel es bastante interesante la posibilidad de que varios usuarios estén usando el mismo libro de trabajo a la vez, desde distintos ordenadores de la misma red. Para ello, ha de seleccionar la Opción Compartir Libro del Menú Herramientas. Nos encontramos con el siguiente Cuadro de Diálogo:
138
Excel XP avanzado
Ed. 3.1
Tema 8: Redes e Internet con Excel
Si en este cuadro de diálogo activamos la casilla “Permitir la modificación por varios usuarios a la vez…” estaremos permitiendo que otros usuarios tengan acceso al libro de trabajo. Al mismo tiempo, en la Pestaña “Uso avanzado” podremos configurar varias opciones sobre el acceso que otros usuarios tendrán al libro tales como qué cambios prevalecen en caso de conflicto, modo de actualizar cambios…
8.4.1. Crear y trabajar con un libro compartido Cada vez que se guarda un libro, en cualquiera de los puestos, los otros recibirán los cambios generándose un documento consolidado. Si dos usuarios han realizado modificaciones en los mismos puntos, al efectuar la consolidación aparecerán las correspondientes indicaciones sobre conflictos, en las que podrá decidir qué cambios desea conservar. A tener en cuenta: Existen ciertas funciones de Excel que no estarán disponibles a partir de que el libro haya sido compartido, por ejemplo: no se podrá establecer formatos condicionales, crear esquemas, crear listas, eliminar hojas de cálculo, trabajar con datos XML… Todas estas acciones deben efectuarse antes de compartir el libro.
Excel XP avanzado
139
Tema 8: Redes e Internet con Excel
Ed. 3.1
8.4.2. Áreas de trabajo compartidas Un área de trabajo compartida es un área alojada en un servidor Web en la que los miembros de un grupo pueden compartir documentos e información, mantener listas de datos relevantes y ofrecer a los demás miembros del grupo información actualizada sobre el estado de un proyecto. Las áreas de trabajo compartidas son sitios de Microsoft Windows SharePoint Services que se pueden abrir en un explorador de Web o en el panel de tareas Área de trabajo compartida de un programa de Microsoft Office integrado con los servicios de Windows SharePoint. Excel se integra perfectamente con éste servicio. El panel de tareas Área de trabajo compartida muestra la biblioteca en la que se almacena el documento abierto actualmente.
140
•
Biblioteca de documentos: puede almacenar documentos a los que tendrán acceso todos los miembros del área de trabajo compartida. El panel muestra la biblioteca en la que se almacena el documento abierto recientemente.
•
Lista de tareas: puede asignar elementos pendientes con fechas de vencimiento definidas a miembros del área de trabajo compartida. Si un miembro le ha asignado una tarea, puede comprobarla en la Lista de tareas. Cuando los otros miembros del equipo abran la Lista de tareas en el panel de tareas Área de trabajo compartida, podrán ver que usted ha completado la tarea.
•
Lista Vínculos: puede agregar hipervínculos a recursos o información de interés para miembros del área de trabajo compartida.
•
Lista Integrantes: muestra los nombres de usuario de los miembros del área de trabajo compartida. En el panel de tareas Área de trabajo compartida se incluye la información de contacto, como el estado de disponibilidad, el número de teléfono y la dirección de correo electrónico, y otras propiedades, de forma que los miembros puedan comunicarse fácilmente.
Excel XP avanzado
Ed. 3.1
Tema 8: Redes e Internet con Excel
•
Ficha Estado: muestra información importante sobre el documento actual: si está actualizado, si está en conflicto con una copia de otro miembro o si está desprotegido...
•
Ficha Información del documento: muestra propiedades asociadas con el documento: cuándo se modificó por última vez…
•
Alertas de correo electrónico: Puede utilizar alertas de correo electrónico para recibir notificaciones de cambios realizados en una lista, un elemento concreto (como el estado de una tarea) o un documento del área de trabajo compartida.
Los paneles de tareas Área de trabajo compartida y Actualizaciones de documentos son una novedad de Excel 2003. Otras mecanismos con los que cuenta Excel para trabajar en equipo, intercambiando información entre usuarios, sería a través de reuniones virtuales entre múltiples personas, usando: voz, video…
Excel XP avanzado
141
Tema 8: Redes e Internet con Excel
Ed. 3.1
IDEAS CLAVE
142
•
Debido a la gran difusión actual de las redes en general y de Internet en particular, Excel incluye herramientas de trabajo en red.
•
Existe un nuevo panel de tareas, denominado Área de trabajo compartida, desde la que puede crear nuevas áreas, añadir nuevos documentos, obtener actualizaciones y comunicarse con otros usuarios.
•
Otra utilidad importante que aprovecha el potencial del trabajo en red son los Hipervínculos. Se puede crear un Hipervínculo con otra zona de la Hoja de Cálculo, con otro Libro de trabajo en el mismo disco o en otro ordenador de la red, con una Página Web de Internet o con una dirección de Correo Electrónico.
•
Para crear un Hipervínculo disponemos del Botón Insertar Hipervínculo de la Barra de Herramientas Hipervínculo o también de la Opción Hipervículo del Menú Insertar.
•
También puede guardar una Hoja de Cálculo o Libro de Trabajo como una Página Web. Para ello debe seleccionar la Opción Guardar como Página Web del Menú Archivo.
•
Otra opción relacionada con Internet es la posibilidad de activar, en Excel, la Barra de Herramientas Web. A través de la misma puede acceder a “Mis Favoritos”, a la Página de Inicio que se tenga configurada desde el Navegador… Dicha Barra puede activarla a través de la Opción Barra de Herramientas del Menú Ver.
•
Por último, Excel nos ofrece también la posibilidad de permitir que varios usuarios a la vez puedan utilizar un mismo Libro de Trabajo. Para ello ha de activar la casilla correspondiente en la Opción Compartir Libro del Menú Herramientas.
Excel XP avanzado
Ed. 3.1
Tema 8: Redes e Internet con Excel
AUTOEVALUACIÓN DEL TEMA 8
1. Un enlace entre una celda de un libro de Excel y otra de un libro de la red, recibe el nombre de: a) b) c) d)
Enlace. Vínculo. Cadena. Hipervínculo.
2. La Opción para Compartir datos en la red se encuentra en el Menú: a) b) c) d)
Datos. Herramientas. Archivo. Insertar.
3. Para crear un Hipervínculo en la Hoja de Cálculo hemos de seleccionar la opción en el Menú: a) b) c) d)
Datos. Herramientas. Archivo. Insertar.
4. Para almacenar una Hoja o Libro como Página Web encontramos la opción en el Menú: a) b) c) d)
Datos. Herramientas. Archivo. Insertar.
5. La barra de herramientas Web se puede abrir desde el menú: a) b) c) d)
Herramientas. Ver. Archivo. Insertar.
Excel XP avanzado
143
Tema 8: Redes e Internet con Excel
Ed. 3.1
6. ¿Qué botón de la barra de herramientas Web permite introducir la ruta de la página a visualizar?: a) b) c) d)
Lista desplegable. Favoritos. URL. Ninguna es correcta.
7. Cuando se comparte un libro, ¿qué funciones no están disponibles?: a) b) c) d)
Crear listas. Crear formatos condicionales. a y b son correctas. Ninguna es correcta.
8. De los dominios siguientes, ¿cuál no es habitual? a) b) c) d)
.com .net .es Ninguna es correcta.
9. Los Hipervínculos sirven de enlace con: a) b) c) d)
Otra posición en el mismo Libro de Trabajo. Otro Libro de Trabajo en una red local. Una Página Web de Internet. Todas son correctas.
10. ¿Puede compartirse un Libro de Trabajo? a) b) c) d)
144
No, sólo puede ser usado por una persona a la vez. Sí, pero sólo desde el mismo ordenador. Sí, entre los distintos ordenadores de la red. Ninguna es correcta.
Excel XP avanzado
Ed. 3.1
Tema 8: Redes e Internet con Excel
EJERCICIOS
1. Desarrolle los diferentes ejemplos y ejercicios planteados a lo largo del tema.
Excel XP avanzado
145
Tema 8: Redes e Internet con Excel
146
Ed. 3.1
Excel XP avanzado
CURSO:
EXCEL XP AVANZADO GUÍA DE SOLUCIONES
Soluciones
II
Ed. 3.1
Excel XP avanzado
Ed. 3.1
Soluciones
Solución a las pruebas de Autoevaluación TEMA 1 1. 2. 3. 4. 5. 6. 7. 8. 9. 10.
d b c c b c b a d d
TEMA 2 1. 2. 3. 4. 5. 6. 7. 8. 9. 10.
c a b a d c b a b b
TEMA 3 1. 2. 3. 4. 5. 6. 7. 8. 9. 10.
b a c b b a d c d a
Excel XP avanzado
TEMA 4 1. 2. 3. 4. 5. 6. 7. 8. 9. 10.
c a c d b a d c d c
TEMA 5 1. 2. 3. 4. 5. 6. 7. 8. 9. 10.
b d c d b a b a a d
TEMA 7 1. 2. 3. 4. 5. 6. 7. 8. 9. 10.
c b b a c c b c a a
TEMA 8 1. 2. 3. 4. 5. 6. 7. 8. 9. 10.
d b d c b a c d d c
TEMA 6 1. 2. 3. 4. 5. 6. 7. 8. 9. 10.
b d a b a b d c d d
III
Soluciones
IV
Ed. 3.1
Excel XP avanzado