sadržaj
Komunikacija je vrlo korisna funkcija u Excelu. Uostalom, vrlo često korisnici moraju koristiti informacije iz drugih datoteka. Ali u nekim situacijama mogu učiniti više štete nego koristi. Uostalom, na primjer, ako ove datoteke pošaljete poštom, veze ne rade. Danas ćemo detaljnije razgovarati o tome šta učiniti kako biste izbjegli takav problem.
Šta su odnosi u Excelu
Relacije u Excelu se vrlo često koriste u sprezi sa funkcijama kao što su VPRda dobijete informacije iz druge radne sveske. Može biti u obliku posebne veze koja sadrži adresu ne samo ćelije, već i knjige u kojoj se nalaze podaci. Kao rezultat, takva veza izgleda otprilike ovako: =VLOOKUP(A2;'[Sales 2018.xlsx]Izvještaj'!$A:$F;4;0). Ili, za jednostavniji prikaz, predstavite adresu u sljedećem obliku: ='[Sales 2018.xlsx]Izvještaj'!$A1. Analizirajmo svaki od elemenata veze ovog tipa:
- [Prodaja 2018.xlsx]. Ovaj fragment sadrži vezu do datoteke iz koje želite dobiti informacije. Naziva se i izvorom.
- Fotografije. Koristili smo sljedeći naziv, ali ovo nije naziv koji bi trebao biti. Ovaj blok sadrži naziv lista u kojem trebate pronaći informacije.
- $A:$F i $A1 – adresa ćelije ili opsega koji sadrži podatke sadržane u ovom dokumentu.
Zapravo, proces kreiranja veze do eksternog dokumenta naziva se povezivanje. Nakon što smo registrovali adresu ćelije koja se nalazi u drugom fajlu, sadržaj kartice „Podaci“ se menja. Naime, aktivira se dugme „Promeni veze“ uz pomoć kojeg korisnik može uređivati postojeće veze.
Suština problema
U pravilu ne nastaju dodatne poteškoće pri korištenju linkova. Čak i ako se pojavi situacija u kojoj se ćelije mijenjaju, tada se sve veze automatski ažuriraju. Ali ako već preimenujete samu radnu svesku ili je premestite na drugu adresu, Excel postaje nemoćan. Stoga proizvodi sljedeću poruku.
Ovdje korisnik ima dvije moguće opcije kako postupiti u ovoj situaciji. On može kliknuti na “Nastavi” i tada promjene neće biti ažurirane, ili može kliknuti na dugme “Promijeni asocijacije” pomoću kojeg ih može ažurirati ručno. Nakon što kliknemo na ovo dugme, pojavit će se dodatni prozor u kojem će biti moguće promijeniti veze, pokazujući gdje se trenutno nalazi ispravna datoteka i kako se zove.
Osim toga, veze možete uređivati putem odgovarajućeg dugmeta koje se nalazi na kartici „Podaci“. Korisnik također može saznati da je veza prekinuta zbog greške #LINK, koja se pojavljuje kada Excel ne može pristupiti informacijama koje se nalaze na određenoj adresi zbog činjenice da je sama adresa nevažeća.
Kako prekinuti vezu u Excelu
Jedna od najjednostavnijih metoda za rješavanje gore opisane situacije u slučaju da ne možete sami ažurirati lokaciju povezane datoteke je brisanje same veze. Ovo je posebno lako učiniti ako dokument sadrži samo jednu vezu. Da biste to učinili, morate izvršiti sljedeći niz koraka:
- Otvorite meni „Podaci“.
- Pronalazimo odjeljak "Veze", a tamo - opciju "Promijeni veze".
- Nakon toga kliknite na “Prekini vezu”.
Ako namjeravate poslati ovu knjigu drugoj osobi, preporučljivo je da to učinite unaprijed. Uostalom, nakon brisanja veza, sve vrijednosti koje se nalaze u drugom dokumentu automatski će se učitati u datoteku, koristiti u formulama, a umjesto adrese ćelije, informacije u odgovarajućim ćelijama jednostavno će se transformirati u vrijednosti .
Kako prekinuti vezu sa svim knjigama
Ali ako broj veza postane prevelik, njihovo ručno brisanje može potrajati dugo. Da biste riješili ovaj problem u jednom potezu, možete koristiti poseban makro. Nalazi se u dodatku VBA-Excel. Morate ga aktivirati i otići na karticu istog imena. Postojaće odjeljak "Veze" u kojem trebamo kliknuti na dugme "Prekini sve veze".
VBA kod
Ako nije moguće aktivirati ovaj dodatak, možete sami kreirati makro. Da biste to učinili, otvorite Visual Basic editor pritiskom na tipke Alt + F11 i upišite sljedeće redove u polje za unos koda.
Sub UnlinkWorkBooks()
Dim WbLinks
Dim and As Long
Odaberite Case MsgBox(“Sve reference na druge knjige će biti uklonjene iz ove datoteke, a formule koje se odnose na druge knjige će biti zamijenjene vrijednostima.” & vbCrLf & “Jeste li sigurni da želite nastaviti?”, 36, “Prekini vezu?” )
Slučaj 7′ br
Izlaz iz pod
Kraj Odaberite
WbLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
Ako nije IsEmpty(WbLinks) Onda
Za i = 1 do UBound(WbLinks)
ActiveWorkbook.BreakLink Naziv:=WbLinks(i), Tip:=xlLinkTypeExcelLinks
sljedeći
drugo
MsgBox “Nema veza ka drugim knjigama u ovoj datoteci.”, 64, “Veze ka drugim knjigama”
End If
End Sub
Kako prekinuti veze samo u odabranom rasponu
S vremena na vrijeme, broj linkova je jako velik, a korisnik se boji da nakon brisanja jednog od njih neće biti moguće vratiti sve ako je neki bio suvišan. Ali ovo je problem koji je lako izbjeći. Da biste to učinili, morate odabrati raspon u kojem želite izbrisati veze, a zatim ih izbrisati. Da biste to učinili, morate izvršiti sljedeći niz radnji:
- Odaberite skup podataka koji treba izmijeniti.
- Instalirajte VBA-Excel dodatak, a zatim idite na odgovarajuću karticu.
- Zatim pronalazimo meni "Veze" i kliknemo na dugme "Prekini veze u odabranim rasponima".
Nakon toga, sve veze u odabranom skupu ćelija bit će izbrisane.
Šta učiniti ako veze nisu prekinute
Sve navedeno zvuči dobro, ali u praksi uvijek postoje neke nijanse. Na primjer, može postojati situacija u kojoj veze nisu prekinute. U ovom slučaju i dalje se pojavljuje dijaloški okvir u kojem se navodi da nije moguće automatski ažurirati veze. Šta učiniti u ovoj situaciji?
- Prvo morate provjeriti da li se u imenovanim rasponima nalazi bilo koja informacija. Da biste to učinili, pritisnite kombinaciju tipki Ctrl + F3 ili otvorite karticu "Formule" - "Upravitelj imena". Ako je naziv datoteke pun, trebate ga samo urediti ili potpuno ukloniti. Prije brisanja imenovanih raspona, trebate kopirati datoteku na neku drugu lokaciju kako biste se mogli vratiti na originalnu verziju ako su poduzeti pogrešni koraci.
- Ako ne možete riješiti problem uklanjanjem imena, možete provjeriti uvjetno formatiranje. Ćelije u drugoj tabeli mogu se referencirati u pravilima uslovnog oblikovanja. Da biste to učinili, pronađite odgovarajuću stavku na kartici "Početna", a zatim kliknite na dugme "Upravljanje datotekama".
Uobičajeno, Excel vam ne daje mogućnost da date adresu drugih radnih knjiga u uslovnom formatiranju, ali imate ako upućujete na imenovani opseg sa referencom na drugu datoteku. Obično, čak i nakon što se veza ukloni, veza ostaje. Nema problema u uklanjanju takvog linka, jer link zapravo ne radi. Stoga se ništa loše neće dogoditi ako ga uklonite.
Također možete koristiti funkciju “Provjera podataka” da saznate da li postoje nepotrebne veze. Veze obično ostaju ako se koristi tip provjere valjanosti podataka “Lista”. Ali šta učiniti ako ima puno ćelija? Da li je zaista potrebno svaki od njih provjeravati uzastopno? Naravno da ne. Na kraju krajeva, to će potrajati jako dugo. Stoga morate koristiti poseban kod da biste ga značajno sačuvali.
Opcija Eksplicitna
'—————————————————————————————
' Autor: The_Prist(Shcherbakov Dmitry)
' Profesionalni razvoj aplikacija za MS Office bilo koje složenosti
' Sprovođenje obuka za MS Excel
' https://www.excel-vba.ru
' [email zaštićen]
'WebMoney—R298726502453; Yandex.Money — 41001332272872
' Svrha:
'—————————————————————————————
Sub FindErrLink()
'Moramo pogledati vezu Data -Change do izvorne datoteke
'i stavite ključne riječi ovdje malim slovima (dio naziva datoteke)
'zvjezdica samo zamjenjuje bilo koji broj znakova tako da ne morate brinuti o tačnom imenu
Const sToFndLink$ = “*sales 2018*”
Dim rr As Range, rc As Range, rres As Range, s$
'definiraj sve ćelije s provjerom valjanosti podataka
On Error Resume Next
Postavi rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
Ako rr nije ništa onda
MsgBox “Na aktivnom listu nema ćelija sa validacijom podataka”, vbInformation, “www.excel-vba.ru”
Izlaz iz pod
End If
U slučaju greške GoTo 0
'provjerite svaku ćeliju za veze
Za svaki rc In rr
'za svaki slučaj preskočimo greške – i to se može dogoditi
'ali naše veze moraju biti bez njih i sigurno će se naći
s = «»
On Error Resume Next
s = rc.Validacija.Formula1
U slučaju greške GoTo 0
'pronađeno – sve skupljamo u posebnom asortimanu
Ako LCase (ovi) kao sToFndLink Onda
Ako je rres ništa onda
Postavite rres = rc
drugo
Postavi rres = Union(rc, rres)
End If
End If
sljedeći
'ako postoji veza, odaberite sve ćelije s takvim provjerama podataka
Ako nije, onda je rres ništa
rres.Select
' rres.Interior.Color = vbRed 'ako želite da istaknete bojom
End If
End Sub
Potrebno je napraviti standardni modul u uređivaču makroa, a zatim tu umetnuti ovaj tekst. Nakon toga pozovite prozor makronaredbe pomoću kombinacije tipki Alt + F8, a zatim odaberite naš makro i kliknite na dugme "Pokreni". Postoji nekoliko stvari koje treba imati na umu kada koristite ovaj kod:
- Prije nego što tražite vezu koja više nije relevantna, prvo morate utvrditi kako izgleda veza preko koje se kreira. Da biste to učinili, idite na meni "Podaci" i tamo pronađite stavku "Promijeni veze". Nakon toga, trebate pogledati naziv datoteke i navesti ga pod navodnicima. Na primjer, ovako: Const sToFndLink$ = “*sales 2018*”
- Moguće je napisati ime ne u cijelosti, već jednostavno zamijeniti nepotrebne znakove zvjezdicom. I pod navodnicima napišite naziv datoteke malim slovima. U ovom slučaju, Excel će pronaći sve datoteke koje sadrže takav niz na kraju.
- Ovaj kod može provjeriti samo veze u listu koji je trenutno aktivan.
- Pomoću ovog makroa možete odabrati samo ćelije koje je pronašao. Morate sve ručno izbrisati. Ovo je plus, jer možete još jednom sve provjeriti.
- Također možete označiti ćelije posebnom bojom. Da biste to učinili, uklonite apostrof ispred ovog reda. rres.Interior.Color = vbRed
Obično, nakon što dovršite korake opisane u gornjim uputama, više ne bi trebalo biti nepotrebnih veza. Ali ako ih ima u dokumentu i ne možete ih ukloniti iz ovog ili onog razloga (tipičan primjer je sigurnost podataka u listu), onda možete koristiti drugačiji slijed radnji. Ovo uputstvo vrijedi samo za verzije 2007 i novije.
- Pravimo rezervnu kopiju dokumenta.
- Otvorite ovaj dokument pomoću arhivatora. Možete koristiti bilo koji koji podržava ZIP format, ali će raditi i WinRar, kao i onaj koji je ugrađen u Windows.
- U arhivi koja se pojavi potrebno je pronaći mapu xl, a zatim otvoriti externalLinks.
- Ovaj folder sadrži sve eksterne veze, od kojih svaka odgovara datoteci oblika externalLink1.xml. Svi su samo numerisani, pa samim tim korisnik nema priliku da shvati o kakvoj se vezi radi. Da biste razumjeli kakva je veza, morate otvoriti direktorij _rels i pogledati ga tamo.
- Nakon toga uklanjamo sve ili određene veze, na osnovu onoga što saznamo u datoteci externalLinkX.xml.rels.
- Nakon toga otvaramo našu datoteku koristeći Excel. Pojavit će se informacija o grešci poput "Greška u dijelu sadržaja u knjizi." Dajemo saglasnost. Nakon toga, pojavit će se još jedan dijalog. Zatvaramo.
Nakon toga, sve veze treba ukloniti.