manual ms excel 2013 avanzado (1)

101

Upload: jesus

Post on 20-Oct-2015

183 views

Category:

Documents


14 download

TRANSCRIPT

Page 1: Manual MS Excel 2013 Avanzado (1)
Page 2: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

2

Contenido SESION ........................................................................................................................................ 4

1. FUNCIONES DE INFORMACION. ........................................................................................ 5

2. EJERCICIO......................................................................................................................... 10

3. FUNCIONES DE TEXTO. .................................................................................................... 12

SESION ...................................................................................................................................... 16

4. ESQUEMAS. ..................................................................................................................... 17

5. EJERCICIO......................................................................................................................... 22

6. TABLAS DINAMICAS. ........................................................................................................ 23

7. EJERCICIOS. ...................................................................................................................... 32

SESION ...................................................................................................................................... 33

8. FORMULARIO DE DATOS. ................................................................................................ 34

9. EJERCICIO......................................................................................................................... 36

10. VALIDACION DE DATOS. .............................................................................................. 37

11. EJERCICIO..................................................................................................................... 41

SESION ...................................................................................................................................... 42

12. MACROS DE COMANDO. ............................................................................................. 43

13. Ejercicio. ...................................................................................................................... 49

SESION ...................................................................................................................................... 50

14. CONTROLES DE FORMULARIO. .................................................................................... 51

15. CUADRO DE GRUPO. ................................................................................................... 53

16. BOTONES DE OPCION. ................................................................................................. 54

17. CASILLA DE VERIFICACION. .......................................................................................... 55

18. CUADRO COMBINADO. ............................................................................................... 56

19. EJERCICIO..................................................................................................................... 57

SESION ...................................................................................................................................... 58

..................................................................................................................................................... 59

SESION ...................................................................................................................................... 60

16. Macros de Función ...................................................................................................... 61

17. EJERCICIO..................................................................................................................... 62

SESION ...................................................................................................................................... 63

20. EDITOR VBA ................................................................................................................. 64

Page 3: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

3

21. VARIABLES VBA. .......................................................................................................... 68

SESION ...................................................................................................................................... 70

22. CREACION DE FORMULARIO. ...................................................................................... 71

23. AGREGAR CONTROLES DE FORMULARIO. ................................................................... 73

24. EJERCICIO..................................................................................................................... 79

SESION .................................................................................................................................... 83

25. LIBROS COMPARTIDOS. ............................................................................................... 84

SESION .................................................................................................................................... 90

26. IMPORTAR O EXPORTAR DATOS. ................................................................................ 91

27. IMPORTAR DATOS DELA WEB ..................................................................................... 94

28. ORIGENES DE BASE DE DATOS. ................................................................................... 96

SESION .................................................................................................................................. 100

Page 4: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

4

SESION

Temas

Funciones de Información.

- Celda.

- EsBlanco.

- EsError.

- Es.Impar/Es.Par.

- EsNumero.

Ejercicio.

Funciones de Texto.

- Carácter.

- Concatenar.

- Encontrar.

- Mayus.

- Minusc.

- Nompropio.

Logros

Al término el alumno será capaz de manejar las funciones de celdas y funciones

De texto de manera avanzada.

Page 5: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

5

1. FUNCIONES DE INFORMACION.

FUNCION CELDA.

La función CELDA devuelve información acerca del formato, la ubicación o el contenido

de una celda. Por ejemplo:si desea comprobar que una celda contiene un valor

numérico en lugar de texto antes de realizar un cálculo en ella, utilizaremos esta

función.

SINTAXIS:

=CELDA (Tipo de información, referencia)

Tipo de Información: Es Obligatorio. Es un valor de texto que especifica el tipo

de información de la celda que se desea obtener.

Referencia: Opcional. La celda sobre la que desea información. Si se omite, se

devuelve la información especificada en el argumento “tipo de información”

para la última celda cambiada. Si el argumento de referencia es un rango de

celdas, la función CELDA devuelve la información sólo para la celda superior

izquierda del rango.

TIPO DE INFORMACION RESULTADO

DIRECCION La referencia, en forma de texto de la primera celda del argumento referido.

COLUMNA El número de columna de la celda del argumento referido.

COLOR Valor 1 si la celda tiene formato de color para los valores negativos; de lo contrario, devuelve 0 (cero).

CONTENIDO Valor de la celda superior izquierda de la referencia, no una fórmula.

ARCHIVO Nombre del archivo (incluida la ruta de acceso completa) que contiene la referencia, en forma de texto. Devuelve vacío (“”) si todavía no se ha guardado la hoja de cálculo que contiene la referencia.

FORMATO Un valor de texto correspondiente al formato numérico de la celda. Los valores de texto para los distintos formatos se muestran en el sgte. Ejemplo: Si la celda tiene formato de color para los números negativos, devuelve “-“al final del valor de texto. Si la celda está definida para mostrar todos los valores o los valores positivos entre paréntesis, devuelve “()” al final del valor de texto.

PARENTESIS Valor 1 si la celda tiene formato con paréntesis para los valores positivos o para todos los valores, de lo contrario, devuelve 0 (cero).

PREFIJO Un valor de texto que corresponde al “prefijo de rotulo” de la

Page 6: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

6

celda. Devuelve un apostrofo (´) si la celda contiene texto alineado a la izquierda, comillas (“) si la celda contiene texto alineado a la derecha, un acento circunflejo (^) si el texto de la celda está centrado, una barra inversa (\) si la celda contiene texto con alineación de relleno y devolverá texto vacío (“”) si la celda contiene otro valor.

PROTEGER Valor 0 (cero) si la celda no está bloqueada; de lo contrario, devuelve 1 si la celda está bloqueada.

FILA El número de la fila de la celda del argumento ref.

TIPO Un valor de texto que corresponde al tipo de datos de la celda. Devolverá “b” (para blanco) si la celda está vacía, “r” (para rotulo) si la celda contiene una constante de texto y “v” (para valor) si la celda contiene otro valor.

ANCHO El ancho de columna de la celda redondeado a un entero. Cada unidad del ancho de columna es igual al ancho de un carácter en el tamaño de fuente predeterminada.

- EJEMPLO:

Calcular, si la celda contiene valor numérico, multiplicarlo por 3, en caso contrario

mostrar “no es número”.

DATO RESPUESTA

15 45

total no es numero

Carmen no es numero

20 60

5 15

45 135

juan no es numero

10 30

en que fila se ubica "juan" 90

en que columna se ubica 60 3

FORMULA:

=SI(CELDA("tipo",B84)="v",B84*3,"no es numero")

EXPLICACION LOGICA:

Si la celda B84, es igual a un valor numérico,

entonces multiplicar B84 x 3, en caso no se cumpla

colocar “no es numero”.

=CELDA("fila",B90)

=CELDA("columna",C87)

Page 7: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

7

FUNCIÓN ESBLANCO.

Devuelve el valor lógico verdadero si el argumento de valor es una referencia a una

celda vacía, de lo contrario devuelve falso.

SINTAXIS:

=ESBLANCO(valor)

- EJEMPLO:

Multiplica el valor de la celda por 3 siempre que no se encuentre vacía.

DATO RESPUESTA

10 30

35 105

10 30

0

21 63

0

10 30

- EJERCICIO:

Descontar AFP al sueldo, siempre y cuando tenga hijos, pero si hay celdas en blanco

colocar “Falta Valor.”

SUELDO HIJOS DESC.AFP

850 Si

730 No

No

920 Si

Si

800 No

AFP 10%

FORMULA:

=SI(ESBLANCO(B14),0,B14*3)

EXPLICACIÓN LÓGICA:

Si la celda B14 está vacía, será 0, sino será b14 x 3

Page 8: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

8

FUNCION ESERROR

ESERROR se refiere a uno de los valores de error (#N/A, #¡VALOR!, #¡REF!, #¡DIV/0!,

#¡NUM!, #¿NOMBRE? o #¡NULO!).

SINTAXIS

=ESERROR(valor)

- EJEMPLO:

Comprobar si existe error en la sgt. Tabla.

SUELDO AFP TOTAL CORRECCION

890 89 979 FALSO

550 55 605 FALSO

N/A #¡VALOR! #¡VALOR! VERDADERO

780 78 858 FALSO

695 69.5 764.5 FALSO

950 95 1045 FALSO

N/A #¡VALOR! #¡VALOR! VERDADERO

N/A #¡VALOR! #¡VALOR! VERDADERO

960 96 1056 FALSO

AFP 10%

FUNCION ES.IMPAR / ES.PAR

FUNCION ES.IMPAR

Devuelve VERDADERO si número es impar o FALSO si número es par.

SINTAXIS.

=ES.IMPAR(número)

Número:Es el valor que desea probar. Si el argumento número no es un entero, se

trunca.

- EJEMPLO.

FORMULA:

=ESERROR(D15)

EXPLICACIÓN LOGICA:

Existe error en la celda D15

(total)

Page 9: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

9

DATOS RESPUESTA

10 FALSO

50 FALSO

20 FALSO

5 VERDADERO

- EJERCICIO.

Si el sueldo contiene valores impares, redondear, de lo contrario dejarlo igual.

NOMBRE SEGURO EDAD SUELDO NETO

juan afp 20 850.23

luisa essalud 35 965.1

fernanda afp 10 1425

sofia afp 43 856.12

pedro essalud 29 863.15

FUNCION ES.PAR

Devuelve VERDADERO si el número es par y FALSO si el número es impar.

SINTAXIS

=ES.PAR(número)

DATOS RESPUESTA

8 VERDADERO

22 VERDADERO

21 FALSO

5 FALSO

- EJERCICIO.

Si el número de su edad es par, ponerle "Equipo Rojo" sino "Equipo Azul"

NOMBRE SEGURO EDAD DISTINTIVO

juan afp 20

luisa essalud 35

fernanda afp 10

sofia afp 43

pedro essalud 29

FORMULA:

=ES.IMPAR(B14)

EXPLICACION LOGICA:

Busca dentro de la celda si el valor es un número impar.

FORMULA:

=ES.PAR(B46)

EXPLICACION LOGICA:

Busca dentro de la celda si el valor es un número par.

Page 10: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

10

FUNCION ESNUMERO.

Esta función nos permite saber si el valor de una celda es un número.

SINTAXIS

=ESNUMERO(VALOR)

- EJEMPLO

DATOS RESPUESTA

55 VERDADERO

10 VERDADERO

Carmen FALSO

Texto FALSO

10 VERDADERO

n/a FALSO

65 VERDADERO

2. EJERCICIO.

Realizar la sgt. Tabla.

FORMULA:

=ESNUMERO(C13)

EXPLICACION LOGICA:

Busca dentro de la celda un valor numérico, si es número

colocará verdadero, sino falso.

Page 11: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

11

- Colocar en la columna azul, las edades pares y en la verde los que tienen edad

impar.

Armado en Excel.

Page 12: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

12

3. FUNCIONES DE TEXTO.

FUNCION CARÁCTER.

Devuelve el carácter especificado por un número.La función CARACTER en Excel

regresa un carácter específico para un número entero que ha sido proporcionado

como argumento. Un computador tiene un juego de caracteres con un número entero

asignado y es precisamente ese número el que debemos proporcionar como

argumento.

SINTAXIS

=CARÁCTER(numero)

- EJEMPLO.

DATOS RESPUESTA

65 A

192 À

35 #

45 -

55 7

85 U

FUNCION CONCATENAR.

La función CONCATENAR en Excel nos permite unir dos o más cadenas de texto en una

misma celda lo cual es muy útil cuando nos encontramos manipulando bases de datos

y necesitamos hacer una concatenación.Puede usar también el símbolo & (ampersen)

para unir textos.

La función CONCATENAR tiene una sintaxis muy sencilla donde cada argumento será

un texto que se irá uniendo al resultado final. El máximo de argumentos que podemos

especificar en la función es de 255 y el único obligatorio es el primer argumento.

SINTAXIS

=CONCATENAR(texto1,texto2,…)

FORMULA

=CARACTER(B14)

RESPUESTA LOGICA.

Muestra el carácter 65 del conjunto (A)

Page 13: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

13

- EJEMPLO:

La función CONCATENAR nos puede ayudar en casos en los que la información está

distribuida en varias columnas y deseamos integrar la información en una sola.

Nombres Apellidos F. Concatenar

Sara Lopez Sara Lopez

Fiorella Arias Fiorella Arias

Denisse Prado Denisse Prado

Mirtha Quispe Mirtha Quispe

Mirza Castillo Mirza Castillo

FUNCION ENCONTRAR

ENCONTRAR y ENCONTRARB buscan una cadena de texto dentro de una segunda

cadena y devuelven el número de la posición inicial de la primera cadena de textoa

partir del primer carácter de la segunda cadena de texto.

SINTAXIS

=ENCONTRAR(texto buscado,dentro del texto, num. Inicial)

Texto buscado: Es el texto que desea encontrar.

Dentrodel_texto: Es el texto que a su vez contiene el texto que desea encontrar.

Núm_inicial: Especifica el carácter a partir del cual comenzará la búsqueda. El primer

carácter de dentro_del_texto es el carácter número 1. Si omite núm_inicial, se supone

que es 1.

- EJEMPLO

DATO FORMULA DESCRIPCION RESULTADO Amanda Artiaga =ENCONTRAR("A",C17)

Posición de la primera "A" de la cadena anterior 1

=ENCONTRAR("m",C17) Posicion de la primera "m" de la cadena anterior 2

=ENCONTRAR("a",C17,11)

Posicion de la segunda "a" de la cadena anterior 12

FORMULA:

=CONCATENAR(C13," ",D13)

EXPLICACION LOGICA:

Escribimos la función y especificamos el primer

argumento (C13), como segundo argumento

especificamos un espacio vacio para que el

nombre y el apellido queden separados (“ “) y

por ultimo el tercer argumento (D13)

Page 14: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

14

FUNCION MAYUS

La función mayus convierte un texto en mayúsculas.

SINTAXIS

=MAYUS(texto)

- EJEMPLO

NOMBRE MAYUSCULAS

jose JOSE

antonio ANTONIO

karen KAREN

milagros MILAGROS

- EJERCICIO

En el sgt. Cuadro, si hay texto, convertirlo en mayúsculas.

DATO RESPUESTA

452

karen

135

185

jose

FUNCION MINUSC

Convierte una cadena de texto en minúsculas.

SINTAXIS

=MINUSC(texto)

EJEMPLO

Nombre Conv. Minuscula

CRISTHIAN cristhian

FORMULA:

=MAYUSC(D12)

EXPLICACION LOGICA.

Con esta función convertimos el texto de la celda en

mayúsculas.

FORMULA:

=MINUSC(C13)

EXPLICACION LOGICA.

Con esta función convertimos el texto de la celda en

minúscula.

Page 15: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

15

JHONNY jhonny

CARLOS carlos

LUIS luis

HERLINDA herlinda

FUNCION NOMPROPIO.

Cambia la primera letra de cada palabra en mayúsculas.

SINTAXIS

=NOMPROPIO(Texto)

- EJEMPLO

Apellidos y Nombres Conv. con Nompropio

sara lopez Sara Lopez

fiorella arias Fiorella Arias

denisse prado Denisse Prado

mirtha quispe Mirtha Quispe

mirza castillo Mirza Castillo

Page 16: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

16

SESION

Temas

Esquemas.

- Agrupar / Desagrupar.

- Auto esquema.

- Borrar esquema.

Ejercicio.

Tablas dinámicas.

- Personalizar campos y datos de distribución.

- Segmentación de datos.

- Actualizar campos.

- Cambiar origen de datos.

- Grafico Dinámico.

Ejercicio.

Logros

- El alumno puede Manejar esquemas.

- Trabajar con tablas dinámicas

Page 17: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

17

4. ESQUEMAS.

Si tiene una lista de datos que desea agrupar y resumir, puede crear un esquema de

hasta ocho niveles, uno para cada grupo.Cada nivel interno, representado por un

número superior de símbolos de esquema, muestra datos de detalle del nivel externo

anterior, representado por un número inferior de símbolos de esquema.

Use un esquema para mostrar rápidamente filas o columnas de resumen, o bien para

mostrar los datos de detalle de cada grupo.

1. para mostrar filas para un nivel, haga clic en los símbolos de esquema adecuado. 2. el nivel 1 contiene las ventas totales de todas las filas de detalle. 3. el nivel 2 contiene las ventas totales para cada mes en cada región.

4. el nivel 3 contiene las filas de detalle (sólo las fi las de detalle 11 a 13 están

actualmente visibles).

5. para expandir o contraer datos en el esquema, haga cl ic en los símbolos de

esquema y .

AGRUPAR / DESAGRUPAR.

AUTO ESQUEMA.

- Abrimos el archivo con el que vamos a trabajar.

Page 18: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

18

- Vamos a la cinta Datos.

- Ve a Datos > Agrupar > Autoesquema.

- Cuando estés agrupando información, Excel buscará la información que está

entre los extremos lógicos (ejemplo: títulos al principio y fórmulas de totales al

final), lo seleccionará y te dará la opción para minimizarlo. Si Excel te informa

que no pudo crear un esquema automático, la razón es porque tu hoja de

cálculo no contiene fórmulas. Tendrás que hacerlo manualmente.

- Observa tu hoja de cálculo automáticamente agrupada. Dependiendo en la

complejidad y el acomodo de tu hoja de cálculo, notarás que las secciones de

tu información se habrán agrupado y ahora están marcadas con barras a lo

largo de la parte superior y/o el lado izquierdo de las celdas.

Page 19: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

19

- Haz clic en el botón de menos [-] para minimizar un grupo de información.

BORRAR ESQUEMA.

Para borrar el esquema, vamos a Datos > Desagrupar > Borrar esquema.

AGRUPAR INFORMACIÓN DE MANERA MANUAL.

Seleccionar la celda que se quiera minimizar. En ese caso quiero minimizar la de

“vendedor”

Page 20: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

20

Vamos a Datos > Agrupar > Agrupar.

Nos aparece un cuadro de dialogo, el cual nos ofrece 2 opciones de agrupar: Filas y

Columnas. En este ejemplo elegimos columnas.

Observa la sección de la información que se ha agrupado. Ahora está marcada con una

barra a lo largo dela parte superior de las celdas, oculta “vendedor” y se muestra

sueldo y lo demás.

Se puede agrupar áreas adicionales, en este ejemplo, agruparemos la columna que e

sigue a “vendedor”, seleccionamos “vendedor” y Sueldo” y repetimos los pasos

anteriores.

Page 21: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

21

DESAGRUPAR.

Para desagrupar una sección, vuelve a seleccionarla y ve a “Datos > Desagrupar >

Desagrupar”.

Page 22: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

22

5. EJERCICIO.

- Crear una tabla, como el que se muestra.

- Calcula el total, comisión Dscto y neto.

Comisión: 15%

Dscto: 20%

- Resolver el resumen 01, 02 y 03.

- Una vez terminado agrupar:

- Sueldo.

- Total.

- Comisión.

- Dscto.

- Neto.

Page 23: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

23

6. TABLAS DINAMICAS.

Las tablas dinámicas son tablas interactivas que permiten al usuario agrupar y resumir

grandes cantidades de datos en un formato conciso y tabular, para analizar e informar

los contenidos más fácilmente.

Pueden clasificar, contar y resumir los datos, y están disponibles en muchos programas

de hojas de cálculos.

CREAR UNA TABLA DINAMICA.

- Poner el cursor en cualquier celda de la Tabla o seleccionamos la tabla.

- Insertar ->Tabla dinámica ->Tabla Dinámica (Grafico dinámico)

- Seleccionar la opción.

Page 24: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

24

- Aparece la siguiente ventana.

Elegimos donde se creara la tabla dinámica, si en una nueva hoja de cálculo o en la

misma hoja de calculo existente.

CUANDO CONVIENE USAR UN INFORME DE TABLA DINAMICA

Cuando deseas comparar totales relacionados, sobre todo si tienes una lista larga de

números para resumir y deseas realizar comparaciones distintas con cada número.

Puesto que los informes de tabla dinámica son interactivos, puede cambiar la

presentación de los datos para ver más detalles o calcular diferentes resúmenes, como

recuentos o promedios.

Page 25: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

25

COMO SE ORGANIZAN LOS DATOS

Cada columna o campo de los datos de origen se convierte en un campo de tabla

dinámica que resume varias filas de información. Ejemplo, la columnaApellidos se

convierte en el campo Apellidos.

- EJEMPLO.

-

Creamos tabla dinámica.

Etiquetas de fila

Suma de NETO

Suma de N°

Suma de Faltas

Suma de Sueldo

Suma de Bon1

Suma de Bon2

Suma de Dscto1

Suma de Dcsto2

Palomino 4190 3 0 5000 40 150 400 600

Total general 4190 3 0 5000 40 150 400 600

Al crear una tabla dinámica se muestra la tabla de campos.

Aquí podemos distribuir los datos y personalizar nuestros

campos. Podemos hacer que se muestre toda la

información o elegir cuales se mostraran como: turno,

faltas, apellidos, sueldo, etc.

Hacemos que nuestra tabla sea más larga o más corta,

dependiendo de los datos que se mostraran en ella.

Page 26: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

26

La primera que se crea es el de Apellidos (en este ejemplo) y este contiene el filtro de

datos, podemos seleccionar el nombre de las personas que queremos mostrar datos.

CAMBIAR EL DISEÑO DE TABLA.

En Excel 2013 al seleccionar una celda de nuestra tabla dinámica, aparecen 2 nuevas

cintas “analizar” y “diseño”.

En la cinta de Diseño, encontramos el grupo diseño.

Este grupo nos permite cambiar el diseño de nuestra tabla, cambian el diseño de

Informe de nuestra tabla dinámica.

Page 27: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

27

- El primero es “mostrar en forma compacta” que es el que se tiene por defecto

al crear nuestra tabla dinámica.

- El segundo es “mostrar en forma de esquema”, cambiando la forma de nuestra

tabla dinámica, creando filtros predefinidos.

- El tercero es “mostrar en formato tabular”, cambiando nuestra tabla a un

aspecto tabulado.

- Y en “filas en Blanco”, nos permite insertar líneas en blanco después de un

elemento.

Aquí también podemos cambiar el estilo de nuestra tabla, seleccionando uno de los

estilos que nos presenta Excel.

SEGMENTACION DE DATOS.

Para filtrar los datos de un informe de tabla dinámica,se puede usar la segmentación

de datos.

La segmentación de datos proporciona botones en los que se puede hacer clic para

filtrar los datos de las tablas dinámicas. Además del filtrado rápido, la segmentación de

datos también indica el estado actual de filtrado, lo cual facilita el entendimiento de lo

que se muestra exactamente en un informe de tabla dinámica filtrado.

Page 28: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

28

Cuando se selecciona un elemento, dicho elemento se incluye en el filtro y los datos de

ese elemento se muestran en el informe.

¿Qué es la segmentación de datos?

Las segmentaciones de datos son componentes de filtrado fáciles de usar que

contienen un conjunto de botones que le permiten filtrar los datos de un informe de

tabla dinámica rápidamente sin la necesidad de abrir listas desplegables para buscar

los elementos que se desean filtrar.

Cuando se usa un filtro común de informe de tabla dinámica para filtrar varios

elementos, el filtro solamente indica que se filtran varios elementos y es necesario

abrir una lista desplegable para ver los detalles de filtrado. Sin embargo, las

segmentaciones de datos etiquetan claramente el filtro que se aplica y proporcionan

detalles para que los datos que se muestran en el informe de tabla dinámica filtrado

puedan entenderse fácilmente.

CREAR SEGMENTACION DE DATOS.

Creada nuestra tabla dinámica, seleccionamos una celda de nuestra tabla y vamos a la

cinta “Analizar”.

En el grupo “filtrar”, elegimos “Insertar Segmentación de datos”.

Nos aparecerá una ventana, en el cual elegiremos que es lo que vamos a filtrar.

Page 29: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

29

Ahora nos aparece una ventana flotante, podemos insertar más de un segmento de

datos.

Page 30: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

30

ACTUALIZAR DATOS.

Para actualizar los datos de nuestra tabla dinámica, siempre que se halla realizado

cambios en nuestra tabla de origen. En nuestra cinta “Analizar” en el grupo “datos”

encontramos “actualizar”.

CAMBIAR ORIGEN DE DATOS.

Dentro de la misma cinta “Analizar” en el grupo “datos”, tenemos “cambiar origen de

datos”, este nos permite cambiar el origen de los datos de nuestra tabla por otra.

GRAFICO DINAMICO.

Puede ser difícil obtener una visión global cuando los datos están en un gráfico

dinámico grande o cuando la mayoría de los datos son complejos e incluyen texto y

números con encabezados de columna, como el siguiente:

Page 31: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

31

Con un gráfico dinámico le será más fácil darle sentido a los datos. Aunque un gráfico

dinámico muestre las series de datos, las categorías y los ejes de gráficos igual que un

gráfico estándar, también incluye controles de filtrado interactivos en el mismo

gráfico, lo que le permite analizar rápidamente un subconjunto de datos.

CREAR GRAFICO DINAMICO.

- Haga clic en cualquier punto de los datos.

- En la pestaña Insertar, en el grupo Gráficos, seleccione Grafico dinámico.

-

Elegimos un modelo de gráfico dinámico.

Este gráfico se insertara con todos los datos que se muestran en los campos de la tabla

dinámica, podemos modificar los datos del gráfico, modificando los campos.

Estos gráficos cambiaran también de acuerdo a los segmentos de campos.

Page 32: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

32

7. EJERCICIOS.

Crea la sgte. Tabla, resuelve y crea tabla dinámica.

A esta tabla dinámica, insértale segmentos de datos y gráficos dinámicos.

Page 33: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

33

SESION

Temas

Formularios de datos.

- Insertar registro.

- Eliminar registro.

- Deshacer cambios en registro.

- Buscar registros.

- Criterios.

Ejercicio.

Validación de Datos.

- Configuración.

- Eliminar validación.

- Mensaje entrante.

- Mensaje Error.

Ejercicio.

Logros

- Al termino el alumno será capaz de

Trabajar con planillas.

Page 34: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

34

8. FORMULARIO DE DATOS.

Una forma diferente de trabajar con las tablas grandes o usar una presentación

diferente en el ingreso y mantenimiento de datos es utilizando formulario, este es un

cuadro de diálogo que permite al usuario escribir o mostrar una fila entera de datos o

registro dentro de una ventana vertical.

La herramienta formularios no está disponible directamente en la ventana de Excel,

por ello debemos agregarla a la barra de acceso rápido:

- Seleccionar más comandos en la Barra de acceso rápido.

- En el cuadro Comandos disponibles en: elegir comandos que no están en la

cinta de opciones, buscar el comando formulario, clic en agregar y aceptar.

Page 35: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

35

- Ahora vemos integrado en la barra de acceso rápido al comando formulario.

Ahora crearemos los encabezados de nuestra tabla, porque delo contrario no se podrá

completar la operación, ejm.

Una vez creado los encabezados, realizamos un clic en formulario.

En el formulario aparecen los datos que hemos ingresado como nuestro encabezado,

ahora simplemente llenamos el contenidos que nos pide, y para seguir ingresando

datos, realizamos un clic en “nuevo”.

Al crear el formulario, disponemos de las siguientes opciones:

Nuevo: nuevo registro.

Eliminar: Eliminar el registro activo.

Restaurar: Deshace los cambios efectuados mientras no haya presionado Enter

(guarda los cambios).

Buscar anterior: Ir al registro anterior.

Buscar siguiente: Ir al siguiente registro.

Criterios: Sirve para aplicar filtros a la búsqueda.

Page 36: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

36

Cerrar: Cierra el formulario.

9. EJERCICIO.

Crea el sgt. Encabezado.

Utiliza el formulario para ingresar los datos que se piden.

Esto es muy útil cuando se trabaja con planillas.

Page 37: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

37

10. VALIDACION DE DATOS.

Si desea asegurarse de que si se introducen los datos correctos en una hoja de cálculo

entonces puede especificar qué datos son válidos para cada celda o rango de celdas.

Se puede restringir los datos a un tipo determinado(como números enteros, números

decímales, texto, etc.)

- Abra o cree un archivo de Excel el cual contenga celdas con datos específicos a

validar.

- Colóquese en la celda que desea validar, o si son varias las celdas que llevarán

la misma validación, selecciónelas todas.

- Vaya a la ficha Datos, y en el grupo Herramientas de datos, presione el botón

Validación de datos.

Page 38: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

38

En la ficha Configuración de la ventana Validación de datos, especifique el criterio de

validación para la celda o celdas seleccionadas.

Cualquier valor Permite cualquier tipo de dato, opción predeterminada. Número entero solo números enteros. Decimal solo números enteros y decimales. Lista Permite seleccionar un dato de una lista desplegable. Fecha Permite ingresar datos fecha. Hora Permite ingresar datos hora. Longitud del texto Permite ingresar un dato con un límite de longitud en

caracteres. Personalizada Permite la introducción de una fórmula que calcule un valor

lógico.

Page 39: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

39

Mensaje Entrante

Ingrese un título y un mensaje que será mostrado al posicionarse en la celda o celdas

con la validación.

Mensaje de error

Seleccione un estilo para el mensaje de error, ingrese un título y el mensaje el cual será

mostrado al ingresar un dato fuera de la regla de validación, y presione Aceptar.

Page 40: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

40

ELIMINAR VALIDACION.

Para eliminar registros de validación, seleccionamos un rango de celdas validadas,

ingresamos a validación de datos y clic en borrar todos.

Page 41: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

41

11. EJERCICIO.

Validar la siguiente listad de datos paracadacampo o columna según las características

siguientes:

La Columna Nombres deberá permitir solo el ingreso de 10 caracteres. Para la Columna

Apellidos deberá permitir el ingreso de 20 caracteres. Para la Columna Sueldo deberá

ingresar solo valores entre 800 y 2000.

La Columna Categoría mostrara una lista desplegable en la cual se elijara la categoría

del Empelado.

La Columna Área mostrara una lista desplegable en la cual se elegirá el área del

Empelado.

Para la Columna Fecha de Contrato solo permitirá el ingreso de fechas menores o

iguales al año actual.

Cada Columnadeberá tener su mensaje entrante y su mensaje de error según la

condición de validación.

Page 42: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

42

SESION

Temas

Marcos de comando.

- Tipos de Marcos.

- Activas ficha desarrollador.

- Crear macros.

- Asignar macros a botones.

- Ejecutar macros.

- Eliminar macros.

Ejercicio.

Logros

- El alumno puede automatizar tareas repetitivas.

Page 43: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

43

12. MACROS DE COMANDO.

Es un conjunto de instrucciones de código que permiten realizar una tarea

determinada. Las macros se escriben (codifican)en el lenguaje de programación VBA

(Visual Basic forApplications).

El Excel trae incorporado el editor de VBA, de este modo las macros se programan

dentro del mismo programa.

TIPOS DE MACROS PARA EXCEL.

Las macros se pueden agrupar en dos categorías: Macros de funciones y Macros de

comandos.

Macros de funciones: Si ejecuta frecuentemente una tarea de Microsoft Excel, puede

automatizarla mediante una macro. Una macro consiste en una serie de comandos y

funciones que se almacenan en un módulo de Visual Basic y que puede ejecutarse

siempre que sea necesario ejecutar la tarea.

Macros Comandos: La creación de estas macro funciones consiste en poner una serie

de argumentos, en las cuales podemos ir poniendo los datos que lleva una función

normal de Excel para Windows y al final le indicamos que operaciones hacer con estos

argumentos y de esta manera se optimiza el uso de varias fórmulas para llegar a un

resultado.

Macros de Visual Basic: Las macros de Visual Basic están basados en módulos, que por

tal hecho se vuelven más complicadas, teniendo como ventaja que son compatibles

dentro de varias aplicaciones, de esta manera se tendría un lenguaje común de macros

entre varios programas, con lo cual se logra que se pierda menos tiempo en realizar

una macro en alguno de estos programas. Por esta razón es que las macros de Visual

Basic son un tema particular.

Activar Ficha Desarrollador.

Para trabajar con Macros debemos activar la ficha Programador donde están

contenidos todos los comandos asociados a la programación en VBA.

Archivo-> Opciones de Excel->Personalizar Cinta de Opciones->Activar Ficha

Desarrollador-> Aceptar.

Page 44: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

44

Crear macro

Cuando grabe una macro, es recomendable que primero anote y ensaye cada uno de

los pasos antes de grabar la macro, esto le permitirá tener una macro más limpia, es

decir, con los menos errores posibles.

Para grabar una macro, realice lo siguiente:

Page 45: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

45

- Vaya a la ficha Desarrollador, en el grupo Código, haga clic en Grabar macro. - En la ventana mostrada escriba un nombre adecuado para la macro, si lo desea

puede asignar un método abreviado el cual ejecutará la macro al presionar la combinación de teclas asignadas, especifique en donde se guardará, es decir, el alcance de su funcionamiento, opcionalmente podrá escribir una descripción sobre su funcionamiento o creación, por ultimo haga clic en Aceptar para iniciar la grabación.

- Realice todas y cada una de las acciones que desee grabar en el orden

adecuado.

- Cuando termine la grabación de las acciones, será necesario detener la grabación, para ello vaya de nuevo a la ficha Programador, en el grupo Código, haga clic en Detener grabación.

ASIGNAR MACRO A BOTONES. Creamos una tabla con los sgts. Datos.

En la ficha Desarrollador, en el grupo controles, clic en Insertar.

Page 46: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

46

Agregamos botones de Controles de Formulario.,

En otra hoja de cálculo creamos una tabla, con los mismos datos del formulario anterior, que será donde se graben todos los datos ingresados con anterioridad.

Grabamos macro.

Ahora grabamos los datos ingresados en nombre, se guardaran en nombre de la nueva hoja de cálculo.

Page 47: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

47

Seleccionamos nuestra primera celda, clic derecho y copiar.

Nos dirigimos a nuestra hoja donde se copiaran los datos ingresados, realizamos clic derecho y clic en insertar celdas copiadas.

Realizamos lo mismo con todas los demás datos, luego detenemos la grabación de la macro. Clic derecho sobre nuestro botón “enviar” y elegimos “asignar macro” elegimos la macro y aceptamos.

Ahora cada vez que hagamos clic en enviar, se enviara la informacion del formulario a nuestra hoja de datos.

Page 48: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

48

ELIMINAR MACRO. Para eliminar una macro, hacemos un clic en macro.

Nos aparecerá una ventana, aquí seleccionamos la macro que queremos eliminar. La seleccionamos y clic en eliminar.

Page 49: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

49

13. Ejercicio.

Crea la sgte. Macro.

Page 50: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

50

SESION

Temas

Controles de Formulario.

Cuadro de grupo.

Botones de Opción.

Casilla de verificación.

Cuadro combinado.

Ejercicio.

Logros

- El alumno tiene manejo básico de los

Controles de formularios.

- Puede crear boletas y facturas.

Page 51: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

51

14. CONTROLES DE FORMULARIO.

Los controles de formulario en Excel son objetos que podemos colocar dentro de una

hoja y que nos darán funcionalidad adicional para interactuar mejor con los usuarios y

tener un mejor control sobre la información.

Podemos utilizar estos controles para ayudar a los usuarios a seleccionar elementos de

una lista predefinida o permitir que el usuario inicie una macro con tan solo pulsar un

botón. Los controles de formulario en Excel se encuentran dentro de la ficha

Desarrollador dentro del grupo Controles. Solamente pulsa el botón Insertar y

observarás cada uno de ellos:

INSERTAR UN CONTROL DE FORMULARIO.

Para insertar cualquiera de los controles de formulario debes seleccionarlo del menú

desplegable y hacer clic sobre la hoja de Excel arrastrando el borde para “dibujar” el

contorno del control.

TIPOS DE CONTROLES DE FORMULARIO.

Existen diferentes tipos de controles de formulario en Excel que ofrecen diversos tipos

de funcionalidad e interacción con el usuario. Desde una simple etiqueta hasta

controles que permiten una selección múltiple de sus opciones.

Page 52: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

52

Barra de desplazamiento: Al hacer clic en las flechas se va desplazando la barra

dentro de un intervalo predefinido.

Botón: El botón nos permite ejecutar una macro al momento de hacer clic

sobre él.

Botón de opción: Nos permite una única selección dentro de un conjunto de

opciones.

Casilla de verificación: Permite la selección o no selección de una opción.

Control de número: Nos ayuda a aumentar o disminuir un valor numérico.

Cuadro combinado: Es una combinación de un cuadro de texto con un cuadro

de lista.

Cuadro de grupo: Agrupa varios controles dentro de un rectángulo.

Cuadro de lista: Muestra una lista de valores de los cuales podemos elegir una

sola opción o múltiples opciones de acuerdo a la configuración del control.

Etiqueta: Permite especificar un texto o breves instrucciones en el formulario.

Page 53: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

53

15. CUADRO DE GRUPO.

Se pueden utilizar un cuadro de grupo o un marco para agrupar controles afines (por

ejemplo: botones de opción, casillas o contenidos estrechamente relacionados) en una

sola unidad visual.

AGREGAR UN CUADRO DE DIALOGO.

- Seleccionar el cuadro de dialogo de la ficha Desarrollador, en el cuadro

grupo, controles de formulario.

- Realice un clic sin soltar y arrastre hasta formar el cuadro.

- Dentro de este cuadro se pueden ingresar botones de opciones, casillas de

verificación, etiquetas y cuadros de texto.

Page 54: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

54

16. BOTONES DE OPCION.

Los botones de opción permiten a un usuario de Excel hacer una selección dentro de

una lista de posibilidades y en todo momento tener un solo elemento seleccionado.

Hoy mostraré cómo crear grupos de botones de opción en Excel independientes a

pesar de encontrarse en la misma hoja.

De manera predeterminada podemos seleccionar una sola opción entre todos los

botones de opción ubicados en la misma hoja. La solución para crear dos grupos de

botones de opción es agregar el control llamado Cuadro de grupo:

Debemos agregar un Cuadro de grupo para cada conjunto de botones de opción que

deseamos crear. Es muy importante que al dibujar el Cuadro de grupo te asegures de

abarcar el área completa de los botones de opción que deseas asociar de manera que

el grupo sea creado correctamente.

FORMATO DE CONTROL

Si queremos conocer la opción seleccionada en cada grupo es suficiente con hacer clic

derecho sobre cualquier botón de opción y seleccionar la opción Formato de control.

En la sección Control debemos colocar la dirección de la celda de nuestra hoja que

mostrará la opción seleccionada.

Page 55: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

55

Nota: los botones de opción son representados por números (1,2,3, etc) dependiendo de la

cantidad de botones que se hallan asignado al formulario.

17. CASILLA DE VERIFICACION.

Casilla de verificación Permite a un usuario seleccionar o anular la selección de uno o

varios valores en un grupo de opciones. Puede activar más de una casilla de

verificación a la vez en una hoja de cálculo o en un cuadro de grupo.

FORMATO DE CONTROL.

Clic derecho sobre la casilla e ingresamos a formato de control.

Ahora asignamos su valor a una celda.

Nota: los valores que representan a la casilla de verificación es “verdadero y “falso”;

verdadero si la casilla se encuentra activa y falso si esta desactivada.

Page 56: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

56

18. CUADRO COMBINADO.

Un cuadro combinado muestra una colección de uno o más elementos de entre los

cuales podemos elegir una opción. Un cuadro combinado es diferente a una lista de

validación de datos ya que nos permite conocer la opción que el usuario ha

seleccionado en el momento.

Ahora, utilizando el puntero del ratón, deberás dibujar sobre la hoja el Cuadro

combinado que necesitas. De inmediato observarás en pantalla el control de

formulario recién creado. Aún después de haber dibujado el control, puedes modificar

su tamaño arrastrando cualquiera de las esquinas hasta obtener el tamaño deseado.

FORMATO DE CONTROL.

Para configurar las opciones del cuadro combinado debemos hacer clic en el botón

Propiedades que también forma parte del grupo Controles dentro de la ficha

Programador. Se mostrará el cuadro de diálogo Formato de control.

En la opción Rango de entrada seleccionaremos las celdas que contienen los valores de

texto que se desplegarán dentro del cuadro combinado, si los valores se encuentran en

otra hoja, se puede crear un nombre de rango para vincularla. Y en el cuadro de texto

Vincular con celda seleccionaremos una celda que mostrará el número de la opción

que se haya elegido.

Observa cómo la celda vinculada muestra el número del elemento de la lista que ha

sido seleccionado. Si se selecciona el primer elemento se mostrará el número 1 y así

sucesivamente hasta el final de la lista. La celda vinculada puede ser de gran utilidad

para tomar alguna decisión en base a la selección del usuario.

Page 57: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

57

19. EJERCICIO. Diseñar el sgte. Formulario.

MARCA y COSTO:

NISSAN $35.000.00

KIA $ 28.100.00

BMW $ 45.000.00

TOYOTA $ 15.800.00

HYUNDAI $ 31.000.00

Valores: Negro 5%

Blanco 2%

Rojo 3%

Azul 4%

N° ARO Aro 13 $200.00

Aro 15 $ 260.00

Aro 18 $ 350.00

SEGURO s/.450.00

ACCESORIOS

Kit de limpieza S/. 60.00

Llanta de repuesto S/. 235.00

Forro de asientos S/. 120.00

Page 58: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

58

SESION

Temas

Ejercicios.

Page 59: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

59

DATOS

Clientes 2

ITEM CODIGO NOMBRES Y APELLIDOS DISTRITO CIUDAD PAIS

1 C001 JUAN MARCOS GARCIA PEREZ LIMA LIMA PERU

2 C002 MARIA BERNAL RODRIGUEZ SAN MIGUEL LIMA PERU

3 C003 NANCY MENDEZ AGUIRRE SAN BORJA LIMA PERU

Vendedor 1

ITEM CODDIGO NOMBRES Y APELLIDOS

1 V001 HERLINDA CODINA

2 V002 JOSE MARQUEZ

3 V003 JHONNY GUTIERREZ

Productos

CODIGO DESCRIPCION PRECIO

P001 TECLADO ACER ERGONOMICO $ 25.00

P002 MONITOR SAMSUNG 17 PULGADAS $ 150.00

P003 CASE MIDITOWER 1000 V $ 45.00

Page 60: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

60

SESION

Temas

Macros de función.

- Crear macros.

- Ejecutar macros.

- Eliminar macros.

Ejercicio.

Logros

- Al termino el alumno será capaz de crear

Funciones para Excel.

Page 61: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

61

16. Macros de Función

- Abrir el Editor VBA (ALt+F11) y F7 (para abrir la Hoja de código)

- Ingresamos la definición de la macro:

- Estructura de una Macro de función:

Function nombre de la función (argumento)

.

termina con

.

End Function

- Entre ambas definiciones va el código de la función.

- En el código ingresamos el nombre de la función seguida del signo igual y

de su definición.

- Como vemos el nombre de la función es cubo y su argumento se llama

número (lo traduciríamos como "el Cubo de un número").

- Después volvemos a escribir cubo, el signo igual, y su definición.

- Ahora Guardamos la función, el programa nos muestra el siguiente

mensaje:

Respondemos Si.

Page 62: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

62

17. EJERCICIO.

Crear lasfunciones: IGV, Cambiar Dólares, Cambiar Soles.

Page 63: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

63

SESION

Temas

Editor VBA.

Variables VBA.

Logros

- El alumno tendrá conocimientos básicos

De programación en Visual Basic.

Page 64: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

64

20. EDITOR VBA

El Editor de Visual Basic, VBE por sus siglas en inglés, es un programa independiente a

Excel pero fuertemente relacionado a él porque es el programa que nos permite

escribir código VBA que estará asociado a las macros.

Existen al menos dos alternativas para abrir este editor, la primera de ellas es a través

del botón Visual Basic de la ficha Desarrollador.

El segundo método para abrir este programa es, en mi opinión, el más sencillo y rápido

y que es a través del atajo de teclado: ALT + F11. El Editor de Visual Basic contiene

varias ventanas y barras de herramientas.

Page 65: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

65

En la parte izquierda se muestra el Explorador de proyectos el cual muestra el proyecto

VBA creado para el libro actual y además muestra las hojas pertenecientes a ese libro

de Excel. Si por alguna razón no puedes visualizar este módulo puedes habilitarlo en la

opción de menú Ver y seleccionando la opción Explorador de proyectos.

El Explorador de proyectos también nos ayuda a crear o abrir módulos de código que

se serán de gran utilidad para reutilizar todas las funciones de código VBA que

vayamos escribiendo.

Dentro del Editor de Visual Basic puedes observar una ventana llamada Inmediato que

está en la parte inferior. Esta ventana es de mucha ayuda al momento de escribir

código VBA porque permite introducir instrucciones y observar el resultado inmediato.

Además, desde el código VBA podemos imprimir mensajes hacia la ventana Inmediato

con el comando Debug.Print de manera que podamos depurar nuestro código. Si no

puedes observar esta ventana puedes mostrarla también desde el menú Ver.

El área más grande en blanco es donde escribiremos el código VBA. Es en esa ventana

en donde escribimos y editamos las instrucciones VBA que dan forma a nuestras

macros.

Page 66: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

66

ENTORNO DEL EDITOR VBA.

Barra de menú: En la parte superior de la ventana se encuentran las opciones

generales del programa, como las clásicas (Archivo, Edición, Ver, Insertar, Ventana y

Ayuda) y las especializadas (Depuración, Ejecutar, Complementos).

Barra de herramientas: Debajo de la barra de menú, se encuentra la barra de

herramientas. En orden, los botones son: Ver <aplicación principal >, Insertar

(UserForm, Módulo, Módulo de clase, Procedimiento), Guardar, Cortar, Copiar, Pegar,

Buscar, Deshacer, Rehacer, Ejecutar Sub/UserForm o Ejecutar macro, Interrumpir,

Restablecer <proyecto>, Modo de diseño, Explorador de proyectos, Ventana de

Propiedades, Examinador de objetos, Cuadro de herramientas, Asistente de Office.

Ventana de proyecto: En esta ventana que se encuentra al lado izquierdo de la

pantalla, hay 3 botones: Ver código, Ver objeto y Alternar carpetas. Debajo de estos

botones, se encuentran numeradas las hojas del libro de Excel que se esta usando y en

las cuales se pueden ingresar funciones.

Page 67: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

67

Si se activara la Ventana de Propiedades (Ver>Ventana de Propiedades o F4), esta

aparecerá debajo de la ventana del proyecto. En esta se pueden apreciar las

características de la hoja de trabajo activa que se encuentran en el libro que se esta

editando.

Área de Trabajo: El resto del espacio está dedicado a una hoja donde se escribirán los

comandos que servirán para programar las funciones que caracterizarán el macros que

se está creando.

Page 68: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

68

21. VARIABLES VBA.

Cuando programamos en VBA frecuentemente necesitamos un repositorio para

almacenar el resultado de alguna operación. Las variables en VBA son utilizadas para

guardar valores y su tipo dependerá de clase da dato que deseamos guardar dentro de

ellas.

En VBA existen variables de tipo entero que almacenan números, variables de tipo

doble que también almacenan números pero con decimales, variables de tipo texto

para guardar una cadena de caracteres entre algunos otros tipos de variables. A

continuación haremos una revisión de cada uno de estos tipos.

Variables de tipo entero

Las variables de tipo entero son utilizadas para guardar números enteros. Debemos

utilizar la palabra clave Integer para declarar una variable de este tipo.

Dim x As Integer

x = 6

En la primera instrucción estoy declarando la variable con el nombre “x” y estoy

indicando que será del tipo Integer. “Declarar una variable” significa avisar a Excel

sobre la existencia de dicho repositorio para guardar información. En la segunda

instrucción asigno el valor 6 a la variable “x”.

Variables de tipo doble

Las variables de tipo doble pueden almacenar números con el doble de precisión

incluyendo números decimales. La palabra clave para este tipo de variables es Double.

Dim x As Double

x = 3.1416

Aunque las variables de tipo doble pueden almacenar números enteros sin problema,

no es recomendable hacerlo porque estaremos desperdiciando espacio en la memoria

del ordenador. Es decir, el tamaño reservado para una variable doble es el adecuado

para guardar números decimales, si solo guardamos un número entero quedará

especio sin utilizar. Por lo tanto es recomendable utilizar siempre el tipo de variable

adecuado para cualquier número.

Page 69: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

69

Variables de tipo texto

Una variable de tipo texto se declara con la palabra clave String. En el siguiente código

declararé la variable título y posteriormente le asignaré un valor.

Dim libro As String

libro = "Programación en Excel"

Variables de tipo lógico

Una variable de tipo lógico es aquella que puede almacenar solamente dos valores:

falso o verdadero. La palabra clave para definir estas variables es Boolean.

Dim continuar As Boolean

continuar = True

La primera línea declara la variable “booleana” y en la segunda le asignamos un valor.

Solamente podemos asignar dos valores a este tipo de variables: True (verdadero) y

False (falso).

Page 70: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

70

SESION

Temas

Creación de formularios.

Agregar controles de Formulario.

Ejercicio.

Logros

- El alumno puede crear formularios a través

De Visual Basic.

Page 71: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

71

22. CREACION DE FORMULARIO.

Un formulario VBA es un cuadro de diálogo donde agregamos diversos tipos de

controles como botones de comando, cajas de texto, casillas de verificación, y otros

controles más que serán de utilidad para obtener información de un usuario ya sea

para procesarla y devolver algún resultado o simplemente almacenarla.

Los formularios nos permiten crear aplicaciones en Excel para extender el uso y

funcionalidad de la herramienta y aunque su uso está fuertemente relacionado con los

usuarios avanzados de Excel, pronto verás que no es tan complicado utilizarlos. Para

iniciar nuestro ejemplo agregaremos un nuevo formulario de la siguiente manera. En la

ficha desarrollador pulsa el botón Visual Basic y se mostrará el Editor de VBA. Haz clic

derecho sobre VBA Project y selecciona la opción Insertar > UserForm:

Page 72: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

72

De inmediato el panel principal mostrará el formulario recién creado así como un

pequeño cuadro de diálogo con el título Cuadro de herramientas el cual contiene los

controles que podemos agregar al formulario.

El Cuadro de herramientas tiene un botón para cada tipo de control como etiqueta,

cuadro de texto, cuadro combinado, cuadro de lista, casilla de verificación, botón de

opción, etc. Para agregar un control al formulario es necesario hacer clic en el botón

correspondiente dentro del Cuadro de herramientas y posteriormente hacer clic

dentro del formulario. Otra manera de agregar un control al formulario es pulsar el

botón del control y arrastrarlo sobre el formulario.

Page 73: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

73

23. AGREGAR CONTROLES DE FORMULARIO.

Para nuestro ejemplo necesitamos agregar una etiqueta, un cuadro de texto, un

cuadro de lista y un botón de comando de manera que nuestro formulario quede de la

siguiente manera:

Los botones lo encontramos en el cuadro de herramientas.

Lo activamos desde ver > cuadro de

herramientas.

Page 74: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

74

PARTES DEL CUADRO DE HERRAMIENTAS

Seleccionar Objetos: Con esto activo, podemos mover los elementos dentro del

formulario.

Etiqueta: Sirve para poder poner mensajes o textos en la pantalla, pero no son

editables por el usuario.

Cuadro de texto:Permite que el usuario pueda escribir directamente.

Marco: Es un contenedor para los demás controles.

Boton de comando: Permite que el usuario pueda pulsar botones y que realicen

determinadas acciones.

Casilla: Permite tener opciones sobre las que el usuario confirma o niega.

Botón de opción: Permite tener varias opciones de las cuales seleccionar una.

Cuadro combinado:permite tener varias opciones de las cuales seleccionar y ver una.

Cuadro de lista: Presenta una lista de opciones y permite seleccionar una o varias.

Imagen: Permite incluir imágenes como fotografías, dibujos, etc..

Page 75: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

75

Para cambiar el texto de la etiqueta y del botón de comando debemos modificar la

propiedad llamada Caption y eso lo haremos desde la ventana Propiedades. Si esta

ventana no se muestra actualmente en el Editor de Visual Basic puedes seleccionarla

desde el menú Ver o también puedes hacer clic derecho sobre alguno de los controles

y seleccionar la opción Propiedades lo cual mostrará la ventana:

Encuentra la propiedad Caption y al lado derecho ingresa el valor de dicha propiedad

para el control seleccionado. Para hacer el cambio de esta propiedad para el botón de

comando solo debes seleccionarlo de la lista desplegable que se encuentra en la parte

superior de la misma ventana Propiedades. Al terminar el cambio de la propiedad

Caption tendremos el formulario de la siguiente manera:

Page 76: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

76

En nuestro ejemplo hemos nombrado las cajas de texto de la sgte. Manera:

CODIGO DEL BOTON CMDINGRESO

Text1

Text2

Text3

Text4

Text5

CMDINGRESO CMDNUEVO CMDSALIR

Page 77: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

77

CODIGO CMDNUEVO.

CODIGO CMDSALIR

End (se cierra el formulario)

CARGAR FORMULARIO DESDE EXCEL.

Para cargar un formulario desde una hoja de Excel será suficiente con agregar un

botón de comando ActiveX desde la ficha Desarrollador:

Al hacer doble clic sobre el botón se mostrará el código asociado al evento Click del

botón y debemos colocar una sola instrucción:

Page 78: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

78

Con esto será suficiente para que al momento de pulsar el botón de la hoja de Excel se

muestre el formulario VBA recién creado.

Page 79: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

79

24. EJERCICIO.

Crear el sgte, formulario.

NOMBRAMOS LAS HERRAMIENTAS.

Cajas de texto: text1 y text2

Botones de opción: OP1, OP2 y OP3

Page 80: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

80

Casillas: CHK1 y CHK2

Etiquetas: donde se mostrara el resultado.

LBPT, LBDES, LBPN

Botones: CMDCALCULAR, CMDNUEVO, CMDGUARDAR, CMDSALIR.

CODIGOS:

CMDCALCULAR.

Dim HR As Integer

HR = Val(TEXT2.Text)

If OP1 = True Then

pt = HR * 7

ElseIf OP2 = True Then

pt = HR * 6.4

Else

pt = HR * 6

End If

If CHK1 = True Then

Page 81: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

81

D1 = pt * 0.2

Else

D1 = 0

End If

If CHK2 = True Then

D2 = pt * 0.01

Else

D2 = 0

End If

If HR > 8 Then

DES = D1 + D2

PN = pt - DES

Else

MsgBox ("la hora trabajada es menor a 8")

End If

LBPT.Caption = pt

LBDES.Caption = DES

LBPN.Caption = PN

CMDGUARDAR

Dim HR, DES, PN As Integer

Dim FILA As Double

Dim AREA As String

If OP1 = True Then

AREA = ("PRODUCCION")

ElseIf OP2 = True Then

AREA = ("ALMACEN")

Else

AREA = ("LOGISTICA")

End If

HR = TEXT2.Text

DES = LBDES.Caption

PN = LBPN.Caption

Sheets("HOJA1").Select

FILA = 1 + ActiveSheet.UsedRange.Rows.Count

Cells(FILA, 1).Value = TEXT1.Text

Cells(FILA, 2).Value = HR

Cells(FILA, 3).Value = DES

Cells(FILA, 4).Value = PN

Cells(FILA, 5).Value = AREA

Page 82: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

82

CMDNUEVO

TEXT1.Text = ""

TEXT2.Text = ""

OP1.Value = False

OP2.Value = False

OP3.Value = False

CHK1.Value = False

CHK2.Value = ""

LBPT.Caption = ""

LBDES.Caption = ""

LBPN.Caption = ""

TEXT1.SetFocus

Por ultimo crear un botón en Excel para llamar al Formulario.

Page 83: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

83

SESION

Temas

Libros compartidos.

Logros

- El alumno puede compartir su trabajo en Excel

Con otros en la red.

- Puede realizar trabajos en equipo con Excel.

Page 84: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

84

25. LIBROS COMPARTIDOS.

Puede crear un libro compartido y colocarlo en una ubicación de red donde varias

personas puedan editar el contenido al mismo tiempo. Por ejemplo, si las personas de

su grupo de trabajo administran varios proyectos cada una y desea conocer el estado

de los proyectos de sus compañeros, el grupo puede utilizar un libro compartido para

realizar un seguimiento del estado de los proyectos. Todas las personas implicadas

podrán especificar la información sobre sus proyectos en el mismo libro.

Como propietario del libro compartido, puede administrarlo quitando usuarios del

mismo y resolviendo los cambios que estén en conflicto con otros. Cuando se hayan

incluido todos los cambios, puede dejar de compartir el libro.

Cuando comparta un libro, varios usuarios no pueden trabajar en el mismo archivo a la

vez. En su lugar, combine cambios de una o más copias de dicho libro en el nuevo que

ha compartido.

El proceso funciona de la siguiente forma: comparte un libro, y como parte del hecho

de compartirlo, lo guarda. Otro usuario copia el libro compartido y agrega sus datos a

la copia. Cuando hayan terminado, usted combina los datos de la copia de nuevo en el

original.

Una de las mejores maneras de compartir un libro es colocarlo en una ubicación de red

o una carpeta SkyDrive. De esa manera se permite a los demás usuarios obtener

acceso con facilidad al libro, copiarlo y agregar sus cambios.

Page 85: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

85

Compartir un libro - Cree un libro nuevo o abra un libro existente que desee poner disponible para

compartirlo.

- En la ficha Revisar, en el grupo Cambios, haga clic en Compartir libro

- En la pestaña Modificación del cuadro de diálogo Compartir libro, active la

casilla Permitir la modificación por varios usuarios a la vez. Esto también

permite combinar libros.

- En la pestaña Uso avanzado, seleccione las opciones que desea usar para

realizar un control de los cambios y actualizarlos y, a continuación, haga clic en

Aceptar.

Page 86: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

86

- En el cuadro Guardar como, especifique una ubicación de red en la Barra de

direcciones.

- Siga uno de los procedimientos siguientes:

- Si es un libro nuevo, escriba un nombre en el cuadro Nombre de archivo y haga

clic en Guardar.

- Si se trata de un libro existente, haga clic en Guardar.

- Envíe un mensaje de correo a la personas que van a compartir el libro. En el

mensaje, incluya la ubicación del archivo y pídales que cree una copia del libro

para su uso y conserve todas las copias de la carpeta con el original.

COMBINAR CAMBIOS

Una vez que sus compañeros de trabajo agreguen sus datos a sus copias del libro,

combine los cambios que realicen con los que haga usted. Sin embargo, antes de

empezar, tiene que agregar un comando a la barra de herramientas de acceso rápido.

AGREGAR UN COMANDO

- La barra de herramientas de acceso rápido se coloca en la esquina superior

izquierda de su pantalla de Excel. Haga clic en la flecha abajo del lado derecho

de la barra de herramientas y, a continuación, haga clic en Más comandos.

- En el cuadro de diálogo Opciones de Excel, abra la lista debajo de Comandos

disponibles en y seleccione Todos los comandos.

- Desplácese en esa lista hasta que vea Comparar y combinar libros.

- Seleccione dicho elemento y haga clic en Agregar.

- De esa manera se desplaza el comando encima de la lista de comandos de su

barra de herramientas de acceso rápido.

- Haga clic en Aceptar para cerrar el cuadro de diálogo Opciones de Excel y el

nuevo comando aparecerá en la barra de herramientas de acceso rápido.

Page 87: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

87

COMBINAR CAMBIOS.

- Abra la copia del libro compartido en el libro en el que desea combinar

cambios.

- En la barra de herramientas de acceso rápido, haga clic en Comparar y

combinar libros.

- Si se le solicita, guarde el libro.

- En el cuadro de diálogo Seleccionar archivos para combinar en el libro actual,

haga clic en la copia del libro que contiene los cambios que desea combinar y, a

continuación, haga clic en Aceptar.

RESOLVER CONFLICTOS DE CAMBIOS EN UN LIBRO COMPARTIDO

Cuando dos usuarios tratan de guardar cambios en la misma celda, se produce un

conflicto. Excel solo puede conservar uno de los cambios realizados en la celda.

Cuando el segundo usuario guarda el libro, Excel muestra el cuadro de diálogo

Resolución de conflictos a dicho usuario.

- En el cuadro de diálogo Resolución de conflictos, lea la información acerca de

cada cambio y los cambios conflictivos realizados por el otro usuario.

- Para mantener el cambio que ha realizado o el del otro usuario y continuar con

el siguiente cambio en conflicto, haga clic en Aceptar los míos o en Aceptar

otros.

- Para mantener todos los cambios restantes o todos los cambios de los demás

usuarios, haga clic en Aceptar todos los míos o Aceptar todos los otros.

- Para que sus cambios anulen todos los demás cambios sin mostrar de nuevo el

cuadro de diálogo Resolución de conflictos, haga lo siguiente:

En la ficha Revisar, en el grupo Cambios, haga clic en Compartir libro.

En la pestaña Uso avanzado, en En caso de cambios conflictivos entre

usuarios, haga clic en Prevalecen los cambios guardados y, a

continuación, haga clic en Aceptar.

- Para ver cómo usted u otros usuarios resolvieron los conflictos anteriores, haga

lo siguiente:

En el grupo Cambios de la pestaña Revisar, haga clic en Control de

cambios y, a continuación, en Resaltar cambios.

En la lista Cuándo, seleccione Todos.

Page 88: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

88

Desactive las casillas Quién y Dónde.

Active la casilla Mostrar cambios en una hoja nueva y haga clic en

Aceptar.

En la hoja de cálculo de historial, desplácese hacia la derecha para ver

las columnas Tipo de acción y Cambio perdedor.

Los cambios en conflicto que se han mantenido tienen el valor Prevalece en Tipo de

acción. Los números de fila incluidos en la columna Cambio perdedor identifican las

filas con información acerca de los cambios en conflicto que no se han mantenido,

incluidos los datos eliminados.

QUITAR UN USUARIO DE UN LIBRO COMPARTIDO

Si es necesario, puede desconectar usuarios de un libro compartido.

Importante: Antes de desconectar usuarios, asegúrese de que han acabado de usar el

libro. Si quita a algún usuario activo, se perderá todo el trabajo que el usuario no haya

guardado.

- En la ficha Revisar, en el grupo Cambios, haga clic en Compartir libro.

- Revise los nombres de los usuarios en la lista Los siguientes usuarios tienen

abierto este libro de la pestaña Modificación.

- Seleccione el nombre del usuario al que desea desconectar y haga clic en Quitar

usuario.

- Para eliminar la configuración de vista personal del usuario, haga lo siguiente:

Haga clic en Vista, en el grupo Vistas de libro y en Vistas personalizadas.

En la lista Vistas, seleccione la vista que desea quitar y haga clic en

Eliminar.

DEJAR DE COMPARTIR UN LIBRO

- En el libro compartido, haga clic en la opción Compartir libro en el grupo

Cambios de la pestaña Revisar.

- En la pestaña Modificación, asegúrese de que es el único usuario que aparece

en la lista Los siguientes usuarios tienen abierto este libro.

- Desactive la casilla Permitir la modificación por varios usuarios a la vez. Esto

también permite combinar libros.

Page 89: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

89

Si esta casilla no está disponible, deberá desproteger el libro. Para quitar la protección

del libro compartido, haga lo siguiente:

- Haga clic en Aceptar para cerrar el cuadro de diálogo Compartir libro.

- En la ficha Revisar, en el grupo Cambios, haga clic en Desproteger libro

compartido.

- Si se le solicita, escriba la contraseña y haga clic en Aceptar.

- En la ficha Revisar, en el grupo Cambios, haga clic en Compartir libro.

- En la pestaña Modificación, desactive la casilla Permitir la modificación por

varios usuarios a la vez. Esto también permite combinar libros.

- Cuando se le pregunte sobre los efectos que esta acción puede tener en los

demás usuarios, haga clic en Sí.

Page 90: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

90

SESION

Temas

Importar y exportar datos.

Importar una tabla desde un archivo HTML.

Orígenes de base de datos.

Logros

- El alumno tiene manejo avanzado de Excel.

- Puede trabajar con archivos de Access en Excel.

- Tiene conocimiento de Base de Datos.

Page 91: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

91

26. IMPORTAR O EXPORTAR DATOS.

Hay dos formas de importar datos de un archivo de texto en Microsoft Office Excel:

puede abrir el archivo de texto en Excel o puede importarlo como rango de datos

externos. Para exportar datos de Excel a un archivo de texto, utilice el comando

Guardar como.

Son dos los formatos de archivo de texto que se utilizan habitualmente:

Archivos de texto delimitado (.txt), en los que el carácter de tabulación (el código de

carácter ASCII 009) separa normalmente cada campo de texto.

Archivos de texto de valores separados por comas (.csv), en los que el carácter de

coma (,) separa normalmente cada campo de texto.

Puede cambiar el carácter separador que se utiliza tanto en los archivos de texto

delimitados como en los .csv. Esto puede ser necesario para asegurarse de que la

operación de importación o de exportación se realizará de la manera deseada.

Importación de objetos en Excel

Es relativamente fácil copiar objetos de otros programas (más aun con la herramienta

de copiado que ha sido mejorada en esta versión, permitiéndonos una vista previa de

la apariencia del objeto dentro del documento) y colocarlos dentro de Excel. En Excel,

los objetos incrustados aparecen como cuadros que flotan sobre la hoja de cálculo

(imágenes y gráficos). Podemos colocar estos cuadros en cualquier parte de su hoja de

cálculo.

El procedimiento incrustar está habilitado para todo tipo de objetos. Aunque

técnicamente se puede pegar un texto de un documento de Word dentro de un cuadro

flotante, esto no es necesario, ya que es un tipo de dato reconocido naturalmente por

la hoja de cálculo Excel. Sin embargo, otros tipos de objetos pueden tener más sentido.

Un ejemplo es un formato de imagen que no es compatible con Excel. Se puede pegar

esta imagen como un objetoincrustado al copiarlo desde el programa de dibujo

adecuado. El truco es utilizar la renovada herramienta de Excel ➝ Portapapeles Pegar

➝ comando Pegado especial, que funciona igual que lo hace en todos los demás

programas de Office, como Word.

Page 92: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

92

TRANSFERENCIA DE DATOS

Con incrustación y la vinculación de dos o más programas en conjunto, podemos crear

un documento compuesto. Sin embargo, a pesar que los diferentes objetos aparecen

en la hoja de cálculo, estos son propios de los diferentes programas. Por ejemplo: Al

incrustar una tabla de Excel en un documento de Word, no podemos arreglar cualquier

texto de la tabla utilizando el corrector gramatical que viene integrado en Word por

ser el objeto nativo de otra aplicación.

En estos casos, usted puede transferir los datos en bruto, en lugar de objetos. Así que

en lugar de incrustar un objeto de hoja de cálculo dentro de Word, puede

simplemente copiar los datos con formato de hoja de cálculo Excel y, a continuación

moverlos a Word. Usted perderá la capacidad de actualizar la información con Excel,

pero gana algunos beneficios, como poder editar los datos directamente en Word sin

necesidad de acceder a Excel.

Podemos editar los datos de forma rápida y cómoda. Esta capacidad es

particularmente importante si desea dar formato a los datos para que coincidan con el

resto del documento.

El archivo es de menor tamaño del que sería si utilizamos un objeto incrustado. Evitamos el modificar la información accidentalmente, si cambiamos la información

en la hoja de origen (como lo haría si se ha utilizado un objeto vinculado). Por estas razones, vale la pena considerar cuidadosamente si debemos copiar un

objeto de hoja de cálculo completa, o simplemente transferir la información que desea

utilizar.

hoja de cálculo completa, o simplemente transferir la información que desea utilizar.

EXPORTACIÓN DE TABLAS DE DATOS

El secreto para pegar datos desde la hoja en otro programa como Word, es el mismo

comando Pegado especial que se utiliza para crear objetos incrustados y vinculados.

Los siguientes pasos lo guiarán a través del proceso.

- Seleccione un rango de celdas de la hoja de cálculo, a continuación haga clic en:

Inicio➝ Portapapeles ➝Copiar. - Para obtener los mejores resultados, trate de evitar la selección de filas y

columnas vacías.

- Cámbiese al documento de destino y, a continuación, utilice el comando Pegado especial.

Page 93: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

93

- El cuadro de diálogo Pegado especial aparece. Este cuadro de diálogo muestra

lo que vería si se copia una selección de celdas de Excel a Word. Al copiar una

selección de celdas, el cuadro de diálogo Pegado especial le da varias opciones

sobre la forma en que desea importar los datos desde Excel.

- En el cuadro de diálogo Pegado especial, seleccione el tipo de objeto que desea insertar.

- Usted puede elegir cualquier formato compatible de la lista. Las opciones de

formato dependen del tipo de objeto que está insertando. Por ejemplo, Excel

permite pegar las celdas seleccionadas como un objeto de Excel, o convertirla

en texto sin formato, texto HTML, o incluso una imagen de baja resolución.

- También puede elegir la forma en Excel sincroniza los datos pegados con la

fuente.

- Seleccione la opción "Pegar vínculo" si desea crear un objeto vinculado de Excel

que se actualiza según los cambios que realice en la información de la fuente.

Seleccione la opción "Pegar" si desea crear un objeto incrustado que puede

modificar de forma independiente.

- Haga clic en Aceptar. - La información de Excel aparece en el formato elegido.

Page 94: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

94

27. IMPORTAR DATOS DELA WEB

Para importar datos procedentes de una página Web, elija Obtener datos externos en

el menú Datos y haga clic en Nueva consulta Web. Debe tener acceso al World Wide

Web (World Wide Web o WWW: que es una rama multimedia de Internet que

presenta no sólo texto, sino también gráficos, sonido y vídeo. En la Web, los usuarios

pueden saltar fácilmente de un elemento a otro, de una página a otra o de un sitio a

otro utilizando hipervínculos), a través de la intranet de su organización o un módem

instalado en el equipo o la red, o bien puede realizar una consulta de código de origen

HTML o XML local.

Ejemplo: Importar una tabla de una página Web, en este caso la tabla del

descentralizado de futbol local.

- Ir a la ficha Datos y del grupo Obtener datos externos elegimos: Desde Web.

- En una nueva ventana, ingresamos la dirección de la página donde

importaremos la tabla. El programa carga la página Web y nos muestra todos

los objetos importables colocando una pequeña flecha de color amarillo que

usaremos para hacer clic sobre el objeto a importar.

Page 95: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

95

Y Hacemos clic en el botón importar.

- Aparece una ventana pidiéndonos la celda donde colocaremos la tabla a

importar:

- Hacemos clic en el botón Aceptar y aparecerá lo siguiente:

Apareciendo los datos:

Page 96: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

96

28. ORIGENES DE BASE DE DATOS.

Puede utilizar Microsoft Office Excel para crear y editar conexiones a orígenes de datos

externos almacenados en un libro o en un archivo de conexión. En el cuadro de diálogo

Conexiones del libro, puede administrar fácilmente estas conexiones, incluida su

creación, modificación y eliminación.

CONECTARSE A DATOS EXTERNOS.

La ventaja principal de conectarse con datos externos consiste en que puede analizar

periódicamente estos datos en Microsoft Office Excel sin tener que copiar

repetidamente los datos, una operación que puede ser larga y provocar errores. Tras

conectarse con los datos externos, también puede actualizar automáticamente los

libros de Excel desde el origen de datos original siempre que el origen de datos se

actualice con información nueva.

- En la ficha Datos, en el grupo Obtener datos externos, haga clic en Conexiones

existentes.

En el cuadro de diálogo Conexiones existentes de la lista desplegable Mostrar, siga uno

de los procedimientos siguientes:

Page 97: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

97

Para mostrar todas las conexiones, haga clic en Todas las conexiones. Este es el valor

predeterminado.

Para mostrar sólo la lista de conexiones recientemente utilizadas, haga clic en

Conexiones en este libro.

Esta lista se crea a partir de conexiones que el usuario ha definido, ha creado desde el

cuadro de diálogo Seleccionar origen de datos del Asistente para la conexión de datos,

o ha seleccionado previamente como conexión desde este cuadro de diálogo.

Para mostrar sólo las conexiones disponibles en el equipo, haga clic en Archivos de

conexión en este equipo.

Esta lista se crea a partir de la carpeta Mis archivos de origen de datos, normalmente

almacenada en la carpeta Mis documentos.

Para mostrar solo las conexiones disponibles de un archivo de conexión al que es

posible tener acceso desde la red, haga clic en Archivos de conexión en la red.

IMPORTAR DATOS DE ACCESS

No necesitas conocer mucho sobre Access ya que Excel ha simplificado grandemente

este proceso para los usuarios. Lo primero que debemos hacer es ir a la ficha Datos y

pulsar el botón Desde Access.

Este comando mostrará el cuadro de dialogo Seleccionar archivos de origen de datos

en donde deberás especificar el archivo Access que deseas abrir:

Page 98: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

98

De inmediato Excel mostrará otro cuadro de diálogo que enlistará las tablas contenidas

en el archivo Access.

Una tabla es el lugar en donde se almacenan los datos en Access. Podríamos pensar

que una tabla de Access es similar a una hoja de Excel porque también está formada

por columnas y filas. Y aunque el concepto de tabla es un poco más avanzado que una

hoja de Excel, es suficiente la comparación por ahora para saber que ahí se encuentra

almacenada la información de Access.

Una vez seleccionada la tabla de Access que deseamos importar debemos pulsar el

botón Aceptar y ahora Excel nos preguntará por algunas configuraciones de

importación:

Page 99: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

99

En primer lugar podemos elegir traer los datos desde Access como una Tabla de datos

o también como un Informe de tabla dinámica. Así mismo podemos especificar la celda

de nuestra hoja donde queremos que se inserten los datos o simplemente especificar

que se inserten en una nueva hoja. Al pulsar el botón Aceptar Excel realizará la

importación de los datos.

La información de la tabla de Access será mostrada dentro de la hoja de Excel en el

mismo orden en que aparecen los datos en la tabla.

Page 100: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

100

SESION

Ejercicios.

Page 101: Manual MS Excel 2013 Avanzado (1)

Ms. Excel Avanzado 2013

101

CREAR FORMULARIO “INGRESO”

Debe crear un marcador de ingreso:

- Nombre y apellidos

- DNI

- Hora de ingreso

- fecha

El formulario debe de contener los datos pedidos, y cuando se presione el botón

ingresar, deberá de enviarse a la hoja de cálculo.

En la hoja de cálculo debe de grabarse:

- Nombre y apellidos

- DNI

- Hora de ingreso

- fecha

Ejemplo: