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

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

 

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

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

 

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

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

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

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

 

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

 

۱- روش اول:

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

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

 

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

 

۲- روش دوم:

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

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

 

۳- روش سوم:

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

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

 

۴- روش چهارم:

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

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

 

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

 

۵- روش پنجم:

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

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

۶- روش ششم:

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

 

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

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

 

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

 

نویسنده: محمود بنی اسدی