آموزش تصویری تابع sumifs در اکسل

یکی دیگر از محاسبات پیچیده و وقت‌گیری که می‌توان به‌راحتی با توابع اکسل انجام داد عملیات جمع چه به‌صورت ساده و چه جمع شرطی است. کاربرد تابع Sumifs در اکسل دقیقا این است که مقادیر مشخصی که تعیین کرده‌ایم را ابتدا به کمک شرط یا شروط تنظیم شده پیدا کند و سپس مجموع این مقادیر را محاسبه نماید. کار کردن با این تابع، کار چندان پیچیده‌ای نیست. در این مقاله آموزش تصویری تابع Sumifs را برای شما قرار داده‌ایم اما پیش از شروع باید ببینیم این تابع اصلا چیست و چه ویژگی‌هایی دارد؟

تابع Sumifs چیست؟

تابع Sumifs نسخه پیشرفته‌تر تابع Sumif و Sum است. در تابع Sum، اعداد به‌صورت ساده با یکدیگر جمع می‌شدند و در تابع Sumif می‌توانستیم تنها یک شرط برای جمع کردن اعداد قرار بدهیم. تابع Sumifs که در این مقاله کاملا آن را بررسی کرده و آموزش خواهیم داد، قابلیت جمع نمودن اعداد با چند شرط مختلف را دارد. تعداد این شرط‌ها می‌تواند تا 127 عدد برسد.

تابع Sumifs به زبان ساده:

=Sumifs(ستون مربوط به ارقامی که باید جمع شوند,
ستون مربوط به شرط اول,
بیان شرط اول,
ستون مربوط به شرط دوم,
بیان شرط دوم,
...)

 

اگر هنوز نمی‌دانید چطور می‌توانید از این تابع استفاده کنید نگران نباشید! هنوز آموزش شروع نشده است!

چگونه از تابع sumifs در اکسل استفاده کنیم؟

برای توضیح بهتر بد نیست دو مثال از تابع sumifs را بررسی کنیم. مثال اول ساده‌تر است و تابع را به‌شکل ابتدایی توضیح می‌دهد. در مثال دوم، تابع را گسترش می‌دهیم و عملکردهای آن را بهتر بررسی می‌کنیم:

تابع sumifs در اکسل، مثال اول

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

حالا قصد داریم تعداد قسمت‌های منتشر شده از سریال‌های مختلف را که رتبه IMDB آن‌ها یکسان است جمع بزنیم. به بیان دیگر می‌خواهیم ببینیم مجموعه تعداد اپیزود از سریال‌هایی که IMDB 9.5 داشته‌اند چند تا بوده است. همین‌طور IMDB با نمره 9.4، 9.3، 9.2 و 9.1. بنابراین جدول مورد نظرمان را به این شکل رسم می‌کنیم:

روی اولین سلول در ستون تعداد قسمت‌ها کلیک می‌کنیم و تابع را به این شکل می‌نویسیم:

=sumifs(

 

برای پر کردن داخل پرانتز، به چند متغیر نیاز داریم. این متغیرها را یک به یک به دست می‌آوریم:

1. متغیر اول؛ ستون مجهول (sum_range)

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

=sumifs(G:G

می‌توان با کلیک بر روی ستون مورد نظر آن را انتخاب کرد. همچنین می‌توانیم حرف مربوط به ستون مورد نظر را در الگوی «X:X» قرار بدهیم. یعنی از ابتدا تا انتهای ستون X باید انتخاب شود. پس از هر متغیری، لازم است از کاما (,) استفاده کنیم و بعد به سراغ متغیر بعدی برویم، بنابراین:

2. متغیر دوم؛ ستون معلوم

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

=sumifs(G:G, F:F

برای وارد کردن ستون مورد نظر، می‌توان حرف آن را به‌صورت (تکرار حرف مورد نظر):(حرف مورد نظر) تایپ کرد یا به‌جای آن مستقیما روی خود ستون کلیک نمود. علامت دو نقطه (:) به معنای «تا» به کار می‌رود و با تکرار نام ستون، نشان می‌دهیم محدوده عملکرد ما صرفا همان ستون مشخص است.

در انتهای این قسمت از معامله تابع نیز حتما از کاما () استفاده کنید.

3. متغیر سوم؛ شاخص معلوم

و بالاخره باید شاخص معلوم را مشخص کنیم. قرار است تابع sumifs ما بر حسب چه شاخصی، یافته‌ها را با هم جمع کند؟ این همان داده مشخصی است که باید در قسمت نهایی تابع وارد نماییم و در این مثال رتبه 9.5 در مقیاس IMDB است.

=SUMIFS(G:G, F:F, I6

شما می‌توانید مستقیما روی مقدار مورد نظر کلیک کنید، سلول مربوط به آن را بنویسید یا حتی نامش را با حروف و اعداد تایپ کنید. فقط توجه داشته باشید در صورتی که این متغیر با حروف نوشته می‌شود و عددی نیست باید آن را بین دو آپاستروف قرار بدهید تا تابع به‌صورت متن آن را بخواند نه به شکل مقادیری که قابل تغییر هستند:

در نهایت پرانتز را می‌بندیم و بر روی Enter کلیک می‌کنیم.

همان‌طور که تابع sumifs محاسبه نموده است، تعداد قسمت‌های سریال‌هایی که رتبه IMDB آن‌ها 9.5 است 68 عدد است. می‌توانیم محتوای این سلول را کپی در سلول‌های بعدی ستون Paste کنیم تا تعداد اپیزودهای ربته‌های IMDB دیگر نیز مشخص شود.

مجموعه متغیر دوم و سوم را شرط تابع Sumifs می‌گوییم. درواقع ابتدا مشخص می‌کنیم که مجموع چه متغیرهایی را می‌خواهیم مصاحبه کنیم (کدام ستون)، سپس شرط‌ یا شرط‌هایی را برای آن قرار می‌دهیم(برابر با چه عدد یا مقدار مشخصی). در مثال قبلی، تنها شرطی که تابع را به آن محدود کردیم، توجه به متغیر «رتبه IMDB» بود. در مثال بعدی موارد بیشتری را بررسی می‌کنیم:

تابع Sumifs، مثال دوم

در این نمونه قصد داریم علاوه بر شرط اعمال شده در مثال قبل، تابع را محدودتر کنیم. بنابراین Sumifs را مطابق مثال قبلی تا مرحله آخر می‌نویسیم و پیش از بستن پرانتز آن را متوقف می‌کنیم:

=SUMIFS(G:G, F:F, I6

برای افزودن شرط بعدی، به یک جفت شرطی‌ساز دیگر نیاز داریم. فرض کنید می‌خواهیم تعداد اپیزوهایی را محاسبه کنیم که علاوه بر آن‌که امتیاز IMDB آن‌ها 9.5 است، در سال 2019 به پایان رسیده‌اند.

برای این کار یک کاما در انتهای تابع اضافه می‌کنیم، ابتدا محدوده شاخص معلوم (ستون تاریخ پایان) و سپس مقدار مشخص آن (سال 2019) را وارد می‌نماییم. بین این دو مقدار باید از علامت کاما استفاده کنیم:

=SUMIFS(G:G, F:F, I6, E:E, E6

می‌توان به‌جای نوشتن E6 یا کلیک بر روی سلول آن، مقدار عددی‌اش (2019) را قرار داد. اگر این عبارت به‌جای عدد از حروف انگلیسی تشکیل شده بود (یعنی نوشته بودیم دو هزار و نوزده)، لازم بود آن را بین آپاستروف بنویسیم تا اکسل به‌عنوان یک متن آن را ادراک کند(‘دو هزار و نوزده’).

نکته قابل توجه این است که امکان کپی کردن این سلول و انتقال به سلول‌های بعدی وجود ندارد زیرا به‌عنوان مثال مقدار 2019 که ما آن را ثابت نگه داشته‌ایم با انتقال به سلول‌های بعدی تغییر می‌کند. بنابراین لازم است برای هر سلول جداگانه این تابع را بنویسیم:

همان‌طور که دیدید تا اینجای کار دو شرط متفاوت را در تابع sumifs وارد کردیم و به کمک این دو شرط، داده‌های مورد نظرمان را فراخوانی و با هم جمع نمودیم. شما می‌توانید تا 127 شرط مختلف به این تابع اضافه کنید و تنها لازم است جفت شرطی مورد نظرتان را آماده کرده و بعد از کاما، وارد تابع نمایید.

سومین مثال از تابع sumifs

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

حال قصد داریم به کمک تابع Sumifs، مجموع هزینه‌های ماهانه بالای یک میلیون تومان را محاسبه کنیم:

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

2. حالا روی سلول مورد نظر کلیک می‌کنیم و تابع را می‌نویسیم:

=sumifs(

3. متغیرهایی که قصد جمع کردنشان را داریم در ستون «مبلغ/تومان» قرار گرفته‌اند. بنابراین اولین شاخصی که باید وارد کنیم محدوده این ستون است اما چون ردیف آخر که مجموع مخارج محاسبه شده را نمی‌خواهیم در هزینه‌های بالای یک میلیون تومانی محاسبه کنیم، باید به‌طور دقیق سلول شروع و پایان مورد نظرمان را تعیین کنیم. بین دو سلول از علامت دو نقطه و در انتهای آن از کاما استفاده می‌کنیم:

=Sumifs(C3:C14,

4. شرط ما نیز دقیقا مربوط به همین ستون است. بنابراین برای وارد کردن قسمت اول جفت شرطی، یعنی محدوده شاخص، دوباره همین بازه را تکرار می‌کنیم. شرط جمع ما این است که مقادیر مورد نظر حتما بالای یک میلیون تومان باشد. برای نوشتن این شرط از علامت نقل قول (گیومه) انگلیسی (یعنی “”) استفاده می‌کنیم و شرط را با علامت ریاضی بزرگتر نشان می‌دهیم:

=SUMIFS(C3:C14,C3:C14,">1000000")

به همین ترتیب می‌توانید شرط‌های دیگری به جدول اضافه کنید. مثلا:

استفاده از or در تابع sumifs در اکسل

شرط‌های متعددی که در تابع یکی پس از دیگری وارد می‌کنیم درواقع معادل عملکرد «و» در ریاضی (همان AND) هستند. یک نتیجه نهایی حتما باید تک‌به‌تک شرط‌ها را برآورده کند تا در تابع شمرده شود. گاهی به‌جای «و» به «یا» (همان OR) نیاز داریم.

مثال:

آن چیست که 1)شیرین باشد «و» 2) خنک باشد؟ جواب: بستنی

آن چیست که 1)شیرین باشد «یا» 2) خنک باشد؟ جواب: یخ، شکلات، شکر، بستنی

با استفاده از «یا» می‌خواهیم تابع ما دو دسته داده مختلف را جمع بزند. مثلا در مثال قبلی می‌خواهیم هزینه‌هایی بالای یک میلیون تومان یا هزینه‌های کمتر از پنج هزار تومان را پیدا کنیم و جمع بزنیم. در این صورت باید روش استفاده از or در تابع sumifs را بدانیم.

برای نوشتن تابع گفته‌شده، ابتدا قسمت اول تابع را مطابق قبل می‌نویسیم. سپس در ادامه پرانتز را می‌بندیم، علامت به‌علاوه (+) می‌گذاریم و تابع مشابه دیگری با شرط دوم می‌نویسیم:

=SUMIFS(C3:C14,C3:C14,">1000000")+SUMIFS(C3:C14, C3:C14, "<5000")

محاسن تابع sumifs در اکسل

محاسن متعددی را می‌توان برای تابع sumifs نام برد. برای نمونه جمع شرطی در اکسل به کمک این تابع انجام می‌شود. با ترکیب دو تابع Vlookup و sumifs می‌توانیم مسائل متعدد و متنوعی را حل کنیم و سر و سامان بدهیم. (اگر به آموزش تابع Vlookup نیاز دارید پیشتر در مقاله‌ای به آن پرداخته‌ایم.) شرط‌های این تابع می‌توانند عدد، تاریخ یا عبارت باشند. تابع sumifs در اکسل می‌تواند هم‌زمان تا 127 شرط مختلف را پشتیبانی کند.

نکات پایانی

هنگام استفاده از تابع sumifs باید به این نکات توجه کنید:

• ترتیب شرط‌های تابع مهم نیست تنها باید مراقب باشید که جفت شرطی‌ساز به ترتیب و پشت سر هم نوشته شوند.
• اگر داده‌های مورد نیاز ما در صفحه دیگری از اکسل باشند، هنگام نوشتن تابع sumifs باید نام آن صفحه را با علامت تعجب (!) بعد از پرانتز و قبل از نوشتن متغیرها و مقیاس‌ها بیاوریم:

=SUMIFS('صفحه اول'!C3:C14, C3:C14,">1000000")

• طول محدوده همه شرط‌ها باید یکسان باشد. مثلا اگر می‌خواهید از ستون C، ردیف 3 تا 21 را در شرط اول بررسی و جمع کنید، در شرط دوم هم باید ردیف 3 تا 21 از ستون مربوط به شرط دوم را انتخاب کنید. در غیر این صورت با ارور اکسل روبرو خواهید شد.

در انتهای این مقاله امیدواریم توانسته باشیم به روشنی طریقه استفاده از تابع sumifs در اکسل را آموزش داده باشیم. می‌توانید هرگونه سوال یا نکته‌ای در این زمینه را در قسمت نظرات با ما در میان بگذارید و یا برای دریافت نکات بیشتر، سری به صفحه آموزش پیشرفته اکسل ایران لرن بزنید.