Оновлення зв'язку із зовнішніми даними в Excel

У таблиці нижче підсумовано дії з оновлення, сполучення клавіш і команди.

Дія	Клавіші	Або
Оновлення вибраних даних на аркуші	Alt+F5	Виберіть пункт Дані > Стрілка розкривного списку біля кнопки Оновити все > Оновити
Оновлення всіх даних у книзі	Ctrl+Alt+F5	Виберіть елемент Дані > Оновити всі
Перевірка стану оновлення		Двічі клацніть повідомлення "Отримання даних" у рядку стану.
Припинення оновлення	Esc
Припинення оновлення у фоновому режимі.		Двічі клацніть повідомлення в рядку стану. А потім у діалоговому вікні Стан оновлення зовнішніх даних натисніть кнопку Зупинити оновлення.

Дані в книзі можуть зберігатися безпосередньо в книзі або зберігатися в зовнішньому джерелі даних, наприклад у текстовому файлі, базі даних або в хмарі. Під час першого імпорту зовнішніх даних програма Excel створює відомості про підключення, іноді збережені у файлі зв'язку з даними Office (ODC), у якому описано, як знайти зовнішнє джерело даних, увійти в нього та отримати доступ до нього.

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

Докладніше про оновлення даних

Відомості про проблеми безпеки

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

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

• Додавання, видалення або змінення надійного розташування файлів

• Надійні документи

• Додавання, видалення або перегляд надійного видавця

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

OdC file    – файл зв'язку даних (ODC) часто містить один або кілька запитів, які використовуються для оновлення зовнішніх даних. Замінивши цей файл, користувач, який має зловмисні наміри, може створити запит, щоб отримати доступ до конфіденційної інформації та розповсюдити його іншим користувачам або виконати інші шкідливі дії. Тому важливо переконатися, що файл підключення створено надійним користувачем, а файл підключення захищено й походить із надійної бібліотеки зв'язків даних (DCL).

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

Обміну-   Ви надаєте спільний доступ до цієї книги іншим користувачам, які, можливо, захочете оновити дані? Допоможіть колегам уникнути помилок оновлення даних, нагадуючи їм надіслати запит на дозволи для джерел даних, що надають дані.

Докладні відомості див. в статті Керування параметрами та дозволами джерела даних.

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

1. Виділіть клітинку в діапазоні зовнішніх даних.

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

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

4. Якщо потрібно зберігати книгу разом із визначенням запиту, але без зовнішніх даних, установіть прапорець Видаляти дані з зовнішнього діапазону даних перед збереженням робочої книги.

1. Виділіть клітинку в діапазоні зовнішніх даних.

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

3. Перейдіть на вкладку Використання.

4. Установіть прапорець Оновлювати кожні та вкажіть у хвилинах потрібний інтервал оновлень.

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

1. Виділіть клітинку в діапазоні зовнішніх даних.

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

3. Перейдіть на вкладку Використання .

4. Установіть прапорець Фонове оновлення, щоб виконувати запит у фоновому режимі. Зніміть цей прапорець, щоб не виконувати запит у фоновому режимі.

   Кінчик    Під час записування макрос, який містить запит, програма Excel не виконує запит у фоновому режимі. Щоб змінити записаний макрос так, щоб запит запускав у фоновому режимі, відредагуйте макрос у Редактор Visual Basic. Змініть метод оновлення для об'єкта QueryTable з BackgroundQuery := False на BackgroundQuery := True.

Коли ви формуєте дані в надбудові Power Query, зазвичай завантажуються зміни до аркуша або моделі даних. Важливо розуміти різницю під час оновлення даних і способу їх оновлення.

Більшість запитів базуються на ресурсах зовнішніх даних одного або іншого типу. Проте існує ключова відмінність між програмою Excel і надбудовою Power Query. Надбудова Power Query кешує зовнішні дані локально, щоб підвищити продуктивність.  Крім того, надбудова Power Query не оновлює локальний кеш автоматично, щоб уникнути витрат на джерела даних в Azure.

Оновлення запиту в редакторі Power Query

Коли ви оновлюєте запит у редакторі Power Query, ви не тільки оновлюєте дані із зовнішнього джерела даних, а й оновлюєте локальний кеш. Але ця операція оновлення не оновлює запит на аркуші або в моделі даних.

1. У редакторі Power Query виберіть основне

2. Виберіть Оновити попередній перегляд > Попередній перегляд оновлення (поточний запит у режимі попереднього перегляду даних)  або Оновити все (усі відкриті запити з області запити).

3. У нижній частині редактора Power Query праворуч відображається повідомлення "Preview downloaded at <hh:mm> AM/PM". Це повідомлення відображається під час першого імпорту та після кожної наступної операції оновлення в редакторі Power Query.

Оновлення запиту на аркуші

1. У програмі Excel виділіть клітинку в запиті на аркуші.

2. Перейдіть на вкладку Запит на стрічці, а потім натисніть кнопку Оновити > Оновити.

3. Аркуш і запит оновлюються із зовнішнього джерела даних і кеша Power Query.

У будь-який час можна натиснути кнопку Оновити , щоб оновити дані для зведених таблиць у книзі. Ви можете оновити дані зведених таблиць, підключених до зовнішніх даних, як-от бази даних (SQL Server, Oracle, Access тощо), куба служб аналізу Analysis Services, каналу даних, а також даних із вихідної таблиці в тій самій або іншій книзі. Зведені таблиці можна оновлювати вручну або автоматично під час відкриття книги.

Оновлення вручну

1. Виберіть будь-де у зведеній таблиці, щоб відобразити на стрічці вкладку Аналіз зведеної таблиці .

   : Щоб оновити зведену таблицю в інтернет-версії Excel, клацніть правою кнопкою миші будь-де у зведеній таблиці та виберіть команду Оновити.

2. Натисніть кнопку Оновити або Оновити все.
   

3. Щоб перевірити стан оновлення, якщо оновлення триває довше, ніж очікувалося, клацніть стрілку під кнопкою Оновити > Стан оновлення.

4. Щоб припинити оновлення, натисніть кнопку Скасувати оновлення або натисніть клавішу Esc.

Запобігання налаштуванню ширини стовпців і форматування клітинок

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

1. Виберіть будь-де у зведеній таблиці, щоб відобразити на стрічці вкладку Аналіз зведеної таблиці .

2. Виберіть вкладку PivotTable Analyze (Аналіз зведеної таблиці), > у групі Зведена таблиця, натисніть кнопку Options (Параметри).

   

3. На вкладці Layout & Format (Формат ) > встановіть прапорці поруч із параметрами Автодобір ширини стовпців під час оновлення та Збереження форматування клітинок під час оновлення.

Автоматичне оновлення даних під час відкриття книги

1. Виберіть будь-де у зведеній таблиці, щоб відобразити на стрічці вкладку Аналіз зведеної таблиці .

2. Виберіть вкладку PivotTable Analyze (Аналіз зведеної таблиці), > у групі Зведена таблиця, натисніть кнопку Options (Параметри).

   

3. На вкладці Дані натисніть кнопку Оновити дані під час відкриття файлу.

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

1. Виберіть зведену таблицю, підключену до автономного файлу куба.

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

Докладні відомості див. в статті Робота з автономними файлами кубів.

Коли ви оновлюєте модель даних у надбудові Power Pivot, ви також можете побачити, чи вдалося виконати оновлення, чи скасувати оновлення. Докладні відомості див. в статті Power Pivot: ефективний аналіз даних і моделювання даних у програмі Excel.

Оновлення та перегляд стану оновлення   

1. У надбудові Power Pivot виберіть основне > Отримати зовнішні дані > Оновити або Оновити все , щоб оновити поточну таблицю або всі таблиці в моделі даних.

2. Стан оновлення позначається для кожного підключення, яке використовується в моделі даних. Є три можливі результати:

• Success – повідомляє про кількість рядків, імпортованих до кожної таблиці.

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

• Скасовано . Програма Excel не видала запит на оновлення, імовірно, тому що під час підключення вимкнуто оновлення.

Використання властивостей таблиці для відображення запитів, які використовуються для оновлення даних

Оновлення даних – це просто повторне виконання того самого запиту, який використовувався, щоб отримати дані в першу чергу. Запит можна переглядати, а іноді й змінювати, переглядаючи властивості таблиці у вікні Power Pivot.

1. Щоб переглянути запит, який використовується під час оновлення даних, виберіть Power Pivot > Керування , щоб відкрити вікно Power Pivot.

2. Виберіть конструктор > властивості таблиці.

3. Перейдіть до редактора запитів, щоб переглянути базовий запит.

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

Настроювання властивостей підключення для скасування оновлення даних

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

1. Щоб переглянути властивості підключення, у програмі Excel виберіть елемент Запити даних > запити & Підключення , щоб переглянути список усіх підключень, що використовуються в книзі.

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

3. Якщо на вкладці "Використання " в розділі "Оновити" знято прапорець "Оновити це підключення на "Оновити все", ви отримаєте повідомлення про скасування під час спроби оновити все у вікні Power Pivot.

Оновлення даних на сервері SharePoint Server 2013

На сервері SharePoint Server можна запланувати автоматичне оновлення даних, але для цього потрібно, щоб надбудову Power Pivot для SharePoint 2013 було інстальовано та настроєно в середовищі SharePoint. Зверніться до адміністратора служби SharePoint, щоб дізнатися, чи можна запланувати оновлення даних. Докладні відомості див. в статті Настроювання запланованого оновлення даних для надбудови Power Pivot.

Якщо дані, використані для карти, змінилися, їх можна оновити вручну в надбудові 3D Maps. Після цього зміни буде відображено на карті. Ось як це зробити:

• У надбудові 3D Maps виберіть пункт Домашня > Оновити дані.
  
  

Додавання даних у Power Map

Щоб додати дані до надбудови 3D MapsPower Map:

1. У надбудові 3D Maps перейдіть до карти, до якої потрібно додати дані.

2. Не закривайте вікно 3D Maps.

3. У програмі Excel виберіть дані аркуша, які потрібно додати.

4. На стрічці Excel на вкладці Вставлення клацніть стрілку поруч із кнопкою Карта й виберіть пункт Додати вибрані дані до Power Map. Карту в 3D Maps буде автоматично оновлено, і на ній з’являться додаткові дані. Докладні відомості див. в статті Отримання та підготовка даних до надбудови Power Map.
   
   

Оновлення зовнішніх даних у службах Excel Services має унікальні вимоги.

Керування оновленням даних

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

Оновлення під час відкриття за допомогою     Excel Services

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

1. У книзі із зв'язками із зовнішніми даними перейдіть на вкладку Дані .

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

3. Перейдіть на вкладку Використання та натисніть кнопку Оновити дані під час відкриття файлу.

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

Оновлення за допомогою odc-файлу

Якщо використовується файл зв'язку даних Office (ODC), переконайтеся, що також встановлено прапорець Завжди використовувати файл підключення :

1. У книзі із зв'язками із зовнішніми даними перейдіть на вкладку Дані .

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

3. Перейдіть на вкладку Визначення та натисніть кнопку Завжди використовувати файл підключення.

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

Оновлення вручну   

1. Виберіть клітинку у звіті зведеної таблиці.

2. На панелі інструментів Excel Web Access у меню Оновлення виберіть пункт Оновити вибране підключення.

   : 

   • Якщо ця команда "Оновити " не відображається, автор веб-частини очистив властивість "Оновити вибране підключення", "Оновити всі підключення ". Докладні відомості див. в статті Настроювані властивості веб-частини Excel Web Access.

   • Будь-яка інтерактивна операція, яка викликає повторний запит джерела даних OLAP, ініціює операцію оновлення вручну.

• Оновити всі підключення . На панелі інструментів Excel Web Access у меню Update (Оновлення) виберіть Refresh All Connections (Оновити всі підключення).

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

  Автор веб-частини може вибрати або очистити властивість Дозволити періодичне оновлення даних у веб-програмі Excel Web Access , щоб дозволити або заборонити періодичне оновлення. Коли за замовчуванням мине проміжок часу, у нижній частині веб-частини Excel Web Access відобразиться оповіщення про оновлення. 

  Автор веб-частини Excel Web Access також може встановити властивість Відображати періодичне оновлення даних, щоб керувати поведінкою повідомлення, яке відображається, коли служби Excel Services періодично оновлюються під час сеансу:

  Докладні відомості див. в статті Настроювані властивості веб-частини Excel Web Access.

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

• За потреби – це означає, що користувач може продовжити періодичне оновлення, не відображаючи повідомлення.

• Ніколи – це означає, що веб-програма Excel Web Access періодично оновлюється, не відображаючи повідомлення або запит.

• Скасування оновлення . Під час оновлення книгиExcel Services відображає повідомлення з запитом, оскільки це може тривати довше, ніж очікувалося. Можна натиснути кнопку Скасувати , щоб припинити оновлення, щоб завершити його пізніше в зручніший час. Відображаться дані, повернуті запитами до скасування оновлення.