Как обновить сводную таблицу в excel если изменил данные
Как обновить сводную таблицу при изменении данных в Excel?
Как вы знаете, если вы изменяете данные в исходной таблице, относительная сводная таблица не обновляет данные в ней в то же время. Если вам нужно обновить сводную таблицу при изменении данных в таблице в Excel, я могу рассказать вам несколько быстрых способов.
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу .
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Обновите сводную таблицу на листе, нажав кнопку «Обновить»
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
В Excel есть функция «Обновить» и «Обновить все» для обновления сводной таблицы на одном листе.
Щелкните сводную таблицу, данные которой нужно обновить, а затем щелкните Опция (в Excel 2013 щелкните АНАЛИЗ)> обновление > обновление. Смотрите скриншот:
Если вы хотите обновить все сводные таблицы на одном листе, вы можете выбрать Обновить все.
Обновить сводную таблицу на листе или в книге с помощью VBA
С помощью VBA вы можете не только обновлять все сводные таблицы на одном листе, но также можете обновлять все сводные таблицы во всей книге.
1. Нажмите F11 + Альт вместе на клавиатуре, чтобы открыть Microsoft Visual Basic для приложений окно.
2. Затем нажмите Вставить > Модули чтобы вставить новое окно модуля. А затем скопируйте следующий код VBA в окно.
VBA: обновить сводные таблицы на листе.
3. Нажмите Run кнопку в окне или клавишу F5 для запуска VBA, то сводные таблицы на листе обновляются.
Функции: Чтобы обновить все сводные таблицы во всей книге, вы можете использовать следующий VBA.
Большой диапазон данных в таблице Excel рационально представить в виде сводного отчета. Структура данного инструмента позволяет получить быстрый доступ к итогам, информации по определенному параметру. Рассмотрим создание и обновление сводных таблиц.
О целесообразности и возможности сводных таблиц
Оптимально формировать сводный отчет на основе исходной таблицы, если она отвечает следующим параметрам:
- содержится несколько сотен строк;
- пользователю нужно представлять одни и те же данные в разных разрезах, выбирать информацию по заданному условию, группировать, а встроенный фильтр и группировка справляются плохо.
Требования к исходной таблице:
- у каждого столбца есть заголовок (первый вариант – неправильный; второй – правильный);
- значения в одном столбце имеют одинаковый формат (число, дата, текст);
- все ячейки в строках и столбцах заполнены значениями;
- данные из одной ячейки нельзя разнести в разные столбцы (без объединения ячеек).
Нерациональная организация информации:
Нельзя будет вывести итоги, например, только по городу.
Лучше значения ввести следующим таким образом.
Как сделать сводную таблицу в Excel
Чтобы создать сводную таблицу в качестве исходного диапазона возьмем каталог учебной литературы:
Подразумевается, что данная таблица состоит из сотен строк. Создадим сводный отчет для выведения списка книг из конкретной категории или определенного года. Задача сформулирована – перейдем к реализации.
- Активизируем любую ячейку в исходном диапазоне – щелкаем мышкой. Переходим на вкладку «Вставка» - «Таблица». Нажимаем кнопку «Сводная таблица».
- Автоматически выделяется весь диапазон. Открывается диалоговое окно инструмента. Необходимо проверить правильность параметров для отчета (диапазон, куда выводить сводную таблицу).
- Открывается окно для построения отчета, список полей. В правой нижней части страницы – области для размещения данных из исходного диапазона.
- Области позволяют сформировать структуру сводного отчета.
- Сначала заполним «Названия строк». Так как нужно вывести список книг по годам, то в этом разделе должен быть перечень названий книг. В списке полей ставим птичку напротив поля «Название». Данные столбца имеют текстовый формат – автоматически попадают в область «Названия строк». К значениям сразу применяется сортировка по алфавиту.
- Теперь ставим птичку напротив поля «Год выпуска».
Сводная таблица сделана. С помощью нескольких кликов. Такой способ представления информации удобен для финансовых отчетов.
Как обновить сводную таблицу в Excel
От сводной таблицы больше пользы, если она динамическая. То есть при внесении новых данных в исходный диапазон поля отчета можно обновить. Как это сделать?
- Когда данные внесены в исходную таблицу, переходим на лист со сводным отчетом и щелкаем в любом его месте правой кнопкой мыши. В открывшемся меню выбираем «Обновить».
- Активизируем нужное поле сводного отчета – становится доступен инструмент «Работа со сводными таблицами». Открываем вкладку «Параметры». В группе «Данные» нажимаем кнопку «Обновить».
- Выделить сводную таблицу или отдельное поле, нажать сочетание клавиш Alt + F5.
Как настроить автоматическое обновление сводной таблицы в Excel:
- Открыть лист со сводным отчетом. Щелкнуть в любом месте таблицы левой кнопкой мыши. Это нужно для того, чтобы активизировалась «Работа со сводными таблицами».
- На вкладке «Параметры» находим группу «Сводная таблица». Нажимаем – открывается меню кнопки. Выбираем пункт «Параметры».
- В открывшемся меню «Параметры сводной таблицы» нажать кнопку «Разметка и формат». Поставить галочки напротив следующих пунктов:
Вы можете в любой момент нажать кнопку Обновить, чтобы обновить данные в сводных таблицах в книге. Можно обновлять значения в сводных таблицах, подключенных к внешним данным, таким как базы данных (SQL Server, Oracle, Access и другие), куб служб Analysis Services, веб-каналы данных и многие другие источники. Вы также можете обновлять данные из исходной таблицы в той же или другой книге. Кроме того, можно настроить автоматическое обновление данных сводной таблицы при открытии книги.
По умолчанию с помощью этой ссылки вы можете указать, что при ее автоматическом обновлении в книге, которая содержит ее, с помощью стеблицы.
Обновление вручную
Щелкните любое место сводной таблицы. На ленте появится вкладка Работа со сводными таблицами.
На вкладке Анализ выберите команду Обновить или нажмите ALT+F5.
Совет: Чтобы одновременно обновить все стебли в книге, нажмите кнопку Анализ > Обновить все.
Если таблица обновляется слишком долго, на вкладке Анализ нажмите стрелку рядом с кнопкой Обновить и выберите пункт Состояние обновления. Так вы сможете проверить состояние обновления.
Чтобы отменить обновление, нажмите Отменить обновление.
Блокировка изменения ширины столбцов и форматирования ячеек
Если при обновлении сводной таблицы изменяется ширина столбцов и форматирование данных, но вы не хотите, чтобы это происходило, установите вот эти флажки.
На вкладке Анализ нажмите кнопку Параметры.
На вкладке Макет и формат установите флажки Автоматически изменять ширину столбцов при обновлении и Сохранять форматирование ячеек при обновлении.
Автоматическое обновление данных при открытии книги
Щелкните любое место сводной таблицы. На ленте появится вкладка Работа со сводными таблицами.
На вкладке Анализ нажмите кнопку Параметры.
На вкладке Данные проверьте поле Обновлять данные при открытии файла.
Обновление вручную
Щелкните в любом месте сводной таблицы.
Отобразит вкладку "Параметры" и "Конструктор" в области "Инструменты для работы со таблицами".
На вкладке Параметры в группе Данные сделайте следующее:
Чтобы обновить данные в соответствие с источником данных, нажмите кнопку Обновить или нажмите клавиши ALT+F5.
Вы также можете щелкнуть правой кнопкой мыши и нажать кнопку Обновить.
Чтобы обновить все стебли в книге, нажмите кнопку Обновить и выберите обновить все.
Если обновление занимает больше времени, чем вы ожидаете, щелкните Параметры > обновить > Состояние обновления, чтобы проверить состояние обновления.
Чтобы отменить обновление, нажмите Отменить обновление.
Блокировка изменения ширины столбцов и форматирования ячеек
Если при обновлении сводной таблицы изменяется ширина столбцов и форматирование данных, но вы не хотите, чтобы это происходило, установите вот эти флажки.
Щелкните Параметры > параметры.
На вкладке Макет и формат установите флажки Автоматически изменять ширину столбцов при обновлении и Сохранять форматирование ячеек при обновлении.
Автоматическое обновление данных в pivottable при открытии книги
Щелкните в любом месте сводной таблицы.
На вкладке Параметры в группе Сводная таблица нажмите кнопку Параметры.
В диалоговом окне Параметры таблицы на вкладке Данные выберите параметр Обновлять данные при открытии файла.
При добавлении новых данных в источник необходимо обновить все основанные на нем сводные таблицы. Чтобы обновить сводную таблицу, можно щелкнуть правой кнопкой мыши в любом месте ее диапазона и выбрать команду Обновить
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
После создания сводной таблицы можно изменить диапазон исходных данных. Например, расширить его и включить дополнительные строки данных. Однако если исходные данные существенно изменены, например содержат больше или меньше столбцов, рекомендуется создать новую сводную таблицу.
Вы можете изменить источник данных для таблицы Excel на другую таблицу или диапазон ячеок либо другой внешний источник данных.
Щелкните Отчет сводной таблицы.
На вкладке "Анализ" в группе "Данные" нажмите кнопку "Изменить источник данных" и выберите "Изменить источник данных".
Отобразилось диалоговое окно "Изменение источника данных в pivotTable".
Выполните одно из указанных ниже действий.
Чтобы изменить источник данных для таблицы Excel на другую таблицу или диапазон ячеок, щелкните "Выбрать таблицу или диапазон", а затем введите первую ячейку в текстовом поле "Таблица или диапазон" и нажмите кнопку "ОК".
Чтобы использовать другое подключение, сделайте следующее:
Выберите "Использовать внешний источник данных" инажмите кнопку "Выбрать подключение".
Отобразилось диалоговое окно "Существующие подключения".
В списке "Показать" в верхней части диалоговых окнах выберите категорию подключений, для которых нужно выбрать подключение, или выберите "Все существующие подключения" (значение по умолчанию).
Выберите подключение в списке "Выберите подключение" и нажмите кнопку "Открыть".Что делать, если подключения нет в списке?
Примечание: При выборе подключения из категории "Подключения" в этой категории будет повторное использование или совместное использование существующего подключения. Если выбрать подключение из файлов подключения в сети или файлов подключения в этой категории компьютеров, файл подключения будет скопирован в книгу как новое подключение к книге, а затем использован в качестве нового подключения для отчета pivottable.
Что делать, если подключения нет в списке?
Если подключения нет в диалоговом окне "Существующие подключения", нажмите кнопку "Обзор дополнительных данных" и найдите нужный источник данных в диалоговом окне "Выбор источника данных". Если необходимо, щелкните Создание источника и выполните инструкции мастера подключения к данным, а затем вернитесь в диалоговое окно Выбор источника данных.
Если сводная таблица основана на подключении к диапазону или таблице в модели данных, сменить таблицу модели или подключение можно на вкладке Таблицы. Если же сводная таблица основана на модели данных книги, сменить источник данных невозможно.
Выберите нужное подключение и нажмите кнопку Открыть.
Выберите вариант Только создать подключение.
Щелкните пункт Свойства и выберите вкладку Определение.
Если файл подключения (ODC-файл) был перемещен, найдите его новое расположение в поле Файл подключения.
Если необходимо изменить значения в поле Строка подключения, обратитесь к администратору базы данных.
Щелкните Отчет сводной таблицы.
На вкладке "Параметры" в группе "Данные" нажмите кнопку "Изменить источник данных" и выберите "Изменить источник данных".
Отобразилось диалоговое окно "Изменение источника данных в pivotTable".
Выполните одно из указанных ниже действий.
Чтобы использовать другую таблицу или диапазон ячеок Excel, щелкните "Выбрать таблицу или диапазон", а затем введите первую ячейку в текстовом поле "Таблица или диапазон".
Вы также можете нажать кнопку "Свернуть ", чтобы временно скрыть диалоговое окно, выбрать ячейку в начале, а затем нажать кнопку "Развернуть ".
Чтобы использовать другое подключение, выберите "Использовать внешний источник данных" и нажмите кнопку "Выбрать подключение".
Отобразилось диалоговое окно "Существующие подключения".
В списке "Показать" в верхней части диалоговых окнах выберите категорию подключений, для которых нужно выбрать подключение, или выберите "Все существующие подключения" (значение по умолчанию).
Выберите подключение в списке "Выберите подключение" и нажмите кнопку "Открыть".
Примечание: При выборе подключения из категории "Подключения" в этой категории будет повторное использование или совместное использование существующего подключения. Если выбрать подключение из файлов подключения в сети или файлов подключения в этой категории компьютеров, файл подключения будет скопирован в книгу как новое подключение к книге, а затем использован в качестве нового подключения для отчета pivottable.
Что делать, если подключения нет в списке?
Если подключения нет в диалоговом окне "Существующие подключения", нажмите кнопку "Обзор дополнительных данных" и найдите нужный источник данных в диалоговом окне "Выбор источника данных". Если необходимо, щелкните Создание источника и выполните инструкции мастера подключения к данным, а затем вернитесь в диалоговое окно Выбор источника данных.
Если сводная таблица основана на подключении к диапазону или таблице в модели данных, сменить таблицу модели или подключение можно на вкладке Таблицы. Если же сводная таблица основана на модели данных книги, сменить источник данных невозможно.
Выберите нужное подключение и нажмите кнопку Открыть.
Выберите вариант Только создать подключение.
Щелкните пункт Свойства и выберите вкладку Определение.
Если файл подключения (ODC-файл) был перемещен, найдите его новое расположение в поле Файл подключения.
Если необходимо изменить значения в поле Строка подключения, обратитесь к администратору базы данных.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Вы узнали, как суммировать данные с помощью сводной таблицы. Данные, на которых основана сводная таблица, могут обновляться либо периодически, либо при возникновении события. Кроме того, вам также может потребоваться изменить макет сводной таблицы для разных отчетов.
В этой главе вы узнаете о различных способах обновления макета и / или обновления данных в сводной таблице.
Обновление макета сводной таблицы
Вы можете решить, будет ли обновляться сводная таблица всякий раз, когда вы вносите изменения в макет, или она должна обновляться с помощью отдельного триггера.
Как вы узнали ранее, в области задач Поля сводных таблиц в нижней части вы найдете флажок для отложенного обновления макета. По умолчанию этот флажок снят, что означает, что макет сводной таблицы обновляется, как только вы вносите изменения в области сводной таблицы.
Установите флажок « Отложить обновление макета» .
Кнопка UPDATE рядом с ней будет включена. Если вы внесете какие-либо изменения в области сводной таблицы, эти изменения будут отражены только после нажатия кнопки ОБНОВЛЕНИЕ.
Обновление данных сводной таблицы
Когда данные сводной таблицы изменяются в ее источнике, то же самое можно отразить в сводной таблице, обновив ее.
- Нажмите на сводную таблицу.
- Нажмите вкладку ANALYZE на ленте.
- Нажмите Обновить в группе данных.
Существуют различные варианты обновления данных в раскрывающемся списке.
Обновить – Получить последние данные из источника, подключенного к активной ячейке.
Обновить все – для получения последних данных путем обновления всех источников в рабочей книге.
Свойства соединения – установка свойств обновления для соединений книги.
Обновить – Получить последние данные из источника, подключенного к активной ячейке.
Обновить все – для получения последних данных путем обновления всех источников в рабочей книге.
Свойства соединения – установка свойств обновления для соединений книги.
Изменение исходных данных сводной таблицы
Вы можете изменить диапазон исходных данных сводной таблицы. Например, вы можете расширить исходные данные, включив в них большее количество строк данных.
Однако, если исходные данные были существенно изменены, например, имеется больше или меньше столбцов, рассмотрите возможность создания новой сводной таблицы.
Нажмите на сводную таблицу. НА ЛЕНТЕ появляются СВОБОДНЫЕ ИНСТРУМЕНТЫ .
Нажмите на вкладку АНАЛИЗ.
Нажмите Изменить источник данных в группе данных.
Нажмите на сводную таблицу. НА ЛЕНТЕ появляются СВОБОДНЫЕ ИНСТРУМЕНТЫ .
Нажмите на вкладку АНАЛИЗ.
Нажмите Изменить источник данных в группе данных.
Выберите Изменить источник данных в раскрывающемся списке.
Откроется диалоговое окно «Изменить источник данных сводной таблицы», и текущий источник данных будет выделен.
Выберите таблицу или диапазон, который хотите включить в поле «Таблица / диапазон» в разделе «Выбор таблицы или диапазона». Нажмите ОК.
Источник данных для сводной таблицы будет изменен на выбранную таблицу / диапазон данных.
Переход на внешний источник данных
Если вы хотите изменить внешний источник данных для сводной таблицы, лучше всего создать новую сводную таблицу. Однако, если расположение внешнего источника данных изменяется, например, имя базы данных SQL Server такое же, но оно было перемещено на другой сервер, или ваша база данных Access была перемещена в другой сетевой ресурс, вы можете изменить ваше текущее подключение к данным, чтобы отражать то же самое.
Нажмите на сводную таблицу.
Нажмите вкладку ANALYZE на ленте.
Нажмите Изменить источник данных в группе данных. Откроется диалоговое окно « Изменить источник данных сводной таблицы ».
Нажмите на сводную таблицу.
Нажмите вкладку ANALYZE на ленте.
Нажмите Изменить источник данных в группе данных. Откроется диалоговое окно « Изменить источник данных сводной таблицы ».
Откроется диалоговое окно « Существующие подключения ».
Выберите Все соединения в поле Показать. Все Соединения в вашей Рабочей книге будут отображены.
Выберите Все соединения в поле Показать. Все Соединения в вашей Рабочей книге будут отображены.
Откроется окно выбора источника данных .
- Нажмите на кнопку «Новый источник».
- Пройдите шаги мастера подключения к данным.
Если ваш источник данных находится в другой книге Excel, выполните следующие действия:
- Нажмите на поле Имя файла.
- Выберите имя файла книги.
Удаление сводной таблицы
Вы можете удалить сводную таблицу следующим образом:
- Нажмите на сводную таблицу.
- Нажмите вкладку ANALYZE на ленте.
- Нажмите «Выбрать» в группе «Действия».
В раскрывающемся списке выберите « Вся сводная таблица». Вся сводная таблица будет выбрана.
Нажмите клавишу удаления. Сводная таблица будет удалена.
Если сводная таблица находится на отдельной рабочей таблице, вы также можете удалить сводную таблицу, удалив всю рабочую таблицу.
Щелкните правой кнопкой мыши вкладку листа и выберите « Удалить» в раскрывающемся списке.
Читайте также: