Фаќање на податоци во Microsoft Excel

Кога работите со табели во Excel, доста често мора да ги изберете според одреден критериум или според неколку услови. Програмата може да го направи ова на различни начини користејќи голем број алатки. Ајде да откриеме како да се тестираат во Excel со користење на различни опции.

Земање мостри

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

Метод 1: користете напреден автофилтер

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

1. Изберете ја областа на листот, меѓу податоците за кои сакате да направите избор. Во табулаторот „Дома“ кликнете на копчето Сортирајте и филтрирајте. Се наоѓа во блокот за поставки. „Уредување“. Во списокот што се отвора после ова, кликнете на копчето "Филтер".

   

   Постои можност да се дејствува поинаку. За да го направите ова, откако ќе ја изберете областа на листот, преместете се на јазичето „Податоци“. Кликнете на копчето "Филтер"која е објавена на снимката во групата Сортирајте и филтрирајте.



2. По оваа акција, пиктограмите се појавуваат во заглавието на табелата за да започнат со филтрирање во форма на мали триаголници свртени наопаку на десниот раб на клетките. Ние кликнуваме на оваа икона во заглавието на колоната со која сакаме да направиме избор. Во менито што се отвора, одете до објектот "Филтри за текст". Следно, изберете ја позицијата "Прилагодено филтер ...".



3. Активиран е прозорецот за филтрирање на корисникот. Во него, можете да го поставите лимитот со кој ќе се изврши изборот. Во паѓачката листа за колоната што ги содржи клетките со формат на броеви што ги користиме како пример, можете да изберете еден од петте видови на состојби:
   • еднаква на;
   • не еднаков;
   • повеќе;
   • повеќе или еднакви;
   • помалку

   Да дадеме пример како состојба на таков начин што ќе ги избереме само вредностите за кои износот на приходот надминува 10.000 рубли. Поставете го прекинувачот во позиција Повеќе. Внесете ја вредноста во вистинското поле "10000". За да извршите акција, кликнете на копчето „ОК“.



4. Како што можете да видите, по филтрирањето имало само линии во кои износот на приходот надминува 10.000 рубли.



5. Но, во истата колона, можеме да го додадеме и вториот услов. За да го направите ова, повторно се враќаме во прозорецот за филтрирање на корисникот. Како што можете да видите, во неговиот долен дел има друг прекинувач за состојба и соодветно поле за влез. Дозволете ни сега да ја поставиме горната граница на селекција на 15.000 рубли. За да го направите ова, ставете го прекинувачот во позиција Помалку, а во полето десно, ја внесуваме вредноста "15000".

   Покрај тоа, постои и прекинувач за услови. Тој има две позиции "И" и „ИЛИ“. Стандардно, таа е поставена на првата позиција. Ова значи дека само редови што ги исполнуваат обете ограничувања ќе останат во примерокот. Ако тоа ќе биде ставено во позиција „ИЛИ“тогаш ќе има вредности што одговараат на кој било од двата услови. Во нашиот случај, треба да го поставите прекинувачот "И", односно, оставете ја оваа поставка како стандардна. Откако ќе ги внесете сите вредности, кликнете на копчето „ОК“.



6. Сега во табелата има само линии во кои износот на приходот не е помал од 10.000 рубли, но не надминува 15.000 рубли.



7. Слично на тоа, можете да конфигурирате филтри во други колони. Во исто време, можно е да се зачува филтрирање според претходните услови што беа поставени во колоните. Значи, ајде да видиме како се врши филтрирање за клетките во формат на датум. Кликнете на иконата за филтрирање во соодветната колона. Сексуално кликнете на ставките од списокот "Филтер по датум" и Прилагодено филтер.



8. Прозорецот за автоматско филтрирање на корисникот започнува повторно. Изборот на резултатите го извршуваме на табелата од 4 мај до 6 мај 2016 година, вклучително. Во прекинувачот за избор на состојба, како што гледаме, има дури и повеќе опции отколку за формат на број. Изберете позиција "После или еднакво". Во полето десно, поставете ја вредноста "04.05.2016". Во долниот блок, поставете го прекинувачот во позиција "До или еднакво на". Внесете ја вредноста во вистинското поле "06.05.2016". Го оставаме прекинувачот за компатибилност на состојба во стандардната позиција - "И". За да примените филтрирање во акција, кликнете на копчето „ОК“.



9. Како што можете да видите, нашиот список е дополнително намален. Сега во него остануваат само редови, во кои количината на приход варира од 10.000 до 15.000 рубли за периодот од 4 до 6 мај 2016 година.



10. Може да го поставиме филтрирањето во една од колоните. Ова ќе го сториме за вредностите на приходот. Кликнете на иконата за автоматски филтер во соодветната колона. Во паѓачката листа, кликнете на ставката Отстранете го филтерот.



11. Како што можете да видите, по овие активности, изборот според висината на приходот ќе биде оневозможен, а ќе остане само изборот по датуми (од 05.04.2016 до 05/06/2016).



12. Во оваа табела има уште една колона - "Име". Содржи податоци во формат на текст. Ајде да видиме како да се создаде избор со користење на филтрирање според овие вредности.

    Кликнете на иконата за филтрирање во името на колоната. Ние ги поминуваме имињата на списокот "Филтри за текст" и "Прилагодено филтер ...".



13. Прозорецот за автоматско филтрирање на корисникот се отвора повторно. Ајде да направиме избор по предмети „Компир“ и Месо. Во првиот блок, поставете го прекинувачот за состојба на „Еднаква“. Во полето десно од него го внесуваме зборот „Компир“. Долниот блок прекинувач е исто така ставен во позиција „Еднаква“. На полето спротивно, направете записник - Месо. И тогаш го правиме она што не го сторивме порано: постави го премиерот на компатибилност со услови „ИЛИ“. Сега на екранот ќе се појави линија што содржи која било од наведените услови. Кликнете на копчето „ОК“.



14. Како што можете да видите, во новиот примерок има ограничувања на датумот (од 05.04.2016 до 05.05.2016 година) и по име (компири и месо). Нема ограничувања за висината на приходите.



15. Можете целосно да го отстраните филтерот на истите начини на кои сте го инсталирале. Покрај тоа, не е важно кој метод се користеше. За да го поставите филтрирањето, да се наоѓате во табот „Податоци“ кликнете на копчето "Филтер"кој се става во група Сортирајте и филтрирајте.

    

    Втората опција вклучува одење на јазичето „Дома“. Таму кликнуваме на копчето на лентата Сортирајте и филтрирајте во блок „Уредување“. Во активираната листа, кликнете на копчето "Филтер".

Користејќи еден од двата горенаведени методи, филтрирањето ќе биде избришано, а резултатите од изборот ќе бидат расчистени. Тоа е, на табелата ќе се прикаже целата низа на податоци што ги има.

Лекција: Функција за автофилтер во Excel

Метод 2: примена на формула за низа

Можете исто така да направите избор со примена на сложена формула за низа. За разлика од претходната верзија, овој метод овозможува излез на резултатот во посебна табела.

1. На истиот лист, создадете празна маса со исти имиња на колони во заглавието како изворот.



2. Изберете ги сите празни ќелии во првата колона од новата табела. Ние го поставуваме курсорот во редот на формулите. Само тука ќе биде внесена формула што произведува селекција според наведените критериуми. Ние ги избираме линиите во кои износот на приходот надминува 15.000 рубли. Во нашиот специфичен пример, формулата за внесување ќе изгледа вака:

   = INDEX (A2: A29; LOW (IF (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))

   Секако, во секој случај, адресата на клетките и опсезите ќе биде различна. Во овој пример, можете да ја споредите формулата со координатите на илустрацијата и да ја прилагодите на вашите потреби.



3. Бидејќи ова е низа формула, за да ја примените во акција, треба да го притиснете не копчето Внесетеи кратенка на тастатурата Ctrl + Shift + Enter. Ние го правиме тоа



4. Избирајќи ја втората колона со датуми и го ставаме курсорот во лентата со формули, го воведуваме следниот израз:

   = INDEX (B2: B29; LOW (IF (15000 <= C2: C29; STRING: C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))

   Притиснете кратенка на тастатурата Ctrl + Shift + Enter.



5. Слично на тоа, во колоната со приходи ја внесуваме формулата како што следи:

   = INDEX (C2: C29; LOW (IF (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))

   Повторно, внесување кратенка на тастатура Ctrl + Shift + Enter.

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



6. Како што можете да видите, табелата е исполнета со податоци, но нејзиниот изглед не е целосно привлечен, покрај тоа, вредностите на датумот се пополнуваат неправилно. Треба да ги поправите овие недостатоци. Датумот не е точен затоа што мобилниот формат на соодветната колона е вообичаен и треба да го поставиме форматот на датумот. Изберете ја целата колона, вклучувајќи клетки со грешки и кликнете на изборот со десното копче на глувчето. Во списокот што се појавува, одете до "Формат на ќелија ...".



7. Во прозорецот за форматирање што се отвора, отворете го јазичето „Број“. Во блок "Формати на броеви" означете ја вредноста Датум. Во десниот дел од прозорецот, можете да го изберете посакуваниот тип на приказ на датумот. Откако ќе ги поставите поставките, кликнете на копчето „ОК“.



8. Сега датумот е прикажан правилно. Но, како што гледаме, целиот долен дел од табелата е исполнет со ќелии кои содржат погрешна вредност "# БРОЈ!". Всушност, ова се оние клетки за кои немаше доволно податоци од примерокот. Би било попривлечно ако воопшто беа прикажани празни. За овие цели ќе користиме форматирање на условно. Изберете ги сите ќелии во табелата, освен заглавието. Да се ​​биде во табулаторот „Дома“ кликнете на копчето Условно форматирањесместена во блокот со алатки Стилови. Во списокот што се појавува, изберете "Креирај правило ...".



9. Во прозорецот што се отвора, изберете го типот на правило "Форматирајте само клетки што содржат". Во првото поле под натписот "Форматирајте само клетки за кои е точно следната состојба" изберете позиција "Грешки". Следно, кликнете на копчето "Формат ...".



10. Во прозорецот за форматирање што започнува, одете до јазичето Фонт и во соодветното поле, изберете бело. После овие активности, кликнете на копчето „ОК“.



11. Кликнете на копчето со точно исто име откако ќе се вратите на прозорецот за создавање услови.

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

Лекција: Условно форматирање во Excel

Метод 3: земање мостри според неколку услови со употреба на формулата

Исто како кога користите филтер, користејќи ја формулата, можете да изберете според неколку услови. На пример, ќе ја земеме целата иста изворна табела, а исто така и празна табела каде ќе се прикажат резултатите, со веќе извршено нумеричко и условно форматирање. Ние ја поставивме првата граница до долната граница на селекција за приход од 15.000 рубли, а втората состојба до горната граница од 20.000 рубли.

1. Ние ги внесуваме граничните услови за избор во посебна колона.



2. Како и во претходниот метод, ние ги избираме празните колони од новата табела еден по еден и ги внесуваме соодветните три формули во нив. Во првата колона, додадете го следниот израз:

   = INDEX (A2: A29; LOW (IF ((($ D $ 2 = C2: C29)); LINE (C2: C29); ""); LINE (C2: C29) -LINE ($ C $ 1)) - LINE ($ C $ 1))

   Во следните колони, внесуваме точно исти формули, менувајќи ги само координатите веднаш по името на операторот Индекс на соодветните колони што ни се потребни, по аналогија со претходниот метод.

   Секој пат откако ќе влезете, не заборавајте да напишете комбинација на клучеви Ctrl + Shift + Enter.



3. Предноста на овој метод во однос на претходната е дека ако сакаме да ги промениме границите на примерокот, тогаш нема да треба да ја промениме формулата на самата низа, што е доста проблематично само по себе. Доволно е во колоната на услови на листот да се сменат граничните броеви на оние што му се потребни на корисникот. Резултатите од селекцијата автоматски ќе се променат веднаш.

Метод 4: случаен избор

Во Excel користејќи специјална формула СРЕAPНО може да се примени и случаен избор. Потребно е да се произведе во некои случаи кога работите со голема количина на податоци, кога е неопходно да се претстави општата слика без сеопфатна анализа на сите податоци во низата.

1. Лево од табелата прескокнуваме една колона. Во ќелијата на следната колона, која се наоѓа спроти првата ќелија со табелата, ја внесуваме формулата:

   = РАНД ()

   Оваа функција прикажува случаен број. За да го активирате, кликнете на копчето Внесете.



2. За да направите цела колона од случаен број, поставете го курсорот во долниот десен агол на ќелијата што веќе ја содржи формулата. Се појавува маркер за пополнување. Ние го влечеме надолу со левото копче на глувчето притиснето паралелно со табелата со податоци до крај.



3. Сега имаме голем број на клетки исполнети со случаен број. Но, содржи формула СРЕAPНО. Треба да работиме со чисти вредности. За да го направите ова, копирајте ја во празната колона десно. Изберете опсег на ќелии со случаен број. Сместено во табулаторот „Дома“кликнете на иконата Копирај на снимката.



4. Изберете празна колона и кликнете со десното копче со десен клик, повикувајќи се на контекстното мени. Во групата алатки Вметнете опции изберете ставка „Вредности“прикажан како пиктограм со броеви.



5. После тоа, да се биде во табот „Дома“кликнете на иконата што веќе ја знаеме Сортирајте и филтрирајте. Во паѓачката листа, запрете го изборот на Прилагодено сортирање.



6. Прозорецот за поставки за сортирање се активира. Бидете сигурни дека проверете го полето до параметарот "Моите податоци содржат заглавија"ако има капа, но обележувач. На полето Подредете според означете го името на колоната што ги содржи копираните вредности на случаен број. На полето „Подреди“ оставете ги стандардните поставки. На полето "Нарачај" можете да го изберете параметарот како "Растечки"така и "Опаѓање". За случајно земање примероци, ова не е важно. Откако ќе ги направите поставките, кликнете на копчето „ОК“.



7. После тоа, сите вредности на табелата се распоредени по растечки или опаѓачки редослед на случаен броеви. Можете да земете кој било број на првите редови од табелата (5, 10, 12, 15, итн.) И истите може да се сметаат како резултат на случајно земање мостри.

Лекција: Подредете ги и филтрирајте ги податоците во Excel

Како што можете да видите, изборот во табелата со табели во Excel може да се изврши или со употреба на автоматскиот филтер или со примена на специјални формули. Во првиот случај, резултатот ќе биде прикажан во оригиналната табела, а во вториот - во посебна област. Можно е да се направи селекција, како под еден услов, така и на неколку. Исто така, можете случајно да изберете со помош на функцијата СРЕAPНО.