Komfortabler rechnen

Tipps zu Steuerelementen und Formeln in Excel

Die Arbeit mit Tabellen kann zeitraubend sein: Viele Schritte wiederholen sich dauernd, andere lassen sich viel zu kompliziert ausführen. Steuerelemente und Formeln erleichtern die Arbeit. Sie können zum Beispiel die Auswahl von Daten beschleunigen oder Vor- und Nachnamen aus E-Mail-Adressen extrahieren.

Von Rainer Schwabe

Umfangreiche Excel-Tabellen sind oft unübersichtlich und lassen sich nur schwer bearbeiten. Autofilter verschaffen eine bessere Übersicht, da sie aus dem Datenwust das heraussieben, was Sie wirklich wollen. Dazu drücken Sie Strg+T und Ihre Liste wird schnell als Tabelle mit Auswahlfiltern in der ersten Zeile jeder Spalte formatiert. Mit diesen zeigen Sie in Excel nur die Listeneinträge beziehungsweise Datensätze an, die bestimmte Bedingungen erfüllen. Die formatierte Tabelle wird sofort erweitert, sobald Sie Daten in den Nachbarzellen ergänzen. Auch Formeln und Funktionen, die sich auf diese Tabelle beziehen, werden automatisch aktualisiert. Wer die Filterpfeile nicht braucht, lässt sie mit Umschalt+Strg+L verschwinden. Mit derselben Tastenkombination lassen sie sich bei Bedarf wieder einblenden.

Währungskurse auf Knopfdruck

Aber auch bei der Gestaltung der Tabellen selbst können Sie einiges zugunsten übersichtlicher Ergebnisse tun. Mithilfe von Steuerelementen lassen sich zum Beispiel Berechnungen schrittweise per Mausklick ausführen. Zudem spart es Platz und vereinfacht Einzelauswertungen. Dies zeigen wir anhand eines Währungsrechners. Den aktuellen Währungskurs können Sie direkt von einer Webseite kopieren, wobei Excel ihn meistens als Zahl erkennt. Der Währungsrechner erwartet den Euro-Wert eines US-Dollars in Zelle F1 und errechnet daraus, wie viel 10, 20, 30 und mehr Dollar wert sind. Den umzurechnenden Betrag erhöht der Nutzer einfach per Mausklick in 10-Dollar-Schritten, woraufhin der Währungsrechner das Ergebnis automatisch anpasst.

Um das zugehörige Steuerelement auszuwählen, holen Sie zunächst das Menüband „Entwicklertools“ in den Vordergrund. Falls es im Menü nicht zu sehen ist, aktivieren Sie es. Bewegen Sie dazu den Mauszeiger irgendwo ins Menüband und drücken Sie die rechte Maustaste. Wählen Sie den Befehl „Menüband anpassen“ und aktivieren Sie unter „Hauptregisterkarten“ die Registerkarte „Entwicklertools“. Nach Schließen des Dialogs mit OK steht das Menüband „Entwicklertools“ zur Verfügung und lässt sich per Maus oder Alt+W aufrufen.

Klicken Sie dort auf die Schaltfläche „Einfügen“, wählen Sie das Steuerelement „Drehfeld“ aus und ziehen Sie es mit gedrückter linker Maustaste im Tabellenblatt auf. Sobald Sie die Maustaste loslassen, erscheinen um das Feld die Begrenzungslinien und -punkte, über die Sie das Steuerelement vergrößern oder verkleinern können. Wenn Sie den Mauszeiger auf das Steuerelement bewegen, lässt es sich bei gedrückter linker Maustaste im Tabellenblatt verschieben. Steuerelemente lassen sich im Nachhinein schlecht aktivieren, was aber zum Beispiel zum Anpassen der Größe notwendig ist. Wenn Sie das Steuerelement aber bei gedrückter Strg-Taste anklicken, klappt es problemlos.

Bei Klick rechnen

Dem Steuerelement können bestimmte Anweisungen zugewiesen werden. Klicken Sie mit der rechten Maustaste darauf, öffnet sich ein Kontextmenü. Hier lässt sich auch ein Makro zuweisen. Für den Währungsrechner führen Sie „Steuerelement formatieren“ aus. Wichtig ist die Schrittweite: Hier geben Sie an, in welchen Schritten der Wert hoch- oder heruntergezählt werden soll, für den Währungsrechner also 10. Bei „Zellverknüpfung“ geben Sie an, in welcher Zelle dies geschehen soll. Sobald Sie das Fenster mit OK schließen, ist die Schaltfläche aktiv und der Währungsrechner lässt sich einsetzen.

Schnelle Auswahl

Sie können über das Menüband „Entwicklertools“ auch eine Liste erstellen, um etwa Elemente aus einer Tabelle einzubinden, die der Nutzer per Mausklick auswählen und so schnell auswerten kann. Sobald dieser einen Eintrag aus der Liste auswählt, werden die zugehörigen Daten automatisch angezeigt. Das wäre zum Beispiel in einer Tabelle mit Umsatzzahlen der Filialen mehrerer Städte praktisch, um einen Städtenamen auszuwählen, woraufhin die Tabelle die Daten für diesen Ort aufbereitet.

Um ein solches Element anzulegen, öffnen Sie das Auswahlfenster über die Schaltfläche „Einfügen“ und klicken Sie auf die Schaltfläche „Kombinationsfeld“. Legen Sie das Feld mit gedrückter linker Maustaste im Tabellenblatt an und platzieren Sie es. Klicken Sie anschließend mit der rechten Maustaste auf das Kombinationslistenfeld und geben Sie im Eingabebereich die Daten der Liste an. Auch wenn sich die Liste auf einem anderen Tabellenblatt befindet, können Sie sie einbinden. Bei „Zellverknüpfung“ geben Sie die Zelle an, in der das Ergebnis der Auswertung stehen soll, zum Beispiel G9. Wenn Sie im Kombinationslistenfeld auf einen Eintrag klicken, zeigt Excel die Position in der Liste an.

Die Funktion INDEX(Bezug;Zeile;[Spalte];[Bereich]) gibt die Werte einer bestimmten Zeile wieder. Geben Sie innerhalb der Funktion zunächst den Bereich der Liste an. Aktivieren Sie danach die Zelle, in der Excel die Position des Datensatzes anzeigt. Dieser wird über die Auswahl der zuvor angelegten Liste verbunden. Da diese Zelle für die Funktion fixiert werden soll, damit der Bezug nach Kopieren des Inhalts erhalten bleibt, legen Sie mit der Taste F4 einen absoluten Bezug fest. Nun geben Sie an, aus welcher Spalte die Informationen übertragen werden sollen. Kopieren Sie die Funktion anschließend in die anderen Zellen und ändern Sie noch für jede Funktion die Spaltenzahl. Excel übernimmt die Inhalte der ausgewählten Spalten und zeigt den kompletten Datensatz an.

Eleganter wäre es, in der Umsatzübersicht den Städtenamen und einen Monat auszuwählen. Dies lässt sich mit den Funktionen INDEX(Bereich;Zeile;[Spalte]) und VERGLEICH(Suchkerkriterium;Suchmatrix;[Vergleichstyp]) realisieren: =INDEX(A1:D24;VERGLEICH(G10;A1:A24;0);VERGLEICH(G8;A1:D1;0)).

Texte verbinden und trennen

In Excel gibt es mehrere Wege, um Zelleninhalte in Text einzubetten. Mit dem kaufmännischen Und lassen sich die Inhalte mehrerer Zellen verketten. Anstelle von Zellbezügen können Sie auch Text in Anführungszeichen hinzufügen: ="Der Umsatz am "&A1&" beträgt für die Filiale "&B2&" "&B3&" Euro." .

Mitunter stehen in einer Spalte jedoch Inhalte, die man in einzelne Spalten aufteilen möchte – zum Beispiel, wenn Nachname und Vorname durch ein Komma getrennt in derselben Spalte stehen. Eine Sisyphusarbeit wäre es, alles per Hand zu trennen. Solange das Trennzeichen eindeutig ist, erledigt dies der Textkonvertierungsassistenten recht komfortabel. Nach dem Markieren der Spalte mit den Ursprungsdaten startet man ihn im Menüband „Daten“ über die Schaltfläche „Text in Spalten“. Hier wählen Sie als ursprünglichen Datentyp „Getrennt“ und geben nach einem Klick auf „Weiter“ das Komma als Trennzeichen ein. In der Vorschau können Sie das Ergebnis überprüfen und den Assistenten mit einem Klick auf „Fertig stellen“ beenden.

Umständlicher wird es, wenn eine Liste unterschiedliche Trennzeichen verwendet. Das wäre zum Beispiel bei E-Mail-Adressen wie „peter.lustig@abc.de“ der Fall, aus denen man Vor- und Nachnamen extrahieren möchte. Im Textkonvertierungsassistenten müsste man zunächst die Domain durch einen Lauf mit „@“ als Trennzeichen extrahieren, sodass in der Ursprungsspalte nur noch Vor- und Nachnamen übrig bleiben. Ein zweiter Lauf würde diese dann mit Punkt als Trennzeichen in zwei Spalten aufteilen.

Einfacher funktioniert dies über eine Formel, die auch gleich für die korrekte Groß- und Kleinschreibung sorgt. Mit GROSS(), GROSS2() und KLEIN() ändert Excel die Schreibweise. Von diesen schreibt GROSS() die gesamte Zeichenkette groß, während GROSS2() nur den ersten Buchstaben anpasst und sich daher für die korrekte Schreibweise von Namen eignet. Die Funktionen LINKS() beziehungsweise RECHTS() selektieren die Zeichen vor beziehungsweise nach einer bestimmten Stelle.

Wenn die E-Mail-Adresse in Zelle A4 steht, extrahiert die Formel =GROSS2(LINKS(A4;FINDEN(".";A4)-1)) den Vornamen. Dabei sucht sie über FINDEN() den ersten Punkt einer Zeichenfolge und gibt die Zeichen links davon (-1) zurück. GROSS2() setzt schließlich das erste Zeichen groß.

Für die Übertragung des Nachnamens sorgt die Funktion TEIL(), die aus einer Zeichenkette ab einer bestimmten Position eine bestimmte Anzahl Zeichen liefert. Auf diese Weise filtert =GROSS2(TEIL(A14;FINDEN(".";A14)+1;FINDEN("@";A14)-FINDEN(".";A14)-1)) den Nachnamen aus der E-Mail-Adresse in Zelle A14. Dabei gibt TEIL() die Zeichenfolge zwischen dem ersten Punkt bis zum Zeichen „@“ wieder. Die beiden ersten FINDEN() ermitteln die beiden Zeichen. Durch Subtraktion der dritten Funktion FINDEN() liefert die Formel schließlich alle Zeichen zwischen Punkt und „@“ zurück.

Zellen verbinden

Zellen lassen sich auch mit einem Mausklick miteinander verbinden, um etwa eine Überschrift über mehrere Spalten hinweg zu zentrieren. Über die Schaltfläche „Verbinden und zentrieren“ im Start-Menüband fassen Sie alles zusammen. Doch einen großen Haken hat die Sache: Die so verbundenen Zellen stören bei Berechnungen. So können Sie zum Beispiel keine Summe aus einer Spalte mit =SUMME(B:C) bilden.

Hier bietet sich ein anderer Weg an. Markieren Sie wieder die entsprechenden Zellen und öffnen Sie den Dialog „Zellen formatieren“ mit Strg+1. Holen Sie dort die Registerkarte „Ausrichtung“ in den Vordergrund. Öffnen Sie die Auswahl bei „Horizontal“ und wählen Sie hier die Angabe „Über Auswahl zentrieren“. Der Inhalt der Zelle wird entsprechend der Markierung zentriert, die Zellen dahinter bleiben jedoch einzeln bestehen und lassen sich für Berechnungen uneingeschränkt nutzen.

Bedingungen verknüpfen

Zum Kasten: Zellzeiger im Sauseschritt

Funktionen wie SUMME(), MAX(), MIN() oder WENN() gehören zum Alltag vieler Excel-Nutzer. In Excel können Sie mehrere Bedingungen gleichzeitig mit nur einer Funktion aufstellen. Mit SUMMEWENN() ermitteln Sie die Summe eines Bereichs, wenn eine Bedingung erfüllt ist. Mit =SUMMEWENNS() dagegen können Sie gleich mehrere Bedingungen aufstellen. Doch bei zahlreichen Verschachtelungen verliert man schon mal den Überblick. Wenn Sie mehrere WENN-Bedingungen einsetzen möchten, addieren Sie die Funktionen einfach: =WENN(…)+WENN() … +WENN(). Hier ist der Rückgabewert eine Zahl. Setzen Sie dagegen „&“ statt des Plus-Zeichens ein, formatiert Excel das Ergebnis als Text, auch bei Zahlen. Die Funktionen UND() und ODER() verbinden Bedingungen miteinander. Dabei müssen diese Funktionen wie in =25000;C4>=5);B4*10%;WENN(ODER(B4>=25000;C4>=5);B4*5%;0)) immer vor den Argumenten angegeben werden.

In dieser Kategorie gibt es seit Excel 2016 weitere Funktionen, die allerdings nur im Office-365-Abonnement bereitstehen. Mit WENNS() können Sie mehrere Bedingungen angeben, ohne vor lauter Verschachtelungen den Überblick zu verlieren. Die Funktion arbeitet die Bedingungen in der angegebenen Reihenfolge ab und gibt den Wert zurück, der der ersten Wahr-Bedingung entspricht. Als letzte Bedingung können Sie auch WAHR und einen Wert angeben, den die Funktion als Standard zurückgibt, wenn alle vorherigen Bedingungen FALSCH ergeben. Mit dieser Funktion können Sie bis zu 127 verschiedene Bedingungen hintereinander aufstellen, die weitaus verständlicher als ebenso viele verschachtelte WENN-Funktionen wären.

Darüber hinaus bieten sich weitere Optionen an. Die Funktion MAXWENNS() zeigt die größte Zahl aus einem bestimmten Bereich an, wenn mindestens eine Bedingung erfüllt ist. Analog dazu gibt MINWENNS() die kleinste Zahl in einem Bereich zurück, die mindestens ein Kriterium erfüllt. Auf gleiche Art und Weise setzen Sie MITTELWERTWENNS ein, um mehrere Bedingungen für den Durchschnitt zu berücksichtigen. (db@ct.de)