Reference-in-excel-functions
Reference-in-excel-functions

در نرم افزار اکسل در رفرنس های سلولی یا محدوده ای یک فرمول، می توان از سه نوع رفرنس استفاده کرد:

  • وابسته یا Relative: وقتی فرمول را به سلول دیگری کپی کنید، رفرنس های ردیفی و ستونی می تواند تغییر کند، زیرا آن ها از ردیف و ستون جاری نشأت می گیرند. به حالت پیش گزیده (default)، اکسل از این حالت در رفرنس های فرمول ها استفاده می کند.
  • مستقل یا Absolute: با کپی فرمول به محل جدید، رفرنس های ردیفی و ستونی تغییر نمی کنند؛ زیرا به یک آدرس سلولی واقعی ارجاع داده شده اند. برای فهماندن این نوع رفرنس دهی به اکسل، باید دو نشان دلار ($) در آدرس سلول وارد کرد، یکی پیش از حرف ردیف و دیگری پیش از عدد ستون (مانند $A$5).
  • ترکیبی یا Mixed: یکی از رفرنس های ردیفی یا ستونی، مستقل عمل می کند و دیگری، وابسته. تنها یکی از بخش های آدرس، مستقل است (مانند $A4 یا A$4).نوع رفرنس سلولی، تنها زمانی مفید است که قصد داشته باشید فرمول را به سلول دیگری کپی کنید. به نمونه های زیر دقت کنید.

نوع رفرنس سلولی، تنها زمانی مفید است که قصد داشته باشید فرمول را به سلول دیگری کپی کنید. به نمونه های زیر دقت کنید.

در شکل زیر، کاربرگ ساده ای می بینید که در سلول D2 آن، تعداد یک کالا در بهای آن ضرب شده است:

=B2*C2

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

در این فرمول، از رفرنس وابسته استفاده شده است (نشان $ ندارد). پس اگر این فرمول را در سلول های زیرین آن کپی کنید رفرنس ها خود را به یک ترتیب وابسته، اصلاح می کنند؛ مثلا فرمول سلول D3 به این شکل درمی آید:

=B3*C3

اما اگر در رفرنس های سلولی فرمولی که در سلول D2 نوشتیم، از رفرنس دهی مستقل به شکل زیر استفاده می کردیم چه می شد؟

=$B$2*$C$2

در این حالت، کپی فرمول در سلول های زیرین آن، به نتایج نادرستی منجر می شد. چرا که فرمول سلول D3 دقیقا همانند فرمول سلول D2 می شد.

اجازه دهید این مثال را اندکی گسترش دهیم و مالیات فروش ها را که در سلول B7 درج است، وارد محاسبه کنیم (شکل روبه رو). در این مورد، فرمول سلول D2 به این شکل خواهد بود:

=(B2*C2)*$B$7

رفرنس-دهی-ثبابت-و-متغیر-در-اکسل

تعداد در بها ضرب شده و نتیجه ی آن در مقدار ثابت مالیات فروش که در سلول B7 درج است، ضرب شده است. در اینجا دقت کنید که ارجاع به سلول B7، یک رفرنس مستقل محسوب می شود. با کپی کردن فرمول درون سلول D2 در سلول های زیرین آن، سلول D3 فرمولی به شکل روبه رو را در خود خواهد دید:

=(B3*C3)*$B$7

می بینید که رفرنس به سلول های B2 و C2 اصلاح شده است اما رفرنس به سلول B7 ثابت مانده است و این همان چیزی است که می خواستیم، زیرا سلول حاوی مقدار مالیات فروش، هرگز تغییر نمی کند و ثابت است.

کاربرد رفرنس های ترکیبی را نیز در شکل زیر می بینید. فرمول های بازه ی C3:F6، ناحیه ی طول و عرض های متغیر را محاسبه می کند. فرمول درون سلول C3 به این شکل است:

=$B3*C$2

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

رفرنس-ترکیبی-ثابت-و-نسبی-در-اکسل

می بینید که هر دو رفرنس به کار رفته در این فرمول، از نوع ترکیبی است. در رفرنس به سلول B3، از یک رفرنس مستقل ستونی ($B) و در رفرنس به سلول C2 از یک رفرنس مستقل ردیفی ($۲) استفاده شده است. در نتیجه، این فرمول می تواند به پایین و اطراف کپی شود و نتایج درستی را ارائه دهد. برای نمونه، پس از کپی آن، فرمول سلول F6 به صورت روبه رو در خواهد آمد:

=$B6*F$2

تغییر نوع رفرنس ها

با افزودن نماد دلار در محل های مناسب از آدرس سلول، می توان رفرنس های ناوابسته (nonrelative) را وابسته کرد (مستقل یا ترکیبی). همچنین می توان از یک میانبر دستی (کلید F4) استفاده کرد. پس از وارد کردن یک رفرنس سلولی (با تایپ یا با اشاره کردن)، می توان کلید F4 را پی در پی فشرد و از بین حالت های رفرنس دهی که اکسل به آن می دهد، نوع دلخواه را برگزید.

 

برای نمونه، اگر عبارت A1= را برای آغاز شدن یک فرمول وارد کنید و کلید F4 را بفشارید، این فرمول به $A$1 تبدیل می گردد. فشار دوباره ی F4، فرمول را به A$1= تبدیل می کند و فشار دوباره، به $A1= می رسد. یک فشار دیگر کلید F4، دوباره به فرمول آغازین A1= برمی گردد. پس می توان به فشار این کلید ادامه داد و پس از نمایش نوع دلخواه رفرنس، آن را برگزید.

 

بسیار مهم: وقتی سلول یا محدوده ای را نام گذاری می کنید، اکسل (حالت پیش فرض) از یک رفرنس مستقل برای نام استفاده می کند. برای نمونه، اگر نام SalesForecast را به محدوده ی B1:B12 دهید، کادر Refers To در پنجره ی New Name، رفرنس را به شکل $B$1:$B$12 نشان می دهد. این تقریبا همان چیزی است که همیشه می خواهیم. اگر سلولی که دارای یک رفرنس نامدار در فرمول خود است را کپی کنید، فرمول کپی شده حاوی رفرنسی به نام اصلی خواهد بود.

 

No votes yet.
Please wait...