Die Power von Excel nutzen und anwenden

Klick den Tipp!


Bilderauswahl

Video - Matrixformeln

excelmx

Video - Dynamische Diagramme

 

excel2010dyd

 

Video - Diagrammerstellung


exceldiagramme

Microsoft Award 2011

Microsoft Community Contributor

Excel 2003/2007 nachrüsten Drucken
Fragen und Antworten zu Excel - Formeln und Funktionen
Geschrieben von: Frank Arendt-Theilen   

 

Die Funktion NETTOARBEITSTAGE()link kann ich nicht benutzen, da in meinem Gewerbe nicht der Samstag und Sonntag, sondern der Sonntag und Montag als Wochenendtag gelten.

Ich habe davon erfahren, das es in Excel 2010 möglich ist mit der Funktion NETTOARBEITSTAGE.INTL()link die Wochenendtage variabel einzurichten. Kann man das nicht auch irgendwie in Excel 2003 oder Excel 2007 hinbekommen?

Die Funktion NETTOARBEITSTAGE.INTL() aus Excel 2010 kann in einer vereinfachten Version als Matrixformel oder mit einer benutzerdefinierten Funktion nachgebildet werden.


1.) Als Matrixformel

Die folgende Matrixformel berechnet die Arbeitstage zwischen (incl.) einem Anfangsdatum und Enddatum. Sie rechnet dabei angebbare Wochenendtage aus der ermittelten Tagesanzahl heraus.

Die Formel erwartet in Zelle A1 das Anfangsdatum, in Zelle B1 das Enddatum und in Zelle C1 eine Ziffernfolge die angibt, welche Wochentage als Wochenendtage herausgerechnet werden sollen. Eine Liste der freien Tage wird in dieser Formel nicht berücksichtigt (4. Argument der Funktion NETTOARBEITSTAGE.INTL()).

Anfangsdatum ist ein gültiges Excel-Datum.
Enddatum ist ein gültiges Excel-Datum.
Zeichenfolge ist eine Ziffernfolge aus den Ziffern 1 bis 7 (Mo = 1, Di = 2, ..., So = 7). Die Ziffernfolge 17 kennzeichnet den Montag(1) und den Sonntag(7) als Wochenendtag. Diese beiden Wochentage werden bei der Zählung der Tage zwischen dem Anfangsdatum und dem Enddatum nicht berücksichtigt.

Die Matrixformel lautet:

=(ABS(B1-A1)+1)-SUMMENPRODUKT(--(WOCHENTAG(ZEILE(INDIREKT(A1&":"&B1));2)=MTRANS(WERT(TEIL(C1;ZEILE(INDIREKT("1:"&LÄNGE(C1)));1)))))


Schließen Sie die Eingabe der Matrixformel mit der Tastenkombination Strg+Umsch+Enter ab. Excel umschließt die Formel mit geschweiften Klammern und kennzeichnet sie damit als Matrixformel.

Beispiel:

A1: 23.02.2011
B1: 01.04.2011
C1 (Mo =1, So = 7): 17
Ergebnis: 28; es befinden sich zwischen (incl.) dem Anfangsdatum und dem Enddatum 28 Arbeitstage.


Geben Sie den relevanten Zellen einen Namen, ist die folgende Matrixformel universell, ohne Adressanpassung, einsetzbar:

=(ABS(Enddatum-Anfangsdatum)+1)-SUMMENPRODUKT(--(WOCHENTAG(ZEILE(INDIREKT(Anfangsdatum&":"&Enddatum));2)=MTRANS(WERT(TEIL(Zeichenfolge;ZEILE(INDIREKT("1:"&LÄNGE(Zeichenfolge)));1)))))


Schließen Sie die Eingabe der Matrixformel mit der Tastenkombination Strg+Umsch+Enter ab. Excel umschließt die Formel mit geschweiften Klammern und kennzeichnet sie damit als Matrixformel.

 

2.) Als benutzerdefinierte Funktion

Die folgende benutzerdefinierte Funktion ist eine vereinfachte Adaption der Tabellenblattfunktion NETTOARBEITSTAGE.INTL() aus Excel 2010. Sie kann in allen Versionen ab Excel 2003 eingesetzt werden:


Option Base 1

Function NETTOARBEITSTAGE_INTL(Ausgangsdatum As Long, _
                                                    Enddatum As Long, _
                                                    Optional Zeichenfolge As String = "0000011", _
                                                    Optional Freie_Tage As Range) As Variant

'********************************************************************************
'*
'* Copyright 2011 by Frank Arendt-Theilen, Hameln
'*
'* Diese Funktion ist eine vereinfachte Adaption der Tabellenblattfunktion
'* NETTOARBEITSTAGE.INTL aus Excel 2010. Sie kann in allen Versionen ab Excel 2003
'* eingesetzt werden.
'*
'* Syntax: =NETTOARBEITSTAGE_INTL(Ausgangsdatum;Enddatum[;Zeichenfolge][;Freie_Tage])
'*
'********************************************************************************

Dim lngI As Long
Dim arrZeichenfolge(7)
Dim rngCell As Range

'das Ausgangsdatum muss kleiner als Enddatum sein, sonst Fehlerwert #ZAHL!
   If Enddatum < Ausgangsdatum Then
     NETTOARBEITSTAGE_INTL = CVErr(xlErrNum)
     Exit Function
   End If

   'die Länge von Zeichenfolge muss sieben Zeichen betragen, sonst Fehlerwert #WERT!
   If Len(Zeichenfolge) <> 7 Then
     NETTOARBEITSTAGE_INTL = CVErr(xlErrValue)
     Exit Function
   End If

   For lngI = 1 To 7
   'Zeichenfolge darf nur 0 und 1 enthalten, sonst Fehlerwert #WERT!
     If Mid(Zeichenfolge, lngI, 1) Like "[0-1]" Then
       arrZeichenfolge(lngI) = Mid(Zeichenfolge, lngI, 1) * lngI
     Else
       NETTOARBEITSTAGE_INTL = CVErr(xlErrValue)
       Exit Function
     End If
   Next

   For lngI = Ausgangsdatum To Enddatum
     If Application.WorksheetFunction.Weekday(lngI, 2) <> _
       arrZeichenfolge(Application.WorksheetFunction.Weekday(lngI, 2)) Then
       NETTOARBEITSTAGE_INTL = NETTOARBEITSTAGE_INTL + 1
     Else
       'falls das Datum in der Zeichenfolge als ein freier Tag markiert
       'und gleichzeitig in der Liste der freien Tage enthalten ist
       If Not Freie_Tage Is Nothing Then
         For Each rngCell In Freie_Tage.Cells
           If rngCell.Value = lngI Then
             NETTOARBEITSTAGE_INTL = NETTOARBEITSTAGE_INTL + 1
           End If
         Next
       End If
     End If

     If Not Freie_Tage Is Nothing Then
       For Each rngCell In Freie_Tage.Cells
         If rngCell.Value = lngI Then
           NETTOARBEITSTAGE_INTL = NETTOARBEITSTAGE_INTL - 1
         End If
       Next
     End If
   Next

End Function


Anwendung

Syntax: =NETTOARBEITSTAGE_INTL(Ausgangsdatum;Enddatum[;Zeichenfolge][;Freie_Tage])

Ausgangswert (erforderlich). Ausgangsdatum ist ein gültiges Datum in Excel. Dieses Datum muss vor dem Enddatum liegen, sonst gibt die Funktion den Fehlerwert #ZAHL! zurück.

Enddatum (erforderlich). Enddatum ist ein gültiges Datum in Excel. Dieses Datum muss nach dem Anfangsdatum liegen, sonst gibt die Funktion den Fehlerwert #ZAHL! zurück.

Zeichenfolge (optional). Zeichenfolge ist eine Folge von sieben Zeichen, entweder eine 0 (Null) für einen Arbeitstag oder eine 1 (Eins) für einen freien Tag, Bsp: 0001001. Das linke Zeichen steht für den Montag, das zweite für den Dienstag, ..., das siebente für den Sonntag. In dem Beispiel bedeutet die Zeichenfolge, das am Donnerstag und Sonntag ein freier Tag ist, alle anderen Tage sind Arbeitstage.
Wird die Zeichenfolge nicht angegeben, werden der Samstag und Sonntag automatisch als Wochenendtage berücksichtigt.
Hat die Zeichenfolge nicht genau sieben Zeichen oder andere Zeichen als 0 und 1, gibt die Funktion den Fehlerwert #WERT! zurück.

Freie_Tage (optional). Freie_Tage ist der Bereich einer Liste mit Datumswerten für freie Tage.

Wichtig: Die Funktion benötigt im Modulkopf die Option: Option Base 1

Beispiel:

A1 Ausgangsdatum: 01.01.2011
A2 Enddatum: 10.01.2011
A3 Zeichenfolge: '0001001 --> das Apostroph sorgt dafür, das die führenden Nullen erhalten bleiben
B1 Datum in Liste der freien Tage: 05.01.2011
B2 Datum in Liste der freien Tage: 06.01.2011
Formel: =NETTOERBEITSTAGE_INTL(A1;A2;A3;$B$1:$B$2)
Ergebnis: 6

Zuletzt aktualisiert am Dienstag, den 23. August 2011 um 11:11 Uhr
 

Heute

Mittwoch, 22. November 2017

Video - Management-Reports

 

excel2013report

 

Video - Bedingte Formatierung


excel2010v2bbf


Buch - Bedingte Formatierung

ExcelBedingteFormatierung

Office2010-Blog


Office2010-Blog Logo

Hinweis

Fast alle Hardware- und Software- bezeichnungen, die auf dieser Web- site erwähnt werden, sind gleichzeitig eingetragene Warenzeichen und sollten als solche behandelt werden.

Video2Brain-Autor

v2bautor