Manual Excel 2007 Nivel Avanzo v.1

(1)

Curso Microsoft Excel

2007

(2)

Contenido

MÓDULO 1: Formato de Datos y Contenido ... 15

Formato de Datos y Contenido ... 17

Los códigos de formato que puede utilizar ... 17

Laboratorio ... 23

Formato Condicional ... 25

Laboratorio ... 30

Formato Avanzado de Gráficos SmartArt ... 32

Formato Avanzado de Imágenes ... 35

Laboratorio ... 40

MÓDULO 2: Organización de Datos... 43

Validación de Datos ... 45

Formularios ... 48

Laboratorio ... 52

Consolidación de Datos ... 55

Laboratorio ... 59

MÓDULO 3: Trabajando con Bases de Datos ... 61

Importar Datos Desde un Origen Externo ... 63

Laboratorio ... 68 Ordenación ... 70 Laboratorio ... 74 Subtotales ... 76 Filtros Avanzados ... 82 Laboratorio ... 86

Funciones de Bases de Datos ... 88

Funciones de Búsqueda ... 90

Laboratorio ... 93

MÓDULO 4: Tablas Dinámicas ... 95

Creación de Tablas Dinámicas ... 97

Modificación de Tablas Dinámicas ... 99

Creación de Gráficos Dinámicos ... 101

Creación de Tablas Dinámicas con Datos Externos ... 102

(3)

MÓDULO 5: Fórmulas y Funciones ... 107

Funciones: Lógicas ... 109

Funciones Lógicas Anidadas ... 120

Laboratorio ... 126

MÓDULO 6: Auditoría de Fórmulas ... 127

Precedentes y Dependientes ... 129

Comprobación y Rastreo de fórmulas... 131

Mostrar Fórmulas ... 134

Ventana de Inspección ... 135

Evaluar Fórmulas ... 137

Laboratorio ... 141

MÓDULO 7: Colaboración ... 143

Propiedades de los Libros de Trabajo ... 145

Compartir Libros de Trabajo ... 147

Control de Cambios ... 149

Laboratorio ... 153

Administración de Comentarios ... 155

Laboratorio ... 159

Protección ... 161

Autenticación de Libros de Trabajo ... 163

Laboratorio ... 167

MÓDULO 8: Análisis de Datos ... 169

Tabla de Datos ... 171 Laboratorio ... 178 Escenarios... 179 Laboratorio ... 184 Buscar Objetivo ... 186 Laboratorio ... 190

Buscar Soluciones con Solver ... 192

Laboratorio ... 198

Análisis de Datos con Estadística Descriptiva ... 200

Laboratorio ... 205

MÓDULO 9: Controles de Formulario y Macros... 207

(4)

Creación y modificación de Macros ... 211

El Editor de Visual Basic... 214

Laboratorio ... 219

Controles de Formulario ... 221

Gráficos con Controles ... 225

Agregar Botones de Macros ... 229

(5)

(6)

(7)

(8)

(9)

(10)

(11)

(12)

(13)

(14)

(15)

(16)

(17)

Formato de Datos y Contenido

Para crear un formato de número personalizado, seleccione las celdas a las que desea dar formato. En la ficha Inicio en el grupo Número inicie el cuadro de diálogo Formato de Celdas, en la pestaña correspondiente a Número. En la lista Categoría seleccione Personalizada y establezca los formatos integrados para que sean similares a los que usted desea.

En el cuadro Tipo, modifique los códigos de formato de número para crear el formato que desee. Puede especificar hasta cuatro secciones de códigos de formato. Los códigos de formato, separados por punto y coma, definen los formatos para números positivos, números negativos, valores cero y texto, por ese orden. Si usted especifica sólo dos secciones, la primera se utilizará para números positivos y ceros, y la segunda para números negativos. Si especifica sólo una sección, se utilizará para todos los números. Si omite una sección, incluya el punto y coma final de esa sección.

Los códigos de formato que puede utilizarson:

Texto y espaciado

Para ver el texto y los números de una celda, encierre los caracteres de texto entre comillas (" ") o ponga delante de un solo carácter una barra invertida (\). Por ejemplo, escriba el formato #" Ganancia"; #" Pérdida" para mostrar 1500 Ganancia o -1500

(18)

Pérdida. Los siguientes caracteres se muestran sin utilizar comillas: $ - + / ( ) : ! ^ & ' (comilla simple izquierda) ' (comilla simple derecha) ~ { } = < > y el espacio.

Si se incluye, la sección de texto es siempre la última en el formato de número. Incluya el carácter ( @ ) en la sección en que desee mostrar el texto escrito en la celda. Si se omite el carácter @ en la sección de texto, no se verá el texto que se haya escrito. Si desea ver siempre caracteres de texto específicos con el texto escrito, encierre el texto adicional entre comillas dobles (" ").

Si el formato no incluye una sección de texto, el texto que escriba no se verá afectado por el formato.

Para crear un espacio con el ancho de un carácter en un formato de número, incluya un subrayado (_) seguido del carácter.

Para repetir el carácter siguiente en el formato y llenar el ancho de la columna, incluya un asterisco (*) en el formato de número.

Posiciones decimales, colores y condiciones

Para dar formato a las fracciones o los números con decimales, incluya los siguientes dígitos marcadores en una sección. Si un número tiene más dígitos a la derecha del separador decimal que marcadores en el formato, se redondeará para que tenga tantos decimales como marcadores. Si hay más dígitos a la izquierda del separador decimal que marcadores, se mostrarán los dígitos adicionales. Si el formato contiene solamente signos de número (#) a la izquierda del separador decimal, los números menores que la unidad comenzarán por el separador.

o # muestra únicamente los dígitos significativos y no muestra los ceros sin valor.

o 0 (cero) muestra los ceros sin valor si un número tiene menos dígitos que ceros en el formato.

o ? agrega los espacios de los ceros sin valor a cada lado del separador decimal, para alinear los decimales con formato de fuente de ancho fijo, como Courier New. También se puede utilizar ? para las fracciones que tengan un número de dígitos variable.

Para definir el color de una sección del formato, escriba en la sección el nombre de uno de los siguientes ocho colores entre corchetes. El color debe ser el primer elemento de la sección.

[Negro] [Azul] [Aguamarina] [Verde] [Magenta] [Rojo] [Blanco] [Amarillo]

(19)

Para definir los formatos de número que se aplicarán únicamente si coinciden con las condiciones que se hayan especificado, ponga la condición entre corchetes. La condición consta de un operador de comparación y un valor.

Moneda, porcentajes y notación científica

Los formatos personalizados se guardan con el libro. Para que Microsoft utilice siempre un símbolo de moneda específico, cambie el símbolo de moneda seleccionado en Configuración regional en el Panel de control antes de iniciar Excel. Para mostrar los números como un porcentaje, incluya el signo de porcentaje (%) en el formato de número. Por ejemplo, un número como 0,08 aparecerá como 8%; 2,8 aparecerá como 280%.

Para mostrar los números en formato científico, utilice los códigos exponenciales "E-", "E+"E-", "e-" o "e+" en una sección. Si un formato contiene un cero (0) o el signo de número (# ) a la derecha de un código exponencial, Excel muestra el número en formato científico e inserta una "E" o "e". El número de ceros o de signos a la derecha de un código determina el número de dígitos del exponente. "E-" o "e-" pone un signo menos en los exponentes negativos. "E+" o "e+" pone un signo menos en los exponentes negativos y un signo más en los positivos.

Fechas y horas

Si utiliza una "m" inmediatamente detrás del código "h" o "hh", o bien inmediatamente delante del código "ss", Microsoft Excel mostrará los minutos en lugar del mes.

Para mostrar Use este código

Los meses como 1-12 m

Los meses como 01-12 mm

Los meses como ene-dic mmm

Los meses como enero-diciembre mmmm Los meses como la inicial de cada mes mmmmm

Los días como 1-31 d

Los días como 01-31 dd

Los días como dom-sáb ddd

Los días como domingo-sábado dddd

Los años como 00-99 yy

Los años como 1900-9999 yyyy

(20)

Las horas como 0-23 H

Las horas como 00-23 hh

Los minutos como 0-59 m

Los minutos como 00-59 mm

Los segundos como 0-59 s

Los segundos como 00-59 ss

Las horas como 4 a.m. h a.m./p.m.

La hora como 4:36 p.m. h:mm a.m./p.m.

La hora como 4:36:03 p. h:mm:ss a/p

El tiempo transcurrido en horas; por ejemplo, 25:02 [h]:mm El tiempo transcurrido en minutos; por ejemplo, 63:46 [mm]:ss

El tiempo transcurrido en segundos [ss]

Fracciones de segundo h:mm:ss.00

Si el formato contiene la indicación a.m. o p.m., la hora se basará en el formato de 12 horas, donde "a.m." o "a" indica las horas desde la medianoche hasta el mediodía y "p.m." o "p" indica las horas desde el mediodía hasta la medianoche. En caso contrario, el reloj se basará en el formato de 24 horas. La letra "m" o las letras "mm" deben aparecer inmediatamente detrás del código "h" o "hh", o bien inmediatamente delante del código "ss"; de lo contrario, Microsoft Excel mostrará el mes en lugar de mostrar los minutos.

(21)

(22)

Creación y Modificación de Formatos

PRACTICA

(23)

Laboratorio MÓDULO 1. FORMATO DE DATOS Y CONTENIDO. Ejercicio 1. Creación y Modificación de Formatos

En este ejercicio creará un formato personalizado de celdas.

Tareas Guía para completar la tarea

1. Abra el archivo de

Excel llamado formato personalizado.

2. Para la celda D5,

donde se captura la fecha, cree un formato de número

personalizado para que al ser introducida se muestre como sigue: “Mérida, Yucatán, 01 de enero de 2007”.

 Seleccione la celda D5.

 En la ficha Inicio en el grupo Número haga clic en Iniciar el cuadro de diálogo Formato de celdas: Número.

 Aparecerá el cuadro de diálogo de formato de celdas, haga clic en caso de ser necesario en la ficha número.

 En el cuadro Categorías haga clic en Personalizada.

 En el cuadro de texto Tipo teclee: "Mérida, Yucatán, "d" de "mmmm" de "aaaa

 Presione Aceptar, el formato creado se mostrará.

donde se captura el código de cliente, cree un formato de número personalizado para que al ser introducido el número de cliente se muestre como sigue: “C – 0002”.

 En el cuadro Categorías haga clic en Personalizada.  En el cuadro de texto Tipo teclee: “C” - 0000

 Presione Aceptar, el formato creado se mostrará.

donde se captura el teléfono del cliente, cree un formato de número personalizado para que al ser

introducido se muestre como sigue:

“(999)911-81-31”.

 En el cuadro Categorías haga clic en Personalizada.  En el cuadro de texto Tipo teclee: (###)### - ## - ##  Presione Aceptar, el formato creado se mostrará.

que captura el status, cree un formato personalizado para que al ser introducido muestre: 1 muestre “Vigente”, 2 “Inactivo”, cualquier otro número “No registrado”.

 En el cuadro Categorías haga clic en Personalizada.  En el cuadro de texto Tipo teclee:

[verde][=1]"Vigente";[Rojo][=2]"Inactivo";[Azul]"No registrado"  Presione Aceptar, el formato creado se mostrará.

(24)

6. Guarde los cambios realizados con el nombre Mod1 Lab1

 Haga clic en el botón de Microsoft Office (Archivo), despliegue y de clic en Guardar Como… El cuadro de diálogo Guardar como… aparecerá.

 Cree una carpeta en Mis documentos con el nombre Prácticas, donde guardará todos los laboratorios que hará durante el curso.

 En el cuadro Nombre de archivo escriba Mod1 Lab1.

 En el cuadro Guardar como tipo verifique que diga Libro de Excel.  De clic en el botón Guardar.

(25)

Formato Condicional

Módulo 1

El formato condicional facilita el proceso de resaltar celdas o rangos de celdas interesantes, de destacar valores inusuales y de ver datos empleando barras de datos, escalas de colores y conjuntos de iconos. Un formato condicional cambia el aspecto de un rango de celdas en función de una condición (o criterio).

Cuando cree un formato condicional, puede hacer referencia a otras celdas de una hoja de cálculo, pero no puede realizar referencias externas a otro libro.

Para aplicar un formato condicional seleccione un rango de celdas o asegúrese de que la celda activa está en una tabla o en un informe de tabla dinámica. En la ficha Inicio, en el grupo Estilos, haga clic en Formato condicional y, después, en el formato condicional que elija.

Entre las opciones de formato condicional encuentra:

Resaltar reglas de celdas. Se usa cuando desee realizar un formato condicional basado en comparaciones como Es mayor que, Es menor que, Entre, Es igual, Una Fecha, Texto que contiene, Duplicar valores. Cada uno de los anteriores le permite indicar el criterio de comparación y aplicar el formato preferido.

Reglas superiores e inferiores. Se usa para encontrar las 10 mejores, 10% mejores, 10 peores, 10% peores, Por encima del promedio, Por debajo del promedio. Aún cuando el nombre indica que son los 10 primeros valores o 10 últimos, es posible cambiar este parámetro para ajustarlo a la cantidad de valores superiores o inferiores que queremos visualizar con el formato condicional correspondiente. Al hacer clic

(26)

sobre la opción, aparece un cuadro de diálogo dónde indicamos el número de valores y el formato a aplicar.

Barras de datos. Una barra de datos le ayuda a ver el valor de una celda con relación a las demás. La longitud de la barra de datos representa el valor de la celda. Una barra más grande representa un valor más alto y una barra más corta representa un valor más bajo. Las barras de datos son útiles para encontrar números más altos y más bajos especialmente con grandes cantidades de datos, como las mayores y menores ventas de juguetes en un informe de ventas. Al desplegar el menú mueva el Mouse por los estilos que se ofrecen para ver una vista previa y elegir el apropiado. Escalas de color. Las escalas de colores son guías visuales que ayudan a comprender la variación y la distribución de datos. Una escala de dos colores permite comparar un rango de celdas utilizando una gradación de dos colores. El tono de color representa los valores superiores o inferiores. Al desplegar el menú mueva el Mouse por los estilos que se ofrecen para ver una vista previa y elegir el apropiado. Conjuntos de iconos. Utilice un conjunto de iconos para comentar y clasificar datos de tres a cinco categorías separadas por un valor de umbral. Cada icono representa un rango de valores. Al desplegar el menú mueva el Mouse por los estilos que se ofrecen para ver una vista previa y elegir el apropiado.

Para aplicar un formato condicional rápido seleccione el estilo que desea y al desplegarse el menú correspondiente mueva el Mouse por los estilos que se ofrecen para ver cuál es el apropiado y seleccionarlo.

Para aplicar un formato avanzado, en la ficha Inicio, en el grupo Estilos, haga clic en Formato condicional y, después, en Administrar reglas. Se mostrará el cuadro de diálogo Administrador de reglas de formato condicionales.

Para agregar un formato condicional, haga clic en Nueva regla. Aparecerá el cuadro de diálogo Nueva regla de formato. En el cuadro de dialogo seleccione el tipo de regla a aplicar y posteriormente la descripción de la regla. Esta descripción cambia de acuerdo al tipo de regla elegido:

 según sus valores, le pedirá el estilo de formato (escala de colores, barras de datos, conjunto de iconos), valores mínimos, máximos y colores a utilizar.

 Únicamente a las celdas que contengan. Permite elegir si utilizará valores, texto, fechas, etc. y utilizar un criterio de comparación, así como el formato a aplicar.

 Valores con rango inferior o superior. Permite indica si el rango a utilizar es inferior o superior y el número de elementos, así como si es porcentaje y el formato a utilizar.

 Valores por encima o por debajo del promedio. Permite indicar el tipo de rango y el formato.

(27)

 Fórmula para determinar el formato condicional.

Se pueden agregar las reglas que considere necesarias, siempre teniendo en cuenta el orden o prioridad.

Para cambiar un formato condicional, seleccione antes el rango donde está la regla a editar, y en el Administrador de reglas seleccione la regla y, a continuación, haga clic en Editar regla. Aparecerá el cuadro de diálogo Editar regla de formato. El cuadro de diálogo es similar al de Crear nueva regla, le permite elegir el tipo de regla y su descripción.

Para borrar formatos condicionales, en la ficha Inicio, en el grupo Estilos, haga clic en la flecha situada junto a Formato condicional y, después, en Borrar reglas. Si desea borrar todos los formatos haga clic en Toda la hoja. Para borrar solamente las reglas de un rango, seleccione el rango y haga clic en Celdas seleccionadas, o bien Esta tabla o Esta tabla dinámica.

(28)

DEMOSTRACI

Ó

N

OBJETIVO:

Crear y aplicar formato condicional a celdas de

acuerdo a condiciones establecidas.

DEMOSTRACI DEMOSTRACIÓÓNN

(29)

Formato condicional

PRACTICA

(30)

Laboratorio MÓDULO 1. FORMATO DE DATOS Y CONTENIDO. Ejercicio 2. Uso del Formato Condicional.

En este ejercicio creará formatos condicionales para determinados criterios.

Excel llamado formato condicional.

2. Aplique un formato

condicional para las celdas

correspondientes a la columna Cancelados de tal forma que si el número es igual o mayor a la tercera parte de los Vendidos se muestre en Rojo y Negritas.

 Seleccione la celda C8.

 En la ficha Inicio en el grupo Estilos haga clic en Formato condicional y a continuación en Nueva regla.

 Aparecerá el cuadro de diálogo Nueva regla de formato, en el cuadro Seleccionar un tipo de regla haga clic en Utilice una fórmula que determine las celdas para aplicar formato.

 En el cuadro Dar formato a los valores donde esta fórmula sea verdadera escriba =C8>=B8/3.

 Haga clic en el botón Formato. El cuadro de diálogo Formato de celdas aparecerá.

 En la ficha Fuente, elija Estilo Negrita, Color Rojo. Haga clic en Aceptar para regresar al cuadro Nueva regla de formato.  Verifique el formato en el cuadro de Vista Previa y haga clic en

Aceptar. El formato será aplicado a la celda.

 Seleccione la celda C8 y copie el formato al rango C9:C13. En la ficha Inicio en el grupo Portapapeles haga clic en Copiar formato.

 Aplíquelo sobre el rango C9:C13.

 Verifique que el formato se haya aplicado variando los datos correspondientes a la columna Cancelados.

condicional de Barras de datos para las celdas

correspondientes a la columna Total venta, también aplique uno para mostrar las dos mejores ventas en letras color Verde y las dos peores ventas en letras color Rojo.

 Seleccione el rango F8:F13.

 En la ficha Inicio en el grupo Estilos haga clic en Formato condicional y a continuación vaya a Barras de datos, cuando el menú se

despliegue seleccione un estilo, por ejemplo Barras de datos Azul claro. Haga clic sobre el estilo elegido para aplicarlo.

 Manteniendo la selección de celdas, en la ficha Inicio en el grupo Estilos haga clic en Formato condicional y a continuación vaya a Reglas superiores e inferiores, cuando el menú se despliegue haga clic en 10 mejores.

 El cuadro de diálogo 10 elementos superiores aparecerá. En el cuadro Número de elementos ajuste al número 2. En la lista de formato seleccione Formato personalizado. El cuadro de diálogo Formato de celdas se abrirá, en la ficha Fuente elija Estilo Negrita y Color Verde. Haga clic en Aceptar para regresar al cuadro 10 Elementos

superiores. Haga clic en Aceptar.

 Manteniendo la selección de celdas, en la ficha Inicio en el grupo Estilos haga clic en Formato condicional y a continuación vaya a Reglas superiores e inferiores, cuando el menú se despliegue haga clic en 10 inferiores.

(31)

Número de elementos ajuste al número 2. En la lista de formato seleccione Formato personalizado. El cuadro de diálogo Formato de celdas se abrirá, en la ficha Fuente elija Estilo Negrita y Color Rojo. Haga clic en Aceptar para regresar al cuadro 10 Elementos inferiores. Haga clic en Aceptar.

 Verifique que los tres formatos están aplicados.

condicional para las celdas

correspondientes a Puntos Totales para mostrar todos los valores duplicados.

 Seleccione el rango H8:H13.

 En la ficha Inicio en el grupo Estilos haga clic en Formato condicional y a continuación en Resaltar reglas de celdas, una vez que el menú se despliegue haga clic en Duplicar valores. El cuadro de diálogo Duplicar valores aparecerá.

 En la lista de formato elija Relleno Amarillo con Texto Amarillo Oscuro. Haga clic en Aceptar para aplicar el formato.

5. Guarde los cambios realizados con el nombre Mod1 Lab2.

 Vaya a la carpeta de Prácticas que creo anteriormente en Mis Documentos.

(32)

Formato Avanzado de Gráficos SmartArt

Módulo 1

Herramientas de

Herramientas de SmartArtSmartArt. Dise. Diseññoo

Herramientas de

Herramientas de SmartArtSmartArt. Formato. Formato

Formato Avanzado de Gráficos SmartArt

Un gráfico SmartArt es una representación visual de la información que se puede crear de forma rápida y fácil, eligiendo entre los diferentes diseños, con el objeto de comunicar mensajes o ideas eficazmente. Aunque los gráficos SmartArt no se pueden crear en otros programas de versión Office 2007, puede copiarlos y pegarlos como imágenes en dichos programas.

Al crear un gráfico SmartArt, se le pide que elija un tipo, que puede ser: Proceso, Jerarquía, Ciclo o Relación. Un tipo es similar a una categoría de gráfico SmartArt y cada tipo contiene varios diseños diferentes. Una vez elegido el diseño resulta fácil cambiar el diseño del gráfico SmartArt. Gran parte del texto, así como otro contenido, los colores, los estilos, los efectos y el formato de texto se transfieren automáticamente al nuevo diseño. Una vez seleccionado un diseño aparece un texto de marcador de posición (por ejemplo, [Texto]) para que pueda ver el aspecto del gráfico SmartArt.

Para añadir un gráfico SmartArt vaya a la ficha Insertar en el grupo Ilustraciones, seleccione SmartArt. En el cuadro de diálogo Elegir un gráfico SmartArt, haga clic en el tipo y en el diseño que desea. Escriba el texto mediante uno de estos procedimientos:

 Haga clic en una forma del gráfico SmartArt y, a continuación, escriba el texto.

 Haga clic en [Texto] en el panel de texto y escriba o pegue el texto.

 Copie texto desde otro programa, haga clic en [Texto] y péguelo en el panel de texto.

(33)

Cuando seleccione un gráfico SmartArt aparecerá una ficha más llamada Herramientas de SmartArt, con las cinta de opciones Diseño y Formato.

Herramientas de SmartArt Diseño. En esta cinta de opciones encontrará los siguientes grupos:

 Crear gráfico donde encuentra:

 Agregar forma. Agrega una forma al gráfico, indicando el lugar donde la insertará.

 Agregar viñeta. Agrega viñetas al texto de la forma

 De izquierda a derecha. Cambia el diseño del gráfico intercambiando los lugares de las formas.

 Diseño. Está activo dependiendo del tipo de gráfico SmartArt elegido. Permite cambiar el diseño de una rama del gráfico.  Aumentar y disminuir nivel. Aumenta o reduce el nivel de la

forma o viñeta seleccionada.

 Panel de texto. Muestra u oculta el panel de texto.

 Diseños. Ofrece diseños rápidos para el gráfico

 Estilos SmartArt. Ofrece estilos rápidos de colores y de presentación.

 Restablecer. Descarta todos los cambios de formato realizados.

Herramientas de SmartArt Formato. En esta cinta de opciones encontrará los siguientes grupos:

 Formas. Permite cambiar el aspecto de la forma seleccionada a través de: editarla en 2D, cambiar la forma por otra, ajustarla al espacio. Para cambiar una forma por otra también se puede dar clic derecho sobre la forma a cambiar y seleccionar el menú Cambiar forma donde se mostrarán las formas disponibles.

 Estilos de forma. Permite cambiar el estilo de la forma de manera rápida, o bien personalizarlo mediante los comandos Relleno de forma, Contorno de forma y Efectos de formas.

 Estilos de WordArt. Permite aplicar formato personalizado o rápido al texto del gráfico. Para ello se tienen los estilos rápidos o bien las herramientas de Relleno, Contorno y Efectos de texto.

 Organizar. Permite, como su nombre indica, organizar el diagrama con respecto a las formas, imágenes, etc. que se tengan en la hoja.

 Tamaño. Permite ajustar el ancho y alto del objeto.

También es posible dar formato a un gráfico, dando clic derecho sobre él y seleccionando Formato de forma o bien formato de objeto. Se abrirá un cuadro de diálogo con los siguientes paneles:

 Relleno. Permite cambiar el relleno de la forma u objeto (sin relleno, sólido, degradado, con imagen o textura), dependiendo del tipo de relleno se puede aplicar una cantidad de colores y una transparencia.

(34)

 Color de línea. Permite utilizar una línea sólida, degradada o bien no utilizar línea. Dependiendo del tipo de línea se permite utilizar una transparencia.

 Estilo de línea. Permite elegir el ancho de línea, el tipo, tipo de guión, tipo de remate y combinación, así como la configuración de flechas.

 Sombra. Permite elegir alguna sombra Preestablecida (Exterior, Interior, Perspectiva), y posteriormente configurar el color de sombra, su transparencia, tamaño, desenfoque, ángulo, distancia.

 Formato 3D. Permite elegir el tipo de Bisel superior e inferior, así como su ancho y su alto. La profundidad y su color. El tamaño de contorno y su color. El material de la superficie y su iluminación y ángulo.

 Giro 3D. Permite elegir el tipo de giro preestablecido, o bien configurar el giro de acuerdo a los ejes X, Y, Z y la perspectiva. También si es un texto mantenerlo sin relieve, así como la posición del objeto o distancia desde la superficie.

 Imagen. Permite cambiar el control de color de la imagen, así como su brillo y contraste.

 Cuadro de texto. Permite personalizar el diseño de texto (alineación vertical y dirección del texto), el autoajuste y los márgenes. También permite el uso de columnas.

(35)

Formato Avanzado de Imágenes

Módulo 1

Herramientas de imagen. Formato

Formato Avanzado de Imágenes

Una manera de añadir una imagen a una hoja de cálculo es mediante la ficha Insertar en el grupo Ilustraciones con los comandos Imágenes prediseñadas Imagen. Si selecciona el comando Imágenes Prediseñadas aparecerá un panel donde podrá indicar mediante el cuadro Buscar, un texto que haga referencia a las imágenes que desea. También le permite indicar en qué parte de la galería multimedia Buscar y de qué tipo deben de ser los resultados de la búsqueda. Para insertarla basta con dar clic sobre la imagen deseada.

Si selecciona el comando Imagen, o bien desde archivo, se abrirá un cuadro de diálogo que le permita localizar la unidad de disco donde se encuentra la imagen que se desea añadir.

Una vez añadida la imagen a la hoja de cálculo puede cambiar su ubicación haciendo clic sobre ella y arrastrándola a la zona deseada o bien puede cambiar el tamaño haciendo clic con el botón secundario y seleccionando Formato de Imagen en el menú contextual. También arrastrando uno de los manejadores que rodean el gráfico.

Cuando seleccione una imagen añadida a la hoja de cálculo aparecerá una ficha más de Herramientas de Imagen, con la cinta de opciones Formato donde encontrará los siguientes grupos:

(36)

 Ajustar o Brillo o Contraste

o Volver a colorear (disponible sólo para imágenes prediseñadas) o Comprimir imagen o Cambiar imagen o Restablecer imagen  Estilos de imagen o Galería de estilos o Forma de la imagen o Contorno de forma o Efectos de la imagen  Organizar o Traer al frente o Enviar al fondo o Panel de Selección o Alinear o Agrupar o Girar  Tamaño o Recortar o Alto o Ancho

También puede dar clic derecho sobre la imagen y elegir Formato de imagen, se abrirá un cuadro de diálogo con los siguientes paneles:

 Relleno. Permite cambiar el relleno de la forma u objeto (sin relleno, sólido, degradado, con imagen o textura), dependiendo del tipo de relleno se puede aplicar una cantidad de colores y una transparencia.

 Color de línea. Permite utilizar una línea sólida, degradada o bien no utilizar línea. Dependiendo del tipo de línea se permite utilizar una transparencia.

 Estilo de línea. Permite elegir el ancho de línea, el tipo, tipo de guión, tipo de remate y combinación, así como la configuración de flechas.

 Sombra. Permite elegir alguna sombra Preestablecida (Exterior, Interior, Perspectiva), y posteriormente configurar el color de sombra, su transparencia, tamaño, desenfoque, ángulo, distancia.

 Formato 3D. Permite elegir el tipo de Bisel superior e inferior, así como su ancho y su alto. La profundidad y su color. El tamaño de contorno y su color. El material de la superficie y su iluminación y ángulo.

 Giro 3D. Permite elegir el tipo de giro preestablecido, o bien configurar el giro de acuerdo a los ejes X, Y, Z y la perspectiva. También si es un texto mantenerlo sin relieve, así como la posición del objeto o distancia desde la superficie.

 Imagen. Permite cambiar el control de color de la imagen, así como su brillo y contraste.

(37)

 Cuadro de texto. Permite personalizar el diseño de texto (alineación vertical y dirección del texto), el autoajuste y los márgenes. También permite el uso de columnas.

También dando clic derecho puede acceder al cuadro de diálogo Tamaño y Propiedades, donde tiene tres fichas:

 Tamaño. Permite ajustar el tamaño y giro, la escala, el recorte, el bloqueo de la imagen de forma más exacta.

 Propiedades. Permite controlar la ubicación del objeto al mover celdas, su impresión y bloquearlos.

 Texto alternativo. Permite agregar un texto alternativo en caso de que la hoja o el libro sea publicado.

La cinta de opciones Herramientas de Imagen. Formato, sólo está disponible cuando se tiene seleccionada una imagen.

(38)

Formato Avanzado de gráficos SmartArt e Imágenes

DEMOSTRACI

Ó

N

OBJETIVO: OBJETIVO: Mostrar la creaci

Mostrar la creacióón de grn de grááficos ficos SmartArtSmartArt y y

aplicaci

aplicacióón de formato avanzado.n de formato avanzado.

Mostrar la inserci

Mostrar la insercióón de imn de imáágenes y aplicacigenes y aplicacióón de n de

formato avanzado.

DEMOSTRACI DEMOSTRACIÓÓNN

(39)

PRACTICA

Módulo 1

(40)

Laboratorio MÓDULO 1. FORMATO DE DATOS Y CONTENIDO. Ejercicio 3. Formato Avanzado de Gráficos SmartArt e Imágenes

En este ejercicio insertará gráficos SmartArt e imágenes y les dará un formato personalizado.

1. Abra un nuevo libro

de Excel en blanco.

2. Inserte en la celda

B4 una imagen

llamada Escudo que se encuentra en la

carpeta de Mis documentos\

Laboratorios\Modulo 1.

 De clic en la celda B4.

 En la ficha Insertar en el grupo Ilustraciones, dé clic en Imagen (Desde archivo).

 En el cuadro de diálogo busque la carpeta que se encuentra en Mis documentos\Laboratorios\Modulo 1 y una vez en ella seleccione la imagen llamada Escudo.

 De clic en Insertar. La imagen aparecerá entonces en la hoja de cálculo.

3. Escale la imagen al

50% de su tamaño original.

 Seleccione la imagen.

 En herramientas de Imagen en la ficha Formato en el grupo Tamaño, dé clic en la flecha situada en la esquina inferior derecha del grupo. El cuadro de diálogo Tamaño y propiedades se mostrará.

 En la pestaña Tamaño, vaya al apartado de Escala y en el cuadro Alto y Ancho teclee 50.

 Clic en Cerrar, para establecer el cambio de tamaño.

4. Haga una copia de

la imagen.

 Clic derecho sobre la imagen y seleccione Copiar.  Sitúese en la primera celda donde quiere crear la copia.  Clic derecho sobre la celda y seleccione Pegar.

5. Aplique a la primera

imagen el siguiente formato: Estilo de imagen 18 y color de contorno.

 Seleccione la primera imagen.

 En herramientas de Imagen, en la ficha Formato en el grupo Estilos de Imagen, dé clic en el botón de Más que se encuentra en la parte inferior a la derecha de los estilos y seleccione Perspectiva relajada, blanca.

 En herramientas de Imagen, en la ficha Formato en el grupo Estilos de Imagen, dé clic en Contorno de forma y elija el color de contorno de su preferencia.

6. Aplique a la segunda

imagen el siguiente formato: Resplandor, Reflexión y Giro 3D.

 Seleccione la segunda imagen.

 En herramientas de Imagen, en la ficha Formato en el grupo Estilos de Imagen, despliegue el botón de Efectos de Imagen,

posteriormente despliegue Resplandor y elija el de su preferencia.  En herramientas de Imagen, en la ficha Formato en el grupo Estilos

de Imagen, despliegue el botón de Efectos de Imagen,

posteriormente despliegue Reflexión y elija la Variación del reflejo de su preferencia.

(41)

de Imagen, despliegue el botón de Efectos de Imagen,

posteriormente despliegue Giro 3D y elija el de su preferencia.

7. Dibuje una

autoforma Bisel que cubra las dos imágenes.

 En la ficha Insertar, en el grupo Ilustraciones, despliegue el comando Formas y en la categoría Formas básicas, haga clic en la forma Bisel. El cursor se convertirá en una cruz delgada.

 Haga clic en la celda A3 y sin soltar, arrastre hasta crear un Bisel que cubra las dos figuras.

8. Dé formato al Bisel creado.

 Seleccione el Bisel y haga clic derecho sobre él. Seleccione Formato de forma. El cuadro de diálogo Formato de forma aparecerá.

 En el panel izquierdo, haga clic sobre relleno. En la parte derecha elija el relleno de su preferencia.

 En el panel izquierdo, haga clic sobre línea. En la parte derecha elija la línea de su preferencia.

 En el panel izquierdo, haga clic sobre Estilo de línea. En la parte derecha elija las características del estilo de su preferencia.  En el panel izquierdo, haga clic sobre Sombra. En la parte derecha

elija las características de sombra de su preferencia.

 Dé clic en cerrar para establecer las características ya elegidas.

9. Envíe hacia atrás el Bisel, de tal forma que las imágenes anteriores se visualicen.

 Seleccione el Bisel y haga clic derecho sobre él. Seleccione Enviar al fondo.

 Verifique que la autoforma es enviada al fondo y que las imágenes se visualizan. De ser necesario reacomode la autoforma para una mejor presentación. 10. Inserte un gráfico SmartArt de tipo Ciclo y aplíquele un formato personalizado.

 En la ficha Insertar en el grupo Ilustraciones, dé clic en SmartArt. El cuadro de diálogo Elegir un gráfico SmartArt aparecerá.

 En el panel izquierdo haga clic sobre la categoría Ciclo y seleccione el tipo Ciclo de Texto. Dé clic en Aceptar. El gráfico SmartArt aparecerá junto con su panel de texto.

 En el panel de texto escriba la siguiente lista, un espacio para escribir texto quedará vacío.

1. Analizar negocio 2. Analizar información 3. Proyectar sistema 4. Implantar sistema

 Seleccione la forma que no incluyo texto nuevo y presione Supr. Verifique que sólo quedan 4 formas.

 En Herramientas de SmartArt en la ficha Diseño en el grupo Estilos SmartArt haga clic en Cambiar colores y seleccione el de su preferencia.

 En Herramientas de SmartArt en la ficha Diseño en el grupo Estilos SmartArt haga clic en el botón de Más diseños y seleccione el de su preferencia.

11. Guarde los cambios realizados con el nombre Mod1 Lab3.

(42)

Documentos.

(43)

(44)

(45)

Validación de Datos

Es común que los usuarios por prisa u otro motivo o razón al introducir datos se equivoquen. La validación de datos le permite establecer reglas para que cuando un dato sea introducido, éste sea supervisado y se asegure que se encuentre en el rango deseado o con las características establecidas.

Microsoft Excel le permite validar que los valores pertenezcan a una lista, que estén dentro de ciertos límites, fechas y horas, dentro de ciertos períodos, longitudes de texto específicas, etc. Incluso si desea evitar o permitir valores en blanco. También es esencial proporcionar una ayuda inmediata para dar instrucciones a los usuarios y mensajes claros cuando se escriban datos no válidos para hacer que el proceso de entrada de datos se lleve a cabo sin problemas.

Los estilos de error que se manejan son:

• Grave. Este estilo no permite que el usuario introduzca una información que no sea válida, de tal manera que si el dato introducido no es válido solamente permite borrarlo o bien cambiarlo para poder pasar a otra celda.

• Advertencia. Este estilo indica que la información introducida no es válida, sin embargo permite dejar el dato introducido, al ignorar la validación.

• Información. Indica al usuario que la información introducida no es válida, pero permite pasar a otra celda sin mayor complicación.

Los mensajes sólo aparecen cuando se escriben los datos directamente en las celdas. El mensaje no aparecerá:

(46)

• Cuando un usuario inserte datos en una celda copiando o rellenando. • Cuando una fórmula de la celda calcule un resultado que no sea válido.

• Cuando una macro (macro: acción o conjunto de acciones que se pueden utilizar para automatizar tareas.) inserte datos no válidos en la celda.

Una vez que decida qué validación desea utilizar en una hoja de cálculo, seleccione una o más celdas para validar. En la ficha Datos, en el grupo Herramientas de datos, haga clic en Validación de datos. Aparece el cuadro de diálogo Validación de datos. Haga clic en la ficha Configuración.

En la ficha Configuración especifique el criterio de validación utilizando la lista desplegable Permitir. Entre las opciones encuentra:

• Lista. Le permite elegir el Origen del cual se tomarán los valores para la lista desplegable, ya sea escribiéndolos directamente en el cuadro Origen separándolos por coma o bien seleccionando algún rango donde se encuentre dicha información. Asegúrese de que esté activada la casilla de verificación Celda con lista desplegable. • Número entero. El cuadro Datos permite seleccionar el tipo de restricción a utilizar para los números (entre, no está entre, igual, no igual, mayor que, etc.), y al seleccionar el tipo se habilitan los cuadros para las restricciones (máximo, mínimo, valor) dependiendo del tipo de restricción.

• Decimal. El cuadro Datos permite seleccionar el tipo de restricción a utilizar para los números decimales (entre, no está entre, igual, no igual, mayor que, etc.), y al seleccionar el tipo se habilitan los cuadros para las restricciones (máximo, mínimo, valor) dependiendo del tipo de restricción.

• Fecha. El cuadro Datos permite seleccionar el tipo de restricción a utilizar para las fechas (entre, no está entre, igual, no igual, mayor que, etc.), y al seleccionar el tipo se habilitan los cuadros para las restricciones (fecha inicial, fecha final) dependiendo del tipo de restricción. También puede utilizar una fórmula que devuelva una fecha.

• Hora. El cuadro Datos permite seleccionar el tipo de restricción a utilizar para las horas (entre, no está entre, igual, no igual, mayor que, etc.), y al seleccionar el tipo se habilitan los cuadros para las restricciones (hora inicial, hora final) dependiendo del tipo de restricción. También puede utilizar una fórmula que devuelva un valor de hora.

• Longitud del texto. El cuadro Datos permite seleccionar el tipo de restricción a utilizar para la longitud del texto (entre, no está entre, igual, no igual, mayor que, etc.), y al seleccionar el tipo se habilitan los cuadros para las restricciones (máximo, mínimo, longitud) dependiendo del tipo de restricción.

• Personalizado. En el cuadro Fórmula, escriba una fórmula que calcule un valor lógico (VERDADERO para las entradas válidas o FALSO para las no válidas).

Para especificar cómo desea administrar los valores en blanco (nulos), active o desactive la casilla de verificación Omitir blancos.

Una vez terminada la configuración del criterio de validación, utilice la ficha Mensaje de entrada para personalizar el mensaje que aparecerá cuando se haga clic en la celda validada. Asegúrese de que la casilla de verificación Mostrar mensaje de

(47)

entrada al seleccionar la celda está activada. Rellene el título y el texto del mensaje. Este mensaje es opcional.

En la ficha Mensaje de error especifique cómo desea que Excel responda cuando se especifiquen datos no válidos. Asegúrese de que esté activada la casilla de verificación Mostrar mensaje de error si se escriben datos no válidos. Seleccione una de las siguientes opciones en el cuadro Estilo: Información, Advertencia, Grave. Escriba el título y el texto del mensaje (máximo 225 caracteres).

Si quiere comprobar si existen datos no válidos en las celdas configuradas, en la ficha Datos, en el grupo Herramientas de datos, despliegue Validación de datos y haga clic en Rodear con un círculo datos no válidos. Los datos que no sean válidos entonces se rodearán con un círculo rojo. Para borrar estos círculos, en la ficha Datos, en el grupo Herramientas de datos, despliegue Validación de datos y haga clic en Borrar círculos de validación.

(48)

Formularios

Cuando se llenan listas muy largas con datos repetitivos, los formularios le ayudan a insertar al final de la lista una nueva fila con los datos que introducimos en él. No sólo ayudan a insertar filas, también a localizarlas y posteriormente poder modificarlas, eliminarlas o bien simplemente visualizar la información.

Para activar la herramienta formulario haga clic en el Botón de Microsoft Office y a continuación en el botón de Opciones de Excel. El cuadro de diálogo Opciones de Excel se abrirá, en el panel izquierdo seleccione Personalizar. En la ventana correspondiente a Personalizar haga clic en la lista desplegable Comandos disponibles en y seleccione Comandos que no están en la cinta de opciones. De la lista de comandos que se muestran seleccione Formulario y haga clic en el botón Agregar. El icono del comando aparecerá en la barra de herramientas de acceso rápido. Haga clic en Aceptar.

Una vez activado sitúese en cualquier celda que se encuentre dentro de la lista de datos, haga clic en el botón Formulario. Aparecerá un formulario que lleve por título el nombre de la hoja. Los rótulos de las columnas aparecerán como los nombres de campos y para cada uno el cuadro de texto correspondiente para llenar. Se mostrará sobre los botones del formulario el número del registro en el cual está situado el cursor y el total.

(49)

Los botones disponibles en un formulario son:

 Nuevo. Permite dejar en blanco el formulario para introducir una nueva fila. La inserta siempre al final.

 Eliminar. Borra el registro actual y ya no puede ser recuperado.

 Restaurar. Desecha los cambios realizados.

 Buscar anterior, Buscar siguiente. Después de especificar los criterios permite avanzar entre los registros ya sea hacia delante o hacia atrás para ir visualizando las filas que los cumplan.

 Criterios. Permite especificar en los cuadros de texto los criterios a utilizar en una búsqueda.

(50)

DEMOSTRACI

DEMOSTRACIÓ

ÓN

N

OBJETIVO:

Mostrar el uso de los formularios para captura

r

ráápida de datos y validacipida de datos y validacióón de datos para captura n de datos para captura

correcta. correcta. DEMOSTRACI DEMOSTRACIÓÓNN Módulo 4 Validación y Formularios

(51)

PRACTICA

Módulo 4

(52)

Laboratorio MÓDULO 2. ORGANIZACIÓN DE DATOS Ejercicio 1. Validación y Formularios

En este ejercicio aplicará la validación a un rango de celdas y posteriormente utilizará un formulario para introducir datos.

Excel llamado Validación y formularios.

2. Valide el rango

B6:B20 para que los números de cama sean mayores a cero pero menores a 15.

 Seleccione el rango B6:B20.

 En la ficha Datos en el grupo Herramientas de datos, haga clic en Validación de datos, el cuadro de diálogo Validación de datos aparecerá.

 En la ficha configuración, en el cuadro Permitir seleccione Número entero.

 En el cuadro Datos seleccione Entre.  En el cuadro mínimo teclee 1.  En el cuadro máximo teclee 15.

 En la ficha Mensaje de entrada active, en caso de ser necesario, la casilla Mostrar mensaje de entrada al seleccionar la celda.

 En el cuadro Título teclee Cama.

 En el cuadro Mensaje de entrada teclee “Sólo hay 15 camas”.  En la ficha Mensaje de error active, en caso de ser necesario, la

casilla Mostrar mensaje de error si se introducen datos no válidos.  En la lista estilo seleccione Información.

 En el cuadro Título teclee Cama.

 En el cuadro Mensaje de entrada teclee “Sólo hay 15 camas, verifique el número”.

 Haga clic en Aceptar.

C6:C20 para que los nombres introducidos no sean mayores de 50 caracteres.

 Seleccione el rango C6:C20.

 En la ficha configuración, en el cuadro Permitir seleccione Longitud de texto.

 En el cuadro Datos seleccione Menor o igual que.  En el cuadro máximo teclee 50.

 En la ficha Mensaje de entrada desactive, en caso de ser necesario, la casilla Mostrar mensaje de entrada al seleccionar la celda.  En la ficha Mensaje de error active, en caso de ser necesario, la

casilla Mostrar mensaje de error si se introducen datos no válidos  En la lista estilo seleccione Advertencia.

 En el cuadro Título teclee Error en Nombre.

 En el cuadro Mensaje de entrada teclee “El nombre excede los 50 caracteres”.

(53)

D6:D20 para que la fecha introducida no sea mayor que la fecha actual, ni menor al 01/01/2007.

 Seleccione el rango D6:D20.

 En la ficha configuración, en el cuadro Permitir seleccione Fecha y Desactive la casilla Omitir blancos.

 En el cuadro Datos seleccione Entre.  En el cuadro mínimo teclee 01/01/2007.  En el cuadro máximo teclee =hoy().

 En la ficha Mensaje de entrada desactive, en caso de ser necesario, la casilla Mostrar mensaje de entrada al seleccionar la celda.  En la ficha Mensaje de error active, en caso de ser necesario, la

casilla Mostrar mensaje de error si se introducen datos no válidos.  En la lista estilo seleccione Grave.

 En el cuadro Título teclee Fecha Visita.

 En el cuadro Mensaje de entrada teclee “La fecha debe ser mayor al 01/01/2007 y menor o igual a la fecha actual, teclee una fecha válida”.  Haga clic en Aceptar.

E6:E20 para que la hora introducida sea entre las 08:00 y las 22:00 horas.

 Seleccione el rango E6:E20.

 En la ficha configuración, en el cuadro Permitir seleccione Hora  En el cuadro Datos seleccione Entre.

 En el cuadro mínimo teclee 08:00.  En el cuadro máximo teclee 22:00.

 En la ficha Mensaje de entrada active, en caso de ser necesario, la casilla Mostrar mensaje de entrada al seleccionar la celda.

 En el cuadro Título teclee Hora de visita.

 En el cuadro Mensaje de entrada teclee “Las visitas son entre las 08:00 y 22.00 horas”.

 En la ficha Mensaje de error active, en caso de ser necesario, la casilla Mostrar mensaje de error si se introducen datos no válidos.  En la lista estilo seleccione Advertencia.

 En el cuadro Título teclee Hora Visita.

 En el cuadro Mensaje de entrada teclee “La hora está fuera del horario de visitas”.

F6:F20 para que se despliegue una lista de servicios de donde elegir el

correspondiente.

 Seleccione el rango F6:F20.

 En la ficha configuración, en el cuadro Permitir seleccione Lista, verifique que las casillas Omitir blancos y Celda con lista desplegable están activados.

 Haga clic en el cuadro Origen y seleccione el rango K5:K7.

 En la ficha Mensaje de entrada desactive, en caso de ser necesario, la casilla Mostrar mensaje de entrada al seleccionar la celda.  En la ficha Mensaje de error desactive, en caso de ser necesario, la

(54)

 En la lista estilo seleccione Grave.  Haga clic en Aceptar.

7. Inserte un registro en la fila 7.

 En la celda B7 escriba 12.

 En la celda C7 escriba Juan Pérez Pérez.  En la celda D7 escriba 12/01/2007.  En la celda E7 escriba 07:00.

 Se mostrará el mensaje de error Advertencia, haga clic en No y escriba 09:00.

 En la celda F7 despliegue y elija Cirugía.

8. Inserte un registro utilizando un

formulario.

 En caso de ser necesario active el icono del formulario, para ello haga clic en el botón de Microsoft Office y a continuación en el botón de Opciones de Excel. El cuadro de diálogo Opciones de Excel se abrirá.  En el panel izquierdo seleccione Personalizar.

 En la ventana correspondiente a Personalizar haga clic en la lista desplegable Comandos disponibles en y seleccione Comandos que no están en la cinta de opciones.

 De la lista de comandos que se muestran seleccione Formulario y haga clic en el botón Agregar. El icono del comando aparecerá en la barra de herramientas de acceso rápido.

 Haga clic en el botón de Formulario de la barra de herramientas de acceso rápido. El formulario Agenda aparecerá.

 Haga clic en Nuevo.

 En No, de cama escriba 16 y presione Tabulador.

 En Nombre Visitante escriba María José Perera y presione Tabulador.  En Fecha Visita escriba 15/01/2007 y presione Tabulador.

 En Hora Visita escriba 23:00 y presione Tabulador.  En Servicio escriba Quimio y presione Tabulador.  Haga clic en Nuevo.

 El mensaje de error en el número de cama aparecerá, haga clic en Aceptar.

 El mensaje de error en la hora de visita aparecerá, haga clic en Sí.  Haga clic en Cerrar.

9. Encierre en un círculo los datos no válidos.

 En la ficha Datos en el grupo Herramientas de datos, despliegue Validación de datos y haga clic en Rodear con un círculo datos no válidos.

 Se mostrarán encerrados en un círculo los datos no válidos.

10. Guarde el libro creado con el nombre Mod2 Lab1.

(55)

Consolidación de Datos

Si estamos trabajando con varias tablas, todas con un esquema idéntico y queremos en una sola totalizar los datos contenidos en ellas consolidar datos es la herramienta adecuada para realizar esta operación. Por medio de consolidar datos podemos entonces encontrar el total de todos los datos correspondientes a diferentes tablas y concentrarlo en una nueva.

Para consolidar datos, se combinan los valores de varios rangos de datos. Por ejemplo, si tiene una hoja de cálculo de cifras de gastos para cada una de sus oficinas regionales, puede utilizar una consolidación para reunir estas cifras en una hoja de cálculo de gastos para toda la organización.

Excel permite consolidar datos de varios modos:

 Por posición. Cuando los datos de todas las áreas de origen se organicen en un orden y una ubicación idénticos; por ejemplo, si cuenta con datos de una serie de hojas de cálculo creadas desde la misma plantilla.

 Por categorías. Si desea resumir un conjunto de hojas de cálculo que tienen los mismos rótulos de filas y columnas pero organizan los datos de forma diferente. Este método combina los datos que tienen rótulos coincidentes en cada hoja de cálculo. La consolidación se actualiza automáticamente cuando cambian los datos de los rangos de origen.

(56)

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. Para consolidar datos, utilice el comando Consolidar del grupo Herramientas de datos de la ficha Datos.

Para consolidar haga clic en la celda superior izquierda del área donde desee que aparezcan los datos consolidados. Asegúrese de dejar suficientes celdas a la derecha y por debajo de esta celda para los datos de consolidación. En la ficha Datos en el grupo Herramientas de datos haga clic en Consolidar. El cuadro de diálogo Consolidar aparecerá.

En el cuadro Función, haga clic en la función de resumen (Sumar, Contar, Promedio, etc.) que desea que utilice Excel para consolidar los datos.

En el cuadro Referencia 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, o bien escriba o seleccione el rango a utilizar y haga clic en el botón Agregar. Repita esto para cada rango.

Si desea que la consolidación se actualice de forma automática cuando cambien los datos de origen active Crear vínculos con los datos de origen.

Si desea consolidar Por Posición, en la sección Usar rótulos en… deje en blanco las casillas para que Excel no copie los rótulos de fila o columna de los rangos de origen a la consolidación.

Si desea consolidar Por Categorías, 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.

Para consolidar por fórmula 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. Haga clic en la celda en que desea incluir los datos de consolidación. 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.

(57)

(58)

(59)

Laboratorio MÓDULO 2. ORGANIZACIÓN DE DATOS Ejercicio 2. Consolidar datos

En este ejercicio utilizará la herramienta Consolidar datos.

1. Abra el archivo de Excel llamado Consolidar. 2. Sitúese en la hoja Enero y al rango C6:N36 asígnele el nombre Enero.

 De ser necesario de clic en la etiqueta de la hoja Enero para situarse en ella.

 Seleccione el rango C6:N36.

 Haga clic en el cuadro de nombres y teclee Enero.  Presione Enter.

3. Sitúese en la hoja

Febrero y al rango C6:N36 asígnele el nombre Febrero.

 De ser necesario de clic en la etiqueta de la hoja Febrero para situarse en ella.

 Seleccione el rango C6:N36.

 Haga clic en el cuadro de nombres y teclee Febrero.  Presione Enter.

4. Efectúe el

consolidado para la hoja Totales.

 De ser necesario de clic en la etiqueta de la hoja Totales para situarse en ella.

 En la ficha Datos en el grupo Herramientas de datos haga clic en Consolidar. El cuadro de diálogo Consolidar aparecerá.

 En la lista Función seleccione Suma.

 En el cuadro referencia escriba Enero. Haga clic en Agregar y verifique que el rango se haya agregado.

 En el cuadro referencia escriba Febrero. Haga clic en Agregar y verifique que el rango se haya agregado.

 En el recuadro referencia haga clic en el botón Contraer cuadro de diálogo.

 Haga clic en la etiqueta de la hoja Marzo.  Seleccione el rango C6:N36.

 Haga clic en el botón Expandir cuadro de diálogo.  Haga clic en Agregar.

 Verifique que en la sección Usar rótulos en estén desactivadas las casillas Fila superior y Columna izquierda.

5. Efectúe el

consolidado para la hoja Promedios.

 De ser necesario de clic en la etiqueta de la hoja Promedios para situarse en ella.

 En la ficha Datos en el grupo Herramientas de datos haga clic en Consolidar. El cuadro de diálogo Consolidar aparecerá.

 En la lista Función seleccione Promedio.

(60)

verifique que el rangos se haya agregado.

 En el cuadro referencia escriba Febrero. Haga clic en Agregar y verifique que el rangos se haya agregado.

 En el recuadro referencia haga clic en el botón Contraer cuadro de diálogo.

 Haga clic en la etiqueta de la hoja Marzo.  Seleccione el rango C6:N36.

 Haga clic en el botón Expandir cuadro de diálogo.  Haga clic en Agregar.

 Verifique que en la sección Usar rótulos en estén desactivadas las casillas Fila superior y Columna izquierda.

 Active la casilla Crear vínculos con los datos de origen.  Haga clic en Aceptar.

 Verifique que se haya creado el esquema para la vinculación con los datos de origen.

6. Guarde el libro

creado con el nombre Mod2 Lab2.

 En el cuadro Nombre de archivo escriba Mod2 Lab2..

(61)

(62)

Trabajando con Bases de Datos

Al t

Al téérmino del mrmino del móódulo el alumno serdulo el alumno seráácapaz de:capaz de:

•

• Importar datos a Excel desde fuentes externas.Importar datos a Excel desde fuentes externas.

•

• Utilizar la herramienta filtros avanzados.Utilizar la herramienta filtros avanzados.

•

• Generar subtotales.Generar subtotales.

•

• Realizar bRealizar búúsquedas por medio de funciones de squedas por medio de funciones de

b

búúsqueda y de bases de datos.squeda y de bases de datos.

•

• Ordenar los datos generados , importados, etc., para un Ordenar los datos generados , importados, etc., para un mejor manejo.

(63)

Importar Datos Desde un Origen Externo

Actualmente las bases de datos son muy utilizadas para almacenar la información que trabajamos. Existen diferentes tipos de bases de datos. Excel nos permite interactuar con ellas por medio de la importación de datos. De esta manera podemos extraer a Excel los datos que necesitemos de la base para poder trabajarla o bien presentarla de la forma que deseemos, así como realizar cálculo u otras operaciones sobre ellas.

Al importar datos, no tiene necesidad de volver a escribir los datos que desea analizar en Microsoft Excel. También puede actualizar los informes y resúmenes de Excel automáticamente de la base de datos de origen inicial siempre que la base de datos se actualice con información nueva.

Microsoft Excel nos permite importar toda la información de una base de datos, e incluso importar sólo una parte que cumpla algún criterio. También es posible que si utilizamos una consulta se nos permita ordenar los datos antes de importarlos a Excel.

Se permite importar bases de datos de Access, dBase, Archivos de texto e inclusive información web.

Para importar datos en la ficha Datos haga clic en Obtener datos externos y elija una de las fuentes que se ofrecen: Desde Access, Desde Web, Desde Texto, De otras

(64)

fuentes (SQL Server, Análisis Server, Microsoft Query, Desde origen XML, mediante el asistente) o bien elija alguna de las conexiones existentes.

Desde Access

Al hacer clic sobre la opción aparecerá el cuadro de diálogo Seleccionar archivos de origen de datos donde podrá elegir la ubicación de la base de datos. Selecciónela y haga clic en el botón Abrir. Se mostrará el cuadro Seleccionar Tabla donde se listarán las consultas y las tablas que se encuentren dentro de la Base de Datos. Seleccione la que contenga los datos a importar y haga clic en Aceptar. Aparecerá el cuadro de diálogo Importar datos donde podrá indicar Cómo desea ver los datos en el libro:

 Tabla

 Informe de tabla dinámica

 Informe de gráfico y tabla dinámicos

 Crear sólo conexión Y donde situar los datos:

 Hoja de cálculo existente

 Hoja de cálculo nueva

También puede editar las propiedades de la conexión como son el nombre, su descripción, su control de actualización, definición de conexión, etc.

Desde Web

El cuadro de diálogo Nueva consulta Web le permite ingresar la dirección de la página donde se encuentran los datos a importar. Las tablas que pueden ser importadas aparecerán marcadas por unas flechas en cuadros de color amarillo, basta con dar un clic sobre las que se desea importar para seleccionar, se indicará esta selección con una paloma dentro de un cuadro verde. Puede editar si así lo desea las opciones de consulta Web (formato). Una vez que haga clic en Aceptar se establecerá contacto con el servidor para importar la información y aparecerá el cuadro de diálogo Importar datos para indicar donde situará los datos y en caso de ser necesario editar las propiedades de la conexión.

Desde Texto

El cuadro de diálogo Importar Archivo de Texto le permite localizar el archivo de texto a importar. Una vez seleccionado haga clic sobre el botón Importar, aparecerá el Asistente para importar texto que se basa en tres pasos:

1. Elegir si el archivo de texto tiene datos delimitados o de ancho fijo 2. Establecer los separadores (delimitado) o ancho de campo (ancho fijo) 3. Establecer el formato de los datos