Da ich mehrere hundert Zeitperioden in meiner Datei habe, machts die ganze Sache etwas kompliziert, weshalb ich am liebsten eine Funktion suche, für die ich nicht viel mehr als Anfangs und Enddatum einzugeben brauche.
Da ich mehrere hundert Zeitperioden in meiner Datei habe,
machts die ganze Sache etwas kompliziert, weshalb ich am
liebsten eine Funktion suche, für die ich nicht viel mehr als
Anfangs und Enddatum einzugeben brauche.
Hallo thierry lustenberger
Die Anzahl der Wochentage lassen sich natürlich per Formel errechnen. Beispiel:
A1=Beginn
A2=Ende
C1=1 [für Mo. bis So. Ziffer 1 bis 7]
{=SUMME(WENN(WOCHENTAG(ZEILE(INDIREKT(A1&":"&A2));2)=C1;1;0))}
>Matrix-Formel: Die Umrandende { } nicht mit eingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen!
Da du diese Funktion oft brauchst würde ich die Frage noch mal ins Forum stellen. Es liegt nahe das als benutzerdefinierte Funktion zu erstellend wo nur Beginn, Ende, und Wochentag abgefragt werden.
da ich die konkrete Aufgabenstellung Datenlage nicht kenne wäre mein vorgehen folgendermaßen:
Ich baue eine Datenreihe vom Anfangsdatum bis zum Endatum auf. Ermittel dann mit der Funktion Wochentag() den jeweiligen Wochentag. dann erstelle ich eine PivotTable und zähle damti den gefragten Wochentag mit der Funktion Anzahl - und schon habe ich die Anzahl der in dem Zeitraum vorkommenden Wochentage. Dabei ist es unabhänig ob ich beispielsweise nur den Dienstag oder Dienstag und Mittwoch zählen und addieren möchte.
Beste Grüße
H. Schuster
Der Bereich Wenn(…) überprüft vom ersten bis zum letzten Tag ob das Datum zum Wochentag passt. Wenn ja gibt es eine 1 sonnst eine 0.
Es entsteht eine Zahlenreihe 0;1;0;0;0;0;0;0;1…
Diese wird von Summe() addiert.
Für die Zahlenreihe kann ich alle Datum untereinander in eine Spalte Schreiben, mit Wochentag() prüfen und anschließend die Summe bilden. siehe Link
Man kann Excel aber auch anwiesen alle diese Berechnungen zu durchlaufen, man erzeugt eine Matrix-Formel wo der Bereich angegeben sein muss.
In der Formel steht durch die Funktionen ZEILE(INDIREKT(A1&":"&A2)folgendes:
{=SUMME(WENN(WOCHENTAG(40389:40404);2)=C1;1;0))}
Es wir der vom Tag 40389 bis 40404 geprüft.
du kannst die Anzahl der Tage berechnen lassen, (einfach Zelle mit Enddatum - Zelle mit Anfangsdatum), dabei muss dann die Zelle mit der Formel als Zahl formatiert werden.
Als Ergebniss kommt dann hier 15 raus.
Ich weis leider keine Formel für die Berrechnung der einzelnen Wochentage.
Sorry, aber vielleicht kommst du mit der Anzahl der Tage weiter
Hallo, Thierry,
man könnte über mehrere Zwischenschritte und viele geschachtelte Wenn-Funktionen arbeiten, aber das wird dann sehr lang und unübersichtlich. Deshalb hier ein Vorschlag mit einer selbst programmierten Funktion, die als AddIn aufgenommen wird und dann wie jede normale Excel-Funktion aufgerufen werden kann.
Vorgehen:
In Excel über Extras -> Makro -> Visual Basic-Editor ins VB-Fenster gehen. Dort je nach Excel-Version über Symbolleiste oder Menü neues Modul einfügen. Falls in dem Modulfenster nicht schon Option Explicit steht, dies als erste Zeile schreiben, sonst mit Public Function … beginnen und alle Programmzeilen bis einschl. End Function abschreiben. Dabei auf ganz korrekte Schreibweise aller Namen und Funktionen achten! Zusätzliche Leerzeilen sind erlaubt. Die Public Function-Zeile ohne Zeilenumbruch bis einschl Integer schreiben!
Option Explicit
Public Function Wtag_Anzahl(Beginn As Date, Ende As Date, tag As Byte) As Integer
Dim i As Integer
If Beginn > Ende Or tag 7 Then
Wtag_Anzahl = 0
Exit Function
End If
i = 0
Do
If Weekday(Beginn, 2) = tag Then i = i + 1
Beginn = Beginn + 1
Loop While Beginn Extras -> AddInns-Manager öffnen (genaue Bezeichnung im Menü hängt von Version ab). Entweder steht der Name der AddIn-Datei bereits im Listenfeld und muss evtl. nur noch mit einem Häkchen aktiviert werden, oder man muss erst die Durchsuchen-Schaltfläche benutzen.
Nun sollte die Funktion Wtag_Anzahl in jeder Excel-Mappe verwendet werden können; sie steht auch im Funktionsassistenten unter der Kategorie Benutzerdefiniert zur Verfügung.
Es ist zwar etwas Arbeit zur Erstellung der Funktion nötig, aber danach lässt sie sich so einfach anwenden wie eine Summe.
Ich hoffe, Dir ist damit geholfen!
Viele Grüße
Heidi
Danke für deine Hilfe!
Ich bin hochinteressiert, eine solche Funktion zu erstellen.
Allerdings habe ich erst einmal vor etwas längerer Zeit mit Makros gearbeitet, und damals auf einer anderen Version.
Ich wäre deshalb heilfroh, wenn du mir eine zweite Starthilfe geben könntest. Deine Beschreibung ist zwar ziemlich gut, aber ich verliere mich relativ schnell in meiner Version.
Ich erkläre dir somit wie’s in meiner Version aussieht. Vielleicht etwas kompliziert sich das im Kopf vorzustellen, mit Stift und Papier sollte es aber aufzuzeichnen und zu verstehen sein:
Meine Excel Version: Excel 08 (bin nicht vollständig zufrieden damit, hoffe aber das Makro lässt sich trotzdem programmieren)
Im Menu Extras habe ich folgende Auswahlmöglichkeiten:
Makros…
Wenn ich darauf klicke (kann nur klicken, nichts auswählen) kommt ein Fenster:
Ein Feld über dem Makroname steht. Darin kann ich schreiben, was ich will. Rechts davon ist ein Klickfeld, mit dem ich auf eine Zelle in der Datei verweisen kann.
Darunter ein grosses leeres Feld, in das ich nichts schreiben kann… könnte es sein das darin schon vorhandene Markos angezeigt würden (hab ja noch keine), die ich dann auswählen könnte?
Darunter steht: „Makros in:“, daneben ein Auswahlfenster mit den Möglichkeiten „Allen offenen Arbeitsmappen“, „Dieser Arbeitsmappe“ und „Arbeitsmappe1“ (Name der geöffneten Datei).
Darunter ein leeres Feld über dem steht: Beschreibung
Ganz rechts sind vier Klickfelder: Ausführen, Abbrechen, Einzelschritt, Optionen. Davon ist allerdings nur Abbrechen anklickbar… worauf sich das Fenster natürlich schliesst.
Add-Ins…
Fenster mit:
leeres Feld, in das ich nicht schreiben kann (ähnlich wie oben) über dem steht „Verfügbare Add-Ins“
rechts daneben das Klickfeld „Auswählen…“, wenn ich darauf klicke, öffnet sich ein Fenster in dem ich eine Datei auf meinem Computer öffnen und auswählen könnte/sollte.
darunter nur die beiden Klickfelder „Abbrechen“ und „OK“
Hoffe das war einigermassen verständlich. Meine eMail addresse wäre [email protected], falls du screen shots haben willst, oder es die ganze Sache vereinfachen würde.
Wenn du mir aufgrund den obengenannten Versionen sagen kannst, wo genau ich den Programm Code eingeben kann, wäre das 100% genial!
kurze Erklärung
A2 = Start Datum
B2 = Ende Datum
C2 = 1 (für So), C3 = 2 (für Mo), C4 = 3 (für Di), …
Formel (in D2, D3, …)
=RUNDEN((B$2-A$2)/7;0)+WENN(WOCHENTAG(A$2)=C2;1;0)-1
Erklärung:
RUNDEN((B$2-A$2)/7;0) = Anzahl voller Wochen in der angebenen Periode
WENN(WOCHENTAG(A$2)=C2;1;0) = Ende der Periode liegt am oder vor dem angegebenen Wochentag
-1 (Korrekturwert, da der erste gefundene Tag auf Anzahl der Wochen aufaddiert wurde)
Die Formel enthält also 8 Berechnungen, wahrscheinlich nicht die Effizienteste. Außerdem keine Garantie auf Korrektheit ;o)
Hallo, Thierry,
die Version Excel 08 sagt mir nichts. Ich habe das AddIn mit Excel 2000 gemacht, in Excel XP und 2003 müsste der Menüverlauf etwa gleich sein. In Excel 2007 geht der Weg über die Multifunktionsleiste Entwicklertools -> Code anzeigen. Die Aktivierung des AddIns erfolgt dann über das Office-Symbol -> Excel-Optionen -> AddIns.
Falls Du damit nicht klar kommst, schicke ich Dir gern die Datei. Du müsstest dann nur den Schritt 3 aus meiner ersten Beschreibung ausführen, indem Du in dem Dialogfeld auf Auswählen klickst und den betr. Ordner und die Datei raussuchst.
Viele Grüße
Heidi
Hallo Thierry,
war in Urlaub - sorry!
ist das Problem noch aktuell?
Sollte lösbar sein, könnte aber etwas umständlich sein!
Ich schaue es mir gerne nochmal an.
B.
Die Anzahl der Wochentage lassen sich natürlich per Formel errechnen. Beispiel:
A1=Beginn
A2=Ende
C1=1 [für Mo. bis So. Ziffer 1 bis 7]
{=SUMME(WENN(WOCHENTAG(ZEILE(INDIREKT(A1&":"&A2));2)=C1;1;0))}
>Matrix-Formel: Die Umrandende { } nicht mit eingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen!