Sesion 3

Curso de Excel avanzado 2007 Sesión 3

1

SESIÓN 3 .............................................................................................................................................. 1

LECCIÓN 7 ACCESO A DATOS EXTERNOS I ....................................................................................... 1

LECCIÓN 8 ACCESO A DATOS EXTERNOS II ...................................................................................... 9

LECCIÓN 9 TABLAS Y GRÁFICAS DINÁMICAS ................................................................................. 16

SESIÓN 3

LECCIÓN 7 ACCESO A DATOS EXTERNOS I

En muchas ocasiones tenemos la necesidad de trabajar en Excel con datos procedentes de otras

aplicaciones. Tenemos dos alternativas:

o Introducir de nuevo los datos en un libro de trabajo, con el consumo de tiempo

que ello implica más el riesgo de introducir erróneamente los datos al

introducirlos manualmente.

o Utilizar algunas de las herramientas disponibles en Excel para importar datos.

Para importar datos externos a Excel disponemos básicamente de dos opciones:

o Utilizar el portapapeles de Windows, es decir, copiar los datos de la aplicación

externa y pegarlos en una hoja de Excel.

o Importar datos de otro archivo que no tiene que ser necesariamente del formato

Excel.

La primera opción es la más directa, pero tiene el contrapunto de ser más laboriosa y tediosa.

La segunda opción es más rápida, pero pueden surgir problemas de compatibilidad dependiendo

del formato del archivo a importar.

El problema fundamental de la importación de datos externos, hemos dicho que es debido al

formato de los propios archivos, esto quiere decir que cada aplicación genera un archivo con un

formato propio para identificar mejor el contenido de los datos, por ejemplo Excel al generar un

archivo .xls no solamente guarda los datos que hemos introducido sino que lo guarda con un

formato especial para interpretar el documento en su plenitud, de esta manera sabe exactamente

dónde están las fórmulas, qué formato estético tiene el documento, etc.

Además de esto, al importar datos de una aplicación externa debemos tener en cuenta que

pueden surgir los siguientes fallos:

o Algunas de las fórmulas no ajustan correctamente.

o El formato no se ajusta al original.

o Las fórmulas y funciones que no entiende no las copia.


2

Pese a todos estos contratiempos, siempre es mejor intentar realizar una importación y después

comprobar si todo ha salido correctamente.

Con lo anterior pondremos en practica un par de ejemplos donde se compradera la primera fase

del tema datos externos.

Importar un archivo de texto conectándose a él

Practica 24

Puede importar datos de un archivo de texto en una hoja de cálculo existente como un rango de

datos externos.

Abrir libro de la lección 7 denominado Ejemplo 7_1.XLSX

Haga clic en la celda en la que desea poner los datos del archivo de texto. (Celda A2)

En la ficha Datos, en el grupo Obtener datos externos, haga clic en Desde texto.

En el cuadro de dialogo buscar o seleccionar el archivo de texto “TXT” llamado

“EMPLEADOS” ubicado en la carpeta de la lección 7, como siguiente paso presionar el

botón de importar parte inferior del cuadro de dialogo.


3

A continuación se presentara un cuadro de dialogo del cual seleccionaremos los puntos

marcados en la secuencia de imágenes siguientes.

1

2

3

4


4

Al finalizar encontraremos como una tabla en lazada hacia y con un archivo de texto de

Word.

Para realizar un comprobación realice el siguiente ejercicio

5

6

7


5

Practica 25

Abrir de la carpeta LECCIÓN 7 y seleccionar el archivo de texto llamado Empleados.TXT

Para ello oprimimos el botó derecho del ratón y seleccionar el comando de ABRIR CON.

Y tomar la opción de la lista el programa de WORD 07

Se abrirá un cuando de dialogo donde seleccionaremos las siguientes opciones.

1

2


6

Al realizar lo anterior, se abrirá Microsoft Word dando como resultado un listado de

información al cual ya podremos agregar la siguiente información como un registró

nuevo.

Pablo Limón, Velador, Secundaria, 56 años, $12547

Guardamos y cerramos el documento.

NOTA! Si va a estar realizado cambios continuos en la lista. Esta puede quedarse ABIERTA y

continuar capturando la info.

Hecho lo anterior regresemos al libro de Excel ejemplo 7_1

Vamos a la ficha de datos

Al grupo de conexiones

En e botón de ACTUALIZAR

Y seleccionar ACTUALIZAR TODO

Se abrirá un cuadro de dialogo, solicitándole reingresar el archivo actualizado

Abrirá automáticamente en la ubicación del archivo.

Lo seleccionamos y oprimimos el botón de ACEPTAR.

El resultado se estará mostrando en nuestra tabla datos, apareciendo la información

capturada en Word.


7

Conectar con datos de Access desde Excel

Para traer datos de Access que se puedan actualizar en Excel, puede crear una conexión,

almacenada normalmente en un archivo de conexión de datos de Office (.odc), con la base de

datos de Access y recuperar todos los datos de una tabla o consulta. La ventaja principal de crear

una conexión con los datos de Access en lugar de importarlos es que puede analizar

periódicamente estos datos en Excel sin tener que copiarlos o exportarlos constantemente desde

Access. Tras conectarse con los datos, también puede actualizar automáticamente los libros de

Excel desde la base de datos de Access original siempre que la base de datos se actualice con

información nueva. Por ejemplo, tal vez desee actualizar un informe presupuestario de Excel que

distribuye cada mes para que contenga los datos del mes en curso.

Practica 26

Para realizar esta práctica utilizaremos el ejercicio anterior en la hoja llamada DATOS DE ACCES

Haga clic en la celda en la que desea colocar los datos de la base de datos de Access.

(celda A2).

En la ficha Datos, en el grupo Obtener datos externos, haga clic en Desde Access.

Busque en la carpeta de esta lección el archivo Emeplados.ACCDB y haga doble clic para

enlazarla.

En el cuadro de diálogo que apareció, Seleccionar tabla Clientes y luego haga clic en

Aceptar.


8

En Seleccione cómo desea ver estos datos en el libro, siga uno de los procedimientos

siguientes:

Para ver los datos como una tabla, seleccione Tabla.

En ¿Dónde desea situar los datos?, siga uno de los procedimientos siguientes: Celda

seleccionada de un inicio

Haga clic en Aceptar.

Con esto estaremos importando información desde una base de datos de ACCES.


9

LECCIÓN 8 ACCESO A DATOS EXTERNOS II

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.

IMPORTANTE Las conexiones a datos externos pueden estar deshabilitadas en el equipo.

Para conectarse a los datos al abrir un libro, deberá habilitar las conexiones de datos en la

barra Centro de confianza, o bien colocar el libro en una ubicación de confianza. Para

obtener más información, vea Crear, quitar o cambiar una ubicación de confianza para los

archivos, Agregar, quitar o ver un editor de confianza y Ver la configuración de seguridad

en el Centro de confianza.

Conectarse con datos externos (importar) Puede crear o ejecutar una consulta Web (consulta Web: consulta que recupera datos

almacenados en una intranet o en Internet.) para recuperar texto o datos de una página

Web. Las páginas Web suelen contener información que es perfecta para analizar en Excel.

Según sus necesidades, puede recuperar datos que se puedan actualizar. Es decir, puede

actualizar los datos en Excel con los datos más recientes de la página Web. O bien puede

recuperar los datos de una página Web y hacer que permanezcan invariables en la hoja.

Practica 27

Para realizar consulta e importación de datos de un sito WEB lo podemos hacer maneras

diferentes.

Para esto abrimos el libro de Excel ejemplo8.XLXS ubicado en la carpeta de esta lección.

Pasos

1. Seleccionamos la celda E8

2. Pasamos a un explorador web y podemos visitar el siguiente sitio.

http://www.banxico.org.mx/portal-mercado-cambiario/index.html



10

3. Al llegar al sito, seleccionamos y copiamos el valor del dólar y lo pegamos en el libro

abierto en la celda E8. Se observara que en la parte inferior de la celda se muestra el icono

de opción es de pegado, señalado en la siguiente imagen

4. Oprimimos este botón para seleccionar la función de CREAR CONSULTA WEB

ACTUALIZADA. Esto nos permitirá que la celda se actualice cada que nosotros medemos

llamar dicha acción.

5. Al seleccionar esta opción, nos enlazaremos al sitio para dejar enlazada dicha celda para

su posterior actualización. Esta opción nos mostrar con una etiqueta de fondo amarillo y

un flecha aquellos dato que podamos seleccionar.


11

6. Posterior a esto buscamos el valor copiado y damos un clic a la etiqueta que este a su

izquierda para quedar seleccionada verificándola cuando esta se convierta en verde y

palomeada. Como la siguiente ilustración.

7. Posterior mente oprimimos importar para que esto quede enlazado.

8. Una vez enlazados podemos hacer operaciones correspondientes para determinar el valor

actual en la celda G8.

Practica 28

Otra forma de aplicarlo será seccionando la celda I8 la misma celda y seguir los siguientes pasos.

1. Ir a la ficha de dato

2. Seleccionar del Bloque DATOS EXTERNOS el icono de DESDE WEB


12

3. Donde nos mostrara la siguiente pantalla.

4. En ella estará la pagina principal de su navegador y partiendo podemos ir al pagina del

banco de México y extraer la información necesario. (Pueden copiar y pegar el siguiente

enlace)


5. Una vez que nos muestre el resultado podremos seleccionar la casilla amarilla para

importar el dato correspondiente al valor del EURO. Posterior a la selección oprimir

importar.

6. Seguido de la función anterior no pedirá la ubicación donde deseamos el resultado y

notara que ya se encuentra visible la ubicación seleccionada al principió de esta practica.

Así que oprimíos aceptar.

7. Y el resultado se muestre así en la celda seleccionada



13

Practica 29

En esta practica extraeremos el valor del dólar con respecto días anteriores. Y para esto

cambiamos a la hoja Divisas 2 y seguir los siguientes pasos.

1. Vamos a la celda D8, continuamos visitando la ficha datos, en el bloque de DATOS

EXTERNOS, en el icono de DESDE LA WEB.

2. Dentro de esta ventana podremos hacer una búsqueda web para dirigirnos al banco de

México y consultar el tipo de cambio. O bien copiar y pegar el siguiente link.

http://www.banxico.org.mx/tipcamb/tipCamMIAction.do?idioma=sp

3. Ahí marcamos la tabla para descargar todo ese segmento de información; Oprimimos

importar.

4. Al realizar el paso anterior obtendremos como resultado la siguiente tabla enlazada.

http://www.banxico.org.mx/tipcamb/tipCamMIAction.do?idioma=sp


14

5. Agregar un grafico para el cual la información se que se utilizara será la que muestra en la

columna D y G (modelo al gusto del grafico)

6. Un vez que tenemos el resultado resolvemos por el método de aplicar formula, la hoja de

calculo denominada electrónica en la celda F6 CON LA SIGUEINTE FORMULA.

=E8/'Divisas 2'!$G$10

NOTA1. Todas las practicas anteriores se puede actualizar los datos cada vez que necesitemos

siguiendo los pasas que a continuación se describe. IR A DATO, DATOS EXTERNOS, ACTUALIZAR Y

SELECCIONAR ACTUALIZAR TODOS.

NOTA 2. Lo anterior puede tardar un segundos o minutos mientras consulta si ay cambios en le

servidor de la información (banco de México)

Practica 30

Importaremos de sito del banco de México los valores monetarios de algunos países que se

muestran en la página oficial visitada.

1. Para esto cambiarnos a la hoja de datos llamada OTRAS DIVISAS

2. Nos colocamos en la celda A5 para IR A DATO, DATOS EXTERNOS, DESDE LA WEB

3. Para colocar el siguiente link en la barra de navegación

http://www.banxico.org.mx/SieInternet/consultarDirectorioInternetAction.do?accion=consultarC

uadroAnalitico&idCuadro=CA113&sector=6&presentacionCompacta=true&locale=es

http://www.banxico.org.mx/SieInternet/consultarDirectorioInternetAction.do?accion=consultarCuadroAnalitico&idCuadro=CA113&sector=6&presentacionCompacta=true&locale=es

http://www.banxico.org.mx/SieInternet/consultarDirectorioInternetAction.do?accion=consultarCuadroAnalitico&idCuadro=CA113&sector=6&presentacionCompacta=true&locale=es


15

4. Posterior a ello seleccionar la casilla amarilla del lado izquierdo del titulo País 1/ para

después presionar el botón de importar, seleccionar la celda destino A5 y pre4sionar

aceptar.

5. Par obtener el resultado como se muestra a continuación.

6. Guarda el libro de trabajo para su comparación con el libro llamado COMPARATIVO 8 que

se encuentra en la carpeta de la lección.


16

LECCIÓN 9 TABLAS Y GRÁFICAS DINÁMICAS Las Tablas Dinámicas son una forma alternativa de presentar o resumir los datos de una lista, es

decir, una forma de ver los datos desde puntos de vista diferentes.

El nombre Tabla Dinámica se debe a que los encabezados de fila y columna de la lista pueden

cambiar de posición y también pueden ser filtrados.

Con las Tablas Dinámicas también podremos preparar los datos para ser utilizados en la

confección de gráficos.

Ventajas y Desventajas de las Tablas Dinámicas Las Tablas Dinámicas en Excel deben ser una de las herramientas más usadas de este software y

por las que más nos preguntan a la hora de ver cuánto sabemos de Excel.

Esta herramienta tiene muchas ventajas, muy fácil de usar, rápidas, pueden trabajar con muchos

datos, permite incorporar gráficos entre otras. Pero también tiene limitaciones que es importante

conocer.

Principales Ventajas de las Tablas Dinámicas

Fácil de usar: este debe ser su principal atributo, con solo algunos clicks puedo resumir

una gran cantidad de datos y mostrarlos de la manera en que los necesite. Independiente

del nivel de la consulta que esté haciendo, con muy pocas otras herramientas puedo en

dos clicks, mostrar todas las ventas de un determinado vendedor, por poner un ejemplo.

Dinámicas: justamente haciendo referencia a su nombre; el hecho de que sea tan fácil

agregar y quitar dimensiones hacen que sea la herramienta ideal para, por ejemplo, llevar

los datos a una reunión y comenzar a sacar información "on demand" o trabajar con los

datos en equipo.

Trabajo en varias dimensiones: Siempre se habla de las fórmulas "tridimensionales"

cuando se hace referencia a que Excel puede trabajar en fila, columna y hoja. En Tablas

Dinámicas podemos trabajar con 3 dimensiones de datos: Fila, Columna y Filtro (o Página

como se llamaba hasta Excel 2007). De esta forma es posible agregar un nivel más de

"resumen" que cuesta conseguirlo con fórmulas normales.

Múltiples vistas y cálculos: Es muy sencillo cambiar las visualizaciones de los datos y los

estadísticos resumen, con un par de clicks puedo: cambiar la función de agrupación

(SUMA, CONTAR, PROMEDIO) o bien cambiar la forma en que se muestran los datos:

como % del total, de la fila o incluso como diferencias porcentuales. Además podemos

agregar campos o elementos calculados para complementar el análisis.


17

Las Desventajas de las Tablas Dinámicas

Poco Flexibles: Si bien hay muchas posibilidades, las Tablas Dinámicas no son tan flexibles

como uno quisiera. Por ejemplo, no puedo en una misma tabla dinámica agrupar datos y

por otro lado generar un elemento calculado. Necesariamente hay que generar una nueva

Tabla Dinámica, pero inclusive con un origen de datos nuevos (Ver punto 3).

"Monolíticas": En general la Tabla Dinámica genera una especie de "aplicación" a la que

cuesta mucho acceder a sacar datos. La opción de trabajar sin las funciones de GETPIVOT

permiten de alguna manera poder acceder a los datos, pero al momento de cambiar la

tabla dinámica esa referencia se volverá sin sentido. Hay que recurrir a las funciones de

Importación de Datos Dinámicos, que deben ser las fórmulas más complejas de entender

de todas (según mi parecer).

Memoria de Datos: Excel trata siempre de optimizar recursos y hay veces en que esta

optimización nos trae repercusiones: en Excel 2003 era común tener dos o más Tablas

Dinámicas, modificar una y replicar lo mismo en todo el resto. En Excel 2007, al parecer

eso no ocurre, pero sí pasa que las Tablas quedan linkeadas a la fuente, cualquier Informe

Dinámico que haga sobre esos datos va a compartir características con otros generados.

Procesamiento y Cálculos: Si bien los elementos y campos calculados son una herramienta

muy potente, no queda tan claro (por lo menos a primera vista) de como operan y muchas

veces pueden llevar a confusión a usuarios no tan expertos. Además hay cálculos que

matemáticamente pueden parecer triviales, pero no son soportados por la herramienta.

La comprensión cabal de este tema se obtiene con la práctica y es así como se verá que es uno de

los tópicos más potentes de Excel, principalmente en las versiones más recientes.

Practica 31 Para comprender la apelación procederemos abrir el archivó que se encuentra en la carpeta de la

lección denominado Ejemplo9.XLSX

Una empresa de exportación de máquinas agrícolas tiene la siguiente tabla en una Hoja de Excel

2007.donde figuran los datos del 1º trimestre del año. A partir de ella se quiere crear una nueva

tabla en la que se informe la cantidad de maquinarias exportadas y el detalle de cuantas se

vendieron de cada una.


18

Pasos

1. Nos ubicamos en cualquier celda de la tabla, luego vamos a la pestaña "insertar" panel

"Tablas"

2. Pulsamos en "Tabla dinámica", al hacer esto aparece un menú en el que tenemos las

opciones de "Gráfico dinámico" y "Tabla dinámica" , pulsaremos en este último como se

puede ver.

3. A continuación aparece el panel "Crear tabla dinámica"

4. En este caso el rango de la tabla base queda automáticamente seleccionado, incluyendo

los rótulos, también seleccionamos "Nueva hoja de cálculo" y pulsamos en aceptar y

automáticamente se crea la Hoja4 en la que se destacan:


19

5. Una nueva cinta de opciones denominada "Herramienta de tabla dinámica"

6. En el panel llamado "Lista de campos de tabla dinámica" que es una novedad de Excel

2007 y que tiene un rectángulo en la parte superior, donde se ubican los campos o rótulos

de la tabla de origen, también hay cuatro rectángulos, en la parte inferior, denominados "

Filtro de informe", "Rótulos de columna", "Rótulos de fila" y "Valores" donde irán

apareciendo los rótulos de la tabla a medida que los seleccionemos en la parte superior en

forma de botones como el que se nuestra.


20

7. Como se ve, hasta este momento, tiene las

casillas de verificación de rótulos sin marcar ,

pues bien, es justamente seleccionar las

casillas "MAQUINA" y "CANTIDAD" lo

debemos hacer en el próximo paso

8. Observar que aparecen automáticamente 2 botones. Listo ya tenemos la primera tabla

con las respuestas pedidas recuadradas en rojo.


21

Podemos querer saber el detalle de las máquinas que fueron exportadas y por cual vendedor.

9. En este caso tendremos que seleccionar la casilla del rótulo VENDEDOR y en la nueva Hoja

aparece una tabla y el panel "Lista de campos de tabla dinámica"

10. Se ve que en los rectángulos, mas precisamente en el llamado Rótulo de fila, aparece un

nuevo botón, el botón VENDEDOR en forma simultánea a la la selección de la casilla de

verificación VENDEDOR.

La tabla responde a lo que queremos saber, pero le podemos dar otro aspecto arrastrando

el botón VENDEDOR al rectángulo "Rótulo de columna"


22

11. Y la tabla queda como la que esta abajo , luego de haberle dado algo de formato

12. Si agregamos el campo país. Nuestra tabla se despliega un poco mas generando como

resultado la siguiente imagen.


23

13. Podemos cambiar de ubicación el campo maquina hacia el filtro del informe, esto para

tener la estadística en general de países y vendedores para todas las maquinas. El

resultado seria el siguiente.

14. Guarda el ejemplo para su posterior análisis.

Practica 32 En la hoja ESTADISTICA con base en estos datos iniciaremos una nueva tabla dinámica.

Pasos.

1. Seleccionamos el rango B5:G19, después nos vamos a insertar, en el bloque de tablas, en

el comando de tablas; aceptando la indicación del cuadro de dialogo que se nos presente.

2. Posterior a esto en las herramientas de tabla seleccionaremos la opción siguiente

demostrada en la imagen.


24

3. Seleccionar los dato como se muestran en la imagen siguiente para analizar el informe

generado.

4. Guarda el archivo

Sesion 3

Documents

Transcript of Sesion 3