آموزش رایگان فارسی و تصویری تابع OFFSET در اکسل و کاربرد آن دینامیک کردن نمودارها، جدول ها و … است. تابع OFFSET از تابع های نسبتاً پرکاربرد اکسل می باشد. میتونیم با تعیین فاصله سطری و ستونی مشخص از یک سلول یا محدوده خاص، به یک سلول یا محدوده دیگر برسیم.پاپولی توضیح کامل این تابع پرکاربرد را به صورت آنلاین در اختیار شما عزیزان قرار می دهد.
ترکیب فرمول OFFSET
آرگومان های این فرمول به شرح زیر است
=OFFSET(starting point, rows to move, columns to move, height, width)
یا
(عرض[اختیاری]،ارتفاع [اختیاری]، تعداد ستون، تعداد سطر، سلول یا محدوده مرجع)OFFSET=
آموزش رایگان فارسی و تصویری تابع OFFSET در اکسل
خروجی تابع offset یک محدوده می باشد این تابع جهت اجرا نیازمند تعیین پنج مولفه می باشد.
1- محل مبدا جهت شروع محاسبات
مورد دو و سه مربوط به جابه جایی محل مبدا می باشد به طوریکه
2- تعداد سطری که از مبدا جابه جا می شوییم
3- تعداد ستونی که از مبدا جابه جا می شوییم
در این مرحله کار مبدا و میزان جابه جایی آن مشخص شد.
دو مولفه بعدی در واقع محدوده مورد نظر را از محل مبدا مشخص می کند
4- ارتفاع محدوده مورد نظر(1 یعنی بدون جابه جایی)
5- عرض محدوده مورد نظر(1 یعنی بدون جابه جایی)
توضیح بیشتر مولفه ها:
سلول یا محدوده مبدا: این پارامتر سلول یا محدوده ای رو مشخص میکنه که ما میخوایم از اون شروع کنیم و با حرکت کردن به تعداد سطر و ستون مشخص به سلول یا محدوده جدید برسیم. این پارامتر میتونه یک سلول مثل A1 یا یک محدوده مثل A1:B3 باشه.
تعداد سطر: این پارامتر مشخص میکنه که ما از سلول یا محدوده مبدا میخوایم چند سطر و در چه جهتی جابجا بشیم. در صورتی که این عدد مثبت باشه محدوده مبدا به سمت پایین و در صورتی که این عدد منفی باشه به سمت بالا جابجا میشه. در صورتی که محدوده مبدا شما بیش از یک سلول باشه (مثلا A1:B6)، جابجایی از اولین سلول موجود در محدوده یعنی A1 محاسبه میشه. به عنوان مثال با تعیین عدد 1، محدوده A2:B7 به عنوان نتیجه برگردونده میشه.
تعداد ستون: این پارامتر مشخص میکنه که ما از سلول یا محدوده مبدا میخوایم چند ستون و در چه جهتی جابجا بشیم. در در حالت راست به چپ در صورتی که این عدد مثبت باشه محدوده مبدا به سمت چپ و در صورتی که این عدد منفی باشه به سمت راست جابجا میشه.در صورتی که محدوده مبدا شما بیش از یک سلول باشه (مثلا A1:B6)، جابجایی از اولین سلول موجود در محدوده (A1) محاسبه میشه. به عنوان مثال با تعیین عدد 1، محدوده B1:C6 به عنوان نتیجه برگردونده میشه.
ارتفاع [اختیاری]: این پارامتر مشخص میکنه که بعد از جابجا شدن از محدوده مبدا به تعداد سطر و ستون مشخص، ارتفاع محدوده ای که به عنوان نتیجه تابع برگردونده میشه باید چند سطر باشه. تعیین این پارامتر اختیاری هست و در صورتی که ما پارامترهای ارتفاع و عرض رو تعیین نکنیم، تابع OFFSET اکسل به صورت خودکار پارامترهای ارتفاع و عرض رو مساوی با ارتفاع و عرض محدوه مرجع ما قرار میده. به عنوان مثال اگر ما ارتفاع و عرض رو تعیین نکنیم و سلول A1 رو به عنوان پارامتر اول تابع OFFSET اکسل انتخاب کنیم ارتفاع و عرض هر دو برابر 1 و اگر A1:B3 رو به عنوان محدوده مبدا انتخاب کنیم، ارتفاع برابر 3 و عرض برابر 2 خواهد بود.
عرض[اختیاری]: این پارامتر مشخص میکنه که بعد از جابجا شدن از محدوده مبدا به تعداد سطر و ستون مشخص، عرض محدوده ای که به عنوان نتیجه تابع برگردونده میشه باید چند ستون باشه. تعیین این پارامتر هم مثل پارامتر ارتفاع اختیاری هست.
نکته: تابع OFFSET اکسل یک تابع VOLATILE هست بدین معنی که با هر تغییر در wordbook شما، فارغ از اینکه تغییر موردنظر تاثیری رو نتیجه این تابع داشته باشه یا نه، این تابع مجدداً محاسبه میشه. بنابراین در استفاده از این تابع دقت کنید چون استفاده نابجا و زیاد از این تابع میتونه فایل اکسل شما رو خیلی کند و سنگین کنه.
آموزش رایگان فارسی و تصویری تابع OFFSET در اکسل
همونطور که گفتم این تابع یک سلول یا محدوده رو به عنوان ورودی از ما میگیره، به تعداد سطر و ستونی که مشخص کنیم روی جدول نسبت به اون سلول یا محدوده جابجا میشه و نتیجه رو در قالب یک سلول یا محدوده با توجه به پارامترهایی که ما براش تعیین میکنیم برمیگردونه. در واقع اگر بخوایم ابعاد محدوده مبدا رو هم همزمان با جابجایی تغییر بدیم میتونیم از پارامترهای ارتفاع و عرض استفاده کنیم.به راحتی و با استفاده از تابع offset می توان فرمول جمع را یکبار نوشته و با تغییر محدوده داده ها محدوده جمع زدن نیز تغییر می یابد.
مثال:در این فرمول از سلول A1 شروع می شود D1 ردیف D2
ستون جابجا شده و محدوده ای با ارتفاع D3 و عرض D4 برمی گرداند
آموزش رایگان و تصویری تابع OFFSET در اکسل
دلیل استفاده از تابع OFFSET
به چه دلیل به طور مستقیم نمی نویسیم (مثلا A1:C4)
- استفاده از محدوده های داینامیک : عبارتی مانند A1:C4 محدوده ای استاتیک فراهم می آورد در حالی که گاهی نیاز به محدوده ای داینامیک وجود دارد
- گاهی آدرس دقیق وجود ندارد، یا بهتر بگم این که اطلاعاتی در دست نیست که به کدام محدوده باید ارجاع دهیم ممکن است تعداد ستون یا ردیف و همچنین عرض و ارتفاع اعدادی حاصل از یک محاسبه باشد در این صورت استفاده از فرمول offset مفید خواهد بود.
محدودیت های Offset
با آنکه این فرمول قادر به بازگرداندن محدوده های پویا است با این حال محدودیت هایی نیز دارد.
- این فرمول حافظه را اشغال می کند ، با هر تغییری که در کاربرگ اتفاق افتد این فرمول مجددا کلیه محاسبات را انجام می دهد لذا استفاده از تعداد زیادی از این فرمول کارپوشه را کند خواهد کرد.
- با توجه به اینکه در این فرمول از داده های ارجاع شده استفاده می شود لذا در صورت بروز خطا ، هنگام خطایابی ممکن است گمراه کننده باشد .
آموزش رایگان فارسی و تصویری تابع OFFSET در اکسل
جایگزین هایی برای Offset
دو جایگزین برای Offset وجود دارد
استفاده از Excel tables : از ورژن 2007 به بعد با استفاده از table ایجاد محدوده های پویا تسهیل شده است و به راحتی می توان فرمول هایی جهت استفاده از محدوده های پویا نوشت
استفاده از فرمول index : توسط این فرمول نیز قادر به ایجاد محدوده هایی پویا خواهید بود گرچه در نوع استفاده تفاوت هایی وجود دارد در ضمن این فرمول همانند offset حافظه اشغال نمی کند.
آموزش رایگان فارسی و تصویری تابع OFFSET در اکسل
7 دلیل برای استفاده از فرمول index:
دلیل اول : بدست آوردن n امین داده
این فرمول بهترین و ساده ترین فرمول برای این کار است:
=index(list;n)
دلیل دوم : بدست آوردن مقدار حاصل از تقاطع یک سطر و یک ستون با دادن شماره های سطر و ستون
=index(list;n;m)
دلیل سوم: دریافت کل یا ستونی از یک جدول
گاهی شما می خواهید اطلاعاتی از کل یک جدول یا ستونی از یک جدول بدست آورید مثلا متوسط سن افراد شما می توانید از فرمول زیر استفاده کنید:
=average(age column)
همچنین می توانید از فرمول زیر نیز استفاده کنید
مثلا سن افراد در ستون 5 جدول قرار دارد می توان نوشتم
=average(index(list; ;5))
نکته: میتوان بجای ردیف از جای خالی یا 0 استفاده کنید همچنین در مورد ستون هم می توان بکار برد.
دلیل چهارم: جستجو به سمت چپ
می دانیم که استفاده از فرمول vlookup امکان جستجو در خلاف جهت را به ما نمی دهد اما با ترکیب فرمول های index&match می توان به این مشکل برطرف کرد ،در مثال فوق می خواهیم بدانیم کدام فرد بیشترین وزن را دارد.
=INDEX(List[F-Name];MATCH(MAX(List[weight]);List[weight];0);1)
دلیل پنجم: ایجاد محدوده داینامیک
تا اینجا با بعضی از توانایی های تابع index آشنا شده اید ، حال به برخی کاربرد ها پیچیده تر این تابع می پردازیم ، توانایی واقعی index در ماهیت آن نهفته است شما در ظاهر می بینید که index مقداری را نمایش می دهد اما در واقع این تابع شما را به سلولی که حاوی آن مقدار است ارجاع می دهد به عنوان مثال وقتی می نویسد index(list;8) شما 8 امین مقدار از لیست را می بینید اما در واقع شما به سلول حاوی هشتمین مقدار ارجاع داده شده اید پس حاصل این تابع یک ارجاع است و هرگاه شما نیاز به ارجاعی داشته باشید می توانید از تابع استفاده کنید .
به مثال زیر توجه فرمایید:
شما وقتی می خواهید ناحیه ای مانند A1:A10 را جمع بزنید از sum(A1:A10) استفاده میکنید در این فرمول a1 , a10 رفرنس هستند حال به این فرمول توجه کنید :
=sum(A1:index(A1:A50;10))
جواب ها در هر دو یکسان است اما در دومی index از محدوده a1 تا a50 شما را به 10 خانه اول ارجاع می دهد.
آموزش رایگان فارسی و تصویری تابع OFFSET در اکسل
دلیل ششم : ارجاع به محدوده خاصی از چندین محدوده.
دلیل هفتم : تابع index می تواند آرایه ها را پردازش کند.
آموزش رایگان فارسی و تصویری تابع OFFSET در اکسل
اکسل ایران،غلیرضا مقربی
