Cum să rupeți legăturile în Excel

Comunicarea este o caracteristică foarte utilă în Excel. La urma urmei, foarte des utilizatorii trebuie să folosească informații din alte fișiere. Dar, în unele situații, pot face mai mult rău decât bine. La urma urmei, de exemplu, dacă trimiteți aceste fișiere prin poștă, linkurile nu funcționează. Astăzi vom vorbi mai detaliat despre ce trebuie făcut pentru a evita o astfel de problemă.

Ce sunt relațiile în Excel

Relațiile în Excel sunt foarte des folosite împreună cu funcții precum VPRpentru a obține informații dintr-un alt registru de lucru. Poate lua forma unui link special care conține adresa nu numai celulei, ci și cărții în care se află datele. Ca rezultat, un astfel de link arată cam așa: =CĂUTAREV(A2;'[Vânzări 2018.xlsx]Raport'!$A:$F;4;0). Sau, pentru o reprezentare mai simplă, reprezentați adresa în următoarea formă: ='[Vânzări 2018.xlsx]Raport'!$A1. Să analizăm fiecare dintre elementele de legătură de acest tip:

1. [Vânzări 2018.xlsx]. Acest fragment conține un link către fișierul din care doriți să obțineți informații. Se mai numește și sursă.
2. Fotografii. Am folosit următorul nume, dar acesta nu este numele care ar trebui să fie. Acest bloc conține numele foii în care trebuie să găsiți informații.
3. $A:$F și $A1 – adresa unei celule sau a unui interval care conține date conținute în acest document.

De fapt, procesul de creare a unui link către un document extern se numește linking. După ce am înregistrat adresa celulei conținute într-un alt fișier, conținutul filei „Date” se modifică. Și anume, devine activ butonul „Modificați conexiunile”, cu ajutorul căruia utilizatorul poate edita conexiunile existente.

Esența problemei

De regulă, nu apar dificultăți suplimentare pentru utilizarea link-urilor. Chiar dacă apare o situație în care celulele se schimbă, atunci toate linkurile sunt actualizate automat. Dar dacă redenumiți deja registrul de lucru sau îl mutați la o altă adresă, Excel devine neputincios. Prin urmare, produce următorul mesaj.

Aici, utilizatorul are două opțiuni posibile pentru cum să acționeze în această situație. El poate face clic pe „Continuare” și apoi modificările nu vor fi actualizate, sau poate face clic pe butonul „Modifica asociații”, cu care le poate actualiza manual. După ce facem clic pe acest buton, va apărea o fereastră suplimentară în care va fi posibilă modificarea legăturilor, indicând unde se află fișierul corect în acest moment și cum se numește.

În plus, puteți edita link-uri prin intermediul butonului corespunzător situat în fila „Date”. De asemenea, utilizatorul poate afla că conexiunea este întreruptă de eroarea #LINK, care apare atunci când Excel nu poate accesa informațiile aflate la o anumită adresă din cauza faptului că adresa în sine este invalidă.

Cum să deconectați în excel

Una dintre cele mai simple metode de a rezolva situația descrisă mai sus în cazul în care nu puteți actualiza singur locația fișierului legat este să ștergeți linkul în sine. Acest lucru este deosebit de ușor de făcut dacă documentul conține un singur link. Pentru a face acest lucru, trebuie să efectuați următoarea secvență de pași:

1. Deschideți meniul „Date”.
2. Găsim secțiunea „Conexiuni”, iar acolo – opțiunea „Schimbați conexiunile”.
3. După aceea, faceți clic pe „Deconectați”.

Dacă intenționați să trimiteți această carte prin poștă unei alte persoane, este foarte recomandat să faceți acest lucru în avans. La urma urmei, după ștergerea legăturilor, toate valorile care sunt conținute într-un alt document vor fi încărcate automat în fișier, utilizate în formule, iar în loc de adresa celulei, informațiile din celulele corespunzătoare vor fi pur și simplu transformate în valori. .

Cum să deconectați toate cărțile

Dar dacă numărul de link-uri devine prea mare, ștergerea manuală a acestora poate dura mult timp. Pentru a rezolva această problemă dintr-o singură mișcare, puteți utiliza o macrocomandă specială. Este în programul de completare VBA-Excel. Trebuie să-l activați și să accesați fila cu același nume. Va exista o secțiune „Legături”, în care trebuie să facem clic pe butonul „Întrerupe toate linkurile”.

Cod VBA

Dacă nu este posibil să activați acest supliment, puteți crea singur o macrocomandă. Pentru a face acest lucru, deschideți editorul Visual Basic apăsând tastele Alt + F11 și scrieți următoarele rânduri în câmpul de introducere a codului.

Sub UnlinkWorkBooks()

    Dim WbLinks

    Dim și As Long

    Selectați Case MsgBox(„Toate referințele la alte cărți vor fi eliminate din acest fișier, iar formulele care se referă la alte cărți vor fi înlocuite cu valori.” & vbCrLf & „Sunteți sigur că doriți să continuați?”, 36, „Deconectați?” )

    Cazul 7′ Nr

        Ieșiți din Sub

    Selectare sfârșit

    WbLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

    Dacă nu este gol (WbLinks), atunci

        Pentru i = 1 To UBound(WbLinks)

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

        Pagina Următoare →

    altfel

        MsgBox „Nu există legături către alte cărți în acest fișier.”, 64, „Legături către alte cărți”

    Final, dacă

End Sub

Cum să rupi legăturile numai în intervalul selectat

Din când în când, numărul de link-uri este foarte mare, iar utilizatorul se teme că, după ștergerea uneia dintre ele, nu va fi posibil să returneze totul înapoi dacă unele au fost de prisos. Dar aceasta este o problemă care este ușor de evitat. Pentru a face acest lucru, trebuie să selectați intervalul în care să ștergeți linkurile și apoi să le ștergeți. Pentru a face acest lucru, trebuie să efectuați următoarea secvență de acțiuni:

1. Selectați setul de date care trebuie modificat.
2. Instalați programul de completare VBA-Excel, apoi accesați fila corespunzătoare.
3. În continuare, găsim meniul „Link-uri” și facem clic pe butonul „Întrerupe linkurile în intervalele selectate”.

După aceea, toate legăturile din setul de celule selectat vor fi șterse.

Ce să faci dacă legăturile nu sunt rupte

Toate cele de mai sus sună bine, dar în practică există întotdeauna câteva nuanțe. De exemplu, poate exista o situație în care legăturile nu sunt rupte. În acest caz, apare în continuare o casetă de dialog care afirmă că nu este posibilă actualizarea automată a legăturilor. Ce să faci în această situație?

1. În primul rând, trebuie să verificați dacă există informații conținute în intervalele denumite. Pentru a face acest lucru, apăsați combinația de taste Ctrl + F3 sau deschideți fila „Formule” – „Manager de nume”. Dacă numele fișierului este complet, atunci trebuie doar să îl editați sau să îl eliminați complet. Înainte de a șterge intervalele denumite, trebuie să copiați fișierul într-o altă locație, astfel încât să puteți reveni la versiunea originală dacă s-au făcut pași greșiți.
2. Dacă nu puteți rezolva problema eliminând nume, puteți verifica formatarea condiționată. Celulele dintr-un alt tabel pot fi referite în regulile de formatare condiționată. Pentru a face acest lucru, găsiți elementul corespunzător în fila „Acasă”, apoi faceți clic pe butonul „Gestionare fișiere”.

   În mod normal, Excel nu vă oferă posibilitatea de a furniza adresa altor registre de lucru în format condiționat, dar o faceți dacă vă referiți la un interval numit cu o referință la un alt fișier. De obicei, chiar și după ce linkul este eliminat, linkul rămâne. Nu există nicio problemă în eliminarea unui astfel de link, deoarece linkul de fapt nu funcționează. Prin urmare, nu se va întâmpla nimic rău dacă îl eliminați.

De asemenea, puteți utiliza funcția „Verificarea datelor” pentru a afla dacă există link-uri inutile. Legăturile rămân de obicei dacă se utilizează tipul de validare a datelor „Lista”. Dar ce să faci dacă există o mulțime de celule? Este cu adevărat necesar să verificăm fiecare dintre ele secvenţial? Desigur că nu. La urma urmei, va dura foarte mult timp. Prin urmare, trebuie să utilizați un cod special pentru a-l salva în mod semnificativ.

Opțiune explicită

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

Autor: The_Prist (Shcherbakov Dmitry)

' Dezvoltarea profesională a aplicațiilor pentru MS Office de orice complexitate

' Efectuarea de training-uri pe MS Excel

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

' [email protected]

„WebMoney—R298726502453; Yandex.Bani — 41001332272872

' Scop:

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

Sub FindErrLink()

    'trebuie să căutăm în link-ul Data -Change link-uri către fișierul sursă

    „și puneți aici cuvintele cheie cu litere mici (parte din numele fișierului)

    „asteriscul înlocuiește orice număr de caractere, astfel încât nu trebuie să vă faceți griji cu privire la numele exact

    Const sToFndLink$ = „*vânzări 2018*”

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

    'definiți toate celulele cu validarea datelor

    La data de eroare CV următoare

    Set rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)

    Dacă rr este nimic, atunci

        MsgBox „Nu există celule cu validarea datelor pe foaia activă”, vbInformation, „www.excel-vba.ru”

        Ieșiți din Sub

    Final, dacă

    La eroare GoTo 0

    „verificați fiecare celulă pentru linkuri

    Pentru fiecare rc In rr

        „Pentru orice eventualitate, omitem erori – se poate întâmpla și acest lucru

        „dar conexiunile noastre trebuie să fie fără ele și cu siguranță vor fi găsite

        s = «»

        La data de eroare CV următoare

        s = rc.Validare.Formula1

        La eroare GoTo 0

        „găsit – colectăm totul într-o gamă separată

        Dacă LCase(e) ca sToFndLink, atunci

            Dacă rres este nimic, atunci

                Set rres = rc

            altfel

                Set rres = Uniune(rc, rres)

            Final, dacă

        Final, dacă

    Pagina Următoare →

    „dacă există o conexiune, selectați toate celulele cu astfel de verificări de date

    Dacă nu este nimic, atunci

        rres.Selectaţi

' rres.Interior.Color = vbRed 'dacă doriți să evidențiați cu culoare

    Final, dacă

End Sub

Este necesar să faceți un modul standard în editorul de macro și apoi să introduceți acest text acolo. După aceea, apelați fereastra macro folosind combinația de taste Alt + F8, apoi selectați macrocomandă și faceți clic pe butonul „Run”. Există câteva lucruri de reținut atunci când utilizați acest cod:

1. Înainte de a căuta un link care nu mai este relevant, trebuie mai întâi să stabiliți cum arată linkul prin care este creat. Pentru a face acest lucru, accesați meniul „Date” și găsiți elementul „Modificare linkuri” acolo. După aceea, trebuie să vă uitați la numele fișierului și să-l specificați între ghilimele. De exemplu, așa: Const sToFndLink$ = „*vânzări 2018*”
2. Este posibil să scrieți numele nu în întregime, ci pur și simplu înlocuiți caracterele inutile cu un asterisc. Și între ghilimele, scrieți numele fișierului cu litere mici. În acest caz, Excel va găsi toate fișierele care conțin un astfel de șir la sfârșit.
3. Acest cod poate verifica numai linkurile din foaia care este activă în prezent.
4. Cu această macrocomandă, puteți selecta doar celulele pe care le-a găsit. Trebuie să ștergeți totul manual. Acesta este un plus, deoarece puteți verifica totul din nou.
5. De asemenea, puteți face celulele evidențiate într-o culoare specială. Pentru a face acest lucru, eliminați apostroful dinaintea acestei linii. rres.Interior.Color = vbRed

De obicei, după ce parcurgeți pașii descriși în instrucțiunile de mai sus, nu ar trebui să mai existe conexiuni inutile. Dar dacă există unele dintre ele în document și nu le puteți elimina dintr-un motiv sau altul (un exemplu tipic este securitatea datelor dintr-o foaie), atunci puteți utiliza o secvență diferită de acțiuni. Această instrucțiune este valabilă numai pentru versiunile 2007 și ulterioare.

1. Creăm o copie de rezervă a documentului.
2. Deschideți acest document utilizând arhivatorul. Puteți folosi oricare care acceptă formatul ZIP, dar WinRar va funcționa și el, precum și cel încorporat în Windows.
3. În arhiva care apare, trebuie să găsiți folderul xl și apoi să deschideți externalLinks.
4. Acest folder conține toate legăturile externe, fiecare dintre ele corespunde unui fișier de forma externalLink1.xml. Toate sunt doar numerotate și, prin urmare, utilizatorul nu are posibilitatea de a înțelege ce fel de conexiune este aceasta. Pentru a înțelege ce fel de conexiune, trebuie să deschideți folderul _rels și să îl priviți acolo.
5. După aceea, eliminăm toate linkurile sau anumite legături, pe baza a ceea ce aflăm în fișierul externalLinkX.xml.rels.
6. După aceea, deschidem fișierul folosind Excel. Vor apărea informații despre o eroare precum „Eroare în parte din conținutul cărții”. Ne dam acordul. După aceea, va apărea un alt dialog. Îl închidem.

După aceea, toate linkurile ar trebui eliminate.