سبد خرید
0

سبد خرید شما خالی است.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

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

3 روش برای شناسایی و اصلاح هایپرلینک معیوب در اکسل
نظر شما در مورد این آموزش

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

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

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

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

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

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

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

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

پنجره Edit Links

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

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

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

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

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

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

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

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

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

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

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

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

شکل ۶-  نحوه نمایش خطا های رفع شده در پنجره 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

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

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

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

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

نکته:
این روش برای شناسایی لینک های معیوب صد در صد قابل اعتماد نیست. به طور مثال ممکن هست خطای #REF! نمایش داده شده در شکل ۸ مربوط به فرمولی باشه که در سلول لینک شده از فایل مرجع نوشته شده و در این حالت مشکلی در لینک بین فایل ها وجود نداره و این یعنی همه خطاهای #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

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

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

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

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

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

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

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

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

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

 

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

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

پنجره Macro

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

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

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

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

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

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

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

کلیدواژه : ترفندهای اکسل
آواتار
127

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

دیدگاه کاربران
  • nima ۸ تیر ۱۴۰۱ / ۱:۲۷ ب٫ظ

    سلام توضیحات بسیار عالی بود من فقط یه مشکل با لینک های دارم که وقتی فایل اکسل به سیستم دیگه جابجا میکنم دیگه لینک ها فایل بایز نمی کنند نمیدونم تیک گزینه خاصی باید زد؟

    • آواتار
      حسنا خاکزاد ۸ تیر ۱۴۰۱ / ۵:۲۳ ب٫ظ

      درود
      باید همه با هم در یک فولدر جابجا بشن
      یا اینکه وقتی جابجا کردین لینک فایل جدید رو بدید

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد.

توسط
تومان