Hogyan lehet megszakítani a hivatkozásokat Excelben

A kommunikáció nagyon hasznos funkció az Excelben. Végül is a felhasználóknak nagyon gyakran más fájlokból származó információkat kell felhasználniuk. De bizonyos helyzetekben többet ártanak, mint használnak. Végül is, ha például ezeket a fájlokat levélben küldi el, a hivatkozások nem működnek. Ma részletesebben fogunk beszélni arról, mit tegyünk az ilyen probléma elkerülése érdekében.

Mik azok a kapcsolatok az Excelben

Az Excel kapcsolatokat nagyon gyakran olyan függvényekkel együtt használják, mint pl VPRhogy információt szerezzen egy másik munkafüzetből. Ez egy speciális hivatkozás formáját öltheti, amely nemcsak a cella címét tartalmazza, hanem annak a könyvnek a címét is, amelyben az adatok találhatók. Ennek eredményeként egy ilyen hivatkozás valahogy így néz ki: =VLOOKUP(A2;'[Értékesítési 2018.xlsx]Jelentés'!$A:$F;4;0). Vagy az egyszerűbb ábrázolás érdekében ábrázolja a címet a következő formában: ='[Értékesítési 2018.xlsx]Jelentés'!$A1. Elemezzük az egyes ilyen típusú linkelemeket:

1. [Értékesítési 2018.xlsx]. Ez a töredék egy hivatkozást tartalmaz arra a fájlra, amelyből információkat szeretne szerezni. Forrásnak is nevezik.
2. képek. A következő nevet használtuk, de nem ennek kellene lennie. Ez a blokk annak a lapnak a nevét tartalmazza, amelyben információkat kell találnia.
3. $A:$F és $A1 – a dokumentumban található adatokat tartalmazó cella vagy tartomány címe.

Valójában a külső dokumentumra mutató hivatkozás létrehozásának folyamatát linkelésnek nevezik. Miután egy másik fájlban lévő cella címét regisztráltuk, az „Adatok” fül tartalma megváltozik. Ugyanis aktívvá válik a „Kapcsolatok módosítása” gomb, melynek segítségével a felhasználó szerkesztheti a meglévő kapcsolatokat.

A probléma lényege

A hivatkozások használatakor általában nem merülnek fel további nehézségek. Még ha olyan helyzet is adódik, amelyben a cellák megváltoznak, az összes hivatkozás automatikusan frissül. De ha már átnevezi magát a munkafüzetet, vagy áthelyezi egy másik címre, az Excel erőtlenné válik. Ezért a következő üzenetet produkálja.

Itt a felhasználónak két lehetséges módja van arra, hogyan kell eljárni ebben a helyzetben. A „Tovább” gombra kattintva a változtatások nem frissülnek, vagy rákattinthat az „Asszociációk módosítása” gombra, amellyel manuálisan frissítheti azokat. Miután rákattintunk erre a gombra, egy további ablak jelenik meg, amelyben lehetőség nyílik a hivatkozások módosítására, jelezve, hogy pillanatnyilag hol található a megfelelő fájl, és mi a neve.

Ezenkívül szerkesztheti a hivatkozásokat az „Adatok” fülön található megfelelő gombbal. A felhasználó azt is megtudhatja, hogy a kapcsolat megszakadt a #LINK hiba miatt, amely akkor jelenik meg, ha az Excel nem tud hozzáférni egy adott címen található információkhoz, mert maga a cím érvénytelen.

Hogyan lehet leválasztani Excelben

Az egyik legegyszerűbb módszer a fent leírt helyzet megoldására abban az esetben, ha Ön nem tudja frissíteni a hivatkozott fájl helyét, maga a hivatkozás törlése. Ez különösen akkor egyszerű, ha a dokumentum csak egy hivatkozást tartalmaz. Ehhez a következő lépések sorozatát kell végrehajtania:

1. Nyissa meg az „Adatok” menüt.
2. Megtaláljuk a „Kapcsolatok” részt, és ott a „Kapcsolatok módosítása” lehetőséget.
3. Ezt követően kattintson a „Leválasztás” gombra.

Ha ezt a könyvet postán szeretné elküldeni egy másik személynek, erősen ajánlott ezt előre megtenni. Végül is a hivatkozások törlése után a másik dokumentumban található összes érték automatikusan betöltődik a fájlba, képletekben kerül felhasználásra, és a cella címe helyett a megfelelő cellákban lévő információk egyszerűen átalakulnak értékekké. .

Az összes könyv összekapcsolásának megszüntetése

De ha a hivatkozások száma túl nagy lesz, a kézi törlésük sokáig tarthat. A probléma egy menetben történő megoldásához használhat egy speciális makrót. A VBA-Excel kiegészítőben található. Aktiválnia kell, és mennie kell az azonos nevű lapra. Lesz egy „Linkek” rész, amelyben a „Minden link megszakítása” gombra kell kattintanunk.

VBA kód

Ha nem lehetséges aktiválni ezt a kiegészítőt, saját maga is létrehozhat makrót. Ehhez nyissa meg a Visual Basic szerkesztőt az Alt + F11 billentyűk lenyomásával, és írja be a következő sorokat a kódbeviteli mezőbe.

Al UnlinkWorkBooks()

    Dim WbLinks

    Dim és Am Long

    Válassza a Case MsgBox lehetőséget ("Minden más könyvre való hivatkozás eltávolításra kerül ebből a fájlból, és a más könyvekre utaló képletek helyére értékek kerülnek." & vbCrLf & "Biztosan folytatja?", 36, "Leválasztás?" )

    7′ sz

        Exit Sub

    Vége kiválasztása

    WbLinks = ActiveWorkbook.LinkSources(Típus:=xlLinkTypeExcelLinks)

    Ha nem IsEmpty(WbLinks) Akkor

        Ha i = 1 - UBound (Wblinkek)

            ActiveWorkbook.BreakLink Név:=WbLinks(i), Típus:=xlLinkTypeExcelLinks

        Következő

    más

        MsgBox „A fájlban nincsenek hivatkozások más könyvekre.”, 64, „Más könyvekre mutató hivatkozások”

    Ha véget

End Sub

Hogyan szakítsuk meg a kapcsolatokat csak a kiválasztott tartományban

Időről időre a linkek száma nagyon nagy, és a felhasználó attól tart, hogy az egyik törlése után nem lehet mindent visszaadni, ha néhány felesleges volt. De ez egy olyan probléma, amelyet könnyű elkerülni. Ehhez ki kell választani azt a tartományt, amelyben törölni szeretné a hivatkozásokat, majd törölni kell azokat. Ehhez a következő műveletsort kell végrehajtania:

1. Válassza ki a módosítani kívánt adatkészletet.
2. Telepítse a VBA-Excel bővítményt, majd lépjen a megfelelő lapra.
3. Ezután keressük meg a „Linkek” menüt, és kattintsunk a „Hivatkozások megszakítása a kiválasztott tartományokban” gombra.

Ezt követően a kiválasztott cellakészletben lévő összes hivatkozás törlődik.

Mi a teendő, ha a kapcsolatok nem szakadnak meg

A fentiek mindegyike jól hangzik, de a gyakorlatban mindig vannak árnyalatok. Például előfordulhat olyan helyzet, amikor a kapcsolatok nem szakadnak meg. Ebben az esetben továbbra is megjelenik egy párbeszédpanel, amely szerint a hivatkozások automatikus frissítése nem lehetséges. Mi a teendő ebben a helyzetben?

1. Először is ellenőriznie kell, hogy a megnevezett tartományok tartalmaznak-e információkat. Ehhez nyomja meg a Ctrl + F3 billentyűkombinációt, vagy nyissa meg a „Képletek” fület - „Névkezelő”. Ha a fájlnév tele van, akkor csak szerkesztenie kell, vagy teljesen el kell távolítania. A megnevezett tartományok törlése előtt át kell másolnia a fájlt egy másik helyre, hogy helytelen lépések esetén visszatérhessen az eredeti verzióhoz.
2. Ha nem tudja megoldani a problémát a nevek eltávolításával, ellenőrizze a feltételes formázást. Egy másik táblázat celláira a feltételes formázási szabályokban lehet hivatkozni. Ehhez keresse meg a megfelelő elemet a „Kezdőlap” fülön, majd kattintson a „Fájlkezelés” gombra.

   Általában az Excel nem ad lehetőséget más munkafüzetek címének megadására feltételes formázással, de igen, ha egy elnevezett tartományra hivatkozik egy másik fájlra. Általában még a hivatkozás eltávolítása után is a hivatkozás megmarad. Nem okoz gondot egy ilyen hivatkozás eltávolítása, mert a hivatkozás valójában nem működik. Ezért semmi rossz nem fog történni, ha eltávolítja.

Használhatja az „Adatellenőrzés” funkciót is, hogy megtudja, vannak-e szükségtelen hivatkozások. A linkek általában megmaradnak, ha a „Lista” típusú adatellenőrzést használják. De mi a teendő, ha sok a sejt? Valóban szükséges mindegyiket egymás után ellenőrizni? Természetesen nem. Végül is nagyon sokáig fog tartani. Ezért a jelentős mentéshez speciális kódot kell használnia.

Explicit lehetőség

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

Szerző: The_Prist (Shcherbakov Dmitry)

' Alkalmazások professzionális fejlesztése bármilyen bonyolultságú MS Office számára

'Tréningek lebonyolítása MS Excelen

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

' [email protected]

„WebMoney – R298726502453; Yandex.Money — 41001332272872

' Célja:

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

Sub FindErrLink()

    „Meg kell néznünk a Data -Change links linket a forrásfájlra

    és írja be a kulcsszavakat kisbetűvel (a fájlnév egy része)

    A csillag tetszőleges számú karaktert helyettesít, így nem kell aggódnia a pontos név miatt

    Const sToFndLink$ = "*értékesítés 2018*"

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

    'határozza meg az összes adatellenőrzéssel rendelkező cellát

    On Error Resume Next

    Set rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)

    Ha rr Semmi Akkor

        MsgBox „Nincsenek adatellenőrzéssel rendelkező cellák az aktív lapon”, vbInformation, „www.excel-vba.ru”

        Exit Sub

    Ha véget

    Hiba történt GoTo 0

    'Ellenőrizze az egyes cellákat a hivatkozásokért

    Minden rc-hez In rr

        „Minden esetre kihagyjuk a hibákat – ez is előfordulhat

        'de kapcsolatainknak ezek nélkül kell lenniük, és biztosan megtaláljuk őket

        s = «»

        On Error Resume Next

        s = rc.Validation.Formula1

        Hiba történt GoTo 0

        'talált – mindent külön tartományban gyűjtünk

        Ha az LCase(k) Like sToFndLink Akkor

            Ha rres Semmi Akkor

                Állítsa be: rres = rc

            más

                Az rres beállítása = Unió(rc, rres)

            Ha véget

        Ha véget

    Következő

    'ha van kapcsolat, jelölje ki az összes ilyen adatellenőrzéssel rendelkező cellát

    Ha nem rres Semmi Akkor

        rres.Válassza ki

' rres.Interior.Color = vbRed 'ha színnel szeretné kiemelni

    Ha véget

End Sub

A makrószerkesztőben el kell készíteni egy szabványos modult, majd be kell illeszteni oda ezt a szöveget. Ezután hívja meg a makró ablakot az Alt + F8 billentyűkombinációval, majd válassza ki a makrónkat, és kattintson a „Futtatás” gombra. A kód használatakor szem előtt kell tartani néhány dolgot:

1. Mielőtt olyan hivatkozást keresne, amely már nem releváns, először meg kell határoznia, hogy néz ki a hivatkozás, amelyen keresztül létrehozták. Ehhez lépjen az „Adatok” menübe, és ott keresse meg a „Hivatkozások módosítása” elemet. Ezt követően meg kell nézni a fájl nevét, és idézőjelbe kell írni. Például így: Const sToFndLink$ = "*értékesítés 2018*"
2. Lehetséges, hogy a nevet nem teljes egészében írja be, hanem egyszerűen helyettesítse a felesleges karaktereket csillaggal. És idézőjelbe írja be a fájl nevét kis betűkkel. Ebben az esetben az Excel minden olyan fájlt megtalál, amely ilyen karakterláncot tartalmaz a végén.
3. Ez a kód csak a jelenleg aktív munkalap linkjeit tudja ellenőrizni.
4. Ezzel a makróval csak azokat a cellákat jelölheti ki, amelyeket megtalált. Mindent kézzel kell törölnie. Ez egy plusz, mert mindent újra ellenőrizhet.
5. A cellákat speciális színnel is kiemelheti. Ehhez távolítsa el az aposztrófot a sor előtt. rres.Interior.Color = vbRed

Általában a fenti utasításokban leírt lépések végrehajtása után nem szabad többé szükségtelen kapcsolatokat létrehozni. De ha van belőlük néhány a dokumentumban, és valamilyen okból nem tudja eltávolítani őket (tipikus példa a lapon lévő adatok biztonsága), akkor más műveletsort használhat. Ez az utasítás csak a 2007-es és újabb verziókra érvényes.

1. A dokumentumról biztonsági másolatot készítünk.
2. Nyissa meg ezt a dokumentumot az archiváló segítségével. Bármelyik használható, amely támogatja a ZIP formátumot, de működni fog a WinRar is, valamint a Windowsba épített.
3. A megjelenő archívumban meg kell keresnie az xl mappát, majd meg kell nyitnia az externalLinks alkalmazást.
4. Ez a mappa tartalmazza az összes külső hivatkozást, amelyek mindegyike egy externalLink1.xml formátumú fájlnak felel meg. Mindegyik csak számozott, ezért a felhasználónak nincs lehetősége megérteni, hogy milyen kapcsolatról van szó. A kapcsolat típusának megértéséhez meg kell nyitnia a _rels mappát, és ott meg kell néznie.
5. Ezt követően az externalLinkX.xml.rels fájlból tanultak alapján eltávolítjuk az összes vagy bizonyos hivatkozásokat.
6. Ezt követően Excel segítségével megnyitjuk a fájlunkat. Információk jelennek meg egy olyan hibáról, mint például „Hiba a könyv tartalmának egy részében”. Hozzájárulást adunk. Ezt követően egy másik párbeszédpanel jelenik meg. Lezárjuk.

Ezt követően minden hivatkozást el kell távolítani.