За по-голямата част от потребителите на Excel, когато думата „филтриране на данни“ изникне в главата им, само обичайният класически филтър от раздела Данни – Филтър (Данни — Филтър):
Такъв филтър е познато нещо, без съмнение, и в повечето случаи ще свърши работа. Има обаче ситуации, когато трябва да филтрирате по голям брой сложни условия в няколко колони едновременно. Обичайният филтър тук не е много удобен и искам нещо по-мощно. Такъв инструмент може да бъде усъвършенстван филтър, особено с малко „довършване с пила“ (по традиция).
База
За да започнете, вмъкнете няколко празни реда над вашата таблица с данни и копирайте заглавката на таблицата там – това ще бъде диапазон с условия (маркирани в жълто за яснота):
Трябва да има поне един празен ред между жълтите клетки и оригиналната таблица.
Именно в жълтите клетки трябва да въведете критериите (условията), по които след това ще се извърши филтрирането. Например, ако трябва да изберете банани в московския „Ашан“ през III тримесечие, тогава условията ще изглеждат така:
За да филтрирате, изберете произволна клетка в диапазона с изходните данни, отворете раздела Дата И кликнете върху Освен това (Данни — Разширени). В прозореца, който се отваря, диапазонът с данни вече трябва да е въведен автоматично и ще трябва само да посочим диапазона от условия, т.е. A1:I2:
Моля, имайте предвид, че диапазонът от условия не може да бъде разпределен „с марж“, т.е. не можете да изберете допълнителни празни жълти редове, тъй като празна клетка в диапазона от условия се възприема от Excel като липса на критерий и цяла празна линия като заявка за показване на всички данни без разбор.
прекъсвач Копирайте резултата на друго място ще ви позволи да филтрирате списъка не точно там на този лист (както при обикновен филтър), а да разтоварите избраните редове в друг диапазон, който след това ще трябва да бъде посочен в полето Поставете резултата в диапазон. В този случай не използваме тази функция, напускаме Списъкът с филтри е на място и кликнете OK. Избраните редове ще бъдат показани на листа:
Добавяне на макрос
„Е, къде е удобството тук?“ питаш и ще си прав. Не само трябва да въведете условия в жълтите клетки с ръцете си, но и да отворите диалогов прозорец, да въведете диапазони там, да натиснете OK. Жалко, съгласен съм! Но „всичко се променя, когато дойдат ©“ – макроси!
Работата с разширен филтър може значително да се ускори и опрости с помощта на прост макрос, който автоматично ще стартира разширения филтър, когато се въвеждат условия, т.е. промяна на която и да е жълта клетка. Щракнете с десния бутон върху раздела на текущия лист и изберете командата Изходен текст (Програмен код). В прозореца, който се отваря, копирайте и поставете следния код:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:I5")) Is Nothing Then On Error Resume Next ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Диапазон("A1").CurrentRegion End If End Sub Тази процедура ще се стартира автоматично, когато някоя клетка в текущия работен лист бъде променена. Ако адресът на променената клетка попада в жълтия диапазон (A2:I5), тогава този макрос премахва всички филтри (ако има такива) и прилага отново разширения филтър към таблицата с изходни данни, започваща с A7, т.е. всичко ще бъде филтрирано незабавно, веднага след въвеждане на следващото условие:
Така че всичко е много по-добре, нали? 🙂
Изпълнение на сложни заявки
Сега, когато всичко се филтрира в движение, можем да навлезем малко по-дълбоко в нюансите и да разглобим механизмите на по-сложните заявки в разширения филтър. В допълнение към въвеждането на точни съвпадения, можете да използвате различни заместващи знаци (* и ?) и знаци за математическо неравенство в редица условия, за да приложите приблизително търсене. Регистърът на буквите няма значение. За по-голяма яснота съм обобщил всички възможни опции в таблица:
| критерий | Резултат |
| гр* или гр | всички клетки, започващи с GrIe Grухо, Grмаймуна, Grанат и т.н. |
| = лук | всички клетки точно и само с думата Лък, т.е. точно съвпадение |
| *liv* или *liv | клетки, съдържащи Лив как подчертават, т.е ОЛивче, Ливep, СпоредЛив и т.н. |
| =p*v | думи, започващи с П и завършва с В ie Ппървив, Петерв и т.н. |
| като | думи, започващи с А и допълнително съдържащи СIe АPELсin, АНанас, Asai и т.н. |
| =*s | думи, завършващи на С |
| =???? | всички клетки с текст от 4 знака (букви или цифри, включително интервали) |
| =m??????n | всички клетки с текст от 8 знака, започващ с М и завършва с НIe Мandariн, Мбезпокойствон и т.н. |
| =*n??a | всички думи, завършващи с А, където е 4-та буква от края НIe Лъчнikа, Спореднozа и т.н. |
| >=e | всички думи, започващи с Э, Ю or Я |
| <>*о* | всички думи, които не съдържат буква О |
| <>*вич | всички думи, с изключение на тези, които завършват на ХИВ (например филтрирайте жените по бащино име) |
| = | всички празни клетки |
| <> | всички непразни клетки |
| > = 5000 | всички клетки със стойност, по-голяма или равна на 5000 |
| 5 или =5 | всички клетки със стойност 5 |
| >=3 г | всички клетки с дата след 18 март 2013 г. (включително) |
Фините точки:
- Знакът * означава произволен брой знаци, а ? – който и да е знак.
- Логиката при обработката на текстови и цифрови заявки е малко по-различна. Така например клетка с условие с числото 5 не означава търсене на всички числа, започващи с пет, но клетка с условие с буквата B е равна на B*, т.е. ще търси всеки текст, започващ с буквата B.
- Ако текстовата заявка не започва със знака =, тогава можете мислено да поставите * в края.
- Датите трябва да бъдат въведени в американския формат месец-ден-година и чрез дроб (дори ако имате Excel и регионални настройки).
Логически връзки И-ИЛИ
Условията, записани в различни клетки, но в един ред, се считат за свързани помежду си с логически оператор И (И):
Тези. филтър банани за мен през третото тримесечие, точно в Москва и в същото време от Auchan.
Ако трябва да свържете условия с логически оператор OR (OR), тогава те просто трябва да бъдат въведени в различни редове. Например, ако трябва да намерим всички поръчки на мениджър Волина за московски праскови и всички поръчки за лук през третото тримесечие в Самара, тогава това може да бъде посочено в набор от условия, както следва:
Ако трябва да наложите две или повече условия на една колона, тогава можете просто да дублирате заглавката на колоната в диапазона на критериите и да въведете второто, третото и т.н. под него. условия. Така например можете да изберете всички транзакции от март до май:
Като цяло, след „завършване с файл“, разширеният филтър се оказва доста приличен инструмент, на места не по-лош от класическия автоматичен филтър.
- Суперфилтър върху макроси
- Какво представляват макросите, къде и как да вмъкнете макро код във Visual Basic
- Интелигентни таблици в Microsoft Excel