Excel 2007 Modulol II

  • Published on
    01-Feb-2016

  • View
    213

  • Download
    0

DESCRIPTION

excel para todos

Transcript

Educacin y Tecnologas de Informacin S.A. Bogot - Colombia 2008 Educacin y Tecnologas de Informacin S.A. IT Talent Abril 2008 Microsoft office 2007 Excel Modulo II Tabla de contenido Excel 2007 Modulo II I. BASES DE DATOS ......................................................................................................................... 4 Obtener informacin sobre la ordenacin .................................................................................. 4 ORDENAR TEXTO ............................................................................................................................. 5 Ordenar nmeros ........................................................................................................................ 5 Ordenar fechas u horas ............................................................................................................... 5 Ordenar por color de celda, color de fuente o icono .................................................................. 6 Ordenar por una lista personalizada ........................................................................................... 7 Ordenar filas ................................................................................................................................ 8 Ordenar por ms de una columna o fila ..................................................................................... 8 Ordenar una columna en un rango de celdas sin afectar a las dems ....................................... 9 Ms informacin sobre problemas generales de ordenacin .................................................. 10 ESQUEMATIZAR UNA LISTA DE DATOS EN UNA HOJA DE CLCULO ............................................. 10 Crear un esquema de filas ......................................................................................................... 11 Crear un esquema de columnas ................................................................................................ 13 Mostrar u ocultar datos de esquemas ...................................................................................... 16 Personalizar un esquema con estilos ........................................................................................ 16 Copiar datos esquematizados ................................................................................................... 17 Ocultar o quitar un esquema .................................................................................................... 17 Crear un informe de resumen con un grfico ........................................................................... 18 CONSOLIDAR DATOS EN VARIAS HOJAS DE CLCULO .................................................................. 18 Consolide por posicin .............................................................................................................. 19 Consolide por categoras ........................................................................................................... 20 Consolide por frmula ............................................................................................................... 21 Filtrar una Base de Datos: ............................................................................................................. 22 Filtros Avanzados: ......................................................................................................................... 23 ANALISIS DE DATOS ....................................................................................................................... 25 Importar datos de Word a Excel. .............................................................................................. 28 Importar datos de Excel a Word. .............................................................................................. 28 Importar Datos de Access: ........................................................................................................ 29 Tablas Dinmicas: .......................................................................................................................... 30 Grficos con Tablas Dinmicas: ................................................................................................. 32 Vincular Hojas de Clculo: ......................................................................................................... 32 Consolidar Hojas de Trabajo: .................................................................................................... 33 E X C E L 2 0 0 7 M O D U L O I I I. BASES DE DATOS Una base de datos o banco de datos es un conjunto de datos que pertenecen al mismo contexto almacenados sistemticamente para su posterior uso. Un libro de Microsoft Office Excel tiene la capacidad de incluir una o varias hojas de clculo para almacenar y trabajar con grandes cantidades de datos. A su vez es un conjunto de datos organizados en filas o registros, en la que la primera fila contiene las cabeceras de las columnas (los nombres de los campos), y las dems filas contienen los datos almacenados. Es como una tabla de base de datos, de hecho tambin se denominan listas de base de datos. Cada fila es un registro de entrada. Las tablas son muy tiles porque adems de almacenar informacin, incluyen una serie de operaciones que permiten analizar y administrar esos datos de forma muy cmoda. Entre las operaciones ms interesantes que podemos realizar con las listas tenemos: - Ordenar los registros. - Filtrar el contenido de la tabla por algn criterio. - Utilizar frmulas para la lista aadiendo algn tipo de filtrado. - Crear un resumen de los datos. - Aplicar formatos a todos los datos. Ordenar datos en un rango o tabla Ordenar los datos es una parte esencial del anlisis de datos. Puede que desee poner una lista de nombres en orden alfabtico, compilar una lista de niveles de inventario de productos de mayor a menor u ordenar filas por colores o por iconos. Ordenar los datos ayuda a verlos y a comprenderlos mejor, as como a organizarlos y encontrarlos ms fcilmente y a tomar decisiones ms eficaces. Nota Para buscar el valor superior o inferior de un rango de celdas o de una tabla, por ejemplo las 10 mejores calificaciones o las 5 peores cifras de ventas, use Autofiltro o el formato condicional. Para obtener ms informacin, vea Filtrar datos de un rango o una tabla y Agregar, cambiar o borrar formatos condicionales. Obtener informacin sobre la ordenacin Puede ordenar datos por texto (A a Z o Z a A), nmeros (de menor a mayor o de mayor a menor) y fechas y horas (de ms antiguos ms recientes y de ms recientes a ms antiguos) en una o varias columnas. Tambin puede ordenar por una lista personalizada (como Grande, Medio y Pequeo) o por formato, incluyendo el color de celda, el color de fuente o el conjunto de iconos. La mayora de las operaciones de ordenacin son ordenaciones de columnas, pero tambin es posible ordenar por filas. Los criterios de ordenacin se guardan en el libro para que pueda volver a aplicarlos cada vez que lo abra para una tabla de Excel, pero no para un rango de celdas. Si desea guardar los criterios de ordenacin para poder volver a aplicar peridicamente una ordenacin al abrir un libro, entonces lo mejor es utilizar una tabla. Esto es especialmente importante para 5 ordenaciones de varias columna o para las que se tarda mucho tiempo en crear. ORDENAR TEXTO 1. Seleccione una columna de datos alfanumricos en un rango de celdas o asegrese de que la celda activa est en una columna de tabla que contiene datos alfanumricos. 2. En la ficha Inicio, en el grupo Modificar, haga clic en Ordenar y filtrar. 3. Siga uno de los procedimientos siguientes: Para ordenar en orden alfanumrico ascendente, haga clic en Ordenar de A a Z. Para ordenar en orden alfanumrico descendente, haga clic en Ordenar de Z a A. 4. Tambin puede ordenar distinguiendo las maysculas de las minsculas. Cmo ordenar distinguiendo las maysculas de las minsculas 1.En la ficha Inicio, en el grupo Modificacin, haga clic en Ordenar y filtrar y, a continuacin, en Personalizar ordenacin. 2.En el cuadro de dilogo Ordenar, haga clic en Opciones. 3.En el cuadro de dilogo Opciones de ordenacin, seleccione Distinguir maysculas de minsculas. 4. Haga clic dos veces en Aceptar. Problema: comprobar si todos los datos estn almacenados como texto Si la columna que desea ordenar contiene nmeros almacenados como nmeros y nmeros almacenados como texto, ser necesario que les aplique formato de texto. Si no lo hace, los nmeros almacenados como nmeros se ordenarn antes que los nmeros almacenados como texto. Para aplicar formato de texto a todos los datos seleccionados, en la ficha Inicio, en el grupo Fuente, haga clic en el botn Formato de fuente de celda, haga clic en la ficha Nmero y, en Categora, haga clic en Texto. Problema: quitar los espacios a la izquierda En algunos casos, los datos que se hayan importado de otra aplicacin pueden tener insertados espacios a la izquierda delante de los mismos. Antes de ordenar los datos, quite estos espacios. Ordenar nmeros 1. Seleccione una columna de datos numricos en un rango de celdas o asegrese de que la celda activa est en una columna de tabla que contiene datos numricos. 2. En la ficha Inicio, en el grupo Modificar, haga clic en Ordenar y filtrar y, a continuacin, siga uno de los procedimientos siguientes: Para ordenar de nmeros ms bajos a nmeros ms altos, haga clic en Ordenar de menor a mayor. Para ordenar de nmeros ms altos a nmeros ms bajos, haga clic en Ordenar de mayor a menor. Problema: comprobar que todos los nmeros estn almacenados como nmeros Si los resultados no le ofrecen lo que esperaba, es posible que la columna contenga nmeros almacenados como texto y no como nmeros. Por ejemplo, los nmeros negativos importados de algunos sistemas de contabilidad o los nmeros con un apstrofo inicial (') se almacenan como texto. Para obtener ms informacin, vea Convertir nmeros almacenados como texto a nmeros. Ordenar fechas u horas 6 1. Seleccione una columna de fechas u horas en un rango de celdas o asegrese de que la celda activa est en una columna de tabla que contiene fechas u horas. 2. Seleccione una columna de fechas u horas en un rango de celdas o en una tabla. 3. En la ficha Inicio, en el grupo Modificar, haga clic en Ordenar y filtrar y, a continuacin, siga uno de los procedimientos siguientes: Para ordenar de una hora o fecha anterior a una posterior, haga clic en Ordenar de ms antiguos a ms recientes. Para ordenar de una hora o fecha posterior a una anterior, haga clic en Ordenar de ms recientes a ms antiguos. Problema: comprobar que las fechas y las horas estn almacenadas como fechas y horas Si los resultados obtenidos no son los que esperaba, es posible que la columna contengan fechas u horas almacenadas como texto y no como fechas u horas. Para que Excel las ordene correctamente, todas las fechas y las horas de una columna deben estar almacenadas como nmeros de serie de fecha u hora. Si Excel no puede reconocer un valor como fecha u hora, significa que la fecha o la hora est almacenada como texto. Para obtener ms informacin, vea Convertir fechas almacenadas como texto en fechas. Nota Si desea ordenar por das de la semana, aplique formato a las celdas para mostrar el da de la semana. Si desea ordenar por el da de la semana independientemente de la fecha que sea, convirtalos en texto utilizando la funcin TEXTO. Sin embargo, la funcin TEXTO devuelve un valor de texto y, por tanto, la operacin de ordenacin estar basada en datos alfanumricos. Para obtener ms informacin, vea Mostrar la fecha como da de la semana. Ordenar por color de celda, color de fuente o icono Si ha aplicado formato manual o condicionalmente a un rango de celdas o a una columna de tabla, por color de celda o color de fuente, tambin puede ordenar por estos colores. Adems, puede ordenar por un conjunto de iconos creado mediante un formato condicional. 1. Seleccione una columna de datos en un rango de celdas o asegrese de que la celda activa est en una columna de tabla. 2. En la ficha Inicio, en el grupo Modificacin, haga clic en Ordenar y filtrar y, a continuacin, en Personalizar ordenacin. Se mostrar el cuadro de dilogo Ordenar. 3. En Columna, en el cuadro Ordenar por, seleccione la columna que desea ordenar. 4. En Ordenar segn, seleccione el tipo de ordenacin. Siga uno de los procedimientos siguientes: Para ordenar por color de celda, seleccione Color de celda. Para ordenar por color de fuente, seleccione Color de fuente. Para ordenar por un conjunto de iconos, seleccione Icono de celda. 5. En Criterio de ordenacin, haga clic en la flecha situada junto al botn y, a continuacin, dependiendo del tipo de formato, seleccione un color de celda, un color de fuente o un icono de celda. 6. En Ordenar, seleccione el modo en que desea ordenar. Siga uno de los procedimientos siguientes: 7 Para mover el color de celda, el color de fuente o el icono a la parte superior o a la izquierda, seleccione En la parte superior para una ordenacin de columnas y A la izquierda para una ordenacin de filas. Para mover el color de celda, el color de fuente o el icono a la parte inferior o a la derecha, seleccione En la parte inferior para una ordenacin de columnas y A la derecha para una ordenacin de filas. Nota No hay ningn color de celda, color de fuente ni criterio de ordenacin de iconos predeterminados. Debe definir el orden que desea para cada operacin de ordenacin. 7. Para especificar el siguiente color de celda, color de fuente o icono por el que desea ordenar, haga clic en Agregar nivel y, a continuacin, repita los pasos del tres al cinco. Asegrese de que selecciona la misma columna en el cuadro Luego por y de que realiza la misma seleccin en Ordenar. Repita el procedimiento por cada color de celda, color de fuente o icono adicionales que desea incluir en la ordenacin. Ordenar por una lista personalizada Puede utilizar una lista personalizada para ordenar en un orden definido por el usuario. Excel ofrece listas personalizadas integradas de das de la semana y de meses del ao, y puede adems crear sus propias listas. 1. Tambin puede crear una lista personalizada. Cmo crear una lista personalizada 1. En un rango de celdas, escriba los valores por los que va a ordenar, en el orden que desee, de arriba abajo. Por ejemplo: A 1 Mximo 2 Medio 3 Mnimo 2. Seleccione el rango que acaba de especificar. En el ejemplo anterior seleccionara las celdas A1:A3. 3. Haga clic en el botn de Microsoft Office , haga clic en Opciones de Excel, en la categora Populares y, a continuacin, en Opciones principales para trabajar con Excel, haga clic en Modificar listas personalizadas. 4. En el cuadro de dilogo Listas personalizadas, haga clic en Importar y, a continuacin, en Aceptar dos veces. Notas Slo puede crear una lista personalizada basada en un valor (texto, nmero, fecha u hora). No puede crear una lista personalizada basada en un formato (color de celda, color de fuente e icono). La longitud mxima para una lista personalizada es de 255 caracteres y el primer carcter puede no empezar por un nmero. Seleccione una columna de datos en un rango de celdas o asegrese de que la celda activa est en una columna de tabla. En la ficha Inicio, en el grupo Modificacin, haga clic en Ordenar y filtrar y, a continuacin, en Personalizar ordenacin. 8 Se mostrar el cuadro de dilogo Ordenar. En Columna, en el cuadro Ordenar por o en Luego por, seleccione la columna que desea ordenar mediante una lista personalizada. En Ordenar, seleccione Lista personalizada. En el cuadro de dilogo Listas personalizadas, seleccione la lista que desee. En el ejemplo anterior, hara clic en Alto, Medio, Bajo. Haga clic en Aceptar. Ordenar filas 1. Seleccione una fila de datos en un rango de celdas o asegrese de que la celda activa est en una columna de tabla. 2. En la ficha Inicio, en el grupo Modificacin, haga clic en Ordenar y filtrar y, a continuacin, en Personalizar ordenacin. Se mostrar el cuadro de dilogo Ordenar. 3. Haga clic en Opciones. 4. En el cuadro de dilogo Opciones de ordenacin, en Orientacin, haga clic en Ordenar de izquierda a derecha y, a continuacin, en Aceptar. 5. En Columna, en el cuadro Ordenar por, seleccione la fila que desea ordenar. 6. Siga uno de los procedimientos siguientes: Por valor 1. En Ordenar segn, seleccione Valores. 2. En Criterio de ordenacin, siga uno de los procedimientos siguientes: Para los valores de texto, seleccione A a Z o Z a A. Para los valores numricos, seleccione De menor a mayor o De mayor a menor. Para los valores de fecha y hora, seleccione De ms antiguos a ms recientes o De ms recientes a ms antiguos. Por color de celda, color de fuente o icono de celda 3. En Ordenar segn, seleccione Color de celda, Color de fuente o Icono de celda. 4. Haga clic en la flecha situada junto al botn y, a continuacin, seleccione un color de celda, un color de fuente o un icono de celda. 5. En Ordenar, seleccione A la izquierda o A la derecha. Nota Al ordenar filas que forman parte de un esquema de hoja de clculo, Excel ordenar los grupos de nivel ms alto (nivel 1) de modo que las filas o las columnas de detalle permanezcan juntas, aunque estn ocultas. Ordenar por ms de una columna o fila Puede ordenar por ms de una columna o fila cuando tiene datos que desea agrupar por el mismo valor en una columna o fila y ordenar despus otra columna o fila dentro de ese grupo de valores iguales. Por ejemplo, si tiene una columna Departamento y empleado, puede ordenar primero por Departamento (para agrupar a todos los empleados del mismo departamento) y, despus, ordenar por nombre (para escribir los nombres en orden alfabtico dentro de cada departamento). Puede ordenar hasta un mximo de 64 columnas. 9 Nota Para obtener resultados ptimos, el rango de celdas que se ordene debe tener encabezados de columna. 1. Seleccione un rango de celdas con dos o ms columnas de datos, o asegrese de que la celda activa se encuentra en una tabla con dos o ms columnas. 2. En la ficha Inicio, en el grupo Modificacin, haga clic en Ordenar y filtrar y, a continuacin, en Personalizar ordenacin. Se mostrar el cuadro de dilogo Ordenar. 3. En Columna, en el cuadro Ordenar por, seleccione la primera columna que desea ordenar. 4. En Ordenar segn, seleccione el tipo de ordenacin. Siga uno de los procedimientos siguientes: Para ordenar por texto, nmero o fecha y hora, seleccione Valores. Para ordenar por formato, seleccione Color de celda, Color de fuente o Icono de celda. 5. En Ordenar, seleccione el modo en que desea ordenar. Siga uno de los procedimientos siguientes: Para los valores de texto, seleccione A a Z o Z a A. Para los valores numricos, seleccione De menor a mayor o De mayor a menor. Para los valores de fecha y hora, seleccione De ms antiguos a ms recientes o De ms recientes a ms antiguos. Para ordenar segn una lista personalizada, seleccione Lista personalizada. 6. Para agregar otra columna por la que ordenar, haga clic en Agregar nivel y, a continuacin, repita los pasos del tres al cinco. 7. Para copiar una columna por la que ordenar, seleccione la entrada y, a continuacin, haga clic en Copiar nivel. 8. Para eliminar una columna por la que ordenar, seleccione la entrada y, a continuacin, haga clic en Eliminar nivel. Nota Debe conservar al menos una entrada en la lista. 9. Para cambiar el orden en el que se ordenan las columnas, seleccione una entrada y, a continuacin, haga clic en la flecha Arriba o Abajo para cambiar el orden. Las entradas ms altas de la lista se ordenan antes que las ms bajas. Ordenar una columna en un rango de celdas sin afectar a las dems Advertencia Tenga cuidado cuando utilice esta caracterstica. Ordenar por una columna en un rango puede producir resultados que no desea, por ejemplo trasladar celdas de dicha columna lejos de las otras celdas de la misma fila. Nota El siguiente procedimiento no se puede realizar en una tabla. 1. Seleccione una columna en rango de celdas que contenga dos o varias columnas. 2. Para seleccionar la columna que desea ordenar, haga clic en el encabezado de columna. 3. En la ficha Inicio, en el grupo Modificar, haga clic en Ordenar y filtrar y, a continuacin, haga clic en uno de los comandos de ordenacin que haya disponibles. 4. Aparecer el cuadro de dilogo Advertencia antes de ordenar. 5. Seleccione Continuar con la seleccin actual. 6. Haga clic en Ordenar. 10 7. Seleccione las dems opciones de ordenacin que desea en el cuadro de dilogo Ordenar y, a continuacin, haga clic en Aceptar. Si el resultado no es el que desea, haga clic en Deshacer . Ms informacin sobre problemas generales de ordenacin Si obtiene resultados inesperados al ordenar sus datos, haga lo siguiente: Comprobar si los valores que devuelve una frmula han cambiado Si los datos que ha ordenado contienen una o ms frmulas, los valores devueltos de dichas frmulas pueden cambiar cuando se actualiza la hoja de clculo. En este caso, asegrese de que vuelve a aplicar la ordenacin para obtener resultados actualizados. Mostrar las filas y las columnas antes de ordenar Las columnas ocultas no se mueven cuando se ordenan columnas, y las filas ocultas no se mueven cuando se ordenan filas. Antes de ordenar los datos, es una buena idea mostrar las columnas o filas ocultas. Comprobar la configuracin local Los criterios de ordenacin varan segn la configuracin local. Asegrese de que la configuracin local de la Configuracin regional o de la Configuracin regional y de idioma del Panel de control de su equipo es correcta. Para obtener informacin sobre cmo cambiar la configuracin local, vea el sistema de Ayuda de Windows. Especificar encabezados de columna solamente en una fila Si tiene que utilizar rtulos de varias lneas, ajuste el texto dentro de la celda. Activar o desactivar la fila de ttulo Normalmente, lo mejor es tener una fila de ttulo cuando se ordena una columna para facilitar la comprensin del significado de los datos. De manera predeterminada, el valor del ttulo no se incluye en la operacin de ordenacin. A veces, es posible que tenga que activar o desactivar el ttulo para que el valor del mismo se incluya o no se incluya en la operacin de ordenacin. Siga uno de los procedimientos siguientes: Para excluir la primera fila de datos de la ordenacin porque se trata de un encabezado de columna, en la ficha Inicio, en el grupo Modificar, haga clic en Ordenar y filtrar, en Orden personalizado y, a continuacin, active Mis datos tienen encabezados. Para incluir la primera fila de datos en la ordenacin porque no es un encabezado de columna, en la ficha Inicio, en el grupo Modificar, haga clic en Ordenar y filtrar, en Orden personalizado y, a continuacin, desactive Mis datos tienen encabezados. ESQUEMATIZAR UNA LISTA DE DATOS EN UNA HOJA DE CLCULO Si tiene una lista (lista: serie de filas que contienen datos relacionados o serie de filas que designa para que funcionen como hojas de datos mediante el comando Crear lista.) de datos que desea agrupar y resumir, puede crear un esquema de hasta ocho niveles, uno para cada grupo. Cada nivel interno, representado por un nmero superior de smbolos de esquema (smbolos de esquema: smbolos que se utilizan para cambiar la vista de una hoja de clculo esquematizada. Se pueden mostrar u ocultar los datos detallados presionando en el signo ms, en el signo menos y en los nmeros 1, 2, 3 o 4 que indican el nivel de esquema.) muestra datos de detalle (datos de detalle: para subtotales automticos y esquemas de hoja de clculo, filas o columnas de subtotal que totalizan los datos de resumen. Los datos de detalle suelen ser adyacentes, por encima o a la izquierda, a los datos de resumen.) para el nivel externo anterior, representado por un nmero inferior de los smbolos de esquema. Utilice un esquema para mostrar filas o columnas de resumen, o para revelar los datos de detalle para cada grupo. Puede crear un esquema de filas (como se muestra en el ejemplo 11 siguiente), un esquema de columnas o un esquema de filas y columnas. Se muestra una fila de datos de ventas agrupados por regiones geogrficas y meses con varias filas de resumen y detalles. Para mostrar filas para un nivel, haga clic en los smbolos de esquema adecuados. El nivel 1 contiene las ventas totales de todas las filas de detalle. El nivel 2 contiene las ventas totales para cada mes en cada regin. El nivel 3 contiene las filas de detalle (slo las filas de detalle 11 a 13 estn actualmente visibles). Para expandir o contraer datos en el esquema, haga clic en los smbolos de esquema y . Crear un esquema de filas 1. Asegrese de que cada columna tiene un rtulo en la primera fila, contiene datos similares en cada columna y el rango no tiene filas ni columnas en blanco. 2. Seleccione una celda del rango. 3. Ordene las columnas que forman los grupos. Para obtener ms informacin sobre la ordenacin, vea Ordenar datos en un rango o tabla. 4. Inserte las filas de resumen. Para trazar esquemas de los datos por filas, debe tener filas de resumen que contengan frmulas que hagan referencias a las celdas de cada una de las filas de detalle para dicho grupo. Siga uno de los procedimientos siguientes: Insertar filas de resumen utilizando el comando Subtotal Utilice el comando Subtotal, que inserta la funcin SUBTOTAL inmediatamente debajo o encima de cada grupo de filas de detalle y crea automticamente el esquema para usted. Para obtener ms informacin, vea Insertar subtotales en una lista de datos de una hoja de clculo. Insertar sus propias filas de resumen Inserte sus propias filas de resumen con las frmulas inmediatamente anterior o posterior a cada grupo de filas de detalle. 5. Especifique si la ubicacin de la fila de resumen est encima o debajo de las filas de detalle. 1.En la ficha Datos, en el grupo Esquema, haga clic en Esquema. 2.Para especificar una fila de resumen encima de la fila de detalles, desactive la casilla de verificacin Filas resumen debajo del detalle. Para especificar una fila de resumen debajo de la fila de detalles, active la casilla de verificacin Filas resumen debajo del detalle. Aplique un esquema a los datos. Siga uno de los procedimientos siguientes: 0.En caso necesario, seleccione una celda del rango. 12 1.En la ficha Datos, en el grupo Esquema, haga clic en la flecha al lado de Agrupar y, a continuacin, haga clic en Autoesquema. Importante Cuando agrupe manualmente niveles de esquema, es mejor tener todos los datos mostrados para evitar agrupar las filas incorrectamente. 2.Aplique un esquema al grupo externo. 1. Seleccione todas las filas de resumen subordinadas, as como sus datos de detalle relacionados. En el ejemplo siguiente, la fila 6 contiene los subtotales para las filas 2 a 5, y la fila 10 contiene los subtotales para las filas 7 a 9, y la fila 11 contiene los totales generales. Para agrupar todos los datos de detalle para la fila 11, seleccione las filas 2 a 10. A B C 1 Regin Mes Ventas 2 Este Marzo 9.647 $ 3 Este Marzo 4.101 $ 4 Este Marzo 7.115 $ 5 Este Marzo 2.957 $ 6 Este Total de marzo 23.820 $ 7 Este Abril 4.257 $ 8 Este Abril 1.829 $ 9 Este Abril 6.550 $ 10 Este Total de abril 12.636 $ 11 Total del este 36.456 $ Importante No incluya la fila de resumen 11 de la seleccin. 2. En la ficha Datos, en el grupo Esquema, haga clic en Agrupar. Los smbolos del esquema aparecen en la pantalla al lado del grupo. 3.De manera opcional, aplique un esquema a un grupo interno anidado. 1. Para grupo interno anidado, seleccione las filas de detalle adyacentes a la fila que contiene la fila de resumen. En el ejemplo siguiente, para agrupar las filas 2 a 5, que tienen una fila resumen 6, seleccione las filas 2 a 5. Para agrupar las filas 7 a 9, que tiene una fila resumen 10, seleccione las filas 7 a 9. A B C 1 Regin Mes Ventas 2 Este Marzo 9.647 $ 3 Este Marzo 4.101 $ 13 4 Este Marzo 7.115 $ 5 Este Marzo 2.957 $ 6 Este Total de marzo 23.820 $ 7 Este Abril 4.257 $ 8 Este Abril 1.829 $ 9 Este Abril 6.550 $ 10 Este Total de abril 12.636 $ 11 Total del este 36.456 $ Importante No incluya en la seleccin la fila resumen para dicho grupo. 2. En la ficha Datos, en el grupo Esquema, haga clic en Agrupar. Los smbolos del esquema aparecen en la pantalla al lado del grupo. 4.Siga seleccionando y agrupando filas internas hasta que haya creado todos los niveles que desee en el esquema. 5. Si desea desagrupar filas, seleccinelas y, a continuacin, en la ficha Datos, en el grupo Esquema, haga clic en Desagrupar. Nota Tambin puede desagrupar secciones del esquema sin quitarlo todo. Mantenga presionada la tecla MAYS mientras hace clic en o en para el grupo y, a continuacin, en la ficha Datos, en el grupo Esquema, haga clic en Desagrupar. Importante Si desagrupa un esquema mientras los datos de detalle estn ocultos, las filas de detalle permanecen ocultas. Para mostrar los datos, arrastre por los nmeros de fila visibles adyacentes a las filas ocultas. En la ficha Inicio, en el grupo Celdas, haga clic en Formato, elija Ocultar y mostrar y, a continuacin, haga clic en Mostrar filas. Crear un esquema de columnas 1. Asegrese de que cada fila tiene una etiqueta en la primera columna, contiene hechos similares en cada fila y el rango no tiene filas ni columnas en blanco. 2. Seleccione una celda del rango. 3. Ordene las filas que forman los grupos. Para obtener ms informacin sobre la ordenacin, vea Ordenar datos en un rango o tabla. 4. Inserte sus propias columnas resumidas con frmulas inmediatamente a la derecha o a la izquierda de cada grupo de columnas de detalle. Nota Para trazar esquemas de los datos por columnas, debe tener columnas de resumen que contengan frmulas que hagan referencias a las celdas de cada una de las columnas de detalle para dicho grupo. 5. Especifique si la ubicacin de la columna de resumen est a la derecha o a la izquierda de las columnas de detalle. 1. En la ficha Datos, en el grupo Esquema, haga clic en Esquema. 14 2. Para especificar una columna de resumen a la izquierda de la columna de detalles, desactive la casilla de verificacin Columnas resumen a la derecha del detalle. Para especificar una columna de resumen a la derecha de la columna de detalles, active la casilla de verificacin Columnas resumen a la derecha del detalle. 6. Aplique un esquema a los datos. Siga uno de los procedimientos siguientes: 1. En caso necesario, seleccione una celda del rango. 2. En la ficha Datos, en el grupo Esquema, haga clic en la flecha al lado de Agrupar y, a continuacin, haga clic en Autoesquema. Importante Cuando agrupe manualmente niveles de esquema, es mejor tener todos los datos mostrados para evitar agrupar las columnas incorrectamente. 3. Aplique un esquema al grupo externo. 1. Seleccione todas las columnas de resumen subordinadas, as como sus datos de detalle relacionados. En el ejemplo siguiente, la columna E contiene los subtotales para las columnas B a D, y la columna I contiene los subtotales para las columnas F a H, y la columna J contiene los totales generales. Para agrupar todos los datos de detalle para la columna J, seleccione las columnas B a I. A B C D E F G H I J 1 Regin ene Feb Mar T1 Abr May Jun T2 M1 2 Este 371 504 880 1.755 186 653 229 1.068 2.823 3 Oeste 192 185 143 520 773 419 365 1.557 2.077 4 Norte 447 469 429 1.345 579 180 367 1.126 2.471 5 Sur 281 511 410 1.202 124 750 200 1.074 2.276 Importante No incluya la columna resumida J en la seleccin. 2. En la ficha Datos, en el grupo Esquema, haga clic en Agrupar. 15 Los smbolos del esquema aparecen en la pantalla al lado del grupo. 4. De manera opcional, aplique un esquema a un grupo interno anidado. 1. Para grupo interno anidado, seleccione las columnas de detalle adyacentes a la columna que contiene la fila de resumen. En el ejemplo siguiente, para agrupar las columnas B a D, que tiene una columna resumida E, seleccione las columnas B a D. Para agrupar columnas F a H, que tiene una fila de resumen I, seleccione las columnas F a H. A B C D E F G H I J 1 Regin ene Feb Mar T1 Abr May Jun T2 M1 2 Este 371 504 880 1.755 186 653 229 1.068 2.823 3 Oeste 192 185 143 520 773 419 365 1.557 2.077 4 Norte 447 469 429 1.345 579 180 367 1.126 2.471 5 Sur 281 511 410 1.202 124 750 200 1.074 2.276 Importante No incluya en la seleccin la columna resumida para dicho grupo. 2. En la ficha Datos, en el grupo Esquema, haga clic en Agrupar. Los smbolos del esquema aparecen en la pantalla al lado del grupo. 5. Siga seleccionando y agrupando columnas internas hasta que haya creado todos los niveles que desee en el esquema. 6. Si desea desagrupar columnas, seleccinelas y, a continuacin, en la ficha Datos, en el grupo Esquema, haga clic en Desagrupar. Nota Tambin puede desagrupar secciones del esquema sin quitarlo todo. Mantenga presionada la tecla MAYS mientras hace clic en o en para el grupo y, a continuacin, en 16 la ficha Datos, en el grupo Esquema, haga clic en Desagrupar. Importante Si desagrupa un esquema mientras los datos de detalle estn ocultos, las columnas de detalle permanecen ocultas. Para mostrar los datos, arrastre por las letras de columna visibles adyacentes a las columnas ocultas. En la ficha Inicio, en el grupo Celdas, haga clic en Formato, elija Ocultar y mostrar y, a continuacin, haga clic en Mostrar columnas. Mostrar u ocultar datos de esquemas 1. Si no ve los smbolos de esquema , y , haga clic en el botn de Microsoft Office , haga clic en Opciones de Excel, en la categora Avanzadas y, a continuacin, en Mostrar opciones para esta hoja, seleccione la hoja de clculo y, a continuacin, active la casilla de verificacin Mostrar smbolos de esquema si se aplica un esquema. 2. Siga uno o varios de los procedimientos siguientes: Mostrar u ocultar los datos de detalle de un grupo Para mostrar los datos de detalle de un grupo, haga clic en del grupo. Para ocultar los datos de detalle de un grupo, haga clic en del grupo. Expandir o contraer todo el esquema hasta un nivel determinado En los smbolos de esquema , haga clic en el nmero del nivel que desee. Los datos de detalle de los niveles inferiores se ocultan. Por ejemplo, si un esquema tiene cuatro niveles, puede ocultar el cuarto y mostrar los restantes haciendo clic en . Mostrar u ocultar todos los datos de detalle del esquema Para mostrar todos los datos de detalle, haga clic en el nivel ms bajo de los smbolos de esquema . Por ejemplo, si existen tres niveles, haga clic en . Para ocultar todos los datos de detalle, haga clic en . Personalizar un esquema con estilos Para las filas del esquema, Microsoft Office Excel utiliza estilos (estilo: combinacin de caractersticas de formato, como fuente, tamao de fuente y sangra, que se nombra y almacena como un conjunto. Cuando aplique un estilo, todas las instrucciones de formato de ese estilo se aplican al mismo tiempo.) como NivelFila_1 y NivelFila_2. Para las columnas del esquema, Excel utiliza estilos como NivelCol_1 y NivelCol_2. Estos estilos emplean la negrita, la cursiva y otros formatos de texto para diferenciar las filas de resumen de los datos. Cambiando la forma en que estn definidos estos estilos, puede aplicar distintos formatos de texto y celdas para personalizar el aspecto del esquema. Puede aplicar un estilo a un esquema al crear el esquema o despus de crearlo. Siga uno o varios de los procedimientos siguientes: Aplicar un estilo automticamente a una columna o fila resumen 1. En la ficha Datos, en el grupo Esquema, haga clic en Esquema. 2. Active la casilla de verificacin Estilos automticos. Aplicar un estilo a una columna o fila resumen existente 17 1. Seleccione las celdas a las que desee aplicar estilos de esquema. 2. En la ficha Datos, en el grupo Esquema, haga clic en Esquema. 3. Active la casilla de verificacin Estilos automticos. 4. Haga clic en Aplicar estilos. Nota Asimismo, puede utilizar los autoformatos (autoformato: coleccin incorporada de formatos de celda (como tamao de fuente, tramas y alineacin) que puede aplicar a un rango de datos. Excel determina los niveles de resumen y detalle del rango seleccionado y aplica los formatos en consecuencia.) para dar formato a los datos esquematizados. Copiar datos esquematizados 1. Si no ve los smbolos de esquema , y , haga clic en el botn de Microsoft Office , haga clic en Opciones de Excel, en la categora Avanzadas y, a continuacin, en Mostrar opciones para esta hoja, seleccione la hoja de clculo y, a continuacin, active la casilla de verificacin Mostrar smbolos de esquema si se aplica un esquema. 2. Utilice los smbolos de esquema , y para ocultar los datos de detalle que no desee copiar. Para obtener ms informacin, vea la seccin, Mostrar u ocultar datos de esquemas. 3. Seleccione el rango de filas de resumen. 4. En la ficha Inicio, en el grupo Modificacin, haga clic en Buscar y seleccionar y, a continuacin, en Ir a. 5. Haga clic en Ir a Especial. 6. Haga clic en Slo celdas visibles. 7. Haga clic en Aceptar y, a continuacin, copie los datos. Ocultar o quitar un esquema Nota No se eliminan los datos al ocultar o quitar un esquema. Ocultar un esquema 1. Si no ve los smbolos de esquema , y , haga clic en el botn de Microsoft Office , haga clic en Opciones de Excel, en la categora Avanzadas y, a continuacin, en Mostrar opciones para esta hoja, seleccione la hoja de clculo y, a continuacin, active la casilla de verificacin Mostrar smbolos de esquema si se aplica un esquema. 2. Vea todos los datos haciendo clic en el nmero ms alto de los smbolos de esquema . 3. Haga clic en el botn de Microsoft Office , haga clic en Opciones de Excel, en la categora Avanzadas y, a continuacin, en la seccin Mostrar opciones para esta hoja, seleccione la hoja de clculo y desactive la casilla de verificacin Mostrar smbolos de esquema si se aplica un esquema. Quitar un esquema 1. Haga clic en la hoja de clculo. 2. En la ficha Datos, en el grupo Esquema, haga clic en la flecha situada junto a Desagrupar y, despus, en Borrar esquema. 18 3. Si las filas o columnas todava estn ocultas, arrastre los encabezados de filas o columnas visibles a ambos lados de las filas y columnas ocultas, elija Ocultar y mostrar en el comando Formato, en el grupo Celdas de la ficha Inicio y, a continuacin, haga clic en Mostrar filas o en Mostrar columnas. Importante Si quita un esquema mientras los datos de detalle estn ocultos, las columnas o filas de detalle permanecen ocultas. Para mostrar los datos, arrastre los nmeros de fila visibles o las letras de columna adyacentes a las columnas o filas ocultas. En la ficha Inicio, en el grupo Celdas, haga clic en Formato, elija Ocultar y mostrar y, a continuacin, haga clic en Mostrar filas o en Mostrar columnas. Crear un informe de resumen con un grfico Supongamos que desea crear un informe resumido de los datos que slo muestra los totales acompaados de un grfico de dichos totales. En general, puede hacer lo siguiente: 1. Crear un informe de resumen 1. Aplicar un esquema a los datos. Para obtener ms informacin, vea las secciones, Crear un esquema de filas o Crear un esquema de columnas. 2. Oculte el detalle haciendo clic en los smbolos de esquema , y para mostrar slo los totales como se muestra en el siguiente ejemplo de un esquema de fila: Para obtener ms informacin, vea la seccin, Mostrar u ocultar datos de esquemas. 2. Representar en un grfico el informe de resumen 1. Seleccione los datos de resumen que desea representar. Por ejemplo, para representar slo los totales de Buchanan y Davolio, pero no los totales generales, seleccione las celdas A1 a C11, como se muestra en el ejemplo anterior. 2. Cree el grfico. Por ejemplo, si crea el grfico con el Asistente para grficos, tendr un aspecto similar al del siguiente ejemplo. Nota Si muestra u oculta los detalles de la lista esquematizada de datos, el grfico tambin se actualiza para mostrar u ocultar los datos. Para obtener ms informacin, vea Crear un grfico y Mostrar datos ocultos de una hoja de clculo en un grfico. CONSOLIDAR DATOS EN VARIAS HOJAS DE CLCULO Para resumir y registrar resultados de hojas de clculo independientes, puede consolidar datos de cada una de estas hojas en una hoja de clculo maestra. Las hojas pueden estar en el mismo libro que la hoja de clculo maestra o en otros libros. Al consolidar datos, lo que se hace es ensamblarlos de modo que sea ms fcil actualizarlos y agregarlos de una forma peridica o especfica. 19 Por ejemplo, si tiene una hoja de clculo de cifras de gastos para cada una de las oficinas regionales, podra utilizar una consolidacin para resumir estas cifras en una hoja de clculo de gastos corporativa. Esta hoja de clculo maestra podra contener totales de ventas y promedios, niveles de inventario actuales y los productos ms vendidos de toda la organizacin. Para consolidar datos, utilice el comando Consolidar del grupo Herramientas de datos de la ficha Datos. Consolide por posicin 1. Configure los datos que se van a consolidar en cada una de las hojas de clculo independientes. Asegrese de que cada rango de datos est en formato de lista (lista: serie de filas que contienen datos relacionados o serie de filas que designa para que funcionen como hojas de datos mediante el comando Crear lista.): cada columna tiene un rtulo en la primera fila, contiene hechos similares y no tiene filas o columnas en blanco. Coloque cada rango en una hoja de clculo diferente. No ponga ningn rango en la hoja de clculo donde vaya a colocar la consolidacin. Asegrese de que cada rango tiene el mismo diseo. Asigne un nombre a cada rango: seleccione todo el rango y, luego, en la ficha Frmulas, en el grupo Celdas con nombre, haga clic en la flecha situada junto a Asignar nombre a un rango y escriba un nombre para el rango en el cuadro Nombre. 2. Haga clic en la celda superior izquierda del rea donde desee que aparezcan los datos consolidados en la hoja de clculo maestra. Nota Asegrese de dejar suficientes celdas a la derecha y por debajo de esta celda para los datos de consolidacin. El comando Consolidar rellena el rea segn proceda. 3. En la ficha Datos, en el grupo Herramientas de datos, haga clic en Consolidar. 4. En el cuadro Funcin, haga clic en la funcin de resumen (funcin de resumen: tipo de clculo que combina datos de origen en un informe de tabla dinmica o una tabla de consolidacin, o cuando se insertan subtotales automticos en una lista o base de datos. Algunos ejemplos de funciones de resumen son: Sumar, Contar y Promedio.) que desea que utilice Microsoft Office Excel para consolidar los datos. 5. Si la hoja de clculo se encuentra en otro libro, haga clic en Examinar para buscar el archivo y, a continuacin, haga clic en Aceptar para cerrar el cuadro de dilogo Examinar. La ruta de acceso del archivo se escribe en el cuadro Referencia seguido de un signo de exclamacin. 6. Escriba el nombre que le asign al rango y, a continuacin, haga clic en Agregar. Repita este paso para cada rango. 7. Decida cmo desea actualizar la consolidacin. Siga uno de los procedimientos siguientes: Para configurar la consolidacin de manera que se actualice automticamente cuando cambien los datos de origen, active la casilla de verificacin 20 Crear vnculos con los datos de origen. Importante Solamente puede activar esta casilla de verificacin si la hoja de clculo se encuentra en otro libro. Cuando active esta casilla de verificacin ya no podr cambiar las celdas y los rangos que se incluyen en la consolidacin. Para configurar la consolidacin de modo que puede actualizarla manualmente cambiando las celdas y los rangos incluidos, desactive la casilla de verificacin Crear vnculos con los datos de origen. 8. Deje en blanco las casillas de Usar rtulos en. Excel no copia los rtulos de fila o columna de los rangos de origen a la consolidacin. Si desea rtulos para los datos consolidados, cpielos de uno de los rangos de origen o introdzcalos manualmente. Consolide por categoras 1. Configure los datos que se van a consolidar en cada una de las hojas de clculo independientes. Asegrese de que cada rango de datos est en formato de lista (lista: serie de filas que contienen datos relacionados o serie de filas que designa para que funcionen como hojas de datos mediante el comando Crear lista.): cada columna tiene un rtulo en la primera fila, contiene hechos similares y no tiene filas o columnas en blanco. Coloque cada rango en una hoja de clculo diferente. No ponga ningn rango en la hoja de clculo donde vaya a colocar la consolidacin. Asegrese de especificar rtulos de categora exactamente iguales, incluidas maysculas y minsculas, en todas las reas de origen. Por ejemplo, los rtulos Prom. anual y Promedio anual son diferentes y no se consolidarn. Asigne un nombre a cada rango: seleccione todo el rango y, luego, en la ficha Frmulas, en el grupo Celdas con nombre, haga clic en la flecha situada junto a Asignar nombre a un rango y escriba un nombre para el rango en el cuadro Nombre. 2. Haga clic en la celda superior izquierda del rea donde desee que aparezcan los datos consolidados en la hoja de clculo maestra. Nota Asegrese de dejar suficientes celdas a la derecha y por debajo de esta celda para los datos de consolidacin. El comando Consolidar rellena el rea segn proceda. 3. En la ficha Datos, en el grupo Herramientas de datos, haga clic en Consolidar. 4. En el cuadro Funcin, haga clic en la funcin de resumen (funcin de resumen: tipo de clculo que combina datos de origen en un informe de tabla dinmica o una tabla de consolidacin, o cuando se insertan subtotales automticos en una lista o base de datos. Algunos ejemplos de funciones de resumen son: Sumar, Contar y Promedio.) que desea que utilice Excel para consolidar los datos. 5. Si la hoja de clculo se encuentra en otro libro, haga clic en Examinar para 21 buscar el archivo y, a continuacin, haga clic en Aceptar para cerrar el cuadro de dilogo Examinar. La ruta de acceso del archivo se escribe en el cuadro Referencia seguido de un signo de exclamacin. 6. Escriba el nombre que le asign al rango y, a continuacin, haga clic en Agregar. Repita este paso para cada rango. 7. Decida cmo desea actualizar la consolidacin. Siga uno de los procedimientos siguientes: Para configurar la consolidacin de manera que se actualice automticamente cuando cambien los datos de origen, active la casilla de verificacin Crear vnculos con los datos de origen. Importante Solamente puede activar esta casilla de verificacin si la hoja de clculo se encuentra en otro libro. Cuando active esta casilla de verificacin ya no podr cambiar las celdas y los rangos que se incluyen en la consolidacin. Para configurar la consolidacin de modo que puede actualizarla manualmente cambiando las celdas y los rangos incluidos, desactive la casilla de verificacin Crear vnculos con los datos de origen. 8. Active las casillas de verificacin de Usar rtulos en que indican donde se encuentran los rtulos en los rangos de origen, que puede ser: la Fila superior, la Columna izquierda o ambas. Notas Los rtulos que no coincidan con los de las otras reas de origen producirn filas o columnas independientes en la consolidacin. Asegrese de que las categoras que no desee consolidar tienen rtulos nicos que aparecen solamente en un rango de origen. Consolide por frmula 1. En la hoja de clculo maestra, copie o escriba los rtulos de columna o fila que desee para los datos de consolidacin. 2. Haga clic en la celda en que desea incluir los datos de consolidacin. 3. Escriba una frmula que incluya una referencia de celda a las celdas de origen de cada hoja de clculo o una referencia 3D que contenga los datos que desea consolidar. En cuanto a las referencias de celda, siga uno de los procedimientos siguientes: Si los datos que se van a consolidar estn en celdas diferentes de otras hoja de clculo Escriba una frmula con referencias de celda a las otras hojas de clculo, una por cada hoja de clculo independiente. Por ejemplo, para consolidar datos de hojas de clculo denominadas Ventas (en la celda B4), HR (en la celda F5) y Marketing (en la celda B9), en la celda A2 de la hoja de clculo maestra, tendra que escribir lo siguiente: Sugerencia Para especificar una referencia de celda como Ventas3!B4 en una frmula sin escribir, escriba la frmula hasta el punto en el que necesite la referencia, haga clic en la etiqueta de la hoja de clculo y, a continuacin, haga clic en la celda. 22 Si los datos que se van a consolidar estn en las mismas celdas de otras hojas de clculo Escriba una frmula con una referencia 3D que utilice una referencia a un rango de nombres de hojas de clculo. Por ejemplo, para consolidar datos en las celdas A2 desde Ventas hasta Marketing inclusive, en la celda A2 de la hoja de clculo maestra tendra que escribir lo siguiente: Nota Si el libro est configurado para calcular frmulas automticamente, una consolidacin por frmula siempre se actualiza automticamente cuando cambian los datos de las hojas de clculo independientes. Filtrar una Base de Datos: Filtrar una lista no es ni ms ni menos que de todos los registros almacenados en la tabla, seleccionar aquellos que correspondan a algn criterio, es una manera fcil y rpida de buscar un subconjunto de datos de un rango de celdas o de una columna de tabla y trabajar en l. Excel nos ofrece dos formas de filtrar una lista. - Utilizando el Filtro (autofiltro). - Utilizando filtros avanzados. Para utilizar el Filtro nos servimos de las listas desplegables asociadas a las cabeceras de campos (podemos mostrar u ocultar el autofiltro en la pestaa Datos marcando o desmarcando el botn Filtro). Los datos filtrados solamente muestran las filas que cumplen los criterios, es decir condiciones que se especifican para limitar los registros que se incluyen en el conjunto de resultados de una consulta o un filtro que se especifica y oculta las filas que no desea ver. Tras filtrar los datos, puede copiar, buscar, modificar, aplicar formato, representar mediante grficos e imprimir el subconjunto de datos filtrados sin tener que volver a organizarlo ni moverlo. Tambin puede filtrar por ms de una columna. Los filtros son aditivos, lo que significa que cada filtro adicional se basa en el filtro actual y adems reduce el subconjunto de datos. Con Autofiltro, puede crear tres tipos de filtros: por una lista de valores, por un formato o por criterios. Estos tipos de filtro se excluyen mutuamente para cada rango de celdas o tabla de columna. Si pulsamos, por ejemplo, sobre la flecha del campo 1er Apellido, nos aparece un men desplegable como este, donde nos ofrece una serie de opciones para realizar el filtro. 23 Por ejemplo, si slo marcamos Moreno, Excel filtrar todos los registros que tengan Moreno en el 1er apellido y las dems filas 'desaparecern' de la lista. Otra opcin, es usar los Filtros de texto, donde se despliegan una serie de opciones: En cualquier opcin, accedemos a una ventana donde podemos elegir dos condiciones de filtro de texto, y exigir que se cumpla una condicin o las dos. Excel evaluar la condicin elegida con el texto que escribamos, y si se cumple, mostrar la fila. Usaremos el carcter ? para determinar que en esa posicin habr un carcter, sea cual sea, y el asterisco * para indicar que puede haber o no un grupo de caracteres. En el ejemplo de la imagen, solo se mostrarn los registros cuyo 1er Apellido tenga una o en el segundo carcter y no contenga la letra z. Para indicarnos que hay un filtro activo, la flecha de la lista desplegable cambia de icono. Para quitar el filtro, volvemos a desplegar la lista y elegimos la opcin (Seleccionar Todo), reaparecern todos los registros de la lista. Tambin podemos quitar el filtro pulsando en Borrar filtro en la pestaa Datos. Filtros Avanzados: Si se quiere filtrar registros de la lista con una condicin ms compleja, utilizaremos el cuadro de dilogo Filtro avanzado. Previamente deberemos tener en la hoja de clculo, unas filas donde indicaremos los criterios del filtrado. Para abrir el cuadro de dilogo Filtro avanzado, pulse en en la seccin Ordenar y filtrar. Rango de la lista: Aqu especificamos los registros de la lista a los que queremos aplicar el filtro. 24 Rango de criterios: Aqu se selecciona la fila donde se encuentran los criterios de filtrado (la zona de criterios). Tambin podemos optar por guardar el resultado del filtrado en otro lugar, seleccionando la opcin Copiar a otro lugar, en este caso rellenaremos el campo Copiar a: con el rango de celdas que recibirn el resultado del filtrado. Si marcamos la casilla Slo registros nicos, las repeticiones de registros (filas con exactamente los mismos valores) desaparecern. Para volver a visualizar todos los registros de la lista, acceder al men Datos - Filtro - Mostrar todo. Ordenar una tabla de Datos: A la hora de ordenar una tabla, Excel puede hacerlo de forma simple, es decir, ordenar por un nico campo u ordenar la lista por diferentes campos a la vez. Para hacer una ordenacin simple, por ejemplo ordenar la lista anterior por el primer apellido, debemos posicionarnos en la columna del primer apellido, despus podemos acceder a la pestaa Datos y pulsar sobre Ordenar... y escoger el criterio de ordenacin o bien pulsar sobre uno de los botones de la seccin Ordenar y filtrar para que la ordenacin sea ascendente o descendente respectivamente. Estos botones tambin estn disponibles al desplegar la lista que aparece pulsando la pestaa junto al encabezado de la columna. Para ordenar la lista por ms de un criterio de ordenacin, por ejemplo ordenar la lista por el primer apellido ms la fecha de nacimiento, en la pestaa Datos, pulsamos sobre Ordenar... nos aparece el cuadro de dilogo Ordenar donde podemos seleccionar los campos por los que queremos ordenar (pulsando Agregar Nivel para aadir un campo), si ordenamos segn el valor de la celda, o por su color o icono (en Ordenar Segn), y el Criterio de ordenacin, donde elegimos si el orden es alfabtico (A a Z o Z a A) o sigue el orden de una Lista personalizada. Por ejemplo, si en la columna de la tabla se guardan los nombres de das de la semana o meses, la ordenacin alfabtica no sera correcta, y podemos escoger una lista donde se guarden los valores posibles, ordenados de la forma que creamos conveniente, y as el criterio de ordenacin seguir el mismo patrn. Seleccionando un nivel, y pulsando las flechas hacia arriba o hacia abajo, aumentamos o disminuimos la prioridad de ordenacin de este nivel. Los datos se ordenarn, primero, por el primer nivel de la lista, y sucesivamente por los dems niveles en orden descendente. 25 En la parte superior derecha tenemos un botn Opciones..., este botn sirve para abrir el cuadro Opciones de ordenacin dnde podremos especificar ms opciones segn el criterio de la ordenacin. ANALISIS DE DATOS Importar y Exportar Datos: En muchas ocasiones tenemos la necesidad de trabajar en Excel con datos procedentes de otras aplicaciones. Tenemos dos alternativas: - Introducir de nuevo los datos en un libro de trabajo, con el consumo de tiempo que ello implica ms el riesgo de introducir errneamente los datos al introducirlos manualmente. - Utilizar algunas de las herramientas disponibles en Excel para importar datos. Para importar datos externos a Excel disponemos bsicamente de dos opciones: - Utilizar el portapapeles de Windows, es decir, copiar los datos de la aplicacin externa y pegarlos en una hoja de Excel. - Importar datos de otro archivo que no tiene que ser necesariamente del formato Excel. La primera opcin es la ms directa, pero tiene el contrapunto de ser ms laboriosa y tediosa. La segunda opcin es ms rpida, pero pueden surgir problemas de compatibilidad dependiendo del formato del archivo a importar. El problema fundamental de la importacin de datos externos, hemos dicho que es debido al formato de los propios archivos, esto quiere decir que cada aplicacin genera un archivo con un formato propio para identificar mejor el contenido de los datos, por ejemplo Excel al generar un archivo .xls no solamente guarda los datos que hemos introducido sino que lo guarda con un formato especial para interpretar el documento en su plenitud, de esta manera sabe exactamente dnde estn las frmulas, qu formato esttico tiene el documento, etc. Adems de esto, al importar datos de una aplicacin externa debemos tener en cuenta que pueden surgir los siguientes fallos: - Algunas de las frmulas no ajustan correctamente. - El formato no se ajusta al original. - Las frmulas y funciones que no entiende no las copia. Pese a todos estos contratiempos, siempre es mejor intentar realizar una importacin y despus comprobar si todo ha salido correctamente. A no ser que la cantidad de datos no sea demasiado extensa y nos decantemos por utilizar el portapapeles. Cuando decimos archivos de texto nos estamos refiriendo a archivos que no tienen formato, los conocidos como archivos de texto plano (ASCII); los archivos de texto con formato como los de Word (.doc) o los (.rtf) tienen otra forma de importarse a Excel que veremos ms adelante. 26 Pero para importar archivos de texto con el asistente podemos hacerlo de dos formas distintas: - Podemos acceder a la pestaa Datos y seleccionar uno de los tipos de orgenes de datos que podemos encontrar en esta seccin: O acceder mediante el Botn Office - Abrir y donde pone Tipo de datos seleccionar Todos los archivos (*.*). A continuacin en ambos casos se sigue el mismo procedimiento. Nos aparece un cuadro de dilogo para seleccionar el archivo a importar. Una vez seleccionado el archivo de texto aparecen una serie de tres pantallas correspondientes al asistente para importar texto. En la ventana del Asitente para importar texto -Paso 1 , aparecen varias opciones: Podemos indicar si el texto importado tiene los Campos delimitados o no para que Excel sepa donde empieza un campo y dnde acaba. Podemos indicar a partir de qu fila queremos importar por si queremos descartar ttulos, por ejemplo. Y tambin podemos decidir el Origen del archivo. Si todos los datos son correctos pulsamos sobre Siguiente En la segunda pantalla del asistente podemos elegir los separadores de los datos. Por defecto viene marcado el separador de Tabulacin. En la imagen podemos ver que lo que hay en cada lnea lo incluye en un slo campo, esto es debido a que no hay signos de tabulacin en el texto por lo que todo lo escrito lo pone en un slo campo. Si se hubiera seleccionado Espacio, el carcter espacio en blanco delimitara los campos es decir que nos apareceran varias columnas (varios campos. Cuando utilizamos esta opcin (separar con espacios en blanco), es conveniente activar Considerar separadores consecutivos como uno solo, esta opcin hace que si por ejemplo se encuentran dos o ms caracteres separadores juntos, los considera como uno slo. En el ejemplo anterior, vamos a suponer que en la primera lnea tenemos dos espacios en blanco entre 13 y FANTASMAS, si activamos Espacio y dejamos desactivada la casilla Considerar separadores consecutivos como uno solo, Excel colocar 13 en la primera columna y FANTASMAS en la tercera, mientras que si activamos la 27 casilla Considerar separadores consecutivos como uno solo, la palabra FANTASMAS se colocar en la segunda columna, como podemos apreciar en la siguiente imagen. Para continuar con el asistente pulsamos sobre Siguiente. En la tercera y ltima pantalla del asistente, podemos seleccionar el Formato de los datos. Normalmente Excel puede determinar el tipo de los datos de una columna por los valores contenidos en ella, pero podemos cambiar ese formato si no nos parece el ms adecuado. Para ello slo tenemos que hacer clic en la columna para seleccionarla (aparece con fondo negro), y seleccionar el formato en la seccin Formato de los datos en columnas. Esta seccin tambin nos sirve si no queremos importar la columna en nuestra hoja (No importar columna (saltar)). En el botn Avanzadas podemos completar ciertas caractersticas para los nmeros como los separadores de decimales y millares y la posicin del signo en los negativos. Una vez se haya completado o comprobado la definicin, damos clic en Finalizar para que termine el asistente e importe los datos. Si de lo contrario, creemos que se nos ha olvidado algo, podemos volver a las pantallas anteriores pulsando el botn 28 En la seccin Control de actualizacin podemos hacer que Excel actualice automticamente los datos insertados en la hoja de clculo, cada vez que se abra el libro (marcando la casilla Actualizar al abrir archivo). Si los datos almacenados en el fichero origen han cambiado, estos cambios quedarn reflejados en nuestra hoja de clculo. Esto es posible porque Excel almacena en el libro de trabajo la definicin de la consulta de donde son originarios los datos importados, de manera que puede ejecutarla de nuevo cuando se desee. Si marcamos la casilla Solicitar el nombre del archivo al actualizar, cada vez que se vayan a actualizar los datos importados, Excel nos pedir de qu fichero coger los datos, sino los coger directamente del archivo que indicamos cuando importamos por primera vez. Esta opcin puede ser til cuando tenemos varios archivos con datos y en cada momento queremos ver los datos almacenados en uno de esos ficheros. En la seccin Definicin de consulta tenemos activada por defecto la casilla Guardar definicin de consulta para que Excel guarde esta definicin y pueda actualizar los datos cuando se lo pidamos, si desactivamos esta casilla y guardamos el libro, Excel no podr volver a actualizar los datos externos porque stos sern guardados como un rango esttico de datos, digamos que se perder el enlace con el origen de la importacin. Por ltimo, en la seccin Diseo y formato de datos podemos indicarle a Excel qu debe de hacer cuando actualiza los datos. Volviendo a la seccin Conexiones, tambin nos permite actualizar manualmente los datos importados de un archivo externo. Para ello, haz clic en la opcin Actualizar que se encuentra en el desplegable Actualizar todo. Si escoges la opcin Actualizar todo, todos los orgenes de datos se actualizarn. Importar datos de Word a Excel. A veces puede ser til importar una tabla de Word a Excel si se desea agregar algn tipo de frmula a los datos de los que se dispone y no queremos volver a teclear todos los datos en un libro de Excel. Para realizar este proceso es tan sencillo como copiar y pegar. -Estando en Word seleccionamos la tabla que queremos pasar a Excel. - Copiamos la tabla seleccionada, bien pulsando Ctrl + C o desde la pestaa Inicio y haciendo clic en el botn Copiar. - Ahora en Excel seleccionamos la celda donde queremos colocar la tabla. - Y pulsamos Ctrl + V o desde la pestaa Inicio y haciendo clic en el botn Pegar. Es posible que nos advierta de que el texto seleccionado es ms pequeo que la hoja, pulsamos sobre Aceptar y nos pegar la tabla con el mismo formato que la tenamos en Word. Si deseamos que no aparezca el marco de la tabla lo podemos eliminar utilizando las opciones de este men: Importar datos de Excel a Word. Al importar datos de Excel a Word adems de lo visto anteriormente podemos hacer que los datos queden vinculados, es decir que adems de copiar los datos de Excel en un documento de Word si realizamos algn cambio en la hoja de Excel, este cambio queda reflejado en el documento de Word. Para realizar este proceso: - Seleccionamos la informacin a copiar de la hoja de Excel. 29 -Copiamos los datos seleccionados pulsando Ctrl + C o desde la pestaa Inicio y haciendo clic en el botn Copiar. - Nos vamos a Word y pulsamos Ctrl + V o desde la pestaa Inicio y haciendo clic en el botn Pegar. Despus de pegar los datos en Word nos aparece un indicador de pegado donde podemos ver las distintas opciones. Por defecto nos conserva el mismo formato que tena en la hoja de Excel. Si lo queremos insertar en una tabla podemos escoger la opcin Hacer coincidir estilo de tabla de destino. Y si queremos vincular los datos de Excel para que se actualicen en Word podemos escoger la opcin Conservar formato de origen y vincular a Excel. Importar Datos de Access: Tambin disponemos de un botn que nos permitir importar datos directamente de consultas y tablas de Microsoft Access. Pulsando Desde Access, aparecer el dilogo Abrir para que elijamos de qu base de datos deseamos importar los datos, una vez elegida la base, y una lista de tablas y consultas. Seleccionamos una tabla o consulta y pulsamos Aceptar. En la siguiente ventana podemos elegir cmo ver los datos en el libro, entre las opciones que ya conocemos, o si preferimos incluirlos en la hoja actual, indicando su posicin, o en una nueva hoja del libro. Pulsando en Propiedades, podremos modificar las propiedades de la conexin, como la Descripcin o cuando se debe actualizar. Importar desde una Pagina Web: Otra herramienta para importar datos, nos permite obtener datos Desde Web. Pulsando sobre ella, se abrir una ventana del navegador, donde se marcan con flechas amarillas las tablas existentes en la pgina web. 30 Para elegir las tablas, basta con dar clic sobre las flechas. En el botn Opciones... de esta ventana, podremos elegir por ejemplo, si importar o no el formato de la tabla. Ten en cuenta que aunque no se vean los bordes, la mayora de las webs estn estructuradas con tablas. Una vez finalizada la eleccin, pulsamos Importar. Veremos entonces la misma ventana que con Access, donde elegir si importar en la hoja o en una nueva, y donde podremos modificar en Preferencias las opciones de actualizacin de la conexin. Otra forma rpida de hacer esto, es directamente seleccionar la tabla en nuestro navegador, copiarla con Ctrl + C y pegarla en la hoja de Excel. Despus podremos modificar el formato y si se quiere que haya actualizacin. Importar de otros programas: La mayora de tablas que nos encontremos en otros documentos, y podamos seleccionar y copiar, Excel nos permitir pegarlas en una hoja de clculo. En algunos nos permitir crear conexiones de actualizacin y en otros no. Y otros programas nos permitirn exportar tablas directamente a Excel. Por ejemplo, si navegamos con Internet Explorer, al hacer clic derecho sobre una tabla, en el men contextual encontraremos la opcin Exportar a Excel, que volcar los datos de la tabla en una nueva hoja de clculo. Tablas Dinmicas: Una tabla dinmica consiste en el resumen de un conjunto de datos, atendiendo a varios criterios de agrupacin, representado como una tabla de doble entrada que nos facilita la interpretacin de dichos datos. Es dinmica porque nos permite ir obteniendo diferentes totales, filtrando datos, cambiando la presentacin de los datos, visualizando o no los datos origen, etc... Suponga que tiene una coleccin de datos de los artculos de un almacn con el nmero de referencia y el mes de compra, adems sabemos la cantidad comprada y el importe del mismo. Vamos a crear una tabla dinmica a partir de estos datos para poder examinar mejor las ventas de cada artculo en cada mes. Para ello vamos a la pestaa Insertar y hacemos clic en Tabla dinmica (tambin podemos desplegar el men haciendo clic en la flecha al pie del botn para crear un grfico dinmico). 31 Aparece el cuadro de dilogo de creacin de tablas dinmicas. Desde aqu se puede indicar el lugar donde se encuentran los datos que se quieren analizar y el lugar de ubicacin. En este caso se indica que se van a seleccionar los datos de un rango de celdas y que queremos ubicarla en una hoja de clculo nueva. Se puede crear una conexin con otra aplicacin para obtener los datos desde otra fuente diferente a Excel. En el caso de seleccionar la opcin Selecciona una tabla o rango debemos seleccionar todas las celdas que vayan a participar, incluyendo las cabeceras. De clic en Aceptar para seguir, entonces Se abrir un nuevo panel en la derecha de la pantalla: Desde este panel se personaliza la forma en que van a verse los datos en la tabla dinmica. Con esta herramienta podramos construir una tabla dinmica con la siguiente estructura: - Una fila para cada una de las Referencias de la tabla. - Una columna para cada uno de los Meses de la tabla. - En el resto de la tabla se puede incluir el total del Importe para cada Referencia en cada Mes. Para ello simplemente tendremos que arrastrar los elementos que vemos listados a su lugar correspondiente al pie del panel. En este ejemplo deberamos arrastrar el campo REF a , el campo MES a y finalmente el campo IMPORTE a la seccin . 32 Al realizar la tabla dinmica se puede ver que la estructura es la que hemos definido anteriormente, en el campo fila tenemos las referencias, en el campo columnas tenemos los meses y en el centro de la tabla las sumas de los importes. Con esta estructura es mucho ms fcil analizar los resultados, veamos: Al tener creada la tabla dinmica nos aparece la pestaa Opciones: El panel lateral seguir pudindose utilizar, as que en cualquier momento se puede quitar un campo de una zona arrastrndola fuera. Con esto vemos que en un segundo podemos variar la estructura de la tabla y obtener otros resultados. Grficos con Tablas Dinmicas: Para crear una grfica a partir de una tabla dinmica, se debe dar clic en el botn grafico dinmico de la pestaa de opciones. Para cambiar el formato del grfico a otro tipo de grfico que nos agrade ms o nos convenga ms segn los datos que tenemos. Al pulsar este botn se abrir el cuadro de dilogo de Insertar grfico, all deberemos escoger el grfico que ms nos convenga. Luego, la mecnica a seguir para trabajar con el grfico es la misma que se vio en el tema de grficos. Vincular Hojas de Clculo: El concepto de enlazar en Excel es el hecho de utilizar frmulas de varias hojas para combinar datos. Al enlazar hojas de trabajo estamos creando una dependencia de una, con respecto a la otra hoja, entonces se habla de dos trminos: - el libro de trabajo dependiente: es el que contiene las frmulas. 33 - el libro de trabajo fuente: es el que contiene los datos. Para crear un libro de trabajo dependiente debemos crear frmulas de referencias externas, es decir frmulas que hacen referencia a datos que se encuentran en una hoja externa a la que est la frmula. Para crear frmulas de referencia externa debemos seguir los siguientes pasos: - Abrir el libro de trabajo fuente (el libro que contiene los datos). - Abrir el libro de trabajo dependiente, seleccionar la celda donde queremos incluir la frmula. - Introducir la frmula de acuerdo a lo deseado, cuando se llegue al punto de escoger las celdas de datos, activar el libro de trabajo fuente y seleccionar las celdas necesarias. - Terminar la frmula y pulse Intro. Las referencias al libro externo las gestiona automticamente Excel, incluso si cambiamos el nombre del archivo donde estn los datos desde Archivo Guardar como... las referencias tambin se cambiaran. Si en el libro de trabajo dependiente queremos ver todos los vnculos a las pginas fuente podemos hacerlo accediendo a la pestaa Datos y haciendo clic en el botn Editar vnculos... Nos aparece el cuadro de dilogo Modificar vnculos donde podemos hacer modificaciones sobre los vnculos. Actualizar valores: Actualiza la lista con los vnculos. Cambiar origen: Podemos modificar el origen del vnculo seleccionado. Abrir origen: Abre el libro del vnculo seleccionado. Romper vnculo: Quita los vnculos de las frmulas externas. Comprobar estado: Comprueba el estado del origen. Pregunta inicial: Podemos incluir una advertencia al abrir el libro dependiente, indicando que tenemos enlaces externos. Consolidar Hojas de Trabajo: El concepto de consolidar hojas de trabajo viene muy ligado al concepto de enlace que acabamos de ver. Cuando hablamos de consolidar hojas de trabajo estamos entablando una relacin entre varias hojas de trabajo, por tanto es muy posible que existan enlaces entre esas hojas de trabajo. Un ejemplo donde se puede utilizar la consolidacin de hojas de trabajo puede ser una compaa que dispone de varios departamentos, cada uno de ellos con un presupuesto, para conocer el presupuesto total de la empresa, crearamos una hoja dependiente y los libros fuentes seran los libros de los departamentos con sus prepuestos. 34 Si pulsamos sobre la pestaa Datos y pulsando el botn Consolidar... nos aparece el cuadro de dilogo Consolidar como vemos en la imagen donde podemos escoger: - la Funcin: a utilizar, -en Referencia: vamos seleccionando las celdas de uno de los libros fuentes, - pulsamos sobre Agregar para aadir las celdas seleccionadas a la lista de todas las referencias, - repetimos los dos ltimos pasos para seleccionar las celdas de los diferentes libros fuentes. -finalmente pulsamos en Aceptar para finalizar la frmula. Si marcamos la casilla Crear vnculos con los datos de origen, los datos de los libros fuente se incluirn en el libro consolidado a modo de esquema, y si cambiamos un valor en el origen, ese cambio quedar automticamente reflejado en el libro consolidado. Asignar Contraseas: En Microsoft Excel se puede usar contraseas para impedir que otras personas abran o modifiquen los archivos. Para permitir que slo las personas autorizadas observen o modifiquen los datos, puede proteger el libro completo con una contrasea, veamos: 1. Haga clic en el botn de Microsoft Office y, a continuacin, haga clic en Guardar como. 2. Haga clic en Herramientas y, a continuacin, en Opciones generales. 3. Siga uno o ambos procedimientos: Si desea que los revisores escriban una contrasea para poder ver el libro, especifique la contrasea en el cuadro Contrasea de apertura. Si desea que los revisores escriban una contrasea para poder guardar los cambios en el libro, especifique la contrasea en el cuadro Contrasea de escritura. Ambas contraseas Puede asignar ambas contraseas: una para tener acceso al archivo y otra para proporcionar a revisores especficos permiso para modificar el contenido. Asegrese de que cada contrasea es diferente. 4. Si no desea que los revisores de contenido modifiquen el archivo por error, active la casilla de verificacin Recomendado slo lectura. Al abrir el archivo, se preguntar a los revisores si desean abrir el archivo como de slo lectura. 35 5. Haga clic en Aceptar. 6. Cuando se le indique, vuelva a escribir las contraseas para confirmarlas y, a continuacin, haga clic en Aceptar. 7. Haga clic en Guardar. 8. Si se le pregunta si desea reemplazar el libro existente, haga clic en S. Proteger Celdas: Adems de la proteccin mediante contraseas para los libros de trabajo, Excel2007 ofrece rdenes para proteger las celdas del libro. Para ello se tiene que realizar dos operaciones: la primera que consiste en proteger las celdas que no queremos que sufran variaciones, y la segunda que consiste en proteger la hoja. Cuando una celda est bloqueada no podr sufrir variaciones. Realmente por defecto todas las celdas estn protegidas o bloqueadas para que no sufran cambios, pero no nos damos cuenta ya que la hoja no est protegida, para que realmente se bloqueen las celdas antes hay que proteger la hoja de clculo. Para desbloquear las celdas que queremos variar en algn momento sigue los siguientes pasos: Seleccionar el rango de celdas que queremos desbloquear para poder realizar variaciones. Seleccionar la pestaa Inicio. Hacer clic sobre la flecha que se encuentra bajo la seccin Fuente. Hacer clic sobre la pestaa Proteger. Aparecer la ficha de la derecha: Desactivar la casilla Bloqueada y Hacer clic sobre el botn Aceptar. Si se activa la casilla Oculta, lo que se pretende es que la frmula o el valor de la celda no se pueda visualizar en la barra de frmulas. Las operaciones de la ficha Proteger no tienen efecto si no protegemos la hoja de clculo, por lo tanto a continuacin tendremos que realizar los siguientes pasos: Seleccionar la pestaa Revisar. Hacer clic sobre el botn Proteger hoja que se encuentra en la seccin Cambios Aparecer el cuadro de dilogo Proteger hoja. 36 Dejar activada la casilla Proteger hoja y contenido de celdas bloqueadas para proteger el contenido de las celdas de la hoja activa. Activar las opciones deseadas de la casilla Permitir a los usuarios de esta hoja de clculo para que no tenga efecto la proteccin para la modificacin seleccionada y desactivarla para tener en cuenta la proteccin. Si queremos asignar una contrasea para que solamente pueda desproteger la hoja la persona que sepa la contrasea, escribirla en el recuadro Contrasea. Hacer clic sobre el botn Aceptar. Si hemos puesto contrasea nos pedir confirmacin de contrasea, por lo tanto tendremos que volver a escribirla y hacer clic sobre el botn Aceptar. A partir de ahora la hoja activa se encuentra protegida, por lo que no se podrn modificar aquellas celdas bloqueadas en un principio. Si queremos desproteger la hoja, volveremos a realizar los mismos pasos que en la proteccin, es decir: Seleccionar la pestaa Revisar. Hacer clic sobre el botn Desproteger hoja que se encuentra en la seccin Cambios. Si habamos asignado una contrasea nos la pedir, por lo que tendremos que escribirla y hacer clic sobre el botn Aceptar. Si no haba contrasea asignada, automticamente la desprotege. Si la macro a relacionar con el botn ya est creada, simplemente al insertar el botn, seleccione el nombre de la macro del cuadro de dialogo Asignar una Macro y de clic en el botn Aceptar.