Tengo una hoja de cálculo con un conjunto de datos de una serie de transacciones, cada uno de los cuales está compuesto de sub-pasos, cada uno de los cuales tiene el momento en que se produjo. No puede ser una variable de número y orden de los pasos.

Me gustaría encontrar la duración de cada transacción. Si puedo hacer esto en Excel, entonces lo ideal, como ya en ese formato. Si no hay una sencilla manera de hacer esto en Excel, voy a cargar en una base de datos y realizar el análisis con SQL. Si hay un Excel el camino de ronda que este va a ahorrar un par de horas de instalación, sin embargo 🙂

Un ejemplo simplificado de mis datos es como sigue:

TransID, Subpaso, Tiempo

1, el paso, 15:00:00

1, el paso B, 15:01:00

1, el paso C, 15:02:00

2, el paso B, 15:03:00

2, el paso C, 15:04:00

2, el paso E, 15:05:00

2, el paso F, 15:06:00

3, el paso C, 15:07:00

3, el paso D, 15:08:00

etc.

Me gustaría producir un conjunto de resultados de la siguiente manera:

TransID, Duración

1, 00:02:00

2, 00:03:00

3, 00:01:00

etc.

Mi primer intento fue con una columna adicional con una fórmula de restar el tiempo de finalización de la hora de inicio, pero sin la repetición de una serie de pasos, o el mismo inicio y final de los pasos que estoy teniendo dificultad para ver cómo esta fórmula de trabajo.

También he intentado crear una tabla dinámica a partir de estos datos con la IDENTIFICACIÓN de las filas y el Tiempo como los datos. Puedo cambiar la configuración de campo en los datos del tiempo para volver agrupan valores tales como el recuento o max, pero estoy luchando para ver cómo esto puede ser configurado para mostrar max(hora): min(tiempo) para cada ID, por lo tanto ¿por qué estoy pensando en irse a SQL. Si alguien puede señalar algo obvio que me estoy perdiendo, sin embargo, yo estaría muy agradecido.

Según lo sugerido por Hobbo, ahora he utilizado una tabla dinámica con TransID como las filas y dos veces el Tiempo añadido, como el de datos. Después de establecer la configuración de campo en el Tiempo a Max en la primera y Min en el segundo, una fórmula puede ser añadido justo fuera de la tabla dinámica para calcular las diferencias. Una cosa que me había sido vista aquí es que el mismo valor puede ser añadido a la sección de datos más de una vez!

Un seguimiento sobre el problema era que la fórmula que puedo añadir es de la forma =GETPIVOTDATA(«Max de Tiempo»,$A$4,»ID»,1)-GETPIVOTDATA(«Min de Tiempo»,$A$4,»ID»,1), whici no incrementan al copiar y pegar. Las soluciones a esto hay que utilizar la tabla pivote de la barra de herramientas para desactivar GETPIVOTDATA fórmulas, o en lugar de hacer clic sobre la tabla dinámica cuando la selección de celdas en la fórmula, escriba las referencias de celda en lugar (por ejemplo, =H4-G4)

InformationsquelleAutor Kris C | 2008-12-01

6 Comentarios

  1. 1

    Que estaban a la derecha de las líneas con las tablas dinámicas. Arrastre en TransID como un campo de fila, a continuación, arrastre en dos copias de Tiempo como campos de datos en la tabla dinámica clic con el botón derecho en cada uno y especificar Min como el resumen de la función de uno y máximo para el otro. A la derecha de la tabla dinámica agregar una fórmula para calcular la diferencia.

    texto alt http://img296.imageshack.us/img296/5866/pivottableey5.jpg

    «Se ve bien, el único problema que tengo es que la fórmula que puedo añadir es de la forma =GETPIVOTDATA(«Max de Tiempo, $A$4, «ID», 1) – GETPIVOTDATA(«Max de Tiempo, $A$4, «ID», 1). Cuando copio que a las células a continuación, el 1 no se actualiza para 2, 3, etc, por lo que todos muestran la misma hora. – Kris Coverdale «

    Utilice este botón en la tabla pivote de la barra de herramientas para cambiar GETPIVOTDATA fórmulas de descuento.

    texto alt http://img117.imageshack.us/img117/9937/pivottabletoolbarjn3.jpg

    • Se ve bien, el único problema que tengo es que la fórmula que puedo añadir es de la forma =GETPIVOTDATA(«Max de Tiempo, $A$4, «ID», 1) – GETPIVOTDATA(«Max de Tiempo, $A$4, «ID», 1). Cuando copio que a las células a continuación, el 1 no se actualiza para 2, 3, etc, por lo que todos muestran la misma hora.
    • Hmmm – mi Excel (2003) no parece tener ese botón. Voy a echar un vistazo y ver si puedo encontrar un lugar similar en otros lugares
    • Ignorar que, descubierto 🙂 Escriba las referencias de celda (por ejemplo, =H4-G4) en lugar de hacer clic en las casillas para seleccionar en la fórmula.
    • Gracias por su ayuda – parece que debe escala muy bien a los datos reales estoy lidiando con el 🙂
    • Ah.. que el botón no es visible de forma predeterminada. Haga clic en la pequeña flecha de lista desplegable en la parte derecha de la barra de herramientas, «Agregar y Quitar botones’->’Pivote de la barra de herramientas’->’Generar GetPivotData’
  2. 2

    En su fórmula «GETPIVOTDATA(«Max de Tiempo, $A$4, «ID», 1) – GETPIVOTDATA(«Max de Tiempo, $A$4, «ID», 1)’ las referencias de celda se abordan entre el símbolo «$’. Por ejemplo $A$4. Cuando las referencias de celda de haber símbolo $ y copiar la fórmula a otra celda, a continuación, las celdas de referencia no se actualizan automáticamente. Por lo tanto, usted obtener el mismo tipo.

    Tal vez de modificar la fórmula siguiente y, a continuación, copiar la fórmula a las otras celdas. La fórmula debe ser como:

    «GETPIVOTDATA(«Max de Tiempo, A4, «ID», 1) – GETPIVOTDATA(«Max de Tiempo, A4, «ID», 1)».

    Gracias.

  3. 1

    Tal vez algo tan simple como una consulta como esta.

    SELECT TransID, DateDiff(mi, Min(Time),Max(Time)) AS Duration
    FROM MyTable
    GROUP BY TrandID
    
    • Yo podría hacer eso si me carga en una base de datos segura. Me preguntaba si se puede hacer en Excel y fue, simplemente, con vistas a algo. Si no, entonces la anulación de una pequeña base de datos e importar es el de mañana de la tarea 🙂
    • Creo que puede significar ‘n’, y no de mi.
  4. 1

    En 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,"")
    

    nota: las fórmulas son funciones de la matriz de tal forma que presione ctrl-shift-enter después de su edición.

  5. 1

    Para agregar a Kiev post, en referencia a la observación, puede utilizar ADO con 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: he corregido algunos errores en el post original y cambió el nombre de la columna de tiempo para MyTime. Tiempo es una palabra reservada de SQL y causa dificultades en las consultas. Esto ahora trabaja en una prueba muy simple.

    • Buena idea, me gusta. Funciona para una simple consulta – e.g «SELECCIONE TransID DE [Hoja1$] GRUPO POR TransID», pero no para la consulta en cuestión: «SELECCIONE TransID, DateDiff(mi, Min(el Tiempo),Max(Tiempo)) COMO la Duración DE [Hoja1$] GRUPO POR TransID». Supongo que ver con los formatos de tiempo, se va a investigar más…
    • Gracias por su ayuda – esto tengo mis respuestas con un poco de trabajo creo que, a pesar de la tabla dinámica que evita cualquier tipo de codificación y está en marcha y funcionando por lo que voy a usar este tiempo. Útil saber acerca de ADO con Excel – creo que puede encontrar usos para los que en el futuro!
  6. 0

    A veces es posible hacer algo una vez en Excel de una manera mucho más fácil de lo que es hacer algo repetiblemente.

    Suponiendo que usted está tratando de obtener la respuesta de una o dos veces y, a continuación, tire de la hoja de cálculo (como oposición a que se ejecute cada noche, o dar a alguien para que se ejecute), esto es lo que yo haría.

    Puedo asumir que sus datos estén en las columnas a, B y C, con los encabezados de la fila 1, y los datos a partir de la fila 2.

    Ordenar la tabla por TransId como clave principal, y a la Hora de su secundaria, tanto ascendente. (El siguiente no funcionará si no lo hace.)

    Agregar una nueva columna a, D, titulado Duración con una fórmula que, como este (Excel fórmulas no el formato o comentarios; he añadido a aquellos para ayudar a explicar, pero que necesitan ser recortados):

    =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
        )
    

    Ahora los datos que desea está en la columna D, pero no en el formato que usted desee.

    Seleccione las Columnas de la a a la D y copia de ellos. Usar Pegado Especial para copiar el valores sólo en una nueva hoja de cálculo.

    Eliminar la columna B y la columna C de la hoja de cálculo nueva, así que todo está a la izquierda es TransID y Duración.

    Ordenar por Duración, para traer a todas las filas con valores próximos el uno al otro.

    Ordenar sólo las filas con valores por TransId.

    Voila, y no es su solución! Espero que usted no se tenga que repetir este!

    p.s. Esto no está probado

    • Este es el tipo de solución, pensé Excel haría – voy a jugar con esto en contra de mi real los datos de mañana; si no puedo conseguir el ADO consultar cosa que va, esto se ve como debe hacer todo lo necesario. Necesito hacerlo hasta 10 veces, por lo que podría conseguir un poco doloroso m :S Oh excel…

Dejar respuesta

Please enter your comment!
Please enter your name here