Einführung in die planungsbezogene EDV · Eine sehr praktische – weil schnelle – Methode zur...

Einführung in die planungsbezogene EDV Einheit 4 Autoren: Riedl, Reinberg VU 266.076 Suchfunktionen / Mehrfachoperation Hubmann

www.srf.tuwien.ac.at/lva/edv Seite 1

Einführung in die planungsbezogene EDV VU 266.076 – WS 2010/2011

Arbeitsunterlagen

E i n h e i t 4

Lehrveranstaltungsleiter: Univ. Ass. Dipl.‐Ing. Robert KALASEK Autoren: Wiss. Oberrat Dipl.‐Ing. Leopold RIEDL Dipl.‐Ing. Sebastian REINBERG Jakob HUBMANN Univ. Ass. Dipl.‐Ing. Verena RIEDL Univ. Ass. Mag. Philip REINHART Florian STROHMAYER BSc.

Fachbereich für Stadt‐ und

Regionalforschung



Einheit 4, Beispiel 1 – Suchfunktionen und Mehrfachoperationen (die Funktionen VERGLEICH, INDEX, SVERWEIS, ZÄHLENWENN und SUMMEWENN)

Gegeben ist eine Matrix mit Daten über die österreichischen Bundesländer (siehe Abbildung).

Da in diesem Beispiel einige Namen verwendet werden, vorab einmal ein paar einleitende Worte zum Thema:

Namen „erstellen“:

Eine sehr praktische – weil schnelle – Methode zur gleichzeitigen Festlegung von mehreren Namen für verschie‐dene Zellbereiche ist folgende:

(1) Die Namen direkt neben die Zellbereiche, die man benennen möchte, schreiben (also darüber, darunter, links oder rechts davon).

(2) Danach den gesamten Bereich markieren und aus der Multifunktionsleiste „Formeln“ – Kategorie „Definierte Namen“ den Befehl „Aus Auswahl erstellen“ auswäh‐len (siehe Screenshot rechts).

Es erscheint folgendes Dialogfenster:

(3) Je nachdem, wo die Namen stehen, die gewünschte Option auswählen – z.B. Namen aus "Oberster Zeile", auch ev. mehrere gleichzeitig (!) – und mit OK bestätigen.

Man beachte den interessanten Hinweis im zweitenAbsatz der Hilfe… Auch als Sprachwissenschaftler [undnicht nur als Naturwissenschaftler] könnte man vielFreude mit Excel haben.



Beispiel:

Markieren von C4:H13 und Aufruf des „Aus Auswahl Erstellen“ mit der Option "Oberster Zeile" legt die Namen Bundesland, Code, Hauptstadt, Fläche, Bevölkerung und Tabelle für die jeweils darunter liegenden Zellen in den Zeilen 5 bis 13 fest.

In gleicher Manier legen wir den Namen für die oberste Zeile der Matrix (C4:H4) fest. Diesmal markieren wir je‐doch auch die Zelle B4, wo das Wort „Spaltentitel“ steht, und wählen im Dialogfenster Namen aus „Linker Spalte“ erstellen aus. Der Bereich C4:H4 heißt nun „Spaltentitel“.

Diese praktische und relativ schnelle Möglichkeit Namen zu erstellen funktioniert leider nur, wenn auch jede zu benennende Zeile bzw. Spalte eine Überschrift besitzt. Wenn wir nun für unser Beispiel den Bereich C5:H13 mit dem Namen Bld_Matrix versehen möchten, so können wir nicht einfach diesen Bereich inklusive der Zelle B5 markieren und dann Namen aus „Linker Spalte“ auswählen. Excel benennt dann nur die erste Zeile (C5:H5) – alle anderen bleiben unbenannt. Das Vergeben von Namen per Hand bleibt uns also auf Dauer nicht erspart.

Frage 1: Suche nach Werten in einer Matrix

Bei der ersten Fragestellung soll im Ergebnisfeld (D26) der den gewählten „Suchkrite‐rien“ entsprechende Wert ausgegeben werden.

In unserem Beispiel suchen wir die Landeshauptstadt von Tirol.

Hierfür verwenden wir die Funktionen VERGLEICH und INDEX, 2 Suchfunktionen die Matrizen / Datenbanken /

Tabellen ähnlich wie ein Koordinatensystem durchsuchen und den gewünschten Wert bzw. Eintrag zurückgeben. Hierbei liefert die Funktion VERGLEICH die Spalten‐ oder Zeilennummer (quasi die X‐ oder Y‐Koordinate) eines gesuchten Wertes in einer Matrix. Die Funktion INDEX sucht und gibt den Eintrag, der an einer bestimmten Stelle einer Matrix steht, aufgrund von Angaben zur Spalten‐ und Zeilennummer aus.



Frage 1 – die verwendeten Funktionen im Detail

Gesucht ist bei gegebenen Bundeslandcode (B, K, N, ...) und abgekürztem Spaltentitel (Haupt, Bev, ...) der zugehörige Wert in der jeweiligen Zeile und Spalte der Bld_Matrix.

=INDEX(Matrix;Zeile;Spalte) Liefert den Wert der Matrix in der angegebenen Zeile und Spalte.

Matrix ist ein Bezug auf einen rechteckigen Bereich in der Tabelle.

Zeile und Spalte sind positive ganze Zahlen oder Bezüge darauf: Zeile (Spalte) darf nicht größer sein als die Anzahl der Zeilen (Spalten) in der Matrix. Andernfalls liefert die Funktion den Fehlerwert #BEZUG.

z.B.: Enthalten die Zellen A1:C1 die Werte "Tick", "Trick", "Track" und die Zellen A2:C2 die Werte "Donald", "Da‐gobert", "Daisy" dann liefert die Formel =INDEX(A1:C2;2;3) den Wert "Daisy" (2. Zeile, 3. Spalte).

=VERGLEICH(Suchkriterium;Suchmatrix;Vergleichstyp) Liefert die relative Position des Suchkriteriums in der Suchmatrix.

Das Suchkriterium ist ein einzelner Wert oder ein Bezug auf einen Wert.

Die Suchmatrix ist ein Bezug auf einen Zeilen‐ oder einen Spaltenvektor.

Der Vergleichstyp kann die Werte 1, ‐1 oder 0 annehmen:

1 oder weggelassen: Die Suchmatrix muss aufsteigend sortiert sein und die Funktion liefert die Position des größten Wertes in der Suchmatrix, der kleiner oder gleich dem Suchkriterium ist.

‐1: Die Suchmatrix muss absteigend sortiert sein und die Funktion liefert die Position des kleinsten Wertes in der Suchmatrix, der größer oder gleich dem Suchkriterium ist.

0: Die Funktion liefert die Position des ersten Wertes in der Suchmatrix, der exakt gleich dem Suchkriterium ist. Groß‐ und Kleinschreibung wird ignoriert. Die Sortierung der Suchmatrix ist egal!

z.B.: Enthält der Bereich A1:A3 die Werte "Karl", "Franz" und "Peppi", dann liefert die Formel =VERGLEICH("Franz";A1:A3;0) den Wert 2, weil "Franz" der 2. Eintrag im Suchvektor A1:A3 ist (i.e. der Wert der Zelle A2).

z.B.: die Formel in Zelle D25 lautet: =VERGLEICH(Spalten_Abk&"*";Spaltentitel;0)

Das Suchkriterium lautet Spalten_Abk&"*" also z.B. "haupt*". Das Sternchen * hat die Funktion eines Stellvertre‐terzeichens.

Das Sternchen * steht für eine beliebige Zeichenfolge, z.B. findet "haupt*" unabhängig von Groß/Kleinschreibung alle Wörter, die mit "haupt" beginnen ("Haupt", "Hauptstadt", "Hauptort",...).

Das Fragezeichen ? steht für genau 1 Zeichen: "M?yer" findet z.B. "Mayer" und "Meyer", nicht aber "Myer" oder "Mittermayer".

Die obige VERGLEICH‐Funktion mit "haupt*" als Suchkriterium findet im Bsp.1 die relative Position der ersten Zelle von links im Spaltentitel, deren Wert mit der Zeichenfolge "haupt*" beginnt, also 3 ("Hauptstadt").



Frage 2 – Inhaltlicher Hintergrund

In Österreich sind gemäß Bundesverfassung (B‐VG) viele Angelegenheiten in der teilweisen oder alleinigen Kom‐petenz der einzelnen Bundesländer (vgl. dazu die Art. 10 bis 15 B‐VG, die sog. „Kompetenzartikel“). Es ist somit durchaus vorstellbar, dass die Vergabe von Zuschüssen für bestimmte Vorhaben verschieden gehandhabt wird. Ein sehr einfaches (und daher eher unrealistisches) Förderungsmodell wäre z.B. folgendes:

Abhängig von der Antragshöhe (z.B. Investitionskosten für Umweltschutzmaßnahmen) wird ein bestimmter Pro‐zentsatz vom Land als Zuschuss gewährt. Die Grenzwerte und die zugehörigen Prozentsätze sind tabellarisch fest‐gelegt. In den einzelnen Bundesländer gelten allerdings verschiedene Tabellen: TAB1 bis TAB3. In manchen Län‐dern gibt es gar keine Zuschüsse ("‐", siehe Abb. Spalte H).

Ob und welche Tabelle in den einzelnen Ländern gilt, ist in der Bld_Matrix in der Spalte mit der Überschrift Tabel‐le festgehalten (Spalte H, siehe Abb.). Die Tabellen TAB1, TAB2 und TAB3 selbst befinden sich in einem eigenem Tabellenblatt (Tabelle 2, siehe nächste Seite).

Frage 2 – Berechnung des Zuschusses

Als Eingabe wird mit einem Bundesland‐Kürzel Kürzel_Bsp2 und einer Antragssumme gerechnet. Aus diesen An‐gaben soll der daraus resultierende staatliche Zuschuss berechnet werden (siehe letzte Zeile der Abbildung).

o Die Berechnung der Zeile innerhalb der Bld_Matrix wird analog zur Frage 1 mit Hilfe der Funktion VER‐GLEICH vorgenommen (Formel siehe Abbildung).

o Ebenfalls analog zur 1. Fragestellung wird die für das jeweilige Bundesland gültige Tabelle Landes_TAB mittels der Funktion INDEX aus der Bld_Matrix extrahiert (Formel siehe Abbildung).

o Da die Berechnung des Zuschusses etwas länger ist, erfolgt sie in Form einer Art „Nebenrechnung“ im Ta‐bellenblatt Tabelle 2 (siehe nächste Seite).

Das Ergebnis (=der Zuschuss) steht in der 2. Spalte in der Ergebnistabelle der Nebenrechnung neben dem ge‐wünschten Tabellennamen (siehe umseitige Abb. ganz unten: Name TAB_Zuschuß =Tabelle2!$C$25:$D$27 Funktion SVERWEIS) – das allerdings nur, wenn die Landes_TAB ungleich "‐" ist (was ja den Zuschuss 0 bedeutet

Funktion WENN, Formel siehe obige Abbildung Zeile 37).



Frage 2 – Berechnung der Zuschüsse nach TAB – Nebenrechnung

Die Zuschusstabellen sind in der Tabelle 2 aufgelistet (Abb. Links: Werte, rechts: Formeln).

Übrigens: die obige Formelansicht (rechter Screenshot) lässt sich ganz leicht mit der Tastenkombination STRG+# ein‐ und ausschalten. Achtung: bei Aktivierung dieser Ansicht ändern sich die Spaltenbreiten automatisch. Davon bitte nicht verwirren lassen – beim Zurückwechsel in die „normale“ Ansicht werden auch die Spaltenbreiten wie‐der zurückgesetzt. Die Berechnungslogik ist wie folgt (am Beispiel von TAB1):

o Für die ersten € 2.000,‐ werden 100% als Zuschuss gewährt.

o Für die nächsten € 10.000,‐ werden 25% als Zuschuss gewährt.

o Für die nächsten € 3.000,‐ werden 5% als Zuschuss gewährt.

o Darüber hinaus erfolgt kein Zuschuss mehr.

In Summe erhält der Antragsteller für € 7.500,‐ bei Anwendung des Förderungsschemas TAB 1 € 3.375,‐.

Die notwendigen Schritte zur Berechnung eines Zuschusses am Beispiel der TAB1: (1) Die Berechnung beginnt mit der Antragssumme, von der dann in den darunterliegenden Zeilen sukzessive

bereits abgerechnete Teilbeträge abgezogen werden: ⇒ Formel in E4 =Antragssumme

(2) Rechts daneben wird für jede Zeile der Zuschusstabelle berechnet, wie viel von dieser Antragssumme in diesem Intervall liegt. Das Intervall berechnet sich als Bis‐Wert minus Von‐Wert (also C4‐B4 in der ersten Zeile). Dieses Intervall wird mit der Antragssumme verglichen und die kleinere der beiden Zahlen wird als Betrag ausgewiesen: ⇒ Formel in F4 =MIN(C4‐B4;E4)



(3) Der Zuschuss für dieses Intervall ergibt sich durch Multiplikation mit dem Prozentsatz ⇒ Formel in G4 =D4*F4

(4) Die Antragssumme wird in der nächsten Zeile um den (zuvor bereits abgearbeiteten) Betrag verringert (Zelle E5 mit der Formel =E4‐F4) und die Berechnung beginnt erneut (2. und 3. Zeile analog ⇒ relative Bezüge, s. obere Abbildung Formeln rechts!)

(5) Die Berechnung in (bzw. neben) den Tabellen TAB2 und TAB3 erfolgt analog. Es werden also für die gege‐bene Antragssumme die Zuschüsse nach allen 3 Tabellen erstellt.

(6) Damit auf das richtige Ergebnis mit SVERWEIS zugegriffen werden kann (Formel s. vorige Seite), werden alle 3 Ergebnisse in einer kleinen Tabelle zusammengefasst ( Name TAB_Zuschuß für C25:D27; in der 2. Spalte Bezüge verwenden: =G7, etc. !!).

Frage 3 – Statistische Auswertungen mit Bedingungen

=ZÄHLENWENN(Bereich; Suchkriterien) Zählt die nicht leeren Zellen eines Bereichs, deren Inhalte mit dem Suchkriterium übereinstimmen.

Bereich ist der Zellbereich, von dem Du wissen willst, wie viele seiner Zellen einen Inhalt haben, der mit den Suchkriterien übereinstimmt.

Suchkriterium gibt das Kriterium in Form einer Zahl, eines Ausdrucks oder einer Zeichenkette an. Beispiele für gültige Suchkriterien sind 17, ">17" und "Karl".

z.B.: Enthält der Bereich A1:A4 die Werte "Karl", "Franz", "Peppi", "Karl", dann liefert die Funktion =ZÄHLENWENN(A1:A4;"Karl") den Wert 2, weil "Karl" zweimal vorkommt.

z.B.: Enthält der Bereich B1:B4 die Zahlen 22, 18, 16, 23, dann liefert die Formel =ZÄHLENWENN(B1:B4;">17") den Wert 3, weil drei Zahlen größer als 17 sind.

Die Funktion verhält sich also gewissermaßen wie die Funktionen ANZAHL oder ANZAHL2 – nur eben mit einem zusätzlichen WENN‐Kriterium.



=SUMMEWENN(Bereich; Suchkriterien; Summe_Bereich) Addiert Zahlen, die mit dem Suchkriterium übereinstimmen.

Bereich ist der Zellbereich, von dem Du wissen willst, wie viele seiner Zellen einen Inhalt haben, der mit den Suchkriterien übereinstimmt.

Suchkriterien gibt das Kriterium in Form einer Zahl, eines Ausdrucks oder einer Zeichenkette an. Beispiele für gültige Suchkriterien sind 17, ">17" und "Karl".

Summe_Bereich gibt den Bereich an, in dem sich die tatsächlich zu addierenden Zahlen befinden. Die zu Summe_Bereich gehörenden Zellen werden nur dann in die Addition einbezogen, wenn die Inhalte ihrer entspre‐chenden in Bereich befindlichen Zellen dem Suchkriterium genügen. Fehlt das Argument Summe_Bereich, werden die zum Bereich gehörenden Zellen addiert.

Im Prinzip heißt das: WENN die Werte im BEREICH mit den SUCHKRITERIEN übereinstimmen, DANN bilde eine Summe über die dazugehörigen (= sich in der gleichen Zeile befindlichen) Werte aus SUMME_BEREICH. Sollte es keinen SUMME_BEREICH geben, dann wird der BEREICH der den SUCHKRITERIEN entspricht summiert.

z.B.: Enthält der Bereich A1:A4 die Werte "Karl", "Franz", "Peppi", "Karl" und der Bereich B1:B4 die Zahlen 22, 18, 16, 23, dann liefert die Formel

=SUMMEWENN(B1:B4;">17") den Wert 63 und die Formel

=SUMMEWENN(A1:A4;"Karl";B1:B4) den Wert 45 (=22+23).

Anmerkung: Beide Funktionen lassen sich natürlich auch über Zeilen und nicht nur über Spalten (wie es im Vortragsbeispiel der Fall ist) anwenden.

In den letzten Jahren gab es gerade bei der Funktion SUMMEWENN des Öfteren Verständnisprobleme. Da es sich jedoch um eine ziemlich mächtige Funktion handelt (die auch gerne in Prüfungsbeispielen verwendet wird), sollte man sichergehen, dass man auch wirklich verstanden hat wie sie verwendet wird.

Um abschließend noch etwas Verwirrung ☺ rund um die Funktion SUMMEWENN zu stiften, ein kleiner Auszug aus der gern beschworenen Excel‐Hilfe (Taste F1 ):

Auf den ersten Blick erscheint die hier getroffene Aussage widersprüchlich und etwas sinnentleert. Was uns Excel bzw. die Programmierer und Übersetzer hier zu sagen versuchen, ist: Der Bereich über den summiert werden soll, wird von Excel automatisch so gewählt, dass er in Form (=“Richtung“ – Zeile oder Spalte) und Größe (Anzahl der Zellen) mit dem Bereich in dem gesucht werden soll (= 1. Argument) übereinstimmt. Excel benötigt quasi nur eine „Ausgangszelle“, von der aus es den Bereich selbstständig in Form und Größe anpasst. Diese „Ausgangszelle“ wird als die Zelle ganz links oben definiert. Um beim Beispiel aus der grauen Formelbox vom oberen Teil der Seite zu bleiben:

=SUMMEWENN(A1:A4;"Karl";B1:B4) liefert das gleiche Ergebnis wie =SUMMEWENN(A1:A4;"Karl";B1).

Und nun zu etwas ganz anderem…



Exkurs: Die Mehrfachoperation – von Studenten und Nudeln: Die „doppelte“ Mehrfachoperation:

Die Vergangenheit hat gezeigt, dass die Mehrfachoperation (bzw. Datentabelle wie sie in Excel 2007 genannt wird) und das ihr zugrundeliegende Konzept vielen Studierenden einige Schwierigkeiten bereitet. Dies ist durch‐aus verständlich, gilt es doch mit dem etwas konfusen Eingabefenster von Excel fertig zu werden, welches nicht nur etwas spartanisch daherkommt, sondern noch dazu keinerlei Hilfestellung oder geistreiche Anmerkungen bietet. Aber dafür sind wir ja da!

Darum folgt nun ein einfach zu verstehendes, aus dem Studentenleben gegriffenes Beispiel um die doppelte Mehrfachoperation anschaulich zu erklären. (Anm.: mit „doppelter Mehrfachoperation“ ist eine Mehrfachopera‐tion gemeint, bei der es Werte in der ersten Zeile UND der ersten Spalte gibt!)

Wir nehmen nun an, dass es einen kochfreudigen Studenten gibt, der gerne und oft KommilitonInnen bekocht. Da Studenten meistens nicht große Reichtümer ihr eigen nennen können, greift unser Gastgeber auf ein günstiges Nahrungsmittel zurück, nämlich auf Nudeln. Außerdem gibt es nahezu unendlich viele Möglichkeiten, womit man Nudeln essen kann – es wird also nie fad bzw. eintönig (bzw. eingeschmackig).

Unser schlauer Akademiker in spe hat für diesen Abend 7 Freunde eingeladen (da auch er mitisst, sind es insgesamt 8 Personen). Er möchte natürlich nicht zu wenig kochen, daher rechnet er sich die Menge an Nudeln aus, die auf jeden Gast entfallen würde, wenn er seinen ganzen Vorrat von 3 kg zubereiten würde. Da unser Protagonist auch diese LVA besucht hat, startet er Excel und führt dort seine Berechnungen durch (siehe Screenshot rechts). Zur besseren Lesbarkeit vergibt er Namen für die Zellen F2 (Nudeln) und F3 (Studenten). Nun wird die Menge der Nudeln durch Zahl der Studenten dividiert, das Ergebnis sind Nudeln in Kilogramm je Person.

Zufrieden mit dem Ergebnis und erfreut ob der Schnelligkeit und Einfachheit, mit der sich das Problem lösen ließ, geht er einen Schritt weiter. Um in Zukunft Zeit zu sparen will er eine Tabelle anlegen, aus der sich sofort alle Verhältnisse zwischen Anzahl an hungrigen Gästen und verschiedenen Mengen an Nudeln (für zukünftige gesell‐schaftliche Großereignisse) herauslesen lassen. Also legt er folgende Tabelle in Excel an:

Jedes Feld in dieser Tabelle soll nun mit Hilfe der oben verwendeten Formel mit den zugehörigen Werten gefüllt werden.

Da es sich hier aber um eine 10x14 Tabelle han‐delt, müsste er 140 Werte berechnen – aufgrund der unzulänglichen Genauigkeit eines Menschen eine unwahrscheinlich große Fehlerquelle. Zum Glück ist unser Küchen‐Held auch ein Excel‐Held und weiß was eine Mehrfachoperation tut und wofür sie gut ist – nämlich genau für eine derartige Aufgabe. Am Schnittpunkt der ersten Zeile (die Zahl der Personen) und der ersten Spalte (Menge an Nu‐deln) muss die Formel / Berechnungsvorschrift stehen mit der die Mehrfachoperation arbeiten soll (= das orange Feld). Wahlweise kann dies auch der Bezug auf eine Formel sein.

In diesem Fall gibt es die gewünschte Berechnungsvorschrift bereits, da unser Student ja bereits einmal die Men‐ge an Nudeln pro Person bei 3 kg Nudeln und 8 hungrigen Essern ermittelt hat. Daher stellt er mit einem Gleich‐heitszeichen (=) und einem Klick auf die Ergebniszelle einen Bezug im orangen Feld her. Jetzt hat er alle Vorberei‐tungen für die Ausführung der Mehrfachoperation getroffen.



Im nächsten Schritt markiert der Küchenchef die gesamte Tabelle, beginnend beim orangen Rechteck welches mit dem Bezug gefüllt ist, bis zur rechten unteren Ecke, dem Schnittpunkt von 7 kg Nudeln und 10 Studenten (siehe Screenshot links).

Nun wählt er die Multifunktionsleiste „Daten“ aus und findet dort in der Kategorie „Daten‐tools“ das Pull‐Down‐Menü mit dem klingen‐den und vielsagenden Namen „Was‐wäre‐wenn‐Analyse“. Der Menü‐Eintrag, hinter dem sich die Mehr‐fachoperation verbirgt, heißt „Datentabelle…“ (siehe auch nachfolgenden Screenshot).

Anmerkung: bis zur Microsoft‐Office Version 2000 hieß der Menü‐Eintrag tatsächlich noch „Mehrfachoperation“, seither hat sich sowohl der Name als auch die Position dieser Funktion mehrmals geändert. In Excel 2003 bzw. XP fin‐det man die Mehrfachoperation im Menü „Da‐ten“ unter dem Eintrag „Tabelle“.

Nach einem Klick auf den Eintrag „Datentabelle…“ erscheint eines der un‐scheinbarsten und banalsten Eingabefenster die es überhaupt in Excel gibt (siehe Screenshot rechts). Es gibt keinerlei Soforthilfe, brauchbare Hinweise oder Anweisungen, wie mit diesem Fenster zu verfahren ist und was es von einem will. Offensichtlich ist man in Tiefen von Excel eingedrungen, die noch nie ein Mensch zuvor gesehen hat oder die nur wirklichen Spezialisten vorbehalten sind. ☺

Zum Glück weiß der schlaue Student genau was er zu tun hat (offensichtlich hat er in der LVA auch aufgepasst). Sobald nämlich der Eintrag „Datentabelle…“ ausgewählt wird, untersucht Excel den markierten Bereich auf For‐meln und Werte. Wenn in der ersten Zeile sowie in der ersten Spalte Zahlenwerte und im Schnittpunkt (hier das

orange Eck) eine Formel gefunden werden, will die Mehrfachoperation nur mehr wissen, wo sie diese Werte (aus der ersten Zeile und der ersten Spal‐te) einsetzen soll. Und dies muss natürlich dort geschehen, wo sich die Formel im orangen Eck die Werte zur Berechnung holt. In unserem Beispiel sind dies die Zellen F2 für die Nudeln (Werte aus Spalte – gemeint ist hier natürlich die Spalte der Matrix in der bereits Werte stehen, und das ist die … ERSTE!) und die Zelle F3 für die Studenten (Werte aus Zeile – „Werte aus

der ERSTEN Zeile“ …). Da auch Namen für die Zellen vergeben wurden (nämlich „Nudeln“ für Zelle F2, „Studen‐ten“ für Zelle F3), könnten natürlich diese anstelle der Bezüge verwendet werden.

Nach drücken der „OK“‐Schaltfläche passiert folgendes: Excel errechnet die Ergebnisse aller möglichen Werte‐kombinationen basierend auf der Formel in der linken oberen Ecke. Es werden also z.B. 6 kg Nudeln nacheinander durch jede mögliche Zahl an Studenten dividiert (in unserem Fall von 1 bis 10).



Wenn alles richtig gemacht wurde, sollte das Ergebnis wie im Screenshot links aussehen. Ist dies nicht der Fall, so gibt es einige Möglichkei‐ten, 2 seien hier geschildert:

(#) Ergebnisse vorhanden, diese scheinen je‐doch falsch zu sein: eventuell Zeile und Spalte vertauscht?

(#) Alle Werte sind gleich dem Ergebnis der Formel in der linken oberen Ecke:

a) Für „Werte aus Zeile“ bzw. Spalte wur‐den falsche Zellen ausgewählt

b) Die Berechnungsoptionen in den Excel‐Optionen (Office‐Schaltfläche) sind falsch eingestellt.

Die „einfache“ Mehrfachoperation:

Als unser Student nun eines Tages gegen 6 Uhr am Abend aufsteht, kommt ihm die glorreiche Idee wieder ein paar Freunde zum Essen einzuladen. Denn gemeinsam ist (isst?) man ja bekanntlich weniger allein und das Essen schmeckt auch gleich besser, wenn man nicht allein am Tisch sitzt.

Leider muss unser Hauptdarsteller mit Erschrecken feststellen, dass er am Vortag nicht nur sein ganzes Geld in Getränke alkoholischer Natur investiert hat, nein, er hat noch dazu hoffnungslos verschlafen. Daher kann er sei‐nen Nudel Vorrat nicht auffüllen und muss mit den vorhandenen Mengen vorliebnehmen. Ein kurzer Blick in das Nudel‐Regal fördert eine nicht ganz volle 3 kg‐Packung zu Tage. Diese wird einmal kurz auf die Waage gelegt und schon weiß der Studiosus Maximus, dass er über ein Kapital von 2,3 kg (also 2300 g) Nudeln verfügt. Schlau wie er ist, startet er wiederum das Programm seines Vertrauens (also Excel) um zu berechnen, wie viele Freunde er bei einer Fix‐Menge an Essen verköstigen kann.

Schnell ist eine ansprechende Tabelle erstellt (siehe rechts), für die folgende „einfache“ Mehrfachoperation wird das „Eingabefeld“ der ersten (siehe unten) „doppelten“ Mehr‐fachoperation verwendet. Einziger Unterschied: die Menge an Nudeln ist durch den Haushaltsvorrat von 2,3 kg begrenzt. Daher wird in das „Eingabefeld“ bei Nudeln der Wert 2,3 eingetragen. Die Zahl an Studenten ist egal, der Kochprofi wählt hier vorerst den Wert eins.

Nun fehlt nur noch die Formel bzw. der Bezug für die Mehrfachoperation in der obigen Tabelle. Da es aber nur Werte in der ersten Zeile der Tabelle gibt (nämlich die Menge an hungrigen Studenten) und keine in der ersten Spalte (die Menge Nudeln ist ja fix), muss die Formel vor die Ergebnis‐Zeile (also die Zeile die die Ergebnisse beinhalten wird) platziert werden. So weiß die Mehr‐fachoperation, dass keine Werte aus der Spalte benötigt werden.

Unser Excel‐Genie markiert also den Bereich von der grau‐en Zelle über der Formel (oranges Feld) bis zur freien Zelle unter den 10 Studenten (also ein Bereich von 11x2 Zellen). Dann ruft er, wie zuvor, die Mehrfachoperation auf und fügt als „Werte aus Zeile“ das Feld Studenten im Eingabe‐feld ein. Die Mehrfachoperation wird also die Zahl der Studenten nacheinander dort einsetzen. Ein Klick auf OK beendet die Eingabe und schon weiß unser Student, wie viele Freunde er heute bewirten kann.



Frage 4 – Mehrfachoperation (Datentabelle)

Angenommen wir wollen wissen, wie hoch die Zuschüsse für verschiedene Antragssummen (0,‐ bis 20.000,‐ in 1000er‐Schritten) abhängig von den verschiedenen Zuschusstabellen TAB1 bis TAB3 sind.

Diese Fragestellung ist gleichbedeutend mit dem systematischen Einsetzen der verschiedenen Antragssummen und Zuschusstabellen in unser Berechnungsmodell – i.e. in die Zellen mit den Namen Antragssumme ($D$34) und Landes_TAB ($D$36). Das zugehörige Konzept in MS‐Excel heißt Mehrfachoperation bzw. seit Excel 2007 wieder "Datentabelle".

Eine Mehrfachoperation ist immer dann sinnvoll, wenn berechnet werden soll, wie sich die systematische Va‐riation von (einem oder zwei Eingabe‐) Werten auf das Ergebnis einer Berechnung/Formel auswirkt.

So wird’s gemacht (für die Bezüge siehe die Abbildung Frage 4):

(1) Eingabe der zu analysierenden Formel in die linke obere Ecke (Zelle Q18) =Zuschuß

(2) Eingabe der veränderlichen Werte rechts davon und darunter (R18:T18 bzw. Q19:Q39)

(3) Markieren des gesamten Bereiches (Q18:T39)

(4) Aufruf des Befehls „Datentabelle“ in der Multifunktions‐leiste „Daten“ – Kategorie „Datentools“ – Menü „Was‐wäre‐wenn‐Analyse“. Im erscheinenden Dialogfeld wird gefragt, in welche Ta‐bellenposition die Werte aus der (ersten) Zeile des mar‐kierten Bereiches eingesetzt werden sollen ⇒ Die Werte TAB1, TAB2 und TAB3 sollen in die Zelle mit dem Namen Landes_TAB eingesetzt werden. Darunter wird gefragt, wo die verschiedenen Werte aus der (ersten) Spalte des Be‐reichs eingesetzt werden sollen ⇒ Die verschiedenen An‐tragssummen sollen in die benannte Zelle Antragssumme eingesetzt werden.

(5) Nach dem Betätigen der OK‐Taste passiert Folgendes: Die verschiedenen Werte aus der ersten Zeile und Spalte wer‐den (virtuell) an den beiden angegebenen Stellen in der Tabelle eingesetzt. Die Auswirkung dieses Einset‐zens auf das Ergebnis der Formel in der linken oberen Ecke (i.e. der Zuschuss) wird an der ent‐sprechenden Stelle in der Ergebnismatrix einge‐tragen (z.B. TAB3, 9.000 3.900).

(6) Falls die Matrix nicht sofort berechnet wird (sie‐he hierzu das Nudeln und Studenten Beispiel von den vorigen Seiten), liegt das daran, dass unter Excel‐Optionen im Register Formeln das automa‐tische Berechnen von Datentabellen ausgeschal‐tet ist (siehe Abb. rechts). Die automatische Be‐rechnung auszuschalten ist manchmal durchaus sinnvoll, weil große und/oder aufwändige Mehr‐fachoperationen die Performance bei automati‐scher Neuberechnung extrem bremsen können (v.a. auf langsamen Rechnern!). Durch Drücken der Taste F9 wird das Tabellenblatt neu berechnet.



Übungsbeispiel 1: Vervollständigen des Vorlesungsbeispiels (#) Für alle Antragssummen im Mehrfachoperationsbereich: „Was ist der höchste Zuschuss und aufgrund welcher Zuschusstabelle kommt er zustande?“

ANLEITUNG: den höchsten Zuschuss bekommt man mit der MAX‐Funktion und in welcher Spalte dieser größte Wert steht, wird mit der VERGLEICH‐Funktion (mit Vergleichstyp 0!) berechnet. Unter Verwendung dieses Ergebnisses könnt Ihr mit der INDEX‐Funktion die ge‐naue Bezeichnung der jeweiligen Tabelle aus der Zeile mit den Überschriften extrahieren ($R$17:$T$17, ev. Namen vergeben!).

(#) Für alle Antragssummen im Mehrfachoperationsbereich: „Wie hoch ist der gewährte Zuschuss in Prozent der Antragssumme gegliedert nach TAB1, TAB2 und TAB3?“

(#) Zum graphischen Vergleich erstellt die beiden (oder auch andere) Diagramme.



Übungsbeispiel 2: Eine Währungsumrechnungstabelle (Quelle: www.oenb.at; Bearb. Daniela Müller)



Übungsbeispiel 3: Graph einer Funktion mit Achsensteuerung

Beispiel: Gedämpfte Schwingung nach der Formel xexfx

2cos)( 3−

= (Zellen C3:C4)

Die Achsensteuerung besteht in der Angabe der Randwerte eines abgeschlossenen Intervalls auf der X‐Achse [xvon,xbis]. (Werte in F3:F4, Formeln in H4:H44)

(#) Berechnen der x‐Werte (in Spalte H) aus xvon und xbis ( x‐Achsensteuerung). Hierbei wäre es natürlich

sehr elegant, wenn man die Zahl der Schritte bzw. Intervalle zwischen xvon und xbis nicht einfach nur zählt, sondern mit einer Funktion errechnet!

(#) Mehrfachoperation mit Werten nur aus Spalte (die Formel steht dabei schräg rechts über den "Werten aus Spalte", also in I3: =C4 oder =FX; dann H3:I44 markieren, Was‐wäre‐wenn‐Analyse – Datentabelle... mit Werte aus Spalte: C3 oder X; Werte aus Zeile bleibt leer!)

(#) Probiert auch andere Funktionen (Polynomfunktionen, z.B.: 3x³‐2x²+3x‐6 und dergl.)

(#) Variiert die Xvon‐ und Xbis‐Werte ⇒ der Graph sollte sich automatisch ändern (eventuell vorher F9 drü‐cken, wenn die automatische Berechnung von Mehrfachoperationen ausgeschaltet sein sollte!).

(#) Frage: „Was passiert, wenn Xbis < Xvon ist?“ ⇒ mit WENN‐Funktion prüfen!



Übungsbeispiel 4: Graph einer Funktion mit 2 Unbekannten und Achsensteuerung

Einführung in die planungsbezogene EDV · Eine sehr praktische – weil schnelle – Methode zur...

Documents

Transcript of Einführung in die planungsbezogene EDV · Eine sehr praktische – weil schnelle – Methode zur...