~M*E*H*D*I~
2013/10/13, 13:39
فرمول 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 برمی گرداند
تصویر زیر گویای مطلب است :
http://chakane.persiangig.com/image/excel-offset-formula-examples.png
چرا ازOffset استفاده می کنیم
چرا به طور مستقیم نمی نویسیم A1:C4 ، دلایلی وجود دارد
استفاده از محدوده های داینامیک : عبارتی مانند A1:C4 محدوده ای استاتیک فراهم می آورد در حالی که گاهی نیاز به محدوده ای داینامیک وجود دارد
گاهی آدرس دقیق وجود ندارد ، به عبارت بهتر اطلاعاتی در دست نیست که به کدام محدوده باید ارجاع دهیم ممکن است تعداد ستون یا ردیف و همچنین عرض و ارتفاع اعدادی حاصل از یک محاسبه باشد در این صورت استفاده از فرمول offset مفید خواهد بود.
ایجاد یک Workbook پویا
جهت درک بهتر مطلب یک فایل پویا آماده شده است که با تغییر آرگومان های مشخص شده به خوبی می توان پی به عملکرد این فرمول برد.
http://chakane.persiangig.com/image/Untitled-1.gif
کاربرد عملی فرمول offset
اطلاعاتی از درصد ضایعات یک محصول در اختیار داریم که می بایست متوسط این درصد به صورت هفتگی گزارش گردد ، حالت ایده آل آن است که در یک داشبور مدیریتی آیتمی جهت نمایش اطلاعات 7 روز آخر نمایش داده شود اما چگونه:
با استفاده از فرمول offset امکان پذیر است
=AVERAGE(OFFSET(C3,COUNTA(C3:C300)-7,0,7,1))
برای محاسبه متوسط داده های 7 روز آخر می بایست از کل داده ها ، داده های 7 روز آخر را داشته باشیم ، با استفاده از COUNTA(C3:C300) می توان تعداد داده های وارد شده را شمارش کنیم به عنوان مثال داده های 18 روز ثبت شده است با کم کردن 7 می توان به یک سلول قبل از شروع داده های مورد نیاز دست یافت این عدد نمایشگر تعداد ردیف هایی است که از مبدأ فاصله گرفته می شود ارتفاع محدوده مورد نیاز 7 است که در قسمت Height وارد می شود ، برای درک بهتر به فایل مربوطه رجوع و با Evaluate fomula عملکرد فرمول را بررسی کنید .
http://chakane.persiangig.com/image/2013-10-13%2011-46-03%20%D9%82-%D8%B8.png
محدودیت های Offset
با آنکه این فرمول قادر به بازگرداندن محدوده های پویا است با این حال محدودیت هایی نیز دارد.
این فرمول حافظه را اشغال می کند ، با هر تغییری که در کاربرگ اتفاق افتد این فرمول مجددا کلیه محاسبات را انجام می دهد لذا استفاده از تعداد زیادی از این فرمول کارپوشه را کند خواهد کرد.
با توجه به اینکه در این فرمول از داده های ارجاع شده استفاده می شود لذا در صورت بروز خطا ، هنگام خطایابی ممکن است گمراه کننده باشد .
جایگزین هایی برای Offset
دو جایگزین برای Offset وجود دارد
استفاده از Excel tables : از ورژن 2007 به بعد با استفاده از table ایجاد محدوده های پویا تسهیل شده است و به راحتی می توان فرمول هایی جهت استفاده از محدوده های پویا نوشت
استفاده از فرمول index : توسط این فرمول نیز قادر به ایجاد محدوده هایی پویا خواهید بود گرچه در نوع استفاده تفاوت هایی وجود دارد در ضمن این فرمول همانند offset حافظه اشغال نمی کند.7 دلیل برای استفاده از فرمول index (http://forum.exceliran.com/showthread.php?t=2668)
سخن پایانی
آنچه بیان شد کاربردی ساده از این فرمول در ایجاد و دسترسی به محدوده های پویا بود ، ممکن است شما کاربرد های دیگری از این فرمول را در اختیار داشته باشید که می توانید در جهت غنی تر شدن مطلب مطرح کنید
فرمول 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 برمی گرداند
تصویر زیر گویای مطلب است :
http://chakane.persiangig.com/image/excel-offset-formula-examples.png
چرا ازOffset استفاده می کنیم
چرا به طور مستقیم نمی نویسیم A1:C4 ، دلایلی وجود دارد
استفاده از محدوده های داینامیک : عبارتی مانند A1:C4 محدوده ای استاتیک فراهم می آورد در حالی که گاهی نیاز به محدوده ای داینامیک وجود دارد
گاهی آدرس دقیق وجود ندارد ، به عبارت بهتر اطلاعاتی در دست نیست که به کدام محدوده باید ارجاع دهیم ممکن است تعداد ستون یا ردیف و همچنین عرض و ارتفاع اعدادی حاصل از یک محاسبه باشد در این صورت استفاده از فرمول offset مفید خواهد بود.
ایجاد یک Workbook پویا
جهت درک بهتر مطلب یک فایل پویا آماده شده است که با تغییر آرگومان های مشخص شده به خوبی می توان پی به عملکرد این فرمول برد.
http://chakane.persiangig.com/image/Untitled-1.gif
کاربرد عملی فرمول offset
اطلاعاتی از درصد ضایعات یک محصول در اختیار داریم که می بایست متوسط این درصد به صورت هفتگی گزارش گردد ، حالت ایده آل آن است که در یک داشبور مدیریتی آیتمی جهت نمایش اطلاعات 7 روز آخر نمایش داده شود اما چگونه:
با استفاده از فرمول offset امکان پذیر است
=AVERAGE(OFFSET(C3,COUNTA(C3:C300)-7,0,7,1))
برای محاسبه متوسط داده های 7 روز آخر می بایست از کل داده ها ، داده های 7 روز آخر را داشته باشیم ، با استفاده از COUNTA(C3:C300) می توان تعداد داده های وارد شده را شمارش کنیم به عنوان مثال داده های 18 روز ثبت شده است با کم کردن 7 می توان به یک سلول قبل از شروع داده های مورد نیاز دست یافت این عدد نمایشگر تعداد ردیف هایی است که از مبدأ فاصله گرفته می شود ارتفاع محدوده مورد نیاز 7 است که در قسمت Height وارد می شود ، برای درک بهتر به فایل مربوطه رجوع و با Evaluate fomula عملکرد فرمول را بررسی کنید .
http://chakane.persiangig.com/image/2013-10-13%2011-46-03%20%D9%82-%D8%B8.png
محدودیت های Offset
با آنکه این فرمول قادر به بازگرداندن محدوده های پویا است با این حال محدودیت هایی نیز دارد.
این فرمول حافظه را اشغال می کند ، با هر تغییری که در کاربرگ اتفاق افتد این فرمول مجددا کلیه محاسبات را انجام می دهد لذا استفاده از تعداد زیادی از این فرمول کارپوشه را کند خواهد کرد.
با توجه به اینکه در این فرمول از داده های ارجاع شده استفاده می شود لذا در صورت بروز خطا ، هنگام خطایابی ممکن است گمراه کننده باشد .
جایگزین هایی برای Offset
دو جایگزین برای Offset وجود دارد
استفاده از Excel tables : از ورژن 2007 به بعد با استفاده از table ایجاد محدوده های پویا تسهیل شده است و به راحتی می توان فرمول هایی جهت استفاده از محدوده های پویا نوشت
استفاده از فرمول index : توسط این فرمول نیز قادر به ایجاد محدوده هایی پویا خواهید بود گرچه در نوع استفاده تفاوت هایی وجود دارد در ضمن این فرمول همانند offset حافظه اشغال نمی کند.7 دلیل برای استفاده از فرمول index (http://forum.exceliran.com/showthread.php?t=2668)
سخن پایانی
آنچه بیان شد کاربردی ساده از این فرمول در ایجاد و دسترسی به محدوده های پویا بود ، ممکن است شما کاربرد های دیگری از این فرمول را در اختیار داشته باشید که می توانید در جهت غنی تر شدن مطلب مطرح کنید