Как сделать refresh в excel

Обновлено: 05.07.2024

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

Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу . Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Обновите сводную таблицу на листе, нажав кнопку «Обновить»

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

В Excel есть функция «Обновить» и «Обновить все» для обновления сводной таблицы на одном листе.

Щелкните сводную таблицу, данные которой нужно обновить, а затем щелкните Опция (в Excel 2013 щелкните АНАЛИЗ)> обновление > обновление. Смотрите скриншот:

Если вы хотите обновить все сводные таблицы на одном листе, вы можете выбрать Обновить все.

Обновить сводную таблицу на листе или в книге с помощью VBA

С помощью VBA вы можете не только обновлять все сводные таблицы на одном листе, но также можете обновлять все сводные таблицы во всей книге.

1. Нажмите F11 + Alt вместе на клавиатуре, чтобы открыть Microsoft Visual Basic для приложений окно.

2. Затем нажмите Вставить > Модуль чтобы вставить новое окно модуля. А затем скопируйте следующий код VBA в окно.

VBA: обновить сводные таблицы на листе.

3. Нажмите Запустите кнопку в окне или клавишу F5 для запуска VBA, то сводные таблицы на листе обновляются.

Чаевые: Чтобы обновить все сводные таблицы во всей книге, вы можете использовать следующий VBA.

Многие, кто работал со сводными таблицами наверняка знают, что при изменении значений в исходных данных(данные, на основании которых создана сводная) сама сводная таблица при этом не обновляется. Чтобы обновить надо проделать еще пару манипуляций:

Выделить любую ячейку сводной таблицы→Правая кнопка мыши→Обновить (Refresh) или вкладка Данные (Data) →Обновить все (Refresh all) →Обновить (Refresh)

Однако, если в конец исходных данных добавить строку(или несколько), то с большой долей вероятности даже обновление сводной таблицы не поможет - добавленная строка не появится в сводной. И чтобы её увидеть необходимо будет изменить источник данных для сводной таблицы, включив новую строку в диапазон. Не очень удобно, не правда ли? Чтобы добиться расширения диапазона исходных данных автоматически вместе с добавлением туда данных, лучше позаботиться об этом до создания сводной таблицы.

Недоавтообновление
Почему "недо" - жать кнопку Обновить все же придется. Но не отчаивайтесь - читайте до конца и мы научимся обновлять все автоматом.
Для счастливых обладателей Excel 2007 и старше есть простой способ без лишних телодвижений. Это встроенный инструмент Таблица (Table) . Его еще иначе называют "умная таблица" и я тоже буду применять этот термин, чтобы не было путаницы.
Умная таблица - это специальный объект, который представляет собой правильную таблицу с заголовками, которая расширяется по мере добавления в неё данных. В ней много еще чего полезного, но нас интересует сейчас именно то, что она расширяется сама по мере добавления данных и что на её основе можно создать сводную таблицу. В нашем случае она будет играть роль динамического именованного диапазона(стандартный именованный диапазон не может быть источником данных для сводной таблицы, поэтому и приходится идти другими путями). Чтобы создать такую таблицу необходимо:

выделить таблицу исходных данных для создания сводной таблицы -перейти на вкладку Вставка (Insert) и выбрать Таблица (Table)
В появившемся окне согласиться с указанным диапазоном или выбрать свой. Галочку Таблица с заголовками (My table has headers) при этом надо обязательно оставить включенной:

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

А дальше все как привыкли:

Выделить любую ячейку исходной таблицы(теперь уже "умной")
Вкладка Вставка (Insert) -группа Таблица (Table) -Сводная таблица (PivotTable)
В диалоговом окне Создание сводной таблицы (Create PivotTable) в пункте Выбрать таблицу или диапазон (Select a table or range) в поле Таблица или диапазон (Range/Table) будет автоматически указан не адрес какого-то диапазона, а имя созданной умной таблицы:

Далее надо определить место размещения Сводной таблицы:

На новый лист (New Worksheet)
На существующий лист (Existing Worksheet)

Готово. Теперь при добавлении строк в эту таблицу для их отображения в сводной достаточно будет лишь обновить сводную таблицу как привыкли. Правда, тут тоже есть нюанс - добавлять строки надо правильно. Можно вбить данные в любую ячейку первой пустой строки таблицы - таблица автоматом расшириться, добавив еще одну строку. Теперь туда можно скопировать нужные данные или добить вручную. Если надо вставить сразу несколько строк - в правом нижнем углу последней строки умной таблицы есть слегка выделяющийся уголочек, который надо ухватить мышью и растянуть на нужное кол-во строк/столбцов.
Если мы просто скопируем строки ниже таблицы, то она не расширится. Это надо учитывать.

Если необходимо настроить на авторасширение уже созданную сводную , то порядок почти такой же, только сводную таблицу создавать не надо. Преобразуем исходные данные в умную таблицу, переходим на лист со сводной таблицей. Выделяем любую ячейку в сводной таблице, переходим на динамическую вкладку Работа со сводными таблицами (PivotTable Tools) -Параметры (Options) -группа кнопок Даныне (Data) -Источник данных (Change data Source) . В появившемся окне в поле Таблица или диапазон (Table/Range) указываем либо ссылку на всю умную таблицу, либо имя нашей умной таблицы(если знаете где его подсмотреть). На что здесь следует обратить внимание: если указывался диапазон, то если он указан верно - в поле вместо адреса ячеек будет отображено имя умной таблицы:

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

Полное автообновление
Для полного счастья можно подключить работу макросов. Что я хочу? Я хочу, чтобы как только я изменил/добавил данные в исходные данные - сводная тут же обновилась. Для этого надо сделать следующее:

убеждаемся, что макросы разрешены(Почему не работает макрос?, Что такое макрос и где его искать?)
перейти на лист исходных данных(в моем случае лист так и называется - Исходные данные)
жмем на ярлычке этого листа правой кнопкой мыши -Исходный текст (View code) :
вставляем туда следующий код:

Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'проверяем - изменения внутри умной таблицы или нет If Not Intersect(Target, Target.Parent.ListObjects(1).Range) Is Nothing Then 'если внутри таблицы, то обновляем сводную таблицу на листе "Автообновляемая сводная" Sheets("Автообновляемая сводная").PivotTables(1).RefreshTable 'для всех сводных на листе ' Dim pt As PivotTable ' For Each pt In Sheets("Автообновляемая сводная").PivotTables ' pt.RefreshTable ' Next End If End Sub

Все, теперь при любом изменении внутри исходных данных(будь это добавление/удаление строк или просто изменение значений внутри таблицы) сводная таблица обновиться без занудных действий вроде выделения сводной и жмахания кнопки Обновить.
Пара важных комментариев к коду:

Sheets("Автообновляемая сводная") - здесь Автообновляемая сводная это имя листа, на котором расположена сводная таблица. Это очень важно. Если будет указано неверное имя листа - код выдаст ошибку и никакого обновления, конечно же, не произойдет
код сделан таким образом, что на листе исходных данных должна быть только одна умная таблица. Нет, их может быть несколько, но код будет ориентироваться исключительно на первую. И если вы плохо знакомы с принципами создания объектов - то лучше не рисковать. Хотя и здесь можно выйти из положения. Если вы знаете имя своей умной таблицы(его можно подсмотреть на вкладке Конструктор -группа Свойства), то можно изменить код так:
вместо строки
If Not Intersect(Target, Target.Parent.ListObjects( 1 ).Range) Is Nothing Then
записать такую
If Not Intersect(Target, Target.Parent.ListObjects( "Таблица1" ).Range) Is Nothing Then
где Таблица1 - имя вашей умной таблицы, на основании которой создана сводная.
тот же нюанс с листом самой сводной - код ориентирован так, что обновляет только первую сводную на листе Автообновляемая сводная . Здесь так же можно заменить цифру 1 на имя сводной(например .PivotTables("СводнаяТаблица1").RefreshTable ), либо же сделать обновление всех сводных. Для этого надо раскомментировать блок 'для всех сводных на листе (убрать апострофы перед строками) и убрать строку с именем листа, чтобы получилось так:

Private Sub Worksheet_Change(ByVal Target As Range) 'проверяем - изменения внутри умной таблицы или нет If Not Intersect(Target, Target.Parent.ListObjects(1).Range) Is Nothing Then 'если внутри таблицы, то обновляем все сводные на листе "Автообновляемая сводная" Dim pt As PivotTable For Each pt In Sheets("Автообновляемая сводная").PivotTables pt.RefreshTable Next End If End Sub

P.S. Так же можно использовать и иной подход - вставить в модуль листа Автообновляемая сводная такой код:

Private Sub Worksheet_Activate() Me.PivotTables(1).RefreshTable End Sub

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

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

Автоматическое обновление сводной таблицы с помощью VBA

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

1. На панели вкладок листа щелкните правой кнопкой мыши вкладку листа, содержащую исходные данные, и выберите Просмотреть код в контекстном меню. Смотрите скриншот:

2. В открывшемся окне Microsoft Visual Basic для приложений вставьте код.

VBA: автоматическое обновление указанной сводной таблицы

Ноты:
(1) В коде рабочих листов ("имя листа"). Сводные таблицы ("Имя сводной таблицы") .PivotCache.Refresh, замените имя листа на имя листа, содержащего указанную сводную таблицу, и замените Имя сводной таблицы с именем указанной сводной таблицы.
(2) Выберите любую ячейку в указанной сводной таблице, вы получите ее имя в Анализировать вкладку (или Параметры вкладка). Смотрите скриншот:

3. Сохраните код.

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

Легко объединяйте несколько листов / книг / файлов CSV в один рабочий лист / книгу

Объединение десятков листов из разных книг в один может оказаться утомительным занятием. Но с Kutools for Excel's Объединить (рабочие листы и рабочие тетради) утилиту, вы можете сделать это всего за несколько кликов! Полнофункциональная бесплатная 30-дневная пробная версия!

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

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

Если вы окажетесь в этой лодке, попробуйте этот трюк:

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

Для этого начните с выбора столбца.

Затем щелкните раскрывающийся список на панели «Число» ленты «Домой». Выберите Больше числовых форматов . Или, если хотите, выберите одну из предустановок.

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

Обратите внимание, что ничего не изменилось, хотя в раскрывающемся списке «Формат номера» отображается «Пользовательский».

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

Чтобы ускорить процесс, выберите столбец, перейдите на ленту данных и нажмите « Текст в столбцы» .

Выберите с разделителями и нажмите кнопку Далее .

Снимите все разделители и нажмите Далее .

Форматирование чисел всех ячеек будет обновлено.

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

Какие практические применения для Excel?

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

Как использовать данные ячейки Excel для ссылки на имя листа

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

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

ДУМАЙТЕ БОЛЬШЕ из картинки чуть ниже не в фокусе? Подумайте еще раз, мистер Магу. Нажмите где-нибудь на фотографии, а затем прочитайте о новом базовом изобретении, которое

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