Як разарваць спасылкі ў Excel

Сувязь - вельмі карысная функцыя ў Excel. Бо вельмі часта карыстальнікам даводзіцца выкарыстоўваць інфармацыю з іншых файлаў. Але ў некаторых сітуацыях яны могуць прынесці больш шкоды, чым карысці. Бо, напрыклад, калі дасылаць гэтыя файлы поштай, то спасылкі не працуюць. Сёння мы больш падрабязна пагаворым пра тое, што рабіць, каб пазбегнуць такой праблемы.

Што такое адносіны ў excel

Адносіны ў Excel вельмі часта выкарыстоўваюцца ў спалучэнні з такімі функцыямі, як ВПРатрымаць інфармацыю з іншай працоўнай кніжкі. Ён можа быць у выглядзе спецыяльнай спасылкі, якая змяшчае адрас не толькі ячэйкі, але і кнігі, у якой знаходзяцца дадзеныя. У выніку такая спасылка выглядае прыкладна так: =VLOOKUP(A2;'[Продажы 2018.xlsx]Справаздача'!$A:$F;4;0). Або, для больш простага прадстаўлення, прадставіць адрас у наступным выглядзе: ='[Sales 2018.xlsx]Справаздача'!$A1. Давайце прааналізуем кожны з элементаў спасылкі гэтага тыпу:

  1. [Распродажы 2018.xlsx]. Гэты фрагмент змяшчае спасылку на файл, з якога вы хочаце атрымаць інфармацыю. Яе яшчэ называюць крыніцай.
  2. фатаграфіі. Мы выкарысталі наступную назву, але гэта не тое імя, якое павінна быць. Гэты блок утрымлівае назву ліста, у якім неабходна знайсці інфармацыю.
  3. $A:$F і $A1 – адрас ячэйкі або дыяпазону з дадзенымі, якія змяшчаюцца ў гэтым дакуменце.

Уласна, працэс стварэння спасылкі на знешні дакумент называецца спасылкаваннем. Пасля таго, як мы прапісалі адрас ячэйкі, якая змяшчаецца ў іншым файле, змесціва ўкладкі «Дадзеныя» змяняецца. А менавіта, становіцца актыўнай кнопка «Змяніць злучэнне», з дапамогай якой карыстальнік можа рэдагаваць існуючыя злучэння.

Сутнасць праблемы

Як правіла, дадатковых цяжкасцяў пры выкарыстанні спасылак не ўзнікае. Нават калі ўзнікае сітуацыя, пры якой вочкі мяняюцца, то ўсе спасылкі абнаўляюцца аўтаматычна. Але калі вы ўжо перайменуеце саму кнігу або перанясеце яе на іншы адрас, Excel становіцца бяссільным. Такім чынам, ён выдае наступнае паведамленне.

Як разарваць спасылкі ў Excel

Тут у карыстальніка ёсць два магчымых варыянту дзеянняў у гэтай сітуацыі. Ён можа націснуць «Працягнуць», і тады змены не будуць абноўлены, або ён можа націснуць кнопку «Змяніць асацыяцыі», з дапамогай якой ён можа абнавіць іх уручную. Пасля таго, як мы націснем гэтую кнопку, з'явіцца дадатковае акно, у якім можна будзе змяніць спасылкі, паказаўшы, дзе ў дадзены момант знаходзіцца правільны файл і як ён называецца.

Як разарваць спасылкі ў Excel

Акрамя таго, вы можаце рэдагаваць спасылкі праз адпаведную кнопку, размешчаную на ўкладцы «Дадзеныя». Карыстальнік таксама можа даведацца, што злучэнне разарвана з-за памылкі #LINK, якая з'яўляецца, калі Excel не можа атрымаць доступ да інфармацыі, размешчанай па пэўным адрасе з-за таго, што сам адрас несапраўдны.

Як выдаліць сувязь у excel

Адзін з самых простых метадаў вырашэння апісанай вышэй сітуацыі ў выпадку, калі вы не можаце самастойна абнавіць месцазнаходжанне файла па спасылцы, - выдаліць саму спасылку. Гэта асабліва лёгка зрабіць, калі дакумент змяшчае толькі адну спасылку. Для гэтага неабходна выканаць наступную паслядоўнасць дзеянняў:

  1. Адкрыйце меню «Дадзеныя».
  2. Знаходзім раздзел «Падключэння», а там - опцыю «Змяніць падключэння».
  3. Пасля гэтага націсніце «Адмяніць сувязь».

Калі вы збіраецеся адправіць гэтую кнігу іншаму чалавеку, настойліва рэкамендуецца зрабіць гэта загадзя. Бо пасля выдалення спасылак усе значэння, якія змяшчаюцца ў іншым дакуменце, будуць аўтаматычна загружацца ў файл, выкарыстоўвацца ў формулах, і замест адраса ячэйкі інфармацыя ў адпаведных вочках будзе проста трансфармавацца ў значэння .

Як развязаць усе кнігі

Але калі колькасць спасылак становіцца занадта вялікай, іх ручное выдаленне можа заняць шмат часу. Каб вырашыць гэтую задачу за адзін раз, можна выкарыстоўваць спецыяльны макрас. Ён знаходзіцца ў адоне VBA-Excel. Яе трэба актываваць і перайсці на аднайменную ўкладку. Там будзе раздзел «Спасылкі», у якім нам трэба націснуць на кнопку «Разарваць усе спасылкі».

Як разарваць спасылкі ў Excel

Код VBA

Калі няма магчымасці актываваць гэты дадатак, вы можаце стварыць макрас самастойна. Для гэтага адкрыйце рэдактар ​​Visual Basic, націснуўшы клавішы Alt + F11, і напішыце наступныя радкі ў поле ўводу кода.

Sub UnlinkWorkBooks()

    Цьмяны WbLinks

    Dim і As Long

    Выберыце Case MsgBox(“Усе спасылкі на іншыя кнігі будуць выдалены з гэтага файла, а формулы, якія спасылаюцца на іншыя кнігі, будуць заменены значэннямі.” & vbCrLf & “Вы ўпэўнены, што хочаце працягнуць?”, 36, “Выдаліць сувязь?” )

    Справа 7′ No

        Выйсці з пад

    Канец выбару

    WbLinks = ActiveWorkbook.LinkSources(Тып:=xlLinkTypeExcelLinks)

    Калі Not IsEmpty(WbLinks), то

        Для i = 1 To UBound (WbLinks)

            Імя ActiveWorkbook.BreakLink:=WbLinks(i), тып:=xlLinkTypeExcelLinks

        наступны

    яшчэ

        MsgBox «У гэтым файле няма спасылак на іншыя кнігі.», 64, «Спасылкі на іншыя кнігі»

    End If

End Sub

Як разарваць сувязі толькі ў абраным дыяпазоне

Час ад часу спасылак бывае вельмі шмат, і карыстальнік баіцца, што, выдаліўшы адну з іх, не атрымаецца вярнуць усё назад, калі якая-то апынулася лішняй. Але гэта праблема, якой лёгка пазбегнуць. Для гэтага вам трэба выбраць дыяпазон, у якім будуць выдаляцца спасылкі, а затым выдаліць іх. Для гэтага неабходна выканаць наступную паслядоўнасць дзеянняў:

  1. Выберыце набор дадзеных, які неабходна змяніць.
  2. Усталюйце надбудову VBA-Excel, а затым перайдзіце на адпаведную ўкладку.
  3. Далей знаходзім меню «Спасылкі» і націскаем на кнопку «Разарваць спасылкі ў выбраных дыяпазонах».

Як разарваць спасылкі ў Excel

Пасля гэтага ўсе спасылкі ў абраным наборы вочак будуць выдалены.

Што рабіць, калі сувязі не парваліся

Усё вышэйсказанае гучыць добра, але на практыцы заўсёды ёсць некаторыя нюансы. Напрыклад, можа быць сітуацыя, калі сувязі не разрываюцца. У гэтым выпадку па-ранейшаму з'яўляецца дыялогавае акно аб тым, што аўтаматычнае абнаўленне спасылак немагчыма. Што рабіць у гэтай сітуацыі?

  1. Па-першае, вам трэба праверыць, ці змяшчаецца інфармацыя ў названых дыяпазонах. Для гэтага націсніце спалучэнне клавіш Ctrl + F3 або адкрыйце ўкладку «Формулы» - «Дыспетчар імёнаў». Калі імя файла поўнае, то яго трэба проста адрэдагаваць або наогул выдаліць. Перш чым выдаляць найменныя дыяпазоны, вам неабходна скапіяваць файл у іншае месца, каб вы маглі вярнуцца да зыходнай версіі, калі былі зроблены няправільныя крокі.
  2. Калі вы не можаце вырашыць праблему, выдаліўшы імёны, вы можаце праверыць умоўнае фарматаванне. На ячэйкі іншай табліцы можна спасылацца ў правілах умоўнага фарматавання. Для гэтага знайдзіце адпаведны пункт на ўкладцы «Галоўная», а затым націсніце на кнопку «Упраўленне файламі». Як разарваць спасылкі ў Excel

    Звычайна Excel не дае вам магчымасці даць адрасы іншых кніг ва ўмоўным фарматаванні, але вы гэта зробіце, калі спасылаецеся на найменны дыяпазон са спасылкай на іншы файл. Звычайна нават пасля выдалення спасылкі спасылка застаецца. Няма ніякіх праблем з выдаленнем такой спасылкі, таму што спасылка насамрэч не працуе. Такім чынам, нічога страшнага не адбудзецца, калі вы яго выдаліце.

Вы таксама можаце выкарыстоўваць функцыю «Праверка дадзеных», каб даведацца, ці ёсць непатрэбныя спасылкі. Спасылкі звычайна застаюцца, калі выкарыстоўваецца тып праверкі даных «Спіс». Але што рабіць, калі клетак шмат? Няўжо трэба паслядоўна правяраць кожную з іх? Канешне не. Бо гэта зойме вельмі шмат часу. Такім чынам, вам трэба выкарыстоўваць спецыяльны код, каб істотна яго зэканоміць.

Option Explicit

'——————————————————————————————

' Аўтар : The_Prist (Шчарбакоў Дзмітрый)

' Прафесійная распрацоўка прыкладанняў для MS Office любой складанасці

' Правядзенне трэнінгаў па MS Excel

https://www.excel-vba.ru

' [электронная пошта абаронена]

'WebMoney—R298726502453; Яндэкс.Грошы — 41001332272872

' Мэта:

'——————————————————————————————

Sub FindErrLink()

    'нам трэба паглядзець спасылкі Data -Change на зыходны файл

    'і пастаўце тут ключавыя словы ў малым рэгістры (частка назвы файла)

    'зорачка проста замяняе любую колькасць сімвалаў, таму вам не трэба турбавацца аб дакладнай назве

    Const sToFndLink$ = «*распродажы 2018*»

    Dim rr As Range, rc As Range, rres As Range, s$

    'вызначыць усе ячэйкі з праверкай даных

    On Error Resume Next

    Усталюйце rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)

    Калі рр - гэта нічога

        MsgBox «На актыўным аркушы няма вочак з праверкай дадзеных», vbInformation, «www.excel-vba.ru»

        Выйсці з пад

    End If

    Пры памылцы GoTo 0

    'праверыць кожную ячэйку на наяўнасць спасылак

    Для кожнага rc У rr

        «на ўсялякі выпадак мы прапускаем памылкі – такое таксама можа здарыцца

        «але нашы сувязі павінны быць без іх, і яны абавязкова знойдуцца

        s = «»

        On Error Resume Next

        s = rc.Validation.Formula1

        Пры памылцы GoTo 0

        'Знойдзены - збіраем усё ў асобны шэраг

        Калі LCase(s) Like sToFndLink Тады

            Калі rres - гэта нічога

                Усталюйце rres = rc

            яшчэ

                Усталяваць rres = Union(rc, rres)

            End If

        End If

    наступны

    'калі ёсць сувязь, выберыце ўсе ячэйкі з такімі праверкамі дадзеных

    Калі Not rres - гэта нічога

        rres.Select

' rres.Interior.Color = vbRed 'калі вы хочаце вылучыць колерам

    End If

End Sub

Неабходна зрабіць стандартны модуль у рэдактары макрасаў, а потым уставіць туды гэты тэкст. Пасля гэтага выклікаем акно макраса спалучэннем клавіш Alt + F8, а затым выбіраем наш макрас і націскаем на кнопку «Выканаць». Ёсць некалькі рэчаў, якія трэба мець на ўвазе пры выкарыстанні гэтага кода:

  1. Перш чым шукаць спасылку, якая больш не актуальная, вы павінны спачатку вызначыць, як выглядае спасылка, па якой яна створана. Для гэтага зайдзіце ў меню «Дадзеныя» і знайдзіце там пункт «Змяніць спасылкі». Пасля гэтага неабходна паглядзець імя файла, і паказаць яго ў двукоссі. Напрыклад, так: Const sToFndLink$ = «*распродажы 2018*»
  2. Можна напісаць назву не цалкам, а проста замяніць непатрэбныя сімвалы зорачкай. А ў двукоссі напішыце назву файла маленькімі літарамі. У гэтым выпадку Excel знойдзе ўсе файлы, якія змяшчаюць у канцы такі радок.
  3. Гэты код здольны правяраць толькі спасылкі на аркушы, які ў цяперашні час актыўны.
  4. З дапамогай гэтага макраса вы можаце выбраць толькі знойдзеныя ім ячэйкі. Вы павінны выдаліць усё ўручную. Гэта плюс, бо можна яшчэ раз усё пераправерыць.
  5. Таксама вочкі можна зрабіць вылучанымі адмысловым колерам. Для гэтага прыбярыце апостраф перад гэтым радком. rres.Interior.Color = vbRed

Звычайна пасля выканання крокаў, апісаных у інструкцыях вышэй, непатрэбных падключэнняў больш не павінна быць. Але калі некаторыя з іх ёсць у дакуменце, і вы не можаце іх выдаліць па тых ці іншых прычынах (тыповы прыклад - бяспека дадзеных у аркушы), то вы можаце выкарыстоўваць іншую паслядоўнасць дзеянняў. Гэтая інструкцыя дзейнічае толькі для версій 2007 і вышэй.

  1. Ствараем рэзервовую копію дакумента.
  2. Адкрыйце гэты дакумент з дапамогай архіватара. Вы можаце выкарыстоўваць любы, які падтрымлівае фармат ZIP, але WinRar таксама падыдзе, а таксама той, што ўбудаваны ў Windows.
  3. У якое з'явілася архіве трэба знайсці тэчку xl, а затым адкрыць externalLinks.
  4. Гэтая папка змяшчае ўсе знешнія спасылкі, кожная з якіх адпавядае файлу выгляду externalLink1.xml. Усе яны толькі пранумараваны, і таму карыстальнік не мае магчымасці зразумець, што гэта за злучэнне. Каб зразумець, што гэта за злучэнне, трэба адкрыць тэчку _rels і паглядзець яе там.
  5. Пасля гэтага мы выдаляем усе або пэўныя спасылкі на падставе таго, што даведаемся ў файле externalLinkX.xml.rels.
  6. Пасля гэтага адкрываем наш файл з дапамогай Excel. Там будзе інфармацыя пра памылку, напрыклад «Памылка ў частцы змесціва ў Кнізе». Даем згоду. Пасля гэтага з'явіцца яшчэ адно дыялогавае акно. Закрываем.

Пасля гэтага ўсе спасылкі трэба выдаліць.

Пакінуць каментар