در این آموزش قصد دارم یکی از اسرارآمیزترین ساکنان دنیای اکسل، یعنی تابع OFFSET را به شما معرفی کنم. راستش را بخواهید من فکر میکنم این تابع مثل یک راهنمای نقشه گنج است! تابع OFFSET در اکسل در دسته توابع جستجو و مرجع (Lookup & Reference) قرار دارد. این تابع یک سلول را به عنوان نقطه شروع در نظر میگیرد. سپس بر اساس مقادیر مشخص شده در آرگومانها به سمت مقصد موردنظر حرکت میکند و مقداری را برمیگرداند.
ساختار تابع OFFSET در اکسل
عملکرد تابع OFFSET ممکن است کمی پیچیده به نظر بیاید! پس ابتدا توضیحات فنی کوتاهی درباره آرگومانهای این تابع میدهم و تلاش میکنم به سادهترین شکل آن را توضیح دهم. ساختار تابع OFFSET به شکل زیر است:
OFFSET(reference, rows, cols, [height], [width])
از مایکروسافت ممنونم که نام هر آرگومان را متناسب با عملکرد آن انتخاب کرده است 🙂
- Reference: آدرس سلولی که به عنوان نقطه شروع (مرجع) در نظر گرفتهاید، در این قسمت قرار دهید.
- Rows: در این قسمت تعداد ردیفها برای حرکت از نقطه شروع به سمت بالا یا پایین را مشخص کنید. اگر عدد این قسمت مثبت باشد حرکت به سمت پایین و اگر این عدد منفی باشد، حرکت به سمت بالا انجام میشود.
- Cols: در این قسمت تعداد ستونها برای حرکت از نقطه شروع به سمت چپ یا راست را مشخص کنید. اگر عدد این قسمت مثبت باشد حرکت به سمت راست و اگر این عدد منفی باشد حرکت به سمت چپ انجام میشود. (در صورتی که صفحه اکسل شما چپ به راست باشد.)
- Height: در این قسمت تعیین میکنید، بعد از اینکه تابع به سلول موردنظر رسید چند سلول دیگر به سمت بالا یا پایین حرکت کند. (عدد این قسمت حتما باید مثبت باشد.)
- Width: در این قسمت تعیین میکنید بعد از اینکه تابع به سلول موردنظر رسید چند سلول دیگر به سمت راست یا چپ حرکت کند. (عدد این قسمت حتما باید مثبت باشد.)
نکته: ۳ آرگومان اول ضروری و ۲ آرگومان بعدی اختیاری هستند.
حالا به مثال سادهای از عملکرد تابع OFFSET توجه کنید.
مثال ۱: عملکرد تابع OFFSET در اکسل
من در یک فایل اکسل نمرات دانشجویان ۳ کلاس را در ۴ سطح طبقهبندی کردهام. حالا میخواهم ببینم نمره سطح ۳ کلاس ۳ چند است؟ برای این کار از فرمول OFFSET استفاده میکنم. اطلاعات موجود در فایل اکسل من را در تصویر زیر میبینید.
ابتدا یک سلول را به عنوان سلول نتیجه (F2) در نظر میگیرم. سپس در نوار فرمول نرمافزار روی Fx کلیک میکنم (یا اینکه کلیدهای Shift+F3 را فشار میدهم.)
پنجره Inert Function باز میشود. از دسته توابع جستجو و مرجع (Lookup & Reference) تابع OFFSET را انتخاب میکنم و روی OK کلیک میکنم.
پنجره Function Arguments باز میشود. در کادر Reference کلیک کرده و سلول B1 را انتخاب میکنم. سپس در کادر Rows کلیک کرده و عدد ۳ را تایپ میکنم. سپس در کادر Cols کلیک کرده و عدد ۲ را تایپ میکنم. در نهایت روی OK کلیک میکنم. به فرمول OFFSET برای این مثال دقت کنید.
OFFSET(B1,3,2)
این فرمول به Excel میگوید سلول B1 را به عنوان نقطه شروع در نظر بگیرد، سپس ۳ ردیف به سمت پایین (آرگومان ردیف) و ۲ستون به سمت چپ (آرگومان ستون) حرکت کند. (همانطور که گفتیم وقتی به سمت چپ حرکت میکنیم باید عدد منفی وارد کنیم ولی در اینجا چون صفحه اکسل راست به چپ است، عدد را مثبت وارد میکنیم.) در نتیجه فرمول OFFSET مقدار سلول D4 (78) را برمیگرداند.
نتیجه را در تصویر زیر ببینید:
استفاده از ابزار Data Validation برای ایجاد لیست کشویی
شما میتوانید با استفاده از لیست کشویی که در آموزش تابع CHOOSE نحوه ایجاد آن را آموزش دادهام، خیلی سریع به نتیجه موردنظر برسید. برای این کار در یک سلول (G2) کلیک کرده و سپس از سربرگ Data روی ابزار Data validation کلیک کنید.
پنجره Data Validation باز میشود. در قسمت Allow گزینه List را انتخاب کنید و در قسمت Source سلولهای A2:A5 (4 سطح) را انتخاب کنید. روی OK کلیک کنید.
خوب حالا لیست شما آماده است.
حالا در سلول (G3) کلیک کرده و فرمول زیر را تایپ میکنم:
OFFSET(B1,G2,0)
به همین ترتیب در سلول G4 و G5 هم فرمولهای زیر را تایپ میکنم:
OFFSET(C1,G2,0) OFFSET(D1,G2,0)
با انتخاب سطح از لیست کشویی نمرات ۳ کلاس نمایش داده میشود. نتیجه را در تصویر زیر ببینید:
مثال ۲: ترکیب تابع OFFSET و AVERAGE
حالا من میخواهم میانگین نمرات را در هر ۴ سطح برای هر ۳ کلاس به دست آورم. برای این کار از ترکیب دو تابع OFFSET و AVERAGE استفاده میکنم. (برای این کار از هر ۵ آرگومان تابع OFFSET استفاده میکنم.)فرمول زیر را در سلول نتیجه (E2) تایپ میکنم:
AVERAGE(OFFSET(B1,1,0,1,3)
برای اعمال فرمول به سلولهای دیگر به گوشه سلول E1 بروید و وقتی ماوس به شکل علامت بعلاوه (+) درآمد ماوس را به سمت پایین بکشید.
نتیجه را در تصویر زیر ببینید:
چند نکته درباره تابع OFFSET
- اگر آدرس سلولی که در قسمت Reference تایپ کردهاید، آدرس یک سلول خالی باشد تابع عدد (۰) را برمیگرداند.
- اگر محدوده را در قسمت آرگومانهای Rows و Cols در کاربرگ طوری تعیین کنید که در کاربرگ موجود نباشد تابع خطای REF!# را برمیگرداند.
جمعبندی
در این آموزش تابع OFFSET را توضیح دادیم و ترکیب این تابع با توابع دیگر را گفتیم. تابع OFFSET کاربردهای پیشرفتهتری هم دارد. مثلا برای ایجاد دامنههای پویا و همچنین کمک به ایجاد داشبوردهای مدیریتی و غیره. امیدوارم این آموزش برای شما مفید باشد.
سلام
ممنون از آموزش عالی و زحمتی که کشیدید.
مطالب بسیار روان و گیرا بیان شده است.
انتقال مفاهیم بخوبی صورت میگیرد.
بازم ممنون .
سلام. خواهش می کنم. ممنونم از همراهی تون. موفق باشید.
ممنون بخاطر آموزش بسیار روان و کامل
خواهش می کنم.ممنونم از شما به خاطر همراهی و دقت نظرتان.
با درود و احترام-در اکثر موارد بسیار خوب و روان توضیح داده شده ، ضمن سپاس و قدر دانی از زحمات شما کاش در جوار آموزش اکسل موارد مربوط به برنامه نویسی VBA در اکسل را نیز آموزش داده و یک کانال تلگرامی نیز ایجاد مینمودید .
با احترام مجدد و سپاس بیکران –
سلام
اگر یک لیست داشته باشم که موارد تکراری داشته باشد و من بخوام بهم اطلاعات را برگردونه مثلا یک لیست از اسامی دارم به این صورت
نام شماره فاکتور
علی ۱۲
حسن ۱۰
علی ۲۰
حسن ۱۶
علی ۱۵۰
بعد بخوام تمام شماره فاکتورهای علی رو بهم تو یک سطر برگردونه فرمولی وجود داره که بتونم این کار رو انجام بدم؟
سلام. میتونید از فیلتر پیشرفته استفاده کنید. اطلاعاتتون رو انتخاب کنید و سپس از سربرگ data گزینه advanced رو انتخاب کنید. د رپنجره باز شده گزینه اول را انتخاب کنید در کادر criteria range شرط تون رو که در یکی از سلول ها از قبل تایپ کرده اید انتخاب کنید و ok رو بزنید.
درود بر شما تشکر از توضیحاتتون
سوال داشتم از سرکار عالی
یک گزارش تولید داریم که در تاریخهای مختلف محصولات مختلفی تولید شده
من میخوام نمودار داینامیک بکشم که هر محصولی انتخاب شد نمودار میله ایی
تولید آن محصول بصورت خودکار نمایش داده بشود ممنون میشم راهنمایی کنید. سپاس