Contents [show]
Kommunikasie is 'n baie nuttige kenmerk in Excel. Gebruikers moet immers baie dikwels inligting van ander lêers gebruik. Maar in sommige situasies kan hulle meer skade doen as goed. Na alles, byvoorbeeld, as jy hierdie lêers per pos stuur, werk die skakels nie. Vandag sal ons in meer besonderhede praat oor wat om te doen om so 'n probleem te vermy.
Wat is verhoudings in Excel
Verhoudings in Excel word baie dikwels gebruik in samewerking met funksies soos VPRom inligting uit 'n ander werkboek te kry. Dit kan die vorm aanneem van 'n spesiale skakel wat die adres van nie net die sel bevat nie, maar ook die boek waarin die data geleë is. Gevolglik lyk so 'n skakel iets soos volg: =VLOOKUP(A2;'[Verkope 2018.xlsx]Verslag'!$A:$F;4;0). Of, vir 'n eenvoudiger voorstelling, stel die adres in die volgende vorm voor: ='[Verkope 2018.xlsx]Verslag'!$A1. Kom ons ontleed elk van die skakelelemente van hierdie tipe:
- [Verkope 2018.xlsx]. Hierdie fragment bevat 'n skakel na die lêer waaruit jy inligting wil kry. Dit word ook die bron genoem.
- Fotos. Ons het die volgende naam gebruik, maar dit is nie die naam wat moet wees nie. Hierdie blok bevat die naam van die blad waarin jy inligting moet kry.
- $A:$F en $A1 – die adres van 'n sel of reeks wat data bevat wat in hierdie dokument vervat is.
Eintlik word die proses om 'n skakel na 'n eksterne dokument te skep koppeling genoem. Nadat ons die adres van die sel in 'n ander lêer geregistreer het, verander die inhoud van die "Data"-oortjie. Die “Verander verbindings”-knoppie word naamlik aktief, met behulp waarvan die gebruiker die bestaande verbindings kan wysig.
Die essensie van die probleem
As 'n reël ontstaan geen bykomende probleme om skakels te gebruik nie. Selfs as 'n situasie ontstaan waarin die selle verander, dan word alle skakels outomaties opgedateer. Maar as jy reeds die werkboek self hernoem of dit na 'n ander adres skuif, word Excel kragteloos. Daarom lewer dit die volgende boodskap.
Hier het die gebruiker twee moontlike opsies vir hoe om in hierdie situasie op te tree. Hy kan op “Gaan voort” klik en dan sal die veranderinge nie opgedateer word nie, of hy kan op die “Verander verenigings”-knoppie klik, waarmee hy dit handmatig kan opdateer. Nadat ons op hierdie knoppie geklik het, sal 'n bykomende venster verskyn waarin dit moontlik sal wees om die skakels te verander, wat aandui waar die korrekte lêer op die oomblik geleë is en wat dit genoem word.
Daarbenewens kan u skakels wysig deur die ooreenstemmende knoppie op die "Data"-oortjie. Die gebruiker kan ook uitvind dat die verbinding verbreek word deur die #LINK-fout, wat verskyn wanneer Excel nie toegang tot inligting by 'n spesifieke adres kan kry nie, omdat die adres self ongeldig is.
Hoe om te ontkoppel in Excel
Een van die eenvoudigste metodes om die situasie hierbo beskryf op te los in die geval dat jy nie self die ligging van die gekoppelde lêer kan opdateer nie, is om die skakel self uit te vee. Dit is veral maklik om te doen as die dokument slegs een skakel bevat. Om dit te doen, moet u die volgende volgorde van stappe uitvoer:
- Maak die "Data"-kieslys oop.
- Ons vind die afdeling "Verbindings", en daar - die opsie "Verander verbindings".
- Klik daarna op "Ontkoppel".
As jy van plan is om hierdie boek na 'n ander persoon te pos, word dit sterk aanbeveel dat jy dit vooraf doen. Na alles, na die verwydering van die skakels, sal al die waardes wat in 'n ander dokument vervat is, outomaties in die lêer gelaai word, wat in formules gebruik word, en in plaas van die seladres, sal die inligting in die ooreenstemmende selle eenvoudig in waardes omskep word .
Hoe om alle boeke te ontkoppel
Maar as die aantal skakels te groot word, kan dit lank neem om dit met die hand uit te vee. Om hierdie probleem in een slag op te los, kan jy 'n spesiale makro gebruik. Dit is in die VBA-Excel-byvoeging. Jy moet dit aktiveer en na die oortjie met dieselfde naam gaan. Daar sal 'n "Skakels"-afdeling wees, waarin ons op die "Breek alle skakels"-knoppie moet klik.
VBA kode
As dit nie moontlik is om hierdie byvoeging te aktiveer nie, kan jy self 'n makro skep. Om dit te doen, maak die Visual Basic-redigeerder oop deur die Alt + F11-sleutels te druk en skryf die volgende reëls in die kode-invoerveld.
Sub UnlinkWorkBooks()
Dim WbLinks
Dim en Solank
Kies Case MsgBox(“Alle verwysings na ander boeke sal uit hierdie lêer verwyder word, en formules wat na ander boeke verwys, sal met waardes vervang word.” & vbCrLf & “Is jy seker jy wil voortgaan?”, 36, “Ontkoppel?” )
Geval 7′ No
Verlaat sub
Einde kies
WbLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
As dit nie leeg is nie (WbLinks) dan
Vir i = 1 Om UBound (WbLinks)
ActiveWorkbook.BreakLink Naam:=WbLinks(i), Tipe:=xlLinkTypeExcelLinks
volgende
anders
MsgBox "Daar is geen skakels na ander boeke in hierdie lêer nie.", 64, "Skakels na ander boeke"
Einde as
End Sub
Hoe om bande slegs in die geselekteerde reeks te breek
Van tyd tot tyd is die aantal skakels baie groot, en die gebruiker is bang dat dit nie moontlik sal wees om alles terug te stuur as sommige oorbodig was nadat een van hulle uitgevee is nie. Maar dit is 'n probleem wat maklik is om te vermy. Om dit te doen, moet jy die reeks kies waarin jy skakels wil uitvee, en dit dan uitvee. Om dit te doen, moet jy die volgende volgorde van aksies uitvoer:
- Kies die datastel wat gewysig moet word.
- Installeer die VBA-Excel-byvoeging en gaan dan na die toepaslike oortjie.
- Vervolgens vind ons die "Skakels"-kieslys en klik op die "Verbreek skakels in die geselekteerde reekse"-knoppie.
Daarna sal alle skakels in die geselekteerde stel selle uitgevee word.
Wat om te doen as die bande nie gebreek is nie
Al die bogenoemde klink goed, maar in die praktyk is daar altyd 'n paar nuanses. Daar kan byvoorbeeld 'n situasie wees waar bande nie verbreek word nie. In hierdie geval verskyn 'n dialoogkassie wat sê dat dit nie moontlik is om die skakels outomaties op te dateer nie. Wat om te doen in hierdie situasie?
- Eerstens moet jy kyk of enige inligting in die genoemde reekse vervat is. Om dit te doen, druk die sleutelkombinasie Ctrl + F3 of maak die "Formules"-oortjie oop - "Naambestuurder". As die lêernaam vol is, moet jy dit net wysig of heeltemal verwyder. Voordat u benoemde reekse uitvee, moet u die lêer na 'n ander plek kopieer sodat u na die oorspronklike weergawe kan terugkeer as die verkeerde stappe gedoen is.
- As jy nie die probleem kan oplos deur name te verwyder nie, kan jy voorwaardelike formatering nagaan. Daar kan na selle in 'n ander tabel verwys word in voorwaardelike formateringreëls. Om dit te doen, vind die ooreenstemmende item op die "Tuis"-oortjie en klik dan op die "Lêerbestuur"-knoppie.
Normaalweg gee Excel jou nie die vermoë om die adres van ander werkboeke in voorwaardelike formatering te gee nie, maar jy doen dit as jy na 'n benoemde reeks verwys met 'n verwysing na 'n ander lêer. Gewoonlik, selfs nadat die skakel verwyder is, bly die skakel. Daar is geen probleem om so 'n skakel te verwyder nie, want die skakel werk in werklikheid nie. Daarom sal niks sleg gebeur as jy dit verwyder nie.
Jy kan ook die "Data Check"-funksie gebruik om uit te vind of daar enige onnodige skakels is. Skakels bly gewoonlik as die "Lys" tipe data validering gebruik word. Maar wat om te doen as daar baie selle is? Is dit regtig nodig om elkeen van hulle opeenvolgend na te gaan? Natuurlik nie. Dit sal immers baie lank neem. Daarom moet u 'n spesiale kode gebruik om dit aansienlik te stoor.
Opsie eksplisiet
'—————————————————————————————
' Skrywer: The_Prist (Shcherbakov Dmitry)
' Professionele ontwikkeling van toepassings vir MS Office van enige kompleksiteit
' Doen opleiding op MS Excel
' https://www.excel-vba.ru
' [e-pos beskerm]
'WebMoney—R298726502453; Yandex.Money — 41001332272872
' Doel:
'—————————————————————————————
Sub FindErrLink()
'ons moet in die Data -Change skakels kyk na die bronlêer
'en plaas die sleutelwoorde hier in kleinletters (deel van die lêernaam)
'asterisk vervang net enige aantal karakters sodat jy nie bekommerd hoef te wees oor die presiese naam nie
Const sToFndLink$ = “*verkope 2018*”
Dim rr As Range, rc As Range, rres As Range, s$
'definieer alle selle met data-validering
Fout Volgende aflewering
Stel rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
As rr Niks Is Dan
MsgBox "Daar is geen selle met data-validering op die aktiewe blad nie", vbInformation, "www.excel-vba.ru"
Verlaat sub
Einde as
Op fout Gaan na 0
'kyk elke sel vir skakels
Vir elke rc In rr
'net ingeval, slaan ons foute oor – dit kan ook gebeur
'maar ons verbindings moet daarsonder wees en hulle sal beslis gevind word
s = «»
Fout Volgende aflewering
s = rc.Validasie.Formule1
Op fout Gaan na 0
'gevind – ons versamel alles in 'n aparte reeks
As LCase(s) Hou van sToFndLink Dan
As rres Niks Is Dan
Stel rres = rc
anders
Stel rres = Unie(rc, rres)
Einde as
Einde as
volgende
'as daar 'n verbinding is, kies alle selle met sulke datakontroles
Indien nie rres Is Niks Dan
rres.Kies
' rres.Interior.Color = vbRed 'as jy met kleur wil uitlig
Einde as
End Sub
Dit is nodig om 'n standaardmodule in die makroredigeerder te maak, en dan hierdie teks daar in te voeg. Daarna, roep die makro-venster met die sleutelkombinasie Alt + F8, en kies dan ons makro en klik op die "Run" -knoppie. Daar is 'n paar dinge om in gedagte te hou wanneer hierdie kode gebruik word:
- Voordat jy 'n skakel soek wat nie meer relevant is nie, moet jy eers bepaal hoe die skakel waardeur dit geskep word lyk. Om dit te doen, gaan na die "Data"-kieslys en vind die "Change Links"-item daar. Daarna moet u na die lêernaam kyk en dit in aanhalingstekens spesifiseer. Byvoorbeeld, soos volg: Const sToFndLink$ = “*verkope 2018*”
- Dit is moontlik om die naam nie volledig te skryf nie, maar bloot onnodige karakters met 'n asterisk te vervang. En in aanhalingstekens, skryf die lêernaam in klein letters. In hierdie geval sal Excel alle lêers wat so 'n string bevat aan die einde vind.
- Hierdie kode kan slegs kyk vir skakels in die blad wat tans aktief is.
- Met hierdie makro kan jy net die selle kies wat dit gevind het. Jy moet alles handmatig uitvee. Dit is 'n pluspunt, want jy kan alles weer dubbel nagaan.
- Jy kan ook die selle in 'n spesiale kleur uitgelig maak. Om dit te doen, verwyder die apostrof voor hierdie reël. rres.Interior.Color = vbRed
Gewoonlik, nadat u die stappe voltooi het wat in die instruksies hierbo beskryf is, behoort daar nie meer onnodige verbindings te wees nie. Maar as daar sommige van hulle in die dokument is en jy kan dit om een of ander rede nie verwyder nie ('n tipiese voorbeeld is die sekuriteit van data in 'n blad), dan kan jy 'n ander volgorde van aksies gebruik. Hierdie instruksie is slegs geldig vir weergawes 2007 en hoër.
- Ons skep 'n rugsteunkopie van die dokument.
- Maak hierdie dokument oop met die argiefhouer. Jy kan enige een gebruik wat die zip-formaat ondersteun, maar WinRar sal ook werk, sowel as die een wat in Windows ingebou is.
- In die argief wat verskyn, moet u die xl-lêergids vind en dan eksterne skakels oopmaak.
- Hierdie vouer bevat alle eksterne skakels, wat elk ooreenstem met 'n lêer van die vorm externalLink1.xml. Almal van hulle is slegs genommer, en daarom het die gebruiker nie die geleentheid om te verstaan watter soort verband dit is nie. Om te verstaan watter soort verbinding, moet jy die _rels-lêergids oopmaak en daarna kyk.
- Daarna verwyder ons alle of spesifieke skakels, gebaseer op wat ons in die externalLinkX.xml.rels-lêer leer.
- Daarna maak ons ons lêer oop met Excel. Daar sal inligting wees oor 'n fout soos "Fout in 'n gedeelte van die inhoud in die boek." Ons gee toestemming. Daarna sal 'n ander dialoog verskyn. Ons maak dit toe.
Daarna moet alle skakels verwyder word.