Когато работите с таблици, често има нужда да „съберете в купчина“ редица подобни данни, например всички хора с фамилия „Иванов“ или всички цени в диапазона от 500 до 1000 рубли. Естествено MS Excel предлага редица инструменти за такива операции, но на първо място кога ние говорим заза сортиране и филтриране, ние помним Филтри за данни на MS Excel.
Как да активирам филтър за данни в MS Excel?
За да зададете филтър върху колоните на таблицата, изберете заглавката на таблицата с мишката и след това в раздела "Начало".изберете и по-нататък "Филтър". Бутоните със стрелки надолу се появяват в десния ъгъл на клетките на заглавките на колоните. Кликнете върху него и ще видите много солиден списък с различни вградени филтри.
Нека се опитаме да разберем това разнообразие.
- Филтрирайте по цвят. Работи само ако някои от клетките на таблицата са маркирани с цвят (например, ако клетките с приходи са маркирани в зелено, а разходите са маркирани в червено, те могат много удобно да бъдат филтрирани по цвят).
Резултат от прилагане на филтър по цвят
MS Excel цифрови и текстови филтри
- Филтри за числа/текст (в зависимост от това дали колоната съдържа числа или текст). Най-голямата и многофункционална група филтри. Когато изберете който и да е елемент, ще се стартира инструментът „Персонализиран автоматичен филтър“, предлагащ опции за филтриране за всеки вкус и цвят. Доста лесно е да ги разберете, просто погледнете примерите по-долу:
Филтрирайте всички стойности в таблицата с цена под 160.
Филтрирайте всички стойности в таблицата с цена под 160 Ив същото време повече от 80
Филтрирайте всички стойности в таблицата, които не са равни на 180 и 200
- Прост филтър. Тук няма какво да мислите - премахнете отметките от квадратчетата до данните, които трябва да бъдат филтрирани, и щракнете върху „Ok“. В резултат на това на листа остава само необходимата информация.
Най-простият филтър в MS Excel - това, което изберете, се филтрира
Как да деактивирам филтъра в таблицата на MS Excel?
Нулирането на филтър в MS Excel е дори по-лесно от настройването му – щракнете отново върху стрелката от дясната страна на клетките на заглавката на таблицата и в появилото се контекстно менюизберете елемент „Премахване на филтъра от колоната“.
Ако искате напълно да премахнете филтъра от масата, просто повторете същите стъпки като в самото начало - в раздела "Начало".изберете Инструмент за сортиране и филтриранекъдето щракнете отново "Филтър".
Поздравления, филтрите за данни са включени Excel електронна таблицанапълно деактивиран.
Филтриране Данни от Excelпомага за бързо задаване на условия за онези редове, които трябва да бъдат показани, и скриване на останалите редове, които не отговарят на тези условия.
Филтърът е инсталиран на заглавия и подзаглавия на таблици; Основното е, че клетките, на които ще бъде инсталиран филтърът, не са празни. И се намира в менюто Работни книги на Excelв раздела „Данни“, раздел „Сортиране и филтриране“:
С щракване върху иконата „Филтър“ горните клетки на диапазона ще бъдат определени като заглавия и няма да участват във филтрирането. Заглавията ще бъдат маркирани с икона. Кликнете върху него, за да видите опциите за филтриране:
Филтрите в Excel ви позволяват да сортирате. Не забравяйте, че ако не сте избрали всички колони на таблицата, а само някои от тях и приложите сортиране, данните ще бъдат загубени.
„Филтриране по цвят“ ви позволява да изберете редове в колона, които имат конкретен шрифт или цвят на запълване. Можете да изберете само един цвят.
„Текстови филтри“ позволяват да се задават определени условия за низове, като „равно на“, „не е равно“ и други. Като изберете някой от тези елементи, ще се появи прозорец:
Тук можете да зададете следните условия:
- Условията „равни“ и „не равни“ не изискват обяснение, т.к всичко е много ясно с тях;
- „по-голямо от“, „по-малко от“, „по-голямо от или равно на“ и „по-малко от или равно на“. Как могат да се сравняват низовете един с друг? За да разберете това, припомнете си как Excel извършва сортиране. Тези. Колкото по-напред е даден ред в списъка за сортиране, толкова по-голяма е стойността му. Следните твърдения са верни (правилни): А<Б; АА>А; А<=Я; 5 яблок < апельсин.
- „започва с“, „не започва с“, „завършва с“, „не завършва с“, „съдържа“ и „не съдържа“. По принцип условията се обясняват сами по себе си и могат да приемат знак или набор от знаци като стойности. Обърнете внимание на подсказката в прозореца, разположен под всички условия (следват обяснения).
Ако е необходимо, можете да зададете 2 условия, като използвате логическо „И“ или „ИЛИ“.
Ако е избрано „И“, всички условия трябва да бъдат изпълнени. Уверете се, че условията не изключват приятеля, например "<Значение И >Смисъл", защото нищо в един и същи момент не може да бъде едновременно повече и по-малко от един и същ показател.
Когато използвате "ИЛИ", поне едно от посочените условия трябва да бъде изпълнено.
Има подсказка в самия край на прозореца на потребителския автоматичен филтър. Първата му част: „Въпросителният знак „“?“ означава произволен знак...“. Тези. когато задавате условия, когато е невъзможно точно да определите знак на определено място в ред, заменете „?“ на негово място. Примери за условия:
- Започва с “?va” (започва с произволен знак, последван от знаците “va”) ще върне резултатите: “Ivanov”, “Ivanova”, “quartz”, “matchmaker” и други низове, които отговарят на условието;
- Равно на "???????" – ще върне като резултат низ, който съдържа произволни 7 знака.
Втората част от подсказката: "Знакът ""*"" обозначава последователност от всякакви знаци." Ако е невъзможно да се определи в условието кои знаци и в какво количество трябва да има в реда, вместо това заменете „*“. Примери за условия:
- Завършва с „o*t“ (завършва със знаците „o“, последвани от произволна последователност от знаци, след което знакът „t“) ще върне резултата: „пот“, „торта“, „оборот“ и дори този - „rvnshchuooovyunisvrunct“.
- Равно на "*" – ще върне низ, който съдържа поне един знак.
В допълнение към текстовите филтри има „Числови филтри“, които основно приемат същите условия като текстовите, но имат и допълнителни, свързани само с числа:
- „Над средното“ и „Под средното“ – връща стойности, които са съответно над и под средното. Средната стойност се изчислява въз основа на всички числени стойности в списъка с колони;
- „Първите 10...“ – щракването върху този елемент извежда прозорец:
Тук можете да зададете кои елементи да се показват първи от най-големите или първи от най-малките. Също така колко елемента да се показват, ако в последното поле е избран елементът „списъчни елементи“. Ако е избран елементът „% от броя на елементите“, втората стойност определя този процент. Тези. ако в списъка има 10 стойности, ще бъде избрана най-високата (или най-ниската) стойност. Ако в списъка има 1000 стойности, тогава или първите, или последните 100.
Понякога таблиците могат да съдържат доста голямо количество данни и тези данни често се представят под формата на списък. В този случай инструменти като сортиране на списъци и филтрирането им са много полезни. Списъкът обаче трябва да бъде форматиран по определен начин, в противен случай инструментите за сортиране и филтриране няма да работят.
Обикновено списъкът се състои от записи (редове) и полета (колони). Колоните трябва да съдържат един и същи тип данни. Списъкът не трябва да съдържа празни редове или колони. Ако списъкът съдържа заглавия, те трябва да бъдат форматирани по различен начин от останалите елементи на списъка.
Сортиране на списъци
Сортирането или подреждането на списъци прави много по-лесно намирането на информация. След сортиране записите се показват в реда, определен от стойностите на колоните (по азбучен ред, възходящ/низходящ по цена и т.н.).
Направете кратък списък, с който да тренирате.
Изберете го.
Щракнете върху бутона „Сортиране и филтриране“на панела "Редактиране"ленти "Начало".
Изберете "Сортиране от А до Я". Нашият списък ще бъде сортиран по първата колона, т.е. според полето за пълно име.
Ако трябва да сортирате списъка по няколко полета, тогава елементът е предназначен за това .
Сложното сортиране включва подреждане на данни в множество полета. Можете да добавяте полета чрез бутона .
В резултат на това списъкът ще бъде сортиран според установените комплексни параметри за сортиране.
Ако трябва да сортирате поле по нестандартен начин, тогава елементът от менюто е предназначен за това „Персонализиран списък.“падащ списък "Поръчай".
Можете да местите нивата на сортиране с помощта на бутоните "Нагоре"И "надолу".
Не забравяйте за контекстното меню. От него можете също да конфигурирате сортирането на списъка. Освен това има такива интересни опции за сортиране, свързани с подчертаването на един или друг елемент от таблицата.
Основната разлика между филтър и подреждане е, че по време на филтриране записите, които не отговарят на условията за избор, временно се скриват (но не се изтриват), докато при сортиране се показват всички записи в списъка, променя се само редът им.
Има два вида филтри: обикновен филтър(нарича се още автофилтър) и разширен филтър.
За да приложите автоматичен филтър, щракнете върху същия бутон като при сортиране - „Сортиране и филтриране“и изберете "Филтър"(разбира се, диапазон от клетки трябва да бъде избран преди това).
В колоните на списъка ще се появят бутони със стрелки, като щракнете върху които можете да конфигурирате параметрите на филтъра.
Полетата, които имат включен филтър, се показват с икона на фуния. Ако преместите показалеца на мишката върху такава фуния, ще се покаже условието за филтриране.
За да създадете по-сложни условия за избор, параграфът е предназначен „Текстови филтри“или "Числени филтри". В прозореца „Персонализиран автоматичен филтър“трябва да конфигурирате крайните условия за филтриране.
Когато използвате разширен филтър, критериите за избор са посочени в работния лист.
За да направите това, трябва да направите следното.
Копирайте и поставете заглавката на списъка в свободното пространство.
В съответното поле(я) посочете критерии за филтриране.
Филтрирането със сигурност е един от най-удобните и бързи начини да изберете от огромен списък от данни точно това, което е необходимо в момента. В резултат на процеса на филтриране потребителят ще получи малък списък с необходимите данни, с които може лесно и спокойно да се работи. Тези данни ще бъдат избрани според определен критерий, който можете да конфигурирате сами. Естествено, можете да работите с избраните данни, като използвате пълноценно всички други функции на Excel 2010.
Когато работите с таблици, данните могат да бъдат избрани по два начина - да използвате персонализиран автоматичен филтър в Excel или да се фокусирате върху малък набор от основни функции и формули. Вторият вариант е много по-прост и лесен, което означава, че именно с него ще започнем запознаването си с възможностите на актуализирания табличен процесор.
И така, имате таблица с масив от различни данни, които сте получили на работа, и сега трябва да изберете някои конкретни стойности. За щастие можете да използвате автоматичен филтър в Excel, който ще остави на екрана само необходимата информация.
Тук виждаме основните функции за филтриране, разположени в раздела Начало. Можете също така да разгледате раздела „Данни“, където ще ни бъде предложена опция за подробен контрол на филтрирането. За да организирате данните, трябва да изберете необходимия диапазон от клетки или, алтернативно, просто да маркирате горната клетка на необходимата колона. След това трябва да щракнете върху бутона „Филтър“, след което от дясната страна на клетката ще се появи бутон с малка стрелка, сочеща надолу.
Филтрите могат лесно да бъдат "прикрепени" към всички колони.
Това значително ще опрости сортирането на информация за бъдеща обработка.
Сега нека да разгледаме падащото меню за всеки филтър (те ще бъдат еднакви):
— сортиране във възходящ или низходящ ред („от минимална към максимална стойност“ или обратно), сортиране на информация по цвят (т.нар. потребителски);
— Филтриране по цвят;
— възможност за премахване на филтъра;
— параметри за филтриране, които включват числови, текстови и филтри за дата (ако такива стойности присъстват в таблицата);
— възможност за „избиране на всички“ (ако махнете отметката от това квадратче, тогава всички колони просто вече няма да се показват на листа);
— „имена на колони“, където се виждат всички колони, използвани в таблицата. Ако премахнете отметката от квадратчето до определени елементи, те също няма да се показват повече, но тук това може да се направи избирателно.
Ако приложите филтър, изображението на бутона ще се промени и ще приеме формата на филтъра, който сте избрали.
Ако говорим за числени филтри , то тук програмата предлага и доста голям брой различни опции за сортиране на наличните стойности. Това са: „По-голямо от“, „По-голямо или равно на“, „Равно на“, „По-малко или равно на“, „По-малко от“, „Не е равно на“, „Между зададени стойности“. Активираме елемента „Първите 10“ и се появява прозорец
Тук можете да изберете колко първи стойности ни интересуват (само число, което можете да изберете с помощта на стрелките или да посочите сами) и как трябва да бъдат сортирани (най-малка, най-голяма или дори процент от общия брой от елементи).
Ако изберете елемента „Над (или под) средното“, тогава ще ви бъдат представени онези редове, чиито стойности ще съответстват на заявката. Средната аритметична стойност се изчислява автоматично, въз основа на данните, събрани от колоната.
Персонализиран автоматичен филтър в Excel 2010, както беше казано, дава разширен достъп до опциите за филтриране. С негова помощ можете да зададете условие (състои се от 2 израза или „логически функции“ ИЛИ / И), според които ще бъдат избрани данните.
Текстовите филтри са проектирани да работят изключително с текстови стойности. Тук за избор се използват следните параметри: „Съдържа“, „Не съдържа“, „Започва с...“, „Завършва с...“, както и „Равно“, „Не е равно“. Настройването им е доста подобно на настройването на който и да е цифров филтър.
Нека приложим едновременно филтриране по различни параметри към различни колони на нашия отчет относно производителността на склада. И така, нека „Артикулите“ започват с „А“, но в колоната „Склад 1“ посочваме, че резултатът трябва да бъде по-голям от 25. Резултатът от такъв избор е представен по-долу
Филтрирането, когато вече не е необходимо, може да бъде отменено по един от възможните начини - използвайте комбинацията от бутони „Shift+Ctrl+L“, натиснете бутона „Филтър“ (раздел „Основни“, големият „Сортиране и филтриране“ “, включена в „ Редактиране“). Или просто щракнете върху бутона „Филтър“ в раздела „Данни“.
Разбира се, филтрирането е доста удобен елемент за избор на данни в Excel 2010, но понякога може да има нужда да се направи по-подробен избор на информация, която просто не е предвидена от стандартните функции.
Да кажем сега, че трябва да филтрираме с помощта на определено условие, което от своя страна е комбинация от условия за филтриране на няколко колони наведнъж (може да има повече от 2). В този случай може да се използва само разширен персонализиран филтър, в който условията могат да се комбинират с помощта на логически функции И/ИЛИ.
Нека да разгледаме пример, за да разберем по-добре възможностите на филтъра. Да приемем, че имаме таблица със заявки за търсене в системите Yandex и Google. Трябва да определим коя от съществуващите заявки има позиция под 10 във всяка от посочените системи. Тъй като може да има много заявки, трябва да изпълните няколко прости стъпки.
В отделни и напълно свободни клетки копираме имената на колоните, по които ще филтрираме данните. Както бе споменато по-горе, това ще бъде „Видимост в Yandex“ и „Видимост в Google“. Имената могат да бъдат копирани във всички съседни клетки, но ние ще изберем B10 и C10.
Именно под тези клетки ще въведем условията за предстоящото филтриране. Ако е необходимо (и това е необходимо за нас) да вземем предвид и двете условия наведнъж (тази логическа функция се нарича И), тогава условията за филтриране трябва да бъдат поставени в един ред. Ако трябва да се вземе предвид само едно нещо (функцията ИЛИ), тогава условията трябва да бъдат поставени на различни редове.
Сега търсим раздела „Данни“, „Сортиране и филтриране“ и щракваме върху малката икона „Разширени“ и виждаме този прозорец
„Разширен филтър“ ви позволява да изберете една от възможните опции - филтрирайте списъка точно тук или вземете получените данни и ги копирайте на друго удобно за вас място.
В „Изходен диапазон“ трябва да посочите адреса (ако програмата не прави това самостоятелно) на целия диапазон от клетки, чиито данни трябва да бъдат филтрирани. Това може да стане или чрез ръчно посочване на необходимите адреси, или просто чрез избиране на границите на необходимата таблица с мишката.
„Диапазонът от условия“, както се досещате, съдържа адресите на тези клетки, в които се съхраняват условията за филтриране и имената на колоните. За нас ще бъде „B10:C12“.
Ако решите да се отдалечите от примера и да изберете функцията „копиране на резултат ...“, тогава в 3-та колона трябва да посочите адреса на диапазона от тези клетки, където програмата трябва да изпрати данните, които са преминали филтър. Така че ние също ще изберем тази опция и ще посочим "A27:C27".
Потвърждаваме програмата и ако всичко е направено без грешки и посочените условия са изпълнени, тогава ще видим този резултат
Успех в работата.
Понякога, когато работите с голяма база данни, става необходимо да изберете информация според определен критерий (например, покажете само онези хора, които са родени през 1980 г.). За да изпълнявате задачи от този вид, използвайте филтър в Excel.
Видео за филтриране и сортиране на данни в Excel
Видове филтри в Excel
Филтрирането е избор на необходимите данни от списък за последваща работа с тях.Резултатът от тази процедура ще бъдат определени линии, които отговарят на критериите за подбор. Останалите записи са временно скрити и не се използват, докато потребителят не деактивира филтъра. Можете да извършвате стандартни действия с избраните данни: редактиране, форматиране, отпечатване, създаване на графики, диаграми и др.
В Excel има 2 метода за филтриране: AutoFilter и Advanced Filter.Можете да ги стартирате през лентата с менюта, като щракнете върху „Данни - Филтър“. Използвайки първата опция, вие бързо избирате необходимата информация с прости критерии за търсене. В режим на автоматичен филтър заглавният ред на таблицата във всяка колона ще съдържа бутон със стрелка, като щракнете върху който можете да посочите критериите за избор. За всяка колона можете да зададете свои собствени настройки. В този режим можете да зададете следните параметри:
За да премахнете филтър от една колона, трябва да кликнете върху елемента „Всички“ в списъка с елементи. Ако трябва да го отмените за цялата таблица, трябва да изберете „Данни - Филтър - Показване на всички“ в лентата с менюта. Премахването на автофилтър се извършва по същия начин като стартирането му.
Разширен филтър
Пример за създаване на разширен филтър - трябва да посочите диапазона от изходни данни, филтри и диапазона, където се поставят филтрираните данни
Втората опция за филтриране дава на потребителя повече опции за избор на необходимите данни. За да стартирате разширената опция, трябва да създадете копие на заглавките на таблицата (т.е. просто копирайте заглавката). Това ще бъде диапазонът от условия. След това трябва да попълните този диапазон с критерии за избор. Но тук е важно да се придържате към правилата: ако имате нужда от стойности, които да бъдат избрани според два параметъра (например фамилното име и оценката на ученика), тогава условията са написани в един ред; ако критериите са избрани в режим „ИЛИ“ (марка на автомобила или размер на двигателя), тогава те се записват в различни редове.
Да кажем, че има таблица с 2 колони - име на продукта и количество. Продуктите са общо 3 - банани, портокали, мандарини, като количеството е съответно 10, 20 и 15 броя. След като заглавката е копирана, можете да създадете условие, например да показвате продукти, чието количество е по-малко или равно на 15. Тоест под копираната заглавка в колоната „Количество“ трябва да напишете<=15. Затем надо запустить расширенный фильтр, указать исходный диапазон (исходная таблица), диапазон условий (таблица, где указано «кол-во <=15») и нажать «ОК». Исходная таблица изменится: теперь тут будут отображены только бананы (10 штук) и мандарины (15 штук).
Следователно принципът на филтриране трябва да е ясен. По този начин можете да изберете всякакви елементи от базата данни, независимо колко голяма е тя. Тази процедура ще помогне за опростяване на работата на потребителя с големи количества данни.
