EXCEL:Vertiefung
Aus XIMES
Ralf Nebelo
Formeln für Excelperten
Auf die lange Datenbank
Excel-Anwender fassen zusammengehörige Informationen wie Adress- oder Artikellisten meist in einer einfachen Tabelle zusammen. Sinnvoller ist es jedoch, sie in einer sogenannten Datenbank zu speichern. Dabei handelt es sich um ein separates Arbeitsblatt innerhalb der Mappe, das eine Überschriftenzeile mit den Feldnamen trägt. Jede Zeile darunter nimmt dann jeweils einen Datensatz auf, dessen Felder sich über die Spalten einer Zeile verteilen. Um möglichst komfortabel auf die gespeicherten Informationen zugreifen zu können, vergibt man ihr gewöhnlich einen aussagekräftigen Namen wie "Adressdaten" oder "Rechnungseingang". Das funktioniert prima, solange sich die Datenbankgröße nicht ändert. Kommen jedoch neue Datensätze oder -felder hinzu, muss man den benannten Datenbankbereich immer manuell erweitern.
Die Beispieldatenbank erhält einen dynamischen Namen, der sich automatisch anpasst, wenn neue Datensätze hinzukommen.
Namen lassen sich auch mit Hilfe einer Formel zuweisen, die den benannten Bereich dynamisch und damit vollautomatisch ans reale Datenaufkommen anpasst. Ein solches Kunststück lässt sich am besten anhand einer Datenbank nachvollziehen, die beispielsweise in einem Arbeitsblatt namens DBKunden gespeichert ist. Um diese Datenbank mit einem dynamischen Bereichsnamen zu versehen, wählt man den Befehl "Einfügen/Namen/Definieren", gibt unter "Namen in der Arbeitsmappe" etwa "Datenbank" und unter "Bezieht sich auf" die folgende Formel ein:
=BEREICH.VERSCHIEBEN(DBKunden!$A$1;;;ANZAHL2(DBKunden!$A:$A);ANZAHL2(DBKunden!$1:$1))
Diese Formel ermittelt die aktuelle Größe der Datenbank, indem sie die Anzahl der genutzten Zellen in der ersten Zeile ($A:$A) respektive ersten Spalte ($1:$1) des Blatts DBKunden zählt. Die Funktion BEREICH.VERSCHIEBEN erstellt dann einen Verweis auf diesen Bereich, der sich anschließend unter dem Namen "Datenbank" ansprechen lässt. Weil das bei jeder Neuberechnung geschieht, entspricht der Name stets dem tatsächlichen Datenvolumen.
Wer sich davon überzeugen will, gibt in irgendein anderes Blatt der Arbeitsmappe die Formel
=ZEILEN(Datenbank)-1
ein und vergleicht das Ergebnis mit der Anzahl der Datensätze. Der Abzug von 1 in der verwendeten Formel dient übrigens der Ausklammerung der Überschriftenzeile, die ja Teil des Bereichs "Datenbank" ist. Das Ermitteln der Spalten- respektive Datenfeldzahl mit
=SPALTEN(Datenbank)
erfordert dagegen keinen Abzug.
Vollautomat
Die Existenz einer Datenbank hat den großen Vorteil, dass man beispielsweise beim Gestalten eines Rechnungsformulars auf die manuelle Eingabe des Firmennamens verzichten und stattdessen eine Auswahlliste verwenden kann, die sich ihre Daten automatisch aus der Datenbank beschafft, wobei sie automatisch deren aktuelle Länge berücksichtigt. Um das zu realisieren, markiert man die Zelle, in der die Auswahl erfolgen soll, wählt "Daten/Gültigkeit", stellt das Listenfeld auf "Liste" ein und tippt folgende Formel ins Textfeld "Quelle" ein:
=BEREICH.VERSCHIEBEN( Datenbank;1;0;ZEILEN(Datenbank)-1;1)
Die Formel erstellt einen dynamischen Verweis, der gegenüber dem Bereich "Datenbank" um eine Zeile verschoben ist und mit Ausnahme der Überschriftenzeile alle Zeilen, aber nur eine Spalte umfasst. Im Ergebnis verweist die Formel also auf sämtliche Firmennamen der ersten Datenbankspalte und stellt sie dem Anwender per Auswahlliste bereit.
Über einen dynamischen Verweis, der auf eine Spalte der Datenbank zeigt, lassen sich Auswahllisten wie hier mit Firmennamen erstellen.
Nachdem nun schon der Firmenname per Mausklick ins Rechnungsformular befördert wurde, sollte man die zugehörigen Datenfeldinhalte wie Name der Kontaktperson, Adresse, Postleitzahl und Ort ebenfalls nicht mehr eintippen müssen - ein Fall für SVERWEIS. Die Funktion sucht einen Zellwert, der sich stets in der ersten (linken) Spalte des anzugebenden Bereichs befinden muss. Dann liefert sie einen Wert aus der gleichen Zeile zurück, dessen Spaltennummer der Anwender im dritten Funktionsargument bestimmt. Das letzte Argument schließlich muss paradoxerweise den Wert FALSCH aufweisen, damit der Treffer exakt dem Suchbegriff entspricht. Die Formel
=SVERWEIS(A1;Datenbank;3;FALSCH)
durchsucht die Datenbank nach dem Firmennamen, den der Anwender zuvor per Auswahlliste im Feld A1 festgelegt hat, und holt sich den Namen der zugehörigen Kontaktperson aus der dritten Spalte der Fundzeile. Auf diese Art ließen sich auch alle übrigen Datensatzinfos für ein vollständig automatisiertes Rechnungsformular beschaffen.
Innenrevision
Befindet sich der Suchwert zum Bestimmen eines Datensatzes nicht in der ersten, sondern in irgendeiner anderen Spalte, springt DBAUSZUG in die Bresche. Die Funktion liefert den Inhalt des Datenfelds, dessen Name ein Text oder entsprechender Bezug im zweiten Argument festlegt. Zum Bestimmen des richtigen Datensatzes benötigt DBAUSZUG einen Bezug auf zwei übereinanderliegende Zellen, von denen die obere den Namen des zu durchsuchenden Datenfelds, die untere den gesuchten Inhalt enthält. Steht in A1 das Wort "Firma" und darunter in A2 ein Firmenname, so liefert
=DBAUSZUG(Datenbank;"Kontaktperson";A1:A2)
den Namen der zugehörigen Kontaktperson. Im Unterschied zu DBAUSZUG funktioniert die Suche mit SVERWEIS auch außerhalb ausgewiesener Datenbankbereiche.
Beim Auswerten einer Datenbank gilt das Interesse häufig der Frage, wie viele Datensätze einen bestimmten Suchwert, beispielsweise eine Bestellnummer, enthalten. Die Funktion DBANZAHL2 liefert die Antwort, indem man ihr im dritten Argument wieder einen Bezug auf ein Zellenpaar liefert, das den Namen des zu durchsuchenden Datenfelds (obere Zelle) und den gesuchten Begriff (untere Zelle) enthält. Das zweite Argument benennt das Datenfeld, dessen Werte die Funktion zählen soll. Falls A1 den Feldnamen "Ort" und A2 den Text "Hausen" enthält, würde die Formel
=DBANZAHL2(Datenbank;"Firma";A1:A2)
die Anzahl der Firmen nennen, die in Hausen residieren.
Mit Hilfe eines Kriterienbereichs wie hier in den ersten beiden Zeilen kann man Abfragen kombinieren.
Möchte man hingegen wissen, wie viele Firmen es in Wunsen mit einem Bestellumsatz von weniger als 5000 Euro gibt, kann man dies einfach mit einem Kriterienbereich erledigen, der in der ersten Zeile die Spaltenüberschriften und in der zweiten die Kriterien enthält, also in der Spalte für den Ort "Wunsen" und als Umsatz "<5000". Stehen diese Angaben in den ersten beiden Zeilen und die Datenbanktabelle ab der fünften Zeile, lautet die Formel:
=DBANZAHL(A5:G16,"Umsatz";A1:B2)
Mit Format
Mit Formelhilfe lässt sich auch die bedingte Formatierung kräftig aufmotzen, sodass dieses Hilfsmittel wesentlich mehr drauf hat als das Rotfärben negativer Zahlen. Zum Beispiel kann man jede zweite Zeile farbig hinterlegen, um die Lesbarkeit großer Tabellen zu verbessern. Statt ein VBA-Makro mit dieser Arbeit zu betrauen, genügt das Markieren des gewünschten Tabellenbereichs und der Aufruf des Dialogs "Bedingte Formatierung". Dort stellt man das Listenfeld unter "Bedingung 1" auf "Formel ist" ein und weist ihm folgende Formel zu.
=REST(ZEILE(A1);2)=0
Wenn Sie nicht die ganze Tabelle markiert haben, setzen Sie in der Formel statt A1 die linke obere Ecke des markierten Bereichs ein. Die Formel prüft, wann sich die aktuelle Zeilennummer ohne Rest durch 2 teilen lässt, und wendet die zugewiesene Formatierung nur dort an.
Mit Formelhilfe ist die Kennzeichnung von Dubletten, Wochenenden, ausgewählten Zellinhalten sowie größten oder kleinsten Werten kein Problem.
In vielen Fällen interessieren sich Leser einer Excel-Tabelle nur für den größten Wert, der sich mit Hilfe einer Formel unmittelbar optisch hervorheben lässt. Dazu markiert man den Tabellenbereich, beispielsweise A1:D10, und weist ihm über den Dialog "Bedingte Formatierung" das gewünschte Format, etwa rote Schriftfarbe, sowie die folgende Formel zu:
=MAX($A$1:$D$10)
Excel durchläuft dann alle Zellen des Bereichs und überprüft jeweils, ob deren Wert der größte ist. Der Tabellenbereich muss absolut adressiert sein, damit er beim Durchlaufen aller Zellen unverändert bleibt, beispielsweise $A$1:$D$10.
Beim Konsolidieren von Tabellendaten spielt das Eliminieren von unerwünschten Dubletten eine große Rolle. Sie lassen sich mit Hilfe der bedingten Formatierung umgehend aufspüren. Die Formel dafür lautet:
=ZÄHLENWENN($A$1:$D$10;A1)>1
Excel durchläuft dann alle Zellen des Bereichs A1:D10 und zählt, wie oft der jeweilige Zellwert darin vorkommt. Liegt das Ergebnis über 1, versieht Excel die Zelle mit der ausgewählten Formatierung.
Das Kennzeichnen von Wochenenden in selbstgebauten Excel-Kalendern ist ebenfalls kein Problem, wenn man den entscheidenden Kniff kennt. Dazu benötigt man einen Bereich mit Zellwerten, die als Datum formatiert sind. Diesem Bereich weist man dann über die bedingte Formatierung das gewünschte Format und die folgende Formel zu:
=WOCHENTAG(A1)=7
Diese identifiziert jeden siebten Tag der Woche, welcher in der Excel-Zeitrechnung ein Samstag ist. Mit dem Sonntag dagegen beginnt die Excel-Woche, weshalb die passende Formel hier lauten würde:
=WOCHENTAG(A1)=1
Alles nur geklaut
Bei Excel 2007 hat die bedingte Formatierung einen großen Leistungsschub erfahren. Sie enthält nun unter anderem ein ebenso sehenswertes wie nützliches Feature, mit dem man den (relativen) Wert einer Zelle in deren Hintergrund als farbigen Balken visualisieren kann. Ein ähnlicher Effekt lässt sich aber auch in den früheren Excel-Versionen 2000, 2002 und 2003 nachbilden. Die Balkendarstellung erfolgt hier allerdings in einer Nachbarzelle, was der Nützlichkeit aber keinen Abbruch tut. Möchte man beispielsweise die Werte der zehn Zahlen A1 bis A10 jeweils mit einem Balkendiagramm in der Nachbarzelle ausstatten, weist man der Zelle B1 die Formel
=WIEDERHOLEN("I";A1/MAX($A$1:$A$10)*100)
zu und kopiert sie per Füllkästchen (siehe Kasten "Handwerkszeug") auf die neun Zellen darunter. Die Formel errechnet zunächst den prozentualen Wert der Zelle in A, indem sie deren Wert durch den größten der zehn Werte teilt und mit 100 multipliziert. Das Ergebnis dieser Operation setzt die Textfunktion WIEDERHOLEN in entsprechend viele Wiederholungen des Buchstabens "I" um. Durch Auswahl anderer Wiederholungszeichen aus Standard- oder Symbolschriftarten lässt sich die optische Wirkung des Effekts beliebig variieren. Nach Zuweisen einer bedingten Formatierung können die Pseudo-Balken auch farbig gestaltet werden, um etwa den größten oder kleinsten Wert hervorzuheben.
Excel-2007-Spezialitäten wie Datenbalken und Symbolsätze lassen sich auch mit den Bordmitteln früherer Excel-Versionen nachbilden.
Neben Datenbalken lassen sich auch die Symbolsätze von Excel 2007 nachbilden. Diese statten Zellwerte unter anderem mit Ampellichtern oder Pfeilsymbolen aus, die eine schnelle optische Zuordnung der jeweiligen Zahl zu einem bestimmten Wertigkeitsbereich wie hoch, mittel oder tief erlauben. Um die Zahlen A1 bis A10 aus dem letzten Beispiel mit vergleichbaren Symbolen zu versehen, weist man den Nachbarzellen in der B-Spalte zunächst die Formel
=WAHL(RUNDEN(RANG(A1;$A$1:$A$10)/ANZAHL($A$1:$A$10)*2+1;0);"ö";"[DELTA]´";"ø")
und anschließend die Schriftart Wingdings zu. Aus dieser Symbolschriftart stammen nämlich die drei Pfeilsymbole, die innerhalb der auf Standardschrift beschränkten Formel als "ö", "[DELTA]´" und "ø" erscheinen. Das erste ist ein gewöhnliches ö, die anderen beiden erhält man durch Eingabe der Tastencodes 0240 und 0248 bei gedrückter Alt-Taste auf dem Ziffernblock. Die verschachtelte Formel ermittelt jeweils die Rangfolge des benachbarten Zellwerts innerhalb der zehn Werte, teilt das Ergebnis durch die Anzahl aller Werte, multipliziert es mit 2 und addiert 1 hinzu. Nach dem Runden ergibt sich ein Wert zwischen 1 und 3, der angibt, zu welcher der drei Werteklassen die Zahl gehört. Die WAHL-Funktion muss dann abhängig von dieser Werteklasse nur noch das richtige Symbolzeichen ausgeben. Tipp im Tipp: Bei Auswahl alternativer Symbole leistet die Windows-Zeichentabelle unschätzbare Dienste.
Tagesgeschäft
Um in Excel mit Kalenderdaten zu jonglieren, sollte man die spezielle Zeitrechnung von Microsoft kennen, die am 1. 1. 1900 beginnt. Jedes Datum danach verwaltet die Tabellenkalkulation intern als ganze Zahl, deren Wert der Anzahl der seitdem vergangenen Tage entspricht. Das Datum 15. 04. 1912 - der Tag, an dem die Titanic sank - steht damit für den Wert 4489, wovon sich jeder überzeugen kann, der das betreffende Datum probehalber als "Standard" formatiert.
Wegen dieser linearen Tageszählung lassen sich drängende Fragen wie "Wie lange bin ich auf der Welt?" oder "Wie viele Tage bleiben noch bis Weihnachten?" mittels einer simplen Subtraktion nach dem Muster
=B1-A1
beantworten, wobei hier das ältere Datum in A1 und das jüngere in B1 steht. Wenn man das Ergebnis als "Standard" oder "Zahl" formatiert, erhält man die Differenz in Tagen, die zwischen den beiden Kalenderdaten liegt - so weit, so einfach.
Kompliziert wird es bei Datumswerten, die vor dem 1. 1. 1900 liegen. Mit ihnen kann Excel nicht rechnen und interpretiert sie daher als Text. Um das Problem zu lösen, addiert man einfach 400 Jahre hinzu, wonach aus dem Text wieder ein gültiges Excel-Datum wird. Wenn man dies bei beiden Datumsangaben macht, ändert sich an der Differenz nichts. Die Formel
=(LINKS(B1;6)&TEIL(B1;7;4)+400)-(LINKS(A1;6)&TEIL(A1;7;4)+400)
demonstriert das Verfahren, wobei sie von zwei Datumswerten in A1 und B1 mit zweistellig formatierten Tages- und Monatszahlen sowie einer vierstelligen Jahreszahl ausgeht. Bei beiden Werten isoliert die Formel zunächst die Jahreszahl (Funktion TEIL), addiert dieser 400 hinzu und stellt dem Ergebnis den Tages- und Monatsanteil des ursprünglichen Datums voraus (Funktion LINKS). Damit die Formel funktioniert, müssen Sie Daten ab dem 1. 1. 1900 als Text formatieren; am einfachsten, indem Sie ihnen ein einfaches Anführungszeichen voranstellen.
Trick 17,5
Zum Berechnen der Kalenderwoche taugt die gleichnamige Excel-Funktion überhaupt nicht, da sie die hierzulande verbindliche Definition missachtet, wonach die erste Kalenderwoche die Woche ist, in die der 4. Januar fällt. Exakte Ergebnisse erzielt man dagegen mit folgender Formel, welche die Kalenderwoche für das Datum aus A1 berechnet:
=KÜRZEN((A1-WOCHENTAG(A1;2)-DATUM(JAHR(A1+4-WOCHENTAG(A1;2));1;-10))/7)
Viel einfacher zu durchschauen ist die Arbeitsweise der folgenden Formel, mit der sich das Quartal des in A1 gespeicherten Datums kalkulieren lässt:
=AUFRUNDEN(MONAT(A1)/3;0)
Die Formel teilt den Monat des Datums durch 3 und rundet das Ergebnis auf. Möchte man die Anzahl Tage eines Monats berechnen, in den das Datum in A1 fällt, kann man sich eines Tricks bedienen. Den beschreibt die folgende Formel, die den ersten Tag des Monats vom Ersten des Nachfolgemonats (MONAT(A1)+1) abzieht.
=DATUM(JAHR(A1);MONAT(A1)+1;1)-DATUM(JAHR(A1);MONAT(A1);1)
Kommt dabei 29 heraus, weiß man, dass das Datum in A1 in ein Schaltjahr fällt. Enthält A1 eine Jahreszahl, lässt sich die Frage "Schaltjahr oder nicht?" auch direkt beantworten:
=WENN(DATUM(A1;3;1)-DATUM(A1;2;1)+1=29;WAHR;FALSCH)
Übrigens hält Excel das Jahr 1900 fälschlicherweise für ein Schaltjahr; Vorsicht also bei Datumsberechnungen in diesem Zeitraum.
Planwirtschaft
Für Arbeits-, Projekt- und Urlaubsplanungen sind Feiertage eine wichtige Größe. Dummerweise sind viele davon nicht so leicht kalkulierbar wie das feststehende Weihnachtsfest, sondern fallen Jahr für Jahr auf ein variables Datum, das sich auf den Ostersonntag bezieht. Dank des Mathematikers Carl Friedrich Gauß lässt sich dieser exakt berechnen. Bei einem Wettbewerb im Internet wurde vor einigen Jahren die kürzestmögliche Excel-Umsetzung des Gauß'schen Algorithmus ermittelt, die wir leicht verbessert haben:
=DM((TAG(MINUTE(A1/38)/2+55)&".4."&A1)/7;)*7-WENN(JAHR(1)=1904;5;6)
Die Formel berechnet das Osterdatum für das in A1 angegebene Jahr. Die Verbesserung besteht in der angehängten WENN-Funktion, die auch bei einem auf 1904-Datumswerte eingestellten Excel noch korrekte Ergebnisse liefert.
Die mit Excel-Formeln kalkulierte Liste der Feiertage ist ein wesentliches Element für vollautomatische Arbeits-, Projekt- und Urlaubsplanungen.
Die Kenntnis des Osterdatums ermöglicht das problemlose Berechnen der abhängigen Feiertage, die sich durchweg durch eine feste Tagesdifferenz (siehe Tabelle "Bewegliche Feiertage") auszeichnen. Diesen Offset muss man nur noch zum Osterdatum addieren, schon erhält man das Datum des gewünschten Feiertags.
Beim evangelischen Buß- und Bettag, der nur noch in Sachsen ein Feiertag ist, wird der Termin durch einen variablen Abstand vom Weihnachtsdatum bestimmt. Mit der Angabe des Jahres in A1 lautet die Formel:
=DATUM(A1;12;25)-WOCHENTAG(DATUM(A1;12;25);2)-32
Bei den Adventssonntagen verhält es sich genauso, nur dass hier am Ende der Formel nicht 32 abgezogen wird, sondern 21 für den ersten, 14 für den zweiten, 7 für den dritten und im Fall des vierten Advents gar nichts.
Nimmt man die festen Feiertage hinzu, ergibt sich eine vollständige, aber regional anzupassende Liste der arbeitsfreien Tage, wie sie das Bild links zeigt. Mit Hilfe dieser Liste und der Analyse-Funktion NETTOARBEITSTAGE kann man beispielsweise exakt berechnen, wie viele Arbeitstage zwischen dem Starttermin eines Projekts und dessen Ende liegen. Dazu muss man der Funktion nur besagte Termine, im Beispiel A22 und A23, und den Bereich der Feiertagsliste (A2:A20) übergeben. Die Wochenenden zieht die Funktion automatisch ab. Die Formel lautet:
=NETTOARBEITSTAGE(A22;A23;A2:A20)
Bei der Projektplanung kennt man oft nur den Starttermin und die Anzahl der notwendigen Arbeitstage. Um daraus ein Enddatum zu berechnen, braucht es die Analyse-Funktion ARBEITSTAG, die ihrerseits mit den genannten Größen (in A22 und A23) und ebenfalls mit einem Bezug auf die Feiertagsliste versorgt werden will:
=ARBEITSTAG(A22;A23;A2:A20)
Die Funktion lässt sich auch in identischer Form für die Urlaubsplanung einsetzen. Dann muss man nie wieder im Kalender nachsehen, um die Frage zu beantworten, wann Arbeitnehmer X nach Y Tagen Urlaub wieder arbeitet.
Infotainment
In Sachen Informationsbeschaffung haben Excel-Formeln auch einiges zu bieten. So lässt sich die Funktion ZELLE, die viele nützliche Dinge über Inhalt, Form und sonstige Eigenschaften von Zellen verrät, auch zur Herausgabe des Pfadnamens der aktuellen Arbeitsmappe überreden:
=ZELLE("Dateiname")
Die Ausgabe erfolgt allerdings in einem besondern Format, bei dem der Dateiname in eckigen Klammern erscheint und als Anhängsel den Namen der aktuellen Tabelle hinter sich herschleppt. Wer einen für Dateioperationen gültigen Pfadnamen benötigt, sollte zunächst mit der Formel
=LINKS(A1;FINDEN("]";A1))
in A2 den Tabellennamen entfernen, wobei die LINKS-Funktion den linken Teil des Pfadnamens bis zu der von FINDEN gelieferten Fundstelle der schließenden eckigen Klammer isoliert. Im nächsten Schritt tauscht
=WECHSELN(WECHSELN(A2;"[";"");"]";"")
durch doppelten Einsatz der WECHSELN-Funktion die beiden Klammern gegen Leerstrings aus. Mit INFO lassen sich Excel Systeminformationen entlocken, etwa die wahre Bezeichnung der verwendeten Windows-Version:
=INFO("Sysversion")
Was die eigene Version betrifft, erteilt Excel mit
=INFO("Version")
Selbstauskunft. Weitere mögliche Parameter sind "VerfSpeich" für den freien sowie "GesamtSpeich" für den insgesamt vorhandenen Arbeitsspeicher.
Der zurückgelieferte Dateipfad enthält auch den Tabellennamen und Sonderzeichen, die man aber in zwei Zusatzschritten schnell entfernen kann.
Für das Umrechnen von Längen-, Gewichts-, Zeit- und anderen Maßeinheiten macht sich die Analyse-Funktion UMWANDELN nützlich, die neben einem Bezug auf die Zellen mit dem umzurechnenden Wert zwei Zeichenketten als Kennzeichen der Quell- und Zielmaßeinheit erwartet. Die Umrechnung von Grad Celsius in Grad Kelvin gelingt zum Beispiel wie folgt:
=UMWANDELN(A1;"C";"K")
Mit UMWANDELN sind eine Fülle weiterer praxisnaher Umrechnungen möglich, die in der Excel-Hilfe unter dem englischen Funktionsnamen CONVERT dokumentiert sind.
Tief ausgegraben
Wer in umfangreichen Arbeitsmappen mit vielen Tabellen öfter mal die Orientierung verliert, für den ist das scrollende Arbeitsblattregister mit seinem begrenzten Ausschnitt nicht unbedingt eine Hilfe. Wesentlich mehr Überblick böte ein Inhaltsverzeichnisblatt mit funktionierenden Hyperlinks zu sämtlichen Tabellen, was sich - man errät es fast - ganz ohne VBA allein mit Formel-Power realisieren lässt. Dazu benötigt man zunächst einen Namen namens "Tabellen", den man über "Einfügen/Namen/Definieren" erstellt. Als dessen Bezug gebe man die folgende Formel ein:
=ARBEITSMAPPE.ZUORDNEN(1+0*JETZT())
Dabei kommt eine Funktion aus den nahezu in Vergessenheit geratenen Excel-4-Makrofunktionen zum Einsatz, die ein Array mit sämtlichen Tabellennamen der aktuellen Arbeitsmappe zurückliefert. Der Zusatz der JETZT-Funktion sorgt für eine permanente Aktualisierung dieser Liste. Nach dem Anlegen eines neuen Tabellenblatts wähle man die Zelle A1, weise ihr die Formel
=INDEX(Tabellen;ZEILE(A1))
zu und kopiere diese via Füllkästchen über so viele Zeilen nach unten, wie die Arbeitsmappe Tabellen besitzt (es können auch ein paar mehr sein). Das Blatt zeigt dann in Spalte A sämtliche Blattnamen jeweils mit vorangestelltem Dateinamen in eckigen Klammern an. Mit dem Voranstellen eines Nummernkreuzes und dem Anhängen eines Zielvermerks auf die jeweils erste Tabellenzelle werden die Einträge in Spalte A zu gültigen Sprungadressen in Spalte B konvertiert. Dazu gibt man in B1 die Formel
="#'"&A1&"'!A1"
ein und kopiert diese wieder um die gleiche Zeilenanzahl wie zuvor nach unten. Jetzt fehlt noch in Spalte C ein Name, der anstelle der kryptischen Adresse als sichtbarer Inhalt des Hyperlinks erscheinen soll, was die Formel
=RECHTS(A1;LÄNGE(A1)-FINDEN("]";A1))
und ein abermaliges Herunterkopieren erledigt. Die Formel durchsucht den Eintrag in Spalte A nach der schließenden Klammer und gibt den Text rechts davon, den reinen Blattnamen, zurück. Zum Abschluss muss man Adresse und Namen nur noch zu einem Hyperlink verheiraten, was
=HYPERLINK(B1;C1)
und ein letztmaliges Herunterkopieren der Formel erledigt. Ein Nachteil der Excel-4-Makrofunktionen sei aber nicht verschwiegen: Wie alle Makros funktionieren sie nur mit Sicherheitsstufe "Mittel" und darunter.
Per Hyperlink zu allen Tabellen - der Einsatz einer Excel-4-Makrofunktion machts möglich.
Lucky Number
Zum Schluss noch die den Analyse-Funktionen entliehene (Glücks-)Formel für den ultimativen Lottozahlengenerator. Sie lautet
=ZUFALLSBEREICH(1;49)
und ergibt nach sechsmaligem Drücken der F9-Taste ja vielleicht sogar den Hauptgewinn. Falls es nicht klappen sollte, berechnen Sie Ihre Gewinnchancen für die nächste Ziehung. Die passende Formel heißt
=KOMBINATIONEN(49;6)
und kalkuliert die Wahrscheinlichkeit eines einfachen Sechsers ohne Zusatzzahl auf rund 1 zu 14 Millionen. Na dann: Viel Glück! (db)
Literatur
[1] Jens Fleckenstein, Walter Fricke, Boris Georgi, Excel - Das Zauberbuch, Markt+Technik, 2006
[2] Ignatz Schels, Excel - Formeln und Funktionen, Markt+Technik, 2005
[3] Bernd Held, Ignatz Schels, Excel - Geheime Tricks, Markt+Technik, 2006
Handwerkszeug
Excel 2003 bringt es auf über 330 Funktionen. Über diesen mehr als ansehnlichen Werkzeugkasten hinaus enthält das Kalkulationsprogramm mit den Analyse-Funktionen so manchen Formelschatz, durch den sich knifflige Rechenprobleme beheben lassen. Da verwundert es, dass diese nützlichen Helfer zwar von Haus aus installiert werden, das zugehörige Add-in aber abgeschaltet bleibt. Das Aktivieren erfordert nur einen gezielten Mausklick auf das Kontrollkästchen "Analyse-Funktionen" im Add-ins-Dialog, der sich übers Extras-Menü aufrufen lässt.
Sollen mehrere Zellen einer Tabelle die gleichen Formeln oder Werte erhalten, kann man sich viel Arbeit sparen, indem man die Inhalte nicht einzeln über die Zwischenablage kopiert, sondern sich der Hilfe des rechts unten an der Zellmarkierung angebrachten Füllkästchens bedient. Das muss man einfach nur über benachbarte Zellen ziehen, um den Inhalt der Ausgangszelle auf diese zu übertragen.
Alternativ markiert man die gewünschten Zellen, gibt die gewünschte Formel ein und betätigt bei gedrückter Strg-Taste die Eingabetaste. Wer den Überblick im Funktionsdickicht verloren hat, kann jederzeit mit Strg-# auf die Formelansicht umschalten. Dabei werden statt der berechneten Werte alle Formeln im Arbeitsblatt sichtbar. Ein erneuter Druck auf Strg-# schaltet auf die Normalansicht zurück.
Bewegliche Feiertage
Feiertag Differenz zu Ostern Bundesland
Karfreitag -2 alle
Ostersonntag 0 alle
Ostermontag 1 alle
Christi Himmelfahrt 39 alle
Pfingstsonntag 49 alle
Pfingstmontag 50 alle
Fronleichnam 60 Baden-Württemberg, Bayern, Hessen, Nordrhein-Westfalen, Rheinland-Pfalz, Saarland, Sachsen
