Примечание: 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 рекомендуется:
-
Преобразуйте ячейки, содержащие неатомные данные (т. е. несколько значений в одной ячейке), в несколько столбцов. Например, ячейку в столбце "Навыки", которая содержит несколько значений навыков, таких как "Программирование на C#", "Программирование VBA" и "Веб-дизайн", следует разбить на отдельные столбцы, каждый из которых содержит только одно значение навыка.
-
Используйте команду TRIM для удаления начальных, конечных и нескольких внедренных пространств.
-
Удалите непечатаемые символы.
-
Поиск и исправление орфографических ошибок и ошибок препинания.
-
Удалите повторяющиеся строки или повторяющиеся поля.
-
Убедитесь, что столбцы данных не содержат смешанные форматы, особенно числа, отформатированные в виде текста, или даты, отформатированные в виде чисел.
Дополнительные сведения см. в следующих разделах справки Excel:
Примечание: Если ваши потребности в очистке данных сложны или у вас нет времени или ресурсов для самостоятельной автоматизации процесса, вы можете использовать стороннего поставщика. Для получения дополнительных сведений найдите "программное обеспечение для очистки данных" или "качество данных" в любимой поисковой системе в веб-браузере.
Выбор оптимального типа данных при импорте
Во время операции импорта в Access необходимо сделать правильный выбор, чтобы получить несколько ошибок преобразования (если таковые есть) для выполнения которых потребуется вмешательство вручную. В следующей таблице показано, как преобразуются числовые форматы Excel и типы данных Access при импорте данных из Excel в Access, а также приведены советы по выбору лучших типов данных в мастере импорта электронных таблиц.
|
Числовой формат Excel |
Тип данных Access |
Примечания |
Рекомендации |
|---|---|---|---|
|
Text (Текст) |
Текст, памятка |
Тип данных Access Text хранит буквенно-цифровые данные длиной до 255 символов. Тип данных Access Memo хранит буквенно-цифровые данные длиной до 65 535 символов. |
Выберите Memo , чтобы избежать усечения данных. |
|
Число, Процент, Дробь, Научный |
Числовой |
Access имеет один тип данных Number, который зависит от свойства Field Size (Byte, Integer, Long Integer, Single, Double, Decimal). |
Выберите 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 для всех значений No, тогда как Excel использует 1 для всех значений TRUE и 0 для всех значений FALSE. |
Выберите Да/Нет, чтобы автоматически преобразовать базовые значения. |
|
Гиперссылка |
Гиперссылка |
Гиперссылка в Excel и Access содержит URL-адрес или веб-адрес, который можно щелкнуть и подписаться. |
Выберите Гиперссылка, в противном случае access может использовать тип данных Text по умолчанию. |
После того как данные будут в Access, вы можете удалить данные Excel. Не забудьте сначала создать резервную копию исходной книги Excel перед ее удалением.
Дополнительные сведения см. в разделе Справки Access Импорт или ссылка на данные в книге Excel.
Автоматическое добавление данных простым способом
Распространенной проблемой для пользователей Excel является добавление данных с одинаковыми столбцами в один большой лист. Например, у вас может быть решение для отслеживания ресурсов, которое началось в Excel, но теперь выросло до файлов из многих рабочих групп и отделов. Эти данные могут находиться в разных листах и книгах или в текстовых файлах, которые являются веб-каналами данных из других систем. Нет команды пользовательского интерфейса или простого способа добавления аналогичных данных в Excel.
Лучшее решение — использовать Access, где можно легко импортировать и добавлять данные в одну таблицу с помощью мастера импорта электронных таблиц. Кроме того, в одну таблицу можно добавить большое количество данных. Вы можете сохранить операции импорта, добавить их как запланированные задачи Microsoft Outlook и даже использовать макросы для автоматизации процесса.
Шаг 2. Нормализация данных с помощью мастера анализатора таблиц
На первый взгляд, пошаговое выполнение процесса нормализации данных может показаться сложной задачей. К счастью, нормализация таблиц в Access гораздо проще благодаря мастеру анализатора таблиц.
1. Перетаскивание выбранных столбцов в новую таблицу и автоматическое создание связей
2. Используйте команды кнопки, чтобы переименовать таблицу, добавить первичный ключ, сделать существующий столбец первичным ключом и отменить последнее действие.
Этот мастер можно использовать для выполнения следующих действий:
-
Преобразуйте таблицу в набор таблиц меньшего размера и автоматически создайте связь первичного и внешнего ключей между таблицами.
-
Добавьте первичный ключ в существующее поле, содержащее уникальные значения, или создайте новое поле идентификатора, использующее тип данных AutoNumber.
-
Автоматическое создание связей для обеспечения целостности ссылок с помощью каскадных обновлений. Каскадные удаления не добавляются автоматически, чтобы предотвратить случайное удаление данных, но вы можете легко добавить каскадные удаления позже.
-
Выполните поиск в новых таблицах избыточных или повторяющихся данных (например, одного клиента с двумя разными номерами телефонов) и обновите их по мере необходимости.
-
Создайте резервную копию исходной таблицы и переименуйте ее, добавив "_OLD" к ее имени. Затем создается запрос, который восстанавливает исходную таблицу с именем исходной таблицы, чтобы все существующие формы или отчеты, основанные на исходной таблице, работали с новой структурой таблицы.
Дополнительные сведения см. в статье Нормализация данных с помощью анализатора таблиц.
Шаг 3. Подключение к доступу к данным из Excel
После нормализации данных в Access и создания запроса или таблицы, которые восстанавливают исходные данные, просто подключиться к данным Access из Excel. Теперь ваши данные находятся в Access как внешний источник данных, поэтому их можно подключить к книге через подключение к данным, которое представляет собой контейнер данных, используемый для поиска внешнего источника данных, входа в нее и доступа к нему. Сведения о подключении хранятся в книге, а также могут храниться в файле подключения, например в файле подключения к данным Office (ODC) (расширение ODC-файла) или файле имени источника данных (расширение DSN). После подключения к внешним данным вы также можете автоматически обновлять (или обновлять) книгу Excel из Access при каждом обновлении данных в Access.
Дополнительные сведения см. в статье Импорт данных из внешних источников данных (Power Query).
Получение данных в Access
В этом разделе описаны следующие этапы нормализации данных: разбивка значений в столбцах Salesperson и Address на наиболее атомарные фрагменты, разделение связанных субъектов на собственные таблицы, копирование и вставка этих таблиц из Excel в Access, создание ключевых связей между вновь созданными таблицами Access, а также создание и выполнение простого запроса в Access для возврата сведений.
Пример данных в не нормализованной форме
На следующем листе содержатся не атомарные значения в столбцах Salesperson и Address. Оба столбца должны быть разделены на два или несколько отдельных столбцов. Этот лист также содержит сведения о продавцах, продуктах, клиентах и заказах. Эти сведения также следует разделить на отдельные таблицы по темам.
|
Продавец |
Идентификатор заказа |
Дата заказа |
Код товара |
Количество |
Цена |
Имя клиента |
Address (Адрес) |
Телефон |
|---|---|---|---|---|---|---|---|---|
|
Ли, Йель |
2349 |
3/4/09 |
C-789 |
3 |
7,00 долл. США |
Кофейная фабрика |
7007 Корнелл Сент-Редмонд, WA 98199 |
425-555-0201 |
|
Ли, Йель |
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 Колумбия Круг Киркланд, 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 Гарвард авеню Беллевью, 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 |
D-4420 |
3 |
7,25 долл. США |
Adventure Works |
1025 Колумбия Круг Киркланд, WA 98234 |
425-555-0185 |
|
Кох, Рид |
2353 |
3/7/09 |
A-2275 |
6 |
16,75 долл. США |
Кофейная фабрика |
7007 Корнелл Сент-Редмонд, WA 98199 |
425-555-0201 |
|
Кох, Рид |
2353 |
3/7/09 |
C-789 |
5 |
7,00 долл. США |
Кофейная фабрика |
7007 Корнелл Сент-Редмонд, WA 98199 |
425-555-0201 |
Информация в ее мельчайших частях: атомарные данные
При работе с данными в этом примере можно использовать команду "Текст в столбец " в Excel, чтобы разделить "атомарные" части ячейки (например, адрес улицы, город, штат и почтовый индекс) на отдельные столбцы.
В следующей таблице показаны новые столбцы на том же листе после их разделения, чтобы сделать все значения атомарными. Обратите внимание, что сведения в столбце Salesperson были разделены на столбцы Фамилия и Имя, а сведения в столбце Адрес были разделены на столбцы Street Address, City, State и ZIP Code. Эти данные приведены в "первой нормальной форме".
|
Фамилия |
Имя |
|
адрес; |
Город |
Субъект |
Почтовый индекс |
|---|---|---|---|---|---|---|
|
Литий |
Йель |
2302 Гарвард авеню |
Омск |
Красноярский край |
98227 |
|
|
Адамс |
Эллен |
1025 Колумбия Круг |
Сочи |
Красноярский край |
98234 |
|
|
Ханс |
Алексей |
2302 Гарвард авеню |
Омск |
Красноярский край |
98227 |
|
|
Кох |
Рид |
7007 Корнелл Сент-Редмонд |
Редмонд |
Красноярский край |
98199 |
Разделение данных на упорядоченные темы в Excel
В нескольких таблицах с примерами данных, приведенных ниже, отображаются одни и те же сведения с листа Excel после того, как они были разделены на таблицы для продавцов, продуктов, клиентов и заказов. Таблица не является окончательной, но она находится на правильном пути.
Таблица Salespersons содержит только сведения о торговом персонале. Обратите внимание, что каждая запись имеет уникальный идентификатор (SalesPerson ID). Значение Идентификатора SalesPerson будет использоваться в таблице Orders для подключения заказов к продавцам.
|
Продавцов |
||
|---|---|---|
|
Идентификатор продавца |
Фамилия |
Имя |
|
101 |
Литий |
Йель |
|
103 |
Адамс |
Эллен |
|
105 |
Ханс |
Алексей |
|
107 |
Кох |
Рид |
Таблица Products содержит только сведения о продуктах. Обратите внимание, что каждая запись имеет уникальный идентификатор (Product ID). Значение Product ID будет использоваться для подключения сведений о продукте к таблице "Сведения о заказе".
|
Продукты |
|
|---|---|
|
Код товара |
Цена |
|
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 содержит только сведения о клиентах. Обратите внимание, что каждая запись имеет уникальный идентификатор (идентификатор клиента). Значение Идентификатор клиента будет использоваться для подключения сведений о клиенте к таблице Orders.
|
Customers |
||||||
|---|---|---|---|---|---|---|
|
Код клиента |
Имя |
адрес; |
Город |
Субъект |
Почтовый индекс |
Телефон |
|
1001 |
Contoso, Ltd. |
2302 Гарвард авеню |
Омск |
Красноярский край |
98227 |
425-555-0222 |
|
1003 |
Adventure Works |
1025 Колумбия Круг |
Сочи |
Красноярский край |
98234 |
425-555-0185 |
|
1005 |
Кофейная фабрика |
7007 Корнелл Санкт |
Редмонд |
Красноярский край |
98199 |
425-555-0201 |
Таблица Orders содержит сведения о заказах, продавцах, клиентах и продуктах. Обратите внимание, что каждая запись имеет уникальный идентификатор (идентификатор заказа). Некоторые сведения в этой таблице должны быть разделены на дополнительную таблицу, содержащую сведения о заказе, чтобы таблица Orders содержала только четыре столбца: уникальный идентификатор заказа, дата заказа, идентификатор продавца и идентификатор клиента. Показанная здесь таблица еще не разделена на таблицу Сведения о заказе.
|
Заказы |
|||||
|---|---|---|---|---|---|
|
Идентификатор заказа |
Дата заказа |
Идентификатор 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 записей. Обратите внимание, что таблица Orders имеет уникальный идентификатор (Order ID), на который будет ссылаться таблица "Сведения о заказе".
Окончательная конструкция таблицы Orders должна выглядеть следующим образом:
|
Заказы |
|||
|---|---|---|---|
|
Идентификатор заказа |
Дата заказа |
Идентификатор 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 можно создать связи между таблицами, а затем создать запросы для возврата сведений о различных темах. Например, можно создать запрос, возвращающий идентификатор заказа и имена продавцов для заказов, введенных в период с 05.03.09 по 08.03.09.
Кроме того, можно создавать формы и отчеты, чтобы упростить ввод данных и анализ продаж.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
