Hvordan bryte koblinger i Excel

Kommunikasjon er en svært nyttig funksjon i Excel. Tross alt, veldig ofte må brukere bruke informasjon fra andre filer. Men i noen situasjoner kan de gjøre mer skade enn nytte. Tross alt, hvis du for eksempel sender disse filene via post, fungerer ikke koblingene. I dag vil vi snakke mer detaljert om hva du skal gjøre for å unngå et slikt problem.

Hva er relasjoner i Excel

Relasjoner i Excel brukes veldig ofte sammen med funksjoner som f.eks VPRfor å få informasjon fra en annen arbeidsbok. Det kan ha form av en spesiell lenke som inneholder adressen til ikke bare cellen, men også boken der dataene er plassert. Som et resultat ser en slik kobling omtrent slik ut: =SØKUP(A2;'[Salg 2018.xlsx]Rapport'!$A:$F;4;0). Eller, for en enklere fremstilling, representer adressen i følgende skjema: ='[Salg 2018.xlsx]Rapport'!$A1. La oss analysere hvert av koblingselementene av denne typen:

1. [Salg 2018.xlsx]. Dette fragmentet inneholder en lenke til filen du ønsker å hente informasjon fra. Det kalles også kilden.
2. Bilder. Vi brukte følgende navn, men dette er ikke navnet som skal være. Denne blokken inneholder navnet på arket der du trenger å finne informasjon.
3. $A:$F og $A1 – adressen til en celle eller et område som inneholder data som finnes i dette dokumentet.

Egentlig kalles prosessen med å lage en lenke til et eksternt dokument kobling. Etter at vi har registrert adressen til cellen i en annen fil, endres innholdet i "Data"-fanen. Nemlig, "Endre tilkoblinger"-knappen blir aktiv, ved hjelp av denne kan brukeren redigere de eksisterende tilkoblingene.

Essensen av problemet

Som regel oppstår det ingen ekstra vanskeligheter for å bruke lenker. Selv om det oppstår en situasjon der cellene endres, blir alle lenker automatisk oppdatert. Men hvis du allerede gir nytt navn til selve arbeidsboken eller flytter den til en annen adresse, blir Excel maktesløs. Derfor produserer den følgende melding.

Her har brukeren to mulige alternativer for hvordan han skal opptre i denne situasjonen. Han kan klikke "Fortsett" og da vil ikke endringene bli oppdatert, eller han kan klikke på "Endre tilknytninger"-knappen, som han kan oppdatere dem manuelt med. Etter at vi har klikket på denne knappen, vises et ekstra vindu der det vil være mulig å endre lenkene, som indikerer hvor den riktige filen er plassert for øyeblikket og hva den heter.

I tillegg kan du redigere lenker via den tilsvarende knappen på "Data"-fanen. Brukeren kan også finne ut at forbindelsen er brutt av #LINK-feilen, som vises når Excel ikke kan få tilgang til informasjon som ligger på en bestemt adresse på grunn av at selve adressen er ugyldig.

Hvordan koble fra i excel

En av de enkleste metodene for å løse situasjonen beskrevet ovenfor i tilfelle du ikke selv kan oppdatere plasseringen til den koblede filen, er å slette selve koblingen. Dette er spesielt enkelt å gjøre hvis dokumentet bare inneholder én lenke. For å gjøre dette, må du utføre følgende sekvens av trinn:

1. Åpne "Data"-menyen.
2. Vi finner delen "Tilkoblinger", og der - alternativet "Endre tilkoblinger".
3. Etter det klikker du på "Fjern kobling".

Hvis du har tenkt å sende denne boken til en annen person, anbefales det på det sterkeste at du gjør det på forhånd. Tross alt, etter å ha slettet koblingene, vil alle verdiene som finnes i et annet dokument automatisk lastes inn i filen, brukt i formler, og i stedet for celleadressen vil informasjonen i de tilsvarende cellene ganske enkelt bli transformert til verdier .

Hvordan koble fra alle bøker

Men hvis antallet lenker blir for stort, kan det ta lang tid å slette dem manuelt. For å løse dette problemet på en gang, kan du bruke en spesiell makro. Det er i VBA-Excel-tillegget. Du må aktivere den og gå til fanen med samme navn. Det vil være en "Koblinger" -seksjon, der vi må klikke på "Knutt alle lenker" -knappen.

VBA-kode

Hvis det ikke er mulig å aktivere dette tillegget, kan du lage en makro selv. For å gjøre dette, åpne Visual Basic-editoren ved å trykke på Alt + F11-tastene, og skriv følgende linjer i kodeinntastingsfeltet.

Sub UnlinkWorkBooks()

    Dim WbLinks

    Dim og As Long

    Velg Case MsgBox("Alle referanser til andre bøker vil bli fjernet fra denne filen, og formler som refererer til andre bøker vil bli erstattet med verdier." & vbCrLf & "Er du sikker på at du vil fortsette?", 36, "Fjern kobling?" )

    Sak 7′ Nr

        Exit Sub

    Avslutt Velg

    WbLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

    Hvis ikke er tomt (WbLinks) så

        For i = 1 til UBound(WbLinks)

            ActiveWorkbook.BreakLink Navn:=WbLinks(i), Type:=xlLinkTypeExcelLinks

        neste

    ellers

        MsgBox "Det er ingen lenker til andre bøker i denne filen.", 64, "Koblinger til andre bøker"

    End If

End Sub

Hvordan bryte bånd kun i det valgte området

Fra tid til annen er antallet lenker veldig stort, og brukeren er redd for at etter å ha slettet en av dem, vil det ikke være mulig å returnere alt hvis noen var overflødige. Men dette er et problem som er lett å unngå. For å gjøre dette, må du velge området der du vil slette koblinger, og deretter slette dem. For å gjøre dette må du utføre følgende handlingssekvens:

1. Velg datasettet som må endres.
2. Installer VBA-Excel-tillegget, og gå deretter til den aktuelle fanen.
3. Deretter finner vi "Koblinger"-menyen og klikker på knappen "Bryt koblinger i de valgte områdene".

Etter det vil alle koblinger i det valgte settet med celler bli slettet.

Hva skal jeg gjøre hvis båndene ikke er brutt

Alt det ovennevnte høres bra ut, men i praksis er det alltid noen nyanser. Det kan for eksempel være en situasjon der båndene ikke brytes. I dette tilfellet vises fortsatt en dialogboks som sier at det ikke er mulig å automatisk oppdatere lenkene. Hva skal man gjøre i denne situasjonen?

1. Først må du sjekke om det finnes informasjon i de navngitte områdene. For å gjøre dette, trykk på tastekombinasjonen Ctrl + F3 eller åpne kategorien "Formler" - "Navnebehandling". Hvis filnavnet er fullt, trenger du bare å redigere det eller fjerne det helt. Før du sletter navngitte områder, må du kopiere filen til et annet sted slik at du kan gå tilbake til den opprinnelige versjonen hvis feil trinn ble tatt.
2. Hvis du ikke kan løse problemet ved å fjerne navn, kan du sjekke betinget formatering. Celler i en annen tabell kan refereres til i regler for betinget formatering. For å gjøre dette, finn det tilsvarende elementet på "Hjem"-fanen, og klikk deretter på "Filbehandling" -knappen.

   Normalt gir Excel deg ikke muligheten til å gi adressen til andre arbeidsbøker i betinget formatering, men du gjør det hvis du refererer til et navngitt område med en referanse til en annen fil. Vanligvis, selv etter at koblingen er fjernet, forblir koblingen. Det er ikke noe problem å fjerne en slik lenke, fordi lenken faktisk ikke fungerer. Derfor vil det ikke skje noe galt hvis du fjerner det.

Du kan også bruke "Data Check"-funksjonen for å finne ut om det er noen unødvendige lenker. Lenker forblir vanligvis hvis "List"-typen for datavalidering brukes. Men hva skal jeg gjøre hvis det er mange celler? Er det virkelig nødvendig å sjekke hver av dem sekvensielt? Selvfølgelig ikke. Tross alt vil det ta veldig lang tid. Derfor må du bruke en spesiell kode for å lagre den betydelig.

Alternativ Eksplisitt

'—————————————————————————————

' Forfatter: The_Prist(Shcherbakov Dmitry)

' Profesjonell utvikling av applikasjoner for MS Office av enhver kompleksitet

' Gjennomføre opplæring i MS Excel

' https://www.excel-vba.ru

' [e-postbeskyttet]

'WebMoney—R298726502453; Yandex.Money — 41001332272872

' Hensikt:

'—————————————————————————————

Sub FindErrLink()

    'vi må se i koblingen Data -Endre-lenker til kildefilen

    'og legg nøkkelordene her med små bokstaver (en del av filnavnet)

    'stjerne erstatter bare et hvilket som helst antall tegn, slik at du ikke trenger å bekymre deg for det nøyaktige navnet

    Const sToFndLink$ = “*salg 2018*”

    Dim rr As Range, rc As Range, rres As Range, s$

    'definer alle celler med datavalidering

    On Error Resume Next

    Sett rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)

    Hvis rr er ingenting da

        MsgBox "Det er ingen celler med datavalidering på det aktive arket", vbInformation, "www.excel-vba.ru"

        Exit Sub

    End If

    På feil GoTo 0

    'sjekk hver celle for lenker

    For hver rc I rr

        «i tilfelle, vi hopper over feil – dette kan også skje

        «men våre forbindelser må være uten dem, og de vil definitivt bli funnet

        s = «»

        On Error Resume Next

        s = rc.Validation.Formel1

        På feil GoTo 0

        'funnet – vi samler alt i et eget sortiment

        Hvis LCase(r) liker sToFndLink Da

            Hvis rres er ingenting da

                Sett rres = rc

            ellers

                Sett rres = Union(rc, rres)

            End If

        End If

    neste

    'hvis det er en forbindelse, velg alle celler med slike datasjekker

    Hvis ikke rres er ingenting da

        rres.Velg

' rres.Interior.Color = vbRed 'hvis du vil markere med farge

    End If

End Sub

Det er nødvendig å lage en standardmodul i makroeditoren, og deretter sette inn denne teksten der. Deretter ringer du makrovinduet ved å bruke tastekombinasjonen Alt + F8, og velg deretter makroen vår og klikk på "Kjør" -knappen. Det er et par ting du bør huske på når du bruker denne koden:

1. Før du søker etter en lenke som ikke lenger er relevant, må du først finne ut hvordan lenken den er opprettet gjennom ser ut. For å gjøre dette, gå til "Data"-menyen og finn elementet "Endre lenker" der. Etter det må du se på filnavnet, og spesifisere det i anførselstegn. For eksempel slik: Const sToFndLink$ = “*salg 2018*”
2. Det er mulig å skrive navnet ikke i sin helhet, men ganske enkelt erstatte unødvendige tegn med en stjerne. Og i anførselstegn, skriv filnavnet med små bokstaver. I dette tilfellet vil Excel finne alle filene som inneholder en slik streng på slutten.
3. Denne koden kan kun se etter lenker i arket som for øyeblikket er aktivt.
4. Med denne makroen kan du bare velge cellene den har funnet. Du må slette alt manuelt. Dette er et pluss, fordi du kan dobbeltsjekke alt på nytt.
5. Du kan også gjøre cellene uthevet i en spesiell farge. For å gjøre dette, fjern apostrof før denne linjen. rres.Interior.Color = vbRed

Vanligvis, etter at du har fullført trinnene beskrevet i instruksjonene ovenfor, skal det ikke være flere unødvendige tilkoblinger. Men hvis det er noen av dem i dokumentet og du ikke kan fjerne dem av en eller annen grunn (et typisk eksempel er datasikkerheten i et ark), kan du bruke en annen handlingssekvens. Denne instruksjonen er kun gyldig for versjoner 2007 og nyere.

1. Vi lager en sikkerhetskopi av dokumentet.
2. Åpne dette dokumentet ved hjelp av arkiveringsverktøyet. Du kan bruke alle som støtter ZIP-formatet, men WinRar vil også fungere, så vel som den som er innebygd i Windows.
3. I arkivet som vises, må du finne xl-mappen, og deretter åpne eksterne koblinger.
4. Denne mappen inneholder alle eksterne lenker, som hver tilsvarer en fil med formen externalLink1.xml. Alle er kun nummererte, og derfor har ikke brukeren mulighet til å forstå hva slags sammenheng dette er. For å forstå hva slags tilkobling, må du åpne mappen _rels og se på den der.
5. Etter det fjerner vi alle eller spesifikke lenker, basert på det vi lærer i filen externalLinkX.xml.rels.
6. Etter det åpner vi filen vår ved hjelp av Excel. Det vil være informasjon om en feil som "Feil i en del av innholdet i boken." Vi gir samtykke. Etter det vil en annen dialog vises. Vi lukker den.

Etter det skal alle lenker fjernes.