Supportnet Computer
Planet of Tech

Supportnet / Forum / Tabellenkalkulation

Mehrere Zeitdifferenzen trotzt Leerfelder richtig berechnen





Frage

Hallo Exel-Freunde, ich bin noch Anfänger, was Exel anbelangt. Meine Tabelle soll ein paar Zeitdifferenzen korrekt berechnen. Die Zeile schaut in etwa so aus C3 = Von D3 = Bis G3 = Von H3 = Bis K3 = Von L3 = Bis In Von/Bis wird eine Zeitspanne eingegeben z. B. 12:10/12:25. Mit der Formel =D3-C3 kann ich die auch in S3 gut berechnen. Allerdings sollen alle 3 (es sind noch mehr) Zeitdifferenzen berechnet werden. Ich bin auf die Formel =(D3-C3)+(H3-G3)+(L3-K3) gekommen. Das funktioniert insoweit, dass ein korrekter Wert ausgegeben wird, wenn in allen Feldern eine Zeitangabe vorhanden ist. Wenn in einem Feld noch keine Zeitangabe vorhanden ist, dann kommt im Ergebnisfeld ein Fehler (#######). Ich habe im Forum recherchiert. Eine Möglichkeit wäre, alle Zeitfelder so zu konfigurieren, dass bereits 00:00 drinsteht. Das soll aber nicht sein. Eine andere Möglichkeit wäre wohl eine Bedingung. Ich habe mich kurz in die WENN-Funktion eingelesen, weiß aber nicht, wie ich die 3 Zeitdifferenzen verschachteln muß. Vielleicht gibt es auch noch einen anderen Lösungsansatz. Vielen Dank für Eure Hilfe! Gruß Torsten

Antwort 1 von Saarbauer

Hallo,

leider sind durch einen Systemabsturz die Antworten verlorengegangen, hat sich das Problem erledigt oder besteht es immer noch?

Gruß

Helmut

Antwort 2 von fantalight

Hallo Helmut,
deine ist die erste Antwort auf meine Frage; also sprich: immer noch keine Lösung!

Danke schonmal für einen Tip!
Gruß
Torsten

Antwort 3 von M.O.

Hallo Torsten,

hier mein Vorschlag:

=WENN(ISTLEER(ODER(C3;D3));D3-C3;0)+WENN(ISTLEER(ODER(H3;G3));H3-G3;0)+WENN(ISTLEER(ODER(K3;L3));L3-K3;0)

Kannst du natürlich für jede noch zu berechnende Zeit entsprechend ergänzen.

Gruß

M.O.

Antwort 4 von rainberg

Hallo Torsten,

das geht auch ohne WENN-Abfrage.

Wenn Du nur 3 Zeitspannen berechnen willst reicht das

=REST(D3-C3;1)+REST(H3-G3;1)+REST(L3-K3;1)

Hast Du noch mehr Zeitspannen, die immer den gleichen Spaltenabstand haben, funktioniert folgendes

=SUMMENPRODUKT(((REST(SPALTE(C:AB);4)=0)-(REST(SPALTE(C:AB);4)=3))*C3:AB3)

Diese Formel berechnet 7 Zeitspannen im Spaltenbereich C bis AB. (kannst Du noch erweitern)
Wenn als Ergebnis Zeiten >23:59:59 zu erwarten sind, dann formatiere die Ergebniszelle mit "[hh]:mm".

Gruß
Rainer

Antwort 5 von M.O.

Hallo Torsten,

vergiß mein Posting - es klappt so nicht :-(((.
Ich war etwas zu vorschnell.

Gruß

M.O.

Antwort 6 von M.O.

Hallo Torsten,

hier mein Vorschlag mit Wenn (gestestet):

=WENN(D3-C3<0;0;D3-C3)+WENN(H3-G3<0;0;H3-G3)+WENN(L3-K3<0;0;L3-K3)

Gruß

M.O.

Antwort 7 von rainberg

Hallo Torsten,

ich muss mich ebenfalls korrigieren.
Meine zweite Forrmel rechnet falsch, wenn eine oder mehrere der Zeitspannen mitternachtsüberschreitend ist.

so rechnet sie richtig:

=REST(SUMMENPRODUKT((REST(SPALTE(C:AB);4)=0)*C3:AB3) -SUMMENPRODUKT((REST(SPALTE(C:AB);4)=3)*C3:AB3);1)

@ M.O.
unter diesen Bedingungen rechnet Deine Formel übrigens auch falsch.

Gruß
Rainer

Antwort 8 von M.O.

@ Rainer,

stimmt, diese Konstellation hatte ich leider nicht bedacht :-((.

Gruß

M.O.

Antwort 9 von fantalight

@Rainer, @M.O.

Zuerst einmal vielen Dank für Eure Hilfe und den gezeigten Ehrgeiz. Die REST-Formel ...

=REST(D3-C3;1)+REST(H3-G3;1)+REST(L3-K3;1)

...funktioniert; auch wenn ich gern verstehen würde, warum das mit der ganzzahligen Division so hinhaut.


Diese Formel:
=REST(SUMMENPRODUKT((REST(SPALTE(C:AB);4)=0)*C3:AB3) -SUMMENPRODUKT((REST(SPALTE(C:AB);4)=3)*C3:AB3);1)

habe ich nicht zum Laufen gebracht. In welches Ergebnisfeld muß ich die Formel denn einfügen? IN das Feld nach AB also AC?

Über Mitternacht muß übrigens nicht gerechnet werden. Wenn die Zeitspanne über Mitternacht hinausgeht, dann soll eine neue Zeile für den neuen Tag genutzt werden. Hilfreich wäre beim Eintrag einer Zeit über Mittenacht im Feld bis eine Fehlermeldung(nur wenn davor eine Zeit kleiner als Mitternacht war).

DAnke nochmals für Eure Hilfe!
Gruß
Torsten

Antwort 10 von rainberg

Hallo Torsten,


hast Du beachtet, dass die Zeitspannen immer durch zwei Spalten von einander getrennt sind?
Wenn nein, funktioniert die Formel natürlich nicht.

Oder könnte es sein, dass in den Spalten zwischen den Zeitspannen Textwerte eingetragen sind, was aus Deiner Frage nicht hervor ging?
Wenn ja, müsste die Formel wie folgt angepasst werden.

=REST(SUMMENPRODUKT((REST(SPALTE(C:AB);4)=0)*(ISTZAHL(C3:AB3));C3:AB3) -SUMMENPRODUKT((REST(SPALTE(C:AB);4)=3)*(ISTZAHL(C3:AB3));C3:AB3);1)

Gruß
Rainer

Antwort 11 von rainberg

Hallo Torsten,

habe die Hälfte vergessen zu erwähnen.

Die Formel kannst Du in eine beliebige Zelle, die außerhalb von C3:AB3 liegt, eintragen.

Da Du keine mitternachtüberschreitende Zeiten hast, genügt auch diese Formel:

=SUMMENPRODUKT((REST(SPALTE(C:AB);4)=0)*(ISTZAHL(C3:AB3));C3:AB3) -SUMMENPRODUKT((REST(SPALTE(C:AB);4)=3)*(ISTZAHL(C3:AB3));C3:AB3)

Gruß
Rainer

Antwort 12 von fantalight

Hallo Rainer,

wo ist in de Formel definiert, dass zwischen den Zeitfeldern 2 andere Zellen sein müssen? Mein jetziger Tabellenaufbau schaut so aus, dass es sieben aufeineranderfolgende Spaltenreihen VON/BIS gibt. Also

VON|BIS|VON|BIS|VON|BIS|....

Wie muß ich Deine Formel anpassen?

Danke nochmals und Entschuldigung der Nachfrage. Ich möchte die Formel nur gern verstehen und habs halt noch nicht begriffen, obwohl ich mir die verwendeten Funktionen in der OH schon angesehen habe.

Gruß
Torsten

Antwort 13 von rainberg

Hallo Torsten,

schreibe mal folgende Formel in A1

=REST(SPALTE();4)=0

und folgende Formel in A2

=REST(SPALTE();4)=3

nun kopierst Du beide Formeln nach rechts bis in Spalte AB oder auch weiter.
Du wirst nun feststellen, dass in den Spalten, die Du in Deiner Ausgangsfrage angegeben hast, immer ein WAHR steht und diese Spalten sind für die Berechnung relevant.
Außerdem erkennt man, dass das WAHR immer in jeder 4. Spalte steht, es ist also eine Kontinuität vorhanden.

Deine Angaben:
C3 = Von
D3 = Bis
G3 = Von
H3 = Bis
K3 = Von
L3 = Bis
Ich hatte das nur noch etwas erweitert.

Mi dieser dieser Erklärung solltest Du nun in der Lage sein, die Formel anzupassen.
Wenn nicht, dann poste einfach alle Spaltenbuchstaben, di infrage kommen.

Gruß
Rainer

Antwort 14 von rainberg

Hallo Torsten,

schreibe mal folgende Formel in A1

=REST(SPALTE();4)=0

und folgende Formel in A2

=REST(SPALTE();4)=3

nun kopierst Du beide Formeln nach rechts bis in Spalte AB oder auch weiter.
Du wirst nun feststellen, dass in den Spalten, die Du in Deiner Ausgangsfrage angegeben hast, immer ein WAHR steht und diese Spalten sind für die Berechnung relevant.
Außerdem erkennt man, dass das WAHR immer in jeder 4. Spalte steht, es ist also eine Kontinuität vorhanden.

Deine Angaben:
C3 = Von
D3 = Bis
G3 = Von
H3 = Bis
K3 = Von
L3 = Bis
Ich hatte das nur noch etwas erweitert.

Mit dieser dieser Erklärung solltest Du nun in der Lage sein, die Formel anzupassen.
Wenn nicht, dann poste einfach alle Spaltenbuchstaben, di infrage kommen.

Gruß
Rainer

Antwort 15 von fantalight

Hallo Rainer,

danke für Deinen Erklärungsversuch. Ich wollte es nachvollziehen; geht aber nicht. Vielleicht noch zur Erklärung der Ablauf wie ich es nachvollziehen wollte:

In ein leeres TAbellenblatt habe ich in A1 und A2 die von Dir angegebenen Formeln eingegeben und nach nach rechts kopiert. Grundsätzlich stand danach in jedem Feld "Falsch" drin. Irgendwie habe ich an Deinem Erklärungsversuch etwas nicht verstanden. Ich habe mir auch in der Onlinehilfe nochmals die Definitionen von REST und SPALTE angesehen und würde es halt gern verstehen. Vielleicht wagst Du noch einen Erklärungsversuch!

Müssen denn in den Spalten...
C3 = Von
D3 = Bis
G3 = Von
H3 = Bis
K3 = Von
L3 = Bis
...Zeiten drinstehen, damit oben irgendwo Wahr steht? Das glaube ich wiederum nicht, weil ja Dein Beispielformel =REST(SPALTE();4)=0 sich auf die aktuelle Zelle (also A1 oder C1 oder E1 usw.) bezieht.

Bitte versuchs doch noch einmal mir die Funktion näherzubringen (oder auch gern jmd. anderes).
DAnke im voraus!

Gruß
Torsten

Antwort 16 von rainberg

Hallo Torsten,

ich kann mich nur wiederholen, mit den angegebenen Formeln erhältst Du folgende Werte

A________B________C________D_______E________F_______G________H_______I________J________K_______L
FALSCH	FALSCH	FALSCH	WAH_R	FALSCH	FALSCH	FALSCH	WAHR	FALSCH	FALSCH	FALSCH	WAHR
FALSCH	FALSCH	WAHR	FALSCH	FALSCH	FALSCH	WAHR	FALSCH	FALSCH	FALSCH	WAHR	FALSCH


Du siehst in den Spalten Deiner Angabe steht WAHR und nur diese Spalten nimmt die Formel für die Berechnung.

Gruß
Rainer

Antwort 17 von Aliba

Hi Torsten, hi Rainer,


@Torsten,

nur mal kurz zur Erläuterung von Rainers Formel.
Diese geht davon aus, daß:

1. nach einer bis-Spalte zwei Spalten folgen, die nichts mit der Berechnung zu tun haben.

2. die erste von-Spalte die Spalte C ist.

3. direkt nach einer von-Spalte die dazugehörige bis-Spalte kommt.

Das funzt dann auch und führt zu dem richtigen Ergebnis, W E N N es auch zu jedem Von-Eintrag einen Bis-Eintrag gibt.
Und genau das scheint ja das Problem zu sein, daß es Von-Einträge geben kann, die keinen Bis-Eintrag haben.
In diesem Fall liefert der Vorschlag von Rainer das selbe Ergenis wie: =D3-C3+H3-G3+...

Also müsste in Rainers Vorschlag noch die Abfrage eingebaut werden, ob die entsprechende Bis-Spalte auch gefüllt ist, bzw. wenn es auch vorkommt, daß ein Bis-Eintrag ohne zugehörigen Von-Eintrag vorkommt auch noch die entsprechende Abfrage.

=SUMMENPRODUKT((REST(SPALTE(D:AC);4)=0)*(ISTZAHL(D3:AC3))*(ISTZAHL(C3:AB3));D3:AC3) -SUMMENPRODUKT((REST(SPALTE(C:AB);4)=3)*(ISTZAHL(C3:AB3))*(ISTZAHL(D3:AC3));C3:AB3)

Hoffe mal , daß die Formel so stimmt, mangels Excel jetzt nicht getestet.

CU Aliba

Antwort 18 von rainberg

Hallo Aliba,

danke für Deine Erläuterung und Erweiterung.

Ich hatte auf die zusätzlichen Argumente wissentlich verzichtet, da ich fest davon ausging, dass alle relevanten Zeiten eingetragen sind und somit die Formel (für den Laien) nur noch komplizierter würde.

Auf alle Fälle dürften Torsten Deine Erläuterungen nützlich sein und eine Altenative zu meiner Formel bilden.

Gruß
Rainer

Antwort 19 von fantalight

@Rainer:
Danke für die Erläuterung. Ich bringe jetzt das gleiche Ergebnis wie Du zustande. Mein Fehler lag darin, dass ich die erste Formel zwar wie von Dir angegeben in A1 und die zweite Formel nicht wie angegeben in A2 sondern in B1 eingetragen habe. Ich habe mir gestern schon eine halbe Stunde den Kopf zerbrochen, warum es nicht so ausschaut wie bei Dir. Es klappte aber nicht. Erst heute habe ich meinen Fehler bemerkt.

Ich habe heute dann auch etwas gebraucht, um die Formel nachzuvollziehen, aber nach Studium der Onlinehilfe und einer ganze Menge Überlegungen kann ich es reproduzieren. Vielleicht hilft anderen Anfängern mein Erklärungsversuch hier:

Rainer hatte folgende Formel als Erläuterung und zum Eintrag in A1 angegeben:

=REST(SPALTE();4)=0

Wenn ich die Onlinehifle richtig verstehe, dann ist der REST der ganzzahlige Wert einer Division. Bei dieser Formel oben ist ´4´ der Divisor.
SPALTE() liefert die aktuelle Spaltenreihe. Das bedeutet, dass in der Spalte D dieser Wert eine 4 ergibt. Der Rest der Division (4/4) ergibt dann 0 und dies = 0 also WAHR!

Dieser Formelaufbau ist wohl erforderlich, um einen Wert innerhalb einer Zeile, der zyklisch immer wieder kehrt (also z.B. aller 3 Spalten) zu erreichen.

Meinen Tabellenaufbau habe ich ja jetzt etwas geändert und sieht ja jetzt wie ich weiter oben schon schrieb so aus:
VON|BIS|VON|BIS|VON|BIS|....

Um die geänderten Bedingungen anzupassen, habe ich mit Deiner Erklärungsformel etwas rumgespielt und bin auf folgende Formel für A1:
=REST(SPALTE();2)=0
und A2 gekommen:
=REST(SPALTE();2)=1
Hier ist ein beiden Zeilen eins ums andere Mal das WAHR zu sehen.

Allerdings konnte ich die SUMMENPRODUKT-Formel nicht umkonstruieren. Hier fehlen mir wohl noch etwas Kenntnisse in Richtung ´Bezüge´. Da heißt es lernen!

@Rainer, @Aliba:
Alle Euren zuletzt geposteten Formeln funzen. Auch die Berechnung über Mitternacht und die Formel mit der ´BIS´-Prüfung. Euch nochmal herzlichen Dank!

Ich eröffne ein anderes Posting (Wie Zeitdifferenzen aufgrund einer Bedingung summieren?) mit einem neuen Filterproblem aber ebenso in Bezug auf dieses Projekt/Arbeitsmappe. Vielleicht könnt Ihr auch da vorbei schauen, denn ihr gehört wohl zur Exel-Elite!

Gruß
Torsten

Ich möchte kostenlos eine Frage an die Mitglieder stellen:


Ähnliche Themen:


Suche in allen vorhandenen Beiträgen: