☰

¿Cómo puedo "SUMAR" varias columnas en Excel?

tag-icon • tag-icon microsoft-excel worksheet-function date-time
¿Cómo puedo "SUMAR" varias columnas en Excel?

Tengo un archivo delimitado por comas que incluye dos columnas, fecha/hora (que se importa como mm/dd/yyyy hh:mmformato personalizado de Excel) y un estado de 1 o 0. El estado representa que un equipo está encendido o apagado. Estoy intentando generar un gráfico que muestre horas arriba y abajo por día.

CONSIDERAR:

1/1/2012 00:00, 1
1/1/2012 03:00, 0
1/1/2012 14:00, 1
1/3/2012 00:00, 0

Esto me dice que el equipo estuvo activo durante tres horas, inactivo durante once horas y luego activo durante treinta y cuatro horas (en dos días calendario). Sin embargo, me gustaría generar un gráfico que muestre cuántas horas POR DÍA estuvimos arriba o abajo.

CONSIDERAR:

1/1 XXXXXXXXXXXXX-----------   (up 13, down 11)
1/2 XXXXXXXXXXXXXXXXXXXXXXXX   (up 24)

A mí me parece que necesito generar un conjunto de datos que sume HORAS por ESTADO por DÍA CALENDARIO... pero parece que no puedo encontrar una especie de tabla dinámica o SUM(IF(SUMIF(...)))combinación anidada para que funcione.

Lo más preocupante es tener en cuenta los cambios de fecha... en mi ejemplo anterior, dado que mi tiempo de actividad que comienza a las 14:00 del 1/1/2012 cruza la medianoche, necesito saber que 10 horas de tiempo de actividad se suman con el 1/1/2012 y 24 Las horas de actividad se totalizan el 2/1/2012.

Es posible que pueda hacer algo con una lista de calendario para controlar la suma de fechas, pero luego necesito una forma de 01/01/2012compararla 01/01/2012 03:00. Tiene que haber una manera de hacerlo, if(INTEGER-PORTIONS-OF-SERIAL-DATES-ARE-EQUAL,TOTAL-HOURS-IF-VALUE-IS_1,0)pero hasta ahora nada ha funcionado.

¿Alguna sugerencia? He estado luchando contra esto la mayor parte del día y necesito una nueva perspectiva.

Gracias

Respuesta1

¡Dios mío, esto es un desafío! Ojalá pudiera explicar esto mejor, pero tengan paciencia.

Mi solución requiere algo de espacio adicional (3 columnas para ser exactos) y se basa en algunas suposiciones:

  1. Los datos de la marca de tiempo tendrían filas alternas de 1 y 0, emulando un interruptor de encendido/apagado como el que describió (consulte la Imagen 1 a continuación).
  2. Las marcas de tiempo están ordenadas del más antiguo al más nuevo.

ingrese la descripción de la imagen aquí

En aras de la simplicidad, estoy usando horas enteras. Aunque puedes agregar marcas de tiempo a nivel de minutos si lo prefieres.

Columna Erealiza un seguimiento del número de horas que quedan para el día en cada marca de tiempo. Utilicé esto para "DIVIDIR" los tiempos transcurridos que cruzan la medianoche.

Columnas ON/OFF

Aquí está la fórmula en la columna ON:

=SUM(IF(AND($B3,A4<>"",INT($A4)=INT($A3)),($A4-$A3)*24,0),
     IFERROR(IF(AND($B3=1,INT($A3)<>INT($A4)),$E3,IF(AND($B2=1,INT($A3)<>INT($A2)),24-$E3,0)),0))

Esta fórmula suma dos valores:

1.El número de horas que el interruptor está activado si la entrada no es la última marca de tiempo del día; en caso contrario, 0.

2.El número de horas que quedanhastamedianoche si este es elúltimomarca de tiempo para el díaOel número de horas transcurridasdesdemedianoche si este es elprimeromarca de tiempo del día.

La fórmula en la columna APAGADO es similar, excepto que verifica si Estado = 0.

=SUM(IF(AND(NOT($B3),B4<>"",INT($A4)=INT($A3)),($A4-$A3)*24,0),
     IFERROR(IF(AND($B3=0,INT($A3)<>INT($A4)),$E3,IF(AND($B2=0,INT($A3)<>INT($A2)),24-$E3,0)),$B3*($A3-INT($A3))*24))

Para extraer la FECHA de la columna de marca de tiempo, utilicé la INT()función. Excel utiliza números enteros para representar fechas y decimales para representar horas (una fracción de un día o de 24 horas). Por ejemplo, 06/01/2012 18:00es igual a 41061.75(18 horas después de la medianoche es 18/24, que es 0,75).Creo que esto responde a tu último párrafo.

Consolidando las Horas

Aquí está la fórmula en elHoras encendidascolumna (Ver imagen a continuación). Es una fórmula matricial, por lo que debes ingresarla usando Ctrl+ Shift+ Entery luego copiarla:

=SUM((INT(stamps)=$G3)*hours_on)

En elHoras APAGADAcolumna:

=SUM((INT(stamps)=$G3)*hours_off)

¿Dónde
stampshay un rango con nombre que hace referencia alMarca de tiemporango (columnaAen mi ejemplo)
hours_ones un rango con nombre que se refiere alENrango (columnaC)
hours off es un rango con nombre que hace referencia alAPAGADOrango (columnaD)

Cuadro

Observe cómo para cada fecha, el número de horas ON y OFF suman 24.

ingrese la descripción de la imagen aquí

Si desea estudiar las fórmulas y el libro de trabajo, aquí tiene una copia:http://db.tt/KZgH7SFV

Respuesta2

Suponiendo que tiene marcas de tiempo en A3:A24 y estado en B3:B24 según la sugerencia de Kaze, puede obtener las horas [decimales] ONpara una fecha en D3 con esta fórmula

=(LOOKUP(D3+1,A$3:B$24)-SUMPRODUCT((INT(A$3:A$24)=D3)+0,MOD(A$3:A$24,1),B$3:B$24*2-1))*24

no se requieren columnas "auxiliares"....

Respuesta3

No es bonito, pero aquí tienes una solución:

Después de sus dos columnas, agregue una tercera columna definida como día ([columna de fecha])

       A       B    C  D
   ---------------------
1 | 1/1/2012 00:00, 1, 1
2 | 1/1/2012 03:00, 0, 1
3 | 1/1/2012 14:00, 1, 1
4 | 1/3/2012 00:00, 0, 3
 ...
99| 1/31/2012 11:23, 1,31

Luego puedes hacer columnas de suma definidas como

sumif(D1:D99, 1, C1:C99)

Lo cual, en el caso anterior, le mostrará la suma de los 1 solo cuando la columna D (previamente definida como la parte "Día" de la fecha y hora sea 1. Repita esa fórmula para cada día del mes (la parte que No estoy tan entusiasmado), y deberías tener tus valores.

Suma de horas el día 1 del mes

sumif(D1:D99, 1, C1:C99)

Suma de horas del día 2 del mes

sumif(D1:D99, 2, C1:C99)

Suma de horas del día 3 del mes

sumif(D1:D99, 3, C1:C99)

etcétera...

información relacionada