نحوه استفاده از تابع OFFSET در اکسل

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

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

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

تابع OFFSET در اکسل چیست؟

تابع OFFSET یک مرجع را به یک سلول یا محدوده ای از سلول ها برمی گرداند. OFFSET پنج پارامتر دارد و نحو آن به صورت زیر است:

=OFFSET(reference, rows, cols, height, width)

OFFSET در موقعیت سلول مرجع قرار می گیرد و سپس با تعداد ردیف ها و ستون ها از آنجا حرکت می کند و سپس مرجع سلول مقصد را برمی گرداند.

اگر پارامترهای ارتفاع و عرض بیشتر از یک تنظیم شوند، محدوده ای از سلول ها در آن ناحیه برگردانده می شود. ارتفاع و عرض دو پارامتر اختیاری هستند. اگر آنها را خالی بگذارید، OFFSET آنها را به عنوان 1 می خواند و یک مرجع تک سلولی را برمی گرداند.

نحوه استفاده از تابع OFFSET در اکسل

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

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

این ممکن است روی کاغذ پیچیده به نظر برسد، اما درک آن در عمل بسیار ساده تر است. بیایید ببینیم که عملکرد OFFSET با چند مثال چگونه عمل می کند.

1. تابع OFFSET مثال: اشاره به یک سلول واحد

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

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

1. یک سلول را برای وارد کردن فرمول انتخاب کنید. ما از سلول G1 استفاده می کنیم.
2. در نوار فرمول، فرمول زیر را وارد کنید: =OFFSET(A1, 3, 2) این فرمول تابع OFFSET را فراخوانی می کند تا در موقعیت سلول A1 قرار گیرد، سپس 3 سلول به پایین حرکت کند و سپس با 2 سلول به سمت راست حرکت کند. رسیدن به سلول C4.
3. Enter را فشار دهید.

=OFFSET(A1, 3, 2)

وقتی Enter را فشار دادید و فرمول اجرا شد، سلول 5 را برمی‌گرداند. این عدد از سلول C4 است، بنابراین فرمول شما کار کرد! همچنین می توانید از ویژگی ارزیابی برای درک بهتر فرمول های اکسل مانند OFFSET استفاده کنید.

2. تابع OFFSET مثال: اشاره به محدوده ای از سلول ها

با استفاده از دو پارامتر اختیاری، ارتفاع و عرض، می‌توانید محدوده‌ای از سلول‌ها را با تابع OFFSET در اکسل برگردانید. بیایید از جایی که نمونه آخر را رها کردیم ادامه دهیم.

این بار، سلول های C4 را با استفاده از OFFSET در اکسل به D9 برمی گردانیم. درست مانند قبل، ما از A1 به عنوان نقطه مرجع خود استفاده می کنیم.

1. سلولی را که می خواهید فرمول را در آن وارد کنید انتخاب کنید. ما دوباره از سلول G1 استفاده می کنیم.
2. در نوار فرمول، فرمول زیر را وارد کنید: =OFFSET(A1, 3, 2, 6, 2) این فرمول بسیار شبیه فرمول قبلی عمل می کند، با این تفاوت که پس از رسیدن به سلول مقصد (C4) در 6 ردیف قرار می گیرد و 2 ستون به جای سلول هدف به تنهایی. C4 اولین سلول در محدوده جدید خواهد بود.
3. Enter را فشار دهید.

=OFFSET(A1, 3, 2, 6, 2)

اکنون محدوده سلولی C4 تا D9 باید در جایی که در فرمول قرار داده اید ظاهر شود. خروجی یک آرایه خواهد بود.

3. تابع OFFSET مثال: فرمول های مرکب

دو مثال قبل از این یک گرم کردن برای مشاهده نحوه عملکرد OFFSET در اکسل بود. OFFSET معمولاً با سایر توابع اکسل در سناریوهای عملی تو در تو است. بیایید این را با یک مثال تازه نشان دهیم.

در این مثال، ما درآمد یک پروژه جانبی را در طول سال های 2017 تا 2021 داریم. در سمت بالا، سلولی داریم که قرار است کل درآمد را در سال های معین از سال 2021 نشان دهد.

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

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

در این مثال، سال 2021 در سلول H5 است و تعداد سال‌ها برای کل در سلول C1 است. ما از قالب بندی سفارشی در سلول حاوی سال استفاده کرده ایم. مقدار واقعی تعداد سال است و متن Years یک پسوند است. بیایید با ایجاد فرمولی برای محاسبه کل شروع کنیم. ما از تابع SUM در اکسل استفاده می کنیم.

1. سلولی را که می خواهید کل را در آن محاسبه کنید انتخاب کنید.
2. در نوار فرمول، فرمول زیر را وارد کنید: =SUM(OFFSET(H5,0, 0, 1, -C1)) این فرمول از OFFSET و SUM تشکیل شده است. در مرحله اول، OFFSET به سلول H5 می رود. از آنجا، 0 سطر و 0 ستون حرکت می کند، و سپس محدوده ای از سلول ها را که از H5 شروع می شود، برمی گرداند. این انتخاب 1 سلول در ارتفاع و سلول -C1 در عرض خواهد بود. این بدان معنی است که OFFSET محدوده ای را برمی گرداند که از H5 شروع شده و توسط سلول های C1 به سمت چپ حرکت می کند.
3. در مرحله آخر، تابع SUM اعداد موجود در محدوده را با هم جمع کرده و آنها را خروجی می کند.
4. Enter را فشار دهید.

=SUM(OFFSET(H5,0, 0, 1, -C1))

عددی که اکنون به عنوان کل دریافت می کنید به تعداد سال هایی که وارد کرده اید بستگی دارد. اگر تعداد سالها روی 1 تنظیم شده باشد، مجموع آن را فقط برای سال 2021 دریافت خواهید کرد.

توجه داشته باشید که مجموع به‌روزرسانی‌ها بلافاصله پس از تغییر تعداد سال‌ها. اگر تعداد سال ها را به 7 تغییر دهید، تابع SUM کل از 2015 تا 2021 را محاسبه می کند.

صفحات گسترده پویا با OFFSET

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

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

منبع مقاله