اکسل پدیا، دانشنامه پارسی اکسل

021-47625755
[email protected]

ورود یا ثبت نام

  • دانشنامه اکسل
    • اکسل چیست؟
    • اکسل مقدماتی
    • توابع اکسل
    • نمودار ها
    • وی بی – ماکرو
  • آکادمی اکسل
    • ویدئوهای آموزشی
      • رسم نمودارهای حرفه ای
      • اکسل و شروع حرفه ای
      • اکسل نینجا (اکسل پیشرفته)
      • صفر تا صد Pivot Table
      • برنامه نویسی وی بی (VBA)
    • کتاب ها
      • کتاب های فارسی
      • کتاب های انگلیسی
  • داشبورد های مدیریتی
    • آموزش ساخت داشبورد
    • دانلود داشبورد نمونه
  • افزونه های کاربردی
  • دوره های حضوری
    • اکسل نینجا
    • برنامه نویسی وی بی (VBA)
  • درباره ما
    • Cart

      0

ابزارها

  • خانه
  • بلاگ
  • ابزارها
  • ۳ روش برای شناسایی و رفع مشکل لینک های معیوب در اکسل

۳ روش برای شناسایی و رفع مشکل لینک های معیوب در اکسل

  • ارسال شده توسط حسنا خاکزاد
  • دسته بندی ابزارها
  • تاریخ ۱ اسفند ۱۳۹۹
  • نظرات ۰ نظر
3 روش برای شناسایی و اصلاح هایپرلینک معیوب در اکسل
نظر شما در مورد این آموزش

مشکلات هایپرلینک معیوب در اکسل

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

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

برای این منظور مراحل زیر رو طی می کنیم:

  1. از تب Data و از زیر مجموعه Connections روی گزینه Edit Links کلیک می کنیم؛ (در صورتی که این گزینه غیر فعال (کم رنگ) باشه یعنی هیچ لینک خارجی در فایل وجود نداره)

انتخاب Edit Links از تب Data

شکل 1- انتخاب Edit Links از تب Data

  1. در پنجره Edit Links لیستی از لینک هایی رو مشاهده می کنیم که در فایل جاری وجود دارن. وقتی که این پنجره رو برای بار اول باز می کنیم در ستون Status وضعیت همه لینک ها Unknown هست. برای اینکه از وضعیت هر لینک مطلع بشیم بعد از انتخاب لینک مربوطه از لیست، روی دکمه Check Status که در سمت راست قرار گرفته کلیک می کنیم؛

پنجره Edit Links

شکل 2- پنجره Edit Links

  1. بعد از زدن دکمه Check Status در ستون Status وضعیت هر لینک نمایش داده میشه که مطابق شکل 3 لینک هایی که خطا دارن با عبارت Error در ستون Status مشخص شدن؛

نحوه نمایش لینک های معیوب در پنجره پنجره Edit Links

شکل 3- نحوه نمایش لینک های معیوب در پنجره پنجره Edit Links

  1. تا اینجای کار لینک های معیوب رو شناسایی کردیم. برای رفع مشکل لینک هایی که فایل مرجع اون ها پیدا نمیشن ابتدا لینک مربوطه رو انتخاب می کنیم و روی گزینه Change Source در سمت راست پنجره کلیک می کنیم؛

انتخاب گزینه Change Source برای رفع مشکل لینک های معیوب

شکل 4- انتخاب گزینه Change Source برای رفع مشکل لینک های معیوب

  1. در پنجره Change Source فایل مرجع رو از مسیر درستش انتخاب می کنیم و دکمه OK رو می زنیم؛

انتخاب مجدد فایل مرجع از پنجره Change Source

شکل 5- انتخاب مجدد فایل مرجع از پنجره Change Source

  1. با انتخاب دوباره فایل مطابق شکل 5، خطای نمایش داده شده در پنجره Edit Links رفع میشه و به جای عبارت خطا عبارت OK نمایش داده میشه (مطابق شکل زیر). برای تمامی خطاهای از این نوع ( عدم یافتن منبع) می تونیم همین مراحل رو طی کنیم.

نحوه نمایش خطا های رفع شده در پنجره Edit Links

شکل 6-  نحوه نمایش خطا های رفع شده در پنجره Edit Links

روش دوم: شناسایی و رفع مشکل لینک های معیوب با ابزار  Find and Replace

همان طور که در روش اول شناسایی هایپرلینک معیوب مشاهده کردیم استفاده از پنجره Edit Links این امکان رو فراهم می کنه تا لیستی از منابع خارجی که فایل جاری به آن ها لینک شده فراهم بشه اما در Edit Links مشخص نمیشه که چه سلول هایی حاوی لینک هستند لذا برای شناسایی این سلول ها از ابزار Find and Replace استفاده می کنیم. برای این منظور مراحل زیر رو طی می کنیم:

  1. کلید های Ctrl + F رو می زنیم تا پنجره Find and Replace باز بشه یا از تب Home و از زیر مجموعه Editing گزینه Find & Select و بعد گزینه Find رو انتخاب می کنیم؛
  2. در پنجره Find and Replace و در تب Find روی دکمه Options کلیک می کنیم؛
  3. در قسمت Find what عبارت .xl رو وارد می کنیم. این نوع جستجو باعث میشه تا تمامی فایل های اکسل با فرمت قدیمی (.xls)، فرمت جدید (.xlsx) و فایل های اکسل حاوی ماکرو (.xlsm) که به فایل جاری لینک شدن در نتیجه جستجو نمایش داده بشه. اگر اسم فایل مربوطه رو داشته باشیم می تونیم در این قسمت به جای عبارت (.xls) اسم فایل رو درج کنیم؛
  4. در قسمت Within گزینه Workbook رو انتخاب می کنیم تا جستجو در کل شیت ها انجام بشه؛
  5. در قسمت Look in گزینه Formulas رو انتخاب می کنیم؛
  6. روی دکمه Find All کلیک می کنیم. نتایج جستجو مطابق شکل زیر نشان داده شده.
حتما بخوانید:  نحوه فیلتر کردن داده ها بر اساس جنس آنها

همانطور که در شکل زیر مشخص هست هایپرلینک معیوب در ستون Value با خطای #REF! نمایش داده شدند.

نمایش لینک های معیوب در ابزار Finde and Replace با عبارت جستجوی.xl

شکل 7- نمایش لینک های معیوب در ابزار Finde and Replace با عبارت جستجوی.xl

اگر به دنبال اسم فایل مشخصی باشیم که معیوب هست می تونیم اسم فایل رو در قسمت Find What بنویسیم و نتایج جستجو رو بررسی کنیم مطابق شکل 8 نام فایل “آذر” رو جستجو کردیم و در نتایج جستجو هم فایل های معیوب مشخص شدن که به دلیل املای نادرست اسم فایل (فروشآذر) لینک مربوطه با خطا مواجه شده.

نمایش لینک های معیوب در ابزار Finde and Replace با جستجوی نام فایل

شکل 8- نمایش لینک های معیوب در ابزار Finde and Replace با جستجوی نام فایل

نکته:
این روش برای شناسایی لینک های معیوب صد در صد قابل اعتماد نیست. به طور مثال ممکن هست خطای #REF! نمایش داده شده در شکل 8 مربوط به فرمولی باشه که در سلول لینک شده از فایل مرجع نوشته شده و در این حالت مشکلی در لینک بین فایل ها وجود نداره و این یعنی همه خطاهای #REF! به معنی وجود مشکل در لینک نیست. پس اگر خواستیم از این روش برای شناسایی لینک های معیوب استفاده کنیم باید روی تک تک خطاها کلیک کنیم و منشا بوجود آمدن خطا رو بررسی کنیم.

 

حل مشکل لینک های معیوب با ابزار Find and Replace

در بخش مربوط به نتایج جستجو در پنجره Find and Replace با کلیک بر روی هر نتیجه ای که با خطای #REF! مشخص شده می تونیم علت خطا رو شناسایی و حل کنیم یا اینکه با استفاده از امکان Replace All همه خطاهای مربوط به یک فایل نامعتبر رو برطرف کنیم. برای این منظور مراحل زیر رو طی می کنیم:

  1. در پنجره Find and Replace روی تب Replace کلیک می کنیم؛
  2. در قسمت Find what اسم فایل نادرست یا مسیر نادرست رو وارد می کنیم؛
  3. در قسمت Replace with اسم فایل درست یا مسیر درست رو وارد می کنیم؛
  4. دکمه Replace All رو می زنیم.
نکته:
بعد از زدن دکمه Replace All، پنجره Update Values مطابق شکل زیر باز میشه تا فایل اکسل درست رو انتخاب کنیم. در این حالت نباید فایلی رو انتخاب کنیم و بازدن دکمه Cancel پنجره Update Values رو می بندیم.

 

پنجره Update Values

شکل 9- پنجره Update Values

برای مثال فرض کنید در شکل 10 می خواهیم اسم فایل “فروش آذر” رو جایگزین عبارت “فروشآذر” کنیم تا خطاهای #REF! بر طرف بشه. همان طور که در شکل 10 نمایش داده شده در قسمت Find what عبارت ” فروشآذر.xlsx” رو می نویسیم و در قسمت Replace with عبارت ” فروش آذر.xlsx” و بعد دکمه Replace All رو می زنیم. مشاهده می کنیم که مطابق شکل 11 تمامی خطاها در ستون Value برطرف شده و اسم فایل هم در ستون Formula تصحیح شده.

رفع مشکل لینک های معیوب با استفاده از ابزار Find and Replace

شکل 10- رفع مشکل لینک های معیوب با استفاده از ابزار Find and Replace

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

وضعیت ستون های Value و Formula بعد از جایگزینی

شکل 11- وضعیت ستون های Value و Formula بعد از جایگزینی

در خصوص مسیر ذخیره سازی فایل مرجع هم می تونیم از ابزار Find and Replace استفاده کنیم به این صورت که در قسمت Find What آدرس مسیر اشتباه فایل رو وارد می کنیم و در قسمت Replace With آدرس مسیر درست رو وارد می کنیم. برای مثال اگر فایل مرجع در ابتدا در فولدر Documents بوده و بعد به فولدر Reports که در زیر مجموعه فولدر Documents قرار داره منتقل شده باشه، در این حالت در قسمت Find What عبارت “\Documents\” و در قسمت Replace With عبارت “\Documents\Reports\” رو وارد می کنیم.

روش سوم: استفاده از VBA

کد VBA که در زیر نمایش داده شده همه لینک های خارجی که در یک فایل اکسل وجود دارند رو بررسی می کنه و هایپرلینک معیوب رو به صورت یک لیست نمایش میده. در کد VAB زیر برای شناسایی لینک های خارجی از روش LinkSources استفاده شده و برای شناسایی لینک های معیوب از روش LinkInfo استفاده شده.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
Sub FindBrokenLinks()
    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  'https://docs.microsoft.com/en-us/office/vba/api/excel.xllinkstatus
    Dim sheetReportName As String
    sheetReportName = "Broken Links report"
    linksStatusDescr = "File missing"
    reportHeaders = Split("Worksheet, Cell, Formula, Workbook, Link Status", ", ")
    rowNo = 1 'Header row
    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 = 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
                        linksStatusCode = ActiveWorkbook.LinkInfo(CStr(linkFilePath), xlLinkInfoStatus)
                        If xlLinkStatusMissingFile = linksStatusCode And (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
                            End With
                            Exit For
                        End If
                    Next indI
                    For Each namedrangeCur In Names
                       If InStr(rangeCur.Formula, namedrangeCur.Name) Then
                            linkFilePath = ""
                            linksStatusCode = -1
                            If < InStr(namedrangeCur.RefersTo, "[") Then
                                linkFilePath = Replace(Split(Right(namedrangeCur.RefersTo, Len(namedrangeCur.RefersTo) - 2), "]")(), "[", "")
                                linksStatusCode = ActiveWorkbook.LinkInfo(CStr(linkFilePath), xlLinkInfoStatus)
                            End If
                            If xlLinkStatusMissingFile = linksStatusCode 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
                                   If < Len(linkFilePath) Then
                                    .Cells(rowNo, 5) = linksStatusDescr
                                   End If
                               End With
                            End If
                            Exit For
                        End If
                    Next namedrangeCur
                End If
            Next rangeCur
        End If
    Next
    Columns("A:E").EntireColumn.AutoFit
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

 

حتما بخوانید:  نحوه کار با Autofill و ایجاد لیست خودکار در اکسل

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

  • بعد از باز کردن فایل اکسل کلیدهای Alt + F8 رو می زنیم؛
  • از پنجره Macro گزینه FindBrokenLinks رو انتخاب می کنیم؛
  • دکمه Run رو می زنیم. مطابق شکل 12

پنجره Macro

شکل 12- پنجره Macro

در یک شیت جداگانه به نام ” Broken Links report” لیستی از لینک های معیوب مطابق شکل زیر نمایش داده میشه

لیست لینک های معیوب پس از اجرا  ماکرو FindBrokenLinks

شکل 13- لیست لینک های معیوب پس از اجرا  ماکرو FindBrokenLinks

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

دانلود فایل این آموزش

جهت دانلود فایل آموزش شناسایی و اصلاح هایپرلینک معیوب از لینک زیر استفاده کنید:

با عضویت در سایت به صورت مستقیم دانلود کنید

  • ارسال به ایمیل

Des

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

برچسب:ترفندهای اکسل

  • اشتراک گذاری:
profile avatar
حسنا خاکزاد
فارغ التحصیل لیسانس مهندسی صنایع، ارشد مدیریت صنعتی از دانشگاه تربیت مدرس و عاشق اکسل هستم. از سال 1388 که ترم 2 لیسانس بودم، به توصیه استاد مشاورم شروع به خوندن اکسل بصورت حرفه ای کردم و همچنان در حال مطالعه و یادگیری و البته آموزش به بقیه هستم.

مطلب قبلی

مدیریت لینک ها در اکسل
۱ اسفند ۱۳۹۹

مطلب بعدی

فعال کردن Power Query در ورژن های مختلف اکسل
۱ اسفند ۱۳۹۹

ممکن است همچنین دوست داشته باشید

Distinct-and-Unique-Values-Cover
تفاوت داده Distinct و Unique چیست؟ چطور میشه مشخص کرد
۱۰ فروردین, ۱۴۰۰
Slicer Cover
همه چیز در مورد Slicer در اکسل
۱۶ بهمن, ۱۳۹۹
Pivot Table Series- Part 01- Cover
سری آموزش های Pivot Table (قسمت دوم)
۱۲ بهمن, ۱۳۹۹

نظر بدهید لغو پاسخ

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

-- بارگیری کد امنیتی --

زودتر از دیگران با خبر بشید

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

آخرین مطالب

مقادیر متمایز در اکسل
تفاوت داده Distinct و Unique چیست؟ چطور میشه مشخص کرد
۱۰فروردین۱۴۰۰
استفاده از چندین تابع IF اکسل در هم
توابع IF تو در تو و نکات مهمی که باید بدانید
۲۷اسفند۱۳۹۹
تابع Trim
تابع Trim در اکسل
۲۱اسفند۱۳۹۹
کاربرد Power Query
چند مثال کاربردی از ابزار Power Query
۱۵اسفند۱۳۹۹

وجود یک منبع جامع و به روز که بدونی همیشه پشتیبانته برای یادگیری ضروریه. اکسل پدیا رو برای همین به وجود آوردیم.

پاسخ سوالات شما

اگر سوالات اکسلی دارید تو تالار گفتمان اکسل پدیا میتونید مطرح کنید. اساتید بسیاری هستند که به شما کمک میکنند.

کانال و تالار گفتمان اکسل پدیا

ورود به تالار گفتگو تلگرام

عضویت در خبرنامه
با عضویت در خبرنامه، ایمیل‌های آموزشی درباره اکسل و برنامه نویسی وی بی را دریافت خواهید کرد.

  • این فیلد برای اعتبار سنجی است و باید بدون تغییر باقی بماند .

021-47625755

[email protected]

آدرس: تهران، میدان دوم صادقیه، برج گلدیس، طبقه 7 واحد 721

دانلود اپ اکسل پدیادانلود از کافه بازار

تمامی حقوق برای اکسل پدیا محفوظ است.

  • اکسل از راه دور
  • شرایط و قوانین
  • درباره ما
  • تماس با ما