Филтриране на данни в Excel – лесно и изчерпателно

Командата Excel Filter може да ви помогне да филтрирате данни в диапазон или таблица, за да покажете само данните, от които се нуждаете, и да скриете останалите. Можете да приложите неговите вградени оператори за лесно филтриране на числа, текстове или дати, като филтриране на всички числа, които са по-големи или равни на определено число, филтриране на текст, който започва, завършва или съдържа конкретен знак или дума, или показва само редове, където падежът е преди или след определена дата и т.н. След като филтрирате данни в диапазон или таблица, ако данните са променени, можете или да приложите отново филтър, за да получите новите данни, или да изчистите филтър, за да покажете всички данни.

В този урок ще демонстрираме как да добавяте, използвате или премахвате филтри в Excel. Освен това ще ви насочим как да подобрите функцията за филтриране, за да се справите с по-сложни проблеми на Excel.

---

1. Как да добавите филтър в Excel

За да филтрирате данни в диапазон или таблица, първо трябва да добавите филтър към вашите данни. Този раздел предоставя 3 начина за добавяне на филтър в Excel.

1.1 Команда за филтриране в раздела Данни

Изберете клетки в диапазон или таблица, към които искате да добавите филтър, щракнете Дата > Филтър.

1.2 Команда за филтриране в раздела Начало

Изберете клетки в диапазон или таблица, към които искате да добавите филтър, щракнете Начало > Сортиране и филтриране > Филтър.

1.3 Добавете филтър с пряк път

Изберете клетки в диапазон или таблица, към които искате да добавите филтър, и след това натиснете Ctrl + Превключване + L ключове.

След като приложите една от горните операции, можете да видите, че в заглавките на колоните на избраните клетки се добавят падащи стрелки.

---

2. Как да приложите филтър в Excel (един или няколко критерия)

След като добавите филтъра, трябва ръчно да го приложите. Този раздел ще ви покаже как да приложите филтър в една или повече колони в Excel.

2.1 Прилагане на филтър към една колона (един критерий)

Ако искате да приложите филтъра само към една колона, като филтърни данни в колона C, както е показано на екранната снимка по-долу. Моля, отидете до тази колона и след това направете следното.

1. 1) Кликнете върху падащата стрелка в заглавката на колоната.
2. 2) Посочете условие за филтър според нуждите ви.
3. 3) Щракнете върху OK бутон за стартиране на филтрирането. Вижте екранна снимка:
   

Сега филтърът се прилага към колона C. Всички данни, които отговарят на критериите за филтър, ще бъдат показани, а останалите ще бъдат скрити.

След като приложите филтъра, можете да видите падащата стрелка да се превръща в икона на филтър .

Много е важно, когато задържите курсора върху иконата на филтъра, критериите за филтър, които сте посочили, ще се покажат като съвет на екрана, както е показано на екранната снимка по-долу. Така че, ако забравите критериите, които сте посочили за филтър, просто задръжте курсора върху иконата на филтъра.

2.2 Прилагане на филтър с множество критерии към множество колони (множество критерии)

2.2.1 Приложете филтър с множество критерии към множество колони една по една

Ако искате да приложите филтър към няколко колони с множество критерии, просто повторете горния метод към няколко колони една по една.

След като приложите филтър към няколко колони, можете да видите падащите стрелки във филтрираните колони да се обърнат към икони за филтриране.

2.2.2 Едновременно прилагане на филтър с множество критерии към множество колони

С горния метод трябва да приложите филтър към колони една по една и най-важният момент е, че този метод поддържа само И критерии. Тук представяме методите за вас не само да приложите филтър към няколко колони едновременно, но и да приложите и двете И намлява OR критерии.

Да предположим, че имате таблица с данни, както е показано на екранната снимка по-долу, и искате да филтрирате данни от множество колони въз основа на множество критерии: Продукт = AAA-1 и поръчка > 80, or Обща цена > 10000. Моля, опитайте един от следните методи, за да го направите.

2.2.2.1 Прилагане на филтър към множество колони с функцията Разширен филтър

Функцията Разширен филтър може да ви помогне да разрешите този проблем, моля, направете следното стъпка по стъпка.

1. Първо създайте критериите в работния лист, както е показано на екранната снимка по-долу.

Забележка: За критериите И, моля, поставете стойностите на критериите в различни клетки на един и същ ред. И поставете стойността на критериите ИЛИ на другия ред.

2. кликване Дата > Подробно за да включите Разширен филтър функция.

3. В Разширен филтър диалогов прозорец, моля, конфигурирайте както следва.

Сега колоните се филтрират едновременно въз основа на зададените критерии, както е показано на екранната снимка по-долу.

2.2.2.2 Лесно прилагане на филтър към множество колони с удивителен инструмент

Тъй като И намлява OR критериите за филтриране не са лесни за управление в горния метод, тук силно препоръчваме Супер филтър функция на Kutools за Excel. С тази функция можете лесно да приложите филтър към множество колони с критерии И и ИЛИ в Excel.

1. След инсталиране Kutools за Excel, Щракнете върху Kutools плюс > Супер филтър.

Тогава Супер филтър прозорецът се показва от дясната страна на работния лист.

По подразбиране се добавят две празни групи критерии с ИЛИ връзка между тях в Супер филтър прозорец. И връзката между критериите в същата група е И. Можете да промените връзката между различните групи според вашите нужди.

2. В Супер филтър прозорец, моля, конфигурирайте критериите за филтриране, както следва.

Сега само съответстващите данни се показват в оригиналния диапазон от данни, а останалите са скрити. Вижте екранна снимка:

Съвет: С тази удобна функция можете да добавите повече критерии в група, да добавите още групи, да запазите текущите настройки на филтъра като сценарий за бъдеща употреба и т.н. Това е незаменим инструмент, който може да спести много работно време и да подобри работната ефективност.

Кликнете, за да научите повече за тази функция.

---

3. Как да използвате филтър в Excel

В този раздел ще научите как да използвате командата за филтриране, за да филтрирате различни типове данни като текст, числа, дати и формати.

3.1 Филтриране на текстови стойности

3.1.1 Филтриране на текстови клетки със специфични критерии (започват с, завършват с, съдържат и т.н.)

Всъщност вграденият филтърен оператор – Текстови филтри предоставя много полезни критерии за лесно филтриране на текст. Да предположим, че искате да филтрирате клетки, които започват с конкретен знак като J, моля, направете следното, за да го направите.

1. Добавете филтър към заглавката на колоната на оригиналния диапазон от данни. Кликнете, за да разберете как.

2. Щракнете върху падащата стрелка в заглавната клетка, за да разгънете менюто за филтриране.

3. кликване Текстови филтри > Започва с.

4. В Персонализиран автофилтър диалогов прозорец, въведете конкретния знак (тук въвеждам J) в текстовото поле и след това щракнете OK.

Съвет: Можете да добавите друг И or Or критерии за връзка, каквито имате нужда.

Сега всички клетки, започващи със знак J, се показват в колона D, както е показано на екранната снимка по-долу.

3.1.2 Филтър с малки и големи букви

Изглежда, че е лесно да филтрирате текстови клетки въз основа на специфични критерии с вградения филтърен оператор. Въпреки това, тъй като функцията Филтър не поддържа филтриране на текст с чувствителен към главни и малки букви, как можем да направим чувствителен към малки и главни букви филтър в Excel? Този раздел ще ви покаже методи за постигането му.

3.1.2.1 Филтриране на определен текст с чувствителни към главни и малки букви чрез формула и командата Филтър

Ако приемем, че искате да филтрирате всички главни букви на определен текст, като например „ИНСТРУМЕНТИ ЗА ТЕКСТ“ в колона B, моля, направете следното.

1. Създайте помощна колона освен оригиналния диапазон от данни (тук избирам колона D като помощна колона). Въведете формулата по-долу във втората клетка и след това натиснете клавиша Enter. Изберете клетката с резултати, плъзнете я Манипулатор за автоматично попълване надолу, за да получите другите резултати.

=ТОЧНО(B2,ГОРЕН(B2))

Забележка: Тази формула помага да се идентифицират клетките с главни и малки букви. Ако една клетка съдържа всички главни букви, резултатът ще бъде ВЯРНО, в противен случай ще получите резултата като НЕВЯРНО.

2. Изберете колона B и D (колоната C ще бъде избрана, няма значение), щракнете върху Data > филтър за да добавите филтри към тях.

3. Отидете до колона B (колоната съдържа текстовете, които ще филтрирате) и след това конфигурирайте както следва.

Сега само главни и малки букви на „инструменти за текст“ се показват в колона B.

4. Щракнете върху падащата стрелка в колона D, премахнете отметката от Избери всички отметка, поставете отметка в квадратчето до TRUE елемент и след това щракнете върху OK бутон.

След това всички главни букви на текста „инструменти за текст“ в колона B се филтрират, както е показано на екранната снимка по-долу.

3.1.2.2 Лесно филтриране на клетки с разпознаване на главни и малки букви с невероятен инструмент

Ако искате да филтрирате само целия текст с главни или малки букви в колона, тук препоръчваме Специален филтър функция на Kutools за Excel. С тази функция целият текст с главни или малки букви ще бъде филтриран лесно само с няколко кликвания.

1. Изберете диапазона от колони, в който ще филтрирате текстовете, и след това щракнете Kutools плюс > Специален филтър > Специален филтър.

2. В Специален филтър диалогов прозорец, моля, направете настройките по-долу.

3. След това a Kutools за Excel се появява диалогов прозорец, за да ви каже колко клетки са намерени и ще бъдат филтрирани, щракнете върху OK бутон.

Сега всички клетки с главни или малки букви се филтрират незабавно, както е показано на екранната снимка по-долу.

Кликнете, за да научите повече за тази функция.

3.1.3 Филтриране по дължина на текста

Ако искате да филтрирате клетки по дължина на текста, например да филтрирате клетки с дължина на текста, равна на 10 знака, какво трябва да направите? Тук следните три метода могат да ви направят услуга.

3.1.3.1 Филтриране на клетки по дължина на текста с командата Филтър

Всъщност командата Filter има вграден оператор за решаване на този проблем, моля, направете следното.

1. Изберете диапазона от клетки, които да бъдат филтрирани (тук избирам B1: B27) и след това добавете филтър към този диапазон от колони, като щракнете върху Дата > Филтър.

2. Щракнете върху падащата стрелка в заглавката на колоната и след това щракнете Текстови филтри > Персонализиран филтър. Вижте екранна снимка:

3. В Персонализиран автофилтър диалогов прозорец, изберете критерии като равни, въведете 10 въпросителни знака (?) като режим на шаблон в текстовото поле и след това щракнете върху OK бутон.

Съвети: Тези 10 въпросителни знаци показват, че ще съответства на текстовия низ, чиято дължина е 10.

Сега всички клетки, чиято дължина на текстовия низ е 10 (включително интервали), се филтрират незабавно.

3.1.3.2 Филтриране на клетки по дължина на текста с формула и командата Филтър

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

1. Създайте помощна колона в съседство с оригиналния диапазон от данни. Въведете формулата по-долу в нея и след това натиснете клавиша Enter. Изберете клетката с резултати и след това я плъзнете Манипулатор за автоматично попълване надолу, за да получите другите резултати.

=LEN(B2)

Сега получавате дължината на текста на всяка клетка в определена колона.

2. Изберете помощната колона (включете заглавка), щракнете Дата > филтър за да добавите филтър към него.

3. Щракнете върху падащата стрелка, премахнете отметката от Избери всички за да премахнете отметката от всички елементи, след което поставете отметка само в квадратчето до номер 10 и накрая щракнете върху OK бутон.

Сега всички клетки, чиято дължина на текстовия низ е 10 (включително интервали), се филтрират незабавно.

3.1.3.3 Лесно филтрирайте клетки по дължина на текста с удивителен инструмент

Тук препоръчайте Специален филтър полезност на Kutools за Excel за да ви помогне лесно да филтрирате клетки по дължина на текста в Excel.

1. Изберете диапазона от колони, в който ще филтрирате клетки въз основа на определена дължина на текста, щракнете върху Kutools плюс > Специален филтър > Специален филтър. Вижте екранна снимка:

2. В Специален филтър диалогов прозорец, моля, конфигурирайте както следва.

3. В Kutools за Excel се появява диалогов прозорец, за да ви каже колко клетки са намерени и ще бъдат филтрирани, щракнете OK да продължа напред.

След това всички клетки с дължини на текстови низове, равни на 10, се филтрират, както е показано на екранната снимка по-долу.

3.2 Филтърни числа

В Excel също е много лесно да филтрирате числа с командата Number Filters.

Да предположим, че искате да филтрирате клетки с числа между 15000 20000 и XNUMX XNUMX в колона (като колона C, както е показано на екранната снимка по-долу), можете да направите следното, за да го постигнете.

1. Изберете диапазона от колони, който съдържа числата, които ще филтрирате, щракнете Дата > филтър за добавяне на филтър.

2. След като добавите филтър, моля, конфигурирайте както следва.

Съвет: Тъй като искам да филтрирам клетки с числа между 15000 и 20000, тук въвеждам 15000 и 20000 отделно в текстовите полета.

Сега клетки с числа между 15000 и 20000 се филтрират, както е показано на екранната снимка по-долу.

3.3 Филтриране на дати

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

3.3.1 Филтриране на дати по ден от седмицата или почивни дни

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

3.3.1.1 Филтриране на дати по ден от седмицата или почивни дни с формула и командата Филтър

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

1. В празна клетка (в този случай D2), въведете формулата по-долу и натиснете Въведете ключ. Изберете клетката с резултати и след това плъзнете Манипулатор за автоматично попълване над клетките по-долу, за да приложите тази формула.

=ДЕЛИЦА(A2;XNUMX)

Съвет:

Забележка: Както можете да видите, формулата връща числа от 1 да се 7, което показва деня от седмицата от Неделя да се Събота (1 е за неделя, 7 е за събота).

2. Изберете всички резултати от формулата (включете заглавната клетка), щракнете Дата > Филтър.

3. Щракнете върху падащата стрелка и след това премахнете отметката от Избери всички отметката.

След това се филтрират всички уикенди или определени дни от седмицата. Вижте екранна снимка:

3.3.1.2 Лесно филтрирайте дати по ден от седмицата или почивни дни с невероятен инструмент

Ако горният метод не е удобен за вас, препоръчайте тук Специален филтър полезност на Kutools за Excel. С тази функция можете лесно да филтрирате клетки, съдържащи всеки ден от седмицата само с няколко кликвания.

1. Изберете клетките, съдържащи датите, които искате да филтрирате въз основа на определен ден от седмицата.

2. кликване Kutools плюс > Специален филтър > Специален филтър.

3. В Специален филтър диалогов прозорец, моля, конфигурирайте както следва.

4. След това a Kutools за Excel изскача диалогов прозорец с колко клетки са намерени и ще бъдат филтрирани, щракнете OK да продължа напред.

Сега всички уикенди или всеки ден от седмицата клетките се филтрират.

3.3.2 Филтрирайте клетки по работни дни с невероятен инструмент

Освен филтриране на клетки по ден от седмицата или почивни дни, Специален филтър полезност на Kutools за Excel може също да помогне за филтриране на клетки по работни дни.

1. Приложете същите стъпки като по-горе за да активирате помощната програма за специален филтър.

2. В Специален филтър диалогов прозорец, направете настройките по-долу.

3. След това a Kutools за Excel изскача диалогов прозорец. Кликнете OK да продължа напред.

Сега всички клетки от работния ден са филтрирани.

3.4 Филтриране на формати

Обикновено Excel поддържа филтриране на данни въз основа на визуални критерии като цвят на шрифта, цвят на клетка или набори от икони, както е показано на екранната снимка по-долу.

Ако обаче искате да филтрирате данни въз основа на други визуални критерии като стил на шрифта (удебелен, курсив), ефекти на шрифта (зачертано) или специална клетка (съдържаща формули), Excel не помага за постигането. Този раздел предоставя методи, които да ви помогнат да разрешите тези проблеми.

3.4.1 Филтриране по удебелен/курсив форматиран текст

Ако приемем, че искате да филтрирате данни с удебелен или курсив, форматиращ текст, както е показано на екранната снимка по-долу, следните методи могат да ви направят услуга. Моля, направете следното.

3.4.1.1 Филтриране на удебелен/курсив, форматиран текст с формула и командата Филтър

Комбинацията от формулата Get.Cell и командата Filter може да помогне за филтриране на удебелен форматиран текст в диапазон от колони.

1. кликване формули > Определете име.

2. В Ново име диалогов прозорец, трябва да:

Синтаксис на формулата:

=GET.CELL(type_num, reference)

Аргументи на формулата

3. Изберете празна клетка в същия ред на B2, въведете формулата по-долу в нея и след това натиснете Въведете ключ. Изберете клетката с резултати, плъзнете я Манипулатор за автоматично попълване над клетките по-долу, за да приложите тази формула.

=Filter_Bold_Cells

4. Изберете целите клетки с резултати (включете заглавка), щракнете Дата > Филтър.

5. Щракнете върху падащата стрелка, само поставете отметка в квадратчето до TRUE опция и след това щракнете върху OK.

След това всички текстови клетки с получер или курсив се филтрират. Вижте екранна снимка:

3.4.1.2 Филтриране на удебелен или курсив, форматиран текст с командите Намиране и замяна и Филтриране

Можете също да използвате комбинацията от команди за намиране и замяна и филтриране, за да постигнете.

1. Изберете диапазона от колони, съдържащ удебелените или курсивните текстови клетки, които ще филтрирате, и след това натиснете Ctrl + F ключове.

2. В Търсене и заместване диалогов прозорец, трябва да конфигурирате както следва.

3. Сега всички текстови клетки с получер или курсив са избрани в оригиналния диапазон, щракнете върху Начало > Цвят на запълване, и след това изберете цвят за запълване на избраните клетки.

4. Изберете отново целия диапазон от колони, щракнете Дата > филтър за да добавите филтър към него.

5. Щракнете върху падащата стрелка, изберете Филтър по цвяти след това щракнете върху цвета на запълване, който сте посочили току-що под Филтриране по цвят на клетката. Вижте екранна снимка:

След това всички текстови клетки с получер или курсив се филтрират.

3.4.1.3 Лесно филтрирайте удебелен или курсив, форматиран текст с невероятен инструмент

Както споменахме по-горе, Специален филтър полезност на Kutools за Excel може да помогне за лесно филтриране с чувствителност към главни и малки букви, филтриране по дължина на текста, филтриране на дати. Тук ще ви научим как да прилагате тази функция за филтриране на текстови клетки с удебелен шрифт или курсив в Excel.

1. Изберете диапазона от колони (включете заглавка), който съдържа удебелени или курсивни форматирани клетки, които ще филтрирате.

2. кликване Kutools плюс > Специален филтър > Филтър Получер / Филтър Курсив. Вижте екранна снимка:

3. кликване OK в изскачането Kutools за Excel диалогов прозорец, за да продължите (този диалогов прозорец се появява, за да ви каже колко клетки отговарят на критериите).

Сега всички текстови клетки с получер или курсив са филтрирани.

3.4.2 Филтриране по зачеркнат форматиран текст

Да предположим, че сте получили списък с хора, които режат чрез добавяне на зачертани към тях, и трябва да намерите всички зачертани клетки чрез филтриране, методите по-долу могат да ви направят услуга.

3.4.2.1 Филтриране на зачеркнат форматиран текст с дефинирана от потребителя функция и командата Филтър

Можете да приложите функцията, дефинирана от потребителя, за да идентифицирате зачертаните форматирани текстови клетки и след това да използвате командата Филтър, за да филтрирате всички зачертани клетки въз основа на резултатите.

1. Натисни Друг + F11 за да отворите Microsoft Visual Basic за приложения прозорец.

2. В Microsoft Visual Basic за приложения прозорец, кликнете Поставете > Модул. След това копирайте долния VBA код в код прозорец.

Function HasStrike(Rng As Range) As Boolean
HasStrike = Rng.Font.Strikethrough
End Function

3. Натисни Друг + Q ключове за затваряне на Microsoft Visual Basic за приложения прозорец.

4. Изберете празна клетка (тази клетка трябва да е в същия ред на клетката, която искате да изчислите), въведете формулата по-долу и натиснете Въведете ключ. Изберете клетката с резултати, плъзнете я Манипулатор за автоматично попълване над клетката по-долу, за да приложите тази формула.

=HasStrike(B2)

Забележка: Ако съответната клетка има ефект на зачеркнат шрифт, тя се връща ВЯРНО, иначе се връща НЕВЯРНО.

5. Изберете целите клетки с резултати (включете заглавната клетка), щракнете Дата > Филтър.

6. След това щракнете върху падащата стрелка > поставете отметка само в квадратчето до опцията TRUE > щракнете OK. Вижте показаната екранна снимка.

Сега можете да видите, че всички зачертани форматирани клетки са филтрирани.

3.4.2.2 Лесно филтриране на зачеркнат форматиран текст с удивителен инструмент

С Специален филтър полезност на Kutools за Excel, всички зачертани форматирани клетки могат да бъдат филтрирани директно само с няколко кликвания.

1. Изберете диапазона от колони, в който ще филтрирате всички зачертани форматирани клетки, щракнете Kutools плюс > Специален филтър > Филтър зачертано.

2. След това a Kutools за Excel изскача диалогов прозорец, за да ви каже колко клетки са квалифицирани, щракнете OK да продължа напред.

След това всички зачертани форматирани клетки се филтрират, както е показано на екранната снимка по-долу.

3.4.3 Филтриране по шрифт или цвят на фона

Както споменахме в самото начало в този раздел за формати на филтри, Excel поддържа филтриране на данни въз основа на визуални критерии като цвят на шрифта, цвят на клетка или набори от икони с вградената си функция. Този раздел демонстрира как да приложите Филтър по цвят функция за филтриране на клетки по шрифт или цвят на фона в детайли. Междувременно препоръчваме удобна функция на трета страна, която да помогне за разрешаването на този проблем.

3.4.3.1 Филтрирайте по един шрифт или фонов цвят с командата Филтър

Можете директно да приложите функцията Филтриране по цвят на командата Филтър, за да филтрирате клетки по конкретен шрифт или цвят на фона в Excel.

1. Изберете диапазона от колони, в който ще филтрирате клетките по шрифт или цвят на фона, и след това щракнете Дата > Филтър.

2. Щракнете върху падащата стрелка > Филтър по цвят. След това можете да видите всички цветове на клетките и цветовете на шрифта на текущия диапазон от колони, които са изброени. Щракнете върху произволен цвят на клетка или цвят на шрифт ще филтрирате всички клетки въз основа на него.

3.4.3.2 Филтриране по множество фонови цветове с дефинирана от потребителя функция и командата Филтър

Ако искате да филтрирате клетки по множество фонови цветове, моля, приложете метода по-долу.

Да предположим, че искате да филтрирате всички клетки с оранжеви и сини фонови цветове в колона B, както е показано на екранната снимка по-долу. Първо, трябва да изчислите цветовия индекс на тези клетки.

1. Натисни Друг + F11 клавиши едновременно.

2. В Microsoft Visual Basic за приложения прозорец, кликнете Поставете > Модул. След това копирайте долния VBA код в прозореца на кода.

VBA код: Вземете индекс на цвета на фона на клетката

Function GetColor(x As Range) As Integer
GetColor = x.Interior.ColorIndex
End Function

3. Натисни Друг + Q ключове за затваряне на Microsoft Visual Basic за приложения прозорец.

4. В нова колона въведете заглавка в първата клетка (тази клетка трябва да е на същия ред на заглавката на оригиналния диапазон).

5. Изберете празна клетка до заглавната клетка (тук избирам E2), въведете формулата по-долу в нея и натиснете Въведете ключ. Изберете клетката с резултати и я плъзнете Манипулатор за автоматично попълване над клетките по-долу, за да приложите тази формула.

=GetColor(B2)

Забележка: Ако клетката няма цвят на запълване, тя връща -4142.

6. Изберете клетките на помощната колона (включете заглавка), щракнете Дата > филтър за да добавите филтър към колоната.

7. Щракнете върху стрелката за падащо меню, за да отворите падащия списък, и след това конфигурирайте както следва.

Сега клетките се филтрират по определени цветове на фона, както е показано на екранната снимка по-долу.

3.4.3.3 Лесно филтрирайте по шрифт или цвят на фона с невероятен инструмент

Несъмнено е лесно да приложите вградената функция Филтриране по цвят за филтриране на клетки по шрифт или цвят на фона. Недостатъкът обаче е, че полето с падащ списък покрива съдържанието на оригиналните данни, така че не можем да прегледаме данните по всяко време, за да изберем правилно шрифт или цвят на фона. За да избегнете този проблем, тук препоръчваме Специален филтър полезност на Kutools за Excel.

1. Изберете диапазона от колони, в който ще филтрирате клетките по шрифт или цвят на фона, и след това щракнете Kutools плюс > Специален филтър > Специален филтър.

2. В Специален филтър диалогов прозорец, моля, конфигурирайте както следва.

След това всички клетки с определен цвят на шрифта или цвят на фона в избрания диапазон се филтрират.

3.4.4 Филтриране на клетки, съдържащи формули

Ако имате дълъг списък с данни, който съдържа както действителни стойности, така и формули, и трябва да филтрирате само клетките с формули, какво можете да направите? Този раздел предоставя два метода за постигането му.

3.4.4.1 Филтриране на клетки с формула с дефинирана от потребителя функция и командата Филтър

Първо, трябва да намерите всички клетки с формула в списъка с функцията, дефинирана от потребителя, след което да приложите командата Филтър, за да филтрирате клетките с формула въз основа на резултатите.

1. Натисни Друг + F11 за да отворите Microsoft Visual Basic за приложения прозорец.

2. В Microsoft Visual Basic за приложения прозорец, кликнете Поставете > Модул, след това копирайте кода на VBA по-долу в прозореца на кода.

Function HasFormula(Cell)
HasFormula = Cell.HasFormula
End Function

3. Натисни Друг + Q ключове за затваряне на Microsoft Visual Basic за приложения прозорец.

4. Изберете празна клетка, тази клетка трябва да е в същия ред на клетката, която искате да проверите дали е клетка с формула, въведете формулата по-долу в нея и след това натиснете Въведете ключ. Изберете клетката с резултати, плъзнете я Манипулатор за автоматично попълване над клетките по-долу, за да приложите тази формула.

=Има Формула(C2)

Както е показано на екранната снимка по-горе, резултатите са FALSE намлява ВЯРНО, които показват, че ако съответната клетка е клетка с формула, тя връща TRUE, в противен случай връща FALSE.

5. Изберете клетките с резултати (включете заглавната клетка), щракнете Дата > филтър за да добавите филтър към него.

6. Щракнете върху падащата стрелка, само поставете отметка в квадратчето до TRUE , след което щракнете върху OK.

След това можете да видите, че всички клетки с формула са филтрирани.

3.4.4.2 Лесно филтриране на клетки с формули с удивителен инструмент

Тук демонстрирайте помощната програма за специален филтър на Kutools за Excel, за да ви помогне лесно да филтрирате клетки с формула в списък само с няколко кликвания.

1. Изберете списъка, в който искате да филтрирате всички клетки с формули, след което щракнете Kutools плюс > Специален филтър > Филтърна формула.

2. В Kutools за Excel изскача диалогов прозорец, за да ви каже колко клетки са квалифицирани, щракнете OK да продължа напред.

След това всички клетки с формула се филтрират, както е показано на екранната снимка по-долу.

Освен това можете да използвате Специален филтър функция на Kutools за Excel за лесно филтриране на клетки с друго форматиране, като например:

Филтрирайте всички клетки с коментари, щракнете, за да научите повече...

Филтрирайте всички обединени клетки въз основа на определена стойност, щракнете, за да научите повече...

3.5 Филтър със заместващ знак

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

В Excel има само 3 заместващи знака:

Заместващ знак	Описание	Пример
* (звездичка)	Представлява произволен брой знаци	Например, * горски плодове намира “къпина","ягода","Боровинка" и така нататък
? (въпросителен знак)	Представлява всеки отделен знак	Например, л?к намира “лижа","заключване","липса" и така нататък
~ (прилив) последвано от *, ? или ~	Представлява реално *, ? or ~ характер	Например, Лице~*Пазарувайте находки “Face*Shop”

Нека да видим как да използваме заместващ знак при филтриране.

Да предположим, че трябва да филтрирате всички клетки, които завършват с Market в колона B, както е показано на екранната снимка по-долу, моля, направете следното.

1. Първо създайте диапазон от критерии. Въведете заглавка, същата като оригиналната заглавка на колона, и след това въведете критериите за филтър в клетката по-долу. Вижте екранна снимка:

2. кликване Дата > Advanced.

3. В откриването Разширен филтър диалогов прозорец, конфигурирайте както следва.

След това всички клетки, завършващи с Market, се филтрират. Вижте екранна снимка:

Използването на * и ~ заместващи символи при филтриране е същото като горните операции.

3.6 Филтър с вградено поле за търсене

Ако използвате Excel 2010 или по-нови версии, може да забележите, че във филтъра на Excel има вградено поле за търсене. Този раздел ще демонстрира как да използвате това поле за търсене за филтриране на данни в Excel.

Както е показано на екранната снимка по-долу, искате да филтрирате всички клетки, които съдържат „Пазар“, полето за търсене може да ви помогне лесно да го направите.

1. Изберете диапазона от колони, в който ще филтрирате данните, щракнете Дата > филтър за да добавите филтър към него.

2. Щракнете върху падащата стрелка, въведете „пазар“ в полето за търсене и след това щракнете OK.

Можете да видите, че всички квалифицирани текстове са изброени в реално време, докато въвеждате данни в полето за търсене.

След това всички клетки, съдържащи „Пазар“, се филтрират, както е показано на екранната снимка по-долу.

---

4. Копирайте само видими данни (игнорирайте скрити или филтрирани данни)

По подразбиране Excel копира както видими, така и скрити клетки. Ако искате да копирате само видимите клетки след филтриране, можете да опитате един от методите по-долу.

Копирайте видими данни само с клавишни комбинации

Можете да използвате клавишни комбинации, за да изберете само видимите клетки и след това ръчно да ги копирате и поставите на необходимото място.

1. Изберете диапазона, който искате да копирате само видимите клетки. В тази стъпка се избират както видими, така и скрити клетки.

2. Натисни Друг намлява ; ключове едновременно. Сега са избрани само видимите клетки.

3. Натисни Ctrl + C клавиши, за да копирате избраните клетки, и след това натиснете Ctrl + V ключове, за да ги поставите.

Копирайте лесно видими данни само с невероятен инструмент

Представете тук Поставете във видимо функция на Kutools за Excel за лесно копиране на видими данни само в Excel. Освен това с тази функция можете да копирате и поставяте стойности само в видими клетки в диапазон, който е филтриран.

1. Изберете филтрирания диапазон, който искате да копирате само видими клетки, след което щракнете Kutools > Обхват > Паста to Видим > Всички / Само поставяне на стойности.

За клетки с формула изберете Всички копира както резултата, така и формулите, изберете Само поставяне на стойности копира само действителните стойности.

2. При изскачане Поставяне във видимия диапазон изберете празна клетка за извеждане на копираните клетки и след това щракнете OK.

Тогава само видимите клетки в избрания филтриран диапазон се копират и поставят на ново място.

Забележка: Ако целевият диапазон е бил филтриран, избраните стойности ще бъдат поставени само във видимите клетки.

---

5. Изтрийте скрити или видими редове след филтриране

За филтриран списък може да се наложи да изтриете скритите редове, за да запазите само видимите данни. Тук, в този раздел, ще научите три метода за изтриване на скрити или видими редове във филтриран списък в Excel.

Изтрийте всички скрити редове от текущия работен лист с VBA код

Кодът VBA по-долу може да помогне за изтриване на всички скрити редове от текущия работен лист в Excel.

Забележка: Този VBA премахва не само скрития ред във филтрирания списък, но също така премахва редовете, които сте скрили ръчно.

1. В работния лист съдържа скрити редове, които искате да премахнете, натиснете Друг + F11 клавиши едновременно, за да отворите Microsoft Visual Basic за приложения прозорец.

2. В Microsoft Visual Basic за приложения прозорец, кликнете Поставете > Модул, и след това копирайте кода на VBA по-долу в прозореца на модула.

VBA код: Изтрийте всички скрити редове от текущия работен лист

Sub RemoveHiddenRows()
    Dim xRow As Range
    Dim xRg As Range
    Dim xRows As Range
    On Error Resume Next
    Set xRows = Intersect(ActiveSheet.Range("A:A").EntireRow, ActiveSheet.UsedRange)
    If xRows Is Nothing Then Exit Sub
        For Each xRow In xRows.Columns(1).Cells
            If xRow.EntireRow.Hidden Then
                If xRg Is Nothing Then
                    Set xRg = xRow
                Else
                    Set xRg = Union(xRg, xRow)
                End If
            End If
        Next
        If Not xRg Is Nothing Then
            MsgBox xRg.Count & " hidden rows have been deleted", , "Kutools for Excel"
            xRg.EntireRow.Delete
        Else
            MsgBox "No hidden rows found", , "Kutools for Excel"
        End If
End Sub

3. Натисни F5 ключ за изпълнение на кода.

4. След това a Kutools за Excel се появява диалогов прозорец, за да ви каже колко скрити реда са премахнати, щракнете OK да го затворите.

Сега всички скрити редове (включително автоматично скрити редове и ръчно скрити редове) са премахнати.

Изтрийте видимите редове след филтриране с функцията Go To

Ако искате да премахнете само видими редове в определен диапазон, Отидете в функция може да ви помогне.

1. Изберете филтрирания диапазон, който искате да премахнете видимите редове, натиснете F5 ключ за отваряне на Отидете в диалогов прозорец.

2. В Отидете в кликнете върху Специален бутон.

3. В Отидете на Special диалогов прозорец, изберете Само видими клетки опция и след това щракнете върху OK бутон.

4. Сега всички видими клетки са избрани. Щракнете с десния бутон върху избрания диапазон и щракнете Изтриване на реда в контекстното меню.

След това всички видими клетки се изтриват.

Изтривайте лесно скрити или видими редове след филтриране с невероятен инструмент

Горните методи са досадни и отнемат много време. Тук препоръчайте Изтриване на скрити (видими) редове и колони функция на Kutools за Excel. С тази функция можете лесно да изтриете скрити или видими редове не само в избран диапазон, но и в текущия работен лист, няколко избрани работни листа или цялата работна книга. Моля, направете следното.

1. Изберете диапазон, от който искате да премахнете всички скрити или видими редове.

Забележки:

2. кликване Kutools > Изтрий > Изтриване на скрити (видими) редове и колони.

3. В Изтриване на скрити (видими) редове и колони диалогов прозорец, трябва да конфигурирате както следва.

4. Тогава всички видими или скрити редове се изтриват наведнъж. Междувременно се появява диалогов прозорец, за да ви каже броя на изтритите редове, щракнете OK за да завърши цялата операция.

---

6. Филтрирайте в няколко листа

Обикновено е лесно да филтрирате данни в работен лист. В този раздел ще научите как да филтрирате данни с едни и същи критерии в множество работни листове, които имат обща структура на данните.

Да предположим, че една работна книга съдържа три работни листа, както е показано на екранната снимка по-долу, сега искате да филтрирате данни в тези три работни листа едновременно със същите критерии "Продукт = KTE”, долният VBA код може да ви направи услуга.

1. Натисни Друг + F11 за да отворите Microsoft Visual Basic за приложения прозорец.

2. В Microsoft Visual Basic за приложения прозорец, кликнете Поставете > Модул, и след това копирайте кода на VBA по-долу в прозореца на модула.

VBA код: Филтрирайте данни в множество работни листове едновременно

Sub apply_autofilter_across_worksheets()
'Updateby Extendoffice 20210518
    Dim xWs As Worksheet
    On Error Resume Next
    For Each xWs In Worksheets
        xWs.Range("A1").AutoFilter 1, "=KTE"
    Next
End Sub

Забележка: Линията "Xws.Range(“A1”.AutoFilter 1, “=KTE”)” в кода показва, че ще филтрирате данни в колона A с критерия =KTE, а числото 1 е номерът на колона на колона A. Можете да ги промените според вашите нужди. Например, ако искате да филтрирате всички числа, които са по-големи от 500 в колона B, можете да промените този ред на „Xws.Range(“B1”.AutoFilter 2, “>500”)".

3. Натисни F5 ключ за изпълнение на кода.

След това посочените колони се филтрират едновременно във всички работни листове на текущата работна книга. Вижте резултатите по-долу.

---

7. Приложете отново филтър след промяна на данните

Понякога може да сте направили промени за филтриран диапазон. Резултатът от филтъра обаче остава същият, независимо какви промени сте направили в диапазона (вижте екранната снимка по-долу). В този раздел ще ви покажем два метода за ръчно или автоматично повторно прилагане на филтъра върху текущия диапазон, така че промените, които сте направили, да бъдат включени.

Ръчно приложете отново филтъра с командата Reapply

Excel има вградена Повторно кандидатстване функция за подпомагане на ръчното повторно прилагане на филтъра. Можете да кандидатствате по следния начин.

Кликнете Дата > Повторно кандидатстване за да приложите отново филтъра в текущия лист.

След това можете да видите, че филтрираният диапазон се прилага отново, за да включи промените, които сте направили.

Автоматично повторно прилагане на филтър с VBA код

Ако филтрираният списък трябва да се променя често, трябва да щракнете многократно, за да приложите тази функция за повторно прилагане. Тук предоставете VBA код, за да помогнете за автоматично повторно прилагане на филтър в реално време, когато данните се променят.

1. В работния лист се съдържа филтърът, който искате да приложите отново автоматично, щракнете с десния бутон върху раздела на листа и изберете Вижте кода.

2. В откриването Microsoft Visual Basic за приложения прозорец, копирайте кода на VBA по-долу в прозореца на кода.

VBA код: Автоматично повторно прилагане на филтъра при промяна на данните

Private Sub Worksheet_Change(ByVal Target As Range)
   Sheets("Sheet2").AutoFilter.ApplyFilter
End Sub

Забележка: в кода, „Лист2“ е името на текущия работен лист. Можете да го промените според вашите нужди.

3. Натисни Друг + Q ключове за затваряне на Microsoft Visual Basic за приложения прозорец.

Отсега нататък, когато променяте данни във филтрирания списък, филтрираният резултат ще бъде динамично коригиран. Вижте gif снимката по-долу.

---

8. Почистете или премахнете филтъра

Научихме как да добавяме, прилагаме и използваме филтър в горното съдържание. Тук ще научим как да изчистите или премахнете филтъра в Excel.

8.1 Изчистване на филтър от колона

След като приложите филтър в колона, ако искате да го изчистите, щракнете върху филтрираната икона и след това щракнете Изчистване на филтъра от „Име на заглавката“ от падащото меню.

8.2 Изчистете всички филтри в работен лист

Ако сте приложили филтър към няколко колони и искате да изчистите всички едновременно, щракнете Дата > Ясно.

След това всички филтри се изчистват, както е показано на екранната снимка по-долу.

8.3 Изчистете филтрите от всички работни листове в текущата работна книга

Да предположим, че сте приложили филтри към множество работни листове в работна книга и искате да изчистите тези филтри наведнъж. Кодът VBA по-долу може да ви направи услуга.

1. Отворете работната книга, ще изчистите всички филтри от нея и след това натиснете Друг + F11 клавиши едновременно.

2. В откриването Microsoft Visual Basic за приложения прозорец, кликнете Поставете > Модул, и след това копирайте кода на VBA по-долу в прозореца на модула.

VBA код: Изчистете филтрите от всички работни листове в текущата работна книга

Sub Auto_Open()
'Updated by Extendoffice 20201113
    Dim xAF As AutoFilter
    Dim xFs As Filters
    Dim xLos As ListObjects
    Dim xLo As ListObject
    Dim xRg As Range
    Dim xWs As Worksheet
    Dim xIntC, xF1, xF2, xCount As Integer
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each xWs In Application.Worksheets
        xWs.ShowAllData
        Set xLos = xWs.ListObjects
        xCount = xLos.Count
        For xF1 = 1 To xCount
         Set xLo = xLos.Item(xF1)
         Set xRg = xLo.Range
         xIntC = xRg.Columns.Count
         For xF2 = 1 To xIntC
            xLo.Range.AutoFilter Field:=xF2
         Next
        Next
    Next
    Application.ScreenUpdating = True
End Sub

3. Натисни F5 ключ за изпълнение на кода. След това всички филтри се изчистват от всички работни листове в текущата работна книга.

8.4 Премахнете всички филтри в работен лист

Горните методи помагат само за изчистване на филтрирания статус, а филтрите остават в работния лист. Ако искате да премахнете всички филтри от работен лист, опитайте методите по-долу.

Премахнете всички филтри в работен лист, като изключите филтъра

Кликнете Дата > филтър за да изключите функцията (бутонът Филтър не е в осветено състояние).

Премахнете всички филтри в работен лист чрез клавиш за бърз достъп

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

В работния лист съдържа филтри, които искате да премахнете, натиснете Ctrl + Превключване + L клавиши едновременно.

Тогава всички филтри в текущия работен лист се премахват незабавно.