如何通过数据透视表中的值返回标头 [英] How to return the header by value in a PivotTable

问题描述

我有一个这样的数据透视表:

I have a PivotTable like this:

Sum of Gf_Amount |  Column Labels               
                 |  2015    |        |        |         | Grand Total
Row Labels       |  17-Mar  | 18-Mar | 19-Mar | 20-Mar  |
3601             |  20      | 20     |        |         | 40
10386            |  35      |        |        |         | 35
76301            |  5       |        |        |         | 5
80941            |          |        |        | 10      | 10     
205738           |          |        | 5      |         | 5
219576           |          |  15    |        |         | 15
Grand Total      |  60      | 35     | 5      | 10      | 110

我想要做的是找到最后一个非空列，然后根据该值返回日期.例如:对于ID 3601，结果应为2015 18-Mar.

What I want do is find the last non-empty column and return the date according to the value. For example: for ID 3601 the result should be 2015 18-Mar.

当前，我知道如何使用=LOOKUP(9.99E+307,B6:E6)查找最后一个非空列.对于ID 3601，它为我提供了20，这是正确的.但是，当我使用时:

Currently I know how to find the last non-empty column by using =LOOKUP(9.99E+307,B6:E6). For ID 3601 it gives me 20 which is correct. However when I use:

=INDEX($B$5:$E$5,MATCH(LOOKUP(9.99E+307,B6:E6),B6:E6,0))  

找到标题，它给了我17-Mar，它是 first 20的对应标题.此外，我写的公式甚至都不能给我年份.

to find the header, it gives me 17-Mar which is the corresponding header for the first 20. Besides, the formula I wrote can't even give me the year.

任何人都可以帮助我，以便我找到日期和年份吗? (它不必在数据透视表中.您可以将其复制并粘贴到普通表中.)

Can anyone help me out so I can find the date and year? (It doesn't have to be in PivotTable. You can copy and paste it in a normal table.)

推荐答案

我猜您的列标签是日期索引，格式为dd-mmm，因此无需查找因此显示的2015:

I'm guessing that your column labels are date indices formatted as dd-mmm so there is no need to find the 2015 that is displayed hence:

 =INDEX($5:$5,MATCH(1E+100,A6:E6))  

格式为dd-mmm-yyyy的

并可能抄写下来可能很适合.

formatted as say dd-mmm-yyyy and presumably copied down may suit.

这是 MATCH 函数，如果没有可选参数，则无法在列表中找到匹配项，它将返回列表中最后一个条目的索引–非常有用，例如此处时代！因此，所有大数字"(它有很多版本，例如您使用的9.99E+307)所做的都是向MATCH输入一个如此大的数字，以至于不可能找到它(强制选择最后一个条目) .

It is a peculiarity (perhaps never really intended) of the MATCH function that, without the optional argument, where it can’t find a match in a list it returns the index of the last entry in the list – very useful, as here, at times! So all the "big number" (there are lots of versions of it – for example the one you used 9.99E+307) does is feed MATCH a number so large it is never likely to find it (to force selection of the last entry).

我喜欢1E+100，这是一个简短易记的 googol ，并且为其衍生".从理论上讲，9.99E+307更好，因为它更接近Excel可以处理的最大数字: 9.99999999999999E + 307 但是

I like 1E+100, a googol, as short and easy to remember, and for its ‘derivation’. 9.99E+307 is theoretically better as closer to the largest number Excel can handle: 9.99999999999999E+307 but

10,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000

10,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000

对我来说足够大-我不希望有一个大于或等于9.99E + 307的数字工作.

for me is big enough – I don’t expect ever to want to work with a number bigger than that and smaller than or equal to 9.99E+307.

这篇关于如何通过数据透视表中的值返回标头的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！