Guia de excel

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.


AREAS DE LA PANTALLA

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)

Barra de menú

Barra de formulas

Celda

Etiquetas de hojas

Identificador de columnas

Identificador de filas


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


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; valor_si_falso)

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

Y(valor_lógico1;valor_lógico2;…) Devuelve VERDADERO si todos los argumentos son VERDADEROS


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 OPERADORES DE COMPARACION

+ Suma - Resta * Multiplicación / División ^ Potencia

= Igual > Mayor < Menor >= Mayor igual <= Menor igual < > Diferente


EJERCICIO 1: Realizar una suma sencilla

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


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 EXPLICACION =A$2 * 50 Se ha fijado la Fila 2 =Abs($B4) Se ha fijado la Columna B =Izquierda($E$5) Se ha fijado la Columna E y la Fila 5


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:


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


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(……..)


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.


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


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.


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


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


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:


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:


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 +


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


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:


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.


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:


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.


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.


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.

inicioinicio

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

Leer num

(-1) ^ num = -1(-1) ^ num = -1 imp = imp + numimp = imp + num

par = par + numpar = par + num

i = 15i = 15

fin

“Pares”; par“Impares”; imp

“Pares”; par“Impares”; imp

si

no

si

noi = i +1 i = i +1


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.


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#


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

ESTRUCTURAS BASICAS DE PROGRAMACION EN VISUAL BASIC

SENTENCIAS DE CONTROL EJEMPLO

IF <condición> THEN Bloque de instrucciones END IF

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

IF <condición> 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 <Expresión-Prueba> 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


BUCLES EJEMPLO FOR <Contador=inicio> TO <final> [STEP <incremento> ] Bloque de instrucciones NEXT

Dim subtotal As Integer subtotal = 0 For i = 1 To 10 subtotal = subtotal + i * 5 Next MsgBox subtotal Rem Muestra el valor de 275

DO WHILE <Condición> Bloque de instrucciones LOOP

Dim vueltas As Integer Dim contador As Integer contador = 20 vueltas = 0 Do While (contador >= 0) vueltas = vueltas + 1 contador = contador - 2 Loop MsgBox vueltas Rem Se muestra el valor de 11

DO UNTIL <Condición> Bloque de instrucciones LOOP

Dim vueltas As Integer Dim contador As Integer contador = 20 vueltas = 0 Do Until (contador = 0) vueltas = vueltas + 1 contador = contador - 2 Loop MsgBox vueltas Rem Se muestra el valor de 10


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


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


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 baja una celda en forma relativa

Next Se cierra el ciclo

End Sub Se finaliza la macro

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.


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 ActiveCell <> Empty

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

Do While ActiveCell <>Empty

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

If ActiveCell >= 0 Then Si la celda activa es mayor igual a cero, entonces

Selection.Interior.Color = 12611584

Resaltar el interior de la celda con el color azul

Else De lo contrario

Selection.Interior.Color = 255

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 abajoy 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


EJERCICIO 7: Según la edad, colocarle a cada persona el estatus de niño, adolescente, joven, adulto.

• 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 Apuntes de Microsoft Excel - 39 - La solución al ejercicio es la siguiente


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 VALOR Boolean * 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


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 Apuntes de Microsoft Excel - 43 - • 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:


FUNCIONES DE VISUAL BASIC

Visual Basic también trae incorporadas funciones que son de gran utilidad. FUNCION SIRVE PARA Sqr(numero) 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 Apuntes de Microsoft Excel - 45 - • Abrir el Editor de Visual Basic • Escribir el código según la siguiente imagen

• Ejecute la macro. El resultado debe ser el siguiente


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


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


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


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.

Technology

Guia de excel