мазмуну
Байланыш Excelдеги абдан пайдалуу функция. Анткени, көп учурда колдонуучулар башка файлдардан маалыматты колдонууга туура келет. Бирок кээ бир учурларда, алар пайдага караганда көбүрөөк зыян алып келиши мүмкүн. Анткени, мисалы, бул файлдарды почта аркылуу жөнөтсөңүз, шилтемелер иштебейт. Бүгүн биз мындай көйгөйдү болтурбоо үчүн эмне кылуу керектиги жөнүндө кененирээк сүйлөшөбүз.
Excelде кандай мамилелер бар
Excelдеги мамилелер көбүнчө функциялар менен бирге колдонулат, мисалы VPRбашка иш китебинен маалымат алуу үчүн. Ал уячанын гана эмес, маалыматтар жайгашкан китептин дарегин камтыган атайын шилтеме формасын ала алат. Натыйжада, мындай шилтеме төмөнкүдөй көрүнөт: =VLOOKUP(A2;'[Sales 2018.xlsx]Report'!$A:$F;4;0). Же жөнөкөй көрсөтүү үчүн даректи төмөнкү формада көрсөтүңүз: ='[Sales 2018.xlsx]Отчёт'!$A1. Бул түрдөгү шилтеме элементтеринин ар бирин талдап көрөлү:
- [Sales 2018.xlsx]. Бул фрагментте сиз маалымат алгыңыз келген файлга шилтеме бар. Ошондой эле булак деп аталат.
- Photos. Биз төмөнкү ысымды колдондук, бирок бул болушу керек болгон ат эмес. Бул блок сиз маалыматты табуу керек болгон баракчанын атын камтыйт.
- $A:$F жана $A1 – бул документте камтылган маалыматтарды камтыган уячанын же диапазонун дареги.
Чынында, тышкы документке шилтеме түзүү процесси шилтеме деп аталат. Биз башка файлда камтылган уячанын дарегин каттагандан кийин, "Маалымат" кошумча барагынын мазмуну өзгөрөт. Тактап айтканда, "Байланыштарды өзгөртүү" баскычы активдүү болот, анын жардамы менен колдонуучу болгон байланыштарды түзөтө алат.
Проблеманын маңызы
Эреже катары, шилтемелерди колдонуу үчүн эч кандай кошумча кыйынчылыктар пайда болот. Клеткалар өзгөргөн жагдай пайда болгон күндө да, бардык шилтемелер автоматтык түрдө жаңыланат. Бирок, эгер сиз иш китебинин атын өзгөртсөңүз же аны башка дарекке жылдырсаңыз, Excel алсыз болуп калат. Ошондуктан, ал төмөнкү кабарды чыгарат.
Бул жерде, колдонуучу бул кырдаалда кандай иш-аракет кылуу үчүн эки мүмкүн болгон варианты бар. Ал "Улантуу" баскычын чыкылдатып, андан кийин өзгөртүүлөр жаңыртылбайт же "Асоциацияларды өзгөртүү" баскычын чыкылдатып, аларды кол менен жаңырта алат. Бул баскычты баскандан кийин, кошумча терезе пайда болот, анда шилтемелерди өзгөртүүгө болот, анда туура файл учурда кайда жайгашканын жана ал эмне деп аталат.
Мындан тышкары, сиз шилтемелерди "Маалымат" өтмөгүндө жайгашкан тиешелүү баскыч аркылуу өзгөртө аласыз. Колдонуучу ошондой эле даректин өзү жараксыз болгондуктан Excel белгилүү бир даректе жайгашкан маалыматка кире албаганда пайда болгон #LINK катасы менен байланыш үзүлгөнүн биле алат.
Excelде кантип ажыратуу керек
Шилтемеленген файлдын жайгашкан жерин өзүңүз жаңырта албасаңыз, жогоруда сүрөттөлгөн кырдаалды чечүүнүн эң жөнөкөй ыкмаларынын бири - шилтеменин өзүн жок кылуу. Документте бир гана шилтеме болсо, муну жасоо өзгөчө оңой. Бул үчүн, сиз кадамдардын төмөнкү ырааттуулугун аткаруу керек:
- "Маалымат" менюну ачыңыз.
- Биз бөлүмдү табабыз "Байланыштар", ал жерде - параметр "Байланыштарды өзгөртүү".
- Андан кийин, "Ажыратууну" чыкылдатыңыз.
Эгер сиз бул китепти башка адамга почта аркылуу жөнөткүңүз келсе, аны алдын ала жөнөтүү сунушталат. Анткени, шилтемелер жок кылынгандан кийин, башка документте камтылган бардык баалуулуктар автоматтык түрдө файлга жүктөлөт, формулаларда колдонулат жана уячанын дарегинин ордуна, тиешелүү уячалардагы маалымат жөн гана маанилерге айланат. .
Бардык китептерди кантип ажыратса болот
Бирок шилтемелердин саны өтө чоң болуп кетсе, аларды кол менен жок кылуу көпкө созулушу мүмкүн. Бул көйгөйдү чечүү үчүн, сиз атайын макро колдоно аласыз. Бул VBA-Excel кошумчасында. Сиз аны активдештирип, ошол эле аталыштагы өтмөккө өтүшүңүз керек. "Шилтемелер" бөлүмү болот, анда биз "Бардык шилтемелерди бузуу" баскычын басышыбыз керек.
VBA коду
Бул кошумчаны жандыруу мүмкүн болбосо, макросту өзүңүз түзө аласыз. Бул үчүн, Alt + F11 баскычтарын басуу менен Visual Basic редакторун ачып, код киргизүү талаасына төмөнкү саптарды жазыңыз.
Sub UnlinkWorkBooks()
Dim WbLinks
Dim and As Long
Case MsgBox тандаңыз("Башка китептерге шилтемелер бул файлдан өчүрүлөт жана башка китептерге шилтемеленген формулалар баалуулуктарга алмаштырылат." & vbCrLf & "Чын эле уланткыңыз келеби?", 36, "Байланыштан ажыратуубу?" )
Иш 7′ №
Sub Sub
Тандоону аяктоо
WbLinks = ActiveWorkbook.LinkSources(Түрү:=xlLinkTypeExcelLinks)
Эгерде Бош болбосо(WbLinks) Анда
i = 1 үчүн UBound(WbLinks)
ActiveWorkbook.BreakLink Name:=WbLinks(i), Type:=xlLinkTypeExcelLinks
кийинки
дагы
MsgBox "Бул файлда башка китептерге шилтемелер жок.", 64, "Башка китептерге шилтемелер"
жок болсо,
End Sub
Тандалган диапазондо гана байланыштарды кантип үзүү керек
Мезгил-мезгили менен шилтемелердин саны абдан көп жана колдонуучу алардын бирин жок кылгандан кийин, кээ бирлери ашыкча болсо, бардыгын кайра кайтаруу мүмкүн болбой калат деп коркот. Бирок бул кыйынчылыктан кутулуу оңой. Бул үчүн, сиз шилтемелерди жок кыла турган диапазонду тандап, андан кийин аларды жок кылышыңыз керек. Бул үчүн, сиз төмөнкү аракеттердин ырааттуулугун аткарышыңыз керек:
- Өзгөртүлүшү керек болгон маалымат топтомун тандаңыз.
- VBA-Excel кошумчасын орнотуп, андан кийин тиешелүү өтмөккө өтүңүз.
- Андан кийин, биз "Шилтемелер" менюну таап, "Тандалган диапазондогу шилтемелерди бузуу" баскычын чыкылдатыңыз.
Андан кийин, тандалган уячалар топтомундагы бардык шилтемелер жок кылынат.
Байланыштары үзүлбөсө эмне кылуу керек
Жогоруда айтылгандардын баары жакшы угулат, бирок иш жүзүндө ар дайым кээ бир нюанстар бар. Мисалы, байланыштар үзүлбөгөн жагдай болушу мүмкүн. Бул учурда, шилтемелерди автоматтык түрдө жаңыртуу мүмкүн эместигин билдирген диалог терезеси дагы эле пайда болот. Бул кырдаалда эмне кылуу керек?
- Биринчиден, аталган диапазондордо кандайдыр бир маалымат камтылганын текшеришиңиз керек. Бул үчүн, Ctrl + F3 баскычтарын басыңыз же "Формулалар" кошумча барагын - "Аты-жөнү башкаргычын" ачыңыз. Эгер файлдын аты толук болсо, анда сиз аны жөн гана түзөтүшүңүз же толугу менен алып салышыңыз керек. Аты аталган диапазондорду жок кылуудан мурун, туура эмес кадамдар жасалган болсо, баштапкы версиясына кайтып келүү үчүн файлды башка жерге көчүрүү керек.
- Эгер аттарды алып салуу менен маселени чече албасаңыз, шарттуу форматтоону текшерсеңиз болот. Башка таблицадагы уячаларга шарттуу форматтоо эрежелеринде кайрылса болот. Бул үчүн, "Башкы" өтмөктөн тиешелүү нерсени таап, андан кийин "Файл башкаруу" баскычын чыкылдатыңыз.
Адатта, Excel шарттуу форматтоодо башка иш китептеринин дарегин берүү мүмкүнчүлүгүн бербейт, бирок сиз башка файлга шилтеме менен аталган диапазонго кайрылсаңыз, бересиз. Адатта, шилтеме алынып салынгандан кийин да, шилтеме кала берет. Мындай шилтемени алып салууда эч кандай көйгөй жок, анткени шилтеме чындыгында иштебей жатат. Ошондуктан, аны алып салсаңыз, эч кандай жаман нерсе болбойт.
Сиз ошондой эле керексиз шилтемелер бар же жок экенин билүү үчүн "Маалыматтарды текшерүү" функциясын колдоно аласыз. Маалыматтарды текшерүүнүн "Тизме" түрү колдонулса, шилтемелер адатта кала берет. Бирок клеткалар көп болсо, эмне кылуу керек? Чын эле алардын ар бирин ырааттуу текшерүү керекпи? Албетте жок. Анткени, бул абдан көп убакытты талап кылат. Ошондуктан, аны олуттуу сактоо үчүн атайын кодду колдонуу керек.
Option Explicit
'————————————————————————————
Автор : Прист (Дмитрий Щербаков)
' Ар кандай татаалдыктагы MS Office үчүн тиркемелерди кесипкөй өнүктүрүү
' MS Excel боюнча тренингдерди өткөрүү
' https://www.excel-vba.ru
' [email protected]
'WebMoney—R298726502453; Yandex.Money — 41001332272872
' Максаты:
'————————————————————————————
Sub FindErrLink()
'биз булак файлына Маалыматтарды өзгөртүү шилтемесин карашыбыз керек
'жана ачкыч сөздөрдү бул жерге кичине тамга менен коюңуз (файлдын аталышынын бир бөлүгү)
"жылдызча каалаган сандагы символдорду алмаштырат, андыктан сиз так аты жөнүндө кабатыр болбоңуз
Const sToFndLink$ = "*сатуу 2018*"
Dim rr As Range, rc As Range, rres As Range, s$
'маалыматтарды текшерүү менен бардык уячаларды аныктоо
Ката Резюме Кийинки
Rr коюу = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
If rr Эч нерсе эмес, анда
MsgBox "Активдүү баракта маалыматтарды текшерүү менен уячалар жок", vbInformation, "www.excel-vba.ru"
Sub Sub
жок болсо,
Ката боюнча 0гө өтүңүз
Шилтемелер үчүн ар бир уячаны текшериңиз
Ар бир rc In rr
"Кандай болгон күндө да, биз каталарды өткөрүп жиберебиз - бул да болушу мүмкүн
Бирок биздин байланыштарыбыз аларсыз болушу керек жана алар сөзсүз табылат
s = «»
Ката Резюме Кийинки
s = rc.Validation.Formula1
Ката боюнча 0гө өтүңүз
'табылды - биз баарын өзүнчө диапазонго чогултабыз
Эгерде LCase(лер) sToFndLinkти жактырса, анда
If rres анда эч нерсе эмес
rres = rc коюңуз
дагы
Орнотуу rres = Биримдик(rc, rres)
жок болсо,
жок болсо,
кийинки
'эгер байланыш бар болсо, мындай маалымат текшерүүлөрү менен бардык уячаларды тандаңыз
Эгерде жок болсо, анда эч нерсе эмес
rres.Select
' 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 программасын колдонуп файлыбызды ачабыз. "Китептин мазмунунун бир бөлүгүндөгү ката" сыяктуу ката жөнүндө маалымат пайда болот. макулдук беребиз. Андан кийин дагы бир диалог пайда болот. Жабабыз.
Андан кийин, бардык шилтемелер алынып салынышы керек.