Com trencar enllaços a Excel

La comunicació és una característica molt útil a Excel. Després de tot, molt sovint els usuaris han d'utilitzar informació d'altres fitxers. Però en algunes situacions, poden fer més mal que bé. Després de tot, per exemple, si envieu aquests fitxers per correu, els enllaços no funcionen. Avui parlarem amb més detall sobre què fer per evitar aquest problema.

Què són les relacions a Excel

Les relacions a Excel s'utilitzen molt sovint juntament amb funcions com ara VPRper obtenir informació d'un altre llibre de treball. Pot prendre la forma d'un enllaç especial que conté l'adreça no només de la cel·la, sinó també del llibre on es troben les dades. Com a resultat, aquest enllaç té un aspecte semblant a això: =CERCAV(A2;'[Informe de vendes 2018.xlsx]'!$A:$F;4;0). O, per a una representació més senzilla, representeu l'adreça de la forma següent: ='[Vendes 2018.xlsx]Informe'!$A1. Analitzem cadascun dels elements d'enllaç d'aquest tipus:

1. [Vendes 2018.xlsx]. Aquest fragment conté un enllaç al fitxer del qual voleu obtenir informació. També s'anomena font.
2. fotos. Hem utilitzat el nom següent, però aquest no és el que hauria de ser. Aquest bloc conté el nom del full en el qual necessiteu trobar informació.
3. $A:$F i $A1 – l'adreça d'una cel·la o un interval que conté dades contingudes en aquest document.

De fet, el procés de creació d'un enllaç a un document extern s'anomena enllaç. Després d'haver registrat l'adreça de la cel·la continguda en un altre fitxer, el contingut de la pestanya "Dades" canvia. És a dir, el botó "Canvia les connexions" s'activa, amb l'ajuda del qual l'usuari pot editar les connexions existents.

L'essència del problema

Per regla general, no hi ha dificultats addicionals per utilitzar els enllaços. Fins i tot si es produeix una situació en què les cel·les canvien, tots els enllaços s'actualitzen automàticament. Però si ja canvieu el nom del llibre de treball o el moveu a una adreça diferent, Excel es torna impotent. Per tant, produeix el missatge següent.

Aquí, l'usuari té dues opcions possibles sobre com actuar en aquesta situació. Pot fer clic a "Continuar" i aleshores els canvis no s'actualitzaran, o pot fer clic al botó "Canvia associacions", amb el qual pot actualitzar-los manualment. Després de fer clic en aquest botó, apareixerà una finestra addicional en la qual es podrà canviar els enllaços, indicant on es troba el fitxer correcte en aquest moment i com es diu.

A més, podeu editar els enllaços mitjançant el botó corresponent situat a la pestanya "Dades". L'usuari també pot esbrinar que la connexió està trencada per l'error #LINK, que apareix quan Excel no pot accedir a la informació ubicada en una adreça concreta pel fet que la mateixa adreça no és vàlida.

Com desenllaçar en excel

Un dels mètodes més senzills per resoldre la situació descrita anteriorment en cas que no puguis actualitzar tu mateix la ubicació del fitxer enllaçat és esborrar l'enllaç. Això és especialment fàcil de fer si el document només conté un enllaç. Per fer-ho, heu de realitzar la següent seqüència de passos:

1. Obriu el menú "Dades".
2. Trobem la secció "Connexions" i allà l'opció "Canviar connexions".
3. Després d'això, feu clic a "Desenllaçar".

Si teniu intenció d'enviar aquest llibre a una altra persona, us recomanem que ho feu amb antelació. Després de tot, després de suprimir els enllaços, tots els valors que es troben en un altre document es carregaran automàticament al fitxer, s'utilitzaran en fórmules i, en lloc de l'adreça de la cel·la, la informació de les cel·les corresponents es transformarà simplement en valors. .

Com desenllaçar tots els llibres

Però si el nombre d'enllaços és massa gran, suprimir-los manualment pot trigar molt de temps. Per resoldre aquest problema d'una vegada, podeu utilitzar una macro especial. Es troba al complement VBA-Excel. Cal activar-lo i anar a la pestanya del mateix nom. Hi haurà una secció "Enllaços", en la qual haurem de fer clic al botó "Trenqueu tots els enllaços".

Codi VBA

Si no és possible activar aquest complement, podeu crear una macro vosaltres mateixos. Per fer-ho, obriu l'editor de Visual Basic prement les tecles Alt + F11 i escriviu les línies següents al camp d'entrada del codi.

Sub UnlinkWorkBooks()

    Dim WbLinks

    Dim i As Long

    Seleccioneu Case MsgBox ("Totes les referències a altres llibres s'eliminaran d'aquest fitxer i les fórmules que fan referència a altres llibres es substituiran per valors." & vbCrLf & "Esteu segur que voleu continuar?", 36, "Desenllaçar?" )

    Cas 7′ núm

        Surt del sub

    Selecció final

    WbLinks = ActiveWorkbook.LinkSources (Tipus:=xlLinkTypeExcelLinks)

    Si no és buit (WbLinks), aleshores

        Per i = 1 a UBound(WbLinks)

            ActiveWorkbook.BreakLink Nom:=WbLinks(i), Tipus:=xlLinkTypeExcelLinks

        Següent

    else

        MsgBox “No hi ha enllaços a altres llibres en aquest fitxer.”, 64, “Enllaços a altres llibres”

    End If

End Sub

Com trencar llaços només a l'interval seleccionat

De tant en tant, el nombre d'enllaços és molt gran i l'usuari té por que, després d'esborrar-ne un, no sigui possible tornar-ho tot si algun era superflu. Però aquest és un problema que és fàcil d'evitar. Per fer-ho, heu de seleccionar l'interval en què voleu suprimir els enllaços i després suprimir-los. Per fer-ho, heu de realitzar la següent seqüència d'accions:

1. Seleccioneu el conjunt de dades que cal modificar.
2. Instal·leu el complement VBA-Excel i, a continuació, aneu a la pestanya adequada.
3. A continuació, trobem el menú “Enllaços” i fem clic al botó “Trenqueu els enllaços als rangs seleccionats”.

Després d'això, se suprimiran tots els enllaços del conjunt de cel·les seleccionat.

Què fer si no es trenquen els llaços

Tot l'anterior sona bé, però a la pràctica sempre hi ha alguns matisos. Per exemple, pot haver-hi una situació en què els llaços no es trenquin. En aquest cas, encara apareix un quadre de diàleg que indica que no és possible actualitzar automàticament els enllaços. Què fer en aquesta situació?

1. En primer lloc, heu de comprovar si hi ha informació als intervals nomenats. Per fer-ho, premeu la combinació de tecles Ctrl + F3 o obriu la pestanya "Fórmules" - "Gestor de noms". Si el nom del fitxer és complet, només heu d'editar-lo o eliminar-lo del tot. Abans d'esborrar els intervals amb nom, heu de copiar el fitxer a una altra ubicació perquè pugueu tornar a la versió original si s'han fet els passos equivocats.
2. Si no podeu resoldre el problema eliminant noms, podeu comprovar el format condicional. Les cel·les d'una altra taula es poden fer referència a regles de format condicional. Per fer-ho, cerqueu l'element corresponent a la pestanya "Inici" i feu clic al botó "Gestió de fitxers".

   Normalment, Excel no us ofereix la possibilitat de donar l'adreça d'altres llibres de treball en format condicional, però ho feu si feu referència a un interval amb nom amb una referència a un altre fitxer. Normalment, fins i tot després d'eliminar l'enllaç, l'enllaç es manté. No hi ha cap problema per eliminar aquest enllaç, perquè de fet l'enllaç no funciona. Per tant, no passarà res dolent si l'elimineu.

També podeu utilitzar la funció "Comprovació de dades" per esbrinar si hi ha enllaços innecessaris. Els enllaços solen romandre si s'utilitza el tipus de validació de dades "Llista". Però què fer si hi ha moltes cèl·lules? És realment necessari comprovar cadascun d'ells de manera seqüencial? És clar que no. Després de tot, trigarà molt de temps. Per tant, cal utilitzar un codi especial per desar-lo de manera significativa.

Opció Explícita

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

Autor: The_Prist (Shcherbakov Dmitry)

' Desenvolupament professional d'aplicacions per MS Office de qualsevol complexitat

' Realització de formacions en MS Excel

https://www.excel-vba.ru

' [correu electrònic protegit]

'WebMoney—R298726502453; Yandex.Money — 41001332272872

' Propòsit:

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

Sub FindErrLink()

    'hem de mirar a l'enllaç Data -Change links al fitxer font

    'i poseu aquí les paraules clau en minúscules (part del nom del fitxer)

    "L'asterisc només substitueix qualsevol nombre de caràcters, de manera que no us haureu de preocupar pel nom exacte

    Const sToFndLink$ = "*vendes 2018*"

    Dim rr com a rang, rc com a rang, rres com a rang, s$

    'definiu totes les cel·les amb validació de dades

    On Error Resume Next

    Estableix rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)

    Si rr no és res, aleshores

        MsgBox "No hi ha cap cel·la amb validació de dades al full actiu", vbInformation, "www.excel-vba.ru"

        Surt del sub

    End If

    En cas d'error GoTo 0

    'comproveu cada cel·la per trobar enllaços

    Per a cada rc In rr

        "Per si de cas, saltem errors; això també pot passar

        "Però les nostres connexions han de ser sense elles i definitivament es trobaran

        s = «»

        On Error Resume Next

        s = rc.Validació.Fórmula1

        En cas d'error GoTo 0

        'trobat: ho recollim tot en un rang separat

        Si LCase(s) com sToFndLink, aleshores

            Si rres no és res, aleshores

                Estableix rres = rc

            else

                Estableix rres = Unió (rc, rres)

            End If

        End If

    Següent

    'si hi ha una connexió, seleccioneu totes les cel·les amb aquestes comprovacions de dades

    Si no és res, aleshores

        rres.Seleccionar

' rres.Interior.Color = vbRed 'si voleu ressaltar amb color

    End If

End Sub

Cal fer un mòdul estàndard a l'editor de macros, i després inserir-hi aquest text. Després d'això, truqueu a la finestra de macro utilitzant la combinació de tecles Alt + F8 i, a continuació, seleccioneu la nostra macro i feu clic al botó "Executar". Hi ha algunes coses a tenir en compte quan utilitzeu aquest codi:

1. Abans de cercar un enllaç que ja no sigui rellevant, primer heu de determinar com és l'enllaç a través del qual es crea. Per fer-ho, aneu al menú "Dades" i cerqueu l'element "Canvia els enllaços". Després d'això, heu de mirar el nom del fitxer i especificar-lo entre cometes. Per exemple, així: Const sToFndLink$ = "*vendes 2018*"
2. És possible escriure el nom no complet, sinó simplement substituir els caràcters innecessaris amb un asterisc. I entre cometes, escriviu el nom del fitxer en lletres minúscules. En aquest cas, Excel trobarà tots els fitxers que continguin aquesta cadena al final.
3. Aquest codi només pot comprovar si hi ha enllaços al full que està actiu actualment.
4. Amb aquesta macro, només podeu seleccionar les cel·les que ha trobat. Has d'esborrar-ho tot manualment. Això és un avantatge, perquè podeu tornar a comprovar-ho tot.
5. També podeu ressaltar les cel·les amb un color especial. Per fer-ho, traieu l'apòstrof davant d'aquesta línia. rres.Interior.Color = vbRed

Normalment, després de completar els passos descrits a les instruccions anteriors, no hi hauria d'haver més connexions innecessàries. Però si hi ha alguns d'ells al document i no podeu eliminar-los per un motiu o un altre (un exemple típic és la seguretat de les dades en un full), podeu utilitzar una seqüència d'accions diferent. Aquesta instrucció només és vàlida per a les versions 2007 i posteriors.

1. Creem una còpia de seguretat del document.
2. Obriu aquest document mitjançant l'arxivador. Podeu utilitzar qualsevol que admeti el format ZIP, però WinRar també funcionarà, així com el integrat a Windows.
3. A l'arxiu que apareix, heu de trobar la carpeta xl i, a continuació, obrir externalLinks.
4. Aquesta carpeta conté tots els enllaços externs, cadascun dels quals correspon a un fitxer de la forma externalLink1.xml. Tots ells només estan numerats i, per tant, l'usuari no té l'oportunitat d'entendre quin tipus de connexió es tracta. Per entendre quin tipus de connexió, heu d'obrir la carpeta _rels i mirar-la allà.
5. Després d'això, eliminem tots els enllaços o específics, en funció del que aprenem al fitxer externalLinkX.xml.rels.
6. Després d'això, obrim el nostre fitxer amb Excel. Hi haurà informació sobre un error com ara "Error en part del contingut del llibre". Donem el consentiment. Després d'això, apareixerà un altre diàleg. El tanquem.

Després d'això, s'han d'eliminar tots els enllaços.