تابع OFFSET (بخش اول)
02 بهمن 1400
دقیقه
تابع OFFSET برای ایجاد ارجاع به محدودهای که شامل تعداد مشخصی از ردیف و ستون دور از یک سلول یا محدوده سلولها است استفاده میشود. اساساً برای ایجاد ارجاعی به محدودهای از سلولها میتوان یک سلول مرجع ایجاد کرد. پس از آن میبایست تعداد ردیف و یا ستونی را که از محدوده سلول مرجع دور میباشد و میخواهید از آن برای ایجاد محدوده خود استفاده کنید را مشخص نمایید.
آخرین بهروزرسانی: 27 دی 1401
در سری مقاله های آموزش اکسل که در فصل گذشته به بررسی توابع SUMIF، AVERAGEIF، SUMIFS، AVERAGEIFS، MAXIFS و MINIFS پرداختیم، در این فصل به تابع OFFSET می پردازیم.
تابع OFFSET برای ایجاد ارجاع به محدودهای که شامل تعداد مشخصی از ردیف و ستون دور از یک سلول یا محدوده سلولها است استفاده میشود. اساساً برای ایجاد ارجاعی به محدودهای از سلولها میتوان یک سلول مرجع ایجاد کرد. پس از آن میبایست تعداد ردیف و یا ستونی را که از محدوده سلول مرجع دور میباشد و میخواهید از آن برای ایجاد محدوده خود استفاده کنید را مشخص نمایید. مثلاً با استفاده از تابع Offset، میتوان ارجاعی به محدوده سلولی که حاوی دو ردیف و سه ستون است و با دو ستون در سمت راست و یک ردیف در بالای سلول موردنظر آغاز میشود ایجاد نمود. میتوان تعداد مشخص ردیفها و ستونهایی را که از سلول مرجع تغییر مکان میدهید را با استفاده از سایر توابع اکسل محاسبه نمایید.
سؤالهای پاسخ داده شده در این فصل:
- چگونه میتوان ارجاعی به محدودهای از سلولها ایجاد کرد که عدد مشخصی ردیف و ستون از سلول یا محدوده ای دیگر از سلولها باشد؟
- چگونه میتوان عملیات جستجویی تنظیم کرد که بهجای آنکه بر اساس آخرین ستون سمت چپ محدوده یک جدول باشد بر اساس اولین ستون سمت راست آن انجام شود؟
- اغلب اوقات فهرست فروش محصولات نرمافزاری را بر اساس کشور/منطقه دانلود میکنیم. میخواهیم درآمدهای حاصل از کشور ایران به همراه هزینهها و محصولات فروخته شده را دنبال کنیم اما اطلاعات مربوط به ایران همیشه در یک مکان از کاربرگ قرار نگرفته است. آیا میتوان فرمولی ایجاد کرد که همواره تنها درآمدها، هزینهها و تعداد کالاهای فروخته شده مرتبط با ایران را برگزیند؟
- هر داروی تهیه شده توسط شرکتها معمولاً میبایست از سه مرحله تولید گذر کند. فهرستی از هزینههای ماهیانه هر دارو به همراه تعداد ماههای هر مرحله از تولید دارو نیز در اختیار ما قرار گرفته است. آیا میتوان فرمولی ایجاد کرد که برای هر دارو هزینه کل در هر مرحله از تولید را محاسبه نماید؟
- فروشگاه فیلمهای ویدئوی را اداره میکنم. حسابدار در یک کاربرگ فهرستی از نام فیلمها و تعداد کپیهای آن را در انبار درج کرده است. بدبختانه او اطلاعات هر فیلم را در یک سلول قراردادهاست. چگونه میتوانم اطلاعات تعداد کپیهای هر فیلم را در انبار جدا کرده و در سلول جداگانهای قرار دهم؟
- ویژگی Evaluate Formula (ارزیابی فرمول) در اکسل چگونه کار میکند؟
- چگونه میتوان محدوده نامهایی تنظیم کرد که به شکل خودکار دادههای جدید را نیز شامل شود؟
- هر ماه نمودار فروش کالاهای ماهیانه شرکت را تنظیم میکنم. اطلاعات فروش ماهیانه کالاها برای تهیه این نمودار را هر ماه دانلود میکنم. میخواهم نمودار به شکل خودکار بهروز شود. آیا راه آسانی برای دستیابی به این هدف وجود دارد؟
- چگونه میتوان فرمولی نوشت که همیشه آخرین عدد یک ستون را نشان دهد؟
دستور زبان تابع OFFSET عبارت است از OFFSET(reference,rows moved,columns moved,height,width) که آرگومانهای زیر در آن مورداستفاده قرار میگیرد.
- Reference سلول یا محدودهای از سلولها است که جابهجایی از آنجا آغاز میشود. چنانچه محدودهای از سلولها را انتخاب کنید، این سلولها بایستی همجوار باشند. آرگومان Rows moved (ردیفهای جابهجاشده) عدد ردیفهای دور از سلول مرجع یا محدودهای است که میخواهید ردیف مرجع از آنجا آغاز شود (سلولهای بالایی سمت چپ محدوده جابهجایی یا Offset range). عدد منفی ردیفها شما را به بالای سلول مرجع منتقل کرده و عدد مثبت ردیفها شما را به پایین آن منتقل مینمایند. مثلاً اگر Reference یا سلول مرجع برابر C5 و Rows moved برابر 1- باشد، به ردیف4 منتقل خواهید شد. اگر عدد Rows moved برابر با 1+ باشد به ردیف 6 منتقل خواهید شد. چنانچه Rows moved برابر با صفر باشد در همان ردیف 5 باقی میمانید.
- Columns moved (ستونهای منتقل شده) عدد ستونهای دور از سلول مرجع با محدودهای است که میخواهید محدوده مرجع از آنجا آغاز شود. عدد منفی ستونها شما را به سمت چپ سلول مرجع و عدد مثبت ستونها شما را به سمت راست آن منتقل مینماید. مثلاً اگر سلول مرجع C5 باشد و Columns moved برابر با 1- باشد به ستون B منتقل خواهید شد. چنانچه Columns moved برابر با 1+ باشد به ستون D منتقل خواهید شد. اگر Columns moved برابر با صفر باشد در همان ستون C باقی خواهید ماند.
- آرگومانهای Height و Width آرگومانهای اختیاری هستند که عدد ردیفها و ستونها در محدوده جابهجایی را به شما میدهند. چنانچه height و Width حذف شوند، تابع OFFSET محدودهای برای هریک از مقادیر height و Width ایجاد میکند که با طول و عرض سلول یا محدوده انتخاب شده برابر خواهد بود.
پاسخ به سؤالهای این فصل
چگونه میتوان ارجاعی به محدودهای از سلولها ایجاد کرد که عدد مشخصی از ردیفها و ستونها از سلول یا محدوده دیگری از سلولها باشد؟
فایلی به نام Offsetexample.xlsx در تصویر 1-22 نشاندادهشده است که مثالی از عملکرد تابع OFFSET را نشان میدهد.
تصویر 1-22 بهکارگیری تابع OFFSET
بهعنوانمثال در سلول B10 فرمول نشاندادهشده در سلول A10 یعنی =SUM(OFFSET(B7,–1,1,2,1)) را وارد میکنیم. این فرمول کار خود را از سلول B7 آغاز میکند. یک ردیف به بالا و یک ستون به سمت راست میرود که ما را به سلول C6 میرساند. حالا فرمول محدودهای شامل دو ردیف و یک ستون را انتخاب میکند و در نهایت محدوده C6:C7 را به ما میدهد. تابع SUM عددهای این محدوده را جمع میزند که میشود 6+2=8. مثال دیگری که در تصویر 1-22 نشاندادهشده به همین طریق عمل میکند. در بخشهای بعدی نشان خواهیم داد که چگونه تابع OFFSET را به مسئلههای دیگری اعمال کنیم که توسط دانشجویان پیشینم که در شرکتهای بزرگ آمریکایی کار میکنند برایم فرستاده اند.
چگونه میتوان عملیات جستجویی تنظیم کرد که بهجای آنکه بر اساس آخرین ستون سمت چپ محدوده یک جدول باشد بر اساس اولین ستون سمت راست آن انجام شود؟
در تصویر 2-22 (فایلی را به نام Lefthandlookup.xlsx مشاهده کنید) فهرستی از اعضای سالهای 2002 تا 2003 تیم بسکتبال آمریکایی دالاس ماوریکز و درصد گلهای هریک را قرار دادهایم. اگر از ما بخواهند بازیکنی را با درصد گل زده خاصی پیدا کنیم بهسادگی میتوانیم این مسئله را با تابع VLOOKUP حل کنیم. اما آنچه میخواهیم انجام دهیم یک جستجوی سمت چپی است که تنها شامل یافتن درصد گلهای یک بازیگر با استفاده از نامش میباشد. تابع VLOOKUP نمیتواند جستجوی سمت چپی را انجام دهد اما این کار بهسادگی امکانپذیر است، آن هم وقتیکه شما توابع Match و OFFSET را باهم ترکیب کنید.
تصویر 2-22 اجرای یک جستجوی سمت چپی با استفاده از توابع Match و Offset
در ابتدا نام بازیکن را در سلول D7 وارد میکنیم. بعد در فرمول OFFSET، از سلولB7 بهعنوان سلول مرجع استفاده میکنیم (سرستون درصد گلهای زده شده) برای پیداکردن درصد گلهای زده شده بازیکن، نیاز داریم به ردیف زیر ردیف شماره 7 که نام بازیکن در آنجا ظاهر شده برویم.
این وظیفه تابع MATCH میباشد. بخش تابع MATCH از فرمول OFFSET(B7,MATCH(D7,$C$8:$C$22,0),0) که در سلول E7 وارد شده به سمت ردیف پایین حاوی نام بازیگر خاص رفته و بعد ستون صفر را پویش میکند. ازآنجاییکه مرجع ما شامل یک سلول است، حذف آرگومانهای height و width از تابع OFFSET به ما اطمینان میدهد که محدوده داده شده توسط این فرمول نیز تنها یک سلول خواهد بود. به همین شکل درصد گلهای زده شده بازیکن خاص را مشخص میکنیم.
اغلب اوقات فهرست فروش محصولات نرمافزاری را بر اساس کشور/منطقه دانلود میکنیم. میخواهیم درآمدهای حاصل کشور ایران به همراه هزینهها و محصولات فروخته شده را دنبال کنیم اما اطلاعات مربوط به ایران همیشه در یک مکان از کاربرگ قرار نگرفته است. آیا میتوان فرمولی ایجاد کرد که همواره تنها درآمدها، هزینهها و تعداد کالاهای فروخته شده مرتبط با ایران را برگزیند؟
فایلی به نام Asiansales.xlsx (تصویر 3-22 را ببینید) حاوی دادههای مربوط به کالاهای فروخته شده، درآمد فروش و هزینههای متغیرهای نرمافزارهای فروخته شده به کشورها/ مناطق مختلف در آسیا و خاورمیانه است. هر ماه وقتیکه اطلاعات ماهیانه گزارشهای مالی دانلود میشود، موقعیت مکانی هریک از کشورها/ مناطق در کاربرگ تغییر میکند، بنابراین به فرمولهایی نیاز است که همواره تعداد کالاها، درآمد و هزینههای متغیر درست (مربوطه به کشور یا ناحیهای خاص) را به ما بدهد.
تصویر 3-22 استفاده از تابع OFFSET در محاسبات دادههایی که در کاربرگ در موقعیت مکانی یکسانی نیستند.
هر داروی تهیه شده توسط شرکتها معمولاً میبایست از سه مرحله تولید گذر کند. فهرستی از هزینههای ماهیانه هر دارو به همراه تعداد ماههای هر مرحله از تولید دارو نیز در اختیار ما قرار گرفته است. آیا میتوان فرمولی ایجاد کرد که برای هر دارو هزینه کل در هر مرحله از تولید را محاسبه نماید؟
فایلی به نام Offsetcost.xlsx حاوی هزینههای ماهیانه برای تولید پنج دارو و برای هر دارو تعداد ماههای موردنیاز برای تکمیل فاز تولید میباشد. مجموعهای از این دادهها در تصویر 4-22 نمایشدادهشده است.
تصویر 4-22 استفاده از تابع OFFSET برای محاسبه هزینه تولید در فازهای 1 الی 3
آن است که برای هر دارو هزینه کلی که در طول هر دوره تولید صرف شده را مشخص نماییم. در سلولهای D4:D6 هزینه کل تولید فازهای یک تا سه را برای داروی شماره 1 محاسبه کردهایم. هزینههای فاز یک داروی شماره یک را با استفاده از سلول مرجعD10 به همراه آرگومانهای rows moved و columns moved برابر با صفر محاسبه میکنیم. با قراردادن آرگومان height را برابر با تعداد ماههای فاز یک و آرگومان Width برابر عدد 1 تمامی هزینههای فاز یک به دست میآوریم. ابتدا هزینههای فاز یک داروی شماره یک را در سلول D4 با فرمول =SUM(OFFSET(D10,0,0,D1,1)) محاسبه میکنیم. سپس در سلول D5 هزینههای کل فاز دو داروی شماره یک را با فرمول =SUM(OFFSET(D10,D1,0,D2,1)) محاسبه میکنیم. توجه کنید که کار را با سلول مرجع D10(اولین ماه از هزینهها) آغاز کردیم و سپس به عدد ردیفهای پایینتر برابر با طول فاز دوم رفتیم.
این کار ما را به ابتدای فاز دوم میرساند. برابر کردن آرگومان height با مقدار سلول D2 به ما اطمینان میبخشد که تمامی هزینههای فاز دوم را مشمول کردهایم. بالاخره در سلول D6 با استفاده از فرمول =SUM(OFFSET(D10,D1+D2,0,D3,1)) هزینههای تولیدداروی شماره یک را به دست میآوریم. در این فرمول کار را از اولین ماه فروش آغاز کرده و سپس به عدد ردیفهای برابر با زمان کل لازم برای فاز یک و دو رفتهایم. این کار ما را به ابتدای فاز سه میرساند که در سلول D6 کل تعداد ردیفهای لازم برای دستیابی به هزینههای فاز سه را جمع زدهایم؛ بنابراین با کپیکردن فرمولها از محدوده D4:D6 به محدوده E4:H6 میتوان جمع کل هزینه فازهای یک تا سه را برای داروهای 2 تا پنج را نیز محاسبه نمود. مثلاً برای داروی شماره 2 کل هزینههای فاز 1 برابر با 313 دلار، کل هزینههای فاز 2 برابر با 789 و کل هزینههای فاز 3 برابر با 876 دلار است.
من یک مغازه فروش فیلمهای ویدئویی را اداره میکنم. حسابدار در یک کاربرگ فهرستی از نام فیلمها و تعداد کپیهای آن را در انبار درج کرده است. بدبختانه او اطلاعات هر فیلم را در یک سلول قرارداده است. چگونه میتوانم اطلاعات تعداد کپیهای هر فیلم را در انبار جدا کرده و در سلول جداگانهای قرار دهم؟
کاربرگ Movies در فایلی بنام Movies.xlsx نشاندادهشده در تصویر 5-22 حاوی نام هریک از فیلمها و تعداد کپیهای موجود میباشد.
تصویر 5-22 مثال Movie با استفاده از تابعOFFSET
قصد داریم تعداد کپیهای موجود از فیلم این فروشگاه ویدئویی را به سلول جداگانهای استخراج کنیم. اگر تعداد کپیها در سمت چپ نام فیلم کپی شده باشند، این کار بسیار آسان خواهد بود. میتوان تابع FIND را برای پیداکردن اولین فضای (space) بکار برده و بعد با استفاده از تابع LEFT تمامی دادههای سمت چپ را به اولین فضا منتقل کرد. (فصل 6 توابع متنی و ابزار Flash Fill را برای مباحث مربوط به چگونگی استفاده از توابع LEFT و FIND و همچنین سایر توابعی که میتوان با متن بکار برد مشاهده کنید) بدبختانه این تکنیک وقتی تعداد کپیها در سمت راست اولین فضا درج شده باشند به کار نمیآید. مثلاً برای فیلمی با نام یککلمهای، تعداد کپیها در اولین فضای سمت راست درج شدهاند اما برای فیلمی با عنوان چهار کلمهای تعداد کپیها در سمت راست چهارمین فضا درج شدهاند.
یک راه برای حل این مسئله آن است که بر سربرگ Data در ریبون نوارابزار کلیک کرده و بعد در گروه Data Tools بر روی دکمه Text To Columns کلیک کرده تا راهنمای Convert Text To Columnsباز شود (دستورالعملهای زیر را مشاهده کنید) تا هر کلمه در عناوین فیلمها و تعداد کپیها را در ستونهای جداگانهای قرارداد. میتوان از تابع COUNTA برای شمارش تعداد کل کلمات یک عنوان هر فیلم که شامل تعداد آیتمها بهعنوان کلمه میشود استفاده کرد. میتوانید از تابع OFFSET برای پیداکردن تعداد آیتمها استفاده کنید.
برای شروع تعداد مشخصی از ستونها در سمت راست دادهها قرار دهید تا با این کار بتوانید هر کلمه در عناوین فیلم و تعداد آیتمها را در ستون مشخصی استخراج کنید. برای این کار شش ستون را انتخاب میکنیم (عنوانRaiders of the Lost Ark به شش ستون نیاز دارد) همانطور که در تصویر 5-22 میبینید ستونهای D الی I را اضافه میکنیم. پس از آن محدوده سلولهای C2: C12 را انتخاب کرده و در سربرگ Data tab روی Text To Columns کلیک میکنیم. در کادر راهنمای Convert Text To columns گزینه Delimited (محدود شده) را انتخاب کرده و روی Next کلیک میکنیم (تا به بخش بعد منتقل شویم) در بخش Delimiters گزینه Tab را از حالت انتخاب خارج کرده و گزینه Space را انتخاب میکنیم تا از کاراکتر Space بهعنوان کاراکتر محدودکننده استفاده کنیم. حالا روی دکمه Nextکلیک میکنیم و سلول D2 را بهعنوان سلول مقصد وارد کرده و دکمه Finish را کلیک میکنیم. میتوان نتایج این فرایند را در ستونهای D الی I در تصویر 5-22 دید.
حالا تعداد کلمات (شامل اعداد نیز میشود) را در هر سلول فیلمها با کپیکردن فرمول =COUNTA(D2:I2) از سلول A2 به محدوده A3:A12 شمارش میکنیم) عدد آیتمها را نیز مثل یک کلمه شمارش میکنیم (نتیجه در تصویر 5-21 نشاندادهشده است.
بالاخره از سلول B2 فرمول OFFSET(C2,0,A2) را در محدوده B3:B12 کپی میکنیم. با این کار میتوانیم تعداد کپیهای هر فیلم در انبار را پیدا کنیم. این فرمول کار خود را در سلول مرجع حاوی عنوان فیلم آغاز میکند و به شماره ستونهای برابر با تعداد کلمات در سلول عنوان میرود. ازآنجاکه سلول مرجع تنها حاوی یک سلول است، میتوان آرگومانهای height و width تابع OFFSET را حذف کرد تا این تابع تنها از سلول حاوی آخرین “کلمه” (یعنی تعداد کپیها) سلول عنوان استفاده کند.
همانطور که در تصویر 6-22 و کاربرگFlash Fill نشاندادهشده، میتوان از خاصیت Flash Fill نیز برای استخراج رقم کپیهای هر فیلم استفاده کرد. در اینجا در سلول D2 عدد 40 و در سلول D3 عدد 12 را وارد میکنیم. پس از فشاردادن کلیدهای Ctrl+E تعداد کپیهای فیلمهای دیگر نیز به شکلی سحرآمیز ظاهر میشوند! توجه کنید که اگر میخواهید این مورد را در کاربرگ Flash Fill امتحان کنید (و پیغام خطا دریافت نکنید) بهتر است محتویات سلولهای D4:D12 را پاک کنید.
تصویر 6-22 استفاده از ابزار Flash Fill جهت استخراج تعداد کپیهای هر فیلم