Hilfe bei Feinschliff

[Thomas78]

Hallo Opa Edgar,

vielen Dank nochmal für deine Hilfe, ich habe während du deine Datei hochgeladen hast selbst versucht das ganze mit einer Hilfsspalte zu lösen und habs zusammen mit deiner Datei geschafft.

2 Summenprodukte habe ich nicht wegbekommen:

1. in Zelle E4 (hier zählt er mir die Wochentage vor einem Feiertag wenn diese kein Montag sind) =SUMMENPRODUKT((MONAT(Feiertag)=MONAT(G2))*(WOCHENTAG(Feiertag;2)<6)*(WOCHENTAG(Feiertag;2)>1))
2. als Hilfsmittel um aus N2:AR2 alle Freitage auszuzählen SUMMENPRODUKT(N(TEXT(N$2:AR$2;"TTT")="Fr"))

Hierfür habe ich keine besseren Alternativen gefunden.

Hat noch jemand Tipps vor allem bezüglich Userform?

Schöne Grüße

Thomas

EDIT: Jetzt habe ich noch eine Spezialfrage:
Ich brauche eine Formel die mir alle Freitage des Monats G2 zählt die kein Feiertag sind und in denen nichts in z.B. N5:AR5 eingetragen ist

[BoskoBiati]

Hi,

zu der Userform habe ich Dir schon was geschrieben.

E4 zählt falsch, wenn der 1. des nächsten Monats ein Feiertag ist (z.B. Oktober/November)

Freitage zählen hier:

http://excelformeln.de/formeln.html?welcher=251


  Hilfe_Aufbau.xlsx (Größe: 31,72 KB / Downloads: 4)

[Thomas78]

Ooh das mit der Userform hatte ich überlesen, das erklär ich später nochmal. Entschuldigung.

Jetzt steh ich aber komplett auf dem Schlauch. 

Auch mit deiner Datei komm ich nicht auf das richtige Ergebnis. Da es mir die Spalte G nicht richtig berechnet.

Ich versuche nochmal zu erklären was ich genau brauche:

Spesensatz 1 ohne x in Spalte a : Mitarbeiter bekommt an allen Tagen 12,-- € 
                                                an allen Tagen in denen in N:AR eine 2 steht bekommt er 24,-- €
                                                an Feiertagen, oder wenn in N:AR ein u (Urlaub) oder k (Krank) steht werden keine Spesen berechnet

Spesensatz 2 mit x in Spalte a : Mitarbeiter bekommt an Montag bis Donnerstag 24,-- € 
                                              an Freitagen oder Mo-Do vor einem Feiertag nur 6,-- € (also wenn z.B. Mittwoch ein Feiertag ist soll für Dienstag nur 6,-- € gezählt werden; wenn Freitag ein Feiertag ist soll für                                                               Donnerstag nur 6,-- € gezahlt werden, wenn Montag ein Feiertag ist wird nichts gesondert gerechnet)
                                              an Feiertagen, oder wenn in N:AR ein u (Urlaub) oder k (Krank) steht werden keine Spesen berechnet


Puh ich kanns nicht besser erklären...
Ich habe nochmal eine Datei angehängt die mir ausser Spalte G eigentlich alles richtig berechnet (oder?) in Spalte G habe ich manuell die richtigen Ergebnisse für den Beispielmonat August 2017 eingetragen.

Ich glaube ihr verzweifelt langsam an mir aber ich verzweifele definitiv momentan an meiner Datei...

Thomas

[BoskoBiati]

Hi,

komisch, meine Datei bringt die gleichen Ergebnisse wie Deine.....

Arbeitsblatt mit dem Namen 'Urlaubsplan'
	E	F	G	H	I	J	K	L
4	1	31	4
5	!	22	0	22	264,00 €		0,00 €
6	!	22	2	20	288,00 €		0,00 €
7	!	22	4	14	360,00 €	4	68,00 €
8	!	22	0	11	132,00 €	4	68,00 €	7
9	!	22	0	8	96,00 €	6	102,00 €	8

Zelle	Formel
E4	=SUMMENPRODUKT((MONAT(Feiertag)=MONAT(G2))*(REST(Feiertag;7)<6)*(REST(Feiertag;7)>1))
F4	=TAG(MONATSENDE(G2;0))
G4	=ZÄHLENWENN(N4:INDEX(N4:AR4;F4);"x")
F5	=NETTOARBEITSTAGE(G2;MONATSENDE(G2;0);Feiertag)
G5	=WENN(C5="x";SUMMENPRODUKT((TEXT($N$3:$AR$3;"MMJJ")=TEXT($G$2;"MMJJ"))*(N5:AR5="")*(REST($N$3:$AR$3;7)=6))-SUMMENPRODUKT((TEXT($N$3:$AR$3;"MMJJ")=TEXT($G$2;"MMJJ"))*(N5:AR5="")*(REST($N$3:$AR$3;7)=6)*($N$3:$AR$3=Feiertag))+$E$4;ZÄHLENWENN(N5:INDEX(N5:AR5;$F$4);2))
H5	=F5-G5-J5-L5
I5	=WENN(C5="x";(G5+H5*4)*6;(G5*2+H5)*12)
J5	=ZÄHLENWENN(N5:INDEX(N5:AR5;$F$4);"u")
K5	=J5*17
L5	=ZÄHLENWENN(N5:INDEX(N5:AR5;$F$4);"k")

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

[Thomas78]

Hallo Opa Edgar,

nicht ganz.

ich habe deine Datei beigefügt. Hier hat nur Mitarbeiter h in Zeile 12 vom 14.08. bis 25.08. Urlaub.
Gemäß der Vorgaben müsste der 04.08. und 11.08. in Spalte G gezählt werden, da der 14.08., 18.08. und 25.08 nicht zählen (entweder Freitag durch Urlaub belegt (18. und 25.) oder Wochentag vor Feiertag (14.)mit Urlaub belegt. Also Ergebnis Spalte G "2" und Spalte H "11"
Wie gesagt sollen Wochentage vor einem Feiertag wie ein Freitag behandelt werden.

Und genau das krieg ich nicht hin.

Grüße

Thomas

[BoskoBiati]

Hallo,

Dein Muster von 11:43

Arbeitsblatt mit dem Namen 'Urlaubsplan'
	D	E	G	H	I	J	K	L
12	h	!	5	13	342,00 €	4	68,00 €

Zelle	Formel
H12	=$H$1-$G12-WENN($J12<>"";$J12)-WENN($L12<>"";$L12)
I12	=(G12*6)+(H12*24)
J12	=WENN(ZÄHLENWENN($N12:$AR12;"u")=0;"";ZÄHLENWENN($N12:$AR12;"u"))
K12	=WENN(J12="";"";J12*17)
L12	=WENN(ZÄHLENWENN($N12:$AR12;"k")=0;"";ZÄHLENWENN($N12:$AR12;"k"))

Meine Datei:

Arbeitsblatt mit dem Namen 'Urlaubsplan'
	D	E	F	G	H	I	J	K	L
12	h	!	22	5	13	342,00 €	4	68,00 €

Zelle	Formel
F12	=F5
G12	=WENN(C12="x";SUMMENPRODUKT((TEXT($N$3:$AR$3;"MMJJ")=TEXT($G$2;"MMJJ"))*(N12:AR12="")*(REST($N$3:$AR$3;7)=6))-SUMMENPRODUKT((TEXT($N$3:$AR$3;"MMJJ")=TEXT($G$2;"MMJJ"))*(N12:AR12="")*(REST($N$3:$AR$3;7)=6)*($N$3:$AR$3=Feiertag))+$E$4;ZÄHLENWENN(N12:INDEX(N12:AR12;$F$4);2))
H12	=F12-G12-J12-L12
I12	=WENN(C12="x";(G12+H12*4)*6;(G12*2+H12)*12)
J12	=ZÄHLENWENN(N12:INDEX(N12:AR12;$F$4);"u")
K12	=J12*17
L12	=ZÄHLENWENN(N12:INDEX(N12:AR12;$F$4);"k")

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Der Fehler liegt daran, dass Deine Formel (die habe ich nur übernommen!) E4 einrechnet, was hier nicht passieren darf. Es wird wohl darauf hinauslaufen, dass Du für jede Zeile separat die Tage vor Feiertagen rechnen musst.

Arbeitsblatt mit dem Namen 'Urlaubsplan'
	E	F	G	H	I	J	K
12	0	22	2	11	276,00 €	9	153,00 €

Zelle	Formel
E12	=SUMMENPRODUKT((N12:AR12="")*(REST(Tagesdatum;7)<6)*(REST(Tagesdatum;7)>1)*($O$3:$AS$3=Feiertag))
F12	=F5
G12	=WENN(C12="x";SUMMENPRODUKT((TEXT($N$3:$AR$3;"MMJJ")=TEXT($G$2;"MMJJ"))*(N12:AR12="")*(REST($N$3:$AR$3;7)=6))-SUMMENPRODUKT((TEXT($N$3:$AR$3;"MMJJ")=TEXT($G$2;"MMJJ"))*(N12:AR12="")*(REST($N$3:$AR$3;7)=6)*($N$3:$AR$3=Feiertag))+E12;ZÄHLENWENN(N12:INDEX(N12:AR12;$F$4);2))
H12	=F12-G12-J12-L12
I12	=WENN(C12="x";(G12+H12*4)*6;(G12*2+H12)*12)
J12	=ZÄHLENWENN(N12:INDEX(N12:AR12;$F$4);"u")
K12	=J12*17

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

[Thomas78]

Hallo Opa Edgar,

es tut mir leid aber das ist das falsche Ergebnis.

In dem Beispiel mit Zeile 12 müsste folgendes rauskommen:

G                         H                      J                  L
 2                        11                     9                  0



Warum?
In G zählen nur 04.08. und 11.08.
In H zählen 01.08. bis 03.08. (3 AT); 07.08. bis 10.08. (4 AT); 28.08. bis 31.08. (4AT) 
In J zählen alle Nettoarbeitstage in N12:AR12 mit "u"

Ich glaub bei dir wird der 14.08. trotz Urlaub mit zu G gezählt?


Tut mir leid für die viele Mühe die du dir machst, ich danke dir sehr !!!

Thomas


EDIT : Habe gerade deinen Nachtrag gesehen ! Wie würdest du das anstellen?

[BoskoBiati]

Hi,

eine ganz kritische Sache ist mir aufgefallen: Du hast in allen Zellen eine Formel und ein Dropdown. Wenn Du das Dropdown benutzt, ist die Formel weg! Das geht gar nicht!

[Thomas78]

Hallo,

ja das weiss ich, das will ich noch durch Blattschutz und Entfernen des Dropdown verbessern. Danke für den Hinweis!

Bezüglich deiner letzten Formeln ändert sich bei mir nichts? 
Ich renn echt grad gegen ne Wand glaub ich.




Ist das richtig, oder müsste da am Ende nicht ($N$3:$AR$3=Feiertag) stehen?

=SUMMENPRODUKT((N12:AR12="")*(REST(Tagesdatum;7)<6)*(REST(Tagesdatum;7)>1)*($O$3:$AS$3=Feiertag))    

Thomas

Bei mir kommt in E12 als Ergebnis 1 raus. Wenn ich die Formel richtig deute sollte da doch im August 0 stehen?

[BoskoBiati]

Hi,

ich vergaß zu erwähnen, dass in AS das Datum des nächsten 1. stehen muß!