Grundlagen von Informationssystemen - iai.mathematik.uni ...iai.mathematik.uni-ulm.de/de/teaching/downloads/skriptOffice.pdf · Excel 2010 unterscheidet sich diesbezüglich nur marginal

Fakultät für Mathematik u. Wirtschaftswissenschaften

Institut für Angewandte Informationsverarbeitung

Grundlagen von Informationssystemen– Tabellenkalkulation: Grundlagen und fortgeschrittene Themen –

Wintersemester 2014/2015

Begleittext zu Vorlesung und Übungen

Prof. Dr. Franz Schweiggert

mit

Dipl.-WiWi Michaela Weiss

Dieser Vorlesungsbegleiter ist urheberrechtlich geschützt.Eine Weiterverbreitung – auch auf elektronischem Weg – ist ausdrücklich untersagt!

Dieser Vorlesungsbegleiter wurde von Dr. Norbert Heidenbluth im Rahmen seiner Tätigkeitam IAI erstellt!

ii

Inhaltsverzeichnis

Abkürzungsverzeichnis 1

Vorwort 3

1 Einführung 51.1 Die bekanntesten Tabellenkalkulationsprogramme . . . . . . . . . . . . . . . . . . . 5

1.1.1 Microsoft Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51.1.2 OpenOffice Calc . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61.1.3 Gemeinsamkeiten und Unterschiede zwischen Calc und Excel . . . . . . . . 71.1.4 Bezug der Programme und ihre Bedeutung für diese Veranstaltung . . . . . 7

1.2 Aufbau des Arbeitsbereichs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91.2.1 Tabellen und Tabellenblätter . . . . . . . . . . . . . . . . . . . . . . . . . . . 91.2.2 Zeilen und Spalten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101.2.3 Zellen und Bereiche . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10

1.3 Das Arbeiten mit Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121.3.1 Verschieben, Kopieren und Ausschneiden . . . . . . . . . . . . . . . . . . . . 121.3.2 Allgemeine Zelloperationen . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121.3.3 Benannte Zellen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131.3.4 Zellbezüge mit relativer Adressierung . . . . . . . . . . . . . . . . . . . . . . 141.3.5 Zellbezüge mit absoluter Adressierung . . . . . . . . . . . . . . . . . . . . . 151.3.6 Automatisches Füllen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151.3.7 Sortieren von Tabellen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161.3.8 Bedingte Formatierung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171.3.9 Ein Praxisbeispiel zur bedingten Formatierung . . . . . . . . . . . . . . . . . 19

1.4 Resumée . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

2 Funktionen 232.1 Was sind Funktionen und welche gibt es? . . . . . . . . . . . . . . . . . . . . . . . . 23

2.1.1 Menü-/Assistenten-basierte Verwendung von Formeln / Funktionen . . . 232.1.2 Manuelle Verwendung von Formeln / Funktionen . . . . . . . . . . . . . . 24

2.2 Minimum und Maximum von Zelleinträgen . . . . . . . . . . . . . . . . . . . . . . 242.3 Bildung eines Durchschnitts bzw. eines Medians . . . . . . . . . . . . . . . . . . . . 262.4 Bestimmung von Summen und Anzahlen . . . . . . . . . . . . . . . . . . . . . . . . 26

2.4.1 Summe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262.4.2 Anzahl und bedingte Anzahl . . . . . . . . . . . . . . . . . . . . . . . . . . . 27

2.5 Betrag . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282.6 Runden . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28

2.6.1 Runden via Formatierung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292.6.2 Verwendung der Funktion runden() . . . . . . . . . . . . . . . . . . . . . . 29

2.7 Suchfunktionen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302.7.1 Die Funktion vergleich() . . . . . . . . . . . . . . . . . . . . . . . . . . . 302.7.2 Die Funktion index() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31

iii

iv INHALTSVERZEICHNIS

2.7.3 Die Funktionen verweis(), sverweis() und wverweis() . . . . . . . . 322.8 Rechnen mit Datums- und Zeitangaben . . . . . . . . . . . . . . . . . . . . . . . . . 342.9 Array-Funktionen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35

2.9.1 Zur Eingabe von Array-Funktionen . . . . . . . . . . . . . . . . . . . . . . . 362.9.2 Zum Umgang mit den Resultaten von Array-Funktionen . . . . . . . . . . . 362.9.3 Zum Unterschied im Umgang mit Array-Formeln in Excel und Calc . . . . 372.9.4 Der Array-Kontext von Funktionen . . . . . . . . . . . . . . . . . . . . . . . 37

2.10 Fremdsprachige Excel-Versionen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 392.11 Resumée . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39

3 Datenimport 413.1 Überblick über die Import-Optionen . . . . . . . . . . . . . . . . . . . . . . . . . . . 413.2 Import aus dem Web . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42

3.2.1 Beispiel zum Import von Daten aus dem Web . . . . . . . . . . . . . . . . . 423.2.2 Die Besonderheit bei diesem Vorgehen . . . . . . . . . . . . . . . . . . . . . 443.2.3 Ein Wort zur Sicherheit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 453.2.4 Wann ist der Web-Import sinnvoll? . . . . . . . . . . . . . . . . . . . . . . . . 45

3.3 Import aus CSV-Dateien . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 463.3.1 Was sind CSV-Dateien? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 463.3.2 Ein formaler Blick auf CSV-Dateien . . . . . . . . . . . . . . . . . . . . . . . 463.3.3 Ein Beispiel für eine CSV-Datei . . . . . . . . . . . . . . . . . . . . . . . . . . 473.3.4 Zum Umgang mit CSV-Dateien in Excel und OpenOffice Calc . . . . . . . . 473.3.5 Export von CSV-Dateien . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493.3.6 Ein Fazit zu CSV-Dateien . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50

3.4 Import aus anderen Datenquellen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 513.5 Resumée . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51

4 Pivot-Tabellen 534.1 Wozu dienen Pivot-Tabellen? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 534.2 Datenquellen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 544.3 Voraussetzungen zum Arbeiten mit Pivot-Tabellen . . . . . . . . . . . . . . . . . . . 554.4 Drei Praxisbeispiele zu Pivot-Tabellen . . . . . . . . . . . . . . . . . . . . . . . . . . 55

4.4.1 Warum heißen Pivot-Tabellen so? . . . . . . . . . . . . . . . . . . . . . . . . 604.5 Aktualisieren von Pivot-Tabellen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 614.6 Pivot-Diagramme („Pivot-Charts“) . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62

4.6.1 Ein Beispiel zu Pivot-Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . 624.6.2 Interaktion mit Pivot-Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . 634.6.3 Die Funktion „Datenschnitt“ . . . . . . . . . . . . . . . . . . . . . . . . . . . 63

4.7 Resumée und ein Blick über den Tellerrand . . . . . . . . . . . . . . . . . . . . . . . 64

5 Der Solver 655.1 Was ist Lineare Optimierung? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65

5.1.1 Anwendungsbeispiel: Produktionsplanung . . . . . . . . . . . . . . . . . . . 665.1.2 Anwendungsbeispiel: Mischungsprobleme . . . . . . . . . . . . . . . . . . . 665.1.3 Anwendungsbeispiel: Spieltheorie . . . . . . . . . . . . . . . . . . . . . . . . 665.1.4 Anwendungsbeispiel: Logistische Probleme / Transportprobleme . . . . . . 66

5.2 Ein konkretes Beispiel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 675.3 Installation des Solvers in Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 685.4 Verwendung des Solvers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69

5.4.1 Der Dialog des Solvers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 695.4.2 Vorbereitung des Tabellenblatts . . . . . . . . . . . . . . . . . . . . . . . . . . 705.4.3 Konfiguration des Solvers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72

5.5 Parametereinstellungen im Solver . . . . . . . . . . . . . . . . . . . . . . . . . . . . 735.6 Die Grenzen des Solvers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75

INHALTSVERZEICHNIS v

5.7 Resumée . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76

Fazit 77

Anhang 77

A Funktionsnamen-Übersetzungen 79A.1 Deutsch→ Englisch . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80A.2 Englisch→ Deutsch . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84

Abbildungsverzeichnis 89

vi INHALTSVERZEICHNIS

Abkürzungsverzeichnis

CSV Comma Separated Values, auch: Character Separated Values

KDD Knowledge Discovery in Databases

LP Lineares Programm (Problemstellung im Zusammenhang mit der linearen Optimierung

OLAP Online Analytical Processing

OO OpenOffice

OR Operations Research

RFC Request for Comments

VBA Visual Basis for Applications

1

2 VORWORT

Vorwort

Vorwort zur ersten Auflage

Dieses Skript ist aus dem freiwilligen „Excel-Teil“ der Veranstaltung „Grundlagen von Infor-mationssystemen“ in den Wintersemestern 2008/2009 und 2009/2010 entstanden. Im Rahmendieses zusätzlichen Teils wurden fortgeschrittene Funktionen in den Tabellenkalkulationspro-grammen „Microsoft Excel“ bzw. „OpenOffice Calc“ vorgestellt. Die behandelten Inhalte dientendazu, gängige Tabellenkalkulationsprogramme zur Verwendung als Reporting- und Business-Intelligence-Werkzeuge vorzustellen.

Aufgrund des großen Anklangs, den dieser Teil der Veranstaltung bei den Studierenden gefun-den hat, haben wir uns entschlossen, ihn nunmehr zu einem regulären Bestandteil der Vorlesungzu machen. Das vorliegende Skript ist mithin der Begleiter für diesen Teil.

Unser herzlicher Dank geht an alle, die am Entstehen dieses Skriptes mitgewirkt haben. Dies wa-ren insbesondere Frau Irene Lebherz und Herr Andreas Lutz, die aus ihren Aufzeichnungen dasvorliegende Manuskript erstellt haben. Ferner möchten wir Frau Andrea Müller und Frau SuzanKaratas danken. Ihre im Rahmen des Seminars „Wirtschaftsinformatik“ entstandenen Ausarbei-tungen über den Solver bzw. über den Umgang mit Pivot-Tabellen haben die entsprechendenKapitel dieses Skripts nachhaltig bereichert. Für Anregungen und Fehlermeldungen zu diesemSkript sind wir natürlich immer sehr dankbar. Bitte zögern Sie nicht, uns Ihre Kommentare per-sönlich oder per E-Mail mitzuteilen.

Ulm, im Oktober 2010

Prof. Dr. Franz SchweiggertDr. Norbert Heidenbluth

Vorwort zur zweiten Auflage

Für das Wintersemester 2011/2012 wurde das Skript nochmals gründlich überarbeitet. Einerseitsbetraf dies die Gestaltung: nun fügt es sich in die Optik der übrigen Skript-Teile nahtlos ein. An-dererseits hat das Skript aber insbesondere auch inhaltlich einige Änderungen erfahren: Themen,die sich als weniger wichtig herausgestellt hatten, wurden entfernt, und stattdessen sind Erklä-rungen zu anderen Themen erweitert oder neu hinzugenommen worden. Ferner erfolgte eineUmstellung der Erläuterungen und Screenshots auf die nunmehr aktuellste Version von Micro-soft Excel (Excel 2010).

3

4 VORWORT

Es ist an dieser Stelle angebracht, nochmals auf die Intention dieses Skript- und Vorlesungsteilshinzuweisen: wir möchten ein Grundverständnis für den professionellen Einsatz von Tabellenkal-kulationsprogrammen (am Beispiel von Microsoft Excel) schaffen. Selbstverständliche Dinge wiedas Öffnen und Speichern von Dateien, das optische Arrangieren von Inhalten und ähnlicheThemen werden wir deshalb nicht behandeln. Im Fokus steht der Einsatz von Excel als „Infor-mationssystem“, und damit fügt sich dieser oftmals fälschlicherweise nur als „Excel-Kurs“ be-zeichnete Teil durchaus sinnvoll in die grundsätzliche Thematik der gesamten Lehrveranstaltungein.

Indes soll dieses Skript nicht als Ersatz für ein Buch über Excel (oder OpenOffice Calc) verstandenwerden: es ist und bleibt ein Vorlesungs- und Übungsbegleiter, der die behandelten Themen ausVorlesung und Übungen nochmals zusammenfasst. Es ist ausdrücklich nicht der Sinn hinter die-sem Skript, den Besuch von Vorlesung und Übung obsolet zu machen und zu suggerieren, sichallein aufgrund der Lektüre des Skripts alle behandelten Inhalte in vollem Umfang selbstständigerarbeiten zu können.

Der bereits im Vorwort zur ersten Auflage artikulierte Aufruf, uns Ihre Meinung zu diesem Skriptmitzuteilen, sei an dieser Stelle nochmals ausdrücklich wiederholt: Kommentare und Verbesse-rungsvorschläge sind stets willkommen! Per E-Mail oder auch persönlich nehmen wir Ihre Anre-gungen gerne entgegen. Selbst gefundene oder von Ihnen gemeldete Fehler kommunizieren wirschnellstmöglich über unsere Vorlesungshomepage, die Sie über den oben gezeigten QR-Codeerreichen können1.


Dr. Norbert Heidenbluth

Vorwort zur dritten Auflage

Es gibt immer etwas zu verbessern, manchmal auch zu „verschlimmbessern“!


Vorwort zur vierten Auflage

Es bleibt dabei!!!

Ulm, im Oktober 20131Die Klartext-URL lautet: http://iai.mathematik.uni-ulm.de/de/teaching/ws2011/glis.html.

Kapitel 1

Einführung

Tabellenkalkulationsprogramme sind üblicherweise Bestandteil von sogenannten „Office“-Pro-grammen bzw. Office-„Suiten“, also von Zusammenstellungen von Applikationen, die für typi-sche Aufgaben im Büroalltag konzipiert sind. Spreadsheet-Applikationen (so der englischsprachi-ge Begriff für Tabellenkalkulationsprogramme) dienen dabei zur Verarbeitung von Daten, die– wie der Name bereits suggeriert – in Tabellenform vorliegen. Dadurch ermöglichen sie es, dieeinzelnen Felder einer Tabelle („Zellen“ genannt) untereinander in Beziehung zu setzen, underlauben so, interaktive Berechnungen vorzunehmen.

Eine tabellarische Aufbereitung der Daten bietet sich einerseits an, weil sie uns intuitiv verständ-lich ist und wir aus dem Alltag daran gewöhnt sind. Andererseits sind Tabellen aber auch dieGrundlage von relationalen Datenbanken, auf die wir im vierten Teil dieser Lehrveranstaltungnoch intensiv eingehen werden. Tabellen sind demnach eine Form der Datenrepräsentation, diein unserer Vorlesung und den begleitenden Übungen im Mittelpunkt stehen wird.

1.1 Die bekanntesten Tabellenkalkulationsprogramme

An dieser Stelle kann durchaus die Behauptung aufgestellt werden, dass vermutlich jede(r) Teil-nehmer(in) unserer Veranstaltung bereits erste Erfahrungen mit einem Tabellenkalkulationspro-gramm gemacht hat: in der Regel werden dies Microsoft Excel, OpenOffice Calc oder ggf. auchdas Programm Numbers auf Apple-Rechnern (als Bestandteil der Software-Suite „iWork“) ge-wesen sein. Insbesondere die ersten bilden die wohl bekanntesten Programme dieses Genres, sodass wir sie hier kurz gegenüberstellen wollen:

1.1.1 Microsoft Excel

Das wohl bekannteste Tabellenkalkulationsprogramm ist Excel von Microsoft. Seine Ursprüngereichen (wenn auch damals noch unter anderem Namen) in die 1980er Jahre zurück, und aktuellliegt es in der Version “Excel 2010” vor. Dies ist die – nach Zählweise von Microsoft – 14. Version,wobei es de facto erst die 13. ist: aus Rücksicht auf die Abergläubischen unter uns wurde dieseZahl übersprungen.

5

6 KAPITEL 1. EINFÜHRUNG

Abbildung 1.1: Start von Microsoft Excel

Bedeutende und weniger bedeutende Unterschiede zwischen den Excel-Versionen

Der Sprung von Excel 2003 und seinen Vorgängern auf Excel 2007 war hinsichtlich der Bedie-nung des Programms gewaltig. Excel 2010 unterscheidet sich diesbezüglich nur marginal vonExcel 2007. In diesem Skript zeigen wir das Vorgehen anhand der neuesten Version von Excel,also anhand von Excel 2010. Aufgrund der erwähnten nur geringen Unterschiede können dieBeispiele dann auch sogleich in Excel 2007 nachvollzogen werden. In früheren Excel-Versionenstehen die hier behandelten Funktionen zwar ebenfalls zur Verfügung, allerdings müssen sie auf-grund des damaligen Bedienkonzepts anders aufgerufen werden. Abgesehen von diesem Aspektgelten die in diesem Skript gegebenen Erklärungen also auch noch in weit zurückliegenden Ver-sionsnummern des Programms.

Microsoft Excel auf Apple-Rechnern

Microsoft Excel hat seinen Ursprung auf DOS- bzw. Windows-Rechnern, und eine Unterstützungdes Apple-Betriebssystems war viele Jahre überhaupt nicht, dann für kurze Zeit rudimentär undsodann wieder gar nicht gegeben. Aktuell steht mit Office 2011 für Mac eine Portierung der Sui-te für Mac OS X bereit, die sich hinsichtlich der Bedienung aber vollkommen anders als dasWindows-Pendant verhält. Eine Kompatibilität zwischen der Windows- und der Mac-Version istzwar grundsätzlich gegeben, aber weil das Bedienkonzept so völlig anders anmutet und für denbetrieblichen Alltag ohnehin die Windows-Version die bedeutendere ist, gehen wir im Rahmendieses Skripts auf die Mac-Version von Excel nicht näher ein.

1.1.2 OpenOffice Calc

Als Alternative zum propietären und kostenpflichtigen Office-Paket von Microsoft steht die kos-tenlose Open-Source-Suite „OpenOffice“ zur Verfügung. Über viele Jahre hinweg wurde sie vondem Unternehmen Sun Microsystems (weiter-)entwickelt, aktuell liegt sie nach einer Fusion inden Händen von Oracle. Inwieweit die zukünftige Weiterentwicklung dort gesichert ist, vermagmomentan jedoch noch niemand zu prognostizieren. Auf die Existenz einiger – nicht zuletzt ausdiesem Grund – mittlerweile entwickelten Derivate von OpenOffice sei an dieser Stelle zwarhingewiesen, sie spielen aber keine weitere Rolle für unsere Veranstaltung.

Abgesehen von der Tatsache, kostenlos verfügbar zu sein, hat OpenOffice (kurz OO) noch dengroßen Vorteil, dass es plattformunabhängig und somit auf allen gängigen Betriebssystemen

1.1. DIE BEKANNTESTEN TABELLENKALKULATIONSPROGRAMME 7

Abbildung 1.2: Start von OpenOffice

lauffähig ist: ob Windows-PC, Apple-Rechner, Linux-Rechner oder Unix-Workstation: OpenOffi-ce basiert auf Java, und wenn Java auf dem Rechner bereitsteht, kann auch OpenOffice verwendetwerden.

1.1.3 Gemeinsamkeiten und Unterschiede zwischen Calc und Excel

Die Funktionsweise von OpenOffice Calc ist im Wesentlichen identisch zu Microsoft Excel. Inso-fern sind die Erklärungen in diesem Skript sowohl für Microsoft Excel als auch für OpenOfficeCalc gültig. Sofern doch Unterschiede zwischen diesen Programmen existieren, wird auf diesegesondert hingewiesen.

OpenOffice ist in der Lage, typische Dateien von Microsoftprogrammen (.doc, .xls, .ppt undweitere) zu öffnen, andersherum gilt diese Aussage jedoch (noch?) nicht. Bei den neuesten Ver-sionen von Microsoft Office kann es allerdings zu Kompatibilitätsproblemen kommen, und ins-besondere Formatierungsinformationen gehen oftmals verloren. Auch steht unter OpenOfficekein VBA zur Verfügung (auf das wir im dritten Skript-Teil noch gesondert eingehen werden).

1.1.4 Bezug der Programme und ihre Bedeutung für diese Veranstaltung

Bezug von OpenOffice

Wie bereits erwähnt, ist OpenOffice kostenlos im Internet zum Download erhältlich. Die offizielledeutschsprachige Seite findet man unter der URL http://de.openoffice.org. Es empfiehltsich, den Bezug stets über diese Seite vorzunehmen, da es in der Vergangenheit mehrfach Be-trugsfälle gab: dubiose Anbieter geben vor, den Download auch über ihre Web-Seiten zu ermög-lichen, doch im fast nicht sichtbaren „Kleingedruckten“ findet sich ein Hinweis darauf, dass manmit der Inanspruchnahme der Dienstleistung ein kostenpflichtiges Abonnement eingeht. Durchden Download der Software direkt über die offizielle Seite ist der Bezug gefahr- und kostenlosmöglich.

http://de.openoffice.org


Bezug von Microsoft Excel

Während Angehörigen der Universität Ulm (und damit insbesondere auch den Studierenden)eine Fülle von Microsoft-Software kostenlos über den KIZ-Zugang zur Verfügung steht, giltdies ausgerechnet für Microsoft Office (und damit auch für Excel) leider nicht. Lediglich dasDatenbank-Programm Access kann dort aus der Reihe der Office-Programme kostenlos bezo-gen werden. Allerdings bekommen Studierende die Office-Suite zu stark vergünstigten Preisen.

Darüberhinaus steht Excel in den Windows-Pools des KIZ selbstverständlich auch zur Verfü-gung, so dass wir die folgende Feststellung gleich zu Beginn dieses Skripts prominent heraus-stellen möchten:

Die Teilnahme an dieser Veranstaltung setzt nicht voraus, dass man Excel oder an-dere Microsoft-Programme erwirbt. Die Nachbereitung der Vorlesung bzw. Übungsowie die Bearbeitung der entsprechenden Übungsaufgaben kann durchaus an denRechnern der Windows-Pools des KIZ oder am eigenen Rechner mittels des Pro-gramms OpenOffice Calc nachvollzogen werden.

Bedeutung der beiden Office-Suiten für unsere Veranstaltung

In den vergangenen Jahren haben wir stets Excel und OpenOffice vorgeführt und im Skript er-wähnt. Es ist leicht zu erkennen, dass dies einen doppelten Zeitaufwand bedeutet, dem jedochkeinesfalls ein doppelter Nutzen entgegensteht. Denn bis auf Details unterscheiden sich die je-weiligen Funktionen gar nicht voneinander, und die eventuellen Abweichungen erschließen sichzudem in der Regel intuitiv. Deshalb haben wir uns in diesem Jahr dafür entschieden, auf Dop-pelerläuterungen zu verzichten und die somit eingesparte Zeit auf mehr Informationen zu ver-wenden.

Diese Maßnahme machte sodann aber eine weitere Entscheidung notwendig: soll unser „An-schauungsobjekt“ im Skript sowie in Vorlesung und Übung OpenOffice Calc oder Microsoft Ex-cel sein? Für OpenOffice spricht, dass es jeder auf seinem Rechner kostenlos installieren kann.Dagegen spricht jedoch, dass es in der „freien Wirtschaft“ noch keine ernstzunehmende Bedeu-tung hat: dort wird in der Regel mit Microsoft Excel gearbeitet. Im Hinblick auf eine praxisnaheAusbildung haben wir uns deshalb für Excel entschieden. Da – wie oben bereits ausgeführt – aufden Windows-Rechnern des KIZ-Pools Excel installiert ist, hat jeder Teilnehmer unserer Veran-staltung die Möglichkeit, es zu verwenden.

Somit gilt für dieses Skript Folgendes:

• Die grundsätzlichen Erklärungen beziehen sich – sofern nicht explizit anders vermerkt –stets sowohl auf Excel als auch auf Calc. Der Einfachheit halber schreiben wir nachfolgendaber nur noch Excel.

• Es wird nichts besprochen, was nicht in beiden Programmen zur Verfügung steht.

• Die Screenshots zeigen nur die deutschsprachige Excel-Version 2010.

• Sofern es eklatante Abweichungen zwischen den Programmen gibt, gehen wir auf beidegesondert ein. Im Umkehrschluss bedeutet das: wenn keine gesonderte Erklärung zu Calcgegeben wird, ist das Vorgehen dort weitgehend analog und die Abweichungen intuitiv zuverstehen.

1.2. AUFBAU DES ARBEITSBEREICHS 9

1.2 Aufbau des Arbeitsbereichs

Auch wenn wir es als Benutzer vermutlich noch nie so wahrgenommen haben: Tabellenkalkula-tionsprogramme basieren auf einer strengen Hierarchie der beteiligten Objekte. Das Objekt amuntersten Ende der Hierarchie ist eine Tabellenzelle, Spalten und Zeilen sind Mengen derartigerZellen, Tabellenblätter fassen wiederum Spalten und Zellen zusammen, und eine Arbeitsmappeist – am oberen Ende der Hierarchie – eine Menge von Tabellenblättern.

Diese Objekthierarchie hat bedeutende Vorteile, wenn wir mit der Programmiersprache VBA(die Gegenstand des dritten Teils unserer Vorlesung sein wird) arbeiten. Einstweilen haben wirjedoch noch keinen Kontakt zu VBA und stellen nachfolgend stattdessen die soeben genanntenbeteiligten Objekte zunächst einmal vor.

1.2.1 Tabellen und Tabellenblätter

In Tabellenkalkulationsprogrammen ist es bereits aufgrund der Namensgebung selbstverständ-lich, dass Tabellen die zentralen Elemente darstellen. Genau genommen ist ein Excel-Dokumenteine nicht-leere Menge von Tabellen (also von mindestens einer). Die technische Bezeichnungfür die Menge der Tabellen bzw. das Excel-Dokument lautet Arbeitsmappe. In einer Arbeitsmappebefinden sich also die einzelnen Tabellen, die in der deutschsprachigen Version auch Tabellen-blatt genannt werden. Jede Tabelle (d.h. jedes Tabellenblatt) ist eine Zusammenstellung von Zei-len und Spalten. Je nach Excel-Version ist die Anzahl der maximal verfügbaren Zeilen auf rund65.000 oder 1 Million begrenzt, und auch die maximal mögliche Anzahl für die Spalten ist limi-tiert. Wer jedoch mehr als 1 Million Zeilen in einer Excel-Tabelle verwalten möchte, sollte sichohnehin die Frage stellen, ob er tatsächlich das richtige Werkzeug benutzt, oder nicht besser aufein Datenbank-System zurückgreift.

Wenn Excel zum ersten Mal aufgerufen wird, stehen drei Tabellenblätter zur Verfügung, zwi-schen denen durch das Anklicken ihrer benannten Registerlaschen gewechselt werden kann.Diese Laschen sind am unteren linken Rand des Bildschirms zu finden. Jede neue Arbeitsmappewartet mit drei Tabellenblättern auf, die mit Tabelle1, Tabelle2 und Tabelle3 beschriftet sind (sie-he Abbildung 1.3). Das in der Abbildung ganz rechts gezeigte Symbol dient zum Einfügen einesneuen (weiteren) Tabellenblatts. Alternativ ist dies auch mit der Tastenkombination Shift+F11möglich.

Abbildung 1.3: Auflistung der Tabellenblätter einer Arbeitsmappe

Bei größeren Excel-Dokumenten mit mehreren Tabellenblättern ist es sinnvoll, die einzelnen Ta-bellenblätter mit aussagekräftigen Namen zu versehen, da sich die standardmäßig vorgenom-mene Durchnummerierung mitunter als unpraktisch erweisen kann. Mittels eines Doppelklicksauf den Tabellennamen (oder alternativ via Klick auf diesen Namen mit der rechten Maustasteund Wahl der entsprechenden Option im sich dann öffnenden Kontextmenü) kann eine Umbe-nennung erfolgen. Sie wird entweder durch Drücken der Return-Taste oder durch ein Anklickeneiner beliebigen Zelle der Tabelle abgeschlossen.


1.2.2 Zeilen und Spalten

Wie bereits erwähnt, besteht jedes einzelne Tabellenblatt aus Zeilen und Spalten. Die Spalten sindmit Buchstaben bezeichnet, die Zeilen mit Zahlen. Dadurch ergibt sich eine eindeutige Adressie-rung jeder einzelnen Zelle, die sich zuerst aus dem Buchstaben (Spalte) und dann aus der Zahl(Zeile) zusammensetzt (z.B. „C6“). Dies ermöglicht eine eindeutige Identifizierung der Tabellen-zellen. Klickt man direkt in eine Zelle, so wird im Feld am linken Rand oberhalb des Tabellen-bereichs die genaue Bezeichnung angegeben. (Im Beispiel von Abbildung 1.4 können wir diesanhand von Zelle C6 nachvollziehen.)

Abbildung 1.4: Benennung der Zellen durch eine Buchstabe-/Zahl-Kombination

Adressierung von Zellen und Bereichen auf anderen Tabellenblättern

Eine Referenzierung von Zellen oder Bereichen anderer Tabellenblätter als dem gerade aktivengelingt dadurch, dass man vor die Adresse den Namen des Tabellenblatts notiert und durch einAusrufungszeichen abschliesst.

Beispiel: Angenommen, wir haben das Tabellenblatt namens Steuersätze, dessen Zelle B2 eszu referenzieren gilt. Dann lautet die Referenz: Steuersätze!B2.

1.2.3 Zellen und Bereiche

Klickt man mit der Maus auf ein Feld (Zelle) oder navigiert man mit den Pfeil-Tasten dorthin,so wird dieses Feld markiert. Die jeweils ausgewählte Zelle ist durch einen schwarzen Randmarkiert und wird als „aktive Zelle“ bezeichnet. Auch diesbezüglich sei auf Abbildung 1.4 ver-wiesen: dort ist die Zelle C6 die aktive Zelle.

Auswahl mehrerer zusammenhängender Zellen / Bereiche

Es ist auch möglich, einen ganzen Bereich (d.h. miteinander zusammenhängende Zellen) zu mar-kieren. Klickt man in eine Zelle, hält die Maustaste gedrückt und zieht die Maus dann in einebeliebige Richtung, so wird ein (Zell-)Bereich markiert. Zellbereiche sind stets rechteckig und in-nerhalb des Rechtecks „lückenlos“. Dies erlaubt eine konsequente Referenzierung des markiertenBereiches: sie ergibt sich aus der linken oberen sowie der rechten unteren Zelle, indem die Adres-sen dieser beiden Zellen – durch einen Doppelpunkt getrennt – hintereinander notiert werden.

1.2. AUFBAU DES ARBEITSBEREICHS 11

Beispiel: Abbildung 1.5 zeigt, dass der Spaltenbereich C bis E sowie der Zeilenbereich 5 bis 9markiert sind. Daraus ergibt sich der Zellbezug C5:E9. Trotz der Markierung über einen Bereichhinweg gibt es weiterhin eine aktive Zelle: in der Abbildung ist dies die oberste linke Zelle (hier:C5).

Abbildung 1.5: Markierung eines zusammenhängenden Zellbereiches

Auswahl mehrerer nicht zusammenhängender Zellen

Mitunter kann es notwendig sein, mehrere Zellen zu markieren, die jedoch nicht zusammenhän-gen und somit keinen Bereich bilden. Das im vorigen Abschnitt erläuterte Vorgehen ist in diesemFall dann nicht anwendbar. Dennoch ist es selbstverständlich möglich, eine solche Auswahl zutreffen. Man erreicht dies, indem man die gewünschten Zellen bei gedrückter STRG-Taste nachein-ander anklickt. Erst, wenn die letzte Zelle angeklickt wurde, lässt man die STRG-Taste wiederlos. Als Zellbezug ergibt sich daraus (automatisch) eine Aufzählung der selektierten Zellen.

Beispiel: Durch das zuvor beschriebene Vorgehen haben wir in Abbildung 1.6 die Zellen B3,D5, F4 und H6 markiert. Daraus ergibt sich der Zellbezug „B3;D5;F4;H6“. Dieser ist in Zel-le C8 als Parameter für die Summations-Funktion1 angegeben worden, so dass sich deshalb derWert 100 in Zelle C8 ergeben hat.

Abbildung 1.6: Markierung mehrerer isolierter Zellen1Den Umgang mit Funktionen im Allgemeinen und mit der Funktion summe() im Besonderen erlernen wir im näch-

sten Kapitel.


1.3 Das Arbeiten mit Excel

Zu den einfachen und alltäglichen Aufgaben in Excel gehören das Verschieben, Kopieren undAusschneiden von Zellen und die Adressierung dieser Zellen.

1.3.1 Verschieben, Kopieren und Ausschneiden

Möchte man Daten aus einem bestimmten Bereich verschieben, so muss man dazu den Bereichmarkieren, in dem bis jetzt die Daten stehen, und dann mit der Maus über den schwarzen Randfahren. Wenn ein schwarzes Kreuz mit Pfeilen an allen Spitzen erscheint, kann man mit gedrück-ter linker Maustaste den Bereich verschieben.

Möchte man hingegen Zellen kopieren (d.h. zusätzlich auch an anderer Stelle einfügen), so lässtsich dies über den typischen Windows-Weg, d.h. über die Zwischenablage, erledigen. Es stehendazu die entsprechenden Symbole in der Symbolleiste sowie Menüeinträge (insbesondere imKontextmenü, das sich durch einen Klick mit der rechten Maustaste in den markierten Bereichaktivieren lässt) zur Verfügung. Wir gehen an dieser Stelle davon aus, dass dieses „Copy-and-Paste“-Prinzip hinreichend bekannt ist.

Es soll an dieser Stelle nicht unerwähnt bleiben, dass mithilfe der Zwischenabla-ge bzw. von „Copy-and-Paste“ auch das Verschieben möglich ist, so dass die obenerläuterte Variante (Verschieben unter Verwendung der Maus) nur eine (weitere)Option darstellt.

1.3.2 Allgemeine Zelloperationen

Der Hauptzweck eines Tabellenkalkulationsprogramms ist es, Berechnungen anhand der Wertein den verschiedenen Zellen und Tabellenblättern vorzunehmen. Einfache Formeln können di-rekt eingegeben werden, für komplexere Formeln steht ein Assistent zur Verfügung. Die Eingabeder Formeln kann entweder direkt in die aktive Zelle erfolgen oder alternativ in die Formel-zeile oberhalb der Tabelle (erkennbar an dem Symbol fx). Eine Formel beginnt stets mit einemGleichheitszeichen, und auf andere Zellen nimmt man durch ihre Adresse bzw. Bennung (vgl.nachfolgender Abschnitt) Bezug.

Abbildung 1.7: Eingabe einer Formel

1.3. DAS ARBEITEN MIT EXCEL 13

Beispiel: In Abbildung 1.7 erkennen wir, dass der Wert in Zelle C6 ein berechneter Wert ist,der durch die Addition der Werte in den Zellen C3 und C4 zustande kommt. Folglich wurde inZelle C6 die folgende Formel notiert: =C3+C4.

Ein unschätzbarer Vorteil der Tabellenkalkulation besteht nun darin, dass sich die Werte der be-rechneten Zellen selbstständig aktualisieren, sobald die in den Formeln referenzierten Zellen Än-derungen erfahren. Wenn wir in Zelle C3 im obigen Beispiel anstelle des vorhandenen Wertes denBetrag 800.000 e eintragen, aktualisiert sich der Wert in Zelle C6. Ein Tabellenkalkulationspro-gramm arbeitet also interaktiv.

1.3.3 Benannte Zellen

Bei umfangreicheren Tabellenblättern kann es schnell vorkommen, dass man den Überblick ver-liert, in welcher Zelle welche Werte stehen. Wenn man an ganz anderer Stelle im Tabellenblattdann auf diese Werte Bezug nehmen möchte, hat man die Zelladresse unter Umständen bereitsnicht mehr im Gedächtnis. Aus diesem Grund besteht die Möglichkeit, Zellen sowie auch ganzeZellbereiche zu benennen. Das Vorgehen zur Vergabe eines Zell- oder Bereichsnamens ist sehr ein-fach: man gibt den gewünschten Namen einfach in das Feld ein, in dem wir bislang die Adressevorfinden (links in der Leiste oberhalb der Tabelle), und bestätigt die Eingabe mit Return. Selbst-verständlich muss ein Zell- bzw. Bereichsname pro Tabellenblatt eindeutig sein. Abbildung 1.8zeigt das entsprechende Vorgehen.

Die Verwendung von benannten Zellen ist nun auch trivial: man notiert in den Formeln anstelleder Zelladressen die entsprechenden Zellnamen.

Abbildung 1.8: Vergabe von Zellnamen

Beispiel: Unter der Annahme, dass die Zelle C3 den Namen „vertriebskosten“ trägt und Zel-le C4 mit „forschungskosten“ benannt wurde, kann man die Formel in Zelle C6 nun auch wiefolgt notieren:

=vertriebskosten+forschungskosten

Es sei an dieser Stelle ausdrücklich darauf hingewiesen, dass die Benennung der Zellen natürlichunabhängig von den Inhalten der Zellen B3 und B4 erfolgen kann. Folgt man einem Benen-nungsmuster ähnlich jenem aus diesem Beispiel, so ergibt sich darauf jedoch eine sehr intuitiveArbeitsweise.


1.3.4 Zellbezüge mit relativer Adressierung

Was passiert beim Kopieren bzw. Verschieben von Zellen, die Formeln beinhalten? Diese Frageist nicht eindeutig zu beantworten, da es darauf ankommt, wie die Formeln jeweils notiert sind.

Die bislang kennengelernte Möglichkeit der Referenzierung von Zellen aufgrund ihrer Adresse(z.B. „C6“) wird als relative Adressierung bzw. relativer Zellbezug bezeichnet. Beim Kopieren derar-tiger Zellen ändern die Formeln die enthaltenen Referenzen in dem Maße, wie sie über die Zeilenund Spalten hinweg nun “relativ” zur Ursprungszelle verschoben wurden.

Beispiel: Kopiert man eine Formel, welche die Zellen A5 und C6 referenziert um drei Zeilennach unten und zwei Spalten nach rechts, so ändern sich die Zellreferenzen analog ebenfalls umdrei Zeilen (nach unten) und zwei Spalten (nach rechts), so dass sie nun auf C8 und E9 verweisen.Ob dieses Verhalten gewünscht ist oder nicht, kommt dabei stets auf die jeweilige Situation an.Wir zeigen in Abbildung 1.9 ein einfaches Beispiel und werden in den Übungen weitere Beispielebehandeln.

Abbildung 1.9: Beispiel für einen relativen Zellbezug

In dem in Abbildung 1.9 gezeigten Beispiel haben wir in Zelle D4 die folgende Formel eingege-ben:

= B4*(1+C4)

Wir berechnen also hier den Gesamtbetrag für Kunde 1, der sich aus dem Grundbetrag (ZelleB4) ergibt, indem jener um einen Risikozuschlag (ausgewiesen in Zelle C4 als prozentualer Wert)erhöht wird. Wie man dem Beispiel ansehen kann, gelten für die Kunden 2 und 3 andere Risiko-zuschläge. Es bleibt aber stets bei der Struktur aus Zeile 4: in Spalte B ist der Grundbetrag notiertund in Spalte C der jeweilige (individuelle) Risikozuschlag. Somit gilt für die Gesamtbeträge derKunden 2 und 3 dieselbe Formel wie für Kunde 1, lediglich die Zeilenreferenz muss von 4 auf 5bzw. 6 verändert werden. Genau dies erledigt Excel bei relativen Zellbezügen automatisch: dieFormel wird beim Kopieren in die Zellen D5 und D6 automatisch modifiziert: sie lautet dann=B5*(1+C5) bzw. =B6*(1+C6). Ebenso wie sich die Zeilennummer anpasst, würden sich beieinem Verschieben in horizontaler Richtung auch die Spaltenbezeichnungen anpassen.

Falls das soeben besprochene Verhalten nicht gewünscht ist, müssen wir natürlich wissen, wiewir es umgehen können. Dies ist der Gegenstand des nachstehenden Abschnitts.


1.3.5 Zellbezüge mit absoluter Adressierung

Neben der Möglichkeit der relativen Adressierung kennt Excel die Möglichkeit, Spalten, Zeilenoder ganze Zellen absolut zu adressieren: dann wird die soeben besprochene automatische Än-derung der Formeln nicht vorgenommen. Eine absolute Adressierung erkennt man am Dollar-Symbol: steht es vor der Spaltenbezeichnung („$A4“), ist die Spalte absolut adressiert, die Zeilehingegen relativ. Umgekehrt gilt bei einer Notation des Dollarzeichens vor der Zeile („A$4“),dass die Zeile absolut, die Spalte hingegen relativ adressiert ist. Ein Dollarzeichen vor Spalte undZeile („$A$4“) fixiert die konkrete Zelle. Dies ist dann von Nutzen, wenn sich in einer ausgewie-senen Zelle ein Wert befindet, auf den die Formeln Bezug nehmen sollen.

Abbildung 1.10: Beispiel für einen absoluten Zellbezug

Beispiel: Abbildung 1.10 zeigt eine Erweiterung des Beispiels zuvor: nach wie vor errechnetsich der Nettobetrag (er hieß im vorigen Beispiel „Gesamtbetrag“) aus relativen Zellbezügen, derBruttobetrag hingegen macht Gebrauch von einem Mehrwertsteuersatz, der in Zelle A10 notiertist. Folglich müssen alle Formeln auf diese Zelle verweisen, so dass eine automatische Anpassungdieser Zellreferenz nicht gewünscht ist. Somit ergibt sich für den Bruttobetrag in Zelle E4 diefolgende Formel:

= D4*(1+$A$10)

Auf diese Weise kann die Formel in die Zellen E5 und E6 kopiert werden, ohne dass die Refe-renz auf die „Mehrwertsteuer-Zelle“ fälschlicherweise angepasst wird. Die übrigen (relativen)Referenzen werden indes korrekt modifiziert.

Tip: Eine bequeme Möglichkeit, eine absolute Adressierung in eine relative umzu-wandeln oder auch den umgekehrten Weg zu beschreiten, besteht darin, die Zelle, inder die Formel enthalten ist, mit einem Doppelklick in den Bearbeitungsmodus zuversetzen. Mit der Maus kann man dann die zu ändernde Referenz markieren. Wennnun die Taste F4 mehrfach nacheinander gedrückt wird, werden alle vier Möglich-keiten der Referenzierung durchlaufen.

1.3.6 Automatisches Füllen

Um fortlaufende Nummern, Buchstaben oder andere Werte, die einer (logischen) Folge entspre-chen, nicht manuell eingeben zu müssen, kennt Excel die Funktion des automatischen Füllens


(„Autofill“). Sie funktioniert bei nahezu allen logischen Folgen, also auch bei Wochentagen, Mo-naten usw. wie folgt:

Zunächst gibt man (mindestens die ersten) zwei Werte vor, man notiert also beispielsweise in ei-ne Zelle „Montag“ und in eine darunter-, darüber- oder danebenliegende Zelle „Dienstag“. Dannwerden beide Zellen wie oben beschrieben mit der Maus markiert, man positioniert den Maus-zeiger in der unteren rechten Ecke und man wartet kurz ab, bis er sich in ein schwarzes Kreuzverwandelt hat. Sodann kann mit Drag-and-Drop in die gewünschte Richtung das automatischeFüllen zur Anwendung gebracht werden. Es funktioniert in alle vier Richtungen.

1.3.7 Sortieren von Tabellen

Oft bietet es sich an, Tabellen oder Bereiche daraus zur weiteren Bearbeitung zu sortieren. Dazuist zuerst der gesamte Bereich, der sortiert werden soll (ggf. also auch die gesamte Tabelle), zumarkieren. Danach wählt man im Ribbon in der Kategorie Daten in der Rubrik Sortieren und Fil-tern den Eintrag bzw. das Symbol Sortieren aus und gelangt in den in Abbildung 1.11 gezeigtenDialog.

Abbildung 1.11: Sortierdialog

In diesem Dialog kann man wählen, nach welcher Spalte sortiert werden soll, wonach die Sor-tierung erfolgen soll2 und ob ab- oder aufsteigend sortiert werden soll. Wenn innerhalb einerprimären Sortierung (z.B. nach Nachnamen) eine weitere Sortierung vorgenommen werden soll(z.B. nach Vornamen), so lässt sich dies über das Hinzufügen einer weiteren Ebene erreichen.Sofern beim Markieren des zu sortierenden Bereichs die Spaltenüberschriften ebenfalls markiertworden sind, ist sicherzustellen, dass der Haken bei „Daten haben Überschriften“ gesetzt ist: nurdann werden die Spalten im gezeigten Dialog auch benannt gezeigt. (Anderenfalls sieht man nurein weitgehend nichtssagendes Spalte1, Spalte2 usw.)

Beispiel: Abbildung 1.11 zeigt an, dass wir eine nach Noten aufsteigend sortierte Liste wün-schen, und innerhalb einer Note sind die Einträge nach Matrikelnummern (ebenfalls aufsteigend)geordnet.

2Zur Auswahl stehen hier: Werte, Zellenfarbe, Schriftfarbe und Zellensymbol(e). In der Regel wird wohl die Option„Werte“ diejenige sein, die für die gewünschte Sortierreihenfolge sorgt.


1.3.8 Bedingte Formatierung

Auf die vielfältigen Möglichkeiten, Tabellen optisch zu gestalten (Farbwahl, Schriftgröße, Schrift-art, Spaltenbreite / Zeilenhöhe) – d.h. zu „formatieren“ – soll an dieser Stelle nicht näher einge-gangen werden. Das entsprechende Vorgehen ist entweder ohnehin bereits bekannt, auf jedenFall aber intuitiv zu erlernen. Wir begnügen uns deshalb mit dem Verweis auf den Dialog, der inAbbildung 1.12 gezeigt ist: ihm kann man auf den ersten Blick ansehen, welche vielfältigen Mög-lichkeiten zur Formatierung einer Zelle (oder eines markierten Bereiches als Ganzes) bereit ste-hen. Der Dialog kann – nachdem die zu formatierende Zelle bzw. der zu formatierende Bereichzuvor markiert wurden – durch die Tastenkombination STRG+1 erreicht werden. Ungeachtetdessen stehen natürlich auch im „Ribbon“ entsprechende Symbole zur Formatierung zur Ver-fügung. Zur Vereinfachung besteht ebenfalls die Möglichkeit, eigene Format-Stile zu definieren:mit einem Mausklick lassen sich dann vielfältige, diesem Stil zugewiesene Einstellungen auf diemarkierte(n) Zelle(n) übertragen. Auch vorgefertigte Stile stehen im Ribbon bereits (prominenterkennbar im Eintrag Start) zur Verfügung.

Abbildung 1.12: Dialog zur Formatierung

Eine besondere Form der Formatierung wollen wir in diesem Abschnitt jedoch etwas ausführli-cher vorstellen: die bedingte Formatierung.

Wenn Excel im Berichtswesen eingesetzt werden soll, dann kann es hilfreich sein, bestimmte Wer-te in einer geeigneten Form herauszuheben. Beispielsweise könnte bei einer Tabelle mit Klausur-ergebnissen gewünscht sein, alle Klausuren, die nicht bestanden wurden, in roter Farbe zu mar-kieren. Während dies einerseits natürlich manuell vorgenommen werden kann, wäre es anderer-seits sicherlich wünschenswert, wenn Excel selbstständig nicht bestandene Klausuren erkenntund sie entsprechend optisch hervorhebt. Analog dazu kann man sich leicht deutlich komplexe-re Beispiele vorstellen, in denen eine farbliche Codierung bestimmter Wertebereiche gewünschtist.


Eine derartige automatische Formatierung ist in Excel durch die Funktion bedingte Formatierungzu erreichen, die Bestandteil der Rubrik Formatvorlagen in der Ribbon-Kategorie Start ist. DasWort „bedingt“ steht hier für eine (oder mehrere) Bedingung(en), mit denen Einfluss auf dieZellformatierung genommen wird.

Abbildung 1.13: Dialog zur bedingten Formatierung

Seit Excel 2007 ist der Funktionsumfang der bedingten Formatierung stark erweitert worden.Abbildung 1.13 gibt einen Eindruck von dieser mächtigen Funktion. Die einzelnen Optionen imDialog von Abbildung 1.13 haben die folgenden Bedeutungen:

Regeln zum Hervorheben von Zellen: Dies ist der wohl typischste Einsatzzweck für die be-dingte Formatierung: abhängig davon, ob der Wert einer Zelle ein Limit unter- oder über-schreitet oder gleich diesem Limit ist, wird die Zelle mit einer gewählten Farbe hinterlegt.Bei dieser Option kann man daher zwischen größer, kleiner, gleich, kleiner gleich usw. wäh-len, und ferner stehen auch sogleich vordefinierte Farben zur Verfügung. Als Vergleichs-wert kann wahlweise ein Wert eingegeben oder auf eine andere Zelle, in der dieser Wertsteht, verwiesen werden.

Obere / untere Regeln: Mit dieser Option markiert Excel die besten oder schlechtesten 10 Werteoder jene, die über bzw. unter dem Durchschnitt liegen.

Datenbalken: Diese Option ermöglicht es, Daten zu veranschaulichen, indem die Werte einerZelle als Balken dargestellt werden. Dabei repräsentiert ein längerer Balken einen größerenWert. Gewissermaßen erhält man hier also eine ganz simple Form eines Balkendiagrammsin einer Zelle.

Farbskalen: Auch hier kann man die Werte veranschaulichen, indem man die Werte in verschie-denen Farben einfärbt, je nach ausgewählter Farbskala in zwei- oder dreifarbigen Skalen.


Symbolsätze: Ebenso funktioniert es mit den Symbolen: je nach ausgewähltem Symbolsatz undzugewiesenem Wert wird in jeder Zelle ein entsprechendes Symbol dargestellt. Somit könn-te man beispielsweise einen „Daumen nach oben“ oder „Daumen nach unten“ anzeigenlassen, um das Bestehen oder Nichtbestehen einer Klausur zu symbolisieren. Ein ande-rer möglicher Anwendungsfall ist ein Ampelsymbol: je nachdem, ob ein Wert „im grünenBereich“ ist, diesen zu verlassen droht (gelb) oder bereits in den sprichwörtlichen „rotenBereich“ fällt, wird ein entsprechend gefärbter Kreis gezeigt.

Die soeben beschriebenen Optionen sowie das Menü stehen nur in Excel (seit Version 2007) zurVerfügung. OpenOffice Calc sowie frühere Versionen von Excel kennen hingegen nur eine ver-einfachte Form der bedingten Formatierung, die sich im Wesentlichen auf den Funktionsumfangder ersten Option (Regeln zum Hervorheben von Zellen) beschränkt. Um das generelle Vorgehenin diesem Fall auch einmal gesehen zu haben, zeigen wir nun das nachfolgende Praxisbeispiel.Es ist überdies auch für aktuelle Excel-Versionen nützlich, denn es zeigt das manuelle Vorge-hen zum Anlegen von Regeln zur bedingten Formatierung, so dass man nicht auf die soebenbeschriebenen Menüeinträge eingeschränkt bleibt.

1.3.9 Ein Praxisbeispiel zur bedingten Formatierung

Nehmen wir an, wir möchten die besagte Tabelle mit Klausurergebnissen (vgl. Abbildung 1.14)dergestalt formatieren, dass

• bestandene Klausurergebnisse in grün unterlegt sind,

• nicht bestandene Klausurergebnisse in rot markiert werden und

• mit einer 4.0 bewertete Klausuren gelb hervorgehoben sind.

Abbildung 1.14: Tabelle mit Klausurergebnissen

Wie bei allen anderen Formatierungen auch, bezieht sich eine bedingte Formatierung stets aufeine Zelle oder einen Zellbereich. Somit markieren wir nun als erstes die Zellen mit den Klausur-ergebnissen, die es automatisch zu formatieren gilt. Sodann wählen wir im Dialog zur bedingten


Abbildung 1.15: Auswahl zum Anlegen einer neuen Regel zur bedingten Formatierung

Formatierung (vgl. Abbildung 1.13) den Eintrag „Neue Regel. . .“. Dies führt zu einem neuenDialog, der in Abbildung 1.15 gezeigt ist.

Unter den vielfältigen Möglichkeiten, die hier zur Auswahl stehen, wählen wir den zweiten Ein-trag („Nur Zellen formatieren, die enthalten“) und füllen den Dialog wie in Abbildung 1.16 ge-zeigt aus. Damit haben wir nun eine Regel beschrieben: nämlich jene für die Markierung nichtbestandener Klausuren, d.h. jener Werte, die ≥ 5 sind.

Nun müssen auf die gleiche Art noch zwei weitere Regeln hinzugefügt werden: jene für dieMarkierung der mit „ausreichend“ (4.0) bewerteten Klausuren in gelb sowie der übrigen in grün.Um dies zu erreichen, wählen wir im Dialog 1.13 den letzten Eintrag („Regeln verwalten. . .“und gelangen in den in Abbildung 1.17 gezeigten Dialog. Dort kann mit jedem Klick auf dieSchaltfläche „Neue Regel. . .“ jeweils eine weitere Regel hinzugenommen werden.

Am Ende dieses Prozesses sollte der Dialog dann – wie in Abbildung 1.18 gezeigt – ausgefülltsein. Mit einem Klick auf OK beenden wir die Definition der bedingten Formatierung und kön-nen die soeben definierten Regeln nun auf unsere Daten anwenden. Das Resultat ist im Screens-hot von Abbildung 1.19 zu sehen3.

Da es sich bei der bedingten Formatierung letztlich um eine „ganz gewöhnliche“Formatierung handelt, kann sie mit Hilfe des Format-Pinsels auch auf andere Zel-len übertragen werden. Dies ist beispielsweise dann hilfreich, wenn weitere Zeilenhinzugekommen sind, die ebenfalls mit einer bereits vorhandenen bedingten For-matierung dargestellt werden sollen.

3In der schwarz-weiß gedruckten Version des Skripts sind die Farbunterschiede nur in Ansätzen aufgrund der unter-schiedlichen Grau-Schattierungen zu erkennen. Die PDF-Version dieses Skripts zeigt hingegen die farbige Variante dieserAbbildung.

1.4. RESUMÉE 21

Abbildung 1.16: Konkretes Anlegen einer neuen Regel zur bedingten Formatierung

Abbildung 1.17: Hinzufügen weiterer Regeln zur bedingten Formatierung

1.4 Resumée

Bei ganz abstrakter Betrachtung haben wir es in Tabellenkalkulationsprogrammen stets nur miteiner Menge von Zellen zu tun, die wir in irgendeiner Form miteinander in Beziehung setzenwollen. Es ist deshalb essentiell wichtig, die verschiedenen Möglichkeiten, die uns zur Adressie-rung und Referenzierung von Zellen und Zellbereichen zur Verfügung stehen, zu kennen undgut zu beherrschen.

Ein bedeutender Aspekt ist dabei der Unterschied zwischen der absoluten und der relativenAdressierung. Einen – in der Praxis die Arbeit erleichternden – zweiten Aspekt stellt die Mög-lichkeit dar, Referenzierungen über Namen statt durch die Notation von Zeile und Spalte vorzu-nehmen. Beides haben wir in diesem einführenden Kapitel nun kennengelernt.


Abbildung 1.18: Dialog nach Eingabe aller Regeln zur bedingten Formatierung

Abbildung 1.19: Resultat der bedingte Formatierung

Mit der bedingten Formatierung haben wir darüber hinaus ein erstes Beispiel für jene Gattungvon Funktionen in Excel und Calc gesehen, die ein auf den ersten Blick nicht gleich offensicht-licher Bestandteil von Tabellenkalkulationsprogrammen sind. Wenn man sich ihrer Existenz je-doch bewusst ist, wird man immer wieder auf Szenarien treffen, bei denen sie eine wertvolleUnterstützung der eigenen Arbeit darstellen.

Gerüstet mit diesem Grundwissen, werden wir uns im nächsten Kapitel nun in die Thematikeinarbeiten, die wohl als die wesentlichste von Excel und Calc angesehen werden kann: die Ver-wendung von Funktionen.

Kapitel 2

Funktionen

Der professionelle Umgang mit Tabellenkalkulationsprogrammen geht in der Regel einher mitumfangreichen Berechnungen. Zu dem Funktionsangebot von Tabellenkalkulationsprogrammengehören deshalb – neben speziellen Tools, die wir im Laufe dieses Skripts noch gesondert bespre-chen werden – auch viele vordefinierte Funktionen, um diese Berechnungen so einfach wie mög-lich zu gestalten. Wenn der Funktionsumfang, den Excel „von Haus“ aus mitbringt, nicht aus-reicht, können überdies noch eigene Funktionen mit VBA programmiert werden. Diesen Aspektbeleuchten wir im dritten Teil unserer Veranstaltung, für den es ein separates Skript geben wird.In dem vorliegenden Kapitel besprechen wir nur Funktionen, die in Excel unmittelbar enthaltensind.

2.1 Was sind Funktionen und welche gibt es?

In der Vorlesung „Einführung in die Programmierung“ haben wir am Beispiel der Program-miersprache Java den Umgang mit Methoden kennengelernt. Es handelt sich bei Methoden um„Dienstleister“, die für uns bestimmte Aufgaben erledigt (z.B. Berechnungen durchgeführt) unduns – sofern es keine void-Methoden waren – einen Rückgabewert geliefert haben. Methodenkönnen beim Aufruf einen oder mehrere Parameter erwarten, manche Methoden benötigen aberauch keinerlei eingehende Parameter von uns.

Exakt so kann man sich auch die Arbeitsweise der Funktionen in Excel vorstellen: manche be-kommen von uns einen oder mehrere Parameter übergeben (manche aber auch keinen) und lie-fern uns – oder besser: der Zelle, von der aus sie aufgerufen wurden – ein Resultat zurück.

2.1.1 Menü-/Assistenten-basierte Verwendung von Formeln / Funktionen

Die mitgelieferten Funktionen befinden sich – thematisch gruppiert – in der Funktionsbibliothekunter der Registerkarte Formeln. Sie ist in Abbildung 2.1 gezeigt. Zudem kann man im Ribbonin der Kategorie Start → Bearbeiten auf die am häufigsten verwendeten Funktionen zugreifen.Eine dritte Möglichkeit zur Notation von Funktionen liefert der Funktions-Assistent, der durchdas Symbol Funktion einfügen (siehe Symbol fx ganz links in Abbildung 2.1) aktiviert werdenkann.

Je nach Vorgehen und verwendeter Funktion erreicht man nun einen Assistenten, der sich spezi-ell auf die ausgewählte Funktion bezieht. Er beschreibt kurz die wesentliche Aufgabe der Funk-tionen sowie die Bedeutung der Parameter. Ferner unterstützt er uns bei der Eingabe der Para-meter. Abbildung 2.2 zeigt einen solchen Assistenten im Einsatz, hier am Beispiel der Funktionverweis(), die wir in Abschnitt 2.7.3 auf Seite 32 kennenlernen werden.

23

24 KAPITEL 2. FUNKTIONEN

Abbildung 2.1: Die Funktionsbibliothek von Excel

Abbildung 2.2: Ein Assistent zur Verwendung einer Funktion (hier: verweis())

2.1.2 Manuelle Verwendung von Formeln / Funktionen

Abgesehen von dieser menügestützten bzw. assistentenbasierten Variante können Funktionenauch manuell eingegeben werden. Dazu notiert man vor dem Namen der Funktion ein Gleich-heitszeichen. Sodann erkennt Excel unser Vorhaben und gibt uns durch kleine EinblendungenHilfestellung bei der Verwendung der jeweiligen Funktion.

Es würde den Rahmen dieses Skripts sprengen, auf jede in Excel integrierte Funktion einzelneinzugehen. Dies ist überdies auch gar nicht notwendig, da bereits die Online-Hilfe des Pro-gramms eine exzellente Informationsquelle für die einzelnen Funktionen darstellt. Zudem findetman natürlich auch im Internet zahlreiche ergänzende Erläuterungen. Wir beschränken uns imFolgenden deshalb darauf, nur eine grobe Kenntnis im generellen Umgang mit Funktionen zuerwerben und verweisen für weitere Informationen insbesondere auf die Online-Hilfe.

2.2 Minimum und Maximum von Zelleinträgen

Bei umfangreichen Tabellen ist es mühselig, von Hand den größten bzw. den kleinsten Zellein-trag zu finden. Daher stehen Excel die Funktionen min() bzw. max() zur Verfügung. Sie sindprinzipiell gleich aufgebaut, deshalb wird in diesem Abschnitt exemplarisch die Funktion max()vorgestellt. Die Verwendung der Funktion min() verläuft analog.

Die Funktion zum Bestimmen eines Maximums benötigt zwei Informationen: einerseits die Zel-le, in die das Ergebnis notiert werden soll, und andererseits einen Datenbereich, von dem das

2.2. MINIMUM UND MAXIMUM VON ZELLEINTRÄGEN 25

Maximum bestimmt werden soll. Deshalb klickt man in jenes Feld, in das der maximale Eintrageiner Datenmenge ausgegeben werden soll („Zielzelle“), um diese zur aktiven Zelle zu machen.

Es gibt drei verschiedene Möglichkeiten für die Angabe, aus welcher Datenbasis die Funktiondas Maximum ermitteln soll:

1. Die betreffenden Zellen können einzeln adressiert werden, indem man sie separat – jeweilsgetrennt durch ein Semikolon – notiert. Dieses Vorgehen eignet sich aber nur für sehr kleineDatenbereiche und ist deshalb nicht zu empfehlen. Wir führen es hier nur der Vollständig-keit halber auf.(Beispiel: Die Formel max(A2;D4;E6) bestimmt das Maximum der Werte in den Zellen„A2“, „D4“ und „E6“.)

2. Es kann ein Bereich in der üblichen Notation („Startzelle oben links : Endzelle unten rechts“)angegeben werden.(Beispiel: Die Formel max(A2:C10) bestimmt das Maximum in den Zeilen 2 bis 10 derSpalten „A“ bis „C“.)

3. Sobald bei Eingabe der Formel die öffnende Klammer notiert wurde, kann ein Bereich mitder Maus markiert werden. Diese Auswahl wird dann in die Formel übernommen (siehenachstehendes Beispiel).

Die hier beschriebenen Möglichkeiten zur Eingabe eines Bereiches, auf den die For-mel angewandt werden soll, gelten sinngemäß auch für alle weiteren Funktionen.

Ein Beispiel für die Verwendung der max()-Funktion finden wir in Abbildung 2.3. Sie zeigt an,dass in Zelle B12 die Formel „max(B3:B10)“ notiert ist, d.h. diese Zelle soll den maximalenWert im Bereich B3 bis B10 enthalten. Dies ist – wie wir durch einfaches Nachprüfen leicht fest-stellen können – der in Düsseldorf getätigte Umsatz, und folglich enthält die Zelle B12 den Wertdes Umsatzes aus Zelle B5.

Abbildung 2.3: Bestimmung eines Maximums mittels der Funktion max()

Es sei an dieser Stelle betont, dass im vorangegangenen Beispiel sowie in allen nochfolgenden stets ein Gleichheitszeichen vor dem Funktionsnamen notiert werdenmuss, um Excel anzuzeigen, dass es die Eingabe als Formel interpretieren soll. Wirverzichten der Einfachheit halber in den Ausführungen dieses Kapitels aber darauf.


2.3 Bildung eines Durchschnitts bzw. eines Medians

Analog zu den Funktionen min() bzw. max() werden auch jene Funktionen verwendet, diezur Berechnung des Durchschnittswerts bzw. des Median bestimmt sind. Auch sie erhalten inKlammern einen Bereich von Zellen übergeben, auf Grundlage dessen die Berechnung erfolgensoll. Die Funktionen lauten:

mittelwert(): Diese Funktion bildet den Durchschnitt eines Datenbereiches.

median(): Diese Funktion bildet den Median eines Datenbereiches1.

mittelwertwenn(): Auch diese Funktion bildet den Durchschnitt eines Datenbereiches, be-rücksichtigt dabei aber nur jene Zellen, die einem angegebenen Kriterium (z.B. > 0) genü-gen.

2.4 Bestimmung von Summen und Anzahlen

Das Bilden von Summen ist sicherlich eines der alltäglichsten Vorgehen beim Umgang mit Daten.Und auch das – auf den ersten Blick vielleicht trivial anmutende – Zählen von Datensätzen hat inkomplexeren Szenarien eine gewichtige Bedeutung. Deshalb lohnt es, sich in diesem Abschnittmit den einschlägigen Funktionen für diese Aufgaben auseinanderzusetzen.

2.4.1 Summe

Abbildung 2.4: Die Funktion AutoSumme

Zum Summieren von Werten innerhalb einesBereiches dient die Funktion summe(), die alsParameter den Bereich übergeben bekommt,dessen Inhalte sie summieren soll. Darüber-hinaus kennt Excel auch die Funktion „Au-tosumme“, die durch eine eigene Schaltflä-che in der Symbolleiste aktiviert werden kann(vgl. Abbildung 2.4). Sie sucht selbstständignach einem benachbarten Bereich, der Datenenthält, und schlägt ihn als Quelle der Datenvor, die summiert werden sollen. Damit dieseFunktion rechnen kann, muss sich die aktiveZelle (in welche die Summe geschrieben wird)nahe bei dem entsprechenden Bereich befinden. Alternativ kann man den zu summierenden Da-tenbereich vollständig mit der Maus markieren und dann auf das Symbol AutoSumme klicken.Je nachdem, ob die markierten Daten horizontal oder vertikal angeordnet sind, wird die Sum-me dann in die rechts benachbarte oder unterhalb benachbarte Zelle des markierten Bereichesgeschrieben.

1Der Median ist als der Wert definiert, der in der Mitte aller betrachteten Werte liegt (wenn sie aufsteigend sortiertsind). Er lässt sich unmittelbar bestimmen, wenn die Anzahl der betrachteten Werte ungerade ist. Sollte es sich um einegerade Anzahl von betrachteten Werten handeln, so wird der Median aus dem Mittelwert der „beiden mittleren“ Zahlenerrechnet.

2.4. BESTIMMUNG VON SUMMEN UND ANZAHLEN 27

2.4.2 Anzahl und bedingte Anzahl

Die Anzahl der Werte (unabhängig von ihren konkreten Ausprägungen) kann mit Hilfe derFunktion anzahl() berechnet werden. Sie erwartet ebenfalls einen Bereich, auf den sie ange-wendet werden soll.

Bedingte Anzahl

Oftmals kann es erwünscht sein, nicht alle Werte innerhalb eines Bereiches abzuzählen, sondernnur eine Teilmenge von ihnen, die bestimmten Kriterien genügt (z.B. „größer 50“, „kleiner 100“etc.). Um eine Zählung unter Berücksichtigung von Bedingungen vorzunehmen, steht die Funk-tion zählenwenn() zur Verfügung. Sie erwartet die zwei Parameter Bereich und Kriterien. Wäh-rend bei Bereich abermals der Datenbereich angegeben wird, auf dem die Funktion arbeiten soll,steht der Parameter Kriterien für die Angabe der Bedingungen, die erfüllt sein müssen, damit einWert gezählt wird (oder nicht).

Abbildung 2.5: Bedingtes Zählen mittels der Funktion zählenwenn()

Beispiel: Abbildung 2.5 zeigt die Verwendung der Funktion zählenwenn() im Einsatz. Er-neut verwenden wir die Datenbasis unserer fiktiven Klausurergebnisse, die wir bereits in der Ein-führung im Zusammenhang mit der bedingten Formatierung kennengelernt haben. Wir möchtennun bestimmen lassen, wieviele nicht bestandene Klausuren vorhanden sind. Dies sieht man auf-grund der bedingten Formatierung zwar auf den ersten Blick (es sind die rot unterlegten Klausu-ren), aber wir möchten diese Zahl nun von Excel ermitteln lassen. Zu diesem Zweck positionierenwir die Zellmarkierung in die Zelle C13 und notieren die folgende Formel:

zählenwenn(C4:C11;">=5")

Damit geben an, dass wir im Bereich C4 bis C11 alle diejenigen Einträge zählen lassen möchten,die größer oder gleich 5 sind. (An der Universität würde das Gleichheitszeichen reichen, aber daes bei den Schulnoten ja noch eine „6“ gibt, ist ein „größer gleich“ auch gerechtfertigt.) Das Resul-tat wird nun berechnet und kann Zelle C13 entnommen werden: es sind genau zwei Klausurennicht bestanden worden.


Bedingte Anzahl über mehrere Bereiche

Neben der Funktion zählenwenn kennt Excel auch noch die ganz ähnliche Funktion zählenwenns.Sie wird im Wesentlichen ebenso verwendet, erlaubt jedoch die Angabe mehrerer Bereiche nebstjeweiligen Kriterien.

2.5 Betrag

Aus der Mathematik ist die Betragsfunktion bekannt. Dort ist sie (für x ∈ R) wie folgt definiert:

|x| =

{x, falls x ≥ 0

−x, falls x < 0

Dieser sogenannte Absolutbetrag wird in der Regel verwendet, um Abstände zwischen zwei Punk-ten anzugeben.

In Excel steht diese Funktion unter der Bezeichnung abs() zur Verfügung. Wir erwähnen sie indiesem Skript, weil sie erstmals ein Beispiel für eine Funktion darstellt, die keinen Wertebereichübergeben bekommt, sondern einen einzelnen Wert: von diesem wird der Betrag gebildet undzurückgegeben. Als Parameter kann anstelle eines konkreten Wertes auch die (zu berechnende)Differenz zweier Werte angegeben werden, wobei es unerheblich ist, ob diese Differenz negativoder positiv ist.

Beispiel: Angenommen, die Zelle A2 enthält den Wert 7 und die Zelle B4 den Wert 10. Durchdie Eingabe der Formel =A2-B4 ergibt sich das Resultat −3. Notiert man als Formel stattdessen=abs(A2-B4), so wird 3 als Ergebnis zurückgeliefert.

2.6 Runden

Beim Durchführen von Rechenoperationen kommt es oft zu vielen Nachkommastellen. Mitunterist eine derartige Präzision aber unerwünscht, so zum Beispiel beim Rechnen mit Währungen.Möchte man daher die Beträge auf zwei Nachkommastellen runden, so stellt Excel hier zweiMöglichkeiten zur Verfügung:

• Runden via Formatierung

• Verwendung der Funktion runden()

Schauen wir uns diese beiden Möglichkeiten im Folgenden an:

2.6. RUNDEN 29

Abbildung 2.6: Runden mittels Formatierung

2.6.1 Runden via Formatierung

In Excel findet man im Ribbon unter der Kategorie Start die Rubrik Zahl. Sie weist in der obe-ren Hälfte ein Auswahlfeld und in der unteren eine Symbolleiste auf. Das Auswahlfeld ist perdefault mit dem Wert Standard belegt. Abbildung 2.6 zeigt die entsprechende Kategorie. Wirdhier im Auswahlfeld der Eintrag Währung oder Buchhaltung gewählt, so wird automatisch aufzwei Stellen gerundet und ein e-Zeichen hinter bzw. vor den Wert gesetzt. Alternativ kann manauch das erste Symbol anklicken: es entspricht der Auswahl „Währung“.

Tip: Die zuvor gezeigte Schaltfläche zum Runden von Währungs-Beträgen eignetsich ebenfalls, um eine schnelle Formatierung in andere Notationen zu erreichen. Sosteht beispielsweise die mit dem Prozentzeichen dargestellte Schaltfläche zur Ver-fügung, mit deren Hilfe der eingegebene Wert in eine Prozentzahl umgewandeltwerden kann. Desweiteren gibt es eine Schaltfläche, mit der man das Tausender-Trennzeichen ein- und ausschalten kann, sowie zwei Schaltflächen zum Hinzufügenoder Entfernen von weiteren Kommastellen.

Dieses Vorgehen ist bei Währungsangaben zwar nützlich, aber es entspricht nicht einem tatsächli-chen Runden, da wir beispielsweise auf die Anzahl der Stellen keinerlei Einfluss nehmen können.Deshalb betrachten wir nachstehend den in diesem Sinne „korrekteren“ Weg:

2.6.2 Verwendung der Funktion runden()

Eine mathematisch elegantere Lösung, um zu gerundeten Werten zu gelangen, steht mit derFunktion runden() zur Verfügung. Sie kann – wie weiter oben beschrieben – direkt als For-mel eingegeben oder über den Funktionsassistenten aufgerufen werden (in letzterem befindetsich die Funktion unter der Rubrik Mathematik & Trigonometrie → Runden). Auf die „üblicheWeise“ werden der Funktion die zu rundenden Werte übergeben, ferner erfolgt die Angabe derAnzahl der gewünschten Nachkomma-Stellen. Das führt zu einer Formel, die wie folgt aussieht:

runden(Zahl;Anzahl_Nachkommastellen)

Anstelle einer Zahl (eines Wertes) kann hier selbstverständlich wieder eine Referenz auf eineZelle angegeben werden.


Beispiel: Ein simples Beispiel möge den Einsatz der Funktion runden() motivieren. Die For-mel

runden(pi();3)

liefert als Ergebnis 3,142. Der erste Parameter (hier: der Aufruf der Funktion pi()) gibt denWert von π mit einer Genauigkeit von 15 Stellen aus2. Da wir hier an dieser Präzision nichtinteressiert sind, lassen wir die Rückgabe dieser Funktion durch die Funktion runden() auf3 Stellen runden. Dies ergibt das genannte Resultat.

2.7 Suchfunktionen

Die manuelle Suche in Excel erfolgt nicht anders, als wir es von vielen anderen Programmengewohnt sind. Doch oftmals müssen Inhalte automatisch gesucht werden, so dass die manuelleSuche nicht praktikabel ist. Aus diesem Grund wartet Excel mit einer Reihe von Suchfunktio-nen auf. Da es sich technisch gesehen eben um Funktionen handelt, ist deren Verwendung etwasgewöhnungsbedürftig. Nach dem Studium dieses Abschnitts werden das wesentliche Vorgehenund die wichtigsten Funktionen dieser Kategorie jedoch klar geworden sein.

2.7.1 Die Funktion vergleich()

Die Funktion vergleich() bietet die Möglichkeit, einen Bereich nach bestimmten Werten zudurchsuchen. Sie liefert die relative Position des zu suchenden Wertes innerhalb des Bereicheszurück und erwartet dazu die folgenden Parameter:

Suchkriterium: Der hier angegebene Wert ist das Element, nach dem in einem Bereich (siehenächster Parameter) gesucht werden soll. Hier können fixe Werte (z.B. 200 oder „Informa-tik“) oder Zellenbezüge (z.B. A2) stehen.

Suchmatrix: Dies entspricht dem Bereich, der mit der Funktion durchsucht werden soll. Dabeikönnen sowohl einzelne Zellen als auch einzelne Reihen (Zeilen) oder Spalten oder auchganze Bereiche (d.h. eine Matrix, deshalb die Bezeichnung dieses Parameters) durchsuchtwerden.

Typ: Hier sind die Werte (0,1,-1) möglich, welche für die folgenden Optionen stehen:

0: Es werden nur exakte Übereinstimmungen gefunden. Wenn der gesuchte Wert mehr-mals existiert, wird nur der Index des ersten Fundes angegeben.

1: Es wird davon ausgegangen, dass die erste Spalte der Tabelle aufsteigend sortiert ist. DieFunktion gibt den Index des letzten Wertes, der kleiner oder gleich dem Suchkriteriumist, zurück.(Anmerkung: Diese Option ist die Default-Option! Wenn bei „Typ“ nichts angegebenwird, findet sie implizit Anwendung, d.h. es wird davon ausgegangen, dass die ersteSpalte der Tabelle aufsteigend sortiert ist.)

-1: Wird diese Option verwendet, gibt die Funktion den Index des letzten Wertes, der grö-ßer oder gleich dem Suchkriterium ist, zurück. Dabei wird davon ausgegangen, dassdie erste Spalte der Tabelle absteigend sortiert ist.

Beachte: Die korrekte Funktionsweise der Funktion vergleich() hängt also da-von ab, ob die Werte in der erwarteten Sortierung vorliegen!

Sehen wir uns die Verwendung dieser Funktion anhand eines Beispiels an:2Man bemerke an dieser Stelle, dass wir damit also eine weitere Funktion kennengelernt haben: die Funktion pi().

2.7. SUCHFUNKTIONEN 31

Beispiel: Nehmen wir an, wir verwenden die Funktion vergleich() wie folgt:

vergleich(150;C10:C50)

Dieser Aufruf der Funktion sucht in den Zeilen 10 bis 50 von Spalte C nach dem Wert 150. Sobalddieser Wert gefunden wird, gibt die Funktion die Nummer der Zeile, in der der Wert gefundenwurde, zurück – allerdings relativ zur Startzeile (hier: 10). Da hier keine Angabe für den Para-meter „Typ“ erfolgte, wird die Default-Einstellung (d.h. 1) verwendet, die Werte müssen alsoaufsteigend sortiert vorliegen, damit die Funktion korrekt arbeiten kann.

2.7.2 Die Funktion index()

Die Funktion index() kann auf zwei verschiedene Arten verwendet werden: einerseits steht sieals sogenannte „Array-Funktion“ zur Verfügung, andererseits auch in der „Bezugsform“ (auch„Referenzform“ genannt). Dem Umgang mit Array-Funktionen ist Abschnitt 2.9 in diesem Kapi-tel gewidmet, so dass die nachstehende Erklärung die Bezugsform dieser Funktion erläutert.

Eine Suche führt diese Funktion zwar nicht durch, dennoch wird sie häufig in Kombination mitSuchfunktionen verwendet. Ihre Aufgabe ist es, auf relativ adressierte Zellen innerhalb einesangegebenen Bereiches zuzugreifen. Anstelle umständlicher verbaler Erläuterungen zeigen wirdie Funktionsweise an einem Beispiel:

Beispiel: Angenommen, wir notieren in einer Zelle den folgenden Funktionsaufruf:

=index(B2:D7;4;2).

Dieser Aufruf liefert – relativ zum Bereich B2 bis D7 – den Wert der vierten Zeile und der zweitenSpalte zurück. Dies entspricht demnach dem Wert in Zelle C5, denn dies ist die vierte Zeile bzw.zweite Spalte des angegebenen Bereiches.

Somit haben wir intuitiv auch die Parameter der Funktion index() kennengelernt. Der Vollstän-digkeit halber wollen wir sie jedoch auch ausformulieren. Formal ist die Signatur der Funktionindex() wie folgt definiert:

index(Bezug; Zeilennummer; Spaltennummer; Bereichsnummer)

Sehen wir uns die Bedeutung der Parameter im Einzelnen an:

Bezug: Wie gewohnt ist dies der Bereich, auf dem die Funktion arbeiten soll. Er wird entwederdirekt oder über einen Bereichsnamen eingegeben. Wenn der Bezug aus mehreren Berei-chen besteht, muss er in Klammern eingeschlossen werden.

Zeilennummer: Sie stellt den Zeilenindex des Bezugsbereiches dar, für den der Wert zurückge-geben wird. Im Falle von Null (keine bestimmte Zeile) werden alle referenzierten Zeilenzurückgegeben.

Spaltennummer: analog zu Zeilen, d.h. sie stellt den Spaltenindex des Bezugsbereiches dar, fürden der Wert zurückgegeben wird.

Bereichsnummer: Wenn der Bezug auf einen Mehrfachbereich verweist, wird hier die (laufendeNummer) des Unterbereiches angegeben.

Die Angabe von Spalten- und Bereichsnummer ist optional. Wir gehen an dieser Stelle aber nichtnäher auf die Funktionsweise von index() in dem Fall ein, dass diese Parameter nicht angege-ben werden. Dies kann bei Bedarf in der Hilfe zu dieser Funktion nachgelesen werden.


Verknüpfung der Funktionen index() und vergleich()

Die soeben kennengelernten Funktionen lassen sich auch ineinander verschachteln.

Beispiel: Angenommen, wir verwenden die folgende Formel:

index(A2:J11; vergleich(P9; H2:H11; 0); 1)

Die Verwendung der Funktion vergleich() sucht hier im Bereich H2 bis H11 nach dem Wert,der sich in Zelle P9 befindet. Da nach einem konkreten Wert gesucht wird, wird als dritter Pa-rameter 0 angegeben. Das Ergebnis, das der Aufruf dieser Funktion liefert, ist dann zugleich einEingabeparameter der Funktion index(). Somit erhalten wir – in umgangssprachlicher Formu-lierung – die folgende „Leistung“:

„Gib den Wert, der sich – bezogen auf den Bereich A2 bis J11 – in dessen erster Spalteund dessen x. Zeile befindet, wobei x durch die Funktion vergleich() gefundenwurde, aus.“

Selbstverständlich funktioniert die Verschachtelung von Funktionen nicht nur beiden hier genannten, sondern bei allen Funktionen!

2.7.3 Die Funktionen verweis(), sverweis() und wverweis()

Die Funktionen verweis(), sverweis() und wverweis() bieten – ähnlich wie die Funktionindex() – die Möglichkeit, bestimmte Zelleneinträge zu finden und Werte in anderen Zellen, dierelativ zur gefundenen Zelle adressiert sind, zurückzuliefern. Dabei stellen sverweis() undwverweis() Spezialfälle von verweis dar, die – ein Verständnis für die Funktionsweise vonverweis() vorausgesetzt – intuitiv verwendet werden können. Deshalb können wir uns andieser Stelle auf eine Erläuterung der Funktion verweis() beschränken.

Genau wie zuvor bereits bei der Funktion index() angesprochen, kennt auch verweis() zweiverschiedene Modi: den Vektor-Modus sowie den Array-Modus. Letzteren besprechen wir inAbschnitt 2.9 (siehe Seite 35). Damit gelten die folgenden Ausführungen für den Vektor-Modusder Funktion. Sie erwartet drei Parameter (von denen der letzte optional ist):

Suchkriterium: Dieser Parameter steht für den Wert, den es zu finden gilt.

Suchvektor: Dies ist der Spaltenbereich oder die einzelne Zelle, innerhalb dessen nach demSuchkriterium aus Parameter 1 gesucht wird.

Ergebnisvektor: Dieser Parameter ist optional und gibt den Bereich an, aus dem die Funktionden Ergebnisvektor beziehen soll.

Mit dem soeben erworbenen Wissen können wir nochmals einen Blick auf den Screenshot desAssistenten dieser Funktion werfen, der in Abbildung 2.2 auf Seite 24 schon einmal gezeigt wor-den ist.

2.7. SUCHFUNKTIONEN 33

Wichtig: Damit die Funktion korrekt funktioniert, ist eine aufsteigende Sortierung des Suchbe-reiches erforderlich. Diese muss also zuvor – beispielsweise anhand der in Abschnitt 1.3.7 vorge-stellten Sortiermöglichkeiten – erfolgt sein!

Ein Praxis-Beispiel zur Verwendung der Funktion verweis()

Die abstrakte Beschreibung der Funktion ist sicherlich nicht sehr hilfreich, um leicht zu verste-hen, was genau die Funktion nun wirklich macht. Deshalb demonstrieren wir ihre Verwendunganhand eines Beispiels und werden dabei erneut sehen, welches Potenzial sich in Tabellenkalku-lationsprogrammen ergibt, wenn man die verfügbaren Funktionen ineinander verschachtelt.

Erinnern wir uns für den Augenblick nochmals an das Beispiel zur Verwendung der Funktionmax() (zur Bestimmung eines Maximums in einem Bereich) und den in diesem Zusammenhanggezeigten Screenshot (siehe Abbildung 2.3 auf Seite 25). Wir haben uns „damals“ den maximalenUmsatz aus einer Reihe von Umsatzdaten verschiedener deutscher Städte durch diese Funktionliefern lassen.

Wir variieren dieses Beispiel nun ein wenig und stellen uns vor, dass wir nicht an dem Maxi-mum der Umsatzwerte, sondern am Median interessiert sind (vgl. Abschnitt 2.3 auf Seite 26).Genau genommen interessiert uns jedoch nicht der eigentliche Wert des Medians, sondern derName der Stadt, deren Umsatz am nächsten am, nicht aber oberhalb vom Median liegt. Wie kön-nen wir uns dieser Aufgabe stellen? Überlegen wir uns dazu den folgenden „Algorithmus“ zurProblemlösung:

1. Zunächst müssen wir – analog zum Beispiel in Abbildung 2.3 – den Median aller Umsatz-werte bestimmen lassen.

2. Wenn wir den Median kennen, dann können wir ihn im Datenbereich suchen lassen.

3. In diesem Beispiel haben wir eine gerade Anzahl von Werten, so dass der Median alsDurchschnitt der beiden mittleren Werte berechnet wird (vgl. Abschnitt 2.3 auf Seite 26).Somit gibt es keine Stadt, deren Umsatz exakt dem Median entspricht. Wir möchten daherdie Stadt wissen, deren Umsatz so nah wie möglich unterhalb des Medians liegt.

4. In der Spalte links von den Umsatzwerten befindet sich der Name der zugehörigen Stadt(„Zielvektor“). Somit befindet sich die Information, die wir eigentlich beziehen wollen, alsorelativ zum gefunden Wert in der unmittelbar links benachbarten Zelle.

5. Um auf in dieser Form referenzierte „benachbarte“ Zellen zuzugreifen, steht uns die Funk-tion verweis() zur Verfügung. Überdies hat sie (wie beispielsweise in der Hilfe zu die-ser Funktion nachgelesen werden kann) die gewünschte Eigenschaft, dass sie – sofern dergesuchte Wert nicht exakt gefunden werden kann – den größten Wert zurückliefert, derkleiner oder gleich dem Wert des Suchkriteriums ist.

6. Damit die Funktion verweis() korrekt funktioniert, müssen wir die (Umsatz-)Daten zu-nächst aufsteigend sortieren lassen.

Mit diesem „Algorithmus“ können wir die gestellte Aufgabe nun ganz leicht lösen. Das Resultatsowie die benötigte Formel sehen wir in Abbildung 2.7. Der besseren Lesbarkeit halber notierenwir hier nochmals die in Zelle B12verwendete Formel:

=verweis(median(B3:B10);B3:B10;A3:A10)

Man beachte hier, dass wir die Funktionen verweis() und median() ineinander verschachtelthaben und somit die Rückgabe von median() als (Eingabe-)Parameter von verweis() inter-pretiert wird.


Abbildung 2.7: Anwendung der Funktion verweis()

Und was machen jetzt die Funktionen sverweis() und wverweis()?

Diese beiden Funktionen sind – wie bereits erwähnt – jeweils nur ein Spezialfall von verweis().Die Funktion sverweis erwartet als Parameter neben dem Suchkriterium die Angabe eines Be-reiches („Matrix“) sowie die Angabe einer natürlichen Zahl („Spaltenindex“). Der Wert (lautSuchkriterium) wird in der ersten Spalte des genannten Bereiches gesucht. Der Rückgabewertergibt sich ebenfalls aus dem angegebenen Bereich, allerdings um soviele Spalten versetzt, wiedies im Parameter Spaltenindex notiert wurde. Optional kann noch angegeben werden, ob eineexakte Übereinstimmung gewünscht ist, oder das Verhalten wie oben beschrieben erfolgen soll:dann wird der größte kleinere Wert (bezogen auf das Suchkriterium) verwendet.

Ganz anlaog wird die Funktion wverweis() verwendet: sie bezieht sich allerdings nicht aufSpalten, sondern auf Zeilen. Die Notwendigkeit, dass die Werte in der ersten Spalte bzw. Zeiledes Suchbereiches aufsteigend sortiert vorliegen müssen, gilt bei beiden Funktionen übrigensebenfalls.

2.8 Rechnen mit Datums- und Zeitangaben

Excel ist in der Lage, Berechnungen mit Datumsangaben durchzuführen. Einer der einfachstenAnwendungsfälle für derartige Berechnungen ist das Notieren des aktuellen Datum in einer Zel-le. Zu diesem Zweck steht die Funktion heute() zur Verfügung. Analog gibt es auch die Funk-tion jetzt() die neben dem aktuellen Tagesdatum auch die momentane Uhrzeit ausgibt.

Bei Verwendung dieser Funktionen ist jedoch Vorsicht geboten: sie zeigen stets das aktuelle Ta-gesdatum bzw. die aktuelle Zeit, d.h. ein am heutigen Tag erstelltes Tabellenblatt mit dieser Funk-tion wird beim Öffnen in drei Tagen nicht das Datum des heutigen Tages, sondern das dann ak-tuelle Tagesdatum anzeigen. Für die Zeit gilt dies analog. Somit eignen sich diese Funktionenalso nicht, um Zeitstempel zu setzen.

Excel erlaubt zudem arithmetische Berechnungen mit Datumsangaben. Steht beispielsweise inZelle A1 ein Datum und enthält eine andere Tabelle die Formel =A1+5, so ergibt diese Formel als

2.9. ARRAY-FUNKTIONEN 35

Resultat das Datum, das 5 Tage später als jenes in Zelle A1 liegt. Ebenso lassen sich auch Datums-angaben voneinander subtrahieren: das Resultat ist die Anzahl von Tagen, die zwischen diesenDatumsangaben liegen. Entsprechende arithmetische Berechnungen sind ebenfalls für Zeitanga-ben möglich.

Zum Rechnen mit Datums- und Zeitangaben stehen über die hier genannten zwei Funktionenhinaus noch viele weitere zur Verfügung, die – wie in Einführung zu diesem Kapitel beschrie-ben – über die entsprechende Gruppierung im Formelassistenten leicht gefunden werden kön-nen. Die Benennung und Verwendung der Funktionen rund um Datums- und Zeitangaben sindrecht intuitiv gehalten, so dass bei Bedarf eine Recherche in der Online-Hilfe in der Regel schnellzu den gewünschten Informationen führt.

2.9 Array-Funktionen

Arrays sind uns aus der Vorlesung „Einführung in die Programmierung“ als Datencontainerfester Länge und festen Typs bekannt. Diese Anschauung müssen wir jedoch außer Acht lassen,wenn wir in Excel auf den Begriff „Array“ – oder präziser: „Array-Funktionen“ – treffen, dennin Excel versteht man unter dem Begriff „Array“ letztendlich einen Bereich von Zellen, der sichüber mehr als eine Zeile und/oder Spalte erstreckt.

Funktionen dieser Kategorie liefern nicht nur einen einzelnen Wert zurück, sondern mehrereWerte zugleich – mithin also ein Array von Werten. Ein solches Array kann einerseits ein Zeilen-oder Spalten-Vektor (und damit eindimensional) sein, andererseits aber auch aus einer Matrixbestehen und somit eine mehrdimensionale Ausprägung besitzen.

Abbildung 2.8: Matrix, die es zu invertieren gilt

Beispiel: Ein Beispiel für eine Funktion, die ein (mehrdimensionales) Array zurückgibt, ist dieFunktion minv(). Dies ist die Abkürzung für „Matrix Inverse“. Die Funktion bekommt einen Be-reich übergeben, der sich über mehrere Zeilen und mehrere Spalten erstreckt – also eine Matrixdarstellt – und hat zur Aufgabe, die Inverse dieser Matrix zu bestimmen3. Aus der Mathematiksind die Bedingungen bekannt, unter denen ein Invertieren einer Matrix überhaupt nur möglichist, aber diese wollen wir nun nicht näher betrachten. Wir gehen davon aus, dass die Eingabeaus einem gültigen Bereich besteht und möchten nun das Resultat der Invertierungs-Operation

3Wir setzen an dieser Stelle als bekannt voraus, dass die Inverse A−1 einer quadratischen Matrix A derart definiertist, dass ihr die Eigenschaft

A ·A−1 = A−1 ·A = E

(wobei E die Einheitsmatrix bezeichnet) zukommt.


entgegennehmen. Dazu betrachten wir die Eingabe auf dem in Abbildung 2.8 gezeigten Tabel-lenblatt.

Die gegebene Matrix befindet sich also im Bereich A2 bis C4. Inituitiv notieren wir nun die fol-gende Formel zur Verwendung der Funktion minv() in Zelle A6:

minv(A2:C4)

Doch wenn wir nun die Eingabe der Formel wie gewohnt bestätigen, erhalten wir keinesfallsdie erwartete Inverse dieser Matrix angezeigt, sondern lediglich die Zahl 3, wie Abbildung 2.9beweist. Woran liegt das? Das klären wir im folgenden Unterabschnitt!

Abbildung 2.9: Intuitive, jedoch falsche Verwendung einer Array-Funktion

2.9.1 Zur Eingabe von Array-Funktionen

Bei Formeln (Funktionen), die Arrays zurückgeben, müssen wir bei der Eingabe eine Beson-derheit beachten: die Eingabe wird nicht wie gewohnt lediglich mit der Return-Taste beendet,sondern durch Drücken der Tastenkombination STRG + Shift + Return. Da auf englischen Tasta-turen die Bezeichnung für die STRG-Taste („Steuerung“) CTRL lautet („Control“), spricht manim Zusammenhang mit den Array-Formeln auch von den CSE-Formeln (C = CTRL, S = SHIFT,E = ENTER). Auf Apple-Rechnern muss anstelle von STRG bzw. CTRL übrigens die CMD-Tasteverwendet werden. Überdies muss der gesamte Zielbereich, in den das Ergebnis notiert werdensoll, zuvor markiert werden. Unterbleibt die Eingabe mit diesem „Abschluss“, so erhält man alsResultat lediglich den Wert der ersten Zelle des eigentlich berechneten Bereiches (Arrays). Inunserem Fall war das ja die 3. Das gesamte Array – also die zu der in der obigen Abbildung ge-zeigten Matrix inverse Matrix – ergibt sich nach vorheriger Markierung des Zielbereiches sowienach korrekter Bestätigung der Formel wie in Abbildung 2.10 gezeigt.

Wenn man eine Array-Funktion wie hier vorgestellt eingegeben hat, ist dies inder Zeile, in der die Formeln dargestellt werden, zu erkennen: Array-Funktionenwerden von einem Paar geschweifter Klammern umschlossen. (Dies ist in Abbil-dung 2.10 zu erkennen.)

2.9.2 Zum Umgang mit den Resultaten von Array-Funktionen

Es ist nicht möglich, eine einzelne Zelle des zurückgegebenen Arrays zu verändern. Beim Ver-such, dies dennoch zu machen, erhält man die Fehlermeldung, die in Abbildung 2.11 gezeigt ist.Je nach verwendeter Formel muss stattdessen zunächst das gesamte Array markiert sein, um einEditieren wie gewohnt durchführen zu können.

2.9. ARRAY-FUNKTIONEN 37

Abbildung 2.10: Korrekte Verwendung einer Array-Funktion

Abbildung 2.11: Reaktion auf den Versuch, ein Array-Formel-Resultat zu verändern

2.9.3 Zum Unterschied im Umgang mit Array-Formeln in Excel und Calc

Das vorstehend Beschriebene gilt sowohl für Excel als auch für Calc. Die Einschränkung, dassder Bereich, in den das Ergebnis-Array geschrieben werden soll, zuvor markiert sein muss, giltindes nur für Excel, denn Calc belegt automatisch den benötigten Platz. Der Vorteil des Excel-Ansatzes liegt darin, dass es auf diese Weise ein Überschreiben von anderen Zellen verhindert,da der Benutzer selber zunächst den Bereich, in den geschrieben werden soll, angibt und beidieser Gelegenheit auf eventuell bereits darin vorhandene Daten aufmerksam wird.

2.9.4 Der Array-Kontext von Funktionen

Man muss nicht zwangsläufig mit Funktionen arbeiten, um eine Ausgabe mehrerer Zellen zu-gleich zu bekommen. Abbildung 2.12 zeigt, wie man auch eine „normale“ Rechenoperation alsArray-Formel verwenden kann. Man spricht in diesem Zusammenhang vom Array-Kontext ei-ner Funktion bzw. Formel. Im bisherigen Verlauf unseres Skripts haben wir bei den Funktionenindex() und verweis() bereits diese Bezeichnung zitiert: diese beiden Funktionen (wie übri-gens auch weitere) unterscheiden zwischen einer gewöhnlichen Verwendung („Vektor-Kontext“)und jener im Array-Kontext.

Den Unterschied wollen wir anhand eines Beispiels motivieren. Wir sehen in Abbildung 2.12 eineTabelle über verkaufte Essen in der Mensa.

Nun möchten wir möglichst einfach den getätigten Gesamt-Umsatz pro Essenskategorie berech-nen. Dazu muss für jede Zeile die Anzahl der verkauften Essen mit dem Preis multipliziert


Abbildung 2.12: Ausgangstabelle des Beispiels zum Array-Kontext einer Funktion

werden4. Einerseits kann man diese Aufgabe auf traditionelle Weise lösen, indem man die Be-rechnungsformel in die Summenspalte der ersten Zeile notiert und auf die anderen Zellen derSummenspalte überträgt. Mit einer Funktion, die wir im Array-Kontext notieren, geht es abernoch einfacher:

Abbildung 2.13: Verwendung einer normalen Rechenoperation im Array-Kontext

1. Der Zielbereich für die Ergebnisse wird markiert.

2. Als Formel wird das Produkt aus verkaufter Anzahl und Preis eingeben – allerdings der-gestalt, dass als Faktoren jeweils die Bereiche aller Anzahlen verkaufter Essen bzw. derenPreise notiert werden. Anstelle von B3*C3 (was ja ein Produkt berechnet), geben wir nunan: =B4:B7*C4:C7. Dies zeigt Abbildung 2.13.

3. Der Abschluss der Eingabe unserer Funktion bzw. Formel mittels der TastenkombinationCSE gibt zu erkennen, dass wir einen Array-Kontext wünschen. Deshalb erhalten wir alsResultat ein Array, dessen Inhalt in sich nicht veränderlich ist, wie wir in Abbildung 2.11bereits gesehen haben.

Die Eingabe dieser Formel (hier: nebst Bestätigung mit der CSE-Tastenkombination) ergibt dasResultat, das wir in Abbildung 2.14 zeigen.

Für das Resultat macht es in diesem Beispiel selbstverständlich keinen Unterschied,ob wir die Berechnung im Array-Kontext durchführen lassen oder nicht. Lediglichin der Handhabung bemerken wir einen Unterschied, da –wie bereits ausgeführt –die Zellen eines Resultats einer Array-Funktion nicht einzeln verändert werden kön-nen.

4Dass es in der Realität unterschiedliche Preise für Studierende, Angestellte und Gäste gibt, lassen wir in diesemBeispiel der Einfachheit halber weg.

2.10. FREMDSPRACHIGE EXCEL-VERSIONEN 39

Abbildung 2.14: Resultat unserer Berechnung im Array-Kontext

2.10 Fremdsprachige Excel-Versionen

Falls man nicht mit der deutschsprachigen Excel-Version arbeitet, hat dies zur Folge, dass sichmit der Sprache des gesamten Programms auch die Funktionsnamen ändern. Es ist klar, dassbeispielsweise die Funktion mittelwert() in nicht-deutschsprachigen Ländern völlig andersheisst. Im Einzelfall muss man sich eine geeignete Übersetzungshilfe im Internet besorgen, aberfür den wohl wahrscheinlichsten Fall, im Beruf später einmal auf eine englischsprachige Excel-Version zu treffen, haben wir im Anhang dieses Skriptes einerseits eine alphabetisch sortierte Ta-belle mit deutschen Funktionsnamen und ihren englischsprachigen Übersetzungen abgedruckt(siehe Seite 80 ff.). Andererseits befindet sich im Anhang auch eine Tabelle, die genau anders her-um funktioniert: sie listet alphabetisch sortiert die englischsprachigen Funktionsnamen auf, undwir können auf diese Weise die deutschen Bezeichnungen nachschlagen (siehe Seite 84 ff.).

2.11 Resumée

Für nahezu allen typischen „Rechen“-Aufgaben bringen die Tabellenkalkulationsprogramme dienotwendigen Funktionen mit. Seien es statistische, trigonometrische, finanzmathematische oderauch nur grundlegende Rechenoperationen: man kann sicher sein, dass die wichtigsten dieserOperationen bereits enthalten sind.

Wir können in diesem Skript nicht auf alle Funktionen eingehen und haben in diesem Kapitelnur einen Überblick darüber gegeben, wie man grundsätzlich mit Funktionen bzw. Formeln inExcel und Calc umgeht.

Eine Einzelerläuterung der Funktionen ist überdies auch gar nicht notwendig, da die Online-Hilfen von Excel und Calc derart umfangreich und mit Beispielen durchsetzt sind, dass manschnell die Antwort auf die gewünschte Frage findet. Und sollte dies einmal nicht gleich der Fallsein, so führt spätestens eine Web-Recherche zum Ziel.

Im folgenden Kapitel wollen wir uns nun der Frage widmen, wie man Daten in unsere Tabellen-kalkulationsprogramme importieren kann.


Kapitel 3

Datenimport

Um die vielfältigen Funktionen der Tabellenkalkulationsprogramme verwenden zu können, istes notwendig, die zu verarbeitenden Daten in der aktuellen Arbeitsmappe (bzw. im aktuellen Ar-beitsblatt) vorliegen zu haben. Doch wie gelangen umfangreiche Datensammlungen dort hinein?Dies beleuchten wir in diesem Kapitel.

3.1 Überblick über die Import-Optionen

Es ist einleuchtend, dass eine manuelle Eingabe von Daten, die in Excel (weiter-)verarbeitet wer-den sollen, in aller Regel aufgrund des hohen Aufwandes keine Option ist. Deshalb bietet Excelverschiedene Möglichkeiten, externe Daten zu beziehen. Der Weg zu diesen Funktionen führtüber die Ribbon-Kategorie Daten. Dort finden wir – gleich als erstes – die Rubrik Externe Da-ten abrufen (vgl. Abbildung 3.1). Diese Rubrik enthält die folgenden Optionen zum Import vonDaten aus Fremdquellen:

• Import von Daten aus Microsoft Access

• Import von Daten aus dem Web (siehe Abschnitt 3.2)

• Import von Daten aus einer Textdatei (siehe Abschnitt 3.3)

• Import aus „anderen Quellen“ (siehe Abschnitt 3.4)

Abbildung 3.1: Optionen zum Import von Daten aus Fremdquellen

41

42 KAPITEL 3. DATENIMPORT

Abgesehen von diesen „offiziellen“ Import-Optionen gibt es selbstverständlich die Möglichkeit,Daten via „Copy-&-Paste“ – also via Zwischenablage – aus Fremdanwendungen in das aktuelleArbeitsblatt zu übertragen.

Egal, für welche Variante man sich entschieden hat: wenn der Import erfolgt ist, kann das Ar-beitsblatt zukünftig mit der gewohnten Operation Speichern im Excel-Format in das Dateisystemgeschrieben werden. Fortan steht diese Datei dann auch zum „normalen“ Öffnen zur Verfügung.Der Import-Vorgang braucht dann (für diese Datenbasis) nicht mehr wiederholt zu werden.

Die erste Option der in Abbildung 3.1 gezeigten Rubrik bezieht sich auf den Import von Datenaus dem Programm Microsoft Access. Dieser ist weitgehend selbsterklärend, auf jeden Fall aberintuitiv, so dass wir deshalb nicht weiter darauf eingehen. Die übrigen Optionen stellen wir inden nachfolgenden Abschnitten etwas genauer vor.

3.2 Import aus dem Web

Einerseits ist es natürlich möglich, via „Copy-&-Paste“ Daten aus Fremdanwendungen (z.B. ei-nem Internet-Browser) nach Excel zu kopieren. Eine wesentlich elegantere Möglichkeit steht an-dererseits jedoch mit der Option Import aus dem Web zur Verfügung.

Hinter dieser Option verbirgt sich in simpler Web-Browser in Excel. Wie gewohnt gibt man inder entsprechenden Zeile die Adresse (URL) einer Webseite an und bekommt sie daraufhin an-gezeigt. Zugleich nimmt Excel aber eine Analyse der dargestellten Seite vor und untersucht, obsie Tabellen oder Daten enthält, die in tabellenähnlicher Form dargestellt werden. Sofern der-artige Strukturen gefunden werden konnten, werden sie mit einem kleinen Symbol (schwarzerPfeil auf gelbem Grund) markiert. Mit Hilfe dieses Symbols lassen sich die Tabellen dann ganzbequem nach Excel importieren.

3.2.1 Beispiel zum Import von Daten aus dem Web

Wir demonstrieren das Vorgehen hier anhand der Wikipedia-Webseite über Ulm. Schaut mansich diese in einem regulären Web-Browser an, so stellt sie sich wie in Abbildung 3.2 dar. Wir (alsMenschen) erkennen auf den ersten Blick, dass die Basisdaten über die Stadt Ulm (an der rechtenSeite des Bildschirms) in tabellarischer Form zusammengefasst sind. Diese gilt es nun, nach Excelzu importieren.

Zu diesem Zweck wählen wir nun die Option des Datenbezugs Aus dem Web aus. Es öffnetsich der besagte Miniatur-Web-Browser, und in dessen Adresszeile geben wir die URL unseresWikipedia-Eintrags über Ulm ein. Wie Abbildung 3.3 zeigt, stellt auch dieser spartanische Web-Browser die Seite korrekt dar. Ferner entnehmen wir der gezeigten Abbildung, dass Excel (bzw.dessen interner Web-Browser) die Tabelle der Basisdaten nicht korrekt erkannt hat, denn sie wur-de nicht mit dem Pfeil-Symbol markiert. Stattdessen ist lediglich die gesamte Seite als eine großeTabelle wahrgenommen worden, wie das Pfeilsymbol am äußersten oberen linken Rand zu er-kennen gibt.

Der Grund, weshalb die Tabellenerkennung zunächst nicht korrekt funktioniert hat, soll an dieserStelle nicht weiter analysiert werden. (Dazu sind Kenntnisse über die Gestaltung von Web-Seiten

3.2. IMPORT AUS DEM WEB 43

Abbildung 3.2: Wikipedia-Eintrag zu Ulm in einem gewöhnlichen Web-Browser

Abbildung 3.3: Wikipedia-Eintrag zu Ulm im Excel-internen Web-Browser


notwendig, die wir hier nicht in aller Kürze geben können). Wohl aber wollen wir uns über-legen, ob und wie sich das Problem lösen lässt. Glücklicherweise ist dies ganz einfach: oftmalsreicht es bereits aus, dass man sich – sofern vorhanden – die Druckansicht der Seite anzeigen lässt.Denn wie der Name schon aussagt: diese Version einer Internetseite ist nicht mehr für die Dar-stellung am Bildschirm optimiert. Wikipedia-Artikel lassen sich über eine entsprechende Optionin der Kategorie Drucken/exportieren (am linken Bildschirmrand) in eine Druckversion überfüh-ren. Wir klicken also im Excel-Browser auf den Link Druckversion. Nun gelingt die Analyse derSeite, und Excel erkennt korrekt die Basisdaten-Tabelle am rechten Rand (visualisiert durch dasPfeilsymbol, das nun auch am Beginn dieser Tabelle angebracht wurde).

Abbildung 3.4: Korrekte Erkennung der Tabelle in der Druckversion der Wikipedia-Seite

Wenn wir mit der Maus nun über das Pfeilsymbol an der Basisdaten-Tabelle navigieren, wirdsie mit einem blauen Rahmen versehen, und der gelbe Untergrund des Pfeilsymbols wird grün.Abbildung 3.4 zeigt dies. Durch einen Klick auf das nun grün unterlegte Pfeilsymbol wird ausdiesem ein grüner Haken. Wir haben somit die Tabelle zum Import markiert. Ein Klick auf dieSchaltfläche Importieren startet nun die Extraktion der Daten von der Webseite nach Excel. Wirbekommen noch die Möglichkeiten, den Bereich zu wählen, in den die Tabelle eingefügt werdensoll1, und verschiedene Import-Optionen durch Klicken auf Eigenschaften. . . festzulegen. (Diesebesprechen wir an dieser Stelle jedoch nicht näher). Ein Klick auf OK vollzieht den Datenim-port und schließt ihn ab. In diesem Fall haben wir die Basisdatentabelle also bequem nach Excelimportieren können.

3.2.2 Die Besonderheit bei diesem Vorgehen

Wie eingangs in diesem Abschnitt erwähnt, lassen sich derartige Datentabellen aus einem norma-len Web-Browser auch via Copy-&-Paste nach Excel importieren. Wo liegt also der Vorteil dieses„Umwegs“?

1Genau genommen geben wir hier die erste Zelle an, von der aus sich die Tabelle dann „ausbreitet“.

3.2. IMPORT AUS DEM WEB 45

Die Antwort auf diese Frage ist leicht zu geben: das Übertragen von Daten mittels „Copy-&-Paste“ führt den Datenexport einmalig aus. Die auf diese Weise importierten Daten verbleiben alsKopie in Excel und erfahren – sofern wir nicht erneut so vorgehen – nie wieder eine Aktualisie-rung.

Bei dem beschriebenen Web-Import hingegen aktualisiert Excel mitunter die Daten, indem esauf die Datenbasis erneut zugreift und die Daten dort mit den importierten Daten vergleicht undletztere ggf. (durch einen automatischen Neu-Import) auf den neuesten Stand bringt.

Diese Aktualisierung geschieht einerseits automatisch bei jedem Öffnen des Excel-Dokuments,kann insbesondere aber auch manuell vorgenommen werden, indem auf das Symbol Alle aktua-lisieren in der Rubrik Verbindungen in der Ribbon-Kategorie Daten geklickt wird. Abbildung 3.5zeigt dieses Symbol.

Abbildung 3.5: Aktualisierung von Webdaten

3.2.3 Ein Wort zur Sicherheit

Der Bezug von Daten aus dem Internet geht natürlich stets einher mit dem Risiko, auf Seiten zugelangen, die Schwachstellen in Browsern gezielt für Angriffe auf die jeweiligen Rechner nutzen.Somit besteht ein potenzielles Risiko, bei dem hier vorgestellten Import von Daten auf derartmanipulierte Seiten zu gelangen.

Damit der Benutzer nicht ohne sein Wissen diesem Risiko ausgesetzt ist, unterbindet Excel – so-fern diese Einstellung nicht vom Benutzer deaktiviert wurde – den Aufbau einer Verbindung zuexternen Quellen und zeigt beim Öffnen der Excel-Datei stattdessen den in Abbildung 3.6 gezeig-ten Sicherheitshinweis. Wenn der Benutzer nun auf Inhalt aktivieren klickt, ist ihm – hoffentlich –fortan bewusst, dass auf seinem Tabellenblatt eine Datenbasis existiert, die (zwecks Aktualisie-rung) eine Verbindung zur einer Webseite aufbauen wird. Diese Sicherheitswarnung erscheintbeim nächsten Öffnen der Excel-Datei erneut.

Abbildung 3.6: Hinweis auf die Deaktivierung der Datenverbindung

3.2.4 Wann ist der Web-Import sinnvoll?

Wir können uns nun die Frage stellen, wann das in diesem Abschnitt beschriebene Vorgehensinnvoll genutzt werden kann. Das hier gezeigte Vorgehen für den Import der Basisdaten-Tabelledes Wikipedia-Eintrags ist nur als Anschauungs-Beispiel gedacht gewesen und stellt nicht dentypischen Anwendungsfall dar. Denn einerseits ließe sich diese Tabelle via „Copy-&-Paste“ auch


manuell importieren, und andererseits unterliegt sie in aller Regel keinen ständigen Änderungen,so dass eine regelmäßige Aktualisierung der Daten nicht nötig ist.

Anders verhält es sich hingegen bei Daten, die sich permanent verändern. Als Beispiel seien hierBörsenkurse genannt. In diesem Fall ist es eine enorme Erleichterung, wenn wir auf „Knopf-druck“ jederzeit die aktuellsten Daten aus dem Internet beziehen und in unsere Berechnungenin der aktuellen Arbeitsmappe integrieren können.

3.3 Import aus CSV-Dateien

Diese oftmal unterschätzte oder gar übersehene Import-Funktionalität bedarf einer intensiverenBetrachtung, die wir in diesem Abschnitt nun vornehmen wollen. Doch bevor wir uns dem Im-port von CSV-Dateien widmen können, müssen wir zunächst einmal klären, was CSV-Dateienüberhaupt sind.

3.3.1 Was sind CSV-Dateien?

CSV steht ursprünglich für “Comma-Separated Values”, in neuerer Zeit jedoch auch für “Charac-ter Separated Values”, um nicht mehr auf das Komma fixiert zu sein, sondern ein (weitgehend)beliebiges Zeichen als Trennzeichen definieren zu können. In beiden Fällen sind simple Textda-teien gemeint, die mit einem beliebigen Editor erstellt werden können (z.B. mit dem Notepadunter Windows) und deren Inhalt einem festgelegten Aufbau folgt, der es erlaubt, derart struk-turierte Daten über verschiedene Programme hinweg auszutauschen. Ein solcher Aufbau kannwie folgt beschrieben werden:

• Jede Zeile entspricht (genau) einem Datensatz, welcher sich aus verschiedenen Informatio-nen (genannt „Felder“, sie sind vergleichbar mit Spalten) zusammensetzt.

• Die Felder sind voneinander mit einem expliziten Zeichen getrennt. Dieses Trennzeichenkann das Komma sein (dies war urspünglich namensgebend für CSV-Dateien), aber prin-zipiell ist auch jedes andere Zeichen als Trenner denkbar (deshalb auch die weichere Be-zeichnung „Character Separated Values“).

• Sofern das Trennzeichen auch als Bestandteil der Daten in Erscheinung tritt, muss der be-treffende Datensatz in doppelte Hochkommas gesetzt werden2. Darüberhinaus darf jederandere Datensatz ebenfalls in doppelten Hochkommas notiert werden.

• Eine Datei kann (theoretisch) beliebig viele Datensätze (also Zeilen) beinhalten.

3.3.2 Ein formaler Blick auf CSV-Dateien

Dieser simple Aufbau von CSV-Dateien lässt sich auch formal spezifizieren, und es gibt selbst-verständlich eine entsprechende Spezifikation. Diese ist im RFC 4180 in angereicherter Backus-Naur-Form (ABNF) durch die folgende Grammatik zusammengefasst:

2Dies nennt man auch „schützen“ oder – in einem unschönen Anglizismus – „escape-n“.

3.3. IMPORT AUS CSV-DATEIEN 47

file = [header CRLF] record *(CRLF record) [CRLF]

header = name *(COMMA name)

record = field *(COMMA field)

name = field

field = (escaped / non-escaped)

escaped = DQUOTE *(TEXTDATA / COMMA / CR / LF / 2DQUOTE) DQUOTE

non-escaped = *TEXTDATA

COMMA = %x2C

CR = %x0D ;as per section 6.1 of RFC 2234 [2]

3.3.3 Ein Beispiel für eine CSV-Datei

Ein einfaches Beispiel für eine anhand der Spezifikation erstellte CSV-Datei sieht wie folgt aus:

Matrikelnummer,Name,Studienfaecher4711,Hans Huber,Wirtschaftswissenschaften4712,Martha Meier,Mathematische Biometrie4713,Paul Fuchs,"Physik, Chemie"

Die Datei enthält drei Datensätze, die jeweils in einer eigenen Zeile notiert sind. Die erste Zeilenennt die Namen der einzelnen Felder der Datensätze, und das Komma dient als Trennzeichender Felder. Auf die permanente Verwendung von doppelten Hochkommas wird hier verzichtet,jedoch ist sie in der letzten Zeile notwendig, da hier das Komma Bestandteil des Datenfeldesist (Aufzählung mehrerer Studienfächer) und somit das gesamte Feld in doppelte Hochkommaseingeschlossen werden muss, um das Komma innerhalb des Feldes zu “schützen”.

3.3.4 Zum Umgang mit CSV-Dateien in Excel und OpenOffice Calc

Aufgrund der langen Tradition von CSV-Dateien beherrschen Tabellenkalkulationsprogrammeden Import und Export dieses Formats. Während OpenOffice Calc beim Öffnen einer CSV-Dateibereits automatisch erkennt, dass es sich um eine solche handelt, setzt dies bei Excel eine be-stimmte Benennung der Datei voraus. Das grundsätzliche Vorgehen ist bei beiden Programmenanalog, lediglich die Besonderheit der korrekten Dateiendung ist abweichend und gilt nur fürExcel. Somit können die nachfolgenden Erklärungen – mit Ausnahme dieser Besonderheit – inExcel und Calc angewendet werden.

Das Vorgehen zum Import einer Datei in der CSV-Struktur in Microsoft Excel beginnt wie dasÖffnen eines gewöhnlichen Excel-Arbeitsblattes. Um die Datei im Auswahldialog jedoch sehen


zu können, ist es notwendig, dass wir zunächst den Dateityp von der Voreinstellung (Excel-Arbeitsmappen) auf “alle Dateitypen” ändern. Nun navigieren wir im Dateisystem zu unsererCSV-Datei und öffnen sie mit einem Doppelklick. Infolgedessen startet der Import-Assistent undgibt uns zunächst die Möglichkeit, einige grundlegende Einstellungen (Textcodierung und Be-ginn der Datenzeilen) vorzunehmen (siehe Abbildung 3.7). Die Einstellung des Beginns der Da-tenzeilen ist insbesondere dann sinnvoll, wenn wir eine Datei mit Überschriften haben, da dannder eigentliche Inhalt erst beispielsweise mit Zeile 2 beginnt. Die Vorschau dient an dieser Stelleinsbesondere dazu, die korrekte Zeichencodierung (z.B. bei Umlauten und Sonderzeichen) zuverifizieren. Wenn alle Einstellungen korrekt vorgenommen wurden, bestätigen wir den erstenSchritt mit der Schaltfläche Weiter.

Abbildung 3.7: Import-Assistent – Schritt 1/3

Im zweiten Schritt (dargestellt in Abbildung 3.8) haben wir nun die Möglichkeit, alle diejenigenParameter einzustellen, die wir beim Export gesetzt haben (Feldtrennzeichen, umschliessendeAnführungszeichen / Hochkommata etc.). Indem wir die Einstellungen vornehmen, passt sichdie Vorschau an und zeigt uns nun bereits, dass die Struktur unserer CSV-Daten korrekt erkanntund umgesetzt wurde. Wenn alles nach unseren Wünschen ist, können wir auch diesen Schrittmit der Schaltfläche Weiter verlassen.

Der dritte und letzte Schritt des Assistenten gibt uns Gelegenheit, für jede erkannte Spalte einenDatentyp festzulegen oder die Auswahl beim voreingestellten Eintrag „Standard“ zu belassen.Je nach Verwendungszweck der Daten empfiehlt es sich, hier ggf. gleich den korrekten Datentypzu hinterlegen, damit spätere Berechnungen nicht zu unerwarteten Seiteneffekten führen. Abbil-dung 3.9 zeigt diesen dritten Schritt des Assistenten, den wir im Übrigen mit der SchaltflächeFertig stellen nun beenden können.

Das Resultat unserer Bemühungen ist nun eine Übernahme der in der CSV-Datei enthaltenenDatensätze in eine saubere, leicht in Excel weiterzuverarbeitende Tabellenstruktur (siehe Abbil-dung 3.10).

Achtung: Der zuvor gezeigte Import-Assistent für CSV-Dateien erscheint nicht,wenn die Dateiendung auf .csv lautet, denn dann interpretiert Excel den Inhaltselbstständig und oftmals leider falsch. Sofern das hier gezeigte, assistentenbasierteVorgehen zur Anwendung kommen soll, ist es unabdingbar, die Dateiendung zu-nächst in .txt zu ändern.

3.3. IMPORT AUS CSV-DATEIEN 49



3.3.5 Export von CSV-Dateien

Abgesehen von dem bislang kennengelernten Import von CSV-Dateien ist auch der umgekehrteWeg möglich: bestehende Tabellen können als CSV-Datei abgespeichert (“exportiert”) werden.Dazu wählt man im Dialog Speichern unter als Format die entsprechende Einstellung aus (vgl.Abbildung 3.11).

Das Prozedere ist weitgehend selbsterklärend und bedarf an dieser Stelle keiner weiteren Erläu-terungen.


Abbildung 3.10: Resultat des CSV-Imports

Abbildung 3.11: Export in eine CSV-Datei mit Microsoft Excel

3.3.6 Ein Fazit zu CSV-Dateien

CSV-Dateien sind von Aufbau und Struktur her sehr simpel und erlauben nur einfache (linea-re) Datenstrukturen und insbesondere keine Verschachtelungen. Aufgrund der systemübergrei-fenden Verbreitung und der weiten Unterstützung in jedweden Anwendungen stellen sie eineinfaches, jedoch probates Mittel für den Datenaustausch über Systemgrenzen hinweg dar.

3.4. IMPORT AUS ANDEREN DATENQUELLEN 51

3.4 Import aus anderen Datenquellen

Erinnern wir uns an dieser Stelle nochmals an Abbildung 3.1 auf Seite 41 und die darin zu erken-nende Schaltfläche zum Datenimport „aus anderen Quellen“. Wie am Symbol des kleinen, nachunten gerichteten Dreiecks zu erkennen ist, führt ein Klick darauf zu einer weiteren Auswahl.Diese ist in Abbildung 3.12 gezeigt.

Abbildung 3.12: Optionen zum Import von Daten aus externen Datenquellen

Die eingehende Betrachtung der hier zur Auswahl stehenden fünf Optionen führt leider weitüber den Rahmen dieser einführenden Vorlesung hinaus, so dass wir diese nicht näher behan-deln können. Dennoch verbergen sich hinter diesen – mit unserem derzeitigen Kenntnisstandvermutlich noch recht kryptisch anmutenden – Optionen hochinteressante Möglichkeiten, Excelals Tool für Business Intelligence-Aufgaben zu verwenden und eine Interaktion mit anderen (ex-ternen) Informationssystemen zu ermöglichen. Hier wäre – entsprechende Vorbereitungen vor-ausgesetzt – beispielsweise auch ein Zugriff auf relationale Datenbanken, denkbar, wie wir ihnim vierten Teil unserer Veranstaltung noch intensiv besprechen werden.

Bei Interesse an der Thematik, Excel als Reporting- und Business Intelligence-Tool einzusetzen,sowie am Erlernen der theoretischen Hintergründe über die hier genannten Optionen sei auf dieVorlesung „Datenmanagement“ verwiesen, die voraussichtlich im kommenden Sommerseme-ster wieder angeboten werden wird.

3.5 Resumée

Wir haben uns in diesem Kapitel auf jene Datenimport-Optionen beschränkt, die wir im Rahmenunserer Veranstaltung auch in der Praxis nutzen werden. Der triviale Import über die Zwischen-ablage, das „Durchsuchen“ von HTML-Seiten mittels des integrierten Web-Browsers sowie ins-besondere der Umgang mit CSV-Dateien: sie stellen zusammengenommen eine solide Basis fürden Alltagsgebrauch von Tabellenkalkulationsprogrammen dar.

Selbstverständlich beherrscht Excel auch den Datenimport durch unmittelbare Kommunikationmit anderen Informationssystemen der betrieblichen Infrastruktur, aber wie in Abschnitt 3.4 dar-gestellt, ist dieser Themenkomplex Gegenstand einer anderen Vorlesung und würde uns zu sehr


von unserer eigentlichen Absicht ablenken: dem Erlernen der fortgeschrittenen Funktionen vonExcel.

Deshalb schließen wir an dieser Stelle unsere Betrachtungen über den Datenimport und wendenuns im nächsten Kapitel einer oftmals unbekannten, aber ungemein leistungsfähigen Funktiona-lität zu: den Pivot-Tabellen.

Kapitel 4

Pivot-Tabellen

Tabellenkalkulationsprogramme stellen Daten stets in Form von Tabellen dar. Diese können je-doch – gerade bei großen Datenmengen– sehr schnell unübersichtlich werden. Darüberhinausbleibt trotz der vielen Daten oftmals die eigentliche Aussage der Daten verborgen: sie ergibt sicherst durch eine geeignete Aufbereitung bzw. Interpretation der Daten.

Zu diesem Zweck steht in Excel mit den sogenannten Pivot-Tabellen1 ein sehr attraktives Werk-zeug zur Verfügung, das in diesem Kapitel genauer vorgestellt werden soll.

4.1 Wozu dienen Pivot-Tabellen?

Mitunter sind es nicht die einzelnen Daten, die für eine Auswertung von Interesse sind, son-dern geeignete Zusammenfassungen (sogenannte „Verdichtungen“) dieser Daten. Die in Abbil-dung 4.1 gezeigte Tabelle soll dies illustrieren. Sie stellt die (fiktiven) Umsatzdaten einer Juwe-lierkette im vergangenen Jahr dar. Die Daten sind wie folgt zu interpretieren:

• Die Juwelierkette unterhält Filialen in den Städten Düsseldorf, Frankfurt, München undUlm.

• Die angebotenen Produkte lassen sich in die Kategorien Amulett, Armband, Kette, Ohr-stecker, Ring und Uhr einteilen.

• Jedes Produkt ist entweder aus Gold, Silber oder Bronze.

• Der Juwelier erfasst die Anzahl der verkauften Produkte auf monatlicher Basis.

• Es kann durchaus vorkommen, dass zu identischen Parametern (gleiches Produkt, gleicherMonat, gleiches Metall, gleiche Stadt) mehrere verschiedene Einträge in der Tabelle exis-tieren. In diesem Fall ergänzen sich die jeweiligen Daten (d.h. die Produktzahlen sind zukumulieren).

1Diese Bezeichnung ist in Excel geläufig. In Calc findet man dieselbe Funktionalität unter dem Begriff „Datenpilot“.

53

54 KAPITEL 4. PIVOT-TABELLEN

Abbildung 4.1: Fiktive Umsatzdaten der Juwelierkette 2010

Somit sagt Zeile 5 also aus, dass in der Filiale in Frankfurt im Juli 16 goldene Ringe verkauftworden sind. Und Zeile 11 entnehmen wir entsprechend, dass im Dezember in Düsseldorf sechssilberne Uhren umgesetzt wurden.

Informationen dieser Art sind für sich alleine genommen noch nicht sehr aussagekräftig. Interes-santer sind viel mehr Fragestellungen der folgenden Natur:

1. Wurden im gesamten Jahr mehr goldene oder mehr silberne Armbänder verkauft?

2. War der Umsatz von Ketten im Februar höher als im November?

3. Wie ist der Umsatz der Produkte “Amulett“, „Ohrstecker“ und „Uhr“ in der Filiale in Düs-seldorf im Vergleich zur Filiale in Ulm?

Es leuchtet ein, dass sich die Antworten auf Fragen dieser Art zwar aus den Daten ergeben, sie inder vorliegenden Tabelle aber keinesfalls leicht zu ermitteln und erst recht nicht abzulesen sind.Wir benötigen also eine geeignete Aufbereitung der Daten, um die gewünschten Antworten zufinden. Eine solche Aufbereitung liefert uns eine Pivot-Tabelle.

4.2 Datenquellen

Zur Datenanalyse mithilfe einer Pivot-Tabelle gibt es prinzipiell drei Optionen, um die aufzube-reitenden Daten zu beziehen:

1. Bezug aus Tabellenblättern der aktuellen Arbeitsmappe

4.3. VORAUSSETZUNGEN ZUM ARBEITEN MIT PIVOT-TABELLEN 55

2. Bezug aus einer angemeldeten Datenquelle

3. Bezug aus externen Datenquellen

Wir beschränken unsere Ausführungen auf den Bezug von Daten aus der momentan geöffnetenArbeitsmappe (d.h. auf Option 1 der vorstehenden Auflistung). Letztendlich hat es auf den Um-gang mit Pivot-Tabellen ohnehin keinen Einfluss, auf welche Weise die Daten bezogen werden.

4.3 Voraussetzungen zum Arbeiten mit Pivot-Tabellen

Ein wichtiges Kriterium zum effektiven Umgang mit Pivot-Tabellen ist das Vorliegen einer ein-deutigen Beschriftung der Spalten, denn diese Beschriftungen werden in den entsprechendenDialogen sowie insbesondere später in der Ergebnistabelle als „Erkennungsmerkmal“ der Datenverwendet.

Desweiteren dürfen keine Leerzeilen und keine leeren Spalten in den Tabellen vorhanden sein, dadie Auswertung der Daten auf einer automatischen Listenerkennung beruht, die (nur) so langefunktioniert, bis sie auf eine leere Spalte oder Zeile stößt.

4.4 Drei Praxisbeispiele zu Pivot-Tabellen

Die geforderten Voraussetzungen sind durch die Tabelle gegeben, die wir in Abbildung 4.1 aufSeite 54 bereits kennengelernt haben, so dass wir sie als Datenquelle verwenden können.

Abbildung 4.2: Symbol zum Erstellen einerPivot-Tabelle

Wir starten die Erstellung unserer Pivot-Tabelle durch einen Klick auf das Symbol Pi-votTable der Rubrik Tabellen in der Ribbon-Kategorie Einfügen (siehe Abbildung 4.2).Dies führt uns zu dem in Abbildung 4.3 ge-zeigten Dialog.

In diesem Dialog geben wir einerseits den Da-tenbereich an, aus dem die zu analysieren-den Daten bezogen werden sollen (hier wür-de alternativ auch die Möglichkeit bestehen,externe Datenquellen anzugeben), und ande-rerseits legen wir fest, wo die erstellte Pivot-Tabelle zur Anzeige gebracht werden soll. Wirhaben die Möglichkeit, sie in einem neuenoder auf dem vorhandenen Arbeitsblatt zu platzieren. Wir entscheiden uns in diesem Beispielfür die Option Neues Arbeitsblatt und bestätigen den Dialog durch einen Klick auf OK.

Wir gelangen nun zurück in unsere Arbeitsmappe, in der – gemäß unserer Auswahl – ein neuesTabellenblatt hinzugefügt wurde. Ferner sehen wir am rechten Rand ein neues Fenster, das mitPivotTable-Feldliste betitelt ist. Es dient uns im Folgenden zur Konfiguration von und Interaktionmit Pivot-Tabellen (vgl. Abbildung 4.4).

Wir beantworten nun sukzessive die auf Seite 54 gestellten Fragen mit Hilfe von Pivot-Tabellen.


Abbildung 4.3: Quelle und Ziel: Konfiguration der Pivot-Tabelle

Abbildung 4.4: Noch leere Pivot-Tabelle mit Feldliste

Pivot-Tabelle zur ersten Frage

Wir führen uns kurz nochmals vor Augen, welche Frage wir zunächst beantworten möchten:

„Wurden im gesamten Jahr mehr goldene oder mehr silberne Armbänder verkauft?“

4.4. DREI PRAXISBEISPIELE ZU PIVOT-TABELLEN 57

Um die Daten so aufzubereiten, dass sie Aufschluss hinsichtlich dieser Fragestellung geben, be-nötigen wir auf jeden Fall Daten aus der Spalte Anzahl. Deshalb kreuzen wir dieses Feld nun inder Feldliste an. Sogleich erkennt Excel, dass es sich dabei um numerische Werte handelt, undvermutet – völlig zu Recht –, dass unsere Pivot-Tabelle die Werte dieser Spalte in irgendeinerForm aggregieren (zusammenfassen) soll. Folgerichtig ordnet Excel dieses Feld unter der RubrikWerte ein und wählt – ebenfalls zu Recht – die Voreinstellung “Summe“. Dies bedeutet, dassdie Werte dieses Feldes addiert werden. Durch einen Klick auf das kleine, nach unten gerichteteDreieck und die anschließende Auswahl des Menüpunktes Wertfeldeinstellungen haben wir dieMöglichkeit, auch andere Funktionen anstelle der Summierung zu wählen. Für unseren Zweckist die Summierung aber korrekt, so dass wir keine Änderungen vornehmen.

Ein weiteres Feld, das wir für die Beantwortung unserer Frage benötigen, ist das Feld Metall. So-bald wir es ankreuzen, wird es in der Rubrik Zeilenbeschriftung eingeordnet. Aus rein optischenGründen möchten wir jedoch eine Darstellung als Spalten, so dass wir via „Drag-and-Drop“ dasFeld aus dem Bereich Zeilenbeschriftung in den Bereich Spaltenbeschriftung ziehen. Die Pivot-Tabelle passt sich daraufhin automatisch an.

Nun können wir bereits eine erste Information sehen: den Vergleich der Umsatzdaten zwischenden einzelnen Metallen. Noch sind uns aber keine Gegenüberstellungen der Städte, der Mona-te oder der Produkte ersichtlich. Für unsere Fragestellung hier benötigen wir ja die Informati-on über die verkauften Armbänder, so dass wir als drittes und letztes Feld nun noch das FeldProdukt ankreuzen. Erneut wird es automatisch als Zeilenfeld verwendet, und die Pivot-Tabellepasst sich abermals automatisch an. Nun haben wir bereits mit wenigen Mausklicks eine sehraussagekräftige Darstellung unserer Roh-Daten erreicht. Insbesondere können wir anhand die-ser Darstellung die Fragestellung nun beantworten:

Es wurden im vergangenen Jahr mehr silberne Armbänder (nämlich 5.565) als golde-ne (5.527) verkauft!

Abbildung 4.5 zeigt die soeben erstellte Pivot-Tabelle als Screenshot nebst Markierung der fürunsere erste Fragestellung relevanten Information.

Pivot-Tabelle zur zweiten Frage

Wir lernen nun die Filterfunktion bei Pivot-Tabellen kennen, indem wir – auf diesmal etwasanderem Wege – die zweite in der Einführung artikulierte Fragestellung bearbeiten:

„War der Umsatz von Ketten im Februar höher als im November?“

Analog zur Fragestellung zuvor bleibt es dabei, dass wir die Werte aus dem Feld Anzahl sum-mieren möchten. Wir benötigen jedoch nicht mehr das Feld Metall, so dass wir das Häkchendort entfernen können. Stattdessen ist nun aber das Feld Monat relevant für die Fragestellung.Deshalb kreuzen wir es an und verwenden es als Zeilenbeschriftung. Überdies bezieht sich dieFragestellung nur auf Ketten. Zwar könnten wir – wie im Beispiel zuvor – nun eine Pivot-Tabellemit allen Produkten erstellen lassen und uns beim Ablesen der Daten auf die relevante Zeilebzw. Spalte mit Informationen über Ketten beschränken. Wir möchten dieses Beispiel jedoch da-zu nutzen, die Filterfunktion kennenzulernen. Deshalb ziehen wir das Feld Produkt nun in denBereich Bereichsfilter. Dies hat zu Folge, dass wir vor dem Beginn unserer Pivot-Tabelle ein Drop-Down-Menü erhalten, in dem wir nach einem Produkt filtern können. Die in der Pivot-Tabelleaggregierten Daten beziehen sich dann also nur auf das dort ausgewählte Produkt. In unseremFall wählen wir als Produktfilter deshalb „Kette“ und erhalten die in Abbildung 4.6 gezeigte neuePivot-Tabelle. Ihr können wir die Antwort auf die Frage nun unmittelbar entnehmen:


Abbildung 4.5: Pivot-Tabelle zur Beantwortung von Frage 1

Der Umsatz von Ketten war im Februar nicht höher als im November, da im Februar1.159 Ketten umgesetzt wurden, im November hingegen 1.449.

In Excel 2010 (nicht jedoch in früheren Versionen und auch nicht in OpenOffice Calc) gibt esüberdies noch eine weitere Filterfunktion, den sogenannten Datenschnitt. Wir werden ihn späterin diesem Kapitel vorstellen (vgl. Abschnitt 4.6.3 auf Seite 63).

Pivot-Tabelle zur dritten Frage

Die dritte auf Seite 54 gestellte Frage lautete:

Wie ist der Umsatz der Produkte “Amulett“, „Ohrstecker“ und „Uhr“ in der Filiale inDüsseldorf im Vergleich zur Filiale in Ulm?

Das Vorgehen zur Beantwortung dieser Frage sollte nun im Prinzip bekannt sein: wir könntenbeispielsweise so vorgehen, wie bei Frage 1 zuvor auch. Um eine Alternative kennenzulernen,variieren wir unsere Arbeitsweise nun jedoch ein wenig.

Erneut benötigen wir die Summierung der Anzahl, relevant sind diesmal die Felder Produkt undStadt. Anstelle der Verwendung eines Filters oder einer Aufteilung in Zeilen und Spalten lassen

4.4. DREI PRAXISBEISPIELE ZU PIVOT-TABELLEN 59


wir diesmal jedoch beide Felder als Zeilen darstellen. Der Bereich Spaltenbeschriftungen bleibtalso leer. Auf diese Weise erhalten wir eine verschachtelte bzw. hierarchisch aufgebaute Pivot-Tabelle: zunächst gruppieren wir die Informationen nach Stadt, und pro Stadt gruppieren wirsie nach Produkt. Um dies zu erreichen, können wir diese Reihenfolge – sollte sie nicht bereitsvorliegen – manuell erzwingen: durch ein „Drag-and-Drop“ der Feldnamen im Bereich Zeilen-beschriftung kann die Hierarchie variiert werden. Ein Herumdrehen dieser Felder würde danndazu führen, dass zunächst nach Produkt und dann pro Produkt nach Stadt sortiert wird.

Da wir nur an den Städten Düsseldorf und Ulm und nur an den Produkten Amulett, Ohrsteckerund Uhr interessiert sind, müssen wir unsere Resultate in der Pivot-Tabelle wieder filtern lassen.Diesmal verwenden wir dazu nicht das Feld Bereichsfilter, sondern gehen anders vor: wenn wirüber angekreuzte Felder in der Feldliste mit der Maus navigieren und dann das kleine schwarze,nach unten gerichtete Dreieck anklicken, so bekommen wir die Möglichkeit, einzelne Werte (odernatürlich alle) anzeigen zu lassen. Abbildung 4.7 zeigt dies am Beispiel des hier gewünschtenFilters im Feld Produkt: wir möchten nur Informationen über Amulette, Ohrstecker und Uhrenerhalten. (Das Produkt „Armband“ haben wir soeben deaktiviert, wie der Abbildung zu entneh-men ist.) Analog können wir auch nach Städten filtern und nur Düsseldorf und Ulm berücksich-tigen lassen.

Die Antwort auf Frage 3 ergibt sich nun durch Ablesen der in Abbildung 4.8 gezeigten Pivot-Tabelle:

Insgesamt war der Umsatz über alle drei Produkte in Ulm (11.920) besser als in Düs-seldorf (11.652). In Düsseldorf wurden jedoch mehr Amuletts (4.655) verkauft als inUlm (4.258), wohingegen Ohrstecker und Uhren häufiger in Ulm verkauft wurden.


Abbildung 4.7: Pivot-Tabellen: Filterfunktion via Feldliste

Die Reihenfolge der Gruppierung der Zeilen hat selbstverständlich keinen Einfluss auf die Ant-wort auf die von uns gesuchte Frage. Zwar erhalten wir eine unterschiedliche Darstellung derDaten, die letztendlich auf die Antwort führen, aber das Resultat bleibt natürlich identisch. Dieskann leicht anhand von Abbildung 4.9 nachvollzogen werden: sie zeigt die Pivot-Tabelle, diesich zur zuvor gezeigten Version lediglich durch die Vertauschung der Reihenfolge unterschei-det. Dies erlaubt eine andere Sicht auf die Daten, die aggregierten Werte (aufgrund derer wirunsere Frage ja beantworten) bleiben jedoch identisch.

4.4.1 Warum heißen Pivot-Tabellen so?

Mit dem soeben nachvollzogenen Beispiel zur Beantwortung von Frage 3 können wir nun auchklären, wie Pivot-Tabellen zu ihrem Namen kommen: wir haben ein Feld, das Ausgangsbasis fürunsere Darstellung ist. In Abbildung 4.8 war dies das Feld Stadt, in Abbildung 4.9 hingegen dasFeld Produkt. Dieses „Ausgangselement“ ist das „Pivot-Element“, und somit gelangen wir zueiner Erklärung für den Namensursprung von Pivot-Tabellen.

Das Wort „Pivot“ ist französischen Ursprungs und wird im Lexikon u.a. mit „Drehachse“ und„Hauptstütze“ übersetzt. Diese Übersetzung ergibt auch in unserem Kontext ja durchaus Sinn.

Pivot-Elemente kennen wir auch aus der Mathematik: Algorithmen wie beispielsweise das Gauß’scheEliminationsverfahren, der Suchalgorithmus Quicksort sowie das aus der linearen Optimierungbekannte Simplex-Verfahren verwenden ebenfalls explizite Startwerte – eben: Pivot-Elemente.

4.5. AKTUALISIEREN VON PIVOT-TABELLEN 61


4.5 Aktualisieren von Pivot-Tabellen

Änderungen an den einer Pivot-Tabelle zugrunde liegenden Daten wirken sich nicht automa-tisch auf diese aus. Selbstverständlich ist es aber auch nicht notwendig, nach jeder Änderungder Quelldaten eine neue Pivot-Tabelle von Grund auf zu erstellen. Stattdessen hilft abermals dasSymbol Aktualisieren der Rubrik Verbindungen in der Ribbon-Kategorie Daten, das wir bereitsin Abbildung 3.5 auf Seite 45 in einem völlig anderen Zusammenhang kennengelernt haben. EinKlick auf dieses Symbol führt dazu, dass die aggregierten Werte einer bestehenden Pivot-Tabelleneu berechnet werden und die gesamte Pivot-Tabelle so zu einer Aktualisierung gelangt.

Mit anderen Worten: nach jeder Änderung an der Datenbasis kann bzw. muss eine bestehendePivot-Tabelle durch das beschriebene Symbol aktualisiert werden. Lediglich eine automatischeAktualisierung nach jeder Änderung ist (vermutlich aus Performance-Gründen) nicht möglich.

Diese Aktualisierung gelingt indes nur korrekt, wenn zwischenzeitlich nicht weitere Spalten oderZeilen zur Datenbasis hinzugekommen sind. Ist dies der Fall, so muss in der Tat eine neue Pivot-Tabelle erstellt werden.

Tip: Alternativ zum beschriebenen Vorgehen kann auch mit der rechten Maustastein eine bestehende Pivot-Tabelle geklickt werden. In dem sich daraufhin öffnendenKontext-Menü steht die Option Aktualisieren ebenfalls zur Verfügung.


Abbildung 4.9: Variation zur Pivot-Tabelle aus Abbildung 4.9 (Vertauschung der Reihenfolge)

4.6 Pivot-Diagramme („Pivot-Charts“)

Analog zum Erstellen von Pivot-Tabellen bietet Excel auch die Möglichkeit, die aggregierten Da-ten in Form von Diagrammen („Charts“) darzustellen. Das dazu notwendige Vorgehen weichtnicht maßgeblich von jenem für Pivot-Tabellen ab, so dass wir hierauf nicht näher eingehen brau-chen.

Lediglich eine kleine Besonderheit sei erwähnt: anstelle der Rubriken Spaltenbeschriftung undZeilenbeschriftung haben wir nun die Rubriken Achsenfelder und Legendenfelder zur Verfü-gung.

4.6.1 Ein Beispiel zu Pivot-Charts

Da für die Datenvisualisierung mit Hilfe von Tabellen andere „Spielregeln“ gelten als für jene mitHilfe von Diagrammen, ist in der Regel davon abzuraten, mehrere Achsenfelder zu verwenden(dies entspräche der Hierarchie, die wir bei der Beantwortung von Frage 3 kennengelernt haben),da auf diese Weise schnell unübersichtliche Diagramme entstehen würden. Stattdessen ist essinnvoll, genau ein Achsen- und genau ein Legendenfeld zu verwenden.

Abbildung 4.10 stellt zwei Pivot-Charts gegenüber: sie summieren abermals die Umsatzdatenund vergleichen den Umsatz der Metalle in den einzelnen Städten. Je nach Verwendung derFelder Stadt und Metall als Legenden- oder als Achsenfeld erhalten wir die in der Abbildunggezeigten verschiedenen Diagramme.

4.6. PIVOT-DIAGRAMME („PIVOT-CHARTS“) 63

Abbildung 4.10: Pivot-Charts mit vertauschten Achsen-/Legendenfeldern

4.6.2 Interaktion mit Pivot-Charts

Abgesehen davon, dass sich die erstellten Pivot-Charts selbstverständlich problemlos zum Ge-brauch in Präsentationsprogrammen (wie beispielsweise Microsoft PowerPoint) exportieren las-sen, haben sie den Reiz, interaktiv zu sein. Das heisst, jede Änderung in der Pivot-Tabellen-Feldliste wirkt sich unmittelbar („on-the-fly“) auf das Diagramm aus. Für Analysezwecke ist dieseInteraktion ungemein hilfreich.

Ferner sind die Achsen- und Legendenfelder als Drop-Down-Listen konzipiert, in denen wir be-quem Filterungen vornehmen können. Dies lässt sich anhand von Abbildung 4.10 gut erkennen.

Eine weitere Möglichkeit, Daten zu filtern, steht überdies mit der Funktion „Datenschnitt“ zurVerfügung, die wir im nächsten Abschnitt vorstellen.

4.6.3 Die Funktion „Datenschnitt“

Excel bietet seit Version 2010 die Funktion „Datenschnitt“ an, aktuelle OpenOffice Calc- sowiefrühere Excel-Versionen allerdings nicht. Wir erreichen diese Funktion über die Ribbon-KategoriePivotChart-Tools / Analyse2 in der Rubrik Daten. Wir bekommen beim Klicken auf das SymbolDatenschnitt einfügen auch hier die Möglichkeit, einzelne Datenfelder durch Ankreuzen zu se-lektieren. Für jedes angekreuzte Feld öffnet sich dann ein separates PopUp-Fenster, in dem wirdie verschiedenen Ausprägungen der in diesem Feld gespeicherten Werte sehen und einzeln an-oder abwählen können. Dabei gilt die folgende Farb-Codierung: angewählte Werte sind blau un-terlegt, abgewählte weiß. Das Pivot-Diagramm passt sich dergestalt an, dass es sich stets nur aufdie selektierten Werte dieses Feldes bezieht.

Ein Datenschnitt ist auch bei Pivot-Tabellen möglich, nicht nur bei Pivot-Diagrammen. Dortfunktioniert er analog. Im Zusammenhang mit Pivot-Tabellen findet man diese Funktion in derRibbonkategorie PivotTable-Tools / Optionen3 in der Rubrik Sortieren und Filtern.

2Diese Kategorie wird im Ribbon jedoch nur dann angezeigt, wenn wir zuvor auf ein existierendes Pivot-Chartgeklickt haben.

3Hier gilt sinngemäß dasselbe wie bei der Fußnote zuvor: damit diese Kategorie im Ribbon verfügbar ist, muss sichdie aktive Zelle in einer Pivot-Tabelle befinden.


4.7 Resumée und ein Blick über den Tellerrand

Pivot-Tabellen und Pivot-Charts bieten einen Einblick in das, was in der Wirtschaftsinformatikals Online Analytical Processing, kurz OLAP, bekannt ist. Hinter diesem Begriff verbergen sichSysteme einerseits und Operationen andererseits, die Analysten, Controllern und Managern zurAufbereitung von Datenbeständen zur Verfügung stehen. So ist beispielsweise eine (von mehre-ren) OLAP-Operation das sogenannte Pivoting, und auch eine Operation namen Slicing ist beiOLAP bekannt: sie ist die englische Übersetzung des hier vorgestellten Datenschnitts in Ex-cel 2010.

Es würde den Rahmen unserer einführenden Veranstaltung übersteigen, auf OLAP im Detail ein-zugehen. Bei Interesse an dieser Thematik sei auf die Vorlesung „Datenmanagement“ verwiesen,die voraussichtlich im kommenden Sommersemester wieder angeboten werden wird.

Technisch gesehen passiert „hinter den Kulissen“ von Pivot-Tabellen jedoch nicht viel Geheim-nisvolles. Wir werden die Funktionsweise von Pivot-Tabellen am Ende des Semesters verstehen,denn bis dahin haben wir – im Zusammenhang mit relationalen Datenbanken und SQL – dasSQL-Statement group by kennengelernt. Mit Hilfe dieses Statements lassen sich Abfragen, dieeiner Pivot-Tabelle gleichkommen, auch mit SQL-Bordmitteln erreichen.

Somit stellen Pivot-Tabellen also eine einfache und kompakte Alternative zum Umgang mit „ech-ten“ Datenbanken dar. Jedoch ist man bei der Verwendung von Datenbanken – z.B. MySQL – inder Regel flexibler, und es können weit mehr und auch komplexere Fragestellungen mithilfe der„group by“-Klauseln beantwortet werden. Ferner kann die Pivot-Tabelle mit einigen Funktio-nen – wie Median oder Quantile – nicht arbeiten, obwohl diese innerhalb der Tabellenkalkulationzur Verfügung stehen, so dass für diesen Fall dann auf andere statistische Tools oder eben Da-tenbanken zurückgegriffen werden muss.

Pivot-Tabellen sind bei den hier gezeigten Einsatzszenarien dennoch eine ideale Alternative zuDatenbanken, da sie einfacher zu handhaben sind, kaum Vorkenntnisse benötigen und die Dar-stellung in der Regel ansprechender ist. Mit ihnen lassen sich alltägliche Fragestellungen schnellund einfach beantworten und nach individuellem Bedarf aufbereiten, ohne tiefere Programmier-kenntnisse vorauszusetzen.

Die Aufbereitung der Datenmengen ist naturgemäß mit einem (optischen) Datenverlust verbun-den, da die Ergebnistabelle in der Regel nicht mehr jeden einzelnen Datensatz in der Datenquelleaufzeigt. Aus diesem Grund sollte eine Pivot-Tabelle ein Ergänzung zu den Quelldaten sein, nichtjedoch ein Ersatz!

Pivot-Tabellen gehören zu den Analyse-Werkzeugen und stellen „ihre“ Datenbasis zu Auswer-tungszwecken geeignet dar. Sie dienen jedoch nicht einer Analyse im Sinne einer Prognose fürzukünftige Entwicklungen, und ebensowenig geben sie eine Erklärung für die vorhandenen Da-ten. Auswertungen dieser Art gehören zu einer anderen Sphäre der (Wirtschafts-)Informatik4.

Insgesamt stellen Pivot-Tabellen und -Charts mächtige, wenngleich aber auch oftmals unbeach-tete Werkzeuge in Excel dar, die in der Lage sind, große Mengen an Datensätzen schnell undunkompliziert in eine aussagekräftige Darstellung zu überführen. Sie dürfen deshalb in unserem„Repertoire der Excel-Kenntnisse“ keinesfalls fehlen!

4genau gesagt zum Bereich „Wissenserkennung in Datenbanken“, kurz „KDD“ (für Knowledge Discovery in Databases)

Kapitel 5

Der Solver

Mit den Pivot-Tabellen haben wir im vorausgegangenen Kapitel ein Leistungsmerkmal kennen-gelernt, das zwar schon seit vielen Jahren bzw. Versionen in Excel enthalten, aber dennoch längstnicht jedem Anwender bekannt ist.

Ähnlich verhält es sich mit dem Gegenstand dieses Kapitels: dem Solver. Wie sein Name bereitssuggeriert, besteht seine Aufgabe darin, etwas zu lösen. Und dieses „Etwas“ ist nichts Geringeresals ein lineares Optimierungsproblem.

Im Rahmen der Mathematik-Vorlesungen im wirtschaftswissenschaftlichen Studium an unsererFakultät werden Sie die theoretischen Hintergründe der linearen Optimierung bereits kennen-gelernt haben oder in Kürze kennenlernen. Aber auch ohne diese Vorkenntnisse lässt sich derSolver problemlos verwenden. Wir verzichten daher in diesem Kapitel auf die Theorie der linea-ren Optimierung und konzentrieren uns stattdessen auf die praktische Benutzung des Solvers.

5.1 Was ist Lineare Optimierung?

Die Lineare Optimierung (auch: „Lineare Programmierung“) ist ein Anwendungsgebiet der Li-nearen Algebra im Bereich Operations Research und hat große Bedeutung für die Lösung von Op-timierungsproblemen in der Wirtschaft. Der Begriff „Programmierung“ ist dabei im Sinne von„Planung“ zu verstehen und wurde schon Mitte der 1940er Jahre von George Dantzig, einem Be-gründer der Linearen Optimierung, geprägt – lange, bevor Computer zur Lösung von linearenOptimierungsproblemen eingesetzt wurden.

Bei der linearen Optimierung geht es darum, eine bestimmte lineare Zielfunktion zu maximie-ren oder zu minimieren – unter der Bedingung, dass gewisse Restriktionen in Form von linearenGleichungen bzw. Ungleichungen eingehalten werden. Als Lösung möchte man somit einen op-timalen Wert unter Einhaltung der Restriktionen finden.

Es gibt viele Beispiele für Situationen, in denen man mit Hilfe der linearen Optimierung ein realesProblem aus der Praxis lösen kann. Schauen wir uns einige davon an dieser Stelle einmal an.

65

66 KAPITEL 5. DER SOLVER

5.1.1 Anwendungsbeispiel: Produktionsplanung

Ein Unternehmen kann verschiedene Produkte mit bekanntem Deckungsbeitrag herstellen. Beigegebenen Verkaufspreisen soll nun der Gewinn maximiert werden. Durch lineare Optimierungkann dann die optimale Produktionsmenge bestimmt werden, wenn die Produktionsmöglich-keiten durch Kapazitätsbeschränkungen, Absatzbedingungen und Finanzierungsengpässe ein-geschränkt werden.

Ebenso sind Zuschnittprobleme mit dem Ziel der Verschnittminimierung oder Zeitmanagement-probleme mit dem Ziel, die Arbeitsabläufe oder Lieferzeiten zu minimieren, lösbar.

5.1.2 Anwendungsbeispiel: Mischungsprobleme

Bei Mischungsproblemen soll mit verschiedenen Zutaten ein Endprodukt zusammengestellt wer-den, wobei die Menge der Zutaten innerhalb eines bestimmten Bereichs variiert werden kann.Ein Beispiel dafür ist das „Diät-Problem“ von George Dantzig: Gegeben sind verschiedene Roh-materialien (wie z.B. Hafer, Fleisch, Milch) zusammen mit ihren Nährwertgehalten (z.B. Eiweiß,Fett, Vitamine etc.) und dem jeweiligen Preis pro Kilogramm.

Die Aufgabe ist nun, aus den Rohmaterialien ein Endprodukt mit minimalen Kosten zu mi-schen – unter der Restriktion, dass bestimmte Mindest- und Höchstgrenzen für die verschie-denen Nährwerte eingehalten werden müssen.

5.1.3 Anwendungsbeispiel: Spieltheorie

Die lineare Optimierung wird auch in der mathematischen Spieltheorie verwendet, um eine op-timale Strategie in Zwei-Personen-Nullsummenspielen zu finden. Indem man für jeden Spielereine Wahrscheinlichkeitsverteilung berechnet, bei der es sich um ein zufälliges Mischungsver-hältnis seiner Strategien handelt, maximiert dieser Spieler somit seinen Gewinn, wenn er seineStrategie unabhängig von der seines Gegners wählt und seine verschiedenen Handlungsalterna-tiven gemäß der zugrunde liegenden Wahrscheinlichkeitsverteilung bestimmt.

5.1.4 Anwendungsbeispiel: Logistische Probleme / Transportprobleme

Transportprobleme behandeln die Organisation des Transports von Waren zwischen verschie-denen Destinationen zu minimalen Kosten bei zudem bestmöglicher Auslastung. Ebenso sindLadeprobleme, beispielsweise die Beladung von LKWs mit Gütern verschiedener Größen unterbestmöglicher Ausnutzung des zur Verfügung stehenden Raumes mit Hilfe der linearen Opti-mierung lösbar.

5.2. EIN KONKRETES BEISPIEL 67

5.2 Ein konkretes Beispiel

Im Folgenden wird nun anhand eines ausgewählten Beispiels das Verfahren der linearen Opti-mierung exemplarisch vorgestellt1.

Ein Unternehmen kann aufgrund seiner Ausstattung mit Personal, Betriebsmitteln und Rohstof-fen in einer Planperiode zwei Produkte (P1 und P2) herstellen. Die realisierbaren Mengenein-heiten (ME) der Produkte werden durch drei Inputfaktoren begrenzt: einer zur Herstellung allerProdukte gemeinsam genutzten Maschine, einem Rohstoff, von dem sich 720 ME auf Lager be-finden, und knappen Kapazitäten in der Montageabteilung für P2. Die pro Periode verfügbarenKapazitätseinheiten (KE) und der Bedarf je hergestellter ME (Produktionskoeffizienten) sowiedie Deckungsbeiträge sind der Tabelle 5.1 zu entnehmen.

Tabelle 5.1: Daten zum konkreten BeispielP1 P2 verfügbare Kapazität

Maschine 1 1 100Rohstoff 6 9 720Montageabteilung 0 1 60Deckungsbeitrag 10 20

Mit diesen Daten ergibt sich die folgende Frage:

„Wie viele ME soll das Unternehmen pro Periode von jedem Produkt herstellen, da-mit es einen größtmöglichen Gesamtdeckungsbeitrag erzielt?“

Das Problem lässt sich auch mathematisch formulieren. Dann lautet die Aufgabe:

maximiere Z := f(x1, x2) = 10x1 + 20x2 (5.1)

unter den Nebenbedingungen

x1 + x2 ≤ 100 (Maschinenrestriktion) (5.2)6x1 + 9x2 ≤ 720 (Rohstoffrestriktion) (5.3)

x2 ≤ 60 (Rohstoffrestriktion) (5.4)x1, x2 ≥ 0 (5.5)

(wobei x1 und x2 die von P1 bzw. P2 herzustellenden Mengenangaben bezeichnen).

Der mathematische Lösungsweg führt in der Regel über das Simplex-Verfahren. Auch eine grafi-sche Lösung lässt sich im hier gezeigten zweidimensionalen Fall leicht motivieren. Dies wird Ge-genstand der bereits angesprochenen Mathematik-Vorlesung sein und soll an dieser Stelle nichtweiter vertieft werden. Stattdessen schauen wir uns nun an, wie die Lösung derartiger linearerOptimierungsprobleme in Excel gefunden werden kann.

1Quelle dieses Beispiels: „Einführung in Operation Research“, Domschke/Drexl, Springer Verlag, 6. Auflage, Seite 14


5.3 Installation des Solvers in Excel

Der Solver ist zwar Bestandteil einer regulären Installation von Excel, allerdings ist er per defaultnicht im Menü zu finden. Dort muss er zunächst hinzugefügt (wir können auch sagen: „akti-viert“) werden. Dies funktioniert wie folgt:

1. Wir wählen im Ribbon die erste Kategorie (Datei) und klicken darin auf Optionen (vorletz-ter Eintrag in der Menüleiste links).

2. Es öffnet sich ein neuer Dialog, der seinerseits am linken Rand eine Menüleiste besitzt. Indieser klicken wir auf Add-Ins (abermals den vorletzten Eintrag).

3. Im Hauptbereich des Dialogs finden wir ganz unten die Drop-Down-Liste Verwalten unddirekt daneben die mit Gehe zu. . . beschriftete Schaltfläche (vgl. Abbildung 5.1). Wir stel-len sicher, dass in der Drop-Down-Liste der Eintrag „Excel-Add-Ins“ ausgewählt ist undklicken auf die benachbarte Schaltfläche.

4. Nun öffnet sich der Dialog „Add-Ins“, der in Abbildung 5.2 dargestellt ist. Wir kreuzendarin den Eintrag „Solver“ an und bestätigen den Dialog mit OK.

5. Nun finden wir im Ribbon in der Kategorie Daten die neue Rubrik Analyse, in der dasSymbol für den Solver gefunden werden kann (vgl. Abbildung 5.3).

6. Durch einen Klick auf dieses Symbol starten wir den Solver und erhalten den in Abbil-dung 5.4 gezeigten Dialog.

Abbildung 5.1: Auswahl der Add-Ins in den Excel-Optionen

5.4. VERWENDUNG DES SOLVERS 69

Abbildung 5.2: Auswahl der zu aktivierenden bzw. aktivierten Add-Ins

Abbildung 5.3: Neuer Eintrag zum Solver in der Ribbon-Kategorie Daten

5.4 Verwendung des Solvers

Wir verwenden den Solver nun, um das obige Optimierungsproblem zu lösen. Doch zunächstmüssen wir ein Verständnis für die vielfältigen Dialog-Optionen entwickeln.

5.4.1 Der Dialog des Solvers

Der Solver-Dialog (vgl. Abbildung 5.4) sieht auf den ersten Blick sehr technisch und kompliziertaus, aber wenn wir uns seine einzelnen Bestandteile in Bezug auf unser konkretes Beispiel an-sehen, dann verliert er sehr schnell seinen Schrecken. Wir erläutern deshalb nun die einzelnenBestandteile des Dialogs:

Ziel festlegen / Bis: Der errechnete Zielwert wird in eine eigens dafür vorgesehene Zelle ge-schrieben, die im ersten Textfeld des Solver-Dialogs angegeben werden kann. Desweiterenkann bzw. muss unsere Optimierungsabsicht angegeben werden: möchten wir unser linea-res Problem minimieren, maximieren oder möchten wir einen bestimmten Wert erreichen?Unser Vorhaben wählen wir durch das passende Optionsfeld. In unserem Beispiel wählenwir deshalb Max.

Durch Ändern von Variablenzellen: Die gesuchten Variablen (hier: x1 und x2) werden in dasTabellenblatt geschrieben. Bildlich kann man sich das Vorgehen so vorstellen, dass diese


Abbildung 5.4: Der Dialog des Solvers

Variablen in Zellen notiert sind und durch wiederholte Variationen ihrer Werte eine opti-male Lösung gesucht wird2. Somit notiert man in dieser Rubrik des Solver-Dialogs, welchesdie (veränderbaren) Zellen zur Notation der gesuchten Variablenwerte sind.

Unterliegt den Nebenbedingungen: Wir haben gelernt, dass die Lösungen der linearen Opti-mierungsprobleme in aller Regel Nebenbedingungen unterliegen. Diese können wir in die-ses Feld eintragen.

Lösungsmethode: In der Mathematik-Vorlesung lernen Sie die lineare Optimierung mit Hil-fe des Simplex-Verfahrens kennen. Für nicht-lineare Optimierungsprobleme gibt es darü-ber hinaus eigene Lösungsverfahren. Auch Excel kennt die prominentesten Vertreter dereinzelnen Kategorien und erlaubt in diesem Feld, das Optimierungsproblem als linear,nicht-linear kontinuierlich und nicht kontinuierlich zu klassifizieren. Da wir in unseremBeispiel ein lineares Problem vorliegen haben, entscheiden wir uns hier für den Simplex-Algorithmus.

5.4.2 Vorbereitung des Tabellenblatts

Damit wir den Solver-Dialog verwenden können, muss das Tabellenblatt ein wenig präpariertwerden. Zunächst benötigen wir eine Zelle, in der unsere Zielfunktion – also jene Vorschrift, die

2Selbstverständlich handelt es sich hierbei nur um eine anschauliche Erklärung. In der Realität findet Excel die Lösungdurch einen entsprechenden Algorithmus!

5.4. VERWENDUNG DES SOLVERS 71

wir maximieren bzw. minimieren wollen – notiert ist. Ferner benötigen wir zwei Zellen, in denendie Anzahlen x1 und x2 der zu produzierenden Mengen von P1 und P2 notiert sind. Diese beidenZellen werden vom Solver zur Findung einer Lösung verwendet und zu diesem Zweck variiert.

Zudem ist auch für die Notation der Nebenbedingungen ein kleiner „Umweg“ notwendig. Wennwir uns beispielsweise die erste Nebenbedingung ansehen, so sagt sie aus, dass die Summe derproduzierten Mengen (also x1 + x2) unter dem Wert 100 bleiben muss. Die Eingabe einer Summeist unmittelbar im Solver jedoch nicht möglich. Deshalb müssen wir auf dem Tabellenblatt eineZelle anlegen, in der die entsprechende Summationsformel notiert ist. Auf diese explizite Zellenehmen wir im Solver-Dialog dann Bezug. Analog gehen wir auch für die zweite Nebenbedin-gung vor.

Abbildung 5.5: Das für die Verwendung des Solvers vorbereitete Tabellenblatt

Somit erstellen wir zunächt also einige Formeln auf dem Tabellenblatt. Abbildung 5.5 zeigt eineMöglichkeit dazu. In Zelle B3 sehen wir die Notation der folgenden Formel:

=10*B5+20*B6

Dies entspricht unserer Zielfunktion, und diesen Wert wollen wir maximieren. In den Zellen B5und B6 sind die zu produzierenden Mengen von P1 und P2 notiert. Die durch die Nebenbedin-gungen ausgedrückten Terme (die jeweils linke Seite) sind in den Zellen B8, B9 und B10 notiert.Beispielsweise enthält die Zelle B8 die folgende Formel:

=B5+B6

Dieser Wert soll laut der ersten Nebenbedingung ja kleiner oder gleich 100 sein. Der Solver wirdspäter den Wert dieser Formel verwenden.

Analogie: Wir erkennen hier wieder einmal die Vorzüge eines Tabellenkalkulati-onsprogramms und der Verwendung von Referenzen: letztendlich werden „nur“die Werte in den Zellen B5 und B6 variiert, und sämtliche darauf Bezug nehmen-den Formeln (Zielfunktion, Nebenbedingungen) passen sich dann automatisch andie neuen Werte an.


5.4.3 Konfiguration des Solvers

Wir konfigurieren nun den in Abbildung 5.4 auf Seite 70 bereits gezeigten Dialog derart, dass erdas gegebene Optimierungsproblem lösen kann.

Feld: Ziel festlegen

Unsere Zielfunktion befindet sich in Zelle B10, und entsprechend geben wir diesen Zellbezug imFeld Ziel festlegen an. Wir können ihn wahlweise manuell eingeben oder mit Hilfe des kleinenSymbols rechts neben dem Eingabefeld auf dem Tabellenblatt via Maus anklicken. In diesemFall erhalten wir dann einen absoluten Zellbezug, der für die korrekte Arbeitsweise des Solversjedoch keine Voraussetzung ist.

Feld: Durch Ändern von Variablenzellen

Die zu variierenden Variablen (hier: Produktionsmenge) befinden sich im Zellbereich B5 bis B6,den wir deshalb ebenfalls im entsprechenden Feld angeben.

Feld: Unterliegt den Nebenbedingungen

Die einzelnen Nebenbedingungen können wir sukzessive durch Anklicken der Schaltfläche Hin-zufügen eingeben. Mit jedem Klick auf diese Schaltfläche öffnet sich der in Abbildung 5.6 gezeig-te Dialog. Wie in dieser Abbildung zu sehen, gelingt die Eingabe der Nebenbedingungen damitsehr intuitiv.

Abbildung 5.6: Eingabe von Nebenbedingungen im Solver

Die Eingabe der ersten drei Nebenbedingungen lässt sich auf diese Weise vollziehen. Die letzteNebenbedingung (Forderung nach nicht-negativen Produktionsmengen) kann ganz leicht durchdas Ankreuzen des Feldes Nicht eingeschränkte Variablen als nicht-negativ festlegen erreichtwerden.

Feld: Lösungsmethode auswählen

Da wir es in unserem Beispiel mit einem Problem der linearen Optimierung zu tun haben, selek-tieren wir im Feld Lösungsmethode auswählen als Lösungsverfahren den Simplex-Algorithmus.

5.5. PARAMETEREINSTELLUNGEN IM SOLVER 73

Abbildung 5.7: Fertig gestellte Konfiguration des Solvers

Die benachbarte Schaltfläche Optionen gibt uns noch präzisere Konfigurationsmöglichkeiten, diewir im nachstehenden Abschnitt besprechen werden.

Nach der korrekten Konfiguration des Solver-Dialogs muss er demnach so aussehen, wie dies inAbbildung 5.7 gezeigt ist.

Durch einen Klick auf die Schaltfläche Lösen beginnt der Solver, eine Lösung des Optimierungs-problems zu ermitteln. Abhängig von der Leistungsfähigkeit des verwendeten Rechners kannes einen kurzen Augenblick dauern, bis die Lösung gefunden wurde. Die zu der gefundenenLösung gehörigen Wertbelegungen der Zielvariablen (hier: x1 und x2) werden in den Zielzellen(hier: B5 und B6) angezeigt. Zudem wird ein kleiner Dialog eingeblendet, der mit OK bestätigtwerden kann (siehe Abbildung 5.8).

Somit haben wir nun die folgende Lösung unseres eingangs genannten Optimierungsproblems:

„Man produziere 30 Einheiten P1 und 60 Einheiten P2!“

Zudem erkennen wir in der Abbildung 5.8, dass durch die gefundene Lösung alle Nebenbedin-gungen erfüllt werden: bei der Rohstoff- und der Montagerestriktion ist der maximal zulässigeWert erreicht (aber nicht überschritten), und bei der Maschinenrestriktion verbleiben wir sogarmit dem Wert 90 noch unter dem maximal zulässigen Wert von 100.

5.5 Parametereinstellungen im Solver

Für lineare Optimierungsaufgaben, die keine komplexen Berechnungen erfordern, findet der Sol-ver mit seinen Standardeinstellungen gleich die richtige Lösung. Es kann aber durchaus vorkom-men, dass das gewünschte Ergebnis nicht erreicht wird (wenn z.B. die Rechenzeit nicht ausreicht


Abbildung 5.8: Die vom Solver gefundene Lösung des Optimierungsproblems

oder das Ergebnis nicht hinreichend genau ist). Dann müssen (und können) die Parameter desSolvers angepasst werden.

Zu diesem Zweck besteht im Solver-Dialog die Möglichkeit, durch Anklicken der SchaltflächeOptionen Einfluss auf die Arbeitsweise des Solvers bzw. den jeweils verwendeten Algorithmuszu nehmen. Es öffnet sich dazu das in Abbildung 5.9 gezeigte Fenster.

Abbildung 5.9: Parametereinstellungen im Solver

Dieses Fenster ist in drei „Karteikarten“ unterteilt, die mit den Benennungen oder – falls dieseauf kleineren Monitoren nicht dargestellt werden – mit Hilfe der nach links und rechts weisendenDreiecke durchlaufen werden können.

5.6. DIE GRENZEN DES SOLVERS 75

Zwei der Karteikarten beziehen sich jeweils nur auf einen Algorithmus (GRG-Nichtlinear undEvolutionärer Algorithmus), die dritte indes zeigt bereits durch ihre Bezeichnung Alle Methoden,dass sie globale Einstellungen beinhaltet. Wir beschränken unsere Ausführungen an dieser Stelleauf diese alle Methoden betreffenden Einstellungen. Ihre Bedeutungen sind im Einzelnen:

Nebenbedingungsgenauigkeit: Sie legt fest, bei welcher Genauigkeit der Solver eine Bedingungals erfüllt betrachtet. Die Genauigkeit wird mit einer Bruchzahl zwischen 0 und 1 angege-ben. Je größer die vorgegebene Genauigkeit ist (je kleiner also der angegebene Wert), umsolänger wird der Solver benötigen (0,0001 führt beispielsweise zu einer größeren Genauig-keit als 0,01).

Automatische Skalierung verwenden: Wenn in einer Berechnung sehr große und sehr kleineZahlen vorkommen, kann es günstig sein, mit dieser Option zu arbeiten. Der Solver bringtdann die Zahlen intern in eine vergleichbare Größenordnung und rechnet am Ende dieErgebnisse zurück. So lässt sich unter Umständen die Genauigkeit erhöhen (z.B. bei derMaximierung des prozentualen Gewinns auf der Grundlage von Investitionen in Millio-nenhöhe).

Iterationsergebnisse anzeigen: Diese Option führt dazu, dass der Solver unterbricht, um die Er-gebnisse jeder einzelnen Iteration anzuzeigen. Das kann bei komplexen und langwierigenBerechnungen sinnvoll sein, da sich dann verfolgen lässt, wie der Solver arbeitet.

Ganzzahlige Nebenbedingungen: Wenn (mindestens) eine Nebenbedingung ganzzahliger Na-tur ist, sollte diese Option aktiviert (angekreuzt) werden.

Ganzzahloptimalität: Wenn ganzzahlige Nebenbedingungen vorliegen, kann in diesem Feldder zulässige Prozentsatz angegeben werden, um den der Wert einer (die ganzzahlige Ne-benbedingungen erfüllenden) Lösung von dem eigentlich optimalen Wert abweichen darf.Die hier getätigte Eingabe hat Auswirkung auf die Perfomance: je höher die Toleranz, destoschneller ist der Lösungsprozess.

Höchstzeit: Dies bestimmt die Zeit, die dem Solver insgesamt zur Verfügung steht. Für die meis-ten kleineren Probleme ist der Standardwert von 100 Sekunden ausreichend (das Maximumliegt bei 32.767 Sekunden).

Iterationen: Diese Einstellung begrenzt die zulässige Lösungszeit, indem die Anzahl der Zwi-schenberechnungen eingeschränkt wird.

Auf die übrigen Parameter wollen wir an dieser Stelle nicht näher eingehen.

5.6 Die Grenzen des Solvers

Obwohl sich der Solver als mächtiges Tool zum Lösen linearer Optimierungsprobleme erweist,stößt er bei gewissen Problemen an seine Grenzen. Deshalb sind die Lösungen des Solvers mitVorsicht zu genießen, denn zum einen findet der Solver nur relative Extremwerte und keine Ex-trempunkte, die am Rand liegen (vgl. dazu Abbildung 5.10). Zum anderen ist die optimale Lö-sung immer von der Wahl der Startwerte abhängig3. Es wird also immer nur das nächstliegendeExtremum gefunden. Dies illustriert Abbildung 5.11. Um ein derartiges Problem zu vermeiden,gibt es zwei Möglichkeiten: entweder beginnt man die Suche bei einem Wert, der schon nahe amOptimum liegt, oder man formuliert den Bereich, den der Solver durchsuchen soll, als Nebenbe-dingung. Bei Aufgaben, die mehrere Lösungen kennen, ist eine Suche nach allen Lösungen leidernicht möglich.

3Hier haben wir eine konkrete Begegnung mit dem im Abschnitt 4.4.1 auf Seite 60 angedeuteten Pivot-Element beimSimplex-Algorithmus!


Abbildung 5.10: Relative (lokale) vs. global-optimale Lösung

Abbildung 5.11: Abhängigkeit von den Startwerten

5.7 Resumée

Genau wie im Fall von Pivot-Tabellen dürfte es dem durchschnittlichen Excel-Benutzer entge-hen, dass „sein“ Tabellenkalkulationsprogramm die Möglichkeit zur Lösung von Optimierungs-problemen bietet. Dieser Umstand wird natürlich auch dadurch provoziert, dass der Solver perdefault gar nicht im Menü bzw. im Ribbon enthalten ist, sondern zunächst – wie in Abschnitt 5.3erläutert – dort hinzugefügt werden muss. Ist dies geschehen, steht er aber fortan zur Verfügung.Auch in OpenOffice Calc ist ein Solver enthalten, der im Wesentlichen genau wie hier beschrie-ben funktioniert.

Sicherlich gibt es Tools und Spezial-Software, die sich besser als der Solver auf das Lösen vonOptimierungsaufgaben verstehen. Für einfache Aufgaben dieser Art ist der Solver jedoch einweiteres wertvolles Werkzeug, das uns ab jetzt im Berufsleben bereitsteht.

Fazit

Wir haben in diesem Skript Vieles aus dem Funktionskanon von Tabellenkalkulationsprogram-men kennengelernt, dem man bei der „üblichen“ Verwendung der Programme Excel und Calcohne Weiteres nicht begegnet.

Wir wollten neugierig machen, was „unter der Haube“ derjenigen Programme, die uns tagtäglichin den Büros dieser Welt als Werkzeuge an die Hand gegeben werden, an noch unentdecktemPotenzial schlummert.

Der Leistungsumfang von Excel (und Calc) ist immer wieder auf’s Neue erstaunlich: es stehenfür Standardaufgaben aus allen Bereichen typische Funktionen zur Verfügung, die unmittelbarauf den Tabellenblättern genutzt werden können. Und hinsichtlich der Auswertung und Aufbe-reitung der Daten sind Werkzeuge wie beispielsweise Pivot-Tabellen und der Solver integriert.Ebenfalls sehr wertvoll sind die vielfältigen Möglichkeiten der Datenvisualisierung mit Hilfe vonDiagrammen.

Aber das ist noch lange nicht alles! Excel und Calc lassen sich durch eigene Programmierungin ihrem Leistungsumfang erweitern. Während Calc diesbezüglich auf die ProgrammierspracheJava setzt, bringt Excel – wie übrigens alle anderen Office-Programme auch – eine vollwertigeProgrammierumgebung mit. Sie ist an die Programmiersprache „Visual Basic for Applications“(kurz: VBA) geknüpft, und durch den Einsatz von VBA kann der Leistungsumfang von Excelum schier unbegrenzte Möglichkeiten erweitert werden.

Der dritte Teil dieser Skriptreihe zu unserer Veranstaltung „Grundlagen von Informationssys-temen“ behandelt eine Einführung in VBA und soll auf diese spannende Welt der „Office-Pro-grammierung“ neugierig machen.

77

78 FAZIT

Anhang A

Funktionsnamen-Übersetzungen

Während die Bedienung einer englischsprachigen Excel-Version in der Regel nicht schwer fällt,wenn man die Symbole im Ribbon kennt, kann es jedoch schnell tückisch werden, wenn manin englischsprachigen Excel-Versionen Funktionen bzw. Formeln eingeben muss. Denn oftmalsist die deutsche Bezeichnung der Funktion völlg anders als ihr englischsprachiges Pendant. Werwäre zum Beispiel gleich darauf gekommen, dass die Funktion sverweis in der englischenVersion vlookup heisst?

Um für einen Gebrauch der englischen Excel-Version gerüstet zu sein, stellen wir in diesem An-hang die Funktionsnamen in deutscher und englischer Sprache gegenüber. Somit haben wir eineReferenz zur Hand, um sowohl die Übersetzung von Deutsch nach Englisch als auch von Eng-lisch nach Deutsch leicht finden zu können.

79

80 ANHANG A. FUNKTIONSNAMEN-ÜBERSETZUNGEN

A.1 Deutsch→ Englisch

A

abrunden rounddownabs absachsenabschnitt interceptadresse addressanzahl countanzahl2 countaanzahlleerezellen countblankarccos acosarccoshyp acosharcsin asinarcsinhyp asinharctan atanarctan2 atan2arctanhyp atanhaufrufen callaufrunden roundup

B

bereich.verschieben offsetbereiche areasbestimmtheitsmass rsqbetainv betainvbetavert betadistbinomvert binomdistbogenmass radiansbw pv

C

chiinv chiinvchitest chitestchivert chidistcode codecos coscoshyp cosh

D

datum datedatwert datevaluedbanzahl dcountdbanzahl2 dcountadbauszug dgetdbmax dmaxdbmin dmindbmittelwert daveragedbprodukt dproductdbstdabw dstdevdbstdabwn dstdevpdbsumme dsumdbvarianz dvardbvarianzen dvarpdia syddm dollar

E

ersetzen replaceexp expexponvert expondist

F

fakultät factfalsch falsefehler.typ error.typefest fixedfinden findfinv finvfisher fisherfisherinv fisherinvftest ftestfvert fdist

A.1. DEUTSCH→ ENGLISCH 81

G

gammainv gammainvgammaln gammalngammavert gammadistganzzahl intgda ddbgda2 dbgeomittel geomeangerade evengestutztmittel trimmeanglätten trimgrad degreesgross uppergross2 propergtest ztest

H

harmittel harmeanhäufigkeit frequencyheute todayhypgeomvert hypgeomdist

I

identisch exactikv irrindex indexindirekt indirectinfo infoistbezug isrefistfehl iserristfehler iserroristktext isnontextistleer isblankistlog islogicalistnv isnaisttext istextistzahl isnumber

J

jahr yearjetzt now

K

kapz ppmtkgrösste largekkleinste smallklein lowerkombinationen combinkonfidenz confidencekorrel correlkovar covarkritbinom critbinomkurt kurtkürzen trunc

L

länge lenlia slnlinks leftln lnlog loglog10 log10loginv loginvlognormvert lognormdist

M

max maxmdet mdetermmedian medianmin minminute minuteminv minversemittelabw avedevmittelwert averagemmult mmultmodalwert modemonat monthmtrans transpose

N

nbw npvnegbinomvert negbinomdistnicht notnorminv norminvnormvert normdistnv na


O

oder or

P

pearson pearsonpi pipoisson poissonpotenz powerprodukt product

Q

qikv mirrquadratesumme sumsqquantil percentilequantilsrang percentrankquartile quartile

R

rang rankrechts rightregister.kennummer register.idrest modrgp linestrkp logestrmz pmtrömisch romanrunden round

S

säubern cleanschätzer forecastschiefe skewsekunde secondsin sinsinhyp sinhspalte columnspalten columnsstabw stdevstabwn stdevpstandardisierung standardizestandnorminv normsinvstandnormvert normsdiststeigung slopestfehleryx steyxstunde hoursuchen searchsumme sumsummenprodukt sumproductsummewenn sumifsummex2my2 sumx2my2summex2py2 sumx2py2summexmy2 sumxmy2sumquadabw devsqsverweis vlookup

T

tag daytage360 days360tan tantanhyp tanhteil midteilergebnis subtotaltext texttinv tinvtrend trendttest ttesttvert tdisttyp type

U

und andungerade odduntergrenze floor

A.1. DEUTSCH→ ENGLISCH 83

V

varianz varvarianzen varpvariation growthvariationen permutvdb vdbvergleich matchverketten concatenateverweis lookupvorzeichen sign

W

wahl choosewahr truewahr truewahrschbereich probwechseln substituteweibull weibullwenn ifwert valuewiederholen reptwochentag weekdaywurzel sqrtwverweis hlookup

Z

zählenwenn countifzeichen charzeile rowzeilen rowszeit timezeitwert timevaluezelle cellzins ratezinsz ipmtzufallszahl randzw fvzzr nper


A.2 Englisch→ Deutsch

A

abs absacos arccosacosh arccoshypaddress adresseand undareas bereicheasin arcsinasinh arcsinhypatan arctanatan2 arctan2atanh arctanhypavedev mittelabwaverage mittelwert

B

betadist betavertbetainv betainvbinomdist binomvert

C

call aufrufenceiling obergrenzecell zellechar zeichenchidist chivertchiinv chiinvchitest chitestchoose wahlclean säuberncode codecolumn spaltecolumns spaltencombin kombinationenconcatenate verkettenconfidence konfidenzcorrel korrelcos coscosh coshypcount anzahlcounta anzahl2countblank anzahlleerezellencountif zählenwenncovar kovarcritbinom kritbinom

D

date datumdatevalue datwertdaverage dbmittelwertday tagdays360 tage360db gda2dcount dbanzahldcounta dbanzahl2ddb gdadegrees graddevsq sumquadabwdget dbauszugdmax dbmaxdmin dbmindollar dmdproduct dbproduktdstdev dbstdabwdstdevp dbstdabwndsum dbsummedvar dbvarianzdvarp dbvarianzen

E

error.type fehler.typeven geradeexact identischexp expexpondist exponvert

F

fact fakultätfalse falschfdist fvertfind findenfinv finvfisher fisherfisherinv fisherinvfixed festfloor untergrenzeforecast schätzerfrequency häufigkeitftest ftestfv zw

A.2. ENGLISCH→ DEUTSCH 85

G

gammadist gammavertgammainv gammainvgammaln gammalngeomean geomittelgrowth variation

H

harmean harmittelhlookup wverweishour stundehypgeomdist hypgeomvert

I

if wennindex indexindirect indirektinfo infoint ganzzahlintercept achsenabschnittipmt zinszirr ikvisblank istleeriserr istfehliserror istfehlerislogical istlogisna istnvisnontext istktextisnumber istzahlisref istbezugistext isttext

L

large kgrössteleft linkslen längelinest rgpln lnlog loglog10 log10logest rkploginv loginvlognormdist lognormvertlookup verweislower klein

M

match vergleichmax maxmdeterm mdetmedian medianmid teilmin minminute minuteminverse minvmirr qikvmmult mmultmod restmode modalwertmonth monat

N

negbinomdist negbinomvertnormdist normvertnorminv norminvnormsdist standnormvertnormsinv sandnorminvnot nichtnow jetztnper zzrnpv nbw

O

odd ungeradeoffset bereich.verschiebenor oder

P

pearson pearsonpercentile quantilpercentrank quantilsrangpermut variationenpi pipmt rmzpoisson poissonpower potenzppmt kapzprob wahrschbereichproduct produktproper gross2pv bw


Q

quartile quartile

R

radians bogenmassrand zufallszahlrank rangrate zinsregister.id regiser.kennummerreplace ersetzenrept wiederholenright rechtsroman römischround rundenrounddown abrundenroundup aufrundenrow zeilerows zeilenrsq bestimmtheitsmass

S

search suchensecond sekundesign vorzeichensin sinsinh sinhypskew schiefesln liaslope steigungsmall kkleinstesqrt wurzelstandardize standardisierungstdev stabwstdevp stabwnsteyx stfehleryxsubstitute wechselnsubtotal teilergebnissum summesumif summewennsumproduct summenproduktsumsq quadratesummesumx2my2 summex2my2sumx2py2 summex2py2sumxmy2 summexmy2

T

tan tantanh tanhyptdist tverttext texttime zeittimevalue zeitwerttinv tinvtoday heutetranspose mtranstrend trendtrim glättentrimmean gestutztmitteltrue wahrtrue wahrtrunc kürzenttest ttesttype typ

U

upper gross

V

value wertvar varianzvarp varianzenvdb vdbvlookup sverweis

W

weekday wochentagweibull weibull

Y

year jahr

Z

ztest gtest

Abbildungsverzeichnis

1.1 Start von Microsoft Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61.2 Start von OpenOffice . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71.3 Auflistung der Tabellenblätter einer Arbeitsmappe . . . . . . . . . . . . . . . . . . . 91.4 Benennung der Zellen durch eine Buchstabe-/Zahl-Kombination . . . . . . . . . . 101.5 Markierung eines zusammenhängenden Zellbereiches . . . . . . . . . . . . . . . . . 111.6 Markierung mehrerer isolierter Zellen . . . . . . . . . . . . . . . . . . . . . . . . . . 111.7 Eingabe einer Formel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121.8 Vergabe von Zellnamen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131.9 Beispiel für einen relativen Zellbezug . . . . . . . . . . . . . . . . . . . . . . . . . . 141.10 Beispiel für einen absoluten Zellbezug . . . . . . . . . . . . . . . . . . . . . . . . . . 151.11 Sortierdialog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161.12 Dialog zur Formatierung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171.13 Dialog zur bedingten Formatierung . . . . . . . . . . . . . . . . . . . . . . . . . . . 181.14 Tabelle mit Klausurergebnissen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191.15 Auswahl zum Anlegen einer neuen Regel zur bedingten Formatierung . . . . . . . 201.16 Konkretes Anlegen einer neuen Regel zur bedingten Formatierung . . . . . . . . . 211.17 Hinzufügen weiterer Regeln zur bedingten Formatierung . . . . . . . . . . . . . . . 211.18 Dialog nach Eingabe aller Regeln zur bedingten Formatierung . . . . . . . . . . . . 221.19 Resultat der bedingte Formatierung . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

2.1 Die Funktionsbibliothek von Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242.2 Ein Assistent zur Verwendung einer Funktion (hier: verweis()) . . . . . . . . . . 242.3 Bestimmung eines Maximums mittels der Funktion max() . . . . . . . . . . . . . . 252.4 Die Funktion AutoSumme . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262.5 Bedingtes Zählen mittels der Funktion zählenwenn() . . . . . . . . . . . . . . . . 272.6 Runden mittels Formatierung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292.7 Anwendung der Funktion verweis() . . . . . . . . . . . . . . . . . . . . . . . . . 342.8 Matrix, die es zu invertieren gilt . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 352.9 Intuitive, jedoch falsche Verwendung einer Array-Funktion . . . . . . . . . . . . . 362.10 Korrekte Verwendung einer Array-Funktion . . . . . . . . . . . . . . . . . . . . . . 372.11 Reaktion auf den Versuch, ein Array-Formel-Resultat zu verändern . . . . . . . . . 372.12 Ausgangstabelle des Beispiels zum Array-Kontext einer Funktion . . . . . . . . . . 382.13 Verwendung einer normalen Rechenoperation im Array-Kontext . . . . . . . . . . 382.14 Resultat unserer Berechnung im Array-Kontext . . . . . . . . . . . . . . . . . . . . . 39

3.1 Optionen zum Import von Daten aus Fremdquellen . . . . . . . . . . . . . . . . . . 413.2 Wikipedia-Eintrag zu Ulm in einem gewöhnlichen Web-Browser . . . . . . . . . . 433.3 Wikipedia-Eintrag zu Ulm im Excel-internen Web-Browser . . . . . . . . . . . . . . 433.4 Korrekte Erkennung der Tabelle in der Druckversion der Wikipedia-Seite . . . . . 443.5 Aktualisierung von Webdaten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 453.6 Hinweis auf die Deaktivierung der Datenverbindung . . . . . . . . . . . . . . . . . 453.7 Import-Assistent – Schritt 1/3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48

87

88 ABBILDUNGSVERZEICHNIS

3.8 Import-Assistent – Schritt 2/3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493.9 Import-Assistent – Schritt 3/3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493.10 Resultat des CSV-Imports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 503.11 Export in eine CSV-Datei mit Microsoft Excel . . . . . . . . . . . . . . . . . . . . . . 503.12 Optionen zum Import von Daten aus externen Datenquellen . . . . . . . . . . . . . 51

4.1 Fiktive Umsatzdaten der Juwelierkette 2010 . . . . . . . . . . . . . . . . . . . . . . . 544.2 Symbol zum Erstellen einer Pivot-Tabelle . . . . . . . . . . . . . . . . . . . . . . . . 554.3 Quelle und Ziel: Konfiguration der Pivot-Tabelle . . . . . . . . . . . . . . . . . . . . 564.4 Noch leere Pivot-Tabelle mit Feldliste . . . . . . . . . . . . . . . . . . . . . . . . . . 564.5 Pivot-Tabelle zur Beantwortung von Frage 1 . . . . . . . . . . . . . . . . . . . . . . 584.6 Pivot-Tabelle zur Beantwortung von Frage 2 . . . . . . . . . . . . . . . . . . . . . . 594.7 Pivot-Tabellen: Filterfunktion via Feldliste . . . . . . . . . . . . . . . . . . . . . . . . 604.8 Pivot-Tabelle zur Beantwortung von Frage 3 . . . . . . . . . . . . . . . . . . . . . . 614.9 Variation zur Pivot-Tabelle aus Abbildung 4.9 (Vertauschung der Reihenfolge) . . 624.10 Pivot-Charts mit vertauschten Achsen-/Legendenfeldern . . . . . . . . . . . . . . . 63

5.1 Auswahl der Add-Ins in den Excel-Optionen . . . . . . . . . . . . . . . . . . . . . . 685.2 Auswahl der zu aktivierenden bzw. aktivierten Add-Ins . . . . . . . . . . . . . . . 695.3 Neuer Eintrag zum Solver in der Ribbon-Kategorie Daten . . . . . . . . . . . . . . . 695.4 Der Dialog des Solvers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 705.5 Das für die Verwendung des Solvers vorbereitete Tabellenblatt . . . . . . . . . . . . 715.6 Eingabe von Nebenbedingungen im Solver . . . . . . . . . . . . . . . . . . . . . . . 725.7 Fertig gestellte Konfiguration des Solvers . . . . . . . . . . . . . . . . . . . . . . . . 735.8 Die vom Solver gefundene Lösung des Optimierungsproblems . . . . . . . . . . . 745.9 Parametereinstellungen im Solver . . . . . . . . . . . . . . . . . . . . . . . . . . . . 745.10 Relative (lokale) vs. global-optimale Lösung . . . . . . . . . . . . . . . . . . . . . . 765.11 Abhängigkeit von den Startwerten . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76

Tabellenverzeichnis

5.1 Daten zum konkreten Beispiel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67

89

Documents

Grundlagen von Informationssystemen - iai.mathematik.uni ...iai.mathematik.uni-ulm.de/de/teaching/downloads/skriptOffice.pdf · Excel 2010 unterscheidet sich diesbezüglich nur marginal