excel 2007 modulol ii

Educación y Tecnologías de Información S.A.

Bogotá - Colombia

2008

Educación y Tecnologías de Información 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 información sobre la ordenación .................................................................................. 4

ORDENAR TEXTO ............................................................................................................................. 5

Ordenar números ........................................................................................................................ 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 más de una columna o fila ..................................................................................... 8

Ordenar una columna en un rango de celdas sin afectar a las demás ....................................... 9

Más información sobre problemas generales de ordenación .................................................. 10

ESQUEMATIZAR UNA LISTA DE DATOS EN UNA HOJA DE CÁLCULO ............................................. 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 gráfico ........................................................................... 18

CONSOLIDAR DATOS EN VARIAS HOJAS DE CÁLCULO .................................................................. 18

Consolide por posición .............................................................................................................. 19

Consolide por categorías ........................................................................................................... 20

Consolide por fórmula ............................................................................................................... 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 Dinámicas: .......................................................................................................................... 30

Gráficos con Tablas Dinámicas: ................................................................................................. 32

Vincular Hojas de Cálculo: ......................................................................................................... 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 sistemáticamente para su posterior uso. Un libro de Microsoft Office Excel tiene la capacidad de incluir una o varias hojas de cálculo 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 demás filas contienen los datos almacenados. Es como una tabla de base de datos, de hecho también se denominan listas de base de datos. Cada fila es un registro de entrada.

Las tablas son muy útiles porque además de almacenar información, incluyen una serie de operaciones que permiten analizar y administrar esos datos de forma muy cómoda.

Entre las operaciones más interesantes que podemos realizar con las listas tenemos:

- Ordenar los registros.

- Filtrar el contenido de la tabla por algún criterio.

- Utilizar fórmulas para la lista añadiendo algún 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 análisis de datos. Puede que desee poner una lista de nombres en orden alfabético, 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 más fácilmente y a tomar decisiones más 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 más información, vea Filtrar datos de un rango o una tabla y Agregar, cambiar o borrar formatos condicionales. Obtener información sobre la ordenación Puede ordenar datos por texto (A a Z o Z a A), números (de menor a mayor o de mayor a menor) y fechas y horas (de más antiguos más recientes y de más recientes a más antiguos) en una o varias columnas. También puede ordenar por una lista personalizada (como Grande, Medio y Pequeño) o por formato, incluyendo el color de celda, el color de fuente o el conjunto de iconos. La mayoría de las operaciones de ordenación son ordenaciones de columnas, pero también es posible ordenar por filas. Los criterios de ordenación 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 ordenación para poder volver a aplicar periódicamente una ordenación al abrir un libro, entonces lo mejor es utilizar una tabla. Esto es especialmente importante para

ms-help://MS.EXCEL.12.3082/EXCEL/content/HP10073941.htm






5

ordenaciones de varias columna o para las que se tarda mucho tiempo en crear.

ORDENAR TEXTO

1. Seleccione una columna de datos alfanuméricos en un rango de celdas o asegúrese de que la celda activa está en una columna de tabla que contiene datos alfanuméricos.

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

alfanumérico ascendente, haga clic en Ordenar de A a Z.

Para ordenar en orden alfanumérico descendente, haga clic en Ordenar de Z a A.

4. También puede ordenar distinguiendo las mayúsculas de las minúsculas.

Cómo ordenar distinguiendo las mayúsculas de las minúsculas

1.En la ficha Inicio, en el grupo Modificación, haga clic en Ordenar y filtrar y, a continuación, en Personalizar ordenación. 2.En el cuadro de diálogo Ordenar, haga clic en Opciones. 3.En el cuadro de diálogo Opciones de ordenación, seleccione Distinguir mayúsculas de minúsculas.

4. Haga clic dos veces en Aceptar.

Problema: comprobar si todos los datos están almacenados como texto Si la columna que desea ordenar contiene números almacenados

como números y números almacenados como texto, será necesario que les aplique formato de texto. Si no lo hace, los números almacenados como números se ordenarán antes que los números 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 botón Formato de fuente de celda, haga clic en la ficha Número y, en Categoría, haga clic en Texto. Problema: quitar los espacios a la izquierda En algunos casos, los datos que se hayan importado de otra aplicación pueden tener insertados espacios a la izquierda delante de los mismos. Antes de ordenar los datos, quite estos espacios. Ordenar números 1. Seleccione una columna de datos

numéricos en un rango de celdas o asegúrese de que la celda activa está en una columna de tabla que contiene datos numéricos.

2. En la ficha Inicio, en el grupo Modificar, haga clic en Ordenar y filtrar y, a continuación, siga uno de los procedimientos siguientes: Para ordenar de números más

bajos a números más altos, haga clic en Ordenar de menor a mayor.

Para ordenar de números más altos a números más bajos, haga clic en Ordenar de mayor a menor.

Problema: comprobar que todos los números están almacenados como números Si los resultados no le ofrecen lo que esperaba, es posible que la columna contenga números almacenados como texto y no como números. Por ejemplo, los números negativos importados de algunos sistemas de contabilidad o los números con un apóstrofo inicial (') se almacenan como texto. Para obtener más información, vea Convertir números almacenados como texto a números. Ordenar fechas u horas




6

1. Seleccione una columna de fechas u horas en un rango de celdas o asegúrese 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 continuación, siga uno de los procedimientos siguientes: Para ordenar de una hora o

fecha anterior a una posterior, haga clic en Ordenar de más antiguos a más recientes.

Para ordenar de una hora o fecha posterior a una anterior, haga clic en Ordenar de más recientes a más antiguos.

Problema: comprobar que las fechas y las horas están 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 números 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 más información, vea Convertir fechas almacenadas como texto en fechas. Nota Si desea ordenar por días de la semana, aplique formato a las celdas para mostrar el día de la semana. Si desea ordenar por el día de la semana independientemente de la fecha que sea, conviértalos en texto utilizando la función TEXTO. Sin embargo, la función TEXTO devuelve un valor de texto y, por tanto, la operación de ordenación estará basada en datos alfanuméricos. Para obtener más información, vea Mostrar la fecha como día 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, también puede ordenar por estos colores. Además, puede ordenar por un conjunto de iconos creado mediante un formato condicional. 1. Seleccione una columna de datos en

un rango de celdas o asegúrese de que la celda activa está en una columna de tabla.

2. En la ficha Inicio, en el grupo Modificación, haga clic en Ordenar y filtrar y, a continuación, en Personalizar ordenación.

Se mostrará el cuadro de diálogo Ordenar.

3. En Columna, en el cuadro Ordenar por, seleccione la columna que desea ordenar.

4. En Ordenar según, seleccione el tipo de ordenación. 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 ordenación, haga clic en la flecha situada junto al botón y, a continuación, 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 ordenación de columnas y A la izquierda para una ordenación 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 ordenación de columnas y A la derecha para una ordenación de filas.

Nota No hay ningún color de celda, color de fuente ni criterio de ordenación de iconos predeterminados. Debe definir el orden que desea para cada operación de ordenación.

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 continuación, repita los pasos del tres al cinco. Asegúrese de que selecciona la misma columna en el cuadro Luego por y de que realiza la misma selección en Ordenar. Repita el procedimiento por cada color de celda, color de fuente o icono adicionales que desea incluir en la ordenación.

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 días de la semana y de meses del año, y puede además crear sus propias listas. 1. También puede crear una lista

personalizada. Cómo 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 Máximo

2 Medio

3 Mínimo

2. Seleccione el rango que acaba de especificar. En el ejemplo anterior seleccionaría las celdas A1:A3.

3. Haga clic en el botón de

Microsoft Office , haga clic en Opciones de Excel, en la categoría Populares y, a continuación, en Opciones principales para trabajar con Excel, haga clic en Modificar listas personalizadas.

4. En el cuadro de diálogo Listas personalizadas, haga clic en Importar y, a continuación, en Aceptar dos veces.

Notas Sólo puede crear una lista

personalizada basada en un valor (texto, número, fecha u hora). No puede crear una lista personalizada basada en un formato (color de celda, color de fuente e icono).

La longitud máxima para una lista personalizada es de 255 caracteres y el primer carácter puede no empezar por un número.

Seleccione una columna de datos en un rango de celdas o asegúrese de que la celda activa está en una columna de tabla. En la ficha Inicio, en el grupo Modificación, haga clic en Ordenar y filtrar y, a continuación, en Personalizar ordenación.

8

Se mostrará el cuadro de diálogo 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 diálogo Listas personalizadas, seleccione la lista que desee. En el ejemplo anterior, haría clic en Alto, Medio, Bajo. Haga clic en Aceptar.

Ordenar filas 1. Seleccione una fila de datos en un

rango de celdas o asegúrese de que la celda activa está en una columna de tabla.



3. Haga clic en Opciones. 4. En el cuadro de diálogo Opciones de

ordenación, en Orientación, haga clic en Ordenar de izquierda a derecha y, a continuación, 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 según, seleccione Valores.

2. En Criterio de ordenación, siga uno de los procedimientos siguientes: Para los valores de texto,

seleccione A a Z o Z a A. Para los valores

numéricos, seleccione De menor a mayor o De mayor a menor.

Para los valores de fecha y hora, seleccione De más antiguos a más recientes o De más recientes a más antiguos.

Por color de celda, color de fuente o icono de celda

3. En Ordenar según, seleccione Color de celda, Color de fuente o Icono de celda.

4. Haga clic en la flecha situada junto al botón y, a continuación, 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 cálculo, Excel ordenará los grupos de nivel más alto (nivel 1) de modo que las filas o las columnas de detalle permanezcan juntas, aunque estén ocultas. Ordenar por más de una columna o fila Puede ordenar por más de una columna o fila cuando tiene datos que desea agrupar por el mismo valor en una columna o fila y ordenar después 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, después, ordenar por nombre (para escribir los nombres en orden alfabético dentro de cada departamento). Puede ordenar hasta un máximo 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 más columnas de datos, o asegúrese de que la celda activa se encuentra en una tabla con dos o más columnas.



3. En Columna, en el cuadro Ordenar por, seleccione la primera columna que desea ordenar.

4. En Ordenar según, seleccione el tipo de ordenación. Siga uno de los procedimientos siguientes: Para ordenar por texto,

número 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 numéricos,

seleccione De menor a mayor o De mayor a menor.

Para los valores de fecha y hora, seleccione De más antiguos a más recientes o De más recientes a más antiguos.

Para ordenar según una lista personalizada, seleccione Lista personalizada.

6. Para agregar otra columna por la que ordenar, haga clic en Agregar nivel y, a continuación, repita los pasos del tres al cinco.

7. Para copiar una columna por la que ordenar, seleccione la entrada y, a continuación, haga clic en Copiar nivel.

8. Para eliminar una columna por la que ordenar, seleccione la entrada y, a continuación, 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 continuación, haga clic en la flecha Arriba o Abajo para cambiar el orden. Las entradas más altas de la lista se ordenan antes que las más bajas.

Ordenar una columna en un rango de celdas sin afectar a las demás Advertencia Tenga cuidado cuando utilice esta característica. 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 continuación, haga clic en uno de los comandos de ordenación que haya disponibles.

4. Aparecerá el cuadro de diálogo Advertencia antes de ordenar.

5. Seleccione Continuar con la selección actual.

6. Haga clic en Ordenar.

10

7. Seleccione las demás opciones de ordenación que desea en el cuadro de diálogo Ordenar y, a continuación, haga clic en Aceptar.

Si el resultado no es el que desea, haga clic en

Deshacer . Más información sobre problemas generales de ordenación Si obtiene resultados inesperados al ordenar sus datos, haga lo siguiente: Comprobar si los valores que devuelve una fórmula han cambiado Si los datos que ha ordenado contienen una o más fórmulas, los valores devueltos de dichas fórmulas pueden cambiar cuando se actualiza la hoja de cálculo. En este caso, asegúrese de que vuelve a aplicar la ordenación 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 configuración local Los criterios de ordenación varían según la configuración local. Asegúrese de que la configuración local de la Configuración regional o de la Configuración regional y de idioma del Panel de control de su equipo es correcta. Para obtener información sobre cómo cambiar la configuración local, vea el sistema de Ayuda de Windows. Especificar encabezados de columna solamente en una fila Si tiene que utilizar rótulos de varias líneas, ajuste el texto dentro de la celda. Activar o desactivar la fila de título Normalmente, lo mejor es tener una fila de título cuando se ordena una columna para facilitar la comprensión del significado de los datos. De manera predeterminada, el valor del título no se incluye en la operación de ordenación. A veces, es posible que tenga que activar o desactivar el título para que el valor del mismo se incluya o no se incluya en la operación de ordenación. Siga uno de los procedimientos siguientes:

Para excluir la primera fila de datos de la ordenación 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 continuación, active Mis datos tienen encabezados.

Para incluir la primera fila de datos en la ordenación 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 continuación, desactive Mis datos tienen encabezados.

ESQUEMATIZAR UNA LISTA DE

DATOS EN UNA HOJA DE CÁLCULO

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 número superior de símbolos de esquema (símbolos de esquema: símbolos que se utilizan para cambiar la vista de una hoja de cálculo esquematizada. Se pueden mostrar u ocultar los datos detallados presionando en el signo más, en el signo menos y en los números 1, 2, 3 o 4 que indican el nivel de esquema.) muestra datos de detalle (datos de detalle: para subtotales automáticos y esquemas de hoja de cálculo, 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 número inferior de los símbolos 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 geográficas y meses con varias filas de resumen y detalles.

Para mostrar filas para un nivel, haga clic en los símbolos 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 región.

El nivel 3 contiene las filas de detalle (sólo las filas de detalle 11 a 13 están actualmente visibles).

Para expandir o contraer datos en el esquema, haga clic en los símbolos de esquema y .

Crear un esquema de filas 1. Asegúrese de que cada columna tiene

un rótulo 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 más información sobre la ordenación, 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 fórmulas 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 función SUBTOTAL inmediatamente debajo o encima de cada grupo de filas de detalle y crea automáticamente el esquema para usted. Para obtener más información, vea Insertar subtotales en una lista de datos de una hoja de cálculo.

Insertar sus propias filas de resumen Inserte sus propias filas de

resumen con las fórmulas inmediatamente anterior o posterior a cada grupo de filas de detalle.

5. Especifique si la ubicación 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 verificación Filas resumen debajo del detalle. Para especificar una fila de resumen debajo de la fila de detalles, active la casilla de verificación 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 continuación, 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 Región 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 selección.

2. En la ficha Datos, en el grupo Esquema, haga clic en Agrupar.

Los símbolos 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 Región 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 selección la fila resumen para dicho 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, selecciónelas y, a continuación, en la ficha Datos, en el grupo Esquema, haga clic en Desagrupar. Nota También puede desagrupar secciones del esquema sin quitarlo todo. Mantenga presionada la tecla

MAYÚS mientras hace clic en o en para el grupo y, a continuación, en la ficha Datos, en el grupo Esquema, haga clic en Desagrupar. Importante Si desagrupa un esquema mientras los datos de detalle están ocultos, las filas de detalle permanecen ocultas. Para mostrar los datos, arrastre por los números 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 continuación, haga clic en Mostrar filas.

Crear un esquema de columnas 1. Asegúrese 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 más información sobre la ordenación, vea Ordenar datos en un rango o tabla.

4. Inserte sus propias columnas resumidas con fórmulas 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 fórmulas que hagan referencias a las celdas de cada una de las columnas de detalle para dicho grupo.

5. Especifique si la ubicación 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 verificación 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 verificación 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 continuación, 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

Región

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 selección.


15


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

Región

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 selección la columna resumida para dicho 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, selecciónelas y, a continuación, en la ficha Datos, en el grupo Esquema, haga clic en Desagrupar.

Nota También puede desagrupar secciones del esquema sin quitarlo todo. Mantenga presionada la tecla MAYÚS mientras hace clic en o en

para el grupo y, a continuación, en

16

la ficha Datos, en el grupo Esquema, haga clic en Desagrupar. Importante Si desagrupa un esquema mientras los datos de detalle están 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 continuación, haga clic en Mostrar columnas.

Mostrar u ocultar datos de esquemas 1. Si no ve los símbolos de esquema

, y , haga clic en el botón

de Microsoft Office , haga clic en Opciones de Excel, en la categoría Avanzadas y, a continuación, en Mostrar opciones para esta hoja, seleccione la hoja de cálculo y, a continuación, active la casilla de verificación Mostrar símbolos 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 símbolos de esquema

, haga clic en el número 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 más bajo de los símbolos 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: combinación de características de formato, como fuente, tamaño de fuente y sangría, 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 están 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 después de crearlo. Siga uno o varios de los procedimientos siguientes: Aplicar un estilo automáticamente a una columna o fila resumen 1. En la ficha Datos, en el grupo

Esquema, haga clic en Esquema.

2. Active la casilla de verificación Estilos

automáticos. 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 verificación Estilos

automáticos. 4. Haga clic en Aplicar estilos. Nota Asimismo, puede utilizar los autoformatos (autoformato: colección incorporada de formatos de celda (como tamaño de fuente, tramas y alineación) 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 símbolos de esquema



2. Utilice los símbolos de esquema , y para ocultar los datos

de detalle que no desee copiar. Para obtener más información, vea la sección, Mostrar u ocultar datos de esquemas.

3. Seleccione el rango de filas de resumen.

4. En la ficha Inicio, en el grupo Modificación, haga clic en Buscar y seleccionar y, a continuación, en Ir a.

5. Haga clic en Ir a Especial. 6. Haga clic en Sólo celdas visibles. 7. Haga clic en Aceptar y, a

continuación, 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 símbolos de esquema



2. Vea todos los datos haciendo clic en el número más alto de los símbolos de esquema .

3. Haga clic en el botón de Microsoft

Office , haga clic en Opciones de Excel, en la categoría Avanzadas y, a continuación, en la sección Mostrar opciones para esta hoja, seleccione la hoja de cálculo y desactive la casilla de verificación Mostrar símbolos de esquema si se aplica un esquema.

Quitar un esquema 1. Haga clic en la hoja de cálculo. 2. En la ficha Datos, en el grupo

Esquema, haga clic en la flecha situada junto a Desagrupar y, después, en Borrar esquema.

ms-help://MS.EXCEL.12.3082/EXCEL/content/HA10095247.htm#Show or hide outlined data



18

3. Si las filas o columnas todavía están 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 continuación, haga clic en Mostrar filas o en Mostrar columnas. Importante Si quita un esquema mientras los datos de detalle están ocultos, las columnas o filas de detalle permanecen ocultas. Para mostrar los datos, arrastre los números 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 continuación, haga clic en Mostrar filas o en Mostrar columnas.

Crear un informe de resumen con un gráfico Supongamos que desea crear un informe resumido de los datos que sólo muestra los totales acompañados de un gráfico de dichos totales. En general, puede hacer lo siguiente: 1. Crear un informe de resumen

1. Aplicar un esquema a los datos. Para obtener más información, vea las secciones, Crear un esquema de filas o Crear un esquema de columnas.

2. Oculte el detalle haciendo clic en los símbolos de esquema

, y para mostrar sólo los totales como se muestra en el siguiente ejemplo de un esquema de fila:

Para obtener más información, vea la sección, Mostrar u ocultar datos de esquemas.

2. Representar en un gráfico el informe de resumen

1. Seleccione los datos de resumen que desea representar. Por ejemplo, para representar sólo 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 gráfico. Por ejemplo, si crea el gráfico con el Asistente para gráficos, tendrá un aspecto similar al del siguiente ejemplo.

Nota Si muestra u oculta los detalles de la lista esquematizada de datos, el gráfico también se actualiza para mostrar u ocultar los datos. Para obtener más información, vea Crear un gráfico y Mostrar datos ocultos de una hoja de cálculo en un gráfico.

CONSOLIDAR DATOS EN VARIAS

HOJAS DE CÁLCULO

Para resumir y registrar resultados de hojas de cálculo independientes, puede consolidar datos de cada una de estas hojas en una hoja de cálculo maestra. Las hojas pueden estar en el mismo libro que la hoja de cálculo maestra o en otros libros. Al consolidar datos, lo que se hace es ensamblarlos de modo que sea más fácil actualizarlos y agregarlos de una forma periódica o específica.

ms-help://MS.EXCEL.12.3082/EXCEL/content/HA10095247.htm#Create an outline of rows



ms-help://MS.EXCEL.12.3082/EXCEL/content/HA10095247.htm#Create an outline of columns










19

Por ejemplo, si tiene una hoja de cálculo de cifras de gastos para cada una de las oficinas regionales, podría utilizar una consolidación para resumir estas cifras en una hoja de cálculo de gastos corporativa. Esta hoja de cálculo maestra podría contener totales de ventas y promedios, niveles de inventario actuales y los productos más vendidos de toda la organización. Para consolidar datos, utilice el comando Consolidar del grupo Herramientas de datos de la ficha Datos. Consolide por posición 1. Configure los datos que se van a

consolidar en cada una de las hojas de cálculo independientes. Asegúrese 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 rótulo en la primera fila, contiene hechos similares y no tiene filas o columnas en blanco.

Coloque cada rango en una hoja de cálculo diferente. No ponga ningún rango en la hoja de cálculo donde vaya a colocar la consolidación.

Asegúrese de que cada rango tiene el mismo diseño.

Asigne un nombre a cada rango: seleccione todo el rango y, luego, en la ficha Fórmulas, 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 cálculo maestra. Nota Asegúrese de dejar suficientes celdas a la derecha y por debajo de esta celda para los datos de consolidación. El comando Consolidar rellena el área según proceda.

3. En la ficha Datos, en el grupo Herramientas de datos, haga clic en Consolidar.

4. En el cuadro Función, haga clic en la

función de resumen (función de resumen: tipo de cálculo que combina datos de origen en un informe de tabla dinámica o una tabla de consolidación, o cuando se insertan subtotales automáticos 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 cálculo se encuentra en otro libro, haga clic en Examinar para buscar el archivo y, a continuación, haga clic en Aceptar para cerrar el cuadro de diálogo Examinar. La ruta de acceso del archivo se escribe en el cuadro Referencia seguido de un signo de exclamación.

6. Escriba el nombre que le asignó al rango y, a continuación, haga clic en Agregar. Repita este paso para cada rango.

7. Decida cómo desea actualizar la consolidación. Siga uno de los procedimientos siguientes: Para configurar la consolidación

de manera que se actualice automáticamente cuando cambien los datos de origen, active la casilla de verificación

20

Crear vínculos con los datos de origen. Importante Solamente puede activar esta casilla de verificación si la hoja de cálculo se encuentra en otro libro. Cuando active esta casilla de verificación ya no podrá cambiar las celdas y los rangos que se incluyen en la consolidación.

Para configurar la consolidación de modo que puede actualizarla manualmente cambiando las celdas y los rangos incluidos, desactive la casilla de verificación Crear vínculos con los datos de origen.

8. Deje en blanco las casillas de Usar rótulos en. Excel no copia los rótulos de fila o columna de los rangos de origen a la consolidación. Si desea rótulos para los datos consolidados, cópielos de uno de los rangos de origen o introdúzcalos manualmente.

Consolide por categorías 1. Configure los datos que se van a

consolidar en cada una de las hojas de cálculo independientes. Asegúrese 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 rótulo en la primera fila, contiene hechos similares y no tiene filas o columnas en blanco.

Coloque cada rango en una hoja de cálculo diferente. No ponga ningún rango en la hoja de cálculo donde vaya a colocar la consolidación.

Asegúrese de especificar rótulos de categoría exactamente iguales, incluidas mayúsculas y minúsculas, en todas las áreas de origen. Por ejemplo, los rótulos Prom. anual y Promedio anual son diferentes y no se consolidarán.

Asigne un nombre a cada rango: seleccione todo el rango y, luego, en la ficha Fórmulas, 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 cálculo maestra. Nota Asegúrese de dejar suficientes celdas a la derecha y por debajo de esta celda para los datos de consolidación. El comando Consolidar rellena el área según proceda.

3. En la ficha Datos, en el grupo Herramientas de datos, haga clic en Consolidar.

4. En el cuadro Función, haga clic en la

función de resumen (función de resumen: tipo de cálculo que combina datos de origen en un informe de tabla dinámica o una tabla de consolidación, o cuando se insertan subtotales automáticos 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 cálculo se encuentra en otro libro, haga clic en Examinar para

21

buscar el archivo y, a continuación, haga clic en Aceptar para cerrar el cuadro de diálogo Examinar. La ruta de acceso del archivo se escribe en el cuadro Referencia seguido de un signo de exclamación.

6. Escriba el nombre que le asignó al rango y, a continuación, haga clic en Agregar. Repita este paso para cada rango.

7. Decida cómo desea actualizar la consolidación. Siga uno de los procedimientos siguientes: Para configurar la consolidación

de manera que se actualice automáticamente cuando cambien los datos de origen, active la casilla de verificación Crear vínculos con los datos de origen. Importante Solamente puede activar esta casilla de verificación si la hoja de cálculo se encuentra en otro libro. Cuando active esta casilla de verificación ya no podrá cambiar las celdas y los rangos que se incluyen en la consolidación.

Para configurar la consolidación de modo que puede actualizarla manualmente cambiando las celdas y los rangos incluidos, desactive la casilla de verificación Crear vínculos con los datos de origen.

8. Active las casillas de verificación de Usar rótulos en que indican donde se encuentran los rótulos en los rangos de origen, que puede ser: la Fila superior, la Columna izquierda o ambas. Notas Los rótulos que no coincidan

con los de las otras áreas de origen producirán filas o columnas independientes en la consolidación.

Asegúrese de que las categorías que no desee consolidar tienen rótulos únicos que aparecen solamente en un rango de origen.

Consolide por fórmula 1. En la hoja de cálculo maestra, copie o

escriba los rótulos de columna o fila que desee para los datos de consolidación.

2. Haga clic en la celda en que desea incluir los datos de consolidación.

3. Escriba una fórmula que incluya una referencia de celda a las celdas de origen de cada hoja de cálculo 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 están en celdas diferentes de otras hoja de cálculo Escriba una fórmula con

referencias de celda a las otras hojas de cálculo, una por cada hoja de cálculo independiente. Por ejemplo, para consolidar datos de hojas de cálculo 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 cálculo maestra, tendría que escribir lo siguiente:

Sugerencia Para especificar una referencia de celda como Ventas3!B4 en una fórmula sin escribir, escriba la fórmula hasta el punto en el que necesite la referencia, haga clic en la etiqueta de la hoja de cálculo y, a continuación, haga clic en la celda.

22

Si los datos que se van a consolidar están en las mismas celdas de otras hojas de cálculo Escriba una fórmula con una

referencia 3D que utilice una referencia a un rango de nombres de hojas de cálculo. Por ejemplo, para consolidar datos en las celdas A2 desde Ventas hasta Marketing inclusive, en la celda A2 de la hoja de cálculo maestra tendría que escribir lo siguiente:

Nota Si el libro está configurado para calcular fórmulas automáticamente, una consolidación por fórmula siempre se actualiza automáticamente cuando cambian los datos de las hojas de cálculo independientes.

Filtrar una Base de Datos:

Filtrar una lista no es ni más ni menos que de todos los registros almacenados en la tabla, seleccionar aquellos que correspondan a algún criterio, es una manera fácil y rápida 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 pestaña Datos marcando o desmarcando el botón 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 gráficos e imprimir el subconjunto

de datos filtrados sin tener que volver a

organizarlo ni moverlo.

También puede filtrar por más de una

columna. Los filtros son aditivos, lo que

significa que cada filtro adicional se basa en el

filtro actual y además 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 sólo marcamos Moreno, Excel filtrará todos los registros que tengan Moreno en el 1er apellido y las demás filas 'desaparecerán' de la lista.

Otra opción, es usar los Filtros de texto, donde se despliegan una serie de opciones:

En cualquier opción, accedemos a una ventana

donde podemos elegir dos condiciones de

filtro de texto, y exigir que se cumpla una

condición o las dos. Excel evaluará la condición

elegida con el texto que escribamos, y si se

cumple, mostrará la fila. Usaremos el carácter

? para determinar que en esa posición habrá

un carácter, 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 mostrarán los registros cuyo 1er Apellido tenga una o en el segundo carácter 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 opción (Seleccionar Todo), reaparecerán todos los registros de la lista. También podemos quitar el filtro pulsando

en Borrar filtro en la pestaña Datos.

Filtros Avanzados:

Si se quiere filtrar registros de la lista con una condición más compleja, utilizaremos el cuadro de diálogo Filtro avanzado. Previamente deberemos tener en la hoja de cálculo, unas filas donde indicaremos los criterios del filtrado.

Para abrir el cuadro de diálogo Filtro

avanzado, pulse en en la sección 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).

También podemos optar por guardar el resultado del filtrado en otro lugar, seleccionando la opción Copiar a otro lugar, en este caso rellenaremos el campo Copiar a:

con el rango de celdas que recibirán el resultado del filtrado.

Si marcamos la casilla Sólo registros únicos, las repeticiones de registros (filas con exactamente los mismos valores) desaparecerán.

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 ordenación simple, por ejemplo ordenar la lista anterior por el primer apellido, debemos posicionarnos en la columna del primer apellido, después podemos acceder a la pestaña Datos y pulsar sobre Ordenar... y escoger el criterio de

ordenación o bien pulsar sobre uno de los botones

de la sección Ordenar y filtrar para que la ordenación sea ascendente o descendente respectivamente. Estos botones también están disponibles al desplegar la lista que aparece pulsando la pestaña junto al

encabezado de la columna.

Para ordenar la lista por más de un criterio de ordenación, por ejemplo ordenar la lista por el primer apellido más la fecha de nacimiento, en la pestaña Datos, pulsamos sobre Ordenar... nos aparece el cuadro de diálogo Ordenar donde podemos seleccionar los campos por los que queremos ordenar (pulsando Agregar Nivel para añadir un campo), si ordenamos según el valor de la celda, o por su color o icono (en Ordenar Según), y el Criterio de ordenación, donde elegimos si el orden es alfabético (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 días de la semana o meses, la ordenación alfabética no sería correcta, y podemos escoger una lista donde se guarden los valores posibles, ordenados de la forma que creamos conveniente, y así el criterio de ordenación seguirá el mismo patrón.

Seleccionando un nivel, y pulsando las flechas hacia arriba o hacia abajo, aumentamos o disminuimos la prioridad de ordenación de este nivel. Los datos se ordenarán, primero, por el primer nivel de la lista, y sucesivamente por los demás niveles en orden descendente.

25

En la parte superior derecha tenemos un botón Opciones..., este botón sirve para abrir el cuadro Opciones de ordenación dónde podremos especificar más opciones según el criterio de la ordenación.

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 más el riesgo de introducir erróneamente los datos al introducirlos manualmente.

- Utilizar algunas de las herramientas disponibles en Excel para importar datos.

Para importar datos externos a Excel disponemos básicamente de dos opciones:

- Utilizar el portapapeles de Windows, es decir, copiar los datos de la aplicación externa y pegarlos en una hoja de Excel.

- Importar datos de otro archivo que no tiene que ser necesariamente del formato Excel.

La primera opción es la más directa, pero tiene el contrapunto de ser más laboriosa y tediosa.

La segunda opción es más rápida, pero pueden surgir problemas de compatibilidad dependiendo del formato del archivo a importar.

El problema fundamental de la importación de datos externos, hemos dicho que es debido al formato de los propios archivos, esto quiere decir que cada aplicación 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 dónde están las fórmulas, qué formato estético tiene el documento, etc.

Además de esto, al importar datos de una aplicación externa debemos tener en cuenta que pueden surgir los siguientes fallos:

- Algunas de las fórmulas no ajustan correctamente.

- El formato no se ajusta al original.

- Las fórmulas y funciones que no entiende no las copia.

Pese a todos estos contratiempos, siempre es mejor intentar realizar una importación y después 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 más adelante.

26

Pero para importar archivos de texto con el asistente podemos hacerlo de dos formas distintas:

- Podemos acceder a la pestaña Datos y seleccionar uno de los tipos de orígenes de datos que podemos encontrar en esta sección:

O acceder mediante el Botón Office - Abrir y donde pone Tipo de datos seleccionar Todos los archivos (*.*).

A continuación en ambos casos se sigue el mismo procedimiento.

Nos aparece un cuadro de diálogo 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 dónde acaba.

Podemos indicar a partir de qué fila queremos importar por si queremos descartar títulos, por ejemplo.

Y también 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 Tabulación. En la imagen podemos ver que lo que hay en cada línea lo incluye en un sólo campo, esto es debido a que no hay signos de tabulación en el texto por lo que todo lo escrito lo pone en un sólo campo.

Si se hubiera seleccionado Espacio, el carácter espacio en blanco delimitaría los campos es decir que nos aparecerían varias columnas (varios campos.

Cuando utilizamos esta opción (separar con espacios en blanco), es conveniente activar Considerar separadores consecutivos como uno solo, esta opción hace que si por ejemplo se encuentran dos o más caracteres separadores juntos, los considera como uno sólo. En el ejemplo anterior, vamos a suponer que en la primera línea 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 más adecuado.

Para ello sólo tenemos que hacer clic en la columna para seleccionarla (aparece con fondo negro), y seleccionar el formato en la sección Formato de los datos en columnas. Esta sección también nos sirve si no queremos importar la columna en nuestra hoja (No importar columna (saltar)).

En el botón Avanzadas podemos completar ciertas características para los números como los separadores de decimales y millares y la posición del signo en los negativos.

Una vez se haya completado o comprobado la definición, 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 botón <Atrás.

Aparece un cuadro de diálogo preguntando dónde deseamos Importar los datos.

Marcamos Hoja de cálculo existente y la celda =$A$1, para que los importe a partir de la celda A1 o bien podemos importar los datos en una nueva hoja dentro del libro.

Pulsamos sobre Aceptar y aparecerán los datos importados en la hoja.

Importar Datos a Excel: Desde esta sección en la pestaña Datos podremos acceder a las opciones de Conexión con archivos externos de los cuales hayamos importado algún dato.

Haciendo clic en la opción Propiedades veremos este cuadro de diálogo donde encontraremos propiedades muy útiles:

28

En la sección Control de actualización podemos hacer que Excel actualice automáticamente los datos insertados en la hoja de cálculo, 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 quedarán reflejados en nuestra hoja de cálculo. Esto es posible porque Excel almacena en el libro de trabajo la definición 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 opción 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 sección Definición de consulta tenemos activada por defecto la casilla Guardar definición de consulta para que Excel guarde esta definición 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 serán guardados como un rango estático de datos, digamos que se perderá el enlace con el origen de la importación.

Por último, en la sección Diseño y formato de datos podemos indicarle a Excel qué debe de hacer cuando actualiza los datos.

Volviendo a la sección Conexiones, también nos permite actualizar manualmente los datos importados de un archivo externo.

Para ello, haz clic en la opción Actualizar que se encuentra en el desplegable Actualizar

todo. Si escoges la opción Actualizar todo, todos los orígenes de datos se actualizarán.

Importar datos de Word a Excel.

A veces puede ser útil importar una tabla de Word a Excel si se desea agregar algún tipo de fórmula 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 pestaña Inicio y haciendo clic en el botón Copiar.

- Ahora en Excel seleccionamos la celda donde queremos colocar la tabla.

- Y pulsamos Ctrl + V o desde la pestaña Inicio y haciendo clic en el botón Pegar. Es posible que nos advierta de que el texto seleccionado es más pequeño que la hoja, pulsamos sobre Aceptar y nos pegará la tabla con el mismo formato que la teníamos 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 además de lo visto anteriormente podemos hacer que los datos queden vinculados, es decir que además de copiar los datos de Excel en un documento de Word si realizamos algún cambio en la hoja de Excel, este cambio queda reflejado en el documento de Word.

Para realizar este proceso:

- Seleccionamos la información a copiar de la hoja de Excel.

29

-Copiamos los datos seleccionados pulsando Ctrl + C o desde la pestaña Inicio y haciendo clic en el botón Copiar.

- Nos vamos a Word y pulsamos Ctrl + V o desde la pestaña Inicio y haciendo clic en el botón Pegar.

Después 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 tenía en la hoja de Excel.

Si lo queremos insertar en una tabla podemos escoger la opción Hacer coincidir estilo de tabla de destino.

Y si queremos vincular los datos de Excel para que se actualicen en Word podemos escoger la opción Conservar formato de origen y vincular a Excel.

Importar Datos de Access:

También disponemos de un botón que nos permitirá importar datos directamente de consultas y tablas de Microsoft Access.

Pulsando Desde Access, aparecerá el diálogo 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 cómo ver los datos en el libro, entre las opciones que ya conocemos, o si preferimos incluirlos en la hoja actual, indicando su posición, o en una nueva hoja del libro.

Pulsando en Propiedades, podremos modificar las propiedades de la conexión, como la Descripción 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 página web.

30

Para elegir las tablas, basta con dar clic sobre las flechas.

En el botón 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 mayoría de las webs están estructuradas con tablas.

Una vez finalizada la elección, 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 actualización de la conexión.

Otra forma rápida de hacer esto, es directamente seleccionar la tabla en nuestro navegador, copiarla con Ctrl + C y pegarla en la hoja de Excel. Después podremos modificar el formato y si se quiere que haya actualización.

Importar de otros programas: La mayoría de tablas que nos encontremos en otros documentos, y podamos seleccionar y copiar, Excel nos permitirá pegarlas en una hoja de cálculo. En algunos nos permitirá crear conexiones de actualización y en otros no. Y otros programas nos permitirán 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 opción Exportar a Excel, que volcará los datos de la tabla en una nueva hoja de cálculo.

Tablas Dinámicas:

Una tabla dinámica consiste en el resumen de un conjunto de datos, atendiendo a varios criterios de agrupación, representado como una tabla de doble entrada que nos facilita la interpretación de dichos datos. Es dinámica porque nos permite ir obteniendo diferentes totales, filtrando datos, cambiando la presentación de los datos, visualizando o no los datos origen, etc...

Suponga que tiene una colección de datos de los artículos de un almacén con el número de referencia y el mes de compra, además sabemos la cantidad comprada y el importe del mismo.

Vamos a crear una tabla dinámica a partir de estos datos para poder examinar mejor las ventas de cada artículo en cada mes.

Para ello vamos a la pestaña Insertar y hacemos clic en Tabla dinámica (también podemos desplegar el menú haciendo clic en la flecha al pie del botón para crear un gráfico dinámico).

31

Aparece el cuadro de diálogo de creación de tablas dinámicas. Desde aquí se puede indicar el lugar donde se encuentran los datos que se quieren analizar y el lugar de ubicación.

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 cálculo nueva.

Se puede crear una conexión con otra aplicación para obtener los datos desde otra fuente diferente a Excel.

En el caso de seleccionar la opción 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 dinámica.

Con esta herramienta podríamos construir una tabla dinámica 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 deberíamos arrastrar el

campo REF a , el campo

MES a y finalmente el

campo IMPORTE a la sección .

32

Al realizar la tabla dinámica 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 más fácil analizar los resultados, veamos:

Al tener creada la tabla dinámica nos aparece la pestaña Opciones:

El panel lateral seguirá pudiéndose utilizar, así que en cualquier momento se puede quitar un campo de una zona arrastrándola fuera.

Con esto vemos que en un segundo podemos variar la estructura de la tabla y obtener otros resultados.

Gráficos con Tablas Dinámicas:

Para crear una gráfica a partir de una tabla dinámica, se debe dar clic en el botón grafico dinámico de la pestaña de opciones.

Para cambiar el formato del gráfico a otro tipo de gráfico que nos agrade más o nos convenga más según los datos que tenemos.

Al pulsar este botón se abrirá el cuadro de diálogo de Insertar gráfico, allí deberemos escoger el gráfico que más nos convenga.

Luego, la mecánica a seguir para trabajar con el gráfico es la misma que se vio en el tema de gráficos.

Vincular Hojas de Cálculo:

El concepto de enlazar en Excel es el hecho de utilizar fórmulas 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 términos:

- el libro de trabajo dependiente: es el que contiene las fórmulas.

33

- el libro de trabajo fuente: es el que contiene los datos.

Para crear un libro de trabajo dependiente debemos crear fórmulas de referencias externas, es decir fórmulas que hacen referencia a datos que se encuentran en una hoja externa a la que está la fórmula.

Para crear fórmulas 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 fórmula.

- Introducir la fórmula 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 fórmula y pulse Intro.

Las referencias al libro externo las gestiona automáticamente Excel, incluso si cambiamos el nombre del archivo donde están los datos desde Archivo → Guardar como... las referencias también se cambiarían.

Si en el libro de trabajo dependiente queremos ver todos los vínculos a las páginas fuente podemos hacerlo accediendo a la pestaña Datos y haciendo clic en el botón Editar vínculos... Nos aparece el cuadro de diálogo Modificar vínculos donde podemos hacer modificaciones sobre los vínculos.

Actualizar valores: Actualiza la lista con los vínculos.

Cambiar origen: Podemos modificar el origen del vínculo seleccionado.

Abrir origen: Abre el libro del vínculo seleccionado.

Romper vínculo: Quita los vínculos de las fórmulas 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 relación 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 consolidación de hojas de trabajo puede ser una compañía que dispone de varios departamentos, cada uno de ellos con un presupuesto, para conocer el presupuesto total de la empresa, crearíamos una hoja dependiente y los libros fuentes serían los libros de los departamentos con sus prepuestos.

34

Si pulsamos sobre la pestaña Datos y pulsando el botón Consolidar... nos aparece el cuadro de diálogo Consolidar como vemos en la imagen donde podemos escoger:

- la Función: a utilizar,

-en Referencia: vamos seleccionando las celdas de uno de los libros fuentes,

- pulsamos sobre Agregar para añadir 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 fórmula.

Si marcamos la casilla Crear vínculos con los datos de origen, los datos de los libros fuente se incluirán en el libro consolidado a modo de esquema, y si cambiamos un valor en el origen, ese cambio quedará automáticamente reflejado en el libro consolidado.

Asignar Contraseñas: En Microsoft Excel se puede usar contraseñas para impedir que otras personas abran o modifiquen los archivos.

Para permitir que sólo las personas

autorizadas observen o modifiquen los datos,

puede proteger el libro completo con una

contraseña, veamos:

1. Haga clic en el botón de Microsoft Office

y, a continuación, haga clic en Guardar

como.

2. Haga clic en Herramientas y, a

continuación, en Opciones generales.

3. Siga uno o ambos procedimientos:

Si desea que los revisores escriban una

contraseña para poder ver el libro,

especifique la contraseña en el cuadro

Contraseña de apertura.

Si desea que los revisores escriban una

contraseña para poder guardar los

cambios en el libro, especifique la

contraseña en el cuadro Contraseña de

escritura.

Ambas contraseñas Puede asignar

ambas contraseñas: una para tener

acceso al archivo y otra para

proporcionar a revisores específicos

permiso para modificar el contenido.

Asegúrese de que cada contraseña es

diferente.

4. Si no desea que los revisores de

contenido modifiquen el archivo por

error, active la casilla de verificación

Recomendado sólo lectura. Al abrir el

archivo, se preguntará a los revisores si

desean abrir el archivo como de sólo

lectura.

35

5. Haga clic en Aceptar.

6. Cuando se le indique, vuelva a escribir las

contraseñas para confirmarlas y, a

continuación, 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: Además de la protección mediante contraseñas 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 están 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 cálculo.

Para desbloquear las celdas que queremos variar en algún momento sigue los siguientes pasos:

Seleccionar el rango de celdas que queremos desbloquear para poder realizar variaciones.

Seleccionar la pestaña Inicio.

Hacer clic sobre la flecha que se encuentra bajo la sección Fuente.

Hacer clic sobre la pestaña Proteger.

Aparecerá la ficha de la derecha:

Desactivar la casilla Bloqueada y Hacer clic sobre el botón Aceptar.

Si se activa la casilla Oculta, lo que se pretende es que la fórmula o el valor de la celda no se pueda visualizar en la barra de fórmulas.

Las operaciones de la ficha Proteger no tienen efecto si no protegemos la hoja de cálculo, por lo tanto a continuación tendremos que realizar los siguientes pasos:

Seleccionar la pestaña Revisar.

Hacer clic sobre el botón Proteger hoja que se encuentra en la sección Cambios Aparecerá el cuadro de diálogo 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 cálculo para que no tenga efecto la protección para la modificación seleccionada y desactivarla para tener en cuenta la protección.

Si queremos asignar una contraseña para que solamente pueda desproteger la hoja la persona que sepa la contraseña, escribirla en el recuadro Contraseña.

Hacer clic sobre el botón Aceptar.

Si hemos puesto contraseña nos pedirá confirmación de contraseña, por lo tanto tendremos que volver a escribirla y hacer clic sobre el botón Aceptar.

A partir de ahora la hoja activa se encuentra protegida, por lo que no se podrán modificar aquellas celdas bloqueadas en un principio.

Si queremos desproteger la hoja, volveremos a realizar los mismos pasos que en la protección, es decir:

Seleccionar la pestaña Revisar.

Hacer clic sobre el botón Desproteger hoja que se encuentra en la sección Cambios.

Si habíamos asignado una contraseña nos la pedirá, por lo que tendremos que escribirla y hacer clic sobre el botón Aceptar. Si no había contraseña asignada, automáticamente la desprotege.

Si la macro a relacionar con el botón ya está creada, simplemente al insertar el botón, seleccione el nombre de la macro del cuadro de dialogo Asignar una Macro y de clic en el botón Aceptar.

excel 2007 modulol ii

Documents