Мазмұны
Байланыс - Excel бағдарламасындағы өте пайдалы мүмкіндік. Өйткені, пайдаланушыларға басқа файлдардағы ақпаратты жиі пайдалануға тура келеді. Бірақ кейбір жағдайларда олар пайдадан гөрі көп зиян келтіруі мүмкін. Өйткені, мысалы, бұл файлдарды пошта арқылы жіберсеңіз, сілтемелер жұмыс істемейді. Бүгін біз мұндай проблеманы болдырмау үшін не істеу керектігі туралы толығырақ сөйлесетін боламыз.
Excel-де қандай қатынастар бар
Excel бағдарламасындағы қарым-қатынастар сияқты функциялармен бірге жиі пайдаланылады VPRбасқа жұмыс кітабынан ақпарат алу үшін. Ол тек ұяшықтың ғана емес, сонымен қатар деректер орналасқан кітаптың мекенжайын қамтитын арнайы сілтеме түрінде болуы мүмкін. Нәтижесінде мұндай сілтеме келесідей көрінеді: =VLOOKUP(A2;'[Sales 2018.xlsx]Есеп'!$A:$F;4;0). Немесе қарапайым көрсету үшін мекенжайды келесі пішінде көрсетіңіз: ='[Sales 2018.xlsx]Есеп'!$A1. Осы түрдегі сілтеме элементтерінің әрқайсысын талдап көрейік:
- [Sales 2018.xlsx]. Бұл фрагментте ақпарат алғыңыз келетін файлға сілтеме бар. Оны дереккөз деп те атайды.
- фотосуреттер. Біз келесі атауды қолдандық, бірақ бұл атау емес. Бұл блокта ақпаратты табу керек парақтың атауы бар.
- $A:$F және $A1 – осы құжаттағы деректерді қамтитын ұяшықтың немесе ауқымның мекенжайы.
Іс жүзінде сыртқы құжатқа сілтеме жасау процесі сілтеме деп аталады. Басқа файлдағы ұяшықтың мекенжайын тіркегеннен кейін «Деректер» қойындысының мазмұны өзгереді. Атап айтқанда, «Қосылымдарды өзгерту» түймесі белсенді болады, оның көмегімен пайдаланушы бар қосылымдарды өңдей алады.
Мәселенің мәні
Әдетте, сілтемелерді пайдалану үшін қосымша қиындықтар туындамайды. Ұяшықтар өзгеретін жағдай туындаса да, барлық сілтемелер автоматты түрде жаңартылады. Бірақ жұмыс кітабының атын әлдеқашан өзгертсеңіз немесе оны басқа мекен-жайға жылжытсаңыз, Excel жұмыс істемейді. Сондықтан ол келесі хабарламаны шығарады.
Мұнда пайдаланушыда бұл жағдайда қалай әрекет етудің екі ықтимал нұсқасы бар. Ол «Жалғастыру» түймесін баса алады, содан кейін өзгертулер жаңартылмайды немесе ол оларды қолмен жаңарта алатын «Қауымдастықтарды өзгерту» түймесін баса алады. Осы түймені басқаннан кейін сілтемелерді өзгертуге болатын қосымша терезе пайда болады, онда дұрыс файл қазіргі уақытта қайда орналасқанын және оның қалай аталатынын көрсетеді.
Сонымен қатар, сілтемелерді «Деректер» қойындысында орналасқан сәйкес түйме арқылы өңдеуге болады. Сондай-ақ пайдаланушы мекенжайдың өзі жарамсыз болғандықтан Excel нақты мекенжайда орналасқан ақпаратқа қол жеткізе алмаған кезде пайда болатын #LINK қатесі арқылы қосылымның бұзылғанын біле алады.
Excel бағдарламасында байланысты қалай жоюға болады
Байланыстырылған файлдың орнын өзіңіз жаңарта алмасаңыз, жоғарыда сипатталған жағдайды шешудің қарапайым әдістерінің бірі сілтеменің өзін жою болып табылады. Құжатта тек бір сілтеме болса, мұны істеу әсіресе оңай. Ол үшін келесі қадамдар тізбегін орындау керек:
- «Деректер» мәзірін ашыңыз.
- Біз «Қосылымдар» бөлімін табамыз, ал сол жерде «Қосылымдарды өзгерту» опциясы бар.
- Осыдан кейін «Жою» түймесін басыңыз.
Егер сіз бұл кітапты басқа адамға пошта арқылы жібергіңіз келсе, оны алдын ала жасау ұсынылады. Өйткені, сілтемелерді жойғаннан кейін, басқа құжаттағы барлық мәндер автоматты түрде файлға жүктеледі, формулаларда пайдаланылады және ұяшық мекенжайының орнына сәйкес ұяшықтардағы ақпарат жай ғана мәндерге түрлендіріледі. .
Барлық кітаптарды қалай ажыратуға болады
Бірақ егер сілтемелер саны тым көп болса, оларды қолмен жою ұзақ уақыт алуы мүмкін. Бұл мәселені бір әрекетте шешу үшін арнайы макросты пайдалануға болады. Ол VBA-Excel қосымшасында орналасқан. Сіз оны іске қосып, аттас қойындыға өтуіңіз керек. «Сілтемелер» бөлімі болады, онда біз «Барлық сілтемелерді бұзу» түймесін басуымыз керек.
VBA коды
Бұл қондырманы белсендіру мүмкін болмаса, макросты өзіңіз жасай аласыз. Ол үшін Alt + F11 пернелерін басу арқылы Visual Basic редакторын ашыңыз және код енгізу өрісіне келесі жолдарды жазыңыз.
Sublink UnlinkWorkBooks()
WbLinks күңгірттенеді
Dim and As Long
Case MsgBox таңдаңыз («Басқа кітаптарға барлық сілтемелер осы файлдан жойылады және басқа кітаптарға сілтеме жасайтын формулалар мәндермен ауыстырылады.» & vbCrLf & «Жалғастыруды шынымен қалайсыз ба?», 36, «Байланысты жою?» )
Іс 7′ №
Sub бөлімінен шығу
Таңдауды аяқтау
WbLinks = ActiveWorkbook.LinkSources(Түрі:=xlLinkTypeExcelLinks)
Бос болмаса(WbLinks) Содан кейін
i = 1 үшін UBound(WbLinks)
ActiveWorkbook.BreakLink Name:=WbLinks(i), Түр:=xlLinkTypeExcelLinks
Келесі
тағы
MsgBox “Бұл файлда басқа кітаптарға сілтемелер жоқ.”, 64, “Басқа кітаптарға сілтемелер”
End If
End Sub
Таңдалған диапазондағы байланыстарды қалай бұзуға болады
Кейде сілтемелердің саны өте көп және пайдаланушы олардың біреуін жойғаннан кейін кейбіреулері артық болса, бәрін қайтару мүмкін емес деп қорқады. Бірақ бұл қиындықтан құтылу оңай. Ол үшін сілтемелер жойылатын ауқымды таңдап, содан кейін оларды жою керек. Ол үшін келесі әрекеттер тізбегін орындау керек:
- Өзгерту қажет деректер жиынын таңдаңыз.
- VBA-Excel қондырмасын орнатып, сәйкес қойындыға өтіңіз.
- Содан кейін біз «Сілтемелер» мәзірін тауып, «Таңдалған ауқымдардағы сілтемелерді бұзу» түймесін басыңыз.
Осыдан кейін таңдалған ұяшықтар жинағындағы барлық сілтемелер жойылады.
Егер байланыстар үзілмесе не істеу керек
Жоғарыда айтылғандардың бәрі жақсы естіледі, бірақ іс жүзінде әрқашан кейбір нюанстар бар. Мысалы, байланыстар үзілмейтін жағдай болуы мүмкін. Бұл жағдайда сілтемелерді автоматты түрде жаңарту мүмкін емес екенін көрсететін диалогтық терезе әлі де пайда болады. Бұл жағдайда не істеу керек?
- Біріншіден, аталған ауқымдарда қандай да бір ақпарат бар-жоғын тексеру керек. Ол үшін Ctrl + F3 пернелер тіркесімін басыңыз немесе «Формулалар» қойындысын ашыңыз - «Аты менеджері». Егер файл атауы толық болса, оны өңдеу немесе мүлдем жою қажет. Атаулы ауқымдарды жоймас бұрын, қате қадамдар жасалса, бастапқы нұсқаға оралу үшін файлды басқа орынға көшіру керек.
- Егер атауларды жою арқылы мәселені шеше алмасаңыз, шартты пішімдеуді тексеруге болады. Басқа кестедегі ұяшықтарға шартты пішімдеу ережелерінде сілтеме жасауға болады. Мұны істеу үшін «Басты» қойындысынан сәйкес элементті тауып, «Файлдарды басқару» түймесін басыңыз.
Әдетте, Excel бағдарламасы шартты пішімдеуде басқа жұмыс кітаптарының мекенжайын беру мүмкіндігін бермейді, бірақ сіз басқа файлға сілтеме арқылы аталған ауқымға сілтеме жасасаңыз. Әдетте, сілтеме жойылғаннан кейін де сілтеме қалады. Мұндай сілтемені жоюда ешқандай проблема жоқ, себебі сілтеме іс жүзінде жұмыс істемейді. Сондықтан, егер сіз оны алып тастасаңыз, жаман ештеңе болмайды.
Сондай-ақ, қажетсіз сілтемелердің бар-жоғын білу үшін «Деректерді тексеру» функциясын пайдалануға болады. Деректерді тексерудің «Тізім» түрі пайдаланылса, сілтемелер әдетте қалады. Бірақ жасушалар көп болса не істеу керек? Олардың әрқайсысын ретімен тексеру қажет пе? Әрине жоқ. Өйткені, бұл өте ұзақ уақытты алады. Сондықтан оны айтарлықтай сақтау үшін арнайы кодты пайдалану керек.
Опция Ашық
'—————————————————————————————
Авторы: Присть (Дмитрий Щербаков)
' Кез келген күрделіліктегі MS Office қосымшаларын кәсіби дамыту
' MS Excel бойынша тренингтер өткізу
' https://www.excel-vba.ru
' [электрондық пошта қорғалған]
'WebMoney — R298726502453; Yandex.Money — 41001332272872
' Мақсаты:
'—————————————————————————————
Sub FindErrLink()
'бізге бастапқы файлға Деректер -Сілтемелерді өзгерту сілтемесін қарау керек
'және мұнда кілт сөздерді кіші әріппен (файл атауының бөлігі) қойыңыз
'жұлдызша кез келген таңбалардың санын ауыстырады, сондықтан нақты атау туралы алаңдамайсыз
Const sToFndLink$ = “*сату 2018*”
Dim rr As Range, rc As As Range, rres As As Range, s$
'деректерді тексеру арқылы барлық ұяшықтарды анықтау
Келесіде қатені жалғастыруда
rr орнату = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
Егер rr ештеңе болмаса
MsgBox «Белсенді парақта деректерді тексеруі бар ұяшықтар жоқ», vbInformation, «www.excel-vba.ru»
Sub бөлімінен шығу
End If
Өту қатесі туралы 0
'әр ұяшықта сілтемелерді тексеріңіз
Әрбір rc In rr үшін
«Қандай жағдайда қателерді өткізіп жібереміз – бұл да болуы мүмкін
«бірақ біздің байланыстарымыз оларсыз болуы керек және олар міндетті түрде табылады
s = «»
Келесіде қатені жалғастыруда
s = rc.Validation.Formula1
Өту қатесі туралы 0
'табылды - біз барлығын бөлек диапазонда жинаймыз
Егер LCase(лар) sToFndLink ұнаса, онда
Егер rres Ештеңе болмаса
rres = rc орнатыңыз
тағы
Орнату rres = Бірлестік (rc, rres)
End If
End If
Келесі
'егер қосылым болса, деректер тексерулері бар барлық ұяшықтарды таңдаңыз
If Not rres Ештеңе емес
rres.Таңдау
' rres.Interior.Color = vbRed 'түспен бөлектегіңіз келсе
End If
End Sub
Макроредакторда стандартты модуль жасау керек, содан кейін осы мәтінді сол жерге кірістіру керек. Осыдан кейін Alt + F8 пернелер тіркесімін пайдаланып макрос терезесіне қоңырау шалыңыз, содан кейін біздің макросты таңдап, «Іске қосу» түймесін басыңыз. Бұл кодты пайдалану кезінде есте сақтау керек бірнеше нәрсе бар:
- Маңызды емес сілтемені іздемес бұрын, алдымен ол жасалған сілтеменің қандай екенін анықтау керек. Мұны істеу үшін «Деректер» мәзіріне өтіп, «Сілтемелерді өзгерту» тармағын табыңыз. Осыдан кейін сіз файл атауын қарап, оны тырнақшаға көрсетуіңіз керек. Мысалы, келесідей: Const sToFndLink$ = “*сату 2018*”
- Атын толық емес жазуға болады, бірақ қажет емес таңбаларды жұлдызшамен алмастыруға болады. Ал тырнақшаға файл атауын кіші әріптермен жазыңыз. Бұл жағдайда Excel бағдарламасы соңында осындай жолды қамтитын барлық файлдарды табады.
- Бұл код тек қазіргі уақытта белсенді парақтағы сілтемелерді тексере алады.
- Бұл макростың көмегімен сіз ол тапқан ұяшықтарды ғана таңдай аласыз. Барлығын қолмен жою керек. Бұл плюс, өйткені сіз бәрін қайтадан тексере аласыз.
- Сондай-ақ ұяшықтарды ерекше түспен бөлектеуге болады. Ол үшін осы жолдың алдындағы апострофты алып тастаңыз. rres.Interior.Color = vbRed
Әдетте, жоғарыдағы нұсқауларда сипатталған қадамдарды орындағаннан кейін қажетсіз қосылымдар болмауы керек. Бірақ егер олардың кейбіреулері құжатта болса және оларды бір немесе басқа себептермен жою мүмкін болмаса (әдеттегі мысал - парақтағы деректердің қауіпсіздігі), онда сіз басқа әрекеттер тізбегін пайдалана аласыз. Бұл нұсқаулық тек 2007 және одан жоғары нұсқалар үшін жарамды.
- Біз құжаттың сақтық көшірмесін жасаймыз.
- Бұл құжатты архиватор арқылы ашыңыз. Сіз ZIP пішімін қолдайтын кез келген файлды пайдалана аласыз, бірақ WinRar да Windows жүйесіне орнатылған файл сияқты жұмыс істейді.
- Пайда болған мұрағатта xl қалтасын тауып, сыртқы сілтемелерді ашу керек.
- Бұл қалтада әрқайсысы externalLink1.xml пішінінің файлына сәйкес келетін барлық сыртқы сілтемелер бар. Олардың барлығы тек нөмірленген, сондықтан пайдаланушының бұл байланыс түрін түсінуге мүмкіндігі жоқ. Байланыстың қандай түрін түсіну үшін _rels қалтасын ашып, оны сол жерден қарау керек.
- Осыдан кейін біз externalLinkX.xml.rels файлында үйренгенімізге негізделген барлық немесе нақты сілтемелерді жоямыз.
- Осыдан кейін біз Excel бағдарламасын пайдаланып файлымызды ашамыз. «Кітаптағы мазмұнның бір бөлігіндегі қате» сияқты қате туралы ақпарат болады. Келісім береміз. Осыдан кейін басқа диалогтық терезе пайда болады. Біз оны жабамыз.
Осыдан кейін барлық сілтемелер жойылуы керек.