• Author / Uploaded
• Dubraska Aguiar Puerta

Citation preview

Universidad de Oriente Núcleo de Anzoátegui Escuela de Ing. y Cs. Aplicadas Departamento de Ing. Industrial

APUNTES DE MICROSOFT EXCEL 2.007 By Longo: Msc. Joseph Stalin Loján Paladines

Hoja de cálculo & Gráficos Diagrama de flujo Macros Introducción a Visual basic para aplicaciones

Barcelona - Venezuela, Abril 2.011

Serie de consulta LONGO

Apuntes de Microsoft Excel - 1 -

INTRODUCIÓN Excel es una aplicación del tipo hoja de cálculo, desarrollada por Microsoft en la cual se combinan las capacidades de una hoja de cálculo normal, listas, base de datos, gráficos, lenguaje propio de programación y generación de macros; todo dentro de la misma aplicación. Con Excel se puede trabajar simultáneamente con un número ilimitado de hojas de cálculo siempre y cuando los recursos de su computador lo soporten, permitiendo guardar, manipular, calcular, y analizar datos numéricos, textos y formulas, además se puede resumir toda esa información y presentarla mediante gráficos de distinto tipo, que pueden ser creados sobre la misma hoja de cálculo. El objetivo de este material consiste en ofrecer al participante una serie de herramientas básicas y avanzadas, así como nuevos conocimientos para aprovechar al máximo las ventajas de Excel y mejorar el rendimiento de sus actividades. No obstante es importante resaltar que algunos de estos ejercicios han sido extraídos del manual “Recordando al Microsoft Excel” del prof. Pedro Salazar.

OBJETOS DE EXCEL • • •

Libro de hojas de cálculo: Es el documento principal de Excel, el cual está formado por un conjunto variable de hojas de cálculo. Hoja de cálculo: Matrices de celdas, es decir arreglos bidimensionales de filas y columnas. Celdas: Es la intersección entre una fila y una columna de la hoja de cálculo, representa la unidad de almacenamiento de datos de Excel, ya que guarda un solo datos a la vez.

TIPOS DE DATOS EN EXCEL • • •

• •

Rótulos o texto: Es una cadena de caracteres alfanuméricos, justificados por defecto a la izquierda Números: Constituido solo por números incluyendo el separador decimal (punto o coma). Justificación por defecto a la derecha. Formulas: Es una secuencia de números, caracteres, operadores matemáticos, funciones y referencias de celdas que devuelven un nuevo valor. Se debe iniciar con el símbolo igual (con el fin de diferenciarlos con los datos tipo rótulo). Por defecto, se actualizan automáticamente y muestran el resultado; más no su contenido. Fecha: Equivalentes numéricamente a los días transcurridos desde el primero de enero del año 1.900 hasta la fecha indicada. Hora: Equivale numéricamente al decimal correspondiente a la fracción del día transcurrido hasta la hora indicada.

Serie de consulta LONGO

Apuntes de Microsoft Excel - 2 -

AREAS DE LA PANTALLA Barra de menú

Barra de formulas Identificador de columnas Identificador de filas Celda Etiquetas de hojas

RANGO DE CELDAS Es un conjunto de celdas organizadas en forma rectangular. Un rango puede estar conformado por más de un área rectangular. Su sintaxis es (CeldaInicial:CeldaFinal), donde: CeldaInicial: Es la celda ubicada más arriba y más a la izquierda en el rango CeldaFinal: Es la celda ubicada más abajo y más a la derecha en el rango. Para separar varias áreas rectangulares se utiliza punto y coma (;). Ejemplos: (B2:D5)

(A2:C4;E3:E6)

Serie de consulta LONGO

Apuntes de Microsoft Excel - 3 -

FUNCIONES INCORPORADAS

MATEMATICAS Abs(número)

Devuelve el valor absoluto de un número

Aleatorio( )

Devuelve un numero aleatorio mayor o igual que cero y menor que 1

Cos(número)

Devuelve el coseno de un ángulo. Número es el ángulo en radianes.

Entero(número)

Redondea un número hasta el entero inferior más próximo

Pi()

Devuelve el valor de Pi (3.1419..……) con precisión de 15 dígitos

Potencia(número;potencia)

Devuelve el resultado de elevar el número a una potencia)

Producto(número1;número2;….)

Multiplica todos los números especificados como argumentos

Raiz(número)

Devuelve la raíz cuadrada

Redondear(número;num_decimales)

Redondea un número al número de decimales especificado

Seno(número)

Devuelve el seno de un ángulo determinado. Número: Representa el ángulo en radianes del que se desea obtener el seno. Grados*Pi()/180 = Radianes

Suma(número1;número2)

Suma todos los números en un rango de celdas

Sumar.Si(rango;criterio;rango_suma) Suma las celdas que cumplen determinado criterio o condición ESTADISTICAS Contar(Ref1;Ref2)

Cuenta el número de celdas que contienen números y los números que hay en la lista de argumentos

Contar.Blanco(rango)

Cuenta el número de celdas en blanco dentro de un rango especificado.

Contar.Si(rango;criterio)

Cuenta las celdas en el rango que coinciden con la condición dada

Contara(valor1;valor2;……)

Cuenta el número de celdas no vacías

Desvest(número1;númeo2;………)

Calcula la desviación estándar de una muestra. Omite los valores lógicos y el texto.

Max(número1;númeo2;………)

Devuelve el valor máximo de una lista de valores. Omite los valores lógicos y de texto

Mediana(número1;númeo2;………)

Devuelve la mediana o el número central de un conjunto de números

Min(número1;número2;………)

Devuelve el valor mínimo de una lista de valore. Omite los valores lógicos y de texto

Promedio(número1;número2;…..)

Devuelve el promedio (media aritmética) de los argumentos

Serie de consulta LONGO

Apuntes de Microsoft Excel - 4 -

TEXTO Concatenar(texto1;texto2…..)

Une varios elementos de texto es uno solo

Derecha(texto;num_caracteres)

Devuelve el número especificado de caracteres (del lado derecho) de una cadena de caracteres

Igual(texto1;texto2)

Comprueba si dos cadenas de texto son exactamente iguales y devuelve VERDADERO o FALSO. Se diferencia entre mayúsculas y minúsculas

Encontrar(texto_buscado;dentro_del_texto; num_inicial)

Devuelve la posición inicial de una cadena de texto dentro de otra cadena de texto. BUSCAR diferencia entre mayúsculas y minúsculas. El texto_buscado es el texto que se desea encontrar. No se admite caracteres comodín

Espacios(texto)

Quita todos los espacios del texto, excepto los espacios individuales entre palabras

Extrae(texto;posición_inicial;num_caracteres) Devuelve los caracteres del centro de una cadena de texto, dada una posición y longitudes iniciales Izquierda(texto;núm_caracteres)

Devuelve el número especificado de caracteres (del lado derecho) de una cadena de caracteres

Largo(texto)

Devuelve el número de caracteres de una cadena de texto

Mayusc(texto)

Convierte una cadena de texto en letras mayúsculas

Minusc(texto)

Convierte todas las letras de una cadena de texto en minúsculas

Nompropio(texto)

Convierte una cadena de texto en mayúsculas o minúsculas, según corresponda; la primera letra de cada palabra en mayúscula y las demás en minuscula

Repetir(texto;núm_de_veces)

Repite el texto un número determinado de veces.

LOGICAS No(valor_lógico)

Cambia FALSO por VERDADERO y VERDADERO por FALSO

O(valor_lógico1;valor_lógico2;….)

Comprueba si alguno de los argumentos es VERDADERO. Devuelve FALSO si todos los argumentos son FALSOS

Si(prueba_lógica;valor_si_verdadero; Comprueba si se cumple una condición, y devuelve un valor si se evalúa como VERDADERO y otro valor si se evalúa como valor_si_falso) FALSO. Y(valor_lógico1;valor_lógico2;…)

Devuelve VERDADERO si todos los argumentos son VERDADEROS

Serie de consulta LONGO

Apuntes de Microsoft Excel - 5 -

FECHA Ahora()

Devuelve la fecha y hora actuales con formato de fecha y hora

Año(núm_serie)

Devuelve el año, un entero en el rango 1900 - 9999

Dia(núm_de_serie)

Devuelve el día del mes (un numero de 1 a 31)

Diasem(núm_de_serie;tipo)

Devuelve un número de 1 a 7 que identifica el día de la semana. Num_de_serie es un numero que representa una fecha. Tipo es un numero que representa el primer día de la semana

Hoy()

Devuelve la fecha actual con formato de fecha

Mes(núm_de_serie)

Devuelve el mes, un numero entero de 1(enero) a 12 (diciembre)

OTRAS FUNCIONES Pago(tasa;nper;va;vf;tipo)

Calcula el pago de un préstamo basado en pagos y tasa de interés constante

Texto(valor;formato)

Convierte un valor en texto, con un formato de número especifico. VALOR: Es un valor numérico, una formula que evalúa un valor numérico o una referencia a una celda que contiene un valor numérico. FORMATO Es un nombre de categoría: “General”, “Numero”, “Moneda”, “Fecha”, etc.

OPERADORES PARA HOJA DE CALCULO Y MACROS OPERADORES MATEMATICOS + Suma - Resta * Multiplicación / División ^ Potencia

OPERADORES DE COMPARACION = Igual > Mayor < Menor >= Mayor igual Diferente

Serie de consulta LONGO

Apuntes de Microsoft Excel - 6 -

EJERCICIO 1: Realizar una suma sencilla

En lugar de escribir la formula =SUMA(     ), también puedes hacer clic en el icono   para que aparezca el asistente y trabajar de forma  insertar función   mas fácil. 

Serie de consulta LONGO

Apuntes de Microsoft Excel - 7 -

REFERENCIAS RELATIVAS A medida que una formula se copia, ésta apunta a nuevas referencias.

REFERENCIAS ABSOLUTAS (se debe usar el símbolo $) A medida que una formula se copia, ésta apunta siempre a la misma referencia, siempre y cuando esté configurada para ello. EJEMPLO =A$2 * 50 =Abs($B4) =Izquierda($E$5)

EXPLICACION Se ha fijado la Fila 2 Se ha fijado la Columna B Se ha fijado la Columna E y la Fila 5

Serie de consulta LONGO

Apuntes de Microsoft Excel - 8 -

EJERCICIO 2: Calcular el pvp a partir del costo y un % de utilidad fijo

EJERCICIO 3: Poner separador de miles y dos decimales al PVP

Sombrear el rango B4:E4 Clic en Inicio (barra de menú) Clic en Formato de celdas: número

Aparecerá la pantalla de la derecha Clic en la categoría Número Clic en usar separador de miles En posiciones decimales, poner 2 Clic en Aceptar Clic en cualquier celda Hacer lo mismo con el COSTO El resultado debe ser como la figura de la página siguiente:

Serie de consulta LONGO

Apuntes de Microsoft Excel - 9 -

EJERCICIO 4: Determinar si un alumno está aprobado o reprobado, utilizando la función =SI(……..)

Serie de consulta LONGO

Apuntes de Microsoft Excel - 10 -

EJERCICIO 5: Validar que los montos sean positivos y luego sumar, utilizando la función O(…….) dentro de la función =SI(…..)

EJERCICIO 6: Calcular el promedio de notas de los varones

Para resolver este ejercicio también se puede utilizar la función  =PROMEDIO.SI(……..) 

Serie de consulta LONGO

Apuntes de Microsoft Excel - 11 -

EJERCICIO 7: Dadas una serie de cedulas, determinar en que mesa van a votar. Las cedulas que terminen en 0,1,2,3,4,5, van a votar en la mesa 1. Las demás en la mesa 2.

EJERCICIO 8: Colocar en la celda D1, el rotulo SL. Luego crear una fórmula que coloque la segunda letra del nombre de cada persona.

Serie de consulta LONGO

Apuntes de Microsoft Excel - 12 -

MANEJO DE FECHAS

EJERCICIO 9: Calcular el número de días que falta para tu próximo cumpleaños.

EJERCICIO 10: Aplicar un formato de número a las celdas B1 y B2. Para ello debe repetir el ejercicio 3, pero en posiciones decimales poner 0

EJERCICIO 11: Calcular el número de meses entre dos fechas de diferentes años. Escribir las fechas según la siguiente planilla. Luego con una(s) formula(s) obtener la respuesta

Serie de consulta LONGO

Apuntes de Microsoft Excel - 13 -

EJERCICIO 12: Calcular el tiempo que duró un vehículo en un estacionamiento.

EJERCICIO 13: Un coche entró en un estacionamiento el día 27/10/2009 a las 11 de la mañana y salió el día 28/10/2009 a las 3 de la tarde. ¿Cuántas horas duró ese coche en el estacionamiento?. Desarrolle un(a) formula(s) para que haga el calculo independientemente de la cantidad de días que el coche dure en el estacionamiento.

Serie de consulta LONGO

Apuntes de Microsoft Excel - 14 -

DISEÑO DE GRAFICOS

EJERCICIO 14: Realizar la grafica del seno. Tal como se muestra en la figura siguiente.

Para lograrlo debe realizar los siguientes pasos • • • •

En la columna A, genere una serie de datos desde -5 hasta 5 con un paso de 0.5 tal como se muestra en la figura de la izquierda En la columna B, escriba la formula del Seno Luego sombree desde A1 hasta B22 Haga clic en Insertar/Dispersión. Aparecerá la siguiente pantalla

• •

Seleccione el tercer tipo de grafico “Dispersión con líneas suavizadas” Luego puede mover o escalar el gráfico según su preferencia

Serie de consulta LONGO

Apuntes de Microsoft Excel - 15 -

EJERCICIO 15: Realizar un gráfico de columnas de 3 gastos variables de una familia en los meses de Enero a Mayo

Para lograrlo debe realizar los siguientes pasos •

Escriba los datos según la siguiente planilla

• •

Luego sombree desde A1 hasta F4 Haga clic en Insertar/Columnas. Aparecerá pantalla que está en el lado izquierdo Seleccione el grafico “Columna Agrupada 3D” Luego puede mover o escalar el gráfico según su preferencia

• •

Serie de consulta LONGO

Apuntes de Microsoft Excel - 16 -

EJERCICIO 16: Tomando como base el ejercicio anterior, realice un gráfico de torta donde se aprecie el gasto mensual de la familia Peluche.

Para realizar gráficos es necesario sombrear rangos de datos, cuando los rangos  no son contiguos se debe utilizar la tecla control mientras se sombrea. Otra  alternativa consiste en ocultar las filas o columnas que no sean necesarias. 

EJERCICIOS PROPUESTOS EJERCICIO 17: Realizar una tabla de multiplicar. Para ello debe diseñar una sola fórmula y luego copiarla, tal como se muestra en la figura siguiente

El resultado debe ser como el mostrado en la página siguiente:

Serie de consulta LONGO

Apuntes de Microsoft Excel - 17 -

EJERCICIO 18: Tomando como base el ejercicio 17, coloque en la celda B12 el numero 500. Ahora modifique la formula de tal manera que a cada resultado se le sume el valor de la celda B12. Tal como se muestra en la figura siguiente:

Serie de consulta LONGO

Apuntes de Microsoft Excel - 18 -

EJERCICIO 19: Realice la tabla del 11 usando funciones de texto y una suma.

Explicación: La tabla del 11 se resuelve sumando los dos dígitos  y ese valor se  debe colocar en el medio de dichos dígitos. Ejemplo: 36 * 11 = 396. Debe utilizar las  funciones de texto: Extrae o en su lugar Izquierda y Derecha, la suma debe  realizarse con el operador +  

Serie de consulta LONGO

Apuntes de Microsoft Excel - 19 -

EJERCICIO 20: Escriba las fechas según la siguiente planilla y luego indique si ese año es o no un año bisiesto. Una de las dos funciones que debe utilizar es la función Año.

El resultado debe ser como el mostrado en la siguiente figura

Serie de consulta LONGO

Apuntes de Microsoft Excel - 20 -

EJERCICIO 21: Una empresa ha realizado las siguientes ventas y tiene una serie de gastos variables. Primero usted debe calcular en cuanto se han incrementado las ventas de cada mes con respecto a Enero. Luego usted debe calcular los gastos en Bs. (de: luz, Comisiones, Papelería…) de cada mes según los porcentajes de la columna G. En total debe diseñar dos formulas

Finalmente calcule el total de gastos de cada mes. El resultado se muestra a continuación:

Serie de consulta LONGO

Apuntes de Microsoft Excel - 21 -

EJERCICIO 22: Una empresa de ventas tiene una lista de cuentas por cobrar (según se muestra en la siguiente planilla). Usted debe crear en la cela E2 una fórmula para determinar el status (vencida o vigente) tomando en cuenta la fecha de corte (11/02/2011). Además en la celda B15 debe contar el número de vencidas y en la celda C15 el monto en Bs de las vencidas, el proceso similar debe hacerlo para las vigentes.

Serie de consulta LONGO

Apuntes de Microsoft Excel - 22 -

EJERCICIO 23: Dada una lista de cedulas con su respectivo país de origen y estado civil, diseñe el Rif para cada uno. El último digito del rif depende del estado civil (para ello utilice la función si dentro de la función si) NOTA: Cuando la cedula está por debajo de 10 millones se debe poner un 0 a la izquierda.

El rif debe quedar de la siguiente manera:

Serie de consulta LONGO

Apuntes de Microsoft Excel - 23 -

EJERCICIO 24: Se tiene una lista de jóvenes con su respectiva edad y el estado civil, tal como se muestra en la figura siguiente. Diseñe una formula en la columna Status para determinar si el joven está Apto o No apto para prestar el servicio militar.

Para que un joven pueda prestar el servicio militar debe ser soltero y al mismo  tiempo mayor de edad.  

Serie de consulta LONGO

Apuntes de Microsoft Excel - 24 -

DIAGRAMAS DE FLUJO Los diagramas de flujo representan la forma más tradicional para especificar los detalles algorítmicos de un proceso. Se utilizan principalmente en programación, economía y procesos industriales; estos diagramas utilizan una serie de símbolos con significados especiales. Los diagramas de flujo son modelos tecnológicos utilizados para comprender los rudimentos de la programación Se basan en la utilización de diversos símbolos para representar operaciones específicas. Se les llama diagramas de flujo porque los símbolos utilizados se conectan por medio de flechas para indicar la secuencia de operación. La simbología utilizada para la elaboración de diagramas de flujo es única y debe ajustarse a un patrón definido previamente. SIMBOLOS PRINCIPALES Indica el sentido y trayectoria del proceso de información o tarea. Representa un evento, proceso u operación. Es el símbolo más comúnmente utilizado.

Se utiliza para representar una condición. Normalmente el flujo de información entra por arriba y sale por un lado si la condición se cumple o sale por el lado opuesto si la condición no se cumple. Lo anterior hace que a partir de éste el proceso tenga dos caminos posibles.

Representa un punto de conexión entre procesos. Se utiliza cuando es necesario dividir un diagrama de flujo en varias partes, por ejemplo por razones de espacio o simplicidad. La mayoría de las veces se utilizan números dentro de los círculos para poder distinguirlos. Permite indicar el inicio o el final del diagrama. Debe existir un solo inicio y un solo final.

Simbolo de pantalla utilizado para mostrar mensajes o resultados de las operaciones. Simbolo utilizado para la captura de datos por el teclado.

Serie de consulta LONGO

Apuntes de Microsoft Excel - 25 -

EJERCICIO UNICO: Leer 15 números por teclado, sumar en la variable Par los números pares y en la variable Imp los números impares.

inicio

par = 0, imp = 0 i = 1, num = 0

Leer num

(-1) ^ num = -1

no par = par + num

no i = i +1

i = 15

si “Pares”; par “Impares”; imp

fin

si

imp = imp + num

Serie de consulta LONGO

Apuntes de Microsoft Excel - 26 -

MACROS Excel es un programa que tiene un gran potencial, pero la mayoría de la gente lo maneja sin la ventaja de las macros. Excel cuenta con un lenguaje muy poderoso llamado Visual Basic, y permite hacer o resolver los problemas de una manera mas fácil, solo se debe aprender a programarlo. Visual Basic es una herramienta sencilla de aprender. Sin embargo para la programación en Visual Basic es necesario tener cierta creatividad, cada persona puede crear estructuras diferentes pero que trabajen igual. ¿Qué es una macro ? Una Macro son una serie de pasos que se almacenan y se pueden activar con alguna combinación de teclas o con un botón. Por ejemplo, alomejor usted todos los días necesita hacer una planilla con las carreras que ofrece la universidad de oriente, por lo tanto para no repetir todos los pasos involucrados, estos se pueden almacenar en una macro y posteriormente ejecutarla las veces que el usuario lo desee. ¿Cómo ejecutar una macro? Para poder ejecutar una macro, se debe hacer clic en el menú Vista, tal como se muestra en la siguiente figura

Luego se debe hacer clic en el botón Macros, tal como se puede apreciar en la siguiente figura

Serie de consulta LONGO

Apuntes de Microsoft Excel - 27 -

En ese instante aparecerá la siguiente ventana, donde se deberá hacer clic en el nombre de la macro que desee correr y luego se debe hacer clic en el botón ejecutar

Es importante recalcar que esta ventana está vacía ya que aún no se ha diseñado  ninguna macro. 

¿Cómo se diseña una macro? Para poder diseñar (escribir) una macro se debe ejecutar los siguientes pasos •

•

•

Hacer clic con el botón derecho en la pestaña Hoja1 o en la hoja donde se desea diseñar la macro. Tal como se aprecia en la imagen de la derecha. Al desplegarse el menú contextual se debe hacer clic en la opción Ver código. Tal como se ve en la figura de abajo.

Luego aparecerá una ventana en blanco, a la cual llamaremos Editor de Visual Basic. Es ahí donde se diseñará la macro. Tal como se observa en la figura de la derecha.

Serie de consulta LONGO

Apuntes de Microsoft Excel - 28 -

EJERCICIO 1: Diseñar una macro donde se puedan apreciar todas las carreras que ofrece la Universidad de Oriente.

•

Activar el Editor de Visual Basic

•

• •

Escribir el código Sub Universidad() Range("a1").Select ActiveCell = "CARRERAS" Range("a2").Select ActiveCell = "Ing. Industrial" Range("a3").Select ActiveCell = "Ing. Computación" Range("a4").Select ActiveCell = "Ing. Civil" Range("a5").Select ActiveCell = "Ing. Química" End Sub Minimice la venta del Editor de Visual Basic. Esto hace que regresemos a la hoja de Excel Haga clic en el Menú Vista y luego clic en el botón Macro, deberá aparecer la siguiente ventana

•

Haga clic en Hoja1.Universidad (color azul) y luego clic en Ejecutar

Serie de consulta LONGO

Apuntes de Microsoft Excel - 29 -

El resultado deberá ser el siguiente

NOTA IMPORTANTE: Todo el código de Visual Basic se debe escribir única y  exclusivamente en minúscula, luego al presionar enter la primera letra de cada  palabra se deberá cambiar automáticamente a mayúscula.  Si esto ocurre  entonces el código está bien escrito, caso contrario deberá corregirlo ud mismo.  QUE HACER EN CASO DE UN ERROR ? Es muy probable que al diseñar una macro cometamos errores de sintaxis, es lógico que esto suceda ya que el código debe ser escrito en ingles. Al momento de correr una macro con errores, puede aparecer la siguiente ventana

Para solucionarlo se debe cumplir los siguientes pasos: •

Hacer clic en Aceptar, y el puntero (en la mayoría de la ocasiones) sombrea la palabra mal escrita (no se emocione, que eso es solo algunas veces). Luego se debe corregir el error, en este caso observe que el programador ha escrito rango en lugar de range (es por ello que toda la palabra permanece en minúscula).

Serie de consulta LONGO

Apuntes de Microsoft Excel - 30 -

•

Una vez corregido el error, se debe OBLIGATORIAMENTE hacer clic en el botón Restablecer, tal como lo muestra la siguiente figura

•

Por último, minimice la ventana del Editor de Visual Basic y vuelva a ejecutar la Macro.

EJERCICIO 2: Modifique la macro anterior para que tenga la siguiente apariencia.

Cuando se asigna a ActiveCell un texto, éste debe ir entre “Comillas dobles”; sin  embargo cuando asignamos un número, éste debe ir sin comillas. Para asignar una  fecha a una celda, se debe hacer de la siguiente manera ActiveCell = #24/05/2010# 

Serie de consulta LONGO

Apuntes de Microsoft Excel - 31 -

EJERCICIO 3: Modifique la macro anterior para que tenga la siguiente apariencia.

ESTRUCTURAS BASICAS DE PROGRAMACION EN VISUAL BASIC SENTENCIAS DE CONTROL IF THEN Bloque de instrucciones END IF

EJEMPLO IF (nota >=5) then Aprobado=Aprobado + 1 END IF

IF THEN Bloque de instrucciones + ELSE Bloque de instrucciones END IF

IF (nota >=5) then Msgbox “Usted esta aprobado” ELSE Msgbox “Usted está reprobado” END IF

SELECT CASE CASE prueba1 Bloque1 CASE prueba2 Bloque2 CASE ELSE Bloque3 END SELECT

Dim R As Integer Range("B1").Select R = ActiveCell.FormulaR1C1 Select Case R Case 1 MsgBox "el valor es uno" Case 2 To 5 MsgBox "el valor está entre 2 y 5" Case Else MsgBox "el valor es desconocido" End Select

Serie de consulta LONGO

Apuntes de Microsoft Excel - 32 -

BUCLES EJEMPLO Dim subtotal As Integer FOR TO [STEP subtotal = 0 ] For i = 1 To 10 Bloque de instrucciones subtotal = subtotal + i * 5 NEXT Next MsgBox subtotal Rem Muestra el valor de 275 Dim vueltas As Integer DO WHILE Dim contador As Integer Bloque de instrucciones contador = 20 LOOP vueltas = 0 Do While (contador >= 0) vueltas = vueltas + 1 contador = contador - 2 Loop MsgBox vueltas Rem Se muestra el valor de 11 Dim vueltas As Integer DO UNTIL Dim contador As Integer Bloque de instrucciones contador = 20 LOOP vueltas = 0 Do Until (contador = 0) vueltas = vueltas + 1 contador = contador - 2 Loop MsgBox vueltas Rem Se muestra el valor de 10

Serie de consulta LONGO

Apuntes de Microsoft Excel - 33 -

CODIGOS COMUNES SENTENCIA

OBJETIVO

Range("A1").Select

Trasladarse a una celda en forma absoluta

ActiveCell="Texto"

Escribir un texto en una celda

ActiveCell=25

Escribir un valor numérico en una celda

ActiveCell = #27/10/2010#

Escribir una fecha en una celda

Selection.Font.Bold = True

Letra Negrita

Selection.Font.Italic = True

Letra Cursiva

Selection.Font.Underline = xlUnderlineStyleSingle Letra Subrayada Selection.Copy

Copiar

Selection.Cut

Cortar

ActiveSheet.Paste

Pegar

Selection.EntireRow.Insert

Insertar una fila

Selection.EntireRow.Delete

Eliminar una fila

Selection.EntireColumn.Insert

Insertar una columna

Selection.EntireColumn.Delete

Eliminar una columna

ActiveCell.Offset(-1, 3).Select

Desplazarse en nfilas, ncolumnas en forma relativa

Selection.End(xlDown).Select

Desplaza a la ultima celda (hacia abajo)

Selection.End(xlUp).Select

Desplaza a la ultima celda (hacia arriba)

Selection.End(xlToRight).Select

Desplaza a la ultima celda (hacia la derecha)

Selection.End(xlToLeft).Select

Desplaza a la ultima celda (hacia la izquierda)

Selection.Font.Color = -16711681

Poner el color del texto en Amarillo

Selection.Font.Color = -16776961

Poner el color del texto en Rojo

Selection.Font.Color = -4165632

Poner el color del texto en Azul

Selection.Interior.Color = 65535

Colocar el fondo de la celda en color Amarillo

Selection.Interior.Color = 255

Colocar el fondo de la celda en color Rojo

Selection.Interior.Color = 12611584

Colocar el fondo de la celda en color Azul

Serie de consulta LONGO

Apuntes de Microsoft Excel - 34 -

EJERCICIO 4: Poner en color azul las notas de los alumnos aprobados.

•

Escribir en la hoja de calculo los siguientes datos

•

Abrir el Editor de Visual Basic y escribir el siguiente código

•

Minimice el Editor de Visual Basic y ejecute la macro Colorear, el resultado debe ser como la imagen de de la pagina siguiente

Serie de consulta LONGO

Apuntes de Microsoft Excel - 35 -

INSTRUCCIÓN

EXPLICACIÓN

Sub Colorear( )

Se asigna un nombre a la macro

Range("c2").Select

El puntero se ubica en forma absoluta en la celda C2

For i = 1 To 7

Se abre un ciclo (bucle) de 7 iteraciones

If ActiveCell >= 5 Then

Se abre una pregunta. Si la celda activa es mayor igual a cinco, entonces

Selection.Font.color = -4165632

El contenido de la ceda se pone en color Azul

End If

Se cierra la pregunta

ActiveCell.Offset(1, 0).Select

El puntero relativa

Next

Se cierra el ciclo

End Sub

Se finaliza la macro

baja

una

celda

en

forma

EJERCICIO 5 (para el hogar): Diseñe una nueva macro tomando como base los datos del ejercicio anterior , de tal manera que el fondo de las celdas que contengan la letra F (femenino) se ponga en amarillo. Luego en la celda F1 coloque la cantidad de Hombres y en F2 la cantidad de mujeres.

Serie de consulta LONGO

Apuntes de Microsoft Excel - 36 -

EJERCICIO 6: Resaltar el fondo de las celdas en color azul para los valores positivos y de color rojo para los valores negativos. Para ello utilice un doble bucle

•

Escribir los valores según la siguiente planilla

•

Activar el Editor de Visual Basic y escribir el siguiente código

Serie de consulta LONGO

Apuntes de Microsoft Excel - 37 -

El resultado debe ser como la siguiente imagen

INSTRUCCIÓN

EXPLICACIÓN

Range("B2").Select

Seleccionar en forma absoluta la celda B2

Do While Empty

ActiveCell

Hacer un bucle mientras la celda activa sea diferente de vacío

Do While Empty

ActiveCell

Hacer un bucle anidado mientras celda activa sea diferente de vacío

If ActiveCell >= 0 Then Selection.Interior.Color 12611584 Else Selection.Interior.Color 255

la

Si la celda activa es mayor igual a cero, entonces = Resaltar el interior de la celda con el color azul De lo contrario = Resaltar el interior de la celda con el color rojo

End If

Fin de la pregunta

ActiveCell.Offset(1, 0).Select

Selección relativa, 1 fila hacia abajo y 0 columnas a la derecha

Loop

Repetir bucle interno

ActiveCell.Offset(-1, 1).Select

Selección relativa, 1 fila hacia arriba y 1 columna a la derecha

Selection.End(xlUp).Select

Seleccionar la última celda con datos del bloque, en dirección hacia arriba

Loop

Repetir bucle externo

Serie de consulta LONGO

EJERCICIO 7: Según la edad, adolescente, joven, adulto.

Apuntes de Microsoft Excel - 38 -

colocarle a cada persona el estatus de niño,

•

Escribir los datos, según la siguiente planilla

• • •

Abrir el Editor de Visual Basic Asignarle a la macro el nombre de Sub StatusEdad ( ) Diseñe la macro (USANDO SELECT CASE) para que coloque el estatus correspondiente, según las siguiente condiciones: (De 0 a 12 años => Niño) (de 13 a 17 => Adolescente) (de 18 a 25 => Joven) (de 26 a 35 => Adulto) ( En adelante => Mayor). El resultado debe ser igual a la siguiente imagen

•

Serie de consulta LONGO

La solución al ejercicio es la siguiente

Apuntes de Microsoft Excel - 39 -

Serie de consulta LONGO

Apuntes de Microsoft Excel - 40 -

MANEJO DE VARIABLES Las variables son posiciones o lugares en la memoria del computador en donde los programas pueden almacenar información dinámica, es decir, cuyo contenido puede variar durante la ejecución de los mismos. En visual basic es recomendable declarar las variables antes de usarlas, ya que, esto nos permite controlar eficientemente los tipos de datos y su manejo. La forma de declarar las variables es la siguiente: Dim nombre_variable As tipo TIPO DE VARIABLE Boolean *

VALOR Solo admite 2 valores TRUE o FALSE

Byte *

Admite valores entre 0 y 255

Integer *

Admite valores entre -32.768 y 32.767

Long

Admite valores entre -2.147.483.648 y 2.147.483.647

Single

Admite valores decimales con precisión simple (4 bytes)

Double *

Admite valores decimales con precisión doble (8 bytes)

Currency

Válido para valores de tipo moneda

String *

Utilizado para declarar variables de tipo cadena de caracteres

Date *

Válido para datos de tipo fecha (se puede hacer operaciones)

Variant

Tipo genérico

(*) Son los tipos mas utilizados

EJERCICIO 8: Repetir el ejercicio numero 7, pero en lugar de usar SELECT CASE, utilice la sentencia IF

Si lo desea puede descargar la solución del ejercicio 8 desde la pagina del autor  www.josephlojan.com el archivo se llama Titis.xls 

Serie de consulta LONGO

Apuntes de Microsoft Excel - 41 -

EJERCICIO 9: Generar una macro que desarrolle una tabla de conversiones, que tome los “N” primeros valores enteros múltiplos de 5, en grados centígrados (partiendo de 0 ºC), y los traduzca a grados fahrenheit o grados Kelvin, según la preferencia del usuario. ºF = 9/5 ºC + 32

ºK = ºC + 273

•

Llenar en la planilla solamente las celdas A1, A2, B4, C4, según la siguiente figura:

•

Abrir el Editor de Visual Basic y escribir el siguiente código

Serie de consulta LONGO

•

Apuntes de Microsoft Excel - 42 -

Un ejemplo de la ejecución de la macro, es la siguiente:

EJERCICIO 10: Realizar una macro que cuente el número de empleados que se encuentren en un rango de sueldo. Para ello la macro debe pedir el sexo del empleado, además el límite inferior y el límite superior del sueldo. •

Crear una planilla como la siguiente:

Serie de consulta LONGO

• • •

Abrir el Editor de Visual Basic Escriba el código necesario para que se cumpla el objetivo solicitado Al ejecutar la macro se deberá observar las siguientes pantallas:

La solución es la siguiente:

Apuntes de Microsoft Excel - 43 -

Serie de consulta LONGO

Apuntes de Microsoft Excel - 44 -

FUNCIONES DE VISUAL BASIC Visual Basic también trae incorporadas funciones que son de gran utilidad. FUNCION Sqr(numero)

SIRVE PARA Calcular la raíz de un numero

Rnd( )

Generar un numero aleatorio entre 0 y 1

Date

Devolver la fecha actual

Year(Fecha)

Devolver el año de una fecha dada

Month(Fecha)

Devolver el numero de mes de una fecha dada

Int(Numero)

Devolver solamente la parte entera de un numero

Ucase(Texto)

Convertir a mayúscula un texto

EJERCICIO 11: Calcular la hipotenusa de una serie de triángulos rectángulo

•

Crear una planilla como la siguiente:

Serie de consulta LONGO

• •

Abrir el Editor de Visual Basic Escribir el código según la siguiente imagen

•

Ejecute la macro. El resultado debe ser el siguiente

Apuntes de Microsoft Excel - 45 -

Serie de consulta LONGO

EJERCICIO 12: Crear una lista de 10 numero aleatorios

• •

Abrir el Editor de Visual Basic Escribir el código según la siguiente imagen

•

Ejecute la macro. El resultado debe ser similar al siguiente

Apuntes de Microsoft Excel - 46 -

Serie de consulta LONGO

Apuntes de Microsoft Excel - 47 -

EJERCICIOS PROPUESTOS

EJERCICIO 13: Se tiene una lista de vehículos: Taxi y Particular. Se debe colocar el fondo de la celda con color amarillo solamente a los taxis que tengan más de 10 años. •

Crear una planilla como la siguiente

•

Diseñe la macro correspondiente, el resultado debe ser parecido a la siguiente imagen

Trate de hacer por sus propios medios el ejercicio # 13. Si desea ver una solución  propuesta, puede descargarla desde la pagina del autor www.josephlojan.com el  archivo se llama Taxi.xls 

Serie de consulta LONGO

Apuntes de Microsoft Excel - 48 -

EJERCICIO 14: Crear un diccionario Ingles-Español. Dada una palabra en Inglés, la macro me debe indicar su equivalente en español. Además la macro debe colocar un * (asterisco) a la palabra consultada y ponerla en fondo amarillo. •

Crear una planilla como la siguiente

•

Diseñe la macro correspondiente, el resultado debe ser parecido a las siguientes imágenes

Trate de hacer por sus propios medios el ejercicio # 14. Si desea ver una solución  propuesta, puede descargarla desde la pagina del autor www.josephlojan.com el  archivo se llama Diccionario.xls 

Serie de consulta LONGO

Apuntes de Microsoft Excel - 49 -

COMO DESCARGAR ARCHIVOS DESDE LA PAGINA ? •

Ingrese a la dirección www.josephlojan.com aparecerá la siguiente pantalla

• •

En Descarga Interactiva escriba el nombre del archivo que desea descargar, por ejemplo Titis.xls Recuerde siempre respetar las minúsculas y mayúsculas y sin dejar espacios en blanco Luego haga clic en el botón

•

Aparecerá la siguiente ventana

Serie de consulta LONGO

Apuntes de Microsoft Excel - 50 -

•

Se debe hacer clic en Guardar y aparecerá la siguiente pantalla

• • •

Seleccione la carpeta donde desea guardar el archivo (Generalmente Mis Documentos) y luego haga clic en Guardar Luego diríjase a Mis Documentos y haga doble clic en el archivo correspondiente NOTA IMPORTANTE. Al abrir el archivo, la macro no se va a ejecutar. Primero deberá hacer clic en Opciones, tal como lo muestra la siguiente figura

•

Aparecerá la siguiente pantalla

• •

Haga clic en Habilitar este contenido y luego en aceptar Ahora ya puede ejecutar la macro. Sin embargo si el botón de opciones no aparece, entonces cierre el archivo y vuelva a abrirlo.