فرمول offset و کاربردهای آن

[~M*E*H*D*I~]

فرمول OFFSET و کاربردهای آن

فرمول offset جهت ارجاع به یک محدوده از طریق تعیین نقطه شروع و دادن طول و عرض بکار برده می شود .

ترکیب فرمول OFFSET


آرگومان های این فرمول به شرح زیر است

:

=OFFSET(starting point, rows to move, columns to move, height, width) 

Starting point: یک سل که نقطه آغاز ما برای جابه جا شدن است
Rows & columns to move: چه تعداد ستون یا ردیف می خواهید از نقطه شروع فاصله بگیرید ، این اعداد می توانند مثبت ، منفی یا صفر باشند.
Height & width: این مقادیر مشخص کننده ابعاد محدوده ای است که می خواهید به آن ارجاع کنید به عنوان مثال 3و4 محدوده ای از سلول ها به بلندی 3 سلول و عرض 4 سلول می دهد.

شما می توانید همه آرگومان های فرمول Offset را به سلول های دیگر ارجاع دهید. به عنوان مثال می توانید بنویسید:

=OFFSET(A1,D1,D2,D3,D4) 

در این فرمول از سلول A1 شروع می شود D1 ردیف D2


ستون جابجا شده و محدوده ای با ارتفاع D3 و عرض D4 برمی گرداند


تصویر زیر گویای مطلب است :

چرا ازOffset استفاده می کنیم


چرا به طور مستقیم نمی نویسیم A1:C4 ، دلایلی وجود دارد

• استفاده از محدوده های داینامیک : عبارتی مانند A1:C4 محدوده ای استاتیک فراهم می آورد در حالی که گاهی نیاز به محدوده ای داینامیک وجود دارد
• گاهی آدرس دقیق وجود ندارد ، به عبارت بهتر اطلاعاتی در دست نیست که به کدام محدوده باید ارجاع دهیم ممکن است تعداد ستون یا ردیف و همچنین عرض و ارتفاع اعدادی حاصل از یک محاسبه باشد در این صورت استفاده از فرمول offset مفید خواهد بود.

ایجاد یک Workbook پویا


جهت درک بهتر مطلب یک فایل پویا آماده شده است که با تغییر آرگومان های مشخص شده به خوبی می توان پی به عملکرد این فرمول برد.

کاربرد عملی فرمول offset


اطلاعاتی از درصد ضایعات یک محصول در اختیار داریم که می بایست متوسط این درصد به صورت هفتگی گزارش گردد ، حالت ایده آل آن است که در یک داشبور مدیریتی آیتمی جهت نمایش اطلاعات 7 روز آخر نمایش داده شود اما چگونه:
با استفاده از فرمول offset امکان پذیر است

=AVERAGE(OFFSET(C3,COUNTA(C3:C300)-7,0,7,1)) 

برای محاسبه متوسط داده های 7 روز آخر می بایست از کل داده ها ، داده های 7 روز آخر را داشته باشیم ، با استفاده از COUNTA(C3:C300) می توان تعداد داده های وارد شده را شمارش کنیم به عنوان مثال داده های 18 روز ثبت شده است با کم کردن 7 می توان به یک سلول قبل از شروع داده های مورد نیاز دست یافت این عدد نمایشگر تعداد ردیف هایی است که از مبدأ فاصله گرفته می شود ارتفاع محدوده مورد نیاز 7 است که در قسمت Height وارد می شود ، برای درک بهتر به فایل مربوطه رجوع و با Evaluate fomula عملکرد فرمول را بررسی کنید .

محدودیت های Offset

با آنکه این فرمول قادر به بازگرداندن محدوده های پویا است با این حال محدودیت هایی نیز دارد.

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

جایگزین هایی برای Offset


دو جایگزین برای Offset وجود دارد

• استفاده از Excel tables : از ورژن 2007 به بعد با استفاده از table ایجاد محدوده های پویا تسهیل شده است و به راحتی می توان فرمول هایی جهت استفاده از محدوده های پویا نوشت
• استفاده از فرمول index : توسط این فرمول نیز قادر به ایجاد محدوده هایی پویا خواهید بود گرچه در نوع استفاده تفاوت هایی وجود دارد در ضمن این فرمول همانند offset حافظه اشغال نمی کند.7 دلیل برای استفاده از فرمول index
  
  

سخن پایانی


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

[Behnam]

با تشکر از استاد وطن پرست برای مطالب آموزشی مختصر و بسیار مفید
استاد لطفا واسه مطالب بعدی، آموزش هایی از ترکیب توابع offset و index مخصوصا با توابعی مثل vlookup و match هم قرار بدین
ممنون

[~M*E*H*D*I~]

با تشکر از استاد وطن پرست برای مطالب آموزشی مختصر و بسیار مفید
استاد لطفا واسه مطالب بعدی، آموزش هایی از ترکیب توابع offset و index مخصوصا با توابعی مثل vlookup و match هم قرار بدین
ممنون

درود به شما ، بنده شاگردم و دارم درس پس میدم ، چشم حتما مطلبی آماده خواهم کرد

[mostafap4]

با سلام و تشکر
لطفا میشه پسورد فایل را هم بگویید