تابع offset در اکسل

توجه:برای استفاده و مطلع شدن از سایر آموزش های رایگان و غیر رایگان سایت در موارد زیر عضویت یابید:


تابع offset در اکسل


استفاده از تابع offset در اکسل

تابع offset در اکسل چیست؟

تابع offset در لغت به معنای انحراف است. از این تابع در تهیه داشبورد های مدیریتی بسیار استفاده می شود. استفاده اصلی این تابع در تهیه داشبورد های مدیریتی در Name Manager و validation data است.

همانطور که از تصاویر موجود در این صفحه مشخص است، از تابع offset همچون یک marker (نشانگر) برای حرکت در داخل یک محدوده داده به منظور خروجی گرفتن یک سلول یا یک محدوده داده استفاده می شود. قبلا در رابطه با توابع index ، match و vlookup ، Hlookup که به نوعی همین کار رو برای ما انجام می دهند به تفصیل صحبت شد.

ورودی های تابع offset:

تابع offset در اکسل دارای ۵ ورودی به صوزت زیر است:

reference: مبدا حرکت می باشد و یک سلول بایستی باشد.

rows: تعداد سطری که نسبت به مبدا به سمت پایین یا بالا می توان حرکت کرد. (عدد مثبت به سمت پایین و عدد منفی به سمت بالا)

cols: تعداد ستونی که نسبت به مبدا به سمت جلو یا عقب می توان حرکت کرد. (عدد مثبت به سمت جلو و عدد منفی به سمت عقب)

[height]: ارتفاع محدودی خروجی می باشد. این ورودی اختیاری است.

[width]: عرض محدوده خروجی است. این ورودی اختیاری است.

اکسل داشبوردسازی اکسل حرفه ای کد نویسی

تابع offset در اکسل را می توان به ۲ صورت مورد استفاده قرار داد:

***استفاده اول از تابع offset:

چنانچه ورودی چهارم و پنجم(دو ورودی آخر) به تابع وارد نشود. در این نوع استفاده از تابع offset، نسبت به مبدا در جهت سطر یا ستون جا به جا می شویم و یک سلول را همچون مثال زیر خروجی میگیرم:

تابع offset چیست

در مثال بالا، طبق استفاده انجام شده از تابع offset، از فروش سال ۱۳۹۶ (سطر سوم) در ماه تیر(ستون چهارم) خروجی میگیریم و خروجی تابع سلول F5 و عدد ۱۰۸۲ خواهد بود.

***استفاده دوم از تابع offset:

چنانچه ورودی چهارم و پنجم(دو ورودی آخر) به تابع وارد شود. در این نوع استفاده از تابع offset، نسبت به مبدا در جهت سطر یا ستون جا به جا می شویم و سپس محدوده ای با ارتفاع height و عرض width خروجی میگیرم. (توجه داشته باشید که مبدا جدیدی که با وارد کردن rows و cols به آن می رسیم، گوشه محدوده خروجی قرار می گیرد.)

فرض کنید قصد داریم از  ماه تیر تا مهر سطر قرمز رنگ در تصویر زیر خروجی بگیریم:

تابع offset چیست 1

ورودی اول یا مبدا حرکت تابع offset را سلول B10 انتخاب می کنیم. چون قصد داریم از  ماه تیر تا مهر سطر قرمز رنگ را خروجی بگیریم، پس نیازی نیست که در جهت سطر به سمت پایین حرکت داشته باشیم، پس ورودی rows برابر مقدار ۰ می باشد. در جهت ستون بایستی ۴ ستون به جلو برویم تا به تیر ماه برسیم، پس ورودی cols را ۴ وارد میکنیم. اکنون از اینجا، مستعطیلی به ارتفاع ۱ سلول و عرض ۴ سلول را نیاز داریم، تا ماه تیر تا ماه مهر را شامل شود.

خب به همین سادگی از محدوده خروجی گرفتیم. اما با زدن اینتر و تایید تابع نوشته شده، با خطای Value# روبه رو می شویم. به نظر شما آیا می توان محدوده ای از سلول ها را در یک سلول که تابع offset را در آن نوشته ایم، خروجی بگیریم؟! مسلما خیر.

خب راه حل چیه؟؟؟

بایستی به ذهن بسپارید که هر زمان خواستین استفاده دوم تابع offset را به کار بگیرید، یعنی خروجی گرفتن یک محدوده با وارد کردن ورودی های چهارم و پنجم، یک راه حل وجود داره: (البته تکنیک های دیگه ای هم هست که در پست های دیگری به اونها اشاره می کنم.)

  • استفاده از توابع جمع پذیر همچون SUM قبل offset که محدوده خروجی گرفته شده را جمع کند. همچون شکل زیر:

تابع offset چیست 2

در این قسمت طی یک فیلم، به آموزش دقیق تابع offset می پردازیم. اگر مفید بود یا درخواست آموزشی مشابه دارین، زیر همین پست کامنت بگذارید.

توصیه میکنم که حتما بعد از تماشای آموزش تابع Offset، آموزش کنترل اسناد و مدارک مهندسی پروژه ها که مثال کاربردی از تابع offset را شامل می شود، مشاهده بفرمایید.


*****سایر آموزش های رایگان اکسل*****
ردیفنام آموزش
۱کنترل اسناد و مدارک و اسناد مهندسی در اکسل
۲نمودار جریان نقدینگی
۳کادر پیغام در اکسل
۴گیج سرعت حرفه ای
۵چک باکس و چک لیست پیشرفت
۶افزایش سرعت اجرای کد
۷آدرس دهی نسبی و مطلق در اکسل
۸نمودار داینامیک خطی
۹ماکرونویسی
۱۰توابع Vlookup و Hlookup
۱۱ توابع داشبورد ساز Index و Match
۱۲تکنیک کاهش زمان پروژه در اکسل
۱۳توابع اکسل در محیط برنامه نویسی
۱۴مدیریت خطا در محیط برنامه نویسی
۱۵ایجاد لیست کشویی وابسته
۱۶ترسیم نمودار و شخصی سازی لیبل ها
۱۷دستور InputBox در محیط برنامه نویسی
۱۸تابع IF در اکسل
۱۹فرمول نویسی IF در IF
۲۰weight value و weight factor و پیشرفت پروژه در اکسل
۲۱نکات کاربردی در مورد فرمت ساعت در اکسل
مشاهده سایر آموزش های رایگان اکسل…(کلیک کنید)

 

 

مطالعه بیشتر