از SUM و OFFSET برای پیدا کردن بالغ برای محدوده های پویا داده استفاده کنید
اگر کاراکتر اکسل شامل محاسبات بر اساس محدوده تغییر سلول باشد، با استفاده از توابع SUM و OFFSET با هم در یک فرمول SUM OFFSET، وظیفه نگهداری محاسبات تا تاریخ را ساده می کند.
ایجاد محدوده دینامیکی با عملکردهای SUM و OFFSET
اگر از محاسبات برای یک دوره زمانی استفاده می کنید که به طور مداوم تغییر می کند - مانند فروش کل برای ماه - عملکرد OFFSET به شما امکان می دهد که یک محدوده دینامیکی را تنظیم کنید که به صورت ارقام فروش هر روز اضافه می شود.
به طور خودكار، تابع SUM معمولا می تواند سلولهای جدیدی از اطلاعات را كه در محدوده ی جمع شده قرار می گیرند، جای دهد.
یک استثناء زمانی اتفاق می افتد که داده ها به سلولی که در آن تابع در حال حاضر قرار دارد وارد شود.
در تصویر نمونه ای که همراه با این مقاله است، ارقام فروش جدید برای هر روز به پایین لیست افزوده می شود، که باعث می شود کل در هر بار که اطلاعات جدید اضافه می شود، به طور مداوم حرکت کند.
اگر تابع SUM به صورت خود به منظور جمع آوری داده ها مورد استفاده قرار گیرد، لازم است که طیف وسیعی از سلول های استفاده شده به عنوان استدلال عملکرد را هر بار که داده های جدید اضافه شد تغییر دهید.
اما با استفاده از توابع SUM و OFFSET با هم، محدوده ای که در مجموع پویا می شود. به عبارت دیگر، آن را تغییر می دهد تا سلول های جدید داده را جای دهد. اضافه کردن سلول های جدید از داده ها باعث نمی شود که مشکلات به دلیل ایجاد محدوده به عنوان هر سلول جدید اضافه شده است.
نحو و Arguments
به تصویری که همراه با این مقاله همراه است همراه با این آموزش مراجعه کنید.
در این فرمول، تابع SUM برای جمع آوری داده های ارائه شده به عنوان استدلال آن استفاده می شود. نقطه شروع این محدوده استاتیک است و به عنوان مرجع سلولی به شماره اول که توسط فرمول جمع می شود شناسایی می شود.
تابع OFFSET در داخل تابع SUM تداخل دارد و برای ایجاد یک نقطه پایانی پویا به دامنه ای از داده های جمع شده توسط فرمول استفاده می شود. این کار با تنظیم نقطه پایانی محدوده به یک سلول بالای محل فرمول انجام می شود.
نحو فرمول:
= SUM (محدوده شروع: OFFSET (مرجع، ردیف، ستون ها))محدوده شروع - (لازم) نقطه شروع برای محدوده سلول است که توسط عملکرد SUM به پایان می رسد. در مثال تصویر، این سلول B2 است.
مرجع - (مورد نیاز) مرجع سلولی مورد استفاده برای محاسبه نقطه انتهایی محدوده، ردیفها و ستونهای زیادی را در بر می گیرد. در تصویر مثال، استدلال Reference مرجع سلولی برای فرمول خود است، زیرا ما همیشه می خواهیم محدوده برای پایان دادن به یک سلول بالای فرمول باشد.
ردیف ها - (مورد نیاز) تعداد ردیف های بالا یا پایین پارامتر مرجع مورد استفاده در محاسبه افست. این مقدار می تواند مثبت، منفی یا صفر باشد.
اگر محل جبران بالاتر از استدلال ارجاع باشد ، این مقدار منفی است. اگر زیر است، استدلال ردیف مثبت است. اگر افست در همان ردیف واقع شود، این استدلال صفر است. در این مثال، offset شروع می شود یک ردیف بالاتر از استدلال مرجع ، بنابراین ارزش برای این استدلال منفی است (-1).
Cols - (مورد نیاز) تعداد ستون ها به سمت چپ یا راست از استدلال ارجاع استفاده شده در محاسبه افست. این مقدار می تواند مثبت، منفی یا صفر باشد
اگر موقعیت افست به سمت چپ استدلال مرجع باشد ، این مقدار منفی است. اگر به راست، استدلال Cols مثبت است. در این مثال، داده های جمع شده در ستون همانند فرمول است بنابراین مقدار این استدلال صفر است.
با استفاده از فرمول SUM OFFSET به داده های کل فروش
این مثال از فرمول SUM OFFSET برای بازده کل برای ارقام فروش روزانه که در ستون B برگه استفاده می کنند استفاده می کند.
در ابتدا، فرمول وارد سلول B6 شد و اطلاعات فروش را به مدت چهار روز به فروش رساند.
گام بعدی این است که فرمول SUM OFFSET را در یک ردیف حرکت دهید تا محل فروش روز پنجم را در اختیار شما قرار دهد.
این کار با قرار دادن یک ردیف جدید 6 انجام می شود که فرمول را به سطر 7 منتقل می کند.
به عنوان یک نتیجه از حرکت، اکسل به طور خودکار استدلال ارجاع را به سلول B7 به روز رسانی و سلول B6 را به محدوده جمع شده توسط فرمول اضافه می کند.
وارد کردن فرمول SUM OFFSET
- با کلیک بر روی سلول B6، که محل است که نتایج فرمول در ابتدا نمایش داده می شود.
- روی برگه فرمول نوار منو کلیک کنید.
- ریاضی و ریز را از روبان انتخاب کنید تا لیست کشویی تابع باز شود.
- روی SUM در لیست کلیک کنید تا کادر محاوره ای تابع ظاهر شود.
- در کادر محاوره ای، بر روی Line Number1 کلیک کنید.
- بر روی سلول B2 کلیک کنید تا مرجع سلولی را در جعبه محاوره وارد کنید. این محل نقطه پایانی برای فرمول است.
- در کادر محاوره ای، بر روی Line Number2 کلیک کنید.
- عملکرد OFFSET زیر را وارد کنید: OFFSET (B6، -1،0) برای ایجاد نقطه پایانی پویا برای فرمول.
- برای تکمیل تابع و کلیک بر روی OK را کلیک کنید.
مجموع 5679.15 دلار است در سلول B7 ظاهر می شود.
وقتی روی سلول B3 کلیک میکنید، تابع کامل = SUM (B2: OFFSET (B6، -1،0)) در نوار فرمول بالای برگه ظاهر می شود.
اضافه کردن اطلاعات فروش روز بعدی
برای اضافه کردن اطلاعات فروش روز بعد:
- راست کلیک بر روی ردیف هدر برای ردیف 6 برای باز کردن منوی زمینه.
- در منو، بر روی Insert کلیک کنید تا یک ردیف جدید در صفحه کار گذاشته شود.
- در نتیجه فرمول SUM OFFSET به سلول B7 حرکت می کند و ردیف 6 اکنون خالی است.
- بر روی سلول A6 کلیک کنید.
- شماره 5 را وارد کنید تا نشان دهد که کل فروش برای روز پنجم وارد شده است.
- بر روی سلول B6 کلیک کنید.
- شماره 1458.25 دلار را وارد کنید و کلید Enter را در صفحه کلید فشار دهید.
سلول B7 به مجموع جدیدی از $ 7137.40 اضافه می شود.
هنگامی که شما بر روی سلول B7 کلیک می کنید، فرمول به روز شده = SUM (B2: OFFSET (B7، -1،0)) در نوار فرمول ظاهر می شود.
توجه : عملکرد OFFSET دارای دو آرگومان اختیاری است: ارتفاع و عرض، که در این مثال حذف شدند.
این استدلال می تواند مورد استفاده قرار گیرد تا عملکرد OFFSET را به شکل خروجی بگوید که تعداد زیادی ردیف بالا و ستون های زیادی است.
با حذف این استدلال، تابع به طور پیش فرض از ارتفاع و عرض استدلال ارجاع به جای آن استفاده می کند که در این مثال یک ردیف بالا و یک ستون گسترده است.