Webinar 05.06.14: Reduzca el coste de contact center con comunidades de soporte
INDICE TEMA 3: INTRODUCCION DE DIFERENTES TIPOS DE...
Transcript of INDICE TEMA 3: INTRODUCCION DE DIFERENTES TIPOS DE...
INDICE
TEMA 3: INTRODUCCION DE DIFERENTES TIPOS DE INFORMACION
1.1 DATOS ALFANUMERICOS, NUMERICOS, FECHA, ETC.
1.2 COMO USAR LAS HOJAS DE TRABAJO
1.3 INSERCION DE HOJAS
1.4 ELIMINACION DE HOJAS
1.5 COPIA DE HOJAS Y MOVIMIENTO DE HOJAS
1.6 TRABAJO CON RANGOS
1.7 INSERCION DE CELDAS, FILAS Y COLUMNAS.
INTEGRANTES DEL EQUIPO
1.- AMEYALLI GABRIELA ZARATE MARTINEZ
2.- ANDREA PEREZ MALDONADO
3.- IXCHEL BERENICE MERLIN CANSECO
4.- DULCE JAZMIN PEÑA SANTIAGO
5.- ERIKA LOURDES TOLEDO RASGADO
6.- VERONICA PATRICIA AGUDO RIOS
7.- ROSARIO BERENICE RAMIREZ GARCIA
8.- MARIBEL ZULEYMA GONZÁLEZ NIÑO
USO DE LAS FUNCIONES DE EXCEL PARA LA IMPORTACIÓN DE DATOS
Esta unidad es la unidad una de las más importantes del curso, pues en su comprensión y manejo está la base de Excel. Qué es una hoja de cálculo sino una base de datos que utilizamos con una serie de fórmulas para evitar tener que recalcular por cada cambio que hacemos. Por eso esta unidad es fundamental para el desarrollo del curso y la buena utilización de Excel. Vamos a profundizar en el manejo de funciones ya definidas por Excel 2010 para agilizar la
creación de hojas de cálculo, estudiando la sintaxis de éstas así como el uso del asistente para
funciones, herramienta muy útil cuando no conocemos muy bien las funciones existentes o la
sintaxis de éstas.
Introducir funciones Una función es una fórmula predefinida por Excel (o por el usuario) que opera con uno o más valores y devuelve un resultado que aparecerá directamente en la celda o será utilizado para calcular la fórmula que la contiene. La sintaxis de cualquier función es: Nombre _función (argumento 1; argumento 2;...; argumento N) Siguen las siguientes reglas: - Si la función va al comienzo de una fórmula debe empezar por el signo =.
- Los argumentos o valores de entrada van siempre entre paréntesis. No dejes espacios antes o después de cada paréntesis.
- Los argumentos pueden ser valores constantes (número o texto), fórmulas o funciones.
- Los argumentos deben de separarse por un punto y coma; Ejemplo: =SUMA (A1:C8) Tenemos la función SUMA () que devuelve como resultado la suma de sus argumentos. El operador ":" nos identifica un rango de celdas, así A1:C8 indica todas las celdas incluidas entre la celda A1 y la C8, así la función anterior sería equivalente a: =A1+A2+A3+A4+A5+A6+A7+A8+B1+B2+B3+B4+B5+B6+B7+B8+C1+C2+C3+C4+C5+C6+C7+C8 En este ejemplo se puede apreciar la ventaja de utilizar la función. Las fórmulas pueden contener más de una función, y pueden aparecer funciones anidadas dentro
de la fórmula.
Ejemplo: =SUMA (A1:B4)/SUMA (C1:D4) Existen muchos tipos de funciones dependiendo del tipo de operación o cálculo que realizan. Así hay funciones matemáticas y trigonométricas, estadísticas, financieras, de texto, de fecha y hora, lógicas, de base de datos, de búsqueda y referencia y de información. Para introducir una fórmula debe escribirse en una celda cualquiera tal cual introducimos cualquier texto, precedida siempre del signo =. 6.2. Autosuma y funciones más frecuentes Una función como cualquier dato se puede escribir directamente en la celda si conocemos su sintaxis, pero Excel dispone de herramientas que facilitan esta tarea.
En la pestaña Inicio o en la de Fórmulas encontrarás el botón de Autosuma que
nos permite realizar la función SUMA de forma más rápida
Con este botón tenemos acceso también a otras funciones utilizando la flecha de la derecha del botón. Al hacer clic sobre ésta aparecerá la lista desplegable de la imagen. Y podremos utilizar otra función que no sea la Suma, como puede ser Promedio (calcula la media aritmética), Cuenta (cuenta valores), Máx. (Obtiene el valor máximo) o Mín. (obtiene el valor mínimo). Además de poder accesar al diálogo de funciones a través de Más Funciones.... Para utilizar éstas opciones, asegúrate de que tienes seleccionada la celda en que quieres que se
realice la operación antes de pulsar el botón.
Insertar función Para insertar cualquier otra función, también podemos utilizar el asistente. Si queremos Introducir una función en una celda: Situarse en la celda donde queremos introducir la función. Hacer clic en la pestaña Fórmulas
Elegir la opción Insertar función.
O bien, hacer clic sobre el botón de la barra de fórmulas. Aparecerá el siguiente cuadro de diálogo Insertar función:
Excel 2010 nos permite buscar la función que necesitamos escribiendo una breve descripción
de la función necesitada en el recuadro Buscar una función: y a continuación hacer clic sobre el
botón, de esta forma no es necesario conocer cada una de las funciones que
incorpora Excel ya que el nos mostrará en el cuadro de lista Seleccionar una función: las
funciones que tienen que ver con la descripción escrita.
Para que la lista de funciones no sea tan extensa podemos seleccionar previamente una categoría del cuadro combinado O seleccionar una categoría:, esto hará que en el cuadro de lista sólo aparezcan las funciones de la categoría elegida y reduzca por lo tanto la lista. Si no estamos muy seguros de la categoría podemos elegir Todas. En el cuadro de lista Seleccionar una función: hay que elegir la función que deseamos haciendo clic sobre ésta. Observa como conforme seleccionamos una función, en la parte inferior nos aparecen los distintos argumentos y una breve descripción de ésta. También disponemos de un enlace Ayuda sobre esta función para obtener una descripción más completa de dicha función. A final, hacer clic sobre el botón Aceptar. La ventana cambiará al cuadro de diálogo Argumentos de función, donde nos pide introducir los
argumentos de la función: Este cuadro variará según la función que hayamos elegido, en nuestro
caso se eligió la función SUMA ().
En el recuadro Número 1 hay que indicar el primer argumento que generalmente será una celda
o rango de celdas tipo A1:B4. Para ello, hacer clic sobre el botón para que el cuadro se haga
más pequeño y podamos ver toda la hoja de cálculo, a continuación seleccionar el rango de
celdas o la celda deseadas como primer argumento (para seleccionar un rango de celdas haz
clic con el botón izquierdo del ratón sobre la primera celda del rango y sin soltar el botón arrástralo
hasta la última celda del rango) y pulsar la tecla INTRO para volver al cuadro de diálogo.
En el recuadro Número 2 habrá que indicar cuál será el segundo argumento. Sólo en caso de que existiera. Si introducimos segundo argumento, aparecerá otro recuadro para el tercero, y así sucesivamente. Cuando tengamos introducidos todos los argumentos, hacer clic sobre el botón Aceptar. Si por algún motivo insertáramos una fila en medio del rango de una función, Excel expande automáticamente el rango incluyendo así el valor de la celda en el rango. Por ejemplo: Si tenemos en la celda A5 la función =SUMA (A1:A4) e insertamos un fila en la posición 3 la fórmula se expandirá automáticamente cambiando a =SUMA (A1:A5). Utilizar Expresiones como argumentos de las Funciones Excel permite que en una función tengamos como argumentos expresiones, por ejemplo la suma de dos celdas (A1+A3). El orden de ejecución de la función será primero resolver las expresiones y después ejecutar la función sobre el resultado de las expresiones. Por ejemplo, si tenemos la siguiente función =Suma ((A1+A3) ;(A2-A4)) donde: A1 vale 1 A2 vale 5 A3 vale 2 A4 vale 3 Excel resolverá primero las expresiones (A1+A3) y (A2-A4) por lo que obtendremos los valores 3 y 2 respectivamente, después realizará la suma obteniendo así 5 como resultado. Utilizar Funciones como argumentos de las Funciones Excel también permite que una función se convierta en argumento de otra función, de esta forma
podemos realizar operaciones realmente complejas en una simple celda. Por ejemplo =MAX
(SUMA (A1:A4);B3) , esta fórmula consta de la combinación de dos funciones, la suma y el valor
máximo. Excel realizará primero la suma SUMA (A1:A4) y después calculará el valor máximo entre
el resultado de la suma y la celda B3.
Funciones de fecha y hora De entre todo el conjunto de funciones, en este apartado estudiaremos las funciones dedicadas al tratamiento de fechas y horas. Y estas son todas las posibles funciones ofrecidas por Excel. En varias funciones veremos que el argumento que se le pasa o el valor que nos devuelve es un
"número de serie". Pues bien, Excel llama número de serie al número de días transcurridos desde
el 0 de enero de 1900 hasta la fecha introducida, es decir coge la fecha inicial del sistema como el
día 0/1/1900 y a partir de ahí empieza a contar, en las funciones que tengan núm_de_serie como
argumento, podremos poner un número o bien la referencia de una celda que contenga una fecha.
Funciones de texto
Una hoja de cálculo está pensada para manejarse dentro del mundo de los números, pero Excel también tiene un conjunto de funciones específicas para la manipulación de texto. Estas son todas las funciones de texto ofrecidas por Excel.
Funciones de búsqueda
En una hoja de Excel es muy importante coger los datos correctos para trabajar con las fórmulas diseñadas. Por eso existe una agrupación de funciones específicas para realizar búsquedas de datos. Comprendamos qué es en sí una búsqueda, cuando queremos encontrar alguna información de algo no buscamos directamente por lo que buscamos pues lo desconocemos, realizamos una búsqueda de una propiedad o algo similar que conocemos que puede tener lo que buscamos. Por ejemplo, si buscamos a una persona, describimos su aspecto físico, si buscamos el nº de teléfono de un restaurante, buscamos en la guía de teléfonos por el nombre del restaurante. Normalmente el dato que queremos encontrar no lo conocemos por eso buscamos por otros datos que sí conocemos. Estas son las funciones disponibles por Excel para realizar búsquedas:
Funciones financieras Excel es una de las herramientas más potentes para trabajar con información y cálculos financieros, ofrece una amplia gama de funciones prediseñadas para crearte tu propia "caja de ahorros en casa". Todas estas funciones están agrupadas en la categoría de Financieras. Vamos a estudiar la amplia gama de funciones financieras que nos ofrece Excel:
Controlar errores en funciones Al igual que podíamos definir condiciones de validación para los datos, podemos controlar los errores en las fórmulas. Para corregirlos necesitaremos, primero, localizarlos. Por ejemplo, al introducir una fórmula manualmente podemos cometer un error sintáctico como =PROMEDO (A1:A9), lo que provocaría que apareciese en la celda un error de tipo #¿NOMBRE?. Si pulsamos sobre la pestaña Formulas encontraremos el botón Comprobación de errores...
dentro del grupo Auditoría de fórmulas.
Desde la pequeña flecha de la derecha podemos desplegar un menú, con opciones interesantes como localizar Referencias circulares. Si hay alguna, aparece su localización en el submenú. En éste apartado vamos a ver la primera opción, Comprobación de errores..., que realiza la misma acción que pulsar directamente sobre el botón sin desplegar el menú. Aparece el cuadro de diálogo Comprobaciones de errores como el que vemos en la imagen
donde nos informa de qué tipo de error se ha detectado y en ocasiones nos puede ofrecer una
corrección.
La parte más interesante es la descripción del error. Lo normal es que con ella sepamos cuál es el problema y pulsando Modificar en la barra de fórmulas, la rectifiquemos manualmente.
Con los botones Anterior y Siguiente podremos ir moviéndonos entre los errores del libro, si es que hay más de uno. Además, disponemos de herramientas útiles como la Ayuda sobre este error, u Omitir error, para dejar la fórmula tal y como está. El botón Mostrar pasos de cálculo... nos abre un cuadro de diálogo donde evalua la fórmula y nos informa dónde se encuentra el error, si es en el nombre de la función o si está en los parámetros de la fórmula. Otras herramientas de Auditoría de fórmulas En la pestaña de Fórmulas también hay disponibles otras opciones útiles.
- Rastrear precedentes dibuja unas flechas indicando dónde están las celdas involucradas en la fórmula. - Rastrear dependientes dibuja flechas indicando a qué fórmula pertenece la celda seleccionada, si es que pertenece a alguna fórmula. También se pueden rastrear desde la opción Rastrear error del menú Comprobación de errores..... - Quitar flechas elimina las flechas indicativas de celdas creadas con Rastrear dependientes o Rastrear precedentes.
- Evaluar fórmula abre un cuadro de diálogo que muestra la fórmula de la celda activa y
sus resultados.
- La Ventana inspección permite hacer un seguimiento del valor de las celdas.
6.10. Enlazando y consolidando hojas de trabajo Ya hemos visto cómo trabajar con datos y cómo trabajar con fórmulas. Es inevitable hablar de los conceptos enlazar y consolidar hojas. Enlazar hojas de trabajo. El concepto de enlazar en Excel es el hecho de utilizar fórmulas de varias hojas para combinas datos. Al enlazar hojas de trabajo estamos creando una dependencia de una con respecto a la otra, apareciendo así dos conceptos: - el libro de trabajo dependiente: es el que contiene las fórmulas.
- el libro de trabajo fuente: es el que contiene los datos. La cuestión que debemos plantearnos antes de enlazar hojas de trabajo, es si realmente nos hace falta complicarnos tanto o sencillamente podemos hacerlo todo en una misma hoja.
No existe una respuesta genérica, dependerá de la envergadura de los datos y de las fórmulas; si las hojas las van a utilizar varias personas, etc. Para crear un libro de trabajo dependiente debemos crear fórmulas de referencias externas, es
decir fórmulas que hacen referencia a datos que se encuentran en una hoja externa a la que está
la fórmula.
IMPORTAR DATOS EN EXCEL Y CREAR UN MODELO DE DATOS
IMPORTAR DATOS DESDE UNA BASE DE DATOS
El objetivo de esta sección es conectarse a un origen de datos externo e importar datos en Excel
para realizar un análisis más exhaustivo.
Empecemos por descargar algunos datos de Internet. Los datos describen medallas olímpicas y
son parte de una base de datos de Microsoft Access.
Haga clic en los siguientes vínculos para descargar los archivos que usaremos durante esta serie
de tutoriales. Descargue cada uno de los cuatro archivos a una ubicación que sea fácilmente
accesible, como Descargas o Mis documentos, o en una carpeta nueva que cree:
> OlympicMedals.accdb base de datos de Access
> OlympicSports.xlsx libro de Excel
> Population.xlsx libro de Excel
> DiscImage_table.xlsx libro de Excel
En Excel, abra un libro vacío.
Haga clic en DATOS > Obtener datos externos > Desde Access. La cinta de opciones se ajusta
dinámicamente en función del ancho del libro, de modo que los comandos de la cinta pueden ser
levemente diferentes a los de las pantallas siguientes. La primera pantalla muestra la cinta de
opciones cuando un libro es extenso, la segunda imagen muestra un libro que ha cambiado de
tamaño para ocupar solo una parte de la pantalla.
IMPORTAR DATOS DESDE ACCESS
Importar datos desde Access con una cinta de opciones pequeña
Seleccione el archivo OlympicMedals.accdb que descargó y haga clic en Abrir. Aparece la
siguiente ventana Seleccionar tabla, que muestra las tablas de la base de datos. Las tablas de la
base de datos son similares a las hojas de cálculo o las tablas de Excel. Active la casilla Activar
selección de varias tablas y seleccione todas las tablas. A continuación, haga clic en Aceptar.
Seleccione la ventana de tablas
Aparece la ventana Importar datos.
NOTA Observe la casilla de la parte inferior de la ventana que permite Agregar estos datos al
modelo de datos y que se muestra en la siguiente pantalla. Cuando importa o trabaja con dos o
más tablas al mismo tiempo, se crea un modelo de datos de forma automática. Un modelo de datos
integra tablas, lo que permite realizar un análisis exhaustivo con tablas dinámicas, PowerPivot y
POWER View. Al importar tablas de una base de datos, las relaciones existentes entre estas en la
base de datos se usan para crear el modelo de datos en Excel. El modelo de datos es transparente
en Excel, pero puede visualizarlo y modificarlo directamente mediante el complemento PowerPivot.
El modelo de datos se describe con más detalles más adelante en este tutorial.
Seleccione la opción Informe de tabla dinámica, que permite importar las tablas en Excel y prepara
una tabla dinámica para analizar las tablas importadas. A continuación, haga clic en Aceptar.
Ventana Importar datos
Una vez importados los datos, se crea una tabla dinámica con las tablas importadas.
Tabla dinámica en blanco
Con los datos importados en Excel y el modelo de datos creado automáticamente, ya estará listo
para explorar los datos.
Explorar datos con una tabla dinámica
Explorar datos importados es sencillo mediante una tabla dinámica. En una tabla dinámica, arrastre
los campos (parecidos a las columnas de Excel) de las tablas (como las tablas que acaba de
importar de la base de datos de Access) a distintas áreas de la tabla dinámica para ajustar el modo
en que se presentan los datos. Una tabla dinámica tiene cuatro áreas: FILTROS, COLUMNAS,
FILAS y VALORES.
Las cuatro áreas de Campos de tabla dinámica
Puede que se requieran varias pruebas para determinar a qué área se debe arrastrar un campo.
Puede arrastrar todos los campos que quiera a las tablas hasta que la tabla dinámica presente los
datos del modo en que desea verlos. No dude en probar arrastrar campos a diferentes áreas de la
tabla dinámica; los datos subyacentes no se ven afectados cuando arrastra campos a una tabla
dinámica.
Vamos a explorar los datos de medallas olímpicas en la tabla dinámica, comenzando por los
medallistas olímpicos organizados por disciplina, tipo de medalla y país o región del atleta.
En Campos de tabla dinámica, expanda la tabla Medallas haciendo clic en la flecha junto a ella.
Busque el campo País Región CON en la tabla Medallas expandida y arrástrelo hasta el área
COLUMNAS. CON (NOC) son las siglas en inglés de Comités Olímpicos Nacionales, que es la
unidad organizativa de un país o una región.
A continuación, desde la tabla Disciplinas, arrastre Disciplina hasta el área FILAS.
Vamos a filtrar Disciplinas para mostrar sólo cinco deportes: tiro con arco, saltos, esgrima, patinaje
artístico y patinaje de velocidad. Esto se puede hacer desde dentro del área Campos de tabla
dinámica, o desde el filtro Etiquetas de fila de la propia tabla dinámica.
Haga clic en cualquier parte de la tabla dinámica para asegurarse de que la tabla dinámica de
Excel esté seleccionada. En la lista Campos de tabla dinámica, donde la tabla Disciplinas está
expandida, pase el cursor sobre el campo Disciplina para que aparezca una flecha desplegable en
la parte derecha. Haga clic en el menú desplegable, luego en (Seleccionar todo) para eliminar
todas las selecciones y, a continuación, desplácese hacia abajo y seleccione tiro con arco, saltos,
esgrima, patinaje artístico y patinaje de velocidad. Haga clic en Aceptar.
O bien, en la sección Etiquetas de fila de la tabla dinámica, haga clic en el menú desplegable que
se encuentra al lado de Etiquetas de fila, haga clic en (Seleccionar todo) para eliminar todas las
selecciones y, a continuación, desplácese hacia abajo y seleccione tiro con arco, saltos, esgrima,
patinaje artístico y patinaje de velocidad. Haga clic en Aceptar.
En Campos de tabla dinámica, en la tabla Medallas, arrastre Medalla al área VALORES. Puesto
que los valores deben ser numéricos, Excel cambia automáticamente Medalla a Recuento de
medallas.
En la tabla Medallas, seleccione Medalla nuevamente y arrástrela al área FILTROS.
Vamos a filtrar la tabla dinámica con el fin de que muestre solo los países o las regiones con más
de 90 medallas. Para ello, proceda de la siguiente manera.
En la tabla dinámica, haga clic en la lista desplegable situada a la derecha de Etiquetas de
columna.
Seleccione Filtros de valor y luego Mayor que...
Escriba 90 en el último campo (a la derecha). Haga clic en Aceptar.
Ventana Filtro de valor
La tabla dinámica es similar a la de la pantalla siguiente.
Tabla dinámica actualizada
Con poco trabajo, ahora dispone de una tabla dinámica básica que incluye campos de tres tablas
distintas. Lo que ha facilitado tanto esta tarea fueron las relaciones ya existentes entre las tablas.
Dado que existían relaciones de tabla en la base de datos de origen y que importó todas las tablas
en una sola operación, Excel pudo volver a crear esas relaciones en el modelo de datos.
Sin embargo, ¿qué sucede si los datos provienen de orígenes distintos o se importan más
adelante? Normalmente, puede crear relaciones con datos nuevos según las columnas
coincidentes. En el paso siguiente, importará tablas adicionales y aprenderá cómo crear relaciones
nuevas.
IMPORTAR DATOS DE UNA HOJA DE CÁLCULO
Ahora vamos a importar datos desde otro origen, esta vez desde un libro existente, y, a
continuación, especificar las relaciones entre nuestros datos existentes y los datos nuevos. Las
relaciones le permiten analizar colecciones de datos en Excel y crear visualizaciones interesantes e
inversivas de los datos que importa.
Empecemos creando una hoja de cálculo en blanco, para luego importar datos de un libro de
Excel.
Inserte una nueva hoja de cálculo de Excel y nómbrela Deportes.
Vaya a la carpeta que contiene los archivos de datos de muestra descargados y abra
OlympicSports.xlsx.
Seleccione y copie los datos en Hoja 1. Si selecciona una celda con datos, como, por ejemplo, la
celda A1, puede presionar Ctrl + A para seleccionar todos los datos adyacentes. Cierre el libro
OlympicSports.xlsx.
En la hoja de cálculo Deportes, coloque el cursor en la celda A1 y pegue los datos.
Con los datos aún resaltados, presione Ctrl + T para darles formato de tabla. También puede
aplicar formato de tabla a los datos desde la cinta de opciones seleccionando INICIO > Dar formato
de tabla. Dado que los datos tienen encabezados, seleccione Mi tabla tiene encabezados en la
ventana Crear tabla que aparece, tal como se muestra aquí.
Ventana Crear tabla
Aplicar formato a los datos como una tabla tiene muchas ventajas. Puede asignar un nombre a una
tabla, lo que hace que sea fácil identificar. También puede establecer relaciones entre tablas, lo
que permite la exploración y el análisis en tablas dinámicas, PowerPivot y POWER View.
Asigne un nombre a la tabla. En HERRAMIENTAS DE TABLA > DISEÑO > Propiedades, busque el
Nombre de la tabla y escriba Deportes. El libro es similar al de la pantalla siguiente.
Nombrar una tabla en Excel
Guarde el libro.
Importar datos con la función copiar y pegar
Ahora que hemos importado datos de un libro de Excel, vamos a importar datos de una tabla de
una página web o de cualquier otra fuente de la que podamos copiar y pegar en Excel. En los
pasos siguientes, agregará las ciudades anfitrionas de los Juegos Olímpicos de una tabla.
Inserte una nueva hoja de cálculo de Excel y asígnele el nombre Anfitriones.
En Excel, coloque el cursor en la celda A1 de la hoja de cálculo Anfitriones y pegue los datos.
Aplique formato de tabla a los datos. Como se ha descrito anteriormente en este tutorial, para dar
formato de tabla a los datos, debe presionar Ctrl + T o hacer clic en INICIO > Dar formato de tabla.
Dado que los datos tienen encabezados, seleccione Mi tabla tiene encabezados en la ventana
Crear tabla que aparece.
Asigne un nombre a la tabla. En HERRAMIENTAS DE TABLA > DISEÑO > Propiedades, busque el
campo Nombre de la tabla y escriba Anfitriones.
Seleccione la columna Edición y, desde la pestaña INICIO, dele el formato Número con 0
posiciones decimales.
Guarde el libro. El libro es similar al de la pantalla siguiente.
Libro de Excel con la tabla Anfitriones importada
Ahora que tiene un libro de Excel con tablas, puede crear relaciones entre ellas. La creación de
relaciones entre tablas le permite mezclar los datos de las dos tablas.
Crear una relación entre datos importados
Puede comenzar inmediatamente usando campos de las tablas importadas en la tabla dinámica. Si
Excel no puede determinar cómo incorporar un campo a una tabla dinámica, se debe establecer
una relación con el modelo de datos existente. En los pasos siguientes, aprenderá a crear una
relación entre los datos importados de orígenes diferentes.
En Hoja 1, en la parte superior de Campos de tabla dinámica, haga clic en Todas para ver la lista
completa de tablas disponibles, tal como se muestra en la pantalla siguiente.
Haga clic en Todas en Campos de tabla dinámica para mostrar todas las tablas disponibles.
Desplácese por la lista para ver las tablas nuevas que acaba de agregar.
Expanda Deportes y seleccione Deporte para agregarlo a la tabla dinámica. Observe que Excel le
solicita que cree una relación, como puede verse en la pantalla siguiente.
13
Solicitud de CREAR... relación en Campos de tabla dinámica
Esta notificación se produce porque usó campos de una tabla que no forma parte del modelo de
datos subyacente. Una manera de agregar una tabla al modelo de datos es crear una relación a
una tabla que ya esté en el modelo de datos. Para crear la relación, una de las tablas debe tener
una columna de valores únicos y no repetidos. En los datos de ejemplo, la tabla Disciplinas
importada desde la base de datos contiene un campo con códigos de deportes, llamado ID
Deporte. Esos mismos códigos de deportes están presentes en forma de campo en los datos de
Excel que importamos. Vamos a crear la relación.
Haga clic en CREAR... en el área resaltada de Campos de tabla dinámica para abrir el cuadro de
diálogo Crear relación, tal como se muestra en la siguiente pantalla.
Ventana Crear relación
En Tabla, elija Disciplinas en la lista desplegable.
En Columna (extranjero), elija ID Deporte.
En Tabla relacionada, elija Deportes.
En Columna relacionada (principal), elija ID Deporte.
Haga clic en Aceptar.
La tabla dinámica cambia para reflejar la nueva relación. Sin embargo, la tabla dinámica todavía no
tiene el aspecto correcto, debido al orden de campos del área FILAS. Disciplina es una
subcategoría de un determinado deporte, pero, debido a que acomodamos Disciplina por encima
de Deporte en el área FILAS, no se encuentra organizada de forma correcta. La siguiente pantalla
muestra esta ordenación no deseada.
Tabla dinámica con ordenación no deseada
En el área FILAS, mueva Deporte arriba de Disciplina. Así es mucho mejor, y la tabla dinámica
muestra los datos de la forma en que desea, tal como se muestra en la pantalla siguiente.
Tabla dinámica con ordenación corregida
En segundo plano, Excel está generando un modelo de datos que se puede usar en el libro, en
cualquier tabla dinámica, gráfico dinámico, PowerPivot o cualquier informe de Power View. Las
relaciones de tabla son la base de un modelo de datos y lo que determina las rutas de navegación
y cálculo.
En el siguiente tutorial, Ampliar relaciones del modelo de datos usando Excel 2013, PowerPivot y
DAX, hará lo que aprendió aquí y realizará los pasos para ampliar el modelo de datos con un
complemento sofisticado y visual de Excel llamado PowerPivot. También sabrá cómo calcular una
columna de una tabla y usar esa columna calculada para que una tabla no relacionada pueda
agregarse al modelo de datos.
Control y evaluación
Revise lo que ha aprendido
Ahora tiene un libro de Excel que incluye datos de acceso de una tabla dinámica en varias tablas,
de las cuales muchas importaron por separado. Aprendió a importar desde una base de datos y
desde otro libro de Excel, y a copiar los datos y pegarlos en Excel.
Para que los datos funcionen juntos, tuvo que crear una relación de tabla que Excel usó para
correlacionar las filas. También ha aprendido que tener columnas en una tabla que estén
correlacionadas a datos de otra tabla es fundamental para crear relaciones y buscar filas
relacionadas.
Hay varias formas de intercambiar datos entre Microsoft Office Access y Microsoft Office Excel.
Para traer datos de Access a Excel, puede copiarlos de una hoja de datos de Access y pegarlos en
una hoja de cálculo de Excel, conectar con una base de datos de Access desde una hoja de
cálculo de Excel o exportar datos de Access a una hoja de cálculo de Excel.
Para traer datos de Excel a Access, puede copiarlos de una hoja de cálculo de Excel y pegarlos en
una hoja de datos de Access, importar una hoja de cálculo de Excel en una tabla de Access o crear
un vínculo con una hoja de cálculo de Excel desde una tabla de Access.
NOTAS
El término "importar" significa diferentes cosas en Excel y Access. En Excel, al importar datos, se
crea una conexión permanente con los datos que se puede actualizar. En Access, al importar
datos, se traen los datos a Access una sola vez sin una conexión permanente con ellos.
No se puede guardar un libro de Excel como una base de datos de Access. Ni Excel ni Access
proporcionan la funcionalidad necesaria para crear una base de datos de Access a partir de datos
de Excel.
TRABAJAR CON DATOS DE ACCESS EN EXCEL
Es posible que desee trabajar con datos de Access en un libro de Excel para aprovechar las
características de análisis de datos y creación de gráficos, la flexibilidad en la presentación y
diseño de los datos o la infinidad de funciones que no están disponibles en Access.
COPIAR DATOS DE ACCESS EN EXCEL
Desde Access, puede copiar datos de una vista de hoja de datos y después pegarlos en una hoja
de cálculo de Excel.
Inicie Access y abra la tabla, la consulta o el formulario que contiene los registros que desea
copiar.
En la ficha Inicio, haga clic en Vista y luego en Vista Hoja de datos.
Seleccione los registros que desea copiar.
Si desea seleccionar columnas específicas, arrastre los encabezados de las columnas adyacentes.
En la ficha Inicio, en el grupo Portapapeles, haga clic en Copiar.
Método abreviado de teclado También puede presionar CTRL+C.
Inicie Excel y abra la hoja de cálculo en la que desea pegar los datos.
Haga clic en la esquina superior izquierda del área de la hoja de cálculo en la que desea que
aparezca el primer nombre de campo.
Para evitar que los registros copiados reemplacen los datos existentes, asegúrese de que la hoja
de cálculo no contiene datos bajo la celda en la que va a hacer clic, ni a su derecha.
En la ficha Inicio, en el grupo Portapapeles, haga clic en Pegar Imagen del botón.
Método abreviado de teclado También puede presionar CTRL+V.
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 (.doc.), 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.
Haga clic en la celda en la que desea colocar los datos de la base de datos de Access.
En la ficha Datos, en el grupo Obtener datos externos, haga clic en Desde Access.
3. En la lista Buscar en, busque y haga doble clic en la base de datos de Access que desea importar.
En el cuadro de diálogo Seleccionar tabla, haga clic en la tabla o consulta que desea importar y
luego haga clic en Aceptar.
4. En el cuadro de diálogo Importar datos, haga lo siguiente:
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.
Para ver los datos como un informe de tabla dinámica, seleccione Informe de tabla dinámica.
Para ver los datos como un informe de gráfico dinámico y un informe de tabla dinámica, seleccione
Informe de gráfico y tabla dinámicos.
Si lo desea, haga clic en Propiedades para definir las opciones de actualización, formato y diseño de los
datos importados y, a continuación, haga clic en Aceptar.
En ¿Dónde desea situar los datos?, siga uno de los procedimientos siguientes:
Para devolver los datos a la ubicación seleccionada, haga clic en Hoja de cálculo existente.
Para devolver los datos a la esquina superior izquierda de la nueva hoja de cálculo, haga clic en Hoja de
cálculo nueva.
5. Haga clic en Aceptar.
Excel coloca el rango de datos externos en la ubicación especificada.
Para obtener más información sobre cómo crear una conexión con datos, vea Conectarse con
datos externos (importar).
NOTA: CONECTARSE CON DATOS EXTERNOS (IMPORTAR)
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.
1. 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:
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.
Esta lista se crea a partir de una biblioteca de conexiones de datos (DCL) de Servicios de Excel en
un sitio de Microsoft Office SharePoint Server 2007. Una DCL es una biblioteca de documentos del
sitio de Microsoft Office SharePoint Services 2007 que contiene un conjunto de archivos de
conexión de datos de Office (.doc.). Generalmente, es el administrador del sitio el que configura la
DCL, quien, además, puede configurar el sitio de SharePoint para mostrar archivos ODC desde la
DCL en el cuadro de diálogo Conexiones externas. Para obtener más información, vea la Ayuda de
la Administración central de Office SharePoint Server 2007.
SUGERENCIA Si no ve la conexión que desea, puede crear una haciendo clic en Examinar en
busca de más y, a continuación, en el cuadro de diálogo Seleccionar origen de datos, haga clic en
Nuevo origen para iniciar el Asistente para conexión de datos y seleccionar el origen de datos al
cual quiere conectarse.
NOTA Si elige una conexión de las categorías Archivos de conexión en la red o Archivos de
conexión en este equipo, el archivo de conexión se copia en el libro como una nueva conexión de
libro y se usa como la nueva información de conexión.
Seleccione la conexión que desee y, a continuación, haga clic en Abrir.
En el cuadro de diálogo Importar datos, en la sección Seleccione cómo desea ver estos datos en el
libro, siga uno de los procedimientos siguientes:
IMPORTANTE La sección Seleccione cómo desea ver estos datos en el libro y sus opciones,
como se muestra en la lista a continuación, no están disponibles para conexiones de texto,
consulta web y datos XML. Si se está conectando a estos datos proceda al paso 5.
Haga clic en Tabla para crear una tabla para realizar una ordenación y filtrado sencillos.
Haga clic en Informe de tabla dinámica para crear un informe de tabla dinámica para resumir
cantidades grandes de datos agregando y subtotalizando los datos.
Haga clic en Informe de gráfico y tabla dinámicos para crear una tabla dinámica y un informe de
gráfico dinámicos para resumir los datos visualmente.
Haga clic en Crear sólo conexión para almacenar la conexión seleccionada en el libro para su uso
posterior.
Use la opción Crear sólo conexión para almacenar la conexión seleccionada en el libro para su uso
posterior. Por ejemplo, si se está conectado a un origen de datos de un cubo de procesamiento
analítico en línea (OLAP) y desea convertir celdas de tabla dinámica en fórmulas de hoja cálculo
con el comando Convertir en fórmulas (en la ficha Opciones, en el grupo Herramientas, haga clic
en Herramientas OLAP), puede utilizar esta opción, ya que no necesita almacenar el informe de
tabla dinámica.
En ¿Dónde desea situar los datos?, siga uno de los procedimientos siguientes:
Para situar el informe de tabla dinámica o gráfico dinámico en una hoja de cálculo existente,
seleccione Hoja de cálculo existente y, a continuación, escriba la primera celda del rango de celdas
donde desea situar el informe de tabla dinámica.
O bien, haga clic en Contraer cuadro de diálogo Imagen del botón para ocultar temporalmente el
cuadro de diálogo, seleccione la primera celda de la hoja de cálculo y, a continuación, presione
Expandir diálogo Imagen del botón.
Para poner el informe de tabla dinámica en una hoja de cálculo nueva que empiece por la celda
A1, haga clic en Nueva hoja de cálculo.
También puede cambiar las propiedades de conexión haciendo clic en Propiedades, realizando los
cambios en los cuadros de diálogo Propiedades de conexión, Rango de datos externos o
Propiedades de asignación XML y, por último, haciendo clic en Aceptar.
Importar datos de Excel a Access
Para almacenar datos de Excel en una base de datos de Access y utilizar y mantener estos datos
en Access de ahora en adelante, puede importar los datos. Cuando se importan datos, Access los
almacena en una tabla nueva o existente sin modificarlos. Sólo puede importar una hoja de cálculo
cada vez durante una operación de importación. Para importar datos de varias hojas de cálculo,
repita la operación de importación para cada hoja de cálculo.
A continuación se incluyen algunos escenarios comunes de importación de datos de Excel en
Access:
Utiliza Excel con mucha frecuencia, pero a partir de ahora desea utilizar Access para trabajar con
estos datos. Desea mover los datos de las hojas de cálculo de Excel a una o varias bases de datos
de Access.
Su departamento o grupo de trabajo utiliza Access, pero de vez en cuando recibe datos en formato
de Excel que debe combinar con bases de datos de Access. Desea importar estas hojas de cálculo
de Excel a la base de datos cuando las recibe.
Utiliza Access para administrar los datos, pero los informes semanales que recibe del resto de los
integrantes de su equipo son libros de Excel. Desea agilizar el proceso de importación para
asegurarse de que los datos se importan cada semana a una hora específica a la base de datos.
Para obtener más información sobre cómo importar datos de Excel a Access, vea el sistema de
Ayuda de Access.
CREAR UN VÍNCULO CON DATOS DE EXCEL DESDE ACCESS
Puede vincular un rango de datos de Excel en una base de datos de Access como una tabla.
Utilice este sistema si tiene previsto mantener el rango en Excel, pero desea también que esté
disponible en Access. Este tipo de vínculo se crea desde la base de datos de Access, no desde
Excel.
Cuando se crea un vínculo con una hoja de cálculo o un rango con nombre de Excel, Access crea
una nueva tabla que está vinculada a las celdas de origen. Todos los cambios que realice en las
celdas de origen en Excel se reflejan en la tabla vinculada. Sin embargo, no puede modificar el
contenido de la tabla correspondiente en Access. Si desea agregar, modificar o suprimir datos,
debe realizar los cambios en el archivo de origen.
A continuación se incluyen algunos escenarios comunes de vinculación a una hoja de cálculo de
Excel desde Access:
Desea seguir manteniendo los datos en hojas de cálculo de Excel, pero desea poder utilizar las
eficaces características de consulta y generación de informes de Access.
Su departamento o grupo de trabajo utiliza Access, pero trabaja con datos de orígenes externos
que están en hojas de cálculo de Excel. No desea mantener copias de los datos externos, pero
desea poder trabajar con ellos en Access.
Para obtener más información sobre cómo vincular datos de Access a Excel, vea el sistema de
Ayuda de Access.
http://office.microsoft.com/es-es/excel-help/intercambiar-copiar-importar-exportar-datos-entre-excel-
y-access-HA010096300.aspx.
IMPORTACIÓN DE DATOS DESDE UNA BASE DE DATOS DE ACCESS
Existen diversas maneras de intercambiar datos entre Microsoft Access y Microsoft Excel.
Para traer datos de Access a Excel, puede copiarlos de una hoja de datos de Access y pegarlos en
una hoja de cálculo de Excel, conectar con una base de datos de Access desde una hoja de
cálculo de Excel o exportar datos de Access a una hoja de cálculo de Excel. Este es el primer paso
a seguir, como se puede ver es muy fácil, solo necesitamos leer las instrucciones detalladamente y
hacerlo en nuestra máquina.
Para traer datos de Excel a Access, puede copiarlos de una hoja de cálculo de Excel y pegarlos en
una hoja de datos de Access, importar una hoja de cálculo de Excel en una tabla de Access o crear
un vínculo con una hoja de cálculo de Excel desde una tabla de Access. El importar desde Access
a Excel es una propiedad interesante que se puede realizar para con la base de datos del
programa Access llevar a cabo cálculos y aplicar otra serie de utilidades de la hoja de cálculo Excel
como por ejemplo gráficos. Ten en cuenta que no solo puedes hacer esta importación con Access
también desde la Web, desde texto, o desde otras fuentes (SQL Server, XML, Análisis Services,
entre otras).
Es muy sencillo hacerlo para ello necesitas una base de datos de Access y seguir los siguientes pasos: Abre una hoja de Excel en blanco de las versiones indicadas. En la Cinta de opciones, fíjate y sitúate en la pestaña Datos y luego en el grupo de trabajo Obtener datos externos. Ver imagen ampliada. Despliega en el triángulo negro a la derecha y se abren diferentes posibilidades desde donde importar, elige Desde Access. La ventana que se abre con el título Seleccionar archivos de origen de datos te permite que busques y selecciones la base de datos Access que quieras importar. Pulsa Abrir una vez seleccionado y fíjate que apare en el recuadro Nombre del archivo. Si la base de datos que has elegido tiene varias tablas, has de optar por una de ellas y en la ventana Seleccionar tabla indicarla. En caso de querer importar dos o más tablas has de repetir el proceso descrito aquí tantas veces como tablas quieras traer. Puedes optar por ver los datos en el libro como una Tabla (es el que vas a elegir), si quieres también puedes optar a que se muestren como un informe de Tabla dinámica, o como un informe de gráfico y tabla dinámica. A continuación especifica si quieres que se abran los datos que se van a importar en otra hoja de cálculo o en cuales celdas de la hoja donde estas situado quieres que se coloquen los datos. Además al pie dispones del botón llamado Propiedades, si pulsas sobre él veras cuales son las propiedades de la importación. Ver imagen ampliada. Por ejemplo si quieres sincronizar los datos de Access en Excel pulsa en Propiedades, luego en Propiedades de conexión, verás en la ventana que se abre dos pestañas Uso y Definición, por defecto te encontraras en la primera y ahí podrás elegir cada cuanto se ponga al día el fichero Excel importado.
Para ello fíjate en donde pone Actualizar cada: al lado 60 minutos, indica cada cuantos minutos quieres que se actualicen los datos en Excel tras haber realizado modificaciones en Access. Otra opción es Actualizar al abrir el archivo, pon un tic en el cuadro que consideres. Todo lo demás no hace falta que lo cambies. Por último no olvides pulsar en el botón Aceptar. Ahora de nuevo pulsa Aceptar en la ventana Importar datos y en la hoja Excel donde previamente has indicado donde querías los datos de Access estos aparecerán. Fíjate arriba en una nueva pestaña llamada Diseño, Herramientas de tabla desde donde te aparecen otra serie de utilidades para aplicar, como son: Propiedades, Herramientas, Datos externos de tabla, Opciones de estilo de tabla, Estilos de tabla.
Si la base de datos consta de una sola tabla se abrirá el cuadro Importar datos.
NOTA El término importar tiene dos significados distintos en Excel y Access. En Excel, significa crear una conexión permanente con los datos que se puede actualizar. En Access, significa traer de una vez los datos a Access sin una conexión de datos. Consejo: En algunas ocasiones para realizar esta acción necesitarás conocer la contraseña de la base de datos de Access para poder importar. En ese caso habrás de solicitarla a la persona encargada de gestionar dicha base de datos y proceder a la importación. IMPORTTAR DAT Las páginas web suelen contener información que es perfecta para el análisis en Excel. Por
ejemplo, puede analizar cotizaciones en Excel mediante el uso de información que viene
directamente de una página web. Según sus necesidades, puede recuperar datos actualizables (es
decir, puede actualizar los datos en Excel con los datos más recientes de la página web) o puede
obtener datos de una página web y mantenerlos estáticos en la hoja de cálculo.
Puede usar una consulta web para recuperar datos almacenados en una intranet o en Internet,
como una sola tabla, varias tablas o todo el texto de una página web, y analizar los datos mediante
las herramientas y características de Excel. Con un solo clic, puede actualizar fácilmente los datos
con la información más actualizada de la página web. Por ejemplo, puede recuperar y actualizar
índices de cotizaciones desde una página web pública o recuperar y actualizar una tabla de
información de ventas de una página web de la empresa.
Puede importar datos que se originen en una página web mediante el cuadro de diálogo Nueva
consulta web. (En la ficha Datos, en el grupo Obtener datos externos, haga clic en Desde web).
Debe tener acceso a Internet a través de la intranet de la empresa o mediante un módem en el
equipo o red, o puede realizar una consulta en archivos HTML o XML almacenados en el equipo.
A continuación daremos los pasos a a seguir para la importación de datos desde Excel, en lo
personal se me hizo más fácil siguiendo paso a paso las instrucciones, cabe darnos cuenta que
esto nos sirve de mucho al momento en el que queremos facilitar el trabajo que tenemos con tan
solo vincular, sin necesidad de copiar manualmente.
Una consulta web (Web Query) de Excel te permite importar información de un sitio web a una hoja de Excel. La consulta encontrará cualquier tabla de datos en la página web y te permitirá seleccionar la tabla que necesitas para poder importar los datos a tu hoja de Excel.
Vamos a trabajar en un ejemplo básico para tener una idea de cómo operan las consultas web. Para empezar debes seleccionar la celda donde quieres que se coloquen los datos y
seleccionar la ficha Datos y el comando Desde Web que se encuentra en el grupo Obtener datos externos.
Seleccionar tabla de datos
Se abrirá el cuadro de diálogo Nueva consulta web y en el cuadro de Dirección colocaré lo siguiente: http://www.imdb.com/chart/top (esta dirección muestra las mejores 250 películas de acuerdo al sitio IMDB). Observa los íconos amarillos que aparecen indicando las tablas de datos que se pueden seleccionar. Haz clic sobre el ícono de la tabla deseada (el icono se tornará verde) y posteriormente haz clic en el botón Importar.
Inmediatamente se mostrará el cuadro de diálogo Importar datos que te preguntará la ubicación donde deseas colocar los datos. Una vez indicado el rango, haz clic en Aceptar.
Excel comenzará a importar los datos y al terminar los desplegará dentro de la hoja correspondiente.
Con esto hemos importado información contenida dentro de una página Web, pero Excel puede ir aún más lejos y permitirnos actualizar los datos sin tener que volver a visitar el sitio Web original.
Actualizar datos desde la Web
Una vez construida la consulta Web podrás refrescar los datos con tan solo seleccionar el comando Actualizar todo que se encuentra en el grupo Conexiones de la ficha Datos. Este comando ocasionará que Excel descargue una versión actualizada de los datos automáticamente.
Debes tomar en cuenta que los datos deben venir de una página de dominio público, es decir, no deberá solicitar usuario ni contraseña, de lo contrario Excel no podrá importar los datos. Y por supuesto, si la página de origen elimina los datos publicados, no habrá forma de realizar la actualización.
Las aplicaciones para las consultas web pueden ser muchas. Se podrían construir tableros de mando que actualicen la información automáticamente al abrir el archivo. Inclusive se podrían implementar consultas web para intercambiar información en una Intranet (LAN) donde alguna aplicación Web genera información en tablas con lo cual no habrá necesidad de copiar y pegar la información manualmente en Excel sino que se refrescará automáticamente a través de una consulta web.
IMPORTACIÓN DE ARCHIVOS DE TEXTO
Puede utilizar Excel para importar datos de un archivo de texto a una hoja de cálculo. (En la ficha
Datos, en el grupo Obtener datos externos, haga clic en Desde texto). El Asistente para importar
texto examina el archivo de texto que se va a importar y ayuda a garantizar que los datos se
importen del modo deseado.
Existen dos formas de importar datos de un archivo de texto usando Excel: puede abrir el archivo
de texto en Excel (lo cual no establece una conexión con el archivo de texto) o puede importar el
archivo de texto como un rango de datos externos (lo cual sí establece una conexión con el archivo
de texto).
Son dos los formatos de archivo de texto que se usan habitualmente:
Archivos de texto delimitados (.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 (,) suele
separar cada campo de texto.
*NOTA: QUE ES .csv
Los ficheros CSV (del inglés comma-separated values) son un tipo de documento en formato
abierto sencillo para representar datos en forma de tabla, en las que las columnas se separan por
comas (o punto y coma en donde la coma es el separador decimal: España, Francia, Italia...) y las
filas por saltos de línea. Los campos que contengan una coma, un salto de línea o una comilla
doble deben ser encerrados entre comillas dobles.
El formato CSV es muy sencillo y no indica un juego de caracteres concreto, ni cómo van situados
los bytes, ni el formato para el salto de línea. Estos puntos deben indicarse muchas veces al abrir
el fichero, por ejemplo, con una hoja de cálculo.
También puede cambiar el carácter separador que se usa 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.
A continuación daremos los pasos a seguir para realizar la importación de archivos de texto, si las seguimos paso a paso, lograremos un excelente trabajo.
Los archivos de texto son muy comunes al momento de intercambiar información entre diferentes tipos de sistemas. Anteriormente los sistemas de las grandes empresas no generaban archivos Excel sino archivos de texto.
Por esa razón Excel incorporó desde sus primeras versiones la funcionalidad de importar archivos de texto de manera que se pudiera analizar dicha información dentro de Excel. El día de hoy se sigue utilizando este tipo de archivos para intercambiar información ya que son archivos muy ligeros a pesar de la gran cantidad de datos que pueden llegar a contener.
Un archivo de texto que contiene información de una base de datos siempre hace uso de algún carácter especial para separar la información perteneciente a cada columna. Es precisamente ese carácter utilizado el que hace la diferencia entre un tipo de archivo y otro. Por ejemplo, aquellos archivos en donde cada cambio de columna está indicado por el signo de puntuación “,” (coma) es conocido como archivo CSV por sus siglas en inglés (Comma-Separated Values). Observa el siguiente ejemplo de un archivo CSV:
La diferencia entre un archivo CSV y uno de texto es solamente el tipo de separador utilizado. Un archivo de texto generalmente indica la separación entre columnas utilizando el tabulador:
Estos dos tipos de archivos son los más comunes, pero podrías encontrarte alguno diferente que tal vez utilice el signo “;” para separar las columnas, sin embargo cualquier tipo de archivo de texto podrá ser importado a Excel sin importar el carácter separador utilizado.
IMPORTAR ARCHIVOS DE TEXTO EN EXCEL
Para importar un archivo de texto debes ir a la ficha Datos y seleccionar el comando Desde texto que se encuentra dentro del grupo Obtener datos externos. Esto abrirá el cuadro de diálogo Importar archivo de texto que permitirá seleccionar el archivo a importar.
Después de seleccionar el archivo adecuado debes hacer clic en Importar. Eso mostrará el primer paso del Asistente para importar texto. Este primer paso es importante porque permite especificar el tipo de archivo a importar ya sea que utilice algún carácter específico como delimitador o que sea un archivo que utiliza un ancho fijo de columna.
De igual manera en este paso se puede indicar a Excel que comience a importar a partir de alguna fila específica del archivo. Al hacer clic en el botón Siguiente, Excel sugerirá el tipo de separador que ha descubierto dentro del archivo a importar y generalmente será acertado en su sugerencia. Aun así recomiendo validar que dentro de la vista previa efectivamente se muestra una separación adecuada de los datos.
En caso de que Excel se haya equivocado solamente deberás seleccionar el separador adecuado de la lista o especificar algún otro si es necesario. Haz clic en siguiente que nos llevará al último paso que permite especificar el tipo de datos de cada columna.
Solamente tienes 3 opciones: General, Texto y Fecha. Sugiero poner especial atención a las fechas de manera que después de la importación se facilite su manejo e interpretación dentro de Excel. Al hacer clic en el botón Finalizar, se preguntará por la ubicación dentro de la hoja de Excel donde se colocarán los datos importados.
Una vez indicada la celda destino deberás hacer clic en el botón Aceptar y los datos serán importados por Excel.
IMPORTACIÓN DE DATOS DESDE MICROSOFT SQL SERVER
Microsoft SQL Server es una base de datos relacional completa diseñada para las soluciones de
datos profesionales que requieren un óptimo rendimiento, disponibilidad, escalabilidad y seguridad.
En Excel, puede conectarse fácilmente a una base de datos de Microsoft SQL Server. (En el grupo
Obtener datos externos de la ficha Datos, haga clic en Desde otros orígenes y, a continuación, en
Desde SQL Server).
Descripción de la técnica
Para importar datos de Excel, los ejemplos de este artículo con:
Servicios de transformación de datos (DTS) de SQL Server
Microsoft SQL Server 2005 Integration Services (SSIS)
Servidores vinculados de SQL Server
Consultas distribuidas de SQL Server
Objetos de datos ActiveX (ADO) y el proveedor Microsoft OLE DB para SQL Server
ADO y el proveedor Microsoft OLE DB para Jet 4.0
Al conectarse a una base de datos de Microsoft SQL Server, el Asistente para la conexión de datos
muestra tres páginas:
Página 1: Conectar con el servidor de base de datos Use esta página para especificar el servidor
de base de datos y la forma de iniciar sesión en el servidor.
Página 2: Seleccionar una base de datos y una tabla Use esta página para especificar la base de
datos y la tabla o consulta que contiene los datos que desea.
Página 3: Guardar archivo de datos y conexión Use esta página para especificar y describir el
archivo de conexión y las frases de búsqueda para localizar el archivo.
Descripción de la técnica
La lista siguiente describe el hardware recomendado, software, infraestructura de red y service
packs necesarios:
Instancia disponible de Microsoft SQL Server 7.0 o Microsoft SQL Server 2000 o Microsoft SQL Server 2005
Microsoft Visual Basic 6.0 de los ejemplos de ADO que utilice Visual Basic Partes de este artículo se supone que está familiarizado con los temas siguientes:
Servicios de transformación de datos
Los servidores vinculados y las consultas distribuidas
Desarrollo de ADO en Visual Basic
Requisitos
Ejemplos
Las instrucciones SQL de ejemplo que se utilizan en este artículo muestran consultas Create Table
que importan datos de Excel en una nueva tabla de SQL Server mediante SELECT...EN...DE la
sintaxis. Puede convertir estas instrucciones en consultas de datos anexados mediante el uso de
INSERT INTO...SELECCIÓN...DE la sintaxis mientras continúe hacer referencia a los objetos de
origen y de destino como se muestra en estos ejemplos de código.
Importación y anexar
Puede utilizar el Asistente para importación de servicios de transformación de datos (DTS) de SQL
Server o la importación de SQL Server y Asistente para exportación para importar datos de Excel
en tablas de SQL Server. Cuando se recorre el asistente y seleccionar las tablas de origen de
Excel, recuerde que los nombres de objeto de Excel se anexan con un signo de dólar ($)
representan hojas de cálculo (por ejemplo, Sheet1$), y que los nombres de objeto normal sin el
signo de dólar representan Excel rangos con nombre.
Utilice DTS o SSIS
Para simplificar las consultas, puede configurar un libro de Excel como un servidor vinculado en
SQL Server. Para obtener información adicional, haga clic en el número de artículo siguiente para
verlo en Microsoft Knowledge Base:
306397 Cómo: Utilizar Excel con SQL Server de servidores vinculados y consultas distribuidas
El código siguiente importa los datos de la hoja de cálculo de los clientes en el servidor vinculado
de Excel "EXCELLINK" en una nueva tabla de SQL Server denominada XLImport1:
SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$] También puede ejecutar la consulta en el origen de una manera passthrough mediante
OPENQUERY como sigue:
SELECT * INTO XLImport2 FROM OPENQUERY (EXCELLINK, 'SELECT * FROM [Customers$]')
UTILIZAR UN SERVIDOR VINCULADO
Si no desea configurar una conexión persistente con el libro de Excel como un servidor vinculado,
puede importar datos para un propósito específico mediante el OPENDATASOURCE o la función
OPENROWSET. Ejemplos de código siguientes también importación los datos de la hoja de
cálculo Excel clientes a nuevas tablas de SQL Server:
SELECT * INTO XLImport3 FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$] SELECT * INTO XLImport4 FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; Database=C: \test\xltest.xls', [Customers$]) SELECT * INTO XLImport5 FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; Database=C: \test\xltest.xls', 'SELECT * FROM [Customers$]')
Utilizar consultas distribuidas
Cuando se conectan a SQL Server en una aplicación de ADO con Microsoft OLE DB para SQL
Server (SQLOLEDB), puede utilizar la misma sintaxis "consulta distribuida" de la sección Utilizar
consultas distribuidas para importar datos de Excel a SQL Server.
En el siguiente ejemplo de código de Visual Basic 6.0, se requiere que se agregue una referencia
de proyecto a objetos de datos ActiveX (ADO). Este ejemplo de código también muestra cómo
utilizar OPENDATASOURCE y OPENROWSET a través de una conexión de SQLOLEDB.
Dim cn As ADODB.Connection Dim strSQL As String Dim lngRecsAff As Long Set cn = New ADODB.Connection cn.Open "Provider=SQLOLEDB; Data Source=<server>;" & _ "Initial Catalog=<database>; User ID=<user>; Password=<password>" 'Import by using OPENDATASOURCE. StrSQL = "SELECT * INTO XLImport6 FROM " & _ "OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', “& _ "'Data Source=C:\test\xltest.xls;" & _ "Extended Properties=Excel 8.0')...[Customers$]" Debug. Print strSQL Cn. Execute strSQL, lngRecsAff, adExecuteNoRecords Debug.Print "Records affected: " & lngRecsAff 'Import by using OPENROWSET and object name. StrSQL = "SELECT * INTO XLImport7 FROM " & _ "OPENROWSET ('Microsoft.Jet.OLEDB.4.0', “& _ "'Excel 8.0; Database=C: \test\xltest.xls', “& _ "[Customers$])" Debug.Print strSQL Cn. Execute strSQL, lngRecsAff, and adExecuteNoRecords Debug.Print "Records affected: " & lngRecsAff 'Import by using OPENROWSET and SELECT query. StrSQL = "SELECT * INTO XLImport8 FROM " & _ "OPENROWSET ('Microsoft.Jet.OLEDB.4.0', “& _ "'Excel 8.0; Database=C: \test\xltest.xls', “& _ "'SELECT * FROM [Customers$]')" Debug.Print strSQL Cn. Execute strSQL, lngRecsAff, and adExecuteNoRecords Debug.Print "Records affected: " & lngRecsAff
cn.Close Set cn = Nothing
Utilizar ADO y SQLOLEDB
En el ejemplo de la sección anterior se utiliza ADO con el proveedor de SQLOLEDB para conectar
con el destino de la importación de Excel a SQL. También puede utilizar el proveedor OLE DB para
Jet 4.0 para conectar con el origen de Excel.
El motor de base de datos Jet puede hacer referencia a bases de datos externas en las
instrucciones de SQL mediante el uso de una sintaxis especial que ya tiene tres formatos
diferentes:
[Ruta de acceso completa a la base de datos de Microsoft Access].[Nombre de la tabla]
[Nombre ISAM;Cadena de conexión de ISAM].[Nombre de la tabla]
[ODBC;Cadena de conexión ODBC].[Nombre de la tabla] En esta sección se utiliza el formato tercero para realizar una conexión ODBC a la base de datos
de SQL Server de destino.Puede utilizar un nombre de origen de datos ODBC (DSN) o una cadena
de conexión sin DSN:
DSN: [Odbc; DSN=<DSN name>; UID=<user>; PWD=<password>] DSN-less: [Odbc; Driver={SQL Server}; Server=<server>; Database=<database>; UID=<user>; PWD=<password>] En el siguiente ejemplo de código de Visual Basic 6.0, se requiere que se agregue una referencia
de proyecto a ADO.
Dim cn As ADODB.Connection Dim strSQL As String Dim lngRecsAff As Long Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\test\xltestt.xls;" & _ "Extended Properties=Excel 8.0" 'Import by using Jet Provider. StrSQL = "SELECT * INTO [Odbc; Driver= {SQL Server};" & _ "Server=<server>; Database=<database>;" & _ "UID=<user>; PWD=<password>].XLImport9 " & _ "FROM [Customers$]" Debug.Print strSQL cn.Execute strSQL, lngRecsAff, adExecuteNoRecords Debug.Print "Records affected: " & lngRecsAff cn.Close Set cn = Nothing También puede utilizar esta sintaxis, que admite el proveedor Jet, para importar datos de Excel en
otras bases de datos de Microsoft Access, bases de datos de acceso secuencial indizado (ISAM)
método ("escritorio") o bases de datos ODBC.
Utilizar ADO y el proveedor Jet
Recuerde que los nombres de objeto de Excel se anexan con un signo de dólar ($) representan rangos de hojas de cálculo (por ejemplo, Sheet1$) y que los nombres de objeto simple con nombre de Excel.
En algunos casos, especialmente cuando se designa el origen de datos de Excel con el nombre de tabla en lugar de una consulta de selección, las columnas de la tabla de SQL Server de destino se reorganizan en orden alfabético. 299484 PRB: Las columnas se ordenan alfabéticamente cuando utiliza ADOX para
recuperar las columnas de la tabla de Access
Cuando el proveedor Jet* determina que una columna de Excel contiene datos numéricos y texto mixto, el proveedor Jet selecciona el tipo de datos "la mayoría" y devuelve valores no coincidentes como NULLs.** Para obtener información adicional acerca de cómo evitar este problema, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base: 194124 PRB: Valores de Excel se devuelven como NULL utiliza OpenRecordset de DAO
*NOTA:
Se utiliza Jet OLE DB proveedor 4.0 para conectarse a diferentes bases de datos externas a través
de los controladores ISAM instalables. Las cadenas de conexión se proporcionan para la apertura
de cada base de datos ISAM. El código de este artículo se supone que se ha creado un objeto
ADO Connection: cnny un objeto RecordSet de ADO: rst.
** NULLs: Todos los valores de una columna determinada tienen el mismo tipo de dato, y éstos
están extraídos de un conjunto de valores legales llamado dominio de la columna. A parte de los
valores del dominio, una columna puede contener el valor nulo (NULL) que indica que no contiene
ningún valor.
IMPORTACIÓN DE DATOS DESDE MICROSOFT SQL SERVER ANALYSIS SERVICES
Análisis Services (un componente de Microsoft SQL Server) que admite la inteligencia empresarial
y es una base de los sistemas de análisis de procesamiento analítico en línea (OLAP), cuadros de
mandos de indicador clave de rendimiento (KPI), extracción de datos y sistemas de informes de
panel. En Excel, puede conectarse fácilmente a una base de datos OLAP de Analysis Services
mediante un proveedor OLAP. (En el grupo Obtener datos externos de la ficha Datos, haga clic en
Desde otros orígenes y, a continuación, en Desde Analysis Services. Un proveedor OLAP es un
conjunto de software que proporciona acceso a un tipo concreto de base de datos OLAP. Este
software puede incluir un controlador de origen de datos y otro software cliente que sea necesario
para conectarse a una base de datos. Para conectarse a un proveedor OLAP, debe usar una tabla
dinámica.
También puede tener acceso a datos OLAP cuando está desconectado del origen de datos OLAP.
Un archivo de cubo sin conexión es un archivo con la extensión .cub que almacena una porción de
datos de origen de una base de datos de servidor OLAP. Use un archivo de cubo sin conexión para
continuar realizando cambios en informes de tabla dinámica y de gráfico dinámico cuando el
servidor no está disponible o se interrumpe la conexión con la red.
Al conectarse a Analysis Services, el Asistente para la conexión de datos muestra tres páginas:
Página 1: Conectar con el servidor de base de datos Use esta página para especificar el servidor
y la forma de iniciar sesión en el servidor de base de datos.
Página 2: Seleccionar una base de datos y una tabla Use esta página para especificar la base de
datos y el cubo.
Página 3: Guardar archivo de datos y conexión Use esta página para especificar y describir el
archivo de conexión y las frases de búsqueda para localizar el archivo.
Importar datos de Analysis Services o PowerPivot
SQL Server 2012 Otras versiones Personas que lo han encontrado útil: 1 de 2 - Valorar este tema
En PowerPivot para Excel, puede utilizar una base de datos de Analysis Services como un origen
de datos para un libro PowerPivot. Esa base de datos puede ser un cubo tradicional, compilado
usando SQL Server Analysis Services u otro libro de PowerPivot* publicado en un servidor de
SharePoint.
*Nota
En resumen, Power Pívot es la nueva herramienta que permite a los usuarios de negocio a tomar
un papel mucho más importante en la creación de ideas que son relevante para la toma de
decisiones. Mientras que el BI tradicional se basa casi por completo en el departamento de TI para
crear los informes, comprender la lógica de negocio y convertir los datos en una idea que podría
ser consumida por los usuarios finales, Power Pívot transforma esto en una solución de BI
impulsada por los usuarios donde los usuarios de negocio pueden realmente definir lo que
realmente quieren de la infraestructura de TI y ser capaz de romper la barrera de entendimiento
entre el personal de TI y el usuario final.
PowerPivot cierra las consultas de procesamiento de ejecución prolongada después de una hora
(3600 segundos). El tiempo de espera está integrado en la aplicación y no se puede cambiar. Si
bien este límite se aplica a todos los orígenes de datos, es más probable que lo encuentre si
importa datos de Analysis Services. Puede evitar temporalmente esta limitación si importa menos
filas cada vez y las combina más adelante. Para obtener más información, vea La importación de
PowerPivot se detiene después de 3600 segundos (o 1 hora).
Requisitos previos
Los cubos de Analysis Services deben ser de la versión SQL Server 2005, SQL Server 2008, SQL
Server 2008 R2 o SQL Server 2012. Se debe tener acceso al cubo en un servidor. No puede
utilizar un cubo local como origen de datos para un libro PowerPivot.
Los libros de PowerPivot que usa como orígenes de datos se deben publicar en un sitio de
SharePoint 2010. El sitio de SharePoint se debe estar ejecutando en un equipo diferente del que
emplea para importar datos.
Debe tener permisos Ver en el sitio de SharePoint para importar datos de los libros PowerPivot.
Elegir un método de importación
Puede usar cualquiera de los siguientes métodos para trabajar con Analysis Services o datos de
PowerPivot en un libro de Excel.
Aplicación
Método
Vínculo
PowerPivot para Excel
Haga clic en De Analysis Services o PowerPivot para importar datos de un cubo de Analysis
Services.
Cómo
PowerPivot para Excel
Haga clic en De Analysis Services o PowerPivot para importar los datos de un libro de PowerPivot
publicado en un servidor de SharePoint.
Cómo Excel:
Haga clic en De otros orígenes en el grupo Obtener datos externos para establecer una conexión
con un libro de PowerPivot publicado en un servidor de SharePoint.
Cómo:
Importar datos desde un cubo
Cualquier dato contenido en una base de datos de SQL Server Analysis Services se puede
importar en un libro PowerPivot. Puede extraer todo el contenido o parte de una dimensión, u
obtener segmentaciones y agregados del cubo, como la suma de ventas, mes por mes, durante el
año actual. Sin embargo, debería tener presente las siguientes restricciones:
Todos los datos que importa de un cubo u otro libro de PowerPivot se reducen. Por consiguiente, si
define una consulta que recupera medidas a lo largo de varias dimensiones, los datos se
importarán con cada dimensión en una columna independiente.
Los datos son estáticos una vez importados. No se actualizan desde el servidor de Analysis
Services a petición. Si desea actualizar un libro para captar los cambios en la base de datos de
Analysis Services, debe crear una programación de la actualización de datos una vez publicado el
libro en SharePoint. Alternativamente, puede actualizar manualmente los datos en PowerPivot para
Excel. Para obtener más información, vea Maneras diferentes de actualizar datos en PowerPivot.
El siguiente procedimiento muestra cómo obtener un subconjunto de datos de un cubo tradicional
en una instancia de Analysis Services. Este procedimiento usa la base de datos de ejemplo
Adventure Works DW Multidimensional 2012 para explicar cómo importar un subconjunto de un
cubo. Si tiene acceso a un servidor de Analysis Services que tiene la base de datos de ejemplo
Adventure Works DW Multidimensional 2012, puede seguir estos pasos para aprender a importar
datos de Analysis Services.
En la ventana de PowerPivot, en el grupo Obtener datos externos, haga clic en Desde base de
datos y seleccione De Analysis Services o PowerPivot.
Se inicia el Asistente para la importación de tablas.
En la página Conectarse con Microsoft SQL Server Analysis Services, en Nombre descriptivo de la
conexión, escriba un nombre descriptivo para la conexión de datos.
En Nombre de servidor o archivo, escriba el nombre del equipo que hospeda la instancia y el
nombre de instancia, por ejemplo, Contoso-srv\CONTOSO.
Nota:
No puede usar un cubo local como un origen de datos; se debe acceder al cubo desde una
instancia de Analysis Services.
Opcionalmente, haga clic en Opciones avanzadas para abrir un cuadro de diálogo donde puede
configurar las propiedades que son específicas del proveedor. Haga clic en Aceptar.
Haga clic en la flecha abajo a la derecha de la lista Nombre de la base de datos y seleccione una
base de datos de Analysis Services en la lista. Por ejemplo, si tiene acceso a la base de datos de
ejemplo Adventure Works DW Multidimensional 2012, seleccione Adventure Works DW
Multidimensional 2012.
Haga clic en Probar conexión para comprobar que el servidor de Analysis Services está disponible.
Haga clic en Siguiente.
En la página Especificar una consulta MDX, haga clic en Diseño para abrir un generador de
consultas MDX.
En este paso, arrastre en el área de diseño de consulta grande todas las medidas, atributos de
dimensión, jerarquías y miembros calculados que desea importar en el libro PowerPivot.
Si tiene una instrucción de MDX que desea usar, péguela en el cuadro de texto y haga clic en
Validar para asegurarse de que la instrucción funcionará. Para obtener más información acerca de
cómo generar consultas MDX, vea Diseñador de consultas MDX de Analysis Services
(PowerPivot).
Para este procedimiento, usando el cubo de ejemplo de Adventure Works como ejemplo, haga lo
siguiente:
En el panel Meta data, expanda Measures y, a continuación, expanda Sales Summary.
Arrastre Average Sales Amount hasta el panel de diseño grande.
En el panel Metadata, expanda la dimensión Product.
Arrastre Product Categories hacia la izquierda de Average Sales Amount en el área de
diseño grande.
En el panel Metadata, expanda la dimensión Date y, a continuación, expanda Calendar.
Arrastre Date. Calendar Year hacia la izquierda de Category en el área de diseño grande.
Opcionalmente, agregue un filtro para importar un subconjunto de los datos. En el panel superior
derecho del diseñador, para Dimensión, arrastre Date al campo de dimensión. En Hierarchy,
seleccione Date. Calendar Year; para Operador, seleccione Range (Exclusive); para Filter
Expresión, haga clic en la flecha abajo y seleccione Year 2005.
De este modo se crea un filtro en el cubo para que excluya los valores de 2005.
Haga clic en Aceptar y revise la consulta MDX que se creó con el diseñador de consultas.
Escriba un nombre descriptivo para el conjunto de datos. Este nombre se usará como nombre de
tabla en el libro. Si no asigna un nuevo nombre, de forma predeterminada los resultados de la
consulta se guardan en una nueva tabla denominada Query.
Haga clic en Finalizar.
Cuando los datos han terminado de cargarse, haga clic en Cerrar.
Después de importar los datos en la ventana de PowerPivot, puede comprobar el tipo de datos
seleccionando cada columna y viendo Tipo de datos en el grupo Formato en la cinta de opciones.
Asegúrese de comprobar el tipo de datos de las columnas que contienen datos numéricos o
financieros. En ocasiones, PowerPivot cambiará el tipo de datos a Text si encuentra valores vacíos.
Puede utilizar la opción Tipo de datos para corregir el tipo de datos si los datos numéricos o
financieros tienen asignado un tipo equivocado.
Importar datos desde un libro PowerPivot
En la ventana de PowerPivot, en el grupo Obtener datos externos, haga clic en Desde base de
datos y seleccione De Analysis Services o PowerPivot.
Se inicia el Asistente para la importación de tablas.
En Conectarse con Microsoft SQL Server Analysis Services, en Nombre descriptivo de la conexión,
escriba un nombre descriptivo para la conexión de datos. Usar nombres descriptivos para la
conexión puede ayudarle a recordar cómo se usa la conexión.
En Nombre de servidor o archivo:, escriba la dirección URL del archivo .xlsx publicado. Por
ejemplo, http://Contoso-srv/Shared Documents/ContosoSales.xlsx.
Nota
No puede usar un libro de PowerPivot local como un origen de datos; el libro de PowerPivot se
debe publicar en un sitio de SharePoint.
Opcionalmente, haga clic en Opciones avanzadas para abrir un cuadro de diálogo donde puede
configurar las propiedades que son específicas del proveedor. Haga clic en Aceptar.
Haga clic en Probar conexión para comprobar que el libro de PowerPivot está disponible.
Haga clic en Siguiente.
Haga clic en Diseño.
Compile la consulta arrastrando medidas, atributos de dimensión o jerarquías hacia el área de
diseño grande. Opcionalmente, use el panel de filtro en la esquina superior derecha para
seleccionar un subconjunto de datos para la importación. Consulte los pasos de la sección anterior
para obtener un ejemplo de cómo compilar la consulta.
Haga clic en Aceptar.
Haga clic en Validar.
Haga clic en Finalizar.
Los datos de PowerPivot se copian en el libro y de almacenan en un formato comprimido,
separado del libro original. Una vez importados los datos, se cierra la conexión con el libro. Para
volver a consultar los datos originales, puede actualizar el libro. Para obtener más información, vea
Maneras diferentes de actualizar datos en PowerPivot.
Conectar con un libro PowerPivot como un origen de datos externo
Puede utilizar los datos de PowerPivot como un origen de datos externo en Excel sin incrustar los
datos en el libro. No necesita PowerPivot para Excel para este escenario, pero debe tener la
versión correcta del proveedor OLE DB de Analysis Services. Para obtener la versión más reciente
del proveedor, descargue e instale el proveedor OLE DB de Microsoft SQL Server 2008 R2
Analysis Services desde la página SQL Server 2008 R2 Feature Pack en el sitio web de Microsoft.
En la pestaña Datos de Excel, en el grupo Obtener datos externos, haga clic en De otros orígenes.
Haga clic en De Analysis Services.
En el cuadro Nombre del servidor, escriba la dirección del libro de PowerPivot. La dirección debe
incluir el archivo .xlsx que contiene los datos (por ejemplo, http://constoso-srv/team site/shared
documents/contoso-quarterly-sales.xlsx).
Haga clic en Siguiente.
En Seleccionar base de datos y tabla, haga clic en Finalizar.
En Importar datos, especifique cómo desea que aparezcan los datos (por ejemplo, elija informe de
tabla dinámica).
Haga clic en Propiedades y a continuación, abra la pestaña Definición para comprobar que la
cadena de conexión especifica Provider=MSOLAP .4. Este paso comprueba que tiene el proveedor
OLE DB correcto.
Haga clic en Aceptar y, a continuación, haga clic en Finalizar para preparar la conexión.
Una lista de campos de tablas dinámicas aparece en el área de trabajo que contiene campos del
libro PowerPivot.
Interacción de PowerPivot con cubos de Analysis Services
Cuando usa el asistente para conectarse a un origen de datos de Analysis Services, el motor
analítico en memoria xVelocity interno (VertiPaq) crea una consulta MDX para la base de datos
multidimensional Analysis Services y, a continuación, descarga los datos en el libro. Los datos no
se pueden actualizar y no se actualizan automáticamente cuando los datos del cubo cambian.
Los datos que importa a un libro de PowerPivot son autónomos tras la fase de importación. En
lugar de considerar un libro de PowerPivot como una superficie de exploración para un cubo
existente, debería considerarlo como un área de trabajo donde puede obtener subconjuntos de
datos del cubo útiles y derivar de ahí nuevos análisis que son independientes del cubo y de otros
orígenes de datos.
Si desea ver las sentencias MDX* que genera el libro PowerPivot durante importación, puede crear
un archivo de seguimiento. Para obtener información acerca de cómo crear un archivo de
seguimiento, vea Cuadro de diálogo Opciones y diagnóstico de PowerPivot.
Si está acostumbrado a trabajar con cubos de Analysis Services en Excel, debería saber que
algunas características de Excel no se pueden usar con libros PowerPivot. Al conectarse a un
cubo** de PowerPivot, no se admiten las características de Excel siguientes:
Cubos sin conexión
Agrupación en tablas dinámicas
Comando de obtención de detalles *NOTA: El lenguaje MDX permite realizar consultas con una sintaxis parecida al lenguaje natural. Para ejemplificar las consultas y la sintaxis trabajaremos con un ejemplo de un cubo.
Imaginamos un cubo de ventas con las siguientes dimensiones: Dimensión temporal de las ventas con niveles de año y mes. Dimensión productos vendidos con niveles de familia de productos y productos. Dimensión de medidas con importe de las ventas y unidades vendidas ** NOTA: Los cubos de Analysis Services deben ser de la versión SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 o SQL Server 2012. Se debe tener acceso al cubo en un servidor. No puede utilizar un cubo local como origen de datos para un libro PowerPivot. Los libros de PowerPivot que usa como orígenes de datos se deben publicar en un sitio de SharePoint 2010. El sitio de SharePoint se debe estar ejecutando en un equipo diferente del que emplea para importar datos. Debe tener permisos Ver en el sitio de SharePoint para importar datos de los libros PowerPivot.
IMPORTACIÓN DE DATOS XML
Excel facilita la importación de datos XML (Lenguaje de marcado extensible) creados a partir de
otras bases de datos y aplicaciones, asigna elementos XML de un esquema XML a celdas de una
hoja de cálculo y exporta datos XML revisados con vistas a la interacción con otras bases de datos
y aplicaciones. Estas nuevas características XML convierten a Excel en un generador de archivos
de datos XML con una interfaz de usuario familiar.
Gracias a las asignaciones XML, es posible agregar, identificar y extraer partes concretas de datos
comerciales de los documentos de Excel con facilidad. Por ejemplo, una factura que contenga el
nombre y la dirección de un cliente o un informe que contenga los resultados financieros del último
trimestre ya no son informes estáticos. Esta información se puede importar fácilmente desde bases
de datos y aplicaciones, revisar y exportar a las mismas bases de datos y aplicaciones o a otras
distintas.
Situaciones clave de XML
Las características de XML están diseñadas para las siguientes situaciones clave:
Aumentar la funcionalidad de las plantillas existentes de Excel al asignar elementos XML a celdas
existentes. Eso facilita la especificación y la extracción de datos XML en las plantillas sin necesidad
de volver a diseñarlas.
Usar datos XML como entrada para los modelos de cálculo existentes al asignar elementos XML a
las hojas de cálculo existentes.
Importar archivos de datos XML a un nuevo libro.
Importar datos XML de un servicio Web a la hoja de cálculo de Excel.
Exportar datos de las celdas de asignación a archivos de datos XML independientes de otros datos
del libro.
Proceso básico para el uso de datos XML en Excel
El siguiente diagrama muestra cómo los diferentes archivos y operaciones trabajan conjuntamente
cuando se utiliza XML con Excel. Básicamente, el proceso se compone de cinco fases.
Información general sobre el trabajo de Excel con datos XML
Paso 1 Agregar un archivo de esquema XML (.xsd) a un libro.
Paso 2 Asignar elementos de esquema XML a celdas individuales o listas XML.
Paso 3 Importar un archivo de datos XML (.XML) y enlazar los elementos XML con celdas de
asignación.
Paso 4 Especificar datos, mover celdas de asignación y aprovechar la funcionalidad de Excel sin
perder la estructura y las definiciones XML.
Paso 5 Exportar datos revisados desde celdas de asignación a un archivo de datos XML.
Uso de datos XML
Cuando importa el contenido de un archivo de datos XML a una asignación XML existente en el
libro, los datos del archivo se enlazan con una asignación XML almacenada en el libro. Eso
significa que cada elemento de datos del archivo de datos XML tiene un elemento correspondiente
en el esquema XML que se ha asignado desde un archivo de esquema XML o un esquema
deducido. Cada asignación XML solo puede tener un enlace de datos XML, y un enlace de datos
XML está enlazado con todas las asignaciones creadas desde una única asignación XML.
Puede abrir el cuadro de diálogo Propiedades de la asignación XML, (en el grupo XML de la ficha
Programador, haga clic en Propiedades de la asignación), donde encontrará tres opciones (todas
activadas de manera predeterminada) que puede establecer o desactivar para controlar el
comportamiento de un enlace de datos XML:
Validar datos con el esquema para importar y exportar Especifica si Excel valida datos en la
asignación XML al importar datos. Establezca esta opción si desea asegurarse de que los datos
XML que importe se ajusten al esquema XML.
Sobrescribir los datos existentes con los nuevos Especifica si los datos se sobrescriben al
importar datos. Establezca esta opción si desea sustituir los datos actuales por datos nuevos, por
ejemplo, si hay datos actualizados en el nuevo archivo de datos XML.
Anexar los nuevos datos a las listas XML existentes Especifica si el contenido del origen de datos
se anexa a los datos existentes en la hoja de cálculo. Establezca esta opción, por ejemplo, si está
consolidando datos de varios archivos de datos XML parecidos en una lista XML o si no desea
sobrescribir el contenido de una celda que contiene una función.
Es posible importar a Excel datos de archivos creados con otros programas, con Excel puedes
abrir fácilmente archivos de texto, guardados con extensión txt y con extensión csv,(además de
otros formatos como SQL, Analysis Services, XML,…). En este caso te indico txt porque la mayoría
de los programas informáticos pueden guardar archivos en este formato.
Las utilidades son enormes, así como el ahorro de tiempo, el aumento de productividad, la
eliminación de errores,…Y como ejemplo de la aplicación que le puedes dar entre otras importar
datos de cuentas de correo o números de teléfono, importar cuadro de existencias generado por el
programa de almacén, importar los datos del programa de contabilidad cuando este no te los da en
Excel.
Para llevar a cabo este proceso los pasos son muy similares en las diferentes versiones Excel,
presta atención y de verdad te recomiendo que lo uses asiduamente.
Pasos a seguir para importar a Excel datos de archivos con formato txt:
Abre el programa Excel.
A continuación en pulsa en Archivo y luego en Abrir, en Excel 2007 lo encontrarás en el botón de
Office, (también en la pestaña Datos, Obtener datos externos, Desde texto).
En la ventana que se despliega, el cursor del ratón se sitúa en la parte inferior, al lado de donde
pone Nombre de Archivo: examina en tu computador, a través de los iconos de la parte izquierda y
central de la ventana, dónde tienes el archivo que quieres pasar a Excel. Para ello has de
seleccionar en Tipo de archivo: Todos los archivos o Archivos de texto.
Pulsa Abrir.
Aparece una nueva ventana que es el Asistente para importar texto.
Este asistente consta de tres pasos muy sencillos, en ellos vas a definir las características de la
importación.
Paso 1.- Ver imagen ampliada. Lo primero que te pide es que indiques el Tipo de los datos originales: Delimitados, los datos del archivo de texto están separados por tabuladores o por comas. De ancho fijo, en este caso los datos están separados solo por espacios. En principio esta opción se deja por defecto la que interprete Excel. En la parte central de la ventana puedes establecer:
Comenzar a importar en la fila: 1, por defecto. Si quieres que los datos se empiecen a importar
desde otra fila has de indicarlo aquí, por ejemplo si las cuatro primeras filas están vacías o tienes
datos que no quieres importar en esta casillas pondrás el número 5.
Origen del archivo: Para mí el más adecuado es Windows (ANSI) es el que mejor me interpreta los
caracteres.
Por último tienes la vista previa del archivo de texto a importar, con las barras de desplazamiento a
la derecha y abajo desplázate por el mismo para ver cómo va quedando a lo largo de estos tres
pasos del asistente.
Pulsa Siguiente.
En principio has de dejar todos los parámetros preestablecidos, en la parte central verás
separaciones con líneas, haz clic dos veces y podrás crear y borrar líneas para ajustar los datos a
hoja de cálculo según tus necesidades. También puedes desplazar las líneas de separación
pinchando sobre cada una de ellas y sin soltar desplazándote con el ratón.
Pulsa Siguiente.
En este paso has de establecer el tipo de datos que tiene cada columna, por defecto el programa
interpreta que el tipo de datos es General, donde los datos que interpreta como valores numéricos
los pone en formato número, ídem con texto y fechas. Aun así yo siempre hago alguna
puntualización.
Las opciones que tienes son General, Texto, Fecha, y también puedes saltar columnas con No
importar columna.
Lo más habitual es que tengas una columna de fechas y por tanto se lo tendrás que indicar. Para
ello situado en la columna donde tengas las fechas pon en negro el punto al lado de Fecha, a
continuación podrás indicar el formato de fecha que uses generalmente, (DMA para día, mes, año
o por ejemplo MDA para mes, día, año).
Truco: Si quieres seleccionar varias columnas a la vez pulsa la tecla Mayús y sin soltar las
columnas que desees.
Pulsa Finalizar.
En tu hoja de cálculo se han colocado los datos de tu archivo de texto. Puedes trabajar con ellos
desde esta hoja electrónica y realizar todas las acciones que te permite Excel, cambiar fuente,
introducir formulas,….Te aconsejo que primero de todo Guarda tu archivo en formato Excel para
evitar tener que repetir todo el proceso.
IMPORTACIÓN DE DATOS MEDIANTE EL ASISTENTE PARA LA CONEXIÓN DE DATOS
Puede usar el Asistente para la conexión de datos para conectarse a un origen de datos externos
OLE DB u ODBC que ya haya sido definido. Para abrir el Asistente para la conexión de datos, en el
grupo Obtener datos externos de la ficha Datos, haga clic en De otras fuentes y luego en Desde el
Asistente para la conexión de datos.
Si elige la opción de origen de datos Otro o avanzado en el Asistente para la conexión de datos,
puede ver una lista de proveedores OLE DB disponibles en el cuadro de diálogo Propiedades de
vínculo de datos. Además, el proveedor OLE DB de Microsoft para controladores ODBC también
permite el acceso a orígenes de datos ODBC. Para obtener más información acerca de cómo usar
cada ficha de este cuadro de diálogo, haga clic en Ayuda en el cuadro de diálogo Propiedades de
vínculo de datos.
En general, para definir la información de conexión en el cuadro de diálogo Propiedades de vínculo
de datos, siga este procedimiento:
Haga clic en la pestaña Proveedor, seleccione el proveedor OLE DB y, a continuación, haga clic en
Siguiente. Se mostrará la ficha Conexión, en la cual podrá escribir información de conexión
específica para el proveedor OLE DB.
Cada proveedor OLE DB define información de conexión específica. Por ejemplo, el proveedor
OLE DB de Microsoft para SQL Server requiere un nombre de servidor, una ubicación del servidor
y un nombre de usuario. Es posible que también desee definir información adicional, como una
contraseña, o si desea usar la seguridad integrada de Microsoft Windows.
Para proporcionar información adicional, como la configuración de red y los permisos de acceso,
haga clic en la pestaña Avanzadas.
Para definir las propiedades de inicialización del proveedor OLE DB, haga clic en la pestaña Todo.
NOTA No se puede filtrar ni unir datos en el Asistente para la conexión de datos.
IMPORTACIÓN DE DATOS MEDIANTE MICROSOFT QUERY
Microsoft Query* también se puede usar para importar datos. (En el grupo Obtener datos externos
de la ficha Datos, haga clic en Desde otros orígenes y, a continuación, en Desde Microsoft Query).
Puede usar Microsoft Query para configurar orígenes de datos ODBC para recuperar datos. En
Microsoft Query, puede usar el Asistente para consultas para crear una consulta simple o puede
usar criterios avanzados en Consulta para crear una consulta más compleja y hacer lo siguiente:
*NOTA:
Microsoft Query es un programa incluido en Excel que nos permite importar datos externos a
nuestras hojas de cálculo lo cual nos evitará ingresar manualmente los datos provenientes de
fuentes de datos externar ya sean bases de datos corporativas o archivos de nuestros de texto o
Excel.
Podemos encontrar a Microsoft Query dentro de la ficha Datos en el menú desplegable De otras
fuentes.
Filtrar filas o columnas de datos antes de llevarlos a Excel.
Crear una consulta de parámetros.
Ordenar datos antes de llevarlos a Excel.
Unir varias tablas.
Microsoft Query proporciona un front-end simple, fácilmente accesible desde Excel, para realizar
estas tareas de consultas especializadas.
Importación de datos mediante programación y mediante el uso de funciones
Si es programador, existen varios enfoques dentro de Excel que puede tomar para importar datos:
Puede usar Visual Basic* para Aplicaciones para obtener acceso a un origen de datos externo.
Según el origen de datos, puede usar Objetos de datos ActiveX u Objetos de acceso a datos para
recuperar los datos. También puede definir una cadena de conexión en el código que especifique
la información de conexión. Usar una cadena de conexión es útil, por ejemplo, cuando desea evitar
que se les exija a los administradores del sistema o a los usuarios que primero creen un archivo de
conexión o para simplificar la instalación de la aplicación.
*NOTA:
Visual Basic es un lenguaje de programación dirigido por eventos, desarrollado por Alan Cooper
para Microsoft. Este lenguaje de programación es un dialecto de BASIC, con importantes
agregados. Su primera versión fue presentada en 1991, con la intención de simplificar la
programación utilizando un ambiente de desarrollo completamente gráfico que facilitara la creación
de interfaces gráficas y, en cierta medida, también la programación misma.
Aunque Visual Basic es de propósito general, también provee facilidades para el desarrollo de
aplicaciones de bases de datos usando Data Access Objects, Remote Data Objects o ActiveX Data
Objects.
Visual Basic contiene un entorno de desarrollo integrado o IDE que integra editor de textos para
edición del código fuente, un depurador, un compilador (y enlazador) y un editor de interfaces
gráficas o GUI
Si importa datos de una base de datos de SQL Server, considere la posibilidad de usar SQL Native
Client, que es una interfaz de programación de aplicaciones (API) de acceso de datos
independiente que se usa para OLE DB y ODBC. Combina el proveedor OLE DB de SQL y el
controlador ODBC de SQL en una misma biblioteca de vínculos dinámicos (DLL) nativa, mientras
que también proporciona nuevas funciones que son independientes y distintivas de Microsoft Data
Access Components (MDAC). Puede usar SQL Native Client para crear nuevas aplicaciones o para
mejorar aplicaciones existentes que pueden aprovechar las características más nuevas de SQL
Server, como Conjuntos de resultados activos múltiples (MARS), Tipos definidos por el usuario
(UDT) y compatibilidad con el tipo de datos XML.
La función RTD* recupera datos en tiempo real desde un programa compatible con la
automatización COM. El complemento de automatización COM de RTD debe crearse y registrarse
en un equipo local.
NOTA*:
Un RTD (del inglés: resistance temperature detector) es un detector de temperatura resistivo, es decir, un sensor de temperatura basado en la variación de la resistencia de un conductor con la temperatura. Su símbolo es el siguiente, en el que se indica una variación lineal con coeficiente de
temperatura positivo.
Al calentarse un metal habrá una mayor agitación térmica, dispersándose más los electrones y reduciéndose su velocidad media, aumentando la resistencia. A mayor temperatura, mayor agitación, y mayor resistencia.
La variación de la resistencia puede ser expresada de manera polinómica como sigue a continuación. Por lo general, la variación es bastante lineal en márgenes amplios de temperatura. La función SQL.REQUEST conecta con un origen de datos externo y ejecuta una consulta desde
una hoja de cálculo. SQL.REQUEST devuelve el resultado como una matriz, sin necesidad de
programar una macro. Si esta función no está disponible, debe instalar el programa de
complemento ODBC de Microsoft Excel (XLODBC.XLA). Puede instalar el complemento desde
Office.com.
EJEMPLO PRÁCTICO TOMANDO COMO FUENTE DE DATOS DE ORIGEN ACCESS
Supongamos que tenemos en el directorio C: \ACCESS, la base de datos denominada
BASE1.MDB que contiene la tabla TABLA1 con los campos NOMBRE y CODIGO con 100
registros. En este caso queremos recuperar en la celda A1 de nuestra hoja, el contenido del campo
NOMBRE cuyo campo CODIGO sea igual a 20.
Como la función es matricial, devolverá un conjunto de datos. Por tanto seleccionamos en la hoja
el rango de celdas donde queremos que se muestre la información partiendo para nuestro ejemplo
de la celda A1 (por ejemplo A1:A10) y escribimos:
=SQL.REQUEST ("DSN=Bases de datos de MS -
Access;DBQ=C:\access\base1.mdb;FIL=redisam";A1;2;"select - nombre from tabla1 where
codigo=20"; VERDADERO)
NOTA: concluir la función con las teclas CTRL+MAY+ENTER ya que se trata de una fórmula
matricial.
En A1 aparecerá NOMBRE y en A2 y sucesivas, todos los nombres de los registros cuyo código es
igual a 20.
Si SQL.REQUEST no logra acceder a la fuente de datos utilizando el argumento conexión,
devuelve el valor de error #N/A
MANEJO DE LAS FUNCIONES DE EDICION, SUSTITUCION Y ALTA DE DATOS
DESDE EXCEL
Menú Edición: es una de herramienta muy útil que tiene como objetivo las Funciones de
manipulación directa de los elementos integrantes del documento, las encontraremos en este
menú. Los dos primeros comandos al comienzo de este menú permiten:
deshacer o
rehacer acciones que se han
realizado al texto.
Cortar, Copiar, y Pegar
tienen
un carácter de utilidad bastante
notable, ya que permiten con
suma facilidad el traslado de
elementos de una región del
documento a otra.
Esta opción, al igual que todas las
utilidades de Excel, se puede
llevar a cabo de diversas formas:
o A través del menú: EDICIÓN/COPIAR y EDICIÓN/PEGAR. Mediante combinaciones de
teclas: Ctrl + C y Ctrl + V
o Haciendo clic con el ratón en la celda de origen y simultáneamente la tecla Ctrl y
arrastrando hasta la celda de destino.
o Utilizando las opciones del "menú emergente"
Borrar como podrá suponerse elimina el texto que se ha escrito previamente.
Seleccionar todo, realiza una selección de todo el documento de principio a fin, esto
puede ahorrar tiempo en el caso de que se desee aplicar un formato o cambiar el tipo de
letra a todo el documento.
Buscar y Reemplazar permiten la localización de una cadena de texto específica y si se
desea ubicarla para reemplazar por alguna otra.
Ir a le indica a Excel que debe desplazarse de acuerdo al criterio del elemento que hemos
escogido para que realice el desplazamiento.
Arrastrar y soltar datos en Excel
Arrastrar y soltar es una técnica de ratón que se puede utilizar en Excel para mover una
celda (o un rango) a otra ubicación dentro de la hoja. Esta misma técnica también la podemos
utilizar para copiar celdas.
Colocar adecuadamente el puntero del ratón
Lo primero que demos hacer es seleccionar la celda, o rango de celdas, que deseamos mover y
posteriormente posicionar el puntero del ratón en alguna de las orillas de la celda. Sabrás que
estás en la posición adecuada cuando el puntero se transforme en unas flechas negras
perpendiculares.
Arrastrar celdas a su destino
Con el puntero del ratón sobre alguno de los bordes, pulsa y arrastra las celdas hacia la nueva
ubicación dentro de la hoja. Observarás el contorno de las celdas moverse junto con el ratón hacia
la nueva posición de manera que puedas ver la nueva ubicación adonde se moverán las celdas.
Finalmente suelta el botón del ratón para mover las celdas a la nueva posición.
Copiar las celdas
Si lo que deseas es copiar las celdas en lugar de moverlas el procedimiento es exactamente el
mismo con la diferencia que se deberá mantener pulsada la tecla CTRL todo el tiempo. Al oprimir la
tecla CTRL, el cursor ya no será unas flechas negras perpendiculares sino que será el puntero de
flecha blanca con un pequeño símbolo “+”.
Finalmente debo comentar que si
al mover o copiar celdas con este método coinciden con
otras celdas que ya tienen contenido entonces Excel
mostrará un mensaje de advertencia preguntando si
deseamos reemplazar el contenido de las celdas destino.
Pulsa el botón Cancelar en caso de que desees evitar el reemplazo de las celdas o de lo contrario
pulsa Aceptar para que se realice el cambio.
Rellenar datos automáticamente en celdas de hojas de cálculo
Para escribir datos de forma rápida, puede configurar Microsoft Office Excel de manera que repita
los datos o puede rellenar los datos de manera automática.
Repetir automáticamente elementos ya escritos en la columna
Si los primeros caracteres que se escriben en una celda coinciden con una entrada existente en
esa columna, Microsoft Excel completará automáticamente los caracteres restantes. Excel sólo
completa automáticamente las entradas que contengan texto o una combinación de texto y
números. Las entradas que contengan sólo números, fechas u horas no se completan
automáticamente.
Siga uno de los procedimientos siguientes:
Para aceptar una entrada propuesta, presione ENTRAR.
La entrada completa coincide exactamente con el modelo de mayúsculas y minúsculas de la
entrada existente.
Para reemplazar los caracteres escritos automáticamente, continúe escribiendo.
Para eliminar los caracteres escritos automáticamente, presione RETROCESO.
Utilizar el controlador de relleno para rellenar datos
Se puede utilizar el comando Rellenar para rellenar datos en las celdas de una hoja de cálculo.
También se puede configurar Excel para que continúe automáticamente una serie de números,
combinaciones de números y texto, fechas o períodos de tiempo, en función de un modelo
establecido. Sin embargo, para rellenar rápidamente varios tipos de series de datos, seleccione las
celdas y arrastre el controlador de relleno .
Rellenar datos en celdas adyacentes
Puede utilizar el comando Rellenar para rellenar la celda activa o el rango seleccionado con el
contenido de una celda o rango adyacente, o bien, rellenar rápidamente las celdas adyacentes
arrastrando el controlador de relleno .
Rellenar la celda activa con el contenido de una celda adyacente
1. Seleccione una celda vacía debajo, a la derecha, por encima o a la izquierda de la celda que contiene
los datos que va a utilizar para rellenar dicha celda.
2. En el grupo Modificar de la ficha Inicio, haga clic en Rellenar y, a continuación,
en Abajo, Derecha, Arriba o Izquierda.
SUGERENCIA Para rellenar una celda rápidamente con el contenido de la celda situada encima o a la
izquierda, puede presionar CTRL+J o CTRL+D.
Teclas de función
TECLA DESCRIPCIÓN
F1 Muestra el panel de tareas Ayuda de Microsoft Office Excel.
CTRL+F1 muestra u oculta la cinta de opciones, que es un componente de la Interfaz
de usuario de Microsoft Office.
ALT+F1 crea un gráfico a partir de los datos del rango actual.
ALT+MAYÚS+F1 inserta una hoja de cálculo nueva.
F2 Modifica la celda activa y coloca el punto de inserción al final del contenido de la celda.
También mueve el punto de inserción a la barra de fórmulas cuando la edición en una
celda está desactivada.
MAYÚS+F2 agrega o modifica un comentario de celda.
CTRL+F2 muestra la ventana Vista preliminar.
F3 Muestra el cuadro de diálogo Pegar nombre.
MAYÚS+F3 muestra el cuadro de diálogo Insertar función.
F4 Repite el último comando o acción, si es posible.
CTRL+F4 cierra la ventana del libro seleccionado.
F5 Muestra el cuadro de diálogo Ir a.
CTRL+F5 restaura el tamaño de ventana de la ventana del libro seleccionado.
F6 Cambia entre la hoja de cálculo, la cinta de opciones, el panel de tareas y los controles
de Zoom. En una hoja de cálculo que se ha dividido (menú Ver, Administrar esta
ventana, Inmovilizar paneles, comando Dividir ventana), F6 incluye los paneles
divididos cuando se alterna entre los paneles y el área de la cinta de opciones.
MAYÚS+F6 cambia entre la hoja de cálculo, los controles de Zoom, el panel de tareas
y la cinta de opciones.
CTRL+F6 cambia a la ventana del libro siguiente cuando hay más de una ventana del
libro abierta.
F7 Muestra el cuadro de diálogo Ortografía para revisar la ortografía de la hoja de cálculo
activa o del rango seleccionado.
CTRL+F7 ejecuta el comando Mover en la ventana del libro cuando no está
maximizada. Utilice las teclas de dirección para mover la ventana y, cuando haya
acabado, presione ENTRAR o ESC para cancelar.
F8 Activa o desactiva el modo extendido. En el modo extendido aparece Selección
extendida en la línea de estado y las teclas de dirección extienden la selección.
MAYÚS+F8 le permite agregar una celda o rango de celdas no adyacentes a una
selección de celdas utilizando las teclas de dirección.
CTRL+F8 ejecuta el comando Tamaño (en el menú Control de la ventana del libro)
cuando una ventana del libro no está maximizada.
ALT+F8 muestra el cuadro de diálogo Macro para crear, ejecutar, modificar o eliminar
una macro.
F9 Calcula todas las hojas de cálculo de todos los libros abiertos.
MAYÚS+F9 calcula la hoja de cálculo activa.
CTRL+ALT+F9 calcula todas las hojas de cálculo de todos los libros abiertos,
independientemente de si han cambiado desde el último cálculo.
CTRL+ALT+MAYÚS+F9 vuelve a comprobar fórmulas dependientes y calcula todas
las celdas de todos los libros abiertos, incluidas las celdas que no tienen marcado que
sea necesario calcularlas.
CTRL+F9 minimiza la ventana del libro hasta convertirla en un icono.
F10 Activa o desactiva la información de los métodos abreviado de teclado.
MAYÚS+F10 muestra el menú contextual de un elemento seleccionado.
ALT+MAYÚS+F10 muestra el menú o mensaje de una etiqueta inteligente. Si hay más
de una etiqueta inteligente, cambia a la siguiente y muestra su menú o mensaje.
CTRL+F10 maximiza o restaura la ventana de libro seleccionada.
F11 Crea un gráfico a partir de los datos del rango actual.
MAYÚS+F11 inserta una hoja de cálculo nueva.
ALT+F11 abre el Editor de Microsoft Visual Basic, donde puede crear una macro
utilizando Visual Basic para Aplicaciones (VBA).
F12 Muestra el cuadro de diálogo Guardar como.
En la hoja de cálculo podemos introducir texto, números y funciones.
Nos situamos marcando con el ratón por ejemplo en la celda A2 y escribimos
PRESUPUESTO ANUAL.
• Al introducir datos en una celta aparece en la Barra el símbolo X que nos permite cancelar o eliminar lo
que acabamos de introducir si nos hemos equivocado o arrepentido.
• Y aparece el símbolo para confirmar o introducir la información. Otra forma de confirmar es simplemente
pulsando Enter en nuestro teclado.
Realizar correcciones
Para cambiar el contenido de una celda que ya he confirmado:
• Pulso sobre la celda que quiero cambiar. El programa muestra el contenido en la parte superior.
• Hago clic sobre la parte de la Barra que muestra el contenido de la celda.
• Y pulsando en la tecla "Supr" borro el contenido o pulso en la tecla de retroceso que se
encuentra encima de Enter en el teclado para realizar modificaciones.
Por ejemplo he escrito PRESUPUESTO ANUAL pero descubro que tenía que poner PRESUPUESTO
Nombrar celdas y rangos
Excel permite un manejo fluido y sencillo de las celdas. Tanto a través de las columnas, las filas o
manejando las celdas individualmente.
Pero Excel también tiene en los Rangos otra herramienta sumamente potente para el manejo de
información. La principal característica de los rangos es que se les puede dar un nombre, a un conjunto de
celdas o incluso a una sola celda, que sería un conjunto de solo una celda.
¿Y por qué es tan importante el hecho de poder dar nombre a los rangos? Bueno, un rango no es más
que un conjunto de celdas definido por sus celdas superior izquierda e inferior derecha, separadas por dos
puntos. Es fácil reconocer el rango situándolo mentalmente en la hoja de cálculo. Pero si la hoja es muy
grande ¿Podríamos recordar exactamente la posición de todos los conjuntos y subconjuntos de celdas?
Es más sencillo hacer referencia a Ventas, que Z51:AZ54. y a Compras que X32:Z37
Veamos un ejemplo, para asignar un nombre, el primer paso es seleccionar el rango. C4:E4. Una vez que
esté marcado escribiremos el nombre que deseemos para el rango en la casilla del cuadro de nombres.
Escribiremos Meses en la casilla y pulsaremos la tecla intro.
Ya tenemos nuestro primer rango.
Veamos ahora sus ventajas, por ejemplo llevemos el puntero a alguna celda lejana por ejemplo la H49.
Si ahora quisiéremos ir de nuevo a la posición de los meses, pero no nos acordamos de su rango solo
tenemos que pulsar la tecla F5
Aquí se mostrara todos los rangos definidos, solo tendremos que seleccionar y aceptar. Para que Excel
nos lleve de a la posición deseada
Los rangos tienen muchas más utilidades que se vuelven más y más imprescindibles cuanto más grande y
compleja es la hoja de cálculo en la que estamos trabajando
Dar Formato a los Datos introducidos
Tenemos que seleccionar las celdas sobre las que queremos aplicar el formato. Por ejemplo hemos
introducido los datos de nuestro presupuesto mensual y
marcamos las celdas a las que deseamos dar formato.
• Vamos a realizar un ejemplo en el que:
o A) Pondremos una Línea de Contorno a nuestro presupuesto.
o B) Cambiaremos el tipo de letra a Verdana y aumentaremos el tamaño de las letras.
o C) Pondremos "PRESUPUESTO MENSUAL" en negrita.
o D) En azul las letras correspondientes a los conceptos de gasto.
o E) Y el fondo de las celdas con números lo pondremos de color verde claro
• Podemos utilizar el menú Formato de la Barra superior o Barra menú.
• También podemos acceder directamente a las funciones de formato más utilizadas teniendo activada en
pantalla la Barra de Formato. Hemos pulsado en el botón de flecha "Más botones" para que se visualicen
el resto de opciones.
Si no aparece en su pantalla la Barra de Formato la activamos con el comando Ver de la Barra de Menú
Ejemplo de Formato
A) Vamos a poner una línea de contorno, es decir un recuadro alrededor de varias celdas.
• Pulsamos sobre Formato y Celdas una vez que tenemos seleccionadas las celdas sobre las que
queremos aplicar el formato.
• Y aparece el formulario Formato de celdas.
o En nuestro ejemplo en Línea hemos seleccionado una gruesa.
o Pulsamos sobre Contorno
o Y Pulsamos Aceptar
Y nuestro presupuesto nos queda recuadrado.
B) Vamos a cambiar el tipo de letra a Verdana y aumentar a 16 el tamaño de las letras...
• Seleccionamos las celdas
• En la Barra de Formato pulsamos sobre el botón que muestra un triángulo para cambiar el tipo de letra. Y
seleccionamos por ejemplo Verdana.
• Y aumentamos el tamaño de las letras a 16, pulsando sobre el siguiente botón y seleccionado.
C) Poner "PRESUPUESTO MENSUAL" en negrita.
• Seleccionamos la celda que queremos poner en negrita.
• Y pulsamos la N de la Barra de Formato
D) Vamos en nuestro ejemplo a poner de color azul las letras correspondientes a los conceptos de gastos.
• Seleccionamos desde la celda donde pusimos Casa a la celda Ropa.
• Si nuestra pantalla no muestra el botón Color de Fuente, pulsamos sobre el botón Más
Botones que se encuentra al final de la Barra de Formato. Y pulsamos sobre el botón Color de fuente.
• Marcamos el color deseado.
E) Poner el fondo de las celdas con números de color verde claro.
• Seleccionamos las celdas a las que deseamos cambiar el color del fondo.
• En la Barra de Formato pulsamos sobre Color de relleno.
• Y pulsamos sobre el color verde claro.
Formulario Formato de Celdas
Las opciones de formato como vimos pueden ser activadas igualmente desde la Barra de Menú
• Seleccionamos las celdas a las que queremos dar formato.
• Pulsando sobre Formato en la Barra superior y
• Celdas.
• En el formulario Formato de celdas que aparece pulsamos sobre la pestaña de Fuente
• En este formulario podemos cambiar la alineación de los datos introducidos, pulsando sobre la pestaña
alineación y marcando una nueva opción.
• Podemos cambiar el aspecto de las líneas que delimitan las celdas utilizando la pestaña Bordes y
seleccionando por ejemplo unas líneas más gruesas.
• Igualmente podemos cambiar el formato numérico en Número, y poner un formato monetario.
• En este mismo formulario podemos cambiar el fondo de las celdas seleccionando diferentes tramas y
proteger para ocultar o bloquear celdas Autoformatos en Excel
El aspecto de nuestras hojas es un aspecto importante, sobre todo si otras personas van a ver y evaluar
nuestro trabajo.
Ya hemos tenido algún contacto con los formatos en Excel, pero hay una forma más rápida y sencilla de
mejorar el aspecto de nuestro trabajo sin tener que dedicarle mucho tiempo ni esfuerzo.
Vamos a aprender a manejar los autoformatos. Veamos una página sin formatos.
El siguiente paso es seleccionar una celda que esté dentro de una tabla, por ejemplo C5.
Cuando esté marcada en el menú Formato, pulsaremos en la opción Autoformato
Atención, es importante que hayamos seleccionado una celda que pertenezca a una tabla, como es
nuestro caso. Si hubiéramos pulsado en alguna celda, fuera de la tabla Excel nos daría un mensaje de
error.
Para evitarlo, solo tenemos que posicionarnos en el interior de la tabla a la que queremos dar autoformato.
Al pulsar en Autoformato, nos surge el cuadro de dialogo.
De entre todos los ejemplos predefinidos podemos elegir el formato que consideremos más apropiado
para nuestra tabla.
Sólo tendremos que pulsar sobre el ejemplo deseado y después aceptar.
Alinear los Datos
Podemos llegar a las instrucciones para cambiar la alineación por varios caminos. Por ejemplo con los
mismos datos que escribimos anteriormente ahora:
A) Los números los vamos a centrar con respecto a su celda.
• Seleccionamos las celdas correspondientes a los números que queremos centrar.
• En la Barra de Formato pulsamos sobre el botón con la imagen de Centrar. Si la imagen centrar no
aparece en su pantalla debe pulsar en el botón Mas Botones situado en el lateral derecho de la Barra
formato.
B) La palabra TOTAL la vamos a alinear a la derecha.
• Para alinear a la derecha la celda donde escribimos TOTAL practicaremos otro camino.
• Marcamos la celda y pulsamos el botón DERECHO del ratón.
• Pulsamos sobre Formato de celdas
• Pulsamos sobre la pestaña Alineación
• Y en Horizontal seleccionamos Derecha
Desbordamiento de datos
Es bastante frecuente que cuando manejamos operaciones numéricas se produzca un desbordamiento de
celda. Esto se produce cuando el dato no cabe en el espacio visible de la celda. Cuando esto ocurre la
Excel nos llama la atención sobre ello con el símbolo "#"
Hay varias formas en que Excel actúa frente al desbordamiento, dependiendo si el desbordamiento se
produce en una celda con datos textos o con datos numéricos
Si el desbordamiento se produce con datos texto, el comportamiento de Excel depende de la situación de
la celda situada inmediatamente a su derecha.
Si está vacía, el texto se prolongara a su derecha libremente. Pero si está ocupada entonces solo será
visible la parte del texto que quepa en la zona visible de la celda.
ejemplo vemos dos desbordamientos distintos, el título de la celda B2 en texto que se prolonga a C2. Y el
desbordamiento numérico de E5, que al ser la cifra más extensa de la longitud de la celda, se muestra con
el símbolo # repetido todo el ancho de la celda
Eso no significa que haya habido un error o que hayamos perdido el resultado de nuestros cálculos.
No, el dato sigue estando allí, para verlo solo tendremos que modificar el ancho de la columna hasta que
aparezca el dato
Si el desborda una celda, numérica sin formula, podemos ver su contenido sin necesidad de desplazar las
columnas, sencillamente pulsando en ella y veremos como su valor aparece en la barra de formulas
Puede haber otras ocasiones en que no nos importe ampliar un poco la celda. En estos casos podremos
ver el contenido de la celda en notación científica.
La notación científica se lee simplemente multiplicando el numero mostrado por 10 elevado a la cifra que
hay tras el signo +. En otras palabras, se corre la coma tantos puestos a la derecha como el número que
haya tras el signo +.
Edición de datos y formulas. Tipos de datos
Antes de definir como se editan datos y fórmulas vamos a recordar algunos conceptos:
En una hoja de cálculo Excel podemos introducir:
datos numéricos y/o de texto.
fechas, horas, valores lógicos y fórmulas. Al ser evaluadas por Excel, este nos devuelve un valor numérico, lógico (VERDADERO y FALSO), una fecha o un error.
Al introducir un dato en una celda, Excel lo interpreta como:
Un texto si introducimos un carácter no numérico.
Un número si introducimos un carácter numérico.
Una formula si introducimos como primer carácter el símbolo igual o más, ( = , +)
En los datos numéricos Excel utiliza por defecto las comas para indicar el valor decimal y el punto para los miles como observamos en la celda C2.
Para cambiar la configuración internacional Excel, elegiremos la opción:
“Herramientas>Opciones”, abrimos la ficha “Internacional” y desmarcamos la opción “Usar separadores del sistema” e introducimos los separadores que deseemos.
En algunas ocasiones Excel interpreta un dato de manera diferente a como queremos. Por ejemplo:
al introducir en una celda el dato 1-5, Excel lo interpreta como fecha (1 de mayo) y no como texto.
lo mismo ocurre si introducimos 2:6 que lo interpreta como hora (las 2 horas y 6 minutos).
Para solucionar este inconveniente:
Nos situaremos en la celdilla que queremos cambiar de formato,
Desplegaremos la ventana “Formato de celdas”, mediante “Control 1” o bien pulsando el botón derecho del ratón,
Elegimos la opción “Texto” para que Excel interprete el dato introducido como texto.
TRUCO: Podemos usar una comilla delante, „1-5, para que Excel interprete el valor como texto en lugar de cómo fecha, así nos evitaremos cambiar el formato de la celda. La comilla no aparece en la celda, solo actúa como indicador.
FECHAS.
Para introducir la fecha en una hoja Excel, lo podemos hacer de varias formas con distintos formatos:
Al introducir 1-3 ó 1/3 en una celda, Excel lo interpreta como 1 de marzo. Esta fecha no se actualizará.
Pulsando “Control;” se obtiene la fecha actual. Esta no se actualiza, permanece fija.
Usando la función HOY. Si introducimos en una celda la fórmula siguiente: =HOY(), nos aparecerá automáticamente la fecha actual y esta sí se actualiza automáticamente.
Las fórmulas y determinados formatos de fecha y hora se actualizan automáticamente pulsando F9.
HORAS.
Para introducir la hora también existen varias formas y formatos:
Introduciendo 4:9 Excel lo interpreta como las 4 horas y 9 minutos. Esta permanece constante sin actualizarse.
Pulsando “Control:” Excel nos ofrece la hora actual. Esta no se actualiza con el paso del tiempo.
Utilizando la función AHORA. Al introducir la formula “=AHORA()” Excel nos devuelve la hora actual y esta si se actualiza automáticamente.
Edición de datos
Modificar los datos ya capturados es natural al trabajar con cualquier programa de computadora, para eso sirven las herramientas de edición.
Las opciones con las que podemos editar los datos en Excel son prácticamente iguales en los diversos programas que componen Office. Sin embargo, también muestran algunas características específicas de la hoja de cálculo que son útiles en el trabajo cotidiano. Revisémoslas rápidamente.
Copiar
Esta opción ya la habíamos utilizado en ejemplos anteriores. En principio, la opción “Copiar”, como su nombre lo indica, sirve para copiar el contenido de una celda, o un rango de celdas, para copiarlo en otras. Esa es la función predeterminada de los botones de la sección “Portapapeles” de la ceja “Inicio” y su uso es simple.
Sin embargo, también podemos copiar un rango de celdas como si se tratara de una imagen, de modo que el resultado puede utilizarse en programas de edición de imágenes, tan simples como Paint o tan sofisticados como Photoshop.
Pegar
A su vez, el botón pegar también tiene distintas posibilidades, toda vez que podemos discernir, por ejemplo, entre copiar el contenido de una celda o sólo el formato en el que se presenta, o bien, entre copiar una fórmula o el valor del resultado que en ese momento tenga. Veamos a qué nos referimos.
Si utilizamos la última opción del botón “Pegar”, llamada “Pegado especial”, obtenemos un cuadro de diálogo que permite modificar los datos de diversos modos al tiempo que los copia. Algunas de sus opciones tienen similitud con los botones anteriores, pero otras nos permiten inclusive operar aritméticamente con los datos a pegar e incluso trasponerlos en el área de destino.
Portapapeles
Cuando copiamos o cortamos el contenido de una celda o un rango, éste va a dar a una sección de la memoria llamada “Portapapeles”. En Excel 2010, el portapapeles puede en realidad contener hasta 24 elementos copiados para reutilizarlos en distintas partes de la hoja de cálculo. Para abrir el panel del portapapeles, utilizamos el disparador de cuadros de diálogo de la sección del mismo nombre.
Si hay algún programa de Office abierto, entonces este portapapeles puede recoger también copias hechas en otros programas (utilizando la combinación de teclas “Ctrl+C”) para pegarse en el documento con el que estemos trabajando.
Finalmente, el portapapeles puede depurarse, ya sea vaciándolo completamente o borrando en lo particular algunos de sus elementos.
Cortar
La opción “Cortar” es prácticamente idéntica a la opción “Copiar”, sólo que el contenido de las celdas que se “copian” desaparece, esto es, estamos “cortando” para “pegar” en otra celda. Si acaso, la única diferencia es que no existe una opción “Cortar como imagen”.
Borrar
Esta opción nos permite borrar las características de una celda o todo su contenido. Al desplegar el menú del botón “Borrar” de la sección modificar, de la ceja “Inicio”, podremos ver que las opciones se explican a sí mismas:
Insertar celdas, filas y columnas
No siempre borrar un rango de celdas o cortar y pegar su contenido es solución cuando estamos creando un cuadro de datos. Con frecuencia lo mejor es insertar algunas celdas entre los datos existentes, pero sin que la información capturada se borre. Así que podemos elegir un rango de celdas y usar cualquiera de las opciones del comando “Insertar” que se encuentra en la sección “Celdas”, en la ceja “Inicio”.
Alternativamente podemos seleccionar filas o columnas completas, haciendo clic en su indicador. Después usamos el menú contextual. Insertar, eliminar y mover hojas
De modo similar, también podemos editar las hojas de un libro de Excel. El menú contextual nos permite, entre otras cosas, insertar y eliminar hojas de cálculo. Para mover una hoja de posición respecto a las demás, lo más simple es tomar la ceja con su nombre con el ratón y arrastrarla a la nueva posición. El comando “Mover o copiar hoja” del menú contextual también nos permite copiarla por completo y ubicarla en algún otro libro que esté abierto en ese momento. Estas opciones también están disponibles con el menú desplegable del botón “Formato” de la sección “Celdas”.
Buscar y seleccionar
En la sección “Modificar” de la ceja “Inicio”, encontramos el botón “Buscar y seleccionar”, que no sólo nos permite buscar celdas con contenido específico, sino además, con él, podemos reemplazar su contenido, buscar celdas que tengan determinado formato, ir a rangos de celdas con nombre, etcétera.
Deshacer y rehacer
En la sección “Modificar” de la ceja “Inicio”, encontramos el botón “Buscar y seleccionar”, que no sólo nos permite buscar celdas con contenido específico, sino además, con él, podemos reemplazar su contenido, buscar celdas que tengan determinado formato, ir a rangos de celdas con nombre, etcétera.
Cuando usamos “Deshacer” éste revierte de inmediato el último cambio realizado sobre la hoja de cálculo. Eligiendo “Deshacer” varias veces consecutivamente podemos revertir todos los cambios.
Cuando no hay nada que revertir el menú del botón indica el mensaje “Imposible deshacer” y se ve desvanecido.
Por el contrario, el botón “Rehacer” anula el efecto de “Deshacer”. Parecerá broma, pero con estas dos opciones podemos arrepentirnos de cualquier cambio o dato que hayamos capturado y también podemos arrepentirnos de nuestro arrepentimiento, en ambos sentidos en un número de veces que puede abarcar una larga sesión de trabajo. Con esto se evita así que en Excel alguien cometa un error irremediable.
Sustitución de datos de Excel
Sustituye texto_nuevo por texto_original dentro de una cadena de texto. Use SUSTITUIR cuando desee reemplazar texto específico en una cadena de texto; use REEMPLAZAR cuando desee reemplazar cualquier texto que aparezca en una ubicación específica dentro de una cadena de caracteres.
Sintaxis
SUSTITUIR (texto; texto_original;texto_nuevo; núm_de_ocurrencia)
Texto es el texto o la referencia a una celda que contiene texto en el que desea sustituir caracteres.
Texto_original es el texto que desea reemplazar.
Texto_nuevo es el texto por el que desea reemplazar texto_original.
Núm_de_ocurrencia especifica la instancia de texto_original que desea reemplazar por texto_nuevo. Si especifica el argumento núm_de_ocurrencia, sólo se remplazará esa instancia de texto_original. De lo contrario, todas las instancias de texto_original en texto se sustituirán con texto_nuevo.
Ejemplo
El ejemplo puede resultar más fácil de entender si lo copia en una hoja de cálculo en blanco.
Cree un libro o una hoja de cálculo en blanco.
Seleccione el ejemplo en el tema de Ayuda. No seleccione los encabezados de fila o de columna.
Seleccionar un ejemplo de la Ayuda
Seleccionar un ejemplo de la Ayuda
Presione CTRL+C.
En la hoja de cálculo, seleccione la celda A1 y presione CTRL+V.
Para alternar entre ver los resultados y ver las fórmulas que devuelven los resultados, presione CTRL+` (acento grave) o, en el menú Herramientas, elija Auditoría de fórmulas y, a continuación, haga clic en Modo de auditoría de fórmulas.
Descripción
Sustituye texto_nuevo por texto_original dentro de una cadena de texto. Use SUSTITUIR cuando
desee reemplazar texto específico en una cadena de texto; use REEMPLAZAR cuando desee
reemplazar cualquier texto que aparezca en una ubicación específica dentro de una cadena de
caracteres.
Sintaxis SUSTITUIR (texto; texto_original; texto_nuevo; [núm_de_instancia])
La sintaxis de la función SUSTITUIR tiene los siguientes argumentos:
Texto Obligatorio. El texto o la referencia a una celda que contiene el texto en el que se desea sustituir caracteres.
Texto_original Obligatorio. El texto que se desea sustituir.
Texto_nuevo Obligatorio. El texto por el que se desea reemplazar texto_original.
Núm_de_instancia Opcional. Especifica la instancia de texto_original que se desea reemplazar por texto_nuevo. Si se especifica el argumento núm_de_instancia, sólo se remplazará esa instancia de texto_original. De lo contrario, todas las instancias de texto_original en texto se sustituirán por texto_nuevo.
Ejemplo
El ejemplo será más fácil de entender si lo copia en una hoja de cálculo en blanco.
¿Cómo copio un ejemplo?
Seleccione el ejemplo de este artículo.
IMPORTANTE No seleccione los encabezados de columna o de fila.
Seleccionar un ejemplo de la Ayuda
Presione CTRL+C.
En Excel, cree una hoja de cálculo o un libro en blanco.
En la hoja de cálculo, seleccione la celda A1 y presione CTRL+V.
IMPORTANTE Para que el ejemplo funcione correctamente, debe pegarlo en la celda A1 de la
hoja de cálculo.
Para cambiar entre ver los resultados y ver las fórmulas que devuelven los resultados, presione Alt+º, o en la ficha Fórmulas, en el grupo Auditoría de fórmulas, haga clic en el botón Mostrar fórmulas.
Después de copiar el ejemplo en una hoja de cálculo en blanco, puede adaptarlo a sus
necesidades.
Cómo utilizar la función SUBSTITUTE (Sustituir) de Excel
La función SUBSTITUTE (Sustituir) de Excel es útil si tienes un montón de celdas que tienen el mismo formato de datos, pero con valores diferentes. Con la función SUBSTITUTE puedes ingresar la información una sola vez y luego escribir una fórmula que muestre una variación de esa celda a lo largo de la hoja de cálculo
Instrucciones
1 Selecciona la celda donde desees introducir la fórmula. Haz clic con el ratón. Haz clic dentro de la función (fx), justo debajo del menú de Excel
2 Escribe la función en este formato: "=SUBSTITUTE(texto,"texto_original", "texto_nuevo", “núm_de_ocurrencia”. "Texto" es la celda que contiene el texto que deseas sustituir, "texto_original" es el texto que vas a cambiar y "texto_nuevo" es el texto con el que lo deseas sustituir; "num_de_ocurrencia" es la instancia del texto que quieres cambiar.
3 Crea un ejemplo para aprender cómo funciona la función SUBSTITUTE. Haz clic en la celda gris de arriba a la izquierda para que la hoja de cálculo entera se resalte. Haz clic en "Formato" y luego en "Celdas" en el menú. Selecciona "texto".
4 Escribe "2 de noviembre 2008" en A1.
5 Haz clic en la celda B1. Ve a la celda de función y escribe “= SUBSTITUTE(A1, "2", "4", 1). Presiona Enter. El texto en B1 será “4 de noviembre de 2008”. Luego, edita la fórmula y saca el "1". Todos los "2" serán cambiados por "4".
6 Ve a la celda B2. En el cuadro de la función, escribe "=SUBSTITUTE(A1,"8","12"). Pulsa Enter. Se mostrará el año "2012". En este caso no es necesario el "num_de_ocurrencia ", ya que solo hay un "8" en la celda.
7 Consigue ayuda y otros ejemplos de la función SUBSTITUTE, haciendo clic en "Ayuda" en el menú de Excel. En el cuadro "palabras clave", escribe "función sustituir de la hoja de cálculo".
Ejemplo
Como podrás apreciar, tenemos una primera columna con códigos digitados de manera errada, digamos que donde hemos pues CL-XXX, debimos poner DL-XXX, para corregir esto procedimos con la siguiente formula de Excel que hemos colocado en la columna C: C,
=SUSTITUIR (A3,”CL”,”DL”)
Con lo anterior excel busca en la celda A3, el texto o los caracteres mostrados entre comillas “CL”
y substituye los mismos por “DL”, el número de ocurrencias que es opcional lo hemos dejado en
blanco ya que conocemos que los datos solo aparecen en una ocasión.
En caso de que los datos pudiesen encontrarse en más de una ocasión en la misma celda, si
utilizamos la fórmula de excel anterior, el resultado con esta sería el siguiente,
Como podrás ver, en cada ocasión que aparece “CL” fue sustituido por “DL”, si quisiéramos solo sustituir la segunda ocurrencia de esta misma tabla, deberíamos utilizar esta fórmula,
=SUSTITUIR (A21,”CL”,”DL”,2)
Con esta el resultado, sería el siguiente,
Importante recalcar que colocamos “CL”, si hubiésemos colocada “cl” no se realiza el cambio
debido a que esta función es sensible a mayúsculas y minúsculas.
Sustituir múltiples valores en Excel
El caso anterior vimos como sustituir una sola incidencia o solo un grupo de caracteres, pero que
ocurriría si nos damos cuenta que en vez de un solo error de entrada tenemos más de uno, para
esto utilizaremos el mismo cuadro y digamos que queremos en adición de sustituir “CL” por “DL”
también queremos sustituir “VL” por “SL”
Para lo anterior la formula a utilizar sería la siguiente,
=SUSTITUIR (SUSTITUIR (A3,”CL”,”DL”),”VL”,”SL”)
En este caso nuestro cuadro quedaría de esta manera,
Como veras, lo que hemos hecho es anidar dos funciones SUSTITUIR, donde la que está dentro
de los paréntesis centrales se evalúa primero y luego se evalúa la segunda, en el caso de esta
ultima podrás notar que no hemos incluido el campo TEXTO ya que será el mismo que se evaluara
en la primera formula
La función SUBSTITUTE (Sustituir) de Excel es útil si tienes un montón de celdas que tienen el
mismo formato de datos, pero con valores diferentes. Con la función SUBSTITUTE
puedes ingresar la información una sola vez y luego escribir una fórmula que muestre una
variación de esa celda a lo largo de la hoja de cálculo.
SUSTITUIR: Sustituye una cadena de texto dentro de otra, por una nueva cadena.
SUSTITUIR (Texto original; Parte de texto a sustituir; Nuevo texto)
Ejemplo de la función
Si en la celda A1 ponemos el texto “Funciones de Excel” y queremos que en la celda A2 aparezca
“Funciones en Excel” deberemos escribir en la celda A2 la siguiente función: =SUSTITUIR
(A1;"de";"en")
Si en la celda A1 ponemos el texto “Funciones de Excel” y queremos que en la celda A2 aparezca
“Funciones en Excel” deberemos escribir en la celda A2 la siguiente función: =SUSTITUIR
(A1;"de";"en")
Reemplazar asteriscos en Excel
¿Alguna vez has intentado reemplazar un asterisco en Excel? Esto puede ser peligroso porque
puedes eliminar toda la información de la hoja. Es importante que sepas la razón por la que sucede
esto en Excel.
El asterisco es un carácter comodín
El asterisco (*) es un carácter comodín de Excel que significa TODOS los caracteres. Por ejemplo,
en la siguiente lista de palabras reemplazaré la cadena
de texto “BA*” por “#”:
Observa el resultado después de realizar la sustitución:
La cadena de texto “BA*” significa la combinación de las letras “BA” seguidas de CUALQUIER
cantidad y tipo de caracteres, es por eso que Excel hizo el reemplazo sin importar que haya
encontrado la combinación de letras “BA” al inicio, en medio o al final de una palabra.
Ahora comienzas a darte cuenta de lo peligroso que puede ser utilizar el carácter “*” en un
reemplazo. Si alguna vez llegas a especificar solamente “*” en el cuadro de búsqueda y lo
reemplazas con una cadena vacía entonces se borrará el contenido de todas las celdas.
Buscar y reemplazar un asterisco
Para poder reemplazar adecuadamente un asterisco debes anteponer una tilde “~”, de esta
manera Excel sabrá que no estás utilizando el asterisco como carácter comodín sino como si fuera
cualquier otro carácter.
Con los parámetros de la imagen anterior Excel reemplazaría efectivamente un asterisco por una
cadena vacía, es decir, los eliminará de los textos de las celdas.
Esta misma solución la debes aplicar en caso de que quieras hacer un reemplazo del símbolo “?”
ya que también es un carácter comodín que representa una sola letra, pero si no se antepone la
tilde Excel hará el reemplazo de todas las letras por una cadena vacía. La manera correcta de
hacer el reemplazo es similar a la del asterisco:
Debes tener mucha precaución al usar los comodines de Excel dentro de una búsqueda o un
reemplazo. Recuerda que los comodines son el asterisco (*) y el signo de interrogación (?) y que
debes anteponer una tilde (~) antes de usarlos en un reemplazo
En la pestaña Reemplazar de esta misma ventana, o bien en la opción Reemplazar... del botón Buscar y seleccionar (teclas CTRL+L) podremos realizar una sustitución de los datos, cambiando un valor original por otro. La opción de reemplazo no se limita únicamente a valores, también puede referirse a formatos. Por ejemplo, podríamos buscar todos las celdas que contengan el valor 0 en formato moneda y reemplazarlo por el mismo valor pero con un color de celda rojo, para destacarlos.
La opción Ir a... (teclas CTRL+I) nos permite situarnos en una celda en concreto. La ventaja que tiene frente a otras formas de desplazarnos entre las celdas es que guarda un historial de las celdas que se van visitando, de forma que si vamos a trabajar con las mismas celdas todo el tiempo, es posible que nos interese utilizar esta opción.
Luego encontramos una serie de opciones: Fórmulas, Comentarios, Formato condicional, Constantes, Validación de datos, Seleccionar objetos que nos permiten seleccionar todas las celdas que contengan este tipo de información a la vez. Es decir, seleccionar todas las fórmulas del documento, por ejemplo.
Alta de datos de Excel
Es posible introducir datos en tu hoja de cálculo Excel de forma rápida y fácil, para ello has de
usar los formularios de datos.
De esta manera los datos se introducen de forma más eficaz incluso aunque no se tengan muchos
conocimientos de Excel.
Vas a ver cómo crear y usar un formulario, introducir datos, realizar búsquedas, eliminar
registros,…
En el ejemplo harás una base de datos para registrar información sobre los clientes de la empresa
para hacer una acción comercial con el correo electrónico.
En primer lugar abre una hoja de cálculo Excel, en otras hojas la sistemática es muy similar por
ejemplo puedes leer Google Docs. formularios.
Escribe en la celda A1 Nombre; en B1 Apellido; en C1 Teléfono; y en D1 Email.
Selecciona el rango desde A1 a D1.
En Excel 2003 pulsa en el Menú (arriba en la parte central), y luego en Formulario.
En Excel 2010 y 2007, pulsa en la Cinta de opciones el icono, Formulario, si no aparece sigue
estas instrucciones.
En la nueva pantalla que se despliega, ver imagen ampliada, ya puedes ir introduciendo los datos
de tus clientes.
En la parte izquierda tienes unos rectángulos blancos donde has de situar el cursor e ir
escribiendo la información.
Así por ejemplo escribe en la primera fila en Nombre: Yolanda, en Apellido: Cuesta, en Dirección:
About en español, Teléfono: 696 22 22 22 y en email:[email protected]. Haz lo
mismo en la segunda fila con tus datos y en la tercera con los datos de una tercera persona.
Pulsa Nuevo para pasar a cumplimentar el siguiente registro.
Así sucesivamente hasta introducir todos los registros.
Para moverte por esta ventana pulsa sobre la barra de desplazamiento que está en la parte
central y desplázate con el ratón sin soltar o pincha en los triángulos negros superior e inferior, así
te iras situando en uno u otro registro.
Otras acciones importantes en el apartado de formularios es la Eliminación de registros y la
Búsqueda de registros.
En cuanto a la Eliminación de registros, sitúate en el registro a eliminar, pulsa en el botón
Eliminar y en el cuadro de dialogo haz clic en Aceptar, recuerda que si en el último momento
decides no eliminarlo puedes pulsar Cancelar y el registro no se borrara, eso sí una vez eliminado
no podrás recuperarlo ni con la acción Deshacer.
También es útil que conozcas la opción Búsqueda de registros del formulario de datos, lo más
sencillo es buscar de uno en uno con los botones Buscar anterior y Buscar siguiente.
Otra opción es pulsar en criterios y en los cuadro de texto, rectángulos en blanco, escribir la
palabra o dato y dar a Buscar siguiente, aquí es importante señalar que la búsqueda de Excel es
bajo la condición”, con lo cual si cumplimentas más de un cuadro de texto han de estar bien
escritos y ser correctos los datos para que te sitúe en el registro.
Para búsquedas más avanzadas puedes usar los Filtros y establecer diferentes condiciones en
función de lo que busques.
Para continuar con la introducción de datos pulsa el botón Nuevo.
Cuando termines de introducir información pulsa en Cerrar y la ventana del Formulario
desaparecerá, dejando los registros en tu hoja de cálculo.
Para volver a abrir el Formulario, sigue los pasos como explicaba al principio y se vuelve a abrir
la ventada desde donde podrás continuar con la introducción de más referencias.
Consejo: si en un momento dado no conocer todos los datos de un registro, por ejemplo
desconoces el teléfono, puedes crear el registro con los datos que conozcas y más tarde
cumplimentar el que te falta.
USO DE LAS FUNCIONES DE EXCEL PARA LA EXPORTACIÓN DE DATOS.
Continuando con el artículo sobre herramientas Excel y su aplicación en la empresa, te introduzco
ahora en las siguientes posibilidades: Escenarios, Macros, Importar y Exportar.
Escenarios
En la empresa, previamente a la toma de decisiones, se han de analizar las situaciones o hipótesis
alternativas ante diferentes hechos potenciales y como la información es poder, con Excel puedes
conocer los valores de diferentes escenarios con facilidad y ayudarte de los datos obtenidos para
tratar de llevar a cabo la decisión más acertada.
Para comprenderlo mejor piensa en el siguiente ejemplo sencillo:
Quieres saber cómo cambia el beneficio de una empresa, en función del precio y de la demanda
cambiante del producto.
En Excel 2010 y 2007 vas a Datos, Análisis Y si, Administrador de escenarios y en la versión 2003:
accede desde el menú herramientas y luego pulsa en Escenarios. Aparecerá la ventana
Administrador de escenarios en la que introducirás los datos y Excel resolverá el problema
mostrándote en una hoja aparte los resultados.
Macros
Excel, y el resto de aplicaciones de Office, tienen entre sus opciones la posibilidad de crear
Macros.
Las Macros son programas personalizados por los usuarios, que permiten que escribas las
acciones que quieres que se realicen y estas se ejecuten en un solo clic. Son atajos para
automatizar acciones repetidas con asiduidad.
El lenguaje de programación usado es Visual Basic para Aplicaciones, pero no es necesario
conocer como programar bajo este leguaje, ya que basta con realizar la secuencia con el ratón y
Excel te lo graba.
En concreto imagina el caso de un libro de Excel, por ejemplo que contenga un presupuesto, en el
que tienes cifras decimales y quieres presentarlo sin ese detalle, harías lo siguiente:
En la versión 2003 de Excel, desde el menú Herramientas eliges Macro y después Grabar nueva
macro..., en la ventana que se abre das nombre a la Macro y aceptar. (Versiones posteriores:
Vista, Macros)
A partir de ahí realizamos las acciones que queremos automatizar y al acabar pulsamos en
Detener Grabación y guarda la Macro.
A partir de ahora cada vez que quieras que los datos de un libro queden redondeados y sin
posiciones decimales seleccionarás las celdas, vas a Herramientas, Macros..., selecciona la macro
recientemente creada y das a Ejecutar, en un abrir y cerrar de ojos todas las cifras se han
transformado desapareciendo los decimales.
Importar y Exportar
Muchas veces necesitas importar datos a Excel, por ejemplo el programa de contabilidad te
proporciona informes en formato .txt o .doc y precisas trabajar con esos datos realizando
operaciones numéricas para realizar análisis varios.
Para ello existen diferentes alternativas pero una de las más usadas es ir a Datos en la barra de
menús y de ahí selecciona Obtener datos externos, Importar datos. Selecciona el archivo que
quieres tener en formato Excel sigues las instrucciones mostradas delimitando columnas y
formatos y como resultado obtendrás en tu hoja de cálculo los datos en formato Excel con el que
puedes realizar todo tipo de operaciones, gráficos y análisis.
Otras veces necesitarás exportar los datos de Excel para ello la manera más sencilla y rápida es
utilizar los comandos copiar y pegar o bien otra opción es situándote encima de la pestaña de la
hoja de cálculo que quieras copiar, pulsar botón derecho y Mover o copiar, en la ventana que se
abre podrás elegir entre llevarla al mismo libro, a otro libro ya existente o a un libro nuevo.
También otra opción es: En Guardar como seleccionar el tipo de archivo en el que quieres que se
transforme tu libro de Excel que lo más usual es que sea en formato texto o HTML en formato Web.
Conclusión:
Lo visto en estos dos artículos es solo una introducción acerca de las posibilidades que ofrece
Excel a nivel intermedio-avanzado, para ampliar estos contenidos y continuar aprendiendo sobre
Excel visita con frecuencia y permanece actualizado en hojas de cálculo en About en español.
EXCEL EN LA EMPRESA: USOS.
Prácticamente todas las empresas disponen en sus ordenadores de una de las herramientas mas
conocidas y usadas en la actualidad, como es el programa informático Excel de Microsoft Office.
Pero en muchas ocasiones este potente recurso informático está altamente infrautilizado.
Las aplicaciones de Excel, o en general de las hojas de cálculo, en la empresa son muchas y no
siempre utilizadas. Para contrarrestar esta situación te explico a continuación una serie de
acciones que Excel te permite realizar y la aplicación de dichas acciones en la empresa.
Con estos procesos lograrás una mayor eficacia y conseguirás ahorro de tiempo e incremento de
productividad.
Estas aplicaciones son:
Subtotales.
Tablas Dinámicas. Gráficos Dinámicos.
Solver.
Escenarios.
Macros.
Importar.
Exportar.
Subtotales:
Con esta acción Excel te permite realizar un análisis de los datos de forma rápida realizando
agrupaciones. Supongamos que dispones de un listado en formato Excel en donde se refleja la
información sobre las ventas.
Dicha información se organiza en varias columnas con los siguientes epígrafes: Fecha, Cliente e
Importe.
Quieres conocer el total de ventas de cada cliente en el mes dado, para ello basta con emplear la
herramienta Subtotales dentro del menú Datos, tanto en Excel 2010 como en versiones anteriores.
Obtendrás la ordenación agrupando por totales las ventas realizadas a cada cliente, además en un
tiempo mínimo y con una absoluta fidelidad de los datos obtenidos.
Para quitar los Subtotales, vas a Datos, Subtotales, Quitar todos.
Tablas Dinámicas. Gráficos Dinámicos:
Las tablas dinámicas te sirven para realizar análisis de datos aún más específicos que los
proporcionados por la herramienta Subtotales. Se denominan dinámicas porque el informe
obtenido con ellas se puede transformar con facilidad en otro en función del criterio que elijas, todo
a base de unos pocos clicks y del arrastre del ratón.
Esta herramienta la encontrarás en la versión 2003 de Excel dentro de Datos, Informe de tablas y
gráficos dinámicos (Excel 2010 y 2007 en Insertar, Tabla dinámica), se abre un asistente que te
indica distintas posibilidades acerca de donde están los datos a usar en la tabla, si la quieres en
una hoja aparte, diseño, ayuda, etc.
Al pulsar Finalizar se abre en una nueva hoja de cálculo una tabla en blanco con una ventana
llamada Lista de campos de tabla dinámica. En fila, columna y en la parte central de la tabla
colocamos los criterios que necesitas para tu informe pinchando en el campo que quieras y
llevándolo arrastrando con el ratón y soltando en el área de la tabla.
Si al abrirse el asistente en vez de elegir tabla dinámica eliges Gráfico dinámico se abre una hoja
con un gráfico en blanco que elaborarás de la misma manera que he comentado para la tabla
dinámica y como resultado obtendrás un gráfico que aúna la información indicada.
DESCRIPCIÓN DE LOS CONCEPTOS BÁSICOS DE LAS CONEXIONES DE DATOS
Los datos de un libro de Excel pueden proceder de dos ubicaciones diferentes. Pueden estar
almacenados directamente en el libro o en un origen de datos externo, como un archivo de texto,
una base de datos o un cubo de procesamiento analítico en línea (OLAP). Este origen de datos
externos se conecta al libro a través de una conexión de datos, que es un conjunto de información
que describe cómo localizar, iniciar una sesión y tener acceso al origen de datos externo.
La ventaja principal de conectarse a datos externos consiste en que puede analizar periódicamente
estos datos sin tener que copiar repetidamente los datos en su libro, una operación que puede ser
larga y provocar errores. Tras conectarse a 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.
La información de conexión se almacena en el libro y también se puede almacenar en un archivo
de conexión, como un archivo de conexión de datos de Office (.odc) o un archivo de nombre de
origen de datos (.dsn)*.
NOTA*
1. DSN de sistema: es el DSN utilizado en todo el sistema, cualquiera con los derechos adecuados
puede iniciar la sesión. Debe crearse este DSN en el equipo donde se encuentra el programa. El
DSN del sistema se almacenan en el registro de Windows bajo la siguiente clave: orígenes de
datos HKEY_LOCAL_MACHINE\Software\Odbc\Odbc.ini\Odbc
2. DSN de usuario: es un DSN creado para un usuario específico. Sólo el usuario que creó el DSN
puede verlo y utilizarlo. Al igual que el DSN del sistema, la información se almacena en el registro
de Windows bajo la siguiente clave: orígenes de datos
HKEY_CURRENT_USER\Software\Odbc\Odbc.ini\Odbc
3. DSN de archivo: se trata de un DSN que se almacena en un archivo de texto con una.Extensión
DSN (no en el registro de Windows). Para utilizar un DSN de archivo, este debe contener la
información necesaria para conectar con el origen de datos deseado. El controlador ODBC debe
instalarse localmente. El archivo de DSN es un archivo de texto que se puede ver en cualquier
editor de texto como Microsoft Notepad. Los DSN de archivo se almacenan de forma
predeterminada en la siguiente carpeta de localización: programa de programa\Archivos
comunes\ODBC\Data Sources.
En DSN de archivo, consulte el artículo de Microsoft knowledge base:
Los productos de terceros que se tratan en este artículo están fabricados por compañías
independientes de FRx Software Corporation.
Para incluir datos externos en Excel, necesita obtener acceso a los datos. Si el origen de datos
externo al que desea obtener acceso no está ubicado en el equipo local, es posible que tenga que
ponerse en contacto con el administrador de la base de datos para que le facilite una contraseña,
permisos de usuario u otro tipo de información de conexión. Si el origen de datos es una base de
datos, asegúrese de que la base de datos no esté abierta en modo exclusivo. Si el origen de datos
es un archivo de texto o una hoja de cálculo, asegúrese de que otro usuario no lo tenga abierto en
modo exclusivo.
Una gran cantidad de orígenes de datos también requiere un controlador ODBC o un proveedor
OLE DB para coordinar el flujo de datos entre Excel, el archivo de conexión y el origen de datos.
En el diagrama siguiente se resumen los principales puntos referentes a las conexiones de datos.
Conexión a orígenes de datos externos
Llamada 1 Existe una variedad de orígenes de datos a los que puede conectarse: Analysis
Services, SQL Server, Microsoft Access, otras bases de datos relacionales y OLAP, hojas de
cálculo y archivos de texto.
Llamada 2 Muchos orígenes de datos tienen un controlador ODBC o proveedor OLE DB asociado.
Llamada 3 Un archivo de conexión define toda la información necesaria para obtener acceso y
recuperar datos de un origen de datos.
Llamada 4 La información de conexión se copia de un archivo de conexión a un libro, y puede
editarse fácilmente.
Llamada 5 Los datos se copian en un libro, de manera tal que puede usarlos simplemente como
usa los datos almacenados directamente en el libro.
Búsqueda de conexiones
Para buscar archivos de conexión, use el cuadro de diálogo Conexiones existentes. (En el grupo
Obtener datos externos de la ficha Datos, haga clic en Conexiones existentes). Al usar este cuadro
de diálogo, podrá ver los siguientes tipos de conexiones:
Conexiones en el libro
Esta lista muestra todas las conexiones actuales del libro. La lista se crea a partir de conexiones
que el usuario ya ha definido y creado en el cuadro de diálogo Seleccionar origen de datos del
Asistente para la conexión de datos o de las conexiones que ha seleccionado previamente como
conexión desde este cuadro de diálogo.
Archivos de conexión en el equipo
Esta lista se crea a partir de la carpeta Mis archivos de origen de datos, generalmente almacenada
en la carpeta Mis documentos (Windows XP) o Documentos (Windows Vista).
Archivos de conexión en la red
Esta lista puede crearse a partir de lo siguiente:
Un conjunto de carpetas de la red local, cuya ubicación se pueda implementar en la red como parte
de la implementación de directivas de grupo de Microsoft Office.
Una biblioteca de conexiones de datos (DCL)* de Servicios de Excel en un sitio de SharePoint
Foundation. Para obtener más información acerca de las DCL, vea la sección Publicar en Servicios
de Excel de forma segura.
NOTA*
La Biblioteca de conexiones de datos (DCL) es un tipo de biblioteca de documentos en Microsoft
SharePoint Server 2010 que permite a aplicaciones como Microsoft InfoPath 2010 y tecnologías
como Servicios de Excel especificar la configuración de conexión de datos con archivos externos.
Esto reporta varias ventajas:
Reconfiguración: la configuración de conexión de datos, como las ubicaciones del servidor de base
de datos, puede modificarse independientemente de las soluciones que la usan.
Implementación en entornos diferentes: los servidores de prueba y de producción pueden
especificar ubicaciones diferentes para el acceso a los datos y una plantilla de formulario usará
automáticamente la ubicación específica del servidor cuando se publiquen en ese servidor.
Autenticación: con el inicio de sesión único de Office, los administradores del servidor pueden
especificar credenciales de autenticación alternativas para que el servidor las use al procesar un
formulario para el explorador.
Seguridad entre dominios: la Biblioteca de conexiones de datos (DCL) actúa como una "lista
segura" para las conexiones de datos. Para los formularios que no se publican con plena
confianza, solo las conexiones especificadas mediante la DCL pueden recuperar datos desde fuera
del conjunto o granja de servidores.
EDICIÓN DE LAS PROPIEDADES DE CONEXIÓN
También puede usar Excel como editor de archivos de conexión para crear y editar conexiones a
orígenes de datos externos almacenados en un libro o en un archivo de conexión. Si no encuentra
la conexión que desea, puede crear una haciendo clic en Examinar en busca de más para mostrar
el cuadro de diálogo Seleccionar origen de datos y, a continuación, en Nuevo origen para iniciar el
Asistente para la conexión de datos.
Una vez creada la conexión, puede usar el cuadro de diálogo Propiedades de conexión (en el
grupo Obtener datos externos de la ficha Datos, haga clic en Propiedades) para controlar
diferentes configuraciones de las conexiones a orígenes de datos externos y para usar, volver a
usar o cambiar los archivos de conexión.
Si usa un archivo de conexión para conectarse a un origen de datos, Excel copia la información de
conexión del archivo de conexión en el libro de Excel. Cada vez que realice alguna modificación en
el cuadro de diálogo Propiedades de conexión, estará editando la información de la conexión de
datos almacenada en el libro actual de Excel, y no la del archivo de conexión original que se puede
haber usado para crear la conexión (que aparece en el nombre de archivo de la propiedad Archivo
de conexión de la ficha Definición). Después de editar la información de conexión (a excepción de
las propiedades Nombre de conexión y Descripción de conexión), se quita el vínculo al archivo de
conexión y se borra la propiedad Archivo de conexión.
Para asegurarse de que el archivo de conexión se use siempre cuando se actualice un origen de
datos, haga clic en Intentar utilizar siempre este archivo para actualizar los datos en la ficha
Definición. Al activar esta casilla de verificación, se garantiza que las actualizaciones del archivo de
conexión siempre se usarán en todos los libros que usen ese archivo de conexión, que también
debe tener esta propiedad establecida.
ADMINISTRACIÓN DE CONEXIONES
En el cuadro de diálogo Conexiones del libro, puede administrar fácilmente estas conexiones,
incluida su creación, modificación y eliminación. (En el grupo Obtener datos externos de la ficha
Datos, haga clic en Conexiones). Puede usar este cuadro de diálogo para hacer lo siguiente:
Crear, editar, actualizar y eliminar las conexiones utilizadas en el libro.
Comprobar el origen de datos externos. Es posible que desee realizar esto si la conexión fue
definida por otro usuario.
Mostrar dónde se utiliza cada conexión en el libro actual.
Diagnosticar un mensaje de error sobre las conexiones a datos externos.
Redirigir una conexión a un servidor u origen de datos distinto, o reemplazar el archivo de conexión
por una conexión existente.
Simplificar la creación y el uso compartido de archivos de conexión con otros usuarios.
Uso compartido de conexiones
Los archivos de conexión son especialmente útiles para compartir conexiones de forma coherente,
resaltar las conexiones, ayudar a mejorar la seguridad de las conexiones y facilitar la
administración de los orígenes de datos. La mejor manera de compartir los archivos de conexión es
colocarlos en una ubicación segura y de confianza, como una carpeta de red o una biblioteca de
SharePoint, donde los usuarios pueden leer el archivo pero solo usuarios designados pueden
modificarlo.
Uso de archivos odc
Puede crear archivos de conexión de datos de Office (.odc) conectándose a datos externos
mediante el cuadro de diálogo Seleccionar origen de datos o mediante el Asistente para la
conexión de datos para conectarse a orígenes de datos nuevos. Un archivo .odc usa etiquetas
HTML* y XML** personalizadas para almacenar la información de conexión. Puede ver o modificar
fácilmente el contenido del archivo en Excel.
Puede compartir los archivos de conexión con otras personas para que puedan tener el mismo
acceso a un origen de datos externo. Los otros usuarios no tienen que configurar un origen de
datos para abrir el archivo de conexión, pero es posible que tengan que instalar el controlador
ODBC o proveedor OLE DB necesario para obtener acceso a los datos externos en sus equipos.
Los archivos ODC son el método recomendado para conectarse a datos y para compartir los datos.
Para convertir fácilmente otros archivos de conexión tradicionales (archivos de consulta, DSN y
UDL) a un archivo ODC, abra el archivo de conexión y haga clic en el botón Exportar archivo de
conexión en la ficha Definición del cuadro de diálogo Propiedades de conexión.
NOTA*
HTML, siglas de HyperText Markup Language («lenguaje de marcas de hipertexto»), hace
referencia al lenguaje de marcado para la elaboración de páginas web. Es un estándar que sirve
de referencia para la elaboración de páginas web en sus diferentes versiones, define una
estructura básica y un código (denominado código HTML) para la definición de contenido de una
página web, como texto, imágenes, etc. Es un estándar a cargo de la W3C, organización dedicada
a la estandarización de casi todas las tecnologías ligadas a la web, sobre todo en lo referente a su
escritura e interpretación. Es el lenguaje con el que se definen las páginas web.
El lenguaje HTML basa su filosofía de desarrollo en la referenciación. Para añadir un elemento
externo a la página (imagen, vídeo, script, etc.), este no se incrusta directamente en el código de la
página, sino que se hace una referencia a la ubicación de dicho elemento mediante texto. De este
modo, la página web contiene sólo texto mientras que recae en el navegador web (interpretador del
código) la tarea de unir todos los elementos y visualizar la página final. Al ser un estándar, HTML
busca ser un lenguaje que permita que cualquier página web escrita en una determinada versión,
pueda ser interpretada de la misma forma (estándar) por cualquier navegador web actualizado.
** NOTA
XML son la siglas en inglés de eXtensible Markup Language (en español, lenguaje de marcas
extensible). Es un lenguaje abstracto que, al igual que HTML, está definido por lo que se llama
etiquetas, que se especifican entre los símbolos < y >, de la siguiente forma: <etiqueta>. El
contenido de los documentos está definido entre estas etiquetas, mismas que tienen una
representación para indicar su límite, de la siguiente forma: <etiqueta>Contenido</etiqueta>. A
estas etiquetas y su contenido se les conoce como elementos. Un elemento puede consistir de
varias etiquetas anidadas, formando árboles. Las etiquetas también pueden tener atributos, que
sirven para indicar características específicas de la etiqueta, y que se representan de la siguiente
forma: <etiqueta atributo="valor del atributo">.
Una diferencia importante de XML con HTML es que este último tiene un conjunto de etiquetas
predefinidas que conforman el lenguaje, en cambio XML le permite a los usuarios definir sus
propias etiquetas para ser usadas como más les convengan. El objetivo de un documento escrito
en XML es describir algo significativo para los autores del documento, de ahí que la flexibilidad de
definir etiquetas según convenga sea una ventaja.
Otra diferencia con respecto a otros lenguajes similares, es que XML soporta acceso a bases de
datos, facilitando el intercambio de información entre varios componentes de una aplicación. XML
es un lenguaje que es usado para intercambio de información no sólo para aplicaciones de
Internet, sino también para otras aplicaciones que requieran intercambiar o integrar información.
Uso de archivos de consulta
Los archivos de consulta son archivos de texto que contienen información del origen de datos,
incluso el nombre del servidor donde están ubicados los datos y la información de conexión que
proporciona al crear un origen de datos. Los archivos de consulta son una forma tradicional de
compartir consultas con otros usuarios de Excel.
Uso de archivos de consulta .dqy* Puede usar Microsoft Query para guardar archivos .dqy que
contienen consultas de datos de bases de datos relacionales o archivos de texto. Al abrir estos
archivos en Microsoft Query, se pueden ver los datos devueltos por la consulta y modificar la
consulta para recuperar resultados diferentes. Puede guardar un archivo .dqy para cualquier
consulta creada, ya sea con el Asistente para consultas o directamente en Microsoft Query
NOTA*.
Los documentos DQY son Archivos de base de datos asociados con Excel ODBC Query File
(Microsoft Corporation).
La forma más rápida y sencilla de abrir su archivo DQY es haciendo doble clic sobre éste. Esto
permite que la inteligencia de Windows decida de forma automática la aplicación correcta para abrir
su archivo DQY.
En caso de que su archivo DQY no abra, es muy probable que no tenga la aplicación correcta
instalada en su PC para ver o editar extensiones de archivo DQY.
Si su PC abre el archivo DQY, pero se trata de la aplicación incorrecta, entonces usted necesitará
cambiar los ajustes de asociación de archivo del registro de Windows. En otras palabras, Windows
está asociando extensiones de archivo DQY con el programa incorrecto.
Le recomendamos que realice un escaneo de su registro de Windows para buscar asociaciones
incorrectas de archivo y otros problemas relacionados al registro.
Uso de archivos de consulta .oqy *
Puede guardar archivos .oqy para conectarse a datos en una base de datos OLAP, ya sea en un
servidor o en un archivo de cubo sin conexión (.cub). Al usar el Asistente para la conexión
multidimensional en Microsoft Query para crear un origen de datos para un cubo o base de datos
OLAP, se crea automáticamente un archivo .oqy. Dado que las bases de datos OLAP no están
organizadas en registros o tablas, no puede crear consultas o archivos .dqy para obtener acceso a
estas bases de datos.
NOTA: *
¿QUE ES UN ARCHIVO .oqy?
Los documentos OQY son Archivos de base de datos asociados con Excel OLAP Query File
(Microsoft Corporation). La forma más rápida y sencilla de abrir su archivo OQY es haciendo doble
clic sobre éste. Esto permite que la inteligencia de Windows decida de forma automática la
aplicación correcta para abrir su archivo OQY.
En caso de que su archivo OQY no abra, es muy probable que no tenga la aplicación correcta
instalada en su PC para ver o editar extensiones de archivo OQY.
Si su PC abre el archivo OQY, pero se trata de la aplicación incorrecta, entonces usted necesitará
cambiar los ajustes de asociación de archivo del registro de Windows. En otras palabras, Windows
está asociando extensiones de archivo OQY con el programa incorrecto.
Le recomendamos que realice un escaneo de su registro de Windows para buscar asociaciones
incorrectas de archivo y otros problemas relacionados al registro.
Uso de archivos de consulta .rqy **
Excel puede abrir archivos de consulta en formato .rqy para admitir controladores de origen de
datos OLE DB que usan este formato. Para obtener más información, vea la documentación del
controlador.
** NOTA: ¿QUE ES UN ARCHIVO .rqy?
Los documentos RQY son Archivos varios asociados con Excel OLE DB Query File (Microsoft
Corporation).
La forma más rápida y sencilla de abrir su archivo RQY es haciendo doble clic sobre éste. Esto
permite que la inteligencia de Windows decida de forma automática la aplicación correcta para abrir
su archivo RQY.
En caso de que su archivo RQY no abra, es muy probable que no tenga la aplicación correcta
instalada en su PC para ver o editar extensiones de archivo RQY.
Si su PC abre el archivo RQY, pero se trata de la aplicación incorrecta, entonces usted necesitará
cambiar los ajustes de asociación de archivo del registro de Windows. En otras palabras, Windows
está asociando extensiones de archivo RQY con el programa incorrecto.
Le recomendamos que realice un escaneo de su registro de Windows para buscar asociaciones
incorrectas de archivo y otros problemas relacionados al registro.
Uso de archivos de consulta .qry Microsoft Query puede abrir y guardar archivos de consulta en
formato .qry para usarlos con versiones anteriores de Microsoft Query que no pueden abrir
archivos .dqy. Si tiene un archivo de consulta en formato .qry que desea usar en Excel, abra el
archivo en Microsoft Query y guárdelo como archivo .dqy. Para obtener más información acerca de
cómo guardar archivos .dqy, vea la Ayuda de Microsoft Query.
Uso de archivos de consulta web .iqy **
Excel puede abrir archivos web de consulta en formato .iqy para recuperar datos de Internet.
** NOTA: ¿QUE ES UN ARCHIVO .iqy?
Los documentos IQY son Archivos web asociados con Excel Web Query (Microsoft Corporation).
Uso de propiedades y rangos de datos externos.
La forma más rápida y sencilla de abrir su archivo IQY es haciendo doble clic sobre éste. Esto
permite que la inteligencia de Windows decida de forma automática la aplicación correcta para abrir
su archivo IQY.
En caso de que su archivo IQY no abra, es muy probable que no tenga la aplicación correcta
instalada en su PC para ver o editar extensiones de archivo IQY.
Si su PC abre el archivo IQY, pero se trata de la aplicación incorrecta, entonces usted necesitará
cambiar los ajustes de asociación de archivo del registro de Windows. En otras palabras, Windows
está asociando extensiones de archivo IQY con el programa incorrecto.
Un rango de datos externos (también llamado una tabla de consulta) es un nombre definido o un
nombre de tabla que define la ubicación de los datos que se traen a una hoja de cálculo. Cuando
se conecta a datos externos, Excel crea un rango de datos externos automáticamente, excepto en
el caso de un informe de tabla dinámica que está conectado a un origen de datos, en el que no se
crea un rango de datos externos. En Excel, puede aplicar formato y disponer un rango de datos
externos o usarlo en cálculos al igual que con los demás datos.
Excel asigna automáticamente los nombres de los rangos de datos externos de la siguiente
manera:
Los rangos de datos externos de archivos de conexión de datos de Office (ODC) reciben el mismo
nombre que el nombre de archivo.
Los rangos de datos externos de bases de datos reciben el nombre de la consulta. De forma
predeterminada, Consulta_de_origen es el nombre del origen de datos que se usó para crear la
consulta.
Los rangos de datos externos de archivos de texto reciben el nombre del archivo de texto.
Los rangos de datos externos de consultas web reciben el nombre de la página web de la cual se
recuperaron los datos.
Si su hoja de cálculo tiene más de un rango de datos externos del mismo origen, los rangos se
numeran. Por ejemplo, MiTexto, MiTexto_1, MiTexto_2, y así sucesivamente.
Un rango de datos externos tiene otras propiedades (que no se deben confundir con las
propiedades de conexión) que se pueden usar para controlar los datos; por ejemplo, la
conservación del formato de celda y el ancho de columna. Estas propiedades de rango de datos
externos pueden ser modificadas. Para ello, haga clic en Propiedades en el grupo Conexiones de
la ficha Datos y, a continuación, realice los cambios en los cuadros de diálogo Propiedades del
rango de datos externos o Propiedades de los datos externos.
NOTA Si desea compartir un resumen o un informe que está basado en datos externos, puede
dar a otros usuarios un libro que contenga un rango de datos externos o bien puede crear una
plantilla de informe. Una plantilla de informe le permite guardar el resumen o el informe sin guardar
los datos externos de modo que el archivo sea más pequeño. Los datos externos se recuperan
cuando el usuario abre la plantilla del informe.
Descripción de la compatibilidad con orígenes de datos en Excel y servicios de Excel
Hay varios objetos de datos (como un rango de datos externos y un informe de tabla dinámica) que
se pueden usar para conectarse a diferentes orígenes de datos. Sin embargo, el tipo de origen de
datos al que puede conectarse es diferente entre cada objeto de datos. También puede usar y
actualizar datos conectados en Servicios de Excel, pero hay otras limitaciones y soluciones que
debe tener en cuenta.
Compatibilidad con orígenes de datos y objetos de datos de Excel
NOTA Estos archivos, un archivo de texto importado mediante el Asistente para importación de
texto, un archivo XML importado mediante una asignación XML y un archivo HTML o XML
importado mediante una consulta web no usan un controlador ODBC o proveedor OLE DB para
establecer la conexión con el origen de datos.
Compatibilidad con orígenes de datos de servicios de Excel
Si desea mostrar un libro de Excel en Servicios de Excel (Excel en un explorador web), puede
conectarse y actualizar los datos, pero debe usar un informe de tabla dinámica. Servicios de Excel
no es compatible con rangos de datos externos, lo que significa que no admite una tabla de Excel
conectada a un origen de datos, una consulta web, una asignación XML o Microsoft Query.
Sin embargo, puede resolver esta limitación mediante el uso de una tabla dinámica para
conectarse al origen de datos y, a continuación, diseñar y presentar la tabla dinámica como tabla
bidimensional sin niveles, grupos ni subtotales, de modo que se muestren todos los valores de fila
y columna deseados. Para obtener más información, vea los vínculos de la sección Vea también.
Descripción de data Access components
Microsoft Data Access Components (MDAC) 2.8 viene incluido en Microsoft Windows Server 2003
y Windows XP con SP2. Con MDAC, puede conectarse y usar los datos de una amplia variedad de
orígenes de datos relacionales y no relacionales. Puede conectarse a numerosos orígenes de
datos diferentes mediante controladores ODBC (conectividad abierta de bases de datos) o
proveedores OLE DB. Ambos proceden de Microsoft o de otros fabricantes. Al instalar Microsoft
Office, se agregan controladores ODBC y proveedores OLE DB adicionales al equipo.
Windows Vista y Windows 7 usan Windows Data Access Components (Windows DAC).
Para ver una lista completa de los proveedores OLE DB instalados en el equipo, abra el cuadro de
diálogo Propiedades de vínculo de datos de un archivo de vínculo de datos y, a continuación, haga
clic en la pestaña Proveedor.
Para ver una lista completa de los controladores ODBC instalados en el equipo, abra el cuadro de
diálogo Administrador de orígenes de datos ODBC y, a continuación, haga clic en la pestaña
Controladores.
Asimismo, puede utilizar controladores * ODBC y proveedores OLE DB de otros fabricantes para
obtener información de orígenes que no sean orígenes de datos de Microsoft, incluidos otros tipos
de bases de datos ODBC y OLE DB. Para obtener información sobre la instalación de estos
controladores ODBC* o proveedores OLE DB, consulte la documentación de la base de datos o
póngase en contacto con el proveedor de la base de datos.
*NOTA: QUE ES UN CONTROLADOR OBDC.
Open DataBase Connectivity (ODBC) es un estándar de acceso a las bases de datos desarrollado
por SQL Access Group en 1992. El objetivo de ODBC es hacer posible el acceder a cualquier dato
desde cualquier aplicación, sin importar qué sistema de gestión de bases de datos (DBMS)
almacene los datos. ODBC logra esto al insertar una capa intermedia (CLI) denominada nivel de
Interfaz de Cliente SQL, entre la aplicación y el DBMS. El propósito de esta capa es traducir las
consultas de datos de la aplicación en comandos que el DBMS entienda. Para que esto funcione
tanto la aplicación como el DBMS deben ser compatibles con ODBC, esto es que la aplicación
debe ser capaz de producir comandos ODBC y el DBMS debe ser capaz de responder a ellos.
Desde la versión 2.0 el estándar soporta SAG y SQL.
El software funciona de dos modos, con un software manejador en el cliente, o una filosofía cliente-
servidor. En el primer modo, el driver interpreta las conexiones y llamadas SQL y las traduce desde
el API ODBC hacia el DBMS. En el segundo modo para conectarse a la base de datos se crea una
DSN dentro del ODBC que define los parámetros, ruta y características de la conexión según los
datos que solicite el creador o fabricante.
Java Database Connectivity (JDBC) es un derivado inspirado en el mismo, una interfaz de
programación de aplicaciones que permite la ejecución de operaciones sobre bases de datos
desde el lenguaje de programación Java independientemente del sistema operativo donde se
ejecute o de la base de datos a la cual se accede utilizando el dialecto SQL del modelo de base de
datos que se utilice.
Uso de ODBC para conectarse a orígenes de datos
En las siguientes secciones se describe más detalladamente la conectividad abierta de bases de
datos (ODBC).
Arquitectura ODBC
En la arquitectura ODBC, una aplicación (como Excel) se conecta al Administrador de
controladores ODBC, que a su vez usa un controlador ODBC específico (como el controlador
ODBC de Microsoft SQL) para conectarse a un origen de datos (como una base de datos de
Microsoft SQL Server).
Definir la información de conexión
Para conectarse a orígenes de datos ODBC, siga este procedimiento:
Asegúrese de que el controlador ODBC adecuado esté instalado en el equipo que contiene el
origen de datos.
Defina un nombre de origen de datos (DSN) mediante el Administrador de origen de datos ODBC
para almacenar la información de conexión en el Registro o en un archivo DNS, o bien, mediante
una cadena de conexión en código de Microsoft Visual Basic para pasar la información de
conexión directamente al Administrador de controladores ODBC.
Para definir un origen de datos, en Windows Vista, haga clic en el botón Inicio y, a continuación, en
Panel de control. Haga clic en Sistema y mantenimiento y, a continuación, haga clic en
Herramientas administrativas. En Windows XP y en Windows Server, haga clic en Inicio y, a
continuación, haga clic en Panel de control. Haga clic en Rendimiento y mantenimiento, haga clic
en Herramientas administrativas y, a continuación, haga clic en Orígenes de datos (ODBC). Para
obtener más información sobre las diferentes opciones, haga clic en el botón Ayuda en cada
cuadro de diálogo.
Orígenes de datos de equipo
Los orígenes de datos de equipo almacenan información de conexión en el Registro en un equipo
específico con un nombre definido por el usuario. Los orígenes de datos de equipo pueden usarse
únicamente en el equipo en el que están definidos. Hay dos tipos de orígenes de datos de equipo:
del usuario y del sistema. Los orígenes de datos del usuario solo los puede usar y ver el usuario
actual. Los orígenes de datos del sistema los pueden usar y ver todos los usuarios de un equipo.
Los orígenes de datos de equipo son especialmente útiles cuando se desea mejorar la seguridad
porque ayudan a asegurar que solo los usuarios que han iniciado sesión pueden verlos y porque
los usuarios remotos no los pueden copiar a otro equipo.
Orígenes de datos de archivo
Los orígenes de datos de archivo (que también se denominan archivos DSN) almacenan
información de conexión en un archivo de texto en vez del Registro y suelen ser más flexibles que
los orígenes de datos de equipo. Por ejemplo, los orígenes de datos de archivo se pueden copiar a
cualquier equipo con el controlador ODBC adecuado, de modo que la aplicación puede contar con
información de conexión coherente y exacta en todos los equipos donde se usa. O bien, se puede
ubicar el origen de datos de archivo en un solo servidor, compartirlo entre diversos equipos de la
red y mantener fácilmente la información de conexión en una sola ubicación.
Un origen de datos de archivo también puede ser susceptible de no ser compartido. En ese caso,
reside en un solo equipo y apunta a un origen de datos de equipo. Este tipo de origen de datos de
archivo puede usarse para obtener acceso a los orígenes de datos de equipo existentes desde los
orígenes de datos de archivo.
Uso de ole db * para conectarse a orígenes de datos
*NOTA: QUE ES OLE DB
OLE DB (algunas veces escrito como OLEDB u OLE-DB) es la sigla de Object Linking and
Embedding for Databases ("Enlace e incrustación de objetos para bases de datos") y es una
tecnología desarrollada por Microsoft usada para tener acceso a diferentes fuentes de información,
o bases de datos, de manera uniforme.
En las siguientes secciones, se describe más detalladamente la base de datos de vinculación e
incrustación de objetos (OLE DB).
Arquitectura ole db
En la arquitectura OLE DB, la aplicación que obtiene acceso a los datos se denomina consumidor
de datos (como Excel) y el programa que permite el acceso nativo a los datos se denomina
proveedor de base de datos (como el proveedor OLE DB de Microsoft para SQL Server).
Definir la información de conexión
Un archivo Universal Data Link (.udl) contiene la información de conexión que un consumidor de
datos utiliza para obtener acceso a un origen de datos a través del proveedor OLE DB de ese
origen de datos. Puede crear la información de conexión siguiendo uno de estos procedimientos:
En el Asistente para la conexión de datos, use el cuadro de diálogo Propiedades de Data Link para
definir un vínculo de datos para un proveedor OLE DB. Para obtener más información, vea la
sección Importación de datos mediante el Asistente para la conexión de datos.
Cree un archivo de texto en blanco con una extensión de nombre de archivo .udl y, a continuación,
modifique el archivo. De este modo, se muestra el cuadro de diálogo Propiedades de vínculo de
datos.
Actualización de datos
Cuando se conecta a un origen de datos externos, también puede realizar una operación de
actualización para recuperar los datos actualizados. Cada vez que actualice los datos, verá la
versión más reciente de los datos, incluidos los cambios realizados desde la última actualización.
En la siguiente ilustración, se explica el proceso básico que tiene lugar cuando se actualizan datos
conectados a un origen de datos externo.
Proceso básico de actualización de datos externos
Llamada 1 Una operación de actualización obtiene datos actualizados.
Llamada 2 El archivo de conexión define toda la información necesaria para obtener acceso y
recuperar datos de un origen de datos externo.
Llamada 3 Existe una variedad de orígenes de datos que puede actualizar: OLAP, SQL Server,
Access, OLE DB, ODBC, hojas de cálculo y archivos de texto.
Llamada 4 Los datos actualizados se agregan al libro actual.
Excel proporciona muchas opciones para actualizar datos importados, entre los que se incluye la
actualización de datos cada vez que se abra el libro y la actualización automática del mismo a
intervalos de tiempo determinados. Puede continuar trabajando en Excel mientras los datos se
están actualizando, y también puede comprobar el estado de la actualización mientras se
actualizan los datos.
Si su origen de datos externo requiere una contraseña para obtener acceso a los datos, puede
exigir que la contraseña se introduzca cada vez que se actualiza el rango de datos externos.
Windows XP, Windows Vista y Microsoft Office ofrecen los controladores ODBC y proveedores
OLE DB que puede usar para recuperar datos de los siguientes orígenes de datos comunes:
Microsoft Access, archivos HTML de Internet, archivos de texto, Microsoft SQL Server, SQL Server
Analysis Services y archivos XML. Al usar el Asistente para la conexión de datos y Microsoft Query,
también puede tener acceso a muchos otros orígenes de datos que tienen los controladores ODBC
y proveedores OLE DB adecuados, incluso otros libros de Excel, Microsoft FoxPro, dBASE,
Paradox, Oracle y DB2.
TRABAJAR CON DATOS DE ACCESS EN EXCEL
Es posible que desee trabajar con datos de Access en un libro de Excel para aprovechar las
características de análisis de datos y creación de gráficos, la flexibilidad en la presentación y
diseño de los datos o las funciones de Excel que no están disponibles en Access.
Exportar a Access datos desde Excel es posible fácilmente, aprende a exportar al programa de
bases de datos Access tus registros creados en el programa de hoja de cálculo Excel. De esta
manera podrás combinar las funcionalidades de ambos programas sin grandes esfuerzos.
Access es un programa de base de datos incluido en la suite informática Office de Microsoft,
permite gestionar información de grandes volúmenes de datos. Se puede combinar la funcionalidad
de los informes de Access con los cálculos y resultados obtenidos con Excel.
En otros artículos he hablado del paso contrario, esto es traer a Excel datos de Access, importar a
Excel, ahora aprenderás a exportar.
Primero asegúrate de que tienes el programa Access y después localiza un libro Excel que tenga
datos en forma de tabla y en donde la primera fila contenga los registros de los títulos de cada
columna.
Si no tienes de ningún libro con datos, puedes usar los datos del Excel que aparece en la imagen y
crear un libro. Ver imagen ampliada.
Pasos para exportar a Access desde Excel
1. Localiza el nombre y la ubicación de un archivo Excel con datos según lo comentado
anteriormente.
2. Abre el programa Access: Nueva base de datos en blanco.
3. A continuación haz clic en la pestaña Datos externos.
4. Después, dentro del primer grupo de trabajo, llamado Importar, pulsa en el icono donde pone
Excel y esta el logo.
5. Se abre una nueva ventana que es el Asistente que te ayuda en la importación. Su
funcionamiento es muy similar al que se describe en: Importar a Excel datos de otros programas.
6. En la parte superior derecha está el botón Examinar, púlsalo para localizar y seleccionar el libro
Excel que quieres importar.
7. Después indica donde quieres que se guarden los datos en Access, por defecto viene pulsada la
primera opción, déjala así. Aceptar.
8. La tabla aparecerá en tu pantalla y el asistente te pide una serie de opciones para que la
adecues a tus necesidades.
9. Generalmente en estos primeros casos hasta que te habitúes a realizar esta importación, no
será necesario realizara ningún cambio en las diferentes pantallas del asistente, si acaso el
indicarle a Access que alguna de tus columnas de Excel es de tipo fecha.
10. Con respecto a la penúltima pantalla, donde te pide que establezcas una clave principal, deja
que Access la establezca. Con ello se añade una nueva columna en la que se va dando un número
de registro a cada fila.
11. En la última pantalla del asistente puedes dar un nombre a la tabla importada. Después pulsa
Finalizar.
Ya has terminado la importación, desde ahora puedes trabajar con los datos de Excel en Access y
usar las funcionalidades de los informes que te proporciona el programa de base de datos, podrás
crear en pocos minutos interesantes trabajos con multitud de información en unos pocos clics.
Tip: Access te permite guardar la importación realizada de modo que no tengas que repetir todos
los pasos sistemáticamente, así si es una acción que vas a realizar con frecuencia basta con
indicárselo al programa y el realizará los pasos las próximas ocasiones.
Otro Tip: Puedes traer los datos a una base de datos como has visto en este artículo y también
puedes crear en Access una base de datos previa y sobre ella realizar la importación
EXPORTA A ACCESS DATOS
DESDE EXCEL
Exportar a Access datos desde Excel es posible fácilmente, aprende a exportar al programa de
bases de datos Access tus registros creados en el programa de hoja de cálculo Excel. De esta
manera podrás combinar las funcionalidades de ambos programas sin grandes esfuerzos.
Access es un programa de base de datos incluido en la suite informática Office de Microsoft,
permite gestionar información de grandes volúmenes de datos. Se puede combinar la funcionalidad
de los informes de Access con los cálculos y resultados obtenidos con Excel.
En otros artículos he hablado del paso contrario, esto es traer a Excel datos de Access, importar a
Excel, ahora aprenderás a exportar.
Primero asegúrate de que tienes el programa Access y después localiza un libro Excel que tenga
datos en forma de tabla y en donde la primera fila contenga los registros de los títulos de cada
columna.
Si no tienes de ningún libro con datos, puedes usar los datos del Excel que aparece en la imagen y
crear un libro. Ver imagen ampliada.
Pasos para exportar a Access desde Excel
1. Localiza el nombre y la ubicación de un archivo Excel con datos según lo comentado
anteriormente.
2. Abre el programa Access: Nueva base de datos en blanco.
3. A continuación haz clic en la pestaña Datos externos.
4. Después, dentro del primer grupo de trabajo, llamado Importar, pulsa en el icono donde pone
Excel y esta el logo.
5. Se abre una nueva ventana que es el Asistente que te ayuda en la importación. Su
funcionamiento es muy similar al que se describe en: Importar a Excel datos de otros programas.
6. En la parte superior derecha está el botón Examinar, púlsalo para localizar y seleccionar el libro
Excel que quieres importar.
7. Después indica donde quieres que se guarden los datos en Access, por defecto viene pulsada la
primera opción, déjala así. Aceptar.
8. La tabla aparecerá en tu pantalla y el asistente te pide una serie de opciones para que la
adecues a tus necesidades.
9. Generalmente en estos primeros casos hasta que te habitúes a realizar esta importación, no
será necesario realizara ningún cambio en las diferentes pantallas del asistente, si acaso el
indicarle a Access que alguna de tus columnas de Excel es de tipo fecha.
10. Con respecto a la penúltima pantalla, donde te pide que establezcas una clave principal, deja
que Access la establezca. Con ello se añade una nueva columna en la que se va dando un número
de registro a cada fila.
11. En la última pantalla del asistente puedes dar un nombre a la tabla importada. Después pulsa
Finalizar.
Ya has terminado la importación, desde ahora puedes trabajar con los datos de Excel en Access y
usar las funcionalidades de los informes que te proporciona el programa de base de datos, podrás
crear en pocos minutos interesantes trabajos con multitud de información en unos pocos clics.
Tip: Access te permite guardar la importación realizada de modo que no tengas que repetir todos
los pasos sistemáticamente, así si es una acción que vas a realizar con frecuencia basta con
indicárselo al programa y el realizará los pasos las próximas ocasiones.
Otro Tip: Puedes traer los datos a una base de datos como has visto en este artículo y también
puedes crear en Access una base de datos previa y sobre ella realizar la importación de Excel.
EXPORTAR DATOS DE ACCESS A EXCEL
Es difícil pensar en capturar manualmente todos los registros de una tabla. Es por eso que Access
nos permite importar datos de Excel de una manera sencilla. A continuación describiré este
proceso de importación de datos.
A continuación una explicación detallada de cómo podemos exportar datos de acces a Excel sin
tanto esfuerzo, ya que esto nos sirve para crear gráficas, tablas, etc.
Por ejemplo, si nosotros tenemos un negocio y queremos saber que es lo que tenemos y las
cantidades exactas pero no queremos un programa muy difícil, entonces acces y Excel nos ayuda.
Ya que con tan solo seguir los pasos y crear nuestra base de datos, acces hará el resto por
nosotros, es una herramienta muy sencilla y nos ayuda bastante, es una muy buena elección para
llevar los datos de nuestro negocio.
Dos opciones para importar datos
Lo primero que debemos decidir antes de iniciar con la importación de datos a Access es decidir si
haremos la importación hacia una nueva tabla o hacia una tabla ya existente de Access. En caso
de que la importación se realice a una nueva tabla, Access intentará asignar el tipo de datos
adecuado para cada campo pero será necesario realizar una revisión posterior para asegurarnos
de que Access haya asignado los tipos de datos adecuados en cada campo.
La otra opción es crear una tabla anticipadamente de manera que la importación se haga
directamente a ella. De esta manera podremos definir los campos apropiados y el tipo de dato
adecuado para recibir la información proveniente de Excel.
De manera personal prefiero utilizar el primer método y dejar que Access cree una nueva tabla y
posteriormente realizar las adecuaciones pertinentes, pero puedes elegir cualquiera de estos dos
métodos. En este artículo seguiremos la opción de crear una nueva tabla.
Mediante el Asistente para exportación de Access, puede exportar un objeto de base de datos de
Access (como una tabla, una consulta o un formulario) o registros seleccionados en una vista, a
una hoja de cálculo de Excel. Cuando lleve a cabo una operación de exportación en Access, puede
guardar el diseño de la operación para usarlo más adelante e incluso puede programar la
operación de exportación para que se ejecute automáticamente en intervalos especificados.
A continuación se describen algunos escenarios comunes de exportación de datos de Access a
Excel:
Su departamento o grupo utiliza Access y Excel para trabajar con los datos. Éstos están
almacenados en bases de datos de Access, pero utiliza Excel para analizarlos y distribuir los
resultados del análisis. Su equipo exporta los datos a Excel cuando es necesario, pero desearía
que el proceso fuera más eficaz.
Utiliza Access con mucha frecuencia, pero su jefe prefiere ver los informes en Excel.
Periódicamente, tiene que copiar los datos a Excel, pero le gustaría automatizar este proceso para
ahorrar tiempo.
Para obtener más información sobre cómo exportar datos de Access a Excel, vea el sistema de
Ayuda de Access. Como en todos los programas de Microsoft, nos ofrece ayuda*, aveces no es
muy explícita y por ello les damos estos pasos sencillos.
*NOTA:
Hay varias formas de acceder a las ventanas de ayuda de Access, la más común es pulsar el
botón Ayuda en la esquina superior derecha de la ventana de Access, o pulsando la tecla F1. En
ese momento, aparece la ventana de la ayuda de Access en la que puedes introducir palabras
sobre las que quieras respuesta en el cuadro Buscar.
Ayuda en Access
Nuestro consejo es que maximices la ventana para trabajar mejor. Observa en la figura 2.10 el
aspecto de esta ventana cuando la maximizas (haz clic en su botón Maximizar, segundo por la
derecha de la barra de título) y en Mostrar tabla de contenido para ver dónde está ubicada la
búsqueda que has solicitado
La ventana de ayuda contiene una serie de elementos que resulta conveniente conocer:
ð El texto normal, en color negro, contiene las explicaciones que hemos pedido sobre la acción que
queremos llevar a cabo.
ð Si haces clic en un texto que aparezca en color azul, la ayuda mostrará una explicación, en otro
color, de dicho texto. Por ejemplo, en la figura 2.9 aparece la explicación del término campos.
ð Botón Atrás. Este botón nos lleva a las ventanas de ayuda que ya hayamos visto. Es una forma
rápida de volver a buscar un tema por el que ya hayamos pasado.
ð Botón Adelante. Este botón sirve para ir hacia adelante en la búsqueda de ventanas de ayuda
que ya hayamos visto.
ð Detener / Actualizar: cancela una búsqueda en línea si se trata de una consulta a Office Online.
Si estás conectado, puedes pulsar Actualizar para que descargue el último contenido disponible.
ð Inicio: vuelve al contenido de ayuda sugerido por defecto.
ð Botón Imprimir. Este botón permite imprimir la ventana de ayuda que estemos viendo.
ð Cambiar tamaño de fuente: ajusta a más grande o pequeño el tamaño de la letra para ponerlo a
nuestro gusto.
ð Mostrar / Ocultar tabla de contenido: ya lo has utilizado. Muestra la ayuda dividida en libros, que
a su vez contienen más libros. Haciendo clic en el icono que representa un libro cerrado, el icono
se sustituirá por otro que representa un libro abierto y se desplegará bajo él un conjunto de libros
que desglosan un poco más su contenido. Para cerrar un libro, únicamente hay que hacer doble
clic sobre el icono del libro abierto, tras lo cual estaremos en la misma situación que antes de
abrirlo. El último desglose viene representado por el icono de la interrogación que señala un
contenido de ayuda.
ð Mantener visible / no visible la ventana Ayuda: hace que la ventana de ayuda se vea por encima
de otras ventanas de Word, es decir, podemos, por ejemplo, escribir en un documento y leer en la
ayuda al mismo tiempo. Si lo mantenemos como no visible, la ventana de ayuda se ocultará detrás
de la ventana de Access.
Es interesante, si disponemos de conexión a internet, la opción de buscar ayuda en Microsoft
Office Online. Pulsa el botón Estado de conexión en la parte inferior derecha de la ventana de
ayuda y elige la opción Mostrar contenido de Office Online. Tras unos segundos de espera el botón
debería mostrar Conectado a Office Online. A partir de ahora la ayuda de Access será la más
completa.
Cuando termines con la ayuda, sólo tienes que hacer clic en el botón Cerrar de su ventana para
volver a la ventana normal de Access.
Hay una última manera de conseguir ayuda, y es la ayuda contextualizada. En determinadas
ventanas o cuadros de diálogo, el programa tendrá un icono con el signo de interrogación junto al
aspa de cierre, o lo indicará con un texto expresamente, pulsando en cualquiera de estas vías se
abrirá la ventana de ayuda de Access pero contendrá ya un tema relacionado con el cuadro de
diálogo en el que estemos trabajando, y desde el que has abierto esa ayuda.
.
TRABAJAR CON DATOS DE EXCEL EN ACCESS
Tal vez desee trabajar con los datos de Excel en una base de datos de Access para aprovechar las
características de administración de datos, seguridad o multiusuario de Access. Aunque Access
contiene muchas características útiles, hay dos de ellas que son especialmente adecuadas para
los datos de Excel:
Informes Si está familiarizado con el diseño de informes de Access y desea resumir u organizar
los datos de Excel en este tipo de informe, puede crear un informe de Access. Por ejemplo, puede
crear informes más flexibles, como informes de grupo y resumen, etiquetas impresas e informes
gráficos.
Formularios Si desea usar un formulario para buscar o mostrar datos de Excel, puede crear un
formulario de Access. Por ejemplo, puede crear un formulario de Access para mostrar campos en
un orden diferente del orden de las columnas de la hoja de cálculo o para ver más fácilmente una
fila larga de datos en la pantalla.
Para obtener más información sobre cómo trabajar con formularios e informes de Access, vea el
sistema de Ayuda de Access.
Establecer un vínculo a datos de Excel desde Access
Puede vincular un rango de datos de Excel en una base de datos de Access como una tabla. Use
este sistema si tiene previsto mantener el rango de datos en Excel, pero desea también que los
datos estén disponibles en Access. Este tipo de vínculo se crea desde la base de datos de Access,
no desde Excel.
Cuando se establece un vínculo a una hoja de cálculo o un rango con nombre de Excel, Access
crea una nueva tabla que está vinculada a las celdas de origen. Todos los cambios que se realicen
en las celdas de origen en Excel se reflejan en la tabla vinculada. Sin embargo, no puede modificar
el contenido de la tabla correspondiente en Access. Si desea agregar, modificar o eliminar datos,
debe realizar los cambios en el archivo de Excel de origen.
A continuación, se incluyen algunos escenarios comunes de vinculación a una hoja de cálculo de
Excel desde Access:
Desea seguir manteniendo los datos en hojas de cálculo de Excel, pero desea poder usar las
eficaces características de consulta y generación de informes de Access.
Su departamento o grupo de trabajo usa Access pero los datos de orígenes externos con los que
trabaja están en hojas de cálculo de Excel. No desea mantener copias de datos externos sino que
desea poder trabajar con ellos en Access.
EXPORTAR DATOS DE EXCEL A ACCESS
Exportar a Access datos desde Excel es posible fácilmente, aprende a exportar al programa de
bases de datos Access tus registros creados en el programa de hoja de cálculo Excel. De esta
manera podrás combinar las funcionalidades de ambos programas sin grandes esfuerzos.
Access es un programa de base de datos incluido en la suite informática Office de Microsoft,
permite gestionar información de grandes volúmenes de datos. Se puede combinar la funcionalidad
de los informes de Access con los cálculos y resultados obtenidos con Excel.
En otros artículos he hablado del paso contrario, esto es traer a Excel datos de Access, importar a
Excel, ahora aprenderás a exportar.
Primero asegúrate de que tienes el programa Access y después localiza un libro Excel que tenga
datos en forma de tabla y en donde la primera fila contenga los registros de los títulos de cada
columna.
Si no tienes de ningún libro con datos, puedes usar los datos del Excel que aparece en la imagen y
crear un libro. Ver imagen ampliada.
Pasos para exportar a Access desde Excel
1. Localiza el nombre y la ubicación de un archivo Excel con datos según lo comentado
anteriormente.
2. Abre el programa Access: Nueva base de datos en blanco.
3. A continuación haz clic en la pestaña Datos externos.
4. Después, dentro del primer grupo de trabajo, llamado Importar, pulsa en el icono donde pone
Excel y esta el logo.
5. Se abre una nueva ventana que es el Asistente que te ayuda en la importación. Su
funcionamiento es muy similar al que se describe en: Importar a Excel datos de otros programas.
6. En la parte superior derecha está el botón Examinar, púlsalo para localizar y seleccionar el libro
Excel que quieres importar.
7. Después indica donde quieres que se guarden los datos en Access, por defecto viene pulsada la
primera opción, déjala así. Aceptar.
8. La tabla aparecerá en tu pantalla y el asistente te pide una serie de opciones para que la
adecues a tus necesidades.
9. Generalmente en estos primeros casos hasta que te habitúes a realizar esta importación, no
será necesario realizara ningún cambio en las diferentes pantallas del asistente, si acaso el
indicarle a Access que alguna de tus columnas de Excel es de tipo fecha.
10. Con respecto a la penúltima pantalla, donde te pide que establezcas una clave principal, deja
que Access la establezca. Con ello se añade una nueva columna en la que se va dando un número
de registro a cada fila.
11. En la última pantalla del asistente puedes dar un nombre a la tabla importada. Después pulsa
Finalizar.
Ya has terminado la importación, desde ahora puedes trabajar con los datos de Excel en Access y
usar las funcionalidades de los informes que te proporciona el programa de base de datos, podrás
crear en pocos minutos interesantes trabajos con multitud de información en unos pocos clics.
Tip: Access te permite guardar la importación realizada de modo que no tengas que repetir todos
los pasos sistemáticamente, así si es una acción que vas a realizar con frecuencia basta con
indicárselo al programa y el realizará los pasos las próximas ocasiones.
Otro Tip: Puedes traer los datos a una base de datos como has visto en este artículo y también
puedes crear en Access una base de datos previa y sobre ella realizar la importación de Excel.
EXPORTACION DE DATOS DESDE INTERNET
Cómo Exportar Hojas de Trabajo de Discoverer
Puede exportar los datos de la hoja de trabajo para que pueda utilizar los datos en una hoja de
trabajo de Discoverer en una aplicación diferente. Por ejemplo, puede que desee crear una hoja de
trabajo de Discoverer en formato de Microsoft Excel.
Para exportar una hoja de trabajo de Discoverer:
Acceda a la hoja de trabajo que desea exportar (para obtener más información, consulte "Cómo
Abrir una Hoja de Trabajo de Discoverer").
Seleccione el enlace Exportar del área Acciones para mostrar la "página Seleccionar Tipo de
Exportación".
Seleccione un tipo de exportación de la lista desplegable y haga clic en Exportar para mostrar la
"página Exportación Preparada".
Haga clic en el botón Haga Clic para Ver o Guardar para mostrar el archivo exportado en la
aplicación asociada a ese tipo de archivo.
Discoverer muestra el archivo exportado en la aplicación asociada a ese tipo de archivo de
exportación (por ejemplo, un explorador, Microsoft Excel).
(Opcional) Para guardar el archivo exportado, guarde el archivo en la aplicación utilizada para ese
tipo de archivo de exportación (por ejemplo, Microsoft Excel).
¿Cuáles Son las Implicaciones de Utilizar Formatos de Exportación Diferentes?
Cuando haya exportado una hoja de trabajo en un formato de archivo de exportación determinado,
puede haber diferencias al abrir los archivos exportados en la aplicación asociada. Estas
diferencias se describen en los temas siguientes:
En una máquina en la que esté instalado Microsoft Excel, normalmente, el sistema operativo
asocia el archivo XLS a Microsoft Excel.
Nota: Cuando exporte una hoja de trabajo al formato de archivo XLS, se recomienda que guarde el
archivo XLS y que lo abra en Microsoft Excel.
Para obtener más información sobre la exportación de datos de las hojas de trabajo, consulte la
"página Seleccionar Tipo de Exportación".
¿Qué Hace Discoverer al Exportar Datos de Hojas de Trabajo con el Formato de Archivo CSV?
Al exportar datos de la hoja de trabajo al formato de archivo CSV, Discoverer:
Define el tipo MIME de la salida de la hoja de trabajo en application/csv
Solicita que el nombre de archivo sea <nombre_hojatrabajo>.csv
Nota: El servidor de Discoverer no formatea la salida de la hoja de trabajo para que la pueda leer
Microsoft Excel (porque los archivos CSV se utilizan con frecuencia para otros fines, y puede ser
inapropiado imponer un formato específico de Microsoft Excel).
En una máquina en la que esté instalado Microsoft Excel, el sistema operativo no asocia
necesariamente el archivo CSV a Microsoft Excel:
Nota: Aunque pueda abrir un archivo CSV con Microsoft Excel, se recomienda que exporte la hoja
de trabajo al formato de archivo XLS y después abra el archivo XLS en Microsoft Excel.
Para obtener más información sobre la exportación de datos de las hojas de trabajo, consulte la
"página Seleccionar Tipo de Exportación".
Acerca de la Exportación de Hojas de Trabajo de Discoverer al Formato de Consulta Web de
Microsoft Excel
Consulta Web de Microsoft Excel es un formato de datos externo de Microsoft Excel que permite
incluir datos relacionales dinámicos desde una dirección de Internet o de Intranet (por ejemplo, una
hoja de trabajo de Discoverer) en una hoja de trabajo de Microsoft Excel. Por ejemplo, puede que
desee crear una hoja de trabajo de Microsoft Excel que contenga un informe de ventas de
Discoverer de un rango de fechas que especifique al abrir la hoja de trabajo en Microsoft Excel. La
hoja de trabajo de Microsoft Excel almacena la consulta utilizada para obtener los datos de
Discoverer para que, de este modo, los datos se refresquen automáticamente.
Discoverer permite exportar datos de Discoverer al formato Consulta Web de Microsoft Excel
seleccionando Consulta Web de Microsoft Excel 2000+ (*.iqy) en la lista de tipos de exportación. A
continuación, los usuarios finales pueden acceder a hojas de trabajo dinámicas de Discoverer en
Microsoft Excel. Tanto Discoverer Plus como Discoverer Viewer permiten exportar datos al formato
Consulta Web de Microsoft Excel.
Notas
El formato Consulta Web de Microsoft Excel 2000+ (*.iqy) no está disponible en un entorno de
conexión única. Póngase en contacto con el gestor de Discoverer o el Administrador de Oracle
para obtener más información.
Para acceder a los informes de Discoverer exportados al formato Consulta Web de Microsoft Excel,
los usuarios finales de Microsoft Excel necesitan Microsoft Excel 2000 (o posterior) y Microsoft
Internet Explorer 5.0 (o posterior).
Sólo se pueden exportar datos numéricos y de texto en el formato Consulta Web de Microsoft
Excel. Los gráficos de Discoverer no se pueden exportar al formato Consulta Web de Microsoft
Excel.
Una vez que ha importado un informe de Discoverer a Microsoft Excel, refresque la hoja en Excel
cuando desee visualizar datos actualizados. Consulte la documentación de Microsoft Excel para
obtener más información sobre cómo refrescar datos en dicha aplicación.
Para obtener más información acerca de la exportación de datos de hojas de trabajo al formato de
Consulta Web de Microsoft Excel, consulte Oracle Fusion Middleware User's Guide for Oracle
Business Intelligence Discoverer Plus (Guía del Usuario de Oracle Fusion Middleware para Oracle
Business Intelligence Discoverer Plus).
Cómo Configurar una Máquina Cliente para Abrir Automáticamente Archivos Excel Exportados
Para que una máquina cliente abra automáticamente los archivos Excel exportados, debe realizar
la configuración que se describe en los temas siguientes:
"Cómo Configurar Microsoft Windows para que Muestre un Recuadro de Diálogo que Solicite Abrir
o Guardar el Archivo de Exportación"
"Cómo Configurar Internet Explorer para que Muestre el Recuadro de Diálogo Descarga
Terminada"
"Cómo Configurar el Valor de Nivel de Seguridad de Macros para Poder Abrir una Hoja de Trabajo
de Discoverer Exportada en Microsoft Excel"
Cómo Configurar Microsoft Windows para que Muestre un Recuadro de Diálogo que Solicite Abrir o
Guardar el Archivo de Exportación
Para configurar Microsoft Windows para que muestre un recuadro de diálogo que solicite abrir o
guardar el archivo de exportación:
Seleccione Configuración | Panel de control | Opciones de carpeta en el menú Inicio de Windows.
Acceda a la lista de tipos de archivos de Windows.
Seleccione el tipo de archivo para el que desea que se muestre un recuadro de diálogo de solicitud
(para abrir o guardar el archivo de exportación).
Haga clic en Avanzadas para mostrar el recuadro de diálogo Modificar tipo de archivo.
Active la casilla de control Confirmar apertura después de la descarga (una propiedad de tipo
MIME para el tipo de archivo).
Haga clic en Aceptar.
Cómo Configurar Internet Explorer para que Muestre el Recuadro de Diálogo Descarga Terminada
Para configurar Internet Explorer para que muestre el recuadro de diálogo Descarga Terminada
una vez finalizada la exportación
Abra Internet Explorer y seleccione Herramientas | Opciones de Internet | Avanzadas.
Active la casilla de control Notificar cuando se haya completado la descarga.
Haga clic en Aceptar.
Cómo Configurar el Valor de Nivel de Seguridad de Macros para Poder Abrir una Hoja de Trabajo
de Discoverer Exportada en Microsoft Excel
Excel no permite abrir una hoja de trabajo de Discoverer exportada a un archivo XLS si el nivel de
seguridad de macros en Excel está definido en Alto. Excel muestra una alerta y evita que se abra
la hoja de trabajo.
Para configurar el valor de nivel de seguridad de macros para poder abrir una hoja de trabajo de
Discoverer exportada en Microsoft Excel:
Abra Microsoft Excel y seleccione Herramientas | Macro | Seguridad.
Seleccione la opción Medio.
Haga clic en Aceptar.
EXPORTAR ARCHIVOS DE TEXTO EN EXCEL
Los archivos de texto son uno de los métodos de intercambio de datos más populares en la actualidad ya que pueden ser abiertos y consultados por diversas aplicaciones en diferentes sistemas operativos como Windows, Mac OS, Linux, iOS, Android, etc.
Los archivos de texto no dependen de ninguna herramienta de software específica, ni tienen problemas de compatibilidad entre versiones porque se han creado de la misma manera por décadas y por lo tanto cualquier herramienta de edición de documentos puede leer un archivo de texto. Por esta razón es muy probable que en más de una ocasión tengas la necesidad de exportar los datos de Excel a un archivo de texto de manera que tus datos puedan ser integrados o analizados desde otro sistema de información.
Formatos de archivo de texto
Existen dos formatos de archivo de texto que son ampliamente utilizados:
Archivos de texto delimitados por tabulaciones (TXT): Cada valor de la columna está separado por el carácter de tabulación. A simple vista tendrás la impresión de que existen varios espacios en blanco entre cada columna, pero en realidad es solo el carácter tabulador el que los separa.
Archivos de texto delimitados por comas (CSV): Cada campo en el archivo de texto está separado por una coma. Este formato es tan conocido y utilizado que es almacenado con su propia extensión de archivo .CSV aunque sigue siendo un archivo de texto plano. Las siglas CSV provienen de su nombre en inglés Comma-Separated Values.
A continuación veremos lo simple que es crear cada uno de estos dos tipos de archivos en Excel. La hoja de datos que exportaremos en nuestros ejemplos será la siguiente:
Crear un archivo de texto delimitado por tabulaciones
Para crear un archivo de texto delimitado por tabulaciones en Excel debemos pulsar la ficha Archivo y seleccionar la opciónGuardar como. Se mostrará el cuadro de diálogo Guardar como y para la opción Tipo debemos elegir Texto (delimitado por tabulaciones):
Una vez que pulses el botón Guardar se mostrarán algunas advertencias. La primera de ellas nos advierte que el tipo de archivo seleccionado no es compatible con libros que tienen múltiples hojas y que por lo tanto solo se exportarán los datos de la hoja activa. Esto quiere decir que, si tienes un libro con datos en varias hojas, deberás exportar cada hoja a un archivo de texto diferente.
Será muy probable que también se muestre una segunda advertencia indicando que nuestro archivo contiene formatos que no son compatibles con los archivos de texto delimitados por tabulaciones. Solamente deberás pulsar el botón Sí para continuar con el proceso y como resultado obtendremos un archivo de texto como el siguiente:
Crear un archivo de texto delimitado por comas
El procedimiento para crear un archivo de texto delimitado por comas (CSV) es prácticamente similar al ejemplo anterior con la diferencia de que en el cuadro de diálogo Guardar como debemos seleccionar el tipo de archivo CSV (delimitado por comas):
Obtendremos las mismas advertencias que en el ejemplo anterior pero como resultado tendremos el siguiente archivo de texto:
Dependiendo del país donde te encuentres y la configuración regional de tu equipo, es probable que Excel genere este tipo de archivo utilizando el punto y coma (;) en lugar de la coma (,) pero esto no es un asunto de Excel sino de Windows y el carácter definido como el separador de listas. A continuación veremos cómo cambiar el carácter utilizado por Excel para generar los archivos de texto.
Carácter delimitador para archivos de texto
Por supuesto que para cambiar el carácter delimitador utilizado por Excel al crear el archivo de texto se podría hacer un simple remplazo con cualquier editor de texto, pero si ésta es una actividad que realizas frecuentemente tal vez sea conveniente hacer un cambio de fondo. El carácter utilizado por Excel como delimitador en un archivo de texto es el que se encuentra definido en Panel de Control > Reloj, idioma y región > Cambiar formato de fecha, hora o número y dentro del cuadro de diálogo mostrado pulsar el botón Configuración adicional:
En dicho cuadro de diálogo encontrarás la opción Separador de listas y el carácter especificado es el utilizado por Excel. Para mi configuración regional, que es Español (México), tengo la coma (,) como el separador de listas pero si hago el cambio hacia punto y coma, al crear de nuevo el archivo CSV obtendré el siguiente resultado:
Observa que en esta ocasión Excel ha utilizado el punto y coma como el separador entre cada uno de los campos.
Campos de longitud determinada
En algunas ocasiones me he encontrado con el requerimiento de generar un archivo de texto que tenga campos de longitud determinada es decir, en caso de que una cadena de texto no sea de la longitud máxima permitida, se deberá rellenar con espacios en blanco. La solución a este problema es tratar los datos dentro de Excel antes de generar el archivo de texto.
Supongamos que para nuestra hoja de datos de ejemplo necesitamos crear un archivo de texto que tenga un ancho máximo de 15 caracteres, tanto para la columna Nombre como para la columna Apellido, y una longitud máxima de 25 caracteres para el correo electrónico. Los datos los generaré en una hoja diferente y utilizaré la siguiente fórmula para la primera columna:
=CONCATENAR(Hoja1!A1,REPETIR(" ",15-LARGO(Hoja1!A1)))
La función principal de esta fórmula es la función CONCATENAR que como primer argumento tiene el texto original de la Hoja1. El segundo argumento de la función CONCATENAR es la función REPETIR que tendrá el objetivo de generar una cadena de espacios en blanco cuyo número estará determinado por la diferencia entre el número máximo de caracteres admitidos (15) menos el número de caracteres de la cadena de texto original el cual obtenemos con la función LARGO. Por ejemplo, para la celda con el nombre Carlos, la función REPETIR generará una cadena de 9 espacios en blanco que será concatenada a la derecha de la cadena de texto “Carlos”.
La fórmula anterior nos sirve para obtener los valores de las columnas Nombre y Apellido. Para la columna Correo electrónico solo es necesario modificar el segundo argumento de la función REPETIR reemplazando el número 15 por el número 25 que es el máximo número de caracteres permitido para dicha columna.
Una vez aplicada esta fórmula a nuestro rango de datos podremos realizar la exportación a un archivo de texto. Ya que en este caso no necesitamos ningún tipo de separador podemos guardar el archivo como Texto Unicode y el resultado será el siguiente:
Así como en este último ejemplo he dado un tratamiento especial a los datos antes de exportarlos, es posible hacer diferentes modificaciones para ajustarnos a los requerimientos del archivo de texto que vamos a exportar. Algunas modificaciones que se realizan con cierta frecuencia son las siguientes:
Colocar comillas simples o dobles alrededor de las cadenas de texto.
Exportar valores numéricos con algún número específico de decimales
Dar algún formato específico a una fecha por ejemplo, cambiar al formato inglés que utiliza mm/dd/aa en lugar de dd/mm/aa.
Estos cambios los podemos hacer con funciones de Excel de manera que coloquemos los datos en una hoja tal como los necesitamos y posteriormente hacer la exportación hacia un archivo de texto.
EXPORTACION DE DATOS DESDE MICROSOFT SQL SERVER
Decir que Azure, la plataforma Cloud de Microsoft, es una de las más potentes del mercado pese
a su corto tiempo de vida no es algo nuevo aunque, precisamente por su juventud, nos
encontramos con algunas carencias que hacen algo más complicado las gestiones típicas de un
sistema en producción.
Una de estas carencias es la posibilidad de automatizar backups (copias de seguridad) de
nuestras bases de datos SQL SERVER* algo que necesitamos tener cuando nuestro proyecto
pasa el estado de desarrollo.
*NOTA:
Definición de Microsoft SQL Server
Microsoft SQL Server es un sistema de gestión de bases de datos relacionales (SGBD) basado en
el lenguaje Transact-SQL, y específicamente en Sybase IQ. Este motor de base de datos admite
multitud de usuarios conectados a la base de datos de forma simultánea y concurrente.
Algunas de las características de Microsoft SQL Server:
Soporte de transacciones.
Escalabilidad, estabilidad y seguridad.
Soporta procedimientos almacenados.
Incluye también un potente entorno gráfico de administración, que permite el uso de comandos
DDL y DML gráficamente.
Permite trabajar en modo cliente-servidor, donde la información y datos se alojan en el servidor y
las terminales o clientes de la red sólo acceden a la información.
Permite administrar información de otros servidores de datos.
Este sistema de gestión de bases incluye una versión reducida, llamada MSDE con el mismo motor
de base de datos pero orientado a proyectos más pequeños, que en su versión 2005 pasa a ser el
SQL Express Edition, que se distribuye en forma gratuita.
La única posibilidad que nos ofrece Azure en este momento (25/06/2013)
es exportar/importar nuestras bases de datos a un blob desde el panel de control a petición pero
no podemos programar esta tarea para que se ejecute periódicamente.
Esta opción genera en uno de nuestros containers un blob con extensión .bacpac con la copia de
seguridad de la base de datos escogida para poder importarla en cualquier momento.
La pregunta es, ¿cómo podemos hacer esto automáticamente? La respuesta, con una aplicación
que llame periódicamente a la misma API REST que usa Azure para estas tareas.
Azure Sql Server API REST
Azure Sql Server expone un API REST con algunas tareas básicas que podemos invocar (con las
debidas credenciales, claro está) para exportar o importar nuestras bases de datos contra Azure
Storage.
Cada una de las regiones de Azure Sql Server tiene su propia url del servicio por lo que deberás
asegurarte de usar la url correcta en función de en que región se encuentre tu base de datos:
DataCenter Endpoint
North Central US https://ch1prod-dacsvc.azure.com/DACWebService.svc/
South Central US https://sn1prod-dacsvc.azure.com/DACWebService.svc/
North Europe https://db3prod-dacsvc.azure.com/DACWebService.svc/
West Europe https://am1prod-dacsvc.azure.com/DACWebService.svc/
East Asia https://hkgprod-dacsvc.azure.com/DACWebService.svc/
Southeast Asia https://sg1prod-dacsvc.azure.com/DACWebService.svc/
East US https://bl2prod-dacsvc.azure.com/DACWebService.svc/
West US https://by1prod-dacsvc.azure.com/DACWebService.svc/
Las dos tareas expuestas por este servicio son "Export" e "Import" (para exportar e importar
respectivamente) por lo que, por ejemplo, para solicitar una exportación de una base de datos
alojada en el Norte de Europa deberíamos hacer unPOST contra la url: https://db3prod-
dacsvc.azure.com/DACWebService.svc/Export
Solicitando Exportación desde código*
*NOTA:
En teoría de la comunicación, un código es un conjunto de elementos que se combinan siguiendo
ciertas reglas y que son semánticamente intepretables, lo cual permite intercambiar información.
En este contexto, las sociedades humanas se caracterizan principalmente porque, valiéndose de
unidades sonoras significativas, logran comunicarse a través del código más complejo: las lenguas
humanas o códigos lingüísticos.
El emisor y el receptor deben utilizar el mismo código para que la comunicación sea posible.
Aunque todos los otros elementos del circuito comunicativo funcionen adecuadamente, la
comunicación no tendrá éxito si el emisor y el receptor no comparten el mismo código.
Para automatizar el proceso de exportación podemos programar un pequeño Worker Role de
Azure, un servicio de Windows que se ejecute en uno de nuestros servidores locales o incluso
que lo implemente nuestra propia web de administración. En mi caso dispongo de un Worker
Role propio con varias tareas de mantenimiento que se ejecutan cada cierto tiempo por lo que me
fue muy fácil incluir una nueva tarea para hacer este backup.
Para hacer esta exportación, como hemos comentados antes, basta con hacer un POST contra
la [url del servicio] + "/Export" incluyendo en el cuerpo de la petición un xml con los datos
necesarios para la tarea.
Este xml que tenemos que postear tiene el siguiente formato:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<ExportInput
xmlns="http://schemas.datacontract.org/2004/07/Microsoft.SqlServer.Management.Dac.ServiceTypes"
xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<BlobCredentials
i:type="BlobStorageAccessKeyCredentials">
<Uri>[URL del Blob a crear]</Uri>
<StorageAccessKey>[Clave de acceso del Azure Storage]</StorageAccessKey>
</BlobCredentials>
<ConnectionInfo>
<DatabaseName>[Nombre de la base de datos a exportar]</DatabaseName>
<Password>[Password del usuario de la base de datos]</Password>
<ServerName>[Nombre del servidor en el que se encuentra la base de datos]</ServerName>
<UserName>[Usuario de acceso a la base de datos]</UserName>
</ConnectionInfo>
</ExportInput>
Los parámetros que debes rellenar son:
[URL del Blob a crear]
o Url completa del blob que vas a crear con ".bacpac" al final.
Ej: https://mistorageenazure.blob.core.windows.net/bak/backup-2013-06-
25_09-49.bacpac
[Clave de acceso del Azure Storage]
o La "Primary Access Key" de nuestro Storage en Azure. La podemos encontrar en
el panel de administración de Azure > Storage > [el storage al que queremos
acceder] > Manage Access Keys
[Nombre del servidor en el que se encuentra la base de datos]
o Nombre completo del servidor de SQL SERVER en el que se encuentra la base de
datos a exportar (debe terminar en "database.windows.net")
Ej: server.database.windows.net
Datos de la BD
o Nombre, usuario y contraseña de la base de datos a exportar
Con este xml generado ya solo necesitamos hacer un POST contra la url del servicio para solicitar
la tarea de exportación.
Este servicio nos devuelve un GUID para la posterior comprobación del estado de la tarea.
Podemos guardarlo si queremos para poder saber si ha terminado con éxito o no.
Aquí un ejemplo con el código completo:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
private Guid RequestExport(string databaseName, string containerUrl, string storageAccessKey, string blobName)
{
// Método que devolvería la URL del Servicio en función de la Región. Yo le paso un tipo enum propio por comodidad
var managementUrl = GetAzureManagementUrlByRegion(AzureRegions.NorthEurope);
var request = WebRequest.Create(managementUrl + "/Export");
request.Method = "POST";
var dataStream = request.GetRequestStream();
// Método que genera el XML que se le pasa como Body con el formato comentado anteriormente
var body = GetExportToBlobBodyRequest(blobUrl, storageAccessKey, databaseName);
var utf8 = new UTF8Encoding();
var buffer = utf8.GetBytes(body);
dataStream.Write(buffer, 0, buffer.Length);
dataStream.Close();
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
request.ContentType = "application/xml";
// La respuesta HTTP contiene el GUID que representa la tarea serializado como XML
using (var response = request.GetResponse())
{
var encoding = Encoding.GetEncoding(1252);
using (var responseStream = new StreamReader(response.GetResponseStream(), encoding))
{
using (var reader = XmlDictionaryReader.CreateTextReader(responseStream.BaseStream, new XmlDictionaryReaderQuotas()))
{
var serializer = new DataContractSerializer(typeof(Guid));
return (Guid)serializer.ReadObject(reader, true);
}
}
}
}
¡Listo! Nuestra tarea de exportación ya se está lanzando y, si todo funciona como debe, en breve
se creará un nuevo blob con el contenido de nuestra base de datos para poder importarlo en caso
de catástrofe o para replicarlo en otra base de datos distinta para hacer pruebas.
Comprobando el estado de la tarea
Si queremos saber si todo ha ido bien o ha fallado algo podemos hacer GET a otro método del API
REST de Azure llamado "Status" indicando el GUID obtenido en la petición de la tarea.
Para ello basta con hacer GET de la siguiente url:
1 [Url del servicio API REST de tu Región]/Status?servername=[servidor de base de datos completo]&username=[usuario de la base de datos]&password=[password de la base de datos]&reqId=[GUID de la tarea]
Esta url nos debe devolver un XML con información del estado de la tarea.
EXPORTACION DE DATOS DESDE MICROSOFT SQL SERVER ANALYSIS SERVICES
Obtener datos de Analysis Services
Analysis Services proporciona datos dimensionales adecuados para la exploración de datos en
informes de tablas dinámicas y Power View. Puede obtener los datos de Analysis Services desde:
Cubos OLAP en un servidor multidimensional de Analysis Services.
Los modelos tabulares de un servidor tabular de Analysis Services.
Los libros de Excel 2013 en SharePoint 2013, si contienen un modelo de datos.
PowerPivot Los libros en SharePoint 2010.
Puede analizar los datos de un origen de datos externos de Analysis Services mediante una
conexión en línea. Cuando gira, desliza y filtra un informe, Excel consulta en Analysis Services los
datos que se solicitaron. Esta forma de analizar datos requiere una conexión en línea al origen de
datos.
Otra manera de hacerlo es trabajar sin conexión. Puede trabajar sin conexión importando los datos
en el libro para que los datos y las visualizaciones del informe sean autónomos y totalmente
portables en un solo archivo. Si importa los datos, debe saber que entran en juego nuevos
requisitos, incluidos el complemento PowerPivot, algún conocimiento de MDX y suficiente espacio
en disco para almacenar libros más grandes. En este artículo se explica cómo importar datos
desde diferentes orígenes de datos de Analysis Services.
Los libros de Excel que guarde en SharePoint u Office 365 están sujetos a un tamaño máximo de
archivo. Con el asistente para la importación de tablas del complemento PowerPivot, puede
importar tablas, columnas y filas a elección para reducir el tamaño del archivo. Si el libro es
demasiado grande para verse en Excel Online, puede abrirlo en Excel 2013 en su lugar.
Office 365 prohíbe la actualización de datos con orígenes de datos externos, incluidas las
soluciones de Analysis Services que se ejecutan en servidores de una red. Si requiere datos que
se puedan actualizar, elija SharePoint o haga un uso compartido de archivos de red en su lugar.
EXPORTACION DE DATOS XML*
Convertir distintos tipos de archivos te ayuda a usarlos con otros programas para los que quizás no
están específicamente desarrollados. El Extensible Markup Language (XML) es un conjunto de
estándares de lenguajes de programación usados para múltiples propósitos, pero usualmente
pueden ser usados para estructuras de bases de datos. XLS es el tipo de archivo de hoja de
cálculo estándar de los documentos de Microsoft Excel. Si deseas usar datos de XML en Microsoft
Excel, el programa tiene la funcionalidad incorporada de importar archivos XML por sí mismo.
Instrucciones
1 Abre Excel.
2 Haz clic en File -archivo-, luego en Open -abrir-, y finalmente en el menú desplegable
Files of type -archivos de tipo-. Selecciona XML (*.xml) y luego busca tu archivo XML.
Selecciónalo y haz clic en Open.
3 Selecciona As an XML list -como una lista XML- para agregar todos los datos de XML en
una lista. Si tu estructura XML tiene muchos encabezados similares entonces serán
repetidos. Selecciona As a read-only workbook -hoja de trabajo de sólo lectura- si deseas
importar el documento XML como una grilla estándar basada en el documento XML en
lugar de una lista. Selecciona Use the XML Source task panel -usa el panel de tareas de la
fuente XML- si deseas tener más control sobre lo que importas. Entonces arrastrarás los
ítems que deseas mapear en diferentes columnas y filas y luego importar datos para cada
entrada XML.
4 Haz clic en OK y Excel abrirá tu documento XML. Haz clic en File -archivo- y luego en
Save as -guardar como- para guardarlo como un documento XLS.
Otra opción
Exportación de datos XML
Dukascopy Trading Technologies se complace en presentarles su nuevo servicio, creado a la
medida de sus necesidades, para todos aquellos portales de información que deseen integrar los
datos de Dukascopy en sus productos o servicios. Los datos se presentan en formato XML,
incluyen todos los instrumentos disponibles de Dukascopy y se pueden integrar en una amplia
variedad de aplicaciones cliente, incluyendo tablas de información, gráficos, etc. Si desea
registrarse para recibir los datos XML de Dukascopy, siga estos 3 pasos simples:
Lea el Contrato de uso de los datos XML así como la cláusula de exención de responsabilidad
relativa a los Productos Web Gratuitos;
Complete el formulario de registro que aparece a continuación;
Una vez que se haya aprobado su solicitud, recibirá su Número de Identificación Único (UIN) que le
permitirá acceder al servicio. Para empezar a utilizar el servicio, consulte el apartado de
instrucciones técnicas y siga las instrucciones para comenzar a recibir datos XML de Dukascopy.
*Nota
Si obtiene "error de análisis XML en línea 1, columna 1", probablemente no tiene la versión
correcta del proveedor OLE DB de Analysis Services. Puede instalar PowerPivot para Excel o
puede descargar e instalar el proveedor OLE DB de Microsoft SQL Server Analysis Services desde
la página SQL Server 2008 R2 Feature Pack del sitio web de Microsoft.
EXPORTACION DE DATOS MEDIANTE EL ASISTENTE PARA LA CONEXIÓN DE DATOS
Puede utilizar un asistente para exportar información de varias conexiones de base de datos a un
archivo XML, que se puede utilizar para volver a llenar el Explorador de fuentes de datos en un
espacio de trabajo nuevo.
Antes de empezar
Antes de exportar información de conexión de base de datos a un archivo XML, debe crear una
conexión con una base de datos en el Explorador de fuentes de datos.
Acerca de esta tarea
Utilizando este método, puede exportar varias conexiones de base de datos a la vez y puede incluir información de ID de usuario y contraseña en el archivo XML. Una vez generado el archivo XML, puede llenar el Explorador de fuentes de datos en un nuevo espacio de trabajo especificando la vía de acceso a este archivo XML en la página Opciones administrativas de la ventana Preferencias.
Procedimiento
Para exportar información de conexión de base de datos a un archivo XML utilizando un asistente:
1. Pulse en Archivo > Exportar. 2. En el Asistente para exportar, expanda Datos y seleccione Descriptores de bases de
datos y a continuación, pulse en Siguiente. 3. En la página Elegir conexiones, seleccione las conexiones que desea exportar y seleccione
las opciones para la exportación. Puede elegir si desea incluir información de ID de usuario y contraseña y dónde desea almacenar el archivo XML.
4. Pulse Finalizar. 5. En la ventana que se abre, navegue hasta la carpeta del sistema de archivos local en el
que desea guardar el archivo XML, especifique un nombre para el archivo y pulse enBien.
Resultados
El archivo XML que se especifica se guarda en la carpeta especificada. Se crea un archivo XML
para cada conexión de base de datos. Un archivo de esquema XML
denominadoDBDescriptor.xsd se guarda en la misma carpeta. El ejemplo siguiente muestra el tipo
de información que se guarda en el archivo XML:
<database xmlns:tns="DBDescriptor"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" conName="SAMPLE"
dbName="SAMPLE" defProduct="DB2 UDB" defVersion="V8.2"
driverClass="com.ibm.db2.jcc.DB2Driver" driverPath="C:\Program
Files\IBM\SDP70Shared\plugins\com.ibm.datatools.db2_1.0.100.v200703190751
\driver\db2jcc.jar;C:\Program
Files\IBM\SDP70Shared\plugins\com.ibm.datatools.db2_1.0.100.v200703190751
\driver\db2jcc_license_cisuz.jar" identifierDelimiter="""
prodVersion="SQL08025" url="jdbc:db2://localhost:50000/SAMPLE"
xsi:schemaLocation="DBDescriptor.xsd">
<property key="user" value="user" />
<property key="retrieveMessagesFromServerOnGetMessage" value="true" />
<variable key="jdklocation" value="" />
<variable key="jdklevel" value="" />
<variable key="wlmEnvironment" value="" />
<variable key="runTimeOptions" value="" />
</database>
INDICE
TEMA 12: MANEJO DE HERRAMIENTAS PARA IMPORTACION Y EXPORTACION DE DATOS
2.1 USO DE LAS FUNCIONES DE EXCEL PARA LA IMPORTACION DE DATOS
2.2 MANEJO DE LAS FUNCIONES DE EDICION, SUSTITUCION Y ALTA DE DATOS DESDE EXCEL
2.3 USO DE LAS FUNCIONES DE EXCEL PARA LA EXPORTACION DE DATOS
2.4 VALORACION DE LA IMPORTACIA DE LAS HERRAMIENTAS PARA IMPORTACION Y
EXPORTACION DE DATOS.
VALORACIÓN DE LA IMPORTANCIA DE LAS HERRAMIENTAS PARA IMPORTACIÓN Y EXPORTACIÓN DE 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.
Puede importar o exportar hasta 1.048.576 filas y 16.384 columnas.
Tanto si va a importar o exportar datos, es posible que desee obtener más información sobre los convertidores de texto de Excel. Para obtener más información, vea la sección sobre formatos de texto en el tema de Ayuda sobreformatos de archivo compatibles en Excel y la sección sobre formatos de archivos de texto en el tema de Ayuda sobreel formato y las funciones de Excel que no se transfieren a otros formatos de archivo.
Formatos de archivo que admite Excel
Para guardar un archivo de Microsoft Office Excel 2007 en otro formato de archivo, haga clic en
el botón de Microsoft Office y, a continuación, en Guardar como. Los formatos de archivo disponibles en el cuadro de diálogo Guardar como varían, dependiendo del tipo de hoja que esté activa (una hoja de cálculo, de gráfico o de otro tipo).
Para abrir un archivo que se creó en otro formato (en una versión anterior de Microsoft Office Excel
o en otro programa), haga clic en el botón de Microsoft Office y, a continuación, en Abrir. Se le pedirá que lo guarde en un formato de Office Excel 2007, a menos que abra un libro que esté compartido. Para los archivos creados en una versión anterior de Excel, tiene la opción de mantener el formato original.
Cada vez que se guarde un archivo en otro formato, es posible que se pierda parte de su formato, sus datos y sus características. Para obtener más información, vea Formato y funciones que no se transfieren en las conversiones de formato de archivo de Excel.
Formatos de Excel
FORMATO EXTENSIÓN DESCRIPCIÓN
Libro de Excel .xlsx Formato de archivo basado en XML predeterminado Office Excel 2007. No se pueden almacenar código de macros de Microsoft Visual Basic para
Aplicaciones (VBA) ni hojas de macros de Microsoft Office Excel 4.0 (.xlm).
Libro de Excel (código)
.xlsm Formato de archivo de Office Excel 2007 basado en XML y habilitado para macros. Almacena código de macros de VBA u hojas de macros de Excel 4.0 (.xlm) .
Libro de Excel binario
.xlsb Formato de archivo binario (BIFF12) de Office Excel 2007.
Plantilla .xltx Formato de archivo de Office Excel 2007 predeterminado para una plantilla de Excel. No puede almacenar código de macros de VBA ni hojas de macros de Excel 4.0 (.xlm).
Plantilla (código) .xltm Formato de archivo habilitado para macros de Office Excel 2007 para una plantilla de Excel. Almacena código de macros de VBA u hojas de macros de Excel 4.0 (.xlm).
Libro de Excel 97-Excel 2003
.xls El formato de archivo binario (BIFF8) de Excel 97 - Excel 2003.
Plantilla de Excel 97- Excel 2003
.xlt El formato de archivo binario (BIFF8) de Excel 97 - Excel 2003 para una plantilla de Excel.
Libro de Microsoft Excel 5.0/95
.xls El formato de archivo binario (BIFF5) de Excel 5.0/95.
Hoja de cálculo XML 2003
.xml Formato de archivo de hoja de cálculo XML 2003 (XMLSS).
Datos XML .xml Formato de datos XML.
Complemento de Excel
.xlam Complemento basado en XML y habilitado para macros de Office Excel 2007, un programa complementario que está diseñado para ejecutar código adicional. Admite el uso de proyectos de VBA y hojas de macros de Excel 4.0 (.xlm).
Complemento de Excel 97-2003
.xla Complemento de Excel 97-2003, un programa complementario diseñado para ejecutar código adicional. Admite el uso de proyectos de VBA.
Libro de Excel 4.0 .xlw Formato de archivo de Excel 4.0 que guarda sólo hojas de cálculo, hojas de gráficos y hojas de macros. Puede abrir un libro en este formato de archivo en Office Excel 2007, pero no puede guardar un archivo de Excel en este formato.
Formatos de texto
FORMATO EXTENSIÓN DESCRIPCIÓN
Texto con formato (delimitado por espacios)
.prn Formato de Lotus delimitado por espacios. Guarda únicamente la hoja activa.
Texto (delimitado por tabulaciones)
.txt Guarda un libro como un archivo de texto delimitado por tabulaciones para su uso en otro sistema operativo Microsoft Windows y garantiza que los caracteres de tabulación, los saltos de línea y otros caracteres se interpreten correctamente. Guarda únicamente la hoja activa.
Text (Macintosh) .txt Guarda un libro como un archivo de texto delimitado por tabulaciones para su uso en un sistema operativo Macintosh y garantiza que los caracteres de tabulación, los saltos de línea y otros caracteres se interpreten correctamente. Guarda únicamente la hoja activa.
Texto (MS-DOS) .txt Guarda un libro como un archivo de texto delimitado por tabulaciones para su uso en un sistema operativo MS-DOS y garantiza que los caracteres de tabulación, los saltos de línea y otros caracteres se interpreten correctamente. Guarda únicamente la hoja activa.
Texto Unicode .txt Guarda un libro como texto Unicode, un estándar de codificación de caracteres que fue desarrollado por el consorcio Unicode.
CSV (delimitado por comas)
.csv Guarda un libro como un archivo de texto delimitado por comas para su uso en otro sistema operativo Windows y garantiza que los caracteres de tabulación, los saltos de línea y otros caracteres se interpreten correctamente. Guarda únicamente la hoja activa.
CSV (Macintosh) .csv Guarda un libro como un archivo de texto delimitado por comas para su uso en un sistema operativo Macintosh y garantiza que los caracteres de tabulación, los saltos de línea y otros caracteres se interpreten correctamente. Guarda únicamente la hoja activa.
CSV (MS-DOS) .csv Guarda un libro como un archivo de texto delimitado por comas para su uso en un sistema operativo MS-DOS y garantiza que los caracteres de tabulación, los saltos de línea y otros caracteres se interpreten correctamente. Guarda únicamente la hoja activa.
DIF .dif Formato para intercambio de datos. Guarda únicamente la hoja activa.
SYLK .slk Formato de vínculo simbólico. Guarda únicamente la hoja activa.
Si se guarda un libro en formato de texto, se perderá todo el formato.
Otros formatos
FORMATO EXTENSIÓN DESCRIPCIÓN
DBF 3, DBF 4 .dbf dBase III y IV. Puede abrir estos formatos de archivo en Excel, pero no puede guardar un archivo de Excel en el formato dBase.
Hoja de cálculo de OpenDocument
.ods Hoja de cálculo de OpenDocument. Puede guardar archivos de Excel 2007 para que puedan abrirse en aplicaciones de hoja de cálculo que usen el formato de hoja de cálculo de OpenDocument, como Google Docs y Calc de OpenOffice.org. También puede abrir hojas de cálculo en formato .ods en Excel 2007. Puede perderse el formato al guardar y abrir archivos .ods.
Formatos de Portapapeles
Puede pegar datos desde el Portapapeles de Microsoft Office en Excel mediante los comandos Pegar o Pegado especial (ficha Inicio, grupo Portapapeles, botón Pegar) si los datos del Portapapeles de Office están en uno de los formatos siguientes.
FORMATO EXTENSIÓN IDENTIFICADORES DE TIPO DE PORTAPAPELES
Imagen .wmf o .emf Imágenes en formato de metarchivo de Windows (WMF) o en formato de metarchivo mejorado de Windows (EMF).
NOTA Si copia una imagen de metarchivo de Windows desde otro
programa, Excel pega la imagen como un metarchivo mejorado.
Mapa de bits .bmp Imágenes almacenadas en formato de mapa de bits (BMP).
Formato de archivo de Microsoft Excel
.xls Formatos de archivos binarios para versiones de Excel 5.0/95 (BIFF5), Excel 97-2003 (BIFF8) y Office Excel 2007 (BIFF12).
SYLK .slk Formato de vínculo simbólico.
DIF .dif Formato para intercambio de datos.
Texto (delimitado por tabulaciones)
.txt Formato de texto separado por tabulaciones.
CSV (delimitado por comas)
.csv Formato de valores separados por comas.
Texto con formato (delimitado por espacios)
.rtf Formato de texto enriquecido (RTF). Solamente de Excel.
Objeto incrustado .gif, .jpg, .doc, .xls o .bmp
Objetos de Microsoft Excel, objetos de programas registrados adecuadamente que admiten OLE 2.0 (OwnerLink), Picture u otro formato de presentación.
Objeto vinculado .gif, .jpg, .doc, .xls o .bmp
OwnerLink, ObjectLink, Link, Picture u otro formato.
Objeto de dibujo de Office
.emf Formato de objeto de Office o Picture (formato de metarchivo mejorado de Windows, EMF)
Texto .txt Muestra texto, texto OEM
HTML .htm Lenguaje de marcado de hipertexto.
NOTA Al copiar texto de otro programa, Excel pega el texto con el
formato HTML, independientemente del formato del texto original.
Formatos de archivo que no admite Excel 2007
Los siguientes formatos de archivo ya no se admiten en Office Excel 2007. No puede abrir o guardar archivos en estos formatos.
FORMATO EXTENSIÓN IDENTIFICADORES DE TIPO DE PORTAPAPELES
Gráfico de Excel .xlc Formatos de archivo Excel 2.0, 3.0 y 2.x
WK1, FMT, WK2, WK3, FM3, WK4
.wk1, .wk2, .wk3, .wk4,
.wks Formatos de archivo Lotus 1-2-3 (todas las versiones)
Microsoft Works .wks Formato de archivo Microsoft Works (todas las versiones)
DBF 2 .dbf Formato de archivo DBASE II
WQ1 .wq1 Formato de archivo de Quatro Pro para MS-DOS
WB1, WB3 .wb1, .wb3 Quattro Pro 5.0 y 7.0 para Windows.
Sugerencias para formatos de archivo que no son compatibles con Excel
Si el formato de archivo que desea usar no es compatible con Excel, intente lo siguiente:
Buscar en World Wide Web (WWW) una compañía que fabrique convertidores de formato de archivos para formatos de archivo que no sean compatibles con Excel.
Guardar en un formato de archivo que sea compatible con otros programas. Por ejemplo, puede que desee importar una hoja de cálculo a otro programa que no sea compatible con el formato de archivo de Excel. Pero puede ser que el otro programa pueda importar a otro formato de archivo compatible, como una hoja de cálculo XML o un formato de archivo de texto. En este caso, puede guardar el libro en el formato de hoja de cálculo XML y, después, desde el otro programa, importar el archivo XML.
El formato y las funciones de Excel no se transfieren a otros formatos de archivo
El formato de libro de Microsoft Office Excel 2007 (.xlsx) conserva todos los datos de hoja de cálculo y gráfico, el formato y otras características disponibles en Office Excel 2007, y el formato de libro habilitado para macros (.xlsm), además de todas estas características, conserva las macros y las hojas de macros. Si guarda un libro con otro formato de archivo, como un formato de archivo de texto, es posible que se pierda algo del formato, datos u otras funciones.
FORMATO DE ARCHIVO
DIFERENCIAS EN FUNCIONES Y FORMATOS
Texto con formato (delimitado por espacios)
Este formato (.prn) guarda únicamente el texto y los valores como aparecen en las celdas de la hoja de cálculo activa.
Si una fila de celdas contiene más de 240 caracteres, los caracteres posteriores al 240 se enviarán a una nueva línea al final del archivo convertido. Por ejemplo, si las filas de la 1 a la 10 contiene cada una más de 240 caracteres, el texto restante de la fila 1 se colocará en la fila 11, el texto restante de la fila 2 se colocará en la fila 12 y así sucesivamente.
Las columnas de datos se separan mediante comas y cada fila de datos termina en un retorno de carro. Si las celdas presentan fórmulas en vez de valores de fórmulas, éstas se convertirán como texto. Todo el formato, gráficos, objetos y cualquier otro contenido de la
hoja de cálculo se perderá. El símbolo del euro se convertirá en un signo de interrogación.
NOTA Antes de guardar una hoja de cálculo en este formato, asegúrese de que todos los
datos que desee convertir están visibles y de que hay entre las columnas el espaciado adecuado. En caso contrario, los datos pueden perderse o no separarse correctamente en el texto convertido. Puede que sea necesario adaptar los anchos de columna en la hoja antes de convertirla en formato de texto con formato.
Texto (delimitado por tabulaciones)
Este formato (.txt) guarda únicamente el texto y los valores como aparecen en las celdas de la hoja de cálculo activa.
Las columnas de datos se separan por tabulaciones y cada fila de datos termina en un retorno de carro. Si una celda contiene una coma, el contenido de la celda se escribirá entre comillas dobles. Todo el formato, gráficos, objetos y cualquier otro contenido de la hoja de cálculo se perderá. El símbolo del euro se convertirá en un signo de interrogación.
Si las celdas presentan fórmulas en vez de valores de fórmulas, éstas se guardarán como texto. Para conservar las fórmulas si se vuelve a abrir el archivo en Microsoft Office Excel, seleccione la opción Delimitados en el Asistente para importar texto y seleccione las tabulaciones como delimitadores.
NOTA Si el libro contiene caracteres de fuente especiales, como el símbolo de copyright
(©), y va a utilizar el archivo de texto convertido en un equipo que tenga otro sistema operativo, guarde el libro en el formato de archivo de texto adecuado para ese sistema. Por ejemplo, si utiliza Microsoft Windows y desea utilizar el archivo de texto en un equipo Macintosh, guarde el archivo en formato Texto (Macintosh). Si utiliza un equipo Macintosh y desea utilizar el archivo de texto en un sistema que ejecute Windows o Windows NT, guárdelo en formato Texto (Windows).
Texto (Unicode) Este formato (.txt) guarda todo el texto y los valores tal como aparecen en las celdas de la hoja de cálculo activa.
Sin embargo, si abre un archivo en formato de texto (Unicode) utilizando un programa que no lee Unicode, como el Bloc de notas de Microsoft Windows 95 o un programa basado en MS-DOS de Microsoft, los datos se perderán.
NOTA El Bloc de notas de Microsoft Windows NT lee archivos en formato de texto
(Unicode).
CSV (delimitado por comas)
Este formato (.csv) guarda únicamente el texto y los valores como aparecen en las celdas de la hoja de cálculo activa. Todas las filas y todos los caracteres en cada celda se guardarán. Las columnas de datos se separan mediante comas y cada fila de datos termina en un retorno de carro. Si una celda contiene una coma, el contenido de la celda se escribirá entre comillas dobles.
Si las celdas presentan fórmulas en vez de valores de fórmulas, éstas se convertirán como texto. Todo el formato, gráficos, objetos y cualquier otro contenido de la hoja de cálculo se perderá. El símbolo del euro se convertirá en un signo de interrogación.
NOTA Si el libro contiene caracteres de fuente especiales, como el símbolo de copyright
(©) y va a utilizar el archivo de texto convertido en un equipo que tenga otro sistema operativo, guarde el libro en el formato de archivo de texto adecuado para ese sistema. Por ejemplo, si utiliza Windows y desea utilizar el archivo de texto en un equipo Macintosh, guarde el archivo en formato CSV (Macintosh). Si utiliza un equipo Macintosh y desea utilizar el archivo de texto en un sistema que ejecute Windows o Windows NT, guárdelo en formato CSV (Windows).
Formato de intercambio de datos (DIF)
Este formato (.dif) guarda únicamente el texto, los valores y las fórmulas de la hoja de cálculo activa.
Si las opciones de la hoja de cálculo se establecen para presentar los resultados de la fórmula en las celdas, solamente se guardarán los resultados de la fórmula en el archivo convertido. Para guardar las fórmulas, preséntelas en la hoja de cálculo antes de guardar el archivo.
Cómo mostrar fórmulas en las celdas de la hoja de cálculo
Los anchos de columna y la mayor parte de los formatos se guardarán, pero todos los demás formatos se perderán.
Se pierden las configuraciones de instalación de página y los saltos de página manuales.
Los comentarios de celda, gráficos, gráficos incrustados, objetos, controles de formularios, hipervínculos, valores de validación de datos, formato condicional y otras funciones de la hoja de cálculo se perderán.
Los datos mostrados en la vista activa de un informe de tabla dinámica se guardan; todos los demás datos de la tabla dinámica se pierden.
El código de Visual Basic se perderá.
El símbolo del euro se convertirá en un signo de interrogación.
SYLK (vínculo simbólico)
Este formato (.slk) guarda únicamente los valores y fórmulas de la hoja de cálculo activa y un formato de celda limitado.
Se guardan hasta un máximo de 255 caracteres por celda.
Si una función de Excel no se admite en formato SYLK, Excel la calculará antes de guardar el archivo y reemplazará la fórmula con el valor resultante.
La mayoría del formato de texto se guardará; el texto convertido tomará el formato del primer carácter en la celda. El texto girado, las celdas combinadas y la configuración de la alineación vertical y horizontal se perderán. El color de fuente tal vez se cambie por otro color si se vuelve a abrir en Excel la hoja SYLK convertida. Los bordes se convertirán en bordes de una sola línea. El sombreado de celda se convertirá en un sombreado punteado de color gris.
Se pierden las configuraciones de instalación de página y los saltos de página manuales.
Los comentarios de celda se guardarán. Podrá ver los comentarios si abre de nuevo el archivo SYLK en Excel.
Los gráficos, gráficos incrustados, objetos, controles de formularios, hipervínculos, valores de validación de datos, formato condicional y otras funciones de la hoja de cálculos se perderán.
El código de Visual Basic se perderá.
Los datos mostrados en la vista activa de un informe de tabla dinámica se guardan; todos los demás datos de la tabla dinámica se pierden.
NOTA Puede utilizar este formato para guardar archivos de libro para utilizarlos en
Microsoft Multiplan. Excel no incluye convertidores de formato de archivo para convertir directamente los archivos de libro en el formato de Multiplan.
Página Web y Estos formatos de página Web (.htm, .html) y los formatos de página Web de un solo archivo
página Web de un solo archivo
(.mht, .mhtml) se pueden utilizar para exportar datos de Excel.
Los proyectos de Visual Basic para Aplicaciones no son compatibles con este formato de archivo.
Las nuevas características de Excel 2007 no son compatibles con este formato de archivo.
Las tablas dinámicas y los gráficos se pueden exportar a este formato de archivo, pero se pierden cuando se abre un archivo otra vez en este formato en Excel.
Hoja de cálculo XML 2003
Este formato de archivo de hoja de cálculo XML 2003 (.xml) no conserva las características siguientes:
Flechas de rastreo de auditoría
Gráficos y otros objetos gráficos
Hojas de gráficos, hojas de macros, hojas de diálogos
Vistas personalizadas
Referencias de consolidación de datos
Capas de objetos de dibujo
Características de esquemas y de grupos
Datos de hojas de cálculo protegidos mediante contraseña.
Escenarios
Categorías de funciones definidas por el usuario
Proyectos de Visual Basic para Aplicaciones
Las nuevas características de Excel 2007, como el formato condicional mejorado, no son compatibles con este formato de archivo. Sin embargo, este formato de archivo es compatible con los nuevos límites de fila y columna de Excel 2007.
Importar un archivo de texto abriéndolo en Excel
Un archivo de texto creado con otro programa se puede abrir en un libro de Excel con el comando Abrir. Abrir un archivo de texto en Excel no cambia el formato del archivo: puede verlo en la barra de título de Excel, donde el nombre del archivo conserva la extensión del nombre del archivo de texto (por ejemplo, .txt or .csv).
Haga clic en el botón de Microsoft Office y, a continuación, haga clic en Abrir.
Aparece el cuadro de diálogo Abrir.
En los equipos que ejecuten Windows Vista
En la lista, seleccione Archivos de texto.
En los equipos que ejecuten Microsoft Windows XP
En la lista Tipo de archivo, seleccione Archivos de texto.
En los equipos que ejecuten Windows Vista
En la barra de direcciones, busque y haga doble clic en el archivo de texto que desea abrir:
En los equipos que ejecuten Microsoft Windows XP
En la lista Buscar en, busque y haga doble clic en el archivo de texto que desea abrir.
Si el archivo es un archivo de texto (.txt), Excel inicia el Asistente para importar texto.
Siga las instrucciones del Asistente para importar texto. Haga clic en Ayuda para obtener más información sobre el uso del Asistente para importar texto o vea Asistente para importar texto. Cuando termine de realizar los pasos del Asistente, haga clic en Finalizar para completar la operación de importar.
Si el archivo es un archivo .csv, Excel abre automáticamente el archivo de texto y muestra los datos en un libro nuevo.
Cuando Excel abre un archivo .csv, usa la configuración de formato de datos predeterminada para interpretar el modo en que debe importar cada columna de datos. Puede usar el Asistente para importar texto si desea disponer de mayor flexibilidad para convertir las columnas a los distintos formatos de datos. Por ejemplo, es posible que el formato de una columna de datos del archivo .csv sea MDA, pero el formato de datos predeterminado de Excel sea AMD, o puede que desee convertir en texto una columna de números precedida de ceros para conservar dichos ceros. Para ejecutar el Asistente para importar texto, puede cambiar la extensión .csv del nombre del archivo a .txt antes de abrirlo, o bien, puede importar un archivo de texto conectándose a él.
Importar o exportar archivos de texto
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.
Puede importar o exportar hasta 1.048.576 filas y 16.384 columnas.
Tanto si va a importar o exportar datos, es posible que desee obtener más información sobre los convertidores de texto de Excel. Para obtener más información, vea la sección sobre formatos de texto en el tema de Ayuda sobre formatos de archivo compatibles en Excel y la sección sobre formatos de archivos de texto en el tema de Ayuda sobre el formato y las funciones de Excel que no se transfieren a otros formatos de archivo.
Importar un archivo de texto conectándose a él
Puede importar datos de un archivo de texto en una hoja de cálculo existente como un rango de datos externos.
Haga clic en la celda en la que desea poner los datos del archivo de texto.
En la ficha Datos, en el grupo Obtener datos externos, haga clic en Desde texto.
En los equipos que ejecuten Windows Vista
En la barra de direcciones, busque y haga clic en el archivo de texto que desea importar.
En los equipos que ejecuten Microsoft Windows XP
En la lista Buscar en, busque y haga doble clic en el archivo de texto que desea importar.
Siga las instrucciones del Asistente para importar texto. Haga clic en Ayuda para obtener más información sobre el uso del Asistente para importar texto o vea Asistente para importar texto. Cuando termine de realizar los pasos del Asistente, haga clic en Finalizar para completar la operación de importar.
En el cuadro de diálogo Importar datos, haga lo siguiente:
Si lo desea, haga clic en Propiedades para definir las opciones de actualización, formato y diseño de los datos importados.
En ¿Dónde desea situar los datos?, siga uno de los procedimientos siguientes:
Para devolver los datos a la ubicación seleccionada, haga clic en Hoja de cálculo existente.
Para devolver los datos a la esquina superior izquierda de una nueva hoja de cálculo, haga clic en Hoja de cálculo nueva.
Haga clic en Aceptar.
Excel coloca el rango de datos externos en la ubicación especificada.
Si Excel no convierte una columna de datos al formato que desea, puede convertir los datos después de importarlos. Para obtener más información, vea los temas de Ayuda siguientes:
Convertir números almacenados como texto a números
Convertir fechas almacenadas como texto en fechas
Función TEXTO
Función VALOR
Exportar datos a un archivo de texto guardándolo
Puede convertir una hoja de cálculo de Excel en un archivo de texto con el comando Guardar como.
Haga clic en el botón de Microsoft Office y, a continuación, haga clic en Guardar como.
Aparecerá el cuadro de diálogo Guardar como.
En el cuadro Guardar como tipo, elija el formato de archivo de texto para la hoja de cálculo.
Por ejemplo, haga clic en Texto (delimitado por tabulaciones) o CSV (delimitado por comas).
Cada formato admite grupos de características diferentes. Para obtener más información sobre los conjuntos de características admitidos por los diferentes formatos de archivo de texto, vea El formato y las funciones de Excel no se transfieren a otros formatos de archivo.
En los equipos que ejecuten Windows Vista
En la barra de direcciones, busque la ubicación en la que desea guardar el nuevo archivo de texto y, a continuación, haga clic en Guardar.
En los equipos que ejecuten Microsoft Windows XP
En el cuadro Guardar en, busque la ubicación en la que desea guardar el nuevo archivo de texto y, a continuación, haga clic en Guardar.
Aparecerá un cuadro de diálogo en el que se informa de que sólo la hoja de cálculo actual se va a guardar en el nuevo archivo. Si está seguro de que la hoja de cálculo actual es la que desea guardar como archivo de texto, haga clic en Aceptar. Puede guardar otras hojas de cálculo como archivos de texto distintos repitiendo este procedimiento para cada hoja.
Aparecerá un segundo cuadro de diálogo en el que se indica que la hoja de cálculo puede contener características no compatibles con los formatos de archivo de texto. Si sólo está interesado en guardar los datos de la hoja de cálculo en el nuevo archivo de texto, haga clic en Sí. Si no está seguro y desea obtener más información sobre las características de Excel incompatibles con los formatos de archivo de texto, haga clic enAyuda para obtener información.
Para obtener más información sobre cómo guardar archivos en otros formatos, vea Guardar un libro con otro formato de archivo.
Cambiar el delimitador que se utiliza en un archivo de texto
Si usa el Asistente para importar texto para importar un archivo de texto, puede cambiar el delimitador usado por un archivo de texto delimitado de un carácter de tabulación a cualquier otro carácter en el paso 2 del Asistente para importar texto. En este paso, puede cambiar también el modo en que se tratan los delimitadores consecutivos, como las comillas consecutivas.
Para obtener más información sobre el uso del Asistente para importar texto, vea la sección sobre cómo importar un archivo de texto abriéndolo o Asistente para importar texto.
Cambiar el separador de todos los archivos de texto .csv
En Microsoft Windows, haga clic en el botón Inicio y, a continuación, en Panel de control.
Abra el cuadro de diálogo Configuración regional y de idioma.
Realice una de las siguientes acciones:
En Windows Vista, haga clic en la ficha Formatos y, a continuación, haga clic en Personalizar este formato.
En Windows XP, haga clic en la ficha Configuración regional y, a continuación, haga clic en Personalizar.
Escriba un nuevo separador en el cuadro Separador de listas.
Haga clic dos veces en Aceptar.
Una vez cambiado el carácter separador de listas del equipo, todos los programas usarán el nuevo carácter como un separador de lista. Puede volver a cambiar al carácter predeterminado mediante el mismo procedimiento.
Importar un archivo de texto conectándose a él
Puede importar datos de un archivo de texto en una hoja de cálculo existente como un rango de datos externos.
Haga clic en la celda en la que desea poner los datos del archivo de texto.
En la ficha Datos, en el grupo Obtener datos externos, haga clic en Desde texto.
En los equipos que ejecuten Windows Vista
En la barra de direcciones, busque y haga clic en el archivo de texto que desea importar.
En los equipos que ejecuten Microsoft Windows XP
En la lista Buscar en, busque y haga doble clic en el archivo de texto que desea importar.
Siga las instrucciones del Asistente para importar texto. Haga clic en Ayuda para obtener más información sobre el uso del Asistente para importar texto o vea Asistente para importar texto. Cuando termine de realizar los pasos del Asistente, haga clic en Finalizar para completar la operación de importar.
En el cuadro de diálogo Importar datos, haga lo siguiente:
Si lo desea, haga clic en Propiedades para definir las opciones de actualización, formato y diseño de los datos importados.
En ¿Dónde desea situar los datos?, siga uno de los procedimientos siguientes:
Para devolver los datos a la ubicación seleccionada, haga clic en Hoja de cálculo existente.
Para devolver los datos a la esquina superior izquierda de una nueva hoja de cálculo, haga clic en Hoja de cálculo nueva.
Haga clic en Aceptar.
Excel coloca el rango de datos externos en la ubicación especificada.
Si Excel no convierte una columna de datos al formato que desea, puede convertir los datos después de importarlos. Para obtener más información, vea los temas de Ayuda siguientes:
Convertir números almacenados como texto a números
Convertir fechas almacenadas como texto en fechas
Función TEXTO
Función VALOR
Exportar datos a un archivo de texto guardándolo
Puede convertir una hoja de cálculo de Excel en un archivo de texto con el comando Guardar como.
Haga clic en el botón de Microsoft Office y, a continuación, haga clic en Guardar como.
Aparecerá el cuadro de diálogo Guardar como.
En el cuadro Guardar como tipo, elija el formato de archivo de texto para la hoja de cálculo.
Por ejemplo, haga clic en Texto (delimitado por tabulaciones) o CSV (delimitado por comas).
NOTA Cada formato admite grupos de características diferentes. Para obtener más información
sobre los conjuntos de características admitidos por los diferentes formatos de archivo de texto, vea El formato y las funciones de Excel no se transfieren a otros formatos de archivo.
En los equipos que ejecuten Windows Vista
En la barra de direcciones, busque la ubicación en la que desea guardar el nuevo archivo de texto y, a continuación, haga clic en Guardar.
En los equipos que ejecuten Microsoft Windows XP
En el cuadro Guardar en, busque la ubicación en la que desea guardar el nuevo archivo de texto y, a continuación, haga clic en Guardar.
Aparecerá un cuadro de diálogo en el que se informa de que sólo la hoja de cálculo actual se va a guardar en el nuevo archivo. Si está seguro de que la hoja de cálculo actual es la que desea guardar como archivo de texto, haga clic en Aceptar. Puede guardar otras hojas de cálculo como archivos de texto distintos repitiendo este procedimiento para cada hoja.
Aparecerá un segundo cuadro de diálogo en el que se indica que la hoja de cálculo puede contener características no compatibles con los formatos de archivo de texto. Si sólo está interesado en guardar los datos de la hoja de cálculo en el nuevo archivo de texto, haga clic en Sí. Si no está seguro y desea obtener más información sobre las características de Excel incompatibles con los formatos de archivo de texto, haga clic enAyuda para obtener información.
Para obtener más información sobre cómo guardar archivos en otros formatos, vea Guardar un libro con otro formato de archivo.
Cambiar el delimitador que se utiliza en un archivo de texto
Si usa el Asistente para importar texto para importar un archivo de texto, puede cambiar el delimitador usado por un archivo de texto delimitado de un carácter de tabulación a cualquier otro carácter en el paso 2 del Asistente para importar texto. En este paso, puede cambiar también el modo en que se tratan los delimitadores consecutivos, como las comillas consecutivas.
Para obtener más información sobre el uso del Asistente para importar texto, vea la sección sobre cómo importar un archivo de texto abriéndolo o Asistente para importar texto.
1. Cambiar el separador de todos los archivos de texto .csv 2. En Microsoft Windows, haga clic en el botón Inicio y, a continuación, en Panel de control. 3. Abra el cuadro de diálogo Configuración regional y de idioma. 4. Realice una de las siguientes acciones: 5. En Windows Vista, haga clic en la ficha Formatos y, a continuación, haga clic
en Personalizar este formato. 6. En Windows XP, haga clic en la ficha Configuración regional y, a continuación, haga clic
en Personalizar. 7. Escriba un nuevo separador en el cuadro Separador de listas. 8. Haga clic dos veces en Aceptar.
Una vez cambiado el carácter separador de listas del equipo, todos los programas usarán el nuevo carácter como un separador de lista. Puede volver a cambiar al carácter predeterminado mediante el mismo procedimiento.
BIBLIOGRAFIA
http://www.uv.mx/personal/llopez/files/2013/03/Manual-Microsoft-Office-Excel-2010.pdf
https://www.google.com.mx/search?q=uso+de+las+funciones+de+excel+para+la+importacion+de+
datos&oq=uso+de+las+funciones+de+excel+para+la+importacion+de+datos&aqs=chrome..69i57.2
4855j0j7&sourceid=chrome&espv=210&es_sm=93&ie=UTF-
8#q=uso+de+las+funciones+de+excel+para+la+importacion+de+datos+tutorial
http://office.microsoft.com/es-es/excel-help/intercambiar-copiar-importar-exportar-datos-entre-excel-
y-access-HA010096300.aspx.
http://hojasdecalculo.about.com/od/Excel_avanzado/a/Importar-A-Excel-Datos-De-Otros-
Programas.htm
http://office.microsoft.com/es-mx/access-help/exportar-datos-a-excel-HA001224122.aspx
http://exceltotal.com/exportar-datos-de-excel-a-un-archivo-de-texto/
http://www.vb-mundo.com/exportar-datos-de-un-programa-de-visualbasic-net-a-excel/
http://support.microsoft.com/kb/305795/es
http://technet.microsoft.com/es-es/library/cc952922(v=sql.100).aspx
http://jldexcelsp.blogspot.mx/2007/08/exportar-datos-de-excel-archivo-texto.html
http://hojasdecalculo.about.com/od/Excel_avanzado/a/Excel-En-La-Empresa-Usos-Parte-I.htm