Adelitusn.ru

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

Как автоматически обновить автофильтр 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 и более ранних версиях, поэтому я покажу путь ниже.

Читайте так же:
Каталог бесплатных программ для Windows, Android

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

Даникотра использовал упрощенный пример. На самом деле, вы можете сделать это более широко. Предположим, с 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. Чтобы отфильтровать строки, содержащие определенное значение, нажмите кнопку со стрелкой в столбце, в котором содержатся искомые данные.

Читайте так же:
Ошибка отсутствия файла steam_api.dll. Что делать?

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте так же:
BSPlayer для Windows

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

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

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

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

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 скрыл все строки, не удовлетворяющие заданному условию, а номера отфильтрованных отобразил синим цветом, чтобы напомнить пользователю, что в данный момент он видит неполный список.

Читайте так же:
Paint Tool SAI скачать торрент

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

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

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

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

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

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

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

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

·В любую свободную область (лучше всего вставить над списком несколько пустых строк) нужно скопировать из "шапки" списка заголовки столбцов, по которым будут вводиться условия.
·Затем, ниже скопированных заголовков в пустые ячейки вводятся условия для фильтрации. Причем, условия введенные в ячейки одной строки Excel будет связывать логическим "И", а в ячейки разных строк — логическим "ИЛИ".

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

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

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

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

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