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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Щоб отримати додаткові відомості про створення та редагування веб-запити можна знайти підключення до веб-сторінки (Power Query).

  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 Office 2003 діапазон даних, створені автоматично. Усі діапазони зовнішніх даних, які створюються за допомогою інтерфейсу користувача створюються як таблиці Excel.

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

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

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

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

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

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

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

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

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

Примітка.: Під час отримання даних із бази даних, будь-які зміни імен стовпців у Microsoft Query вони зберігаються в зовнішньому діапазоні даних. Щоб отримати відомості про змінення назви стовпців у запиті див.

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

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

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

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

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

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

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

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

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

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

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

×