GUÍA Y NOVENO TALLER  HERRAMIENTAS AVANZADAS DE HOJA DE CALCULO

Macros  en Excel

Las Macros en Excel son un conjuntos de instrucciones que se ejecutan de manera secuencial por medio de una orden de ejecución, claro esta que una Macro puede invocar a otras, logrando de esta forma obtener operaciones cada vez mas complejas.

En el caso de Excel el lenguaje empleado para el uso de Macros es VBA (Visual Basic para Aplicaciones), Excel cuenta con un "Editor de Visual Basic"  o también conocido como "Editor de VBA" que permite la creación, y/o mantenimiento de las Macros que se tengan disponibles.

¿Para qué sirve una Macro en Excel?

Las Macros en Excel son útiles entre otras cosas por que permiten la automatización de tareas repetitivas, por ejemplo si se da cuenta que todos lo días se encuentra creando la misma tabla dinámica (pero con distintos datos), o colocando el mismo formato a una hoja, entonces es el momento de automatizar dicha labor por medio de Macros.

Crear una Macro:

El punto fuerte a favor del uso de Macros, es que es sencillo de aprender a crearlas y ejecutarlas, crear Macros es simple, y se puede llegar a ahorrar mucho tiempo remplazando aquellas tareas repetitivas por Macros que usted mismo haya creado, uno de los primeros pasos para acercarse al uso de Macros puede ser el comenzar a utilizar el Grabador de macros

Clic en el siguiente link

https://www.youtube.com/watch?v=cMKdXk4be3E

TALLER

Décima     Guía y Taller  de Excel Avanzado

Visual Basic  Excel

El Editor de Visual Basic, o Editor de VBA,  forma parte del programa Microsoft Excel, este Editor nos permite crear, modificar o emplear Macros, las cuales nos permiten ampliar el funcionamiento por defecto de la funcionalidad de Excel.

Este editor es también conocido como VBE por sus siglas en ingles (Visual Basic Editor).

¿Cómo acceder al Editor de Visual Basic?

Para para poder acceder al Editor de Visual Basic se puede emplear la combinación de teclas ALT + F11, si bien esto nos permite ingresar y trabajar con el Editor de VBA, lo mejor es tener disponible una Ficha conocida como Ficha Programador o Ficha Desarrollador, la cual contiene mas opciones.

Primer Ejercicio

Mostrando un mensaje

Sólo tienes que insertar en tu macro el comando MsgBox. En el ejemplo siguiente:

MsgBox "texto del mensaje"

Pasos:

ALT + F11  

- Clic en el menú herramientas

- Clic en macros

- Digitar el nombre de la macro y clic en crear

-Pegamos el código o lo digitamos

 Código: MsgBox "mi nombre es..."

- Cerrar.

-Ejecutar la macro

Segundo Ejercicio

Escribir la fecha y la hora actual en la hoja de cálculo. El siguiente ejemplo inserta estos datos en la celda A1, vea.

Sub escribirDataEHora ()
Range ("A1") = Now
End Sub

Tercer  Ejercicio

Código para pasar numero a letra con decimales

'FUNCION ALETRAS

Function ALETRAS(Numero As Double, Optional DecimalEnLetra As Boolean) As String

'Declaracion de variables

Dim Moneda As String

Dim Monedas As String

Dim Centavo As String

Dim Centavos As String

Dim Con As String

Dim NumCentavos As Double

Dim Letra As String

Const Maximo = 1999999999.99

' Parámetros

Moneda = "Peso"

Monedas = "Pesos"

Decimal1 = "Centavo"

Decimales = "Centavos"

Con = "Con"

'Si el numero esta dentro de 0 y 1999999999.99 (Maximo) entonces

'convertir el numero a letras

If (Numero >= 0) And (Numero <= Maximo) Then

    Letra = LETRAS((Fix(Numero)))

    'Si solo es un numero entonces

    'agregar la moneda en singular

    If (Numero = 1) Then

        Letra = Letra & " " & Moneda

    'De lo contrario agregar la moneda en plural

    Else

        Letra = Letra & " " & Monedas

    End If

    NumCentavos = Round((Numero - Fix(Numero)) * 100)   'Pasar a decimales (al numero le resta el numero entero y lo multiplica por 100)

    'Si los centvos son mayores a cero entonces

    If NumCentavos >= 0 Then

        'Si el parámetro DecimalEnLetra es VERDADERO

        If DecimalEnLetra Then

        'Convertir los centavos en letra

            Letra = Letra & " " & Con & " " & LETRAS(Fix(NumCentavos))

            'Si el centavo es uno agregar leyenda Centavo (Singular)

            If (NumCentavos = 1) Then

                Letra = Letra & " " & Decimal1

            'De lo contrario agregar la leyenda Centavos (Plural)

            Else

                Letra = Letra & " " & Decimales

            End If

         'De lo contrario mostrar los centecimos como número

         Else

            'Si los centavos son menores a 10 entonces

            If NumCentavos < 10 Then

                Letra = Letra & " " & Con & " " & " 0" & NumCentavos & "/100"

            Else

            'De lo contrario

                Letra = Letra & " " & Con & " " & NumCentavos & "/100"

            End If

         End If

    End If

    'Regresar el resultado final de la conversión

    ALETRAS = Letra

Else

    'Si el Numero no está dentro de los límites mostrar un mensaje de error

    ALETRAS = "ERROR: el importe esta fuera del límite."

End If

End Function

'Funcion LETRAS

Function LETRAS(Numero As Long) As String

'Declaracion de las variables

Dim Unidades, Decenas, Centenas

Dim Resultado As String

'Numeros en letras

Unidades = Array("", "Un", "Dos", "Tres", "Cuatro", "Cinco", "Seis", "Siete", "Ocho", "Nueve", "Diez", "Once", "Doce", "Trece", "Catorce", "Quince", "Dieciséis", "Diecisiete", "Dieciocho", "Diecinueve", "Veinte", "Veintiuno", "Veintidos", "Veintitres", "Veinticuatro", "Veinticinco", "Veintiseis", "Veintisiete", "Veintiocho", "Veintinueve")

Decenas = Array("", "Diez", "Veinte", "Treinta", "Cuarenta", "Cincuenta", "Sesenta", "Setenta", "Ochenta", "Noventa", "Cien")

Centenas = Array("", "Ciento", "Doscientos", "Trescientos", "Cuatrocientos", "Quinientos", "Seiscientos", "Setecientos", "Ochocientos", "Novecientos")

Select Case Numero

    Case 0

        Resultado = "Cero"

    Case 1 To 29

        Resultado = Unidades(Numero)

    Case 30 To 100

        Resultado = Decenas(Numero \ 10) + IIf(Numero Mod 10 <> 0, " y " + LETRAS(Numero Mod 10), "")

    Case 101 To 999

        Resultado = Centenas(Numero \ 100) + IIf(Numero Mod 100 <> 0, " " + LETRAS(Numero Mod 100), "")

    Case 1000 To 1999

        Resultado = "Mil" + IIf(Numero Mod 1000 <> 0, " " + LETRAS(Numero Mod 1000), "")

    Case 2000 To 999999

        Resultado = LETRAS(Numero \ 1000) + " Mil" + IIf(Numero Mod 1000 <> 0, " " + LETRAS(Numero Mod 1000), "")

    Case 1000000 To 1999999

        Resultado = "Un Millón" + IIf(Numero Mod 1000000 <> 0, " " + LETRAS(Numero Mod 1000000), "")

    Case 2000000 To 1999999999

        Resultado = LETRAS(Numero \ 1000000) + " Millones" + IIf(Numero Mod 1000000 <> 0, " " + LETRAS(Numero Mod 1000000), "")

End Select

LETRAS = Resultado 

End Function

 

Pasos:

Seleccionar y copiar el código

- Abrir VisualBasic

- Clic en Insertar

-Clic en Modulo

- Pegamos el código

- Cerrar

- Clic donde saldrá los números en texto

- Abrír  la función con igual, digitar aletras, paréntesis, clic en la celda a pasar en letras, cerrar el paréntesis. Enter

=ALETRAS(C3)

TALLER EN FORMACIÓN

8.  En el total que salga en letras en la siguiente celda:

9.  Una macro con un botón que salga el mensaje de la agencia de viaje.

TALLER

1) Crear  macros con el comando 

    MsgBox "Bienvenidos a esta web!", vbExclamation

MsgBox "Bienvenidos a esta web!", vbYesNo, "Mi título"

 

Combinemos varios botones

MsgBox "Bienvenidos a esta web!", vbYesNo + vbExclamation, "Mi título"

 

GUÍA Y OCTAVO TALLER  HERRAMIENTAS AVANZADAS DE HOJA DE CALCULO

Formato de celdas en Excel 

Ya hemos revisado cómo utilizar el formato de números en Excel para modificar la apariencia de valores numéricos. Ahora revisaremos las modificaciones que podemos realizar a la apariencia de la celda para cambiar sus bordes o su color de relleno entre otras cosas más.

El formato de celdas no es indispensable en el funcionamiento de las hojas de cálculo, pero puede ayudar a tener una mejor visualización y entendimiento de los datos presentados. Las herramientas de formato en Excel 2013 las podemos encontrar en tres ubicaciones:

1.   En la Cinta de opciones.

2.   En la mini barra que se muestra al hacer clic derecho sobre una celda.

3.   En el cuadro de diálogo Formato de celdas.

Ya que el formato de celdas es una funcionalidad muy utilizada en Excel, podremos acceder a una gran cantidad de comandos desde las tres ubicaciones antes mencionadas. Los comandos de la Cinta de opciones se encuentran en la ficha Inicio, repartidos en los grupos Fuente, Alineación y Estilos. También podemos encontrar varios de estos comandos en la mini barra mostrada al hacer clic derecho sobre una celda o rango.

A través de este cuadro de diálogo tendremos un mayor control sobre el formato aplicado a una celda de Excel. La sección Número nos muestra 12 categorías de formato de las cuales podemos elegir y además configurar ciertas propiedades particulares. A continuación describo brevemente cada una de las 12 categorías:

1. General: El formato General es el formato predeterminado de todas las celdas de una hoja y despliega los números tal como son ingresados excepto cuando el valor es muy grande para caber en la celda y lo convierte a notación científica.
2. Número: Este formato nos permite especificar la cantidad de posiciones decimales que deseamos mostrar y además indicar si deseamos incluir el separador de miles. Otra opción de configuración es la de indicar el formato de los números negativos ya sea solamente con el símbolo negativo (-), o de color rojo, o ambas opciones.
3. Moneda: El formato de Moneda nos permite elegir el símbolo a utilizar así como la cantidad de decimales a utilizar. Al igual que el formato anterior, podemos indicar la apariencia de los números negativos.
4. Contabilidad: El formato de Contabilidad es diferente del de Moneda en que los símbolos de moneda siempre están alineados de manera vertical.
5. Fecha: Esta opción nos permite elegir entre diferentes formatos para una fecha.
6. Hora: Nos permite elegir un formato para las horas ingresadas en una celda.
7. Porcentaje: Nos permite desplegar el símbolo de porcentaje e indicar la cantidad de decimales.
8. Fracción: Para las fracciones mostradas en la hoja de cálculo podemos elegir entre nueve diferentes formatos.
9. Científica: Para números expresados en notación científica podemos especificar la cantidad de decimales a mostrar.
10. Texto: Cuando este formato es aplicado a una celda, Excel tratará dicho valor como un texto, inclusive si es un número.
11. Especial: Esta opción contiene formatos especiales y dependen de la Configuración regional seleccionada. Algunas opciones de formatos especiales son los códigos postales, los números de teléfono o los números de seguridad social.
12. Personalizada: La opción de formato personalizado nos da la posibilidad de crear nuestros propios formatos de números. Esta es una opción que requiere una explicación más amplia por lo cual encontrarás un vínculo a otro artículo en la sección de material adicional.

Además de la Cinta de opciones y la mini barra, podemos encontrar estas opciones de configuración de formato dentro del cuadro de diálogo Formato de celdas. Podemos acceder a este cuadro de diálogo de diferentes maneras:

• Haciendo clic en el indicador de cuadro de diálogo de los grupos Fuente o Alineación de la ficha Inicio.
• Haciendo clic derecho sobre una celda y dentro del menú desplegable seleccionar la opción Formato de celdas.
• En la ficha Inicio, dentro del grupo Celdas, pulsar el botón Formato y seleccionar la opción Formato de celdas.

Además existen comandos de la Cinta de opciones que ofrecen la posibilidad de consultar más opciones y que nos llevarán a este mismo cuadro de diálogo. Por ejemplo, el comando Inicio > Alineación > Formato de alineación de celdas abrirá el cuadro de diálogo Formato de celdas en la sección correspondiente:

Resultado final

Proteger  Hoja 

Excel ofrece numerosas opciones para proteger el contenido de las hojas de cálculo. Podemos bloquear celdas específicas, o bien una parte de la hoja de cálculo, o toda ella.

Es importante entender que nos referimos a hojas de cálculo y no a los ficheros en sí, ya que ese es otro procedimiento. En este caso las protegemos para que no se modifiquen, pero cualquiera podrá leerlas o copiarlas.

Vamos a ver cómo proteger una hoja de cálculo en Excel.

1. Ir a: Cinta de opciones / Pestaña: Revisar / Grupo: Cambios / Proteger hoja. Mostrará ventana de opciones de proteger hoja.

   

2. Ir a: Permitir a los usuarios de esta hoja de cálculo / Activar todas las casillas.

3. Pulsar el botón Aceptar. Cierra la ventana y protege la hoja.

4. Ir a: Celda D2 o cualquier celda con fórmulas.

5. Verificar como en la barra de fórmulas no se muestra la fórmula. Pero sí actualiza los valores.

• OCULTAR FÓRMULAS

1. SELECCIONAR TODO. Clic sobre el botón seleccionar todo (botón en la intersección de rótulo de filas y columnas); O también, Atajo de teclado Ctrl+E. Seleccionará todas las celdas de la hoja.

2. Ir a: Cinta de opciones / Pestaña: Inicio / Grupo: Número / Formato de número. Mostrará la ventana de formato de celdas.

   
   

3. Clic sobre la Pestaña: Proteger.

4. Desactivar o activar la casilla: Bloqueada. Para permitir la edición de todas las celdas de la hoja.

5. Activar la casilla: Oculta. Para ocultar las fórmulas de la hoja.

6. Pulsar el botón Aceptar. Cierra la ventana y aplica las opciones.

TALLER

1)  De la nómina proteger y ocultar las fórmulas matemáticas.  Adicionalmente desproteger las celdas donde ingresamos información constantemente.

2)

 

GUÍA Y SEXTO TALLER  HERRAMIENTAS AVANZADAS DE HOJA DE CALCULO

La función SI anidada

Al referirnos a la función SI ANIDADA en realidad nos estamos refiriendo a la función SI con un agregado de complejidad. La función SI normalmente acepta dos condicionales, un valor de resultado para cuando se cumple la condición lógica y un valor cuando no se cumple dicha condición. Sin embargo los condicionales pueden tener múltiples secuencias, con lo cual se podría incluir una nueva función SI dentro de otra función SI y así sucesivamente.

 

SINTAXIS DE LA FUNCIÓN SI ANIDADA: 

=SI(PRUEBA_LÓGICA;VALOR_SI_VERDADERO;SI(PRUEBA_LÓGICA;VALOR_SI_VERDADERO;VALOR_SI_FALSO)) 

La siguiente imagen muestra el funcionamiento de la función SI anidada. Observa que la clave es que, en lugar de ejecutar una segunda acción, la primera función SI incluye una segunda función SI de manera que entre ambas funciones puedan ejecutar un máximo de tres acciones.

Ejercicio

Si la edad es menor de 4 años "jardín" si la edad es de 4 años a 6 años "preescolar" si es mayor de 6 años  "primaria"

Digitamos la fórmula

=SI(C5<4;"Jardin";SI(C5<6;"Preescolar";"Primaria"))

Taller

1) Si la compra es menor de $100,000 descuento 5% , si es mayor de $100,000 y menor de $500,000 descuento del 10%, si la compra es mayor de  $ 500,000 descuento del 20%. Mostrar la cantidad que descuentan. 

2) Tenemos un listado de Aprendices y sus calificaciones finales, las condiciones para obtener una beca son:

1. Si la calificación final está entre 9.5 y 10, obtiene un 80% de beca

2. Si la calificación final está entre 8.5 y 9.4, obtiene un 40% de beca

 3. Si la calificación final está entre 8.0 y 8.4, obtiene un 25% de beca

 4. Si la calificación final es menor a 7.9, no se otorga beca.

GUÍA Y SEPTIMO TALLER  HERRAMIENTAS AVANZADAS DE HOJA DE CALCULO

FUNCIÓN Y DE EXCEL

La función Y de Excel comprueba si se cumplen dos o más pruebas lógicas. Devuelve VERDADERO si se cumplen todas las pruebas y FALSO en caso de que alguna no se cumpla.

Sintaxis:

La fórmula Y se llama por la siguiente sintaxis:

=Y (valor_lógico1; valor_lógico2)

Pueden haber tantas pruebas lógicas como queramos, solo tendríamos que añadirlas por orden separadas por “;”. Se pueden poner entre 1 y 255 pruebas lógicas.

Donde:

• valor_lógico1: Obligatorio. Es obligatoria al menos una prueba lógica. Esta prueba puede ser desde una desigualdad matemática, B3>=1, hasta una prueba de cadenas o más complejo: LARGO(A2)>7. Esta prueba lógica será VERDADERO si se cumple y FALSO en caso contrario.
• valor_lógico2: Opcional. El segundo valor lógico es opcional, aunque no tiene mucho sentido la función Y con solo una prueba lógica.

Veamos un ejemplo de la fórmula Y. Tenemos una base de datos de un colectivo de personas. Para realizar una excursión a un parque de atracciones han de cumplir dos restricciones: ser mayor de edad (edad>=18) y por medidas de seguridad del parque, medir más de 1,60 m.

Necesitamos, en este caso, utilizar dos pruebas lógicas para valorar si los individuos cumplen los requisitos. La primera, mediante una desigualdad B2>=18 comprobamos que el individuo es mayor de edad. En la segunda prueba, sometemos a los valores de la altura (columna C) a la desigualdad de tener una estatura mayor o igual que 1,60 m.

=Y (B2>=18; C2>=1,60)

Ejemplo 2:

Veamos un ejemplo un poco más complejo. SI LA EDAD ES MENOR A 18 AÑOS Y LA ALTURA ES MAYOR A 180 CENTÍMETROS JUEGA FUTBOL, DE LO CONTRARIO JUEGA TENIS

Digitamos la fórmula

Copiamos la fórmula. El resultado

FUNCIÓN O DE EXCEL

Uso:

La función O de Excel (que expresa la llamada “disyunción lógica inclusiva”) verifica si se cumplen dos o más pruebas lógicas. Si se cumple al menos una de las pruebas, devuelve el valor VERDADERO y si no se cumple ninguna, el valor FALSO.

Sintaxis:

La sintaxis de la fórmula O es la siguiente:

=O (valor_lógico1; valor_lógico2)

Se pueden utilizar tantas pruebas lógicas como queramos (hasta un límite de 255). Todas las pruebas lógicas deben ir separadas por un punto y coma: “;”.

Donde:

• valor_lógico1: Obligatorio. Es obligatoria al menos una prueba lógica. Podemos utilizar cualquier función, desde una desigualdad de una otra celda a una función de cadena o incluso una igualdad interior 2+2=4. Esta prueba lógica será VERDADERO si se cumple y FALSO en caso contrario.
• valor_lógico2: Opcional. El segundo valor lógico es opcional, aunque no tiene mucho sentido la función O de Excel con solo una prueba lógica.

Ejemplo 1:

Un profesor ha decidido que solo hará el examen final de su asignatura a los alumnos que hayan suspendido al menos un trimestre de su asignatura. Para ello se construye una hoja de cálculo Excel y utiliza una fórmula O para determinar quién hará el examen final.

=O (B2<5; C2<5; D2<5)

Ejemplo 2:

SI LA EDAD ES MENOR A 18 AÑOS O  LA ALTURA ES MAYOR A 180 CENTÍMETROS JUEGA FÚTBOL, DE LO CONTRARIO JUEGA TENIS

Digitamos la fórmula

Copiamos la fórmula.

TALLER

1)

SI ES MAYOR A 35 AÑOS Y  EL SEXO ES MASCULINO; JUEGA TEJO , DE LO CONTRARIO BILLAR

Nombre	EDAD	SEXO	DEFINITIVA
Hazel	34	M
Liza	67	F
Robert	45	M
Howard	15	M
Maxine	22	M
Joe	67	M
Gail	34	M
Alyssa	32	F
Sheryl	12	F
Brad	16	M
Kendrick	15	M
Ellen	33	F
Felix	45	M
Mark	27	F
Allen	34	F
Ari	56	M
Sam	45	M

Lo copian y lo pegan. Queda así: 

2) 

si el sueldo del empleado es mayor a 500.000 y es de sexo masculino, ganara de bonificación el 50% del sueldo, de lo contrario ganara el 60%

nombre total vendido sexo bonificacion Y    bonificacion O

Hazel $ 200.000 M

Liza $ 1.000.000 F

Robert $ 300.000 M

Howard $ 600.000 M

Maxine $ 459.000 M

Joe $ 2.000.000 M

Gail $ 600.000 M

Alyssa $ 800.000 F

Sheryl $ 300.000 F

Brad $ 3.000.000 M

Kendrick $ 590.000 M

Ellen $ 789.000 F

Felix $ 999.000 M

Mark $ 50.000 F

Allen $ 5.000.000 F

Ari $ 88.231 M

Sam $ 111.250 M