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

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

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

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

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

Я обнаружил, что когда я работал с таблицами, это не сработало. Фильтр был не на листе, а на столе. этот код добился цели

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

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

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

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

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

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

Критерии является строкой , которая описывает фильтр для применения в диапазоне данных. В моем примере я хочу показать только те строки, где столбец L отличается от 0 (поэтому я использовал «<> 0»).

Просто чтобы закрепить ответ (ы):

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

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

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

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

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

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

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

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

Соберите в массив myAutofilters каждое из 4 свойств каждого из элементов автофильтра .AutoFilter.Filters.Count, следя за тем, чтобы избежать ошибок, определяемых приложением, когда .On или .Operator имеет значение false. (myAutofilters будет reDim’d на количество строк и столбцов в шаге 1)

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

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

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

7. Работа со списками данных

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

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

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

Выборка данных средствами Автофильтра

Автофильтр следует применять для быстрой фильтрации данных с одним или двумя условиями, накладываемыми на ячейки от­дельного столбца. Для одного столбца можно определить несколько условий поиска. Чтобы установить этот режим надо выполнить следующие действия:

1. Укажите ячейку в фильтруемом списке.

2. Дайте команду Данные=> Фильтр=> Автофильтр.

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

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

4. Выберите значение в списке.

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

• Чтобы отобразить строки, удовлетворяющие одному условию отбора, выберите в первом поле под надписью Показать только те строки, значения которых необходимый оператор сравнения (равно, больше, меньше, и т.д.), а в правое поле введите ограничение. Переключатель и/или позволяет указать второе условие фильтрации для выбранного столбца. и значение сравнения справа от него.
• Чтобы отобразить строки, удовлетворяющие одновременно двум условиям отбора, введите оператор и значение сравнения в первой группе полей, нажмите кнопку И, а затем введите второй оператор и значение сравнения во второй группе полей.
• Чтобы отобразить строки, удовлетворяющие одному из двух условий отбора, введите оператор и значение сравнения в первой группе полей, нажмите кнопку Или, а затем введите второй оператор и значение сравнения во второй группе полей.
• При этом в условиях поиска можно использовать символы подстановки "*" и "?". Условия могут быть объединены одним из двух способов: по "И" либо по "ИЛИ". Чтобы отфильтровать заданное число строк с максимальными или минимальными значениями ячеек текущего столбца, воспользуйтесь условием отбора "Первые 10. ".
• Наконец, записи можно отфиль­тровать просто по конкретному значению, которое присутствует в столбце, а также по пустому или непустому значению.

При вводе условий отбора можно использовать перечисленные ниже элементы условий.

1. Чтобы отобрать строки, содержащие ячейки с заданным значением, введите требуемые число, дату, текстовую или логическую константу в ячейку ниже заголовка столбца диапазона условий. Например, чтобы отобрать строки, в которых индекс отделения связи равен 115522, введите в диапазоне условий число 115522 ниже заголовка «Индекс отделения связи».

2. При использовании текстовой константы в качестве условия отбора будут отобраны все строки с ячейками, содержащими текст, начинающийся с заданной последовательности символов. Например, при вводе условия «Анг» будут отобраны строки с ячейками, содержащими слова Ангара, Англия и Ангола. Чтобы получить точное соответствие отобранных значений заданному образцу, например, текст, следует ввести условие:
=»=текст»

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

(тильда), за которой следует ?, * или

Знак вопроса, звездочка или тильдаЧто

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

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

В Microsoft Ехсеl фильтрованные объекты выделяются специальным образом. Номера отфильтрованных строк становятся голубыми. Стрелки автофильтра в столбцах, по значению которых отбирались строки, также выделяются голубым цветом.

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

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

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

Чтобы удалить автофильтр из списка, дайте ещё раз команду Данные => Фильтр=> Автофильтр.

Ниже приведен фрагмент списка данных, который мы будем использовать в дальнейшей работе. В каждом поле содержится информация определенного типа, например, фамилии, имена и т.д., а запись состоит из описания элемента списка. В нашем списке каждая запись содержит основные сведения о сотруднике: № п.п., Фамилия, Имя, Отчество, Пол, Разряд, Улица, Дом, Кв., Дата рождения, Телефон, Оклад, Дата приема на работу.

23.Автофильтр Excel

сформируйте список так, как это было описано в выпуске про сортировку.

Затем выберите пункт меню <Данные — Фильтр — Автофильтр> (menu Data — Filter — AutoFilter).

Совет! Если задать список через меню <Данные — Списки — Создать список> (menu Data — List — Create List) или клавиши Ctrl+L, в списке автоматически включится автофильтр.

В результате в заголовке списка появятся <флажки> с выпадающими меню автофильтра. Здесь Вы увидите несколько вариантов отбора строк нашего списка.

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

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

В-третьих, можно отобрать строки по сложным критериям, выбрав пункт <Условие:>.

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

Условия, как и в функции =ЕСЛИ(), могут относиться как к текстовым значениям, так и к числам. Например:

* Чтобы показать все записи, начинающиеся на букву А, выберите тип условия <равно> и значение <А*>;

* Чтобы показать все записи, объем продаж в которых больше 2000 руб., выберите тип <больше или равно> и значение 2000.

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

О том, что список отфильтрован, свидетельствует надпись в строке состояния:

и отображение номеров строк ячеек синим цветом:

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

Чтобы снова показать все записи, выберите соответствующий пункт в выпадающем меню автофильтра в каждом столбце или просто отключите автофильтр. Для этого снова войдите в меню <Данные — Фильтр — Автофильтр> (menu Data — Filter — AutoFilter) и снимите галочку.

24.Расширенный фильтр Excel

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

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

1. Поместите указатель ячейки внутри списка. В этом случае Excel автоматически распознает диапазон списка и представит ссылку на диапазон в диалоговом окне.

2. Выполните команду Данные – Фильтр – Расширенный фильтр. Поместите курсор ввода в поле “Диапазон условий” и выделите соответствующий диапазон в рабочем листе.

3. Закройте диалоговое окно нажатием кнопки “ОК”. На экране теперь будут отображены записи, удовлетворяющие заданным критериям.

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

Если в результате применения расширенного фильтра не должны быть отображены повторяющиеся записи, в диалоговом окне “Расширенный фильтр” установите флажок параметра “Только уникальные записи”.

Чтобы после фильтрации задать отображение в рабочем листе всех записей списка, выполните команду Данные – Фильтр – Отобразить все.

25.Меню «формат ячейки/шрифт» Excel

26.Меню «формат ячейки/выравнивание» Excel

27.Меню «формат ячейки/границы» Excel

28.30. Меню «формат/столбец» Excel

29.Меню «формат/строка» Excel

31.Меню «формат/автоформат» Excel

Для быстрого оформления таблицы можно воспользоваться командой Автоформат :

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

Из меню Format ( Формат ) выберите команду AutoFormat ( Автоформат ).

Из списка доступных форматов выберите стиль.

Щелкните на кнопке ОК, согласившись на форматирование таблицы с использованием выбранного стиля.

Для частичного применения автоформата нажмите кнопку Options (Параметры) и снимите флажки для форматов , которые не нужно применять.

Автофильтр в EXCEL

Способы фильтрации списков

Фильтрация или выборка — очень частая операция во время работы со списками. Суть ее в том, чтобы отобрать из списка все строки (записи), удовлетворяющие определенным условиям. Условий может быть много, они могут быть простыми и сложными, связанными друг с другом или независимыми. Существует несколько способов фильтрации списков в Excel.

Способ 1. Автофильтр

Отфильтровать список автофильтром — значит скрыть все строки за исключением тех, которых удовлетворяют заданным условиям отбора. Для выполнения такой операции необходимо выделить любую ячейку списка и, выбрать в меню Данные — Фильтр — Автофильтр (Data — Filter — AutoFilter) .

В первой строке, содержащей заголовки столбцов, появятся кнопки со стрелками — кнопки автофильтра:

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

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

К сожалению, ни одна версия Excel, кроме Excel 2007 не позволяет выбрать из выпадающего списка более одного варианта.

Мы можем также использовать автофильтр, чтобы найти заданное число (или процент) наибольших или наименьших элементов в списке. Для этого необходимо щелкнуть по кнопке автофильтра в соответствующем поле и выбрать Первые 10 (Top 10) . Откроется окно, в котором пользователь может задать количество элементов и выбрать из выпадающих списков их тип — наибольшие или наименьшие:

Способ 2. Пользовательский автофильтр

Мы можем использовать автофильтр для выборки записей по более сложным условиям. Допустим, необходимо выбрать из списка строки, где оклад сотрудника лежит в диапазоне от 1000 до 2000 или меньше/больше определенной величины. Для выполнения такой фильтрации из выпадающего списка поля "Стоимость заказа" необходимо выбрать пункт Условие (Custom) . Появится окно Пользовательского автофильтра :

При помощи выпадающих списков необходимо задать условие отбора и нажать кнопку ОК — Excel отобразит только те строки, которые удовлетворяют заданным условиям.

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

Способ 3. Расширенный фильтр

В отличие от Автофильтра и Пользовательского автофильтра — Расширенный фильтр практически не имеет ограничений на количество условий, налагаемых на список. Но (бесплатных пирожных не бывает!) требует некоторых подготовительных операций. А именно:

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

Excel отберет всех мужчин с окладом 1000 и более, неженатых и женщин не старше 1 января 1950 года рождения, незамужних, с детьми.

Для того, чтобы отфильтровать данные по критериям из нашей таблицы, выделите любую ячейку исходного списка сотрудников и выберите в меню Данные — Фильтр — Расширенный фильтр (Data — Filter — Advanced Filter) . Откроется окно Расширенного фильтра , в которое необходимо ввести адрес исходного диапазона, адрес диапазона условий и указать место, куда поместить результаты фильтрации:

Флажок Только уникальные записи служит для отбора неповторяющихся данных .