Примечание
Microsoft Access не поддерживает импорт данных Excel с примененной меткой конфиденциальности. В качестве обходного решения можно удалить метку перед импортом, а затем повторно применить ее после импорта. Дополнительные сведения см. в статье Применение меток конфиденциальности к файлам и электронной почте в Office.
В этой статье показано, как перенести данные из Excel в Access и преобразовать данные в реляционные таблицы, чтобы использовать Microsoft Excel и Access вместе. Подводя итог, можно сказать, что Access лучше всего подходит для сбора, хранения, запроса и обмена данными, а Excel — для вычислений, анализа и визуализации данных.
В двух статьях "Использование Access или Excel для управления данными " и " Десять основных причин использовать Access вместе с Excel" обсуждается, какая программа лучше всего подходит для конкретной задачи и как использовать Excel и Access вместе, чтобы получить практическое решение.
При переносе данных из Excel в Access этот процесс состоит из трех основных этапов.
Примечание
Сведения о моделировании данных и связях в Access см. в статье Основы создания баз данных.
Шаг 1. Импорт данных из Excel в Access
Импорт данных можно выполнить гораздо дольше, если потратить время на подготовку и очистку данных. Импорт данных похож на переезд в новый дом. Если вы уберете и наведете порядок со своим имуществом перед переездом, обустроиться в новом доме будет намного проще.
Очистка данных перед импортом
Перед импортом данных в Access рекомендуется сделать следующее:
- Преобразуйте ячейки, которые содержат неатомарные данные (то есть несколько значений в одной ячейке), в несколько столбцов. Например, ячейку в столбце "Навыки", содержащую несколько значений навыков, таких как "Программирование на C#", "Программирование VBA" и "Веб-дизайн", должна быть разбита на отдельные столбцы, каждый из которых содержит только одно значение навыка.
- С помощью команды СЖПРОБЕЛЫ удалите начальные, конечные и множественные встроенные пробелы.
- Удаление непечатаемых символов.
- Поиск и исправление орфографических и пунктуационных ошибок.
- Удаление повторяющихся строк или повторяющихся полей.
- Убедитесь, что столбцы данных не содержат смешанных форматов, особенно числового формата в текстовом формате или дат в числовом формате.
Дополнительные сведения см. в следующих разделах справки Excel.
- Первые 10 способов очистки данных
- Фильтр уникальных значений или удаление повторяющихся значений
- Преобразование чисел из текстового формата в числовой
- Преобразование дат из текстового формата в формат даты
Примечание
Если ваши потребности в очистке данных сложны или у вас нет времени или ресурсов для самостоятельной автоматизации процесса, вы можете рассмотреть возможность привлечения стороннего поставщика. Для получения дополнительных сведений выполните поиск по запросу "программное обеспечение для очистки данных" или "качество данных" в своей любимой поисковой системе в веб-браузере.
Выбор оптимального типа данных при импорте
Во время импорта в Access необходимо сделать правильный выбор, чтобы при этом возникало мало ошибок преобразования, требующих вмешательства вручную. В таблице ниже описано, как будут преобразованы числовые форматы и типы данных Access при импорте данных из Excel в Access, а также приведены советы по выбору оптимальных типов данных в мастере электронных таблиц.
| Формат чисел в Excel | Тип данных Access | Примечания | Рекомендации |
|---|---|---|---|
| Text (Текст) | Текст, заметка | Текстовый тип данных Access содержит буквенно-цифровые данные длиной до 255 знаков. Тип данных Access MEMO содержит буквенно-цифровые данные длиной до 65 535 знаков. | Выберите "Поле MEMO", чтобы не обрезать данные. |
| Число, процент, дробь, экспоненциальное | Число | В Access существует один тип данных "Числовой", который зависит от свойства "Размер поля" (байт, целое число, длинное целое, одинарное, двойное или десятичное). | Чтобы избежать ошибок преобразования данных, выберите значение "Два". |
| Дата | Дата | В Access и Excel для хранения дат используется один и тот же серийный номер даты. В Access диапазон дат больше: от -657 434 (1 января 100 г.) до 2 958 465 (31 декабря 9999 г.). Так как Access не распознает систему дат 1904 (которая используется в Excel для Macintosh), во избежание путаницы проводить преобразование дат необходимо либо в Excel, либо в Access. Дополнительные сведения см. в статьях "Изменение системы дат, формата или интерпретации двузначного года" и "Импорт или связывание данных в книге Excel". |
Выберите "Дата". |
| Время | Время | И Access, и Excel хранят значения времени с использованием одного и того же типа данных. | Выберите время, которое обычно указывается по умолчанию. |
| Валюта, бухгалтерский учет | Валюта | В Access тип данных "Денежный" сохраняет данные в виде 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) с расширением имени файла или файле имени источника данных (расширение DMS). Подключившись к внешним данным, можно также автоматически обновлять книгу Excel из Access при каждом обновлении данных в Access.
Дополнительные сведения см. в разделе Импорт данных из внешних источников (Power Query).
Добавление данных в Access
В этом разделе рассматриваются следующие этапы нормализации данных: разбиение значений в столбцах "Продавец" и "Адрес" на наиболее атомарные части, разделение связанных между собой тем, выделение связанных между собой таблиц, копирование и вставка таблиц из Excel в Access, создание ключевых связей между вновь созданными таблицами Access, а также создание и выполнение простого запроса в Access для получения данных.
Пример данных в ненормализованной форме
Лист содержит неатомарные значения в столбцах "Продавец" и "Адрес". Оба столбца должны быть разделены на два или несколько отдельных столбцов. Этот лист также содержит сведения о продавцах, товарах, клиентах и заказах. Эту информацию также следует разделить по темам в отдельные таблицы.
| Продавец | Идентификатор заказа | Дата заказа | Код товара | Кол-во | Цена | Имя клиента | Address (Адрес) | Телефон |
|---|---|---|---|---|---|---|---|---|
| Ли, Йель | 2349 | 3/4/09 | С-789 | 3 | $7.00 | Кофейная фабрика | 7007 Корнелл-стрит Редмонд, WA 98199 | 425-555-0201 |
| Ли, Йель | 2349 | 3/4/09 | С-795 | 6 | 9,75 $ | Кофейная фабрика | 7007 Корнелл-стрит Редмонд, WA 98199 | 425-555-0201 |
| Адамс, Эллен | 2350 | 3/4/09 | А-2275 | 2 | $16.75 | Adventure Works | 1025 Columbia Circle Kirkland, WA 98234 | 425-555-0185 |
| Адамс, Эллен | 2350 | 3/4/09 | Ф-198 | 6 | $5.25 | Adventure Works | 1025 Columbia Circle Kirkland, WA 98234 | 425-555-0185 |
| Адамс, Эллен | 2350 | 3/4/09 | В-205 | 1 | $4.50 | Adventure Works | 1025 Columbia Circle Kirkland, WA 98234 | 425-555-0185 |
| Хэнс, Джим | 2351 | 3/4/09 | С-795 | 6 | 9,75 $ | Contoso, Ltd. | 2302 Harvard Ave Bellevue, WA 98227 | 425-555-0222 |
| Хэнс, Джим | 2352 | 3/5/09 | А-2275 | 2 | $16.75 | Adventure Works | 1025 Columbia Circle Kirkland, WA 98234 | 425-555-0185 |
| Хэнс, Джим | 2352 | 3/5/09 | Д-4420 | 3 | 7,25 $ | Adventure Works | 1025 Columbia Circle Kirkland, WA 98234 | 425-555-0185 |
| Кох, Рид | 2353 | 3/7/09 | А-2275 | 6 | $16.75 | Кофейная фабрика | 7007 Корнелл-стрит Редмонд, WA 98199 | 425-555-0201 |
| Кох, Рид | 2353 | 3/7/09 | С-789 | 5 | $7.00 | Кофейная фабрика | 7007 Корнелл-стрит Редмонд, WA 98199 | 425-555-0201 |
Информация в мельчайших частях: атомарные данные
Для работы с данными из этого примера можно использовать команду Текст по столбцу в Excel, чтобы разделить "атомарные" части ячейки (например, почтовый адрес, город, область и почтовый индекс) на отдельные столбцы.
В таблице ниже показаны новые столбцы на том же листе после их разделения на атомарные значения. Обратите внимание, что сведения в столбце "Продавец" разделены на столбцы "Фамилия" и "Имя", а сведения в столбце "Адрес" разделены на столбцы "Адрес", "Город", "Регион" и "Почтовый индекс". Эти данные представлены в "первой нормальной форме".
| Фамилия | Имя | адрес; | Город | Режим | Почтовый индекс |
|---|---|---|---|---|---|
| Ли | Йель | 2302 Гарвард-авеню | Омск | Красноярский край | 98227 |
| Адамс (Adams) | Эллен | 1025 Колумбийский круг | Сочи | Красноярский край | 98234 |
| Хэнс (Hance) | Алексей | 2302 Гарвард-авеню | Омск | Красноярский край | 98227 |
| Кох | Камыш | 7007 Корнелл-стрит Редмонд | Редмонд | Красноярский край | 98199 |
Разбиение данных на упорядоченные темы в Excel
В нескольких приведенных ниже таблицах с примерами данных отображаются одни и те же данные из листа Excel после его разделения на таблицы для продавцов, продуктов, клиентов и заказов. Дизайн таблицы не окончательный, но он на правильном пути.
Таблица "Продавцы" содержит только сведения о сотрудниках по продажам. Обратите внимание, что каждая запись имеет уникальный идентификатор (идентификатор продавца). Значение "ИД продавца" будет использоваться в таблице "Заказы" для связи заказов с продавцами.
| Продавцы | ||
|---|---|---|
| Код продавца | Фамилия | Имя |
| 101 | Ли | Йель |
| 103 | Адамс (Adams) | Эллен |
| 105 | Хэнс (Hance) | Алексей |
| 107 | Кох | Камыш |
Таблица "Товары" содержит только сведения о товарах. Обратите внимание, что каждая запись имеет уникальный идентификатор (код продукта). Значение кода продукта будет использоваться для подключения сведений о продукте к таблице "Сведения о заказе".
| Продукты | |
|---|---|
| Код товара | Цена |
| А-2275 | 16.75 |
| В-205 | 4.50 |
| С-789 | 7.00 |
| С-795 | 9.75 |
| Д-4420 | 7.25 |
| Ф-198 | 5,25 |
Таблица "Клиенты" содержит только сведения о клиентах. Обратите внимание, что у каждой записи есть уникальный идентификатор (код клиента). Значение кода клиента будет использоваться для подключения сведений о клиентах к таблице "Заказы".
| Customers | ||||||
|---|---|---|---|---|---|---|
| Код клиента | Имя | адрес; | Город | Режим | Почтовый индекс | Телефон |
| 1001 | Contoso, Ltd. | 2302 Гарвард-авеню | Омск | Красноярский край | 98227 | 425-555-0222 |
| 1003 | Adventure Works | 1025 Колумбийский круг | Сочи | Красноярский край | 98234 | 425-555-0185 |
| 1005 | Кофейная фабрика | 7007 Корнелл-стрит | Редмонд | Красноярский край | 98199 | 425-555-0201 |
Таблица "Заказы" содержит сведения о заказах, продавцах, клиентах и товарах. Обратите внимание, что каждая запись имеет уникальный идентификатор (идентификатор заказа). Часть информации из этой таблицы необходимо выделить в дополнительную таблицу со сведениями о заказах, чтобы она содержала только четыре столбца: уникальный код заказа, дату заказа, код продавца и код клиента. Приведенная здесь таблица еще не разделена на таблицу "Сведения о заказах".
| Заказы | |||||
|---|---|---|---|---|---|
| Идентификатор заказа | Дата заказа | Идентификатор продавца | Код клиента | Код товара | Кол-во |
| 2349 | 3/4/09 | 101 | 1005 | С-789 | 3 |
| 2349 | 3/4/09 | 101 | 1005 | С-795 | 6 |
| 2350 | 3/4/09 | 103 | 1003 | А-2275 | 2 |
| 2350 | 3/4/09 | 103 | 1003 | Ф-198 | 6 |
| 2350 | 3/4/09 | 103 | 1003 | В-205 | 1 |
| 2351 | 3/4/09 | 105 | 1001 | С-795 | 6 |
| 2352 | 3/5/09 | 105 | 1003 | А-2275 | 2 |
| 2352 | 3/5/09 | 105 | 1003 | Д-4420 | 3 |
| 2353 | 3/7/09 | 107 | 1005 | А-2275 | 6 |
| 2353 | 3/7/09 | 107 | 1005 | С-789 | 5 |
Сведения о заказе, такие как код продукта и количество, переносятся из таблицы "Заказы" и сохраняются в таблице "Сведения о заказах". Имейте в виду, что в таблице 9 заказов, поэтому имеет смысл, что в этой таблице 9 записей. Обратите внимание, что таблица "Заказы" имеет уникальный идентификатор (ИД заказа), на который будет ссылаться таблица "Сведения о заказах".
Окончательный вид таблицы "Заказы" должен выглядеть следующим образом:
| Заказы | |||
|---|---|---|---|
| Идентификатор заказа | Дата заказа | Идентификатор продавца | Код клиента |
| 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 три изделия С-789 составляют одну из двух частей всего заказа.
Поэтому таблица "Сведения о заказе" должна выглядеть следующим образом:
| Сведения о заказе | ||
|---|---|---|
| Код заказа | Код товара | Кол-во |
| 2349 | С-789 | 3 |
| 2349 | С-795 | 6 |
| 2350 | А-2275 | 2 |
| 2350 | Ф-198 | 6 |
| 2350 | В-205 | 1 |
| 2351 | С-795 | 6 |
| 2352 | А-2275 | 2 |
| 2352 | Д-4420 | 3 |
| 2353 | А-2275 | 6 |
| 2353 | С-789 | 5 |
Копирование и вставка данных из Excel в Access
Теперь, когда сведения о продавцах, клиентах, товарах, заказах и сведениях о заказах разбиты на отдельные темы в Excel, их можно скопировать непосредственно в Access, где они преобразуются в таблицы.
Создание связей между таблицами Access и выполнение запроса
После перемещения данных в Access можно создать связи между таблицами, а затем создать запросы для возврата сведений по различным темам. Например, можно создать запрос, возвращающий код заказа и имена продавцов для заказов, введенных в период с 05.03.09 по 08.03.09.
Кроме того, можно создавать формы и отчеты, упрощающие ввод данных и анализ продаж.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.