Примечание
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:
- Первые 10 способов очистки данных
- Фильтр уникальных значений или удаление повторяющихся значений
- Преобразование чисел из текстового формата в числовой
- Преобразование дат из текстового формата в формат даты
Примечание
Если ваши потребности в очистке данных сложны или у вас нет времени или ресурсов для самостоятельной автоматизации процесса, вы можете использовать стороннего поставщика. Для получения дополнительных сведений найдите "программное обеспечение для очистки данных" или "качество данных" в любимой поисковой системе в веб-браузере.
Выбор оптимального типа данных при импорте
Во время операции импорта в 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 или получить поддержку в сообществах.