Zum Inhalt springen
Frage 12. August 2010, 14:10 Uhr · 15 Antworten

'wenn dann' mit vielen anforderungen

Hallo, ich brauche (wieder einmal) Hilfe bei einer Berechnung, die mit einer "Wenn-und-oder-dann-Formel funktionieren sollte - aber wie??:

  • In Spalte A stehenn -variabel- irgendwo der Eintrag „Beginn“ und „Ende“;
  • in Spalte G stehen Wochentage
  • in Spalte N steht die regelmäßige Arbeitszeit (idR 8:00. evtl auch 0:00)

Jetzt soll gerechnet werden "Bilde die Summe aus Spalte N für die Woche (Bsp. KW 3), in der der Eintrag steht(Montag bis Sonntag) und bis zu dem Ende der Woche, die der mit dem Eintrag folgt (Ende ist in KW 6, es wäre also Sonntag der KW 7).

Ich hoffe, ich habe das verständlich dargestellt.
Das müsste doch klappen, ich finde aber nicht, wie ich zu den variablen „Beginn-“ und "Ende-"Zellen komme.
Ich danke für Eure Hilfe
petsch

Diese Frage stammt aus dem Jahr 2010. Weißt du es heute besser?

Aktuelle Antwort ergänzen

15 Antworten

Hallo Petsch,

wie wäre es mit einer aussagekräftigen Beipielmappe?

Gruß
Reinhard

0 Hilfreich
Antwort auf Reinhard_fb8cb7zum Beitrag ↑

Hallo Petsch, wie wäre es mit einer aussagekräftigen Beipielmappe? Gruß Reinhard

Danke ersteinmal für das Hilfsangebot.

Ich habe meine Tabelle auf die maßgeblichen Spalten reduziert. Aber wie kriege ich die Beispielmappe hier dargestellt??
mit erwartungsvollem Gruß
petsch

0 Hilfreich
Antwort auf petschzum Beitrag ↑

Danke ersteinmal für das Hilfsangebot. Ich habe meine Tabelle auf die maßgeblichen Spalten reduziert. Aber wie kriege ich die Beispielmappe hier dargestellt?? mit erwartungsvollem Gruß petsch

Beispiel: OWT
http://www.file-upload.net/

0 Hilfreich
Antwort auf abgemeldet5zum Beitrag ↑

Beispiel: OWT http://www.file-upload.net/

danke für die Nachhilfe!

Also auf ein Neues, meine Beispieldatei liegt bereit:

http://www.file-upload.net/download-2743707/Mappe2.x…

bis denn mit Dank
Silke

0 Hilfreich

Hallo, ich brauche (wieder einmal) Hilfe bei einer Berechnung,
die mit einer "Wenn-und-oder-dann-Formel funktionieren sollte

  • aber wie??:

Hallo Silke,

wie, das weiß ich nicht, brauchte kein Wemm-Dann-Sonst 😄

Kalenderwoche() rechnet falsch, deshalb in B47/49 anders berechnet.

Tabellenblatt: C:\DOKUME~1\ich2\LOKALE~1\Temp\[Mappe2.xls]!Überstundenberechnung (Blanko)
 │ A │ B │ C │
───┼─────────────┼────────┼───────────┤
46 │ Beginn │ │ │
───┼─────────────┼────────┼───────────┤
47 │ 28.05.2010 │ 21 │ 24.5.2010 │
───┼─────────────┼────────┼───────────┤
48 │ Ende │ │ │
───┼─────────────┼────────┼───────────┤
49 │ 07.06.2010 │ 24 │ 20.6.2010 │
───┼─────────────┼────────┼───────────┤
50 │ Zeit │ │ │
───┼─────────────┼────────┼───────────┤
51 │ Überstunden │ 153.00 │ │
───┴─────────────┴────────┴───────────┘
Benutzte Formeln:
A47: =SVERWEIS("Beginn HW-Einsatz";A1:B46;2;0)
A49: =SVERWEIS("Ende HW-Einsatz";A1:B46;2;0)
B47: =KÜRZEN((A47-DATUM(JAHR(A47+3-REST(A47-2;7));1;REST(A47-2;7)-9))/7)
B49: =KÜRZEN((A49-DATUM(JAHR(A49+3-REST(A49-2;7));1;REST(A49-2;7)-9))/7)+1
B51: =SUMMENPRODUKT((B4:B45\>=$C$47)\*(B4:B45
Tabellendarstellung erreicht mit dem Code in [FAQ:2363](/t/faq/9292363)

Gruß
Reinhard
0 Hilfreich
Antwort auf petschzum Beitrag ↑

danke für die Nachhilfe! Also auf ein Neues, meine Beispieldatei liegt bereit: http://www.file-upload.net/download-2743707/Mappe2.x… bis denn mit Dank Silke

Hallo Petsch!

Beginn- und Enddatum waren für mich kein großes Problem.

=SVERWEIS(„Beginn*“;A:B;2;0)
=SVERWEIS(„Ende*“;A:B;2;0)

Die Summenformel war dann schon etwas tricky, weil da eine Matrixformel notwendig ist: {=SUMME(WENN((B4:B45>=A47)*(B4:B45

0 Hilfreich
Antwort auf Reinhard_fb8cb7zum Beitrag ↑

Hallo, ich brauche (wieder einmal) Hilfe bei einer Berechnung, die mit einer "Wenn-und-oder-dann-Formel funktionieren sollte aber wie??: Hallo Silke, wie, das weiß ich nicht, brauchte kein Wemm-Dann-Sonst 😄 Kalenderwoch …

Hallo Reinhard,
hallo Alex,

ich danke Euch für die Hilfe und die Mühe und werde mich heute abend dransetzen!

petsch

0 Hilfreich
Antwort auf petschzum Beitrag ↑

danke für die Nachhilfe! Also auf ein Neues, meine Beispieldatei liegt bereit: http://www.file-upload.net/download-2743707/Mappe2.x… bis denn mit Dank Silke

Hallo Silke,
Ich würde mir als erstes eine Hilfsspalte einbauen,
in der die aktuelle Kalenderwoche [KW] berechnet wird.
Kann ja später ja ausgeblendet werden.
Excel hat dafür eine fertige Funktion =KALENDERWOCHE(B4;2)

Runter kopieren
Die nachfolgenden Formeln gehen davon aus, das die KW in den Zellen F4:F45 stehen.

Als zweites kannst du ganz einfach per SVerweis die relevante KW für Beginn und Ende abfragen:
=SVERWEIS(„Beginn*“;A4:F45;6;0)
=SVERWEIS(„Ende*“;A4:F45;6;0)

Nachfolgende Formel summiert alle Werte aus D4:smiley:45 wenn

  1. Es >= die Kalenderwoche vom Beginn ist,
  2. Es =SVERWEIS(„Beginn*“;A4:F45;6;0))*(F4:F45Add-Ins… >Analyse-Funktionen aktivieren [x] >ok
0 Hilfreich
Antwort auf abgemeldet5zum Beitrag ↑

Hallo Silke, Ich würde mir als erstes eine Hilfsspalte einbauen, in der die aktuelle Kalenderwoche [KW] berechnet wird. Kann ja später ja ausgeblendet werden. Excel hat dafür eine fertige Funktion =KALENDERWOCHE(B4;2) Ru …

Hallo,
ein Nachtrag zu der Tabelle:

In A47 & A49 wir das Datum vom Beginn bzw. Ende des Einsatzes angezeigt.
Möchtest du statt dessen Beginn und Ende des maßgeblichen Zeitraums, dann so:

Beginn: [24.05.2010]
=SVERWEIS(„Beginn*“;A4:B45;2;0)-WOCHENTAG(SVERWEIS(„Beginn*“;A4:B45;2;0);3)

Ende: [20.06.2010]
=SVERWEIS(„Ende*“;A4:B45;2;0)-WOCHENTAG(SVERWEIS(„Ende*“;A4:B45;2;0);3)+13

Gruß Holger

0 Hilfreich
Antwort auf abgemeldet5zum Beitrag ↑

Hallo Silke, Ich würde mir als erstes eine Hilfsspalte einbauen, in der die aktuelle Kalenderwoche [KW] berechnet wird. Kann ja später ja ausgeblendet werden. Excel hat dafür eine fertige Funktion =KALENDERWOCHE(B4;2) Ru …

Hallo Holger,

Excel hat dafür eine fertige Funktion =KALENDERWOCHE(B4;2)

die Kalenderwoche rechnet aber falsch, nicht jedes Jahr aber in manchen.
Bei dem Beispiel von Silke berechnet sie für den 28.5 die KW 22, korrekt ist aber 21.
Siehe: http://excelformeln.de/formeln.html?welcher=7

Übrigens, ich habe das ganze so gelesen daß das „Ende“ der Sonntag sein soll, der in der oche liegt, die auf die Woche in der „Ende…“ steht, folgt.

Naja, manches im Blatt war/ist schlecht deutbar, wie bei 1-2-oder 3, wer Recht hat sieht man wenn das Licht angeht, der darf sich dann ein Bällchen nehmen 😄)

Gruß
Reinhard

0 Hilfreich
Antwort auf abgemeldet5zum Beitrag ↑

Hallo, ein Nachtrag zu der Tabelle: In A47 & A49 wir das Datum vom Beginn bzw. Ende des Einsatzes angezeigt. Möchtest du statt dessen Beginn und Ende des maßgeblichen Zeitraums, dann so: Beginn: [24.05.2010] =SVERWEIS(„B …

Ups, hatte ich nicht gesehen als ich eben postete
Hallo Holger,

ich laß mein Posting aber drin wegen kalenderwoche().

Gruß
Reinhard

0 Hilfreich
Antwort auf Alex_Winterzum Beitrag ↑

Hallo Petsch! Beginn- und Enddatum waren für mich kein großes Problem. =SVERWEIS(„Beginn*“;A:B;2;0) =SVERWEIS(„Ende*“;A:B;2;0) Die Summenformel war dann schon etwas tricky, weil da eine Matrixformel notwendig ist: {=SUMM …

an alle Helfer vielen Dank, es funktioniert bestens!
Ich bin sehr froh, dass es dieses Forum und die vielen Experten gibt, die Amateuren wie mir auf die Sprünge helfen!

petsch

0 Hilfreich
Antwort auf Reinhard_fb8cb7zum Beitrag ↑

Ups, hatte ich nicht gesehen als ich eben postete Hallo Holger, ich laß mein Posting aber drin wegen kalenderwoche(). Gruß Reinhard

Reinhards Deutung war richtig, ich habe aber die „Kalenderwoche“ auch verwendet, das Add-In hilft bestimmt auch beim nächsten Mal weiter 😉
Silke

0 Hilfreich
Antwort auf petschzum Beitrag ↑

Reinhards Deutung war richtig, ich habe aber die „Kalenderwoche“ auch verwendet, das Add-In hilft bestimmt auch beim nächsten Mal weiter 😉 Silke

Reinhards Deutung war richtig, ich habe aber die
„Kalenderwoche“ auch verwendet, das Add-In hilft bestimmt auch
beim nächsten Mal weiter 😉
Silke

Hallo Silke,

ich krieg langsam die Krise 😦
Kalenderwoche() rechnet in D falsch, PUNKT.

Daß es bei dir trotzdem klappt liegt daran daran du quasi die Differenz zwischen zwei KW bildest, dann ist es für die Differnz unerheblich ob es um 22-25 oder 21-24 geht.

Aber was soll’s, wenn du meinst jetzt klappt es bei dir, dann scheint es so zu sein.

Gruß
Reinhard

0 Hilfreich
Antwort auf Reinhard_fb8cb7zum Beitrag ↑

Reinhards Deutung war richtig, ich habe aber die „Kalenderwoche“ auch verwendet, das Add-In hilft bestimmt auch beim nächsten Mal weiter 😉 Silke Hallo Silke, ich krieg langsam die Krise 😦 Kalenderwoche() rechnet in D f …

Hallo Reinhard,
Mir ist bekannt, das die Funktion Kalenderwoche falsch rechnet.
Das ist aber wie schon geschrieben steht für diese Berechnung absolut irrelevant. Die relevanten Wochen ergeben sich aus den Einträgen „Beginn…“ und „Ende…“

Ich benötige für die Wochen eine aufsteigende Zahl die jewails am Montag um ein höher ist, das ist alles. Mann könnte genau so gut einen Zähler verwenden, der sich jeden Montag um 1 erhöht. Vielleicht ist es sogar gut das Jahr mit ein zu rechen damit es auch über den Jahreswechsel hinaus funktioniert [zB. Jahr*100+KW]

Da gibt es viele Wege. Mir war es wichtig möglichst einfach die Wochen aufzuteilen um anschließend die Summe zu bilden. Ohne Hilfsspalte wäre das eine Mammutformel geworden die keiner mehr versteht.

Gruß Holger

0 Hilfreich

Deine Antwort

Zum Antworten bitte anmelden oder kostenlos registrieren.