Імпортування та зв’язування з даними у книзі Excel

Імпортування та зв’язування з даними у книзі Excel

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

У цій статті детально описано процес імпорту або зв’язування з даними Excel у настільній базі даних Access.

Примітки : 

  • Під час роботи з веб-базою даних Access 2010 у браузері імпортувати дані не можна. Щоб імпортувати дані до веб-бази даних, спочатку потрібно відкрити її у програмі Access.

  • Зв’язані таблиці несумісні з веб-базами даних Access 2010. Щоб використовувати зовнішні дані у веб-базі даних, потрібно імпортувати дані, а не зв’язувати їх. Докладні відомості про веб-бази даних див. у статті Створення бази даних Access для спільного використання в Інтернеті.

Виберіть дію

Загальні відомості про імпорт даних з Excel

Імпорт даних з Excel

Усунення несправностей із відсутніми або неправильними значеннями

Зв’язування з даними в Excel

Усунення помилки #Num! та інших неправильних значень у зв’язаній таблиці

Загальні відомості про імпорт даних з Excel

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

Типові сценарії імпортування даних Excel до Access

  • Ви досвідчений користувач Excel, але для більш ефективної роботи з даними вам потрібно користуватись Access. Можна перемістити дані з аркушів Excel до однієї або кількох нових баз даних Access.

  • Відділ або робоча група використовує Access, але час від часу отримує дані у форматі Excel, які потрібно вносити до баз даних Access. Можна імпортувати аркуші Excel до бази даних у міру їх отримання.

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

Якщо імпортування даних з Excel здійснюється вперше

  • Не можна зберегти книгу Excel як базу даних Access. Excel не підтримує можливості створення бази даних Access на основі даних Excel.

  • Якщо книгу Excel відкрити у програмі Access (для цього в діалоговому вікні Файл > Відкрити потрібно у списку Тип файлів вибрати значення Файли Microsoft Office Excel і вибрати потрібний файл). Буде створено посилання на книгу, але дані не буде імпортовано. Посилання на книгу суттєво відрізняється від імпортування аркуша до бази даних. Докладні відомості про посилання див. в розділі Зв’язування з даними в Excel далі в цій статті.

Імпорт даних з Excel

У цьому розділі описано, як підготуватися до операції імпортування та запустити її, а також як зберегти параметри імпорту як специфікацію для подальшого повторного використання. Слід пам’ятати, що можна одночасно імпортувати дані лише з одного аркуша. Не можна відразу імпортувати всі дані з усієї книги.

Підготовка аркуша

  1. Знайдіть вихідний файл і виберіть аркуш із даними, які потрібно імпортувати до Access. Якщо потрібно імпортувати лише частину аркуша, можна визначити іменований діапазон, що містить лише клітинки, які потрібно імпортувати.

    Визначення іменованого діапазону (необов’язково)   

    1. Перейдіть до файлу Excel і відкрийте аркуш із даними, які потрібно імпортувати.

    2. Виділіть діапазон клітинок із даними, які потрібно імпортувати.

    3. Клацніть правою кнопкою миші виділений діапазон і виберіть команду Назвати діапазон або Визначити ім’я.

    4. У діалоговому вікні Нове ім’я вкажіть ім’я діапазону в полі Ім’я та натисніть кнопку ОК.

      Пам’ятайте, що за раз можна імпортувати лише один аркуш. Щоб імпортувати дані з кількох аркушів, потрібно повторити операцію імпорту для кожного з них.

  2. Перегляньте вихідні дані та виконайте дії згідно з наведеною нижче таблицею.

    Елемент

    Опис

    Кількість стовпців

    Кількість вихідних стовпців, які потрібно імпортувати, не може перевищувати 255, позаяк Access підтримує не більше 255 полів у таблиці.

    Пропускання стовпців і рядків

    У вихідний аркуш або іменований діапазон рекомендовано включати лише рядки та стовпці, які потрібно імпортувати.

    Рядки    Під час операції імпортування не можна фільтрувати або пропускати рядки.

    Стовпці    Під час імпортування не можна пропускати стовпці, якщо дані додаються до наявної таблиці.

    Табличний формат

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

    Пусті стовпці, рядки та клітинки

    Видаліть усі непотрібні пусті стовпці та рядки з аркуша або діапазону. Якщо аркуш або діапазон містить пусті клітинки, додайте відсутні дані. Якщо заплановано додати записи до наявної таблиці, переконайтеся, що відповідне поле в таблиці приймає пусті (відсутні або невідомі) значення. Поле прийматиме пусті значення, якщо його властивість Обов’язково (Required) має значення Ні, а властивість Правило перевірки (ValidationRule) не забороняє пустих значень.

    Значення помилок

    Якщо одна або кілька клітинок в аркуші або діапазоні містять значення помилок, наприклад #NUM і #DIV, виправте ці значення, перш ніж починати імпортування. Якщо вихідний аркуш або діапазон містить значення помилок, у відповідному полі в таблиці у програмі Access буде розміщено пусте значення. Докладні відомості про виправлення цих помилок див. в розділі Виправлення неполадок із відсутніми або неправильними значеннями далі в цій статті.

    Тип даних

    Щоб уникнути помилок під час імпортування, переконайтеся, що кожен вихідний стовпець містить дані одного типу в усіх рядках. Програма Access перевірить перші вісім вихідних рядків, щоб визначити тип даних полів у таблиці. Рекомендовано перевірити, чи перші вісім вихідних рядків не мають значень різних типів даних у всіх стовпцях. В іншому разі програма Access може не призначити правильний тип даних стовпцю.

    Також перед операцією імпортування рекомендовано відформатувати всі вихідні стовпці у програмі Excel і призначити певний формат даних кожному з них. Форматування рекомендовано виконати, якщо стовпець включає значення різних типів даних. Наприклад, стовпець «Номер рейсу» може містити числові та текстові значення, наприклад, 871, AA90 і 171. Щоб уникнути відсутніх або неправильних значень, виконайте такі дії:

    1. Клацніть заголовок стовпця правою кнопкою миші та виберіть елемент Формат клітинок.

    2. На вкладці Число в області Числові формати виберіть формат. Для стовпця «Номер рейсу» найкраще вибрати варіант Текстовий.

    3. Натисніть кнопку ОК.

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

    Перший рядок

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

    Примітка : Якщо заплановано додати дані до наявної таблиці, переконайтеся, що ім’я кожного стовпця збігається з іменем відповідного поля. Якщо ім’я стовпця відрізняється від імені відповідного поля в таблиці, операцію імпортування не буде виконано. Щоб переглянути імена полів, відкрийте таблицю в режимі конструктора в Access.

  3. Закрийте вихідну книгу, якщо її відкрито. Якщо вихідний файл залишатиметься відкритим, під час операції імпортування можуть виникнути помилки перетворення даних.

Підготовка цільової бази даних

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

    -або-

    Якщо дані не потрібно зберігати в жодній наявній базі даних, створіть пусту базу даних. Для цього:

    На вкладці Файл виберіть команду Створити та натисніть кнопку Нова база даних. Зверніть увагу: якщо ви використовуєте Access 2007, натисніть кнопку Microsoft Office, а потім клацніть Створити.

  2. Перед початком операції імпортування визначте, чи дані потрібно зберегти в новій, чи в наявній таблиці.

    Створення нової таблиці    Якщо дані потрібно зберегти в новій таблиці, в Access буде створено таблицю та додано імпортовані дані до неї. Якщо таблиця з указаним іменем уже існує, в Access буде перезаписано вміст таблиці імпортованими даними.

    Додавання до наявної таблиці    Якщо дані потрібно додати до наявної таблиці, рядки аркуша Excel буде додано до вказаної таблиці.

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

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

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

      Порада : Поле прийматиме пусті значення, якщо його властивість Обов’язково має значення Ні, а властивість Правило перевірки не забороняє пустих значень.

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

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

      Для запуску операції імпортування перейдіть до наступних етапів.

Запуск операції імпортування

  1. Залежно від версії Access розташування майстра імпорту та зв’язування може дещо відрізнятися. Виконайте дії для своєї версії Access:

    • Якщо ви використовуєте Access останньої версії за передплатою на Office 365, на вкладці Зовнішні дані в групі Імпорт і зв’язування виберіть Нове джерело даних > З файлу > Excel.

    • Якщо ви використовуєте Access 2016, Access 2013 або Access 2010, на вкладці Зовнішні дані в групі Імпорт і зв’язування натисніть кнопку Excel.

    • Якщо ви використовуєте Access 2007, на вкладці Зовнішні дані в групі Імпорт натисніть кнопку Excel.

    Примітка : Вкладка Зовнішні дані доступна, лише якщо відкрито базу даних.

  2. У діалоговому вікні Отримати зовнішні дані - Таблиця Excel у полі Ім’я файлу вкажіть ім’я файлу Excel із даними, які потрібно імпортувати.

    -або-

    Натисніть кнопку Огляд і за допомогою діалогового вікна Відкриття файлу знайдіть файл, який потрібно імпортувати.

  3. Укажіть спосіб зберігання імпортованих даних.

    Щоб зберегти дані в новій таблиці, виберіть варіант Імпортувати дані джерела до нової таблиці в поточній базі даних. Пізніше буде запропоновано присвоїти цій таблиці ім’я.

    Щоб додати дані до наявної таблиці, виберіть варіант Додати копію записів до таблиці та виберіть таблицю з розкривного списку. Цей параметр недоступний, якщо база даних не містить таблиць.

    Щоб підключитися до джерела даних, створивши зв’язану таблицю, див. розділ Зв’язування з даними в Excel далі в цій статті.

  4. Натисніть кнопку ОК.

    Буде запущено майстер імпорту електронних таблиць, який допоможе здійснити операцію імпортування. Перейдіть до наступних кроків.

Використання майстра імпорту електронних таблиць

  1. На першій сторінці майстра виберіть аркуш із даними, які потрібно імпортувати, відтак натисніть кнопку Далі.

  2. На другій сторінці майстра виберіть варіант Показати робочі аркуші або Показати іменовані діапазони, виділіть аркуш або іменований діапазон, який потрібно імпортувати, відтак натисніть кнопку Далі.

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

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

    Якщо дані додаються до наявної таблиці, відразу перейдіть до кроку 6. Якщо дані додаються до нової таблиці, виконайте інші дії.

  4. Майстер запропонує переглянути властивості полів. Для відображення властивостей відповідного поля клацніть стовпець у нижній частині сторінки. За потреби виконайте будь-яку з таких дій:

    • За потреби перегляньте та змініть ім’я та тип даних цільового поля.

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

    • Щоб створити індекс для поля виберіть для параметра Індексовано значення Так.

    • Щоб повністю пропустити вихідний стовпець, установіть прапорець Не імпортувати поле (Пропустити).

      Після завершення вибору параметрів натисніть кнопку Далі.

  5. На наступній сторінці вкажіть первинний ключ для таблиці. Якщо вибрано варіант Дозволити Access додати первинний ключ, як перше поле в цільовій таблиці буде додано поле автонумерації, яке автоматично заповниться унікальними значеннями ідентифікаторів, починаючи з 1. Натисніть кнопку Далі.

  6. На останній сторінці майстра вкажіть ім’я таблиці призначення. У полі Імпортувати до таблиці введіть ім’я таблиці. Якщо таблиця з таким іменем уже існує, програма Access запропонує перезаписати наявний вміст таблиці. Натисніть кнопку Так, щоб продовжити, або кнопку Ні, щоб указати інше ім’я для таблиці призначення. Відтак натисніть кнопку Готово, щоб імпортувати дані.

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

  7. Натисніть кнопку Так, щоб зберегти відомості про операцію для подальшого використання. Збереження відомостей допомагає надалі виконувати операцію імпортування без повторного виконання кроків майстра.

Щоб дізнатися, як зберегти специфікацію, див. статтю Збереження відомостей про операцію імпорту або експорту у вигляді специфікації.

Щоб дізнатися, як запустити збережену специфікацію імпорту або зв’язування, див. статтю Запуск збереженої специфікації імпорту або експорту.

Щоб дізнатися, як запланувати завдання імпорту або зв’язування на визначений час, див. статтю Планування специфікації імпорту або експорту.

Усунення несправностей із відсутніми або неправильними значеннями

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

Увага! : Навіть якщо повідомлення стану вказує, що операцію успішно завершено, перед використанням таблиці слід переглянути її вміст і структуру, щоб переконатись у правильності всіх даних.

  • Відкрийте таблицю призначення у вікні табличного подання даних і переконайтеся, що до таблиці додано всі дані.

  • Відкрийте таблицю в режимі конструктора, щоб перевірити тип даних та інші настройки властивостей полів.

У таблиці нижче описано кроки, які потрібно виконати, щоб виправити помилки, пов’язані з відсутніми або хибними значеннями.

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

Проблема

Вирішення

Графічні елементи

Графічні елементи, наприклад, емблеми, схеми та рисунки, не можна імпортувати. Вручну додайте їх до бази даних після завершення операції імпортування.

Обчислювані значення

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

Значення TRUE або FALSE і -1 або 0

Якщо вихідний аркуш або діапазон включає стовпець, який містить лише значення TRUE або FALSE, для стовпця буде створено поле «Так/Ні», у яке вставляється значення -1 або 0. Однак, якщо вихідний аркуш або діапазон включає стовпець, який містить лише значення -1 або 0, за промовчанням для стовпця буде створено числове поле. Для вирішення цієї проблеми під час операції імпортування змініть тип даних поля на «Так/Ні».

Багатозначні поля

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

Скорочені дані

Якщо дані у стовпці таблиці обітнуто, спробуйте збільшити ширину стовпця в поданні таблиці. Якщо це не вирішує проблеми, обсяг даних у числовому стовпці Excel завеликий для цільового поля в Access. Наприклад, властивість цільового поля Розмір поля (FieldSize) може мати значення Байт у базі даних Access, але вихідні дані містять значення більше 255. Виправте значення у вихідному файлі та повторіть спробу імпортування.

Формат відображення

Може знадобитися встановити властивість певних полів Формат (Format) у режимі конструктора, щоб переконатися, що в поданні таблиці правильно відображено дані. Наприклад:

  • Після завершення операції імпортування поле «Так/Ні» відображає значення -1 і 0 у поданні таблиці. Щоб виправити цю помилку, після завершення операції імпортування змініть значення властивості поля Формат (Format) на Так/Ні для відображення прапорців.

  • Довгий і середній формат дати в Access може відобразитися як короткий формат. Щоб виправити цю помилку, відкрийте цільову таблицю в режимі конструктора в Access і змініть властивість поля дати Формат (Format) на Довгий формат дати або Середній формат дати.

Примітка : Якщо вихідний аркуш містить форматування тексту у форматі RTF, наприклад, жирний шрифт, підкреслення або курсив, текст імпортується, але форматування буде втрачено.

Повторювані значення (порушення унікальності ключа)

Імпортовані записи можуть містити повторювані значення, які не можна зберігати в полі первинного ключа цільової таблиці або в полі, властивість якого Індексовано має значення Так (Без повторень). Видаліть повторювані значення у вихідному файлі та повторіть спробу імпортування.

Зсув значень дат на 4 роки

Значення полів даних, які імпортуються з аркуша Excel, можуть зсунутися на чотири роки. В Excel для Windows використовується система дат 1900 (у якій дати представлено порядковими номерами від 1 до 65 380), яка відповідає датам від 1 січня 1900 р. до 31 грудня 2078 р. Однак, в Excel для Macintosh використовується система дат 1904 (у якій дати представлено порядковими номерами від 0 до 63 918), яка відповідає датам від 1 січня 1904 р. до 31 грудня 2078 р.

Перед імпортуванням даних змініть систему дат для книги Excel або після додавання даних виконайте запит на оновлення з використанням виразу [ім’я поля дати] + 1462 для виправлення дат.

Пусті значення

Після завершення операції імпортування може відобразитися повідомлення про помилку зі списком даних, які було видалено або втрачено під час операції. Також під час відкриття таблиці в поданні таблиці можна виявити пусті значення полів. Якщо вихідні стовпці в Excel не відформатовано, або перші вісім вихідних рядків містять значення різних типів даних, відкрийте вихідний аркуш і виконайте такі дії:

  • Відформатуйте вихідні стовпці.

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

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

Описані вище кроки дають змогу звести до мінімуму ймовірність того, що в таблиці з’являться значення Null. У таблиці нижче перелічено випадки, коли можуть з’явитися значення Null:

Тип відсутніх значень...

Під час імпорту в...

Тип цільового поля...

Щоб вирішити...

Текст

Нова таблиця

Дата

Замініть усі текстові значення значеннями дати та повторіть спробу імпортування.

Текст

Наявна таблиця

Числове поле або поле дати

Замініть усі текстові значення значеннями, які збігаються з типом даних цільового поля, відтак повторіть спробу імпортування.

Значення дат замінено числовими значеннями

Випадкові п’ятизначні числа відображаються замість фактичних значень дат у таких випадках:

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

  • Вихідний стовпець містить у деяких із перших восьми рядків значення дат, і було здійснено спробу його імпортування до числового поля. Ці значення дат буде перетворено неправильно.

    Щоб уникнути цієї проблеми, замініть у вихідному стовпці значення дат числовими значеннями і повторіть спробу імпортування.

    Іноді, якщо стовпець, який містить здебільшого значення дат, містить кілька текстових значень, усі значення дат можуть відображатися як випадкові п’ятизначні числа. Щоб уникнути цього, замініть текстові значення на значення дат і повторіть імпортування.

Числові значення замінено значеннями дат

Випадкові значення дат відображаються замість фактичних числових значень у таких випадках:

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

  • Вихідний стовпець містить у деяких із перших восьми рядків числові значення, і було здійснено спробу його імпортування до поля дати. Ці числові значення буде перетворено неправильно.

Щоб уникнути цієї проблеми, замініть у вихідному стовпці числові значення значеннями дат і повторіть спробу імпортування.

Крім того, може знадобитися переглянути таблицю журналу помилок (про яку зазначено на останній сторінці майстра) в поданні таблиці. Ця таблиця складається із трьох полів  — «Помилка», «Поле» та «Рядок». Кожен рядок містить відомості про певну помилку, а вміст поля «Помилка» допоможе виправити неполадки.

Рядки з помилками та поради щодо їх виправлення

Помилка

Опис

Скорочення поля

Значення в полі завелике для властивості поля Розмір поля (FieldSize).

Помилка перетворення типу

Значення на аркуші має неправильний тип даних для поля. Значення може бути відсутнім або неправильно відображатися в цільовому полі. Відомості про виправлення цієї неполадки див. у попередній таблиці.

Порушення ключа

Значення первинного ключа запису повторює  вже наявне в таблиці значення.

Порушення правила перевірки

Значення порушує правило, установлене властивістю Правило перевірки (ValidationRule) для поля або таблиці.

Пусте значення в обов’язковому полі

Пусте значення заборонено для цього поля, позаяк властивість поля Обов’язково (Required) має значення Так.

Пусте значення в полі автонумерації

Імпортовані дані містять значення Null. Було здійснено спробу додавання цього значення до поля автонумерації.

Запис, який не можна проаналізувати

Текстове значення містить символ роздільника тексту (зазвичай подвійні лапки). Якщо значення містить символ роздільника, символ необхідно двічі включити в текстовий файл; наприклад:

діаметр 4,5""

На початок сторінки

Зв’язування з даними в Excel

Зв’язавши базу даних Access з даними іншої програми, можна скористатися наявними в Access засобами створення запитів і звітів, причому не потрібно зберігати копію даних Excel у базі даних.

Під час створення зв’язку з аркушем або іменованим діапазоном Excel у програмі Access створюється нова таблиця, зв’язана з вихідними клітинками. Будь-які зміни у вихідних клітинках Excel відображатимуться у зв’язаній таблиці. Однак, у програмі Access не можна змінити вміст відповідної таблиці. Додавати, редагувати або видаляти дані можна лише у вихідному файлі.

Типові сценарії зв’язування з аркушем Excel в Access

Зазвичай зв’язок (на противагу імпортуванню) з аркушем Excel створюється з таких причин:

  • Потрібно, продовжуючи зберігати дані в Excel, скористатися потужними засобами створення запитів і звітів Access.

  • Відділ або робоча група використовує Access, але дані з зовнішніх джерел надходять у вигляді аркушів Excel. Потрібно, не зберігаючи копії зовнішніх даних, забезпечити роботу з ними в Access.

Якщо зв’язування з аркушем Excel здійснюється вперше

  • У програмі Excel не можна створити зв’язок із базою даних Access.

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

  • Не можна зв’язати дані Excel із наявною таблицею в базі даних. Це означає, що за допомогою операції зв’язування не можна додати дані до наявної таблиці.

  • База даних може містити кілька зв’язаних таблиць.

  • Будь-які зміни в даних Excel автоматично відображаються у зв’язаній таблиці. Однак, вміст і структура зв’язаної таблиці Access доступні лише для читання.

  • Якщо книгу Excel відкрито у програмі Access (для цього в діалоговому вікні Відкриття файлу слід вибрати у списку Тип файлів пункт Microsoft Excel і вказати потрібний файл), буде створено пусту базу даних і автоматично запущено майстер зв’язування електронних таблиць.

Підготовка даних Excel

  1. Знайдіть файл Excel і аркуш або діапазон із даними, з якими потрібно створити зв’язок. Якщо не потрібно створити зв’язок з усім аркушем, можна визначити іменований діапазон, який включає лише потрібні клітинки.

    Створення іменованого діапазону у програмі Excel (необов’язково, але корисно, якщо посилання потрібно створити лише до деяких даних на аркуші)   

    1. Перейдіть до Excel і відкрийте аркуш, у якому потрібно визначити іменований діапазон.

    2. Виділіть діапазон клітинок із даними, з якими потрібно зв’язатися.

    3. Клацніть правою кнопкою миші виділений діапазон і виберіть команду Назвати діапазон або Визначити ім’я.

    4. У діалоговому вікні Нове ім’я вкажіть ім’я діапазону в полі Ім’я та натисніть кнопку ОК.

      Зверніть увагу, що під час операції зв’язування одночасно можна створити зв’язок лише з одним аркушем або діапазоном. Щоб зв’язатися з кількома розташуваннями у книзі, повторіть операцію зв’язування для кожного аркуша або діапазону.

  2. Перегляньте вихідні дані та виконайте дії згідно з наведеною нижче таблицею.

    Елемент

    Опис

    Табличний формат

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

    Пропускання стовпців і рядків

    Під час операції зв’язування не можна пропускати вихідні стовпці та рядки. Однак, можна приховати поля та відфільтрувати записи, відкривши зв’язану таблицю в поданні таблиці після імпорту записів до Access.

    Кількість стовпців

    Кількість вихідних стовпців не може перевищувати 255, позаяк Access підтримує не більше 255 полів у таблиці.

    Пусті стовпці, рядки та клітинки

    Видаліть усі непотрібні пусті стовпці та рядки з аркуша або діапазону Excel. За наявності пустих клітинок спробуйте додати відсутні дані.

    Значення помилок

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

    Тип даних

    Не можна змінити тип даних або розмір полів у зв’язаній таблиці. Перед початком операції зв’язування необхідно перевірити, чи кожен стовпець містить дані певного типу.

    Рекомендовано відформатувати стовпець, якщо він містить значення різних типів даних. Наприклад, стовпець «Номер рейсу» може містити числові та текстові значення, наприклад, 871, AA90 і 171. Щоб уникнути відсутніх або неправильних значень, виконайте такі дії:

    1. Клацніть стовпець правою кнопкою миші та виберіть пункт Формат клітинок.

    2. На вкладці Число в області Числові формати виберіть формат.

    3. Натисніть кнопку ОК.

    Перший рядок

    Якщо перший рядок аркуша або іменованого діапазону містить імена стовпців, в Access можна вказати, що під час операції зв’язування дані в першому рядку мають сприйматися як імена полів. Якщо на аркуші немає імен стовпців, або якщо певним іменем стовпця порушено правила іменування полів в Access, усім відповідним полям буде призначено припустиме ім’я.

  3. Закрийте вихідний файл, якщо його відкрито.

Підготовка цільової бази даних

  1. Відкрийте базу даних, у якій потрібно створити зв’язок. Переконайтеся, що цю базу даних не призначено лише для читання, і що ви маєте необхідні дозволи на внесення змін до бази даних.

  2. Якщо зв’язок не потрібно зберігати в жодній наявній базі даних, створіть пусту базу даних: На вкладці Файл виберіть команду Створити та натисніть кнопку Нова база даних. Зверніть увагу: якщо ви використовуєте Access 2007, натисніть кнопку Microsoft Office, а потім клацніть Створити.

Після цього можна розпочати операцію зв’язування.

Створення зв’язку

  1. Залежно від версії Access розташування майстра імпорту та зв’язування може дещо відрізнятися. Виконайте дії для своєї версії Access:

    • Якщо ви використовуєте Access останньої версії за передплатою на Office 365, на вкладці Зовнішні дані в групі Імпорт і зв’язування виберіть Нове джерело даних > З файлу > Excel.

    • Якщо ви використовуєте Access 2016, Access 2013 або Access 2010, на вкладці Зовнішні дані в групі Імпорт і зв’язування натисніть кнопку Excel.

    • Якщо ви використовуєте Access 2007, на вкладці Зовнішні дані в групі Імпорт натисніть кнопку Excel.

    Примітка : Вкладка Зовнішні дані доступна, лише якщо відкрито базу даних.

  2. У діалоговому вікні Отримати зовнішні дані - Таблиця Excel у полі Ім’я файлу вкажіть ім’я вихідного файлу Excel.

  3. Виберіть пункт Підключитися до джерела даних за допомогою створення пов’язаної таблиці та натисніть кнопку ОК.

    Відкриється вікно майстра зв’язування електронних таблиць, який допоможе виконати операцію зв’язування.

  4. На першій сторінці майстра виберіть аркуш або іменований діапазон і натисніть кнопку Далі.

  5. Якщо перший рядок вихідного аркуша або діапазону містить імена полів, виберіть варіант Перший рядок містить заголовки стовпців. Заголовки стовпців використовуються в Access для іменування полів таблиці. Якщо ім’я стовпця включає певні спеціальні символи, його не можна використати як ім’я поля в Access. У таких випадках відобразиться повідомлення про те, що полю буде призначено припустиме ім’я. Для продовження натисніть кнопку ОК.

  6. На останній сторінці майстра вкажіть ім’я для зв’язаної таблиці та натисніть кнопку Готово. Якщо таблиця з указаним іменем уже існує, буде запропоновано перезаписати наявну таблицю або запит. Натисніть кнопку Так, щоб перезаписати таблицю або запит, або кнопку Ні, щоб указати інше ім’я.

    Буде здійснено спробу створення зв’язаної таблиці. Якщо операцію завершено успішно, відобразиться повідомлення Зв’язування таблиці завершено. Відкрийте зв’язану таблицю й перегляньте поля та дані, щоб переконатися, що всі поля містять правильні дані.

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

На початок сторінки

Усунення помилки #Num! та інших неправильних значень у зв’язаній таблиці

Навіть якщо відобразилося повідомлення Зв’язування таблиці завершено, таблицю слід відкрити у вікні табличного подання даних і переконатися, що рядки та стовпці містять правильні дані.

За наявності в таблиці помилок або неправильних даних виконайте дії, описані в цій таблиці, і повторіть операцію зв’язування. Пам’ятайте, що значення не можна додати безпосередньо до зв’язаної таблиці, позаяк її призначено лише для читання.

Проблема

Вирішення

Графічні елементи

Графічні елементи на аркуші Excel, наприклад, емблеми, схеми та рисунки, не можна зв’язати з Access.

Формат відображення

Може знадобитися встановити властивість певних полів Формат (Format) у режимі конструктора, щоб переконатися, що в поданні таблиці правильно відображено дані.

Обчислювані значення

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

Скорочені текстові значення

Збільште ширину стовпця в поданні таблиці. Якщо після цього значення все ще не відображено повністю, можливо, воно містить більше 255 символів. У програмі Access можна зв’язатися лише з першими 255 символами, тому слід імпортувати дані, а не зв’язуватися з ними.

Повідомлення про переповнення числового поля

Зв’язана таблиця може відображатися правильно, але під час запуску запиту до таблиці може відобразитися повідомлення про помилку Переповнення числового поля. Це може бути зумовлено конфліктом між типом даних поля у зв’язаній таблиці та типом даних, які зберігаються в цьому полі.

Значення TRUE або FALSE і -1 або 0

Якщо вихідний аркуш або діапазон включає стовпець, який містить лише значення TRUE або FALSE, для стовпця у зв’язаній таблиці буде створено поле «Так/Ні». Однак, за наявності у вихідному аркуші або діапазоні стовпця, який містить лише значення -1 або 0, для стовпця в застосунку Access за промовчанням буде створено числове поле, і змінити тип даних відповідного поля в таблиці не вдасться. Якщо у зв’язаній таблиці потрібно розмістити поле «Так/Ні», переконайтеся, що вихідний стовпець включає значення TRUE та FALSE.

Багатозначні поля

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

#NUM!

Повідомлення про помилку #Num! відобразиться в полі замість фактичних даних за таких умов:

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

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

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

Виконання цих дій допомагає звести до мінімуму появу пустих значень у таблиці:

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

  2. Відформатуйте стовпці на аркуші Excel.

  3. Під час операції зв’язування виберіть правильний тип даних для кожного поля. Якщо вказано неправильний тип даних, стовпець результатів може містити лише значення #Num! у всіх рядках даних.

Значення дат замінено числовими значеннями

Якщо в полі відображено випадкове п’ятизначне число, перевірте, чи вихідний стовпець містить як (переважно) числові значення, так і кілька значень дат. Значення дат, які відображено в числових стовпцях, неправильно перетворено на числа. Замініть значення дат числовими значеннями та повторіть операцію зв’язування.

Числові значення замінено значеннями дат

Якщо в полі відображено випадкове значення дати, перевірте, чи вихідний стовпець містить як (переважно) значення дат, так і кілька числових значень. Числові значення, які відображено у стовпцях дат, неправильно перетворено на дати. Замініть числові значення значеннями дат і повторіть операцію зв’язування.


На початок сторінки

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

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

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

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×