Agenda für heute, 12. März, 2008 Tabellenkalkulation:Tabellenkalkulation: Das Arbeitspferd der...

Agenda für heute, 12. März, 2008

• Tabellenkalkulation: Tabellenkalkulation: Das Arbeitspferd der DatenverarbeitungDas Arbeitspferd der Datenverarbeitung

• Mehr als rechnen: Arbeitsprozesse unterstützen

• Anders rechnen: Numerische Methoden

• Zirkelbezug

• Pause

• Zielwertsuche

• Lineare Optimierung

Informatik für Biol. & Pharm. Wissenschaften © Institut für Computational Science, ETH Zürich

Das heutige Thema und der Informationsarbeitsplatz

2/32


Historischer Kontext der Datenverarbeitung

3/32

Def.: Zweckgebundene Transformation von Daten durch Menschen oder Maschinen nach einem vorgegebenen Verfahren

Manuelle Datenverarbeitung• Bürokratische Verfahren mit standardisierten Dokumenten• Formulare, Fragebögen, tabellarisch fixiert• geringer Durchsatz

Maschinelle Datenverarbeitung• Zählen: Hollerith Lochkarten (1890)• Rechnen: IBM (1924)• Vergleichen:Sortier- & Tabelliermaschinen• Automatisierung


Historischer Kontext der Datenverarbeitung

4/32

Elektronische Datenverarbeitung

• flexible Programmierung• keine mechanischen Speichermedien

Einsatz des Computers als

• Rationalisierungsinstrument = schnelles

Zählen, Rechnen, Vergleichen

• Restrukturierungswerkzeug = flexibles

Formatieren, Kombinieren, Adressieren, Synchronisieren

Eine Gemeinsamkeit aller Methoden:

Die Tabelle – sie strukturiert jede Art der Datenverarbeitung


Die Tabelle als Organisationsprinzip

5/32

Tabellen werden verwendet als

Formalisierungsmedium: Formatierung durch• Rasterung• Adressierung

Speichermedium: vereinfacht• Suchen• Sortieren

Die Informatik kann die Tabellen als Objekt verselbständigen

Daten werden aus den Prozeduren heraus gelöst

Anwender hängen nicht mehr von Programmierern ab


Formalisierung und Formatierung

6/32

Der Wechsel von Schriftzeichen zu binär codierten Zahlenwerten erlaubt Automatisierung


Buchungsvorgang mit verschalteten Tabellen

7/32


Entscheidungstabellen

8/32

Wenn es regnet und weder Schnee noch Nebel hat, dann nehm ich das Auto.


Datenverarbeitung am Informationsarbeitsplatz

• Das universellste Informatikmittel für die individuelle Datenverarbeitung ist die Tabellenkalkulation

9/32

1977

Dan Bricklin (Harvard MBA Student)

Robert Frankston (Programmierer)


Universell heisst …

… Verschiedene Arten des Einsatzes:

• Automatisches Ausführen und Aktualisieren von Berechnungen

• Textverarbeitung, Präsentation

• Datenspeicherung & Datenverwaltung (Praxis 4)

• Online-Zusammenarbeit

• Automatisches erstellen und aktualisieren von Diagrammen (Praxis 3)

• Modellierung (Praxis 2)

• Programmierung (Makros, Praxis 6)

… heisst, unterschiedlichste Arbeitsprozesse unterstützen!

10/32

• Tabellenkalkulation: Das Arbeitspferd der Datenverarbeitung

• Mehr als rechnen: Arbeitsprozesse unterstützen Mehr als rechnen: Arbeitsprozesse unterstützen • Anders rechnen: Numerische Methoden

• Zirkelbezug

• Pause

• Zielwertsuche



Mehr als rechnen: Arbeitsprozesse unterstützen

• Schutzfunktionen

Eine Arbeitsmappe oder Teile davon vor dem Einblick oder der Änderung durch Unbefugte schützen

• Online-Zusammenarbeit

Über "NetMeeting" eine Arbeitsmappe für andere in Echtzeit zugänglich machen

• Mehrfachoperationen

Eine Formel oder Funktion, die auf mehrere Werte einer Variablen gleichzeitig angewandt wird, muss nur einmal eingegeben werden

• Direkte Manipulation: z.B. Bildlaufleiste eines Formulars

Interaktiv Werte einer Eingabezelle dynamisch verändern

11/32


Schutzfunktionen

• Kennwortgeschütztes Speichern einer Datei

• Schreibschutzkennwort festlegen

• Eingaben gegen Überschreiben schützen

• Alle Änderungen in der Arbeitsmappe protokollieren

• Einzelne Zellbereiche können ungeschützt bleiben

12/32


Online-Zusammenarbeit

Mehrere Sitzungsteilnehmer können gleichzeitig dasselbe Dokument bearbeiten

13/32


Planen mit Was-Wenn-Tabelle

Befehl: 'Tabelle' im Menü 'Daten'

14/32

Sechs Varianten, mit der gleichen Funktion berechnet wie der Wert in C7


Bäume wachsen nicht in den Himmel

Einschränkungen der Tabellenkalkulation

• Datenverwaltung wird schnell aufwändig

(Lösung: Datenbanksystem verwenden)

• Beschränkt in der Grösse

(Lösung: Datenbanksystem verwenden)

• Zusammenhänge sind nicht sichtbar

(Wichtig: Gute Dokumentation)

• Gefahr von Nebenwirkungen (side effects) ist gross

(Wichtig: Sorgfältig arbeiten)

15/32



16/32



On the accuracy of statistical procedures in Microsoft Excel 2003B.D. McCullough, Berry Wilson

Computational Statistics & Data Analysis 49 (2005) 1244-1252

Abstract

Some of the problems that rendered Excel 97, Excel 2000 and Excel 2002 unfit for use as a statistical package have been fixed in Excel 2003, though some have not. Additionally, in fixing some errors, Microsoft introduced other errors.

….

Excel 2003 is an improvement over previous versions, but not enough has been done that its use for statistical purposes can be recommended.

1732


Verbreitete Fehlerquellen

18/32

• Falsche Eingabe

• Falsche Rechenoperatoren

• Falsche Formatierungen

• Zirkelbezüge

• Relative und absolute Bezüge

• Falsche Inhalte

• Denkfehler

• Nebenwirkungen

Literatur: Berechnungen in Excel: Zahlen, Formeln und Funktionen

R. Martin, Hanser Verlag, 2004



• Anders rechnen: Numerische Methoden Anders rechnen: Numerische Methoden • Zirkelbezug

• Pause

• Zielwertsuche



Wunsch und Wirklichkeit

Wunsch:

Realität:

19/32

Die meisten quantitativen Probleme können mit einer geigneten mathematischen Methode analytisch exakt gelöst werden.

Für die wenigsten mathematischen Probleme in der (wissen-schaftlichen) Praxis gibt es eine explizite Darstellung der Lösung.

Die Lösungen verursachen oft einen grossen Aufwand oder sind mit Fehlern behaftet.

Um dennoch zu Resultaten zu kommen, werden angenäherte Lösungen mit Methoden aus der numerischen Mathematik gesucht.

Dazu braucht es Computer.


Numerische Methoden

Typische Vorgehensweise

Ausgehend von einer geschätzten Lösung wird wiederholt eine Berechnung mit leicht veränderten Werten so oft ausgeführt, bis entweder eine vorgegebene Zeit ausläuft oder der Unterschied zwischen zwei Lösungsschritten ein vorbestimmtes Mass unterschreitet.

20/32


Anwendungsbeispiele numerischer Methoden in Excel

• Zirkelbezüge

Gleichungssysteme numerisch lösen

• Zielwertsuche

"Taschenrechner" für eine Unbekannte


Zielwerte unter Berücksichtigung von Nebenbedingungen maximieren oder minimieren

Literatur: The Active Modeler: Mathematical Modeling with Excel

E. Neuwirth, D. Arganbright, Thomson, Brooks/Cole, 2004

Hinweis: Hier geht es nicht um die Theorie numerischer Methoden, sondern um deren beispielhafter Anwendung.

21/32




• ZirkelbezugZirkelbezug• Pause

• Zielwertsuche



Zirkelbezüge

Formeln dienen dazu, aus bestehenden Werten neue zu erzeugen.

22/32

Ein Zirkelbezug liegt dann vor, wenn eine Formel sich direkt oder indirekt auf die Zelle in der sie steht zurück bezieht.

Sie können absichtlich oder unabsichtlich entstehen!


Beispiel eines beabsichtigten Zirkelbezuges

Bruttogewinn B = Fr. 2000.-

Nettogewinn N = B – P

Provision P = 10% von N

Bevor N berechnet werden kann, muss P bekannt sein. Um P zu berechnen muss aber N bekannt sein. Es ensteht ein Zirkelbezug:

N = B – P

P = 10% von N

23/32

Numerische Lösung mit Excel

(Extras Optionen Berechnen Iteration)




• Zirkelbezug

• Pause

• ZielwertsucheZielwertsuche• Lineare Optimierung


Zielwertsuche in der Chemie

Beispiel: Van-der-Waals-Gleichung.

• Empirisch gefundene Zustandsgleichung für das näherungsweise thermische Verhalten von realen Gasen und Flüssigkeiten.

• Gleichung: (p + a / V2) * (V – b) = R*T (für 1 mol)

mit p = DruckV = MolvolumenT = absolute TemperaturR = Gaskonstantea, b =stoffspezifische Konstanten

• Gegeben: Temperatur und Druck

• Gesucht: Molvolumen V

• Klassische Lösung: Gleichung 3. Grades für V !

24/32

Johannes Diderik van der Waals


Methodik der Zielwertsuche

Die Ausgangslage

• Es ist klar, welche Formel zur Berechnung eines bestimmten Ergebnisses verwendet wird

• Man weiss auch welches Ergebnis die Formel liefern soll (Zielwert)

Das Problem

• Aber man kennt einen bestimmten Wert nicht, den die Formel zum Aber man kennt einen bestimmten Wert nicht, den die Formel zum Errechnen dieses Zieles benötigtErrechnen dieses Zieles benötigt

Die Lösung

• Excel verändert diesen Wert so lange, bis die von dieser Zelle abhängige Formel den festgelegten Zielwert berechnet hat

25/32


Lösung der Van-der-Waals-Gleichung mit Zielwertsuche

Umstellung der Gleichung (Zielwert = 0)

(p + a / V2) * (V – b) – R*T = 0

• Konstanten:

a = 656500,

b = 0.0562

R = 8282

p = 1011060

T = 300C = 303.15 K

Tabellenmodell

26/32

Formel und Ergebnissind bekannt

ein Parameterist unbekannt




• Zirkelbezug

• Pause

• Zielwertsuche

• Lineare OptimierungLineare Optimierung


Worum geht es? Ein Beispiel zur Illustration

Wir sollen aus Gemüse und Fleisch, welche Magnesium, Eisen und die Vitamine C und B12 enthalten, Mahlzeiten herstellen, die möglichst kostengünstig sind.

Gleichzeitig müssen Diätanforderungen in Form von Mindestmengen der Mineral-stoffe und Vitamine erfüllt werden, welche mit der Mahlzeit aufgenommen werden.

Die folgende Tabelle gibt eine Übersicht über die Inhaltsstoffe:

27/32

Menge der Mineralien & Vitaminen

[mg je 100g]

Mindestmengen an Mineralien und Vitaminen in den Rationen

[mg]Gemüse Fleisch

MagnesiumEisenVitamin C

Vitamin B12

501

500

2520

0.001

1758

750.002

Kosten [Fr./100g] 4 6


Das mathematische Modell

28/32

Die Kontrollvariablen sind die Menge an Gemüse x1 (in 100g) und an Fleisch x2

(in 100g), welche für die Mahlzeit verwendet werden.

Die Mindestanforderungen an Mineralstoffen und Vitaminen lassen sich als " ≥ -Beziehungen" ausdrücken.

Das, bezüglich der minimalen Kosten optimale Produktionsprogramm, wird durch folgende Ungleichungen beschrieben:

min K (x1,x2) = 4x1 + 6x2

unter den Nebenbedingungen:

(i) 50x1 + 25x2 ≥ 175

(ii) x1 + 2x2 ≥ 8

(iii) 50x1 ≥ 75

(iv) 0.001x2 ≥ 0.002

(v) x1 ≥ x2 ≥ 0

Die Lösung dieses Ungleichungssystems ist die kostenminimale Zusammensetzung einer Mahlzeit aus Fleisch und Gemüse, welche die Diätanforderungen erfüllt.


Grafische Lösung des linearen Optimierungsproblems

29/32

x2

x11.5

2

7

3.5

4

8

Nebenbedingung (i)

Nebenbedingung (ii)

Nebenbedingung (iii)

Nebenbedingung (iv)

Ant

eil

Fle

isch

Anteil Gemüse

Wo liegt das Minimum?


Lineare Optimierung mit Excel

Das Hilfsprogramm Solver von Excel ist ein Instrument, mit dem Lösungen für einen oder mehrere Werte gefunden werden können, um einen Zielwert unter Berücksichtigung von Nebenbedingungen zu maximieren oder zu minimieren.

Tabellenmodell für das Mahlzeitenproblem

30/32


Mehr als nur rechnen!

Mit Excel stellt uns der Informationsarbeitsplatz ein Werkzeug zur Verfügung, das höchsten Ansprüchen der Datenverarbeitung genügt.

Mit ihm lassen sich nicht nur die traditionellen Berechnungen der Tabellenkalkulation ausführen, sondern Methoden anwenden, welche z.B. für die Entscheidungsfindung unschätzbare Dienste leistet.

31/32


Verschiedene Anwendungsgebiete der linearen Optimierung

• Ernährungswissenschaft und FuttermittelindustrieMischungsproblem: preiswerte Rationen mit vorgegebenem Gehalt an Nährstoffen

• TransportwesenUmfangreiche Transporte mit möglichst geringem Aufwand

• ErdölindustrieGewinnung, Aufbereitung und Verteilung des Erdöls mit minimalen Kosten

• KommunikationTelefonverbindungen zwischen Städten kostengünstig einrichten

• LandwirtschaftRationelle Aussaat bestimmen

• StahlherstellungOptimale Ausnutzung der Walzstrassen

• PharmazieHerstellungskosten für Präparate optimieren

32/32

Danke für Ihre Aufmerksamkeit

Agenda für heute, 12. März, 2008 Tabellenkalkulation:Tabellenkalkulation: Das Arbeitspferd der...

Documents

Transcript of Agenda für heute, 12. März, 2008 Tabellenkalkulation:Tabellenkalkulation: Das Arbeitspferd der...