TABELAT E LIDHURA Në EXCEL

Send

Kur kryeni detyra të caktuara në Excel, ndonjëherë duhet të merreni me disa tabela, të cilat gjithashtu lidhen. Kjo do të thotë, të dhënat nga një tabelë tërhiqen në një tjetër dhe kur ato ndryshohen, vlerat rillogariten në të gjitha sferat e tabelave të lidhura.

Tabelat e lidhura janë shumë të përshtatshme për t'u përdorur për përpunimin e një sasie të madhe të informacionit. Për të vendosur të gjitha informacionet në një tabelë, përveç kësaj, nëse nuk është homogjene, nuk është shumë e përshtatshme. Shtë e vështirë të punosh me objekte të tilla dhe t'i kërkosh ato. Problemi i treguar është krijuar për të eleminuar nga tabela të lidhura, informacioni midis të cilave shpërndahet, por në të njëjtën kohë është i ndërlidhur. Seritë e tabelave të lidhura mund të vendosen jo vetëm brenda një flete të vetme ose një libri të vetëm, por gjithashtu mund të vendosen në libra (skedarë) të veçantë. Dy opsionet e fundit përdoren më shpesh në praktikë, pasi qëllimi i kësaj teknologjie është që të largohet nga grumbullimi i të dhënave, dhe grumbullimi i tyre në një faqe nuk e zgjidh thelbësisht problemin. 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ë ndalemi në pyetjen se cilat metoda ekziston një mundësi për të krijuar një marrëdhënie midis vargjeve të ndryshme të tabelave.

Metoda 1: drejtpërdrejt lidhni tabelat me një formulë

Mënyra më e lehtë për të lidhur të dhënat është përdorimi i formulave që referojnë diapazoni tjetër. Quhet detyruese direkte. Kjo metodë është intuitive, pasi që me të lidhja kryhet në pothuajse të njëjtën mënyrë si krijimi i lidhjeve me të dhënat në një grup tabelash.

Le të shohim se si, për shembull, një lidhje mund të formohet nga lidhja direkte. Kemi dy tabela në dy fletë. Në një tabelë, lista e pagave llogaritet duke përdorur formulën duke shumëzuar normën e punonjësve me një koeficient të vetëm për të gjithë.

Në fletën e dytë është një tabelë, e cila përmban një listë të punonjësve me pagat e tyre. Lista e punonjësve në të dy rastet paraqitet në të njëjtin rend.

Shtë e nevojshme të siguroheni që të dhënat për normat nga fleta e dytë të tërhiqen në qelizat përkatëse të së parës.

Në fletën e parë, zgjidhni qelizën e parë në kolonë "Bet". Ne vendosëm një shenjë në të "=". Tjetra, klikoni në shkurtore "Fleta 2", e cila është e vendosur në anën e majtë të ndërfaqes Excel sipër shiritit të statusit.

Kalohet në zonën e dytë të dokumentit. Ne klikojmë në qelizën e parë në kolonë "Bet". Pastaj klikoni në butonin hyj në tastierë për të futur të dhëna në qelizën në të cilën shenja ishte vendosur më parë "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ë tërhiqet në qelizën përkatëse. Duke vendosur kursorin në qelizë që përmban bastin, shohim që formula e zakonshme përdoret për të shfaqur të dhëna në ekran. Por para koordinatave të qelizës nga ku dalin të dhënat, ekziston një shprehje "Sheet2!", i cili tregon emrin e zonës së dokumentit ku janë vendosur. Formula e përgjithshme në rastin tonë duket si kjo:
= Fleta2! B2

Tani ju duhet të transferoni të dhënat mbi normat e të gjithë punonjësve të tjerë të ndërmarrjes. Sigurisht, kjo mund të bëhet në të njëjtën mënyrë që kemi kryer detyrën për punonjësin e parë, por duke pasur parasysh që të dy listat e punonjësve janë rregulluar në të njëjtën renditje, detyra mund të thjeshtohet dhe përshpejtohet shumë. Kjo mund të bëhet duke kopjuar thjesht formulën në rangun e mëposhtëm. Për shkak të faktit se lidhjet në Excel janë relativisht standarde, kur ato kopjohen, vlerat zhvendosen, gjë që është ajo që na nevojitet. Procedura e kopjimit në vetvete mund të bëhet duke përdorur shënuesin e mbushjes.
Pra, vendoseni kursorin në zonën e poshtme të djathtë të elementit me formulën. Pas kësaj, kursori duhet të shndërrohet në një shënues mbushës në formën e një kryqi të zi. Mbërthen butonin e majtë të miut dhe tërhiqeni kursorin në fund të kolonës.

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

Metoda 2: përdorimi i një grupi të operatorëve INDEX - KARRKONI

Por, nëse lista e të punësuarve në grupet e tabelave nuk është në të njëjtën mënyrë? Në këtë rast, siç u përmend më herët, një nga opsionet është të vendosni një lidhje midis secilës prej atyre qelizave që duhet të lidhen me dorë. Por kjo është e përshtatshme vetëm për tavolina të vogla. Për sferat masive, një opsion i tillë në rastin më të mirë do të marrë shumë kohë për t'u zbatuar, dhe në rastin më të keq, në praktikë nuk do të jetë i realizueshëm. Por ky problem mund të zgjidhet duke përdorur një bandë operatorësh INDEX - MATCH. Le të shohim se si kjo mund të bëhet duke lidhur të dhënat në rangun e tabelave që u diskutuan në metodën e mëparshme.

Zgjidhni elementin e parë të kolonës "Bet". Shko tek Wizard Featureduke klikuar në ikonën "Vendos funksionin".

Magjistar i funksionit në grup Referenca dhe vargje gjeni dhe nxjerrë në pah emrin "INDEX".

Ky operator ka dy forma: një formë për të punuar me vargje dhe një referencë. Në rastin tonë, kërkohet opsioni i parë, prandaj, në dritaren tjetër për zgjedhjen e formularit që hapet, zgjidhni atë dhe klikoni në butonin "OK".

U hap dritarja e argumenteve të operatorit INDEX. Detyra e këtij funksioni është të nxjerrë një vlerë që është në rangun e zgjedhur në përputhje me numrin e specifikuar. Formula e përgjithshme e operatorit INDEX e tillë është:
= INDEX (varg; rresht_number; [kolona_number])
"Array" - një argument që përmban adresën e diapazonit nga i cili do të nxjerrim informacione me numrin e rreshtit të specifikuar.
Numri i linjës - argumenti, i cili është numri i kësaj linje. Shtë e rëndësishme të dini se numri i linjës nuk duhet të specifikohet në lidhje me të gjithë dokumentin, por vetëm në lidhje me grupin e zgjedhur.
Numri i kolonës - një argument që është opsional. Ne nuk do ta përdorim atë për të zgjidhur problemin tonë specifik, dhe për këtë arsye nuk është e nevojshme ta përshkruajmë thelbin e saj veç e veç.
Vendoseni kursorin në fushë "Array". Pas kësaj, shkoni te Fleta 2 dhe duke mbajtur butonin e majtë të miut, zgjidhni të gjithë përmbajtjen e kolonës "Bet".

Pasi të shfaqen koordinatat në dritaren e operatorit, vendoseni kursorin në fushë Numri i linjës. Ne do ta nxjerrim këtë argument duke përdorur operatorin MATCH. Prandaj, ne klikojmë në trekëndëshin, i cili ndodhet në të majtë të linjës së funksionit. Hapet një listë e operatorëve të përdorur kohët e fundit. Nëse gjeni një emër midis tyre "Match"atëherë mund të klikoni mbi të. Përndryshe, klikoni në artikullin e fundit në listë - "Karakteristika të tjera ...".

Fillon dritarja standarde Magjistarët e funksionit. Ne i kalojmë asaj në të njëjtin grup Referenca dhe vargje. Këtë herë, zgjidhni artikullin në listë "Match". Klikoni në butonin. "OK".

Aktivizohet dritarja e argumenteve të 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 veçorie, ne do të llogarisim numrin e linjës së një vlere të caktuar për funksionin INDEX. sintaksë MATCH e përfaqësuar si më poshtë:
= KARRKONI (search_value; lookup_array; [match_type])
"Duke kërkuar vlerë" - një argument që përmban emrin ose adresën e qelizës së intervalit të palëve të treta në të cilën ndodhet. Theshtë pozicioni i këtij emri në rangun e synuar që duhet të llogaritet. Në rastin tonë, argumenti i parë do të jetë referenca për qelizat në vazhdim Fleta 1ku janë vendosur emrat e punonjësve.
Shikuar vargun - një argument që përfaqëson një referencë për një grup në të cilin kërkohet vlera e specifikuar për të përcaktuar pozicionin e saj. Adresa e kolonës "do të luajë këtë rol këtu."Emri në Fleta 2.
Lloji i ndeshjes - një argument, i cili është opsional, por, ndryshe nga deklarata e mëparshme, do të na duhet ky argument opsional. Ai tregon se si operatori do të përputhet me vlerën e kërkimit me grupin. Ky argument mund të ketë një nga tre vlerat: -1; 0; 1. Për grupe të pakontrolluara, zgjidhni "0". Ky opsion është i përshtatshëm për rastin tonë.
Pra, le të fillojmë të plotësojmë fushat e dritares së argumenteve. Vendoseni kursorin në fushë "Duke kërkuar vlerë"klikoni në qelizën e parë të kolonës "Emri" në Fleta 1.

Pasi të shfaqen koordinatat, vendoseni kursorin në fushë Shikuar vargun dhe klikoni në shkurtore "Fleta 2", e cila është e vendosur në fund të dritares Excel sipër shiritit të statusit. Mbajeni butonin e majtë të miut dhe zgjidhni të gjitha qelizat në kolonë me kursorin "Emri".

Pasi koordinatat e tyre shfaqen në fushë Shikuar vargunshkoni në fushë Lloji i ndeshjes dhe vendosni numrin atje nga tastiera "0". Pas kësaj, kthehemi përsëri në fushë Shikuar vargun. Fakti është që ne do të kopjojmë formulën, siç kemi bërë në metodën e mëparshme. Ndryshimi i adresës do të ndodhë, por këtu duhet të rregullojmë koordinatat e grupit që po shikohen. Nuk duhet të zhvendoset. Zgjidhni koordinatat me kursorin dhe shtypni butonin e funksionit F4. Siç mund ta shihni, shenja e dollarit u shfaq para koordinatave, që do të thotë se lidhja është kthyer nga relative në absolute. Pastaj klikoni në butonin "OK".

Rezultati shfaqet në qelizën e parë të kolonës. "Bet". Por para kopjimit, duhet të rregullojmë një zonë tjetër, domethënë argumentin e parë të funksionit INDEX. Për ta bërë këtë, zgjidhni elementin e kolonës që përmban formulën dhe lëvizni në vijën e formulave. Ne zgjedhim argumentin e parë të operatorit INDEX (B2: B7) dhe klikoni në butonin F4. Siç mund ta shihni, shenja e dollarit u shfaq pranë koordinatave të zgjedhura. Klikoni në butonin hyj. Në përgjithësi, formula ka marrë formën e mëposhtme:
= INDEX (Sheet2! $ B $ 2: $ B $ 7; K SERKONI (Sheet1! A4; Sheet2! $ A $ 2: $ A 7 $; 0))

Tani mund të kopjoni duke përdorur shënuesin e mbushjes. Ne e quajmë atë në të njëjtën mënyrë për të cilën kemi biseduar më herët, dhe e shtrijmë atë deri në fund të diapazonit.

Siç mund ta shihni, përkundër faktit se renditja e radhës e dy tabelave të lidhura nuk përkon, megjithatë, të gjitha vlerat tërhiqen sipas emrave të punonjësve. Kjo u arrit përmes përdorimit të një kombinimi të operatorëve INDEX-MATCH.

Lexoni gjithashtu:
Funksioni EXEX në Excel
Funksioni EXCEL në Excel

Metoda 3: kryejnë operacione matematikore me të dhëna përkatëse

Lidhja direkte e të dhënave është gjithashtu e mirë sepse ju lejon të tregoni jo vetëm vlera që shfaqen në rangje të tabelave të tjera në njërën prej tabelave, por edhe të kryeni operacione të ndryshme matematikore me to (shtesë, ndarje, zbritje, shumëzim, etj.).

Le të shohim se si zbatohet kjo në praktikë. Le ta bëjmë këtë Fleta 3 të dhënat e pagave të përgjithshme për kompaninë do të shfaqen pa ndarje nga punonjësit. Për ta bërë këtë, nivelet e punonjësve do të tërhiqen nga Fleta 2, përmbledhur (duke përdorur funksionin SUM) dhe shumohen me një koeficient duke përdorur formulën.

Zgjidhni qelizën ku do të shfaqet rezultati i llogaritjes së pagave. Fleta 3. Klikoni në butonin. "Vendos funksionin".

Dritarja duhet të fillojë Magjistarët e funksionit. Shkoni në grup "Matematike" dhe zgjidhni emrin atje "SUM". Tjetra, klikoni në butonin "OK".

Argumentet e funksionit zhvendosen në dritare SUM, i cili është krijuar për të llogaritur shumën e numrave të zgjedhur. Ajo ka sintaksën e mëposhtme:
= SUM (numri 1; numri2; ...)
Fushat në dritare korrespondojnë me argumentet e funksionit të specifikuar. Edhe pse numri i tyre mund të arrijë në 255, vetëm një do të jetë i mjaftueshëm për qëllimin tonë. Vendoseni kursorin në fushë "Number 1". Klikoni në shkurtore "Fleta 2" sipër shiritit të statusit.

Pasi të kemi kaluar në pjesën e dëshiruar të librit, zgjidhni kolonën që duhet të përmblidhet. Ne e bëjmë këtë me kursorin ndërsa mban butonin e majtë të miut. Siç mund ta shihni, koordinatat e zonës së zgjedhur shfaqen menjëherë në fushën e dritares së argumenteve. Pastaj klikoni në butonin "OK".

Pas kësaj, ne automatikisht kalojmë në Fleta 1. Siç mund ta shihni, shuma e përgjithshme e ofertave të punonjësve është shfaqur tashmë në elementin përkatës.

Por nuk janë të gjitha. Siç e kujtojmë, paga llogaritet duke shumëzuar vlerën e normës me një faktor. Prandaj, ne përsëri zgjedhim qelizën në të cilën ndodhet vlera e përmbledhur. Pas kësaj ne kalojmë në vijën e formulave. Shtoni në formulë në të një shenjë shumëzimi (*), dhe pastaj kliko në elementin në të cilin është vendosur treguesi i koeficientit. Për të kryer llogaritjen, klikoni në butonin hyj në tastierë. Siç mund ta shihni, programi llogarit pagën totale për ndërmarrjen.

Kthehu tek Fleta 2 dhe ndryshoni shkallën e çdo punonjësi.

Pas kësaj, ne përsëri kalojmë në faqe me shumën totale. Siç mund ta shihni, për shkak të ndryshimeve në tabelën e lidhur, rezultati i pagës totale u llogarit në mënyrë automatike.

Metoda 4: insert personal

Ju gjithashtu mund të lidhni vargjeve të tabelave në Excel duke përdorur një futje të veçantë.

Ne zgjedhim vlerat që do të duhet të "tërhiqen" në një tabelë tjetër. Në rastin tonë, kjo është diapazoni i kolonës "Bet" në Fleta 2. Ne klikojmë në fragmentin e zgjedhur me butonin e djathtë të miut. Në listën që hapet, zgjidhni "Copy". Një shkurtore alternative e tastierës është Ctrl + C. Pas kësaj ne kalojmë në Fleta 1.

Pasi të kemi kaluar në zonën e librit që na nevojitet, zgjedhim qelizat në të cilat do të na duhen për të tërhequr vlerat. Në rastin tonë, kjo është një kolonë "Bet". Ne klikojmë në fragmentin e zgjedhur me butonin e djathtë të miut. Në menynë e kontekstit në bllokun e mjeteve Fut opsione klikoni në ikonën Paste Link.
Ekziston edhe një alternative. Rastësisht, është i vetmi për versionet më të vjetra të Excel. Në menunë e kontekstit, rri pezull "Futje speciale". Në menunë shtesë që hapet, zgjidhni pozicionin me të njëjtin emër.

Pas kësaj, hapet dritarja speciale e futjes. Klikoni në butonin Paste Link në këndin e poshtëm të majtë të qelizës.

Cilindo mundësi të zgjidhni, vlerat nga një grup tabelash do të futen në një tjetër. Kur ndryshoni të dhënat në burim, ato gjithashtu do të ndryshojnë automatikisht në rangun e futur.

Mësimi: Futje speciale në Excel

Metoda 5: lidhje midis tabelave në libra të shumtë

Përveç kësaj, ju mund të organizoni komunikimin midis zonave të tabelave në libra të ndryshëm. Përdoret një mjet i posaçëm për futje. Veprimet do të jenë absolutisht të ngjashme me ato që kemi konsideruar në metodën e mëparshme, përveç që do t'ju duhet të lundroni ndërsa bëni formula jo midis zonave të të njëjtit libër, por midis skedarëve. Natyrisht, të gjithë librat e lidhur duhet të jenë të hapura.

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

Pastaj ne kalojmë në librin në të cilin këto të dhëna do të duhet të futen. Zgjidhni gamën e dëshiruar. Klikoni me të djathtën. Në menynë e kontekstit në grup Fut opsione zgjidhni artikullin Paste Link.

Pas kësaj, vlerat do të futen. Kur të dhënat në fletoren e punës ndryshojnë, grupi i tabelës nga libri i punës automatikisht i tërheq ato. Për më tepër, nuk është e nevojshme që të dy librat të jenë të hapur për këtë. Shtë e mjaftueshme për të hapur vetëm një libër pune, dhe automatikisht do të tërheq të dhënat nga një dokument i lidhur i mbyllur nëse janë bërë ndryshime në të më parë.

Por duhet të theksohet se në këtë rast futja do të bëhet si një grup i pandryshueshëm. Kur përpiqeni të ndryshoni ndonjë qelizë me të dhënat e futura, një mesazh shfaqet duke ju informuar se është e pamundur ta bëni këtë.

Ndryshimet në një grup të tillë të lidhur me një libër tjetër mund të bëhen vetëm duke prishur lidhjen.

Boshllëku midis tabelave

Ndonjëherë ju duhet të prishni lidhjen midis vargjeve të tabelave. Arsyeja për këtë mund të jetë ose rasti i përshkruar më sipër, kur duhet të ndryshoni një koleksion të futur nga një libër tjetër, ose thjesht ngurrimi i përdoruesit që të dhënat në një tabelë azhurnohen automatikisht nga një tjetër.

Metoda 1: prishja e lidhjes midis librave

Ju mund të prishni 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ë azhurnueshme, të cilat në asnjë mënyrë nuk varen nga dokumentet e tjera.

Në libër, në të cilin tërhiqen vlerat nga skedarët e tjerë, shkoni te skedari "Data". Klikoni në ikonën "Ndrysho komunikimet"vendosur në shiritin në kutinë e veglave "Connections". Duhet të theksohet se nëse libri aktual nuk përmban lidhje me skedarët e tjerë, atëherë ky buton është joaktiv.

Fillon dritarja e ndryshimeve të lidhjes. Ne zgjedhim skedarin me të cilin duam të prishim lidhjen nga lista e librave të lidhur (nëse ka disa). Klikoni në butonin Thyej lidhjen.

Hapet një dritare informacioni, në të cilën ekziston një paralajmërim për pasojat e veprimeve të mëtejshme. Nëse jeni të sigurt se çfarë do të bëni, atëherë klikoni në butonin "Thyerje e lidhjeve".

Pas kësaj, të gjitha lidhjet me 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ë prisheni plotësisht të gjitha lidhjet midis dy librave. Tofarë duhet të bëni nëse duhet të veçoni tabela të lidhura që janë brenda të njëjtit skedar? Ju mund ta bëni këtë duke kopjuar të dhënat dhe pastaj duke i ngjitur në të njëjtin vend me vlerat. Nga rruga, në të njëjtën mënyrë, ju mund të prishni lidhjen midis vargjeve të të dhënave individuale 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 gamën në të cilën ne duam të heqim lidhjen në një tabelë tjetër. Ne klikojmë atë me butonin e djathtë të miut. Në menunë që hapet, zgjidhni "Copy". Në vend të këtyre veprimeve, mund të shkruani një kombinim alternative të çelësave të nxehtë Ctrl + C.

Më tej, pa hequr zgjedhjen nga i njëjti fragment, përsëri kliko me të djathtën mbi të. Këtë herë në listën e veprimeve, klikoni në ikonën "Vlerat"i cili ndodhet në grupin e mjeteve Fut opsione.

Pas kësaj, të gjitha lidhjet në gamën e zgjedhur do të zëvendësohen me vlera statike.

Siç mund ta shihni, në Excel ka mënyra dhe mjete për të lidhur disa tabela së bashku. Në të njëjtën kohë, të dhënat tabelare mund të jenë në fletë të tjera dhe madje edhe në libra të ndryshëm. Nëse është e nevojshme, kjo lidhje mund të prishet lehtë.

Send