Excel Especialista

TABLA DE CONTENIDO MICROSOFT EXCEL 2010 ESPECIALISTA Módulo 1 – Herramientas Gerenciales Tablas dinámicas................................................................................ 3 Componentes de las tablas dinámicas .................................................... 3 Objetivo de una tabla dinámica ............................................................. 4 Crear una tabla dinámica ..................................................................... 4 Diseño e impresión ............................................................................ 13 Agrupar y desagrupar datos................................................................ 17 Desagrupar elementos ....................................................................... 20 Desagrupar celdas ............................................................................ 20 Mostrar u ocultar los detalles de un informe ......................................... 21 Formatos de tabla dinámica ................................................................ 23 Ordenar campos en tablas dinámicas ................................................... 24 Actualizar......................................................................................... 25 Cambiar origen de datos .................................................................... 25 Acciones en la tabla dinámica ............................................................. 27 Elementos y campos calculados en tablas dinámicas .............................. 27 Para crear un elemento calculado a un campo .................................... 28 Para crear un campo calculado a un campo ......................................... 29 Gráficos Dinámicos ............................................................................ 31 Consolidación ................................................................................... 33

Módulo 2 - Consultas Externas Datos externos ................................................................................. 39 Tablas Dinámicas de Consolidación Múltiple .......................................... 42 Consultas Access............................................................................... 45 Consultas Query ............................................................................... 49 Crear consultas con MS Query............................................................. 52 Consultas desde la Web .................................................................... 55 Conexiones desde Excel a Datos de Word ............................................ 57 Quitar conexión de datos ................................................................... 57 Importar Archivos de texto ................................................................ 58 Obtener datos externos...................................................................... 58 Conexiones de Microsoft Excel con Microsoft Outlook .............................. 58 Guardar libros como WEB .................................................................. 59

PowerPivot ....................................................................................... 59 Segmentación de datos vertical y horizontal.......................................... 72 Realizar un gráfico dinámico ............................................................... 74

Módulo 3 - Soluciones dinámicas Buscar Objetivo ................................................................................ 77 Utilizar Buscar Objetivo ...................................................................... 78 Escenarios ....................................................................................... 80 Crear un escenario ........................................................................... 80 Mostrar escenarios ............................................................................ 83 Modificar o eliminar escenarios............................................................ 83 Resumen Escenarios .......................................................................... 83 Solver ............................................................................................. 85 Habilitar Solver ................................................................................. 85 Utilizar Solver ................................................................................... 87 Cambiando las celdas de variables ....................................................... 90

Módulo 4 - Automatización Grabadora de macros ........................................................................ 95 Nombre de la macro .......................................................................... 96 Teclas de método abreviado ............................................................... 96 Pasos para crear una macro ............................................................... 97 Pasos para ver macros ....................................................................... 98 Ver el código macro ........................................................................... 99 Pasos para eliminar una macro ........................................................... 99 Ejecutar y asignar macros ................................................................ 100 Ejecutar mediante Objetos gráficos .................................................... 100 Creando un botón en la Barra de acceso rápido ................................... 102 Seguridad ...................................................................................... 104 Configuración de la seguridad de macros ............................................ 105

Microsoft Excel 2010 Especialista

MÓDULO 1 Herramientas Gerenciales Tablas dinámicas Los informes de tablas dinámicas permiten al usuario resumir la información por categorías y subcategorías, totalizar los valores para facilitar la toma de decisiones y analizar los datos desde diferentes puntos de vista, permiten manipular grandes cantidades de datos y agregar nuevos campos con fórmulas personalizadas. Es muy ágil el manejo de los formatos para presentar comparaciones, facilitan la organización y el resumen de datos en bases de datos. Componentes de las tablas dinámicas 1. Campos de filas: Son los campos de base de datos que están ubicados en el área de Filas, representan las categorías por las cuales se resume la información. 2. Campos de columnas: Son los campos de la base de datos ubicados en el área de Columnas, representan las categorías por las cuales se resume la información. 3. Campos de página: Son los campos de la base de datos ubicados en el área de Página, que permiten mostrar los registros que cumplen un determinado criterio, muy similar al manejo de autofiltros. 4. Campo de datos: Es el campo de la base de datos ubicado en el área de Datos que se va a totalizar, con las funciones para realizar totales. Normalmente, un campo de datos resume datos numéricos, 3


pero también puede contener texto. Los valores numéricos de forma predeterminada utilizan la función de SUMA y los valores texto tienen como función predeterminada CONTAR. 5. Campos calculados: Corresponden a los campos que se crean en el área de datos con fórmulas, estos campos también los puede crear el usuario. 3 2

1

4

5

5 Objetivo de una tabla dinámica El objetivo de la tabla dinámica, es aplicar herramientas de análisis para la toma de decisiones (costos, presupuestos, inventarios, ventas) en bases de datos robustas, grandes o complejas donde la manipulación y administración de la información es compleja. Crear una tabla dinámica Al crear un informe de tabla o gráfico dinámico, se conecta con un origen de datos y luego se especifica la ubicación del informe. 1. Seleccionar una celda de la base de datos. 2. En la ficha Insertar, en el grupo Tablas, hacer clic en Tabla dinámica y, a continuación, seleccionar Tabla dinámica.

4


3. En la opción Seleccione una tabla o rango; si estaba ubicado en una celda de la base de datos, el rango se visualiza en este cuadro, si la ubicación es una celda vacía se debe seleccionar el rango de celdas o la referencia del nombre de tabla.

4. Si los datos de origen se encuentran en otro archivo en una fuente de conexión externa: 

Seleccione Utilice una fuente de datos externa, luego dar clic en Elegir conexión.



Se mostrará el cuadro de diálogo Conexiones existentes.

5




Seleccionar en la lista desplegable Mostrar, de la parte superior del cuadro de diálogo una de las categorías de conexiones existentes o, Todas las conexiones existentes (que es el valor predeterminado).



Seleccionar una conexión del cuadro de lista Seleccionar una conexión y hacer clic en Abrir.

5. En la parte inferior permite activar la ubicación de la tabla dinámica. 

Para poner el informe de tabla dinámica en una hoja de cálculo nueva que empiece por la celda A1, hacer clic en Nueva hoja de cálculo.



Para ubicar el informe de tabla dinámica en una hoja de cálculo existente, seleccionar Hoja de cálculo existente y, a continuación, escribir la primera celda del rango de celdas donde se desea situar el informe de tabla dinámica.

6. Dar clic en Aceptar.

6


Excel crea una tabla dinámica en blanco para insertar los campos en las diferentes ubicaciones.

Para comenzar a crear la tabla dinámica se deben colocar los campos a analizar. Para ubicar los campos en la tabla dinámica se puede arrastrar los Encabezados de las columnas situados en la Lista de Campos de tabla dinámica con clic sostenido al área de diseño donde aparecen los campos de rótulos de fila, columna, o datos, dependiendo de la información requerida o dar clic en el cuadro de selección del campo que se va a agregar. 1. Del cuadro lista de campos, arrastrar el campo Departamento a rótulos de fila parte inferior.

7


2. Del cuadro lista de campos, arrastrar el campo Delegación a rótulos de columna. 3. Del cuadro lista de campos, arrastrar el campo Sueldo a valores.

El botón Lista de Campos permite cambiar la forma de visualización de los campos y las áreas de la tabla dinámica.

8


Para organizar los campos una vez elaborada la tabla dinámica se puede dar clic derecho en el nombre de campo en una de las áreas y, a continuación, seleccionar la opción Mover y elegir una de las opciones de mover.

9


Configuración de campo Para cambiar el nombre de un campo se debe situar sobre el área de diseño y en la celda del título del campo y escribir el nuevo nombre. Al dar clic en alguno de los campos que se encuentran en la lista de campos se encuentra la opción Configuración de campo; la cual permite controlar las diversas configuraciones de formato, impresión, subtotales y filtros de los campos de un informe de tabla dinámica. También se puede encontrar en la ficha Opciones de la barra Herramientas de tabla dinámica.

10


El cuadro Configuración de campo cambia su aspecto dependiendo de la ubicación del campo en el diseño de la tabla dinámica. Para campos de Fila, Columna o Página son los siguientes: Nombre personalizado: Muestra el nombre del campo actual en el informe de tabla dinámica o el nombre del origen si no hay ningún nombre personalizado. Subtotales

Automáticos: Utiliza la función predeterminada para el campo. Ninguno: Muestra el campo sin ningún subtotal. Personalizados: Habilita la selección de una de las funciones siguientes como subtotal.

11

Microsoft Excel 2010 Especialista Función

Descripción

Suma

La suma de los valores. Es la función predeterminada de los valores numéricos.

Contar

Cuenta las celdas que tengan un valor ya sea numérico o texto

Promedio

El promedio de los valores.

Máx

El valor máximo.

Mín

El valor mínimo.

Producto

El producto de los valores.

Contar números

Cuenta las celdas que tengan un valor numérico.

DesvEst

Un cálculo de la desviación estándar de una población, donde la muestra es un subconjunto de toda la población.

Filtros: La opción Incluir nuevos elementos en el filtro manual, al activar o desactivar esta casilla de verificación se incluyen o excluyen nuevos elementos en un informe de tabla dinámica con un filtro aplicado.

12


Diseño e impresión Mostrar etiquetas de elementos en formato de esquema: Activar esta casilla de verificación para ver los elementos de los campos en formato de esquema. Mostrar elementos del campo siguiente en la misma columna (forma compacta): Activar o desactivar esta casilla de verificación para mostrar u ocultar los rótulos del campo siguiente en la misma columna de forma compacta. Mostrar subtotales en la parte superior de cada grupo: Si se habilita esta casilla de verificación permite mostrar los subtotales en la parte superior de cada grupo. Mostrar etiquetas de elementos en formato tabular: Active esta casilla de verificación para ver los elementos de los campos en formato tabular. Esta configuración sólo afecta a los campos situados en el área de rótulos de fila. Insertar línea en blanco después de cada elemento: Activar esta casilla de verificación para insertar una línea en blanco a continuación de cada elemento y aumentar el espaciado de elementos como los subtotales. Mostrar elementos sin datos: Al activar esta casilla de verificación muestra los elementos que no contengan datos.

13


Para campos de Valores Nombre de origen: Indicar el nombre del campo en el origen de datos. Nombre personalizado: Muestra el nombre del campo actual en el informe de tabla dinámica, o el nombre del origen si no hay ningún nombre personalizado. Resumir Por muestra: Representa la función utilizada para el resumen de los datos en los campos de valor, si se desea modificar, se debe seleccionar nuevamente la función.

14


Definir campos en términos de % Función

Resultado

Diferencia de

Muestra los valores como la diferencia del valor de Elemento base en Campo base.

% de

Muestra los valores como un porcentaje del valor de Elemento base en Campo base.

% de la diferencia de

Muestra los valores como la diferencia de porcentaje del valor de Elemento base en Campo base.

Total en

Muestra los valores de elementos Campo base como un total.

sucesivos

en

% de la fila

Muestra el valor de cada fila o categoría como un porcentaje del total de la fila o categoría.

% de la columna

Muestra todos los valores de cada columna ó serie como un porcentaje del total de la columna ó serie.

% del total

Muestra los valores como un porcentaje del total general de todos los valores o puntos de datos en el informe.

Índice

Calcula los valores como se indica a continuación: ((valor en celda) x (Suma total de sumas totales)) / ((Suma total de fila) x (Suma total de columna)) 15


Ejercicio Determinar en términos de %, las ventas por delegación, para cada Departamento. 1. En la tabla dinámica sobre el campo cantidad que se encuentra en la lista de campos dar clic sobre la flecha desplegable. 2. Del menú contextual seleccionar configuración del campo valor. 3. En la ventana configuración del campo valor, hacer clic sobre la pestaña mostrar valores como. 4. De la lista desplegable seleccionar % de la fila

5. Hacer clic sobre el botón Aceptar.

16


Agrupar y desagrupar datos Esta opción permite agrupar por números o textos una sección de la tabla dinámica. Agrupar elementos numéricos 1. Seleccionar el campo numérico en el informe de tabla dinámica que se desea agrupar. 2. En la ficha Opciones, en el grupo Agrupar, hacer clic en Agrupar campos. 3. En el cuadro Comenzar en, escribir el primer elemento que se desee agrupar. 4. En el cuadro Terminar en, escribir el último elemento que se desee agrupar. 5. En el cuadro Por, escribir un número que represente el intervalo incluido en cada grupo.

Agrupar fechas u horas 1. Seleccionar el campo de fecha u hora en el informe de tabla dinámica que se desea agrupar. 17


2. Dar clic derecho en el campo de fechas de la tabla dinámica o en el campo de la lista de campos dar clic en el botón desplegable del campo, dar clic en la opción Agrupar campos. 3. En los campos Comenzar en y Terminar en, escribir la fecha por la que se desee agrupar. 4. En el cuadro Por, hacer clic en uno o más períodos de tiempo para los grupos. La opción de días en la parte inferior permite determinar el número exacto.

1 3 4 2 2

Agrupar elementos seleccionados Seleccionar los elementos del informe de tabla dinámica que se deseen agrupar. En la ficha Opciones, en el grupo Agrupar, hacer clic en Agrupar selección.

18


Ejemplo 1. En la tabla dinámica seleccionar los 4 primeros vendedores que aparecen. 2. Hacer clic derecho del mouse y seleccionar Agrupar, o en la ficha Opciones dar clic en el grupo de opciones Agrupar y seleccionar Agrupar Selección.

3. Se crea un nuevo campo a la parte superior llamado Grupo1. Cambiar el rótulo Grupo1 por Zona Norte, ubicarse en la celda con el rótulo y escribir el nombre.

19


Desagrupar elementos Seleccionar los elementos que se deseen desagrupar. En la ficha Opciones, en el grupo Agrupar, hacer clic en Desagrupar. También estas opciones de agrupamiento se pueden realizar dando clic derecho sobre el campo que se encuentra agrupado y seleccionar la opción desagrupar. Desagrupar celdas 1. Seleccionar todo el rango de celdas o clic en el rótulo del campo que este agrupado. 2. Hacer clic derecho del mouse y seleccionar del menú desagrupar o en la ficha Opciones dar clic en Desagrupar que se encuentra en el grupo de opciones Agrupar.

20


Se pueden agrupar los elementos de los campos para generar nuevos subconjuntos de valores de resumen adicionales. Los niveles de agrupamiento varían de acuerdo con el tipo de datos que se utiliza:

Mostrar u ocultar los detalles de un informe Cuando existen diferentes categorías en los campos de fila o en los campos de columna, es posible expandir o contraer hasta cualquier nivel de detalle de los datos e incluso todos los niveles en una sola operación. Si solo se tiene un campo, la opción Expandir, permite activar nuevos campos en el área correspondiente.

21


Para expandir o contraer un campo se puede ubicar en la Ficha Opciones de Tablas Dinámicas, en el Grupo Campo Activo y seleccionar la opción Expandir todo el Campo o Contraer todo el campo según corresponda. También es viable si se da doble clic sobre la categoría del elemento situado en el área de fila o columna o utilizando el clic derecho del mouse sobre el elemento y seleccionar la opción Expandir/Contraer con las siguientes alternativas: Para ver los detalles del elemento actual, hacer clic en Expandir. Para ocultar los detalles del elemento actual, hacer clic en Contraer. Para ver los detalles de todos los elementos de un campo, hacer clic en Expandir todo el campo. Para ocultar los detalles de todos los elementos de un campo, hacer clic en Contraer todo el campo.

22


Formatos de tabla dinámica Si desea aplicar un formato a la tabla dinámica, se debe ubicar en una celda dentro de la tabla dinámica, dar clic en la ficha Inicio, en el grupo Estilos seleccionar la opción Dar formato como tabla y seleccionar uno de los estilos.

Para dar un formato al área de valores siga uno de los siguientes pasos: 1. Utilizar el cuadro Configuración de Campo en el campo Valores dar clic en el botón y seleccionar Configuración de Campo, dar clic en el botón Formato de número, y seleccionar el formato de número que se va a aplicar a las celdas.

23


2. También es posible seleccionar las celdas a las que se les va a dar el formato, en la ficha Inicio en el grupo Estilos, seleccionar la opción Estilos de Celdas.

Ordenar campos en tablas dinámicas La opción ordenar, permite organizar la información de acuerdo al tipo de datos del campo, ubicado en cualquier área de la tabla dinámica. Así mismo la opción Ordenar activa un cuadro de diálogo que permite organizar los elementos de un nivel en una forma que sean fáciles de entender.

24


En la ficha Opciones, en el grupo Ordenar, realizar uno de los siguientes procedimientos: Se debe ubicar sobre el área a organizar, luego dar clic en los íconos Orden ascendente u Orden descendente para ordenar un campo de la tabla dinámica. De acuerdo al tipo de datos se organizan los datos en la tabla. Actualizar Esta opción permite actualizar el contenido de la tabla dinámica para que refleje los cambios en los datos de origen correspondiente. Si la tabla dinámica está basada en datos externos, al actualizarla se ejecutará la consulta para recuperar los datos nuevos o los que se hayan cambiado.

Con el teclado se pueden presionar las teclas + para actualizar los datos. Cambiar origen de datos Si se aumentan registros o eliminan registros en la base de datos que origina la tabla dinámica, se debe volver a seleccionar el origen de datos.

Para modificar el rango de origen de los datos se debe: - En la ficha Opciones, en el grupo Datos, hacer clic en Cambiar origen de datos y luego en Cambiar origen de datos. Aparecerá el cuadro de diálogo Cambiar origen de datos de tabla dinámica.

25


Para usar una tabla o rango de celdas diferente de Microsoft Office Excel, hacer clic en Seleccione una tabla o rango y luego especificar la primera celda en el cuadro de texto Tabla o rango. - Para usar una conexión diferente, hacer clic en Utilice una fuente de datos externa y luego en Elegir conexión. - Se mostrará el cuadro de diálogo Conexiones existentes. - En la lista desplegable Mostrar, situada en la parte superior del cuadro de diálogo, seleccionar la categoría de conexiones para la que desea elegir una conexión o seleccionar Todas las conexiones existentes (opción predeterminada). - Seleccionar una conexión del cuadro de lista Seleccione una conexión y, a continuación, hacer clic en Abrir.

26


Acciones en la tabla dinámica Si se requiere quitar todos los campos ubicados en el diseño de la tabla dinámica, se debe dar clic en la ficha Opciones, en el grupo Acciones, en la opción Borrar Todo. Si en el campo de página hay filtros activos la opción Borrar Filtros del Grupo Acciones en la ficha Opciones, quita los filtros mostrando nuevamente todos los registros en el informe de tabla dinámica.

Para seleccionar información de una tabla dinámica se debe: 

Situar el puntero del mouse en la esquina superior del campo hasta que cambie a una flecha descendente y, a continuación, hacer clic.



En la ficha Opciones, en el grupo Acciones, hacer clic en Seleccionar y, a continuación, hacer clic en lo que se desee seleccionar:  Etiquetas y datos  Datos  Etiquetas

para seleccionar ambos.

para seleccionar sólo los valores de los elementos. para seleccionar sólo los rótulos de los elementos.

Si se desea cambiar la posición de la tabla dinámica se debe activar la Ficha Opciones en el Grupo Acciones y opción Mover Tabla dinámica, en el cuadro de diálogo se puede cambiar la celda de inicio de la tabla dinámica si se quiere ubicar en la misma hoja, o seleccionar la opción Hoja nueva, para insertar una nueva hoja con la tabla dinámica. Elementos y campos calculados en tablas dinámicas Elemento Calculado: Es una categoría adicional de un campo de una tabla dinámica, situado en el área de filas o columnas que utiliza una fórmula creada por el usuario. 27


Campos Calculados: Es un campo ubicado en el área de datos en la tabla dinámica que utiliza una fórmula creada por él usuario. Los campos calculados pueden ejecutar cálculos utilizando el contenido de otros campos de la tabla dinámica.

Para crear un elemento calculado a un campo 1. Si los elementos del campo están agrupados, en la ficha Opciones, en el grupo Agrupar, hacer clic en Desagrupar. 2. Hacer clic en el campo donde se desee agregar el elemento calculado. 3. En la ficha Opciones, en el grupo Herramientas, hacer clic en Fórmulas y, seleccionar la opción Elemento calculado. 4. En el cuadro Nombre, escribir un nombre para el elemento calculado. 5. En el cuadro Fórmula, escribir la fórmula para el elemento, en la cual se pueden utilizar operadores y expresiones como en la hoja de cálculo iniciando con el =. Pueden utilizarse constantes y hacer referencia a los datos de la tabla dinámica (elementos), pero no pueden utilizarse referencias de celda o nombres, posteriormente hacer clic en Agregar.

28


Para crear un campo calculado a un campo 1. Hacer clic sobre un campo en el área de datos del informe de tabla dinámica. 2. En la ficha Opciones, en el grupo Herramientas, hacer clic en Fórmulas y, a continuación, hacer clic en Campo calculado. 3. En el cuadro Nombre, escribir un nombre para el campo. 4. En el cuadro Fórmula, escribir la fórmula para el campo, en las cuales se pueden utilizar operadores y expresiones como en la hoja de cálculo iniciando con el =. Pueden utilizarse constantes y hacer referencia a los datos de la tabla dinámica (elementos), pero no pueden utilizarse referencias de celda ó nombres. 5. Hacer clic en Agregar.

29


Para mostrar una lista de todas las fórmulas utilizadas en el informe de tabla dinámica actual. En la ficha Opciones, en el grupo Herramientas, hacer clic en Cálculos seleccionar Campos, elementos y conjuntos a continuación hacer clic en Crear lista de fórmulas.

En la ficha Opciones de Tabla dinámica el último grupo Mostrar permite activar o desactivar la lista de campos, los botones para expandir o contraer elementos y los encabezados de los campos.

30


Gráficos Dinámicos Los gráficos dinámicos se pueden basar sobre tablas dinámicas para observar los informes de una manera diferente, más clara y concisa. 1. Ubicarse en la tabla dinámica. 2. En la ficha Opciones de la tabla dinámica, en el grupo Herramientas, seleccionar Gráfico dinámico.

3. Seleccionar el gráfico y dar clic en Aceptar.

31


4. Se crea el gráfico con los datos que se encuentran en la tabla dinámica, al actualizar o hacer cambio a la tabla dinámica, automáticamente se actualiza el gráfico.

32


Consolidación

Esta opción combina valores de varios rangos de celda en un nuevo rango, puede resumir y registrar resultados de hojas de cálculo; la opción de consolidar se encuentra en el Herramientas de datos. Se pueden consolidar datos de las siguientes formas: Utilizando por posición, por categorías y fórmula. En este capítulo se presentará el método por posición, es decir, cuando los datos de todas las áreas de origen se organizan en idéntico orden y localización; por ejemplo, para combinar los datos de una serie de hojas de cálculo creadas desde la misma plantilla. Si se tienen rangos numéricos en una o más hojas de cálculo, se puede obtener un total de éstos.

33


Por ejemplo, si se tienen las ventas de tres años consecutivos clasificadas por trimestres según artículos, es posible realizar operaciones acumuladas como suma, promedio etc.; y así poder analizar sobre un total acumulado o promediar utilizando consolidación. Copiar el siguiente escenario en Excel a partir de la celda A1

Para consolidar por años se debe realizar lo siguiente: 1. Insertar una nueva hoja de cálculo, asignarle como nombre a la hoja de cálculo consolidado. 2. Hacer clic sobre la ficha de Datos, en el grupo Herramientas de datos, Consolidar.

34


3. En el campo Función, seleccionar la función que se desea para consolidar. 4. En el cuadro de texto de referencia existe un cuadro que permite seleccionar celdas o rangos seleccionar de la hoja año 2005 el rango de celdas de la tabla y hacer clic sobre el botón Agregar. 5. En el cuadro de texto de referencia seleccionar de la hoja año 2006 el rango de celdas de la tabla y hacer clic sobre el botón Agregar. 6. En el cuadro de texto de referencia seleccionar de la hoja año 2007 el rango de celdas de la tabla y hacer clic sobre el botón Agregar. 7. Seleccionar los cuadros de chequeo fila superior y columna izquierda. (Estos se deben seleccionar si las referencias fueron agregadas incluyendo los títulos de filas y columnas, para el ejemplo si se incluyeron los artículos (columna izquierda) y los meses (fila superior). 8. Seleccionar el cuadro de chequeo crear vínculos con los datos de origen.

35


Se pueden utilizar las funciones: suma, promedio, max, min, contar números, desvestp, desvest, var varp.

36


Permite mostrar el detalle de un grupo de celdas. Permite ocultar el detalle de un grupo de celdas. Si la hoja de cálculo se encuentra en otro libro, hacer clic en Examinar para buscar el archivo y, a continuación, hacer clic en Aceptar para cerrar el cuadro de diálogo Examinar.

37


MÓDULO 2 Consultas Externas Datos externos Normalmente, la BD tiene una gran cantidad de información que no siempre es necesaria tenerla toda al tiempo, sino que con algunos de los campos de la BD es suficiente, o con solo algunos de los registros, se puede obtener los resultados que se requieren. Sin embargo, cuando se requiere un mejor manejo de la información, Excel nos ofrece una posibilidad de generar nuevas BD y nuevas consultas utilizando MS Query, una herramienta de Office. Al conectarse a datos externos se puede hacer un análisis de los datos sin tener que tener la información en Excel. Es necesario tener habilitada la conexión de datos en el centro de Confianza, o ubicar el archivo en un centro de confianza. Ubicación de Confianza Una ubicación de confianza no es más que un espacio en el disco duro, una carpeta o un recurso de red, si un archivo está en una ubicación o centro de confianza este puede abrirse sin que Excel lo compruebe. Crear Ubicaciones de confianza Clic en el Menú Archivo Clic en Opciones. Clic en la categoría Centros de confianza. Clic en configuración de centros de confianza y luego en Ubicaciones de confianza.

39


Clic en Agregar nueva ubicación.

40


En el cuadro Ruta de acceso, buscar la carpeta que se usará como centro de confianza con el botón examinar.

Si se quiere que las subcarpetas también sean centros de confianza se 41


debe activar la casilla de verificación Las subcarpetas de esta ubicación también son de confianza. Clic en Aceptar.

Tablas Dinámicas de Consolidación Múltiple Para el manejo de las tablas dinámicas de consolidación múltiple es necesario primer activar el icono del Asistente para tablas y gráficos dinámicos, en el menú Archivo  opciones y clic en Barra de herramientas de acceso rápido. Dentro de las opciones de Comandos disponibles en: se debe seleccionar Todos los comandos y dentro de la lista desplegable buscar el icono de Asistente para tablas y gráficos dinámicos, Agregar y Aceptar.

Las tablas dinámicas consolidadas nos permiten generar un tipo de informe dinámico con rangos de celdas que no se encuentran dentro de 42


una base de datos. En el asistente se debe marcar la opción Rangos de consolidación múltiples y Tabla Dinámica  Siguiente.

En el paso 2 del asistente se especifica el número de campos de página que se necesite, Crear un solo campo de página o Campos de página personalizados y Siguiente.

En el paso 3 se agregan los rangos de hojas de cálculo que se desea

43


consolidar, los rangos pueden estar en la misma hoja, en diferentes hojas o en diferentes libros haciendo clic en Examinar, una vez se han seleccionados los rangos se hace clic en Agregar o Eliminar según el caso y Siguiente.

En este último paso se da la ubicación de la tabla dinámica, en una hoja de cálculo nueva o en una existente y Finalizar.

44


En la opción de página muestra las opciones de Elemento1 que corresponde a la primera referencia de rango y Elemento2 a la segunda referencia que en este caso se encuentra en otra hoja y es posible visualizar todo el consolidado o elemento por elemento.

Consultas Access Esta herramienta permite recuperar en Excel la información de una tabla o consulta de un archivo de Access permitiendo el análisis de la información periódicamente sin tener que importarlos nuevamente de Access. Pasos

45


para realizar conexiones: Ubicarse en la celda donde se quieren dejar los datos que se traerán de Access. Clic en la opción Desde Access, que se encuentra en la ficha datos grupo Obtener datos externos.

Seleccionar el archivo de Access del que se desean importar los datos.

Seleccionar la tabla o consulta que se desea importar y clic en Aceptar. Se activa el cuadro de diálogo Importar datos, presentando tres opciones:

46


Tabla: Trae la información como una tabla. Informe de tabla dinámica: Si se quiere resumir la información. Informe de gráfico y tabla dinámicos: Si se quiere crear la tabla dinámica y un gráfico dinámico para resumir información. Definir ¿Dónde desea situar los datos?, seleccionar Hoja de cálculo existente y seleccionar la celda a partir de la cual se quiere la información o, seleccionar la opción Nueva hoja de cálculo. Clic en Aceptar. Conexión a Datos externos Clic a conexiones existentes que se encuentra en la ficha Datos grupo obtener datos externos.

Se activa el cuadro de diálogo conexiones existentes.

47


En la lista desplegable Mostrar presenta 4 opciones: Todas las conexiones, Conexiones en este libro (si se quiere el listado de las conexiones utilizadas recientemente), Archivos de conexión en la Red (para mostrar solamente las conexiones disponibles de un archivo de conexión que solo se accede desde la red), Archivos de conexión en este equipo. Seleccionar la conexión y dar clic en Abrir. Se activa el cuadro de diálogo Importar datos presentando 3 opciones:

48


Tabla: Trae la información como una tabla. Informe de tabla dinámica: Si se quiere resumir la información. Informe de gráfico y tabla dinámicos: Si se quiere crear la tabla dinámica y un gráfico dinámico para resumir información. Definir en dónde desea situar los datos, Seleccionar Hoja de cálculo existente y seleccionar la celda a partir de la cual se quiere la información o, seleccionar la opción Nueva hoja de cálculo. Clic en Aceptar.

Consultas Query Microsoft Query permite traer información de bases de datos relacionales permitiendo tener actualizada la información en Excel si se ha actualizado en la base de datos de origen. Microsoft Query permite obtener datos de bases de datos como Microsoft Access, Microsoft SQL, FoxPro, Oracle, Archivos de texto, o de Microsoft Excel. Crear Conexiones a Bases de datos Seleccionar la opción de otras fuentes que se encuentra en el grupo Obtener Datos externos de la ficha Datos. Seleccionar la opción Desde Microsoft Query.

49


Presenta una caja de diálogo Elegir Origen de datos.

Seleccionar la opción Nuevo origen de datos. En el cuadro de diálogo Crear Nuevo Origen de datos, escribir un nombre para identificar el origen de datos y luego seleccionar un controlador de la lista desplegable y luego dar clic en Conectar.

50


Especificar qué se hará con la base de datos, seleccionar para abrir, Crear, Reparar o compactar la base de datos. Al dar clic en Seleccionar se abre la caja de diálogo seleccionar base de datos, seleccionar el archivo y clic en Aceptar.

51


Si se quiere que una tabla o consulta aparezca automáticamente seleccionarla en el paso 4 del cuadro de diálogo nuevo Origen de datos. Dar clic en Aceptar. El nombre del origen aparecerá en el cuadro de diálogo Elegir Origen. Crear consultas con MS Query Ms Query permite realizar consultas de una o más tablas permitiendo seleccionar uno o más campos de cada una de las tablas seleccionadas y además permitiendo definir criterios para extraer un conjunto de registros teniendo en cuenta la condición. Cuando se consulta información de dos tablas o más, las tablas deben tener un campo coincidente para poderlas relacionar. Un campo coincidente es un campo idéntico en las tablas. El asistente es capaz de reconocer el campo de relación es decir un campo con el mismo nombre en otra tabla. Seleccionar la opción de otras fuentes que se encuentra en el grupo Obtener Datos externos de la ficha Datos. Seleccionar la opción Desde Microsoft Query. Presenta una caja de diálogo Elegir Origen de datos (para usar el asistente activar la casilla

52


de verificación Usar el Asistente para crear consultas o modificar consultas. Seleccionar el origen de datos y Aceptar. Presenta el listado de tablas que pertenecen a la base de datos seleccionada. Seleccionar las tablas y/o campos que se quieran para las consultas (recordar que las tablas deben tener una relación) y llevarlos al campo Columnas en la consulta, por medio del botón y clic al paso siguiente.

Si se desea filtrar los registros seleccionar el campo y definir el criterio en la sección derecha de la pantalla. Si no se quiere filtrar clic en siguiente.

53


Si se quiere ordenar seleccionar el campo y clic en Siguiente.

Definir qué se desea hacer Devolver datos a Microsoft Office Excel, o Ver datos o modificar consulta en Microsoft Query y clic en Finalizar.

54


Consultas desde la Web Las consultas Web son utilizadas para traer información de una página Web organizada en tablas a un documento de Excel y ser actualizados según los cambios que surjan en la página Web o bien mantener la información sin cambios. Para obtener datos de una página Web se puede realizar de dos formas: Copiando y pegando de una página web a una hoja de Excel. Con las opciones de pegado se puede hacer que la información se actualice o que permanezca igual. Clic con el botón derecho del Mouse y seleccionar la opción Exportar a Microsoft Excel. Se abre un nuevo libro en Excel con la información de la tabla. Crear una consulta Web Dar clic al comando Desde la Web que se encuentra en el grupo Obtener datos externos de la ficha Datos. En el cuadro de diálogo Nueva consulta Web escribir la dirección de la que se desean recuperar los datos.

55


Clic en Ir. Modificar una consulta Web Hacer clic al comando conexiones que se encuentra en el grupo Conexiones de la ficha Datos. En el cuadro de diálogo Conexiones del libro seleccionar la consulta Web. Dar clic al botón propiedades, se abre un cuadro de diálogo Propiedades de conexión. En la ficha Definición dar clic en Modificar Consulta. Clic en Editar consulta. En el cuadro de diálogo Modificar consulta, seleccionar una nueva página web o seleccionar las tablas que se quieren importar dando clic al recuadro amarillo que aparece en el extremo superior izquierdo de cada tabla de la página y luego dar clic en el Botón Importar. Clic en Aceptar y luego en Cerrar.

56


Conexiones desde Excel a Datos de Word Esta herramienta permite recuperar en Excel la información de una tabla de Word insertándola o incrustándola, si una tabla es incrustada los datos de la tabla destino se cambiarán si se cambian en el origen. Se pueden traer también tablas copiando los datos de Word y pegándolos a Excel. Pasos para realizar conexiones a Microsoft Word: Seleccionar la tabla en Word y seleccionar el comando Copiar para llevar la información al Visor de portapapeles. En Excel, Ubicar el cursor en la celda, a partir del cual se quiere insertar la tabla. Dar clic a Pegado especial que se encuentra en la ficha Inicio del grupo portapapeles. Si se quiere vincular el archivo, dar clic en pegar vínculos para que se actualicen los datos del destino (tabla en Excel) cada vez que se hagan cambios en el origen (tabla de Word). Seleccionar Texto Unicode o Texto para pegarlo como una tabla. Clic en Aceptar. Quitar conexión de datos Al quitarla conexión los datos vinculados se convertirán en valores. Para quitar la conexión se deben tener en cuenta los siguientes pasos: Dar clic en el comando Editar vínculo que se encuentra en la ficha Datos del grupo conexiones. En la caja de diálogo Modificar vínculos dar clic en Romper vínculo. Se activa una ventana advirtiendo la ruptura de vínculo y que ellos serán convertidos en valores. Dar clic en Romper Vínculos.

57


Importar Archivos de texto Para importar archivos de texto, Microsoft Excel utiliza un asistente quien se encarga de analizar el documento para traer la información correctamente. Para esto se deben tener en cuenta los siguientes pasos. Clic al comando Desde texto que se encuentra en la ficha Datos del grupo. Obtener datos externos Seleccionar el archivo que se importará y dar clic a importar. Se activa el asistente para importación de archivos de texto. El paso 1 permite definir el tipo de archivo. Si el archivo contiene información separada por coma, punto y coma, tabulaciones, etc. O si los campos están alineados en columnas. Dar clic en Siguiente. El paso 2 permite seleccionar el caracter que separa las columnas del archivo de texto. Activar la casilla de verificación Considerar separadores consecutivos como uno solo, si los datos contienen varios caracteres entre campos como delimitador. Dar clic al paso siguiente. El paso 3 permite definir el formato o tipo de datos de cada una de las columnas y además permite no importar una columna si se activa la casilla de verificación No importar columna (saltar). Clic al botón Finalizar. Definir en dónde quedarán los datos si en la hoja de cálculo actual o en una nueva hoja de cálculo. Clic en Aceptar. Conexiones de Microsoft Excel con Microsoft Outlook Si el libro que está trabajando debe ser remitido a un usuario a través del correo, Excel nos permite enviarlo directamente a través de la opción Enviar a, que se encuentra en el botón de Office. Al seleccionar la opción se abre la ficha Mensaje con el archivo adjunto, listo para seleccionar el destinatario de correo y enviar el mensaje.

58


Guardar libros como WEB Es posible publicar cualquier hoja o libro de Excel en la página WEB, poniendo los datos en la WEB para que cualquier usuario los pueda ver: Llamar la opción guardar como, del botón de office. En el tipo de archivo seleccionar Página Web (*htm;*html). Se activan dos botones, Cambiar título, para asignar un título a la página y Publicar, para publicar el archivo. Definir si se publicará todo el libro o solo una hoja del libro activando los botones de opción. Dar clic en Cambiar título. Se abre la ventana establecer título de la página. En el cuadro de texto escribir el título y dar clic a Aceptar. Dar clic en publicar. Seleccionar el elemento a publicar y clic en Publicar. Si se ha activado la casilla de verificación Abrir la página en el explorador Web, se abre el archivo publicado.

PowerPivot Microsoft PowerPivot es un complemento adicional de Excel que se puede manejar a nivel de cliente (PowerPivot para Excel) o de servidor (PowerPivot para SharePoint), este complemento se puede descargar gratis desde la web en http://powerpivot.com

59


Cuando se abre Microsoft Excel por primera vez después de la instalación, abre una ficha nueva llamada PowerPivot, dentro de esta ventana se puede crear un modelo relacional. PowerPivot puede importar datos de varios tipos: Access, oracle, Sybase, Informix, DB2, Teradata y otros. Este proceso para importar datos de cada uno de estos orígenes es muy parecido. Para crear una conexión a una base de datos de SQL Server: 1. Desde la ventana de Excel en la ficha de PowerPivot clic en Ventana de PowerPivot. 2. En la ventana de PowerPivot, en la ficha Inicio, clic en Desde base de datos y seleccione De SQL Server. Excel abrirá un Asistente importar la tablas. 60


3. En el campo Nombre descriptivo de la conexión, escriba ContosoDB desde SQL Server. 4. A continuación digite el nombre del servidor donde se encuentra instalado el SQL server y seleccionar Usar autenticación de Windows. 5. En el campo Base de datos, haga clic en la flecha abajo para recuperar una lista de bases de datos en el servidor. Seleccione ContosoRetailDW, pruebe la conexión y, a continuación, haga clic en Siguiente. 6. Para seleccionar una lista de tablas y vistas debe hacer clic en Siguiente para mostrar una lista de todas las tablas de origen dentro de la base de datos. 7. Active la casilla para las siguientes tablas: DimChannel, DimDate, DimEntity, DimProduct, DimProductCategory, DimProductSubcategory y FactSales. A continuación daremos un ejemplo de cómo utilizar PowerPivot con una base de datos de Microsoft Access. Para utilizar Powerpivot con una base de datos de Access hacer clic en el icono Ventana de PowerPivot.

En esta ventana es necesario definir qué base de datos se desea usar, De SQL server, De Access, De Analysis Services o PowerPivot. En esta oportunidad trabajaremos De Access, haciendo un clic en esta opción aparece la siguiente ventana.

61


Es un asistente para conectar a una base de datos de Microsoft Access, si conocemos el nombre y la ruta del archivo lo digitamos de lo contrario hacemos un clic en Examinar…

62


En esta ventana selecciona la ruta y el archivo y Abrir.

63


En esta ventana se especifica la ruta y el archivo y Abrir. El asistente muestra un aviso de espera mientras se conecta al origen de datos; en este caso a la base de datos Access Nomina XYZ.

64


El siguiente paso es especificar cómo se importaran los datos desde una tabla o a través de consultas con SQl, en este caso se escoge la opción Listas de tablas y vistas para que importe las tablas y consultas de la base de datos.

65


En el siguiente paso del asistente se activan las tablas y vistas que se desean importar para la elaboración del PowerPivot, además se puede tener una vista preliminar y se puede filtrar en caso que solo se desee información específica de uno o varios campos.

66


Esta es la vista preliminar de la tabla seleccionada en donde se puede filtrar los campos con información específica.

67


Una vez se definen las tablas y la información que se desea importar hacemos clic en Aceptar para que inicie el proceso de importar el cual puede tardar algunos minutos en realizarlo presentando un informe de análisis indicando el número de registros o filas que se han importado. Durante el proceso de importar es posible detener este proceso haciendo clic en Detener importación.

68


Al finalizar este proceso aparece una ventana con la información que importó de la base de datos de Access y en cada hoja presenta las tablas que conforman la base de datos.

69


Una vez se tiene la información necesaria se puede realizar el PivotTable haciendo clic en el icono

y PivotTable nuevamente.

70


Al hacer clic en la opción de Pivottable tenemos que definir la ubicación de la tabla si es en una hoja de cálculo nueva o en una existente, de ser en una existente, se define la ubicación de la hoja y de la celda.

La apariencia es similar a la de una Tabla dinámica, pero esta presenta las tablas importadas de Access brindando la posibilidad de expandir cada tabla y escoger un campo de cada tabla ubicándolo dentro del diseño en la posición deseada.

71


Segmentación de datos vertical y horizontal Las segmentaciones generan controles que permiten filtrar y personalizar los datos de una tabla o grafico dinámico. Esta segmentación permite cambiar de criterios y ver esos cambios al instante proporcionando una vista interactiva de los datos. La segmentación se agrega como un control adicional a la tabla o al gráfico y permite seleccionar rápidamente un criterio y muestra los cambios al instante; las segmentaciones no agregan filas adicionales solo proporciona una vista interactiva de los datos. Para agregar campos a las segmentaciones se debe arrastrar el campo hasta el área de segmentación de datos verticales o segmentación de datos horizontales según la posición y comodidad en la lectura de la tabla. Se pueden agregar varios campos a la segmentación de datos presionando la tecla mientras se seleccionan cada uno, para borrar la segmentación y mostrar todos los datos se debe hacer clic en el botón de borrar en la esquina superior derecha de la segmentación. En este ejemplo los campos de segmentación son EPS y Estado Civil. 72


Filtro de Informe: Muestra una lista desplegable del control del filtro, aparece encima de la tabla dinámica o grafico dinámico, permite seleccionar uno o varios elementos de la base a través de ese campo. Etiquetas de columna: Muestra los campos en columnas. Etiquetas de Fila: Muestra los campos como filas en el informe. Valores: En esta opción se debe ubicar el o los campos sobre quien recaerá la función. 73


Realizar un gráfico dinámico Desde la ventana de PowerPivot hacer clic en el icono de Pivottable, seguido de un clic en dos gráficos (horizontales), como es el caso mostrado en la siguiente ventana.

Esta es la presentación de un gráfico dinámico utilizando el PowerPivot, se puede utilizar el tipo de gráfico que más se ajuste a sus necesidades.

74


75


MÓDULO 3 Soluciones dinámicas Soluciones dinámicas pretende u herramientas basadas en análisis de sensibilidad para optimizar la toma de decisiones, el análisis de datos, la comparación de información con el objetivo de encontrar mejores resultados.

Buscar Objetivo Buscar objetivo permite que un valor de una celda cambie a un valor exacto modificando otro valor. Ejemplo: En este modelo se realizó una función financiera (Pago) para hallar el valor de la cuota de un préstamo. La intención es poder definir el valor de la cuota en 400.000 (es un valor exacto), pero esto es posible alterando otro valor que para el ejemplo puede ser el Monto, Plazo o Interés.

77


Cambiando el monto el resultado sería:

Utilizar Buscar Objetivo Crear una tabla con la valores que al cambiar afecten el resultado de las formulas o simplemente un tabla donde cambien los valores. Antes de utilizar buscar objetivo es importante saber que la celda objetivo debe estar formulada con otras para que de esta manera Excel pueda realizarlo

1. Seleccionar la celda con el valor a cambiar. Para el ejemplo la celda donde esta 569.757.48 que es la celda que tendrá el valor exacto de 400.000 2. Hacer clic en la ficha datos, grupo herramientas de datos opción Análisis y si y buscar objetivo. 3. En las opciones de Buscar objetivo se encuentra: a. Definir celda: Es la celda con el valor a cambiar.

78


b. Con el valor: Es el valor exacto para ajustarse. c. Para cambiar la celda: Es una celda que ajustara su valor para que la celda objetivo se cumpla. Para el ejemplo es el monto 15.000.000

Al pulsar Aceptar Excel genera un reporte indicando en cuanto quedo el valor del objetivo, si el usuario pulsa aceptar los cambio se realizan directamente para evitar que los cambio se realicen, pulsar Cancelar.

79


Escenarios Los escenarios en Excel permiten guardar un conjunto de datos y volver a cargarlo de forma automática, se utiliza para comparar valores utilizando una misma tabla. El siguiente ilustra una cotización con tres proveedores utilizando la misma tabla para determinar la mejor opción. Excel guarda los valores por unidad de cada producto y el nombre del proveedor, la tabla contiene las formulas y totaliza a medida que el valor cambia.

Crear un escenario 1. Crear una tabla con la valores que al cambiar afecten el resultado de las formulas ó simplemente un tabla donde cambien los valores.

80


Al cambiar las celdas con valores diferentes, se modifica el resultado. 2. Hacer clic en la ficha datos, grupo herramientas, análisis y si y a continuación Administrador de escenarios. 3. Hacer clic en botón Agregar.

Nombre del escenario: Definir el nombre que identifique los datos actuales. Celdas Cambiantes: Se deben seleccionar las celdas que contienen los

81


valores que cambian para cada caso. 4. Pulsar Aceptar y Excel muestra una ventana con la referencia de las celdas cambiantes y sus valores actuales.

En este ejemplo no muestra referencias de celdas sino el nombre de cada producto, esto se debe a que cada celda cambiante se nombró como el producto de la izquierda.

5. Una vez agregado el escenario pulsar cerrar. 6. modificar las celdas cambiantes con valores nuevos. 7. Realizar todo el procedimiento para agregar otros escenarios

82


Mostrar escenarios Una vez se han creado varios escenarios, es posible cargar los valores de cada uno utilizando la misma tabla de Excel.

1. Hacer clic en la ficha datos, grupo herramientas de datos y a continuación Análisis y Si, elegir la opción administrador de escenarios. 2. Seleccionar el escenario que desea pre visualizar y pulsar el botón mostrar ó 2 clic sobre el nombre del escenario. 3. Excel muestra directamente en la tabla los datos del escenario. Modificar o eliminar escenarios Es posible modificar los valores de un escenario o eliminar escenarios por completo. 1. Ingresar al administrador de escenarios. 2. Seleccionar el escenario y pulsar el botón eliminar, el escenario desaparece de la lista y no existe forma de recuperarlo. 3. Para modificar los datos se debe seleccionar el escenario 4. Pulsar el botón modificar 5. Seleccionar las nuevas celdas o pulsar aceptar para modificar los valores de forma manual. Resumen Escenarios Excel puede generar un resumen automático en el que compara todas las tablas una lectura fácil y rápida. 1. Seleccionar la celda con el resultado que se va a comparar o analizar. 83


2. Ingresar al administrador de escenarios. 3. Pulsar el botón Resumen.

4. Elegir el tipo de informe, el tipo resumen muestra una tabla en la que compara todos los escenarios incluyendo detalles mientras que el informe de tabla dinámica compara los valores únicamente. 5. Elegir Resumen y pulsar Aceptar.

84


Solver Es una herramienta que permite en una celda con fórmula buscar un valor óptimo según un grupo de celdas relacionadas con la fórmula, de esta forma se halla la mejor solución a un problema mediante restricciones. Solver entonces ajusta las celdas cambiantes para dar el resultado especificado en el valor objetivo, teniendo en cuenta todas sus restricciones. Antes de utilizar solver es necesario para algunos computadores instalar el complemento Habilitar Solver Solver es un programa que está disponible cuando instala Microsoft Office o Excel. Sin embargo, para ser utilizado en Excel primero se debe cargar. Para ello, seguir los siguientes pasos: 1. Hacer clic sobre la ficha Archivo y luego clic en el botón opciones.

2. Hacer clic en Complementos, en el cuadro Administrar, seleccionar Complementos de Excel.

85


3. Hacer clic en Ir.

86


4. En el cuadro Complementos disponibles, activar la casilla de verificación Complemento Solver, a continuación, hacer clic en Aceptar. 5. La opción Solver aparecen en la ficha datos, grupo Análisis y solver.

Utilizar Solver Para utilizar Solver es necesario tener un problema planteado y conectado con fórmulas para que Excel lo pueda resolver:

87


Ejemplo:

Este es un consolidado de ventas de un almacén que mantiene el mismo promedio de ventas para los últimos tres meses, la empresa necesita aumentar sus utilidades a 100.000.000 para lo cual pide un plan de acción. Los planteamientos para resolver el problema pueden ser varios:    

Disminuir Costos Mercancía. Disminuir Gastos Administrativos. Aumentar las unidades vendidas o el valor unitario. Todos en diferentes proporciones.

Para resolver este problema y hacer que la utilidad sea de 100.000.000 utilizamos solver de la siguiente manera:

88


1. Es necesario tener un esquema de fórmulas en las que se relacionan todos los valores que pueden cambiar y un valor objetivo.

2. Ingresar a la herramienta Solver (Ficha datos – Grupo Análisis – Solver)

89


Establecer Objetivo: Especificar una celda con fórmula a la que se desee calcular el mínimo valor, el máximo valor o un valor específico. Valor de: Activar el botón de opción de acuerdo al objetivo, si se desea calcular un valor específico, digitarlo en el cuadro de texto. Cambiando las celdas de variables: Son los valores que Excel puede cambiar para lograr el objetivo final.

90


Estas celdas deben estar relacionadas directa o indirectamente en la fórmula de la celda objetivo, y deben ser celdas sin fórmulas. Sujeto a las restricciones: Muestra el listado de las restricciones agregadas. Para agregarlas, dar clic en el botón Agregar y especificar la restricción.

Agregar: En Referencia de la celda, definir la celda a la que se va a aplicar la restricción, a continuación definir el operador de comparación, <=, =, ó >= y especificar la restricción en el cuadro, ya sea una celda o un valor. Para cambiar una condición, se debe seleccionar y dar clic en el botón cambiar, y de la misma manera, seleccionar una restricción y dar clic en el botón eliminar, si se desea quitar una restricción. Resolver:Pulsar este botón una vez esten definidos los parametros del problema.

91


Conservar Solución de Solver: Al dar clic en Aceptar con ese botón de opción activo, Solver modificará los valores en la hoja de cálculo sin poder deshacerse. Restaurar valores originales: Solver mantiene los valores iniciales en la hoja de cálculo. Guardar escenario: Los nuevos valores sugeridos por solver serán guardados como escenario, de manera que puedan ser analizados más adelante. Informes: Existen tres tipos de informe, informe de respuesta, sensibilidad y límite. Para el informe de respuesta, solver crea una nueva hoja mostrando los valores originales y los valores con la solución tanto para celdas cambiantes como para la celda objetivo. En el informe de Sensibilidad solver muestra los nuevos valores de las celdas cambiantes. Informe de límites muestra al usuario los valores nuevos de la celda 92


cambiante y de la celda objetivo, y además, muestra el valor del límite inferior y límite superior de cada celda cambiante y cuál sería en tal caso, el valor objetivo.

93


MÓDULO 4 Automatización En todo proceso de manejo de información en Excel, es muy común repetir acciones y comandos en forma continua. Igualmente, es fundamental, facilitar que otros usuarios utilicen los modelos desarrollados y las soluciones ya creadas de una manera sencilla y sin necesidad de conocer a fondo el manejo de la hoja de cálculo. Microsoft, tiene una herramienta de programación muy poderosa llamada Visual Basic, la cual viene en todas las herramientas de productividad de esta casa de software y a la cual se tiene acceso a través de las diferentes herramientas de Office. Sin embargo, como el problema de manejo de la información no debe estar limitado a los expertos de sistemas, Excel facilita la creación de pequeños programas que automatizan los modelos desarrollados y que facilitan la interacción de otros usuarios con estos desarrollos. Toda macro tiene unos pasos básicos: Se crea, se graba, se detiene, se ejecuta.

Grabadora de macros Para crear una macro se deben tener en cuenta los siguientes pasos: 1. Plantear el objetivo. 2. Definir el tipo de macro, si es absoluta o relativa. Más adelante se verá un dará un ejemplo, en donde se utilizará este concepto y se verá la importancia en la creación de las macros. 3. Dar clic al comando Macros que se encuentra en la ficha Vista. 4. En la lista desplegable dar clic al comando Grabar Macro…

95


5. Se abre la ventana Grabar macro a. Dar nombre a la macro b. Definir el método Abreviado c. Definir en dónde se guardará y dar clic al botón Aceptar. 6. Realizar una a una las tareas ordenadamente para cumplir el objetivo propuesto y de la manera más eficiente. 7. Y por último se debe Detener grabación. Nombre de la macro Para definir el nombre de una macro se deben tener en cuenta los siguientes puntos: 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. Si utiliza un nombre de macro que también es una referencia de celda, una función, o un objeto puede aparecer un mensaje indicando que el nombre de la macro no es válido. Teclas de método abreviado Se puede utilizar cualquier letra, sea en mayúsculas o en minúsculas apoyado sobre la tecla CTRL. Es importante tener en cuenta no utilizar 96


métodos abreviados que ejecuten tareas de Excel porque serán anulados. Por ejemplo, si el método abreviado que se utiliza para una macro es + , este comando ya no servirá sino para ejecutar la nueva macro y ya no servirá como comando para copiar. Guardar macro en: Si desea que la macro esté disponible siempre que utilice Excel, seleccionar Libro de macros personal. Cuando se selecciona Libro de macros personal, Excel crea un libro oculto de macros personal (Personal.xlsb), si no existe todavía, guarda la macro en este libro. En Microsoft Windows XP, este libro se guarda en una carpeta diferente a la de Windows Vista para que se pueda cargar automáticamente cada vez que se inicia Excel. En Microsoft Windows Vista, este libro se guarda en la carpeta C:\Usuarios\nombre de usuario\Datos de programa\Microsoft\Excel\XLStart. Si se desea que se ejecute automáticamente una macro del libro de macros personal en otro libro, también debe guardar ese libro en la carpeta XLStart, de forma que ambos libros se abran cuando se inicie Excel. En el momento de dar aceptar, aparece un botón parar en la barra de estado que indica que las acciones que se ejecuten a partir de ese momento, quedarán grabadas dentro de la macro que se indicó anteriormente. Una vez se haya terminado las acciones a realizar, se debe oprimir el botón de parar. Pasos para crear una macro 1. Ubicarse en una celda dentro de una base de datos. 2. En la ficha vista, grupo macros, hacer clic en la flecha desplegable de macros y seleccionar grabar una macro. 3. En el cuadro de texto de nombre de la macro: escribir Operaciones 4. Verificar que el cuadro de lista desplegable guardar macro en esté seleccionado este libro. 5. En Método abreviado asignar Mayús + S 6. Oprimir la tecla o clic en el botón Aceptar 7. En la celda E12 escriba =Suma(H2:h11) 8. Oprimir la tecla 9. En la ficha vista, grupo macros, hacer clic en la flecha desplegable de macros y seleccionar Detener grabación o seleccionar el botón parar macro de la barra de estado.

97


Pasos para ver macros 1. En la ficha vista, grupo macros, hacer clic en la flecha desplegable de macros y seleccionar ver Macros.

98


Ver el código macro 1. En la ficha Vista, grupo macros, hacer clic en la flecha desplegable de macros y hacer clic sobre el botón Ver Macros y a continuación cliv en el botón paso a paso. Sub macro_suma() ' ' macro_suma Macro Range("E12").Select ActiveCell.FormulaR1C1 = "=SUM(total)" Range("H13").Select End Sub Si se desea cerrar el editor de visual Basic, hacer clic en cerrar, si aparece un mensaje de este comando detendrá el depurador habilitar Aceptar (si no aparece este mensaje, simplemente la ventana se ha cerrado).

Pasos para eliminar una macro En la ficha vista, grupo macros, hacer clic en la flecha desplegable de macros y seleccionar ver Macros. Seleccionar la macro que se creó y hacer clic sobre el botón Eliminar.

99


Hacer clic sobre el botón Si. Ejecutar y asignar macros Una de las labores finales dentro de la automatización de procesos en Excel es ejecutar procedimientos (macros) de una manera fácil y amigable. Hasta este punto se han visto dos formas para ejecutar una macro como son: El cuadro de diálogo Macro y la combinación de teclas que se asignan a una macro cuando ésta se graba. El primer método de ejecución implica que el usuario debe conocer el nombre exacto de la macro y las rutinas que esta realiza. En el segundo método el usuario debe tener una lista de combinaciones de teclas para poder ejecutar la macro. Existen otras formas de asignación y ejecución de macros donde el usuario puede interactuar con elementos gráficos, en los cuales lo único que se debe hacer, es dar clic sobre el icono o botón al que se le asignó la macro, dichas formas de ejecución se describen a continuación: A través de elementos u objetos gráficos. Mediante un botón de la Barras de Herramientas de acceso rápido. Ejecutando la macro automáticamente al abrir un libro. Ejecutar mediante Objetos gráficos Las Ilustraciones proporcionan un mecanismo que puede ser utilizado para asignar y ejecutar una macro de manera rápida y sencilla. Cualquier elemento del grupo Ilustraciones (una imagen, una imagen prediseñada, una forma o un SmartArt) se pueden emplear para asignar una macro. 100


Es importante tener en cuenta que cuando una macro es asignada a un objeto gráfico, esta sólo puede ser ejecutada desde la hoja de cálculo en donde se colocó el objeto. Para asignar la macro Bordes creada anteriormente a un objeto gráfico, se deben seguir los siguientes pasos: Dibujar sobre la hoja de cálculo el objeto al cual se asignará la macro. Sobre el objeto, dar clic derecho y seleccionar la opción Asignar Macro.

En el cuadro de diálogo Asignar Macro, seleccionar la macro Bordes.

101


Dar clic en Aceptar. Creando un botón en la Barra de acceso rápido La forma más común para ejecutar una macro es mediante un botón en la barra de herramientas de acceso rápido. Las Barras de Herramientas son elementos dentro de Excel que se pueden ocultar fácilmente sin que estas lleguen a interferir con los datos dentro de una hoja de cálculo. Cuando una macro es asignada a un botón de la Barra de acceso rápido, esta puede ser ejecutada desde cualquier libro de Excel, siempre y cuando se tenga la configuración de seguridad adecuada. Por defecto la Barra quedará configurada para la aplicación de Excel. Para asignar la macro Bordes a un botón de la Barra de Herramientas, se deben seguir los siguientes pasos: Dar clic en el botón Opciones de Excel que se encuentra en el Menú Archivo. Seleccionar de la lista la opción Barra de herramientas de acceso rápido. 102


De la lista Comandos Disponibles que se encuentra en el panel central de la ventana, seleccionar Macros. Seleccionar la macro a agregar. Dar clic en Agregar, para pasar esta opción a la lista del costado derecho de la ventana.

Si se quiere representar la macro con un ícono específico se selecciona la macro del costado derecho de la ventana y dar clic en Modificar.

103


Dar clic en Aceptar. El botón se agrega a la barra de acceso rápido. El botón Ver macros se agrega a la barra de acceso rápido.

Al ejecutar si se está en un archivo diferente al que contiene la macro, la opción Todos los libros abiertos mostrará el listado de todas las macros de los libros abiertos, se puede seleccionar la macro y ejecutarla para el archivo activo.

Seguridad Una de las situaciones de mayor relevancia en Excel es la de poder o no ejecutar una macro. Esto va de la mano con la posibilidad de proteger los archivos de posibles virus que pueden venir de libros con macros, de ahí que Excel establece unos niveles de seguridad que permiten definir si las macros de un libro pueden ejecutarse o no. En ocasiones cuando se abre un libro de Excel con macros, estas aparecen deshabilitadas por el nivel de seguridad aplicado a ese computador mostrando un cuadro de diálogo como el de la siguiente figura.

104


Si los libros de Microsoft Excel contienen macros, los antivirus instalados en la máquina se encargan de analizarlos, así si se encuentran virus el libro no podrá abrirse. Este mensaje es típico de un entorno de Excel 2010, ya que en esta versión el nivel de seguridad configurado de manera predeterminada es Deshabilitar todas las macros con notificación. Si estos niveles de seguridad no son cambiados las macros de un libro de Excel no se ejecutarán a menos de que estas sean reconocidas como seguras por Excel Configuración de la seguridad de macros Para cambiar la configuración de la seguridad de macros se deben tener en cuenta los siguientes pasos: Clic en el botón Opciones de Excel que se encuentra en el Menú Archivo. Seleccionar de la lista Centros de confianza que se encuentra al lado izquierdo de la ventana, y a la derecha seleccionar el botón Configuración del centro de confianza.

105


Se presenta una ventana Centro de confianza, con varias opciones:

106


Deshabilitar todas las macros sin notificación: Esta opción si está activa deshabilitará todas las macros contenidas en los documentos, y no se ejecutarán a menos de que el archivo esté en una ubicación de confianza. Además no presentará avisos informativos indicando que las macros se encuentran deshabilitadas. Deshabilitar todas las macros con notificación: Esta opción si está activa deshabilitará todas las macros contenidas en los documentos presentando los avisos si el libro contiene macros Deshabilitar todas las macros excepto las firmadas digitalmente: Esta opción si está activa deshabilitará todas las macros contenidas en los documentos sin notificación, excepto si el libro contiene macros que tengan Firmas digitales. Una firma digital certifica la autenticidad de la macro es decir indica que no ha sido cambiada y que ha sido creada y/o modificada por quien firma.

107


Habilitar todas las macros (no recomendado, puede ejecutarse código posiblemente peligroso): Si esta opción está activa se permite la ejecución de todas las macros. Clic en Aceptar.

108

Excel Especialista

Recommend Documents