Manual Excel 2007 Nivel Avanzo v.1

Curso Microsoft Excel 2007 Nivel Avanzado

Aster Mérida

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 Laboratorio .......................................................................................................... 105

Microsoft Excel 2007 Avanzado

Página 2

Aster Mérida

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 Introducción a Macros ......................................................................................... 209


Página 3

Aster Mérida

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 Laboratorio .......................................................................................................... 232


Página 4

Aster Mérida


Página 5

Aster Mérida


Página 6

Aster Mérida


Página 7

Aster Mérida


Página 8

Aster Mérida


Página 9

Aster Mérida


Página 10

Aster Mérida


Página 11

Aster Mérida


Página 12

Aster Mérida


Página 13

Aster Mérida


Página 14

Aster Mérida

MÓDULO 1: Formato de Datos y Contenido


Página 15

Aster Mérida


Página 16

Aster Mérida

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 utilizar son: 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


Página 17

Aster Mérida

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 o o

# muestra únicamente los dígitos significativos y no muestra los ceros sin valor. 0 (cero) muestra los ceros sin valor si un número tiene menos dígitos que ceros en el formato. ? 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]


Página 18

Aster Mérida

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-" 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

Para mostrar Microsoft Excel 2007 Avanzado

Use este código Página 19

Aster Mérida

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.


Página 20

Aster Mérida


Página 21

Aster Mérida

Creación y Modificación de Formatos

PRACTICA

Módulo 1


Página 22

Aster Mérida

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”.

 

3. Para la celda D7, 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”.

 

4. Para la celda D11, 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”.

 

5. Para la celda D13, 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”.

 

   

   

   

   


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á.

Seleccione la celda D7. 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: “C” - 0000 Presione Aceptar, el formato creado se mostrará.

Seleccione la celda D11. 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: (###)### - ## - ## Presione Aceptar, el formato creado se mostrará.

Seleccione la celda D13. 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: [verde][=1]"Vigente";[Rojo][=2]"Inactivo";[Azul]"No registrado" Presione Aceptar, el formato creado se mostrará.

Página 23

Aster Mérida

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.

Página 24

Aster Mérida

Formato Condicional

Módulo 1

Formato Condicional 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


Página 25

Aster Mérida

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.  Valores únicos y duplicados


Página 26

Aster Mérida



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.


Página 27

Aster Mérida

Formato Condicional

DEMOSTRACIÓN OBJETIVO: Crear y aplicar formato condicional a celdas de acuerdo a condiciones establecidas.

Módulo 1


DEMOSTRACIÓ DEMOSTRACIÓN

Página 28

Aster Mérida

Formato condicional

PRACTICA

Módulo 1


Página 29

Aster Mérida

Laboratorio MÓDULO 1. FORMATO DE DATOS Y CONTENIDO. Ejercicio 2. Uso del Formato Condicional. En este ejercicio creará formatos condicionales para determinados criterios. Tareas


1. Abra el archivo de 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.

         

3. Aplique un formato 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 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. 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. El cuadro de diálogo 10 elementos inferiores aparecerá. En el cuadro

Página 30

Aster Mérida

 4. Aplique un formato condicional para las celdas correspondientes a Puntos Totales para mostrar todos los valores duplicados. 5. Guarde los cambios realizados con el nombre Mod1 Lab2.

 



    


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. 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. 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á. Vaya a la carpeta de Prácticas que creo anteriormente en Mis Documentos. En el cuadro Nombre de archivo escriba Mod1 Lab2. En el cuadro Guardar como tipo verifique que diga Libro de Excel. De clic en el botón Guardar.

Página 31

Aster Mérida

Formato Avanzado de Gráficos SmartArt Herramientas de SmartArt. SmartArt. Diseñ Diseño

Herramientas de SmartArt. SmartArt. Formato

Módulo 1

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.


Página 32

Aster Mérida

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.


Página 33

Aster Mérida

    

 

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.


Página 34

Aster Mérida

Formato Avanzado de Imágenes

Herramientas de imagen. Formato

Módulo 1

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:


Página 35

Aster Mérida









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.


Página 36

Aster Mérida



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.


Página 37

Aster Mérida

Formato Avanzado de gráficos SmartArt e Imágenes

DEMOSTRACIÓN OBJETIVO: Mostrar la creació creación de grá gráficos SmartArt y aplicació aplicación de formato avanzado. Mostrar la inserció inserción de imá imágenes y aplicació aplicación de formato avanzado.

Módulo 1



Página 38

Aster Mérida

Formato Avanzado de gráficos SmartArt e Imágenes

PRACTICA

Módulo 1


Página 39

Aster Mérida

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. Tareas


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.

3. Escale la imagen al 50% de su tamaño original.

   

 

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.



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. En herramientas de Imagen, en la ficha Formato en el grupo Estilos

Página 40

Aster Mérida

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.

8. Dé formato al Bisel creado.

 

     

9. Envíe hacia atrás el Bisel, de tal forma que las imágenes anteriores se visualicen. 10. Inserte un gráfico SmartArt de tipo Ciclo y aplíquele un formato personalizado.

 

  

  

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

 


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. 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. 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. 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. 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á. Vaya a la carpeta de Prácticas que creo anteriormente en Mis

Página 41

Aster Mérida

  


Documentos. En el cuadro Nombre de archivo escriba Mod1 Lab3. En el cuadro Guardar como tipo verifique que diga Libro de Excel. De clic en el botón Guardar.

Página 42

Aster Mérida

MÓDULO 2: Organización de Datos


Página 43

Aster Mérida


Página 44

Aster Mérida

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á:


Página 45

Aster Mérida

• 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 Microsoft Excel 2007 Avanzado

Página 46

Aster Mérida

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.


Página 47

Aster Mérida

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.


Página 48

Aster Mérida

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.  Cerrar. Cierra el formulario.


Página 49

Aster Mérida

Validación y Formularios

DEMOSTRACIÓN OBJETIVO: Mostrar el uso de los formularios para captura rápida de datos y validació validación de datos para captura correcta.

Módulo 4



Página 50

Aster Mérida

Validación y Formularios

PRACTICA

Módulo 4


Página 51

Aster Mérida

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. Tareas


1. Abra el archivo de 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.

             

3. Valide el rango C6:C20 para que los nombres introducidos no sean mayores de 50 caracteres.

          


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. Seleccione el rango C6:C20. 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 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”. Haga clic en Aceptar.

Página 52

Aster Mérida

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

           

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

             

6. Valide el rango F6:F20 para que se despliegue una lista de servicios de donde elegir el correspondiente.

     


Seleccione el rango D6:D20. 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 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. Seleccione el rango E6:E20. 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 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”. Haga clic en Aceptar. Seleccione el rango F6:F20. 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 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 casilla Mostrar mensaje de error si se introducen datos no válidos.

Página 53

Aster Mérida

7. Inserte un registro en la fila 7.

 

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

    

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.

               

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

 

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

    


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 Aceptar. 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. 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. 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á. Vaya a la carpeta de Prácticas que creo anteriormente en Mis Documentos. En el cuadro Nombre de archivo escriba Mod2 Lab1. En el cuadro Guardar como tipo verifique que diga Libro de Excel. De clic en el botón Guardar.

Página 54

Aster Mérida

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.


Página 55

Aster Mérida

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.


Página 56

Aster Mérida


Página 57

Aster Mérida


Página 58

Aster Mérida

Laboratorio MÓDULO 2. ORGANIZACIÓN DE DATOS Ejercicio 2. Consolidar datos En este ejercicio utilizará la herramienta Consolidar datos. Tareas


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.



3. Sitúese en la hoja Febrero y al rango C6:N36 asígnele el nombre Febrero.



4. Efectúe el consolidado para la hoja Totales.



  

  

           

5. Efectúe el consolidado para la hoja Promedios.

    


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. 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. De ser necesario de clic en la etiqueta de la hoja Totales para situarse en ella. Seleccione la celda C6. 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. Haga clic en Aceptar. De ser necesario de clic en la etiqueta de la hoja Promedios para situarse en ella. Seleccione la celda C6. 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. En el cuadro referencia escriba Enero. Haga clic en Agregar y

Página 59

Aster Mérida

         


    


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. 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á. Vaya a la carpeta de Prácticas que creo anteriormente en Mis Documentos. En el cuadro Nombre de archivo escriba Mod2 Lab2.. En el cuadro Guardar como tipo verifique que diga Libro de Excel. De clic en el botón Guardar.

Página 60

Aster Mérida

MÓDULO 3: Trabajando con Bases de Datos


Página 61

Aster Mérida

Trabajando con Bases de Datos

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

Importar datos a Excel desde fuentes externas.

•

Utilizar la herramienta filtros avanzados.

•

Generar subtotales.

•

Realizar bú búsquedas por medio de funciones de búsqueda y de bases de datos.

•

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


Página 62

Aster Mérida

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 Microsoft Excel 2007 Avanzado

Página 63

Aster Mérida

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


Página 64

Aster Mérida

Una vez que haga clic en Finalizar 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.

De otras fuentes Para importar datos desde SQL Server o Analysis Services el Asistente para conexión le pedirá el nombre del Servidor y las Credenciales de conexión para poder importar datos. Para importar desde XML seleccione el archivo XML e impórtelo. Una vez que haga clic en Importar aparecerá el panel de asignaciones XML donde podrá elegir que campos utilizar para Importar datos e indicar donde situará los datos y en caso de ser necesario editar las propiedades de la conexión. El Asistente para la conexión le permite importar datos desde otro origen no especificado en la lista. Para algunos se requerirá el nombre del servidor y credenciales de conexión, mientras que para otros solamente la ubicación de los datos a importar. Desde Microsoft Query El cuadro Elegir origen de datos le permite seleccionar la base de datos, la consulta o cubo OLAP de donde importará la información. También puede utilizar el botón Examinar para localizar un origen de datos. Una vez seleccionado el origen aparecerá el cuadro de diálogo Examinar donde podrá localizar y seleccionar el archivo a utilizar. Una vez elegido el tipo de origen, le mostrará las tablas o bien los datos disponibles para que pueda importar los que necesite. Microsoft Query le permite ordenar y filtrar los datos en caso de ser necesario. Una vez guardada la consulta puede ser editada. El comando Conexiones existentes le permite ver las conexiones que se tienen y abrirlas. En la ficha Inicio en el grupo Conexiones tiene el comando Actualizar que permite actualizar los datos que se importan. De igual formas dispone del comando Conexiones para ver las existentes, Editar sus propiedades y los vínculos.


Página 65

Aster Mérida


Página 66

Aster Mérida


Página 67

Aster Mérida

Laboratorio MÓDULO 3. TRABAJANDO CON BASES DE DATOS Ejercicio 1. Importar Datos Desde un Origen Externo En este ejercicio importara datos de una base de datos existente a Excel, lista que será usada en ejercicios posteriores. Tareas


1. Abra un libro en blanco de Excel y sitúese en la celda A7. 2. Importe los campos que se indican de la base de datos Importar.mdb a una hoja de Excel.

  

 

En la ficha Datos sitúese en Obtener datos externos, a continuación clic en el comando De otras fuentes y seleccione Desde Microsoft Query. El cuadro de diálogo Elegir origen de datos aparecerá. En la ficha Bases de datos seleccione MS Access Database, Clic en Aceptar. Se comenzará la conexión al origen de datos y el cuadro Seleccionar base de datos aparecerá. En el cuadro de diálogo Seleccionar base de datos, localiza la base Importar que está en C:, en Mis documentos\Alumno\Laboratorios\Modulo 5. Selecciónela y haga clic en Aceptar. El asistente para consultas se iniciará. En la ventana Elegir columnas del Asistente para consultas se muestran las tablas y se puede desplegar cada una para ver las columnas que contiene. Elegir las siguientes columnas:

  

Columna Idproducto NombreProducto

  

Tabla Productos Productos

      

IdProveedor NombreCompañia Ciudad Región País PrecioUnidad UnidadesEnExistencia

      

Productos Proveedores Proveedores Proveedores Proveedores Productos Productos



UnidadesEnPedido



Productos

 

Clic en Siguiente. En la ventana filtrar datos seleccione en el cuadro Columnas para filtrar Idproducto. En la sección incluir sólo columnas donde, en la lista IdProducto seleccione Menor o igual a. En el segundo cuadro teclee 50. Haga clic en siguiente. En la ventana Criterio de ordenación en el cuadro Ordenar por, seleccione IdProducto y de forma Ascendente. Haga clic en siguiente.

   


Página 68

Aster Mérida

    


    


En la ventana Finalizar, active ala opción Devolver Datos a Microsoft Office Excel. Clic en Finalizar. Aparecerá el cuadro de diálogo Importar datos En la sección Seleccione cómo desea ver los datos en este libro, active Tabla. En la sección ¿Dónde desea situar los datos? Verifique que esté activa la opción Hoja de cálculo existente y se haga referencia a la celda A7. Haga clic en Aceptar. Los datos serán importados a Excel en una tabla. 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á. Vaya a la carpeta de Prácticas que creo anteriormente en Mis Documentos. En el cuadro Nombre de archivo escriba Mod3 Lab1. En el cuadro Guardar como tipo verifique que diga Libro de Excel. De clic en el botón Guardar.

Página 69

Aster Mérida

Ordenación Ordenar los datos es una parte esencial del análisis de datos. Puede que desee poner una lista de nombres en orden alfabético, 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. 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. Cuando ordena texto lo ordena de izquierda a derecha, carácter por carácter. El texto y el texto que incluye números se ordenarán del siguiente modo: 0 1 2 3 4 5 6 7 8 9 (espacio) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C DEFGHIJKLMNOPQRSTUVWXYZ Los apóstrofes (') y guiones (-) no se tienen en cuenta, con una excepción: si dos cadenas de texto son iguales salvo por un guión, el texto con el guión se ordenará en último lugar.


Página 70

Aster Mérida

Si son valores lógicos, FALSO se coloca antes que VERDADERO. Los espacios en blanco siempre se colocan en último lugar. Para ordenar, seleccione los datos o asegúrese de que la celda activa está en una parte de la tabla que contiene los datos. Si sólo desea ordenar por un criterio entonces colóquese en la columna que utilizará para ordenar y en la ficha Inicio en el grupo Modificar haga clic en Ordenar y filtrar y elija la manera en que desea ordenar (ascendente o descendentemente). Si desea ordenar por color de celda, color de fuente o icono, o bien por más de un nivel de ordenamiento, entonces al desplegar Ordenar y filtrar elija Orden personalizado. Aparecerá el cuadro de diálogo de Ordenar.  



En Columna, en el cuadro Ordenar por, seleccione la columna que desea ordenar. En Ordenar según, seleccione el tipo de ordenación: o Para ordenar según el contenido, seleccione Valores o Para ordenar por color de celda, seleccione Color de celda. o Para ordenar por color de fuente, seleccione Color de fuente. o Para ordenar por un conjunto de iconos, seleccione Icono de celda 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, o bien si es texto, número o fecha elija si en orden ascendente u orden descendente.

Para especificar los siguientes criterios de ordenación haga clic en Agregar nivel. Si después no desea alguno de los niveles agregados selecciónelo y de clic en Eliminar nivel. Si los datos tienen encabezados asegúrese de que la casilla Mis datos tienen encabezados esté activada. Cuando desee ordenar por filas o bien que se distingan mayúsculas de minúsculas haga clic en el botón Opciones y active las casillas correspondientes. Puede encontrar ordenar también en la ficha Datos en el grupo Ordenar y filtrar.


Página 71

Aster Mérida


Página 72

Aster Mérida


Página 73

Aster Mérida

Laboratorio MÓDULO 3. TRABAJANDO CON BASES DE DATOS Ejercicio 2. Ordenación En este ejercicio ordenará datos de una lista. Tareas


1. Abra el archivo de Excel llamado Ordenar. 2. Sitúese en la hoja Bebidas y ordene las columnas de tal forma que queden en orden de mes de Enero a Junio.

       

3. Ordene de forma ascendente el total.

  

4. Ordene de forma ascendente los meses: Enero, Febrero y Marzo.

        


Seleccione el rango E1:K65. En la ficha Inicio en el grupo Modificar, haga clic en el comando Ordenar y filtrar, y a continuación, en Orden personalizado. El cuadro de diálogo ordenar aparecerá. Haga clic en el botón Opciones. El cuadro de diálogo Opciones de ordenación aparecerá. Active la opción Ordenar de izquierda a derecha. Dé clic en Aceptar. En el apartado Fila, haga clic en la lista desplegable Ordenar por y seleccione Fila 1. En el apartado Criterio de ordenación, haga clic en la lista desplegable y seleccione Lista personalizada. El cuadro de diálogo Listas personalizadas se abrirá. Seleccione la lista personalizada Enero, Febrero, …, Diciembre. Haga clic en Aceptar. Regresará al cuadro de diálogo Ordenar. De clic en el botón Aceptar. Verifique que los meses se encuentren ordenados. Sitúese en cualquier celda correspondiente a la columna de Total. En la ficha Inicio en el grupo Modificar, haga clic en el comando Ordenar y filtrar, y a continuación, en Ordenar de menor a mayor. Verifique que las cantidades se hayan ordenado. Sitúese en cualquier celda de la tabla. En la ficha Inicio en el grupo Modificar, haga clic en el comando Ordenar y filtrar, y a continuación, en Orden personalizado. El cuadro de diálogo Ordenar aparecerá. En el apartado Columna, haga clic en la lista desplegable Ordenar por y seleccione Enero. En el apartado Criterio de ordenación, haga clic en la lista desplegable y seleccione De menor a mayor. Haga clic en el botón Agregar nivel. En el apartado Columna, haga clic en la lista desplegable Luego por y seleccione Febrero. En el apartado Criterio de ordenación, haga clic en la lista desplegable y seleccione De menor a mayor. Haga clic en el botón Agregar nivel. En el apartado Columna, haga clic en la lista desplegable Luego por y seleccione Marzo.

Página 74

Aster Mérida

 


    


En el apartado Criterio de ordenación, haga clic en la lista desplegable y seleccione De menor a mayor. Haga clic en el botón Aceptar. Verifique que los datos se encuentren ordenados. 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á. Vaya a la carpeta de Prácticas que creo anteriormente en Mis Documentos. En el cuadro Nombre de archivo escriba Mod3 Lab2. En el cuadro Guardar como tipo verifique que diga Libro de Excel. De clic en el botón Guardar.

Página 75

Aster Mérida

Subtotales Si trabaja con datos que necesiten ser totalizados por grupos los subtotales le permiten realizar esta operación de manera rápida, ya que por grupo de datos ofrece no solamente el total del grupo, sino también puede encontrar el promedio, mínimo, máximo o bien el número de elementos pertenecientes al grupo en cuestión. Microsoft Excel calcula automáticamente valores de subtotales y de totales generales en una lista. Cuando se insertan subtotales automáticos, Excel esquematiza la lista para que se puedan mostrar y ocultar las filas de detalle de cada subtotal. Para insertar subtotales, primero se ordena la lista para agrupar las filas cuyos subtotales se desea calcular. Después pueden calcularse los subtotales de cualquier columna que contenga números. Para calcular subtotales utilice el comando Subtotal del grupo Esquema de la ficha Datos. Los subtotales se calculan mediante una función de resumen (suma, promedio, etc.) y los Totales generales se obtienen a partir de los datos de detalle, no de los subtotales. Si el libro se establece para calcular fórmulas automáticamente, el comando Subtotal vuelve a calcular los valores del subtotal y del total general a medida que modifica los datos. El comando Subtotal también esquematiza la lista de modo que pueda mostrar u ocultar las filas de detalle de cada subtotal.


Página 76

Aster Mérida

Para insertar subtotales 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. Sitúese en una celda del rango y haga clic en el comando Subtotal, el cuadro de diálogo subtotales aparecerá. En el cuadro para cada cambio en, seleccione el nombre del campo para el cual desea aplicar el subtotal. La columna correspondiente a este campo debe estar ordenada, de lo contrario los subtotales no serán correctos. En el cuadro Usar función seleccione la función de resumen que desea utilizar. Sólo puede utilizar una función por cada vez que aplique el subtotal. En la lista Agregar subtotal a, active las casillas de los campos sobre los cuales trabajará la función de resumen. Si desea conservar subtotales calculados anteriormente desactive la casilla Reemplazar subtotales actuales. Si desea que entre cada grupo se inserte un salto de página, ya sea por necesidades de impresión o de vista preliminar, active la casilla Salto de página entre grupos. Si desea que los Totales generales se muestren debajo de los datos, active la casilla Mostrar resumen debajo de los datos, de lo contrario se mostrarán en la parte superior. Para mostrar un resumen solamente de los subtotales y de los totales generales, haga clic en los símbolos de esquema que aparecen junto a los números de fila. Utilice los símbolos + y - para mostrar u ocultar las filas de detalle de los subtotales individuales. Si quita los subtotales de una lista, Excel eliminará también el esquema y todos los saltos de página que se hayan insertado en la lista. Para ello haga clic en una celda de la lista que contenga un subtotal. En el grupo Esquema de la ficha Datos, haga clic en Subtotal. Se mostrará el cuadro de diálogo Subtotales. Haga clic en Quitar todos.


Página 77

Aster Mérida


Página 78

Aster Mérida


Página 79

Aster Mérida

Laboratorio MÓDULO 3. TRABAJANDO CON BASES DE DATOS Ejercicio 3. Subtotales En este ejercicio aplicará la herramienta de subtotales a una lista importada previamente Tareas


1. Abra el libro llamado Mod3 Lab1, donde realizó la importación de datos. 2. Convierta la tabla a un rango para aplicar subtotales.

   

3. Ordene la lista de forma ascendente tomando como criterio de ordenación el IdProveedor.



4. Agregue un subtotal de Unidades en Existencia y Unidades en pedido por proveedor.





   

5. Agregue además del total el Promedio de las Unidades en Existencia y Unidades en Pedido por proveedor.

     





Sitúese en cualquier lugar de la tabla. En Herramientas de tabla en la ficha Diseño en el grupo Herramientas haga clic en Convertir en rango. Un cuadro de diálogo aparecerá con la pregunta: Esto quita de forma permanente la definición de consulta de la hoja y convierte la tabla en un rango normal ¿Desea continuar? Haga clic en Aceptar. El filtro y la definición de tabla se borrarán y quedará un rango normal para aplicar los subtotales. Sitúese en cualquier celda perteneciente a la columna de IdProveedor. En la ficha Datos en el grupo Ordenar y filtrar haga clic en Ordenar de menor a mayor.

En la ficha Datos en el grupo Esquema haga clic en Subtotal. El cuadro de diálogo aparecerá. En la lista Para cada cambio en, seleccione IdProveedor. En la lista Usar función, seleccione Suma. En la lista Agregar subtotal, active las casillas UnidadesEnExistencia y UnidadesEnPedido. Haga clic en Aceptar. Los subtotales se agregarán y se mostrará el esquema. En la ficha Datos en el grupo Esquema haga clic en Subtotal. El cuadro de diálogo aparecerá. En la lista Para cada cambio en, seleccione IdProveedor. En la lista Usar función, seleccione Promedio. En la lista Agregar subtotal, active las casillas UnidadesEnExistencia y UnidadesEnPedido. Desactive la casilla Reemplazar subtotales actuales. Haga clic en Aceptar. Los subtotales se agregarán y se mostrará el esquema. 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á.

Página 80

Aster Mérida

   


Vaya a la carpeta de Prácticas que creo anteriormente en Mis Documentos. En el cuadro Nombre de archivo escriba Mod3 Lab3. En el cuadro Guardar como tipo verifique que diga Libro de Excel. De clic en el botón Guardar.

Página 81

Aster Mérida

Filtros Avanzados Los filtros avanzados permiten buscar ciertas filas que cumplan con condiciones establecidas y que no puedan ser aplicadas por medio de los autofiltros. De esta manera localiza la información deseada de manera rápida y confiable. Aplicar filtros es una forma rápida y fácil de buscar y trabajar con un subconjunto de datos de una lista. Una lista filtrada muestra sólo las filas que cumplen el criterio o los criterios que se especifiquen para una columna. Microsoft Excel proporciona dos comandos para aplicar filtros a las listas:  Autofiltro, que incluye filtrar por selección, para criterios simples  Filtro avanzado, para criterios más complejos A diferencia de ordenar, el filtrado no reorganiza las listas. El filtrado oculta temporalmente las filas que no desee mostrar. Cuando Excel filtra filas, puede modificar, aplicar formato, representar en gráficos e imprimir el subconjunto de la lista sin necesidad de reorganizarlo u ordenarlo. El comando Filtro avanzado permite filtrar una lista en su lugar, como el comando Autofiltro, pero no muestra listas desplegables para las columnas. En lugar de ello, tiene que escribir los criterios según los cuales desea filtrar los datos en un rango de criterios independiente situado sobre la lista. Un rango de criterios permite filtrar criterios más complejos.


Página 82

Aster Mérida

Si incluye dos o más condiciones en una sola columna, escriba los criterios en filas independientes, una directamente bajo otra. Para buscar datos que cumplan una condición en dos o más columnas, introduzca todos los criterios en la misma fila del rango de criterios. Para buscar datos que cumplan una condición de una columna o una condición de otra, introduzca los criterios en filas diferentes del rango. Para buscar filas que cumplan uno de dos conjuntos de condiciones, donde cada conjunto incluye condiciones para más de una columna, introduzca los criterios en filas independientes. Para buscar filas que cumplan más de dos conjuntos de condiciones, incluya columnas múltiples con el mismo título. Puede utilizar como criterio un valor calculado que sea el resultado de una fórmula. Cuando evalúa datos, Microsoft Excel no distingue entre caracteres en mayúscula y minúscula. Para aplicar un filtro avanzado, utilice el comando Avanzadas del grupo Ordenar y filtrar de la ficha Datos. El cuadro de diálogo Filtro Avanzado se mostrará. Indique si desea filtrar la lista sin moverla a otro lugar o filtrarla copiándola a otro lugar. En el cuadro Rango de la Lista indique el rango que ocupa la tabla o lista a ser filtrada (puede utilizar los botones contraer cuadro y expandir cuadro para realizarlo). En el cuadro Rango de Criterios especifique el rango de criterios para realizar el filtrado. En caso de haber activado Copiar lista a otro lugar en el cuadro Copiar a indique el lugar al cual se realizará la copia, que debe ser dentro de la misma hoja. En caso de haber registros idénticos puede activar Sólo registros únicos para descartar los repetidos. Para borrar el filtro haga clic en el comando Borrar del grupo Ordenar y filtrar de la ficha Datos. El rango de criterios debe tener rótulos de columna. Compruebe que existe al menos una fila vacía entre los valores de criterios y el rango. Se puede asignar a un rango el nombre Criterios y la referencia del rango aparecerá automáticamente en el cuadro Rango de criterios. También se puede definir el nombre Base de datos para el rango de datos que se deben filtrar y definir el nombre Extraer para el área donde desea pegar las filas, y estos rangos aparecerán automáticamente en los cuadros Rango de la lista y Copiar a, respectivamente


Página 83

Aster Mérida


Página 84

Aster Mérida


Página 85

Aster Mérida

Laboratorio MÓDULO 3. TRABAJANDO CON BASES DE DATOS Ejercicio 4. Filtros Avanzados En este ejercicio aplicará la herramienta de filtro avanzado para realizar la búsqueda de filas que cumplan determinados criterios. Tareas


1. Abra el libro llamado Mod3 Lab1, donde realizó la importación de datos. 2. Convierta la tabla a un rango para aplicar Filtros avanzados.

   

3. Cree el rango de criterios para conocer los productos cuyo proveedor sea el 1 y su existencia sea mayor a cero o bien del país Australia y con existencia mayor a cero, pero menor a 50.

   

4. Aplique el filtro avanzado.

 

    

 

 

5. Desactive el filtro.




Sitúese en cualquier lugar de la tabla. En Herramientas de tabla en la ficha Diseño en el grupo Herramientas haga clic en Convertir en rango. Un cuadro de diálogo aparecerá con la pregunta: Esto quita de forma permanente la definición de consulta de la hoja y convierte la tabla en un rango normal ¿Desea continuar? Haga clic en Aceptar. El filtro y la definición de tabla se borrarán y quedará un rango normal. Sitúese en B1 y escriba IdProveedor. Sitúese en C1 y escriba País. Sitúese en D1 y escriba UnidadesEnExistencia. Sitúese en E1 y escriba UnidadesEnExistencia. NOTA: Cuide que los rótulos estén iguales a los de la lista. Puede utilizar copiar y pegar para asegurarse de que sea así. Sitúese en B2 y escriba 1. Sitúese en C3 y escriba Australia. Sitúese en D2 y escriba >0. Sitúese en D3 y escriba >0. Sitúese en E3 y escriba <50. Sitúese en cualquier celda perteneciente a la lista. En la ficha Datos en el grupo Ordenar y filtrar haga clic en Avanzadas. El cuadro de diálogo aparecerá. En Acción verifique que esté activada la casilla Filtrar la lista sin moverla a otro lugar. En el cuadro Rango de lista, verifique que se encuentre el rango A7:J77, en caso contrario haga clic en el botón Contraer cuadro y seleccione el rango correspondiente, para regresar al cuadro haga clic en el botón Expandir cuadro. En el cuadro Rango de Criterios haga clic en el botón Contraer cuadro y seleccione el rango B1:E3. Haga clic en el botón Expandir cuadro para regresar. Haga clic en Aceptar. El filtro estará aplicado, visualizándose solamente las 7 filas que cumplen los criterios. En la ficha Datos en el grupo Ordenar y filtrar haga clic en Borrar.

Página 86

Aster Mérida


    


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á. Vaya a la carpeta de Prácticas que creo anteriormente en Mis Documentos. En el cuadro Nombre de archivo escriba Mod3 Lab4. En el cuadro Guardar como tipo verifique que diga Libro de Excel. De clic en el botón Guardar.

Página 87

Aster Mérida

Funciones de Bases de Datos La información contenida en una base de datos por lo común es extensa y encontrar un dato en ella puede llevar tiempo. Las funciones de bases de datos permiten localizar datos requeridos y que cumplan ciertos criterios facilitando de esta manera el trabajo. Las funciones de bases de datos con las que Excel cuenta son: Función

Descripción

BDPROMEDIO

Devuelve el promedio de las entradas seleccionadas en la base de datos

BDCONTAR

Cuenta el número de celdas que contienen números en la base de datos

BDCONTARA

Cuenta el número de celdas no vacías de la base de datos

BDEXTRAER

Extrae de la base de datos un único registro que cumple los criterios especificados

BDMAX

Devuelve el valor máximo de las entradas seleccionadas de la base de datos

BDMIN

Devuelve el valor mínimo de las entradas seleccionadas de la base de datos


Página 88

Aster Mérida

BDPRODUCTO

Multiplica los valores de un campo concreto de registros de la base de datos que cumplen los criterios especificados

BDDESVEST

Calcula la desviación estándar a partir de una muestra de entradas seleccionadas en la base de datos

BDDESVESTP

Calcula la desviación estándar en función de la población total de las entradas seleccionadas de la base de datos

BDSUMA

Agrega los números de la columna de campo de los registros de la base de datos que cumplen los criterios

BDVAR

Calcula la varianza a partir de una muestra de entradas seleccionadas de la base de datos

BDVARP

Calcula la varianza a partir de la población total de entradas seleccionadas de la base de datos

La sintaxis para una función de Base de Datos en general es: Nombre_Función(base_de_datos;nombre_de_campo;criterios) Donde: Base_de_datos. Es el rango de celdas que compone la lista o base de datos. Una base de datos es una lista de datos relacionados en la que las filas de información son registros y las columnas de datos, campos. La primera fila de la lista contiene los rótulos de cada columna. Nombre_de_campo. Indica qué columna se utiliza en la función. Escriba el rótulo de la columna entre comillas o un número (sin las comillas) que represente la posición de la columna en la lista: 1 para la primera columna, 2 para la segunda y así sucesivamente. Criterios. Es el rango de celdas que contiene las condiciones especificadas. Puede utilizar cualquier rango en el argumento Criterios mientras éste incluya por lo menos un rótulo de columna y al menos una celda debajo del rótulo de columna en la que se pueda especificar una condición de columna.


Página 89

Aster Mérida

Funciones de Búsqueda Buscarv y Buscarh permiten recuperar la información de acuerdo a un dato clave y visualizar en la celda que le indique, de esta forma obtiene los datos necesitados rápidamente. BuscarV Busca un valor específico en la columna más a izquierda de una matriz y devuelve el valor en la misma fila de una columna especificada en la tabla. BuscarH Busca un valor en la fila superior de una tabla o una matriz de valores y, a continuación, devuelve un valor en la misma columna de una fila especificada en la tabla o matriz. Use BUSCARH cuando los valores de comparación se encuentren en una fila en la parte superior de una tabla de datos y desee encontrar información que se encuentre dentro de un número especificado de filas. Use BUSCARV cuando los valores de comparación se encuentren en una columna a la izquierda o de los datos que desee encontrar. Para la fórmula el valor buscado es el valor que se desea buscar en la primera fila o bien primera columna de la tabla. La matriz es la tabla donde se encuentran los datos. Indicador de filas (o columnas, según sea el caso) es donde se encuentra en valor a devolver si valor buscado coincide, y por último ordenado nos especifica si la lista esta ordenada o no.


Página 90

Aster Mérida


Página 91

Aster Mérida


Página 92

Aster Mérida

Laboratorio MÓDULO 3. TRABAJANDO CON BASES DE DATOS Ejercicio 5. Funciones de Búsqueda y de Bases de Datos En este ejercicio utilizará las funciones de búsqueda y de bases de datos sobre una lista. Tareas


1. Abra el libro llamado Mod3 Lab1, donde realizó la importación de datos. 2. Cree el rango de criterios para conocer el nombre de la compañía y unidades en existencia de un producto determinado.

  

Sitúese en B1 y escriba IdProducto. Sitúese en C1 y escriba NombreCompañia. Sitúese en D1 y escriba UnidadesEnExistencia. NOTA: Los rótulos no necesariamente deben estar iguales a los de la lista.

3. Utilice la función BuscarV para extraer de la lista el nombre de la compañía.

 

Sitúese en la celda C2. En la ficha Fórmulas en el grupo Biblioteca de funciones despliegue el botón Búsqueda y referencia y haga clic en BuscarV. El cuadro de diálogo Argumentos de función aparecerá. Haga clic en el cuadro Valor_buscado y seleccione la celda B2. Haga clic en el cuadro Matriz_buscar_en y seleccione el rango de la lista (A8:J77) sin los encabezados. Es posible que al seleccionarlo aparezca el nombre Tabla_Consulta_desde_MS_Access_Database. En el cuadro Indicador_columnas teclee 4. En el cuadro Ordenado escriba 0. Haga clic en Aceptar. La fórmula se insertará y mostrará el error #N/A.

      4. Utilice la función BuscarV para extraer de la lista las existencias.

 

   

Sitúese en la celda D2. En la ficha Fórmulas en el grupo Biblioteca de fórmulas despliegue el botón Búsqueda y referencia y haga clic en BuscarV. El cuadro de diálogo Argumentos de función aparecerá. Haga clic en el cuadro Valor_buscado y seleccione la celda B2. Haga clic en el cuadro Matriz_buscar_en y seleccione el rango de la lista (A8:J77) sin los encabezados. Es posible que al seleccionarlo aparezca el nombre Tabla_Consulta_desde_MS_Access_Database. En el cuadro Indicador_columnas teclee 9. En el cuadro Ordenado escriba 0. Haga clic en Aceptar. La fórmula se insertará y mostrará el error #N/A.

  

Sitúese en B2 y teclee 5. Verifique que en la celda C2 aparezca New Orleans Cajun Delights. Verifique que en la celda D2 aparezca 0.

 

5. Extraiga la información correspondiente al


Página 93

Aster Mérida

producto 5.



Si desea puede verificar con otro producto.

6. Sitúese en la hoja 2 y cree una fila de rótulos para encontrar cuantos productos son de Australia y con existencia igual a cero o de Japón con existencia menor a 30.

  

Haga clic en la etiqueta de la hoja 2 para situarse en ella. Sitúese en la celda A7 y escriba País. Sitúese en la celda B7 y escriba UnidadesEnExistencia. NOTA: Cuide que los rótulos estén iguales a los de la lista. Puede utilizar copiar y pegar para asegurarse de que sea así. Sitúese en la celda A8 y escriba Australia. Sitúese en la celda B8 y teclee 0. Sitúese en la celda A9 y escriba Japón. Sitúese en la celda B9 y teclee <30.

7. Utilice la función BDContara para saber la cantidad de productos que cumplen las condiciones.

 

   

      


    


Sitúese en B12. En la ficha fórmulas en el grupo Biblioteca de funciones haga clic en Insertar función. El cuadro de diálogo Insertar función aparecerá. En la lista O seleccionar una categoría elija Base de datos. De la lista Seleccionar una función, haga clic sobre BDCONTARA. Haga clic en Aceptar. El cuadro de diálogo Argumentos de función aparecerá. En el cuadro Base_de_datos seleccione la lista que se encuentra en la hoja 1. Incluya los rótulos. Es posible que al seleccionarlo aparezca el nombre Tabla_Consulta_desde_MS_Access_Database[#Todo]. En el cuadro Nombre_de_campo escriba 1. En el cuadro Criterios seleccione el rango A7:B9 de la hoja2. Haga clic en Aceptar. Verá como resultado 3. 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á. Vaya a la carpeta de Prácticas que creo anteriormente en Mis Documentos. En el cuadro Nombre de archivo escriba Mod3 Lab5. En el cuadro Guardar como tipo verifique que diga Libro de Excel. De clic en el botón Guardar.

Página 94

Aster Mérida

MÓDULO 4: Tablas Dinámicas


Página 95

Aster Mérida

Tablas Dinámicas

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

Crear y modificar Tablas Diná Dinámicas.

•

Crear y modificar Tablas Diná Dinámicas usando fuentes externas.


Página 96

Aster Mérida

Creación de Tablas Dinámicas Los datos que tiene en una tabla puede manipularlos de tal forma que produzcan un cruce de información que arroje datos requeridos para ciertos análisis. Esto se logra con la herramienta de tablas dinámicas que ayudan a realizar dicho cruce con la ventaja de poder modificarla las veces que desee y así obtener la información deseada. Una vez realizada una tabla dinámica es posible crear a partir de ella un gráfico dinámico que tal forma que cualquier cambio a ella sea posible reflejarlo. Si la tabla origen es modificada basta con actualizar la tabla para poder mantenerla actualizada con los cambios realizados. Un informe de tabla dinámica es una tabla interactiva que combina y compara rápidamente grandes volúmenes de datos. Podrá girar las filas y las columnas para ver diferentes resúmenes de los datos de origen, y mostrar los detalles de determinadas áreas de interés. Utilice un informe de tabla dinámica cuando desee comparar totales relacionados, sobre todo si tiene una lista larga de números para resumir y desea realizar comparaciones distintas con cada número. Puesto que los informes de tabla dinámica son interactivos, puede cambiar la presentación de los datos para ver más detalles o calcular diferentes resúmenes, como recuentos o promedios. En los informes de tabla dinámica, cada columna o campo de los datos de origen se convierte en un campo de tabla dinámica que resume varias filas de información.


Página 97

Aster Mérida

Para crear una tabla dinámica seleccione una celda de un rango de celdas o coloque el punto de inserción dentro de una tabla de Excel. Asegúrese de que el rango de celdas tiene encabezados de columna. En la ficha Insertar, en el grupo Tablas, haga clic en Tabla dinámica y, a continuación, en Tabla dinámica. Aparecerá el cuadro de diálogo Crear tabla dinámica. Seleccione los datos que desea analizar. Para ello haga clic en Seleccione una tabla o rango. En el cuadro Tabla o rango escriba el rango de celdas o la referencia del nombre de tabla. Si seleccionó una celda de un rango de celdas o si el punto de inserción estaba en una tabla antes de iniciar el asistente, el rango de celdas o la referencia del nombre de tabla se muestra en el cuadro. Puede seleccionar un rango en otra hoja de cálculo del mismo libro o de otro libro. Elija dónde desea colocar el informe de tabla dinámica. Para poner el informe de tabla dinámica en una hoja de cálculo nueva que empiece por la celda A1, haga clic en Nueva hoja de cálculo. Para poner el informe de tabla dinámica en una hoja de cálculo existente, seleccione Hoja de cálculo existente y, a continuación, escriba la primera celda del rango de celdas donde desea situar el informe de tabla dinámica. Haga clic en Aceptar. Un informe de tabla dinámica vacío se agregará a la ubicación que especificó en la Lista de campos de tabla dinámica que se muestra de modo que puede comenzar a agregar campos, crear un diseño y personalizar el informe de tabla dinámica. Si desea puede crear un informe de tabla dinámica con gráfico dinámico, para ello en la ficha Insertar, en el grupo Tablas, haga clic en Tabla dinámica y, a continuación, en Gráfico dinámico. El procedimiento es el mismo que para crear sólo la tabla dinámica. Una vez creada una tabla dinámica puede crear a partir de ella un gráfico, para ello teniendo seleccionada la tabla dinámica utilice el grupo Gráficos en la ficha Insertar.


Página 98

Aster Mérida

Modificación de Tablas Dinámicas Después de crear un informe de tabla dinámica o gráfico dinámico, utilice la lista de campos de tabla dinámica para agregar campos. Si desea cambiar un informe de tabla dinámica o gráfico dinámico, utilice la lista de campos para ordenar y quitar campos. De manera predeterminada, la lista de campos de tabla dinámica contiene dos secciones: una sección de campos en la parte superior para agregar o quitar campos, y una sección de diseño en la parte inferior para volver a organizar campos y ajustar su posición. Puede acoplar la lista de campos de tabla dinámica en algún lado de la ventana y cambiarle el tamaño horizontalmente. También puede desacoplarla, en cuyo caso podrá cambiarle el tamaño tanto vertical como horizontalmente. Si no aparece la lista de campos de tabla dinámica, asegúrese de hacer clic en el informe de tabla dinámica o gráfico dinámico. Si aún así no aparece, en un informe de tabla dinámica, en la ficha Opciones, en el grupo Mostrar u ocultar, haga clic en Lista de campos, y en un informe de gráfico dinámico, en la ficha Analizar, en el grupo Datos, haga clic en Lista de campos. Si no aparecen los campos que desea utilizar en la lista de campos, actualice el informe de tabla dinámica o gráfico dinámico para que se muestren los nuevos campos, campos calculados, medidas, medidas calculadas o dimensiones que haya agregado desde la última operación.


Página 99

Aster Mérida

Para agregar un campo basta con seleccionar los campos que desea agregar y arrastrarlos hacia el área de tabla correspondiente. Para quitar un campo selecciónelo de la tabla y arrástrelo hacia fuera de ella. Cuando se tiene seleccionada una tabla dinámica aparecerán la ficha herramientas de tabla dinámica que constan de dos cintas de opciones: La cinta Opciones presenta los siguientes grupos:  Tabla dinámica. Permite cambiar el nombre de la tabla dinámica, así como editar sus opciones para diseño y formato, totales y filtro, mostrar, impresión y datos.  Campo activo. Muestra el campo con el cual se está trabajando, así como su configuración.  Agrupar. Permite establecer un agrupamiento de campos  Ordenar.  Datos. Permite actualizar y cambiar el origen de datos de la tabla.  Acciones. Permite borrar, seleccionar y mover la tabla dinámica y sus elementos.  Herramientas. Permite insertar gráficos dinámicos, crear y modificar campos y elementos calculados y trabajar con las herramientas OLAP.  Mostrar u ocultar. Permite mostrar u ocultar la lista de campo, los botones y los encabezados de campos. La cinta Diseño presenta los siguientes grupos:  Diseño. Permite mostrar u ocultar subtotales y totales generales, el diseño de informe y de filas en blanco.  Opciones de estilo de tabla dinámica. Permite activar o desactivar encabezados de fila, encabezados de columna, filas con bandas, columnas con bandas.  Estilos de tabla dinámica. Permite aplicar estilos rápidos a la tabla dinámica, crear nuevos y eliminar.


Página 100

Aster Mérida

Creación de Gráficos Dinámicos Para crear una gráfica de nuestra tabla dinámica deberemos hacer clic en el botón Gráfico dinámico de la pestaña 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.


Página 101

Aster Mérida

Creación de Tablas Dinámicas con Datos Externos En la ficha Insertar, en el grupo Tablas, haga clic en Tabla dinámica y, a continuación, en Tabla dinámica. Aparecerá el cuadro de diálogo Crear tabla dinámica. Seleccione los datos que desea analizar. Para ello haga clic en Utilice una fuente de datos externa. Haga clic en Elegir conexión. Se mostrará el cuadro de diálogo Conexiones existentes. Vaya a la lista desplegable Mostrar y seleccione la categoría para la cual desea elegir una conexión o seleccione Todas las conexiones existentes. Seleccione una conexión del cuadro Seleccionar una conexión y haga clic en Abrir. Elija dónde desea colocar el informe de tabla dinámica. Para poner el informe de tabla dinámica en una hoja de cálculo nueva que empiece por la celda A1, haga clic en Nueva hoja de cálculo. Para poner el informe de tabla dinámica en una hoja de cálculo existente, seleccione Hoja de cálculo existente y, a continuación, escriba la primera celda del rango de celdas donde desea situar el informe de tabla dinámica. Haga clic en Aceptar. Si elige una conexión de la categoría Conexiones en este libro, estará reutilizando o compartiendo una conexión existente. Si elige una conexión de las categorías Archivos de conexión en la red o Archivos de conexión en este equipo, el archivo de conexión se copia en el libro como una nueva conexión de libro y se usa como la nueva conexión para el informe de tabla dinámica.


Página 102

Aster Mérida


Página 103

Aster Mérida


Página 104

Aster Mérida

Laboratorio MÓDULO 4. TABLAS DINÁMICAS Ejercicio 1. Tablas Dinámicas En este ejercicio creará una tabla dinámica, la editará y posteriormente creará un gráfico dinámico basado en ella. Tareas


1. Abra el libro llamado Tablas dinámicas. 2. Genere una tabla dinámica en una hoja nueva a partir de la lista que se encuentra en la hoja Tablas.

     

3. Utilice la tabla dinámica para conocer cuantas Unidades por canal se vendieron cada año.

    

4. Modifique la tabla para saber la información por año y por trimestre.



5. Filtre la información para que aparezca solamente la correspondiente a los catálogos 26059 y 50724.





   


Haga clic en la etiqueta de la hoja Tablas. Haga clic en una celda dentro de la lista. En la ficha Insertar en el grupo Tablas haga clic en el botón Tabla Dinámica. El cuadro de diálogo Crear tabla dinámica aparecerá. En la sección Seleccione una tabla o un rango verifique que se encuentre el rango de A1:F170, de lo contrario selecciónelo. En la sección Elija dónde desea colocar el informe de tabla dinámica seleccione Nueva hoja de cálculo. Haga clic en Aceptar. Una nueva hoja se insertará con la tabla dinámica. En el panel lista de campos de la tabla dinámica en la lista Seleccionar campos para agregar al informe haga clic en Año y arrástrelo hacia la sección Rótulos de fila. En el panel lista de campos de la tabla dinámica en la lista Seleccionar campos para agregar al informe haga clic en Canal y arrástrelo hacia la sección Rótulos de columna. En el panel lista de campos de la tabla dinámica en la lista Seleccionar campos para agregar al informe haga clic en Unidades y arrástrelo hacia la sección Valores. En el panel lista de campos de la tabla dinámica en la lista Seleccionar campos para agregar al informe haga clic en No Catálogo y arrástrelo hacia la sección Filtro de informe. Verá la tabla dinámica con los valores resumen ya creados. En el panel lista de campos de la tabla dinámica en la lista Seleccionar campos para agregar al informe haga clic en Trimestre y arrástrelo hacia la sección Rótulos de fila debajo del campo Año. Verifique que la información de la tabla ha cambiado. En el área correspondiente a Filtro de informe despliegue la pestaña de filtro y active la casilla Seleccionar varios elementos. Desactive la casilla Todas. Active las casillas correspondientes a 26059 y 50724. Haga clic en Aceptar. Verifique el cambio de información.

Página 105

Aster Mérida

6. Quite el filtro de catálogos para que aparezca de nuevo la información de todos.



7. Muestre en la tabla dinámica los valores máximos en lugar de los totales.

 

8. Muestre la tabla detalle correspondiente al trimestre 4 del año 98.



9. Aplique un formato a la tabla dinámica.



En Herramientas de tabla dinámica en la ficha Diseño en el grupo Estilos de tabla dinámica haga clic en Más estilos y elija el de su preferencia para aplicar a la tabla.

10. Genere un gráfico dinámico a partir de la tabla dinámica creada.



En Herramientas de tabla dinámica en la ficha Opciones en el grupo Herramientas haga clic en Gráfico Dinámico. El cuadro de diálogo Insertar gráfico aparecerá. En los tipos seleccione Columnas y en los subtipos seleccione Columna agrupada 3D. Haga clic en Aceptar. El gráfico dinámico aparecerá en la misma hoja.

 

 



  11. Guarde el libro creado con el nombre Mod4 Lab1.

    


En el área correspondiente a Filtro de informe despliegue la pestaña de filtro y active la casilla Todas. Haga clic en Aceptar. Verifique el cambio de información. Sobre una celda de la tabla dinámica haga clic derecho. En el menú que se presenta elija Configuración de campo de valor. El cuadro de diálogo se abrirá. En la lista Resumir campo de valor por, haga clic en Máx. Haga clic en Aceptar, verifique el cambio de información. En la celda E9 correspondiente al Total general del trimestre 4 del año 1998 haga doble clic. Se insertará una nueva hoja con los valores de detalle.


Página 106

Aster Mérida

MÓDULO 5: Fórmulas y Funciones


Página 107

Aster Mérida


Página 108

Aster Mérida

Funciones: Lógicas Las fórmulas lógicas se diferencian de las fórmulas, en que estas últimas devuelven un resultado numérico, en tanto que las primeras entregan un resultado lógico, es decir: verdadero o falso. Las más simples sirven para hacer la comparación entre el contenido numérico de dos celdas utilizando los operadores lógicos que se muestran en la siguiente tabla:

Estos operadores se llaman binarios ya que la operación se realiza entre dos operandos, para Excel estos operandos son el contenido numérico de dos celdas, por lo tanto podemos compararlas.


Página 109

Aster Mérida

Entre las Funciones Lógicas que Excel nos permite utilizar están las siguientes: FUNCIÓN DESCRIPCIÓN Y

Devuelve VERDADERO si todos sus argumentos son VERDADERO

FALSO

Devuelve el valor lógico FALSO

SI

Especifica una prueba lógica que realizar

SI.ERROR

Devuelve un valor que se especifica si una fórmula lo evalúa como un error; de lo contrario, devuelve el resultado de la fórmula

NO

Invierte el valor lógico del argumento

O

Devuelve VERDADERO si cualquier argumento es VERDADERO

VERDADERO

Devuelve el valor lógico VERDADERO

Función Y Descripción: Devuelve VERDADERO si todos los argumentos se evalúan como VERDADERO; devuelve FALSO si uno o más argumentos se evalúan como FALSO. Un uso común de la función Y es expandir la utilidad de otras funciones que realizan pruebas lógicas. Por ejemplo, la función SI realiza una prueba lógica y, luego, devuelve un valor si la prueba se evalúa como VERDADERO y otro valor si la prueba se evalúa como FALSO. Con la función Y como argumento prueba_lógica de la función SI, puede probar varias condiciones diferentes en lugar de sólo una. Sintaxis Y(valor_lógico1; [valor_lógico2]; ...) La sintaxis de la función Y tiene los siguientes argumentos: Y(valor_lógico1; [valor_lógico2]; ...)

 

valor_lógico1 Obligatorio. La primera condición que desea probar se puede evaluar como VERDADERO o FALSO. valor_lógico2, ... Opcional. Las condiciones adicionales que desea probar se pueden evaluar como VERDADERO o FALSO, hasta un máximo de 255 condiciones.

Observaciones  Los argumentos deben evaluarse como valores lógicos, como VERDADERO o FALSO, o bien deben ser matrices o referencias que contengan valores lógicos.  Si un argumento de matriz o de referencia contiene texto o celdas vacías, esos valores se pasarán por alto.  Si el rango especificado no contiene valores lógicos, la función Y devuelve el valor de error #¡VALOR!.


Página 110

Aster Mérida

Ejemplo 1 El ejemplo será más fácil de entender si lo copia en una hoja de cálculo en blanco. A

B

C

1

Fórmula

Descripción

Resultado

2

=Y(VERDADERO; VERDADERO)

Todos los argumentos son VERDADERO

VERDADERO

3

=Y(VERDADERO; FALSO)

Un argumento es FALSO

FALSO

4

=Y(2+2=4; 2+3=5)

Todos los argumentos se evalúan como VERDADERO

VERDADERO


Página 111

Aster Mérida

Ejemplo 2 El ejemplo será más fácil de entender si lo copia en una hoja de cálculo en blanco. A

B

C

1

Datos

2

50

3

104

4

Fórmula

Descripción

Resultado

=Y(1
Muestra VERDADERO si el número de la celda A2 es un número entre 1 y 100. De lo contrario, muestra FALSO.

VERDADERO

6

=SI(Y(1
Muestra el número en la celda A3, si es un número entre 1 y 100. De lo contrario, muestra el mensaje "El valor está fuera del rango".

El valor está fuera del rango.

Muestra el número en la celda A2, si es un número entre 1 y 100. De lo contrario, se muestra un mensaje.

50

7

=SI(Y(1
5

Función FALSO Devuelve el valor lógico FALSO. Sintaxis FALSO( ) Observación También puede escribir la palabra FALSO directamente en la hoja de cálculo o en la fórmula y Microsoft Excel la interpreta como el valor lógico FALSO. Función SI Devuelve un valor si la condición especificada es VERDADERO y otro valor si dicho argumento es FALSO. Utilice SI para realizar pruebas condicionales en valores y fórmulas.


Página 112

Aster Mérida

Sintaxis SI(prueba_lógica;valor_si_verdadero;valor_si_falso)

Prueba_lógica: es cualquier valor o expresión que pueda evaluarse como VERDADERO o FALSO. Por ejemplo, A10=100 es una expresión lógica; si el valor de la celda A10 es igual a 100, la expresión se evalúa como VERDADERO. De lo contrario, se evaluará como FALSO. Este argumento puede utilizar cualquier operador de comparación. Valor_si_verdadero: es el valor que se devuelve si el argumento prueba_lógica es VERDADERO. Por ejemplo, si este argumento es la cadena de texto "Dentro de presupuesto" y el argumento prueba_lógica se evalúa como VERDADERO, la función SI muestra el texto "Dentro de presupuesto". Si el argumento prueba_lógica es VERDADERO y el argumento valor_si_verdadero está en blanco, este argumento devuelve 0 (cero). Para mostrar la palabra VERDADERO, utilice el valor lógico VERDADERO para este argumento. Valor_si_verdadero puede ser otra fórmula. Valor_si_falso: es el valor que se devuelve si el argumento prueba_lógica es FALSO. Por ejemplo, si este argumento es la cadena de texto "Presupuesto excedido" y el argumento prueba_lógica se evalúa como FALSO, la función SI muestra el texto "Presupuesto excedido". Si el argumento prueba_lógica es FALSO y se omite valor_si_falso, (es decir, después de valor_si_verdadero no hay ninguna coma), se devuelve el valor lógico FALSO. Si prueba_lógica es FALSO y valor_si_falso está en blanco (es decir, después de valor_si_verdadero hay una coma seguida por el paréntesis de cierre), se devuelve el valor 0 (cero). Valor_si_falso puede ser otra fórmula. Observaciones  Es posible anidar hasta 64 funciones SI como argumentos valor_si_verdadero y valor_si_falso para crear pruebas más complicadas. (Vea el ejemplo 3 para ver una muestra de funciones SI anidadas). Como alternativa, para comprobar muchas condiciones, plantéese usar las funciones BUSCAR, BUSCARV o BUSCARH. (Vea el ejemplo 4 para obtener una muestra de la función BUSCARH.)  Cuando los argumentos valor_si_verdadero y valor_si_falso se evalúan, la función SI devuelve el valor devuelto por la ejecución de las instrucciones.  Si uno de los argumentos de la función SI es una matriz, cada elemento de la matriz se evaluará cuando se ejecute la instrucción SI.  Microsoft Excel proporciona funciones adicionales que se pueden utilizar para analizar los datos en función de una condición. Por ejemplo, para contar el número de veces que una cadena de texto o un número aparecen dentro de un rango de celdas, utilice las funciones de hoja de cálculo CONTAR.SI y CONTAR.SI.CONJUNTO. Para calcular una suma basándose en una cadena de texto o un número de un rango, utilice las funciones SUMAR.SI y SUMAR.SI.CONJUNTO.


Página 113

Aster Mérida

Ejemplo 1 Es más sencillo comprender el ejemplo si se copia en una hoja de cálculo en blanco. A 1 2

Datos 50 Fórmula

Descripción (resultado)

=SI(A2<=100;"Dentro de presupuesto";"Presupuesto excedido")

Si el número anterior es igual o menor que 100, la fórmula muestra "Dentro de presupuesto". De lo contrario, la función mostrará "Presupuesto excedido" (Dentro de presupuesto)

=SI(A2=100;SUMA(B5:B15);"")

Si el número anterior es 100, se calcula el rango B5:B15. En caso contrario, se devuelve texto vacío ("") ()

Ejemplo 2 Es más sencillo comprender el ejemplo si se copia en una hoja de cálculo en blanco. A

B

1

Gastos reales

Gastos previstos

2

1500

900

3

500

900

4

500

925

Fórmula


=SI(A2>B2;"Presupuesto excedido";"Aceptar")

Comprueba si la primera fila sobrepasa el presupuesto (Presupuesto excedido)

=SI(A3>B3;"Presupuesto excedido";"Aceptar")

Comprueba si la segunda fila sobrepasa el presupuesto (Aceptar)


Página 114

Aster Mérida

Función SI.ERROR Devuelve un valor que se especifica si una fórmula lo evalúa como un error; de lo contrario, devuelve el resultado de la fórmula. Utilice la función SI.ERROR para interceptar y controlar errores en una fórmula. Sintaxis SI.ERROR(valor,valor_si_error) Valor: es el argumento en el que se busca un error. Valor_si_error: es el valor que se devuelve si la fórmula lo evalúa como error. Se evalúan los tipos siguientes de error: #N/A, #¡VALOR!, #¡REF!, #¡DIV/0!, #¡NUM!, #¿NOMBRE? o #¡NULO! Observaciones  Si valor o valor_si_error están en una celda vacía, SI.ERROR los trata como un valor de cadena vacía ("").  Si valor es una fórmula de matriz, SI.ERROR devuelve una matriz de resultados para cada celda del rango especificado en el valor. Vea el segundo ejemplo siguiente.


Página 115

Aster Mérida

Ejemplo: Interceptar errores de división utilizando una fórmula regular Es más sencillo comprender el ejemplo si se copia en una hoja de cálculo en blanco. A

B

1

Cuota

Unidades vendidas

2

210

35

3

55

0

4

23

5

Fórmula


6

=SI.ERROR(A2/B2; "Error en el cálculo")

Comprueba si hay un error en la fórmula en el primer argumento (divide 210 por 35), no encuentra ningún error y devuelve los resultados de la fórmula (6).


Comprueba si hay un error en la fórmula en el primer argumento (divide 55 por 0), encuentra un error de división por 0 y devuelve valor_si_error (Error en el cálculo).


Comprueba si hay un error en la fórmula en el primer argumento (divide "" por 23), no encuentra ningún error y devuelve los resultados de la fórmula (0).

7

8

9 10


Página 116

Aster Mérida

Ejemplo: Interceptar errores de división utilizando una fórmula de matriz Es más sencillo comprender el ejemplo si se copia en una hoja de cálculo en blanco.

1 2 3 4 5 6 7 8 9

A

B

C

Cuota

Unidades vendidas

Relación

210

35

=SI.ERROR(A2:A4/B2:B4; "Error en el cálculo")

55

0 23

Fórmula


=C2

Comprueba si hay un error en la fórmula en el primer argumento del primer elemento de la matriz (A2/B2 o divide 210 por 35), no encuentra ningún error y devuelve los resultados de la fórmula (6).

=C3

Comprueba si hay un error en la fórmula en el primer argumento del segundo elemento de la matriz (A3/B3 o divide 55 por 0), encuentra un error de división por 0 y devuelve valor_si_error (Error en el cálculo).

=C4

Comprueba si hay un error en la fórmula en el primer argumento del tercer elemento de la matriz (A4/B4 o divide "" por 23), no encuentra ningún error y devuelve los resultados de la fórmula (0).

NOTA: La fórmula del ejemplo debe especificarse como fórmula de matriz. Después de copiar el ejemplo en una hoja de cálculo en blanco, seleccione el rango C2:C4, presione F2 y, a continuación, presione CTRL+MAYÚS+ENTRAR.


Página 117

Aster Mérida

Función NO Invierte el valor lógico del argumento. Use NO cuando desee asegurarse de que un valor no sea igual a otro valor específico. Sintaxis NO(valor_lógico) Valor_lógico: es un valor o expresión que puede evaluarse como VERDADERO o FALSO. Observación Si valor_lógico es FALSO, NO devuelve VERDADERO; si valor_lógico es VERDADERO, NO devuelve FALSO. Ejemplo El ejemplo será más fácil de entender si lo copia a una hoja de cálculo en blanco. A

B

1

Fórmula


2

=NO(FALSO)

Invierte FALSO (VERDADERO)

3

=NO(1+1=2)

Invierte una ecuación que se evalúa como VERDADERO (FALSO)

Función O Devolverá VERDADERO si alguno de los argumentos es VERDADERO; devolverá FALSO si todos los argumentos son FALSO. Sintaxis O(valor_lógico1;valor_lógico2; ...) Valor_lógico1, Valor_lógico2, ... son de 1 a 255 condiciones que se desea comprobar y que pueden tener el resultado de VERDADERO o FALSO. Observaciones  Los argumentos deben evaluarse como valores lógicos, como VERDADERO O FALSO, o bien en matrices o referencias que contengan valores lógicos.  Si un argumento matricial o de referencia contiene texto o celdas vacías, dichos valores se pasarán por alto.  Si el rango especificado no contiene valores lógicos, la función O devuelve el valor de error #¡VALOR!.


Página 118

Aster Mérida



Puede utilizar la fórmula de matriz O para comprobar si un valor aparece en una matriz. Para especificar una fórmula de matriz, presione CTRL+MAYÚS+ENTRAR.

Ejemplo El ejemplo será más fácil de entender si lo copia a una hoja de cálculo en blanco A

B

1

Fórmula


2

=O(VERDADERO)

Un argumento es VERDADERO (VERDADERO)

3

=O(1+1=1;2+2=5)

Todos los argumentos se evalúan como FALSO (FALSO).

4

=O(VERDADERO;FALSO;VERDADERO)

Al menos un argumento es VERDADERO (VERDADERO).

Función VERDADERO Devuelve el valor lógico VERDADERO. Sintaxis VERDADERO( ) Observación El valor VERDADERO puede especificarse directamente en las celdas y fórmulas sin necesidad de usar esta función. La función VERDADERO se proporciona principalmente por su compatibilidad con otros programas para hojas de cálculo.


Página 119

Aster Mérida

Funciones Lógicas Anidadas En Excel es muy común la utilización de varias fórmulas dentro de una misma celda, es decir, la utilización de fórmulas compuestas de otras fórmulas o formulas anidadas. Una de las fórmulas que aparece anidada más comúnmente es la función lógica “=SI()”. Para combinar fórmulas, no necesariamente tienen que pertenecer al mismo grupo, por ejemplo se pueden combinar fórmulas de búsqueda con fórmulas de texto, fórmulas matemáticas con fórmulas lógicas, etc. La función SI se puede combinar para crear estructuras anidadas del tipo: =SI (condición1; respuesta verdad1; SI (condición2; respuesta verdad2; respuesta falso2)) =SI (condición1; SI (condición2; respuesta verdad2; respuesta falso2); SI(condición3; respuesta verdad3; respuesta falso3)) Ejemplo de aplicación: Un profesor necesita que las notas de sus estudiantes sean cualitativas, teniendo un listado con notas cuantitativas, para ello tiene en cuanta lo siguiente:  -Si la nota es igual a 10 debe aparece el texto “Sobresaliente”  -Si la nota está entre 7 y 9 debe aparece el texto “Bueno”  -Si la nota está entre 6 y 7 debe aparece el texto “Aceptable”  -Si la nota es menor o igual que 5 debe aparece el texto “Deficiente”


Página 120

Aster Mérida


Página 121

Aster Mérida

Ejemplo 3 Es más sencillo comprender el ejemplo si se copia en una hoja de cálculo en blanco. A 1

Puntuación

2

45

3

90

4

78 Fórmula


=SI(A2>89,"A",SI(A2>79;"B";SI(A2>69;"C";SI(A2>59;"D";"F"))))

Asigna una puntuación de una letra al primer resultado (F)

=SI(A3>89;"A";SI(A3>79;"B";SI(A3>69;"C";SI(A3>59;"D";"F"))))

Asigna una puntuación de una letra al segundo resultado (A)

=SI(A4>89;"A";SI(A4>79;"B";SI(A4>69;"C";SI(A4>59;"D";"F"))))

Asigna una puntuación de una letra al tercer resultado (C)

En el ejemplo anterior, la segunda instrucción SI representa también el argumento valor_si_falso de la primera instrucción SI. De manera similar, la tercera instrucción SI es el argumento valor_si_falso de la segunda instrucción SI. Por ejemplo, si el primer argumento prueba_lógica (Promedio>89) es VERDADERO, se devuelve el valor "A". Si el primer argumento prueba_lógica es FALSO, se evalúa la segunda instrucción SI y así sucesivamente.


Página 122

Aster Mérida

Las letras de puntuación se asignan a números utilizando la siguiente clave. SI LA PUNTUACIÓN ES

LA FUNCIÓN DEVUELVE

Mayor que 89

A

De 80 a 89

B

De 70 a 79

C

De 60 a 69

D

Menor que 60

F

Otra aplicación de las formulas anidadas puede ser incluir una función dentro de uno de los valores Verdaderos y/o Falsos para poder cumplir con la condición.


Página 123

Aster Mérida


Página 124

Aster Mérida


Página 125

Aster Mérida

Laboratorio MÓDULO 5. FORMULAS Y FUNCIONES Ejercicio 1. Funciones Lógicas y Funciones Lógicas Anidadas En este ejercicio creará una tabla dinámica, la editará y posteriormente creará un gráfico dinámico basado en ella. Tareas


1. Abra el libro llamado F Lógicas y F Logs Anidadas. 2. Realice una fórmula para poder emitir una etiqueta que indique el momento de Resurtir o mantener los inventarios al momento..

     

3. Utilice la fórmula sumar si conjunto para demostrar el uso de las fórmulas condicionales.

  

4. Determine el tipo de proveedor según el valor que se encuentre en la celda E22.

 

   

  


    


En la Hoja Inventarios Haga clic en la celda M2. Teclee en la barra de Fórmulas la siguiente función: =SI(I2<$D$27,"Momento de Resurtir", "Inventarios Aceptables") Arrastra la fórmula en las celdas adyacentes hasta llegar a la celda M26. Reemplaza la cantidad señalada en la celda D27 por la cantidad de 25 Observa cómo queda la tabla. En la Hoja Herramientas. Selecciona la celda E22 Presione en la barra de fórmulas lo siguiente =Sumar.si y selecciona la opción llamada Sumar.Si.Conjunto. En la Opción denominada rango_suma, teclea lo siguiente: F5:F19 En la opción rango_Criterios1 teclee: G5:G19. Y en la opción Criterio1, seleccione la celda F22 y cierre paréntesis. Sitúese la celda F22 y seleccione cualquiera de los valores que aparecen en la lista que se despliega. Seleccione la celda D23. En la Barra de fórmulas escriba lo siguiente: =Si, presione la tecla tabulador. En la Opción de Prueba_lógica, teclee: E22>4000, en la siguiente opción Valor_si_verdadero, escriba "Proveedor Elite". Por último en la opción Valor_si_falso escriba la siguiente fórmula: SI(E22<2000,"Retirar licencia de distribución") Sitúese la celda F22 y seleccione cualquiera de los valores que aparecen en la lista que se despliega. 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á. Vaya a la carpeta de Prácticas que creo anteriormente en Mis Documentos. En el cuadro Nombre de archivo escriba Mod5 Lab1. En el cuadro Guardar como tipo verifique que diga Libro de Excel. De clic en el botón Guardar.

Página 126

Aster Mérida

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


Página 127

Aster Mérida


Página 128

Aster Mérida

Precedentes y Dependientes Cuando trabajamos con fórmulas por lo común se cometen errores durante el cálculo o bien al indicar las referencias a las celdas que intervienen en el cálculo. Excel nos ofrece las herramientas necesarias para auditar las fórmulas, evaluarlas y encontrar la referencia exacta en la cual se produce el error. Estas herramientas nos simplifican la corrección de los errores para que nuestras fórmulas siempre produzcan el resultado adecuado. La auditoría de fórmulas nos permite no sólo rastrear errores, sino también rastrear dependientes y precedentes, así como efectuar una inspección o evaluación de las mismas. Las flechas de color rojo muestran las celdas que generan errores. Si una celda de otra hoja de cálculo o de otro libro hace referencia a la celda seleccionada, se mostrará una flecha de color negro que señala de la celda seleccionada a un icono de hoja de cálculo. El otro libro debe estar abierto para que Microsoft Excel pueda rastrear las dependencias. Para realizar la auditoria de fórmulas en la ficha Fórmulas, en el grupo Auditoría de fórmulas utilice alguno de los siguientes comandos de acuerdo a lo que requiera: 

Rastrear precedentes. Indica con flechas cuáles son las celdas que intervienen en el cálculo de la fórmula que se encuentra en la celda actual. En caso de no haber fórmula en la celda activa se mostrará un mensaje para notificarlo.


Página 129

Aster Mérida

 

Rastrear dependientes. Indica con flechas cuáles son las celdas que utilizan la celda activa para el cálculo de alguna fórmula. En caso de no utilizar la celda actual en una fórmula se notificará. Quitar flechas. Permite quitar todas flechas activadas, o bien hacerlo por niveles.

NOTA: Las flechas de seguimiento son flechas que muestran la relación entre la celda activa y sus celdas relacionadas. Las flechas de seguimiento son azules cuando se señalan desde una celda que proporciona datos a otra celda, y rojas si una celda contiene un valor de error, como #DIV/0!.    

Mostrar fórmulas. Muestra la fórmula correspondiente en cada celda en lugar del valor resultante Comprobación de errores. Busca errores comunes en las celdas. Rastrear error. Esta herramienta sólo se utiliza cuando la celda activa contiene un error, y muestra o rastrea desde donde se origina. Marca las celdas que intervienen en el error con flechas. Evaluar fórmula. Permite depurar una fórmula visualizando paso a paso su cálculo.

AL CAMBIAR LA HOJA DESAPARECEN LAS FLECHAS DE RASTREO Si se cambia la fórmula de la ruta de rastreo, se insertan o se eliminan columnas o filas, o si se eliminan o mueven celdas, desaparecerán todas las flechas de rastreo. Para restaurar las flechas de rastreo después de hacer cualquiera de estos cambios, deberán utilizarse otra vez los comandos de auditoría en la hoja de cálculo. MICROSOFT EXCEL EMITE UN PITIDO AL RASTREAR… Si Microsoft Excel emite un pitido al hacer clic en Rastrear dependientes o en Rastrear precedentes en el grupo Auditoría de fórmulas, significa que Excel ha buscado en todos los niveles de la fórmula o que está tratando de rastrear un elemento que no puede rastrearse. Los siguientes elementos de la hoja de cálculo, a los que pueden hacer referencia las fórmulas, no pueden rastrearse mediante las herramientas de auditoría:    

Las referencias a cuadros de texto, o imágenes en una hoja de cálculo. Informes de tabla dinámica Las referencias a constantes con nombre Las fórmulas ubicadas en otro libro que hacen referencia a la celda activa si se cierra el otro libro


Página 130

Aster Mérida

Comprobación y Rastreo de fórmulas Microsoft Excel tiene varias herramientas que le ayudarán a encontrar y corregir los problemas con las fórmulas HERRAMIENTA DE COMPROBACIÓN DE ERRORES EN FÓRMULAS Al igual que un corrector gramatical, Excel emplea algunas reglas para comprobar si hay problemas en las fórmulas. Estas reglas no garantizan que la hoja de cálculo no tenga ningún problema, pero ayudan en gran medida a encontrar los errores más comunes. Se pueden activar y desactivar individualmente. Los problemas se pueden revisar de dos formas: de uno en uno, como con el corrector ortográfico, o inmediatamente sobre la hoja de cálculo mientras trabaja. Cuando se detecta un problema, aparece un triángulo en la esquina superior izquierda de la celda. Ambos métodos presentan las mismas opciones.

Celda con un problema de fórmula

Puede solucionar el problema utilizando las opciones que aparecen u omitirlo. Si lo omite, ya no volverá a aparecer en las comprobaciones de errores subsiguientes. Sin embargo, todos los errores que haya omitido se pueden restablecer para que vuelvan a mostrarse.


Página 131

Aster Mérida

REGLAS Y ERRORES QUE SE DETECTAN Evalúa valor de error. La fórmula no utiliza la sintaxis, los argumentos o los tipos de datos que se esperaba. Entre los valores de error se encuentran #DIV/0!, #N/A, #¿NOMBRE?, #¡NULO!, #¡NUM!, #¡REF! y #¡VALOR!. Cada valor de error tiene distintas causas y se resuelve de diferente modo. NOTA: Si introduce un valor de error directamente en una celda, no se marcará como problema. Año con dos dígitos en fecha de texto: La celda contiene una fecha de texto en la que el siglo se puede interpretar incorrectamente si se utiliza en fórmulas. Por ejemplo, la fecha de la fórmula =AÑO("1/1/31") podría ser 1931 ó 2031. Utilice esta regla para comprobar fechas de texto ambiguas. Número almacenado como texto: La celda contiene números guardados como texto. Suelen proceder de datos importados de otros orígenes. Los números guardados como texto pueden ocasionar cambios inesperados en la forma de ordenar; es preferible convertirlos a números Para utilizar todas las herramientas anteriores, es necesario que esté situado en una celda que contenga una fórmula, de lo contrario Excel le notificará que no pueden ser utilizadas. CORREGIR UN VALOR DE ERROR Si una fórmula no puede evaluar correctamente un resultado, Excel muestra un valor de error, como #####, #¡DIV/0!, #N/A, #¿NOMBRE?, #¡NULO!, #¡NÚM!, #¡REF! y #¡VALOR!. Cada tipo de error tiene diversas causas y soluciones diferentes.

Valor Error #####

de Descripción

Excel muestra este error cuando el ancho de una columna no es suficiente para mostrar todos los caracteres de una celda o cuando una celda incluye valores negativos en la fecha o la hora. Por ejemplo, una fórmula que resta a una fecha del pasado una fecha del futuro, como =15/06/2008-01/07/2008. Esto genera un valor negativo en la fecha. #¡DIV/0! Excel muestra este error cuando un número se divide por cero (0) o por una celda que no contiene ningún valor. #N/A Excel muestra este error cuando un valor no está disponible para una función o una fórmula. #¿NOMBRE? Este error aparece cuando Excel no reconoce el texto de una fórmula. Por ejemplo, el nombre de un intervalo o de una función puede estar mal


Página 132

Aster Mérida

#¡NULO!

#¡NÚM! #¡REF!

#¡VALOR!

escrito. Excel muestra este error cuando se especifica una intersección de dos áreas que no forman intersección (no se cruzan). El operador de intersección es un carácter de espacio que separa referencias en una fórmula. Por ejemplo, las áreas A1:A2 y C3:C5 no forman intersección, de modo que al escribir la fórmula =SUMA(A1:A2 C3:C5) se devuelve el error #¡NULO!.

Excel muestra este error cuando una fórmula o función contiene valores numéricos no válidos. Excel muestra este error cuando una referencia de celda no es válida. Por ejemplo, cuando se eliminan celdas a las que otras fórmulas hacían referencia o se pegan celdas movidas sobre otras a las cuales se hacía referencia en otras fórmulas. Excel puede mostrar este error si la fórmula incluye celdas que contienen tipos de datos diferentes. Si se habilita la comprobación errores, la información en pantalla muestra el mensaje "Un valor utilizado en la fórmula es de un tipo de datos erróneo". Por lo general, para resolver este problema, se pueden realizar pequeñas modificaciones en la fórmula.


Página 133

Aster Mérida

Mostrar Fórmulas Entre las herramientas de auditoría de fórmulas podemos encontrar una opción llamada Mostrar fórmulas cuya finalidad es mostrar la fórmula en cada celda en lugar del valor resultante. Esto facilita la identificación de cuáles son las funciones y/o fórmulas que se están empleando en la hoja y así poder realizar los ajustes pertinentes para aplicar otras fórmulas o en su caso corregir aquellas celdas que tiene algún error.


Página 134

Aster Mérida

Ventana de Inspección INSPECCIONAR UNA FÓRMULA Y SU RESULTADO A TRAVÉS DE LA VENTANA INSPECCIÓN Cuando las celdas no están visibles en una hoja de cálculo, puede ver las celdas y sus fórmulas en la barra de herramientas ventana Inspección. La ventana Inspección es útil para revisar, controlar o confirmar el cálculo de fórmulas y los resultados en hojas de cálculo grandes. Con el uso de la ventana Inspección, no necesitará desplazarse repetidamente ni ir a las distintas partes de su hoja de cálculo. Esta barra de herramientas se puede mover o acoplar como cualquier otra barra de herramientas. Por ejemplo, puede acoplarla en la parte inferior de la ventana. La barra de herramientas realiza un seguimiento de las siguientes propiedades de una celda: libro, hoja, nombre, celda, valor y fórmula. Nota: Sólo puede inspeccionar las celdas una vez.


Página 135

Aster Mérida

AGREGAR CELDAS A LA VENTANA INSPECCIÓN 1. Seleccione las celdas que desee inspeccionar. 2. En el grupo Auditoría de fórmulas de la ficha Fórmulas, haga clic en Ventana Inspección.

3. Haga clic en Agregar inspección . 4. Haga clic en Agregar. 5. Mueva la barra de herramientas Ventana Inspección a la parte superior, inferior, izquierda o derecha de la ventana. 6. Para cambiar el ancho de una columna, arrastre el borde derecho del encabezado de la columna. 7. Para mostrar la celda a la que hace referencia una entrada en la barra de herramientas Ventana Inspección, haga doble clic en la entrada. NOTA: Las celdas que tienen referencias externas a otros libros solo se muestran en la barra de herramientas ventana Inspección cuando el otro libro está abierto.

Quitar celdas de la ventana Inspección 1. En el grupo Auditoría de fórmulas de la pestaña Fórmulas, haga clic en Ventana Inspección. 2. Seleccione las celdas que desee quitar. Para seleccionar varias celdas, presione CTRL y haga clic en las celdas. 3. Haga clic en Eliminar inspección .


Página 136

Aster Mérida

Evaluar Fórmulas EVALUAR UNA FÓRMULA ANIDADA PASO A PASO A veces resulta difícil comprender cómo se calcula el resultado final de una fórmula anidada porque hay diversos cálculos intermedios y pruebas lógicas. Sin embargo, mediante el cuadro de diálogo Evaluar fórmula, puede ver las diferentes partes de una fórmula anidada evaluados en el orden en que la fórmula se calcula. Por ejemplo, la fórmula =SI(PROMEDIO(F2:F5)>50;SUMA(G2:G5);0) es más fácil de comprender cuando puede ver los siguientes resultados intermedios: Pasos que se muestran en el cuadro de diálogo =SI(PROMEDIO(F2:F5)>50; SUMA(G2:G5);0)

Descripción

=SI(40>50;SUMA(G2:G5);0)

El rango de celdas F2:F5 contiene los valores 55, 35, 45 y 25 y, por lo tanto, el resultado de la función PROMEDIO(F2:F5) es 40.

=SI(Falso;SUMA(G2:G5);0)

Puesto que 40 no es mayor que 50, la expresión en el primer argumento de la función SI (el argumento de la prueba lógica) es Falso.


Se muestra inicialmente la fórmula anidada. La función PROMEDIO y la función SUMA están anidadas dentro de la función SI.

Página 137

Aster Mérida

0

La función SI devuelve el valor del tercer argumento (el argumento falso de valor de SI). La función SUMA no se evalúa porque es el segundo argumento de la función SI (argumento verdadero de valor de SI) y se devuelve sólo cuando la expresión es Verdadera.

1. Seleccione la celda con una fórmula que desee evaluar. Sólo se puede evaluar una celda a la vez. 2. En el grupo Auditoría de fórmulas de la pestaña Fórmulas, haga clic en la opción Evaluar fórmula. 3. Haga clic en Evaluar para examinar el valor de la referencia subrayada. El resultado de la evaluación se muestra en cursiva.

Si la parte subrayada de la fórmula es una referencia a otra fórmula, haga clic en Paso a paso para entrar para mostrar la otra fórmula en el cuadro Evaluación. Haga clic en Paso a paso para salir para volver a la celda y fórmula anteriores. NOTA: El botón Paso a paso para entrar no está disponible para una referencia la segunda vez que ésta aparece en la fórmula, ni si la fórmula hace referencia a una celda de un libro distinto. 4. Continúe hasta que se hayan evaluado todas las partes de la fórmula. 5. Para ver de nuevo la evaluación, haga clic en Reiniciar. Para finalizar la evaluación, haga clic en Cerrar. OBSERVACIONES:  Algunas partes de fórmulas que usan las funciones SI y ELEGIR no se evalúan. En estos casos, se muestra #N/A en el cuadro Evaluación.  Si una referencia está en blanco, aparece el valor cero (0) en el cuadro Evaluación.  Las siguientes funciones se vuelven a calcular cada vez que se modifica la hoja de cálculo y puede provocar que el cuadro de diálogo Evaluar fórmula presente resultados distintos de los que aparecen en la celda: ALEATORIO, ÁREAS, ÍNDICE, DESPLAZAMIENTO, CELDA, INDIRECTO, FILAS, COLUMNAS, AHORA, HOY, ALEATORIO ENTRE.


Página 138

Aster Mérida


Página 139

Aster Mérida


Página 140

Aster Mérida

Laboratorio 1: AUDITORÍA DE FÓRMULAS Ejercicio 1. Encontrar Errores en los Cálculos y Corregirlos En este ejercicio encontrará errores en fórmulas y utilizando las herramientas de auditoria de fórmulas las corregirá. Tareas


1. Abra el archivo de Excel llamado Errores. 2. Rastrea los precedentes de la celda H21.

  

3. Rastre el error de la celda H21.

  

4. Evalué la fórmula y corrija el error que se muestra.

   

 5. Verifique que la primera fórmula contenga la referencia absoluta y cópiela a las celdas adyacentes.

  




Seleccione la celda H21. En la ficha fórmulas en el grupo Auditoria de fórmulas haga clic en rastrear precedentes. Una flecha color roja indicará todas las celdas que preceden la fórmula. Seleccione la celda H21. Despliegue la etiqueta inteligente de Error que está a la izquierda de la celda y haga clic en Seguimiento de error. La celda activa se moverá hacia donde se ubica el error (H7) y mostrará los precedentes al error. Seleccione la celda H7 en caso de ser necesario. En la ficha fórmulas en el grupo Auditoria de fórmulas haga clic en Evaluar fórmula. El cuadro de diálogo Evaluar fórmula aparecerá. Haga clic en Evaluar, se mostrará en lugar de G7 el valor de la celda Haga clic de nuevo en Evaluar se mostrará en lugar de H4 el valor de la celda. Haga clic nuevamente en evaluar y observe que aparece el resultado #¡VALOR! NOTA: No realice operación alguna El error se debe a que se está multiplicando una celda con valor numérico (G7) por una celda que contiene un texto (H4). La fórmula correcta debe ser: G7 * H2. Debido a que H2 está fuera de la tabla y todas las fórmulas hacen referencia a ella, debe de utilizar una referencia absoluta, es decir $H$2. La fórmula entonces debe ser G7 * $H$2. Haga clic en Cerrar. Seleccione la celda H5. En la barra de fórmulas verifique que la fórmula esté escrita como sigue: G2*H2. Corrija la fórmula para que quede como se indica. Haga clic en la barra de fórmula y para volver absoluta la referencia H2 haga clic en ella y presione la tecla F4. Una vez que la formula este escrita como sigue G5*$H$2 presiona [ENTER]. Seleccione la celda H5 y cópiela de forma adyacente al rango H6:H19.

Página 141

Aster Mérida

6. Seleccione la celda H21 y rastree sus precedentes y el error y corríjalo. Copie la fórmula corregida a la celda I21.



Verifique que los errores del rango H5:H19 se hayan corregido.

 

Seleccione la celda H21. En la ficha fórmulas en el grupo Auditoria de fórmulas haga clic en rastrear precedentes. Una flecha color roja indicará todas las celdas que preceden la fórmula. Despliegue la etiqueta inteligente de Error que está a la izquierda de la celda y haga clic en Rastrear error. Un mensaje le indicará que el Rastreador ha encontrado una flecha o una referencia circular. En la ficha fórmulas en el grupo Auditoria de fórmulas haga clic en rastrear precedentes, cuando la flecha roja indique las celdas que intervienen en el cálculo verifique que la celda H21 no esté dentro de ellas. Vaya a la barra de fórmulas y corrija la fórmula de tal manera que se muestre como sigue: =PROMEDIO(H5:H19). Verifique que el error se haya corregido. Copie la fórmula a la celda I21. Verifique que el error se haya corregido.

   

    7. Guarde el libro creado con el nombre Mod6 Lab1.

    


Haga clic en la ficha Archivo, despliegue y de clic en Guardar Como… El cuadro de diálogo Guardar como… aparecerá. Vaya a la carpeta de Prácticas que creo anteriormente en Mis Documentos. En el cuadro Nombre de archivo escriba Mod6 Lab1. En el cuadro Guardar como tipo verifique que diga Libro de Excel. De clic en el botón Guardar.

Página 142

Aster Mérida

MÓDULO 7: Colaboración


Página 143

Aster Mérida


Página 144

Aster Mérida

Propiedades de los Libros de Trabajo Las propiedades de los libros de trabajo son datos adicionales que se pueden incluir en forma complementaria para información del mismo. Existen los siguientes tipos de propiedades de documentos: Propiedades estándar. De forma predeterminada, los documentos de Microsoft Office están asociados con un conjunto de propiedades estándar, tales como el autor, el título y el asunto. Propiedades de actualización automática. Estas propiedades incluyen tanto propiedades del sistema de archivos como estadísticas que los programas de Office mantienen. No es posible especificar ni modificar las propiedades de actualización automática. Propiedades personalizadas. Puede definir propiedades personalizadas adicionales para los documentos de Office. Propiedades para la organización. Si la organización personalizó el Panel de información del documento, las propiedades del documento asociadas al documento pueden ser específicas de la organización. Para ver o editar las propiedades del libro haga clic en el botón de Microsoft Office, seleccione Preparar y, a continuación, haga clic en Propiedades. El panel de información Propiedades del documento aparecerá. Si desea ver las propiedades Microsoft Excel 2007 Avanzado

Página 145

Aster Mérida

avanzadas en el Panel de información del documento, haga clic en la flecha situada junto a Propiedades del documento para seleccionar Propiedades avanzadas. Los campos de propiedades marcados con un asterisco rojo son campos obligatorios; quizá deba completarlos antes de poder guardar el documento. Las propiedades personalizadas son propiedades que el usuario define para un documento de Office. En el Panel de información del documento, haga clic en la flecha situada junto a Propiedades del documento y, a continuación, haga clic en Propiedades avanzadas. En el cuadro de diálogo Propiedades del documento, haga clic en la ficha Personalizar. En el cuadro Nombre, escriba un nombre para las propiedades personalizadas o seleccione un nombre de la lista. En la lista Tipo, seleccione el tipo de datos de la propiedad que desee agregar. En el cuadro Valor, escriba un valor para la propiedad. Haga clic en Agregar.


Página 146

Aster Mérida

Compartir Libros de Trabajo Si tiene una red y varios usuarios necesitan trabajar sobre un mismo libro, es posible compartir el libro en la red. Es una forma fácil para trabajar en grupo y no tener duplicados de información. Esto permite que varios usuarios trabajen simultáneamente sobre el mismo libro a la vez, ahorrando tiempo y agilizando la actualización de la información. Para compartir un libro en la ficha Revisar en el grupo Cambios haga clic en Compartir libro. El cuadro de diálogo Compartir libro se mostrará. En la ficha Modificación, active la casilla de verificación Permitir la modificación por varios usuarios a la vez. Esto también permite combinar libros. En la lista Los siguientes usuarios tienen el libro abierto se listarán los usuarios y en modo en el cual visualizan el libro. En la ficha Uso Avanzado, en la sección Control de cambios determine cuantos días desea guardar el Historial de cambios o si no desea guardarlo. En la sección Actualizar cambios determine si los cambios se actualizarán al cerrar el libro, Automáticamente cada determinado tiempo y si se verán los cambios de los otros usuarios que estén trabajando en el libro. En la sección En caso de conflictos entre usuarios determine cuáles prevalecerán o bien si antes se preguntará. En la sección Vista personal determine si se verá la Configuración de impresora y la de filtro.


Página 147

Aster Mérida

Al presionar Aceptar, si es un libro nuevo, aparecerá el cuadro de diálogo Guardar como para nombrar el archivo. Si se trata de un libro existente, se mostrará un mensaje para indicar que esta acción guardará el libro, haga clic en Aceptar para guardarlo. Al guardarlo debe utilizar una carpeta de red compartida, no un servidor Web.


Página 148

Aster Mérida

Control de Cambios El historial de cambios registra detalles acerca de los cambios en el libro, durante un período determinado de tiempo, siempre que se guarda el libro. Puede utilizar este historial para comprender qué cambios se han realizado y aceptar o rechazar las revisiones. Esta posibilidad resulta especialmente útil cuando hay varios usuarios modificando el mismo libro. También es útil cuando se envía un libro a los revisores para que escriban comentarios y luego se desea combinar toda la información en una sola copia tras seleccionar los cambios y comentarios que se van a conservar. Una vez activado el registro de cambios, nos ofrece la posibilidad de ver el historial de cambios que se han realizado y en caso de ser necesario, o bien de existir un conflicto en un libro compartido, rechazar o bien aceptar los diferentes cambios que se han venido haciendo. De esta forma podemos controlar toda la información introducida en la hoja y descartar aquella que consideremos no necesaria. Para utilizar el control de cambios, en la ficha Revisar en el grupo Cambios haga clic en Control de cambios y a continuación en Resaltar cambios. El cuadro de diálogo Resaltar cambios aparecerá. Si desea realizar el control mediante Cuándo se realizaron los cambios, active la casilla de verificación Cuándo y determine si revisará Desde la última vez que se guardo, Sin revisar, Desde una fecha determinada o Todos.


Página 149

Aster Mérida

Si desea especificar los cambios de que usuarios va a revisar active la casilla de verificación Quién y seleccione si Todos, Todos excepto yo o algún usuario en particular. Active Resaltar cambios en pantalla para ver marcados los cambios que se van o han efectuado. En caso de querer que los cambios se indiquen en otra hoja active la casilla Mostrar cambios en una hoja Nueva. Una vez que se comparte el libro y se efectúa sobre él Control de cambios, es posible indicar que Cambios se aceptan y cuáles se rechazan. Para ello en la ficha Revisar en el grupo Cambios haga clic en Control de cambios y a continuación en Aceptar o rechazar cambios. El cuadro de diálogo Seleccionar cambios para aceptar o rechazar aparecerá. En el cuadro Cuándo indique Desde que fecha se revisarán los cambios o bien si sólo los que están sin revisar. En el cuadro Quién determine los cambios de que usuarios o usuarios revisará, o bien si de todos los usuarios. Determine si aparecerán los cambios en otro lugar. Presione Aceptar. En caso de haber cambios para revisar aparecerá el cuadro de diálogo Aceptar o rechazar cambios, donde se mostrará el cambio realizado, que usuario lo hizo, fecha y hora. Este cuadro permite Aceptar o Rechazar el cambio. De igual manera permite Aceptar o Rechazar todos a la vez.


Página 150

Aster Mérida


Página 151

Aster Mérida


Página 152

Aster Mérida

Laboratorio MÓDULO 7. COLABORACIÓN Ejercicio 1. Compartir Libros y Realizar Control de Cambios En este ejercicio compartirá un libro de Excel y controlará quien tiene acceso y permiso para modificarlo. También efectuará el control de cambios. Tareas


1. Abra el archivo de Excel llamado Compartir y cambios. 2. Comparta el libro y configure el Historial de cambios de forma que guarde los cambios durante 45 días.



  

En la ficha Revisar en el grupo Cambios haga clic en Compartir libro. El cuadro de diálogo aparece. En la ficha Modificación active la casilla Permitir la modificación de varios usuarios a la vez. En la ficha Uso Avanzado, en la sección Control de cambios en la casilla Guardar historial de cambios durante, ajústelo a 45 días. Haga clic en Aceptar. Aparecerá un cuadro de diálogo indicando que el libro será guardado. Haga clic en Aceptar .

3. Modifique el libro.

  

Sitúese en la celda D3 y escriba 121000. Sitúese en la celda E3 y escriba 452215. Sitúese en la celda F3 y escriba 235410.

4. Configure el documento de forma que resalte los cambios hechos.



En la ficha Revisar en el grupo Cambios, despliegue Control de cambios y haga clic en Resaltar cambios. El cuadro de diálogo aparecerá. Active la casilla Efectuar control de cambios al modificar. En la sección Resaltar cambios active la casilla Cuándo y seleccione Todos, active la casilla Quién y seleccione Todos. Active la casilla Resaltar cambios en pantalla. Haga clic en Aceptar. Los cambios que se hayan efectuado son resaltados y se les incluye un comentario con sus detalles.

 

    

5. Revise los cambios efectuados para aceptar todos.

       


En la ficha Revisar en el grupo Cambios, despliegue Control de cambios y haga clic en Aceptar o rechazar cambios. Un cuadro de diálogo aparecerá indicando que el libro será guardado. El cuadro de diálogo Seleccionar cambios para aceptar o rechazar aparecerá. Active la casilla Cuándo y seleccione Sin revisar. Active la casilla Quién y seleccione Todos. Haga clic en Aceptar. Se mostrará el cuadro de diálogo Aceptar o rechazar cambios indicando quién realizó el cambio, la fecha y hora y el cambio realizado. Haga clic en Aceptar todos.

Página 153

Aster Mérida


    



Página 154

Aster Mérida

Administración de Comentarios Excel facilita la tarea de añadir comentarios a las celdas de un libro, añadiendo puntos de vista que van más allá de lo que indican los datos de las celdas. Cuando añade un comentario a una celda, aparece un marcador en la esquina superior derecha de dicha celda. Cuando el puntero del Mouse se posiciona sobre una celda que contiene un comentario, éste se muestra en un cuadro junto a la celda, junto con el nombre del usuario que lo creo. Para agregar un comentario haga clic en la celda a la que desea agregar el comentario. En la ficha Revisar en el grupo Comentarios haga clic en Nuevo Comentario. En el cuadro de texto del comentario escriba el texto que desea incluir. Para modificar un comentario haga clic en la celda que contiene el comentario, en la ficha Revisar en el grupo Comentarios haga clic en Modificar comentario, o bien haga clic en Mostrar u ocultar comentarios para mostrar el comentario y, a continuación, haga doble clic en el texto del comentario. Para eliminar un comentario haga clic en la celda que contiene el comentario, en la ficha Revisar en el grupo Comentarios haga clic en Eliminar, o bien haga clic en Mostrar u ocultar comentarios para mostrar el contenido y, a continuación, haga doble clic en el cuadro de texto y presione Supr.


Página 155

Aster Mérida

Para aplicar formato a un comentario haga clic en la celda que contiene el comentario a dar formato. En la ficha Revisar en el grupo Comentarios haga clic en Mostrar u ocultar comentarios. Para aplicar formato al texto, selecciónelo y utilice las opciones de formato de la ficha Inicio en el grupo Fuente. Las opciones Color de relleno y Color de fuente del grupo Fuente no están disponibles para el texto de los comentarios. Para cambiar el color del texto, haga clic derecho en el comentario y, a continuación, haga clic en Formato de comentario. Si desea dar formato a la forma de comentario entonces de clic derecho sobre ella y seleccione Formato de comentario. El cuadro de diálogo Formato de comentario le permite dar formato a la fuente, alineación, colores y líneas (relleno y contorno), tamaño, proteger, web, propiedades y márgenes. Puede utilizar los comandos Anterior y Siguiente, para avanzar y retroceder entre los diferentes comentarios que tenga en la hoja.


Página 156

Aster Mérida


Página 157

Aster Mérida


Página 158

Aster Mérida

Laboratorio MODULO 7. COLABORACIÓN Ejercicio 2. Administración de Comentarios En este ejercicio practicará los conocimientos adquiridos acerca de los comentarios. Tareas


1. Abra el archivo de Excel llamado Comentarios. 2. Inserte un comentario en la celda G6 para saber cómo se calculó el importe de los días trabajados

 

3. Inserte un comentario en la celda I6 para saber cómo se calculó el IMSS.

 

4. Inserte un comentario en la celda J6 para saber cómo se calculó el ISPT.

 

5. Inserte un comentario en la celda K6 para saber cómo se calculó el importe de los vales.

 

6. Muestre todos los comentarios.



En la ficha Revisar, en el grupo Comentarios, haga clic en Mostrar todos los comentarios.

7. Aplique un formato personalizado a los comentarios.



Aplique un formato a los comentarios, cambiando el tipo de fuente y sombreado, por ejemplo, de cada uno de ellos. Para ello recuerde dar clic derecho en el borde del comentario y personalizar en las diferentes fichas del cuadro de diálogo.

8. Oculte todos los comentarios.



En la ficha Revisar, en el grupo Comentarios, haga clic en Mostrar todos los comentarios para desactivarlo.










Seleccione la celda G6. En la ficha Revisar, en el grupo Comentarios, haga clic en Nuevo comentario. El cuadro de comentario aparecerá junto a la celda. Borre el nombre de Usuario y teclee: IMP. DIAS TRABAJADOS = SUELDO DIARIO POR DIAS TRABAJADOS. Seleccione la celda I6. En la ficha Revisar, en el grupo Comentarios, haga clic en Nuevo comentario. El cuadro de comentario aparecerá junto a la celda. Borre el nombre de Usuario y teclee: IMSS = IMP. DIAS TRABAJADOS POR PORC. DEL IMSS. Seleccione la celda J6. En la ficha Revisar, en el grupo Comentarios, haga clic en Nuevo comentario. El cuadro de comentario aparecerá junto a la celda. Borre el nombre de Usuario y teclee: ISPT = IMP. DIAS TRABAJADOS POR PORC. DEL ISPT. Seleccione la celda K6. En la ficha Revisar, en el grupo Comentarios, haga clic en Nuevo comentario. El cuadro de comentario aparecerá junto a la celda. Borre el nombre de Usuario y teclee: VALES = IMP. DIAS TRABAJADOS POR PORC. DE VALES.

Página 159

Aster Mérida


    



Página 160

Aster Mérida

Protección Excel permite proteger los libros de trabajo, desde las celdas de una hoja, la estructura de una hoja hasta el acceso al libro de trabajo. Inclusive permite especificar permisos por cada usuario que vaya a utilizar el libro. De esta forma puede mantener segura la información que trabaja. Para proteger celdas de una hoja de cálculo, asegúrese que la casilla Bloqueada de la ficha Proteger esté activa. Si lo que desea es que se puedan modificar seleccione cada celda o rango que desea desbloquear y desactive la casilla Bloqueada. La casilla Oculto le permite ocultar fórmulas que no desea mostrar. Una vez desbloqueadas o bloqueadas las celdas que necesite, en la ficha Revisión, en el grupo Cambios, haga clic en Proteger hoja. En la lista Permitir a los usuarios de esta hoja de cálculo, seleccione los elementos que desee que los usuarios puedan cambiar. Active la casilla Proteger hoja y contenido de celdas bloqueadas. En el cuadro Contraseña para desproteger la hoja escriba la contraseña a utilizar. Haga clic en Aceptar, el cuadro de diálogo Confirmar contraseña aparecerá, escriba de nuevo la contraseña y presione Aceptar. La hoja quedará entonces protegida. Para desproteger la hoja en la ficha Revisión en el grupo Cambios haga clic en Desproteger hoja. En el cuadro Contraseña para desproteger la hoja, escriba una contraseña para la hoja, haga clic en Aceptar.


Página 161

Aster Mérida

Para proteger el libro en la ficha Revisión, en el grupo Cambios, haga clic en Proteger libro y a continuación en Proteger estructura y ventanas. En el cuadro de diálogo para proteger la estructura de un libro, active la casilla de verificación Estructura. Para que las ventanas del libro tengan siempre el mismo tamaño y posición cada vez que se abra el libro, active la casilla de verificación Ventanas. Para impedir que otros usuarios quiten la protección del libro, en el cuadro Contraseña (opcional), escriba una contraseña, haga clic en Aceptar y vuelva a escribir la contraseña para confirmarla. Si el libro ya está compartido y desea asignar una contraseña para protegerlo, debe anular el uso compartido del libro. Si desea compartir y al mismo tiempo indicar la protección haga clic en el comando Proteger y compartir libro. El cuadro de diálogo Proteger libro compartido a aparecerá. Active la casilla Compartir con control de cambios y escriba la contraseña, haga clic en Aceptar y vuelva a escribir la contraseña para confirmarla. También puede permitir que en una hoja protegida determinados usuarios modifiquen un rango mediante una contraseña. En la ficha Revisar, en el grupo Cambios, haga clic en Permitir que los usuarios modifiquen rangos. Este comando solamente está disponible cuando la hoja de cálculo no está protegida. El cuadro de diálogo Permitir que los usuarios modifiquen rangos aparecerán. Para agregar un nuevo rango, haga clic en Nuevo. El cuadro de diálogo Nuevo rango se mostrará. En el cuadro Título escriba el nombre correspondiente al rango que dejará para editar. En el cuadro correspondiente a las celdas escriba la referencia a las celdas que se podrán editar. En el cuadro Contraseña del rango escriba la contraseña a utilizar. Si desea que solamente algunos usuarios tengan acceso al rango haga clic en el botón Permisos y en el cuadro de diálogo agregue los usuarios o grupos que desea tengan acceso al rango. Para modificar un rango existente, selecciónelo en el cuadro Rangos desbloqueados mediante contraseña cuando la hoja está protegida y, a continuación, haga clic en Modificar. Para eliminar un rango, selecciónelo en el cuadro Rangos desbloqueados mediante contraseña cuando la hoja está protegida y, a continuación, haga clic en Eliminar. Una vez agregados los rangos y especificado las contraseñas y permisos en el cuadro de diálogo Permitir que los usuarios modifiquen rangos, haga clic en Proteger hoja e indique la contraseña de protección.


Página 162

Aster Mérida

Autenticación de Libros de Trabajo Microsoft Office usa la tecnología Microsoft Authenticode para permitirle firmar digitalmente un archivo o una macro, mediante un certificado digital. El certificado utilizado para crear esta firma confirma que la macro o el documento se han originado en el firmante y la firma confirma que no se ha modificado. Una vez firmado digitalmente un documento, será de sólo lectura para impedir la realización de modificaciones. Para firmar un libro haga clic en el botón de Microsoft Office, seleccione Preparar y, a continuación, haga clic en Agregar una firma digital. El cuadro de diálogo Firmar aparecerá, seleccione el certificado o firma y en el cuadro Razón para firmar este documento coloque un comentario, Haga clic en el botón Firmar. En caso de querer modificar la firma haga clic en el botón Modificar para seleccionar una firma diferente a la indicada. Si intenta firmar digitalmente un documento de Office sin un id. digital (certificado digital), aparece el cuadro de diálogo Obtener un id. digital y se le pide que seleccione la forma en que desea obtener su propio id. digital. Dispone de dos opciones para obtener un id. digital:  Obtener un id. digital de un colaborador de Microsoft  Crear su propio id. digital


Página 163

Aster Mérida

Si selecciona la opción Obtener un id. digital de un colaborador de Microsoft del cuadro de diálogo Obtener un id. digital, abre el Catálogo de soluciones de Office, donde puede adquirir un certificado digital de una de las entidades emisoras de certificados. Utilice esta opción cuando vaya a compartir el libro con otros usuarios. Si no desea adquirir un certificado digital de una entidad emisora de certificados de terceros o si desea firmar digitalmente el documento enseguida, puede crear su propio id. digital si selecciona la opción Crear su propio id. digital del cuadro de diálogo Obtener un id. digital. En el cuadro de diálogo Crear un id. Digital, en el cuadro Nombre, escriba su nombre. En el cuadro Dirección de correo electrónico, escriba su dirección de correo electrónico. En el cuadro Organización, escriba el nombre de la organización o compañía. En el cuadro Ubicación, escriba su ubicación geográfica. Haga clic en Crear. Si la crea de esta manera la firma digital sólo se puede autenticar en el equipo en el que la creó. Una vez firmado el documento aparece el Panel Firmas donde se muestran las firmas agregadas. Si desea quitar alguna firma, selecciónela y haga clic en Quitar firma.


Página 164

Aster Mérida


Página 165

Aster Mérida


Página 166

Aster Mérida

Laboratorio MODULO 7. COLABORACIÓN Ejercicio 3. Protección y Autenticación de Libros de Trabajo En este ejercicio creará una contraseña para acceder al libro, así como cuando se modifiquen celdas y hojas de trabajo Tareas


1. Abra el archivo de Excel llamado Proteger. 2. Aplique una protección por contraseña al archivo y verifique la protección.

           

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á. Vaya a la carpeta de Prácticas que creo anteriormente en Mis Documentos. En el cuadro Nombre de archivo escriba Mod10 Lab3. En el cuadro Guardar como tipo verifique que diga Libro de Excel. Despliegue el botón Herramientas y haga clic en Opciones generales. El cuadro de diálogo se abrirá. En el cuadro Contraseña de apertura escriba 12345. En el cuadro Contraseña de escritura escriba 54321. Haga clic en Aceptar. Se mostrará el cuadro de diálogo para confirmar la contraseña de apertura, escriba 12345 y haga clic en Aceptar. Se mostrará el cuadro de diálogo para confirmar la contraseña de escritura, escriba 54321 y haga clic en Aceptar. De clic en el botón Guardar. Cierre el libro y ábralo de nuevo para verificar que le pida las contraseñas correspondientes.

3. Desbloquee el rango D6:D36.

  

Sitúese en caso de ser necesario en la hoja llamada Enero Seleccione el rango D6:D36. En la ficha Inicio en el grupo Celdas, despliegue el comando Formato y desactive Bloquear celda.

4. Permita que los usuarios puedan modificar por contraseña el rango E6:E36.

 

Seleccione el rango E6:E36. En la ficha Revisar en el grupo Cambios haga clic en Permitir que los usuarios modifiquen rangos. El cuadro de diálogo aparecerá. Haga clic en nuevo. En el cuadro Título escriba S3. En el cuadro Correspondiente a las celdas verifique que se encuentre el rango E6:E36, de lo contrario selecciónelo. En el cuadro Contraseña del rango, escriba ABCDE. Haga clic en Aceptar. El cuadro de diálogo Confirmar contraseña aparecerá, teclee ABCDE y haga clic en Aceptar para regresar al cuadro de diálogo Permitir que los usuarios modifiquen rangos, donde se mostrará el rango S3. Haga clic en Aceptar.

      


Página 167

Aster Mérida

NOTA: En caso de que existan varios usuarios en la misma máquina se pueden crear permisos para cada uno. Para ello haga clic en el botón Permisos, en el cuadro de diálogo agregue al usuario y configure los permisos que le correspondan. El nombre del usuario debe escribirse de la manera en que está registrado. 5. Proteja la hoja.

     

6. Agregue una firma digital.

 

  


     


En la ficha Revisar en el grupo Cambios haga clic en Proteger hoja. El cuadro de diálogo aparecerá. Verifique que estén activadas las casillas Seleccionar celdas bloqueadas, Seleccionar celdas desbloqueadas. Verifique que la casilla Proteger hoja y contenido de celdas bloqueadas esté activa. En el cuadro Contraseña para desproteger la hoja, escriba 123 y haga clic en Aceptar. El cuadro de diálogo Confirmar contraseña aparecerá, teclee 123 y haga clic en Aceptar. La hoja estará entonces protegida. Verifique que los rangos a los cuales se modificó anteriormente su protección. Haga clic en el botón de Microsoft Office (Archivo), despliegue el menú Preparar y haga clic en Agregar una firma digital. El mensaje de Servicios de firmas de Microsoft aparecerá, haga clic en Aceptar. NOTA: En dado caso que no haya firmas en el equipo Office le proporciona la posibilidad de crear una firma de prueba por medio del SelfCert para ello sólo será necesario el nombre de la firma. En el cuadro de diálogo Firmar, haga clic en Firmar si el certificado que se muestra es el que desea, de lo contrario haga clic en Cambiar para que se muestren las firmas existentes y elija otra. Una vez que haga clic en Firmar aparecerá el cuadro de diálogo de Confirmación de la firma. Haga clic en Aceptar. Se mostrará el Panel de tareas Firmas donde podrá ver las firmas que el libro contiene. 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á. Vaya a la carpeta de Prácticas que creo anteriormente en Mis Documentos. En el cuadro Nombre de archivo escriba Mod7 Lab3. En el cuadro Guardar como tipo verifique que diga Libro de Excel. De clic en el botón Guardar. En caso que se pregunte si desea reemplazar el archivo existente, haga clic en Sí.

Página 168

Aster Mérida

MÓDULO 8: Análisis de Datos


Página 169

Aster Mérida


Página 170

Aster Mérida

Tabla de Datos Una tabla de datos es un rango de celdas que muestra cómo el cambio de una o dos variables en las fórmulas afectará a los resultados de las mismas. Las tablas de datos constituyen un método rápido para calcular varios resultados en una operación y una forma de ver y comparar los resultados de todas las variaciones diferentes en la hoja de cálculo. Cuando se usan tablas de datos, se realizan análisis de hipótesis. El análisis de hipótesis es el proceso de cambiar los valores de las celdas para ver cómo los cambios afectarán al resultado de fórmulas de la hoja de cálculo. Por ejemplo, puede usar una tabla de datos para variar el tipo de interés y el plazo que se utilizan en un préstamo para determinar posibles importes de pago mensual. TIPOS DE ANÁLISIS DE HIPÓTESIS Existen tres tipos de herramientas de análisis de hipótesis en Excel: escenarios, tablas de datos y búsqueda de objetivo. Los escenarios y las tablas de datos analizan conjuntos de valores de entrada y determinan posibles resultados. La búsqueda de objetivo funciona de forma distinta de los escenarios y las tablas de datos porque analiza un resultado y determina los posibles valores de entrada que producen ese resultado. Al igual que los escenarios, las tablas de datos le ayudan a explorar un conjunto de resultados posibles. A diferencia de los escenarios, las tablas de datos muestran todos los resultados en una tabla en una hoja de cálculo. El uso de tablas


Página 171

Aster Mérida

de datos permite examinar fácilmente una variedad de posibilidades de un vistazo. Como es posible centrarse en sólo una o dos variables, los resultados son fáciles de leer y compartir en formato tabular. Una tabla de datos no puede dar cabida a más de dos variables. Si desea analizar más de dos variables, debe utilizar en su lugar escenarios. Aunque está limitado a sólo uno o dos variables (una para la celda de entrada de fila y otra para la celda de entrada de columna), una tabla de datos puede incluir tantos valores de variables diferentes como se desee. Un escenario puede tener un máximo de 32 valores diferentes, pero se pueden crear tantos escenarios como se desee.

TABLAS DE DATOS DE UNA VARIABLE: Use una tabla de datos de una variable si desea ver cómo diferentes valores de una variable en una o más fórmulas cambiarán los resultados de esas fórmulas. Por ejemplo, puede usar una tabla de datos de una variable para ver cómo diferentes tipos de interés afectan al pago mensual de una hipoteca mediante la PAGO. Escribe los valores de variables en una columna o fila y los resultados aparecen en la columna o fila adyacente En la siguiente ilustración, la celda D2 contiene la fórmula de pago =PAGO(B3/12,B4,-B5), que hace referencia a la celda de entrada B3.

Una tabla de datos de una variable

TABLAS DE DATOS DE DOS VARIABLES: Use una tabla de datos de dos variables para ver cómo diferentes valores de dos variables en una fórmula cambiarán los resultados de la misma. Por ejemplo, puede usar una tabla de datos de dos variables para ver cómo diferentes combinaciones de tipos de interés y términos de préstamos afectarán al pago mensual de una hipoteca. En la siguiente ilustración, la celda C2 contiene la fórmula de pago =PAGO(B3/12,B4,-B5), que usa dos celdas de entrada: B3 y B4.


Página 172

Aster Mérida

Una tabla de datos de dos variables

CREAR UNA TABLA DE DATOS DE UNA VARIABLE Una tabla de datos de una variable tiene valores de entrada que se enumeran en una columna (orientado a columnas) o en una fila (orientado a filas). Las fórmulas que se usan en una tabla de datos de una variable deben hacer referencia a solo una celda de entrada. 1. Escriba la lista de valores que desea sustituir en la celda de entrada en una columna o en una fila. Deje unas cuantas filas y columnas vacías en cualquiera de los lados de los valores. 2. Siga uno de estos pasos: 

Si la tabla de datos está orientada a columnas (los valores de variables se encuentran en una columna), escriba la fórmula en la celda situada una fila más arriba y una celda a la derecha de la columna de valores. La ilustración de la tabla de datos de una variable que se muestra en la sección Información general está orientada a columnas y la fórmula está dentro de la celda D2.



Si desea examinar los efectos de diferentes valores en otras fórmulas, escriba las fórmulas adicionales en las celdas a la derecha de la primera fórmula.



Si la tabla de datos está orientada a filas (los valores de las variables se encuentran en una fila), escriba la fórmula en la celda situada una columna a la izquierda del primer valor y una celda más abajo de la fila de valores. Si desea examinar los efectos de distintos valores en otras fórmulas, escriba las fórmulas adicionales en las celdas debajo de la primera fórmula.

3. Seleccione el rango de celdas que contiene las fórmulas y los valores que desea sustituir. Según la primera ilustración en la sección anterior Información general, este rango es C2:D5.


Página 173

Aster Mérida

a) En el grupo Herramientas de datos de la ficha Datos, haga clic en Análisis de hipótesis y, a continuación, en Tabla de datos. b) Siga uno de estos pasos: 



Si la tabla de datos está orientada a columnas, escriba la referencia de celda para la celda de entrada en el cuadro Celda de entrada (columna). Si usamos el ejemplo de la primera ilustración, la celda de entrada es B3. Si la tabla de datos está orientada por filas, escriba la referencia de celda de la celda de entrada en el cuadro Celda de entrada (fila).

NOTA: Después de crear la tabla de datos, quizás desee cambiar el formato de las celdas de resultados. En la ilustración, las celdas de resultados tienen el formato de moneda

CREAR UNA TABLA DE DATOS DE DOS VARIABLES Una tabla de datos de dos variables usa una fórmula que contiene dos listas de valores de entrada. La fórmula debe hacer referencia a dos celdas de entrada diferentes. 1. En una celda de la hoja de cálculo, escriba la fórmula que hace referencia a las dos celdas de entrada. 2. En el ejemplo siguiente, en el cual los valores iniciales de la fórmula se introducen en las celdas B3, B4 y B5, escriba la fórmula =PAGO(B3/12,B4,B5) en la celda C2. 3. Escriba una lista de valores de entrada en la misma columna, debajo de la fórmula. En este caso, escriba los diferentes tipos de interés en las celdas C3, C4y C5. 4. Introduzca la segunda lista en la misma fila que la fórmula, a su derecha. Escriba el término del préstamo (en meses) en las celdas D2 y E2. 5. Seleccione el rango de celdas que contiene la fórmula (C2), tanto la fila como la columna de valores (C3:C5 y D2:E2) y las celdas en las que desea los valores calculados (D3:E5). Microsoft Excel 2007 Avanzado

Página 174

Aster Mérida

6. En este caso, seleccione el rango C2:E5. 7. En la ficha Datos, en el grupo Herramientas de datos, haga clic en Análisis de hipótesis y, a continuación, en Tabla de datos. 8. En el cuadro Celda de entrada (fila), introduzca la referencia a la celda de entrada para los valores de entrada en la fila. Escriba B4 en el cuadro Celda de entrada (fila). 9. En el cuadro Celda de entrada (columna), introduzca la referencia a la celda de entrada para los valores de entrada en la columna. Escriba B3 en el cuadro Celda de entrada (columna). 10. Haga clic en Aceptar. EJEMPLO: Una tabla de datos de dos variables puede mostrar de qué manera distintas combinaciones de tipos de interés y términos del préstamo afectarán al pago mensual de la hipoteca. En la siguiente ilustración, la celda C2 contiene la fórmula de pago =PAGO(B3/12,B4,-B5), que usa dos celdas de entrada: B3 y B4.


Página 175

Aster Mérida


Página 176

Aster Mérida


Página 177

Aster Mérida

Laboratorio 1 MODULO 8. ANÁLISIS DE DATOS Ejercicio 1. Tabla de datos con una y dos variables En este ejercicio creará una tabla de datos con una y con dos variables para observar los resultados que se obtendrían en caso de los intereses o plazos cambien. Tareas


1. Abra el libro llamado Tabla de datos. 2. Elabore una tabla de datos de una variable en la hoja Cambio de Interés.

    

3. Cree una tabla de datos de 2 variables en la hoja de cálculo Meses.

      


    

En caso de ser necesario active la hoja Cambio de interés. Seleccione el rango de celdas D2:E5. En la ficha Datos en el grupo Herramientas de Datos haga clic en Análisis y si y luego en Tabla de datos. En el cuadro de dialogo Tabla de datos, haga clic en el campo Celda de entrada (columna). Seleccione la celda $B$3 y haga clic en aceptar. Active la hoja Meses. Selecciona el rango de celdas D2:F5. En la ficha Datos en el grupo Herramientas de Datos haga clic en Análisis y si y luego en Tabla de datos. En el cuadro de dialogo Tabla de datos, haga clic en el campo Celda de entrada (Fila). Seleccione la celda $B$4. En el cuadro de dialogo Tabla de datos, haga clic en el campo Celda de entrada (Columna). Seleccione la celda $B$3 y haga clic en aceptar. Haga clic en la ficha Archivo, despliegue y haga clic en Guardar Como… El cuadro de diálogo Guardar como… aparecerá. Vaya a la carpeta de Prácticas que creo anteriormente en Mis Documentos. En el cuadro Nombre de archivo escriba Mod8 Lab1. En el cuadro Guardar como tipo verifique que diga Libro de Excel. De clic en el botón Guardar.


Página 178

Aster Mérida

Escenarios Un escenario es un conjunto de valores que Excel guarda y puede reemplazar de forma automática en las celdas de una hoja de cálculo. Puede crear y guardar diferentes grupos de valores en una hoja de cálculo y, luego, cambiarlos a cualquiera de estos nuevos escenarios para ver los diferentes resultados. Por ejemplo, suponga que tiene dos escenarios del presupuesto: un caso bueno y otro malo. Puede usar la característica Administrador de escenarios para crear dos escenarios en la misma hoja de cálculo y luego cambiar entre ellos. Para cada escenario, debe especificar las celdas que cambian y los valores que va a usar para dicho escenario. Al cambiar entre los escenarios, la celda de resultado cambia para reflejar los diferentes valores de las celdas cambiantes. Escenario Peor opción

Celdas cambiantes Celda de resultado


Página 179

Aster Mérida

Escenario Mejor opción

Celdas cambiantes Celda de resultado Si varias personas tienen información específica en libros independientes que desea usar en escenarios, puede recopilar los libros y combinar los escenarios. Para comparar varios escenarios, puede crear un informe que los resuma en la misma página. Un informe de escenario muestra toda la información del escenario en una tabla en una nueva hoja de cálculo. Informe Resumen de escenario

NOTA: Los informes de escenario no se actualizan automáticamente. Si cambia los valores de un escenario, los cambios no se reflejarán en un informe de resumen existente. En lugar de ello, debe crear un nuevo informe de resumen

CREAR UN ESCENARIO 1. En la ficha Datos en el grupo Herramientas de datos, haga clic en Análisis Y si, a continuación en Administrador de escenarios. 2. El cuadro de diálogo Administrador de escenarios aparecerá. Haga clic en el botón Agregar. El cuadro de diálogo Agregar escenario aparecerá. 3. En el cuadro Nombre del escenario escriba el nombre que dará al escenario.


Página 180

Aster Mérida

4. En el cuadro Celdas cambiantes, especifique las referencias de las celdas que van a variar. 5. En la sección Proteger seleccione si el escenario estará bloqueado para Evitar cambios u Oculto, pueden ser ambas. Haga clic en Aceptar. En caso de requerirlo puede modificar el comentario. 6. El cuadro de diálogo Valores del escenario aparecerá mostrando las referencias a las celdas cambiantes, introduzca los valores que desee para cada una. 7. Para crear el escenario haga clic en Aceptar y regresar al Administrador de escenarios. Si desea agregar otro escenario haga clic en Agregar para regresar al cuadro Agregar escenario sin necesidad de visualizar de nuevo el Administrador y una vez que haya agregado todos los escenarios requeridos presione Aceptar. Se recomienda que para preservar los valores originales de las celdas cambiantes, cree un escenario que utilice los valores originales de las celdas antes de crear escenarios que cambien los valores. Cuando se muestra un escenario, se cambian los valores de las celdas que se guardan como parte de ese escenario. Para ello en la ficha Datos, en el grupo Herramientas de datos, haga clic en Análisis Y si y, después, en Administrador de escenarios. El cuadro Administrador de escenarios aparecerá mostrando los escenarios existentes en el libro. Haga clic en el nombre del escenario y presione el botón Mostrar. Una vez creado el escenario es posible realizarle modificaciones para ello en la ficha Datos, en el grupo Herramientas de datos, haga clic en Análisis Y si y, después, en Administrador de escenarios. El cuadro Administrador de escenarios aparecerá mostrando los escenarios existentes en el libro. Haga clic en el nombre del escenario y presione el botón Modificar. De igual forma puede eliminar un escenario seleccionándolo y presionando el botón Eliminar. Puede combinar escenarios de otro libro para agregarlos al actual, para ello en el Administrador de escenarios haga clic en el botón Combinar. Aparecerá el cuadro Combinar escenarios donde indicará de que libro y en que hoja se encuentran los escenarios a agregar. Para crear un informe resumen de escenario, En la ficha Datos, en el grupo Herramientas de datos, haga clic en Análisis Y si y, después, en Administrador de escenarios. El cuadro Administrador de escenarios aparecerá. Haga clic en el botón Resumen. En el cuadro de diálogo resumen del escenario indique el tipo de informe (resumen o informe de tabla dinámica de escenario) y en el cuadro Celdas de resultado escriba las referencias de las celdas cuyos resultados se van a mostrar.


Página 181

Aster Mérida


Página 182

Aster Mérida


Página 183

Aster Mérida

Laboratorio 2: MODULO 8. ANÁLISIS DE DATOS Ejercicio 1. Conjuntos de Datos Alternativos En este ejercicio creará conjuntos de datos alternativos para ser utilizados posteriormente. Tareas


1. Abra el libro llamado Escenarios. 2. Cree un escenario llamado 24meses con los datos originales.

      

3. Cree un escenario llamado 36meses.

    

4. Cree un escenario llamado 48meses.

    

5. Muestre el escenario llamado 36meses.

  

En la ficha Datos en el grupo Herramientas de datos, despliegue el comando Análisis Y si y haga clic en Administrador de escenarios. Haga clic en el botón Agregar. El cuadro de diálogo Agregar escenarios aparecerá. En el cuadro Nombre del escenario escriba 24meses. En el cuadro Celdas cambiantes seleccione las celdas C7, C9 y C11 presionando [CTRL]. Haga clic en Aceptar. El cuadro de diálogo Valores del escenario aparecerá. Verifique que los datos sean los correspondientes a la hoja. C7=0.35, C9=24, C11=0.03. Haga clic en Agregar. El cuadro de diálogo Agregar escenario aparecerá. En el cuadro Nombre del escenario escriba 36meses. En el cuadro Celdas cambiantes seleccione las celdas C7, C9 y C11 en caso de ser necesario. Haga clic en Aceptar. El cuadro de diálogo Valores del escenario aparecerá. Escriba los siguientes valores para las celdas C5=150000, C7=0.25, C9=36, C11=0.05. Haga clic en Agregar. El cuadro de diálogo Agregar escenario aparecerá. En el cuadro Nombre del escenario escriba 48meses. En el cuadro Celdas cambiantes seleccione las celdas C7, C9 y C11 en caso de ser necesario. Haga clic en Aceptar. El cuadro de diálogo Valores del escenario aparecerá. Escriba los siguientes valores para las celdas C7=0.20, C9=48, C11=0.075. Haga clic en Aceptar. El cuadro de diálogo Administrador de escenarios aparecerá mostrando los 3 escenarios creados. En la lista escenarios haga clic sobre 36meses. Haga clic en el botón Mostrar. Verifique que los resultados de las fórmulas cambian.


Página 184

Aster Mérida

6. Modifique el escenario 36meses para que el valor de la celda C7 sea .30.

     

7. Genere resumen de escenarios.

un los

8. Genere una tabla dinámica con los datos de los escenarios.

   

   


    

En la lista escenarios de la ventana de administrador de escenarios haga clic sobre 36meses. Haga clic en el botón Modificar. El cuadro de diálogo Modificar escenario aparecerá. Haga clic en Aceptar. El cuadro de diálogo Valores del escenario aparecerá. En el cuadro correspondiente a la celda C7 borre el contenido y escriba .30. Haga clic en Aceptar. El cuadro de diálogo Administrador de escenarios aparecerá. Haga clic en Mostrar. Verifique que los resultados de las fórmulas cambian. En el Administrador de escenarios haga clic en el botón Resumen. El cuadro de diálogo Resumen del escenario aparecerá. En la sección Tipo de informe seleccione Resumen. En el cuadro Celdas de resultado seleccione C13 y C15. Haga clic en Aceptar. Una nueva hoja con el resumen del escenario se insertará. En el Administrador de escenarios haga clic en el botón Resumen. El cuadro de diálogo Resumen del escenario aparecerá. En la sección Tipo de informe seleccione Informe de tabla dinámica de escenario. En el cuadro Celdas de resultado seleccione C13 y C15. Haga clic en Aceptar. Una nueva hoja con el informe de la tabla dinámica del escenario se insertará. Cambie los valores según sus preferencias. Haga clic en la ficha Archivo, despliegue y haga clic en Guardar Como… El cuadro de diálogo Guardar como… aparecerá. Vaya a la carpeta de Prácticas que creo anteriormente en Mis Documentos. En el cuadro Nombre de archivo escriba Mod8 Lab2. En el cuadro Guardar como tipo verifique que diga Libro de Excel. De clic en el botón Guardar.


Página 185

Aster Mérida

Buscar Objetivo Si sabe qué resultado desea obtener de una fórmula, pero no está seguro de qué valor de entrada necesita la fórmula para obtener ese resultado, use la característica Buscar objetivo. Por ejemplo, imagine que debe pedir prestado dinero. Sabe cuánto dinero desea, cuánto tiempo va a tardar en saldar el préstamo y cuánto puede pagar cada mes. Puede usar Buscar objetivo para determinar qué tipo de interés deberá conseguir para cumplir con el objetivo del préstamo.

NOTA: La característica Buscar objetivo funciona solamente con un valor de entrada variable. Si desea aceptar más de un valor de entrada; por ejemplo, el importe del préstamo y el importe del pago mensual del préstamo, use el complemento Solver


Página 186

Aster Mérida

USAR BUSCAR OBJETIVO 1. En el grupo Herramientas de datos de la ficha Datos, haga clic en Análisis Y Si y, a continuación, en Buscar objetivo. 2. En el cuadro Definir la celda, escriba la referencia de la celda que contiene la fórmula que desea resolver. En el ejemplo es la celda B4. 3. En el cuadro Con el valor, especifique el resultado de la fórmula que desee. En el ejemplo es -900. Observe que este número es negativo porque representa un pago. 4. En el cuadro Para Cambiar la celda, indique la referencia de la celda que contiene el valor que desea ajustar. En el ejemplo es la celda B3. NOTA: La fórmula de la celda especificada en el cuadro Definir la celda debe hacer referencia a la celda que cambia Buscar objetivo


Página 187

Aster Mérida


Página 188

Aster Mérida


Página 189

Aster Mérida

Laboratorio 3: MODULO 8. ANALISIS DE DATOS. Ejercicio 2. Variar los Datos para Obtener los Resultados Deseados. En este ejercicio aplicará la herramienta Buscar objetivo para encontrar un valor que resuelva alguna fórmula. Tareas


1. Abra el libro llamado Objetivo. 2. Cree un escenario llamado 24meses con los datos originales.

      

3. Encuentre el valor para el porcentaje de enganche que haga que la mensualidad sea de 3500.

        

4. Presente los valores originales utilizando los escenarios



5. Encuentre la tasa de interés a pagar realizando un pago de $ 900.00 mensuales. Utilizando la hoja Préstamo.



 

  

En la ficha Datos en el grupo Herramientas de datos, despliegue el comando Análisis Y si y haga clic en Administrador de escenarios. Haga clic en el botón Agregar. El cuadro de diálogo Agregar escenarios aparecerá. En el cuadro Nombre del escenario escriba 24meses. En el cuadro Celdas cambiantes seleccione las celdas C7, C9 y C11 presionando [CTRL]. Haga clic en Aceptar. El cuadro de diálogo Valores del escenario aparecerá. Verifique que los datos sean los correspondientes a la hoja. C7=0.35, C9=24, C11=0.03. Haga clic en Agregar. El cuadro de diálogo Agregar escenario aparecerá. Seleccione la celda C15. En la ficha Datos en el grupo Herramientas de datos, despliegue el comando Análisis Y si y haga clic en Buscar objetivo. El cuadro de diálogo aparecerá. En el cuadro Definir celda, seleccione la celda C15. En el cuadro Con el valor escriba 3500. En el cuadro Para cambiar la celda, seleccione la celda C7. Haga clic en Aceptar. El cuadro de diálogo Estado de la búsqueda de objetivo aparecerá mostrando el valor del objetivo y el valor actual. Puede ver en la hoja el resultado obtenido. Haga clic en Aceptar para hacer permanente el cambio. En la ficha Datos en el grupo Herramientas de datos, despliegue el comando Análisis Y si y haga clic en Administrador de escenarios. Seleccione 24meses y haga clic en Mostrar. luego clic en Cerrar. De manera opcional puede crear un escenario para conservar los valores originales de este ejemplo. Y considerando como celdas cambiantes la B1, B2 y B3. Haga clic en la hoja Préstamo. Seleccione la Celda B4 si es necesario En la ficha Datos en el grupo Herramientas de datos, despliegue el comando Análisis Y si y haga clic en Buscar objetivo. El cuadro de


Página 190

Aster Mérida

       3. Guarde el libro creado con el nombre Mod8 Lab3.

    

diálogo aparecerá. En el cuadro Definir celda, seleccione la celda B4. En el cuadro Con el valor escriba 900. En el cuadro Para cambiar la celda, seleccione la celda B3. Haga clic en Aceptar. El cuadro de diálogo Estado de la búsqueda de objetivo aparecerá mostrando el valor del objetivo y el valor actual. Puede ver en la hoja el resultado obtenido. Haga clic en Aceptar para hacer permanente el cambio Haga clic en la ficha Archivo, despliegue y de clic en Guardar Como… El cuadro de diálogo Guardar como… aparecerá. Vaya a la carpeta de Prácticas que creó anteriormente en Mis Documentos. En el cuadro Nombre de archivo escriba Mod8 Lab3. En el cuadro Guardar como tipo verifique que diga Libro de Excel. De clic en el botón Guardar.


Página 191

Aster Mérida

Buscar Soluciones con Solver Solver puede manejar problemas que dependen de numerosas celdas cambiantes, y ayuda a encontrar combinaciones que maximizan o minimizan una celda objetivo. Su principal diferencia con Buscar Objetivo es que no solamente varía una celda sino varias y además permite establecer restricciones, para encontrar sino el resultado exacto el más aproximado Con Solver, puede buscarse el valor óptimo para una fórmula de celda, denominada celda objetivo, en una hoja de cálculo. Solver funciona en un grupo de celdas que estén relacionadas, directa o indirectamente, con la fórmula de la celda objetivo. Solver ajusta los valores en las celdas cambiantes que se especifiquen, para generar el resultado especificado en la fórmula de la celda objetivo. Pueden aplicarse restricciones para restringir los valores que puede utilizar Solver en el modelo y las restricciones pueden hacer referencia a otras celdas a las que afecte la fórmula de la celda objetivo. Por ejemplo, puede cambiar el importe del presupuesto previsto para publicidad y ver el efecto sobre el margen de beneficio.

EJEMPLO DE UNA EVALUACIÓN DE SOLVER En el siguiente ejemplo, el nivel de publicidad de cada trimestre afecta al número de unidades vendidas, determinando indirectamente el monto de los ingresos por ventas, los gastos asociados y los beneficios. Solver puede modificar los presupuestos trimestrales de publicidad (celdas variables de decisión B5:C5), con


Página 192

Aster Mérida

una restricción total máxima de $20.000 (celda F5), hasta que el valor total de beneficios (celda objetivo F7) alcance el monto máximo posible. Los valores en las celdas variables se usan para calcular los beneficios para cada trimestre, por tanto, están relacionados con la fórmula en la celda objetivo F7, =SUMA(Q1 Beneficios:Q2 Beneficios).

Celdas variables Celda restringida Celda objetivo Una vez ejecutado Solver, los nuevos valores son los siguientes:

Para utilizar la herramienta Solver, en la ficha Datos, en el grupo Análisis, haga clic en Solver. Si el comando Solver o el grupo Análisis no está disponible, deberá cargar el programa Solver. 1. Haga clic en la pestaña Archivo, elija Opciones y, a continuación, haga clic en la categoría Complementos. 2. En el cuadro Administrar, haga clic en Complementos de Excel y, a continuación, en Ir. 3. En el cuadro Complementos disponibles, active la casilla de verificación Complemento Solver y, a continuación, haga clic en Aceptar. PASOS GENERALES EN EL USO DE SOLVER 1. En el grupo Análisis de la ficha Datos, haga clic en Solver. 2. Cuando ingrese al cuadro de diálogo Parámetros de Solver, en el cuadro Celda objetivo, escriba una referencia a celda o un nombre para la celda objetivo. La celda objetivo debe contener una fórmula. NOTA: Determine si el valor de la celda objetivo será el valor máximo posible (haga clic en Máx) o el valor mínimo posible (haga clic en Mín). Si desea que la celda objetivo tenga un valor determinado, haga clic en Valor de y, a continuación, escriba el valor en el cuadro.


Página 193

Aster Mérida

3. En el cuadro Cambiando las celdas de variables, escriba un nombre o una referencia para cada rango de celda de variable de decisión. Separe con comas las referencias no adyacentes. Las celdas de variables deben estar directa o indirectamente relacionadas con la celda objetivo. Se puede especificar un máximo de 200 celdas de variables 4. En el cuadro Sujeto a las siguientes restricciones, especifique todas las restricciones que desee aplicar.   



En el cuadro de diálogo Parámetros de Solver, haga clic en Agregar. En el cuadro Referencia de la celda, escriba la referencia de celda o el nombre del rango de celdas para los que desea restringir el valor. Haga clic en la relación (<=, =, >=, int, bin o dif ) que desea establecer entre la celda a la cual se hace referencia y la restricción. o Si hace clic en int, aparece integer en el cuadro Restricción. Si hace clic en bin, aparece binary en el cuadro Restricción. Si hace clic en dif, aparece alldifferent en el cuadro de diálogo Restricción. Si elige <=, =, o >= para la relación en el cuadro Restricción, escriba un número, una referencia de celda o nombre o una fórmula.

5. Siga uno de los procedimientos siguientes: o Para aceptar una restricción y agregar otra, haga clic en Agregar. o Para aceptar la restricción y volver al cuadro de diálogo Parámetros de Solver, haga clic en Aceptar. NOTA: Para eliminar una restricción basta con seleccionarla en el cuadro Sujetas a las siguientes restricciones y hacer clic sobre el botón Eliminar. También puede modificar la restricción si hace clic en el botón Cambiar. 6. Una vez especificados la celda objetivo, cómo modificar la celda objetivo y las restricciones, haga clic en Resolver. Solver entonces hará los procesos y cálculos necesarios para encontrar la solución.  

Una vez terminado el proceso Solver para mantener los valores de la solución en la hoja de cálculo, haga clic en Utilizar solución de Solver en el cuadro de diálogo Resultados de Solver. Para restaurar los datos originales, haga clic en Restaurar valores originales. También puede guardar los valores en un escenario para utilizarlos posteriormente, o bien crear un informe basado en la solución.


Página 194

Aster Mérida

Puede interrumpir el proceso de solución presionando ESC. Excel actualiza la hoja de cálculo con los últimos valores encontrados para las células ajustables. 

Para crear un informe basado en su solución después de que Solver encuentre una solución, seleccione un tipo de informe en el cuadro Informes y haga clic en Aceptar. El informe se crea en una nueva hoja de cálculo del libro. Si Solver no encuentra una solución, la opción de crear un informe no está disponible.



Para guardar los valores de la celda de variable de decisión como un escenario que pueda mostrar más tarde, haga clic en Guardar escenario en el cuadro de diálogo Resultados de Solver y luego escriba un nombre para el escenario en el cuadro Nombre del escenario.

MÉTODOS DE RESOLUCIÓN USADOS POR SOLVER Puede elegir cualquiera de los tres algoritmos o métodos de resolución siguientes en el cuadro de diálogo Parámetros de Solver: 

Generalized Reduced Gradient (GRG) Nonlinear que son no lineales suavizados.



LP Simplex



Evolutionary

Se usa para problemas

Se usa para problemas lineales. Se usa para problemas no suavizados


Página 195

Aster Mérida


Página 196

Aster Mérida


Página 197

Aster Mérida

Laboratorio 4: MODULO 8. ANÁLISIS DE DATOS Ejercicio 1. Búsqueda de Soluciones en Solver En este ejercicio aplicará la herramienta Solver para la búsqueda de un objetivo aplicando restricciones Tareas


1. Abra el libro llamado Solver. 2. En caso de ser necesario active la herramienta Solver.

  

3. Usando la herramienta Solver maximizar el beneficio total cambiando las cantidades a fabricar por artículo y de acuerdo a las restricciones que siguen: El número de piezas utilizadas debe ser menor o igual a las existencias, el número de cantidades a fabricar debe ser mayor o igual a cero.

             

4. Guarde el libro creado con el



Haga clic en la pestaña Archivo, elija Opciones y, a continuación, haga clic en la categoría Complementos. En el cuadro Administrar, haga clic en Complementos de Excel y, a continuación, en Ir. En el cuadro Complementos disponibles, active la casilla de verificación Complemento Solver y, a continuación, haga clic en Aceptar. En la ficha Datos en el grupo Análisis haga clic en Solver. El cuadro de diálogo aparecerá. En el cuadro Establecer objetivo seleccione la celda D18. En la sección Para seleccione Máximo. En el cuadro Cambiando las celdas de variables seleccione el rango D9:F9. En la sección Sujeto a las Restricciones haga clic en el botón Agregar. El cuadro de diálogo Agregar restricción aparecerá. En el cuadro Referencia de la celda seleccione el rango C11:C15, como operador seleccione <= y en el cuadro Restricción seleccione el rango B11:B15. Haga clic en Agregar. El cuadro de diálogo Agregar restricción aparecerá. En el cuadro Referencia de la celda seleccione el rango D9:F9, como operador seleccione >= y en el cuadro Restricción escriba 0. Haga clic en Aceptar. El cuadro parámetros de Solver se mostrará. Haga clic en Resolver. El cuadro de diálogo Resultados de Solver se mostrará indicando que Solver ha hallado una solución. Seleccione Restaurar valores originales. De la lista Informes seleccione Responder. Haga clic en Aceptar. Se creará una hoja de informe de responder indicando la celda objetivo, las celdas cambiantes, las restricciones, sus nombres, sus valores originales, valores finales y en caso de fórmulas, el valor de la celda, su estado y la divergencia.

Haga clic en la ficha Archivo, despliegue y de clic en Guardar Como… El cuadro de diálogo Guardar como… aparecerá.


Página 198

Aster Mérida

nombre Mod8 Lab4.

   

Vaya a la carpeta de Prácticas que creo anteriormente en Mis Documentos. En el cuadro Nombre de archivo escriba Mod8 Lab4. En el cuadro Guardar como tipo verifique que diga Libro de Excel. De clic en el botón Guardar.


Página 199

Aster Mérida

Análisis de Datos con Estadística Descriptiva Excel proporciona un conjunto de herramientas para el análisis de datos que ayudan en el análisis estadístico de un conjunto de datos. Algunas incluso generan gráficos. De esta forma el análisis de ciertos datos estadísticos se realiza de forma tal que permita ahorrar tiempo. Para tener acceso a estas herramientas, haga clic en Análisis de datos en el grupo Análisis de la ficha Datos. Si el comando Análisis de datos no está disponible, deberá cargar el programa de complemento Herramientas para análisis. 1. Haga clic en la pestaña Archivo, elija Opciones y, a continuación, haga clic en la categoría Complementos. 2. En el cuadro Administrar, haga clic en Complementos de Excel y, a continuación, en Ir. 3. En el cuadro Complementos disponibles, active la casilla de verificación Complemento Herramientas para análisis y, a continuación, haga clic en Aceptar

Las herramientas para análisis disponibles con las siguientes: 

Varianza. Las herramientas de análisis de varianza proporcionan distintos tipos de análisis de la varianza. La herramienta que debe usar depende del número de factores y del número de muestras que tenga de la población que


Página 200

Aster Mérida

  



 



   

 

desee comprobar: Varianza de un factor, Varianza de dos factores con varias muestras por grupo, Varianza de dos factores con una sola muestra por grupo Correlación. Las funciones COEF.DE.CORREL y PEARSON de la hoja de cálculo calculan el coeficiente de correlación entre dos variables de medida cuando se observan medidas de cada variable para cada uno de los N sujetos Covarianza. La herramienta Covarianza calcula el valor de la función COVAR de la hoja de cálculo para cada uno de los pares de variables de medida. Estadística descriptiva. La herramienta de análisis Estadística descriptiva genera un informe estadístico de una sola variable para los datos del rango de entrada, y proporciona información acerca de la tendencia central y dispersión de los datos. Suavización exponencial. La herramienta de análisis Suavización exponencial predice un valor que está basado en el pronóstico del período anterior, ajustado al error en ese pronóstico anterior. La herramienta utiliza la constante de suavización a, cuya magnitud determina la exactitud con la que los pronósticos responden a los errores en el pronóstico anterior. Prueba t para varianzas de dos muestras. La herramienta de análisis Prueba t para varianzas de dos muestras ejecuta una Prueba t de dos muestras para comparar dos varianzas de población. Análisis de Fourier. La herramienta Análisis de Fourier resuelve problemas de sistemas lineales y analiza datos periódicos, transformándolos mediante el método de transformación rápida de Fourier (FFT, Fast Fourier Transform). Esta herramienta también realiza transformaciones inversas, en las que el inverso de los datos transformados devuelve los datos originales. Histograma. La herramienta de análisis Histograma calcula las frecuencias individuales y acumulativas de rangos de celdas de datos y de clases de datos. Esa herramienta genera datos acerca del número de apariciones de un valor en un conjunto de datos. Media móvil. La herramienta de análisis Media móvil proyecta valores en el período de pronósticos, basándose en el valor promedio de la variable calculada durante un número específico de períodos anteriores. Generación de números aleatorios. Rellena un rango con números aleatorios independientes extraídos de una de varias distribuciones. Jerarquía y percentil. La herramienta de análisis Jerarquía y percentil crea una tabla que contiene los rangos ordinales y porcentuales de cada valor de un conjunto de datos. Regresión. La herramienta de análisis Regresión efectúa el análisis de regresión lineal utilizando el método de "mínimos cuadrados" para ajustar una línea a un conjunto de observaciones. Puede utilizar esta herramienta para analizar la forma en que los valores de una o más variables independientes afectan a una variable dependiente. Muestreo. La herramienta de análisis Muestreo crea una muestra de población tratando el rango de entrada como una población. Prueba t. Las herramientas de análisis Prueba t de dos muestras permiten comprobar la igualdad de las medias de población que subyacen a cada muestra. Son 3: prueba t para medias de dos muestras emparejadas, prueba t


Página 201

Aster Mérida



para dos muestras suponiendo varianzas iguales y prueba t para dos muestras suponiendo varianzas desiguales. Prueba z. La herramienta de análisis Prueba z para medias de dos muestras realiza una Prueba z en las medias de dos muestras con varianzas conocidas.

Las funciones de análisis de datos sólo pueden utilizarse en una única hoja de cálculo a la vez. Cuando se analizan los datos de hojas agrupadas, los resultados aparecerán en la primera hoja, y en las hojas restantes aparecerán tablas con formato vacías. Para analizar los datos del resto de las hojas, actualice la herramienta de análisis para cada una de ellas.


Página 202

Aster Mérida


Página 203

Aster Mérida


Página 204

Aster Mérida

Laboratorio 5: MODULO 8. ANÁLISIS DE DATOS Ejercicio 1. Análisis de Datos con Estadística Descriptiva. En este ejercicio aplicará la herramienta para Análisis Tareas


1. Abra el libro llamado Análisis de datos. 2. En caso de ser necesario active la herramienta de Análisis de Datos.

  

3. Utilizando los datos de la hoja Estadística genere un reporte utilizando la herramienta de Estadística Descriptiva.

         

4. Utilizando los datos de la hoja Fallas cree un histograma.

           

Haga clic en la pestaña Archivo, elija Opciones y, a continuación, haga clic en la categoría Complementos. En el cuadro Administrar, haga clic en Complementos de Excel y, a continuación, en Ir. En el cuadro Complementos disponibles, active la casilla de verificación Complemento Herramientas para análisis y, a continuación, haga clic en Aceptar. Haga clic en la etiqueta de la hoja Estadística en caso de ser necesario. En la ficha Datos en el grupo Análisis haga clic en Análisis de datos. El cuadro de diálogo aparecerá. De la lista de Funciones para análisis seleccione Estadística descriptiva. Haga clic en Aceptar. El cuadro de diálogo aparecerá. En el cuadro Rango de entrada, seleccione el rango D8:G20 En Agrupado por, active Columnas. Active la casilla Rótulos en la primera fila. En la sección Opciones de salida, active En una hoja nueva y en el cuadro correspondiente escriba Resumen. Active resumen de estadísticas. Haga clic en Aceptar. Se insertará una hoja con una tabla con los datos correspondientes. Haga clic en la etiqueta de la hoja Fallas. En la ficha Datos en el grupo Análisis haga clic en Análisis de datos. El cuadro de diálogo aparecerá. De la lista de Funciones para análisis seleccione Histograma. Haga clic en Aceptar. El cuadro de diálogo aparecerá. En el cuadro Rango de entrada seleccione C7:G16. En el cuadro Rango de clases seleccione B18:B22. Active la casilla Rótulos. En la sección Opciones de salida, active En una hoja nueva y en el cuadro correspondiente escriba Histograma. Active la casilla Pareto (Histograma ordenado). Active la casilla Porcentaje acumulado Active la casilla Crear gráfico. Haga clic en Aceptar. Se insertará una hoja con el histograma ordenado y la tabla resumen.


Página 205

Aster Mérida


    

Haga clic en la ficha Archivo, despliegue y de clic en Guardar Como… El cuadro de diálogo Guardar como… aparecerá. Vaya a la carpeta de Prácticas que creo anteriormente en Mis Documentos. En el cuadro Nombre de archivo escriba Mod8 Lab5. En el cuadro Guardar como tipo verifique que diga Libro de Excel. De clic en el botón Guardar.


Página 206

Aster Mérida

MÓDULO 9: Controles de Formulario y Macros


Página 207

Aster Mérida


Página 208

Aster Mérida

Introducción a Macros Una macro es una combinación de instrucciones que pueden ser ejecutadas automáticamente con una simple pulsación de teclas. La palabra macro es una abreviatura de la palabra macroinstrucción. En ocasiones, se ve en la necesidad de realizar una serie de tareas repetitivas de forma rutinaria. Puede crear una macro que le evite ese trabajo. Una macro es en sí un pequeño programa en código Visual Basic que se graba con un nombre y que puede invocar en cualquier momento. Puede asignar una combinación de teclas para invocarla. Cuando grabe una macro, la grabadora de macros graba todos los pasos necesarios para completar las acciones que desea grabar. En los pasos grabados no se incluye el desplazamiento por la cinta de opciones. CAMBIAR LA CONFIGURACIÓN DE SEGURIDAD DE MACROS Para editar y ejecutar macros, debe establecer el nivel de seguridad para habilitar todas las macros temporalmente: 1. En la ficha Programador, en el grupo Código, haga clic en Seguridad de macros.


Página 209

Aster Mérida

2. En Configuración de macros, haga clic en Habilitar todas las macros (no recomendado; puede ejecutarse código posiblemente peligroso) y, a continuación, haga clic en Aceptar. NOTA: Para ayudar a evitar que se ejecute código potencialmente peligroso, se recomienda que vuelva a cualquiera de las configuraciones que deshabilitan todas las macros cuando termine de trabajar con las macros. Antes de grabar una macro Compruebe que se muestra la ficha Programador en la cinta de opciones. Dado que la ficha Programador no se muestra de manera predeterminada, haga lo siguiente: 1. Haga clic en la pestaña Archivo, elija Opciones y, a continuación, haga clic en la categoría Personalizar cinta de opciones. 2. En Personalizar cinta de opciones, en la lista Fichas principales, haga clic en Programador y, a continuación, haga clic en Aceptar.


Página 210

Aster Mérida

Creación y modificación de Macros GRABE UNA MACRO. 1. En el grupo Código en la pestaña Programador, haga clic en Grabar macro y luego haga clic en Aceptar para comenzar a grabar. El cuadro de diálogo Grabar macro aparecerá. 





En el cuadro Nombre de la macro, escriba un nombre para la macro. El primer carácter del nombre de la macro debe ser una letra. Los caracteres siguientes pueden ser letras, números o caracteres de subrayado. No se permiten espacios en un nombre de macro; puede utilizarse un carácter de subrayado como separador de palabras. En el cuadro Tecla de método abreviado, escriba cualquier letra en mayúsculas o minúsculas que desee utilizar para la combinación de teclas que invocará a la macro. Se recomienda no utilizar letras ya asignadas a alguna función predeterminada de Excel, ya que de lo contrario la función predeterminada será sustituida por la macro mientras el libro se tenga abierto. En la lista Guardar macro en, seleccione el libro en el que desea almacenar la macro (Este libro, nuevo libro, Libro personal de macros). Si desea que la macro esté disponible siempre que utilice Excel, seleccione Libro de macros personal (Personal.xlsb)


Página 211

Aster Mérida



En el cuadro Descripción, escriba en caso de ser necesaria una breve descripción acerca de la macro a grabar. Haga clic en Aceptar para comenzar la grabación.

2. Realice algunas acciones en la hoja de cálculo como escribir algún texto, seleccionar algunas columnas o filas o rellenar con algunos datos. 

A partir de este momento, la macro comienza a grabar cualquier pulsación, selección, etc., que realice, por lo que debe tener cuidado en hacer los pasos poco a poco, tratando de no cometer errores.

3. En el grupo Código en la pestaña Programador, haga clic en Detener grabación.

También puede crear una macro mediante Microsoft Visual Basic. En la ficha Programador, en el grupo Código, haga clic en Visual Basic. Si es necesario, en el menú Insertar, haga clic en Módulo. En la ventana de código del módulo, escriba o copie el código de macro que desea utilizar. Para ejecutar la macro desde la ventana del módulo, presione [F5]. En el menú Archivo, haga clic en Cerrar y volver a Microsoft Excel cuando termine de escribir la macro. Para eliminar una macro En la ficha Programador, en el grupo Código, haga clic en Macros. O en la ficha Vista en el grupo Macros haga clic en Macros y a continuación en Ver Macros. En la lista Macros en, seleccione Este libro, o bien el libro en donde se encuentra la macro a eliminar. En el cuadro Nombre de la macro, haga clic en el nombre de la macro que desee eliminar. Haga clic en Eliminar. Para modificar una macro use el editor de Visual Basic. En la ficha Programador, en el grupo Código, haga clic en Macros. O en la ficha Vista en el grupo Macros haga clic en Macros y a continuación en Ver Macros. En la lista Macros en, seleccione Este libro, o bien el libro en donde se encuentra la macro a modificar. En el cuadro Nombre de la macro, haga clic en el nombre de la macro que desee modificar. Haga clic en Modificar. Para ejecutar una macro puede: 

Ejecutar desde el cuadro de Macros. En la ficha Programador, en el grupo Código, haga clic en Macros. O en la ficha Vista en el grupo Macros haga clic en Macros y a continuación en Ver Macros. En el cuadro Nombre de la macro, haga clic en el nombre de la macro y haga clic en Ejecutar.


Página 212

Aster Mérida



 



Ejecutar mediante la combinación de teclas de método abreviado. Si no recuerda o quiere modificar la tecla a utilizar. En la ficha Programador, en el grupo Código, haga clic en Macros. O en la ficha Vista en el grupo Macros haga clic en Macros y a continuación en Ver Macros. En el cuadro Nombre de la macro, haga clic en el nombre de la macro y haga clic en Opciones. En el cuadro Tecla de método abreviado, escriba cualquier letra minúscula o mayúscula que desee utilizar Ejecutar haciendo clic en un botón de la barra de herramientas de acceso rápido. Ejecutar haciendo clic en un área de un objeto gráfico. Para ello en la hoja de cálculo, seleccione un objeto gráfico existente, como una imagen, una imagen prediseñada, una forma o un gráfico SmartArt. Haga clic derecho sobre el objeto y seleccione Asignar macro. En el cuadro Nombre de la macro seleccione la macro que asignará. Haga clic en Aceptar. Ejecutar automáticamente una macro al abrir un libro. Si graba una macro y la guarda usando el nombre "Auto_Abrir", la macro se ejecutará cada vez que se abra el libro que contiene la macro. Otra forma de ejecutar automáticamente una macro al abrir un libro es escribir un procedimiento de Visual Basic para Aplicaciones (VBA) en el evento Open del libro.


Página 213

Aster Mérida

El Editor de Visual Basic Macros y el Editor de Visual Basic Ahora que ya conoce en parte cómo Microsoft Excel 2007 expone su modelo de objetos, puede intentar llamar a los métodos de un objeto y establecer las propiedades del objeto. Para hacerlo, debe escribir el código en un lugar y de una manera que Office pueda comprender; generalmente, mediante el uso del Editor de Visual Basic. Aunque se instala de forma predeterminada, muchos usuarios ni siquiera saben que está disponible hasta que no se habilita en la cinta de opciones. Ficha Programador Todas las aplicaciones de Office 2007 usan la cinta de opciones. La ficha Programador es una de las fichas incluidas en la cinta de opciones, donde se puede tener acceso al Editor de Visual Basic y a otras herramientas de programador. Debido a que Office 2007 no muestra la ficha Programador de manera predeterminada, debe habilitarla mediante el siguiente procedimiento: Para habilitar la ficha Programador 1. En la ficha Archivo, elija Opciones para abrir el cuadro de diálogo Opciones de Excel. 2. Haga clic en Personalizar cinta de opciones en el lado izquierdo del cuadro de diálogo. 3. En Comandos disponibles en:, en el lado izquierdo del cuadro de diálogo, seleccione Comandos más utilizados. 4. En Personalice esta cinta de opciones, en el lado derecho del cuadro de diálogo, seleccione Fichas principales y, a continuación, active la casilla de verificación Programador. 5. Haga clic en Aceptar.


Página 214

Aster Mérida

Después de que Excel muestre la ficha Programador, observe la ubicación de los botones Visual Basic, Macros y Seguridad de macros en la ficha. Editor de Visual Basic El siguiente procedimiento muestra cómo crear un nuevo libro en blanco donde se almacenarán las macros. A continuación, podrá guardar el libro con el formato .xlsm. Para crear un nuevo libro en blanco 1. Haga clic en el botón Macros, de la ficha Programador. 2. En el cuadro de diálogo Macro que aparece, escriba Hello en Nombre de macro. 3. Haga clic en el botón Crear para abrir el Editor de Visual Basic que incluirá los esquemas de una nueva macro ya escritos. VBA es un lenguaje de programación completo y, en consecuencia, tiene un entorno de programación completo. En este artículo solo se estudian las herramientas que se usan para empezar a trabajar en programación sin incluir la mayoría de las herramientas del Editor de Visual Basic. Realizada esta salvedad, cierre la ventana Propiedades en el lado izquierdo del Editor de Visual Basic e ignore las dos listas desplegables que aparecen sobre el código. Figura 2. Editor de Visual Basic

El Editor de Visual Basic contiene el siguiente código. VB Sub Hello() End Sub Sub se refiere a Subrutinaque, por el momento, se puede definir como "macro". Al ejecutar la macro Hello se ejecuta cualquier código que se encuentre entre Sub Hello() y End Sub. Ahora, edite la macro para que tenga un aspecto similar al siguiente código. VB Sub Hello() MsgBox ("Hello, world!") End Sub Vuelva a la ficha Programador en Excel y haga clic de nuevo en el botón Macros. Seleccione la macro Hello en la lista que aparece y, a continuación, haga clic en Ejecutar para mostrar un cuadro de mensaje pequeño que contiene el texto "Hello, world!". Acaba de crear e implementar correctamente código de VBA personalizado en Excel. Haga clic en Aceptar en el cuadro de mensaje para cerrarlo y terminar de ejecutar la macro. Microsoft Excel 2007 Avanzado

Página 215

Aster Mérida

Si no aparece el cuadro de mensaje, compruebe la configuración de seguridad de la macro y reinicie Excel. Accesibilidad de las macros También puede tener acceso al cuadro de diálogo Macros desde la ficha Ver, pero si usa una macro con frecuencia, le resultará más cómodo tener acceso a ella mediante un método abreviado de teclado o un botón de la Barra de herramientas de acceso rápido. Para crear un botón para la macro Hello en la Barra de herramientas de acceso rápido, use el siguiente procedimiento. El siguiente procedimiento describe cómo crear un botón para una macro en la Barra de herramientas de acceso rápido: Para crear un botón para una macro en la Barra de herramientas de acceso rápido 1. Haga clic en la pestaña Archivo. 2. Haga clic en Opciones para abrir el cuadro de diálogo Opciones de Excel y, a continuación, haga clic en Barra de herramientas de acceso rápido. 3. En la lista que se encuentra en Comandos disponibles en:, elija Macros. Busque en la lista el texto que es similar a Book1!Hello y selecciónelo. 4. Haga clic en el botón Agregar >> para agregar la macro a la lista en el lado derecho y, a continuación, haga clic en el botón Modificar…, a fin de seleccionar una imagen del botón para asociar a la macro. 5. Haga clic en Aceptar. El nuevo botón deberá mostrarse en la Barra de herramientas de acceso rápido, encima de la ficha Archivo. Ahora puede ejecutar rápidamente la macro en cualquier momento sin tener que usar la ficha Programador: inténtelo.


Página 216

Aster Mérida


Página 217

Aster Mérida


Página 218

Aster Mérida

Laboratorio 1: MÓDULO 9: Controles de Formulario y Macros Ejercicio 1. Automatización de Tareas Repetitivas con Macros. En este ejercicio grabará una Macro que aplique formato a celdas y hará una modificación de la misma por medio del Editor de Visual Basic. Tareas


1. Abra un archivo nuevo de Excel 2. Grabe una macro que aplique un formato a celdas.

          

3. Modifique la macro creada por medio del editor de Visual Basic para que la celda B1 tenga el estilo cursiva.

        


    

En la ficha Vista en el grupo Macros, despliegue Macros y haga clic en Grabar macro. El cuadro de diálogo aparecerá. En el cuadro Nombre de la macro escriba Formato. En el cuadro Método abreviado escriba q. Haga clic en Aceptar. La grabación de la macro comenzará. Seleccione el rango D6:O36. En la ficha Inicio en el grupo Fuente haga clic en Negrita. En la ficha Inicio en el grupo Fuente, despliegue la herramienta Relleno y haga clic en el color de su preferencia. Haga clic en la celda B1 y teclee “Reporte de Movimientos por Día y por Hora. Seleccione la celda B1 y en la ficha Inicio en el grupo Fuente haga clic en Negrita. En la barra de estado haga clic en el icono Detener grabación. Sitúese en la Hoja2 y ejecute la macro utilizando el método abreviado CTRL + q. En la ficha Vista en el grupo Macros, despliegue Macros y haga clic en Macros. El cuadro de diálogo aparecerá. Seleccione la macro Formato. Haga clic en el botón Modificar. El editor de Visual Basic aparecerá. Sitúese antes de la orden EndSub y deje una línea en blanco. Escriba en esa línea Selection.Font.Italic = True Haga clic en el menú Archivo y seleccione Cerrar y volver a Microsoft Excel. Sitúese en la Hoja2 y ejecute la macro utilizando el método abreviado CTRL + q. Verifique que el contenido de la celda B1 esté en cursiva. Haga clic en la ficha Archivo, despliegue y de clic en Guardar Como… El cuadro de diálogo Guardar como… aparecerá. Vaya a la carpeta de Prácticas que creo anteriormente en Mis Documentos. En el cuadro Nombre de archivo escriba Mod9 Lab1. En el cuadro Guardar como tipo verifique que diga Libro de Excel. De clic en el botón Guardar.


Página 219

Aster Mérida


Página 220

Aster Mérida

Controles de Formulario CONTROLES DE FORMULARIO Sí, es verdad. Puede crear excelentes formularios con poco o ningún código de Microsoft Visual Basic para Aplicaciones (VBA) o en Microsoft Excel. Mediante el uso de formularios y de los numerosos controles y objetos que es posible agregarles, puede mejorar de manera notable la entrada de datos en las hojas de cálculo y el modo en que dichas hojas se muestran. ¿QUÉ ES UN FORMULARIO? Un formulario, ya sea impreso o en línea, es un documento diseñado con formato y estructura estándar que facilita la captura, la organización y la edición de la información. Los formularios impresos contienen instrucciones, formato, etiquetas y espacios en blanco para escribir datos. Puede usar Excel y plantillas de Excel para crear formularios impresos. Los formularios en línea tienen las mismas características que los formularios impresos. Además, contienen controles, que son objetos que muestran datos o hacen que sea más fácil para los usuarios entrar o editar los datos, realizar una acción o seleccionar una opción. En general, los controles facilitan el uso de los formularios. Algunos ejemplos de controles comunes son los cuadros de lista, los botones de opción y los botones de comando. Los controles también pueden ejecutar


Página 221

Aster Mérida

macros asignadas y responder a eventos, tales como clics del mouse, mediante la ejecución de código de Visual Basic para Aplicaciones (VBA). TIPOS DE FORMULARIOS DE EXCEL Existen diversos tipos de formularios que puede crear en Excel: formularios de datos (tema tratado en el módulo 5), hojas de cálculo que contienen controles ActiveX y de formulario, y formularios del usuario de VBA. Puede usar cada tipo de formulario por separado o puede combinarlos de diferentes maneras para crear una solución que sea apropiada para su caso particular. 

Hoja de cálculo con controles ActiveX y de formulario

Una hoja de cálculo es un tipo de formulario que permite que el usuario escriba datos y los vea en una cuadrícula; existen diversas características similares a los controles ya integradas en las hojas de cálculo de Excel, como validación de datos y comentarios. Las celdas se asemejan a cuadros de texto dado que el usuario puede escribir texto y aplicarles formato de diversas maneras. Las celdas con frecuencia se usan como etiquetas y, ajustando su alto y ancho, así como combinándolas, puede hacer que una hoja de cálculo se comporte como un simple formulario de entrada de datos. Otras características similares a los controles, como los comentarios de celda, los hipervínculos, las imágenes de fondo, la validación de datos, el formato condicional, los gráficos incrustados y el Filtro automático, pueden hacer que una hoja de cálculo se comporte como un formulario avanzado. Para incrementar la flexibilidad, puede agregar controles y otros objetos de dibujo al lienzo de dibujo de una hoja de cálculo, y combinarlos y coordinarlos con las celdas de la hoja de cálculo. Por ejemplo, puede usar un control de cuadro de lista para facilitar la selección por parte del usuario de un elemento de una lista, o bien puede usar un control de control de número para facilitar la escritura de un número por parte de un usuario. La mayor parte del tiempo, muchos de estos controles también pueden vincularse con celdas de la hoja de cálculo y no requieren código de VBA para hacer que funcionen. Excel tiene dos tipos de controles: controles de formulario y controles ActiveX. Además de estos conjuntos de controles, también puede agregar objetos desde las Herramientas de dibujo, como autoformas, WordArt, elementos gráficos SmartArt o cuadros de texto. CONTROLES DE FORMULARIO Los controles de formulario son controles originales que son compatibles con versiones anteriores de Excel, a partir de la versión 5.0 de Excel. Los controles de formulario también están diseñados para usarse en hojas de macros XLM.


Página 222

Aster Mérida

Los controles de formulario se usan cuando se desea hacer referencia e interactuar fácilmente con datos de celda sin usar código de VBA y cuando se desea agregar controles a hojas de gráfico (hoja de gráfico: hoja de un libro que contiene sólo un gráfico. Una hoja de gráfico es muy útil si se desea ver un gráfico o un informe de gráfico dinámico separado de los datos de una hoja de cálculo o un informe de tabla dinámica.). Por ejemplo, después de agregar un control de cuadro de lista a una hoja de cálculo y vincularlo con una celda, puede devolver un valor numérico para la posición actual del elemento seleccionado en el control. Después, puede usar dicho valor numérico junto con la función INDICE para seleccionar elementos diferentes de la lista. También puede ejecutar macros usando los controles de formulario. Puede adjuntar una macro existente a un control, o bien escribir o grabar una nueva macro. Cuando un usuario del formulario hace clic en el control, éste ejecuta la macro. No obstante, estos controles no se pueden agregar a formularios del usuario, usarse para controlar eventos, ni modificarse para que ejecuten scripts web en páginas web. EJEMPLOS DE CONTROLES DE FORMULARIO Casilla de verificación: Permite a un usuario seleccionar o anular la selección de uno o varios valores en un grupo de opciones. Puede activar más de una casilla de verificación a la vez en una hoja de cálculo o en un cuadro de grupo. Por ejemplo, puede usar una casilla de verificación para crear un formulario de pedido que contenga una lista de artículos disponibles o en una aplicación de seguimiento de inventario para mostrar si se ha interrumpido la producción de un artículo. Casilla de verificación (control de formulario)

Casilla de verificación (control ActiveX)

Botón de opción: Permite una sola opción de un conjunto limitado de opciones mutuamente excluyentes. Por lo general, un botón de opción (o botón de radio) está contenido en un marco o cuadro de grupo. Por ejemplo, se puede usar un botón de opción en un formulario de pedido para que un usuario pueda seleccionar un tamaño de un intervalo de tamaños, como pequeño, mediano, grande o extra grande.


Página 223

Aster Mérida

También se puede usar para seleccionar diferentes opciones de envío, como por tierra, expreso o al día siguiente. Botón de opción (control de formulario)


Botón de opción (control ActiveX)

Página 224

Aster Mérida

Gráficos con Controles Una vez que se ha mencionado la utilidad de los controles de formulario, podemos hacer uso de los controles según la necesidad. A continuación se enlistan algunos controles con una pequeña descripción de sus funciones:

NOMBRE DEL BOTÓN Etiqueta

DESCRIPCION Identifica el propósito de una celda o un cuadro de texto, o muestra texto descriptivo (como títulos, leyendas, imágenes) o breves instrucciones.

Cuadro de grupo

Agrupa controles relacionados en una unidad visual en un rectángulo con una etiqueta opcional. Generalmente, se agrupan botones de opción, casillas de verificación o contenido estrechamente relacionado.

Botón

Ejecuta una macro que realiza una acción cuando un usuario hace clic en él. Los botones también se conocen como botones de comando.

Casilla de verificación

Activa o desactiva un valor que representa una selección inequívoca entre opuestos. Puede seleccionar más de una casilla de verificación en una hoja de cálculo o en un cuadro


Página 225

Aster Mérida

NOMBRE DEL BOTÓN

DESCRIPCION de grupo. Una casilla de verificación puede tener uno de tres estados: activada, desactivada y mixta, lo que significa una combinación de los estados activada y desactivada (como en una selección múltiple).

Botón de opción

Permite una única elección dentro de un conjunto limitado de opciones que se excluyen mutuamente; un botón de opción generalmente está contenido en un cuadro de grupo o un marco. Un botón de opción puede tener uno de tres estados: activado, desactivado y mixto, lo que significa una combinación de los estados activado y desactivado (como en una selección múltiple). Los botones de opción también se conocen como botones de radio.

Cuadro de lista

Muestra una lista de uno o más elementos de texto de entre los cuales puede elegir el usuario. Use un cuadro de lista para mostrar grandes cantidades de opciones que varían en número o contenido. Existen tres tipos de cuadros de lista: Un cuadro de lista de selección única permite solamente una elección. En este caso, un cuadro de lista se asemeja a un grupo de botones de opción, a excepción de que un cuadro de lista puede controlar un gran número de elementos de manera más eficiente. Un cuadro de lista de selección múltiple permite una elección o elecciones contiguas (adyacentes). Un cuadro de lista de selección extendida permite una elección, elecciones y no contiguas, o inconexas.



 

Cuadro combinado

Combina un cuadro de texto con un cuadro de lista para crear un cuadro de lista desplegable. Un cuadro combinado es más compacto que un cuadro de lista pero requiere que el usuario haga clic en la flecha abajo para mostrar una lista de elementos. Use un cuadro combinado para permitir que un usuario escriba una entrada o elija solamente un elemento de la lista. El control muestra el valor actual en el cuadro de texto, sin importar el modo en que dicho valor se haya proporcionado.

Barra de desplazamiento

Se desplaza por un intervalo de valores cuando el usuario hace clic en las flechas de desplazamiento o arrastra el cuadro de desplazamiento. Además, se puede mover por una página (en un intervalo preestablecido) de valores haciendo clic en el área entre el cuadro de desplazamiento y cualquiera de las flechas de desplazamiento. Generalmente, el usuario


Página 226

Aster Mérida

NOMBRE DEL BOTÓN

DESCRIPCION también puede escribir un valor de texto directamente en un cuadro de texto o una celda asociados.

Control de número

Aumenta o disminuye un valor, como un incremento numérico, una hora o una fecha. Para incrementar el valor, es necesario hacer clic en la flecha arriba; para disminuirlo, se debe hacer clic en la flecha abajo. Generalmente, el usuario también puede escribir un valor de texto directamente en un cuadro de texto o una celda asociados

AGREGAR UNA CASILLA DE VERIFICACIÓN (CONTROL DE FORMULARIO) 1. En la ficha Programador, en el grupo Controles, haga clic en Insertar y, a continuación, en Controles de formulario, haga clic en Casilla de verificación . 2. Haga clic en la ubicación de la hoja de cálculo donde desea que aparezca la esquina superior izquierda del control. EDITAR LAS PROPIEDADES DE LOS CONTROLES 1. Seleccione el control. 2. En la ficha Programador, en el grupo Controles, haga clic en Propiedades . También puede hacer clic con el botón secundario en el control y, a continuación, hacer clic en Formato de control. Para especificar las propiedades del control casilla de verificación, siga este procedimiento: a. En Valor, especifique el estado inicial de la casilla de verificación siguiendo uno de estos procedimientos: 

 

Para mostrar una casilla de verificación que tiene una marca de verificación, haga clic en Activada. Una marca de verificación indica que la casilla de verificación está activada. Para mostrar una casilla de verificación desactivada, haga clic en Desactivada. Para mostrar una casilla de verificación con sombra, haga clic en Mixta. La sombra indica una combinación de los estados activado y desactivado; por ejemplo, cuando hay una selección múltiple.

b. En el cuadro Vincular con la celda, escriba una referencia de celda que contenga el estado actual de la casilla de verificación:


Página 227

Aster Mérida



Cuando la casilla de verificación está activada, la celda vinculada devuelve un valor TRUE.  Cuando la casilla de verificación está desactivada, la celda vinculada devuelve un valor FALSE. NOTA: Cuando la celda vinculada está vacía, Excel interpreta el estado de la casilla de verificación como FALSE. 

Si el estado de la casilla de verificación es mixto, la celda vinculada devuelve un valor de error #N/A.

Use el valor devuelto en una fórmula para responder al estado actual de la casilla de verificación. Por ejemplo, un formulario de encuesta de viaje contiene dos casillas de verificación denominadas Europa y Australia en un cuadro de grupo Lugares visitados. Estas dos casillas de verificación están vinculadas a las celdas C1 (para Europa) y C2 (para Australia). Cuando un usuario activa la casilla de verificación Europa, la siguiente fórmula de la celda D1 se evalúa como "Visitados en Europa": =SI(C1=TRUE,"Visitados en Europa","Nunca visitó Europa") Cuando un usuario desactiva la casilla de verificación Australia, la siguiente fórmula de la celda D2 se evalúa como "Nunca visitó Australia": =SI(C2=TRUE,"Visitados en Australia","Nunca visitó Australia") Si tiene tres estados para evaluar (Activada, Desactivada y Mixta) en el mismo grupo de opciones, puede usar las funciones ELEGIR o BUSCAR de forma similar. Para obtener más información, vea los temas sobre la función ELEGIR y la función BUSCAR. NOTA: El tamaño de la casilla de verificación dentro del control y la distancia a la que se encuentra de su texto asociado no se puede ajustar. De manera similar podrá utilizar los controles de formulario como los botones de opción, los cuadros de lista, listas combinadas.


Página 228

Aster Mérida

Agregar Botones de Macros Para agregar un botón de macro a la barra de acceso rápido haga clic la ficha Archivo y, a continuación, haga clic en Opciones. En el cuadro de dialogo Opciones de Excel, haga clic en el panel izquierdo a la opción Barra de Herramientas de Acceso Rápido en la lista Comandos disponibles en, seleccione Macros. En el cuadro de lista, haga clic en la macro que desee agregar y, a continuación, en Agregar. Haga clic en Aceptar. El botón correspondiente a la macro se agregará a la barra de herramientas de acceso rápido. También puede agregar el botón Ver Macros de la lista de Comandos más utilizados. Si agrega el botón Ver Macros en la barra de herramientas de acceso rápido, haga clic en él y en el cuadro Nombre de la macro, haga clic en la macro que desea ejecutar y, a continuación, en Ejecutar. Para ejecutar automáticamente una macro al abrir un libro, guárdela con el nombre "Auto_Abrir", la macro se ejecutará cada vez que se abra el libro que contiene la macro. Otra forma de ejecutar automáticamente una macro al abrir un libro es escribir un procedimiento de Visual Basic para Aplicaciones (VBA) en el evento Open del libro


Página 229

Aster Mérida


Página 230

Aster Mérida


Página 231

Aster Mérida

Laboratorio 2: MÓDULO 9: Controles de Formulario y Macros. En este ejercicio creará una gráfica, le hará modificaciones, agregará controles de formularios y aplicará formato, con la finalidad de añadir un elemento que nos permita con un clic mostrar u ocultar cada una de las series Tareas


1. Abra el archivo llamado Reporte Anual.

2. Inserte un gráfico de líneas.

  

  3. Inserta 3 casillas de verificación de la barra de formularios, uno para cada serie.

    

    4. Define una celda para cada uno de las casillas de verificación insertadas.

5. crear cuatro nombres definidos,

Haga clic en la celda B1. En la ficha insertar en el grupo gráficos haga clic en la categoría de gráfico Líneas 2D y seleccione Líneas. El grafico se insertará en la hoja, en caso de ser necesario mueva el gráfico a un lado de la tabla de datos, por ejemplo en la columna F. Si es necesario activa la ficha programador, haciendo clic en la ficha Archivo, luego en opciones. En el cuadro de diálogo Opciones de Excel hacer clic en Personalizar cinta de opciones. En la columna Personalizar la cinta de opciones, active la casilla programador y haga clic en Aceptar. En la ficha programador en el grupo controles haga clic en insertar, y seleccione Casilla de Verificación (Control de Formulario). Haga un clic en la celda F17, y el control se insertará. Clic con el botón derecho sobre la casilla de verificación y elija Modificar texto. Escribe en el control “Ventas”. Repite los últimos 4 pasos para insertar las 2 casillas restantes con el texto “Costo” y “Ganancias” ubicadas en las celdas H17 y J17 respectivamente. Haga clic derecho sobre la casilla de verificación con la etiqueta Ventas y elija Formato de Control. Clic en la ficha control y luego en el campo Vincular con la celda. Seleccione la celda $F$18 y haga clic en aceptar. Repite los primeros 3 pasos para vincular las celdas $H$18 y $J$18 con controles “Costo” y “Ganancias” respectivamente. NOTA: Cuando la casilla está activada, el valor de la celda vinculada pasa a ser VERDADERO; cuando no lo está el valor será FALSO. Vamos a aprovechar estos valores en nuestro mecanismo.

DEFINA EL RANGO DE LOS MESES  Seleccione el rango de celdas de la A1: A13.


Página 232

Aster Mérida

uno para cada serie y uno para los valores de las categorías (el eje de las X, los meses)



En la ficha fórmulas en el grupo nombres definidos, haga clic en administrador de nombres y luego clic en nuevo.  En el campo Nombre escribe “Meses”.  En el campo Hace Referencia a, debe estar indicado: ='Reporte Anual'!$A$1:$A$13.  Haga clic en Aceptar. CREA EL NOMBRE PARA LA CATEGORIA DE VENTAS  Haga clic en Nuevo.  En el campo Nombre escribe “Ventas”.  En el campo Hace Referencia a, escribe lo siguiente: =SI(  Luego hace clic en la celda F18 y seguido escribe una coma.  Selección el rango B2:B13 y seguido escribe una coma,  Por ultimo selecciona el rango E1:E13, cierre el paréntesis y haga clic en aceptar. La fórmula debe quedar así: =SI('Rpt Anual'!$F$18,'Rpt Anual'!$B$2:$B$13,'Rpt Anual'!$E$1:$E$13) DEFINIE EL NOMBRE PARA LA CATEGORIA COSTO.  Haga clic en Nuevo.  En el campo Nombre escribe “Costo”.  En el campo Hace Referencia a, escribe lo siguiente: =SI(  Luego hace clic en la celda H18 y seguido escribe una coma.  Selección el rango C2:C13 y seguido escribe una coma,  Por ultimo selecciona el rango E1:E13, cierre el paréntesis y haga clic en aceptar. La fórmula debe quedar así: =SI('Rpt Anual'!$H$18,'Rpt Anual'!$C$2:$C$13,'Rpt Anual'!$E$2:$E$13) DEFINE EL NOMBRE PARA LA CATEGORIA GANANCIA.  Haga clic en Nuevo.  En el campo Nombre escribe “Ganancia”.  En el campo Hace Referencia a, escribe lo siguiente: =SI(  Luego hace clic en la celda J18 y seguido escribe una coma.  Selección el rango D2:D13 y seguido escribe una coma,  Por ultimo selecciona el rango E1:E13, cierre el paréntesis y haga clic en aceptar. La fórmula debe quedar así: =SI('Rpt Anual'!$J$18,'Rpt Anual'!$D$2:$D$13,'Rpt Anual'!$E$2:$E$13) 

Finalmente haga clic en Cerrar. NOTA: Como puede verse, estos nombres contienen fórmulas condicionales. Si el valor de la casilla de verificación es VERDADERO, la condición se cumple y el rango es el definido en la tabla de datos; si el valor es FALSO, el rango elegido es E2:E13 que no contiene ningún valor (y por lo tanto no tiene representación en el gráfico).

6. Reemplazar los rangos relevantes en

EDITE LA SERIE VENTAS DEL GRÁFICO  Haga clic dentro del gráfico a la serie ventas.


Página 233

Aster Mérida

la función SERIES de cada una de las series con los nombres definidos.

    

En Herramientas de Gráficos haga clic en la ficha Diseño y luego en Seleccionar Datos en el grupo Datos. En el cuadro de dialogo seleccionar orígenes de datos, seleccione ventas y haga clic en Editar. En el cuadro de dialogo Modificar Serie, haga clic en el campo Valores de la serie, y borre el texto que se encuentra después de: 'Rpt Anual'! Presione la tecla [F3]. Del cuadro de dialogo pegar nombre, seleccione Ventas y haga clic en aceptar 2 veces. 'RptAnual'!Ventas

EDITE LA SERIE COSTO DEL GRÁFICO  Haga clic dentro del gráfico a la serie costo.  En Herramientas de Gráficos haga clic en la ficha Diseño y luego en Seleccionar Datos en el grupo Datos.  En el cuadro de dialogo seleccionar orígenes de datos, seleccione Costo y haga clic en Editar.  En el cuadro de dialogo Modificar Serie, haga clic en el campo Valores de la serie, y borre el texto que se encuentra después de: 'Rpt Anual'!  Presione la tecla [F3].  Del cuadro de dialogo pegar nombre, seleccione Costo y haga clic en aceptar 2 veces. 'RptAnual'!Costo EDITE LA SERIE GANANCIAS DEL GRÁFICO  Haga clic dentro del gráfico a la serie ventas.  En Herramientas de Gráficos haga clic en la ficha Diseño y luego en Seleccionar Datos en el grupo Datos.  En el cuadro de dialogo seleccionar orígenes de datos, seleccione Ganancias y haga clic en Editar.  En el cuadro de dialogo Modificar Serie, haga clic en el campo Valores de la serie, y borre el texto que se encuentra después de: 'Rpt Anual'!  Presione la tecla [F3].  Del cuadro de dialogo pegar nombre, seleccione Ventas y haga clic en aceptar 2 veces. 'RptAnual'!Ganancias 7. oculte la fila 18.

8. Oculte o Muestre las series deseadas.

9. Guarda el archivo con el nombre Mod9 Lab2.



Haga clic derecho en el encabezado de la fila 18 y seleccione la opción Ocultar.



Haga clic para activar las casillas de verificación que sean de su preferencia, para mostrar 1, 2 o las 3 series en el gráfico.



Haga clic en la ficha Archivo, despliegue y seleccione Guardar Como… El cuadro de diálogo Guardar como… aparecerá. Vaya a la carpeta de Mis Documentos. Puede utilizar los botones que se encuentran a la izquierda en el cuadro. Sitúese en la carpeta de Prácticas. En el cuadro Nombre de archivo escriba Mod9 Lab2.

 


Página 234

Aster Mérida

  

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


Página 235

Manual Excel 2007 Nivel Avanzo v.1

Recommend Documents