Dynamische Matrizen

Die bisherige Vorgehensweise, um eine Formel als Matrixformel einzugeben, sah so aus: Der erste Schritt ist die Markierung des Zellbereichs, der die Ergebnismatrix aufnehmen soll, beispielsweise D4:D26.

Die Größe dieser Ergebnismatrix sollte in der Regel exakt so groß sein, dass die Matrixformel alle Ergebnisse erzeugen kann, die durch die Wertegruppe(n), auf die sich die Formel bezieht, möglich sind. Ist die Ergebnismatrix kleiner, werden mögliche Ergebnisse »unterschlagen«, ist sie dagegen größer, erscheint in den überzähligen Zellen der Wert NV als Hinweis darauf, dass für diesen Fall keine Werte vorliegen, mit denen die Formel arbeiten kann.

Der zweite Schritt ist die Eingabe der Formel in eine der Zellen der Ergebnismatrix.

Dabei spielt es keine Rolle, in welche dieser Zellen die Formel eingetragen wird. Die Formel kann wie jede Einzelwertformel Konstanten, Operatoren, Zellbezüge und Funktionen enthalten. Die Besonderheit besteht darin, dass in der Formel Bezug auf Wertegruppen genommen wird, die in der Matrixformel dann zeilen- oder spaltenweise ausgewertet werden.

Um beispielsweise Werte aus einem Bereich in der Spalte B mit Faktoren in einem Bereich der Spalte C zu multiplizieren, könnte folgende Formel verwendet werden: {=B4:B26 * C4:C26} Die geschweiften Klammern, die die gesamte Formel einschließen und sie damit als Matrixformel kennzeichnen, dürfen allerdings nicht manuell eingegeben werden. Sie werden automatisch erzeugt, wenn die Formel quittiert wird. Dies geschieht in diesem Fall nicht wie bei den Einzelwertformeln durch einen Klick auf das Häkchen oder durch Enter. Um Excel zu veranlassen, eine Formel als Matrixformel zu behandeln, musste die Formel mit der Tastenkombination Strg+Umschalt+Enter abgeschlossen werden.

Wenn Sie sich die Ergebnisspalte anschauen, können Sie feststellen, dass Excel in jede Zelle dieselbe Formel gesetzt hat. Trotzdem sind die Ergebnisse verschieden. Die Matrixformel bewirkt nämlich, um beim Beispiel zu bleiben, dass in der ersten Ergebniszelle D4 die Multiplikation B4*C4 berechnet wird, in D5 aber B5*C5 etc. Das gewählte Beispiel setzt also zwei gleich große Zellbereiche miteinander in Beziehung und erzeugt eine entsprechend große Ergebnismatrix. Wenn Sie versuchen, eine Zelle in der Ergebnismatrix mit einem anderen Wert zu überschreiben, wird das mit einer Fehlermeldung verhindert.

Das neuere Verfahren In der Version 2021 und der entsprechenden Abo-Version ist dieses etwas umständliche Verfahren deutlich vereinfacht worden. Excel arbeitet dabei mit dynamischen Matrizen. Eine Markierung des Ergebnisbereichs vor der Formeleingabe entfällt. Die Formel für eine dynamische Matrix wird immer in die erste Zelle der erwarteten Ergebnismatrix eingetragen, im beschriebenen Beispiel also in D4 und ganz normal mit Enter oder dem Häkchen quittiert.

Die Formel wird dann von Excel automatisch auf so viele Zellen nach unten ausgebreitet, wie es der Anzahl der Zellen in den verwendeten Wertebereichen entspricht. In der Bearbeitungsleiste erscheint die Formel ohne die geschweiften Klammern. Soll die Formel nachträglich verändert werden, muss dies ebenfalls in der ersten Zelle des Ergebnisbereichs stattfinden. In den anderen Zellen des Ergebnisbereichs erscheint die Formel in einer helleren Schriftfarbe.

Wird die Zelle mit der Matrixformel gelöscht, wird die gesamte Matrix gelöscht. Bei allen anderen Zellen wird das Löschen einfach – also ohne einen entsprechenden Hinweis - verhindert Wollen Sie erzwingen, dass die Formel in D4 statt einer Matrix nur einen Wert für die Zeile 4 liefert, können sie mit dem impliziten Schnittmengenoperator arbeiten: =@B4:B26 * @C4:C26 Wird dagegen =@B4:B26 * C4:C26 eingegeben, verweigert Excel die Annahme eines solchen Mischbezuges und schlägt eine entsprechende Korrektur vor.

Auf eine solche dynamische Matrix kann mit dem #-Operator sehr einfach ein Bezug hergestellt werden. =D4# liefert die komplette Ergebnismatrix, die die Formel in D4 generiert hat. Wenn Sie nun beispielsweise den größten Wert aus dieser Matrix finden wollen, reicht die Formel =MAX(D4#). Der Bezug mit # gilt auch bei Matrizen, die mit Strg+Umschalt+Enter erstellt wurden, aber nur, wenn dabei die linke obere Eckzelle als Adresse angegeben wird. Beachten Sie, dass der Bereich, in dem die Ergebnismatrix ausgerollt werden soll, leer sein muss und auch keine verbundenen Zellen enthalten darf. Ist das nicht der Fall, erscheint in der Formelzelle der Fehler #ÜBERLAUF!. Über das Menü zu diesem Fehler können die störenden Zellinhalte ausgewählt werden, um sie zu entfernen.

Dieser Fehler erscheint auch, wenn Sie eine Zelle in der Ergebnismatrix ändern. Sobald der störende Zellinhalt gelöscht ist, wird die Matrix wieder mit den Ergebnissen gefüllt. Zum Fehler #ÜBERLAUF! kommt es auch, wenn der Ergebnisbereich über die letztmögliche Zeile oder Spalte des Arbeitsblattes hinausreichen würde. Auch verbundene Zellen im Ergebnisbereich produzieren diesen Fehler. Ein Spezialfall sind dynamische Bereiche, deren Größe instabil ist, weil z.B. die Anzahl der Zeilen mit einer Zufallszahl ermittelt wird. Dies führt zu dem Fehler #KALK!.

Für den Einsatz dynamischer Matrizen ist es vorteilhaft, mit benannten Bereichen zu arbeiten. Werden die Bereiche in ihrem Umfang verändert, wird die Ergebnismatrix automatisch angepasst. Allerdings werden Formatierungen nicht auf neue Zellbereiche übertragen. Bei Tabellen ist es praktisch, innerhalb der Matrixformeln mit strukturierten Verweisen zu arbeiten, die ebenfalls zu einer automatischen Anpassung führen, wenn die Größe der Tabelle verändert wird. Matrixformeln bearbeiten

Auch wenn Sie eine Matrixformel ändern wollen, unterscheiden sich die beiden beschriebenen Verfahren. Bei einer "alten" Matrixformel mit den geschweiften Klammern können Sie eine beliebige Formelzelle in der Ergebnismatrix auswählen. Sobald Sie die Bearbeitungsleiste aktivieren, verschwinden dort die geschweiften Klammern. Wenn die Änderungen an der Formel abgeschlossen sind, muss die Formel erneut mit Strg+Umschalt+Enter quittiert werden. Daraufhin werden alle zur Matrix gehörenden Formeln geändert. Dieser Abschluss ist unbedingt notwendig, sonst erhalten Sie die Fehlermeldung, dass ein Teil einer Matrix nicht geändert werden kann. Das hat seinen Grund darin, dass Excel eine solche Formelmatrix als eine Einheit betrachtet. Es ist also nicht möglich, einzelne Elemente einer Matrix gesondert zu bearbeiten. Ausgenommen von dieser Regel sind Operationen, die nur die Gestaltung betreffen. Alle inhaltlichen Veränderungen betreffen aber immer die gesamte Matrix. Deshalb können einzelne Zellen auch nicht gelöscht oder verschoben werden. Nur die komplette Matrix kann gelöscht oder verschoben werden. Auch das Einfügen von Zellen in eine Matrix, die mit Matrixformeln belegt ist, führt zu einer Fehlermeldung. Dagegen ist es durchaus möglich, den Inhalt einzelner Zellen an eine andere Stelle der Tabelle zu kopieren. Die Bezüge werden dabei automatisch angepasst.

Matrixformeln nach dem neuen Verfahren, also ohne die geschweiften Klammern, können nur in der ersten Zelle der Ergebnismatrix geändert werden. Die Änderung wird dann wieder einfach mit Enter oder dem Häkchen bestätigt. Wenn es gewünscht ist, Matrixformeln in Einzelwerte – also in ihr jeweiliges Ergebnis – zu verwandeln, muss zunächst die gesamte Matrix markiert werden. Mit dem Befehl Start • Zwischenablage • Kopieren bringen Sie die Daten in die Zwischenablage und fügen sie anschließend an derselben Stelle mit Start • Zwischenablage • Einfügen und der Option Werte wieder ein. Die Formeln werden durch ihr Ergebnis überschrieben.

Um einen Matrixbereich nach der alten Methode vollständig zu markieren, genügt es, eine Zelle des Bereichs zu wählen und dann Strg+/ zu drücken. Diese Tastenkombination entspricht der Option Aktuelles Array in dem Dialogfeld Inhalte auswählen des Befehls Gehe zu. Diese Option kann bei den dynamischen Matrizen nicht verwendet werden. Eine Hilfe ist hier, dass die Matrix vorübergehend eingerahmt wird, wenn eine Zelle darin ausgewählt wird. Außerdem können Sie mit dem Befehl Gehe zu oder Strg+G den Bereich markieren, wenn Sie unter Verweis den Bezug auf die erste Stelle der Matrix mit dem #-Operator angeben.

Der #-Operator ist auch hilfreich, wenn Sie die ganze dynamische Matrix mit einem Namen belegen wollen. Sie markieren die erste Zelle und geben in Formeln • Definierte Namen • Namen definieren unter Bezieht sich auf den Bezug auf diese Zelle mit # an.

Suchfilter in Tabellen

Eine kleine, aber feine Funktion in Excel ist der Suchfilter, der insbesondere bei Spalten mit tausenden von Texteinträgen hilfreich ist. Sind in einem Tabellenbereich die Filterschaltflächen eingeblendet, bietet der Dialog zu jeder dieser Schaltflächen jetzt ein Eingabefeld für Suchbegriffe, in dem auch die in Excel üblichen Platzhalterzeichen funktionieren. Die Abbildung zeigt ein Beispiel, in dem die Treffer für die bereits eingegebenen Zeichen immer sofort zur Auswahl gestellt werden. Diese Auswahl lässt sich dann in der Liste unter dem Suchfeld noch manuell bearbeiten, wenn nicht alle Suchergebnisse erwünscht sind.

Wertaggregate

Eine der interessantesten Funktionen in Excel ist die AGGREGAT()-Funktion. Sie kommt in zwei syntaktischen Varianten:

AGGREGAT(Funktion; Optionen; Bezug1; Bezug2…)

AGGREGAT(Funktion; Optionen; Array; k)

Die Funktion erlaubt es, Daten in Tabellenbereichen oder Arrays mit verschiedenen Funktionen auszuwerten, ohne dass diese Auswertung beispielsweise durch eventuelle Fehlerwerte gestört wird. Damit wird das Problem gelöst, dass bestimmte statistische Funktionen als Ergebnis einen Fehlerwert liefern, wenn in dem ausgewerteten Datenbereich Fehlerwerte vorkommen. Die Suche nach dem höchsten Wert in einem Zellbereich A5:A11 mit der Funktion MAX() scheitert z. B. schon dann, wenn eine einzige Zelle darin einen Fehlerwert enthält. Wird stattdessen die Formel

=AGGREGAT(4;6;A5:A11)

eingesetzt, liefert Excel den höchsten Wert unter Ignorierung der Zelle mit dem Fehlerwert. Das erste Argument von AGGREGAT() bestimmt dabei die Funktion, mit der die Auswertung vorgenommen wird. Die möglichen Werte werden bei der Eingabe der Funktion angeboten, 4 steht beispielsweise für die Funktion MAX(). Das zweite Argument Optionen bestimmt, wie die Auswertung mit Fehlerwerten, ausgeblendeten Zellbereichen oder Leerwerten bzw. mit verschachtelten TEILERGEBNIS()- oder AGGREGAT()-Funktionen verfahren soll, die in dem auszuwertenden Datenbereich vorkommen. Mit dem Wert 6 ignoriert die Funktion alle Fehlerwerte, der Wert 5 vernachlässigt ausgeblendete Zellen. Auch hier werden die möglichen Werte bei der manuellen Formeleingabe angeboten. Sie müssen sich diese Codes also nicht merken.

Syntax1 erlaubt als weitere Argumente bis zu 254 Bezüge auf Zellbereiche. In Syntax2 werden die auszuwertenden Daten über das Argument Array bestimmt. Benötigt die mit Funktion gewählte Auswertung selbst noch ein weiteres Argument, wird dieses mit dem Wert für k angegeben. Bei der Funktion KKLEINSTE() gibt k = 2 z. B. an, dass der zweitkleinste Wert gesucht wird.

Die Funktion kann insbesondere auch für bedingte Formatierungen von Bereichen genutzt werden, um zu verhindern, dass Zellen mit Fehlerwerten die Auswertung mit statistischen Funktionen blockieren.

Beispiel für die AGGREGAT()-Funktion 

Dynamischer Quellbereich

Definition eines dynamischen Bereichs 

Der Einsatz von Pivottabellen in Excel ist besonders effektiv, wenn mit dynamischen Datenquellen gearbeitet wird. Die Pivottabelle ist in diesem Fall in der Lage, flexible Datenbereiche auszuwerten.

Um dies zu erreichen, definieren Sie für den Datenbereich, der als Quelle der Pivottabelle vorgesehen ist, zunächst einen Bereichsnamen, der mit einer dynamischen Formel arbeitet. Wenn der aktuelle Quellbereich - beispielsweise eine Tabelle mit Vertriebsdaten - ausgewählt ist, öffnen Sie über das Register Formeln mit Namen definieren den Dialog Neuer Name. Die im Beispiel verwendeten Vertriebsdaten werden etwa als VDaten bezeichnet.

Unter Bezieht sich auf geben Sie normalerweise einfach einen fixen Tabellenbereich an oder wählen ihn aus. Um den Bereich zu dynamisieren, nehmen Sie die Funktion mit dem Namen BEREICH.VERSCHIEBEN(), eine etwas holprige Übersetzung der engl. Bezeichnung OFFSET(). Als erstes Argument geben Sie den Startpunkt des Bereichs mit absoluten Bezügen an. Die Tabelle beginnt beispielsweise auf dem Blatt "Vertrieb" mit der Zelle $A$1. Deshalb werden die beiden folgenden Argumente für die Zeilen- und Spaltenverschiebung auf null gesetzt. Der Bereich soll also in diesem Fall nicht insgesamt verschoben, sondern nur von seinem fixen Startpunkt aus erweitert werden. Die Bereichsgröße bestimmt die Zahl der Datenzeilen, die in der angegebenen Spalte keine leeren Zellen enthalten. Die Erweiterung geht nach unten, deshalb wird für die Angabe des Arguments Höhe die Funktion ANZAHL2() herangezogen. Sie prüft, wie viele Einträge in der Spalte A aktuell vorkommen. Für Breite ist dagegen eine fixe Spaltenanzahl angegeben. Insgesamt lautet die Formel:

=BEREICH.VERSCHIEBEN(Vertrieb!$A$1;0;0;ANZAHL2(Vertrieb!$A:$A);6)

Den so definierten Namen geben Sie beim Einfügen einer Pivottabelle im Dialog PivotTable erstellen unter Tabelle/Bereich an oder wählen ihn mit F3 aus. Wächst der Quelldatenbereich, werden die neuen Daten automatisch in der Pivottabelle mit ausgewertet.

Sichere Eingabe von Beschriftungen

Wenn Sie ein Tabellenblatt entwerfen, das von anderen Mitarbeitern ausgefüllt werden soll, tun Sie gut daran, Vorsorge gegen potenzielle Fehlerquellen zu treffen. Sind in einer Spalte nur bestimmte Werte zugelassen, hilft die Definition einer Gültigkeitsregel. Wird eine längere Liste von erlaubten Werten benötigt, ist es ganz praktisch, diese Liste zunächst in einem freien Zellbereich anzulegen. In dem abgebildeten Beispiel ist eine Liste von Kategorien angelegt, um Buchtitel danach zu ordnen. Ist die Liste fertig, sollten Sie den entsprechenden Bereich mit einem passenden Namen versehen, etwa "Kategorien". Markieren Sie den Bereich und benutzen Sie in Excel 2010/2007 das Register Formeln und den Befehl Namen definieren.

Im nächsten Schritt markieren Sie die Spalte der Tabelle, in der Sie den Buchtiteln jeweils die entsprechende Kategorie zuweisen. Wählen Sie über das Register Daten in der Gruppe Datentools die Schaltfläche Datenüberprüfung. Auf dem Register Einstellungen nehmen Sie unter Zulassen die Option Liste. Aktivieren Sie die Optionen Leere Zellen ignorieren und Zellendropdown, damit die erlaubten Werte pro Zelle als Listenfeld angeboten werden. Unter Quelle geben Sie den Namen des Bereichs an, in dem die Kategorien abgelegt sind. Vergessen Sie nicht, ein Gleichheitszeichen davor zu setzen. Auf dem Register Fehlermeldung lässt sich noch festlegen, ob die Werte der Liste die einzig möglichen sind oder ob ausnahmsweise auch andere Werte zugelassen sind. Im ersten Fall wählen Sie unter Typ die Option Stopp. Die beiden anderen Optionen sind Warnung oder Informationen. Die Warnung bietet einen Dialog an, über den eine eigentlich nicht vorgesehene Eingabe doch noch zugelassen werden kann. Dazu wählen Sie Ja, ansonsten lehnen Sie die Eingabe mit Nein ab. Bei Informationen erscheint ein Dialog, der nur darüber informiert, dass die Eingabe nicht den Regeln entspricht. OK lässt die Eingabe dennoch in der Zelle stehen.

Die Abbildung zeigt die Übernahme der Liste der angebotenen Werte zu einer Zelle im Dialogfeld für die Definition der Gültigkeitsregel.

Festlegen von Regeln für die Eingabe von Werten  

Wie oft kommt etwas vor? Häufigkeitsberechnungen

Wer wissen will, wie häufig ein bestimmter Wert in einem Zellbereich vorkommt, dem bietet Excel die statistische Funktion HÄUFIGKEIT() an. Die Bestimmung der Verteilung von Häufigkeiten ist oft der erste Schritt bei der Auswertung von statistischen Daten. Sie dient dazu, die Einzeldaten so zusammenzufassen, dass brauchbare Aussagen über den erhobenen Datenbestand möglich werden. Vor allem, wenn es sich um stetige Merkmale handelt, ist es sinnvoll, bestimmte von-bis-Klassen zu bilden und für diese die Häufigkeitsverteilung zu ermitteln.

Als Argumente der Funktion HÄUFIGKEIT() werden der Bereich mit den Daten und ein Bereich mit Klassengrenzen angegeben. Die Funktion ist dann beispielsweise in der Lage, eine Gruppe von Personen nach bestimmten Alterstufen oder Einkommensstufen zu gruppieren. Die Abbildung zeigt als Beispiel eine Tabelle mit einer Spalte, in der Trefferquoten bei einem Spiel notiert sind. Im Bereich E4:E6 sind die Klassengrenzen eingetragen. Da die Funktion als Ergebnis eine Matrix liefert, muss zunächst ein entsprechender Bereich markiert werden, hier ist es F4:F7. Der Bereich enthält eine Zelle mehr als der Bereich mit den Klassengrenzen, damit in der letzten Zelle die Anzahl der Werte angezeigt wird, die über der höchsten Klassengrenze liegen. Die Formel für all diese Zellen ist dann:

{=HÄUFIGKEIT(B4:B23;E4:E6)}

Damit es eine Matrixformel ist, muss sie entsprechend mit Strg+Umschalt+Enter abgeschlossen werden.

Beispiel für die Funktion Häufigkeit()

Was aber, wenn Sie einfach nur wissen sollen, wie häufig die einzelnen Werte einer Spalte darin vorkommen? Hier hilft die Funktion ZÄHLENWENN(). Die Formel in C4 der abgebildeten Tabelle heißt:

=ZÄHLENWENN($B4:$B24;B4)

Diese Formel wird per Doppelklick auf das Ausfüllkästchen bis zu C24 kopiert. Das erste Argument, der Bereich, der ausgewertet werden soll, wird mit absoluten Zelladressen angegeben, damit er beim Kopieren nicht verändert wird. Als zweites Argument – Suchkriterium – wird jeweils die Zelle mit der einzelnen Trefferquote angegeben.

Import von XML-Daten in ein Arbeitsblatt

Wenn Daten in einem XML-Dokument in einer relativ flachen Hierarchie geordnet sind, bietet Excel ein unkompliziertes Verfahren, diese Daten in eine Arbeitsmappe einzubinden. Ist die XML-Datei mit einem XML-Schema verknüpft, wird dieses automatisch übernommen, im anderen Fall generiert Excel automatisch ein passendes Schema. Die Übernahme des XML-Dokuments lässt sich über das Register Daten bewerkstelligen. Benutzen Sie in der Gruppe Externe Daten abrufen die Option Aus anderen Quellen und dort die Schaltfläche Vom XML-Datenimport. Im Dialog geben Sie die vorgesehene XML-Datei an. Ist die Datei nicht mit einem Schema verknüpft, bestätigen Sie die Meldung, dass Excel ein Schema dafür generiert.

Im nächsten Schritt legen Sie fest, wo die Daten, innerhalb des Arbeitsblatts erscheinen. Über die Schaltfläche Eigenschaften bestimmen Sie, ob jeweils eine Validierung am Schema stattfinden soll, regeln die Formatierung der Spalten und die Anpassung der Breite. Vor allem wählen Sie die Art der Aktualisierung, falls sich die Quelldaten später einmal ändern sollten. Mit der Übernahme der XML-Dateien wird die Quelldatei der Excel-Datei zugeordnet. Es entsteht dabei also eine Verknüpfung der beiden Dateien. Neue Daten in der XML-Datei überschreiben bei einer Aktualisierung des Arbeitsblatts entweder die dort schon abgelegten Daten oder werden an diese angehängt.

Excel übernimmt die XML-Daten in einen mit einer Tabellenformatvorlage vorformatierten Tabellenbereich. Die Hierarchie der Quelle wird dabei in eine zweidimensionale Tabelle flach gedrückt, wie die Abbildung an einem Beispiel zeigt, das Belegdaten verwendet. Die Kundendaten des Belegs werden in den vorderen Spalten für jeden Artikel einfach wiederholt. Übergeordnete Elemente wie <kunde> oder <position> werden nicht angezeigt. Über das Kontextmenü der Tabelle blenden Sie mit XML/XML-Quelle den Aufgabenbereich ein, der den Baum der Elemente des XML-Dokuments anzeigt. Mit XML-Daten aktualisieren werden Änderungen im Quelldokument in das Arbeitsblatt übernommen.

XML-Daten als Tabelle und der Baum der Elemente

Indirekte Bezüge – dynamische Bezüge

Eher zu den Mauerblümchen-Funktionen in Excel zählt INDIREKT() aus der Kategorie der Nachschlage- und Verweisfunktionen, wie sie jetzt in Excel 2010 genannt werden. Es gibt aber Situationen, in denen gerade diese Funktion hilfreich ist. Die Syntax ist einfach:

INDIREKT(Bezug; A1)

Die Funktion ermittelt indirekt den Inhalt einer Zelle, auf die in einer anderen Zelle, die das Argument Bezug angibt, verwiesen ist. Mit dem Argument A1 wird angegeben, welche Schreibweise für den Eintrag in Bezug gilt: die A1-Schreibweise (WAHR oder weggelassen) oder die Z1S1-Schreibweise (FALSCH). Der Bezug wird in beiden Fällen als Zeichenfolge in der Zelle abgelegt, die ihn für Formeln in anderen Zellen bereitstellt. Wird das Argument Bezug direkt eingegeben, muss es deshalb auch in Anführungszeichen gesetzt werden. Wird das Argument selbst durch andere Funktionen erzeugt, wie in dem folgenden Beispiel, muss das Ergebnis ebenfalls eine Zeichenfolge sein, die sich als Angabe für einen Bezug auf eine Zelle oder einen Zellbereich auswerten lässt. Ist das nicht der Fall,  liefert die Funktion den Fehlerwert #Bezug!.

Nützlich ist das indirekte Verfahren insbesondere, um Zellbezüge dynamisch zu erzeugen. In einer Arbeitsmappe kann beispielsweise für jeden Monat ein Blatt angelegt werden, benannt mit  kurzen Monatsnamen. Nun lassen sich mithilfe der Funktion INDIREKT() in dieser Arbeitsmappe Zellbezüge herstellen, die  vom Monat des aktuellen Datums abhängig sind.  Ist auf allen Monatsblättern in der Zelle B7 ein bestimmter Planwert abgelegt, kann eine Formel in der Zelle C7 in einem den Monatsblättern vorgestellten Blatt „Aktuelle Werte“ den im aktuellen Monat gültigen Wert  mit folgender Formel abgreifen und verwerten:

=WAHL(MONAT(HEUTE());"Jan";"Feb";"Mrz";"Apr";

"Mai";"Jun";"Jul";"Aug";"Sep";"Okt";

"Nov";"Dez") & "!B7

Diese Formel liefert etwa im Mai die Zeichenfolge: Mai!B7. Die Formel

=INDIREKT(C7)

in der Zelle B7 im Blatt „Aktuelle Werte“  liefert dann den Inhalt der Zelle B7 aus dem Blatt für den Monat Mai.

Interaktive Kalkulation mit  Steuerelementen

Für Was-wäre-wenn-Analysen stellt Excel eine ganze Reihe von Werkzeugen zur Verfügung. Eine handliche Lösung besteht darin, Werte, deren Wirkung auf ein Kalkulationsmodell beobachtet werden sollen, mit Formularsteuerelementen zu verknüpfen. Auf diese Weise ergibt sich ein interaktives Modell. Ein kleines Beispiel zeigt das Verfahren. Mit einer Barwertberechnung lässt sich abschätzen, ob ein bestimmter Betrag - geerbt oder gewonnen - ausreicht, eine monatliche Zusatzrente über einen bestimmten Zeitraum zu gewährleisten. Die benötigte Formel mit der BW()-Funktion arbeitet dazu mit Zellen für Betrag, Laufzeit, Zinssatz und Monatsrate. Es ist sinnvoll, die Zellen zunächst entsprechend zu benennen. Das erlaubt dann die folgende Formel zu erzeugen:

=BW(Zinssatz/12;Laufzeit*12;Monatsrate)

Wollen Sie nun beispielsweise herausfinden, wie sich verschiedene Laufzeiten auf das Ergebnis dieser Formel auswirken, koppeln Sie die Zelle mit den Laufzeiten an ein Drehfeld, das die Palette der Formularsteuerelemente anbietet. Um die Palette zu öffnen, klicken Sie auf dem Register Entwicklertools in der Gruppe Steuerelemente auf Einfügen. Falls das Register nicht sichtbar ist, blenden Sie es über Excel-Optionen unter Menüband anpassen ein, indem sie die Option im rechten Fenster abhaken. 

Sie ziehen das Steuerelement Drehfeld in der gewünschten Größe auf und verwenden über das Kontextmenü Steuerelement formatieren. Auf dem Register Steuerung geben Sie unter Zellverknüpfung die Adresse der Laufzeitzelle an. Minimal- und Maximalwert legen die Bandbreite der Laufzeitwerte fest, Schrittweite bestimmt das gewünschte Intervall, das einem Klick entsprechen soll. Anschließend lassen sich die verschiedenen Laufzeiten per Mausklick bequem auswählen. Entsprechende Steuerelemente für Zinssatz oder Monatsrate erweitern das Modell, wie die Abbildung zeigt.

Interaktives Kalkulationsmodell mit verschiedenen Steuerelementen 

Listenvergleich per Format

Es kommt vor, dass Daten zum gleichen Thema an verschiedenen Stellen oder zu verschiedenen Zeitpunkten erfasst werden, etwa eine Liste von eindeutigen Nummern oder Benutzernamen.  Soll jetzt festgestellt werden, welche Daten jeweils in der einen oder anderen Liste fehlen, kann ein Verfahren helfen, dass die nicht in beiden Listen vorkommenden Einträge optisch kennzeichnet, sodass geprüft werden kann, welcher Eintrag eventuell in die andere Liste übernommen werden sollte.

Mit bedingten Formaten lassen sich in Excel Beziehungen zwischen Daten auf einfache Weise optisch hervorheben. Um beispielsweise die Unterschiede zwischen zwei Wertespalten schnell zu finden, reichen Formeln, die bestimmen, in welcher Farbe der Zellhintergrund und die Schrift erscheinen. Es vereinfacht die Sache, wenn Sie beide Wertebereiche zunächst über Formeln/Namen definieren benennen. Ist der erste Bereich ausgewählt, rufen Sie über Start/Bedingte Formatierung/Neue Regel den Dialog Neue Formatierungsregel auf. Unter Regeltyp auswählen wird die letzte Option benötigt, die die Eingabe einer Formel erlaubt. Die Formel

=ZÄHLENWENN(WertelisteB;A2)=0

prüft, ob der erste Wert in der Spalte in der Nachbarspalte nicht zu finden ist. Falls dies der Fall ist, soll die Zelle eine andere Hintergrund- und Textfarbe erhalten, was Sie über die Schaltfläche Formatieren festlegen. Für die zweite Spalte wird entsprechend die Formel

=ZÄHLENWENN(WertelisteA;B2)=0

verwendet. Insbesondere, wenn es sich in beiden Spalten um eindeutige Werte handelt, die in der gleichen Weise sortiert sind, ist dieses Verfahren sehr effektiv, wie die Abbildung zeigt. Sie sehen sofort, welche Werte jeweils in der anderen Spalte fehlen oder zusätzlich vorkommen. Dabei spielt es keine Rolle, ob es sich um Zahlen oder Textwerte handelt.

Hintergrundfarben zeigen die Unterschiede zwischen Zellwerten in benachbarten Spalten

Netzdiagramme für Zyklen

Zu den eher selten benutzen Diagrammtypen gehört das Netzdiagramm, das aber gerade für die Visualisierung von Abläufen hilfreich sein kann.  Das Netzdiagramm in Excel ähnelt einem Polarkoordinaten-Diagramm, allerdings gibt es einige Unterschiede, die beachtet werden müssen:  Die Winkel im Netzdiagramm von Excel lassen sich nicht direkt angeben, sie werden durch die Anzahl der Werte automatisch bestimmt. Die Reihenfolge entspricht dem Uhrzeigersinn, der erste Wert liegt oben am höchsten Punkt; bei Polarkoordinaten geht die Reihenfolge  gegen den Uhrzeigersinn und beginnt an der 3 Uhr-Position. Die Größenachsen des Netzdiagramms dürfen außerdem auch negative Werte annehmen.

Nützlich ist das Netzdiagramm hauptsächlich für die Abbildung zyklischer Prozesse oder Daten. Das mag die Temperatur oder die Niederschlagsmenge im Laufe eines Monats oder Jahres sein, die Wachstumsrate einer Pflanze in einem Jahreszyklus usw. Im Prinzip ist es auch bei diesem Diagrammtyp möglich, mehrere Datenreihen zu verarbeiten, aber bei mehr als zwei oder drei Datenreihen wird das Diagramm ziemlich unübersichtlich.

Das (fiktive) Beispiel in der Abbildung  zeigt die durchschnittliche tägliche Temperatur in einem Land über die zwölf Monate eines Jahres. Die Darstellung der Datenlinie und der Datenpunkte sollten Sie dabei immer so wählen, dass sie im Diagramm gut sichtbar sind. Eine geschickte Skalierung sorgt dafür, dass tatsächlich alle Werte gut ablesbar sind. Durch eine geeignete Wahl der Intervalle lässt sich gewährleisten, dass die Achsenbeschriftung nicht mit den Daten kollidiert.

Netzdiagramme erlauben die Anzeige zyklischer Daten

Excel bietet zwei zusätzliche Varianten dieses Diagrammtyps an: eine Variante zeigt die einzelnen Datenpunkte mit Markern an, die über die Dialoge Datenreihen formatieren oder Datenpunkt formatieren unterschiedlich gestaltet werden können, etwa um bestimmte Werte besonders hervorzuheben.  Die andere Variante füllt den Raum des Netzes mit einer beliebigen Farbe aus.

Verknüpfte Tabellen-Snapshots

Eine häufig übersehene  Funktion in Excel erlaubt verknüpfte Tabellenbilder. Damit erzeugen Sie Abbildungen von vorher markierten Tabellenbereichen, die anschließend als grafische Objekte behandelt werden. Jedes Objekt bleibt dabei über eine Formel mit dem entsprechenden Tabellenbereich verknüpft, zeigt also alle Änderungen sofort an.

Zunächst markieren Sie den Tabellenbereich, der fotografiert werden soll. Kopieren Sie die Markierung in die Zwischenablage. Klicken Sie auf die Zelle in dem Blatt, in der das Foto des Tabellenbereichs abgelegt werden soll. Benutzen Sie aus der Gruppe Start/Zwischenablage in dem Menü der Schaltfläche Einfügen die Option Verknüpfte Grafik.

Excel zeigt in der Bearbeitungsleiste eine Formel mit dem Tabellenbereich an, wenn das neue Objekt ausgewählt ist. Es handelt sich also um eine Verknüpfung. Eine ganz hübsche Anwendung dieser Möglichkeit besteht darin, sich von wichtigen Arbeitsmappen, die täglich verwendet werden,  oder von wichtigen Blättern innerhalb einer Arbeitsmappe jeweils relevante Ausschnitte auf ein Blatt zu kopieren, wie es die Abbildung an einem einfachen Beispiel zeigt. Ein Doppelklick auf einen Auszug öffnet jeweils die zugehörige Arbeitsmappe oder das entsprechende Blatt innerhalb der Arbeitsmappe.

Solche Tabellenfotos lassen sich auch nutzen, um wichtige Daten oder Indikatoren aus verschiedenen Tabellen auf einem Blatt zusammenzuziehen, sodass sie mit einem Blick überprüft werden können.  Für die Tabellenbilder können alle Formatierungsmöglichkeiten genutzt werden, die auch sonst für grafische Objekte zur Verfügung stehen.  Durch Ziehen an den Rahmen oder den  Anfassern ausgewählter Objekte lassen sich die Größen und Positionen frei bestimmen, sodass sich auf einem Arbeitsblatt leicht eine Art Dashboard für kritische Daten anlegen lässt.

Blatt mit zwei Tabellenbildern