0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

Как я могу автоматически обновлять автоматический фильтр Excel при изменениях данных?

Я изменяю значение в одной ячейке на отфильтрованное значение. Другими словами, я хочу, чтобы текущая строка исчезла.

6 ответов

Щелкните правой кнопкой мыши на имени листа, выберите «Показать код» и вставьте приведенный ниже код. После вставки нажмите на значок Excel в разделе «Файл» в левом верхнем углу или введите Alt-F11, чтобы вернуться к просмотру электронной таблицы.

Он обновится автоматически. Обязательно сохраните файл в формате .xlsm с поддержкой макросов.

Замена кода на этот также, похоже, помогает (по крайней мере, в Excel 2010):

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

Я нашел информацию здесь: Ссылка

Я использую VBA / макрос на основе события Worksheet_Change, но мой подход немного отличается. Хорошо, сначала код, а затем объяснения:

Нажав Alt + F11, вы можете сделать панель разработки и вставить код на рабочий лист, содержащий фильтр, который вы хотите обновлять автоматически.)

Представим себе простой фильтр в одном столбце (L в моем случае) и диапазон данных от 1 (даже если заголовок может присутствовать) до 126 (выберите число совершенно наугад). Мой метод прост: всякий раз, когда что-то меняется на моем листе, фильтр в указанном диапазоне автоматически удаляется или применяется заново. Вот краткое объяснение поля и критериев.

Поле представляет собой целочисленное смещение диапазона. В моем случае у меня только одностолбцовый фильтр, и диапазон задается столбцом (L), который является первым столбцом диапазона (поэтому я использую 1 в качестве значения).

Критерии описывают набор правил, применяемых к диапазону данных. Чтобы отобразить только строки со столбцами L, отличными от нуля, я использовал «* > 0».

Читайте так же:
Как автозаполнить альфа-символы в Excel?

Вот и все. Более подробную информацию о методе Range.AutoFilter можно найти в разделе Справочник

Чтобы закрепить ответы:

Щелкните правой кнопкой мыши на имени рабочего листа, выберите «Показать код» и вставьте приведенный ниже код. После вставки нажмите на значок Excel под «Файл» в левом верхнем углу или введите Alt-F11, чтобы вернуться к просмотру электронной таблицы.

Обновления будут происходить автоматически. Не забудьте сохранить файл в формате .xlsm с поддержкой макросов.

Кроме того, Крис использовал этот код (который я создал в 2010 году):

При отсутствии раскрытия сообщения вы увидите только длинный ответ! 😉

Извините, недостаточно комментариев для комментариев. (Admins, не стесняйтесь вырезать это в комментарии выше.) Пользовательский ответ «danicotra», начинающийся с «Я использую VBA / Macro на основе события Worksheet_Change, но мой подход . » с помощью ‘сначала удалите фильтр
‘затем примените его снова
является правильным решением при использовании Excel 2007+. Однако .AutoFilter.ApplyFilter недействителен в XL03 и ранее, поэтому я показываю путь ниже.

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

Danicotra использовал упрощенный пример. На самом деле это можно сделать в более общем виде. Перенесите из ActiveSheet в следующий (или другой объект листа):

Сохранить диапазон автофильтра. Он содержит столбцы .AutoFilter.Filters.Count и (.AutoFilter.Range.Count / .AutoFilter.Filters.Count), сохраненные в rngAutofilter

Соберите все 4 свойства каждого .AutoFilter.Filters.Count в массиве myAutofilters, стараясь избежать «ошибок, определяемых приложением», когда OO или .Operator являются ложными. (myAutofilters уменьшается до количества строк и столбцов из шага 1)

Отключение фильтра при сохранении выпадающих списков. ShowAllData

Читайте так же:
Как быстро вставить комментарий в несколько ячеек в Excel?

Для каждого элемента фильтра, который был. В соответствии с вашим сохраненным массивом, сбросьте 3 из 4 свойств каждого из элементов автофильтра .AutoFilter.Filters.Count. Опять позаботьтесь о том, чтобы вы избегали «ошибок, определяемых приложением», когда .Operator является ложным, поэтому для каждого элемента «i»,
rngAutofilter.AutoFilter Поле: = i, Критерии1: = myAutofilters (i, 2)
или
rngAutofilter.AutoFilter Поле: = i, Criteria1: = myAutofilters (i, 2), Operator: = myAutofilters (i, 3), Criteria2: = myAutofilters (i, 4)

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

Повторное вычисление формул в Power Pivot

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

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

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

В Power Pivot используется как обновление, так и пересчет данных:

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

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

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

Пересчет вручную и автоматически

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

Читайте так же:
Как быстро сохранить книгу в нескольких местах в Excel?

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

Использование автоматического пересчета

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

Обновление внешнего по отношению к системе источника данных.

Изменение определения формулы

Имена таблиц или столбцов, на которые ссылается формула, были изменены.

Связи между таблицами были изменены, добавлены или убраны.

Теперь есть дополнительные меры или рассчитываемые столбцы.

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

Добавление и удаление строк было сделано.

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

Пересчет вручную

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

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

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

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

Обратите внимание, что если в книге установлен ручной режим расчета, Power Pivot в Microsoft Excel 2013 не проверяет и не подтверждает формулы, что дает следующие результаты:

Читайте так же:
Как быстро сгенерировать случайное время в Excel?

Книга будет помечена как содержащая ошибку для всех добавленных формул.

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

Настройка книги для пересчета вручную

В Power Pivot выберите Конструктор > Расчеты > Параметры расчета > Режим расчета вручную.

Пересчитайте все таблицы, выбрав Параметры вычислений > Пересчитать.

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

Важно: Перед публикацией книги всегда следует изменить режим расчета на автоматический. Это позволит избежать проблем при составлении формул.

Устранение неполадок пересчета

Зависимости

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

Предположим, например, что у вас есть таблица Sales, связанная с таблицами Product и ProductCategory; и формулы в таблице Sales зависят от обеих таблиц. Все вычисляемые столбцы в таблице Sales будут пересчитаны, если таблицы Product или ProductCategory будут изменены. Если у вас есть формула для сворачивания продаж по категориям или продуктам, это имеет смысл. Таким образом, чтобы обеспечить точность вычислений, необходимо пересчитать формулы на основе данных.

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

Читайте так же:
Как быстро перемещать элементы между двумя списками в Excel?

В некоторых случаях Power Pivot может указать, что столбцы могут быть исключены из пересчета. Например, если у вас есть формула, которая ищет значение [Цвет товара] в таблице Sales[Quantity], а изменяемый столбец имеет значение [Quantity] в таблице Sales[Quantity], формулу не нужно пересчитывать, даже если таблицы Sales[Quantity] и Sales[Quantity] объединены. Однако для формул, зависящих от параметра sales[Quantity], требуется пересчет.

Последовательность пересчета для зависимых столбцов

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

Операций

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

Пересчет переменных функций

Некоторые функции, такие как NOW, RAND или TODAY, не имеют постоянных значений. Чтобы избежать проблем с производительностью, запросы или фильтрация обычно не проводят повторную оценку таких функций, если они используются в вычисляемом столбце. Результаты для этих функций пересчитываются только при пересчете всего столбца. Такие ситуации включают обновление данных из внешнего источника данных или ручное изменение данных, что приводит к пересчету формул, содержащих эти функции. Однако функции переменных, такие как NOW, RAND или TODAY, всегда будут пересчитываться, если функция используется в определении вычисляемого поля.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector