diplomado ejecutivo de excel

Diplomado Ejecutivo de Excel

2012

New Horizons Inc.

Tabla de contenido Creación de Base de Datos en Excel ............................................................................................... 4

CONCEPTO DE BASE DE DATOS .................................................................................................. 4

DATOS EN TABLAS DE INFORMACIÓN ........................................................................................ 5

FORMULARIOS ........................................................................................................................... 6

TABLAS DE DATOS ...................................................................................................................... 8

IMPORTACIÓN DE DATOS DESDE FUENTES EXTERNAS. ............................................................... 8

ORDENAR UNA TABLA DE INFORMACIÓN ................................................................................... 9

FILTRANDO DATOS EN MICROSOFT EXCEL ................................................................................ 13

FILTROS AUTOMÁTICOS ........................................................................................................... 13

FILTROS AVANZADOS ............................................................................................................... 14

FORMATOS CONDICIONALES.................................................................................................... 17

FUNCIONES DE DECISIONES SI() ............................................................................................... 22

Decisiones Simples ............................................................................................................... 22

Decisiones Anidadas ............................................................................................................. 24

VALIDACIÓN DE DATOS ............................................................................................................ 25

FUNCIONES DE BASE DE DATOS ............................................................................................... 29

BDSUMA: ............................................................................................................................. 29

BDCONTAR: .......................................................................................................................... 30

BDMIN: ................................................................................................................................ 31

BDMAX: ............................................................................................................................... 32

FUNCION INDICE: ..................................................................................................................... 33

MATRICIAL ........................................................................................................................... 33

FORMA DE REFERENCIA ....................................................................................................... 34

FUNCIONES DE TIPO TEXTO ...................................................................................................... 35

MINUSC ............................................................................................................................... 35

MAYUSC ............................................................................................................................... 36

NOMPROPIO ........................................................................................................................ 36

CONCATENAR ....................................................................................................................... 37

IZQUIERDA ........................................................................................................................... 38

DERECHA .............................................................................................................................. 38

FUNCIÓN DE BÚSQUEDA .......................................................................................................... 40

BUSCARV .............................................................................................................................. 40

BUSCARH ............................................................................................................................. 43

TABLAS DINAMICAS ..................................................................................................................... 45

AREAS DE TRABAJO .................................................................................................................. 45

Guardar Área de Trabajo ...................................................................................................... 45

ESQUEMAS .............................................................................................................................. 46

Esquemas Manuales ............................................................................................................. 46

Auto Esquema ...................................................................................................................... 48

ESCENARIOS ............................................................................................................................. 49

Creación de Escenarios Excel 2003 ........................................................................................ 49

Escenarios en Excel 2007 y 2010 ........................................................................................... 50

BUSCAR OBJETIVO ................................................................................................................... 51

USAR BUSCAR OBJETIVO PARA DETERMINAR EL TIPO DE INTERÉS ........................................ 52

TABLAS DINAMICAS ................................................................................................................. 55

Versión 2003 ...................................................................................................................... 55

CAMPOS CALCULADOS ............................................................................................................. 63

GRAFICOS DINAMICOS ............................................................................................................. 67

SOLVER .................................................................................................................................... 69

Cuadro de diálogo Parámetros de Solver .............................................................................. 70

MACROS EN EXCEL ....................................................................................................................... 74

CONCEPTOS BASICOS ................................................................................................................... 74

TIPOS DE MACROS ................................................................................................................... 74

LA GRABADORA DE EXCEL .................................................................................................... 74

EJECUTAR UNA MACRO. ....................................................................................................... 79

OBSERVAR EL CODIGO DE UNA MACRO ................................................................................... 81

EDITOR DE VISIAL BASIC APLICATIONS ...................................................................................... 84

LA FUNCIÓN MSGBOX. ............................................................................................................. 86

INSERTAR UN NUEVO MÓDULO. .......................................................................................... 86

INSERTAR UN PROCEDIMIENTO. ........................................................................................... 87

EJECUTAR UN PROCEDIMIENTO O FUNCIÓN. ....................................................................... 88

USO DE FORMULARIOS ............................................................................................................ 89

PROGRAMANDO LOS OBJETOS ................................................................................................. 96

TRABAJAR CON FORMULAS EN FORMULARIOS ............................................................................ 98

ESTRUCTURAS CONDICIONALES ............................................................................................... 98

ESTRUCTURAS IF ANIDADAS ....................................................................................................101

PROPIEDAD OFFSET ................................................................................................................101

ESTRUCTURA REPETITIVA DO WHILE..Loop (Hacer Mientras). .................................................102

CREAR UN FORMULARIO DE CONSULTA ......................................................................................103

BOTÓN BUSCAR CÓDIGO.........................................................................................................104

BOTÓN LIMPIAR CODIGO ........................................................................................................105

BOTÓN SALIR ..........................................................................................................................105

CUADROS COMBINADOS (COMBOBOX) ......................................................................................106

BOTONES DE OPCIÓN (OPTION BUTTON) ....................................................................................107

EXCEL FINANCIERO .....................................................................................................................110

FUNCIONES DE PRESTAMO .....................................................................................................110

Función para el Cálculo de una cuota constante (Cuota Nivelada) .......................................110

Función para el Cálculo de Pago a Capital ............................................................................111

Función para el Cálculo de Pago de Interes ..........................................................................111

PLAN DE AMORTIZACIÓN ........................................................................................................112

Cuota Nivelada ....................................................................................................................112

Valor Futuro ........................................................................................................................113

Valor Actual ........................................................................................................................115

Tiempo Para Amortizar una Deuda ......................................................................................117

Calculo de Tasa que se paga en un Préstamo .......................................................................118

CALCULO PARA DEPRECIACIÓN ...............................................................................................119

MÉTODO DIRECTO ..................................................................................................................119

MÉTODO SUMA DE DÍGITOS ....................................................................................................120

MEDIA GEOMETRICA ...............................................................................................................121

TASA INTERNA DE RETORNO ...................................................................................................123

VALOR PRESENTE NETO ..........................................................................................................125


Modulo: Base de datos

New Horizons Inc.

3 | P á g i n a

Material Propiedad de New Horizons Inc.



New Horizons Inc.

4 | P á g i n a


CreaciondeBasedeDatosenExcel

CONCEPTO DE BASE DE DATOS

Microsoft Excel permite la creación de “Tablas de Información”, la cual logra recolectar

información necesaria para las empresas, Nombres de Empleados, Sueldos, Artículos, Existencias,

etc. Dándole un orden a la información y así permitir la elaboración de consultas, modificaciones,

ó resúmenes, a partir de la misma.

Cuando nos disponemos a la elaboración de estas Tablas de información es importante identificar

el rol de la información desde la óptica de Excel. Para ejemplificarlo vea la siguiente tabla:

Tabla 1

Código Nombre Apellido 1 Henry Zuniga

2 Susan Gálvez

3 Felipe Benítez

4 Carlos Lanza

5 Sonia Bú

La fila donde están situados los encabezados, representan los campos de su tabla, (campos ó

criterios de la tabla), Excel logra identificarlos como campos cuando aplicamos una diferencia en

el formato de celda de los encabezados, del formato que posee la información.

Cada campo visto como una columna, almacena “Registros”. (Registro es la información como tal

en la tabla de Excel)

Estos registros son fracciones de una tabla, al juntar todas estas fracciones, o elementos de la

información, nos dan como resultado una Tabla de información y al juntar varias tablas dan como

resultado, Una base de Datos.

Las tablas, frecuentemente, se entienden como listas de información, y por listas de información

se hace referencia a un conjunto de filas que contiene datos, en dichas listas no se cuentan los

Campos ó Encabezados

Registros de Tabla



New Horizons Inc.

5 | P á g i n a


encabezados ya que estos son solo el identificativo de la información almacenada. En la creación

de las tablas, no se deben dejar filas en blanco, ya que Excel entenderá que es el final de su tabla

de información. Si al final de su tabla de información es necesario un cálculo de Total o algo por el

estilo, basándose en lo anterior, debe dejarse en blanco para el cálculo.

Dos son las operaciones que se pueden realizar de manera básica cuando la información esta en

forma de tablas de información.

1. Organizar (Ordenar) los registros con un orden determinado

2. Separar (Filtrar) los registros para ver únicamente la información requerida

¿Por qué usar Excel para la elaboración de la Base de datos?

Excel posee herramientas para el análisis de datos a partir de las tablas de información. Que

facilitan el manejo de la información, siempre y cuando la cantidad de información no se excesiva.

(Recuerde que según la versión de Microsoft Excel, así será la capacidad de su hoja de cálculo).

A parte de Microsoft Excel, existen otras alternativas para la elaboración de Base de Datos, entre

estas contamos con: Microsoft Access, Microsoft SQL.

DATOS EN TABLAS DE INFORMACIÓN Excel maneja 3 tipos de Datos Principales.

A. Datos Numéricos

B. Datos de Tipo Texto

C. Datos de Tipo fecha

Al dejar un campo que combine Texto y Número (Alfanumérico) Excel lo tomara como un dato de

tipo Texto.



New Horizons Inc.

6 | P á g i n a


FORMULARIOS

La primera herramienta que examinaremos en Microsoft Excel para facilitarnos el manejo de

información dentro de una tabla de información se denomina Formularios.

Esta herramienta permite que una ventana de Microsoft Excel posea los encabezados que

tenemos en la tabla de información, y desde la misma ventana podamos crear nuevos registros, o

eliminar algún criterio de la tabla, o simplemente consultarlos.

Pasos para Crear un formulario en Microsoft Excel 2007 y 2010

Haga clic en una celda del rango o de la tabla donde desee agregar el formulario.

Para agregar el botón Formulario a la Barra de herramientas de acceso rápido , realice lo siguiente:

1. Haga clic en la flecha que aparece junto a la Barra de herramientas de acceso rápido y, a

continuación, haga clic en Más comandos .

2. En el cuadro Comandos disponibles en , haga clic en Todos los comandos y, a continuación,

seleccione el botón Formulario de la lista.

3. Haga clic en Agregar y, a continuación, en Aceptar .

En la Barra de herramientas de acceso rápido , haga clic en Formulario .

Aparece un mensaje que indica "Hay demasiados campo s

en el formulario de datos"

Puede crear un formulario de datos únicamente para un rango

o una tabla con 32 columnas o menos. Si puede, reduzca el

número de columnas



New Horizons Inc.

7 | P á g i n a


Pasos para Crear un formulario en Microsoft Excel 2003

Haga clic en una celda del rango o de la tabla donde desee agregar el formulario.

Luego busque en la barra de Menú la opción Datos.

En el Menú desplegable seleccione la opción Formulario.

La ventana adoptara las características de su Tabla de Información. Los Botones que se ubicaran en la parte

derecha de la ventana le permitirán manipular los datos de la Tabla.

Ilustración 1 (Muestra de un Formulario)



New Horizons Inc.

8 | P á g i n a


TABLAS DE DATOS Existe una herramienta adicional en Microsoft Excel, para que nuestros cuadros adopten la

característica de una tabla de Información. (Este tipo de Opción solo esta disponible para la

versión 2007 y 2010).

Para poder usar esta opción, solo necesitara marcar el cuadro que desea convertir en una tabla de

datos.

En la pestaña de inicio, utilizaremos la opción Dar Formato Como Tabla.

Una lista de formatos de tabla aparecerá inmediatamente. Seleccione el

formato que considere mas apropiado. Cuando haya seleccionado un

formato, una nueva ventana aparecerá, la casilla La tabla tiene

encabezados, debe estar seleccionado.

Una nueva pestaña aparecerá con las herramientas de la tabla.

IMPORTACIÓN DE DATOS DESDE FUENTES EXTERNAS.

Microsoft Excel permite que nuestras tablas de información sean alimentadas desde otros

archivos, sean estos de Excel o no.

Las tablas pueden venir de datos de Microsoft Access o Microsoft SQL, fuentes ODBC, OLEDB, o

archivos XLM, incluso podemos crear conexiones WEB, para que se actualicen desde algún sitio de

internet. Para poder crear el vínculo con el archivo externo dependerá de la versión de Excel que

estemos Empleando.

Microsoft Excel 2007 y 2010.

Para las versiones 2007 y 2010, solo deberemos hacer clic en la pestaña de Datos.

En la sección izquierda, el grupo de herramientas Obtener datos externos, contiene las

herramientas que necesitamos.



New Horizons Inc.

9 | P á g i n a


Si en una conexión con Microsoft Access, utilizamos la primera opción.

Una nueva ventana aparecerá, y tendremos que ubicar el archivo de

Microsoft Access que deseamos importar a Excel.

Si fuese de otro tipo de fuente alojada en la computadora, podremos usar la opción De otras

fuentes.

En Microsoft Excel 2003

Haremos clic en el menú Datos, y luego ubicaremos la opción Obtener datos Externos.

ORDENAR UNA TABLA DE INFORMACIÓN

Cuando la tabla de Información ya tiene la estructura correcta, en ocasiones trataremos de

encontrar información específica en la tabla. Pero si la tabla contiene muchos registros, se

convertirá en un problema el tratar de extraer lo que necesitamos. La herramienta Ordenar nos

puede ayudar para que la información se agrupe bajo un criterio de la tabla, o varios criterios.

Para entenderlo daremos un ejemplo.

Imagine que su tabla condensa los empleados de su compañía. Y sus campos son:

No. De Empleado, Nombre del empleado, Departamento, Teléfono, Sueldo.

Al posicionarse por debajo de cualquier criterio, podremos mover la información de acorde a ese

criterio. Vea la siguiente imagen, y observe que la tabla esta ordenada por los No. De empleados.



New Horizons Inc.

10 | P á g i n a


Ilustración 2 (Tabla de Datos)

Si necesitáramos que los nombres de los empleados se ordenaran en orden alfabético podríamos

ordenar la tabla bajo ese criterio. Para ello:

1. Hacemos clic por debajo del encabezado Nombre del empleado.

2. Para ordenar la Tabla, hacemos clic en la pestaña de Datos.

3. Luego podemos usar la herramienta de Orden Ascendente y Descendente

La Tabla se vería así:

Ilustración 3 (Muestra de Orden de Un Criterio)



New Horizons Inc.

11 | P á g i n a


:

Ahora pongamos un segundo caso. Usted desea que su tabla muestre por Departamento sus

empleados, pero igual que muestre en orden ascendente los sueldos de estos empleados. A este

tipo de ordenamiento lo denominaremos Ordenamiento de criterios Múltiples.

Microsoft Excel 2007 y 2010

Para este tipo de caso usaremos la herramienta de Ordenar, que se encuentra en la Pestaña de

Datos.

(La posición de nuestro cursor debe ser en la tabla, no importando su ubicación, pero cuidando que

sea dentro de la tabla)



New Horizons Inc.

12 | P á g i n a


Un cuadro de dialogo se mostrará para poder elegir el orden de los criterios, para nuestro caso la

tabla puede quedar así:

Microsoft Excel 2003:

Para esta versión de Excel, necesitamos hacer clic en el menú de Datos, y Luego Ordenar

Cabe mencionar que en esta versión, solo nos permite 3 niveles de ordenamiento, y en la versión

2007 y 2010 pueden ser los niveles que necesitemos. El cuadro de Dialogo se vería así:

Ilustración 4 (muestra de Orden de Múltiples criterios)

Ilustración 5 (Múltiples Criterios Versión 2003)



New Horizons Inc.

13 | P á g i n a


FILTRANDO DATOS EN MICROSOFT EXCEL

Un filtro de datos es la herramienta que nos permite ocultar aquellos registros que no cumplan

cierto criterio que necesitemos.

Para ilustrarlo pongamos un ejemplo basándonos en la tabla anterior.

El criterio, solo los empleados del Depto. De Informática. La herramienta de Filtro permitirá que

los empleados que no son de ese departamento, queden ocultos.

Los filtros pueden ser aplicados de dos maneras: Automáticos, y Avanzados.

FILTROS AUTOMÁTICOS

Son los filtros que la mayoría de las personas utiliza, ya que son fáciles de aplicar, y aun mas

cuando se trata de varios criterios para la misma tabla.

Para Activar los filtros automáticos:

En Microsoft Excel 2007 y 2010

Clic en la pestaña de Datos, y luego clic en Filtro

Inmediatamente se aplicara sobre cada encabezado de la nuestra tabla un botón de filtro,

tomando el siguiente aspecto:

Ilustración 6 (AutoFiltros)



New Horizons Inc.

14 | P á g i n a


En Microsoft Excel 2003

Los pasos son:

1. Clic en el menú de Datos

2. Usar la opción Filtro

3. Y clic en la opción AutoFiltro

La tabla tomara el mismo aspecto que la ilustración 6.

FILTROS AVANZADOS La Herramienta de Filtros avanzados permite, al igual que los Autofiltros, que podamos ocultar los

registros que no cumplan con cierto criterio. Con la diferencia que los Autofiltros no permiten

varios casos a filtrar.

Daremos un ejemplo.

Con los auto filtros podrimos mostrar los empleados de las ciudad de San Pedro Sula, que

pertenecen al Depto. de Ventas, cuyas ventas estén por encima de Lps. 95,000.00

Pero Seria difícil, si al mismo tiempo, quisiéramos ver la ciudad de La Ceiba Cuyas Ventas fuesen

de Lps. 50,000.00 hacia abajo.

Para este tipo de casos es cuando los filtros Avanzados entran en juego. ¿Qué necesitamos para

poder aplicar los filtros avanzados?

Al final de la tabla de información, colocar los encabezados de la tabla que necesita para filtrar.

Para el ejemplo que anteriormente teníamos los encabezados podrían ser:

Ciudad, Depto. Y Ventas, tomando en cuenta que deben ser escritos tal y como están en la tabla a

filtrar. Por debajo de esos encabezados, debemos escribir los criterios a Filtrar, Ejemplo:

Ilustración 7 (Filtros Avanzados)



New Horizons Inc.

15 | P á g i n a


Note como los criterios de ventas, están escritos con Operadores lógicos de Excel.

Si necesitáramos un rango de ventas bastaría con duplicar el encabezado y colocar algo así:

Venta Venta

>=50000 <=125000

Una vez estipulada los criterios, procedemos a utilizar la herramienta.

Recomendación:

Microsoft Excel 2007 y 2010.

La herramienta de Filtros Avanzados, se encuentra en la Pestaña de Datos, y Luego la opción

Avanzadas.

Un nuevo cuadro de dialogo aparecerá, en el cual podremos configurar el resto.



New Horizons Inc.

16 | P á g i n a


Notara, que según la ilustración, Rango de la lista, ya posee un rango, si dejamos el cursor sobre la

tabla de información, el identificará nuestra tabla.

Ahora solo tendremos que dejar el cursor sobre la opción Rango de criterios:

Y proceder a Marcar los criterios que hemos escrito al final de la tabla de información, incluyendo

los encabezados de la misma.

Una vez estipuladas estas dos opciones, damos clic en Aceptar.

Inmediatamente nuestra tabla de información, nos dejara ala vista solo aquellos registros que

cumplan con nuestros criterios establecidos.

Microsoft Excel 2003

En la versión de Excel 2003 solo necesitamos hacer clic en el menú de Datos, Clic en Filtros, y por

ultimo clic en Filtros Avanzado...

Aparecerá la misma ventana que la Figura 8.

Ilustración 8 (Ventana Filtro Avanzado)



New Horizons Inc.

17 | P á g i n a


FORMATOS CONDICIONALES

Al referirnos al Formato de una celda, debemos tener claro que implica dicho formato.

Este abarca:

El grupo de Fuente (Negrita, Cursiva, Subrayado, Tamaño de Fuente, Color de Relleno, Color de

Fuente, Tipo de Fuente, Bordes de Celda, Color de Borde, etc.)

A veces es difícil leer e interpretar datos recorriendo filas y filas de información. Pero puede usar

el formato condicional para resaltar ciertos datos, lo que ayuda a analizar datos y a identificar

patrones y tendencias.

Ejemplifiquemos esto. Imagine que su tabla de información condensa los clientes que por rangos

de compra están categorizados como Excelentes clientes, Buenos Clientes, y clientes normales.

Usted necesita que la tabla destaque aquellos clientes que son Excelentes y así saber quienes son

de una manera más rápida y fácil.

Ó usted condensa en una tabla de información los clientes que poseen Crédito, pudiera destacar

mediante un formato, aquellos clientes que ya entraron en una mora Crediticia.

Para estos casos y otros similares, donde se necesite destacar información rápidamente, nos

ayuda el formato condicional.



New Horizons Inc.

18 | P á g i n a


La herramienta de Formato Condicional, se encuentra en la pestaña de inicio en la Versión 2007 y

2010. Y en la Versión 2003 haciendo clic en el menú de Formato, y luego Formato condicional.

Regalas de Formato Condicional

Aplicando operadores lógicos, podemos destacar los datos que sean Mayores a, Menores a,

Mayores o Iguales a, Distintos, etc.

En la versión 2007 y 2010 presenta todo un menú con estas reglas de validación, en la versión

2003 aparecen en la misma venta de configuración del formato.

Vea la siguiente Ilustración.

Ilustración 10(Icono Formato Condicional Versión 2007 y 2010)

Ilustración 9 (Formato Condicional Excel 2003)



New Horizons Inc.

19 | P á g i n a


Donde Actualmente la Ilustración deja ver Entre, podemos escoger varias opciones lógicas.

Para Poder Agregar mas reglas solo activamos el botón Agregar>> dos veces mas

Dejándonos ver la ventana así:

Ilustración 11(Ventana Formato Condicional V.2003)

Ilustración 12(Botón de Agregar Reglas)



New Horizons Inc.

20 | P á g i n a


En la Versión 2007 y 2010

Cada regla que usamos, se agrega al Administrador de Reglas. Dicha opción se encuentra en el

mismo menú de Formato condicional.

Ilustración 13 (Ventana Formato condicional 3 Reglas)



New Horizons Inc.

21 | P á g i n a


Una Ventana con todas las reglas de Formato condicional configuradas se nos mostrará.

Desde esta ventana, podremos crear nuevas reglas, editar las que ya existen, o eliminar las reglas.

Siempre que nuestro cursor este en una celda que hayamos configurado con formato condicional,

podremos ver el conjunto de reglas en el administrador de reglas.

Ilustración 14(Ventana Administrador de Reglas)



New Horizons Inc.

22 | P á g i n a


FUNCIONES DE DECISIONES SI()

Esta Función en la versión en ingles se conoce como IF.

Las decisiones dentro de Microsoft Excel pueden ocurrir de dos formas. Decisiones Simples y

Anidadas.

Decisiones Simples Se conocen como aquellas que solo contemplan 2 resultados. Uno Verdadero y otro Falso.

Para comprenderlo discutamos la sintaxis del comando.

PRUEBA LOGICA

Es una evaluación que debe realizarse para determinar cual de los 2 resultado ocurrirá, para esta

prueba lógica, necesitaremos Operadores Lógicos.

Estos pueden ser:

< Menor Que

> Mayor Que

<= Menor ó Igual

>= Mayor ó Igual

<> Distinto

“ “ Vacío

= Igual a



New Horizons Inc.

23 | P á g i n a


Ejemplo:

=Si(B7<1000 …

=SI(A2<>”” …

=SI(A1>=D3 …

=SI(B56=”” …

ETC.

VALOR SI VERDADERO

Es el resultado de la celda donde estamos editando la formula, que ocurrirá si la prueba lógica se

cumple.

VALOR SI FALSO

Es el resultado de la celda donde estamos editando la formula, que ocurrirá si la prueba lógica no

se cumple.

Piense en este caso; usted necesita determinar si el la venta de un empleado ha llegado a la meta

que la empresa a puesto para los vendedores, necesitamos saber quienes llegaron y quienes no,

para ello usaremos una formula de decisión para dejar la leyenda “Llego o supero” ó la leyenda

“No llego”.

Nuestra Formula podría quedar así:

Ilustración 15(ejemplo formula SI simple)



New Horizons Inc.

24 | P á g i n a


La prueba lógica evalúa si la venta del empleado (c7) es mayor o es igual a la meta (d3), en caso

afirmativo (Valor si Verdadero) el Texto “Llego ó Supero La Meta” será el valor de la celda donde

estamos colocando la formula (d7).

En caso que la evaluación no se cumpla, la leyenda “No Llego” será el resultado de la celda.

La función nos ayudará a determinar quienes llegaron a la meta, luego con algún formato

condicional, podríamos crear alguna regla (Texto que contiene) que nos destaque aquellas celdas

que tengan cualquiera de las dos leyendas.

Decisiones Anidadas Son las que nos ayudan cuando deseamos más de 2 resultados en la celda.

La anidaciones en la versión 2003 puede ocurrir hasta un total de 7 Veces.

Y 64 en la versión 2007 y 2010.

Para anidar, en la mayoría de los casos, se hace en la opción Valor Si Falso.

Pongamos un ejemplo:

Imaginemos que tenemos una tabla donde controlamos la comisión a pagar a los vendedores,

pero esa comisión depende del margen de venta que ha tenido, por x o y margen de venta una

tasa de comisión diferente:

Notemos que en cada opción Valor Si falso, si anida, o se junta una nueva decisión, permitiendo

controlar todas las comisiones que tenemos en la tabla.

Ilustración 16(ejemplo SI anidado)



New Horizons Inc.

25 | P á g i n a


VALIDACIÓN DE DATOS La validación de datos se usa para controlar el tipo de datos o los valores que los usuarios pueden

escribir en una celda. Por ejemplo, es posible que desee restringir la entrada de datos a un

intervalo determinado de fechas, limitar las opciones con una lista o asegurarse de que sólo se

escriben números enteros positivos.

Permite establecer restricciones respecto a los datos que se pueden o se deben escribir en una

celda. La validación de datos puede configurarse para impedir que los usuarios escriban datos no

válidos. Si lo prefiere, puede permitir que los usuarios escriban datos no válidos en una celda y

advertirles cuando intenten hacerlo. También puede proporcionar mensajes para indicar qué tipo

de entradas se esperan en una celda, así como instrucciones para ayudar a los usuarios a corregir

los errores.

Excel 2007 y 2010

En estas versiones la herramienta se ubica en la pestaña de Datos, y luego clic en el icono de

Validación de Datos.

Una nueva ventana aparecerá inmediatamente con las opciones de validación que ocupamos.

Ilustración 17(ventana Validación de Datos)



New Horizons Inc.

26 | P á g i n a


Según la ilustración anterior, la sección Permitir (donde actualmente dice Cualquier valor) es

donde podemos escoger cualquiera de las reglas de validación para aplicarla en una o varias

celdas.

¿Cuándo es útil la validación de datos?

La validación de datos es sumamente útil cuando desea compartir un libro con otros miembros de

la organización y desea que los datos que se escriban en él sean exactos y coherentes.

Puede usar la validación de datos para lo siguiente, entre otras aplicaciones:

• Restringir los datos a elementos predefinidos de una lista Por ejemplo, puede limitar los tipos

de departamentos a Ventas, Finanzas, Investigación y desarrollo y TI. De forma similar, puede

crear una lista de valores a partir de un rango de celdas que se encuentren en otra parte del

libro.

• Restringir los números que se encuentren fuera de un intervalo específico Por ejemplo, puede

especificar un límite mínimo de deducciones de dos veces el número de hijos en una celda

específica.

• Restringir las fechas que se encuentren fuera de un período de tiempo específico Por ejemplo,

puede especificar un período de tiempo entre la fecha actual y los 3 días siguientes.

• Restringir las horas que se encuentren fuera de un período de tiempo específico Por ejemplo,

puede especificar un período de tiempo para servir el desayuno entre la hora en que abre el

restaurante y cinco horas después.



New Horizons Inc.

27 | P á g i n a


• Limitar la cantidad de caracteres de texto Por ejemplo, puede limitar el texto permitido en una

celda a 10 caracteres o menos. De forma similar, puede establecer la longitud específica de un

campo de nombre completo (C1) en la longitud actual de un campo de nombre (A1) y un campo

de apellidos (B1), más 10 caracteres.

• Validar datos según fórmulas o valores de otras celdas Por ejemplo, puede usar la validación de

datos para establecer un límite máximo para comisiones y bonificaciones de 3.600 dólares,

según el valor de nómina proyectado general. Si los usuarios escriben un valor de más de 3.600

dólares en la celda, aparecerá un mensaje de validación.

Mensajes de validación de datos

Lo que los usuarios vean al escribir datos no válidos en una celda depende de cómo se haya

configurado la validación de datos. Puede elegir mostrar un mensaje de entrada cuando el usuario

seleccione la celda. Los mensajes de entrada suelen usarse para ofrecer a los usuarios orientación

acerca del tipo de datos que debe especificarse en la celda. Este tipo de mensaje aparece cerca de

la celda. Si lo desea, puede mover este mensaje y dejarlo visible hasta que el usuario pase a otra

celda o presione ESC.

Ilustración 18 (Ejemplo de Mensaje de Entrada)



New Horizons Inc.

28 | P á g i n a


También puede elegir mostrar un mensaje de error que solo aparecerá cuando el usuario escriba

datos no válidos.

Puede elegir entre tres tipos de mensajes de error:

Icono Tipo Se usa para

Detener Evitar que los usuarios escriban datos no válidos en una celda. Un mensaje de alerta Detener tiene dos opciones: Reintentar o Cancelar.

Advertencia Advertir a los usuarios que los datos que han escrito no son válidos, pero no les impide escribirlos. Cuando aparece un mensaje de alerta Advertencia, los usuarios pueden hacer clic en Sí para aceptar la entrada no válida, en No para editarla o en Cancelar para quitarla.

Información Informar a los usuarios que los datos que han escrito no son válidos, pero no les impide escribirlos. Este tipo de mensaje de error es el más flexible. Cuando aparece un mensaje de alerta Información, los usuarios pueden hacer clic en Aceptar para aceptar el valor no válido o en Cancelar para rechazarlo.

Puede personalizar el texto que los usuarios ven en un mensaje de error. Si elige no hacerlo, los

usuarios verán un mensaje predeterminado.

Los mensajes de entrada y de error sólo aparecen cuando los datos se escriben directamente en

las celdas. No aparecen en los siguientes casos:



New Horizons Inc.

29 | P á g i n a


• El usuario escribe datos en la celda mediante copia o relleno.

• Una fórmula en la celda calcula un resultado que no es válido.

• Una macro (macro: acción o conjunto de acciones utilizados para automatizar tareas. Las

macros se graban en el lenguaje de programación de Visual Basic para Aplicaciones.) especifica

datos no válidos en la celda.

FUNCIONES DE BASE DE DATOS

Las Funciones de base de datos en Microsoft Excel, permiten que manipulemos la tabla de

información y logremos realizar resúmenes de Datos, a partir de la misma tabla de información.

A continuación detallamos la lista de funciones y el concepto de las mismas.

BDSUMA: Esta Función en la versión en ingles se conoce como DSUM.

Suma los números de un campo (columna) de registros de una lista o base de datos que cumplen

las condiciones especificadas.

Sintaxis:

BDSUMA(base_de_datos; nombre_de_campo; criterios)

La sintaxis de la función BDSUMA tiene los siguientes argumentos (argumento: valor que

proporciona información a una acción, un evento, un método, una propiedad, una función o un

procedimiento.):

• Base_de_datos Obligatorio. El rango de celdas que compone la lista o base de datos. Una base

de datos es una lista de datos relacionados en la que las filas de información son registros y las

columnas de datos, campos. La primera fila de la lista contiene los rótulos de cada columna.



New Horizons Inc.

30 | P á g i n a


• Nombre_de_campo Obligatorio. Indica qué columna se usa en la función. Escriba el rótulo de la

columna entre comillas, como por ejemplo "Edad" o "Rendimiento", o un número (sin las

comillas) que represente la posición de la columna en la lista: 1 para la primera columna, 2 para

la segunda y así sucesivamente.

• Criterios Obligatorio. El rango de celdas que contiene las condiciones especificadas. Puede usar

cualquier rango en el argumento Criterios mientras éste incluya por lo menos un rótulo de

columna y al menos una celda debajo del rótulo de columna en la que se pueda especificar una

condición de columna.

BDCONTAR: Esta Función en la versión en ingles se conoce como DCOUNT.

Cuenta las celdas que contienen números en un campo (columna) de registros de una lista o base

de datos que cumplen las condiciones especificadas.

El argumento nombre_de_campo es opcional. Si se pasa por alto, BDCONTAR cuenta todos los

registros de la base de datos que coinciden con los criterios.

Sintaxis:

BDCONTAR(base_de_datos; nombre_de_campo; criterios)

La sintaxis de la función BDCONTAR tiene los siguientes argumentos (argumento: valor que


procedimiento.):








New Horizons Inc.

31 | P á g i n a








BDMIN: Esta Función en la versión en ingles se conoce como DMIN.

Devuelve el valor mínimo de un campo (columna) de registros en una lista o base de datos que

cumple las condiciones especificadas.

Sintaxis

BDMIN(base_de_datos; nombre_de_campo; criterios)

La sintaxis de la función BDMIN tiene los siguientes argumentos (argumento: valor que


procedimiento.):














New Horizons Inc.

32 | P á g i n a


BDMAX: Esta Función en la versión en ingles se conoce como DMAX.

Devuelve el valor máximo de un campo (columna) de registros en una lista o base de datos que

cumple las condiciones especificadas.

Sintaxis:

BDMAX(base_de_datos; nombre_de_campo; criterios)

La sintaxis de la función BDMAX tiene los siguientes argumentos (argumento: valor que


procedimiento.):












A esta lista podríamos agregar BDPROMEDIO, BDCONTARA, BDPRODUCTO, se usan de la misma

forma que los anteriores.



New Horizons Inc.

33 | P á g i n a


FUNCION INDICE:

Devuelve un valor o la referencia a un valor de una tabla o rango. La función INDICE presenta dos

formas: matricial y de referencia.

MATRICIAL

Devuelve el valor de un elemento de una tabla o matriz (matriz: utilizada para crear fórmulas

sencillas que producen varios resultados o que funcionan en un grupo de argumentos que se

organizan en filas y columnas. Un rango de matriz comparte una fórmula común; una constante de

matriz es un grupo de constantes utilizadas como un argumento.) seleccionado por los índices de

número de fila y de columna.

Use la forma matricial si el primer argumento de INDICE es una constante matricial.

Sintaxis:

INDICE(matriz; núm_fila; [núm_columna])

La sintaxis de la función INDICE tiene los siguientes argumentos (argumento: valor que


procedimiento.):

• Matriz Obligatorio. Un rango de celdas o una constante de matriz.

Si matriz contiene solo una fila o columna, el argumento núm_fila o núm_columna

correspondiente es opcional.

Si matriz tiene más de una fila y más de una columna y solo utiliza núm_fila o núm_columna,

INDICE devuelve una matriz con toda una fila o columna.

• Núm_fila Obligatorio. Selecciona la fila de la matriz desde la cual se devolverá un valor. Si se

omite núm_fila, se requiere el argumento núm_columna.



New Horizons Inc.

34 | P á g i n a


• Núm_columna Opcional. Selecciona la columna de la matriz desde la cual se devolverá un valor.

Si se omite núm_columna, se requiere el argumento núm_fila.

FORMA DE REFERENCIA

Devuelve la referencia de la celda ubicada en la intersección de una fila y de una columna

determinadas. Si el argumento ref es una selección múltiple, se podrá elegir la selección en la que

se buscará la referencia.

Sintaxis

INDICE(ref; núm_fila; [núm_columna]; [núm_área])

La sintaxis de la función INDICE tiene los siguientes argumentos (argumento: valor que


procedimiento.):

• Ref Obligatorio. Una referencia a uno o varios rangos de celdas.

Si especifica un rango no adyacente como argumento ref, escríbalo entre paréntesis.

Si cada área del argumento ref contiene una sola fila o columna, el argumento núm_fila o

núm_columna respectivamente, es opcional. Por ejemplo, utilice INDICE(ref;;núm_columna)

para un argumento ref con una sola fila.

• Núm_fila Obligatorio. El número de la fila en el argumento ref desde la que se devolverá una

referencia.

• Núm_columna Opcional. El número de la columna en el argumento ref desde la que se

devolverá una referencia.

• Núm_área Opcional. Selecciona un rango en el argumento ref desde el cual se devolverá la

intersección de núm_fila y núm_columna. La primera área seleccionada o especificada se

numera con 1, la segunda con 2 y así sucesivamente. Si se omite núm_área, INDICE usa el área 1



New Horizons Inc.

35 | P á g i n a


Por ejemplo, si ref describe las celdas (A1:B4;D1:E4;G1:H4), entonces núm_área 1 es el rango

A1:B4, núm_área 2 será el rango D1:E4, y núm_área 3 es el rango G1:H4.

FUNCIONES DE TIPO TEXTO

Este grupo de funciones permiten que le demos ciertos atributos a las celdas cuando estas

contienen datos de tipo texto, ejemplo: cuantas veces hemos comenzado a escribir un listado y

nos damos cuenta, muy tarde, que todo esta en minúscula, o viceversa.

Las funciones de tipo texto nos ayudaran a corregirlas rápidamente. Veamos una lista de ellas con

sus descripciones.

MINUSC

En la versión en ingles de Microsoft Office se conoce como LOWER.

Convierte todas las mayúsculas de una cadena de texto en minúsculas.

Sintaxis:

MINUSC(texto)

La sintaxis de la función MINUSC tiene los siguientes argumentos (argumento: valor que


procedimiento.):

• Texto. El texto que se desea convertir en minúsculas. MINUSC no cambia los caracteres de texto

que no sean letras.



New Horizons Inc.

36 | P á g i n a


MAYUSC

En la versión en ingles de Microsoft Office se conoce como UPPER.

Pone el texto en mayúsculas.

Sintaxis:

MAYUSC(texto)

La sintaxis de la función MAYUSC tiene los siguientes argumentos (argumento: valor que


procedimiento.):

• Texto. El texto que se desea pasar a mayúsculas. El argumento texto puede ser una referencia o

una cadena de texto.

NOMPROPIO

En la versión en ingles de Microsoft Office se conoce como PROPER.

Cambia a mayúscula la primera letra del argumento texto y cualquiera de las otras letras de texto

que se encuentren después de un carácter que no sea una letra. Convierte todas las demás letras a

minúsculas.

Sintaxis:

NOMPROPIO(texto)

La sintaxis de la función NONPROPIO tiene los siguientes argumentos (argumento: valor que


procedimiento.)

• Texto. El texto entre comillas, una fórmula que devuelve texto o una referencia a una celda que

contiene el texto al que se desea agregar mayúsculas.



New Horizons Inc.

37 | P á g i n a


CONCATENAR

En la versión en ingles de Microsoft Office se conoce como CONCATENATE.

La función CONCATENAR une hasta 255 cadenas de texto en una sola. Los elementos que se unirán

pueden ser texto, números, referencias de celda o una combinación de estos elementos. Por

ejemplo, si la hoja de cálculo contiene el nombre de una persona en la celda A1 y el apellido en la

celda B1, ambos valores se pueden combinar en otra celda mediante la fórmula siguiente:

=CONCATENAR(A1;" ";B1)

El segundo argumento del ejemplo (" ") es un carácter de espacio. Todos los caracteres de espacio

o puntuación que desee que aparezcan en el argumento, debe especificarlos como un argumento

entre comillas.

Sintaxis:

CONCATENAR(texto1; [texto2]; ...)

La sintaxis de la función CONCATENAR tiene los siguientes argumentos (argumento: valor que


procedimiento.):

• Texto1 Obligatorio. El primer elemento de texto que se concatenará.

• Texto2; ... Opcional. Elementos de texto adicionales, hasta un máximo de 255 elementos.

Deben estar separados con punto y coma.

Nota También puede utilizar el operador de cálculo símbolo de "y" comercial (&) en lugar de la

función CONCATENAR para unir elementos de texto. Por ejemplo,=A1 & B1 devuelve el mismo

valor que =CONCATENATE(A1, B1) .



New Horizons Inc.

38 | P á g i n a


IZQUIERDA En la versión en ingles de Microsoft Office se conoce como LEFT.

devuelve el primer carácter o caracteres de una cadena de texto, según el número de caracteres

que especifique el usuario.

Sintaxis

IZQUIERDA(texto; [núm_de_caracteres])

La sintaxis de las funciones tiene los siguientes argumentos (argumento: valor que proporciona

información a una acción, un evento, un método, una propiedad, una función o un

procedimiento.):

• Texto Obligatorio. La cadena de texto que contiene los caracteres que se desea extraer.

• Núm_de_caracteres Opcional. Especifica el número de caracteres que se desea extraer con la

función IZQUIERDA.

Núm_de_caracteres debe ser mayor o igual a cero.

Si núm_de_caracteres es mayor que la longitud del texto, IZQUIERDA devolverá todo el texto.

Si núm_de_caracteres se omite, se calculará como 1.

DERECHA

En la versión en ingles de Microsoft Office se conoce como RIGHT

Devuelve el último carácter o caracteres de una cadena de texto, según el número de caracteres

que el usuario especifica.

DERECHA se ha diseñado para ser utilizado con idiomas que utilizan el juego de caracteres de un

byte (SBCS), La configuración de idioma predeterminada del equipo influye en el valor devuelto del

modo siguiente:

• DERECHA siempre cuenta cada carácter como 1, ya sea de un byte o de doble byte e

independientemente de la configuración predeterminada de idioma.



New Horizons Inc.

39 | P á g i n a


Sintaxis:

DERECHA(texto;[núm_de_caracteres])

Las funciones DERECHA tiene los siguientes argumentos (argumento: valor que proporciona

información a una acción, un evento, un método, una propiedad, una función o un

procedimiento.):

• Texto Obligatorio. Cadena de texto que contiene los caracteres que se desea extraer.

• Núm_de_caracteres Opcional. Especifica el número de caracteres que desea extraer con

DERECHA.

Observaciones

• Núm_de_caracteres debe ser mayor o igual a cero.

• Si núm_de_caracteres es mayor que la longitud del texto, DERECHA devolverá todo el texto.

• Si núm_de_caracteres se omite, se calculará como 1.

Esta lista de funciones no son las únicas que podemos usar para el manejo de texto. Pero si son de

los más frecuentes.



New Horizons Inc.

40 | P á g i n a


FUNCIÓN DE BÚSQUEDA

BUSCARV

Esta función se conoce en la versión de Microsoft Office en Ingles como VLOOKUP

Puede usar la función BUSCARV para buscar la primera columna de un rango (rango: dos o más

celdas de una hoja. Las celdas de un rango pueden ser adyacentes o no adyacentes.) de celdas y, a

continuación, devolver un valor de cualquier celda de la misma fila del rango.

Por ejemplo, si tiene una lista de empleados contenida en el rango A2:C10, los números de

identificación de los empleados se almacenan en la primera columna del rango, como muestra la

siguiente ilustración.

Si conoce el número de identificación del empleado, puede usar la función BUSCARV para devolver

el departamento o el nombre de dicho empleado. Para obtener el nombre del empleado número

38, puede usar la fórmula =BUSCARV(38; A2:C10; 3; FALSO). Esta fórmula busca el valor 38 en la

primera columna del rango A2:C10 y, a continuación, devuelve el valor contenido en la tercera

columna del rango y en la misma fila que el valor de búsqueda ("Juan Carlos Rivas").

La V de BUSCARV significa vertical. Use BUSCARV en lugar de BUSCARH si los valores de

comparación se encuentran en una columna situada a la izquierda de los datos que desea buscar.



New Horizons Inc.

41 | P á g i n a


Sintaxis:

BUSCARV(valor_buscado; lookup_value, matriz_buscar_en; indicador_columnas; [ordenado])

La sintaxis de la función BUSCARV tiene los siguientes argumentos (argumento: valor que


procedimiento.):

• valor_buscado Obligatorio. Valor que se va a buscar en la primera columna de la tabla o rango.

El argumento valor_buscado puede ser un valor o una referencia. Si el valor que proporcione

para el argumento valor_buscado es inferior al menor valor de la primera columna del

argumento matriz_buscar_en, BUSCARV devuelve al valor de error #N/A.

• matriz_buscar_en Obligatorio. El rango de celdas que contiene los datos. Puede usar una

referencia a un rango (por ejemplo, A2:D8) o un nombre de rango. Los valores de la primera

columna de matriz_buscar_en son los valores que busca valor_buscado. Estos valores pueden

ser texto, números o valores lógicos. Las mayúsculas y minúsculas del texto son equivalentes.

• indicador_columnas Obligatorio. Un número de columna del argumento matriz_buscar_en

desde la cual debe devolverse el valor coincidente. Si el argumento indicador_columnas es igual

a 1, la función devuelve el valor de la primera columna del argumento matriz_buscar_en; si el

argumento indicador_columnas es igual a 2, devuelve el valor de la segunda columna de

matriz_buscar_en y así sucesivamente.

Si el argumento indicador_columnas es:

• Inferior a 1, BUSCARV devuelve al valor de error #¡VALOR!.

• Superior al número de columnas de matriz_buscar_en, BUSCARV devuelve el valor de error

#¡REF!.

• ordenado Opcional. Un valor lógico que especifica si BUSCARV va a buscar una coincidencia

exacta o aproximada:



New Horizons Inc.

42 | P á g i n a


Si ordenado se omite o es VERDADERO, se devolverá una coincidencia exacta o aproximada. Si

no encuentra ninguna coincidencia exacta, devolverá el siguiente valor más alto inferior a

valor_buscado.

Importante Si ordenado se omite o es VERDADERO, los valores de la primera

columna de matriz_buscar_en deben estar clasificados según un criterio de

ordenación ascendente; en caso contrario, es posible que BUSCARV no devuelva el

valor correcto.

Si ordenado es FALSO, no es necesario ordenar los valores de la primera columna de

matriz_buscar_en.

• Si el argumento ordenado es FALSO, BUSCARV sólo buscará una coincidencia exacta. Si hay

dos o más valores en la primera columna de matriz_buscar_en que coinciden con el

argumento valor_búsqueda, se usará el primer valor encontrado. Si no se encuentra una

coincidencia exacta, se devolverá el valor de error #N/A.

Nota:

Observaciones:

� Al buscar valores de texto en la primera columna de matriz_buscar_en, asegúrese de que

los datos de la primera columna de matriz_buscar_en no tienen espacios al principio ni al

final, de que no hay un uso incoherente de las comillas rectas ( ' o " ) ni tipográficas ( ‘ o “) y

de que no hay caracteres no imprimibles. En estos casos, BUSCARV puede devolver un valor

inesperado o incorrecto.



New Horizons Inc.

43 | P á g i n a


� Al buscar valores de fechas o números, asegúrese de que los datos de la primera columna

de matriz_buscar_en no se almacenen como valores de texto, ya que, en ese caso,

BUSCARV puede devolver un valor incorrecto o inesperado.

BUSCARH Esta función se conoce en la versión de Microsoft Office en Ingles como HLOOKUP

Permite realizar las búsquedas de manera Horizontal, basándonos en la misma lógica del comando

BUSCARV.


Modulo: Tablas Dinámicas

New Horizons Inc.

44 | P á g i n a




New Horizons Inc.

45 | P á g i n a


TABLAS DINAMICAS

AREAS DE TRABAJO Cuando Tenemos la costumbre de trabajar con múltiples archivos, y estos a su vez están

relacionados de alguna manera, conviene que los convirtamos en áreas de trabajo, esta

herramienta permite que mediante un único acceso, llamemos a varios archivos (los que se han

guardado dentro del área de trabajo) y así poder trabajar con ellos normalmente.

Guardar Área de Trabajo Para ello debemos abrir todos los archivos que formaran parte del área de trabajo, hacemos clic

en la pestaña de VISTA, y utilizamos la opción Organizar Todo.

Una vez hacemos clic en esta opción un nuevo cuadro de

Dialogo aparecerá donde podremos organizar los archivos abiertos, la opción a escoger será

Mosaico en esta nueva ventana.

El total de las ventanas activas se mostraran en una sola, subdividiéndola según el total de

archivos abiertos. En la misma pestaña de VISTA, encontraremos la opción para guardar nuestra

área de trabajo.

Ilustración 19(Panel Organizar Ventanas)



New Horizons Inc.

46 | P á g i n a


Un cuadro de dialogo de guardar aparecerá, con el que podremos guardar el acceso a nuestra área

de trabajo. Una vez guardado, el icono del área de trabajo nos quedará así.

ESQUEMAS La esquematización permite que nuestras hojas de trabajo puedan ocultar información y mostrarla

en el momento correcto, así el análisis y comprensión de la información será mucho más fácil. Esta

esquematización puede ocurrir de dos maneras.

Esquemas Manuales Son aquellas que a nuestra conveniencia colocamos en la hoja de Excel, pongamos un ejemplo

para entenderlo. Imagine que usted tiene un cuadro con el que condensa el movimiento de 3

meses. Y tiene una columna de total así:

Marcaremos los encabezados de Enero: Marzo para que sea esta área la que pueda ocultarse o

mostrarse. En la pestaña de datos encontraremos la herramienta de Agrupar.



New Horizons Inc.

47 | P á g i n a


Bajo el Menú Agrupar escogemos la opción

Una nueva ventana se mostrará y preguntara como deseamos agrupar, por columna, o por fila.

Escogeremos por columna para este ejemplo.

Y la hoja de Excel, dejara los controles de

Agrupamiento así:

Con este control podremos contraer los meses de Enero a Marzo.

Los botones 1 y 2 que vemos en la parte izquierda, automatizarían la acción de contraer y

expandir.

El mismo método podría aplicarse para agrupar los artículos, y solo dejar ver el Total por fila que

tenemos.

Ilustración 20(ejemplo de Agrupamiento)



New Horizons Inc.

48 | P á g i n a


Auto Esquema El auto esquema permite que el Microsoft Excel elija donde es el punto mas adecuado para

generar el agrupamiento. Para ello, nos exige que nuestra tabla de información, contenga

formulas, y así, donde encuentre una formula será el punto de agrupamiento.

Para ello, hacemos clic en la Pestaña de Vista, la opción de Agrupar, y Luego Auto Esquema.

Inmediatamente se colocaran los puntos de agrupamiento. Y la hoja de Excel se vera así:

Los agrupamientos se aplicaran tanto por columna como por fila.



New Horizons Inc.

49 | P á g i n a


ESCENARIOS Un escenario es un conjunto de valores que Microsoft Excel guarda y puede sustituir

automáticamente en la hoja de cálculo. Puede utilizar los escenarios para prever el resultado de

un modelo de hoja de cálculo. Puede crear y guardar diferentes grupos de valores en una hoja de

cálculo y, a continuación, pasar a cualquiera de estos nuevos escenarios para ver distintos

resultados.

Creación de Escenarios Excel 2003

La herramienta de escenarios en la versión 2003 se encuentra haciendo clic en el menú de

Herramientas, luego haciendo clic en la opción Escenarios. Una vez cumplido estos pasos una

ventana mostrará.

Una vez el asistente este a la vista daremos clic en agregar, daremos un nombre al escenario y este

guardara los datos según como estén en ese momento, repetiremos esa misma acción en cada

cambio que necesitemos de la hoja para luego solo aplicar con doble clic los resultados según el

escenario.

Ilustración 21(Administrador de Escenarios 2003)



New Horizons Inc.

50 | P á g i n a


Escenarios en Excel 2007 y 2010 Para Iniciar el administrador de Escenarios, nos moveremos a la pestaña de Datos, Luego

ubicaremos la opción Analisis Y Si.

Dentro de este grupo de herramientas, encontraremos Administrador de Escenarios, aplicaremos

el mismo procedimiento antes explicado.

Ilustración 22 (Administrador de Escenario 2007 y 2010)



New Horizons Inc.

51 | P á g i n a


BUSCAR OBJETIVO Buscar objetivo es parte de una serie de comandos a veces denominados herramientas de análisis

Y si. En el caso de que conozca el resultado deseado de una fórmula sencilla, pero no la variable

que determina el resultado, podrá utilizar la función Buscar objetivo.

Si sabe qué resultado desea obtener de una fórmula, pero no está seguro de qué valor de entrada

necesita la fórmula para obtener ese resultado, use la característica Buscar objetivo. Por ejemplo,

imagine que debe pedir prestado dinero. Sabe cuánto dinero desea, cuánto tiempo va a tardar en

saldar el préstamo y cuánto puede pagar cada mes. Puede usar Buscar objetivo para determinar

qué tipo de interés deberá conseguir para cumplir con el objetivo del préstamo.

La característica Buscar objetivo funciona solamente con un valor de entrada variable.

Pongamos un ejemplo.

1. Abra una nueva hoja de cálculo en blanco.

2. Primero agregue algunos rótulos en la primera columna para que sea más fácil leer la hoja de

cálculo.

1. En la celda A1, escriba Importe del préstamo.

2. En la celda A2, escriba Período en meses.

3. En la celda A3, escriba Tipo de interés.

4. En la celda A4, escriba Pago.

3. A continuación, agregue los valores.

1. En la celda B1, escriba 100.000. Éste es el importe del préstamo que desea.

2. En la celda B2, escriba 180. Ésta es la cantidad de meses que desea para saldar el préstamo.

1. A continuación, agregue la fórmula para la cual tiene un objetivo. Para este ejemplo, use la

función PAGO:

• En la celda B4, escriba =PAGO(B3/12,B2,B1). Esta fórmula calcula el importe del pago. En este

ejemplo desea pagar 900 € cada mes. No especifica ese importe aquí ya que desea usar

Buscar objetivo para determinar el tipo de interés y esta característica requiere que se

empiece con una fórmula.



New Horizons Inc.

52 | P á g i n a


La fórmula hace referencia a las celdas B1 y B2, que contienen los valores especificados en los

pasos anteriores. La fórmula también hace referencia a la celda B3 que es el lugar que especificará

para que Buscar objetivo coloque el tipo de interés. La fórmula divide el valor de B3 por 12 porque

ha especificado un pago mensual y la función PAGO da por supuesto un tipo de interés anual.

Como no hay ningún valor en la celda B3, Excel da por supuesto un tipo de interés del 0% y, con

los valores del ejemplo, devuelve un pago de 555,56 €. Puede pasar por alto ese valor por ahora.

2. Por último, dé formato a la celda objetivo (B3) de manera que muestre el resultado como un

porcentaje.

1. En la ficha Inicio, en el grupo Número, haga clic en la opción Porcentaje.

2. Haga clic en Aumentar decimales o en Disminuir decimales para establecer la cantidad de

posiciones decimales.

USAR BUSCAR OBJETIVO PARA DETERMINAR EL TIPO DE INTERÉS

1. En el grupo Herramientas de datos de la ficha Datos, haga clic en Análisis de hipótesis y, a

continuación, en Buscar objetivo.

2. En el cuadro Definir la celda, escriba la referencia de la celda que contiene la fórmula que desea

resolver. En el ejemplo, la referencia es la celda B4.

3. En el cuadro Con el valor, especifique el resultado de la fórmula que desee. En el ejemplo es -900.

Observe que este número es negativo porque representa un pago.

4. En el cuadro Cambiando la celda, indique la referencia de la celda que contiene el valor que desea

ajustar. En el ejemplo es la celda B3.

5. Haga clic en Aceptar.

Ilustración 23(Ventana Buscar Objetivo)



New Horizons Inc.

53 | P á g i n a


CONSOLIDACION DE DATOS

Para resumir y registrar resultados de hojas de cálculo independientes, puede consolidar datos de

cada una de estas hojas en una hoja de cálculo maestra. Las hojas pueden estar en el mismo libro

que la hoja de cálculo maestra o en otros libros. Al consolidar datos, lo que se hace es

ensamblarlos de modo que sea más fácil actualizarlos y agregarlos de una forma periódica o

específica.

Por ejemplo, si tiene una hoja de cálculo de cifras de gastos para cada una de las oficinas

regionales, podría utilizar una consolidación para resumir estas cifras en una hoja de cálculo de

gastos corporativa. Esta hoja de cálculo maestra podría contener totales de ventas y promedios,

niveles de inventario actuales y los productos más vendidos de toda la organización.

Para consolidar datos, utilice el comando Consolidar del grupo Herramientas de datos de la

ficha Datos.



New Horizons Inc.

54 | P á g i n a


1. Configure los datos que se van a consolidar en cada una de las hojas de cálculo independientes.

Cómo configurar los datos

2. Haga clic en la celda superior izquierda del área donde desee que aparezcan los datos

consolidados en la hoja de cálculo maestra.

NOTA Asegúrese de dejar suficientes celdas a la derecha y por debajo de esta celda para los

datos de consolidación. El comando Consolidar rellena el área según proceda.

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

4. En el cuadro Función, haga clic en la función de resumen que desea que utilice Microsoft Office

Excel para consolidar los datos.

5. Si la hoja de cálculo se encuentra en otro libro, haga clic en Examinar para buscar el archivo y, a

continuación, haga clic en Aceptar para cerrar el cuadro de diálogo Examinar.

La ruta de acceso del archivo se escribe en el cuadro Referencia seguido de un signo de

exclamación.

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

para cada rango.

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

• Para configurar la consolidación de manera que se actualice automáticamente cuando cambien los

datos de origen, active la casilla de verificación Crear vínculos con los datos de origen.

IMPORTANTE Solamente puede activar esta casilla de verificación si la hoja de cálculo se

encuentra en otro libro. Cuando active esta casilla de verificación ya no podrá cambiar las celdas y

los rangos que se incluyen en la consolidación.

• Para configurar la consolidación de modo que puede actualizarla manualmente cambiando

las celdas y los rangos incluidos, desactive la casilla de verificación Crear vínculos con los

datos de origen.

8. Deje en blanco las casillas de Usar rótulos en. Excel no copia los rótulos de fila o columna de los

rangos de origen a la consolidación. Si desea rótulos para los datos consolidados, cópielos de uno

de los rangos de origen o introdúzcalos manualmente.



New Horizons Inc.

55 | P á g i n a


TABLAS DINAMICAS Las tablas dinámicas parten de un solo conjunto de datos para poder realizar el análisis correspondiente. Este conjunto de datos debe tener las siguientes propiedades: 1.- Todas las columnas deben tener un nombre 2.- No se pueden usar celdas combinadas 3.- Las tablas dinámicas no distinguen formatos de celdas

Una vez que tenemos la estructura de la tabla de datos, podremos construir la tabla dinámica.

Versión 2003 Para construir una tabla dinámica en Excel 2003, dejamos el cursor en la tabla de datos. Luego

hacemos clic en el menú de Datos. Escogemos la opción Informe de Tabla y Grafico Dinámico…

Ilustración 24(Asistente Tabla Dinámica)



New Horizons Inc.

56 | P á g i n a


Este asistente de 3 pasos nos guiara para poder crear la estructura de Tabla Dinámica.

En el paso 2, el asistente confirmará si es la base de datos marcada la correcta para la lectura de

datos.

El paso 3, el asistente pregunta donde dejará la tabla, si en la hoja de cálculo actual o en una

nueva.

Antes de confirmar la ubicación, usamos el botón Diseño que permite configurar la tabla.

Esta ventana mostrará la lista de campos que podremos usar en la tabla dinámica.

Ejemplo:



New Horizons Inc.

57 | P á g i n a


Todo lo que tendremos que hacer es posicionar los campos en las secciones correspondientes, La

sección llamada Pagina, tiene como finalidad filtrar la información por un criterio en especifico, un

país en especial, una ciudad, un nombre, un articulo, un mes, etc. La sección Columna tiene como

finalidad filtrar la información por más de un criterio o desplegar todos los criterios en forma de

columna, varios países, varios meses, varios productos, varios nombres, o solo algunos de ellos. Al

igual que la sección Fila, con al diferencia que aquí los datos estarían cada uno en una fila. (El uso

de estas secciones dependerá de que tan clara veamos la información que perseguimos para el

informe). La sección Datos permite que los criterios se vean por Sumas, promedio, conteos, etc.

Siempre y cuando estos datos sean numéricos. Si los datos son de tipo texto, esta sección solo

podrá contar cuantas veces aparecen en la tabla de información.

Pongamos un caso.

Necesitamos ver la lista de empleados de la ciudad de San Pedro Sula, y El Depto. De Ventas de

esta ciudad, saber cuanto vendieron, y el promedio de venta. Note que en la imagen anterior, la

lista de campos existen esos datos, por lo que podríamos ordenarlos así:

(Para cambiar el criterio de datos a ventas a un promedio, arrastramos 2 veces el campo de

VENTAS hacia esta sección, sobre el segundo campo hacemos doble clic y cambiamos de una

suma a un promedio).



New Horizons Inc.

58 | P á g i n a


Ya que solo queremos la ciudad de San Pedro Sula, y solo el depto. De ventas, lo ponemos el la

sección de pagina. Y veremos la lista de nombres hacia abajo colocándolos en la sección de Fila.

Al dar clic en aceptar y finalizar en el paso 3 de 3 el informe se vería así:

Hasta este punto, aun no se ha filtrado para lo que necesitamos, si nos fijamos en la parte

superior, Ciudad y DEPTO tienen dos botones de filtro, seleccionaremos los criterios de SAN

PEDRO SULA, y VENTAS respectivamente. Y se vera así:



New Horizons Inc.

59 | P á g i n a


Notaremos que la tabla se redujo en gran medida dándonos un resumen de lo que realmente

necesitamos. Cuando nuestra tabla dinámica aparece por primera vez, la barra de herramientas

TABLA DINÁMICA nos permitirá cambiar el diseño de la tabla dinámica y podremos ponerle algún

formato a la misma.

La sección dar formato al informe permite seleccionar cualquiera de los formatos de

una galería.

Seleccionemos el autoformato llamado “Informe 6”.

Ahora Veremos este mismo caso en la versión 2007 y 2010.

Partiremos de la base de datos en Excel. Para poder generar la estructura de tabla dinámica,

dejaremos el cursor dentro de la tabla. Luego haremos clic en la pestaña de Insertar. Y la primera

opción de la cinta de opciones es Tabla Dinámica.

Ilustración 25 (Barra Tabla Dinámica)



New Horizons Inc.

60 | P á g i n a


Al hacer clic en el icono de tabla dinámica, el asistente se mostrará.

Notaremos que este asistente no tiene una secuencia de pasos como en la versión 2003. Aquí nos

deja elegir la ubicación de la Tabla dinámica directamente. Solo tendremos que hacer un 2do clic

en el botón Aceptar.



New Horizons Inc.

61 | P á g i n a


Así es como se vería el informe. La lista de campos y secciones para filtrar están al lado derecho de

nuestra ventana de Microsoft Excel. Si movemos los campos a las secciones correspondientes

(Ciudad y DEPTO en Filtro de informe, Nombre en la sección de Etiquetas de Fila, venta en la

sección de Valores), y filtramos estos campos (Ciudad=SAN PEDRO SULA, DEPTO=Ventas) la tabla

se vera así:



New Horizons Inc.

62 | P á g i n a


El formato de la tabla la encontraremos en la pestaña de Diseño al final de la cinta de opciones, la

opción Diseño, presenta una galería muy amplia para poder seleccionar el formato que considere

más apropiado.

Para añadir el promedio a la tabla, arrastraremos el dato de venta a la sección de Valores

nuevamente, en ese momento una pestaña de color negro se mostrará a la derecha del campo, si

hacemos clic sobre esta pestaña, un menú se mostrará, escogeremos la opción, Configuración de

campo de Valor.

En la venta que nos muestra cambiamos de suma a promedio y haremos clic en aceptar para ver el

cambio en la tabla.



New Horizons Inc.

63 | P á g i n a


CAMPOS CALCULADOS Imagine que usted desea aplicar un porcentaje de comisión del 7.5%, y le gustaría saber a cuanto

asenderea ese calculo para la tabla dinámica que ya tenemos, sin tener que modificar la tabla de

datos original. Para este tipo de caso podremos hacer uso de la herramienta Campo Calculado.

Ilustración 26 (Ventana Configuración de Campo de Valor)



New Horizons Inc.

64 | P á g i n a


Excel 2003

En esta versión el campo calculado se encuentra en la barra de herramienta Tabla dinámica.

La ventana que aparecerá nos permitirá realizar el cálculo.

Ilustración 27 (ejemplo de ventana Campo Calculado)



New Horizons Inc.

65 | P á g i n a


Nombre: permite configurar el nombre que deseamos para el nuevo campo. (Cabe mencionar que

este campo no se reflejará en la tabla original, solo será para uso de la tabla dinámica).

Formula: es la sección donde podremos crear el contenido del campo, es importante tomar en

cuenta que esta formula debe involucrar procesos matemáticos únicamente.

El ejemplo nos muestra como quedaría el cálculo que necesitamos.

Versión 2007.

En esta versión la opción de campo calculado se encuentra haciendo clic en la pestaña de

Opciones, luego haciendo clic sobre Formulas, y por ultimo Campo Calculado.



New Horizons Inc.

66 | P á g i n a


Versión 2010

En esta versión la opción de campo calculado se encuentra haciendo clic en la pestaña de

Opciones, luego haciendo clic sobre Cálculos, Luego en Campos, elementos y conjuntos, y por

ultimo Campo Calculado.

Después solo repetimos la ilustración 27 de este manual.



New Horizons Inc.

67 | P á g i n a


GRAFICOS DINAMICOS Para poder generar gráficos dinámicos, siempre dependeremos de un informe de tabla dinámica,

lo que significa, que si ya poseemos una tabla dinámica, podremos generar el grafico a partir de la

misma.

Versión 2003

En esta versión hacemos uso de la barra de Tabla Dinámica para generar el grafico.

Este icono permite que se genere el grafico a partir de la tabla ya armada previamente. El grafico

se ubicará en una nueva página.



New Horizons Inc.

68 | P á g i n a


Notaremos que en el mismo grafico puede aplicarse los filtros.

Versión 2007 y 2010

Para activar el grafico en estas versiones, partiremos que ya tenemos la Tabla Dinámica Armada,

luego, haremos clic en la pestaña de opciones, luego haremos clic en el Icono llamado Grafico

Dinámico.

Una nueva ventana aparecerá y nos preguntara que tipo de grafico necesitamos. Al seleccionarlo,

y dar clic en aceptar, el grafico quedará en la misma hoja de trabajo de nuestra tabla.

Nota:

En la versión 2007, los filtros son aplicables fuera del grafico, en una barra nueva, mientras que en

la versión 2010 al igual que la versión 2003 los filtros son aplicables dentro del grafico.

Ilustración 28(Grafico Dinámico en Excel 2010)



New Horizons Inc.

69 | P á g i n a


SOLVER

Es una de las herramientas más potentes de Excel, ya que permite hallar la mejor solución a un

problema, modificando valores e incluyendo condiciones o restricciones. La herramienta no viene

activa en ninguna barra de herramienta al inicio, por lo que vamos a mostrar la opción primero.

Versión 2003

Primero hacemos clic en Herramientas, luego la opción Complementos. Y por ultimo activamos la

casilla de verificación del solver.



New Horizons Inc.

70 | P á g i n a


Versión 2007 y 2010

1. Mostramos la ventana de opciones de Excel

2. Hacemos clic en Complementos

3. En la parte inferior de la ventana hacemos clic en el icono IR..

4. Activamos la casilla de Solver

La herramienta estará: En la versión 2003. Herramientas/Solver, en la versión 2007 y 2010

Datos/Solver.

Cuadro de diálogo Parámetros de Solver Con Solver, puede buscarse el valor óptimo para una celda, denominada celda objetivo, en una

hoja de cálculo. Funciona en un grupo de celdas que estén relacionadas, directa o indirectamente,

con la fórmula de la celda objetivo. Solver ajusta los valores en las celdas cambiantes que se

especifiquen, denominadas celdas ajustables, para generar el resultado especificado en la

fórmula de la celda objetivo. Pueden aplicarse restricciones para restringir los valores que puede

utilizar Solver en el modelo y las restricciones pueden hacer referencia a otras celdas a las que

afecte la fórmula de la celda objetivo.



New Horizons Inc.

71 | P á g i n a


Celda objetivo: Celda que se desea definir con un valor determinado o que se desea maximizar o

minimizar.

Valor de la celda objetivo: Específica si se desea maximizar o minimizar la celda objetivo, o bien

definirla con un valor específico el cual se introducirá en el cuadro.

Cambiando las celdas: Celdas que pueden ajustarse hasta que se satisfagan las restricciones del

problema, pueden especificarse 200 celdas como máximo. Estimar: Estima todas las celdas que

no contienen ninguna fórmula a las que se hace referencia en la fórmula de la celda objetivo y

escribiéndola en el cuadro Cambiando las celdas.

Sujeto a las siguientes restricciones: Muestra una lista de las restricciones actuales en el problema,

permitiéndose editar dichas restricciones. Resolver: Inicia el proceso de solución del problema

definido. Cerrar: Cierra el cuadro de diálogo sin resolver el problema. Retiene todos los cambios

que se hayan realizado mediante los botones Opciones, Agregar, Cambiar o

Borrar. Opciones: Muestra el cuadro de diálogo Opciones de Solver, donde pueden cargarse y

guardarse los modelos de problema y las características de control avanzado del proceso de

solución. Restablecer todo: Borra los valores actuales del problema y restablece todos los valores

a sus valores originales.

Ilustración 29 (ventana Solver Excel 2003 y 2007)



New Horizons Inc.

72 | P á g i n a


Ilustración 30(Ventana Solver Excel 2010)


Modulo: Creación de Macros

New Horizons Inc.

73 | P á g i n a




New Horizons Inc.

74 | P á g i n a


MACROS EN EXCEL

CONCEPTOS BASICOS

Una macro es un conjunto de instrucciones que sirven para automatizar procesos. Consiste en una

serie de comandos y funciones que se almacenan en un módulo de Microsoft Visual Basic y que

puede ejecutarse siempre que sea necesario realizar la tarea. Para esto Excel para Windows

cuenta con dos herramientas básicas: las macros y los módulos. Refiriéndonos a Excel,

supongamos que realizamos frecuentemente la acción de seleccionar un rango para aplicarle

negrita, cambio de fuente y centrado. En lugar de hacer estas acciones manualmente, se puede

elaborar una macro e invocarla para que ejecute los tres procesos automáticamente

TIPOS DE MACROS Podríamos hablar de dos tipos de Macros Principales, Una de ellas, son las que creamos utilizando

la Grabadora de Microsoft Excel. Y las que escribimos mediante líneas de programación en el

editor de VBA (Visual Basic Aplications).

LA GRABADORA DE EXCEL Si no somos muy experimentados con el código de programación, es la primera opción que

tenemos para la elaboración de macros, ya que la herramienta de macros nos permite automatizar

nuestras acciones.

Recomendación: Antes de utilizar la herramienta de grabar Macro, haga un plan de acción,

practique los pasos que desea grabar con la herramienta, ya que una equivocación al momento de

grabar, implicaría grabarla también, por lo que tendríamos una macro inexacta.


En esta versión de Excel, la grabadora se encuentra haciendo clic en el Menú de Herramientas,

Luego Clic en la Opción de Macro, y por ultimo Grabar Nueva Macro. Un cuadro de dialogo

aparecerá para que podamos configurar la grabación.



New Horizons Inc.

75 | P á g i n a



En esta versión de Microsoft Excel, necesitamos que la Ficha de Programador deba estar activa.

Para ello:

1. Haremos clic en el botón de Inicio de Office.

2. Clic en Opciones de Excel (En la parte Inferior)

3. En la Sección Mas Frecuentes, encontraremos la opción Mostrar Ficha de

Programador en la Cinta de Opciones.

4. Clic en Aceptar.

Ilustración 31(Opciones de Excel)



New Horizons Inc.

76 | P á g i n a



Igual que en la versión 2007 necesitamos la ficha de Programador. Para activarlo en esta versión:

1. Haremos clic en la Pestaña de Inicio

2. Clic en opciones

3. Clic en Personalizar Cinta de opciones

4. Activar Programador

Ilustración 32(Ventana de Opciones)



New Horizons Inc.

77 | P á g i n a


Cuando ya tenemos activa esta pestaña, tendremos un grupo de Herramientas Llamada Código

La opción Grabar macro es la que ocupamos. Al hacer clic en esta opción, un nuevo cuadro de

dialogo aparecerá, (este es el mismo cuadro de dialogo de la versión 2003).

Nombre de la Macro

Esta sección nos ofrece un nombre asignado por el Excel, si nosotros deseamos asignar uno

diferente, solo debemos tener el cuidado de No incluir espacios en el nombre.

Método abreviado

Permite que configure una combinación de Teclas para ejecutar la macro directamente desde su

teclado, si utiliza una letra minúscula, la combinación será CTRL + la letra elegida, si usa una letra

Mayúscula, la combinación será CTRL + SHIFT + la letra elegida. (Hay que tener el cuidado de no

elegir una combinación que ya posee el Excel, como CTRL + C, CTRL + V, etc.). Este es un paso

opcional, ya que como veremos mas adelante, podremos ejecutar la macro de diferentes formas.

Ilustración 33(Ventana Grabadora de Macro)



New Horizons Inc.

78 | P á g i n a


Una vez la grabación comience, la opción Grabar Macro, cambiará por, Detener Grabación.

También podrá ver esa misma opción al pie de la ventana de Microsoft Excel.

En la versión 2003 tendremos que regresar a Herramientas, Macros, Detener Grabación, para

poder terminar de grabar, ó solo utilizar el icono de la barra de herramientas de macro que

aparece cuando iniciamos la grabación.

Una vez grabada la Macro, podremos ejecutarla desde el teclado si lo configuramos.

En caso contrario, detallamos las opciones para poder ejecutarla.

Ilustración 34(opción detener grabación)



New Horizons Inc.

79 | P á g i n a


EJECUTAR UNA MACRO.

Mediante la herramientas de MACROS

La ejecución de Macros se puede realizar mediante el administrador de Macros.

En la versión 2007 y 2010

Nos posicionamos en la pestaña de programador, luego hacemos clic en la opción, MACROS.

Una Nueva Ventana se desplegará con la lista de macros grabadas en nuestro Libro de Trabajo.

Ilustración 35(Ventana de Administrador de Macros)



New Horizons Inc.

80 | P á g i n a


Bastará con seleccionar la macro que deseamos ejecutar, y hacer doble clic sobre ella, ó, hacer clic

en el botón de Ejecutar.

En la Versión 2003.

Primero hacemos clic en la Opción de Herramientas, luego nos posicionamos en la opción

MACROS, y luego la opción Macros.

Un nuevo cuadro de dialogo aparecerá para ejecutar las macros del libro. (Ver Ilustración 23).

Ilustración 36 (Administrador de Macros en 2003)



New Horizons Inc.

81 | P á g i n a


OBSERVAR EL CODIGO DE UNA MACRO

Todas las macros creadas con la grabadora de Excel, son escritas en el lenguaje de VBA.

(Para poder acceder al editor de VBA puede usar la combinación de teclas ALT F11).

Antes de poder examinar el código que deja una macro, entendamos en que consiste la estructura

de escritura del VBA.

En primera instancia, el editor hará referencia a un objeto;

Cuando en el mundo real nos referimos a objeto significa que hablamos de algo más o menos abstracto que puede ser cualquier cosa. Si decidimos concretar un poco más podemos referirnos a objetos coche, objetos silla, objetos casa, etc. En OOP, la generalización (o definición) de un objeto se llama Clase, así la clase coche seria como la representante de todos los coches del mundo, mientras que un objeto coche seria un coche en concreto. De momento, no definiremos ni estudiaremos las clases sino que nos concentraremos en los objetos, tenga en cuenta pero que cualquier objeto está definido por una clase. Cuando decimos que la clase coche representa a todos los coches del mundo significa que define como es un coche, cualquier coche. Dicho de otra forma y para aproximarnos a la definición informática, la clase coche define algo que tiene cuatro ruedas, un motor, un chasis,... entonces, cualquier objeto real de cuatro ruedas, un motor, un chasis,... es un objeto de la clase coche.

En Excel un objeto puede ser:

Una celda, una hoja, un libro de trabajo, etc. Los objetos se identifican así:

Nombre Natural Nombre en VBA

Celda ActiveCell

Varias Celdas Seleccionadas Selection

Hoja de Trabajo Worksheets

Libro de Trabajo ActiveWorkbook

Una Hoja En Especifico (Hoja1) Sheets (“hoja1”)

Una celda/Varias Celdas en especifico (A2) ó (A2:A21)

Range(“A2”) ó Range(“A2:A21”)



New Horizons Inc.

82 | P á g i n a


Estos entre los más relevantes.

Adicionalmente especifica una propiedad de ese objeto. (Tal y como nosotros describiríamos los

atributos de cualquier objeto, su tamaño, color, sabor, olor, altura, profundidad, etc. Así VBA

destaca las “propiedades” de un objeto).

Ejemplo:

Activecell.Select

Esto Significa “La celda Celeccionada”

Range(“A1”).Select

“Seleccionar la Celda A1”

Hoja1.activate

Mostrar la Hoja1

Para todos los casos notaremos que el objeto es separado de su propiedad mediante un punto (.)

Nombre Natural Nombre en VBA

Contenido de Celda .Value

Seleccionar una celda .Select / .Activate

Copiar un objeto .Copy

Pegar un Objeto .Paste

Activar Negría a una o mas Celdas .Font.Bold

Activar Cursiva a una o mas Celdas .Font.Italic

Activar Subrayado a una o mas Celdas .Font.Underline

Ocultar o mostrar un Objeto .Visible

Activar o desactivar un Objeto .Enabled

Después de especificar la propiedad, en muchos casos también hay que especificar un valor.

Objeto.Propiedad=Valor



New Horizons Inc.

83 | P á g i n a


Ejemplo:

ActiveCell.Font.Bold=True (Activar Negría a una Celda)

Ejemplo 2:

1. Sub cambiar_fuente()

2. Range(“A2:A10”).select

3. Selection.Font.italic=True

4. Selection.Font.Size=16

5. Range(“A2”).Select

6. End Sub

1. Marca el Inicio de la macro y nos muestra el nombre de la Macro “Cambiar_Fuente”

2. Selecciona las Celdas desde A2 hasta A10

3. Activa Cursiva a esas celdas

4. Cambia el tamaño de fuente a 16

5. Selecciona solo la Celda A2

6. Marca el Fin de la Macro

Para poder observar el código de una macro que hemos grabado, seguimos estos pasos:

Excel 2007 y 2010

1. Clic en la pestaña de programador

2. Hacemos clic en la opción MACROS

3. En la venta de Macros (Ilustración 23) seleccionamos la macro que deseamos ver su

código,

4. Hacemos clic en el botón de modificar.

El editor de VBA aparecerá.



New Horizons Inc.

84 | P á g i n a


Excel 2003

Clic en herramientas

Clic en Macros

Clic en Ejecutar Macros

Pasos 3 y 4 explicados anteriormente.

Conjuntos. Una conjunto es una colección de objetos del mismo tipo, para los que conozcan algún lenguaje de programación es un array de objetos. Por ejemplo, dentro de un libro de trabajo puede existir más de una hoja (WorkSheet), todas las hojas de un libro de trabajo forman un conjunto, el conjunto WorkSheets. Cada elemento individual de un conjunto se referencia por un índice, de esta forma, la primera, segunda y tercera hoja de un libro de trabajo, se referenciarán por WorkSheets(1), WorkSheets(2) y WorkSheets(3).

EDITOR DE VISIAL BASIC APLICATIONS

El editor de visual básic es la aplicación que utilizaremos para construir las macros que interactuaran junto con los libros de trabajo. A continuación prepararemos un archivo en el que escribiremos las primeras instrucciones en Visual basic. Preparar un archivo nuevo. Para entrar en el editor de Visual Basic, ejecute los pasos siguientes. Excel 2003 1. Active opción Herramientas/ Macro/ Editor de Visual Básic.

Excel 2007 y 2010

1. Clic en la pestaña de Programador

2. Clic en Visual Basic



New Horizons Inc.

85 | P á g i n a


Aparecerá una ventana así:

1. Ventana de Explorador de Proyecto

2. Ventana de Propiedades

3. Área de Trabajo

Ilustración 37(Ventana de Visual Basic Aplications)



New Horizons Inc.

86 | P á g i n a


Las Macros que nosotros grabamos en Excel, mediante la herramienta “Grabar Macro” se

almacenan en la ventana de Explorador de Proyectos, bajo la sección Módulos.

Al igual que la Grabadora, nosotros podemos fabricar nuestras macros mediante el uso de líneas

de programación.

LA FUNCIÓN MSGBOX. Esta función muestra un mensaje en un cuadro de diálogo hasta que el usuario pulse un botón. Se puede usar de dos formas, una donde configuramos los botones que mostrará la ventana del comando, o de manera simple, donde solo dejara un único botón, normalmente Aceptar. Sintáxis Msgbox Simple.

MsgBox “ Mensaje”

INSERTAR UN NUEVO MÓDULO. Un módulo sirve para agrupar procedimientos y funciones. El procedimiento y la función son entidades de programación que sirven para agrupar instrucciones de código que realizan una acción concreta.

Ilustración 38(Sección Módulos en VBA)



New Horizons Inc.

87 | P á g i n a


Para insertar un módulo active opción del menú Insertar/ Módulo. Se activará una nueva ventana, si aparece demasiado pequeña, maximícela.

INSERTAR UN PROCEDIMIENTO. Ya hemos dicho que un procedimiento es un bloque de instrucciones de código que sirven para llevar a cabo alguna tarea específica. Un procedimiento empieza siempre con la instrucción Sub <Nombre_Procedimiento> Y termina con la instrucción End Sub. A continuación crearemos un procedimiento para poner el texto "Hola" en la casilla A1. Ejemplo 1: Sub Primero Range("A1").Value = "Hola" End Sub Observe el código.

Range("A1").Value="Hola" En esta línea estamos indicando que trabajamos con un objeto Range. Para indicarle que nos referimos a la casilla A1, encerramos entre paréntesis esta referencia (más adelante verá otra forma de referirnos a las casillas). De este objeto, indicamos que queremos establecer un nuevo valor para la propiedad Value, observe que para separar el objeto de su propiedad utilizamos la notación punto. Recuerde que el conjunto Range es un objeto que pende del objeto WorkSheets, así por ejemplo el siguiente código haría lo mismo que el anterior con una leve diferencia: WorkSheets(1).Range("A1").Value = "Hola" En la primera opción, el texto "Hola" se pone dentro de la casilla A1 de la hoja activa, mientras que en este ejemplo es en la casilla A1 de primera hoja (Del conjunto de hojas). La segunda notación es más larga, pero también más recomendable ya que se especifican todos los objetos. En muchas ocasiones se pueden omitir algunos objetos precedentes, no le aconsejamos hacerlo, sus programas perderán claridad y concisión. Si desea hacer referencia a la hoja activa puede utilizar ActiveSheet, así, el primer ejemplo lo dejaremos de la manera siguiente.



New Horizons Inc.

88 | P á g i n a


Sub Primero ActiveSheet.Range("A1").Value = "Hola" End Sub Si desea poner "Hola" (o cualquier valor) en la casilla activa, puede utilizar la propiedad (objeto) Activecell de WorkSheets. Así para poner "Hola" en la casilla activa de la hoja activa seria, Sub Primero ActiveSheet.ActiveCell.Value = "Hola" End Sub Para terminar con este primer ejemplo. WorkSheets están dentro del Objeto WorkBooks (libros de trabajo) y WorkBooks están dentro de Application. Application es el objeto superior, es el que representa la aplicación Excel. Así, el primer ejemplo, siguiendo toda la jerarquía de objetos quedaría de la forma siguiente. Sub Primero Application.WorkBooks(1).WorkSheets(1).Range("A1").Value = "Hola" End Sub Insistiendo con la nomenclatura, Application casi nunca es necesario especificarlo, piense que todos los objetos penden de este, WorkBooks será necesario implementarlo si en las macros se trabaja con diferentes libros de trabajo (diferentes archivos), a partir de WorkSheets, es aconsejable incluirlo en el código, sobre todo si se quiere trabajar con diferentes hojas, verá, sin embargo, que en muchas ocasiones no se aplica.

EJECUTAR UN PROCEDIMIENTO O FUNCIÓN. Pruebe ejecutar el primer procedimiento de ejemplo. 1. Sitúe el cursor dentro del procedimiento. 2. Active opción de la barra de menús Ejecutar/ Ejecutar Sub Userform. También puede hacer clic sobre el botón ó pulsar la tecla F5.



New Horizons Inc.

89 | P á g i n a


USO DE FORMULARIOS Los Formulario de VBA permiten la creación de Macros en un formato más Grafico, lo que se

conoce como Programación Orientada a Objetos (OOP, por sus siglas en Ingles).

Para poder Iniciar Un formulario Siga los siguientes pasos:

1. Clic en el menú de Insertar

2. Clic sobre la opción de UserForm

La Ventana quedará así:

El recuadro UserForm1 representa el objeto principal a programar. Los objetos adicionales que

tendrá nuestro formulario, para poder manipular la información, se condensan en el Cuadro de

Herramientas.

Ilustración 39(Formulario en blanco en VBA)



New Horizons Inc.

90 | P á g i n a


Pondremos un ejemplo y así conoceremos mejor dicho cuadro. Imagine que usted desea crear un

formulario que le permita dar entrada de datos a una tabla de datos. Esta tabla de datos contiene

los siguientes campos.

Note que la línea 5 esta oculta, para evitar que el primer registro copie el relleno de celda de los

campos. Por lo que nuestros registros estarán a partir de la fila 6. A dicha celda le asignaremos un

nombre de Celda “Fin_Hoja”. Esto nos ayudará para determinar cual es el último registro de la

tabla e ir grabando los nuevos en la parte final de la tabla.

Una vez estructurada la tabla, procedemos a crear el formulario en VBA. (Pasos al inicio del

Subtema)

En el Formulario en Blanco comenzaremos a insertar los elementos del cuadro de herramientas.

Etiqueta (Label). Es el primer elemento que usaremos para crear un encabezado de

la ventana, y los encabezados de cada campo.

Ilustración 40(Cuadro de Herramienta en VBA)



New Horizons Inc.

91 | P á g i n a


Solo necesitamos arrastrar dicho objeto hacia nuestro formulario, hasta que se vea así:

Para poder cambiar el contenido de esta etiqueta, puede ubicar la barra de propiedades al lado

izquierdo, una de las propiedades nombra como CAPTION.

La propiedad Caption permite que cambiemos el contenido de la etiqueta, para mostrar un

nombre natural, lo que escribiremos aquí es “Control de Personal”. Notaremos que la etiqueta del

formulario mostrará ese contenido.

Ilustración 41(Objeto Etiqueta en el Formulario)



New Horizons Inc.

92 | P á g i n a


Para poder agrandar el tamaño de la fuente de la etiqueta, ubicamos una propiedad llamada

FONT.

Al posicionarnos en esta opción, aparecerá un botón con puntos suspensivos.

Una nueva ventana aparecerá para poder configurar dicho tamaño.

Ilustración 42(Ventana Propiedad FONT)



New Horizons Inc.

93 | P á g i n a


Una vez que hacemos los cambios que consideremos para este titulo, podría verse así:

Repitiendo estos pasos Trataremos que el formulario se vea así:

Luego Utilizaremos un Objeto llamado, Cuadro de Texto (TextBox)

Este objeto, permite que se hagan capturas de información, es el espacio donde introduciremos la

información.



New Horizons Inc.

94 | P á g i n a


Al igual que la etiqueta solo debemos arrastrarlo uno por debajo de cada Etiqueta. Se verá así:

Incluiremos un objeto más, un botón de comando (Command Button)

Igual que los casos anteriores, podremos arrastrar el objeto al formulario, y mediante la propiedad

de Caption, y Font, podremos cambiar el rotulo del Botón. Para que se vea así:



New Horizons Inc.

95 | P á g i n a


Si hacemos clic en el formulario directamente, y ubicamos la propiedad BackColor podremos

cambiar el color de fondo del Formulario completo. Dos opciones aparecen cuando usamos esta

propiedad, Sistema y Paleta. Escogemos Paleta.

Al escoger un color de nuestra preferencia el formulario podrá verse así:

Notará que las etiquetas poseen un pequeño marco que pueden confundirse con los botones de

comando, para poder eliminarlos, selecciónelas, y utilice la propiedad BackStyle. Le asignamos el

valor cero (0) para que el control quede Transparente.

Ilustración 43(Propiedad BACKCOLOR)



New Horizons Inc.

96 | P á g i n a


PROGRAMANDO LOS OBJETOS

La programación orientada a objetos permite que sea mucho más fácil la escritura de las líneas de

programación. Para nuestro ejemplo, solo necesitamos programar los botones. Previo a eso,

seleccionaremos las Cajas de Texto que ya hemos dibujado. (Pruebe con la caja de texto que esta

por debajo de Código).

La primera propiedad que encontramos en la barra se llama Nombre esta nos permitirá cambiar el

nombre con el cual VBA identifica este objeto, por omisión le asigna TextBox1 cada TextBox

tendrá un numero diferente, nosotros lo cambiaremos por un nombre natural, así podremos

recordarlo mas fácil. (Para nuestro ejemplo, se pueden asignar el mimos nombre del encabezado,

así sabremos que TextBox utilizar con cual Etiqueta).

Una vez hechos los cambios podremos programar el botón de Guardar. Para ellos daremos doble

clic sobre este botón.

El editor nos dejará nuestro modulo interno así:

Private Sub CommandButton1_Click()

End Sub



New Horizons Inc.

97 | P á g i n a


Private Sub/End Sub marcan el inicio y fin del modulo interno. CommandButton1 solo hace

referencia al objeto que se va a programar. _Click hace referencia a la acción departe de usuario

que espera el VBA para ejecutar las líneas de programación. La programación podría quedar asi:


‘Nos posicionamos en la celda llamada Fin_Hoja

Range(“Fin_Hoja”).select

‘Insertamos una fila nueva a partir de la celda Fin_Hoja

Selection.EntireRow.Insert

ActiveCell.value=Codigo

ActiveCell.Offset(0,1).value=Nombre

ActiveCell.Offset(0,2).value=Val(Sueldo)

ActiveCell.Offset(0,3).value=Depto

‘Limpiar los TextBox

Codigo=Empty

Nombre=Empty

Sueldo=Empty

Depto=Empty

‘Dejamos el Cursor en Codigo

Codigo.SetFocus

End Sub



New Horizons Inc.

98 | P á g i n a


Botón de Salir


UnLoad UserForm1

UserForm1.Hide

End Sub

Una vez programado los Botones, procedemos a presionar F5 para la

ejecución de la Macro.

TRABAJAR CON FORMULAS EN FORMULARIOS

ESTRUCTURAS CONDICIONALES Ahora que ya ha experimentado con unos cuantos objetos y propiedades, nos detendremos a estudiar las estructuras condicionales. Las estructuras condicionales son instrucciones de programación que permiten controlar la ejecución de un fragmento de código en función de si se cumple o no una condición. Estudiaremos en primer lugar la instrucción if Condición then..End (Si Condición Entonces...Fin Si) La estructura condicional que se construye con la instrucción Si Condición Entonces... Fin tiene la forma siguiente.



New Horizons Inc.

99 | P á g i n a


Si Condición Entonces Senténcia1 Senténcia2 .. SenténciaN Fin Si .. Cuando el programa llega a la instrucción Si Condición Entonces, se evalúa la condición, si esta se cumple (es cierta), se ejecutan todas las sentencias que están encerradas en el bloque, si no se cumple la condición, se saltan estas sentencias. Esta estructura en Visual Basic tiene la sintaxis siguiente, If Condición Then Senténcia1 Senténcia2 . . SenténciaN End If

Ejemplo.

Macro que compara los valores de las casillas A1 y A2 de la hoja activa. Si son iguales pone el color de la fuente de ambas en azul. Sub Condicional2() If ActiveSheet.Range("A1").Value = ActiveSheet.Range("A2").Value Then ActiveSheet.Range("A1").Font.Color = RGB(0, 0, 255) ActiveSheet.Range("A2").Font.Color = RGB(0, 0, 255) End If End Sub



New Horizons Inc.

100 | P á g i n a


Estructura If..Else Esta estructura se utiliza cuando se requiere una respuesta alternativa a una condición. Su estructura es la siguiente. Si Condición Entonces Senténcia1 Senténcia2 .. SenténciaN Sino Senténcia1 Senténcia2 .. SenténciaN Fin Si Observe que, si se cumple la condición, se ejecuta el bloque de sentencias delimitado por Si Condición Entonces y Si no se cumple la condición se ejecuta el bloque delimitado por Sino y Fin Si. En Visual Basic la instrucción Si Condición Entonces... Sino ... Fin Si se expresa con las instrucciones siguientes. If Condición Then Senténcia1 Senténcia2 SenténciaN Else Senténcia1 Senténcia2 . . SenténciaN End If



New Horizons Inc.

101 | P á g i n a


ESTRUCTURAS IF ANIDADAS

No tiene que sorprenderle, dentro de una estructura if puede ir otra, y dentro de esta otra, y otra... Vea el ejemplo siguiente. Comparar los valores de las celdas A1 y A2 de la hoja activa. Si son iguales, escribir en A3 "Los valores de A1 y A2 son iguales", si el valor de A1 es mayor que A2, escribir "A1 mayor que A2", sino, escribir "A2 mayor que A1". Sub Condicional() If ActiveSheet.Range("A1").Value = ActiveSheet.Range("A2").Value Then ActiveSheet.Range("A3").Value = "Los Valores de A1 y A2 son iguales" Else If ActiveSheet.Range("A1").Value > ActiveSheet.Range("A2").Value Then ActiveSheet.Range("A3").Value = "A1 mayor que A2" Else ActiveSheet.Range("A3").Value = "A2 mayor que A1" End If End If

PROPIEDAD OFFSET Esta propiedad es también muy útil a la hora de recorrer rango. Offset, que significa desplazamiento, es una propiedad del objeto Range y se utiliza para referenciar una casilla situada a n Filas y n Columnas de una celda dada. Vea los ejemplos siguientes. Range("A1").Offset(2, 2).Value = "Hola" 'Casilla C3 = Hola, 2 filas y 2 columnas desde A1. ActiveCell.Offset(5,1).Value = "Hola" ' 5 Filas por debajo de la casilla Activa = Hola



New Horizons Inc.

102 | P á g i n a


ESTRUCTURA REPETITIVA DO WHILE..Loop (Hacer Mientras).

Suponga que ha de recorrer un rango de filas en los que no se sabe cuantos valores habrá (esto es, cuantas filas llenas habrá), en ocasiones puede que hayan veinte, en ocasiones treinta, en ocasiones ninguna, etc. Para estos casos deberemos recurrir a la sentencia Do While..Loop en alguna de sus formas. Esta estructura repetitiva está controlada por una o varias condiciones, la repetición del bloque de sentencias dependerá de si se va cumpliendo la condición o condiciones. Ejemplo: Sub Ejemplo_dowhile() ‘ Activar hoja1 WorkSheets("Hoja1").Activate ‘ Activar casilla A2 ActiveSheet.Range("A1").Activate ‘ Mientras la celda activa no esté vacía Do While Not IsEmpty(ActiveCell) ‘ Hacer activa la celda situada una fila por debajo de la actual ActiveCell.Offset(1,0).Activate Loop End Sub



New Horizons Inc.

103 | P á g i n a


CREAR UN FORMULARIO DE CONSULTA

Imagine que desea realizar una búsqueda en una tabla de información.

Utilizando un código de empleado usted necesita que en el formulario nos

muestre el Nombre, el Sueldo, y el Depto. En el que trabaja. Veamos primero

como podría que dar el diseño del formulario.

La programación ira en los objetos Buscar Código, Limpiar Código, Salir.



New Horizons Inc.

104 | P á g i n a


BOTÓN BUSCAR CÓDIGO Nuestra Tabla de Información comienza en la celda B6 de la hoja de Excel.

Lo que significa que está en la Línea 6 Columna 2.

Para este ejemplo haremos uso de los comandos antes explicados.


Hoja1.Select

linea = 6

col = 2

encontro = False

Hoja1.Cells(linea, col).Activate

Do While Not IsEmpty(ActiveCell)

Hoja1.Cells(linea, col).Activate

If ActiveCell.Value = codigo Then 'si los codigo escritos son numericos habra que poner Val(codigo)

nombre = ActiveCell.Offset(0, 1).Value

sueldo = ActiveCell.Offset(0, 2).Value

depto = ActiveCell.Offset(0, 3).Value

encontro = True

Exit Do

End If

linea = linea + 1

Loop

If encontro = False Then

MsgBox "No Existe Este Codigo"

codigo = Empty

codigo.SetFocus

End If

End Sub



New Horizons Inc.

105 | P á g i n a


BOTÓN LIMPIAR CODIGO Private Sub CommandButton1_Click()

codigo = Empty

nombre = Empty

sueldo = Empty

depto = Empty

codigo.SetFocus

End Sub

BOTÓN SALIR Private Sub CommandButton2_Click()

UnLoad UserForm2

UserForm2.Hide

End Sub



New Horizons Inc.

106 | P á g i n a


CUADROS COMBINADOS (COMBOBOX)

Las opciones que se pueden configurar en este tipo de control facilita al

usuario a seleccionar datos y dejarlos como valores del control, si solo

necesitamos que se despliegue una lista de opciones simple, programaremos

dichas opciones en el formulario, bajo el evento de Initialize()

Por cada opción que necesitamos que se despliegue, hemos de repetir la

siguiente instrucción:

ComboBox1.AddItem “Valor1”

ComboBox1, es el nombre del Elemento (recuerde que este puede variar, ya

sea porque hemos dibujados varios en el formulario, o por que le hemos

cambiado el nombre al control).

“Valor1” es lo que deseamos despliegue en el menú.

Ejemplo: Si deseamos que un ComboBox despliegue las ciudades de venta,

(SPS, TGU, CEIBA, CHOLUTECA, STA. ROSA DE COPAN, TELA, PUERTO CORTES)

la programación podría quedar así:



New Horizons Inc.

107 | P á g i n a


Una vez digitemos esta programación, en el ComboBox y lo ejecutamos, el

control se vera así:

BOTONES DE OPCIÓN (OPTION BUTTON)

Los botones de opción se utilizan para elegir una única opción entre una serie de ellas, es decir, de un grupo de opciones sólo permitirán que se escoja una. De la misma forma que las casillas de verificación, casi siempre implican una estructura condicional para comprobar cual de ellas está activada. El botón activado tendrá su propiedad Value igual a true. Como ejemplo de su utilización crearemos dos botones de opción que sirvan para que a la hora de copiar datos hacia la hoja, se copie sólo los valores de Nombre y Apellidos o todos como hasta ahora. Incluya dos botones de opción y establezca las siguientes propiedades. Botón1.

Name, Todo. Caption, Todo.

Ilustración 44(Ejemplo de ComboBox)



New Horizons Inc.

108 | P á g i n a


Botón2. Name, Solo_Nombre. Caption, Nombre y Apellidos. Si está activado el primer botón deberán copiarse todos los datos mientras que si está activado el segundo solo se copiarán el Nombre y los Apellidos. El procedimiento Copiar_Datos_Hojas quedará de la forma siguiente.

Private Sub Copiar_Datos_Hojas(r1 As Range, r2 As Range) Dim i As Integer Dim Datos As Variant Dim Final As Integer ' Si Botón Todo Activado, se copian todas las columnas If Todo.Value = True Then Final = Num_Columnas - 1 Else ' Sólo se copian las dos primera columnas Final = 1 End If ' recorrer las columnas del registro y copiar celda a celda For i = 0 To Final ' Si la casilla Mayúsculas está activada y el tipo de datos es String If Mayusculas.Value = True And TypeName(r1.Offset(0, i).Value) = "String" Then

Datos = UCase(r1.Offset(0, i).Value) Else Datos = r1.Offset(0, i).Value End If r2.Offset(0, i).Value = Datos Next i End Sub


Modulo: Excel Financiero

New Horizons Inc.

109 | P á g i n a




New Horizons Inc.

110 | P á g i n a


EXCEL FINANCIERO

FUNCIONES DE PRESTAMO

Función para el Cálculo de una cuota constante (Cuota Nivelada)

Esta Función se conoce como Pago (PMT en la Versión en Ingles de Excel) .

La Sintaxis de este comando es la Siguiente:

=Pago(Tasa, Nper, VA, [VF], [Tipo]) Los datos entre corchetes, no son una exigencia.

Pongamos un ejemplo:

Una Persona, Solicita un préstamo de Lps. 80,000.00, la institución financiera estipula una tasa de

13.7% anual, y el solicitante le pide 24 meses para Pagar, ¿Cuál será su cuota durante este periodo

de tiempo a fin de cubrir capital e interés? Podríamos plantearlo así:

Ilustración 45 (Función PAGO)



New Horizons Inc.

111 | P á g i n a


Comenzamos con la Tasa, en esta ocasión, la hemos dividido entre 12 ya que nuestros pagos son

mensuales, por lo que el interés deberá ser mensual también. El siguiente dato es el plazo en

meses (NPER ó Numero de Períodos) y por ultimo el Monto (VA) lo hemos estipulado en negativo,

con el único interés que el resultado aparezca en positivo. Con estos Tres Datos, logramos hacer el

calculo de la cuota Mensual, para este ejemplo será Lps. 3,829.70 por omisión este calculo se

hace asumiendo que el cliente hará sus pagos al final de cada periodo, (Cuando los

intereses ya hayan corrido durante este período). Si deseáramos que el cálculo se

efectuara tomando los pagos al inicio del período, tendríamos que cambiar el tipo

(parámetro final de la función) en uno (1).

Función para el Cálculo de Pago a Capital Esta Función se conoce como Pagoprin(

La sintaxis de esta Función es:

=Pagoprin(Tasa, Período, nper, va, [vf], [Tipo])

En esta función se agrega un dato Período, este es el numero de cuota que deseamos calcular,

esta ligada al dato NPER por lo que no podremos calcular una cuota fuera del numero total que

especifique nper.

Función para el Cálculo de Pago de Interes

Esta Función se conoce como Pagoint(

La sintaxis de esta Función es:

=Pagoint(Tasa, Período, nper, va, [vf], [Tipo])

En esta función se agrega un dato Período, este es el numero de cuota que deseamos calcular,

esta ligada al dato NPER por lo que no podremos calcular una cuota fuera del numero total que

especifique nper.



New Horizons Inc.

112 | P á g i n a


PLAN DE AMORTIZACIÓN

Cuota Nivelada Utilizando las funciones aprendidas hasta ahora, podremos crear un plan de amortización, capaz

de mostrar nuestra deuda (asumiendo que nosotros somos el cliente con la deuda). Para ello

trataremos de Crear el siguiente cuadro:

Ahora Colocaremos los siguientes valores en la tabla superior:

Valor de Préstamo: 80000

Tasa Anual: 12.5%

Plazo: 30

Tipo de Pago: Vencido (este dato solo es una referencia ya que no influirá en nuestro cuadro)

Las Formulas podrían quedar así en el cuadro inferior.



New Horizons Inc.

113 | P á g i n a


A partir del pago 2, arrastramos la formula (AUTORELLENO DE CELDAS) hasta la fila 100 solo por

efectos de prueba para poder tener un rango grande y así apreciar nuestro cuadro.

Valor Futuro La función VF (Valor Futuro) en Microsoft Excel devuelve el valor futuro de una inversión

basándose en pagos periódicos constantes y una tasa de interés constante.

También puede utilizarse la función VF para calcular el valor futuro de un pago único suma. Para

ello, escriba el importe del pago suma como el valor actual (va) y escriba el importe del pago como

cero. Escribir un cero como el importe de pago indica a Excel que no hay ninguna secuencia

constante de pagos.

La función VF utiliza la siguiente sintaxis y argumentos FV ( tasa, nper, pago, va, tipo ) donde: Tasa: Es la tasa de interés por período. Nper: Es el número total de períodos de pago de una anualidad. Pago: Es el pago efectuado cada período; no puede cambiar durante la vida de la anualidad. Si se omite este argumento, debe incluir el argumento va. VA: Es el valor actual o la suma total de una serie de pagos futuros. Si va se omite, se supone que es 0 (cero) y debe incluir el argumento pago. Tipo: Es el número 0 ó 1 e indica cuando vencen los pagos. Si se omite el tipo, se supone que es 0. Por ejemplo, suponga que se invierta hoy mismo con una tasa de interés del 12 por ciento de 1.000 $, y le gustaría saber cuál la inversión será merece la pena al final de cinco años.



New Horizons Inc.

114 | P á g i n a


La fórmula FV se introduce como sigue: =FV(12%,5,0,-1000,0)

Pongamos Un ejemplo:

Suponga que usted desee crear un fondo de ahorro para Navidad. Usted realiza un depósito de LPS.500.00 dentro de una cuenta de ahorros que gana el 6% de interés anual compuesto mensualmente. Planea depositar LPS.100.00 al principio de cada mes durante los próximos 10 meses. ¿Qué cantidad obtendrá al final de los 10 meses?

La función de FV le pide los siguientes datos, FV (Tasa, nper, Pagos, VA, tipo):

Tasa es la tasa de interés por período, el cual en este ejemplo es el 6% anual, el 6%/12.

nper es el número total de períodos de pago, el cual en este ejemplo es 10.

Pago es el pago hecho para cada período, el cual en este ejemplo es LPS. 100.00.

VA es el valor actual, o sea, la cantidad que vale ahora, en este ejemplo son LPS. 500.00 del

depósito inicial.

Tipo es 0 ó 1, dependiendo de cuando se realicen los pagos, al final (0) o al inicio (1) de cada

período. Si es omitido, se asume que será 0. En este ejemplo es 1.

Como resultado, nuestra función es la siguiente: FV (5%, 10, -100, -500,1), la cual es igual a LPS. 1,553.49.



New Horizons Inc.

115 | P á g i n a


Valor Actual

Devuelve el valor actual de una inversión. El valor actual es el valor que tiene actualmente la suma

de una serie de pagos que se efectuarán en el futuro. Por ejemplo, cuando pide dinero prestado,

la cantidad del préstamo es el valor actual para el prestamista.

Sintaxis

VA(tasa;nper;pago;vf;tipo)

Tasa es la tasa de interés por período. Por ejemplo, si obtiene un préstamo para un automóvil

con una tasa de interés anual del 10 por ciento y efectúa pagos mensuales, la tasa de interés

mensual será del 10%/12 o 0,83%. En la fórmula escribiría 10%/12, 0,83% ó 0,0083 como tasa.

Nper es el número total de períodos de pago en una anualidad. Por ejemplo, si obtiene un

préstamo a cuatro años para comprar un automóvil y efectúa pagos mensuales, el préstamo

tendrá 4*12 (ó 48) períodos. La fórmula tendrá 48 como argumento nper.

Pago es el pago efectuado en cada período, que no puede variar durante la anualidad.

Generalmente el argumento pago incluye el capital y el interés, pero no incluye ningún otro

arancel o impuesto. Por ejemplo, los pagos mensuales sobre un préstamo de 10.000 $ a cuatro

años con una tasa de interés del 12 por ciento para la compra de un automóvil, son de 263,33 $.

En la fórmula escribiría -263,33 como argumento pago. Si se omite el argumento pago, deberá

incluirse el argumento vf.

Vf es el valor futuro o un saldo en efectivo que se desea lograr después de efectuar el último

pago. Si el argumento vf se omite, se supone que el valor es 0 (por ejemplo, el valor futuro de un

préstamo es 0). Si desea ahorrar 50.000 $ para pagar un proyecto especial en 18 años, 50.000 $

sería el valor futuro. De esta forma, es posible hacer una estimación conservadora a cierta tasa de

interés y determinar la cantidad que deberá ahorrar cada mes. Si se omite el argumento vf, deberá

incluirse el argumento pago.

Tipo es el número 0 ó 1 e indica cuándo vencen los pagos.

DEFINA TIPO COMO SI LOS PAGOS VENCEN

0 u omitido Al final del período 1 Al inicio del período



New Horizons Inc.

116 | P á g i n a


Veamos un Ejemplo:

1

2

3

4

A B

Datos Descripción

500 Dinero pagado por una póliza de seguros al final de cada mes

8% Interés ganado por el dinero pagado

20 Número de años durante los cuales se efectuarán los pagos

Fórmula Descripción (resultado) =VA(A3/12; 12*A4; A2; ; 0) Valor actual de una anualidad con los términos anteriores (-59.777,15)

El resultado es negativo porque representa el dinero que se pagaría, un flujo de caja saliente. Si le

piden (60,000) para la anualidad, determinará que ésta no es una buena inversión, puesto que el

valor actual de la anualidad (59,777.15) es inferior a lo que tendría que pagar.

NOTA

La tasa de interés se divide por 12 para obtener una tasa mensual. Los años de duración del

préstamo se multiplican por 12 para obtener el número de pagos Mensual.



New Horizons Inc.

117 | P á g i n a


Tiempo Para Amortizar una Deuda

Devuelve el número de períodos de una inversión basándose en los pagos periódicos constantes y

en la tasa de interés constante.

Sintaxis

NPER(tasa; pago; va; vf; tipo)

Para obtener una descripción más completa de los argumentos de NPER y más información acerca

de las funciones de anualidades, vea VA.

Tasa es la tasa de interés por período.

Pago es el pago efectuado en cada período; debe permanecer constante durante la vida de la

anualidad. Por lo general, pago incluye el capital y el interés, pero no incluye ningún otro arancel o

impuesto.

Va es el valor actual o la suma total de una serie de futuros pagos.

Vf es el valor futuro o un saldo en efectivo que desea lograr después de efectuar el último pago.

Si el argumento vf se omite, se asume que el valor es 0 (por ejemplo, el valor futuro de un

préstamo es 0).

Tipo es el número 0 ó 1 e indica el vencimiento de los pagos.

DEFINA TIPO COMO SI LOS PAGOS VENCEN

0 u omitido Al final del período

1 Al inicio del período



New Horizons Inc.

118 | P á g i n a


Ejemplo:

Imagine que tiene un préstamo personal de $. 2,500.00 acuerda pagar $150 al mes con una tasa

de Interés Anual del 3%, ¿Cuánto Tiempo tardar en saldar esa deuda?

A B 1 Interes 3%

2 Pago Mensual 150.00 3 Monto $. 2,500.00

4 Tiempo (?) =Nper(b1/12,-b2,b3)

El resultado de esto es 17,0451 (este resultado es 17 Meses y días)

Calculo de Tasa que se paga en un Préstamo Calcula el tipo de interés fraccionado (tipo de interés nominal dividido por los plazos de pago

anuales).

TASA(nper, va, vf, tipo, estimar)

nper: Número total de períodos de pago en la vigencia de la operación financiera.

pago: Es el importe de la renta periódica y constante durante el plazo de la operación

financiera.

va: es el valor actual de las rentas periódicas y constantes o el capital inicial. Es

importante destacar, que los pagos se deben poner en negativo o sino nos dará un error

#NUM.

vf: es el capital final o saldo residual después de efectuar el último pago.

tipo: es la modalidad de pago. 0 o dato omitido significan pos pagable, 1 pre pagable.

Estimar: es el valor de estimación de la tasa de interés periódica que tomo como

referencia la función TASA en el cálculo interactivo para hallar una solución a la ecuación.



New Horizons Inc.

119 | P á g i n a


CALCULO PARA DEPRECIACIÓN

MÉTODO DIRECTO

Para el cálculo de una depreciación con una cuota anual permanente usaremos la Función SLN.

Sintaxis

SLN (costo;valor_residual;vida)

Costo es el costo inicial del bien (El valor de costo del Activo).

Valor_residual es el valor al final de la depreciación (también conocido como valor residual del

bien).

Vida es el número de períodos durante los cuales se produce la depreciación del bien (también

conocido como la vida útil del bien).

Ejemplo

1

2

3

4

A B

Datos Descripción

30.000 Costo

7.500 Valor residual

10 Años de vida útil

Fórmula Descripción (resultado) =SLN(A2;A3;A4) Depreciación permitida para cada año (2.250)



New Horizons Inc.

120 | P á g i n a


MÉTODO SUMA DE DÍGITOS

Para este tipo de método Usaremos la Función SYD.

SYD Devuelve la depreciación por suma de dígitos de los años de un bien durante un período

específico.

Sintaxis

SYD (costo;valor_residual;vida;período)

Costo es el costo inicial del bien.

Valor_residual es el valor al final de la depreciación (también conocido como valor residual del

bien).

Vida es el número de períodos durante los cuales se produce la depreciación del bien (también

conocido como la vida útil del bien). Período es el período, que debe utilizar las mismas unidades que el argumento vida.

Observación:

SYD se calcula como:



New Horizons Inc.

121 | P á g i n a


Ejemplo

A B

1 Datos Descripción

2 30.000 Costo inicial

3 7.500 Valor residual

4 10 Vida en años

5 Fórmula Descripción (resultado)

=SYD(A2;A3;A4;1) Depreciación anual permitida para el primer año (4.090,91)

=SYD(A2;A3;A4;10) Depreciación anual permitida para el décimo año (409,09)

MEDIA GEOMETRICA La Función MEDIA.GEOM, devuelve la media geométrica de una matriz o de un rango de datos

positivos. Por ejemplo, es posible utilizar la función MEDIA.GEOM para calcular la tasa de

crecimiento promedio, dado un interés compuesto por tasas variables.

Sintaxis

MEDIA.GEOM(número1;número2; ...)

Número1, número2, ... son de 1 a 30 argumentos cuya media desea calcular. También puede

utilizar una matriz única o una referencia matricial en lugar de argumentos separados con punto y

coma.

Observaciones

� Los argumentos deben ser números o nombres, matrices o referencias que contengan

números.

� Si el argumento matricial o de referencia contiene texto, valores lógicos o celdas vacías, estos

valores se pasan por alto; sin embargo, se incluirán las celdas con el valor cero.

� Si uno de los puntos de datos ≤ 0, MEDIA.GEOM devuelve el valor de error #¡NUM!

� La ecuación para la media geométrica es:



New Horizons Inc.

122 | P á g i n a


Ejemplo:

A

Datos

4

5

8

7

11

4

3

Fórmula Descripción (Resultado)

=MEDIA.GEOM(A2:A8) Media geométrica del conjunto de datos anterior (5,476987)

1

2

3

4

5

6

7

8



New Horizons Inc.

123 | P á g i n a


TASA INTERNA DE RETORNO

Devuelve la tasa interna de retorno de los flujos de caja representados por los números del

argumento valores. Estos flujos de caja no tienen por que ser constantes, como es el caso en una

anualidad. Sin embargo, los flujos de caja deben ocurrir en intervalos regulares, como meses o

años. La tasa interna de retorno equivale a la tasa de interés producida por un proyecto de

inversión con pagos (valores negativos) e ingresos (valores positivos) que ocurren en períodos

regulares.

Sintaxis

TIR(valores;estimar)

� Valores es una matriz o una referencia a celdas que contienen los números para los

cuales desea calcular la tasa interna de retorno.

� El argumento valores debe contener al menos un valor positivo y uno negativo para

calcular la tasa interna de retorno.

� TIR interpreta el orden de los flujos de caja siguiendo el orden del argumento valores.

Asegúrese de introducir los valores de los pagos e ingresos en el orden correcto.

� Si un argumento matricial o de referencia contiene texto, valores lógicos o celdas vacías,

esos valores se pasan por alto.

� Estimar es un número que el usuario estima que se aproximará al resultado de TIR.

� Microsoft Excel utiliza una técnica iterativa para el cálculo de TIR. Comenzando con el

argumento estimar, TIR reitera el cálculo hasta que el resultado obtenido tenga una

exactitud de 0,00001%. Si TIR no llega a un resultado después de 20 intentos, devuelve el

valor de error #¡NUM!

� En la mayoría de los casos no necesita proporcionar el argumento estimar para el cálculo

de TIR. Si se omite el argumento estimar, se supondrá que es 0,1 (10%).

� Si TIR devuelve el valor de error #¡NUM!, o si el valor no se aproxima a su estimación,

realice un nuevo intento con un valor diferente de estimar.



New Horizons Inc.

124 | P á g i n a


Observaciones

TIR está íntimamente relacionado a VNA, la función valor neto actual. La tasa de retorno calculada

por TIR es la tasa de interés correspondiente a un valor neto actual 0 (cero). La fórmula siguiente

demuestra la relación entre VNA y TIR:

VNA(TIR(B1:B6),B1:B6) es igual a 3,60E-08 [Dentro de la exactitud del cálculo TIR, el valor 3,60E-08

es en efecto 0 (cero).]

Ejemplo

A B

1 Datos Descripción

2 -70.000 Costo inicial de un negocio

3 12.000 Ingresos netos del primer año

4 15.000 Ingresos netos del segundo año

5 18.000 Ingresos netos del tercer año

6 21.000 Ingresos netos del cuarto año

7 26.000 Ingresos netos del quinto año

8 Fórmula Descripción (Resultado) =TIR(A2:A6) Tasa interna de retorno de la inversión después de cuatro años (-2%) =TIR(A2:A7) Tasa interna de retorno después de cinco años (9%) =TIR(A2:A4;-10%) Para calcular la tasa interna de retorno de la inversión después de dos

años, tendrá que incluir una estimación (-44%)



New Horizons Inc.

125 | P á g i n a


VALOR PRESENTE NETO La función a utilizar es VNA.

Descripción

Calcula el valor neto presente de una inversión a partir de una tasa de descuento y una serie de

pagos futuros (valores negativos) e ingresos (valores positivos).

Sintaxis

� VNA(tasa;valor1;[valor2];...)

� La sintaxis de la función VNA tiene los siguientes argumentos:

� Tasa ( Obligatorio). La tasa de descuento a lo largo de un período.

� Valor1; valor2... Valor1 es obligatorio, los valores siguientes son opcionales.

� Valor1; valor2; ... deben tener la misma duración y ocurrir al final de cada período.

VNA usa el orden de valor1; valor2; ... para interpretar el orden de los flujos de caja. Asegúrese de

escribir los valores de los pagos y de los ingresos en el orden adecuado.

Los argumentos que son celdas vacías, valores lógicos o representaciones textuales de números,

valores de error o texto que no se pueden traducir a números, se pasan por alto.

Si un argumento es una matriz o una referencia, solo se considerarán los números de esa matriz o

referencia. Se pasan por alto las celdas vacías, valores lógicos, texto o valores de error de la matriz

o de la referencia.

Observaciones

� La inversión VNA comienza un período antes de la fecha del flujo de caja de valor1 y

termina con el último flujo de caja de la lista. El cálculo VNA se basa en flujos de caja

futuros. Si el primer flujo de caja se produce al principio del primer período, el primer

valor se debe agregar al resultado VNA, que no se incluye en los argumentos valores. Para

obtener más información, vea los siguientes ejemplos.

� Si n es el número de flujos de caja de la lista de valores, la fórmula de VNA es:



New Horizons Inc.

126 | P á g i n a


� VNA es similar a la función VA (valor actual). La principal diferencia entre VA y VNA es que

VA permite que los flujos de caja comiencen al final o al principio del período. A diferencia

de los valores variables de flujos de caja en VNA, los flujos de caja en VA deben

permanecer constantes durante la inversión. Para obtener más información acerca de

anualidades y funciones financieras, vea VA.

� VNA también está relacionado con la función TIR (tasa interna de retorno). TIR es la tasa

para la cual VNA es igual a cero: VNA(TIR(...); ...) = 0.

� Ejemplo

� EJEMPLO 1

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

A B

Datos Descripción

10% Tasa anual de descuento

-10.000 Costo inicial de la inversión un año después de la fecha actual

3.000 Rendimiento del primer año

4.200 Rendimiento del segundo año

6.800 Rendimiento del tercer año

Fórmula Descripción (resultado)

=VNA(A2;A3;A4;A5;A6) Valor neto actual de esta inversión (1.188,44)

� En el ejemplo anterior se incluye el costo inicial de 10.000 $ como uno de los valores

porque el pago se produce al final del primer período.

� NOTA Para ver el resultado como un número, seleccione la celda y, en el grupo Número

de la pestaña Inicio, haga clic en la flecha que aparece junto a Formato de número y, a

continuación, en Número.



New Horizons Inc.

127 | P á g i n a


EJEMPLO 2

1

2

3

4

5

6

7

8

9

10

11

A B

Datos Descripción

8% Tasa anual de descuento. Esto puede representar la

tasa de inflación o la tasa de interés de una inversión de

la competencia.

-40.000 Costo inicial de la inversión

8.000 Rendimiento del primer año

9.200 Rendimiento del segundo año

10.000 Rendimiento del tercer año

12.000 Rendimiento del cuarto año

14.500 Rendimiento del quinto año

Fórmula Descripción (resultado)

=VNA(A2;A4:A8)+A3 Valor neto actual de esta inversión (1.922,06)

=VNA(A2;A4:A8;-

9000)+A3

Valor neto actual de esta inversión, con una pérdida en

el sexto año de 9000 (-3.749,47)

� En el ejemplo anterior no se incluye el costo inicial de 40.000 $ como uno de los valores

porque el pago se produce al principio del primer período.

� El valor del resultado depende del número de posiciones decimales que se usa en el

formato de número de la celda que contiene la fórmula.

diplomado ejecutivo de excel

Technology