فهرست محتوا
مثالهای OFFSET
ساده ترین مثالی که میشه از تابع OFFSET در اکسل بیان کرد اینه از توابع اجباری فقط استفاده کنیم ، اگر تصویر زیر را در نظر بگیرید ، ساده ترین مثال به شکل زیر خواهد بود .
=OFFSET(D3,4,0)
مثال بالا در واقع آدرس D3 را به عنوان نقطه شروع و آرگومان اول در نظر گرفته و عدد 3 که در آرگومان دوم است تعداد سلولی را نمایش می دهد که در ردیف پائین می آید ( اگر منفی باشد بالا می رود ) آرگومان سوم هم که عدد 0 وارد شده یعنی در ستون حرکتی نکن و به چپ یا راست نرو و همونجا بمون و مقدارش رو نمایش بده .
تا اینجا متوجه شدیم که در آرگومان دوم خود سلول نقطه شروع شمارش نمیشه .
جمع کردن محدوده داده با تابع OFFSET
همان تصویر و مثال قبلی را در نظر داشته باشید و فرض کنید که بخواهیم داده های ماههای تیر و مرداد و شهریور را جمع بزنیم .
سناریو با استفاده از تابع OFFSET این است که آرگومان اول یا همان نقطه شروع باید D3 باشد و 4 ردیف به پائین حرکت کند و 0 ستون به چپ و راست برود و 3 ردیف پائین تر را در خود نگهدارد . حالا نتیجه را با یک تابع SUM ساده جمع می زنیم.
=SUM(OFFSET(D3,4,0,3))
مثال محدوده داینامیک با OFFSET
حالا همان مثال بالا را مجددا در نظر بگیرید ، با این فرض که می خواهیم همواره جمع سه ماههه آخر را داشته باشیم مثلا اگر مهر ماه هم اضافه جمع مرداد و شهریور و مهر را نمایش دهد و اگر آبان اضافه شد جمع شهریور و مهر و آبان را نمایش دهد .
برای این منظوز باید تابع OFFSET را با تابع COUNTA ترکیب کنیم .
=SUM(OFFSET(D3,COUNTA(D3:D16)-3,0,3))
در مثال بالا از آنجا تعداد آرگومان دوم یعنی تعداد ردیفی که باید به پائین بیاید و نقطه شروع جمع را مشخص کند ، بر اساس تعداد ماهها تعیین می شود باید با تابع COUNTA جمع شود .
در مورد محدوده D3:D16 هم محدوده ای هست که انتظار داریم ماهها تا آنجا ادامه داشته باشد و اگر امکان رشد بیشتر داشته باشد طبیعی است که محدوده هم باید بزرگتر در نظر گرفته شود .
ایجاد لیست کشویی داینامیک با OFFSET
حالا فرض کنیم که می خواهیم لیست کشویی ایجاد کنیم که حاوی نام ماههای وارد شده باشد و از آنجایی که اگر محدوده بزرگتر از داده های موجود را انتخاب کنیم ، اکسل فضاهای خالی را هم نمایش می دهد ، این مشکل را باید با تابع OFFSET حل کنیم .
قبلا در مورد کومبوباکس در اکسل صحبت کرده ایم ، اگر به تصویر بالا دقت کنید نتیجه انتخاب محدوده برای استفاده در لیست پائین افتادنی را می بینید که فضای خالی در آن نمایان شده است .
اگر بخواهیم کاری کنیم که فقط در صورت داشتن اطلاعات نمایش داده شود باید مراحل زیر را طی کنیم.
- با زدن کلیدهای Alt + A + V + V پنجره دیتا ولیدیشن را باز می کنیم.
- از گزینه ها گزینه List را انتخاب می کنیم .
- در قسمت محدوده به جای اینکه محدوده ثابت وارد کنیم فرمول زیر را وارد می کنیم.
=OFFSET(C3,1,0,COUNTA(C4:C16))
استفاده از OFFSET در رسم نمودار
همانند روش بالا از این تابع پرکارد اکسل می توان در رسم نمودار هم استفاده کرد و در دوره آموزش پیشرفته اکسل از این تابع خیلی زیاد استفاده کرده ایم مخصوصا زمان ریم نمودارهای داینامیک.
