Обновление подключения к внешним данным в Excel

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

Задача	Клавиши	Или
Обновление выбранных данных на листе	ALT+F5	Выберите Данные > стрелка раскрывающегося списка рядом с элементом Обновить все > обновить
Обновление всех данных в книге	CTRL+ALT+F5	Выберите Data > Обновить все
Проверка состояния обновления		Дважды щелкните сообщение "Извлечение данных" в строке состояния.
Остановка обновления	ESC
прервать фоновое обновление.		Дважды щелкните сообщение в строке состояния. Затем в диалоговом окне Состояние обновления внешних данных выберите Остановить обновление.

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

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

Дополнительные сведения об обновлении данных

Сведения о проблемах безопасности

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

Подключения доверия    — Внешние данные в настоящее время могут быть отключены на компьютере. Чтобы обновить данные при открытии книги, необходимо включить подключения к данным с помощью панели Центра управления безопасностью или поместить книгу в надежное расположение. Дополнительные сведения см. в следующих статьях:

• Добавление, удаление или изменение надежного расположения для файлов

• Надежные документы

• Добавление, удаление и просмотр надежного издателя

• Просмотрите мои параметры и параметры в центре управления безопасностью.

ODC-файл    . Файл подключения к данным (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 справа отображается сообщение "Предварительный просмотр скачан в <чч:мм> AM/PM". Это сообщение отображается при первом импорте и после каждой последующей операции обновления в редакторе Power Query.

Обновление запроса на листе

1. В Excel выделите ячейку в запросе на листе.

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

3. Лист и запрос обновляются из внешнего источника данных и кэша Power Query.

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

Обновление вручную

1. Выберите в любом месте сводной таблицы, чтобы отобразить вкладку Анализ сводной таблицы на ленте.

   : Чтобы обновить сводную таблицу в Excel для Интернета, щелкните правой кнопкой мыши в любом месте сводной таблицы и выберите команду Обновить.

2. Выберите Обновить или Обновить все.
   

3. Чтобы проверить состояние обновления, если обновление занимает больше времени, чем ожидалось, щелкните стрелку в разделе Обновление > Состояние обновления.

4. Чтобы остановить обновление, выберите Отмена обновления или нажмите клавишу ESC.

Блокировка изменения ширины столбцов и форматирования ячеек

Если при обновлении сводной таблицы изменяется ширина столбцов и форматирование данных, но вы не хотите, чтобы это происходило, установите вот эти флажки.

1. Выберите в любом месте сводной таблицы, чтобы отобразить вкладку Анализ сводной таблицы на ленте.

2. Перейдите на вкладку Анализ сводной таблицы > в группе Сводная таблица и выберите Параметры.

   

3. На вкладке Макет & Формат > установите флажки Автоподбор ширины столбцов при обновлении и Сохранить форматирование ячейки при обновлении.

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

1. Выберите в любом месте сводной таблицы, чтобы отобразить вкладку Анализ сводной таблицы на ленте.

2. Перейдите на вкладку Анализ сводной таблицы > в группе Сводная таблица и выберите Параметры.

   

3. На вкладке Данные выберите Обновить данные при открытии файла.

Обновление файла автономного куба, при котором этот файл создается повторно с использованием самых свежих данных из серверного куба, может потребовать много времени и места на диске для хранения временных файлов. Запускайте этот процесс тогда, когда вам не нужен немедленный доступ к другим файлам в Excel и у вас достаточно места на диске, чтобы повторно сохранить этот файл.

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

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

Дополнительные сведения см. в статье Работа с автономными файлами куба.

При обновлении модели данных в Power Pivot можно также увидеть, успешно ли выполнено обновление, сбой или отменено. Дополнительные сведения см. в статье Power Pivot: мощный анализ данных и моделирование данных в Excel.

Обновление и просмотр состояния обновления   

1. В Power Pivot выберите Главная > Получить внешние данные > Обновить или Обновить все , чтобы обновить текущую таблицу или все таблицы в модели данных.

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

• Успешно — сообщает о количестве строк, импортированных в каждую таблицу.

• Ошибка . Возникает, если база данных находится в автономном режиме, у вас больше нет разрешений или таблица или столбец удалены или переименованы в источнике. Убедитесь, что база данных доступна, возможно, создав новое подключение в другой книге.

• Отменено — Excel не выдал запрос на обновление, возможно, потому, что обновление отключено при подключении.

Использование свойств таблицы для отображения запросов, используемых при обновлении данных

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

1. Чтобы просмотреть запрос, используемый во время обновления данных, выберите Power Pivot > Управление , чтобы открыть окно Power Pivot.

2. Выберите Конструктор > свойства таблицы.

3. Перейдите в редактор запросов, чтобы просмотреть базовый запрос.

Запросы видны не для всех типов источников данных. Например, запросы не отображаются для импорта веб-канала данных.

Настройка свойств подключения для отмены обновления данных

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

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

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 имеет уникальные требования.

Управление обновлением данных

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

Обновление при открытии с     помощьюслужбы Excel

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

1. В книге с подключениями к внешним данным выберите вкладку Данные .

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

3. Перейдите на вкладку Использование и выберите Обновить данные при открытии файла.

   : Если вы снимите флажок Обновить данные при открытии файла , будут отображаться данные, кэшированные вместе с книгой. Это означает, что при ручном обновлении данных пользователь видит актуальные данные во время текущего сеанса, но данные не сохраняются в книге.

Обновление с помощью ODC-файла

Если вы используете файл подключения к данным Office (ODC), также установите флажок Всегда использовать файл подключения :

1. В книге с подключениями к внешним данным выберите вкладку Данные .

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

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

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

Обновление вручную   

1. Выберите ячейку в отчете сводной таблицы.

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

   : 

   • Если эта команда Обновить не отображается, автор веб-части очистил свойство Обновить выбранное подключение, Обновить все подключения . Дополнительные сведения см. в разделе Настраиваемые свойства веб-части Excel Web Access.

   • Любая интерактивная операция, которая вызывает повторный запрос к источнику данных OLAP, инициирует операцию обновления вручную.

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

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

  Автор веб-части может выбрать или удалить свойство Разрешить периодическое обновление данных в Excel Web Access , чтобы разрешить или запретить периодическое обновление. По истечении интервала времени по умолчанию вы увидите оповещение об обновлении в нижней части веб-части Excel Web Access. 

  Автор веб-части Excel Web Access также может задать свойство Display Периодическое обновление данных Prompt для управления поведением сообщения, которое отображается при периодическом обновлении данных служб Excel во время сеанса:

  Дополнительные сведения см. в разделе Настраиваемые свойства веб-части Excel Web Access.

• Всегда — означает, что сообщение отображается с запросом через каждый интервал.

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

• Никогда — означает, что Excel Web Access выполняет периодическое обновление без отображения сообщения или запроса.

• Отмена обновления . Во время обновления книгислужбы Excel отображает сообщение с запросом, так как это может занять больше времени, чем ожидалось. Вы можете нажать кнопку Отмена , чтобы остановить обновление, чтобы завершить его позже в более удобное время. Будут отображаться данные, возвращаемые запросами до отмены обновления.