Kako prekiniti povezave v Excelu

Komunikacija je zelo uporabna funkcija v Excelu. Navsezadnje morajo uporabniki zelo pogosto uporabljati informacije iz drugih datotek. Toda v nekaterih situacijah lahko naredijo več škode kot koristi. Konec koncev, na primer, če te datoteke pošljete po pošti, povezave ne delujejo. Danes bomo podrobneje govorili o tem, kaj storiti, da bi se izognili takšni težavi.

Kaj so razmerja v Excelu

Relacije v Excelu se zelo pogosto uporabljajo v povezavi s funkcijami, kot je npr VPRpridobiti informacije iz drugega delovnega zvezka. Lahko je v obliki posebne povezave, ki vsebuje naslov ne le celice, ampak tudi knjige, v kateri so podatki. Posledično je taka povezava videti nekako takole: =VLOOKUP(A2;'[Prodaja 2018.xlsx]Poročilo'!$A:$F;4;0). Ali pa za enostavnejšo predstavitev naslov predstavite v naslednji obliki: ='[Prodaja 2018.xlsx]Poročilo'!$A1. Analizirajmo vsakega od elementov povezave te vrste:

1. [Prodaja 2018.xlsx]. Ta fragment vsebuje povezavo do datoteke, iz katere želite pridobiti informacije. Imenuje se tudi vir.
2. slike. Uporabili smo naslednje ime, vendar to ni ime, ki bi moralo biti. Ta blok vsebuje ime lista, v katerem morate najti informacije.
3. $A:$F in $A1 – naslov celice ali obsega s podatki, ki jih vsebuje ta dokument.

Pravzaprav se postopek ustvarjanja povezave do zunanjega dokumenta imenuje povezovanje. Ko registriramo naslov celice v drugi datoteki, se spremeni vsebina zavihka »Podatki«. Aktiven postane namreč gumb “Spremeni povezave”, s pomočjo katerega lahko uporabnik ureja obstoječe povezave.

Bistvo problema

Pri uporabi povezav praviloma ne nastanejo dodatne težave. Tudi če pride do situacije, v kateri se celice spremenijo, se vse povezave samodejno posodobijo. Če pa že preimenujete sam delovni zvezek ali ga premaknete na drug naslov, Excel postane nemočen. Zato ustvari naslednje sporočilo.

Tukaj ima uporabnik dve možni možnosti, kako ravnati v tej situaciji. Lahko klikne »Nadaljuj« in takrat spremembe ne bodo posodobljene, lahko pa klikne gumb »Spremeni povezave«, s katerim jih posodobi ročno. Ko kliknemo na ta gumb, se prikaže dodatno okno, v katerem bo mogoče spremeniti povezave, kjer je označeno, kje se trenutno nahaja prava datoteka in kako se imenuje.

Poleg tega lahko urejate povezave prek ustreznega gumba na zavihku »Podatki«. Uporabnik lahko tudi ugotovi, da je povezava prekinjena zaradi napake #LINK, ki se pojavi, ko Excel ne more dostopati do informacij, ki se nahajajo na določenem naslovu, ker je sam naslov neveljaven.

Kako odstraniti povezavo v excelu

Eden najpreprostejših načinov za rešitev zgoraj opisane situacije, če ne morete sami posodobiti lokacije povezane datoteke, je brisanje same povezave. To je še posebej enostavno narediti, če dokument vsebuje samo eno povezavo. Če želite to narediti, morate izvesti naslednje zaporedje korakov:

1. Odprite meni »Podatki«.
2. Najdemo razdelek »Povezave« in tam možnost »Spremeni povezave«.
3. Po tem kliknite »Prekini povezavo«.

Če nameravate to knjigo poslati drugi osebi, je zelo priporočljivo, da to storite vnaprej. Navsezadnje bodo po izbrisu povezav vse vrednosti, ki jih vsebuje drug dokument, samodejno naložene v datoteko, uporabljene v formulah, namesto naslova celice pa se bodo informacije v ustreznih celicah preprosto preoblikovale v vrednosti .

Kako odstraniti povezavo med vsemi knjigami

Če pa število povezav postane preveliko, lahko njihovo ročno brisanje traja dolgo. Če želite to težavo rešiti naenkrat, lahko uporabite poseben makro. Je v dodatku VBA-Excel. Morate ga aktivirati in iti na zavihek z istim imenom. Tam bo razdelek »Povezave«, v katerem moramo klikniti gumb »Prekini vse povezave«.

Koda VBA

Če tega dodatka ni mogoče aktivirati, lahko sami ustvarite makro. To storite tako, da s pritiskom na tipki Alt + F11 odprete urejevalnik Visual Basic in v polje za vnos kode vpišete naslednje vrstice.

Sub UnlinkWorkBooks()

    Dim WbLinks

    Dim in As Long

    Izberite Case MsgBox(»Vsa sklicevanja na druge knjige bodo odstranjena iz te datoteke, formule, ki se nanašajo na druge knjige, pa bodo zamenjane z vrednostmi.« & vbCrLf & »Ali ste prepričani, da želite nadaljevati?«, 36, »Odstrani povezavo?« )

    Zadeva 7′ št

        Exit Sub

    Konec Izberi

    WbLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

    Če ni prazno (WbLinks), potem

        Za i = 1 na UBound (WbLinks)

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

        Naslednji

    ostalo

        MsgBox »V tej datoteki ni povezav do drugih knjig.«, 64, »Povezave do drugih knjig«

    Konec Če

End Sub

Kako prekiniti vezi samo v izbranem obsegu

Občasno je število povezav zelo veliko in uporabnik se boji, da po brisanju ene od njih ne bo več mogoče vrniti vse nazaj, če je bila katera odveč. Toda to je težava, ki se ji je enostavno izogniti. Če želite to narediti, morate izbrati obseg, v katerem želite izbrisati povezave, in jih nato izbrisati. Če želite to narediti, morate izvesti naslednje zaporedje dejanj:

1. Izberite nabor podatkov, ki ga je treba spremeniti.
2. Namestite dodatek VBA-Excel in nato pojdite na ustrezen zavihek.
3. Nato poiščemo meni »Povezave« in kliknemo na gumb »Prekini povezave v izbranih obsegih«.

Po tem bodo vse povezave v izbranem nizu celic izbrisane.

Kaj storiti, če vezi niso pretrgane

Vse našteto se sliši dobro, vendar v praksi vedno obstajajo nekatere nianse. Na primer, lahko pride do situacije, ko vezi niso pretrgane. V tem primeru se še vedno prikaže pogovorno okno, ki navaja, da povezav ni mogoče samodejno posodobiti. Kaj storiti v tej situaciji?

1. Najprej morate preveriti, ali so v imenovanih obsegih vsebovane informacije. Če želite to narediti, pritisnite kombinacijo tipk Ctrl + F3 ali odprite zavihek »Formule« – »Upravitelj imen«. Če je ime datoteke polno, ga morate samo urediti ali v celoti odstraniti. Preden izbrišete imenovane obsege, morate kopirati datoteko na drugo mesto, da se lahko vrnete na izvirno različico, če ste naredili napačne korake.
2. Če težave ne morete rešiti z odstranitvijo imen, lahko preverite pogojno oblikovanje. Na celice v drugi tabeli se je mogoče sklicevati v pravilih pogojnega oblikovanja. Če želite to narediti, poiščite ustrezen element na zavihku »Domov« in kliknite gumb »Upravljanje datotek«.

   Običajno vam Excel ne omogoča podajanja naslovov drugih delovnih zvezkov v pogojnem oblikovanju, vendar to storite, če se sklicujete na imenovan obseg s sklicevanjem na drugo datoteko. Običajno tudi po odstranitvi povezave ta ostane. Takšne povezave ni problema odstraniti, ker povezava dejansko ne deluje. Zato se ne bo zgodilo nič slabega, če ga odstranite.

Uporabite lahko tudi funkcijo »Preverjanje podatkov«, da ugotovite, ali obstajajo nepotrebne povezave. Povezave običajno ostanejo, če je uporabljena vrsta preverjanja podatkov »Seznam«. Toda kaj storiti, če je celic veliko? Ali je res potrebno preverjati vsakega od njih zaporedno? Seveda ne. Navsezadnje bo trajalo zelo dolgo. Zato morate uporabiti posebno kodo, da jo znatno prihranite.

Option Explicit

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

' Avtor: The_Prist (Shcherbakov Dmitry)

Profesionalni razvoj aplikacij za MS Office katere koli zahtevnosti

' Izvajanje izobraževanj na MS Excel

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

' [e-pošta zaščitena]

'WebMoney—R298726502453; Yandex.Money — 41001332272872

' Namen:

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

Sub FindErrLink()

    'moramo pogledati povezavo Data -Change links do izvorne datoteke

    'in vnesite ključne besede tukaj z malimi črkami (del imena datoteke)

    'zvezdica samo nadomesti poljubno število znakov, tako da vam ni treba skrbeti za točno ime

    Const sToFndLink$ = “*prodaja 2018*”

    Dim rr kot obseg, rc kot obseg, rres kot obseg, s$

    'določite vse celice s preverjanjem podatkov

    On Error Resume Next

    Nastavite rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)

    Če rr ni nič, potem

        MsgBox »Na aktivnem listu ni celic s preverjanjem podatkov«, vbInformation, »www.excel-vba.ru«

        Exit Sub

    Konec Če

    Ob napaki Pojdi na 0

    'preveri vsako celico za povezave

    Za vsak rc In rr

        'za vsak slučaj preskočimo napake – tudi to se lahko zgodi

        'toda naše povezave morajo biti brez njih in zagotovo se bodo našle

        s = «»

        On Error Resume Next

        s = rc.Validation.Formula1

        Ob napaki Pojdi na 0

        'najdeno – zbiramo vse v ločeni ponudbi

        Če je LCase(-i) všeč sToFndLink Potem

            Če rres ni nič, potem

                Nastavite rres = rc

            ostalo

                Nastavi rres = Union(rc, rres)

            Konec Če

        Konec Če

    Naslednji

    'če obstaja povezava, izberite vse celice s takimi preverjanji podatkov

    Če ni rres, potem ni nič

        rres.Izberi

' rres.Interior.Color = vbRed 'če želite poudariti z barvo

    Konec Če

End Sub

V urejevalniku makrov je treba narediti standardni modul in nato tja vstaviti to besedilo. Po tem pokličite okno makra s kombinacijo tipk Alt + F8, nato izberite naš makro in kliknite gumb »Zaženi«. Pri uporabi te kode morate upoštevati nekaj stvari:

1. Preden iščete povezavo, ki ni več pomembna, morate najprej ugotoviti, kako izgleda povezava, prek katere je ustvarjena. Če želite to narediti, pojdite v meni »Podatki« in tam poiščite postavko »Spremeni povezave«. Po tem morate pogledati ime datoteke in ga navesti v narekovajih. Na primer takole: Const sToFndLink$ = “*prodaja 2018*”
2. Ime lahko napišete ne v celoti, ampak preprosto zamenjate nepotrebne znake z zvezdico. In v narekovajih napišite ime datoteke z malimi črkami. V tem primeru bo Excel našel vse datoteke, ki na koncu vsebujejo takšen niz.
3. Ta koda lahko preveri samo povezave na listu, ki je trenutno aktiven.
4. S tem makrom lahko izberete samo celice, ki jih je našel. Vse morate izbrisati ročno. To je plus, saj lahko vse še enkrat preverite.
5. Celice lahko poudarite tudi s posebno barvo. Če želite to narediti, odstranite apostrof pred to vrstico. rres.Interior.Color = vbRed

Običajno po dokončanju korakov, opisanih v zgornjih navodilih, ne bi smelo biti več nepotrebnih povezav. Če pa jih je nekaj v dokumentu in jih iz enega ali drugega razloga ne morete odstraniti (tipični primer je varnost podatkov na listu), potem lahko uporabite drugačno zaporedje dejanj. To navodilo velja samo za različice 2007 in novejše.

1. Ustvarimo varnostno kopijo dokumenta.
2. Odprite ta dokument s programom za arhiviranje. Uporabite lahko katerega koli, ki podpira format ZIP, deloval pa bo tudi WinRar, pa tudi tisti, ki je vgrajen v Windows.
3. V arhivu, ki se prikaže, morate najti mapo xl in nato odpreti externalLinks.
4. Ta mapa vsebuje vse zunanje povezave, od katerih vsaka ustreza datoteki v obliki externalLink1.xml. Vsi so le oštevilčeni, zato uporabnik nima možnosti razumeti, za kakšno povezavo gre. Če želite razumeti, kakšna je povezava, morate odpreti mapo _rels in jo pogledati tam.
5. Po tem odstranimo vse ali določene povezave na podlagi tega, kar izvemo v datoteki externalLinkX.xml.rels.
6. Po tem odpremo našo datoteko z Excelom. Prikazane bodo informacije o napaki, kot je »Napaka v delu vsebine v knjigi«. Dajemo soglasje. Po tem se prikaže drugo pogovorno okno. Zapremo ga.

Po tem je treba vse povezave odstraniti.