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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Закріплення даних у таблиці Excel

  1. Виберіть аркуш із таблицею Excel, з якого потрібно видалити зв'язок даних.

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

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

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

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

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

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

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

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

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

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

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

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

    Як можна вставити пустий рядок

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

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

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

      • Додавання кількох рядків    Виділіть рядки безпосередньо під розташування нових рядків. Виберіть однакову кількість рядків, як потрібно вставити.

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

      • Вставлення кількох стовпців    Виберіть стовпці справа від якого ви бажаєте, щоб додати нові стовпці. Виберіть однакова кількість стовпців, як потрібно вставити.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

×