Innehåll
Kommunikation är en mycket användbar funktion i Excel. När allt kommer omkring måste användare väldigt ofta använda information från andra filer. Men i vissa situationer kan de göra mer skada än nytta. När allt kommer omkring, till exempel, om du skickar dessa filer med post, fungerar inte länkarna. Idag kommer vi att prata mer i detalj om vad man ska göra för att undvika ett sådant problem.
Vad är relationer i Excel
Relationer i Excel används mycket ofta i samband med funktioner som t.ex VPRför att få information från en annan arbetsbok. Det kan ha formen av en speciell länk som innehåller adressen till inte bara cellen utan också boken där data finns. Som ett resultat ser en sådan länk ut ungefär så här: =VLOOKUP(A2;'[Försäljning 2018.xlsx]Rapport'!$A:$F;4;0). Eller, för en enklare representation, representera adressen i följande form: ='[Försäljning 2018.xlsx]Rapport'!$A1. Låt oss analysera vart och ett av länkelementen av denna typ:
- [Försäljning 2018.xlsx]. Detta fragment innehåller en länk till filen som du vill hämta information från. Det kallas också för källan.
- Bilder. Vi använde följande namn, men det är inte det namnet som borde vara det. Detta block innehåller namnet på arket där du behöver hitta information.
- $A:$F och $A1 – adressen till en cell eller ett område som innehåller data som finns i detta dokument.
Egentligen kallas processen att skapa en länk till ett externt dokument länkning. Efter att vi har registrerat adressen till cellen som finns i en annan fil, ändras innehållet på fliken "Data". Knappen "Ändra anslutningar" blir nämligen aktiv, med hjälp av vilken användaren kan redigera de befintliga anslutningarna.
Kärnan i problemet
Som regel uppstår inga ytterligare svårigheter för att använda länkar. Även om det uppstår en situation där cellerna ändras, så uppdateras alla länkar automatiskt. Men om du redan byter namn på själva arbetsboken eller flyttar den till en annan adress blir Excel maktlös. Därför ger den följande meddelande.
Här har användaren två möjliga alternativ för hur han ska agera i denna situation. Han kan klicka på "Fortsätt" och då kommer ändringarna inte att uppdateras, eller så kan han klicka på knappen "Ändra associationer", med vilken han kan uppdatera dem manuellt. Efter att vi klickat på den här knappen kommer ett ytterligare fönster att visas där det kommer att vara möjligt att ändra länkarna, som anger var den korrekta filen finns för tillfället och vad den heter.
Dessutom kan du redigera länkar via motsvarande knapp som finns på fliken "Data". Användaren kan också få reda på att anslutningen är bruten av #LINK-felet, som visas när Excel inte kan komma åt information som finns på en specifik adress på grund av att själva adressen är ogiltig.
Hur man tar bort länken i excel
En av de enklaste metoderna för att lösa situationen som beskrivs ovan om du inte själv kan uppdatera platsen för den länkade filen är att ta bort själva länken. Detta är särskilt enkelt att göra om dokumentet bara innehåller en länk. För att göra detta måste du utföra följande steg:
- Öppna menyn "Data".
- Vi hittar avsnittet "Anslutningar" och där - alternativet "Ändra anslutningar".
- Efter det klickar du på "Ta bort länk".
Om du tänker skicka den här boken till en annan person, rekommenderas det starkt att du gör det i förväg. När allt kommer omkring, efter att ha tagit bort länkarna kommer alla värden som finns i ett annat dokument automatiskt att laddas in i filen, användas i formler, och istället för celladressen kommer informationen i motsvarande celler helt enkelt att omvandlas till värden .
Hur man tar bort länken till alla böcker
Men om antalet länkar blir för stort kan det ta lång tid att manuellt ta bort dem. För att lösa detta problem på en gång kan du använda ett speciellt makro. Det finns i VBA-Excel-tillägget. Du måste aktivera den och gå till fliken med samma namn. Det kommer att finnas en "Länkar"-sektion, där vi måste klicka på knappen "Krossa alla länkar".
VBA-kod
Om det inte går att aktivera detta tillägg kan du själv skapa ett makro. För att göra detta, öppna Visual Basic-redigeraren genom att trycka på Alt + F11-tangenterna och skriv följande rader i kodinmatningsfältet.
Sub UnlinkWorkBooks()
Dim WbLinks
Dim och As Long
Välj Case MsgBox("Alla referenser till andra böcker kommer att tas bort från den här filen, och formler som refererar till andra böcker kommer att ersättas med värden." & vbCrLf & "Är du säker på att du vill fortsätta?", 36, "Ta bort länk?" )
Fall 7′ Nr
Exit Sub
Avsluta Välj
WbLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
Om inte är tomt (WbLinks) då
För i = 1 Till UBound(WbLinks)
ActiveWorkbook.BreakLink Namn:=WbLinks(i), Typ:=xlLinkTypeExcelLinks
Nästa
annars
MsgBox “Det finns inga länkar till andra böcker i den här filen.”, 64, “Länkar till andra böcker”
End If
End Sub
Hur man bryter band endast i det valda intervallet
Från tid till annan är antalet länkar mycket stort, och användaren är rädd att efter att ha tagit bort en av dem, kommer det inte att vara möjligt att returnera allt om några var överflödiga. Men detta är ett problem som är lätt att undvika. För att göra detta måste du välja intervallet där du vill ta bort länkar och sedan ta bort dem. För att göra detta måste du utföra följande sekvens av åtgärder:
- Välj den datauppsättning som behöver ändras.
- Installera VBA-Excel-tillägget och gå sedan till lämplig flik.
- Därefter hittar vi menyn "Länkar" och klickar på knappen "Bryt länkar i de valda områdena".
Efter det kommer alla länkar i den valda uppsättningen celler att raderas.
Vad ska man göra om banden inte är brutna
Allt ovanstående låter bra, men i praktiken finns det alltid några nyanser. Det kan till exempel finnas en situation där banden inte bryts. I det här fallet visas fortfarande en dialogruta som säger att det inte är möjligt att automatiskt uppdatera länkarna. Vad ska man göra i den här situationen?
- Först måste du kontrollera om någon information finns i de namngivna intervallen. För att göra detta, tryck på tangentkombinationen Ctrl + F3 eller öppna fliken "Formler" - "Namnhanterare". Om filnamnet är fullt behöver du bara redigera det eller ta bort det helt. Innan du tar bort namngivna intervall måste du kopiera filen till någon annan plats så att du kan återgå till den ursprungliga versionen om fel steg togs.
- Om du inte kan lösa problemet genom att ta bort namn kan du kontrollera villkorlig formatering. Celler i en annan tabell kan refereras i regler för villkorlig formatering. För att göra detta, hitta motsvarande objekt på fliken "Hem" och klicka sedan på knappen "Filhantering".
Normalt ger Excel dig inte möjligheten att ge adressen till andra arbetsböcker i villkorlig formatering, men det gör du om du refererar till ett namngivet område med en referens till en annan fil. Vanligtvis, även efter att länken har tagits bort, finns länken kvar. Det är inga problem att ta bort en sådan länk, eftersom länken faktiskt inte fungerar. Därför kommer inget dåligt att hända om du tar bort det.
Du kan också använda "Data Check"-funktionen för att ta reda på om det finns några onödiga länkar. Länkar finns vanligtvis kvar om datavalidering av typen "List" används. Men vad ska man göra om det finns många celler? Är det verkligen nödvändigt att kontrollera var och en av dem sekventiellt? Självklart inte. Det kommer trots allt att ta väldigt lång tid. Därför måste du använda en speciell kod för att avsevärt spara den.
Explicit alternativ
'—————————————————————————————
' Författare: The_Prist(Shcherbakov Dmitry)
Professionell utveckling av applikationer för MS Office oavsett komplexitet
' Genomför utbildningar i MS Excel
' https://www.excel-vba.ru
' [e-postskyddad]
'WebMoney—R298726502453; Yandex.Money — 41001332272872
' Ändamål:
'—————————————————————————————
Sub FindErrLink()
'vi måste titta i länken Data -Ändra länkar till källfilen
'och skriv nyckelorden här med gemener (en del av filnamnet)
'asterisk ersätter bara valfritt antal tecken så att du inte behöver oroa dig för det exakta namnet
Const sToFndLink$ = “*försäljning 2018*”
Dim rr As Range, rc As Range, rres As Range, s$
'definiera alla celler med datavalidering
On Error Resume Next
Set rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
Om rr är ingenting då
MsgBox "Det finns inga celler med datavalidering på det aktiva bladet", vbInformation, "www.excel-vba.ru"
Exit Sub
End If
På Fel GoTo 0
'kontrollera varje cell för länkar
För varje rc I rr
"för säkerhets skull hoppar vi över fel – det kan också hända
Men våra kontakter måste vara utan dem och de kommer definitivt att hittas
s = «»
On Error Resume Next
s = rc.Validation.Formel1
På Fel GoTo 0
'hittad – vi samlar allt i ett separat sortiment
Om LCase(s) gillar sToFndLink Då
Om rres är ingenting då
Ställ in rres = rc
annars
Ange rres = Union(rc, rres)
End If
End If
Nästa
'om det finns en anslutning, välj alla celler med sådana datakontroller
Om inte rres är ingenting då
rres.Välj
' rres.Interior.Color = vbRed 'om du vill markera med färg
End If
End Sub
Det är nödvändigt att göra en standardmodul i makroredigeraren och sedan infoga den här texten där. Därefter ringer du upp makrofönstret med tangentkombinationen Alt + F8 och väljer sedan vårt makro och klickar på "Kör" -knappen. Det finns några saker att tänka på när du använder den här koden:
- Innan du söker efter en länk som inte längre är relevant måste du först fastställa hur länken genom vilken den skapas ser ut. För att göra detta, gå till "Data"-menyn och hitta alternativet "Ändra länkar" där. Efter det måste du titta på filnamnet och ange det inom citattecken. Till exempel så här: Const sToFndLink$ = “*försäljning 2018*”
- Det är möjligt att skriva namnet inte i sin helhet, utan helt enkelt ersätta onödiga tecken med en asterisk. Och inom citattecken, skriv filnamnet med små bokstäver. I det här fallet hittar Excel alla filer som innehåller en sådan sträng i slutet.
- Den här koden kan endast söka efter länkar i arket som för närvarande är aktivt.
- Med detta makro kan du bara markera de celler som det har hittat. Du måste radera allt manuellt. Detta är ett plus, eftersom du kan dubbelkolla allt igen.
- Du kan också göra cellerna markerade i en speciell färg. För att göra detta, ta bort apostrof före denna rad. rres.Interior.Color = vbRed
Vanligtvis, efter att du har slutfört stegen som beskrivs i instruktionerna ovan, bör det inte finnas fler onödiga anslutningar. Men om det finns några av dem i dokumentet och du inte kan ta bort dem av en eller annan anledning (ett typiskt exempel är säkerheten för data i ett ark), kan du använda en annan sekvens av åtgärder. Denna instruktion är endast giltig för version 2007 och senare.
- Vi skapar en säkerhetskopia av dokumentet.
- Öppna detta dokument med hjälp av arkivet. Du kan använda vilket som helst som stöder ZIP-formatet, men WinRar fungerar också, liksom det som är inbyggt i Windows.
- I arkivet som visas måste du hitta xl-mappen och sedan öppna externa länkar.
- Denna mapp innehåller alla externa länkar, som var och en motsvarar en fil i formen externalLink1.xml. Alla är endast numrerade, och därför har användaren inte möjlighet att förstå vilken typ av koppling det är. För att förstå vilken typ av anslutning måste du öppna mappen _rels och titta på den där.
- Efter det tar vi bort alla eller specifika länkar, baserat på vad vi lär oss i filen externalLinkX.xml.rels.
- Efter det öppnar vi vår fil med Excel. Det kommer att finnas information om ett fel som "Fel i en del av innehållet i boken." Vi ger samtycke. Därefter visas en annan dialogruta. Vi stänger den.
Efter det ska alla länkar tas bort.