Problem mit Spaltenfunktion in einer Array-Formel

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Hallo
Leider kann ich keine File bereitstellen, da sich sensieble Daten darin befinden. Hier versuche ich mal zu erklären wie das Konzept der Tabelle aussieht. Weiter unten habe ich die ganze Formel um die es geht reinkoppiert. Ich weiss es gib noch einiges zu optimieren oder wahrscheinlich ganz andere Ansätze die besser/stabieler laufen.
Die Tabelle:
Statischer Bereich: von A10:C57. Mit statisch ist gemeint, dass dieser Bereich als Bzugsquelle von Werten verwendet wird um Abrfragen, Überprüfungen für weiter unten erwähnte Bereiche (Zellen oder Spalten)durzuführen. Spalte A10:A57 = Kalenderwoche, B10:B57 = Wochentag als Text Bsp. SO,MO,DI...,C10:C57 = DatumSpalte mit benutzerdefiniertem Format so dass man nur den Tag sieht (Bsp. 1., .... 31.) Jede Zeile ist ein Tag.
Dynamischer Bereich: D10:I57 = Mitarbeiter 1, J10:O57 = Mitarbeiter 2....usw. Da werde ich je nach Bedarf eine Mitarbeiter-Bereich Kopieren und in die ersten 6 frein Spalten einfügen.
Spalte D10:D57 = Startzeit oder Text oder leer (Bsp. 19:00 oder R für Ruhetag, FT für Feiertag usw.), E10:E57 = Endzeit nur im Zeitformat (Bsp. 05:00)oder leer, F10:F57 = Formel welche die Stunden von Start bis Ende berechnet, G10:G57 = Formel die die Nachtarbeitszeit berechnet pro Tag, H10:H57 = Formel welche die Pausen berechnet pro Tag und I10:I57 = Formel Gleitzeitsaldo pro Woche (d.h. an jedem: FR. werden die Absenztage, SA. die Sollstunden, SO. die Ist-Stunden, MO. die Diferenz zwischen Ist- und Sollstunden ausgewiesen). Diese Werte werden dynamisch angepasst wenn man andere Anfangs- oder Enddatums Werte eingibt, d.h. sie verschieben sich mit dem Wochentagen.
In der Zeile 58 werden entsprechnend Spalten-Totalwerte errechnet.
Alle diese Formeln funktionieren einwandfrei.
Jetzt zum Problem Bereich: Wenn einer oder mehrere dieser Gleitzeitsaldo Werte nicht mehr in der Tabelle ausgewiesen werden kann weil die Zeilen nicht mehr reichen, dann sollen diese in einen für die letzte Woche bestimmten Bereich von 4 Zellen ausgewiesen werden.(Die letzte Woche kann auch nur eine angebrochene Woche sein).
Die unten stehende Formel, soll die Sollstunden im Letzte-Woche-Bereich in die Entsprechnede Zelle ausweisen. So wie sie unten steht funktioniert sie auch, aber falls ich den mit Copy/Paste einen neuen Mitarbeiter an rechts an die Tabelle anhängen will, muss ich in den Formeln die Spalten Anzahl für die Bereich.verschieben Funktion händisch überall anpassen. Ich würde das gerne lösen mit einer Formel die mir die Anzahl Spalten ab Spalte C als relativen Bezug errechnet. Meine Lösung wäre gewesen =Spalte($C$10)-Spalte(entsprechende Startzeit Spalte des Mitarbeiters) anstatt eine negative Zahl einzutragen.
Dummerweise scheint diese mit der Bereich.verschieben Funktion in konflikt zu stehen und gibt mir #WERT zurück.
=WENN(WENN(((WENN((KALENDERWOCHE($C$57;21)>KALENDERWOCHE((BEREICH.VERSCHIEBEN(D$10; MAX(WENN(ZEILE(DatumSpalte)*(WOCHENTAG(DatumSpalte;1)=1);ZEILE(DatumSpalte)-ZEILE($C$10);0));-1));21) );WAHR();FALSCH()))*(WENN((BEREICH.VERSCHIEBEN(D$10;MAX(WENN(ZEILE(DatumSpalte) *(WOCHENTAG(DatumSpalte;1))*(DatumSpalte<=(WENN((H$5<>"")*(H$5<Lohnmonat_Ende);H$5; Lohnmonat_Ende)))*(D$10:D$57<>"");ZEILE(DatumSpalte)-ZEILE($C$10);0));-1))> =(BEREICH.VERSCHIEBEN(D$10;MAX(WENN(ZEILE(DatumSpalte)*(WOCHENTAG(DatumSpalte;1)=2); ZEILE(DatumSpalte)-ZEILE($C$10);0));-1));WAHR();FALSCH()))*WENN((ZEILE($C$57)<> (MAX(WENN(ZEILE(DatumSpalte)*(WOCHENTAG(DatumSpalte;1)=1);ZEILE(DatumSpalte)-ZEILE($C$10);0)))); WAHR();FALSCH()))=1;WAHR();FALSCH())*(WOCHENTAG($C$57;1)<>1)*(WOCHENTAG($C$57;1)<=6) *(WOCHENTAG($C$57;1)>2);(WENN(VERWEIS(Lohnmonat_Ende;DatumSpalte;ZEILE(DatumSpalte))-ZEILE($C$10) -MAX(WENN(ZEILE(DatumSpalte)*(WOCHENTAG(DatumSpalte;1)*(DatumSpalte<=Lohnmonat_Ende)=1); ZEILE(DatumSpalte)-ZEILE($C$10);0))-(VERWEIS(Lohnmonat_Ende;DatumSpalte;ZEILE(DatumSpalte)) -ZEILE($C$10)-(MAX(WENN(ZEILE(DatumSpalte)*(WOCHENTAG(DatumSpalte;1))*(DatumSpalte< =Lohnmonat_Ende)*(D$10:D$57<>"");ZEILE(DatumSpalte)-ZEILE($C$10);0))))>0; (VERWEIS((WENN((H$5<>"")*(H$5<Lohnmonat_Ende);H$5;Lohnmonat_Ende));DatumSpalte; ZEILE(DatumSpalte))-ZEILE($C$10)-MAX(WENN(ZEILE(DatumSpalte)*(WOCHENTAG(DatumSpalte;1) *(DatumSpalte<=(WENN((H$5<>"")*(H$5<Lohnmonat_Ende);H$5;Lohnmonat_Ende)))=1); ZEILE(DatumSpalte)-ZEILE($C$10);0))-(VERWEIS((WENN((H$5<>"")*(H$5<Lohnmonat_Ende);H$5; Lohnmonat_Ende));DatumSpalte;ZEILE(DatumSpalte))-ZEILE($C$10)-(MAX(WENN(ZEILE(DatumSpalte) *(WOCHENTAG(DatumSpalte;1))*(DatumSpalte<=(WENN((H$5<>"")*(H$5<Lohnmonat_Ende);H$5; Lohnmonat_Ende)))*(D$10:D$57<>"");ZEILE(DatumSpalte)-ZEILE($C$10);0)))));0) -WENN(WOCHENTAG($C$57;1)>1;(SUMMENPRODUKT(SUMMEWENNS($A$10:$A$57;$A$10:$A$57; KALENDERWOCHE(BEREICH.VERSCHIEBEN(D$10;MAX(WENN(ZEILE(D$10:D$57)*(D$10:D$57<>""); ZEILE(D$10:D$57)-ZEILE(D$10);0));-1);21);D$10:D$57;D$86:D$92;DatumSpalte;"<="&(WENN((H$5<> "")*(H$5<Lohnmonat_Ende);H$5;Lohnmonat_Ende))))/KALENDERWOCHE(BEREICH.VERSCHIEBEN(D$10; MAX(WENN(ZEILE(D$10:D$57)*(D$10:D$57<>"");ZEILE(D$10:D$57)-ZEILE(D$10);0));-1);21));0)) *Bezüge!$D$5*D$8/Bezüge!$D$10;0)
Funktion dieser Formel:
Beim ersten WENN wird überprüft überhaupt ein Wert in die entsprechende Zelle im "letzte Woche Bereich"
ausgewiesen werden soll, sonst soll dort 0,00 stehen.
Wenn ein Wert ausgewiesen werden soll, dann errechnet der Dann-Teil den richtigen Wert aus.
Zu der Frage: wie bekomme ich das mit der Formel für den relativen Bezug hin, so dass es nicht einen Fehlerwert gibt?
Ich bedanke mich mal schon für eure Inputs, Anregungen oder Kritik und entschuldige mich für den langen Text den ich euch da antue. Sorry :-)
Grüsse Alex ;-)

Hallo Werner
Kann ich verstehen...trotzdem vielen Dank, aber ich würde lieber die Tabelle nicht auf den Server stellen. Ich hätte eine Frage die du vielleicht beantworten kannst.
Wenn ich diesen Teil der Formel (es scheint auch nur das dort ein der Wertfehler autritt) so schreibe:
(WENN((BEREICH.VERSCHIEBEN(D$10;MAX(WENN(ZEILE(DatumSpalte)*(WOCHENTAG(DatumSpalte;1))*(DatumSpalte<=(WENN((H$5<>"")*(H$5<Lohnmonat_Ende);H$5;Lohnmonat_Ende)))*(D$10:D$57<>"");ZEILE(DatumSpalte)-ZEILE($C$10);0));-1))>=(BEREICH.VERSCHIEBEN(D$10;MAX(WENN(ZEILE(DatumSpalte)*(WOCHENTAG(DatumSpalte;1)=2);ZEILE(DatumSpalte)-ZEILE($C$10);0));-1));WAHR();FALSCH()))
~f~
Die -1 stellt den Spaltenversatz um eine Spalte zurück. Die einzelnen Bereich.verschieben Resultate die man durch drücken der F9-Taste in der Bearbeitungsleiste herausfinden sehen so aus:
~f~
=(WENN(42390>=42401;WAHR();FALSCH()))
~f~
Die WENN-Funktion gibt mir den Richtigen Wert zurück. FALSCH
Ersetze ich die -1 mit der Formel ~f~SPALTE($C$10)-SPALTE(D$10)~f~ um.:
~f~
=(WENN((BEREICH.VERSCHIEBEN(D$10;MAX(WENN(ZEILE(DatumSpalte)*(WOCHENTAG(DatumSpalte;1)) *(DatumSpalte<=(WENN((H$5<>"")*(H$5<Lohnmonat_Ende);H$5;Lohnmonat_Ende)))*(D$10:D$57< >"");ZEILE(DatumSpalte)-ZEILE($C$10);0));SPALTE($C$10)-SPALTE(D$10)))> =(BEREICH.VERSCHIEBEN(D$10;MAX(WENN(ZEILE(DatumSpalte)*(WOCHENTAG(DatumSpalte;1)=2); ZEILE(DatumSpalte)-ZEILE($C$10);0));SPALTE($C$10)-SPALTE(D$10)));WAHR();FALSCH()))
~f~
Dan erhalte ich mit der F9 Taste in der Bearbeitungsleiste zuerst bei den einzelenen Komponenten der Bereich.verschieben Funktion für die Zeile eine Zahl und die Spalte eine Zahl in geschweiften Klammern:
Schritt 1
~f~=(WENN((BEREICH.VERSCHIEBEN(D$10;36;{-1}))>=(BEREICH.VERSCHIEBEN(D$10;47;{-1}));WAHR();FALSCH()))~f~
dann nach der Überprüfung durch die F9-Taste der einzelnen Bereich.verschieben Funktionen:
Schritt 2
~f~=(WENN({42390}>={42401};WAHR();FALSCH()))~f~
Gehe ich jetzt einen Schritt weiter und markiere in der Bearbeitungsleiste nur den grösser/gleich Vergleich erhalte ich mit der F9 Taste:
Schritt 3
~f~=(WENN({FALSCH};WAHR();FALSCH()))~f~
Was dann ja auch funktiniert.
Wenn ich aber die Formel mit der F9 Taste in der Bearbeitungsleiste nur bis zum Schritt 1 aufschlüssle und dann die WENN-Prüfung dierekt in die Bearbeitungsleiste markeire und die teils aufgeschlüsselte Prüfung mit der F9 Taste teste erhalte ich das:
~f~=(WENN({#WERT!};WAHR();FALSCH()))~f~
die Formel funktioneirt dann nicht.
UND Jetzt das Verrückte:
Schreibe ich die Formel ohne die WENN Funktion:
~f~
=((BEREICH.VERSCHIEBEN(D$10;MAX(WENN(ZEILE(DatumSpalte)*(WOCHENTAG(DatumSpalte;1))*(DatumSpalte< =(WENN((H$5<>"")*(H$5<Lohnmonat_Ende);H$5;Lohnmonat_Ende)))*(D$10:D$57<>""); ZEILE(DatumSpalte)-ZEILE($C$10);0));SPALTE($C$10)-SPALTE(D$10)))>=(BEREICH.VERSCHIEBEN(D$10; MAX(WENN(ZEILE(DatumSpalte)*(WOCHENTAG(DatumSpalte;1)=2);ZEILE(DatumSpalte)-ZEILE($C$10);0)); SPALTE($C$10)-SPALTE(D$10))))
Funktioniert diese Formel!!! Was is denn da los? Ich galub mich tritt ein Pferd ;-)
Jezt binn ich wirklich verwirrt.
Sorry das ich euch da nerve, aber ich möchte wirklich verstehen was da geschieht und wo ich wahrscheinlich einen Denkfehler mache.
Grüsse an alle

Beliebteste Forumthreads (12 Monate)

Problem mit Spaltenfunktion in einer Array-Formel

13
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

319 Forumthreads zu ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

Problem mit Spaltenfunktion in einer Array-Formel

13 Beiträge zum Forumthread Beiträge zu diesem Forumthread

319 Forumthreads zu ähnlichen Themen

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

13
Beiträge zum Forumthread

Beiträge zu diesem Forumthread