Властивості зовнішнього діапазону даних можна змінити, щоб керувати даними, імпортованими до аркуша через підключення до зовнішнього джерела даних, наприклад бази даних, веб-запиту або текстового файлу.
Діапазон зовнішніх даних – це визначене ім'я або ім'я таблиці, яке визначає розташування даних, перенесених на аркуш. Під час підключення до зовнішніх даних Excel автоматично створює діапазон зовнішніх даних. Єдиний виняток – це звіт зведеної таблиці, підключений до джерела даних – у звіті зведеної таблиці не використовується діапазон зовнішніх даних. У програмі Excel можна форматувати діапазон зовнішніх даних або використовувати його для обчислень, як і з іншими даними.
Властивості зовнішнього діапазону даних
Зовнішній діапазон даних має додаткові властивості (не слід плутати з властивостями запиту або підключення), за допомогою яких можна керувати даними, наприклад збереженням форматування клітинок і шириною стовпця. Щоб змінити ці властивості зовнішнього діапазону даних, на вкладці Дані в групі Connections натисніть кнопку Властивості. Потім внесіть зміни, виконавши такі дії:
-
Для діапазонів зовнішніх даних, створених із microsoft Query та майстра зв'язків даних, використовуйте діалогове вікно Властивості зовнішніх даних .
-
Для діапазонів зовнішніх даних, створених з імпортованого текстового файлу або веб-запиту, який отримує HTML-дані, використовуйте діалогове вікно Властивості зовнішнього діапазону даних .
-
Для діапазонів зовнішніх даних, створених із веб-запиту, який отримує XML-дані, використовуйте діалогове вікно Властивості карти XML .
-
Виберіть аркуш, на якому потрібно знайти діапазон зовнішніх даних.
-
На рядок формул клацніть стрілку поруч із полем Ім'я та виберіть потрібне ім'я діапазону зовнішніх даних.
-
Виберіть пункт Формули > Диспетчер імен.
-
У діалоговому вікні Диспетчер імен клацніть ім'я діапазону зовнішніх даних і натисніть кнопку Редагувати. Можна також просто двічі клацнути ім’я.
-
У діалоговому вікні Редагування імені введіть нове ім'я посилання в полі Ім'я .
Примітка.: Кнопка Закрити закриває лише діалогове вікно Диспетчер імен . Щоб зберегти вже внесені зміни, не потрібно натискати кнопку Закрити .
Ви можете змінити базовий запит для зовнішнього діапазону даних, створеного з Microsoft Query, імпортованого текстового файлу, веб-запиту або майстра зв'язків даних.
-
Клацніть клітинку в діапазоні зовнішніх даних, для якої потрібно змінити базовий запит.
-
Виберіть Властивості > даних.
-
У діалоговому вікні Властивості зовнішніх даних натисніть кнопку Властивості підключення
-
У діалоговому вікні Властивості підключення перейдіть на вкладку Визначення та натисніть кнопку Редагувати запит.
Докладні відомості про створення та редагування запитів у Microsoft Query див. в довідці Microsoft Query.
-
Клацніть клітинку в діапазоні зовнішніх даних, для якої потрібно змінити базовий запит.
-
Виберіть > дані Connections & запити > Connections вкладку, клацніть потрібне підключення правою кнопкою миші та виберіть пункт Властивості.
-
У діалоговому вікні Властивості підключення перейдіть на вкладку Визначення та натисніть кнопку Редагувати запит.
-
У діалоговому вікні Імпорт текстового файлу натисніть кнопку Імпорт.
-
Внесіть зміни до імпортованого текстового файлу в майстрі імпорту тексту та натисніть кнопку Готово.
Докладні відомості про імпорт текстових файлів див. в статті Імпорт і експорт текстових файлів.
-
Клацніть клітинку в діапазоні зовнішніх даних, для якої потрібно змінити базовий запит.
-
Виберіть Властивості> даних .
-
Виберіть > дані Connections & запити > Connections вкладку, клацніть потрібне підключення правою кнопкою миші та виберіть пункт Властивості.
-
У діалоговому вікні Властивості підключення перейдіть на вкладку Визначення та натисніть кнопку Редагувати запит.
-
У діалоговому вікні Редагування веб-запиту внесіть зміни до веб-запиту та натисніть кнопку Готово.
Докладні відомості про створення та редагування веб-запитів див. в статті Підключення до веб-сторінки.
-
Клацніть клітинку в діапазоні зовнішніх даних, для якої потрібно змінити базовий запит.
-
Виберіть Властивості> даних .
-
У діалоговому вікні Властивості зовнішніх даних натисніть кнопку Властивості підключення
-
У діалоговому вікні Властивості підключення перейдіть на вкладку Визначення .
-
Виконайте одну з таких дій:
-
У полі Тип команди натисніть кнопку Таблиця , а потім у текстовому полі Команда змініть значення на ім'я відповідної таблиці, подання або запиту.
-
У полі Тип команди виберіть пункт SQL або За замовчуванням , а потім у текстовому полі Command змініть інструкцію SQL.
Примітка.: Залежно від того, як було визначено підключення, поле Тип команди може бути недоступним (воно відображається сірим кольором).
-
-
У рядку формул клацніть стрілку поруч із полем Ім'я та виберіть ім'я діапазону зовнішніх даних, який потрібно скопіювати.
Для таблиці Excel виділіть ім'я діапазону, а потім натисніть клавіші Ctrl+A, щоб виділити заголовки таблиці.
Якщо потрібно включити підписи стовпців або формули, які не входять до діапазону зовнішніх даних, виділіть клітинки з підписами стовпців або формулами, які потрібно скопіювати. Клацніть стрілку поруч із полем "Ім'я" в рядку формул і виберіть ім'я зовнішнього діапазону даних, який потрібно скопіювати.
-
Виберіть елемент Основне > Копіювати.
-
Перейдіть до книги, у яку потрібно вставити діапазон зовнішніх даних.
-
Клацніть верхню ліву клітинку область вставлення.
Щоб переконатися, що зовнішні дані не замінює наявні дані, переконайтеся, що аркуш не містить даних під виділеною клітинкою або праворуч від неї.
-
На вкладці Основне у групі Буфер обміну натисніть кнопку Вставити.
Примітка.: Якщо скопіювати лише частину діапазону зовнішніх даних, базовий запит не скопіюється, а скопійовані дані не можна оновити.
Ви можете керувати тим, як обробляти менший або більший набір даних, який повертається до програми Excel під час оновлення даних.
-
Виберіть Властивості> даних .
-
У діалоговому вікні Властивості зовнішнього діапазону даних у розділі Якщо кількість рядків у діапазоні даних змінюється після оновлення, виберіть один із таких варіантів:
-
Вставлення клітинок для нових даних, видалення невикористовуваних клітинок
Примітки.:
-
Коли до джерела даних додається один або кілька рядків, клітинки під зовнішнім діапазоном даних переміщуються вниз, але клітинки праворуч від діапазону зовнішніх даних не переміщуються.
-
Якщо видалити один або кілька рядків у джерелі даних, клітинки безпосередньо під зовнішнім діапазоном даних переміщуються вгору, але клітинки праворуч від діапазону зовнішніх даних не переміщуються.
-
-
Вставлення цілих рядків для нових даних, очищення невикористовуваних клітинок
Примітки.:
-
Коли до джерела даних додається один або кілька рядків, клітинки безпосередньо під діапазоном зовнішніх даних і праворуч переміщуються вниз.
-
Якщо в джерелі даних видаляється один або кілька рядків, клітинки, розташовані безпосередньо під діапазоном зовнішніх даних і праворуч від нього, не переміщуються.
-
-
Перезаписати наявні клітинки новими даними, очистити невикористовувані клітинки
Примітки.:
-
Коли до джерела даних додається один або кілька рядків, клітинки безпосередньо під зовнішнім діапазоном даних перезаписуються, але клітинки праворуч від діапазону зовнішніх даних не переміщуються.
-
Якщо в джерелі даних видаляється один або кілька рядків, клітинки, розташовані безпосередньо під діапазоном зовнішніх даних і праворуч від нього, не переміщуються.
-
-
-
Виберіть Властивості> даних .
-
У розділі Форматування та макет даних виконайте одну або кілька з таких дій:
-
Щоб додати імена полів як перший рядок, установіть прапорець Включити імена полів .
Примітка.: Цей прапорець доступний лише для діапазону зовнішніх даних, перетвореного з програми Microsoft Office 2003, або для зовнішнього діапазону даних, створеного програмним способом.
-
Щоб додати стовпець із номерами рядків, установіть прапорець Включити номери рядків .
Примітка.: Цей прапорець недоступний для імпортованого текстового файлу, XML-файлу або веб-запиту.
-
Щоб зберегти застосоване форматування клітинок, установіть прапорець Зберегти форматування клітинок .
Примітка.: Для веб-запиту цей параметр автоматично очищається, якщо в діалоговому вікні Параметри вибрати параметр Повне форматування HTML. Ви отримуєте доступ до діалогового вікна Параметри в діалоговому вікні Редагування запиту .
-
Щоб зберегти установлену ширину стовпців, установіть прапорець Настроїти ширину стовпця .
-
Щоб зберегти застосовані сортування, фільтрування та макет стовпців, установіть прапорець Зберігати сортування, фільтр або макет стовпця .
Примітка.: Цей прапорець недоступний для веб-запиту.
-
Щоб зберегти застосоване фільтрування стовпців, установіть прапорець Зберегти фільтр стовпця .
Примітка.: Цей прапорець доступний лише для веб-запиту на основі XML-даних.
-
Примітка.: Наведена нижче процедура застосовується лише до діапазону зовнішніх даних, перетвореного з програми Microsoft Office 2003 або зовнішнього діапазону даних, створеного програмним способом. Усі діапазони зовнішніх даних, створені за допомогою інтерфейсу користувача, створюються як таблиці Excel. Ці таблиці автоматично розгортаються під час додавання нових рядків і заповнення обчислюваних стовпців.
-
Введіть формулу в клітинку, суміжну з першим рядком даних у діапазоні зовнішніх даних.
Примітка.: Перший рядок даних може бути першим або другим рядком у діапазоні зовнішніх даних залежно від того, чи містить перший рядок заголовки.
-
Виділіть клітинку та двічі клацніть маркер заповнення, щоб скопіювати формулу до всіх рядків у діапазоні зовнішніх даних.
-
Виберіть клітинку в діапазоні зовнішніх даних.
-
Виберіть Властивості> даних .
-
У діалоговому вікні Властивості зовнішнього діапазону даних установіть прапорець Заповнювати формули стовпцями, суміжними з даними .
Примітка.: Якщо діапазон зовнішніх даних розширюється, коли оновити, програма Excel копіює лише формули, суміжні із зовнішнім діапазоном даних або в межах нього.
Закріплення діапазону зовнішніх даних зберігає дані, але не його базовий запит, тому не можна оновити закріплений діапазон зовнішніх даних.
-
Клацніть аркуш, що містить таблицю Excel, з якої потрібно видалити зв'язок із даними.
-
У рядку формул клацніть стрілку поруч із полем Ім'я та виберіть ім'я діапазону зовнішніх даних, з якого потрібно видалити зв'язок даних. Потім буде вибрано діапазон даних.
-
На вкладці Знаряддя в групі Дані зовнішньої таблиці натисніть кнопку Розірвати зв'язок. Діапазон даних залишається та має однакове ім'я, але зв'язок видаляється.
Примітка.: Наведена нижче процедура застосовується лише до діапазону зовнішніх даних, перетвореного з програми Microsoft Office 2003 або зовнішнього діапазону даних, створеного програмним способом. Усі діапазони зовнішніх даних, створені за допомогою інтерфейсу користувача, створюються як таблиці Excel. Ці таблиці автоматично розгортаються під час додавання нових рядків і заповнення обчислюваних стовпців.
-
Виберіть Властивості> даних .
-
У розділі Визначення запиту зніміть прапорець Зберегти визначення запиту .
Примітка.: Коли ви зберігаєте книгу, базовий запит видаляється з книги. Однак збережені файли запитів бази даних (файли DQY або ODC) не видаляються. Якщо ви зберегли запит, створений у майстрі запитів або в Microsoft Query, файл запиту буде збережено на комп'ютері, і ви зможете скористатися запитом ще раз для отримання зовнішніх даних. Докладні відомості див. в довідці Microsoft Query.
Примітка.: Наведена нижче процедура застосовується лише до зовнішнього діапазону даних, створеного за допомогою майстра зв'язків даних або Microsoft Query (а не до імпортованого текстового файлу чи веб-запиту), зовнішнього діапазону даних, перетвореного з програми Microsoft Office 2003, або діапазону зовнішніх даних, створеного програмно. Усі діапазони зовнішніх даних, створені за допомогою інтерфейсу користувача, створюються як таблиці Excel.
-
Вставте пустий рядок над зовнішнім діапазоном даних, виконавши одну з таких дій:
-
Виберіть клітинку, над якою потрібно вставити новий рядок. Потім на вкладці Основне в групі Клітинки клацніть стрілку поруч із кнопкою Вставити та виберіть пункт Вставити рядки аркуша. Оскільки виділено одну клітинку, над поточним рядком вставляється один новий рядок.
-
Клацніть селектор рядків у рядку, над яким потрібно вставити новий рядок. Потім на вкладці Основне в групі Клітинки натисніть кнопку Вставити. Оскільки виділено один рядок повністю, над виділеним рядком вставляється один новий рядок.
-
-
Введіть потрібні підписи в клітинки в пустому рядку.
-
Виберіть клітинку в діапазоні зовнішніх даних.
-
Виберіть Властивості> даних .
-
У діалоговому вікні Властивості зовнішнього діапазону даних у розділі Форматування та макет даних зніміть прапорець Включити імена полів і натисніть кнопку OK.
-
Щоб видалити наявні імена полів і оновити діапазон зовнішніх даних, клацніть клітинку в діапазоні зовнішніх даних і натисніть кнопку Оновити дані
Примітка.: Під час отримання даних із бази даних будь-які зміни імен стовпців у Microsoft Query зберігаються в діапазоні зовнішніх даних. Відомості про змінення імен стовпців у запиті див. в довідці Microsoft Query.
-
Клацніть аркуш із зовнішнім діапазоном даних, який потрібно видалити.
-
У рядку формул клацніть стрілку поруч із полем Ім'я та виберіть ім'я діапазону зовнішніх даних, який потрібно видалити.
Якщо діапазон зовнішніх даних – це таблиця Excel, натисніть клавіші Ctrl+A, щоб виділити всю таблицю.
-
Щоб видалити діапазон зовнішніх даних, натисніть клавішу Delete.
-
Щоб видалити базовий запит, натисніть кнопку Так , коли в Excel з'явиться відповідний запит.
