Mover dados do Excel para o Access

Este artigo mostra como mover seus dados do Excel para acessar e converter seus dados em tabelas relacionais para que você possa usar o Microsoft Excel e acessar juntos. Para resumir, o acesso é melhor para captura, armazenamento, consulta e compartilhamento de dados, e o Excel é melhor para calcular, analisar e Visualizar dados.

Dois artigos, usando o Access ou o Excel para gerenciar seus dados e os10 principais motivos para usar o Access com o Excel, discuta qual programa é mais adequado para uma tarefa específica e como usar o Excel e acessar juntos para criar uma solução prática.

Quando você move dados do Excel para o Access, há três etapas básicas para o processo.

três etapas básicas

Observação: Para obter informações sobre a modelagem de dados e relações no Access, consulte noções básicas de design de bancode dados.

Etapa 1: importar dados do Excel para o Access

Importar dados é uma operação que pode ficar muito mais tranqüila se você levar algum tempo para preparar e limpar seus dados. Importar dados é como migrar para uma nova casa. Se você limpar e organizar seus bens antes de se mover, é muito mais fácil se basear na sua nova casa.

Limpar os dados antes de importar

Antes de importar dados para o Access, no Excel, é uma boa ideia:

  • Converta células que contenham dados não atômicos (ou seja, vários valores em uma célula) para várias colunas. Por exemplo, uma célula em uma coluna "qualificações" que contém vários valores de habilidades, como "programação em C#", "programação VBA" e "design da Web", deve ser dividida para separar colunas que contêm apenas um valor de habilidade.

  • Use o comando APARAr para remover espaços inseridos à esquerda, à direita e vários.

  • Remover caracteres não imprimíveis.

  • Localizar e corrigir erros de ortografia e pontuação.

  • Remova linhas duplicadas ou campos duplicados.

  • Certifique-se de que as colunas de dados não contenham formatos mistos, especialmente números formatados como texto ou datas formatados como números.

Para obter mais informações, consulte os seguintes tópicos da ajuda do Excel:

Observação: Se as suas necessidades de limpeza de dados forem complexas ou se você não tiver tempo ou recursos para automatizar o processo por conta própria, considere o uso de um fornecedor de terceiros. Para obter mais informações, procure por "software de limpeza de dados" ou "qualidade de dados" pelo seu mecanismo de pesquisa favorito em seu navegador da Web.

Escolher o melhor tipo de dados ao importar

Durante a operação de importação no Access, você deseja fazer opções boas para receber alguns erros de conversão (se houver) que exijam intervenção manual. A tabela a seguir resume como os formatos de número do Excel e os tipos de dados do Access são convertidos quando você importa dados do Excel para o Access e oferece algumas dicas sobre os melhores tipos de dados a serem escolhidos no assistente de importação de planilha.

Formato de número do Excel

Tipo de dados do Access

Comentários

Práticas recomendadas

Texto

Texto, memorando

O tipo de dados texto do Access armazena dados alfanuméricos de até 255 caracteres. O tipo de dados memorando do Access armazena dados alfanuméricos de até 65.535 caracteres.

Escolha memorando para evitar truncar os dados.

Número, porcentagem, fração, científico

Núm

O Access tem um tipo de dados de número que varia de acordo com uma propriedade de tamanho de campo (Byte, inteiro, inteiro longo, único, duplo, Decimal).

Escolha duplo para evitar erros de conversão de dados.

Data

Data

O Access e o Excel usam o mesmo número de data em série para armazenar datas. No Access, o intervalo de datas é maior: de-657.434 (1 de janeiro de 100 D.C.) a 2.958.465 (31 de dezembro de 9999 D.C.).

Como o Access não reconhece o sistema de data do 1904 (usado no Excel para Macintosh), você precisa converter as datas no Excel ou no Access para evitar confusão.

Para obter mais informações, consulte alterar o sistema de data, o formato ou a interpretação de ano de dois dígitos e importar ou vincular a dados em uma pasta de trabalho do Excel.

Escolha Data.

Hora

Hora

O Access e o Excel armazenam valores de tempo usando o mesmo tipo de dados.

Escolha tempo, que geralmente é o padrão.

Moeda, contabilidade

Moeda

No Access, o tipo de dados de moeda armazena dados como números de 8 bytes com precisão para quatro casas decimais e é usado para armazenar dados financeiros e evitar o arredondamento de valores.

Escolha moeda, que geralmente é o padrão.

Booliano

Sim/Não

O Access usa-1 para todos os valores Sim e 0 para todos os valores nenhum, enquanto o Excel usa 1 para todos os valores TRUE e 0 para todos os valores falso.

Escolha Sim/não, que converte automaticamente os valores subjacentes.

Hiperlink

Hiperlink

Um hiperlink no Excel e no Access contém uma URL ou endereço Web no qual você pode clicar e seguir.

Escolha hiperlink; caso contrário, o Access poderá usar o tipo de dados texto por padrão.

Depois que os dados estiverem no Access, você poderá excluir os dados do Excel. Não se esqueça de fazer backup da pasta de trabalho original do Excel primeiro antes de excluí-la.

Para obter mais informações, consulte o tópico da ajuda do Access para importar ou vincular dados em uma pasta de trabalho do Excel.

Acrescentar dados automaticamente da maneira mais fácil

Um problema comum que os usuários do Excel têm está acrescentando dados com as mesmas colunas em uma planilha grande. Por exemplo, você pode ter uma solução de rastreamento de ativos iniciada no Excel, mas agora cresceu para incluir arquivos de vários grupos e grupos de programas. Esses dados podem estar em diferentes planilhas e pastas de trabalho ou em arquivos de texto que são feeds de dados de outros sistemas. Não há um comando de interface do usuário ou uma maneira fácil de acrescentar dados semelhantes no Excel.

A melhor solução é usar o Access, onde você pode importar e acrescentar dados facilmente em uma tabela usando o assistente de importação de planilha. Além disso, você pode acrescentar muitos dados em uma tabela. Você pode salvar as operações de importação, adicioná-las como tarefas programadas do Microsoft Outlook e até mesmo usar macros para automatizar o processo.

Etapa 2: normalizar dados usando o assistente de análise de tabela

À primeira vista, percorrer o processo de normalização dos seus dados pode parecer uma tarefa assustadora. Felizmente, a normalização de tabelas no Access é um processo que é muito mais fácil, graças ao assistente de análise de tabela.

o assistente de análise de tabela

1. Arraste as colunas selecionadas para uma nova tabela e crie automaticamente relações

2. Use os comandos de botão para renomear uma tabela, adicionar uma chave primária, transformar uma coluna existente em uma chave primária e desfazer a última ação

Você pode usar esse assistente para fazer o seguinte:

  • Converta uma tabela em um conjunto de tabelas menores e crie automaticamente uma relação de chave primária e externa entre as tabelas.

  • Adicione uma chave primária a um campo existente que contenha valores exclusivos ou crie um novo campo ID que use o tipo de dados numeração automática.

  • Crie automaticamente relações para impor a integridade referencial a atualizações em cascata. As exclusões em cascata não são adicionadas automaticamente para evitar a exclusão acidental de dados, mas você pode facilmente adicionar exclusões em cascata mais tarde.

  • Pesquise novas tabelas para dados redundantes ou duplicados (como o mesmo cliente com dois números de telefone diferentes) e atualize isso como desejar.

  • Faça backup da tabela original e renomeie-a acrescentando "_OLD" ao seu nome. Em seguida, crie uma consulta que reconstrua a tabela original, com o nome da tabela original para que todos os formulários ou relatórios existentes baseados na tabela original funcionem com a nova estrutura de tabela.

Para obter mais informações, consulte normalizar seus dados usando o analisador de tabela.

Etapa 3: conectar-se para acessar dados do Excel

Depois que os dados tiverem sido normalizados no Access e uma consulta ou tabela tiver sido criada para reconstruir os dados originais, é uma simples questão de conexão com os dados do Access a partir do Excel. Seus dados agora estão no Access como uma fonte de dados externa e, portanto, podem ser conectados à pasta de trabalho por meio de uma conexão de dados, que é um contêiner de informações que é usado para localizar, fazer logon em e acessar a fonte de dados externa. As informações de conexão são armazenadas na pasta de trabalho e também podem ser armazenadas em um arquivo de conexão, como um arquivo de conexão de dados do Office (ODC) (extensão de nome de arquivo. odc) ou um arquivo de nome de fonte de dados (extensão. DSN). Depois de conectar-se a dados externos, você também pode atualizar (ou atualizar) automaticamente a pasta de trabalho do Excel a partir do Access sempre que os dados forem atualizados no Access.

Para obter mais informações, consulte importar dados de fontes de dados externas (Power Query).

Colocar seus dados no Access

Esta seção orienta você pelas fases a seguir de normalizar seus dados: quebrar valores nas colunas vendedor e endereço para suas partes mais atômicas, separar assuntos relacionados em suas próprias tabelas, copiar e colar essas tabelas do Excel para o Access, criar relações de chave entre tabelas do Access recém-criadas e criar e executar uma consulta simples no Access para retornar informações.

Dados de exemplo em formato não normalizado

A planilha a seguir contém valores não atômicos na coluna vendedor e na coluna endereço. Ambas as colunas devem ser divididas em duas ou mais colunas separadas. Esta planilha também contém informações sobre vendedores, produtos, clientes e pedidos. Essas informações também devem ser divididas ainda mais, por assunto, em tabelas separadas.

Vendedor

ID do pedido

Data do Pedido

ID do Produto

Quant

Andrade

Nome do cliente

Endereço

Telefone

Li, Yale

2349

3/4/09

C-789

3

$7

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, Stellem

2350

3/4/09

A-2275

2

$16.75

Empresa Aventura

1025 Rio de círculo da Colômbia, WA 98234

425-555-0185

Adams, Stellem

2350

3/4/09

F-198

6

$5.25

Empresa Aventura

1025 Rio de círculo da Colômbia, WA 98234

425-555-0185

Adams, Stellem

2350

3/4/09

B-205

1

$4.50

Empresa Aventura

1025 Rio de círculo da Colômbia, WA 98234

425-555-0185

Hance, Jim

2351

3/4/09

C-795

6

$9.75

Contoso, Ltd.

2302 Harvard ave Palmares, WA 98227

425-555-0222

Hance, Jim

2352

3/5/09

A-2275

2

$16.75

Empresa Aventura

1025 Rio de círculo da Colômbia, WA 98234

425-555-0185

Hance, Jim

2352

3/5/09

D-4420

3

$7.25

Empresa Aventura

1025 Rio de círculo da Colômbia, 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

Fourth Coffee

7007 Cornell St Redmond, WA 98199

425-555-0201

Informações em suas menores partes: dados atômicos

Trabalhando com os dados neste exemplo, você pode usar o comando texto para coluna no Excel para separar as partes "atômicas" de uma célula (como endereço, cidade, estado e CEP) em colunas discretas.

A tabela a seguir mostra as novas colunas na mesma planilha após elas terem sido divididas para tornar todos os valores atômicos. Observe que as informações na coluna vendedor foram divididas nas colunas sobrenome e nome e se as informações da coluna endereço foram divididas nas colunas endereço, cidade, estado e CEP do endereço. Estes dados estão em "primeiro formulário normal".

Sobrenome

Nome

 

Endereço

Cidade

Estado

Código Postal

Li

Yale

2302 Harvard ave

Palmares

WA

98227

Adams

Leonilde

1025 em círculo de Colômbia

Rio de Janeiro

WA

98234

Hance

Jim

2302 Harvard ave

Palmares

WA

98227

Koch

Reed

7007 Cornell St Redmond

Fortaleza

WA

98199

Separar dados para entidades organizadas no Excel

As várias tabelas de dados de exemplo a seguir mostram as mesmas informações da planilha do Excel depois de serem divididas em tabelas de vendedores, produtos, clientes e pedidos. O design da tabela não é final, mas está no caminho certo.

A tabela vendedores contém apenas informações sobre a equipe de vendas. Observe que cada registro tem uma ID exclusiva (ID do vendedor). O valor da ID do vendedor será usado na tabela pedidos para conectar os pedidos aos vendedores.

Vendedores

ID do vendedor

Sobrenome

Nome

101

Li

Yale

103

Adams

Leonilde

105

Hance

Jim

107

Koch

Reed

A tabela produtos contém apenas informações sobre produtos. Observe que cada registro tem uma ID exclusiva (ID do produto). O valor da ID do produto será usado para conectar informações do produto à tabela detalhes do pedido.

Product

ID do Produto

Andrade

A-2275

16,75

B-205

4,50

C-789

7, 0

C-795

9,75

D-4420

7,25

F-198

5,25

A tabela clientes contém apenas informações sobre clientes. Observe que cada registro tem uma ID exclusiva (ID do cliente). O valor da ID do cliente será usado para conectar as informações do cliente à tabela pedidos.

Clientes

Código do cliente

Nome

Endereço

Cidade

Estado

Código Postal

Telefone

1001

Contoso, Ltd.

2302 Harvard ave

Palmares

WA

98227

425-555-0222

1003

Empresa Aventura

1025 em círculo de Colômbia

Rio de Janeiro

WA

98234

425-555-0185

1005

Fourth Coffee

7007 Cornell St

Fortaleza

WA

98199

425-555-0201

A tabela pedidos contém informações sobre pedidos, vendedores, clientes e produtos. Observe que cada registro tem uma ID exclusiva (ID do pedido). Algumas das informações nesta tabela devem ser divididas em uma tabela adicional que contém detalhes do pedido para que a tabela pedidos contenha apenas quatro colunas — a identificação exclusiva do pedido, a data do pedido, a ID do vendedor e a ID do cliente. A tabela mostrada aqui ainda não foi dividida na tabela detalhes do pedido.

Pedidos

ID do pedido

Data do Pedido

ID do vendedor

ID do Cliente

ID do Produto

Quant

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

Detalhes do pedido, como a ID do produto e a quantidade, são movidos da tabela pedidos e armazenados em uma tabela chamada detalhes do pedido. Lembre-se de que há 9 pedidos, portanto, faz sentido que há 9 registros nessa tabela. Observe que a tabela pedidos tem uma ID exclusiva (ID do pedido), que será referida na tabela detalhes do pedido.

O design final da tabela pedidos deve ser semelhante ao seguinte:

Pedidos

ID do pedido

Data do Pedido

ID do vendedor

ID do 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

A tabela detalhes do pedido não contém colunas que exijam valores exclusivos (ou seja, não há chave primária), portanto, para que qualquer ou todas as colunas contenham dados "redundantes". No entanto, os dois registros nessa tabela devem ser completamente idênticos (essa regra se aplica a qualquer tabela em um banco de dados). Nesta tabela, deve haver 17 registros — cada um deles correspondendo a um produto em uma ordem individual. Por exemplo, na ordem 2349, três produtos C-789 compõem uma das duas partes da ordem inteira.

A tabela detalhes do pedido deve, portanto, parecer como a seguir:

Detalhes do pedido

ID do Pedido

ID do Produto

Quant

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 e colar dados do Excel para o Access

Agora que as informações sobre vendedores, clientes, produtos, pedidos e detalhes do pedido foram divididas em assuntos separados no Excel, você pode copiar esses dados diretamente para o Access, onde ele se tornará tabelas.

Criando relações entre as tabelas do Access e executando uma consulta

Depois de mover seus dados para o Access, você pode criar relações entre tabelas e, em seguida, criar consultas para retornar informações sobre várias entidades. Por exemplo, você pode criar uma consulta que retorne a ID do pedido e os nomes dos vendedores para pedidos inseridos entre o 3/05/09 e o 3/08/09.

Além disso, você pode criar formulários e relatórios para facilitar a entrada de dados e a análise de vendas.

Precisa de mais ajuda?

Você pode sempre consultar um especialista na Excel Tech Community, obter suporte na Comunidade de respostas ou sugerir um novo recurso ou melhoria no UserVoice do Excel.

Precisa de mais ajuda?

Expanda suas habilidades no Office
Explore o treinamento
Obtenha novos recursos primeiro
Ingressar no Office Insider

Essas informações foram úteis?

Obrigado por seus comentários!

Agradecemos pelos seus comentários! Parece que pode ser útil conectar você a um de nossos agentes de suporte do Office.

×