Как обновить страницу Excel

Обновлено: 03.07.2024

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

Введение

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

Ниже приведены шаги, показывающие, как обновить сводную таблицу:

• вручную
• автоматически при открытии файла
• автоматически по таймеру (для подключений)
• автоматически с помощью макроса

ПРИМЕЧАНИЕ. Когда вы обновляете сводную таблицу, обновляется ее кеш. ВСЕ сводные таблицы, использующие один и тот же кэш сводных данных, также будут обновлены.

Обновить вручную

Быстрый и простой способ обновить сводную таблицу после изменения данных — обновить ее вручную:

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

Сводная таблица на защищенном листе

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

Вот как обойти эту проблему:

• Вручную: если вы знаете пароль, вручную снимите защиту с листа, обновите сводную таблицу, а затем снова защитите лист.
• Макросы. Если вы пытаетесь обновить сводную таблицу с помощью макроса, добавьте код для снятия защиты с листа, обновите сводную таблицу, а затем снова защитите лист. На листе защиты сводной таблицы есть примеры.

Предупреждающее сообщение

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

• Эта команда не может быть выполнена, пока защищенный лист содержит другой отчет сводной таблицы, основанный на тех же исходных данных.
  Чтобы снять защиту с листа, содержащего другой отчет, щелкните вкладку листа, а затем щелкните Снять защиту с листа (вкладка "Рецензирование", группа "Изменения"). Затем повторите команду.

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

Если вы выполняете обновление с помощью макросов, этот макрос показывает, как снять защиту со всех листов, обновить сводные таблицы, а затем снова защитить листы.

Обновить при открытии файла

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

Для этого установите параметр сводной таблицы для автоматического обновления сводной таблицы:

• Щелкните правой кнопкой мыши ячейку в сводной таблице и выберите "Параметры сводной таблицы".
• В разделе "Параметры данных" установите флажок "Обновлять данные при открытии файла".
• Нажмите "ОК", чтобы сохранить настройку параметра.

Обновлять по таймеру

Для сводных таблиц на основе OLAP вы также можете обновлять на основе таймера в их соединении.

ПРИМЕЧАНИЕ. Когда вы создаете сводную таблицу, если вы добавляете ее данные в модель данных, ваша сводная таблица основана на OLAP.

Чтобы установить автоматическое обновление таймера подключения, выполните следующие действия:

• На ленте Excel перейдите на вкладку "Данные".
• Нажмите "Запросы и подключения".
• На панели "Запросы и подключения" перейдите на вкладку "Подключения".
• Щелкните правой кнопкой мыши WorksheetConnection и выберите "Свойства"

  Обновить с помощью макросов

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

  Добавьте следующий макрос в модуль кода листа сводной таблицы. См. инструкции по копированию кода VBA в собственные файлы.

  ПРИМЕЧАНИЕ. Если вы пытаетесь обновить сводные таблицы на защищенных листах, на листе защиты сводных таблиц есть примеры макросов.

  Макрос для одной сводной таблицы

  Поместите следующий код в модуль рабочего листа, если на листе есть только 1 сводная таблица. Это автоматически обновит сводную таблицу, как только вы перейдете к ее рабочему листу.

  Макрос для нескольких сводных таблиц

  Если на листе есть несколько сводных таблиц с разными исходными данными, используйте этот код, вставленный в модуль рабочего листа. Это автоматически обновит все сводные таблицы на листе:

  Обновить несколько сводных кэшей

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

  Вместо этого вы можете использовать кнопку "Обновить все".

  Примечание. Использование команды "Обновить все" также обновляет все диапазоны внешних данных в активной книге и влияет как на видимые, так и на скрытые листы в активной книге.

  Чтобы одновременно обновить все сводные таблицы и диапазоны внешних данных в активной книге:

  • На ленте откройте вкладку "Данные".
  • В группе "Подключения" щелкните верхнюю часть команды "Обновить все".

  СОВЕТ. Вы можете добавить кнопку "Обновить все" на панель быстрого доступа, чтобы ее было проще использовать.

  
  

  Остановить обновление

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

  клавиша Esc

  Чтобы остановить длительное обновление, нажмите клавишу Esc на клавиатуре.

  Строка состояния

  Если обновление выполняется как фоновый запрос, выполните следующие действия, чтобы остановить обновление:

  • Нажмите на индикатор "Обновить" в строке состояния.

Не удалось получить данные об ошибке

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

Вот отредактированная версия этого сообщения, часть текста перемещена, чтобы вы могли прочитать его полностью.

А вот текстовая версия сообщения на случай, если кому-то понадобится помощь с этим сообщением об ошибке:

• Не удалось получить данные из модели данных. Вот сообщение об ошибке, которое мы получили:
• Произошла непредвиденная ошибка (файл 'pcminorobjcoll.inl', строка 109, функция PCMinorObjectCollection ::SetNameAndUpdateCollection')

ПРИМЕЧАНИЕ. Другие типы сообщений об ошибках сводной таблицы см. на странице "Ошибки сводной таблицы".

Когда появилось это сообщение об ошибке?

Это сообщение об ошибке появилось после того, как один из заголовков исходных данных был изменен с ПРОПИСНОГО регистра на правильный регистр. Это создало второй экземпляр поля в модели данных, который можно было увидеть в списке полей сводной таблицы.

Решить проблему

Чтобы решить эту проблему, если вы видите это сообщение об ошибке:

• В таблице исходных данных верните заголовок к исходному регистру — я изменил День обратно на ДЕНЬ.
• Затем обновите сводную таблицу.
• Дополнительное поле должно исчезнуть из списка полей сводной таблицы.

Избегайте проблемы

Чтобы избежать этой ошибки, если вы хотите изменить регистр заголовка поля:

• В исходной таблице данных измените регистр заголовка.
• На вкладке "Данные" ленты Excel нажмите команду "Управление моделью данных".
• В окне Power Pivot для Excel на вкладке "Главная" нажмите команду "Дизайн".
• На диаграмме щелкните правой кнопкой мыши имя поля, которое вы изменили, и выберите "Переименовать"

  ПРИМЕЧАНИЕ. Это краткое описание ошибки и ее исправления. Более длинную версию с дополнительными снимками экрана и подробностями можно найти в статье в моем блоге Contextures Excel.

  Загрузить образец файла

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

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

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

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

  В этой статье есть ДВА разных способа принудительного обновления в Excel. Один из них — это «традиционный» подход к использованию макросов, а другой — более новый прием, использующий PowerQuery в сочетании с одним макросом.

  Любой вариант дает вам больший контроль над обновлением, чем упрощенное добавление Microsoft к Excel 365 с пометкой.

  Тип биржевых данных

  Для типа биржевых данных возможность автоматического обновления важнее. Пользователи захотят, чтобы их рабочие листы автоматически получали последние цены, чего не могут сделать текущие предварительные выпуски. Вместо красивого автоматического «тикера» мы должны нажать «Обновить», чтобы получить последние цены.

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

  NOW() и другие изменчивые функции

  Функция СЕЙЧАС() обновляется до последней даты и времени всякий раз, когда Excel пересчитывает рабочий лист. Но если этого не происходит, Now() не меняет значение. Некоторый внешний фактор необходим, чтобы Excel обновил Now() и остальную часть рабочего листа. Другими словами, вы должны иметь возможность взглянуть на рабочий лист и знать, что это до секунды, но это невозможно с Excel «из коробки».

  Microsoft называет NOW() и подобные функции "изменчивыми", поскольку их значения могут измениться, даже если никакие другие ячейки не изменились. Другими изменчивыми функциями являются Today(), Randbetween(), Offset() и Indirect(). В некоторых ситуациях Info(), Cell() и SumIf() также могут быть непостоянными.

  Пользовательские функции VBA также можно пометить как volatile с помощью этой строки в коде функции:

  Эта строка запускает функцию каждый раз, когда Excel обновляет/пересчитывает рабочий лист.

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

  Дополнительная осторожность

  Возможно, вы хотите, чтобы лист автоматически обновлялся в качестве меры предосторожности? Многие старые пользователи Excel помнят ситуации, когда Excel не обновлялся должным образом, поэтому им нравится подход «пояс и скобки» (по крайней мере, иногда).

  Код автоматического обновления

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

  
  

  Есть три функции.

  Обновить все данные

  выполняет фактическое обновление подключений к данным ( Workbooks(ThisWorkbook.Name).RefreshAll ), и мы добавили две необязательные строки для отображения времени последнего обновления в нижней строке состояния.

  Если вы хотите быть особенно осторожным, добавьте строку, явно вызывающую пересчет. Либо ActiveSheet.Calculate, либо экстремальный Application.CalculateFull (используйте экономно, это замедлит работу с большим рабочим листом).

  Автообновление

  запускать подпрограмму RefreshAllDataConn каждую минуту или любое другое значение, которое вы установили в строке Application.OnTime Now + TimeValue("00:01:00"), "AutoRefresh"

  Рабочая_книга_Открыть

  встроенная функция Excel, которая запускается автоматически при открытии рабочего листа. В этом случае запускается автообновление.

  Обходной путь подключения к данным PowerQuery

  Недостаток подхода VBA заключается в том, что необходим рабочий лист .xlsm (рабочий лист Excel с поддержкой макросов). Могут возникнуть проблемы с совместным использованием файлов с поддержкой макросов из соображений безопасности.

  Появление PowerQuery/Get and Transform означает, что появился еще один способ принудительного пересчета рабочего листа. Это обходной путь, и он не идеален, но возможен и не требует рабочего листа с поддержкой макросов.

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

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

  В идеале типы данных Stock и Geo также должны обновляться.

  Любой запрос данных Excel поставляется с некоторыми параметрами обновления в свойствах запроса. Большинство из них по умолчанию отключены.

  
  

  Обновлять каждые nnn минут — по умолчанию отключено, предлагается 60 минут.

  Обновлять данные при открытии файла

  Включить фоновое обновление

  Обновить это подключение в Обновить все

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

  Некоторые версии этого временного решения добавляют ссылку на крошечный CSV-файл на том же компьютере. Мы получили тот же результат, используя подключение к данным из таблицы на листе.

  Создайте небольшую таблицу с одной ячейкой. В ячейке может быть что угодно, но мы создаем в ней ячейку с NOW() по причинам, которые мы объясним позже.

  
  

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

  
  

  Когда откроется редактор запросов, просто закройте и загрузите его. На панели подключений к данным вы увидите запрос.

  
  

  Щелкните запрос правой кнопкой мыши, выберите свойства, чтобы увидеть нужные параметры.

  
  

  Установите подходящую вам частоту обновления.

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

  В этом руководстве мы рассмотрим, как пересчитывать и обновлять формулы в Microsoft Excel.

  Мы сделаем это с помощью функций "Рассчитать сейчас" и "Рассчитать лист" в приложении.

  Мы также рассмотрим использование сочетаний клавиш!

  *Это руководство предназначено для Excel 2019/Microsoft 365 (для Windows). Есть другая версия? Нет проблем, вы все равно можете выполнить те же шаги.

  
  

  Каспер Лангманн, соучредитель Spreadsheeto

  Оглавление

  Зачем нужно пересчитывать и обновлять?

  Excel автоматически вычисляет все формулы на листе.

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

  Вы можете потратить больше времени на ожидание завершения работы Excel, чем на обновление или ввод информации.

  Чтобы решить эту проблему, вы можете установить параметр расчета с Автоматически на Вручную. Затем вы можете использовать функции приложения для ручного пересчета формул.

  Два способа установить расчет вручную

  Для первого метода перейдите на вкладку "Файл" на ленте и выберите "Параметры".

  
  

  Когда откроется диалоговое окно "Параметры Excel", выберите "Формулы" в списке параметров слева.

  Выберите «Вручную» в параметрах расчета.

  
  

  Для второго метода перейдите в группу "Расчет" на вкладке "Формулы".

  
  

  Каспер Лангманн, соучредитель Spreadsheeto

  Нажмите стрелку вниз для параметра "Параметры расчета" и убедитесь, что вы выбрали "Вручную".

  
  

  Получите БЕСПЛАТНЫЙ файл с упражнениями

  Прежде чем начать:

  В этом руководстве вам понадобится набор данных для практики.

  Я включил один для вас (бесплатно).

  Загрузите прямо ниже!

  БОНУС: загрузите файл рабочей тетради Recalculate and Refresh Formulas, чтобы сопровождать этот пост.

  Как пересчитать и обновить формулы

  Теперь ваши формулы больше не будут обновляться автоматически. Вам нужно знать, как пересчитывать и обновлять формулы, чтобы изменения вступили в силу.

  Первым шагом для пересчета является переход в группу "Расчет" на вкладке "Формулы".

  Затем вы нажимаете на один из вариантов расчета, где вы можете выбрать один из двух вариантов.

  Первый вариант — "Рассчитать сейчас". Этот вариант позволяет рассчитать всю книгу.

  
  

  Второй вариант — «Вычислить лист» — этот параметр будет вычислять активный рабочий лист.

  
  

  Есть также несколько сочетаний клавиш для повышения эффективности при обновлении вычислений.

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

  Заранее спасибо.

  Факты об Excel

  за кадром

  Известный участник

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

  Поместите имя листа в соответствии с требованиями.
  Если обновление не вызывает событие изменения рабочего листа, то вы можете поместить уравнение в запасную ячейку, которая использует одну из ячеек, которая обновляется внешним источником, и выполняет расчет на ней, например
  предполагая, что A1 обновляется автоматически и что это текст, вы можете поместить это уравнение в Z100
  =Len(A1)
  Это вызовет событие вычисления рабочего листа, поэтому вы можете поместить приведенный выше код в это событие рабочего листа, если оно вам все еще нужно. функция все равно должна была сделать это за вас.

  шина67

  Обычная доска

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

  Поместите имя листа в соответствии с требованиями.
  Если обновление не вызывает событие изменения рабочего листа, то вы можете поместить уравнение в запасную ячейку, которая использует одну из ячеек, которая обновляется внешним источником, и выполняет расчет на ней, например
  предполагая, что A1 обновляется автоматически и что это текст, вы можете поместить это уравнение в Z100
  =Len(A1)
  Это вызовет событие вычисления рабочего листа, поэтому вы можете поместить приведенный выше код в это событие рабочего листа, если оно вам все еще нужно. функция все равно должна была сделать это за вас.

  Мой код выглядит следующим образом:-

  Sub Macro1()
  '
  ' Macro1 Macro
  '
  ' Сочетание клавиш: Ctrl+q
  '

  Читайте также:

    
  • Как удалить звук из видео в Adobe Premiere Pro
    
  • Установить программу zone для скачивания фильмов бесплатно для windows 10
    
  • Видео не встраивается в Adobe Premiere Pro
    
  • Приложение для поиска рыбы для телефона
    
  • Программа для скачивания музыки с одноклассников на телефон