برای تهیه یه سری گزارش نیاز شد که از فرمول جمع شرطی در اکسل استفاده کنم. یه سری جاها هم sum به تنهایی از عهده کار بر نمیومد. در ادامه نمونه های پیش اومده و راهی که استفاده کردم رو میذارم.
قرار بود گزارشی از یه لیست بلند بالای قیمت های لوله آماده کنم که دید بهتری به مدیریت بده برای تصمیم گیری. تصمیم گرفتم علاوه بر به دست آورد طول لوله ها در حالت کلی، بر اساس سایزشون هم تو بازه های مختلف، مجموع طول رو به دست بیارم.
نمونه ای از لیست اول
برای اینکه بتونیم به جدول زیر برسم، باید بتونم از ستون QTY اون عددهایی رو جمع کنم که ستون مربوط به سایز یعنی M_Size در بازه های مشخص شده است.
گزارش
برای بازه ۰ تا ۴ اینچ از این فرمول استفاده کردم:
1 2 3 | // SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2,criteria2], …) //and in this case: =SUMIFS(F3:F3000,E3:E3000,"<="&H6,E3:E3000,">0") |
در فرمول بالا H6 همون خونه از جدوله که عدد ۴ توش نوشته شده. عملا دارم بهش می گم به شرطی دستور جمع رو روی خونه های بین F3 تا F3000 (مقدار) انجام بده که سلولهای ستون E (یا همون سایز) از همون ردیف، بزرگتر از صفر و کوچکتر از ۴ باشه.
همونطور که در نمونه فرمول sumifs می بینید مقایسه با یک رشته متنی انجام می شود. حالت ساده اش چیزی شبیه “>0” به معنی بزرگتر از صفره و اگر بخواهیم به جای صفر آدرس یک خونه جدول رو بدیم تا بتونیم مبنای فیلتر رو به دلخواه تغییر بدیم از چیزی شبیه “<=”&H6 استفاده می کنیم.
البته فرم ساده فرمول بالا، به شکل زیره که فقط یک شرط داره. در ضمن محل قرار گرفتن ستون شرط، خود شرط و ستونی که جمع خواهد شد با نمونه بالا متفاوته.
1 | SUMIF(range, criteria, [sum_range]) |
از فرمول بالا نیز جایی استفاده کردم که می خواستم مطمئن بشم اگر در ردیفی خطای محاسباتی وجود داره، در جمع کل مشکلی ایجاد نکنه. پس فرمول به شکل زیر دراومد:
1 | =SUMIF(E3: E3000,"<>#N/A") |
این فرمول می گه ستون E از ردیف ۳ تا ۳۰۰۰ رو جمع کن به شرطی که مقدار خونه ها مخالف #N/A باشه.