Manual 1
-
Upload
jeffersson-munoz -
Category
Documents
-
view
46 -
download
0
Transcript of Manual 1
Contenido
ANÁLISIS DE DATOS I.............................................................................................................. 1
FORMATO COMO TABLA .............................................................................................................. 2 Lista de datos ...................................................................................................................... 2 Estructura de una lista de datos.......................................................................................... 2 Uso de las tablas de datos .................................................................................................. 2 Ordenar datos ..................................................................................................................... 8
FILTRO DE DATOS..................................................................................................................... 14 Filtro personalizado ........................................................................................................... 16 Filtro múltiple ..................................................................................................................... 17 Quitar un Filtro................................................................................................................... 18 Utilizar estilos rápidos y crear estilos de formato de tabla ................................................ 18
HERRAMIENTAS DE DATOS ........................................................................................................ 25 Texto en columnas ............................................................................................................ 25 Quitar duplicados .............................................................................................................. 30
VALIDACIÓN DE DATOS ............................................................................................................. 32 Restringir el ingreso de datos ........................................................................................... 32 Validar con intervalo de números...................................................................................... 32
ANÁLISIS Y SI........................................................................................................................... 37 Uso del administrador de escenarios ................................................................................ 38 Buscar Objetivo ................................................................................................................. 44 Tabla de datos................................................................................................................... 45 Tabla de datos de una variable ......................................................................................... 45 Tablas de datos de dos variables ..................................................................................... 48
CONSOLIDACIÓN DE DATOS Y REFERENCIA 3D .......................................................................... 49 Cuando consolidar datos de varias hojas ......................................................................... 49 Consolide por fórmula ....................................................................................................... 50
REFERENCIAS 3D .................................................................................................................... 55 CUESTIONARIOS ...................................................................................................................... 58
FUNCIONES FINANCIERAS .................................................................................................... 59
GENERALIDADES SOBRE EXCEL EN EL MUNDO DE LOS NEGOCIOS ............................................... 60 USO DE FUNCIONES FINANCIERAS ............................................................................................. 60
Consideraciones del índice de inflación ............................................................................ 60 Tasa nominal ..................................................................................................................... 62 Tasa efectiva (i’) ................................................................................................................ 63 Préstamo de un banco: Función PAGO ........................................................................... 64 Valor presente de la inversión/pagos futuros: Función VA ............................................... 68 Valor futuro de la inversión: Función VF ........................................................................... 70 Número de pagos de inversión: Nper ............................................................................... 73 Función TASA ................................................................................................................... 75 Tasa interna de retorno (TIR)............................................................................................ 76
DESARROLLO DE PROYECTOS CON FUNCIONES FINANCIERAS ..................................................... 78 Comprar frente a un leasing.............................................................................................. 78 Calcular la tasa interna de devolución para un flujo de liquidez no periódico .................. 82 Calcular la tasa interna de devoluciones múltiples ........................................................... 83
CUESTIONARIOS ...................................................................................................................... 84
ANÁLISIS DE DATOS II........................................................................................................... 85
HERRAMIENTAS DE ANÁLISIS ESTADÍSTICO................................................................................ 87 Tendencia lineal de ajuste perfecto automáticamente...................................................... 87 Tendencia geométrica de forma automática..................................................................... 88 Tendencia lineal o geométrica de forma manual .............................................................. 89 Agregar una línea de tendencia a un gráfico .................................................................... 90 Proyectar valores .............................................................................................................. 93
Uso de cuadros de Histogramas para el cálculo de frecuencias individuales y acumulativas ..................................................................................................................... 96
DEFINICIÓN Y RESOLUCIÓN DE PROBLEMAS CON SOLVER ......................................................... 101 Generalidades sobre Solver............................................................................................ 101 Carga del programa de complemento Solver ................................................................. 101 Cómo configura Solver .................................................................................................... 104 Modificación de forma de búsqueda de soluciones en Solver ........................................ 105 Desarrollo de casos tipo utilizando Solver ...................................................................... 106
CUESTIONARIOS .................................................................................................................... 108
FORMULARIOS ...................................................................................................................... 109
ACTIVACIÓN DE LA FICHA PROGRAMADOR ................................................................................ 110 DISEÑO DE FORMULARIOS ..................................................................................................... . 111
Mostrar y ocultar elementos ............................................................................................ 111 Trabajando con Controles ............................................................................................... 112 Uso de los controles de formulario.................................................................................. 113
CUESTIONARIOS .................................................................................................................... 129
MACROS ................................................................................................................................. 130
MACROS................................................................................................................................ 131 Definición......................................................................................................................... 131 Editor de Visual Basic ..................................................................................................... 131 Para que se utilizan las Macros ...................................................................................... 132 Macros VBA con Excel .................................................................................................... 133 Ventajas........................................................................................................................... 133
SEGURIDAD DE MACROS ........................................................................................................ 134 Los Macrovirus ................................................................................................................ 134 Ayudar a proteger archivos de virus en macros ............................................................. 135 Firmas digitales ............................................................................................................... 136 Lista de editores de confianza ........................................................................................ 137 Advertencias acerca de plantillas y complementos instalados ....................................... 137
GRABAR UNA NUEVA MACRO .................................................................................................. 138 EDITAR UNA MACRO UTILIZANDO VISUAL BASIC ....................................................................... 140 ELIMINAR MACROS ................................................................................................................. 141 REFERENCIAS RELATIVAS....................................................................................................... 141
Diferencia entre celda relativa y absoluta ....................................................................... 142
USO DE MACROS EN FORMULARIOS ........................................................................................ 142 PLANTILLAS CON FORMULARIOS Y MACROS ............................................................................. 148
Crear Plantillas personalizadas ....................................................................................... 150 Utilizar Plantillas personalizadas..................................................................................... 151
DESARROLLO DE PROYECTOS CON MACRO Y FORMULARIOS..................................................... 151 CUESTIONARIOS .................................................................................................................... 158
PROGRAMACIÓN CON VBA ................................................................................................. 159
INTRODUCCIÓN AL VISUAL BASIC ............................................................................................ 160 FUNDAMENTOS DE PROGRAMACIÓN VBA ................................................................................ 162
Estructura Secuencial. .................................................................................................... 162 Estructura Condicional. ................................................................................................... 163 Sentencia selección-caso................................................................................................ 165 Estructura Repetitiva. ...................................................................................................... 167
LA VENTANA DEL EDITOR DE VISUAL BASIC .............................................................................. 170 Terminología de Visual Basic.......................................................................................... 171 Programación por eventos .............................................................................................. 172 Convenciones para los nombres de los objetos ............................................................. 172 Formularios ..................................................................................................................... 173 Editando Código .............................................................................................................. 174
CREACIÓN Y USO DE PROCEDIMIENTOS ................................................................................... 175 Procedimientos................................................................................................................ 175 Procedimientos de Evento .............................................................................................. 175
Procedimientos Generales .............................................................................................. 176 Procedimientos Sub ........................................................................................................ 176 Procedimientos Function ................................................................................................. 177 Ámbito de las variables. .................................................................................................. 177 Formas de declaración de variables en un proyecto VB. ............................................... 178 Sentencia Dim ................................................................................................................. 178 Sentencia PRIVATE ........................................................................................................ 178 Sentencia PUBLIC .......................................................................................................... 178 Sentencia GLOBAL ......................................................................................................... 178 Sentencia STATIC........................................................................................................... 178 Resumen de declaración de variables ............................................................................ 179 Forma de conocer el tipo de una variable. Función TypeName ..................................... 179
OBJETOS PROPIEDADES, MÉTODOS Y EVENTOS ....................................................................... 180 Control Etiqueta (Label) .................................................................................................. 180 Control Cuadro de Texto (Textbox)................................................................................. 181 Control Botón de Comando (Commandbutton) .............................................................. 181 Estableciendo Propiedades ............................................................................................ 182
DEFINICIÓN DE VARIABLES, TIPOS DE DATOS Y CONSTANTES .................................................... 183 Option Explicit .................................................................................................................. 183 Tipos de Variables........................................................................................................... 183 Declaración de variables ................................................................................................. 184 Tipos de variables ........................................................................................................... 184 a. Variables Alfanuméricas.............................................................................................. 185 b. Variable Numéricas ..................................................................................................... 185 c. Variable Date ............................................................................................................... 186 d. Variable Boolean ......................................................................................................... 186 Constantes ...................................................................................................................... 186 Operadores ..................................................................................................................... 187
CONSTRUCCIONES: IF – THEN, SELECT CASE ......................................................................... 188 Condicional simple. If .. then ........................................................................................... 188 Condicional doble If...Then...Else ................................................................................... 189 Condicional múltiple Select Case.................................................................................... 190
BUCLES FOR … NEXT, WHILE .. DO ........................................................................................ 192 Do...Loop ......................................................................................................................... 193 For...Next......................................................................................................................... 194 For Each...Next ............................................................................................................... 194
TRABAJAR CON RANGOS DE CELDA ......................................................................................... 196 Utilizando la notación A1 ................................................................................................. 196 Hacer referencia a celdas utilizando números de índice ................................................ 196 Hacer referencia a filas y columnas ................................................................................ 197 Hacer referencia a celdas utilizando una notación abreviada ........................................ 198 Hacer referencia a rangos con nombre........................................................................... 198 Hacer referencia a un rango con nombre ....................................................................... 198 Ejecutar un bucle en las celdas de un rango con nombre .............................................. 199 Hacer referencia a celdas en relación con otras celdas ................................................. 200 Hacer referencia a celdas usando un objeto Range ....................................................... 200 Hacer referencia a todas las celdas de la hoja de cálculo .............................................. 201 Hacer referencia a varios rangos .................................................................................... 201 Usar la propiedad Range ............................................................................................... . 201 Usar el método Union...................................................................................................... 201 Usar la propiedad Areas.................................................................................................. 202 Bucles en un rango de celdas ......................................................................................... 202 Seleccionar y activar celdas............................................................................................ 203 Usar el método Select y la propiedad Selection ............................................................. 204 Seleccionar celdas en la hoja de cálculo activa .............................................................. 204 Activar una celda en una selección................................................................................. 205 Trabajar con rangos 3D................................................................................................... 205 Trabajar con la celda activa ............................................................................................ 206 Mover la celda activa....................................................................................................... 206
Seleccionar las celdas que rodean la celda activa ......................................................... 207
TRABAJO CON LIBROS Y HOJAS ............................................................................................... 207 Hacer referencia a hojas por número de índice .............................................................. 207 Hacer referencia a hojas por su nombre ......................................................................... 207
AÑADIR MÓDULOS VBA .......................................................................................................... 208 TRABAJAR CON USERFORMS .................................................................................................. 208 USO DE CONTROLES DE FORMULARIO...................................................................................... 209
Control Marco (Frame) .................................................................................................... 209 Control Casilla de Verificación (CheckBox) .................................................................... 209 Control Botón de Opción (OptionButton) ........................................................................ 210 Control Cuadro de Lista (ListBox) ................................................................................... 211 Control Cuadro Combinado (ComboBox) ....................................................................... 212
FUNCIONES VBA INPUTBOX, MSGBOX ................................................................................... 215 Función MsgBox() ........................................................................................................... 215 Función InpuBox() ........................................................................................................... 215
METODO GETOPENFILENAME , GETSAVEASFILENAME ............................................................ 215 Función GetOpenfilename () ........................................................................................... 215 Función GetSaveAsFileName ......................................................................................... 217
CREAR Y ABRIR LIBRO ............................................................................................................ 217 Crear un libro nuevo ........................................................................................................ 217
CUESTIONARIOS .................................................................................................................... 218 EJERCICIOS UTILIZANDO MACRO, FORMULARIO Y VBA ............................................................ 219
FUNCIONES PERSONALIZADAS ......................................................................................... 230
USO DE MÓDULOS PARA FUNCIONES ....................................................................................... 231 Ventajas........................................................................................................................... 231 Características ................................................................................................................ 231
USO DE ARGUMENTOS EN LAS FUNCIONES............................................................................... 232 EJECUTAR UNA FUNCIÓN ........................................................................................................ 233
Recursividad.................................................................................................................... 234
CONTROLES ACTIVE X ........................................................................................................... 235 CUESTIONARIOS .................................................................................................................... 246
Capítulo
Análisis de Datos I
En este capítulo trataremos:
Herramientas de datos
Consolidación y referencias 3D
¿Qué se necesita para ser el mejor?
Concentración. Disciplina. Una ilusión.
F. Griffith
Medalla de oro olímpica
SENATI-Computación e Informática 1
Microsoft Office Excel 2007
Formato como tabla Excel es primariamente una hoja de cálculo, pero además de ello tiene capacidad para
analizar tablas de datos, tales como stock, clientes, planillas, monitorear cuentas y
ventas.
La combinación de una serie de funciones hace al Excel una excelente herramienta de
análisis para negocios y administración de sistemas.
Excel usa el término Lista para referirse a información almacenada en filas y columnas,
si usted está familiarizado con versiones anteriores de Excel, entonces es probable que
este más familiarizado con el término Base de Datos.
Lista de datos
Una lista de datos es un conjunto de registro formado por filas y columnas.
Las filas representan los registros de datos
Las columnas representan los campos de datos
Estructura de una lista de datos
Campo1 Campo2 Campo3 Campo4 Campo5
Registros
Uso de las tablas de datos
Una vez que se han ingresado los nombres de los campos y la fila de datos iníciales
a su lista, podemos usar cualquiera de los siguientes métodos para ingresar datos:
Uso de ingreso automático de datos usando la ficha de datos
Ingresar los datos directamente en la hoja dentro de las celdas vacías
2 SENATI-Computación e Informática
Paso a Paso: Agregar el comando formulario a la barra de herramientas
de acceso rápido
1. Hacer clic en el botón Botón Office
2. Hacer clic en el botón , se presenta el cuadro de diálogo “Opciones Excel”.
Hacer clic en la opción Personalizar
3. Se presenta el cuadro de diálogo “Personalizar la barra de herramientas de acceso rápido”.
SENATI-Computación e Informática 3
Microsoft Office Excel 2007
Seleccionar “Todos los comandos”
Seleccionar “Formulario …”
4. Hacer clic en el botón Aceptar para terminar
Paso a Paso: Agregar datos
1. Abrir el archivo “1 BD Pedidos”
2. Adicionaremos registros a la base de datos de pedidos, la cual mostramos a
continuación.
4 SENATI-Computación e Informática
s de I
3. Seleccionar al celda A2
4. Hacer clic en el botón Formulario
5. Se presenta la ficha formulario mostrando los registros de la base de datos “1
BD Pedidos”
6. Para adicionar registros hacer clic en el botón
7. Se presenta un formulario en blanco, ingrese el siguiente registro
SENATI-Computación e Informática 5
Microsoft Office Excel 2007
8. Hacer clic en el botón para terminar
Paso a Paso: Eliminar datos
1. Abrir el archivo “1 BD Pedidos”
2. Eliminaremos el registro de pedido realizado por el cliente SoftPlus
3. Seleccionar al celda A2
4. Hacer clic en el botón Formulario
5. Se presenta la ficha formulario mostrando los registros de la base de datos “1
BD Pedidos”
6 SENATI-Computación e Informática
s de datos II
6. Primero localizaremos el registro, para ello hacer clic en el botón
Ingrese “SoftPlus”, nombre del cliente a
localizar
Hacer clic en el botón “Buscar siguiente”
7. Se mostrará el registro de pedido de la empresa SoftPlus
8. Hacer clic en el botón , Excel preguntará si desea eliminar permanentemente este registro.
SENATI-Computación e Informática 7
Microsoft Office Excel 2007
9. Hacer clic en el botón
10. Hacer clic en el botón para terminar
Ordenar datos
La ordenación de los registros nos permite ver y comprender mejor los datos, así como a
organizarlos y encontrarlos más fácilmente y a tomar decisiones más eficaces.
Se puede ordenar los registros por: Texto, números, fechas u horas, color de celda, color
de fuente o icono, por una lista personalizada, filas, por más de una columna o fila,
ordenar una columna en un rango de celdas sin afectar a las demás.
Paso a Paso: Ordenar por un campo texto
1. Abrir el archivo “1 BD Pedidos”
2. Ordenaremos por el campo de datos “Cliente” alfabéticamente en orden
ascendente
3. Hacer clic en la celda A2
4.
Hacer Clic en la ficha
, desplace su visión al grupo Ordenar y filtrar
Hacer clic en el botón
“Ordenar de A a Z”
5. Se mostrarán sus registros ordenados por el campo cliente
8 SENATI-Computación e Informática
s de datos II
,
.
I
Paso a Paso: Ordenar por un campo texto distinguiendo las mayúsculas de las minúsculas
1. Abrir el archivo “1 BD Pedidos”
2. Ordenaremos por el campo de datos “NombreProducto” alfabéticamente en
orden ascendente
3. Hacer clic en la celda A2
4.
Hacer clic en la ficha
desplace su visión al grupo Ordenar y filtrar
Hacer clic en el botón
“Ordenar”
5. Se muestra el siguiente cuadro de diálogo
6. Hacer clic en el botón
7. Del cuadro de diálogo presentado activar
la casilla de verificación “Distinguir
mayúsculas de minúscula”
SENATI-Computación e Informática 9
,
Microsoft Office Excel 2007
8. Hacer clic en el botón para continuar. Se mostrará el cuadro de diálogo ordenar
Seleccionar columna “NombreProducto”
9. Hacer clic en el botón para terminar. Se mostrarán los registros
ordenados por el campo NombreProducto, distinguiendo las mayúsculas de las
minúsculas.
Paso a Paso: Ordenar por un campo numérico
1. Abrir el archivo “1 BD Pedidos”
2. Ordenaremos por el campo de datos “Cantidad” en orden descendente
3. Hacer clic en la celda A2
4.
Hacer clic en la ficha
desplace su visión al grupo Ordenar y filtrar
10 SENATI-Computación e Informática
s de datos II
Hacer clic en el botón “Ordenar”
5. Se presenta el siguiente cuadro de diálogo
Hacer clic para seleccionar la
columna “Cantidad”
Seleccionar como criterio de ordenación “De mayor a menor”
6. Hacer clic en el botón para terminar, se mostrarán los
registros ordenados por el campo cantidad
Paso a Paso: Ordenar por fecha u hora
1. Abrir el archivo “1 BD Pedidos”
SENATI-Computación e Informática 11
,
Microsoft Office Excel 2007
2. Ordenaremos por el campo de datos “Fecha Pedido” en orden descendente
3. Hacer clic en la celda A2
4.
Hacer clic en la ficha
desplace su visión al grupo Ordenar y filtrar
Hacer clic en el botón “Ordenar”
5. Se presenta el siguiente cuadro de diálogo
Seleccionar la
columna “FechaPedido”
Seleccione su criterio de ordenación
6. Para terminar hacer clic en el botón se muestran los registros ordenados por fecha de pedido
Ordenar por color de celda, color de fuente o icono
Si ha aplicado formato manual o condicionalmente a un rango de celdas o a
una columna de tabla, por color de celda o color de fuente, también puede
ordenar por estos colores. Además, puede ordenar por un conjunto de iconos
creado mediante un formato condicional.
12 SENATI-Computación e Informática
s de datos II
,
Paso a Paso: Ordenar por más de una columna
1. Abrir el archivo “1 BD Pedidos”
2. Ordenaremos por el campo: cliente, fechapedido, nombreproducto en orden
descendente
3. Hacer clic en la celda A2
4.
Hacer clic en la ficha
desplace su visión al grupo Ordenar y filtrar
Hacer clic en el botón “Ordenar”
5. Se presenta el siguiente cuadro de diálogo
Seleccionar columna “Cliente” como primer criterio de ordenación
Seleccionar columna “NombreProducto” como tercer
criterio de ordenación
Seleccionar columna “FechaPedido” como segundo
criterio de ordenación
6. Hacer clic en el botón para terminar. Se muestran los registros ordenados por cliente, fechapedido, nombreproducto en orden descendente.
SENATI-Computación e Informática 13
,
Microsoft Office Excel 2007
Filtro de datos El filtrado de datos constituye un método fácil y rápido para encontrar subconjuntos de
datos en una lista y trabajar con ellos.
Cuando se filtra una lista sólo visualizará las filas que cumplen un conjunto de
condiciones de búsqueda llamado criterios.
A diferencia de la ordenación, la filtración no reorganiza las listas. La filtración oculta
provisionalmente las filas que no desea mostrar.
Cuando Excel filtra las filas, la hoja de cálculo se coloca en el modo de filtración. En
este modo se podrá editar, dar formato, efectuar representaciones gráficas e imprimir la
lista de subconjuntos sin tener que reorganizarla o moverla.
Paso a Paso: Aplicar Autofiltros
1. Abrir el archivo “FILTROS”
2. Utilice la siguiente lista de datos para filtrar registros por sección.
3. Ubicarse en la celda A5.
4. Hacer clic en la ficha botón
5. Ahora simplemente con hacer clic en la lista desplegable podrá filtrar los
registros de datos según sus requerimientos.
14 SENATI-Computación e Informática
s de datos II
Desactive las casillas del 2do.
Al 6to. grado
6. Hacer clic en el autofiltro grado y desactive las casillas del 2do. Al 6to. Grado,
de tal forma que sólo se muestre alumnos del 1er. Grado.
7. Los registros filtrados se muestran como en la gráfica.
SENATI-Computación e Informática 15
Microsoft Office Excel 2007
Filtro personalizado
Se utiliza para especificar condiciones utilizando operadores booleanos.
Paso a Paso: Filtros personalizados
1. Abrir el archivo “FILTROS” o diseñar la hoja de cálculo
Utilice la siguiente lista de datos para filtrar registros donde el monto de la
pensión está entre: 200 y 350.
a. Ubicarse en la celda A5.
b. Hacer clic en la ficha , botón , ,
c. De la lista de opciones presentadas elegir
La opción: Mayor o igual a…
Se presenta el siguiente cuadro de diálogo
2. Ingrese los valores según se muestra en la gráfica, para que sólo se muestre los
alumnos que pagan una pensión que está entre 200 y 350 nuevos soles.
3. Hacer clic en el botón para aplicar el filtro.
16 SENATI-Computación e Informática
s de datos II
,
Pensiones de alumnos con valor entre 200 y 350.
Filtro múltiple
Se utiliza para especificar múltiples condiciones
Paso a Paso: Filtros múltiple
1. Abrir el archivo “FILTROS”
Utilice la siguiente lista de datos para filtrar registros donde:
Procedencia: CIV
Nivel: P
Grado: 1
2. Ubicarse en la celda A5.
3.
Hacer clic en la ficha
botón
4. De la lista de opciones presentadas elegir
Procedencia: CIV
Nivel: P
Grado: 1
SENATI-Computación e Informática 17
,
Microsoft Office Excel 2007
Hacer clic y dejar sólo activado la casilla de verificación P
Hacer clic y dejar sólo activado la casilla de verificación CIV
Hacer clic y dejar sólo activado la casilla de verificación 1
Quitar un Filtro
Para mostrar en su tabla todos los registros, debe quitar los filtros aplicados.
Paso a Paso: Quitar filtros
Hacer clic en la ficha botón
Utilizar estilos rápidos y crear estilos de formato de tabla
Microsoft Office Excel proporciona un gran número de estilos de tabla (o estilos
rápidos) predefinidos que puede utilizar para dar formato rápidamente a una tabla. Si los
estilos de la tabla predefinida no satisfacen sus necesidades, puede crear y aplicar un
estilo de tabla personalizado. Aunque sólo se pueden eliminar los estilos de tabla
personalizados, puede quitar cualquier estilo de tabla para que ya no se aplique a los
datos.
Paso a Paso: Utilizar estilos rápidos de tabla
1. Abrir el archivo “1BD pedidos”. Se muestra la siguiente hoja de cálculo
18 SENATI-Computación e Informática
s de datos II
2. Hacer clic en la celda A2
3. Hacer clic en la ficha
4. Del grupo Estilos seleccionar el comando “Dar formato como tabla”
5. Se presenta un conjunto de estilos prediseñados, categorizados en: Claro,
medio, oscuro.
Seleccionar uno de los estilos
mostrados
6. Luego de elegir un estilo se presenta un cuadro de diálogo
SENATI-Computación e Informática 19
Microsoft Office Excel 2007
7. Verifique que el rango seleccionado es el correcto, active la casilla de verificación “La tabla tiene encabezados”.
8. Finalmente hacer clic en el botón se muestra la tabla con formato
Paso a Paso: Crear estilos de formato de tabla
1. Abrir el archivo “1BD pedidos”. Se muestra la siguiente hoja de cálculo
2. Hacer clic en la celda A2
3. Hacer clic en la ficha
4. Del grupo Estilos seleccionar el comando “Dar formato como tabla”
20 SENATI-Computación e Informática
s de datos II
5. Se presenta un conjunto de estilos prediseñados, categorizados en: Claro, medio, oscuro y al final se presentan dos botones de comando que mostramos
a continuación.
Hacer clic en el botón “Nuevo
estilo de tabla …”
6. Se muestra el cuadro de diálogo Nuevo estilo rápido de tabla
Escriba la palabra “Pedidos”
como nombre de estilo
Aplique los formatos a cada uno de los elementos de la
tabla
Hacer clic en el botón “Formato”
7. Se presenta el cuadro de diálogo formato de celda
SENATI-Computación e Informática 21
Microsoft Office Excel 2007
Hacer clic en la ficha “Bordes”
Elegir donde aplicará los
bordes
Elegir el estilo
de línea
Elegir color
Hacer clic para cambiar a la ficha
“Relleno”
Elegir color de fondo
Elegir efecto de
relleno
8. Para terminar hacer clic en el botón
22 SENATI-Computación e Informática
s de datos II
Paso a Paso: Modificar estilo rápido de la tabla de datos
1. Abrir el archivo “1BD pedidos”. Se muestra la siguiente hoja de cálculo
2. Hacer clic en la celda A2
3. Hacer clic en la ficha
4. Del grupo Estilos seleccionar el comando “Dar formato como tabla”
5. Del grupo de opciones “Personalizada” hacer clic derecho sobre la que desea
modificar
Hacer clic derecho
6. Del grupo de opciones presentadas elegir “Modificar”
Hacer clic sobre la opción “Modificar”
7. Realizar los cambios necesarios
SENATI-Computación e Informática 23
Microsoft Office Excel 2007
Elegir “Primera
franja de fila”
Hacer clic sobre la opción “Formato”
8. Del cuadro de diálogo “Formato de celdas”, elegir la ficha “Relleno”
Seleccionar color de fondo
24 SENATI-Computación e Informática
s de datos II
,
9. Hacer clic en el botón para terminar y veamos cómo queda la tabla. Se muestra con colores intercalados entre fila y fila
10. Repita el procedimiento para modificar cada uno de los elementos de la tabla
Seleccione una de estas opciones, luego hacer clic en
el botón para modificar cada uno de los elementos de la tabla
Herramientas de datos Excel presenta un conjunto de herramientas de datos entre las cuales tenemos: Texto en
columnas, validación de datos, análisis Y si. Las cuales describiremos a continuación.
Texto en columnas
Si copia datos de otro programa y lo pegarlo en Microsoft Excel, Excel puede
comprimir varias columnas de datos a una sola columna. Puede utilizar el comando de
texto en columnas para colocar cada una de las columnas de datos en una celda
(Columna independiente)
Dividir el contenido en función de un delimitador
Utilice este método si los nombres tienen un formato delimitado, como "Nombre
Apellido" (donde el espacio entre Nombre y Apellido es el delimitador) o
"Apellido, Nombre" (donde la coma es el delimitador).
Paso a Paso: Convertir texto en columnas separado por comas
1. Abrir el archivo “Texto en columnas”. Se muestra la siguiente hoja de cálculo
SENATI-Computación e Informática 25
Microsoft Office Excel 2007
2. Seleccione el bloque de celdas A1:A4. Hacer clic en la ficha y visualizar el grupo herramienta de datos.
Hacer clic sobre la
herramienta “Texto en
columnas”
3. Se presenta el asistente para convertir texto en columnas
Debido a que el texto se separa
con comas, elegir la opción
“Delimitados”
4. Hacer clic en el botón
5. En el siguiente cuadro de diálogo se preguntará que separador utilizará entre los
siguientes: Tabulación, punto y coma, coma, espacio, otro.
Elegir la opción “coma”
26 SENATI-Computación e Informática
s de datos II
Elegir coma como separador
6. Hacer clic en el botón
Elegir
“Texto”
Celda a partir de donde se colocaran los datos
7. Hacer clic en el botón para terminar, su hoja queda como se muestra a continuación
SENATI-Computación e Informática 27
Microsoft Office Excel 2007
El texto de dividió en dos columnas, una para el
nombre y la otra para el apellido. Se utilizo como
delimitador de división la coma.
8. Grabar el archivo con el nombre “Texto en columnas dividido 1”
Paso a Paso: Convertir texto en columnas separado por espacios
1. Abrir el archivo “Texto en columnas”. Se muestra la siguiente hoja de cálculo
2. Seleccione el bloque de celdas A1:A4. Hacer clic en la ficha y visualizar el grupo herramienta de datos.
Hacer clic sobre la herramienta “Texto
en columnas”
3. Se presenta el asistente para convertir texto en columnas
Debido a que el texto se separa con
comas, elegir la opción
“Delimitados”
28 SENATI-Computación e Informática
s de datos II
4. Hacer clic en el botón
5. En el siguiente cuadro de diálogo se preguntará que separador utilizará entre los
siguientes: Tabulación, punto y coma, coma, espacio, otro.
Elegir la opción “Espacio”
Elegir “Espacio” como separador
6. Hacer clic en el botón
Elegir
“Texto”
Celda a partir de donde se colocaran
los datos
SENATI-Computación e Informática 29
Microsoft Office Excel 2007
7. Hacer clic en el botón para terminar, su hoja queda como se muestra a continuación
El texto de dividió en seis columnas
8. Grabar el archivo con el nombre “Texto en columnas dividido 2”
Quitar duplicados
Es posible eliminar valores duplicados de una lista utilizando la herramienta quitar
duplicados.
Paso a Paso: Quitar duplicados
1. Abrir el archivo “1 Quitar duplicados”. Se muestra la siguiente hoja de
cálculo
2. Ordenar la lista por el campo que desea eliminar los datos duplicados
3. Hacer clic en la ficha
Hacer clic en el botón Ordenar
30 SENATI-Computación e Informática
s de datos II
4. Se presenta el cuadro de diálogo ordenar
Hacer clic para seleccionar Nombre como columna a
ordenar
Hacer clic para activar la casilla indicando que si
contamos con encabezados
5. Hacer clic en el botón “Aceptar” para terminar con la ordenación
6. Hacer clic en la ficha
Hacer clic en el botón “Quitar duplicados
7. Se presenta el cuadro de diálogo “Quitar duplicados”
Hacer clic para indicar que se eliminan duplicados de la columna “Nombre”
Hacer clic para activar la casilla “Mis datos
tienen encabezados”
8. Hacer clic en el botón para terminar. Excel envía un mensaje que indica que se eliminaron 4 valores duplicados
SENATI-Computación e Informática 31
Microsoft Office Excel 2007
9. Su tabla queda como se muestra a continuación.
Validación de datos Si desea asegurarse de que se introducen los datos correctos en una hoja de cálculo,
puede especificar qué datos son válidos para cada celda o cada rango de celdas. Puede
restringir los datos a un tipo determinado (como números enteros, números decimales o
texto) y definir límites en las entradas válidas. Puede especificar una lista de entradas
válidas o limitar el número de caracteres en las entradas.
Restringir el ingreso de datos
Cuando quiera validar una celda o un conjunto de celdas tendrá que establecer un
criterio para la validación de datos, especificar un mensaje de entrada de datos con este
podrá indicar que tipo de datos se podrá ingresar y un mensaje de error, para indicar al
usuario que cometió un error de ingreso de datos.
Validar con intervalo de números
Se puede restringir el ingreso de datos a las celdas, de tal forma que solamente acepte
como datos un intervalo numérico.
Paso a Paso: Validar intervalo numérico
1. Abrir el archivo “1 Validar datos”
32 SENATI-Computación e Informática
s de datos II
,
2. Seleccionar las celdas C4:C13 para agregarle una restricción, que acepte sólo como datos los números: 1 hasta 120.
3. Elegir la ficha
Hacer clic en la ficha
“Configuración”
Elegir “Números enteros”
Elegir “Entre”
Ingresar “1” como mínimo y “120” como máximo.
SENATI-Computación e Informática 33
Microsoft Office Excel 2007
a. Seleccionar la opción Permitir: “Número entero”
b. Datos: Entre
c. Mínimo: 1 Máximo: 120
4. Cambiar a la pestaña Mensaje de entrada, para definir un mensaje que se
mostrará cuando intente ingresar un dato.
Hacer clic en la ficha “Mensaje de entrada”
Escribir como título “Edad 1 a 120”
Escribir mensaje “Ingrese un número entre 1 y 120”
5. Finalmente hacer un clic en el botón
Ahora a propósito ingrese valor menos a 1 o mayores a 120 y observe lo que
sucede.
Paso a Paso: Validar dato fecha
1. Abrir el archivo “1 Validar datos”
34 SENATI-Computación e Informática
s de datos II
,
2. Seleccionar las celdas E4:E13 para agregarle una restricción, que acepte sólo
como datos fechas: entre 1/1/1965 hasta 1/1/1980
3. Elegir la ficha
Hacer clic en la ficha configuración
Elegir “Fecha”
Elegir “Entre”
Ingresar “1/1/1965” como mínimo y “1/1/1980” como máximo.
4. Cambiar a la pestaña Mensaje de entrada, para definir un mensaje que se
mostrará cuando intente ingresar un dato.
Hacer clic en la ficha “Mensaje de entrada”
SENATI-Computación e Informática 35
Microsoft Office Excel 2007
Escribir título “1/1/1965 a 1/1/1980”
Escribir mensaje
5. Finalmente hacer un clic en el botón
Paso a Paso: Eliminar una regla de validación
1. Seleccionar las celdas que desee eliminar la validación de datos.
2. Hacer clic en la ficha Hacer clic en la lista “Validación de datos”
Hacer clic en el botón
“Validación de datos …”
36 SENATI-Computación e Informática
s de datos II
3. Del cuadro de diálogo “Validación de datos” hacer clic en el botón
Paso a Paso: Rodear con círculo datos no válidos
1. Hacer clic en la ficha Hacer clic en la lista
“Validación de datos”
Hacer clic en el botón “Rodear con un círculo
datos no válidos”
Paso a Paso: Borrar círculos de validación
1. Hacer clic en la ficha
Hacer clic en la lista “Validación de datos”
Hacer clic en el botón “Borrar círculos de
validación”
Análisis Y si Permite crear escenarios para realizar predicciones. Por ejemplo, puede realizar análisis
y si para crear dos presupuestos donde en cada uno de ellos se supone un cierto grado de
ingresos. O, puede especificar un resultado que desea que genere una fórmula y, a
continuación, determinar qué conjuntos de valores generarán dicho resultado.
Excel proporciona varias herramientas diferentes para ayudar a realizar el tipo de
análisis que se ajuste a sus necesidades.
SENATI-Computación e Informática 37
Microsoft Office Excel 2007
Uso del administrador de escenarios
Excel es ideal para el análisis Y-Si. Ud. Puede ingresar valores dentro de las celdas y
observar que pasa dependiendo de su contenido.
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.
Componentes de un escenario
Un Modelo con Escenarios nombrados debe tener:
- Un grupo claro de uno o más valores de entrada
- Un grupo claro de uno o más valores resultantes que deberán cambiar basado
en las entradas.
Paso a Paso: Crear escenarios
1. Abrir el archivo “1 Escenarios”
2. Crear tres escenarios: Mejor Opción, Caso Optimista y el Caso Pesimista.
3. Hacer clic en la ficha
38 SENATI-Computación e Informática
s de datos II
Hacer clic en el botón análisis Y si.
4. De las opciones presentadas elegir “Administrador de escenarios”
Hacer clic en el botón “Administrador de escenarios”
5. Se presenta el cuadro de diálogo “Administrador de escenarios”
6. Hacer clic en el botón
Escriba “Mejor opción”
Seleccione el bloque de celdas B15:B18
Hacer clic en el botón “Aceptar”
7. Del cuadro de diálogo valores del escenario especifique según la gráfica mostrada
SENATI-Computación e Informática 39
Microsoft Office Excel 2007
Valores del escenario “Mejor opción”
8. Hacer clic en el botón para adicionar los escenarios restantes.
9. Se presenta el cuadro de diálogo “Modificar escenario”
Escriba “caso optimista” como nombre de escenario
Escriba B15:B18 como celdas cambiantes
Del cuadro de diálogo valores del escenario especifique según la gráfica mostrada
Hacer clic en el botón “Aceptar”
Valores del escenario “Caso optimista”
10. Hacer clic en el botón para adicionar los escenarios restantes.
40 SENATI-Computación e Informática
s de datos II
Escriba “caso Pesimista” como nombre de escenario
Escriba B15:B18 como celdas cambiantes
Hacer clic en el botón “Aceptar”
11. Del cuadro de diálogo valores del escenario especifique según la gráfica mostrada
Valores del escenario “Caso Pesimista”
12. Hacer clic en el botón para terminar
13. Se presenta el cuadro de diálogo administrador de escenarios.
14. Guardar el archivo
SENATI-Computación e Informática 41
Microsoft Office Excel 2007
Paso a Paso: Mostrar escenarios
1. Abrir el archivo “1 Escenarios”
2. Hacer clic en la ficha
Hacer clic en el botón análisis Y si.
3. De las opciones presentadas elegir “Administrador de escenarios”
Hacer clic en el botón “Administrador de escenarios”
4. Se presenta el cuadro de diálogo “Administrador de escenarios”
5. Seleccione el escenario a mostrar, luego hacer clic en el botón
Seleccione escenario a mostrar, luego hacer clic en el botón
Paso a Paso: Modificar un Escenario
1. Abrir el archivo “1 Escenarios”
2. Hacer clic en la ficha
42 SENATI-Computación e Informática
s de datos II
Hacer clic en el botón análisis Y si.
3. De las opciones presentadas elegir “Administrador de escenarios”
Hacer clic en el botón “Administrador de escenarios”
4. Se presenta el cuadro de diálogo “Administrador de escenarios”
5. Seleccione el escenario a modificar, luego hacer clic en el botón
6. Se presenta el cuadro de diálogo “Modificar escenario”
7. Hacer clic en el botón
8. Se presenta el cuadro de diálogo “Valores dele escenario”
Escriba los nuevos valores de su escenario
9. Hacer clic en el botón para terminar
SENATI-Computación e Informática 43
Microsoft Office Excel 2007
Buscar Objetivo
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. Al
realizar una búsqueda de objetivo, Microsoft Excel varía el valor de celda
específica hasta que una fórmula dependiente de dicha celda devuelve el resultado
deseado.
Paso a Paso: Modificar un escenarios
1. Diseñar la siguiente hoja de cálculo
En el ejemplo se muestra el calculo de pago de un préstamo en un periodo de
90 meses a una tasa de interés del 14%. Se utiliza la función =Pago
2. Hacer clic en la ficha
Hacer clic en el botón análisis Y si.
3. De las opciones presentadas elegir “Buscar objetivo …”
Hacer clic en el botón “Buscar objetivo”
4. Se presenta el cuadro de diálogo “Buscar objetivo”
Queremos conocer cuánto de interés se debe pagar si queremos desembolsar
2200 mensual, para pagar los 50,000 del préstamo
Fórmula que
calcula el pago
mensual Valor que queremos pagar mensualmente
Valor a
localizar
44 SENATI-Computación e Informática
s de datos II
5. Hacer clic en el botón , se muestra el estado de la búsqueda de objetivo.
6. Hacer clic en el botón , para terminar
Tabla de datos
Una tabla de datos es un rango de celdas que muestra cómo afecta el cambio de
algunos valores de las fórmulas a los resultados de las mismas.
Las tablas de datos constituyen un método abreviado para calcular varias
versiones en una sola operación, así como una manera de ver y comparar los
resultados de todas las variaciones distintas en la hoja de cálculo.
Tabla de datos de una variable
Entre uno de los mejores ejemplos de análisis sensitivo, esta una tabla de datos
que calcula el pago de préstamo para diferentes tasas de interés.
La tabla de datos de ingreso simple descrita en esta sección crea un cuadro de
pagos mensuales para una serie de tasas de interés.
Paso a Paso: Tabla de datos de una variable
1. Diseñar la siguiente hoja de cálculo
Escribir los datos
según se muestra
En el ejemplo se muestra el calculo de pago de un préstamo de 20,000 en un periodo de 20 años a una tasa de interés del 25%. Se utiliza la función =Pago
SENATI-Computación e Informática 45
Microsoft Office Excel 2007
2. Diseñar un cuadro que permita conocer cuánto pagaremos si la tasa de interés
varía entre 26% y 35%.
Escriba esta fórmula
=PAGO(B4/12;B5*12;B3)
Editar la tabla de tasa de interés
3. Seleccionar el bloque de celdas A8:B18 (Tabla de interés)
Seleccionar celdas A8:B18
46 SENATI-Computación e Informática
s de datos II
4. Hacer clic en la ficha
Hacer clic en el botón análisis Y si.
5. De las opciones presentadas elegir “Buscar objetivo …”
Hacer clic en el botón “Tabla de datos”
6. Se muestra el cuadro de diálogo tabla de datos
7. En el campo: Celda de entrada (Columna) escribir B4 que representa la tasa
de interés del cuadro de préstamo.
8. Hacer clic en el botón para terminar, se debe mostrar el siguiente
resultado
Montos que se deben pagar según la tasa de interés asociada
SENATI-Computación e Informática 47
Microsoft Office Excel 2007
Tablas de datos de dos variables
Continuando con nuestro ejemplo anterior, como resolveríamos para analizar
cuanto pagaríamos, según un rango de tasas de interés y un rango de montos
prestados.
Paso a Paso: Tabla de datos de dos variable
1. Modificar su hoja como se muetsra en la gráfica
Agregar el siguiente
cuadro a su hoja
2. Seleccionar el bloque de celdas A8:G18 (Matriz: interés - monto)
3. Hacer clic en la ficha
Hacer clic en el botón análisis Y si.
4. De las opciones presentadas elegir “Buscar objetivo …”
Hacer clic en el botón “Tabla de datos”
48 SENATI-Computación e Informática
s de datos II
5. Se muestra el cuadro de diálogo tabla de datos
6. En el campo: Celda de entrada (Columna) escribir B4 que representa la tasa
de interés del cuadro de préstamo. En el cuadro celda de entrada (fila) escribir
b3 que representa al monto prestado.
Monto prestado
Tasa de interés
7. Hacer clic en el botón para terminar, se debe mostrar el siguiente resultado
Montos prestados
Tasas de
interés Monto a pagar mensual
Consolidación de datos y Referencia 3D Cuando consolidar datos de varias hojas
Cuando se tenga listas de datos con información semejante una de otra, pero escrita en
cuadros diferentes ya sea en la misma hoja o en hojas distintas, entonces se puede hacer
uso del menú Datos/Consolidar. Esta opción se utiliza para obtener diversos tipos de
cálculo estadístico (suma, promedio, máximo, mínimo, varianza, etc.) en base a la
información guardada en todos estos cuadros.
SENATI-Computación e Informática 49
Microsoft Office Excel 2007
Si desea...
Entonces…
Organizar los datos de todas las hoja de cálculo en orden
y ubicación idénticos.
Consolide por posición
Organizar los datos de forma diferente en las hoja de
cálculo independientes pero utilizar los mismos rótulos
de fila y de columna para que la hoja de cálculo maestra
pueda hacer coincidir los datos.
Consolide por categorías
Utilizar fórmulas con referencias de celdas o referencias
3D a otras hojas de cálculo que esté combinando porque
no tiene una posición o categoría coherente en la que
basarse.
Consolide por fórmula
Consolide por fórmula
En la hoja de cálculo maestra, copie o escriba los rótulos de columna o fila que
desee para los datos de consolidación.
a. Haga clic en la celda en que desea incluir los datos de consolidación.
b. Escriba una fórmula que incluya una referencia de celda a las celdas de
origen de cada hoja de cálculo o una referencia 3D que contenga los
datos que desea consolidar. En cuanto a las referencias de celda, siga uno
de los procedimientos siguientes:
c. Si los datos que se van a consolidar están en celdas diferentes de
otras hoja de cálculo
d. Escriba una fórmula con referencias de celda a las otras hojas de cálculo,
una por cada hoja de cálculo independiente.
Por ejemplo, para consolidar datos de hojas de cálculo
denominadas Ventas (en la celda B4), HR (en la celda F5) y
Marketing (en la celda B9), en la celda A2 de la hoja de cálculo
maestra, tendría que escribir lo siguiente:
e. Para especificar una referencia de celda como Ventas3!B4 en una
fórmula sin escribir, escriba la fórmula hasta el punto en el que necesite
la referencia, haga clic en la etiqueta de la hoja de cálculo y, a
continuación, haga clic en la celda.
f. Si los datos que se van a consolidar están en las mismas celdas de
otras hojas de cálculo
50 SENATI-Computación e Informática
s de datos II
g. Escriba una fórmula con una referencia 3D que utilice una referencia a
un rango de nombres de hojas de cálculo.
Por ejemplo, para consolidar datos en las celdas A2 desde Ventas hasta
Marketing inclusive, en la celda A2 de la hoja de cálculo maestra tendría
que escribir lo siguiente:
Paso a Paso: Consolidación de datos
1. Diseñar las siguientes 4 hojas de cálculo o abrir el archivo “CONSOLIDADO”
SENATI-Computación e Informática 51
,
Microsoft Office Excel 2007
2. Se quiere consolidar las ventas de las sucursales de: Miraflores, Surco y San
Borja en la hoja de Totales.
3. Hacer clic en la ficha
4. Del cuadro de diálogo presentado realizar las siguientes acciones
Elegir la función Suma
Adicionar estas tres referencias y lic en
el botón “Agregar”.
Activar las casillas de verificación
La casilla de verificación “Crear vínculos con los datos de origen” permiten que la hoja de totales se actualice, cuando realice cambios en las hojas orígenes.
52 SENATI-Computación e Informática
s de datos II
5. Finalmente hacer clic en el botón , se obtiene el siguiente resultado. La consolidación del as ventas de las tres sucursales.
Paso a Paso: Consolidación de datos
1. Ingresar la información de acuerdo al diseño sugerido
SENATI-Computación e Informática 53
Microsoft Office Excel 2007
2. Cambiar el nombre de las hojas:
Hoja1 por BAL2005
Hoja2 por BAL2006
Hoja3 por BAL2007 y
Hoja4 por CONSOLIDADO.
Seleccionar el área de valores numéricos a consolidar y asignarle los
nombres de campo: TBAL2005, TBAL2006 y TBAL2007.
3. EN la hoja CONSOLIDADO, ubicarse en la celda C6.
4. Clic en la Ficha de Herramientas Datos; Comando Consolidar del Grupo
Herramienta de datos
El sistema mostrará la siguiente ventana del Comando Consolidar
5. Seleccionar la función a realizar: Suma
6. En Referencia agregar los tres nombres de campos creados sobre los valores
a Sumar de la hojas anteriormente creadas; digitar: TBAL2005 [Agregar],
TBAL2006 [Agregar], TBAL2007 [Agregar]
7. Activar la Casilla de verificación de Crear Vínculos para que el consolidado
siempre este actualizado así se modifiquen los datos orígenes
8. Para ver el resultado, clic en Aceptar.
9. Observar que ha sucedido y comentar las dudas o sugerencias con el
instructor.
54 SENATI-Computación e Informática
s de datos II
Paso a Paso: Consolidación por fórmula
Podemos resolver el consolidado también de la
siguiente manera; por REFERENCIA 3D.
1. Crear una nueva hoja e ingresar la
información de acuerdo al diseño
sugerido en la Hoja5.
Crear una nueva hoja e ingresar la
información de acuerdo al diseño
sugerido en la Hoja5.
2. Cambiar el nombre de la Hoja5 por
CONS_FORMULAS.
3. Clic en la celda C6 y escribir la
siguiente fórmula:
='BAL2005'!C6+'BAL2006'!C6+'BAL2007'!C6
o
=SUMA('BAL2005:BAL2007'!C6)
4. Luego copiar la fórmula, hasta total de ingresos.
NOTAS
Al establecer fórmulas los nombres de las hojas se específica entre
comillas y luego un signo de admiración, ejemplo:
'BAL2005'!C6 hace referencia a la celda C6 de la hoja BAL2005.
Al establecer fórmulas también puede especificarlas como rango de
hojas, separándolos con dos puntos pero solamente especificando las
comillas simples al inicio y final del rango de hojas, luego un signo de
admiración que indica que son nombres de hojas, ejemplo:
=SUMA('BAL2005:BAL2007'!C6) hace referencia a la celda C6 del
rango de hojas BAL2005 hasta BAL2007; esto quiere indicar a la suma
del valor de las celda C6 de las hojas BAL2005, BAL2006 y BAL2007.
Referencias 3D
Una referencia a la misma celda o al mismo rango (rango: dos o más celdas de una hoja.
Las celdas de un rango pueden ser adyacentes o no adyacentes.) en varias hojas se
denomina referencia 3D. Una referencia 3D es un método útil y cómodo de hacer
referencia a varias hojas de cálculo que siguen el mismo patrón y a las celdas de cada
hoja de cálculo que contienen el mismo tipo de datos para, por ejemplo, consolidar los
datos presupuestarios de diferentes departamentos de la organización.
SENATI-Computación e Informática 55
Microsoft Office Excel 2007
Ejemplo 1
La Empresa Corp. Perú desea realizar un consolidado de los ingresos y/o inversión de
las áreas de ventas, marketing y recursos humanos.
PASOS
1. Crear las hojas BAL5, BAL6 y BAL7 con el diseño sugerido.
2. Crear la hoja CONSOLIDADO con el diseño sugerido.
3. Especificar la fórmula de referencia 3D en la celda C6
4. =SUMA('BAL5:BAL7'!C6)
5. Luego copiar la fórmula y observar que ha sucedido.
NOTAS
Al establecer fórmulas referencias 3D debe especificarlas separándolos con
dos puntos pero solamente especificando las comillas simples al inicio y final
del rango de hojas, luego un signo de admiración que indica que son
nombres de hojas, posteriormente se indica la celda a operar; ejemplo:
56 SENATI-Computación e Informática
s de datos II
=SUMA('BAL5:BAL7'!C6) hace referencia a la celda C6 del rango de hojas BAL5 hasta BAL7; esto quiere indicar a la suma del valor de las celda C6 de las
hojas BAL5, BAL6 y BAL7.
Puede utilizar las siguientes funciones en una referencia 3D:
Función Descripción
SUMA
Suma números.
PROMEDIO Calcula el promedio (media aritmética) de números.
PROMEDIOA Calcula el promedio (media aritmética) de números; incluye
valores de texto y lógicos.
CONTAR Cuenta celdas que contienen números.
CONTARA Cuenta las celdas que no están vacías.
MAX Busca el valor mayor de un conjunto de valores.
MAXA Busca el valor mayor de un conjunto de valores; incluye valores
de texto y lógicos.
MIN Busca el valor menor de un conjunto de valores.
MINA Busca el valor menor de un conjunto de valores; incluye valores
de texto y lógicos.
PRODUCTO Multiplica números.
DESVEST Calcula la desviación estándar de una muestra.
DESVESTA
Calcula la desviación estándar de una muestra; incluye valores de
texto y lógicos.
DESVESTP Calcula la desviación estándar de una población.
DESVESTPA
Calcula la desviación estándar de una población; incluye valores
de texto y lógicos.
VAR Calcula la varianza de una muestra.
VARA
Calcula la varianza de una muestra; incluye valores de texto y
lógicos.
VARP Calcula la varianza de una población.
VARPA
Calcula la varianza de una población; incluye valores de texto y
lógicos.
SENATI-Computación e Informática 57
Microsoft Office Excel 2007
Cuestionarios
1. Si tienen una base de datos con n registros duplicados ubicados en diferentes
lugares, cómo los eliminaría.
2. Si desea ingresar datos en un campo que solo permita de acuerdo a un formato preestablecido, cómo lo haría.
3. Cuál es la diferencia entre 3D y Consolidación de datos.
4. Cuál es la utilidad del administrador de escenarios.
5. Cuál es la utilidad de buscar objetivo.
58 SENATI-Computación e Informática
Capítulo
Funciones Financieras
En este capítulo trataremos:
Generalidades sobre Excel en el mundo de los negocios.
Uso de funciones financieras Desarrollo de proyectos con funciones financieras
SENATI-Computación e Informática 59
Microsoft Office Excel 2007
Generalidades sobre Excel en el mundo de los negocios Actualmente no hay ejecutivo que no utilice la hoja de cálculo Microsoft Excel, la cual
es una herramienta muy útil para llevar diseñar modelos de cálculo: Administrativos, de
control, contables, financieros, estadísticos, económicos, matemáticos, ingeniería, etc.
Uso de funciones financieras A continuación se desarrollarán temas financieros con Excel, puntualmente se verán las
siguientes funciones: PAGO, VA, VF, NPER, TASA, TIR
Consideraciones del índice de inflación
La inflación se refiere a un aumento prolongado de todos los precios en la economía, el
cual afecta el nivel general de precios de manera permanente.
Si mañana cae un huayco en la sierra central, paraliza el transporte y debido a eso suben
los precios de los alimentos, no podemos decir que se trate de un proceso inflacionario:
este aumento de precios no sería generalizado, ni tampoco tendría carácter de un
proceso prolongado que afecta el nivel general de precios
La tasa de inflación depende mucho del índice de precios que se emplea para calcularla,
así como del período de referencia.
El índice más empleado es el del precio al consumidor, mide el costo de la canasta de
bienes finales que consume la familia promedio
Otra técnica de cálculo es utilizando el índice de precios al por mayor o el deflactor
implícito del PBI.
El deflactor implícito del PBI mide el costo promedio de los bienes de consumo
privado y público, los bienes de inversión y de los bienes que se importan y/o exportan.
Mide el costo en el último mes del año en cuestión
Inflación Peruana utilizando El deflactor implícito del PBI
1988 629%
1989 2537%
1990 6135%
Los índices Latinoamericanos son los más altos que se han alcanzado en el mundo
durante toda la segunda mitad del siglo XX.
Hiperinflación.
Phillip Cagan lo definió como el proceso que comienza en aquel mes donde el alza de
los precios excede el 50% , y concluye en el mes previo en que el alza mensual de los
precios cae debajo de este nivel y permanece por debajo al menos durante un año.
Inflación anual de más de 12000%.
60 SENATI-Computación e Informática
Financieras
Si aplicamos la definición de Cagan al caso peruano, tendríamos que la hiperinflación comenzó en septiembre de 1988 y concluyó en agosto de 1990.
Durante 24 meses que duró este proceso hiperinflacionario, la inflación
acumulada fue de 3.38x105
(338,000%) y la inflación mensual promedio fue de 46%
Causas de la inflación
Existen diferentes explicaciones sobre las causas de la inflación. De hecho parece
que existen diversos tipos de procesos económicos diferentes que producen
inflación, y esa es una de las causas por las cuales existen diversas explicaciones:
cada explicación trata de dar cuenta de un proceso generador de inflación
diferente, aunque no existe una teoría unificada que integre todos los procesos. De
hecho se han señalado que existen al menos tres tipos de inflación:
Inflación de demanda (Demand pull inflation), cuando la demanda general de bienes se incrementa, sin que el sector productivo haya tenido
tiempo de adaptar la cantidad de bienes producidos a la demanda existente.
Inflación de costos (Cost push inflation), cuando el coste de la mano de
obra o las materias primas se encarece, y en un intento de mantener la tasa de beneficio los productores incrementan los precios.
Inflación autoconstruida (Build-in inflation), ligada al hecho de que los
agentes prevén aumentos futuros de precios y ajustan su conducta actual a
esa previsión futura.
Como se mide
Índice de precios al consumidor (IPC): diseñado para registrar las variaciones en
el poder adquisitivo del promedio de la población (sobre la base de una canasta de
productos de consumo representativa).
Índice Laspeyres (ponderado en el año base)
Índice Paasche (ponderado en el año corriente)
En ambos casos aislamos el efecto de las cantidades ya que nos interesa el cambio
en precios.
SENATI-Computación e Informática 61
Microsoft Office Excel 2007
IPC: ¿índice Laspeyres o Paasche?
Extraído del documento publicado por el profesor: Juan F. Castro del Departamento de
Economía de la Universidad del Pacífico
Tasa nominal
Conocida también como tanto por uno o simplemente como tasa de interés, es la
ganancia que genera un capital de $1 en un año; o sea, es igual a la centésima
parte de la razón o tanto por ciento (ganancia producida por un capital de $100 en
un año).
Generalizando, cuando el tiempo “n” y el período en que está expresada la tasa “i”
coinciden con la capitalización, se dice que la tasa i es nominal.
Paso a Paso: Convertir de tasa efectiva a tasa nominal
1. Dado una tasa nominal, tiempo de capitalización y tasa nominal mensual,
calcular la tasa efectiva.
62 SENATI-Computación e Informática
Financieras
Tasa efectiva (i’)
Es el tanto por uno que, aplicado a un capital C en n períodos, produce un monto
M2 igual al que se obtiene utilizando la tasa proporcional m veces en cada uno de
los n períodos con capitalización subperiódica.
Aparece en la fórmula de monto M2 = C (1 + i’) n, de modo que M2 = M3.
Partiendo de esta última igualdad, podemos expresar la tasa efectiva en función de
la tasa proporcional:
M2 = M3
C (1 + i’) n = C (1 + i/m) n m
1 + i’ = (1 + i/m) m (Simplificamos C y n.)
I’ = (1 + i/m) m – 1 (Despejamos I’.)
Paso a Paso: Convertir de tasa nominal a tasa efectiva
1. Diseñar el siguiente cuadro
SENATI-Computación e Informática 63
Microsoft Office Excel 2007
Paso a Paso: Conversión de tasa nominal a tasa efectiva
1. Editar y desarrollar la siguiente hoja.
= (1 + ($B$4/B9)) ^ B9 -1
= (1 + ($B$4/B8)) ^ B8 -1
Préstamo de un banco: Función PAGO
La función PAGO devuelve el importe de la renta constante vencida o anticipada
en una anualidad simple, en función de su valor presente o futuro
Sintaxis
PAGO(tasa;nper;va;vf;tipo)
Argumentos
Tasa. Es el tipo de interés del préstamo.
Nper. Es el número total de pagos del préstamo. La Tasa y Nper deben
expresarse en la misma unidad de tiempo (Ambas trimestrales, anuales,
etc.)
Va. Es el valor actual o lo que vale ahora la cantidad total de una serie de
pagos futuros, también se conoce como el principal.
64 SENATI-Computación e Informática
Financieras
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 (es decir, el valor futuro de un préstamo es 0).
Si utiliza el argumento opcional Vf sin considerar valor alguno en el
argumento obligatorio Va, la función PAGO obtiene la renta constante en
función de ese valor futuro que nos permite constituir un fondo de
amortización cuyo monto es el importe de Vf.
Si utiliza conjuntamente el argumento obligatorio Va y el argumento
opcional Vf la función PAGO trae al momento 0 el importe de Vf, lo
resta del importe de Va y sobre este saldo le calcula la cuota constante
vencida o anticipada.
Tipo. Es el número 0 (cero) ó 1. Indica el vencimiento de los pagos.
Defina tipo como Si los pagos vencen
0 u omitido Al final del período (Rentas vencidas)
1 Al inicio del período (Rentas anticipadas)
Fórmulas financieras
Renta En función de P En función de S
Vencida
Anticipada
Paso a Paso: Préstamo de banco (Amortización)
1. Utilizar la función pago para desarrollar una tabla de amortización de un
préstamo utilizando el método francés (Pago de cuotas iguales). Usar la
función PAGO.
2. Desarrollaremos el siguiente caso: Se tiene un préstamo de $50,000. Por el
cual se cobrará una tasa de 30% anual. Durante 12 años. Se pide calcular la
cuota constante que debe pagar para cancelar el préstamo.
Tasa 30%
Va=50,000 Pago? Pago? Pago? Pago? nper=12
SENATI-Computación e Informática 65
Microsoft Office Excel 2007
3. Diseñar el siguiente cuadro
Se considera como negativo por ser un
desembolso
4. Se debe obtener como resultado el siguiente cuadro
66 SENATI-Computación e Informática
Financieras
Paso a Paso: Costo equivalente
1. Cuál es el costo equivalente anual de una máquina cuyo precio es de $50,000
su vida útil está estimada en 5 años, su valor de salvamento al final de la vida
útil es de $ 10,000. La tasa de interés es de 12%.
Tasa 12%
Vf = 10,000
Va=50,000 Pago? Pago? Pago? Pago? nper=5
2. Diseñar el siguiente cuadro.
Paso a Paso: Préstamo de banco (Amortización)
1. Se tiene un préstamo de $ 10000 el cual debe cancelarse en el plazo de un año
con cuotas uniformes trimestrales aplicando una tasa del 15%. Calcular el
importe de la cuota en el caso que sea vencida y en el caos de que sea
anticipada. Desarrollar sus tablas de amortización
2. Diseñar el siguiente cuadro
3. El resultado queda como se muestra en la siguiente hoja de cálculo
SENATI-Computación e Informática 67
Microsoft Office Excel 2007
Valor presente de la inversión/pagos futuros: Función VA
Uno de los indicadores más importantes y utilizados en la evaluación de
inversiones es el Valor Actual VA.
La función VA 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 toma dinero prestado, la cantidad del préstamo es el
valor actual para el prestamista.
Sintaxis
VA(tasa; nper; pago; vf; tipo)
Argumentos
Tasa
Es la tasa de interés por período. Por ejemplo, si obtiene un préstamo
para una motocicleta con una tasa de interés anual del 10% y efectúa
pagos mensuales, la tasa de interés mensual será del 10%/12 ó 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 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 que se efectúa en cada período y que no cambia durante la
vida de la anualidad. Por lo general, el argumento pago incluye el capital
y el interés pero no incluye ningún otro cargo o impuesto. Por ejemplo,
los pagos mensuales sobre un préstamo de $10.000 a cuatro años con una
68 SENATI-Computación e Informática
Financieras
tasa de interés del 12% para la compra de una motocicleta, son de $263,33. En la fórmula escribiría -263,33 como el argumento pago.
Vf
Es el valor futuro o el 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). 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.
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 (Rentas vencidas)
1 Al inicio del período (Rentas anticipadas)
Fórmulas financieras
Flujo Vencido
Flujo Anticipados
Paso a Paso: Calculo del Valor actual
1. Calcular el valor actual de una renta de S/. 1000 anuales durante 10 años, si la
tasa de interés es de 5% efectivo anual.
1000 1000 1000 1000 1000 1000
0 1 2 …. 8 9 10
P=?
SENATI-Computación e Informática 69
Microsoft Office Excel 2007
2. Diseñar el siguiente cuadro
3. El resultado queda como se muestra en la siguiente hoja de cálculo
Valor futuro de la inversión: Función VF
Devuelve el valor futuro de una inversión basándose en pagos periódicos
constantes y en una tasa de interés constante.
70 SENATI-Computación e Informática
Financieras
Sintaxis
VF(tasa; nper; pago; va; tipo)
Argumentos
Tasa
Es la tasa de interés por período. Por ejemplo, si obtiene un préstamo
para una motocicleta con una tasa de interés anual del 10% y efectúa
pagos mensuales, la tasa de interés mensual será del 10%/12 ó 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 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 que se efectúa en cada período y que no cambia durante la
vida de la anualidad. Por lo general, el argumento pago incluye el capital
y el interés pero no incluye ningún otro cargo 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% para la compra de una motocicleta, son de $263,33. En la fórmula escribiría -263,33 como el argumento pago.
Va
Es el valor actual de la cantidad total de una serie de pagos futuros. Si el
argumento va se omite, se considerará 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 (Rentas vencidas)
1 Al inicio del período (Rentas anticipadas)
Paso a Paso: Calcular VF
1. Se quiere ahorrar un dinero para un proyecto especial que tendrá lugar dentro de un
año a partir de la fecha de hoy, para lo cual se cuenta con los siguientes datos:
- Depositaremos $15000 en una cuenta de ahorros que devenga un interés
anual de 6%, que se capitaliza mensualmente (interés mensual de
6%/12 ósea 0.5%)
SENATI-Computación e Informática 71
Microsoft Office Excel 2007
- Se depositará $1500 el primer día de cada mes durante los próximos 12
meses
2. Cuánto dinero tendrá en su cuenta al final de los 12 meses
3. Diseñar la siguiente hoja de cálculo
4. El resultado queda como se muestra en la siguiente hoja de cálculo
72 SENATI-Computación e Informática
Financieras
Número de pagos de inversión: Nper
Devuelve el número de rentas constantes vencidas o anticipadas, que forman una
anualidad simple, en función de un stock de efectivo: inicial o final, en la que la
tasa de interés efectiva no varía durante el plazo de la operación.
Nper en función del valor actual calcula el número de rentas con las que puede
amortizarse totalmente un préstamo
Nper en función del valor futuro calcula el número de rentas con las que se puede
constituir un fondo de amortización
Paso a Paso: Nper en función del valor presente, con renta constante
vencida
1. Nper en función del valor presente puede calcularse conociendo el importe de
la renta constante vencida, o de la renta constante anticipada, que amortiza el
préstamo.
2. Con cuántas cuotas trimestrales vencidas, pueden cancelarse un préstamo de
$ 9,000 el mismo que devenga una tasa efectiva trimestral del 5% y se
amortizará con pagos uniformes de $ 2500 cada 90 días.
Va=9000
Tasa=5% tasa=5% tasa5%
Nper=?
pago= -2500 pago= -2500 pago= -2500
3. Diseñar la siguiente hoja
SENATI-Computación e Informática 73
Microsoft Office Excel 2007
Paso a Paso: Nper en función del valor presente, con renta constante anticipada
1. Nper en función del valor presente puede calcularse conociendo el importe de
la renta constante vencida, o de la renta constante anticipada, que amortiza el
préstamo.
2. Con cuántas cuotas trimestrales anticipadas, pueden cancelarse un préstamo
de $ 9,000 el mismo que devenga una tasa efectiva trimestral del 5% y se
amortizará con pagos uniformes de $ 2500 cada 90 días.
Va=9500
Tasa=5% tasa=5% tasa5%
Nper=?
pago= -2500 pago= -2500 pago= -2500
3. Diseñar la siguiente hoja
Paso a Paso: Nper en función del valor futuro
1. Nper en función del valor futuro puede calcularse conociendo el importe de la
renta constante vencida o de renta constante anticipada, que acumula un
fondo de amortización.
2. Cuántos depósitos mensuales vencidos de $500, serán necesarios ahorrar en
un banco que paga una tasa del 24% con capitalización mensual, para
acumular un monto de $ 5474.86
74 SENATI-Computación e Informática
Financieras
Va=9500
Tasa=5% tasa=5% tasa5%
Nper=?
pago= -2500 pago= -2500 pago= -2500
3. Diseñar la siguiente hoja
Función TASA
Devuelve la tasa de interés por período de una anualidad. TASA se calcula por
iteración y puede tener cero o más soluciones. Si los resultados consecutivos de
TASA no convergen en 0,0000001 después de 20 iteraciones, TASA devuelve el
valor de error #¡NUM!
Sintaxis: TASA(nper; pago; va; vf; tipo; estimar)
Argumentos
Nper
Es el número total de períodos de pago en una anualidad.
Pago
Es el pago que se efectúa en cada período y que no puede cambiar
durante la vida de la anualidad. Generalmente el argumento pago incluye
el capital y el interés, pero no incluye ningún otro arancel o impuesto.
Va
Es el valor actual de la cantidad total de una serie de pagos futuros.
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).
SENATI-Computación e Informática 75
Microsoft Office Excel 2007
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 (Rentas vencidas)
1 Al inicio del período (Rentas anticipadas)
Estimar
Es la estimación de la tasa de interés.
Si el argumento estimar se omite, se supone que es 10%.
Si TASA no converge, trate de usar diferentes valores para el argumento
estimar. TASA generalmente converge si el argumento estimar se
encuentra entre 0 y 1.
Paso a Paso: Uso de la función Tasa
1. Calcular la tasa de un préstamo de $8000 a cuatro años con pagos mensuales de $200
Tasa interna de retorno (TIR)
Devuelve la tasa interna de retorno de una inversión, sin costos de financiación o
las ganancias por reinversión representadas por los números del argumento
valores.
Estos flujos de caja no tienen por qué ser constantes, como es el caso en una
anualidad. 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)
76 SENATI-Computación e Informática
Financieras
Argumentos
Valores
Es una matriz o referencia a celdas que contengan los números para los
cuales se 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 ignoran.
Estimar
El 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.
Paso a Paso: Calcular TIR
1. Supongamos que desea abrir un restaurante. El costo estimado para la inversión
inicial es de $70.000, esperándose el siguiente ingreso neto para los primeros
cinco años: $12.000; $15.000; $18.000; $21.000 y $26.000.
2. Calcular la tasa interna de retorno de su inversión después de 2, 3, 4 y 5 años.
SENATI-Computación e Informática 77
Microsoft Office Excel 2007
Desarrollo de proyectos con funciones financieras
Comprar frente a un leasing
El leasing es una forma de financiamiento de activos. Tener en cuenta la siguiente
frase: “Para generar utilidades, no es necesario ser propietario del activo fijo,
basta ser un usuario”
- El arrendamiento puede ser operativo o financiero
- El arrendamiento operativo, nunca le hará dueño del activo
- El arrendamiento financiero, si le hará dueño del activo, a su solicitud y
tiene la preferencia de compra, cuando termine de pagar la última cuota
Actores que participan en el Leasing
Financiador (Compra el activo)
Como se gestiona
Proveedor Usuario del activo del activo
- El futuro usuario, busca al proveedor del activo que necesita solicitando
cotizaciones
- Una vez elegido el proveedor se dirige a la entidad financiera de Leasing
llevándole la información
- La sociedad de Leasing, compra el activo y se lo alquila al cliente
usuario
Algunas diferencias entre comprar y alquilar
El préstamo (COMPRAR)
El Leasing (ALQUILAR)
No financia el 100% de la inversión
Si financia el 100% de la inversión
El estudio del préstamo demora varios
meses
La maquinaria se dispone a las 24
horas
78 SENATI-Computación e Informática
Financieras
El empresario es dueño del activo
desde el inicio
El empresario podría ser dueño, al final
de plazo
El empresario es el propietario y puede
depreciar
El financiador es el propietario. No el
empresario
Comprando o alquilando tenemos que efectuar un flujo de pagos, si comparamos que flujo de pagos es más barato podríamos saber que es más conveniente
comprar o alquilar
Debemos tener en cuenta que al comprar un activo, es para hacerlo producir y
obtener utilidades, el estado cobra impuestos y ello representa un costo.
“La ventaja relativa del financiamiento mediante Leasing, o mediante préstamo,
dependerá de los flujos de fondos derivados de cada uno de ambos métodos, y del
costo de oportunidad de los fondos, después de impuestos” (Van Horne).
Evaluación por ingresos netos
Permite ver panorámicamente las diferencias en el estado de resultados de ambas
formas de financiamiento
Consideraciones
- El leasing, no afecta el pasivo del balance, por cuanto no es un préstamo,
tampoco afecta el activo del balance, el empresario no es dueño del bien
- Con préstamo, usted es propietario y puede depreciar
- Con leasing, no puede depreciar. El propietario es el financiador
- Con préstamo, los intereses van antes de impuestos y, las amortizaciones
después de impuestos
- Con leasing, hay una sola cuota que íntegramente va a costos, antes de
impuestos
Paso a Paso: Evaluación por ingresos netos
1. Se quiere adquirir una máquina que cuesta $5000 y tiene una vida útil de 2 años.
Tasa para leasing 18 %, tasa para préstamo 25%
2. Alternativa con leasing
SENATI-Computación e Informática 79
Microsoft Office Excel 2007
3. Alternativa con préstamo
Saldo = saldo ant. – Amort.
=F10-H10
Interés = Tasa*Saldo actual
=F10*$F$4
Amort = Cuota - interés
=I10-G10
=$F$6
4. Desarrollando el flujo de fondos
80 SENATI-Computación e Informática
Financieras
5. Calculando el valor presente, elegir el que tiene mayor valor presente.
Evaluación por costos netos
Consta de tres pasos.
- Preparar el flujo de pagos con Leasing, después de impuestos
- Preparar el flujo de pagos con préstamos, después de impuestos
- Hallar el valor presente de ambos flujos, al costo del capital después
de impuestos y, elegir la alternativa de menor costo
Paso a Paso: Evaluación por costos netos
1. Se quiere adquirir una máquina que cuesta $5000 y tiene una vida útil de 2 años.
Tasa para leasing 18 %, tasa para préstamo 25%
Tasa de evaluación para calcular el valor presente 10%.
SENATI-Computación e Informática 81
Microsoft Office Excel 2007
Calcular la tasa interna de devolución para un flujo de liquidez no periódico
Función TIR.NO.PER
Devuelve la TIR de un proyecto cuyos flujos de caja netos son de diferentes
magnitudes, están distribuidos en plazos diferentes, se puede considerar flujo
positivo o negativo.
Sintaxis
TIR.NO.PER(valores;fechas;estimar)
Valores
Es una serie de flujos de caja que corresponde a un calendario de pagos
determinado por el argumento fechas. El primer pago es opcional y
corresponde al costo o pago en que se incurre al principio de la inversión.
Si el primer valor es un costo o un pago, debe ser un valor negativo.
Todos los pagos sucesivos se descuentan basándose en un año de 365
días. La serie de valores debe incluir al menos un valor positivo y un
valor negativo.
Fechas
Es un calendario de fechas de pago que corresponde a los pagos del flujo
de caja. La primera fecha de pago indica el principio del calendario de
pagos. El resto de las fechas deben ser posteriores a ésta, pero pueden
aparecer en cualquier orden. Las fechas deben especificarse utilizando la
función FECHA o como resultado de otras fórmulas o funciones. Por
ejemplo, utilice FECHA(2008;5;23) para el 23 de mayo de 2008. Pueden
producirse problemas si las fechas se escriben como texto.
Estimar
Es un número que el usuario estima que se aproximará al resultado de
TIR.NO.PER.
82 SENATI-Computación e Informática
Microsoft Office Excel 2007
Tasa_reinversión
Es la tasa de interés obtenida por los flujos de caja a medida que se
reinvierten.
Si n es el número de flujos de caja en valores, tasaf es la
tasa_financiamiento y tasar es la tasa_reinversión, la fórmula de TIRM
es:
Paso a Paso: TIRM
Un proyecto cuya inversión inicial es de 120,000, y se tiene flujos de 39000, 30000,
21000, 37000, 46000 cada año.
La tasa de interés del préstamo es de 10%
La tasa de interés anual de los beneficios reinvertidos es de 12%
Calcular TIRM
SENATI-Computación e Informática 83
Financieras
Cuestionarios
1. Cuál es la diferencia entre un interés simple y un interés compuesto.
2. Cuál es la diferencia entre valor presente y valor futuro.
3. Para que sirve la función TIR.
4. Cuál es la diferencia entre activo y pasivo.
5. Qué es un Leasing.
84 SENATI-Computación e Informática
Capítulo
Análisis de Datos II
En este capítulo trataremos:
Herramientas de análisis estadístico
Uso de cuadros de histogramas Uso de Solver
SENATI-Computación e Informática 85
s de datos III
Herramientas de Análisis Estadístico
Excel presenta un conjunto de herramientas para proyectar resultados de ingresos,
gastos de su negocio. A continuación se describe el uso de estas herramientas.
Tendencia lineal de ajuste perfecto automáticamente
En una serie lineal, el incremento, o diferencia entre el primer valor de la serie y
el siguiente, se agrega al valor inicial y, a continuación, a cada uno de los valores
siguientes.
Paso a Paso: Tendencia lineal de ajuste perfecto
1. La empresa “Productos Agrícolas SAC” desea proyectar sus ventas del último
semestre del año, para ello nos presenta la información de ventas de los seis primeros meses.
2. Utilizaremos el procedimiento para proyectar la tendencia lineal
a. Seleccionar el bloque de celdas A4:B9
b. Señalar con su mouse este cuadradito y arrastrar con el botón derecho del mouse hasta la fila 15, luego soltar el mouse
c. Del menú contextual presentado elegir La opción “Tendencia
lineal”
SENATI-Computación e Informática 87
Microsoft Office Excel 2007
3. Se debe mostrar como resultado el siguiente cuadro
Ventas del primer
semestre
Ventas
proyectadas con tendencia lineal
automática, para el último semestre
Tendencia geométrica de forma automática
En una serie geométrica, el valor inicial se multiplica por el incremento para
obtener el siguiente valor en la serie. El resultado y los siguientes resultados se
multiplican a continuación por el incremento.
Paso a Paso: Tendencia geométrica de forma automática
1. Utilizando el cuadro anterior, pero esta vez proyectar con tendencia geométrica.
b. Seleccionar el bloque
de celdas B4:B9
b. Arrastrar con el botón derecho del mouse hasta la fila 15, luego soltar el mouse
c. Del menú contextual presentado elegir La opción “Tendencia
geométrica”
88 SENATI-Computación e Informática
s de datos III
2. Se presentará como resultado el siguiente cuadro
Ventas del
primer semestre
Ventas proyectadas con tendencia
geométrica, para el último semestre
Tendencia lineal o geométrica de forma manual
En una serie lineal, los valores iniciales se aplican al algoritmo de tendencia lineal
(y = mx+b) para generar la serie.
En una serie geométrica, los valores iniciales se aplican al algoritmo de curva
exponencial (y=b*m^x) para generar la serie.
En ambos casos, se omite el incremento. La serie creada es equivalente a los
valores devueltos por las funciones TENDENCIA o CRECIMIENTO.
Paso a Paso: Tendencia lineal o geométrica de forma manual
1. Diseñar el siguiente cuadro
a. Arrastre este recuadro pequeño con el botón derecho del mouse, hasta la celda B9. Y suelte el mouse
b. Hacer clic sobre la opción Series…
SENATI-Computación e Informática 89
Microsoft Office Excel 2007
2. Se presenta el siguiente cuadro de diálogo.
Elegir
columna, para que la serie se extienda hacia abajo
Seleccione tipo de tendencia:
Lineal o geométrica
Active la casilla Tendencia
3. Hacer clic en el botón para terminar
Se genera la tendencia
reemplazando a los tres primeros valores e la serie
original
Agregar una línea de tendencia a un gráfico
Las líneas de tendencia se utilizan para el estudio de problemas de predicción, lo
que se denomina también análisis de regresión. En un gráfico si tiene los datos de
ventas de los primeros meses del año, puede agregar una línea de tendencia al
gráfico que muestre la tendencia general de las ventas (creciente, decreciente o
uniforme) o que muestre la tendencia prevista para los meses venideros.
Media móvil.
Se puede crear una media móvil, que suaviza las fluctuaciones en los datos y
muestra la trama o tendencia con más claridad.
Tipos de gráfico que admiten líneas de tendencias
Pueden agregarse líneas de tendencia a las series de datos en los siguientes
gráficos:
90 SENATI-Computación e Informática
s de datos III
• Áreas 2D no apiladas
• Barras
• Columnas
• Líneas
• Cotizaciones
• Tipo XY (Dispersión), y
• Burbujas.
No pueden agregarse líneas de tendencia a las series de datos en los gráficos 3D,
radiales, circulares, de superficie o de anillos.
Si se cambia un gráfico o una serie de datos de modo que ya no permita la línea de
tendencia asociada (por ejemplo, si se cambia el tipo de gráfico por un gráfico de
áreas 3D o si se cambia la vista de un informe de gráfico dinámico o de un
informe de tabla dinámica asociado), se perderán las líneas de tendencia.
Paso a Paso: Agregar línea de tendencia a un gráfico
1. Diseñar el siguiente cuadro
2. Seleccionar el bloque de de celdas A2:B7 a. Hacer clic en botón
c. Seleccionar las
celdas A2:B7
b. Hacer clic en la ficha Insertar
d. Elegir este
tipo de gráfico
SENATI-Computación e Informática 91
Microsoft Office Excel 2007
3. Se muestra el siguiente gráfico
Tasa de inflación Perú
4
3
2
1
0
2003 2004 2005 2006 2007
4. Para agregar la línea de tendencia, realice las siguientes operaciones
a. Hacer un clic sobre el gráfico para seleccionarlo.
b. Hacer clic sobre la ficha presentación
c. Hacer clic sobre el botón , ,
Mas opciones de línea de tendencia
d. Se presenta el siguiente cuadro de diálogo
e. Elegir Lineal
f. Activar la casilla “Presentar ecuación
en el gráfico”
92 SENATI-Computación e Informática
s de datos III
5. Se muestra el siguiente gráfico
4
2
y = -0.27x + 3.09 0
2003 2004 2005 2006 2007
Tasa de inflación Perú Lineal (Tasa de inflación Perú)
Si se reemplaza “x” en la ecuación por el número del año siguiente se obtienen los pronósticos de la inflación de los próximos años (2008, 2009, etc.)
Proyectar valores
Función PRONÓSTICO
Calcula un valor futuro utilizando los valores existentes. El valor previsto es un
valor del eje Y para un valor del eje X dado. Los valores conocidos son valores de
x e y existentes, y el nuevo valor se calcula utilizando una regresión lineal. Esta
función se puede utilizar para prever las ventas futuras, las necesidades de
inventario y las tendencias de los consumidores.
Sintaxis
PRONOSTICO(x;conocido_y;conocido_x)
Argumentos
X Es el punto de datos cuyo valor se desea predecir.
Conocido_y Es la matriz o rango de datos dependientes.
Conocido_x Es la matriz o rango de datos independientes.
La ecuación de la función pronóstico es a + bx, donde:
y
Y donde x e y son las medias de muestra PROMEDIO(conocido_x) y
PROMEDIO (conocido y).
SENATI-Computación e Informática 93
Microsoft Office Excel 2007
Paso a Paso: Uso de la función Pronóstico
1. Diseñar el siguiente cuadro
94 SENATI-Computación e Informática
s de datos III
Función TENDENCIA
Devuelve valores que resultan de una tendencia lineal. Ajusta una recta (calculada
con el método de mínimos cuadrados) a los valores de las matrices definidas por
los argumentos conocido_y y conocido_x. Devuelve, a lo largo de esa recta, los
valores y correspondientes a la matriz definida por el argumento nueva_matriz_x
especificado.
Sintaxis
TENDENCIA(conocido_y;conocido_x;nueva_matriz_x;constante)
Argumentos
Conocido_y Es el conjunto de valores de y que se conocen en la
relación y = mx+b.
Conocido_x Es un conjunto opcional de valores x que se conocen en la
relación y = mx+b.
Nueva_matriz_x Son los nuevos valores de x para los cuales desea que
TENDENCIA devuelva los valores de y correspondientes
Paso a Paso: Uso de la función Tendencia
1. Diseñar el siguiente cuadro
SENATI-Computación e Informática 95
Microsoft Office Excel 2007
Uso de cuadros de Histogramas para el cálculo de frecuencias individuales y acumulativas
El gráfico de la distribución de frecuencias, se llama histograma.
El histograma de frecuencias es una representación visual de los datos en donde se
evidencian fundamentalmente tres características: forma, acumulación o tendencia
posicional y dispersión o variabilidad.
El histograma (de frecuencias) en si es una sucesión de rectángulos construidos
sobre un sistema de coordenadas de la siguiente manera:
1. Las bases de los rectángulos se localizan en el eje horizontal.
La longitud de la base es igual al ancho del intervalo.
2. Las alturas de los rectángulos se registran sobre el eje vertical y
corresponden a las frecuencias de los intervalos.
3. Las áreas de los rectángulos son proporcionales a las frecuencias de las
clases.
Para que usar los histogramas
1. Los histogramas de frecuencia son una herramienta útil cuando hay que
analizar una gran cantidad de datos.
a. Para mostrar en forma de gráficos de barras las características de
un producto o servicio:
- Tipos de defectos
- Problemas
- Riesgos de seguridad, etc.
2. Un histograma toma datos de mediciones
a. Temperatura, presiones, alturas, pesos, etc.
b. Muestra su distribución.
3. Un histograma revela la cantidad de variación propia de un proceso.
Datos necesarios para construir un histograma en Excel
Datos de Entrada. Estos son los datos que desea analizar mediante la
herramienta Histograma.
Números de clase. Estos números representan los intervalos que desea
que utilice la herramienta Histograma para medir
los datos de entrada en el análisis de datos.
Paso a Paso: Construcción de un histograma
1. Diseñar el siguiente cuadro
96 SENATI-Computación e Informática
s de datos III
,
,
2. Hacer clic en la ficha se presenta el siguiente
cuadro de diálogo.
3. Hacer clic sobre la opción “Histograma”
4. Hacer clic en el botón Aceptar. Se presenta el siguiente cuadro de diálogo.
Seleccionar rango de entrada y rango de clase según se muestra en la gráfica.
Como opciones de salida hacer clic en la opción “En una hoja nueva”.
Hacer clic en “Crear gráfico”.
SENATI-Computación e Informática 97
,
Programa N
Microsoft Office Excel 2007
5. Hacer clic en el botón se agregará una nueva hoja con el cuadro de clases, frecuencias y la gráfica del histograma.
Interpretación de un Histograma
Se trata de identificar y clasificar la pauta de variación del conjunto de datos
estudiado, que relacione la variación con el proceso o fenómeno en estudio.
El resultado de este análisis es una teoría sobre el funcionamiento del proceso o
sobre la causa del problema que se está investigando. A continuación se presentan
pautas de variación típicas:
a. Distribución en forma de campana
Es la distribución normal. La desviación respecto a esta forma puede indicar la existencia de problemas externas al proceso. La forma de campana no asegura, por sí misma y sin analizar su valor medio y el recorrido de los datos, que el proceso funcione de forma satisfactoria.
Pico
b. Distribución con doble campana o con doble pico
Representa generalmente la combinación de dos distribuciones y sugiere la presencia de dos procesos distintos.
s de datos III
c. Distribución plana
Representa un caso típico de departamentos que no tienen el trabajo bien definido y cada cual lo hace "a su manera".
Varias distribuciones en campana con sus centros distribuidos uniformemente a lo largo del recorrido de los datos.
d. Distribución en peine
Esta pauta de variación es típica de errores de medición, errores en la forma de agrupar los datos o sesgos sistemáticos de redondeo.
Debe revisar los procesos de recogida de datos y construcción del Histograma.
Valores altos y bajos se alternan de forma regular
e. Distribución con un pico aislado
El proceso con el pico pequeño será una anormalidad o deficiencia que no
sucede a menudo o regularmente.
Estos picos unidos a distribuciones sesgadas o truncadas indican falta de
eficacia en la eliminación de elementos defectuosos.
Esta forma sugiere la existencia de dos procesos distintos
SENATI-Computación e Informática 99
Microsoft Office Excel 2007
f. Distribución con un pico en el extremo
Esta forma se presenta cuando la cola de una distribución regular se ha
cortado y acumulado en una sola categoría en el extremo del recorrido de los
datos. Suele indicar un registro poco cuidadoso o sesgado de los datos.
Un pico situado en un extremo de una distribución regular
g. Distribución sesgada o truncada
Esta distribución es típica de procesos con límites prácticos a un lado del
valor nominal o a datos parciales de un proceso (distribuciones con parte de
los datos suprimidos).
Distribución sesgada Distribución truncada
Pico descentrado Descendencia suave de la cola.
Descendencia brusca de la cola
Posibles problemas y deficiencias de interpretación
a. Si los datos utilizados no son adecuados (sesgados, inexactos,
anticuados, poco significativos, etc) las conclusiones no reflejarán la
situación real.
b. Muestra pequeña y poco representativa. Se requiere mínimo cuarenta
observaciones para cada uno de los Histogramas que se desee realizar.
c. Aceptar las conclusiones del análisis como hechos.
La interpretación de un Histograma es una simple teoría y por tanto deberá ser
confirmada posteriormente mediante el análisis adicional y la observación de los
hechos reales.
100 SENATI-Computación e Informática
s de datos III
Definición y resolución de problemas con Solver Solver se utiliza cuando queremos encontrar la mejor manera de hacer algo. O dicho de
un modo más formal: queremos encontrar los valores de determinadas celdas de una
hoja de cálculo que optimicen (aumenten o disminuyan) un determinado objetivo.
Generalidades sobre Solver
Un modelo de optimización consta de tres partes: la celda objetivo, las celdas
cambiantes y las restricciones.
a. La celda objetivo representa el objetivo como, por ejemplo, aumentar las
ganancias mensuales.
b. Las celdas cambiantes son las celdas de la hoja de cálculo que podemos
cambiar o ajustar para optimizar la celda objetivo como, por ejemplo, la
cantidad de cada producto fabricada durante un mes.
c. Las restricciones son delimitaciones que se aplican a las celdas cambiantes
como, por ejemplo, no usar más recursos que los disponibles y no producir
más cantidad de un producto que la que pueda venderse.
Carga del programa de complemento Solver
1. Hacer clic en el Botón Microsoft Office ,
2.
Hacer clic en la opción
3. En el cuadro de diálogo “Administrar”, hacer clic en la opción
“Complementos de Excel” y luego hacer clic en el botón “Ir”.
4. En el cuadro de diálogo Complementos disponibles, active la casilla de
verificación “Complemento Solver”
Hacer clic para carga el complemento “Solver”
SENATI-Computación e Informática 101
Microsoft Office Excel 2007
5. Para terminar hacer clic en el botón
Paso a Paso: Desarrollo de casos utilizando Solver
1. La empresa “Comida a su puerta” basado en la experiencia del año anterior
sabe que por cada sol gastado en publicidad ingresa aproximadamente como
promedio 8.75 soles en pedidos. El negocio está sujeto a variaciones
estaciónales muy acentuadas. Este año 2009 se tiene proyectado unos pedidos de
300,000 nuevos soles la pregunta es cuanto se debe invertir en publicidad.
2. Diseñar el siguiente cuadro se considera 20,000 en inversión en publicidad
distribuido entre los 4 trimestres.
=B5*$F$2*B4 =SUMA(B5:E5)
3. Hacer clic en la ficha , luego hacer clic en el botón
4. Se presenta el cuadro de diálogo parámetros de Solver
Escriba F5. Que define la celda que mostrará el valor en
pedidos que se desea alcanzar
Grupo de celdas
cambiantes
Valor de la celda objetivo, Monto que deseamos obtener
como pedidos
5. Haga clic en el botón para hallar una solución
102 SENATI-Computación e Informática
s de datos III
.
.
Hacer clic para guardar este
escenario
6. Hacer clic en el botón para almacenar esta solución
7. Para terminar haga clic en el botón Se presenta la siguiente solución.
Paso a Paso: Agregar restricciones a Solver
1. Del ejemplo anterior busque una solución que mantenga el presupuesto
publicitario total por debajo de 25,000.
2. Hacer clic en la ficha
Luego hacer clic en el botón
3. Se presenta el cuadro de diálogo parámetros de Solver
4. Hacer clic en el botón
5. Defina la siguiente restricción. Presupuesto de publicidad <= 25,000
SENATI-Computación e Informática 103
Microsoft Office Excel 2007
6. Haga clic en el botón . Su cuadro de diálogo debe quedar así.
7. Haga clic en el botón , . Para mostrar la nueva solución con la restricción especificada
Cómo configura Solver
1. Hacer clic en la ficha
Luego hacer clic en el botón
2. Del cuadro de diálogo presentado
Hacer clic en el botón
104 SENATI-Computación e Informática
s de datos III
Se aplica sólo a los problemas no lineales. Se indica mediante una fracción entre 0 y 1. Cuantos más decimales tenga el número, menor será la convergencia
Porcentaje donde la celda objetivo da una solución satisface las restricciones externas. Una tolerancia mayor tiende a acelerar el proceso de solución.
Tiempo que tarda el proceso de
solución.
Tiempo que tarda el proceso de solución.
Debe indicarse la precisión mediante una fracción entre 0 (cero) y 1. Cuantas más posiciones decimales tenga el número que se escriba, mayor será la precisión; por ejemplo, 0,0001 indica una precisión mayor que 0,01.
Para resolver un problema de optimización lineal.
Hace que Solver presuponga un límite de 0 (cero) para todas las celdas ajustables en las que no se haya establecido un límite inferior en el cuadro Restricción
Utilizar la escala automática cuando haya grandes diferencias de magnitud entre las entradas y los resultados
Utiliza la extrapolación lineal de un vector
tangente.
Utiliza la extrapolación cuadrática, que puede mejorar en gran medida los resultados de problemas no
lineales Progresiva Se utilizan para la mayor parte
de los problemas, en los que los valores de
restricción cambian relativamente poco.
Central Se utiliza en los problemas en que
las restricciones cambian rápidamente, en
especial cerca de los límites.
Newton Utiliza un método
quasi-Newton que
normalmente necesita más
memoria pero menos
iteraciones que el método
de gradiente conjugada.
Conjugado Necesita menos memoria que el método Newton, pero normalmente necesita más iteraciones para alcanzar un nivel de exactitud concreto
3. En este cuadro de diálogo puede especificar sus opciones de cálculo.
Modificación de forma de búsqueda de soluciones en Solver
1. Hacer clic en la ficha
Luego hacer clic en el botón
2. Del cuadro de diálogo presentado
Hacer clic en el botón
SENATI-Computación e Informática 105
Microsoft Office Excel 2007
Cambie las opciones de Estimación, Derivadas y Buscar para optimizar su modelo
Desarrollo de casos tipo utilizando Solver
1. Diseñar el siguiente cuadro
2. Ajustar los precios de los productos de forma que el precio de venta al
público (P.V.P.) (F7) se rebaje a 17000. Se debe tener en cuenta que el precio
de cada producto no puede ser superior o inferior a un precio determinado.
Grupo de celdas
cambiantes
Escriba F7. Que define la celda que mostrará el valor en
pedidos que se desea alcanzar
Valor de la celda objetivo, Monto que deseamos ajustar el precio de venta.
106 SENATI-Computación e Informática
s de datos III
3. Hacer clic en la ficha , luego hacer clic en el botón
4. Haga clic en el botón para hallar una solución
5. Para terminar haga clic en el botón Se presenta la siguiente
primera solución sin aplicar restricciones.
6. Ahora aplicar a los precios de los productos las siguientes restricciones.
B4 > 900 and B4 <1250
B5 > 1300 and B5 <1500
B6 > 1600 and B6 <1850
7. Hacer clic en la ficha , luego hacer clic en el botón
8. Se presenta el cuadro de diálogo parámetros de Solver
9. Hacer clic en el botón se presenta el cuadro de diálogo
parámetros de Solver.
Como no se puede comprar fracciones de artefactos
modificaremos el rango de celdas cambiantes a: B4:B6
Agregar la siguiente lista
de restricciones
8. Haga clic en el botón para hallar una solución
SENATI-Computación e Informática 107
Microsoft Office Excel 2007
9. Para terminar haga clic en el botón Se presenta la siguiente solución esta vez incluye las restricciones.
Cuestionarios
1. Cuál es la utilidad de las herramientas de análisis estadísticos.
2. Cuál es la diferencia entre tendencia lineal y geométrica.
3. Cuál es la utilidad de un Histograma.
4. En que aplicaría Solver.
5. Cuál es la diferencia de las funciones Pronóstico y Tendencia.
108 SENATI-Computación e Informática
Capítulo
Formularios
En este capítulo trataremos:
Activación de la ficha PROGRAMADOR Diseño de formularios
Desarrollo de ejemplos tipo con formularios
SENATI-Computación e Informática 109
,
.
Microsoft Office Excel 2007
Activación de la ficha programador La ficha Programador se utiliza cuando vaya a escribir macros, ejecutar macros que
haya grabado previamente, o crear aplicaciones para utilizar con programas de
Microsoft Office. También puede utilizarlo para el diseño de formularios.
Paso a Paso: Activar la ficha programador
1. Hacer clic en el botón Botón Office
2. Hacer clic en el botón se
presenta el cuadro de diálogo “Opciones de Excel”
3. Hacer clic en la opción
4. Se presenta el cuadro de diálogo de opciones más
frecuentes.
Activar la casilla “Mostrar ficha
Programador en la cinta de opciones”.
5. Hacer clic en el botón para terminar.
110 SENATI-Computación e Informática
Formularios
Diseño de formularios
Un formulario es una hoja de cálculo con un formato y diseño ya establecido. Puede
contener fórmulas, funciones e incluso controles (una lista, botones de opción, casillas
de verificación). Gracias a un formulario limitamos el ingreso y manipulación de datos
a nuestras hojas, así como brindamos soluciones de negocios para nuestros clientes.
Mostrar y ocultar elementos Lo primero que debemos modificar, es la pantalla de presentación de nuestra
solución, interfaz que debe ser mucho más elaborada, más limpia, sin los
elementos propios de una hoja de cálculo, como las líneas de división o las
etiquetas de hojas.
Mucho de los elementos que usted nota en Excel pueden ser ocultados, de esta
manera lograr que su pantalla sea visualmente más sencilla y simple. Los
elementos que se ocultaran son: Líneas de división, Encabezados de fila y
Columna y Etiquetas de hojas.
Encabezado de columna
Encabezado de fila Líneas de
división
Etiquetas de
hojas
Paso a Paso: Modificar el diseño de la hoja
1. Hacer clic en la ficha
2. Hacer clic en el botón se muestra el siguiente cuadro de diálogo
SENATI-Computación e Informática 111
Microsoft Office Excel 2007
Hacer clic para ocultar: Líneas de cuadrícula, Títulos
3. Hacer clic con el botón derecho de la hoja que desea ocultar, luego hacer clic en
el botón “Ocultar”.
Trabajando con Controles Los controles nos permiten interactuar con nuestra ventana en forma más
intuitiva, a través de listas, botones de opción y botones de comando, tal como si
fueran cuadros de diálogos de Windows.
Controles de Formularios
CONTROL NOMBRES DETALLES
Botón Permite ejecutar una macro.
Macros automatiza procesos repetitivos.
Etiqueta Sirve para agregar rótulos al formulario.
Campo de texto Sirve para agregar datos al formulario.
Cuadro de grupo Para agrupar controles como casillas y
botones de opción.
Casillas de verificación Podrá elegir una, varias o ninguna de las
opciones.
Botón de opción Sólo podrá elegir una de un grupo de
opciones.
Control de número Para cambiar el valor de una celda
Cuadro combinado Muestra una lista de opciones.
112 SENATI-Computación e Informática
Formularios
,
Cuadro de lista Muestra una lista de opciones desplegables.
Barra de
desplazamiento
Permite es desplazamiento por un objeto.
Uso de los controles de formulario Los controles tienen la función de permitir al usuario indicar las operaciones a
realizar. Los controles son el mecanismo que utiliza el usuario para indicar a las
acciones a efectuar. A continuación describiremos los controles más importantes
a utilizarse en Excel.
Paso a Paso: Uso del control casilla de verificación
1. Crear una casilla de verificación que al activarla permita mostrar el valor del
Flete del transporte de un determinado producto, el cual equivale a S/. 20.00;
cabe mencionar que si se desactiva la casilla de verificación mostrará el valor de
cero o vacío.
2. Hacer en la ficha botón
3. Hacer clic en el control casilla de verificación
4. Clic en cualquier celda de la hoja de cálculo
5. Diseñar el control en la pantalla y vincularlo, es necesario la vinculación de los
controles de formularios con una celda, dado que posteriormente por la celda
vinculante aplicaremos lógica para solucionar el problema.
6. Hacer clic derecho sobre este control y elegir la opción
Hacer clic para activar el control
Escribir $E$3 para vincular este control con la celda en mención
SENATI-Computación e Informática 113
,
Microsoft Office Excel 2007
Las casillas verificación devuelven como resultado en la celda vinculante
Si está activado, devuelve VERDADERO
Si está desactivado, devuelve FALSO
7. Para resolver el problema, aplicaremos funciones lógicas en la ceda C2
=SI(E2=VERDADERO;20;0)
También puede hacerlo de la siguiente manera
=SI(E2;20;0)
Dado que el sistema asume por defecto en la condición lógica la
expresión VERDADERO.
8. Ahora recomendamos activar y desactivar la casilla de verificación y observar
que sucede.
Paso a Paso: Uso del control botón de opción
1. Crear tres botones de opción que permitan mostrar el Precio a pagar entre tres
diferentes equipos de Cómputo.
HP Core 2, RAM 4 Mb, HD 250 GB $ 1,000.00
Dell Dual Core, RAM 4 Mb, HD 250 GB $ 900.00
IBM Core Quad, RAM 8 Mb, HD 250 GB $ 1,300.00
2. Hacer en la ficha botón
3. Hacer clic en el control botón de opción
4. Diseñar la siguiente pantalla
5. Hacer clic derecho sobre el primer botón de opción y elegir la opción
114 SENATI-Computación e Informática
Formularios
Hacer clic para activar el control
Escribir $F$2 para vincular este control con la celda en mención
6. Repetir este procedimiento para los otros dos controles.
7. Los botones de opción devuelven como resultado en la celda vinculante:
1 = Si se eligió la primera opción, Computadora HP
2 = Si se eligió la primera opción, Computadora DELL
3 = Si se eligió la primera opción, Computadora IBM
8. Para resolver el problema, aplicaremos la función INDICE en la ceda D2
9. Ahora cada vez que hagamos clic, en las opciones de los productos, mostrará el
precio que le corresponde.
Paso a Paso: Uso de control numérico
1. Abrir el archivo “4 Prestamo Bancario”
SENATI-Computación e Informática 115
,
,
Microsoft Office Excel 2007
Agregar el control numérico para el principal
2. Hacer en la ficha botón
3. Hacer clic en el control numérico
4. Agregar el control numérico sobre la celda C3
5. Hacer clic derecho sobre este control y elegir la opción
6. Se presenta el siguiente cuadro de diálogo defina los datos mostrados en la
gráfica.
Estos datos se utilizarán en el control numérico que se vinculará con el principal, celda B3
Agregar el control numérico para la tasa de interés
7. Hacer en la ficha botón
8. Hacer clic en el control numérico
9. Agregar el control numérico sobre la celda C4
10. Hacer clic derecho sobre este control y elegir la opción
11. Se presenta el siguiente cuadro de diálogo defina los datos mostrados en la
gráfica.
116 SENATI-Computación e Informática
Formularios
,
Estos datos se utilizarán en el control numérico que se vinculará con la tasa de interés, celda C4
12. Finalmente su hoja de cálculo queda como se muestra a continuación.
=c4/100
Utilice estos controles para
definir el principal
y la tasa de interés.
Paso a Paso: Uso del cuadro combinado
1. Crear un Cuadro combinado que permita la elección entre tres productos.
Computadora HP Optimux $ 1,000.00
Impresora HP 840C $ 150.00
Scanner HP 3500 $ 100.00
2. Hacer en la ficha botón
3. Hacer clic en el cuadro combinado
4. Agregar el control cuadro combinado sobre la celda A3
5. Diseñar los cuadros adicionales
SENATI-Computación e Informática 117
Microsoft Office Excel 2007
6. Hacer clic derecho sobre el control cuadro combinado y elegir la opción
7. Se presenta el siguiente cuadro de diálogo defina los datos mostrados en la
gráfica.
8. Las opciones del Cuadro combinado devuelven como resultado en la celda
vinculante:
1 = Si se eligió la primera opción, Computadora HP Optimux
2 = Si se eligió la primera opción, Impresora HP 640C
3 = Si se eligió la primera opción, Scanner 3500
9. Para resolver el problema, aplicaremos la función INDICE en la ceda A6
10. Ahora cada vez que hagamos clic, en las opciones de los productos, mostrará el
precio que le corresponde.
118 SENATI-Computación e Informática
Formularios
Paso a Paso: Ejemplos tipo con formularios
1. Abrir el archivo “Controles - para elaborar . XLSX”
2. El archivo contiene dos páginas: “Cotización” y “Lista de productos”
3. La idea es desarrollar la hoja de cotización. Comenzaremos con la hoja “Lista de
productos”. Asignaremos nombres de rango a la lista de productos y lista de
precios. Para hacer referencia a ellos desde la hoja de cotización.
Asignar nombre de rango a la lista de
productos
4. Seleccionar el bloque de celdas A2:A21
5. Hacer clic derecho sobre el bloque
seleccionado, del menú presentado elegir
la opción
6. Del cuadro de diálogo presentado en el
recuadro Nombre, escriba “Productos”
SENATI-Computación e Informática 119
,
Microsoft Office Excel 2007
7. Hacer clic en el botón para terminar
Asignar nombre de rango a la lista de
productos y precios
8. Seleccionar el bloque de celdas A2:B21
9. Hacer clic derecho sobre el bloque
seleccionado, del menú presentado elegir
la opción
10. Del cuadro de diálogo presentado en el
recuadro Nombre, escriba “ProducPrecio”
11. Hacer clic en el botón para terminar
Insertar control casilla de verificación en la hoja de cotización.
12. Hacer clic para cambiar a la hoja cotización
13. Hacer en la ficha botón
14. Hacer clic en el control casilla de verificación
120 SENATI-Computación e Informática
Formularios
,
15. Agregar este control sobre la celda A3
Hacer clic para asignar como nombre: “Tarjeta
Bonus”
16. Hacer clic derecho sobre este control y elegir la opción
Hacer clic para activar el control
Escribir $A$3 para vincular este control con la celda en mención
Insertar controles botones de opción
17. Hacer en la ficha botón
18. Hacer clic en el control botón de opción
19. Agregar tres de estos controles sobre las celdas
A5 … A9
Hacer clic derecho y luego clic izquierdo para al entrar al modo edición y cambiar el nombre de los controles. Asignar los nombres mostrados en la gráfica.
20. Hacer clic derecho sobre este control y elegir la opción
SENATI-Computación e Informática 121
Microsoft Office Excel 2007
Hacer clic para activar el control
Escribir $A$7 para vincular este control con la celda en mención
21. Repetir el paso 20 para vincular los otros dos controles de opción con la celda A7
Asignar validación de datos al rango de celdas A13:A18
22. Seleccionar el bloque de celdas A13:A18
23. Hacer clic en
24. Se presenta el siguiente cuadro de diálogo
Elegir la
opción “Lista”
Presione la tecla F3 para presentar la
lista de nombres de rango, elegir “Productos”.
25. Hacer clic en el botón para terminar
122 SENATI-Computación e Informática
Formularios
26. Su cuadro de diálogo queda como se muestra en la gráfica.
Adicionar las fórmulas
27. Ingresar las fórmulas mostradas para terminar el módelo
=SI(ESBLANCO(B13);"";C13*B13)
=SI(ESBLANCO(A13);"";BUSCARV(A13;ProducPrecio;2;0))
=SI(A3=VERDADERO;D19*10%;0)
=D19*ELEGIR(A7;10%;5%;0)
SENATI-Computación e Informática 123
Microsoft Office Excel 2007
Paso a Paso: Ejemplos tipo con formularios
1. Realizar el siguiente diseño y asigne a la hoja el nombre ENCOMIENDA.
ENCOMIENDA
Hoja ENCOMIENDA del libro Proformas.
2. Luego cree el siguiente diseño y asígnele el nombre a la hoja de COSTOS.
COSTO
Hoja COSTOS del libro Proformas.
3. Añada los controles de formulario a su hoja de cálculo, para tener un diseño de
acuerdo a la figura.
124 SENATI-Computación e Informática
Formularios
Formato de control
Para terminar con el diseño, debe modificar el formato de los controles para que
interactúen con celdas de su hoja de cálculo. Es decir, por ahora los controles
son sólo elementos que no se relacionan con la hoja de cálculo, al modificar el
formato de los controles, logramos que dichos controles se vinculen a celdas y
así podemos construir fórmulas basadas en los valores de esas celdas.
Para modificar el formato de un control
Use el menú contextual sobre el control. Elija Formato de control…
Opción Formato de control
Control numérico Kilogramos
Asignarle las siguientes propiedades:
SENATI-Computación e Informática 125
Microsoft Office Excel 2007
Valor actual : 0
Valor mínimo : 1
Valor máximo : 50
Incremento : 1
Celda vinculante : C7
Botones de opción Normal y Express
De igual manera, debe modificar el formato de cada uno de los controles de su
formulario.
En el caso de los botones de opción (Normal y Express) debemos indicar una
celda con la que se vincule H1.
Al elegir uno de los botones de opción, la celda H1 toma un valor.
Casilla de verificación Delivery
Además, se debe modificar el formato de la casilla de verificación (Delivery)
para que se vincule con una celda, si la casilla está marcada muestra
VERDADERO, si no muestra FALSO.
Debemos indicar una celda con la que se vincule H2.
Al activar Delivery, la celda H2 toma un valor lógico
Lista Destino
Finalmente, debe modificar el formato de la lista. En el caso de una lista o un
cuadro combinado, en necesario indicar un Rango de Entrada, es decir el rango
que tenga los elementos que desea mostrar en su lista. Además, debe vincular el
control a una celda de su hoja de cálculo. Asigne el nombre DESTINO al rango
B5:B11 (Hoja Costos).
126 SENATI-Computación e Informática
Formularios
Debemos definir un Rango de entrada DESTINO y una celda vinculante H3
Formato del control Lista
Observe que la lista se llena con las ciudades indicadas en el rango de entrada.
La lista se llena con las ciudades
Observe que al hacer sobre algunas de las ciudades, la celda H3 muestra el
número del elemento seleccionado de la lista.
La celda H3 muestra el número del elemento seleccionado
Fórmulas relacionadas a controles.
Gracias a las celdas vinculadas de sus controles, usted podrá definir ciertas
fórmulas basados en los valores de las celdas vinculadas.
SENATI-Computación e Informática 127
Microsoft Office Excel 2007
Calcular el Costo Básico
Debe multiplicar los Kilogramos por el Costo de acuerdo a la ciudad (vea
la tabla Costos).
Antes de realizar los cálculos asigne el nombre COSTO al rango C5:C11.
En la celda C16 de la Hoja Encomienda escriba:
=INDICE(COSTO; H3) * C7
Gracias a la función INDICE podemos ubicar directamente el costo en
base al número de elemento seleccionado de su lista (recuerde, la celda
H3 posee este valor).
Calcular el Recargo de Servicio
Debe considerar un recargo del 50% del Costo Básico sí el servicios es
Express. En la celda C17 escriba la fórmula:
=SI( H1=2; C16 * 50%; 0)
Recuerde:
El valor 2 en la celda H1 indica que se ha elegido el tipo Express
Calcular Delivery
Debe considerar S/. 10.00 si la casilla esta activa y 0 en caso contrario.
En la celda C18 escriba la fórmula:
=SI (H2 = VERDADERO; 10; 0)
Recuerde:
Si la casilla está activa la celda H2 muestra VERDADERO y sino
FALSO.
Completar fórmulas
Escribir fórmulas en base las celdas vinculadas de sus controles,
sabiendo:
Total del Servicio = Suma del Costo + Recargo + Delivery
IGV = 19% del Total del Servicio
Total a Pagar = Total del Servicio + IGV
Pruebe la funcionalidad de sus fórmulas, interactuando con sus controles, por
ejemplo, haga clic en su control número o elija otra ciudad.
128 SENATI-Computación e Informática
Capítulo
Macros
En este capítulo trataremos:
Seguridad de macros Grabar una nueva macro Editar una macro utilizando
Visual Basic Eliminar macros Uso de macros en
formularios Plantillas con formularios y
macros Desarrollo de proyectos con
macros y formularios
SENATI-Computación e Informática 129
Macros
Cada día las grandes y pequeñas empresas están descubriendo el poder de utilizar
conjuntamente las macros con Excel, las compañías requieren de programas
informáticos realizados a "medida" de sus necesidades, para gestionar de manera
automática, rápida y eficaz los procesos internos del negocio.
A la vez muchas personas en su trabajo diario realizan tareas repetitivas frente a sus
hojas Excel, desperdiciando días enteros en realizar informes de manera manual los
cuales, con un simple clic a un botón en cuestión de segundos estaría realizado.
Las macros nos permiten automatizar y realizar tareas complejas, aumentando la
eficiencia y eficacia del trabajo.
Definición
Las macros son un grupo de instrucciones programadas bajo entorno VBA (Visual
Basic para aplicaciones), cuya tarea principal es la automatización de tareas
repetitivas y la resolución de cálculos complejos.
El lenguaje VBA, es un lenguaje de programación basado en el Visual Basic,
enfocado a la realización de programas sobre las herramientas Excel, Access,
Word...., mediante macros en VBA podemos crear nuevas funciones para nuestras
hojas Excel, personalizar estilos y formatos, crear programas para la resolución de
cálculos complejos, automatizar tareas...
Ejemplo de código VBA aplicado en Excel
Una vez realizado el código VBA, podemos ejecutar el programa mediante la
creación de botones, nuevos menús... de tal forma que la aplicación creada resulte
intuitiva y de fácil manejo.
También puede crear una macro utilizando el Editor de Visual Basic.
Editor de Visual Basic
Entorno en el que puede escribir y modificar código y procedimientos de Visual
Basic para Aplicaciones.
SENATI-Computación e Informática 131
Microsoft Office
Excel 2007
El Editor de Visual Basic contiene un conjunto completo de herramientas de depuración para buscar sintaxis, tiempo de ejecución y problemas lógicos en el
código en uso en Microsoft Visual Basic para escribir sus propias secuencias de
comandos de macro o para copiar toda o parte de una macro en una nueva macro.
Una vez creada una macro, puede asignarla a un objeto (como un botón de barra
de herramientas, un gráfico o un control) para que pueda ejecutarla haciendo clic
en ese objeto.
Sin embargo es importante mencionar que usted debe contar con conocimientos
de programación y conocer los objetos de Microsoft Excel.
Para que se utilizan las Macros
Mediante macros VBA podemos crear múltiples aplicaciones que realicen los
cálculos automáticamente y obtengamos resultados en apenas unos segundos.
Nos permite automatizar procesos y evitar estar dependiendo de personas con
conocimientos superiores para el manejo de procesos diseñado especialmente para
las necesidades de los usuarios.
Las macros VBA trabajan en diversos programas, los más conocidos son los
programas que se incluyen en el paquete Office (Excel ,Word, Access...) pero
además las macros VBA también trabajan en diversos programas como
SolidWorks, Autocad... (software creado para el diseño de ingeniería) lo cual
proporciona una enorme versatilidad de utilización, así como una comunicación
entre diversos programas. 132 SENATI-Computación e Informática
Macros VBA con Excel
Creación de nuevas funciones- como por ejemplo la función "código" que
convierte dígitos alfanuméricos a dibujos basados en codificación de código de barras.
Automatización de tareas repetitivas- realización de informes automáticos,
conexión con bases de datos y otros programas.
Resolución de cálculos complejos e iterativos
Creación de programas, como gestores documentales, software de
planificación.
Ventajas
Realización de programas a medida
Cada empresa posee sus peculiaridades y características, en muchas
ocasiones se adquieren paquetes de software estandarizado que no
cumplen las expectativas iníciales y que acaban por no aportar una
solución real a las necesidades del negocio, mediante las macros en
Excel cada programa desarrollado se adapta fácilmente a cada tipo de
empresa, proporcionando un versatilidad y flexibilidad incomparable.
Rápido y fácil manejo
De los programas desarrollados bajo entorno Excel, cuando la
empresa adquiere un software nuevo ha de dedicar cierta parte de
tiempo en adquirir los conocimientos necesarios para el buen
manejo del software, la gran ventaja que nos aporta las macros es
que están desarrolladas bajo Excel, herramienta muy conocida y utilizada en
la mayoría de las empresas.
Realización de tareas y cálculos complejos
En muchas ocasiones dejamos de utilizar técnicas conocidas para la
resolución de problemas, que nos podrían aportar datos de vital
importancia, por la gran complejidad de cálculo que conllevan,
mediante macros en Excel estas tareas y cálculos pasaran a la
historia, realizando la propia hoja Excel el trabajo por nosotros.
Aumento de eficacia y eficiencia
En el trabajo, puesto que reducimos horas y horas de nuestro
trabajo en realizar tareas manuales al convertirlas en automáticas,
ocupando el tiempo ganado en otros asuntos.
SENATI-Computación e Informática 133
Microsoft Office
Excel 2007
Seguridad de Macros Los Macrovirus
Los macro virus son una nueva familia de virus que infectan documentos y hojas
de cálculo. Fueron reportados a partir de Julio de 1995, cambiando el concepto de
aquella época, de que los virus tan sólo podían infectar o propagarse a través de
archivos ejecutables con extensiones .EXE o .COM
Hoy en día basta con abrir un documento en Word o una hoja de cálculo de Excel
infectados para que un sistema limpio de virus sea también infectado.
Los macro virus tienen 3 características básicas:
Infectan documentos de MS-Word o MS-Excel y archivos de bases de
datos en MS-Access.
Poseen la capacidad de infectar y auto-copiarse en un mismo sistema, a
otros sistemas o en unidades de red a las cuales estén conectadas.
Haciendo uso de las funciones de la interfaz de las librerías MAPI
(Messaging Application Programming Interface), desde el sistema infectado se envía a todos los buzones de la libreta de direcciones de MS
Outlook y Outlook Express.
Parte del MacroVirus Melissa
Private Sub AutoOpen()
On Error Resume Next
p$ = "clone"
If System.PrivateProfileString("", "HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Word\Security", "Level") <> "" Then
CommandBars("Macro").Controls("Security...").Enabled = False
System.PrivateProfileString("", "HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Word\Security", "Level") = 1&
Else
p$ = "clone"
CommandBars("Tools").Controls("Macro").Enabled = False
Options.ConfirmConversions = (1 - 1): Options.VirusProtection = (1 - 1): Options.SaveNormalPrompt = (1 - 1)
End If
. . .
. . .
End sub 134 SENATI-Computación e Informática
A pesar de que los macro virus son escritos en los lenguajes macro de MS-Word o MS-Excel y por consiguiente deberían infectar únicamente a documentos y hojas
de cálculo, es posible desarrollar macro virus que ejecuten llamadas al sistema
operativo, dando órdenes de borrar archivos o hasta de reformatear al disco duro.
Otra característica de los macro virus es que sus acciones están destinadas
exclusivamente a un tipo de documento, hoja de cálculo o archivo de base de
datos, creados en MS-Word, MS-Excel y MS-Access.
Cada vez aparecen y se propagan una mayor cantidad de macro virus que los virus
de archivos ejecutables y esto se debe a dos simples razones:
Los macro virus a pesar de tener acciones muy sofisticadas en sus
procesos de infección, son sumamente fáciles de crear o modificar, pues
tan sólo es necesario tener nociones de programación en lenguaje macro.
Incluso se distribuyen Generadores de Macro Virus en muchos sitios de
Internet.
Ahora todos los usuarios intercambian más documentos que archivos
ejecutables, ya sea a través de diskettes, correo electrónico u otro medio, lo cual alienta a los desarrolladores de virus.
Ayudar a proteger archivos de virus en macros
Las macros se graban en el lenguaje de programación Visual Basic para
Aplicaciones.) dentro de un archivo, plantilla (plantilla: archivo o archivos que
contienen la estructura y las herramientas para dar forma a elementos como el
estilo y el diseño de página de los archivos terminados.
Para reducir más el riesgo de infección con virus en los archivos de Office,
establezca el nivel de seguridad de las macros y use firmas digitales (firma digital:
sello electrónico seguro basado en cifrado para autenticar una macro o un
documento. Esta firma garantiza que la macro o el documento fueron creados por
el firmante y que no se han modificado.).
Para aplicar seguridad hacer los siguientes pasos:
o Hacer clic en la ficha de la cinta de opciones
o
Hacer clic en el botón
SENATI-Computación e Informática 135
Microsoft Office
Excel 2007
Firmas digitales
Una firma digital en una macro es como un sello de cera en un sobre: confirma
que la macro se originó en el programador que la ha firmado y que no se ha
modificado.
Al abrir un archivo o cargar un complemento que contenga una
macro con firma digital, esta firma aparece en el equipo en
forma de certificado. El certificado menciona la fuente de la
macro, y otra información acerca de la identidad e integridad de
la fuente. Una firma digital no garantiza necesariamente la
seguridad de una macro, por ello el usuario debe decidir si
confiar en una macro que lleva firma digital.
¿Cómo obtener un certificado Digital?
Puede obtener un certificado digital de una autoridad de certificación comercial,
como VeriSign, Inc. o de su administrador de seguridad interna o profesional de
Tecnologías de la Información (TI). También puede crear una firma digital
mediante la herramienta Selfcert.exe.
Para obtener más información acerca de las autoridades de certificación que
ofrecen servicios para productos de Microsoft, vea la lista de Microsoft Root
Certificate Program Members (Integrantes del programa de certificados raíz de
Microsoft).
Para agregar un certificado digital
Hacer clic en la ficha , botón
Hacer clic en el menú , opción
Se presenta el cuadro de diálogo
Hacer clic en el botón
Se presenta el siguiente cuadro de diálogo 136 SENATI-Computación e Informática
Elegir el certificado que desea utilizar
Lista de editores de confianza
Al abrir un archivo que incluye macros con firma, se le pregunta si desea confiar
en todas las macros originadas en ese editor.
Si selecciona esta opción, agrega el propietario del certificado a su lista de
editores de confianza. Antes de decidirse, debe revisar los detalles del certificado
digital, como por ejemplo, mirar los campos Emitido para y Emitido por, para
determinar si confía en el editor, y mirar el campo Válido desde para determinar si
el certificado es actual.
El certificado también puede incluir detalles como la dirección de correo
electrónico o e sitio Web de la persona que lo ha obtenido.
Una vez que haya agregado una persona (o corporación) a la lista de editores de
confianza, Office activará las macros firmadas por este editor sin mostrarle una
advertencia de seguridad.
De todos modos, se puede eliminar entradas de la lista de editores de confianza.
Nota. Cualquier certificado que figure como certificado de confianza en la lista
de editores de confianza será también considerado de confianza en Internet
Explorer.
Advertencias acerca de plantillas y complementos instalados
Al abrir una plantilla o cargar un complemento desde la carpeta Inicio, las macros
del archivo se pueden activar automáticamente.
De todos modos, puede desactivar la opción de seguridad Confiar en todas las
plantillas y complementos instalados, para recibir una advertencia acerca de estas
macros. La advertencia variará en función del nivel de seguridad que haya
elegido.
SENATI-Computación e Informática 137
Microsoft Office
Excel 2007
Grabar una nueva Macro
Hacer clic en la ficha , botón
Se presenta el siguiente cuadro de diálogo
Escribir el nombre de la
Macro
Especificar donde se grabará la
Macro
Escriba una descripción de lo que realiza su Macro
Paso a Paso: Grabar una Macro
1. Abrir el archivo “5 Macro1”.
2. En la siguiente hoja de cálculo crear una macro que permita automáticamente
ordenar la tabla por categoría del empleado.
3. Hacer clic en la ficha , botón
138 SENATI-Computación e Informática
,
4. Se presenta el siguiente cuadro de diálogo
Escriba como nombre de la Macro “OrdenarLista”
Hacer clic en el botón
5. A partir de este momento todo acción que realice se grabará como parte d e la
Macro
6. Presione las teclas Ctrl + Inicio para ubicarse en la primera celda.
7. Haga un clic en la celda A4, luego presione [Ctrl] + [*] para seleccionar toda la
base de datos.
8. Hacer clic en la ficha elegir
Elegir la opción “CATEGORÍA”
9. Hacer clic en la ficha , botón
SENATI-Computación e Informática 139
Microsoft Office
Excel 2007
Editar una Macro utilizando Visual Basic La macro se graba en código de Visual Basic en una hoja de módulo.
Hacer clic en la ficha , botón
Del cuadro de diálogo presentado elegir su macro.
Elegir la Macro
“OrdenarLista”
Hacer clic en el botón se muestra el código
Ahora puede modificar el código según sus nuevos requerimientos.
140 SENATI-Computación e Informática
Eliminar macros Para eliminar una Macro seguir el siguiente procedimiento.
Hacer clic en la ficha , botón
Del cuadro de diálogo presentado elegir su macro.
Elegir la Macro que
desea eliminar
Hacer clic en el botón se muestra el código
Referencias Relativas Una referencia es la ubicación de una celda en Microsoft Excel, tal como A1, B4.
En Microsoft Excel se tiene dos tipos de referencia: Absoluta o relativa.
Si configura el tipo de referencia en absoluta, Microsoft Excel lleva un control de
la posición exacta de cada celda seleccionada. Por ejemplo si crea una macro que
subraya la celda A1, cuando ejecute la macro siempre va a afectar a la celda A1.
Si configura el tipo de referencia en relativa, Microsoft Excel lleva un control de
la posición de cada celda seleccionada en relación a la celda seleccionada con
anterioridad. Por ejemplo si me ubico en la celda A1 y crea una macro que pone
en subrayado a la celda B2. Observe que B2 se encuentra una celda a la derecha y
una celda hacia abajo de la celda A1.
Si me ubico en la celda A4 y ejecuto la macro se pondrá en subrayado la celda B5.
Ya que esta es la celda que se encuentra una celda más a la derecha y una celda
más hacia abajo.
SENATI-Computación e Informática 141
Microsoft Office
Excel 2007
Como usted puede observar configurar como absoluta o relativa tiene sus ventajas según el caso presentado.
Para lograr que las celdas empleadas en su macro sean consideradas como
relativas, al grabar una Macros, debe activar Referencias relativa.
Usted puede combinar celdas absolutas y relativas en una macro.
Diferencia entre celda relativa y absoluta
Celda absoluta
Hace referencia siempre a una celda fija.
Ejemplo:
Range(“A1”).Select Selecciona la celda A1
Celda relativa
Se basa en una referencia de celda, la cual si cambia la
posición de las celda que involucra la sentencia, esto se
verá reflejado en el resultado.
Ejemplo:
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Realiza la fórmula n = n +1
Paso a Paso: Crear Macro con referencia relativa
1. Hacer clic en la ficha , botón
Uso de Macros en Formularios Es posible asignar Macros a los controles de un formulario, esto permite mejorar la
interfaz de su aplicación y automatizar sus tareas.
Paso a Paso: Asignar Macro a botones
1. Abrir el archivo “5 Macro1”.
2. En la siguiente hoja de cálculo crear tres Macros:
142 SENATI-Computación e Informática
- Ordenar_Por_Apellidos
- Ordenar_Por_Ocupación
- Ordenar_Por_Sueldo
3. Hacer clic en la ficha , botón
4. Se presenta el siguiente cuadro de diálogo
Escriba como nombre de la Macro “Ordenar_Por_Apellidos
Hacer clic en el botón
5. A partir de este momento todo acción que realice se grabará como parte de la
Macro
6. Presione las teclas Ctrl + Inicio para ubicarse en la primera celda.
7. Haga un clic en la celda A4, luego presione [Ctrl] + [*] para seleccionar toda la
base de datos.
SENATI-Computación e Informática 143
Microsoft Office
,
,
Excel 2007
8. Hacer clic en la ficha elegir
Elegir la opción “APELLIDOS Y NOMBRES
9. Hacer clic en la ficha , botón
10. Repetir este proceso para crear a las otras dos Macros.
11. Adicionar los botones de comando a la hoja
12. Hacer en la ficha botón
13. Hacer clic en el control Botón
14. Al dibujarla en la hoja de cálculo se presenta el
siguiente cuadro de diálogo
Elegir “Ordenar_Por:Apellidos” y hacer clic en el botón
15. Agregar los otros dos botones y asignar la Macro, su hoja debe quedar como se muestra en la gráfica, cada vez que presione los botones se ordenará la tabla.
144 SENATI-Computación e Informática
Paso a Paso: Asignar Macro a botones de opción
1. Abrir el archivo “5 Macro1”.
2. En la siguiente hoja de cálculo crear tres Macros:
- Filtrar_Afil_AFP
- Sueldo_Mayor_30000
- Elimina_Filtro
3. Hacer clic en la ficha , botón
4. Se presenta el siguiente cuadro de diálogo
SENATI-Computación e Informática 145
Microsoft Office
,
Excel 2007
Escriba como nombre de la Macro “Filtra_Afil_AFP”
Hacer clic en el botón
5. A partir de este momento todo acción que realice se grabará como parte de la Macro
6. Presione las teclas Ctrl + Inicio para ubicarse en la primera celda.
7. Haga un clic en la celda A4, luego presione [Ctrl] + [*] para seleccionar toda la
base de datos.
8. Hacer clic en la ficha elegir
Hacer clic sobre el filtro
“A_AFP”.
Desactive la casilla “N”
146 SENATI-Computación e Informática
,
9. Hacer clic en la ficha , botón
10. Repetir este proceso para crear a las otras dos Macros.
11. Adicionar los botones de opción en la hoja
12. Hacer en la ficha botón
13. Hacer clic en el control Botón
14. Dibujar los tres botones de opción en la hoja y asignar
sus nombres según la gráfica
15. Hacer clic derecho sobre uno de los botones de opción
16. Del menú contextual presentado elegir la opción
Elegir la opción “Filtra_Afil_AFP”
Hacer clic en el botón
para
terminar
17. Repetir este procedimiento para signar Macro a los otros dos botones de opción
SENATI-Computación e Informática 147
Microsoft Office
1.
Hacer clic en el botón
Botón Office,
2.
Hacer clic en el botón
Excel 2007
Plantillas con formularios y Macros Para mejorar la forma de trabajo con archivos semejantes que se generan cada mes,
puede diseñar un modelo y grabarlo como plantilla, esta plantilla puede ncluir Macros y
controles de formulario.
Una plantilla es un archivo que se utiliza como base para generar nuevos archivos los
cuales tienen las mismas características.
Paso a Paso: Uso de plantillas predefinidas
3. Se presenta el siguiente cuadro de diálogo
Seleccionar la opción Plantillas instaladas
Seleccionar una de las plantillas mostradas
4. Hacer clic en el botón
148 SENATI-Computación e Informática
Paso a Paso: Uso de plantillas de MS Office Online
1.
Hacer clic en el botón
Botón Office,
2.
Hacer clic en el botón
3. Se presenta el siguiente cuadro de diálogo
Seleccionar una de
las plantillas
Microsoft Office
Online Seleccionar una de
las plantillas mostradas
4. Hacer clic en el botón
5. A continuación se muestran algunas plantillas de Ms Office OnLine
SENATI-Computación e Informática 149
Microsoft Office
Excel 2007
Crear Plantillas personalizadas
1. Para crear una plantilla diseñe su hoja de cálculo y al guardarla elegir el tipo
150 SENATI-Computación e Informática
2. De esta forma puede utilizar esta plantilla para generar nuevos archivos
Utilizar Plantillas personalizadas
1. Para utilizar una plantilla hacer clic en el botón Office ,
2. Se presenta el siguiente cuadro de diálogo
Elegir la plantilla
que
quiera
utilizar
Elegir plantillas
Desarrollo de proyectos con Macro y formularios La Empresa JH Import's dedicada al Rubro de Importación y Exportación de Productos
en general, ha adquirido una Computadora Core 2 DUO, 2GHZ y 1GB RA, Disco duro
80GB para automatizar los procesos que realiza.
La Empresa cuenta actualmente con 50 trabajadores y dentro de los primeros procesos
que desea automatizar es de un Sistema para el Control de Pagos implementando
controles y Macros a su solución actual, para lo cual ha contratado los Servicios de un
Profesional de Computación de SENATI, el cual será el encargado de desarrollar esta
aplicación.
SENATI-Computación e Informática 151
Microsoft Office
Excel 2007
Consideraciones
El Programa que se determinó utilizar será MICROSOFT EXCEL For Windows,
ya que se maneja poca información y dado el conocimiento de su personal del
Suite Office.
El Sistema de Planillas será almacenado en el archivo PLANI2008.XLS y el
análisis ha determinado que será distribuido en varias hojas de cálculo, según el
siguiente flujograma de datos.
Flujo Lógico del Programa
Proceso de Automatización
1. Automatización del Sistema de Planillas
1.1. Crear un nuevo Libro o Cuaderno de Trabajo
1.2. Asignar nombres a las Hojas de Cálculo
Para una mejor comprensión de donde se encuentra cada Información, se le
asignarán nombres a todas las hojas según el Flujo Lógico del Programa (ver
parte inferior del libro).
En adelante nos referiremos a las hojas por sus respectivos nombres.
1.3. Automatización de la Planilla
152 SENATI-Computación e Informática
Recomendamos para los Nombres de Campos utilizar un tipo de letra diferente al de los datos. Ejemplo: Nombres de Campo: Letra Times New Roman 12 puntos y Datos: Letra Arial 10 puntos.
Formatear las Hojas de Cálculo según su criterio y darle un aspecto
presentable y agradable (utilizar tipos de letras, bordes, sombreados, datos
numéricos a 2 decimales y en millares, etc.)
Hoja: Datos Personales
Lo único que tiene que hacer, es ingresar los datos de los trabajadores
conforme se muestra en la hoja de Datos Personales y darle un formato
apropiado.
Hoja: Tabla AFP
Lo único que tiene que hacer, es ingresar los datos de descuentos de la Tabla
de AFP conforme se muestra en la hoja de Tabla AFP y darle un formato
apropiado.
Hoja: Planilla
A. El único dato ingresado será el No. DE CARNET.
B. Los datos: APELLIDOS Y NOMBRES, OCUPACION, AFILIADO
A AFP, CODIGO DE AFP y SUELDO BASICO deben ser extraídos
de la hoja Datos Personales mediante el campo No. DE CARNET.
Para realizar esta operación de búsqueda y extracción de datos
utilice la función BUSCARV...
Recomendamos utilizar el Asistente para funciones.
Ejemplo: Para extraer los APELLIDOS Y NOMBRES mediante
el Campo NUMERO DE CARNET.
Pasos:
1.- Ubicarse en la Hoja Datos Personales y anote el No. de
columna donde se encuentra el Campo a extraer
APELLIDOS Y NOMBRES (ver Hoja Datos Personales
celda B57)
2.- Ahora ubíquese en la Hoja Planilla, en la celda B10 y
realice lo siguiente
3.- Haga Clic en el icono Asistente para funciones o en el
menú Insertar, Función...
4.- Luego, haga Clic en Búsqueda y referencia, BUSCARV
5.- Haga Clic en el primer dato del campo No. CARNET de
la Hoja Planilla (Celda A10), mediante el cual se extrae
la información.
6.- Haga Clic en el recuadro de matriz_a_buscar_en, luego
en la Hoja Datos Personales seleccione el rango A6..K55
y presione la tecla F4 (celdas absolutas), lo cual permitirá
SENATI-Computación e Informática 153
Microsoft Office
Excel 2007
realizar la búsqueda del dato de la celda A10 en el rango especificado.
7.- Haga Clic en el recuadro de indicador_columnas y digite
2 No. de columna a extraer.
8.- Haga Clic en el botón Terminar y copie la fórmula (B6 a
B59) para extraer los APELL. Y NOMBRES.
C. Cálculo de los Ingresos
Por política de la empresa se le asignará a todos los trabajadores
5% de Bonificación por Costo de Vida el cual será deducido del
sueldo básico.
BONIFICACION = 5% del SUELDO BASICO
Por acuerdo con el sindicato de la Empresa se acordó asignar 10
soles por c/hijo como asignación familiar.
ASIGNACION FAMILIAR = 10 * Número de hijos
El número de hijos debe ser extraído de la hoja de Datos
Personales. Utilizar función BUSCARV...
El INCREMENTO es de 10% y sólo se les asignará a los
trabajadores afiliados a una AFP.
Recomendamos utilizar la función lógica =SI... para realizar este
cálculo.
El INCENTIVO es de 3% y sólo se le asignará a los empleados
afiliados a una AFP.
Recomendamos utilizar la función lógica =SI... para realizar este
cálculo.
El Total de Ingresos se obtiene de la suma de los siguientes
campos:
TOTAL INGRESOS = SUELDO BASICO + BONIFICACION +
ASIG. FAMILIAR + INCREMENTO + INCENTIVO
D. Aportaciones del Empleado
Cuando un trabajador es contratado o estable se le descuenta por
Ley IPSS, SNP y FONAVI.
Prestaciones de salud 0% el cual será deducido del Total de
Ingresos.
IPSS = 0% del TOTAL DE INGRESOS
154 SENATI-Computación e Informática
SNP (Seguro Nacional de Pensiones) sólo se le debe descontar
11% deducido del Total de Ingresos, a los trabajadores afiliados
al SNP (no estar afiliados a una AFP).
Recomendamos utilizar la función lógica =SI... para realizar este
cálculo.
SNP = 11% del TOTAL DE INGRESOS
FONAVI (Fondo Nacional de Vivienda) 0% el cual será
deducido del Total de Ingresos
FONAVI = 0% del TOTAL DE INGRESOS
Aportaciones de los trabajadores Afiliados a una AFP
A los trabajadores afiliados a una AFP se le descuentan
adicionalmente los siguientes rubros:
CUENTA INDIVIDUAL = % de Cta. Individual * Total de ingresos
SOLIDARIDAD IPSS = % de solid. IPSS * Total de ingresos
SEG. INV. GTOS. SEPELIO = % Seg. Inv. Gtos. sepelio * Total de ing
COMISION FIJA = VALOR DE LA COMISION FIJA COMISION
VARIABLE = %Comisión variable * Total de ingresos.
Para Calcular los datos recomendamos utilizar la función lógica
=SI(...BUSCARV(...
Ejemplo.
=SI(esta Afiliado a AFP,Extraer el % de descuento de la Tabla
AFP * Total Ingresos,Caso Contrario 0)
Fórmula Hoja Planilla Celda O10: Cta Individual
=SI(D10="S";BUSCARV(E10;'Tabla AFP'!$B$6:$H$12;3);0)*K10
5ta CATEGORIA se le dscta. 15% sobre la cantidad excedida, si
su Sueldo (Total Ingresos) excede a 1,200
Recomendamos utilizar la función lógica =SI... para realizar este
cálculo.
El Total de Aportaciones del Empleado se obtiene de la suma de
lo siguientes campos:
TOTAL DSCTO = IPSS+ SNP + FONAVI + CTA. INDIV. +
SOLID. IPSS + SEG. INV. GTOS. SEPELIO + COMISION
FIJA + COMISION VARIABLE + 5ta CATEGORIA
E. Cálculo del Total a Pagar
TOTAL PAGAR = TOTAL INGRESOS - TOTAL DE DESCUENTOS
F. Aportaciones del Empleador - referencial para está planilla
Cuando un trabajador es contratado o estable, la Empresa va a aportar
por este trabajador por:
SENATI-Computación e Informática 155
Microsoft Office
Excel 2007
Prestaciones de salud 9% el cual será deducido del Total de
Ingresos.
IPSS = 9% del TOTAL DE INGRESOS
SNP (Seguro Nacional de Pensiones) sólo se le debe descontar
0% deducido del Total de Ingresos, a los trabajadores afiliados al
SNP (no estar afiliados a una AFP).
Recomendamos utilizar la función lógica =SI... para realizar este
cálculo.
SNP = 0% del TOTAL DE INGRESOS
FONAVI (Fondo Nacional de Vivienda) 9% el cual será
deducido del Total de Ingresos
FONAVI = 9% del TOTAL DE INGRESOS
El Total de Aportaciones del Empleador se obtiene de la suma de
lo siguientes campos:
TOTAL APORTACIONES DEL EMPLEADOR = IPSS + SNP +FONAVI
MANTENIMIENTO DE LA PLANILLA
Una vez culminada la Planilla:
Copiar la hoja Planilla a Plani Ago 2008 (Hoja Histórica)
Copiar todos las fórmulas a valores (para que no cambien su valor si hay
modificaciones)
Ahora puede generar las Planillas de los siguientes meses, guardando como una
Hoja Histórica la Planillas ya realizadas.
DISEÑAR LA HOJA DE DATOS PERSONALES
156 SENATI-Computación e Informática
DISEÑAR LA HOJA DE TABLA AFP
DISEÑAR LA HOJA DE PLANILLA
DISEÑAR LA HOJA BOLETA DE PAGO
SENATI-Computación e Informática 157
Microsoft Office
Excel 2007
Cuestionarios
1. Cuáles son los pasos para crear una macro.
2. Es posible asignarle un atajo a una macro, para ejecutarlo rápidamente.
3. Para programar en una Macro, que lenguaje de programación se utiliza.
4. Qué es un Macrovirus.
5. Cual es la utilidad de la creación de plantillas, aplicando formularios y macros.
158 SENATI-Computación e Informática
Capítulo
Programación con VBA
En este capítulo trataremos:
Programación con Visual Basic
SENATI-Computación e Informática 159
Microsoft Office
Excel 2007
Introducción al Visual Basic Microsoft VBA (Visual Basic for Applications) es el lenguaje de macros de Microsoft
Visual Basic y viene integrado en aplicaciones de Microsoft Office, como Word, Excel
y Access, Powerpoint y Visio. VBA permite acceder a las
funcionalidades de un lenguaje orientado a eventos con acceso a
la API de Windows.
La utilidad de VBA es automatizar tareas cotidianas, el programa
generado sólo se puede compilar con el documento, hoja o base
de datos en que fue creado
Visual Basic es una herramienta de diseño de aplicaciones para
Windows, en la que estas se desarrollan en una gran parte a partir del diseño de una
interface gráfica. En una aplicación Visual Basic, el programa está formado por una
parte de código puro, y otras partes asociadas a los objetos que forman la interface
gráfica.
Objetos Procedimientos
Código
Es por tanto un término medio entre la programación tradicional, formada por una sucesión lineal de código estructurado, y la programación orientada a objetos.
Combina ambas tendencias. Ya que no podemos decir que Visual Basic
pertenezca por completo a uno de esos dos tipos de programación, debemos
inventar una palabra que la defina: PROGRAMACION VISUAL.
Pasos para la creación de un programa en VBA
La creación de un programa bajo Visual Basic lleva los siguientes pasos:
Análisis, planteamiento lógico de la solución del problema, diagrama de flujo,
diseño del formulario, programa.
160 SENATI-Computación e Informática
ción con VBA
Análisis
En esta etapa se construye un modelo del problema extraído del mundo real
especificando los elementos que alimentan el proceso (especificaciones de
entrada), los elementos que se espera produzca el proceso (especificaciones de
salida) y se define lo mejor posible al problema en sí mismo.
Para poder definir bien un problema es conveniente responder a las siguientes
preguntas
1. ¿Qué es lo que me pide que realice el problema?
2. ¿Qué datos se requieren ingresar, analice el tipo de dato que necesita
(numérico, texto, fecha, hora, …) y su valor inicial?
3. ¿Qué resultado desea hallar, cálculos, reportes, consultas, analice el
tipo de dato?
4. ¿Qué método puedo utilizar para encontrar este resultado?
Requerimientos del análisis del problema:
Análisis del problema
Definición del
problema
Definir datos de entrada
Definir datos de salida
Planteamiento lógico de la solución del problema.
El planteamiento lógico de basa en la idea que uno tiene para resolver el
problema, basado en un modelo matemático o secuencia de procesos (leer
datos, registrar datos, consultar datos, reportar datos, … etc.).
Diagrama de flujo
Es una herramienta que permite plantear una solución lógica a un problema de
computadoras.
Diseño del formulario
Es la interfaz de comunicación hombre máquina, tanto para salida de datos
como para entrada.
Programa
El programa está distribuido en: Las propiedades de los controles, los
procedimientos (Eventos Click, keypress, etc.)
SENATI-Computación e Informática 161
Microsoft Office
Excel 2007
Fundamentos de programación VBA Los algoritmos se estructuran de diversas formas, en algunos casos simplemente su
desarrollo es consecutivo, a este tipo de algoritmos se denomina de estructura
secuencial, en otros casos durante el desarrollo secuencial se generan preguntas a este
tipo de algoritmos se denomina de estructura condicional y en otros casos generan
bucles es decir repetición de ciertas líneas de programas, a estos algoritmos se
denominan de estructura repetitiva.
Estructura básica para cualquier algoritmo a desarrollar:
Declaración de
variables Captura de
datos Proceso de
datos Salida de
información
Estructura Secuencial.
Este tipo de algoritmos se caracteriza por que entre sus
instrucciones no existen estructuras condicionales ni
repetitivas, se desarrollan línea a línea hasta culminar con su
ejecución, gráficamente se observaría de la siguiente manera: 162 SENATI-Computación e Informática
ción con VBA
Ejemplo
Diseñar un programa que permita hallar el área de un triangulo rectángulo si se
sabe: Área_triángulo = (B * H) / 2
Donde B es base del triangulo y H es la altura.
1. Análisis
i. ¿Qué te piden que realices?
Hallar el área de un triangulo.
ii. ¿Qué datos necesito conocer?
Según la fórmula que se muestra debería de conocer la base y la altura.
2. Planteamiento Lógico.
El problema se resuelve con una fórmula matemática AR = B*H
3. Definición de variables de entrada
Las variables que se usaran para la captura de la base y la altura son: B y H.
4. Definición de variables de salida
La variable en donde se muestra el área del triangulo rectángulo es: AR
5. Programa
Sub AreaTriangulo()
Dim a B as integer
Dim H as integer
Dim AR as single
B= val (Textbox1.text)
H = val (Textbox¨2.text)
AR = (B*H) / 2
Textbox2.text = AR
End Sub
Estructura Condicional.
Este tipo de algoritmos se caracteriza por que entre sus instrucciones muestran
estructuras condicionales.
a. Condiciones Simples. Sentencia SI – ENTONCES
Se ejecuta un conjunto de instruciones si se cumple la condición
V
Condición Instrucciones
SENATI-Computación e Informática 163
Microsoft Office
Excel 2007
b. Condiciones doble. Sentencia SI – ENTONCES – SINO
Se ejecuta un conjunto de instruciones si se cumple la condición, caso
contrario se ejecuta otro conjunto de instrucciones.
F V
Condición
Instrucciones_B Instrucciones_A
Ejemplo
Realizar un algoritmo que permita ingresar 2 números, luego determinar
si el primer número ingresado fue el mayor (mostrar un mensaje).
1. Análisis.
i. ¿Qué te piden que realices?
Evaluar 2 números para determinar si el primer número ingresado fue el mayor.
ii. ¿Qué datos necesito conocer?
Los 2 números.
2. Planteamiento Lógico.
La forma directa de poder saber si un número es mayor a otro es
creando una condición relacional. A > B
3. Definición de variables de entrada.
Se requerirán dos variables, N1 y N2 que representen a los números
que se evalúan.
4. Definición de variables de salida.
Para este problema no existirán variables de salida debido a que se
desea mostrar solo mensajes.
5. Programa
Private Sub CommandButton1_Click()
N1 = val(text1.text)
N2 = val(text2.text)
If n1>n2 then
164 SENATI-Computación e Informática
ción con VBA
Else
End If
End Sub
Textbox1.text = “El primer número es el mayor”
Textbox1.text = “El segundo número es el mayor”
6. Diagrama de Flujo
INICIO
Declaración de variables N1, N2: entero
N1, N2
F V
A>B
El primer número no es mayor
El primer número es mayor
FIN
Sentencia selección-caso
Esta es una estructura de decisión múltiple, evaluará una expresión condicional
que podrá tomar uno de los “n” valores distintos que para algunos casos puede
tratarse de rangos o valores individuales, según cumpla con uno de estos.
SENATI-Computación e Informática 165
Microsoft Office
Excel 2007
Ejemplo
Un movil recorre un tramo de la carretera con Movimiento Rectilíneo Uniforme
(MRU), determinar y mostrar cual es el espacio recorrido:
Espacio = Velocidad * Tiempo
Adicionalmente mostrar un mensaje que indique el consumo de gasolina según la
tabla:
Espacio Recorrido
Gasolina
0 y 30
1 galón
31 y 60
2 galones
61 y 200
3 o más galones
1. Análisis.
ii. ¿Qué te piden que realices?
Calcular el espacio recorrido y en base a ello mostrar cuanta gasolina se consume.
iii. ¿Qué datos necesito conocer?
La velocidad y el tiempo (según formula).
2. Planteamiento Lógico.
El desarrollo es simple, solamente deberá ingresar la velocidad y el tiempo
para calcular el espacio recorrido, en base a ello deberá observar la tabla para
que desarrolle la estructura correspondiente y muestre el mensaje solicitado.
Ejemplo:
Si el espacio recorrido es de 25 kilómetros
El mensaje es 1 galón.
3. Definición de variables de entrada.
Se requerirán dos variables, V y T que representen a la velocidad y el tiempo
respectivamente.
4. Definición de variables de salida.
La variable de salida estará representada por E.
5. Programa.
Private Sub CommandButton1_Click()
v = val(text1.text)
t = val(text2.text)
e = v*t
Select case e
case 0 to 30
166 SENATI-Computación e Informática
ción con VBA
text3.text =”Debe usar un galón”
case 31 to 60
text3.text =”Debe usar dos galones”
case 61 to 200
text3.text =”Debe usar tres galones”
End select
End Sub
6. Diagrama de flujo.
INICIO
Declaración de variables V, T, E: entero
V, T
E = V * T
E
31 y 60
0 y 30 60 y 200
Debe
usar 1
galón
Debe
usar 2
galones
Debe
usar 3 o
más
galones
E
FIN
Estructura Repetitiva.
Conjunto de instrucciones que se repiten un número determinado de veces
mientras se cumple una determinada condición o en todo caso se le ha dado un
límite de veces a ejecutar.
Inicio de Bucle
Instrucción 1
Instrucción N
Fin de Bucle
SENATI-Computación e Informática 167
Microsoft Office
Excel 2007
Contador. Los procesos repetitivos por lo general lo utilizan, ya que necesitan
contar los sucesos o acciones internas del bucle. Una Inicio de Bucle
C = C + 1
de las formas de controlar un bucle es mediante un
contador. Un contador es una variable cuyo valor
crece o decrece en una cantidad constante por cada
vuelta (interacción) que da el bucle.
Inicio de Bucle
Fin de Bucle
Acumulador. Es denominado también totalizador,
es una variable cuya misión es almacenar cantidades
o valores resultantes de sumas sucesivas. Realiza la
misma función que un contador con la diferencia de
que el incremento o decremento de cada suma es
variable en lugar de constante como en el caso del
contador.
N
AC = AC + N
Fin de Bucle
Ejemplo
Desarrollar un algoritmo que permita calcular y mostrar la suma de los n
primeros números naturales, deberá ingresar el límite de números a sumar.
S = 1 + 2 + 3 + 4 + 5 +... + n
1. Análisis.
i. ¿Qué te piden que realices?
Calcular la suma de n números naturales.
ii. ¿Qué datos necesito conocer?
La cantidad de números a sumar.
2. Planteamiento Lógico.
Este problema se puede haciendo uso de acumuladores y contadores.
3. Definición de variables de entrada.
Se requerirán una variable que represente a la cantidad de números a
sumar(N).
4. Definición de variables de salida.
Tilizaremos la variable AC.
5. Programa
General Declaraciones
Dim i As Byte
Dim AC As Integer
168 SENATI-Computación e Informática
ción con VBA
Private Sub CmdProcesar_Click()
List1.Clear
AC = 0
For i = 1 To Val(Text1.Text)
List1.AddItem Str(i)
AC = AC + i
Next i
Text2 = AC
End Sub
Private Sub CmdLimpiar_Click()
Text1 = 0
Text2 = 0
Text1.SetFocus
End Sub
Private Sub CmdSalir_Click()
If MsgBox("Desea salir?", vbInformation + vbYesNo, "SALIDA") = vbYes Then
End
End If
End Sub
6. Diagrama de flujo
INICIO
Declaración de variables
I, N, AC: entero
N
AC = 0
Para I=1 Hasta N
AC = AC + I
AC
FIN
SENATI-Computación e Informática 169
Microsoft Office
Excel 2007
La ventana del editor de Visual Basic Para trabajar en el Entorno de Visual Basic, hacer lo siguiente.
Hacer clic en la ficha , botón
Se presenta la ventana de programación Visual Basic
Barra de menú Barra de herramienta
Ventana de proyecto
Formulario
Cuadro de herramientas
A continuación se describen los principales elementos de la ventana de Microsoft Visual Basic.
a. Barra de Menús
Presenta los comandos que se usan para trabajar con Visual Basic. Además
de los menús estándar Archivo, Edición, Ver, Insertar, Formato, Depuración,
Ejecutar, Herramientas, Complementos, Ventana y Ayuda.
b. Barra de Herramientas
Permite un acceso directo (solo un clic) a muchas de las operaciones más
frecuentes utilizadas durante el desarrollo de aplicaciones.
c. Cuadro de Herramientas
Contiene todos los objetos y controles que se pueden añadir a los formularios
para crear aplicaciones.
170 SENATI-Computación e Informática
ción con VBA
d. Diseñador de Formularios
Funciona como una ventana en la que se puede personalizar el diseño de la
interfaz de usuario (ventana) de una aplicación.
e. Explorador de Proyectos
Lista de los archivos (formularios, módulos, etc.) del proyecto actual. Un
Proyecto es una colección de archivos que utiliza para construir una
aplicación.
f. Ventana de Propiedades
Lista los valores de las propiedades del formulario o control seleccionado
que pueden ser modificados durante el diseño del formulario o control.
g. Ventana de Código
Funciona como un editor para escribir el código (sentencias) de la
aplicación. Cuando se ingresa el nombre de una función en la ventana de
código, Visual Basic automáticamente proporciona el formato o sintaxis de
la función.
Terminología de Visual Basic
Conforme trabaje con VBA necesitará estar familiarizado con los siguientes
términos:
Término
Definición
Tiempo de diseño
Es el momento en el que se construye la aplicación.
Tiempo de ejecución
Es el momento en el cual ejecutamos aplicación.
Formulario
ES el contenedor de los controles donde se diseña la
aplicación, también conocida como interfaz de
usuario.
Controles
Representación gráfica de objetos tales como botones,
cuadros de lista, cuadros de edición, etc.
Objetos
Un término general usado para describir todos los
formularios y controles que forman parte de la
aplicación.
Propiedades
Los valores de un objeto, tales como tamaño, título,
color, etc.
Métodos
Las acciones que un objeto puede realizar sobre sí
mismo.
Eventos
Son acciones reconocidas por un formulario o control.
Los eventos ocurren a medida que el usuario interactúa
SENATI-Computación e Informática 171
Microsoft Office
Excel 2007
con los objetos de la aplicación.
Programación
controlada por
eventos
La programación controlada por eventos es la esencia
de las interfaces gráficas de usuario; el usuario acciona
y el código responde.
Programación por eventos
En las aplicaciones manejadas por eventos, la ejecución no sigue una ruta
predefinida. En vez de esto, se ejecutan diferentes secciones de código en
respuesta a eventos.
La secuencia de eventos determina la secuencia en que el código se ejecuta. Es
por esto que la ruta que sigue el código de la aplicación es diferente cada vez que
se ejecuta el programa.
Convenciones para los nombres de los objetos
Los objetos deben llevar nombres con un prefijo coherente que facilite la
identificación del tipo de objeto. A continuación se ofrece una lista de
convenciones recomendadas para algunos de los objetos permitidos poro Visual
Basic.
Tipo de Control
Prefijo
Detalles
Etiqueta
lbl
lblAPELLIDOS
Cuadro de texto
txt
txtAPELLIDO
Casilla de verificación
chk
chkIMPRESORA
Botones de opción
opt
optCPU
Cuadro combinado, cuadro
lista desplegable
cbo
cboCUDADES
Cuadro de lista
lst
lstPAISES
Botón de comando
cmd
cmdSALIR
Formulario
frm
frmENTRADA
Marco
fra
fraTIPOS
Línea
lin
linVERTICAL
Imagen (Picture)
pic
picLOGOTIPO
Cuadro de número
spn
spnPÁGINAS
172 SENATI-Computación e Informática
ción con VBA
Formularios
El formulario es el principal medio de
comunicación entre el usuario y la
aplicación. Los usuarios interactúan con los
controles sobre el formulario para ingresarle
datos y obtener resultados, para mostrar las
propiedades de un objeto pulsar F4.
Propiedades
BackColor Color de fondo del formulario.
Caption Texto en la barra de título del formulario.
Enabled True/False. Determina si está habilitado para responder a las
acciones del usuario.
Left y Top Ubicación del formulario.
Name Nombre del formulario.
Eventos
Activate Ocurre cuando el formulario se convierte en la ventana activa.
Click Ocurre cuando hace clic sobre el formulario.
Deactivate Ocurre cuando el formulario deja de ser la ventana activa.
Añadir controles al formulario
Para añadir controles a un formulario lo hacemos de la siguiente manera:
1. Haga clic sobre el control en el Cuadro de Herramientas.
2. Ubique el puntero del Mouse (una cruz) sobre el formulario en la
esquina superior izquierda donde desea colocar el control.
3. Realice un clic sostenido mientras arrastra el puntero a la esquina
superior derecha donde colocará el control.
4. Suelte el botón del Mouse.
Estos cuatro pasos se repiten con cada control que desea añadir al
formulario.
La Ventana de Código
La Ventana de Código se usa para escribir, mostrar y editar el código de su
aplicación. Puede abrir una ventana de código por cada módulo de su aplicación,
de modo que puede fácilmente copiar y pegar entre ellos. El editor de texto es
solo un editor ASCII.
SENATI-Computación e Informática 173
Microsoft Office
Excel 2007
La Ventana de Código contiene:
Lista de objetos Lista de eventos
La barra de división
a. El Cuadro Lista de Objetos
Muestra el nombre del objeto seleccionado. Haga clic en la flecha a la
derecha del cuadro Objeto para mostrar una lista de todos los objetos
asociados con el formulario.
b. El Cuadro Lista de Eventos
Muestra todos los eventos reconocidos para el formulario o control mostrado
en el cuadro Objeto. Cuando seleccionamos un evento, en la ventana de
código se muestra el procedimiento de evento asociado con ese evento.
Ejemplo:
c. La Barra de División
Permite dividir la ventana de código en dos partes.
Editando Código
Use las características de edición de Visual Basic para que su código sea más
fácil de leer.
Sangría
Use la sangría para diferenciar partes de su código, tales como estructuras
repetitivas y condicionales. Veamos el siguiente ejemplo:
Private Sub cmdIngresar_Click()
If Len(Trim(txtUsuario))=0 Then
txtUsuario.SetFocus
ElseIf Len(Trim(txtContraseña))=0 Then
txtContraseña.SetFocus
ElseIf txtContraseña = “AGPS” Then
Para aplicar sangría a una sección de sentencias de un
código use la tecla Tab.
174 SENATI-Computación e Informática
ción con VBA
Else
End If
MsgBox “La clave ingresada es correcta”
Unload Me
MsgBox “La clave ingresada no es válida”
txtContraseña.SelStart=0
txtContraseña.SelLength= Len(Trim(txtContraseña))
txtContraseña.SetFocus
End Sub
Comentarios
El añadir documentación y comentarios a su código permite comprender mejor lo
que hace el código. El texto que continúe al símbolo de comentario será ignorado
en la ejecución de la aplicación. Veamos el siguiente ejemplo:
Private Sub cmdLimpiar_Click()
'Este procedimiento limpia la ventana de identificación
txtUsuario.Text = "" 'Limpia el cuadro de texto
txtUsuario.SetFocus ' Mueve el enfoque a txtUsuario End
Sub
Un comentario se
inicia con el carácter
apóstrofe ( „ )
Creación y uso de procedimientos En las aplicaciones tradicionales o procedurales, es la aplicación quien controla que
porciones de código se ejecuta, y la secuencia en que este se ejecuta. La ejecución de la
aplicación se inicia con la primera línea de código, y sigue una ruta predefinida a través
de la aplicación, llamando procedimientos según sea necesario.
Procedimientos
Existen dos tipos de procedimientos con los que se trabaja en Visual Basic: los
procedimientos de evento y los procedimientos generales.
Procedimientos de Evento
Visual Basic invoca automáticamente procedimientos de evento en respuesta a
acciones del teclado, del ratón o del sistema. Cada control tiene un conjunto fijo
de procedimientos de evento. Los procedimientos de evento para cada control
son mostrados en un cuadro de lista despegable en la ventana de código.
SENATI-Computación e Informática 175
Microsoft Office
Excel 2007
El código que se escriba en el procedimiento de evento Click es
ejecutado cuando el usuario haga
clic en un botón de comando.
Procedimientos Generales
Son procedimientos Sub o Function que son creados para que lleven a cabo tareas
específicas.
Para crearlos hacer clic en el menú Insertar, Procedimiento.
Si se tiene código duplicado en varios procedimientos de evento, se puede
colocar el código en un procedimiento general y luego invocar al procedimiento
general desde los procedimientos de evento.
Procedimientos Sub
Los procedimientos Sub no retornan valores. Por ejemplo:
Public Sub Seleccionar(Cuadro As TextBox)
Cuadro.SelStart = 0
Cuadro.SelLength = Len(Cuadro.Text)
End Sub
Los procedimientos Sub son invocados especificando sólo el nombre del
procedimiento, o empleando la instrucción Call con el nombre del procedimiento.
Por ejemplo:
Call Seleccionar(Text1)
Si se emplea la instrucción Call, se debe encerrar la lista de argumentos entre
paréntesis. Si se omite Call, también se deben omitir los paréntesis alrededor de
la lista de argumentos.
176 SENATI-Computación e Informática
ción con VBA
:
Procedimientos Function
Los procedimientos Function devuelven valores. En el siguiente ejemplo, el
procedimiento Function recibe un número y devuelve ese número al cuadrado.
Public Function Cuadrado(N As Integer) As Integer
Cuadrado = N * N
End Function
Si se desea guardar el valor devuelto, se debe usar paréntesis cuando se invoque a
la función, como se muestra a continuación:
Resultado = Cuadrado (5)
Si se omiten los paréntesis, se puede ignorar el valor devuelto y no guardarlo en
una variable. Esto puede ser útil si se quiere ejecutar una función y no se desea el
valor devuelto. Por ejemplo:
Ámbito de las variables.
Denominamos ámbito de una variable a las partes del programa donde esa
variable está declarada. Para entenderlo mejor, veamos someramente la forma de
un programa desarrollado en VB.
A estas partes las habíamos llamado Procedimientos. Podemos tener
procedimientos que no estén relacionados con ningún evento ocurrido al
formulario o a sus controles. (Los Procedimientos que iremos insertando a lo
largo de la aplicación).
Aquí puede insertar proc. y funciones del módulo.
Si se declara una variable dentro de un procedimiento o
Función, esa variable tiene como ámbito el Procedimiento
o Función donde se declaró.
En un Formulario, una variable puede declararse de dos formas: Privada o Pública.
Variable Privada tiene como ámbito sólo el proc. y función donde
fue declarada
Variable Pública tiene como ámbito todos los proc. y funciones
del formulario y sus controles.
Variable a nivel de formulario debe declararse en la sección de
declaraciones, que está la ventana de código Objeto = General,
Proc. = Declaraciones. Tiene como ámbito todo el formulario
En un Módulo una variable puede declararse como Privada, con lo que no saldrá de ese Módulo, o Pública, pudiendo en este caso
usarse en todo el programa
No es recomendable declarar variables con el mismo nombre.
SENATI-Computación e Informática 177
Microsoft Office
Excel 2007
Formas de declaración de variables en un proyecto VB.
Sentencia Dim
Es la forma más común de declarar una variable como Privada. Puede emplearse
en un Procedimiento, Función, Formulario o Módulo. La sintaxis es de la
siguiente forma:
Dim nombrevariable As Integer
Sentencia PRIVATE
Su ámbito depende donde la declaro. Cada vez que entremos al formulario,
procedimiento o módulo, esa variable tomará el
valor cero (si es numérica) o nulo (si es string).
Es la forma de declarar una variable como Privada. Puede emplearse solamente
en la sección de declaraciones de un Formulario o Módulo. La sintaxis es de la
siguiente forma:
Private nombrevariable As Tipovariable
La sentencia Private no puede usarse en un
procedimiento o función.
La variable NO puede utilizarse fuera del Formulario o Módulo donde se
declaró.
Sentencia PUBLIC
Puede emplearse solamente en la sección de declaraciones de un Formulario o
Módulo. La sintaxis es de la siguiente forma:
Public nombrevariable As Tipovariable
Para nombrarla, si estamos en el Formulario
donde se declaró basta con citarla por su
nombre. Si no estamos en ese Formulario,
Si se declara de esta forma en la sección de declaraciones de un
Formulario, esa variable puede usarse
en toda el programa.
habrá que citarla por el nombre del Formulario, seguido del nombre de la
variable, separado por un punto:
NombreFormulario.Nombrevariable
Sentencia GLOBAL
Una variable declarada como Global es reconocida en cualquiera de los
formularios y módulos del proyecto. La sintaxis es:
Global nombrevariable As tipovariable
Sentencia STATIC
La sentencia Global sólo puede usarse
en el apartado de declaraciones de un
Módulo.
Variable estática permite retener el valor de la variable cuando se vuelve a
invocar el proc. o función. Esta declaración como estática se realiza mediante la
instrucción Static
Static nombrevariable As tipovariable Sólo ̀ puede declararlo dentro de un procedimiento o función.
178 SENATI-Computación e Informática
ción con VBA
Pese a que Visual Basic no obliga a declarar variables, es muy útil hacerlo. De esta forma se tiene control sobre el programa. La experiencia se lo irá
demostrando.
Resumen de declaración de variables
Procedimiento
La variable no puede usarse
fuera de esta Procedimiento
Dim Variable As
Tipovariable
Procedimiento, como
permanente
La variable no puede usarse
fuera de este procedimiento, y
dentro de él conserva el valor
aunque se salga y se vuelva a
entrar
Static Variable As
Tipovariable
Formulario
En su sección de
declaraciones, como
Privada
Solamente se puede usar en
ese Formulario
Dim Variable As
Tipovariable
Private Variable As
Tipovariable
Formulario
En su sección de
declaraciones, como
Pública
Puede usarse en toda la
aplicación
Public Variable As
Tipovariable
Módulo
Como Privada
Solamente puede usarse en
ese Módulo
Dim Variable As
Tipovariable
Private Variable As
Tipovariable
Módulo
Como Pública
Puede usarse en toda la
aplicación
Public Variable As
Tipovariable
Global Variable As
Tipovariable
Forma de conocer el tipo de una variable. Función TypeName
Podemos conocer el tipo con el que se ha declarado una variable. Esto se hace
mediante la Función TypeName, que devuelve una cadena con el tipo de una
variable.
MiTipo = TypeName(NombreVariable)
NombreVariable puede ser cualquier variable con excepción de las de tipos
definidos por el usuario.
SENATI-Computación e Informática 179
Microsoft Office
Excel 2007
La cadena de caracteres devuelta por TypeName puede ser una de las siguientes:
Cadena devuelta La variable contiene
Byte Un byte
Entero Un entero.
Largo Un entero largo.
Simple Un número de punto flotante de precisión simple.
Doble Un número de punto flotante de precisión doble.
Moneda Un valor de moneda.
Fecha Una fecha.
Cadena Una cadena.
Boolean Un valor Boolean.
Error Un valor de error.
Empty No inicializado.
Null No hay datos válidos.
Objeto Un objeto que no respalda Automatización OLE.
Desconocido Un objeto de Automatización OLE cuyo tipo es
desconocido.
Nada Una variable de objeto que no se refiere a un objeto.
Si NombreVariable es una matriz, la cadena devuelta puede ser cualquiera de las cadenas posibles con un paréntesis vacío adherido. Por ejemplo, si
NombreVariable es una matriz de enteros, TypeName devolverá "Integer()".
Objetos propiedades, métodos y eventos
Control Etiqueta (Label)
Se utiliza para mostrar texto que el usuario no puede modificar.
Generalmente para identificar otros controles en el formulario o para mostrar
instrucciones al usuario.
Propiedades
Name Nombre del control.
AutoSize True/False. Determina si el tamaño del control se ajusta
automáticamente al texto que contiene.
Caption Texto que muestra el control.
Font Establece la fuente, estilo y tamaño para el texto del control. 180 SENATI-Computación e Informática
ción con VBA
Control Cuadro de Texto (Textbox)
Se utiliza para que el usuario le proporcione datos a la aplicación o
para que la aplicación le devuelva la información al usuario. El texto que se
muestra en el control puede ser cambiado por el usuario.
Propiedades
Enabled True/False. Establece un valor que determina si el control
puede responder a eventos generados por el usuario.
Font Establece la fuentes, estilo y tamaño para el texto del control.
Locked True/False. Determina si es posible modificar el texto en el
control.
MaxLength Establece la longitud máxima permitida para el texto en el
control.
MultiLine Establece si el control puede aceptar múltiples líneas de texto.
Name Nombre del control.
PasswordChar Carácter utilizado para ocultar el texto que realmente contiene
el control.
Text Texto que realmente contiene y muestra el control.
Visible Establece si el control será visible para el usuario.
Eventos
Change Ocurre cuando cambia el texto que contiene el control.
KeyDown Ocurre cuando el usuario presiona una tecla mientras el
control tiene el enfoque.
Control Botón de Comando (Commandbutton)
Permite que la aplicación inicie, interrumpa o termine un proceso.
Propiedades
Caption Establece el texto que muestra el botón.
Font Establece la fuente, estilo y tamaño para el texto del control.
Name Nombre del botón.
Visible True/False. Establece si el botón será visible para el usuario.
Eventos
Click Ocurre cuando se hace clic sobre el botón.
SENATI-Computación e Informática 181
Microsoft Office
Excel 2007
Estableciendo Propiedades
Al diseñar la interface de usuario de una aplicación Visual Basic, se deben
establecer la propiedades para los controles (objetos) creados.
Estableciendo Propiedades en Tiempo de Diseño
Algunas propiedades pueden ser establecidas en tiempo de diseño.
Para establecer estas propiedades se emplea la ventana de propiedades.
Si selecciona varios objetos a la vez y accede a la ventana de propiedades, sólo se
mostrarán las propiedades que son
comunes para todos los controles seleccionados. Cualquier cambio que se haga a
una propiedad será aplicada a todos los controles.
Para acceder a la ventana de propiedades, oprima en botón
secundario del ratón sobre un
objeto, y luego haga clic en
Propiedades.
También se puede obtener el
mismo resultado seleccionado el
objeto y luego presionando F4
Estableciendo Propiedades en Tiempo de Ejecución
En tiempo de ejecución, se puede escribir código para establecer u obtener el
valor de una propiedad.
txtData.Font.Bold = True La siguiente línea de código establece a negrita la fuente de un cuadro de texto llamado txtData.
Este código establece la propiedad Text del cuadro de texto txtData
txtData.Text = "Hola mundo" Establece el valor del texto
Si se omite el nombre de la propiedad, se establece la propiedad predeterminada del control. La propiedad predeterminada de un cuadro de texto es la propiedad
Text. La propiedad predeterminada de una etiqueta es la propiedad Caption. Las
siguientes líneas de código establecen las propiedades predeterminadas text y
caption de un cuadro de texto y de una etiqueta.
txtData = “Pedro”
lblData = "Nombre”
Se establece la propiedad Text del cuadro de texto
Se establece la propiedad Caption de la etiqueta
182 SENATI-Computación e Informática
ción con VBA
Obteniendo Propiedades en Tiempo de Ejecución
Puede emplear el siguiente código para obtener el valor de una propiedad en
tiempo de ejecución.
Dim sNombre as String
sNombre = txtName.Text
Asigna a la variable sNombre el valor del cuadro de texto
txtName
Definición de variables, tipos de datos y constantes Una variable es un lugar de memoria en la memoria del computado.
Es un nombre que en el programa le asignamos a un dato.
Ese dato podrá cambiar.
Piense por ejemplo, en un programa
consistente en la toma de datos de los
Variable
alumnos de un centro escolar. Existirán varias
variables para poder introducir los datos de los
Valor de la variable
alumnos. Estas variables pueden tener nombre tales como:
Nombre, Apellido_Paterno, Apellido_Materno, Direccion, Telefono,
La variable Nombre tomará valores distintos según vayamos introduciendo los
datos de los distintos alumnos. Es posible, que a lo largo de la ejecución del
programa, esta variable Nombre contenga los datos:
Option Explicit
Obliga a declarar previamente las variables que se vayan a usar.
Esta declaración debe ponerla al comienzo de la sección de declaraciones de cada
formulario y módulo que contenga su aplicación.
Tipos de Variables
Las variables pueden ser de los siguientes tipos: (El número indicado en segundo
lugar indica el número de Bytes que ocupa en memoria.)
VARIABLE
ESPACIO
QUE
OCUPA
DETALLES
Booleana
2 Bytes
Admite los valores 0 y 1, o True (verdadero) y
False (falso)
Byte
1 Bytes
Números enteros, en el rango de 0 a 255
SENATI-Computación e Informática 183
Microsoft Office
Excel 2007
Integer
2 Bytes
Números enteros en el rango de -32768 a 32767
Long
4 Bytes
Números enteros en el rango de -2147483648 a
2147483647
Single
4 Bytes
Punto flotante, simple precisión
Doble
8 Bytes
Punto flotante, doble precisión.
Currency
Entero,
con punto
decimal
fijo
(Típico de
monedas)
String
* Cadenas alfanuméricas de longitud variable o fija.
Una variable tipo String ocupa el mismo número
de bytes que caracteres tenga la cadena.
Date
8 Bytes
Fechas
Objet
4 Bytes
Referencia a objetos
Variant
*
Otros tipos de datos.
Una variable tipo Variant ocupa 16 bytes si se
trata de un número y 22 bytes + longitud de la
cadena si se trata de un dato tipo cadena de
caracteres.
Los bytes necesarios para almacenar esa variable dependerán de los datos que se hayan definido.
NOTA. Observe en la lista anterior que un dato Booleano ocupa 2 Bytes,
mientras que un dato tipo Byte ocupa un byte. En muchas ocasiones declaramos
variables tipo Boolean con la intención de que ocupen menos espacio.
Declaración de variables
Para declarar una variable se utiliza la sentencia Dim.
Sintaxis: Dim nombre_variable As Tipo_variable
Tipos de variables
A continuación se describen los tipos de variable.
184 SENATI-Computación e Informática
ción con VBA
a. Variables Alfanuméricas
Es toda información que va a contener texto o la unión de textos y números;
información que no representa cálculos matemáticos.
Ejemplo:
Nombre de una persona Dim nombres As String
Apellido Paterno de una persona Dim apel_pat As String
Dirección de una persona Dim direccion As String
Definiendo la cantidad de caracteres que aceptará la variable
Dim nombres1 As String
Dim nombres2 As String *15
nombres1 puede tener cualquier número de caracteres.
nombres2 puede tener un máximo de 15 caracteres.
En el caso del DNI, código postal, No. De calle, piso del edificio, etc. Es recomendable declararlo como cadena. Para ahorrar memoria.
b. Variable Numéricas
¿Qué variables debemos declarar entonces como numéricas ? La respuesta es
bien sencilla: Aquellas que van a contener datos con lo que vamos a realizar
operaciones matemáticas.
Ejemplo:
Edad de una persona Dim nombres As Byte
Nota de un curso Dim nota1 As Byte
Sueldo Básico Dim basico As Single
Bonificaciones Dim boni1 As Single
Las variables booleanas (True/False) pueden en muchos casos sustituirse por una
variable del tipo Byte. Si ese datos True / False se va a introducir en una base de
datos o en fichero en el disco, puede ser más prudente poner 0 en vez de False y 1
en vez de True.
Una variable byte ocupa muy poco, simplemente 1 byte como su nombre indica.
Pero no puede contener números mayores de 255 ni números negativos.
Cada vez que declare una variable numérica piense en los valores que puede
tener, sobre todo cuando esa variable va a ser el resultado de una operación
matemática. Recuerde el escaso margen de una variable tipo Integer ( de -32768
a 32767)
Si la aplicación va a tratar moneda, piense en la forma de expresar los números
decimales y el número de ellos permitidos, así como el redondeo.
SENATI-Computación e Informática 185
Microsoft Office
Excel 2007
La variable correcta para este caso es Currency, pero Currency le añade automáticamente el tipo de moneda de cada país lo que con frecuencia es un
engorro. Los datos del tipo de moneda los toma del sistema operativo del
ordenador, por lo que no se extrañe si le expresa el número en dólares. Cambie el
país en su Sistema Operativo Windows.
c. Variable Date
Otro tipo de variable es Date. Este tipo de variable representa una fecha.
Ejemplo:
Fecha de nacimiento de una persona Dim fnac As Date
d. Variable Boolean
Este tipo de variable representa dos valores TRUE (verdadero) o FLASE (falso).
Ejemplo:
Sexo Dim sexo As Boolean
Error típico de un programador novel
Creo que esta costumbre viene del lenguaje C. Pero no vale en VB. Se trata de
declarar varias variables juntas en una misma línea:
Dim Variable1, Variable2, Variable3, Variable4 As String
Esta declaración está MAL hecha. Visual Basic interpretará que Variable1,
Variable2 y Variable3 son del tipo Variant, y solamente Variable4 la supone
como tipo String
La forma correcta de hacerlo, si queremos declarar esas variables un una sola
línea, es la siguiente :
Dim Variable1 As String, Variable2 As String, Variable3 As String, Variable4
As String.
Constantes
Una constante es un nombre significativo que sustituye a un número o una
cadena que no varía. Hay dos orígenes para las constantes:
Constantes intrínsecas o definidas por el sistema proporcionadas por
Visual Basic.
Las constantes simbólicas o definidas por el usuario se declaran mediante
la instrucción Const.
186 SENATI-Computación e Informática
ción con VBA
La sintaxis para declarar una constante es la siguiente:
[Public|Private] Const nombre_constante [As tipo] = expresión
El argumento nombre_constante es un nombre simbólico válido (las reglas son
las mismas que para crear nombres de variable) y expresión está compuesta por
constantes y operadores de cadena o numéricos; sin embargo, no puede utilizar
llamadas a funciones en expresión. Una instrucción Const puede representar una
cantidad matemática o de fecha y hora:
Const conPi = 3.14159265358979
Public Const conMaxPlanetas As Integer = 9
Const conFechaSalida = #1/1/95#
Se puede utilizar también la instrucción Const para definir constantes de cadena:
Public Const conVersion = “ 07.10.A”
Const conNombreClave = “Enigma”
Puede colocar más de una declaración de constante en una única línea si las
separa con comas:
Public Const conPi=3.14, conMaxPlanetas=9, conPobMundial=6E+09
Operadores
a. Aritméticos
^ Exponenciación
* Multiplicación
/ División
\ División entera
Mod Residuo entero (Ejm: A Mod B)
+ Suma
- Resta
& Concatenación de cadenas
b. Comparación
= Igual
<> Distinto
< Menor que
SENATI-Computación e Informática 187
Microsoft Office
Excel 2007
<= Menor o igual
>= Mayor o igual
Like Compara dos cadenas
* Cero o más caracteres (Ejm: cad Like “ma*”)
? Cualquier carácter
# Cualquier dígito (0-9)
c. Lógicos
And “Y” lógico
Or “O” lógico
Xor “O” Exclusivo
Not Negación
Construcciones: If – then, Select Case Las estructuras condicionales le permiten controlar el flujo de ejecución del programa.
A continuación se describen las estructuras de control
Condicional simple. If .. then
Use la estructura If...Then para ejecutar una o más instrucciones basadas en una
condición. Puede utilizar la sintaxis de una línea o un bloque de varias líneas:
If condición Then Sentencias
If condición Then
Sentencias
End If
Donde:
Condición. Es una expresión lógica, que devuelve un valor lógico: Verdadero o
falso. Ejemplo:
If cualquierFecha < Now Then CualquierFecha = Now
o
If then en una sola línea, sólo puede ejecutar una línea de código
If cualquierFecha < Now Then
CualquierFecha = Now
De este otro modo se pueden ejecutar varias líneas de código
End If
188 SENATI-Computación e Informática
ción con VBA
Condicional doble If...Then...Else
Utilice un bloque If...The...Else para definir varios bloques de sentencias, uno de
los cuales se ejecutará:
If condición1 Then
[bloque de sentencias 1]
[ElseIf condición2 Then
Se evalúa esta condición si es verdadera se ejecuta el bloque de sentencias 1
[Else
[bloque de sentencias 2]] ... Si es falsa la condición1, evalúa la condición2, si es verdadera ejecuta el bloque de sentencias 2
End If
[bloque de sentencias n]] Si no se cumple ninguna condición se ejecuta el bloque de sentencias n
Por ejemplo, la aplicación podría realizar distintas acciones dependiendo del control en que se haya hecho clic de una matriz de controles de menú:
Private Sub mnuCut_Click (Index As Integer)
If Index = 0 Then „ Comando Cortar
CopyActiveControl „ Llama a procedimientos generales
ClearActiveControl
ElseIf Index = 1 Then „ Comando Copiar
CopyActiveControl
ElseIf Index = 2 Then „ Comando Borrar
ClearActiveControl
Else „ Comando Pegar
PasteActiveControl
End If
End Sub
o
If ClaveUsuario=”DSI” Then
„ Permite al usuario entrar al sistema
...
...
Else
„ Mostrar un mensaje advirtiendo error en la clave
...
...
End If
SENATI-Computación e Informática 189
Microsoft Office
Excel 2007
o
Private Sub DeterminaCondición ( )
If Val (txtPromedio) >=13 Then
txtCondición = “Aprobado”
ElseIf Val (txtPromedio) >= 10 Then
txtCondición = “Asistente”
Else
txtCondición = “Desaprobado”
End If
End Sub
Observe que siempre puede agregar más cláusulas ElseIf a la estructura If...Then.
Sin embargo, esta sintaxis puede resultar tediosa de escribir cuando cada ElseIf
compara la misma expresión con un valor distinto. Para estas situaciones, puede
utilizar la estructura de decisión Select Case.
Condicional múltiple Select Case
Visual Basic proporciona la estructura Select Case para ejecutar selectivamente
un bloque de sentencias entre varios bloques.
La estructura Select Case funciona con una única expresión de prueba que se
evalúa una vez solamente, al principio de la estructura. Visual Basic compara el
resultado de esta expresión con los valores de cada Case de la estructura. Si hay
una coincidencia, ejecuta el bloque de sentencias asociado a ese Case:
Selec Case expresión_prueba
[Case lista_expresiones1
[bloque de sentencias 1]]
[Case lista_expresiones2
[bloque de sentencias 2]]
.
.
.
[Case Else
[bloque de sentencias n]]
End Select
Cada lista_expresiones es una lista de uno a
más valores.
Si hay más de un valor en una lista, se
separan los valores con comas.
Cada bloque de sentencias contiene cero o
más instrucciones.
Si más de un Case coincide con la expresión
de prueba, sólo se ejecutará el bloque de
instrucciones asociado con la primera
coincidencia.
Visual Basic ejecuta las instrucciones de la
cláusula (opcional) Case Else si ningún valor
de la lista de expresiones coincide con la
expresión de prueba.
Por ejemplo, suponga que agrega otro comando al menú Edición en el ejemplo If...Then...Else. Podría agregar otra cláusula ElseIf o podría escribir la función
con Select Case:
190 SENATI-Computación e Informática
ción con VBA
Private Sub mnuCut_Click (Index As Integer)
Select Case Index
Case 0 „ Comando Cortar
CopyActiveControl „Llama a procedimientos generales
ClearActiveControl
Case 1 „ Comando copiar.
CopyActiveControl
Case 2 „ Comando borrar.
ClearActiveControl
Case 3 „ Comando Pegar.
PasteActiveControl
Case Else
frmFind.Show „ Muestra el cuadro de diálogo Buscar.
End Select
End Sub
o
Select Case TipoUsuario
Case “Supervisor”
„ Proporciona al usuario privilegios de Supervisor
...
...
Case “Usuario”
„ Proporciona al usuario privilegios de Usuario
...
...
Case Else
„ Proporciona al usuario privilegio de invitado
...
...
End Select
Observe que la estructura Select Case evalúa una expresión cada vez que al
principio de la estructura. Por el contrario, la estructura If...Then...Else puede
evaluar una expresión diferente en cada sentencia ElseIf. Sólo puede sustituir una
esructura If...Then...Else con una estructura Select Case si la intrucción If y cada
instrucción ElseIf evalúa la misma expresión.
SENATI-Computación e Informática 191
Microsoft Office
Excel 2007
Otros Ejemplos
If Ventas > 100000 Then
strDscto = Format (0.10, “Fixed”)
ElseIf Ventas > 50000 Then
strDscto = Format (0.05, “Fixed”)
Else
strDscto = Format (0.02, “Fixed”)
End If
Select Case Cantidad
Case 1
sngDscto = 0.0
Case 2, 3
sngDscto = 0.05
Case 4 To 6
sngDscto = 0.10
Case Else
sngDscto = 0.20
End Select
intRpta = MsgBox (“Guarda cambios antes de salir” , vbYesNo)
Select Case intRpta
Case vbYes
GuardarCambios
Unload Me
Case vbNo
Unload Me
End Select
Bucles For … Next, While .. Do Las estructuras de repetición o bucle le permiten ejecutar una o más líneas de
código repetidamente. Las estructuras de repetición que acepta Visual Basic son:
Do...Loop
For...Next
For Each...Next
192 SENATI-Computación e Informática
ción con VBA
Do...Loop
Utilice el bucle Do para ejecutar un bloque de sentencias un número indefinido
de veces. Hay algunas variantes en la sentencia Do...Loop, pero cada una evalúa
una condición numérica para determinar si continúa la ejecución. Como ocurre
con If...Then, la condición debe ser un valor o una expresión que dé como
resultado False (cero) o True (distinto de cero).
Do While condición
Sentencias
Loop
Cuando se ejecuta este bucle Do, primero evalúa condición. Si condición es False (cero), se salta todas las sentencias. Si es True
(distinto de cero) Visual Basic ejecuta las sentencias, vuelve a la
instrucción Do While y prueba la condición de nuevo.
Por tanto, el bucle se puede ejecutar cualquier número de veces, siempre y cuando condición sea distinta de cero o True. Nunca se ejecutan las sentencias si
condición es False inicialmente. Por ejemplo, este procedimiento cuenta las veces
que se repite una cadena destino dentro de otra cadena repitiendo el bucle tantas
veces como se encuentre la cadena de destino:
Function ContarCadenas (cadenalarga, destino)
Dim posición, contador
posición = 1
Do While InStr (posición, cadenalarga, destino)
posición = InStr (posición, cadenalarga, destino)+1
contador = contador + 1
Loop
ContarCadenas = contador
End Function
Si la cadena destino no está en la otra cadena, InStr devuelve 0 y no se ejecuta el
bucle.
Otra variante de la instrucción Do...Loop
Do
Sentencias
Loop While condición
Se ejecuta las sentencias primero y prueba la condición después de
cada ejecución. Esta variación garantiza al menos una ejecución
de sentencias:
Hay otras dos variantes análogas a las dos anteriores, excepto en que repiten el bucle siempre y cuando condición sea False en vez de True.
Hace el bucle cero o más veces Hace el bucle al menos una vez
Do Until condición Do
Sentencias Sentencias
Loop Loop Until condición
SENATI-Computación e Informática 193
Microsoft Office
Excel 2007
For...Next
Utiliza una variable llamada contador que incrementa o reduce su valor en cada
repetición del bucle. La sintaxis es la siguiente:
For contador = iniciar To finalizar [Step incremento]
Los argumentos contador, iniciar, finalizar e incremento
son todos numéricos.
Sentencias
Next [contador]
El argumento incremento puede ser positivo
o negativo. Si incremento es positivo, iniciar
debe ser menor o igual que finalizar o no se
ejecutarán las sentencias del bucle.
Si incremento es negativo, iniciar debe ser mayor o igual que finalizar para que se ejecute el
cuerpo del bucle. Si no se establece Step, el valor predeterminado de incremento es 1.
Al ejecutar el bucle For, Visual Basic:
1. Establece contador al mismo valor que iniciar.
2. Comprueba si contador es mayor que finalizar. Si lo es, Visual Basic
sale del bucle. (Si incremento es negativo, Visual Basic comprueba si
contador es menor que finalizar.)
3. Ejecuta las sentencias.
4. Incrementa contador en 1 o en incremento, si se especificó.
5. Repite los pasos 2 a 4.
Este código imprime los nombres de todas las fuentes de pantalla disponibles:
Private Sub Form-Click ( )
Dim I As Integer
For i = 0 To Screen.FontCount
Print Screen.Fonts (i)
Next
End Sub
For Each...Next
El bucle For Each...Next es similar al bucle For...Next, pero repite un grupo de
sentencia por cada elemento de una colección de objetos o de una matriz en vez
de repetir las sentencias un número especificado de veces.
Esto resulta especialmente útil si no se sabe cuántos elementos hay en la
colección. He aquí la sintaxis del bucle For Each...Next:
194 SENATI-Computación e Informática
ción con VBA
For Each elemento In grupo
Sentencias
Next elemento
El siguiente ejemplo habilita todos los
Cuadro de Texto del formulario:
Private Sub ModoEdición ( )
Dim control
For Each control In form1.Controls
If TypeOf control Is TextBox Then
Control.Enabled = True
End If
Next control
End Sub
Tenga en cuenta las restricciones siguientes
cuando utilice For Each...Next:
Para las colecciones, elemento sólo puede
ser una variable Variant, una variable
Object genérica o un objeto mostrado en el
Examinador de objetos.
Para las matrices, elemento sólo puede ser
una variable Variant.
No puede utilizar For Each...Next con una
matriz de tipos definidos por el usuario
porque un Variant no puede contener un
tipo definido por el usuario.
Salida de una Estructura de Control
La instrucción Exit le permite salir directamente de un bucle For o de un bucle
Do. La sintaxis de la sentencia Exit es sencilla: Exit For puede aparecer tantas
veces como sea necesario dentro de un bucle For y Exit Do puede aparecer
tantas veces como sea necesario dentro de un bucle Do:
For contador = iniciar To finalizar [Step incremento]
[bloque sentencias]
[Exit For]
[bloque sentencias]
Next [contador]
Do [{While / Until} condición]
[bloque de sentencias]
[Exit Do]
[bloque de sentencias]
Loop
Do
[bloque de sentencias]
[Exit Do]
[bloque de sentencias]
Loop [{While / Until} condición]
Exit For y Exit Do
Son muy útiles ya que, algunas veces, resulta
apropiado salir inmediatamente de un bucle sin
realizar más iteraciones o sentencias dentro del
bucle.
Cuando utilice la instrucción Exit para salir de
un bucle, el valor de la variable contador
difiere, dependiendo de cómo haya salido del
bucle:
Cuando termina un bucle, la variable
contador contiene el valor del límite
superior más el paso.
Cuando sale de un bucle prematuramente,
la variable contador conserva su valor
según las reglas usuales del alcance.
Cuando sale antes del final de una
colección, la variable contador contiene
Nothing si se trata de un tipo de dato
Object y Empty si es un tipo de dato
Variant.
SENATI-Computación e Informática 195
Microsoft Office
Excel 2007
Trabajar con rangos de celda Utilizando la notación A1
Puede hacer referencia a una celda o rango de celdas del estilo de referencia A1
utilizando el método Range.
La siguiente subrutina cambia el formato de las celdas A1:D5 a negrita.
Sub FormatoRango()
Workbooks("Libro1").Sheets("Hoja1").Range("A1:D5").Font.Bold = True End
Sub
La siguiente tabla muestra algunas referencias de estilo A1 utilizando el método
Range.
Referencia
Significado
Range("A1")
Celda A1
Range("A1:B5")
Celdas de la A1 a la B5
Range("C5:D9,G9:H16")
Selección de varias áreas
Range("A:A")
Columna A
Range("1:1")
Fila 1
Range("A:C")
Columnas de la A a la C
Range("1:5")
Filas de la 1 a la 5
Range("1:1,3:3,8:8")
Filas 1, 3 y 8
Range("A:A,C:C,F:F")
Columnas A, C y F
Hacer referencia a celdas utilizando números de índice
Esta propiedad devuelve un objeto Range que representa una sola celda.
En el siguiente ejemplo, Cells(6,1) devuelve la celda A6 de la hoja Hoja1.
Entonces, la propiedad Value se establece en 10.
Sub IngreseValor()
Worksheets("Hoja1").Cells(6, 1).Value = 10
End Sub
196 SENATI-Computación e Informática
ción con VBA
La propiedad Cells funciona bien para ejecutar bucles en un rango de celdas, ya que puede sustituir las variables por los números de índice, como se muestra en el
siguiente ejemplo.
Sub CicloHojas()
Dim Contador As Integer
For Contador = 1 To 20
Worksheets("Hoja1").Cells(Contador,3).Value = Contador
Next Contador
End Sub
Hacer referencia a filas y columnas
Estas propiedades devuelven un objeto Range que representa un rango de celdas.
En el siguiente ejemplo, Rows(1) devuelve la fila uno de la hoja Hoja1.
A continuación, la propiedad Bold del objeto Font del rango se establece en True.
Sub FilasNegrita()
Worksheets("Hoja1").Rows(1).Font.Bold = True
End Sub
La siguiente tabla muestra algunas referencias de fila y columna, utilizando las
propiedades Rows y Columns.
Referencia Significado
Rows(1) Fila uno
Rows Todas las filas de la hoja de cálculo
Columns(1) Columna uno
Columns("A") Columna uno
Columns Todas las columnas de la hoja de cálculo
Para trabajar con varias filas o columnas al mismo tiempo, cree una variable de objeto y utilice el método Union, combinando varias llamadas a la propiedad
Rows o Columns.
SENATI-Computación e Informática 197
Microsoft Office
Excel 2007
El siguiente ejemplo cambia a negrita el formato de las filas uno, tres y cinco de la hoja de cálculo uno del libro activo.
Sub FilasNegritaVarios()
Worksheets("Hoja1").Activate Dim
myUnion As Range
Set myUnion = Union(Rows(1), Rows(3), Rows(5))
myUnion.Font.Bold = True
End Sub
Hacer referencia a celdas utilizando una notación abreviada
Puede utilizar el estilo de referencia A1 o un rango con nombre entre paréntesis
como método abreviado para la propiedad Range.
No es necesario escribir la palabra "Range" o utilizar comillas, como se muestra
en los siguientes ejemplos.
Sub BorrarRango()
Worksheets("Hoja1").[A1:B5].ClearContents
End Sub
Sub AsinarValor()
[MyRange].Value = 30
End Sub
Hacer referencia a rangos con nombre
Es más sencillo identificar los rangos por nombre que por la notación A1. Para
asignar un nombre a un rango seleccionado, haga clic en el cuadro de nombre
situado a la izquierda de la barra de fórmulas, escriba un nombre y, a
continuación, presione la tecla ENTRAR.
Hacer referencia a un rango con nombre
El siguiente ejemplo hace referencia al rango denominado "MiRango" en el libro
"Libro1.xls".
Sub FormatoRango ()
Range("Libro1.xls!MiRango").Font.Italic = True
End Sub
198 SENATI-Computación e Informática
ción con VBA
El siguiente ejemplo hace referencia al rango de hojas de cálculo específico denominado "Hoja1!Ventas" en el libro "Libro1.xls".
Sub FormatSales()
Range("[Libro1.xls]Hoja1!Ventas").BorderAround Weight:=xlthin
End Sub
Para seleccionar un rango con nombre utilice el método GoTo, que activa el libro
y la hoja de cálculo y, a continuación, selecciona el rango.
Sub LimpiaRango()
Application.Goto Reference:="Libro1.xls!MiRango"
Selection.ClearContents
End Sub
El siguiente ejemplo muestra cómo se escribiría el mismo procedimiento para el
libro activo.
Sub LimpiarRango()
Application.Goto Reference:="MiRango"
Selection.ClearContents
End Sub
Ejecutar un bucle en las celdas de un rango con nombre
El siguiente ejemplo ejecuta un bucle en cada una de las celdas de un rango con
nombre utilizando un bucle For Each...Next. Si el valor de cualquiera de las
celdas del rango supera el valor de limit, el color de la celda cambia a amarillo.
Sub AplicarColor()
Const Limit As Integer = 25
For Each c In Range("MiRango")
If c.Value > Limit Then
c.Interior.ColorIndex = 27
End If
Next c
End Sub
SENATI-Computación e Informática 199
Microsoft Office
Excel 2007
Hacer referencia a celdas en relación con otras celdas
Una manera de trabajar con una celda relacionada con otra es utilizar la
propiedad Offset.
El siguiente ejemplo asigna un formato de doble subrayado al contenido de la
celda situada una fila más abajo y a tres columnas de la hoja de cálculo activa.
Sub SubrayadoDoble()
ActiveCell.Offset(1, 3).Font.Underline = xlDouble
End Sub
Nota. Puede grabar macros que utilicen la propiedad Offset en lugar en
referencias absolutas. En el menú Herramientas elija Macro, haga clic en Grabar
nueva macro, haga clic en Aceptar y, a continuación, en el botón Referencia
relativa en la barra de herramientas de grabación de macros.
Para ejecutar un bucle en un rango de celdas, utilice en el rango una variable con
la propiedad Cells. El siguiente ejemplo rellena las primeras 20 celdas de la
tercera columna con valores entre 5 y 100, en incrementos de 5.
La variable contador se utiliza como índice de fila para la propiedad Cells.
Sub HojasValores()
Dim contador As Integer
For contador = 1 To 20
Worksheets("Hoja1").Cells(contador, 3).Value = contador * 5
Next contador
End Sub
Hacer referencia a celdas usando un objeto Range
Si establece una variable de objeto para un objeto Range, puede manipular
fácilmente el rango utilizando el nombre de la variable.
El siguiente procedimiento crea la variable de objeto myRange y, a continuación,
asigna la variable al rango A1:D5 de la hoja Hoja1 del libro activo. Las
instrucciones posteriores modifican las propiedades del rango, sustituyendo el
nombre de la variable por el objeto del rango.
Sub Aleatorio()
Dim MiRango As Range
Set MiRango = Worksheets("Hoja1").Range("A1:D5")
MiRango.Formula = "=RAND()"
MiRango.Font.Bold = True
End Sub
200 SENATI-Computación e Informática
ción con VBA
Hacer referencia a todas las celdas de la hoja de cálculo
Al aplicar la propiedad Cells a una hoja de cálculo sin especificar un número de
índice, el método devuelve un objeto Range que representa todas las celdas de la
hoja de cálculo. El siguiente procedimiento Sub borra el contenido de todas las
celdas de la hoja Hoja1 del libro activo.
Sub ClearSheet()
Worksheets("Hoja1").Cells.ClearContents
End Sub
Hacer referencia a varios rangos
Utilizando el método apropiado puede hacer referencia fácilmente a varios
rangos. Utilice los métodos Range y Union para hacer referencia a cualquier
grupo de rangos; utilice la propiedad Areas para hacer referencia al grupo de
rangos seleccionados en una hoja de cálculo.
Usar la propiedad Range
Puede hacer referencia a varios rangos con la propiedad Range, pero debe poner
comas entre dos o más referencias.
El siguiente ejemplo borra el contenido de los tres rangos de la hoja Hoja1.
Sub ClearRanges()
Worksheets("Hoja1").Range("C5:D9,G9:H16,B14:D18"). _
ClearContents
End Sub
Los rangos con nombre permiten que la propiedad Range funcione más
fácilmente con varios rangos.
El siguiente ejemplo funciona cuando los tres rangos con nombre están en la
misma hoja.
Sub BorrarNombres()
Range("MiRango, TLista, TValores").ClearContents
End Sub
Usar el método Union
Puede combinar varios rangos en un objeto Range utilizando el método Union.
El siguiente ejemplo crea un objeto Range denominado myMultipleRange, los
define como A1:B2 y C3:D4 y, a continuación, asigna el formato de negrita a los
rangos combinados.
SENATI-Computación e Informática 201
Microsoft Office
Excel 2007
Sub MultiplesRangos()
Dim r1, r2, MiMultiplesRangos As Range
Set r1 = Sheets("Hoja1").Range("A1:B2")
Set r2 = Sheets("Hojat1").Range("C3:D4")
Set MiMultiplesRangos = Union(r1, r2)
MiMultiplesRangos.Font.Bold = True
End Sub
Usar la propiedad Areas
Puede utilizar la propiedad Areas para hacer referencia al rango o conjunto de
rangos seleccionados en una selección de varias áreas.
El siguiente procedimiento cuenta las áreas de la selección. Si existe más de un
área, se muestra un mensaje de advertencia.
Sub BusquedaMultiple()
If Selection.Areas.Count > 1 Then
MsgBox "Existe más de una area de selección…"
End If
End Sub
Bucles en un rango de celdas
Al utilizar Visual Basic, con frecuencia necesitará ejecutar el mismo bloque de
instrucciones en cada una de las celdas de un rango. Para ello, combine una
instrucción de repetición y uno o más métodos para identificar cada celda, una a
una, y ejecutar la operación.
Una manera de ejecutar un bucle en un rango es utilizar el bucle For...Next con la
propiedad Cells. Al utilizar la propiedad Cells, puede sustituir el contador del
bucle, u otras variables o expresiones, por el número de índice de las celdas.
En el siguiente ejemplo se sustituye la variable contador por el índice de fila. El
procedimiento ejecuta un bucle en el rango C1:C20, estableciendo en 0 (cero)
cualquier número cuyo valor absoluto sea menor que 0,01.
Sub EstableceCero1()
For Contador = 1 To 20
Set curCell = Worksheets("Hoja1").Cells(Contador, 3)
If Abs(curCell.Value) < 0.01 Then curCell.Value = 0
Next Contador
End Sub
202 SENATI-Computación e Informática
ción con VBA
Otra manera sencilla de ejecutar un bucle en un rango es utilizar el bucle For Each...Next en el conjunto de celdas devuelto por el método Range. Visual Basic
establece automáticamente una variable de objeto para la siguiente celda cada vez
que se ejecuta el bucle.
El siguiente procedimiento realiza un bucle en el rango A1:D20, estableciendo en
0 (cero) cualquier número cuyo valor absoluto sea menor que 0.01.
Sub EstableceCero2()
For Each c In Worksheets("Hoja1").Range("A1:D10").Cells
If Abs(c.Value) < 0.01 Then c.Value = 0
Next
End Sub
Si no conoce los límites del rango en que desea ejecutar el bucle, puede utilizar la
propiedad CurrentRegion para devolver el rango que rodea la celda activa.
Por ejemplo, el siguiente procedimiento, cuando se ejecuta desde una hoja de
cálculo, ejecuta un bucle en el rango que rodea la celda activa, estableciendo en 0
(cero) todos los números cuyo valor absoluto sea menor que 0.01.
Sub EstableceCero3()
For Each c In ActiveCell.CurrentRegion.Cells
If Abs(c.Value) < 0.01 Then c.Value = 0
Next
End Sub
Seleccionar y activar celdas
Al trabajar con Microsoft Excel, normalmente selecciona una o varias celdas y, a
continuación, realiza una acción, como darles formato o escribir valores. En
Visual Basic normalmente no es necesario seleccionar las celdas antes de
modificarlas.
Por ejemplo, si desea escribir una fórmula en la celda D6 utilizando Visual Basic,
no es necesario seleccionar el rango D6. Sólo necesita devolver el objeto Range
y, a continuación, establecer la propiedad Formula en la fórmula que desee, como
se muestra en el siguiente ejemplo.
Sub IngreseFormula()
Worksheets("Hoja1").Range("D6").Formula = "=SUM(D2:D5)"
End Sub
Para obtener ejemplos sobre cómo utilizar métodos para controlar las celdas sin
seleccionarlas, consulte Cómo hacer referencia a celdas y rangos.
SENATI-Computación e Informática 203
Microsoft Office
Excel 2007
Usar el método Select y la propiedad Selection
El método Select activa las hojas y los objetos de las hojas; la propiedad
Selection devuelve un objeto que representa la selección actual de la hoja activa
del libro activo. Antes de utilizar la propiedad Selection, debe activar un libro,
activar o seleccionar un hoja y, a continuación, seleccionar un rango, u otro
objeto, con el método Select.
La grabadora de macros suele crear una macro que utiliza el método Select y la
propiedad Selection.
El siguiente procedimiento Sub se creó utilizando la grabadora de macros, y
muestra cómo trabajan juntas Select y Selection.
Sub Macro1()
Sheets("Hoja1").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "Nombres"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Direccion"
Range("A1:B1").Select
Selection.Font.Bold = True
End Sub
El siguiente ejemplo realiza la misma tarea, sin activar ni seleccionar la hoja de
cálculo ni las celdas.
Sub Etiquetas()
With Worksheets("Hoja1")
.Range("A1") = "Nombres"
.Range("B1") = "Direccion"
.Range("A1:B1").Font.Bold = True
End With
End Sub
Seleccionar celdas en la hoja de cálculo activa
Si utiliza el método Select para seleccionar celdas, recuerde que Select sólo
funciona en la hoja de cálculo activa. Si ejecuta el procedimiento Sub desde el
módulo, el método Select devuelve un error a menos que el procedimiento active
la hoja de cálculo antes de utilizar el método Select en un rango de celdas.
Por ejemplo, el siguiente procedimiento copia una fila de la hoja "Hoja1" a la
hoja "Hoja2" del libro activo.
204 SENATI-Computación e Informática
ción con VBA
Sub CopiarFilas()
Worksheets("Hoja1").Rows(1).Copy
Worksheets("Hoja2").Select
Worksheets("Hoja2").Rows(1).Select
Worksheets("Hoja2").Paste
End Sub
Activar una celda en una selección
Puede utilizar el método Activate para activar una celda en una selección. Sólo
puede haber una celda activa, aunque se haya seleccionado un rango de celdas.
El siguiente procedimiento selecciona un rango y, a continuación, activa una
celda del rango sin cambiar la selección.
Sub ActivarRango()
Worksheets("Hoja1").Activate
Range("A1:D4").Select
Range("B2").Activate
End Sub
Trabajar con rangos 3D
Si trabaja con el mismo rango en más de una hoja, utilice la función Array para
especificar dos o más hojas a seleccionar.
El ejemplo siguiente da formato al borde de un rango tridimensional de celdas.
Sub FormatoHojas()
Sheets(Array("Hoja2", "Hoja3", "Hoja5")).Select
Range("A1:H1").Select
Selection.Borders(xlBottom).LineStyle = xlDouble
End Sub
El ejemplo siguiente aplica el método FillAcrossSheets para transferir los
formatos y datos del rango de la hoja Hoja2 a los rangos correspondientes de
todas las hojas de cálculo del libro activo.
Sub FormatoTodasHojas()
Worksheets("Hoja2").Range("A1:H1").Borders(xlBottom).LineStyle=xlDouble
Worksheets.FillAcrossSheets (Worksheets("Hoja2").Range("A1:H1"))
End Sub
SENATI-Computación e Informática 205
Microsoft Office
Excel 2007
Trabajar con la celda activa
La propiedad ActiveCell devuelve un objeto Range que representa la celda que
está activa. Puede aplicar cualquiera de las propiedades o los métodos de un
objeto Range a la celda activa, como en el ejemplo siguiente.
Sub CeldaActiva1()
Worksheets("Hoja1").Activate
ActiveCell.Value = 35
End Sub
Nota. Sólo se puede trabajar con la celda activa cuando la hoja de cálculo en la
que se encuentra sea la hoja activa.
Mover la celda activa
Puede utilizar el método Activate para designar cuál es la celda activa. Por
ejemplo, el siguiente procedimiento convierte B5 en la celda activa y, a
continuación, le da formato de negrita.
Sub CeldaActiva2()
Worksheets("Hoja1").Activate
Worksheets("Hoja1").Range("B5").Activate
ActiveCell.Font.Bold = True
End Sub
Nota. Para seleccionar un rango de celdas, use el método Select. Para activar sólo
una celda, utilice el método Activate.
Puede utilizar la propiedad Offset para pasar a la celda activa.
El siguiente procedimiento inserta texto en la celda activa del rango seleccionado
y, a continuación, mueve la celda activa una celda a la derecha, sin cambiar la
selección.
Sub MoverDatos()
Worksheets("Hoja1").Activate
Range("A1:D10").Select
ActiveCell.Value = "Total Mensual"
ActiveCell.Offset(0, 1).Activate
End Sub
206 SENATI-Computación e Informática
ción con VBA
Seleccionar las celdas que rodean la celda activa
La propiedad CurrentRegion devuelve un rango de celdas limitadas por filas y
columnas en blanco.
En el siguiente ejemplo, la selección se amplía para incluir las celdas contiguas a
la celda activa que contiene datos. A continuación, se asigna el estilo Moneda a
este rango.
Sub Region()
Worksheets("Hoja1").Activate
ActiveCell.CurrentRegion.Select
Selection.Style = "Currency"
End Sub
Trabajo con libros y hojas Hacer referencia a hojas por número de índice
Un número de índice es un número secuencial asignado a una hoja, según la
posición de su etiqueta, contando desde la izquierda, respecto a las hojas del
mismo tipo.
El siguiente procedimiento utiliza la propiedad Worksheets para activar la hoja
de cálculo uno del libro activo.
Sub SeleccionarHoja()
Worksheets(1).Activate
End Sub
Si desea trabajar con todos los tipos de hojas (hojas de cálculo, de gráficos, de
módulos y de diálogo), utilice la propiedad Sheets.
El siguiente procedimiento activa la hoja cuatro del libro.
Sub SeleccionarHoja()
Sheets(4).Activate End
Sub
Hacer referencia a hojas por su nombre
Puede identificar las hojas por su nombre, utilizando las propiedades Worksheets
y Charts. Las siguientes instrucciones activan varias hojas del libro activo.
SENATI-Computación e Informática 207
Microsoft Office
,
Excel 2007
Puede utilizar la propiedad Sheets para devolver una hoja de cálculo, de gráficos, de módulo o de cuadro de diálogo, incluidos todos en el conjunto Sheets.
El siguiente ejemplo activa la hoja denominada "hoja1" del libro activo.
Sub ActivarHoja()
Worksheets("hoja1").Activate
End Sub
El siguiente ejemplo activa la hoja denominada "grafico1" del libro activo.
Sub ActivarHojaGrafico()
Sheets("grafico1").Activate
End Sub
Añadir módulos VBA Para añadir módulos seguir el siguiente procedimiento.
1. Hacer clic en la ficha , botón
2. Se abre la ventana de Visual Basic, hacer clic en el menú botón
3. Se presenta la ventana de programación de módulo.
Trabajar con UserForms Para añadir formularios seguir el siguiente procedimiento.
1. Hacer clic en la ficha , botón
2. Se abre la ventana de Visual Basic, hacer clic en el menú
208 SENATI-Computación e Informática
ción con VBA
Botón
3. Se presenta la ventana de formulario
Uso de controles de formulario A continuación se describe el uso de controles de un formulario
Control Marco (Frame)
Este control permite agrupar otros controles para darle mayor funcionalidad a la
interfaz. Los controles Botones de Opción necesariamente tienen que estar
agrupados por el control Marco. Para agrupar controles, dibuje primero el control
Marco y, a continuación, dibuje los controles dentro de Marco.
Propiedades
Caption Título de marco.
Enabled Determina si está habilitado para responder a las acciones del
usuario.
Name Nombre del control.
Visible Determina si el Marco y los controles que contiene están
visibles o no.
Control Casilla de Verificación (CheckBox)
Las casillas de verificación se utilizan para proporcionar al usuario opciones de
tipo Si/No o Verdadero/Falso. Cuando el usuario selecciona una opción (activa la
casilla), aparece una marca de verificación () dentro de la casilla.
Propiedades
Caption Descripción que acompaña a la casilla.
Enabled True/False. Determina si está habilitado para responder a las
acciones del usuario.
Name Nombre del control.
SENATI-Computación e Informática 209
Microsoft Office
Excel 2007
Value 0 – Unchecked (Vacío, no marcado)
1 – Checked (Marcado)
2 – Grayed (Gris, Indefinido)
Visible Determina si la casilla está visible o no.
Eventos
Click Ocurre cuando el usuario hace clic sobre la casilla.
Control Botón de Opción (OptionButton) Estos controles se utilizan para que el usuario seleccione una opción de un grupo opciones. La opción seleccionada tiene un punto en el centro.
Propiedades
Caption Descripción que acompaña a la opción.
Enabled True/False. Determina si está habilitado para responder a las
acciones del usuario.
Name Nombre del control.
Value True/False, marcado o no marcado.
Visible True/False.
Determina si el
botón está visible
o no.
Eventos
Click Ocurre cuando el
usuario hace clic
sobre el botón.
210 SENATI-Computación e Informática
ción con VBA
Control Cuadro de Lista (ListBox)
Un control ListBox muestra una lista de elementos entre los cuales el
usuario puede seleccionar uno o más elementos. Si el número de elementos
supera el número que puede mostrarse, se agregará automáticamente una barra de
desplazamiento al control ListBox
La propiedad List es un arreglo que contiene los elementos de la lista, y comienza
con índice 0. La propiedad ListCount establece el número total de elementos de
la lista. La propiedad ListIndex contiene el índice del elemento seleccionado, el
cual es un número entre 0 (primer elemento) y el número total de elementos en la
lista –1 (ListCount – 1). Si no se selecciona ningún elemento, el valor de la
propiedad ListIndex será –1.
La propiedad NewIndex contiene el índice del último elemento añadido a la lista.
Esto puede ser útil si desea hacer algo con el elemento añadido, por ejemplo, que
sea el elemento actualmente seleccionado.
Propiedades
Enabled True/False. Determina si el control responde a las acciones del
usuario.
List Arreglo con los elementos de la lista.
ListCount Número de elementos de la lista.
ListIndex Elemento seleccionado.
MultiSelect Establece si es posible seleccionar varios elementos o uno
solo.
Name Nombre del control.
Selected Arreglo de valores lógicos paralelo y del mismo tamaño al
arreglo list, indica que elementos han sido seleccionados
(True) de la lista. Se utiliza en lugar de ListIndex cuando
establecemos la propiedad Multiselect en 1 ó 2.
Sorted True/False. Establece los elementos se ordenan
alfabéticamente.
Style Establece el comportamiento del control.
Text Devuelve el elemento seleccionado en el cuadro de lista; el
valor de retorno es siempre equivalente al que devuelve la
expresión List(ListIndex). Es de sólo lectura en tiempo de
diseño y es de sólo lectura en tiempo de ejecución.
Métodos
AddItem Permite añadir nuevos elementos a la lista.
RemoveItem Permite eliminar elementos de la lista.
SENATI-Computación e Informática 211
Microsoft Office
Excel 2007
Eventos
Click Ocurre cuando el usuario interactúa con el control.
Ejemplos:
Muestra en el Cuadro de Texto
txtGaseosa el elemento seleccionado
Private Sub lstGaseosas_Click()
txtGaseosa.Text =
lstGaseosas.Text
End Sub
Añade un nuevo elemento al Cuadro de Lista lstGaseosas
Private Sub cmdAgregar_Click()
Dim strNuevoElemento As String
strNuevoElemento = InputBox("Ingrese una nueva gaseosa:", _
"Nueva gaseosa")
If Trim(strNuevoElemento) <> "" Then
lstGaseosas.AddItem strNuevoElemento
End If
End Sub
Elimina el elemento actual del Cuadro de Lista lstGaseosas
Private Sub cmdEliminar_Click()
If lstGaseosas.ListIndex <> -1 Then
lstGaseosas.RemoveItem lstGaseosas.ListIndex
End If
End Sub
Control Cuadro Combinado (ComboBox) Un control ComboBox combina las características de un control TextBox y un control ListBox; los usuarios pueden introducir información en la
parte del cuadro de texto o seleccionar un elemento en la parte de cuadro de lista
del control.
212 SENATI-Computación e Informática
ción con VBA
Para agregar o eliminar elementos en un control ComboBox, se usa el método AddItem o RemoveItem. Establezca las propiedades List, ListCount y ListIndex
para permitir a un usuario tener acceso a los elementos de un control ComboBox.
Como alternativa, puede agregar elementos a la lista mediante la propiedad List
en tiempo de diseño.
Propiedades
Enabled True/False. Determina si el control responde a las acciones del
usuario.
List Arreglo con los elementos de la lista.
ListCount Número de elementos de la lista
ListIndex Elemento seleccionado.
Name Nombre del control.
Sorted True/False. Establece si los elementos se ordenan
alfabéticamente.
Style Establece el comportamiento del control.
Text Texto que contiene el control.
Métodos
AddItem Permite añadir nuevos elementos a la lista.
RemoveItem Permite eliminar elementos de la lista.
Eventos
Click Ocurre cuando el usuario interactúa con el control
Change Ocurre cuando el valor de la propiedad Text es modificado.
Constante
Valor
Descripción
vbComboDropDown
0
(Predeterminado) Cuadro combinado desplegable.
Incluye una lista desplegable y un cuadro de texto.El Usuario puede seleccionar datos en la lista o escribir en cuadro de texto.
vbComboSimple
1
Cuadro combinado simple. Incluye un cuadro de
texto y una lista, que no se despliega. Incremente
la propiedad Height para mostrar más elementos
de la lista.
vbComboDrop-
DownList
2
Lista desplegable. Este estilo sólo permite la
selección desde la lista desplegable.
SENATI-Computación e Informática 213
Microsoft Office
Excel 2007
Propiedad Style
Esta propiedad establece el comportamiento del control ComboBox, y puede
tomar los siguientes valores:
Ejemplo
En la siguiente interfaz se ilustra el uso del control ComboBox y la propiedad
Style.
Muestra la gaseosa seleccionada por el usuario en la etiqueta lblGaseosa
Private Sub cboGaseosas_Click()
lblGaseosa.Caption = cboGaseosas.Text
End Sub
Actualiza la etiqueta lblGaseosa cuando el usuario modifica el control
cboGaseosas
Private Sub cboGaseosas_Change()
lblGaseosa.Caption = cboGaseosas.Text
End Sub
Muestra el encuestado seleccionado por el usuario en la etiqueta
lblEncuestado
Private Sub cboEncuestados_Click()
lblEncuestado.Caption = cboEncuestados.Text
End Sub
214 SENATI-Computación e Informática
ción con VBA
Muestra en la etiqueta lblCiudad el elemento seleccionado del control cboCiudades
Private Sub cboCiudades_Click()
lblCiudad.Caption = cboCiudades.Text
End Sub
Funciones VBA InputBox, MsgBox Una de las formas más simples de obtener información para y desde el usuario es
utilizando las funciones MagBox e InpuBox respectivamente.
Función MsgBox()
Los cuadros de mensaje ofrecen un modo simple y rápido de consultar a los
usuarios por información simple o para permitirles tomar decisiones sobre el
camino que su programa debe tomar. Puede usar esta función para mostrar
diferentes tipos de mensaje y botones con los cuales el usuario da una respuesta.
Rpta = MsgBox("¿Está seguro de eliminar a este cliente?" vbQuestion + vbYesNo, "Confirmación")
Función InpuBox()
La función InputBox muestra un mensaje en un cuadro de diálogo, espera que el
usuario escriba un texto o haga clic en un botón y devuelve un tipo String con el
contenido del cuadro de texto.
strCodigo = InputBox("Ingrese el código del cliente a
buscar:","Búsqueda", "CLI0001")
Metodo GetOpenfilename , GetSaveAsFileName Función GetOpenfilename ()
Este método nos permite desplegar el cuadro de dialogo abrir (del menu archivo)
pero no abre el archivo indicado.
SENATI-Computación e Informática 215
Microsoft Office
Excel 2007
El método nos devuelve una cadena con la ruta y nombre del archivo seleccionado.
Sintaxis Object.GetOpenFilename(FileFilter,FilterIndex,Title,ButtonText,Multiselect)
Argumentos
FileFilter. Opcional. Una serie especifica con criterios de filtro de
archivo.
FilterIndex. Opcional. Los números del índice por defecto bajo criterios
de filtro de archivo.
Titulo. Opcional .El titulo del cuadro de dialogo. si se omite, el titulo..
mostrara "Abrir"
ButtonText. Solo para Macintosh
Multiselect. Opcional. Si es verdadero, se pueden seleccionar varios
nombres de archivos
El argumento Filtro de archivo determínalo que muestra el cuadro de
dialogo de los archivos del tipo lista desplegable. Consiste en pares de
series de filtro de archivo seguido del comodín especificado. Si se omite
sera por defecto : "All File(*.*),*.*"
Ejemplo El argumento Filterindex especificara el tipo de archivo que aparece por defecto, 'el titulo del argumento es un texto que se despliega en la barra del título. si el
argumento de multiselect es verdadero,el usuario puede seleccionar varios
archivos(y se devolveran en una serie)
Private Sub CommandButton1_Click()
Dim filtrox As String
Dim FilterIndex As Integer
Dim titulo As String
Dim nombreArchivo As Variant
'Configurar Filtro para la lista de archivos
filtrox = "Archivos de texto (*.txt),*.txt," & _
"Word(*.doc),*.doc, " & _
"Excel (*.xls), *.xls, " & _
"Power Point(*.ppt), *.ppt, " & _
"Todos los archivos (*.*),*.* "
216 SENATI-Computación e Informática
ción con VBA
'Mostrar por defecto todos los archivos
FiltroIndex = 3
titulo = "Ejemplo"
'Obtener Nombre del archivo
nombreArchivo = Application.GetOpenFilename(FileFilter:=filtrox,
FilterIndex:=FiltroIndex, Title:=titulo)
TextBox1.Text = nombreArchivo
End Sub
Función GetSaveAsFileName
Permite grabar un archivo
Ejemplo
Private Sub CommandButton2_Click()
Dim FileSaveName As Variant
FileSaveName = Application.GetSaveAsFilename( _
Filefilter:="Libro de microsoft Office Excel (*.Xls), *.Xls," & "Ficheros de
TEXTO (*.TXT), *.TXT", _
Title:="Guardar Archivo", _
InitialFileName:="MyLibro666", _
FilterIndex:=1)
'Si Anulamos la operacion con Cancelar...
If FileSaveName = False Then
MsgBox "El libro no será Guardado", vbInformation + vbOKOnly,
"ATENCION:"
Exit Sub
End If
ActiveWorkbook.SaveAs Filename:=FileSaveName
End Sub
Crear y abrir libro Crear un libro nuevo
Para crear un nuevo libro en Visual Basic, utilice el método Add. El siguiente
procedimiento crea un nuevo libro. Microsoft Excel asigna automáticamente el
SENATI-Computación e Informática 217
Microsoft Office
Excel 2007
nombre BookN al libro, donde N es el siguiente número disponible. El nuevo libro se convertirá en el libro activo.
Sub CrearNuevoLibro()
Workbooks.Add
End Sub
Abrir un libro
Al abrir un nuevo libro utilizando el método Open, se convierte en un miembro
del conjunto Workbooks.
El siguiente procedimiento abre un libro denominado Amortizacion.xls, ubicado
en la carpeta SENATI de la unidad C.
Sub AbrirLibro()
Workbooks.Open("C:\SENATI\Amortizacion.xls")
End Sub
Cuestionarios
1. Realice un formulario aplicativo de acuerdo a las necesidades de su área donde labores, donde pueda aplicar los conocimiento aprendidos en programación.
2. Crear un formulario aplicativo que permita ingresar una contraseña al abrir una hoja de cálculo.
3. Crear un atajo que permita llamar a un formulario que contiene un calendario.
218 SENATI-Computación e Informática
ción con VBA
Ejercicios utilizando Macro, Formulario y VBA
Ejercicio 1: Números primos
Grabar el archivo con el nombre primo.xls
Option Explicit
Sub primos() ' Igual que primos pero evitando usar etiquetas y goto
Dim i As Long
Dim j As Long
Dim p As Long
Dim n As Long
Dim primo As Boolean
n = InputBox("¿Hasta que número? (máximo 821507)", "Calculo de primos")
p = 1
Application.Workbooks("primos.xls").Sheets("Hoja1").Range("c6").Select
ActiveCell.Value = 2
ActiveCell.Offset(1, 0).Value = 3
For i = 5 To n Step 2
primo = True
For j = 3 To Sqr(i) Step 2
If (i / j) - Int(i / j) = 0 Then primo = False: Exit For
Next j
If primo Then
p = p + 1
ActiveCell.Offset(p, 0).Value = i
End If
Next i End
Sub
Sub Limpiar()
Range("B6").Select
Selection.CurrentRegion.Select
Selection.ClearContents
Range("B6").Select
End Sub
SENATI-Computación e Informática 219
Microsoft Office
Excel 2007
Ejercicio 2: Fechas
Option Explicit
Sub nacimiento()
Dim dias As Integer, Dsemana As Integer, Factual As Date, d As String, cumple As Date
Rem Dsemana es una variable que da un número que indica el día de la semana
Rem dado por la función WEEKDAY, que en Excel es =DIASEM(fecha)
Static Fnacimiento As Date
Factual = Date 'Date es la función de VBA equivalente a =HOY()
Fnacimiento = Factual
Fnacimiento = InputBox(Prompt:="Introduzca su fecha de nacimiento", _
Title:="Formato DD-MM-AAAA", Default:=Fnacimiento)
dias = Factual - Fnacimiento
Dsemana = Application.WorksheetFunction.Weekday(Fnacimiento)
Select Case Dsemana
Case 1: d = "Domingo"
Case 2: d = "Lunes" Case
3: d = "Martes" Case 4: d =
"Miercoles" Case 5: d =
"Jueves" Case 6: d =
"Viernes" Case 7: d =
"Sabado" End Select
MsgBox Prompt:="Usted nació un " & d & " hace " & dias & " días" & Chr(10) _
& "Tiene " & CalEdad(Fnacimiento) & " Años", _
Title:="Esta información es correcta siempre que hoy sea " & Factual
End Sub 220 SENATI-Computación e Informática
ción con VBA
'Función que calcula la edad en años
Function CalEdad(Fnacimiento As Date)
Dim Fecha As Date
CalEdad = Abs(DateDiff("yyyy", Fnacimiento, Date))
Fecha = DateAdd("YYYY", CalEdad, Fnacimiento)
If Fecha > Date Then CalEdad = CalEdad - 1
End Function
Sub Trimestres()
'Proporciona el trimestre en el que se encuentra una fecha
Dim LaFecha As Date
Dim Msj
LaFecha = InputBox("Escriba una fecha:")
Msj = "Trimestre: " & DatePart("q", LaFecha)
'DatePart es una función VBA
MsgBox Msj
End Sub
Ejercicio 3: TIR
Option Explicit
Sub CalculaTIR() Range("F7").Value = 0 Range("H7").Value = 1 Do While Abs(Range("G8")) > 0.00001 'error admitido
If Range("G8") < 0 Then Range("H7") = Range("G7")
Else Range("F7") = Range("G7")
End If 'La siguiente línea se utiliza para retrasar la ejecución de la macro 'de esta forma se puede ver como converge la tasa a la TIR Application.Wait Now + TimeValue("00:00:1")
Loop End Sub
SENATI-Computación e Informática 221
Microsoft Office
Excel 2007
Ejercicio 4: =PAGO
Option Explicit
Sub prestamo()
Static Principal 'Variable estática. No cambia
Static Tasa
Static Terminos
Dim Pago As Double
Principal = Application.InputBox(Prompt:="Principal (100000 por jemplo)",Default:=Principal)
Tasa = Application.InputBox(Prompt:="Tipo de interés nominal anual (4,75 por ejemplo)", Default:=Tasa)
Terminos = Application.InputBox(Prompt:="Número de años (30 por ejemplo)",Default:=Terminos)
'Vea como se usa la función de Excel Pmt (Pago) sin necesidad de calcularla en una celda
Pago = Application.WorksheetFunction.Pmt(Tasa / 1200, Terminos * 12, Principal)
MsgBox Prompt:="La Mensualidad es " & Format(-Pago, "Currency"), Title:="Calculadora de Préstamos"
End Sub
Ejercicio 5: Listado de hojas
222 SENATI-Computación e Informática
ción con VBA
Option Explicit Sub NombreHojas() 'Pone los nombres de las hojas, salvo la primera
Dim contador As Integer For contador = 1 To Sheets.Count
With Sheets(1) Cells(contador + 5, 6).Value = Sheets(contador).Name
End With Next
End Sub
Sub Limpia() Range("F6").Select Selection.CurrentRegion.Select Selection.ClearContents Range("A1").Select
End Sub
Ejercicio 6: Eliminar registros en blanco
Option Explicit
Sub EliminarFilasEnBlanco()
Dim strC As String, lngFila As Long With Worksheets("Hoja1") 'Nombre de la hoja
For lngFila = 1 To .UsedRange.Rows.Count If WorksheetFunction.CountA(.Rows(lngFila)) = 0 Then strC = strC & lngFila & ":" & lngFila & ","
Next lngFila Application.ScreenUpdating = False .Range(Left(strC, Len(strC) - 1)).Delete Application.ScreenUpdating = True
End With End Sub
SENATI-Computación e Informática 223
Microsoft Office
Excel 2007
Ejercicio 7: Funciones personalizadas
Option Explicit
Function DiasLaborablesYSabados(Fecha_Inicial As Date, Fecha_Final As Date, Optional Festivos As Range) As Long
Dim Laborables As Long
Dim i As Long
Dim c As Variant
Dim F As Long
Dim esta As Boolean
esta = False
Laborables = 0
If Festivos Is Nothing Then
For i = Fecha_Inicial To Fecha_Final
If i Mod 7 <> 1 Then
Laborables = Laborables + 1
224 SENATI-Computación e Informática
ción con VBA
End If
Next i
Else
For i = Fecha_Inicial To Fecha_Final
If i Mod 7 <> 1 Then
esta = False
For Each c In Festivos
F = CDate(c)
If i = F Then esta = True: Exit For
Next c
If Not esta Then Laborables = Laborables + 1
End If
Next i
End If
DiasLaborablesYSabados = Laborables
End Function
Ejercicio 8: Filtrar la base de datos
Sub Filtra()
Range("basedatos").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _ Range("H5:K6"), CopyToRange:=Range("H12:K12"), Unique:=False Range("A1").Select
End Sub
SENATI-Computación e Informática 225
Microsoft Office
Excel 2007
Sub Auto_Open()
'Copiamos y pegamos, con pegado especial valores, las celdas y9.ab9 'que son las que generan la base de datos. Range("Y9:AB9").Select Selection.Copy Range("B6:E1005").Select ActiveSheet.Paste Application.CutCopyMode = False Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False 'Nombramos la base de datos como basedatos Range("B5").Select Selection.CurrentRegion.Select ActiveWorkbook.Names.Add Name:="basedatos", RefersToR1C1:="=Hoja1!R5C2:R1005C5"
Range("A1").Select End Sub
Ejercicio 9: Eliminar valores duplicados de una lista
Option Explicit
Function CuentaListaA()
226 SENATI-Computación e Informática
ción con VBA
Application.Workbooks("EliminarValores.xls").Worksheets("Hoja1").Range("C5").Activate
Selection.End(xlDown).Select
CuentaListaA = ActiveCell.Row - 5
End Function
Sub Repetidos()
'Borra los elementos de la columna E que esten en la C
'Pero sólo los borra una vez. Si se repiten en C sólo borra el 1º
'Si se quiere que se eliminen todos quitar del programa la vble. "salir"
Dim posicion As Long
Dim salir As Boolean
Dim comodin
Dim respuesta As String * 5
Dim filasiniciales As Long
Dim filasfinales As Long
filasiniciales = CuentaListaA
Range("E6").Select
posicion = 1
While ActiveCell.Value <> ""
comodin = ActiveCell.Value
Range("C6").Select
salir = False
While ActiveCell.Value <> "" And salir = False
If ActiveCell.Value = comodin Then
ActiveCell.Font.Bold = True respuesta = MsgBox("¿Deseas borrar la celda " & ActiveCell.Address & "?", 4, "¡¡Encontrado!!")
If respuesta = vbYes Then
'Edición, Eliminar, Desplazar las celdas hacia arriba
Selection.Delete Shift:=xlUp
End If
salir = True
Else
ActiveCell.Offset(1, 0).Select
End If
Wend
posicion = posicion + 1
Range("E6").Select
ActiveCell.Offset(posicion - 1, 0).Select
Wend
filasfinales = CuentaListaA
MsgBox "La Lista A inicialmente tenia " & filasiniciales & " filas." & Chr(13) _
& "Se han eliminado " & filasiniciales - filasfinales & " filas." & Chr(13) _
& "Por tanto, quedan " & filasfinales & " filas."
End Sub
SENATI-Computación e Informática 227
Microsoft Office
Excel 2007
Ejercicio 10: Rellenar y Eliminar registros de una lista
'En las celdas vacias de una tabla copia el valor de la celda precedente
Sub RellenarCeldas()
Range("B5").Select
228 SENATI-Computación e Informática
Capítulo
Funciones personalizadas
En este capítulo trataremos:
Creación de funciones personalizadas con VBA Uso de controles ActiveX
SENATI-Computación e Informática 229
ersonalizas
Uso de módulos para Funciones
Los módulos permiten dividir un programa muy grande en partes mucho más pequeñas
y manejables.
La división de un programa en unidades más pequeñas o funciones presenta –entre
otras– las ventajas siguientes:
Ventajas
1. Modularización
Cada función tiene una misión muy concreta, de modo que nunca tiene un
número de líneas excesivo y siempre se mantiene dentro de un tamaño
manejable.
Una misma función puede ser invocada muchas veces en un mismo programa,
e incluso puede ser reutilizada por otros programas. Cada función puede ser
desarrollada y comprobada por separado.
2. Ahorro de memoria y tiempo de desarrollo
En la medida en que una misma función es utilizada muchas veces, el número
total de líneas de código del programa disminuye, y también lo hace la
probabilidad de introducir errores en el programa.
3. Independencia de datos y ocultamiento de información.
Una de las fuentes más comunes de errores en los programas de computador
son los efectos colaterales o perturbaciones que se pueden producir entre
distintas partes del programa.
Es muy frecuente que al hacer una modificación para añadir una funcionalidad o
corregir un error, se introduzcan nuevos errores en partes del programa que antes
funcionaban correctamente.
Características
Una función es capaz de mantener una gran independencia con el resto del
programa, manteniendo sus propios datos y definiendo muy claramente la
interfaz o comunicación con la función que la ha llamado y con las funciones a
las que llama, y no teniendo ninguna posibilidad de acceso a la información que
no le compete.
La principal característica de una función es que ésta puede ser utilizada en una
expresión porque tiene un valor de retorno.
La sintaxis correspondiente a una función es la siguiente:
Function nombre ([parámetros]) [As tipo]
[sentencias]
[nombre = expresion]
[Exit Function]
SENATI-Computación e Informática 231
Microsoft Office
Excel 2007
[sentencias]
[nombre = expresion]
End Function
A continuación se describe en un cuadro los elementos de la función
NOMBRES
FUNCION
nombre
Define el nombre de la función
Parámetros
Son los argumentos que son pasados cuando se llama a la función.
As Tipo
Define el tipo de dato que devuelve la función, pueden ser:
Boolean Lógico, devuelve dos valores True o False.
Numéricos Byte, Integer, Single, Double
String Aceptar caracteres alfanuméricos de Desde 1 a 65.400
aproximadamente
Variant Cualquier valor numérico, String.
Sentencias
Es el código del programa en VBA.
expresión
Valor de retorno de la función
Exit Function
Permite salir de una función antes de que ésta finalice.
End Function
Marca el final del código de la función.
Uso de argumentos en las funciones Los argumentos son los parámetros que se pasan a la función.
Argumentos opcionales
Se puede especificar argumentos opcionales.
Cuando un argumento es opcional y en la llamada es omitido, el valor que se le pasa es
un Variant con valor Empty. A los argumentos opcionales se les puede dar en la
definición de la función un valor por defecto para el caso en que sean omitidos en la
llamada, como por ejemplo:
Function AREAT(base As integer, altura As integer, Optional n As Integer)
AREAT=base * altura / 2
End Sub
232 SENATI-Computación e Informática
ersonalizadas
Argumentos arreglos
Para utilizar argumentos Array utilice la palabra ParamArray en la definición del
procedimiento, como por ejemplo:
Public Function maximo(ParamArray numeros())
For Each x in numerous
Sentencias
maximo = x
Next x
End Function
Ejecutar una función Para ejecutar una función utilice la siguiente sintaxis:
= nombre([argumentos])
Donde argumentos son una lista de constantes, variables o expresiones separadas por
comas que son pasadas a la función.
En principio, el número de argumentos debe ser igual al número de parámetros de la
función.
Los tipos de los argumentos deben coincidir con los tipos de sus correspondientes
parámetros, de lo contrario puede haber fallos importantes en la ejecución del
programa.
En cada llamada a una función hay que incluir los paréntesis, aunque ésta no tenga
argumentos.
El siguiente ejemplo corresponde a una función que devuelve como resultado la raíz
cuadrada de un número N:
Function Raiz (N As Double) As Double If N < 0 Then
Exit Function
Else
Raiz = Sqr(N)
End If
End Function
La llamada a esta función se hace de la forma siguiente:
= Raiz(4)
SENATI-Computación e Informática 233
Microsoft Office
Excel 2007
Recursividad
Se dice que una función
(Function) es recursiva si se
llaman a sí mismos.
A continuación se presenta
una ejemplo de una función
que calcula el factorial de un
número programada de
forma recursiva.
„Cálculo de Factorial
Function Fact (N As Integer) As Long
If N = 0 Then Fact = 1 Else Fact = N * Factorial (N - 1)
End Function
En este ejemplo, si la variable N que se le pasa a la función vale 0, significará
que se ha llegado al final del proceso, y por tanto se le asigna el valor 1 al valor
del factorial (recordar que 0! = 1). Si es distinto de 0, la función se llama a ella
misma, pero variando el argumento a (N-1), hasta llegar al punto en el que N-
1=0, finalizándose el proceso.
Para invocar a la función utilice =FACT(6)
Paso a Paso: Crear la función área del triangulo
1. Realizar una función que permita calcular el área del triángulo.
2. AREAT = ( B x H ) / 2
3. Hacer clic en la ficha , botón
4. Hacer clic en el menú , botón
Function AREAT(base As integer, altura As integer, Optional n As Integer)
AREAT=base * altura / 2
End Sub
5. Cerrar el Editor de VBA
6. Estando en Excel, Diseñar la siguiente hoja
7. En la celda B5 escribir la función: =AreaT (B3,B5)
234 SENATI-Computación e Informática
ersonalizadas
Controles Active X
Veamos veremos una serie de controles que le pueden brindar interactividad a nuestras
aplicaciones Excel
Los Controles ActiveX son objetos gráficos que se colocan en un formulario
(userforms) o en una determinada posición de la hoja de trabajo, con el objetivo de
mostrar, seleccionar o introducir (capturar) datos, para la realización de una acción
determinada (ej. ejecución de una macro) o para facilitar la lectura de un formulario.
Estos objetos pueden consistir por ejemplo en cuadros de texto, cuadros de lista
(ComboBox), botones de opciones, casillas de verificación, botones de comandos,
barras de desplazamiento u otros elementos.
A continuación se describe los Controles
1. Cuadro de Texto
Un Cuadro de Texto nos permite capturar información, la cual puede ser
numérica, alfabética o alfanumérica.
2. Botón de Comando
Este tipo de control es uno de los más conocidos. Entre otras cosas, este control
nos permite por ejemplo la ejecución de una macro, o marcar el inicio de la
realización de una determinada actividad previamente asignada o programada.
3. Casilla de Verificación
Una casilla de verificación es utilizada normalmente para capturar información
previamente estructurada. Normalmente se presentan en grupos y es posible
seleccionar más de una opción.
4. Botón de Opción
Un botón de opción en general presenta las misma características que un grupo
de casillas de verificación, sólo que en este caso no es posible seleccionar más de
una opción.
5. Cuadro Combinado
Su utilización es recomendable cuando la lista de opciones es bastante extensa.
Inclusive puede ser configurado para que mostrar un cierto número de opciones
solamente, debiendo desplazarse el usuario con la barra de desplazamiento que se
generará automáticamente para buscar las otras opciones no visualizadas.
6. Cuadro de Lista
Un cuadro de lista contiene una lista de los elementos que pueden ser
seleccionados. En términos generales es prácticamente lo mismo que un cuadro
SENATI-Computación e Informática 235
Microsoft Office
Excel 2007
combinado, sólo que no posee el menú desplegable, sino que es necesario desplazarse a través de él con el cursor.
7. Botón o Control de Número
Este control puede ser usado para aumentar o disminuir el valor de un
determinado parámetro o celda vinculada. Cuenta con dos flechas, una hacia
arriba y otra hacia abajo, o una hacia la derecha y otra hacia la izquierda. Al
presionar alguna de ellas aumentará o disminuirá el valor según corresponda.
8. Botón de Alternar
Este botón puede ser utilizado para que el usuario seleccione o no una
determinada característica, ya que este control tiene la ventaja de que una vez
seleccionado permanece seleccionado, devolviendo el valor de Verdadero o Falso
según corresponda. Este botón además puede ser vinculado a una determinada
celda, en la cual devolverá el valor lógico anteriormente mencionado (verdadero
o falso).
Paso a Paso: Uso de control ActiveX - 1
1. Diseñar una aplicación que muestre en un control ListBox1 los números pares y
en un ListBox2 los números impares
2. Hacer clic en la ficha , botón
3. Hacer clic en el menú , botón y diseñar el formulario mostrado a continuación.
236 SENATI-Computación e Informática
ersonalizadas
4. Hacer clic en el menú , botón y escriba el siguiente
programa
General Declaraciones
Dim SumaImpar As Integer
Dim SumaPar As Integer
Dim cont As Integer
Private Sub CommandButton1_Click()
Randomize
SW = 1
cont = 1
SumaImpar = 0
SumaPar = 0
ListBox1.Clear
ListBox2.Clear
For cont = 1 To 10
If SW = 1 Then
ListBox1.AddItem cont
SumaImpar = SumaImpar + cont
SW = 0
Else
ListBox2.AddItem cont
SumaPar = SumaPar + cont
SW = 1
End If
Next
TextBox1.Text = SumaImpar
TextBox2.Text = SumaPar
End Sub
Paso a Paso: Uso de control ActiveX - 2
1. Diseñar una aplicación que permita ingresar los siguientes datos a controles
TextBox: Nombres, Apellidos, Dirección, teléfono y suledo. El ingreso de
datos debe ser consistenciado. Luego estos datos deben ser pasados a una
grilla (Control FlexGrid). Calcular el total del sueldo.
SENATI-Computación e Informática 237
Microsoft Office
Excel 2007
2. Hacer clic en la ficha , botón
3. Hacer clic en el menú , botón y diseñar el
formulario mostrado a continuación.
Hacer clic en el menú Herramientas, Controles
adicionales para agregar el
control FlexGrid a la caja
de controles.
4. Hacer clic en el menú , botón . Edite el siguiente código
Public Sub IngNum(KeyAscii As MSForms.ReturnInteger)
If (KeyAscii = 8) Then Exit Sub
If Not (KeyAscii >= Asc("0") And KeyAscii <= Asc("9")) Then KeyAscii = 0
End Sub
Public Sub IngNumTelf(KeyAscii As MSForms.ReturnInteger)
If (KeyAscii = 8) Then Exit Sub
If (KeyAscii = Asc("-")) Then Exit Sub
If Not (KeyAscii >= Asc("0") And KeyAscii <= Asc("9")) Then KeyAscii = 0
End Sub
Public Function IngFechas(Fecha As Variant) As Boolean
IngFechas = IsDate(Fecha)
End Function
238 SENATI-Computación e Informática
ersonalizadas
Public Sub IngCar(KeyAscii As MSForms.ReturnInteger)
If (KeyAscii = 8) Then Exit Sub
If (KeyAscii = 32) Then Exit Sub
If (KeyAscii = Asc(".")) Then Exit Sub
If (KeyAscii >= Asc("0") And KeyAscii <= Asc("9")) Then KeyAscii = 0
Select Case KeyAscii
Case Is >= Asc("A") And KeyAscii <= Asc("Z"): Exit Sub
Case Is >= Asc("a") And KeyAscii <= Asc("z"): Exit Sub
Case Is = Asc("á"): Exit Sub
Case Is = Asc("é"): Exit Sub
Case Is = Asc("í"): Exit Sub
Case Is = Asc("ó"): Exit Sub
Case Is = Asc("ú"): Exit Sub
Case Else: KeyAscii = 0
End Select
End Sub
5. Hacer clic en el menú , botón y escriba el siguiente
programa
General Declaraciones
Dim FILA As Integer
Private Sub CommandButton1_Click()
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
End Sub
Private Sub CommandButton2_Click()
FILA = FILA + 1
MSFlexGrid1.Rows = MSFlexGrid1.Row + FILA
MSFlexGrid1.TextMatrix(FILA, 1) = TextBox1.Text
MSFlexGrid1.TextMatrix(FILA, 2) = TextBox2.Text
SENATI-Computación e Informática 239
Microsoft Office
Excel 2007
MSFlexGrid1.TextMatrix(FILA, 3) = TextBox3.Text
MSFlexGrid1.TextMatrix(FILA, 4) = TextBox4.Text
MSFlexGrid1.TextMatrix(FILA, 5) = TextBox5.Text
Label7.Caption = Val(Label7.Caption) + Val(TextBox5.Text)
Call CommandButton1_Click
TextBox1.SetFocus
End Sub
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Call IngCar(KeyAscii)
End Sub
Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Call IngCar(KeyAscii)
End Sub
Private Sub TextBox3_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Call IngCar(KeyAscii)
End Sub
Private Sub TextBox4_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Call IngNumTelf(KeyAscii)
End Sub
Private Sub TextBox5_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Call IngNum(KeyAscii)
End Sub
Private Sub UserForm_Activate()
FILA = 0
MSFlexGrid1.Cols = 6
MSFlexGrid1.TextMatrix(0, 1) = Label1.Caption
MSFlexGrid1.TextMatrix(0, 2) = Label2.Caption
MSFlexGrid1.TextMatrix(0, 3) = Label3.Caption
MSFlexGrid1.TextMatrix(0, 4) = Label4.Caption
MSFlexGrid1.TextMatrix(0, 5) = Label5.Caption
240 SENATI-Computación e Informática
ersonalizadas
MSFlexGrid1.ColWidth(0) = 10
MSFlexGrid1.ColWidth(1) = 2000
MSFlexGrid1.ColWidth(2) = 2000
MSFlexGrid1.ColWidth(3) = 2000
MSFlexGrid1.ColWidth(4) = 800
MSFlexGrid1.ColWidth(5) = 1000
WindowState = 2
End Sub
Paso a Paso: Uso de control ActiveX - 3
1. Diseñar una aplicación que permita mostrar el cuadro de desembolsos de un
préstamo, dado a un determinado número de periodos y tasa de interés,
aplicando el cálculo de la amortización con el método Alemán. Así mismo se
debe dar consistencia al ingreso de datos numéricos.
2. Fórmulas a utilizar.
Préstamo = Préstamo período anterior – Amortización
Saldo = Saldo anterior – Amortización
Interés = Saldo del período * Tasa de interés
Cuota = Amortización + Interés
3. Hacer clic en la ficha , botón
4. Hacer clic en el menú , botón y diseñar el formulario mostrado a continuación.
Hacer clic en el menú Herramientas, Controles
adicionales para agregar el
control FlexGrid a la caja
de controles.
SENATI-Computación e Informática 241
Microsoft Office
Excel 2007
5. Hacer clic en el menú , botón y escriba el siguiente programa
General Declaraciones „Permite dar consistencia al ingreso de datos numéricos
Public Sub IngNumero(ByVal KeyAscii As MSForms.ReturnInteger)
If (KeyAscii >= 48 And KeyAscii <= 56) Then
Exit Sub
Else
KeyAscii = 0
End If
End Sub
Si el carácter ingresado
no es número, se impide
el ingreso del caracter
Ascii 48 =0
Ascii 57 = 9
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Call IngNumero(KeyAscii)
End Sub
Private Sub CommandButton1_Click()
Dim xcapital As Single
Invoca al procedimiento IngNumero
Dim xinteres As Single
Dim xperiodos As Single
Dim xamortiza As Single
Dim xpa As Single
xcapital = Val(TextBox1.Text)
xinteres = Val(TextBox2.Text)
xperiodos = Val(TextBox3.Text)
xamortiza = xcapital / xperiodos
Declarar las variables a utilizarse
Asigna datos a las variables
Asigna como No.de
filas del control
FlexGrid, el número
de periodos
MSFlexGrid1.Rows = Val(TextBox3.Text) + 1
For f = 1 To Val(TextBox3.Text)
MSFlexGrid1.TextMatrix(f, 1) = f
MSFlexGrid1.TextMatrix(f, 2) = xamortiza
If f = 1 Then
MSFlexGrid1.TextMatrix(f, 3) = xcapital
Else
Crea un ciclo
repetitivo de 1 hasta
el No. de periodos
MSFlexGrid1.TextMatrix(f, 3) = xpa - xamortiza
End If
242 SENATI-Computación e Informática
ersonalizadas
xpa = Val(MSFlexGrid1.TextMatrix(f, 3))
MSFlexGrid1.TextMatrix(f, 4) = xpa * (xinteres / 100)
Next
xi = Val(MSFlexGrid1.TextMatrix(f, 4))
MSFlexGrid1.TextMatrix(f, 5) = xamortiza + xi
End Sub
Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Call IngNumero(KeyAscii)
End Sub Invoca al procedimiento IngNumero
Private Sub TextBox3_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Call IngNumero(KeyAscii)
End Sub
Private Sub UserForm_Activate()
MSFlexGrid1.Cols = 6
MSFlexGrid1.TextMatrix(0, 1) = "No."
Invoca al procedimiento IngNumero
Asigna la cantidad de filas
para el FlexGrid.
MSFlexGrid1.TextMatrix(0, 2) = "Amortización"
MSFlexGrid1.TextMatrix(0, 3) = "Saldo"
MSFlexGrid1.TextMatrix(0, 4) = "Interés"
MSFlexGrid1.TextMatrix(0, 5) = "Cuota"
End Sub
Paso a Paso: Uso de control ActiveX - 4
1. Diseñar una aplicación que permita asignara puntajes al azar a 10
participantes a un concurso de belleza, se evalúan por 4 criterios: Belleza,
inteligencia, cuerpo, desenvolvimiento. Los puntajes están entre 1 y 10 por
cada criterio. Al final mostrar el número de la participante ganadora y su
puntaje
2. Hacer clic en la ficha , botón
SENATI-Computación e Informática 243
Microsoft Office
Excel 2007
3. Hacer clic en el menú , botón y diseñar el
formulario mostrado a continuación.
Control ListBox1
4. Hacer clic en el menú , botón y escriba el siguiente
programa
General Declaraciones
Dim numpart As Integer
Dim numcrit As Integer
Dim PtjeMax As Integer
Dim PtjeMin As Integer
Dim Puntaje(12, 4) As Integer
Dim total(12) As Integer
Private Sub CommandButton1_Click()
ListBox1.Clear
Randomize
ScaleMode = 3
'Generación de los puntajes aleatorios
For candidata = 1 To 12
244 SENATI-Computación e Informática
ersonalizadas
For criterio = 1 To 4
Puntaje(candidata, criterio) = Int((10 - 5 + 1) * Rnd + 5)
Next
Next
'Determinar el puntaje total por participante
For contpart = 1 To 12
total(contpart) = 0
For contcrit = 1 To 4
total(contpart) = total(contpart) + Puntaje(contpart, contcrit)
Next
Next
For contpart = 1 To 12
registro = registro + "Concursante No. " & Right(("0" + Trim(Str(contpart))), 2) + " "
For contcrit = 1 To 4
registro = registro + Right(("0" + Trim(Str(Puntaje(contpart, contcrit)))), 2) + " "
Next
ListBox1.AddItem registro + Str(total(contpart))
registro = ""
Next
'Determinar mayor puntaje total
mayor = 0
numero = 0
For contpart = 1 To 12
If total(contpart) > mayor Then
mayor = total(contpart)
numero = contpart
End If
Next
TextBox1.Text = numero
TextBox2.Text = mayor
End Sub
SENATI-Computación e Informática 245
Microsoft Office
Excel 2007
Cuestionarios
1. Qué es una función.
2. Qué es un Control Active X.
__
3. Considera útil la creación de funciones personalizadas.
4. Crear una función que permite determinar si un año es bisiesto.
5. Crear una función que permita determinar la edad de una persona.
246 SENATI-Computación e Informática