تابع SUMIF نسبتاً چندکاره است، اما اگر با آن آشنایی ندارید میتواند کمی دشوار باشد. اگر از اکسل برای مدیریت کارهای کاری و مالی شخصیتان (و البته برای ایجاد نمودارها و چارتهای جذاب) استفاده میکنید یا فقط کنجکاوید که این ابزار مفید چگونه کار میکند، پس در مکان درستی آمدهاید.
تابع SUMIF کاملاً چندمنظوره است، اما اگر با آن آشنایی نداشته باشید، ممکن است استفاده از آن کمی دشوار باشد. اگر از اکسل برای مدیریت کارها و مالیات شخصیتان (و البته برای ایجاد نمودارها و گرافهای سرگرمکننده) استفاده میکنید یا فقط کنجکاوید که این ابزار مفید چگونه کار میکند، پس در مکان مناسب آمدهاید.
در اینجا، ما شما را با برخی روشهای منحصر بهفرد برای بهکارگیری این تابع در مجموعهای وسیع از موقعیتهای عملی آشنا میکنیم.
تابع SUMIF چیست؟
SUMIF یک تابع پیشفرض در اکسل است که میتوانید برای جمعکردن یک محدوده از مقادیر تحت شرایط خاص از آن استفاده کنید. این یک تابع فوقالعاده مفید است و اگر قصد استفاده از اکسل را در هر زمینهای دارید، باید با آن آشنا شوید.
نحوۀ نوشتن تابع SUMIF به شکل زیر است:
=SUMIF(range, criteria, [sum_range])
میتوانید با استفاده از تابع SUMIF محاسبات مالی پیشرفتهای را در انواع سناریوها انجام دهید. بیایید ببینیم این چطور کار میکند.
برای فریلنسرها
وقتی به عنوان فریلنسر کار میکنید، پیگیری تمام پروژهها و مشتریان میتواند دشوار باشد. خبر خوب این است که تابع SUMIF این فرآیند را بسیار ساده میکند. در ادامه به برخی موقعیتهای رایج که این تابع میتواند مفید باشد میپردازیم.
1. محاسبه ساعتهای کار شده برای هر مشتری در یک بازهٔ زمانی معین
میتوانید از تابع SUMIF در اکسل برای محاسبهٔ مجموع ماهانه ساعتهای کاری پروژههای مشتریان استفاده کنید. بهعنوان مثال، فرض کنید میخواهید ساعتهای کار ماهانهٔ هر یک از مشتریان خود را محاسبه کنید.
یا مثلاً میخواهید تعداد ساعتهایی که در ماه ژانویه برای هر مشتری کار کردهاید را محاسبه کنید و این مقدار را به حساب آنها اضافه کنید.
در این مثال، ما کل ساعتهای کار شده در ژانویه را در نظر گرفتیم و سپس با تعیین نام هر مشتری (مانند «مشتری ۴») تعداد ساعتهای کاری آنها را استخراج کردیم.
2. مشاهده فاکتورهای معوق از مشتریان
به عنوان فریلنسر، مدیریت درست مشتریان بسیار مهم است. برای مثال، ممکن است بخواهید مقدار فاکتورهای معوق از مشتریان خود پس از یک تاریخ قطع را بدانید.
در مثال بالا، آرایهای از نامهای مشتری، تاریخ قرارداد و مقادیر فاکتورهای معوق داریم. اگر بخواهید بدانید مشتریانی که بعد از 01 ژانویه 2020 فاکتورهای خود را پرداخت نکردهاند، چه مقدار بدهی دارند، میتوانید از فرمول SUMIF زیر استفاده کنید:
=SUMIF(I9:I14,">1/1/2020",J9:J14)
با استفاده از این فرمول میتوانیم همه مقادیر معوق مربوط به تاریخهای ستون I که بزرگتر از تاریخ قطع 01/01/2020 هستند را جمع کنیم.
برای استفادهٔ شخصی
SUMIF یک تابع عالی برای هر کاربر صفحهگستردهای است. استفاده از آن ساده است و میتواند به شما کمک کند کارهایتان را سریعتر انجام دهید.
گاهی ممکن است فقط بخواهید هزینههای شخصی خود را پیگیری کنید—مثلاً محاسبهٔ بدهی کارتاعتبار، مخارج خوراکی یا فقط بهروز شدن با عملکرد تیمهای ورزشی مورد علاقهتان. بیایید نگاهی دقیقتر به این ببندیم که چگونه تابع SUMIF میتواند به سازماندهی این موارد کمک کند.
3. پیگیری آمار تیم ورزشی مورد علاقهتان
SUMIF راهی عالی برای پیگیری آمار تیم ورزشی مورد علاقهتان است. بهراحتی میتوانید تعداد بردهای تیم، رکورد برد/باخت و مجموع امتیازهای آنها را مشاهده کنید.
بهعنوان مثال، اگر بخواهید تعداد گلهای ثبتشده توسط تیم یا بازیکنان مورد علاقهتان در این فصل را بدانید، میتوانید از SUMIF برای شمردن آنها استفاده کنید. تنها کافی است معادلهای به شکل زیر تنظیم کنید:
با استفاده از این تابع میتوانید کل تعداد گلهای هر بازیکن یا تعداد بردهای تیم در خانه را محاسبه کنید. خیلی ساده به نظر میرسد، نه؟
4. نظارت بر هزینههای شخصی شما
تابع SUMIF ابزار قدرتمندی برای پیگیری بودجهٔ شماست. اگر بخواهید مخارج خود را بر اساس دستهبندی پیگیری کنید، میتوانید با SUMIF تمام مقادیر مطابق با یک شرط خاص را جمع کنید.
مثال زیر را نگاه کنید. با استفاده از صفحهگستردهٔ بالا، اگر بخواهید بدانید این ماه چقدر برای خرید مواد غذایی خرج کردهاید، میتوانید از فرمول SUMIF به شکل زیر استفاده کنید:
=SUMIF(D4:D18,"Groceries",E4:E18)
این فرمول تمام مقادیر ستونی E که متن «Groceries» را دارند جمع میکند که در این مثال برابر با 140 دلار است.
برای کار یا تجارت
تابع SUMIF در بسیاری از سناریوها بهخوبی کار میکند. میتواند راه حلهای مناسبی برای کارهای روزمرهتان فراهم کرده و به مدیریت بهتر روزتان کمک کند. بیایید برخی از روشهای بهکارگیری این تابع برای کار یا تجارت را بررسی کنیم.
5. محاسبه پاداشهای کارکنان
اگر میخواهید پاداشهای کارکنان را بر اساس معیارهای خاصی (مانند فروش یا درآمد) محاسبه کنید، تابع SUMIF اکسل گزینهٔ مناسبی است.
فرض کنید صفحهگستردهای حاوی دادههای کارکنان و درآمدهای حاصل از فروش برای ماههای ژوئیه، اوت و سپتامبر در برگههای مختلف دارید.
علاوه بر این، جدولی از مقادیر پاداش (ستونهای F14:G21) دارید که پس از برآورده شدن معیارهای مربوط به گردش مالی به کارکنان اختصاص مییابد. بهعنوان مثال، اگر یک کارمند فروش بیش از 5000 دلار اما کمتر از 10 000 دلار داشته باشد، حق دریافت پاداش 600 دلار را دارد.
اکنون میخواهیم مقدار پاداش Amy را بر اساس گردش مالی او تا پایان سپتامبر محاسبه کنیم. برای این کار باید از توابع SUMIF، SUMPRODUCT و VLOOKUP بهصورت ترکیبی استفاده کنیم:
ابتدا، نام برگهها را در شیت Summary (I3:16) مرور کنید. با در نظر گرفتن فقط ماه ژوئیه، تابع SUMIF برای Amy به صورت زیر نوشته میشود.
در ادامه باید تابع SUMPRODUCT را معرفی کنیم تا اطمینان حاصل شود همه مقادیر بهدرستی جمع میشوند.
سپس باید مقادیر گردش مالی فروش را برای Amy در تمام برگهها جمع کنیم. برای این کار تنها کافی است نام تمام برگهها را در ستون I در نظر گرفته و از تابع INDIRECT برای استخراج دادهها در سه ماه استفاده کنیم. تنظیمات زیر را برای تابع SUMIF اعمال کنید:
- برای [July! B3:B8] – با INDIRECT(“‘”&$I$4:$I$6&”‘!”&”B3:B8”) جایگزین کنید.
- برای [July! D3:D8] – با INDIRECT(“‘”&$I$4:$I6&”‘!”&”D3:D8”) جایگزین کنید.
عبارت نهایی به این شکل خواهد بود:
این محاسبه مجموع فروش Amy برای ژوئیه، اوت و سپتامبر را نشان میدهد که برابر با 6 892 دلار است.
برای محاسبه مقدار پاداش، تمام تابع را داخل یک تابع VLOOKUP دیگر میگذاریم تا مبلغ پاداشهای واجد شرایط را از ستونهای F4 تا G9 جستجو کنیم. عبارت نهایی به این شکل است:
بر پایه این فرمول، مقدار پاداش Amy برابر با 600 دلار میشود.
6. پیگیری بازدیدهای صفحهٔ گوگل برای موارد خاص
تابع SUMIF راهی مفید برای جمعآوری بازدیدهای صفحات آنلاین برای آیتمهای مشخص در فروشگاه اینترنتی شماست. میتوانید از آن برای شمارش بازدیدهای صفحه، بازدیدکنندگان، تعاملات شبکههای اجتماعی یا هر معیار دیگری که میخواهید از طریق Google Analytics یا داشبورد دیگری پیگیری کنید، استفاده کنید.
در مثال ما، میخواهیم بازدیدهای صفحه برای آیتمهای SKU با عنوان «Furniture» و «Décor items» را برای یک دورهٔ دو‑هفتهای جمع کنیم. به سادگی با استفاده از معیارهای متنی، بازدیدهای هر آیتم را جمع میکنیم.
ابتدا محدودهٔ سلولهای حاوی دادهها (ستونهای C و D) را شناسایی میکنیم. سپس معیار «Furniture» را در یک تابع SUMIF وارد میکنیم و همین کار را برای «Décor items» نیز انجام میدهیم. برای بهدست آوردن مجموع بازدیدها، دو تابع SUMIF را داخل یک تابع SUM داخلی میپیچیم.
این کار با عبارت زیر انجام میشود:
=SUM(SUMIF(C3:C17,"Furniture",D3:D17)+(SUMIF(C3:C17,"Décor items",D3:D17)))
پس از اطمینان از نحوهٔ استفاده از SUMIF، میتوانید بهراحتی موارد یا ردیفهای فردی را بر اساس معیارهایشان جمع کنید. این کار ارزیابی دادهها، ارزیابی اثربخشی کمپینهای بازاریابی، جمعآوری معیارهای تعامل در شبکههای اجتماعی، شناسایی بهترین منابع ترافیک و بررسی کلی تعامل با محتوای شما را بسیار ساده میسازد.
SUMIF: ابزاری قدرتمند برای تجزیه و تحلیل دادهها
نمیتوان به اندازهٔ اهمیت تابع SUMIF برای افزایش بهرهوری کار شما تأکید کرد. اگرچه محدودیتهایی دارد، اما میتوانید از آن برای ساخت فرمولهای بسیار قدرتمند استفاده کنید. بهدلیل انعطافپذیریاش، میتواند سادهگیاش را جبران کرده و زمان زیادی را برای شما صرفهجویی کند. هماکنون شروع به بهکارگیری این تابع در صفحهگستردههای خود کنید؛ تنها کافی است کمی تمرین کنید.