مدیریت لینک ها در اکسل

مدیریت لینک های داخلی و خارجی

استفاده از لینک و ایجاد ارتباط بین اجزاء مختلف یک فایل یا حتی بین فایل های دیگه یکی از مسائل پرکاربرد و مهم در اکسل هست. پس توانایی مدیریت لینک ها (ایجاد، ویرایش و حذف) خیلی خیلی اهمیت داره. به همین دلیل ردیابی تمامی ارجاعات و لینک های مورد استفاده در یک فایل اکسل موضوعی چالش برانگیز برای کاربران هست. قطعا هیچ راه سریعی برای شناسایی تمام لینک ها در یک فایل اکسل وجود نداره و براساس اینکه لینک در فرمول، شکل، نمودار یا Defined Names تعریف شده باشه، روش های شناسایی متفاوت خواهد بود.در این مقاله قصد داریم تکنیک های جستجو و از بین بردن لینک ها در فرمول ها، شکل ها و نمودار ها رو توضیح بدیم.

پیدا کردن لینک ها در فایل اکسل

در بخش اول روش های شناسایی لینک ها رو در هر کدوم از حالات گفته شده (فرمول، اشکال، نمودار یا Defined Names) بررسی می کنیم.

جستجوی لینک در سلول ها

مرسوم ترین نوع استفاده از لینک ها به کارگیری اون ها در سلول ها هست که راحت ترین حالت برای جستجو و حذف رو دارند. برای پیدا کردن این لینک ها مراحل زیر رو طی می کنیم:

• در فایل مربوطه Ctrl + F رو می زنیم تا پنجره Find and Replace نمایش داده بشه؛
• در تب Find و در قسمت Find what عبارت .xl رو وارد می کنیم. این نوع جستجو باعث میشه تا تمامی فایل های اکسل با فرمت قدیمی (.xls)، فرمت جدید (.xlsx) و فایل های اکسل حاوی ماکرو (.xlsm) که به فایل جاری لینک داده شدن در نتیجه جستجو نمایش داده بشن.
• در قسمت Within گزینه Workbook رو انتخاب می کنیم تا جستجو در کل شیت ها انجام بشه.
• در قسمت Look in گزینه Formulas رو انتخاب می کنیم.
• روی دکمه Find All کلیک می کنیم. نتایج جستجو مطابق شکل زیر نشان داده شده.

شکل ۱- جستجوی سلول های حاوی لینک با استفاده از ابزار Find

مطابق شکل ۱ لیستی از تمامی سلول هایی که در آن ها از لینک استفاده شده (در ستون Cell) و نام فایلی که سلول به آن لینک شده (در ستون (Formula مشخص هست.در صورتی که بخواهیم یک از سلول هایی که لینک دارند رو انتخاب کنیم، از ستون Cell روی آدرس سلول مورد نظر کلیک می کنیم. برای اینکه تمامی سلول هایی که لینک دارند رو انتخاب کنیم، در پنجره ای که نتایج جستجو نمایش داده شده کلیک می کنیم و Ctrl + A رو می زنیم.  

جستجوی لینک در Define Name

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

• در تب Formula و در زیر مجموعه Define Name روی گزینه Name Manager کلیک می کنیم یا کلید میانبر Ctrl + F3 رو می زنیم؛
• در پنجره Name Manager مطابق شکل ۲ لیست محدوده هایی که نام گذاری شدند نمایش داده شده که در ستون Refers To محدوده هایی که به یک فایل خارجی لینک شدند در داخل دو تا براکت نمایش داده شدن مثل [xlsx]

شکل ۲- شناسایی و مدیریت لینک های خارجی در Define Name

جستجوی لینک در Objects

برای شناسایی تمامی Objectهای استفاده شده در فایل اکسل از ابزار Go To Special  استفاده می کنیم و مسیر زیر رو طی می کنیم:

• در تب Home و در زیرمجموعه Editing روی گزینه Find & Select کلیک می کنیم و گزینه Go to Special رو انتخاب می کنیم و یا کلید میانبر F5 رو می زنیم و در پنجره Go To گزینه Special رو انتخاب می کنیم.
• در پنجره Go to Special مطابق شکل ۳ گزینه Objects رو انتخاب می کنیم و تمامی Objects به حالت انتخاب درمیان.

شکل ۳- پنجره Go To Special

• کلید تب رو می زنیم تا Objects موجود در فایل یکی یکی به حالت انتخاب در بیان، و با انتخاب هر Object چک می کنیم که آیا به فایل دیگری لینک شده یا خیر. در صورتی که Object به یک سلول از یک فایل خارجی لینک شده باشه وقتی Object رو انتخاب می کنیم در نوار فرمول (Formula Bar) می تونیم اسم فایل و آدرس سلول رو مطابق شکل ۴ مشاهده کنیم. راجع به اتصال Shape به سلول این مقاله رو مطالعه کنید

شکل ۴- لینک Object به یک سلول از فایل خارجی

در صورتی که Object به یک فایل لینک شده باشه (Hyperlink)، وقتی موس رو روی Object نگه می داریم مطابق شکل ۵ آدرس فایل مربوطه نمایش داده می شه.

شکل ۵- لینک Object به یک فایل خارجی

 

جستجوی لینک در نمودارها

برای شناسایی و مدیریت لینک های استفاده شده در یک نمودار مراحل زیر روی طی می کنیم:

• در نمودار مورد نظر روی عنوان نمودار (chart title) یا سری داده ای (data series) کلیک می کنیم؛
• در صورتی که عنوان نمودار یا سری داده ای به فایلی لینک شده باشند مطابق شکل ۶ در نوار فرمول نام فایل مربوطه (“فروش مهر”) نشان داده میشه.

شکل ۶- لینک عنوان نمودار و سری داده ای به فایل خارجی

اگر نمودار از چند سری داده ای تشکیل شده باشه (مثل شکل ۶ که نمودار دو تا سری داده ای داره) برای شناسایی لینک های هر سری داده ای به شکل زیر عمل می کنیم:

• نمودار رو انتخاب می کنیم؛
• از تب Format و از زیر مجموعه Current Selection، بر روی فلش Chart Elements کلیک می کنیم و از لیست نمایش داده شده، سری مورد نظرمون رو انتخاب می کنیم و با مراجعه به نوار فرمول می تونیم لینک تعریف شده رو مشاهده کنیم. (مطابق شکل ۷)

شکل ۷- نمایش لینک ها در نمودار با بیش از یک سری داده ای

جستجوی و مدیریت لینک در Pivot Table

بیشتر مواقع Pivot Table و منبع داده های ورودی اون در یک فایل قرار دارند اما در بعضی مواقع از داده های یک منبع خارجی برای ساخت Pivot Table استفاده می کنیم. برای اینکه متوجه بشیم منبع داده های Pivot Table کجا قرار گرفته به شکل زیر عمل می کنیم:

• یکی از سلول های Pivot Table رو انتخاب می کنیم؛
• در تب Analyze و در زیر مجموعه Data روی دکمه Change Data Source کلیک می کنیم؛

شکل ۸- شناسایی منبع داده ای Pivot Table

• در پنجره Change Pivot Table Data Source و در قسمت Table/Range آدرس داده های مورد استفاده در Pivot Table مشخص شده. همان طور که در شکل ۹ مشخص شده داده ها از فایلی با نام “فروش مهر” گرفته شده.

شکل ۹- پنجره Change Pivot Table Data Source

بروزرسانی و مدیریت لینک های ایجاد شده به فایل های خارجی

وقتی فایل اکسلی رو باز می کنیم که به فایل های دیگری لینک شده، اکسل پیغامی رو مطابق شکل ۱۰ نمایش میده مبنی بر اینکه فایل مربوطه با فایل های دیگری لینک هست. در صورتی که از لینک های فایل اطمینان داشته باشیم، گزینه Update رو انتخاب می کنیم تا اطلاعات فایل بروز رسانی بشه.

شکل ۱۰- پیغام بروز رسانی لینک های فایل

هر بار که فایل حاوی لینک (به فایل های دیگر) رو باز می کنیم اکسل این پیغام رو نمایش میده. برای تنظیم نمایش/ عدم نمایش این پیغام و نحوه بروز رسانی لینک ها به شکل زیر عمل می کنیم:

• در تب Data و در زیرمجموعه Connections بر روی گزینه Edit Links کلیک می کنیم؛
• مطابق شکل ۱۱ در گوشه پایین سمت چپ پنجره Edit Links، روی گزینه Startup Prompt… کلیک می کنیم؛

شکل ۱۱- پنجره Edit Links برای مدیریت لینک های خارجی

• در پنجره Startup Prompt مطابق شکل ۱۲ یکی از گزینه ها رو انتخاب می کنیم. عملکرد هر کدوم از گزینه ها به شرح زیر است:

گزینه اول که به صورت پیش فرض انخاب شده، این امکان رو به کاربر میده تا خودش نمایش / عدم نمایش پیغام رو مشخص بکنه؛با انتخاب گزینه دوم پیغام نمایش داده نمیشه و لینک ها هم به طور خودکار بروز رسانی نمیشن؛با انتخاب گزینه سوم پیغام نمایش داده نمیشه و لینک ها بروزرسانی می شن.

شکل ۱۲- پنجره Startup Prompt

برای حذف پیغام و خودکار کردن بروزرسانی لینک ها یک روش دیگر هم وجود داره که تنظیمات اون به ترتیب زیر هست:

• در فایل اکسل مربوطه بر روی تب File روی گزینه Options کلیک می کنیم؛
• در پنجره Excel Options روی Trust Center کلیک می کنیم و بعد مطابق شکل ۱۳ روی Trust Center Settings کلیک می کنیم؛

شکل ۱۳- Trust Center در پنجره Excel Options

• در پنجره Trust Center مطابق شکل ۱۴ روی گزینه External Content کلیک می کنیم و بعد گزینه Enable automatic update for all Workbook Links که در زیر عنوان Security Settings for Workbook Links قرار دارد را انتخاب می کنیم؛

شکل ۱۴- تنظیمات بروزرسانی و مدیریت لینک ها در پنجره Trust Center

با فعال کردن این گزینه، اکسل به طور خودکار تمامی لینک های موجود در فایل را بروز رسانی می کند بدون اینکه پیغام یا هشداری برای کاربر نمایش داده بشه.توجه داشته باشید که بروزرسانی خودکار لینک های مربوط به فایل های شناخته نشده می تونه خطرناک باشه و به همین دلیل انتخاب این گزینه توصیه نمیشه. بهتره این گزینه رو زمانی فعال کنیم که صد در صد از امنیت فایل های خارجی مطمئن هستیم یا اینکه به طور موقت این گزینه رو فعال کنیم و بعد دوباره حالت پیش فرض رو (Prompt user on automatic update for Workbook Links) انتخاب کنیم.در صورتی که فایل حاوی لینک های حذف شده یا نامعتبر باشه پیغام زیر (شکل ۱۵) نمایش داده میشه.

شکل ۱۵- پیغام نمایش داده در صورت نامعتبر بودن یا حذف شدن لینک ها

قطع کردن لینک ها با فایل های خارجی

وقتی در فایل اکسل ارتباط لینک خارجی رو با فایل قطع می کنیم در واقع مقدار فعلی منبع خارجی رو در فایل جاری جایگزین کردیم. به طور مثال اگر در ارجاع زیر لینک Source data.xlsx رو قطع کنیم، مقدار سلول A1 در شیت Jan در فایل Source data جایگزین عبارت زیر خواهد شد.

='[Source data.xlsx]Jan’!$A$1

 برای قطع کردن لینک به صورت زیر عمل می کنیم:

• در تب Data در زیر مجموعه Connections رو گزینه Edit Links کلیک می کنیم؛(در صورتی که در فایل مربوطه لینکی وجود نداشته باشه این گزینه به حالت غیر فعال در میاد)
• در پنجره Edit Links مطابق شکل ۱۶ لینک هایی که می خواهیم حذف کنیم رو انتخاب می کنیم. برای انتخاب بیش از یک لینک، دکمه Ctrl رو نگه می داریم و لینک های مورد نظر رو انتخاب می کنیم و برای انتخاب همه لینک ها Ctrl + A رو می زنیم.
• روی دکمه Break Link کلیک می کنیم.

شکل ۱۶- نحوه حذف و مدیریت لینک ها در پنجره Edit Links

 

دسترسی به لیستی از لینک های خارجی

یکی از مرسوم ترین راه ها برای دسترسی به لیست لینک های موجود در فایل، استفاده از پنجرهEdit Links  هست. مطابق شکل ۱۶ این پنجره اطلاعات زیر رو در مورد لینک ها مشخص می کنه:Source: در این بخش اسم فایل لینک شده نمایش داده میشهType: این قسمت مشخص می کنه که فایل Worksheet یا WorkbookUpdate: این قسمت روش بروزرسانی لینک رو مشخص می کنه که می تونه دستی (Manually) یا خودکار (Automatically) باشهStatus: این بخش وضعیت لینک رو مشخص می کنه که می تونه یکی از موارد ,OK, Warning, Unknown Source is Open باشه. برای دریافت آخرین وضعیت مربوط به لینک روی گزینه Check Status کلیک می کنیم.اما همان طور که قبلا هم گفتیم در این پنجره تمامی لینکها نمایش داده نمی شن.روش بعدی برای دسترسی به لیستی از لینک های موجود در فایل استفاده از کد های VBA هست. برای این منظور از کد VBA که در شکل زیر نشان داده شده استفاده می کنیم.

Sub Get_Links()
    Dim links As Variant
    links = ActiveWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(links) Then
        Sheets.Add
        For i = 1 To UBound(links)
            Cells(i, 1).Value = links(i)
        Next i
    Else
        MsgBox "No external links are found.", vbInformation, "Find Links"
    End If
End Sub

شکل ۱۷- کد VBA برای نمایش لیستی از لینک های خارجی

برای استفاده از این کد مطابق مراحل زیر عمل می کنیم:

• کلید های Alt + F11 رو می زنیم تا Visual Basic Editor نمایش داده بشه؛
• در سمت چپ صفحه مطابق شکل ۱۸ روی ThisWorkbook کلیک راست می کنیم و از زیر مجموعه Insert گزینه Module رو انتخاب می کنیم؛

شکل ۱۸- پنجره Visual Basic Editor

• کد شکل ۱۷ رو در صفحه ای که باز شده کپی می کنیم؛
• برای اجرای ماکرو، در صفحه اکسل کلیدهای Alt + F8 رو می زنیم، پنجره Macro مطابق شکل ۱۹ نمایش داده می شه. در پنجره Macro، اسم ماکرو مورد نظر رو انتخاب می کنیم (Get_Links) و روی دکمه Run کلیک می کنیم تا ماکرو اجرا بشه. مطابق شکل ۱۹ لیست لینک هایی که در فایل استفاده شده نمایش داده میشه

شکل ۱۹- پنجره Macro

شکل ۲۰- نمایش لیست لینک های موجود در فایل با استفاده از اجرای ماکرو Get_Links

در صورتی که بخواهیم در نتایج شکل ۲۰ آدرس سلول هایی که حاوی لینک هستند هم نمایش داده بشه از کد VBA زیر استفاده می کنیم (برای دسترسی کامل به این کد از فایل اکسلی که در انتهای مقاله درج شده استفاده کنید) و دقیقا مراحل مثال قبل رو برای اجرای ماکرو طی می کنیم.

Sub Cells_With_Links()
 linksDataArray = ActiveWorkbook.LinkSources(xlExcelLinks)
 Dim reportHeaders() As String
 Dim rangeCur As Range
 Dim sheetCur As Worksheet
 Dim rowNo As Integer
 Dim linkFilePath, linkFilePath2, linkFileName As String
 Dim linksStatusDescr() As String
 Dim sheetReportName As String
 
 sheetReportName = "All Links report"
 linksStatusDescr = Split("No errors/File missing/Sheet missing/Status may be out of date/Not yet calculated/Unable to determine status/Not started/Invalid name/Not open/Source document is open/Copied values", "/")
 reportHeaders = Split("Worksheet,Cell,Formula,Workbook,Link Status", ",")
 
 rowNo = 1 'Header row
 
 If Not IsEmpty(linksDataArray) Then
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
 
  If Evaluate("ISREF('" & sheetReportName & "'!A1)") Then
   ActiveWorkbook.Worksheets(sheetReportName).Cells.Clear
  Else
   Sheets.Add.Name = sheetReportName
  End If
  Set sheetReport = ActiveWorkbook.Worksheets(sheetReportName)
 
  For indI = 0 To UBound(reportHeaders)
   sheetReport.Cells(rowNo, indI + 1) = reportHeaders(indI)
  Next
 
  For Each sheetCur In ActiveWorkbook.Worksheets
   If sheetCur.Name <> sheetReport.Name Then
    For Each rangeCur In sheetCur.UsedRange
     If rangeCur.HasFormula Then
      For indI = LBound(linksDataArray) To UBound(linksDataArray)
       linkFilePath = linksDataArray(indI)   'LinkSrouces returns the full file path with the file name
       linkFileName = Right(linkFilePath, Len(linkFilePath) - InStrRev(linkFilePath, "\"))   'extract only the file name
       linkFilePath2 = Left(linksDataArray(indI), InStrRev(linksDataArray(indI), "\")) & "[" & linkFileName & "]"  'the file path with the workbook name in square brackets
 
       If InStr(rangeCur.Formula, linkFilePath) Or InStr(rangeCur.Formula, linkFilePath2) Then
        rowNo = rowNo + 1
        With sheetReport
         .Cells(rowNo, 1) = sheetCur.Name
         .Cells(rowNo, 2) = Replace(rangeCur.Address, "$", "")
         .Hyperlinks.Add Anchor:=.Cells(rowNo, 2), Address:="", SubAddress:="'" & sheetCur.Name & "'!" & rangeCur.Address
         .Cells(rowNo, 3) = "'" & rangeCur.Formula
         .Cells(rowNo, 4) = linkFilePath
         .Cells(rowNo, 5) = linksStatusDescr(ActiveWorkbook.LinkInfo(CStr(linkFilePath), xlLinkInfoStatus))
        End With
        Exit For
       End If
      Next indI
 
      For Each namedrangeCur In Names
         If InStr(rangeCur.Formula, namedrangeCur.Name) Then
        rowNo = rowNo + 1
        linkFilePath = ""
 
        If 0 < InStr(namedrangeCur.RefersTo, "[") Then
         linkFilePath = Replace(Split(Right(namedrangeCur.RefersTo, Len(namedrangeCur.RefersTo) - 2), "]")(0), "[", "")
        End If
 
          With sheetReport
           .Cells(rowNo, 1) = sheetCur.Name
           .Cells(rowNo, 2) = Replace(rangeCur.Address, "$", "")
           .Hyperlinks.Add Anchor:=.Cells(rowNo, 2), Address:="", SubAddress:="'" & sheetCur.Name & "'!" & rangeCur.Address
           .Cells(rowNo, 3) = "'" & rangeCur.Formula
           If 0 < Len(linkFilePath) Then
          .Cells(rowNo, 4) = linkFilePath
           .Cells(rowNo, 5) = "'" & ActiveWorkbook.LinkInfo(CStr(linkFilePath), xlLinkInfoStatus)
           Else
          .Cells(rowNo, 4) = "Unknown"
          .Cells(rowNo, 5) = "Unknown"
           End If
          End With
          Exit For
       End If
      Next namedrangeCur
     End If
    Next rangeCur
   End If
  Next
  Columns("A:E").EntireColumn.AutoFit
 
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
 Else
  MsgBox "No external links"
 End If
End Sub

نتیجه اجرای این کد در شکل زیر نمایش داده شده. همان طور که مشخص هست در خروجی این ماکرو، آدرس سلول ها و شیت هایی که حاوی لینک هستند هم اضافه شده.

شکل ۱۲- نمایش لیست لینک های موجود در فایل به همراه آدرس سلول ها با اجرای ماکرو Cells_With_Links

در این مقاله روش جستجوی لینک ها در سلول، Pivot Table، نمودار و Object رو یاد گرفتیم همچنین نحوه تنظیم پیغام مربوط به بروزرسانی لینک ها و روش حذف لینک ها رو توضیح دادیم و در انتها هم دو نمونه کد VBA برای نمایش لیست لینک ها در فایل اکسل رو ارائه کردیم.

دانلود فایل های آموزش مدیریت لینک

برای دانلود فایل های استفاده شده در این آموزش روی دکمه زیر کلیک کنید: