تابع Offset و آغاز ایجاد داشبورد

تابع Offset و کاربردهای آن

تابع Offset از دسته توابع جستجو یا Lookup & Reference هست. این تابع کاربرد خیلی زیادی در ایجاد نمودارهای پویا، محدوده های داینامیک داره که همه این موارد در تهیه نرم افزار، گزارشگیری حرفه ای و تهیه داشبوردهای مدیریتی کاربرد خیلی زیادی دارن. از این تابع برای ایجاد یک محدوده استفاده میشه. برای اینکه خوب درک کنیم اول باید آرگومانهاش رو بشناسیم:

Reference: این آرگومان آدرس یک سلول هست. سلول مرجعی که آدرس دادن از این سلول شروع میشه.

Row: این آرگومان عددی است. این عدد به این معنی هست که میگه از سلول مرجع، این تعداد سلول بیا پایین/بالا. اگر عدد مثبت باشه، به سمت پایین و اگر منفی باشه به سمت بالا شیفت میشه.

Column: این آرگومان عددی است. این عدد به این معنی هست که میگه از سلول مرجع، این تعداد سلول برو سمت راست/چپ. اگر عدد مثبت باشه، به سمت راست و اگر منفی باشه به سمت چپ شیفت میشه.

[height]: این آرگومان عددی و اختیاری است. ارتفاع (تعداد سلول) محدوده مورد نظر رو تعیین میکنه. در صورتی که تعیین نشه، ۱ در نظر گرفته میشه.

[width]: این آرگومان عددی و اختیاری است. پهنای (تعداد سلول) محدوده مورد نظر رو تعیین میکنه.در صورتی که تعیین نشه، ۱ در نظر گرفته میشه.

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

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

مثال: میخوایم داده مشخصی رو از بین داده های یک محدوده فراخوانی کنیم.

=OFFSET(E3,3,2)

شکل ۱- فراخوانی داده خاصی از از یک سری داده با استفاده از تابع Offset

از سلول E3 به تعداد ۳ سلول میره پایین و به تعداد ۲ سلول میره راست. تا اینجا رسیدیم به سلول G6. از اونجا محدوده ای به ارتفاع ۱ و پهنای ۱ سلول رو در نظر میگیره (آرگومان های ۴ و ۵ تابع Offset در صورتیکه مشخص نشه، ۱ در نظر گرفته میشه). چون خروجی این تابع یک سلول است، محتویات همون سلول G6 نمایش داده میشه.

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

مثال: میخوایم داده های موجود در ردیف دوم رو با هم جمع بزنیم.

=Offset(E3,1,o,1,4)

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

از سلول E3 به تعداد ۱ سلول میره پایین و به تعداد ۰ سلول میره راست. تا اینجا رسیدیم به سلول E4. از اونجا محدوده ای به ارتفاع ۱ و پهنای ۴ سلول رو در نظر میگیره. یعنی E4:H4. چون خروجی این تابع یک محدوده است، به تنهایی خطا میده. پس با تابع دیگه ای مثل Sum ترکیب میکنیم. خروجی جمع محدوده تعیین شده خواهد بود.

به تصویر زیر نگاه کنید. با تغییر آرگومان های مختلف، خروجی محدوده ای که تعیین میشه رنگی میشه. میتونید این فایل رو دانلود کنید و با تغییر اجزای تابع، نتیجه رو ببینید تا بهتر بتونید هر دو مفهوم این تابع رو درک کنید. سل مرجع با خط کشی قرمز و محدوده در نظر گرفته شده به رنگ زرد نمایش داده میشه.

این فایل تمرین بسیار خوبی از فرمول نویسی در فرمت دهی شرطی هم میتونه باشه. سعی کنید روش کار این فایل رو برای خودتون تحلیل کنید که چطور با تغییر در سلول ها، محدوده های رنگی (متناسب با مفهوم تابع Offset) تغییر میکنه. محدوده سلول A1:B3 سلولهای کمکی هستند که در فرمول نویسی فرمت دهی شرطی استفاده شدن.

دانلود فایل این آموزش

فایلی که تو این آموزش ایجاد شده رو میتونید از لینک زیر دانلود کنید: