Kuinka katkaista linkkejä Excelissä

Viestintä on erittäin hyödyllinen ominaisuus Excelissä. Loppujen lopuksi käyttäjien on usein käytettävä tietoja muista tiedostoista. Mutta joissakin tilanteissa niistä voi olla enemmän haittaa kuin hyötyä. Jos esimerkiksi lähetät nämä tiedostot postitse, linkit eivät toimi. Tänään puhumme yksityiskohtaisemmin siitä, mitä tehdä tällaisen ongelman välttämiseksi.

Mitä suhteet ovat Excelissä

Excelin suhteita käytetään hyvin usein funktioiden, kuten esim VPRsaada tietoa toisesta työkirjasta. Se voi olla erityisen linkin muodossa, joka sisältää paitsi solun, myös sen kirjan osoitteen, jossa tiedot sijaitsevat. Tämän seurauksena tällainen linkki näyttää suunnilleen tältä: =VHAKU(A2;'[Myynti 2018.xlsx]Raportti'!$A:$F;4;0). Tai yksinkertaisempaa esitystä varten esitä osoite seuraavassa muodossa: ='[Myynti 2018.xlsx]Raportti'!$A1. Analysoidaan jokainen tämän tyyppinen linkkielementti:

1. [Myynti 2018.xlsx]. Tämä fragmentti sisältää linkin tiedostoon, josta haluat saada tietoja. Sitä kutsutaan myös lähteeksi.
2. Valokuvat. Käytimme seuraavaa nimeä, mutta tämän nimen ei pitäisi olla. Tämä lohko sisältää sen arkin nimen, josta sinun on löydettävä tietoja.
3. $A:$F ja $A1 – tämän asiakirjan tietoja sisältävän solun tai alueen osoite.

Itse asiassa linkin luomista ulkoiseen asiakirjaan kutsutaan linkittämiseksi. Kun olemme rekisteröineet toisen tiedoston sisältämän solun osoitteen, "Data"-välilehden sisältö muuttuu. Aktivoituu nimittäin ”Vaihda yhteyksiä” -painike, jonka avulla käyttäjä voi muokata olemassa olevia yhteyksiä.

Ongelman ydin

Pääsääntöisesti linkkien käytössä ei synny ylimääräisiä vaikeuksia. Vaikka syntyisi tilanne, jossa solut muuttuvat, kaikki linkit päivitetään automaattisesti. Mutta jos nimeät itse työkirjan uudelleen tai siirrät sen toiseen osoitteeseen, Excelistä tulee voimaton. Siksi se tuottaa seuraavan viestin.

Tässä käyttäjällä on kaksi mahdollista vaihtoehtoa toimia tässä tilanteessa. Hän voi napsauttaa "Jatka", jolloin muutokset eivät päivity, tai hän voi napsauttaa "Muuta assosiaatioita" -painiketta, jolla hän voi päivittää ne manuaalisesti. Kun napsautamme tätä painiketta, näkyviin tulee lisäikkuna, jossa on mahdollista muuttaa linkkejä, jotka osoittavat, missä oikea tiedosto tällä hetkellä sijaitsee ja mikä sen nimi on.

Lisäksi voit muokata linkkejä vastaavan painikkeen kautta, joka sijaitsee "Data"-välilehdellä. Käyttäjä voi myös saada selville, että yhteys katkeaa #LINK-virheestä, joka ilmestyy, kun Excel ei pääse käsiksi tietyssä osoitteessa sijaitseviin tietoihin, koska itse osoite on virheellinen.

Kuinka poistaa linkitys excelissä

Yksi yksinkertaisimmista tavoista ratkaista yllä kuvattu tilanne, jos et pysty itse päivittämään linkitetyn tiedoston sijaintia, on itse linkin poistaminen. Tämä on erityisen helppoa, jos asiakirja sisältää vain yhden linkin. Voit tehdä tämän suorittamalla seuraavat vaiheet:

1. Avaa "Data"-valikko.
2. Löydämme osion "Yhteydet" ja sieltä - vaihtoehdon "Vaihda yhteyksiä".
3. Napsauta sen jälkeen "Poista linkitys".

Jos aiot lähettää tämän kirjan postitse toiselle henkilölle, on erittäin suositeltavaa tehdä se etukäteen. Loppujen lopuksi linkkien poistamisen jälkeen kaikki toiseen asiakirjaan sisältyvät arvot ladataan automaattisesti tiedostoon, niitä käytetään kaavoissa, ja solun osoitteen sijaan vastaavien solujen tiedot yksinkertaisesti muunnetaan arvoiksi .

Kuinka poistaa kaikkien kirjojen linkit

Mutta jos linkkien määrä kasvaa liian suureksi, niiden manuaalinen poistaminen voi kestää kauan. Voit ratkaista tämän ongelman yhdellä kertaa käyttämällä erityistä makroa. Se on VBA-Excel-lisäosassa. Sinun on aktivoitava se ja siirryttävä samannimiseen välilehteen. Siellä on "Linkit" -osio, jossa meidän on napsautettava "Katkaise kaikki linkit" -painiketta.

VBA koodi

Jos tämän lisäosan aktivointi ei ole mahdollista, voit luoda makron itse. Voit tehdä tämän avaamalla Visual Basic -editorin painamalla Alt + F11-näppäimiä ja kirjoittamalla seuraavat rivit koodin syöttökenttään.

Sub Unlink WorkBooks()

    Himmeä WbLinkit

    Himmeä ja yhtä pitkä

    Select Case MsgBox("Kaikki viittaukset muihin kirjoihin poistetaan tästä tiedostosta ja muihin kirjoihin viittaavat kaavat korvataan arvoilla." & vbCrLf & "Haluatko varmasti jatkaa?", 36, "Poista linkitys?" )

    Tapaus 7′ No

        Exit Sub

    Lopeta Valitse

    WbLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

    Jos Ei IsEmpty(WbLinks) Sitten

        Jos i = 1 UBoundiin (WbLinkit)

            ActiveWorkbook.BreakLink Name:=WbLinks(i), Tyyppi:=xlLinkTypeExcelLinks

        seuraava

    muu

        MsgBox "Tässä tiedostossa ei ole linkkejä muihin kirjoihin." 64, "Linkkejä muihin kirjoihin"

    End If

End Sub

Kuinka katkaista siteet vain valitulla alueella

Ajoittain linkkien määrä on erittäin suuri, ja käyttäjä pelkää, että yhden niistä poistamisen jälkeen ei ole mahdollista palauttaa kaikkea takaisin, jos jotkut olivat tarpeettomia. Mutta tämä on ongelma, joka on helppo välttää. Tätä varten sinun on valittava alue, jolta linkit poistetaan, ja sitten poistettava ne. Voit tehdä tämän suorittamalla seuraavan toimintosarjan:

1. Valitse tietojoukko, jota on muokattava.
2. Asenna VBA-Excel-lisäosa ja siirry sitten sopivaan välilehteen.
3. Seuraavaksi löydämme "Linkit"-valikon ja napsautamme "Katkaise linkit valittuihin alueisiin" -painiketta.

Sen jälkeen kaikki linkit valitusta solujoukosta poistetaan.

Mitä tehdä, jos siteet eivät katkea

Kaikki yllä oleva kuulostaa hyvältä, mutta käytännössä on aina joitain vivahteita. Voi olla esimerkiksi tilanne, jossa siteet eivät katkea. Tässä tapauksessa näyttöön tulee silti valintaikkuna, joka ilmoittaa, ettei linkkejä ole mahdollista päivittää automaattisesti. Mitä tehdä tässä tilanteessa?

1. Ensin sinun on tarkistettava, sisältyykö nimettyihin alueisiin tietoja. Voit tehdä tämän painamalla näppäinyhdistelmää Ctrl + F3 tai avaamalla "Kaavat" -välilehden - "Name Manager". Jos tiedostonimi on täynnä, sinun tarvitsee vain muokata sitä tai poistaa se kokonaan. Ennen kuin poistat nimetyt alueet, sinun on kopioitava tiedosto johonkin toiseen paikkaan, jotta voit palata alkuperäiseen versioon, jos olet tehnyt vääriä vaiheita.
2. Jos et pysty ratkaisemaan ongelmaa poistamalla nimiä, voit tarkistaa ehdollisen muotoilun. Toisen taulukon soluihin voidaan viitata ehdollisissa muotoilusäännöissä. Voit tehdä tämän etsimällä vastaavan kohteen "Home"-välilehdeltä ja napsauttamalla sitten "Tiedostohallinta" -painiketta.

   Normaalisti Excel ei anna mahdollisuutta antaa muiden työkirjojen osoitteita ehdollisessa muotoilussa, mutta voit antaa, jos viittaat nimettyyn alueeseen viitaten toiseen tiedostoon. Yleensä linkin poistamisen jälkeenkin linkki säilyy. Tällaisen linkin poistaminen ei ole ongelma, koska linkki ei itse asiassa toimi. Siksi mitään pahaa ei tapahdu, jos poistat sen.

Voit myös käyttää "Data Check" -toimintoa selvittääksesi, onko tarpeettomia linkkejä. Linkit säilyvät yleensä, jos käytetään "Lista"-tyyppistä tietojen validointia. Mutta mitä tehdä, jos soluja on paljon? Onko todella tarpeen tarkistaa jokainen niistä peräkkäin? Ei tietenkään. Loppujen lopuksi se kestää hyvin kauan. Siksi sinun on käytettävä erityistä koodia tallentaaksesi sen merkittävästi.

Option Explicit

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

Kirjoittaja: The_Prist (Shcherbakov Dmitry)

' Ammattimainen kehitys MS Office -sovellusten monimutkaisuuteen

' MS Excelin koulutusten pitäminen

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

' [sähköposti suojattu]

"WebMoney — R298726502453; Yandex.Money — 41001332272872

' Tarkoitus:

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

Sub FindErrLink()

    "Meidän on katsottava Data -Change links -linkistä lähdetiedostoon

    'ja laita avainsanat tähän pienillä kirjaimilla (osa tiedoston nimeä)

    'tähti vain korvaa minkä tahansa määrän merkkejä, joten sinun ei tarvitse huolehtia tarkasta nimestä

    Const sToFndLink$ = "*myynti 2018*"

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

    'määritä kaikki solut, joissa on tietojen validointi

    On Error Resume Next

    Aseta rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)

    Jos rr ei ole mitään sitten

        MsgBox "Aktiivisella arkilla ei ole soluja, joissa on tietojen validointi", vbInformation, "www.excel-vba.ru"

        Exit Sub

    End If

    Virhe GoTo 0

    'tarkista jokaisesta solusta linkit

    Jokaiselle rc:lle In rr

        "Ohitamme virheet vain siltä varalta, että näinkin voi tapahtua."

        'mutta yhteytemme täytyy olla ilman niitä, ja ne varmasti löytyvät

        s = «»

        On Error Resume Next

        s = rc.Validation.Formula1

        Virhe GoTo 0

        Löytyi – keräämme kaiken erilliseen valikoimaan

        Jos LCase(t) Like sToFndLink Sitten

            Jos rres ei ole mitään sitten

                Aseta rres = rc

            muu

                Aseta rres = Union(rc, rres)

            End If

        End If

    seuraava

    Jos yhteys on olemassa, valitse kaikki solut, joissa on tällaisia ​​tietotarkistuksia

    Jos ei rres ei ole mitään sitten

        rres.Valitse

' rres.Interior.Color = vbRed 'jos haluat korostaa värillä

    End If

End Sub

On tarpeen tehdä vakiomoduuli makroeditorissa ja lisätä sitten tämä teksti sinne. Soita sen jälkeen makro-ikkunaan näppäinyhdistelmällä Alt + F8 ja valitse sitten makromme ja napsauta "Suorita" -painiketta. Tätä koodia käytettäessä on pidettävä mielessä muutama asia:

1. Ennen kuin etsit linkkiä, joka ei ole enää relevantti, sinun on ensin määritettävä, miltä linkki, jonka kautta se on luotu, näyttää. Voit tehdä tämän siirtymällä "Data" -valikkoon ja etsimällä sieltä "Muuta linkkejä" -kohdan. Tämän jälkeen sinun on tarkasteltava tiedoston nimeä ja määritettävä se lainausmerkeissä. Esimerkiksi näin: Const sToFndLink$ = "*myynti 2018*"
2. On mahdollista kirjoittaa nimeä ei kokonaan, vaan yksinkertaisesti korvata tarpeettomat merkit tähdellä. Ja lainausmerkeissä kirjoita tiedoston nimi pienillä kirjaimilla. Tässä tapauksessa Excel löytää kaikki tiedostot, jotka sisältävät tällaisen merkkijonon lopusta.
3. Tämä koodi pystyy tarkistamaan vain aktiivisen taulukon linkit.
4. Tällä makrolla voit valita vain ne solut, jotka se on löytänyt. Sinun on poistettava kaikki manuaalisesti. Tämä on plussa, koska voit tarkistaa kaiken uudelleen.
5. Voit myös korostaa soluja erityisellä värillä. Poista heittomerkki ennen tätä riviä. rres.Interior.Color = vbRed

Yleensä, kun olet suorittanut yllä olevissa ohjeissa kuvatut vaiheet, tarpeettomia yhteyksiä ei pitäisi enää olla. Mutta jos dokumentissa on niitä, etkä voi poistaa niitä syystä tai toisesta (tyypillinen esimerkki on taulukon tietojen suojaus), voit käyttää erilaista toimintosarjaa. Tämä ohje koskee vain versioita 2007 ja uudempia.

1. Luomme asiakirjasta varmuuskopion.
2. Avaa tämä asiakirja arkistaattorilla. Voit käyttää mitä tahansa ZIP-muotoa tukevaa, mutta myös WinRar toimii, samoin kuin Windowsin sisäänrakennettu.
3. Avautuvasta arkistosta sinun on löydettävä xl-kansio ja avattava ulkoiset linkit.
4. Tämä kansio sisältää kaikki ulkoiset linkit, joista jokainen vastaa tiedostoa, jonka muoto on externalLink1.xml. Kaikki ne ovat vain numeroituja, joten käyttäjällä ei ole mahdollisuutta ymmärtää, millainen yhteys on kyseessä. Ymmärtääksesi, millainen yhteys, sinun on avattava _rels-kansio ja katsottava sitä siellä.
5. Tämän jälkeen poistamme kaikki tai tietyt linkit ulkoisenLinkX.xml.rels-tiedostossa opittujen tietojen perusteella.
6. Tämän jälkeen avaamme tiedostomme Excelillä. Siellä on tietoa virheestä, kuten "Virhe kirjan sisällössä". Annamme suostumuksen. Tämän jälkeen näkyviin tulee toinen valintaikkuna. Suljemme sen.

Tämän jälkeen kaikki linkit tulee poistaa.