manual base de datos

EXPLOTACIÓN DE DATOS A TRAVÉS DE EXCEL

Dedicatoria

Al Dios Supremo, por haberme dado la vida, el

conocimiento y estar rodeado de las personas que más

queremos.

1

Agradecimientos

El agradecimiento a nuestras madres y padres

quienes con sus sabios consejos supieron hacer un

hombre de bien y vocación de servicio a la

colectividad, a mis alumnos gracias a ellos, aprendí y

aprendo hacer un mejor profesional.

2

Resumen

El objetivo de este manual es enseñarle, mediante ejercicios prácticos, como crear macros

en un entorno Microsoft Excel y permitir reproducirlas automáticamente.

No está dirigido a programadores si no a cualquier usuario habitual, el manual está

organizado en 3 capítulos.

3

Tabla de Contenidos

Introducción

Capitulo I: Grabador de Macros..............................................................................................6

Capitulo II: Creacion de formulario ultilizando Macro........................................................13

Capitulo III: Macro usando codigo VB.................................................................................32

Capitulo IV: Filtros Avanzados Usando Macros..................................................................40

Capitulo V: Sql reportes en excel..........................................................................................49

Bibliografia..........................................................................................................................5

4

Introducción

Las macros de Excel nos permiten automatizar tareas que realizamos cotidianamente de

manera que podamos ser más eficientes en nuestro trabajo. Una macro no es más que una

serie de comandos o instrucciones que permanecen almacenados dentro de Excel y que

podemos ejecutar cuando sea necesario y cuantas veces lo deseemos. (Ortíz, 2011)

El programa Excel dispone de una gran cantidad de objetos. “El automatizar tareas nos

permite en determinado momento, minimizar esfuerzos y recursos”. (Delgado, 2010)

Uno de los principales beneficios de las macros de Excel, es poder automatizar las tareas

que realizamos en nuestro día a día, y los ahorros que logramos por medio de la

automatización nos permitirán ser más eficientes en nuestro trabajo, o en la actividad en la

que nos encontremos.

Sin bien las macros son temas avanzados de Excel, tan pronto como hayas comenzado con

las macros y la programación en VBA, encontrarás que el Excel se puede convertir en una

herramienta muy útil para resolver problemas complejos.

5

Capítulo I: Grabador de Macros

Ejemplo Creación de una búsqueda sencilla usando grabador de Macros

1. Habilitar la opción Desarrollador dentro de nuestro Excel, para eso entramos a

archivos, luego opciones y en personalizar cinta de opciones; marcamos la casilla de

Desarrollador y aceptamos.(Fig1)

6

Fig1.Opciones de Excel.

2. Crear una tabla con las columnas Id producto, Nombre del producto, cantidad y

Marca; rellenar datos como se muestra en la imagen. (Fig2)

Fig2. Datos de la tabla.

3. En la misma hoja de Excel crear una tabla con 2 celdas que nos ayudaran a hacer la

búsqueda y añadir un botón que nos servirá para ejecutar la macros.(Fig3)

7

Fig3. Celdas para búsqueda.

4. Agregar una nueva hoja donde se copiaran los datos de la búsqueda, y crear 4

celdas.(Fig4)

8

Fig4. Resultados.

5. Estando en la Hoja2 , Vamos a la pestaña Desarrollador, Gravar macro y asignamos

un nombre en este caso será Búsqueda y aceptar(Fig5)

9

Fig5. Gibar.

6. Luego vamos a Datos, Filtro y avanzada; con el cursor sobre “Rango de la lista”,

vamos a Hoja1 y seleccionamos toda la tabla de nuestros datos.(Fig6)

Fig6.

10

7. Cambiamos a “Rango de criterios” y seleccionamos nuestras celdas donde

pondremos los parámetros para realizar la búsqueda.(Fig7)

Fig7.

8. Marcamos la opción Copiar a otro lugar, colocamos el cursor en “copiar”; y

seleccionamos las casillas de la segunda Hoja donde aparecerán los datos que

coincidan con la búsqueda. Vamos a la pestaña Desarrollador y presionamos

Detener grabación, nuestra macros ya está lista.(Fig8)

11

Fig8.

9. Finalmente en la primera hoja vamos al botón que creamos que se hace insertando

una Forma hacemos clic derecho asignar macros, y elegimos en nombre dela macro

que acabamos de grabar, en mi caso la macro es Búsqueda.(Fig9)

Fig9.

10. Para probar la macro escribimos un valor o ambos en las celdas de la hoja 1(Fig10)

presionamos Buscar y podemos ver q en la hoja 2 se mostraran el resultado.(Fig11)

Fig10.

12

Fig11.

13

Capitulo II: Creación de formulario utilizando Macro

1- Primero hacemos el formato que le damos al formulario, en este caso utilizamos

una platilla donde ponemos Producto, cantidad y precio. Eso es para ingresar en la

hoja 2.(Fig12)

Fig12.

14

2- Hoja 2 ingresa los datos de la hoja 1(Fig13).

Fig13.

3- Como se realiza Primero grabar un macro ,ingresamos en la pestaña desarrollador y

le damos grabar macros(Fig14)

Fig13.

15

4- Saldar una pantalla, en nombre ponemos Inventario y le damos aceptar se supone

que ya está grabando el macro (Fig14)

Fig14.

5- Luego en la hoja 2 en eso espacios en blanco pegamos los cuadros q se encuentra

en la primero hoja. (Fig15)

16

Fig15.

6.- Estos cuadros de la primera hoja, seleccionamos y le damos copiar. (Fig16)

Fig16.

17

7.- Luego vamos a la hoja 2 seleccionamos y le damos pegado especial. (Fig17)

Fig17.

8.- Y luego saldrá ese cuadro y le damos en balastes y aceptar así asenso con los tres

cuadros de producto, cantidad y precio. (Fig18)

18

Fig18.

9.- Por ultimo le damos detener grabación y generamos un botón en herramientas. (Fig19)

Fig19.

19

10.- Luego le damos inventario y aceptar. (Fig20)

Fig20.

11.- Probamos el programa. (Fig21)

Fig21.

20

12.- Y los datos pasaran a la hoja 2 por defecto. (Fig22)

Fig22.

En este ejemplo vamos utilizar macros en un sistema de ventas pequeño para ir mostrando

la lista de los pedidos que realiza el cliente a través de un botón asignándole una macro.

Para este ejemplo hemos diseñado previamente un Excel de la siguiente manera (Fig23):

Fig23.

21

El precio lo hayamos con el método coincidir, el monto multiplicando el precio por la

cantidad el descuento si el monto es mayor a 3000 y el total es el monto menos el

descuento.

Código precio: =INDICE(L6:P8;COINCIDIR(F7;L6:L8;0);L1+1)

Código descuento: =SI(C11>3000;C11*10%;0)

Código Marca: =INDICE(M5:P5;1;L1)

Diseñamos otra hoja en el Excel de la siguiente manera(Fig24):

Fig24.

La idea es pegar los datos de la fila 13 de la Hoja1 al hacer clic en el botón ingresar,

pasarlos a la Hoja 2 a partir de la fila 4 en adelante y al hacer clic en el botón de la Hoja 2

llamado regresar nos mande a la Hoja 1.Para eso usaremos macros.

22

1.- Primero crearemos el macro para pasar de la Hoja 2 a la Hoja 1 para eso vamos a vista,

macro, grabar macro le ponemos el nombre a la macro, método abreviado de teclado,

guardamos en este libro y ponemos una pequeña descripción y aceptamos.(Fig25)

Fig25.

2.- La macro ya empezó a grabarse, damos clic en la Hoja 1 y detenemos la macro ya que

eso será todo lo que hará esa macro. (Fig26)

23

Fig26.

3.- Ahora damos clic derecho en el botón regresar, asignar macro, seleccionamos la macro

que hemos creado para este botón y aceptamos. (Fig27)

Fig27.

4.- Ahora la probamos, damos clic en el botón regresar y veremos que ejecuta la macro

porque nos manda a la Hoja 1. (Fig28)

24

Fig28.

5.- Ahora veremos cómo hacer la macro para que copie la información de la hoja 1 a la hoja

2 como mencionamos anteriormente para esto vamos a la hoja dos, damos ctrl + fecha

hacia abajo y llegamos a la última fila de la hoja 2 y le ponemos de nombre fin. (Fig29)

25

Fig29.

6.- Ahora seleccionamos los datos que queremos pasar desde la hoja 1 y le damos copiar.

(Fig30)

Fig30.

26

7.- Usamos el pegado especial para ponerla en la Hoja 2 en la parte donde queremos. Bien

ahora para ir pegando uno debajo de otro hacemos lo siguiente, copiamos los datos de la

Hoja 1 presionamos F5 seleccionamos fin aceptamos nos pondrá en la última fila de la Hoja

2 ahora presionaremos ctrl + fecha para arriba y nos llevara donde está el ultimo pedido que

hicimos bajaremos una fila y haremos el pegado especial (valores). Eso haremos al grabar

la macro. (Fig31)

Empezamos a grabar la macro.

Fig31.

27

8.- Ahora que ya empezó a grabarse la macro copiamos los datos de la Hoja 1 y seguimos

los pasos dicho anteriormente.Fig (32)

Fig32.

9.- Presionamos F5 damos clic en fin y aceptamos. (Fig33)

28

Fig33.

Al aceptar nos manda a la última fila de la Hoja dos, presionamos Carl + fecha para arriba y

luego bajamos una fila y allí pegamos los datos. Detenemos la grabación de la Macro.

(Fig34)

Fig34

.

10.- Asignamos la macro ingresar al botón ingresar de la Hoja 1. (Fig35)

29

Fig35.

11.- Probamos la Macro y vemos que ingresa los datos en la línea 5 volvemos a ejecutar

con otros datos y se pegan encima de los datos puestos anteriormente en la línea 5 en lugar

de la línea 6. (Fig36)

Fig36.

12.- Para solucionar esto nos vamos a vista, macros, ver macros, ingresar, y damos clic en

paso a paso para modificarla. (Fig37)

30

Fig37.

13.- Nos aparecerá una ventana de esta manera (Fig38):

31

Fig38.

La primera parte del código quiere decir que estamos copiando de A23 a G23 luego la

referencia fin , el ctrl + fecha hacia arriba

Para solucionar nuestro problema vamos a modificar el siguiente código:

Range("A5").Select

Por este de acá:

Selection.Range("A2").Select

El selection lo que hace es dar una posesión relativa y el Range(“A2”) hace que donde

estuvo el cursor la última vez se lea como celda uno y entonces mande los datos a la celda

2, entonces al pasar al fin luego ctrl + fecha hacia arriba ese casillero se lee como celda 1 y

la siguiente seria la celda 2 donde irán los datos.

14.- Ahora salimos del programa, se guarda el código y lo probamos (Fig39).

32

Fig39.

Pues bien vemos que ya podemos ingresar los datos y se van guardando fila a fila.

Capitulo III: Macro usando código VB

Ejercicio: Crear una calculadora en macros

1.-Crear un nuevo archivo de Excel, ir a desarrollador luego Visual Basic (Fig40).

Fig40.

33

2.- Insertar un UserFrom(Fig41).

Fig41.

3.- Ponerle de nombre frmCalculadora, y darle el siguiente diseño (Fig42).

34

Fig42.

4.- Declarar las siguientes variables en el formulario

Di x, y As Single

Dim operacion As String

5.- A continuación mostraremos el código para cada botón

BOTONES NUMERICOS

Prívate Sub cmd1_Click()

txtResultado.Text = txtResultado.Text + "1"

End Sub

35

Private Sub cmd2_Click()


End Sub



End Sub



End Sub



End Sub



36

End Sub



End Sub



End Sub



End Sub

BOTONES DE OPERACIONES

Private Sub cmdDivision_Click()

x = txtResultado.Text

operacion = "division"

txtResultado.Text = ""

37

End Sub

Private Sub cmdMenos_Click()


operacion = "resta"


End Sub

Private Sub cmdMultiplicacion_Click()


operacion = "multiplicacion"


End Sub

Private Sub cmdSuma_Click()


operacion = "suma"


38

End Sub

BOTON “CE”

Private Sub cmdReinicio_Click()


End Sub

BOTON Resultado

Private Sub cmdIgual_Click()

y = txtResultado.Text

If operacion = "suma" Then txtResultado.Text = CInt(x) + CInt(y)

If operacion = "resta" Then txtResultado.Text = CInt(x) - CInt(y)

If operacion = "multiplicacion" Then txtResultado.Text = CInt(x) * CInt(y)

If operacion = "division" Then txtResultado.Text = CSng(x) / CSng(y)

End Sub

39

6.- Insertamos un módulo (Fig43).

Fig43.

7.- Creamos una macros en el modulo

Sub calculadora()

frmCalculadora.Show

End Sub

40

8.- creamos un botón al cual asignamos la macro creada (Fig44).

Fig44.

9.- Probamos la calculadora

41

Capitulo IV: Filtros Avanzados Usando Macros

En este caso realizaremos un ejercicio cuyo propósito es diseñar un Filtro Avanzado

automatizado con el uso de una Macro.

Para este ejemplo utilizaremos la siguiente lista de datos de las ventas de una empresa

que contiene el vendedor (Fig45), el producto, las unidades vendidas, el precio unitario, el

valor total y el sector.

Fig45.

42

Los cuadros de la parte de superior que se encuentran vacíos son los que usaremos como

criterios para realizar nuestros filtros avanzados que deben tener los mismos nombres que

nuestra información de ventas.

Lo que buscaremos es ingresar datos en los cuadros vacíos de la parte superior y que según

esos criterios se muestre la información en el cuadro.

1.- Para empezar a grabar la macro nos dirigimos a la barra de herramientas, vista, macros,

grabar macro (Fig46).

Fig46.

2.- Nos aparecerá un cuadro de dialogo con el nombre Grabar Macro donde ingresaremos el

nombre que queremos darle a la macro en este caso: Filtro avanzado, método de tecla

abreviado para que el macro se ejecute al pulsar dicha combinación de teclas, guardamos la

macro en este libro y le damos una pequeña descripción, luego aceptamos (Fig47).

43

Fig47.

3.- Luego de aceptar la macros empieza a grabarse y en parte inferior nos aparece un botón

para detener cuando hayamos acabado de grabar nuestra macro (Fig48).

Fig48.

44

4.- Vamos a la barra de herramientas, datos y damos clic en avanzadas (Fig49).

Fig49.

Nos aparece el cuadro de filtro avanzado (Fig50).

45

Fig50.

5.- En acción dejamos marcado Filtrar la lista sin moverla a otro lugar, y en rango de lista

marcamos nuestro rango de lista de la siguiente manera (Fi51):

Fig51.

6.- En el rango de criterio seleccionamos los cuadros de la parte superior de la siguiente

manera (Fig52):

46

Fig52.

Damos clic en aceptar y listo hemos acabado de grabar nuestra macro, vamos al botón

detener y le damos clic (Fig53).

Fig53.

7.- Ahora queremos llamar la macro que ya está grabada a través de un botón en este caso

vamos a insertar, formas e insertamos un cuadrado y le ponemos texto (Fig54).

47

Fig54.

8.- Damos clic derecho en el cuadro, y le damos en asignar macro (Fig55).

48

Fig55.

9.- Nos aparecerá el siguiente cuadro, donde seleccionaremos la macro que hemos creado

en este caso Filtro Avanzado y le damos aceptar (Fig56).

Fig56.

49

10.- Ahora probemos si funciona nuestro filtro avanzado usando macros.

Para probarlo en unidades pondremos >50 para que solo muestre las ventas cuyas unidades

sean mayores que 50, y pulsamos el botón aplicar macro que creamos y le asignamos la

macro (Fig57).

Fig57.

11.- Y listo vemos que solo nos muestran las ventas con unidades mayores que 50(Fig58).

50

Fig58.

También podemos filtrar con varios criterios a la vez.

2 V: SQL reportes en Excel

1. Entramos al Excel y nos vamos a la opción datos y seleccionamos la opción de otras

fuentes para iniciar desde SQL Server (Fig59).

51

Fig59.

2. Luego nos aparece un asistente para la conexión de datos ponemos nuestro nombre de

servidor y le damos siguiente (Fig60):

Fig60.

52

3. Luego nos aparecerá una ventana que nos va permitir seleccionar la base de datos y la

tabla y de ahí le damos siguiente (Fig61).

Fig61.

4. Luego guardamos el archivo de conexión de datos y de ahí le damos finalizar (Fig62).

53

Fig62.

5. Luego nos aparece esta venta que nos va permitir seleccionar como queremos que vaya

aparecer los datos y le damos aceptar (Fig63).

54

Fig63.

6. Finalmente nos aparece el reporte de la tabla seleccionada y podemos ver lo que nos

piden (Fig64).

55

Fig64.

56

Referencias

Moisés Ortíz (enero del 2011) Excel Total. https://exceltotal.com/

Rob Wong (17 de julio 2015) udemy blog. https://blog.udemy.com/tutorial-de-macros-

excel-como-crear-una-sencilla-macro-en-excel-2013/

Ludovico Lannoy(2010) VBA Excel 2010: Desarrolle mediante un ejemplo una aplicación

profesional

Edwin Raymundo Delgado (2010) Macros en Excel.

https://docs.google.com/document/d/1VHYZAfWvunR-

BQfPZYu6tgWR9P29LuOPzn5dfEYmPro/edit?pli=1

57

https://www.google.com.pe/search?hl=es&tbo=p&tbm=bks&q=inauthor:%22Ludovic+Lannoy%22

https://blog.udemy.com/tutorial-de-macros-excel-como-crear-una-sencilla-macro-en-excel-2013/

https://blog.udemy.com/tutorial-de-macros-excel-como-crear-una-sencilla-macro-en-excel-2013/

https://exceltotal.com/

manual base de datos

Documents