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

ارتباط دو شیت در اکسل

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

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

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

• ارجاع به شیت دیگر ( ارتباط دو شیت در اکسل )
• ارجاع به فایل دیگر
• ارجاع به یک محدوده نامگذاری شده

1. چگونگی ارتباط دو شیت در اکسل

برای ارجاع دادن به یک سلول یا یک محدوده از یک شیت دیگه از همون فایل اکسل باید الگوی زیر رو رعیات کنیم. یعنی بعد از اسم شیت، علامت تعجب (!) و سپس آدرس سلول گذاشته میشه. یعنی:

لینک به یک سلول از شیت دیگه:

Sheet_name!Cell_address

نمونه : =Sheet1!A1

لینک به یک محدوده از شیت دیگه:

Sheet_name!First_cell:Last_cell

نمونه : =Sheet1!A1:A10

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

• تابع مورد نظر رو نوشته و پرانتز رو باز میکنیم.
• موقع انتخاب محدوده، روی تب اسم شیت مورد نظر کلیک کرده و محدوده دلخواه رو انتخاب میکنیم.
• پرانتز رو بسته و Enter را میزنیم. به تصویر زیر دقت کنید:

2. چطور به یک سلول یا محدوده از فایل دیگر، لینک برقرار کنیم

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

• ارجاع دادن به یک فایل اکسل باز

وقتی که فایل مرجعی که قراره اطلاعات از آن فراخوانی بشه، باز باشه، آدرس لینک شامل نام فایل (workbook) به همراه پسوند داخل براکت [ ]، به همراه نام شیت و نام سلول (با الگوی بالا) هست. به الگوی نمایش داده شده دقت کنید:

[Workbook_name]Sheet_name!Cell_address

مثلا فرض کنید میخوایم جمع محدوده B2:D50 در شیت farvardin از فایلی به نام sale حساب کنیم. الگو بصورت زیر خواهد بود:

=SUM( [Sale.xlsx]Farvardin! $B$2:$D$50 )

• ارجاع دادن به یک فایل بسته

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

Drive:\ folder\ [Workbook_name]Sheet_name!Cell_address

مثلا فرض کنید میخوایم جمع محدوده B2:D50 در شیت Farvardin از فایلی به نام sale حساب کنیم که فایل sale بسته است. الگو بصورت زیر خواهد بود:

=SUM(‘D:\reports\[Sale.xlsx]Farvardin’!$B$2:$B$50)

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

3. چطور به یک محدوده نامگذاری شده، لینک برقرار کنیم

علاوه بر ارجاع به یک سلول یا محدوده (Range) میتونیم به محدوده های نامگذاری شده هم ارجاع بدیم. برای این کار کافیه بجای اسم سلول یا محدوده مورد نظر، نام تعیین شده رو تایپ کنیم.

فرض کنید محدوده B2:B50 شیت farvardin از فایل Sale رو گذاشتیم Sale_Farvardin مطابق شکل ۱.

شکل ۱- ارتباط بین شیتها در اکسل – نامگذاری محدوده

حالا برای اینکه از این نام در شیت یا فایل دیگه بتونیم استفاده کنیم، الگوی کلی بصورت زیر خواهد بود:

=Function(name)

اما باید به یک نکته توجه داشته باشیم. اگر محدوده (Scope) نام مورد نظر Workbook هست، که فقط کافیه نام محدوده رو داخل تابع تایپ کنیم.

=SUM(Sale_Farvardin)

اما اگر محدوده در سطح شیت هست (یکی از شیت های فایل مورد نظر)، باید نام شیت رو هم در فرمول بیاریم:

=SUM(Farvardin!Sale_Farvardin)

اگر هم بخوایم به یک فایل خارج از این فایل ارجاع بدیم، بصورت زیر خواهد بود:

=Function(Workbook_name!name)

همه مواردی که در قسمت workbook تشریح شد برای ارجاع به محدوده نامگذاری نیز برقرار هست و فقط کافیه بجای آدرس سلول یا محدوده (Range) از نام مورد نظر استفاده کرد.

زمانی که فایلی که حاوی لینک هست رو باز میکنیم، پنجره ای ظاهر میشه که آیا میخواید لینک ها بروز شوند یا خیر؟ (مطابق شکل ۲)

با زدن گزینه Update نتایج سلول هایی که حاوی لینک هستن بروز خواهد شد.

شکل ۲- ارتباط بین شیتها در اکسل – پیغام بروزرسانی لینک ها

برای مشاهده همه لینک هایی که در یک فایل وجود داره، از مسیر زیر روی Edit Link کلیک میکنیم. از پنجره نمایش داده شده در شکل ۳ همه لینک های موجود در فایل قابل مشاهده هستند.

Data/ Connections/ Edit links

شکل ۳- پنجره نمایش لینک های استفاده شده در یک فایل

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

در انتها حتما مقاله مدیریت لینک ها در اکسل رو نگاه کنید.