Почему не обновляются данные в сводной таблице

Представление и обновление данных в сводных таблицах: подробная инструкция с картинками. Учимся обновлять отчет при внесении новых данных в исходный диапазон. Настраиваем автоматическое обновление.

Как обновить сводную таблицу в Google Таблицах

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

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

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

О целесообразности и возможности сводных таблиц

Оптимально формировать сводный отчет на основе исходной таблицы, если она отвечает следующим параметрам:

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

Требования к исходной таблице:

  • у каждого столбца есть заголовок (первый вариант – неправильный; второй – правильный);Заголовки.
  • значения в одном столбце имеют одинаковый формат (число, дата, текст);
  • все ячейки в строках и столбцах заполнены значениями;
  • данные из одной ячейки нельзя разнести в разные столбцы (без объединения ячеек).

Нерациональная организация информации:

Информация.

Нельзя будет вывести итоги, например, только по городу.

Рационально.

Лучше значения ввести следующим таким образом.



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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

Обновление макета сводной таблицы

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

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

обновление

Установите флажок « Отложить обновление макета» .

Кнопка UPDATE рядом с ней будет включена. Если вы внесете какие-либо изменения в области сводной таблицы, эти изменения будут отражены только после нажатия кнопки ОБНОВЛЕНИЕ.

Отложить обновление макета

Кнопка “Обновить все” на панели инструментов

Кнопка “Обновить все” – самый простой способ обновить все сводные таблицы в файле одновременно. Она находится на панели инструментов в разделе “Данные”:

Как обновить несколько сводных таблиц в Excel файле

Все что требуется сделать, это перейти во вкладку “Данные” => раздел “Запросы и подключения” = >  “Обновить все”.

Обновление данных сводной таблицы

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

  • Нажмите на сводную таблицу.
  • Нажмите вкладку ANALYZE на ленте.
  • Нажмите Обновить в группе данных.

освежение

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

  • Обновить — Получить последние данные из источника, подключенного к активной ячейке.

  • Обновить все — для получения последних данных путем обновления всех источников в рабочей книге.

  • Свойства соединения — установка свойств обновления для соединений книги.

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

Обновить все — для получения последних данных путем обновления всех источников в рабочей книге.

Свойства соединения — установка свойств обновления для соединений книги.

Как сделать сводную таблицу в Excel

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

Каталог.

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

  1. Активизируем любую ячейку в исходном диапазоне – щелкаем мышкой. Переходим на вкладку «Вставка» – «Таблица». Нажимаем кнопку «Сводная таблица».Вставка.
  2. Автоматически выделяется весь диапазон. Открывается диалоговое окно инструмента. Необходимо проверить правильность параметров для отчета (диапазон, куда выводить сводную таблицу).Параметры.
  3. Открывается окно для построения отчета, список полей. В правой нижней части страницы – области для размещения данных из исходного диапазона.Настройки.
  4. Области позволяют сформировать структуру сводного отчета.Структура.
  5. Сначала заполним «Названия строк». Так как нужно вывести список книг по годам, то в этом разделе должен быть перечень названий книг. В списке полей ставим птичку напротив поля «Название». Данные столбца имеют текстовый формат – автоматически попадают в область «Названия строк». К значениям сразу применяется сортировка по алфавиту.Название.
  6. Теперь ставим птичку напротив поля «Год выпуска».

Год выпуска.

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

Почему не обновляется сводная таблица?

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

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

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

Проблема 1: сводная таблица не обновляется при добавлении новой строки

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

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

Решение

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

Например, если ваши данные в настоящее время занимают более 20 строк, вы можете создать сводную таблицу с 1000 строками. Дополнительные 980 строк могут оставаться пустыми, и вы можете позже заполнить их данными по мере необходимости.

Теперь, пока ваши данные находятся в пределах 1000 строк, сводная таблица будет обновляться автоматически.

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

Проблема: ваша сводная таблица использует фильтры

Если в сводной таблице используются фильтры, ваши данные не будут обновляться при изменении исходных значений данных. Это ограничение возможностей сводной таблицы Google Таблиц.

Решение

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

  • Нажмите на крестик рядом со всеми полями в категории «Фильтры» в редакторе сводной таблицы.
  • Внесите необходимые изменения в исходный набор данных.
  • Теперь изменения должны быть отражены в сводной таблице.
  • Когда вы закончите вносить изменения, добавьте фильтры обратно, используя кнопку «Добавить» в категории «Фильтры» (в редакторе сводной таблицы).

Проблема: сводная таблица не обновляется, если исходный набор данных содержит такие функции, как RANDOM, TODAY и т. Д.

Сводная таблица плохо работает с функциями, требующими обновления. Поэтому, если ваши исходные данные содержат такие функции, как RANDOM, TODAY и т. Д., То изменения исходных данных не будут обновлены в сводной таблице.

Решение

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

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

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

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

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

Надеюсь, вы нашли этот урок полезным.

Get expert help now

Don’t have time to figure this out? Our expert partners at Excelchat can do it for you, 24/7.

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

Необходимо выделить ячейку в сводной таблице, содержимое которой необходимо обновить.

После этого нажмите кнопку “Обновить” данные на панели инструментов Сводные таблицы или же в пункте меню “Данные” “обновить данные”. После этого произойдет автоматическое обновление ячейки.

image004.png

Рисунок 4 Значок «Обновить»

Чтобы сводная таблица обновлялась при открытии книги, выберите команду Параметры в меню “Сводная таблица” на панели инструментов “Сводные таблицы”. Установите флажок “обновить” при открытии в группе “Источник”.

Если сводная таблица создана на основе данных, находящихся в списке Microsoft Excel, и происходит добавление новых полей в исходный диапазон, добавить эти поля после обновления таблицы можно с помощью мастера сводных таблиц. Для этого выделите ячейку сводной таблицы, нажмите кнопку “Мастер сводных таблиц” на панели инструментов “Сводные таблицы” и перетащите новые поля в область сведения.

Чтобы обновить все сводные таблицы в книге, на панели инструментов “Сводные таблицы” выберите команду ” Обновить данные” в меню “Сводная таблица”.

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

Для отключения обновления сводной таблицы при открытии файла выделите ячейку сводной таблицы, для которой обновление не будет происходить при открытии. Далее на панели инструментов “Сводные таблицы” выберите команду “Параметры” в меню “Сводная таблица”.

После этого в появившемся окне снимите флажок “обновить при открытии”.

image005.jpg

Рисунок 5 Параметры данных сводной таблицы

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

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

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

  • Кликните правой кнопкой мыши на любой сводной таблице файла
  • В выпадающем меню выберите пункт “Параметры сводной таблицы”:

Как обновить несколько сводных таблиц в Excel файле

  • В появившемся диалоговом окне “Параметры сводной таблицы” перейдите на вкладку “Данные” и отметьте галочкой пункт “Обновить при открытии файла”:

Как обновить несколько сводных таблиц в Excel файле

  • Нажмите “ОК”
  • После этого Excel сообщит вам в диалоговом окне о том, что примененные настройки будут распространены на параметры других отчетов сводных таблиц, основанных на этих же исходных данных. Другими словами, если ваши сводные таблицы построены на одном и том же массиве исходных данных, то такую настройку не нужно осуществлять на других сводных таблицах вашего файла.

Как обновить несколько сводных таблиц в Excel файле

Овладеть всеми навыками работы со сводными таблицами вы можете на моем курсе “Сводные таблицы в Excel“!

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!

Удаление сводной таблицы

Вы можете удалить сводную таблицу следующим образом:

  • Нажмите на сводную таблицу.
  • Нажмите вкладку ANALYZE на ленте.
  • Нажмите «Выбрать» в группе «Действия».

Удаление

В раскрывающемся списке выберите « Вся сводная таблица». Вся сводная таблица будет выбрана.

Вся сводная таблица

Нажмите клавишу удаления. Сводная таблица будет удалена.

Удалить ключ

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

Щелкните правой кнопкой мыши вкладку листа и выберите « Удалить» в раскрывающемся списке.

Выберите Удалить

Весь рабочий лист вместе со сводной таблицей будет удален.

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...