PëRMBLEDHJE E SPREADSHEETEVE Në EXCEL

Shumë shpesh, përdoruesit e Excel përballen me detyrën e krahasimit të dy tabelave ose listave për të identifikuar dallimet ose elementet që mungojnë në to. Secili përdorues ballafaqohet me këtë detyrë në mënyrën e vet, por më së shpeshti shpenzon shumë kohë për zgjidhjen e kësaj çështjeje, pasi jo të gjitha qasjet ndaj këtij problemi janë racionale. Në të njëjtën kohë, ekzistojnë disa algoritme të provuar të veprimeve 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ë bëjmë një vështrim më të afërt në këto opsione.

Shih gjithashtu: Krahasimi i dy dokumenteve në MS Word

Metodat e krahasimit

Ka disa mënyra për të krahasuar tabelat në Excel, por të gjitha mund të ndahen në tri grupe të mëdha:

krahasimi i listave që janë në të njëjtën fletë;

krahasimi i tabelave të vendosura në fletë të ndryshme;

krahasimi i tabelave të tabelave në skedarë të ndryshëm.

Në bazë të këtij klasifikimi, së pari, zgjidhen metodat e krahasimit dhe përcaktohen veprimet dhe algoritmet specifike për kryerjen e detyrës. Për shembull, kur bën krahasime në libra të ndryshëm, duhet të hapësh njëkohësisht dy skedarë Excel.

Përveç kësaj, duhet të thuhet se krahasimi i tabelave ka kuptim vetëm kur ato kanë një strukturë të ngjashme.

Metoda 1: formulë e thjeshtë

Mënyra më e lehtë për të krahasuar të dhënat në dy tabela është përdorimi i një formule të thjeshtë të barazisë. Nëse të dhënat përputhen, atëherë ai jep vlerën TRUE, dhe nëse jo, pastaj - FALSE. Është e mundur të krahasohen të dyja të dhënat numerike dhe tekstet. Disavantazhi i kësaj metode është se mund të përdoret vetëm nëse të dhënat në tabelë janë porositur ose klasifikuar në të njëjtën mënyrë, janë të sinkronizuara dhe kanë një numër të barabartë rreshta. 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 emrat janë vendosur.

Për këtë ne kemi nevojë për një kolonë shtesë në fletë. Futni shenjën atje "=". Pastaj klikoni në artikullin e parë që do të krahasohet në listën e parë. Përsëri kemi vënë simbolin "=" nga tastiera. Pastaj klikoni në qelizën e parë të kolonës, të cilën e krahasojmë, në tabelën e dytë. Shprehja është e llojit në vijim:
= A2 = D2
Edhe pse, natyrisht, në çdo rast koordinatat do të jenë të ndryshme, por thelbi do të mbetet i njëjtë.
Kliko në butonin hyjpër të marrë rezultate krahasimi. Siç mund ta shihni, kur krahasoni qelizat e para të të dyja listave, programi tregoi një tregues "TRUE"që do të thotë ndeshje e të dhënave.

Tani ne duhet të kryejmë një operacion të ngjashëm me qelizat e mbetura të të dy tabelave në kolonat që krahasojmë. Por thjesht mund të kopjoni formulën, e cila do të kursente shumë kohë. Ky faktor është veçanërisht i rëndësishëm kur krahasojmë listat me një numër të madh të linjave.
Procedura e kopjimit është më e lehtë për të kryer duke përdorur dorezën e mbushjes. Ne vendosim kursorin në këndin e poshtëm të djathtë të qelizës, ku kemi marrë treguesin "TRUE". Në të njëjtën kohë, ajo duhet të konvertohet në një kryq të zi. Ky është shënuesi i mbushjes. Klikoni butonin e majtë të miut dhe tërhiqni kursorin poshtë nga numri i rreshtave në vargjet e tabelave të krahasuara.

Siç e shohim, tani në kolonën shtesë shfaqen të gjitha rezultatet e krahasimit të të dhënave në dy kolona të vargjeve tabelare. Në rastin tonë, të dhënat nuk përputheshin vetëm në një rresht. Kur u krahasua, formula dha rezultatin "FALSE". Për të gjitha linjat e tjera, siç mund ta shihni, formula e krahasimit dha treguesin "TRUE".

Përveç kësaj, është e mundur për të llogaritur numrin e 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 mbi ikonën "Funksioni i futjes".

Në dritare Zotëruesit e funksioneve në një grup operatorësh "Matematike" zgjidhni emrin SUMPRODUCT. Kliko në butonin "OK".

Aktivizohet dritarja e argumentit të funksionit. SUMPRODUCTdetyra kryesore e të cilit është për të llogaritur shumën e produkteve të gamës së zgjedhur. Por ky funksion mund të përdoret për qëllimet tona. Sintaksa e tij është shumë e thjeshtë:
= SUMPRODUCT (array1; array2; ...)
Në total, mund të përdorni adresa deri në 255 vargje si argumente. Por në rastin tonë ne do të përdorim vetëm dy vargje, përveç kësaj, si një argument.
Vendos kursorin në fushë "Array1" dhe zgjidhni vargun e të dhënave në krahasim në zonën e parë në fletë. Pas kësaj vendosëm një shenjë në fushë. "jo e barabartë" () dhe zgjidhni vargun e krahasuar të rajonit të dytë. Tjetra, mbyllni shprehjen që rezulton me kllapa, para së cilës ne vendosim dy karaktere "-". Në rastin tonë, marrim shprehjen e mëposhtme:
- (A2: A7D2: D7)
Kliko në butonin "OK".

Operatori llogarit dhe tregon rezultatin. Siç e shohim, në rastin tonë rezultati është i barabartë me numrin "1", që do të thotë, do të thotë që në listat e krahasuara u gjet një mospërputhje. Nëse listat ishin plotësisht identike, rezultati do të ishte i barabartë me numrin "0".

Në të njëjtën mënyrë, mund të krahasoni të dhënat në tabelat që ndodhen në fletë të ndryshme. Por në këtë rast është e dëshirueshme që linjat në to të numërohen. Pjesa tjetër e procedurës së krahasimit është pothuajse pikërisht siç është përshkruar më sipër, përveç faktit që kur të bëni një formulë, duhet të kaloni në mes të fletëve. Në rastin tonë shprehja do të ketë formën e mëposhtme:

= B2 = fletë2! B2

Kjo është, siç e shohim, para koordinatave të të dhënave, të cilat gjenden në fletë të tjera, të ndryshme nga vendi ku shfaqet rezultati i krahasimit, tregohet numri i fletës dhe emri i thirrjes.

Metoda 2: Zgjidhni grupet e qelizave

Krahasimi mund të bëhet duke përdorur mjetin përzgjedhës të grupeve qelizore. Me të, ju gjithashtu mund të krahasoni 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ë.

Zgjidh grupet e krahasuara. Shko te tab "Home". Tjetra, klikoni mbi ikonën "Gjeni dhe theksoj"e cila gjendet në kasetë në bllokun e mjeteve "Editing". Hapet një listë në të cilën duhet të zgjidhni një pozicion. "Përzgjedhja e një grupi të qelizave ...".
Përveç kësaj, në dritaren e dëshiruar të përzgjedhjes së një grupi të qelizave mund të arrihet 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 versionin e programit më herët se Excel 2007, sepse metoda përmes butonit "Gjeni dhe theksoj" Këto aplikacione nuk mbështesin. Zgjidhni grupet që duam të krahasojmë dhe shtypni butonin F5.
Është aktivizuar një dritare e vogël e tranzicionit. Kliko në butonin "Highlight ..." në këndin e poshtëm të majtë.

Pas kësaj, cilado nga dy opsionet e mësipërme që zgjidhni, nis një dritare për përzgjedhjen e grupeve të qelizave. Set kaloni në pozitë "Zgjidhni sipas rreshtit". Kliko në butonin "OK".

Siç mund ta shikoni, pas kësaj, vlerat jo të përputhura të rreshtave do të theksohen me një ngjyrë të ndryshme. Përveç kësaj, siç mund të gjykohet nga përmbajtja e vijës së formulës, programi do të bëjë një nga qelizat aktive në linjat e përafërta të specifikuara.

Metoda 3: Formulimi i kushtëzuar

Mund të bëni një krahasim 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 të Excel dhe të sinkronizohen me njëra-tjetrën.

Para së gjithash, ne zgjedhim se cilat tabela do të shqyrtojmë kryesore dhe cilat për të kërkuar dallime. E fundit ne do të bëjmë në tabelën e dytë. Prandaj, zgjidhni listën e punonjësve të vendosur në të. Lëvizja në skedën "Home", klikoni mbi butonin "Formatimi i kushtëzuar"e cila gjendet në kasetë në bllok "Styles". Nga drop-down list, vazhdoni "Menaxhimi i Rregullave".

Aktivizohet dritarja e administratorit të rregullave. Ne shtypim në atë në butonin "Krijo një rregull".

Në dritaren e nisjes, bëni një zgjedhje pozitive "Përdor formulën". Në fushë "Format qelizat" shkruani formulën që përmban adresat e qelizave të para të vargjeve të kolonave të krahasuara, të ndara nga shenja "jo e barabartë" (). Vetëm kjo shprehje do të ketë një shenjë këtë herë. "=". Përveç kësaj, 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 klikoni tri herë mbi çelësin F4. Siç mund ta shihni, një shenjë e dollarit u shfaq pranë të gjitha adresave të kolonës, 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 $ D2
Ne shkruajmë këtë shprehje në fushën e mësipërme. Pas kësaj klikoni mbi butonin "Format ...".

Dritarja e aktivizuar "Format qelizat". Shko te tab "Mbushja". Këtu në listën e ngjyrave ne ndalim zgjedhjen në ngjyrë me të cilën ne duam të ngjyrosim ato elemente ku të dhënat nuk do të përputhen. Ne shtypim butonin "OK".

Duke u kthyer tek dritarja për krijimin e një rregulli të formatimit, klikoni mbi butonin. "OK".

Pas lëvizjes automatikisht në dritare Menaxher i Rregullit klikoni mbi butonin "OK" dhe në të.

Tani në tabelën e dytë, elementët që kanë të dhëna që nuk përputhen me vlerat korresponduese 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ë përdorur formatimin e kushtëzuar për të kryer detyrën. Ashtu si opsionet e mëparshme, ajo kërkon vendndodhjen e të dy zonave të krahasuara në të njëjtën fletë, por ndryshe nga metodat e përshkruara më parë, kushtet për sinkronizimin ose klasifikimin e të dhënave nuk do të jenë të nevojshme, gjë që e dallon këtë opsion nga ato të përshkruara më parë.

Bëni një përzgjedhje të zonave që duhet të krahasohen.

Kryen një kalim në skedën e quajtur "Home". Kliko në butonin. "Formatimi i kushtëzuar". Në listën e aktivizuar, zgjidhni pozicionin "Rregullat për përzgjedhjen e qelizave". Në menynë tjetër ne bëjmë një zgjedhje pozitive. "Vlerat e dyfishta".

Hapet dritarja për caktimin e përzgjedhjes së vlerave të dublikuara. Nëse keni bërë gjithçka në mënyrë korrekte, atëherë në këtë dritare mbetet vetëm të klikoni mbi butonin. "OK". Edhe pse, nëse dëshironi, mund të zgjidhni një ngjyrë tjetër përzgjedhëse në fushën përkatëse të kësaj dritareje.

Pasi të kryejmë veprimin e specifikuar, të gjithë elementët e kopjuar do të theksohen në ngjyrën e zgjedhur. Ato elemente që nuk përputhen do të mbeten të ngjyrosura në ngjyrën e tyre origjinale (të bardhë sipas parazgjedhjes). Kështu, ju mund të shikoni menjëherë shikimin se cili është dallimi midis vargjeve.

Nëse dëshironi, ju mundeni, përkundrazi, të pikturoj elementët jo-koincidentë dhe ato tregues që përputhen mund të lihen me të njëjtën ngjyrë të mbushur. Në këtë rast, algoritmi i veprimeve është pothuajse i njëjtë, por në dritaren e cilësimeve për nxjerrjen në pah të vlerave të dyfishta në fushën e parë në vend të parametrit "Duplicate" zgjidhni opsionin "Unique". Pas kësaj, kliko mbi butonin "OK".

Kështu, do të theksohen ato tregues që nuk përputhen.

Mësimi: Formulimi i kushtëzuar në Excel

Metoda 4: formula komplekse

Ju gjithashtu mund të krahasoni të dhënat duke përdorur një formulë komplekse, e cila bazohet në funksionin countif. Duke përdorur këtë mjet, mund të llogarisni se sa secili element nga kolona e zgjedhur në tabelën e dytë përsëritet në të parën.

operator countif i referohet një grupi statistikor të funksioneve. Detyra e tij është të numërojë numrin e qelizave, vlerat e të cilave përmbushin një kusht të dhënë. Sintaksa e këtij operatori është si më poshtë:

= COUNTERS (rang, kriter)

argument "Gama" është adresa e grupit në të cilin llogariten vlerat e përputhjes.

argument "Kriteri" vendos kushtet e ndeshjes. Në rastin tonë, do të jenë koordinatat e qelizave të veçanta në tabelën e parë.

Përzgjidhni elementin e parë të kolonës shtesë në të cilën do të llogaritet numri i ndeshjeve. Tjetra, klikoni mbi ikonën "Funksioni i futjes".

Lansimi ndodh Zotëruesit e funksioneve. Shko te kategoria "Statistikore". Gjeni në listë emrin "Countif". Pas zgjedhjes së tij, klikoni mbi butonin. "OK".

Dritarja argëtuese e operatorit është nisur. countif. Siç mund ta shihni, emrat e fushave në këtë dritare korrespondojnë me emrat e argumenteve.
Vendosni 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ë bien 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 koordinatat në fushë dhe klikoni mbi çelësin 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 klikoni mbi elementin e parë me emrat e fundit në gamën e tabelës së parë. Në këtë rast, lini lidhjen relative. Pasi të shfaqet në fushë, mund të klikoni mbi 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ë emri i fundit "Grinev V.P."e cila është e para në listën e grupit të parë të tabelës, 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ë, kopjoni atë duke përdorur shënuesin e mbushjes, siç kemi bërë më parë. Vendoseni kursorin në pjesën e poshtme të djathtë të elementit të fletës që përmban funksionin countif, dhe pasi ta konvertoni në shënuesin e mbushjes, mbani të shtypur butonin e majtë të miut dhe tërhiqni kursorin poshtë.

Siç mund ta shihni, programi bëri një llogaritje të ndeshjeve duke krahasuar secilën qelizë të tabelës së parë me të dhënat që ndodhen në gamën e tabelës së dytë. Në katër raste, rezultati doli "1", dhe në dy raste - "0". Pra, programi nuk mund të gjejë në tabelën e dytë dy vlerat që janë në tabelën e tabelës së parë.

Sigurisht, kjo shprehje për të krahasuar treguesit e tabelës, mund të zbatohet në formën ekzistuese, por ka një mundësi për ta përmirësuar atë.

Le të bëjmë që ato vlera që janë në dispozicion në tabelën e dytë, por që mungojnë në të parën, shfaqen në një listë të veçantë.

Para së gjithash, le të riformulojmë formulën tonë countif, përkatësisht e bëjnë atë një nga argumentet e operatorit IF. Për ta bërë këtë, zgjidhni qelizën e parë në të cilën operatori është i vendosur countif. Në shiritin e formulës para se të shtojmë shprehjen "IF" pa kuotat dhe hapni kllapa. Tjetra, për ta bërë më të lehtë për ne të punojmë, ne zgjedhim vlerën në shiritin e formulës. "IF" dhe klikoni mbi ikonën "Funksioni i futjes".

Hapet dritarja e argumentit të funksionit. IF. Siç mund ta shihni, fusha e parë e dritares tashmë është e mbushur me vlerën e operatorit. countif. Por ne duhet të shtojmë diçka tjetër në këtë fushë. Ne vendosim kursorin atje dhe shtojmë shprehjen tashmë ekzistuese "=0" pa kuotat.
Pas kësaj shkoni në fushë "Vlerë nëse është e vërtetë". Këtu do të përdorim një funksion tjetër të mbivendosur - STRING. Shkruani fjalën "LINE" pa citate, atëherë hapni kllapat dhe specifikoni koordinatat e qelizës së parë me emrin e fundit në tabelën e dytë, pastaj mbyllni kllapa. Në mënyrë të veçantë, në rastin tonë në terren "Vlerë nëse është e vërtetë" mori këtë shprehje:
LINE (D2)
Tani operatori STRING do të raportojnë funksionet IF numri i linjës në të cilën ndodhet emri i veçantë i fundit, dhe në rastin kur plotësohet kushti i përcaktuar në fushën e parë, funksioni IF do të nxjerrë këtë numër në qelizë. Ne shtypim butonin "OK".

Siç mund ta shihni, rezultati i parë shfaqet si "FALSE". Kjo do të thotë se 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, në mënyrën e zakonshme ne kopjojmë shprehjen e operatorit IF në të gjithë kolonën. Siç mund ta shihni, në dy pozicione që janë të pranishme në tabelën e dytë, por jo në të parën, formula jep numrat e vijave.

Tërhiqni nga tabela në të djathtë dhe mbushni kolonën me numra në rregull, duke filluar nga 1. Numri i numrave duhet të përputhet me numrin e rreshtave në tabelën e dytë krahasuese. Për të përshpejtuar procedurën e numërimit, mund të përdorni edhe shënuesin e mbushjes.

Pas kësaj, zgjidhni qelizën e parë në të djathtë të kolonës me numra dhe klikoni mbi ikonën "Funksioni i futjes".

hap Funksion magjistar. Shko te kategoria "Statistikore" dhe të bëjë një zgjedhje të emrave "EMRI". Kliko në butonin "OK".

funksion E ULJA, dritarja argumente e së cilës është hapur, është projektuar për të shfaqur vlerën më të ulët të përcaktuar nga llogaria.
Në fushë "Array" specifikoni koordinatat e vargut të kolonës shtesë "Numri i ndeshjeve"të cilat më parë e konvertuam duke përdorur funksionin IF. Ne i bëjmë të gjitha lidhjet absolute.
Në fushë "K" tregoni se çfarë llogarie duhet të shfaqet vlera më e ulët. Këtu tregojmë koordinatat e qelizës së parë të kolonës me numërimin, të cilën e kemi shtuar kohët e fundit. Adresa është lënë relative. Kliko në butonin "OK".

Operatori shfaq rezultatin - numrin 3. Ky është numërimi më i vogël i rreshtave të papërputhshme të vargjeve të tabelave. Duke përdorur shënuesin e mbushjes, kopjoni formulën në fund.

Tani, duke ditur numrin e vijave të elementeve që nuk përputhen, ne mund të fusim në qelizë dhe vlerat e tyre duke përdorur funksionin INDEX. Përzgjidhni elementin e parë të fletës që përmban formulën E ULJA. Pas kësaj shkoni në vijën e formulës dhe përpara emrit "EMRI" shtoj emrin "INDEX" pa citate, hapni menjëherë tabelën dhe vendosni një pikëpresje (;). Pastaj zgjidhni emrin në shiritin e formulës. "INDEX" dhe klikoni mbi ikonën "Funksioni i futjes".

Pas kësaj, hapet një dritare e vogël në të cilën duhet të përcaktoni nëse referenca duhet të ketë një funksion INDEX ose të dizajnuara për të punuar me vargjet. Na duhet alternativa e dytë. Është vendosur sipas parazgjedhur, prandaj në këtë dritare thjesht klikoni mbi butonin. "OK".

Fillon dritarja e argumentit të funksionit. INDEX. Kjo deklaratë është projektuar për të shfaqur vlerën që gjendet në një grup specifik në vijën e specifikuar.
Siç mund ta shikoni, fushën "Numri i linjës" tashmë e mbushur me vlera funksioni E ULJA. Nga vlera që tashmë ekziston atje, hiqni diferencën midis numrit të fletës së Excel dhe numrit të brendshëm të zonës së tabelës. Siç mund ta shihni, mbi vlerat e tabelës ne kemi vetëm një kapak. Kjo do të thotë se dallimi është një rresht. Prandaj ne shtojmë në fushë "Numri i linjës" kuptim "-1" pa kuotat.
Në fushë "Array" specifikoni adresën e vargut të vlerave të tabelës së dytë. Në të njëjtën kohë, ne bëjmë të gjitha koordinatat absolute, dmth. Vendosim një shenjë dollarësh para tyre në mënyrën e përshkruar më parë nga ne.
Ne shtypim butonin "OK".

Pas shfaqjes së rezultatit në ekran, shtrijmë funksionin duke përdorur shënuesin e mbushjes në fund të kolonës poshtë. Siç mund ta shihni, të dy mbiemrat që janë të pranishëm në tryezën e dytë, por jo në të parën, shfaqen në një distancë të veçantë.

Metoda 5: Krahasimi i vargjeve në libra të ndryshëm

Kur krahasoni vargjet në libra të ndryshëm, mund të përdorni metodat e listuara më sipër, duke përjashtuar ato opsione që kërkojnë vendosjen e të dy hapësirave të tryezave në një fletë. Kushti kryesor për kryerjen e procedurës së krahasimit në këtë rast është hapja e dritareve të të dy dosjeve në të njëjtën kohë. Nuk ka probleme për versionet e Excel 2013 dhe më vonë, si dhe për versionet para Excel 2007. Por në Excel 2007 dhe Excel 2010, për të hapur dy dritare në të njëjtën kohë, nevojiten manipulime shtesë. Si ta bëni këtë është përshkruar në një mësim të veçantë.

Mësimi: Si të hapni Excel në dritare të ndryshme

Siç mund ta shihni, ka një numër mundësish për të krahasuar tabelat me njëri-tjetrin. Cili opsion për përdorim varet nga saktësisht se ku gjenden të dhënat tabelare në lidhje me njëri-tjetrin (në një fletë, në libra të ndryshëm, në fletë të ndryshme), dhe gjithashtu se si saktësisht përdoruesi dëshiron që ky krahasim të shfaqet në ekran.