Login

Mooko0124 · 13.08.2024, 13:04

Hallo zusammen,

ich möchte eine etwas komplizierte Berechnung mit Excel erstellen und weiß nicht wie ich diese erstellen soll.

In der Tabelle H3 sollen die Stück pro Stunde berechnet werden. Dies soll aber anhand der vorhanden Arbeitszeit passieren.
Dafür gibt es in I3 ein Startdatum inkl. Uhrzeit und ein Enddatum inkl. Uhrzeit in J3. Die Arbeitszeiten sind in der Tabelle Arbeitszeiten hinterlegt.
Wobei Wochenenden und Feiertage nicht berücksichtig werden dürfen.

Die Mengen werden für den jeweiligen Tag eingetragen (K3 6.000 St. und L3 1.000 St.).

In diesem Bespiel wäre die vorhandene Arbeitszeit 10,5 Stunden, die gefertigte Menge 7.000 St. und das Ergebnis ungefähr 666 St./h .

Wie kann ich das mit Excel berechnen?

Vielen Dank vorab für eure Unterstützung.

.xlsx

Test.xlsx (Größe: 13,75 KB / Downloads: 7)

oee · 13.08.2024, 13:17

" Die Mengen werden für den jeweiligen Tag eingetragen (K3 6.000 St. und L3 1.000 St.). " - Warum stehen sie nicht in der Beispielsdatei ?
" In diesem Bespiel wäre die vorhandene Arbeitszeit 10,5 Stunden, die gefertigte Menge 7.000 St. und das Ergebnis ungefähr 666 St./h . " Woher kommen DIESE Zahlen nun wieder?
6 bis 15 sind brutto schon nur 9,0 Stunden. Wie kommst du auf 10,5 ???

Mooko0124 · 13.08.2024, 13:21

Test.xlsx (Größe: 15,28 KB / Downloads: 14)

Ich hoffe jetzt ist die Datei aktuell.

Es sind Pausenzeit vorhanden.
08.08.24 8,5 Stunden
09.08.24 2 Stunden
Daher komme ich auf 10,5 Stunden.

Sigi.21 · 13.08.2024, 14:17

Hallo,

was soll denn jetzt berechnet werden??

Es geht offenbar um eine Produktion. 10.000 St. sollen produziert werden. Je Std. werden 500 St. produziert. Beginn und Ende werden vorgegeben.
ArbZeit beträgt von M0-Do je 8,5 Std.; Fr nur 6 Std. - M.E. liegt alles vor!
Bitte erläutere mal das Blatt "Drucker" genauer.

Gruß Sigi

Mooko0124 · 13.08.2024, 15:33

Ich weiß das alle Daten vorliegen.

Es sollen die Stück pro Stunden, die effektiv erreicht wurden, berechnet werden.

Eben genau das:
In diesem Bespiel wäre die vorhandene Arbeitszeit 10,5 Stunden, die gefertigte Menge 7.000 St. und das Ergebnis ungefähr 666 St./h .
Die 666 Stück/h sollen in Tabelle Drucker Feld H3 durch die gegeben Informationen automatisch berechnet werden.

Also muss die effektive Arbeitszeit abzüglich Wochenende und Feiertage zwischen Start und Ende berechnet werden. Und das ist mein
größtes Problem. Wie kann ich Excel sagen, dass die effektive Arbeitszeit vom 08.08.24 06:00 Uhr bis 09.08.08.24 08:00 Uhr 10,5 Stunden sind.

Wenn ich das habe, muss ich nur noch die Stunden durch die gefertigte Menge dividieren.

Das Enddatum und die Angegeben Mengen sind fiktiv also immer variabel. Es geht darum, dass geprüft wird ob in dem Fertigungszeitraum
von bis auch die angenommene Menge pro Stunde erreicht wurde.

Ego · 13.08.2024, 15:50

Hallo M...,

1. Office Version
Ab Excel 21 könnte man es mit der LET-Funktion als einzelne Formel wie unten umsetzen. Für Versionen ohne LET weigere ich mich aber das über eine Formel zu lösen und würde ein Makro empfehlen.

2. mit LET
Vorraussetzung ist
a) eine zusätzliche Datumszeile oberhalb der Tagesüberschriften und
b) ein Eintrag der zweiten Pause der Länge 0 am Freitag.

Feiertage habe ich nicht berücksichtigt, da ich erwarte, dass an diesen Tagen nichts produziert wird.

Die Funktion:

Code:
=LET(
TZON;LET(
AT;K1:O1;
ET;K1:O1+1;
WT;WOCHENTAG(K1:O1;2);
AA;AT+INDEX(Arbeitszeit!$C$2:$C$8;WT);
AE;AT+INDEX(Arbeitszeit!$F$2:$F$8;WT);
ZA;WENN(AA>[@Start];AA;[@Start]);
ZE;WENN(AE<[@Ende];AE;[@Ende]);
ZT;ZE-ZA;
ZTON;WENN(ZT>0;ZT;0);
ZTON*24)+
LET(
AT;K1:O1;
ET;K1:O1+1;
WT;WOCHENTAG(K1:O1;2);
AA;AT+INDEX(Arbeitszeit!$G$2:$G$8;WT);
AE;AT+INDEX(Arbeitszeit!$H$2:$H$8;WT);
ZA;WENN(AA>[@Start];AA;[@Start]);
ZE;WENN(AE<[@Ende];AE;[@Ende]);
ZT;ZE-ZA;
ZTON;WENN(ZT>0;ZT;0);
ZTON*24)+
LET(
AT;K1:O1;
ET;K1:O1+1;
WT;WOCHENTAG(K1:O1;2);
AA;AT+INDEX(Arbeitszeit!$I$2:$I$8;WT);
AE;AT+INDEX(Arbeitszeit!$D$2:$D$8;WT);
ZA;WENN(AA>[@Start];AA;[@Start]);
ZE;WENN(AE<[@Ende];AE;[@Ende]);
ZT;ZE-ZA;
ZTON;WENN(ZT>0;ZT;0);
ZTON*24);
SUMME(Tabelle2[@[05. Aug]:[9. Aug]])/SUMME(TZON))

Sigi.21 · 14.08.2024, 11:00

Hallo Mooko,

Zitat: "Also muss die effektive Arbeitszeit abzüglich Wochenende und Feiertage zwischen Start und Ende berechnet werden. Und das ist mein
größtes Problem. Wie kann ich Excel sagen, dass die effektive Arbeitszeit vom 08.08.24 06:00 Uhr bis 09.08.08.24 08:00 Uhr 10,5 Stunden sind."

Für ältere xl-Versionen eignet sich hier - wie Ego schon angedeutet hat - eigentlich nur VBA. Hast du daran Interesse? (Mit Formeln kommst du hier ins Nirwana.)
Ich habe etwas ähnliches schon mal gemacht. Ich müsste es lediglich auf deine Zwecke anpassen. Kannst/darfst du VBA einsetzen? Ggf. Admin fragen.

Gruß Sigi

Ego · 14.08.2024, 12:35

Hallo M...,

hab mir mal den Spass gemacht eine Formel für deine Office-Version aus der LET-Formel zusammenkopieren.

Code:
SUMME(
(WENN(WENN(K1:O1+INDEX(Arbeitszeit!$F$2:$F$8;WOCHENTAG(K1:O1;2))<[@Ende];K1:O1+INDEX(Arbeitszeit!$F$2:$F$8;WOCHENTAG(K1:O1;2));[@Ende])-WENN(K1:O1+INDEX(Arbeitszeit!$C$2:$C$8;WOCHENTAG(K1:O1;2))>[@Start];K1:O1+INDEX(Arbeitszeit!$C$2:$C$8;WOCHENTAG(K1:O1;2));[@Start])>0;WENN(K1:O1+INDEX(Arbeitszeit!$F$2:$F$8;WOCHENTAG(K1:O1;2))<[@Ende];K1:O1+INDEX(Arbeitszeit!$F$2:$F$8;WOCHENTAG(K1:O1;2));[@Ende])-WENN(K1:O1+INDEX(Arbeitszeit!$C$2:$C$8;WOCHENTAG(K1:O1;2))>[@Start];K1:O1+INDEX(Arbeitszeit!$C$2:$C$8;WOCHENTAG(K1:O1;2));[@Start]);0)*24)+
(WENN(WENN(K1:O1+INDEX(Arbeitszeit!$H$2:$H$8;WOCHENTAG(K1:O1;2))<[@Ende];K1:O1+INDEX(Arbeitszeit!$H$2:$H$8;WOCHENTAG(K1:O1;2));[@Ende])-WENN(K1:O1+INDEX(Arbeitszeit!$G$2:$G$8;WOCHENTAG(K1:O1;2))>[@Start];K1:O1+INDEX(Arbeitszeit!$G$2:$G$8;WOCHENTAG(K1:O1;2));[@Start])>0;WENN(K1:O1+INDEX(Arbeitszeit!$H$2:$H$8;WOCHENTAG(K1:O1;2))<[@Ende];K1:O1+INDEX(Arbeitszeit!$H$2:$H$8;WOCHENTAG(K1:O1;2));[@Ende])-WENN(K1:O1+INDEX(Arbeitszeit!$G$2:$G$8;WOCHENTAG(K1:O1;2))>[@Start];K1:O1+INDEX(Arbeitszeit!$G$2:$G$8;WOCHENTAG(K1:O1;2));[@Start]);0)*24)+
(WENN(WENN(K1:O1+INDEX(Arbeitszeit!$D$2:$D$8;WOCHENTAG(K1:O1;2))<[@Ende];K1:O1+INDEX(Arbeitszeit!$D$2:$D$8;WOCHENTAG(K1:O1;2));[@Ende])-WENN(K1:O1+INDEX(Arbeitszeit!$I$2:$I$8;WOCHENTAG(K1:O1;2))>[@Start];K1:O1+INDEX(Arbeitszeit!$I$2:$I$8;WOCHENTAG(K1:O1;2));[@Start])>0;WENN(K1:O1+INDEX(Arbeitszeit!$D$2:$D$8;WOCHENTAG(K1:O1;2))<[@Ende];K1:O1+INDEX(Arbeitszeit!$D$2:$D$8;WOCHENTAG(K1:O1;2));[@Ende])-WENN(K1:O1+INDEX(Arbeitszeit!$I$2:$I$8;WOCHENTAG(K1:O1;2))>[@Start];K1:O1+INDEX(Arbeitszeit!$I$2:$I$8;WOCHENTAG(K1:O1;2));[@Start]);0)*24)
)

Ego · 14.08.2024, 13:21

Hallo M...,

sorry. Die Aussage, dass die Feiertagsliste nicht berücksichtigt werden muss war falsch hier die korrigierten Formeln:

mit LET

Code:
=LET(
TZON;LET(
AT;K1:O1;
ET;K1:O1+1;
WT;WOCHENTAG(K1:O1;2);
AA;AT+INDEX(Arbeitszeit!$C$2:$C$8;WT);
AE;AT+INDEX(Arbeitszeit!$F$2:$F$8;WT);
ZA;WENN(AA>[@Start];AA;[@Start]);
ZE;WENN(AE<[@Ende];AE;[@Ende]);
ZT;ZE-ZA;
ZTON;WENN(ZT>0;ZT;0);
ZTON*24)+
LET(
AT;K1:O1;
ET;K1:O1+1;
WT;WOCHENTAG(K1:O1;2);
AA;AT+INDEX(Arbeitszeit!$G$2:$G$8;WT);
AE;AT+INDEX(Arbeitszeit!$H$2:$H$8;WT);
ZA;WENN(AA>[@Start];AA;[@Start]);
ZE;WENN(AE<[@Ende];AE;[@Ende]);
ZT;ZE-ZA;
ZTON;WENN(ZT>0;ZT;0);
ZTON*24)+
LET(
AT;K1:O1;
ET;K1:O1+1;
WT;WOCHENTAG(K1:O1;2);
AA;AT+INDEX(Arbeitszeit!$I$2:$I$8;WT);
AE;AT+INDEX(Arbeitszeit!$D$2:$D$8;WT);
ZA;WENN(AA>[@Start];AA;[@Start]);
ZE;WENN(AE<[@Ende];AE;[@Ende]);
ZT;ZE-ZA;
ZTON;WENN(ZT>0;ZT;0);
ZTON*24);
SUMME(Tabelle2[@[05. Aug]:[9. Aug]])/SUMME(TZON*ISTFEHLER(VERGLEICH(K$1:O$1;Feiertage!A1:A11;0))))

ohne LET

Code:
=SUMME(Tabelle22[@[05. Aug]:[9. Aug]])/
SUMME(
(
(WENN(WENN(K1:O1+INDEX(Arbeitszeit!$F$2:$F$8;WOCHENTAG(K1:O1;2))<[@Ende];K1:O1+INDEX(Arbeitszeit!$F$2:$F$8;WOCHENTAG(K1:O1;2));[@Ende])-WENN(K1:O1+INDEX(Arbeitszeit!$C$2:$C$8;WOCHENTAG(K1:O1;2))>[@Start];K1:O1+INDEX(Arbeitszeit!$C$2:$C$8;WOCHENTAG(K1:O1;2));[@Start])>0;WENN(K1:O1+INDEX(Arbeitszeit!$F$2:$F$8;WOCHENTAG(K1:O1;2))<[@Ende];K1:O1+INDEX(Arbeitszeit!$F$2:$F$8;WOCHENTAG(K1:O1;2));[@Ende])-WENN(K1:O1+INDEX(Arbeitszeit!$C$2:$C$8;WOCHENTAG(K1:O1;2))>[@Start];K1:O1+INDEX(Arbeitszeit!$C$2:$C$8;WOCHENTAG(K1:O1;2));[@Start]);0)*24)+
(WENN(WENN(K1:O1+INDEX(Arbeitszeit!$H$2:$H$8;WOCHENTAG(K1:O1;2))<[@Ende];K1:O1+INDEX(Arbeitszeit!$H$2:$H$8;WOCHENTAG(K1:O1;2));[@Ende])-WENN(K1:O1+INDEX(Arbeitszeit!$G$2:$G$8;WOCHENTAG(K1:O1;2))>[@Start];K1:O1+INDEX(Arbeitszeit!$G$2:$G$8;WOCHENTAG(K1:O1;2));[@Start])>0;WENN(K1:O1+INDEX(Arbeitszeit!$H$2:$H$8;WOCHENTAG(K1:O1;2))<[@Ende];K1:O1+INDEX(Arbeitszeit!$H$2:$H$8;WOCHENTAG(K1:O1;2));[@Ende])-WENN(K1:O1+INDEX(Arbeitszeit!$G$2:$G$8;WOCHENTAG(K1:O1;2))>[@Start];K1:O1+INDEX(Arbeitszeit!$G$2:$G$8;WOCHENTAG(K1:O1;2));[@Start]);0)*24)+
(WENN(WENN(K1:O1+INDEX(Arbeitszeit!$D$2:$D$8;WOCHENTAG(K1:O1;2))<[@Ende];K1:O1+INDEX(Arbeitszeit!$D$2:$D$8;WOCHENTAG(K1:O1;2));[@Ende])-WENN(K1:O1+INDEX(Arbeitszeit!$I$2:$I$8;WOCHENTAG(K1:O1;2))>[@Start];K1:O1+INDEX(Arbeitszeit!$I$2:$I$8;WOCHENTAG(K1:O1;2));[@Start])>0;WENN(K1:O1+INDEX(Arbeitszeit!$D$2:$D$8;WOCHENTAG(K1:O1;2))<[@Ende];K1:O1+INDEX(Arbeitszeit!$D$2:$D$8;WOCHENTAG(K1:O1;2));[@Ende])-WENN(K1:O1+INDEX(Arbeitszeit!$I$2:$I$8;WOCHENTAG(K1:O1;2))>[@Start];K1:O1+INDEX(Arbeitszeit!$I$2:$I$8;WOCHENTAG(K1:O1;2));[@Start]);0)*24)
)*ISTFEHLER(VERGLEICH(K$1:O$1;Feiertage!A1:A11;0))
)

Mooko0124 · 15.08.2024, 07:44

Hallo Ego,

wirklich vielen Dank bis hierhin, dass übersteigt bei weitem meine Excelfähigkeiten.

Mir sind zwei Dinge aufgefallen.
1. Die Datumstabelle wird nach unten erweitert. Ist das richtig?
2. Die Feiertage sind nicht fixiert. Sobald ich, diese fixieren will, bekomme ich einen Fehler in der Formel (Wert).

Im Bild habe ich diese Stellen gelb markiert.

Zusätzlich würde ich gerne die Spätschicht einbringen. Wie in der Tabelle Drucker ohne Let zu sehen. Die Arbeitszeit habe ich hinterlegt.
Ich hätte gern die Formel angepasst, aber mir ist nicht klar wo die Arbeitszeit berechnet werden. F2 bis F8 ? Das macht für mich keinen Sinn.

Es ist aber auch möglich, dass die Maschine einen Tag mal nicht läuft. Dafür würde ich gerne z.B. statt der Stückzahl ein X einfügen. Dann soll die
Arbeitszeit nicht berücksichtig werden. Ist das möglich?

Gruß

Login
Benutzername:
Passwort:	Passwort vergessen?
	Merken