Керування зовнішніми діапазонами даних і їхніми властивостями

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

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

Властивості діапазону зовнішніх даних

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

  • Для зовнішніх діапазонів даних, створених у Microsoft Query та майстрі зв'язків даних, використовуйте діалогове вікно Властивості зовнішніх даних .

  • Для зовнішніх діапазонів даних, створених із імпортованого текстового файлу або веб-запиту, який завантажує дані HTML, використовуйте діалогове вікно властивостей зовнішнього діапазону даних .

  • Для зовнішніх діапазонів даних, створених із веб-запиту, який отримує дані XML, використовуйте діалогове вікно Властивості карти XML .

Використання шаблонів із зовнішніми діапазонами даних

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

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

  2. У рядок формул клацніть стрілку поруч із полем ім'я, а потім виберіть ім'я потрібного діапазону зовнішніх даних.

  1. На вкладці Формули у групі Визначені імена натисніть кнопку Диспетчер імен.

    Зображення стрічки у програмі Excel

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

  3. У діалоговому вікні редагувати ім'я введіть нове ім'я посилання в полі ім'я .

Примітка.: Кнопка " закрити " закриється лише в діалоговому вікні " Диспетчер імен ". Щоб внести зміни, які ви вже зробили, не потрібно натискати кнопку закрити .

Ви можете змінити основний запит для зовнішнього діапазону даних, створеного в Microsoft Query, імпортованому текстовому файлі, веб-запиті або майстрі зв'язків даних.

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

  2. На вкладці дані в групі підключення натисніть кнопку Властивості.

    Зображення стрічки Excel

  3. У діалоговому вікні Властивості зовнішніх даних натисніть кнопку Властивості підключення Зображення кнопки .

  4. У діалоговому вікні Властивості підключення відкрийте вкладку визначення та натисніть кнопку редагувати запит.

Докладні відомості про конструювання та редагування запитів у Microsoft Query наведено в статті Довідка з Microsoft Query.

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

  2. На вкладці Дані в групі Підключення натисніть кнопку Підключення.

    Зображення стрічки Excel

  3. У діалоговому вікні підключення до книги натисніть кнопку Властивості.

  4. У діалоговому вікні Властивості підключення відкрийте вкладку визначення та натисніть кнопку редагувати запит.

  5. У діалоговому вікні Імпорт текстового файлу натисніть кнопку Імпорт.

  6. Внесіть зміни до імпортованого текстового файлу в майстрі імпорту тексту, а потім натисніть кнопку завершити.

    Докладні відомості про імпорт текстових файлів наведено в статті Імпорт або експорт текстових файлів.

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

  2. На вкладці дані в групі підключення натисніть кнопку Властивості.

    Зображення стрічки Excel

  3. У діалоговому вікні підключення до книги натисніть кнопку Властивості.

  4. У діалоговому вікні Властивості підключення відкрийте вкладку визначення та натисніть кнопку редагувати запит.

  5. Внесіть зміни до веб-запиту в діалоговому вікні редагування веб-запиту , а потім натисніть кнопку завершити.

Докладні відомості про створення та редагування веб-запитів наведено в статті підключення до веб-сторінки.

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

  2. На вкладці дані в групі підключення натисніть кнопку Властивості.

    Зображення стрічки Excel

  3. У діалоговому вікні Властивості зовнішніх даних натисніть кнопку Властивості підключення Зображення кнопки .

  4. У діалоговому вікні Властивості підключення виберіть вкладку визначення .

  5. Виконайте одну з таких дій:

    • У полі тип команди виберіть пункт таблиця , а потім у текстовому полі командний текст змініть значення на ім'я відповідної таблиці, подання або запиту.

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

      Примітка.: Залежно від того, як було визначено підключення, поле тип команди може бути недоступне (вона відображається сірим кольором).

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

    Для таблиці Excel виберіть ім'я діапазону, а потім натисніть сполучення клавіш CTRL + A, щоб вибрати заголовки таблиці.

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

  2. На вкладці Основне у групі Буфер обміну натисніть кнопку Копіювати.

    Зображення стрічки у програмі Excel

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

  4. Клацніть верхню ліву частину область вставлення.

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

  5. На вкладці Основне у групі Буфер обміну натисніть кнопку Вставити.

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

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

  1. На вкладці дані в групі підключення натисніть кнопку Властивості.

    Зображення стрічки Excel

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

    • Вставлення клітинок для нових даних, видалення невикористаних клітинок

      Примітки.: 

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

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

    • Вставлення цілих рядків для нових даних, очищення невикористаних клітинок

      Примітки.: 

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

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

    • Перезаписування наявної клітинки новими даними, очистити невикористовувані клітинки

      Примітки.: 

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

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

  1. На вкладці дані в групі підключення натисніть кнопку Властивості.

    Зображення стрічки Excel

  2. У розділі форматування та макет данихвиконайте одну або кілька з наведених нижче дій.

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

      Примітка.: Цей прапорець доступний лише для зовнішнього діапазону даних, перетвореного з програми Microsoft Office 2003 або до зовнішнього діапазону даних, створеного програмно.

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

      Примітка.: Цей прапорець недоступний для імпортованого текстового файлу, XML-файлу або веб-запиту.

    • Щоб зберегти форматування, застосоване в клітинках, установіть прапорець зберегти форматування у клітинках .

      Примітка.: Для веб-запиту цей параметр автоматично очищується, якщо вибрати повне ФОРМАТУВАННЯ HTML у діалоговому вікні Параметри . Відкрити діалогове вікно " настройки " в діалоговому вікні " редагування запиту ".

    • Щоб зберегти ширину стовпців, встановіть прапорець регулювати ширину стовпця .

    • Щоб зберегти сортування, фільтрування та макет стовпця, установіть прапорець зберегти сортування або фільтр або макет .

      Примітка.: Цей прапорець недоступний для веб-запиту.

    • Щоб зберегти фільтрацію стовпця, який потрібно застосувати, установіть прапорець зберегти фільтр стовпця .

      Примітка.: Цей прапорець доступний лише для веб-запиту на основі XML-даних.

Примітка.: Наведена нижче процедура застосовується лише до зовнішнього діапазону даних, перетвореного з програми Microsoft Office 2003 або до зовнішнього діапазону даних, створеного програмно. Усі зовнішні діапазони даних, створені за допомогою інтерфейсу користувача, створюються як таблиці Excel. Ці таблиці автоматично розгортаються, коли додаються нові рядки та заповнюють обчислювані стовпці.

  1. У клітинці, суміжному з першим рядком даних у діапазоні зовнішніх даних, можна вказати формулу.

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

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

  3. Виберіть клітинку в діапазоні зовнішніх даних.

  4. На вкладці дані в групі підключення натисніть кнопку Властивості.

    Зображення стрічки Excel

  5. У діалоговому вікні Властивості діапазону зовнішніх даних установіть прапорець заповнювати формули в стовпцях, суміжних із даними .

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

Заморожування діапазону зовнішніх даних зберігає дані, але не його основний запит, тому не можна оновити заморожені зовнішні дані.

  1. Клацніть аркуш, який містить таблицю Excel, з якої потрібно видалити зв'язок даних.

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

  3. На вкладці Знаряддя у групі дані зовнішньої таблиці натисніть кнопку розірвати зв'язок. Діапазон даних залишається й надалі має таке саме ім'я, але підключення видаляється.

Примітка.: Наведена нижче процедура застосовується лише до зовнішнього діапазону даних, перетвореного з програми Microsoft Office 2003 або до зовнішнього діапазону даних, створеного програмно. Усі зовнішні діапазони даних, створені за допомогою інтерфейсу користувача, створюються як таблиці Excel. Ці таблиці автоматично розгортаються, коли додаються нові рядки та заповнюють обчислювані стовпці.

  1. На вкладці дані в групі підключення натисніть кнопку Властивості.

    Зображення стрічки Excel

  2. У розділі визначення запитузніміть прапорець зберегти визначення запиту .

Примітка.:  Якщо зберегти книгу, у книзі буде видалено основний запит. Проте збережені файли запитів до бази даних (DQY або ODC-файли) не видаляються. Якщо ви зберегли запит, коли ви створили його в майстрі запитів або в Microsoft Query, файл запиту зберігається на комп'ютері, і ви можете знову скористатися запитом для отримання зовнішніх даних. Докладні відомості наведено в статті Довідка з Microsoft Query.

Примітка.: Наведена нижче процедура застосовується лише до зовнішнього діапазону даних, створеного за допомогою майстра зв'язків даних або Microsoft Query (а не до імпортованого текстового файлу або веб-запиту), діапазону зовнішніх даних, перетворених із програми Microsoft Office 2003, або зовнішнього діапазон даних створено програмно. Усі зовнішні діапазони даних, створені за допомогою інтерфейсу користувача, створюються як таблиці Excel.

  1. Вставте пустий рядок вище діапазону зовнішніх даних, виконавши одну з наведених нижче дій.

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

    • Клацніть маркер виділення рядка, над яким потрібно вставити новий рядок. Потім на вкладці основне у групі клітинки натисніть кнопку Вставити. Оскільки ви вибрали один рядок, один новий рядок буде вставлено над вибраним рядком.

  2. Введіть потрібні Мітки в клітинки в пустому рядку.

  3. Виберіть клітинку в діапазоні зовнішніх даних.

  4. На вкладці дані в групі підключення натисніть кнопку Властивості.

    Зображення стрічки Excel

  5. У діалоговому вікні Властивості діапазону зовнішніх даних у розділі форматування та макет данихзніміть прапорець додавати імена полів і натисніть кнопку OK.

  6. Щоб видалити наявне ім'я поля та оновити діапазон зовнішніх даних, клацніть її в діапазоні зовнішніх даних, а потім виберіть команду Оновити дані Зображення кнопки .

Примітка.: Коли ви Витягаємо дані з бази даних, усі зміни в іменах стовпців у Microsoft Query зберігаються в діапазоні зовнішніх даних. Докладні відомості про змінення імен стовпців у запиті наведено в статті Довідка з Microsoft Query.

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

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

    Якщо діапазон зовнішніх даних – це таблиця Excel, натисніть клавіші CTRL + A, щоб виділити всю таблицю.

  3. Щоб видалити діапазон зовнішніх даних, натисніть клавішу DELETE.

  4. Щоб видалити основний запит, натисніть кнопку так , коли в програмі Excel з'явиться запит.

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

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

Примітка.:  Цю сторінку перекладено за допомогою засобу автоматичного перекладу, тому вона може містити смислові, синтаксичні або граматичні помилки. Ми вважаємо, що цей вміст стане вам у пригоді. Повідомте нас, чи була ця інформація корисною. Для довідки цю статтю можна переглянути англійською мовою.

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

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

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

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

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

×