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

۲۴۵۶ بازدید
آخرین به‌روزرسانی: ۲۴ اردیبهشت ۱۴۰۲
زمان مطالعه: ۵ دقیقه
تابع OFFSET در اکسل – آموزش کامل استفاده + مثال

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

997696

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

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

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 در اکسل

زمانی که از تابع 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 کیبورد را بزنید.
تابع OFFSET در اکسل

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

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

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

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

تابع OFFSET در اکسل

برای رسیدن به هدف موردنظر، از ترکیب تابع 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 کیبورد را بزنید.
تابع OFFSET در اکسل

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

جمع‌بندی

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

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

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

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

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

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

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

بر اساس رای ۱۳ نفر
آیا این مطلب برای شما مفید بود؟
اگر بازخوردی درباره این مطلب دارید یا پرسشی دارید که بدون پاسخ مانده است، آن را از طریق بخش نظرات مطرح کنید.
منابع:
MakeUseOF
نظر شما چیست؟

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *