محاسبه کمترین مقدار بدون در نظر گرفتن صفر

پیرو سوال یک کاربر در گروه تلگرامی ما با توجه به اهمیت موضوع در این مقاله قصد توضیح روشهای محاسبه تابع MIN و MAX داده ها بدون احتساب داده های صفر را داریم یا به عبارتی تابع min و max را به صورت شرطی می خواهیم محاسبه نماییم(Min if, Max if).

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

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

تابع MIN:

تابع MIN در اکسل مقدار کمینه را از بین چند آرگومان یا محدوده‌ای از سلول‌ها برمی‌گرداند.نحوه استفاده:

MIN(number1, [number2], …)=

مثال: اگر مجموعه اعداد 5، 3، 9 و 7 را داشته باشید، می‌توانید کمینه آنها را با استفاده از تابع MIN به شکل زیر محاسبه کنید: =MIN(5, 3, 9, 7) خروجی: 3
همچنین می‌توانید از محدوده‌های سلولی نیز استفاده کنید.

برای مثال، اگر مقادیر در سلول‌های A1 تا A4 قرار داشته باشند، می‌توانید از روش زیر استفاده کنید: MIN(A1:A4)=

تابع MAX:

تابع MAX نیز بیشینه را از بین چند آرگومان یا محدوده‌ای از سلول‌ها برمی‌گرداند.
نحوه استفاده:

MAX(number1, [number2], …)=

مثال: اگر مجموعه اعداد 5، 3، 9 و 7 را داشته باشید، می‌توانید بیشینه آنها را با استفاده از تابع MAX به شکل زیر محاسبه کنید: =MAX(5, 3, 9, 7) خروجی: 9
همچنین می‌توانید از محدوده‌های سلولی نیز استفاده کنید. برای مثال، اگر مقادیر در سلول‌های A1 تا A4 قرار داشته باشند، می‌توانید از روش زیر استفاده کنید: MAX(A1:A4)=

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

اما مسئله وقتی مشکل ساز میشود که بخواهیم کمترین مقدار رو بصورت شرطی بدست بیاوریم!!

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

ابتدا صورت مسئله را به صورت زیر مطرح می کنیم و سپس پاسخ های ممکن را بررسی می نماییم:

فرض کنید در سلول A1:A100 اعدادی را وارد کردیم و اکنون میخواهیم به دو سوال زیر پاسخ دهیم:

کمترین عدد این محدوده بدون در نظر گرفتن اعداد صفر کدام است؟

کمترین عدد بزرگتر از صفر این محدوده کدام است؟

با در نظر گرفتن فرض فوق راهکار های متفاوتی داریم که ابتدا آن را بیان کرده و سپس به بیان مشکل آن می پردازیم.

1- روش اول:

=MIN(IF(A1:A100>0,A1:A100))

*فرمول فوق و سایر فرمولهایی که در ادامه به آن می پردازیم کلا آرایه ای هستند و برای فرمول های آرایه ای لازم است در انتهای فرمول به جای زدن اینتر کلیدهای Ctrl+Shift+Enter را با هم بزنید.

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

2- روش دوم:

=(A1:A100>0)*MIN(A1:A100)

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

3- روش سوم:

=SMALL(A1:A100,COUNTIF(A1:A100,0)+1)

در صورتی که می خواهید کمترین عدد غیر صفر را بدست آورید فرمول فوق پاسخگوی شما خواهد بود.

4- روش چهارم:

=SMALL(A1:A101,COUNTIF(A1:A100,0)+COUNTIF(A1:A100,”<0″)+1)

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

اما فرمول های گفته شده در روش سوم و چهارم در صورتی که محدوده اعداد هم شامل عدد منفی و هم عدد صفر باشد با مشکل روبرو خواهد شد و بدرستی عمل نخواهد کرد.

5- روش پنجم:

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

=IF(COUNTIF(A1:A100,”<0″)>=1,MIN(A1:A100),SMALL(A1:A100,COUNTIF(A1:A100,0)+COUNTIF(A1:A100,”<0″)+1))

6- روش ششم:

در صورتی که می خواهید کمترین عدد بزرگتر از صفر را بدست آورید و محدودیت های گفته شده را نداشته باشید میتوانید از فرمول زیر استفاده کنید.

=SMALL(A1:A100,COUNTIF(A1:A100,0)+COUNTIF(A1:A100,”<0″)+1)

ضمنا همه موارد گفته شده را برای بدست آوردن Maximum با شرایط مذکور نیز می توانید بدست آورید و فقط کافیست در فرمول های فوق بجای Min از Max استفاده کرده و بجای Small از Large استفاده نمایید.

در پایان ذکر این نکته ضروریست که اکسل در آخرین نسخه اکسل 2016 توابع minif و minifs و maxif و maxifs را اضافه کرده است اما برای اینکه بتوانیم در سایر ورژن ها نیز از فایل خود استفاده نماییم باید از فرمولهای گفته شده استفاده کنیم. همچنین بزودی افزونه ای را به شما معرفی خواهیم کرد که ضمن این که این توابع را در خود دارد بسیاری دیگر از مواردی که در اکسل به راحتی قابل انجام نیست را به اکسل شما اضافه می کند.

نکات و خطاهای این توابع :

1. در صورتی که تمام آرگومان‌ها عددی نباشند، تابع MIN و MAX مقدار خطا (#VALUE!) را برمی‌گردانند. بنابراین، اطمینان حاصل کنید که تمام آرگومان‌ها اعداد باشند.
2. اگر تعداد آرگومان‌ها برابر با صفر باشد، تابع MIN و MAX مقدار خطا (#NUM!) را برمی‌گردانند.
   در صورتی که در محدوده‌های سلولی استفاده می‌کنید، مطمئن شوید که محدوده‌ها به درستی تنظیم شده باشند. برای مثال، اگر محدوده‌ای به صورت غلط تعیین شده باشد و شامل سلول‌هایی با مقادیر غیرعددی باشد، خروجی ممکن است نامعتبر باشد.
3. ممکن است در صورتی که تعداد آرگومان‌ها بسیار زیاد باشد، انتخاب وارد کردن آرگومان‌ها در فرمول‌ها سخت و زمان‌بر باشد. در این صورت، می‌توانید از توابع دیگری مانند MINIFS و MAXIFS استفاده کنید که امکان استفاده از شرط‌های بیشتری را برای انتخاب کمینه و بیشینه ارائه می‌دهند.
4. برای استفاده از توابع MIN و MAX در فرمول‌های پیچیده‌تر، می‌توانید از عملگرهای ریاضی مانند +، -، * و / به همراه آرگومان‌های مورد نیاز استفاده کنید.
   با درک و استفاده صحیح از توابع MIN و MAX در اکسل، می‌توانید کمینه و بیشینه مقادیر را به طور سریع و آسان محاسبه کنید.