Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1648to1652
1648to1652
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Turnus-Berechnung

Forumthread: Turnus-Berechnung

Turnus-Berechnung
03.10.2018 08:39:18
Stefan
Hallo zusammen,
ich benötige eine Formel um eine Turnus-Preis-Berechnung zu erstellen. Es soll ein Betrag wöchentlich bzw. zweiwöchig oder monatlich folgend ermittelt werden.
z.B. Büroreinigung wöchentlich Donnerstags
Startdatum: 04.10.2018
Turnus: 7-tägig
Preis 100,00 €
Es soll nun ermittelt werden, wie viele Büroreinigungen jeden Monat stattfinden und wie hoch der Umsatz monatlich ist.
Vielen Dank
MFG Stefan
Anzeige

18
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Turnus-Berechnung
03.10.2018 11:31:24
Stefan
Hallo,
danke für den Link. Das passt für die Berechnung für eine Büroreinigung wöchentlich. Ich benötige allerdings noch die Formel für den 14-tägigen Turnus, der mir die die Menge der Reinigungseinsätze pro Monat ausgibt.
z.B. Büroreinigung 14 tägig Donnerstags
Startdatum: 04.10.2018
Preis 100,00 €
Es soll nun ermittelt werden, wie viele Büroreinigungen jeden Monat stattfinden und wie hoch der Umsatz monatlich ist.
Vielen Dank
MFG
Stefan
Anzeige
AW: Turnus-Berechnung
03.10.2018 11:37:35
Hajo_Zi
Hallo Stefan,
bei mir wird gezählt in B3, Deine Datei sehe ich nicht.

Tabelle4
 AB
101.10.2018Donnerstag
231.10.20184
3 4

verwendete Formeln
Zelle Formel Bereich N/A
B2=VERGLEICH(B1;{"Montag";"Dienstag";"Mittwoch";"Donnerstag";"Freitag";"Samstag";"Sonntag"};0)  
B3=WENN((A2-A1+1-(B2-WOCHENTAG(A1;2))-WENN(WOCHENTAG(A1;2)>B2;7;0))/7<0;0;AUFRUNDEN((A2-A1+1-(B2-WOCHENTAG(A1;2))-WENN(WOCHENTAG(A1;2)>B2;7;0))/7;0))  
Excel-Inn.de
Hajo-Excel.de
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 25.14 einschl. 64 Bit


Gruß Hajo
Anzeige
Mit Summenprodukt
03.10.2018 11:53:16
RPP63
Moin!
Erstelle eine simple Liste mit den Terminen (Start+14).
Spalte mit den Monaten als echtes Datum im Format MMM JJ
 AFG
1Datum:MonatAnzahl
218.10.2018Okt181
301.11.2018Nov183
415.11.2018Dez182
529.11.2018Jan192
613.12.2018Feb192
727.12.2018Mrz192
810.01.2019Apr192
924.01.2019Mai193
1007.02.2019Jun192
1121.02.2019Jul192
1207.03.2019Aug192
1321.03.2019Sep192
1404.04.2019Okt193
1518.04.2019Nov192
1602.05.2019Dez192
1716.05.2019  
1830.05.2019  
1913.06.2019  
2027.06.2019  
2111.07.2019  
2225.07.2019  
2308.08.2019  
2422.08.2019  
2505.09.2019  
2619.09.2019  
2703.10.2019  
2817.10.2019  
2931.10.2019  
3014.11.2019  
3128.11.2019  
3212.12.2019  
3326.12.2019  
3409.01.2020  

ZelleFormel
G2=SUMMENPRODUKT(N(TEXT(A$2:A$34;"MMMJJ")=TEXT(F2;"MMMJJ")))
A3=A2+14
G3=SUMMENPRODUKT(N(TEXT(A$2:A$34;"MMMJJ")=TEXT(F3;"MMMJJ")))

Gruß Ralf
Anzeige
AW: ... in Tabelle mit "freien" Vorgabewerten ...
03.10.2018 14:11:21
neopa
Hallo Stefan,
... hab dazu eine von mir vor einiger Zeit aufgestellte allgemeinen Ermittlung nachfolgend für Deine speziellen Anforderung angepasst.
Die Basisdaten in A2 und C2:E2 sind frei wähl- und kombinierbar. Das Jahr in B2 und der Wert in G2 sind jeweils völlig frei zuweisbar. Wobei insbesondere die freie Wahl in C2:D2 von Vorteil sein kann.
Formeln B5:G5 einfach soweit nach unten kopieren, wie benötigt.
 ABCDEFG
1MonatJahrWo-Tagab welchemRhythmus Umsatz
2Oktober2018Do1.alle 14 Tageje123 €
3     Gesamt3.198 €
4 JahrMonatDatum 1. Tagletzter TagAnzahlmonatl.
5 2018OktoberDo 04.10.2018Do 18.10.20182246 €
6 2018NovemberDo 01.11.2018Do 29.11.20183369 €
7 2018DezemberDo 13.12.2018Do 27.12.20182246 €
8 2019JanuarDo 10.01.2019Do 24.01.20192246 €
9 2019FebruarDo 07.02.2019Do 21.02.20192246 €
10 2019MärzDo 07.03.2019Do 21.03.20192246 €
11 2019AprilDo 04.04.2019Do 18.04.20192246 €
12 2019MaiDo 02.05.2019Do 30.05.20193369 €
13 2019JuniDo 13.06.2019Do 27.06.20192246 €
14 2019JuliDo 11.07.2019Do 25.07.20192246 €
15 2019AugustDo 08.08.2019Do 22.08.20192246 €
16 2019SeptemberDo 05.09.2019Do 19.09.20192246 €
17       

Formeln der Tabelle
ZelleFormel
G3=SUMME(G5:G99)
B5=WAHL(1+(B4="Jahr")+(LINKS(C4;1)="D")*2;B4;B2;B4+1)
C5=WENN(C4="Monat";A$2;TEXT((("1-"&$C4&"-"&B$5)*1)+33;"MMMM"))
D5=WENN(C5="";"";WENN(ZEILE(D1)=1;("1-"&$C5&"-"&$B5)+AGGREGAT(15-ISTTEXT(D$2); 6;ZEILE(D$1:D$33)/(TEXT(ZEILE(INDEX($C:$C;"1-"&$C5&"-"&$B5):INDEX($C:$C;MONATSENDE("1-"&$C5&"-"&$B5;0))); "TTT")=C$2); WENN(ISTTEXT(D$2); 1;D$2))-1;E4+E$2))
E5=WENN(D5="";"";KÜRZEN((MONATSENDE("1-"&$C5&"-"&$B5;0)-D5)/E$2;)*E$2+D5)
F5=WENN(E5="";"";(E5-D5)/E$2+1)
G5=WENN(F5="";"";F5*G$2)

Datengültigkeit der Tabelle
ZelleZulassenDatenEingabe 1Eingabe 2
A2Liste Januar;Februar;März;April;Mai;Juni;Juli;August;September;Oktober;November;Dezember 
C2Liste Mo;Di;Mi;Do;Fr;Sa;So 
D2Liste 1;2;3;4;letzter 
E2Liste 7;14;21;28 


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Anzeige
AW: ... in Tabelle mit "freien" Vorgabewerten ...
05.10.2018 11:05:00
Stefan
Hallo Werner,
erstmal vielen Dank für deine Hilfe. Mit deinen Formeln funktioniert es! Aber leider bekomme ich die Formel nicht so umgeschrieben, dass meine Tabelle funktioniert.
Anbei meine Tabelle, kannst du bitte mal schauen, ob das auch so umsetzbar wäre.
https://www.herber.de/bbs/user/124425.xlsx
Gruß
Stefan
Anzeige
AW: Formelanpassung ist möglich ...
05.10.2018 11:56:46
neopa
Hallo Stefan,
... wäre aber schon in Deinem Fall eine kleine Projektlösung und somit außerhalb "normaler" Forums-Hilfeleistung.
Gruß Werner
.. , - ...
Zu deiner Tabelle hier 2 Fragen
05.10.2018 22:04:12
Josef
Hallo Stefan
1. Ist das Startdatum auch immer der erste Reinigungstag?
2. Wie wird vorgegangen, wenn ein Reinigungstag Turnusgemäss auf einen Feiertag fällt. Bleibt dann die "Bude" für einen weiteren Turnus dreckig ;-)
Gruss Sepp
Anzeige
AW: Zu deiner Tabelle hier 2 Fragen
05.10.2018 23:36:26
Stefan
Hallo Sepp,
ja das Startdatum ist immer der erste Reinigungstag.
Feiertage werden in dieser Planung nicht berücksichtigt, da die Reinigung sich meistens nur leicht verschiebt.
Kannst du mir weiterhelfen?
Gruß Stefan
nur noch eine Frage
06.10.2018 22:20:19
Josef
Hallo Stefan
Ich denke schon, dass ich dir helfen kann.
Das wird aber aus zeitlichen Gründen sicher erst am Sonntag nachts möglich sein.
Und nun zu meiner Frage: Was schreibst du in die Spalte D bei einem Monatlichen Turnus?
Gruss Sepp
Anzeige
AW: nur noch eine Frage
07.10.2018 10:18:06
Stefan
Hallo Sepp,
das wäre ja echt super!
Bei einer monatlichen Reinigung würde ich einen Festpreis eintragen, da sich dieser nicht ändert.
Wenn ein Objekt an zwei Wochentagen gereinigt wird, würde ich es in erste und zweite Reinigung unterteilen. Also würde ich es wie zwei verschiedene Objekte behandeln.
Beste Grüße Stefan
Anzeige
AW: nur noch eine Frage
07.10.2018 23:17:11
Josef
Hallo Stefan
Teste einmal in F4 folgende Formel
=SUMMENPRODUKT(--(TEXT($B4+(ZEILE(Z$1:Z$54)-1)*$D4;"MJJJJ")=SPALTE(B1)/2&$B$1))
Diese Formel ist nach unten und nach rechts kopierbar.
Gruss Sepp
AW: eine geniale Formel ...
08.10.2018 10:30:52
neopa
Hallo Sepp,
... wieder eine echte "Hammer"-Zeitberechnungsformel von Dir.
Wie meine ersten oberflächlichen Test dieser ergeben haben, müsste jedoch folgende zusätzliche Einschränkungen Stefan mit auf den Weg gegeben werden. Das Startdatum in B4:B## muss zwingend im in $B$1 angegebenen Jahr liegen und der Turnus sollte nicht kleiner als 7 sein. Beides sollte jedoch für Stefan problemlos sein.
Eine Kleinigkeit nur noch, mit der ich mich allgemein nicht wirklich anfreunden kann, ist: (ZEILE(Z$1:Z$54) in Deiner Formel. Schon für März lautet dann der Formelteil dann nämlich: (ZEILE(AD$1:AD$54) und keiner weiß wirklich, ob der Nutzer nicht mal eine oder mehrere Spalten hinter AD löscht. Deshalb ist mE etwas sicherer, für diesen Formelteil eine Spalte zu nutzen, wo es sofort auffallen würde, wenn diese Spalte gelöscht wird, z.B. so (ZEILE($B$1:$B$54).
Aber generell kann ich nur nochmal betonen, dass war wieder eine geniale Formel von Dir.
Gruß Werner
.. , - ...
Anzeige
Danke für die Blumen und...
08.10.2018 13:52:00
Josef
Hallo Werner
... deine Bedenken gegen (ZEILE(Z$1:Z$54) teile ich voll und ganz.
Auch ich würde schlussendlich (ZEILE($B$1:$B$54) verwenden.
Meine gepostete Lösung sah ich eher als ersten Schritt zum Testen, denn da ist noch der Turnus 1*Monatlich. Aber dazu habe ich von Stefan bis anhin zu wenig Informationenbekommen.
Gruss Sepp
Anzeige
AW: Danke für die Blumen und...
08.10.2018 20:04:59
Stefan
Hallo Sepp, Hallo Werner,
vielen Dank Sepp für die großartige Formel!!! Ich habe heute schon mal ein wenig getestet und es funktioniert super.
Eines ist mir noch aufgefallen... man muss als Startdatum immer den Januar wählen, da die Formel sonst nicht funktioniert. Allerdings kommen im laufenden Kalenderjahr weitere Objekte dazu, sodass es durchaus einen anderen Startmonat geben kann.
Bei der monatlichen Reinigung habe ich jetzt im Turnus 31 Tage gewählt. Dieses funktioniert durchaus wenn der Starttag am Monatsanfang liegt. Anders sieht es aus, wenn man ein Tag am Monatsende wählt.
Hast du vielleicht noch ne Idee wie man diese beiden Dinge überarbeiten kann?
Vielen Dank nochmals!!!
Beste Grüße
Stefan
Anzeige
Formel funktioniert nicht
08.10.2018 20:51:22
Josef
Hallo Stefan
Ich kann den Fehler beim 7 und 14tägigen Turnus nicht nachvollziehen.
Bitte lade doch nochmals eine Tabelle hoch in der ersichtlich ist, was nicht ordnungsgemäss funktioniert. Auch Beispiele vom monatlichen Turnus sollten nicht fehlen.
Gruss Sepp
AW: Formel funktioniert nicht
09.10.2018 21:12:01
Stefan
Hallo Sepp, hallo Werner,
die Formel funktioniert... da hatte sich bei mir ein Fehler eingeschlichen. Bei monatlich habe ich bis jetzt einfach den Reinigungspreis in die einzelnen Monaten eingetragen.
@ Werner vielen Dank für die Formel, schaue ich mir jetzt an.
Beste Grüße Stefan
Anzeige
AW: Formel ist bei richtigem Einsatz korrekt ...
09.10.2018 16:57:49
neopa
Hallo Stefan,
... ich kann Deine Aussage, wonach diese bei einem anderen Startdatum als im Januar nicht funktionieren würde, ebenfalls nicht nachvollziehen.
Es ist jedoch notwendig, wie ich bereits schrieb, dass das jeweilige Startdatum in dem Jahr liegen muss, welches Du als Jahreszahl in B1 zu stehen hast.
Einen monatlichen Turnus kannst Du z.B. auch mit einem beliebigen Startdatum berechnen, wenn Du in der Datenzeile den Spaltenwert leer lässt und in F4 z.B. folgende erweiterte Formel einsetzt und diese entsprechend kopierst:
=($D4="")*($B4-TAG($B4)0))+SUMMENPRODUKT(--(TEXT($B4+(ZEILE(Z$1:Z$54)-1)*$D4; "MJJJJ")=SPALTE(B1)/2&$B$1))*($D4>0) Sepp hat aber sicherlich noch eine andere Lösungsvariante, aber zuvor solltest Du auf seinen letzten Beitrag reagieren.
Gruß Werner
.. , - ...
Anzeige
;
Anzeige

Infobox / Tutorial

Turnus-Berechnung in Excel

Schritt-für-Schritt-Anleitung

Um eine Turnus-Berechnung in Excel durchzuführen, folge diesen Schritten:

  1. Basisdaten eingeben:

    • Öffne eine neue Excel-Tabelle.
    • Gib in Zelle A1 das Startdatum ein (z.B. 04.10.2018).
    • In Zelle B1 gib den Turnus ein (z.B. 14 für einen 14-tägigen Turnus).
    • In Zelle C1 gib den Preis pro Reinigung ein (z.B. 100 für 100,00 €).

  2. Berechnung der Reinigungstage:

    • In Zelle D1 kannst du die Formel für die Anzahl der Reinigungen pro Monat eingeben: 
      =SUMMENPRODUKT(--(TEXT(A1+(ZEILE(1:100)-1)*B1;"MM")=TEXT(A1;"MM")))
    • Diese Formel zählt, wie oft der Reinigungstag im aktuellen Monat vorkommt.

  3. Monatlichen Umsatz berechnen:

    • In Zelle E1 berechnest du den Umsatz: 
      =D1*C1

  4. Automatisierung:

    • Ziehe die Formeln in D1 und E1 nach unten, um die Berechnung für mehrere Monate zu automatisieren.

Häufige Fehler und Lösungen

  • Fehler: Die Daten werden nicht korrekt gezählt.

    • Lösung: Überprüfe, ob das Startdatum im richtigen Format eingegeben ist. Stelle sicher, dass das Format der Zelle als Datum festgelegt ist.

  • Fehler: Umsatz wird nicht angezeigt.

    • Lösung: Stelle sicher, dass du die richtigen Zellreferenzen in der Formel verwendest und dass der Preis korrekt eingegeben wurde.

Alternative Methoden

Eine alternative Methode zur Berechnung eines 14-tägigen Turnus besteht darin, die Excel-Funktion WENN zu verwenden, um die Reinigungstage zu ermitteln. Beispiel:

=WENN(WOCHENTAG(A1)=5;A1+(14*1);A1+(14*(WOCHE(A1)-1)))

Diese Formel gibt dir den nächsten Reinigungstag zurück, wenn der aktuelle Tag ein Donnerstag ist.

Praktische Beispiele

Beispiel 1: Wöchentliche Reinigung

  • Startdatum: 01.10.2018
  • Turnus: 7
  • Umsatz:
    • Reinigungen pro Monat: 4 (Oktober 2018)
    • Umsatz: 4 * 100,00 € = 400,00 €

Beispiel 2: Monatliche Reinigung

  • Startdatum: 01.10.2018
  • Turnus: 30
  • Umsatz:
    • Reinigungen pro Monat: 1
    • Umsatz: 1 * 100,00 € = 100,00 €

Für eine genaue Berechnung des Umsatzes kannst du auch die Funktion DATUM verwenden, um sicherzustellen, dass das Datum auf den Monatsanfang gerundet wird.

Tipps für Profis

  • Excel Datum auf Monatsanfang runden: Verwende die Funktion DATUM, um das Datum automatisch auf den ersten Tag des Monats zu setzen:

    =DATUM(JAHR(A1);MONAT(A1);1)

  • Verwende bedingte Formatierungen, um Reinigungstage visuell hervorzuheben. Das macht es einfacher, einen Überblick über die geplanten Reinigungen zu behalten.

  • Optimiere die Formeln: Nutze SUMMENPRODUKT und TEXT, um Daten effizient zu verarbeiten und Ergebnisse dynamisch zu aktualisieren.

FAQ: Häufige Fragen

1. Frage Wie kann ich sicherstellen, dass die Formeln bei verschiedenen Startdaten funktionieren?

Antwort: Stelle sicher, dass das Startdatum im Jahr liegt, welches in deiner Berechnung berücksichtigt wird. Für monatliche Turnusberechnungen kannst du auch beliebige Startdaten verwenden, solange die Zellreferenzen korrekt sind.

2. Frage Wie gehe ich mit Feiertagen um, wenn sie auf einen Reinigungstag fallen?

Antwort: In der aktuellen Planung werden Feiertage nicht berücksichtigt. Du kannst das Datum manuell anpassen oder eine zusätzliche Spalte für Feiertage einfügen, um die Reinigungstage entsprechend zu verschieben.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige