Using the Excel SMALL function with filtering crit

问题:

I have a list of prices for products that are variations (different size, color) of multiple parent product. Some products (for reasons not important to this question) have a price of zero.

I'm trying to write an excel formula that will return the lowest price for each parent product that is not zero

I've got this far:

=IF(SUMIFS(I:I,A:A,A3)=0,0,IF(AND(SUMIFS(I:I,A:A,A3)>0,MINIFS(I:I,A:A,A3)>0),MINIFS(I:I,A:A,A3),SMALL(IF(A:A=A3,I:I),2)))

Where I:I is the price column and A:A is the parent reference column

This works in all cases except where I have multiple variations of a parent product that have a zero value.

I need variables in a SMALL or MINIF function that will: A. Only include the values for a single parent product in the calculation B. Ignore any zeros in the calculation of the lowest value

Any help would be appreciated.

回答1:

You can use a 'traditional' PivotTable to do this. See ashleedawg's great answer.

You can also use a 'OLAP' PivotTable that's based off something called the Data Model to do it, that has a small advantage over the 'traditional' PivotTable in that you don't need to clear the PageFilter after refreshing. (More on this at the bottom of my answer).

Any Excel version from Excel 2013 later has the DataModel built in by default, and you also get access to the DataModel in 2010 if you install Microsoft's free PowerPivot add-in. Using the DataModel, here's what you need to do:

Turn your data into an Excel Table using the Ctrl + T keyboard shortcut:

Make a PivotTable out of it, but be sure to check the Add this data to the Data Model option:

Add Group to the Rows pane of the PivotTable, then right-click on Table1 in the fields pane and select Add Measure...:

Give the new Measure a name (I've called it Min Without Zero) and type =CALCULATE(MIN(Table1[Price]),Table1[Price]>0) in the Formula box then push OK:

Put a tick in the Min Without Zero checkbox in the PivotTable fields list.

Bing! The PivotTable shows the min (excluding zero) of each group by price.

Edit: I'd probably use ashleedawg's answer if I was doing this myself. But there's one caveat: If you add additional data in future and refresh, then you need to clear the filter and reapply the zero exclusion to be absolutely sure that any new numbers in the data are included. By way of a demonstration, if I filter out zero from a PageField, then I get just the result I expect:

...and here's how the filter looks:

But if I add new data and refresh, note that nothing has changed in the PivotTable:

...and if I look at the filter I can see why:

That's because when you deselect something from a filter, you're not actually saying "Give me everything except this thing" but rather "Give me the other things currently visible in this list, but leave any new things unselected."

Easily enough fixed: Just clear the filter and deselect zero again (or write some VBA code to do it automatically on refresh).

回答2:

You can use a Pivot Table to easily create a dynamic list of minimums. Blank cells are ignored by default, and zero's (or any other number you want) can be excluded a few ways; I think the easiest would be using the Pivot Table's Filter feature.

_{They say a picture is worth a thousand words so an animated GIF must be worth a million... :)}

---

More Information:

• Office.com : Filter data in a PivotTable

• Office.com : Display or hide zero values

• tips.net : Excluding Zero Values from a PivotTable

回答3:

If the input data is sorted based on the parent reference level you can simply

• use offset to find the required parent reference cells containing the price
• Find the k-th smallest value using the SMALL formula (i.e. the smallest value)
• Put a if condition to give you the 2nd smallest value if the smallest is 0

Place this in the 1st row

=IF(SMALL(OFFSET($I$1,MATCH(C1,$C:$C,0)-1,0,COUNTIF($C:$C,C1)),1)=0,SMALL(OFFSET($I$1,MATCH(C1,$C:$C,0)-1,0,COUNTIF($C:$C,C1)),2),SMALL(OFFSET($I$1,MATCH(C1,$C:$C,0)-1,0,COUNTIF($C:$C,C1)),1))

回答4:

If you want a formula approach, you can use an IF statement to return a value if that value is over zero, and otherwise return FALSE, as functions like MIN conveniently ignore FALSE.

As per my other answer I recommend you turn the data into an Excel Table first with the Ctrl + T keyboard shortcut, as shown below. Then in a summary table you can use the following array-entered formula in E2:

=MIN(IF(Table1[Price]>0,IF(Table1[Group]=D2,Table1[Price])))

(Array-entered means you push Ctrl + SHIFT+ ENTER instead of pushing ENTER like you usually do)

And here's the result: