使用两个过滤后的列在Excel中使用数据透视表查找中位数 [英] Finding median using pivot table in Excel using two filtered columns

问题描述

我在作业类型"中有A列. 我有B列为体验范围". 我的工资列在C栏. 这些位于名为数据"的工作表中.

I have column A for Job Type. I have column B for Experience Range. I have column C for salary. These are in a sheet named 'Data'.

我创建了一个数据透视表，其中作业类型"为行，体验范围"为列. (在另一张纸上)

I have created a pivot table with Job Type as rows and Experience Range as columns. (in another sheet)

我想在数据透视表中找到每个单元格的中位数.

I want to find median for each cell in the Pivot Table.

我该怎么做?

例如我如何找到所有具有少于2年"(B1单元)经验的阶段经理"(A2单元)的薪水中位数?

e.g. how do I find the median of Salary for all 'Stage Manager' (cell A2) with 'less than 2 years' (cell B1) experience?

请帮助.谢谢！

推荐答案

您可以将*用作和，将+用作或. FALSE编码为0，TRUE编码为1.

You can use * for and and + for or. FALSE is coded as 0 and TRUE as 1.

1. 0*1 = 1，因此如果仅满足一个条件，它将为FALSE(AND)
2. 1+0或1+1 is >0，因此，如果仅满足一个条件，它将仍然是>0(OR)

1. 0*1 = 1, so if only one condition is met it will be FALSE(AND)
2. 1+0 or 1+1 is >0, so if only one condition is met, it will still be >0 (OR)

您可以使用此事实对条件是否为> 0进行测试，然后转换数组，以便如果条件为true，则该数组为数组，否则为.基本上，这将消除所有不满足条件的行(使它们在任何数组公式中均视为空白)

You can use this fact to do a test as to whether your condition is >0 and then transform your array, so that if the condition is true, the array is the array but otherwise it equal . This will basically eliminate all rows where the conditions are not met (making them count as blank in any array formula)

您可以在许多AND和OR条件下使用此刻度.

You can use this tick for a large number of AND and OR conditions.

如果使用名称管理器命名数组，则可以非常轻松地非常快速地更改条件并创建模拟数据透视表.

If you use name manager to name your arrays, then you can very easily vary conditions very fast and create a simulated pivot table.

然后您可以使用聚合函数忽略隐藏的行(例如，聚合可以计算中位数，百分位数和其他内容)，然后过滤器功能可以有效地用作枢轴过滤器.

You can then use the aggregate function to ignore hidden rows (e.g. aggregate can calculate median, percentiles and other stuff), and then the filter function can effectively work as a pivot filter.

我通常将第一列设置为测试列，以测试行是否被隐藏，如果可见则返回1，否则返回0.然后，将该条件构建到我的条件堆栈中，以充当附加的AND开关.这意味着您的公式突然对过滤器作出反应，只考虑过滤出的值.

I usually make my first column a test column that tests if a row is hidden or not and returns 1 if visible and 0 otherwise. Then I build that condition into my condition stack to act as an additional AND switch. This means suddenly your formulas react to the filter to only consider values that are filtered in.

这对于在大量数据上进行多条件计算而无需在每个过滤器之后手动进行计算时非常有用-计算在进行过滤时即刻进行！

This stuff is really great to do multi-conditional calculations on a large amount of data without the need to manually calculate after each filter - the calculations happen on the fly, as you filter!

计算可能需要3-5秒的时间，具体取决于您拥有多少条件和原始数据的大小，因此其速度不如数据透视表快，但非常方便.

Calculations can take 3-5 seconds depending on how many conditions you have and the size of your raw data, so not quite as fast as a pivot table, but very convenient.

这篇关于使用两个过滤后的列在Excel中使用数据透视表查找中位数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！