خانه Excel تابع OFFSET اکسل-جا بجاسازی

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

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

آموزش تابع OFFSETاکسل

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

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

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

ایجاد برنامه های پویا

کمک به ایجاد داشبردهای مدیریتی در اکسل

ایجاد رفرنس (محدوده)

ترکیب با سایر فرمول ها

ساختار تابع 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]:

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

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

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

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

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

آموزش تابع OFFSETاکسل برای جستجو داینامیک پویا

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

[cf-restrict]

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

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

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

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

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

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

آرگومان های اختیاری این تابع چه کاربردی دارد.

چطور می تواند به عنوان یک رفرنس عمل کند.

چطور می تواند با توابع دیگر ترکیب شود.

چطور توانست پویا باشد.

می توانید یک کاربرد دیگر این تابع را در اینجا مشاهده کنید.

[/cf-restrict]

 

5 دیدگاه‌ها

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

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

  2. با سلام
    در مورد نمایش آخرین مقدار موجود در یک سطر اکسل از چه فرمولی می توان استفاده نمود ؟

ارسال یک پاسخ

لطفا دیدگاه خود را وارد کنید!
لطفا نام خود را در اینجا وارد کنید