multiple criteria small function excel

问题:

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.

回答1:

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.}

回答2:

Array formulas do not like AND or OR, so reduce them to their boolean values by multiplying them together, using your pseudo code:

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

Remember that this it an array formula and must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

So if person was column A, hours column B, and weather column C, put this in the first cell in which you want the answers:

=SMALL(IF((A1:A100 = "a")*(B1:B100 <> 0) * (C1:C100 <> "rainy"),B1:B100),Row(1:1))

Confirm with Ctrl-Shift-Enter and copy down the desired number of results. The Row(1:1) will iterate from 1 to 2 to 3 as it is copied down.