问从展开/折叠枢轴表- Excel中提取“隐藏”数据

EN

这个答案假设您不想重复PivotTable中的所有项目标签，从“报表布局”下拉到PivotTable "Design“选项卡。

可以使用AGGREGATE、SUMPRODUCT和OFFSET组合来构造B列中与当前单元格相同或更高的第一个非空白值的公式，如下所示：

=OFFSET($B2,SUMPRODUCT(AGGREGATE(14,6,ROW($B$1:$B$100)*--(ROW($B$1:$B$100)<=ROW())*--(LEN($B$1:$B$100)>0),1))-ROW(),0)

它怎麽工作?

从最外层的部分OFFSET($B2, VALUE, 0)开始--这将从单元格B2开始，然后按VALUE行向上或向下查找以获得值。

接下来，我们需要知道需要向上或向下查找多少行。现在，如果我们能够用数据计算出最底层的行，我们就可以从中减去当前的ROW()，给出OFFSET($B2, NON_BLANK-ROW(),0)。

因此，要完成任务，我们需要计算哪些行不是空白的，哪些行位于-或高于当前行，然后取其中最大的行。这将是一个ArrayFormula，但是我们可以使用SUMPRODUCT来正确地计算。为了找到最大的数字，我们可以使用MAX或LARGE，但是如果我们选择AGGREGATE(14,6,..,1)，则会得到较少的错误。( 14的意思是“我们想要k_th最大的数字”，6的意思是“忽略错误值”，而1是_k，所以“我们想要最大的数字，忽略错误”)

但是，我们要看的数字列表是什么，我没听到你问。好的，我们希望从我们的范围输出ROW (我使用的是$B$1:$B$100，因为使用整个B列将花费很长的时间重复计算)，与当前的ROW()进行比较，并检查LENgth是否大于0。最后两个是比较，所以让我们先把它们写出来：

ROW($B$1:$B100)<=ROW() 

和

LEN($B$1:$B$100)>0

我们希望使用--将TRUE和FALSE转换为1和0 -这意味着任何“坏”值都变为0，任何“好”值都大于0：

ROW($B$1:$B$100)*--(ROW($B$1:$B$100)<=ROW())*--(LEN($B$1:$B$100)>0)

这给出了行号，当行在(或)在当前行和列B不是空之前--如果其中任何一个是假的，那么我们将得到0。把它放在AGGREGATE中找出最大的数字：

AGGREGATE(14, 6, ROW($B$1:$B$100)*--(ROW($B$1:$B$100)<=ROW())*--(LEN($B$1:$B$100)>0), 1)

然后将其放入SUMPRODUCT中，强制Excel将其视为ArrayFormula，这就是您的NON_BLANK。这就给出了第一个公式，就在柱子的顶端。