содржина
Комуникацијата е многу корисна карактеристика во Excel. На крајот на краиштата, многу често корисниците треба да користат информации од други датотеки. Но, во некои ситуации, тие можат да направат повеќе штета отколку корист. На крајот на краиштата, на пример, ако ги испратите овие датотеки по пошта, врските не работат. Денес ќе разговараме подетално за тоа што да направите за да избегнете таков проблем.
Кои се односите во Excel
Врските во Excel многу често се користат во врска со функции како што се VPRда се добијат информации од друга работна книга. Може да има форма на специјална врска која ја содржи адресата не само на ќелијата, туку и на книгата во која се наоѓаат податоците. Како резултат на тоа, таквата врска изгледа вака: =VLOOKUP(A2;'[Продажба 2018.xlsx]Извештај'!$A:$F;4;0). Или, за поедноставно претставување, претставувајте ја адресата во следнава форма: ='[Продажба 2018.xlsx]Извештај'!$A1. Ајде да го анализираме секој од елементите на врската од овој тип:
- [Продажба 2018.xlsx]. Овој фрагмент содржи врска до датотеката од која сакате да добиете информации. Се нарекува и извор.
- слики. Го користевме следново име, но ова не е името што треба да биде. Овој блок го содржи името на листот во кој треба да најдете информации.
- $A:$F и $A1 - адресата на ќелијата или опсегот што содржи податоци што се содржани во овој документ.
Всушност, процесот на создавање врска до надворешен документ се нарекува поврзување. Откако ќе ја регистрираме адресата на ќелијата содржана во друга датотека, содржината на табулаторот „Податоци“ се менува. Имено, се активира копчето „Change connections“ со чија помош корисникот може да ги уредува постоечките конекции.
Суштината на проблемот
Како по правило, не се појавуваат дополнителни тешкотии за да се користат врски. Дури и ако се појави ситуација во која ќелиите се менуваат, тогаш сите врски автоматски се ажурираат. Но, ако веќе ја преименувате самата работна книга или ја преместите на друга адреса, Excel станува немоќен. Затоа, ја произведува следнава порака.
Овде, корисникот има две можни опции за тоа како да постапи во оваа ситуација. Може да кликне „Продолжи“ и потоа промените нема да се ажурираат или може да кликне на копчето „Промени асоцијации“ со кое може рачно да ги ажурира. Откако ќе кликнете на ова копче, ќе се појави дополнителен прозорец во кој ќе може да се сменат врските, означувајќи каде се наоѓа точната датотека во моментот и како се нарекува.
Покрај тоа, можете да уредувате врски преку соодветното копче што се наоѓа на табулаторот „Податоци“. Корисникот исто така може да открие дека врската е прекината со грешката #LINK, која се појавува кога Excel не може да пристапи до информациите лоцирани на одредена адреса поради фактот што самата адреса е неважечка.
Како да се прекине врската во ексел
Еден од наједноставните методи за решавање на ситуацијата опишана погоре во случај да не можете сами да ја ажурирате локацијата на поврзаната датотека е да ја избришете самата врска. Ова е особено лесно да се направи ако документот содржи само една врска. За да го направите ова, мора да ја извршите следната низа чекори:
- Отворете го менито „Податоци“.
- Го наоѓаме делот „Поврзувања“, а таму - опцијата „Промени врски“.
- После тоа, кликнете на „Одврзи“.
Ако имате намера да ја испратите оваа книга по пошта на друго лице, препорачливо е да го сторите тоа однапред. На крајот на краиштата, по бришењето на врските, сите вредности што се содржани во друг документ автоматски ќе се вчитаат во датотеката, користени во формули, а наместо адресата на ќелијата, информациите во соодветните ќелии едноставно ќе се трансформираат во вредности .
Како да ја прекинете врската со сите книги
Но, ако бројот на врски стане преголем, рачното бришење на истите може да потрае многу време. За да го решите овој проблем со едно движење, можете да користите специјално макро. Тоа е во додатокот VBA-Excel. Треба да го активирате и да отидете на табот со исто име. Ќе има дел „Линкови“, во кој треба да кликнеме на копчето „Скрши ги сите врски“.
VBA код
Ако не е можно да го активирате овој додаток, можете сами да креирате макро. За да го направите ова, отворете го уредникот на Visual Basic со притискање на копчињата Alt + F11 и напишете ги следните редови во полето за внесување код.
Под UnlinkWorkBooks()
Затемнети WbLinks
Затемнети и Колку долго
Изберете Case MsgBox („Сите референци до други книги ќе бидат отстранети од оваа датотека, а формулите што се однесуваат на други книги ќе бидат заменети со вредности.“ & vbCrLf & „Дали сте сигурни дека сакате да продолжите?“, 36, „Одврзи?“ )
Случај 7′ бр
Излез под
Крај Избери
WbLinks = ActiveWorkbook.LinkSources(Тип:=xlLinkTypeExcelLinks)
Ако не е празно (WbLinks) Тогаш
За i = 1 до UBound (WbLinks)
ActiveWorkbook.BreakLink Name:=WbLinks(i), Тип:=xlLinkTypeExcelLinks
Следна
друго
MsgBox „Нема врски до други книги во оваа датотека.“, 64, „Линкови до други книги“
Ако крајот
Под крајот
Како да ги раскинете врските само во избраниот опсег
Од време на време, бројот на врски е многу голем, а корисникот се плаши дека по бришењето на една од нив, нема да може да се врати сè назад ако некои биле излишни. Но, ова е проблем што е лесно да се избегне. За да го направите ова, треба да го изберете опсегот во кој ќе ги избришете врските, а потоа да ги избришете. За да го направите ова, мора да ја извршите следната низа на дејства:
- Изберете го датот што треба да се измени.
- Инсталирајте го додатокот VBA-Excel, а потоа одете на соодветното јазиче.
- Следно, го наоѓаме менито „Линкови“ и кликнуваме на копчето „Скрши врски во избраните опсези“.
После тоа, сите врски во избраниот сет на ќелии ќе бидат избришани.
Што да направите ако врските не се прекинат
Сето горенаведено звучи добро, но во пракса секогаш има некои нијанси. На пример, може да има ситуација кога врските не се прекинати. Во овој случај, сè уште се појавува дијалог-кутија во која се наведува дека не е можно автоматско ажурирање на врските. Што да направите во оваа ситуација?
- Прво, треба да проверите дали некоја информација е содржана во именуваните опсези. За да го направите ова, притиснете ја комбинацијата на копчиња Ctrl + F3 или отворете го табулаторот „Формули“ - „Управник со имиња“. Ако името на датотеката е полно, тогаш само треба да го уредите или целосно да го отстраните. Пред да ги избришете именуваните опсези, треба да ја копирате датотеката на друга локација за да можете да се вратите на оригиналната верзија доколку се преземени погрешни чекори.
- Ако не можете да го решите проблемот со отстранување на имиња, можете да го проверите условното форматирање. Ќелиите во друга табела може да се референцираат во правилата за условно форматирање. За да го направите ова, пронајдете ја соодветната ставка на табулаторот „Дома“, а потоа кликнете на копчето „Управување со датотеки“.
Вообичаено, Excel не ви дава можност да ја дадете адресата на другите работни книги во условно форматирање, но ја давате ако се однесувате на именуван опсег со повикување на друга датотека. Обично, дури и откако ќе се отстрани врската, врската останува. Нема проблем да се отстрани таквата врска, бидејќи врската всушност не работи. Затоа, ништо лошо нема да се случи ако го отстраните.
Можете исто така да ја користите функцијата „Проверка на податоци“ за да дознаете дали има непотребни врски. Врските обично остануваат ако се користи типот на валидација на податоци „Список“. Но, што да направите ако има многу клетки? Дали е навистина потребно да се провери секој од нив последователно? Се разбира не. На крајот на краиштата, ќе биде потребно многу долго време. Затоа, треба да користите специјален код за значително да го зачувате.
Опција експлицитна
'——————————————————————————————
Автор: The_Prist (Шчербаков Дмитриј)
Професионален развој на апликации за MS Office од секаква сложеност
Спроведување обуки за MS Excel
https://www.excel-vba.ru
' [заштитена е-пошта]
'WebMoney-R298726502453; Yandex.Money — 41001332272872
' Цел:
'——————————————————————————————
Под FindErrLink()
„Треба да погледнеме во врската Data -Change links до изворната датотека
и ставете ги клучните зборови овде со мали букви (дел од името на датотеката)
„Ѕвездичката само заменува кој било број на знаци за да не треба да се грижите за точното име
Const sToFndLink$ = „*продажба 2018*“
Dim rr As Range, rc As Range, rres As Range, s$
'дефинирајте ги сите ќелии со валидација на податоци
На Грешка продолжи следната
Поставете rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
Ако rr не е ништо тогаш
MsgBox „Нема ќелии со валидација на податоци на активниот лист“, vbInformation, „www.excel-vba.ru“
Излез под
Ако крајот
На грешка GoTo 0
„Проверете ја секоја ќелија за врски
За секој rc Во rr
„За секој случај, ние прескокнуваме грешки - ова исто така може да се случи
„Но, нашите врски мора да бидат без нив и тие дефинитивно ќе бидат пронајдени
s = «»
На Грешка продолжи следната
s = rc.Валидација.Формула1
На грешка GoTo 0
'најдов - собираме сè во посебен опсег
Ако LCase(s) Like sToFndLink Потоа
Ако rres е ништо тогаш
Поставете rres = rc
друго
Поставете rres = Унија (rc, rres)
Ако крајот
Ако крајот
Следна
„Ако има врска, изберете ги сите ќелии со такви проверки на податоци
Ако не rres е ништо тогаш
rres.Изберете
' rres.Interior.Color = vbRed 'ако сакате да истакнете со боја
Ако крајот
Под крајот
Неопходно е да се направи стандарден модул во макро уредникот, а потоа да се вметне овој текст таму. После тоа, повикајте го макро прозорецот користејќи ја комбинацијата на копчиња Alt + F8, а потоа изберете го нашето макро и кликнете на копчето „Стартувај“. Има неколку работи што треба да ги имате на ум кога го користите овој код:
- Пред да барате врска која повеќе не е релевантна, прво мора да одредите како изгледа врската преку која е креирана. За да го направите ова, одете во менито „Податоци“ и пронајдете ја ставката „Промени ги врските“ таму. После тоа, треба да го погледнете името на датотеката и да го наведете во наводници. На пример, вака: Const sToFndLink$ = „*продажба 2018*“
- Можно е да се напише името не во целост, туку едноставно да се заменат непотребните знаци со ѕвездичка. И во наводници, напишете го името на датотеката со мали букви. Во овој случај, Excel ќе ги најде сите датотеки што содржат таква низа на крајот.
- Овој код може да провери само за врски во листот што е моментално активен.
- Со ова макро, можете да ги изберете само ќелиите што ги нашол. Треба да избришете сè рачно. Ова е плус, бидејќи можете повторно да проверите сè.
- Можете исто така да ги направите ќелиите означени во посебна боја. За да го направите ова, отстранете го апострофот пред оваа линија. rres.Внатрешност.Боја = vbRed
Обично, откако ќе ги завршите чекорите опишани во упатствата погоре, не треба да има повеќе непотребни врски. Но, ако има некои од нив во документот и не можете да ги отстраните поради една или друга причина (типичен пример е безбедноста на податоците во лист), тогаш можете да користите различна низа на дејства. Оваа инструкција важи само за верзии од 2007 година и повисоки.
- Ние создаваме резервна копија на документот.
- Отворете го овој документ користејќи го архиверот. Можете да користите било кој што поддржува ZIP формат, но WinRar исто така ќе работи, како и оној вграден во Windows.
- Во архивата што се појавува, треба да ја пронајдете папката xl, а потоа да ги отворите надворешните врски.
- Оваа папка ги содржи сите надворешни врски, од кои секоја одговара на датотека од формата externalLink1.xml. Сите тие се само нумерирани, и затоа корисникот нема можност да разбере за каква врска станува збор. За да разберете каква врска, треба да ја отворите папката _rels и да ја погледнете таму.
- После тоа, ги отстрануваме сите или конкретни врски, врз основа на она што го учиме во датотеката externalLinkX.xml.rels.
- После тоа, ја отвораме нашата датотека користејќи Excel. Ќе има информации за грешка како „Грешка во дел од содржината во Книгата“. Ние даваме согласност. После тоа, ќе се појави друг дијалог. Го затвораме.
После тоа, сите врски треба да се отстранат.