Përmbajtje
Komunikimi është një veçori shumë e dobishme në Excel. Në fund të fundit, shumë shpesh përdoruesit duhet të përdorin informacione nga skedarë të tjerë. Por në disa situata, ato mund të bëjnë më shumë dëm sesa mirë. Në fund të fundit, për shembull, nëse i dërgoni këto skedarë me postë, lidhjet nuk funksionojnë. Sot do të flasim më në detaje se çfarë duhet të bëni për të shmangur një problem të tillë.
Cilat janë marrëdhëniet në Excel
Marrëdhëniet në Excel përdoren shumë shpesh në lidhje me funksione të tilla si VPRpër të marrë informacion nga një libër tjetër pune. Mund të marrë formën e një lidhjeje të veçantë që përmban adresën jo vetëm të qelizës, por edhe të librit në të cilin ndodhen të dhënat. Si rezultat, një lidhje e tillë duket diçka si kjo: =VLOOKUP(A2;'[Shitjet 2018.xlsx]Raporto'!$A:$F;4;0). Ose, për një paraqitje më të thjeshtë, përfaqësoni adresën në formën e mëposhtme: ='[Shitjet 2018.xlsx]Raporto'!$A1. Le të analizojmë secilin nga elementët e lidhjeve të këtij lloji:
- [Shitjet 2018.xlsx]. Ky fragment përmban një lidhje me skedarin nga i cili dëshironi të merrni informacion. Quhet edhe burimi.
- Fotografitë. Ne kemi përdorur emrin e mëposhtëm, por ky nuk është emri që duhet të jetë. Ky bllok përmban emrin e fletës në të cilën ju duhet të gjeni informacion.
- $A:$F dhe $A1 - adresa e një qelize ose diapazoni që përmban të dhëna që përmbahen në këtë dokument.
Në fakt, procesi i krijimit të një lidhjeje me një dokument të jashtëm quhet lidhje. Pasi të kemi regjistruar adresën e qelizës që gjendet në një skedar tjetër, përmbajtja e skedës "Data" ndryshon. Gjegjësisht, aktivizohet butoni “Ndrysho lidhjet”, me ndihmën e të cilit përdoruesi mund të modifikojë lidhjet ekzistuese.
Thelbi i problemit
Si rregull, nuk lindin vështirësi shtesë për të përdorur lidhjet. Edhe nëse lind një situatë në të cilën qelizat ndryshojnë, atëherë të gjitha lidhjet përditësohen automatikisht. Por nëse tashmë e riemërtoni vetë librin e punës ose e zhvendosni në një adresë tjetër, Excel bëhet i pafuqishëm. Prandaj, ai prodhon mesazhin e mëposhtëm.
Këtu, përdoruesi ka dy opsione të mundshme se si të veprojë në këtë situatë. Ai mund të klikojë “Vazhdo” dhe më pas ndryshimet nuk do të përditësohen, ose mund të klikojë butonin “Ndrysho shoqatat”, me të cilin mund t'i përditësojë ato manualisht. Pasi të klikojmë këtë buton, do të shfaqet një dritare shtesë në të cilën do të jetë e mundur të ndryshohen lidhjet, duke treguar se ku ndodhet skedari i saktë në këtë moment dhe si quhet.
Përveç kësaj, ju mund të modifikoni lidhjet përmes butonit përkatës të vendosur në skedën "Të dhënat". Përdoruesi gjithashtu mund të zbulojë se lidhja është prishur nga gabimi #LINK, i cili shfaqet kur Excel nuk mund të aksesojë informacionin e vendosur në një adresë specifike për shkak të faktit se vetë adresa është e pavlefshme.
Si të shkëputeni në excel
Një nga metodat më të thjeshta për të zgjidhur situatën e përshkruar më sipër në rast se nuk mund të përditësoni vetë vendndodhjen e skedarit të lidhur është të fshini vetë lidhjen. Kjo është veçanërisht e lehtë për t'u bërë nëse dokumenti përmban vetëm një lidhje. Për ta bërë këtë, duhet të kryeni sekuencën e mëposhtme të hapave:
- Hapni menunë "Të dhënat".
- Ne gjejmë seksionin "Lidhjet" dhe atje - opsionin "Ndrysho lidhjet".
- Pas kësaj, klikoni në "Shkëputje".
Nëse keni ndërmend t'ia dërgoni këtë libër një personi tjetër, rekomandohet që ta bëni këtë paraprakisht. Në fund të fundit, pas fshirjes së lidhjeve, të gjitha vlerat që përmbahen në një dokument tjetër do të ngarkohen automatikisht në skedar, të përdorur në formula, dhe në vend të adresës së qelizës, informacioni në qelizat përkatëse thjesht do të shndërrohet në vlera .
Si të shkëputni të gjithë librat
Por nëse numri i lidhjeve bëhet shumë i madh, fshirja manuale e tyre mund të marrë shumë kohë. Për të zgjidhur këtë problem me një lëvizje, mund të përdorni një makro të veçantë. Është në shtesën VBA-Excel. Duhet ta aktivizoni dhe të shkoni te skeda me të njëjtin emër. Do të ketë një seksion "Lidhje", në të cilin duhet të klikojmë në butonin "Të thyej të gjitha lidhjet".
Kodi VBA
Nëse nuk është e mundur të aktivizoni këtë shtesë, mund të krijoni vetë një makro. Për ta bërë këtë, hapni redaktorin e Visual Basic duke shtypur tastet Alt + F11 dhe shkruani rreshtat e mëposhtëm në fushën e futjes së kodit.
Nën UnlinkWorkBooks()
Wblinks të errëta
E zbehtë dhe për aq kohë sa
Zgjidhni Case MsgBox("Të gjitha referencat për librat e tjerë do të hiqen nga ky skedar dhe formulat që u referohen librave të tjerë do të zëvendësohen me vlera." & vbCrLf & "A jeni i sigurt që dëshironi të vazhdoni?", 36, "Shkëpute?" )
Rasti 7′ Nr
Dalja Nën
Fund Zgjidh
WbLinks = ActiveWorkbook.LinkSources(Lloji:=xlLinkTypeExcelLinks)
Nëse nuk është bosh (WbLinks) Atëherë
Për i = 1 në UBound (WbLinks)
ActiveWorkbook.BreakLink Emri:=WbLinks(i), Lloji:=xlLinkTypeExcelLinks
Tjetër
tjetër
MsgBox "Nuk ka lidhje me libra të tjerë në këtë skedar.", 64, "Lidhje me libra të tjerë"
Nëse End
Sub End
Si të prishni lidhjet vetëm në gamën e zgjedhur
Herë pas here, numri i lidhjeve është shumë i madh dhe përdoruesi ka frikë se pasi të fshijë njërën prej tyre, nuk do të jetë e mundur të kthejë gjithçka nëse disa ishin të tepërta. Por ky është një problem që është i lehtë për t'u shmangur. Për ta bërë këtë, duhet të zgjidhni gamën në të cilën do të fshini lidhjet dhe më pas t'i fshini ato. Për ta bërë këtë, duhet të kryeni sekuencën e mëposhtme të veprimeve:
- Zgjidhni grupin e të dhënave që duhet modifikuar.
- Instaloni shtesën VBA-Excel dhe më pas shkoni te skeda e duhur.
- Tjetra, gjejmë menunë "Lidhjet" dhe klikojmë në butonin "Të thyejmë lidhjet në intervalet e zgjedhura".
Pas kësaj, të gjitha lidhjet në grupin e zgjedhur të qelizave do të fshihen.
Çfarë duhet të bëni nëse lidhjet nuk prishen
Të gjitha sa më sipër tingëllojnë mirë, por në praktikë ka gjithmonë disa nuanca. Për shembull, mund të ketë një situatë ku lidhjet nuk prishen. Në këtë rast, ende shfaqet një kuti dialogu që thotë se nuk është e mundur të përditësohen automatikisht lidhjet. Çfarë duhet bërë në këtë situatë?
- Së pari, duhet të kontrolloni nëse ndonjë informacion gjendet në intervalet e emërtuara. Për ta bërë këtë, shtypni kombinimin e tastit Ctrl + F3 ose hapni skedën "Formulat" - "Menaxheri i emrit". Nëse emri i skedarit është i plotë, atëherë thjesht duhet ta modifikoni ose ta hiqni fare. Përpara se të fshini vargjet e emërtuara, duhet ta kopjoni skedarin në një vend tjetër në mënyrë që të mund të ktheheni në versionin origjinal nëse janë ndërmarrë hapat e gabuar.
- Nëse nuk mund ta zgjidhni problemin duke hequr emrat, mund të kontrolloni formatimin e kushtëzuar. Qelizat në një tabelë tjetër mund të referohen në rregullat e formatimit të kushtëzuar. Për ta bërë këtë, gjeni artikullin përkatës në skedën "Home" dhe më pas klikoni në butonin "Menaxhimi i skedarit".
Normalisht, Excel nuk ju jep mundësinë për të dhënë adresën e librave të tjerë të punës në formatimin e kushtëzuar, por ju e bëni nëse i referoheni një diapazoni të emërtuar me një referencë në një skedar tjetër. Zakonisht, edhe pasi të hiqet lidhja, lidhja mbetet. Nuk ka asnjë problem për të hequr një lidhje të tillë, sepse lidhja në fakt nuk funksionon. Prandaj, asgjë e keqe nuk do të ndodhë nëse e hiqni atë.
Ju gjithashtu mund të përdorni funksionin "Kontrolli i të dhënave" për të zbuluar nëse ka ndonjë lidhje të panevojshme. Lidhjet zakonisht mbeten nëse përdoret lloji "Lista" i vërtetimit të të dhënave. Por çfarë të bëni nëse ka shumë qeliza? A është vërtet e nevojshme të kontrolloni secilën prej tyre në mënyrë sekuenciale? Sigurisht që jo. Në fund të fundit, do të duhet një kohë shumë e gjatë. Prandaj, duhet të përdorni një kod të veçantë për ta ruajtur ndjeshëm atë.
Opsioni i qartë
'——————————————————————————————
Autori: The_Prist (Shcherbakov Dmitry)
Zhvillimi profesional i aplikacioneve për MS Office të çdo kompleksiteti
' Kryerja e trajnimeve për MS Excel
' https://www.excel-vba.ru
' [email i mbrojtur]
'WebMoney-R298726502453; Yandex.Money - 41001332272872
'Qëllimi:
'——————————————————————————————
Nën FindErrLink()
'Ne duhet të shikojmë në lidhjen Data -Change links me skedarin burimor
'dhe vendosni fjalët kyçe këtu me shkronja të vogla (pjesë e emrit të skedarit)
'ylli thjesht zëvendëson çdo numër karakteresh, kështu që nuk duhet të shqetësoheni për emrin e saktë
Const sToFndLink$ = "*shitjet 2018*"
Dim rr As Range, rc As Range, rres As Range, s$
'përcaktoni të gjitha qelizat me vërtetimin e të dhënave
Në Error Resume Next
Cakto rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
Nëse rr nuk është asgjë atëherë
MsgBox "Nuk ka qeliza me vërtetim të të dhënave në fletën aktive", vbInformation, "www.excel-vba.ru"
Dalja Nën
Nëse End
Në gabim GoTo 0
'kontrollo çdo qelizë për lidhje
Për çdo rc Në rr
"Vetëm në rast, ne i anashkalojmë gabimet - kjo gjithashtu mund të ndodhë
“Por lidhjet tona duhet të jenë pa to dhe do të gjenden patjetër
s = «»
Në Error Resume Next
s = rc.Vleresimi.Formula1
Në gabim GoTo 0
'gjetur - ne mbledhim gjithçka në një gamë të veçantë
Nëse LCase(s) Like sToFndLink Pastaj
Nëse rres nuk është asgjë atëherë
Vendosni RRES = RC
tjetër
Set rres = Bashkimi (rc, rres)
Nëse End
Nëse End
Tjetër
'nëse ka një lidhje, zgjidhni të gjitha qelizat me kontrolle të tilla të dhënash
Nëse Jo rres Është Asgjë Atëherë
rres.Zgjidh
' rres.Interior.Color = vbRed 'nëse doni të theksoni me ngjyra
Nëse End
Sub End
Është e nevojshme të krijoni një modul standard në redaktuesin makro, dhe më pas të futni këtë tekst atje. Pas kësaj, thirrni dritaren e makro duke përdorur kombinimin e tastit Alt + F8, dhe më pas zgjidhni makronë tonë dhe klikoni në butonin "Run". Ka disa gjëra që duhen mbajtur parasysh kur përdorni këtë kod:
- Përpara se të kërkoni për një lidhje që nuk është më relevante, së pari duhet të përcaktoni se si duket lidhja përmes së cilës është krijuar. Për ta bërë këtë, shkoni te menyja "Të dhënat" dhe gjeni artikullin "Ndrysho lidhjet" atje. Pas kësaj, duhet të shikoni emrin e skedarit dhe ta specifikoni atë në thonjëza. Për shembull, si kjo: Const sToFndLink$ = "*shitjet 2018*"
- Është e mundur të shkruani emrin jo plotësisht, por thjesht të zëvendësoni karakteret e panevojshme me një yll. Dhe në thonjëza, shkruani emrin e skedarit me shkronja të vogla. Në këtë rast, Excel do të gjejë të gjithë skedarët që përmbajnë një varg të tillë në fund.
- Ky kod mund të kontrollojë vetëm lidhjet në fletën që është aktualisht aktive.
- Me këtë makro, ju mund të zgjidhni vetëm qelizat që ka gjetur. Ju duhet të fshini gjithçka me dorë. Ky është një plus, sepse mund të kontrolloni përsëri gjithçka.
- Ju gjithashtu mund t'i bëni qelizat e theksuara me një ngjyrë të veçantë. Për ta bërë këtë, hiqni apostrofin përpara kësaj rreshti. rres.Interior.Ngjyra = vbE kuqe
Zakonisht, pasi të keni përfunduar hapat e përshkruar në udhëzimet e mësipërme, nuk duhet të ketë më lidhje të panevojshme. Por nëse ka disa prej tyre në dokument dhe nuk jeni në gjendje t'i hiqni ato për një arsye ose një tjetër (një shembull tipik është siguria e të dhënave në një fletë), atëherë mund të përdorni një sekuencë të ndryshme veprimesh. Ky udhëzim është i vlefshëm vetëm për versionet 2007 e më lart.
- Ne krijojmë një kopje rezervë të dokumentit.
- Hapeni këtë dokument duke përdorur arkivuesin. Mund të përdorni cilindo që mbështet formatin ZIP, por WinRar do të funksionojë gjithashtu, si dhe atë të integruar në Windows.
- Në arkivin që shfaqet, duhet të gjeni dosjen xl dhe më pas të hapni Lidhjet e jashtme.
- Kjo dosje përmban të gjitha lidhjet e jashtme, secila prej të cilave korrespondon me një skedar të formës externalLink1.xml. Të gjithë ata janë vetëm të numëruar, dhe për këtë arsye përdoruesi nuk ka mundësinë të kuptojë se çfarë lloj lidhjeje është kjo. Për të kuptuar se çfarë lloj lidhjeje, duhet të hapni dosjen _rels dhe ta shikoni atje.
- Pas kësaj, ne heqim të gjitha ose lidhjet specifike, bazuar në atë që mësojmë në skedarin eternalLinkX.xml.rels.
- Pas kësaj, ne hapim skedarin tonë duke përdorur Excel. Do të ketë informacione për një gabim si "Gabim në një pjesë të përmbajtjes në Libër". Ne japim pëlqimin. Pas kësaj, do të shfaqet një dialog tjetër. E mbyllim.
Pas kësaj, të gjitha lidhjet duhet të hiqen.