Nota: Microsoft Access no admite la importación de datos de Excel con una etiqueta de confidencialidad aplicada. Como solución alternativa, puede quitar la etiqueta antes de importarla y, después, volver a aplicarla después de importarla. Para obtener más información, vea Aplicar etiquetas de confidencialidad a los archivos y al correo electrónico en Office.
En este artículo se muestra cómo mover los datos de Excel a Access y convertir los datos en tablas relacionales para que pueda usar Microsoft Excel y Access juntos. En resumen, Access es ideal para capturar, almacenar, consultar y compartir datos, y Excel es la mejor opción para calcular, analizar y visualizar datos.
En dos artículos, Usar Access o Excel para administrar los datos y 10 motivos principales para usar Access con Excel, explican qué programa es más adecuado para una tarea concreta y cómo usar Excel y Access juntos para crear una solución práctica.
Al mover datos de Excel a Access, hay tres pasos básicos para el proceso.
Nota: Para obtener información sobre el modelado de datos y las relaciones en Access, vea Conceptos básicos del diseño de una base de datos.
Paso 1: Importar datos de Excel a Access
Importar datos es una operación que puede resultar mucho más sencilla si se tarda algún tiempo en preparar y limpiar los datos. Importar datos es como mover a un nuevo hogar. Si usted limpia y organiza sus posesiones antes de mudarse, sentarse en su nuevo hogar es mucho más fácil.
Limpiar los datos antes de importar
Antes de importar datos a Access, en Excel es una buena idea:
-
Convierta las celdas que contienen datos no atómicos (es decir, varios valores en una celda) en varias columnas. Por ejemplo, una celda de una columna "Aptitudes" que contiene varios valores de aptitudes, como "Programación de C#", "Programación de VBA" y "Diseño web" debe dividirse para separar columnas que contengan solo un valor de aptitud.
-
Use el comando ESPACIOS para quitar espacios incrustados a la izquierda, finales y múltiples.
-
Quitar caracteres no imprimibles.
-
Busque y corrija errores ortográficos y de puntuación.
-
Quitar filas duplicadas o campos duplicados.
-
Asegúrese de que las columnas de datos no contienen formatos mixtos, especialmente números con formato de texto o fechas con formato de números.
Para obtener más información, vea los siguientes temas de ayuda de Excel:
Nota: Si sus necesidades de limpieza de datos son complejas o no tiene tiempo o recursos para automatizar el proceso por su cuenta, puede usar un proveedor de terceros. Para obtener más información, busca "software de limpieza de datos" o "calidad de datos" por tu motor de búsqueda favorito en el navegador web.
Elegir el mejor tipo de datos al importar
Durante la operación de importación en Access, desea tomar buenas decisiones para recibir pocos errores de conversión (si los hay) que requerirán intervención manual. La tabla siguiente resume cómo se convierten los formatos de número de Excel y los tipos de datos de Access al importar datos de Excel a Access y ofrece algunas sugerencias sobre los mejores tipos de datos para elegir en el Asistente para importación de hojas de cálculo.
Formato de número de Excel |
Tipo de datos de Access |
Comentarios |
Procedimiento recomendado |
---|---|---|---|
Texto |
Texto, Memo |
El tipo de datos Texto de Access almacena datos alfanuméricos de hasta 255 caracteres. El tipo de datos Memo de Access almacena datos alfanuméricos de hasta 65.535 caracteres. |
Elija Memo para evitar truncar los datos. |
Número, Porcentaje, Fracción, Científica |
Número |
Access tiene un tipo de datos Número que varía según una propiedad Tamaño del campo (Byte, Integer, Long Integer, Single, Double, Decimal). |
Elija Doble para evitar errores de conversión de datos. |
Fecha |
Fecha |
Access y Excel usan el mismo número de fecha de serie para almacenar fechas. En Access, el intervalo de fechas es mayor: de -657.434 (1 de enero de 100 d.C.) a 2.958.465 (31 de diciembre de 9999 d.C.). Como Access no reconoce el sistema de fechas de 1904 (usado en Excel para Macintosh), necesita convertir las fechas en Excel o Access para evitar confusiones. Para obtener más información, vea Cambiar el sistema de fechas, el formato o la interpretación de los años de dos dígitos e Importar o vincular a datos en un libro de Excel. |
Elija Fecha. |
Hora |
Hora |
Access y Excel almacenan los valores de tiempo con el mismo tipo de datos. |
Elija Hora, que suele ser la predeterminada. |
Moneda, Contabilidad |
Código |
En Access, el tipo de datos Moneda almacena los datos como números de 8 bytes con precisión a cuatro posiciones decimales y se usa para almacenar datos financieros y evitar el redondeo de valores. |
Elija Moneda, que suele ser el valor predeterminado. |
Boolean |
Sí/No |
Access usa -1 para todos los valores Sí y 0 para todos los valores No, mientras que Excel usa 1 para todos los valores VERDADERO y 0 para todos los valores FALSO. |
Elija Sí/No, que convierte automáticamente los valores subyacentes. |
Hipervínculo |
Hipervínculo |
Un hipervínculo de Excel y Access contiene una dirección URL o web en la que puede hacer clic y seguir. |
Elija Hipervínculo; de lo contrario, Access puede usar el tipo de datos Texto de forma predeterminada. |
Una vez que los datos están en Access, puede eliminar los datos de Excel. No olvide hacer una copia de seguridad del libro de Excel original antes de eliminarlo.
Para obtener más información, vea el tema de ayuda de Access Importar o vincular a datos en un libro de Excel.
Anexar datos automáticamente de la forma más fácil
Un problema común que tienen los usuarios de Excel es anexar datos con las mismas columnas en una hoja de cálculo grande. Por ejemplo, es posible que tenga una solución de seguimiento de activos que se inició en Excel pero que ahora ha crecido hasta incluir archivos de muchos grupos de trabajo y departamentos. Estos datos pueden estar en hojas de cálculo y libros diferentes, o en archivos de texto que son fuentes de datos de otros sistemas. No hay ningún comando de interfaz de usuario o una forma sencilla de anexar datos similares en Excel.
La mejor solución es usar Access, donde puede importar y anexar fácilmente datos en una tabla mediante el Asistente para importación de hojas de cálculo. Además, puede anexar una gran cantidad de datos en una tabla. Puede guardar las operaciones de importación, agregarlas como tareas programadas de Microsoft Outlook e incluso usar macros para automatizar el proceso.
Paso 2: Normalizar datos mediante el Asistente para analizar tablas
A primera vista, pasar por el proceso de normalización de los datos puede parecer una tarea desalentadora. Afortunadamente, normalizar las tablas en Access es un proceso mucho más sencillo, gracias al Asistente para analizar tablas.
1. Arrastre las columnas seleccionadas a una nueva tabla y cree automáticamente relaciones
2. Usar comandos de botón para cambiar el nombre de una tabla, agregar una clave principal, convertir una columna existente en clave principal y deshacer la última acción
Puede usar este asistente para hacer lo siguiente:
-
Convierta una tabla en un conjunto de tablas más pequeñas y cree automáticamente una relación de clave principal y externa entre las tablas.
-
Agregue una clave principal a un campo existente que contenga valores únicos o cree un nuevo campo id. que use el tipo de datos Autonumeración.
-
Cree automáticamente relaciones para exigir la integridad referencial con actualizaciones en cascada. Las eliminaciones en cascada no se agregan automáticamente para evitar la eliminación accidental de datos, pero puede agregar fácilmente eliminaciones en cascada más adelante.
-
Busque datos redundantes o duplicados en las tablas nuevas (como el mismo cliente con dos números de teléfono diferentes) y actualícelos como desee.
-
Haga una copia de seguridad de la tabla original y cámbiele el nombre anexando "_OLD" a su nombre. Después, cree una consulta que reconstruye la tabla original, con el nombre de la tabla original, de modo que los formularios o informes existentes basados en la tabla original funcionen con la nueva estructura de tabla.
Para obtener más información, vea Normalizar los datos con el Analizador de tablas.
Paso 3: Conectarse a datos de Access desde Excel
Una vez que los datos se han normalizado en Access y se ha creado una consulta o tabla que reconstruye los datos originales, es una cuestión sencilla de conectarse a los datos de Access desde Excel. Los datos ahora están en Access como un origen de datos externo, por lo que pueden conectarse al libro a través de una conexión de datos, que es un contenedor de información que se usa para buscar, iniciar sesión en el origen de datos externo y obtener acceso a él. 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 (extensión de nombre de archivo .odc) o un archivo de nombre de origen de datos (extensión .dsn). Después de conectarse a datos externos, también puede actualizar automáticamente el libro de Excel desde Access siempre que los datos se actualicen en Access.
Para obtener más información, vea Importar datos de orígenes de datos externos (Power Query).
Obtener los datos en Access
Esta sección le guiará por las siguientes fases de normalización de los datos: Dividir los valores de las columnas Vendedor y Dirección en sus partes más atómicas, separar asuntos relacionados en sus propias tablas, copiar y pegar esas tablas de Excel en Access, crear relaciones clave entre las tablas de Access recién creadas y crear y ejecutar una consulta sencilla en Access para devolver información.
Datos de ejemplo en forma no normalizada
La siguiente hoja de cálculo contiene valores no atómicos en la columna Vendedor y en la columna Dirección. Ambas columnas deben dividirse en dos o más columnas independientes. Esta hoja de cálculo también contiene información sobre vendedores, productos, clientes y pedidos. Esta información también debe dividirse, por asunto, en tablas independientes.
Vendedor |
Id. de pedido |
Fecha del pedido |
Id. de producto |
Qty |
Precio |
Nombre del cliente |
Dirección |
Teléfono |
---|---|---|---|---|---|---|---|---|
Li, Yale |
2349 |
3/4/09 |
C-789 |
3 |
7,00 $ |
Fourth Coffee |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Li, Yale |
2349 |
3/4/09 |
C-795 |
6 |
9,75 $ |
Fourth Coffee |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Adams, Ellen |
2350 |
3/4/09 |
A-2275 |
2 |
$16.75 |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Adams, Ellen |
2350 |
3/4/09 |
F-198 |
6 |
$5.25 |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Adams, Ellen |
2350 |
3/4/09 |
B-205 |
1 |
$4.50 |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Hance, Jim |
2351 |
3/4/09 |
C-795 |
6 |
9,75 $ |
Contoso, Ltd. |
2302 Harvard Ave Bellevue, WA 98227 |
425-555-0222 |
Hance, Jim |
2352 |
3/5/09 |
A-2275 |
2 |
$16.75 |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Hance, Jim |
2352 |
3/5/09 |
D-4420 |
3 |
$7.25 |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Koch, Reed |
2353 |
3/7/09 |
A-2275 |
6 |
$16.75 |
Fourth Coffee |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Koch, Reed |
2353 |
3/7/09 |
C-789 |
5 |
7,00 $ |
Fourth Coffee |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Información en sus partes más pequeñas: datos atómicos
Al trabajar con los datos de este ejemplo, puede usar el comando Texto en columna de Excel para separar las partes "atómicas" de una celda (como dirección postal, ciudad, estado y código postal) en columnas discretas.
En la tabla siguiente se muestran las nuevas columnas en la misma hoja de cálculo después de que se hayan dividido para hacer que todos los valores sean atómicos. Tenga en cuenta que la información de la columna Vendedor se ha dividido en las columnas Apellidos y Nombre y que la información de la columna Dirección se ha dividido en las columnas Calle, Ciudad, Estado y Código postal. Estos datos están en "primer formulario normal".
Apellido |
Nombre |
|
Calle |
Ciudad |
Estado: |
Código postal |
---|---|---|---|---|---|---|
Li |
Eale |
2302 Harvard Ave |
Santoña |
WA |
98227 |
|
Adams |
Ellen |
1025 Columbia Circle |
Kirkland |
WA |
98234 |
|
Hance |
Javier |
2302 Harvard Ave |
Santoña |
WA |
98227 |
|
Koch |
Junco |
7007 Cornell St Redmond |
Redmond |
WA |
98199 |
Dividir datos en temas organizados en Excel
Las varias tablas de datos de ejemplo siguientes muestran la misma información de la hoja de cálculo de Excel después de que se haya dividido en tablas para vendedores, productos, clientes y pedidos. El diseño de la tabla no es final, pero va por el buen camino.
La tabla Vendedores contiene solo información sobre el personal de ventas. Tenga en cuenta que cada registro tiene un id. único (Id. del vendedor). El valor Id. de vendedor se usará en la tabla Pedidos para conectar pedidos con vendedores.
Vendedores |
||
---|---|---|
Id. de vendedor |
Apellido |
Nombre |
101 |
Li |
Eale |
103 |
Adams |
Ellen |
105 |
Hance |
Javier |
107 |
Koch |
Junco |
La tabla Productos contiene solo información sobre los productos. Tenga en cuenta que cada registro tiene un id. único (id. de producto). El valor de Id. de producto se usará para conectar la información del producto a la tabla Detalles de pedido.
Productos |
|
---|---|
Id. de producto |
Precio |
A-2275 |
16.75 |
B-205 |
4.50 |
C-789 |
7.00 |
C-795 |
9.75 |
D-4420 |
7.25 |
F-198 |
5.25 |
La tabla Clientes solo contiene información sobre los clientes. Tenga en cuenta que cada registro tiene un id. único (id. de cliente). El valor de Id. de cliente se usará para conectar la información del cliente a la tabla Pedidos.
Clientes |
||||||
---|---|---|---|---|---|---|
Id. de cliente |
Nombre |
Calle |
Ciudad |
Estado: |
Código postal |
Teléfono |
1001 |
Contoso, Ltd. |
2302 Harvard Ave |
Santoña |
WA |
98227 |
425-555-0222 |
1003 |
Adventure Works |
1025 Columbia Circle |
Kirkland |
WA |
98234 |
425-555-0185 |
1005 |
Fourth Coffee |
7007 Cornell St |
Redmond |
WA |
98199 |
425-555-0201 |
La tabla Pedidos contiene información sobre pedidos, vendedores, clientes y productos. Tenga en cuenta que cada registro tiene un id. único (Id. de pedido). Parte de la información de esta tabla debe dividirse en una tabla adicional que contenga los detalles del pedido para que la tabla Pedidos contenga solo cuatro columnas: el id. de pedido único, la fecha del pedido, el id. del vendedor y el id. de cliente. La tabla que se muestra aquí aún no se ha dividido en la tabla Detalles de pedidos.
Pedidos |
|||||
---|---|---|---|---|---|
Id. de pedido |
Fecha del pedido |
Id. de vendedor |
Id. de cliente |
Id. de producto |
Qty |
2349 |
3/4/09 |
101 |
1005 |
C-789 |
3 |
2349 |
3/4/09 |
101 |
1005 |
C-795 |
6 |
2350 |
3/4/09 |
103 |
1003 |
A-2275 |
2 |
2350 |
3/4/09 |
103 |
1003 |
F-198 |
6 |
2350 |
3/4/09 |
103 |
1003 |
B-205 |
1 |
2351 |
3/4/09 |
105 |
1001 |
C-795 |
6 |
2352 |
3/5/09 |
105 |
1003 |
A-2275 |
2 |
2352 |
3/5/09 |
105 |
1003 |
D-4420 |
3 |
2353 |
3/7/09 |
107 |
1005 |
A-2275 |
6 |
2353 |
3/7/09 |
107 |
1005 |
C-789 |
5 |
Los detalles del pedido, como el id. de producto y la cantidad, se mueven de la tabla Pedidos y se almacenan en una tabla denominada Detalles de pedidos. Tenga en cuenta que hay 9 pedidos, por lo que tiene sentido que haya 9 registros en esta tabla. Tenga en cuenta que la tabla Pedidos tiene un id. único (Id. de pedido), al que se hará referencia desde la tabla Detalles de pedidos.
El diseño final de la tabla Pedidos debería ser similar al siguiente:
Pedidos |
|||
---|---|---|---|
Id. de pedido |
Fecha del pedido |
Id. de vendedor |
Id. de cliente |
2349 |
3/4/09 |
101 |
1005 |
2350 |
3/4/09 |
103 |
1003 |
2351 |
3/4/09 |
105 |
1001 |
2352 |
3/5/09 |
105 |
1003 |
2353 |
3/7/09 |
107 |
1005 |
La tabla Detalles del pedido no contiene ninguna columna que requiera valores únicos (es decir, no hay clave principal), por lo que es correcto que alguna o todas las columnas contengan datos "redundantes". Sin embargo, no hay dos registros en esta tabla que sean completamente idénticos (esta regla se aplica a cualquier tabla de una base de datos). En esta tabla, debe haber 17 registros, cada uno correspondiente a un producto en un pedido individual. Por ejemplo, en el orden 2349, tres productos C-789 comprenden una de las dos partes de todo el pedido.
Por lo tanto, la tabla Detalles de pedidos debe tener un aspecto similar al siguiente:
Detalles del pedido |
||
---|---|---|
Id. de pedido |
Id. de producto |
Qty |
2349 |
C-789 |
3 |
2349 |
C-795 |
6 |
2350 |
A-2275 |
2 |
2350 |
F-198 |
6 |
2350 |
B-205 |
1 |
2351 |
C-795 |
6 |
2352 |
A-2275 |
2 |
2352 |
D-4420 |
3 |
2353 |
A-2275 |
6 |
2353 |
C-789 |
5 |
Copiar y pegar datos de Excel en Access
Ahora que la información sobre vendedores, clientes, productos, pedidos y detalles del pedido se ha dividido en temas independientes en Excel, puede copiar esos datos directamente en Access, donde se convertirán en tablas.
Crear relaciones entre las tablas de Access y ejecutar una consulta
Después de mover los datos a Access, puede crear relaciones entre tablas y, a continuación, crear consultas para devolver información sobre varios temas. Por ejemplo, puede crear una consulta que devuelva el Id. de pedido y los nombres de los vendedores de los pedidos introducidos entre el 05/03/09 y el 08/03/09.
Además, puede crear formularios e informes para facilitar la entrada de datos y el análisis de ventas.
¿Necesita más ayuda?
Siempre puede preguntar a un experto en Excel Tech Community u obtener soporte técnico en Comunidades.