کاربرد تابع offset در اکسل: آموزش تصویری گام به گام

رتبه: 0 ار 0 رای sssss

نویسنده: تیم تولید محتوا زمان مطالعه 9 دقیقه

تابع Offset (آفست) با گرفتن یک سلول مرجع و تعداد سطر و ستون از سلول مرجع به تعداد سطرها و ستون ها حرکت می کند تا مقدار ذخیره شده در سطر یا ستون یا ماتریس انتخاب شده را برگرداند. با تعیین سلول مرجع، این سلول به نقطه ابتدایی برای شروع حرکت تبدیل می شود و سپس تابع Offset به تعداد سطرها و ستون های ورودی شروع به شمارش سطر و ستون می کند.

تابع OFFSET از نقطه مرجع به اندازه سطرها و ستون های معین جابه جا می شود و سپس از این نقطه یک سلول یا یک محدوده را بر می گرداند، می توانید تعداد سطرها و تعداد ستون های برگشتی را تعیین کنید.

ویدیوی آموزشی

اگر ویدیوی بالا کافی نبود به خواندن ادامه دهید:

ساختار تابع:

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

در تصویر زیر فرمول OFFSET در اکسل آمده است:

تابع OFFSET شامل مقادیر ورودی زیر است:

reference یا مرجع: این وروردی جایی است که به عنوان مبنای آفست و نقطه شروع در نظر گرفته می شود. می تواند آدرس یک سلول یا محدوده ای از سلول های مجاور باشد و در غیر اینصورت صورت، تابع Offset مقدار !VALUE# را به معنای خطا باز می گرداند.
rows یا سطرها: تعداد سطرها از نقطه مرجع است. اگر یک عدد مثبت باشد به اندازه مقدار وارد شده به سمت سطرهای پایینی جابجا می شود و اگر یک عدد منفی باشد به سمت سطرهای بالا حرکت می کند.
columns یا ستون ها: تعداد ستون ها از نقطه مرجع است. مشابه مفهوم rows است و اگر از یک عدد مثبت استفاده شده باشد، به اندازه مقدار وارد شده به سمت ستون های سمت راست حرکت می کند و اگر عدد منفی باشد به سمت ستون های سمت چپ حرکت می کند.
height یا ارتفاع: این ورودی اختیاری است و باید یک عدد مثبت باشد. عددی است که تعداد سطرها یا ارتفاع یک محدوده را از نقطه بازگشتی تعیین می کند.
width یا عرض: این هم یک ورودی اختیاری است و باید یک عدد مثبت باشد. عددی است که تعداد ستون ها یا عرض یک محدوده را از نقطه بازگشتی تعیین می کند.

نکات:

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

چگونه می توان از تابع OFFSET در اکسل استفاده کرد؟

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

در ادامه شیوه استفاده از تابع OFFSET در اکسل را به طور کامل روی مثال های مختلف توضیح خواهیم داد.

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

استفاده از تابع offset در اکسل

فرمول سلول E11:

=OFFSET(B10,2,0)

سلول B10 به عنوان مرجع وارد شده است.
مقدار ۲ برای تعداد سطر یعنی تابع به اندازه ۲ سطر از سلول B10 رو به پایین حرکت کند و در سطر ۱۲ قرار می گیرد.
چون مقدار ستون ۰ است، تابع روی ستون ها حرکت نمی کند و در ستون B باقی می ماند.
در نهایت، تابع روی سلول B12 قرار می گیرد و چون آرگومان های ارتفاع و عرض تعیین نشده اند، تابع محتوای سلول B12 را برمی گرداند (Product B).

فرمول سلول E12:

=OFFSET(B10,2,1)

سلول B10 به عنوان مرجع وارد شده است.
مقدار ۲ برای تعداد سطر یعنی تابع به اندازه ۲ از سلول B10 رو به پایین حرکت کند و در سطر ۱۲ قرار می گیرد.
چون مقدار ستون ۱ تعیین شده، برخلاف فرمول قبلی، تابع روی ستون به سمت راست به اندازه ۱ ستون حرکت می کند و در ستون C قرار می گیرد.
در نهایت، تابع روی سلول C12 قرار می گیرد و چون آرگومان های ارتفاع و عرض تعیین نشده اند، تابع محتوای همان سلول C12 را برمی گرداند (۲۵,۰۰۰٫۰۰).

مثال ۲: تابع OFFSET با برگشت یک محدوده

تابع OFFSET با برگشت یک محدوده

فرمول سلول E37:

=OFFSET(B34,2,1,6,1)

سلول B34 به عنوان مرجع وارد شده است.
برای آرگومان سطر، عدد ۲ وارد شده است. بنابراین تابع به اندازه ۲ سطر از سلول B34 رو به پایین حرکت می کند و روی سطر ۳۶ قرار می گیرد.
برای آرگومان ستون نیز عدد ۱ تعیین شده است. بنابراین تابع به اندازه ۱ ستون از سلول B34 به سمت راست حرکت می کند و در ستون C قرار می گیرد.
پس از جابه جایی روی سطرها و ستون ها روی سلول C36 قرار می گیرد. در اینجا دو آرگومان ارتفاع و عرض نیز استفاده شده است.
برای آرگومان ارتفاع، عدد ۶ وارد شده است، بنابراین با شروع از سلول C36، ۶ سطر رو به پایین تا سلول C41 در نظر گرفته می شود.
برای آرگومان عرض نیز عدد ۱ وارد شده است، یعنی روی سطرهای مرحله قبل ۱ ستون با شروع از سلول C22 انتخاب شود، عدد ۱ تغییری در ستون ایجاد نمی کند و در همان ستون سلول مرجع باقی می ماند.
چون در اینجا دو آرگومان ارتفاع و عرض نیز تعیین شده پس خروجی تابع یک محدوده به اندازه ۶ سطر و ۱ ستون (داده سلول های C36 تا C41) است، بنابراین OFFSET یک تابع آرایه ای است. ولی این محدوده در یک سلول قابل نمایش نیست و خطا رخ می دهد. در این حالت باید تابع OFFSET را با توابعی ترکیب کرد که آرگومان ورودی آنها یک محدوده است، مانند: SUM، AVERAGE و …

مثال ۳: تابعOFFSET همراه با تابع SUM

تابعOFFSET همراه با تابع SUM

فرمول سلول E23:

=SUM(OFFSET(B20,2,1,6,1))

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

سلول B20 به عنوان مرجع وارد شده است.
برای آرگومان سطر، عدد ۲ وارد شده است. بنابراین تابع به اندازه ۲ سطر از سلول B20 رو به پایین حرکت می کند و در سطر ۲۲ قرار می گیرد.
برای آرگومان ستون نیز عدد ۱ تعیین شده است. بنابراین تابع به اندازه ۱ ستون به سمت راست حرکت می کند و در ستون C قرار می گیرد.
پس از جابه جایی روی سطرها و ستون ها، روی سلول C22 قرار می گیرد.
برای آرگومان ارتفاع، عدد ۶ وارد شده است، بنابراین با شروع از سلول C22، ۶ سطر رو به پایین تا سلول C27 در نظر گرفته می شود.
برای آرگومان عرض نیز عدد ۱ وارد شده است، یعنی روی سطرهای مرحله قبل ۱ ستون با شروع از سلول C22 انتخاب شود، عدد ۱ تغییری در ستون ایجاد نمی کند و در همان ستون سلول مرجع باقی می ماند.
نتیجه چند مقدار خواهد بود (داده سلول های C22 تا C27). به همین دلیل از تابع SUM قبل از تابع OFFSET برای محاسبه مجموع مقادیر استفاده شده است، این تابع مقدار مجموع آرایه برگشتی از تابع OFFSET را برمی گرداند. در اینجا مجموع مقادیر سلول های C22 تا C27 محاسبه شده است (در سلول F23).

نکاتی برای یاد آوری

تابع OFFSET در اکسل فقط یک آدرس را برمی گرداند و در واقع هیچ سلول یا محدوده ای از سلول ها را جابجا نمی کند.
اگر تابع OFFSET محدوده ای از سلول ها را برگرداند، نقطه مبدا سطرها و ستون ها همیشه اولین سلول سمت چپ در بالای محدوده بازگشتی است.
مرجع باید باید یا یک سلول یا یک محدوده از سلول ها باشد و در غیر این صورت فرمول خطای VALUE!# را برمی گرداند.
اگر تعداد سطرها و ستون های مشخص شده از محدوده صفحه بیرون بزنند، فرمول Offset خطای #REF! را بازمی گرداند!
تابع OFFSET را می توان در توابع دیگر اکسل با ورودی آدرس یک سلول یا محدوده ای از سلول ها استفاده کرد.

به عنوان مثال اگر از فرمول

= OFFSET (A1, 3, 1, 1, 3)

به تنهایی استفاده کنیم، خطای #VALUE! مقدار بازگشتی از تابع خواهد بود. چونکه تابع یک محدوده (۱ ردیف، ۳ ستون) برمی گرداند و محدوده نمی تواند در یک سلول جای گیرد. با این حال می توانیم مجموع مقادیر محدوده را با تابع SUM مانند مثال زیر به دست آوریم:

=SUM (OFFSET (A1, 3, 1, 1, 3))

=SUM (OFFSET (A1, 3, 1, 1, 3))

فرمول Offset از سلول A1 با عنوان سلول مرجع شروع می کند و سپس ۳ سطر به سمت پایین و ۱ ستون به سمت راست حرکت می کند و روی سلول B4 قرار می گیرد. سلول B4 به عنوان نقطه مبدا محدوده در نظر گرفته می شود و محدوده بازگشتی از این سلول به ارتفاع یک سطر و عرض سه ستون محاسبه می شود، یعنی تابع Offset در نهایت محدوده سلول های (B4 تا D4 (B4:D4 را برمی گرداند.

همانطور که می بینیم مجموع مقادیر این سلول ها (۳۶ + ۶۳ + ۸۲) برابر با ۱۸۱ در سلول G2 است که پس از اعمال فرمول های sum و Offset به دست آمده است.

فرمول های sum

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

در مثال بالا دیدیم که عملکرد تابع OFFSET چگونه است، ممکنه سؤالاتی داشته باشیم مثلا اینکه چرا باید با استفاده از فرمول OFFSET آن را کمی پیچیده کنیم، چرا به راحتی از یک ارجاع مستقیم مثل sum یا B4:D4 استفاده نمی کنیم؟

تابع OFFSET در اکسل برای موارد زیر بسیار مناسب است:

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

ایجاد محدوده پویا: محدوده هایی شبیه مثال بالا (B1:C4) ایستا و ثابت هستند یعنی اینکه که همیشه به یک محدوده معین یا ثابت اشاره می کنند. اما در بعضی موارد، کار با محدوده های پویا وظایف را آسان تر می کند. به ویژه وقتی که با تغییر داده ها کار می کنیم. به عنوان مثال ممکنه صفحه گسترده ای داشته باشیم که هر روز، هر هفته و هر ماه به آن اضافه می شود و تغییر می کند، در این صورت تابع OFFSET کمک خواهد کرد.

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

فهمیدیم که چگونه و چه زمانی می توان از تابع OFFSET در اکسل استفاده کرد. هر فرمولی در اکسل محدودیت ها و جایگزین های خود را دارد.

موارد زیر محدودیت های مهم تابع OFFSET در اکسل را نشان می دهند:

تابع Resource-hungry: این بدان معناست که هر وقت در داده منبع تغییری ایجاد شود، فرمول برای کل مجموعه دوباره محاسبه می شود. اگر صفحه گسترده کوچک باشد، تاثیر زیادی در آن نخواهد داشت اما اگر چندتا صفحه گسترده وجود داشته باشد، ممکنه اکسل برای محاسبه مجدد یک مدت زمانی نیاز داشته باشد.

مشکل در به روز رسانی: همانطور که می دانیم منابع برگشتی از تابع Offset پویا یا در حال تغییر هستند و ممکنه فرمول بزرگی داشته باشیم که امکان داره برای تصحیح یا ویرایش های لازم به مشکل بخورد.

گزینه های جایگزین تابع OFFSET در اکسل

تابع INDEX: تابع INDEX در اکسل را می توان برای ساخت محدوده پویای مرجع استفاده کرد. دقیقاً مشابه تابع OFFSET نیست. اما مثل OFFSET چندان بی ثبات نیست، بنابراین سرعت اکسل را پایین نمی آورد که یکی از محدودیت های تابع OFFSET است.

تابع INDIRECT: همچنین می توانیم از تابع INDIRECT برای ایجاد دامنه پویای مرجع از منابع زیادی مانند مقادیر سلول، متن و محدوده های نامگذاری شده استفاده کنیم.

دانلود فیلم های آموزش صفر تا صد اکسل +جزوه