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

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

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

При перемещении данных Excel в Access необходимо выполнить три основных шага.

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

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

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

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

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

Прежде чем импортировать данные в Access, Excel рекомендуется:

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

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

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

  • Поиск и исправление ошибок орфографии и пунктуации.

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

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

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

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

Выбор оптимального типа данных при импорте

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

Excel числовом формате

Тип данных Access

Комментарии

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

Текст

Text, Memo

Тип данных Access Text хранит буквенно-цифровые данные до 255 символов. Тип данных Access Memo хранит буквенно-цифровые данные до 65 535 символов.

Выберите memo , чтобы избежать усечения данных.

Number, Percentage, Fraction, Scientific

Числовой

Access имеет один тип данных Number, который зависит от свойства Размера поля (byte, Integer, Long Integer, Single, Double, Decimal).

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

Дата

Дата

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

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

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

Выберите дату.

Время

Системное время

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

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

Валюта, бухгалтерия

Денежный

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

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

логический

Логический

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

Нажмите кнопку "Да/Нет", которая автоматически преобразует базовые значения.

Гиперссылка

Гиперссылка

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

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

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

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

Автоматическое добавление данных простым способом

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

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

Шаг 2. Нормализация данных с помощью мастера анализатора таблиц

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

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

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

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

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

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

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

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

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

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

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

Шаг 3. Подключение доступ к данным из Excel

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

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

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

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

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

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

Продавец

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

Дата заказа

Код товара

Количество

продажи

Имя клиента

Адрес

Телефон

Li, Yale

2349

3/4/09

C-789

3

7,00 долл. США

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

7007 Корнель-Редмонд, WA 98199

425-555-0201

Li, Yale

2349

3/4/09

C-795

6

9,75 долл. США

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

7007 Корнель-Редмонд, WA 98199

425-555-0201

Адамс, Эллен

2350

3/4/09

A-2275

2

16,75 долл. США

Adventure Works

1025 Circle Circle, WA 98234

425-555-0185

Адамс, Эллен

2350

3/4/09

F-198

6

5,25 долл. США

Adventure Works

1025 Circle Circle, WA 98234

425-555-0185

Адамс, Эллен

2350

3/4/09

B-205

1

4,50 долл. США

Adventure Works

1025 Circle Circle, WA 98234

425-555-0185

Хэнс, Хим

2351

3/4/09

C-795

6

9,75 долл. США

Contoso, Ltd.

2302 Суа Бельвью ,Wa 98227

425-555-0222

Хэнс, Хим

2352

3/5/09

A-2275

2

16,75 долл. США

Adventure Works

1025 Circle Circle, WA 98234

425-555-0185

Хэнс, Хим

2352

3/5/09

D-4420

3

7,25 долл. США

Adventure Works

1025 Circle Circle, WA 98234

425-555-0185

Koch, Reed

2353

3/7/09

A-2275

6

16,75 долл. США

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

7007 Корнель-Редмонд, WA 98199

425-555-0201

Koch, Reed

2353

3/7/09

C-789

5

7,00 долл. США

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

7007 Корнель-Редмонд, WA 98199

425-555-0201

Сведения в его наименьших частях: атомарные данные

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

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

Фамилия

Имя

 

адрес;

Город

Субъект

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

Литий

Йель

2302( 2302, 2302, 2302— 2

Омск

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

98227

Адамс

Эллен

Круг 1025

Сочи

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

98234

Hance

Алексей

2302( 2302, 2302, 2302— 2

Омск

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

98227

Кох

Рид

7007 Корнель-Редмонд

Редмонд

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

98199

Разделение данных на упорядоченные субъекты в Excel

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

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

Продавцов

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

Фамилия

Имя

101

Литий

Йель

103

Адамс

Эллен

105

Hance

Алексей

107

Кох

Рид

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

Продукты

Код товара

продажи

A-2275

16.75

B-205

4.50

C-789

7.00

C-795

9.75

D-4420

7.25

F-198

5,25

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

Customers

Код клиента

Имя

адрес;

Город

Субъект

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

Телефон

1001

Contoso, Ltd.

2302( 2302, 2302, 2302— 2

Омск

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

98227

425-555-0222

1003

Adventure Works

Круг 1025

Сочи

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

98234

425-555-0185

1005

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

7007 Корелл-ст

Редмонд

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

98199

425-555-0201

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

Заказы

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

Дата заказа

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

Код клиента

Код товара

Количество

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

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

Окончательная структура таблицы "Заказы" должна выглядеть следующим образом:

Заказы

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

Дата заказа

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

Код клиента

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

D-4420

3

2353

A-2275

6

2353

C-789

5

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

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

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

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

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Нужна дополнительная помощь?

Совершенствование навыков
Перейти к обучению

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

Насколько вы удовлетворены качеством перевода?
Что повлияло на вашу оценку?

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

×