Excelda havolalarni qanday buzish mumkin

Aloqa Excelda juda foydali xususiyatdir. Axir, ko'pincha foydalanuvchilar boshqa fayllardan ma'lumotlardan foydalanishlari kerak. Ammo ba'zi hollarda ular yaxshilikdan ko'ra ko'proq zarar etkazishi mumkin. Axir, masalan, agar siz ushbu fayllarni pochta orqali yuborsangiz, havolalar ishlamayapti. Bugun biz bunday muammoga duch kelmaslik uchun nima qilish kerakligi haqida batafsilroq gaplashamiz.

Excelda qanday munosabatlar mavjud

Exceldagi munosabatlar ko'pincha kabi funktsiyalar bilan birgalikda ishlatiladi VPRboshqa ish kitobidan ma'lumot olish uchun. U nafaqat hujayraning, balki ma'lumotlar joylashgan kitobning manzilini ham o'z ichiga olgan maxsus havola shaklida bo'lishi mumkin. Natijada, bunday havola quyidagicha ko'rinadi: =VLOOKUP(A2;'[Sales 2018.xlsx]Hisobot'!$A:$F;4;0). Yoki soddaroq tasvirlash uchun manzilni quyidagi shaklda ifodalang: ='[Sales 2018.xlsx]Hisobot'!$A1. Keling, ushbu turdagi havola elementlarining har birini tahlil qilaylik:

1. [Sales 2018.xlsx]. Ushbu fragmentda siz ma'lumot olmoqchi bo'lgan faylga havola mavjud. U manba deb ham ataladi.
2. Rasm. Biz quyidagi nomdan foydalandik, lekin bu bo'lishi kerak bo'lgan nom emas. Ushbu blokda siz ma'lumot topishingiz kerak bo'lgan varaq nomi mavjud.
3. $A:$F va $A1 – ushbu hujjatdagi ma'lumotlarni o'z ichiga olgan hujayra yoki diapazonning manzili.

Aslida, tashqi hujjatga havola yaratish jarayoni bog'lanish deb ataladi. Boshqa faylda joylashgan katak manzilini ro'yxatdan o'tkazganimizdan so'ng, "Ma'lumotlar" yorlig'ining mazmuni o'zgaradi. Ya'ni, "Ulanishlarni o'zgartirish" tugmasi faollashadi, uning yordamida foydalanuvchi mavjud ulanishlarni tahrirlashi mumkin.

Muammoning mohiyati

Qoida tariqasida, havolalardan foydalanishda qo'shimcha qiyinchiliklar yuzaga kelmaydi. Hujayralar o'zgargan vaziyat yuzaga kelsa ham, barcha havolalar avtomatik ravishda yangilanadi. Agar siz allaqachon ish kitobining nomini o'zgartirsangiz yoki uni boshqa manzilga ko'chirsangiz, Excel kuchsiz bo'lib qoladi. Shuning uchun u quyidagi xabarni ishlab chiqaradi.

Bu erda foydalanuvchi ushbu vaziyatda qanday harakat qilishning ikkita mumkin bo'lgan variantiga ega. U "Davom etish" tugmasini bosishi mumkin, keyin o'zgarishlar yangilanmaydi yoki "Assotsiatsiyalarni o'zgartirish" tugmasini bosish mumkin, buning yordamida ularni qo'lda yangilash mumkin. Ushbu tugmani bosganimizdan so'ng, qo'shimcha oyna paydo bo'ladi, unda havolalarni o'zgartirish mumkin bo'ladi, bu to'g'ri fayl hozirda qaerda joylashganligi va u nima deb nomlanganligini ko'rsatadi.

Bundan tashqari, siz "Ma'lumotlar" yorlig'ida joylashgan tegishli tugma orqali havolalarni tahrirlashingiz mumkin. Foydalanuvchi shuningdek, manzilning o'zi noto'g'ri bo'lganligi sababli Excel ma'lum bir manzilda joylashgan ma'lumotlarga kira olmaganida paydo bo'ladigan #LINK xatosi tufayli ulanish buzilganligini bilib olishi mumkin.

Excelda qanday qilib uzilish mumkin

Agar bog'langan faylning joylashuvini o'zingiz yangilay olmasangiz, yuqorida tavsiflangan vaziyatni hal qilishning eng oddiy usullaridan biri bu havolaning o'zini o'chirishdir. Hujjatda faqat bitta havola bo'lsa, buni qilish ayniqsa oson. Buning uchun siz quyidagi ketma-ketlikni bajarishingiz kerak:

1. "Ma'lumotlar" menyusini oching.
2. Biz "Ulanishlar" bo'limini topamiz va u erda - "Ulanishlarni o'zgartirish" opsiyasi.
3. Shundan so'ng, "Unlink" tugmasini bosing.

Agar siz ushbu kitobni boshqa shaxsga pochta orqali yubormoqchi bo'lsangiz, buni oldindan qilish tavsiya etiladi. Axir, havolalarni o'chirib tashlaganingizdan so'ng, boshqa hujjatdagi barcha qiymatlar avtomatik ravishda faylga yuklanadi, formulalarda ishlatiladi va hujayra manzili o'rniga tegishli kataklardagi ma'lumotlar oddiygina qiymatlarga aylanadi. .

Barcha kitoblarni qanday ajratish mumkin

Ammo havolalar soni juda katta bo'lsa, ularni qo'lda o'chirish uzoq vaqt talab qilishi mumkin. Ushbu muammoni bir vaqtning o'zida hal qilish uchun siz maxsus makrosdan foydalanishingiz mumkin. U VBA-Excel plaginida joylashgan. Siz uni faollashtirishingiz va xuddi shu nomdagi yorlig'iga o'tishingiz kerak. "Aloqalar" bo'limi bo'ladi, unda biz "Barcha havolalarni buzish" tugmasini bosishimiz kerak.

VBA kodi

Agar ushbu qo'shimchani faollashtirish imkoni bo'lmasa, siz o'zingiz makros yaratishingiz mumkin. Buning uchun Alt + F11 tugmalarini bosib Visual Basic muharririni oching va kod kiritish maydoniga quyidagi qatorlarni yozing.

Sub UnlinkWorkBooks()

    Xira WbLinks

    Dim va As Long

    Case MsgBox-ni tanlang("Boshqa kitoblarga havolalar bu fayldan o'chiriladi va boshqa kitoblarga tegishli formulalar qiymatlar bilan almashtiriladi." & vbCrLf & "Davom etishni xohlaysizmi?", 36, "Ulanishni uzish?" )

    Holat 7' №

        Sub-dan chiqing

    Tanlovni tugatish

    WbLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

    Bo'sh bo'lmasa(WbLinks) keyin

        i = 1 UBound (WbLinks) uchun

            ActiveWorkbook.BreakLink Name:=WbLinks(i), Type:=xlLinkTypeExcelLink

        Keyingi

    yana boshqa

        MsgBox "Ushbu faylda boshqa kitoblarga havolalar mavjud emas.", 64, "Boshqa kitoblarga havolalar"

    End if

End Sub

Faqat tanlangan diapazondagi aloqalarni qanday uzish mumkin

Vaqti-vaqti bilan havolalar soni juda ko'p va foydalanuvchi ulardan birini o'chirib tashlaganidan so'ng, ba'zilari ortiqcha bo'lsa, hamma narsani qaytarib bo'lmaydi, deb qo'rqadi. Ammo bu muammodan qochish oson. Buni amalga oshirish uchun siz havolalarni o'chirish uchun diapazonni tanlashingiz va keyin ularni o'chirishingiz kerak. Buning uchun siz quyidagi harakatlar ketma-ketligini bajarishingiz kerak:

1. O'zgartirilishi kerak bo'lgan ma'lumotlar to'plamini tanlang.
2. VBA-Excel plaginini o'rnating va keyin tegishli yorliqga o'ting.
3. Keyinchalik, biz "Aloqalar" menyusini topamiz va "Tanlangan diapazonlardagi havolalarni buzish" tugmasini bosing.

Shundan so'ng, tanlangan hujayralar to'plamidagi barcha havolalar o'chiriladi.

Agar rishtalar buzilmasa nima qilish kerak

Yuqorida aytilganlarning barchasi yaxshi ko'rinadi, lekin amalda har doim ba'zi nuanslar mavjud. Misol uchun, rishtalar buzilmagan vaziyat bo'lishi mumkin. Bunday holda, havolalarni avtomatik ravishda yangilash mumkin emasligini bildiruvchi dialog oynasi hali ham paydo bo'ladi. Bunday vaziyatda nima qilish kerak?

1. Birinchidan, siz belgilangan diapazonlarda biron bir ma'lumot mavjudligini tekshirishingiz kerak. Buning uchun Ctrl + F3 tugmalar birikmasini bosing yoki "Formulalar" yorlig'ini - "Ism menejeri" ni oching. Agar fayl nomi to'liq bo'lsa, uni tahrirlash yoki butunlay olib tashlash kifoya. Nomlangan diapazonlarni o'chirishdan oldin, noto'g'ri qadamlar qo'yilgan bo'lsa, asl versiyaga qaytishingiz uchun faylni boshqa joyga nusxalashingiz kerak.
2. Agar nomlarni olib tashlash orqali muammoni hal qila olmasangiz, shartli formatlashni tekshirishingiz mumkin. Boshqa jadvaldagi kataklarga shartli formatlash qoidalarida murojaat qilish mumkin. Buni amalga oshirish uchun "Uy" yorlig'ida tegishli elementni toping va keyin "Fayllarni boshqarish" tugmasini bosing.

   Odatda, Excel shartli formatlashda boshqa ish kitoblarining manzilini berish imkoniyatini bermaydi, lekin siz boshqa faylga havola bilan nomlangan diapazonga murojaat qilsangiz, buni qilasiz. Odatda, havola o'chirilgandan keyin ham havola qoladi. Bunday havolani olib tashlashda hech qanday muammo yo'q, chunki havola aslida ishlamayapti. Shuning uchun, agar siz uni olib tashlasangiz, hech qanday yomon narsa bo'lmaydi.

Bundan tashqari, keraksiz havolalar mavjudligini bilish uchun "Ma'lumotlarni tekshirish" funksiyasidan foydalanishingiz mumkin. Ma'lumotlarni tekshirishning "Ro'yxat" turi ishlatilsa, havolalar odatda qoladi. Ammo hujayralar ko'p bo'lsa nima qilish kerak? Haqiqatan ham ularning har birini ketma-ket tekshirish kerakmi? Albatta yo'q. Axir, bu juda uzoq vaqt talab etadi. Shuning uchun uni sezilarli darajada saqlash uchun maxsus koddan foydalanishingiz kerak.

Variant aniq

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

Muallif: The_Prist (Shcherbakov Dmitriy)

' Har qanday murakkablikdagi MS Office dasturlarini professional ishlab chiqish

' MS Excel dasturi bo'yicha treninglar o'tkazish

' https://www.excel-vba.ru

' [elektron pochta himoyalangan]

'WebMoney — R298726502453; Yandex.Money — 41001332272872

' Maqsad:

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

Sub FindErrLink()

    "Biz manba faylga Ma'lumotlarni o'zgartirish havolasini ko'rishimiz kerak

    'va bu erda kalit so'zlarni kichik harflar bilan qo'ying (fayl nomining bir qismi)

    "yulduzcha har qanday sonli belgilarni almashtiradi, shuning uchun siz aniq ism haqida tashvishlanishingiz shart emas

    Const sToFndLink$ = “*sotish 2018*”

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

    'ma'lumotlarni tekshirish bilan barcha hujayralarni aniqlang

    Xato davom ettiriladi

    rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation) ni o‘rnating

    Agar rr hech narsa bo'lmasa

        MsgBox "Faol varaqda ma'lumotlar tekshirilgan hujayralar yo'q", vbInformation, "www.excel-vba.ru"

        Sub-dan chiqing

    End if

    O'tish xatosi haqida 0

    'har bir katakchani havolalar uchun tekshiring

    Har bir rc In rr uchun

        "har holda, biz xatolarni o'tkazib yuboramiz - bu ham sodir bo'lishi mumkin

        Lekin bizning aloqalarimiz ularsiz bo'lishi kerak va ular albatta topiladi

        s = «»

        Xato davom ettiriladi

        s = rc.Validation.Formula1

        O'tish xatosi haqida 0

        'topildi - biz hamma narsani alohida diapazonda yig'amiz

        Agar LCase(lar) sToFndLinkni yoqtirsa

            If rres Hech narsa bo'lmasa

                rres = rc ni o'rnating

            yana boshqa

                rres o'rnating = Birlashma (rc, rres)

            End if

        End if

    Keyingi

    'agar ulanish mavjud bo'lsa, bunday ma'lumotlar tekshiruvi bilan barcha hujayralarni tanlang

    If Not rres hech narsa emas

        rres.Tanlash

' rres.Interior.Color = vbRed 'agar siz rang bilan ajratib ko'rsatishni istasangiz

    End if

End Sub

Ibratli muharrirda standart modul yasash kerak, so'ng u yerga ushbu matnni kiritish kerak. Shundan so'ng, Alt + F8 tugmalar birikmasidan foydalanib, makro oynasiga qo'ng'iroq qiling, so'ngra bizning makrosimizni tanlang va "Ishga tushirish" tugmasini bosing. Ushbu kodni ishlatishda bir nechta narsalarni yodda tutish kerak:

1. Endi ahamiyatsiz bo'lgan havolani qidirishdan oldin, avval u yaratilgan havola qanday ko'rinishini aniqlashingiz kerak. Buni amalga oshirish uchun "Ma'lumotlar" menyusiga o'ting va u erda "Havolalarni o'zgartirish" bandini toping. Shundan so'ng siz fayl nomiga qarashingiz va uni tirnoq ichida ko'rsatishingiz kerak. Masalan, bu kabi: Const sToFndLink$ = “*sotish 2018*”
2. Ismni to'liq emas, balki keraksiz belgilarni yulduzcha bilan almashtirish mumkin. Va tirnoq ichida fayl nomini kichik harflar bilan yozing. Bunday holda, Excel oxirida bunday qatorni o'z ichiga olgan barcha fayllarni topadi.
3. Ushbu kod faqat hozirda faol bo'lgan varaqdagi havolalarni tekshirishi mumkin.
4. Ushbu makros yordamida siz faqat u topgan kataklarni tanlashingiz mumkin. Siz hamma narsani qo'lda o'chirishingiz kerak. Bu ortiqcha, chunki siz hamma narsani yana ikki marta tekshirishingiz mumkin.
5. Shuningdek, hujayralarni maxsus rangda ajratib ko'rsatishingiz mumkin. Buning uchun ushbu qatordan oldin apostrofni olib tashlang. rres.Interior.Color = vbRed

Odatda, yuqoridagi ko'rsatmalarda tavsiflangan amallarni bajarganingizdan so'ng, keraksiz ulanishlar qolmasligi kerak. Ammo agar hujjatda ulardan ba'zilari mavjud bo'lsa va siz ularni biron bir sababga ko'ra o'chira olmasangiz (odatiy misol - varaqdagi ma'lumotlarning xavfsizligi), unda siz boshqa harakatlar ketma-ketligini ishlatishingiz mumkin. Ushbu ko'rsatma faqat 2007 va undan yuqori versiyalar uchun amal qiladi.

1. Biz hujjatning zaxira nusxasini yaratamiz.
2. Arxivator yordamida ushbu hujjatni oching. Siz ZIP formatini qo'llab-quvvatlaydigan har qanday fayldan foydalanishingiz mumkin, lekin WinRar ham ishlaydi, shuningdek Windows-ga o'rnatilgan.
3. Ko'rsatilgan arxivda siz xl jildini topishingiz kerak va keyin externalLinks-ni oching.
4. Ushbu papkada barcha tashqi havolalar mavjud bo'lib, ularning har biri externalLink1.xml formatidagi faylga mos keladi. Ularning barchasi faqat raqamlangan va shuning uchun foydalanuvchi bu qanday aloqa ekanligini tushunish imkoniyatiga ega emas. Qanday ulanish turini tushunish uchun siz _rels papkasini ochishingiz va u yerga qarashingiz kerak.
5. Shundan so'ng, biz externalLinkX.xml.rels faylida o'rganganimizga asoslanib, barcha yoki muayyan havolalarni olib tashlaymiz.
6. Shundan so'ng biz Excel yordamida faylimizni ochamiz. “Kitobdagi kontentning bir qismidagi xato” kabi xato haqida maʼlumot paydo boʻladi. Biz rozilik beramiz. Shundan so'ng, boshqa dialog oynasi paydo bo'ladi. Biz uni yopamiz.

Shundan so'ng, barcha havolalarni olib tashlash kerak.