Перемещение данных из Excel в Access

В этой статье объясняется, как переместить данные из Excel для доступа к реляционным таблицам и их преобразования в Microsoft Excel и совместное использование. Для подведения итогов лучше использовать Microsoft Access для записи, хранения, запроса и совместного использования данных, а Excel лучше подходит для вычисления, анализа и визуализации данных.

Две статьи: Access или Excel для управления данными и наиболее важные причины для использования Access в Excel. Обсудите, какая программа лучше всего подходит для конкретной задачи, а также как использовать Excel и Access для создания практических решений.

При переносе данных из Excel в Access выполняются три основных действия.

Три основных шага

Примечание: Сведения о моделировании данных и связях в Access можно найти в разделе Основные сведения о создании баз данных.

Шаг 1: импорт данных из Excel в Access

Импорт данных — это операция, которая может пойти намного более гладко, если вы захотите время для подготовки и очистки данных. Импорт данных напоминает переход на новую домашнюю страницу. Если вы хотите удалить и упорядочить свои права перед тем, как вы перейдете, присоединиться к новой домашней сети будет еще проще.

Очистка данных перед импортом

Перед импортом данных в Access в Excel рекомендуется выполнить следующие действия.

  • Преобразуйте ячейки, содержащие неатомарные данные (то есть несколько значений в одной ячейке), в несколько столбцов. Например, ячейка в столбце "квалификация", содержащая несколько значений опыта, например "Программирование C#", "программирование VBA" и "веб-дизайн", должна быть разделена для отдельных столбцов, каждый из которых содержит только одно значение навыка.

  • Для удаления начальных, конечных и нескольких внедренных пробелов используйте команду "обрезать".

  • Удаление непечатаемых символов.

  • Поиск и устранение ошибок правописания и знаков препинания.

  • Удалите повторяющиеся строки или повторяющиеся поля.

  • Убедитесь, что столбцы данных не содержат смешанные форматы, особенно числа в виде текста или дат, отформатированных как числа.

Дополнительные сведения можно найти в следующих статьях справки по Excel:

Примечание: Если вы не хотите выполнять чистку данных или у вас нет времени или ресурсов для автоматизации процесса, вы можете воспользоваться сторонним поставщиком. Чтобы получить дополнительные сведения, выполните поиск по запросу "Программная очистка данных" или "качество передачи данных" в веб-браузере.

Выбор наиболее подходящих типов данных при импорте

В процессе импорта в Access вы можете выбрать подходящий вариант, чтобы получить доступ к некоторым ошибкам преобразования, для которых потребуется ручное вмешательство. В следующей таблице показано, как преобразуются форматы чисел Excel и типы данных Access при импорте данных из Excel в Access, а также приведены советы по наилучшим типам данных, которые можно выбрать в мастере импорта электронной таблицы.

Формат номера Excel

Тип данных Access

Примечания

Рекомендации

Текст

Текст, поле MEMO

Тип данных "текст" в Access хранит буквенно-цифровые данные длиной до 255 символов. Тип данных МЕМО Access сохраняет алфавитно-цифровые данные до 65 535 символов.

Нажмите кнопку " записка ", чтобы не усекать данные.

Число, процент, дробь, экспоненциальный

Число.

У Access есть один числовой тип данных, который может изменяться в зависимости от свойства размера поля (байт, целое, длинное целое, одинарное, двойное, дес.).

Чтобы избежать ошибок при преобразовании данных, выберите значение Double .

Дата

Дата

Для хранения дат Access и Excel используют одинаковый порядковый номер даты. В Access диапазон дат больше: от-657 434 (1 января 100 г. Н.Э.) до 2 958 465 (31 декабря, 9999 г. Н.Э.).

Поскольку Access не распознает систему дат 1904 (используется в Excel для Macintosh), вам нужно преобразовать даты в Excel или Access, чтобы избежать путаницы.

Дополнительные сведения можно найти в разделе изменение системы дат, формата или двух цифр года , а также импорт или связывание данных в книге Excel.

Выберите пункт Дата.

Время

Время

Access и Excel. оба значения времени в магазине хранят данные с одинаковым типом данных.

Выберите время, которое обычно является значением по умолчанию.

Валюта, учет

Деньги

В Access тип данных Currency хранит данные в виде 8-байтовых чисел с точностью до четырех десятичных разрядов и используется для хранения финансовых данных и предотвращения округления значений.

Выберите денежный формат, который обычно является значением по умолчанию.

логический

Логический

Access использует-1 для всех значений "Да" и 0 для всех значений, в то время как Excel использует 1 для всех значений истина и 0 для всех значений "ложь".

Выберите "да/нет", который автоматически преобразует базовые значения.

Гиперссылка

Гиперссылка

Гиперссылка в Excel и Access включает URL-адрес или веб-адреса, по которым можно перейти и подписаться на него.

Выберите пункт Гиперссылка, в противном случае Access по умолчанию использует текстовый тип данных.

После того как данные будут в Access, вы можете удалить данные Excel. Прежде чем удалять исходную книгу Excel, не забудьте сначала создать ее резервную копию.

Дополнительные сведения можно найти в статье Справка по Access Импорт или связывание с данными в книге Excel.

Простой способ автоматического добавления данных

Чаще всего пользователи Excel добавляют данные с одинаковыми столбцами в один большой лист. Например, вы можете использовать решение для отслеживания ресурсов, которое началось в Excel, но теперь выросла для включения файлов из множества рабочих групп и отделов. Эти данные могут находиться на разных листах и книгах, а также в текстовых файлах, которые являются каналами данных из других систем. В Excel не существует команды пользовательского интерфейса или простого способа добавления похожих данных.

Лучшим решением будет использование Access, где вы можете легко импортировать и добавлять данные в одну таблицу с помощью мастера импорта электронной таблицы. Кроме того, вы можете добавить в одну таблицу большое количество данных. Вы можете сохранить операции импорта, добавить их как запланированные задачи Microsoft Outlook и даже использовать макросы для автоматизации процесса.

Действие 2: нормализация данных с помощью мастера анализа таблиц

На первый взгляд процесс нормализации данных может показаться довольно сложной задачей. К счастью, нормализация таблиц в Access — это процесс, который был бы более простым благодаря мастеру анализа таблиц.

Мастер анализа таблиц

1. Перетащите выделенные столбцы в новую таблицу и автоматически создайте связи.

2. Использование команд кнопок для переименования таблицы, добавления первичного ключа, создания существующего столбца первичного ключа и отмены последнего действия

С помощью этого мастера вы можете сделать следующее:

  • Преобразование таблицы в набор небольших таблиц и автоматическое создание первичного и внешнего ключа между таблицами.

  • Добавьте первичный ключ в существующее поле, содержащее уникальные значения, или создайте новое поле с типом данных "Счетчик".

  • Автоматическое создание связей для обеспечения целостности данных с помощью каскадных обновлений. Каскадные удаления не добавляются автоматически, чтобы предотвратить случайное удаление данных, но вы можете легко добавить каскадные удаления позже.

  • Поиск в новых таблицах избыточных или повторяющихся данных (например, для одного и того же клиента с двумя разными номерами телефонов), а также при необходимости обновлять.

  • Создавайте резервные копии исходной таблицы и переименование, добавив "_OLD" к имени. Затем вы создаете запрос, который восстанавливает исходную таблицу с исходным именем таблицы, так что существующие формы и отчеты, основанные на исходной таблице, будут работать с новой структурой таблицы.

Дополнительные сведения можно найти в разделе нормализация данных с помощью анализа таблиц.

Шаг 3: подключение к данным Access из Excel

После нормализации данных в Access и создания запроса или таблицы, которые воссоздают исходные данные, достаточно просто подключиться к данным Access из Excel. Теперь ваши данные находятся в Access как внешний источник данных и могут быть подключены к книге с помощью подключения к данным, которое является контейнером данных, который используется для поиска, входа и доступа к внешнему источнику данных. Информация о соединении хранится в книге, и ее также можно хранить в файле подключения, например в файле подключения к данным Office (ODC) или в файле имени источника данных (расширение. DSN). После подключения к внешним данным вы также можете автоматически обновлять (или обновлять) книгу Excel при каждом обновлении данных в Access.

Дополнительные сведения можно найти в разделе Импорт данных из внешних источников данных (Power Query).

Получение данных в Access

Этот раздел поможет вам выполнить следующие этапы нормализации данных: разбиение значений в столбцах "продавец" и "адрес" на свои самые атомарные части и разделение связанных тем на собственные таблицы, копирование и вставка этих таблиц из Excel в Доступ, создание связей между только что созданными таблицами Access, а также создание и выполнение простого запроса в Access для возврата данных.

Пример данных в ненормализованной форме

На следующем листе содержатся неатомарные значения в столбце "продавец" и в столбце "адрес". Оба столбца следует разделить на несколько отдельных столбцов. На этом листе также содержатся сведения о продавцах, продуктах, клиентах и заказах. Эти сведения также следует разбить на отдельные таблицы с помощью темы.

Продавец

Идентификатор заказа

Дата заказа

Код товара

Ве

продажи

Имя клиента

Адрес

Телефон

Li, Яле

2349

3/4/09

C-789

3

$7,00

Кофейная фабрика

7007 Корнелл St Redmond, WA 98199

425-555-0201

Li, Яле

2349

3/4/09

C-795

6

$9,75

Кофейная фабрика

7007 Корнелл St Redmond, WA 98199

425-555-0201

Адамова извлекла, Екатерине

2350

3/4/09

A – 2275

2

$16,75

Adventure Works

1025 Колумбия, Киркланд, WA 98234

425-555-0185

Адамова извлекла, Екатерине

2350

3/4/09

F-198

6

$5,25

Adventure Works

1025 Колумбия, Киркланд, WA 98234

425-555-0185

Адамова извлекла, Екатерине

2350

3/4/09

B – 205

1

$4,50

Adventure Works

1025 Колумбия, Киркланд, WA 98234

425-555-0185

Ханце, Джим

2351

3/4/09

C-795

6

$9,75

Contoso, Ltd.

2302 Харвард Ave Омск, WA 98227

425-555-0222

Ханце, Джим

2352

3/5/09

A – 2275

2

$16,75

Adventure Works

1025 Колумбия, Киркланд, WA 98234

425-555-0185

Ханце, Джим

2352

3/5/09

Г-4420

3

$7,25

Adventure Works

1025 Колумбия, Киркланд, WA 98234

425-555-0185

Коч, Рид

2353

3/7/09

A – 2275

6

$16,75

Кофейная фабрика

7007 Корнелл St Redmond, WA 98199

425-555-0201

Коч, Рид

2353

3/7/09

C-789

5

$7,00

Кофейная фабрика

7007 Корнелл St Redmond, WA 98199

425-555-0201

Данные в наименьшей части: атомарные данные

Работа с данными. в этом примере вы можете использовать команду " текст по столбцам " в Excel, чтобы отделить отдельные части ячейки (такие как почтовый адрес, город, область и почтовый индекс) на дискретные столбцы.

В следующей таблице показаны новые столбцы на том же листе, после того как они были разделены, чтобы сделать все значения атомарными. Обратите внимание, что информация в столбце "продавец" была разделена на "Фамилия" и "имя", а данные в столбце "адрес" разделены на столбцы "почтовый адрес", "город", "штат" и "индекс". Эти данные находятся в первой обычной форме.

Фамилия

Имя

 

адрес;

Город

Субъект

Почтовый индекс

Li

яле

2302 Харвард Ave

Омск

Красноярский край

98227

Адамова извлекла

Екатерине

1025 Колумбия, круг

Сочи

Красноярский край

98234

ханце

Алексей

2302 Харвард Ave

Омск

Красноярский край

98227

коч

Рид

7007 Корнелл St Redmond

Редмонд

Красноярский край

98199

Разбиение данных на организованные темы в Excel

В нескольких таблицах примеров данных отображаются те же данные, что и на листе Excel, после того как они были разделены на таблицы для продавцов, продуктов, клиентов и заказов. Работа с таблицами не является конечной, но она находится на нужном дорожке.

В таблице менеджеры содержатся сведения только о сотрудниках отдела продаж. Обратите внимание, что у каждой записи есть уникальный идентификатор (идентификатор продавца). Значение идентификатора продавца будет использоваться в таблице "заказы" для соединения заказов с продавцами.

Менеджеры

Идентификатор продавца

Фамилия

Имя

101

Li

яле

103

Адамова извлекла

Екатерине

105

ханце

Алексей

107

коч

Рид

В таблице Товары содержатся только сведения о продуктах. Обратите внимание, что у каждой записи есть уникальный идентификатор (код продукта). Значение кода продукта будет использоваться для соединения сведений о продукте с таблицей "сведения о заказе".

Товара

Код товара

продажи

A – 2275

16,75

B – 205

4,50

C-789

7,00

C-795

9,75

Г-4420

7,25

F-198

5,25

В таблице "клиенты" содержатся только сведения о клиентах. Обратите внимание, что у каждой записи есть уникальный идентификатор (идентификатор клиента). Значение идентификатора клиента будет использоваться для подключения сведений о клиенте к таблице "заказы".

Customers

Код клиента

Имя

адрес;

Город

Субъект

Почтовый индекс

Телефон

1001

Contoso, Ltd.

2302 Харвард Ave

Омск

Красноярский край

98227

425-555-0222

1003

Adventure Works

1025 Колумбия, круг

Сочи

Красноярский край

98234

425-555-0185

1005

Кофейная фабрика

7007 Корнелл St

Редмонд

Красноярский край

98199

425-555-0201

В таблице "заказы" содержатся сведения о заказах, продавцах, клиентах и продуктах. Обратите внимание, что у каждой записи есть уникальный идентификатор (идентификатор заказа). Некоторые сведения в этой таблице должны быть разделены на дополнительные таблицы, содержащие сведения о заказах, чтобы таблица "заказы" содержала только четыре столбца — уникальный идентификатор заказа, дату заказа, идентификатор продавца и идентификатор клиента. Показанная здесь таблица пока не разделена на таблицу "сведения о заказах".

Orders

Идентификатор заказа

Дата заказа

Идентификатор продавца

Код клиента

Код товара

Ве

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

Г-4420

3

2353

3/7/09

107

1005

A – 2275

6

2353

3/7/09

107

1005

C-789

5

Сведения о заказах, например код продукта и количество, переносятся из таблицы "заказы" и хранятся в таблице "сведения о заказе". Имейте в виду, что у вас есть 9 заказов, поэтому в этой таблице имеет смысл 9 записей. Обратите внимание, что в таблице Orders (заказы) есть уникальный идентификатор (идентификатор заказа), на который будут обращаться из таблицы сведения о заказе.

Конечный дизайн таблицы Orders должен выглядеть следующим образом:

Orders

Идентификатор заказа

Дата заказа

Идентификатор продавца

Код клиента

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

В таблице "сведения о заказе" не содержатся столбцы, для которых требуются уникальные значения (это значит, что первичный ключ не существует), поэтому все столбцы должны содержать избыточные данные. Однако ни одна из двух записей в этой таблице не должна быть полностью идентичной (это правило применяется к любой таблице в базе данных). В этой таблице должно быть 17 записей, каждый из которых соответствует продукту в отдельном порядке. Например, в 2349, три изделия C-789 составляют одну из двух частей всего заказа.

В таблице "сведения о заказе" должны быть указаны следующие данные:

Сведения о заказе

Номер заказа

Код продукта

Ве

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

Г-4420

3

2353

A – 2275

6

2353

C-789

5

Копирование и вставка данных из Excel в Access

Теперь, когда сведения о продавцах, клиентах, продуктах, заказах и заказах были разбиты на отдельные темы в Excel, вы можете скопировать эти данные прямо в Access, где они становятся таблицами.

Создание связей между таблицами Access и выполнением запроса

После перемещения данных в Access вы можете создавать связи между таблицами, а затем создавать запросы, возвращающие сведения о различных темах. Например, можно создать запрос, возвращающий идентификатор заказа и имена продавцов для заказов, введенных в диапазоне между 3/05/09 и 3/08/09.

Кроме того, вы можете создавать формы и отчеты для упрощения ввода данных и анализа продаж.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Совершенствование навыков работы с Office
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×