همه چیز در مورد تابع Offset در اکسل

مهمترین کار تابع OFFSET ایجاد رنج متغیر و رنج داده های دلخواه می باشد!!

تابع آفست Offset چیست و چه کار می کند؟ اگر بخوام با بیان ساده و خلاصه بگم تابع افست Offset چکار میکنه باید بگم که آدرس یک سلول رو از شما میگیره و به تعدادی که شما مشخص میکنید محدوده داده به شما بر میگردونه .

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

به تصویر زیر دقت کنید ، آرگومانهای فرمول افست  Offset رو نشون میده :

 

Reference

آدرس سلول مبدأ رو از شما می خواد.

Rows

میگه از چند تا سطر بالاتر یا پائینتر از آدرسی که تو آرگومان اول دادی من محاسبه کنم ؟مثلا اگر تو آرگومان اول B5 رو انتخاب کنبد و در آرگومان دوم عدد 2 رو وارد کنید نقطه شروع شما رو B7 در نظر میگیره و اگر -2 قرار بدید دوتا سطر به عقب بر می گرده و B3 رو ملاک قرار میده . میتونید این قسمت رو 0 بذارید . که شما به اکسل میگید که همون آدرس که من انتخاب کردم رو مبدأ قرار بده .

Cols

میگه از چند تا ستون جلوتر یا عقب تر از آدرسی که تو آرگومان اول دادی من محاسبه کنم ؟مثلا اگر تو آرگومان اول B5 رو انتخاب کنبد و در آرگومان دوم عدد 0 رو وارد کنید و آرگومان سوم رو 1 بذارید نقطه شروع شما به C5 تغییر میکنه یعنی اکسل از C5 شروع به محاسبه میکنه اگر هم عدد منفی قرار بدین مثلا -1 خب یه ستون میره عقب و از A5 محاسبه میکنه .نکته:سه تا آرگومان اول اجباری هست و شما حتما باید مفدار داخلشون ارد کنید که میتونید آرگومان دوم و سوم رو معادل 0 قرار بدید.

Height

میگه ارتفاع داده ای که میخوای بگردونم چقدر باشه؟ یعنی اگر ما آرگومان اول رو A1 انتخاب کنیم و آرگومان دوم و سوم رو 0 قرار بدیم و آرگومان چهارم که ارتفاع هست رو عدد 3 در نظر بگیریم . خب اکسل رنج A1:A3 رو در نظر میگیره و مقدارهاش رو برمیکردونه .

Width

مشابه آرگومان چهارم هست ولی در عرض محاسبه میکنه . مثلا اگر مثل فرمول زیر آرگومان اول A1 باشه و سه تا آرگومان بعدی 0 باشن و آرگومان پنجم رو 3 قرار بدیم . خب اکسس رنج A1:C1 رو در نظر میگیره
OFFSET(A1,0,0,0,3)

اگر با توضیحات بالا متوجه شدید که تابع Offset چکار میکنه که باید به خودتون خیلی امیدوار باشین چون میدونم یه مقدار گنگ هست این تابع و هنوز هم کاربرد مشخص نیست. اگر هنوز هم متوجه نشدید که باز هم حق دارید ، ادامه مطلب رو بخونید.

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

شما خیلی راحت میتونید توی A2 کلیک کنید و موس رو درگ کنید و تا B3 رو انتخاب کنید .

حالا محدوده ای که انتخاب شده توی نوار وضعیت اکسل داره جمع و میانگین و غیره رو نشون میده .

حالا همین انتخاب رو شما میتونید با تابع Offset انجام بدین با فرمول زیر که البته تو تصویر هم مشخص هست .

همونطور که تو F4 میبینید نتیجه فرمول نوشته شده تو F2 میشه تمام اطلاعات موجود توی رنج A2:B4

=Offset(A2,0,0,3,2)

حالا این رنج به چه درد میخوره ؟

شما میتونید با استفاده از یه تابع مثل Sum قبل از فرمول بالا جمع اعداد موجود تو رنج A2:B4 رو به دست بیارید که میشه 945.

=ُSum(Offset(A2,0,0,3,2))

حالا بیاید یه مثال کاربردی را بررسی کنیم .

تابع Offset هم مثل تابع Index قدرت واقعی خودش رو زمانی نشون میده که با توابع دیگه ترکیب بشه .

فرض کنید تصویر زیر رو به عنوان دیتای نمونه تو اکسل دارید.

 

توی ستون A کد کالا ها و ستون B موجودی اون کالا نمایش داده شده .

شما میخواید وقتی تو D2 یکی از کدها رو انتخاب کردید. موجودی مربوط به اون تو سلول E2 نمایش داده بشه که با فرمول Vlookup میتونید انجام بدید .

حالا چالش اینجاست که شما رو مجبور میکنه از Offset استفاده کنید .

ببینید D2 الان توسط Validation Data به لیست انتخابی تغییر کرده .

اگر ما بخوایم با وارد کردن دیتا تو ستون A  لیست پائین افتادنی هم به صورت خودکار آپدیت بشه چکار باید بکنبم ؟

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

نکته :

وظیفه تابع Count  این هست که عدد مربوط به تعداد سطر یا همون ارتفاع رو تو تابع offset محاسبه کنید .

پس فرمول به شکل زیر میشه :

=Offset(A2,0,0,COUNT(A2:A50),1)

حالا تنها کاری که باید بکنیم اینه که از تابع بالا تو قسمت تعریف نامها در اکسل استفاده کنیم و یک نام ایجاد کنیم و اون نام رو تو قسمت Validation Data استفاده کنیم .

حالا یه سوال مطرح میکنم ، شما روش فکر کنید .

تصویر زیر رو به عنوان نمونه اطلاعات در نظر بگیرید.

 

توی تصویر بالا سلولهای سبز رنگ باید چه فرمولی داشته باشن که بتونن نتیجه رو برگردونن؟