نحوه رفرنس دهی در اکسل
رفرنس در اکسل به یک یا بخشی از یک سلول گفته می‌شود که با اطلاعات درون آن توابع و فرمول های اکسل کامل می‌شوند. در هنگام استفاده از نرم‌افزار Excel یادگیری رفرنس دهی یا ایجاد مراجع سلولی ساده و ترکیبی و… بسیار مهم خواهد بود. زیرا این مهارت‌ها از مهم‌ترین موضوعات در هنگام کار با سلول اکسل هستند. شناخت ابزارهای مخصوص در اکسل هنگام استفاده از سلول‌ها و کسب مهارت‌های لازم در شروع کار مهم است. در این مقاله سعی شده است تمام مراحل نحوه رفرنس دهی در اکسل مورد بررسی قرار بگیرند و به صورت پله پله آموزش داده شوند.
آشنایی با مراجع سلولی مطلق و نسبی
هنگام رفرنس در اکسل باید با مراجع سلولی آشنا شده باشید. مراجع سلولی به دو نوع نسبی و مطلق تقسیم می‌شوند. هر گاه به ساختن و کپی یک فرمول نیاز باشد باید از مرجع نسبی کمک بگیرید. این مرجع هنگام کپی یک فرمول سلول دیگر تغییر خواهند کرد. برعکس مرجع نسبی، مرجع مطلق هنگام پر شدن و یا کپی کردن یک فرمول تغییر نمی‌کند. این مرجع برای ثابت نگه داشتن یک سطر یا یک ستون به شما کمک خواهد کرد. قبل از شروع هر کاری بهتر است با نحوه کار با این مراجع سلولی آشنا شوید. در ادامه به صورت جداگانه نحوه کار با آن‌ها بررسی می‌شود:
- مراجع نسبی
در ابتدا یک مثال ساده را در نظر بگیرید. برای نشان دادن یک قیمت نیاز به فرمولی داریم که در آن قیمت هر آیتم به وسیله مقدار آن، افزایش یابد. یعنی قیمت در هر چیزی ضرب شود. در این زمان به جای فرمول نویسی، می‌توان فرمول یک سلول را در سایر سلول‌ها کپی کرد. برای این کار می‌توانید مراحل زیر را طی کنید:
- برای شروع سلولی که شامل فرمول است را انتخاب کنید. D2 سلولی است که در مثال انتخاب می‌شود.
- یک فرمول را برای محاسبه ارزش دلخواه وارد کنید. در مثال فرمول B2-C2 انتخاب شده است.
- برای نشان دادن نتیجه در سلول باید کلید ENTER در صفحه کلید را فشار دهید. به این ترتیب فرمول محاسبه خواهد شد.
- حال در کنار سلول D2 یک دستگیره توپر مشاهده می‌شود. باید روی آن کلیک کرده، نگه دارید و تا سلولی که مد نظر دارید آن را بکشید. در مثال این دستگیره از سلول D3 تا سلول D12 کشیده شده است.
- در پایان با رها کردن ماوس با کمک از مراجع نسبی فرمول در تمام سلول‌های انتخاب شده کپی می‌شود و مقادیر در آن‌ها محاسبه خواهند شد. به این ترتیب رفرنس در اکسل با کمک مراجع سلولی به درستی انجام می‌شود.
- مراجع مطلق
مثالی که در قسمت قبل برای رفرنس دهی در Excel زدیم، در مرجع مطلق متفاوت خواهد بود. در این مثال ۷٫۵% از میزان مالیات فروش در سلول E1 نوشته می‌شود. با کمک آن مالیات فروش تمام کالا‌ها در ستون D محاسبه خواهند شد. برای این کار مشابه حالت قبل باید مراحلی را انجام دهید که از قرار زیر هستند:
- در ابتدای کار باید سلولی را که فرمول در آن قرار می‌گیرد، انتخاب کنید. سلول اکسل در این مثال E3 است.
- برای اینکه مقادیر در سلول درست محاسبه شوند فرمول مورد نظر را وارد کنید. B3_D3_$E$1 فرمولی است که در مثال وارد می‌شود.
- بعد از وارد کردن باید روی کلید ENTER در صفحه کلید ضربه بزنید. با این کار فرمول محاسبه شده و مقادیر در سلول به نمایش در می‌آیند.
- دستگیره توپر در کنار سلول D3 را گرفته و نگه دارید. سپس تا سلول D13 کشیدن دستگیره توپر را ادامه دهید.
- هنگامی که ماوس را رها کنید با کمک مرجع مطلق محاسبات در تمام سلول‌ها نشان داده می‌شوند. به این ترتیب رفرنس اکسل با کمک مرجع مطلق کامل می‌شود.
استفاده از تابع indirect در اکسل
در ادامه رفرنس در اکسل باید نحوه‌ی کار با تابع indirect را یاد بگیرید. از این تابع معمولا برای آدرس دهی سلول‌ها ، محدوده‌ها و دیگر شیت‌ها یا کاربرگ‌ها استفاده می‌شود. در واقع با کمک این تابع می‌توانید به جای وارد کردن آدرس‌ها در فرمول، یک فرمول پویا بسازید.
در واقع با تابع indirect امکان ارجاع به یک سلول بر اساس نمایش رشته ای آن فراهم می‌شود. این تابع دو فرمت دارد. فرمت اول A1 است که نوع معمول آدرس را نشان می‌دهد. در این فرمت ابتدا ستون و سپس ردیف نشان داده می‌شود. برای استفاده از این فرمت مراحل زیر را دنبال کنید:
1- مثالی را در نظر بگیرید. فکر کنید قرار است در سلول C1 فرمولی را بنویسید که مقدار سلول B1 را نمایش می‌دهد. در این زمان باید فرمول INDIRECT (B1) را بنویسید.
2- با نوشتن این فرمول سلول C1 عدد ۵۷ را نشان می‌دهد.
3- اگر مقدار سلول B1 را به A4 منتقل کنید، عدد C1 به ۶۵ تغییر پیدا می‌کند. به این ترتیب شما بدون تغییر فرمول C1 رنج آن را تغییر داده‌اید.
4- در صورتی که در فرمولی TRUE را مشاهده کردید از فرمت A1 استفاده کنید.
فرمت دیگر در این تابع فرمت R1C1 است. در این استایل ابتدا ردیف و سپس ستون بیان خواهد شد. برای رفرنس اکسل با فرمت R1C1 مراحل زیر را دنبال کنید:
۱. در این فرمت باید فرمول INDIRECT (B2,0) را در سلول C2 وارد کنید. در این صورت عدد ۶۵ در C2 نمایش داده می‌شود.
۲. پارامتر دوم باید برابر با 0 باشد.
۳. برای تغییر مقدار سلول C2 نیازی به تغییر فرمول نخواهد بود. فقط کافی است مقدار سلول B2 را به عبارت R2C1 تغییر دهید.
۴. از این فرمت هنگام دیدن False در فرمول استفاده کنید.
ارجاع با کمک تابع Vlookup در اکسل
یکی از توابع کاربردی در نحوه رفرنس اکسل تابع reference VIOOkup است. اگر سلول‌ها به دلخواه فرد مرتب شده باشند ممکن است سلول رفرنس که به آن ارجاع می‌شود، جا به جا شده باشد. تابع VIOOKUP از این گونه تغییرات در کاربرگ‌ها جلوگیری می‌کند. یادگیری کار با این تابع کاربردی رفرنس در اکسل بسیار مفید خواهد بود. برای این کار به مراحل زیر توجه کنید:
۱. فکر کنید قرار است در یک برگه‌ی دیگر که بر اساس شناسه استخدامی کارمندان نوشته شده، به تاریخ آغاز به کار یک کارمند برگردید.
۲. باید ابتدا فرمول VLOOKUP(A2,Employees!A:E,4,FALSE) نوشته شود.
۳. تابع VLOOKUP باید بین ستون‌های A تا E به دنبال شناسه کارمند باشد. این شناسه در سلول A2 قرار گرفته است.
4.بعد از آن از ستون چهارم تاریخ شروع به کار کارمند مورد نظر را استخراج می‌کند.
جمع بندی
برای ایجاد یک فایل اکسل مهارت‌هایی نیاز است که یادگیری آن‌ها واجب خواهد بود. رفرنس در اکسل با روش‌های مختلفی انجام می‌شود. هرگاه در هنگام فرمول نویسی به تغییر نیاز داشتید باید از مراجع نسبی کمک بگیرید و در غیر این‌صورت از مراجع مطلق بهره ببرید. توابع INDIRECT و VLOOKUP هم هنگام ارجاع به شما کمک می‌کنند. در این مقاله به بررسی روش‌های مختلف دادن رفرنس در Excel و مراحل هریک از آن‌ها پرداختیم.
