msexcel2010experto.pdf
TRANSCRIPT
Contenido
ANÁLISIS DE DATOS II .............................................................................................................. 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 ...................................................................................................................... 85
ANÁLISIS DE DATOS III ........................................................................................................... 86
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 .................................................................................................................... 236
AP. Formación Online. 1
Capítulo
Análisis de datos II
En este capítulo trataremos:
Formato como tabla
Herramientas de datos
Consolidación y referencias 3D
Microsoft Office Excel 2010
2 AP. Formación Online.
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
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
Registros
Microsoft Office Excel 2010
AP. Formación Online. 3
Paso a Paso: Agregar el comando formulario a la barra de herramientas de acceso rápido
1. Hacer clic en la pestaña Archivo
2. Hacer clic en opciones, se presenta el cuadro de diálogo “Opciones Excel”.
3. Se presenta el cuadro de diálogo “Personalizar la barra de herramientas de
acceso rápido”.
Hacer clic en la opción Personalizar
Microsoft Office Excel 2010
4 AP. Formación Online.
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.
Seleccionar “Todos los
comandos”
Seleccionar
“Formulario …”
Microsoft Office Excel 2010
AP. Formación Online. 5
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
Microsoft Office Excel 2010
6 AP. Formación Online.
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”
Microsoft Office Excel 2010
AP. Formación Online. 7
6. Primero localizaremos el registro, para ello hacer clic en el botón
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.
Ingrese “SoftPlus”, nombre del cliente a
localizar
Hacer clic en el botón “Buscar siguiente”
Microsoft Office Excel 2010
8 AP. Formación Online.
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
5. Se mostrarán sus registros ordenados por el campo cliente
Hacer clic en el botón
“Ordenar de A a Z”
Microsoft Office Excel 2010
AP. Formación Online. 9
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
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”
Hacer clic en el botón
“Ordenar”
Hacer clic en el botón
“Opciones”
Microsoft Office Excel 2010
10 AP. Formación Online.
8. Hacer clic en el botón para continuar. Se mostrará el cuadro de
diálogo ordenar
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
Seleccionar columna
“NombreProducto”
Microsoft Office Excel 2010
AP. Formación Online. 11
5. Se presenta el siguiente cuadro de diálogo
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”
Hacer clic en el botón
“Ordenar”
Seleccionar como criterio de ordenación “De mayor a menor”
Hacer clic para seleccionar la
columna “Cantidad”
Microsoft Office Excel 2010
12 AP. Formación Online.
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
5. Se presenta el siguiente cuadro de diálogo
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.
Hacer clic en el botón
“Ordenar”
Seleccionar la columna
“FechaPedido”
Seleccione su criterio
de ordenación
Microsoft Office Excel 2010
AP. Formación Online. 13
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
5. Se presenta el siguiente cuadro de diálogo
6. Hacer clic en el botón para terminar. Se muestran los registros
ordenados por cliente, fechapedido, nombreproducto en orden descendente.
Hacer clic en el botón
“Ordenar”
Seleccionar columna “FechaPedido” como segundo
criterio de ordenación
Seleccionar columna “NombreProducto” como tercer
criterio de ordenación
Seleccionar columna “Cliente” como primer criterio de ordenación
Microsoft Office Excel 2010
14 AP. Formación Online.
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.
Microsoft Office Excel 2010
AP. Formación Online. 15
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.
Desactive las casillas del 2do. Al 6to. grado
Microsoft Office Excel 2010
16 AP. Formación Online.
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.
Microsoft Office Excel 2010
AP. Formación Online. 17
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
Pensiones de alumnos con valor entre 200 y 350.
Microsoft Office Excel 2010
18 AP. Formación Online.
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
Hacer clic y dejar sólo activado la casilla de verificación 1
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
Microsoft Office Excel 2010
AP. Formación Online. 19
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.
6. Luego de elegir un estilo se presenta un cuadro de diálogo
Seleccionar uno de los estilos
mostrados
Microsoft Office Excel 2010
20 AP. Formación Online.
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”
Microsoft Office Excel 2010
AP. Formación Online. 21
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.
6. Se muestra el cuadro de diálogo Nuevo estilo rápido de tabla
7. Se presenta el cuadro de diálogo formato de celda
Hacer clic en el botón “Nuevo
estilo de tabla …”
Escriba la palabra “Pedidos” como nombre de estilo
Hacer clic en el
botón “Formato”
Aplique los formatos a cada uno de los elementos de la
tabla
Microsoft Office Excel 2010
22 AP. Formación Online.
8. Para terminar hacer clic en el botón
Elegir donde aplicará los
bordes
Elegir el estilo de línea
Elegir color de fondo
Hacer clic en la ficha “Bordes”
Elegir efecto de relleno
Hacer clic para cambiar a la ficha
“Relleno”
Elegir color
Microsoft Office Excel 2010
AP. Formación Online. 23
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
6. Del grupo de opciones presentadas elegir “Modificar”
7. Realizar los cambios necesarios
Hacer clic derecho
Hacer clic sobre la opción “Modificar”
Microsoft Office Excel 2010
24 AP. Formación Online.
8. Del cuadro de diálogo “Formato de celdas”, elegir la ficha “Relleno”
Elegir “Primera
franja de fila”
Hacer clic sobre la opción “Formato”
Seleccionar
color de fondo
Microsoft Office Excel 2010
AP. Formación Online. 25
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
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
Seleccione una de estas opciones, luego hacer clic en
el botón para modificar cada uno de los
elementos de la tabla
Microsoft Office Excel 2010
26 AP. Formación Online.
2. Seleccione el bloque de celdas A1:A4. Hacer clic en la ficha y
visualizar el grupo herramienta de datos.
3. Se presenta el asistente para convertir texto en columnas
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”
Hacer clic sobre la herramienta “Texto en
columnas”
Debido a que el texto se separa
con comas, elegir la opción
“Delimitados”
Microsoft Office Excel 2010
AP. Formación Online. 27
6. Hacer clic en el botón
7. Hacer clic en el botón para terminar, su hoja queda como se
muestra a continuación
Elegir coma como separador
Elegir “Texto”
Celda a partir de donde se colocaran los datos
Microsoft Office Excel 2010
28 AP. Formación Online.
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.
3. Se presenta el asistente para convertir texto en columnas
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.
Hacer clic sobre la herramienta “Texto
en columnas”
Debido a que el texto se separa con
comas, elegir la opción
“Delimitados”
Microsoft Office Excel 2010
AP. Formación Online. 29
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”
6. Hacer clic en el botón
Elegir “Espacio” como separador
Elegir “Texto”
Celda a partir de donde se colocaran
los datos
Microsoft Office Excel 2010
30 AP. Formación Online.
7. Hacer clic en el botón para terminar, su hoja queda como se
muestra a continuación
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
El texto de dividió en seis columnas
Hacer clic en el botón Ordenar
Microsoft Office Excel 2010
AP. Formación Online. 31
4. Se presenta el cuadro de diálogo ordenar
5. Hacer clic en el botón “Aceptar” para terminar con la ordenación
6. Hacer clic en la ficha
7. Se presenta el cuadro de diálogo “Quitar duplicados”
8. Hacer clic en el botón para terminar. Excel envía un mensaje
que indica que se eliminaron 4 valores duplicados
Hacer clic para activar la casilla indicando que si
contamos con encabezados
Hacer clic para seleccionar Nombre como columna a
ordenar
Hacer clic en el botón
“Quitar duplicados
Hacer clic para activar la casilla “Mis datos tienen encabezados”
Hacer clic para indicar que se eliminan duplicados de la columna “Nombre”
Microsoft Office Excel 2010
32 AP. Formación Online.
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”
Microsoft Office Excel 2010
AP. Formación Online. 33
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.
Microsoft Office Excel 2010
34 AP. Formación Online.
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.
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”
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”
Microsoft Office Excel 2010
AP. Formación Online. 35
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 ,
4. Cambiar a la pestaña Mensaje de entrada, para definir un mensaje que se
mostrará cuando intente ingresar un dato.
Elegir “Entre”
Ingresar “1/1/1965” como mínimo y “1/1/1980” como máximo.
Elegir “Fecha”
Hacer clic en la ficha configuración
Hacer clic en la ficha
“Mensaje de entrada”
Microsoft Office Excel 2010
36 AP. Formación Online.
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
Escribir mensaje
Escribir título “1/1/1965 a 1/1/1980”
Hacer clic en la lista “Validación de datos”
Hacer clic en el botón
“Validación de datos …”
Microsoft Office Excel 2010
AP. Formación Online. 37
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
Paso a Paso: Borrar círculos de validación
1. Hacer clic en la ficha
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.
Hacer clic en la lista “Validación de datos”
Hacer clic en el botón “Rodear con un círculo
datos no válidos”
Hacer clic en el botón “Borrar círculos de
validación”
Hacer clic en la lista “Validación de datos”
Microsoft Office Excel 2010
38 AP. Formación Online.
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
Microsoft Office Excel 2010
AP. Formación Online. 39
4. De las opciones presentadas elegir “Administrador de escenarios”
5. Se presenta el cuadro de diálogo “Administrador de escenarios”
6. Hacer clic en el botón
7. Del cuadro de diálogo valores del escenario especifique según la gráfica
mostrada
Hacer clic en el botón análisis Y si.
Hacer clic en el botón “Administrador de escenarios”
Escriba “Mejor opción”
Seleccione el bloque de celdas B15:B18
Hacer clic en el botón “Aceptar”
Microsoft Office Excel 2010
40 AP. Formación Online.
8. Hacer clic en el botón para adicionar los escenarios restantes.
9. Se presenta el cuadro de diálogo “Modificar escenario”
Del cuadro de diálogo valores del escenario especifique según la gráfica
mostrada
10. Hacer clic en el botón para adicionar los escenarios restantes.
Valores del escenario “Mejor opción”
Hacer clic en el botón “Aceptar”
Escriba B15:B18 como celdas cambiantes
Escriba “caso optimista” como nombre de escenario
Valores del escenario “Caso optimista”
Microsoft Office Excel 2010
AP. Formación Online. 41
11. Del cuadro de diálogo valores del escenario especifique según la gráfica
mostrada
12. Hacer clic en el botón para terminar
13. Se presenta el cuadro de diálogo administrador de escenarios.
14. Guardar el archivo
Hacer clic en el botón “Aceptar”
Escriba B15:B18 como celdas cambiantes
Escriba “caso Pesimista” como nombre de escenario
Valores del escenario “Caso Pesimista”
Microsoft Office Excel 2010
42 AP. Formación Online.
Paso a Paso: Mostrar escenarios
1. Abrir el archivo “1 Escenarios”
2. Hacer clic en la ficha
3. De las opciones presentadas elegir “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
Paso a Paso: Modificar un Escenario
1. Abrir el archivo “1 Escenarios”
2. Hacer clic en la ficha
Hacer clic en el botón análisis Y si.
Hacer clic en el botón “Administrador de escenarios”
Seleccione escenario a mostrar, luego hacer clic en el botón
Microsoft Office Excel 2010
AP. Formación Online. 43
3. De las opciones presentadas elegir “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”
9. Hacer clic en el botón para terminar
Hacer clic en el botón análisis Y si.
Hacer clic en el botón “Administrador de escenarios”
Escriba los nuevos valores
de su escenario
Microsoft Office Excel 2010
44 AP. Formación Online.
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
3. De las opciones presentadas elegir “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
Hacer clic en el botón análisis Y si.
Hacer clic en el botón “Buscar objetivo”
Fórmula que calcula el pago
mensual Valor que queremos pagar mensualmente
Valor a localizar
Microsoft Office Excel 2010
AP. Formación Online. 45
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
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
Escribir los datos
según se muestra
Microsoft Office Excel 2010
46 AP. Formación Online.
2. Diseñar un cuadro que permita conocer cuánto pagaremos si la tasa de interés
varía entre 26% y 35%.
3. Seleccionar el bloque de celdas A8:B18 (Tabla de interés)
Editar la tabla de tasa de
interés
Escriba esta fórmula
=PAGO(B4/12;B5*12;B3)
Seleccionar
celdas A8:B18
Microsoft Office Excel 2010
AP. Formación Online. 47
4. Hacer clic en la ficha
5. De las opciones presentadas elegir “Buscar objetivo …”
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
Hacer clic en el botón análisis Y si.
Hacer clic en el botón “Tabla de datos”
Montos que se deben pagar según la tasa de interés asociada
Microsoft Office Excel 2010
48 AP. Formación Online.
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
2. Seleccionar el bloque de celdas A8:G18 (Matriz: interés - monto)
3. Hacer clic en la ficha
4. De las opciones presentadas elegir “Buscar objetivo …”
Hacer clic en el botón análisis Y si.
Hacer clic en el botón “Tabla de datos”
Agregar el siguiente
cuadro a su hoja
Microsoft Office Excel 2010
AP. Formación Online. 49
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.
7. Hacer clic en el botón para terminar, se debe mostrar el siguiente
resultado
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.
Monto prestado
Tasa de interés
Montos prestados
Tasas de
interés Monto a pagar mensual
Microsoft Office Excel 2010
50 AP. Formación Online.
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
Microsoft Office Excel 2010
AP. Formación Online. 51
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”
Microsoft Office Excel 2010
52 AP. Formación Online.
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
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.
Elegir la función Suma
Adicionar estas tres referencias y lic en
el botón “Agregar”.
Activar las casillas de verificación
Microsoft Office Excel 2010
AP. Formación Online. 53
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
Microsoft Office Excel 2010
54 AP. Formación Online.
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.
Microsoft Office Excel 2010
AP. Formación Online. 55
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.
Microsoft Office Excel 2010
56 AP. Formación Online.
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:
Microsoft Office Excel 2010
AP. Formación Online. 57
=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.
Microsoft Office Excel 2010
58 AP. Formación Online.
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.
______________________________________________________________
______________________________________________________________
AP. Formación Online. 59
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
Microsoft Office Excel 2010
60 AP. Formación Online.
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%.
Microsoft Office Excel 2010
AP. Formación Online. 61
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.
Microsoft Office Excel 2010
62 AP. Formación Online.
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.
Microsoft Office Excel 2010
AP. Formación Online. 63
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
Microsoft Office Excel 2010
64 AP. Formación Online.
Paso a Paso: Conversión de tasa nominal a tasa efectiva
1. Editar y desarrollar la siguiente hoja.
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.
= (1 + ($B$4/B9)) ^ B9 -1
= (1 + ($B$4/B8)) ^ B8 -1
Microsoft Office Excel 2010
AP. Formación Online. 65
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
Microsoft Office Excel 2010
66 AP. Formación Online.
3. Diseñar el siguiente cuadro
4. Se debe obtener como resultado el siguiente cuadro
Se considera como negativo por ser un
desembolso
Microsoft Office Excel 2010
AP. Formación Online. 67
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%.
Vf = 10,000
Tasa 12%
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
Microsoft Office Excel 2010
68 AP. Formación Online.
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
Microsoft Office Excel 2010
AP. Formación Online. 69
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=?
Microsoft Office Excel 2010
70 AP. Formación Online.
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.
Microsoft Office Excel 2010
AP. Formación Online. 71
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%)
Microsoft Office Excel 2010
72 AP. Formación Online.
- 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
Microsoft Office Excel 2010
AP. Formación Online. 73
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
Nper=?
Tasa=5% tasa=5% tasa5%
pago= -2500 pago= -2500 pago= -2500
3. Diseñar la siguiente hoja
Microsoft Office Excel 2010
74 AP. Formación Online.
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
Nper=?
Tasa=5% tasa=5% tasa5%
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
Microsoft Office Excel 2010
AP. Formación Online. 75
Va=9500
Nper=?
Tasa=5% tasa=5% tasa5%
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).
Microsoft Office Excel 2010
76 AP. Formación Online.
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)
Microsoft Office Excel 2010
AP. Formación Online. 77
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.
Microsoft Office Excel 2010
78 AP. Formación Online.
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)
Proveedor Usuario
del activo del activo
Como se gestiona
- 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
Microsoft Office Excel 2010
AP. Formación Online. 79
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
Microsoft Office Excel 2010
80 AP. Formación Online.
3. Alternativa con préstamo
4. Desarrollando el flujo de fondos
Interés = Tasa*Saldo actual
=F10*$F$4
Amort = Cuota - interés
=I10-G10
=$F$6
Saldo = saldo ant. – Amort.
=F10-H10
Microsoft Office Excel 2010
AP. Formación Online. 81
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%.
Microsoft Office Excel 2010
82 AP. Formación Online.
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.
Microsoft Office Excel 2010
AP. Formación Online. 83
Paso a Paso: TIR no periódica
1. Un proyecto cuya inversión inicial es de 120,000 inicia el 26 de mayo del 2009,
puede generar flujos de caja que se proyectan en la tabla siguiente.
Se quiere calcular la TIR del proyecto para tomar una decisión de aceptación o
rechazo considerando que el coste de oportunidad del capital anual es de 15%.
Diseñar la siguiente hoja de cálculo
Calcular la tasa interna de devoluciones múltiples
Devuelve la tasa interna de retorno modificada para una serie de flujos de caja
periódicos. TIRM toma en cuenta el costo de la inversión y el interés obtenido por
la reinversión del dinero.
Sintaxis:
TIRM(valores;tasa_financiamiento;tasa_reinversión)
Valores
Es una serie de pagos (valores negativos) e ingresos (valores positivos)
que se realizan en períodos regulares.
El argumento valores debe contener por lo menos un valor positivo y uno
negativo para poder calcular la tasa interna de retorno modificada. De lo
contrario, TIRM devuelve el valor de error #¡DIV/0!
Tasa_financiamiento
Es la tasa de interés que se paga por el dinero utilizado en los flujos de
caja.
Microsoft Office Excel 2010
84 AP. Formación Online.
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
Microsoft Office Excel 2010
AP. Formación Online. 85
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.
______________________________________________________________
______________________________________________________________
AP. Formación Online. 86
Capítulo
Análisis de datos III
En este capítulo trataremos:
Herramientas de análisis estadístico
Uso de cuadros de histogramas
Uso de Solver
Microsoft Office Excel 2010
AP. Formación Online. 87
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”
Microsoft Office Excel 2010
88 AP. Formación Online.
3. Se debe mostrar como resultado el siguiente cuadro
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.
Ventas del primer
semestre
Ventas proyectadas con tendencia lineal
automática, para el último semestre
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”
Microsoft Office Excel 2010
AP. Formación Online. 89
2. Se presentará como resultado el siguiente cuadro
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
Ventas del primer
semestre
Ventas proyectadas con tendencia
geométrica, para el último semestre
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…
Microsoft Office Excel 2010
90 AP. Formación Online.
2. Se presenta el siguiente cuadro de diálogo.
3. Hacer clic en el botón para terminar
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:
Seleccione tipo de tendencia:
Lineal o geométrica
Elegir columna,
para que la serie se extienda
hacia abajo
Active la casilla Tendencia
Se genera la tendencia
reemplazando a los tres primeros valores e la serie
original
Microsoft Office Excel 2010
AP. Formación Online. 91
• Á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
c. Seleccionar las
celdas A2:B7
b. Hacer clic en la ficha Insertar
a. Hacer clic en botón
d. Elegir este tipo de gráfico
Microsoft Office Excel 2010
92 AP. Formación Online.
3. Se muestra el siguiente gráfico
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
0
1
2
3
4
2003 2004 2005 2006 2007
Tasa de inflación Perú
e. Elegir Lineal
f. Activar la casilla “Presentar ecuación en el gráfico”
Microsoft Office Excel 2010
AP. Formación Online. 93
5. Se muestra el siguiente gráfico
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).
y = -0,27x + 3,09 0
2
4
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.)
Microsoft Office Excel 2010
94 AP. Formación Online.
Paso a Paso: Uso de la función Pronóstico
1. Diseñar el siguiente cuadro
Microsoft Office Excel 2010
AP. Formación Online. 95
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
Microsoft Office Excel 2010
96 AP. Formación Online.
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
Microsoft Office Excel 2010
AP. Formación Online. 97
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”.
Microsoft Office Excel 2010
98 AP. Formación Online.
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.
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.
Pico
Pico
Microsoft Office Excel 2010
AP. Formación Online. 99
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".
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.
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.
Varias distribuciones en campana con sus centros distribuidos uniformemente a lo largo del recorrido de los datos.
Valores altos y bajos se alternan de forma regular
Esta forma sugiere la existencia de dos
procesos distintos
Microsoft Office Excel 2010
100 AP. Formación Online.
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.
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
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.
Un pico situado en un extremo
de una distribución regular
Pico descentrado Descendencia suave de la cola.
Descendencia
brusca de la cola
Microsoft Office Excel 2010
AP. Formación Online. 101
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 la pestaña Archivo ,
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”
Microsoft Office Excel 2010
102 AP. Formación Online.
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.
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
5. Haga clic en el botón para hallar una solución
=B5*$F$2*B4 =SUMA(B5:E5)
Escriba F5. Que define la celda que mostrará el valor en
pedidos que se desea alcanzar
Valor de la celda objetivo, Monto que deseamos obtener
como pedidos
Grupo de celdas
cambiantes
Microsoft Office Excel 2010
AP. Formación Online. 103
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
Hacer clic para guardar este
escenario
Microsoft Office Excel 2010
104 AP. Formación Online.
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
Microsoft Office Excel 2010
AP. Formación Online. 105
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
Tiempo que tarda el proceso de
solución.
Tiempo que tarda el proceso de
solución.
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 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
Porcentaje donde la celda objetivo da una solución satisface las restricciones externas. Una tolerancia mayor tiende a acelerar el proceso de solució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.
Conjugado Necesita menos memoria que
el método Newton, pero normalmente
necesita más iteraciones para alcanzar un
nivel de exactitud concreto
Newton Utiliza un método
quasi-Newton que
normalmente necesita más
memoria pero menos
iteraciones que el método
de gradiente conjugada.
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.
Microsoft Office Excel 2010
106 AP. Formación Online.
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.
Cambie las opciones de Estimación, Derivadas y Buscar para optimizar su
modelo
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.
Grupo de celdas
cambiantes
Microsoft Office Excel 2010
AP. Formación Online. 107
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.
8. Haga clic en el botón para hallar una solución
Agregar la siguiente lista
de restricciones
Como no se puede comprar fracciones de artefactos
modificaremos el rango de celdas cambiantes a: B4:B6
Microsoft Office Excel 2010
108 AP. Formación Online.
Cuestionarios
9. Para terminar haga clic en el botón Se presenta la siguiente
solución esta vez incluye las restricciones.
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.
______________________________________________________________
______________________________________________________________
AP. Formación Online. 109
Capítulo
Formularios
En este capítulo trataremos:
Activación de la ficha PROGRAMADOR
Diseño de formularios
Desarrollo de ejemplos tipo con formularios
Microsoft Office Excel 2010
110 AP. Formación Online.
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 la pestaña Archivo
2. Hacer clic en el botón , se
presenta el cuadro de diálogo “Opciones de Excel”.
3. Hacer clic en la opción personalizar cinta de
opciones
4. Se presenta el cuadro de diálogo personalizar cinta de
opciones
5. Hacer clic en el botón para terminar.
Activar la casilla
Programador
Microsoft Office Excel 2010
AP. Formación Online. 111
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.
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
Encabezado de columna
Encabezado de fila Líneas de
división
Etiquetas de hojas
Microsoft Office Excel 2010
112 AP. Formación Online.
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.
Hacer clic para ocultar: Líneas de cuadrícula, Títulos
Microsoft Office Excel 2010
AP. Formación Online. 113
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
Microsoft Office Excel 2010
114 AP. Formación Online.
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
Microsoft Office Excel 2010
AP. Formación Online. 115
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”
Hacer clic para activar el control
Escribir $F$2 para vincular este control con la celda en mención
Microsoft Office Excel 2010
116 AP. Formación Online.
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.
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.
Estos datos se utilizarán en el control numérico que se vinculará con el principal, celda B3
Microsoft Office Excel 2010
AP. Formación Online. 117
12. Finalmente su hoja de cálculo queda como se muestra a continuación.
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
Estos datos se utilizarán en el control numérico que se vinculará con la tasa de interés, celda C4
Utilice estos controles para definir el principal
y la tasa de interés.
=c4/100
Microsoft Office Excel 2010
118 AP. Formación Online.
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.
Microsoft Office Excel 2010
AP. Formación Online. 119
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”
Microsoft Office Excel 2010
120 AP. Formación Online.
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
Microsoft Office Excel 2010
AP. Formación Online. 121
15. Agregar este control sobre la celda A3
16. Hacer clic derecho sobre este control y elegir la opció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
20. Hacer clic derecho sobre este control y elegir la opción
Hacer clic para asignar como nombre: “Tarjeta
Bonus”
Hacer clic para activar el control
Escribir $A$3 para vincular este control con la celda en mención
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.
Microsoft Office Excel 2010
122 AP. Formación Online.
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
25. Hacer clic en el botón para terminar
Hacer clic para activar el control
Escribir $A$7 para vincular este control con la celda en mención
Presione la tecla F3 para presentar la
lista de nombres de rango, elegir “Productos”.
Elegir la opción “Lista”
Microsoft Office Excel 2010
AP. Formación Online. 123
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(A13);"";BUSCARV(A13;ProducPrecio;2;0))
=SI(ESBLANCO(B13);"";C13*B13)
=SI(A3=VERDADERO;D19*10%;0)
=D19*ELEGIR(A7;10%;5%;0)
Microsoft Office Excel 2010
124 AP. Formación Online.
Paso a Paso: Ejemplos tipo con formularios
1. Realizar el siguiente diseño y asigne a la hoja el nombre ENCOMIENDA.
Hoja ENCOMIENDA del libro Proformas.
2. Luego cree el siguiente diseño y asígnele el nombre a la hoja de COSTOS.
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.
ENCOMIENDA
COSTO
Microsoft Office Excel 2010
AP. Formación Online. 125
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:
Microsoft Office Excel 2010
126 AP. Formación Online.
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).
Microsoft Office Excel 2010
AP. Formación Online. 127
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.
Microsoft Office Excel 2010
128 AP. Formación Online.
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.
Microsoft Office Excel 2010
AP. Formación Online. 129
Cuestionarios
1. Cuáles son los pasos para activar la Ficha Programador.
______________________________________________________________
______________________________________________________________
2. Cuál es la utilidad del uso de formularios en Excel.
______________________________________________________________
______________________________________________________________
3. Cuál es la diferencia entre un botón de opción y una casilla de verificación.
______________________________________________________________
______________________________________________________________
4. Cuál es la diferencia entre un cuadro de lista y un cuadro combinado.
______________________________________________________________
______________________________________________________________
5. Cual es la utilidad de cuadro de grupos al utilizar los botones de opción.
______________________________________________________________
______________________________________________________________
AP. Formación Online. 130
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
Microsoft Office Excel 2010
AP. Formación Online: 131
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.
Microsoft Office Excel 2010
132 AP. Formación Online.
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.
Microsoft Office Excel 2010
AP. Formación Online: 133
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.
Microsoft Office Excel 2010
134 AP. Formación Online.
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
Microsoft Office Excel 2010
AP. Formación Online: 135
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
Microsoft Office Excel 2010
136 AP. Formación Online.
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
Se presenta el siguiente cuadro de diálogo
Hacer clic en el botón
Microsoft Office Excel 2010
AP. Formación Online: 137
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.
Elegir el certificado que desea utilizar
Microsoft Office Excel 2010
138 AP. Formación Online.
Grabar una nueva Macro
Hacer clic en la ficha , botón
Se presenta el siguiente cuadro de diálogo
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
Escribir el nombre de la
Macro
Especificar donde se grabará la
Macro
Escriba una descripción de lo
que realiza su Macro
Microsoft Office Excel 2010
AP. Formación Online: 139
4. Se presenta el siguiente cuadro de diálogo
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
9. Hacer clic en la ficha , botón
Escriba como nombre de
la Macro “OrdenarLista”
Hacer clic en el botón
Elegir la opción “CATEGORÍA”
Microsoft Office Excel 2010
140 AP. Formación Online.
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.
Hacer clic en el botón se muestra el código
Ahora puede modificar el código según sus nuevos requerimientos.
Elegir la Macro
“OrdenarLista”
Microsoft Office Excel 2010
AP. Formación Online: 141
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.
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.
Elegir la Macro que
desea eliminar
Microsoft Office Excel 2010
142 AP. Formación Online.
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:
Microsoft Office Excel 2010
AP. Formación Online: 143
- 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
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.
Escriba como nombre de la Macro
“Ordenar_Por_Apellidos
Hacer clic en el botón
Microsoft Office Excel 2010
144 AP. Formación Online.
8. Hacer clic en la ficha , elegir
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
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.
Elegir la opción “APELLIDOS Y NOMBRES
Elegir “Ordenar_Por:Apellidos” y hacer clic en el botón
Microsoft Office Excel 2010
AP. Formación Online: 145
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
Microsoft Office Excel 2010
146 AP. Formación Online.
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
Escriba como nombre de la Macro
“Filtra_Afil_AFP”
Hacer clic en el botón
Hacer clic sobre el filtro
“A_AFP”.
Desactive la casilla “N”
Microsoft Office Excel 2010
AP. Formación Online: 147
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
17. Repetir este procedimiento para signar Macro a los otros dos botones de opción
Elegir la opción
“Filtra_Afil_AFP”
Hacer clic en el botón
para
terminar
Microsoft Office Excel 2010
148 AP. Formación Online.
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
1. Hacer clic en la pestaña Archivo,
2. Hacer clic en el botón
3. Se presenta el siguiente cuadro “Plantillas Disponibles”, seleccionar Plantillas de
Ejemplo.
4. Hacer clic en el botón
Seleccionar una de las
plantillas mostradas
Microsoft Office Excel 2010
AP. Formación Online: 149
Paso a Paso: Uso de plantillas de MS Office Online
1. Hacer clic en la pestaña Archivo
2. Hacer clic en el botón
3. Se presenta el siguiente cuadro de diálogo
4. En Plantillas de Office.com, seleccionar una categoría, luego seleccionar una
plantilla ejemplo: Planeamiento Mensual
5. Hacer clic en el botón Descargar
6. A continuación se muestra la plantilla en un nuevo libro.
Seleccionar una de las plantillas mostradas
Microsoft Office Excel 2010
150 AP. Formación Online.
Crear Plantillas personalizadas
1. Para crear una plantilla diseñe su hoja de cálculo y al guardarla elegir el tipo
Microsoft Office Excel 2010
AP. Formación Online: 151
2. De esta forma puede utilizar esta plantilla para generar nuevos archivos
Utilizar Plantillas personalizadas
1. Para utilizar una plantilla hacer clic en la pestaña Archivo,
2. Se presenta el siguiente cuadro de diálogo
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.
Elegir
plantillas
Elegir la
plantilla
que
quiera
utilizar
Microsoft Office Excel 2010
152 AP. Formación Online.
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
Microsoft Office Excel 2010
AP. Formación Online: 153
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á
Microsoft Office Excel 2010
154 AP. Formación Online.
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
Microsoft Office Excel 2010
AP. Formación Online: 155
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:
Microsoft Office Excel 2010
156 AP. Formación Online.
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
Microsoft Office Excel 2010
AP. Formación Online: 157
DISEÑAR LA HOJA DE TABLA AFP
DISEÑAR LA HOJA DE PLANILLA
DISEÑAR LA HOJA BOLETA DE PAGO
Microsoft Office Excel 2010
158 AP. Formación Online.
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.
______________________________________________________________
______________________________________________________________
AP. Formación Online. 159
Capítulo
Programación con VBA
En este capítulo trataremos:
Programación con Visual Basic
Microsoft Office Excel 2010
160 AP. Formación Online.
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.
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.
Código
Objetos Procedimientos
Microsoft Office Excel 2010
AP. Formación Online. 161
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:
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.)
Análisis del problema
Definición del
problema
Definir datos de entrada
Definir datos de salida
Microsoft Office Excel 2010
162 AP. Formación Online.
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:
Estructura Secuencial.
Este tipo de algoritmos se caracteriza por que entre sus
instrucciones no existen estructuras condicionales ni
Declaración de variables
Captura de datos
Proceso de datos
Salida de información
Microsoft Office Excel 2010
AP. Formación Online. 163
repetitivas, se desarrollan línea a línea hasta culminar con su ejecución,
gráficamente se observaría de la siguiente manera:
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
Condición Instrucciones
V
Microsoft Office Excel 2010
164 AP. Formación Online.
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.
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)
Condición
Instrucciones_B Instrucciones_A
V F
Microsoft Office Excel 2010
AP. Formación Online. 165
N2 = val(text2.text)
If n1>n2 then
Textbox1.text = “El primer número es el mayor”
Else
Textbox1.text = “El segundo número es el mayor”
End If
End Sub
6. Diagrama de Flujo
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.
El primer número es
mayor
El primer número no es mayor
INICIO
N1, N2
Declaración de variables N1, N2: entero
FIN
A>B
F V
Microsoft Office Excel 2010
166 AP. Formación Online.
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
Microsoft Office Excel 2010
AP. Formación Online. 167
Inicio de Bucle
Instrucción 1
Instrucción N
Fin de Bucle
Select case e
case 0 to 30
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.
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
V, T
E = V * T
Declaración de variables V, T, E: entero
E
0 y 30 60 y 200 31 y 60
Debe
usar 1
galón
Debe
usar 2
galones
Debe
usar 3 o
más
galones
E
FIN
Microsoft Office Excel 2010
168 AP. Formación Online.
Contador. Los procesos repetitivos por lo general lo utilizan, ya que necesitan
contar los sucesos o acciones internas del bucle. Una
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.
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.
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
Inicio de Bucle
C = C + 1
Fin de Bucle
Inicio de Bucle
AC = AC + N
Fin de Bucle
N
Microsoft Office Excel 2010
AP. Formación Online. 169
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
N
Declaración de variables I, N, AC: entero
FIN
AC = 0
AC = AC + I
AC
Para I=1 Hasta N
Microsoft Office Excel 2010
170 AP. Formación Online.
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
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.
Formulario
Cuadro de herramientas
Ventana de proyecto
Barra de herramienta Barra de menú
Microsoft Office Excel 2010
AP. Formación Online. 171
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
Microsoft Office Excel 2010
172 AP. Formación Online.
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
Microsoft Office Excel 2010
AP. Formación Online. 173
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.
Microsoft Office Excel 2010
174 AP. Formación Online.
La Ventana de Código contiene:
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
Lista de objetos Lista de eventos
La barra de división
Para aplicar sangría a una
sección de sentencias de un
código use la tecla Tab.
Microsoft Office Excel 2010
AP. Formación Online. 175
MsgBox “La clave ingresada es correcta”
Unload Me
Else
MsgBox “La clave ingresada no es válida”
txtContraseña.SelStart=0
txtContraseña.SelLength= Len(Trim(txtContraseña))
txtContraseña.SetFocus
End If
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
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.
Un comentario se
inicia con el carácter
apóstrofe ( „ )
Microsoft Office Excel 2010
176 AP. Formación Online.
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.
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.
Microsoft Office Excel 2010
AP. Formación Online. 177
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).
:
No es recomendable declarar variables con el mismo nombre.
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
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
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ó.
Microsoft Office Excel 2010
178 AP. Formación Online.
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
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.
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,
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
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.
La sentencia Global sólo puede usarse
en el apartado de declaraciones de un
Módulo.
Si se declara de esta forma en la
sección de declaraciones de un
Formulario, esa variable puede usarse
en toda el programa.
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).
La variable NO puede utilizarse fuera
del Formulario o Módulo donde se
declaró.
Microsoft Office Excel 2010
AP. Formación Online. 179
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.
Microsoft Office Excel 2010
180 AP. Formación Online.
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.
Microsoft Office Excel 2010
AP. Formación Online. 181
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.
Microsoft Office Excel 2010
182 AP. Formación Online.
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.
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
Este código establece la propiedad Text del cuadro de texto txtData
txtData.Text = "Hola mundo"
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”
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
Se establece la propiedad Text del cuadro de texto
Se establece la propiedad Caption de la etiqueta
Establece el valor del texto
La siguiente línea de código establece a negrita la
fuente de un cuadro de texto llamado txtData.
Microsoft Office Excel 2010
AP. Formación Online. 183
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
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
alumnos de un centro escolar. Existirán varias
variables para poder introducir los datos de los
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
Variable
Valor de la variable
Asigna a la variable sNombre el valor del cuadro de texto
txtName
Microsoft Office Excel 2010
184 AP. Formación Online.
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.
Microsoft Office Excel 2010
AP. Formación Online. 185
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
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.
nombres1 puede tener cualquier número de caracteres.
nombres2 puede tener un máximo de 15 caracteres.
Microsoft Office Excel 2010
186 AP. Formación Online.
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.
Microsoft Office Excel 2010
AP. Formación Online. 187
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
Microsoft Office Excel 2010
188 AP. Formación Online.
<= 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 cualquierFecha < Now Then
CualquierFecha = Now
End If
If then en una sola línea, sólo puede ejecutar una línea de código
De este otro modo se pueden ejecutar varias líneas de código
Microsoft Office Excel 2010
AP. Formación Online. 189
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
[bloque de sentencias 2]] ...
[Else
[bloque de sentencias n]]
End If
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
Se evalúa esta condición si es verdadera se ejecuta el bloque de sentencias 1
Si es falsa la condición1, evalúa la condición2, si es verdadera ejecuta el bloque de sentencias 2
Si no se cumple ninguna condición se ejecuta el bloque de sentencias n
Microsoft Office Excel 2010
190 AP. Formación Online.
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
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:
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.
Microsoft Office Excel 2010
AP. Formación Online. 191
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.
Microsoft Office Excel 2010
192 AP. Formación Online.
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
Microsoft Office Excel 2010
AP. Formación Online. 193
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
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
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
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.
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:
Microsoft Office Excel 2010
194 AP. Formación Online.
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]
Sentencias
Next [contador]
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:
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.
Los argumentos contador,
iniciar, finalizar e incremento
son todos numéricos.
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.
Microsoft Office Excel 2010
AP. Formación Online. 195
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
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]
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.
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.
Microsoft Office Excel 2010
196 AP. Formación Online.
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
Microsoft Office Excel 2010
AP. Formación Online. 197
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.
Microsoft Office Excel 2010
198 AP. Formación Online.
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
Microsoft Office Excel 2010
AP. Formación Online. 199
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
Microsoft Office Excel 2010
200 AP. Formación Online.
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
Microsoft Office Excel 2010
AP. Formación Online. 201
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.
Microsoft Office Excel 2010
202 AP. Formación Online.
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
Microsoft Office Excel 2010
AP. Formación Online. 203
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.
Microsoft Office Excel 2010
204 AP. Formación Online.
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.
Microsoft Office Excel 2010
AP. Formación Online. 205
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
Microsoft Office Excel 2010
206 AP. Formación Online.
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
Microsoft Office Excel 2010
AP. Formación Online. 207
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.
Microsoft Office Excel 2010
208 AP. Formación Online.
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ú
Microsoft Office Excel 2010
AP. Formación Online. 209
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.
Microsoft Office Excel 2010
210 AP. Formación Online.
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.
Microsoft Office Excel 2010
AP. Formación Online. 211
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.
Microsoft Office Excel 2010
212 AP. Formación Online.
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.
Microsoft Office Excel 2010
AP. Formación Online. 213
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.
Microsoft Office Excel 2010
214 AP. Formación Online.
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
Microsoft Office Excel 2010
AP. Formación Online. 215
Rpta = MsgBox("¿Está seguro de eliminar a este cliente?"
vbQuestion + vbYesNo, "Confirmación")
strCodigo = InputBox("Ingrese el código del cliente a
buscar:","Búsqueda", "CLI0001")
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.
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.
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.
Microsoft Office Excel 2010
216 AP. Formación Online.
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 (*.*),*.* "
Microsoft Office Excel 2010
AP. Formación Online. 217
'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
Microsoft Office Excel 2010
218 AP. Formación Online.
Cuestionarios
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
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.
______________________________________________________________
______________________________________________________________
Microsoft Office Excel 2010
AP. Formación Online. 219
Ejercicio 1: Números primos
Ejercicios utilizando Macro, Formulario y VBA
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
Microsoft Office Excel 2010
220 AP. Formación Online.
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
Microsoft Office Excel 2010
AP. Formación Online. 221
Ejercicio 3: TIR
'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
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
Microsoft Office Excel 2010
222 AP. Formación Online.
Ejercicio 4: =PAGO
Ejercicio 5: Listado de hojas
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
Microsoft Office Excel 2010
AP. Formación Online. 223
Ejercicio 6: Eliminar registros en blanco
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
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
Microsoft Office Excel 2010
224 AP. Formación Online.
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
Microsoft Office Excel 2010
AP. Formación Online. 225
Ejercicio 8: Filtrar la base de datos
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
Sub Filtra() Range("basedatos").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _ Range("H5:K6"), CopyToRange:=Range("H12:K12"), Unique:=False Range("A1").Select End Sub
Microsoft Office Excel 2010
226 AP. Formación Online.
Ejercicio 9: Eliminar valores duplicados de una lista
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
Option Explicit
Function CuentaListaA()
Microsoft Office Excel 2010
AP. Formación Online. 227
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
Microsoft Office Excel 2010
228 AP. Formación Online.
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
Microsoft Office Excel 2010
AP. Formación Online. 229
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=+R[-1]C"
Selection.CurrentRegion.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
End Sub
Sub QuitaRepes()
Dim y As Integer
Dim a As Variant
Dim b As Variant
Dim i As Integer
Dim j As Integer
For i = 0 To 2
Range("B5").Select
ActiveCell.Offset(0, i).Activate
y = 0
Do While Not IsEmpty(ActiveCell.Offset(y, 0))
y = y + 1
Loop
ActiveCell.Offset(y - 1, 0).Activate
For j = y To 1 Step -1
a = ActiveCell.Value
b = ActiveCell.Offset(-1, 0).Value
If a = b Then
Selection.ClearContents
End If
ActiveCell.Offset(-1, 0).Activate
Next j
Next i
Range("A1").Select
End Sub
AP. Formación Online. 230
Capítulo
Funciones personalizadas
En este capítulo trataremos:
Creación de funciones personalizadas con VBA
Uso de controles ActiveX
Microsoft Office Excel 2010
AP. Formación Online. 231
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]
Microsoft Office Excel 2010
232 AP. Formación Online.
[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
Microsoft Office Excel 2010
AP. Formación Online. 233
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)
Microsoft Office Excel 2010
234 AP. Formación Online.
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)
Microsoft Office Excel 2010
AP. Formación Online. 235
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
Microsoft Office Excel 2010
236 AP. Formación Online.
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.
Microsoft Office Excel 2010
AP. Formación Online. 237
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.
Microsoft Office Excel 2010
238 AP. Formación Online.
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.
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
Hacer clic en el menú
Herramientas, Controles
adicionales para agregar el
control FlexGrid a la caja
de controles.
Microsoft Office Excel 2010
AP. Formación Online. 239
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
Microsoft Office Excel 2010
240 AP. Formación Online.
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
Microsoft Office Excel 2010
AP. Formación Online. 241
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.
Microsoft Office Excel 2010
242 AP. Formación Online.
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
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Call IngNumero(KeyAscii)
End Sub
Private Sub CommandButton1_Click()
Dim xcapital As Single
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
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
MSFlexGrid1.TextMatrix(f, 3) = xpa - xamortiza
End If
Invoca al procedimiento IngNumero
Ascii 48 =0
Ascii 57 = 9
Si el carácter ingresado
no es número, se impide
el ingreso del caracter
Declarar las variables a utilizarse
Asigna datos a las variables
Asigna como No.de
filas del control
FlexGrid, el número
de periodos
Crea un ciclo
repetitivo de 1 hasta
el No. de periodos
Microsoft Office Excel 2010
AP. Formación Online. 243
xpa = Val(MSFlexGrid1.TextMatrix(f, 3))
MSFlexGrid1.TextMatrix(f, 4) = xpa * (xinteres / 100)
xi = Val(MSFlexGrid1.TextMatrix(f, 4))
MSFlexGrid1.TextMatrix(f, 5) = xamortiza + xi
Next
End Sub
Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Call IngNumero(KeyAscii)
End Sub
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."
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
Invoca al procedimiento IngNumero
Invoca al procedimiento IngNumero
Asigna la cantidad de filas
para el FlexGrid.
Microsoft Office Excel 2010
244 AP. Formación Online.
3. Hacer clic en el menú , botón y diseñar el
formulario mostrado a continuación.
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
Control ListBox1
Microsoft Office Excel 2010
AP. Formación Online. 245
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
Microsoft Office Excel 2010
246 AP. Formación Online.
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.
______________________________________________________________
______________________________________________________________