کاربرد تابع offset در اکسل

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

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

قبل از بررسی کاربرد تابع Offset در اکسل بیایید با ساختار و نحوه عملکرد این تابع آشنا شویم و سپس به کاربردهای واقعی آن بپردازیم.

در مطلب فرمول نویسی با نحوه فراخوانی توابع آشنا شدیم. تابع Offset را می توان از گروه توابع Lookup/Reference فراخواند.

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

برای بررسی کاربرد تابع Offset در اکسل باید بدانید ساختار و آرگومان های تابع Offset به صورت زیر است:

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

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

آرگومان اول (reference)

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

آرگومان دوم (rows)

این آرگومان به صورت عددی است و بیانگر تعداد سطرهایی است که می خواهیم از نقطه شروع رو به پایین یا بالا حرکت کند (اگر عدد مثبت باشد به سمت پایین و اگر منفی باشد به سمت بالا حرکت می کند). مثلا اگر عدد 2 را انتخاب کنیم نقطه شروع به اندازه دو سطر به پایین حرکت می کند.

آرگومان سوم (cols)

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

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

آرگومان چهارم ([height])

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

آرگومان پنجم ([width])

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

برای آشنایی بیشتر با آرگومان ها، فرض کنیم می خواهیم سلول B3 را به تابع Offset بدهیم و از آن بخواهیم 3 سطر و 3 ستون به جلو حرکت کند و ارتفاع محدوده 3 و عرض آن 2 باشد. در واقع تابع به صورت زیر باشد:

=OFFSET(B3;3;3;3;2)

اکنون می خواهیم محدوده B2:C3 را به تابع بدهیم و از آن بخواهیم محدوده ای را برای ما مشخص کند که 3 سطر و 3 ستون از این محدوده جلوتر باشد. مانند بالا می توانیم آرگومان چهارم و پنجم را 3 و 2 انتخاب کنیم:

=OFFSET(B2:C3;3;3;3;2)

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

اکنون به بررسی کاربردهای تابع Offset با چند مثال ساده می پردازیم.

فراخوانی داده مورد نظر از بین سایر داده ها

چنانچه ورودی چهارم و پنجم ([height] و [width]) به تابع وارد نشود، تابع Offset، نسبت به مبدا در جهت سطر یا ستون جا به جا می شود و یک سلول را خروجی می دهد.

در داده های مثال زیر که قیمت پایانی چند سهم مختلف در بازار بورس ایران را در دو هفته اول آذر ماه نشان می دهد، می خواهیم با استفاده از تابع Offset قیمت پایانی سهم شپنا را در تاریخ 99/9/9 به دست آوریم.

اگر نقطه شروع (reference) برای تابع Offes را A3 در نظر بگیریم، پس سهم شپنا نسبت به این نقطه در سطر سوم و تاریخ 99/9/9 در ستون هفتم قرار دارد. پس تابع Offset را فرا می خوانیم. در پنجره Function Arguments آرگومان ها را به صورت زیر وارد می کنیم.

در مثال بالا، طبق استفاده انجام شده از تابع Offset، از قیمت پایانی سهم شپنا (سطر سوم) در تاریخ 99/9/9 (ستون هفتم) خروجی می گیریم. از آنجایی که دو آرگومان آخر را مشخص نکردیم، 1 در نظر گرفته می شوند و خروجی این تابع یک سلول است. پس مقدار سلول H6 نمایش داده می شود.

فراخوانی یک محدوده در اکسل

تابع Offset می تواند آدرس یک سلول را بگیرد و به تعدادی که ما مشخص می کنیم محدوده داده برگرداند. یعنی چنانچه ورودی چهارم و پنجم به تابع وارد شود، تابع offset، نسبت به مبدا در جهت سطر یا ستون جا به جا می شود و سپس محدوده ای با ارتفاع height و عرض width را خروجی می دهد.

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

به عنوان مثال می خواهیم از قیمت پایانی سهم های “دسبحان” و “آریان” در هفته اول آذر ماه خروجی بگیریم. تابع Offset را فرا می خوانیم و شروع به وارد کردن آرگومان ها می کنیم.

آرگومان اول: برای آرگومان اول می توانیم به دلخواه هر سلول یا محدوده ای را انتخاب کنیم. به عنوان مثال ما سلول A8 را انتخاب می کنیم.

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

آرگومان سوم: برای تعیین ستون ها، به ستون A نیاز نداریم پس عدد 1 را در ورودی سوم وارد می کنیم تا از ستون بعد از ستون A جستجو را آغاز کند.

آرگومان چهارم: این آرگومان ارتفاع محدوده را مشخص می کند. محدوده مورد نظر ما دو سطر دارد پس عدد 2 را وارد می کنیم.

آرگومان پنجم: این آرگومان عرض محدوده یا تعداد ستون های مورد نظر را مشخص می کند. در هفته اول آذر 5 روز کاری وجود داشته است، پس 5 را انتخاب می کنیم.

پس از فشردن کلید Enter مشاهده می کنیم که با خطای !VALUE# مواجه می شویم. زیرا خروجی تابع یک محدوده است و یک سلول نمی تواند یک محدوده را نشان دهد! با انتخاب تابع و فشردن کلید F9 می توانیم ببینیم که تابع چه مقادیری را در خود حفظ کرده است.

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

ترکیب فرمول Offset با سایر توابع

اگر تابع مثال قبل را با یک تابع مانند Sum ترکیب کنیم، بدون خطای !VALUE# جمع قیمت پایانی های دو سهم “دسبحان” و “آریان” در هفته اول آذر نمایش داده می شود.

برای درک بهتر این موضوع بیایید مثال دیگری را با هم تمرین کنیم. فرض کنید می خواهیم میانگین قیمت پایانی های سهم “وبصادر” را از اول آذر تا آخرین روز کاری به دست آوریم. از تابع Offset می خواهیم محدوده مورد نظر را فرا بخواند، سپس با استفاده از تابع Average، میانگین مقادیر این محدوده را بدست می آوریم.

می توانیم تابع را به صورت زیر بنویسیم:

=AVERAGE(OFFSET(A7;0;1;1;10))

ایجاد محدوده های پویا

مهمترین کاربرد تابع Offset در اکسل ایجاد محدوه های داینامیک یا پویا است تا با اضافه شدن سطر و ستون به محدوده نیازی به تغییر دستی تابع نباشد. اما خروجی به روز شود.

در مثال بالا، روز آخر کاری همیشه 99/09/12 نیست، و در روزهای آتی قیمت پایانی های جدید برای هر سهم ثبت می شود. پس چگونه تابع مثال قبل را پویا کنیم تا با اضافه شدن هر ستون بدون تغییر دستی میانگین جدیدی را دریافت کنیم؟

برای پویا شدن تابع Offset، باید به جای اینکه به آرگومان [width] عدد مشخصی بدهیم، تایع Count را بدهیم تا تمامی ستونهای حاوی عدد را به حساب آورد. تابع به شکل زیر در می آید:

=AVERAGE(OFFSET(A7;0;1;1;COUNT(7:7)))

برای پویا شدن سطرها باید چه تغییری در تابع بوجود بیاوریم؟

اگر تابع Offset داشته باشیم که بخواهیم با اضافه شدن سطرها بدون تغییر دستی، سطر جدید را هم وارد محاسبات بکند باید در آرگومان [height] از تابع Counta استفاده کنیم تا سلول های پر را وارد محاسبات کند.

توجه داشته باشید که اگر جدول داده ها شامل هدر یا سطرهای دیگر حاوی توضیحات یا… باشد، باید این تعداد سطر از تابع counta کم شود.

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

آرگومان ها را به صورت زیر وارد می کنیم:

=AVERAGE(OFFSET(B4;0;0;COUNTA(A:A)-1;COUNT(4:4)))