تابع OFFSET در اکسل – آموزش کامل استفاده + مثال

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

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

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

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

1=OFFSET(reference,rows,cols,height,width)

در فرمول فوق، تابع OFFSET آدرس سلول هدف را در آرگومان reference  دریافت کرده و در آرگومان rows  به اندازه عددی که وارد می‌شود، سلول پایینی را انتخاب می‌کند. مثلاً اگر سلول B4 انتخاب شود و در آرگومان rows  عدد ۲ را بنویسیم، تابع سلول B6 را در نظر می‌گیرد. به همین ترتیب برای انتخاب خانه‌های بالایی، از اعداد منفی استفاده می‌شود.

آرگومان cols  هم مانند rows  عمل کرده و در محور افقی سلول موردنظر را پیدا می‌کند. اگر صفحه چپ‌چین باشد (حالت پیش‌فرض اکسل)، مقادیر مثبت cols  سلول انتخابی را به سمت راست جا‌به‌جا می‌کنند. در مثال قبل و برای سلول هدف B4، اگر cols برابر ۳ باشد، تابع سلول E4 را انتخاب می‌کند. برای حرکت به سمت چپ هم از اعداد منفی استفاده می‌شود. در نظر داشته باشید که این جابه‌جایی‌ها در حالت راست‌چین برعکس خواهند بود.

پس از مشخص شدن سلول هدف، نوبت به انتخاب بازه می‌رسد. اگر آرگومان‌های height  و width  خالی باشند، تابع مقدار آن‌ها را برابر ۱ در نظر گرفته و همان سلول انتخابی را نمایش می‌دهد. آرگومان height  ارتفاع ستون بازه انتخابی را مشخص کرده و آرگومان width  هم عرض آن را مشخص می‌کند. به‌عنوان مثال در صفحه چپ‌چین،‌ اگر height  برابر ۲ و width  برابر ۳ باشند، تابع OFFSET از محل سلول هدف، بازه‌ای به اندازه ۲ سلول به سمت پایین و ۳ سلول به سمت راست را انتخاب می‌کند.

توضیحات بالا احتمالاً تا حدودی پیچیده به نظر می‌رسند، اما با مشاهده چند مثال و استفاده از تابع OFFSET در اکسل، خواهید دید که این تابع چندان هم پیچیده نیست و کاربری آسانی دارد.

چگونه از تابع OFFSET در اکسل استفاده کنیم ؟

با توجه به توضیحاتی که ارائه شد، در ادامه به نحوه استفاده از تابع OFFSET در اکسل می‌پردازیم.

۱. ارجاع به یک سلول با تابع OFFSET در اکسل

به منظور آشنایی بهتر با تابع OFFSET، ساده‌ترین حالت آن را برای انتخاب یک سلول بررسی می‌کنیم. در تصویر زیر، گروهی از داده‌ها را مشاهده می‌کنید که می‌خواهیم با استفاده از تابع OFFSET، سلول C4 آن‌ها را انتخاب و محتوای آن را در سلول دیگری نمایش دهیم.

زمانی که از تابع OFFSET به‌صورت ساده و بدون ارتفاع و عرض استفاده می‌کنیم، نتیجه آن، محتوای سلول هدف است. با این توضیح در مثال فوق، نتیجه استفاده از تابع مذکور، عدد ۵ خواهد بود که در سلول C4 قرار گرفته است. برای انجام این کار، گام‌های زیر را دنبال کنید:

1. سلولی که می‌خواهید نتیجه در آن نمایش داده شود را انتخاب کنید.
2. در کادر فرمول اکسل، تابع را به شکل زیر وارد کنید:

   1=OFFSET(A1,3,2)

   بر اساس فرمول فوق، تابع از سلول A1 شروع کرده و ۳ خانه به پایین و ۲ خانه به راست می‌رود و به سلول C4 می‌رسد.

3. دکمه Enter کیبورد را بزنید.

با زدن دکمه Enter، تابع فعال شده و در سلولی که انتخاب کرده بودید، عدد ۵ را نشان می‌دهد. با مشاهده این عدد که در سلول C4 قرار داشت، متوجه می‌شویم که تابع به درستی عمل کرده است. همان‌طور که در این مثال دیدید، دو آرگومان height  و width  اختیاری هستند و در صورت عدم تنظیم آن‌ها، تابع تنها یک سلول را به‌عنوان نتیجه نمایش خواهد داد.

۲. ارجاع به یک بازه با تابع OFFSET در اکسل

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

برای درک بهتر، از مثال قبل استفاده کرده و این بار داده‌های بازه C4 تا D9 را با استفاده از تابع OFFSET فرا می‌خوانیم. مانند قبل، نقطه شروع را A1 در نظر گرفته و گام‌های زیر را انجام دهید:

1. سلولی که می‌خواهید نتیجه را نمایش دهد انتخاب می‌کنیم. در این مثال G1 انتخاب شده است.
2. فرمول را به شکل زیر بنویسید:

   1=OFFSET(A1,3,2,6,2)

   این فرمول هم مانند فرمول قبل عمل می‌کند، با این تفاوت که به جای یک سلول، بازه‌ای به ارتفاع ۶ و عرض ۲ سلول را انتخاب کرده که سلول C4 اولین خانه در بازه انتخابی است.

3. دکمه Enter کیبورد را بزنید.

با زدن دکمه Enter، بازه موردنظر به‌صورت آرایه‌ای از اعداد فراخوانی شده و در سلول G1 وارد می‌شود. از آنجا که داده موردنظر بازه‌ای از سلول‌هاست، نرم‌افزار اکسل به‌صورت خودکار داده‌ها را در سلول‌های مجاور G1 توزیع می‌کند.

۳. ترکیب تابع OFFSET در اکسل با سایر توابع

در دو مثال قبل، با کارکرد ابتدایی تابع OFFSET در اکسل آشنا شدید. در دنیای واقعی، تابع OFFSET به تنهایی کاربردی نداشته و اغلب در ترکیب با دیگر توابع برای ایجاد داشبوردهای پویا به کار گرفته می‌شود. برای درک بهتر موضوع، از مثال جدیدی استفاده می‌کنیم.

فیلم آموزش داشبوردهای مدیریتی اکسل Excel در فرادرس

کلیک کنید

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

برای رسیدن به هدف موردنظر، از ترکیب تابع OFFSET و SUM استفاده می‌کنیم. با استفاده از تابع OFFSET برای ارجاع به سلول‌ها به جای ارجاع مستقیم،‌ فرمول نهایی داینامیک یا پویا شده و بازه مورد استفاده در تابع SUM را با توجه به عدد ورودی تعیین می‌کند.

در مثال، سلول H4 نشان دهنده درآمد آخرین سال و سلول C1 نشانگر تعداد سال‌های موردنظر است. برای محاسبه مجموع درآمد‌ها طی سال‌های منتهی به سال آخر، گام‌های زیر را دنبال کنید:

1. سلول موردنظر برای نمایش نتیجه را انتخاب کنید. در این مثال B1 انتخاب شده است.
2. فرمول را به شکل زیر بنویسید:

   1=SUM(OFFSET(H4,0,0,1,-D1))

   در فرمول فوق، گام اول انتخاب سلول H4 به‌عنوان مرجع اصلی است. تابع OFFSET از این نقطه به اندازه ۰ ردیف و ۰ ستون حرکت می‌کند (در واقع همان سلول را انتخاب می‌کند). در گام بعد، بازه‌ای از سلول‌ها که از H4 شروع شده، انتخاب می‌شود. این بازه یک سلول ارتفاع داشته (عدد ۱) و به اندازه مقدار موجود در سلول D1، به چپ می‌رود. دلیل حرکت تابع به سمت چپ در صفحه چپ‌چین، علامت منفی پشت آدرس D1 است.

3. در گام نهایی، تابع SUM اعداد بازه موردنظر را جمع زده و نتیجه را محاسبه می‌کند.
4. دکمه Enter کیبورد را بزنید.

نتیجه‌ای که مشاهده می‌کنید، به تعداد سال‌های انتخابی بستگی دارد. اگر تعداد سال را برابر با ۱ قرار دهید، تنها درآمد سال آخر در فرمول محاسبه خواهد شد. توجه داشته باشید که به محض تغییر تعداد سال‌ها، مجموع درآمدها هم تغییر کرده و در سلول B1 نشان داده می‌شود.

جمع‌بندی

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

سوالات متداول

در انتها به سوالات متداول درباره تابع OFFSET در اکسل می‌پردازیم.

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

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

چرا تابع OFFSET سلول موردنظر را انتخاب نمی‌کند ؟

مسیر فراخوانی سلول‌ها در تابع OFFSET نسبت به راست‌چین و چپ‌چین بودن صفحه حساس بوده و این نکته باید در زمان تعیین مقادیر آرگومان‌ها مد نظر قرار گیرد.