Excel GETPIVOTDATA公式-如何在公式之间拖动并自动更改日期? [英] Excel GETPIVOTDATA formula - how to drag the formula across and change dates automatically?

问题描述

我需要创建一个从数据透视表中获取数据的公式，当我将公式拖到表中时，数据会自动更新(图2).

I need to create a formula that takes data from pivot table and the data is automatically update when I drag the formula across in my table (picture 2).

我的数据透视表如下:

我的表格如下:

我的GETPIVOTDATA公式如下:

=GETPIVOTDATA("Is it valid?",'Overall by Site-2'!$A$8,"Is it valid?","Valid","Week commencing",DATE(2019,1,14))

我尝试将"Week commencing",BK8-DATE(YEAR(BK8),1,0)+1)添加到我的代码中，但没有选择任何数据.

I tried to add "Week commencing",BK8-DATE(YEAR(BK8),1,0)+1) to my code but it doesn't pick any data.

谢谢.

推荐答案

使用VLOOKUP或INDEX/MATCH公式代替GETPIVOTDATA:

=VLOOKUP(BK8,'Overall by Site-2'!$A:$C,3,0)

或

=INDEX('Overall by Site-2'!$C:$C,MATCH(BK8,'Overall by Site-2'!$A:$A,0))

我个人更喜欢使用INDEX/MATCH，因为它不仅速度更快，而且效率更高，尤其是在处理大型数据集的情况下.

I personally prefer to use INDEX/MATCH as it is much faster method and also much more efficient, especially if you work with large data sets.

这篇关于Excel GETPIVOTDATA公式-如何在公式之间拖动并自动更改日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！