بیل فرمولی دارد که با استفاده از تابع SUM یک ستون را در ردیف های 2 تا 15 جمع می کند. اگر در ردیف های 3 تا 15 ردیفی اضافه کند، فرمول به درستی تنظیم می شود. اگر او یک ردیف در بالا اضافه کند (درست بالای ردیف 2)، فرمول به هیچ وجه برای گنجاندن ردیف درج شده تنظیم نمی شود. بیل به این فکر می کند که آیا راهی برای تنظیم صحیح و خودکار فرمول وجود دارد که وقتی یک ردیف را در بالای آنچه جمع می شود اضافه می کند.
مکاتب فکری زیادی در مورد اینکه چگونه می توان این کار را انجام داد وجود دارد، که هر کدام تکنیک متفاوتی را برای انجام همان کار پیاده می کنند. یک روش این است که به سادگی داده های خود را به یک جدول رسمی در کاربرگ تبدیل کنید. ساده ترین راه این است که یک سلول از داده های خود را انتخاب کنید و سپس Ctrl+T را فشار دهید. اکسل داده های شما را به عنوان یک جدول تشخیص می دهد و اگر هر ردیفی را در بالای جدول خود درست زیر عنوان خود وارد کنید، به طور خودکار در جمع بندی جدول منعکس می شود.
روش دیگر تنظیم فرمول SUM است. شما احتمالاً مجموع ردیف های 2 تا 15 را انجام می دهید زیرا ردیف 1 دارای عنوان ستونی در آن است. اگر اینطور است (و اگر عنوان ستون حاوی متن باشد)، به سادگی فرمول SUM خود را طوری تنظیم کنید که به ردیف های 1 تا 15 ارجاع دهد. اکنون، وقتی شخصی یک سطر جدید در ردیف 2 اضافه می کند، به طور خودکار در جمع گنجانده می شود. علاوه بر این، متن در عنوان ستون حداقل بر این مجموع تأثیر نمی گذارد.
اگر عناوین شما حاوی اعدادی هستند که ممکن است در واقع مجموع شما را به هم بریزند، می توانید یک تغییر را امتحان کنید. یک ردیف خالی در ردیف 2 قرار دهید و فرمول SUM خود را طوری تنظیم کنید که به ردیف های 2 تا 16 اشاره کند. (به جای ردیف 15، ردیف 16 خواهد بود، زیرا ردیف جدید و خالی 2 را اضافه کرده اید.) سپس، ردیف 2 را پنهان کنید تا قابل مشاهده نباشد. اکنون وقتی افراد ردیف هایی را در بالا اضافه می کنند، در واقع آن ها را در ردیف 3 اضافه می کنند و فرمول SUM همچنان به ردیف پنهان 2 اشاره می کند — تنظیمات به درستی انجام می شوند.
همچنین می توانید از روشی استفاده کنید که در آن فرمول SUM را برای استفاده از تابع INDIRECT یا OFFSET تنظیم کنید. برای لحظه ای فرض کنید فرمول SUM فعلی شما به این صورت است:
=SUM(A2:A15)
آن را طوری تغییر دهید که شبیه یکی از موارد زیر باشد:
=SUM(INDIRECT("A2"):A15)
=SUM(OFFSET(A1,1,0):A15)
هر دوی این فرمول ها سلول بالایی محدوده را "لنگر" می کنند. وقتی سلول ها را در بالای محدوده جمع شده (قبل از ردیف 2) قرار می دهید، اکسل مراجع سلولی را در تابع INDIRECT یا در تابع OFFSET تنظیم نمی کند. نتیجه این است که فرمول SUM شما همیشه به سلول محدوده بالایی که می خواهید اشاره می کند.
