将Excel SMALL函数与过滤条件一起使用并忽略零 [英] Using the Excel SMALL function with filtering criteria AND ignoring zeros

问题描述

我有一个价格列表，该价格是多个母产品的变体(大小，颜色).一些产品(出于对该问题不重要的原因)的价格为零.

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.

我正在尝试编写一个Excel公式，该公式将为每个非零的父产品返回最低价格

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

我已经走了这么远:

= 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)))

=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)))

其中I:I是价格列，A:A是父参考列

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.

我需要一个SMALL或MINIF函数中的变量，该变量将: 答:仅在计算中包括单个母产品的值 B.在计算最小值时忽略任何零

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

任何帮助将不胜感激.

推荐答案

您可以使用传统"数据透视表来执行此操作.参见ashleedawg的出色答案.

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

您还可以使用基于数据模型"的"OLAP"数据透视表来执行此操作，这与传统"数据透视表相比有一个小优势，因为您无需在刷新后清除PageFilter. (有关更多信息，请参见我的答案的底部).

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

Excel 2013以后的任何Excel版本默认都内置有DataModel，如果您安装Microsoft的免费PowerPivot加载项，则在2010年也可以访问DataModel.使用DataModel，这是您需要做的:

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:

使用 Ctrl + T 键盘快捷键将数据转换为Excel表:

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:

将组添加到数据透视表的行"窗格中，然后在字段窗格中的表1"上单击鼠标右键，然后选择"添加度量... ":

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

给新的Measure一个名称(我称它为Min Without Zero)，然后在公式"框中键入=CALCULATE(MIN(Table1[Price]),Table1[Price]>0)，然后按OK:

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！数据透视表按价格显示每组的最小值(不包括零).

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

如果我自己这样做，我可能会使用ashleedawg的答案.但是有一个警告:如果以后添加其他数据并刷新，则需要清除过滤器并重新应用零排除，以确保绝对包含数据中的任何新数字.作为演示，如果我从PageField过滤掉零，那么我得到的只是我期望的结果:

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

很容易修复:只需清除过滤器，然后再次取消选择零(或编写一些VBA代码以在刷新时自动执行).

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

这篇关于将Excel SMALL函数与过滤条件一起使用并忽略零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！