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