Jak přerušit odkazy v Excelu

Komunikace je v Excelu velmi užitečná funkce. Uživatelé totiž velmi často musejí využívat informace z jiných souborů. V některých situacích ale mohou způsobit více škody než užitku. Pokud totiž například posíláte tyto soubory poštou, odkazy nefungují. Dnes si povíme podrobněji, co dělat, abychom se takovému problému vyhnuli.

Co jsou vztahy v Excelu

Vztahy v Excelu jsou velmi často používány ve spojení s funkcemi jako např VPRzískat informace z jiného sešitu. Může mít podobu speciálního odkazu, který obsahuje adresu nejen buňky, ale i knihy, ve které se data nacházejí. Ve výsledku takový odkaz vypadá asi takto: =VLOOKUP(A2;'[Prodej 2018.xlsx]Přehled'!$A:$F;4;0). Nebo pro jednodušší reprezentaci uveďte adresu v následujícím tvaru: ='[Sales 2018.xlsx]Report'!$A1. Pojďme analyzovat každý z prvků odkazu tohoto typu:

  1. [Prodeje 2018.xlsx]. Tento fragment obsahuje odkaz na soubor, ze kterého chcete získat informace. Říká se mu také zdroj.
  2. Fotky. Použili jsme následující název, ale není to název, který by měl být. Tento blok obsahuje název listu, ve kterém potřebujete najít informace.
  3. $A:$F a $A1 – adresa buňky nebo rozsahu obsahující data obsažená v tomto dokumentu.

Proces vytváření odkazu na externí dokument se ve skutečnosti nazývá propojení. Poté, co zaregistrujeme adresu buňky obsažené v jiném souboru, změní se obsah záložky „Data“. Aktivuje se totiž tlačítko „Změnit připojení“, pomocí kterého může uživatel upravit stávající připojení.

Podstata problému

S používáním odkazů zpravidla nevznikají žádné další potíže. I když nastane situace, kdy se buňky změní, pak se automaticky aktualizují všechny odkazy. Ale pokud již přejmenujete samotný sešit nebo jej přesunete na jinou adresu, Excel se stane bezmocným. Proto vytváří následující zprávu.

Jak přerušit odkazy v Excelu

Zde má uživatel dvě možné možnosti, jak v této situaci jednat. Může kliknout na „Pokračovat“ a změny nebudou aktualizovány, nebo může kliknout na tlačítko „Změnit asociace“, pomocí kterého je může aktualizovat ručně. Po kliknutí na toto tlačítko se zobrazí další okno, ve kterém bude možné změnit odkazy, kde je uvedeno, kde se správný soubor momentálně nachází a jak se nazývá.

Jak přerušit odkazy v Excelu

Kromě toho můžete odkazy upravovat pomocí odpovídajícího tlačítka umístěného na kartě „Data“. Uživatel může také zjistit, že spojení je přerušeno, chybou #LINK, která se objeví, když Excel nemůže získat přístup k informacím umístěným na konkrétní adrese, protože samotná adresa je neplatná.

Jak odpojit v excelu

Jednou z nejjednodušších metod řešení výše popsané situace v případě, že nemůžete sami aktualizovat umístění odkazovaného souboru, je smazání samotného odkazu. To je obzvláště snadné, pokud dokument obsahuje pouze jeden odkaz. Chcete-li to provést, musíte provést následující posloupnost kroků:

  1. Otevřete nabídku „Data“.
  2. Najdeme sekci „Připojení“ a tam – možnost „Změnit připojení“.
  3. Poté klikněte na „Odpojit“.

Pokud máte v úmyslu poslat tuto knihu poštou jiné osobě, důrazně doporučujeme, abyste tak učinili předem. Koneckonců, po smazání odkazů se všechny hodnoty, které jsou obsaženy v jiném dokumentu, automaticky načtou do souboru, použijí se ve vzorcích a místo adresy buňky se informace v odpovídajících buňkách jednoduše převedou na hodnoty .

Jak odpojit všechny knihy

Pokud je však počet odkazů příliš velký, může jejich ruční odstranění trvat dlouho. Chcete-li tento problém vyřešit najednou, můžete použít speciální makro. Je v doplňku VBA-Excel. Musíte ji aktivovat a přejít na stejnojmennou kartu. Bude zde sekce „Odkazy“, ve které musíme kliknout na tlačítko „Přerušit všechny odkazy“.

Jak přerušit odkazy v Excelu

Kód VBA

Pokud není možné tento doplněk aktivovat, můžete si makro vytvořit sami. Chcete-li to provést, otevřete editor jazyka Visual Basic stisknutím kláves Alt + F11 a do pole pro zadání kódu napište následující řádky.

Sub UnlinkWorkBooks()

    Ztlumit WbLinks

    Tlumené a stejně dlouhé

    Vyberte Case MsgBox („Všechny odkazy na jiné knihy budou z tohoto souboru odstraněny a vzorce odkazující na jiné knihy budou nahrazeny hodnotami.“ & vbCrLf & „Opravdu chcete pokračovat?“, 36, „Odpojit?“ )

    Případ 7′ č

        Konec Sub

    Konec Vybrat

    WbLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

    If Not IsEmpty (WbLinks) Then

        Pro i = 1 až UBound (WbLinks)

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

        další

    jiný

        MsgBox "V tomto souboru nejsou žádné odkazy na jiné knihy.", 64, "Odkazy na jiné knihy"

    End If

End Sub

Jak zlomit vazby pouze ve vybraném rozsahu

Čas od času je počet odkazů velmi velký a uživatel se obává, že po smazání jednoho z nich nebude možné vrátit vše zpět, pokud byly některé nadbytečné. To je ale problém, kterému se lze snadno vyhnout. Chcete-li to provést, musíte vybrat rozsah, ve kterém chcete odstranit odkazy, a poté je odstranit. Chcete-li to provést, musíte provést následující posloupnost akcí:

  1. Vyberte datovou sadu, kterou je třeba upravit.
  2. Nainstalujte doplněk VBA-Excel a poté přejděte na příslušnou kartu.
  3. Dále najdeme nabídku „Odkazy“ a klikneme na tlačítko „Přerušit odkazy ve vybraných rozsazích“.

Jak přerušit odkazy v Excelu

Poté budou všechny odkazy ve vybrané sadě buněk odstraněny.

Co dělat, když vazby nejsou přerušené

Všechno výše uvedené zní dobře, ale v praxi vždy existují určité nuance. Může například nastat situace, kdy se vazby nezpřetrhají. V tomto případě se stále zobrazuje dialogové okno s informací, že není možné automaticky aktualizovat odkazy. Co dělat v této situaci?

  1. Nejprve musíte zkontrolovat, zda jsou v pojmenovaných rozsazích obsaženy nějaké informace. Chcete-li to provést, stiskněte kombinaci kláves Ctrl + F3 nebo otevřete kartu „Vzorce“ – „Správce jmen“. Pokud je název souboru plný, stačí jej upravit nebo zcela odstranit. Před odstraněním pojmenovaných rozsahů je třeba zkopírovat soubor do jiného umístění, abyste se mohli vrátit k původní verzi, pokud byly provedeny nesprávné kroky.
  2. Pokud nemůžete problém vyřešit odstraněním jmen, můžete zkontrolovat podmíněné formátování. Na buňky v jiné tabulce lze odkazovat v pravidlech podmíněného formátování. Chcete-li to provést, vyhledejte odpovídající položku na kartě „Domů“ a poté klikněte na tlačítko „Správa souborů“. Jak přerušit odkazy v Excelu

    Normálně vám Excel nedává možnost zadat adresu jiných sešitů v podmíněném formátování, ale ano, pokud odkazujete na pojmenovaný rozsah s odkazem na jiný soubor. Obvykle i po odstranění odkazu odkaz zůstává. Není problém takový odkaz odstranit, protože odkaz ve skutečnosti nefunguje. Proto se nestane nic špatného, ​​pokud jej odstraníte.

Můžete také použít funkci „Kontrola dat“, abyste zjistili, zda neexistují zbytečné odkazy. Odkazy obvykle zůstávají, pokud je použit typ ověření dat „Seznam“. Co ale dělat, když je buněk hodně? Je opravdu nutné kontrolovat postupně každý z nich? Samozřejmě že ne. Přece jen to bude trvat hodně dlouho. K výraznému uložení je proto potřeba použít speciální kód.

Možnost explicitní

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

Autor: The_Prist(Shcherbakov Dmitry)

' Profesionální vývoj aplikací pro MS Office jakékoliv složitosti

' Vedení školení na MS Excel

https://www.excel-vba.ru

' [e-mail chráněn]

'WebMoney-R298726502453; Yandex.Money — 41001332272872

Účel:

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

Sub FindErrLink()

    'Musíme se podívat do odkazu Data -Change links na zdrojový soubor

    'a vložte sem klíčová slova malými písmeny (část názvu souboru)

    'hvězdička pouze nahrazuje libovolný počet znaků, takže se nemusíte starat o přesné jméno

    Const sToFndLink$ = „*prodej 2018*“

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

    'definovat všechny buňky s ověřením dat

    On Error Resume Next

    Nastavit rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)

    If rr Is Nothing Then

        MsgBox „Na aktivním listu nejsou žádné buňky s ověřením dat“, vbInformation, „www.excel-vba.ru“

        Konec Sub

    End If

    Při chybě GoTo 0

    'zkontrolujte každou buňku pro odkazy

    Za každý ř. V rr

        „Pro případ, že přeskočíme chyby – i to se může stát

        „ale naše spojení musí být bez nich a určitě se najdou

        s = ""

        On Error Resume Next

        s = rc.Validation.Formula1

        Při chybě GoTo 0

        'found – vše shromažďujeme v samostatném sortimentu

        If LCase(s) Like sToFndLink Then

            If rres Is Nothing Then

                Nastavte rres = rc

            jiný

                Nastavit rres = Union(rc, rres)

            End If

        End If

    další

    'pokud existuje spojení, vyberte všechny buňky s takovými kontrolami dat

    If Not rres Is Nothing Then

        rres.Vybrat

' rres.Interior.Color = vbRed 'pokud chcete zvýraznit barvou

    End If

End Sub

V editoru maker je potřeba udělat standardní modul a tam tento text vložit. Poté zavolejte okno makra pomocí kombinace kláves Alt + F8 a poté vyberte naše makro a klikněte na tlačítko „Spustit“. Při používání tohoto kódu je třeba mít na paměti několik věcí:

  1. Než budete hledat odkaz, který již není relevantní, musíte nejprve určit, jak vypadá odkaz, jehož prostřednictvím je vytvořen. Chcete-li to provést, přejděte do nabídky „Data“ a najděte tam položku „Změnit odkazy“. Poté se musíte podívat na název souboru a zadat jej v uvozovkách. Například takto: Const sToFndLink$ = „*prodej 2018*“
  2. Je možné napsat jméno ne celé, ale jednoduše nahradit nepotřebné znaky hvězdičkou. A v uvozovkách napište název souboru malými písmeny. V tomto případě Excel najde na konci všechny soubory, které takový řetězec obsahují.
  3. Tento kód je schopen zkontrolovat pouze odkazy v listu, který je aktuálně aktivní.
  4. Pomocí tohoto makra můžete vybrat pouze buňky, které nalezlo. Vše musíte smazat ručně. To je plus, protože vše můžete znovu zkontrolovat.
  5. Buňky můžete také zvýraznit speciální barvou. Chcete-li to provést, odstraňte apostrof před tímto řádkem. rres.Interior.Color = vbRed

Obvykle by po dokončení kroků popsaných ve výše uvedených pokynech neměly existovat žádné zbytečné spoje. Pokud ale některé z nich v dokumentu jsou a vy je z toho či onoho důvodu nemůžete odstranit (typickým příkladem je zabezpečení dat v listu), můžete použít jinou sekvenci akcí. Tento pokyn platí pouze pro verze 2007 a vyšší.

  1. Vytvoříme záložní kopii dokumentu.
  2. Otevřete tento dokument pomocí archivátoru. Můžete použít jakýkoli, který podporuje formát ZIP, ale bude fungovat i WinRar, stejně jako ten zabudovaný ve Windows.
  3. V archivu, který se objeví, musíte najít složku xl a poté otevřít externalLinks.
  4. Tato složka obsahuje všechny externí odkazy, z nichž každý odpovídá souboru ve tvaru externalLink1.xml. Všechny jsou pouze očíslované, a proto uživatel nemá možnost pochopit, o jaké spojení se jedná. Abyste pochopili, o jaký druh připojení se jedná, musíte otevřít složku _rels a tam se na ni podívat.
  5. Poté odstraníme všechny nebo konkrétní odkazy na základě toho, co se dozvíme v souboru externalLinkX.xml.rels.
  6. Poté otevřeme náš soubor pomocí Excelu. Zobrazí se informace o chybě jako „Chyba v části obsahu v knize“. Dáváme souhlas. Poté se objeví další dialog. Uzavřeme to.

Poté by měly být všechny odkazy odstraněny.

Napsat komentář