Работа с текстови филтри в excel. Разширен филтър в Excel

Помислете как можете да търсите информация в MS Excel с помощта на филтри.

За да направите това, отворете Excel и скицирайте малка таблица в него.

Изберете която и да е клетка в заглавния ред, след което отидете на раздела " Данни"и щракнете върху бутона" Филтър»:

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

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

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

Основи на филтъра

Прилагане на филтри към таблица

Кликнете върху иконата в колоната "Мениджър". Ще се отвори следното меню:

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

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

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

Например, трябва да маркирате само 2 стойности от няколко десетки. Премахването на флага ръчно от всяка позиция, с изключение на тези, от които се нуждаете, отнема доста време. За да ускорите този процес, премахнете отметката от квадратчето „(Избери всички)“. Това ще изчисти всички други квадратчета за отметка. Сега можете да маркирате само тези елементи, които ви трябват.

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

Например, трябва да намерите всички заповеди на управител Иванов от 18.01.2014 г.

За да започнете, щракнете върху в колоната "Мениджър" и изберете Иванова.

Сега щракнете върху в колоната Дата на изпращане, премахнете отметката от „(Изберете всички)“ и изберете 01/18/2014 или въведете 18 в лентата за търсене и щракнете върху OK.

Таблицата ще изглежда така:

По същия начин можете да продължите да филтрирате данните по колоните "Описание", "Количество" и т.н.

Обърнете внимание, че в колоните, които са филтрирани, иконата се променя на .

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

Отмяна на филтъра

За да премахнете всички филтри наведнъж, отидете на " Данни“ и кликнете върху бутона „ ясно».

Ако искате да премахнете филтъра само от една колона, оставяйки филтри за други, щракнете върху иконата на тази колона, например „Дата на изпращане“ и кликнете върху елемента „Премахване на филтъра от<Название столбца>»:

или

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

Преди

След

Допълнителни настройки на филтъра

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

Текстови филтри

Щракнете върху иконата на колоната „Мениджър“, задръжте курсора на мишката над „Текстови филтри“, изчакайте да се появи менюто и изберете някой от критериите за избор или „Персонализиран филтър...“. Ще се появи следният прозорец:

• 1. Условия "се равнява"или "не е равно"приемете, че желаният израз е сто процента същият като съдържанието на клетката. Критерий "се равнява"оставя само онези редове в таблицата, които съдържат избраната стойност. Съответно критерият "не е равно"оставя всички стойности с изключение на избраната. За да опростите задачата, можете да изберете желаната стойност от падащия списък:

• 2. Условия "Повече ▼"и "по-малко"приемете, че в таблицата ще останат стойности, които по азбучен ред започват с по-ранна или по-късна буква. Например, ако изберете стойността "Иванов" с опцията "още", то в таблицата ще останат само тези клетки, които започват с буквата "Y" (Картов, Йогуртов и др.). И с опцията "по-малко" - стойности, започващи с буквата "Z" (Захаров, Букин).
• 3. Единствената разлика между условията "повече или равно"и "по-малко или равно"от предходната точка, тъй като филтърът включва избраната стойност.
• 4. Ако искате да намерите всички стойности, които започват с "върба", след това използвайте условието "Започни с"и ако искате да знаете колко стойности в таблицата завършват на "рович"след това изберете опцията "завършва със".
• 5. Съответно условията "не започва с"и "не завършва с"предполагат, че не е необходимо да показвате стойности, съдържащи фразата за търсене в таблицата.
• 6. При избор на условия "съдържа"или "не съдържа"можете да посочите всяка фраза или комбинация от букви, които да включите или изключите от филтъра. Разликата между този елемент и елементи 1, 4 и 5 е, че фразата за търсене може да бъде разположена навсякъде в клетката. Например, като зададете „Върба“ като филтър, резултатът ще бъде „Алексей Иванов“, „Сергей Иваровски“, „крива“ и т.н.

Числови филтри

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

• 1. Състояние "между". Когато изберете това условие, необходимите критерии веднага се задават в прозореца, който се появява, което улеснява задачата ви:

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

Филтриране по дата

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

И още малко за филтрите

Има и друг начин за филтриране на данни. Нека променим малко нашата маса:

Както виждате, оцветихме го.

Сега, например, трябва да намерим всички линии с Красоткин. Щракнете с десния бутон върху клетката с този човек и изберете „Филтър“ от появилото се меню. Новото меню има няколко нови опции. В този пример имаме нужда от артикул „Филтриране по стойност...“.

Ако изберете условието „Филтриране по цвят...“, тогава редовете с клетки от същия цвят като активната клетка (жълто запълване) ще останат в таблицата.

Ако щракнете върху „Филтриране по цвят на шрифта...“, тогава в нашата таблица ще останат само клетки с червен или черен шрифт, в зависимост от това коя клетка е активна в момента.

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

Администрация на сайта уебсайтблагодаря на автора Александър Царевза подготовка на материала.

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

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

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

Тук виждаме основните функции за филтриране, разположени в раздела Начало. Можете също така да разгледате раздела "Данни", където ще ни бъде предложена подробна версия на контрола на филтрирането. За да сортирате данните, трябва да изберете желания диапазон от клетки или, алтернативно, просто да маркирате горната клетка на желаната колона. След това трябва да кликнете върху бутона "Филтър", след което вдясно в клетката ще се появи бутон с малка стрелка, сочеща надолу.

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

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

Сега разгледайте падащото меню на всеки филтър (те ще бъдат еднакви):

- сортиране по възходящ или низходящ ("от минималната към максималната стойност" или обратно), сортиране на информация по цвят (т.нар. - custom);

— Филтриране по цвят;

- възможност за премахване на филтъра;

— параметри за филтриране, които включват числови, текстови и филтри за дата (ако такива стойности присъстват в таблицата);

- възможност за "избиране на всички" (ако премахнете отметката от това квадратче, тогава абсолютно всички колони просто ще престанат да се показват на листа);

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

Ако приложите филтър, изображението на бутона ще се промени и ще приеме формата на филтъра, който сте избрали.

Ако говорим за цифрови филтри , тук програмата предлага и доста голям брой различни опции за сортиране на наличните стойности. Това са: По-голямо от, По-голямо от или равно на, Равно на, По-малко от или равно на, По-малко от, Не е равно на, Между посочените стойности. Активираме елемента "Първи 10" и се появява прозорец

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

Ако изберете „Над (или под) средната стойност“, вашето внимание ще бъде представено на тези редове, чиито стойности ще съответстват на заявката. Изчисляването на средната аритметична стойност става автоматично въз основа на данните, събрани от колоната.

Персонализиран автоматичен филтър в Excel 2010, както споменахме, ви дава повече достъп до опциите за филтриране. С него можете да зададете условие (състои се от 2 израза или "логически функции" ИЛИ / И), според което ще се извърши изборът на данни.

Текстовите филтри са създадени изключително за работа с текстови стойности. Тук за избор се използват параметри като: „Съдържа“, „Не съдържа“, „Започва с ...“, „Завършва с ...“, както и „Равно“, „Не е равно“. Тяхната настройка е доста подобна на настройката на всеки цифров филтър.

Нека приложим едновременно филтриране по различни параметри към различни колони на нашия отчет за работата на складовете. И така, нека „Имена“ започват с „А“, но в колоната „Склад 1“ посочваме, че резултатът е повече от 25. Резултатът от такъв избор е представен по-долу

Филтрирането, когато вече не е необходимо, може да бъде отменено по един от възможните начини - използвайте комбинацията от бутони „Shift + Ctrl + L“, натиснете бутона „Филтър“ (раздел „Основни“, големият „Сортиране и филтриране“ “, която е част от „ Редактиране“). Или просто щракнете върху бутона "Филтър" в раздела "Данни".

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

Да предположим, че сега трябва да филтрираме с помощта на определено условие, което от своя страна е комбинация от условия за филтриране на няколко колони наведнъж (може да има повече от 2 от тях). В този случай може да се приложи само разширен персонализиран филтър, в който условията могат да се комбинират с помощта на логически функции И/ИЛИ.

Нека разгледаме един пример, за да разберем по-добре възможностите на филтъра. Да приемем, че имаме таблица със заявки за търсене в системите Yandex и Google. Трябва да определим коя от наличните заявки има позиция под 10 във всяка от посочените системи. Тъй като може да има много заявки, е необходимо да изпълните няколко прости стъпки.

В отделни и напълно свободни клетки копирайте имената на колоните, по които ще филтрираме данните. Както бе споменато по-горе, това ще бъде „Видимост в Yandex“ и „Видимост в Google“. Имената могат да бъдат копирани във всички съседни клетки, но ние ще спрем селекциите на B10 и C10.

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

Сега търсим раздела „Данни“, „Сортиране и филтриране“ и щракнете върху малката икона „Разширени“ и виждаме този прозорец

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

В "Изходен диапазон" трябва да посочите адреса (ако програмата не го прави сама) на целия диапазон от клетки, чиито данни искате да филтрирате. Това може да стане или чрез ръчно посочване на необходимите адреси, или просто чрез избиране на границите на необходимата таблица с мишката.

„Диапазонът от условия“, както се досещате, съдържа адреса на онези клетки, които съхраняват условията за филтриране и имената на колоните. За нас ще бъде "B10:C12".

Ако решите да се отдалечите от примера и да изберете функцията „копиране на резултата…“, тогава в 3-та колона трябва да посочите адреса на диапазона от тези клетки, където програмата трябва да изпрати данните, които са преминали през филтъра. Затова ще изберем и тази възможност и ще посочим „A27:C27“.

Потвърждаваме програмата и ако всичко е направено без грешки и посочените условия са изпълними, тогава ще видим следния резултат

Успех в работата.

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

Алгоритъм за създаване разширен филтърпросто:

• Създайте таблица, към която ще се приложи филтърът (изходна таблица);
• Създаваме табела с критерии (с условия за избор);
• Ние стартираме Разширен филтър.

Нека в диапазона А 7:S 83 има начална таблица със списък на стоки, съдържащ полета (колони) Продукт, Количествои Цена(вижте примерния файл). Таблицата не трябва да съдържа празни редове и колони, в противен случай Разширен филтър(и нормално ) няма да работи правилно.

Задача 1 (начало...)

Настройте филтър, за да изберете редове, които съдържат стойности в името на продукта началоот думата Ноктите. Това условие за избор е изпълнено от линии със стоки пирони 20 мм, Гвоздеи 10 мм, Гвоздеи 10 мми Ноктите.

НО 1 :A2 A2 посочете думата Ноктите.

Забележка: Критериална структура y разширен филтъре ясно дефинирана и съвпада със структурата на критериите за функциите BDSUMM(), COUNT() и др.

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

ВНИМАНИЕ!
Уверете се, че има поне един празен ред между таблицата със стойностите на условията за избор и оригиналната таблица (това ще улесни работата с разширен филтър).

Разширен филтър:

• повикване Разширен филтър();
• в полето оригинален диапазон А 7:S 83 );
• в полето Диапазон от условия НО1 :A2 .

Ако желаете, можете да копирате избраните редове в друга таблица, като поставите превключвателя на позиция Копирайте резултата на друго място. Но ние няма да направим това тук.

Натиснете бутона OK и филтърът ще бъде приложен - в таблицата ще останат само редовете, съдържащи името на продукта в колоната пирони 20 мм, Гвоздеи 10 мм, Гвоздеи 50 мми Ноктите. Останалите линии ще бъдат скрити.

Избраните номера на редове ще бъдат маркирани в синьо.

За да отмените филтърното действие, изберете която и да е клетка в таблицата и щракнете CTRL+SHIFT+L(заглавието ще бъде приложено и действието разширен филтърще бъде отменен) или натиснете бутона за меню ясно ().

Задача 2 (точно съвпадение)

Нека настроим филтър за избиране на редове, които имат в колоната Продукт точносъдържа думата Ноктите. Това условие за избор се изпълнява само от редове със стоки ноктии Ноктите(не се брои). Стойности пирони 20 мм, Гвоздеи 10 мм, Гвоздеи 50 ммняма да бъдат взети предвид.

Ще поставим табелата с условието за избор Ще я поставим в диапазона B1: B2 . Табелката трябва да съдържа и името на заглавието на колоната, по която ще бъде направен изборът. Като критерий в клетка B2 посочете формулата ="= нокти".

Сега всичко е готово за работа Разширен филтър:

• изберете произволна клетка от таблицата (това не е необходимо, но ще ускори попълването на параметрите на филтъра);
• повикване разширен филтър ( Данни/ Сортиране и филтриране/ Разширени);
• в полето оригинален диапазонуверете се, че диапазонът от клетки на таблицата е посочен заедно със заглавките ( А 7:S 83 );
• в полето Диапазон от условияпосочете клетките, съдържащи етикета с критерия, т.е. диапазон B1:B2 .
• Натиснете OK

Приложи Разширен филтърс толкова прости критерии няма много смисъл, защото се справят с тези задачи с лекота. Автофилтър. Нека разгледаме по-сложни задачи за филтриране.

Ако посочите не ="=Гвоздеи" като критерий, а просто Ноктите, тогава ще бъдат показани всички записи, съдържащи имена началоот думата нокти ( Пирони 80мм, Нокти2). За да покажете продуктови линии, съдържащина дума нокти, например, Нови нокти, трябва да посочите ="=*Nails" като критерий или просто * Пирони къде* е и означава всяка последователност от знаци.

Задача 3 (ИЛИ условие за една колона)

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

Критериите за избор в този случай трябва да бъдат поставени под съответното заглавие на колона ( Продукт) и трябва да са разположени един под друг в една колона (вижте фигурата по-долу). Поставете табелата с критериите в диапазона C1:C3 .

Прозорецът с Разширените опции за филтър и таблицата с филтрираните данни ще изглеждат така.

След като щракнете върху OK, ще се покажат всички записи, съдържащи се в колоната Продуктпродукти Ноктите ИЛИ Тапет.

Задача 4 (условие И)

точносъдържат в колона Продуктпродукти Ноктите, и в колоната Количествостойност >40. Критериите за подбор в този случай трябва да бъдат поставени под съответните заглавия (Продукт и Количество) и трябва да са на един и същ ред. Условията за подбор трябва да бъдат написани в специален формат: ="= нокти" и =">40" . Ще поставим табелата с условието за избор Ще я поставим в диапазона E1:F2 .

След натискане на бутона OK ще се изведат всички записи, съдържащи се в колоната Продуктпродукти Ноктитес брой >40.

СЪВЕТ:Когато променяте критериите за избор, по-добре е всеки път да създавате табела с критериите и след извикване на филтъра да променяте само връзката към тях.

Забележка: Ако трябваше да изчистите опциите за разширен филтър ( Данни/ Сортиране и филтриране/ Изчистване), тогава преди да извикате филтъра, изберете която и да е клетка в таблицата - EXCEL автоматично ще вмъкне връзка към диапазона, зает от таблицата (ако има празни редове в таблицата, няма да се вмъкне връзка към цялата таблица, а само до първия празен ред).

Задача 5 (ИЛИ условие за различни колони)

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

Нека изберем само онези редове от таблицата, които точносъдържат в колона Продуктпродукти Ноктите, ИЛИ които са в колоната Количествосъдържат стойност >40. Критериите за избор в този случай трябва да бъдат поставени под съответните заглавия (артикул и количество) и трябва да бъдат поставени на различни линии. Условията за подбор трябва да бъдат написани в специален формат: ="> 40" и ="= нокти". Ще поставим табелата с условието за избор Ще я поставим в диапазона E4:F6 .

След натискане на бутона OK ще се изведат записите, съдържащи се в колоната Продуктпродукти НоктитеИЛИ стойност >40 (за всеки продукт).

Задача 6 (Условия за избор, създадени в резултат на прилагане на формулата)

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

Има две опции за определяне на условия за избор на низ:

• директно въведете стойности за критерия (вижте задачите по-горе);
• генерира критерий въз основа на резултатите от изпълнението на формулата.

Разгледайте критериите, дадени от формулата. Формулата, посочена като критерии за избор, трябва да върне TRUE или FALSE.

Например, нека покажем редове, съдържащи продукт, който се среща само 1 път в таблицата. За да направите това, въведете в клетката H2 формула =COUNTIF(Лист1!$A$8:$A$83,A8)=1, и в H1 вместо заглавиевъведете някакъв обяснителен текст, напр. Стойности без дублиране. Приложимо Разширен филтър, определяйки диапазона от условия на клетката H1:H2 .

Обърнете внимание, че диапазонът за търсене на стойност се въвежда с помощта на , а критерият във функцията COUNTIF() се въвежда с относителна препратка. Това е необходимо, тъй като при използване разширен филтър EXCEL ще види това A8 е относителна препратка и ще се придвижи надолу в колоната Продукт един запис наведнъж и ще върне TRUE или FALSE. Ако се върне TRUE, ще се покаже съответният ред на таблицата. Ако се върне FALSE, редът няма да бъде показан след прилагане на филтъра.

Примери за други формули от примерния файл:

• Изведете редове с цени, по-високи от 3-тата най-висока цена в таблицата. =C8>ГОЛЯМ($C$8:$C$83 ;5)Този пример ясно показва сложността на функцията BIGGEST(). Ако сортирате колона ОТ (цени), получаваме: 750; 700; 700 ; 700; 620, 620, 160, ... От човешки термини "3-та най-висока цена" съответства на 620, а в разбирането на функцията GREAT() - 700 . В резултат на това няма да бъдат показани 4 реда, а само един (750);
• Регистърът на изходните низове е чувствителен =ТОЧНО("пирони", A8). Ще бъдат показани само онези редове, в които ще бъде показан продуктът. ноктивъведени с малки букви;
• Извеждане на редове с цена по-висока от средната =C8>СРЕДНО($C$8:$C$83);

ВНИМАНИЕ!
Приложение разширен филтъротменя филтъра, приложен към таблицата ( Данни/ Сортиране и филтриране/ Филтър).

Задача 7 (Условията за избор съдържат формули и обичайни критерии)

Помислете сега за друга таблица от примерен файл на лист Задача 7.

В колона Продукте дадено името на продукта, а в колоната Вид продукт- негов тип.

Задачата е да се показват продукти с цена под средната за даден вид стока. Тоест имаме 3 критерия: първият критерий определя Продукта, 2-рият - Видът му, а 3-тият критерий (под формата на формула) определя цената под средната.

Ще поставим критериите в редове 6 и 7. Въведете необходимия продукт и тип продукт. За даден тип продукт изчисляваме средната стойност и я показваме за яснота в отделна клетка F7. По принцип формулата може да се въведе директно във формулата на критерия в клетка C7.

Ще бъдат показани 2 от 4 продукта (от посочения тип продукт).

Задача 7.1. (Едни и същи ли са 2 стойности на един ред?)

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

Искате да покажете само онези редове, в които годината на издаване е същата като годината на закупуване. Това може да стане с помощта на елементарната формула =B10=C10.

Задача 8 (символ число ли е?)

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

Изисква се да се филтрират само тези редове, чиято колона Продукт съдържа Пирони 1 инч, Пирони 2 инчаи т.н. продукти Неръждаеми пирони, пирони хромирании т.н. не трябва да се филтрира.

Най-лесно това става като зададете условие като филтър след думата Nails да има число. Това може да стане с помощта на формулата =ISNUMBER(--MID(A11,LSTR($A$8)+2,1))

Формулата премахва 1 знак от името на продукта след думата Nails (включително интервалите). Ако този символ е число (цифра), тогава формулата връща TRUE и низът се извежда, в противен случай низът не се извежда. Колона F показва как работи формулата, т.е. може да се тества преди стартиране разширен филтър.

Задача 9 (Изведете редове, които НЕ СЪДЪРЖАТ дадените стоки)

Изисква се да филтрирате само тези редове, които НЕ съдържат в колоната Продукт: Пирони, дъски, лепило, тапети.

За да направите това, трябва да използвате проста формула =КРАЙ(VLOOKUP(A15,$A$8:$A$11;1;0))

Microsoft Excel е повсеместен и лесен за употреба инструмент за електронни таблици. Широката функционалност прави тази програма втората най-популярна след MS Word сред всички офис програми. Използва се от икономисти, счетоводители, учени, студенти и представители на други професии, които трябва да обработват математически данни.

Една от най-удобните функции в тази програма е филтрирането на данни. Помислете как да настроите и използвате MS excel филтри.

Къде са филтрите в Excel - техните видове

Намирането на филтри в тази програма е лесно - трябва да отворите главното меню или просто да задържите клавишите Ctrl + Shift + L.

Как да зададете филтър в Excel

Основни функции за филтриране в Excel:

• филтър по цвят: позволява ви да сортирате данните по шрифт или цвят на запълване,
• текстови филтри в excel: позволяват ви да зададете определени условия за редове, например: по-малко от, по-голямо от, равно на, не е равно на и други, както и да зададете логически условия - и, или,
• цифрови филтри: сортирайте по числови условия, като например под средното, топ 10 и други,
• ръководство: изборът може да бъде направен според критерии, избрани от вас.

Те са лесни за използване. Необходимо е да изберете таблицата и да изберете секцията с филтри в менюто, след което да изясните по какъв критерий ще бъдат проверени данните.

Как да използвате разширен филтър в Excel - как да го персонализирате

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

ВИДЕО ИНСТРУКЦИЯ

Ред на настройка:

1. Създайте таблица с данни за по-нататъшна работа с нея. Не трябва да съдържа празни редове.
2. Създайте таблица с условия за избор.
3. Стартирайте разширен филтър.

Нека да разгледаме примерна настройка.
Имаме таблица с колони Продукт, Количество и Цена.

Например, трябва да сортирате редове, чиито имена на продукти започват с думата „Гвоздеи". Няколко реда попадат под това условие.

Таблицата с условията ще бъде поставена в клетки A1:A2. Важно е да посочите името на колоната, в която ще се извършва селекцията (клетка A1) и самата дума за селекция - Пирони (клетка A2).

Най-удобно е да го поставите над данните или отстрани. Под него също не е забранено, но не винаги е удобно, тъй като от време на време може да се наложи да добавите допълнителни редове. Направете отстъп на поне един празен ред между двете таблици.

Тогава имате нужда от:

1. изберете някоя от клетките
2. отворете "Разширен филтър" по пътя: Данни - Сортиране и филтриране - Разширени,
3. проверете какво е посочено в полето "Начален диапазон" - цялата таблица с информация трябва да отиде там,
4. в "Диапазон от условия" е необходимо да зададете стойностите на клетките с условието за избор, в този пример това е диапазонът A1: A2.

След като щракнете върху бутона „OK“, необходимата информация ще бъде избрана и в таблицата ще се появят само редове с желаната дума, в нашия случай това е „Нокти“. Останалите номера на редовете ще станат сини. За да отмените посочения филтър, просто натиснете клавишите CTRL+SHIFT+L.

Също така е лесно да филтрирате по низове, съдържащи точно думата "Nails" по начин, който не е чувствителен към главни и малки букви. В диапазона B1:B2 ще поставим колона с нов критерий за избор, като не забравяме да посочим заглавието на колоната, в която ще се извършва скринингът. В клетка B2 трябва да въведете следната формула = "= Пирони".

• изберете някоя от клетките на таблицата,
• отворете "Разширен филтър",
• проверете дали цялата таблица с данни е включена в "Начален диапазон",
• в "Обхват от условия" посочете B1:B2.

След като щракнете върху „OK“, данните ще бъдат филтрирани.

Това са най-простите примери. работа с филтри в ексел. В разширената версия е удобно да зададете други условия за избор, например отпадане с параметъра „ИЛИ“, отпадане с параметъра „Гвоздеи“ и стойността в колоната „Сума“> 40.

Как да филтрирате в Excel по колони

Информацията в таблицата може да се филтрира по колони – една или повече. Разгледайте примера на таблица с колони "Град", "Месец" и "Продажби".

Ако трябва да филтрирате данните по колона с имена на градове по азбучен ред, трябва да изберете някоя от клетките в тази колона, да отворите „Сортиране“ и „Филтър“ и да изберете опцията „Y“. В резултат на това информацията ще се покаже, като се вземе предвид първата буква в името на града.

За да получите информация на обратния принцип, трябва да използвате параметъра "YA".

Необходимо е да се отсее информацията по месеци, а също така градът с голям обем продажби трябва да бъде по-висок в таблицата от града с по-малък обем продажби. За да разрешите проблема, трябва да изберете опцията "Сортиране" в "Сортиране и филтриране". В прозореца, който се показва с настройките, посочете "Сортиране по" - "Месец".

След това трябва да добавите второ ниво на сортиране. За да направите това, изберете в "Сортиране" - "Добавяне на ниво" и посочете колоната "Продажби". В колоната с настройки "Подреждане" изберете "По низходящ". След като щракнете върху „OK“, данните ще бъдат избрани според зададените параметри.

ВИДЕО ИНСТРУКЦИЯ

Защо филтрите може да не работят в Excel

При работа с инструмент като филтри потребителите често изпитват затруднения. Обикновено те са свързани с нарушаване на правилата за използване на определени настройки.

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

Решение:

1. изберете колона с дати,
2. отворете раздела Excel в главното меню,
3. изберете бутона "Клетки", в падащия списък изберете опцията "Преобразуване на текст в дата".

Често срещаните потребителски грешки при работа с тази програма трябва също да включват:

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

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

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

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

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

Как да направя разширен филтър в Excel?

Разширеният филтър ви позволява да филтрирате данни по неограничен набор от условия. С инструмента потребителят може:

1. задайте повече от два критерия за избор;
2. копирайте резултата от филтрирането на друг лист;
3. задайте условие с всякаква сложност, като използвате формули;
4. извличане на уникални стойности.

Алгоритъмът за прилагане на разширен филтър е прост:

1. Правим таблица с първоначални данни или отваряме съществуваща. Например така:
2. Създайте таблица с условия. Особености: заглавният ред напълно съвпада с "заглавието" на филтрираната таблица. За да избегнете грешки, копирайте заглавния ред в изходната таблица и го поставете на същия лист (отстрани, отгоре, отдолу) или друг лист. Въведете критериите за избор в таблицата с условия.
3. Отидете в раздела "Данни" - "Сортиране и филтриране" - "Разширени". Ако филтрираната информация трябва да се покаже на друг лист (НЕ където се намира оригиналната таблица), тогава трябва да изпълните разширения филтър от друг лист.
4. В прозореца „Разширен филтър“, който се отваря, изберете метода за обработка на информация (на същия лист или на друг), задайте първоначалния диапазон (Таблица 1, пример) и диапазона от условия (Таблица 2, условия). Заглавните редове трябва да бъдат включени в диапазоните.
5. За да затворите прозореца на разширения филтър, щракнете върху OK. Виждаме резултата.

Горната таблица е резултат от филтриране. Долната табела с условията е дадена за яснота до нея.

Как да използвам разширения филтър в Excel?

За да отмените действието на разширения филтър, поставете курсора където и да е в таблицата и натиснете клавишната комбинация Ctrl + Shift + L или "Данни" - "Сортиране и филтриране" - "Изчистване".

Използвайки инструмента "Разширен филтър", нека намерим информация за стойностите, които съдържат думата "Задаване".

Нека добавим критериите към таблицата с условия. Например тези:

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

Можете да използвате знака "=", за да търсите точна стойност. Нека добавим следните критерии към таблицата с условия:

Excel приема знака "=" като сигнал, че потребителят сега въвежда формула. За да работи програмата правилно, лентата с формули трябва да съдържа запис във формата: = "= Набор от клетки от област 6."

След използване на "Разширен филтър":

Сега нека филтрираме изходната таблица чрез условието "ИЛИ" за различни колони. Операторът "OR" също е в инструмента "AutoFilter". Но там може да се използва в рамките на една колона.

В таблицата с условия въвеждаме критериите за избор: \u003d "= Набор от клетки от регион 6." (в колоната „Име“) и ="

Моля, обърнете внимание: критериите трябва да бъдат написани под съответните заглавия на РАЗЛИЧНИ редове.

Резултат от избора:

Разширеният филтър ви позволява да използвате формули като критерий. Помислете за пример.

Избор на линия с максимален дълг: =MAX(Таблица1).

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

Как да направя няколко филтъра в Excel?

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

Приложете инструмента за разширен филтър:

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

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

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

Как да филтрирам в Excel по редове?

Не по стандартния начин. Microsoft Excel избира данни само в колони. Затова трябва да се търсят други решения.

Ето примери за разширени критерии за филтърни низове в Excel:

1. Преобразуване на таблица. Например от три реда направете списък от три колони и приложете филтриране към преобразуваната версия.
2. Използвайте формули, за да покажете точно данните в реда, от които се нуждаете. Например, направете някакъв индикатор падащ списък. И в следващата клетка въведете формулата с помощта на функцията IF. Когато конкретна стойност е избрана от падащия списък, нейният параметър се появява до нея.

За да дадем пример как работи филтърът по редове в Excel, нека създадем таблица:

За списъка с продукти създайте падащ списък:

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

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

Изтеглете примери за разширени филтри

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

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

Ако сте направили таблица в Excel чрез раздела "Вмъкване" - "Таблица" или раздела "Начало" - "Форматиране като таблица", тогава в такава таблица филтърът е активиран по подразбиране. Показва се под формата на стрелка, която се намира в горната клетка от дясната страна.

Ако просто сте попълнили клетките с данни и след това сте ги форматирали като таблица, филтърът трябва да е включен. За да направите това, изберете целия диапазон от клетки на таблица, включително заглавки на колони, тъй като бутонът за филтриране се намира в горната колона и ако изберете колона, започваща от клетка с данни, това няма да се приложи към филтрираните данни на това колона. След това отидете в раздела "Данни" и щракнете върху бутона "Филтър".

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

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

Сега нека да разгледаме как работи филтърът в Excel. Нека използваме следната таблица като пример. Има три колони: „Име на продукта“, „Категория“ и „Цена“, към тях ще приложим различни филтри.

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

Например, нека оставим само плодове в "Категория". Премахнете отметката в полето "зеленчук" и щракнете върху "OK".

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

Ако трябва да премахнете филтър за данни в Excel, щракнете върху иконата на филтъра в клетката и изберете „Премахване на филтър от (име на колона)“ от менюто.

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

Нека приложим "Цифров филтър" към колоната "Цена". Кликнете върху бутона в горната клетка и изберете съответния елемент от менюто. От падащия списък можете да изберете условието, което искате да приложите към данните в колоната. Например, нека покажем всички продукти, чиято цена е по-ниска от "25". Избираме "по-малко".

Въведете желаната стойност в съответното поле. Множество условия могат да бъдат приложени за филтриране на данни с помощта на логически И и ИЛИ. Когато използвате „И“, трябва да са изпълнени и двете условия, когато използвате „ИЛИ“, трябва да е изпълнено едно от посочените условия. Например, можете да зададете: "по-малко" - "25" - "И" - "по-голямо от" - "55". Така от таблицата ще изключим продукти, чиято цена е в диапазона от 25 до 55.

Таблица с филтър на колона "Цена" под 25.

„Текстовият филтър“ в примерната таблица може да се приложи към колоната „Име на продукта“. Кликнете върху бутона за филтър в колоната и изберете елемента със същото име от менюто. В падащия списък, който се отваря, например, използвайте "започва с".

Да оставим в таблицата продуктите, които започват с "ка". В следващия прозорец, в полето пишем: "ka*". Натиснете "OK".

„*“ в една дума замества поредица от знаци. Например, ако зададете условието "съдържа" - "s * l", думите маса, стол, сокол и така нататък ще останат. "?" ще замени всеки знак. Например "b? тон" - хляб, пъпка. Ако искате да оставите думи, състоящи се от 5 букви, напишете "?????".

Филтър за колоната Име на продукта.

Филтърът може да се регулира по цвета на текста или по цвета на клетката.

Нека направим клетка "Филтър по цвят" за колоната "Име на продукта". Щракваме върху бутона за филтриране и избираме едноименния елемент от менюто. Да изберем червено.

В таблицата останаха само червени продукти.

Филтърът за цвят на текста се прилага към колоната Категория. Оставете само плодове. Изберете червено отново.

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

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

Филтрите в Excel ще ви помогнат да работите с големи таблици. Разгледахме основните точки за това как да направите филтър и как да работите с него. Изберете необходимите условия и оставете интересуващите ви данни в таблицата.

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

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

Стъпка 1: Създайте таблица с критерии за избор

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

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

Стъпка 2: Изпълнение на разширения филтър

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

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