Переміщення даних із програми Excel до Access

У цій статті описано, як перенести дані з Excel, щоб отримати доступ до даних і перетворити їх на реляційні таблиці, щоб можна було використовувати програму Microsoft Excel і Access разом. Щоб підсумувати, програма Access якнайкраще підходить для захоплення, зберігання, створення запитів і спільного доступу до даних, а Excel – оптимальний для обчислення, аналізу та візуалізації даних.

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

Під час переміщення даних із програми Excel до Access є три основні кроки для процесу.

three basic steps

Примітка.: Відомості про моделювання та зв'язки даних у програмі Access наведено в статті основні відомості про Конструктор баз даних.

Крок 1: імпорт даних із програми Excel до Access

Імпорт даних – це операція, яка дає змогу значно легше працювати, якщо ви Приділіть час для підготовки та очищення даних. Імпорт даних подібне до переходу до нового будинку. Якщо ви очистите та організовуєте своє майно перед тим, як ви рухаєтеся, заселення нового будинку набагато простіше.

Очищення даних перед імпортуванням

Перш ніж імпортувати дані в Access, у програмі Excel рекомендовано:

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

  • Використовуйте команду "ОБРІЗАТИ", щоб видалити інтерліньяжу, кінцеву та кілька вбудованих пробілів.

  • Видалення недрукованих символів.

  • Знаходьте та виправте орфографічні та розділові помилки.

  • Видалення повторюваних рядків або повторюваних полів.

  • Переконайтеся, що стовпці даних не містять мішаних форматів, зокрема числа, відформатовані як текст або дати, відформатовані як числа.

Щоб отримати докладніші відомості, ознайомтеся з такими темами довідки Excel:

Примітка.: Якщо ваші потреби очищення даних є складними, або ви не маєте часу або ресурсів, щоб автоматизувати процес самостійно, ви можете скористатися постачальником сторонніх постачальників. Щоб отримати докладніші відомості, виконайте пошук у веб-браузері за допомогою функції "очищення даних" або "якість даних" на вашому улюбленому пошуковому комп'ютері.

Вибір найкращого типу даних під час імпорту

Під час операції імпорту в програмі Access потрібно зробити правильний вибір, щоб отримати кілька (якщо такі є) помилки перетворення, які потребують ручного втручання. У наведеній нижче таблиці стисло описано, як у програмі Excel можна перетворити формати номерів Excel і типи даних Access, коли ви імпортуєте дані з Access, і в майстрі імпорту електронних таблиць буде запропоновано кілька порад.

Формат чисел у програмі Excel

Тип даних Access

Примітки

Рекомендації

Текст

Текст, нотатка

Тип даних "текст Access" зберігає буквено-цифрові дані до 255 символів. Тип даних "Нотатка Access" зберігає буквено-цифрові дані до 65 535 символів.

Виберіть пункт нотатка , щоб уникнути скорочення даних.

Число, відсоток, дріб, науковий

число

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

Натисніть кнопку " вдвічі ", щоб уникнути помилок перетворення даних.

Дата

Date

Щоб зберігати дати, у програмі Access і програмі Excel можна використовувати однаковий числове значення дати. У програмі Access діапазон дат більший: від-657 434 (1 січня 100 р.) до 2 958 465 (31 грудня 9999 р.).

Оскільки програма Access не розпізнає систему Date 1904 (використовується в Excel для Macintosh), потрібно перетворити дати в програмі Excel або Access, щоб уникнути плутанини.

Докладні відомості наведено в статті змінення системи дат, формату або двозначного трактування року , а також Імпорт або зв'язування з даними в книзі Excel.

Виберіть пункт дата.

Час

Час

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

Виберіть час, який зазвичай використовується за замовчуванням.

Грошова одиниця, бухгалтерський облік

Грошова одиниця

У програмі Access тип даних "грошова одиниця" зберігає дані як 8-байтні числа з точністю до чотирьох десяткових розрядів, і використовується для зберігання фінансових даних і запобігання округленню значень.

Виберіть грошовийсимвол, який зазвичай використовується за замовчуванням.

Логічне значення

Так/Ні

У програмі Access використовується – 1 для всіх значень YES і 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) (розширення імені файлу ODC) або файл імені джерела даних (розширення DSN). Після підключення до зовнішніх даних можна також автоматично оновлювати (або оновлювати) книгу Excel у програмі Access щоразу, коли дані оновлюються в програмі Access.

Докладні відомості наведено в статті Імпорт даних із зовнішніх джерел даних (Power Query).

Отримання даних у програмі Access

У цьому розділі ви пройдете кілька етапів нормалізації даних: розрив значень у стовпцях "продавець" і "адреса" у своїх самих атомних частинах, відокремлення пов'язаних тем у власні таблиці, копіювання та вставлення цих таблиць із програми Excel до Access, створюючи ключові зв'язки між щойно створеними таблицями Access і створюючи та запустивши простий запит у програмі Access, щоб повернути відомості.

Приклад даних у ненормалізованої формі

Наведений нижче аркуш містить неатомні значення в стовпці "продавець" і стовпці "адреса". Обидва стовпці мають бути розділені на два або кілька окремих стовпців. На цьому аркуші також містяться відомості про продавці, продукти, клієнти та замовлення. Ці відомості також повинні бути розділені далі, за темою, у окремі таблиці.

Продавець

Ідентифікатор замовлення

Дата замовлення

Ідентифікатор товару;

Кількість

Ціна

Ім'я клієнта

Адреса

Телефон

Лі, Єльський

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

Пригодницькі роботи

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Адамс, Еллен

2350

3/4/09

F-198

6

$5,25

Пригодницькі роботи

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Адамс, Еллен

2350

3/4/09

B-205

1

$4,50

Пригодницькі роботи

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Hance, Джим

2351

3/4/09

C-795

6

$9,75

Contoso Ltd.

2302 Гарвардський пр-т Бельвю, WA 98227

425-555-0222

Hance, Джим

2352

3/5/09

A-2275

2

$16,75

Пригодницькі роботи

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Hance, Джим

2352

3/5/09

D-4420

3

$7,25

Пригодницькі роботи

1025 Columbia Circle Kirkland, 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, щоб відокремити "атомні" частини (наприклад, адресу вулиці, місто, стан і поштовий індекс) у окремі стовпці.

У наведеній нижче таблиці показано нові стовпці на тому самому аркуші після розділення, щоб зробити всі значення атомними. Зверніть увагу, що інформація в стовпці Продавець була розділена на прізвище та стовпці "ім'я", а відомості в стовпці "адреса" розділено на стовпці "адреса", "місто", "стан" і "поштовий індекс". Ці дані наведено в "першу звичайну форму".

Прізвище

Ім’я

 

Вулиця

Місто

Область

Поштовий індекс

Лі

Єльського

Пр-т Гарвардський 2302

Львів

Дніпропетровська

98227

Колесник

Еллен

1025 Columbia Circle

Kirkland

Дніпропетровська

98234

Попович

Антон

Пр-т Гарвардський 2302

Львів

Дніпропетровська

98227

Кох

Рід

7007 Корнелл-Сент-Редмонд

Redmond

Дніпропетровська

98199

Порушення даних у організованих темах у програмі Excel

Кілька таблиць прикладів даних, які слідують за цими даними з аркуша Excel після розділення на таблиці для продавців, продуктів, клієнтів і замовлень. Макет таблиці не є остаточною, але вона міститься в правильному напрямі.

У таблиці "продавці" містяться лише відомості про персонал відділу збуту. Зверніть увагу, що кожен запис має унікальний ІДЕНТИФІКАТОР (ІДЕНТИФІКАТОР продавця). Значення ІДЕНТИФІКАТОРА продавця буде використано в таблиці "замовлення" для підключення замовлень до продавців.

Продавці

ІДЕНТИФІКАТОР продавця

Прізвище

Ім’я

101

Лі

Єльського

103

Колесник

Еллен

105

Попович

Антон

107

Кох

Рід

У таблиці "Товари" містяться лише відомості про продукти. Зверніть увагу, що кожен запис має унікальний ІДЕНТИФІКАТОР (ІДЕНТИФІКАТОР продукту). Значення ІДЕНТИФІКАТОРА продукту використовуватиметься для підключення відомостей про продукт до таблиці "відомості про замовлення".

Продуктів

Ідентифікатор товару;

Ціна

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

Ідентифікатор замовника

Назва

Вулиця

Місто

Область

Поштовий індекс

Телефон

1001

Contoso Ltd.

Пр-т Гарвардський 2302

Львів

Дніпропетровська

98227

425-555-0222

1003

Пригодницькі роботи

1025 Columbia Circle

Kirkland

Дніпропетровська

98234

425-555-0185

1005

Наша кава

7007 Cornell St

Redmond

Дніпропетровська

98199

425-555-0201

У таблиці "замовлення" містяться відомості про замовлення, продавці, клієнти та продукти. Зверніть увагу, що кожен запис має унікальний ІДЕНТИФІКАТОР (ІДЕНТИФІКАТОР замовлення). Деякі відомості в цій таблиці повинні бути розділені на додаткову таблицю, яка містить відомості про замовлення, щоб таблиця "замовлення" містить лише чотири стовпці – унікальний ІДЕНТИФІКАТОР замовлення, дату замовлення, ІДЕНТИФІКАТОР продавця та ІДЕНТИФІКАТОР клієнта. Таблиця, показана тут, ще не була розділена на таблицю відомостей про замовлення.

Orders

Ідентифікатор замовлення

Дата замовлення

ІДЕНТИФІКАТОР продавця

Customer ID (Ідентифікатор клієнта)

Ідентифікатор товару;

Кількість

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

Ідентифікатор замовлення

Дата замовлення

ІДЕНТИФІКАТОР продавця

Customer ID (Ідентифікатор клієнта)

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 складаються з однієї з двох частин всього порядку.

Тому таблиця "відомості про замовлення" має такий вигляд:

Відомості про замовлення

Order ID (Ідентифікатор замовлення)

Ідентифікатор товару;

Кількість

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 можна створити зв'язки між таблицями, а потім створити запити, щоб повернути відомості про різні теми. Наприклад, можна створити запит, який повертає ІДЕНТИФІКАТОР замовлення та імена продавців для замовлень, введених між 3/05/09 і 3/08/09.

Крім того, ви можете створювати форми та звіти, щоб спростити процес введення даних і аналіз збуту.

Потрібна додаткова довідка?

Ви завжди можете поставити запитання експерту в спільноті Tech (у розділі Excel), отримати підтримку в спільноті, що допомагає знайти відповіді на запитання, або запропонувати нову функцію чи вдосконалення на форумі Excel User Voice.

Удосконалення навичок роботи з Office
Ознайомтеся з навчальними матеріалами
Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо, що знайшли час і надіслали нам відгук! Можливо, у нас не буде часу відповісти на кожен коментар, але докладемо максимум зусиль, щоб переглянути їх усі. Вас цікавить, як ми використовуємо ваші відгуки?

×