Excel VBA:如何使用pivot表计算字段，使用“…计数”字段

The goal of my pivot table is to calculate MTBF (Mean Time Between Failures). It is a very simple calculation used in manufacturing to asses machine performance:

我的pivot表的目标是计算MTBF(在故障之间的平均时间)。这是一种在制造过程中用来评估机器性能的非常简单的计算方法:

MTBF = machine_uptime / number_of_stops

Ex: If a machine runs for 840 minutes with 10 stops or breakdowns, the MTBF is 84 minutes.

如果一台机器运行840分钟，有10个停站或故障，MTBF为84分钟。

This is my ideal pivot table layout:

这是我理想的数据透视表布局:

| Date  |Sum of Uptime|Count of Stops|    MTBF    |
|-------|-------------|--------------|------------|
| 08/01 |   1101.4    |      6       |    184     |
| 08/02 |   1068.0    |      7       |    153     |

This is how I generate the pivot table programmatically. However, my question still stands even I was creating this table manually via the GUI.

这就是我如何以编程方式生成pivot表的方式。然而，我的问题仍然是，我正在通过GUI手动创建这个表。

Dim pTable as PivotTable
Set pTable_MTBF = Utilities.CreatePivotTable(ws, "pTable_MTBF", "data", ws.Cells(1, 1))
pTable_MTBF.PivotFields("Date").orientation = xlRowField
pTable_MTBF.AddDataField pTable_MTBF.PivotFields("Uptime"), "Sum of Uptime", xlSum
pTable_MTBF.AddDataField pTable_MTBF.PivotFields("Stops"), "Count of Stops", xlCount
' pTable_MTBF.CalculatedFields.Add "MTBF", "=Uptime/'Count of Faults' (something like this?)
' pTable_MTBF.AddDataField pTable_MTBF.PivotFields("MTBF"), "MTBF "

The 'uptime' field is a sum of uptime entered with each stop, and the 'stops' field is a count of the stops entered each day. My question is, how can I set up the MTBF calculated field to successfully calculate these value? What formula should I enter in order to achieve this?

“正常运行时间”字段是每次停止时输入的正常运行时间的总和，“停止”字段是每天输入的停止计数。我的问题是，如何设置MTBF计算字段来成功计算这些值?为了达到这个目的，我应该输入什么公式?

Thanks.

谢谢。

1 个解决方案

#1

When you do a calculate field, I don't know of any way to do anything other than a sum. The good news is that in your example this is easily overcome by adding another column to your source data -- call it "StopCount" and fill it with all 1's. If your data is in a table, you can use the formula =1 and it will automatically size as your data sizes.

当你做一个计算场时，我不知道除了求和还有什么别的方法。好消息是，在您的示例中，可以通过向源数据添加另一列轻松克服这一问题——将其称为“StopCount”，并将其填满所有的1。如果数据在表中，可以使用公式=1，它将自动根据数据大小进行大小调整。

Once that's done a standard calculated field should work:

一旦完成了一个标准的计算字段应该是:

pTable_MTBF.PivotFields("Date").Orientation = xlRowField
pTable_MTBF.AddDataField pTable_MTBF.PivotFields("Uptime"), "Sum of Uptime", xlSum
pTable_MTBF.AddDataField pTable_MTBF.PivotFields("Stops"), "Count of Stops", xlCount
pTable_MTBF.CalculatedFields.Add "MTBF", "=Uptime/StopCount", True
pTable_MTBF.PivotFields("MTBF").Orientation = xlDataField