آموزش تابع OFFSET در اکسل

آموزش تابع OFFSET در اکسل – حرکت به سوی مقصد

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

ساختار تابع OFFSET در اکسل

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

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

از مایکروسافت ممنونم که نام هر آرگومان را متناسب با عملکرد آن انتخاب کرده است 🙂

  1. Reference: آدرس سلولی که به عنوان نقطه شروع (مرجع) در نظر گرفته‌اید، در این قسمت قرار دهید.
  2. Rows: در این قسمت تعداد ردیف‌ها برای حرکت از نقطه شروع به سمت بالا یا پایین را مشخص کنید. اگر عدد این قسمت مثبت باشد حرکت به سمت پایین و اگر این عدد منفی باشد، حرکت به سمت بالا انجام می‌شود.
  3. Cols: در این قسمت تعداد ستون‌ها برای حرکت از نقطه شروع به سمت چپ یا راست را مشخص کنید. اگر عدد این قسمت مثبت باشد حرکت به سمت راست و اگر این عدد منفی باشد حرکت به سمت چپ انجام می‌شود. (در صورتی که صفحه اکسل شما چپ به راست باشد.)
  4. Height: در این قسمت تعیین می‌کنید، بعد از اینکه تابع به سلول موردنظر رسید چند سلول دیگر به سمت بالا یا پایین حرکت کند. (عدد این قسمت حتما باید مثبت باشد.)
  5. Width: در این قسمت تعیین می‌کنید بعد از اینکه تابع به سلول موردنظر رسید چند سلول دیگر به سمت راست یا چپ حرکت کند. (عدد این قسمت حتما باید مثبت باشد.)

نکته: ۳ آرگومان اول ضروری و ۲ آرگومان بعدی اختیاری هستند.

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

مثال ۱: عملکرد تابع OFFSET در اکسل

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

آموزش تابع OFFSET در اکسل

ابتدا یک سلول را به عنوان سلول نتیجه (F2) در نظر می‌گیرم. سپس در نوار فرمول نرم‌افزار روی Fx کلیک می‌کنم (یا اینکه کلیدهای Shift+F3 را فشار می‌دهم.)

آموزش تابع OFFSET در اکسل

 پنجره Inert Function باز می‌شود. از دسته توابع جستجو و مرجع (Lookup & Reference) تابع OFFSET را انتخاب می‌کنم و روی OK کلیک می‌کنم.

آموزش تابع OFFSET در اکسل

پنجره Function Arguments باز می‌شود. در کادر Reference کلیک کرده و سلول B1 را انتخاب می‌کنم. سپس در کادر Rows کلیک کرده و عدد ۳ را تایپ می‌کنم. سپس در کادر Cols کلیک کرده و عدد ۲ را تایپ می‌کنم. در نهایت روی OK کلیک می‌کنم. به فرمول OFFSET برای این مثال دقت کنید.

OFFSET(B1,3,2)

این فرمول به Excel می‌گوید سلول B1 را به عنوان نقطه شروع در نظر بگیرد، سپس ۳ ردیف به سمت پایین (آرگومان ردیف) و  ۲ستون به سمت چپ (آرگومان ستون) حرکت کند. (همانطور که گفتیم وقتی به سمت چپ حرکت می‌کنیم باید عدد منفی وارد کنیم ولی در اینجا چون صفحه اکسل راست به چپ است، عدد را مثبت وارد می‌کنیم.) در نتیجه فرمول OFFSET مقدار سلول D4 (78) را برمی‌گرداند.

آموزش تابع OFFSET در اکسل

نتیجه را در تصویر زیر ببینید:

آموزش تابع OFFSET در اکسل

استفاده از ابزار Data Validation برای ایجاد لیست کشویی

شما می‌توانید با استفاده از لیست‌ کشویی که در آموزش تابع CHOOSE نحوه ایجاد آن را آموزش داده‌ام، خیلی سریع به نتیجه موردنظر برسید. برای این کار در یک سلول (G2) کلیک کرده و سپس از سربرگ Data روی ابزار Data validation کلیک کنید.

آموزش تابع OFFSET در اکسل

پنجره Data Validation باز می‌شود. در قسمت Allow گزینه List را انتخاب کنید و در قسمت Source سلول‌های A2:A5 (4 سطح) را انتخاب کنید. روی OK کلیک کنید.

آموزش تابع OFFSET در اکسل

خوب حالا لیست شما آماده است.

آموزش تابع OFFSET در اکسل

حالا در سلول (G3) کلیک کرده و فرمول زیر را تایپ می‌کنم:

OFFSET(B1,G2,0)

آموزش تابع OFFSET در اکسل

به همین ترتیب در سلول‌ G4 و G5 هم فرمول‌های زیر را تایپ می‌کنم:

OFFSET(C1,G2,0)
OFFSET(D1,G2,0)

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

آموزش تابع OFFSET در اکسل

مثال ۲: ترکیب تابع OFFSET و AVERAGE

حالا من می‌خواهم میانگین نمرات را در هر ۴ سطح برای هر ۳ کلاس به دست آورم. برای این کار از ترکیب دو تابع OFFSET و AVERAGE استفاده می‌کنم. (برای این کار از هر ۵ آرگومان تابع OFFSET استفاده می‌کنم.)فرمول زیر را در سلول نتیجه (E2) تایپ می‌کنم:

AVERAGE(OFFSET(B1,1,0,1,3)

آموزش تابع OFFSET در اکسل

برای اعمال فرمول به سلول‌های دیگر به گوشه سلول E1 بروید و وقتی ماوس به شکل علامت بعلاوه (+) درآمد ماوس را به سمت پایین بکشید.

آموزش تابع OFFSET در اکسل

نتیجه را در تصویر زیر ببینید:

آموزش تابع OFFSET در اکسل

چند نکته درباره تابع OFFSET

  1. اگر آدرس سلولی که در قسمت Reference تایپ کرده‌اید، آدرس یک سلول خالی باشد تابع عدد (۰) را برمی‌گرداند.
  2. اگر محدوده را در قسمت آرگومان‌های Rows و Cols در کاربرگ طوری تعیین کنید که در کاربرگ موجود نباشد تابع خطای REF!# را برمی‌گرداند.

جمع‌بندی

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

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

دیدگاه‌ها (8)

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

  2. با درود و احترام-در اکثر موارد بسیار خوب و روان توضیح داده شده ، ضمن سپاس و قدر دانی از زحمات شما کاش در جوار آموزش اکسل موارد مربوط به برنامه نویسی VBA در اکسل را نیز آموزش داده و یک کانال تلگرامی نیز ایجاد مینمودید .
    با احترام مجدد و سپاس بیکران –

  3. سلام
    اگر یک لیست داشته باشم که موارد تکراری داشته باشد و من بخوام بهم اطلاعات را برگردونه مثلا یک لیست از اسامی دارم به این صورت
    نام شماره فاکتور
    علی ۱۲
    حسن ۱۰
    علی ۲۰
    حسن ۱۶
    علی ۱۵۰
    بعد بخوام تمام شماره فاکتورهای علی رو بهم تو یک سطر برگردونه فرمولی وجود داره که بتونم این کار رو انجام بدم؟

    • سلام. میتونید از فیلتر پیشرفته استفاده کنید. اطلاعاتتون رو انتخاب کنید و سپس از سربرگ data گزینه advanced رو انتخاب کنید. د رپنجره باز شده گزینه اول را انتخاب کنید در کادر criteria range شرط تون رو که در یکی از سلول ها از قبل تایپ کرده اید انتخاب کنید و ok رو بزنید.

  4. درود بر شما تشکر از توضیحاتتون
    سوال داشتم از سرکار عالی
    یک گزارش تولید داریم که در تاریخهای مختلف محصولات مختلفی تولید شده
    من میخوام نمودار داینامیک بکشم که هر محصولی انتخاب شد نمودار میله ایی
    تولید آن محصول بصورت خودکار نمایش داده بشود ممنون میشم راهنمایی کنید. سپاس

دیدگاهتان را بنویسید

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