اگر کاربرگ اکسل شما شامل محاسباتی است که بر اساس محدوده متغیر سلول ها هستند، از توابع SUM و OFFSET با هم در فرمول SUM OFFSET استفاده کنید تا کار به روز نگه داشتن محاسبات را ساده کنید.
دستورالعمل های این مقاله برای Excel برای Microsoft 365، Excel 2019، Excel 2016، Excel 2013 و Excel 2010 اعمال می شود.
ایجاد یک محدوده پویا با توابع SUM و OFFSET
اگر از محاسبات برای یک دوره زمانی استفاده می کنید که به طور مداوم تغییر می کند - مانند تعیین فروش برای ماه - از تابع OFFSET در اکسل برای تنظیم یک محدوده پویا استفاده کنید که با اضافه شدن ارقام فروش هر روز تغییر می کند.
به خودی خود، تابع SUM معمولاً می‌تواند درج سلول‌های جدید داده را در محدوده جمع‌آوری‌شده جای دهد. یک استثنا زمانی رخ می دهد که داده ها در سلولی که تابع در حال حاضر در آن قرار دارد وارد شود.
در مثال زیر، ارقام فروش جدید برای هر روز در انتهای لیست اضافه شده است، و مجموع را مجبور می کند که به طور مداوم هر بار با اضافه شدن داده های جدید، یک سلول به پایین جابجا شود.
برای دنبال کردن این آموزش، یک کاربرگ خالی اکسل را باز کنید و داده های نمونه را وارد کنید. کاربرگ شما نیازی به قالب بندی مانند نمونه ندارد، اما مطمئن شوید که داده ها را در همان سلول ها وارد کنید.
اگر فقط از تابع SUM برای جمع کردن داده ها استفاده می شود، محدوده سلول های استفاده شده به عنوان آرگومان تابع باید هر بار که داده های جدید اضافه می شود، اصلاح شود.
با استفاده از توابع SUM و OFFSET با هم، محدوده جمع آوری شده پویا می شود و تغییر می کند تا سلول های جدید داده را در خود جای دهد. افزودن سلول‌های جدید داده مشکلی ایجاد نمی‌کند زیرا با اضافه شدن هر سلول جدید، محدوده به تنظیم ادامه می‌دهد.
Syntax and Arguments
در این فرمول، تابع SUM برای مجموع محدوده داده های ارائه شده به عنوان آرگومان استفاده می شود. نقطه شروع برای این محدوده ثابت است و به عنوان مرجع سلول به اولین عددی که با فرمول جمع می شود، مشخص می شود.
تابع OFFSET درون تابع SUM تو در تو قرار دارد و یک نقطه پایانی پویا برای محدوده داده های جمع شده توسط فرمول ایجاد می کند. این کار با تنظیم نقطه پایانی محدوده به یک سلول بالاتر از محل فرمول انجام می شود.
نحو فرمول این است:
=SUM(شروع محدوده:OFFSET(مرجع، ردیف‌ها، ستون‌ها))
آرگومان ها عبارتند از:
- Range Start: نقطه شروع محدوده سلول هایی که توسط تابع SUM جمع می شود. در این مثال، نقطه شروع سلول B2 است.
- Reference: مرجع سلولی مورد نیاز برای محاسبه نقطه پایانی محدوده استفاده می شود. در مثال، آرگومان Reference مرجع سلولی برای فرمول است زیرا محدوده یک سلول بالاتر از فرمول به پایان می رسد.
- Rows: تعداد سطرهای بالا یا پایین آرگومان مرجع مورد استفاده در محاسبه افست مورد نیاز است. این مقدار می تواند مثبت، منفی یا صفر باشد. اگر مکان افست بالای آرگومان مرجع باشد، مقدار آن منفی است. اگر افست زیر باشد، آرگومان Rows مثبت است. اگر افست در همان ردیف قرار گیرد، آرگومان صفر است. در این مثال، افست یک ردیف بالاتر از آرگومان مرجع شروع می شود، بنابراین مقدار آرگومان منفی یک (-1) است.
- Cols: تعداد ستون‌های سمت چپ یا راست آرگومان مرجع که برای محاسبه افست استفاده می‌شود. این مقدار می تواند مثبت، منفی یا صفر باشد. اگر مکان افست در سمت چپ آرگومان Reference باشد، این مقدار منفی است. اگر افست به سمت راست باشد، آرگومان Cols مثبت است. در این مثال، داده های جمع آوری شده در همان ستون فرمول قرار دارند، بنابراین مقدار این آرگومان صفر است.
از فرمول SUM OFFSET برای کل داده های فروش استفاده کنید
این مثال از فرمول SUM OFFSET برای برگرداندن کل ارقام فروش روزانه فهرست شده در ستون B کاربرگ استفاده می کند. در ابتدا، فرمول وارد سلول B6 شد و کل داده های فروش را برای چهار روز جمع آوری کرد.
مرحله بعدی این است که فرمول SUM OFFSET را یک ردیف به پایین ببرید تا جایی برای کل فروش روز پنجم باز شود. این کار با قرار دادن یک ردیف جدید 6، که فرمول را به ردیف 7 منتقل می کند، انجام می شود.
در نتیجه حرکت، اکسل به طور خودکار آرگومان مرجع را به سلول B7 به روز می کند و سلول B6 را به محدوده جمع شده با فرمول اضافه می کند.
- سلول B6 را انتخاب کنید، که مکانی است که در ابتدا نتایج فرمول نمایش داده می شود.
-
برگه Formulas روبان را انتخاب کنید.
Image -
ریاضی و ترفند را انتخاب کنید.
Image -
SUM را انتخاب کنید.
Image - در کادر محاوره ای Function Arguments ، مکان نما را در کادر متنی Number1 قرار دهید.
-
در کاربرگ، سلول B2 را انتخاب کنید تا این مرجع سلول را در کادر محاوره ای وارد کنید. این مکان نقطه پایانی ثابت برای فرمول است.
Image - در کادر محاوره ای Function Arguments ، مکان نما را در کادر متنی Number2 قرار دهید.
-
OFFSET(B6, -1, 0) را وارد کنید. این تابع OFFSET نقطه پایان پویا برای فرمول را تشکیل می دهد.
Image -
OK را برای تکمیل عملکرد و بستن کادر محاوره ای انتخاب کنید. کل در سلول B6 ظاهر می شود.
Image
افزودن اطلاعات فروش روز بعد
برای افزودن اطلاعات فروش روز بعد:
- روی سرصفحه ردیف برای ردیف 6 کلیک راست کنید.
-
Insert را برای درج یک ردیف جدید در کاربرگ انتخاب کنید. فرمول SUM OFFSET یک ردیف به سمت سلول B7 حرکت می کند و ردیف 6 اکنون خالی است.
Image - سلول A6 را انتخاب کنید و عدد 5 را وارد کنید تا نشان دهید کل فروش روز پنجم وارد شده است.
-
سلول B6 را انتخاب کنید، $1458.25 را وارد کنید، سپس Enter را فشار دهید..
Image - Cell B7 به‌روزرسانی‌ها به مجموع جدید 7137.40$.
وقتی سلول B7 را انتخاب می کنید، فرمول به روز شده در نوار فرمول ظاهر می شود.
=SUM(B2:OFFSET(B7, -1, 0))
عملکرد OFFSET دو آرگومان اختیاری دارد: Height و Width که در این مثال استفاده نشدند. این آرگومان ها به تابع OFFSET شکل خروجی را بر حسب تعداد سطرها و ستون ها می گویند.
با حذف این آرگومان ها، تابع به جای آن از ارتفاع و عرض آرگومان مرجع استفاده می کند، که در این مثال یک ردیف ارتفاع و یک ستون عرض دارد.
