☰

Suma cada segunda celda si la celda vecina no está vacía

tag-icon • tag-icon microsoft-excel microsoft-excel-2010
Suma cada segunda celda si la celda vecina no está vacía

He creado una hoja de horas en Excel para realizar un seguimiento de mis tiempos de trabajo. Todo funciona bastante bien, pero tengo un pequeño problema.

Mi hoja tiene el siguiente diseño ( In=comenzó a trabajar, Out=dejó de funcionar):

A          | B     | C     | D     | E     | F     | G
-----------+-------+-------+-------+-------+-------+------
Date       | In    | Out   | In    | Out   | In    | Out
01.03.2014 |       |       |       |       |       |       (weekend)
02.03.2014 |       |       |       |       |       |       (weekend)
03.03.2014 | 08:00 | 12:00 | 12:45 | 17:00 |       |
04.03.2014 | 08:45 | 13:30 | 13:45 | 17:45 |       |
05.03.2014 | 08:15 | 12:30 | 13:00 | 18:00 |       |
06.03.2014 | 08:00 | 12:30 | 13:00 | 17:30 |       |
...

Ahora me gustaría calcular el tiempo que trabajé cada día. Eso es fácil:

= SUM($C2; $E2; $G2) - SUM($B2; $D2; $F2)

Esto funcionará incluso si me salto el almuerzo a veces como esta:

A          | B     | C     | D     | E     | F     | G
-----------+-------+-------+-------+-------+-------+------
Date       | In    | Out   | In    | Out   | In    | Out
01.03.2014 |       |       |       |       |       |       (weekend)
02.03.2014 |       |       |       |       |       |       (weekend)
03.03.2014 | 08:00 | 12:00 | 12:45 | 17:00 |       |
04.03.2014 | 08:45 |       |       | 17:45 |       |
05.03.2014 | 08:15 | 12:30 | 13:00 | 18:00 |       |
06.03.2014 | 08:00 | 12:30 | 13:00 | 17:30 |       |
...

Pero no funciona durante el día en que ingresé la hora en que comencé a trabajar ( In), pero aún no ingresé la hora en que dejé de trabajar ( Out):

A          | B     | C     | D     | E     | F     | G
-----------+-------+-------+-------+-------+-------+------
Date       | In    | Out   | In    | Out   | In    | Out
01.03.2014 |       |       |       |       |       |       (weekend)
02.03.2014 |       |       |       |       |       |       (weekend)
03.03.2014 | 08:00 | 12:00 | 12:45 | 17:00 |       |
04.03.2014 | 08:45 |       |       | 17:45 |       |
05.03.2014 | 08:15 | 12:30 | 13:00 | 18:00 |       |
06.03.2014 | 08:00 | 12:30 | 13:00 |       |       |
...

Pues 06.03.2014esto resultará en un tiempo negativo.

Lo que me gustaría tener es el tiempo que realmente trabajé por la mañana, es decir, 04:30el 06.03.2014. Eso significa que necesito incluir solo aquellas columnas en la segunda suma (negativa) donde la columna vecina no está vacía. ¿Cómo puedo hacer esto de una manera general que aún admita saltarse la hora del almuerzo? Con "forma general" me refiero a una fórmula que también funcionaría para las columnas F, G y posiblemente H, I... Por lo tanto, no debería ser una fórmula muy larga de IFdeclaraciones.

Quería usar la función SUMIFo SUMIFS, pero hasta donde tengo entendido, solo acepta rangos como B1:G1, pero no acepta algo como B1;D1;F1, es decir, omitir cada segunda columna. No tengo idea de cómo puedo hacer eso porque no soy ningún experto en Excel... Esto es lo que probé hasta ahora, pero no funciona, solo me da #VALUE:

= SUM($C2; $E2; $G2) - SUMIF(($C2; $E2; $G2); "<>"; ($B2; $D2; $F2))

Esto debería significar: sumar C, E y G y restar la suma de B, D y F si las celdas correspondientes en C, E y G no están vacías. Sin embargo, los paréntesis alrededor de los rangos no parecen funcionar. ¿O el problema es otro?

Respuesta1

Me inspiré un poco en el enfoque de Madball pero sin la necesidad de una fila adicional. Use esto para la fila 2 copiada

=C2+E2+G2-B2-D2-F2+MOD(COUNT(B2:G2),2)*MAX(B2:G2)

Suma/resta los tiempos según sea necesario pero sumaatrásla última vez (ignorándola así) cuando hay un número impar de veces insertadas.

Respuesta2

Esto es lo que haría. Si se insertan más columnas, la fórmula se ajusta fácilmente:

  1. Suma, en la fila uno, una serie de 1,-1,1,-1... Esto se usa para que sepamos si es una suma o una resta.
  2. En el extremo derecho (H en su ejemplo), use esta fórmula: =IF(ISODD(COUNT(B3:G3)),SUMPRODUCT($B$1:$G$1,B3:G3)+MAX(B3:G3),SUMPRODUCT($B$1:$G$1,B3:G3))

Esto funciona en su ejemplo para todos los casos (excepto cosas raras como salir dos veces seguidas): ingrese la descripción de la imagen aquí

Explicación: SUMPRODUCT multiplica cada celda de la matriz por su compañera en la otra matriz y luego las suma todas. Entonces, el 6/3, hace 8*-1+12:30*1+13:00*-1+17:30*1+0*-1+0*1.

Si hay un número impar de entradas/salidas, vuelve a agregar el último (el más alto) de los tiempos para neutralizarlo.

Respuesta3

No estoy seguro de haber entendido completamente tu pregunta, pero esto funciona.

ingrese la descripción de la imagen aquí

Usé una declaración if simple

=SUM(IF(C2<>"",C2-B2,0),IF(E2<>"",E2-D2,0))

Esto funcionará incluso en días parciales (como en la fila 4, donde solo se trabajó una tarde).

Si tuviera que agregar un tercer conjunto de entrada/salida, actualice su fórmula de acuerdo

=SUM(IF(C2<>"",C2-B2,0),IF(E2<>"",E2-D2,0),IF(G2<>"",G2-F2,0))

Respuesta4

¿Qué tal si intentas reemplazar esto?

= SUM($C2; $E2; $G2)

con este:

= SUM(IF($C2="";TEXT(NOW(); "hh:mm");$C2); IF($E2="";TEXT(NOW(); "hh:mm");$E2); IF($G2="";TEXT(NOW(); "hh:mm");$G2))

Esta fórmula debería poner la hora actual en celdas vacías ($C2,$E2,$G2), lo que debería brindarle más tiempo de trabajo real. Después de todo, estás trabajando hasta ahora, ¿no?

información relacionada