计算与 Excel 数据透视表中上一个日期的差异 [英] Calculate difference from previous date in Excel pivot table
问题描述
我有一张如下表.在此表中，模型"列包含文件类型信息.
I have a table like below. In this table, the "model" column contains file type information.
我们记录任何文件类型(AFP/Arch/等)的最新更新日期以及我们输入这些值的日期.
We log the latest updated date of any file Type(AFP/Arch/ etc..) along with the date of the day that we are inputting these values.
例如，如果我今天输入值并且我发现文件 AFP 是昨天更新的.该行会像
For example, if I'm inputting the values today and I found that the file AFP is updated yesterday. The row would be like
|File type| |Date of Input| |updated date|
| AFP | | 2018.05.09 | | 2018.05.08 |
使用它，我想创建一个数据透视表，显示更新"列与上一个日期"的差异.上例中所需的值是1"(今天和昨天之间的差值).但是使用显示值作为Difference from"选项不会给我正确的日期值.
Using this I would like to create a pivot table that shows me the Difference of the column "Update" from the Previous "Date". The desired values in the above example would be "1"(the difference between today & yesterday). But using the show value as "Difference from" option doesn't give me proper values with dates.
请在下面找到日期.
| Model | Date | Update | | |
|---------------|------------|------------|---|---|
| AFP | 2018.03.05 | 2018.02.26 | | |
| AFP | 2018.03.07 | 2018.02.26 | | |
| AFP | 2018.03.09 | 2018.02.26 | | |
| AFP | 2018.03.14 | 2018.02.26 | | |
| AFP_FP | 2018.03.07 | 2018.03.06 | | |
| AFP_FP | 2018.03.09 | 2018.03.09 | | |
| AFP_FP | 2018.03.14 | 2018.03.09 | | |
| Arch | 2018.03.05 | 2018.02.22 | | |
| Arch | 2018.03.07 | 2018.02.22 | | |
| Arch | 2018.03.09 | 2018.02.22 | | |
| Arch | 2018.03.14 | 2018.03.13 | | |
| EL | 2018.03.05 | 2018.03.01 | | |
| EL | 2018.03.07 | 2018.03.07 | | |
| EL | 2018.03.09 | 2018.03.09 | | |
| EL | 2018.03.14 | 2018.03.09 | | |
| PO HD | 2018.03.05 | 2018.03.02 | | |
| PO HD | 2018.03.07 | 2018.03.07 | | |
| PO HD | 2018.03.09 | 2018.03.09 | | |
| PO HD | 2018.03.14 | 2018.03.14 | | |
| PO HP | 2018.03.05 | 2018.03.02 | | |
| PO HP | 2018.03.07 | 2018.03.07 | | |
| PO HP | 2018.03.09 | 2018.03.09 | | |
| PO HP | 2018.03.14 | 2018.03.14 | | |
| PO PL | 2018.03.05 | 2018.03.02 | | |
| PO PL | 2018.03.07 | 2018.03.07 | | |
| PO PL | 2018.03.09 | 2018.03.09 | | |
| PO PL | 2018.03.14 | 2018.03.14 | | |
| RCP | 2018.03.05 | 2018.02.26 | | |
| RCP | 2018.03.07 | 2018.02.26 | | |
| RCP | 2018.03.09 | 2018.02.26 | | |
| RCP | 2018.03.14 | 2018.02.26 | | |
| SPW() | 2018.03.05 | 2018.02.26 | | |
| SPW() | 2018.03.07 | 2018.02.26 | | |
| SPW() | 2018.03.09 | 2018.02.26 | | |
| SPW() | 2018.03.14 | 2018.02.26 | | |
| STR | 2018.03.05 | 2018.02.22 | | |
| STR | 2018.03.07 | 2018.02.22 | | |
| STR | 2018.03.09 | 2018.02.22 | | |
| STR | 2018.03.14 | 2018.02.22 | | |
| STR - Patriot | 2018.03.09 | 2018.03.07 | | |
| STR - Patriot | 2018.03.14 | 2018.03.07 | | |
| TD PL | 2018.03.05 | 2017.07.11 | | |
| TD PL | 2018.03.07 | 2017.07.11 | | |
| TD PL | 2018.03.09 | 2017.07.11 | | |
| TD PL | 2018.03.14 | 2017.07.11 | | |
推荐答案
将 Model 药丸拖到行和 日期 药丸到列后(看来你已经这样做了已经)，您应该将更新拖到值，按总和汇总，并将值显示为差异，基本字段日期和基本项目<强>上一个
After dragging Model pill to rows and Date Pill to Columns (which seems that you have done this already), you should drag the Update to values, summarize it by Sum and Show Value as Difference From with base fields Date and Base item previous
最终截图如下.只有您希望 14.03.2018 X Arch 的值是 21，我的计算是 19:
The final screenshot is as follows. Only that your desired value for 14.03.2018 X Arch is 21, my calculation is 19:
这篇关于计算与 Excel 数据透视表中上一个日期的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
