Ako prerušiť odkazy v Exceli

Komunikácia je v Exceli veľmi užitočná funkcia. Koniec koncov, používatelia veľmi často musia používať informácie z iných súborov. V niektorých situáciách však môžu spôsobiť viac škody ako úžitku. Ak totiž napríklad posielate tieto súbory poštou, odkazy nefungujú. Dnes si povieme podrobnejšie, čo robiť, aby sme sa takýmto problémom vyhli.

Čo sú vzťahy v Exceli

Vzťahy v Exceli sa veľmi často využívajú v spojení s funkciami ako napr VPRzískať informácie z iného zošita. Môže mať podobu špeciálneho odkazu, ktorý obsahuje adresu nielen bunky, ale aj knihy, v ktorej sa údaje nachádzajú. Výsledkom je, že takýto odkaz vyzerá asi takto: =VLOOKUP(A2;'[Predaj 2018.xlsx]Prehľad'!$A:$F;4;0). Alebo pre jednoduchšiu reprezentáciu uveďte adresu v nasledujúcom tvare: ='[Sales 2018.xlsx]Report'!$A1. Poďme analyzovať každý z prvkov odkazu tohto typu:

1. [Predaj 2018.xlsx]. Tento fragment obsahuje odkaz na súbor, z ktorého chcete získať informácie. Nazýva sa aj zdroj.
2. fotografie. Použili sme nasledujúci názov, ale toto nie je názov, ktorý by mal byť. Tento blok obsahuje názov hárku, v ktorom potrebujete nájsť informácie.
3. $A:$F a $A1 – adresa bunky alebo rozsahu obsahujúceho údaje, ktoré sú obsiahnuté v tomto dokumente.

V skutočnosti sa proces vytvárania prepojenia na externý dokument nazýva prepojenie. Po zaregistrovaní adresy bunky obsiahnutej v inom súbore sa obsah karty „Údaje“ zmení. Aktivuje sa totiž tlačidlo „Zmeniť pripojenia“, pomocou ktorého môže používateľ upraviť existujúce pripojenia.

Podstata problému

Pri používaní odkazov spravidla nevznikajú žiadne ďalšie ťažkosti. Aj keď nastane situácia, že sa bunky zmenia, všetky odkazy sa automaticky aktualizujú. Ak však už premenujete samotný zošit alebo ho presuniete na inú adresu, Excel sa stane bezmocným. Preto vytvára nasledujúcu správu.

Tu má používateľ dve možné možnosti, ako v tejto situácii konať. Môže kliknúť na „Pokračovať“ a potom sa zmeny nebudú aktualizovať, alebo môže kliknúť na tlačidlo „Zmeniť asociácie“, pomocou ktorého ich môže aktualizovať manuálne. Po kliknutí na toto tlačidlo sa zobrazí ďalšie okno, v ktorom bude možné zmeniť odkazy, ktoré označujú, kde sa momentálne nachádza správny súbor a ako sa volá.

Okrem toho môžete odkazy upravovať pomocou príslušného tlačidla umiestneného na karte „Údaje“. Používateľ môže tiež zistiť, že spojenie je prerušené, chybou #LINK, ktorá sa objaví, keď Excel nemôže získať prístup k informáciám umiestneným na konkrétnej adrese z dôvodu, že samotná adresa je neplatná.

Ako zrušiť prepojenie v programe Excel

Jednou z najjednoduchších metód na vyriešenie situácie opísanej vyššie v prípade, že nemôžete aktualizovať umiestnenie prepojeného súboru sami, je odstránenie samotného odkazu. Je to obzvlášť jednoduché, ak dokument obsahuje iba jeden odkaz. Ak to chcete urobiť, musíte vykonať nasledujúcu postupnosť krokov:

1. Otvorte ponuku „Údaje“.
2. Nájdeme sekciu „Pripojenia“ a tam možnosť „Zmeniť pripojenia“.
3. Potom kliknite na „Odpojiť“.

Ak máte v úmysle poslať túto knihu poštou inej osobe, dôrazne vám odporúčame, aby ste tak urobili vopred. Koniec koncov, po odstránení odkazov sa všetky hodnoty, ktoré sú obsiahnuté v inom dokumente, automaticky načítajú do súboru, použijú sa vo vzorcoch a namiesto adresy bunky sa informácie v zodpovedajúcich bunkách jednoducho premenia na hodnoty. .

Ako zrušiť prepojenie všetkých kníh

Ak je však počet odkazov príliš veľký, ich manuálne odstránenie môže trvať dlho. Ak chcete tento problém vyriešiť naraz, môžete použiť špeciálne makro. Nachádza sa v doplnku VBA-Excel. Musíte ho aktivovať a prejsť na kartu s rovnakým názvom. K dispozícii bude sekcia „Odkazy“, v ktorej musíme kliknúť na tlačidlo „Prerušiť všetky odkazy“.

kód VBA

Ak nie je možné aktivovať tento doplnok, môžete si vytvoriť makro sami. Ak to chcete urobiť, otvorte editor jazyka Visual Basic stlačením klávesov Alt + F11 a do poľa na zadanie kódu napíšte nasledujúce riadky.

Sub UnlinkWorkBooks()

    Stlmiť WbLinks

    Dim a As Long

    Vyberte Case MsgBox („Všetky odkazy na iné knihy budú z tohto súboru odstránené a vzorce odkazujúce na iné knihy budú nahradené hodnotami.“ & vbCrLf & „Naozaj chcete pokračovať?“, 36, „Odpojiť?“ )

    Prípad 7′ č

        Koniec Sub

    Koniec Vyberte

    WbLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

    If Not IsEmpty (WbLinks) Then

        Pre i = 1 až UBound (WbLinks)

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

        ďalšie

    inak

        MsgBox "V tomto súbore nie sú žiadne odkazy na iné knihy.", 64, "Odkazy na iné knihy"

    End If

End Sub

Ako zlomiť väzy len vo vybranom rozsahu

Z času na čas je počet odkazov veľmi veľký a používateľ sa obáva, že po odstránení jedného z nich nebude možné vrátiť všetko späť, ak niektoré boli nadbytočné. To je však problém, ktorému sa dá ľahko vyhnúť. Ak to chcete urobiť, musíte vybrať rozsah, v ktorom chcete odstrániť odkazy, a potom ich odstrániť. Ak to chcete urobiť, musíte vykonať nasledujúcu postupnosť akcií:

1. Vyberte súbor údajov, ktorý je potrebné upraviť.
2. Nainštalujte doplnok VBA-Excel a potom prejdite na príslušnú kartu.
3. Ďalej nájdeme ponuku „Odkazy“ a klikneme na tlačidlo „Prerušiť odkazy vo vybraných rozsahoch“.

Potom sa všetky odkazy vo vybranej skupine buniek odstránia.

Čo robiť, ak nie sú pretrhnuté väzby

Všetky vyššie uvedené znie dobre, ale v praxi vždy existujú nejaké nuansy. Môže nastať napríklad situácia, keď sa väzby nepretrhnú. V tomto prípade sa stále zobrazuje dialógové okno s informáciou, že nie je možné automaticky aktualizovať odkazy. Čo robiť v tejto situácii?

1. Najprv musíte skontrolovať, či sa v pomenovaných rozsahoch nachádzajú nejaké informácie. Ak to chcete urobiť, stlačte kombináciu klávesov Ctrl + F3 alebo otvorte kartu "Vzorce" - "Správca mien". Ak je názov súboru plný, stačí ho upraviť alebo úplne odstrániť. Pred odstránením pomenovaných rozsahov musíte skopírovať súbor na iné miesto, aby ste sa mohli vrátiť k pôvodnej verzii, ak boli vykonané nesprávne kroky.
2. Ak nemôžete vyriešiť problém odstránením mien, môžete skontrolovať podmienené formátovanie. Na bunky v inej tabuľke možno odkazovať v pravidlách podmieneného formátovania. Ak to chcete urobiť, nájdite príslušnú položku na karte „Domov“ a potom kliknite na tlačidlo „Správa súborov“.

   Za normálnych okolností vám Excel nedáva možnosť zadať adresu iných zošitov v podmienenom formátovaní, ale áno, ak odkazujete na pomenovaný rozsah s odkazom na iný súbor. Zvyčajne aj po odstránení odkazu zostane odkaz. Nie je problém takýto odkaz odstrániť, pretože odkaz v skutočnosti nefunguje. Preto sa nič zlé nestane, ak ho odstránite.

Môžete tiež použiť funkciu „Kontrola údajov“, aby ste zistili, či nie sú nejaké zbytočné odkazy. Odkazy zvyčajne zostanú, ak sa použije typ overenia údajov „Zoznam“. Čo však robiť, ak je buniek veľa? Je naozaj potrebné kontrolovať každú z nich postupne? Samozrejme, že nie. Koniec koncov, bude to trvať veľmi dlho. Preto je potrebné použiť špeciálny kód, aby ste ho výrazne ušetrili.

Možnosť Explicitná

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

Autor: The_Prist(Shcherbakov Dmitry)

' Profesionálny vývoj aplikácií pre MS Office akejkoľvek zložitosti

Vedenie školení v programe MS Excel

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

[chránený e-mailom]

'WebMoney-R298726502453; Yandex.Money — 41001332272872

Účel:

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

Sub FindErrLink()

    „Musíme sa pozrieť do odkazu Data-Change links na zdrojový súbor

    'a vložte kľúčové slová sem malými písmenami (časť názvu súboru)

    'hviezdička len nahrádza ľubovoľný počet znakov, takže sa nemusíte starať o presný názov

    Const sToFndLink$ = „*predaj 2018*“

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

    'definovať všetky bunky s overením údajov

    On Error Resume Next

    Nastaviť rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)

    Ak rr nie je nič, potom

        MsgBox „Na aktívnom hárku nie sú žiadne bunky s overením údajov“, vbInformation, „www.excel-vba.ru“

        Koniec Sub

    End If

    Pri chybe Prejsť na 0

    'skontrolujte odkazy v každej bunke

    Za každý r V rr

        „Pre každý prípad preskočíme chyby – aj to sa môže stať

        „ale naše spojenia musia byť bez nich a určite sa nájdu

        s = «»

        On Error Resume Next

        s = rc.Validácia.Vzorec1

        Pri chybe Prejsť na 0

        „nájdené – všetko zbierame v samostatnom sortimente

        Ak LCase(s) Like sToFndLink Then

            If rres Is Nothing Then

                Nastavte rres = rc

            inak

                Nastaviť rres = Union(rc, rres)

            End If

        End If

    ďalšie

    'ak existuje spojenie, vyberte všetky bunky s takýmito kontrolami údajov

    If Not rres Is Nothing Then

        rres.Vybrať

' rres.Interior.Color = vbRed 'ak chcete zvýrazniť farbou

    End If

End Sub

V editore makier je potrebné urobiť štandardný modul a potom tam vložiť tento text. Potom zavolajte okno makra pomocou kombinácie klávesov Alt + F8 a potom vyberte naše makro a kliknite na tlačidlo „Spustiť“. Pri používaní tohto kódu je potrebné mať na pamäti niekoľko vecí:

1. Skôr než vyhľadáte odkaz, ktorý už nie je relevantný, musíte najprv určiť, ako vyzerá odkaz, prostredníctvom ktorého je vytvorený. Ak to chcete urobiť, prejdite do ponuky „Údaje“ a nájdite tam položku „Zmeniť odkazy“. Potom sa musíte pozrieť na názov súboru a uviesť ho v úvodzovkách. Napríklad takto: Const sToFndLink$ = „*predaj 2018*“
2. Meno je možné napísať nie celé, ale jednoducho nahradiť nepotrebné znaky hviezdičkou. A v úvodzovkách napíšte názov súboru malými písmenami. V tomto prípade Excel nájde na konci všetky súbory, ktoré takýto reťazec obsahujú.
3. Tento kód dokáže skontrolovať iba odkazy v hárku, ktorý je momentálne aktívny.
4. Pomocou tohto makra môžete vybrať iba bunky, ktoré našlo. Všetko musíte odstrániť ručne. To je plus, pretože všetko môžete znova skontrolovať.
5. Môžete tiež urobiť bunky zvýraznené špeciálnou farbou. Ak to chcete urobiť, odstráňte apostrof pred týmto riadkom. rres.Interior.Color = vbČervená

Zvyčajne po dokončení krokov popísaných v pokynoch vyššie by už nemali existovať žiadne zbytočné pripojenia. Ak sa však niektoré z nich v dokumente nachádzajú a nemôžete ich z jedného alebo druhého dôvodu odstrániť (typickým príkladom je zabezpečenie údajov v hárku), môžete použiť inú postupnosť akcií. Tento pokyn je platný len pre verzie 2007 a vyššie.

1. Vytvárame záložnú kópiu dokumentu.
2. Otvorte tento dokument pomocou archivátora. Môžete použiť ktorýkoľvek, ktorý podporuje formát ZIP, ale poslúži aj WinRar, aj ten zabudovaný vo Windowse.
3. V archíve, ktorý sa zobrazí, musíte nájsť priečinok xl a potom otvoriť externé odkazy.
4. Tento priečinok obsahuje všetky externé odkazy, z ktorých každý zodpovedá súboru v tvare externalLink1.xml. Všetky sú len očíslované, a preto používateľ nemá možnosť pochopiť, o aký druh spojenia ide. Aby ste pochopili, aký druh pripojenia, musíte otvoriť priečinok _rels a pozrieť sa tam.
5. Potom odstránime všetky alebo konkrétne odkazy na základe toho, čo sa dozvieme v súbore externalLinkX.xml.rels.
6. Potom otvoríme náš súbor pomocou Excelu. Objaví sa informácia o chybe, ako napríklad „Chyba v časti obsahu v knihe“. Dávame súhlas. Potom sa zobrazí ďalšie dialógové okno. Zatvoríme to.

Potom by mali byť všetky odkazy odstránené.