تابع OFFSET اکسل – جا بجاسازی

تابع OFFSET اکسل

از تابع offset برای جا به جایی و به نوعی آدرس دهی در اکسل استفاده می شود.

تصویر زیر را در نظر بگیرید:

از شما می خواهند در سلول E1 ،فرمولی بنویسید که از سلول A1، سه ردیف به پایین و دو ردیف به راست جابه جا شوید و محتویات سلول C4 را نمایش دهید.

برای این کار از تابع OFFSET استفاده می کنیم.شاید این کار بیهوده به نظر برسد و کارایی چندانی نداشته باشد.اما این را بدانید که این تابع فواید زیادی دارد که در ادامه با یک مثال کاربرد آن را برای شما توضیح خواهیم داد.

کاربرد و اهداف تابع OFFSET:

1. ایجاد برنامه های پویا
2. کمک به ایجاد داشبردهای مدیریتی در اکسل
3. ایجاد رفرنس (محدوده)
4. ترکیب با سایر فرمول ها

ساختار تابع OFFSET:

=OFFSET(reference, rows, cols, [height], [width])

=OFFSET(پهنا(اختیاری), ارتفاع(اخیاری), تعداد ستون, تعداد ردیف, مرجع)

=OFFSET(A1,3,2)

reference:

ذر این قسمت آدرس سلول یا محدوده مبدا را وارد می کنیم.در مثال فقط سلول را داریم پس فقط A1 را وارد می کنیم.(به عنوان مثال می توانستیم یک محدوده مانند A2:B4 را وارد کنیم)

rows:

تعداد ردیف هایی که قصد داریم از مرجع (reference) به بالا یا پایین جا به جا شویم را وارد می کنیم.می توانید از اعداد مثبت و منفی استفاده کنید.(مثبت برای جهت پایین و منفی برای جهت بالا) در مثال چون قصد داریم 3 ردیف پایین بیاییم، پس عدد 3 را وارد می کنیم.

cols:

تعداد ستون هایی که قصد داریم از مرجع (reference) به راست یا چپ جا یه جا شویم را وارد می کنیم.می توانید از اعداد مثبت و منفی استفاده کنید.(مثبت برای راست و منفی چپ) در مثال چون قصد داریم 2 ردیف پایین بیاییم، پس عدد 2 را وارد می کنیم.

[height]:

این بخش اختیاری بوده و می توانید آن را رها کنید.در مثال ما این بخش را رها کرده ایم.

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

[width]:

این بخش اختیاری بوده و می توانید آن را رها کنید.در مثال ما این بخش را رها کرده ایم.

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

آموزش این تابع به پایان رسید.

مثال کاربردی از تابع Offset:

با یک مثال دیگر می خواهیم کاربرد این تابع را به شما نشان دهیم. در واقع در این مثال به شما یاد خواهیم داد که چطور این تابع را با توابع دیگر ترکیب کنید و یک برنامه پویا (داینامیک)درست کنید.

جدول زیر مربوط به هزینه کرد حسن و حسین می باشد.این دو شخص هزینه روزانه خود را در ستون مربوط به خود وارد می کنند.خواسته این دو شریک از شما این می باشد که فرمولی در سلول A1 بنویسید که همیشه جمع کل هزینه ها را در آخرین تاریخ به شما نشان دهد. به عنوان مثال جمع هزینه ها در تاریخ 1395/05/25 برابر با 8000 تومان می باشد.

برای این کار از فرمول زیر استفاده می کنیم:

=SUM(OFFSET(A1,3,4,1,2))

در واقع در این قسمت به تابع گفتیم که وقتی به سلول مورد نظر رسید، چه محدوده ای را انتخاب کند.

اما این فرمول هنوز داینامیک نمی باشد.برای اینکه پویا باشد باید از فرمول زیر استفاده کنیم.

=SUM(OFFSET(A1,COUNTA(E:E)-1,4,1,2))

اکنون اگر در ردیف بعدی هزینه ها را وارد کنید، تابع به طور پویا جمع هزینه های آخرین روز را به شما نشان خواهد داد.

همان طور که می بیند در این مثال نشان دادیم که :

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

می توانید یک کاربرد دیگر تابع Offset را در شمارنده اعداد خودکار در اکسل مشاهده کنید.