Shpesh, përdoruesit e Excel përballen me detyrën për të krahasuar dy tabela ose lista për të identifikuar dallimet ose elementët që mungojnë në to. Do përdorues përballon këtë detyrë në mënyrën e vet, por më së shpeshti harxhohet një kohë mjaft e madhe për zgjidhjen e kësaj çështje, pasi jo të gjitha qasjet ndaj këtij problemi janë të arsyeshme. Në të njëjtën kohë, ekzistojnë disa algoritme të veprimit të provuar që do t'ju lejojnë të krahasoni listat ose grupet e tabelave në një kohë mjaft të shkurtër me përpjekje minimale. Le të shohim më nga afër këto mundësi.
Shih gjithashtu: Krahasimi i dy dokumenteve në MS Word
Metodat e krahasimit
Ka mjaft mënyra për të krahasuar hapësirat e tryezave në Excel, por ato mund të ndahen në tre grupe të mëdha:
Bazuar në këtë klasifikim, para së gjithash, janë zgjedhur metodat e krahasimit, si dhe veprimet specifike dhe algoritmet përcaktohen për detyrën. Për shembull, kur krahasoni në libra të ndryshëm, duhet të hapni dy skedarë Excel në të njëjtën kohë.
Për më tepër, duhet thënë se krahasimi i zonave të tabelave ka kuptim vetëm kur ato kanë një strukturë të ngjashme.
Metoda 1: formula e thjeshtë
Mënyra më e lehtë për të krahasuar të dhënat në dy tabela është të përdorni një formulë të thjeshtë të barazisë. Nëse të dhënat përputhen, atëherë ai jep treguesin TRUE, dhe nëse jo, atëherë FALSE. Ju mund të krahasoni të dhënat numerike dhe ato të tekstit. Disavantazhi i kësaj metode është se ai mund të përdoret vetëm nëse të dhënat në tabelë janë porositur ose renditur në të njëjtën mënyrë, sinkronizohen dhe kanë të njëjtin numër linjash. Le të shohim se si ta përdorim këtë metodë në praktikë me shembullin e dy tabelave të vendosura në një fletë.
Pra, kemi dy tabela të thjeshta me listat e punonjësve dhe pagat e tyre. Shtë e nevojshme të krahasohen listat e punonjësve dhe të identifikohen mospërputhjet midis kolonave në të cilat vendosen emrat.
- Për ta bërë këtë, ne kemi nevojë për një kolonë shtesë në fletë. Ne hyjmë një shenjë atje "=". Pastaj klikojmë në artikullin e parë që dëshironi të krahasoni në listën e parë. Ne vendosëm përsëri simbolin "=" nga tastiera. Tjetra, klikoni në qelizën e parë të kolonës që po krahasojmë në tabelën e dytë. Rezultati është një shprehje e llojit të mëposhtëm:
= A2 = D2
Edhe pse, natyrisht, në secilin rast, koordinatat do të jenë të ndryshme, por thelbi do të mbetet i njëjtë.
- Klikoni në butonin hyjpër të marrë rezultate të krahasimit. Siç mund ta shihni, kur krahasoni qelizat e para të të dy listave, programi tregoi një tregues "TRUE", që nënkupton përputhjen e të dhënave.
- Tani duhet të bëjmë një operacion të ngjashëm me qelizat e tjera të të dy tabelave në kolonat që po krahasojmë. Por thjesht mund të kopjoni formulën, e cila do të kursejë ndjeshëm kohën. Ky faktor është veçanërisht i rëndësishëm kur krahasoni listat me një numër të madh linjash.
Procedura e kopjimit kryhet më lehtë duke përdorur shënuesin e mbushjes. Ne rri pezull mbi qoshen e poshtme të djathtë të qelizës, ku morëm treguesin "TRUE". Në të njëjtën kohë, ajo duhet të shndërrohet në një kryq të zi. Ky është shënuesi i mbushjes. Ne shtypim butonin e majtë të miut dhe tërheqim kursorin poshtë në numrin e linjave në grupet e tabelave të krahasuara.
- Siç mund ta shihni, tani në një kolonë shtesë shfaqen të gjitha rezultatet e krahasimit të të dhënave në dy kolona të vargjeve të tabelave. Në rastin tonë, të dhënat në vetëm një rresht nuk përputhen. Kur i krahason ato, formula dha rezultatin "FALSE". Për të gjitha linjat e tjera, siç shohim, formula e krahasimit prodhoi një tregues "TRUE".
- Përveç kësaj, është e mundur të llogaritet numri i mospërputhjeve duke përdorur një formulë të veçantë. Për ta bërë këtë, zgjidhni elementin e fletës ku do të shfaqet. Pastaj klikoni në ikonën "Vendos funksionin".
- Në dritare Magjistarët e funksionit në një grup operatorësh "Matematike" zgjidhni emrin SUMPRODUCT. Klikoni në butonin "OK".
- Dritarja e argumentit të funksionit aktivizohet. SUMPRODUCTdetyra kryesore e të cilit është të llogarisë shumën e produkteve të gamës së zgjedhur. Por ky funksion mund të përdoret për qëllimet tona. Sintaksa është goxha e thjeshtë:
= PUMRMBLEDHJE (array1; array2; ...)Në total, adresat deri në 255 varg mund të përdoren si argumente. Por në rastin tonë, ne do të përdorim vetëm dy vargje, përveç kësaj, si një argument.
Vendoseni kursorin në fushë "Array1" dhe zgjidhni në fletë gamën e krahasuar të të dhënave në zonën e parë. Pas kësaj, vendosni një shenjë në fushë jo e barabartë () dhe zgjidhni gamën e krahasuar të rajonit të dytë. Tjetra, mbështillni shprehjen që rezulton në kllapa para së cilës vendosim dy karaktere "-". Në rastin tonë, kjo shprehje doli:
- (A2: A7D2: D7)Klikoni në butonin "OK".
- Operatori llogarit dhe tregon rezultatin. Siç mund ta shihni, në rastin tonë, rezultati është i barabartë me numrin "1", domethënë do të thotë që një mospërputhje u gjet në listat e krahasuara. Nëse listat ishin plotësisht identike, atëherë rezultati do të ishte i barabartë me numrin "0".
Në të njëjtën mënyrë, ju mund të krahasoni të dhënat në tabela që ndodhen në fletë të ndryshme. Por në këtë rast, është e dëshirueshme që linjat në to të numërohen. Përndryshe, procedura e krahasimit është pothuajse saktësisht e njëjtë siç përshkruhet më lart, përveç faktit që kur futni formulën duhet të kaloni midis fletëve. Në rastin tonë, shprehja do të duket si kjo:
= B2 = Fleta2! B2
Kjo do të thotë, siç shohim, përpara koordinatave të të dhënave, të cilat gjenden në fletë të tjera, përveç atyre ku shfaqet rezultati i krahasimit, tregohet numri i fletës dhe një shenjë thirrjeje.
Metoda 2: zgjidhni grupet e qelizave
Krahasimi mund të bëhet duke përdorur mjetin e zgjedhjes së grupit qelizor. Mund të përdoret gjithashtu për të krahasuar vetëm listat e sinkronizuara dhe të porositura. Përveç kësaj, në këtë rast, listat duhet të vendosen pranë njëri-tjetrit në të njëjtën fletë.
- Ne zgjedhim vargjeve të krahasuara. Shko te skeda "Home". Tjetra, klikoni në ikonën Gjeni dhe nënvizonivendosur në shiritin në kutinë e veglave "Editing". Hapet një listë në të cilën mund të zgjidhni një pozicion "Zgjedhja e një grupi qelizash ...".
Përveç kësaj, ne mund të arrijmë në dritaren e dëshiruar për zgjedhjen e një grupi qelizash në një mënyrë tjetër. Ky opsion do të jetë veçanërisht i dobishëm për ata përdorues që kanë instaluar një version të programit më herët se Excel 2007, pasi metoda përmes butonit Gjeni dhe nënvizoni këto aplikacione nuk i mbështesin. Ne zgjedhim vargjeve që duam t'i krahasojmë dhe shtypim tastin F5.
- Aktivizohet një dritare e vogël tranzicioni. Klikoni në butonin "Zgjidh ..." në këndin e saj të poshtëm të majtë.
- Pas kësaj, cilado nga dy nga opsionet e mësipërme që ju zgjidhni, hapet dritarja për zgjedhjen e grupeve të qelizave. Vendosni kaloni në pozicion "Zgjidh linjën në rresht". Klikoni në butonin "OK".
- Siç mund ta shihni, pas kësaj vlerat e mospërputhjes së linjave do të theksohen me një ngjyrim tjetër. Për më tepër, siç mund të gjykohet nga përmbajtja e shiritit të formulës, programi do ta bëjë një prej qelizave aktive të vendosura në linjat e përcaktuara të mospërputhshme.
Metoda 3: formatimi i kushtëzuar
Ju mund të krahasoni duke përdorur metodën e formatimit të kushtëzuar. Ashtu si në metodën e mëparshme, zonat e krahasuara duhet të jenë në të njëjtën fletë pune Excel dhe të sinkronizohen me njëra-tjetrën.
- Para së gjithash, ne zgjedhim cilën zonë të tryezës do ta konsiderojmë kryesore, dhe në të cilën të kërkojmë dallime. Le ta bëjmë të fundit në tabelën e dytë. Prandaj, ne zgjedhim listën e punëtorëve që gjenden në të. Duke lëvizur në skedë "Home"klikoni në butonin Formatimi i kushtëzuare cila është e vendosur në shirit në bllok "Styles". Nga lista lëshuese, shkoni te Menaxhimi i rregullave.
- Aktivizohet dritarja e menaxherit të rregullave. Klikoni në butonin në të Krijoni rregull.
- Në dritaren që fillon, zgjidhni pozicionin Përdorni formula. Në fushë "Qelizat e formatit" shkruaj një formulë që përmban adresat e qelizave të para të rangut të kolonave të krahasuara, të ndara me një shenjë "jo të barabartë" (). Vetëm kjo shprehje do të përballet kësaj here. "=". Për më tepër, adresimi absolut duhet të zbatohet për të gjitha koordinatat e kolonës në këtë formulë. Për ta bërë këtë, zgjidhni formulën me kursorin dhe shtypni tastin tre herë F4. Siç mund ta shihni, një shenjë dollari u shfaq pranë të gjitha adresave të kolonave, që do të thotë kthimin e lidhjeve në ato absolute. Për rastin tonë të veçantë, formula do të marrë formën e mëposhtme:
= A2 $ D2Ne e shkruajmë këtë shprehje në fushën e mësipërme. Pas kësaj, klikoni në butonin "Formati ...".
- Dritarja aktivizohet Formati i qelizave. Shko te skeda "Mbushja". Këtu në listën e ngjyrave ndalojmë zgjedhjen në ngjyrën me të cilën duam të ngjyrosim ato elemente ku të dhënat nuk përputhen. Klikoni në butonin "OK".
- Duke u kthyer në dritare për krijimin e një rregulli të formatimit, klikoni në butonin "OK".
- Pasi të keni lëvizur automatikisht në dritare Menaxheri i rregullave klikoni në butonin "OK" dhe në të.
- Tani në tabelën e dytë, elementët që kanë të dhëna që nuk përkojnë me vlerat përkatëse të zonës së tabelës së parë, do të theksohen në ngjyrën e zgjedhur.
Ekziston një mënyrë tjetër për të aplikuar formatimin e kushtëzuar në detyrë. Ashtu si opsionet e mëparshme, kërkon vendndodhjen e të dy zonave të krahasuara në të njëjtën fletë, por ndryshe nga metodat e përshkruara më parë, kushti për sinkronizimin ose klasifikimin e të dhënave nuk do të jetë i detyrueshëm, gjë që e dallon këtë opsion nga ato të përshkruara më parë.
- Ne zgjedhim fushat që do të krahasohen.
- Shkoni në skedën e thirrur "Home". Klikoni në butonin Formatimi i kushtëzuar. Në listën e aktivizuar, zgjidhni pozicionin Rregullat e zgjedhjes së qelizave. Në menunë tjetër ne bëjmë një zgjedhje pozicioni Vlerat e kopjuar.
- Fillon dritarja për konfigurimin e zgjedhjes së vlerave të kopjuara. Nëse keni bërë gjithçka siç duhet, atëherë në këtë dritare mbetet vetëm të klikoni në butonin "OK". Edhe pse, nëse dëshironi, në fushën përkatëse të kësaj dritare, ju mund të zgjidhni një ngjyrë të ndryshme të theksuar.
- Pasi të kryejmë veprimin e specifikuar, të gjithë elementët përsëritës do të theksohen në ngjyrën e zgjedhur. Ato elemente që nuk përputhen do të mbeten të pikturuara në ngjyrën e tyre origjinale (të bardha si parazgjedhje). Kështu, menjëherë mund të shihni vizualisht se cili është ndryshimi midis vargjeve.
Nëse dëshironi, përkundrazi mund të ngjyrosni elementët e papajtueshëm dhe ata tregues që përputhen, lini mbushjen me të njëjtën ngjyrë. Algoritmi i veprimeve është pothuajse i njëjtë, por në dritaren e cilësimeve për nënvizimin e vlerave të kopjuara në fushën e parë në vend të parametrit "Duplicate" duhet të zgjedhin "Unique". Pas kësaj, klikoni në butonin "OK".
Kështu, pikërisht ato indikatorë që nuk përkojnë do të theksohen.
Mësimi: Formatimi i kushtëzuar në Excel
Metoda 4: formula komplekse
Ju gjithashtu mund të krahasoni të dhënat duke përdorur një formulë komplekse bazuar në funksionin countif. Duke përdorur këtë mjet, mund të llogaritni se sa përsëritet secili element nga kolona e zgjedhur e tabelës së dytë në të parën.
operator countif i referohet një grupi statistikor të funksioneve. Detyra e saj është të numërojë numrin e qelizave, vlerat e të cilave plotësojnë një gjendje të caktuar. Sintaksa e këtij operatori është si më poshtë:
= COUNTIF (varg; kriter)
argument "Gama" paraqet adresën e vargut në të cilin llogariten vlerat përputhen.
argument "Kriteri" cakton një kusht ndeshjes. Në rastin tonë, do të jenë koordinatat e qelizave specifike në zonën e tabelës së parë.
- Ne zgjedhim elementin e parë të kolonës shtesë në të cilën do të llogaritet numri i ndeshjeve. Tjetra, klikoni në ikonën "Vendos funksionin".
- Duke filluar Magjistarët e funksionit. Shkoni në kategori "Statistikore". Gjeni emrin në listë "Countif". Pasi ta zgjidhni atë, klikoni në butonin "OK".
- Hapet dritarja e Argumentit të Operatorit countif. Siç mund ta shihni, emrat e fushave në këtë dritare korrespondojnë me emrat e argumenteve.
Vendoseni kursorin në fushë "Gama". Pas kësaj, duke mbajtur butonin e majtë të miut, zgjidhni të gjitha vlerat e kolonës me emrat e tabelës së dytë. Siç mund ta shihni, koordinatat menjëherë hyjnë në fushën e specifikuar. Por për qëllimet tona, kjo adresë duhet të bëhet absolute. Për ta bërë këtë, zgjidhni këto koordinata në fushë dhe shtypni butonin F4.
Siç mund ta shihni, lidhja ka marrë një formë absolute, e cila karakterizohet nga prania e shenjave të dollarit.
Pastaj shkoni në fushë "Kriteri"duke vendosur kursorin atje. Ne klikojmë elementin e parë me mbiemrat në rangun e tabelës së parë. Në këtë rast, lini lidhjen relative. Pasi të shfaqet në fushë, mund të klikoni në butonin "OK".
- Rezultati shfaqet në elementin e fletës. Shtë e barabartë me numrin "1". Kjo do të thotë që në listën e emrave të tabelës së dytë, mbiemrin "Grinev V.P.", e cila është e para në listën e grupit të tabelës së parë, ndodh një herë.
- Tani duhet të krijojmë një shprehje të ngjashme për të gjithë elementët e tjerë të tabelës së parë. Për ta bërë këtë, ne do të kopjojmë duke përdorur shënuesin e mbushjes, siç kemi bërë tashmë më parë. Vendoseni kursorin në pjesën e poshtme të djathtë të elementit të fletës që përmban funksionin countif, dhe pasi ta ktheni në një shënues mbushës, mbani shtypur butonin e majtë të miut dhe tërhiqeni kursorin poshtë.
- Siç mund ta shihni, programi llogaritte rastësitë duke krahasuar secilën qelizë të tabelës së parë me të dhëna të vendosura në rangun e tabelës së dytë. Në katër raste, rezultati doli "1", dhe në dy raste - "0". Kjo është, programi nuk mund të gjente në tabelën e dytë dy vlera që janë në grupin e tabelës së parë.
Sigurisht, kjo shprehje, për të krahasuar treguesit tabelë, mund të përdoret në formën e saj ekzistuese, por ekziston një mundësi për ta përmirësuar atë.
Ne sigurohemi që ato vlera që janë në tabelën e dytë, por që nuk janë në të parën, shfaqen në një listë të veçantë.
- Para së gjithash, ne do të rimarrim pak formulën tonë countif, gjegjësisht, ne e bëjmë atë një nga argumentet e operatorit IF. Për ta bërë këtë, zgjidhni qelizën e parë në të cilën ndodhet operatori countif. Në rreshtin e formulave para tij, shtoni shprehjen "IF" pa thonjëza dhe hapni kllapën. Tjetra, për ta bërë më të lehtë punën tonë, zgjidhni vlerën në shiritin e formulave "IF" dhe klikoni në ikonën "Vendos funksionin".
- Hapet dritarja e argumenteve të funksionit IF. Siç mund ta shihni, fusha e parë e dritares është mbushur tashmë me vlerën e operatorit countif. Por duhet të shtojmë diçka tjetër në këtë fushë. Ne e vendosim kursorin atje dhe i shtojmë shprehjen ekzistuese "=0" pa thonjëza.
Pas kësaj, shkoni në fushë "Kuptimi nëse është i vërtetë". Këtu do të përdorim një funksion tjetër të fole - STRING. Shkruaj fjalën "LINE" pa thonjëza, pastaj hapni kllapat dhe tregoni koordinatat e qelizës së parë me mbiemrin në tabelën e dytë, dhe pastaj mbyllni kllapat. Konkretisht, në rastin tonë, në terren "Kuptimi nëse është i vërtetë" Shprehja e mëposhtme doli:
LINE (D2)Tani operatori STRING do të raportojë funksionet IF numrin e linjës në të cilën ndodhet një mbiemër të veçantë, dhe në rastin kur kushti i specifikuar në fushën e parë është i përmbushur, funksioni IF do ta shfaq këtë numër në qelizë. Klikoni në butonin "OK".
- Siç mund ta shihni, rezultati i parë shfaqet si "FALSE". Kjo do të thotë që vlera nuk i plotëson kushtet e operatorit. IF. Kjo është, mbiemri i parë është i pranishëm në të dy listat.
- Duke përdorur shënuesin e mbushjes, ne kopjojmë shprehjen e operatorit në mënyrën e zakonshme IF në të gjithë kolonën. Siç mund ta shihni, për dy pozicione që janë të pranishme në tabelën e dytë, por jo në të parën, formula jep numrat e linjës.
- Ne nisemi nga zona e tryezës në të djathtë dhe mbushim kolonën me numra sipas radhës, duke filluar nga 1. Numri i numrave duhet të përputhet me numrin e rreshtave në tabelën e dytë për t'u krahasuar. Për të shpejtuar procesin e numrimit, mund të përdorni gjithashtu shënuesin e mbushjes.
- Pas kësaj, zgjidhni qelizën e parë në të djathtë të kolonës me numra dhe klikoni në ikonën "Vendos funksionin".
- Hapet Wizard Feature. Shkoni në kategori "Statistikore" dhe bëni një zgjedhje të emrit "Vogël". Klikoni në butonin "OK".
- funksion SMALLdritarja e argumentit të të cilit është hapur, ka për qëllim të shfaq vlerën më të vogël të specifikuar në llogari.
Në fushë "Array" specifikoni koordinatat e diapazonit të kolonës shtesë "Numri i ndeshjeve"të cilën e kemi konvertuar më parë duke përdorur funksionin IF. Ne i bëjmë të gjitha lidhjet absolute.
Në fushë "K" tregon se cila llogari duhet të shfaqet vlera më e ulët. Këtu tregojmë koordinatat e qelizës së parë të kolonës me numërim, të cilin kohët e fundit kemi shtuar. Ne e lëmë adresën relative. Klikoni në butonin "OK".
- Operatori tregon rezultatin - një numër 3. Itshtë numri më i vogël i numrave të rreshtave të papërshtatshme të grupeve të tabelave. Duke përdorur shënuesin e mbushjes, kopjoni formulën në fund.
- Tani, duke ditur numrat e linjës së elementeve të papërshtatshme, ne mund të futim në qelizë vlerat e tyre duke përdorur funksionin INDEX. Zgjidhni elementin e parë të fletës që përmban formulën SMALL. Pas kësaj, shkoni në rreshtin e formulave dhe para emrit "Vogël" shtoni emrin "INDEX" pa thonjëza, menjëherë hapni kllapën dhe vendosni një pikëpresje (;). Pastaj zgjidhni emrin në rreshtin e formulave "INDEX" dhe klikoni në ikonën "Vendos funksionin".
- Pas kësaj, hapet një dritare e vogël në të cilën ju duhet të përcaktoni pamjen e referencës duhet të ketë një funksion INDEX ose të dizajnuara për të punuar me vargje. Ne kemi nevojë për opsionin e dytë. Installedshtë instaluar si parazgjedhje, kështu që në këtë dritare thjesht klikoni në butonin "OK".
- Fillon dritarja e argumentit të funksionit INDEX. Ky operator ka për qëllim të nxjerrë një vlerë që është e vendosur në një grup specifik në vargun e specifikuar.
Siç mund ta shihni, fusha Numri i linjës të mbushura tashmë me vlera funksioni SMALL. Nga vlera tashmë ekzistuese atje, diferenca midis numrimit të fletës Excel dhe numrimit të brendshëm të zonës së tabelës duhet të zbritet. Siç mund ta shihni, ne kemi vetëm një kokë mbi vlerat e tabelës. Kjo do të thotë se ndryshimi është një linjë. Prandaj, ne shtojmë në fushë Numri i linjës kuptimi "-1" pa thonjëza.
Në fushë "Array" specifikoni adresën e diapazonit të vlerave të tabelës së dytë. Në të njëjtën kohë, ne i bëjmë të gjitha koordinatat absolute, domethënë, vendosim përpara tyre shenjën e dollarit në mënyrën që kemi përshkruar më parë.
Klikoni në butonin "OK".
- Pas shfaqjes së rezultatit në ekran, ne e zgjasim funksionin duke përdorur shënuesin e mbushjes në fund të kolonës. Siç mund ta shihni, të dy mbiemrat që janë të pranishëm në tabelën e dytë, por nuk janë në të parën, shfaqen në një varg të veçantë.
Metoda 5: krahasoni vargjeve në libra të ndryshëm
Kur krahasoni rangun në libra të ndryshëm, mund të përdorni metodat e mësipërme, përveç atyre opsioneve ku dëshironi të vendosni të dy zonat e tabelës në një fletë. Kushti kryesor për procedurën e krahasimit në këtë rast është hapja e dritareve të të dy skedarëve njëkohësisht. Për versionet e Excel 2013 dhe më vonë, si dhe për versionet para Excel 2007, nuk ka probleme me këtë gjendje. Por në Excel 2007 dhe Excel 2010, në mënyrë që të hapen të dy dritaret në të njëjtën kohë, kërkohen manipulime shtesë. Si ta bëni këtë përshkruhet në një mësim të veçantë.
Mësimi: Si të hapni Excel në dritare të ndryshme
Siç mund ta shihni, ekzistojnë një numër mundësish për të krahasuar tabelat ndërmjet tyre. Cila mundësi për t’u përdorur varet nga ku saktësisht të dhënat tabelare janë të vendosura në lidhje me njëra-tjetrën (në një fletë, në libra të ndryshëm, në çarçafë të ndryshëm), dhe gjithashtu se si saktësisht përdoruesi dëshiron që kjo krahasim të shfaqet në ekran.
