数据透视表 时间汇总月份_数据透视表时间问题

数据透视表 时间汇总月份

It’s pivot table time! First, we’ll take a look two common problems with time values in pivot tables. Then I’ll show you a couple of ways to save time when working with pivot tables.

这是关键的表时间！ 首先，我们来看一下数据透视表中时间值的两个常见问题。 然后，我将向您展示几种使用数据透视表节省时间的方法。

数据透视表时间值 (Pivot Table Time Values)

If you’re showing time values in a pivot table, here are a couple of things that can go wrong:

如果要在数据透视表中显示时间值，则可能会出错：

• The total times are way too low

  总时间太少了

• The times are rounded, and don’t show the tenths or hundredths of a second

  时间是四舍五入的，不显示十分之一或百分之一秒

There are easy fixes for both of those problems, and the details and a sample file are on the Pivot Table Time Values page on my Contextures site. The quick instructions are below.

这两个问题都有简单的解决方法，详细信息和示例文件位于Contextures网站上的“ 数据透视表时间值”页面上。 快速说明如下。

数据透视表中的时间总计不正确 (Incorrect Time Totals in Pivot Table)

If time totals look wrong, change the number format for the pivot table. Use a custom format that totals the hours, such as [h]:mm

如果时间总计看起来不正确，请更改数据透视表的数字格式。 使用总计小时的自定义格式，例如[h]：mm

数据透视表中的舍入时间 (Rounded Times in Pivot Table)

If a pivot table shows times formatted with tenths of a second, or hundredths of a second, they might be rounded, and show zeros instead.

如果数据透视表显示的时间格式为十分之一秒或百分之一秒，则它们可能会四舍五入，而显示为零。

To fix that, add another field in the source data, linked to the original time field. Format the new field as General, and use that field in the pivot table. Then, format the new pivot field, to show the tenths of a second, or hundredths of a second.

要解决此问题，请在源数据中添加另一个字段，该字段链接到原始时间字段。 将新字段的格式设置为“常规”，然后在数据透视表中使用该字段。 然后，格式化新的枢轴字段，以显示十分之一秒或百分之一秒。

使用切片器添加数据透视表值 (Add a Pivot Table Value With Slicers)

Last week, I showed you how to use Slicers to filter your data. That makes it easy to see the specific data that people need, and keeps your data safely hidden.

上周，我向您展示了如何使用切片器来过滤数据 。 这样可以轻松查看人们所需的特定数据，并安全隐藏您的数据。

I’d never thought of using Slicers to select fields for a Pivot table though, and Krisztina Szabó, from The Frankens Team’s blog, explains how to do that.

不过，我从没想过要使用切片器为数据透视表选择字段 ，弗兰肯斯团队博客中的KrisztinaSzabó解释了如何做到这一点。

Kris created a list of fields that can be added to the pivot table’s Value area, and made a pivot table based on that list. With a Slicer, you can select one or more of those fields, and they are added to the pivot table. That’s a great way to save time when working with pivot tables! No more scrolling through a long list in the Pivot Table Field List.

Kris创建了可以添加到数据透视表的“值”区域的字段列表，并基于该列表创建了数据透视表。 使用切片器，您可以选择一个或多个这些字段，并将它们添加到数据透视表中。 这是在使用数据透视表时节省时间的好方法！ 不再滚动查看“数据透视表”字段列表中的长列表。

You can download Kris’ sample file to see how it works, and modify the code slightly to use this technique in your pivot tables. I liked Kris’ technique so much that I added another Slicer, to change the summary function for the Value fields.

您可以下载Kris的示例文件以查看其工作原理，并稍做修改代码以在数据透视表中使用此技术。 我非常喜欢Kris的技术，以至于我添加了另一个Slicer，以更改Value字段的摘要功能。

数据透视表生成器 (Pivot Table Builder)

And speaking of time, it can take lots of time to build your pivot tables, and then rebuild them, if someone messes them up.

说到时间，如果有人弄乱了数据透视表，可能需要花费大量时间来构建数据透视表，然后重新构建它们。

To help you save time, my new Pivot Table Builder add-in lets you create and store a variety of pivot table layouts in a workbook. Then, you can quickly build a new pivot table, or update an existing pivot table, based on any one of those layouts.

为了帮助您节省时间，新的数据透视表生成器加载项使您可以在工作簿中创建和存储各种数据透视表布局。 然后，您可以根据任何一种布局快速构建新的数据透视表或更新现有的数据透视表。

It’s a lot quicker, and less frustrating, than rebuilding from scratch every time.

与每次从头开始重建相比，它更快，更省心。

翻译自: https://contexturesblog.com/archives/2016/04/07/pivot-table-time-problems/

数据透视表 时间汇总月份