چگونه از تابع OFFSET در اکسل استفاده کنیم؟
تابع آفست در اکسل یکی از توابع پیشرفته و کاربردی است که برای ایجاد مرجع پویا و تغییرپذیر استفاده میشود. این تابع به کاربر اجازه میدهد با مشخص کردن تعداد ردیفها و ستونها، به سلولی خاص یا محدودهای از سلولها ارجاع دهد.
برای درک بهتر، شکل عمومی تابع OFFSET را در نظر بگیرید که شامل پنج آرگومان است:
- reference یا A: مرجع پایه (سلول یا محدودهای از سلولها) که از آنجا جابهجایی انجام میشود.
- rows یا B: آرگومان دوم تابع offset، تعداد ردیفهایی است که باید از مرجع پایه جابهجا شود و میتواند مثبت یا منفی باشد.
- cols یا C: تعداد ستونهایی که باید از مرجع پایه جابهجا شود (میتواند مثبت یا منفی باشد).
- height (اختیاری) یا D: ارتفاع محدودهی مورد نظر (تعداد ردیفها).
- width (اختیاری) یا E: عرض محدودهی مورد نظر (تعداد ستونها).
نکتهی اول: اگر مقادیر B و C و D و E بهجای یک عدد ثابت، مرجع سلولی باشند، این مقادیر بهطور پویا تغییر میکنند و میتوانند از سلولهای دیگر تأثیر بپذیرند.
نکتهی دوم: اگر مقدار B منفی باشد، جابهجایی به سمت بالا صورت میگیرد، و اگر مقدار C منفی باشد، جابهجایی به سمت چپ انجام میشود.
چطور از تابع OFFSET در اکسل استفاده کنیم؟
شاید توضیحات بالا کمی پیچیده بهنظر برسند؛ اما با دیدن چند مثال، متوجه میشوید که این تابع بهسادگی قابل استفاده است. برای نشان دادن نحوهی عملکرد تابع آفست، از مثال بسیار سادهای استفاده میکنیم. اگر فرمول زیر را در سلول A6 وارد کنید:
فرمول به این شکل تفسیر میشود و عملیات زیر را انجام میدهد:
- نقطهی شروع، سلول A1 است.
- از سلول A1 دو ردیف به پایین میرویم.
- سپس سه ستون به سمت راست حرکت میکنیم.
- ناحیهای بهاندازهی دو ردیف (ارتفاع) و سه ستون (عرض) از D3 انتخاب میشود.
اگر مقادیر D و E را در فرمول خود وارد نمیکردید، نتیجه بهطور پیشفرض فقط به سلول D3 (هومن) ارجاع میداد. دلیلش این است که در اکسل، اگر ارتفاع و عرض را مشخص نکنید، فرض بر این است که نتیجه همان اندازهی مرجع (Reference) خواهد بود.
استفاده از OFFSET در دنیای واقعی
برای درک بهتر موضوع، از مثال دیگری استفاده میکنیم. توجه داشته باشید که تابع offset در excel بهتنهایی کاربرد چندانی ندارد و معمولاً در کنار توابع دیگر استفاده میشود تا به نتایج کاربردیتر برسیم.
در این مثال، جدولی داریم که تعداد واحدهای فروختهشده توسط ۱۰ کارمند را در سه هفته نشان میدهد. حالا با استفاده از تابع OFFSET، سه نوع گزارش ایجاد میکنیم.
گزارش اول (جدول بنفش): تعداد فروش یک کارمند مشخص در یک هفتهی مشخص
هدف: زمانی که شناسهی کارمند و شمارهی هفته را وارد میکنیم، تعداد فروش آن کارمند در هفتهی مشخص نمایش داده شود. در سلول H4 فرمول زیر را وارد کنید:
توجه: از سلول B1 به عنوان اولین ورودی شروع میکنیم؛ زیرا میخواهیم هفته ۱ در اولین سلول سمت راست سلول B1 باشد؛ بنابراین، هر زمان که از تابع OFFSET استفاده میکنید، با دقت دربارهی محل نقطهی شروع خود فکر کنید.
- B1: نقطهی شروع جدول.
- H2: شمارهی کارمند (تعداد ردیفهایی که باید جابهجا شود).
- H3: شمارهی هفته (تعداد ستونهایی که باید جابهجا شود).
- حالا، اگر عدد ۶ را در سلول H2 (شناسه کارمند) و عدد ۲ را در سلول H3 (شماره هفته) وارد کنیم. این فرمول تعداد فروش کارمند شمارهی ۶ (آیلار) را در هفتهی ۲ نمایش میدهد.
با تغییر مقادیر سلولهای H2 و H3، میتوانید اطلاعات هر کارمند و هفتهی دلخواه را دریافت کنید.
گزارش دوم: گزارش کلی کارکنان
در ادامه، میخواهیم گزارشی از کارکنان در جدول آبی ایجاد کنیم؛ بهطوری که با واردکردن شناسهی یک کارمند، اکسل بهطور خودکار نام کارمند را (با استفاده از تابع VLOOKUP)، تعداد کل هفتههای دادههای موجود را (با استفاده از تابع COUNTIF) و مجموع و میانگین واحدهای فروختهشده توسط آن کارمند را با استفاده از توابع SUM و AVERAGE نشان دهد.
برای شروع، شناسهی کارمند را در سلول H7 تایپ میکنیم. بهعنوان مثال، شناسهی کارمند ۳ را وارد میکنیم که به جاوید مربوط است. سپس از تابع VLOOKUP برای بازگرداندن نام جاوید در سلول H8 استفاده میکنیم:
حال از تابع COUNTIF با استفاده از کاراکتر جایگزین (*) برای شمارش تعداد سلولها در ردیف اول که شامل کلمهی هفته بهاضافهی یک عدد باشد، استفاده میکنیم:
اکنون میتوانیم با استفاده از این اطلاعات، مجموع و میانگین واحدهای فروختهشده توسط جاوید را محاسبه کنیم. ابتدا در سلول H10 این فرمول را وارد میکنیم:
که در آن B1 نقطه شروع است، H7 تعداد ردیفها برای جابهجایی به پایین است (شناسه کارکنان)، عدد ۱ تعداد ستونها برای جابهجایی به سمت راست است، عدد ۱ ارتفاع نتیجه است و H9 عرض نتیجه (تعداد کل هفتهها) است.
افزونبراین میتوانیم فرمول مشابهی را در سلول H11 کپی کنیم و فقط SUM را به AVERAGE تغییر بدهیم؛ زیرا از همان ارجاعات استفاده میشود:
حالا میتوانیم مقدار موجود در سلول H7 را تغییر دهیم تا گزارش کارکنان دیگری را از جدول دادهها دریافت کنیم. افزونبراین، اگر هفتهای جدید به دادهها اضافه شود، سلول H9 بهطور خودکار به هفته ۴ تغییر میکند و محاسبات بعدی در سلولهای H10 و H11 نیز بهروزرسانی میشوند.
گزارش سوم: گزارش هفتگی
میخواهیم با واردکردن شمارهی هفته، مجموع و میانگین فروش کل کارمندان در آن هفته نمایش داده شود:
۱- در سلول H14 شماره هفته را وارد کنید (مثلاً: ۱ برای هفته اول).
۲- در سلول H15 مجموع فروش را با فرمول زیر محاسبه کنید:
- B1: نقطهی شروع.
- ۱: تعداد ردیفهای جابهجایی.
- H14: شمارهی هفته (ستون مربوطه).
- ۱۰: ارتفاع (تعداد کارمندان).
- ۱: عرض (یک ستون).
۳- برای میانگین فروش، فرمول زیر را در سلول H16 وارد کنید:
۴- با تغییر مقدار سلول H14، دادههای هفتههای دیگر نمایش داده میشوند.
تابع offset در excel، در ترکیب با توابعی نظیر SUM و AVERAGE و VLOOKUP، یکی از ابزارهای قدرتمند برای کار با دادههای پویا محسوب میشود. نکتهی حائز اهمیت، انتخاب صحیح نقطهی شروع و تنظیم مقادیر ردیف و ستون برای رسیدن به دادههای مطلوب است.
بهنظر شما این راهکارها چهقدر کاربردی هستند؟ شما هم تجربهای مشابه دارید؟ نظرتان را با ما و دیگر خوانندگان در میان بگذارید.