تابع OFFSET در اکسل

مایکروسافت اکسل یکی از پرکاربردترین نرم افزارهای کاربردی در تمام دوران است. صد میلیون نفر در سرتاسر جهان از Microsoft Excel استفاده می کنند. می توانید از اکسل و اکسس برای وارد کردن انواع داده ها و انجام محاسبات مالی، ریاضی یا آماری استفاده کنید.

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

تابع OFFSET چه کاری انجام می دهد؟

تابع OFFSET در اکسل مقدار یک سلول یا محدوده ای (از سلول های مجاور) را برمی گرداند که تعداد مشخصی از سطرها و ستون ها از نقطه مرجع است. این نقطه مرجع، سلول یا نقطه ی شروعی است که به عنوان آرگومان برای تابع ارائه می شود. با شروع از نقطه مرجع، تابع یک نقطه پایانی (سلول یا محدوده پایانی) که مقدار آن برگردانده می شود، کار می کند.

به عنوان مثال، یک مجموعه داده به صورت زیر ارائه می شود:

ستون A شامل 2، 4 و 3 در محدوده A1:A3 است.

ستون B شامل 1، 5 و 6 در محدوده B1:B3 است.

فرمول OFFSET(A1,2,1)= مقدار 6 را برمی گرداند. تابع OFFSET اکسل شروع به محاسبه مرجع سلول از سلول A1 (نقطه شروع) می کند. سپس، دو ردیف به سمت پایین (به سلول A3) و 1 ستون به سمت راست (به سلول B3) حرکت می کند.

نقطه پایان سلول B3 است. بنابراین، تابع OFFSET مقدار سلول B3، یعنی 6 را برمی گرداند.

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

تابع OFFSET بخشی از توابع جستجو و مرجع اکسل است.

نکات و نحوه استفاده

تابع Excel OFFSET یک محدوده دینامیکی ساخته شده با پنج ورودی را برمی گرداند: (1) یک نقطه شروع، (2) یک افست ردیف، (3) یک افست ستون، (4) یک ارتفاع در ردیف، (5) یک عرض در ستون.

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

به عنوان مثال، برای مرجع C5 که از A1 شروع می شود،  مرجع A1، ردیف ها 4 و cols 2 است:

OFFSET(A1,4,2)=

برای مرجع C1:C5 از A1،  مرجع A1، ردیف ها 0،  ستون ها 2، ارتفاع 5 و عرض 1 است:

OFFSET(A1,0,2,5,1)=

توجه: عرض را می توان حذف کرد، زیرا به طور پیش فرض 1 خواهد بود.

معمول است که OFFSET را در تابع دیگری مشاهده کنید که انتظار یک محدوده را دارد. به عنوان مثال، به SUM C1:C5، که از A1 شروع می شود:

SUM(OFFSET(A1,0,2,5,1))=

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

مثال 1:  در صفحه زیر از OFFSET برای برگرداندن مقدار سوم (Mar) در ستون دوم (West) استفاده می کنیم. فرمول در H4 این است:

OFFSET(B3,3,2)=

مثال 2: در صفحه زیر از OFFSET برای برگرداندن آخرین مقدار ( Jun ) در ستون سوم ( North ) استفاده می کنیم. فرمول در H4 این است:

OFFSET(B3,3,2)=