Excel - 计算分布在多行中的数据的持续时间

发布于 2024-07-09 01:22:31 字数 1217 浏览 9 评论 0原文

我有一个电子表格，其中包含多个事务的数据集，每个事务都由子步骤组成，每个子步骤都有其发生的时间。步骤的数量和顺序可以是可变的。

我想找到每笔交易的持续时间。如果我可以在 Excel 中执行此操作，那就太好了，因为它已经采用了这种格式。如果在 Excel 中没有直接的方法来执行此操作，我会将其加载到数据库中并使用 SQL 进行分析。如果有一个 Excel 方法可以解决这个问题，那么它会节省几个小时的设置时间:)

我的数据的简化示例如下：

TransID、Substep、Time
1、步骤A，15:00:00
1、步骤B，15:01:00
1、步骤C，15:02:00
2、步骤B，15:03:00
2、步骤C，15:04:00
2、步骤E，15:05:00
2、步骤F，15:06:00
3、步骤C，15:07:00
3、步骤D，15:08:00
等等。

我想生成如下结果集：

TransID、Duration
1、00:02:00
2、00:03:00
3、00:01:00
我最初的尝试是使用一个额外的列，其中的

公式从开始时间减去结束时间，但没有重复的步骤数，或者相同的开始和结束步骤，我很难看出这个公式是如何工作的。

我还尝试根据此数据创建一个数据透视表，其中 ID 作为行，时间作为数据。我可以更改时间数据上的字段设置以返回分组值，例如计数或最大值，但我很难了解如何设置它以显示每个 ID 的 max(time) - min(time)，因此我为什么考虑转向 SQL。如果有人能指出我遗漏的任何明显内容，我将非常感激。

按照 Hobbo 的建议，我现在使用了一个数据透视表，其中 TransID 作为行，并两次添加了 Time 作为数据。将第一个字段的“时间”设置为“最大”，将第二个字段设置为“最小”后，可以在数据透视表外部添加一个公式来计算差异。我在这里忽略的一件事是相同的值可以多次添加到数据部分！

后续问题是我添加的公式的形式为 =GETPIVOTDATA("Max of Time",$A$4,"ID",1)-GETPIVOTDATA("Min of Time",$A$4,"ID" ,1)，复制和粘贴时，which 不会增加。解决方案是使用数据透视表工具栏关闭 GETPIVOTDATA 公式，或者在选择公式中的单元格时不要单击数据透视表，而是键入单元格引用（例如 =H4-G4）

原文

I have a spreadsheet with a dataset of a number of transactions, each of which is composed of substeps, each of which has the time that it occurred. There can be a variable number and order of steps.

I'd like to find the duration of each transaction. If I can do this in Excel then great, as it's already in that format. If there isn't a straight-forward way to do this in Excel, I'll load it into a database and do the analysis with SQL. If there is an Excel way round this it'll save a few hours setup though :)

A simplified example of my data is as follows:

TransID, Substep, Time
1, step A, 15:00:00
1, step B, 15:01:00
1, step C, 15:02:00
2, step B, 15:03:00
2, step C, 15:04:00
2, step E, 15:05:00
2, step F, 15:06:00
3, step C, 15:07:00
3, step D, 15:08:00
etc.

I'd like to produce a result set as follows:

TransID, Duration
1, 00:02:00
2, 00:03:00
3, 00:01:00
etc.

My initial try was with an extra column with a formula subtracting end time from start time, but without a repeating number of steps, or the same start and end steps I'm having difficulty seeing how this formula would work.

I've also tried creating a pivot table based on this data with ID as the rows and Time as the data. I can change the field settings on the time data to return grouped values such as count or max, but am struggling to see how this can be setup to show max(time) - min(time) for each ID, hence why I'm thinking about heading to SQL. If anyone can point out anything obvious I'm missing though, I'd be very grateful.

As suggested by Hobbo, I've now used a pivot table with TransID as the rows and twice added Time as the data. After setting the field settings on the Time to Max on the first and Min on the second, a formula can be added just outside the pivot table to calculate the differences. One thing I'd been overlooking here is that the same value can be added to the data section more than once!

A follow-on problem was that the formula I add is of the form =GETPIVOTDATA("Max of Time",$A$4,"ID",1)-GETPIVOTDATA("Min of Time",$A$4,"ID",1), whici doesn't then increment when copying and pasting. Solutions to this are to either use the pivot table toolbar to turn off GETPIVOTDATA formulae, or rather than clicking on the pivot table when selecting cells in the formula, type the cell references instead (e.g. =H4-G4)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

自控 2024-07-16 01:22:31

在公式“GETPIVOTDATA("Max of Time, $A$4, "ID", 1) - GETPIVOTDATA("Max of Time, $A$4, "ID", 1)' 中，单元格引用在符号“$”之间寻址'。例如 4 澳元。当单元格引用具有 $ 符号并将公式复制到其他单元格时，引用单元格不会自动更新。因此你得到相同的类型。

也许您按如下方式修改公式，然后将公式复制到其他单元格。公式应类似于：

“GETPIVOTDATA("最大时间，A4，"ID"，1) - GETPIVOTDATA("最大时间，A4，"ID"，1)"。

谢谢。

回复收藏 0 原文

懒猫 2024-07-16 01:22:31

您使用数据透视表的方法是正确的。将 TransID 作为行字段拖入，然后将 Time 的两个副本拖入数据透视表中作为数据字段；右键单击每个并指定 Min 作为其中一个的汇总函数，将 Max 指定为另一个的汇总函数。在数据透视表的右侧添加一个公式来计算差异。

替代文本 http://img296.imageshack.us/img296/5866/pivottableey5.jpg< /a>

“看起来不错，我遇到的唯一问题是我添加的公式的形式为 =GETPIVOTDATA("Max of Time, $A$4, "ID", 1) - GETPIVOTDATA("Max of Time, $ A$4，“ID”，1)。当我将其复制到下面的单元格时，1 不会更新为 2、3 等，因此它们都显示相同的时间 – Kris Coverdale “

使用数据透视表工具栏上的此按钮。关闭 GETPIVOTDATA 公式。

替代文本 http://img117.imageshack.us/img117/9937/pivottabletoolbarjn3.jpg< /a>

回复收藏 0 原文

栖迟 2024-07-16 01:22:31

也许像这样简单的查询。

SELECT TransID, DateDiff(mi, Min(Time),Max(Time)) AS Duration
FROM MyTable
GROUP BY TrandID

Maybe something as simple as a query like this.

SELECT TransID, DateDiff(mi, Min(Time),Max(Time)) AS Duration
FROM MyTable
GROUP BY TrandID

回复收藏 0 原文

烟若柳尘 2024-07-16 01:22:31

在 Excel 中：

  A     B        C
1 1, step A, 15:00:00
2 1, step B, 15:01:00
3 1, step C, 15:02:00
4 2, step B, 15:03:00
5 2, step C, 15:04:00
6 2, step E, 15:05:00
7 2, step F, 15:06:00
8 3, step C, 15:07:00
9 3, step D, 15:08:00

11 1, =max(if($A$1:$A$9=$A11,$C$1:$C$9,"")-min(if($A$1:$A$9=$A11,$C$1:$C$9,"")
12 2, =max(if($A$1:$A$9=$A12,$C$1:$C$9,"")-min(if($A$1:$A$9=$A12,$C$1:$C$9,"")

注意：公式是数组函数，因此编辑后按 ctrl-shift-enter。

In excel:

  A     B        C
1 1, step A, 15:00:00
2 1, step B, 15:01:00
3 1, step C, 15:02:00
4 2, step B, 15:03:00
5 2, step C, 15:04:00
6 2, step E, 15:05:00
7 2, step F, 15:06:00
8 3, step C, 15:07:00
9 3, step D, 15:08:00

11 1, =max(if($A$1:$A$9=$A11,$C$1:$C$9,"")-min(if($A$1:$A$9=$A11,$C$1:$C$9,"")
12 2, =max(if($A$1:$A$9=$A12,$C$1:$C$9,"")-min(if($A$1:$A$9=$A12,$C$1:$C$9,"")

note: formulas are array functions so press ctrl-shift-enter after editing them.

回复收藏 0 原文

懷念過去 2024-07-16 01:22:31

要添加到 Kibbee 的帖子，参考评论，您可以将 ADO 与 Excel 结合使用：

'From: http://support.microsoft.com/kb/246335 '

strFile = Workbooks(1).FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT TransID, DateDiff('n', Min([MyTime]),Max([MyTime])) AS Duration " _
         & "FROM [Sheet1$] GROUP BY TransID"

rs.Open strSQL, cn

'Write out to another sheet '
Worksheets(2).Cells(2, 1).CopyFromRecordset rs

编辑：我已更正了原始帖子中的一些错误，并将时间列的名称更改为 MyTime。时间是SQL中的保留字，导致查询困难。现在可以进行一个非常简单的测试。

To add to Kibbee's post, in reference to the comment, you can use ADO with Excel:

'From: http://support.microsoft.com/kb/246335 '

strFile = Workbooks(1).FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT TransID, DateDiff('n', Min([MyTime]),Max([MyTime])) AS Duration " _
         & "FROM [Sheet1$] GROUP BY TransID"

rs.Open strSQL, cn

'Write out to another sheet '
Worksheets(2).Cells(2, 1).CopyFromRecordset rs

EDIT: I have corrected some errors in the original post and changed the name of the time column to MyTime. Time is a reserved word in SQL and causes difficulties in queries. This now works on a very simple test.

回复收藏 0 原文

知你几分 2024-07-16 01:22:31

有时，在 Excel 中执行一次某项操作比重复执行某项操作要容易得多。

假设您只是想得到一两次答案，然后扔掉电子表格（而不是每晚运行它，或将其交给其他人运行），我将这样做。

我假设您的原始数据位于 A、B 和 C 列中，标题位于第 1 行，数据从第 2 行开始。

按 TransId 作为主键，按 Time 作为辅助键对表进行排序，两者均按升序排列。（如果不这样做，以下内容将不起作用。）

添加一个新列 D，标题为“持续时间”，其公式如下（Excel 公式没有格式或注释；我添加了这些内容来帮助解释，但它们需要删除）：

=IF(B2=B3,           // if this row's TransId is the same as the next one
    "",              // leave this field blank
    C3-              // else find the difference between the last timestamp and...
     VLOOKUP(        // look for the first value
        A2,          // matching this TransId
        A:C,         // within the entire table,
        3)           // Return the value in the third column - i.e. timestamp
    )

现在您想要的数据位于 D 列中，但不是您想要的格式。

选择列 AD 并复制它们。使用选择性粘贴将值仅复制到新工作表中。

删除新工作表中的 B 列和 C 列，只剩下 TransID 和 Duration。

按持续时间排序，使所有具有值的行彼此相邻。

仅对具有按 TransId 的值的行进行排序。

瞧，这就是您的解决方案！希望您不需要重复此操作！

ps 这是未经测试的

Sometimes it is possible to do something once in Excel far more easily than it is to do something repeatably.

Assuming you are just trying to get the answer once or twice, and then throw away the spreadsheet (as opposed to run it every night, or give it to someone else to run), here's how I would do it.

I assume your raw data is in columns A, B and C, with headings in row 1, and data starting in row 2.

Sort the table by TransId as your primary key, and Time as your secondary, both ascending. (The following won't work if this isn't done.)

Add a new column, D, titled Duration with a formula that like this (Excel formulae haven't formatting or comments; I have added those to help explain, but they need to be stripped out):

=IF(B2=B3,           // if this row's TransId is the same as the next one
    "",              // leave this field blank
    C3-              // else find the difference between the last timestamp and...
     VLOOKUP(        // look for the first value
        A2,          // matching this TransId
        A:C,         // within the entire table,
        3)           // Return the value in the third column - i.e. timestamp
    )

Now the data you want is in column D, but not in the format you want.

Select Columns A-D and copy them. Use Paste Special to copy the values only into a new worksheet.

Delete column B and column C in the new worksheet, so all is left is TransID and Duration.

Sort by Duration, to bring all the rows with values next to each other.

Sort only the rows with values by TransId.

Voila, and there is your solution! Hope you don't need to repeat this!

p.s. This is untested

回复收藏 0 原文

~没有更多了~