Note: The other languages of the website are Google-translated. Back to English

Як автоматично фільтрувати рядки на основі значення комірки в Excel?

Зазвичай функція фільтрації в Excel може допомогти нам відфільтрувати будь-які дані, як нам потрібно, але, іноді, я хотів би автоматично фільтрувати комірки на основі введення комірки вручну, що означає, що коли я ввожу критерії в комірку, дані можуть бути фільтрується автоматично відразу. Чи є якісь ідеї для вирішення цієї роботи в Excel?

Автофільтрувати рядки на основі значення комірки, яке ви ввели за допомогою коду VBA

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


Автофільтрувати рядки на основі значення комірки, яке ви ввели за допомогою коду VBA

Припустимо, у мене є такий діапазон даних, тепер, коли я ввожу критерії в комірки E1 та E2, я хочу, щоб дані автоматично фільтрувались, як показано на знімку екрана нижче:

doc автофільтр 1

1. Перейдіть до аркуша, в якому ви хочете автоматично фільтрувати дату на основі введеного значення комірки.

2. Клацніть правою кнопкою миші вкладку аркуша та виберіть Переглянути код з контекстного меню, що з’явиться Microsoft Visual Basic для додатків вікно, скопіюйте та вставте наступний код у порожнє Модулі вікно, див. скріншот:

Код VBA: дані автоматичного фільтрування відповідно до введеного значення комірки:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20160606
   If Target.Address = Range("E2").Address Then
       Range("A1:C20").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("E1:E2")
   End If
End Sub

doc автофільтр 2

примітки: У наведеному вище коді, A1: C20 діапазон даних, який потрібно відфільтрувати, E2 - цільове значення, за яким потрібно фільтрувати, та E1: E2 це ваш критерій комірка буде відфільтрована на основі. Ви можете змінити їх відповідно до своїх потреб.

3. Тепер, коли ви вводите критерії в комірку E1 і E2 і натисніть вводити ключ, ваші дані будуть автоматично фільтруватися за значеннями комірок.


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

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

Kutools для ExcelАвтора Супер фільтр Функція - це потужна утиліта, ви можете застосувати цю функцію для завершення наступних операцій:

  • Фільтрувати дані з кількома критеріями; Фільтрувати дані за довжиною тексту;
  • Фільтрувати дані за великими / малими літерами; Фільтрувати дату за роком / місяцем / днем ​​/ тижнем / кварталом

Kutools для Excel: з більш ніж 300 зручними надбудовами Excel, спробуйте безкоштовно без обмежень протягом 30 днів. Завантажте та безкоштовно пробну версію зараз!


Демо: автоматичне фільтрування рядків на основі значення комірки, яке ви ввели за допомогою коду VBA


Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Понад 300 потужних функцій. Підтримує Office / Excel 2007-2019 та 365. Підтримує всі мови. Простота розгортання на вашому підприємстві чи в організації. Повна функція 30-денної безкоштовної пробної версії. 60-денна гарантія повернення грошей.
вкладка kte 201905

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (36)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
ВЕЛИКЕ ДЯКУЮ ЗА Вищенаведену формулу - ВОНА ЧУДО ПРАЦЮЄ.
Цей коментар був мінімізований модератором на сайті
Я намагався відфільтрувати аркуш із різними кодами (взятими з різних сайтів, у тому числі з цього), але, здається, жоден не працює. На аркуші з інформацією в діапазоні комірок A101:EF999 (так, велика), я хочу автоматично відфільтрувати аркуш на основі трибуквенного коду, який я вводжу в клітинку B5, який повинен відповідати рядкам із таким самим кодом у стовпці B101. -B999. Зразок фрагмента виглядатиме так: ABCDE 5 ABC ... 101 ABC 102 DEF 103 GHI 104 ABC 105 JKL 106 ABC 107 DEF Після вибору "ABC" у клітинці B5 лише рядки 101, 104 і 106 відображатися, але нічого не повинно відображатися трапляється. Чи є щось, що я тут не помічаю? Будемо вдячні за будь-яку допомогу!
Цей коментар був мінімізований модератором на сайті
Як мені це скасувати? він приховує всі мої дані.
Цей коментар був мінімізований модератором на сайті
Привіт, наведений нижче код працює ідеально. Однак як вимкнути макрос, якщо я хочу скасувати фільтрацію? Приватна підкладка Worksheet_Change(ByVal Target As Range) 'Оновити за Extendoffice 20160606 Якщо Target.Address = Range("E2").Address then Range("A1:C20").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("E1:E2") End If End Sub
Цей коментар був мінімізований модератором на сайті
У діапазоні ("E2"). Адреса видаліть будь-які введені дані. Все буде "відфільтровано".
Цей коментар був мінімізований модератором на сайті
Мені не підходить, можливо, у мене Office 2010? нічого не робить :S
Цей коментар був мінімізований модератором на сайті
Дякуємо за цей запис! Я намагаюся налаштувати код, щоб дозволити діапазон прийняття.

Приклад: я вводжу 5, і він фільтрує і показує лише все, що знаходиться в межах 5 від 5 (тому від 4.5 до 5.5)
Цей коментар був мінімізований модератором на сайті
це працює і чудово... дякую
Цей коментар був мінімізований модератором на сайті
Дякую за цей код. Я намагався змінити його, щоб він працював краще для мене, але виникли труднощі.

На моєму аркуші є дані з A2:G2280. Стовпець А містить назви вулиць. Я хочу мати можливість ввести принаймні частину назви вулиці в A1 і відображати лише дані, які містять A1 повністю або частково. Тому, якщо я введу Bro в A1, я побачу рядки, які містять Broad, Broadway і Brook. Звичайно, якщо A1 порожній, я побачив би все.



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



Дякую.



Відзначити
Цей коментар був мінімізований модератором на сайті
Привіт, Марк,
Щоб вирішити проблему, застосуйте такий код VBA:
Примітка. У наведеному нижче коді A1 – це клітинка, у яку потрібно ввести критерії, A2: D20 – діапазон даних, A – стовпець, який містить критерії, за якими потрібно відфільтрувати, будь ласка, змініть посилання на клітинки на свої власні .

Приватний допоміжний робочий лист_Change(ByVal Target As Range)
Dim xRg As Range
Dim xRRg As Range
Dim xFNum як ціле число
On Error Resume Next
Якщо Target.Address <> Range("A1").Address, то вийдіть із підпорядкованої
Встановіть xRg = Діапазон("A2:D20").Поточна область
Application.ScreenUpdating = Невірний
Якщо Target.Text = "" Тоді
xRg.Rows.Select
Selection.EntireRow.Hidden = False
Application.ScreenUpdating = True
Exit Sub
End If
Для xFNum = 1 До xRg.Rows.Count
Встановити xRRg = xRg.Range("A" & xFNum)
xRRg.Rows.Select
Якщо InStr(xRRg.Text, Target.Text) > 0 Тоді
Selection.EntireRow.Hidden = False
Ще
Selection.EntireRow.Hidden = True
End If
Далі xFNum
Application.ScreenUpdating = True
End Sub

Будь ласка, спробуйте, сподіваюся, це допоможе вам!
Цей коментар був мінімізований модератором на сайті
Дякуємо за допомогу.
Я змінив A2:D20 на A3:G2281, щоб представляти моє поле даних. Тепер, коли я вводжу будь-що в клітинку A1 і вкладку в клітинку, рядки 2-109 приховані. Він не фільтрує та не відображає лише рядки, які містять повністю або частково те, що введено в клітинку A1.



Будь-які ідеї?
Цей коментар був мінімізований модератором на сайті
Привіт Скайян,
ваш код працює ідеально, але у мене є запитання: якщо я не хочу мати критерій фільтра в клітинці A1 і залишаю клітинку порожньою, вона більше не відображатиметься жодного рядка. Так все відфільтровано. Так само, як Марк хотів: "Звичайно, якщо A1 порожній, я побачу все", це не працює, чи не так?
Було б добре, якби ви могли мені допомогти.
Вітаю
Карстен
Цей коментар був мінімізований модератором на сайті
Привіт Скайян,
ваш код працює ідеально, але у мене є запитання: якщо я не хочу мати критерій фільтра в клітинці A1 і залишаю клітинку порожньою, вона більше не відображатиметься жодного рядка. Так все відфільтровано. Так само, як Марк хотів: "Звичайно, якщо A1 порожній, я побачу все", це не працює, чи не так?
Було б добре, якби ви могли мені допомогти.
Вітаю
Карстен
Цей коментар був мінімізований модератором на сайті
Привіт, Карстен,
Якщо залишити клітинку A1 порожньою, відображаються всі рядки даних, при введенні будь-якого символу, який потрібно відфільтрувати, він буде працювати правильно. Як ваша проблема, будь ласка, вставте свій аркуш проблем як знімок екрана сюди.

Дякую!
Цей коментар був мінімізований модератором на сайті
Що станеться, якщо у вас, наприклад, GRADE11 і GRADE12. Чи покаже фільтр їх також, якщо ви спробуєте відфільтрувати
на GRADE1?
Цей коментар був мінімізований модератором на сайті
Привіт, Боб,
Так, як ви сказали, при введенні частини тексту, який потрібно відфільтрувати, усі клітинки, що містять текст частини, будуть відфільтровані. Отже, якщо ви введете Grade1, усі клітинки містять Grade1, Grade11, Grage123...будуть відфільтровані.
Цей коментар був мінімізований модератором на сайті
Сценарій VB працював чудово. Велике спасибі за пост!
Цей коментар був мінімізований модератором на сайті
Приватний допоміжний робочий лист_Change(ByVal Target As Range)
'Оновлення Extendoffice 20160606
Якщо Target.Address = Range("E2").Address Then
Діапазон("A1:C20").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("E1:E2")
End If
End Sub


E2 HUCRESI YERINE E SUTUNUNUNA YAZILAN SON SATIRA GORE FILITRELEME YAPABILIR М.І.


Чи можна згідно з кодом, згаданим вище, зробити фільтрацію за записаними даними до останнього рядка стовпця E?


Я сподіваюся отримати допомогу і дякую за вашу допомогу
Цей коментар був мінімізований модератором на сайті
Привіт, Мурат,
Наведений вище код добре працює на всьому аркуші, вам просто потрібно змінити посилання на клітинки відповідно до ваших потреб. Будь ласка, спробуйте, дякую!
Цей коментар був мінімізований модератором на сайті
Я не знаю, як я можу змінити посилання на клітинку.
Цей коментар був мінімізований модератором на сайті
Чи є спосіб продовжити фільтрацію за допомогою додаткових ящиків. Коли я пишу його як ElseIf, він відповідає лише команді ElseIf.
Цей коментар був мінімізований модератором на сайті
Тож у мене є купа значень, а потім таблиця даних. Мені цікаво, чи можу я відфільтрувати цю таблицю на основі значень, як описано вище. Наприклад, я хотів би клацнути клітинку зі значенням 3, що відповідає 3 записам (200 рядків, 25 стовпців), які відповідають умові, а потім відфільтрувати мою таблицю, щоб просто відобразити ці записи. Прикладом умови може бути, якщо одна змінна більша за 100. У мене більше 100 цих умов, тому я хотів би, щоб моя таблиця була пов’язана з нею якимось чином. Будемо вдячні за будь-яку допомогу. У наведеному прикладі це було б схоже на те, якби ви просто хотіли будь-який вік від 3, 6, 9, 12 тощо, а потім у вас було 25 подібних змінних. Отже, щоб відфільтрувати таблицю, щоб відображати лише записи віком від 3 років, натиснувши значок значення зі списку, де вказано щось на кшталт вік>3 - 2 записи, вік>6 - 4 записи тощо
Цей коментар був мінімізований модератором на сайті
В інструкції може бути помилка. Замість того, щоб вставляти код у порожній модуль, слід вставити його у вікно Аркуш. Наприклад, якщо макрос має працювати на аркуші 1, код слід вставити в об’єкти Microsoft Excel -> аркуш 1 (лист 1). Тільки тоді він працює у мене в Excel 2016.

Дякую за код!
Цей коментар був мінімізований модератором на сайті
Привіт, mjr,
У цій статті немає помилки, сказано в статті, ви повинні помістити код VBA в модуль аркуша, клацнувши правою кнопкою миші назву аркуша, а потім виберіть Переглянути код, щоб перейти до модуля.
Але ваша операція також правильна.
Дякуємо за ваш коментар.
Цей коментар був мінімізований модератором на сайті
Здравствуйте,

Що робити, якщо я отримав відфільтровані дані на іншій вкладці (аркуш 2) у тій самій книзі, а клітинка, на яку має посилатися фільтр, знаходиться на першій вкладці (аркуш 1). Я використовував цей VBA, але він не працює так, тільки якщо у мене є обидві клітинки критеріїв (E2 в цьому VBA) на одній вкладці з відфільтрованими даними (A1: C20)
Цей коментар був мінімізований модератором на сайті
Гей, хлопці,
ідеальне пояснення, дуже дякую.
1 Маленьке запитання: якщо я хочу фільтрувати за 2,3 4 або більше критеріїв, як це зробити?
Наприклад, я хочу сказати, що я хочу побачити ім’я Генрі з 1-им класом і цим віком...тому не просто 1 критерій, а, наприклад, 3..=?


дякую за відповідь


З повагою,


TIM
Цей коментар був мінімізований модератором на сайті
Привіт, Тим,
Щоб автоматично фільтрувати дані на основі кількох критеріїв, слід застосувати наведений нижче код: (будь ласка, змініть посилання на клітинки відповідно до ваших потреб)

Приватний допоміжний робочий лист_Change(ByVal Target As Range)
'Оновлення до Extendoffice
Dim xVStr як рядок
Dim xFStr як рядок
xVStr = "E22:G22" 'критерії, за якими потрібно фільтрувати
xFStr = "E21:G22" 'діапазон містить заголовок критеріїв
Якщо ні (Перетин(Діапазон(xVStr), Ціль) Ніщо) Тоді
Діапазон("A1:C17").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Діапазон(xFStr)
End If
End Sub


Будь ласка, спробуйте, сподіваюся, це допоможе вам!
Цей коментар був мінімізований модератором на сайті
Дякую, це було дуже корисно. У мене є лише одне запитання, після вибору імені в спадному меню (у мене є імена як критерії), воно показує лише людей з цим іменем, як і повинно, але як я можу це зробити після того, як я вибрав ім'я, тоді я хочу так побачити всі рядки моєї таблиці??

сподіваюся, ви можете мені допомогти.

З повагою
Цей коментар був мінімізований модератором на сайті
Привіт, Петре,
Цей код добре працює для підгонки всіх рядків даних, чи могли б ви правильно ввести діапазон таблиці?

A1:C20 – це діапазон ваших даних, E1:E2 – діапазон критеріїв.
Діапазон("A1:C20").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("E1:E2")
Цей коментар був мінімізований модератором на сайті
Дуже дякую за це. Це спрацювало для мене до певної міри, будь ласка, дозвольте мені пояснити:

У моїй робочій книжці є кілька таблиць. Коли я застосував наведений вище код, він працював лише для однієї таблиці, але не для двох інших таблиць. Скажімо, перша таблиця, як у вашому прикладі, з A1:C20. Друга таблиця з A22:C40. Третій з A42:C60. Усі таблиці містять інформацію про «Оцінку», однак вони мають різні стовпці та дату, тому їх не можна об’єднати в одну велику таблицю, а скоріше в 3 менші таблиці.

Як код повинен сподобатися, щоб, якщо я поставлю критерії: "Grade1", всі 3 таблиці будуть автоматично відфільтровані для "Grade1" ?

Заздалегідь дуже дякую.

З найкращими побажаннями
Цей коментар був мінімізований модератором на сайті
Привіт там,

Дякую за чудовий вміст, однак у мене виникли деякі проблеми, і мені було цікаво, чи є у вас ідеї чому.

Я налаштував свої діапазони, щоб мій код читався:

Додатковий автофільтр()
Якщо Target.Address = Range("E13:F14").Address Then
Worksheets('Data Archive').Range("A1:C20").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("F10:F1048576")
End If
End Sub


Однак, коли я запускаю макрос, з’являється повідомлення «Помилка компіляції: синтексна помилка».

Заздалегідь спасибі

Стан
Цей коментар був мінімізований модератором на сайті
Привіт, Стен,
Чи правильно ви встановили діапазон даних і діапазон критеріїв у коді? Або ви можете взяти свій діапазон даних як знімок екрана тут.
Дякую!
Цей коментар був мінімізований модератором на сайті
Привіт, чи є спосіб швидко змінити код VBA, щоб відфільтрувати значення, більші, ніж у вибраній клітинці?
There are no comments posted here yet
Load More
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця