Excel: Datumsfunktionen

Via Twittter wurde ich vor kurzem gefragt, wie man  in Excel die Anzahl Tage ohne die Wochenenden berechnen könne. Das ist in Excel mittels der Funktion NETTOARBEITSTAGE zu lösen. Diese Funktion ist nur eine der nicht wenigen Datumsfunktionen in Excel.

Nicht vielen ist bekannt, dass Microsoft eine (fast) komplette Funktionsreferenz ins Internet gestellt hat, natürlich auch zu den Datumsfunktionen.

NETTOARBEITSTAGE wird also wie folgt genutzt:

NETTOARBEITSTAGE(Ausgangsdatum; Enddatum; [Freie_Tage])

wobei Ausgangsdatum das Datum ist, von wo aus die Berechnung gestartet werden soll. Das Enddatum markiert das letzte Datum. Man kann zusätzliche freie Tage subtrahieren, wenn man will. NETTOARBEITSTAGE berücksichtigt von sich aus keine Feiertage (z. B. Ostern), sondern ausschliesslich die Wochenenden. Daher ist die Angabe der freien Tage  beispielsweise dann sinnvoll, wenn man die Ostertage als Wochenende mit berücksichtigen möchte. Aber leider muss man dies manuell tun. Die freien Tage sind keine obligatorische Angabe, weshalb diese auch in den eckigen Klammern [ ] angegeben sind. Man kann sie komplett weglassen.

Leider kann aber nun nicht einfach ein Datum als Ausgangsdatum eingegeben werden, wie wir es kennen. Excel rechnet nicht mit einem Datum wie 12.06.2011, sondern dieses Datum würde in Excel als 40706 wiedergegeben. Das hängt damit zusammen, das für Excel ein Datum eine fortlaufende Nummer ist. 01.01.1900 ist die Zahl 1. Damit man die NETTOARBEITSTAGE trotzdem verwendet werden kann, muss zusätzlich die Funktion DATUM verwendet werden. DATUM hat den folgenden Aufbau:

DATUM(Jahr; Monat; Tag)

Damit sieht für den Monat Mai 2011 die Formel so aus:

=NETTOARBEITSTAGE(DATUM(2011;5;11);DATUM(2011;6;1))

und als Ergebnis 16, denn der Monat Mai hat genau 16 Arbeitstage. Möchten wir für den April mit insgesamt 15 Arbeitstagen noch Ostern berücksichtigen, müssen wir diese als freie Tage manuell mit eingeben. Der 22. April war ein Karfreitag und der 25. April der Ostermontag, beide Tage gelten in der Schweiz als Feiertage. Damit muss ich also 2 Tage subtrahieren. Nun ist es aber so, dass nur ein Argument, sozusagen ein Platzhalter vorgesehen ist. Man kann also nicht einfach weitere Daten einfügen, aber man kann in mehreren Zellen die Angaben machen. Das sieht wie folgt aus, wenn in A4 und A5 diese beiden Daten angegeben sind:

=NETTOARBEITSTAGE(DATUM(2011;4;11);DATUM(2011;5;1);A4:A5)

Das ergibt 15 – 2 = 13 Arbeitstage.

Dem aufmerksamen Leser wird aufgefallen sein, dass die erwähnte Funktionsreferenz leider nicht komplett ist. Darauf werde ich im nächsten Artikel eingehen.

Veröffentlicht unter Excel | Verschlagwortet mit

Excel-Reihe III

Ja, ja, ich weiss: Ich habe geschrieben, dass in Excel-Sheets die Nutzung der Maus verboten keine gute Idee sei. Stimmt auch. Aber keine Regel ohne Ausnahme.

Man kann nämlich mit der Maus sehr elegant Daten verschieben. Das ist aber nur dann sinnvoll, wenn es sich um einen Datenblock handelt und dieser nicht weit vom Ursprung bewegt werden muss. Seht doch selbst:

Die Daten „Chicken Chick? Chick!“ sind eine Anlehnung an den von Doug Zongker referierten Witz, den er an der AAAS Humor Session vorgetragen hat. Es gibt sogar ein Paper (PDF) dazu.

Excel-Reihe II

Wenn ich Leute sehe, die mit der Maus durch riesige Excel-Tabellen rumkuven und dabei eine ganze Minute haben, nur damit sie von der untersten Zeile zur ersten gelangt sind, macht mich das wahnsinnig. Viele Leute wissen leider nicht, dass man Excel unbedingt mit der Tastatur bedienen sollte. Das macht das Handling mit Excel viel einfacher.

Die wichtigsten Tasten für die Fortbewegung in einer Tabelle sind schnell erklärt. Von einer Zelle zur anderen bewegen kann man sich mit den Pfeiltasten. Also eine Zelle nach oben, links, rechts oder unten. Häufig will man Zellen auch markieren. Kein Problem: [Shift]+[Pfeiltaste] – und schon sind Zellen markiert. Mit [CTRL]+[Pfeiltaste] kann man von Datensätze zu Datensätze hüpfen.  Probiert das mal aus! Ihr werdet sehen, dann werdet Ihr von der Zeile 64’000 nie mehr mit der Maus nach oben fahren wollen, denn dann springt ihr automatisch zum Ende eines Datensatzes. Ihr könnt auch zwischen Datensätzen hüpfen. Wenn Ihr zwischen zwei Datensätzen leere Zellen habt, dann überspringt ihr automatisch die leeren und kommt zur ersten an, die nicht leer ist. Wenn es keine leeren Zellen gibt, dann kommt ihr eben zur Tabellenende.

Und jetzt bleibt nur noch die Kombination: [CTRL]+[Shift]+[Pfeiltaste]. Vermutlich hab Ihr schon erraten, wofür das ist… und jetzt ausprobieren! Und damit ihr mit den markierten Zellen auch gleich was anfangen könnt – [CTRL]+[C]  fürs Kopieren bzw. [CTRL]+[X] fürs Ausschneiden und [CTRL]+[V] fürs Einfügen funktioniert wie auf der ganzen Windows-Plattform auch in Excel.

Kurzübersicht als Spickzettel:

  • [Shift]+[Pfeiltaste] – einzelne Zellen markieren
  • [CTRL]+[Pfeiltaste] – An die äussersten Zellen von Datenblöcken springen
  • [CTRL]+[Shift]+[Pfeiltaste] – beides zusammen
  • [CTRL]+[C] – kopieren
  • [CTRL]+[X] – ausschneiden
  • [CTRL]+[V] – einfügen

Manipulation von Dateinamen via Excel

Ja, jetzt gibts mal ein bisschen Code. Ich bin kein guter Programmierer, bestenfalls ein Amateur, auch wenn ich im Büro hin und wieder Skripte in VBA schreibe und da kann man sicherlich einiges besser machen. Wer Kritik hat… nur zu! Ich lerne gerne 🙂

Eines dieser Skripte aus dem Büro möchte ich Euch nicht vorenthalten, denn dieses ist sehr universell nutzbar.

Ich war vor die Aufgabe gestellt, Dateien in Excel umbenennen zu können – im Batch, denn wir hatten mehrere hundert Dateien umzubenennen. Das Tabellenkalkulationsprogramm so zu verwenden scheint zwar auf den erstmal etwas abgwegig.  Tatsächlich hat Excel aber sehr mächtige Bordmittel, womit sich Texte in Zellen sehr einfach manipulieren lassen. So kann man mit dem Befehl =Verketten(A1;B1) zwei Zellen verketten. Daraus lässt sich als Beispiel eine fortlaufende Nummer vor den Namen heranhängen und die Nummern dazu kann man einfach laufend herunterkopieren. Auf weitere Funktionen wie =Teil() oder gar =SVERWEIS() will ich hier nicht eingehen, das würde den Rahmen für diesen Artikel sprengen.

Doch nun zurück zum Skript – hier ist es:


Option Explicit

Sub GetFiles()

 Dim wBook As Workbook
 Dim wSheet As Variant
 Dim strPath As String
 Dim strLoadfile As String
 Dim intWrite As Integer

 Set wBook = ThisWorkbook
 Set wSheet = wBook.Worksheets
 Set wSheet = ActiveSheet

 strPath = wSheet.Cells(1, 1)
 wSheet.Columns("A:A").ClearContents
 wSheet.Cells(1, 1) = strPath

 If Right(strPath, 1) <> "\" Then
   strPath = strPath & "\"
 End If

 intWrite = 3
 strLoadfile = Dir(strPath)

 If strLoadfile = "" Then
   MsgBox ("No File found or invalid path.")
   Exit Sub
 Else
   Do While strLoadfile <> ""
     wSheet.Cells(intWrite, 1) = strLoadfile
     intWrite = intWrite + 1
     strLoadfile = Dir
   Loop
 End If

 intWrite = 3
 strLoadfile = Dir(strPath)

 If strLoadfile = "" Then
   MsgBox ("No File found or invalid path.")
   Exit Sub
 Else
   Do While Range("B" & intWrite) <> ""
   Name strPath & Range("A" & intWrite) As strPath & Range("B" & intWrite).Value
   intWrite = intWrite + 1
   Loop
 End If

 MsgBox ("Renaming process completed.")
End Sub

Das Skript macht folgendes: Man legt in A1 den Pfadnamen des Ordners fest, in denen sich alle Dateien befinden, deren Namen man ändern will. Ein Button wird ins Excel-File gelegt, das mit


Private Sub CommandButton1_Click()

 Call GetFiles

End Sub

den oben stehenden Code aufruft und ausführt.

Das Skript wird dann den in A1 angegebenen Pfad auslesen und in die Spalte A schreiben. Als nächstes kann man in die Spalte B gleich daneben den gewünschten Namen hinschreiben. Soll eine Datei nicht umbenannt werden, kann der Name einfach von der Spalte A übernommen werden. So bald eine Zelle leer ist, bricht die Prozedur ab. Das Skript funktioniert nicht im Netzwerk, sondern nur lokal.

Excel-Reihe I

Ich starte hiermit die Excel-Reihe, in der ich Funktionen von Microsoft Excel vorstellen will. Bekanntlich nutzen die meisten User nur einen kleineren Bruchteil aller Funktionen dieses Programms. Ich kenne wahrscheinlich auch nicht alle, aber vermutlich die meisten. Ich will ab heute hin und wieder Funktionen, eigene Erlebnisse und Überraschungen verbloggen.

Thema heute: Was ist MS Excel?

Sehr vielen Leuten ist gar nicht bewusst, was Excel eigentlich ist. Viele sagen zu mir, damit könne man Tabellen machen und Tabellen verarbeiten. Andere meinen, damit könne man Datenbanken verwalten.

Beide Behauptungen sind natürlich nicht ganz falsch, aber sie treffen die Grundidee dieser Software nicht. Excel ist ein so genanntes Tabellenkalkulationsprogramm. Natürlich kann man damit auch schöne Tabellen erstellen und natürlich kann man damit ansatzweise eine Datenbank erstellen. Aber dafür ist Excel nicht in erster Linie entwickelt worden. Was muss man denn unter einem Tabellenkalkulationsprogramm verstehen?

MS Excel kann Daten, die in einer Tabelle abgelegt sind, berechnen, analysieren und diese schön präsentieren. Gerade letzteres ist ein Kriterium, das Excel von vielen Datenbanksoftware unterscheidet. MS Access zum Beispiel kann ebenfalls deskriptive Grafiken erzeugen, aber es ist vergleichsweise nicht so gut darin, die Daten mathematisch-analytisch zu verarbeiten und diese einfach anderen Anwendungen zur Verfügung zu stellen, damit man diese Daten in einer Präsentation darstellen kann. Da nimmt man normalerweise den Umweg zu Excel. Access hat dafür andere stärken, die im Datenhandling zu finden sind.

MS Excel ist im Vergleich zuseiner Konkurrenz wie zum Beispiel Open Calc extrem mächtig. Excel hat eindeutig die Nase vorn, wenn es um die Funktionalität geht. MS Excel 2007 kann beispielsweise bis zu 1 Mio. Zeilen und 16’000 Spalten umgehen. In der Praxis benötigen das zwar nur wenige Leute und diese Limiten sind oftmals ohnehin durch den begrenzten Arbeitsspeicher gar nicht auszunutzen. Excel kennt aber auch viel mehr Formeln als Open Office.  Für einen tiefergehenden Vergleich mit der Konkurrenz verweise ich zur englischen Wikipedia.

Ich schliesse damit diese Einleitung einmal ab – Fragen sind immer willkommen, auch Anregungen zu einem Thema rund um Excel.