روش های ارجاع دادن در اکسل — راهنمای کاربردی

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

در این مقاله، به بررسی روش ارجاع به «کاربرگ» (worksheet)-های دیگر در یک فایل اکسل و همچنین روش ارجاع به فایل‌های اکسل دیگر می‌پردازیم. همچنین مواردی مانند روش ساده‌تر ارجاع به یک محدوده از سلول‌ها در یک تابع با استفاده از نام‌های تعریف‌شده و روش بهره‌گیری از VLOOKUP برای ارجاع‌های دینامیک را بررسی می‌کنیم.

ارجاع به یک کاربرگ دیگر در فایل اکسل

یک ارجاع مقدماتی سلول به این صورت نوشته می‌شود که ابتدا حرف مربوط به ستون و سپس شماره ردیف می‌آید.

فیلم آموزش اکسل Microsoft Excel 2019 – مقدماتی در فرادرس

کلیک کنید

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

=January!B3

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

='January Sales'!B3

برای ایجاد این رفرنس‌ها (یا همان ارجاع‌ها) می‌توانید آن‌ها را مستقیماً درون سلول وارد کنید. با این حال روش ساده‌تر و مطمئن‌تر این است که به اکسل اجازه بدهید ارجاع را برای شما بنویسد.

به این منظور یک علامت تساوی (=) در یک سلول وارد کنید. سپس روی برگه Sheet کلیک کرده و در ادامه روی سلولی که می‌خواهید ارجاع بدهید کلیک کنید. در این زمان، اکسل ارجاع را در نوار فرمول برای شما می‌نویسد:

با زدن کلید اینتر، فرمول کامل می‌شود.

ارجاع به یک فایل اکسل دیگر

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

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

کلیک کنید

کافی است مطمئن شوید که فایل اکسل دیگری که مورد نظرتان است را نیز پیش از وارد کردن فرمول باز کرده‌اید. به این منظور مانند قبل یک علامت تساوی (=) وارد کنید، به فایل دیگر بروید و سپس روی سلولی که می‌خواهید ارجاع دهید کلیک نمایید. با زدن اینتر کار به پایان می‌رسد. این ارجاع متقابل شامل نام «کارپوشه» (workbook) دیگر است که درون براکت ها قرار گرفته است و در ادامه نام کاربرگ و شماره سلول آمده است.

=[Chicago.xlsx]January!B3

اگر فایل یا نام کاربرگ شامل فضاهای خالی باشد، در این صورت باید ارجاع فایل (شامل براکت) را در گیومه تکی قرار دهید.

='[New York.xlsx]January'!B3

در این مثال، می‌توانید علامت دلار ($) را در آدرس‌های سلول ببینید که نشان دهنده یک ارجاع مطلق است. زمانی که سلول‌ها و محدوده‌ها را در دیگر فایل‌های اکسل مورد ارجاع قرار می‌دهیم، ارجاع به صورت پیش‌فرض «مطلق» (Absolute) است. شما می‌توانید در صورت نیاز این وضعیت را به ارجاع نسبی (relative reference) تغییر دهید.

اگر هنگامی که کارپوشه (Workbook) ارجاع شده بسته می‌شود، به فرمول نگاه کنید، می‌بینید که شامل مسیر کامل مربوط به آن فایل است.

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

ارجاع به یک محدوده از سلول‌ها در تابع

با این که ارجاع دادن به یک سلول منفرد به قدر کافی مفید است، اما ممکن است بخواهید تابعی (مانند SUM) بنویسید که به یک محدوده سلولی روی کاربرگ یا کارپوشه دیگر ارجاع دهد. به این ترتیب تابع را به طور معمول بازکنید و سپس روی کاربرگ و محدوده سلول‌ها، به همان ترتیبی که در بخش‌های قبلی عمل کردیم، کلیک کنید. در مثال زیر، یک تابع SUM اقدام به جمع کردن مقادیر روی محدوده B2:B6 در یک کاربرگ به نام Sales می‌کند.

=SUM(Sales!B2:B6)

ارجاع با نام‌های تعریف شده برای محدوده‌ای از سلول‌ها

در اکسل می‌توان یک نام به سلول یا محدوده‌ای از سلول‌ها انتساب داد. این کار باعث می‌شود زمانی که مجدداً به نام‌ها نگاه می‌کنید به سادگی دریابید هر سلول یا محدوده سلول‌ها چه داده‌هایی را در برگرفته است. اگر از ارجاع‌های زیادی در یک اسپردشیت استفاده می‌کنید، نام گذاری محدوده مرتبط به این ارجاع‌ها می‌تواند موجب شود که راحت‌تر درک کنید چه کرده‌اید و به چه علت به کجا ارجاع داده‌اید. از این هم بهتر، آن است که هر نام یکتا است و به سلول یا محدوده‌ای معینی از سلول‌ها اشاره می‌کند.

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

کلیک کنید

برای نمونه، می‌توانیم نام یک سلول را به صورت «ChicagoTotal» ذخیره کنیم و سپس ارجاع متقابل آن را به صورت زیر انجام دهیم:

=ChicagoTotal

این یک جایگزین معنی‌دارتر برای یک ارجاع استاندارد مانند زیر است:

=Sales!B2

ایجاد یک نام تعریف‌شده آسان است. کار خود را با انتخاب کردن یک سلول یا محدوده‌ای از سلول‌ها آغاز کنید که می‌خواهید نامی به آن‌ها بدهید. روی کادر Name در گوشه بالا-چپ صفحه کلیک کرده و نامی که می‌خواهید تعیین کنید را وارد نمایید و در ادامه اینتر را بزنید.

زمانی که نام‌های تعریف‌شده ایجاد می‌کنید، نمی‌توانید از Space استفاده کنید. از این رو در این مثال کلمه‌ها در نام به هم چسبیده‌اند و به وسیله یک حرف بزرگ مشخص می‌شوند. همچنین می‌توانید کلمات را با کاراکترهایی مانند خط تیره (-) یا زیرخط (_) از هم جدا کنید.

اکسل یک ابزار برای مدیریت نام‌ها نیز دارد که به نظارت روی این نام‌ها به روشی آسان کمک می‌کند. به منوی Formulas > Name Manager بروید. در پنجره Name Manager می‌توانید فهرستی از همه نام‌های تعریف‌شده در کاربرگ را ببینید و بدانید که کجا قرار دارند و مقادیر کنونی‌شان چیست.

سپس می‌توانید از دکمه‌های ردیف فوقانی برای ویرایش و حذف این نام‌های تعریف شده استفاده کنید.

قالب‌بندی داده‌ها به صورت یک جدول

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

این سلول‌ها را می‌توان به صورت یک جدول قالب‌بندی کرد. به این منظور روی یک سلول در لیست کلیک کنید، به زبانه Home بروید و روی دکمه Format as Table کلیک کرده و سپس یک استایل را انتخاب کنید.

تأیید کنید که محدوده سلول‌ها صحیح است و این که جدول دارای هدر است.

سپس می‌توانید در زبانه Design یک نام معنی‌دار به جدول خود بدهید.

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

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

=SUM(Sales[Chicago])

در ادامه می‌بینید که جدول‌ها چگونه می‌توانند ارجاع دادن در تابع‌های تجمیع از قبیل SUM و AVERAGE را به مراتب آسان‌تر کنند.

گفتنی است که این جدول به منظور حفظ سادگی مثال کوچک انتخاب شده است. هر چه جدول بزرگ‌تر باشد، و برگه‌های بیشتری در کارپوشه داشته باشید، مزیت‌های آن بیشتر نمایان خواهند شد.

استفاده از تابع VLOOKUP برای ارجاع‌های دینامیک (Dynamic References)

روش‌های ارجاع‌ معرفی‌شده در این مقاله تا به اینجا همگی روی سلول‌های خاص یا محدوده‌ای از سلول‌های ثابت تعریف شده بودند. این وضعیت عالی است و برای اغلب نیازها کافی به نظر می‌رسد. اما اگر در این بین به سلولی ارجاع دهید که در آینده امکان جابه‌جایی آن به خاطر اضافه شدن ردیف‌های جدید وجود داشته باشد تکلیف چیست؟ یا اینکه، فرض را بر این بگذاریم فردی سلول‌ها را بر مبنای موردنظر خود مرتب (Sort) کند و به همین علت سلولی که به آن ارجاع داده‌ایم جابه‌جا شود، آنگاه چه باید کرد؟

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

کلیک کنید

در چنین وضعیت‌هایی نمی‌توان تضمین کرد که مقدار مورد نظر همچنان در همان سلولی که در ابتدا ارجاع یافته بود، قرار دارد. یک روش جایگزین در این وضعیت‌ها استفاده از تابع VLOOKUP در اکسل است که برای جستجوی مقدار در یک لیست استفاده می‌شود. بدین شکل ارجاع‌ها تا حد زیادی از خطرات تغییراتی که روی کاربرگ‌ها اعمال می‌شوند در امان خواهند ماند.

در مثال زیر، ما از تابع VLOOKUP برای گشتن به دنبال یک کارمند روی برگه دیگر بر اساس شناسه استخدامی وی استفاده کرده‌ایم و سپس تاریخ آغاز به کار او را بازگشت داده‌ایم. در ادامه فهرستی از کارمندان را مشاهده می‌کنید که در برگه Employees قرار دارند.

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

برای اینکه بهتر متوجه منظور شوید، تابع VLOOKUP زیر در برگه Employees و در بین ستون‌های A تا E، به دنبال شناسه کارمندی می‌گردد که در سلول A2 در اختیار آن قرار گرفته است. سپس تاریخ شروع به کار وی را بعد از پیدا کردن ردیف مربوط به شناسه، از ستون چهارم استخراج می‌کند و آبازگشت می‌دهد.

=VLOOKUP(A2,Employees!A:E,4,FALSE)

در ادامه تصویری از چگونگی جستجوی لیست از سوی این فرمول و بازگشت اطلاعات صحیح را مشاهده می‌کنید:

نکته جالب در مورد VLOOKUP نسبت به مثال‌های قبلی این است که کارمندان حتی در صورتی که ترتیب لیست تغییر یابد باز بر اساس شناسه‌شان جستجو و پیدا می‌شوند.

نکته: VLOOKUP فرمولی بسیار مفید است و ما در این نوشته تنها بخش کوچکی از قابلیت‌های آن را بررسی کردیم.

سخن پایانی

در این مقاله به بررسی چندین روش برای ایجاد ارجاع بین اسپردشیت‌ها و کارپوشه‌های اکسل پرداختیم. شما می‌توانید رویکردی را که مناسب تشخیص می‌دهید و با آن راحت‌تر هستید، برای وظیفه مورد نظر خود انتخاب کنید.