多条件小功能excel [英] multiple criteria small function excel

问题描述

我被困在使用具有多个条件的小型函数中并且不包括零的excel中，找到了excel中至少10个值。

• 我有4个列，人，日期，小时和天气。


• 我希望一个人考虑所有日期的最少时数，而且天气不应该下雨。

我使用了以下公式：

  = small（if（and （person = a，hours，<> 0，天气，<>多雨），小时），否）
  

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

解决方案

有一个已经提供的数组公式解决方案；另一个标准公式（即非CSE）方法可以是更新的

---

¹ _{The AGGREGATE函数。早期版本中不可用。}

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屋！