Kur punoni me tabela në Excel, mjaft shpesh ju duhet t'i zgjidhni ato sipas një kriteri të caktuar ose sipas disa kushteve. Programi mund ta bëjë këtë në mënyra të ndryshme duke përdorur një numër mjetesh. Le të zbulojmë se si të provojmë në Excel duke përdorur një larmi opsionesh.
Ekzekutimi i mostrave
Përzgjedhja e të dhënave konsiston në procedurën e përzgjedhjes nga grupi i përgjithshëm i atyre rezultateve që plotësojnë kushtet e dhëna, me prodhimin e tyre pasues në një fletë si një listë të veçantë ose në rangun origjinal.
Metoda 1: përdorni autofilter të avancuar
Mënyra më e lehtë për të bërë një përzgjedhje është të përdorni një automatik automatik të përparuar. Konsideroni se si ta bëni këtë me një shembull specifik.
- Zgjidhni zonën në fletë, midis të dhënave të së cilës dëshironi të bëni një përzgjedhje. Në skedë "Home" klikoni në butonin Rendit dhe filtro. Ndodhet në bllokun e cilësimeve. "Editing". Në listën që hapet pas kësaj, klikoni në butonin "Filter".
Ekziston një mundësi për të vepruar ndryshe. Për ta bërë këtë, pasi të zgjidhni zonën në fletë, lëvizni në skedë "Data". Klikoni në butonin "Filter"e cila është postuar në kasetë në grup Rendit dhe filtro.
- Pas këtij veprimi, pikturat paraqiten në kokën e tabelës për të filluar filtrimin në formën e trekëndëshave të vegjël të kthyer përmbys në buzë të djathtë të qelizave. Ne klikojmë në këtë ikonë në titullin e kolonës me të cilën duam të bëjmë një përzgjedhje. Në menunë që hapet, shkoni te artikulli "Filtrat e tekstit". Tjetra, zgjidhni pozicionin "Filtri i personalizuar ...".
- Aktivizohet dritarja e filtrimit të përdoruesit. Në të, ju mund të vendosni kufirin me të cilin do të bëhet zgjedhja. Në listën rënëse për kolonën që përmban qelizat e formatit të numrit që ne përdorim si shembull, mund të zgjidhni një nga pesë llojet e kushteve:
- e barabartë me;
- jo e barabartë;
- më shumë se;
- më shumë ose të barabartë;
- më pak.
Le të japim një shembull si kusht në atë mënyrë që të zgjedhim vetëm vlerat për të cilat shuma e të ardhurave tejkalon 10,000 rubla. Vendosni kaloni në pozicion "Më shumë". Vendosni vlerën në fushën e duhur "10000". Për të kryer një veprim, klikoni në butonin "OK".
- Siç mund ta shihni, pas filtrimit kishte vetëm linja në të cilat shuma e të ardhurave tejkalon 10,000 rubla.
- Por në të njëjtën kolonë, mund të shtojmë edhe kushtin e dytë. Për ta bërë këtë, ne përsëri kthehemi në dritaren e filtrimit të përdoruesit. Siç mund ta shihni, në pjesën e poshtme të saj ekziston një ndërprerës tjetër i gjendjes dhe fusha përkatëse e hyrjes. Le të vendosim tani kufirin e sipërm të zgjedhjes në 15,000 rubla. Për ta bërë këtë, vendoseni çelësin në pozicion "Më pak", dhe në fushën në të djathtë vendosim vlerën "15000".
Përveç kësaj, ekziston edhe një ndërprerës i kushteve. Ai ka dy pozicione "DHE" dhe "OR". Si parazgjedhje, është vendosur në pozicionin e parë. Kjo do të thotë që vetëm rreshtat që plotësojnë të dy kufizimet do të mbeten në mostër. Nëse do të vihet në pozitë "OR"atëherë do të ketë vlera që i përshtaten ndonjë prej dy kushteve. Në rastin tonë, ju duhet të vendosni kaloni te "DHE", domethënë, lini këtë cilësim si të paracaktuar. Pasi të jenë futur të gjitha vlerat, klikoni në butonin "OK".
- Tani në tabelë ka vetëm linja në të cilat shuma e të ardhurave nuk është më pak se 10,000 rubla, por nuk kalon 15,000 rubla.
- Në mënyrë të ngjashme, mund të konfiguroni filtrat në kolonat e tjera. Në të njëjtën kohë, është e mundur të ruani filtrimin sipas kushteve të mëparshme që ishin vendosur në kolonat. Pra, le të shohim se si kryhet filtrimi për qelizat në formatin e datës. Klikoni në ikonën e filtrit në kolonën përkatëse. Klikoni në mënyrë sekuenciale në artikujt e listës "Filtri sipas datës" dhe Filter me porosi.
- Dritarja e automatikut të përdoruesit fillon përsëri. Zgjedhjen e rezultateve e kryejmë në tabelë nga 4 maji deri më 6 maj 2016 përfshirëse. Në ndërprerësin e zgjedhjes së kushteve, siç shohim, ka edhe më shumë mundësi sesa për formatin e numrit. Zgjidhni një pozicion "Pas ose barabartë". Në fushën në të djathtë, vendosni vlerën "04.05.2016". Në bllokun e poshtëm, vendosni kaloni në pozicion "Në ose të barabartë me". Vendosni vlerën në fushën e duhur "06.05.2016". Ne e lëmë ndërprerjen e përputhshmërisë së kushteve në pozicionin e paracaktuar - "DHE". Në mënyrë që të aplikoni filtrimin në veprim, klikoni në butonin "OK".
- Siç mund ta shihni, lista jonë është zvogëluar më tej. Tani kanë mbetur vetëm linja në të, në të cilat shuma e të ardhurave ndryshon nga 10,000 në 15,000 rubla për periudhën nga 4 maji deri më 6 maj 2016, përfshirë.
- Ne mund ta rivendosim filtrimin në njërën prej kolonave. Ne do ta bëjmë këtë për vlerat e të ardhurave. Klikoni në ikonën e automatikut në kolonën përkatëse. Në listën rënëse, klikoni në artikull Hiq Filterin.
- Siç mund ta shihni, pas këtyre veprimeve, zgjedhja sipas sasisë së të ardhurave do të çaktivizohet, dhe vetëm zgjedhja sipas datave do të mbetet (nga 05/04/2016 deri më 05/06/2016).
- Ekziston një kolonë tjetër në këtë tabelë - "Emri". Ai përmban të dhëna në format teksti. Le të shohim se si të krijojmë një përzgjedhje duke përdorur filtrimin nga këto vlera.
Klikoni në ikonën e filtrit në emrin e kolonës. Ne kalojmë nëpër emrat e listës "Filtrat e tekstit" dhe "Filtri i personalizuar ...".
- Dritarja e automatikut të përdoruesit hapet përsëri. Le të bëjmë një përzgjedhje sipas artikujve "patate" dhe "Mishi". Në bllokun e parë, vendosni kalimin e kushteve në "Barabartë". Në fushën në të djathtë të saj futemi fjalën "patate". Switchelësi i bllokut të poshtëm është vendosur gjithashtu në pozitë "Barabartë". Në fushën përballë tij, bëni një rekord - "Mishi". Dhe atëherë ne bëjmë atë që nuk e bëmë më parë: vendosni kalimin e kushteve të përputhshmërisë "OR". Tani një vijë që përmban ndonjë prej kushteve të përcaktuara do të shfaqet në ekran. Klikoni në butonin "OK".
- Siç mund ta shihni, në kampionin e ri ekzistojnë kufizime në datën (nga 05/04/2016 deri më 05/06/2016) dhe me emrin (patate dhe mish). Nuk ka kufizime në sasinë e të ardhurave.
- Ju mund ta hiqni plotësisht filtrin në të njëjtat mënyra që keni përdorur për ta instaluar. Për më tepër, nuk ka rëndësi se cila metodë është përdorur. Për të rivendosur filtrimin, duke qenë në skedë "Data" klikoni në butonin "Filter"i cili vendoset në një grup Rendit dhe filtro.
Opsioni i dytë përfshin të shkoni në skedë "Home". Aty klikojmë në butonin në shirit Rendit dhe filtro në bllok "Editing". Në listën e aktivizuar, klikoni në butonin "Filter".
Duke përdorur njërën nga dy metodat e mësipërme, filtrimi do të fshihet, dhe rezultatet e zgjedhjes do të pastrohen. Kjo do të thotë, tabela do të tregojë të gjithë grupin e të dhënave që ka.
mësim: Funksioni i autofilterit në Excel
Metoda 2: aplikimi i një formule të vargjeve
Ju gjithashtu mund të bëni një përzgjedhje duke aplikuar një formulë komplekse të vargjeve. Për dallim nga versioni i mëparshëm, kjo metodë parashikon rezultatin e rezultatit në një tabelë të veçantë.
- Në të njëjtën fletë, krijoni një tabelë të zbrazët me të njëjtat emra kolonë në kokë si burimi.
- Zgjidhni të gjitha qelizat boshe në kolonën e parë të tabelës së re. Ne e vendosim kursorin në vijën e formulave. Vetëm këtu do të futet një formulë që prodhon një përzgjedhje sipas kritereve të përcaktuara. Ne zgjedhim linjat në të cilat shuma e të ardhurave tejkalon 15,000 rubla. Në shembullin tonë specifik, formula e hyrjes do të duket si kjo:
= INDEX (A2: A29; LOW (IF (15000 <= C2: C29; STRING: (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))Natyrisht, në secilin rast, adresa e qelizave dhe rrezet do të jenë të ndryshme. Në këtë shembull, mund ta krahasoni formulën me koordinatat në ilustrim dhe ta përshtatni atë me nevojat tuaja.
- Meqenëse kjo është një formulë e grupit, për ta zbatuar atë në veprim, duhet të shtypni jo butonin hyj, dhe shkurtore e tastierës Ctrl + Shift + Enter. Ne e bëjmë atë
- Zgjedhja e kolonës së dytë me data dhe vendosja e kursorit në shiritin e formulave, ne prezantojmë shprehjen e mëposhtme:
= INDEX (B2: B29; LOW (IF (15000 <= C2: C29; STRING: (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))Shtypni shkurtoren e tastierës Ctrl + Shift + Enter.
- Në mënyrë të ngjashme, në kolonën me të ardhura futim formulën si më poshtë:
= INDEX (C2: C29; LOW (IF (15000 <= C2: C29; STRING: (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))Përsëri, shtypni një shkurtore të tastierës Ctrl + Shift + Enter.
Në të tre rastet, vetëm vlera e parë e koordinatave ndryshon, dhe pjesa tjetër e formulës është plotësisht identike.
- Siç mund ta shihni, tabela është e mbushur me të dhëna, por pamja e saj nuk është tërësisht tërheqëse, përveç kësaj, vlerat e datës plotësohen gabimisht. Duhet të rregulloni këto të meta. Data është e pasaktë sepse formati i qelizës së kolonës përkatëse është i zakonshëm, dhe ne duhet të vendosim formatin e datës. Zgjidhni të gjithë kolonën, përfshirë qelizat me gabime dhe klikoni në zgjedhjen me butonin e djathtë të miut. Në listën që shfaqet, shkoni te "Formati i qelizës ...".
- Në dritaren e formatimit që hapet, hapni skedën "Numri". Në bllok "Format e numrave" nxjerr në pah vlerën "Data". Në pjesën e djathtë të dritares, mund të zgjidhni llojin e dëshiruar të shfaqjes së datës. Pasi të vendosen cilësimet, klikoni në butonin "OK".
- Tani data është shfaqur në mënyrë korrekte. Por, siç e shohim, e gjithë pjesa e poshtme e tabelës është e mbushur me qeliza që përmbajnë një vlerë të gabuar "# NUMRI!". Në fakt, këto janë ato qeliza për të cilat nuk kishte të dhëna të mjaftueshme nga kampioni. Do të ishte më tërheqëse nëse shfaqeshin bosh fare. Për këto qëllime do të përdorim formatimin e kushtëzuar. Zgjidhni të gjitha qelizat në tabelë përveç kokës. Duke qenë në skedë "Home" klikoni në butonin Formatimi i kushtëzuartë vendosura në bllokun e mjeteve "Styles". Në listën që shfaqet, zgjidhni "Krijoni një rregull ...".
- Në dritaren që hapet, zgjidhni llojin e rregullit "Formato vetëm qelizat që përmbajnë". Në kutinë e parë nën mbishkrimin "Formatoni vetëm qelizat për të cilat gjendja e mëposhtme është e vërtetë" zgjidhni pozicionin "Gabimet". Tjetra, klikoni në butonin "Formati ...".
- Në dritaren e formatimit që fillon, shkoni te skedari "Font" dhe në fushën përkatëse, zgjidhni të bardhë. Pas këtyre veprimeve, klikoni në butonin "OK".
- Klikoni në butonin me të njëjtin emër saktësisht pasi të ktheheni në dritare për krijimin e kushteve.
Tani kemi një mostër të gatshme për kufizimin e specifikuar në një tabelë të krijuar si duhet.
mësim: Formatimi i kushtëzuar në Excel
Metoda 3: marrja e mostrave sipas disa kushteve duke përdorur formulën
Ashtu si kur përdorni një filtër, duke përdorur formulën, ju mund të zgjidhni sipas disa kushteve. Për shembull, do të marrim të gjithë tabelën e burimit të njëjtë, dhe gjithashtu një tabelë boshe ku do të shfaqen rezultatet, me formatimin e ekzekutuar tashmë numerik dhe të kushtëzuar. Ne vendosëm kufirin e parë në kufirin e poshtëm të përzgjedhjes për të ardhura prej 15,000 rubla, dhe kushtin e dytë në kufirin e sipërm të 20,000 rubla.
- Ne i futim kushtet kufitare për zgjedhjen në një kolonë të veçantë.
- Ashtu si në metodën e mëparshme, ne zgjedhim kolonat boshe të tabelës së re një nga një dhe vendosim në to tre formula përkatëse. Në kolonën e parë, shtoni shprehjen e mëposhtme:
= INDEX (A2: A29; LOW (IF ((($ D $ 2 = C2: C29)); LINE (C2: C29); ""); LINE (C2: C29) -LINE ($ C $ 1)) - LINE ($ C $ 1))Në kolonat e mëposhtme, ne futim saktësisht të njëjtat formula, duke ndryshuar vetëm koordinatat menjëherë pas emrit të operatorit INDEX te kolonat përkatëse që na duhen, për analogji me metodën e mëparshme.
Sa herë që hyni, mos harroni të shtypni një kombinim kyç Ctrl + Shift + Enter.
- Avantazhi i kësaj metode mbi atë të mëparshmja është se nëse duam të ndryshojmë kufijtë e mostrës, atëherë nuk do të kemi nevojë të ndryshojmë formulën e vargut në vetvete, e cila është mjaft problematike në vetvete. Shtë e mjaftueshme në kolonën e kushteve në fletë për të ndryshuar numrat e kufirit në ato që i nevojiten përdoruesit. Rezultatet e zgjedhjes automatikisht do të ndryshojnë menjëherë.
Metoda 4: marrja e mostrave të rastit
Në Excel duke përdorur një formulë të veçantë RAND mund të aplikohet edhe përzgjedhja e rastësishme. Kërkohet të prodhohet në disa raste kur punoni me një sasi të madhe të të dhënave, kur është e nevojshme të paraqitet fotografia e përgjithshme pa një analizë gjithëpërfshirëse të të gjitha të dhënave në grup.
- Në të majtë të tabelës ne kalojmë një kolonë. Në qelizën e kolonës tjetër, e cila ndodhet përballë qelizës së parë me të dhënat e tabelës, ne futim formulën:
= RAND ()Ky funksion tregon një numër të rastit. Për ta aktivizuar, klikoni në butonin ENTER.
- Në mënyrë që të bëni një kolonë të tërë me numra të rastit, vendoseni kursorin në këndin e poshtëm të djathtë të qelizës që tashmë përmban formulën. Shfaqet një shënues i mbushjes. Ne e tërheqim atë poshtë me butonin e majtë të miut të shtypur paralel me tabelën e të dhënave deri në fund.
- Tani kemi një varg qelizash të mbushura me numra të rastësishëm. Por, ajo përmban një formulë RAND. Duhet të punojmë me vlera të pastra. Për ta bërë këtë, kopjoni në kolonën boshe në të djathtë. Zgjidhni një gamë qelizash me numra të rastësishëm. E vendosur në skedë "Home"klikoni në ikonën "Copy" në shirit.
- Zgjidhni një kolonë bosh dhe kliko me të djathtën, duke thirrur në menunë e kontekstit. Në grupin e mjeteve Fut opsione zgjidhni artikullin "Vlerat"përshkruar si një piktogram me numra.
- Pas kësaj, duke qenë në skedë "Home", klikoni në ikonën që ne tashmë e dimë Rendit dhe filtro. Në listën rënëse, ndaloni zgjedhjen në Renditja me porosi.
- Dritarja e cilësimeve të renditjes aktivizohet. Sigurohuni që të kontrolloni kutinë pranë parametrit "Të dhënat e mia përmbajnë tituj"nëse ka një kapelë por pa shenjë kontrolli. Në fushë Renditur nga tregoni emrin e kolonës që përmban vlerat e kopjuara të numrave të rastit. Në fushë "Rendit" lini cilësimet e paracaktuar. Në fushë "Urdhër" ju mund të zgjidhni parametrin si "Ngjitje"ashtu dhe "Zbritje". Për marrjen e mostrave të rastit, kjo nuk ka rëndësi. Pasi të bëhen cilësimet, klikoni në butonin "OK".
- Pas kësaj, të gjitha vlerat e tabelës janë rregulluar në rend ngjitje ose zbritës të numrave të rastit. Ju mund të merrni çdo numër të rreshtave të parë nga tabela (5, 10, 12, 15, etj.) Dhe ato mund të konsiderohen si rezultat i marrjes së mostrave të rastit.
mësim: Renditni dhe filtroni të dhënat në Excel
Siç mund ta shihni, përzgjedhja në spreadsheet Excel mund të bëhet ose duke përdorur autofilter ose duke aplikuar formula të veçanta. Në rastin e parë, rezultati do të shfaqet në tabelën origjinale, dhe në të dytën - në një zonë të veçantë. Shtë e mundur për të bërë një përzgjedhje, si me një kusht, ashtu edhe në disa. Ju gjithashtu mund të zgjidhni në mënyrë të rastësishme duke përdorur funksionin RAND.
