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

3

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,


4

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.

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.

2

3

1 4

5

5


5

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.


6

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.


7

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.


8

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.


9

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.


10

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.


11

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.


12

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.


13

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.


14

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.


15

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 sucesivos en

Campo base como un total.

% 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))


16

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.


17

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.


18

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.

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.

1

2

2

3

4


19

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.


20

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.


21

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.


22

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.


23

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.


24

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.


25

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 <Alt>+<F5> 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.


26

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.


27

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 para seleccionar ambos.

Datos para seleccionar sólo los valores de los elementos.

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


28

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.


29

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.


30

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.


31

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.


32

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.


33

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.


34

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.


35

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.


36

Se pueden utilizar las funciones: suma, promedio, max, min, contar

números, desvestp, desvest, var varp.


37

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.


39

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.


40

Clic en Agregar nueva ubicación.


41

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


42

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


43

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


44

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.


45

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


46

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:


47

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.


48

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:


49

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.


50

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.


51

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.


52

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


53

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.


54

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.


55

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.


56

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.


57

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.


58

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.


59

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

http://powerpivot.com/


60

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.


61

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.


62

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…


63

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


64

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.


65

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.


66

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.


67

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


68

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.


69

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.


70

Una vez se tiene la información necesaria se puede realizar el PivotTable

haciendo clic en el icono y PivotTable nuevamente.


71

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.


72

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


73

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.


74

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.


75


77

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.


78

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.


79

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.


80

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.


81

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


82

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


83

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.


84

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.


85

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.


86

3. Hacer clic en Ir.


87

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:


88

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:


89

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)


90

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.


91

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.


92

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


93

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.


95

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…


96

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


97

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

<Ctrl> + <c>, 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 <Enter> o clic en el botón Aceptar

7. En la celda E12 escriba =Suma(H2:h11)

8. Oprimir la tecla <Enter> 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.


98

Pasos para ver macros

1. En la ficha vista, grupo macros, hacer clic en la flecha desplegable de

macros y seleccionar ver Macros.


99

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.


100

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.


101

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.


102

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.


103

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.


104

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.


105

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.


106

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


107

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.


108

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.

excel especialista

Documents