Posts Tagged ‘Excel’

Excel-Funktionen in Excel-Visual-Basic nutzen

14.06.2017

am Beispiel sverweis = vlookup

Zunächst legen wir ein paar Daten in unserem Excel-Blatt an. Ich benutze hier die Zellen wie folgt:
A1 soll das Ergebnis enthalten
B1 enthält den Suchwert

C in dieser Spalte will ich nach meinem Wert aus B1 suchen
D diese Spalte enthält das Ergebnis, das meine Prozedur in A1 ausgeben soll

Nun kann ich es mir leicht machen und die Funktion sverweis (im deutschen Excel) in Feld A1 schreiben:
=SVERWEIS(B1;C:D;2;FALSCH)

Eine Funktion hat also immer das Format =FUNKTIONSNAME(). In Klammern werden diverse Parameter übergeben und mit Semikolon getrennt. Nachdem man Enter betätigt hat wird die Funktion automatisch ausgeführt und sie liefert einen Wert zurück und schickt ihn genau in das Feld, in dem die Funktion steht.

In unserem Beispiel nimmt sich also unsere Funktion sverweis den Wert 123 aus Feld B1 und macht sich in Spalte C auf die Suche danach. Gleich in der 1. Zeile wird sie fündig, geht in die 2. Spalte des angegebenen Bereichs, also in Spalte D, und liefert den Wert 1 zurück. Hiermit ist die Funktion beendet.

Dass es einen zweiten Fundort in Zelle C3 gibt und somit auch eine zweite Lösung 3 in Zelle D3 interessiert sverweis nicht. Es ist also so, als hätten wir unsere Funktion losgeschickt, im Telefonbuch nach Müller zu suchen und beim ersten Müller zeigt sie die Rufnummer dieses Müllers an und macht Schluß. Weitere Müllers sind mit dieser Funktion nicht erreichbar. Aber das soll uns jetzt nicht weiter stören.

Wir wollen nun diese Funktion in Visual Basic einsetzen. In dieser Umgebung nennt man diese Funktionen „Methoden“. Wir betreten hier also den objektorientierten Bereich und somit wird leider auch eine neue Sprache und eine andere Sichtweise auf die Dinge angewendet. Das gestaltet die ganze Sache ein wenig fremd und schwierig und schließt viele Menschen davon aus, selber zu programmieren.

Mit Alt-F11 starten wir die Visual Basic Umgebung unter Excel. Wir fügen ein neues Modul hinzu (Einfügen > Modul) und fügen dann eine Prozedur hinzu (Einfügen > Prozedur). Wir müssen uns einen Namen ausdenken, der noch nicht vergeben ist – test wäre eine Möglichkeit. Den Typen belassen wir auf Sub und den Gültigkeitsbereich auf Public > OK.

Excel erstellt ein Grundgerüst für unser Programm:

Public Sub test()
End Sub

Unser Programm soll nun nur aus einer einzigen Zeile bestehen. Diese ist allerdings bereits kompliziert genug.

Range("A1") = Application.WorksheetFunction.VLookup(Range("B1"), Range("C:D"), 2, False)

Die Methode Application.WorksheetFunction.VLookup muß mit dem gesamten Pfad angegeben werden.

Wenn wir diese Zeile in Visual Basic hineinkopieren, müssen wir das Programm mit der Play-Taste (grüner, nach rechts zeigender Pfeil oben) starten.

Der erste Unterschied zwischen dieser Zeile und der gewohnten Funktion besteht darin, dass in Visual Basic immer englische Befehle gelten (hier vlookup), während Funktionen in unserem Excel-Arbeitsblatt immer in der Sprache angenommen werden, mit der Excel läuft (hier sverweis). Eine Übersetzungstabelle für Excel-Befehle findet man hier.

Die Parameter der Funktion werden mit Komma getrennt und nicht mit Semikolon.

Wenn ich einen Tabellenbereich mit Range angeben will, kann ich mich auf das aktuelle Tabellenblatt beziehen wie in Range(„B1“). Ich könnte mich auch ausdrücklich auf Tabelle1.Range(„B1“) beziehen. Da ich hier nur mit einem Blatt arbeite, ist es unwichtig Tabelle1 zu erwähnen, aber durchaus möglich.

Wozu muß man aber Range(„“) um das Feld A1 schreiben? In Visual Basic würde A1 ansonsten einfach als irgendeine neue Variable angelegt werden und mit dem Ergebnis des Terms rechts vom Gleichheitszeichen befüllt werden. Eine Ausgabe würde nicht stattfinden. Visual Basic kennt zunächst keine Tabellenfelder. Wir müssen es ausdrücklich sagen.

Ändern wir einmal
VLookup(Range("B1")
in
VLookup("B1")

Range("A1") = Application.WorksheetFunction.VLookup("B1", Range("C:D"), 2, False)

Nun sucht unsere Funktion nicht mehr nach dem was in der Zelle B1 steht, sondern nach dem Wert B1 in Spalte C, findet ihn in Zeile 2 und liefert den Wert 2 aus Zelle D2.

Wenn wir auch noch die „“ um B1 herum weglassen, erhalten wir eine Fehlermeldung, weil jetzt ein unbekannter Wert in einer undefinierten Variablen an die Funktion übergeben wird.

Range verlangt also nach einem String, der den Bereich (in unserem Fall nur eine einzelne Zelle) definiert. Range liefert wiederum den Inhalt der Zelle an vlookup.

Wir haben nun eine Prozedur erstellt, die wir manuell aufrufen müssen. Wir können diese Prozedur in eine Funktion umwandeln, indem wir in unserem Quellcode den Begriff Sub durch Function insgesamt zweimal ersetzen. Wenn wir Sub oben ersetzen, wird automatisch auch unten End Sub durch End Function ersetzt.

Nun möchten wir noch die Übergabe des Suchwertes an die Funktion realisieren. Die Übergabe erfolgt immer innerhalb der Klammern. Wir wollen später eine Funktion erhalten, die man wie folgt aufrufen kann:
=TEST(B1)
Die einleitenden Worte müssen also lauten:
Public Function test(Wert)
Schließlich soll die Ausgabe unseres Terms nicht direkt und absolut in eine Zelle erfolgen, sondern – wie bei sverweis – als Rückgabe der Funktion test. Also:

test = Application.WorksheetFunction.VLookup(Wert, Range("C:D"), 2, False)

Nun haben wir die Funktion sverweis über den Umweg der Programmierung in Visual Basic mit Hilfe der vorhandenen Methode vlookup ersetzt. Mit diesem Wissen kann man auch andere vorhandene Funktionen nutzen, um sie zu erweitern und für eigene Projekte einzusetzen.


Advertisements

Kalenderberechnung: n-ter Wochentag im Monat

18.09.2016

die Funktion WOCHENTAG (Datum) liefert Werte von 1 bis 7 mit folgender Bedeutung:
1 = So
2 = Mo
3 = Di
4 = Mi
5 = Do
6 = Fr
7 = Sa

wochentagdatum

Datum – WOCHENTAG (Datum + 0) = Sa vor Datum
Datum – WOCHENTAG (Datum + 1) = Fr vor Datum
Datum – WOCHENTAG (Datum + 2) = Do vor Datum
Datum – WOCHENTAG (Datum + 3) = Mi vor Datum
Datum – WOCHENTAG (Datum + 4) = Di vor Datum
Datum – WOCHENTAG (Datum + 5) = Mo vor Datum
Datum – WOCHENTAG (Datum + 6) = So vor Datum

z.B. letzter So im März (Beginn der Sommerzeit)
also der erste Sonntag rückwärts vom Ende des Monats gezählt
Datum = 01.04.xx = der erste Tag im Folgemonat
Formel:
= Datum – WOCHENTAG (Datum + 6)

z.B. erster Sonntag im Oktober (Erntedankfest)
also der erste Sonntag vorwärts vom Anfang des Monats gezählt
Datum = 30.09.xx = der letzte Tag im Vorgängermonat
Formel:
= Datum – WOCHENTAG (Datum) + 8

Verwendung finden diese Formeln z.B. zur Berechnung von:
Beginn der Sommerzeit => letzter Sonntag im März
Ende der Sommerzeit => letzter Sonntag im Oktober
Muttertag => 2. Sonntag im Mai
Erntedankfest => erster Sonntag im Oktober
Volkstrauertag => 2 Sonntage vor dem 1. Adventssonntag
Buß- und Bettag => 11 Tage vor dem 1. Adventssonntag bzw. am Mittwoch vor dem 23.11.
Totensonntag => Sonntag vor dem 1. Adventssonntag
1. Advent => 4. Sonntag vor dem 25.12.
2. Advent => 3. Sonntag vor dem 25.12.
3. Advent => 2. Sonntag vor dem 25.12.
4. Advent => Sonntag vor dem 25.12.


Excel-Funktion aggregat und ein Excel-Freak

16.09.2016

Wer viel mit der Excel-Funktion sverweis arbeitet, stößt schnell an deren Grenze. Nun gibt es die neue Funktion aggregat, die etwas mehr kann.

Andreas Thehos Excel Blog ist hier eine Fundgrube. Folgendes Video läßt staunen:

(Leider hat der Autor die Ansicht des Videos auf anderen Seiten als auf youtube gesperrt. Bitte das Video direkt auf youtube anschauen.)

LibreOffice 5 Calc enthält übrigens ebenfalls die Funktion aggregat.