Formel gesucht.

Question

Hallo.

Ich versuche seit einiger Zeit vergeblich eine Formel zu finden, um eine Tabelle endlich fertig zu machen. Im Bereich B3 bis B64 sollen alle Zellen mit geradzahligen Zeilennummern berücksichtigt werden. Die ungeradzahligen Zellen des Bereiches enthalten alle jeweils eine positive ganze Zahl. Alle Werte aus diesen geradzahligen Zeilen, in denen einen eine Zahl steht, sollen addiert werden. Wenn dort eine Zahl steht, steht dort nur eine Zahl. (Dort stehen also nie Werte, die sowohl Zahlen als auch ext enthalten. Die Zahlen sind immer positiv und zwischen 0 und 11. (Als Dezimaltrennzeichen wird, wenn nötig, das Komma verwendet.) Es kann aber auch sein, dass eine oder mehrere dieser Zellen leer sind oder dass sie Buchstaben enthalten. Bei leeren Zellen soll nichts passieren (bzw. 0 addiert werden). Bei den Buchstaben u, k und f soll geprüft werden, ob der Wert in der (ungeradzahligen) Zeile darüber bei Teilung durch 7 einen Rest von 6 oder 7 ergibt. Wenn das der Fall ist, soll 0 addiert werden, ansonsten soll 8 addiert werden. Wenn die Zelle ein f enthält, braucht die Zelle darüber nicht geprüft werden und es soll immer 8 addiert werden.

Das Ganze soll in Microsoft Excel 2013 und in LibreOffice Calc funktionieren. (Die Tabelle wird auf USB-Stick gespeichert und an mehreren Rechnern eingesetzt.)

Bisher habe ich vergeblich mit =SUMMENPRODUKT((REST(ZEILE(B3:B64);2)=0)… und mit=((ZÄHLENWENN(M3:M64;"u")+ZÄHLENWENN… experimentiert, bin aber noch nicht zu einem brauchbaren Ergebnis gekommen. Wer weiß Rat?

Mfg Friedel

Answer 1

Hallo Friedel,

kannst du eine Beispielmappe uppen in der man sieht welche Werte im Bereich B3:B64 stehen und welche Ergebnis du anhand dieser erwartest.

Formelvorschläge kannst du außen vor lassen.

Gruß
Rainer

Answer 2

Hallo.

Nein, eine Beispieldatei darf ich nicht weitergeben. Aber kann ja mal so eine Spalte mit Phantasiewerten füllen und hier posten. Ich hatte nach Spalte B3 bis B64 gefragt, aber das gleiche wiederholt sich in allen Spalten rechts davon.

Folgende Liste beschreibt also so eine Spalte:

[list=1][*] [*] [*] Mo, 01.
[*] 8
[*] Di, 02.
[*] 8
[*] Mi, 03.
[*] 8
[*] Do, 04.
[*] f
[*] Fr, 05.
[*] u
[*] Sa, 06.
[*]
[*] So, 07.
[*]
[*] Mo, 08.
[*] k
[*] Di, 09.
[*] k
[*] Mi, 10.
[*] k
[*] Do, 11.
[*] 8
[*] Fr, 12.
[*] 8
[*] Sa, 13.
[*]
[*] So, 14.
[*]
[*] Mo, 15.
[*] 8
[*] Di, 16.
[*] 8
[*] Mi, 17.
[*] 8
[*] Do, 18.
[*] 8
[*] Fr, 19.
[*] 7,25
[*] Sa, 20.
[*]
[*] So, 21.
[*]
[*] Mo, 22.
[*] 8
[*] Di, 23.
[*] 8
[*] Mi, 24.
[*] 8
[*] Do, 25.
[*] 8
[*] Fr, 26.
[*] z
[*] Sa, 27.
[*]
[*] So, 28.
[*]
[*] Mo, 29.
[*] 8
[*] Di, 30.
[*] 9,5
[*] Mi, 31.
[*]
[/list]

Das Datum in den ungeradzahligen Zeilen wird erzeugt, indem einfach zum vorherigen Datum 1 addiert wird.

In Zeile 1 steht der Monat, also z.B. Mai 2015. Das wird z.B. ist Spalte H durch =SUMME(MONATSENDE(G1;0);1) erzeugt.

In Zeile 2 steht ein Wert, der mit dem Problem nichts zu tun hat.

In Zeile 67 sollen die geradzahligen Werte addiert werden. Es soll also die 8 aus Zeile 4 + die 8 aus Zeile 6 + die 8 aus Zeile 8 usw. addiert werden.

Für das f in Zeile 10 soll 8 addiert werden. Das soll immer passieren, wenn in einer geradzahligen Zeile ein f eingetragen ist, aber nicht wenn das Feld zu einem Samstag oder Sonntag gehört. Also nicht, wenn in der ungeradzahligen Zeile davor das Datum von einem Sa oder So steht.

Für das u in Zeile 12 soll auch 8 addiert werden. Das soll natürlich auch wieder bei allen u passieren, außer wenn sie bei einem Sa oder So eingetragen sind.

Das gleiche passiert bei einem k.

Bei einem z braucht nicht geprüft werden, ob es am Wochenende eingetragen ist. Aber es schadet auch nicht, wenn es geprüft wird. Die z werden immer nur für maximal 2 Tage im Monat von Hand eingetragen und man trägt sie nicht am Wochenende ein.

Wenn ein geradzahliges Feld leer ist, wird 0 addiert.

In Zeile 67 sollte also 176,75 stehen, wenn ich mich jetzt nicht verrechnet habe.

Ich denke, man kann erkennen, dass es hier um Abrechnungen von Arbeitsstunden geht. Die Zahlen beschreiben gearbeitete Stunden. u steht für Urlaub, f steht für Feiertag, z steht für Zeitkonto und k steht für krank. Am Wochenende kann natürlich ein Feiertag sein. das WE kann auch in den Urlaub fallen und man kann am WE auch krank sein. In all diesen Fällen wird nichts addiert. Dass man am WE Überstunden abfeiert ist natürlich nicht möglich.

Answer 3

Hallo Friedel,

das geht mit folgender Matrixformel (Formeleingabe mit Strg+Umschalt+Enter bestätigen)

=SUMME(WENN(REST(ZEILE(B4:B64);2)=0;B4:B64))+SUMME(WENN((REST(ZEILE(4:64);2)=0)*(B4:B64={"f"."u"."k"."z"})*(REST(ZEILE(3:63);2)=1);1))*8

Gruß
Rainer

Answer 4

Vielen Dank. Klappt wunderbar. Ich habe mich noch nie mit Matrixformeln befasst und wäre da wohl nicht in absehbarer Zeit drauf gekommen.