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

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

امروز در مورد یکی از این توابع - صحبت خواهیم کرد سامسلیمن.

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

توضیحات تفصیلی عملکرد SUMIFS

قبل از در نظر گرفتن عملکرد سامسلیمن، ابتدا باید بفهمید که نسخه ساده تری از آن چیست - SUMMESLI، زیرا تابعی که در نظر می گیریم بر آن استوار است. احتمالاً هر یک از ما قبلاً با دو عملکرد که اغلب استفاده می شود آشنا هستیم: مبلغ (جمع بندی مقادیر را انجام می دهد) و اگر (یک مقدار را در برابر یک شرایط مشخص آزمایش می کند).

اگر آنها را ترکیب کنید، تابع دیگری دریافت خواهید کرد - SUMMESLI، که داده ها را بر اساس معیارهای مشخص شده توسط کاربر بررسی می کند و فقط اعدادی را جمع می کند که آن معیارها را برآورده می کنند. اگر در مورد نسخه انگلیسی اکسل صحبت کنیم، این تابع SUMIF نامیده می شود. به عبارت ساده، نام -language ترجمه مستقیمی از یک زبان انگلیسی است. این تابع برای اهداف مختلفی قابل استفاده است. به طور خاص، می توان از آن به عنوان جایگزین استفاده کرد VPR، یعنی یادداشت کنید

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

خود نحو این است:

SUMIFS(محدوده_محدوده، محدوده_شرط1، شرط1، [محدوده_شرط2، شرط2]، …)

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

بیایید به استدلال ها با جزئیات بیشتری نگاه کنیم:

1. مجموع_محدوده. این آرگومان و همچنین محدوده شرط 1 و شرط 1 مورد نیاز است. مجموعه ای از سلول هایی است که باید جمع شوند.
2. محدوده_شرایط 1. این محدوده ای است که شرایط بررسی می شود. با آرگومان بعدی - Condition1 - جفت می شود. جمع مقادیر مربوط به معیار در سلول های مشخص شده در آرگومان قبلی انجام می شود.
3. شرط 1. این آرگومان معیارهایی را که باید بر اساس آن بررسی شوند را مشخص می کند. می توان آن را به عنوان مثال به این ترتیب تنظیم کرد: "> 32".
4. محدوده شرط 2، شرط 2 ... در اینجا، شرایط زیر به همین ترتیب تنظیم می شوند. اگر باید بیش از چند شرط مشخص شود، آرگومان های Condition Range 3 و Condition 3 اضافه می شوند. نحو برای آرگومان های زیر یکسان است.

این عملکرد حداکثر پردازش تا 127 جفت شرایط و محدوده را امکان پذیر می کند. 

شما می توانید آن را در چندین زمینه به طور همزمان استفاده کنید (ما فقط چند مورد را ارائه می دهیم، لیست در واقع حتی طولانی تر است):

1. حسابداری. به عنوان مثال، استفاده از تابع خوب است سامسلیمن برای ایجاد گزارش های خلاصه، به عنوان مثال، به صورت سه ماهه برای هزینه های بیش از مقدار معین. یا گزارشی از یک محصول از یک دسته قیمت خاص ایجاد کنید.
2. مدیریت فروش. در اینجا نیز عملکرد می تواند بسیار مفید باشد. به عنوان مثال، ما با این وظیفه روبرو هستیم که فقط بهای تمام شده کالاهایی را که در یک زمان خاص به مشتری خاصی فروخته شده اند، جمع بندی کنیم. و در چنین شرایطی تابع سامسلیمن می تواند بسیار مفید باشد.
3. تحصیلات. ما امروز مثال های کاربردی بیشتری از این حوزه خواهیم داد. به ویژه، می توانید از آن برای دریافت خلاصه نمرات دانش آموزان استفاده کنید. شما می توانید برای یک موضوع واحد یا برای نمرات فردی انتخاب کنید. یک فرد می تواند بلافاصله چندین معیار را تعیین کند که ارزیابی توسط آنها انتخاب می شود، که بسیار راحت است و می تواند در زمان زیادی صرفه جویی کند.

همانطور که می بینید، دامنه برنامه های کاربردی این تابع بسیار گسترده است. اما این تنها شایستگی آن نیست. بیایید به چند مزیت دیگر که این ویژگی دارد نگاهی بیندازیم:

1. امکان تعیین معیارهای متعدد. چرا این مزیت است؟ می توانید از عملکرد معمولی استفاده کنید SUMMESLI! و همه به این دلیل که راحت است. نیازی به انجام محاسبات جداگانه برای هر یک از معیارها نیست. همه اقدامات را می توان از قبل، حتی قبل از آن، برنامه ریزی کرد. جدول داده ها چگونه تشکیل می شود. این یک صرفه جویی در زمان عالی است.
2. اتوماسیون. عصر مدرن عصر اتوماسیون است. فقط کسی که می داند چگونه کار خود را به درستی خودکار کند می تواند درآمد زیادی کسب کند. به همین دلیل است که توانایی تسلط بر اکسل و عملکرد سامسلیمن به ویژه برای هر فردی که می خواهد شغلی ایجاد کند بسیار مهم است. دانستن یک تابع به شما امکان می دهد چندین عمل را به طور همزمان انجام دهید. و در اینجا به مزیت بعدی این ویژگی می رویم.
3. صرفه جویی در زمان. فقط به دلیل این واقعیت است که یک تابع چندین کار را همزمان انجام می دهد.
4. سادگی. علیرغم اینکه سینتکس به دلیل حجیم بودن در نگاه اول بسیار سنگین است، در واقع منطق این تابع بسیار ساده است. ابتدا یک محدوده از داده ها انتخاب می شود، سپس یک محدوده از مقادیر، که برای مطابقت با یک شرایط خاص بررسی می شود. و البته خود شرط نیز باید مشخص شود. و همینطور چندین بار. در واقع، این تابع تنها بر اساس یک ساختار منطقی است که آن را ساده تر از شناخته شده می کند VPR با وجود این واقعیت که می توان از آن برای اهداف مشابه استفاده کرد، همچنین با در نظر گرفتن تعداد بیشتری از معیارها. 

ویژگی های استفاده از تابع SUMIFS

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

1. می‌توانید از این نوع مقادیر به‌عنوان شرایطی برای انتخاب سلول‌ها برای افزودن بیشتر مقادیر موجود در آنها استفاده کنید: مقادیر عددی، عبارات بولی، مراجع سلولی و غیره. 
2. اگر متن، عبارات منطقی یا نشانه های ریاضی بررسی می شوند، این معیارها از طریق نقل قول مشخص می شوند.
3. نمی توان از اصطلاحات بیش از 255 کاراکتر استفاده کرد.
4. می توان از معیارهای تقریبی برای انتخاب مقادیر با استفاده از حروف عام استفاده کرد. علامت سوال برای جایگزینی یک کاراکتر استفاده می شود و علامت ضرب (ستاره) برای جایگزینی چندین کاراکتر مورد نیاز است. 
5. مقادیر بولی که در محدوده جمع قرار دارند به طور خودکار با توجه به نوع آنها به مقادیر عددی تبدیل می شوند. بنابراین، مقدار "TRUE" به یک و "FALSE" به صفر تبدیل می شود. 
6. اگر یک #VALUE! خطا در یک سلول ظاهر می شود، به این معنی است که تعداد سلول ها در محدوده شرایط و مجموع متفاوت است. باید مطمئن شوید که اندازه این آرگومان ها یکسان است. 

نمونه هایی از استفاده از تابع SUMIFS

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

محدوده دینامیکی جمع شرط

پس بیایید با مثال اول شروع کنیم. فرض کنید جدولی داریم که حاوی اطلاعاتی درباره نحوه کنار آمدن دانش آموزان با برنامه درسی در یک موضوع خاص است. مجموعه ای از نمرات وجود دارد، عملکرد در مقیاس 10 امتیازی ارزیابی می شود. وظیفه این است که نمره امتحان آن دسته از دانش آموزانی که نام خانوادگی آنها با حرف A شروع می شود و حداقل نمره آنها 5 است را پیدا کنید.

جدول به این شکل است.

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

بیایید استدلال ها را با جزئیات بیشتری شرح دهیم:

1. C3:C14 محدوده جمع ما است. در مورد ما، با محدوده شرط منطبق است. از آن نقاطی انتخاب می‌شوند که برای محاسبه مقدار استفاده می‌شوند، اما فقط مواردی که تحت معیارهای ما قرار می‌گیرند.
2. "> 5" اولین شرط ما است.
3. B3:B14 دومین محدوده جمع بندی است که برای مطابقت با معیار دوم پردازش می شود. می بینیم که هیچ تصادفی با محدوده جمع وجود ندارد. از این نتیجه می‌گیریم که محدوده جمع و محدوده شرط ممکن است یکسان باشند یا نباشند. 
4. "A*" دومین محدوده است که انتخاب نمره را فقط برای دانش آموزانی که نام خانوادگی آنها با A شروع می شود مشخص می کند. در مورد ما، ستاره به معنای هر تعداد کاراکتر است. 

پس از محاسبات به جدول زیر می رسیم.

همانطور که می بینید، فرمول مقادیر را بر اساس محدوده دینامیکی و بر اساس شرایط مشخص شده توسط کاربر خلاصه می کند.

جمع انتخابی بر اساس شرط در اکسل

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

خود جدول به این شکل است. 

برای تعیین نتیجه نهایی، به چنین فرمولی نیاز داریم.

=(СУММЕСЛИМН(D2:D14;A2:A14;»=июнь»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)+(СУММЕСЛИМН(D2:D14;A2:A14;»=август»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)))

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

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

تابع SUMIFS برای جمع کردن مقادیر در شرایط مختلف

حال بیایید مثال دیگری برای روشن شدن مطلب بیاوریم. در این حالت جدول مانند مورد قبلی باقی می ماند. 

از فرمول زیر استفاده می کنیم (اما آن را به صورت فرمول آرایه می نویسیم، یعنی از طریق کلید ترکیبی CTRL + SHIFT + ENTER وارد می کنیم).

=СУММ(СУММЕСЛИМН(D2:D14;B2:B14;»Товар_1″;C2:C14;{«Китай»;»Грузия»}))

پس از عملکرد سامسلیمن آرایه مقادیر را بر اساس معیارهای مشخص شده در فرمول (یعنی کشورهای چین و گرجستان) جمع می کند، آرایه حاصل با تابع معمول جمع می شود. جمع، که به صورت فرمول آرایه ای نوشته می شود.

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

حال بیایید نگاهی به جدول حاوی مجموع بیاندازیم.

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

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