LISTAT E LIDHURA ME EXCEL Në EXCEL

Gjatë kryerjes së detyrave të caktuara në Excel, ndonjëherë ju duhet të merreni me disa tabela, të cilat gjithashtu lidhen me njëri-tjetrin. Kjo është, të dhënat nga një tabelë tërhiqen në tjetrën, dhe kur ato ndryshojnë, vlerat në të gjitha vargjet e tabelave të lidhura rillogariten.

Tabelat e lidhura janë shumë të dobishme për përpunimin e sasive të mëdha të informacionit. Nuk është shumë e përshtatshme që të gjitha informatat të jenë në një tabelë dhe nëse nuk është homogjene. Është e vështirë të punosh me objekte të tilla dhe t'i bësh ato. Ky problem ka për qëllim eliminimin e tabelave të ndërlidhura, informacion ndërmjet të cilit shpërndahet, por në të njëjtën kohë është i ndërlidhur. Shkon lidhjet e lidhura mund të vendosen jo vetëm brenda një fletë ose një libri, por gjithashtu gjenden në libra të ndara (skedarë). Në praktikë, dy opsionet e fundit përdoren më së shpeshti, meqë qëllimi i kësaj teknologjie është largimi nga grumbullimi i të dhënave dhe grumbullimi i tyre në të njëjtën faqe nuk e zgjidh problemin rrënjësisht. Le të mësojmë se si të krijojmë dhe si të punojmë me këtë lloj të menaxhimit të të dhënave.

Krijimi i tabelave të lidhura

Para së gjithash, le të qëndrojmë në pyetjen se si është e mundur të krijohet një lidhje midis vargjeve të ndryshme të tryezave.

Metoda 1: Lidhja e tabelave direkte me një formulë

Mënyra më e lehtë për të lidhur të dhënat është përdorimi i formulave që lidhen me vargjet e tjera të tabelave. Ajo quhet lidhja e drejtpërdrejtë. Kjo metodë është intuitive, meqë me të, lidhja kryhet pothuajse në të njëjtën mënyrë si krijimi i referencave ndaj të dhënave në një tabelë të vetme tabelare.

Le të shohim se si një shembull mund të formojë një lidhje me lidhje të drejtpërdrejtë. Ne kemi dy tavolina në dy fletë. Në një tabelë, lista e pagave llogaritet duke përdorur një formulë duke shumëzuar shkallën e punëtorëve me një normë të vetme për të gjithë.

Në fletën e dytë ka një gamë tabelare në të cilën ka një listë të të punësuarve me pagat e tyre. Lista e të punësuarve në të dyja rastet paraqitet në të njëjtën mënyrë.

Është e nevojshme që të bëhet që të dhënat mbi normat nga fleta e dytë të tërheqen në qelizat korresponduese të së parës.

Në fletën e parë, zgjidhni qelizën e parë të kolonës. "Bet". Ne kemi vënë në shenjën e saj "=". Tjetra, klikoni mbi etiketën "Fleta 2"Cila ndodhet në anën e majtë të ndërfaqes së Excel-it mbi shiritin e statusit.

Lëviz në zonën e dytë të dokumentit. Klikoni në qelizën e parë në kolonën. "Bet". Pastaj klikoni mbi butonin. hyj në tastierë për të kryer futjen e të dhënave në qelizën në të cilën ishte vendosur shenja "Barabartë".

Pastaj ka një kalim automatik në fletën e parë. Siç mund ta shihni, shkalla e punonjësit të parë nga tabela e dytë është tërhequr në qelizën e duhur. Duke e vendosur kursorin në qelizën që përmban bastin, shohim që formula e zakonshme përdoret për të shfaqur të dhëna në ekran. Por përpara koordinatave të qelizës ku shfaqet të dhënat, ekziston një shprehje "Sheet2!"që tregon emrin e zonës së dokumentit ku ndodhen. Formula e përgjithshme në rastin tonë është si vijon:
= Sheet2! B2

Tani ju duhet të transferoni të dhënat mbi normat e të gjithë punonjësve të tjerë të ndërmarrjes. Natyrisht, kjo mund të bëhet në të njëjtën mënyrë që ne kemi kryer detyrën për punonjësin e parë, por duke pasur parasysh se të dy listat e punonjësve janë të rregulluar në të njëjtën mënyrë, detyra mund të thjeshtohet ndjeshëm dhe të përshpejtojë zgjidhjen e saj. Kjo mund të bëhet duke thjesht kopjuar formulën në rangun e mëposhtëm. Për shkak të faktit se lidhjet në Excel janë relativisht të paracaktuar, kur kopjohen, vlerat ndryshojnë, gjë që na nevojitet. Procedura e kopjimit vetë mund të kryhet duke përdorur shënuesin e mbushjes.
Pra, vendosni kursorin në zonën e djathtë të poshtëm të elementit me formulën. Pas kësaj, kursori duhet të konvertohet në një mbushje në formën e një kryqi të zi. Ne kryejmë pirgin e butonit të majtë të miut dhe terhiqim kursorin në pjesën e poshtme të kolonës.

Të gjitha të dhënat nga e njëjta kolonë Fleta 2 u tërhoqën në tryezë Fleta 1. Kur të dhënat ndryshojnë Fleta 2 ata do të ndryshojnë automatikisht në të parën.

Metoda 2: përdorni një bandë e operatorëve INDEX - MATCH

Por çka nëse lista e punonjësve në vargjet tabelore nuk është e rregulluar në të njëjtin renditje? Në këtë rast, siç u përmend më herët, një nga opsionet është vendosja e lidhjes ndërmjet secilës prej këtyre qelizave që duhet të lidhen me dorë. Por kjo është e përshtatshme vetëm për tavolina të vogla. Për vargjet masive, ky opsion, në rastin më të mirë, do të marrë shumë kohë për të zbatuar, dhe në rastin më të keq - në praktikë nuk do të jetë fare e realizueshme. Por mund ta zgjidhësh këtë problem me një bandë operatorësh INDEX - MATCH. Le të shohim se si mund të bëhet kjo duke i lidhur të dhënat në shkallët tabelore, të cilat u diskutuan në metodën e mëparshme.

Zgjidhni artikullin e parë në kolonën. "Bet". Shko te Funksion magjistarduke klikuar mbi ikonën "Funksioni i futjes".

Funksion magjistar në një grup "Lidhjet dhe vargjet" gjeni dhe përzgjidhni emrin "INDEX".

Ky operator ka dy forma: një formë për të punuar me vargjet dhe një referencë. Në rastin tonë, kërkohet opsioni i parë, prandaj në dritaren tjetër të përzgjedhjes së formularit, i cili do të hapet, e zgjedhim atë dhe kliko mbi butonin "OK".

Dritarja e argumentit të operatorit është kryer. INDEX. Detyra e funksionit të specifikuar është për të shfaqur vlerën që është në diapazonin e përzgjedhur në përputhje me numrin e specifikuar. Formula e përgjithshme e operatorit INDEX është kjo:
= INDEKSI (array; line_number; [column_number])
"Array" - argumenti që përmban adresën e vargut nga i cili do të nxjerrim informacionin me numrin e vargut të specifikuar.
"Numri i linjës" - argumenti që është numri i kësaj linje vetë. Është e rëndësishme të dini se numri i linjës nuk duhet të përcaktohet në lidhje me tërë dokumentin, por vetëm në lidhje me grupin e zgjedhur.
"Numri i kolonës" - Argumenti është fakultativ. Për të zgjidhur problemin tonë në mënyrë specifike, ne nuk do ta përdorim atë, prandaj nuk është e nevojshme të përshkruajmë thelbin e saj veç e veç.
Vendos kursorin në fushë "Array". Pas kësaj shkoni në Fleta 2 dhe, duke mbajtur butonin e majtë të miut, zgjidhni gjithë përmbajtjen e kolonës "Bet".

Pasi koordinatat të shfaqen në dritaren e operatorit, vendosni kursorin në fushë "Numri i linjës". Ne do të shfaqim këtë argument duke përdorur operatorin MATCH. Prandaj, klikoni mbi trekëndëshin që gjendet në të majtë të vijës së funksionit. Hapet një listë e operatorëve të përdorur kohët e fundit. Nëse gjeni midis tyre emrin "Match"atëherë ju mund të klikoni mbi të. Përndryshe, klikoni në artikullin më të fundit në listë - "Karakteristika të tjera ...".

Fillon dritarja standarde. Zotëruesit e funksioneve. Shko tek ajo në të njëjtin grup. "Lidhjet dhe vargjet". Këtë herë në listë, zgjidhni artikullin "Match". Kryeni një klikim në butonin. "OK".

Aktivizon argumentet e dritares së operatorit MATCH. Funksioni i specifikuar ka për qëllim të shfaqë numrin e një vlere në një grup specifik me emrin e tij. Falë kësaj mundësie, ne do të llogarisim numrin e rreshtit të një vlere të caktuar për funksionin. INDEX. sintaksë MATCH paraqitur si:
= MATCH (vlera e kërkimit, lookup array; [match_type])
"Vlera e kërkuar" - argumenti që përmban emrin ose adresën e qelizës së vargut të palës së tretë në të cilën ndodhet. Është pozicioni i këtij emri në rangun e synuar që duhet të llogaritet. Në rastin tonë, argumenti i parë do të jetë referenca qelizore për të Fleta 1në të cilën ndodhen emrat e punonjësve.
"Seti i shikuar" - një argument që përfaqëson një lidhje me një grup në të cilin kërkohet vlera e specifikuar për të përcaktuar pozicionin e saj. Ne do të luajmë këtë kolonë të adresave të rolit "Emri në Fleta 2.
"Tipi i hartës" - një argument që është opcional, por, ndryshe nga deklarata e mëparshme, ne do të kemi nevojë për këtë argument opsional. Tregon se si operatori do të përputhet me vlerën e dëshiruar me rrjetin. Ky argument mund të ketë një nga tre vlerat: -1; 0; 1. Për vargjet e parregulluara, zgjidhni opsionin "0". Ky opsion është i përshtatshëm për rastin tonë.
Pra, le të fillojmë të plotësojmë fushat e dritares së argumenteve. Vendos kursorin në fushë "Vlera e kërkuar", klikoni në qelizën e parë të kolonës "Emri" në Fleta 1.

Pasi të shfaqen koordinatat, vendosni kursorin në fushë "Seti i shikuar" dhe shkoni në shkurtore "Fleta 2"e cila gjendet në pjesën e poshtme të dritares Excel mbi shiritin e statusit. Mbani të shtypur butonin e majtë të miut dhe nxjerr në pah të gjitha qelizat në kolonën. "Emri".

Pas koordinatave të tyre shfaqen në fushë "Seti i shikuar"shkoni në fushë "Tipi i hartës" dhe vendosni numrin nga tastiera "0". Pas kësaj, kthehemi përsëri në fushë. "Seti i shikuar". Fakti është se ne do të kopjojmë formulën, ashtu siç bëmë në metodën e mëparshme. Do të ketë një kompensim të adresave, por ne duhet të rregullojmë koordinatat e array që shikohet. Nuk duhet të zhvendoset. Zgjidhni koordinatat e kursorit dhe klikoni mbi çelësin e funksionit F4. Siç mund ta shihni, një shenjë dollarësh shfaqet para koordinatave, që do të thotë se lidhja nga afërsia është bërë absolute. Pastaj klikoni mbi butonin "OK".

Rezultati shfaqet në qelizën e parë të kolonës. "Bet". Por, para se të kopjojmë, duhet të rregullojmë një fushë tjetër, domethënë argumenti i parë i funksionit INDEX. Për ta bërë këtë, zgjidhni elementin e kolonës që përmban formulën dhe lëvizni në shiritin e formulës. Zgjidhni argumentin e parë të operatorit INDEX (B2: B7) dhe kliko mbi butonin F4. Siç mund ta shihni, shenja e dollarit u shfaq pranë koordinatave të zgjedhura. Kliko në butonin hyj. Në përgjithësi, formula mori formën e mëposhtme:
= INDEX (Sheet2! $ B $ 2: $ B $ 7; MATCH (Sheet1! A4; Sheet2! $ A $ 2: $ A $ 7; 0))

Tani mund të kopjoni duke përdorur shënuesin e mbushjes. Thërrisni atë në të njëjtën mënyrë si kemi folur më herët, dhe e shtrijmë deri në fund të tabelës.

Siç mund ta shikoni, përkundër faktit se rendi i rreshtave të dy tabelave nuk përputhet, megjithatë, të gjitha vlerat shtrëngohen sipas emrave të punëtorëve. Kjo u arrit përmes përdorimit të një kombinimi të operatorëve INDEX-MATCH.

Shih gjithashtu:
Funksioni Excel i INDEX
Funksioni i ndeshjes në Excel

Metoda 3: Kryerja e operacioneve matematikore me të dhëna të asociuara

Lidhja e drejtpërdrejtë e të dhënave është gjithashtu e mirë në atë që lejon jo vetëm të shfaqin vlerat që shfaqen në vargjet e tabelave të tjera në njërën nga tavolinat, por gjithashtu të kryejnë operacione të ndryshme matematikore me ta (shtim, ndarje, zbritje, shumëzim etj.).

Le të shohim se si bëhet kjo në praktikë. Le ta bëjmë këtë Fleta 3 Të dhënat e përgjithshme të pagës së ndërmarrjes do të shfaqen pa shpërthimin e punonjësve. Për këtë, normat e stafit do të tërhiqen Fleta 2, përmbledh (duke përdorur funksionin SUM) dhe shumëzuar me koeficientin duke përdorur formulën.

Zgjidhni qelizën ku do të shfaqet lista e pagave totale Fleta 3. Kliko në butonin "Funksioni i futjes".

Duhet të nisë dritarja Zotëruesit e funksioneve. Shko te grupi "Matematike" dhe zgjidhni emrin atje "SUM". Tjetra, kliko mbi butonin "OK".

Lëvizja në dritaren e argumentit të funksionit SUMi cili është projektuar për të llogaritur shumën e numrave të zgjedhur. Ajo ka sintaksën e mëposhtme:
= SUM (numri 1, numri 2; ...)
Fushat në dritare korrespondojnë me argumentet e funksionit të specifikuar. Edhe pse numri i tyre mund të arrijë 255 copë, për qëllimin tonë vetëm do të mjaftojë. Vendos kursorin në fushë "Number 1". Klikoni në etiketë "Fleta 2" mbi shiritin e statusit.

Pasi kemi kaluar në seksionin e dëshiruar të librit, zgjidhni kolonën që duhet përmbledhur. Ne e bëjmë atë kursorin, duke mbajtur butonin e majtë të miut. Siç mund ta shihni, koordinatat e zonës së përzgjedhur shfaqen menjëherë në fushën e dritares së argumentit. Pastaj klikoni mbi butonin. "OK".

Pas kësaj, ne automatikisht kalojmë në Fleta 1. Siç mund ta shihni, sasia totale e normave të pagave të punëtorëve është shfaqur tashmë në elementin përkatës.

Por kjo nuk është e gjitha. Siç e kujtojmë, paga llogaritet duke shumëzuar vlerën e normës sipas koeficientit. Prandaj, ne përsëri zgjedhim qelizën në të cilën gjendet vlera e përmbledhur. Pas kësaj shkoni në barin e formulës. Ne shtojmë një shenjë shumëzimi në formulën e saj (*), dhe pastaj klikoni mbi elementin në të cilin është vendosur koeficienti. Për të kryer llogaritjen klikoni mbi hyj në tastierë. Siç mund ta shikoni, programi llogariti pagën totale për ndërmarrjen.

Kthehu tek Fleta 2 dhe të ndryshojë madhësinë e normës së çdo punonjësi.

Pas kësaj, përsëri të shkosh në faqe me shumën totale. Siç mund ta shikoni, për shkak të ndryshimeve në tabelën përkatëse, rezultati i pagës totale u rillogaritet automatikisht.

Metoda 4: insert special

Ju gjithashtu mund të lidhni vargjet e tabelave në Excel me një shenjë të veçantë.

Zgjidhni vlerat që duhet të "shtrëngohen" në një tabelë tjetër. Në rastin tonë, kjo është varg kolona. "Bet" në Fleta 2. Klikoni mbi fragmentin e përzgjedhur me butonin e djathtë të mausit. Në listën që hapet, zgjidhni artikullin "Copy". Kombinimi alternativ kyç është Ctrl + C. Pas kësaj lëvizjeje Fleta 1.

Duke u zhvendosur në zonën e dëshiruar të librit, ne zgjedhim qelizat në të cilat doni të tërheqni vlerat. Në rastin tonë, kjo është një kolonë. "Bet". Klikoni mbi fragmentin e përzgjedhur me butonin e djathtë të mausit. Në menunë e kontekstit në shiritin e veglave "Opsionet e Futjes" klikoni mbi ikonën "Fut lidhjen".
Ekziston edhe një alternativë. Nga rruga, kjo është e vetmja për versione të vjetra të Excel. Në menunë e kontekstit, lëvizni kursorin në artikull "Paste Speciale". Në menunë shtesë që hapet, zgjidhni artikullin me të njëjtin emër.

Pas kësaj, hapet një dritare e futur speciale. Ne shtypim butonin "Fut lidhjen" në këndin e poshtëm të majtë të qelizës.

Cilado opsion që ju zgjidhni, vlerat nga një tabelë tabelash do të futen në një tjetër. Kur ndryshoni të dhënat në burim, ato gjithashtu do të ndryshojnë automatikisht në diapazonin e futur.

Mësimi: Paste Speciale në Excel

Metoda 5: Lidhja mes tabelave në libra të shumtë

Përveç kësaj, ju mund të organizoni lidhjen midis hapësirave të tryezave në libra të ndryshëm. Kjo përdor mjetin e futur të veçantë. Veprimet do të jenë absolutisht të ngjashme me ato që ne i konsideruam në metodën e mëparshme, përveç se navigimi gjatë futjes së formulave nuk do të duhet të ndodhë midis zonave të një libri, por midis dosjeve. Natyrisht, të gjitha librat e lidhur duhet të jenë të hapura.

Zgjidh gamën e të dhënave që doni të transferoni në një libër tjetër. Klikoni mbi të me butonin e djathtë të miut dhe zgjidhni pozicionin në menunë që hapet "Copy".

Pastaj shkojmë në librin në të cilin do të duhet të futen këto të dhëna. Zgjidhni gamën e dëshiruar. Kliko butonin e djathtë të mausit. Në menunë e kontekstit në grup "Opsionet e Futjes" zgjidhni një artikull "Fut lidhjen".

Pas kësaj, vlerat do të futen. Kur ndryshoni të dhënat në librin burimor, grupi tabelor nga libri i punës do t'i tërheqë ato automatikisht. Dhe nuk është aspak e nevojshme që të dy librat të jenë të hapur për këtë. Mjafton të hapet vetëm një libër pune dhe automatikisht do të tërheqë të dhënat nga dokumenti i mbyllur, nëse ndryshimet janë bërë më parë në të.

Por duhet theksuar se në këtë rast futja do të bëhet në formën e një grupi të pandryshueshëm. Nëse përpiqeni të ndërroni ndonjë qelizë me të dhëna të futura, do t'ju shfaqet një mesazh duke ju informuar se nuk është e mundur të bëhet kjo.

Ndryshimet në një grup të tillë që lidhen me një libër tjetër mund të bëhen vetëm duke thyer lidhjen.

Shkyçja midis tabelave

Ndonjëherë është e nevojshme për të thyer lidhjen midis vargjeve të tavolinës. Arsyeja për këtë mund të jetë, siç është rasti i përshkruar më sipër, kur doni të ndryshoni një grup të futur nga një libër tjetër, ose thjesht sepse përdoruesi nuk dëshiron që të dhënat në një tabelë të përditësohen automatikisht nga një tjetër.

Metoda 1: shkëputje midis librave

Ju mund ta thyeni lidhjen midis librave në të gjitha qelizat duke kryer praktikisht një operacion. Në të njëjtën kohë, të dhënat në qeliza do të mbeten, por ato tashmë do të jenë vlera statike jo të përditësuara që nuk varen nga dokumentet e tjera.

Në librin, në të cilin vlerat nga skedarët e tjerë tërhiqen, shkoni në skedën "Data". Kliko në ikonën "Ndrysho lidhjet"e cila gjendet në kasetë në bllokun e mjeteve "Connections". Duhet të theksohet se nëse libri aktual nuk përmban lidhje me skedarë të tjerë, ky buton nuk është aktiv.

Është hapur dritarja për ndërrimin e lidhjeve. Zgjidhni nga lista e librave të lidhura (nëse ka disa) skedari me të cilin ne dëshirojmë ta thyejmë lidhjen. Kliko në butonin "Pushoni lidhjen".

Hapet një dritare informacioni, në të cilën ka një paralajmërim për pasojat e veprimeve të mëtejshme. Nëse jeni të sigurt për atë që ju do të bëni, atëherë klikoni mbi butonin. "Pushim lidhjet".

Pas kësaj, të gjitha referencat në skedarin e specifikuar në dokumentin aktual do të zëvendësohen me vlera statike.

Metoda 2: Vendos vlerat

Por metoda e mësipërme është e përshtatshme vetëm nëse keni nevojë të hiqni plotësisht të gjitha lidhjet mes dy librave. Çfarë duhet të bëni nëse doni të shkëputni tabelat që janë në të njëjtin skedar? Këtë mund ta bëni duke kopjuar të dhënat, dhe pastaj duke e ngjitur atë në të njëjtin vend me vlerat.Nga rruga, e njëjta metodë mund të përdoret për të prishur lidhjen ndërmjet shkronjave të veçanta të të dhënave të librave të ndryshëm pa prishur lidhjen e përgjithshme midis skedarëve. Le të shohim se si funksionon kjo metodë në praktikë.

Zgjidhni diapazonin në të cilin duam të heqim lidhjen në një tabelë tjetër. Klikoni mbi të me butonin e djathtë të mausit. Në menynë që hapet, zgjidhni artikullin "Copy". Në vend të këtyre veprimeve, mund të shtypni një kombinim alternativ të çelësave të nxehtë. Ctrl + C.

Pastaj, pa hequr përzgjedhjen nga i njëjti fragment, ne përsëri klikoni mbi të me butonin e djathtë të mausit. Këtë herë në listën e veprimeve ne klikoni në ikonën "Vlerat"i cili është vendosur në një grup mjetesh "Opsionet e Futjes".

Pas kësaj, të gjitha lidhjet në rangun e përzgjedhur do të zëvendësohen me vlera statike.

Siç mund ta shihni, Excel ka metoda dhe mjete për të lidhur së bashku disa tabela. Në këtë rast, të dhënat tabelore mund të jenë në fletë të tjera dhe madje edhe në libra të ndryshëm. Nëse është e nevojshme, kjo lidhje mund të thyhet lehtësisht.