نحوه شکستن لینک ها در اکسل

ارتباط یک ویژگی بسیار مفید در اکسل است. از این گذشته، اغلب کاربران مجبورند از اطلاعات فایل های دیگر استفاده کنند. اما در برخی از شرایط، آنها می توانند آسیب بیشتری نسبت به سود داشته باشند. به هر حال، برای مثال، اگر این فایل ها را از طریق پست ارسال کنید، لینک ها کار نمی کنند. امروز با جزئیات بیشتری در مورد آنچه که برای جلوگیری از چنین مشکلی باید انجام دهیم صحبت خواهیم کرد.

روابط در اکسل چیست؟

روابط در اکسل اغلب همراه با توابعی مانند VPRبرای دریافت اطلاعات از یک کتاب کار دیگر این می تواند به شکل یک پیوند خاص باشد که حاوی آدرس نه تنها سلول، بلکه همچنین کتابی است که داده ها در آن قرار دارند. در نتیجه، چنین پیوندی چیزی شبیه به این است: =VLOOKUP(A2;'[Sales 2018.xlsx]گزارش'!$A:$F;4;0). یا برای نمایش ساده‌تر، آدرس را به شکل زیر نشان دهید: ='[Sales 2018.xlsx]گزارش'!$A1. بیایید هر یک از عناصر پیوند از این نوع را تجزیه و تحلیل کنیم:

1. [فروش 2018.xlsx]. این قطعه حاوی پیوندی به فایلی است که می خواهید از آن اطلاعات دریافت کنید. به آن منبع نیز می گویند.
2. عکس. ما از نام زیر استفاده کردیم، اما این نامی نیست که باید باشد. این بلوک حاوی نام برگه ای است که باید در آن اطلاعاتی را پیدا کنید.
3. $A:$F و $A1 - آدرس یک سلول یا محدوده حاوی داده هایی که در این سند موجود است.

در واقع، فرآیند ایجاد پیوند به یک سند خارجی، پیوند نامیده می شود. پس از اینکه آدرس سلول موجود در فایل دیگری را ثبت کردیم، محتویات برگه "داده" تغییر می کند. یعنی دکمه "تغییر اتصالات" فعال می شود که با کمک آن کاربر می تواند اتصالات موجود را ویرایش کند.

اصل مسئله

به عنوان یک قاعده، هیچ مشکل اضافی برای استفاده از پیوندها ایجاد نمی شود. حتی اگر موقعیتی پیش بیاید که در آن سلول ها تغییر کنند، همه پیوندها به طور خودکار به روز می شوند. اما اگر قبلاً خود کتاب کار را تغییر نام دهید یا آن را به آدرس دیگری منتقل کنید، اکسل ناتوان می شود. بنابراین پیام زیر را تولید می کند.

در اینجا، کاربر دو گزینه ممکن برای نحوه عمل در این شرایط دارد. او می‌تواند روی «ادامه» کلیک کند و سپس تغییرات به‌روزرسانی نمی‌شوند، یا می‌تواند روی دکمه «تغییر انجمن‌ها» کلیک کند، که با آن می‌تواند آنها را به‌صورت دستی به‌روزرسانی کند. پس از کلیک بر روی این دکمه، یک پنجره اضافی ظاهر می شود که در آن امکان تغییر پیوندها وجود دارد که نشان می دهد فایل صحیح در حال حاضر کجا قرار دارد و نام آن چیست.

علاوه بر این، می توانید پیوندها را از طریق دکمه مربوطه که در برگه "داده ها" قرار دارد ویرایش کنید. کاربر همچنین می تواند متوجه شود که اتصال با خطای #LINK قطع شده است، که زمانی ظاهر می شود که اکسل به دلیل نامعتبر بودن خود آدرس به اطلاعاتی که در یک آدرس خاص قرار دارد دسترسی نداشته باشد.

نحوه لغو لینک در اکسل

یکی از ساده‌ترین روش‌ها برای حل وضعیتی که در بالا توضیح داده شد، در صورتی که خودتان نمی‌توانید مکان فایل پیوند شده را به‌روزرسانی کنید، حذف خود پیوند است. اگر سند فقط یک پیوند داشته باشد انجام این کار بسیار آسان است. برای این کار باید مراحل زیر را انجام دهید:

1. منوی "داده" را باز کنید.
2. ما بخش "اتصالات" و در آنجا - گزینه "تغییر اتصالات" را پیدا می کنیم.
3. پس از آن، بر روی "Unlink" کلیک کنید.

اگر قصد دارید این کتاب را برای شخص دیگری پست کنید، به شدت توصیه می شود که از قبل این کار را انجام دهید. از این گذشته ، پس از حذف پیوندها ، تمام مقادیر موجود در یک سند دیگر به طور خودکار در پرونده بارگیری می شوند و در فرمول ها استفاده می شوند و به جای آدرس سلول ، اطلاعات موجود در سلول های مربوطه به سادگی به مقادیر تبدیل می شوند. .

نحوه لغو پیوند همه کتاب ها

اما اگر تعداد لینک ها خیلی زیاد شود، حذف دستی آنها می تواند زمان زیادی را ببرد. برای حل این مشکل یکباره می توانید از یک ماکرو مخصوص استفاده کنید. در افزونه VBA-Excel قرار دارد. باید آن را فعال کنید و به تبی با همین نام بروید. یک بخش "پیوندها" وجود خواهد داشت که در آن باید روی دکمه "شکستن همه پیوندها" کلیک کنیم.

کد VBA

اگر امکان فعال سازی این افزونه وجود ندارد، می توانید خودتان یک ماکرو ایجاد کنید. برای این کار ویرایشگر ویژوال بیسیک را با فشردن کلیدهای Alt + F11 باز کنید و خطوط زیر را در قسمت ورودی کد بنویسید.

زیر UnlinkWorkBooks()

    کم نور WbLinks

    کم نور و تا زمانی

    Case MsgBox را انتخاب کنید ("همه ارجاعات به کتاب های دیگر از این فایل حذف می شوند و فرمول های ارجاع به کتاب های دیگر با مقادیر جایگزین می شوند." & vbCrLf & "آیا مطمئن هستید که می خواهید ادامه دهید؟"، 36، "لغو پیوند؟" )

    مورد 7' شماره

        خروج از زیر

    پایان انتخاب

    WbLinks = ActiveWorkbook.LinkSources(نوع:=xlLinkTypeExcelLinks)

    اگر خالی نیست (WbLinks) پس

        برای i = 1 به UBound (WbLinks)

            ActiveWorkbook.BreakLink نام:=WbLinks(i)، نوع:=xlLinkTypeExcelLinks

        بعدی

    دیگر

        MsgBox "هیچ پیوندی به کتاب های دیگر در این فایل وجود ندارد."، 64، "پیوند به کتاب های دیگر"

    اگر پایان

پایان دادن به زیر

نحوه شکستن پیوندها فقط در محدوده انتخاب شده

هر از چند گاهی تعداد پیوندها بسیار زیاد است و کاربر می ترسد که پس از حذف یکی از آنها، در صورتی که برخی از آنها زائد بود، امکان بازگشت همه چیز وجود نداشته باشد. اما این مشکلی است که اجتناب از آن آسان است. برای انجام این کار، باید محدوده ای را انتخاب کنید که در آن لینک ها حذف شوند و سپس آنها را حذف کنید. برای انجام این کار، باید دنباله اقدامات زیر را انجام دهید:

1. مجموعه داده ای را که باید اصلاح شود انتخاب کنید.
2. افزونه VBA-Excel را نصب کنید و سپس به تب مربوطه بروید.
3. بعد، منوی "پیوندها" را پیدا می کنیم و روی دکمه "شکستن پیوندها در محدوده های انتخاب شده" کلیک می کنیم.

پس از آن، تمام پیوندهای موجود در مجموعه سلول های انتخاب شده حذف خواهند شد.

اگر پیوندها شکسته نشد چه باید کرد

همه موارد فوق خوب به نظر می رسد، اما در عمل همیشه برخی از تفاوت های ظریف وجود دارد. برای مثال، ممکن است شرایطی پیش بیاید که پیوندها شکسته نشوند. در این حالت همچنان یک کادر محاوره ای ظاهر می شود که نشان می دهد امکان به روز رسانی خودکار لینک ها وجود ندارد. در این شرایط چه باید کرد؟

1. ابتدا باید بررسی کنید که آیا اطلاعاتی در محدوده های نامگذاری شده وجود دارد یا خیر. برای انجام این کار، کلیدهای ترکیبی Ctrl + F3 را فشار دهید یا برگه "Formulas" - "Name Manager" را باز کنید. اگر نام فایل پر است، فقط باید آن را ویرایش کنید یا به طور کلی حذف کنید. قبل از حذف محدوده های نامگذاری شده، باید فایل را در مکان دیگری کپی کنید تا در صورت انجام مراحل اشتباه، بتوانید به نسخه اصلی بازگردید.
2. اگر نمی توانید با حذف نام ها مشکل را حل کنید، می توانید قالب بندی شرطی را بررسی کنید. سلول‌های جدول دیگر را می‌توان در قوانین قالب‌بندی شرطی ارجاع داد. برای انجام این کار، مورد مربوطه را در تب "Home" پیدا کنید و سپس روی دکمه "File Management" کلیک کنید.

   به طور معمول، اکسل این امکان را به شما نمی دهد که آدرس سایر کتاب های کار را با قالب بندی شرطی ارائه دهید، اما اگر به یک محدوده نام گذاری شده با ارجاع به فایل دیگری مراجعه کنید، این امکان را به شما نمی دهد. معمولا، حتی پس از حذف لینک، لینک باقی می ماند. هیچ مشکلی برای حذف چنین پیوندی وجود ندارد، زیرا پیوند در واقع کار نمی کند. بنابراین، اگر آن را حذف کنید، اتفاق بدی نمی افتد.

همچنین می‌توانید از عملکرد «بررسی داده‌ها» برای اطلاع از وجود پیوندهای غیر ضروری استفاده کنید. در صورت استفاده از نوع اعتبارسنجی داده ها، پیوندها معمولاً باقی می مانند. اما در صورت وجود تعداد زیادی سلول چه باید کرد؟ آیا واقعاً لازم است هر یک از آنها را به ترتیب بررسی کنیم؟ البته که نه. پس از همه، زمان بسیار طولانی خواهد بود. بنابراین، برای ذخیره قابل توجه آن باید از کد خاصی استفاده کنید.

گزینه صریح

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

نویسنده: The_Prist (شچرباکوف دیمیتری)

توسعه حرفه ای برنامه های کاربردی برای MS Office با هر پیچیدگی

' برگزاری دوره های آموزشی MS Excel

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

[ایمیل محافظت شده]

'WebMoney—R298726502453; Yandex.Money — 41001332272872

هدف:

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

زیر FindErrLink()

    ما باید در پیوند Data -Change به فایل منبع نگاه کنیم

    و کلمات کلیدی را در اینجا با حروف کوچک قرار دهید (بخشی از نام فایل)

    ستاره فقط جایگزین هر تعداد کاراکتر می شود، بنابراین لازم نیست نگران نام دقیق آن باشید

    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.Validation.Formula1

        در خطای GoTo 0

        پیدا شد - ما همه چیز را در یک محدوده جداگانه جمع آوری می کنیم

        اگر LCase(های) مانند sToFndLink Then

            اگر rres هیچ چیز نیست پس

                rres = rc را تنظیم کنید

            دیگر

                تنظیم rres = اتحادیه (rc، rres)

            اگر پایان

        اگر پایان

    بعدی

    اگر اتصالی وجود دارد، تمام سلول‌های دارای چنین بررسی داده‌ای را انتخاب کنید

    اگر نه rres هیچ چیز نیست پس

        rres.انتخاب کنید

' rres.Interior.Color = vbRed 'اگر می خواهید با رنگ هایلایت کنید

    اگر پایان

پایان دادن به زیر

لازم است یک ماژول استاندارد در ویرایشگر ماکرو ایجاد کنید و سپس این متن را در آنجا وارد کنید. پس از آن، پنجره ماکرو را با استفاده از کلید ترکیبی Alt + F8 فراخوانی کنید و سپس ماکرو خود را انتخاب کنید و روی دکمه "Run" کلیک کنید. هنگام استفاده از این کد باید به چند نکته توجه داشت:

1. قبل از اینکه پیوندی را جستجو کنید که دیگر مرتبط نیست، ابتدا باید تعیین کنید که پیوندی که از طریق آن ایجاد شده است چگونه است. برای انجام این کار، به منوی "داده" بروید و مورد "تغییر پیوندها" را در آنجا پیدا کنید. پس از آن، شما باید به نام فایل نگاه کنید، و آن را در نقل قول مشخص کنید. به عنوان مثال، مانند این: Const sToFndLink$ = "*فروش 2018*"
2. این امکان وجود دارد که نام را نه به طور کامل بنویسید، بلکه به سادگی کاراکترهای غیر ضروری را با یک ستاره جایگزین کنید. و در گیومه نام فایل را با حروف کوچک بنویسید. در این صورت، اکسل تمام فایل هایی که حاوی چنین رشته ای هستند را در انتها پیدا می کند.
3. این کد فقط می تواند پیوندهای موجود در برگه ای را که در حال حاضر فعال است بررسی کند.
4. با این ماکرو فقط می توانید سلول هایی را که پیدا کرده است انتخاب کنید. شما باید همه چیز را به صورت دستی حذف کنید. این یک امتیاز مثبت است، زیرا می توانید دوباره همه چیز را بررسی کنید.
5. همچنین می توانید سلول های برجسته شده را با رنگ خاصی بسازید. برای انجام این کار، آپستروف قبل از این خط را حذف کنید. rres.Interior.Color = vbRed

معمولاً پس از تکمیل مراحل توضیح داده شده در دستورالعمل های بالا، دیگر نباید اتصالات غیر ضروری وجود داشته باشد. اما اگر تعدادی از آنها در سند وجود دارد و به دلایلی قادر به حذف آنها نیستید (نمونه معمولی امنیت داده ها در یک برگه است)، می توانید از دنباله ای متفاوت از اقدامات استفاده کنید. این دستورالعمل فقط برای نسخه های 2007 و بالاتر معتبر است.

1. ما یک نسخه پشتیبان از سند ایجاد می کنیم.
2. این سند را با استفاده از بایگانی باز کنید. می‌توانید از هر کدام که از فرمت ZIP پشتیبانی می‌کند استفاده کنید، اما WinRar نیز کار می‌کند، و همچنین فرمت داخلی ویندوز.
3. در آرشیو ظاهر شده، باید پوشه xl را پیدا کنید و سپس externalLinks را باز کنید.
4. این پوشه حاوی تمام پیوندهای خارجی است که هر کدام مربوط به فایلی به شکل externalLink1.xml است. همه آنها فقط شماره گذاری شده اند و بنابراین کاربر این فرصت را ندارد که بفهمد این چه نوع ارتباطی است. برای درک نوع اتصال، باید پوشه _rels را باز کنید و در آنجا نگاه کنید.
5. پس از آن، بر اساس آنچه در فایل externalLinkX.xml.rels یاد می گیریم، همه یا پیوندهای خاص را حذف می کنیم.
6. پس از آن، فایل خود را با استفاده از اکسل باز می کنیم. اطلاعاتی درباره خطایی مانند «خطا در بخشی از محتوای کتاب» وجود خواهد داشت. رضایت می دهیم. پس از آن، گفتگوی دیگری ظاهر می شود. آن را می بندیم.

پس از آن، تمام لینک ها باید حذف شوند.