Гледайте видео - Excel Advanced Filter
Разширеният филтър на Excel е една от най-подценяваните и недостатъчно използвани функции, на които съм попадал.
Ако работите с Excel, сигурен съм, че сте използвали (или поне сте чували за обикновения филтър на Excel). Той бързо филтрира набор от данни въз основа на подбор, посочен текст, номер или други подобни критерии.
В това ръководство ще ви покажа някои готини неща, които можете да направите, като използвате разширения филтър на Excel.
Но първо … Какво е разширеният филтър на Excel?
Разширеният филтър на Excel - както подсказва името - е усъвършенстваната версия на обикновения филтър. Можете да използвате това, когато трябва да използвате по -сложни критерии за филтриране на набора от данни.
Ето някои разлики между обикновения филтър и разширения филтър:
- Докато обикновеният филтър за данни ще филтрира съществуващия набор от данни, можете да използвате разширения филтър на Excel, за да извлечете набора от данни и на друго място.
- Разширеният филтър на Excel ви позволява да използвате сложни критерии. Например, ако имате данни за продажбите, можете да филтрирате данни по критерий, при който търговският представител е Боб, а регионът е или на север, или на юг (ще видим как да направите това в примерите). Поддръжката на Office има някои добри обяснения за това.
- Можете да използвате разширения филтър на Excel, за да извлечете уникални записи от вашите данни (повече за това след секунда).
EXCEL ADVANCED FILTER (Примери)
Сега нека да разгледаме някои примери за използване на разширения филтър в Excel.
Пример 1 - Извличане на уникален списък
Можете да използвате Excel Advanced Filter за бързо извличане на уникални записи от набор от данни (или с други думи премахване на дубликати).
В Excel 2007 и по -нови версии има опция за премахване на дубликати от набор от данни. Но това променя съществуващия ви набор от данни. За да запазите оригиналните данни непокътнати, трябва да създадете копие на данните и след това да използвате опцията Премахване на дубликати. Разширеният филтър на Excel ще ви позволи да изберете местоположение, за да получите уникален списък.Нека да видим как да използваме разширени филтри, за да получим уникален списък.
Да предположим, че имате набор от данни, както е показано по -долу:
Както можете да видите, в този набор от данни има дублирани записи (маркирани в оранжево). Това може да се дължи на грешка при въвеждане на данни или резултат от компилиране на данни.
В такъв случай можете да използвате инструмента Excel Advanced Filter, за да получите бързо списък с всички уникални записи на различно място (така че оригиналните ви данни да останат непокътнати).
Ето стъпките за получаване на всички уникални записи:
- Изберете целия набор от данни (включително заглавките).
- Отидете в раздела Данни -> Сортиране и филтриране -> Разширени. (Можете също да използвате клавишната комбинация - Alt + A + Q). Това ще отвори диалоговия прозорец Разширен филтър.
- В диалоговия прозорец Разширен филтър използвайте следните подробности:
- Действие: Изберете опцията „Копиране на друго място“. Това ще ви позволи да посочите местоположението, където можете да получите списъка с уникални записи.
- Списък: Уверете се, че се отнася до набора от данни, от който искате да намерите уникални записи. Също така се уверете, че заглавките в набора от данни са включени.
- Обхват на критериите: Оставете това празно.
- Копирай в: Посочете адреса на клетката, където искате да получите списъка с уникални записи.
- Копирайте само уникални записи: Проверете тази опция.
- Щракнете върху OK.
Това незабавно ще ви даде списък с всички уникални записи.
Внимание: Когато използвате Advanced Filter, за да получите уникалния списък, уверете се, че сте избрали и заглавката. Ако не го направите, той ще разгледа първата клетка като заглавка.
Пример 2 - Използване на критерии в Excel Advanced Filter
Получаването на уникални записи е едно от многото неща, които можете да направите с разширения филтър на Excel.
Основната му полезност се състои в способността му да позволява използването на сложни критерии за филтриране на данни.
Ето какво имам предвид под сложни критерии. Да предположим, че имате набор от данни, както е показано по -долу и искате бързо да получите всички записи, където продажбите са по -големи от 5000, а регионът е САЩ.
Ето как можете да използвате разширения филтър на Excel за филтриране на записите въз основа на посочените критерии:
- Първата стъпка, когато използвате Excel Advanced Filter със сложни критерии, е да посочите критериите. За да направите това, копирайте заглавките и ги поставете някъде в работния лист.
- Посочете критериите, за които искате да филтрирате данните. В този пример, тъй като искаме да получим всички записи за САЩ с продажби над 5000, въведете „САЩ“ в клетката под Регион и> 5000 в клетката под Продажби. Това сега ще се използва като вход в Advanced Filter за получаване на филтрираните данни (както е показано в следващите стъпки).
- Изберете целия набор от данни (включително заглавките).
- Отидете в раздела Данни -> Сортиране и филтриране -> Разширени. Това ще отвори диалоговия прозорец Разширен филтър.
- В диалоговия прозорец Разширен филтър използвайте следните подробности:
- Действие: Изберете опцията „Копиране на друго място“. Това ще ви позволи да посочите местоположението, където можете да получите списъка с уникални записи.
- Списък: Уверете се, че се отнася до набора от данни, от който искате да намерите уникални записи. Също така се уверете, че заглавките в набора от данни са включени.
- Обхват на критериите: Посочете критериите, които изградихме в стъпките по -горе. В този пример това би било F1: I3.
- Копирай в: Посочете адреса на клетката, където искате да получите списъка с уникални записи.
- Копирайте само уникални записи: Проверете тази опция.
- Щракнете върху OK.
Това незабавно ще ви даде всички записи, където регионът е САЩ и продажбите са повече от 5000.
Горният пример е случай, когато филтрирането се извършва въз основа на два критерия (САЩ и продажби по -големи от 5000).
Разширеният филтър на Excel ви позволява да създавате много различни комбинации от критерии.
Ето няколко примера за това как можете да конструирате тези филтри.
Използване на критериите И
Когато искате да използвате И критерии, трябва да го посочите под заглавката.
Например:
- За да филтрирате записи, когато регионът е САЩ, а търговският представител е Джо.
- За филтриране на записи, когато регионът е САЩ И стойността на продажбите е по -голяма от 5000.
- Когато регионът е САЩ И продажбите се записват след 31-03-2017.
Използване на критериите ИЛИ
Когато искате да използвате ИЛИ критерии, трябва да посочите критериите в същата колона.
Например:
- За да филтрирате записи, когато регионът е САЩ ИЛИ регионът е Азия.
- За да филтрирате записи, когато търговският представител е БОБ ИЛИ Марта.
Пример 3 - Използване на WILDCARD символи в разширения филтър в Excel
Разширеният филтър на Excel също позволява използването на заместващи знаци при изграждането на критериите.
В Excel има три заместващи знака:
- * (звездичка) - Той представлява произволен брой знаци. Например, ex* може да означава excel, excels, например, експерт и т.н.
- ? (въпросителен знак) - Той представлява един единствен знак. Например, Tr? Mp може да означава Тръмп или Бродяга.
- ~ (тилда) - Използва се за идентифициране на заместващ знак (~, *,?) В текста.
Сега нека видим как можем да използваме тези заместващи знаци, за да направим някакво разширено филтриране в Excel.
- За да филтрирате записи, където името на търговския представител започва от J.
Имайте предвид, че * представлява произволен брой знаци. Така че всеки представител с името, започващо с J, ще бъде филтриран по тези критерии.
По същия начин можете да използвате и другите два заместващи знака.
Забележка: В случай, че използвате Office 365, трябва да проверите функцията FILTER. Той може да направи много неща, които усъвършенстваният филтър може да направи с проста формула.
ЗАБЕЛЕЖКА:
- Не забравяйте, че заглавките в критериите трябва да са точно същите като тези в набора от данни.
- Разширеното филтриране не може да бъде отменено, когато се копира на други места.
