تابع SUMIFS اکسل - آموزش قدم به قدم و نکته های کاربردی آن

در این مقاله می‌خواهم به صورت ساده، قدم به قدم و البته کاربردی به شما تابع SUMIFS اکسل را آموزش دهم. البته قبل از شروع باید چند نکته مقدماتی را گوشزد کنم:

نکته 1) به جای تابع SUMIFS به اشتباه SUMIF تایپ نکنید.

نکته 2) اکسل توابع COUNTIFS و AVERAGEIFS را دارد که رفتار آن ها دقیقا شبیه به SUMIFS است.

نکته 3) می خواهم سوالی را از شما بپرسم. «آیا بجز SUMIFS راه ساده تری برای جمع زدن سلول ها بر اساس یک شرط یا چندین شرط وجود دارد؟» 

پاسخ بلی است. ابزار PIVOT TABLE در اکسل بدون نیاز به هیچ فرمول نویسی می تواند دقیقا کار SUMIFS را برای شما انجام دهید و اتفاقا بسیار هم ساده است.حال شما باید بپرسید که پس چرا SUMIFS اینقدر مشهور است و چه ویژگی خاصی دارد که PIVOT TABLE قادر به انجام آن نیست. در ادامه مقاله حتما این نکته را باید به شما بگویم. فعلا حوصله کنید .

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

تابع SUMIFS اکسل - معرفی

بیایید با یک مثال خیلی ساده شروع کنیم. پدری سه فرزند دارد و پول توجیبی هایی که به آنها در طی یک سال می دهد را در یک فایل اکسل نوشته است:

حال می خواهیم به پرسش‌های زیر پاسخ دهیم:

بیایید این سوال با قسمت به قسمت  یکبار با هم بخواهیم و دقیقا آن را به صورت اکسلیسی (ترکیب انگلیسی و اکسلی ) بازنویسی کنیم :

1) اولین قسمت این جمله کلمه  «جمع» است. پس در اکسل بنویسید:

=SUMIFS(

2) دومین کلمه‌ی این جمله «پول‌توجیبی» است. در جدول بالا، پول توجیبی در ستون D وارد شده است پس اینطوری اکسلیسی آن را تکمیل می کنیم:

=SUMIFS(D:D

3) سومین قسمت جمله «به شرط اینکه» است و من علامت کاما را در فرمول اضافه می‌کنم:

=SUMIFS(D:D,

نکته: اولین علامت کاما در فرمول SUMIFS را در ذهنتان باصدای بلند «به شرط اینکه» بخوانید.

4) چهارمین قسمت این جمله کلمه «اسمش» است، در جدول بالا، اسامی در ستون B نوشته شده است، پس بنویسید:

=SUMIFS(D:D, B:B,

5) قسمت بعدی «رومینا» است، پس اسم او را در داخل علامت «دَبل کوت» به فرمول اضافه می‌کنیم:

=SUMIFS(D:D , B:B, "Romina")

و اگر ENTER را بزنیم، عدد 46 را خواهید دید. SUMIFS به همین سادگی بود. همان جلمه فارسی را به صورت «اکسلیسی» بازنویسی می کنید.

قبل از ادامه چند سوال احتمالی شما را پاسخ می دهم:

سوال 1) آیا گذاشتن فاصله بعد هر علامت کاما اجباری است؟

پاسخ: خیر ، اما توصیه می  شود این علامت را بگذارید تا فرمول ها خوانا تر شوند. باید بدانید که فرمول یکبار نوشته می شود اما هزاران بار ممکن است خوانده شود. پس خوانایی فرمول مهم است.(برگرفته از PYTHON ZEN)

سوال 2) شما کل ستون D:D و کل ستون C:C را به عنوان محدوده انتخاب کردید، آیا اکسل کند نمی شود؟

پاسخ: خیر. اکسل دارای خاصیتی به نام LAST CELL است. یعنی همه سلولها را در حافظه لود نمی کندو فقط سلولهایی واقعا درحافظه لود می شوند که استفاده شده اند. بنابراین این فرمول اگر چه همه سلولهای ستون D و C را شامل می شود، اما عملا فقط آنهایی که در حافظه هستند را پردازش می کند. البته در این مثال اگر فرمول زیر را بنویسید کاملا صحیح است اما فرمول طولانی تر و در نتیجه ناخواناتر خواهد شد:

=SUMIFS(D3:D11, C3:C11, 2)

سوال 3) من این فرمول را دقیقا مانند شما نوشتم، اما هربار که Enter را می زنم، اکسل یک پیغام خطا می دهد. چرا؟

پاسخ: چک کنید به جای SUMIFS ، به اشتباه SUMIF ننوشته باشید و یا  احتمالا تنظیمات Control Panel --> Region کامپیوتر بر روی فارسی است و باید به جای علامت کاما در فرمول، علامت سیمی کالن یا همان چشمک یعنی «;» را بگذارید، بنابراین این فرمول را  امتحان کنید:

=SUMIFS(D:D ;B:B; "Romina")

سوال4) چرا Romina را در داخل علامت دابل کوت یعنی "" نوشتید؟

پاسخ: هر گاه در فرمولی یک متن نوشته می شود باید در داخل این علامت باشد تا اکسل متوجه شود این یک متن است وگرنه سعی می کند که تفسیرش کند و چون نمی تواند، خطای Name# را خواهید دید. (اعداد را لازم نیست در داخل علامت " " بگذارید)

سوال 5) آیا می توان Romina را در یک سلول نوشت، و به آن سلول ارجاع داد؟

پاسخ: بله. مثلا فرض کنید که می خواهید گزارشی بسازید که جمع پول توجیبی هر کسی را حساب کنید:

بیایید قبل از ادامه، این فرمول را مجدد ببینیم و از چپ به راست، فارسی بخوانیم:

 تابع SUMIFS اکسل- صورت کلی

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

از فرمول پیداست که :

1) اولین ورودی تابع، آدرس محدوده‌‍‌ای که اعدادی در آن است و باید آن اعداد با هم جمع زده شوند.

2) شرط‌ها به صورت زوج هستند یعنی هم باید یک «محدوده شرط» را مشخص کنیم و هم باید «خود شرط» را بنویسیم.

3) همواره اول محدوده شرط را باید مشخص کنیم و سپس شرط را بنویسیم.

یادآوری) تابع SUMIFS از 1 تا 127 زوج «محدوده شرط و خود شرط» را قبول می‌کند.

چندین شرط در SUMIFS اکسل

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

1) در این مثال دو شرط داریم: هم باید اسمش «رکسانا» باشد و همچنین باید «ماه» آن بیشتر از عدد 6 باشد. بیایید شرط دوم را نادیده بگیریم و همان فرمولی را که قبلا برای «رمینا» نوشتیم را بنویسیم و فقط نام را تغییر بدهیم:

=SUMIFS(D:D, B:B, "Roxana")

2) حال باید شرط دوم را اضافه کنیم، بنابراین من یک علامت کاما در فرمول اضافه می‌کنم و این کاما را در ذهنم «همچنین» ‌می‌خوانم:

=SUMIFS(D:D, B:B, "Roxana",

نکته: علامت کاما در فرمول SUMIFS که بین شرط‌ها گذاشته می‌شود را در ذهنتان باصدای بلند «همچنین» بخوانید.

3)  در پرسش نوشته است «ماه» ، بنابراین ما هم در فرمول به ستونی که ماه‌ها در آن نوشته شده است، یعنی C اشاره می‌کنیم:

=SUMIFS(D:D, B:B, "Roxana", C:C,

4) قسمت بعدی پرسش، گفته است «6 به بعد»، و ما هم در قسمت بعدی فرمول به ریاضی می‌نویسیم بزرگتر از 6 باشد:

=SUMIFS(D:D, B:B, "Roxana", C:C, ">6")

قبل از ادامه چند سوال احتمالی شما را پاسخ می دهم:

سوال 1) چرا شرط را در داخل علامت "  " قرار داده‌اید؟

پاسخ: باید شرط‌های بزرگتر و .. را در داخل علامت " " قرار دهید، زیرا این نحوه نگارش استاندارد SUMIFS است.

سوال 2) چطور می توانم عدد 6 را در یک سلول مثلا F10 بنویسیم و به این سلول در فرمول ارجاع دهم؟

پاسخ:

=SUMIFS(D:D, B:B, "Roxana", C:C, ">" & F10)

سوال 3) شما علامت بزرگتر را در فرمول درست نوشتید!؟

پاسخ: بله، علامت‌های ریاضی از چپ به راست خوانده می شوند، بنابراین علامت بزرگتر به شکل "<" نوشته می‌شود و علامت کوچکتر به شکل ">" . راستی علامت "=<"  و علامت "=>" به ترتیب «بزرگتر یا مساوی»  و «کوچکتر یا مساوی» هستند.

سوال 4) اگر خواستم اعداد بین دو ماه مثلا جمع تابستان را حساب کنیم، فرمول چطور می شود:

پاسخ: ادامه این مقاله را بخوانید.

سوال 5) آیا ترتیب شرط‌ها در SUMIFS مهم است؟

پاسخ: خیر. هیچ تفاوتی نمی‌کند. بنابراین اگر ابتدا شرط ماه را بنویسید و سپس شرط رکسانا را ، فرمول یکسان خواهد بود چون شرط‌های SUMIFS باهم AND (که آن را همچنین ترجمه کردیم) می‌شوند. معمولا ما سعی می کنیم فرمول را طوری بنویسیم که درکش برای خودمان ساده تر باشد. بنابراین هر دو فرمول زیر نتیجه کاملا یکسانی دارند:

=SUMIFS(D:D, B:B, "Roxana", C:C, ">6")

=SUMIFS(D:D, C:C, ">6", B:B, "Roxana")

بیایید قبل از ادامه، این فرمول را مجدد ببینیم و از چپ به راست، فارسی بخوانیم:

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

بگذارید کمی مقدمه بگویم:

اگر بخواهیم در ریاضی بنویسیم که X مقداری بین عدد 3 تا 6 است، اینگونه خواهد شد و همه آن را می‌فهمند:

3 < X < 7

اما در دنیای کامپیوتر و برنامه نویسی اینگونه نیست. یعنی کامپیوتر این فرمول را اینگونه که ذهن ما تفسیر می کند، تفسیر نخواهد کرد. در واقع ابتدا کامپیوتر مقدار X را با عدد 3 مقایسه می کند و حاصل آن یا TRUE و یا FALSE می شود و سپس TURE یا FALSE را با عدد 7 مقایسه می کند، که در اکسل همواره پاسخش FALSE می شود. به همین دلیل در دنیای کامپیوتر این عبارت ریاضی **باید** اینگونه نوشته شود:

در این مثال ما با «ماه» سر و کار داریم، بنابراین اجازه دهید من پرسش سوم را مجدد و بگونه‌ای که اکسل فهم می کند، بازنویسی کنم:

جمع پول توجیبی‌ها به شرط اینکه ماه آن بزرگتر از 3 باشد و همچنین ماه آن کوچکتر از عدد 7 باشد.

حالا مشخص شد که ما دو شرط داریم: باید ماه بزرگتر از 3 باشد و همچنین باید ماه کوچکتر از 7 باشد. از قبل هم می‌ دانیم که علامت کامای بین دو شرط را همچنین باید بخوانیم. پس فرمول ما خواهد شد:

=SUMIFS(D:D, C:C, ">3", C:C, "<7")

قبل از ادامه چند سوال احتمالی شما را پاسخ می دهم:

سوال 1) آیا می‌شود برای محاسبه جمع تابستان بگوییم «بزرگتر یا مساوی 4» باشد و همچنین «کوچکتر یا مساوی 6»؟

پاسخ: بله ، فرمول شما می‌شود:

=SUMIFS(D:D, C:C, ">=4", C:C, "<=6")

سوال 2) من فرمول را می نویسم و مرتب خطای "You've entered too few arguments for this function" را می‌بینیم. چرا؟

پاسخ: کلمه argument یعنی ورودی که شما به یک تابع می‌دهد و این پیغام به شما می گوید که تعداد ورودی ها کم است. احتمال زیاد آن است که دومین C:C را در فرمول جا انداخته باشید. یعنی نوشته باشد:

=SUMIFS(D:D, C:C, ">=4",  "<=6")

سوال 3) اگر بخواهم به جای نوشتن اعداد ماه شروع و پایان به سلولهای اکسل ارجاع دهم، فرمولم چگونه می شود؟

پاسخ: فرض کنید که ماه شروع را در سلول F10 و ماه پایان را در G10 نوشته باشد، فرمول شما خواهد شد:

=SUMIFS(D:D, C:C, ">=" & F10, C:C, "<=" & G10)

دقت داشته باشید که & و آدرس سلول خارج از دَبل کوت، نوشته شده اند!

در این مثال ما شانس آورده ایم ، چون اسامی دخترها با "RO" شروع می‌شود و خوشبختانه در SUMIFS می تواند از WILDCARDها استفاده کرد.این هم یکی دیگر از مزایای مهم SUMIFS نسبت به PIVOT است و فرمول ما می شود:

=SUMIFS(D:D, B:B, "RO*")

بگذارید دقیق تر توضیح دهیم. WILDCARDها یعنی علامت هایی که مافوق خودشان معنا می دهند. در اکسل ما کلا 2 تا WILDCARD داریم. یکی علامت «*» است و دیگری علامت «؟». علامت «*» یعنی هرچیزی و یا هیچ چیز و علامت «؟» یعنی یک کاراکتر. در جدول زیر مثالهای بیشتری را برای شما خواهم می نویسم:

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

سایر نکته‌های SUMIFS اکسل

نکته 1) تابع SUMIFS تنها تابعی است در اکسل که نمی‌تواند از روی فایل که بسته است، داده‌ها را بخواند و محاسبه کند. اجازه دهید توضیح دهم:

ما در اکسل می‌توانیم فرمولی بنویسیم که محاسبات را بر روی داده‌های «یک فایل دیگر» انجام دهد و این محاسبات به سادگی UPDATE خواهند شد حتی اگر آن فایل «بسته» باشد. اما اگر از SUMIFS استفاده می‌کنید، حتما باید برای UPDATE شدن محاسبات، آن «فایل» باز باشد.

نکته 2) شرط ها در SUMIFS باهم AND می شوند. یعنی چیزهایی را جمع می زند که «همه شرطها» را داشته باشند. ما در این مقاله کلمه AND انگلیسی را «همچنین» ترجمه کردیم نه «و».

زیرا ما «و» را در فارسی به چندین منظور مختلف به کار می بریم و خوانش آن در SUMIFS کمی مبهم می شد.

نکته 3) اگر بخواهیم شرط ها OR شوند، یعنی چیزهایی را جمع بزند که یکی از شروط را داشته باشند، تکنیک های مختلفی وجود دارد، مثلا می توانیم 2 بار SUMIFS بنویسم. مثلا اگر بخواهیم «جمع پول توجیبی ها به شرط آنکه اسم فرد رومینا یا آرین باشد» را محاسبه کنیم، فرمول اینگونه خواهد شد:

=SUMIFS(D:D , B:B, "Romina") + SUMIFS(D:D , B:B, "Arian")

نکته 4) تفاوت SUMIFS و SUMIF در چیست؟

تابع SUMIFS در اکسل 2007 به توابع اکسل اضافه شد و می‌توان از 1 تا 127 شرط را بوسیله آن پوشش داد. اما تابع SUMIF فقط یک شرط قبول می‌کند. توجه داشته باشید که ورودی‌های این دو تابع دقیقا عکس هم هستند و برای آنکه شما دچار سردرگمی نشوید، از ذکر SUMIF خودداری شد و به جای آن از SUMIFS استفاده کنید.

معرفی AVERAGEIFS و COUNTIFS و MAXIFS و MINIFS

توابع MAXIFS و MINIFS به ترتیب برای ماگزیمم شرطی و مینیمم شرطی هستند و به اکسل از نسخه 2016 اضافه شده‌ا‌ند.

رفتار و قواعد این 4 تابع کاملا شبیه SUMIFS است و در فایل پیوست مثال هایی از آن برای شما زده شده است.