سبد خرید
0

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

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

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

مدیریت لینک
۳/۵ - (۲ امتیاز)

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

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

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

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

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

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

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

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

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

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

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

نکته:
با استفاده از ابزار Find and Replace فقط می تونیم لینک های استفاده شده در سلول ها رو شناسایی کنیم و لینک های استفاده شده در نمودارها، Defined Names و شکل ها رو باید با روش های دیگه ای جستجو کنیم که در ادامه به این موضوع می پردازیم

 

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

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

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

شناسایی لینک های خارجی در Define Name

شکل ۲- شناسایی و مدیریت لینک های خارجی در 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

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

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

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

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

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

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

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

نکته:
در صورتی که Object به یک فایل خارجی لینک شده باشه برای حذف کردن لینک نمی توانیم از Edit Links استفاده کنیم. برای این منظور روی Object کلیک راست می کنیم و از لیست نمایش داده شده گزینه Remove link رو انتخاب می کنیم.

 

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

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

  • در نمودار مورد نظر روی عنوان نمودار (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

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

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

پنجره Change Pivot Table Data Source

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

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

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

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

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

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

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

پنجره Edit Links

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

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

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

با انتخاب گزینه دوم پیغام نمایش داده نمیشه و لینک ها هم به طور خودکار بروز رسانی نمیشن؛

با انتخاب گزینه سوم پیغام نمایش داده نمیشه و لینک ها بروزرسانی می شن.

پنجره Startup Prompt

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

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

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

Trust Center در پنجره Excel Options

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

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

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

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

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

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

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

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

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

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

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

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

نکته:
با توجه به اینکه قطع کردن لینک ها با Undo کردن قابل بازگشت نیست بهتره قبل از اینکه لینکی رو قطع کنیم حتما تمامی جوانب رو بسنجیم.

 

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

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

نحوه حذف لینک ها در پنجره Edit Links

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

نکته:
با توجه به اینکه همه لینک های مورد استفاده در فایل در پنجره Edit Links نمایش داده نمیشه (برای مثال لینک های که در Pivot Table تعریف شدند) پس اگر گزینه Edit Links غیر فعال بود و باز هم در مورد لینک به فایل های خارجی پیغامی دریافت کردیم باید تمامی قسمت هایی که احتمال میدیم که لینک داشته باشن (مثلا نمودارها، شکل ها و…) رو چک کنیم.

 

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

یکی از مرسوم ترین راه ها برای دسترسی به لیست لینک های موجود در فایل، استفاده از پنجرهEdit Links  هست. مطابق شکل ۱۶ این پنجره اطلاعات زیر رو در مورد لینک ها مشخص می کنه:

Source: در این بخش اسم فایل لینک شده نمایش داده میشه

Type: این قسمت مشخص می کنه که فایل Worksheet یا Workbook

Update: این قسمت روش بروزرسانی لینک رو مشخص می کنه که می تونه دستی (Manually) یا خودکار (Automatically) باشه

Status: این بخش وضعیت لینک رو مشخص می کنه که می تونه یکی از موارد ,OK, Warning, Unknown Source is Open باشه. برای دریافت آخرین وضعیت مربوط به لینک روی گزینه Check Status کلیک می کنیم.

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

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

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

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

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

پنجره Visual Basic Editor

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

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

پنجره Macro

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

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

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

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

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

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

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

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

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

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

126

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

ارسال دیدگاه

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

توسط
تومان