Saturs
Komunikācija programmā Excel ir ļoti noderīga funkcija. Galu galā ļoti bieži lietotājiem ir jāizmanto informācija no citiem failiem. Bet dažās situācijās tie var nodarīt vairāk ļauna nekā laba. Galu galā, piemēram, ja sūtāt šos failus pa pastu, saites nedarbojas. Šodien mēs runāsim sīkāk par to, kā rīkoties, lai izvairītos no šādas problēmas.
Kas ir attiecības programmā Excel
Attiecības programmā Excel ļoti bieži tiek izmantotas kopā ar tādām funkcijām kā VPRlai iegūtu informāciju no citas darbgrāmatas. Tas var izpausties kā īpaša saite, kurā ir ne tikai šūnas adrese, bet arī grāmata, kurā atrodas dati. Rezultātā šāda saite izskatās apmēram šādi: =VLOOKUP(A2;'[Pārdošanas 2018.xlsx]Pārskats'!$A:$F;4;0). Vai arī vienkāršākam attēlojumam norādiet adresi šādā formā: ='[Pārdošanas 2018.xlsx]Pārskats'!$A1. Analizēsim katru no šāda veida saites elementiem:
- [Pārdošana 2018.xlsx]. Šajā fragmentā ir saite uz failu, no kura vēlaties iegūt informāciju. To sauc arī par avotu.
- pics. Mēs izmantojām šādu nosaukumu, taču tam nevajadzētu būt. Šajā blokā ir tās lapas nosaukums, kurā jums jāatrod informācija.
- $A:$F un $A1 – šūnas vai diapazona adrese, kas satur šajā dokumentā ietvertos datus.
Faktiski saites izveides procesu uz ārēju dokumentu sauc par saistīšanu. Pēc tam, kad esam reģistrējuši citā failā esošās šūnas adresi, mainās cilnes “Dati” saturs. Proti, tiek aktivizēta poga “Mainīt savienojumus”, ar kuras palīdzību lietotājs var rediģēt esošos savienojumus.
Problēmas būtība
Parasti saišu izmantošana nerodas nekādas papildu grūtības. Pat ja rodas situācija, kad šūnas mainās, visas saites tiek automātiski atjauninātas. Bet, ja jau pārdēvējat pašu darbgrāmatu vai pārvietojat to uz citu adresi, programma Excel kļūst bezspēcīga. Tāpēc tas rada šādu ziņojumu.
Šeit lietotājam ir divas iespējas, kā rīkoties šajā situācijā. Viņš var noklikšķināt uz "Turpināt", un tad izmaiņas netiks atjauninātas, vai arī viņš var noklikšķināt uz pogas "Mainīt asociācijas", ar kuru viņš var atjaunināt tās manuāli. Pēc tam, kad noklikšķināsim uz šīs pogas, parādīsies papildu logs, kurā būs iespējams mainīt saites, norādot, kur šobrīd atrodas pareizais fails un kā to sauc.
Turklāt saites var rediģēt, izmantojot atbilstošo pogu, kas atrodas cilnē “Dati”. Lietotājs var arī uzzināt, ka savienojumu pārtrauc kļūda #LINK, kas parādās, kad Excel nevar piekļūt informācijai, kas atrodas konkrētā adresē, jo pati adrese nav derīga.
Kā atsaistīt programmā Excel
Viena no vienkāršākajām metodēm, kā atrisināt iepriekš aprakstīto situāciju, ja nevarat pats atjaunināt saistītā faila atrašanās vietu, ir pašas saites dzēšana. Tas ir īpaši viegli izdarāms, ja dokumentā ir tikai viena saite. Lai to izdarītu, jums jāveic šāda darbību secība:
- Atveriet izvēlni "Dati".
- Atrodam sadaļu “Savienojumi” un tur – opciju “Mainīt savienojumus”.
- Pēc tam noklikšķiniet uz "Atsaistīt".
Ja plānojat šo grāmatu nosūtīt pa pastu citai personai, ļoti ieteicams to izdarīt iepriekš. Galu galā pēc saišu dzēšanas visas citā dokumentā esošās vērtības tiks automātiski ielādētas failā, izmantotas formulās, un šūnas adreses vietā informācija attiecīgajās šūnās tiks vienkārši pārveidota par vērtībām. .
Kā atsaistīt visas grāmatas
Bet, ja saišu skaits kļūst pārāk liels, to manuāla dzēšana var aizņemt ilgu laiku. Lai atrisinātu šo problēmu vienā piegājienā, varat izmantot īpašu makro. Tas atrodas VBA-Excel papildinājumā. Jums tas jāaktivizē un jādodas uz tāda paša nosaukuma cilni. Būs sadaļa “Saites”, kurā mums jānoklikšķina uz pogas “Pārtraukt visas saites”.
VBA kods
Ja šo papildinājumu nav iespējams aktivizēt, varat izveidot makro pats. Lai to izdarītu, atveriet Visual Basic redaktoru, nospiežot taustiņus Alt + F11, un ierakstiet šādas rindiņas koda ievades laukā.
Pakārtot darba grāmatu atsaistīšanu()
Blāvs WbLinks
Blāvs un tikpat garš
Select Case MsgBox (“No šī faila tiks noņemtas visas atsauces uz citām grāmatām, un formulas, kas attiecas uz citām grāmatām, tiks aizstātas ar vērtībām.” & vbCrLf & “Vai tiešām vēlaties turpināt?”, 36, “Atsaistīt?” )
Lieta 7′ Nr
Iziet no apakš
Beigu atlase
WbLinks = ActiveWorkbook.LinkSources(Tips:=xlLinkTypeExcelSaites)
Ja nav tukšs (WbLinks), tad
Ja i = 1 līdz UBound (Wblinks)
ActiveWorkbook.BreakLink nosaukums:=WbSaites(i), Tips:=xlLinkTypeExcelLinks
nākamais
cits
MsgBox “Šajā failā nav saišu uz citām grāmatām.”, 64, “Saites uz citām grāmatām”
galu Ja
End Sub
Kā saraut saites tikai izvēlētajā diapazonā
Ik pa laikam saišu skaits ir ļoti liels, un lietotājs baidās, ka pēc kādas no tām izdzēšanas nevarēs visu atgriezt atpakaļ, ja dažas bija liekas. Bet šī ir problēma, no kuras ir viegli izvairīties. Lai to izdarītu, jums ir jāizvēlas diapazons, kurā dzēst saites, un pēc tam tās jāizdzēš. Lai to izdarītu, jums jāveic šāda darbību secība:
- Atlasiet datu kopu, kas jāmaina.
- Instalējiet VBA-Excel papildinājumu un pēc tam dodieties uz atbilstošo cilni.
- Pēc tam atrodam izvēlni “Saites” un noklikšķiniet uz pogas “Pārtraukt saites atlasītajos diapazonos”.
Pēc tam visas saites atlasītajā šūnu komplektā tiks dzēstas.
Ko darīt, ja saites nav pārrautas
Viss iepriekš minētais izklausās labi, taču praksē vienmēr ir dažas nianses. Piemēram, var būt situācija, kad saites netiek pārrautas. Šajā gadījumā joprojām tiek parādīts dialoglodziņš, kurā teikts, ka nav iespējams automātiski atjaunināt saites. Ko darīt šajā situācijā?
- Pirmkārt, jums ir jāpārbauda, vai nosauktajos diapazonos ir ietverta informācija. Lai to izdarītu, nospiediet taustiņu kombināciju Ctrl + F3 vai atveriet cilni "Formulas" - "Name Manager". Ja faila nosaukums ir pilns, jums tas vienkārši jārediģē vai jānoņem pavisam. Pirms nosaukto diapazonu dzēšanas fails ir jākopē uz kādu citu vietu, lai varētu atgriezties pie sākotnējās versijas, ja veiktas nepareizas darbības.
- Ja nevarat atrisināt problēmu, noņemot nosaukumus, varat pārbaudīt nosacījumu formatējumu. Citas tabulas šūnām var būt atsauces nosacījumformatēšanas noteikumos. Lai to izdarītu, cilnē “Sākums” atrodiet atbilstošo vienumu un pēc tam noklikšķiniet uz pogas “Failu pārvaldība”.
Parasti programma Excel nedod iespēju norādīt citu darbgrāmatu adreses nosacījumformātā, taču jūs to darāt, ja atsaucaties uz nosauktu diapazonu ar atsauci uz citu failu. Parasti pat pēc saites noņemšanas saite paliek. Nav nekādu problēmu noņemt šādu saiti, jo saite faktiski nedarbojas. Tāpēc nekas slikts nenotiks, ja to noņemsit.
Varat arī izmantot funkciju “Datu pārbaude”, lai noskaidrotu, vai nav nevienas nevajadzīgas saites. Saites parasti paliek, ja tiek izmantots datu validācijas veids “Saraksts”. Bet ko darīt, ja šūnu ir daudz? Vai tiešām ir nepieciešams katru no tiem pārbaudīt secīgi? Protams, nē. Galu galā tas prasīs ļoti ilgu laiku. Tāpēc, lai to ievērojami saglabātu, ir jāizmanto īpašs kods.
Izteikta opcija
'——————————————————————————————
Autors: The_Prist (Dmitrijs Ščerbakovs)
Jebkuras sarežģītības MS Office aplikāciju profesionāla attīstība
' Apmācību vadīšana par MS Excel
https://www.excel-vba.ru
' [aizsargāts ar e-pastu]
“WebMoney — R298726502453; Yandex.Money — 41001332272872
'Mērķis:
'——————————————————————————————
Sub FindErrLink()
'mums ir jāmeklē saite Datu maiņas saites uz avota failu
un ievietojiet atslēgvārdus šeit ar mazajiem burtiem (daļa no faila nosaukuma)
Zvaigznīte vienkārši aizstāj jebkuru rakstzīmju skaitu, tāpēc jums nav jāuztraucas par precīzu nosaukumu
Const sToFndLink$ = “*pārdošana 2018*”
Dim rr As Range, rc As Range, rres As Range, s$
'definējiet visas šūnas ar datu validāciju
Par kļūdu Resume Next
Iestatīt rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
Ja rr nav nekas, tad
MsgBox “Aktīvajā lapā nav šūnu ar datu validāciju”, vbInformation, “www.excel-vba.ru”
Iziet no apakš
galu Ja
Par kļūdu GoTo 0
'pārbaudiet, vai katrā šūnā nav saišu
Katram rc In rr
Katram gadījumam mēs izlaižam kļūdas — arī tas var notikt
'bet mūsu sakariem ir jābūt bez tiem, un tie noteikti tiks atrasti
s = «»
Par kļūdu Resume Next
s = rc.Validation.Formula1
Par kļūdu GoTo 0
'atrasts – mēs visu savācam atsevišķā diapazonā
Ja LCase(s) Like sToFndLink Tad
Ja rres nav nekas Tad
Iestatiet rres = rc
cits
Iestatīt rres = Savienība(rc, rres)
galu Ja
galu Ja
nākamais
Ja ir savienojums, atlasiet visas šūnas ar šādām datu pārbaudēm
Ja nav rres nav nekas Tad
rres.Atlasiet
' rres.Interior.Color = vbRed 'ja vēlaties izcelt ar krāsu
galu Ja
End Sub
Makro redaktorā ir nepieciešams izveidot standarta moduli un pēc tam ievietot šo tekstu. Pēc tam izsauciet makro logu, izmantojot taustiņu kombināciju Alt + F8, un pēc tam atlasiet mūsu makro un noklikšķiniet uz pogas “Palaist”. Lietojot šo kodu, jāpatur prātā dažas lietas:
- Pirms meklējat saiti, kas vairs nav aktuāla, vispirms ir jānosaka, kā izskatās saite, caur kuru tā ir izveidota. Lai to izdarītu, dodieties uz izvēlni “Dati” un atrodiet tur vienumu “Mainīt saites”. Pēc tam jums jāskatās faila nosaukums un jānorāda pēdiņās. Piemēram, šādi: Const sToFndLink$ = “*pārdošana 2018*”
- Vārdu var rakstīt ne pilnībā, bet vienkārši aizstāt nevajadzīgās rakstzīmes ar zvaigznīti. Un pēdiņās ierakstiet faila nosaukumu ar maziem burtiem. Šajā gadījumā Excel atradīs visus failus, kuru beigās ir šāda virkne.
- Šis kods var pārbaudīt tikai pašlaik aktīvajā lapā esošās saites.
- Izmantojot šo makro, varat atlasīt tikai tās šūnas, kuras tas ir atradis. Viss ir jāizdzēš manuāli. Tas ir pluss, jo jūs varat vēlreiz visu pārbaudīt vēlreiz.
- Varat arī padarīt šūnas izceltas īpašā krāsā. Lai to izdarītu, pirms šīs rindas noņemiet apostrofu. rres.Interjers.Krāsa = vbRed
Parasti pēc iepriekš sniegtajos norādījumos aprakstīto darbību veikšanas vairs nevajadzētu būt nevajadzīgiem savienojumiem. Bet, ja dokumentā ir daži no tiem un jūs viena vai otra iemesla dēļ nevarat tos noņemt (tipisks piemērs ir datu drošība lapā), varat izmantot citu darbību secību. Šī instrukcija ir derīga tikai 2007. gada un jaunākām versijām.
- Mēs izveidojam dokumenta rezerves kopiju.
- Atveriet šo dokumentu, izmantojot arhivētāju. Varat izmantot jebkuru, kas atbalsta ZIP formātu, taču darbosies arī WinRar, kā arī sistēmā Windows iebūvētais.
- Parādītajā arhīvā ir jāatrod mape xl un pēc tam jāatver ārējās saites.
- Šajā mapē ir visas ārējās saites, no kurām katra atbilst failam, kura forma ir externalLink1.xml. Visi no tiem ir tikai numurēti, un tāpēc lietotājam nav iespējas saprast, kāda veida savienojums tas ir. Lai saprastu, kāda veida savienojums, jums ir jāatver mape _rels un jāskatās tur.
- Pēc tam mēs noņemam visas vai konkrētas saites, pamatojoties uz to, ko uzzinām failā externalLinkX.xml.rels.
- Pēc tam mēs atveram failu, izmantojot Excel. Tiks parādīta informācija par kļūdu, piemēram, “Kļūda grāmatas satura daļā”. Mēs dodam piekrišanu. Pēc tam parādīsies cits dialoglodziņš. Mēs to aizveram.
Pēc tam visas saites ir jānoņem.