Excel Experto 2007

1

Matrices y Referencias Externas 1. FÓRMULAS DE MATRIZ

Normalmente, las funciones producen un único resultado. Sin embargo, a veces resulta interesante tener funciones que calculen varios resultados a la vez. Estas funciones se llaman fórmulas de matriz o matriciales, y su resultado puede ocupar varias celdas. Las fórmulas de matriz también se usan para ahorrar tiempo cuando escribimos fórmulas, ya que puedes sustituir varias fórmulas distintas por una sola fórmula de matriz. Por ejemplo, en una hoja de cálculo puedes usar una fórmula de matriz para aplicar la misma fórmula sobre distintos datos. En el siguiente caso, si no utilizas fórmulas de matriz, puedes calcular el total de dinero ingresado por la venta de unos coches multiplicando el número de coches vendidos por su precio (fila SUBTOTAL) y sumando estas cantidades (celda B9):

En lugar de realizar todas estas operaciones, podríamos haber Combinado todos los cálculos en una solo,

usando una fórmula de matriz o matricial:

Excel Experto 2007

2

En este caso, en la celda B8 tenemos la siguiente fórmula de matriz: {=SUMA (B5:C5*B6:C6)} Las fórmulas de matriz se muestran encerradas entre llaves ( { } ), Para diferenciarlas del resto de fórmulas, y para introducirlas debemos Aceptar la fórmula escrita con De esta forma, después de introducir la fórmula de matriz y pulsar esta combinación de teclas, Excel la colocará automáticamente entre Llaves ( { } ). En el ejemplo anterior, si, tras introducir la fórmula de matriz, Modificas esta fórmula y aceptas con Ten en cuenta que Excel no Introducirá una fórmula de matriz, sino que se tratará de una fórmula “normal”. Recuerda la combinación de teclas que tienes que pulsar Para aceptar la fórmula de matriz. Hemos dicho que una fórmula de matriz o matricial puede Devolver un resultado o varios; en este segundo caso, estos resultados se pondrían en varias celdas. El rango de celdas donde se colocarán los resultados de la fórmula de matriz se llama rango matricial. Ten en cuenta que todas las celdas del rango matricial compartirán la misma fórmula de matriz, por lo que no puedes eliminar celdas de un rango matricial.

2. CONSTANTES MARICIALES

En las fórmulas simples, puedes introducir referencias a celdas o valores constantes. Pues bien, en las fórmulas de matriz o matriciales también tenemos una forma de introducir valores constantes. A estos valores constantes en las fórmulas matriciales se les denomina constantes matriciales. Para incluir una constante matricial en una fórmula:

Escribe los valores directamente en la fórmula entre llaves ( { } ).

Como separador de columnas, tienes que utilizar el punto y coma ( ; ).

Para separar las filas, utiliza el símbolo de barra invertida ( \ ). Por ejemplo, para incluir la siguiente matriz como constante matricial:

10 20

30 40

Deberías escribir {10;20\30;40}.

Excel Experto 2007

3

Matrices y referencias externas Por ejemplo, en la siguiente imagen puedes ver una fórmula de matriz que se ha introducido en las celdas C1:D2:

Según esta fórmula matricial, hemos sumado las celdas A1:B2 y La matriz {10;11\20;21}. ¿Cómo ha hecho Excel esta suma?

Las operaciones de las fórmulas matriciales se hacen celda a celda, Dentro del rango seleccionado. Por ejemplo, en este caso se sumaría el valor de la celda A1 más 10, el valor de la celda B1 más 11, el valor

De A2 más 20 y el de B2 más 21. Puedes ver el resultado de estas Operaciones en la hoja de cálculo. Recuerda que las fórmulas de matriz no tienen nada que ver con las operaciones matemáticas sobre matrices, son dos cosas Distintas. Por ejemplo, aquí podrías multiplicar una matriz de (2x3) por otra de (2x3), produciendo un resultado de (2x3), cosa que no puedes hacer con las operaciones matemáticas de matrices. En este ejemplo que acabamos de ver, el rango matricial estaría formado por las celdas C1:D2 (es donde están los resultados de la fórmula de matriz). Si intentas eliminar el contenido de una de estas celdas individualmente, Excel te mostrará un mensaje diciendo que no puedes hacer eso.

Excel Experto 2007

4

Además de eliminar celdas, otras operaciones que no se pueden realizar en celdas pertenecientes al rango de una matriz son:

Cambiar el contenido de las celdas.

Mover celdas que forman parte de la matriz.

Insertar celdas en un rango matricial. Algunas de las operaciones que sí están permitidas son las siguientes:

Dar formato a cada una de las celdas del rango de forma Individual.

Copiar y pegar celdas de un rango matricial. Por lo tanto, si cambias una celda que pertenece a un rango matricial, debes tener en cuenta que no puedes hacer operaciones que cambien únicamente esta celda: tienes que cambiar todo el rango matricial, como si se tratase de una única celda. Para seleccionar un rango matricial de una fórmula de matriz, puedes utilizar el comando Ir a Especial del menú del botón En la ventana que aparecerá, selecciona la opción Matriz actual. Cuando se trabaja con fórmulas de matriz, cada elemento de la fórmula tiene que tener el mismo número de filas y de columnas. Si esto no es así, Excel expande las fórmulas de matriz. Por ejemplo, la fórmula ={1;2;3}*{2\3} se transformaría en ={1;2;3\1;2;3}*{2;2;2\3;3;3}. Para introducir una fórmula de matriz, tienes que seleccionar un rango de celdas igual al de la matriz resultante. Si no existe un valor expansible, aparecerá el valor de error #N/A. Por otra parte, si el resultado matricial es más grande que el rango seleccionado, los valores sobrantes no se mostrarán en la hoja de cálculo.

Excel Experto 2007

5

También hay varias cosas que tienes que tener en cuenta al trabajar con constantes matriciales:

Pueden ser números, texto, valores lógicos o incluso valores de error.

Si introduces texto, debe ponerse entre comillas.

En una misma constante matricial se puede incluir distintos Tipos de valores. Por ejemplo: {1;VERDADERO;2\"hola";55;10}.

No pueden estar formadas por fórmulas.

Todas las filas y todas las columnas de una constante matricial tienen que tener la misma longitud. Es decir, no puedes tener una fila con cinco elementos y otra con tres.

No pueden tener símbolos de moneda, paréntesis ni símbolos de porcentaje.

3. REFERENCIAS EXTERNAS (VÍNCULOS)

Sabemos que, en una celda, podemos hacer referencia a celdas que se encuentran en hojas de cálculo distintas, dentro de un mismo libro de trabajo. Excel también permite la creación de vínculos o referencias externas entre dos o más libros, de forma que la información que veas en un libro refleje los cambios que ocurren en los datos de otro libro. Es decir, que un vínculo o referencia externa es una referencia a una celda o a un rango de celdas que están en una hoja de cálculo de otro libro. Por ejemplo: podemos incluir los resultados de varios libros en un Libro resumen, de forma que se puedan comparar los resultados de cada uno de ellos de una forma rápida; además, podemos hacer que cualquier cambio en los libros fuente se refleje en el libro de resultados. También tienes que tener en cuenta que, al trabajar con libros pequeños, éstos son más fáciles de editar y más rápidos de abrir, guardar y calcular. De esta forma, podemos tener los datos en un libro Y analizarlos en otro libro distinto, asegurándonos de que los datos originales no van a ser modificados.

Excel Experto 2007

6

Hay una serie de conceptos que tienes que conocer:

Libro de origen (o fuente): es aquel libro que contiene los datos a los que hace referencia una fórmula que está en otro libro, a través de una referencia externa.

Libro de destino (también llamado libro dependiente o vinculado): es el libro que tiene los vínculos con el otro libro y que, por lo tanto, depende de los datos de este último.

Referencia externa (o vínculo): en un libro de Excel, es cualquier referencia a una celda, a un rango de celdas o a un nombre de otro libro de trabajo. En resumen, que en el libro de destino o dependiente hay una referencia externa a una o varias celdas que están en el libro origen. O sea, que, a través de esta referencia, en el libro destino podemos ver Y trabajar con los datos que hay en el libro origen.

Para que lo tengas más claro, podemos ver las relaciones que se establecen a través de una referencia externa o vínculo entre un libro de origen y un libro de destino en la siguiente imagen:

Excel Experto 2007

7

El libro de destino es donde se ha insertado el vínculo o referencia externa, mientras que el libro de origen es realmente el que tiene los datos, es decir, la información que se muestra A través del vínculo, Para crear referencias externas, primero tenemos que indicar la ruta o posición del archivo en el disco, seguido del nombre del libro entre corchetes ( [ ] ). A continuación figura el nombre de la hoja; si el nombre de la hoja de cálculo o del libro de la referencia tienen caracteres no alfabéticos, estos elementos figuran entre apóstrofos o comillas sencillas ( ' ). Luego aparece el símbolo de exclamación ( ! ) y, a continuación, la celda o rango de celdas al que hacemos referencia.

Aunque puede parecer complicado, la verdad es que la mayoría de veces no tendrás que escribir toda la referencia externa, ya que se puede introducir directamente pulsando con el ratón. Para seleccionar mediante el ratón celdas que estén en otros libros, podemos tener abiertos los dos libros (el de origen y el de destino o vinculado) y organizarlos en ventanas dentro de Excel (por ejemplo, desde el botón ORGANIZAR TODO de la ficha Vista de la Cinta de opciones). Luego iríamos escribiendo la fórmula adecuada en el libro de destino: escribiríamos el signo igual e iríamos introduciendo los distintos elementos de la fórmula (valores constantes, operadores, Referencias a celdas...). Para introducir una referencia externa, pulsaríamos en la celda o celdas del libro de origen o fuente. Excel irá construyendo la fórmula con las sintaxis correctas. Por supuesto, también podemos poner referencias externas con Funciones, como si se tratara de referencias a celdas del mismo libro. Si el libro al cual está vinculado el libro actual está cerrado, la referencia externa se muestra en la barra de fórmulas con la ruta de acceso completa, tal como hemos indicado. En cambio, si el libro de origen o fuente está abierto, la referencia externa se muestra sin la ruta de acceso. Cuando trabajes con celdas con mucho texto o que tengan fórmulas largas (como en ocasiones ocurre con las referencias externas), puede ser interesante cambiar el tamaño de la barra de fórmulas, para poder ver todo el contenido de la celda. Si pulsas el botón Expandir barra de fórmulas, que está en la parte derecha de la barra de fórmulas.

Excel Experto 2007

8

En las referencias externas, ten en cuenta la ruta que se mostrará en el caso de que tengas los documentos (es decir, los libros) en la carpeta Documentos del usuario. En Windows Vista, la carpeta Documentos es distinta para cada usuario del sistema. Para cada uno de los distintos usuarios que pueda haber definidos, su carpeta Documentos se almacena dentro de la carpeta Users\nombre_de_ usuario del disco duro, y su nombre real es Documents (en la carpeta Users\nombre_de_usuario, aparte de guardarse esta carpeta, también se almacenan imágenes, favoritos y otros archivos y configuraciones propias de cada usuario). Por lo tanto, si seleccionas una referencia externa de un libro que esté en la carpeta Documentos, verás algo parecido a esto:

Si el libro de origen está en otra carpeta, verás la ruta adecuada en la referencia externa:

En Windows XP, la carpeta con los documentos propios del usuario se denomina Mis documentos, y está almacenada dentro de la carpeta Documents and Settings\nombre_de_usuario, mientras que, en versiones anteriores de Windows, la carpeta Mis documentos se guardaba directamente en el directorio raíz del disco duro: C:\Mis documentos. Finalmente, indicarte también que, al abrir un libro que tiene vínculos o referencias externas (es decir, el libro de destino), aparece una barra de mensajes debajo de la Cinta de opciones, que te indica que, por razones de seguridad, se ha desactivado la actualización automática de los vínculos. Desde el botón Opciones de esta barra podríamos habilitar la actualización automática de los vínculos, de forma que se actualizarían los datos desde el libro de origen, si esta información hubiera cambiado.

Excel Experto 2007

9

4. TRABAJO CON REFERENCIAS EXTERNAS

Del mismo modo que se pueden hacer referencias externas a celdas, también podemos utilizar nombres en las referencias. La sintaxis utilizada es la misma que la vista hasta ahora, con la única diferencia de que se utiliza el nombre en lugar de las referencias. Por ejemplo: ='C:\Trabajo\[Ventas.xls]Hoja3'!Total_Este.Cuando trabajas con libros vinculados y quieres guardarlos, normalmente se debe guardar el libro de origen antes de guardar el de destino. De esta forma, nos aseguraremos de que todos los datos del libro de destino están actualizados. En la ficha Datos de la Cinta de opciones, el botón Del grupo de comandos Conexiones proporciona algunas herramientas y utilidades para el trabajo con vínculos. Por ejemplo, podemos cambiar O actualizar los vínculos de un libro de destino en cualquier momento. En este cuadro de diálogo podrás ver los distintos vínculos que hay en el libro actual, además de hacer ciertas operaciones relacionadas con ellos. Si quieres acceder al libro de origen y éste no está abierto, con el botón Abrir origen podrías hacerlo. Al editar el libro de origen y tener abierto el libro de destino, los cambios se reflejarán en este último. Con Actualizar valores podrás realizar una actualización manual de la información vinculada, mientras que Romper vínculo romperá los vínculos, poniendo en las celdas que tengan vínculos el valor que tengan en ese momento. Por último, con el botón Pregunta inicial podremos indicar si, al abrir el libro, se actualizarán automáticamente o no los vínculos sin preguntar nada, o si se preguntará al usuario si quiere actualizarlos.

Excel Experto 2007

10

Al trabajar con libros vinculados, tienes que procurar no perder los Vínculos establecidos. Para ello, trata de guardar los libros vinculados en la misma carpeta. Si, por cualquier motivo, los guardas en carpetas distintas, guarda el libro de origen antes de guardar el libro de destino. Si, al abrir un libro de destino, Excel no encuentra el libro de origen, te mostrará un mensaje indicándolo. A veces, también puede suceder que un libro de destino ocupe mucho espacio en disco o tarde mucho tiempo en abrirse. Esto puede ser debido a que, en este libro de destino, se guardan las copias de los valores contenidos en el libro de origen junto con los datos de este libro de destino (esto suele pasar si tienes referencias externas a muchos rangos de celdas del libro de origen y se trata de rangos grandes). Puedes hacer que esto no sea así accediendo al cuadro de las Opciones de Excel (desde el menú del Botón de Office) y, en la sección Avanzadas, desactivar la casilla de verificación Guardar valores de vínculos externos. Así podrás reducir el espacio en disco y el tiempo que se tarda en abrir la hoja de cálculo.

Excel Experto 2007

11

Plantillas y Vistas 5. QUÉ SON LAS PLANTILLAS

Vamos a ver cómo utilizar las plantillas en los documentos de Excel. Las plantillas son elementos de Excel que se utilizan como base para crear libros parecidos. En una plantilla se guardan textos, formatos de celdas, estilos, gráficos, fórmulas, macros, etc. Entonces, al crear un nuevo libro basado en esta plantilla, este libro tendrá todas estas características de la plantilla, lo que nos servirá para ahorrarnos trabajo, al no tener que crear de nuevo todos estos elementos.

Excel Experto 2007

12

Desde el cuadro de diálogo Nuevo libro (que se muestra al acceder a la opción Nuevo del Botón de Office ) tienes varias opciones para crear un libro: puedes crearlo en blanco, a partir de otro libro existente o utilizando una plantilla, que es lo que veremos ahora.

Puedes utilizar las plantillas instaladas en tu ordenador por Office, tus propias plantillas o las que te ofrecen Microsoft a través de Internet, en su sitio web Microsoft Office Online. Si pulsas en Plantillas instaladas, dentro del apartado Plantillas, podrás ver las plantillas instaladas en tu ordenador. Al seleccionar una plantilla podrás ver una vista previa de la misma en la parte derecha del cuadro, para que te hagas una idea del aspecto que tendrá el libro.

Excel Experto 2007

13

Tras seleccionar la plantilla adecuada, pulsa el botón Crear para crear un nuevo libro utilizando esa plantilla. De esta forma, el libro tendrá los textos, formatos, colores, fórmulas, etc., de dicha plantilla, con lo que sólo te quedaría introducir los datos en las celdas adecuadas. Indicarte también que, al igual que puedes crear nuevos libros basados en plantillas, también puedes crear nuevas hojas en un libro basadas en una plantilla. Para ello, muestra el menú contextual de una hoja de cálculo y elige la opción Insertar. Desde el cuadro de diálogo que aparece podemos seleccionar la plantilla de la nueva hoja que insertaremos en este libro. En la ficha General aparecen varios tipos de hojas estándar, mientras que en la ficha Soluciones de hoja de cálculo están las plantillas predefinidas por Excel. Aunque basemos el documento en una plantilla, al guardarlo le tenemos que asignar un nombre. La plantilla no se modifica al crear, modificar y guardar documentos desde ella, permaneciendo igual. Para modificar una plantilla, deberemos abrir el archivo de dicha plantilla explícitamente y cambiar lo que queramos. Indicarte también, para acabar el capítulo, que el libro que aparece al abrir Excel sigue la plantilla predeterminada. Si creas un libro con la combinación de teclas CTRL + U o con el botón Nuevo (que puedes añadir a la barra de herramientas de acceso rápido), también se utilizará esta plantilla predeterminada. En este caso, como ya sabrás, esta plantilla no tiene ningún texto ni ningún formato en sus celdas. La plantilla predeterminada de Excel se denomina Libro.xltx, y está almacenada en la carpeta XLSTART, donde tienes instalado Excel (normalmente, esta carpeta se encuentra en la ruta C:\Archivos de programa\Microsoft Office\Office12\XLSTART). Por lo tanto, si quieres cambiar esta plantilla predeterminada, de forma que, al crear nuevos libros o al abrir Excel, el libro que aparezca tenga las características indicadas en dicha plantilla, tendrás que crear la plantilla y guardarla en este directorio con este nombre. Por otra parte, la plantilla de hoja de cálculo predeterminada, Que se utiliza al crear nuevas hojas de cálculo, se llama Hoja.xltx y también está en dicha carpeta. Si quieres restaurar las plantillas de libro y de hoja que tiene por defecto Excel, puedes eliminar los archivos Libro.xltx y Hoja.xltx que se encuentren en este directorio XLSTART.

6. CREAR PLANTILLAS

Aparte de utilizar las plantillas que proporciona Excel o las que están disponibles en Office Online, también puedes crear tus propias plantillas. De esta forma, podrías crear plantillas personalizadas para crear de una forma rápida documentos comunes que suelas utilizar: facturas, informes, fichas de artículos, etc. Por ejemplo, podrías crear una plantilla para un informe que suelas hacer mensualmente. Así, cada mes, simplemente tendrías que crear un nuevo libro basado en esa plantilla, con lo que ya tendrías los títulos, formatos, colores, fórmulas, etc., y sólo te quedaría rellenar los campos correspondientes a dicho mes. Para crear una plantilla, introduce en un libro los textos, formatos, fórmulas, funciones y demás elementos que quieras que tenga dicha plantilla. A continuación, guarda el documento, pero indicando, en la lista Tipo del cuadro Guardar como, la opción Plantilla de Excel (*.xltx), para que Excel guarde el archivo como una plantilla. Fíjate que la extensión de los ficheros de plantillas de Excel es xltx. Si te fijas en el campo superior del cuadro, puedes ver que, al indicar que queremos guardar el documento como una plantilla, Excel asigna la ruta adecuada. También puedes ver esta ruta reflejada en la sección Carpetas, en la parte izquierda del cuadro. Las plantillas personalizadas se guardarán en la carpeta Plantillas de Excel para el usuario de Windows. Si utilizas Windows Vista, esta carpeta se encuentra en C:\Users\nombre_de_usuario\AppData\ Roaming\Microsoft\Plantillas, mientras que, si usas Windows XP, está en C:\Documents and Settings\nombre_de_usuario\Datos de programa\Microsoft\Plantillas.

Excel Experto 2007

14

Si quieres copiar las plantillas a otro ordenador o a otra persona o eliminar una plantilla de las plantillas personalizadas, tienes que hacerlo desde esta carpeta. Para crear documentos basados en las plantillas que hayas creado, accede al cuadro de diálogo de creación de un nuevo libro y selecciona la opción Mis plantillas, que está en el apartado Plantillas. En el cuadro que aparecerá entonces podrás ver las plantillas que hayas creado; selecciona la plantilla adecuada y acepta. En la plantilla también se guardan los márgenes, el encabezado y pie de página, etc., por lo que si en la plantilla estableces estas características, los documentos que crees a partir de ella también las tendrán definidas. Esto lo puedes utilizar para que se impriman de la misma forma todos los documentos que crees a partir de una plantilla.

7. VISTAS PERSONALIZADAS

E n Excel puedes guardar varias vistas dentro de un mismo documento. Una vista es una forma de mostrar el libro; es decir, que si nos ponemos en una celda en concreto de una hoja y creamos una vista, siempre que volvamos a dicha vista, volveremos a ver el libro tal como lo teníamos al crear la vista (nos pondremos en la hoja y en la celda en la que nos encontrábamos). De esta forma, puedes tener varias vistas guardadas de un mismo libro, que te permiten mostrar en cada una hojas distintas, con distintos niveles de zoom, en distintas posiciones, etc., y acceder a ellas rápidamente.

Excel Experto 2007

15

Para crear una nueva vista, pulsa Agregar e indica el nombre de la misma. Establece el nombre adecuado, de forma que sepas a qué se refiere dicha vista y puedas identificarla claramente. En el apartado Incluir en la vista puedes indicar las opciones que quieres que se almacenen en la vista que estás creando. Después de crear la vista, puedes volver a ver el libro de esa forma siempre que quieras. Para esto, accede al cuadro de las vistas personalizadas, selecciona la vista y pulsa el botón Mostrar. Ten en cuenta que, al volver a esta vista, simplemente vuelves a ver el libro tal como lo veías al crear la vista, no se deshacen los cambios que hayas hecho en esta u otras hojas. Además, en una vista personalizada también puedes almacenar la configuración de impresión, de forma que puedes tener varias formas de imprimir una misma hoja en distintas vistas y seleccionar una u otra según quieras imprimir una cosa u otra.

8. ZOOM Y OTRAS VISTAS

Vamos a ver algunas opciones más relativas al tema de las vistas y los distintos modos de ver un libro, para acabar con este aspecto. Ya sabes que, desde los controles de zoom puedes acercar o alejar la vista de la hoja de cálculo, para ver más detalles o para ver una mayor parte de la hoja. Para establecer el factor de zoom o de ampliación que quieras, puedes utilizar los botones Alejar y Acercar o arrastrar el control de la barra del zoom hacia la izquierda o hacia la derecha. Los controles del grupo de comandos Zoom de la ficha Vista de la Cinta de opciones también sirven para establecer el nivel de ampliación de la hoja.

Por otro lado, ya hemos trabajado con la Vista previa de salto de página, que permite ver y cambiar la situación de los saltos de página en el documento impreso. Para acceder a esta vista puedes pulsar el botón Vista previa de salto de página , en la parte inferior derecha de la barra de estado, en la ficha Vista de la Cinta o el botón VISTA PREVIA DE SALTO DE PAGINA de opciones. Una vista relacionada con esta es la vista Diseño de página, que permite cambiar e introducir datos del mismo modo que en la vista Normal, y además cambiar los encabezados y pies de página,

Excel Experto 2007

16

Establecer los márgenes, etc.; es decir, aspectos relacionados con la configuración de la impresión de la hoja. Para ponerte en esta vista, haz clic en DISENO DE PAGINA , en el grupo de comandos Vistas de libro de la Cinta de opciones Las reglas horizontal y vertical que aparecen en la parte superior e izquierda de la ventana, respectivamente, te pueden ayudar a colocar los objetos en la hoja (gráficos, dibujos, imágenes, etc.). También se utilizan para cambiar los márgenes de la página, pulsando y arrastrando en sus extremos. Para añadir o cambiar los encabezados o pies de página, simplemente pulsa en el lugar adecuado (es decir, en la parte izquierda, central o derecha de la zona superior o inferior de la página) y escribe el texto que quieras. Al igual que en la vista Normal, puedes introducir datos o editar las celdas, modificando su contenido o su formato.

9. PROPIEDADES DEL LIBRO

Los archivos de documentos de Excel tienen unas propiedades, que puedes utilizar para identifica más claramente un documento. Además, las propiedades también te pueden ayudar a buscar documentos. Por ejemplo, puedes buscar todos los libros que haya creado una determinada persona, que traten de un tema en concreto, etc. Para acceder a las propiedades del libro actual, pulsa el Botón de Office y selecciona Preparar – Propiedades. Desde el Panel de información del documento que aparece puedes introducir las propiedades relativas al libro: un título y un asunto que definan el tema del libro, quién lo ha creado, el estado o etapa en la que se encuentra el documento (por ejemplo, borrador, beta, por revisar o final), etc. Al pulsar el botón Propiedades del documento, en la parte superior izquierda del Panel de información del documento, y seleccionar la opción Propiedades avanzadas, aparecerá el cuadro de diálogo de las propiedades del archivo (al que también puedes acceder desde el Explorador de archivos de Windows, a través del menú contextual del archivo), que muestra toda la información y propiedades del documento.

Excel Experto 2007

17

Además de las propiedades básicas que hemos visto antes, desde la ficha Resumen desde aquí puedes introducir a qué organización o empresa pertenece la persona que ha creado el libro o la persona que hace de administrador del documento. La casilla de Guardar miniaturas te permite ver lo que contiene un documento antes de abrirlo, en el cuadro de Abrir. Esto te puede servir especialmente al crear una plantilla, ya que, si activas esta casilla, podrás ver la vista previa al seleccionar la plantilla. En la ficha Estadísticas puedes ver estadísticas acerca del documento, como la fecha en que fue creado o modificado, quién lo ha guardado, etc. En la ficha General también están las fechas de creación y modificación, y además puedes ver el tamaño del archivo en disco y la ruta donde está guardado. Recuerda que también puedes usar estos campos para buscar un archivo. Por ejemplo, podrías buscar los libros que se hubieran creado antes o después de una fecha determinada o los que se hubieran modificado hace dos días. En la ficha Contenido se muestra el contenido del documento, en este caso, las hojas de cálculo de las que consta el libro. En la ficha Personalizar puedes ver o cambiar las propiedades personalizadas. Estas propiedades puedes crearlas tú mismo. Es decir, puedes crear una nueva propiedad y ponerle el nombre que quieras (por ejemplo, Enviado a, que indicaría a quién has enviado el libro), y después utilizar esta propiedad para buscar o clasificar los documentos (por ejemplo, podrías buscar todos los libros que hubieras enviado a una determinada persona). En la ficha Personalizar también hay una lista de nombres de propiedades (Cliente, Departamento, Grupo, Proyecto, etc.), para que puedas seleccionar uno de estos nombres para la propiedad que crees. Si no te gusta ninguno de estos nombres que te propone Excel, recuerda que puedes escribir el nombre que quieras. En estas propiedades personalizadas, además de asignar el nombre, también podrás indicar el tipo de la propiedad, es decir, si la propiedad tendrá un texto, un número, una fecha o será de tipo "Sí/No".

10. FIRMAS DIGITALES

Otra característica que puedes aplicar a los archivos de Excel es firmarlos digitalmente. Una firma digital es una información electrónica segura y cifrada, que sirve para autentificar un documento. De esta forma, garantizas a cualquier persona que abra el libro que dicho libro fue creado por ti y que nadie lo ha modificado. Esto se utiliza para que la gente sepa con total seguridad quién ha creado el libro, de forma que nadie pueda engañarlos y hacerse pasar por otra persona. Para añadir una firma digital a un libro, pulsa el Botón de Office y selecciona Preparar - Agregar una firma digital. Para firmar digitalmente un archivo, lo primero que necesitas es un certificado digital. Existen varias empresas que se dedican a proporcionar certificados digitales, por lo que tendrás que contactar con alguna de ellas si quieres obtener uno. La primera opción se conecta al sitio web de Microsoft Office Online para buscar una empresa que te proporcione este certificado digital. Una vez has obtenido y has instalado en tu ordenador tu certificado digital, ya podrás utilizarlo para firmar digitalmente archivos. Otra posibilidad es crear tu propio certificado o identificador digital, a través de la segunda opción de este cuadro de diálogo. E n este caso se necesita tu nombre, dirección de correo electrónico, organización o empresa para la que trabajas y el lugar o país en el que estás. Ten en cuenta que, si creas tu propio certificado digital, podrás comprobar la autenticidad de esta firma digital sólo en el equipo en el que lo creaste, no en otros ordenadores. Para esto, deberías obtener un certificado digital de una empresa autorizada. Si tienes alguna duda o necesitas más información sobre este tema, puedes consultar la ayuda que tiene disponible Excel. De la misma manera que firmas un documento, también puedes firmar una macro. De esta forma, indicarías que la macro que has creado es segura.

Excel Experto 2007

18

Filtros y tablas

11. FILTRAR DATOS

En esta lección vamos a seguir viendo más características que tiene Excel para ayudarnos a trabajar con listas de datos. Recuerda que utilizamos las listas para almacenar datos de una forma estructurada en una hoja de cálculo. Las distintas filas de la hoja de cálculo son los registros de la lista, mientras que las distintas columnas son los campos de información. Si trabajas con listas de datos largas, puede ser muy útil el tener algún método que permite seleccionar un conjunto de datos dentro de la lista, para verlo o hacer operaciones con estos datos. Para extraer un cierto subconjunto de registros de una lista, estableciendo criterios que permitan seleccionar los registros que queremos, Excel dispone del filtrado de datos.

Para activar el filtrado de datos, selecciona la opción Filtro en que está en la ficha Inicio de la Cinta del menú del botón ordenar y filtrar opciones. Este comando Filtro permite filtrar los elementos de una lista de una manera rápida. Cuando seleccionas esta opción, Excel busca en la lista activa de la hoja la fila que contiene los títulos de las columnas y coloca flechas desplegables en cada uno de estos títulos o rótulos de los campos de la lista.

Excel Experto 2007

19

Al pulsar sobre estas flechas, se despliega una lista con todos los datos distintos que aparecen en una misma columna. Puedes activar o desactivar las casillas que aparecen en los elementos de esta lista, para mostrar u ocultar, respectivamente, las filas que tengan dicho valor en esa misma columna. Observa las casillas en los distintos elementos. La opción (Seleccionar todo) permite seleccionar o deseleccionar las casillas de todos los valores, para, a continuación, poder elegir los adecuados.

Excel Experto 2007

20

Al pulsar el botón Aceptar, se cerrará el menú y se aplicará el filtro. De esta forma, al seleccionar un determinado valor o varios, se ocultarán las filas de la lista que en la columna correspondiente no tengan dicho valor o valores.

La lista puede filtrarse aún más eligiendo otro criterio de una columna diferente. Es decir, los filtros son aditivos: cada filtro se basa en el filtro actual, reduciendo el subconjunto de datos que se muestran.

Dependiendo del tipo de dato de la columna, este submenú se denominará de manera diferente (por

ejemplo, Filtros de número o Filtros de fecha) y sus opciones cambiarán.

Excel Experto 2007

21

Por ejemplo, para números puedes indicar que el valor sea mayor o menor que otro dado, que esté entre dos valores, que esté en los valores superiores o inferiores, por encima o por debajo del promedio; para fechas, antes o después de una fecha, en esta semana o mes, en la anterior o siguiente semana o mes, etc. Con la opción Filtros de número - Diez mejores podrás filtrar la lista de forma que seleccione aquellas entradas superiores o inferiores (numéricamente) sin tener que ordenarla. Desde esta ventana que se muestra con esta opción podrás indicar el número de filas que quieres mostrar y si quieres mostrar las que tengan un valor más alto o más bajo. Con la opción Filtro personalizado de estos submenús se mostrará un cuadro donde podrás indicar las características del filtro a aplicar. Incluso podemos introducir dos criterios de selección, para ajustar más el filtro (por ejemplo, que un campo sea mayor que un valor y menor que otro). Si indicas dos criterios en el campo, tendrás que seleccionar la opción Y o la opción O, según quieras que los dos criterios se cumplan o que lo haga al menos uno de ellos. Fíjate cómo, a través de este cuadro de diálogo, no es posible establecer más de dos condiciones para un campo. También puedes utilizar caracteres comodines para indicar los criterios de comparación en los filtros personalizados.

Excel Experto 2007

22

Recuerda que un carácter comodín es un símbolo que indica que puede ser cualquier letra o conjunto de letras:

El signo? representa un único carácter individual que esté en la posición donde pongamos este signo. Por ejemplo : ?alencia encontraría Valencia y Palencia.

El signo * representa cualquier grupo o serie de caracteres. Por ejemplo, N*este encontraría Nordeste, Noroeste y Noreste. Para introducir alguno de estos caracteres como parte del texto por el que queremos filtrar, pon una tilde (~) delante del signo: ~?,

Si quieres indicar una tilde en el texto a filtrar, escribe ~~.

12. FILTRO AVANZADO

Hemos visto cómo, con la opción Filtro, podemos poner criterios personalizados. Sin embargo, no puedes introducir criterios demasiado complejos. El Filtro avanzado permite introducir filtros más complejos. En este caso, no se muestran las listas desplegables en las columnas, sino que tenemos que introducir los criterios en un rango de criterios en la hoja de cálculo; este rango estará fuera de la lista a filtrar. Existen dos tipos de criterios complejos:

Criterios de comparación: hacemos una comparación con valores concretos o comprobamos que el valor está dentro de unos límites determinados.

Criterios calculados: comparan el resultado de una columna de la lista con datos que no están en la lista. Se introducen mediante una fórmula y, si su resultado es VERDADERO, se Se recomienda muestra la fila correspondiente. Para introducir los criterios de comparación en un rango de criterios, tienes que introducir una fila con los títulos de los criterios y, al menos, una fila de criterios. Los títulos de los criterios deben coincidir con los títulos de las columnas de la lista.

Excel Experto 2007

23

Por ejemplo, en la imagen anterior, el rango de criterios de la hoja indica que se seleccionen sólo aquellas filas que contienen el valor Murcia en el campo Ciudad.

Para ejecutar el comando Filtro avanzado tenemos que situarnos en una celda que pertenezca a la lista. A continuación, pulsa el, que está en el grupo de comandos Ordenar y botón AVANZADAS filtrar de la ficha Datos de la Cinta de opciones. Como ves, desde este grupo también puedes ordenar una lista o filtrarla de la forma que hemos visto antes.

Aparece un cuadro de diálogo en el que debes Indicar el rango de la lista a filtrar (Excel lo introduce Automáticamente, pero puedes cambiarlo si quieres) y dónde están los criterios de filtrado, es decir, las celdas del rango de criterios (acuérdate de incluir Los títulos de los criterios en este rango de criterios). Si no sabes con exactitud la referencia del rango de criterios, pulsa el botón Minimizar diálogo y señala las celdas con el ratón. También podría ser una buena idea crear un nombre para el rango de Criterios y utilizarlo en lugar de las referencias a las celdas. Tras aceptar el cuadro se realiza el filtrado, con lo únicamente se Muestran en la lista las filas que cumplan los criterios indicados. Tras aceptar el cuadro se realiza el filtrado, con lo únicamente se muestran en la lista las filas que cumplan los criterios indicados. Para eliminar un filtro avanzado, al igual que con Filtro, selecciona la opción Borrar (a través del botón ORDENAR Y FILTRAR de la ficha Inicio o pulsando el botón BORRAR en esta ficha Datos). También podemos introducir más de un criterio en la misma fila. En este caso, estaremos indicando que se tiene que cumplir cada uno de los criterios.

Excel Experto 2007

24

En el ejemplo que puedes ver en la imagen anterior, estamos indicando que queremos seleccionar las filas de la lista cuya columna Ciudad sea igual a Murcia y cuyo valor de Días sea mayor que 15. Si ponemos los criterios en filas distintas, se indica que el filtrado se debe llevar a cabo de forma que sólo es necesario que se cumpla una de las filas. En este caso, el filtrado se llevaría a cabo tanto si se cumple uno o más de un criterio. Fíjate cómo, en la imagen anterior, vemos las filas cuya Ciudad sea Murcia o cuyo valor de Días sea mayor que 15, Por último, en el ejemplo que ves a continuación, al poner los criterios en distintas filas, indicaremos que se seleccionen las filas Cuya Ciudad sea Murcia o Madrid.

Para introducir criterios calculados en un rango de criterios, tienes que seguir estas normas:

Introducir una fórmula que dé como resultado valores lógicos (es decir, VERDADERO o FALSO).

La fórmula introducida se debe referir por lo menos a una columna de la lista.

El título del criterio calculado, en caso de existir, debe ser un texto que no coincida con el de una columna existente.

Excel Experto 2007

25

Tendrías que hacerlo Igual que antes: introducir los criterios en el rango de criterios, Cuando Utilizas el seleccionar una celda de la lista filtro avanza y pulsar el botón AVANZADAS. En el cuadro de diálogo, indica ocultar las filas el rango donde está situada con datos duplicados. Para la lista y el rango donde se encuentran los criterios. Por ejemplo, en el siguiente caso hemos introducido la fórmula =$A$2<Días en la celda B2 (la referencia Días de esta fórmula Corresponderá al rango de celdas F5...F13). Como en la celda A2 hemos introducido el valor de 15, al hacer el filtro avanzado se mostrarán las filas cuyo número de Días sea mayor que el valor que hay en la celda A2 (es decir, las filas que cumplan la fórmula escrita en B2):

Observa que, aunque la fórmula de la celda E2 produce un error, el filtrado se lleva a cabo correctamente. Por otra parte, ten en cuenta que, si cambias los criterios en el rango de criterios y haces de nuevo el filtrado, éste se realizará sobre todas las filas, y no sólo sobre las que ves ahora, tras haber hecho el anterior filtrado. En cuenta estos aspectos al crear la fórmula para utiliza el Filtro avanzado:

No pongas el título o rótulo de una columna de la lista como rótulo en el rango de criterios: no pongas ningún rótulo en los criterios o utiliza un rótulo distinto al que utilizas en la lista. Por ejemplo, en este caso los rótulos de los criterios son Límite y Duración, que no son iguales a ningún rótulo de la lista.

La fórmula que hay en el rango de criterios tiene que utilizar una referencia relativa para hacer referencia al campo correspondiente del primer registro o utilizar el rótulo de la columna. Las demás referencias de la fórmula deben ser referencias absolutas. En nuestro ejemplo, hemos utilizado el rótulo de columna Días para hacer referencia a las celdas de la lista que están situadas en esta columna. También hubiéramos podido utilizar la referencia relativa F5, para hacer referencia al campo correspondiente del primer registro de la lista (en este caso, la fórmula sería =$A$2<F5).

El resultado de la fórmula debe ser VERDADERO o FALSO Para cada elemento de la lista, si se cumple la fórmula (es decir, si la fórmula devuelve VERDADERO), la fila se mostrará, mientras que si no se cumple la fórmula (su resultado es FALSO), esta fila de la lista no se mostrará al hacer el filtro. Te recomiendo que utilices el Filtro avanzado si vas a establecer criterios en dos o más columnas, con tres o más condiciones en alguna columna o si quieres introducir valores calculados en los criterios. En caso contrario, es más sencillo y rápido utilizar el Filtro.

Excel Experto 2007

26

13. COPIAR DATOS FILTRADOS

Utilizando la opción Filtro avanzado, también puedes copiar automáticamente las filas que cumplen los criterios introducidos en otro lugar de la hoja de cálculo. Si has filtrado una lista con el Filtro, la lista resultado se puede copiar y pegar mediante estos comandos. El comando Copiar sólo copiará los datos de la lista que se ven como resultado del filtrado. En el cuadro del diálogo del Filtro avanzado, en el apartado Acción, podemos indicar que el resultado del filtrado se copie en otro lugar. Al seleccionar esta opción, se activa el campo Copiar a, donde se indica el rango destino de la lista filtrada. Si el rango que indicas en el campo Copiar a del cuadro del Filtro Avanzado tiene algunos de los títulos de columna de la lista, Excel copiará sólo aquellas columnas que coincidan con esos títulos. Si quieres copiar datos en otra hoja de cálculo, la forma más fácil Es filtrar la lista en el mismo lugar y después copiar los datos filtrados utilizando el comando Copiar. Para copiar datos en otra hoja distinta en la que está la lista mediante el Filtro avanzado, primero debemos ponernos en la hoja donde queremos que se copien los datos y elegir la orden Filtro avanzado; después indicaremos en el cuadro de diálogo el rango donde está situada la lista y el rango de los criterios, incluyendo la referencia a la hoja origen. Recuerda que, en el cuadro de diálogo del Filtro Avanzado, puedes indicar los distintos rangos de celdas a través del botón Minimizar diálogo; tras pulsar este botón, puedes ponerte en otra hoja e indicar el rango de celdas adecuado. De esta forma, seleccionando directamente los rangos de celdas con el ratón, es más sencillo establecer estos rangos.

14. TRABAJAR CON LISTAS FILTRADAS

Cuando estás trabajando con listas filtradas, algunas de las operaciones de Excel trabajan sólo con las filas visibles. Por ejemplo, al imprimir una lista desde la opción Imprimir del Botón de Office se imprime sólo los datos visibles de la lista. Las operaciones que están afectadas por el modo filtrar son:

La asignación de un formato a las celdas (mediante los controles de la ficha Inicio de la Cinta de opciones o desde el cuadro del Formato de celdas) sólo afecta a las celdas visibles.

Operaciones de Borra, Eliminar y Copiar sólo actúan sobre las celdas visibles.

Al insertar celdas copiadas, sólo se pueden insertar filas enteras. Otra operación que se modifica es la suma automática (Suma o Autosuma), ya que cuando se está trabajando sobre una lista filtrada, se calcula el total de las filas que se ven. Si luego cambias el criterio de filtrado y, como resultado de esto, cambian las filas visibles, Excel vuelve a calcular el

Excel Experto 2007

27

Total para ajustarse a la nueva situación. Al trabajar con listas filtradas, puede resultar interesante ordenar la lista (por ejemplo, para insertar subtotales automáticos). Si la lista está filtrada, sólo se ordenan las filas visibles, mientras que las filas que no se muestran no se mueven de su posición inicial. Para ordenar una lista, como ya sabes, únicamente hay que seleccionar la columna y elegir la opción adecuada del botón (en la ficha Inicio) o el comando correspondiente en el grupo de comandos Ordenar y filtrar de la ficha Datos.

15. TRABAJAR CON TABLAS

Tras la posibilidad es convertir un rango de celdas con datos relacionados en una tabla, que no es más que un conjunto de filas y columnas con datos dispuestos en forma de lista Hay varias ventajas y que obtenemos al crear una tabla, que hacen más fácil el trabajo con los datos, entre las que destacan:

En la fila de encabezado se añaden automáticamente las listas para filtrar u ordenar los datos.

Puedes aplicar un estilo de tabla predefinido, para cambiar el formato de la lista rápidamente.

Se puede añadir una fila de totales al final de la tabla, Para designar un rango de celdas como una tabla, ponte en una, que está en la ficha Insertar de celda de la lista y pulsa el botón la Cinta de opciones.

En el cuadro de diálogo Que se muestra puedes indicar El rango de celdas que forman la Tabla, y si esta lista tiene una fila Con los títulos o rótulos columnas. Al seleccionar una tabla, aparece en la Cinta de opciones la ficha contextual Diseño, que tiene varios comandos para trabajar con esta Tabla. Lo primero que nos puede llamar la atención al indicar a Excel que este rango es una tabla, es que activa automáticamente el Filtro para las distintas columnas de la tabla.

Excel Experto 2007

28

Para añadir una nueva fila o columna a la celda, simplemente tienes que introducir un valor o un texto en una celda que esté situada debajo o a la derecha de la tabla En el grupo de comandos Opciones de estilo de tabla de esta ficha Diseño dispones de varias opciones que puedes activar o no, según quieras que se muestre la tabla.

Con la casilla Fila de totales puedes mostrar u ocultar la fila Total. Esta fila te permite mostrar el total (u otras funciones) en las columnas de la lista. Para indicar la función que se mostrará en cada columna de la fila Total, ponte en la celda adecuada de esta fila Total, despliega la lista que aparece (pulsando la flecha de la derecha de la celda) y selecciona la función que quieras La opción Ninguno no muestra ninguna función, mientras que con Más funciones puedes elegir la función que quieras mostrar en la fila Total.

Excel Experto 2007

29

La opción Fila de encabezado de la ficha Diseño de la Cinta de opciones permite mostrar u ocultar la fila de encabezado de la tabla. Las opciones Primera columna y Última columna muestran estas columnas con un formato especial, para diferenciarlas del resto, mientras que Filas con bandas y Columnas con bandas muestran bandas en las filas o columnas, para que sea más fácil de leer la tabla. Desde el botón Puedes seleccionar un estilo Visual predefinido para la Tabla, cambiando el formato De las celdas de la misma (Colores, bordes, etc.).

Permite quitar filas duplicadas de la tabla; en este caso, deberás indicar la columna o columnas en los que se buscará información duplicada. Finalmente, para que Excel deje de tratar al conjunto de celdas como una tabla, pulsa el botón convertir rango, con lo que se Convertirá la tabla en un rango de datos. Como has visto, al crear tablas a partir de rangos de celdas, Excel facilita el trabajar con estos datos, ya que proporciona acceso rápido a varias herramientas y comandos que se suelen utilizar.

Excel Experto 2007

30

Subtotales 16. SUBTOTALES AUTOMÁTICOS

Ya sabes cómo introducir datos en listas y hacer operaciones para trabajar con estos datos: ordenar los datos, verlos con un formulario de datos, filtrar los datos según las condiciones que quieras, etc. En esta lección vamos a ver otra herramienta más de Excel que puedes usar en tus listas de datos: los subtotales automáticos. En ocasiones te puede interesar calcular los totales parciales y totales globales de una determinada columna de la lista. Podrías calcular estos valores a través de fórmulas y funciones, pero Excel te proporciona una opción automática para realizar estos cálculos: al utilizar subtotales automáticos, se insertan y se escriben automáticamente los títulos y las fórmulas en las filas de totales.

Excel Experto 2007

31

Excel presenta estos resultados en forma de esquema y nos permite desplazarnos a distintos niveles en este resumen de datos. Este esquema que muestra Excel permite agrupar las filas de la lista, para mostrar la información que hay de manera resumida. Por ejemplo, en la hoja de la página anterior, vemos una lista de clientes de una determinada empresa, con su ciudad, la fecha en que empezaron a comprarnos y la cantidad de dinero que nos han pagado por sus compras. En este caso, a través de los subtotales automáticos podemos obtener, de una manera rápida y sencilla, la cantidad de dinero ingresada en cada ciudad. Para crear los subtotales automáticos en una lista, lo primero que tenemos que hacer es ordenar la lista respecto al campo para el que queramos obtener los subtotales. Recuerda que puedes ordenar la lista desde las opciones adecuadas (en la ficha Inicio de la Cinta de opciones) del menú del botón ORDENAR Y FILTRAR o desde los botones Ordenar de A a la Z (que están en la ficha Datos). y Una vez ordenada la lista, ponte en una celda dela misma y pulsa el botón que está en el grupo de comandos Esquema de la ficha Datos, con lo que aparecerá el cuadro de diálogo Subtotales. En la lista Usar función deberás indicar la función que vas a usar para calcular los subtotales. Las funciones que puedes usar para crear subtotales son:

Suma: suma los valores de las celdas.

Cuenta: sirve para contar el número de datos que hay en las celdas.

Promedio: calcula el valor medio de los datos.

Máx., Mín.: obtiene respectivamente.

Producto: el resultado de esta función es el producto o multiplicación de los datos.

Contar números: esta función devuelve el número de datos de las celdas que son números.

DesvEst: calcula una estimación de la desviación estándar, donde la muestra es un subconjunto de toda la población. La desviación estándar es una medida que indica lo que se alejan los valores

Excel Experto 2007

32

respecto a su valor medio.

DesvEstp: calcula una estimación de la desviación estándar, donde la muestra son todos los datos que van a resumirse.

Var: calcula una estimación de la varianza, donde la muestra es un subconjunto de toda la población.

Varp: obtiene la varianza, donde la muestra son todos los datos que van a resumirse. Consulta la ayuda de Excel si quieres obtener más información sobre estas funciones o sobre cómo se calculan sus resultados.

En el cuadro Agregar subtotal a se indica la columna que tiene los valores sobre los que vamos a aplicar la función seleccionada (es decir, la columna que tiene los valores que vamos a sumar, a contar, o calcular la media, etc.). La casilla Resumen debajo de los datos hace que los subtotales se muestren debajo de las filas sobre las que se calculan. Si la desactivas, los subtotales aparecerán encima de las filas Al aceptar el cuadro de dialogo, Excel calcula los subtotales con la función elegida creando filas para poner estos subtotales mas pequeños dentro de otros grupos mayores. También es posible anidar subtotales; esto significa incluir grupos de subtotales más pequeños dentro de otros grupos mayores. dos veces, eligiendo cada Para ello, ejecuta la orden vez una columna distinta en Para cada cambio en y desactivando la casilla de verificación Reemplazar subtotales actuales la segunda vez que se llama a la orden, con lo que se añadirá otro nivel al esquema de subtotales. Ten en cuenta también que tienes que ordenar la lista, como segundo criterio de ordenación, por el campo que crea el subtotal anidado. Por ejemplo, en la hoja que puedes ver en la siguiente página se han calculado los subtotales para cada ciudad y, dentro de cada ciudad, para cada año. Para hacer esto, hemos ordenado la lista por la ciudad y luego por el año (a través del cuadro Ordenar, accesible a través del de la ficha Datos). Luego hemos creado los subtotales botón ORDENAR automáticos por la ciudad y, tras aceptar la ventana, hemos vuelto a crear los subtotales, pero esta vez por el año y desactivando la casilla Reemplazar subtotales actuales). Otra cosa que podemos hacer es utilizar más de una función en los subtotales. Para ello tendremos que ejecutar dos veces la orden

Excel Experto 2007

33

SUBTOTAL eligiendo cada vez una función distinta y desactivando la opción Reemplazar subtotales actuales la segunda vez. Ten en cuenta que, siempre que la casilla de verificación Reemplazar subtotales actuales esté activada, se cambiarán todos los cálculos y subtotales al aceptar el cuadro de diálogo de Subtotales. Para eliminar los subtotales, podemos ejecutar el comando SUBTOTAL y pulsar el botón Quitar todos.

Además, recuerda que, cuando trabajas con listas filtradas, los subtotales sólo tienen efecto sobre las filas visibles. Ten en cuenta que los valores de los totales generales siempre se calculan a partir de los datos detallados y no de las filas de subtotales. Por ejemplo, si utilizas la función para calcular la media de los datos (función Promedio), la fila de totales mostrará el promedio de todos los datos detallados (es decir, el promedio de los valores de todas las filas), y no el promedio de los valores de los subtotales. Además, los valores de los totales y subtotales se vuelven a calcular automáticamente al editar los datos detallados, es decir, los valores de las distintas columnas de las filas.

17. ORGANIZACIÓN DE SUBTOTALES

Al mostrar los subtotales automáticos, Excel pone en forma de esquema la lista, agrupando las distintas filas con cada fila de subtotal asociada y las filas de subtotales con la fila del total general. En el esquema con los subtotales, puedes mostrar u ocultar los distintos datos de la lista a través de los botones que hay en la zona izquierda de la hoja, a la izquierda de los números de fila.

Excel Experto 2007

34

Los botones que están en la parte superior de esta zona 123 permiten desplazarse por los distintos niveles del esquema. Debajo de se presenta un esquema 123 estos botones de símbolos de esquema del estado actual de la hoja. Los cuadros con el símbolo + en su interior indican que se puede ampliar su contenido,

para ver todas las filas que forman dicho subtotal. Si pulsas sobre los botones con el signo - , el efecto será el contrario, ocultando las filas del grupo y llegando a un mayor nivel de resumen. De esta forma, puedes hacer un resumen de los datos de la hoja, mostrando sólo los subtotales que te interesen en cada momento. De esta forma, puedes hacer un resumen de los datos de la hoja, mostrando sólo los subtotales que te interesen en cada momento Cuando trabajas con subtotales, también puedes ordenar los resultados como lo harías con cualquier otra lista. Para ordenar la lista de subtotales, primero elige el nivel de detalle que muestra sólo los resultados de los subtotales y después utiliza el comando de ordenación adecuado Cuando se ordena una lista de subtotales con las filas de mayor detalle ocultas, las filas ocultas se ordenan con las filas de subtotales asociadas. Por último, puedes ocultar la zona izquierda, donde están los símbolos del esquema, desactivando la casilla Mostrar símbolos de esquema si se aplica un esquema, que está en la sección Mostrar opciones para esta hoja de la categoría Avanzadas del cuadro de las Opciones de Excel (accesible desde el Botón de Office. Para volver a mostrar los símbolos del esquema, activa esta casilla.

18. GRÁFICOS CON SUBTOTALES

A l trabajar con subtotales, podemos utilizar estilos para asignar el formato de las celdas del esquema. De esta forma, puedes crear unos estilos a partir de las filas del esquema y luego modificarlos, para personalizar el aspecto del mismo.

Si pulsas el botón Iniciador de cuadro de diálogo del grupo de comandos Esquema, aparecerá un cuadro que sirve para establecer algunas opciones de creación de esquemas.

Excel Experto 2007

35

Desde este cuadro puedes indicar, por ejemplo, si las filas de subtotales aparecerán debajo de las filas asociadas o encima de ellas (si deseleccionas la casilla). Para crear unos estilos para las filas de subtotales automáticos del esquema, activa la casilla Estilos automáticos y pulsa el botón Crear. Estos estilos aparecerán en el menú del botón que está en la ficha Inicio de la Cinta de opciones.

También es interesante crear un informe de resumen con un gráfico, para mostrar o analizar los datos de un modo sencillo.

Para crear un gráfico a partir de los subtotales, oculta primero las filas de detalle y quédate sólo con las filas de subtotales. Luego selecciona las celdas de la lista sobre las que quieres crear el gráfico y utiliza la opción adecuada de la ficha Gráficos para crearlo. De la ficha

Excel Experto 2007

36

Si es necesario, recuerda que con el botón contextual Diseño (que aparece al seleccionar el gráfico) puedes seleccionar los datos que quieres que se representen en el gráfico, para que se muestren en dicho gráfico los datos que quieras. Por otra parte, si quieres personalizar el gráfico, puedes utilizar Las distintas opciones de las fichas contextuales Presentación y Formato. Ten en cuenta que los datos que se presentan en el gráfico se refieren a los que se ven en la lista. Al aumentar o disminuir el detalle de los datos resumidos, el gráfico se actualiza. Así pues, hacer un informe a partir de los datos de una lista, utilizando todas las posibilidades que proporciona Excel, puede convertirse en una tarea sencilla.

19. TOTALES AVANZADOS

Hasta ahora hemos visto cómo realizar cálculos de forma automática. Pero, a veces, interesa hacer cálculos de celdas o filas que cumplan sólo ciertos criterios. Estos cálculos se pueden realizar mediante el uso de funciones en las fórmulas y, por tanto, puedes poner los resultados en cualquier lugar de la hoja de cálculo. Vamos a ver algunas de las funciones que tiene Excel para realizar totales avanzados:

SUMAR.SI(rango;criterio;sum_rango) Esta función sirve para realizar un cálculo parcial sobre los datos de una lista: sumará sólo los valores de la lista que cumplan cierto criterio. En el primer argumento se indica el rango de la lista a evaluar, es decir, la columna donde estarán los datos que compararemos con el criterio. En el segundo argumento

Excel Experto 2007

37

indicamos el criterio a seguir en la evaluación, que aplicaremos sobre el rango indicado en el argumento anterior. Por último, el tercer argumento se utiliza para indicar el rango que contiene los valores que quieres sumar. Por ejemplo: =SUMAR.SI(D4:D13;"<15/04/2006";F4:F13) Esta función comprueba, para cada una de las filas entre la 4 y la 13, si los valores de las celdas que están en la columna D son anteriores al 15/04/2006 y, si lo son, suma el contenido de las celdas de la columna F, obteniendo el resultado total de esta suma.

CONTAR.SI(rango;criterio) Esta función se utiliza para saber qué número de filas cumplen un determinado criterio. En el primer argumento se indica el rango de evaluación, en el que vas a contar las celdas, y en el segundo, el criterio. Dicho de otra forma: esta función devuelve el número de celdas del rango que cumplen el criterio. Por ejemplo: =CONTAR.SI(D4:D13;"<15/04/2006") Esta función contará el número de celdas entre D4 y D13 que cumplen que tienen un valor de antes del 15/04/2006.

CONTAR.BLANCO(rango) Mientras que con la función anterior contábamos celdas que cumplían un determinado criterio, con esta podremos calcular las celdas en blanco dentro de una lista. En este caso sólo tenemos un argumento, en el que indicaremos el rango del que queremos contar el número de celdas en blanco. Los operadores que se pueden utilizar en los criterios de las funciones SUMAR.SI y CONTAR.SI son:

=: igual a

<: menor que

>: mayor que

<=: menor o igual que

>=: mayor o igual que

<>: distinto

El criterio en la función puede ser un número, un texto o una expresión con estos operadores; en estos dos últimos casos, lo tendremos que encerrar entre comillas dobles (por ejemplo: ">56" o "Madrid"). Recuerda que, si no quieres utilizar los rangos directamente sobre las fórmulas, puedes asignar nombres a los rangos de las columnas de la lista para después utilizar estos nombres como referencias. Para realizar cálculos más complejos, utiliza funciones de bases de datos. Para usar estas funciones, deberás crear un rango de criterios como se hace al utilizar filtros avanzados.

Excel Experto 2007

38

En los argumentos de estas funciones de bases de datos, tendrás que indicar:

El rango de la lista de datos, incluyendo la fila de títulos.

El título de la columna a resumir según la función de que se trate.

El rango de celdas donde está situado el criterio. Para obtener más información o utilizar estas funciones, puedes utilizar el comando Insertar función seleccionando la categoría de Base de datos.

También puedes utilizar este comando para insertar las funciones de totales avanzados que hemos descrito (la primera se encuentra entre las funciones matemáticas y trigonométricas, mientras que CONTAR.SI y CONTAR.BLANCO están entre las funciones estadísticas). En cualquier caso, el cuadro de diálogo te informará sobre los argumentos necesarios y sobre el funcionamiento de cada función. Las funciones que tiene Excel en la categoría de bases de datos son:

BDPROMEDIO: calcula el promedio o media de una serie de números.

BDCONTAR: cuenta el número de celdas que tienen números.

BDCONTARA: cuenta el número de celdas que tienen algún valor (es decir, que no están en blanco).

BDEXTRAER: obtiene un único registro de la lista, según los criterios. Si hay más de un registro que cumple los criterios o no hay ninguno que los cumpla, la función devuelve un valor de error.

BDMAX: encuentra el valor máximo.

BDMIN: encuentra el valor mínimo.

BDPRODUCTO: multiplica los valores de una columna de la lista.

BDDESVEST: calcula la desviación estándar, teniendo en cuenta que los datos de la lista son una muestra de la población total.

BDDESVESTP: calcula la desviación estándar, basándose en toda la población de datos.

BDSUMA: suma los números de una columna de la lista.

BDVAR: calcula la varianza, basándose en una muestra de la población.

BDVARP: calcula la varianza, teniendo en cuenta que los datos de la lista representan el total de la población.

Excel Experto 2007

39

Trabajo con fórmulas

20. ERRORES EN LAS FÓRMULAS

En esta lección vamos a trabajar con fórmulas y funciones de Excel, viendo algunas herramientas que tiene Excel para ayudarnos en este aspecto. Ya sabes que Excel te puede avisar cuando cree que has cometido algún error al introducir valores en las celdas, dándote información de dicho error para que lo puedas solucionar. Por ejemplo, si introduces un número como texto en una celda (poniendo un apóstrofo ( ' ) delante), Excel te avisará, por si se trata de un error. En el caso de las fórmulas, Excel te indica si no ha podido obtener, el resultado de la fórmula, señalándote el posible error. Recuerda los valores de error que puede mostrar Excel al introducir fórmulas:

#¡DIV/0!: error de división por cero, en la fórmula se ha puesto una división que divide un número por cero.

#N/A: se produce cuando un valor no está disponible para una fórmula.

#¿NOMBRE?: se ha utilizado un nombre que Excel no reconoce.

#¡NULO!: se ha indicado una intersección no válida de dos áreas.

#¡NUM!: número usado de forma incorrecta o no válida.

#¡REF!: referencia a una celda no válida.

#¡VALOR!: operando o argumento equivocado. Aparece si los valores que acompañan a los operadores de una fórmula no se pueden convertir al tipo correcto.

######: es un marcador de columna, que indica que el tamaño de la columna es demasiado pequeño para que quepan los datos que hay.

También aparece cuando se utiliza una fecha o una hora negativas. Además de estos errores, Excel también te avisará en otros casos, en los que, aunque no hay ningún error en la fórmula y se puede calcular el resultado, Excel observa algo extraño y cree que puede ser una equivocación. Por ejemplo, puedes tener una serie de celdas que tengan fórmulas semejantes. Si Excel detecta que alguna de las fórmulas no sigue el modelo de las otras fórmulas, te avisará de esto. En la hoja de la siguiente imagen, las fórmulas de la columna D multiplican el valor de la celda correspondiente de la columna B por el de la columna C.

Excel Experto 2007

40

Sin embargo, la fórmula de la celda D7 es distinta, por lo que Excel nos lo indica con un pequeño triángulo verde en la esquina superior izquierda de la celda. Además, si nos ponemos en esa celda, aparece un icono de advertencia al lado de la celda. Al pulsar en este icono de advertencia aparece un menú, en el que se indica, en su parte superior, el tipo de error que ha detectado Excel (en este caso, indique que la fórmula es incoherente, es decir, que no sigue la misma relación de fórmulas de las celdas que están junto a ella) Con las opciones de este menú puedes obtener ayuda sobre este tipo de error, corregir el posible error (copiando la fórmula de la celda de arriba), omitir el error (indicando que la fórmula es correcta) o modificar la fórmula directamente en la barra de fórmulas. Por otro lado, Excel permite ver más claramente las celdas que intervienen en una fórmula. Para esto, ponte en una celda que tenga una fórmula y pulsa la tecla F2

Excel Experto 2007

41

La ventana de Evaluar fórmula te será útil sobre todo cuando una función anidada dé como resultado un valor erróneo o inesperado, ya que podrás utilizarla para poder seguir el cálculo paso a paso y ver dónde puede estar el fallo. En la ficha Fórmulas de la Cinta de opciones se encuentra el grupo de comandos Auditoría de fórmulas, en el que existen varios comandos y opciones para trabajar con fórmulas, que te pueden ayudar a encontrar y solucionar los posibles errores que puedan existir.

Pues bien, para mostrar la ventana de Evaluar fórmula debes pulsar el botón Evaluar fórmula Por ejemplo, imagina que tenemos la siguiente hoja, en la que hemos introducido la fórmula =SI(A3+A4+A5>=15;"Hay 15 puntos o más";"Hay menos de 15 puntos") en la celda A7.

Excel Experto 2007

42

Si nos situamos en esta celda A7 y pulsamos el botón Evaluar fórmula aparecerá esta ventana, que muestra las distintas partes de la fórmula, permitiéndote comprobar cómo se va calculando la fórmula con los distintos valores de las celdas. En el cuadro de Evaluación puedes ver la primera referencia subrayada. Si pulsas el botón Evaluar, Excel cambiará la referencia subrayada por el valor que tenga. Pulsando sucesivas veces Evaluar, se irá calculando cada vez la parte subrayada, hasta obtener finalmente el resultado de la fórmula.

Si decides establecer el cálculo manual, entonces deberás ejecutar el comando Calcular ahora de este mismo grupo de comandos o pulsar la tecla cuando quieras que se vuelvan a calcular las fórmulas. Para acabar el capítulo, describiremos la función SI. Esta función sirve para mostrar una cosa u otra, según se cumpla una condición o no, y se encuentra en la categoría de funciones Lógicas:

SI(condición;valor_si_se_cumple;valor_si_no_se_cumple) En esta función se analiza primero la condición; si la condición se cumple, la función devuelve como resultado lo que haya en su segundo argumento, mientras que, si no se cumple, la función devuelve lo que tenga en el tercer argumento.

Excel Experto 2007

43

Por ejemplo, en este caso, el primer argumento (es decir, la condición que va a evaluar la función SI) es comprobar si la suma de las celdas A3, A4 y A5 es mayor o igual (>=) que 15. Si se cumple la condición, la función devolverá lo que hay en el segundo argumento (es decir, el texto "Hay 15 puntos o más"), mientras que si no se cumple la condición, devolverá el tercer argumento (es decir, el texto "Hay menos de 15 puntos").

21. AUDITORÍA DE FÓRMULAS

Otra posibilidad de Excel es ver de una manera gráfica las relaciones que establecen las fórmulas entre las distintas celdas. El botón RASTREAR PRECEDENTES del grupo de comandos Auditoría de fórmulas permite encontrar las celdas precedentes de la celda actual, mostrando flechas que van desde estas celdas precedentes a la celda actual. Las celdas precedentes son las celdas que proporcionan datos a la fórmula de otra celda. En una fórmula, las celdas a las que se hace referencia son las celdas precedentes de esa celda. Por ejemplo, si la celda C2 tiene la fórmula =A2+A3+A4, las celdas A2, A3 y A4 son celdas precedentes de la celda C2.

Excel Experto 2007

44

El botón RASTREAR PRECEDENTES señala las celdas dependientes de la actual, para ver qué celda o celdas hacen referencia a la celda actual. Las celdas dependientes son las celdas que dependen del valor de otra celda. Las celdas que tienen fórmulas que hacen referencia a una celda son las celdas dependientes de ésta. Por ejemplo, si la celda C2 tiene la fórmula =A2+A3+A4, la celda C2 es celda dependiente de la celda A4 (ya que depende del valor que tenga la celda A4); la celda C2 también sería celda dependiente de la celda A2 y de la celda A3. Las flechas que aparecen en la hoja de cálculo al rastrear las celdas precedentes o dependientes se llaman flechas de rastreo, y muestran gráficamente la relación entre la celda activa y las celdas relacionadas. Para quitar las distintas flechas de rastreo que se muestran en una celda, utiliza el botón. El botón VENTANA DE INSPECCION de este grupo de comandos Auditoría de fórmulas Abre una ventana desde la que puedes ver las celdas y sus fórmulas, aunque las celdas estén en otro lugar del libro. En esta ventana tendrás que indicar las celdas que quieres inspeccionar a través del botón AGREGAR INSPECCION. Entonces podrás ver el valor de estas celdas en la Ventana Inspección, aunque te encuentres en otra hoja del libro.

Excel Experto 2007

45

Incluso, si hemos asignado un nombre a estas celdas, este nombre también aparecería en la Ventana Inspección. De esta forma podríamos identificar más claramente las celdas. Si haces doble clic en una celda de la Ventana Inspección, te desplazarás automáticamente a dicha celda, mientras que, si quieres quitar una celda de esta ventana, selecciona dicha celda y pulsa el Botón ELIMINAR INSPECCION

22. INSERTAR COMENTARIOS

Otra característica que te puede resultar de ayuda en ocasiones, es la posibilidad de insertar comentarios en las celdas. Un comentario es un texto o una nota que puedes poner asociada a una celda, de forma independiente de lo que tenga la celda. Puedes utilizar los comentarios para anotaciones propias o para informar de ciertos aspectos a otros usuarios del libro; por ejemplo, puedes poner indicaciones de qué significa el valor de una celda, de cómo funciona una determinada fórmula... Para crear y trabajar con los comentarios se utilizan los botones Del grupo de comandos Comentarios, en la ficha Revisar de la Cinta de opciones.

Desde el botón NUEVO COMENTARIO puedes introducir un comentario en la celda actual. En la parte de arriba del comentario que introduzcas en una celda aparecerá tu nombre, para que, junto con el comentario, también esté el nombre de la persona que lo ha escrito. De todas formas, puedes cambiar o eliminar el nombre en un comentario, modificándolo o borrándolo directamente en dicho comentario.

Excel Experto 2007

46

Para aceptar la introducción del comentario, pulsa fuera del cuadro del mismo. Las celdas que tienen comentarios se muestran marcadas con un pequeño triángulo rojo en su esquina superior derecha. Para mostrar el comentario de una celda, sitúa el puntero del ratón sobre una celda que tenga uno. Si te pones en una celda con un comentario, el botón MOSTRAR U OCULTAR COMENTARIOS hace que este comentario permanezca visible de manera continua. El botón MOSTRAR TODOS LOS COMENTARIOS hace visibles todos los comentarios de todas las celdas de la hoja También puedes cambiar el tamaño o la posición de un comentario, y, a continuación, arrastrando alguno pulsando el botón MODIFICAR COMENTARIO de los controladores de tamaño del comentario o arrastrando el borde del cuadro del comentario, respectivamente. Otra posibilidad es hacer clic con el botón derecho del ratón en el borde del comentario y seleccionar la opción Formato de comentario, con lo que se abrirá un cuadro de diálogo desde el que podrás cambiar la fuente de texto de todo el comentario, la alineación, el color de texto o de fondo y otras propiedades del cuadro del comentario.

23. CONTROL DEL CÁLCULO

Al aceptar las fórmulas que introduces en las celdas, se presenta en las celdas el resultado de computar la fórmula; a este proceso se le denomina cálculo. Excel actualiza solamente aquellas celdas afectadas por la introducción de nuevos valores o si cambias algún valor, evitando cálculos innecesarios. También se actualizan las celdas al abrir el libro. Por defecto, cada vez que introduces una nueva fórmula, modificas alguna fórmula existente o alguna de las celdas de las que depende la Fórmula, se vuelven a calcular automáticamente la fórmula. En la ficha Fórmulas de la Cinta de opciones, el grupo de comandos Cálculo tiene varias opciones que sirven para configurar cuándo se realiza el cálculo en los libros de Excel.

SI ELIGES LA OPCION MANUAL EN EL MENU QUE APARECE AL PULSAR EL BOTON OPCIONES PARA EL CALCULO, ENTONCES SERAS TU MISMO QUIEN DECIDA CUANDO QUIERES QUE SE RECALCULEN, LAS FORMULAS DE LA HOJA. ESTO PUEDE SER ADECUADO EN GRANDES LIBROS CON MUCHAS FORMULAS. Mientras Excel está calculando, se pueden introducir datos y elegir comandos. Para ello, Excel interrumpe momentáneamente el cálculo y luego lo reanuda de nuevo. Si decides establecer el cálculo manual, entonces deberás ejecutar el comando Calcular ahora de este mismo grupo de comandos o pulsar la tecla F9 cuando quieras que se vuelvan a calcular las fórmulas.

Excel Experto 2007

47

El comando calcular ahora o la tecla f9 vuelven a calcular las formulas de todos los libros, abiertos mientras que con calcular hoja o con shift + f9 solo se calcularan las formulas de la hoja actual, estas dos opciones solo calculan las formulas que han cambiando desde el ultimo calculo, si quieres calcular todas las formulas de todos los libros abiertos, hayan cambiando o no pulsa ctrl + alt + f9 Por otra parte, ya sabes que los cálculos que Excel realiza están basados en los valores internos almacenados en cada celda. Recuerda que los valores se almacenan con una precisión de 15 dígitos y, sin embargo, se presentan según el formato de la celda en la que están. Si el formato de presentación de la celda tiene menor precisión que el valor utilizado para los cálculos, se redondearán los números al mostrarlos en la celda. En ocasiones, puedes querer hacer los cálculos utilizando los valores que se ven en las celdas, en lugar de usar los valores realmente almacenados en la hoja de cálculo. Por ejemplo, si decides que no se muestre ninguna posición decimal en los números, entonces los resultados de las fórmulas pueden ser erróneos. En el siguiente ejemplo, hemos introducido las fórmulas que te indico en las celdas de la hoja, y hemos asignado a las celdas de la columna Al formato General y a las celdas de la columna B el formato de Número sin decimales.

Observa que la suma de B3 no es correcta, ya que se han utilizado los valores internos y se ha redondeado el resultado, al indicar que no se utilicen decimales. En cuenta que el calcular con precisión de pantalla afecta a los cálculos de todo el libro, siempre que las celdas no estén en el formato General. Al trabajar con fórmulas, también tenemos la opción de reemplazar la fórmula por su resultado. Para ello, copia la celda que tiene la fórmula y utiliza la opción Pegar valores del menú del botón PEGAR (o la opción Valores del cuadro Pegado especial, accesible desde este mismo menú). De esta forma, la fórmula desaparece y se reemplaza por su valor. Este proceso también sirve para eliminar vínculos a otros libros, sustituyéndolos por el valor que tienen en ese momento.

Excel Experto 2007

48

Otra de las opciones que tiene Excel es la de reemplazar parte de una fórmula por su resultado. En este caso, deberás seleccionar la parte de la fórmula a calcular en la barra de fórmulas y pulsar la tecla F9 (observa que es la misma que se utiliza con el cálculo manual; en realidad, lo que se hace es calcular la fórmula que se tiene seleccionada en ese momento). Otro aspecto relacionado con los cálculos es la utilización de referencias circulares en las fórmulas. Estas referencias ocurren cuando dos fórmulas dependen entre sí para obtener sus resultados; dicho de otra forma, que una fórmula hace referencia a su propia celda, directa o indirectamente. Cuando Excel detecta una referencia de este tipo, presenta un mensaje de aviso. Para las fórmulas que requieran este tipo de referencia, accede al cuadro de las Opciones de Excel y, en la ficha Fórmulas, activa la casilla Habilitar cálculo iterativo. De manera predeterminada, Excel detiene los cálculos cuando se sobrepasan las 100 iteraciones (una iteración es el cálculo repetido de una hoja de cálculo hasta que se cumple una determinada condición numérica), o bien cuando la diferencia entre una iteración y la siguiente sea menor que 0,001; si lo necesitas, puedes modificar Estos valores desde este cuadro de Opciones de Excel.

Excel Experto 2007

49

24. FUNCIONES DE BÚSQUEDA

Para acabar la lección, veremos algunas funciones que te pueden ser útiles. Las funciones de búsqueda proporcionan información a partir de los valores que se les pasan como parámetros. De esta forma, puedes utilizar una fórmula de la hoja de cálculo para buscar en las celdas valores que estén asociados con otros dentro de la misma lista. Una de las funciones de búsqueda más utilizadas es la función BUSCARV, que realiza una búsqueda de un elemento en la primera Columna de una lista y, si lo encuentra, devuelve el valor que tenga Dicho elemento en otra columna de la lista. Cuando el elemento buscado no aparece en la lista, se produce un valor de error (#N/A). Por ejemplo, en la celda G6 tenemos la fórmula =BUSCARV (G5;A3:C10;2;FALSO). Esta fórmula busca el elemento que está situado en G5 (en este caso, el valor F-210) en el rango de celdas A3:C10 y, si lo encuentra, devuelve el valor que hay en la columna 2 de esa lista (en este caso, la descripción del artículo). El último parámetro de la función se utiliza para indicar si la lista está ordenada o no, ya que, si la lista está ordenada y el valor a buscar no existe, Excel puede proporcionar como resultado el valor más aproximado. Para indicar que está ordenada y buscar un valor aproximado, introduce el valor VERDADERO. Otras funciones de búsqueda que se encuentran en la categoría de Búsqueda y referencia, al igual que la función BUSCARV, y que también puedes utilizar para buscar valores en listas, son COINCIDIR e INDICE.

Consulta la ayuda de Excel si necesitas más información Sobre estas u otras funciones.

COINCIDIR(Valor_buscado;Matriz_buscar_en;Tipo_ de_coincidencia) COINCIDIR compara el valor de búsqueda (primer argumento) con el rango de celdas que le pasamos como segundo argumento, y devuelve el índice del registro en la lista (es decir, la posición que ocupa en esta lista de celdas). Por ejemplo, si escribimos la función =COINCIDIR("F-210"; A4:A10;0), devolverá el valor 3, porque el valor F-210 es el tercero en el rango de celdas A4:A10.

INDICE(Matriz;Número_de_fila;Número_de_columna) Devuelve el valor de la celda, a partir del rango de celdas que le pasamos como primer argumento y según el número de fila y de columna que indiquemos. Por ejemplo, la función =INDICE(A3:C10;4;2) devolvería Ferrari-V.40, porque este es el texto que está en la fila 4 y en la columna número 2 del rango de celdas A3:C10.

Excel Experto 2007

50

Si introdujéramos =INDICE(A4:A10;6), en este caso devolvería V-110, porque este es el valor de la celda que está en el sexto lugar (el argumento número de fila es 6) en el rango A4:A10. Estas tres funciones que hemos visto suelen ser las más utilizadas, pero en la categoría de Búsqueda y referencia hay más funciones de búsqueda que te pueden ser útiles en determinados casos.

Temas Avanzados sobre gráficos

25. GRÁFICOS TRIDIMENSIONALES Y PLANTILLAS DE GRÁFICO Ya sabes cómo puedes crear un gráfico a partir de los datos que se encuentran en una hoja de cálculo, a través de los botones adecuados del grupo de comandos Gráficos de la ficha Insertar. Una vez creado un gráfico, puedes modificarlo a través de las fichas contextuales Diseño, Presentación y Formato, que aparecen al seleccionar un elemento de este tipo.

Excel Experto 2007

51

Recuerda que puedes incrustar el gráfico en la misma hoja de cálculo o crear una hoja de gráfico. Además, también sabes cómo agregar datos al gráfico sin necesidad de volver a crearlo. Ahora veremos cómo realizar tareas que necesitarás cuando quieras conseguir gráficos más complejos. Sin duda, el cambio más radical que podemos hacer sobre un gráfico es cambiar el tipo de gráfico. Cada uno de los tipos de gráficos que existe tiene sus propias características, que hacen que sea más o menos adecuado para los datos que vayamos a representar. Para cambiar el tipo de gráfico, selecciona el gráfico (pulsando en uno de sus lados o en una zona libre del mismo, es decir, sin elementos) de la ficha contextual Diseño, en la Cinta de opciones y pulsa el botón CAMBIAR

26. TIPO DE GRAFICO

Desde el cuadro de diálogo que aparecerá puedes cambiar el tipo de gráfico. Puedes cambiar el gráfico a un subtipo del tipo actual o a un tipo totalmente distinto.

Puedes seleccionar el tipo en la lista de la parte izquierda del cuadro y, a continuación, seleccionar el subtipo adecuado en la parte derecha o seleccionar directamente el nuevo tipo de gráfico desde todos los disponibles en la parte derecha de la ventana. En la mayoría de tipos existen subtipos de dos y de tres dimensiones, con lo que tienes una gran variedad de posibilidades a elegir.

Excel Experto 2007

52

Ten en cuenta que, en muchos casos, para comprobar el efecto producido al cambiar el tipo de gráfico o verlo correctamente, será necesario variar las dimensiones del gráfico o, por lo menos, las de su área de trazado. Con los botones CUADRO GRAFICO y PLANO INFERIOR DEL GRAFICO del grupo de comandos Fondo de la ficha Presentación de la Cinta de opciones, puedes configurar el formato de estos elementos, que se muestran en la parte de detrás y en la parte de debajo del gráfico Tridimensional. Además, en los gráficos tridimensionales, tienes la posibilidad de cambiar la orientación y perspectiva del mismo, lo que puede ayudar a Una mejor comprensión del gráfico. Para esto, pulsa el botón GIRO 3D . Los controles X, Y y Z definen la orientación o giro: X representa al eje vertical, Y al eje horizontal y Z a la profundidad. Al cambiar algo de este cuadro de diálogo, el gráfico cambiará Automáticamente. Puedes mover el cuadro para poder ver el gráfico y el cuadro al mismo tiempo. Una vez has creado y configurado un gráfico, puedes querer utilizar el mismo tipo y formato en otros gráficos. Para esto, puedes crear una plantilla de gráfico. De esta forma, cuando estés creando otros gráficos en otras hojas o en otros libros, podrás utilizar la plantilla de gráfico, con lo que el gráfico tendrá automáticamente el aspecto adecuado: tipo, elementos que se muestran, colores, etc.

27. ELEMENTOS DEL GRÁFICO

Para mostrar, ocultar o cambiar los elementos del gráfico puedes utilizar los controles de la ficha contextual Presentación. Puedes incluir un título al Por ejemplo, desde el botón TITULO DEL GRAFICO gráfico, encima de éste o superpuesto en su parte superior, sin cambiar el tamaño del mismo. Desde las opciones de ROTULOS DE EJES podrás indicar si deben aparecer Etiquetas en los ejes vertical y horizontal del gráfico, que sirvan para describir lo que se representa en cada uno de estos ejes. Sirve para ocultar o mostrar la leyenda El botón LEYENDA (recuerda que la leyenda es un cuadro que sirve para identificar los colores o diseños que se han asignado a las distintas series de datos o Categorías del gráfico, es decir, que se utiliza para saber cómo interpretar Las series del gráfico), indicando la situación donde aparecerá: debajo del gráfico, encima de éste, a su izquierda, etc. Recuerda, sin embargo, que puedes seleccionar la leyenda y situarla en el lugar que quieras arrastrándola con el ratón. Desde el menú del botón ETIQUETAS DE DATOS Puedes mostrar etiquetas en Las distintas series de datos, que muestran el dato que se Está representando en cada punto de datos del gráfico. Si previamente hubieras seleccionado una serie de datos del gráfico, sólo se añadirían etiquetas de datos a esta serie. De todas formas, podrías seleccionar la etiqueta o etiquetas que quisieras y eliminarlas, pulsando la tecla SUPRIMIR. En cuenta también que tanto los rótulos como las etiquetas de datos son elementos del gráfico, por lo que si quieres podrías cambiar el lugar donde aparecen, para que no se superpongan los textos con otros elementos del gráfico o para que se sitúen exactamente en el sitio que quieras. Desde LINEAS DE LA CUADICULA puedes hacer que el gráfico muestre más o menos líneas de división, tanto horizontales como verticales. Estas líneas ayudan a situar los valores que están siendo representados en el gráfico, aunque, si existen Demasiadas divisiones, el gráfico puede llegar a ser confuso. Recuerda que, en ocasiones, al añadir o eliminar elementos al gráfico, puede ser conveniente ajustar el tamaño del gráfico o de estos Elementos, para el conjunto se vea de la mejor manera posible. Por ejemplo, si añades una línea de tendencia puedes mostrar en el gráfico cuál ha sido la tendencia de los datos a lo largo de un

Excel Experto 2007

53

período de tiempo. En este caso, tras seleccionar la opción adecuada, se mostrará un cuadro desde el que podrás elegir la serie en la que quieres crear la línea de tendencia.

Después de elegir los elementos que quieres mostrar o no en el gráfico, puedes configurarlos y cambiar su formato. Para esto, selecciona el elemento y utiliza los controles de la FICHA FORMATO Recuerda que puedes seleccionar un elemento del gráfico haciendo clic en él o eligiéndolo desde la lista Elementos de gráfico, que está en la parte superior del grupo de comandos Selección actual. A continuación, puedes utilizar el botón RELLENO DE FORMA, para elegir el relleno o fondo del elemento (puedes seleccionar un color, para un degradado, una textura, etc.),el botón CONTORNO DE FORMA configurar la línea de contorno (grosor, color y tipo de línea). Desde el grupo de comandos Estilos de WordArt puedes cambiar el color de relleno y de Contorno del texto, además de poder seleccionar Un estilo visual o un efecto a aplicar en el texto (Sombra, resplandor, reflexión, etc.).

Excel Experto 2007

54

Desde la ficha Alineación del cuadro del Formato puedes indicar un ángulo con el que se presente el texto escrito. Esto puede ser útil en ocasiones si tienes poco espacio en el gráfico y quieres aprovecharlo al máximo, sin que el texto ocupe demasiado lugar. Además, los textos de un gráfico también pueden ser modificados como cualquier otro que exista en la hoja de cálculo: cambiar la fuente o el tamaño, aplicar más o menos realces, alineación, etc.

28. DATOS EN UN EJE SECUNDARIO

Otra característica que ya conoces sobre los gráficos es la Posibilidad de añadir series al gráfico, de forma que también sean representadas en el mismo, sin necesidad de crear de nuevo el gráfico. La forma más cómoda y rápida de hacer esto suele ser cambiar el tamaño del rectángulo que señala el rango de celdas representadas en el gráfico, que aparece en la hoja de cálculo al seleccionar el gráfico. Por otro lado, en ocasiones puedes tener en un gráfico una determinada serie de datos cuyos valores distan mucho de los de las otras series.

Si las representas todas juntas, es posible que la escala de los ejes impida ver la serie que tiene los valores mucho más pequeños que el resto. En este caso, es posible incluir dicha serie en un eje secundario. Para esto, selecciónala (pulsando sobre ella, a través de la lista Elementos de gráfico o con las teclas del cursor) y accede a su cuadro de Formato (por ejemplo, pulsando el botón APLICAR FORMATO DE selección En este caso, desde la sección Trazar serie en de este cuadro puedes indicar que una serie se represente respecto a un eje secundario. En estos casos, para

Excel Experto 2007

55

diferenciar el eje secundario, puede ser conveniente cambiar el tipo de gráfico sólo para esa serie de datos. Por Ejemplo, puedes cambiar la serie de datos a un gráfico de líneas. Para esto, Tras seleccionar la serie de datos adecuada, utiliza el botón CAMBIAR TIPO DE GRAFICO . Al tener seleccionada una única serie de datos y no el gráfico completo, este botón cambiará únicamente el tipo de gráfico de dicha serie. Si tuvieras seleccionado todo el gráfico y no sólo una serie, el tipo de gráfico seleccionado se aplicaría a todo el gráfico. De esta forma conseguirías combinar más de un tipo de gráfico en el mismo gráfico. Esto será posible siempre que se combinen distintos tipos de gráficos bidimensionales: no puedes utilizar esta opción si has aplicado un tipo de tres dimensiones.

29. USAR IMÁGENES EN GRÁFICOS

Otra característica de los gráficos de Excel que puedes usar para mejorar la presentación de tus gráficos es usar imágenes en sus elementos. Recuerda que, al igual que en otras ocasiones, lo primero que tienes que hacer es seleccionar el elemento que vayas a cambiar. Tras seleccionar el elemento adecuado, ya hemos comentado que desde el botón puedes indicar el formato del borde o contorno de dicho elemento, mientras que desde indicarás el aspecto de su área o relleno. Estos dos botones están en la ficha Formato de la Cinta de opciones. En el submenú Degradado dispones de más opciones, como rellenar las columnas con un degradado formado por uno o dos colores o elegir algún degradado preestablecido por Excel. La opción Más degradados permite personalizar a tu manera el degradado que quieres utilizar. Desde las opciones de Textura podrás elegir entre distintas texturas o tramas para el área de las columnas.

Excel Experto 2007

56

Además de todas estas posibilidades, también puedes utilizar una imagen que tengas en tu ordenador como relleno de los elementos del gráfico. Para esto, selecciona la opción Imagen en el menú del botón RELLENO DE FORMA o indícalo en el cuadro de Formato, en la categoría Relleno. Desde este cuadro de diálogo Formato, además de poder seleccionar la textura o imagen que quieres utilizar, puedes establecer ciertas opciones sobre la misma, por ejemplo, el nivel de transparencia o crear un mosaico con la imagen.

Excel Experto 2007

57

30. IMPRESIÓN DE GRÁFICOS

Antes de imprimir los gráficos, es conveniente observar qué resultados se obtendrían, para comprobar que la impresión será la adecuada. Recuerda que puedes ver cómo quedarían impresos los gráficos a través de la opción Imprimir - Vista preliminar, en el menú del Botón de Office. Ten en cuenta que existen algunas diferencias a la hora de imprimir gráficos, dependiendo de si los gráficos son incrustados o si Están situados en una hoja de gráfico. En primer lugar, si el gráfico es Incrustado, se puede cambiar su tamaño o posición simplemente mediante la selección y el arrastre con el ratón. Los gráficos incrustados se imprimen con el tamaño, las proporciones y la posición que tienen en la hoja de cálculo. Puedes variar el tamaño del gráfico o su posición desde la vista normal de la hoja o desde la vista previa de salto de página. En cuanto a los Gráficos que están en una hoja de gráficos, por defecto, la configuración del gráfico está predeterminada para una orientación horizontal. Desde el botón CONFIGURAR PAGINA mostrarás el cuadro de diálogo que permite configurar varios aspectos de la impresión. Por ejemplo, desde la ficha Márgenes de este cuadro puedes cambiar los márgenes de la página impresa. Recuerda que, si activas de la Cinta de opciones podrás ver los La casilla MOSTRAR MARGENES Márgenes en la vista previa y modificarlos si quieres. Si activas esta casilla y pulsas y arrastras en la vista preliminar Para cambiar los márgenes, es posible que también tengas que cambiar el tamaño del gráfico, para adaptarlo al nuevo tamaño de impresión. Necesitarás una impresora que admita el color para que la impresión sea en color.

Excel Experto 2007

58

En caso contrario, Excel transformará los colores a una escala de grises que permita diferenciar una serie de otra. Esto lo podrás ver en la Presentación preliminar. Si tu impresora admite el color y quieres imprimir el gráfico en blanco y negro, activa la casilla Imprimir en blanco y negro de la ficha Gráfico. Esta casilla aparece desactivada por defecto si tu impresora es en color de forma que el gráfico se imprime en color.

Como hemos visto en esta lección, podemos cambiar y configurar Los gráficos de muchas maneras, adaptándolos totalmente a nuestro gusto, configurándolos de la forma que queramos en cada momento.

Excel Experto 2007

59

Acceso a Datos Externos (I)

31. ACCESO A ARCHIVOS DE TEXTO

A veces es necesario trabajar con datos que no tenemos en nuestro ordenador o que han sido creados con otras aplicaciones distintas a Excel. Por ejemplo, podrías tener un archivo de base de datos donde has ido almacenando información sobre los pedidos realizados en una empresa. Este archivo podría haber sido creado con una aplicación de bases de datos, como Access o dBase. Pues bien: Excel nos permite acceder a un gran número de formatos de datos distintos: ficheros de texto, bases de datos, etc.

Al acceder y trabajar con los datos desde Excel, podemos hacer Uso de todas sus herramientas para obtener más información de estos Datos: hacer cálculos, mostrar la información en un gráfico, filtrar los Datos, etc. Incluso podemos acceder a información proveniente de páginas Web, bien sea desde un sitio de Internet o desde una intranet. Además, Excel también permite, al acceder a datos almacenados en bases de datos, seleccionar la información a extraer con consultas, De forma que sólo obtengamos y trabajemos con el conjunto de datos que realmente nos interesa. Empezaremos viendo cómo utilizar los datos almacenados en archivos de texto. Los archivos de texto son un medio de compartir información entre aplicaciones informáticas y entre usuarios muy utilizado, ya que son archivos que se pueden leer desde varios programas. Excel permite acceder a los datos almacenados en archivos de texto de una forma muy sencilla, pero la información que hay en estos Archivos deberá estar delimitada de alguna forma para que tenga sentido en la hoja de cálculo. Para importar datos desde un archivo de texto, selecciona la ficha Datos de la Cinta de opciones pulsa el botón OBTENER DATOS EXTERNOS

Excel Experto 2007

60

El menú de este botón tiene varias opciones de Excel para trabajar con datos externos, importándolos Desde distintas fuentes. En el caso que nos ocupa, Debemos seleccionar la opción Desde texto de este menú. A continuación tenemos que indicar el origen de los datos, es decir, el archivo de texto al que vamos a acceder para obtener los datos.

Una vez aceptado este cuadro de diálogo, Excel te proporciona un asistente, que te ayudará a indicar cómo debe importarse la información almacenada en el archivo de texto. Como ya hemos dicho, lo más importante en este caso es que la información del archivo de texto esté delimitada de alguna forma, para que Excel pueda situarla en distintas columnas de la hoja de cálculo. Los distintos campos de información del archivo pueden estar separados por algún carácter o tener un ancho fijo, alineados en Columnas.

Excel Experto 2007

61

En el apartado Tipo de los datos originales del primer paso del asistente podemos indicar cómo están estructurados los datos en el archivo de texto, así como la línea del archivo desde la que comenzaremos a obtener los datos. En el siguiente paso del asistente indicarás los caracteres que separan los campos o la anchura de los campos, en función de lo que hayas seleccionado en la ventana anterior del asistente. En la muestra inferior del cuadro de diálogo iremos viendo el resultado de la importación de estos datos.

Excel Experto 2007

62

En el tercer y último paso puedes establecer el formato de los datos para cada campo, es decir, indicar qué formato de número, fecha o de texto deseamos aplicar en Excel. Pulsando en el botón Avanzadas, incluso podemos indicar qué separador se utiliza para la parte decimal, cuál para los miles y el formato de los números negativos. Al finalizar el asistente, tendrás que indicar a Excel la celda que actuará como esquina superior izquierda para el rango de datos que se va a crear. Una vez has obtenido los datos del archivo, esta información se Encuentra en Excel a la espera de que realices cualquier análisis con ellos. Puedes aplicar cualquier proceso de los que conoces sobre esta información (gráficos, ordenación, filtrado, operaciones con los datos, etc.).

Excel Experto 2007

63

32. BASES DE DATOS DE ACCESS

Si tenemos mucha información y además está relacionada entre sí, no solemos tener los datos en archivos de texto, sino que utilizamos programas para crear y gestionar esta información. Entre otros, podemos trabajar con la información a través de programas de bases de datos. Entre estos programas de bases de datos está Microsoft Access.

Para acceder desde Excel a la información que tengamos en una base de datos de Microsoft Access utilizaremos también el botón OBTENER DATOS EXTERNOS seleccionando esta vez la opción Desde Access. Las bases de datos de Access 2007 tienen extensión accdb (Access Database), aunque también podemos importar datos de otras versiones de Access (que tendrán extensión mdb). El proceso hasta ahora es prácticamente igual al que hemos descrito antes al acceder a los datos de archivos de texto: nos situamos en la ubicación del archivo externo y seleccionamos este archivo Tras seleccionar el archivo de base de datos de Access, aparecerá un cuadro con las tablas y consultas que tiene dicha base de datos, donde simplemente debes seleccionar dónde se encuentran los datos Que quieres mostrar en Excel. En este caso, además de poder incluir los datos Directamente en la hoja, también podemos ver los datos en Excel como una tabla dinámica o como una tabla y un gráfico dinámicos (más adelante veremos en qué consisten y cómo se utilizan estos elementos). De la misma forma que antes, al aceptar el cuadro ya tenemos la información disponible en Excel. Como los datos ya están organizados de forma estructurada, Excel sabe cómo distribuirlos en filas y columnas al realizar la importación.

Excel Experto 2007

64

En este caso, Excel crea una tabla para distribuir los datos, de forma que te sea más fácil trabajar y analizar estos datos, al poder cambiar el formato, ordenar y filtrar más fácilmente estos datos relacionados. Otra forma de utilizar los datos de Access en Excel es utilizar las opciones de Copiar y Pegar: muestra en Access los datos en forma de hoja de datos (bien sean los datos de una tabla, de una consulta o de un formulario), selecciona los registros que quieras, cópialos, ponte en Excel y pega la información copiada. Ten en cuenta, tanto en este último caso como al importar archivos de texto, que si cambias los datos en Excel, estas modificaciones no se reflejarán en los datos originales, que seguirán igual. Por otra parte, desde Access puedes exportar objetos a Excel. De esta forma, Access guardará los datos de la tabla, consulta, formulario o informe seleccionado como un documento de Excel (es decir, con extensión xlsx), pudiendo abrir desde Excel este documento y ver los datos. Además, desde Access también puedes vincular los datos que tengas en un libro de Excel, de forma que los verás en Access como si se tratara de una tabla. De esta forma, si modificas los datos desde Excel, también se verán estos cambios desde el vínculo existente en Access.

33. ACTUALIZAR LOS DATOS

Si el archivo de texto o la base de datos sobre la que has importado los datos se modifica, es posible que quieras que estas modificaciones se vean reflejadas en la hoja de Excel. Por ejemplo si has importado datos desde una base de datos, creando una tabla en Excel, a través del grupo de comandos DATOS EXTERNOS DE LA TABLA, en la ficha contextual de la tabla, tienes siempre la posibilidad de actualizar los datos importados haya ocurrido o no alguna modificación de los mismos.

Esto es posible porque Excel guarda en el libro de trabajo la información sobre el archivo (y la consulta, en caso de que hayas utilizado alguna) que ha proporcionado los datos, de forma que pueda acceder de nuevo a ellos cuando quieras actualizarlos. Si pulsas el botón Propiedades del rango de datos se mostrará un cuadro de diálogo, en el que podrás establecer algunas de las características Que afectan al modo en que Excel trabaja con los datos externos. Por ejemplo, desde el apartado Diseño y formato de datos puedes activar distintas opciones que influyen en el aspecto de los datos Importados, como si se deben incluir números de fila en los distintos registros, se debe ajustar el ancho de las columnas al dato más ancho o se deben mantener el formato de las celdas.

Excel Experto 2007

65

Las opciones de la parte inferior del cuadro indican el comportamiento al actualizar los datos desde la base de datos, en caso de que cambie el número de registros. En el caso de importar datos desde un archivo de texto, este cuadro es accesible desde el botón PROPIEDADES de la ficha Datos. En este caso, además de estas opciones, existe la casilla Guardar definición de consulta; si la desactivas, Excel no podrá volver a actualizar los datos externos desde el archivo De texto original, ya que éstos serán guardados como un rango estático de datos:

Excel Experto 2007

66

Si pulsas el botón Propiedades de conexión aparecerá otro cuadro de diálogo, en el que tienes más opciones sobre la conexión con los Datos externos y su comportamiento respecto a la actualización de datos.

Si quieres que los datos importados se actualicen automáticamente cada cierto periodo de tiempo, activa la casilla Actualizar cada e indica cada cuántos minutos Excel actualizará automáticamente estos datos. Con Actualizar al abrir el archivo, los datos se actualizarán desde el archivo externo al abrir el libro de Excel. Si activas esta última opción, la casilla Quitar datos del rango de datos externo antes de Guardar el libro hace que se guarde en el libro sólo la definición de la consulta, pero no los datos externos. En la ficha Definición puedes ver información sobre la conexión y su origen. Si pulsas el botón Desvincular de la Cinta de opciones se eliminará la conexión con el rango de datos externo, permaneciendo los datos en la hoja de Excel. Así tendrás los datos en la hoja de cálculo de una forma independiente, como si los hubieras introducido directamente en Excel. De esta forma, Excel no podrá volver a actualizar los datos externos, ya que éstos estarán almacenados como un rango estático de Datos. Indicarte también que en una misma hoja de cálculo puedes tener Más de un rango de datos externos. De la Cinta de opciones actualizarás los Con el botón ACTUALIZAR datos importados desde la base de datos en la que te encuentres. Si Hubiera cambiado algo en estos datos, ahora habríamos visto las modificaciones en los datos de la hoja de cálculo. Si quieres actualizar todos los datos que hayas importado en el libro actual, aunque provengan de archivos o consultas distintas, podrás hacerlo a través de la opción Actualizar todo, en el menú de este botón Si hubieras cambiado algún dato de los importados desde Excel, al actualizar los datos, se perderían estas modificaciones, mostrando los datos tal como se encuentran en ese momento en los archivos originales.

Excel Experto 2007

67

34. ACCESO A DATOS DE LA WEB

Finalmente, veamos cómo podemos acceder a datos provenientes de páginas web, ya sea desde algún sitio de Internet o publicadas en una intranet (por ejemplo, en una intranet de una empresa u organización).

OBTENER DATOS EXTERNOS

La ficha Datos. Se abre una ventana que funciona como un navegador de Internet, desde el que puedes ir hasta la página que tenga los datos a importar. La página web debe mostrar los datos en forma de tablas, para Que Excel los reconozca y los importe correctamente en la hoja de cálculo.

Tras acceder a la página web deseada, deberemos marcar lo que queremos importar. Fíjate cómo Excel ha señalado las distintas tablas que hay en la página con flechas de color amarillo. Utiliza estas flechas amarillas para indicar lo que quieres importar. Si quieres importar toda la página, pulsa en la flecha amarilla que aparece en la esquina superior izquierda de la misma. También podemos establecer algunas opciones interesantes a la hora de importar los datos desde una página web. Para ello, pulsa en el botón Opciones de la parte superior del cuadro de diálogo.

Excel Experto 2007

68

Por ejemplo, puedes indicar que Excel respete el formato que se muestra en la página web en los datos importados en la hoja de cálculo, activando la opción Formato HTML completo. Una vez establecidas las opciones y seleccionadas las tablas a importar, pulsa en el botón Importar, con lo que Excel incorporará la información en la hoja de cálculo. Recuerda que, para poder realizar este proceso, los datos se deben mostrar en forma de tablas en la página web, estructurados adecuadamente en filas y columnas. A continuación podrías trabajar con esta información aplicando todos tus conocimientos de Excel.

Excel Experto 2007

69

Acceso a Datos Externos (II)

35. ACCESO A BASES DE DATOS

Ya sabes que desde Excel puedes acceder a archivos externos. Entre otros, puedes acceder a los datos que tengas en archivos de bases de datos (archivos creados desde una aplicación de bases de datos, que permite trabajar con una serie de datos relacionados, como, por ejemplo, Access o dBase). Además de poder ver los datos de estos archivos, desde Excel también podemos realizar consultas sobre dichos datos, utilizando el complemento Query, de forma que sólo importemos a Excel el Conjunto de datos que realmente nos interese. Una consulta es un método para extraer los datos que indiquemos de

una base de datos. El resultado de la consulta será enviado a Excel, donde podrás realizar cualquier proceso que necesites con estos datos.

Para poder acceder mediante Query a una determinada base de datos, Es necesario haber instalado el controlador de bases de datos que permita Acceder a dichos datos. El controlador es un software que se instala como Extensión de un programa (en este caso, de Excel), y que sirve para ver o Utilizar en este programa datos creados con otras aplicaciones. Por ejemplo, si quieres acceder a ficheros de bases de datos Creados con dBase, deberás tener instalado en tu equipo un controlador dBase para ello. En Excel se incluyen distintos controladores de bases de datos, de forma que puedas acceder a esos tipos de archivos de bases de datos.

Excel Experto 2007

70

Entre los distintos controladores que se incluyen en Excel para acceder a datos están:

Bases de datos de Microsoft Access.

Bases de datos de dBase.

Bases de datos de un servidor SQL (SQL Server).

Bases de datos en formato de texto.

Listas creadas en Microsoft Excel.

Bases de datos de Microsoft FoxPro.

Bases de datos de Paradox.

Bases de datos de Oracle. Si quieres acceder a otro tipo de bases de datos, tendrás que conseguir e instalar su controlador, para que Excel pueda acceder a la información de ese tipo de archivos. Vamos a ver cómo podemos acceder directamente a una base de datos, sin hacer ninguna consulta sobre ella. Recuerda que desde el botón OBTENER DATOS EXTERNOS, que está en la ficha Datos de la Cinta de opciones, podemos seleccionar el origen de datos externo desde el que queremos obtener los datos.

Desde las opciones que aparecen al pulsar en De otras fuentes puede importar datos externos desde otros orígenes de datos (SQLServer, archivos XML...). Para obtener datos desde una base de datos, Selecciona la opción Desde el Asistente para la conexión de datos. El primer paso es indicar el origen de los datos, es decir, la base de datos a la que vamos a acceder. A continuación, dependiendo de la opción que seleccionemos, aparecerán distintas opciones, para que podamos configurar el origen de datos de que se trate. Por ejemplo, si seleccionar el tipo DNS (nombre de origen de datos) de ODBC, podrías seleccionar

el origen de datos y, a continuación, el directorio en el que está la base de datos a la que quieres acceder. Después de introducir toda la información necesaria, indica la celda que actuará como esquina superior izquierda para el rango de datos que se va a importar desde la base de datos. Con esto tendremos la información de la base de datos en forma de tabla de Excel Como ves, es bastante sencillo obtener datos desde una base de datos de la que Excel dispone su controlador.

Excel Experto 2007

71

36. CREAR UN ORIGEN DE DATOS

Para seleccionar cierta información de una base de datos, es necesario crear una consulta que defina claramente el conjunto de datos a los que queremos acceder. Al realizar la consulta, estaremos extrayendo la información que queramos a partir de un origen de datos. Para crear la consulta, selecciona la opción De otras fuentes - Desde Microsoft Query del menú del botón OBTENER DATOS EXTERNOS

El primer paso para la realización de la consulta es indicar el origen de los datos, es decir, entre otras cosas, la base de datos a la que vamos a acceder. Lo podemos indicar de la misma forma que hemos hecho antes, pero, si necesitas especificar características especiales a la hora de conectarse a la base de datos (por ejemplo, incluir una contraseña o especificar el directorio donde buscar los archivos), puedes crear un origen nuevo de datos y almacenarlo, de forma que lo puedas utilizar Cuando lo necesites. Para crear un nuevo origen de datos, selecciona la opción correspondiente en la lista y acepta el cuadro. En el cuadro que aparecerá, primero tendrás que indicar el nombre que asignarás a dicho origen, que te servirá para utilizar dicho origen de datos posteriormente, cuando quieras acceder a la información que está en él. En la lista del segundo campo tienes que indicar el controlador de bases de datos que se utilizará para acceder a la base de datos. En definitiva, Estarás indicando el tipo de base de datos al que vamos a acceder. Finalmente, tienes que indicar la información necesaria para acceder a la base de datos, entre la que destaca la ubicación de la base de datos. Una base de datos se compone de una o más tablas, además De otros elementos. Si quieres utilizar una de dichas tablas como predeterminada para la creación de consultas que se realicen a través De este origen de datos, puedes indicarlo en el cuarto campo de este cuadro de diálogo. Recuerda que, en una tabla de una base de datos, la información se almacena de forma parecida a una lista de Excel; es decir, las columnas son los campos (categorías de información) y las filas son los registros. Tras indicar todos los datos adecuados y aceptar la ventana, habremos creado el origen de datos. En definitiva, con esto indicamos las características necesarias para que Microsoft Query pueda acceder a la información almacenada en una determinada base de datos. A continuación puedes crear consultas sobre dicho origen de datos, de forma que accedas a la información que realmente te interese. .

Excel Experto 2007

72

37. CREAR UNA CONSULTA

Ala hora de crear una consulta tienes dos posibilidades: Acceder directamente a Microsoft Query y realizarla en esta aplicación o utilizar el Asistente para consultas, que te guiará paso a paso en la creación de la consulta. Vamos a ver ahora cómo utilizar el Asistente para consultas. Recuerda que el origen de datos puede estar relacionado con un Gran conjunto de datos, mientras que una consulta simplemente es una forma de acceder a un subconjunto particular de dichos datos. Lo primero que tenemos que hacer al crear la consulta es indicar a qué origen de datos queremos acceder. En la parte inferior del cuadro de diálogo Elegir origen de datos aparece una casilla de verificación, a través de la que podremos indicar si queremos o no utilizar el Asistente para consultas. Esta casilla aparece inicialmente activada, indicando que utilizaremos este asistente.

Excel Experto 2007

73

Tras indicar el origen de datos, nos encontraremos en el primer paso del Asistente para consultas. En este paso debes indicar qué columnas o campos de las tablas de datos quieres extraer.

En muchos casos no será necesario importar todos los campos Que tiene una tabla, sino sólo algunos, que serán los que realmente nos Interesan para cada caso en concreto. Pulsa en el signo que aparece a la izquierda de una tabla podremos ver todos los campos de dicha tabla, mientras que, para agregar un campo a la consulta, selecciónalo y pulsa el botón > En el siguiente paso del asistente puedes indicar filtros para las columnas. Es decir, indicar qué registros son los verdaderamente interesantes. Por ejemplo, en una determinada tabla de una base de Datos te podrían interesar sólo “los clientes de España”. Puedes establecer más de un criterio para definir la consulta. Podrías indicar si debe cumplirse cada una de las condiciones (activando la casilla y) o que sólo sea necesario que se cumpla una de ellas (activando la casilla o).

En el siguiente paso puedes indicar qué columnas son las que establecerán el orden en que aparecerán los datos resultados de la consulta. Finalmente, el asistente te preguntará si quieres enviar los datos Resultado de la consulta a Excel o acceder a Microsoft Query con el fin de establecer otras características de la consulta. También podrías guardar la consulta. Al guardar una consulta, estarás almacenando su definición,

pero no los datos que aparezcan como resultado de la misma. Esto es así para que, cada vez que Ejecutes la consulta, ésta devuelva los

Excel Experto 2007

74

Datos actualizados y no los que se consiguió en el momento de la creación de la consulta. Al finalizar, Microsoft Query ejecutará la consulta sobre el origen de datos, obteniendo los registros que cumplan los criterios indicados.

Una vez tienes la información en Excel, puedes hacer el análisis o las operaciones que quieras con estos datos. Si quieres modificar la consulta, es decir, volver a mostrar el Asistente para consultas y cambiar algo, accede al cuadro de las Propiedades de o pulsando Propiedades conexión (a través de la opción del botón ACTUALIZAR y, a continuación, Propiedades de conexión del rango de datos ponte en la ficha Definición y pulsa el botón Editar consulta.

38. TRABAJAR CON MICROSOFT QUERY

El Asistente para consultas es una herramienta muy útil cuando queremos realizar consultas sencillas. Sin embargo, si la consulta es más compleja, será necesario trabajar en Microsoft Query directamente. para ello, accede al comando De otras fuentes - Desde Microsoft Query del botón OBTENER DATOS EXTERNOS y, en el momento de indicar el origen de datos, desactiva la casilla Usar el Asistente para consultas. De esta forma indicaremos a Excel que queremos acceder a Microsoft Query para realizar la nueva consulta, en lugar de utilizar el Asistente para consultas. El funcionamiento es parecido al del asistente. Lo primero es indicar sobre qué tablas se realizará la consulta. Una consulta puede realizarse sobre una única tabla o sobre más de una tabla.

Excel Experto 2007

75

Una vez agregada la tabla o tablas, aparecerá la ventana de Microsoft Query. En la parte superior de esta ventana se sitúan las tablas que están incluidas en la consulta; en el interior de cada panel de Tabla puedes ver los campos de la misma. En la zona inferior es donde se van ubicando los campos que intervienen en la consulta. La forma de establecer estos campos es seleccionarlos en el panel de la tabla y arrastrarlos hasta esta sección de la ventana. Al ir añadiendo los distintos campos, Query presenta Automáticamente la información almacenada en la base de datos. Esto es así gracias a que el botón Consulta automática está activado. Si estuviera desactivado, sería necesario pulsar el botón Ejecutar consulta para ver el resultado de la misma. Al agregar un criterio, pasarás a la vista de Consulta de selección, accesible también a través del botón Ocultar o mostrar criterios En esta vista podrías agregar más criterios si quisieras. Recuerda que, al agregar criterios, estamos indicando cuáles son los datos del origen de datos que realmente nos interesan. Al sitúas varios criterios en columnas distintas, estarás indicando Que se deben cumplir ambos para que el registro entre en el resultado de la consulta.

Si quieres utilizar dos o más criterios alternativos, utiliza la fila o, bajo la de Valor, e incluye el valor optativo. En este caso, un registro entrará en el resultado de la consulta si cumple como mínimo uno de Los dos criterios.

Excel Experto 2007

76

Por otra parte, también podrías ver la definición de la consulta en el lenguaje SQL (Structured Query Language, Lenguaje Estructurado de Consultas), que es un lenguaje utilizado justamente para crear consultas sobre bases de datos. Este lenguaje pretende ser una forma estándar de acceso a las bases de datos relacionales, de forma que, a través de él, puedas acceder a cualquier tipo de base de datos, sin importar el tipo de la misma o con qué aplicación fue creada. Con este lenguaje (que está fuera del objetivo de este curso) podrías crear consultas más complejas y personalizadas. Una vez realizada la consulta, no queda más que devolver los datos a Excel, tal como hicimos con el Asistente para consultas. Para ello, pulsa el botón Devolver datos o accede a la opción de menú Archivo - Devolver datos a Microsoft Excel. Todo lo que puedas hacer a través del Asistente para consultas podrás realizarlo en Query: grabar la consulta, ordenar el resultado de la misma, establecer múltiples criterios, etc. El Asistente para consultas es adecuado cuando necesites realizar una consulta simple, en la que intervengan pocas tablas y en la que los criterios de selección sean sencillos. En otros casos será necesario acudir a Microsoft Query y realizar la consulta como hemos visto ahora. Si necesitas más información, acude a la ayuda de esta aplicación Microsoft Query.

39. CONSULTAS CON PARÁMETROS

Un tipo de consulta que sólo puedes crear si trabajas directamente en Query es el de consulta con parámetros. En este tipo de consultas, el valor que se establece en alguno de los Campos que determinan el criterio de la selección de registros no es un valor fijo, sino que lo proporcionas cada vez que ejecutas la consulta. Por ejemplo, puedes tener una consulta que te permita obtener la información referente a los registros que cumplan el criterio que el Campo PAIS sea igual a España. Pero, ¿qué sucede si quieres realizar la misma consulta, pero para un país distinto? Lo mejor sería que el valor del campo PAIS fuera un parámetro que le proporcionáramos cada vez que ejecutáramos la consulta. De esta forma, te ahorrarías el tener que definir la consulta cada vez. Si te encuentras en Excel, recuerda que puedes modificar una consulta a través de la ficha Definición del cuadro de las Propiedades de conexión, pulsando el botón Editar consulta. Entonces volvería a aparecer Microsoft Query, donde podrías hacer las modificaciones Que quisieras. Pará que se te pida un valor en un campo a la hora de ejecutar la consulta, escribe, en la fila del Valor del campo adecuado y entre corchetes, el texto que quieras que aparezca al ejecutarla. Ten en cuenta que este texto que escribas entre corchetes no debe coincidir exactamente con el nombre del campo afectado.

Excel Experto 2007

77

Entonces al ejecutar la Consulta, se te pedirá que Introduzcas el valor de dicho Parámetro, y se realizará la Selección de los registros de la Base de datos respecto de dicho Valor. Al actualizar los datos Del origen de datos desde Excel (desde el botón ACTUALIZAR o la opción equivalente de la Ficha Datos de la Cinta de Opciones), Excel también Te preguntará los valores de Los parámetros. La flexibilidad que ofrecen los parámetros hace que una misma Consulta pueda servir para obtener distinta información en cada caso, sin necesidad de modificar la consulta para ello.

Excel Experto 2007

78

Tablas y Gráficos Dinámicos 40. CREAR TABLAS DINÁMICAS

Una tabla dinámica permite presentar desde distintos puntos de vista una misma información, usando para ello funciones de resumen, como la suma o el promedio. Una tabla dinámica funciona de una forma interactiva, de forma que, una vez creada, puedes resumir y organizar de distintas formas la información

mostrada. Cuando se modifican los datos origen de la tabla, se puede volver a calcular esta tabla dinámica. Además, también puedes mostrar el nivel de detalle que quieras en cada momento. Para construir una tabla dinámica, hay que indicar los campos que queremos incluir y la forma en que se van a organizar. En una tabla dinámica, entendemos por campo una categoría de datos. Por ejemplo, en la hoja de cálculo que te muestro a continuación, Año, Mes y Producto serían campos. Un elemento es una subcategoría o un integrante de un campo. Por ejemplo: 2002, Feb, Mar, COD-1, COD-2..., son elementos. Es decir, que los elementos representan las entradas únicas de los campos en los datos de origen.

Excel Experto 2007

79

Estos campos y elementos de los datos de origen aparecen también Representados en las tablas dinámicas que crees a partir de ellos.

Por otra parte, una tabla dinámica utiliza dos tipos de campos:

Campos que se utilizarán como títulos del campo de fila, columna y campos de filtro de la tabla. Estos campos suelen contener texto.

Campos de valores que se utilizarán para realizar los cálculos (también se llaman, en ocasiones, campos de datos). Estos campos suelen tener datos numéricos.

Excel Experto 2007

80

Para crear tablas dinámicas, pulsa el botón Insertar tabla dinámica, que está en la ficha Insertar de la Cinta de opciones. Desde las opciones del menú de este botón TABLA DINAMICA, puedes Crear, además de tablas dinámicas, gráficos dinámicos. Si antes de pulsar el botón estabas situado en una celda de una lista de datos, Excel establece automáticamente el rango de datos que utilizará para crear la tabla dinámica. De todas formas, si quieres (por ejemplo, si el rango de datos establecido por Excel no es correcto), puedes cambiarlo a través del campo Tabla o rango, indicando el libro y la hoja donde están los datos, si fuera necesario. En la sección inferior del cuadro puedes indicar dónde se creará la tabla dinámica: en una hoja nueva o en una hoja que ya exista en el libro (que puede ser la misma hoja en la que estamos trabajando u otra cualquiera). Al aceptar el cuadro de diálogo se creará el esquema de la tabla En la hoja de cálculo. Entonces tendremos que establecer su diseño, indicando los campos que actuarán como categorías de información, aquéllos para los que resumiremos la información, etc.

Excel Experto 2007

81

En la parte derecha de la ventana aparece el panel Lista de campos de tabla dinámica, desde el que indicarás los distintos campos que se incluirán en la tabla, así como la función que tienen que desempeñar en la misma. Al ir seleccionando campos, Excel los añade en el área predeterminada de la sección de diseño del informe, aunque siempre puedes organizar estos campos, cambiando el área en el que se encuentran o su posición dentro del área. Vamos a ver cómo se distribuyen los campos en la tabla dinámica, según el área en la que se encuentren en el panel Lista de campos de tabla dinámica. 1. Los datos que queremos representar pueden estar en una hoja de cálculo o proceder de un origen de datos externo. 2. Los campos que estén en el área Filtro de informe se situarán como filtro de informe en la tabla dinámica. 3. Los campos que estén en el área Rótulos de columna se situarán como campo de columna en la tabla dinámica. 4. Los campos que estén en el área Rótulos de fila se situarán en el área de los campos de fila en la tabla dinámica. 5. Los campos que estén en el área Valores se convertirán en campos de valores en la tabla dinámica. Las otras opciones sirven para mover el campo a otra área de la tabla dinámica, quitarlo de la tabla dinámica o establecer ciertas características del mismo. Para añadir un nuevo campo a la tabla, indicando a la vez el área en el que se colocará, puedes utilizar el menú contextual del campo, en la sección Seleccionar campos para agregar al informe. Otra posibilidad es arrastrar el campo desde esta sección hasta el área adecuada. Al ir colocando los distintos campos en los lugares adecuados, la tabla dinámica resumirá la información que presenta el rango de datos seleccionado. De esta forma es más sencillo el analizar los datos.

Excel Experto 2007

82

Además, se calculan los totales generales, tanto por filas como por columnas; de todas formas, esta es una característica que podemos cambiar a través de las opciones de la tabla, como veremos más adelante. El botón Ver que aparece en la parte superior derecha del panel Lista de campos de tabla dinámica permite cambiar la vista de este panel, mostrando de distintas formas la lista de campos y las Áreas de colocación. Para ocultar o mostrar este panel, desactiva o que está en la ficha activa, respectivamente, el botón LISTA DE CAMPO contextual de la tabla dinámica Opciones.

41. TRABAJO CON TABLAS DINÁMICAS

Tras crear con una tabla dinámica, podemos personalizarla para que realice y presente todo aquello que nos sea de interés. Al seleccionar una tabla dinámica, aparecen las fichas contextuales Opciones y Diseño en la Cinta de opciones, a través de las que podemos realizar acciones sobre ella.

Excel Experto 2007

83

Por ejemplo, ya hemos dicho que el botón LISTA DE CAMPO permite mostrar u ocultar este panel, desde el que podrías añadir o quitar campos de la tabla dinámica. Desde los controles del grupo de comandos Ordenar puedes ordenar la información mostrada en la tabla dinámica, según el campo que tengas seleccionado. Desde las opciones del menú del botón puedes realizar ciertas acciones sobre el campo seleccionado en la tabla. Con las opciones Contraer todo el campo o Expandir todo el campo podrás ocultar las filas de detalle del campo activo o mostrarlas, respectivamente. De esta forma puedes ocultar y mostrar ciertos elementos en cada momento, para observar los que te interesen con mayor claridad.

Para ocultar o mostrar filas de detalle de un determinado elemento puedes hacer doble clic en dicho elemento o, lo que es lo mismo, utilizar los botones y que aparecen a la izquierda del elemento de que se trate. Desde el cuadro de diálogo que aparece al seleccionar Configuración de campo, en el menú del botón CAMPO ACTIVO, puedes establecer algunas configuraciones de formato, diseño, impresión y subtotales para el campo de la tabla dinámica.

Excel Experto 2007

84

Por ejemplo, desde la sección Subtotales puedes indicar si quieres mostrar algún subtotal en el campo o no, pudiendo incluso seleccionar la función o funciones de subtotal que quieres mostrar. En cuanto a la ficha diseño e impresión si muestras los elementos del campo, en forma de esquema, puedes seleccionar, si quieres mostrar los subtotales en la parte superior y si los elementos del campo siguiente, estarán en la misma columna que el campo actual, Por otra parte, puedes insertar una línea en blanco a continuación de cada elemento del campo y mostrar o no los elementos que no tengan datos. Desde el botón Formato de número puedes cambiar el formato de los números del campo de valores, lo que puedes servir para apreciar mejor los distintos datos. Por otra parte, aparte de configurar las propiedades de cada campo, también existen algunas características de la tabla dinámica en general que se pueden ajustar. Para esto, pulsa el botón TABLA DINAMICA para mostrar su menú y selecciona Opciones.

Excel Experto 2007

85

Como ves, existen muchas opciones de la tabla que se pueden configurar. El nombre de las opciones suele ser bastante explicativo; de todas formas, si necesitas más información, accede a la ayuda de Excel. Por ejemplo, desde la ficha Totales y filtros puedes ocultar la Columna y la fila de los totales generales, que aparecen en la parte derecha y en la parte inferior de la tabla dinámica, respectivamente, a través de las casillas de la sección Totales generales. Entre otras posibilidades, a través de otros comandos disponibles en la ficha Opciones de la Cinta de opciones podemos cambiar los moverla a otra datos de origen de la tabla dinámica ubicación en el libro “MOVER TABLA DINAMICA” Desde la ficha contextual Diseño puedes establecer varias opciones

relativas al diseño de la tabla: mostrar u ocultar los subtotales o los totales generales, ajustar el diseño del informe, poner una línea en blanco después de cada elemento, cambiar el estilo visual de la tabla, etc.

Otra cosa que puedes hacer en una tabla dinámica es comprobar los datos a partir de los cuales se ha calculado una celda en concreto del área de datos. Para ello, haz doble clic sobre la celda. Excel presentará los datos fuente usados para calcular la celda en una nueva hoja, justo antes de la hoja actual. Ten en cuenta que los datos que aparecen en esta nueva hoja son una copia de los datos originales; si cambias cualquier cosa aquí, esta modificación no se verá reflejada en los datos originales ni en la tabla Dinámica.

Excel Experto 2007

86

42. ACTUALIZACIÓN Y GRÁFICOS DINÁMICOS

Si se modifican los datos a partir de los cuales se formó la tabla, se puede actualizar la tabla sin tener que volver a crearla Para actualizar la tabla, utiliza el botón Actualizar del grupo de comandos Datos de la ficha contextual Opciones. Los cambios que se pueden realizar son:

Cambios en los datos de un campo de datos en la tabla de datos fuente.

Campos o elementos eliminados de la tabla de datos fuente.

Elementos nuevos o modificados en las filas o campos actuales de la tabla fuente. Cuando se actualiza la tabla, Excel sólo mira los datos originales que se utilizaron en la creación de la tabla. Si se han añadido nuevas filas en los datos iníciales, habrá que indicar el nuevo rango desde CAMBIAR ORIGEN DE DATOS

Excel Experto 2007

87

En la ficha Datos del cuadro de las Opciones de tabla dinámica existe la casilla Actualizar al abrir el archivo, que hace que Excel actualice la tabla dinámica automáticamente cada vez que se abre en Libro en el que se encuentra. Otro asunto importante es la posibilidad de crear gráficos dinámicos. Es la misma idea que se tiene a la hora de crear tablas dinámicas, pero con un gráfico. Puedes crear un gráfico dinámico a partir de un rango de datos desde la opción Gráfico dinámico del menú del botón TABLA DINAMICA de la ficha Insertar. Otra posibilidad es crear el gráfico partiendo de una tabla Dinámica ya existente, a través del botón GRAFICO DINAMICO del grupo de comandos Herramientas de la ficha contextual Opciones. Tras seleccionar el tipo de gráfico, se creará dicho gráfico dinámico; es dinámico porque puedes cambiar la forma de presentar la información. Si expandes o contraes elementos en la tabla dinámica de la que procede el gráfico, también se expandirá o contraerá en el gráfico.

Excel Experto 2007

88

Puedes mostrar este tipo de gráficos dinámicos como gráficos incrustados o en una hoja de gráfico independiente. En este tipo de gráficos, lo primero que te puede llamar la atención es el Panel de filtros del gráfico dinámico. Puedes ver este panel acoplado en una parte de la ventana de Excel o flotante, pulsando y arrastrando en su título y colocándolo en la posición adecuada. Este panel sirve para filtrar la información que se muestra en el gráfico, permitiendo elegir los elementos que quieres ver en cada momento. El botón Lista de campo del Panel de filtros permite mostrar u ocultar el panel de Lista de campos de tabla dinámica, desde el que puedes añadir, eliminar o cambiar el área en el que están situados los Distintos campos del gráfico. Gran parte de las posibilidades que hay a la hora de trabajar con gráficos también están disponibles cuando creas un gráfico dinámico. Podrás cambiar el tipo de gráfico, mostrar u ocultar distintos elementos, establecer distintas opciones de formato, cambiar la ubicación del gráfico, etc. De la misma forma que en los gráficos dinámicos, también puedes utilizar las listas desplegables de los campos para mostrar sólo la información que selecciones. Los campos en los que hay aplicados filtros se indican con un icono especial en el botón que sirve para desplegar su lista, distinto al icono normal. Al crear un gráfico dinámico desde un rango de datos también se crea la tabla dinámica asociada al mismo. De esta forma, estos dos elementos (tabla y gráfico dinámicos) están enlazados, de forma que los filtros aplicados en una se tienen en cuenta también en el otro.

43. CONSOLIDACIÓN DE DATOS

También podemos resumir datos que tenemos en distintas hojas de cálculo, con distribuciones similares y con rótulos de fila iguales, consolidando la información en una hoja maestra. Por ejemplo, podemos tener en un libro los datos para dos zonas de venta (Levante y Madrid) de una misma empresa, referidos a los años 2006 y 2007, distribuidos en cuatro hojas de cálculo. Fíjate cómo los rótulos de fila y de columna de las distintas hojas son iguales. Ahora nos interesa consolidar ambas zonas de venta, para obtener los datos globales de la empresa.

Excel Experto 2007

89

Excel Experto 2007

90

Lo primero que tienes que hacer es colocarte en la celda a partir de la cual se colocarán los datos consolidados y, a continuación, pulsar el botón CONSOLIDAR de la ficha Datos de la Cinta de opciones.

En la lista Función podrás seleccionar la función de resumen, que es el cálculo que realizaremos en

los subtotales al consolidar los datos. En la sección Usar rótulos en debes indicar dónde se encuentran los rótulos o etiquetas en los rangos de origen, que puede ser en la fila superior, en la columna izquierda o en

estos dos sitios. Al aceptar el cuadro de diálogo, se consolidarán todos datos seleccionados, con lo que logramos juntar y resumir toda la información, de forma que puede resultar más sencillo analizar los datos y obtener conclusiones a partir de ellos.

Excel Experto 2007

91

Análisis de Datos y Macros 44. BÚSQUEDA DE OBJETIVOS

En ocasiones, cuando trabajamos con fórmulas, conocemos el resultado que queremos alcanzar, pero desconocemos uno de los datos de la fórmula que permiten obtener el valor deseado. Excel proporciona un método para buscar un valor concreto que cumpla una determinada fórmula: el comando Buscar objetivo. Veamos un ejemplo: en este caso, tenemos una hoja de cálculo que simula un préstamo. Así, vemos que tenemos el importe del préstamo (celda B1) y el interés anual que nos cobra el banco (es fijo, y está indicado en la celda B2).

Sabiendo estos datos, podemos calcular la mensualidad que nos sale a pagar, respecto al número de pagos que hay que realizar (meses en los que se amortiza el préstamo, que figuran en la celda B3). Para esto, utilizaremos la función PAGO en la celda B5. Fíjate en los argumentos de la función: la Tasa es el interés del préstamo; como, en este caso, en la hoja tenemos el interés anual, lo dividiremos entre 12 para obtener el interés mensual. El segundo argumento indica el número total de pagos del préstamo (en este caso, el plazo en meses), mientras que el tercero contiene la cantidad del préstamo. En este último argumento, indicamos la cantidad con el signo negativo, para que el resultado de la función

tenga signo positivo. Si no pusiéramos la cantidad con el signo negativo, el resultado de la función Sería negativo, porque indicaría el dinero que pagaríamos. Con los valores actuales de las celdas B1..B3, el importe a pagar es casi 287 unidades monetarias al mes, como puedes ver en la celda B5. Imagínate ahora que sólo puedes pagar 170 u.m. al mes. Teniendo

Excel Experto 2007

92

En cuenta esto, ¿cuántos meses necesitarías para amortizar el préstamo, manteniendo el importe y el tipo de interés? Para encontrar el valor que resuelva una fórmula (es decir, que esta fórmula dé como resultado el valor que indiquemos), debes ponerte en la celda que contiene dicha fórmula y seleccionar la orden Buscar objetivo, que se encuentra en el menú del botón “ ANALISIS Y” , en la Ficha Datos de la Cinta de opciones. En el campo Definir la celda del cuadro de diálogo que aparecerá estará la referencia a la celda que tiene la fórmula. Al aceptar el cuadro, Excel buscará el objetivo y, al terminar, se mostrará otro cuadro indicándote si se ha encontrado alguna solución. Si el resultado es satisfactorio y aceptas el cuadro, las celdas se actualizarán con los nuevos valores; si se cancela, las celdas quedarán con los valores anteriores. En nuestro caso, vemos que necesitaríamos un poco más de 134 meses para amortizar el préstamo si sólo queremos pagar 170 u.m. al mes. Si aceptas la solución de la búsqueda y después cambias de opinión, puedes deshacer la acción con el botón Deshacer de la barra de herramientas de acceso rápido.

Excel Experto 2007

93

45. TABLAS DE DATOS

O tras posibilidad que proporciona Excel es la utilización de una tabla de datos para calcular distintos resultados en una operación. Una tabla de datos es un rango de celdas en las que podemos Ver cómo cambia los resultados de una fórmula según los valores de algunos de sus argumentos. Con las tablas de datos podemos ver y comparar cómo influyen uno o varios argumentos sobre el resultado de una fórmula. Existen dos tipos de tablas:

Tabla de una variable: utilizada cuando se quiere comprobar cómo afecta un valor a una o varias fórmulas.

Tabla de dos variables: cuando se quiere comprobar cómo afectan dos valores a una fórmula. Para utilizar una tabla de datos, selecciona el rango de celdas que contenga la fórmula y las variables a utilizar y, después, utiliza el comando Tabla de datos del botón “ANALISIS Y”. Por ejemplo, vamos a ver cómo variaría la cantidad que pagaríamos mensualmente en el préstamo con diferentes tipos de interés. Hemos colocado los diferentes tipos de interés en las celdas A6..A8. La fórmula que vamos a utilizar la tenemos que tener situada una fila por encima y una celda a la derecha (en este caso, la tenemos en la celda B5).

Entonces, para utilizar la tabla de datos, primero deberíamos seleccionar el rango de celdas A5..B8 y luego acceder al comando Tabla de datos. Como, en este caso, las variables las hemos introducido en una columna (celdas A6..A8), tenemos que introducir la celda variable en el campo Celda de entrada (columna) del cuadro de diálogo que se mostrará; si las variables estuvieran en una fila, deberías utilizar el otro campo. Esta celda variable indicará la celda de la fórmula que utilizaremos para ir sustituyendo su valor por los valores de la tabla de datos.

Excel Experto 2007

94

Al aceptar el cuadro, Excel te presentará en la hoja de cálculo los resultados para cada uno De los valores introducidos para la variable indicada (en este caso, para la variable Tipo de interés: 15%, 11% y 9%). También es posible añadir a una tabla más de una fórmula. Al utilizar la orden Tabla de datos, los valores de la tabla se sustituirán en la celda a variar y se realizarán los cálculos para cada una de las fórmulas.

46. ESCENARIOS Un escenario es un grupo de valores para celdas, denominadas celdas cambiantes, que se guarda con un nombre. Trabajar con escenarios es otra forma de obtener resultados cambiantes en función de los datos presentes en las celdas. Para trabajar con los diferentes escenarios, accede al Administrador de escenarios, a través de la opción correspondiente en el menú del botón “ANALISIS Y “el cuadro del Administrador de escenarios se indican las celdas cambiantes de la hoja. Dispones de los siguientes botones: * Mostrar: muestra el escenario seleccionado, es decir, que se presentarán los valores de las celdas en la hoja de cálculo, recalculando las fórmulas para los nuevos valores.

Excel Experto 2007

95

* Cerrar: cierra el cuadro de diálogo. * Agregar: crea un escenario nuevo. * Eliminar: elimina el escenario seleccionado de la lista de escenarios. Ten cuidado con esta opción, ya que el proceso de borrar un escenario no se puede deshacer ni cancelar.

* Modificar: edita un escenario existente para cambiarlo, es decir, para cambiar su nombre, las celdas cambiantes y los valores Que tienen asignados cada una de estas celdas cambiantes. * Combinar: te permite combinar o unir los escenarios de otras hojas de cálculo en la hoja actual. Incluso puedes combinar los Escenarios de varias hojas que estén en distintos libros; para ello, tendrás que abrir antes todos los libros que tengan los Escenarios que quieras combinar. * Resumen: crea un resumen de todos los escenarios existentes en una hoja para así poder comparar los resultados. En este caso, en el campo Celdas De resultado debes indicar. La referencia a la celda o celdas cuyos

valores cambian dependiendo del escenario seleccionado, aquí puedes separar las referencias múltiples mediante el punto y la coma. (;) Al crear un escenario nuevo a través del botón Agregar, tendrás que introducir el nombre del escenario y el rango de las celdas cambiantes. Para indicar varias celdas cambiantes o varios rangos del cedas, sepáralas mediante el signo de punto y coma ( ; ). Luego tendrás que introducir los valores que debe tener cada una De las celdas cambiantes en el escenario que estés creando.

Excel Experto 2007

96

47. SOLVER La herramienta Solver se utiliza para resolver problemas complejos, creando un modelo de hoja de cálculo con múltiples celdas cambiantes. Solver nos ayudará en hojas y en relaciones más complicadas que las que hemos visto hasta ahora. Utilizaremos Solver cuando queramos calcular el valor óptimo (es decir, el máximo, mínimo o un valor que especifiquemos) para una celda, ajustando el valor de las otras celdas que influyan en ésta. Solver no se instala inicialmente con Excel, a no ser que se lo indiques expresamente. Para ello, accede al cuadro de Opciones de Excel a través del Botón de Office y sitúate en la categoría Complementos.

Excel Experto 2007

97

A continuación, selecciona la opción Complementos de Excel en la lista inferior, pulsa el botón Ir y activa la casilla correspondiente del cuadro Complementos. Al aceptar el cuadro, y si es necesario, se abrirá el programa de instalación de Office para instalar esta herramienta. Al activar el activar el complemento Solver, aparece el grupo de comandos Análisis en la ficha Datos de la Cinta de opciones, en el que puedes ver el comando SOLVER. Para resolver un problema con esta herramienta, deberás definir la celda objetivo, las celdas cambiantes y las restricciones:

La Celda objetivo es aquella cuyo valor queremos maximizar, minimizar o establecer a un cierto valor.

Excel Experto 2007

98

En el campo Cambiando las celdas se indican aquellas celdas cuyos valores se pueden cambiar para obtener el objetivo deseado, es decir, las que utilizará Solver para encontrar el valor deseado en la celda objetivo. Puedes especificar varias celdas o rangos de celdas no adyacentes, separando cada uno de ellos con puntos y comas ( ; ).

Las restricciones son limitaciones o condiciones que se aplican sobre los valores que pueden tomar las celdas; por ejemplo, que queden dentro de unos límites establecidos. La celda objetivo debe contener una fórmula, y dependerá directa o indirectamente de las celdas cambiantes. Para indicar las restricciones, pulsa el botón Agregar del apartado De las restricciones del cuadro de Solver. Se abrirá otro cuadro en el que podrás introducir la celda y el tipo de restricción.

Una vez introducidos todos los detalles del modelo, debemos indicar a Excel que lo resuelva y que nos muestre el resultado, pulsando en el botón Resolver. El proceso consiste en sucesivas pruebas o iteraciones. En cada una de ellas, Solver utiliza un nuevo conjunto de valores en las celdas cambiantes para volver a calcular la hoja y examina las restricciones y los valores de celda óptimos. El proceso se detiene cuando se encuentra una solución satisfactoria, cuando ya no es posible avanzar más o cuando alcanza el tiempo máximo permitido o el número máximo de iteraciones (estas características se pueden cambiar a través del botón Opciones del cuadro de Solver). Todas estas herramientas de análisis de datos que hemos visto (búsqueda de objetivos, tablas de datos, escenarios y Solver) se denominan en Excel herramientas de análisis Y si o análisis qué si: cambiar los valores de unas celdas para ver cómo afectan estos cambios al resultado de ciertas fórmulas de la hoja.

Excel Experto 2007

99

48. MACROS Las macros son funciones que permiten la ejecución de distintos comandos de forma consecutiva y automática. Utilizarás las macros cuando tengas que hacer un mismo proceso una y otra vez y quieras hacerlo de forma rápida. La ficha de la Cinta de opciones que permite crear y trabajar con macros, además de otras opciones, es Programador.

Para grabar una macro, pulsa el Botón GRAGAR MACRO Aparecerá un cuadro de diálogo donde tendrás que darle un nombre a la macro y asignarle una descripción, así como indicar bajo qué combinación de teclas se ejecutará.

En la lista Guardar macro en podrás indicar el lugar donde se guardará la macro, que determinará dónde puede utilizarse. Si la guardas en el libro actual (opción Este libro), sólo estará disponible en Dicho libro, mientras que, si la guardas en el Libro de macros personal, estará disponible siempre que trabajes en Excel, en cualquier libro de trabajo, sin importar dónde fue creada. Al aceptar el cuadro de diálogo, podrás realizar todas las acciones necesarias que constituyan la macro, para que se vayan grabando en ella. El Botón GRABAR MACRO de la cinta de

opciones, se convierte en DETENER GRABACION que permite finalizar la grabación de la macro, indica si las referencias que van El botón usar “referencias relativas” Para ello, accede a la lista de macros desde el botón “macros” A guardarse en la macro deben hacerse como relativas (botón pulsado activado) o como absolutas (sin pulsar el botón). Una vez creada la macro, cuando necesites utilizarla, podrás

Excel Experto 2007

100

Ejecutarla. En el cuadro de diálogo que se Muestra al pulsar MACROS aparecerán las Macros que tienes disponibles. Ahora Simplemente debes elegir una y pulsar en El botón Ejecutar. Al abrir un libro que tiene macros, Excel te indica esta circunstancia, permitiéndote habilitar o deshabilitarlas, por si no quieres ejecutar Macros que no estés seguro de su procedencia. Para finalizar, indicarte que las macros son pequeños programas que se escriben en un lenguaje común a todas las aplicaciones de Office, el Visual Basic para Aplicaciones. Aunque este lenguaje de programación no es muy difícil, deberás aprender a programar en el mismo si quieres realizar macros complejas o modificar las ya existentes. Utilizando este lenguaje, las posibilidades de las macros se amplían muchísimo.

Excel Experto 2007

101

Plantillas y Vistas 49. QUÉ SON LAS PLANTILLAS

Vamos a ver cómo utilizar las plantillas en los documentos de Excel. Las plantillas son elementos de Excel que se utilizan como base para crear libros parecidos. En una plantilla se guardan textos, formatos de celdas, estilos, gráficos, fórmulas, macros, etc. Entonces, al crear un nuevo libro basado en esta plantilla, este libro tendrá todas estas características de la plantilla, lo que nos servirá para ahorrarnos trabajo, al no tener que crear de nuevo todos estos elementos.

Excel Experto 2007

102

Desde el cuadro de diálogo Nuevo libro (que se muestra al acceder a la opción Nuevo del Botón de Office) tienes varias opciones para crear un libro: puedes crearlo en blanco, a partir de otro libro existente o utilizando una plantilla, que es lo que veremos ahora.

Puedes utilizar las plantillas instaladas en tu ordenador por Office, tus propias plantillas o las que te ofrece Microsoft a través de Internet, en su sitio web Microsoft Office Online. Si pulsas en Plantillas instaladas, dentro del apartado Plantillas, podrás ver las plantillas instaladas en tu ordenador. Al seleccionar una plantilla podrás ver una vista previa de la misma en la parte derecha del cuadro, para que te hagas una idea del aspecto que tendrá el libro.

Excel Experto 2007

103

Tras seleccionar la plantilla adecuada, pulsa el botón Crear para crear un nuevo libro utilizando esa plantilla. De esta forma, el libro tendrá los textos, formatos, colores, fórmulas, etc., de dicha plantilla, con lo que sólo te quedaría introducir los datos en las celdas adecuadas. Indicarte también que, al igual que puedes crear nuevos libros basados en plantillas, también puedes crear nuevas hojas en un libro basadas en una plantilla. Para ello, muestra el menú contextual de una hoja de cálculo y elige la opción Insertar. Desde el cuadro de diálogo que aparece podemos seleccionar la plantilla de la nueva hoja que insertaremos en este libro. En la ficha General aparecen varios tipos de hojas estándar, mientras que en la ficha Soluciones de hoja de cálculo están las plantillas predefinidas por Excel. Aunque basemos el documento en una plantilla, al guardarlo le tenemos que asignar un nombre. La plantilla no se modifica al crear, modificar y guardar documentos desde ella, permaneciendo igual. Para modificar una plantilla, deberemos abrir el archivo de dicha plantilla explícitamente y cambiar lo que queramos. Indicarte también, para acabar el capítulo, que el libro que aparece al abrir Excel sigue la plantilla predeterminada. Si creas un libro con la combinación de teclas CTRL + U o con el botón Nuevo (que puedes añadir a la barra de herramientas de acceso rápido), también se utilizará esta plantilla predeterminada. En este caso, como ya sabrás, esta plantilla no tiene ningún texto ni ningún formato en sus celdas. La plantilla predeterminada de Excel se denomina Libro.xltx, y está almacenada en la carpeta XLSTART, donde tienes instalado Excel (normalmente, esta carpeta se encuentra en la ruta C:\Archivos de programa\Microsoft Office\Office12\XLSTART). Por lo tanto, si quieres cambiar esta plantilla predeterminada, de forma que, al crear nuevos libros o al abrir Excel, el libro que aparezca tenga las características indicadas en dicha plantilla, tendrás que crear la plantilla y guardarla en este directorio con este nombre. Por otra parte, la plantilla de hoja de cálculo predeterminada, Que se utiliza al crear nuevas hojas de cálculo, se llama Hoja.xltx y también está en dicha carpeta. Si quieres restaurar las plantillas de libro y de hoja que tiene por defecto Excel, puedes eliminar los archivos Libro.xltx y Hoja.xltx que se encuentren en este directorio XLSTART.

50. CREAR PLANTILLAS

Aparte de utilizar las plantillas que proporciona Excel o las que están disponibles en Office Online, también puedes crear tus propias plantillas. De esta forma, podrías crear plantillas personalizadas para crear de una forma rápida documentos comunes que suelas utilizar: facturas, informes, fichas de artículos, etc. Por ejemplo, podrías crear una plantilla para un informe que suelas hacer mensualmente. Así, cada mes, simplemente tendrías que crear un nuevo libro basado en esa plantilla, con lo que ya tendrías los títulos, formatos, colores, fórmulas, etc., y sólo te quedaría rellenar los campos correspondientes a dicho mes. Para crear una plantilla, introduce en un libro los textos, formatos, fórmulas, funciones y demás elementos que quieras que tenga dicha plantilla. A continuación, guarda el documento, pero indicando, en la lista Tipo del cuadro Guardar como, la opción Plantilla de Excel (*.xltx), para que Excel guarde el archivo como una plantilla. Fíjate que la extensión de los ficheros de plantillas de Excel es xltx. Si te fijas en el campo superior del cuadro, puedes ver que, al indicar que queremos guardar el documento como una plantilla, Excel Asigna la ruta adecuada. También puedes ver esta ruta reflejada en la sección Carpetas, en la parte izquierda del cuadro. Las plantillas personalizadas se guardarán en la carpeta Plantillas de Excel para el usuario de Windows. Si utilizas Windows Vista, esta carpeta se encuentra en C:\Users\nombre_de_usuario\AppData\ Roaming\Microsoft\Plantillas, mientras que, si usas Windows XP, está en C:\Documents and Settings\nombre_de_usuario\Datos de programa\Microsoft\Plantillas.

Excel Experto 2007

104

Si quieres copiar las plantillas a otro ordenador o a otra persona o eliminar una plantilla de las plantillas personalizadas, tienes que hacerlo desde esta carpeta. Para crear documentos basados en las plantillas que hayas creado, accede al cuadro de diálogo de creación de un nuevo libro y selecciona la opción Mis plantillas, que está en el apartado Plantillas. En el cuadro que aparecerá entonces podrás ver las plantillas que hayas creado; selecciona la plantilla adecuada y acepta. En la plantilla también se guardan los márgenes, el encabezado y pie de página, etc., por lo que si en la plantilla estableces estas características, los documentos que crees a partir de ella también las tendrán definidas. Esto lo puedes utilizar para que se impriman de la misma forma todos los documentos que crees a partir de una plantilla.

51. VISTAS PERSONALIZADAS

E n Excel puedes guardar varias vistas dentro de un mismo documento. Una vista es una forma de mostrar el libro; es decir, que si nos ponemos en una celda en concreto de una hoja y creamos una vista, siempre que volvamos a dicha vista, volveremos a ver el libro tal como lo teníamos al crear la vista (nos pondremos en la hoja y en la celda en la que nos encontrábamos). De esta forma, puedes tener varias vistas guardadas de un mismo libro, que te permiten mostrar en cada una hojas distintas, con distintos niveles de zoom, en distintas posiciones, etc., y acceder a ellas rápidamente.

Excel Experto 2007

105

Para crear una nueva vista, pulsa Agregar e indica el nombre de la misma. Establece el nombre adecuado, de forma que sepas a qué se refiere dicha vista y puedas identificarla claramente. En el apartado Incluir en la vista puedes indicar las opciones que quieres que se almacenen en la vista que estás creando. Después de crear la vista, puedes volver a ver el libro de esa forma siempre que quieras. Para esto, accede al cuadro de las vistas personalizadas, selecciona la vista y pulsa el botón Mostrar. Ten en cuenta que, al volver a esta vista, simplemente vuelves a ver el libro tal como lo veías al crear la vista, no se deshacen los cambios que hayas hecho en esta u otras hojas. Además, en una vista personalizada también puedes almacenar la configuración de impresión, de forma que puedes tener varias formas de imprimir una misma hoja en distintas vistas y seleccionar una u otra según quieras imprimir una cosa u otra.

52. ZOOM Y OTRAS VISTAS

Vamos a ver algunas opciones más relativas al tema de las vistas y los distintos modos de ver un libro, para acabar con este aspecto. Ya sabes que, desde los controles de zoom puedes acercar o alejar la vista de la hoja de cálculo, para ver más detalles o para ver una mayor parte de la hoja. Para establecer el factor de zoom o de ampliación que quieras, puedes utilizar los botones Alejar y Acercar o arrastrar el control de la barra del zoom hacia la izquierda o hacia la derecha. Los controles del grupo de comandos Zoom de la ficha Vista de la Cinta de opciones también sirven para establecer el nivel de ampliación de la hoja.

Por otro lado, ya hemos trabajado con la Vista previa de salto de página, que permite ver y cambiar la situación de los saltos de página en el documento impreso. Para acceder a esta vista puedes pulsar el botón Vista previa de salto de página, en la parte inferior derecha de la barra de estado, en la ficha Vista de la Cinta o el botón VISTA PREVIA DE SALTO DE PAGINA de la cinta de opciones. Una vista relacionada con esta es la vista Diseño de página, que permite cambiar e introducir datos del mismo modo que en la vista Normal, y además cambiar los encabezados y pies de página, establecer los márgenes, etc.; es decir, aspectos relacionados con la configuración de la impresión de la hoja. Para ponerte en esta vista, haz clic en DISENO DE PAGINA , en el grupo de comandos Vistas de libro de la Cinta de opciones Las reglas horizontal y vertical que aparecen en la parte superior e izquierda de la ventana, respectivamente, te pueden ayudar

Excel Experto 2007

106

a colocar los objetos en la hoja (gráficos, dibujos, imágenes, etc.). También se utilizan para cambiar los márgenes de la página, pulsando y arrastrando en sus extremos. Para añadir o cambiar los encabezados o pies de página, simplemente pulsa en el lugar adecuado (es decir, en la parte izquierda, central o derecha de la zona superior o inferior de la página) y escribe el texto que quieras. Al igual que en la vista Normal, puedes introducir datos o editar las celdas, modificando su contenido o su formato.

53. PROPIEDADES DEL LIBRO

Los archivos de documentos de Excel tienen unas propiedades, que puedes utilizar para identificar más claramente un documento. Además, las propiedades también te pueden ayudar a buscar documentos. Por ejemplo, puedes buscar todos los libros que haya creado una determinada persona, que traten de un tema en concreto, etc. Para acceder a las propiedades del libro actual, pulsa el Botón de Office y selecciona Preparar – Propiedades. Desde el Panel de información del documento que aparece puedes introducir las propiedades relativas al libro: un título y un asunto que definan el tema del libro, quién lo ha creado, el estado o etapa en la que se encuentra el documento (por ejemplo, borrador, beta, por revisar o final), etc. Al pulsar el botón Propiedades del documento, en la parte superior izquierda del Panel de información del documento, y seleccionar la opción Propiedades avanzadas, aparecerá el cuadro de diálogo de las propiedades del archivo (al que también puedes acceder desde el Explorador de archivos de Windows, a través del menú contextual del archivo), que muestra toda la información y propiedades del documento.

Excel Experto 2007

107

Además de las propiedades básicas que hemos visto antes, desde la ficha Resumen desde aquí puedes introducir a qué organización o empresa pertenece la persona que ha creado el libro o la persona que hace de administrador del documento. La casilla de Guardar miniaturas te permite ver lo que contiene un documento antes de abrirlo, en el cuadro de Abrir. Esto te puede servir especialmente al crear una plantilla, ya que, si activas esta casilla, podrás ver la vista previa al seleccionar la plantilla. En la ficha Estadísticas puedes ver estadísticas acerca del documento, como la fecha en que fue creado o modificado, quién lo ha guardado, etc. En la ficha General también están las fechas de creación y modificación, y además puedes ver el tamaño del archivo en disco y la ruta donde está guardado. Recuerda que también puedes usar estos campos para buscar un archivo. Por ejemplo, podrías buscar los libros que se hubieran creado antes o después de una fecha determinada o los que se hubieran modificado hace dos días. En la ficha Contenido se muestra el contenido del documento, en este caso, las hojas de cálculo de las que consta el libro. En la ficha Personalizar puedes ver o cambiar las propiedades personalizadas. Estas propiedades puedes crearlas tú mismo. Es decir, puedes crear una nueva propiedad y ponerle el nombre que quieras (por ejemplo, Enviado a, que indicaría a quién has enviado el libro), y después utilizar esta propiedad para buscar o clasificar los documentos (por ejemplo, podrías buscar todos los libros que hubieras enviado a una determinada persona). En la ficha Personalizar también hay una lista de nombres de propiedades (Cliente, Departamento, Grupo, Proyecto, etc.), para que puedas seleccionar uno de estos nombres para la propiedad que crees. Si no te gusta ninguno de estos nombres que te propone Excel, recuerda que puedes escribir el nombre que quieras. En estas propiedades personalizadas, además de asignar el nombre, también podrás indicar el tipo de la propiedad, es decir, si la propiedad tendrá un texto, un número, una fecha o será de tipo "Sí/No".

54. FIRMAS DIGITALES

Otra característica que puedes aplicar a los archivos de Excel es firmarlos digitalmente. Una firma digital es una información electrónica segura y cifrada, que sirve para autentificar un documento. De esta forma, garantizas a cualquier persona que abra el libro que dicho libro fue creado por ti y que nadie lo ha modificado. Esto se utiliza para que la gente sepa con total seguridad quién ha creado el libro, de forma que nadie pueda engañarlos y hacerse pasar por otra persona. Para añadir una firma digital a un libro, pulsa el Botón de Office y selecciona Preparar - Agregar una firma digital. Para firmar digitalmente un archivo, lo primero que necesitas es un certificado digital. Existen varias empresas que se dedican a proporcionar certificados digitales, por lo que tendrás que contactar con alguna de ellas si quieres obtener uno. La primera opción se conecta al sitio web de Microsoft Office Online para buscar una empresa que te proporcione este certificado digital. Una vez has obtenido y has instalado en tu ordenador tu certificado digital, ya podrás utilizarlo para firmar digitalmente archivos. Otra posibilidad es crear tu propio certificado o identificador digital, a través de la segunda opción de este cuadro de diálogo. E n este caso se necesita tu nombre, dirección de correo electrónico, organización o empresa para la que trabajas y el lugar o país en el que estás. Ten en cuenta que, si creas tu propio certificado digital, podrás comprobar la autenticidad de esta firma digital sólo en el equipo en el que lo creaste, no en otros ordenadores. Para esto, deberías obtener un certificado digital de una empresa autorizada. Si tienes alguna duda o necesitas más información sobre este tema, puedes consultar la ayuda que tiene disponible Excel. De la misma manera que firmas un documento, también puedes firmar una macro. De esta forma, indicarías que la macro que has creado es segura.

Excel Experto 2007

108

Colaborar con Otros Usuarios 55. PROTECCIÓN DEL TRABAJO

Excel permite ciertos niveles de protección del trabajo que has realizado en un libro. Cuando hablamos de protección, nos estamos refiriendo a impedir que se produzcan cambios no deseados, bien en el libro de trabajo o bien en una determinada hoja de cálculo. Existen dos niveles fundamentales de protección: a nivel de libro de trabajo o a nivel de hoja de cálculo. En el grupo de comandos Cambios de la ficha Revisar de la Cinta de opciones se encuentran los comandos que utilizaremos para proteger el libro o la hoja.

PROTECCIÓN DEL LIBRO: Si pulsas en el botón PROTEGER LIBRO , podrás proteger la estructura del libro o las ventanas, activando la opción u opciones correspondientes. Para que la protección sea efectiva, deberás incluir una contraseña, ya que, en otro caso, un usuario no autorizado podría ejecutar el comando DESPROTEGER LIBRO para poder realizar cualquier cambio en el mismo. Si decides proteger la Estructura del libro, un usuario no autorizado no podrá realizar, entre otras cosas:

Ver las hojas de cálculo que estén ocultas.

Mover, eliminar, ocultar o cambiar de nombre las hojas de cálculo.

Insertar nuevas hojas de cálculo u hojas de gráficos. Sin embargo, sí que podría agregar gráficos incrustados en una hoja existente.

Mover o copiar hojas de cálculo en otro libro.

Utilizar las herramientas para análisis de datos que coloquen los resultados en una hoja nueva. Si la protección se realiza activando la casilla Ventanas, entonces se incluye protección para impedir:

Excel Experto 2007

109

Si decides proteger la Estructura del libro, un usuario no autorizado no podrá realizar, entre otras cosas:

Ver las hojas de cálculo que estén ocultas.

Mover, eliminar, ocultar o cambiar de nombre las hojas de cálculo.

Insertar nuevas hojas de cálculo u hojas de gráficos. Sin embargo, sí que podría agregar gráficos incrustados en una hoja existente.

Mover o copiar hojas de cálculo en otro libro.

Utilizar las herramientas para análisis de datos que coloquen los resultados en una hoja nueva. Si la protección se realiza activando la casilla Ventanas, entonces se incluye protección para impedir:

Mover, cambiar el tamaño o cerrar las ventanas. Sin embargo, sí que podría ocultar o mostrar las ventanas.

PROTECCIÓN DE LA HOJA DE CÁLCULO: Al proteger la hoja de cálculo en sí y no el libro en su totalidad, a través del comando PROTEGER HOJA, incluirás un nivel de protección más concreto del trabajo que has hecho. Al proteger la hoja, podremos indicar qué podrán hacer los usuarios sobre la misma. En cualquier caso, es fundamental introducir una contraseña. Si no lo haces, la protección no tendrá ningún sentido, ya que cualquiera podría desproteger la hoja. Muchas veces querrás proteger ciertas zonas de tu hoja de cálculo y dejar otras que sí puedan ser editadas. Por ejemplo, podemos dejar que los usuarios puedan introducir ciertos datos en determinadas celdas de la hoja pero no cambiar otros detalles. En la imagen que te muestro a continuación, podríamos dejar que los usuarios introduzcan los detalles de los gastos que se van produciendo (celdas B13 a E20), pero evitar que cambien el período de gastos o la columna del importe, cuyo valor podría obtenerse a través de una determinada fórmula. A TRAVES DE LA FICHA PROTEGER DEL CUADRO DE FORMATO DE CELDAS PUEDES DESPROTEGER UNA DETERMINADA ZONA Y MANTENER OTRA PROTEGIDA

Excel Experto 2007

110

Si quieres que una celda no esté bloqueada cuando se proteja la hoja, desactiva la casilla Bloqueada (lógicamente, esta acción no puede hacerse una vez se ha protegido la hoja de cálculo). Esta casilla sólo tiene efecto cuando se ha protegido la hoja de cálculo, en un nivel que afecte a las celdas de la misma. Como la casilla aparece activada por defecto en las celdas, lo que se hace es desactivarla en aquellas celdas donde el usuario podrá introducir los datos necesarios en la hoja protegida. Posteriormente, deberás proteger la hoja de cálculo. La casilla Oculta oculta la fórmula que tenga la celda para que no aparezca en la barra de fórmulas al seleccionar la celda. Igual que la otra opción, esto sólo tiene efecto si la hoja está protegida. Existe otro nivel de protección que ya conoces se refiere a la posibilidad de proteger el archivo para que solo pueda ser abierto, por aquellos usuarios autorizados. Podrías introducir una contraseña de apertura, de forma que cualquier usuario que quiera abrir el archivo deba conocerla. Si quieres que otros usuarios puedan abrir el archivo, pero no modificarlo, utiliza la contraseña de escritura. Puedes establecer estas opción es a través de comando Herramientas- Opciones generales del cuadro de diálogo de Guardar.

Excel Experto 2007

111

56. CREAR UN LIBRO COMPARTIDO

Excel permite que distintos usuarios utilicen al mismo tiempo un libro y que puedan trabajar en la misma hoja de cálculo. Al crear un libro compartido, estás permitiendo que distintos usuarios lo modifiquen al mismo tiempo y que incluso puedan ver los cambios que otros hacen en el mismo.

Por ejemplo, fíjate en la hoja de cálculo que te muestro a continuación. Esta hoja se ha confeccionado para recoger los resultados de una encuesta que se realiza a la salida de los colegios electorales durante unas elecciones municipales. Supón que tenemos un equipo de encuestadores, y cada uno de los miembros de ese equipo se encarga de un determinado colegio electoral. Todos ellos tienen acceso a este libro, de forma que, a medida que vayan recogiendo datos, los tienen que ir reflejando en la hoja de cálculo.

Excel Experto 2007

112

En este caso, es una buena idea el compartir el libro, para tener esta información actualizada en todo momento. Lo primero es preparar el libro para ser compartido. Por ejemplo, si quieres proteger la hoja, deberás hacerlo antes de compartir el libro. También existen otras características, como combinar celdas, aplicar formatos condicionales, crear gráficos, etc., que no estarán disponibles una vez compartido el libro, en la ficha Revisar Para compartir el libro, pulsa en el botón COMPARTIR LIBRO de la Cinta de opciones, y activa la casilla Permitir la modificación Por varios usuarios a la vez del cuadro de diálogo que aparecerá.

Excel Experto 2007

113

Por ejemplo, podremos guardar un historial con los cambios que vayan realizándose. Esto puede venir muy bien cuando surjan conflictos, es decir, cuando dos usuarios modifiquen una misma celda De la hoja de cálculo. También podremos indicar cada cuanto tiempo se actualizarán los Cambios que vaya sufriendo el libro (por parte de todos los usuarios). De esta forma, podríamos comprobar y actualizar los cambios cada vez que se guarde el libro o a intervalos regulares. Finalmente, indicaremos cuál debe ser el comportamiento a seguir por Excel cuando encuentre cambios conflictivos. Lo mejor suele ser permitir que sea el usuario quien decida qué hacer. Una vez aceptado el cuadro, Excel te indicará que guardará el libro. A partir de ese momento, en la barra de título aparecerá la palabra [Compartido], para indicarnos esta circunstancia.

El siguiente paso sería situar este libro en una carpeta compartida de la red, es decir, en un lugar al que puedan acceder los distintos usuarios que van a modificarlo. A partir de entonces, los usuarios podrán acceder al libro y modificarlo, para incluir los datos necesarios.

57. TRABAJAR EN UN LIBRO COMPARTIDO

A medida que se trabaja en el libro compartido y, dependiendo de cómo lo hayas configurado, irás viendo los cambios que realiza el resto de usuarios que lo tienen abierto. Por defecto, estos cambios se muestran cada vez que guardas el libro. En ese momento, Excel comprueba si otro usuario ha guardado anteriormente el libro y ha realizado cambios. En este caso, Excel mostrará las modificaciones de los otros usuarios en la hoja de cálculo y te indicará dicha circunstancia.

Si no hay ningún conflicto entre las celdas que hayan modificado los otros usuarios del libro y los cambios que hayas hecho tú en la hoja, la actualización se lleva a cabo sin ningún problema. Pero también es posible que dos usuarios modifiquen las mismas celdas de la hoja de cálculo. En este caso, se produce un conflicto en el que Excel necesita saber qué datos son los que finalmente prevalecerán.

Excel Experto 2007

114

Esto no debería ser muy frecuente, ya que cada usuario del libro debe saber qué rangos de celdas puede modificar. Cuando ocurre un conflicto, debes decidir qué hacer: mantener los datos que habías escrito tú o los datos de los otros usuarios del libro. Para ello, utiliza los botones del cuadro De diálogo. Ten en cuenta que cualquier usuario del libro tiene la oportunidad de decidir mantener sus datos o aceptar los que ha introducido otro. Esta decisión la tomará el último que guarde el libro y haya modificado celdas que entren en conflicto con otros usuarios.

58. EL HISTORIAL DE CAMBIOS

En cualquier momento puedes resaltar los cambios que se han producido en la hoja de cálculo e incluso acceder al historial de dichos cambios. De esta forma, conoceremos quién modificó una Determinada celda y qué valor tenía antes de hacerlo. Y selecciona la Para esto, pulsa el botón HISTORIAL DE CAMBIOS opción Resaltar cambios.

Excel Experto 2007

115

En Excel también puedes enviar un documento a un compañero a través de Internet, utilizando el correo electrónico. Desde el submenú Enviar del Botón de Office puedes enviar el libro actual adjunto en un mensaje de correo electrónico a la persona o personas que selecciones. Además, si has instalado el complemento o plug-in para crear documentos PDF o XPS desde los archivos de Office, también aparecerán unas opciones que permiten generar un documento de estos tipos y enviarlo adjunto en un mensaje de correo electrónico. Otra posibilidad es enviar un libro a una o varias personas para A continuación, para enviar el libro, pulsa el botón Destinatario de correo (para revisión) de esta barra de herramientas de acceso rápido. En este caso, es interesante compartir el libro, porque, de esta manera, luego podremos combinarlo con los libros modificados que recibamos de las personas a las que se lo mandemos. Excel nos indica esto y nos da la posibilidad de compartir el libro. A continuación, se abrirá el programa para mandar mensajes de correo electrónico, que, por defecto, es Microsoft Outlook, que es parte de Office. En el mensaje a enviar se incluye el archivo a revisar como un adjunto, para que lo reciban la persona o personas a las que lo envíes. En el campo Asunto se suele escribir un pequeño texto que indica de qué trata el mensaje. Excel incluye automáticamente la indicación de que se revise el libro, pero puedes escribir lo que quieras. Para mandar por correo Finalmente, pulsa el botón electrónico el mensaje. Entonces, la persona o personas a las que hemos mandado el libro recibirán el mensaje. Tendrán que abrir el archivo adjunto, modificar o Cuando el usuario al que has enviado el libro lo abra, se Activará el botón Remitente original, desde el que podrá volver a enviarte el libro, una vez haya acabado de modificarlo. Cuando recibamos el libro modificado, podemos guardarlo con otro nombre. Para combinar los libros, de forma que se unan todas la modificaciones realizadas en un libro, debemos utilizar el comando Comparar y combinar libros, que tampoco aparece en la Cinta de opciones, por lo que debes añadirlo a la barra de herramientas de acceso rápido. Al pulsar pulsa en este botón Comparar y combinar libros, podremos seleccionar y abrir el libro revisado o modificado que hemos recibido en el mensaje de correo, con lo que se combinarán los libros, de forma que se unirán todas las modificaciones realizadas. Por otro lado, si dispones de conexión con un servidor web que tenga los servicios Microsoft SharePoint, desde la opción Publicar - Crear área de trabajo de documento del Botón de Office puedes crear una zona virtual o Área de documentos, que es una ubicación compartida donde incluir documentos de Office en los que vayan a trabajar un grupo de usuarios. En el Área de documentos puedes exponer documentos para que acceda una serie de usuarios y los revisen.