تابع OFFSET در اکسل

تابع OFFSET جزو توابع جستجوی اکسل است. این تابع که برای ارجاع به یک سلول یا ناحیه از طریق یک نقطه شروع و مشخص کردن سطر و ستون به کار می‌رود.

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

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

آرگومان‌ها یا پارامترهای ورودی

آرگومان اول (reference): نقطه شروع حرکت می‌باشد که به صورت یک سلول است.

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

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

آرگومان چهارم ([height]): تعداد سطرهای ماتریس یا جدول برگردانده‌شده. این آرگومان اختیاری است.

آرگومان پنجم ([width]): تعداد ستون‌های ماتریس یا جدول برگردانده‌شده. این آرگومان اختیاری است.

توجه مهم:

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

تابع Offset سە آرگومانه و Offset پنج آرگومانه

تابع Offset را می‌توان به دو بخش Offset سە آرگومانه و Offset پنج آرگومانه تقسیم کرد.

نمایی از Offset سه آرگومانه به صورت شماتیک

‎=OFFSET(reference, rows, cols)‎

ترجمه فارسی تصویر فوق

اکسل از سلول نارنجی رنگ حرکت کن، به اندازه 3 سطر بیا پایین و 5 ستون برو جلو و اطلاعات سلول سبز رنگ را نمایش بده.

نمایی از Offset پنج آرگومانه به صورت شماتیک

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

ترجمه فارسی تصویر فوق

اکسل از سلول نارنجی رنگ حرکت کن، به اندازه 3 سطر بیا پایین و 5 ستون برو جلو، حالا از این‌جا یک ماتریس یا جدول 3 در 2 جدا کن و برای ما نمایش بده.

توجه مهم: هنگام نوشتن Offset پنج آرگومانه ابتدا باید ناحیه ماتریس را انتخاب کنید و سپس تابع مورد نظر را تایپ کرده و در انتها بعد از نوشتن تابع، به جای Enter از Ctrl+Shift+Enter استفاده کرد.

نتیجه‌گیری

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

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

مثال‌ها

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

توجه:

از تابع Offset در حالت سه آرگومانه استفاده زیادی نمی‌شود چون معمولاً در این حالت استفاده از تابع‌های جایگزین مانند Vlookup، Match، Index و … بجای این تابع راحت‌تر است. ولی از حالت پنج آرگومانه استفاده زیادی در ساخت بازه‌های داینامیک استفاده می‌شود.

بنابراین پیشنهاد می‌کنیم درصورتی که نیاز به بازه‌های داینامیک و متغیر دارید، از این تابع استفاده کنید در غیر این صورت لزومی به استفاده از این تابع نیست.

خطاهای تابع Offset

خطای !REF#: این خطا به معنای اشتباه وارد شدن رفرنس (Reference) می‌باشد و زمانی نمایش داده می‌شود محدوده خروجی تابع offset نامعتبر باشد (از لبه‌ی ورک‌شیت بیرون بزند).
خطای !VALUE#: این خطا زمانی رخ می‌دهد که هر یک از آرگومان‌های rows، cols، [height] یا [width] غیرعددی (متن) باشند.

نکته: اگر آدرس سلولی که در قسمت Refrence تایپ کرده اید آدرس یک سلول خالی باشد تابع عدد 0 را برمی گرداند.

تابع OFFSET از دسته توابع جست و جو یا LOOKUP& REFERNCE است.این تابع کاربرد خیلی زیادی در ایجاد نمودارهای پویا، محدوده های دنیامیک دارد که همه ی این موارد در تهیه ی نرم افزار، گزارشگیری حرفه ایی و تهیه ی داشبوردهای مدیریتی کاربرد خیلی زیادی دارند، از این توابع برای ایجاد یک محدوده استفاده میشود.