多条件小函数excel [英] multiple criteria small function excel

问题描述

我一直在使用具有多个条件的小函数并排除零在 excel 中找到至少 10 个值.

• 我有 4 列:人物、日期、时间和天气.
• 考虑到所有日期和天气不应该下雨，我想要一个人最少的时间.

我用过这个公式:

=small(if(and(person="a",hours,"<>0",weather,"<>rainy"),hours),no)

其中 no 代表 1-5 个数字.

解决方案

已经提供了一个数组公式解决方案；另一个标准公式(即非 CSE)方法可能是更新的

<小时>

¹ _{AGGREGATE 函数是在 Excel 2010 中引入的.它在早期版本中不可用.}

I am stuck in finding the least 10 values in excel using small function with multiple criteria and also excluding zeros.

• I have 4 columns, person, date, hours and weather.
• I want the least hours of a person considering all the dates and the weather should not be rainy.

I have used this formula:

=small(if(and(person="a",hours,"<>0",weather,"<>rainy"),hours),no)

where no represents the the 1-5 numbers.

解决方案

There is one array formula solution provided already; another standard formula (i.e. non-CSE) method could be the newer AGGREGATE¹ function which provides a layer of cyclic processing without having to finalize with CSE.

AGGREGATE has the option to ignore errors; by forcing any non-compliant combination of values into a #DIV/0! state, those values will be discarded from consideration. When all three conditions match, the denominator will be 1 and any number divided by 1 is unchanged.

=AGGREGATE(15, 6, C$2:C$13/((A$2:A$13=G$4)*(D$2:D$13=H$4)*(C$2:C$13<>0)), ROW(1:1))

Fill down for the second then third, etc. smallest. If you prefer to fill right, change ROW(1:1) to COLUMN(A:A) to attain a k increment laterally. Remember to 'anchor' the columns of the range and criteria references with $.

---

¹ _{The AGGREGATE function was introduced with Excel 2010. It is not available in earlier versions.}

这篇关于多条件小函数excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！