Съдържание
Комуникацията е много полезна функция в Excel. В крайна сметка много често потребителите трябва да използват информация от други файлове. Но в някои ситуации те могат да причинят повече вреда, отколкото полза. В крайна сметка, например, ако изпратите тези файлове по пощата, връзките не работят. Днес ще говорим по-подробно какво да правим, за да избегнем такъв проблем.
Какво представляват релациите в Excel
Релациите в Excel много често се използват заедно с функции като ВПРза да получите информация от друга работна книга. Тя може да бъде под формата на специална връзка, която съдържа адреса не само на клетката, но и на книгата, в която се намират данните. В резултат на това такава връзка изглежда по следния начин: =VLOOKUP(A2;'[Продажби 2018.xlsx]Отчет'!$A:$F;4;0). Или, за по-просто представяне, представете адреса в следната форма: ='[Продажби 2018.xlsx]Отчет'!$A1. Нека анализираме всеки от елементите на връзката от този тип:
- [Продажби 2018.xlsx]. Този фрагмент съдържа връзка към файла, от който искате да получите информация. Нарича се още източникът.
- Снимки. Използвахме следното име, но това не е името, което трябва да бъде. Този блок съдържа името на листа, в който трябва да намерите информация.
- $A:$F и $A1 – адресът на клетка или диапазон, съдържащи данни, които се съдържат в този документ.
Всъщност процесът на създаване на връзка към външен документ се нарича свързване. След като регистрираме адреса на клетката, съдържаща се в друг файл, съдържанието на раздела „Данни“ се променя. А именно бутонът „Промяна на връзките“ става активен, с помощта на който потребителят може да редактира съществуващите връзки.
Същността на проблема
По правило не възникват допълнителни затруднения при използването на връзки. Дори ако възникне ситуация, при която клетките се променят, тогава всички връзки се актуализират автоматично. Но ако вече преименувате самата работна книга или я преместите на друг адрес, Excel става безсилен. Следователно, той произвежда следното съобщение.
Тук потребителят има две възможни опции за това как да действа в тази ситуация. Той може да щракне върху „Продължи“ и тогава промените няма да бъдат актуализирани, или може да щракне върху бутона „Промяна на асоциациите“, с който може да ги актуализира ръчно. След като щракнете върху този бутон, ще се появи допълнителен прозорец, в който ще можете да промените връзките, като посочите къде се намира правилният файл в момента и как се нарича.
Освен това можете да редактирате връзки чрез съответния бутон, намиращ се в раздела „Данни“. Потребителят може също да разбере, че връзката е прекъсната от грешката #LINK, която се появява, когато Excel не може да получи достъп до информация, намираща се на конкретен адрес, поради факта, че самият адрес е невалиден.
Как да прекъсна връзката в excel
Един от най-простите методи за разрешаване на ситуацията, описана по-горе, в случай че не можете сами да актуализирате местоположението на свързания файл, е да изтриете самата връзка. Това е особено лесно да се направи, ако документът съдържа само една връзка. За да направите това, трябва да изпълните следната последователност от стъпки:
- Отворете менюто "Данни".
- Намираме секцията „Връзки“, а там – опцията „Промяна на връзките“.
- След това кликнете върху „Прекратяване на връзката“.
Ако възнамерявате да изпратите тази книга по пощата на друго лице, силно се препоръчва да го направите предварително. В края на краищата, след изтриване на връзките, всички стойности, които се съдържат в друг документ, ще бъдат автоматично заредени във файла, използвани във формули, и вместо адреса на клетката, информацията в съответните клетки просто ще се трансформира в стойности .
Как да прекъсна връзката на всички книги
Но ако броят на връзките стане твърде голям, ръчното им изтриване може да отнеме много време. За да разрешите този проблем с едно движение, можете да използвате специален макрос. Той е в добавката VBA-Excel. Трябва да го активирате и да отидете в раздела със същото име. Ще има раздел „Връзки“, в който трябва да кликнете върху бутона „Прекъсване на всички връзки“.
VBA код
Ако не е възможно да активирате тази добавка, можете сами да създадете макрос. За да направите това, отворете редактора на Visual Basic, като натиснете клавишите Alt + F11 и напишете следните редове в полето за въвеждане на код.
Sub UnlinkWorkBooks()
Dim WbLinks
Dim и As Long
Изберете Case MsgBox(„Всички препратки към други книги ще бъдат премахнати от този файл и формулите, препращащи към други книги, ще бъдат заменени със стойности.“ & vbCrLf & „Сигурни ли сте, че искате да продължите?“, 36, „Прекратяване на връзката?“ )
Случай 7′ No
Излезте от под
Край Изберете
WbLinks = ActiveWorkbook.LinkSources(Тип:=xlLinkTypeExcelLinks)
If Not IsEmpty(WbLinks) Тогава
За i = 1 към UBound (WbLinks)
Име на ActiveWorkbook.BreakLink:=WbLinks(i), Тип:=xlLinkTypeExcelLinks
Напред
още
MsgBox „В този файл няма връзки към други книги.“, 64, „Връзки към други книги“
крайна сметка, ако
End Sub
Как да късаме връзки само в избрания диапазон
От време на време броят на връзките е много голям и потребителят се страхува, че след изтриване на една от тях няма да е възможно да върне всичко обратно, ако някои са били излишни. Но това е проблем, който лесно се избягва. За да направите това, трябва да изберете диапазона, в който да изтриете връзките, и след това да ги изтриете. За да направите това, трябва да изпълните следната последователност от действия:
- Изберете набора от данни, който трябва да бъде модифициран.
- Инсталирайте добавката VBA-Excel и след това отидете на съответния раздел.
- След това намираме менюто „Връзки“ и кликваме върху бутона „Прекъсване на връзки в избраните диапазони“.
След това всички връзки в избрания набор от клетки ще бъдат изтрити.
Какво да направите, ако връзките не са прекъснати
Всичко по-горе звучи добре, но на практика винаги има някои нюанси. Например, може да има ситуация, в която връзките не са прекъснати. В този случай все още се появява диалогов прозорец, който гласи, че не е възможно автоматично да се актуализират връзките. Какво да правим в тази ситуация?
- Първо, трябва да проверите дали в именуваните диапазони се съдържа някаква информация. За да направите това, натиснете клавишната комбинация Ctrl + F3 или отворете раздела „Формули“ – „Мениджър на имена“. Ако името на файла е пълно, тогава просто трябва да го редактирате или да го премахнете напълно. Преди да изтриете именувани диапазони, трябва да копирате файла на друго място, за да можете да се върнете към оригиналната версия, ако са предприети грешни стъпки.
- Ако не можете да разрешите проблема чрез премахване на имена, можете да проверите условното форматиране. Клетките в друга таблица могат да бъдат посочени в правилата за условно форматиране. За да направите това, намерете съответния елемент в раздела „Начало“ и след това щракнете върху бутона „Управление на файлове“.
Обикновено Excel не ви дава възможността да дадете адреса на други работни книги в условно форматиране, но го правите, ако препращате към наименуван диапазон с препратка към друг файл. Обикновено, дори след като връзката бъде премахната, връзката остава. Няма проблем да премахнете такава връзка, защото връзката всъщност не работи. Следователно нищо лошо няма да се случи, ако го премахнете.
Можете също така да използвате функцията „Проверка на данни“, за да разберете дали има ненужни връзки. Връзките обикновено остават, ако се използва типът „Списък“ за валидиране на данни. Но какво да направите, ако има много клетки? Наистина ли е необходимо да проверявате всеки един от тях последователно? Разбира се, че не. В крайна сметка ще отнеме много време. Следователно трябва да използвате специален код, за да го спестите значително.
Опция Изрично
'——————————————————————————————
' Автор : The_Prist (Щербаков Дмитрий)
Професионална разработка на приложения за MS Office с всякаква сложност
' Провеждане на обучения по MS Excel
https://www.excel-vba.ru
' [имейл защитен]
„WebMoney—R298726502453; Yandex.Money — 41001332272872
' Предназначение:
'——————————————————————————————
Под FindErrLink()
„трябва да потърсим връзката Data -Change към изходния файл
'и поставете ключовите думи тук с малки букви (част от името на файла)
'звездичката просто замества произволен брой знаци, така че не е нужно да се притеснявате за точното име
Const sToFndLink$ = „*продажби 2018*“
Dim rr като диапазон, rc като диапазон, rres като диапазон, s$
'дефинирайте всички клетки с валидиране на данни
При грешка се възобнови при следващото
Задайте rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
Ако rr не е нищо, тогава
MsgBox „В активния лист няма клетки с валидиране на данни“, vbInformation, „www.excel-vba.ru“
Излезте от под
крайна сметка, ако
При грешка GoTo 0
'проверете всяка клетка за връзки
За всеки rc In rr
„за всеки случай пропускаме грешки – това също може да се случи
„но нашите връзки трябва да са без тях и те определено ще бъдат намерени
s = «»
При грешка се възобнови при следващото
s = rc.Validation.Formula1
При грешка GoTo 0
„намерено – събираме всичко в отделна гама
Ако LCase(s) като sToFndLink Тогава
Ако rres не е нищо, тогава
Задайте rres = rc
още
Задайте rres = Union(rc, rres)
крайна сметка, ако
крайна сметка, ако
Напред
'ако има връзка, изберете всички клетки с такива проверки на данни
Ако не rres е нищо тогава
rres.Избери
' rres.Interior.Color = vbRed 'ако искате да подчертаете с цвят
крайна сметка, ако
End Sub
Необходимо е да направите стандартен модул в редактора на макроси и след това да вмъкнете този текст там. След това извикайте прозореца на макроса, като използвате клавишната комбинация Alt + F8, след което изберете нашия макрос и щракнете върху бутона „Изпълни“. Има няколко неща, които трябва да имате предвид, когато използвате този код:
- Преди да търсите връзка, която вече не е подходяща, първо трябва да определите как изглежда връзката, чрез която е създадена. За да направите това, отидете в менюто „Данни“ и намерете там елемента „Промяна на връзки“. След това трябва да погледнете името на файла и да го посочите в кавички. Например така: Const sToFndLink$ = „*продажби 2018*“
- Възможно е да напишете името не изцяло, а просто да замените ненужните знаци със звездичка. И в кавички напишете името на файла с малки букви. В този случай Excel ще намери всички файлове, които съдържат такъв низ в края.
- Този код може да проверява само за връзки в листа, който е активен в момента.
- С този макрос можете да изберете само клетките, които е намерил. Трябва да изтриете всичко ръчно. Това е плюс, защото можете да проверите всичко отново.
- Можете също да направите клетките маркирани в специален цвят. За да направите това, премахнете апострофа преди този ред. rres.Interior.Color = vbRed
Обикновено, след като завършите стъпките, описани в инструкциите по-горе, не трябва да има повече ненужни връзки. Но ако има някои от тях в документа и не можете да ги премахнете по една или друга причина (типичен пример е сигурността на данните в лист), тогава можете да използвате различна последователност от действия. Тази инструкция е валидна само за версии 2007 и по-нови.
- Създаваме резервно копие на документа.
- Отворете този документ с помощта на архиватора. Можете да използвате всеки, който поддържа ZIP формат, но WinRar също ще работи, както и този, вграден в Windows.
- В архива, който се показва, трябва да намерите папката xl и след това да отворите externalLinks.
- Тази папка съдържа всички външни връзки, всяка от които съответства на файл с формата externalLink1.xml. Всички те са само номерирани и следователно потребителят няма възможност да разбере какъв вид връзка е това. За да разберете какъв вид връзка, трябва да отворите папката _rels и да я погледнете там.
- След това премахваме всички или конкретни връзки въз основа на това, което научаваме във файла externalLinkX.xml.rels.
- След това отваряме нашия файл с помощта на Excel. Ще има информация за грешка като „Грешка в част от съдържанието на книгата“. Даваме съгласие. След това ще се появи друг диалогов прозорец. Затваряме го.
След това всички връзки трябва да бъдат премахнати.