Sadržaj
Komunikacija je vrlo korisna značajka 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 te datoteke pošaljete poštom, linkovi ne rade. Danas ćemo detaljnije govoriti o tome što učiniti kako bismo izbjegli takav problem.
Što su odnosi u Excelu
Relacije u Excelu vrlo se često koriste u sprezi s funkcijama kao što su VPRda biste dobili podatke iz druge radne knjižice. Može biti u obliku posebne veze koja sadrži adresu ne samo ćelije, već i knjige u kojoj se podaci nalaze. Kao rezultat, takva veza izgleda otprilike ovako: =VLOOKUP(A2;'[Prodaja 2018.xlsx]Izvješće'!$A:$F;4;0). Ili, za jednostavniji prikaz, predstavite adresu u sljedećem obliku: ='[Prodaja 2018.xlsx]Izvješće'!$A1. Analizirajmo svaki od elemenata veze ove vrste:
- [Prodaja 2018.xlsx]. Ovaj fragment sadrži poveznicu na datoteku iz koje želite dobiti informacije. Naziva se i izvorištem.
- Fotografije. Koristili smo sljedeći naziv, ali to nije naziv koji bi trebao biti. Ovaj blok sadrži naziv lista u kojem trebate pronaći informacije.
- $A:$F i $A1 – adresu ćelije ili raspona koji sadrži podatke sadržane u ovom dokumentu.
Zapravo, proces stvaranja poveznice na vanjski dokument naziva se povezivanjem. Nakon što smo registrirali adresu ćelije koja se nalazi u drugoj datoteci, mijenja se sadržaj kartice "Podaci". Naime, aktivira se tipka “Promijeni veze” pomoću koje korisnik može uređivati postojeće veze.
Bit problema
U pravilu ne nastaju nikakve dodatne poteškoće pri korištenju poveznica. Čak i ako dođe do situacije u kojoj se ćelije mijenjaju, tada se sve veze automatski ažuriraju. Ali ako već preimenujete samu radnu knjigu ili je premjestite na drugu adresu, Excel postaje nemoćan. Stoga proizvodi sljedeću poruku.
Ovdje korisnik ima dvije moguće opcije kako postupiti u ovoj situaciji. Može kliknuti "Nastavi" i tada se promjene neće ažurirati ili može kliknuti gumb "Promijeni asocijacije", s kojim ih može ažurirati ručno. Nakon što kliknemo na ovaj gumb, pojavit će se dodatni prozor u kojem će biti moguće promijeniti poveznice, naznačujući gdje se ispravna datoteka trenutno nalazi i kako se zove.
Osim toga, možete uređivati veze putem odgovarajućeg gumba koji se nalazi na kartici "Podaci". Korisnik također može saznati da je veza prekinuta greškom #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 sami ne možete ažurirati lokaciju povezane datoteke je brisanje same veze. To 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 izbornik "Podaci".
- Pronalazimo odjeljak "Veze", a tamo - opciju "Promijeni veze".
- Nakon toga kliknite na "Razvezivanje".
Ako ovu knjigu namjeravate poslati drugoj osobi, preporučuje se da to učinite unaprijed. Uostalom, nakon brisanja poveznica, 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 ukloniti sve knjige
Ali ako broj poveznica postane prevelik, njihovo ručno brisanje može potrajati dugo. Da biste riješili ovaj problem u jednom potezu, možete koristiti posebnu makronaredbu. Nalazi se u VBA-Excel dodatku. Morate ga aktivirati i otići na istoimenu karticu. Pojavit će se odjeljak "Veze" u kojem moramo kliknuti gumb "Prekini sve veze".
VBA kod
Ako nije moguće aktivirati ovaj dodatak, možete sami izraditi makronaredbu. Da biste to učinili, otvorite Visual Basic editor pritiskom na tipke Alt + F11 i napišite sljedeće retke u polje za unos koda.
Sub UnlinkWorkBooks()
Dim WbLinks
Dim i As Long
Odaberite Case MsgBox("Sve reference na druge knjige bit će uklonjene iz ove datoteke, a formule koje se odnose na druge knjige bit će zamijenjene vrijednostima." & vbCrLf & "Jeste li sigurni da želite nastaviti?", 36, "Ukloni vezu?" )
Slučaj 7′ br
Izlaz iz pod
Kraj Odaberite
WbLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
If Not IsEmpty(WbLinks) Onda
Za i = 1 To UBound(WbLinks)
ActiveWorkbook.BreakLink Naziv:=WbLinks(i), Vrsta:=xlLinkTypeExcelLinks
Sljedeći
drugo
MsgBox “U ovoj datoteci nema poveznica na druge knjige.”, 64, “Veze na druge knjige”
Kraj Ako je
End Sub
Kako prekidati veze samo u odabranom rasponu
S vremena na vrijeme, broj poveznica je vrlo velik, a korisnik se boji da nakon brisanja jedne od njih neće biti moguće sve vratiti natrag ako je neka bila suvišna. Ali to je problem koji je lako izbjeći. Da biste to učinili, trebate 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 je potrebno izmijeniti.
- Instalirajte VBA-Excel dodatak, a zatim idite na odgovarajuću karticu.
- Zatim pronalazimo izbornik "Veze" i kliknemo na gumb "Prekini veze u odabranim rasponima".
Nakon toga će se izbrisati sve veze u odabranom skupu ćelija.
Što učiniti ako veze nisu prekinute
Sve gore navedeno zvuči dobro, ali u praksi uvijek postoje neke nijanse. Na primjer, može postojati situacija u kojoj veze nisu prekinute. U tom slučaju i dalje se pojavljuje dijaloški okvir koji navodi da nije moguće automatski ažurirati veze. Što učiniti u ovoj situaciji?
- Najprije morate provjeriti sadrže li ikakvi podaci u imenovanim rasponima. 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 neko drugo mjesto kako biste se mogli vratiti na izvornu verziju ako su poduzeti pogrešni koraci.
- Ako ne možete riješiti problem uklanjanjem imena, možete provjeriti uvjetno oblikovanje. Na ćelije u drugoj tablici može se referencirati u pravilima uvjetnog oblikovanja. Da biste to učinili, pronađite odgovarajuću stavku na kartici "Početna", a zatim kliknite gumb "Upravljanje datotekama".
Obično vam Excel ne daje mogućnost davanja adresa drugih radnih knjiga u uvjetnom oblikovanju, ali to možete učiniti ako upućujete na imenovani raspon s referencom na drugu datoteku. Obično, čak i nakon što se veza ukloni, veza ostaje. Nema problema u uklanjanju takve veze, jer ona zapravo ne radi. Stoga se ništa loše neće dogoditi ako ga uklonite.
Također možete koristiti funkciju "Provjera podataka" kako biste saznali postoje li nepotrebne veze. Veze obično ostaju ako se koristi vrsta provjere podataka "List". Ali što učiniti ako ima puno stanica? Je li doista potrebno provjeravati svaki od njih redom? Naravno da ne. Uostalom, to će trajati jako dugo. Stoga morate upotrijebiti poseban kod da biste ga značajno uštedjeli.
Opcija Eksplicitno
'——————————————————————————————
' Autor: The_Prist (Shcherbakov Dmitry)
Profesionalni razvoj aplikacija za MS Office bilo koje složenosti
' Provođenje edukacija na MS Excelu
' https://www.excel-vba.ru
' [e-mail zaštićen]
'WebMoney—R298726502453; Yandex.Money — 41001332272872
' Svrha:
'——————————————————————————————
Sub FindErrLink()
'trebamo pogledati poveznicu Data -Change do izvorne datoteke
'i ovdje stavite ključne riječi malim slovima (dio naziva datoteke)
'zvjezdica samo zamjenjuje bilo koji broj znakova tako da ne morate brinuti o točnom nazivu
Const sToFndLink$ = “*prodaja 2018*”
Dim rr kao raspon, rc kao raspon, rres kao raspon, s$
'definirajte sve ćelije s validacijom podataka
Na Error Resume Next
Postavite rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
Ako je rr Ništa Onda
MsgBox "Nema ćelija s provjerom podataka na aktivnom listu", vbInformation, "www.excel-vba.ru"
Izlaz iz pod
Kraj Ako je
Pri pogrešci Idi na 0
'provjerite poveznice u svakoj ćeliji
Za svaki rc In rr
'za svaki slučaj preskačemo pogreške – i to se može dogoditi
'ali naše veze moraju biti bez njih i sigurno će se pronaći
s = «»
Na Error Resume Next
s = rc.Validacija.Formula1
Pri pogrešci Idi na 0
'pronađeno – sve skupljamo u poseban asortiman
Ako LCase(s) Like sToFndLink Onda
Ako je rres Ništa Onda
Postavite rres = rc
drugo
Postavi rres = Union(rc, rres)
Kraj Ako je
Kraj Ako je
Sljedeći
'ako postoji veza, odaberite sve ćelije s takvim provjerama podataka
Ako nije rres, onda je ništa
rres.Odaberi
' rres.Interior.Color = vbRed 'ako želite istaknuti bojom
Kraj Ako je
End Sub
Potrebno je napraviti standardni modul u uređivaču makronaredbi, a zatim tamo umetnuti ovaj tekst. Nakon toga pozovite makro prozor kombinacijom tipki Alt + F8, a zatim odaberite našu makronaredbu i kliknite na gumb "Pokreni". Prilikom upotrebe ovog koda morate imati na umu nekoliko stvari:
- Prije nego što tražite poveznicu koja više nije relevantna, prvo morate utvrditi kako izgleda poveznica preko koje je kreirana. Da biste to učinili, idite na izbornik "Podaci" i tamo pronađite stavku "Promijeni veze". Nakon toga trebate pogledati naziv datoteke i navesti ga u navodnicima. Na primjer, ovako: Const sToFndLink$ = “*prodaja 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 tom će slučaju Excel pronaći sve datoteke koje na kraju sadrže takav niz.
- Ovaj kod može samo provjeriti veze na listu koji je trenutno aktivan.
- S ovom makronaredbom možete odabrati samo ćelije koje je pronašao. Sve morate izbrisati ručno. To je plus jer sve možete još jednom provjeriti.
- Također možete označiti ćelije posebnom bojom. Da biste to učinili, uklonite apostrof ispred ovog retka. 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 postoje neki od njih u dokumentu i ne možete ih ukloniti iz jednog ili drugog razloga (tipičan primjer je sigurnost podataka u listu), tada možete koristiti drugačiji slijed radnji. Ova uputa vrijedi samo za verzije 2007 i novije.
- Izrađujemo sigurnosnu kopiju dokumenta.
- Otvorite ovaj dokument pomoću programa za arhiviranje. Možete koristiti bilo koji koji podržava ZIP format, ali radit će i WinRar, kao i onaj ugrađen u Windowse.
- U arhivi koja se pojavi morate pronaći mapu xl, a zatim otvoriti externalLinks.
- Ova mapa sadrži sve vanjske veze, od kojih svaka odgovara datoteci oblika externalLink1.xml. Svi su samo numerirani, pa korisnik nema priliku razumjeti o kakvoj se vezi radi. Da biste razumjeli kakva je veza, morate otvoriti mapu _rels i pogledati je tamo.
- Nakon toga uklanjamo sve ili određene veze, na temelju onoga što saznamo u datoteci externalLinkX.xml.rels.
- Nakon toga otvaramo našu datoteku pomoću programa Excel. Pojavit će se informacija o pogrešci poput "Pogreška u dijelu sadržaja u knjizi." Dajemo suglasnost. Nakon toga pojavit će se drugi dijaloški okvir. Zatvaramo ga.
Nakon toga sve poveznice treba ukloniti.