☰

Summiere über jede zweite Zelle, wenn die Nachbarzelle nicht leer ist

tag-icon • tag-icon microsoft-excel microsoft-excel-2010
Summiere über jede zweite Zelle, wenn die Nachbarzelle nicht leer ist

Ich habe eine Zeiterfassung in Excel erstellt, um meine Arbeitszeiten zu erfassen. Das funktioniert alles ganz gut, aber ich habe ein kleines Problem.

Mein Blatt hat das folgende Layout ( In= hat begonnen zu arbeiten, Out= hat aufgehört zu arbeiten):

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 |       |
...

Nun möchte ich die Zeit berechnen, die ich täglich gearbeitet habe. Das ist ganz einfach:

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

Das funktioniert sogar, wenn ich manchmal das Mittagessen auslasse, und zwar wie folgt:

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 |       |
...

Es funktioniert jedoch tagsüber nicht, wenn ich die Uhrzeit eingegeben habe, zu der ich mit der Arbeit begonnen habe ( ), die Uhrzeit, zu der ich mit der Arbeit aufgehört habe ( ), Injedoch noch nicht eingegeben habe :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 |       |       |
...

Denn 06.03.2014dadurch entsteht eine negative Zeit.

Ich hätte gern die Zeit, die ich tatsächlich am Morgen gearbeitet habe, also 04:30am 06.03.2014. Das heißt, ich muss in die zweite (negative) Summe nur die Spalten einbeziehen, deren Nachbarspalte nicht leer ist. Wie kann ich das auf eine allgemeine Weise tun, die das Auslassen der Mittagszeit trotzdem unterstützt? Mit „allgemeiner Weise“ meine ich eine Formel, die auch für die Spalten F, G und möglicherweise H, I ... funktioniert. Es sollte also keine laaaange Formel mit IFAnweisungen sein.

SUMIFIch wollte die Funktion oder verwenden SUMIFS, aber soweit ich verstanden habe, akzeptiert sie nur Bereiche wie B1:G1, aber nichts wie B1;D1;F1, also das Überspringen jeder zweiten Spalte. Ich habe keine Ahnung, wie ich das machen kann, da ich überhaupt kein Excel-Experte bin... Das habe ich bisher versucht, aber es funktioniert nicht, es gibt mir nur #VALUE:

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

Das müsste heißen: Summe über C, E und G bilden und Summe aus B, D und F abziehen, wenn die entsprechenden Zellen in C, E und G nicht leer sind. Die Klammern um die Bereiche scheinen allerdings nicht zu funktionieren. Oder liegt das Problem woanders?

Antwort1

Ich habe mich von Madballs Ansatz inspirieren lassen, aber ohne die Notwendigkeit einer zusätzlichen Reihe. Verwenden Sie dies für Reihe 2, kopiert nach unten

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

Es addiert/subtrahiert die Zeiten nach Bedarf, fügt aber hinzuzurückdie späteste Zeit (und ignoriert sie daher), wenn eine ungerade Anzahl von Einfügungen vorhanden ist.

Antwort2

Ich würde folgendermaßen vorgehen. Wenn weitere Spalten eingefügt werden, lässt sich die Formel leicht anpassen:

  1. Fügen Sie in Zeile eins eine Reihe von 1, -1,1, -1 hinzu. Dies wird verwendet, damit wir wissen, ob es sich um eine Addition oder Subtraktion handelt.
  2. Ganz rechts (H in Ihrem Beispiel) verwenden Sie diese Formel: =IF(ISODD(COUNT(B3:G3)),SUMPRODUCT($B$1:$G$1,B3:G3)+MAX(B3:G3),SUMPRODUCT($B$1:$G$1,B3:G3))

Dies funktioniert in Ihrem Beispiel in jedem Fall (außer in merkwürdigen Fällen, wie z. B. zweimaliges Verlassen hintereinander): Bildbeschreibung hier eingeben

Erklärung: SUMPRODUCT multipliziert jede Zelle im Array mit ihrem Partner im anderen Array und addiert dann alles zusammen. Am 3.6. ergibt das also 8*-1+12:30*1+13:00*-1+17:30*1+0*-1+0*1.

Wenn es eine ungerade Anzahl von Ein-/Ausgängen gibt, wird die letzte (höchste) Zeit wieder addiert, um dies zu neutralisieren.

Antwort3

Ich bin nicht sicher, ob ich Ihre Frage vollständig verstehe, aber das funktioniert

Bildbeschreibung hier eingeben

Ich habe eine einfache if-Anweisung verwendet

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

Dies gilt sogar für Teiltage (z. B. Reihe 4, in der nur ein Nachmittag gearbeitet wurde).

Wenn Sie einen dritten Satz von Ein-/Ausgängen hinzufügen möchten, aktualisieren Sie Ihre Formel entsprechend

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

Antwort4

Wie wäre es, wenn Sie versuchen, dies zu ersetzen:

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

mit diesem:

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

Diese Formel sollte die aktuelle Zeit in leere Zellen ($C2,$E2,$G2) eintragen, wodurch Sie mehr reale Arbeitszeit erhalten. Schließlich arbeiten Sie bis jetzt, oder nicht?

verwandte Informationen