اگر در اکسل فقط می‌خواهید بدانید «بیشترین مقدارِ مطابق با چند شرط چیست؟» یا «کمترین مقدارِ مطابق با این شرایط کدام است؟»، لازم نیست داده‌ها را فیلتر و مرتب کنید. توابع MAXIFS و MINIFS دقیقاً برای همین کار ساخته شده‌اند و می‌توانند نتیجه را مستقیماً در یک سلول برگردانند.
چگونه در اکسل بیشترین مقدار را با چند شرط پیدا کنیم؟
تابع MAXIFS بزرگ‌ترین عددی را پیدا می‌کند که شرایط تعیین‌شده را داشته باشد. مزیت اصلی آن این است که برای پیدا کردن نتیجه، نیازی به مخفی کردن ردیف‌ها، فیلتر کردن ستون‌ها یا مرتب‌سازی جدول ندارید. نتیجه مستقیماً در سلولی که فرمول را وارد کرده‌اید نمایش داده می‌شود و با تغییر داده‌های جدول نیز دوباره محاسبه خواهد شد.
ساختار کلی تابع به شکل زیر است:
=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
در این ساختار، max_range محدوده‌ای است که می‌خواهید بزرگ‌ترین عدد آن پیدا شود. criteria_range محدوده‌ای است که شرط روی آن بررسی می‌شود و criteria خود شرط را مشخص می‌کند.
به عنوان مثال، فرض کنید جدولی از مشخصات کامپیوترها دارید و ستون‌های آن شامل سازنده، قیمت، حافظه RAM و فضای ذخیره‌سازی است. اگر بخواهید بیشترین مقدار RAM را بین کامپیوترهایی که قیمتشان حداکثر ۱۵۰۰ دلار است پیدا کنید، می‌توانید از این فرمول استفاده کنید:
=MAXIFS(tblComputers[RAM], tblComputers[Price], "<=1500")
در این فرمول، tblComputers[RAM] محدوده‌ای است که عدد نهایی از آن انتخاب می‌شود. tblComputers[Price] محدوده‌ای است که اکسل برای بررسی شرط به آن نگاه می‌کند و "<=1500" مشخص می‌کند که فقط قیمت‌های کمتر یا مساوی ۱۵۰۰ در نظر گرفته شوند.
اگر نتیجه 32GB باشد، یعنی بیشترین ظرفیت RAM در میان کامپیوترهایی که قیمتشان در محدوده تعیین‌شده قرار دارد، 32GB است.
شرط می‌تواند متن، عدد یا یک عبارت مقایسه‌ای باشد. به عنوان مثال می‌توانید از شرط‌هایی مانند "Dell" یا 1500 و همچنین عباراتی مانند ">50" استفاده کنید.
استفاده از چند شرط در تابع MAXIFS اکسل
یکی از کاربردی‌ترین قابلیت‌های MAXIFS امکان استفاده از چند شرط به صورت هم‌زمان است. فرض کنید علاوه بر محدودیت قیمت، می‌خواهید فقط کامپیوترهای شرکت Dell بررسی شوند.
فرمول به این شکل تغییر می‌کند:
=MAXIFS(tblComputers[RAM], tblComputers[Price], "<=1500", tblComputers[Manufacturer], "Dell")
اکسل فقط ردیف‌هایی را بررسی می‌کند که هر دو شرط را داشته باشند؛ یعنی قیمت حداکثر ۱۵۰۰ باشد و سازنده نیز Dell باشد.
می‌توانید شرط دیگری هم اضافه کنید. به عنوان مثال اگر کامپیوتر باید حداقل 512GB فضای ذخیره‌سازی داشته باشد، فرمول به شکل زیر خواهد بود:
=MAXIFS(tblComputers[RAM], tblComputers[Price], "<=1500", tblComputers[Manufacturer], "Dell", tblComputers[Storage], ">=512")
در این حالت هر سه شرط باید برای یک ردیف برقرار باشند تا مقدار RAM آن ردیف در محاسبه قرار بگیرد. این روش برای جدول‌های فروش، محصولات، موجودی کالا، مشخصات سخت‌افزار و گزارش‌های مالی بسیار کاربردی است.
نکته مهم این است که محدوده اصلی و تمام محدوده‌های شرط باید از نظر اندازه و شکل با یکدیگر هماهنگ باشند. ناسازگاری محدوده‌ها می‌تواند باعث خطای #VALUE! شود.
پیدا کردن کمترین مقدار با تابع MINIFS در اکسل
اگر به جای بیشترین مقدار به دنبال کمترین مقدار هستید، تابع MINIFS تقریباً دقیقاً با همان منطق کار می‌کند. تفاوت اصلی این است که به جای بزرگ‌ترین مقدار، کوچک‌ترین مقدار مطابق با شرایط را برمی‌گرداند.
ساختار تابع MINIFS به صورت زیر است:
=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
به عنوان مثال فرض کنید جدولی از نتایج مسابقات دارید و می‌خواهید سریع‌ترین زمان ثبت‌شده برای مسابقه 5K در گروه سنی 40 تا 49 سال را پیدا کنید:
=MINIFS(tblRaces[Time], tblRaces[Event], "5K", tblRaces[Age Group], "40-49")
در اینجا اکسل فقط رکوردهایی را بررسی می‌کند که مسابقه آنها 5K و گروه سنی آنها 40-49 باشد و سپس کمترین زمان را از میان آنها انتخاب می‌کند.
می‌توانید شرایط بیشتری نیز اضافه کنید. به عنوان مثال می‌توان جنسیت، محل برگزاری یا هر ستون دیگری را به عنوان شرط وارد کرد.
همین روش برای داده‌های فروش نیز کاربرد دارد. فرض کنید می‌خواهید ارزان‌ترین قیمت یک محصول را در یک شهر خاص و از میان کالاهای موجود پیدا کنید. کافی است محدوده قیمت را در ابتدای MINIFS قرار دهید و شرایط موردنظر را بعد از آن اضافه کنید.
شرط MAXIFS و MINIFS را از داخل سلول تغییر دهید
لازم نیست شرط‌ها همیشه مستقیماً داخل فرمول نوشته شوند. یکی از روش‌های کاربردی‌تر این است که شرط‌ها را در سلول‌های جداگانه قرار دهید تا بتوانید بدون ویرایش فرمول، نتیجه را تغییر دهید.
فرض کنید در سلول H2 نوع مسابقه و در سلول I2 گروه سنی قرار دارد. در این صورت می‌توانید فرمول را به شکل زیر بنویسید:
=MINIFS(tblRaces[Time], tblRaces[Event], H2, tblRaces[Age Group], I2)
حالا اگر مقدار H2 را از 5K به 10K تغییر دهید، نتیجه نیز خودکار تغییر می‌کند. همین اتفاق با تغییر مقدار I2 نیز رخ می‌دهد.
این روش برای ساخت داشبورد و گزارش‌های تعاملی بسیار مفید است، چون کاربر می‌تواند معیار موردنظرش را از داخل سلول تغییر دهد و نتیجه بدون دستکاری فرمول به‌روزرسانی شود.
اگر علاوه بر مقدار کمینه یا بیشینه، بخواهید نام فرد یا محصول مربوط به آن را هم نمایش دهید، می‌توانید از FILTER در کنار این توابع استفاده کنید. به عنوان مثال اگر نتیجه MINIFS در سلول J2 قرار داشته باشد، برای نمایش نام تمام افرادی که همان زمان را ثبت کرده‌اند می‌توانید بنویسید:
=FILTER(tblRaces[Runner], tblRaces[Time]=J2)
اگر چند نفر دقیقاً یک مقدار داشته باشند، FILTER می‌تواند همه موارد منطبق را برگرداند.
MAXIFS و MINIFS در چه نسخه‌ای از اکسل وجود دارند؟
توابع MAXIFS و MINIFS در نسخه‌های جدید اکسل در دسترس هستند و مایکروسافت آنها را برای Excel 2019 و نسخه‌های جدیدتر از جمله Microsoft 365 ارائه کرده است. بنابراین اگر از Excel 2016 یا نسخه‌های قدیمی‌تر استفاده می‌کنید، ممکن است این توابع را در اختیار نداشته باشید.
در نسخه‌های قدیمی‌تر می‌توان با ترکیب توابعی مانند MAX یا MIN و IF به نتیجه مشابه رسید، اما فرمول‌ها پیچیده‌تر هستند. اگر نسخه جدید اکسل در اختیار دارید، استفاده از MAXIFS و MINIFS معمولاً خواناتر و ساده‌تر است.
در عمل، مزیت مهم این دو تابع فقط کوتاه‌تر شدن فرمول نیست. آنها اجازه می‌دهند سؤال موردنظر را مستقیماً از داده‌ها بپرسید؛ مثلاً «بیشترین فروش در یک منطقه با وضعیت پرداخت‌شده چقدر بوده؟» یا «کمترین قیمت یک محصول در یک بازه زمانی چقدر است؟» بدون اینکه ظاهر جدول اصلی را با فیلتر و مرتب‌سازی تغییر دهید.
گیم پلنت