Мундариҷа
Муошират як хусусияти хеле муфид дар Excel мебошад. Дар ниҳоят, корбарон аксар вақт бояд маълумотро аз файлҳои дигар истифода баранд. Аммо дар баъзе ҳолатҳо, онҳо метавонанд аз фоида бештар зарар расонанд. Баъд аз ҳама, масалан, агар шумо ин файлҳоро тавассути почта фиристед, истинодҳо кор намекунанд. Имрӯз мо ба таври муфассал дар бораи чӣ бояд кард, ки барои пешгирӣ кардани чунин мушкилот сӯҳбат хоҳем кард.
Муносибатҳо дар Excel чӣ гунаанд
Муносибатҳо дар Excel аксар вақт дар якҷоягӣ бо функсияҳо ба монанди VPRки аз дигар дафтарчаи мехнатй маълумот гиранд. Он метавонад шакли пайванди махсус дошта бошад, ки суроғаи на танҳо ячейка, балки китоберо, ки дар он маълумот ҷойгир аст, дар бар мегирад. Дар натиҷа, чунин пайванд чунин менамояд: =VLOOKUP(A2;'[Sales 2018.xlsx]Ҳисобот'!$A:$F;4;0). Ё, барои муаррифии соддатар, суроғаро дар шакли зерин муаррифӣ кунед: ='[Sales 2018.xlsx]Ҳисобот'!$A1. Биёед ҳар як унсури пайванди ин намудро таҳлил кунем:
- [Sales 2018.xlsx]. Ин фрагмент дорои истинод ба файлест, ки шумо мехоҳед аз он маълумот гиред. Он инчунин сарчашма номида мешавад.
- Суратҳо. Мо номи зеринро истифода мебарем, аммо ин номест, ки бояд бошад. Ин блок номи варақеро дар бар мегирад, ки дар он шумо бояд маълумотро пайдо кунед.
- $A:$F ва $A1 - суроғаи ячейка ё диапазони дорои маълумоти дар ин ҳуҷҷат мавҷудбуда.
Дар асл, раванди эҷоди истинод ба ҳуҷҷати беруна пайванд номида мешавад. Пас аз ба қайд гирифтани суроғаи ячейка дар файли дигар, мундариҷаи ҷадвали "Маълумот" тағир меёбад. Махз, тугмаи "Тағйир додани пайвастҳо" фаъол мешавад, ки бо ёрии он корбар метавонад пайвастҳои мавҷударо таҳрир кунад.
Мохияти масъала
Чун қоида, барои истифодаи истинодҳо ҳеҷ гуна мушкилоти иловагӣ ба миён намеояд. Ҳатто агар вазъияте ба миён ояд, ки дар он ҳуҷайраҳо тағир меёбанд, ҳама истинодҳо ба таври худкор нав карда мешаванд. Аммо агар шумо аллакай номи китоби кориро тағир диҳед ё онро ба суроғаи дигар интиқол диҳед, Excel беқувват мешавад. Аз ин рӯ, он паёми зеринро истеҳсол мекунад.
Дар ин ҷо, корбар ду имконоти имконпазирро барои чӣ гуна амал кардан дар ин вазъият дорад. Вай метавонад "Идома" -ро клик кунад ва он гоҳ тағиротҳо нав карда намешаванд, ё ӯ метавонад тугмаи "Тағйир додани Ассотсиатсияҳо" -ро пахш кунад, ки бо он метавонад онҳоро дастӣ навсозӣ кунад. Пас аз пахш кардани ин тугма, равзанаи иловагӣ пайдо мешавад, ки дар он истинодҳоро тағир додан мумкин аст, ки дар он файли дуруст дар айни замон дар куҷо ҷойгир аст ва он чӣ ном дорад.
Илова бар ин, шумо метавонед истинодҳоро тавассути тугмаи мувофиқе, ки дар ҷадвали "Маълумот" ҷойгир аст, таҳрир кунед. Истифодабаранда инчунин метавонад фаҳмад, ки пайвастшавӣ бо хатои #LINK вайрон шудааст, ки вақте пайдо мешавад, ки Excel ба маълумоти дар суроғаи мушаххас ҷойгиршуда бо сабаби нодуруст будани худи суроға дастрасӣ пайдо карда наметавонад.
Чӣ тавр ҷудо кардан дар Excel
Яке аз усулҳои соддатарин барои ҳалли вазъияти дар боло тавсифшуда, агар шумо худатон ҷойгиршавии файли алоқамандро навсозӣ карда наметавонед, ин нест кардани худи истинод мебошад. Ин кор махсусан осон аст, агар ҳуҷҷат танҳо як пайванд дошта бошад. Барои ин, шумо бояд пайдарпайии амалҳои зеринро иҷро кунед:
- Менюи "Маълумот" -ро кушоед.
- Мо қисмати "Пайвастҳо" -ро пайдо мекунем ва дар он ҷо - варианти "Тағйир додани пайвастҳо".
- Пас аз он, тугмаи "Нест кардан" -ро клик кунед.
Агар шумо ният доред, ки ин китобро ба шахси дигар фиристед, тавсия дода мешавад, ки ин корро пешакӣ анҷом диҳед. Дар ниҳоят, пас аз нест кардани истинодҳо, ҳама арзишҳое, ки дар ҳуҷҷати дигар мавҷуданд, ба таври худкор ба файл бор карда мешаванд, ки дар формулаҳо истифода мешаванд ва ба ҷои суроғаи ячейка, иттилоот дар ячейкаҳои мувофиқ танҳо ба арзишҳо табдил меёбанд. .
Чӣ тавр ҳамаи китобҳоро ҷудо кардан мумкин аст
Аммо агар шумораи истинодҳо хеле зиёд шавад, дастӣ нест кардани онҳо метавонад вақти зиёдро талаб кунад. Барои ҳалли ин мушкилот дар як вақт шумо метавонед макроси махсусро истифода баред. Он дар иловаи VBA-Excel аст. Шумо бояд онро фаъол созед ва ба ҷадвали ҳамон ном равед. Қисмати "Истинодҳо" мавҷуд аст, ки дар он мо бояд тугмаи "Шикастани ҳама истинодҳо" -ро пахш кунем.
Рамзи VBA
Агар фаъол кардани ин изофа имконнопазир бошад, шумо метавонед худатон макрос созед. Барои ин муҳаррири Visual Basic-ро бо пахш кардани тугмаҳои Alt + F11 кушоед ва дар майдони вуруди код сатрҳои зеринро нависед.
Sub UnlinkWorkBooks()
WbLinks хира
Dim and As Long
Case MsgBox-ро интихоб кунед("Ҳама истинодҳо ба китобҳои дигар аз ин файл нест карда мешаванд ва формулаҳои марбут ба китобҳои дигар бо арзишҳо иваз карда мешаванд." & vbCrLf & "Шумо мутмаин ҳастед, ки мехоҳед идома диҳед?", 36, "Барои пайваст?" )
Парвандаи 7' №
Баромади зер
Интихоби хотима
WbLinks = ActiveWorkbook.LinkSources(Намуд:=xlLinkTypeExcelLinks)
Агар холӣ набошад(WbLinks) Пас
Барои i = 1 Ба UBound(WbLinks)
ActiveWorkbook.BreakLink Ном:=WbLinks(i), Навъи:=xlLinkTypeExcelLinks
Баъдӣ
боз
MsgBox "Дар ин файл истинод ба китобҳои дигар вуҷуд надорад.", 64, "Истинодҳо ба китобҳои дигар"
Агар End
End Sub
Чӣ тавр бояд танҳо дар доираи интихобшуда пайвандҳоро вайрон кард
Баъзан шумораи истинодҳо хеле зиёд мешавад ва корбар метарсад, ки пас аз нест кардани яке аз онҳо, агар баъзеи онҳо зиёдатӣ бошад, ҳама чизро баргардонидан имконнопазир аст. Аммо ин мушкилотест, ки пешгирӣ кардан осон аст. Барои ин, шумо бояд диапазонеро, ки дар он истинодҳоро нест кунед, интихоб кунед ва сипас онҳоро нест кунед. Барои ин, шумо бояд пайдарпайии амалҳои зеринро иҷро кунед:
- Маҷмӯи маълумотеро, ки бояд тағир дода шавад, интихоб кунед.
- Иловаи VBA-Excel-ро насб кунед ва сипас ба ҷадвали мувофиқ гузаред.
- Баъдан, мо менюи "Истинодҳо" -ро пайдо мекунем ва тугмаи "Истинодҳоро дар диапазони интихобшуда" пахш мекунем.
Баъд аз ин, ҳамаи истинодҳо дар маҷмӯи интихобшудаи чашмакҳо нест карда мешаванд.
Чӣ бояд кард, агар риштаҳо канда нашаванд
Ҳамаи гуфтаҳои боло хуб садо медиҳанд, аммо дар амал ҳамеша баъзе нозукиҳо мавҷуданд. Масалан, мумкин аст вазъияте бошад, ки риштаҳо канда нашудаанд. Дар ин ҳолат, қуттии муколама ҳанӯз пайдо мешавад, ки дар он гуфта мешавад, ки ба таври худкор навсозии истинодҳо имконнопазир аст. Дар ин вазъият чӣ бояд кард?
- Аввалан, шумо бояд тафтиш кунед, ки оё ягон маълумот дар диапазони номбаршуда мавҷуд аст. Барои ин, комбинатсияи калидҳоро пахш кунед Ctrl + F3 ё ҷадвали "Формулаҳо" - "Мудири ном" -ро кушоед. Агар номи файл пур бошад, пас шумо бояд онро таҳрир кунед ё тамоман нест кунед. Пеш аз нест кардани диапазонҳои номбаршуда, шумо бояд файлро ба ягон макони дигар нусхабардорӣ кунед, то шумо метавонед ба нусхаи аслӣ баргардед, агар қадамҳои нодуруст андешида шуда бошанд.
- Агар шумо бо нест кардани номҳо мушкилотро ҳал карда натавонед, шумо метавонед форматкунии шартиро тафтиш кунед. Ба чашмакҳои ҷадвали дигар дар қоидаҳои форматкунии шартӣ истинод кардан мумкин аст. Барои ин, дар ҷадвали "Home" ашёи мувофиқро пайдо кунед ва тугмаи "Идоракунии файл" -ро клик кунед.
Одатан, Excel ба шумо имкон намедиҳад, ки суроғаи китобҳои дигари кориро дар форматкунии шартӣ диҳед, аммо шумо ин корро мекунед, агар шумо ба диапазони номбаршуда бо истинод ба файли дигар муроҷиат кунед. Одатан, ҳатто пас аз нест кардани пайванд, пайванд боқӣ мемонад. Барои нест кардани чунин пайванд мушкиле нест, зеро пайванд дар асл кор намекунад. Аз ин рӯ, агар шумо онро хориҷ кунед, ҳеҷ чизи бад рӯй нахоҳад дод.
Шумо инчунин метавонед функсияи "Тафтиши маълумот" -ро истифода баред, то бидонед, ки оё ягон истиноди нолозим вуҷуд дорад. Пайвандҳо одатан боқӣ мемонанд, агар навъи "Рӯйхат" тасдиқи маълумот истифода шавад. Аммо агар ҳуҷайраҳо зиёд бошанд, чӣ бояд кард? Оё воқеан зарур аст, ки ҳар яки онҳоро пай дар пай тафтиш кунем? Албатта на. Баъд аз ҳама, ин вақти хеле дарозро мегирад. Аз ин рӯ, шумо бояд рамзи махсусро истифода баред, то онро ба таври назаррас захира кунед.
Опсияи равшан
'——————————————————————————
Муаллиф: Прист (Дмитрий Щербаков)
' Рушди касбии замимаҳо барои MS Office дар ҳама гуна мураккабӣ
' Гузаронидани тренингҳо оид ба MS Excel
' https://www.excel-vba.ru
' [почтаи электронӣ ҳифз шудааст]
'WebMoney — R298726502453; Яндекс.Пул — 41001332272872
' Мақсад:
'——————————————————————————
Sub FindErrLink()
'мо бояд дар истиноди Маълумот -Тағйир додани истинод ба файли манбаъ назар кунем
'ва калимаҳои калидиро дар ин ҷо бо ҳарфҳои хурд гузоред (қисми номи файл)
'ситорача танҳо ҳар як шумораи аломатҳоро иваз мекунад, то шумо дар бораи номи дақиқ хавотир нашавед
Const sToFndLink$ = "*фурӯш 2018*"
Dim rr As Range, rc As Diage, rres As Range, s$
'ҳамаи ячейкаҳоро бо тасдиқи маълумот муайян кунед
Дар бораи хатогии хатогӣ Next
Танзими rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
Агар rr Ҳеҷ чиз набошад, пас
MsgBox "Дар варақаи фаъол ячейкаҳои тасдиқи маълумот вуҷуд надоранд", vbInformation, "www.excel-vba.ru"
Баромади зер
Агар End
Дар хатогии GoTo 0
'ҳар як чашмакро барои истинодҳо тафтиш кунед
Барои ҳар як rc In rr
'танҳо дар ҳолате, ки мо хатогиҳоро аз даст медиҳем - ин метавонад рӯй диҳад
«аммо алокахои мо бояд бе онхо бошанд ва онхо хатман пайдо мешаванд
s = «»
Дар бораи хатогии хатогӣ Next
s = rc.Validation.Formula1
Дар хатогии GoTo 0
' ёфт — мо ҳама чизро дар як қатор алоҳида ҷамъоварӣ мекунем
Агар LCase(ҳо) ба sToFndLink монанд бошанд
Агар rres Ҳеҷ чиз нест
Танзими rres = rc
боз
Танзими rres = Иттиҳод (rc, rres)
Агар End
Агар End
Баъдӣ
'агар пайвастшавӣ мавҷуд бошад, ҳамаи чашмакҳоро бо чунин тафтиши маълумот интихоб кунед
Агар Не rres Ҳеҷ чиз аст, пас
rres.Интихоб кунед
' rres.Interior.Color = vbRed 'агар шумо хоҳед, ки бо ранг таъкид кунед
Агар End
End Sub
Дар муҳаррири макрос модули стандартиро сохтан лозим аст ва баъд ин матнро ба он ҷо гузоред. Пас аз он, бо истифода аз комбинатсияи калидҳо Alt + F8 ба равзанаи макро занг занед ва пас макроси моро интихоб кунед ва тугмаи "Иҷро" -ро клик кунед. Ҳангоми истифодаи ин код чанд чизро бояд дар хотир дошт:
- Пеш аз ҷустуҷӯи пайванде, ки дигар мувофиқ нест, шумо бояд аввал муайян кунед, ки пайванде, ки тавассути он сохта шудааст, чӣ гуна аст. Барои ин, ба менюи "Маълумот" гузаред ва дар он ҷо ҷузъи "Истинодҳоро тағир диҳед" -ро пайдо кунед. Баъд аз ин, шумо бояд номи файлро бубинед ва онро дар нохунак нишон диҳед. Масалан, ба ин монанд: Const sToFndLink$ = "*фурӯш 2018*"
- Номро на пурра навиштан мумкин аст, балки аломатҳои нолозимро бо ситорача иваз кардан мумкин аст. Ва дар нохунак номи файлро бо ҳарфҳои хурд нависед. Дар ин ҳолат, Excel ҳама файлҳоеро, ки чунин сатр доранд, дар охир пайдо мекунад.
- Ин код танҳо метавонад истинодҳоро дар варақаи фаъол тафтиш кунад.
- Бо ин макрос, шумо метавонед танҳо чашмакҳоеро, ки онро ёфтаанд, интихоб кунед. Шумо бояд ҳама чизро дастӣ нест кунед. Ин як плюс аст, зеро шумо метавонед ҳама чизро дубора тафтиш кунед.
- Шумо инчунин метавонед ҳуҷайраҳои бо ранги махсус таъкидшударо созед. Барои ин пеш аз ин сатр апострофро хориҷ кунед. rres.Interior.Color = vbRed
Одатан, пас аз анҷом додани амалҳои дар дастурҳои дар боло тавсифшуда, дигар пайвастҳои нолозим набояд вуҷуд дошта бошанд. Аммо агар баъзеи онҳо дар ҳуҷҷат мавҷуд бошанд ва шумо бо ин ё он сабаб онҳоро нест карда наметавонед (мисоли маъмулӣ амнияти маълумот дар варақ аст), пас шумо метавонед пайдарпайии дигари амалҳоро истифода баред. Ин дастур танҳо барои версияҳои 2007 ва навтар эътибор дорад.
- Мо нусхаи эҳтиётии ҳуҷҷатро эҷод мекунем.
- Бо истифода аз архив ин ҳуҷҷатро кушоед. Шумо метавонед ҳама чизеро, ки формати ZIP-ро дастгирӣ мекунад, истифода баред, аммо WinRar низ кор хоҳад кард, инчунин формати дар Windows сохташуда.
- Дар бойгоние, ки пайдо мешавад, шумо бояд ҷузвдони xl -ро пайдо кунед ва пас аз берун пайвандҳо кушоед.
- Ин ҷузвдон дорои ҳама истинодҳои беруна мебошад, ки ҳар кадоми онҳо ба файли шакли externalLink1.xml мувофиқат мекунанд. Ҳамаи онҳо танҳо рақамгузорӣ шудаанд ва аз ин рӯ, корбар имкони фаҳмидани он, ки ин чӣ гуна пайваст аст, надорад. Барои фаҳмидани кадом намуди пайвастшавӣ, шумо бояд папкаи _rels -ро кушоед ва дар он ҷо ба он нигаред.
- Баъд аз ин, мо ҳама ё истинодҳои мушаххасро дар асоси он чизе, ки мо дар файли externalLinkX.xml.rels меомӯзем, нест мекунем.
- Пас аз он мо файли худро бо истифода аз Excel кушоем. Маълумот дар бораи хатогӣ ба монанди "Хато дар қисми мундариҷаи Китоб" мавҷуд хоҳад буд. ризоият медихем. Пас аз он, муколамаи дигар пайдо мешавад. Мо онро мепӯшем.
Баъд аз ин, ҳамаи пайвандҳо бояд хориҷ карда шаванд.