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

Увага! : Цю статтю перекладено за допомогою служби машинного перекладу; див. застереження. Версію цієї статті англійською мовою див. тут для отримання довідки.

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

Виберіть одну з таких можливостей:

Докладні відомості про діапазони зовнішніх даних і властивостей

Пошук діапазону зовнішніх даних

Зміна імені діапазону зовнішніх даних

Змінення базового запиту для зовнішнього діапазону даних

Копіювання діапазону зовнішніх даних і запиту, на якому його засновано

Вибір способу додавання нових даних до діапазону зовнішніх даних

Визначення форматування даних і макету

Копіювання формул при розширенні діапазону зовнішніх даних

Закріплення діапазону зовнішніх даних

Настройка імен полів у діапазоні зовнішніх даних

Видалення діапазону зовнішніх даних

Докладно про зовнішні діапазони даних і їхні властивості

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

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

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

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

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

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

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

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

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

Пошук діапазону зовнішніх даних

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

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

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

Зміна імені діапазону зовнішніх даних

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

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

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

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

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

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

Змінення базового запиту для зовнішнього діапазону даних

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

Змінення базового запиту для зовнішнього діапазону даних, створеного за допомогою застосунку 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. На вкладці Home, у групі Clipboard клацніть елемент Copy.

    зображення стрічки в excel

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

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

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

  5. На вкладці Home, у групі Clipboard клацніть елемент Paste.

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

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

Вибір способу додавання нових даних до діапазону зовнішніх даних

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

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

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

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

    • вставити нові клітинки, видалити пусті клітинки

      Примітки : 

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

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

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

      Примітки : 

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

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

    • замінити наявні клітинки, очистити пусті клітинки

      Примітки : 

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

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

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

Визначення форматування даних і макету

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

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

  2. У групі Формат і розмічування даних виконайте одну з таких дій:

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

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

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

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

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

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

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

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

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

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

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

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

Копіювання формул при розширенні діапазону зовнішніх даних

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

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

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

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

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

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

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

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

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

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

Закріплення діапазону зовнішніх даних

Закріплення діапазону зовнішніх даних — це фіксація даних (але не відповідного запиту), внаслідок якої закріплений діапазон зовнішніх даних стає неможливо оновити.

Закріплення даних у таблиці 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 відобразиться відповідний запит.

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

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

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

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

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

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

×