نحوه استفاده از جمع شرطی در اکسل

جمع شرطی در اکسل : 

جمع شرطی در اکسل : جمع یکی از ساده ترین و معمول ترین عملیات ریاضی است که شما در روزمره زندگی خود نیز با آن سر و کار دارید. وقتی با یک لیست طولانی از اعداد روبرو باشید، استفاده از قلم و کاغذ و تکنیک‌های سنتی و یا حتی ماشین حساب کار طاقت فرسا و به دور از دقتی می‌باشد. کافی ا‌ست حین محاسبات یک رقم را سهواً اشتباه بزنید و نتیجه کاملا برعکس می‌شود. در چنین شراطی  اکسل و ابزارهای قدرتمندش برای کمک حاضر می‌شوند.

 

اما اگر اطلاعات، ارقام و اعداد در یک کاربرگ  اکسل  جمع‌آوری  شده باشند، با یک دستور یا فرمان می‌توان عمل جمع کردن در اکسل را برای یک و حتی چندین سطر و ستون به طور همزمان انجام داد. در این آموزش نگاهی گذرا به روش‌های مختلف جمع در اکسل  داشته و در مورد جمع شرطی در اکسل به طور مفصل و با ذکر مثال‌های متنوعی صحبت خواهیم کرد. به این ترتیب در انتهای آموزش  قادرخواهید بود در کوتاه‌ترین زمان ممکن، با دقت کافی، محاسبات را در کاربرگ الکترونیکی اکسل انجام دهید و از درستی نتایج اطمینان کافی داشته باشید.

SUMIF در اکسل :

بدون شک تاکنون برای راحتی کار و جمع ارقام از تابع  SUM استفاده کرده‌اید. ابتدا یک مرور کلی بر کارکرد این تابع در اکسل داشته باشیم.

ساختار تابع SUM به صورت زیر می‌باشد:

=SUM(number1,[number2], …)

در این تابع، وجود آرگومان اول اجباری بوده و برای به دست آوردن جمع یک ستون، ردیف، محدوده یا سلول به کار گرفته می‌شود.

در مقابل،  نوشتن آگومان‌های بعدی  اختیاری بوده و می‌تواند برای جمع یک ستون با محدوده در شیت دیگر یا محدوده‌ای دیگر استفاده  شود.

یک ترفند آسان‌تر استفاده از کلید‌های میان‌بر می باشد. برای این‌کار هنگام جمع زدن ستون یا یک ردیف، کلید Alt به همراه +  را درسلول موردنظر همزمان فشاردهید.

با همه این‌ها اگر قصد استفاده از عملگر جمع  با قید و ذکر یک شرط یا چند شرط داشته باشید، دیگراستفاده از تابع SUM به تنهایی کارساز نیست.

برای این کار در اکسل بایستی از توابع بسیار کاربردی SUMIFو SUMIFS استفاده شود.

تابع جمع شرطی  در اکسل چیست؟

تابع SUMIF یک تابع کاربرگی(worksheet function) که به اختصار WS  گفته می‌شود، می‌باشد که براساس معیار و شرطی اعداد را در محدوده‌ی خاصی جمع می‌کند. تابع جمع شرطی در اکسل در دسته بندی توابع  Math/Trig  قرار گرفته و به انجام عملیات جمع شرطی سرعت می‌بخشد.

به علت اینکه این تابع در در دسته توابع کاربرگ قرار می‌گیرد، می‌تواند به عنوان قسمتی از فرمول در سلول وارد شود.

ساختار(Syntex) استفاده از تابع SUMIF در اکسل

ساختار(Syntex) استفاده از تابع جمع شرطی در مایکروسافت اکسل به شرح زیر است:

SUMIF( range, criteria,[sum_range])=

پارامترها و آرگومان های تابع جمع شرطی در اکسل را به تفصیل در اینجا بررسی می‌کنیم.

• Range

محدوده‌ای از سلول‌ها که می‌خواهید با معیارهایی آن‌ها را ارزیابی کنید. این سلول‌ها بایستی شامل اعداد، نام، آرایه ها و یا مراجع باشند.درنظر داشته باشید که مقادیر خالی و متن نادیده گرفته می‌شوند.

• Criteria

معیارهایی (شرط‌هایی) در قالب یک عدد، عبارت، مرجع سلول، متن و یا یک تابع تعریف شده. به عنوان مثال این معیار می تواند به عنوان یک عدد مثل ۴۵بیان شود و یا شرط “>32″”،  و یا سلول B9 ، و یا رشته، “۳۲”

باشد.

معیارهای متن (رشته) و یا عبارات منطقی (True ,false) بایستی در دو علامت نقل قول یا گیومه (“) قرار بگیرند. اگر معیار عددی باشد، علامت نقل قول لازم نیست.

می توان از کلمات wildcard (علامت سوال (؟) و ستاره (*)) به عنوان آرگومان معیار (Criteria) استفاده کرد.

• [sum_range]

نوشتن این قسمت کاملا اختیاری‌ست و محدوده‌ی سلول‌هایی است که بایستی با یکدیگر جمع شوند. اگر این پارامتر حذف شود، از Range به عنوان sum_range استفاده می‌شود.

 

خروجی تابع جمع شرطی در اکسل، یک مقدار عددی است. این تابع در نسخه‌هایی مانند اکسل برای آفیس ۳۶۵، اکسل ۲۰۱۹، اکسل ۲۰۱۶،اکسل ۲۰۱۳،اکسل ۲۰۱۱ برای Mac، اکسل ۲۰۰۷، اکسل ۲۰۰۳، اکسل Xp و اکسل ۲۰۰۰ قابل اعمال است.

مثال‌هایی از تابع SUMIF در اکسل

SUMIF(A2:A6, D2, C2:C6)=

شرط مقدار سلول D2  بوده و مقدار بازگشتی از این تابع برابر ۲۱۸٫۶   می باشد.

SUMIF(A:A, D2, C:C)=

شرط بر روی کل ستون A اعمال شده و نتیجه برابر ۲۱۸٫۶ می‌باشد.

SUMIF(A2:A6, 2003, C2:C6)=

شرط عدد ۲۰۰۳ بوده و بعد از اعمال، خروجی برابر عدد ۷٫۲  می‌باشد.

=SUMIF(A2:A6, “>=2001”, C2:C6)

معیار بزرگتر مساوی ۲۰۰۱ وخروجی عدد ۱۲٫۶ است.

=SUMIF(C2:C6, “<100”)

پارامتر سوم حذف شده و خروجی برابر ۳۱٫۲ است.

حال نگاه دقیق‌تری بر مثال اول می‌اندازیم.

SUMIF(A2:A6, D2, C2:C6)=

همان‌گونه که ذکر شد خروجی این تابع برابر۲۱۸٫۶ است. می‌‌خواهیم بدانیم چرا و چگونه اکسل، این خروجی را نمایش می‌دهد

اولین پارامتر در تابع جمع شرطی اکسل، محدوده‌ی سلول‌هایی است که شرط بر آن‌ها اعمال می‌شود.

در این مثال، اولین پارامتر A2:A6 بوده و محدوده سلول‌های مدنظر است.

در این مثال پارامتر دوم، D2 بوده و به سلول D2 و مقدار عددی ۲۰۰۰ اشاره دارد. تابع جمع شرطی در اکسل بر روی تمام سلول‌های A2:A6 برابری با عدد ۲۰۰۰ را بررسی می‌کند.

در این مثال ، پارامتر سوم C2: C6 است. برای هر مقدار در A2: A6 که با D2 مطابقت دارد، مقدار مربوط به C2: C6 جمع می شود.

نحوه‌ی استفاده از تابع جمع شرطی SUMIFS در اکسل

اگر با یک شرط سر و کار نداشته باشید و ترجیح بدهید چندین شرط بر روی تابع جمع خود اعمال کنید. در چنین مواردی  به جای تابع جمع شرطی در اکسل، تابع SUMIFS برای کمک رسانی حاضر می‌شود.

 

این تابع نیز همچون تابع SUMIF در دسته‌ی توابع Math/Trig قرار گرفته و می‌تواند به عنوان تابع WS  در اکسل استفاده شود به صورتی که در سلول‌ها به عنوان بخشی ار فرمول‌های موردنظر واردشده و نتایج به سرعت محاسبه می‌شود.

ساختار این تایع، پارامترها و آرگومان‌های آن به شرح زیر می‌باشد:

=SUMIIFS( sum_range, criteria-range1, criteria1, [criteria_range2, criteria2,…,criteria_range_n, criteria_n] )

Sum_range

سلول‌هایی که باید جمع شوند.

criteria_range1

محدوده و دامنه‌ی سلول‌هایی که قراراست criteria1 بر آن‌ها اعمال شود.

Criteria1

برای تعیین سلول‌هایی که باید جمع شوند استفاده می شود.

Criteria_range2,.., criteria_range_n

Criteria2,…,criteria_n

استفاده از این  دو قسمت کاملا اختیاری‌ می باشد.

 

خروجی این تابع نیز یک مقدار کاملا عددی است. در  مثال  زیر نحوه‌ی استفاده ی این تابع در اکسل مشاهده می‌شود.

SUMIFS(C2:C9,A2:A9, “=۲۰۱۳”)=

در اینجا یک شرط اعمال شده و خروجی برابر ۴۰٫۰۵ می‌باشد.

SUMIFS(C2:C9,A2:A9, “=۲۰۱۳” , B2:B9, “Oranges”)=

در این مثال، دو شرط اعمال شده و خروجی در عکس شماره ۱ قابل مشاهده بوده و برابر ۲۵٫۷ می‌باشد. مشاهده می‌شود که همان‌طور که گفته شد خروجی مقدار عددی است.

SUMIFS(C2:C9,A2:A9,”>=2009″,B2:B9,”=Oranges”, A2:A9,”<=2012″)=

تابع با سه شرط کار کرده و خروجی برابر۴٫۵۵ می‌باشد.

 

 

استفاده از محدوده‌های نامگذاری شده در تابع جمع شرطی در اکسل

همچنین شما می‌توانید از یک محدوده نامگذاری شده در تابع SUMIF استفاده کنید. محدوده نامگذاری یک نام توصیفی برای مجموعه ای از سلول‌ها یا دامنه در یک کاربرگ اکسل می‌باشد. اگر از چگونگی استفاده ازاین ویژگی در اکسل بی‌خبر هستید، با ما باشید.

به طور مثال، اگر ما یک محدوده نامگذاری شده مانند years را که به سلول های A2:A6 در شیت ۱ اشاره می‌کند بسازیم. می‌توان از این محدوده‌ی نامگذاری شده در این مثال استفاده کرد.

این ویژگی امکان جایگذاری اولین پارامتر که A2:A6  بود را با محدوده نامگذاری شده که years نامگذاری شد را می‌دهد. برای شفاف سازی این مطلب مثال زیر راهگشا است.

 

SUMIF(A2:A6, D2, C2:C6) =

در اینجا، اولین پارامتر از محدوده‌ی استاندارد استفاده کرده و نتیجه برابر عدد ۲۱۸٫۶ می‌باشد.

SUMIF(years, D2, C2:C6)=

پارامتر اول از محدوده‌ی نامگذاری شده به ‌years استفاده کرده و نتیجه همان عدد ۲۱۸٫۶ است.

 

پاسخ به چند سوال رایج  و جذاب در مورد جمع شرطی در اکسل

سوال اول:

تعدادی سلول در اکسل داریم اما نیازمند این هستیم که فقط در خروجی جمع سلول با مقادیر منفی حساب شود، در این مثال ۸ مقدار وجود دارد که به ترتیب در سلول‌های A1 تا A8جای گرفته‌اند و در این بین فقط سلول های A1، A4  و A6 مقدار منفی دارند.

 

اگر بخواهیم جمع این سه سلول (A1,A4,A6) را تحت عنوان B1 به دست آوریم، چگونه در اکسل باید فرمول نویسی انجام دهیم؟

پاسخ:

با استفاده از تابع جمع شرطی، جمع با شرایط بالا به آسانی قابل محاسبه است.

SUMIF(A1:A8,”<0″)=

فرمول بالا، فقط تابع جمع را بر روی سلول‌هایی از A1:A8 اعمال می‌کند که مقداری منفی و کوچکتر از صفردارند.

سوال دوم:

در دو ستون F وG مقادیری داریم. می‌خواهیم این شرط چک شود که اگر مقداری در سلول‌های ستون F برابر با “food” باشد در آن صورت مقدار متناظر با آن سلول در ستون G جمع شود. به طور مثال سلول متناظر با  F2 برابر G2 می‌باشد. اگر از تابع شرطی  if  استفاده شود، تنها بر روی یک جفت سلول اعمال می‌شود. چگونه برای کل ستون این ویژگی اعمال شود؟

پاسخ:

در این مثال و با شرایط ذکر شده، می‌توان به جای استفاده از تابع شرطی if از تابع جمع شرطی SUMIF  استفاده کرد.

=SUMIF(F1:F10,”=food”,G1:G10)

این فرمول ۱۰ سطر از داده‌ی شما در صفحه گسترده اکسل را ارزیابی می‌کند، در نظر داشته باشید که با توجه به سلول‌ها  محدوده‌ی موردنظر این عدد می‌تواند تغییرکند.

نکته قابل توجه این است که هنگامی که در استفاده از تابع جمع شرطی در اکسل، فرمولی را کپی و پیست کردید و با خطا رو به رو شدید، علت این موضوع این است که جدا کننده متغیر ها در ویندوز کامپیوتر شما ممکن است بر روی چیز دیگری تنظیم شده باشد، که در بیشتر موارد یا از سمی کالون “;” و یا از ویرگول “,”  استفاده می‌شود.

خطاها و بازگشت مقدار نادرست از تابع جمع شرطی در اکسل

تابع sumif در اکسل نتایج نتایج نادرست برمی‌گرداند، اگر:

• هنگامی‌که از رشته‌ای با طول بیش از ۲۵۵ کاراکتر استفاده شود.
• آرگومان sum_range به اندازه و شکل آرگومان range نباشد.

 

 

استفاده از جمع شرطی برای چند شیت  در اکسل

تا اینجای کار با نحوه‌ی استفاده از جمع شرطی اکسل در یک شیت آشنا شدیم، در این قسمت از آموزش با استفاده از جمع شرطی برای چند شیت  در اکسل آشنا خواهیم شد.

روش اول) استفاده از تابع SUMIF در هر شیت و محاسبه‌ی مجموع کل

شیت‌های موردنظر با نگه‌ داشتن کلید کنترل انتخاب کرده و شیت ها در یک گروه دسته‌بندی می‌شوند. در آخرین شیت انتخابی، در یکی از سلول‌های خالی علامت مساوی را تایپ کرده و فرمول SUMIF را در اکسل بنویسید. سپس کلید Enter را فشاردهید تا این فرمول بر تمامی شیت‌ها اعمال شود. سپس مقادیر به دست آمده از هر شیت را با استفاده از تابع جمع شرطی در اکسل جمع بزنید.

روش دوم) استفاده از تابع sumproduct

در این مثال می‌خواهیم در سه شیت  ۱ تا ۳  مجموع اعداد ستون H را با درنظر گرفتن این شرط که در ستون G حرف A  باشد را محاسبه کنیم و نتیجه را در شیت ۴ درج کنیم.

برای اینکار می‌توان از فرمول زیر استفاده کرد. البته باید در شیت ۴، نام شیت‌ها تایپ شود (A2:A4):

 

 

SUMPRODUCT(SUMIF(INDIRECT(“‘”&A2:A4&”‘!G2:G5”),C2,INDIRECT(“‘”&A2:A4&”‘!H2:H5”)))=

خروجی رابطه‌ی بالا، برابر عدد ۱۵۰ می‌باشد.

روش سوم) تعریف تابع جدید SUMIF3D

برای رفع محدودیت‌های تابع SUMIF در اکسل ، با استفاده از ماکرونویسی می‌توان تابع جدیدی به توابع موجود اکسل اضافه کرد.

برای ساخت ماکرو، کلیدهای Alt+F11 را فشارداده یا از تب Developer قسمت code گزینه visual Basic را انتخاب کنید. پنجره‌ی Microsoft Visual Basic ظاهر می‌شود. مطابق شکل زیر، در این پنجره از تب Insert گزینه‌ی Module را انتخاب کنید.

سپس در پنجره جدید بازشده کدهای زیر را تایپ کنید:

از محیط ماکرونویسی خارج شده و به محیط اکسل برگردید (Alt+Q).

در این مثال، در شیت سوم به‌جای علامت سوال، می‌توان یکی از دو تابع زیر را استفاده کرد.

=SUMIF3D(A2:A5,A2,B2:B5,”Sheet1″,”Sheet2″,”Sheet3″)

یا

 

SUMIF3D(A:A,A2,B:B,”Sheet1″,”Sheet2″,”Sheet3″)=

 

در واقع  توسط این توابع، مجموع اعداد محدوده B2:B5 در سه شیت در صورتی‌که در محدوده A2:A5 عبارت موجود در سلول A2 یعنی حرف B وجود داشته باشد. محاسبه می‌شود.

خروجی رابطه فوق برابر عدد ۶۰ بوده و در نظر داشته باشید که در تابع SUMIF3D نام شیت‌ها بایستی مابین علامت نقل قول ” ” قرار گیرند. همچنین ترتیب نام‌ها اهمیتی ندارد.

جمع بر اساس رنگ و استفاده از تابع جمع شرطی در اکسل

فرمول کلی:

=SUMIF(color_range,criteria,number_range)

اگر شما بخواهید جمع را بر اساس رنگ انجام دهید، می‌توانید به آسانی از تابع SUMIF در اکسل استفاده کنید. در مثال تصویر بالا، فرمول نوشته شده در سلول G5 برابر است با:

=SUMIF($B$4:$B$11,F5,$D$4:$D$11)

محدوده  در تابع جمع شرطی اکسل بالا برابر $B$4:$B$11 بوده و معیار F5 در نظرگرفته شده است.